DROP TABLE IF EXISTS a_implementation_report;
CREATE TABLE a_implementation_report
(
ValueType	varchar(255),
Quantity 	varchar(50)
);



INSERT INTO a_implementation_report (ValueType, quantity) VALUES ('Количество товаров на сервере', (
SELECT count(DISTINCT(ucp.markingofthegood)) from un_cg_product ucp 

join un_cg_price cpr on (cpr.product_marking=ucp.markingofthegood and cpr.price_number=1 and cpr.product_status=99 and now() BETWEEN cpr.begindate and cpr.enddate)
where ucp.deleted=FALSE and ucp.status=99 ));


INSERT INTO a_implementation_report (ValueType, quantity) VALUES ('Количество диапазонов карт/купонов на сервере', (SELECT "count"(1) from card_cardrange where deleted<>true));
INSERT INTO a_implementation_report (ValueType, quantity) VALUES ('Количество рекламных акций на сервере', (SELECT count(1) from discounts_advertisingactions WHERE active=TRUE and  (periodfinish>now() or periodfinish is null)));
INSERT INTO a_implementation_report (ValueType, quantity) VALUES ('Количество кассиров на сервере', (SELECT count(1)  from us_user  where deleted=FALSE and blocked=FALSE));
INSERT INTO a_implementation_report (ValueType, quantity) VALUES (null, null);



--Введите IP-адрес кассы
INSERT INTO a_implementation_report (ValueType, quantity) VALUES ('Количество товаров на кассе', (select q.crd from dblink('host= 

172.29.17.19 

dbname=catalog port=5432 user=postgres password=postgres','SELECT count(DISTINCT(cp.item)) from cg_product cp left join  likond l on substring(cp.item from 1 for 6) = l.marking join cg_price cpr on (cpr.product_item=cp.item and cpr.price_number=1 and now() BETWEEN cpr.begindate and cpr.enddate) where (l.begin_date < now() and ((l.end_date > now())) or  l.marking is null)') q(crd int)));


INSERT INTO a_implementation_report (ValueType, quantity) VALUES ('Количество диапазонов карт/купонов на кассе', (select q.crd from dblink('host=

172.29.17.19 

dbname=cards port=5432 user=postgres password=postgres','SELECT "count"(1) from card_cardrange where deleted<>true') q(crd int)));

INSERT INTO a_implementation_report (ValueType, quantity) VALUES ('Количество рекламных акций на кассе', (select q.crd from dblink('host=

172.29.17.19 

dbname=discount port=5432 user=postgres password=postgres','SELECT count(1) from discounts_advertisingactions WHERE active=TRUE and  periodfinish>now() or periodfinish is null') q(crd int)));

INSERT INTO a_implementation_report (ValueType, quantity) VALUES ('Количество кассиров на кассе', (select q.crd from dblink('host=

172.29.17.19 

dbname=user port=5432 user=postgres password=postgres','SELECT count(1)  from us_user  where deleted=FALSE and blocked=FALSE') q(crd int)));



------------------
SELECT ValueType || ' = ' || Quantity || '' as Report FROM a_implementation_report