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

function write_project_payment_application_detail($pa_id, $pa_boq_item, $site_id,
                                                    $current_qty, $previous_qty, 
                                                    $cumulative_qty, $certi_cuml_qty,
                                                    $ord_date, $rate_include_taxes,
                                                    $update_boq_item)
{
    //$tran_date = date2sql($tran_date);
    
    $total_work_done = get_total_work_done_upto_pa_date($pa_boq_item['id'], $site_id, $ord_date);
//    $total_previous_qty = get_payment_application_previous_quantity($pa_boq_item['id'], $ord_date); 
        
    begin_transaction();
    $ex = get_project_payment_application_detail($pa_id, $pa_boq_item['id']);
    if(!(isset($ex['id']) && $ex['id'] > 0)){
        $pa_line_id = add_project_payment_application_details($pa_id, $pa_boq_item['id'], $current_qty, 
                                                $cumulative_qty, $certi_cuml_qty,
                                                $pa_boq_item['unit'], $pa_boq_item['rate'], 
                                                $total_work_done, $previous_qty, $pa_boq_item['tax_group_id']);    
    }
    else{
        $pa_line_id = $ex['id'];
        update_project_payment_application_details($pa_line_id, $current_qty, 
                                                    $cumulative_qty, $certi_cuml_qty,
                                                    $pa_boq_item['unit'], $pa_boq_item['rate'], $total_work_done, 
                                                    $previous_qty, $pa_boq_item['tax_group_id']);
    }
    
    $pa_line_item_tax_total = update_pa_item_tax_details($pa_line_id, $rate_include_taxes);

    update_project_pa_item_tax_total($pa_line_id, $pa_line_item_tax_total);    

    if($update_boq_item){
        update_project_boq_item_pa_quntities($pa_boq_item['id'], $cumulative_qty, $certi_cuml_qty);
    }
 
    commit_transaction();
    
    return '';
}


function delete_project_payment_application_detail($pa_line_id)
{

    begin_transaction();
    delete_pa_item_tax_details($pa_line_id);

    $sql = "DELETE FROM ".TB_PREF."project_payment_application_details   
            WHERE id = ".db_escape($pa_line_id);     
     
    db_query($sql, "Could not delete_pa_item_tax_details.");

    commit_transaction();
    
    return '';
}


function add_project_payment_application_details($pa_id, $boq_item_id, $current_qty, 
                                                $cumulative_qty, $certi_cuml_qty,
                                                $unit, $rate, 
                                                $total_work_done, $total_previous_qty, $tax_group_id)
{
    $created_by = $_SESSION['wa_current_user']->user;
    $created_on = date('Y-m-d H:i:s');

    $sql = "INSERT INTO ".TB_PREF."project_payment_application_details
                        (pa_id, boq_item_id, 
                        quantity, cumulative_qty, certi_cuml_qty, 
                        unit, rate, 
                        total_work_done, previous_qty, 
                        tax_group_id, 
                        created_by, created_on)
            VALUES (".db_escape($pa_id).", ".db_escape($boq_item_id).",
                       ".db_escape($current_qty).",
                        ".db_escape($cumulative_qty).",".db_escape($certi_cuml_qty).",   
                        ".db_escape($unit).",".db_escape($rate).",   
                        ".db_escape($total_work_done).",".db_escape($total_previous_qty).",  
                        ".db_escape($tax_group_id).",
                        ".db_escape($created_by).", ".db_escape($created_on).")";                      

    db_query($sql,"Could not save project_payment_application_details");      
    
    return db_insert_id();
}

function update_project_payment_application_details($id, $current_qty,
                                                    $cumulative_qty, $certi_cuml_qty,
                                                    $unit, $rate, $total_work_done, 
                                                    $total_previous_qty, $tax_group_id)
{
      $updated_by = $_SESSION['wa_current_user']->user;
      $updated_on = date('Y-m-d H:i:s');
      
        $sql =  "UPDATE ".TB_PREF."project_payment_application_details
                SET quantity=".db_escape($current_qty).", 
                    cumulative_qty=".db_escape($cumulative_qty).", 
                    certi_cuml_qty=".db_escape($certi_cuml_qty).", 
                    unit=".db_escape($unit).", 
                    rate=".db_escape($rate).", 
                    total_work_done=".db_escape($total_work_done).",
                    previous_qty=".db_escape($total_previous_qty).",
                    tax_group_id=".db_escape($tax_group_id).",
                    updated_by=".db_escape($updated_by).",
                    updated_on=".db_escape($updated_on)."
                WHERE id=".db_escape($id);
        
        db_query($sql, "project_payment_application_details could not be updated");             
}

function update_project_pa_item_tax_total($pa_line_id, $pa_line_item_tax_total)
{
    $sql =  "UPDATE ".TB_PREF."project_payment_application_details
                SET taxes=".db_escape($pa_line_item_tax_total)." 
                WHERE id=".db_escape($pa_line_id);
        
        db_query($sql, "Could not update_project_pa_item_tax_total");   
}

function get_project_payment_application_boq_details($project_id, $payment_application_id, $boq_group_id)
{
    $sql = "SELECT  boqi.*,
                    pad.id pad_id,     
                    pad.total_work_done,
                    pad.comment,                 
                    pad.previous_qty,
                    pad.cumulative_qty,
                    pad.previous_qty,
                    pad.quantity,
                    pad.certi_cuml_qty 
                  
            FROM ".TB_PREF."project_boq_items boqi 
                    LEFT JOIN ".TB_PREF."project_payment_application_details pad ON 
                            boqi.id = pad.boq_item_id AND 
                            pad.pa_id =".db_escape($payment_application_id)." 
                                    
            WHERE boqi.project_id =".db_escape($project_id)." AND 
                    boqi.group_id =".db_escape($boq_group_id);                 
             
   return db_query($sql, "Could not get project_payment_application_details");    
   
}

function get_project_payment_application_details($payment_application_id, $boq_item_group_id)
{
    $sql = "SELECT  boqi.*,
                    pad.id pad_id,     
                    pad.total_work_done,
                    pad.comment,                 
                    pad.previous_qty,
                    pad.taxes,  
                    pad.quantity 
            FROM ".TB_PREF."project_boq_items boqi 
                    INNER JOIN ".TB_PREF."project_payment_application_details pad ON 
                            boqi.id = pad.boq_item_id 
            WHERE pad.pa_id =".db_escape($payment_application_id)." AND 
                    boqi.group_id =".db_escape($boq_item_group_id);                 
             
   return db_query($sql, "Could not get get_project_payment_application_details");    
   
}

function get_project_payment_application_detail($pa_id, $boq_item_id)
{
   $sql = "SELECT pad.*                                   
           FROM ".TB_PREF."project_payment_application_details pad                 
           WHERE pad.pa_id = ".db_escape($pa_id)." AND 
                 pad.boq_item_id =".db_escape($boq_item_id);                 
             
   $result = db_query($sql, "Could not get get_project_payment_application_detail");  
   return db_fetch($result);
}

function get_project_payment_application_detail_by_id($id)
{
   $sql = "SELECT pad.*                                   
           FROM ".TB_PREF."project_payment_application_details pad                 
           WHERE pad.id = ".db_escape($id);                 
             
   $result = db_query($sql, "Could not get get_project_payment_application_detail_by_id");  
   return db_fetch($result);
}


function get_payment_application_previous_quantity($boq_item_id, $tran_date)
{
    $sql = "SELECT SUM(quantity) total_previous_quantity 
            FROM ".TB_PREF."project_payment_application_details pad, 
                    ".TB_PREF."project_payment_applications pa 
            WHERE pad.pa_id = pa.order_no AND 
                    pad.boq_item_id =".db_escape($boq_item_id)." AND 
                    pa.ord_date<".db_escape($tran_date);
    
    $result = db_query($sql);
    $tpq = db_fetch($result);
    
    $total_previous_quantity = 0;
    if(isset($tpq['total_previous_quantity']) &&  $tpq['total_previous_quantity'] > 0){
        $total_previous_quantity =  $tpq['total_previous_quantity'];
    }    
    return $total_previous_quantity;    
}


function get_total_work_done_upto_pa_date($boq_item_id, $site_id, $pa_date)
{
    if(!($site_id > 0)){
        $site_id = 0;
    }
    
    $sql = "SELECT SUM(quantity) total_work_done
            FROM ".TB_PREF."work_done_entry_details
            WHERE boq_item_id =".db_escape($boq_item_id)." AND 
                    site_id =".db_escape($site_id)." AND 
                    tran_date <= ".db_escape($pa_date);   
    
    $result = db_query($sql);
    $total_wd = db_fetch($result);
    
    $total_work_done = 0;
    if(isset($total_wd['total_work_done']) &&  $total_wd['total_work_done'] > 0){
        $total_work_done =  $total_wd['total_work_done'];
    }    
    return $total_work_done;    
}

function update_pa_item_tax_details($pa_line_id, $tax_included)
{
    $pa_line = get_project_payment_application_detail_by_id($pa_line_id);
    $item = $pa_line['boq_item_id'];
    $price = $pa_line['rate'];
    $quantity = $pa_line['quantity'];
    $line_amount = $price * $quantity;
    $tax_group_id = $pa_line['tax_group_id'];   
    $item_taxes_array = calculate_pa_item_taxes($item, $line_amount, $tax_group_id, $tax_included);
    $item_tax_total = 0;
    
    delete_pa_item_tax_details($pa_line_id);
    foreach ($item_taxes_array as $item_tax_line) {
        add_pa_item_tax_detail($pa_line_id, $item_tax_line['tax_type_id'], $item_tax_line['Value']);
        $item_tax_total += $item_tax_line['Value'];
    }
    return $item_tax_total;
}


function calculate_pa_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);
    
    $ret_tax_array = get_tax_group_items_as_array($tax_group_id);
    
    foreach($ret_tax_array as $k=>$t){
		$ret_tax_array[$k]['Net'] = 0;
    }
    
    $dec = 2;
    calculate_item_taxes($ret_tax_array, $line_amont, $tax_included, $dec, $ret_tax_array);

	return $ret_tax_array;	
}


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


function add_pa_item_tax_detail($pi_line_id, $tax_id, $tax_value)
{     
     $sql = "INSERT INTO ".TB_PREF."project_payment_application_item_taxes 
                   (pa_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 view_project_payment_application_boq_items($payment_application_id, $boq_group_id)
{
    $sql = "SELECT  boqi.*,
                    pad.id pad_id,     
                    pad.total_work_done,
                    pad.comment,                 
                    pad.previous_qty,
                    pad.quantity 
                  
            FROM ".TB_PREF."project_boq_items boqi
                    INNER JOIN ".TB_PREF."project_payment_application_details pad ON 
                            boqi.id = pad.boq_item_id AND 
                            pad.pa_id =".db_escape($payment_application_id)." 
                                    
            WHERE boqi.group_id =".db_escape($boq_group_id);                 
             
   return db_query($sql, "Could not get project_payment_application_details");    
   
}

?>