--
-- База данных: `reestrz`
-- 

-- --------------------------------------------------------

--
-- Структура таблицы `credit`
--

CREATE TABLE IF NOT EXISTS `credit` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`id_jur` int(9) NOT NULL DEFAULT '0',
`id_firm` int(9) NOT NULL DEFAULT '0',
`postup` decimal(10,2) NOT NULL DEFAULT '0.00',
`datapost` date DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `id_jur` (`id_jur`),
KEY `id_firm` (`id_firm`),
KEY `postup` (`postup`),
KEY `datapost` (`datapost`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=85 ;

--
-- Дамп данных таблицы `credit`
--

INSERT INTO `credit` (`id`, `id_jur`, `id_firm`, `postup`, `datapost`) VALUES
(1, 1, 2, 1161.12, '2012-01-12'),
(2, 2, 2, 2485.08, '2012-01-12'),
(3, 3, 2, 1600.00, '2012-03-22'),
(4, 4, 2, 800.00, '2012-11-19'),
(5, 5, 3, 6119.27, '2012-03-21'),
(6, 5, 3, 5065.53, '2012-04-13'),
(7, 5, 3, 7054.35, '2012-05-28'),
(8, 5, 3, 5065.53, '2012-06-27'),
(9, 5, 3, 7782.80, '2012-07-24'),
(10, 5, 3, 12210.95, '2012-08-30'),
(11, 5, 3, 11885.66, '2012-10-04'),
(12, 5, 3, 7823.19, '2012-10-24'),
(13, 5, 3, 10248.02, '2012-12-07'),
(14, 5, 3, 2802.34, '2012-02-02'),
(15, 5, 3, 6804.26, '2012-12-26'),
(16, 6, 3, 4000.00, '2013-03-07'),
(17, 6, 3, 11406.15, '2013-03-07'),
(18, 6, 3, 15200.00, '2013-03-25'),
(19, 6, 3, 6221.02, '2013-03-25'),
(20, 6, 3, 11200.00, '2013-04-23'),
(22, 6, 3, 7117.82, '2013-05-24'),
(26, 6, 3, 2800.00, '2013-07-24'),
(27, 6, 3, 9733.90, '2013-07-24'),
(28, 6, 3, 4800.00, '2013-07-24'),
(29, 5, 3, 5424.25, '2013-01-17'),
(30, 6, 3, 1313.57, '2013-08-26'),
(31, 6, 3, 3200.00, '2013-08-26'),
(32, 6, 3, 800.00, '2013-08-26'),
(33, 6, 3, 5600.00, '2013-09-24'),
(34, 6, 3, 11975.90, '2013-09-26'),
(35, 6, 3, 12004.44, '2013-10-23'),
(36, 6, 3, 4800.00, '2013-10-24'),
(37, 6, 3, 3200.00, '2013-11-25'),
(38, 6, 3, 7566.22, '2013-11-25'),
(39, 6, 3, 7200.00, '2013-12-27'),
(40, 6, 3, 11406.16, '2013-12-24'),
(41, 6, 3, 7600.00, '2014-01-20'),
(42, 6, 3, 7566.22, '2014-01-20'),
(44, 7, 3, 8379.38, '2014-03-25'),
(45, 8, 3, 8400.00, '2014-04-09'),
(47, 8, 3, 8000.00, '2014-04-24'),
(48, 7, 3, 8850.20, '2014-05-27'),
(49, 8, 3, 9600.00, '2014-06-25'),
(50, 7, 3, 13576.07, '2014-06-25'),
(51, 8, 3, 4000.00, '2014-07-25'),
(52, 7, 3, 6959.86, '2014-07-25'),
(53, 8, 3, 4400.00, '2014-08-25'),
(54, 7, 3, 12134.13, '2014-08-25'),
(55, 8, 3, 5600.00, '2014-10-22'),
(56, 7, 3, 11820.24, '2014-10-24'),
(57, 8, 3, 5600.00, '2014-10-24'),
(58, 7, 3, 8686.20, '2014-10-28'),
(59, 8, 3, 7600.00, '2014-11-26'),
(60, 7, 3, 6877.24, '2014-11-26'),
(61, 8, 3, 7600.00, '2014-02-21'),
(62, 8, 3, 14400.00, '2014-05-27'),
(63, 8, 3, 1200.00, '2015-01-27'),
(64, 7, 3, 7818.85, '2015-01-27'),
(65, 9, 3, 9713.66, '2015-02-24'),
(66, 10, 3, 7542.56, '2015-02-24'),
(67, 9, 3, 6720.30, '2015-03-24'),
(68, 10, 3, 7986.24, '2015-04-21'),
(69, 11, 4, 2200.61, '2012-05-16'),
(70, 11, 4, 2047.98, '2012-10-19'),
(71, 11, 4, 2047.98, '2012-10-26'),
(75, 12, 4, 3236.74, '2013-07-24'),
(76, 12, 4, 2099.22, '2013-11-14'),
(77, 13, 4, 13510.17, '2014-06-06'),
(78, 13, 4, 7290.43, '2014-09-10'),
(79, 13, 4, 1070.71, '2014-11-20'),
(80, 14, 5, 2990.88, '2012-04-16'),
(81, 14, 5, 2388.75, '2012-08-06'),
(82, 15, 5, 2000.46, '2013-01-17'),
(84, 17, 5, 2203.89, '2012-11-20');

-- --------------------------------------------------------

--
-- Структура таблицы `firms`
--

CREATE TABLE IF NOT EXISTS `firms` (
`id` int(9) NOT NULL AUTO_INCREMENT,
`n_short` varchar(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=8 ;

--
-- Дамп данных таблицы `firms`
--

INSERT INTO `firms` (`id`, `n_short`) VALUES
(1, 'не указано'),
(2, 'Западная компания'),
(3, 'Северная компания'),
(4, 'Восточная компания'),
(5, 'Южная компания'),
(6, 'Северо-Восточная компания'),
(7, 'Юго-Западная компания');

-- --------------------------------------------------------

--
-- Структура таблицы `jurnal`
--

CREATE TABLE IF NOT EXISTS `jurnal` (
`id` int(9) NOT NULL AUTO_INCREMENT,
`id_firm` int(9) NOT NULL DEFAULT '0',
`sumdoc` decimal(10,2) NOT NULL DEFAULT '0.00',
PRIMARY KEY (`id`),
KEY `id_firm` (`id_firm`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=19 ;

--
-- Дамп данных таблицы `jurnal`
--

INSERT INTO `jurnal` (`id`, `id_firm`, `sumdoc`) VALUES
(1, 2, 1161.12),
(2, 2, 2485.08),
(3, 2, 1600.00),
(4, 2, 800.00),
(5, 3, 88286.15),
(6, 3, 232231.44),
(7, 3, 88799.37),
(8, 3, 125045.67),
(9, 3, 112535.31),
(10, 3, 82524.48),
(11, 4, 22162.85),
(12, 4, 6296.57),
(13, 4, 21871.30),
(14, 5, 5379.63),
(15, 5, 2822.56),
(16, 5, 17414.12),
(17, 5, 2203.89),
(18, 6, 5384.16); 

SELECT
company.`n_short`
, COUNT(*) as count_zayav
, SUM(`sumdoc`) as sum_zayav
, IFNULL(cred.`cntcr`,0) as count_post
, IFNULL(cred.`sumpost`,0) as sum_post
, IFNULL(SUM(`sumdoc`)-IFNULL(cred.`sumpost`,0),0) as debet
FROM `firms` company, `jurnal` 
LEFT JOIN
(SELECT
IFNULL(COUNT(`id`),0) AS cntcr
, IFNULL(SUM(`postup`),0) AS sumpost
, `id_firm`
, `id_jur`
FROM `credit`
GROUP BY `id_firm`
) cred ON cred.`id_jur`=jurnal.`id`
WHERE jurnal.`id_firm`=company.`id`
GROUP BY company.`id`

SELECT
firms.`n_short`,
  COUNT(DISTINCT jurnal.`id`) as count_zayav, 
  SUM(jurnal.`sumdoc`) as sum_zayav,
  cred.`count_post`,
  cred.`sum_post`
FROM 
  `jurnal` , `firms`
LEFT JOIN 
 (
	SELECT
 		COUNT( credit.`id`) as count_post,
 		SUM(credit.`postup`) as sum_post,
		credit.`id_firm`
	FROM `firms` , `credit` 
	WHERE  
 		credit.`id_jur` IN
   	(
     	SELECT DISTINCT jurnal.`id` 
     	FROM `jurnal` 
     	WHERE 
	     jurnal.`id`  IN
   	   ( 
      		SELECT `id_jur` 
      		FROM `credit` 
      		WHERE credit.`id_jur`=jurnal.`id`
       		HAVING( jurnal.`sumdoc`=IFNULL(SUM(credit.`postup`),0) )
     	    )
  	 )
	AND firms.`id`=credit.`id_firm`
	GROUP BY credit.`id_firm`
 ) cred ON firms.`id`=cred.`id_firm`
WHERE
  jurnal.`id`  IN
  ( 
   SELECT DISTINCT `id_jur` FROM `credit` WHERE credit.`id_jur`=jurnal.`id`
   HAVING( jurnal.`sumdoc`=IFNULL(SUM(credit.`postup`),0) )
  ) 
AND firms.`id`=jurnal.`id_firm`
GROUP BY firms.`id` 

SELECT
firms.`n_short`,
  COUNT(DISTINCT jurnal.`id`) as count_zayav, 
  SUM(jurnal.`sumdoc`) as sum_zayav,
  IFNULL(cred.`countpost`,0) as count_post,
  IFNULL(cred.`sumpost`,0) as sum_post,
  SUM(jurnal.`sumdoc`)-IFNULL(cred.`sumpost`,0) as debet
FROM 
  `jurnal` , `firms`
LEFT JOIN 
 (
	SELECT
 		COUNT( credit.`id`) as countpost,
 		SUM(credit.`postup`) as sumpost,
		credit.`id_firm`
	FROM `firms` , `credit` 
	WHERE  
 		credit.`id_jur` IN
   	(
     	SELECT DISTINCT jurnal.`id` 
     	FROM `jurnal` 
     	WHERE 
	     jurnal.`id`  IN
   	   ( 
      		SELECT `id_jur` 
      		FROM `credit` 
      		WHERE credit.`id_jur`=jurnal.`id`
       		HAVING( jurnal.`sumdoc`=SUM(credit.`postup`) )
     		)
  		)
	AND firms.`id`=credit.`id_firm`
	GROUP BY credit.`id_firm`
 ) cred ON firms.`id`=cred.`id_firm`
WHERE
  jurnal.`id`  IN
  ( 
   SELECT DISTINCT `id_jur` FROM `credit` WHERE credit.`id_jur`=jurnal.`id`
   HAVING( jurnal.`sumdoc`=SUM(credit.`postup`) )
  ) 
AND firms.`id`=jurnal.`id_firm`
GROUP BY firms.`id`