Changes between Initial Version and Version 1 of usefulQueries

Timestamp:

06/01/2018 11:54:08 PM (7 years ago)

Author:

sokol

Comment:

--

usefulQueries

@@ +1 @@
+= Полезные запросы =
+{{{
+#!access
+#allow(TRAC_ADMIN)
+* [#point1 Видалення товарів (без руху)]
+* [#point2 Не вірні залишки по аналітичним карткам]
+* [#point4 Скрипт очистки БД]
+* [#point5 Скрипт очистки ЦБД]
+* [#point6 Перевірка дублікатів записів у Ревізії]
+
+== [=#point1]Видалення товарів (без руху) ==
+Товаром без движения будем вважати той, на який не заведено жодної аналітичної картки (приходу). Наступна послідовність запитів, дозволить позбутись таких товарів:
+{{{
+#!text/x-sql
+delete from t_rests n where n.nomen_id not in (select distinct nomen_id from t_goods g )
+delete from t_nomen_bars n where n.nomen_id not in (select distinct nomen_id from t_goods g )
+delete from t_price_logs n where n.nomen_id not in (select distinct nomen_id from t_goods g )
+delete from t_markup_records n where n.nomen_id not in (select distinct nomen_id from t_goods g )
+delete from t_ao_records n where n.aor_nomen_id not in (select distinct nomen_id from t_goods g )
+delete from t_rev_records n where n.nomen_id not in (select distinct nomen_id from t_goods g )
+delete from t_nomens n where n.nomen_id not in (select distinct nomen_id from t_goods g )
+delete from t_barcodes b where b.barcode_id not in (select distinct barcode_id from t_nomen_bars)
+}}}
+ну або скриптом:
+{{{
+#!text/x-sql
+delete from t_rests n where n.nomen_id not in (select distinct nomen_id from t_goods g ) ;
+commit work;
+delete from t_nomen_bars n where n.nomen_id not in (select distinct nomen_id from t_goods g );
+commit work;
+delete from t_price_logs n where n.nomen_id not in (select distinct nomen_id from t_goods g );
+commit work;
+delete from t_markup_records n where n.nomen_id not in (select distinct nomen_id from t_goods g );
+commit work;
+delete from t_ao_records n where n.aor_nomen_id not in (select distinct nomen_id from t_goods g );
+commit work;
+delete from t_rev_records n where n.nomen_id not in (select distinct nomen_id from t_goods g );
+commit work;
+delete from t_nomens n where n.nomen_id not in (select distinct nomen_id from t_goods g );
+commit work;
+delete from t_barcodes b where b.barcode_id not in (select distinct barcode_id from t_nomen_bars);
+commit work;
+}}}
+
+== [=#point2]Не вірні залишки по аналітичним карткам ==
+При нормальних залишках в БД різниця всіх приходів та розходів по кожній аналітичній картці в точності співпадає із залишком по кожній з аналитичних карток. Якщо останній стовпчик в виборці не рівний нулю, то має місце проблема з залишками по цій аналітичній картці. 
+{{{
+#!text/x-sql
+select aa.ogoods_id,
+        sum(aa.orest) as orest,
+        sum(aa.oprihod) as oprihod,
+        sum(aa.orashod) as orashod,
+        sum(aa.oprihod) - sum(aa.orashod) - sum(aa.orest) as orizn from
+(
+  select g.goods_id as ogoods_id,
+         g.goods_rest as orest,
+         0.0 as oprihod,
+         0.0 as orashod
+          from t_goods g
+union all
+  select    dg.goods_id as ogoods_id,
+            0.0 as orest,
+            0.0 as oprihod,
+            dg.kilk as orashod
+             from t_documents d left join t_doc_records dr on (d.document_id = dr.document_id and d.objects_id = 1)
+                    left join t_doc_goods dg on (dr.docrec_id = dg.docrec_id)
+union all
+  select    dg.goods_id as ogoods_id,
+            0.0 as orest,
+            dg.kilk as oprihod,
+            0.0 as orashod
+             from t_documents d left join t_doc_records dr on (d.document_id = dr.document_id and d.clients_id = 1)
+                    left join t_doc_goods dg on (dr.docrec_id = dg.docrec_id)
+
+) aa
+group by aa.ogoods_id
+}}}
+
+Аналогічне справедливо і для залишку по товару та записів в документах (без врахування аналітичних карток):
+{{{
+#!text/x-sql
+select aa.onomen_id,
+        sum(aa.orest) as orest,
+        sum(aa.oprihod) as oprihod,
+        sum(aa.orashod) as orashod,
+        sum(aa.oprihod) - sum(aa.orashod) - sum(aa.orest) as orizn from
+(
+  select r.nomen_id as onomen_id,
+         r.rest as orest,
+         0.0 as oprihod,
+         0.0 as orashod
+          from t_rests r
+union all
+  select    dr.nomen_id as onomen_id,
+            0.0 as orest,
+            0.0 as oprihod,
+            dr.kilk as orashod
+             from t_documents d left join t_doc_records dr on (d.document_id = dr.document_id and d.objects_id = 1)
+union all
+  select    dr.nomen_id as onomen_id,
+            0.0 as orest,
+            dr.kilk as oprihod,
+            0.0 as orashod
+             from t_documents d left join t_doc_records dr on (d.document_id = dr.document_id and d.clients_id = 1)
+
+) aa
+group by aa.onomen_id
+}}}
+
+Іще один варіант, при якому сумарна кількість по t_goods не відповідає залишку в t_rests. Після виконання наступного запиту, різниця в останньому стовпчику має бути нульовою:
+{{{
+#!text/x-sql
+select nomen_id, sum(orst_rest), sum(ogoods_rest), sum(orst_rest)-sum(ogoods_rest) from
+(
+select r.nomen_id, r.rest as orst_rest, 0 as ogoods_rest from t_rests r
+
+union all
+
+select g.nomen_id, 0 as orst_res, sum(g.goods_rest) as ogoods_rest from t_goods g
+    group by g.nomen_id
+)
+group by nomen_id
+}}}
+
+== [=#point4]Скрипт очистки БД ==
+Загальний скрипт очищення бази (не зачіпає користувачів, клієнтів, дисконтників та товари) виглядає так:
+{{{
+#!text/x-sql
+SET SQL DIALECT 3;
+
+delete from t_document_autoorder;
+delete from t_ao_records;
+alter sequence gen_ao_record_id restart with 0;
+delete from t_autoorders;
+alter sequence gen_autoorder_id restart with 0;
+commit work;
+
+delete from t_rev_records;
+alter sequence gen_rev_records_id restart with 0;
+delete from t_revisions;
+alter sequence gen_revision_id restart with 0;
+commit work;
+
+alter trigger docgoods_ad0 inactive;
+delete from t_doc_goods;
+alter trigger docgoods_ad0 active;
+alter sequence gen_docgoods_id restart with 0;
+commit work;
+
+alter trigger docrec_bd0 inactive;
+alter trigger docrec_ad4 inactive;
+alter trigger t_record_exts_ad0 inactive;
+delete from t_record_exts;
+alter trigger t_record_exts_ad0 active;
+delete from t_doc_records;
+alter trigger docrec_ad4 active;
+alter trigger docrec_bd0 active;
+alter sequence gen_docrec_id restart with 0;
+commit work;
+
+
+alter trigger GOODS_AD0 inactive;
+alter trigger T_GOODS_AD0 inactive;
+delete from t_goods;
+alter sequence gen_goods_id restart with 0;
+alter trigger GOODS_AD0 active;
+alter trigger T_GOODS_AD0 active;
+commit work;
+update t_rests set rest = 0.0, rest_sum = 0.0;
+commit work;
+
+delete from t_tax_correctings;
+alter sequence gen_t_tax_correctings_id restart with 0;
+delete from t_taxes;
+alter sequence reestr_gen_id restart with 0;
+commit work;
+
+alter trigger t_pays_ad1 inactive;
+alter trigger t_pays_ad0 inactive;
+delete from t_pays;
+alter trigger t_pays_ad1 active;
+alter trigger t_pays_ad0 active;
+alter sequence gen_t_pays_id restart with 0;
+commit work;
+
+update t_documents set prew_document_id = null where prew_document_id is not null;
+alter trigger documents_bd0 inactive;
+alter trigger documents_bd2 inactive;
+alter trigger documents_bd3 inactive;
+alter trigger documents_ad4 inactive;
+delete from t_documents;
+alter trigger documents_bd0 active;
+alter trigger documents_bd2 active;
+alter trigger documents_bd3 active;
+alter trigger documents_ad4 active;
+alter sequence gen_documents_id restart with 0;
+commit work;
+
+delete from t_price_logs;
+delete from t_del_docrecs;
+delete from t_del_documents;
+delete from docrec_del;
+delete from docrec_log;
+delete from docrec_tmp;
+delete from doc_del;
+delete from t_doc_records_log;
+alter sequence gen_t_doc_records_log_id restart with 0;
+commit work;
+
+delete from t_markup_records;
+delete from t_markups;
+commit work;
+
+delete from t_delete_logs;
+commit work;
+
+delete from t_io_logs;
+delete from t_z_reports;
+delete from t_nomens_log;
+commit work;
+
+delete from t_abc_lists;
+delete from t_logs;
+delete from t_nomen_bars_log;
+commit work;
+
+update t_objects set uuid = gen_uuid();
+commit work;
+}}}
+Очистити товари можна, виконавши наступний блок:
+{{{
+#!text/x-sql
+execute block
+as
+declare variable TNOMEN_ID integer;
+declare variable TCNT integer;
+begin
+    for select n.nomen_id from t_nomens n
+              -- тут, за необхідності, може бути довыльна умова
+            into tnomen_id
+    do begin
+      select count(ocount) from PS_NOMEN_DEL(:tnomen_id) into :tcnt;
+
+      delete from t_nomens_log where nomen_id = :tnomen_id;
+      delete from t_nomen_bars_log where nomen_id = :tnomen_id;
+    end
+end
+}}}
+якщо в цей блок вставити певну умову (наприклад шаблон імен, чи приналежності до акцизу) то можна видаляти товар вибірково.
+
+== [=#point5]Скрипт очистки ЦБД ==
+Повне очищення центральної БД:
+{{{
+#!text/x-sql
+SET SQL DIALECT 3;
+
+SET NAMES WIN1251;
+
+delete from t_abc_lists;
+commit work;
+
+delete from t_ao_records;
+commit work;
+alter sequence gen_t_ao_records_id restart with 0;
+commit work;
+delete from t_autoorders;
+commit work;
+alter sequence gen_t_autoorders_id restart with 0;
+commit work;
+
+delete from t_goods;
+commit work;
+delete from t_rests;
+commit work;
+
+delete from t_doc_record_bonuses;
+delete from t_doc_records;
+delete from t_pays;
+delete from t_documents;
+commit work;
+
+delete from t_kards_ralase;
+delete from t_kards;
+delete from t_kard_types where kard_type_id > 0;
+commit work;
+
+delete from t_nomen_links;
+commit work;
+delete from t_nomen_bars;
+commit work;
+delete from t_nomen_bars_log;
+commit work;
+delete from t_recipe_records;
+commit work;
+delete from t_recipes;
+commit work;
+delete from t_nomens;
+commit work;
+delete from t_makers where maker_id > 0;
+commit work;
+delete from t_indicatives where indicative_id > 0;
+commit work;
+delete from t_price_logs;
+commit work;
+
+delete from T_ACCOUNTS;
+alter sequence gen_clientaccount_id restart with 0;
+commit work;
+delete from t_contacts;
+alter sequence gen_t_contacts_id restart with 0;
+commit work;
+delete from t_client_links;
+commit work;
+delete from t_objects where objects_id > 0;
+        --  T_CLIENTS_BD0 not active
+delete from t_clients where clients_id > 100 and clients_id < 1000000;
+        --  T_CLIENTS_BD0 active
+commit work;
+
+-- T_USERS_BD0 off
+delete from t_users where user_id > 2;
+-- T_USERS_BD0 on
+commit work;
+
+update t_objects set uuid = GEN_UUID() where objects_id = 1000000;
+
+delete from t_delete_logs;
+commit work;
+}}}
+
+== [=#point6]Перевірка дублікатів записів у Ревізії ==
+
+{{{
+select rr.nomen_id, count(1) from t_revisions r join t_rev_records rr on r.revision_id = rr.revision_id
+        where r.revision_date = current_date - 1
+    group by rr.nomen_id
+}}}
+
+}}}