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


function write_subcontractor_credit_note_header($sccn_no, $reference, $project_id, $supplier_id,   
                                                    $ord_date, $supp_ref, $sc_invoice_id,
                                                    $total, $taxes_total,                            
                                                    $tax_group_id, $sccn_taxes, $staxes_total)
{
//    begin_transaction();
  
    global $Refs;
         
    if($sccn_no > 0){        
        update_subcontractor_credit_note_header($sccn_no, $reference, 
                                                    $ord_date, $supp_ref, 
                                                    $total, $taxes_total, $tax_group_id, $staxes_total);                           
                                            
    }else{
          
        
        $sccn_no = add_subcontractor_credit_note_header($reference, $project_id, $supplier_id,   
                                                    $ord_date, $supp_ref, $sc_invoice_id,
                                                    $total, $taxes_total, $tax_group_id, $staxes_total); 
        
      
        $Refs->save(ST_SUBCONTRACTOR_CREDIT_NOTE, $sccn_no, $reference);
                                                                                                       
    }
    
    update_subcontractor_credit_note_tax_details($sccn_no, $sccn_taxes);
        
//    commit_transaction();
    
    return $sccn_no;
}


function write_subcontractor_credit_note_detail($sccn_no, $order_no, $line_type, $item_type, 
                                                $sequence, $item_id, $text, $completed_date, $unit, 
                                                $quantity, $rate, $amount, $line_comment,
                                                $task_id, $category_id, $debit_account, $credit_account,
                                                $invoice_line_id)
{  
    global $systypes_array;
    
    if($order_no > 0){         
        update_subcontractor_credit_note_details($order_no, $line_type, $item_type,
                                                $sequence, $item_id, $text, $completed_date, $unit, 
                                                $quantity, $rate, $amount, $line_comment,
                                                $task_id, $category_id, $debit_account, $credit_account,
                                                $invoice_line_id);     
        return array("success" => true, "message" => "Selected detail line has been updated.");
    }
    else{

        $sccn_detail_id = add_subcontractor_credit_note_details($sccn_no, $line_type, $item_type, 
                                                                $sequence, $item_id, $text, $completed_date, $unit, 
                                                                $quantity, $rate, $amount, $line_comment,
                                                                $task_id, $category_id, $debit_account, $credit_account,
                                                                $invoice_line_id);
        
        return array("success" => true, "message" => "New detail line has been added to ".$systypes_array[ST_SUBCONTRACTOR_CREDIT_NOTE]." #".$sccn_detail_id);
    }
          
}


function update_subcontractor_credit_note_tax_details($sccn_no, $sccn_taxes)
{   
    delete_subcontractor_credit_note_tax_details($sccn_no);
    foreach ($sccn_taxes as $tax_detail) {
        add_subcontractor_credit_note_tax_detail($sccn_no, $tax_detail['tax_type_id'], $tax_detail['tax_amount']);
    }
}


function delete_subcontractor_credit_note_tax_details($sccn_no)
{     
     $sql = "DELETE FROM ".TB_PREF."subcontractor_credit_note_taxes  
             WHERE order_no = ".db_escape($sccn_no);     
     
    db_query($sql, "Could not delete_subcontractor_credit_note_tax_details.");
 
}


function add_subcontractor_credit_note_tax_detail($sccn_no, $tax_id, $tax_value)
{     
     $sql = "INSERT INTO ".TB_PREF."subcontractor_credit_note_taxes 
                   (order_no, tax_type_id, amount)
             VALUES ("
                    .db_escape($sccn_no).","
                    .db_escape($tax_id).","
                    .db_escape($tax_value).")";              
     
    db_query($sql, "Could not add_subcontractor_credit_note_tax_detail.");
 
}

function update_subcontractor_credit_note_header($sccn_no, $reference, 
                                                    $ord_date, $supp_ref, 
                                                    $total, $taxes_total, $tax_group_id, $staxes_total)                           
                                              
{
    $updated_by = $_SESSION['wa_current_user']->user;
    $updated_on = date('Y-m-d H:i:s');
    
    $sql = "UPDATE ".TB_PREF."subcontractor_credit_notes
            SET 
                reference=".db_escape($reference).",              
                ord_date=".db_escape(date2sql($ord_date)).", 
                supp_ref=".db_escape($supp_ref).", 
                total=".db_escape($total).", 
                taxes =".db_escape($taxes_total).",  
                staxes =".db_escape($staxes_total).",  
                tax_group_id=".db_escape($tax_group_id).",                       
                updated_by=".db_escape($updated_by).",
                updated_on=".db_escape($updated_on)."                  
            WHERE order_no=".db_escape($sccn_no);
    
    db_query($sql);
    return db_num_affected_rows();     
}


function add_subcontractor_credit_note_header($reference, $project_id, $supplier_id,   
                                                $ord_date, $supp_ref, $sc_invoice_id,
                                                $total, $taxes_total, $tax_group_id, $staxes_total)                                        
{
    
    $created_by = $_SESSION['wa_current_user']->user;
    $created_on = date('Y-m-d H:i:s');
    
    $next_no = get_next_trans_no(ST_SUBCONTRACTOR_CREDIT_NOTE);
    
    $sql = "INSERT INTO ".TB_PREF."subcontractor_credit_notes
                    (order_no, reference, 
                        project_id, supplier_id, 
                        ord_date, supp_ref, sc_invoice_id,
                        total, taxes, tax_group_id, 
                        staxes, created_by, created_on )         
                VALUES 
                ( ".db_escape($next_no).", ".db_escape($reference).",  
                    ".db_escape($project_id).", ".db_escape($supplier_id).",                  
                    ".db_escape(date2sql($ord_date)).", ".db_escape($supp_ref).",  
                    ".db_escape($sc_invoice_id).", ".db_escape($total).",
                    ".db_escape($taxes_total).", ".db_escape($tax_group_id).", 
                    ".db_escape($staxes_total).", ".db_escape($created_by).", ".db_escape($created_on).")";
     
    db_query($sql);
    return $next_no;        
}


function add_subcontractor_credit_note_details($sccn_no, $line_type, $item_type, 
                                            $sequence, $item_id, $text, $completed_date, $unit, 
                                            $quantity, $rate, $amount, $line_comment,
                                            $task_id, $category_id, $debit_account, $credit_account,
                                            $invoice_line_id)
                                            
{
    $created_by = $_SESSION['wa_current_user']->user;
    $created_on = date('Y-m-d H:i:s');
    
     $sql = "INSERT INTO ".TB_PREF."subcontractor_credit_note_details (
                        order_no, line_type, item_type, 
                        sequence, item_id, description, 
                        completed_date, unit, quantity, 
                        rate, amount, line_comment,
                        task_id, category_id, 
                        debit_account, credit_account, invoice_line_id,
                        created_by, created_on )                
            VALUES (".db_escape($sccn_no).", ".db_escape($line_type).",".db_escape($item_type).","
                        .db_escape($sequence).", ".db_escape($item_id).",".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($task_id).", ".db_escape($category_id).", "
                        .db_escape($debit_account).", ".db_escape($credit_account).", ".db_escape($invoice_line_id).", "  
                        .db_escape($created_by).", ".db_escape($created_on).")";
              
    db_query($sql);
    return db_insert_id();    
}


function update_subcontractor_credit_note_details($order_no, $line_type, $item_type,
                                                $sequence, $item_id, $text, $completed_date, $unit, 
                                                $quantity, $rate, $amount, $line_comment,
                                                $task_id, $category_id, $debit_account, $credit_account,
                                                $invoice_line_id)
                                                
{
    $updated_by = $_SESSION['wa_current_user']->user;
    $updated_on = date('Y-m-d H:i:s');
    
     $sql = "UPDATE ".TB_PREF."subcontractor_credit_note_details
                SET 
                    line_type=".db_escape($line_type).",
                    item_type=".db_escape($item_type).",
                    sequence=".db_escape($sequence).",
                    item_id=".db_escape($item_id).", 
                    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($line_comment).",
                    category_id=".db_escape($category_id).",
                    task_id=".db_escape($task_id).",
                    debit_account=".db_escape($debit_account).",
                    credit_account=".db_escape($credit_account).",
                    invoice_line_id=".db_escape($invoice_line_id).", 
                    updated_by=".db_escape($updated_by).",
                    updated_on=".db_escape($updated_on)."                  
                        
                WHERE id=".db_escape($order_no);
     
    db_query($sql);
    return db_num_affected_rows();  
    
}


function get_subcontractor_credit_note_header($sccn_no)
{
    $sql = "SELECT scn.*,
                    pm.description project_description,
                    su.supp_name supplier_name

            FROM ".TB_PREF."subcontractor_credit_notes scn 
                LEFT JOIN ".TB_PREF."projects_master pm ON 
                    scn.project_id = pm.project_id
                    
                LEFT JOIN ".TB_PREF."suppliers su ON
                    scn.supplier_id = su.supplier_id
           
            WHERE scn.order_no=".db_escape($sccn_no);
    
    $result = db_query($sql);
    return db_fetch($result);   
    
}


function get_subcontractor_credit_note_details($order_no)
{
    $sql = "SELECT sccnd.*, 
                    u.name unit_name,
                    cn.value_name category_name,
                    da.account_name debit_account_name,
                    ca.account_name credit_account_name,
                    pt.task_code, 
                    pt.description task_description, 
                    boqi.code boq_item_code,
                    scpod.invoiced_qty,
                    scpod.quantity po_line_qty

            FROM ".TB_PREF."subcontractor_credit_note_details sccnd
                    LEFT JOIN ".TB_PREF."item_units u ON 
                            sccnd.unit = u.abbr 
                    LEFT JOIN ".TB_PREF."list_values cn ON 
                            sccnd.category_id = cn.id 
                    LEFT JOIN ".TB_PREF."chart_master da ON 
                            sccnd.debit_account = da.account_code  
                    LEFT JOIN ".TB_PREF."chart_master ca ON 
                            sccnd.credit_account = ca.account_code  
                    LEFT JOIN ".TB_PREF."project_tasks pt ON 
                            sccnd.task_id = pt.task_id  
                    LEFT JOIN ".TB_PREF."project_boq_items boqi ON 
                            sccnd.item_id = boqi.id  
                            
                    LEFT JOIN ".TB_PREF."subcontractor_invoice_details scid ON
                            sccnd.invoice_line_id = scid.id
                            
                    LEFT JOIN ".TB_PREF."subcontractor_purchase_order_details scpod ON
                            scid.po_line_id = scpod.id

            WHERE sccnd.order_no=".db_escape($order_no)." 
            ORDER BY sequence";
    
    return db_query($sql, "Could not get subcontractor credit note");
    
}



function get_subcontractor_credit_note_detail_records($order_no)
{
    $sql = "SELECT sccnd.* 
            FROM ".TB_PREF."subcontractor_credit_note_details sccnd
                     
            WHERE sccnd.order_no=".db_escape($order_no)." 
            ORDER BY sequence";
    
    return db_query($sql, "get_subcontractor_credit_note_detail_records");
    
}




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

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

function get_subcontractor_credit_note_tax_type_amount($sccn_no, $tax_type_id)
{
    $sql = "SELECT amount 
            FROM ".TB_PREF."subcontractor_credit_note_taxes 
            WHERE order_no=".db_escape($sccn_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_subcontractor_credit_note_header_record($sccn_no)
{
    $sql = "SELECT sci.* 
            FROM ".TB_PREF."subcontractor_credit_notes sci                     
            WHERE sci.order_no=".db_escape($sccn_no);
    
    $result = db_query($sql, "Could not get_subcontractor_credit_note_header_record");
    return db_fetch($result);   
    
}

function release_subcontractor_credit_note($sccn_no)
{
    $sci = get_subcontractor_credit_note_header_record($sccn_no);
    $document_total = $sci['total'];
    if(!($document_total > 0)){
        $message = "This Subcontractor Credit Note tax free total is zero. It can not be released.";
        $set_focus = '';
        return array("success" => false, "focus_to" => $set_focus, "message" => $message);
    }
    
    
    $task_costs = array();
    $other_gl_accounts = array();
    
    $current_user_id = $_SESSION['wa_current_user']->user;
    $trans_type = ST_SUBCONTRACTOR_CREDIT_NOTE;
    
    $date_ = sql2date($sci['ord_date']);
    
    $result = get_subcontractor_credit_note_details($sccn_no);
    while ($sci_detail = db_fetch($result)){  
        if($sci_detail['task_id'] > 0){
            if($sci_detail['amount'] != 0){// Consider returning values (negative values) to the budegts too
                $sti_line_amount = -$sci_detail['amount'];        
                add_to_task_cost($task_costs, $sci_detail['task_id'], $sti_line_amount, $sti_line_amount, $sti_line_amount);
            }
        }
        else{
            if($sci_detail['amount'] != 0){
                if(strlen($_POST['debit_account']) > 0 && strlen($_POST['credit_account']) > 0){
                    $gl_info['amount'] = $sci_detail['amount'];  
                    if($sci_detail['amount'] > 0){
                        $gl_info['debit_account'] = $sci_detail['debit_account'];  
                        $gl_info['credit_account'] = $sci_detail['credit_account'];  
                    }
                    else{
                        $gl_info['debit_account'] = $sci_detail['credit_account'];  
                        $gl_info['credit_account'] = $sci_detail['debit_account'];  
                    }
                    $gl_info['memo'] = $sci_detail['category_name']; 
                    
                    $other_gl_accounts[] = $gl_info;
                }
            }
        }
    }
    
    
    
    $info = approve_document($trans_type, $sccn_no, $sci['project_id'], $task_costs, 
                                $sci['next_approval_group_id'], $current_user_id, $sci['row_state'], 
                                $sci['created_by'], $document_total);

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

    if($info['approval_status'] != AS_APPROVED){
        return array("success" => $info['success'], "trans_no" => $sccn_no, "message" => $info['message']);
    }
    
    $cct_msg = charge_cost_to_tasks($trans_type, $sccn_no, $task_costs, $current_user_id);
    if($cct_msg['success'] !== true)
    {
        return array("success" => $cct_msg['success'], "tran_no" => $sccn_no, "message" => $cct_msg['message']);
    }
    
    
    /* Financial entries */
    
    if(exists_supp_trans($trans_type, $sccn_no)){
        return array("success" => false, "message" => "Subcontractor Credit Note $sccn_no is already in the Supplier's transactions list.");
    }
    
    $total = 0;
    $supplier = get_supplier($sci['supplier_id']);
    $ex_rate = get_exchange_rate_from_home_currency($supplier['curr_code'], $date_);
    
    write_supp_trans($trans_type, $sccn_no, $sci['supplier_id'], $date_, '', $sci['reference'], $sci['supp_ref'], -$sci['total'], -$sci['taxes'], 
                     0, "", 0, $sci['rate_include_taxes'], 0, 0, $sci['tax_group_id'], -$sci['staxes']);
    
    
    $taxes = get_subcontractor_credit_note_tax_details($sccn_no);
    foreach ($taxes as $taxitem)
    {
        if ($taxitem['amount'] != 0)
        {
            add_trans_tax_details($trans_type, $sccn_no, $taxitem['tax_type_id'], $taxitem['rate'], $sci['rate_include_taxes'], 
                        -$taxitem['amount'], -$sci['total'], $ex_rate, $date_, $sci['supp_ref']);

//            $total += add_gl_trans_supplier($trans_type, $sccn_no, $date_,
//                                $taxitem['purchasing_gl_code'], 0, 0, -$taxitem['amount'],
//                                $sci['supplier_id'],
//                                "A general ledger transaction for the tax amount could not be added");
        }
    }
    
//    add_gl_trans_supplier($trans_type, $sccn_no, $date_, $supplier["payable_account"], 0, 0,
//                                    +($sci['total'] + $sci['taxes']), $sci['supplier_id'],
//                                    "The general ledger transaction for the control total could not be added");
    
    $subcontract_exp_act = get_sys_prefs_parameter_value('subcontract_exp_act');
    if(!(isset($subcontract_exp_act) && strlen($subcontract_exp_act) > 0)){
        $message = "Subcontract Expenses Account not entered in the system.";
        return array("success" => false, "tran_no" => $sccn_no, "message" => $message);
    }
                
    $memo_ = "";
    $total += add_gl_trans_supplier($trans_type, $sccn_no, $date_, $subcontract_exp_act,
        0, 0, -$sci['total'] , $sci['supplier_id'], "", 0, $memo_);
    
    // Other GL transactions
    
//    $err_msg = "Other GL transaction of Subcontractor credit note could not be inserted";	
//    $supp_curr = get_supplier_currency($sci['supplier_id']);
//    foreach ($other_gl_accounts as $gl_info)
//    {
//        $gl_info_memo = "";
//        if(strlen($gl_info['memo']) > 0){
//            $gl_info_memo = $gl_info['memo'];
//        }
//        add_gl_trans($trans_type, $sccn_no, $date_, $gl_info['debit_account'], 0, 0, $gl_info_memo, 
//                        -$gl_info['amount'], $supp_curr, 
//                        PT_SUPPLIER, $sci['supplier_id'], $err_msg, 0);
//        
//        add_gl_trans($trans_type, $sccn_no, $date_, $gl_info['credit_account'], 0, 0, $gl_info_memo, 
//                        +$gl_info['amount'], $supp_curr, 
//                        PT_SUPPLIER, $sci['supplier_id'], $err_msg, 0);
//    }
        
//  add_gl_balance($trans_type, $sti_no, $date_, -$sti_total_excl, PT_SUPPLIER, $sti['supplier_id']); // ??

//	add_comments(ST_SUPPLIER_TRANSPORT_INVOICES, $invoice_id, $date_, $sti['comments']);

//	$Refs->save(ST_SUPPLIER_TRANSPORT_INVOICES, $invoice_id);
    
    return array("success" => true, "tran_no" => $sccn_no, "message" => $info['message']);
}

function get_subcontractor_credit_note_tax_details($order_no)
{     
    $ret_tax_array = array();
    
    $sql = "SELECT sccn_taxes.*, tax_types.purchasing_gl_code   
            FROM ".TB_PREF."subcontractor_credit_note_taxes sccn_taxes 
                    JOIN ".TB_PREF."tax_types tax_types 
            WHERE order_no = ".db_escape($order_no)." AND 
                    sccn_taxes.tax_type_id = tax_types.id ";
     
    $result = db_query($sql, "Could not get_subcontractor_credit_note_tax_details.");
    
//    	$active_taxes = $ret_tax_array = array();
//
//	$tax_group_items = get_tax_group_rates($id);

	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;
    
}


function get_sc_po_line_unapproved_cn_qty($po_line_id, $cn_line_id = 0)
{    
    $sql = "SELECT SUM(sccnd.quantity) total_cn_qty       
            FROM ".TB_PREF."subcontractor_credit_note_details sccnd 
                    JOIN ".TB_PREF."subcontractor_credit_notes sccn ON 
                            sccnd.order_no = sccn.order_no 
                    JOIN ".TB_PREF."subcontractor_invoice_details scid ON
                            sccnd.invoice_line_id = scid.id 
            WHERE sccn.approval_status IN (".AS_NEW.",".AS_AWAITING_APPROVAL.",".AS_WITH_THE_APPROVER.",".AS_BACK_TO_ORIGINATOR.") AND 
                            scid.po_line_id=".db_escape($po_line_id);
    //subcontractor_credit_note_details['invoice_line_id'] = subcontractor_invoice_details['id']
    
    if($cn_line_id > 0){
        $sql .= " AND sccnd.id <> ".db_escape($cn_line_id);
    }
     
    $result = db_query($sql);

    $t =  db_fetch($result);
    if(isset($t['total_cn_qty']) && $t['total_cn_qty'] > 0){
        return $t['total_cn_qty'];
    }
    else{
        return 0;
    }
}


function get_sc_po_line_cn_qty($po_line_id)
{  
    $sql = "SELECT SUM(sccnd.quantity) total_cn_qty       
             FROM ".TB_PREF."subcontractor_credit_note_details sccnd
                JOIN ".TB_PREF."subcontractor_credit_notes inv ON 
                    sccnd.order_no = inv.order_no 
                    
                JOIN ".TB_PREF."subcontractor_invoice_details scid ON
                    sccnd.invoice_line_id = scid.id
      
             WHERE inv.approval_status NOT IN (".AS_NEW.",".AS_AWAITING_APPROVAL.",".AS_WITH_THE_APPROVER.","
                                                .AS_BACK_TO_ORIGINATOR.",".AS_REJECTED.") AND 
                scid.po_line_id=".db_escape($po_line_id);

    $result = db_query($sql);

    $t =  db_fetch($result);
    if(isset($t['total_cn_qty']) && $t['total_cn_qty'] > 0){
        return $t['total_cn_qty'];
    }
    else{
        return 0;
    }
}


?>