-- this script is to run on the old database that is converted from psql to mysql
-- export pland data from postgres
-- converting psql to mysql :: run the command line in current production server
-- php /home/webr/rta2.0/pg2mysql_cli.php pland_prod06-05-2022.psql  pland_prod06-05-2022.mysql.sql MyISAM
-- import the msyql and then run online migration for all data except tax
-- for tax migration run the sql below

select
'insert into transaction (`service_workflow_id`, `current_workflow_state_id`, `status`, `deliver_district_id`, `district_id`, `assigned_to`, `service_id`, `created_by`, `created_at`, `updated_by`, `updated_at`) values (2, 4, ''ISSUED'', 51, 51, 1, 2, 1, now(), 1, now());'
'set @transaction_id = last_insert_id();'
'set @party_id = (select id from party where migration_id = ' || ai.partner_id ||  ');'
'set @vehicle_id = (select id from vehicle where migration_id = ' || vt.vehicle ||  ');'
'set @party_vehicle_id  = (select id from migration_party_vehicle where party_migration_id = ' || ai.partner_id  ||' and vehicle_migration_id = ' ||  vt.vehicle  ||  ');'
'set @term_id = (select id from term where year = ' || qy.name || ' and quarter =  ' || tqc.quarter || ');'
'set @tax_sticker_id = (select id from tax_sticker where name = ''' || COALESCE (lc.name, lc.name, '') || ''');'
'insert into vehicle_tax (`status`, `term_id`, `party_id`, `vehicle_id`, `party_vehicle_id`, `transaction_id`, `tax_sticker_id`, `tax_number`,  `migration_id`, `created_by`, `created_at`, `updated_by`, `updated_at`)'
'values (''' || ai.state || ''', @term_id' || ', @party_id' || ', @vehicle_id' || ', @party_vehicle_id' || ', @transaction_id' || ', @tax_sticker_id'  || ', ''' || vt.tax_number || ''''  || ', ' || COALESCE (lc.id, lc.id, 0) || ''  || ', 1, now(), 1, now());'
-- 'insert into invoice (`party_id`, `transaction_id`, `invoice_number`, `invoice_date`, `due_date`, `outstanding_balance`, `invoice_amount`, `total_invoice_amount`, `status`, `discounts`, `taxes`, `migration_id`, `created_at`, `created_by`, `updated_by`, `updated_at`)'
-- 'values ( @party_id' || ', @transaction_id' || ',''' || ai.number || ''', ''' || ai.date_invoice || ''', ''' || ai.date_due || ''', ''' || ai.amount_total || ''', ''' || ai.amount_total || ''', ''' || ai.amount_total || ''', ''' || upper(ai.state) || ''', 0, 0,' || ai.id || ',''' || ai.create_date || ''',  1, 1, now()); '
from
            title_transactions tt
            join vehicle_tax vt on tt.vehicle_id = vt.vehicle
            join account_invoice ai on vt.invoice_id  = ai.id
            join tax_quarter_color tqc on vt.quarterly_tax_id  = tqc.id
            join quarter_year qy on tqc.year  = qy.id
            full outer join label_code lc on lc.id = vt.label_id
            where  ai.state = 'paid'
			order by tt.id
            limit 1;



