На сервере

Узнать цену в БД по шк (БД set)

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');

Кол-во актуальных(действующих) рекламных акций (БД set)

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

Кол-во рекламных акций привязанных к магазину (БД set)

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='номер магазина'

Проверка загруженных товаров на весы (БД set)

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

Кол-во актуальных(действующих) рекламных акций на конкретном магазине (БД set)

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;

Посмотреть условия и результат рекламных акций (БД set)

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

Посмотреть перечень отработанных рекламных акций по данным чека (БД set_loyal)

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'));

Список ограничений на товар в БД по ШК (БД set)

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

Вывод списка документов кассовой смены по порядку (БД set_operday)

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

Недостающие номера документов в закрытых сменах (БД set_operday)

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

Добавление заданий на загрузку различных типов данных (товары, кассиры, карты и проч.) с сервера Retail на кассу (БД set)

Не допускается запуск скриптов на Centrum в топологии Centrum-Retail-POS, т.к. это ведёт к некорректной работе сервера и может привести к полной остановке.

После добавления заданий с помощью скрипта перезапустите службу сервера приложений JBOSS_SVC.

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='номер кассы';

Код данных	Тип данных
cards	карты
cashiers	кассиры
counterparty	данные юридических лиц
products	товары
properties	настройки касс для таблицы `sales_management_properties`
loy	рекламные акции
equipment	оборудование
paymenttypemanager	настройки типов оплат
producttypemanager	настройки типов товаров
externalsystemsmanager	настройки внешних систем
limits	ограничения по товарам
image	изображения для купонов
salegroupcommands	группы продаж (работает только при включении настройки `send.salegroups.separately.from.goods`

Для добавления заданий на все кассы следует убрать номер кассы из запроса.

ВАЖНО!

Добавление всего товарного справочника на большое количество касс может привести к созданию огромного количества строк в таблицах un_cg_not_received_to_cash_product . Мы рекомендуем отправлять товарный справочник на кассы поочередно, чтобы не создавать нагрузку на сервер приложений.

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' ;

Не допускается запуск скриптов на Centrum в топологии Centrum-Retail-POS, т.к. это ведёт к некорректной работе сервера и может привести к полной остановке.

Для отправки юридического лица и шаблона касс необходимо использовать скрипт:

На все кассы:

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 = номер кассы;

Добавление заданий на загрузку различных типов данных (товары, кассиры, карты и проч.) с Centrum на Retail (БД set)

После добавления заданий с помощью скрипта перезапустите службу сервера приложений JBOSS_SVC.

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

Тип данных	Название
coupons	купоны
users	пользователи
cashiers	кассиры
cash	список касс
equipment	оборудование
externalsystemsmanager	внешние системы
products	группы продаж
loy	рекламные акции
cards	карты
image	изображения для купонов
producttypemanager	настройки типов товаров https://crystals.atlassian.net/browse/SRTS-2776 https://crystals.atlassian.net/browse/CR-10853 10.4.6.0 Данный тип товаров отправляется только при включенной настройке `is.send.product.types.to.shops.enabled = true`. По умолчанию настройка `false`. Настройка находится на сервере SetRetail/SetCentrum в базе данных set.
pricetagsFormats	форматы шаблонов ценников
directivePrinting	расписание директивной печати
pricetags	шаблоны ценников
counterparty	данные юридических лиц
properties	настройки из таблицы `sales_management_properties`
limits	ограничения по товарам

Пример:

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

Добавление заданий для всех ТК

После добавления заданий с помощью скрипта перезапустите службу сервера приложений JBOSS_SVC

Пример:

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;

Просмотр позиций по всем чекам за определенный ОД (БД set)

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;

Просмотр позиций по заданным чекам (БД set)

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;

Проверка заданий на кассы (БД set)

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

Проверка дублированных чеков на сервере и выгрузка одной из их копий (БД set)

--Поиск дублированных чеков (наблюдается до версии 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)

Поиск чеков для ERP (БД set)

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

Поиск отсутствующих чеков на Centrum за опердень (БД set)

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);

Просмотр расхождений в суммах z-отчета и чеков в ERP за опердень (БД set)

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;

Выявить расхождения по оплатам (БД set_operday)

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

Показать все чеки за смену (БД set_operday)

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

Показать проданные подарочные карты (БД set_operday )

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;

Показать отоваренные подарочные карты (БД set_operday )

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;

Показать все чеки не попавшие в ФР,но сохранившиесся в БД (БД set_operday)

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 ;

Показать расхождения по суммам z-отчета и чеков в рамках смены (БД set_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))

Вывести суммы оплат за смену (БД set_operday)

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 = 'номер кассы'

Просмотр чеков, по ШК (БД set_operday)

SELECT *

FROM od_purchase 

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

Переотправить юридическое лицо и шаблон кассы (БД set)

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  -- номер кассы - раскомментировать, если требуется отправка на конкретную кассу
;

На кассе

Узнать цену в БД по шк (БД catalog)

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

Узнать полную информацию по ценам в БД по шк (БД catalog)

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 ('-ШТРИХ-КОД ТОВАРА-')

Узнать инфо о ценах в БД по коду товара (БД catalog)

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)

Список ограничений товар в БД по ШК (БД catalog)

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

Посмотреть условия и результат рекламных акций (БД discount)

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

Вывод списка документов кассовой смены по порядку (БД cash)

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

Полезные SQL-скрипты