SELECT id, begindate, currency_code, enddate, price_number, price, senttoscales,
       department_number, department_status, product_marking, product_status
FROM un_cg_price where product_marking in (select product_marking from un_cg_barcode where code='2078086726020');

SELECT "count"(1) from discounts_advertisingactions where periodfinish >=current_timestamp OR periodfinish is NULL;

select * from discounts_advertisingactions daa
	join discounts_topology_condition dtc on daa.id = dtc.action_id
	join topology_shop ts on dtc.location_code = ts.id
		where ts.number='номер магазина'

SELECT 
	v.number,
	v.model, 
	v.addressing,
	sum(v.PLU_NEW) as PLU_NEW,
	sum(v.PLU_LOAD) as PLU_LOAD,
	sum(v.PLU_LOADED) as PLU_LOADED,
	sum(v.PLU_ERROR) as PLU_ERROR,
	sum(v.OFFLINE_ERROR) as OFFLINE_ERROR,
	v.name,
	v.plu_generation_method
 from 
(
select 
	distinct
	s.number,
	s.code,
	s.model, 
	s.addressing,
	case l0.status when 0 then 1 else 0 end as PLU_NEW,
	case l1.status when 1 then 1 else 0 end as PLU_LOAD,
	case l2.status when 2 then 1 else 0 end as PLU_LOADED,
	case l3.status when 3 then 1 else 0 end as PLU_ERROR,
	case l3_p.status when 3 then 1 else 0 end as OFFLINE_ERROR,
	l0.id,
	l1.id,
	l2.id,
	l3.id,
	l3_p.id,
	t.name,
	t.plu_generation_method
from 
	scales_scales s 
	join scales_template t on t.id = s.scales_template_id
	join scales_plues p on 1=1
	left join scales_linktoplu l1  on l1.scales_code = s.code and p.id = l1.id_plu and l1.status = 1
	left join scales_linktoplu l2  on l2.scales_code = s.code and p.id = l2.id_plu and l2.status = 2
	left join scales_linktoplu l3  on l3.scales_code = s.code and p.id = l3.id_plu and l3.status = 3 and l3.lasterrorcode <> 'PORT-ERROR'
	left join scales_linktoplu l3_p on l3_p.scales_code = s.code and p.id = l3_p.id_plu and l3_p.status = 3 and l3_p.lasterrorcode = 'PORT-ERROR'
	left join scales_linktoplu l0  on l0.scales_code = s.code and p.id = l0.id_plu and l0.status = 0
where 
	s.deleted = false
) v
group by 
	number,
	model, 
	addressing,
	name,
	plu_generation_method
order by 
	number

SELECT id, active,  guid, lastchanges, modefield, "name", parentguid, userestrictions, "version", periodfinish, periodstart, worksanytime, external_code
  FROM discounts_advertisingactions
where id in (select action_id from discounts_topology_condition 
where  location_code in (select id from topology_shop where number='номер магазина')) 
and (periodfinish >=current_timestamp or periodfinish is NULL) order by name;

SELECT id, plugin_id, plugin_name, name, value, type, class_name, parent_id, action_id  FROM discounts_action_plugin_property where action_id='id рекламной акции';

SELECT *   FROM loy_adv_action_in_purchase 
where guid in (select advert_act_guid from loy_discount_positions 
where transaction_id   in (select id from loy_transaction where cash_number='1' and purchase_number='5' and shift_number='45'));

select * from un_cg_sale_restrictions where product_marking in (select product_marking from un_cg_barcode where code='4607145132038');

SELECT s.shopindex, s.cashnum, s.numshift, docs.numberfield as nom, type FROM od_shift s
LEFT JOIN (SELECT id_shift, numberfield, CASE WHEN 
operationtype=true THEN 'CHECK_P' ELSE 'CHECK_R' END || CASE WHEN checkstatus=1 THEN '_ANNUL' ELSE '' END as type 
FROM od_purchase UNION ALL
SELECT id_shift, numberfield, 'INTRO' FROM od_introduction UNION ALL
SELECT id_shift, numberfield, 'WITDR' FROM od_withdrawal UNION ALL
SELECT id_shift, numberfield, CASE WHEN reportz THEN 'Z' ELSE 'X' END FROM od_reportshift) docs ON docs.id_shift = s.id

WHERE

s.shopindex=3

AND s.cashnum=1
AND s.numshift=11

ORDER BY 1,2,3,4

SELECT t1.shopindex, t1.cashnum, t1.numshift, t1.nom-1 as nom_not_exists FROM (
SELECT s.shopindex, s.cashnum, s.numshift, docs.numberfield as nom FROM od_shift s
LEFT JOIN (SELECT id_shift, numberfield FROM od_purchase UNION ALL
SELECT id_shift, numberfield FROM od_introduction UNION ALL
SELECT id_shift, numberfield FROM od_withdrawal UNION ALL
SELECT id_shift, numberfield FROM od_reportshift) docs ON docs.id_shift = s.id
WHERE s.shiftclose is not null

) t1

LEFT JOIN (SELECT s.shopindex, s.cashnum, s.numshift, docs.numberfield as nom FROM od_shift s
LEFT JOIN (SELECT id_shift, numberfield FROM od_purchase UNION ALL
SELECT id_shift, numberfield FROM od_introduction UNION ALL
SELECT id_shift, numberfield FROM od_withdrawal UNION ALL
SELECT id_shift, numberfield FROM od_reportshift) docs ON docs.id_shift = s.id

WHERE s.shiftclose is not null) t2 on t1.nom = t2.nom+1

AND t1.shopindex = t2.shopindex AND t1.cashnum = t2.cashnum AND t1.numshift = t2.numshift

WHERE t2.nom is null AND t1.nom<>1

ORDER BY 1,2,3,4

INSERT INTO cash_cash_event (id, cash_id, event, created, module)
    SELECT nextval('hibernate_sequence'),id,'NEW_CASH',(select now()),'тип данных' from cash_cash where status='ACTIVE' AND shop_number='номер магазина' AND number='номер кассы';

INSERT INTO cash_cash_event ( id , cash_id , event , created , module ) SELECT nextval ( 'hibernate_sequence' ) , id , 'NEW_CASH' , ( select now ( ) ) , 'operday' from cash_cash where status = 'ACTIVE' ;

INSERT INTO cash_settingsoutboundmessage (id, objectid, senddate, status, cashnumber, shopnumber)
SELECT nextval('hibernate_sequence'), template_id, now(), 0, number, shop_number 
FROM cash_cash 
WHERE status = 'ACTIVE';

INSERT INTO cash_settingsoutboundmessage (id, objectid, senddate, status, cashnumber, shopnumber)
SELECT nextval('hibernate_sequence'), template_id, now(), 0, number, shop_number 
FROM cash_cash 
WHERE status = 'ACTIVE'
AND number = номер кассы;

INSERT INTO cash_shop_event (ID, shop_num, created, MODULE) SELECT nextval('hibernate_sequence'), 'номер магазина', now(), 'тип данных';

INSERT INTO cash_shop_event (ID, shop_num, created, MODULE) SELECT nextval('hibernate_sequence'), '125', now(), 'equipment';

INSERT INTO cash_shop_event (ID, shop_num, created, MODULE) 
SELECT nextval('hibernate_sequence'), NUMBER, now(), 'equipment' 
from topology_shop where version ISNULL or version IS NOT NULL;

SELECT ec.id, ep.shop, ep.shift, ep.cash, ep.number as receipt_number, ep.saletime,  ec.positionorder,   ec.barcode, ec.goodscode, ec.cost/100 as cost_rub, 
	ec.costwithdiscount/100 as costwithdiscount_rub, ec.discountvalue/100 as discountvalue_rub, ec.count/1000 as count,   ec.nds, ec.ndssum/100 as ndssum_rub
  FROM erpi_positions ec,erpi_purchase ep where ep.operday = '2012-12-20' and ec.id_purchase=ep.id 
order by ep.saletime, ec.positionorder;

create temp table temp (cash int,shift int, cheque int) 
insert into temp select 19,20,4	;   -- задаем кассу, смену, чек
insert into temp select 19,20,5	;
insert into temp select 19,20,6	;

SELECT ec.id, ep.shop, 

ep.shift, ep.cash, ep.number as receipt_number, ep.saletime,  
ec.positionorder,   ec.barcode, ec.goodscode, ec.cost/100 as cost_rub, 
 ec.costwithdiscount/100 as costwithdiscount_rub, 
 ec.discountvalue/100 as discountvalue_rub, ec.count/1000 as count,   
 ec.nds, ec.ndssum/100 as ndssum_rub

FROM erpi_purchase ep

join erpi_positions ec on ec.id_purchase=ep.id
join temp t on t.cash = ep.cash and t.shift = ep.shift and t.cheque = ep.number 

order by ep.saletime, ec.positionorder;

select shopnumber,cashnumber,sum(notloadedproductscount) "count" from cash_activity --where shopnumber in (86,189)
group by shopnumber,cashnumber
having sum(notloadedproductscount)>0
order by shopnumber,cashnumber

--Поиск дублированных чеков (наблюдается до версии 37)

SELECT cash, "number", operday, shift, shop, count(*)
  FROM erpi_purchase group by 1,2,3,4,5 having count(*) > 1

--Выгрузка только одной из копий из этих

UPDATE erpi_purchase 
set sendedtoerp= false
where id in 
 (SELECT min(id) id
  FROM erpi_purchase where sendedtoerp = true group by  operday,shop,cash,shift,  "number" having count(*) > 1)

select * from erpi_purchase where operday='2013-01-27' and shift=52 and cash=23 and number=185

SELECT q.checknum, q.cash, q.operday, q.shop, q.shift

   FROM dblink('host= IP-адрес сервера Retail
                dbname=set_operday
                port=5432
                user=postgres 
                password=postgres'::text, 

   'Select od_purchase.numberfield,od_shift.cashnum, od_shift.operday, od_shift.shopindex, od_shift.numshift 
       from od_purchase,od_shift 
     where od_shift.id=od_purchase.id_shift and checkstatus=0 and operday=''2014-09-14--опердень'''::text) q

   (checknum int, cash int, operday date, shop int, shift int) 
     where not EXISTS (Select e.number, e.cash from erpi_purchase e where e.shop=701 
      and e.operday='2014-09-14' and q.checknum=e.number and e.cash=q.cash);

SELECT ez.amountbypurchasefiscal as sum_zreport, 
sum(ep.amount) as sum_purchase,ez.amountbypurchasefiscal - sum(ep.amount) as difference, 
ep.operday, ep.shop ,ep.cash, ep.shift 
FROM erpi_zReport ez, erpi_purchase ep
Where ep.operationtype = true and ep.operday = '2013-01-02' 

and ep.cash = ez.cashnumber and ep.shift=ez.shiftnumber and ep.shop=shopnumber
Group by ez.amountbypurchasefiscal, ep.cash, ep.operday, ep.shift, ep.shop
order by shop;

select c.id, sum(p.sumpay - COALESCE(cp.changecash,0))

from od_purchase c

join od_payment p on p.id_purchase = c.id
left join od_cashpayment cp on cp.id = p.id
join od_shift s on s.id = c.id_shift

where s.numshift = 'номер смены'

and s.shopindex = 'номер магазина'
and s.cashnum = 'номер кассы'
and c.checksumend <> (p.sumpay - COALESCE(cp.changecash,0))

group by c.id

SELECT * FROM od_purchase WHERE id_shift in (select id from od_shift where numshift = 'номер смены' and cashnum='номер кассы') ;

Select od_position.datecommit, od_giftcardposition.cardnumber, od_position.priceend, od_shift.shopindex, od_shift.cashnum, od_shift.numshift, 
od_purchase.numberfield

from od_position, od_giftcardposition, od_product, od_shift, od_purchase where

od_position.id=od_giftcardposition.id and

od_product.hash=od_position.product_hash and

od_purchase.id_shift=od_shift.id and

od_position.id_purchase=od_purchase.id and

od_product.discriminator='ProductGiftCardEntity' and

od_purchase.checkstatus=0 and

od_position.datecommit BETWEEN '2012-01-01' and '2070-12-31' AND

od_giftcardposition.cardnumber = 'номер карты'

order by od_position.datecommit asc;

Select od_purchase.datecommit, od_payment_gift_card.cardnumber, od_payment_gift_card.amountcard, od_shift.shopindex, 
od_shift.cashnum, od_shift.numshift, od_purchase.numberfield from

od_shift, od_purchase, od_payment, od_payment_gift_card where

od_payment_gift_card.id=od_payment.id and

od_payment.id_purchase=od_purchase.id and

od_purchase.id_shift=od_shift.id and

od_purchase.checkstatus=0 and

od_purchase.datecommit BETWEEN '2012-01-01' and '2070-12-31' AND

od_payment_gift_card.cardnumber = 'номер карты'

order by od_purchase.datecommit asc;

select shopindex, cashnum, numshift, operday, split_part(fiscaldocnum,';',1), id_shift, count(*) 

from od_purchase,od_shift where od_shift.id=od_purchase.id_shift and datecommit>'2014-11-01'  

group by split_part(fiscaldocnum,';',1), id_shift, cashnum,operday, shopindex, numshift having count(*)>1 order by operday ;

SELECT * from od_shift, od_reportshift per where od_shift.id=per.id_shift and (per.datecommit in (select max(rep.datecommit) 

from od_reportshift as rep 

where rep.datecommit > now()- cast('25 days' as interval) 

GROUP BY rep.id_shift) and per.sumpurchase<>per.sumpurchasefiscal and per.datecommit > now()- cast('25 days' as interval))

select  sum(p.sumpay - COALESCE(cp.changecash,0))

from od_purchase c

join od_payment p on p.id_purchase = c.id

left join od_cashpayment cp on cp.id = p.id

join od_shift s on s.id = c.id_shift

where s.numshift = 'номер смены'

and s.shopindex = 'номер магазина'

and s.cashnum = 'номер кассы'

SELECT *

FROM od_purchase 

where id in (SELECT id_purchase from od_position where product_hash in (select hash from od_product where barcode = 'штрих-код'));

INSERT INTO cash_settingsoutboundmessage (id, objectid, senddate, status, cashnumber, shopnumber)
SELECT nextval('hibernate_sequence'), template_id, now(), 0, number, shop_number 
FROM cash_cash 
WHERE status = 'ACTIVE'
-- AND shop_number = 1 -- номер магазина - раскомментировать, если требуется отправка на конкретный магазин
-- AND number = 1  -- номер кассы - раскомментировать, если требуется отправка на конкретную кассу
;

select * from cg_price where product_item in (select product_item from cg_barcode where barcode='2078080936876')

select b.barcode,

p.name,

p1.price*0.01 "price1",

case when now() between p1.begindate and p1.enddate then '+' else '-' end "activ1",

p2.price*0.01 "price2",

case when now() between p2.begindate and p2.enddate then '+' else '-' end "activ2",

p3.price*0.01 "price3",

case when now() between p3.begindate and p3.enddate then '+' else '-' end "activ3",

p4.price*0.01 "price4",

case when now() between p4.begindate and p4.enddate then '+' else '-' end "activ4",

sr_min.min_price*0.01 "min_price_restr",

case when now() between sr_min.sincedate and sr_min.tilldate then '+' else '-' end "activ_restr",

sr_max.max_discount*0.01 "max_disc",

case when now() between sr_max.sincedate and sr_max.tilldate then '+' else '-' end "activ_max_disc",

p1.begindate "begindate_price1",

p1.enddate "enddateprice1",

p2.begindate "begindate_price2",

p2.enddate "enddate_price2",

p3.begindate "begindate_price3",

p3.enddate "enddate_price3",

p4.begindate "begindate_price4",

p4.enddate "enddate_price4",

sr_min.sincedate "begindate_min_price_rest",

sr_min.tilldate "enddate_min_price_rest",

sr_max.sincedate "begindate_max_discs",

sr_max.tilldate "enddate_max_discs",

p.lastimporttime

from cg_barcode b

join cg_product p on p.item=b.product_item

left join cg_price p1 on p1.product_item=b.product_item and p1.price_number=1

left join cg_price p2 on p2.product_item=b.product_item and p2.price_number=2

left join cg_price p3 on p3.product_item=b.product_item and p3.price_number=3

left join cg_price p4 on p4.product_item=b.product_item and p4.price_number=4

left join (select min.min_price, sr.sincedate, sr.tilldate, sr.product_marking from loy_cg_sale_restrictions min

    left join loy_cg_sale_restrictions sr on sr.code=min.code and sr.deleted='f') sr_min on sr_min.product_marking=b.product_item

left join (select max.max_discount, sr.sincedate, sr.tilldate, sr.product_marking from loy_cg_sale_restrictions max

    left join loy_cg_sale_restrictions sr on sr.code=max.code and sr.deleted='f') sr_max on sr_max.product_marking=b.product_item

/*

left join loy_cg_sale_restrictions sr on sr.product_marking=b.product_item

left join loy_products_min_price_restriction min on min.code=sr.code

left join loy_products_max_discount_restriction max on max.code=sr.code*/

where b.barcode in ('-ШТРИХ-КОД ТОВАРА-')

select cg_product.item, cg_product.lastimporttime, cg_product.name, cg_price.price_number, 

cg_price.begindate, cg_price.enddate, ROUND (cg_price.price/100.00, 2) as price 

from cg_product LEFT OUTER JOIN cg_price ON (cg_product.item = cg_price.product_item) 

where cg_product.item = '4300043192' -- здесь ввести САП-код товара

group by cg_product.item, cg_product.lastimporttime, cg_product.name, cg_price.price_number, cg_price.begindate, cg_price.enddate, cg_price.price 

order by cg_price.begindate asc, cg_price.price_number desc.product_item)

select * from loy_cg_sale_restrictions where product_marking in (select product_item from cg_barcode where barcode='4607145132038');

SELECT id, plugin_id, plugin_name, name, value, type, class_name, parent_id, action_id  FROM discounts_action_plugin_property 
where action_id='id рекламной акции';

SELECT s.shopindex, s.cashnum, s.numshift, docs.numberfield as nom, type FROM ch_shift s

LEFT JOIN (SELECT id_shift, numberfield, CASE WHEN operationtype=true THEN 'CHECK_P' ELSE 'CHECK_R' END || CASE WHEN checkstatus=1 
THEN '_ANNUL' ELSE '' END as type FROM ch_purchase UNION ALL

SELECT id_shift, numberfield, 'INTRO' FROM ch_introduction UNION ALL

SELECT id_shift, numberfield, 'WITDR' FROM ch_withdrawal UNION ALL

SELECT id_shift, numberfield, CASE WHEN reportz THEN 'Z' ELSE 'X' END FROM ch_reportshift) docs ON docs.id_shift = s.id

WHERE

s.shopindex=161

AND s.cashnum=1

AND s.numshift=17

ORDER BY 1,2,3,4

UPDATE ch_purchase

   SET senttoserverstatus = 0 

 WHERE id_shift in (select id from ch_shift where numshift = 'номер смены') ;

UPDATE ch_withdrawal

   SET senttoserverstatus = 0 

 WHERE id_shift in (select id from ch_shift where numshift = 'номер смены') ;

UPDATE ch_introduction

   SET senttoserverstatus = 0 

 WHERE id_shift in (select id from ch_shift where numshift = 'номер смены') ;

UPDATE ch_reportshift

   SET senttoserverstatus = 0 

 WHERE id_shift in (select id from ch_shift where numshift = 'номер смены') ;

SELECT *   FROM ch_position WHERE id_purchase in (SELECT id  FROM ch_purchase where id_shift 
in (select id from ch_shift where numshift='номер смены' )and numberfield='номер чека') ;

SELECT sum( (priceend * qnty)/1000) from ch_position where id_purchase in (SELECT id FROM ch_purchase where id_shift in
(select id from ch_shift where numshift='номер смены') and numberfield='номер чека');

SELECT sum (checksumend)

  FROM ch_purchase

where id_shift in (select id from ch_shift where numshift='номер смены' ) and checkstatus='0' and operationtype='true';

select 'Ограничение алкоголя ' || id || ' | Время ' || 
coalesce (to_char(time_from, 'HH24:MI:SS'), '-') || ' - ' || 
coalesce (to_char(time_to, 'HH24:MI:SS'), '-') || ' | Срок действия: ' || 
coalesce (to_char(date_from, 'DD.MM.YYYY'), '-') || ' - ' || 
coalesce (to_char(date_to, 'DD.MM.YYYY'), '-') 
as Alcohol_restriction from cg_limits_alcohol order by id asc;

SELECT 'БД: ' || pg_database.datname || ' | размер = ' || pg_size_pretty (pg_database_size(pg_database.datname)) 
AS size FROM pg_database where pg_database.datname not in (SELECT pg_database.datname from pg_database 
where pg_database.datname = 'template0' or pg_database.datname = 'template1')

SELECT 
      *	
    FROM
      pg_stat_activity;

SELECT relname,n_tup_ins,n_tup_upd,n_tup_del from pg_stat_user_tables order by n_tup_ins desc;

SELECT sum(numbackends) FROM pg_stat_database;

SELECT * FROM pg_stat_database;