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

function write_manual_project_invoice($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, $project_sales_order_id)
{

    $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, $project_sales_order_id);
        
    return $pi_no;
}

function 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, 
                                                $project_sales_order_id, $staxes)
{    
    begin_transaction();
    
    if($pi_no > 0){        
        update_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, 
                                                $project_sales_order_id, $staxes);
    }else{
            
        global $Refs;
        $pi_no = add_manual_project_invoice_header($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, 
                                                    $project_sales_order_id, $staxes);
        
        $Refs->save(ST_PROJECT_INVOICE, $pi_no, $reference);
    }
    
    update_manual_project_invoice_tax_details($pi_no, $pi_taxes);
    
    commit_transaction();
    
    return $pi_no;
}


function write_manual_project_invoice_detail($pi_no, $pi_detail_row_id, $type, 
                                                $sequence, $text, $completed_date, $unit, 
                                                $quantity, $rate, $amount, $line_comment,
                                                $category_id, $debit_account, $credit_account)
{
                
    if($pi_detail_row_id > 0){         
        update_manual_project_invoice_details($pi_detail_row_id, $type, 
                                                $sequence, $text, $completed_date, $unit, 
                                                $quantity, $rate, $amount, $line_comment,
                                                $category_id, $debit_account, $credit_account);     
    }else{

        $pi_detail_row_id = add_manual_project_invoice_details($pi_no, $type, 
                                                                $sequence, $text, $completed_date, $unit, 
                                                                $quantity, $rate, $amount, $line_comment,
                                                                $category_id, $debit_account, $credit_account);
    }
          
}

function update_manual_project_invoice_tax_details($pi_no, $pi_taxes)
{   
    delete_manual_project_invoice_tax_details($pi_no);
    foreach ($pi_taxes as $tax_detail) {
        add_manual_project_invoice_tax_detail($pi_no, $tax_detail['tax_type_id'], $tax_detail['tax_amount'], $tax_detail['suspended']);
    }
}


function delete_manual_project_invoice_tax_details($pi_no)
{     
     $sql = "DELETE FROM ".TB_PREF."project_invoice_taxes  
             WHERE order_no = ".db_escape($pi_no);     
     
    db_query($sql, "Could not delete_manual_project_invoice_tax_details.");
 
}


function add_manual_project_invoice_tax_detail($pi_no, $tax_id, $tax_value, $suspended)
{     
     $sql = "INSERT INTO ".TB_PREF."project_invoice_taxes 
                   (order_no, tax_type_id, 
                   amount, suspended)
             VALUES ("
                    .db_escape($pi_no).","
                    .db_escape($tax_id).","
                    .db_escape($tax_value).","            
                    .db_escape($suspended).")";              
     
    db_query($sql, "Could not add_manual_project_invoice_tax_detail.");
 
}

function update_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, $project_sales_order_id, $staxes)
{
    $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($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).", 
                staxes =".db_escape($staxes).", 
                payment_application_id=".db_escape($payment_application_id).",    
                payment_certificate_ref=".db_escape($payment_certificate_ref).", 
                payment_certificate_amount =".db_escape($payment_certificate_amount).",
                project_sales_order_id=".db_escape($project_sales_order_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($pi_no)." 
                AND approval_status IN (".AS_NEW.",".AS_AWAITING_APPROVAL.",".AS_WITH_THE_APPROVER.",".AS_BACK_TO_ORIGINATOR.") ";
    
    db_query($sql);
    return db_num_affected_rows();  
    
}

function add_manual_project_invoice_header($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, $project_sales_order_id, $staxes)
{
    $created_by = $_SESSION['wa_current_user']->user;
    $created_on = date('Y-m-d H:i:s');
    
    $sql = "INSERT INTO ".TB_PREF."project_invoices
                    (project_id, debtor_no, 
                    branch_code, reference, 
                    payment_terms, ord_date, 
                    salesman_code, rate_include_taxes,
                    tax_group_id, total, 
                    taxes, staxes,
                    payment_application_id, payment_certificate_ref, payment_certificate_amount,
                    due_date, project_sales_order_id, 
                    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($staxes).",          
                    ".db_escape($payment_application_id).", ".db_escape($payment_certificate_ref).",".db_escape($payment_certificate_amount).",
                    ".db_escape(date2sql($due_date)).",".db_escape($project_sales_order_id).",
                    ".db_escape($comments).",
                    ".db_escape($created_by).", ".db_escape($created_on).")";
     
    db_query($sql);
    return db_insert_id();        
}

function add_manual_project_invoice_details($pi_no, $type, $sequence, $text, $completed_date, $unit, 
                                            $quantity, $rate, $amount, $line_comment,
                                            $category_id, $debit_account, $credit_account)
{
    $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, line_type, 
                        sequence, description, 
                        completed_date, unit, quantity, 
                        rate, amount, line_comment,
                        category_id, debit_account, credit_account,
                        created_by, created_on )                
            VALUES (".db_escape($pi_no).", ".db_escape($type).","
                        .db_escape($sequence).", ".db_escape($text).",".db_escape($completed_date).","
                        .db_escape($unit).", ".db_escape($quantity).","
                        .db_escape($rate).", ".db_escape($amount).",".db_escape($line_comment).","
                        .db_escape($category_id).", ".db_escape($debit_account).",".db_escape($credit_account).","
                        .db_escape($created_by).", ".db_escape($created_on).")";
     
         
    db_query($sql);
    return db_insert_id();    
}

function update_manual_project_invoice_details($pi_detail_row_id, $type, $sequence, $text, $completed_date, $unit, 
                                                $quantity, $rate, $amount, $lne_comment,
                                                $category_id, $debit_account, $credit_account)
{
    $updated_by = $_SESSION['wa_current_user']->user;
    $updated_on = date('Y-m-d H:i:s');
    
     $sql = "UPDATE ".TB_PREF."project_invoice_details
                SET 
                    line_type=".db_escape($type).",
                    sequence=".db_escape($sequence).",
                    description=".db_escape($text).", 
                    completed_date=".db_escape($completed_date).", 
                    unit=".db_escape($unit).",
                    quantity=".db_escape($quantity).",
                    rate=".db_escape($rate).",
                    amount=".db_escape($amount).",
                    line_comment=".db_escape($lne_comment).",
                    category_id=".db_escape($category_id).",
                    debit_account=".db_escape($debit_account).",
                    credit_account=".db_escape($credit_account).",
                    updated_by=".db_escape($updated_by).",
                    updated_on=".db_escape($updated_on)."                  
                        
                WHERE id=".db_escape($pi_detail_row_id);
     
    db_query($sql);
    return db_num_affected_rows();  
    
}

function get_manual_project_invoice_header($pi_no)
{
    $sql = "SELECT *
            FROM ".TB_PREF."project_invoices
            WHERE order_no=".db_escape($pi_no);
    
    $result = db_query($sql);
    return db_fetch($result);   
    
}


function get_manual_project_invoice_details($order_no)
{
    $sql = "SELECT pid.*, 
                    u.name unit_name,
                    cn.value_name category_name,
                    da.account_name debit_account_name,
                    ca.account_name credit_account_name 
            FROM ".TB_PREF."project_invoice_details pid 
                    LEFT JOIN ".TB_PREF."item_units u ON 
                            pid.unit = u.abbr 
                    LEFT JOIN ".TB_PREF."list_values cn ON 
                            pid.category_id = cn.id 
                    LEFT JOIN ".TB_PREF."chart_master da ON 
                            pid.debit_account = da.account_code  
                    LEFT JOIN ".TB_PREF."chart_master ca ON 
                            pid.credit_account = ca.account_code  
                    
            WHERE order_no=".db_escape($order_no)." 
            ORDER BY sequence";
    
    return db_query($sql, "Could not get manual project invoices");
    
}

function get_manual_project_invoice_line($selected_id)
{
     $sql = "SELECT pid.*                     
         
             FROM ".TB_PREF."project_invoice_details pid 
                      
             WHERE pid.id=".db_escape($selected_id);
     
     $result = db_query($sql);
     return db_fetch($result);
    
}

function get_manual_project_invoice_line_count($mpi_id)
{
     $sql = "SELECT COUNT(pid.id) num_rows                    
         
             FROM ".TB_PREF."project_invoice_details pid 
                      
             WHERE pid.order_no=".db_escape($mpi_id);
     
     $result = db_query($sql);
     $row = db_fetch($result);
     if(isset($row['num_rows']) && $row['num_rows'] > 0){
         return $row['num_rows'];
     }else{
         return 0;
     }
    
}


function delete_manual_project_invoice_item($selected_id)
{
   $sql = "DELETE FROM ".TB_PREF."project_invoice_details WHERE id=".db_escape($selected_id);
   
   db_query($sql);
   return db_num_affected_rows();
    
}

function get_manual_project_invoice_tax_type_amount($pi_no, $tax_type_id)
{
    $sql = "SELECT amount 
            FROM ".TB_PREF."project_invoice_taxes 
            WHERE order_no=".db_escape($pi_no)." AND 
                tax_type_id = ".db_escape($tax_type_id);
    
    $result = db_query($sql);
    $amt =  db_fetch($result);   
    $amount = 0;
    if(isset($amt['amount']) && $amt['amount'] > 0){
        $amount = $amt['amount'];
    }
    
    return $amount;
}


function copy_and_save_manual_project_invoice_details($new_pi_no, $old_pi_no) {
    
    global $systypes_array;
    
    $old_result = get_manual_project_invoice_details($old_pi_no);
        while ($row = db_fetch($old_result)) {
            add_manual_project_invoice_details($new_pi_no, $row['line_type'], $row['sequence'], $row['description'], $row['completed_date'], $row['unit'],
                                               $row['quantity'], $row['rate'], $row['amount'], $row['line_comment'], $row['category_id'], 
                                               $row['debit_account'], $row['credit_account']);
            
            if (db_num_affected_rows() != 1) {
                cancel_transaction();
                
                return array('success' => false, 'message' => "Could not be copied ".$systypes_array[ST_PROJECT_INVOICE]." #$old_pi_no.");
            }
        }
        

        
        return array('success' => true, 'message' => $systypes_array[ST_PROJECT_INVOICE]." #$old_pi_no has been copied to new ".$systypes_array[ST_PROJECT_INVOICE]." #$new_pi_no.");
}
