/*-- Fix (update existing) reserves in database --*/
DROP TEMPORARY TABLE IF EXISTS reserve_fixes;
SET @edso=0,@adso=0,@days=0;
CREATE TEMPORARY TABLE IF NOT EXISTS reserve_fixes AS (
SELECT @edso:=IFNULL(DATEDIFF(DATE(expected_date), DATE(actual_date)),0) as edso, @adso:=IFNULL(DATEDIFF(IF(mt1.reconcile_payment=0,mt1.transaction_date,mt2.transaction_date),actual_date),0) as adso,@days:=IF(@edso>@adso,@edso,@adso) as days, ROUND((invoice_master.base_face_value-invoice_master.base_face_value*(max_thirty_day_reserve/100)*12*(@days)/360)-invoice_closed_attributes.processing_fee-invoice_master.base_face_value*(purchase_discount/100),2) as real_reserve,invoice_reserve.reserve,invoice_master.invoice_id,invoice_master.manual_transaction_id
FROM invoice_master
LEFT JOIN invoice_closed_attributes ON invoice_closed_attributes.invoice_id = invoice_master.invoice_id
LEFT JOIN manual_transactions  mt1 ON mt1.manual_transaction_id = invoice_master.manual_transaction_id
LEFT JOIN manual_transactions mt2 ON mt1.reconcile_payment = mt2.manual_transaction_id
LEFT JOIN invoice_reserve ON invoice_reserve.invoice_id = invoice_master.invoice_id
HAVING real_reserve<>invoice_reserve.reserve
ORDER BY invoice_no);
UPDATE invoice_reserve, reserve_fixes
SET invoice_reserve.reserve = reserve_fixes.real_reserve
WHERE invoice_reserve.invoice_id = reserve_fixes.invoice_id;
DROP TEMPORARY TABLE IF EXISTS reserve_fixes;

/*-- Add missed reserves --*/
TRUNCATE invoice_reserve;
SET @edso=0,@adso=0,@days=0;
CREATE TEMPORARY TABLE IF NOT EXISTS new_reserves AS (
SELECT @edso:=IFNULL(DATEDIFF(DATE(expected_date), DATE(actual_date)),0) as edso, @adso:=IFNULL(DATEDIFF(IF(mt1.reconcile_payment=0,mt1.transaction_date,mt2.transaction_date),actual_date),0) as adso,@days:=IF(@edso>@adso,@edso,@adso) as days, ROUND((invoice_master.base_face_value-invoice_master.base_face_value*(max_thirty_day_reserve/100)*12*(@days)/360)-invoice_closed_attributes.processing_fee-invoice_master.base_face_value*(purchase_discount/100),2) as real_reserve,invoice_master.invoice_id,invoice_master.manual_transaction_id
FROM invoice_master
LEFT JOIN invoice_closed_attributes ON invoice_closed_attributes.invoice_id = invoice_master.invoice_id
LEFT JOIN manual_transactions  mt1 ON mt1.manual_transaction_id = invoice_master.manual_transaction_id
LEFT JOIN manual_transactions mt2 ON mt1.reconcile_payment = mt2.manual_transaction_id
WHERE invoice_master.manual_transaction_id<>0);
INSERT INTO invoice_reserve (reserve,invoice_id,manual_transaction_id,created_at)
SELECT real_reserve, invoice_id, manual_transaction_id,NOW()
FROM new_reserves;

/*-- Drop duplicates in invoice_reserve --*/
ALTER TABLE invoice_reserve ENGINE = MYISAM;
ALTER IGNORE TABLE invoice_reserve ADD UNIQUE INDEX drop_duplicates (invoice_id);
ALTER TABLE invoice_reserve ENGINE = InnoDB;
ALTER TABLE `invoice_reserve`  DROP INDEX drop_duplicates;

/*-- Find mismatch in invoice_reserve.reserve+credits and manual_transactions.amount --*/
SELECT N.*, manual_transactions.amount
FROM (
    SELECT T.manual_transaction_id, ROUND(SUM(T.total),2) as total, T.debtors
    FROM (
        SELECT manual_transactions.manual_transaction_id,IFNULL(SUM(invoice_reserve.reserve),0) as total,manual_transactions.debtors, 'reserve' as t
        FROM manual_transactions
        LEFT JOIN invoice_reserve ON  manual_transactions.manual_transaction_id = invoice_reserve.manual_transaction_id AND  FIND_IN_SET(invoice_reserve.invoice_id,manual_transactions.reference_to)>0
        WHERE manual_transactions.transaction_type=11
        GROUP BY manual_transactions.manual_transaction_id
        UNION 
        SELECT manual_transactions.manual_transaction_id, IFNULL(SUM(-1*m2.amount),0) as total,manual_transactions.debtors, 'credits' as t
        FROM manual_transactions
        LEFT JOIN manual_transactions as m2 ON m2.transaction_type=4 AND FIND_IN_SET(m2.manual_transaction_id,manual_transactions.transref_list)>0
        WHERE manual_transactions.transaction_type=11
        GROUP BY manual_transactions.manual_transaction_id
    ) AS T
    GROUP BY T.manual_transaction_id
) AS N
LEFT JOIN manual_transactions ON manual_transactions.manual_transaction_id = N.manual_transaction_id
HAVING N.total<> manual_transactions.amount

/*-- Fix mismatch in invoice_reserve.reserve+credits and manual_transactions.amount --*/
DROP TEMPORARY TABLE IF EXISTS reserve_transactions;
CREATE TEMPORARY TABLE IF NOT EXISTS reserve_transactions AS 
(
    SELECT N.*, manual_transactions.amount
    FROM (
        SELECT T.manual_transaction_id, ROUND(SUM(T.total),2) as total, T.debtors
        FROM (
            SELECT manual_transactions.manual_transaction_id,IFNULL(SUM(invoice_reserve.reserve),0) as total,manual_transactions.debtors, 'reserve' as t
            FROM manual_transactions
            LEFT JOIN invoice_reserve ON  FIND_IN_SET(invoice_reserve.invoice_id,manual_transactions.reference_to)>0
            WHERE manual_transactions.transaction_type=11
            GROUP BY manual_transactions.manual_transaction_id
            UNION 
            SELECT manual_transactions.manual_transaction_id, IFNULL(SUM(-1*m2.amount),0) as total,manual_transactions.debtors, 'credits' as t
            FROM manual_transactions
            LEFT JOIN manual_transactions as m2 ON m2.transaction_type=4 AND FIND_IN_SET(m2.manual_transaction_id,manual_transactions.transref_list)>0
            WHERE manual_transactions.transaction_type=11
            GROUP BY manual_transactions.manual_transaction_id
        ) AS T
        GROUP BY T.manual_transaction_id
    ) AS N
    LEFT JOIN manual_transactions ON manual_transactions.manual_transaction_id = N.manual_transaction_id
    HAVING N.total<> manual_transactions.amount
);
UPDATE manual_transactions, reserve_transactions
SET manual_transactions.amount = reserve_transactions.total
WHERE manual_transactions.manual_transaction_id = reserve_transactions.manual_transaction_id;
DROP TEMPORARY TABLE IF EXISTS reserve_transactions;

UPDATE invoice_master SET reserve_on=0;
DROP TEMPORARY TABLE IF EXISTS reactivate_reserves;
CREATE TEMPORARY TABLE IF NOT EXISTS reactivate_reserves AS 
(
    SELECT invoice_id
    FROM manual_transactions
    LEFT JOIN invoice_master ON FIND_IN_SET(invoice_master.invoice_id,manual_transactions.reference_to)>0
    WHERE transaction_type=11 AND reserve_on=0
);
UPDATE invoice_master, reactivate_reserves
SET invoice_master.reserve_on=1
WHERE invoice_master.invoice_id = reactivate_reserves.invoice_id;
DROP TEMPORARY TABLE IF EXISTS reactivate_reserves;


/**-- Create Specific Deductible transactions --**/
INSERT INTO manual_transactions(manual_transaction_id,transaction_type,debtor_id,originator_id,currency_id,amount,debtors,status,interface,paid_status,transaction_date,reconcile_ref)
SELECT null, 16, debtor_id, originator_id,currency_id, deductible_amount, debtors, status, interface, paid_status, transaction_date,manual_transaction_id
FROM manual_transactions
WHERE deductible_amount>0;
INSERT INTO statements (manual_transaction_id,originator_id)
SELECT manual_transaction_id,originator_id
FROM manual_transactions
WHERE transaction_type=16;

/**-- Create Trailing Balance transactions --**/
INSERT INTO manual_transactions(manual_transaction_id,transaction_type,debtor_id,originator_id,currency_id,amount,debtors,status,interface,paid_status,transaction_date,reconcile_ref)
SELECT null, 17, debtor_id, originator_id,currency_id, trailing_balance_amt, debtors, status, interface, paid_status, transaction_date, manual_transaction_id
FROM manual_transactions
WHERE trailing_balance_amt>0;
INSERT INTO statements (manual_transaction_id,originator_id)
SELECT manual_transaction_id,originator_id
FROM manual_transactions
WHERE transaction_type=17;


/**-- Check is R-trades are correct --**/
DROP TEMPORARY TABLE IF EXISTS deduct_fixes;
CREATE TEMPORARY TABLE IF NOT EXISTS deduct_fixes AS (
    SELECT 000000.00 as result, manual_transactions.amount, 000000.00 as credits, SUM(IFNULL(invoice_master.face_value,0)) as invoices, manual_transactions.deductible_amount, manual_transactions.trailing_balance_amt, manual_transactions.manual_transaction_id, manual_transactions.debtors, manual_transactions.originator_id, notes
    FROM manual_transactions
    LEFT JOIN invoice_master ON FIND_IN_SET(invoice_master.invoice_id, manual_transactions.reference_to)>0
    WHERE manual_transactions.transaction_type=6
    GROUP BY manual_transactions.manual_transaction_id
);
DROP TEMPORARY TABLE IF EXISTS credits_check;
CREATE TEMPORARY TABLE IF NOT EXISTS credits_check AS (
    SELECT SUM(IFNULL(credits.amount,0)) as credits,  manual_transactions.manual_transaction_id
    FROM manual_transactions
    LEFT JOIN manual_transactions as credits ON credits.transaction_type=4  AND FIND_IN_SET(credits.manual_transaction_id,manual_transactions.transref_list)>0
    WHERE manual_transactions.transaction_type=6
    GROUP BY manual_transactions.manual_transaction_id
);

UPDATE credits_check, deduct_fixes
SET deduct_fixes.credits = credits_check.credits, deduct_fixes.result =  deduct_fixes.amount+credits_check.credits-invoices+ deduct_fixes.deductible_amount - deduct_fixes.trailing_balance_amt
WHERE deduct_fixes.manual_transaction_id = credits_check.manual_transaction_id;

SELECT deduct_fixes.*, debtors_detail.debtor_reference_id, debtors_detail.debtor_name, organisation_details.organisation_reference_id, organisation_details.organisation_name
FROM deduct_fixes
LEFT JOIN debtors_detail ON debtors_detail.debtor_id = deduct_fixes.debtors
LEFT JOIN organisation_details ON organisation_details.user_id = deduct_fixes.originator_id

/**-- Sell rate changing--**/
UPDATE organisation_details, invoice_master
SET max_thirty_day_reserve=(IF(invoice_master.actual_date<'2014-01-29',0.825,0.875))
WHERE organisation_details.organisation_reference_id=11307000040 AND invoice_master.user_id = organisation_details.user_id;
UPDATE organisation_details, invoice_master
SET max_thirty_day_reserve=(IF(invoice_master.actual_date<'2014-05-23',0.825,0.875))
WHERE organisation_details.organisation_reference_id=11308000053 AND invoice_master.user_id = organisation_details.user_id;
UPDATE organisation_details, invoice_master
SET max_thirty_day_reserve=(IF(invoice_master.actual_date<'2014-07-17',0.825,0.875))
WHERE organisation_details.organisation_reference_id=11307000041 AND invoice_master.user_id = organisation_details.user_id;
UPDATE organisation_details, invoice_master
SET max_thirty_day_reserve=(IF(invoice_master.actual_date<'2014-11-07',1,2))
WHERE organisation_details.organisation_reference_id=11406000143 AND invoice_master.user_id = organisation_details.user_id;
UPDATE organisation_details, invoice_master
SET max_thirty_day_reserve=(IF(invoice_master.actual_date<'2014-05-15',0.85,0.875))
WHERE organisation_details.organisation_reference_id=11310000067 AND invoice_master.user_id = organisation_details.user_id;

/**-- PP changing--**/
UPDATE organisation_details,invoice_master, invoice_closed_attributes
SET invoice_closed_attributes.purchase_discount = IF(invoice_master.actual_date<'2014-07-01',90,80)
WHERE organisation_details.organisation_reference_id=11310000068 AND invoice_master.user_id = organisation_details.user_id AND invoice_closed_attributes.invoice_id = invoice_master.invoice_id;
UPDATE organisation_details,invoice_master, invoice_closed_attributes
SET invoice_closed_attributes.purchase_discount = IF(invoice_master.actual_date<'2014-07-03',90,70)
WHERE organisation_details.organisation_reference_id=11401000106 AND invoice_master.user_id = organisation_details.user_id AND invoice_closed_attributes.invoice_id = invoice_master.invoice_id;
UPDATE organisation_details,invoice_master, invoice_closed_attributes
SET invoice_closed_attributes.purchase_discount = IF(invoice_master.actual_date<'2014-01-29',94,IF(invoice_master.actual_date<'2014-04-30',90,IF(invoice_master.actual_date<'2014-05-22',75,50)))
WHERE organisation_details.organisation_reference_id=11307000040 AND invoice_master.user_id = organisation_details.user_id AND invoice_closed_attributes.invoice_id = invoice_master.invoice_id;
UPDATE organisation_details,invoice_master, invoice_closed_attributes
SET invoice_closed_attributes.purchase_discount = IF(invoice_master.actual_date<'2014-07-21',94,IF(invoice_master.actual_date<'2014-08-20',90,IF(invoice_master.actual_date<'2014-09-18',75,85)))
WHERE organisation_details.organisation_reference_id=11307000041 AND invoice_master.user_id = organisation_details.user_id AND invoice_closed_attributes.invoice_id = invoice_master.invoice_id;
UPDATE organisation_details,invoice_master, invoice_closed_attributes
SET invoice_closed_attributes.purchase_discount = IF(invoice_master.actual_date<'2014-05-23',80,IF(invoice_master.actual_date<'2014-06-04',50,60))
WHERE organisation_details.organisation_reference_id=11308000053 AND invoice_master.user_id = organisation_details.user_id AND invoice_closed_attributes.invoice_id = invoice_master.invoice_id;
UPDATE organisation_details,invoice_master, invoice_closed_attributes
SET invoice_closed_attributes.purchase_discount = IF(invoice_master.actual_date<'2014-02-05',91.5,90)
WHERE organisation_details.organisation_reference_id=11307000043 AND invoice_master.user_id = organisation_details.user_id AND invoice_closed_attributes.invoice_id = invoice_master.invoice_id;
UPDATE organisation_details,invoice_master, invoice_closed_attributes
SET invoice_closed_attributes.purchase_discount = IF(invoice_master.actual_date<'2014-09-11',90,IF(invoice_master.actual_date<'2014-11-20',85,90))
WHERE organisation_details.organisation_reference_id=11310000066 AND invoice_master.user_id = organisation_details.user_id AND invoice_closed_attributes.invoice_id = invoice_master.invoice_id;

/**-- Recalculate Purchased value--**/
UPDATE invoice_closed
LEFT JOIN invoice_closed_attributes ON invoice_closed_attributes.invoice_id = invoice_closed.invoice_id
LEFT JOIN invoice_master ON invoice_master.invoice_id = invoice_closed_attributes.invoice_id
SET invoice_closed.purchase_payment=  (invoice_master.face_value * (invoice_closed_attributes.purchase_discount/100)) - invoice_closed_attributes.processing_fee;


/*-- Find duplicated reserves --*/
SELECT *
FROM (
    SELECT  invoice_id
    FROM invoice_reserve
    GROUP BY invoice_id
    HAVING COUNT(invoice_id)>1
) AS T
LEFT JOIN invoice_reserve ON invoice_reserve.invoice_id = T.invoice_id;


/**-- Update PP for debtors ( 20130710000032, 20307110000057, 20130710000023 ) --**/
UPDATE debtors_detail
SET debtors_detail.purchase_price=80.0
WHERE debtors_detail.debtor_reference_id=20130711000057;
UPDATE invoice_closed_attributes, invoice_master, debtors_detail
SET purchase_discount=80.0
WHERE invoice_closed_attributes.invoice_id = invoice_master.invoice_id AND invoice_master.debtor_id = debtors_detail.debtor_id AND debtors_detail.debtor_reference_id=20130711000057 AND invoice_master.actual_date>='2014-09-30';

UPDATE debtors_detail
SET debtors_detail.purchase_price=80.0
WHERE debtors_detail.debtor_reference_id=20130710000023;
UPDATE invoice_closed_attributes, invoice_master, debtors_detail
SET purchase_discount=80.0
WHERE invoice_closed_attributes.invoice_id = invoice_master.invoice_id AND invoice_master.debtor_id = debtors_detail.debtor_id AND debtors_detail.debtor_reference_id=20130710000023 AND invoice_master.actual_date>='2014-09-25';
UPDATE invoice_closed_attributes, invoice_master, debtors_detail
SET purchase_discount=75.0
WHERE invoice_closed_attributes.invoice_id = invoice_master.invoice_id AND invoice_master.debtor_id = debtors_detail.debtor_id AND invoice_master.trade_reference_id=140918007711

UPDATE debtors_detail
SET debtors_detail.purchase_price=80.0
WHERE debtors_detail.debtor_reference_id=20130710000032;
UPDATE invoice_closed_attributes, invoice_master, debtors_detail
SET purchase_discount=80.0
WHERE invoice_closed_attributes.invoice_id = invoice_master.invoice_id AND invoice_master.debtor_id = debtors_detail.debtor_id AND debtors_detail.debtor_reference_id=20130710000032 AND invoice_master.actual_date>='2014-09-24';

/**--Fix error when reconcile_ref==1--**/
UPDATE manual_transactions
SET reconcile_ref=0, reconcile_status=0
WHERE manual_transaction_id IN (6689,6690,6691,6692,6694,6695,6696,6697,6698,6702)

/**--Fix invoices which were used in R-trade but has wrong value in  manual_transaction_id --**/
UPDATE invoice_master, manual_transactions as payment_trans, manual_transactions as reconcile_trans
SET invoice_master.manual_transaction_id = reconcile_trans.manual_transaction_id
WHERE payment_trans.transaction_type=1 AND payment_trans.transaction_date>='2014-31-12' AND payment_trans.reconcile_ref  = reconcile_trans.manual_transaction_id AND invoice_master.manual_transaction_id = payment_trans.manual_transaction_id

/**--Remove trailing "," in reference_to field --**/
UPDATE manual_transactions
SET reference_to=TRIM(TRAILING ',' FROM reference_to)
WHERE reference_to LIKE "%,"

/**--Remove organisation--**/
DELETE 
organisation_details,address_details, organisation_bank_account_details, organisation_bank_details, organisation_documents, contact_details, address_details2, debtor_relation
FROM organisation_details
LEFT JOIN address_details ON address_details.address_id IN (organisation_details.organisation_address_id,organisation_details.organisation_regoffice_id, organisation_details.mail_address_id)
LEFT JOIN organisation_bank_account_details ON organisation_bank_account_details.organisation_id = organisation_details.organisation_id
LEFT JOIN organisation_bank_details ON organisation_bank_details.organisation_id = organisation_details.organisation_id
LEFT JOIN organisation_documents ON organisation_documents.organisation_id = organisation_details.organisation_id
LEFT JOIN contact_details ON contact_details.organisation_id = organisation_details.organisation_id
LEFT JOIN address_details as address_details2 ON address_details2.address_id IN (contact_details.home_address_id, contact_details.hq_address_id, contact_details.work_address_id)
LEFT JOIN debtor_relation ON debtor_relation.organisation_id = organisation_details.organisation_id
WHERE organisation_details.organisation_reference_id  IN (11502000242)

/**--Delete duplicated SD and TB  transactions**/
DROP TEMPORARY TABLE IF EXISTS duplicated_sd_tb;
CREATE TEMPORARY TABLE IF NOT EXISTS duplicated_sd_tb AS (
    SELECT manual_transactions.manual_transaction_id
    FROM manual_transactions
    WHERE transaction_type IN (17,16)
    GROUP BY transaction_type,originator_id, amount, debtors, transaction_date
    HAVING COUNT(manual_transactions.manual_transaction_id)>1
);
DELETE  manual_transactions
FROM manual_transactions, duplicated_sd_tb
WHERE manual_transactions.manual_transaction_id = duplicated_sd_tb.manual_transaction_id;
DROP TEMPORARY TABLE IF EXISTS duplicated_sd_tb;

/**--Fix payment transactions which were reconciled but not assigned to R-Trade--**/
UPDATE manual_transactions m1, manual_transactions m2
SET m2.reconcile_status=1, m2.reconcile_ref = m1.manual_transaction_id
WHERE m1.transaction_type=6 AND m2.manual_transaction_id = m1.reconcile_payment AND m2.reconcile_status=0


SET FOREIGN_KEY_CHECKS=0; -- to disable
SET FOREIGN_KEY_CHECKS=1; -- to enable

/**--Assign SD transaction to parent R-trades --**/
UPDATE manual_transactions as sd_trans, manual_transactions as r_trade
SET r_trade.transref_list = IF(r_trade.transref_list='',sd_trans.manual_transaction_id, IF (FIND_IN_SET(sd_trans.manual_transaction_id,r_trade.transref_list)>0,r_trade.transref_list,CONCAT(r_trade.transref_list,",",sd_trans.manual_transaction_id))), sd_trans.reconcile_status=1
WHERE sd_trans.transaction_type=16 AND r_trade.manual_transaction_id = sd_trans.reconcile_ref AND r_trade.transaction_type=6

/**--Check is SD trans. assigned to R-Trades which created them--**/
SELECT organisation_details.organisation_name, organisation_details.organisation_reference_id, debtors_detail.debtor_reference_id, debtors_detail.debtor_name, T.amount, T.transaction_date, T.manual_transaction_id
FROM (
SELECT manual_transactions.*
FROM manual_transactions
LEFT JOIN manual_transactions as sd_trans ON sd_trans.transaction_type=16 AND  FIND_IN_SET(sd_trans.manual_transaction_id,manual_transactions.transref_list)>0 AND sd_trans.reconcile_ref <> manual_transactions.manual_transaction_id
WHERE manual_transactions.transaction_type=6 AND manual_transactions.transref_list<>'' AND sd_trans.manual_transaction_id IS NOT NULL
GROUP BY manual_transactions.manual_transaction_id
) AS T
LEFT JOIN debtors_detail ON debtors_detail.debtor_id  = T.debtors
LEFT JOIN organisation_details ON organisation_details.user_id = T.originator_id;

/**--Fix issue with false payment ids--**/
UPDATE manual_transactions as return_payments, manual_transactions as canceled_payments
SET return_payments.payment_return = canceled_payments.manual_transaction_id
WHERE return_payments.payment_return=1 AND  canceled_payments.false_payment = return_payments.manual_transaction_id;


/***/
SELECT trade_reference_id, country_mas.currency_code, face_value, invoice_master.created_at, organisation_details.organisation_reference_id, organisation_details.organisation_name, organisation_details.organisation_trade_name
FROM invoice_master
LEFT JOIN organisation_details ON organisation_details.user_id = invoice_master.user_id
LEFT JOIN country_mas ON country_mas.country_id = invoice_master.country_id
WHERE revolving=1 AND root_invoice_id=0 AND draft=1
ORDER BY organisation_details.organisation_name


SELECT debtors_detail.debtor_reference_id as DEBTORID, organisation_details.organisation_reference_id as ORIGINATORID, DATE(DATE_ADD(NOW(),INTERVAL FLOOR(RAND() * 47) DAY)) as INVOICEDATE,FLOOR(RAND() * 70676) as PURCHASEORDERNO,FLOOR(RAND() * 2345)  as INVOICENO,ROUND(RAND() * 9456,2) as FACEVALUE,DATE(DATE_ADD(NOW(),INTERVAL FLOOR(RAND() * 84+20) DAY)) as EXPECTEDDATE,FLOOR(RAND() * 84)  as `PAYMENT TERMS`,invoice_master.trade_reference_id as TRADE_REFERENCE_ID
FROM debtor_relation
LEFT JOIN debtors_detail ON debtors_detail.debtor_id = debtor_relation.debtor_id
LEFT JOIN organisation_details ON organisation_details.organisation_id = debtor_relation.organisation_id
LEFT JOIN invoice_master ON invoice_master.revolving=1 AND invoice_master.root_invoice_id>0 AND invoice_master.user_id = organisation_details.user_id AND debtors_detail.debtor_id = invoice_master.debtor_id
WHERE invoice_master.trade_reference_id IS NOT NULL
ORDER BY RAND()
LIMIT 10


SET FOREIGN_KEY_CHECKS=0; -- to disable
SET FOREIGN_KEY_CHECKS=1; -- to enable>>>>>>> .merge-right.r407


//Fix of issue with wrong Purchased value in adminindex.php?do=trastatusmgnt&id=xxx
UPDATE invoice_closed
LEFT JOIN invoice_master ON invoice_closed.invoice_id = invoice_master.invoice_id
LEFT JOIN invoice_closed_attributes ON invoice_closed_attributes.invoice_id = invoice_master.invoice_id
LEFT JOIN debtors_detail ON debtors_detail.debtor_id = invoice_master.debtor_id
SET invoice_closed.purchase_payment=ROUND((invoice_master.face_value * (invoice_closed_attributes.purchase_discount/100)) - invoice_closed_attributes.processing_fee,2)
WHERE invoice_master.root_invoice_id>0 AND debtors_detail.type='debtor' AND invoice_closed.invoice_id IN (10235,10358,11157,11427,11839,12423,12597,12820,13062,13427,13588,13832,14418,14707,15145,15778,16146,16448,17165,17640,18102,18749,19101,10191,10345,10435,11151,11257,11832,12319,12560,12750,13008,13390,13553,13808,14374,14517,15076,15513,16031,16240,16942,17385,18042,18272,18865,10166,10232,10329,10436,11246,11647,12250,12517,12712,12969,13197,13499,13774,14275,14575,15057,15301,15774,15971,16371,16445,16884,17162,17342,17637,17742,18099,18670,18744,18854,18964,19283,17974,17975,18060,18062,18119,18120,18668,18672,18730,18739,18740,18969,18132,18133,18134,18135,18136,18137,18138,18139,18140,18141,18142,18143);