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



function write_project_advance_batch_header($ub_no, $reference, $project_id, 
                                            $date, $float_id, $comment)                                       
{
    if($ub_no > 0){        
        update_project_advance_batch_header($ub_no, $reference, $project_id, 
                                            $date, $float_id, $comment);
    }else{
        global $Refs;
        $ub_no = add_project_advance_batch_header($reference, $project_id, 
                                                    $date, $float_id, $comment);
        if(!($ub_no > 0)){
            return;
        }  
    
        $Refs->save(ST_PROJECT_ADVANCE_BATCH, $ub_no, $reference);

    }

    return $ub_no;
}


function write_project_advance_batch_detail($ub_no, $ubd_line_id, $line_project_id, $description, 
                                        $amount, $period_from, $period_to )                                                                              
                                       
{            
    if($ubd_line_id > 0){         
        $n = update_project_advance_batch_detail($ubd_line_id, $line_project_id, $description, $amount,                                       
                                                    $period_from, $period_to );    
                                                  
        if($n != 1){
           return array("success" => false, "message" => "Another user has updated bill detail $ubd_line_id.");  
        }
    }
    else{
        add_project_advance_batch_detail($ub_no, $line_project_id, 
                                        $description, $amount,
                                        $period_from, $period_to ); 
    }
   
}



function get_ub_lines_total($ub_no)
{
    $sql = "SELECT SUM(ubd.amount) lines_amount_total
            FROM ".TB_PREF."project_advance_batch_details ubd
            WHERE ubd.pab_id=".db_escape($ub_no);
    
    $result = db_query($sql, "Could not get_ub_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_project_advance_batch_header($ub_no, $reference, $project_id, 
                                        $date, $float_id, $comment)
{
    $updated_by = $_SESSION['wa_current_user']->user;
    $updated_on = date('Y-m-d H:i:s');
    
    $sql = "UPDATE ".TB_PREF."project_advance_batches
            SET 
                reference=".db_escape($reference).",
                project_id=".db_escape($project_id).",               
                tran_date=".db_escape(date2sql($date)).",             
                float_id=".db_escape($float_id).",
                comment=".db_escape($comment).",
                updated_by=".db_escape($updated_by).",
                updated_on=".db_escape($updated_on)."       
                
            WHERE id=".db_escape($ub_no);
    
    db_query($sql);
    return db_num_affected_rows();  
    
}

function add_project_advance_batch_header($reference, $project_id, 
                                            $date, $float_id, $comment)
{
    $created_by = $_SESSION['wa_current_user']->user;
    $created_on = date('Y-m-d H:i:s');
    
    $sql = "INSERT INTO ".TB_PREF."project_advance_batches
                  (reference, project_id,  
                    tran_date, float_id, comment,
                    created_by, created_on )         
                VALUES 
                (".db_escape($reference).", ".db_escape($project_id).", 
                   ".db_escape(date2sql($date)).",
                    ".db_escape($float_id).", ".db_escape($comment).",  
                    ".db_escape($created_by).", ".db_escape($created_on).")";
     
    db_query($sql);
    return db_insert_id();        
}


function add_project_advance_batch_detail($ub_no, $line_project_id, 
                                            $description, $amount,
                                            $period_from, $period_to)
{
    $created_by = $_SESSION['wa_current_user']->user;
    $created_on = date('Y-m-d H:i:s');
    
     $sql = "INSERT INTO ".TB_PREF."project_advance_batch_details 
                      ( pab_id, project_id, 
                        description, 
                        amount, period_from, period_to,                          
                        created_by, created_on )                
            VALUES (".db_escape($ub_no).", ".db_escape($line_project_id).","
                        .db_escape($description).",".db_escape($amount).","
                        .db_escape(date2sql($period_from)).", ".db_escape(date2sql($period_to)).", "                   
                        .db_escape($created_by).", ".db_escape($created_on).")";
             
    db_query($sql);
       
}

function update_project_advance_batch_detail($ubd_line_id, $line_project_id, $description, $amount,
                                                $period_from, $period_to ) 
                                                
{
    $updated_by = $_SESSION['wa_current_user']->user;
    $updated_on = date('Y-m-d H:i:s');
    
     $sql = "UPDATE ".TB_PREF."project_advance_batch_details
                SET 
                    description=".db_escape($description).",
                    project_id=".db_escape($line_project_id).",
                    amount=".db_escape($amount).",
                    period_from=".db_escape(date2sql($period_from)).",
                    period_to=".db_escape(date2sql($period_to)).",  
                    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_project_advance_batch_header($ub_no)
{
    $sql = "SELECT ub.*,
                    pm.description project_description
        
            FROM ".TB_PREF."project_advance_batches ub
                LEFT JOIN ".TB_PREF."projects_master pm ON
                    ub.project_id = pm.project_id
                
            WHERE ub.id=".db_escape($ub_no);
    
    $result = db_query($sql);
    return db_fetch($result);   
    
}


function get_project_advance_batch_details($order_no)
{
    $sql = "SELECT ubd.*,
                    pm.description project_description 
                
            FROM ".TB_PREF."project_advance_batch_details ubd                        
                    JOIN ".TB_PREF."project_advance_batches pab ON
                            ubd.pab_id = pab.id 
                    JOIN ".TB_PREF."projects_master pm ON
                            ubd.project_id = pm.project_id 
                            
            WHERE ubd.pab_id=".db_escape($order_no)." 
            ORDER BY id";
    
    return db_query($sql, "Could not get_project_advance_batch_details.");
    
}

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

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

    return db_num_affected_rows();
    
}

function get_project_advance_batch_tax_type_amount($ub_no, $tax_type_id)
{
    $sql = "SELECT amount 
            FROM ".TB_PREF."project_advance_batch_taxes 
            WHERE ub_id=".db_escape($ub_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_project_advance_batch_line_count($ub_no)
{
    $sql = "SELECT COUNT(id) line_count 
            FROM ".TB_PREF."project_advance_batch_details 
            WHERE ub_id  = ".db_escape($ub_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 update_header_total($ub_lines_total, $ub_no)
{
    $sql = "UPDATE ".TB_PREF."project_advance_batches
            SET total=".db_escape($ub_lines_total)." 
            WHERE id=".db_escape($ub_no);
    
    db_query($sql);
          
}


function update_project_advance_batch_detail_paid($id)
{
    $sql = "UPDATE ".TB_PREF."project_advance_batch_details
            SET paid=1
            WHERE id=".db_escape($id)." AND paid=0";
    
    db_query($sql);
    return db_num_affected_rows();
    
}

function update_project_advance_batch_header_paid($ub_no)
{
    $paid_by = $_SESSION['wa_current_user']->user;
    $paid_on = date('Y-m-d H:i:s');

    $sql = "UPDATE ".TB_PREF."project_advance_batches
            SET paid=1,
                paid_by=".db_escape($paid_by).", 
                paid_on=".db_escape($paid_on)."
            WHERE id=".db_escape($ub_no)." AND paid=0";
    
    db_query($sql);
    return db_num_affected_rows();            
}


function update_project_advance_float_reimbursement_amount($id, $reimbursed_amount, $row_state)
{
    $sql = "UPDATE ".TB_PREF."project_advance_batch_details   
            SET reimbursed_amount = $reimbursed_amount ,
                row_state =". db_new_row_state($row_state)."  
            WHERE amount >= ".db_escape($reimbursed_amount)." AND 
                    id =".db_escape($id)." AND 
                    row_state =".db_escape($row_state);
    
    db_query($sql, "update_project_advance_float_reimbursement_amount");  
    return db_num_affected_rows();    
    
}

?>