<?php

$path_to_root = "../..";
include_once($path_to_root . "/config_db.php");
$db_connection = $db_connections[0];
$con = mysqli_connect($db_connection['host'], $db_connection['dbuser'], $db_connection['dbpassword'], $db_connection['dbname']);

if (mysqli_connect_errno()) {
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
    exit();
}

if (isset($_GET["Go"])) {
    $sql = "select * from 0_debtor_trans where order_ IN (select order_no from 0_sales_orders where debtor_no='717' and branch_code='666')";
    $result = mysqli_query($con, $sql);
    while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
        clear_cust_alloctions($row['type'],$row['trans_no']);
        if(mysqli_query($con, "UPDATE 0_debtor_trans set debtor_no='717', branch_code='666' where type=" . $row['type']." and trans_no=".$row['trans_no'])){
            echo 'Update debtor trans type:'.$row['type']." trans_no:".$row['trans_no']."<br/>";
        }
    }
}

mysqli_close($con);
function clear_cust_alloctions($type, $type_no){
    global $con;
    $sql = "UPDATE  0_cust_allocations ca
				LEFT JOIN 0_debtor_trans paym ON ca.trans_type_from=paym.type AND ca.trans_no_from=paym.trans_no AND ca.person_id=paym.debtor_no
				LEFT JOIN 0_debtor_trans dt ON ca.trans_type_to=dt.type AND ca.trans_no_to=dt.trans_no AND ca.person_id=dt.debtor_no
				LEFT JOIN 0_sales_orders so ON ca.trans_type_to=so.trans_type AND ca.trans_no_to=so.order_no AND ca.person_id=so.debtor_no
	 		SET paym.alloc=paym.alloc - ca.amt,
	 			dt.alloc=dt.alloc -  ca.amt,
	 			so.alloc=so.alloc -  ca.amt
			WHERE  ((ca.trans_type_from=".($type)." AND ca.trans_no_from=".($type_no).")
				OR (ca.trans_type_to=".($type)." AND ca.trans_no_to=".($type_no)."))";
	
        if(mysqli_query($con, $sql)){
            echo 'Reset Allocation type:'.$type." trans_no:".$type_no."<br/>";
        }

	// remove any allocations for this transaction
	$sql = "DELETE FROM 0_cust_allocations
			WHERE  ((trans_type_from=".($type)." AND trans_no_from=".($type_no).")
				OR (trans_type_to=".($type)." AND trans_no_to=".($type_no)."))";
	if(mysqli_query($con, $sql)){
            echo 'Clear Allocation type:'.$type." trans_no:".$type_no."<br/>";
        }
}
