HEX
Server: Apache/2.2.15 (CentOS)
System: Linux ip-10-0-2-146.eu-west-1.compute.internal 2.6.32-754.35.1.el6.centos.plus.x86_64 #1 SMP Sat Nov 7 11:33:42 UTC 2020 x86_64
User: root (0)
PHP: 5.6.40
Disabled: NONE
$ pwd: /www/exchange0old/exchange/custom_reports/
Upload Files
File: /www/exchange0old/exchange/custom_reports/2_payments_sql_fixes.sql
#155
INSERT INTO manual_transactions_rejected (manual_transaction_id,transaction_type,debtor_id,originator_id,currency_id,fx_rate,actual_fx_rate,amount,debtors,reference_to,specific_allocation,notes,transaction_file,specific_deductible,deductible_amount,trailing_balance,trailing_balance_amt,transref_list,reconcile_payment,reconcile_ref,status,reconcile_status,interface,transaction_date,notification_title,transaction_name,reject_status,created_at,created_by,delete_notes) 
(
SELECT manual_transaction_id,transaction_type,debtor_id,originator_id,currency_id,fx_rate,actual_fx_rate,amount,debtors,reference_to,specific_allocation,notes,transaction_file,specific_deductible,deductible_amount,trailing_balance,trailing_balance_amt,transref_list,reconcile_payment,reconcile_ref,status,reconcile_status,interface,transaction_date,notification_title,transaction_name,0,NOW(),1,''
FROM manual_transactions 
WHERE manual_transaction_id = '1660'
);
DELETE manual_transactions, manual_transactions_invoice 
FROM manual_transactions
LEFT JOIN manual_transactions_invoice ON manual_transactions.manual_transaction_id = manual_transactions_invoice.transaction_id
WHERE manual_transactions.reconcile_ref=1660 AND manual_transactions.transaction_type IN (16,17);

UPDATE manual_transactions  SET reconcile_status=0,reconcile_ref=0, specific_allocation=0 WHERE manual_transaction_id='155';
UPDATE invoice_master SET manual_transaction_id = 0, auto_assign = 0 WHERE invoice_id IN (824,918,968,1032,1285,5227);
DELETE FROM invoice_reserve WHERE invoice_id IN (824,918,968,1032,1285,5227);
DELETE FROM `manual_transactions`  WHERE `manual_transaction_id` ='1660';
DELETE FROM `statements` WHERE `manual_transaction_id` ='1660';
DELETE FROM manual_transactions_invoice  WHERE transaction_id='1660';


INSERT INTO manual_transactions_rejected (manual_transaction_id,transaction_type,debtor_id,originator_id,currency_id,fx_rate,actual_fx_rate,amount,debtors,reference_to,specific_allocation,notes,transaction_file,specific_deductible,deductible_amount,trailing_balance,trailing_balance_amt,transref_list,reconcile_payment,reconcile_ref,status,reconcile_status,interface,transaction_date,notification_title,transaction_name,reject_status,created_at,created_by,delete_notes) 
(
SELECT manual_transaction_id,transaction_type,debtor_id,originator_id,currency_id,fx_rate,actual_fx_rate,amount,debtors,reference_to,specific_allocation,notes,transaction_file,specific_deductible,deductible_amount,trailing_balance,trailing_balance_amt,transref_list,reconcile_payment,reconcile_ref,status,reconcile_status,interface,transaction_date,notification_title,transaction_name,0,NOW(),1,''
FROM manual_transactions 
WHERE manual_transaction_id = '3319'
);
DELETE manual_transactions, manual_transactions_invoice
FROM manual_transactions
LEFT JOIN manual_transactions_invoice ON manual_transactions.manual_transaction_id = manual_transactions_invoice.transaction_id
WHERE manual_transactions.reconcile_ref=3319 AND manual_transactions.transaction_type IN (16,17);

UPDATE manual_transactions SET reconcile_status=0,reconcile_ref=0, specific_allocation=0 WHERE manual_transaction_id='155';
UPDATE invoice_master SET manual_transaction_id = 0, auto_assign = 0 WHERE invoice_id IN (824,918,968,1032,1285);
DELETE FROM invoice_reserve WHERE invoice_id IN (824,918,968,1032,1285);
UPDATE manual_transactions SET reconcile_status=0, reconcile_credit=0 WHERE manual_transaction_id IN (6439);
DELETE FROM `manual_transactions` WHERE `manual_transaction_id` ='3319';
DELETE FROM `statements` WHERE `manual_transaction_id` ='3319';
DELETE FROM manual_transactions_invoice  WHERE transaction_id='3319';


INSERT INTO `manual_transactions` (`transaction_type`,`debtors`,`originator_id`,`fx_rate`,`actual_fx_rate`,`amount`,`reference_to`,`specific_allocation`, 
 `notes`,`transaction_file`,`specific_deductible`,`deductible_amount`,`trailing_balance`,`trailing_balance_amt`,`transref_list`,`reconcile_payment`,`payment_return`,`status`,`multiple_payments`,
 `interface`,`transaction_date`,`issue_date`,`created_at`,`created_by`) 
VALUES('6','79','43','1.2',0,'5934','824,918,968,1032,1285','1',
'','','1','93','0','','','155','0',0,'',
1,'2015-06-29','',NOW(),'1');

SET @trans_id= LAST_INSERT_ID();

UPDATE manual_transactions 
SET reconcile_status=1, reconcile_ref = @trans_id
WHERE manual_transaction_id=155;
UPDATE manual_transactions_invoice SET allocation='1',trailing_status='1' WHERE transaction_id=@trans_id;

UPDATE `invoice_master` 
SET payment_status=1,manual_transaction_id=@trans_id,auto_assign='1' 
WHERE invoice_id IN (824,918,968,1032,1285);

INSERT INTO manual_transactions_invoice (`transaction_id`, `invoice_id`, `allocation`, `balance_amount`, `reserve_amount`,`trailing_status`, `created_at`, `modified_at`) 
SELECT @trans_id,invoice_id,'1','18.600000000000001421','0',1,NOW(),NOW() 
FROM invoice_master
WHERE invoice_id IN (824,918,968,1032,1285);

INSERT INTO invoice_reserve (manual_transaction_id,invoice_id,reserve,created_at) VALUES (@trans_id,824,26.133500000000026375,NOW()),(@trans_id,918,56.395100000000184082,NOW()),(@trans_id,968,64.599500000000034561,NOW()),(@trans_id,1032,20.496800000000007458,NOW()),(@trans_id,1285,36.984800000000063847,NOW());

INSERT INTO manual_transactions(manual_transaction_id,transaction_type,debtor_id,originator_id,currency_id,amount,debtors,status,interface,transaction_date,reconcile_ref,reconcile_status)
SELECT null, 16, debtor_id, originator_id,currency_id, deductible_amount, debtors, 1, interface, transaction_date,manual_transaction_id,1
FROM manual_transactions
WHERE manual_transaction_id=@trans_id AND specific_deductible=1 AND deductible_amount<>0;

SET @sd_trans = LAST_INSERT_ID();

INSERT INTO manual_transactions(manual_transaction_id,transaction_type,debtor_id,originator_id,currency_id,amount,debtors,status,interface,transaction_date,reconcile_ref)
SELECT null, 17, debtor_id, originator_id,currency_id, trailing_balance_amt, debtors, 1, interface,  transaction_date,manual_transaction_id
FROM manual_transactions
WHERE manual_transaction_id=@trans_id AND trailing_balance=1 AND trailing_balance_amt<>0;

SET @tb_trans = LAST_INSERT_ID();

INSERT INTO statements (manual_transaction_id,originator_id)
SELECT manual_transaction_id,originator_id
FROM manual_transactions
WHERE manual_transaction_id IN (@sd_trans,@tb_trans);

UPDATE statements 
SET reference_id = (CONCAT("1",DATE_FORMAT(created_at,"%y%m"), "000000") + transaction_id),reference=(CONCAT(reference_id, "-STL") ) 
WHERE transaction_id IN (@sd_trans,@tb_trans);

UPDATE `manual_transactions` 
SET status='1', authorised_at=NOW(), authorised_by=1, transref_list =  IF(transref_list="",@sd_trans,CONCAT(transref_list,",",@sd_trans))
WHERE manual_transaction_id=@trans_id 
LIMIT 1;



#3869
DELETE FROM invoice_reserve WHERE invoice_id IN (4740,4851,4935,7440,7645,7932);
UPDATE `invoice_master` SET `manual_transaction_id`=0 WHERE `invoice_id`=4740 LIMIT 1;
UPDATE `invoice_master` SET `manual_transaction_id`=0 WHERE `invoice_id`=4851 LIMIT 1;
UPDATE `invoice_master` SET `manual_transaction_id`=0 WHERE `invoice_id`=4935 LIMIT 1;
UPDATE `invoice_master` SET `manual_transaction_id`=0 WHERE `invoice_id`=7440 LIMIT 1;
UPDATE `invoice_master` SET `manual_transaction_id`=0 WHERE `invoice_id`=7645 LIMIT 1;
UPDATE `invoice_master` SET `manual_transaction_id`=0 WHERE `invoice_id`=7932 LIMIT 1;
UPDATE `manual_transactions` SET `reconcile_status`=0 WHERE `manual_transaction_id`=3256 LIMIT 1;
UPDATE `manual_transactions` SET `reconcile_status`=0 WHERE `manual_transaction_id`=3284 LIMIT 1;
UPDATE `manual_transactions` SET `reconcile_status`=0 WHERE `manual_transaction_id`=3285 LIMIT 1;
UPDATE `statements` SET `reserve_on`=0 WHERE `transaction_id`=3373 LIMIT 1;
UPDATE `statements` SET `reserve_on`=0 WHERE `transaction_id`=3405 LIMIT 1;
UPDATE `statements` SET `reserve_on`=0 WHERE `transaction_id`=3406 LIMIT 1;


INSERT INTO manual_transactions_rejected (manual_transaction_id,transaction_type,debtor_id,originator_id,currency_id,fx_rate,actual_fx_rate,amount,debtors,reference_to,specific_allocation,notes,transaction_file,specific_deductible,deductible_amount,trailing_balance,trailing_balance_amt,transref_list,reconcile_payment,reconcile_ref,status,reconcile_status,interface,transaction_date,notification_title,transaction_name,reject_status,created_at,created_by,delete_notes) 
(
SELECT manual_transaction_id,transaction_type,debtor_id,originator_id,currency_id,fx_rate,actual_fx_rate,amount,debtors,reference_to,specific_allocation,notes,transaction_file,specific_deductible,deductible_amount,trailing_balance,trailing_balance_amt,transref_list,reconcile_payment,reconcile_ref,status,reconcile_status,interface,transaction_date,notification_title,transaction_name,0,NOW(),1,''
FROM manual_transactions 
WHERE manual_transaction_id = '3892'
);


INSERT INTO manual_transactions_rejected (manual_transaction_id,transaction_type,debtor_id,originator_id,currency_id,fx_rate,actual_fx_rate,amount,debtors,reference_to,specific_allocation,notes,transaction_file,specific_deductible,deductible_amount,trailing_balance,trailing_balance_amt,transref_list,reconcile_payment,reconcile_ref,status,reconcile_status,interface,transaction_date,notification_title,transaction_name,reject_status,created_at,created_by,delete_notes) 
(
SELECT manual_transaction_id,transaction_type,debtor_id,originator_id,currency_id,fx_rate,actual_fx_rate,amount,debtors,reference_to,specific_allocation,notes,transaction_file,specific_deductible,deductible_amount,trailing_balance,trailing_balance_amt,transref_list,reconcile_payment,reconcile_ref,status,reconcile_status,interface,transaction_date,notification_title,transaction_name,0,NOW(),1,''
FROM manual_transactions 
WHERE manual_transaction_id = '5260'
);
DELETE FROM `manual_transactions` WHERE `manual_transaction_id`=3892 LIMIT 1;
DELETE FROM `manual_transactions` WHERE `manual_transaction_id`=5260 LIMIT 1;
UPDATE `manual_transactions` SET `reconcile_ref`=0 WHERE `manual_transaction_id`=3869 LIMIT 1;
UPDATE `manual_transactions` SET `reconcile_status`=0 WHERE `manual_transaction_id`=3869 LIMIT 1;

INSERT INTO `manual_transactions` (`transaction_type`,`debtors`,`originator_id`,`fx_rate`,`actual_fx_rate`,`amount`,`reference_to`,`specific_allocation`, 
`notes`,`transaction_file`,`specific_deductible`,`deductible_amount`,`trailing_balance`,`trailing_balance_amt`,`transref_list`,`reconcile_payment`,`payment_return`,`status`,`multiple_payments`,
`interface`,`transaction_date`,`issue_date`,`created_at`,`created_by`) 
VALUES('6','265','43','1.2',0,'4989.1999999999998181','7440,7645,7932','1',
'','','0','','0','','3285,3256,3374','3869','0',0,'',
1,'2015-06-29','',NOW(),'1');

SET @trans_id= LAST_INSERT_ID();

UPDATE manual_transactions 
SET reconcile_status=1, reconcile_ref = @trans_id 
WHERE manual_transaction_id=3869;
UPDATE manual_transactions_invoice SET allocation='1',trailing_status='1' WHERE transaction_id=@trans_id;
UPDATE manual_transactions SET
    reconcile_credit=1
WHERE manual_transaction_id IN (3285,3256,3374);
UPDATE `invoice_master` 
SET payment_status=1,manual_transaction_id=@trans_id ,auto_assign='1' 
WHERE invoice_id IN (7440,7645,7932);
INSERT INTO manual_transactions_invoice (`transaction_id`, `invoice_id`, `allocation`, `balance_amount`, `reserve_amount`,`trailing_status`, `created_at`, `modified_at`) 
SELECT @trans_id,invoice_id,'1','0','0',1,NOW(),NOW() 
FROM invoice_master
WHERE invoice_id IN (7440,7645,7932);

UPDATE manual_transactions 
    SET reconcile_status=1, reconcile_credit=1 
WHERE manual_transaction_id IN (3285,3256,3374);
INSERT INTO invoice_reserve (manual_transaction_id,invoice_id,reserve,created_at) VALUES (@trans_id,7440,219.95558333333337941,NOW()),(@trans_id,7645,480.44899999999984175,NOW()),(@trans_id,7932,367.6479999999996835,NOW());
UPDATE `manual_transactions` 
SET status='1', authorised_at=NOW(), authorised_by=1, transref_list =  transref_list 
WHERE manual_transaction_id= @trans_id
LIMIT 1;


#3223

DELETE FROM invoice_reserve WHERE invoice_id IN (6495,8080,5341);
UPDATE `invoice_master` SET `manual_transaction_id`=0 WHERE `invoice_id`=5341 LIMIT 1;
UPDATE `invoice_master` SET `manual_transaction_id`=0 WHERE `invoice_id`=6495 LIMIT 1;
UPDATE `invoice_master` SET `manual_transaction_id`=0 WHERE `invoice_id`=8080 LIMIT 1;

INSERT INTO manual_transactions_rejected (manual_transaction_id,transaction_type,debtor_id,originator_id,currency_id,fx_rate,actual_fx_rate,amount,debtors,reference_to,specific_allocation,notes,transaction_file,specific_deductible,deductible_amount,trailing_balance,trailing_balance_amt,transref_list,reconcile_payment,reconcile_ref,status,reconcile_status,interface,transaction_date,notification_title,transaction_name,reject_status,created_at,created_by,delete_notes) 
(
SELECT manual_transaction_id,transaction_type,debtor_id,originator_id,currency_id,fx_rate,actual_fx_rate,amount,debtors,reference_to,specific_allocation,notes,transaction_file,specific_deductible,deductible_amount,trailing_balance,trailing_balance_amt,transref_list,reconcile_payment,reconcile_ref,status,reconcile_status,interface,transaction_date,notification_title,transaction_name,0,NOW(),1,''
FROM manual_transactions 
WHERE manual_transaction_id = '5105'
);


INSERT INTO manual_transactions_rejected (manual_transaction_id,transaction_type,debtor_id,originator_id,currency_id,fx_rate,actual_fx_rate,amount,debtors,reference_to,specific_allocation,notes,transaction_file,specific_deductible,deductible_amount,trailing_balance,trailing_balance_amt,transref_list,reconcile_payment,reconcile_ref,status,reconcile_status,interface,transaction_date,notification_title,transaction_name,reject_status,created_at,created_by,delete_notes) 
(
SELECT manual_transaction_id,transaction_type,debtor_id,originator_id,currency_id,fx_rate,actual_fx_rate,amount,debtors,reference_to,specific_allocation,notes,transaction_file,specific_deductible,deductible_amount,trailing_balance,trailing_balance_amt,transref_list,reconcile_payment,reconcile_ref,status,reconcile_status,interface,transaction_date,notification_title,transaction_name,0,NOW(),1,''
FROM manual_transactions 
WHERE manual_transaction_id = '3780'
);
DELETE FROM `manual_transactions` WHERE `manual_transaction_id`=5105 LIMIT 1;
DELETE FROM `manual_transactions` WHERE `manual_transaction_id`=3780 LIMIT 1;
UPDATE `manual_transactions` SET `reconcile_ref`=0 WHERE `manual_transaction_id`=3223 LIMIT 1;
UPDATE `manual_transactions` SET `reconcile_status`=0 WHERE `manual_transaction_id`=3223 LIMIT 1;

UPDATE `invoice_master` SET `auto_assign`=0 WHERE `invoice_id`=5341 LIMIT 1;
UPDATE `invoice_master` SET `auto_assign`=0 WHERE `invoice_id`=6495 LIMIT 1;
UPDATE `invoice_master` SET `auto_assign`=0 WHERE `invoice_id`=8080 LIMIT 1;


INSERT INTO `manual_transactions` (`transaction_type`,`debtors`,`originator_id`,`fx_rate`,`actual_fx_rate`,`amount`,`reference_to`,`specific_allocation`, 
`notes`,`transaction_file`,`specific_deductible`,`deductible_amount`,`trailing_balance`,`trailing_balance_amt`,`transref_list`,`reconcile_payment`,`payment_return`,`status`,`multiple_payments`,
`interface`,`transaction_date`,`issue_date`,`created_at`,`created_by`) 
VALUES('6','74','43','1.2',0,'2256','6495,8080','1',
'','','0','','0','','','3223','0',0,'',
1,'2015-06-29','',NOW(),'1');

SET @trans_id= LAST_INSERT_ID();

UPDATE manual_transactions 
SET reconcile_status=1, reconcile_ref = @trans_id
WHERE manual_transaction_id=3223;

UPDATE manual_transactions_invoice SET allocation='1',trailing_status='1' WHERE transaction_id=@trans_id;
UPDATE `invoice_master` 
SET payment_status=1,manual_transaction_id=@trans_id,auto_assign='1' 
WHERE invoice_id IN (6495,8080);
INSERT INTO manual_transactions_invoice (`transaction_id`, `invoice_id`, `allocation`, `balance_amount`, `reserve_amount`,`trailing_status`, `created_at`, `modified_at`) 
SELECT @trans_id,invoice_id,'1','0','0',1,NOW(),NOW() 
FROM invoice_master
WHERE invoice_id IN (6495,8080);

INSERT INTO invoice_reserve (manual_transaction_id,invoice_id,reserve,created_at) VALUES (@trans_id,6495,42.807999999999992724,NOW()),(@trans_id,8080,137.41900000000009641,NOW());

UPDATE `manual_transactions` 
SET status='1', authorised_at=NOW(), authorised_by=1, transref_list =  transref_list 
WHERE manual_transaction_id=@trans_id 
LIMIT 1;



#184

UPDATE `invoice_master` SET `manual_transaction_id`=0 WHERE `invoice_id`=1480 LIMIT 1;
UPDATE `invoice_master` SET `manual_transaction_id`=0 WHERE `invoice_id`=4761 LIMIT 1;
UPDATE `invoice_master` SET `manual_transaction_id`=0 WHERE `invoice_id`=5080 LIMIT 1;
UPDATE `invoice_master` SET `manual_transaction_id`=0 WHERE `invoice_id`=5227 LIMIT 1;
UPDATE `invoice_master` SET `manual_transaction_id`=0 WHERE `invoice_id`=5239 LIMIT 1;
UPDATE `invoice_master` SET `auto_assign`=0 WHERE `invoice_id`=5239 LIMIT 1;
UPDATE `invoice_master` SET `auto_assign`=0 WHERE `invoice_id`=5227 LIMIT 1;
UPDATE `invoice_master` SET `auto_assign`=0 WHERE `invoice_id`=5080 LIMIT 1;
UPDATE `invoice_master` SET `auto_assign`=0 WHERE `invoice_id`=4761 LIMIT 1;

DELETE FROM invoice_reserve WHERE invoice_id IN (1302,1359,1480,4761,5080,5239,1302,1359,1480,5227);

INSERT INTO manual_transactions_rejected (manual_transaction_id,transaction_type,debtor_id,originator_id,currency_id,fx_rate,actual_fx_rate,amount,debtors,reference_to,specific_allocation,notes,transaction_file,specific_deductible,deductible_amount,trailing_balance,trailing_balance_amt,transref_list,reconcile_payment,reconcile_ref,status,reconcile_status,interface,transaction_date,notification_title,transaction_name,reject_status,created_at,created_by,delete_notes) 
(
SELECT manual_transaction_id,transaction_type,debtor_id,originator_id,currency_id,fx_rate,actual_fx_rate,amount,debtors,reference_to,specific_allocation,notes,transaction_file,specific_deductible,deductible_amount,trailing_balance,trailing_balance_amt,transref_list,reconcile_payment,reconcile_ref,status,reconcile_status,interface,transaction_date,notification_title,transaction_name,0,NOW(),1,''
FROM manual_transactions 
WHERE manual_transaction_id = '1662'
);


INSERT INTO manual_transactions_rejected (manual_transaction_id,transaction_type,debtor_id,originator_id,currency_id,fx_rate,actual_fx_rate,amount,debtors,reference_to,specific_allocation,notes,transaction_file,specific_deductible,deductible_amount,trailing_balance,trailing_balance_amt,transref_list,reconcile_payment,reconcile_ref,status,reconcile_status,interface,transaction_date,notification_title,transaction_name,reject_status,created_at,created_by,delete_notes) 
(
SELECT manual_transaction_id,transaction_type,debtor_id,originator_id,currency_id,fx_rate,actual_fx_rate,amount,debtors,reference_to,specific_allocation,notes,transaction_file,specific_deductible,deductible_amount,trailing_balance,trailing_balance_amt,transref_list,reconcile_payment,reconcile_ref,status,reconcile_status,interface,transaction_date,notification_title,transaction_name,0,NOW(),1,''
FROM manual_transactions 
WHERE manual_transaction_id = '1661'
);
DELETE FROM `manual_transactions` WHERE `manual_transaction_id`=1661 LIMIT 1;
DELETE FROM `manual_transactions` WHERE `manual_transaction_id`=1662 LIMIT 1;
UPDATE `manual_transactions` SET `reconcile_ref`=0 WHERE `manual_transaction_id`=184 LIMIT 1;
UPDATE `manual_transactions` SET `reconcile_status`=0 WHERE `manual_transaction_id`=184 LIMIT 1;

INSERT INTO `manual_transactions` (`transaction_type`,`debtors`,`originator_id`,`fx_rate`,`actual_fx_rate`,`amount`,`reference_to`,`specific_allocation`, 
`notes`,`transaction_file`,`specific_deductible`,`deductible_amount`,`trailing_balance`,`trailing_balance_amt`,`transref_list`,`reconcile_payment`,`payment_return`,`status`,`multiple_payments`,
`interface`,`transaction_date`,`issue_date`,`created_at`,`created_by`) 
VALUES('6','79','43','1.2',0,'2878','1302,1359,1480','1',
'','','1','162','0','','','184','0',0,'',
1,'2015-06-29','',NOW(),'1');

SET @trans_id= LAST_INSERT_ID();

UPDATE manual_transactions 
SET reconcile_status=1, reconcile_ref = @trans_id
WHERE manual_transaction_id=184;
UPDATE manual_transactions_invoice SET allocation='1',trailing_status='1' WHERE transaction_id=@trans_id;
UPDATE `invoice_master` 
SET payment_status=1,manual_transaction_id=@trans_id,auto_assign='1' 
WHERE invoice_id IN (1302,1359,1480);

INSERT INTO manual_transactions_invoice (`transaction_id`, `invoice_id`, `allocation`, `balance_amount`, `reserve_amount`,`trailing_status`, `created_at`, `modified_at`) 
SELECT @trans_id,invoice_id,'1','54','0',1,NOW(),NOW() 
FROM invoice_master
WHERE invoice_id IN (1302,1359,1480);

INSERT INTO invoice_reserve (manual_transaction_id,invoice_id,reserve,created_at) VALUES (@trans_id,1302,41.032799999999951979,NOW()),(@trans_id,1359,37.941200000000094406,NOW()),(@trans_id,1480,28.764400000000023283,NOW());

INSERT INTO manual_transactions(manual_transaction_id,transaction_type,debtor_id,originator_id,currency_id,amount,debtors,status,interface,transaction_date,reconcile_ref,reconcile_status)
SELECT null, 16, debtor_id, originator_id,currency_id, deductible_amount, debtors, 1, interface, transaction_date,manual_transaction_id,1
FROM manual_transactions
WHERE manual_transaction_id=@trans_id AND specific_deductible=1 AND deductible_amount<>0;

SET @sd_trans = LAST_INSERT_ID();

INSERT INTO manual_transactions(manual_transaction_id,transaction_type,debtor_id,originator_id,currency_id,amount,debtors,status,interface,transaction_date,reconcile_ref)
SELECT null, 17, debtor_id, originator_id,currency_id, trailing_balance_amt, debtors, 1, interface,  transaction_date,manual_transaction_id
FROM manual_transactions
WHERE manual_transaction_id=@trans_id AND trailing_balance=1 AND trailing_balance_amt<>0;

SET @tb_trans = LAST_INSERT_ID();

INSERT INTO statements (manual_transaction_id,originator_id)
SELECT manual_transaction_id,originator_id
FROM manual_transactions
WHERE manual_transaction_id IN (@sd_trans,@tb_trans);

UPDATE statements 
SET reference_id = (CONCAT("1",DATE_FORMAT(created_at,"%y%m"), "000000") + transaction_id),reference=(CONCAT(reference_id, "-STL") ) 
WHERE transaction_id IN (@sd_trans,@tb_trans);

UPDATE `manual_transactions` 
SET status='1', authorised_at=NOW(), authorised_by=1, transref_list =  IF(transref_list="",@sd_trans,CONCAT(transref_list,",",@sd_trans))
WHERE manual_transaction_id=@trans_id 
LIMIT 1;


#179
UPDATE `invoice_master` SET `manual_transaction_id`=0 WHERE `invoice_id`=230 LIMIT 1;
UPDATE `invoice_master` SET `manual_transaction_id`=0 WHERE `invoice_id`=264 LIMIT 1;
UPDATE `invoice_master` SET `manual_transaction_id`=0, `auto_assign`=0 WHERE `invoice_id`=970 LIMIT 1;
UPDATE `invoice_master` SET `auto_assign`=0 WHERE `invoice_id`=230 LIMIT 1;

DELETE  FROM invoice_reserve WHERE invoice_id IN (230,970,264);
UPDATE `manual_transactions` SET `reconcile_status`=0, `reconcile_credit`=0 WHERE `manual_transaction_id`=828 LIMIT 1;

INSERT INTO manual_transactions_rejected (manual_transaction_id,transaction_type,debtor_id,originator_id,currency_id,fx_rate,actual_fx_rate,amount,debtors,reference_to,specific_allocation,notes,transaction_file,specific_deductible,deductible_amount,trailing_balance,trailing_balance_amt,transref_list,reconcile_payment,reconcile_ref,status,reconcile_status,interface,transaction_date,notification_title,transaction_name,reject_status,created_at,created_by,delete_notes) 
(
SELECT manual_transaction_id,transaction_type,debtor_id,originator_id,currency_id,fx_rate,actual_fx_rate,amount,debtors,reference_to,specific_allocation,notes,transaction_file,specific_deductible,deductible_amount,trailing_balance,trailing_balance_amt,transref_list,reconcile_payment,reconcile_ref,status,reconcile_status,interface,transaction_date,notification_title,transaction_name,0,NOW(),1,''
FROM manual_transactions 
WHERE manual_transaction_id = '1342'
);


INSERT INTO manual_transactions_rejected (manual_transaction_id,transaction_type,debtor_id,originator_id,currency_id,fx_rate,actual_fx_rate,amount,debtors,reference_to,specific_allocation,notes,transaction_file,specific_deductible,deductible_amount,trailing_balance,trailing_balance_amt,transref_list,reconcile_payment,reconcile_ref,status,reconcile_status,interface,transaction_date,notification_title,transaction_name,reject_status,created_at,created_by,delete_notes) 
(
SELECT manual_transaction_id,transaction_type,debtor_id,originator_id,currency_id,fx_rate,actual_fx_rate,amount,debtors,reference_to,specific_allocation,notes,transaction_file,specific_deductible,deductible_amount,trailing_balance,trailing_balance_amt,transref_list,reconcile_payment,reconcile_ref,status,reconcile_status,interface,transaction_date,notification_title,transaction_name,0,NOW(),1,''
FROM manual_transactions 
WHERE manual_transaction_id = '4615'
);
DELETE FROM `manual_transactions` WHERE `manual_transaction_id`=1342 LIMIT 1;
DELETE FROM `manual_transactions` WHERE `manual_transaction_id`=4615 LIMIT 1;
UPDATE `manual_transactions` SET `reconcile_ref`=0 WHERE `manual_transaction_id`=179 LIMIT 1;
UPDATE `manual_transactions` SET `reconcile_status`=0 WHERE `manual_transaction_id`=179 LIMIT 1;

INSERT INTO `manual_transactions` (`transaction_type`,`debtors`,`originator_id`,`fx_rate`,`actual_fx_rate`,`amount`,`reference_to`,`specific_allocation`, 
`notes`,`transaction_file`,`specific_deductible`,`deductible_amount`,`trailing_balance`,`trailing_balance_amt`,`transref_list`,`reconcile_payment`,`payment_return`,`status`,`multiple_payments`,
`interface`,`transaction_date`,`issue_date`,`created_at`,`created_by`) 
VALUES('6','1','33','1',0,'14833.309999999999491','230,970','1',
'','','0','','0','','828','179','0',0,'',
1,'2015-06-29','',NOW(),'1');

SET @trans_id= LAST_INSERT_ID();
UPDATE manual_transactions 
SET reconcile_status=1, reconcile_ref = @trans_id 
WHERE manual_transaction_id=179;
UPDATE manual_transactions_invoice SET allocation='1',trailing_status='1' WHERE transaction_id=@trans_id;
UPDATE manual_transactions SET
reconcile_credit=1
WHERE manual_transaction_id IN (828);
UPDATE `invoice_master` 
SET payment_status=1,manual_transaction_id=@trans_id,auto_assign='1' 
WHERE invoice_id IN (230,970);
INSERT INTO manual_transactions_invoice (`transaction_id`, `invoice_id`, `allocation`, `balance_amount`, `reserve_amount`,`trailing_status`, `created_at`, `modified_at`) 
SELECT @trans_id,invoice_id,'1','0','0',1,NOW(),NOW() 
FROM invoice_master
WHERE invoice_id IN (230,970);

UPDATE manual_transactions 
SET reconcile_status=1, reconcile_credit=1 
WHERE manual_transaction_id IN (828);

INSERT INTO invoice_reserve (manual_transaction_id,invoice_id,reserve,created_at) VALUES (@trans_id,230,324.69921875,NOW()),(@trans_id,970,90.49906250000003638,NOW());

UPDATE `manual_transactions` 
SET status='1', authorised_at=NOW(), authorised_by=1, transref_list =  transref_list 
WHERE manual_transaction_id=@trans_id 
LIMIT 1;



#375

UPDATE `manual_transactions` SET `reconcile_ref`=1776 WHERE `manual_transaction_id`=375 LIMIT 1;
UPDATE `manual_transactions` SET `specific_deductible`=1, `deductible_amount`=188.52, `trailing_balance`=0, `trailing_balance_amt`=0 WHERE `manual_transaction_id`=1776 LIMIT 1;

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 AND manual_transactions.manual_transaction_id=1776;

SET @trans_id = LAST_INSERT_ID();

INSERT INTO statements (manual_transaction_id,originator_id)
SELECT manual_transaction_id,originator_id
FROM manual_transactions
WHERE transaction_type=16 AND manual_transaction_id=@trans_id;

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 AND r_trade.manual_transaction_id = 1776 AND sd_trans.manual_transaction_id = @trans_id;

UPDATE `manual_transactions` SET `reference_to`='1002,1003,1004,1010,1027,1050,1075,1077,1087,1088,1089,1102,1103,1130,1132,1136,1137,1138,1158,1279,1287,1298,1319,1320,1322,1323,1409,1444,1461,1463,1531,2597,2599,2627,2703,2704,2705,2706,2713,2714,2845,2846,2847,2868,2949,2950,2951,2971,2988,3031,3032,3033,3034,3050,3150,3151,3152,3191,3192,3193,3211,3214,3215,3326,3327,3328,3358,3432,3433,3434,3458,3459,3460,348,3534,3594,3595,3596,3597,3598,360,362,366,3699,370,3700,3701,372,3731,3732,375,3765,377,3867,3889,3901,3921,3922,4009,4010,4012,4013,4027,4028,4031,410,4126,4188,4193,420,4204,4298,430,4300,4305,4326,4327,4329,4382,4446,4447,4468,4718,4724,4726,4744,4764,4820,4821,4829,4884,4886,4920,4926,4938,4940,4941,5043,5044,5079,5133,5139,5140,5158,5198,5200,5231,5232,5242,5284,5303,5310,5386,5387,5388,5532,5533,5539,5540,5543,5565,5596,5630,5638,5662,5665,570,5790,5791,5798,5799,5822,5823,5855,5915,5920,5935,5936,5937,5990,6037,6038,6044,6045,6067,6184,6185,6192,6208,6209,6211,6254,6310,6311,6315,6317,6334,6336,6337,6442,6457,6465,6466,6501,6502,6503,6535,6581,6582,6587,6588,6626,6628,6687,6693,6723,6724,6755,6779,6780,6788,679,6790,6862,6863,6864,689,6903,6907,6971,6972,704,705,7313,7314,876,884,885,893,894,895,896,901,902,927,928,935,949,950,958,959,976,986' 
WHERE `manual_transaction_id`=6627 
LIMIT 1;

UPDATE manual_transactions SET amount=amount-33.17 WHERE manual_transaction_id=6627;

INSERT INTO manual_transactions (manual_transaction_id,amount,transaction_type,originator_id,debtors,status,interface,transaction_date,created_at)
VALUES(null,33.17,92,102,191,1,1,"2014-12-31",NOW());

SET @trans_id = LAST_INSERT_ID();

INSERT INTO statements (manual_transaction_id,originator_id)
SELECT manual_transaction_id,originator_id
FROM manual_transactions
WHERE manual_transaction_id=@trans_id;

DELETE FROM `invoice_reserve` WHERE `invoice_reserve_id`=13074 LIMIT 1;
UPDATE `invoice_master` SET `manual_transaction_id`=0, `reserve_on`=0 WHERE `invoice_id`=1022 LIMIT 1;
INSERT INTO manual_transactions_rejected (manual_transaction_id,transaction_type,debtor_id,originator_id,currency_id,fx_rate,actual_fx_rate,amount,debtors,reference_to,specific_allocation,notes,transaction_file,specific_deductible,deductible_amount,trailing_balance,trailing_balance_amt,transref_list,reconcile_payment,reconcile_ref,status,reconcile_status,interface,transaction_date,notification_title,transaction_name,reject_status,created_at,created_by,delete_notes) 
(
SELECT manual_transaction_id,transaction_type,debtor_id,originator_id,currency_id,fx_rate,actual_fx_rate,amount,debtors,reference_to,specific_allocation,notes,transaction_file,specific_deductible,deductible_amount,trailing_balance,trailing_balance_amt,transref_list,reconcile_payment,reconcile_ref,status,reconcile_status,interface,transaction_date,notification_title,transaction_name,0,NOW(),1,''
FROM manual_transactions 
WHERE manual_transaction_id = '5160'
);
DELETE FROM `manual_transactions` WHERE `manual_transaction_id`=5160 LIMIT 1;




#2216

UPDATE `manual_transactions` SET `reconcile_ref`=3782 WHERE `manual_transaction_id`=2216 LIMIT 1;
UPDATE `manual_transactions` SET `reference_to`='808,907,967,1056,1155,1304,1414,1535,1695,1934,2175,2331,2503,2529,2962,3118,3245,3529,3761,4128,4402,4644,4907,6219,6255,6525,6719,6894,6996,7444,7649,7931' 
WHERE `manual_transaction_id`=3492 
LIMIT 1;

UPDATE manual_transactions SET amount=amount-65.63 WHERE manual_transaction_id=3492;

INSERT INTO manual_transactions (manual_transaction_id,amount,transaction_type,originator_id,debtors,status,interface,transaction_date,created_at)
VALUES(null,65.63,92,43,75,1,1,"2014-10-07",NOW());

SET @trans_id = LAST_INSERT_ID();

INSERT INTO statements (manual_transaction_id,originator_id)
SELECT manual_transaction_id,originator_id
FROM manual_transactions
WHERE manual_transaction_id=@trans_id;

DELETE FROM `invoice_reserve` WHERE `invoice_reserve_id`=12920 LIMIT 1;
UPDATE `invoice_master` SET `manual_transaction_id`=0, `auto_assign`=0, `reserve_on`=0 WHERE `invoice_id`=5602 LIMIT 1;

INSERT INTO manual_transactions_rejected (manual_transaction_id,transaction_type,debtor_id,originator_id,currency_id,fx_rate,actual_fx_rate,amount,debtors,reference_to,specific_allocation,notes,transaction_file,specific_deductible,deductible_amount,trailing_balance,trailing_balance_amt,transref_list,reconcile_payment,reconcile_ref,status,reconcile_status,interface,transaction_date,notification_title,transaction_name,reject_status,created_at,created_by,delete_notes) 
(
SELECT manual_transaction_id,transaction_type,debtor_id,originator_id,currency_id,fx_rate,actual_fx_rate,amount,debtors,reference_to,specific_allocation,notes,transaction_file,specific_deductible,deductible_amount,trailing_balance,trailing_balance_amt,transref_list,reconcile_payment,reconcile_ref,status,reconcile_status,interface,transaction_date,notification_title,transaction_name,0,NOW(),1,''
FROM manual_transactions 
WHERE manual_transaction_id = '5133'
);
DELETE FROM `manual_transactions` WHERE `manual_transaction_id`=5133 LIMIT 1;


#3130

UPDATE `manual_transactions` SET `reconcile_ref`=3151 WHERE `manual_transaction_id`=3130 LIMIT 1;
UPDATE `manual_transactions` SET `reference_to`='2512,2644,2897,3170,3969,5716,5881,6027,6138,6435,6547,6766,6911' 
WHERE `manual_transaction_id`=6645 
LIMIT 1;

UPDATE manual_transactions SET amount=amount-118.93 WHERE manual_transaction_id=6645;

INSERT INTO manual_transactions (manual_transaction_id,amount,transaction_type,originator_id,debtors,status,interface,transaction_date,created_at)
VALUES(null,118.93,92,46,111,1,1,"2014-12-31",NOW());

SET @trans_id = LAST_INSERT_ID();

INSERT INTO statements (manual_transaction_id,originator_id)
SELECT manual_transaction_id,originator_id
FROM manual_transactions
WHERE manual_transaction_id=@trans_id;

DELETE  FROM invoice_reserve WHERE invoice_id IN (4895,4991,5111);
UPDATE `invoice_master` SET `manual_transaction_id`=0 WHERE `invoice_id`=4895 LIMIT 1;
UPDATE `invoice_master` SET `manual_transaction_id`=0 WHERE `invoice_id`=4991 LIMIT 1;
UPDATE `invoice_master` SET `manual_transaction_id`=0, `reserve_on`=0 WHERE `invoice_id`=5111 LIMIT 1;
UPDATE `invoice_master` SET `reserve_on`=0 WHERE `invoice_id`=4991 LIMIT 1;
UPDATE `invoice_master` SET `reserve_on`=0 WHERE `invoice_id`=4895 LIMIT 1;

INSERT INTO manual_transactions_rejected (manual_transaction_id,transaction_type,debtor_id,originator_id,currency_id,fx_rate,actual_fx_rate,amount,debtors,reference_to,specific_allocation,notes,transaction_file,specific_deductible,deductible_amount,trailing_balance,trailing_balance_amt,transref_list,reconcile_payment,reconcile_ref,status,reconcile_status,interface,transaction_date,notification_title,transaction_name,reject_status,created_at,created_by,delete_notes) 
(
SELECT manual_transaction_id,transaction_type,debtor_id,originator_id,currency_id,fx_rate,actual_fx_rate,amount,debtors,reference_to,specific_allocation,notes,transaction_file,specific_deductible,deductible_amount,trailing_balance,trailing_balance_amt,transref_list,reconcile_payment,reconcile_ref,status,reconcile_status,interface,transaction_date,notification_title,transaction_name,0,NOW(),1,''
FROM manual_transactions 
WHERE manual_transaction_id = '4817'
);
DELETE FROM `manual_transactions` WHERE `manual_transaction_id`=4817 LIMIT 1;


#3239

UPDATE `manual_transactions` SET `reconcile_ref`=3248 WHERE `manual_transaction_id`=3239 LIMIT 1;
UPDATE `manual_transactions` SET `reference_to`='3413,4902,5118,5412,5613,5723,5891,6660,6770,6915,7033,7177,7403,7499,7692,8033,8348,8448,8633,8798' 
WHERE `manual_transaction_id`=6651 
LIMIT 1;

UPDATE manual_transactions SET amount=amount-223.79 WHERE manual_transaction_id=6651;

INSERT INTO manual_transactions (manual_transaction_id,amount,transaction_type,originator_id,debtors,status,interface,transaction_date,created_at)
VALUES(null,223.79,92,46,128,1,1,"2014-12-31",NOW());

SET @trans_id = LAST_INSERT_ID();

INSERT INTO statements (manual_transaction_id,originator_id)
SELECT manual_transaction_id,originator_id
FROM manual_transactions
WHERE manual_transaction_id=@trans_id;

DELETE FROM `invoice_reserve` WHERE `invoice_reserve_id`=11365 LIMIT 1;
UPDATE `invoice_master` SET `manual_transaction_id`=0, `reserve_on`=0 WHERE `invoice_id`=6289 LIMIT 1;

INSERT INTO manual_transactions_rejected (manual_transaction_id,transaction_type,debtor_id,originator_id,currency_id,fx_rate,actual_fx_rate,amount,debtors,reference_to,specific_allocation,notes,transaction_file,specific_deductible,deductible_amount,trailing_balance,trailing_balance_amt,transref_list,reconcile_payment,reconcile_ref,status,reconcile_status,interface,transaction_date,notification_title,transaction_name,reject_status,created_at,created_by,delete_notes) 
(
SELECT manual_transaction_id,transaction_type,debtor_id,originator_id,currency_id,fx_rate,actual_fx_rate,amount,debtors,reference_to,specific_allocation,notes,transaction_file,specific_deductible,deductible_amount,trailing_balance,trailing_balance_amt,transref_list,reconcile_payment,reconcile_ref,status,reconcile_status,interface,transaction_date,notification_title,transaction_name,0,NOW(),1,''
FROM manual_transactions 
WHERE manual_transaction_id = '4744'
);
DELETE FROM `manual_transactions` WHERE `manual_transaction_id`=4744 LIMIT 1;

#9650

DELETE FROM `manual_transactions` WHERE `manual_transaction_id`=9681 LIMIT 1;
DELETE FROM `invoice_reserve` WHERE `invoice_reserve_id`=18610 LIMIT 1;
DELETE FROM `invoice_reserve` WHERE `invoice_reserve_id`=18609 LIMIT 1;

#11477

DELETE FROM `manual_transactions` WHERE `manual_transaction_id`=11479 LIMIT 1;
DELETE FROM `invoice_reserve` WHERE `invoice_reserve_id`=20702 LIMIT 1;
DELETE FROM `invoice_reserve` WHERE `invoice_reserve_id`=20703 LIMIT 1;



#1694


UPDATE `manual_transactions` SET `reconcile_ref`=2609 WHERE `manual_transaction_id`=1694 LIMIT 1;
UPDATE `invoice_master` SET `manual_transaction_id`=0 WHERE `invoice_id`=564 LIMIT 1;
UPDATE `invoice_master` SET `manual_transaction_id`=0 WHERE `invoice_id`=621 LIMIT 1;
UPDATE `invoice_master` SET `manual_transaction_id`=0 WHERE `invoice_id`=652 LIMIT 1;
UPDATE `invoice_master` SET `manual_transaction_id`=0 WHERE `invoice_id`=691 LIMIT 1;
UPDATE `invoice_master` SET `manual_transaction_id`=0 WHERE `invoice_id`=878 LIMIT 1;
UPDATE `invoice_master` SET `manual_transaction_id`=0 WHERE `invoice_id`=904 LIMIT 1;
UPDATE `invoice_master` SET `manual_transaction_id`=0 WHERE `invoice_id`=952 LIMIT 1;
UPDATE `invoice_master` SET `manual_transaction_id`=0 WHERE `invoice_id`=990 LIMIT 1;
UPDATE `invoice_master` SET `manual_transaction_id`=0 WHERE `invoice_id`=1029 LIMIT 1;

DELETE FROM `invoice_reserve` WHERE `invoice_reserve_id`=13640 LIMIT 1;
DELETE FROM `invoice_reserve` WHERE `invoice_reserve_id`=13641 LIMIT 1;
DELETE FROM `invoice_reserve` WHERE `invoice_reserve_id`=13642 LIMIT 1;
DELETE FROM `invoice_reserve` WHERE `invoice_reserve_id`=13643 LIMIT 1;
DELETE FROM `invoice_reserve` WHERE `invoice_reserve_id`=13644 LIMIT 1;
DELETE FROM `invoice_reserve` WHERE `invoice_reserve_id`=13645 LIMIT 1;
DELETE FROM `invoice_reserve` WHERE `invoice_reserve_id`=13646 LIMIT 1;
DELETE FROM `invoice_reserve` WHERE `invoice_reserve_id`=13647 LIMIT 1;
DELETE FROM `invoice_reserve` WHERE `invoice_reserve_id`=13648 LIMIT 1;


INSERT INTO manual_transactions_rejected (manual_transaction_id,transaction_type,debtor_id,originator_id,currency_id,fx_rate,actual_fx_rate,amount,debtors,reference_to,specific_allocation,notes,transaction_file,specific_deductible,deductible_amount,trailing_balance,trailing_balance_amt,transref_list,reconcile_payment,reconcile_ref,status,reconcile_status,interface,transaction_date,notification_title,transaction_name,reject_status,created_at,created_by,delete_notes) 
(
SELECT manual_transaction_id,transaction_type,debtor_id,originator_id,currency_id,fx_rate,actual_fx_rate,amount,debtors,reference_to,specific_allocation,notes,transaction_file,specific_deductible,deductible_amount,trailing_balance,trailing_balance_amt,transref_list,reconcile_payment,reconcile_ref,status,reconcile_status,interface,transaction_date,notification_title,transaction_name,0,NOW(),1,''
FROM manual_transactions 
WHERE manual_transaction_id = '5242'
);
DELETE FROM `manual_transactions` WHERE `manual_transaction_id`=5242 LIMIT 1;



#429

DELETE FROM `manual_transactions` WHERE `manual_transaction_id`=2797 LIMIT 1;
@ Telegram