<?php
/**********************************************************************
***********************************************************************/

//function write_project_sales_order($pi_no, $project_id, $debtor_no, $customer_branch, $reference,
//                                    $payment_terms, $order_date, $sales_person,
//                                    $total, $taxes, $rate_include_taxes, 
//                                    $payment_application_id, $payment_certificate_ref, $payment_certificate_amount,
//                                    $due_date, $comments, $tax_group_id, $pi_taxes)
//{
//
//    $pi_no = write_manual_project_invoice_header($pi_no, $project_id, $debtor_no, $customer_branch, $reference,
//                                                $payment_terms, $order_date, $sales_person,
//                                                $total, $taxes, $rate_include_taxes, 
//                                                $payment_application_id, $payment_certificate_ref, $payment_certificate_amount,
//                                                $due_date, $comments, $tax_group_id, $pi_taxes);
//        
//    return $pi_no;
//}

function write_project_sales_order_header($pso_no, $project_id, $debtor_no, $customer_branch, $reference,
                                                $payment_terms, $order_date, $sales_person,
                                                $total, $taxes, $rate_include_taxes, 
                                                $po_reference, $site_id,
                                                $due_date, $comments, $tax_group_id, $pi_taxes)
{
    begin_transaction();
    
    if($pso_no > 0){        
        update_project_sales_order_header($pso_no, $project_id, $debtor_no, $customer_branch, $reference,
                                                $payment_terms, $order_date, $sales_person,
                                                $total, $taxes, $rate_include_taxes, 
                                                $po_reference, $site_id,
                                                $due_date, $comments, $tax_group_id);
    }else{
        
        $pso_no = add_project_sales_order_header($project_id, $debtor_no, $customer_branch, $reference,
                                                    $payment_terms, $order_date, $sales_person,
                                                    $total, $taxes, $rate_include_taxes, 
                                                    $po_reference, $site_id,
                                                    $due_date, $comments, $tax_group_id);
    }
    
    if(!($pso_no>0)){
        return;
    }
    
//    update_project_sales_order_tax_details($pi_no, $pi_taxes);
    
    commit_transaction();
    
    return $pso_no;
}


function write_project_sales_order_line($pso_no, $pso_line_id, $type, $description, 
                                        $item_id, $order_unit, $order_unit_qty, $conversion_factor, 
                                        $order_unit_rate, $quantity, $line_comment, $tax_group_id, 
                                        $rate_include_taxes)
{
                
    if($pso_line_id > 0){         
        update_project_sales_order_line($pso_line_id, $type, $description, $item_id,
                                            $order_unit, $order_unit_qty, $conversion_factor, 
                                            $order_unit_rate, $quantity, $line_comment);         
    }else{

        $pso_line_id = add_project_sales_order_line($pso_no, $type, $description, $item_id,
                                                    $order_unit, $order_unit_qty, $conversion_factor, 
                                                    $order_unit_rate, $quantity, $line_comment);
    }
    
    $pso_line_tax_total = update_project_sales_order_line_taxes($pso_no, $pso_line_id, $order_unit_rate * $order_unit_qty, $tax_group_id, $rate_include_taxes);

    update_project_sales_order_line_tax_total($pso_line_id, $pso_line_tax_total); 
           
    update_project_sales_order_header_taxes($pso_no);
    
    update_project_sales_order_header_total($pso_no);
}

function update_project_sales_order_header_total($pso_no)
{
    $pso_total = get_project_sales_order_line_totals($pso_no);

    $sql =  "UPDATE ".TB_PREF."project_sales_orders 
                SET total =".db_escape($pso_total)." 
                WHERE order_no=".db_escape($pso_no);
        
    db_query($sql, "Could not update_project_sales_order_header_total");   
}


function update_project_sales_order_header_taxes($pso_no)
{
    delete_project_sales_order_header_taxes($pso_no);    
     
    $pso_tax_total = 0;
    $result = get_project_sales_order_line_tax_totals($pso_no);
    while ($tax_detail = db_fetch($result)){
        add_project_sales_order_tax_detail($pso_no, 0, $tax_detail['tax_type_id'], $tax_detail['tax_total']);
        $pso_tax_total += $tax_detail['tax_total'];        
    }
    
//    $pso_tax_total = get_project_sales_order_tax_total($pso_no);   
//    return $item_tax_total;
    
    $sql =  "UPDATE ".TB_PREF."project_sales_orders 
                SET taxes =".db_escape($pso_tax_total)." 
                WHERE order_no=".db_escape($pso_no);
        
    db_query($sql, "Could not update_project_sales_order_header_taxs");   
}


function delete_project_sales_order_header_taxes($pso_no)
{     
     $sql = "DELETE FROM ".TB_PREF."project_sales_order_taxes  
             WHERE order_no = ".db_escape($pso_no)." AND 
                    pso_line_id = 0";     
     
    db_query($sql, "Could not delete_project_sales_order_header_taxes.");
 
}



function update_project_sales_order_tax_total($pso_no)
{
    $pso_tax_total = get_project_sales_order_taxes($pso_no);
    
    $sql =  "UPDATE ".TB_PREF."project_sales_orders 
                SET taxes =".db_escape($pso_tax_total)." 
                WHERE order_no=".db_escape($pso_no);
        
        db_query($sql, "Could not update_project_sales_order_tax_total");   
}

function get_project_sales_order_line_tax_totals($pso_no)
{
    $sql = "SELECT tax_type_id, SUM(psot.amount) tax_total 
            FROM ".TB_PREF."project_sales_order_taxes psot 
            WHERE psot.order_no=".db_escape($pso_no)." AND 
                    psot.pso_line_id > 0 
            GROUP BY psot.tax_type_id";
    
    return db_query($sql, "Could not get_project_sales_order_line_tax_totals");  

}


function get_project_sales_order_line_totals($pso_no)
{
    $sql = "SELECT SUM(psod.order_unit_rate * psod.order_unit_qty) lines_total 
            FROM ".TB_PREF."project_sales_order_details psod  
            WHERE psod.order_no=".db_escape($pso_no);
    
    $result = db_query($sql, "Could not get_project_sales_order_line_totals");  
    
    $lt = db_fetch($result);
    if(isset($lt['lines_total']) && $lt['lines_total'] > 0){
        return $lt['lines_total'];
    }
    else{
        return 0;
    }

}

//function get_project_sales_order_tax_total($pso_no)
//{
//    $sql = "SELECT SUM(psod.taxes) tax_total 
//            FROM ".TB_PREF."project_sales_order_details psod 
//                    INNER JOIN ".TB_PREF."project_sales_orders pso ON 
//                            psod.order_no = pso.order_no  
//            WHERE pso.order_no=".db_escape($pso_no);
//    
//    $result = db_query($sql, "Could not get_project_sales_order_tax_total");
//    $taxes = db_fetch($result);   
//    
//    if(isset($taxes['tax_total']) && $taxes['tax_total'] > 0){
//        return $taxes['tax_total'];
//    }
//    else{
//        return 0;
//    }
//}

function update_project_sales_order_line_taxes($pso_no, $pso_line_id, $line_total, $tax_group_id, $rate_include_taxes)
{     
    delete_project_sales_order_line_tax_details($pso_line_id);
    
    $item_taxes_array = calculate_common_item_taxes($line_total, $tax_group_id, $rate_include_taxes);
    $item_tax_total = 0;
        
    foreach ($item_taxes_array as $tax_detail) {
        if($tax_detail['rate'] > 0){
            add_project_sales_order_tax_detail($pso_no, $pso_line_id, $tax_detail['tax_type_id'], $tax_detail['Value']);
            $item_tax_total += $tax_detail['Value'];
        }
    }
    
    return $item_tax_total;
}



function update_project_sales_order_line_tax_total($pso_line_id, $pso_line_tax_total)
{
    $sql =  "UPDATE ".TB_PREF."project_sales_order_details 
                SET taxes=".db_escape($pso_line_tax_total)." 
                WHERE id=".db_escape($pso_line_id);
        
        db_query($sql, "Could not update_project_sales_order_line_tax_total");   
}


function delete_project_sales_order_line_tax_details($pso_line_id)
{     
     $sql = "DELETE FROM ".TB_PREF."project_sales_order_taxes  
             WHERE pso_line_id = ".db_escape($pso_line_id);     
     
    db_query($sql, "Could not delete_project_sales_order_line_tax_details.");
 
}


function add_project_sales_order_tax_detail($pso_no, $pso_line_id, $tax_id, $tax_value)
{     
     $sql = "INSERT INTO ".TB_PREF."project_sales_order_taxes 
                   (order_no, pso_line_id, tax_type_id, amount)
             VALUES (".db_escape($pso_no).","
                    .db_escape($pso_line_id).","
                    .db_escape($tax_id).","
                    .db_escape($tax_value).")";              
     
    db_query($sql, "Could not add_project_sales_order_tax_detail.");
 
}

function update_project_sales_order_header($pso_no, $project_id, $debtor_no, $customer_branch, $reference,
                                                $payment_terms, $order_date, $sales_person,
                                                $total, $taxes, $rate_include_taxes, 
                                                $po_reference, $site_id,
                                                $due_date, $comments, $tax_group_id)
        
{
    $updated_by = $_SESSION['wa_current_user']->user;
    $updated_on = date('Y-m-d H:i:s');
    
    $sql = "UPDATE ".TB_PREF."project_sales_orders
            SET 
                project_id=".db_escape($project_id).",
                debtor_no=".db_escape($debtor_no).",
                branch_code=".db_escape($customer_branch).", 
                reference=".db_escape($reference).",
                payment_terms=".db_escape($payment_terms).",    
                ord_date=".db_escape(date2sql($order_date)).", 
                salesman_code =".db_escape($sales_person).",
                rate_include_taxes=".db_escape($rate_include_taxes).",    
                tax_group_id=".db_escape($tax_group_id).", 
                total=".db_escape($total).", 
                taxes =".db_escape($taxes).", 
                po_reference=".db_escape($po_reference).",    
                site_id=".db_escape($site_id).", 
                comments =".db_escape($comments).",    
                due_date =".db_escape(date2sql($due_date)).", 
                updated_by=".db_escape($updated_by).",
                updated_on=".db_escape($updated_on)."                  
            WHERE order_no=".db_escape($pso_no);
    
    db_query($sql);
    return db_num_affected_rows();  
    
}

function add_project_sales_order_header($project_id, $debtor_no, $customer_branch, $reference,
                                            $payment_terms, $order_date, $sales_person,
                                            $total, $taxes, $rate_include_taxes, 
                                            $po_reference, $site_id,
                                            $due_date, $comments, $tax_group_id)
{
    $created_by = $_SESSION['wa_current_user']->user;
    $created_on = date('Y-m-d H:i:s');
    
    $sql = "INSERT INTO ".TB_PREF."project_sales_orders
                    (project_id, debtor_no, 
                    branch_code, reference, 
                    payment_terms, ord_date, 
                    salesman_code, rate_include_taxes,
                    tax_group_id, total, 
                    taxes, po_reference, site_id,
                    due_date, comments,
                    created_by, created_on )         
                VALUES 
                (".db_escape($project_id).", ".db_escape($debtor_no).", 
                    ".db_escape($customer_branch).", ".db_escape($reference).",
                    ".db_escape($payment_terms).", ".db_escape(date2sql($order_date)).",
                    ".db_escape($sales_person).",".db_escape($rate_include_taxes).",
                    ".db_escape($tax_group_id).", ".db_escape($total).", 
                    ".db_escape($taxes).", ".db_escape($po_reference).",".db_escape($site_id).",
                    ".db_escape(date2sql($due_date)).",".db_escape($comments).",
                    ".db_escape($created_by).", ".db_escape($created_on).")";
     
    db_query($sql);
    return db_insert_id();        
}

function add_project_sales_order_line($pso_no, $type, $description, $item_id,
                                        $order_unit, $order_unit_qty, $conversion_factor, 
                                        $order_unit_rate, $quantity, $line_comment)
{
    $created_by = $_SESSION['wa_current_user']->user;
    $created_on = date('Y-m-d H:i:s');
    
     $sql = "INSERT INTO ".TB_PREF."project_sales_order_details
                (order_no, type, 
                description, item_id, 
                order_unit, order_unit_qty, 
                conversion_factor, order_unit_rate,
                quantity, line_comment, 
                created_by, created_on )
            VALUES
                (".db_escape($pso_no).", ".db_escape($type).", 
                 ".db_escape($description).", ".db_escape($item_id).",
                 ".db_escape($order_unit).", ".db_escape($order_unit_qty).",
                 ".db_escape($conversion_factor).", ".db_escape($order_unit_rate).",
                 ".db_escape($quantity).", ".db_escape($line_comment).",
                 ".db_escape($created_by).", ".db_escape($created_on).")";
     
         
    db_query($sql);
    return db_insert_id();    
}

function update_project_sales_order_line($pso_line_id, $type, $description, $item_id,
                                            $order_unit, $order_unit_qty, $conversion_factor, 
                                            $order_unit_rate, $quantity, $line_comment)
{
    $updated_by = $_SESSION['wa_current_user']->user;
    $updated_on = date('Y-m-d H:i:s');
    
    $sql = "UPDATE ".TB_PREF."project_sales_order_details
            SET 
                type=".db_escape($type).",
                description=".db_escape($description).", 
                item_id=".db_escape($item_id).",                    
                order_unit=".db_escape($order_unit).",
                order_unit_rate=".db_escape($order_unit_rate).",  
                order_unit_qty=".db_escape($order_unit_qty).", 
                conversion_factor=".db_escape($conversion_factor).", 
                quantity=".db_escape($quantity).", 
                line_comment=".db_escape($line_comment).",  
                updated_by=".db_escape($updated_by).",
                updated_on=".db_escape($updated_on)."                
            WHERE id=".db_escape($pso_line_id);
     
    db_query($sql);
    return db_num_affected_rows();  
    
}

function get_project_sales_order_header($pi_no)
{
    $sql = "SELECT pso.*,
                    pm.description project_description,
                    tg.name tax_group_name 
            FROM ".TB_PREF."project_sales_orders pso 
                    INNER JOIN ".TB_PREF."projects_master pm ON 
                            pso.project_id = pm.project_id 
                    INNER JOIN ".TB_PREF."tax_groups tg ON 
                            pso.tax_group_id = tg.id 
            WHERE pso.order_no=".db_escape($pi_no);
    
    $result = db_query($sql, "Could not get_project_sales_order_header");
    return db_fetch($result);   
    
}


function get_project_sales_order_details($order_no)
{
    $sql = "SELECT psod.*, u.name unit_name 
            FROM ".TB_PREF."project_sales_order_details psod 
                    LEFT JOIN ".TB_PREF."item_units u  ON 
                            psod.order_unit = u.abbr 
            WHERE order_no=".db_escape($order_no);
    
    return db_query($sql, "Could not get_project_customer_purchase_order_details");
    
}

function get_project_sales_order_line($selected_id)
{
     $sql = "SELECT pod.*              
         
             FROM ".TB_PREF."project_sales_order_details pod 
                      
             WHERE pod.id=".db_escape($selected_id);
     
     $result = db_query($sql, "Could not get_project_sales_order_line");
     return db_fetch($result);
    
}

function delete_project_sales_order_item($pso_line_id)
{
    begin_transaction();
    delete_project_sales_order_line_tax_details($pso_line_id);
    
    $sql = "DELETE FROM ".TB_PREF."project_sales_order_details WHERE id=".db_escape($pso_line_id);
   
    db_query($sql);
    $db_num_affected_rows =  db_num_affected_rows();
    
    commit_transaction();
    
    return $db_num_affected_rows;
}

function get_project_sales_order_taxes($pso_no, $pso_line_id = 0)
{
    global $suppress_tax_rates;
    
    $taxes =  array();
     
    $sql = "SELECT t.*,";
    
    if(isset($suppress_tax_rates)&& $suppress_tax_rates == 1){
        $sql .= " tn.name tax_type_name ";
    }
    else{
        $sql .= " CONCAT(tn.name, ' (', tn.rate, '%)') tax_type_name ";
    }
    
    $sql .= " FROM ".TB_PREF."project_sales_order_taxes t 
                    INNER JOIN ".TB_PREF."tax_types tn ON 
                            t.tax_type_id = tn.id 
            WHERE t.order_no=".db_escape($pso_no)." AND 
                t.pso_line_id = ".db_escape($pso_line_id)." 
            ORDER BY tn.sequence ";
    
    $result = db_query($sql);
    
    while ($row = db_fetch($result)){
        $taxes[] = array("tax_type_id" => $row['tax_type_id'], "Value" => $row['amount'], "tax_type_name" => $row['tax_type_name']);
    }
    return $taxes;
}

?>