-- todo 9730

-- correct current data from database

update customerrentprice cr
set cr.invoicefrequencynumber = 10
where cr.invoicefrequencynumber = 0
and cr.typeofrent != 8
and not exists (select 1
               from customerrentprice cr2
               where cr2.productlistpercustomer_id = cr.productlistpercustomer_id
               and cr2.startdate = cr.startdate
               and cr2.typeofrent = cr.typeofrent
               and cr2.invoicefrequencynumber = 10);

commit;


update generalrentprice gr
set gr.invoicefrequencynumber = 10
where gr.invoicefrequencynumber = 0
and gr.typeofrent != 8
and not exists (select 1
               from generalrentprice gr2
               where gr2.generalpricelist_id = gr.generalpricelist_id
               and gr2.product_id = gr.product_id
               and gr2.sizedefinition_id = gr.sizedefinition_id
               and gr2.finishingmethod_id = gr.finishingmethod_id
               and gr2.startdate = gr.startdate
               and gr2.typeofrent = gr.typeofrent
               and gr2.invoicefrequencynumber = 10);

commit;


-- add 2 new DBCONSISTENCYCHECKS

     insert into DBCONSISTENCYCHECK
      (DBCONSISTENCYCHECK_ID,
       DESCRIPTION,
       SQLSTATEMENT,
       ERRORMESSAGE,
       EXACTLYONEROW,
       ADDITIONALERRORMESSAGE,
       ACTIVE)
    values
      (61,
       'Check if there are duplicate  rent prices defined in a general price list',
       'select g.code pricelist,
       p.code product,
       s.code sizecode,
       f.code finmethod,
       (select i.factorydescription from invoicefrequency i where i.invoicefrequencynumber = gr.invoicefrequencynumber) pricefrequency,
       gr.changesperweek,
       gr.startdate,
       gr.price,
       gr.timestamp,
       (select su.username from systemuser su where su.systemuser_id = gr.systemuser_id) systemuser,
       gr.systemuser_id,
       gr.generalrentprice_id,
       gr.generalpricelist_id,
       gr.product_id,
       gr.sizedefinition_id,
       gr.finishingmethod_id,
       gr.typeofrent,
       gr.invoicefrequencynumber
       from generalrentprice gr,
       generalpricelist g,
       product p,
       sizedefinition s,
       finishingmethod f 
       where g.generalpricelist_id = gr.generalpricelist_id
       and p.product_id = gr.product_id
       and s.sizedefinition_id = gr.sizedefinition_id
       and f.finishingmethod_id = gr.finishingmethod_id
       and gr.typeofrent <> 8
       and exists (select 1
       from generalrentprice gr1
       where gr1.generalpricelist_id = gr.generalpricelist_id
       and gr1.product_id = gr.product_id
       and gr1.sizedefinition_id = gr.sizedefinition_id
       and gr1.finishingmethod_id = gr.finishingmethod_id
       and gr1.startdate = gr.startdate
       and gr1.typeofrent = gr.typeofrent
       and gr1.changesperweek = gr.changesperweek
        and gr1.invoicefrequencynumber <> gr.invoicefrequencynumber
        and gr1.generalrentprice_id <> gr.generalrentprice_id)
        order by gr.generalpricelist_id,
        gr.product_id,
        gr.sizedefinition_id,
        gr.finishingmethod_id,
        gr.typeofrent,
        gr.startdate,
        gr.generalrentprice_id',
        'There are duplicate rent prices defined for a general price list!',
        'N',
        'They need to be corrected manually!',
        'Y');

    insert into DBCONSISTENCYCHECK
      (DBCONSISTENCYCHECK_ID,
       DESCRIPTION,
       SQLSTATEMENT,
       ERRORMESSAGE,
       EXACTLYONEROW,
       ADDITIONALERRORMESSAGE,
       ACTIVE)
    values
      (62,
       'Check if there are duplicate  customer rent prices defined for a customer',
       'select cu.customernumber customer,
       p.code product,
       s.code sizecode,
       f.code finmethod,
       (select i.factorydescription from invoicefrequency i where i.invoicefrequencynumber = cr.invoicefrequencynumber) pricefrequency,
       cr.changesperweek,
       cr.startdate,
       cr.price,
       cr.timestamp,
       (select su.username from systemuser su where su.systemuser_id = cr.systemuser_id) systemuser,
       cr.systemuser_id,
       cr.customerrentprice_id,
       cr.productlistpercustomer_id,
       plpc.customer_id,
       plpc.product_id,
       plpc.sizedefinition_id,
       plpc.finishingmethod_id,
       cr.typeofrent,
       cr.invoicefrequencynumber
       from customerrentprice cr,
       productlistpercustomer plpc,
       product p,
       sizedefinition s,
       finishingmethod f,
       customer cu
       where plpc.productlistpercustomer_id = cr.productlistpercustomer_id
       and p.product_id = plpc.product_id
       and s.sizedefinition_id = plpc.sizedefinition_id
       and f.finishingmethod_id = plpc.finishingmethod_id
       and cu.customer_id = plpc.customer_id
       and cr.typeofrent <> 8
       and exists (select 1
       from customerrentprice cr1
       where cr1.productlistpercustomer_id = cr.productlistpercustomer_id
       and cr1.startdate = cr.startdate
       and cr1.typeofrent = cr.typeofrent
       and cr1.changesperweek = cr.changesperweek
       and cr1.invoicefrequencynumber <> cr.invoicefrequencynumber
       and cr1.customerrentprice_id <> cr.customerrentprice_id)
       order by plpc.productlistpercustomer_id,
       plpc.product_id,
       plpc.sizedefinition_id,
       plpc.finishingmethod_id,
       cr.typeofrent,
       cr.startdate,
       cr.customerrentprice_id',
       'There are duplicate rent prices defined for a customer!',
       'N',
       'They need to be corrected manually!',
       'Y');
