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



function write_utility_bill_cn_header($ubcn_no, $reference, $project_id, $supplier_id,
                                        $date, $rate_include_taxes, $tax_group_id)                                       
{
    global $Refs;

    if($ubcn_no > 0){        
        update_utility_bill_cn_header($ubcn_no, $reference, $project_id, $supplier_id,
                                        $date, $rate_include_taxes, $tax_group_id);
    }else{
        
        $ubcn_no = add_utility_bill_cn_header($reference, $project_id, $supplier_id,
                                        $date, $rate_include_taxes, $tax_group_id);
        
        $Refs->save(ST_UTILITY_BILL_CREDIT_NOTE, $ubcn_no, $reference);
    }

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


function write_utility_bill_cn_detail($ubcn_no, $ubd_line_id, $bill_no, 
                                        $bill_reference, $description, $amount,
                                        $period_from, $period_to, $task_id, $expense_type_id,
                                        $rate_include_taxes, $tax_group_id,
                                        $expense_category_id, $expense_item_id, $project_site_id)
{            
    if($ubd_line_id > 0){         
        $n = update_utility_bill_cn_detail($ubd_line_id, $bill_no, 
                                        $bill_reference, $description, $amount,
                                        $period_from, $period_to, $task_id, $expense_type_id,
                                        $expense_category_id, $expense_item_id, $project_site_id); 
        if($n != 1){
           return array("success" => false, "message" => "Another user has updated bill detail $ubd_line_id.");  
        }
    }
    else{
        add_utility_bill_cn_detail($ubcn_no, $bill_no, 
                                        $bill_reference, $description, $amount,
                                        $period_from, $period_to, $task_id, $expense_type_id,
                                        $expense_category_id, $expense_item_id, $project_site_id);
    }
    
    update_utility_bill_cn_taxes($ubcn_no, $tax_group_id, $rate_include_taxes);
    
    return array("success" => true, "message" =>"Utility Bill Credit Note $ubcn_no has been updated.");
    
}

function update_utility_bill_cn_taxes($ubcn_no, $tax_group_id, $rate_include_taxes)
{
    $ub_lines_total = get_ub_cn_lines_total($ubcn_no);
    
    $taxes_array = calculate_common_item_taxes($ub_lines_total, $tax_group_id, $rate_include_taxes);
    
    $ub_all_taxs = update_utility_bill_cn_header_taxes($ubcn_no, $taxes_array);
    $ub_taxes_total = $ub_all_taxs["taxes"];
    $ub_staxes_total = $ub_all_taxs["staxes"];
    
    update_utility_bill_cn_header_totals($ubcn_no, $ub_taxes_total, $rate_include_taxes, $ub_staxes_total);
    
}


function update_utility_bill_cn_taxes_manual($ubcn_no, $taxes_array)
{
    $ubh = get_utility_bill_cn_header($ubcn_no);
    
    $ub_all_taxs = update_utility_bill_cn_header_taxes($ubcn_no, $taxes_array);
    $ub_taxes_total = $ub_all_taxs["taxes"];
    $ub_staxes_total = $ub_all_taxs["staxes"];
    
    update_utility_bill_cn_header_totals($ubcn_no, $ub_taxes_total, $ubh['rate_include_taxes'], $ub_staxes_total);
          
}

function update_utility_bill_cn_header_totals($ubcn_no, $ub_taxes_total, $rate_include_taxes, $ub_staxes_total)
{
    $ub_line_total = get_ub_cn_lines_total($ubcn_no);
    if($rate_include_taxes){
        $tax_free_total = $ub_line_total - ($ub_taxes_total + $ub_staxes_total);
    }
    else{
        $tax_free_total = $ub_line_total;
    }

    $sql =  "UPDATE ".TB_PREF."utility_bill_credit_notes 
                SET total =".db_escape($tax_free_total).",
                    taxes =".db_escape($ub_taxes_total).", 
                    staxes =".db_escape($ub_staxes_total)." 
                WHERE id=".db_escape($ubcn_no);
        
    db_query($sql, "Could not update_utility_bills_cn_header_total");   
}

function get_ub_cn_lines_total($ubcn_no)
{
    $sql = "SELECT SUM(ubd.amount) lines_amount_total
            FROM ".TB_PREF."utility_bill_credit_note_details ubd
            WHERE ubd.ubcn_id=".db_escape($ubcn_no);
    
    $result = db_query($sql, "Could not get_ub_cn_lines_total");
    
    $ubl_detail = db_fetch($result);
        
    if(isset($ubl_detail['lines_amount_total']) && $ubl_detail['lines_amount_total'] > 0){
        return $ubl_detail['lines_amount_total'];    
    }else{
        return 0;
    }  
}


//function update_utility_bills_line_taxes($ubcn_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($ubcn_no, $ubd_line_id, $tax_detail['tax_type_id'], $tax_detail['Value']);
//            $item_tax_total += $tax_detail['Value'];
//        }
//    }
//    
//    return $item_tax_total;
//}



function add_utility_bill_cn_tax_detail($ubcn_no, $tax_id, $rate, $tax_value, $tax_type_name, $suspended)
{    
    $sql = "INSERT INTO ".TB_PREF."utility_bill_credit_note_taxes 
                   (ubcn_id, tax_type_id, rate, amount, tax_type_name, suspended)
             VALUES (".db_escape($ubcn_no).","
                    .db_escape($tax_id).","
                    .db_escape($rate).","
                    .db_escape($tax_value).","
                    .db_escape($tax_type_name)."," 
                    .db_escape($suspended).")"; 
     
    db_query($sql, "Could not add_utility_bills_tax_detail.");
 
}

//function update_utility_bills_line_tax_total($ubd_line_id, $ub_line_tax_total)
//{
//    $sql =  "UPDATE ".TB_PREF."utility_bill_credit_note_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_utility_bill_cn_header_taxes($ubcn_no, $taxes_array)
{
    delete_utility_bill_cn_header_taxes($ubcn_no);    
     
    $ub_tax_total = $ub_stax_total = 0;
//    $result = get_utility_bill_line_tax_totals($ubcn_no);
    foreach ($taxes_array as $tax_detail) {
        if($tax_detail['Value'] > 0){
            add_utility_bill_cn_tax_detail($ubcn_no, $tax_detail['tax_type_id'], $tax_detail['rate'], $tax_detail['Value'], $tax_detail['tax_type_name'],
                                           $tax_detail['suspended']);
            if($tax_detail['suspended'] == 1){
                $ub_stax_total += $tax_detail['Value'];
            }
            else{
                $ub_tax_total += $tax_detail['Value'];  
            }
        }
            
    }
    
//    $ub_tax_total = get_project_sales_order_tax_total($ubcn_no);   
//    return $item_tax_total;
    
//    $sql =  "UPDATE ".TB_PREF."utility_bill_credit_notes 
//                SET taxes =".db_escape($ub_tax_total)." 
//                WHERE id=".db_escape($ubcn_no);
        
//    db_query($sql, "Could not update_utility_bills_header_taxs");  
    
    return array("taxes" => $ub_tax_total, "staxes" => $ub_stax_total);
}


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


function delete_utility_bill_cn_header_taxes($ubcn_no)
{     
    $sql = "DELETE FROM ".TB_PREF."utility_bill_credit_note_taxes  
            WHERE ubcn_id = ".db_escape($ubcn_no);
     
    db_query($sql, "Could not delete_utility_bill_cn_header_taxes.");
 
}


function update_utility_bill_cn_header($ubcn_no, $reference, $project_id, $supplier_id,
                                        $date, $rate_include_taxes, $tax_group_id)
{
    $updated_by = $_SESSION['wa_current_user']->user;
    $updated_on = date('Y-m-d H:i:s');
    
    $sql = "UPDATE ".TB_PREF."utility_bill_credit_notes
            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).",    
                updated_by=".db_escape($updated_by).",
                updated_on=".db_escape($updated_on)."       
                
            WHERE id=".db_escape($ubcn_no);
    
    db_query($sql);
    return db_num_affected_rows();  
    
}

function add_utility_bill_cn_header($reference, $project_id, $supplier_id,
                                    $date, $rate_include_taxes, $tax_group_id)
{
    $created_by = $_SESSION['wa_current_user']->user;
    $created_on = date('Y-m-d H:i:s');
    
    $next_no = get_next_trans_no(ST_UTILITY_BILL_CREDIT_NOTE);
    
    $sql = "INSERT INTO ".TB_PREF."utility_bill_credit_notes
                  ( id, reference, project_id,  
                    supplier_id, date,
                    rate_include_taxes, tax_group_id,                   
                    created_by, created_on )         
                VALUES 
                (".db_escape($next_no).", ".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($created_by).", ".db_escape($created_on).")";
     
    db_query($sql);
    return db_insert_id();        
}


function add_utility_bill_cn_detail($ubcn_no, $bill_no, 
                                    $bill_reference, $description, $amount,
                                    $period_from, $period_to, $task_id, $expense_type_id,
                                    $expense_category_id, $expense_item_id, $project_site_id)
{
    $created_by = $_SESSION['wa_current_user']->user;
    $created_on = date('Y-m-d H:i:s');
    
     $sql = "INSERT INTO ".TB_PREF."utility_bill_credit_note_details 
                      ( ubcn_id, bill_no, 
                        bill_reference, description, 
                        amount, period_from, period_to, 
                        task_id, expense_type_id, 
                        expense_category_id, expense_item_id,
                        project_site_id,
                        created_by, created_on )                
            VALUES (".db_escape($ubcn_no).", ".db_escape($bill_no).","
                        .db_escape($bill_reference).", ".db_escape($description).",".db_escape($amount).","
                        .db_escape(date2sql($period_from)).", ".db_escape(date2sql($period_to)).","
                        .db_escape($task_id).", ".db_escape($expense_type_id).","
                        .db_escape($expense_category_id).", ".db_escape($expense_item_id).","
                        .db_escape($project_site_id).", "
                        .db_escape($created_by).", ".db_escape($created_on).")";
             
    db_query($sql);
       
}

function update_utility_bill_cn_detail($ubd_line_id, $bill_no, 
                                        $bill_reference, $description, $amount,
                                        $period_from, $period_to, $task_id, $expense_type_id,
                                        $expense_category_id, $expense_item_id, $project_site_id)
{
    $updated_by = $_SESSION['wa_current_user']->user;
    $updated_on = date('Y-m-d H:i:s');
    
     $sql = "UPDATE ".TB_PREF."utility_bill_credit_note_details
                SET 
                    bill_no=".db_escape($bill_no).",
                    bill_reference=".db_escape($bill_reference).",
                    description=".db_escape($description).",
                    amount=".db_escape($amount).",
                    period_from=".db_escape(date2sql($period_from)).",
                    period_to=".db_escape(date2sql($period_to)).",
                    task_id=".db_escape($task_id).",
                    expense_type_id=".db_escape($expense_type_id).",
                    expense_category_id=".db_escape($expense_category_id).",
                    expense_item_id=".db_escape($expense_item_id).",
                    project_site_id=".db_escape($project_site_id).",
                    updated_by=".db_escape($updated_by).",
                    updated_on=".db_escape($updated_on)."                  
                        
                WHERE id=".db_escape($ubd_line_id);
     
    db_query($sql);
    return db_num_affected_rows();  
    
}

function get_utility_bill_cn_header($ubcn_no)
{
    $sql = "SELECT ub.*,
                    pm.description project_description
        
            FROM ".TB_PREF."utility_bill_credit_notes ub
                LEFT JOIN ".TB_PREF."projects_master pm ON
                    ub.project_id = pm.project_id
                
            WHERE ub.id=".db_escape($ubcn_no);
    
    $result = db_query($sql);
    return db_fetch($result);   
    
}


function get_utility_bill_cn_details($order_no)
{
    $sql = "SELECT ubd.*,
                    pt.description budget_description,
                    uet.description expense_type,
                    ps.description project_site_description 
                    
        
            FROM ".TB_PREF."utility_bill_credit_note_details ubd
                    LEFT JOIN ".TB_PREF."project_tasks pt ON 
                            ubd.task_id = pt.task_id                             
                    LEFT JOIN ".TB_PREF."utility_expense_types uet ON
                            ubd.expense_type_id = uet.id
                    LEFT JOIN ".TB_PREF."project_sites ps ON
                            ubd.project_site_id = ps.id
                            
            WHERE ubd.ubcn_id=".db_escape($order_no)." 
            ORDER BY id";
    
    return db_query($sql, "Could not get_utility_bill_cn_details.");
    
}

function get_utility_bill_cn_line($selected_id)
{
     $sql = "SELECT ubd.*                     
         
             FROM ".TB_PREF."utility_bill_credit_note_details ubd 
                      
             WHERE ubd.id=".db_escape($selected_id);
     
     $result = db_query($sql);
     return db_fetch($result);
    
}

function delete_utility_bill_cn_item($ub_line_id)
{  
    $sql = "DELETE FROM ".TB_PREF."utility_bill_credit_note_details WHERE id=".db_escape($ub_line_id);
    db_query($sql);

    return db_num_affected_rows();
    
}

function get_utility_bill_cn_tax_type_amount($ubcn_no, $tax_type_id)
{
    $sql = "SELECT amount 
            FROM ".TB_PREF."utility_bill_credit_note_taxes 
            WHERE ubcn_id=".db_escape($ubcn_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_utility_bill_cn_line_count($ubcn_no)
{
    $sql = "SELECT COUNT(id) line_count 
            FROM ".TB_PREF."utility_bill_credit_note_details 
            WHERE ubcn_id =".db_escape($ubcn_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_utility_bill_cn($ubcn_no)
{    
    $task_costs = array();
    $current_user_id = $_SESSION['wa_current_user']->user;
    $trans_type = ST_UTILITY_BILL_CREDIT_NOTE;
    
    $ubh = get_utility_bill_cn_header($ubcn_no);
    $date_ = sql2date($ubh['date']);
    
    $ubh_total_incl = $ubh['total'] + $ubh['taxes'];
    $ubh_total_excl = $ubh['total'];
    $tax_factor = $ubh_total_excl/$ubh_total_incl;
    
    $ub_details = get_utility_bill_cn_details($ubcn_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($ubh['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, $ubcn_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" => $ubcn_no, "message" => $info['message']);
    }
    
    if($info['approval_status'] != AS_APPROVED){
        return array("success" => $info['success'], "trans_no" => $ubcn_no, "message" => $info['message']);
    }
    
    $cct_msg = charge_cost_to_tasks($trans_type, $ubcn_no, $task_costs, $current_user_id);
    
    if($cct_msg['success'] !== true)
    {
        return array("success" => $cct_msg['success'], "tran_no" => $ubcn_no, "message" => $cct_msg['message']);
    }
    
    
    /* Financial entries */
    
    if(exists_supp_trans($trans_type, $ubcn_no)){
        return array("success" => false, "message" => "Utility Bill $ubcn_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, $ubcn_no, $ubh['supplier_id'], $date_, sql2date($ubh['date']), $ubh['reference'], '', 
                     -$ubh_total_excl, -$ubh['taxes'], 0, "", 0, $ubh['rate_include_taxes'], 0, 0, $ubh['tax_group_id'], -$ubh['staxes']);
    
    /**********
    $taxes = get_utility_bill_tax_details($ubcn_no);
    foreach ($taxes as $taxitem)
    {
		if ($taxitem['amount'] != 0)
		{
			add_trans_tax_details($trans_type, $ubcn_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, $ubcn_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, $ubcn_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($ubcn_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" => $ubcn_no, "message" => $message);
        }

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



//function get_utility_bill_tax_details($ubcn_no)
//{
//    $sql = "SELECT ub_tax.*, tax_types.purchasing_gl_code   
//            FROM ".TB_PREF."utility_bill_credit_note_taxes ub_tax 
//                    JOIN ".TB_PREF."tax_types tax_types 
//            WHERE ub_id = ".db_escape($ubcn_no)." AND 
//                    ub_tax.tax_type_id = tax_types.id ";
//     
//    $result = db_query($sql, "Could not get_utility_bill_tax_details.");
//
//	while ($tax_group_item = db_fetch_assoc($result)) 
//	{
//		$tax_group_item['Value'] = $tax_group_item['amount'];
//		$ret_tax_array[$tax_group_item['tax_type_id']] = $tax_group_item;
//	}
//
//	return $ret_tax_array;     
//}



?>