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

function save_project_invoice(&$pi_no, $billing_item_id, $description, $tran_quantity, 
                              $unit, $list_price, $unit_price,                        
                              $discount, $tax_group, $project_id, $customer_id, $branch_code,
                              $reference, $payment_term_id, $ord_date, $due_date, 
                                $salesman_code, $pi_detail_row_id)
{
//    $pi_no = get_session_variable("project_invoice_no");
    //$pi_no = 0;

    if($pi_no> 0){
        
    }
    else{
        $project = get_project($project_id);
        $pi_no = add_project_invoice_header($project_id, $customer_id, $branch_code,
                                            $reference, $payment_term_id, $ord_date, $due_date, $salesman_code,
                                            $project['rate_include_taxes']);
//        set_session_variable("project_invoice_no", $pi_no);    
    }
    

   //$pi_detail_row_id = get_session_variable("project_invoice_no");
   //$pi_detail_row_id = 0;
    if($pi_detail_row_id > 0){
        update_project_invoice_details($pi_detail_row_id, $billing_item_id, $description, $tran_quantity, 
                                       $list_price, $unit_price, $discount, $tax_group);                      
                                      
    }
    else{
        $pi_detail_row_id = add_project_invoice_details($pi_no, $billing_item_id, $description, $tran_quantity, 
                                                        $unit, $list_price, $unit_price,                        
                                                        $discount, $tax_group);
    }    
    
    $pi_header = get_project_invoice_header($pi_no);
    $pi_line_tax_total = update_pi_item_tax_details($pi_detail_row_id, $pi_header['rate_include_taxes']);
    update_project_invoice_detail_taxes($pi_line_tax_total, $pi_detail_row_id);
    
    
    
    update_project_invoice_header($pi_no, $project_id, $customer_id, $branch_code,
                                      $payment_term_id, $ord_date, $due_date, $salesman_code);

    
    return $pi_no;
}

function add_project_invoice_header($project_id, $debtor_no, $branch_code, 
                                    $reference, $payment_terms,                        
                                    $ord_date, $due_date, $salesman_code, $rate_include_taxes)
{
     $created_by = $_SESSION['wa_current_user']->user;
     $created_on = date('Y-m-d H:i:s');  
     
     $next_no = get_next_trans_no(ST_PROJECT_INVOICE);
     
     $sql = "INSERT INTO ".TB_PREF."project_invoices
                   (order_no, project_id, 
                    debtor_no, branch_code, 
                    reference, payment_terms, ord_date, 
                    due_date, salesman_code, rate_include_taxes,
                    created_by, created_on)           
             VALUES ("
                    .db_escape($next_no).","
                    .db_escape($project_id).","
                    .db_escape($debtor_no).","
                    .db_escape($branch_code).","
                    .db_escape($reference).","
                    .db_escape($payment_terms).","            
                    .db_escape(date2sql($ord_date)).","
                    .db_escape(date2sql($due_date)).","
                    .db_escape($salesman_code).", "
                    .db_escape($rate_include_taxes).","
                    .db_escape($created_by).","
                    .db_escape($created_on).")";              
     
     db_query($sql, "Project invoice header could not be insert.");
     return db_insert_id();    
}

function add_project_invoice_details($pi_no, $invoicing_item_id, $description, $tran_quantity, 
                                     $unit, $list_price, $unit_price,                        
                                     $discount, $tax_group)
{
        $created_by = $_SESSION['wa_current_user']->user;
        $created_on = date('Y-m-d H:i:s');        
        
	$sql = "INSERT INTO ".TB_PREF."project_invoice_details (
                           order_no, invoicing_item_id, description, quantity, 
                           unit, list_price, unit_price, discount,  
                           tax_group_id, created_by, created_on) 
                VALUES ("
                    .db_escape($pi_no).", ".db_escape($invoicing_item_id).", ".db_escape($description).", "  
                    .db_escape($tran_quantity).", ".db_escape($unit).", " 
                    .db_escape($list_price).", ".db_escape($unit_price).", "
                    .db_escape($discount).", ".db_escape($tax_group).", "                
                    .db_escape($created_by).", ".db_escape($created_on).")";
        
        db_query($sql,"Project Invoice could not be added");        
        return db_insert_id();
}

function update_project_invoice_details($pi_detail_row_id, $invoicing_item_id, $description, $tran_quantity, 
                                        $list_price, $unit_price, $discount, $tax_group)                            
                                          
{
         $updated_by = $_SESSION['wa_current_user']->user;
         $updated_on = date('Y-m-d H:i:s');

        $sql = "UPDATE ".TB_PREF."project_invoice_details 
                SET                     
                    invoicing_item_id=".db_escape($invoicing_item_id).",
                    description=".db_escape($description).",   
                    quantity=".db_escape($tran_quantity).",                    
                    list_price =".db_escape($list_price).",     
                    unit_price =".db_escape($unit_price).",    
                    discount =".db_escape($discount).", 
                    tax_group_id =".db_escape($tax_group).",
                    updated_by=".db_escape($updated_by).",
                    updated_on=".db_escape($updated_on)." 
                WHERE id=".db_escape($pi_detail_row_id);

        db_query($sql,"Selected project invoice could not be updated");
        return db_num_affected_rows();
}

function update_project_invoice_header($pi_no, $project_id, $debtor_no, $branch_code,
                                       $payment_terms, $ord_date, $due_date, $salesman_code)
{
    $pi_total = get_project_invoice_total($pi_no);
    
    $updated_by = $_SESSION['wa_current_user']->user;
    $updated_on = date('Y-m-d H:i:s');
    
    $sql = "UPDATE ".TB_PREF."project_invoices
            SET
                project_id=".db_escape($project_id).", 
                debtor_no=".db_escape($debtor_no).", 
                branch_code=".db_escape($branch_code).",  
                payment_terms=".db_escape($payment_terms).",                 
                ord_date=".db_escape(date2sql($ord_date)).", 
                due_date=".db_escape(date2sql($due_date)).", 
                salesman_code=".db_escape($salesman_code).", 
                total=".db_escape($pi_total['lines_total']).",
                taxes=".db_escape($pi_total['taxes_total']).",
                updated_by=".db_escape($updated_by).",
                updated_on=".db_escape($updated_on)." 
            WHERE order_no=".db_escape($pi_no);
    
    db_query($sql, "Selected project invoice header could not be updated");
    return db_num_affected_rows();     
}

function delete_project_invoice_item($pi_line_id)
{
//        begin_transaction();
        delete_pi_item_tax_details($pi_line_id);
        
        $sql = "DELETE FROM ".TB_PREF."project_invoice_details  
                WHERE id=".db_escape($pi_line_id)."  ";                    

        db_query($sql," Project invoice could not be deleted");    
//        commit_transaction();
        return db_num_affected_rows();
        
        
}

function get_project_invoice_header($inv_no)
{
    $sql = "SELECT pi.*, pm.description project_description                   
                   
            FROM  ".TB_PREF."project_invoices pi,
                    ".TB_PREF."projects_master pm
                   
            WHERE pi.project_id = pm.project_id AND order_no =".db_escape($inv_no);
               
    $result =  db_query($sql, "Could not get project invoice details");
    return db_fetch($result);
}

function get_project_invoice_details($inv_no)
{
    $sql = "SELECT pid.id,                  
                   pbi.code item_code,                   
                   pid.quantity, 
                   pid.unit,
                   pid.unit_price,
                   pid.list_price,
                   pid.discount,
                   tg.name tax_group_name,
                   pid.item_taxes
                   
            FROM  ".TB_PREF."project_invoice_details pid, 
                  ".TB_PREF."project_billing_items pbi,
                  ".TB_PREF."project_invoices pi,
                  ".TB_PREF."tax_groups tg           
                   
            WHERE pi.order_no = pid.order_no AND
                  pid.billing_item_id = pbi.id AND 
                  tg.id = pid.tax_group_id AND
                  pid.order_no =".db_escape($inv_no);          
                     
               
    return db_query($sql, "Could not get project invoice details");
}

function get_project_invoice_line($selected_id)
{
    $sql = "SELECT pid.id,
                   pid.description,
                   pid.invoicing_item_id,                                     
                   pid.quantity, 
                   pid.unit_price,
                   pid.list_price,
                   pid.discount,
                   pid.tax_group_id,
                   pid.item_taxes,
                   pii.total_only,
                   pii.type,
                   pii.deduction,
                   pii.unit 
            FROM ".TB_PREF."project_invoice_details pid 
                    INNER JOIN ".TB_PREF."project_invoicing_items pii ON 
                            pid.invoicing_item_id = pii.id 
                    LEFT JOIN ".TB_PREF."tax_groups tg ON 
                            pid.tax_group_id = tg.id 
            WHERE pid.id=".db_escape($selected_id);
            
    $result = db_query($sql, "Could not get project_invoice_details for edit");
    return db_fetch($result);
    
}
//
//function get_project_billing_item_rate($project_id)
//{
//    $sql = "SELECT SUM(rate)
//            FROM ".TB_PREF."project_billing_items
//            WHERE project_id=".db_escape($project_id);
//
// $result = db_query($sql);
// return db_fetch($result);
//    
//}

function update_project_invoice_detail_taxes($pi_line_tax_total, $selected_id)
{
    $sql = "UPDATE ".TB_PREF."project_invoice_details
            SET item_taxes=".db_escape($pi_line_tax_total)."
            WHERE id=".db_escape($selected_id);
    
    db_query($sql, "Could not update project invoice details taxes");    
    return db_num_affected_rows();
}

function calculate_pi_item_taxes($item, $line_amont, $tax_group_id, $tax_included)
{
    $shipping_cost = 0;
    $items = array();
	$line_amonts = array();
//        if($shipping_cost==null)

    $items[] = $item;
    $line_amonts[] = $line_amont;		

    $taxes = get_tax_for_items($items, $line_amonts, $shipping_cost,
                                   $tax_group_id, $tax_included);

	return $taxes;	
}

function update_pi_item_tax_details($pi_line_id, $tax_included)
{
    $pi_line = get_project_invoice_line($pi_line_id);
    $item = $pi_line['invoicing_item_id'];
    $price = $pi_line['unit_price'];
    $discount = $pi_line['discount'];
    $quantity = $pi_line['quantity'];
    $line_amount = ($price * (1 - $discount/100)) * $quantity;
    $tax_group_id = $pi_line['tax_group_id'];   
    $item_taxes_array = calculate_pi_item_taxes($item, $line_amount, $tax_group_id, $tax_included);
    $item_tax_total = 0;
    
    delete_pi_item_tax_details($pi_line_id);
    foreach ($item_taxes_array as $item_tax_line) {
        add_pi_item_tax_detail($pi_line_id, $item_tax_line['tax_type_id'], $item_tax_line['Value']);
        $item_tax_total += $item_tax_line['Value'];
    }
    return $item_tax_total;
}

function get_project_invoice($inv_no)
{
    $sql = "SELECT pid.id,                  
                   pbi.code item_code,                   
                   pid.quantity, 
                   pid.unit,
                   pid.unit_price,
                   pid.list_price,
                   pid.discount,
                   tg.name tax_group_name,
                   pid.item_taxes
                   
            FROM  ".TB_PREF."project_invoice_details pid, 
                  ".TB_PREF."project_invoicing_items pbi,
                  ".TB_PREF."project_invoices pi,
                  ".TB_PREF."tax_groups tg           
                   
            WHERE pi.order_no = pid.order_no AND
                  pid.invoicing_item_id = pbi.id AND 
                  tg.id = pid.tax_group_id AND
                  pid.order_no =".db_escape($inv_no)."
                      
            LIMIT 1"; 
                          
 $result = db_query($sql, "Could not get project invoice details");  
 return db_fetch($result); 
    
}

function add_pi_item_tax_detail($pi_line_id, $tax_id, $tax_value)
{     
     $sql = "INSERT INTO ".TB_PREF."project_invoice_item_taxes
                   (pi_line_id, tax_type_id, amount)
             VALUES ("
                    .db_escape($pi_line_id).","
                    .db_escape($tax_id).","
                    .db_escape($tax_value).")";              
     
    db_query($sql, "Could not add_pi_item_tax_detail.");
 
}


function delete_pi_item_tax_details($pi_line_id)
{     
     $sql = "DELETE FROM ".TB_PREF."project_invoice_item_taxes 
             WHERE pi_line_id = ".db_escape($pi_line_id);     
     
    db_query($sql, "Could not delete_pi_item_tax_details.");
 
}

function get_project_invoice_tax_totals($pi_no)
{
    $sql = "SELECT tt.name, tt.rate, it.tax_type_id, SUM(amount) tax_amount 
            FROM ".TB_PREF."project_invoice_item_taxes it, 
                ".TB_PREF."project_invoice_details pid,
                ".TB_PREF."tax_types tt                 
            WHERE it.pi_line_id = pid.id AND it.tax_type_id = tt.id  AND pid.order_no = ".db_escape($pi_no)." 
            GROUP BY tax_type_id";
    
    return db_query($sql, "Could not get_project_invoice_tax_totals");  
  
}

//
//function check_item_in_project_invoice($rec_id, $order_no, $billing_item_id)
//{
//    $sql = "SELECT id 
//            FROM ".TB_PREF."project_invoice_details 
//            WHERE id <> ".db_escape($rec_id)." AND 
//                    order_no = ".db_escape($order_no)." AND 
//                    billing_item_id = ".db_escape($billing_item_id)." LIMIT 1";
//    
//    $result = db_query($sql, "Could not get_project_invoice_tax_totals"); 
//    $item = db_fetch($result);
//    if(isset($item['id']) && $item['id'] > 0){
//        return true;
//    }
//    else{
//        return false;
//    }
//}

function get_project_invoice_detail_item($order_no, $billing_item_id, $invoice_detail_id = null)
{
    $sql = "SELECT pid.*, tg.name tax_group_name  
            FROM ".TB_PREF."project_invoice_details pid 
                    LEFT JOIN ".TB_PREF."tax_groups tg  ON 
                            pid.tax_group_id = tg.id 
            WHERE  pid.order_no = ".db_escape($order_no)." AND 
                    pid.invoicing_item_id = ".db_escape($billing_item_id);
    
    if($invoice_detail_id){
        $sql .= " AND pid.id = ".db_escape($invoice_detail_id);
    }
    
    $result = db_query($sql, "Could not get_project_invoice_detail_item");  
    
    return db_fetch($result);
}


function get_project_invoice_item_totals($order_no)
{
    $sql = "SELECT SUM(unit_price * (1 - (discount/100)) * quantity) lines_total, SUM(item_taxes) taxes_total  
            FROM ".TB_PREF."project_invoice_details pid 
            WHERE pid.order_no = ".db_escape($order_no);
    
    $result = db_query($sql, "Could not get_project_invoice_item_totals");  
    
    return db_fetch($result);
}

function get_project_invoice_total($order_no)
{
//    if($tax_included === null){
//        $pi = get_project_invoice_header($order_no);
//        $tax_included = $pi['rate_include_taxes'];
//    }
//    
    $pi_lines_totals = get_project_invoice_item_totals($order_no);
    

    return array("lines_total" => $pi_lines_totals['lines_total'], "taxes_total" => $pi_lines_totals['taxes_total']);
    
}


function get_project_invoice_header_record_by_reference($reference)
{
    $sql = "SELECT pi.*                   
                   
            FROM  ".TB_PREF."project_invoices pi 
                   
            WHERE pi.reference = ".db_escape($reference);
               
    $result =  db_query($sql, "get_project_invoice_header_record_by_reference");
    return db_fetch($result);
}

?>