insert into master_app_production.code_types set id=65, disp_name="締め区分", j_name="締め区分", e_name="cutoff type", invalid_flag_code=0, created_at=NOW(), updated_at=NOW();
insert into master_app_production.code_masters set code_type_id=65, code_number=1, disp_name="月1回締め", disp_sort=1, j_name="月1回締め", e_name="", note="", invalid_flag_code=0, created_at=NOW(), updated_at=NOW();
insert into master_app_production.code_masters set code_type_id=65, code_number=2, disp_name="月2回締め", disp_sort=2, j_name="月2回締め", e_name="", note="", invalid_flag_code=0, created_at=NOW(), updated_at=NOW();
insert into master_app_production.code_masters set code_type_id=65, code_number=3, disp_name="月3回締め", disp_sort=3, j_name="月3回締め", e_name="", note="", invalid_flag_code=0, created_at=NOW(), updated_at=NOW();
insert into master_app_production.code_masters set code_type_id=65, code_number=4, disp_name="週締め",    disp_sort=4, j_name="週締め", e_name="", note="", invalid_flag_code=0, created_at=NOW(), updated_at=NOW();
insert into master_app_production.code_masters set code_type_id=65, code_number=5, disp_name="毎日締め", disp_sort=5, j_name="毎日締め", e_name="", note="", invalid_flag_code=0, created_at=NOW(), updated_at=NOW();
insert into master_app_production.code_masters set code_type_id=65, code_number=0, disp_name="都度払い", disp_sort=6, j_name="都度払い", e_name="", note="", invalid_flag_code=0, created_at=NOW(), updated_at=NOW();

insert into master_app_production.code_types set id=66, disp_name="締日", j_name="締日", e_name="cutoff day", invalid_flag_code=0, created_at=NOW(), updated_at=NOW();
insert into master_app_production.code_masters set code_type_id=66, code_number=101, disp_name="日曜日", disp_sort=1, j_name="日曜日", e_name="", note="", invalid_flag_code=0, created_at=NOW(), updated_at=NOW();
insert into master_app_production.code_masters set code_type_id=66, code_number=102, disp_name="月曜日", disp_sort=2, j_name="月曜日", e_name="", note="", invalid_flag_code=0, created_at=NOW(), updated_at=NOW();
insert into master_app_production.code_masters set code_type_id=66, code_number=103, disp_name="火曜日", disp_sort=3, j_name="火曜日", e_name="", note="", invalid_flag_code=0, created_at=NOW(), updated_at=NOW();
insert into master_app_production.code_masters set code_type_id=66, code_number=104, disp_name="水曜日", disp_sort=4, j_name="水曜日", e_name="", note="", invalid_flag_code=0, created_at=NOW(), updated_at=NOW();
insert into master_app_production.code_masters set code_type_id=66, code_number=105, disp_name="木曜日", disp_sort=5, j_name="木曜日", e_name="", note="", invalid_flag_code=0, created_at=NOW(), updated_at=NOW();
insert into master_app_production.code_masters set code_type_id=66, code_number=106, disp_name="金曜日", disp_sort=6, j_name="金曜日", e_name="", note="", invalid_flag_code=0, created_at=NOW(), updated_at=NOW();
insert into master_app_production.code_masters set code_type_id=66, code_number=107, disp_name="土曜日", disp_sort=7, j_name="土曜日", e_name="", note="", invalid_flag_code=0, created_at=NOW(), updated_at=NOW();
insert into master_app_production.code_masters set code_type_id=66, code_number=5,  disp_name="5日", disp_sort=8, j_name="5日", e_name="", note="", invalid_flag_code=0, created_at=NOW(), updated_at=NOW();
insert into master_app_production.code_masters set code_type_id=66, code_number=10, disp_name="10日", disp_sort=9, j_name="10日", e_name="", note="", invalid_flag_code=0, created_at=NOW(), updated_at=NOW();
insert into master_app_production.code_masters set code_type_id=66, code_number=15, disp_name="15日", disp_sort=10, j_name="15日", e_name="", note="", invalid_flag_code=0, created_at=NOW(), updated_at=NOW();
insert into master_app_production.code_masters set code_type_id=66, code_number=20, disp_name="20日", disp_sort=11, j_name="20日", e_name="", note="", invalid_flag_code=0, created_at=NOW(), updated_at=NOW();
insert into master_app_production.code_masters set code_type_id=66, code_number=25, disp_name="25日", disp_sort=12, j_name="25日", e_name="", note="", invalid_flag_code=0, created_at=NOW(), updated_at=NOW();
insert into master_app_production.code_masters set code_type_id=66, code_number=99, disp_name="末日", disp_sort=13, j_name="末日", e_name="", note="", invalid_flag_code=0, created_at=NOW(), updated_at=NOW();

insert into master_app_production.code_types set id=67, disp_name="売掛金支払条件対象", j_name="売掛金支払条件対象", e_name="customer payment target", invalid_flag_code=0, created_at=NOW(), updated_at=NOW();
insert into master_app_production.code_masters set code_type_id=67, code_number=1, disp_name="得意先", disp_sort=1, j_name="得意先", e_name="", note="", invalid_flag_code=0, created_at=NOW(), updated_at=NOW();
insert into master_app_production.code_masters set code_type_id=67, code_number=2, disp_name="得意先グループ", disp_sort=2, j_name="得意先グループ", e_name="", note="", invalid_flag_code=0, created_at=NOW(), updated_at=NOW();

insert into master_app_production.code_types set id=68, disp_name="買掛金支払条件対象", j_name="買掛金支払条件対象", e_name="supplier payment target", invalid_flag_code=0, created_at=NOW(), updated_at=NOW();
insert into master_app_production.code_masters set code_type_id=68, code_number=1, disp_name="仕入先", disp_sort=1, j_name="仕入先", e_name="", note="", invalid_flag_code=0, created_at=NOW(), updated_at=NOW();
insert into master_app_production.code_masters set code_type_id=68, code_number=2, disp_name="仕入先グループ", disp_sort=2, j_name="仕入先グループ", e_name="", note="", invalid_flag_code=0, created_at=NOW(), updated_at=NOW();

#得意先
alter table master_app_production.customers add bill_3_site int(11) after attention_fax;
alter table master_app_production.customers add payment_3_site int(11) after attention_fax;
alter table master_app_production.customers add payment_type_3_code smallint(6) after attention_fax;
alter table master_app_production.customers add payment_condition_3_code smallint(6) after attention_fax;
alter table master_app_production.customers add payment_condition_3_price int(11) after attention_fax;

alter table master_app_production.customers add bill_2_site int(11) after attention_fax;
alter table master_app_production.customers add payment_2_site int(11) after attention_fax;
alter table master_app_production.customers add payment_type_2_code smallint(6) after attention_fax;
alter table master_app_production.customers add payment_condition_2_code smallint(6) after attention_fax;
alter table master_app_production.customers add payment_condition_2_price int(11) after attention_fax;

alter table master_app_production.customers add bill_1_site int(11) after attention_fax;
alter table master_app_production.customers add payment_1_site int(11) after attention_fax;
alter table master_app_production.customers add payment_type_1_code smallint(6) after attention_fax;

#カンマ区切りで複数回の締めに対応するため、varchar型とする。
alter table master_app_production.customers add cutoff_day varchar(40) after attention_fax;
alter table master_app_production.customers add cutoff_type_code smallint(6) after attention_fax;

#仕入先
alter table master_app_production.suppliers add bill_3_site int(11) after attention_fax;
alter table master_app_production.suppliers add payment_3_site int(11) after attention_fax;
alter table master_app_production.suppliers add payment_type_3_code smallint(6) after attention_fax;
alter table master_app_production.suppliers add payment_condition_3_code smallint(6) after attention_fax;
alter table master_app_production.suppliers add payment_condition_3_price int(11) after attention_fax;

alter table master_app_production.suppliers add bill_2_site int(11) after attention_fax;
alter table master_app_production.suppliers add payment_2_site int(11) after attention_fax;
alter table master_app_production.suppliers add payment_type_2_code smallint(6) after attention_fax;
alter table master_app_production.suppliers add payment_condition_2_code smallint(6) after attention_fax;
alter table master_app_production.suppliers add payment_condition_2_price int(11) after attention_fax;

alter table master_app_production.suppliers add bill_1_site int(11) after attention_fax;
alter table master_app_production.suppliers add payment_1_site int(11) after attention_fax;
alter table master_app_production.suppliers add payment_type_1_code smallint(6) after attention_fax;

#カンマ区切りで複数回の締めに対応するため、varchar型とする。
alter table master_app_production.suppliers add cutoff_day varchar(40) after attention_fax;
alter table master_app_production.suppliers add cutoff_type_code smallint(6) after attention_fax;

#得意先グループ別支払方法／仕入先グループ別支払方法をくび
update menus set invalid_flag_code=1 where name IN ('customer_group_payments', 'supplier_group_payments');



#-----------------------glan DB変更--------------------------------------------------
#salesとpurchasesにform_cutoff_dateとcutoff_type_codeを追加する。
alter table sales add form_cutoff_date date after price_fraction_method_code;
alter table sales add cutoff_type_code smallint(6) after price_fraction_method_code;

alter table purchases add form_cutoff_date date after price_fraction_method_code;
alter table purchases add cutoff_type_code smallint(6) after price_fraction_method_code;

alter table receivings add form_cutoff_date date after customer_group_id;
alter table receivings add cutoff_type_code smallint(6) after customer_group_id;

alter table payings add form_cutoff_date date after supplier_group_id;
alter table payings add cutoff_type_code smallint(6) after supplier_group_id;

alter table receivable_carry_remains add form_cutoff_date date after price_fraction_method_code;
alter table receivable_carry_remains add cutoff_type_code smallint(6) after price_fraction_method_code;

alter table payable_carry_remains add form_cutoff_date date after price_fraction_method_code;
alter table payable_carry_remains add cutoff_type_code smallint(6) after price_fraction_method_code;

#sales, purchases, receivings, payings, receivable_carry_remains, payable_carry_remainsにcutoff_dayを追加する。
alter table sales add cutoff_day varchar(40) after cutoff_type_code;
alter table purchases add cutoff_day varchar(40) after cutoff_type_code;
alter table receivings add cutoff_day varchar(40) after cutoff_type_code;
alter table payings add cutoff_day varchar(40) after cutoff_type_code;
alter table receivable_carry_remains add cutoff_day varchar(40) after cutoff_type_code;
alter table payable_carry_remains add cutoff_day varchar(40) after cutoff_type_code;

#customer_ledgers, supplier_ledgersにcutoff_type_codeとcutoff_dayを追加する
alter table customer_ledgers add cutoff_type_code smallint(6) after cutoff_date_code;
alter table customer_ledgers add cutoff_day varchar(40) after cutoff_type_code;
alter table supplier_ledgers add cutoff_type_code smallint(6) after cutoff_date_code;
alter table supplier_ledgers add cutoff_day varchar(40) after cutoff_type_code;

#receivables/payablesにcutoff_type_codeとcutoff_dayを追加する
alter table receivables add cutoff_type_code smallint(6) after customer_group_id;
alter table receivables add cutoff_day varchar(40) after cutoff_type_code;
alter table payables add cutoff_type_code smallint(6) after supplier_group_id;
alter table payables add cutoff_day varchar(40) after cutoff_type_code;


#-------------------------------------------------マスターデータ変換---------------------------------------------
#customersテーブルとsuppliersテーブルの締め支払方法をgroupとgroup_paymentsからコピーする。
#締め日
UPDATE master_app_production.customers SET cutoff_type_code=NULL, cutoff_day="";
UPDATE master_app_production.customers as a
        LEFT JOIN master_app_production.customer_groups as b on a.charge_customer_group_id=b.id
SET a.cutoff_type_code=1, a.cutoff_day=CAST(b.cutoff_date_code AS CHAR) WHERE b.cutoff_date_code > 0;
#支払方法1
UPDATE master_app_production.customers SET payment_type_1_code=NULL, payment_1_site=NULL, bill_1_site=NULL;
UPDATE master_app_production.customers as a
        LEFT JOIN (
          SELECT *, 
            IF(cutoff_date_code=99,30,IFNULL(cutoff_date_code,0)) AS cutoff_date 
          FROM master_app_production.customer_groups 
        ) AS b ON a.charge_customer_group_id=b.id
        LEFT JOIN (
          SELECT *, 
            IF(payment_date_code=99,30,IFNULL(payment_date_code,0)) AS payment_date,
            IF(encach_date_code=99, 30,IFNULL(encach_date_code,0)) AS encach_date
          FROM master_app_production.customer_group_payments WHERE payment_condition_price is null or payment_condition_price=0 GROUP BY customer_group_id
        ) AS c ON a.charge_customer_group_id=c.customer_group_id
SET 
  payment_type_1_code = c.payment_type_code, 
  payment_1_site = IF(c.payment_month_code > 0, 
                      (30-b.cutoff_date) + (c.payment_month_code-1)*30 + c.payment_date,
                      c.payment_date - b.cutoff_date
                   ),
  bill_1_site = IF(c.payment_type_code=2,
                    IF(c.encach_month_code > 0,
                       (30-c.payment_date) + (c.encach_month_code-1)*30 + c.encach_date,
                       c.encach_date - c.payment_date
                    )
                 , NULL)
WHERE a.cutoff_type_code > 0;
#支払方法2
UPDATE master_app_production.customers SET payment_type_2_code=NULL, payment_2_site=NULL, bill_2_site=NULL, payment_condition_2_price=NULL, payment_condition_2_code=NULL;
UPDATE master_app_production.customers as a
        LEFT JOIN (
          SELECT *, 
            IF(cutoff_date_code=99,30,IFNULL(cutoff_date_code,0)) AS cutoff_date 
          FROM master_app_production.customer_groups 
        ) AS b ON a.charge_customer_group_id=b.id
        LEFT JOIN (
          SELECT *, 
            IF(payment_date_code=99,30,IFNULL(payment_date_code,0)) AS payment_date,
            IF(encach_date_code=99, 30,IFNULL(encach_date_code,0)) AS encach_date
          FROM master_app_production.customer_group_payments WHERE payment_condition_price > 0 GROUP BY customer_group_id
        ) AS c ON a.charge_customer_group_id=c.customer_group_id
SET 
  payment_condition_2_price = c.payment_condition_price,
  payment_condition_2_code = c.payment_condition_code,
  payment_type_2_code = c.payment_type_code, 
  payment_2_site = IF(c.payment_month_code > 0, 
                      (30-b.cutoff_date) + (c.payment_month_code-1)*30 + c.payment_date,
                      c.payment_date - b.cutoff_date
                   ),
  bill_2_site = IF(c.payment_type_code=2,
                    IF(c.encach_month_code > 0,
                       (30-c.payment_date) + (c.encach_month_code-1)*30 + c.encach_date,
                       c.encach_date - c.payment_date
                    )
                 , NULL)
WHERE a.cutoff_type_code > 0;

#締め日
UPDATE master_app_production.suppliers SET cutoff_type_code=NULL, cutoff_day="";
UPDATE master_app_production.suppliers as a
        LEFT JOIN master_app_production.supplier_groups as b on a.payto_supplier_group_id=b.id
SET a.cutoff_type_code=1, a.cutoff_day=CAST(b.cutoff_date_code AS CHAR) WHERE b.cutoff_date_code > 0;
#支払方法1
UPDATE master_app_production.suppliers SET payment_type_1_code=NULL, payment_1_site=NULL, bill_1_site=NULL;
UPDATE master_app_production.suppliers as a
        LEFT JOIN (
          SELECT *, 
            IF(cutoff_date_code=99,30,IFNULL(cutoff_date_code,0)) AS cutoff_date 
          FROM master_app_production.supplier_groups 
        ) AS b ON a.payto_supplier_group_id=b.id
        LEFT JOIN (
          SELECT *, 
            IF(payment_date_code=99,30,IFNULL(payment_date_code,0)) AS payment_date,
            IF(encach_date_code=99, 30,IFNULL(encach_date_code,0)) AS encach_date
          FROM master_app_production.supplier_group_payments WHERE payment_condition_price is null or payment_condition_price=0 GROUP BY supplier_group_id
        ) AS c ON a.payto_supplier_group_id=c.supplier_group_id
SET 
  payment_type_1_code = c.payment_type_code, 
  payment_1_site = IF(c.payment_month_code > 0, 
                      (30-b.cutoff_date) + (c.payment_month_code-1)*30 + c.payment_date,
                      c.payment_date - b.cutoff_date
                   ),
  bill_1_site = IF(c.payment_type_code=2,
                    IF(c.encach_month_code > 0,
                       (30-c.payment_date) + (c.encach_month_code-1)*30 + c.encach_date,
                       c.encach_date - c.payment_date
                    )
                 , NULL)
WHERE a.cutoff_type_code > 0;
#支払方法2
UPDATE master_app_production.suppliers SET payment_type_2_code=NULL, payment_2_site=NULL, bill_2_site=NULL, payment_condition_2_price=NULL, payment_condition_2_code=NULL;
UPDATE master_app_production.suppliers as a
        LEFT JOIN (
          SELECT *, 
            IF(cutoff_date_code=99,30,IFNULL(cutoff_date_code,0)) AS cutoff_date 
          FROM master_app_production.supplier_groups 
        ) AS b ON a.payto_supplier_group_id=b.id
        LEFT JOIN (
          SELECT *, 
            IF(payment_date_code=99,30,IFNULL(payment_date_code,0)) AS payment_date,
            IF(encach_date_code=99, 30,IFNULL(encach_date_code,0)) AS encach_date
          FROM master_app_production.supplier_group_payments WHERE payment_condition_price > 0 GROUP BY supplier_group_id
        ) AS c ON a.payto_supplier_group_id=c.supplier_group_id
SET 
  payment_condition_2_price = c.payment_condition_price,
  payment_condition_2_code = c.payment_condition_code,
  payment_type_2_code = c.payment_type_code, 
  payment_2_site = IF(c.payment_month_code > 0, 
                      (30-b.cutoff_date) + (c.payment_month_code-1)*30 + c.payment_date,
                      c.payment_date - b.cutoff_date
                   ),
  bill_2_site = IF(c.payment_type_code=2,
                    IF(c.encach_month_code > 0,
                       (30-c.payment_date) + (c.encach_month_code-1)*30 + c.encach_date,
                       c.encach_date - c.payment_date
                    )
                 , NULL)
WHERE a.cutoff_type_code > 0;

#-------------------------------------------------glanデータ変換---------------------------------------------
update sales as a
set a.cutoff_day = CAST(a.cutoff_date_code AS CHAR), a.cutoff_type_code=1, 
a.form_cutoff_date=IF(a.cutoff_date_code=99, 
                      LAST_DAY(DATE_ADD(a.target_date, interval a.charge_timing_code month)), 
                      CONCAT(
                        DATE_FORMAT(
                          DATE_ADD(a.target_date, interval a.charge_timing_code month)
                        , "%Y-%m-"),
                        CAST(a.cutoff_date_code AS CHAR)
                      )
                   );

update purchases as a
set a.cutoff_day = CAST(a.cutoff_date_code AS CHAR), a.cutoff_type_code=1, 
a.form_cutoff_date=IF(a.cutoff_date_code=99, 
                      LAST_DAY(DATE_ADD(a.target_date, interval a.charge_timing_code month)), 
                      CONCAT(
                        DATE_FORMAT(
                          DATE_ADD(a.target_date, interval a.charge_timing_code month)
                        , "%Y-%m-"),
                        CAST(a.cutoff_date_code AS CHAR)
                      )
                   );

update receivings as a
set a.cutoff_day = CAST(a.cutoff_date_code AS CHAR), a.cutoff_type_code=1, 
a.form_cutoff_date=IF(a.cutoff_date_code=99, 
                      LAST_DAY(DATE_ADD(a.target_date, interval a.credit_timing_code month)), 
                      CONCAT(
                        DATE_FORMAT(
                          DATE_ADD(a.target_date, interval a.credit_timing_code month)
                        , "%Y-%m-"),
                        CAST(a.cutoff_date_code AS CHAR)
                      )
                   );

update payings as a
set a.cutoff_day = CAST(a.cutoff_date_code AS CHAR), a.cutoff_type_code=1, 
a.form_cutoff_date=IF(a.cutoff_date_code=99, 
                      LAST_DAY(DATE_ADD(a.target_date, interval a.credit_timing_code month)), 
                      CONCAT(
                        DATE_FORMAT(
                          DATE_ADD(a.target_date, interval a.credit_timing_code month)
                        , "%Y-%m-"),
                        CAST(a.cutoff_date_code AS CHAR)
                      )
                   );

update receivable_carry_remains as a
set a.cutoff_day = CAST(a.cutoff_date_code AS CHAR), a.cutoff_type_code=1, 
a.form_cutoff_date=IF(a.cutoff_date_code=99, 
                      LAST_DAY(DATE_ADD(a.target_date, interval a.credit_timing_code month)), 
                      CONCAT(
                        DATE_FORMAT(
                          DATE_ADD(a.target_date, interval a.credit_timing_code month)
                        , "%Y-%m-"),
                        CAST(a.cutoff_date_code AS CHAR)
                      )
                   );

update payable_carry_remains as a
set a.cutoff_day = CAST(a.cutoff_date_code AS CHAR), a.cutoff_type_code=1, 
a.form_cutoff_date=IF(a.cutoff_date_code=99, 
                      LAST_DAY(DATE_ADD(a.target_date, interval a.credit_timing_code month)), 
                      CONCAT(
                        DATE_FORMAT(
                          DATE_ADD(a.target_date, interval a.credit_timing_code month)
                        , "%Y-%m-"),
                        CAST(a.cutoff_date_code AS CHAR)
                      )
                   );
#元帳
update customer_ledgers set cutoff_type_code=1, cutoff_day = CAST(cutoff_date_code AS CHAR);
update supplier_ledgers set cutoff_type_code=1, cutoff_day = CAST(cutoff_date_code AS CHAR);
#売掛／買掛
update receivables set cutoff_type_code=1, cutoff_day=IF(last_day(cutoff_date)=cutoff_date, "99", DATE_FORMAT(cutoff_date, "%d"));
update payables set cutoff_type_code=1, cutoff_day=IF(last_day(cutoff_date)=cutoff_date, "99", DATE_FORMAT(cutoff_date, "%d"));
