<?php

global $path_to_root;


function cs_get_customer_allocation_to_sum()
{        
        $sql = "SELECT SUM(ca.amt) total_allocated, ca.trans_type_to trans_type, ca.trans_no_to trans_no,
			dt.alloc 
                FROM ".TB_PREF."cust_allocations ca    
                    JOIN ".TB_PREF."debtor_trans dt 
                        ON dt.trans_no = ca.trans_no_to 
                        AND dt.type = ca.trans_type_to 
                WHERE dt.alloc <>                  
                                    (SELECT SUM(amt) ta 
                                    FROM ".TB_PREF."cust_allocations sca 
                                    WHERE sca.trans_type_to = ca.trans_type_to AND sca.trans_no_to = ca.trans_no_to  
                                    GROUP BY sca.trans_type_to, sca.trans_no_to)  
                                    
		GROUP BY ca.trans_type_to, ca.trans_no_to 
                LIMIT 1000";
                
	return db_query($sql,"cs_get_customer_allocation_to_sum"); 
}

function cs_get_customer_allocation_from_sum()
{        
        $sql = "SELECT SUM(ca.amt) total_allocated, ca.trans_type_from trans_type, ca.trans_no_from trans_no,
			dt.alloc 
                FROM ".TB_PREF."cust_allocations ca    
                JOIN ".TB_PREF."debtor_trans dt ON 
                dt.trans_no = ca.trans_no_from 
                AND dt.type = ca.trans_type_from 
                WHERE dt.alloc  <>                 
                        (SELECT SUM(amt) ta 
                	FROM ".TB_PREF."cust_allocations sca 
                        WHERE sca.trans_type_from = ca.trans_type_from AND sca.trans_no_from = ca.trans_no_from  
                        GROUP BY sca.trans_type_from, sca.trans_no_from)     
		GROUP BY ca.trans_type_from, ca.trans_no_from  
                LIMIT 1000";
                
	return db_query($sql,"cs_get_customer_allocation_from_sum"); 
}

function cs_update_cust_trans_allocation($trans_type, $trans_no, $total_allocated)
{
//    return;
    
    $sql = "UPDATE ".TB_PREF."debtor_trans    
            SET alloc =".db_escape($total_allocated). " 
            WHERE type = ".db_escape($trans_type)."  
                AND trans_no =".db_escape($trans_no);
    
    db_query($sql,"cs_update_cust_trans_allocation");
}


function cs_custom_script_update_customer_allocations()
{
    global $systypes_array;
    
    $message = "==================Script Sarted==========================";
    write_custom_script_log("cs_update_customer_allocations", $message);
    
    
//    $invs = cs_get_customer_allocation_to_sum();
    $invs = cs_get_customer_allocation_from_sum();
    $recs = db_num_rows($invs);
    write_custom_script_log("cs_update_customer_allocations", $recs." Records selected");
    
    while ($row = db_fetch($invs)) {
        $cust_trans = get_customer_trans_record($row['trans_type'], $row['trans_no']);
        if($cust_trans['alloc'] != $row['total_allocated']){
            cs_update_cust_trans_allocation($row['trans_type'], $row['trans_no'], $row['total_allocated']);
        
            $n = db_num_affected_rows();
            if($n==1){
                $message = $systypes_array[$row['trans_type']]." ".$row['trans_no']." allocation amount ".$row['total_allocated']." updated";
            }
            else{
                $message = "Could not update ".$systypes_array[$row['trans_type']]." ".$row['trans_no']." allocation amount ".$row['total_allocated'];
            }
        }
        else{
            $message = "No need to update ".$systypes_array[$row['trans_type']]." ".$row['trans_no']." allocation amount ".$row['total_allocated']." matches";
        }
        write_custom_script_log("cs_update_customer_allocations", $message);
    }
    
    $message = "------------------Script Completed----------------------------";
    write_custom_script_log("cs_update_customer_allocations", $message);
}

?>