= Полезные запросы =
{{{
#!access
#allow(TRAC_ADMIN)
* [#point1 Видалення товарів (без руху)]
* [#point2 Не вірні залишки по аналітичним карткам]
* [#point4 Скрипт очистки БД]
* [#point5 Скрипт очистки ЦБД]
* [#point6 Перевірка дублікатів записів у Ревізії]
* [#point7 Сховати товари по яким не було жодного руху]
* [#point8 Проставити останього постачалька в якості основного]
* [#point9 Деактивувати постачальників по яким не було жодного приходу]

== [=#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_doc_records;
set generator gen_t_ao_doc_records_id to 0;
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;
update t_nomen_groups set markup = null where markup is not null;
commit work;

delete from t_kards_ralase;
delete from t_kard_scores;
delete from t_kards;
alter sequence gen_kards_id restart with 0;
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;

delete from t_scale_parameters;
SET GENERATOR GEN_T_SCALE_PARAMETERS_ID TO 0;
delete from t_scales_v1;
SET GENERATOR GEN_T_SCALES_V1_ID TO 0;
commit work;

update t_objects set uuid = gen_uuid(), objects_name = '',
    dpi_code = null, director_contact_id = null;
commit work;
update t_clients set shortname = 'Магазин', name = 'Магазин',
    zkpo = '', ipn = '', numpdv = '', adress = '', phone = '',
    deliv_addr = '', email = '' where (clients_id = 1);
commit work;
delete from t_contacts where contact_id = 1;
commit work;

update t_configs set marker = 'Магазин' where module = 'kassa_labels_top' and config_id = 1;
update t_configs set marker = '' where module = 'kassa_labels_top' and config_id > 1;
update t_configs set marker = '' where module = 'leader' and config_id in (100, 101);
commit work;

update t_sync_steps set ts = '01.01.1990 00:00:00' where sync_proc_name = 'PL_IMPORT_KARDS';
update t_sync_steps set ts = '01.01.1990 00:00:00' where sync_proc_name = 'PL_IMPORT_KARDS_DEL';
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_descriptions;
commit work;

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;

alter trigger t_im_records_bd0 inactive;
delete from t_im_records;
alter sequence gen_t_im_records_id restart with 0;
commit work;
delete from t_intern_movs;
alter sequence gen_t_intern_movs_id restart with 0;
commit work;
alter trigger t_im_records_bd0 active;

delete from t_goods;
commit work;
delete from t_rests;
commit work;

alter sequence gen_t_prices_id restart with 0;
delete from t_prices;
commit work;

delete from t_doc_record_bonuses;
delete from t_doc_records;
delete from t_pays;
delete from t_documents;
delete from t_record_exts;
commit work;

delete from t_doc_records_log;
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_markup_records;
alter sequence gen_t_markup_records_id restart with 0;
commit work;
delete from t_markups;
alter sequence gen_t_markups_id restart with 0;
commit work;

delete from t_ao_records;
delete from t_autoorders;
delete from t_ao_routine_records;
delete from t_ao_routine_objects;
delete from t_ao_routines;
commit work;

delete from t_im_records;
commit work;
delete from t_intern_movs;
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_set_records;
commit work;
delete from t_nomens;
commit work;
delete from t_packs where pack_id > 0;
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_cash_orders;
commit work;
delete from t_cash_order_types where  cash_order_type_id > 0;
commit work;
delete from t_cash_order_groups where  cash_order_group_id > 0;
commit work;

delete from T_MK_ACTIONS where mk_action_id >= 0;
commit work;
delete from T_MKA_RULES where mk_action_id >= 0;
commit work;
delete from t_coupons;
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 = 0;

delete from t_io_logs;
commit work;
delete from t_logs;
commit work;
delete from t_delete_logs;
commit work;
delete from t_r_configs;
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
}}}

== [=#point7] Сховати товари по яким не було жодного руху ==

{{{
update  t_nomens uu
set IS_ACTIVE = 0, IS_VISIBLE = 0
 where uu.nomen_id in (select t.nomen_id from t_nomens  t left join t_goods n on t.nomen_id= n.nomen_id
 where ((IS_ACTIVE = 1)or (IS_VISIBLE =1) ) and goods_id is null)
}}}

== [=#point8] Проставити останнього постачальника в якості основного ==

{{{
update  t_nomens uu
set uu.src_client_id = (select first(1) clients_id
                        from t_goods pp
                        where pp.nomen_id = uu.nomen_id and pp.clients_id > 100 order by pp.goods_id desc)
 where uu.nomen_id in (select distinct t.nomen_id from t_nomens  t join t_goods n on t.nomen_id= n.nomen_id
 where n.clients_id > 100 )
}}}

== [=#point9] Деактивувати постачальників, по яким не було жодного приходу ==

{{{
update t_clients set is_active = 0 where clients_id > 100 and
    clients_id not in
(
select distinct clients_id as id from t_documents where typedoc_id != 11
union
select distinct objects_id as id from t_documents where typedoc_id != 11
 )
}}}

Оптимізація FB: [https://ib-aid.com/en/optimized-firebird-configuration/]

}}}