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

function save_payment_application($payment_application_id , $project_id, $customer_id, $branch_code, $reference, $date)
{      
    $spa_msg = save_payment_application_header($payment_application_id, $project_id, $customer_id, $branch_code, $reference, $date);        

    if($spa_msg["success"] !== true){
        return $spa_msg;
    }
  
    return $spa_msg;
}


function save_payment_application_header($payment_application_id, $project_id, $customer_id, $branch_code, $reference, $date)
{
    if($payment_application_id > 0){       
        $n = update_payment_application_header($payment_application_id, $project_id, $customer_id, $branch_code, $reference, $date );
        if($n == 1){
            return array("success" => true, "trans_no" => $payment_application_id,  "message" => "Payment Application $payment_application_id has been updated.");
        }
        else{
            return array("success" => false, "trans_no" => $payment_application_id,  "message" => "Payment Application $payment_application_id could not be updated.");
        }
    }else{
        $new_id = create_payment_application_header($project_id, $customer_id, $branch_code, $reference, $date);
        if($new_id > 0){
            global $Refs;
            $Refs->save(ST_PROJECT_PAYMENT_APPLICATION, $new_id, $reference);
//            create_payment_application_boq_item_groups($new_id, $project_id); 
            return array("success" => true, "trans_no" => $new_id,  "message" => "Payment Application $new_id has been created.");
        }
        else{
            return array("success" => false, "trans_no" => 0,  "message" => "Payment Application could not be created.");
        }
    }
}
 
function create_payment_application_boq_item_groups($payment_application_id, $project_id)
{
    delete_payment_application_boq_groups($payment_application_id);
    
    $boq_item_groups_q = get_project_boq_item_groups($project_id);
    
    while($boq_item_group = db_fetch($boq_item_groups_q))
    {
        create_payment_application_boq_item_group($payment_application_id, $boq_item_group['id']);      
    }
}
   
function delete_payment_application_boq_groups($payment_application_id)
{
    $sql = "DELETE
            FROM ".TB_PREF."project_payment_application_boq_item_groups  
            WHERE pa_id=".db_escape($payment_application_id);
                 
    
    db_query($sql, "Could not delete project_payment_application");    
}
  
function update_payment_application_header($payment_application_id, $project_id, $customer_id, $branch_code, $reference, $date )
{
    $updated_by = $_SESSION['wa_current_user']->user;
    $updated_on = date('Y-m-d H:i:s');
    $sql = "UPDATE ".TB_PREF."project_payment_applications
                SET                    
                    project_id=".db_escape($project_id).", 
                    customer_id=".db_escape($customer_id).", 
                    branch_code=".db_escape($branch_code).", 
                    reference=".db_escape($reference).", 
                    ord_date=".db_escape(date2sql($date)).",                    
                    updated_by=".db_escape($updated_by).",
                    updated_on=".db_escape($updated_on)."
                        
                WHERE order_no=".db_escape($payment_application_id);

    db_query($sql,"Project payment balance could not be updated");    
    
    return db_num_affected_rows();
}

function create_payment_application_header($project_id, $customer_id, $branch_code, $reference, $date)
{
     $id_user = $_SESSION['wa_current_user']->user;
     $datetime = date('Y-m-d H:i:s');
        
    $sql = "INSERT INTO ".TB_PREF."project_payment_applications
        
              (project_id, customer_id, branch_code, reference, ord_date, created_by, created_on )
              
            VALUES (".db_escape($project_id).",  ".db_escape($customer_id).", ".db_escape($branch_code).", "
                     .db_escape($reference).", ".db_escape(date2sql($date)).", "
                     .db_escape($id_user).", ".db_escape($datetime).")";
    
        db_query($sql, "Could not insert project_payment_application");
        return db_insert_id();
}

function create_payment_application_boq_item_group($pa_id, $boq_group_id, $pa_boq_group_items_total, $pa_boq_group_items_taxes_total)
{
    $datetime = date('Y-m-d H:i:s');
    
    $sql = "INSERT INTO ".TB_PREF."project_payment_application_boq_item_groups 
                        (pa_id, boq_group_id,
                        boq_items_total, boq_items_taxes_total, 
                        calculated_on) 
            VALUES(".db_escape($pa_id).",  ".db_escape($boq_group_id).",  "
                        .db_escape($pa_boq_group_items_total).",  ".db_escape($pa_boq_group_items_taxes_total).", "
                        .db_escape($datetime).")";
    
    db_query($sql, "Could not create_payment_application_boq_item_group");
   
}


function get_project_payment_application_header($payment_application_id)
{
   $sql = "SELECT ppa.*,
                pm.description project_description,
                lv.value_name department_name,
                dm.name customer_name,
                pm.project_manager_name,
                ps.description project_site_description 
            FROM ".TB_PREF."project_payment_applications ppa 
                LEFT JOIN ".TB_PREF."projects_master pm ON
                    ppa.project_id = pm.project_id
                LEFT JOIN ".TB_PREF."debtors_master dm ON
                    ppa.customer_id = dm.debtor_no 
                LEFT JOIN ".TB_PREF."project_sites ps ON
                    ppa.site_id = ps.id 
                LEFT JOIN ".TB_PREF."list_values lv ON
                    pm.department_id = lv.id                        

            WHERE ppa.order_no = ".db_escape($payment_application_id);

   $result = db_query($sql, "Could not get get_project_payment_application_header");
   return db_fetch($result);     
}


function insert_project_application_payment_header_for_selected_item($project_id, $customer_id, 
                                                                     $reference, $date)
{
     $created_by = $_SESSION['wa_current_user']->user;
     $created_on = date('Y-m-d H:i:s');
     
    $sql = "INSERT INTO ".TB_PREF."project_payment_application
        
             (project_id, customer_id, reference, date, created_by, created_on)
             
            VALUES(".db_escape($project_id).", ".db_escape($customer_id).", "
                    .db_escape($reference).", ".db_escape(date2sql($date)).", "
                    .db_escape($created_by).", ".db_escape($created_on).")";
    
            db_query($sql, "Could not add project_payment_application");           
                
}


function get_payment_application_header_record($pa_id)
{
    $sql = "SELECT *
            FROM ".TB_PREF."project_payment_applications
            WHERE order_no=".db_escape($pa_id);

$result = db_query($sql);
return db_fetch($result);
    
}


function get_top_level_boq_groups_from_pa($project_id, $pa_id)
{
    $sql = "SELECT p_boqg.*, 
                    pa_boqg.group_total,
                    pa_boqg.calculated_on,
                    pa_boqg.child_groups_total,
                    pa_boqg.child_groups_taxes_total,
                    pa_boqg.boq_items_total,
                    pa_boqg.boq_items_taxes_total 
            FROM ".TB_PREF."project_boq_item_groups p_boqg  
                    LEFT JOIN ".TB_PREF."project_payment_application_boq_item_groups pa_boqg ON 
                            p_boqg.id = pa_boqg.boq_group_id AND 
                            pa_boqg.pa_id=".db_escape($pa_id)." 
            WHERE  p_boqg.project_id=".db_escape($project_id)." 
                    AND p_boqg.parent_id = 0 
            ORDER BY p_boqg.display_order";
	
	return db_query($sql,"Could not get_top_level_boq_groups_from_pa");
}

function get_top_level_boq_groups_new_pa($project_id)
{
    $sql = "SELECT p_boqg.*, 
                    pa_boqg.group_total,
                    pa_boqg.calculated_on,
                    pa_boqg.child_groups_total,
                    pa_boqg.child_groups_taxes_total,
                    pa_boqg.boq_items_total,
                    pa_boqg.boq_items_taxes_total 
            FROM ".TB_PREF."project_boq_item_groups p_boqg  
                    LEFT JOIN ".TB_PREF."project_payment_application_boq_item_groups pa_boqg ON 
                            p_boqg.id = pa_boqg.boq_group_id AND 
                            pa_boqg.pa_id = -1 
            WHERE  p_boqg.project_id=".db_escape($project_id)." 
                    AND p_boqg.parent_id = 0 
            ORDER BY p_boqg.display_order";
	
	return db_query($sql,"Could not get_top_level_boq_groups_new_pa");
}

function get_child_boq_groups_from_pa($parent_id, $pa_id)
{
    $sql ="SELECT p_boqg.*, 
                    pa_boqg.group_total,
                    pa_boqg.calculated_on,
                    pa_boqg.child_groups_total,
                    pa_boqg.child_groups_taxes_total,
                    pa_boqg.boq_items_total,
                    pa_boqg.boq_items_taxes_total 
            FROM ".TB_PREF."project_boq_item_groups p_boqg  
                    LEFT JOIN ".TB_PREF."project_payment_application_boq_item_groups pa_boqg ON 
                            p_boqg.id = pa_boqg.boq_group_id AND 
                            pa_boqg.pa_id=".db_escape($pa_id)." 
            WHERE  p_boqg.parent_id = ".db_escape($parent_id)." 
            ORDER BY p_boqg.display_order";
	
	return db_query($sql,"Could not get_child_boq_groups_from_pa");
}

function get_child_boq_groups_new_pa($parent_id)
{
    $sql ="SELECT p_boqg.*, 
                    pa_boqg.group_total,
                    pa_boqg.calculated_on,
                    pa_boqg.child_groups_total,
                    pa_boqg.child_groups_taxes_total,
                    pa_boqg.boq_items_total,
                    pa_boqg.boq_items_taxes_total 
            FROM ".TB_PREF."project_boq_item_groups p_boqg  
                    LEFT JOIN ".TB_PREF."project_payment_application_boq_item_groups pa_boqg ON 
                            p_boqg.id = pa_boqg.boq_group_id AND 
                            pa_boqg.pa_id = -1
            WHERE p_boqg.parent_id = ".db_escape($parent_id)." 
            ORDER BY p_boqg.display_order";
	
	return db_query($sql,"Could not get_child_boq_groups_new_pa");
}

function get_project_pa_boq_groups($pa_id)
{
    $sql ="SELECT pa_boqg.* 
            FROM ".TB_PREF."project_payment_application_boq_item_groups pa_boqg
            WHERE  pa_boqg.pa_id=".db_escape($pa_id);
	
	return db_query($sql,"Could not get_project_pa_boq_groups");
}

function update_project_pa_boq_group_item_totals($pa_boq_group_id, $boq_items_total, $boq_items_taxes_total)
{
    $updated_on = date('Y-m-d H:i:s');
    $sql = "UPDATE ".TB_PREF."project_payment_application_boq_item_groups 
                SET                    
                    boq_items_total=".db_escape($boq_items_total).", 
                    boq_items_taxes_total=".db_escape($boq_items_taxes_total).", 
                    calculated_on=".db_escape($updated_on)."
                WHERE id=".db_escape($pa_boq_group_id);

    db_query($sql,"Project payment balance could not be updated");    

}

function update_payment_application_totals($payment_application_id, $value_total, $tax_total)
{
    $sql = "UPDATE ".TB_PREF."project_payment_applications
                SET                    
                    total=".db_escape($value_total).", 
                    taxes=".db_escape($tax_total)."
                        
                WHERE order_no=".db_escape($payment_application_id);

    db_query($sql,"Could not update_payment_application_totals");    

}

function get_project_pa_boq_groups_total($pa_id)
{
    $sql ="SELECT SUM(boq_items_total) boq_items_total, SUM(boq_items_taxes_total) boq_items_taxes_total    
            FROM ".TB_PREF."project_payment_application_boq_item_groups 
            WHERE  pa_id=".db_escape($pa_id);
	
	$result = db_query($sql,"Could not get_project_pa_boq_groups_total");
    return db_fetch($result);
}


function get_ppa_no_boq_item_group($pa_id)
{
    $sql ="SELECT pa_boqg.* 
            FROM ".TB_PREF."project_payment_application_boq_item_groups pa_boqg                 
            WHERE  pa_boqg.pa_id=".db_escape($pa_id)." AND 
                pa_boqg.boq_group_id = 0 ";
	
	$result = db_query($sql,"Could not get_ppa_no_boq_item_group");
    return db_fetch($result);
}

function get_latest_payment_application_no($project_id)
{
    $sql = "SELECT MAX(order_no) max_order_no
            FROM ".TB_PREF."project_payment_applications
            WHERE project_id=".db_escape($project_id);
    
    $result = db_query($sql);
    return db_fetch($result);
}






?>