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


function write_sc_purchase_order_header($scpo_no, $reference, $project_id, $supplier_id,
                                        $date, $rate_include_taxes, $tax_group_id,
                                        $approval_status, $agreement_reference, 
                                        $comment, $payment_terms)                          
{
    global $Refs;
    
    if($scpo_no > 0){        
        update_sc_purchase_order_header($scpo_no, $reference, $project_id, $supplier_id,
                                        $date, $rate_include_taxes, $tax_group_id,
                                        $approval_status, $agreement_reference, 
                                        $comment, $payment_terms);
    }else{
        
        $scpo_no = add_sc_purchase_order_header($reference, $project_id, $supplier_id,
                                        $date, $rate_include_taxes, $tax_group_id,
                                        $approval_status, $agreement_reference, 
                                        $comment, $payment_terms);
        $Refs->save(ST_SUBCONTRACTOR_PURCH_ORDER, $scpo_no, $reference);
    }

    if(!($scpo_no > 0)){
        return;
    }    
    
    return $scpo_no;
}


function write_sc_purchase_order_detail($scpo_no, $pod_line_id, $item_type, 
                                        $description, $boq_item_id, $unit_id, $quantity, $rate,
                                        $period_from, $period_to, 
                                        $task_id, $comment, 
                                        $tax_group_id, $rate_include_taxes)
{            
    if($pod_line_id > 0){         
        $n = update_sc_purchase_order_detail($pod_line_id, $item_type, $description,
                                        $boq_item_id, $unit_id, $quantity, $rate,
                                        $period_from, $period_to, 
                                        $task_id, $comment);    
        if($n != 1){
           return array("success" => false, "message" => "Another user has updated subcontractor purchase order detail $pod_line_id.");  
        }
    }
    else{
        add_sc_purchase_order_detail($scpo_no, $item_type, $description,
                                    $boq_item_id, $unit_id, $quantity, $rate,
                                    $period_from, $period_to, 
                                    $task_id, $comment); 
    }
    
    update_sc_purchase_order_taxes($scpo_no, $tax_group_id, $rate_include_taxes);
    
    return array("success" => true, "message" =>"Subcontractor purchase order $scpo_no has been updated.");
    
}

function update_sc_purchase_order_taxes($scpo_no, $tax_group_id, $rate_include_taxes)
{
    $po_lines_total = get_sc_purchase_order_lines_total($scpo_no);
    
    $taxes_array = calculate_common_item_taxes($po_lines_total, $tax_group_id, $rate_include_taxes);
    
    $po_tax_total = update_sc_purchase_order_header_taxes($scpo_no, $taxes_array);
    
    update_sc_purchase_order_header_totals($scpo_no, $po_tax_total, $rate_include_taxes);
    
}


function update_sc_purchase_order_taxes_manual($scpo_no, $taxes_array)
{
    $poh = get_sc_purchase_orders_header($scpo_no);
    
    $po_tax_total = update_sc_purchase_order_header_taxes($scpo_no, $taxes_array);
    
    update_sc_purchase_order_header_totals($scpo_no, $po_tax_total, $poh['rate_include_taxes']);
          
}


function update_sc_purchase_order_header_totals($scpo_no, $po_tax_total, $rate_include_taxes)
{
    $po_line_total = get_sc_purchase_order_lines_total($scpo_no);
    if($rate_include_taxes){
        $tax_free_total = $po_line_total - $po_tax_total;
    }
    else{
        $tax_free_total = $po_line_total;
    }

    $sql =  "UPDATE ".TB_PREF."subcontractor_purchase_orders 
                SET total =".db_escape($tax_free_total).",
                    taxes =".db_escape($po_tax_total)." 
                WHERE id=".db_escape($scpo_no);
        
    db_query($sql, "Could not update_sc_purchase_order_header_totals");   
}

function get_sc_purchase_order_lines_total($scpo_no)
{
    $sql = "SELECT SUM(spod.quantity * rate) lines_amount_total
            FROM ".TB_PREF."subcontractor_purchase_order_details spod
            WHERE spod.sc_po_id=".db_escape($scpo_no);
    
    $result = db_query($sql, "Could not get_sc_purchase_order_lines_total");
    
    $po_detail = db_fetch($result);
        
    if(isset($po_detail['lines_amount_total']) && $po_detail['lines_amount_total'] > 0){
        return $po_detail['lines_amount_total'];    
    }else{
        return 0;
    }
  
}


//function update_utility_bills_line_taxes($scpo_no, $ubd_line_id, $line_total, $tax_group_id, $rate_include_taxes)
//{     
//    delete_utility_bills_line_tax_details($ubd_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_utility_bills_tax_detail($scpo_no, $ubd_line_id, $tax_detail['tax_type_id'], $tax_detail['Value']);
//            $item_tax_total += $tax_detail['Value'];
//        }
//    }
//    
//    return $item_tax_total;
//}



function add_sc_purchase_order_tax_detail($scpo_no, $tax_type_id, $rate, $tax_value)
{    
    $sql = "INSERT INTO ".TB_PREF."subcontractor_purchase_order_taxes 
                   (sc_po_id, tax_type_id, rate, amount)
            VALUES 
            (".db_escape($scpo_no).","
                .db_escape($tax_type_id).","
                .db_escape($rate).","
                .db_escape($tax_value).")";              
     
    db_query($sql, "Could not add_sc_purchase_order_tax_detail.");
 
}

//function update_utility_bills_line_tax_total($ubd_line_id, $ub_line_tax_total)
//{
//    $sql =  "UPDATE ".TB_PREF."utility_bill_details 
//                SET taxes=".db_escape($ub_line_tax_total)." 
//                WHERE id=".db_escape($ubd_line_id);
//        
//        db_query($sql, "Could not update_utility_bills_line_tax_total");   
//}

function update_sc_purchase_order_header_taxes($scpo_no, $taxes_array)
{
    delete_sc_purchase_order_header_taxes($scpo_no);    
     
    $po_tax_total = 0;
//    $result = get_utility_bill_line_tax_totals($scpo_no);
    foreach ($taxes_array as $tax_detail) {
        if($tax_detail['Value'] > 0){
            add_sc_purchase_order_tax_detail($scpo_no, $tax_detail['tax_type_id'], $tax_detail['rate'], $tax_detail['Value']);
            $po_tax_total += $tax_detail['Value'];  
        }
            
    }
    
//    $po_tax_total = get_project_sales_order_tax_total($scpo_no);   
//    return $item_tax_total;
//    
//    $sql =  "UPDATE ".TB_PREF."utility_bills 
//                SET taxes =".db_escape($po_tax_total)." 
//                WHERE id=".db_escape($scpo_no);
//        
//    db_query($sql, "Could not update_utility_bills_header_taxs");  
    
    return $po_tax_total;
}


//function get_utility_bill_line_tax_totals($scpo_no)
//{
//    $sql = "SELECT tax_type_id, SUM(ubt.amount) tax_total 
//            FROM ".TB_PREF."utility_bill_taxes ubt 
//            WHERE ubt.ub_id=".db_escape($scpo_no)."                   
//            GROUP BY ubt.tax_type_id";
//    
//    return db_query($sql, "Could not get_utility_bills_line_tax_totals");  

//}


function delete_sc_purchase_order_header_taxes($scpo_no)
{     
    $sql = "DELETE FROM ".TB_PREF."subcontractor_purchase_order_taxes  
            WHERE sc_po_id = ".db_escape($scpo_no);
     
    db_query($sql, "Could not delete_sc_purchase_order_header_taxes.");
 
}


function update_sc_purchase_order_header($scpo_no, $reference, $project_id, $supplier_id,
                                        $date, $rate_include_taxes, $tax_group_id,
                                        $approval_status, $agreement_reference
                                        ,$comment, $payment_terms)
{
    $updated_by = $_SESSION['wa_current_user']->user;
    $updated_on = date('Y-m-d H:i:s');
    
    $sql = "UPDATE ".TB_PREF."subcontractor_purchase_orders
            SET 
                reference=".db_escape($reference).",
                project_id=".db_escape($project_id).",
                supplier_id=".db_escape($supplier_id).",
                date=".db_escape(date2sql($date)).",
                rate_include_taxes=".db_escape($rate_include_taxes).",    
                tax_group_id=".db_escape($tax_group_id).",    
                approval_status=".db_escape($approval_status).",   
                agreement_reference=".db_escape($agreement_reference).", 
                payment_terms=".db_escape($payment_terms).", 
                comment=".db_escape($comment).", 
                updated_by=".db_escape($updated_by).",
                updated_on=".db_escape($updated_on)."       
                
            WHERE id=".db_escape($scpo_no);
    
    db_query($sql);
    return db_num_affected_rows();  
    
}


function add_sc_purchase_order_header($reference, $project_id, $supplier_id,
                                        $date, $rate_include_taxes, $tax_group_id,
                                        $approval_status, $agreement_reference,
                                        $comment, $payment_terms)
{
    $created_by = $_SESSION['wa_current_user']->user;
    $created_on = date('Y-m-d H:i:s');
    
    $sql = "INSERT INTO ".TB_PREF."subcontractor_purchase_orders
                  ( reference, project_id,  
                    supplier_id, date,
                    rate_include_taxes, tax_group_id,  
                    approval_status, agreement_reference, 
                    payment_terms, comment,
                    created_by, created_on )         
                VALUES 
                (".db_escape($reference).", ".db_escape($project_id).", 
                    ".db_escape($supplier_id).", ".db_escape(date2sql($date)).",
                    ".db_escape($rate_include_taxes).", ".db_escape($tax_group_id).",
                    ".db_escape($approval_status).", ".db_escape($agreement_reference).", 
                    ".db_escape($payment_terms).", ".db_escape($comment).", 
                    ".db_escape($created_by).", ".db_escape($created_on).")";
     
    db_query($sql);
    return db_insert_id();        
}


function add_sc_purchase_order_detail($scpo_no, $item_type, $description,
                                    $boq_item_id, $unit_id, $quantity, $rate,
                                    $period_from, $period_to, 
                                    $task_id, $comment)
{
    $created_by = $_SESSION['wa_current_user']->user;
    $created_on = date('Y-m-d H:i:s');
    
    $sql = "INSERT INTO ".TB_PREF."subcontractor_purchase_order_details 
                      ( sc_po_id, item_type, description,
                        boq_item_id, unit, quantity, rate,
                        period_from, period_to,
                        task_id, comment,
                        created_by, created_on )                
            VALUES (".db_escape($scpo_no).", ".db_escape($item_type).",".db_escape($description).","
                        .db_escape($boq_item_id).", ".db_escape($unit_id).", "
                        .db_escape($quantity).",".db_escape($rate).","
                        .db_escape(date2sql($period_from)).", ".db_escape(date2sql($period_to)).","
                        .db_escape($task_id).", "
                        .db_escape($comment).","
                        .db_escape($created_by).", ".db_escape($created_on).")";
             
    db_query($sql);
       
}

function update_sc_purchase_order_detail($spod_line_id, $item_type, $description,
                                        $boq_item_id, $unit_id, $quantity, $rate,
                                        $period_from, $period_to, 
                                        $task_id, $comment)
{
    $updated_by = $_SESSION['wa_current_user']->user;
    $updated_on = date('Y-m-d H:i:s');
    
     $sql = "UPDATE ".TB_PREF."subcontractor_purchase_order_details
                SET 
                    item_type=".db_escape($item_type).",
                    description=".db_escape($description).",
                    boq_item_id=".db_escape($boq_item_id).",
                    unit=".db_escape($unit_id).",
                    quantity=".db_escape($quantity).",
                    rate=".db_escape($rate).",
                    period_from=".db_escape(date2sql($period_from)).",
                    period_to=".db_escape(date2sql($period_to)).",
                    task_id=".db_escape($task_id).",
                    comment=".db_escape($comment).",
                    updated_by=".db_escape($updated_by).",
                    updated_on=".db_escape($updated_on)."                  
                        
                WHERE id=".db_escape($spod_line_id);
     
    db_query($sql);
    return db_num_affected_rows();  
    
}


function get_sc_purchase_orders_header($scpo_no)
{
    $sql = "SELECT spo.*,
                    pm.description project_description,
                    sup.supp_name,
                    tg.name tax_group_name  
        
            FROM ".TB_PREF."subcontractor_purchase_orders spo
                JOIN ".TB_PREF."projects_master pm ON
                    spo.project_id = pm.project_id 
                JOIN ".TB_PREF."suppliers sup ON
                    spo.supplier_id = sup.supplier_id 
                JOIN ".TB_PREF."tax_groups tg ON
                    spo.tax_group_id = tg.id 
                
            WHERE spo.id=".db_escape($scpo_no);
    
    $result = db_query($sql);
    return db_fetch($result);   
    
}

function get_sc_purchase_orders_header_record($scpo_no)
{
    $sql = "SELECT * 
            FROM ".TB_PREF."subcontractor_purchase_orders spo 
            WHERE spo.id=".db_escape($scpo_no);
    
    $result = db_query($sql);
    return db_fetch($result);   
    
}

function get_sc_purchase_order_details($order_no)
{
    $sql = "SELECT spod.*,
                    pt.description budget_description,
                    pt.task_code budget_code,
                    pbi.description boq_item_description,
                    pbi.code boq_item_code,
                    iu.name unit_name
        
            FROM ".TB_PREF."subcontractor_purchase_order_details spod
                    LEFT JOIN ".TB_PREF."project_tasks pt ON 
                            spod.task_id = pt.task_id
                            
                    LEFT JOIN ".TB_PREF."project_boq_items pbi ON
                            spod.boq_item_id = pbi.id
                            
                    LEFT JOIN ".TB_PREF."item_units iu ON
                            spod.unit = iu.abbr
                                              
            WHERE spod.sc_po_id=".db_escape($order_no)." 
            ORDER BY id";
    
    return db_query($sql, "Could not get_sc_purchase_order_details.");
    
}


function get_sc_purchase_order_line_records($order_no)
{
    $sql = "SELECT spod.*          
            FROM ".TB_PREF."subcontractor_purchase_order_details spod  
            WHERE spod.sc_po_id=".db_escape($order_no)." 
            ORDER BY id";
    
    return db_query($sql, "Could not get_sc_purchase_order_details.");
    
}



function get_sc_purchase_orders_line($selected_id)
{
     $sql = "SELECT spod.*,
                    pbi.group_id item_boq_group_id,
                    ptg.boq_group_id task_boq_group_id 
         
            FROM ".TB_PREF."subcontractor_purchase_order_details spod 
                LEFT JOIN ".TB_PREF."project_boq_items pbi ON
                    spod.boq_item_id = pbi.id 
                LEFT JOIN ".TB_PREF."project_tasks pt ON
                    spod.task_id = pt.task_id 
                LEFT JOIN ".TB_PREF."project_task_groups ptg ON
                    pt.task_group_id = ptg.id 
                    


                      
             WHERE spod.id=".db_escape($selected_id);
     
     $result = db_query($sql);
     return db_fetch($result);
    
}

function get_sc_purchase_order_line_record($selected_id)
{
     $sql = "SELECT spod.* 
            FROM ".TB_PREF."subcontractor_purchase_order_details spod   
            WHERE spod.id=".db_escape($selected_id);
     
     $result = db_query($sql);
     return db_fetch($result);
    
}

function delete_sc_purchase_order_details($scpo_detail_id)
{  
    $sql = "DELETE FROM ".TB_PREF."subcontractor_purchase_order_details WHERE id=".db_escape($scpo_detail_id);
    db_query($sql);

    return db_num_affected_rows();
    
}


function get_sc_purchase_orders_tax_type_amount($scpo_no, $tax_type_id)
{
    $sql = "SELECT amount 
            FROM ".TB_PREF."subcontractor_purchase_order_taxes 
            WHERE sc_po_id=".db_escape($scpo_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 get_sc_purchase_orders_line_count($scpo_no)
{
    $sql = "SELECT COUNT(id) line_count 
            FROM ".TB_PREF."subcontractor_purchase_order_details 
            WHERE sc_po_id = ".db_escape($scpo_no);
    
    $result = db_query($sql);
    $count =  db_fetch($result);   
    if(isset($count['line_count']) && $count['line_count'] > 0){
        return $count['line_count'];
    }
    
    return 0;
}


function release_sc_purchase_order111($scpo_no)
{
    $task_costs = array();
    $current_user_id = $_SESSION['wa_current_user']->user;
    $trans_type = ST_SUBCONTRACTOR_PURCH_ORDER;
    
    $scpoh = get_sc_purchase_orders_header($scpo_no);
    $date_ = sql2date($scpoh['date']);
    
    $scpoh_total_with_taxes = $scpoh['total'] + $scpoh['taxes'];
    $scpoh_total_no_taxes = $scpoh['total'];
    $tax_factor = $scpoh_total_no_taxes/$scpoh_total_with_taxes;
    
    $ub_details = get_sc_purchase_order_details($scpo_no);
    
    while ($myrow = db_fetch($ub_details))
    {
        $a['row_id'] = $myrow['id'];
        $a['task_id'] = $myrow['task_id'];
        $a['requested_qty'] = 1;
        $a['high_value_item'] = 0;
        
        if($scpoh['rate_include_taxes']){
            $line_tax_free_amount = $myrow['amount'] * $tax_factor;
        }
        else{
            $line_tax_free_amount = $myrow['amount'];
        }
        $a['reference_cost'] = $line_tax_free_amount;
        $a['standard_cost'] = $line_tax_free_amount;
        $a['total_issued_qty'] = 0;
        $a['qty'] = 1;
        
        $ub_details_array[] = $a;
        
    }
    
    $task_costs = calculate_task_remaining_totals($ub_details_array);

    $info = approve_document($trans_type, $scpo_no, $ubh['project_id'], $task_costs, 
                                    $ubh['next_approval_group_id'], $current_user_id, $ubh['row_state'], $ubh['created_by']);
        
    if($info['success'] !== true){
        return array("success" => $info['success'],"trans_no" => $scpo_no, "message" => $info['message']);
    }
    
    if($info['approval_status'] != AS_APPROVED){
        return array("success" => $info['success'], "trans_no" => $scpo_no, "message" => $info['message']);
    }
    
    $cct_msg = charge_cost_to_tasks($trans_type, $scpo_no, $task_costs, $current_user_id);
    
    if($cct_msg['success'] !== true)
    {
        return array("success" => $cct_msg['success'], "tran_no" => $scpo_no, "message" => $cct_msg['message']);
    }
    
    
    /* Financial entries */
    
    if(exists_supp_trans($trans_type, $scpo_no)){
        return array("success" => false, "message" => "Utility Bill $scpo_no is already in the Supplier's transactions list.");
    }
    
    $total = 0;
    $supplier = get_supplier($ubh['supplier_id']);
    $ex_rate = get_exchange_rate_from_home_currency($supplier['curr_code'], $date_);
    
    write_supp_trans($trans_type, $scpo_no, $ubh['supplier_id'], $date_, sql2date($ubh['date']),
                                    $ubh['reference'], '',
                                    $ubh_total_excl, $ubh['taxes'], 0, "", 0, $ubh['rate_include_taxes']);

    /**********
    $taxes = get_utility_bill_tax_details($scpo_no);
    foreach ($taxes as $taxitem)
    {
		if ($taxitem['amount'] != 0)
		{
			add_trans_tax_details($trans_type, $scpo_no, $taxitem['tax_type_id'], $taxitem['rate'], $ubh['rate_include_taxes'], 
                                    $taxitem['amount'], $ubh_total_excl, $ex_rate, $date_, $ubh['reference']);

			$total += add_gl_trans_supplier($trans_type, $scpo_no, $date_,
                                            $taxitem['purchasing_gl_code'], 0, 0, $taxitem['amount'],
                                            $ubh['supplier_id'],
                                            "A general ledger transaction for the tax amount could not be added");
		}
    }
    
    add_gl_trans_supplier($trans_type, $scpo_no, $date_, $supplier["payable_account"], 0, 0,
                                    -($ubh_total_incl), $ubh['supplier_id'],
                                    "The general ledger transaction for the control total could not be added");
    
    $ub_details2 = get_utility_bill_details($scpo_no);
    
    while ($ub_detail = db_fetch($ub_details2))
    {
        $ub_line_expense_type = get_utility_expense_type($ub_detail['expense_type_id']);
        $ub_exp_act = $ub_line_expense_type['debit_account'];
        if(!(isset($ub_exp_act) && strlen($ub_exp_act) > 0)){
            $message = "Debit account not defined for Utility bill expense type ".$ub_detail['name'];
            return array("success" => false, "tran_no" => $scpo_no, "message" => $message);
        }

        $memo_ = $ub_detail['description'];
        $line_tax_free_amount = $ub_detail['amount'] * $tax_factor;
        $total += add_gl_trans_supplier($trans_type, $scpo_no, $date_, $ub_exp_act,
            0, 0, $line_tax_free_amount , $ubh['supplier_id'], "", 0, $memo_);
    }
     * 
     */
    return array("success" => true, "tran_no" => $scpo_no, "message" => $info['message']);
}



function release_sc_purchase_order($scpo_no)
{
    $current_user_id = $_SESSION['wa_current_user']->user;
    
    $scpoh = get_sc_purchase_orders_header($scpo_no);
    
//    $scpoh_total_with_taxes = $scpoh['total'] + $scpoh['taxes'];
    
    $tasks_summary = calculate_sc_po_task_remaining_totals($scpo_no); 
    
    $info = approve_document(ST_SUBCONTRACTOR_PURCH_ORDER, $scpo_no, $scpoh['project_id'], 
                        $tasks_summary, $scpoh['next_approval_group_id'], 
                        $current_user_id, $scpoh['row_state']);

    if($info['success'] !== true){
        cancel_transaction();
        return array("success" => $info['success'],"tran_no" => $scpo_no, "message" => $info['message']);
    }

    if($info['approval_status'] == AS_APPROVED)
    {            
        $apc_msg = allocate_to_tasks(ST_SUBCONTRACTOR_PURCH_ORDER, $scpo_no, 
                                    $tasks_summary, $current_user_id);
        if($apc_msg['success'] !== true)
        {
            cancel_transaction();
            return array("success" => $apc_msg['success'], "tran_no" => $scpo_no, "message" => $info['message']);
        }
    }
    commit_transaction();
    return array("success" => true, "tran_no" => $scpo_no, "message" => $info['message']);
        
}

function calculate_sc_po_task_remaining_totals($sc_po_id)
{
        $task_costs = array();
        
        $result = get_sc_po_task_remaining_totals($sc_po_id);
        
        while($row = db_fetch($result))
        {            
            if($row['task_cost'] >= 0){
                add_to_task_cost($task_costs, $row['task_id'], $row['task_cost'], $row['task_cost'], $row['task_cost']);
            }
        }
        
        get_task_cost_details($task_costs);
        
        return $task_costs;
}

function get_sc_po_task_remaining_totals($sc_po_id)
{
    $sql = "SELECT task_id, SUM((quantity - invoiced_qty) * rate) task_cost 
            FROM ".TB_PREF."subcontractor_purchase_order_details 
            WHERE sc_po_id = ".db_escape($sc_po_id)." 
            GROUP BY task_id";
    
    return db_query($sql);
}

function update_sc_po_line_inviced_qty($po_line_id, $total_invoiced_qty)
{
    $sql = "UPDATE ".TB_PREF."subcontractor_purchase_order_details
                SET invoiced_qty = ".db_escape($total_invoiced_qty)."                  
                        
                WHERE id=".db_escape($po_line_id);
     
    db_query($sql);
}


function update_sc_po_closed($scpo_no)
{
    $sql = "UPDATE ".TB_PREF."subcontractor_purchase_orders
                SET approval_status = ".db_escape(AS_CLOSED)."                  
                        
                WHERE id=".db_escape($scpo_no)." AND 
                        approval_status = ".db_escape(AS_APPROVED);     
    
    db_query($sql);
}



?>