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

function add_asset_depreciation_run($description,$period_end_date, 
                       $no_of_months, $status)
{
        $created_by = $_SESSION['wa_current_user']->user;
        $created_on = date('Y-m-d H:i:s');        
        
	$sql = "INSERT INTO ".TB_PREF."asset_depreciation_runs (                     
                    description, period_end_date, 
                    no_of_months, status, 
                    created_by, created_on) 
                VALUES ("
                    .db_escape($description).", ".db_escape($period_end_date).", "
                    .db_escape($no_of_months).", ".db_escape($status).", "
                    .db_escape($created_by).", ".db_escape($created_on).")";
        
        db_query($sql,"Payroll run could not be added");
        return db_insert_id();

}

function update_asset_depreciation_run($rec_id, $description, 
                          $period_end_date, $no_of_months,
                          $status)
{

        $updated_by = $_SESSION['wa_current_user']->user;
        $updated_on = date('Y-m-d H:i:s');

        $sql = "UPDATE ".TB_PREF."asset_depreciation_runs 
                SET description=".db_escape($description).",
                    period_end_date =".db_escape($period_end_date).",
                    no_of_months =".db_escape($no_of_months).",
                    status =".db_escape($status).",
                    updated_by=".db_escape($updated_by).",
                    updated_on=".db_escape($updated_on)." 
                WHERE id=".db_escape($rec_id);

        db_query($sql,"Payroll run could not be added");            
}

function get_asset_depreciation_runs(){
    	$sql = "SELECT adr.*,
                        lv1.value_name status_name 
                FROM ".TB_PREF."asset_depreciation_runs adr  
                    JOIN ".TB_PREF."list_values lv1 ON 
                        adr.status = lv1.value_code AND lv1.list_code = 'PAYROLL_RUN_STATUS' 
                WHERE 1=1";                      
	
	$result = db_query($sql,"Could not retrieve payrolls");        
        return $result;    
}

function get_asset_depreciation_run_details($selected_id){
    	$sql = "SELECT *    
                FROM ".TB_PREF."asset_depreciation_runs                        
                WHERE id =".db_escape($selected_id);
	
	$result = db_query($sql,"Could not retrieve payroll run details");        
        return db_fetch($result);    
}

function get_process_codes()
{  
	$sql = "SELECT process_code  
                FROM ".TB_PREF."payroll_calculation_seq   
		ORDER BY seq_no";
	
	$result = db_query($sql,"Could not retrieve process codes");	
        
        return $result;
}

function get_all_employee_codes()
{  
	$sql = "SELECT employee_code 
                FROM ".TB_PREF."employee_master  
		ORDER BY employee_code";
	
	$result = db_query($sql,"Could not retrieve employee codes");	
        
        return $result;
}

function get_next_payroll_run_id()
{     
        return rand(1000, 9999);
}

function insert_log_message($payroll_run_id, $payroll_type, $message_type, $message_details)
{  
	$added_by = $_SESSION['wa_current_user']->user;
        $added_on = date('Y-m-d H:i:s');
        
        $sql = "INSERT INTO ".TB_PREF."payroll_log(
                        payroll_run_id, payroll_type,
                        message_type, message_details,
                        added_by, added_on) 
                VALUES(".$payroll_run_id.",".db_escape($payroll_type).","
                        .db_escape($message_type).",".db_escape($message_details).","
                        .$added_by.",".db_escape($added_on).")";
	
	db_query($sql,"Could not add log message");        
}

function update_payroll_run_joq_request_id($payroll_run_id, $jobq_id)
{
    $sql = "UPDATE ".TB_PREF."payroll_runs 
            SET jobq_request_id=".db_escape($jobq_id)." 
            WHERE id = ".db_escape($payroll_run_id);
    
    db_query($sql,"Could not update payroll run jobq id");
}

function update_payroll_link_id($linked_payroll_run_id, $selected_id)
{    
    $updated_by = $_SESSION['wa_current_user']->user;
    $updated_on = date('Y-m-d H:i:s');
    
    $sql = "UPDATE ".TB_PREF."payroll_runs 
            SET linked_payroll_run_id=".db_escape($linked_payroll_run_id).",  
                updated_by=".db_escape($updated_by).",
                    updated_on=".db_escape($updated_on)." 
            WHERE id = ".db_escape($selected_id);
    
    db_query($sql,"Could not update payroll run link id");
    return db_num_affected_rows();
}

//---------------------------------------------------------------------------------------------
function get_asset_current_value() //start of the insert depreciation calculation data to the asset run details table
{
    $sql = "SELECT a.purchase_value,
                    a.current_value,
                   a.asset_id,
                   ac.depreciation_rate,
                   a.current_value,
                   ac.asset_account,
                   ac.depreciation_account,
                   ac.accumulated_account
        
            FROM ".TB_PREF."asset_catergories ac,
                 ".TB_PREF."asset_master a
                     
            WHERE a.asset_category_id = ac.id 
                    AND a.is_hired = 0 
                    AND a.current_value > 0";  
    
  return db_query($sql, "");     
}

function insert_calculation_value_to_asset_depreciation_run_details($asset_run_id, $asset_id, 
                                                       $starting_value, $depreciation_type,
                                                       $depreciation_rate, $depreciation_value, $asset_account,
                                                       $depreciation_account, $accumulated_account)
{
    $sql = "INSERT INTO ".TB_PREF."asset_depreciation_run_details
                              (asset_run_id, asset_id, starting_value, 
                               depreciation_type, depreciation_rate, depreciation_value,
                               asset_account, depreciation_account, accumulated_account)
           
           VALUES(".db_escape($asset_run_id).",
                  ".db_escape($asset_id).",  
                  ".db_escape($starting_value).",  
                  ".db_escape($depreciation_type).",
                  ".db_escape($depreciation_rate).",
                  ".db_escape($depreciation_value).",
                  ".db_escape($asset_account).",
                  ".db_escape($depreciation_account).",
                  ".db_escape($accumulated_account).")";
                      
     db_query($sql, "Asset run could not be added");
     return db_insert_id();    
}

function delete_asset_depreciation_run_asset_run_details($asset_run_id)
{
    $sql = "DELETE FROM ".TB_PREF."asset_depreciation_run_details
            WHERE asset_run_id=".db_escape($asset_run_id);     
    
    db_query($sql, "Could not Delete asset run details");     
    
} //end of the insert depreciation calculation data to the asset run details table

//--------------------------------------------------------------------------------------

// start of the depreciation summary
function calculate_depreciation_summary($asset_run_id)  
{
    $sql = "SELECT sum(depreciation_value) AS dv, depreciation_account AS dc
        
            FROM ".TB_PREF."asset_depreciation_run_details 
                
            WHERE asset_run_id =".db_escape($asset_run_id)." 
                    AND asset_id > 0 
                
            GROUP BY depreciation_account"; 
    
   return db_query($sql, "");    
}

function insert_depreciation_summary($depreciation_value, $depreciation_account, $asset_run_id)
{
   $sql = "INSERT INTO ".TB_PREF."asset_depreciation_run_details
                 (depreciation_value, depreciation_account, asset_run_id)
    
           VALUES(".db_escape($depreciation_value).",
                  ".db_escape($depreciation_account).",
                  ".db_escape($asset_run_id).")";
   
   db_query($sql, "");
   return db_insert_id();    
    
} //end of the depreciation summary

//--------------------------------------------------------------------------------------

function calculate_asset_account_summary($asset_run_id)
{
    $sql = "SELECT sum(depreciation_value) AS dv, asset_account AS ac
        
            FROM ".TB_PREF."asset_depreciation_run_details 
                
            WHERE asset_run_id =".db_escape($asset_run_id)." 
                    AND asset_id > 0 
                
            GROUP BY asset_account"; 
    
   return db_query($sql, "");    
}

function insert_asset_account_summary($depreciation_value, $asset_account, $asset_run_id)
{
    $sql = "INSERT INTO ".TB_PREF."asset_depreciation_run_details
                 (depreciation_type, depreciation_value, 
                    asset_account, depreciation_account, 
                    accumulated_account, asset_run_id)
    
           VALUES('', ".db_escape($depreciation_value).",
                  ".db_escape($asset_account).", '',
                  '', ".db_escape($asset_run_id).")";
   
   db_query($sql, "");
   return db_insert_id();     
}

function delete_asset_depreciation_run($id)
{
    $sql = "DELETE FROM ".TB_PREF."asset_depreciation_runs
            WHERE id=".db_escape($id);  
    
    db_query($sql, "Could not Delete Asset Runs");    
}



function get_last_period_start_date()
{
    $sql = "SELECT period_end_date 
            FROM ".TB_PREF."asset_depreciation_runs                
            ORDER BY period_end_date
            DESC LIMIT 1";
            
    $result = db_query($sql);
    return db_fetch($result);
}
  

function add_asset_depreciation()
{
    $sql = "INSERT INTO ".TB_PREF."asset_depreciation_run_detail
            ()        
            VALUES
            ()";
    
    db_query($sql);

}


function get_asset_details_for_depreciation()
{
    $sql = "SELECT am.asset_id, 
                    am.current_value,
                    ac.* 

            FROM ".TB_PREF."asset_master am 
            JOIN ".TB_PREF."asset_catergories ac ON
                    am.asset_category_id = ac.id";
            
    return db_query($sql);

}
 
 
function update_last_calculated_asset($ar_id, $asset_id)
{
    $sql = "UPDATE ".TB_PREF."asset_depreciation_runs
            SET last_calculated_asset_id =".db_escape($asset_id)." 
            WHERE id=".db_escape($ar_id);
    
    db_query($sql);
}

  
function delete_asset_depreciation($ar_id)
{
    $sql = "DELETE FROM ".TB_PREF."asset_depreciation_run_detail WHERE run_id=".db_escape($ar_id);
    
    db_query($sql);
}
   
   

function update_asset_depreciation_run_status($payroll_run_id, $status_code)
{
        $run_by = $_SESSION['wa_current_user']->user;
        $time = date('Y-m-d H:i:s');
        
        $sql = "UPDATE ".TB_PREF."asset_depreciation_runs 
                SET 
                    status=".db_escape($status_code);
        
        $sql .= " WHERE id=".db_escape($payroll_run_id);

        db_query($sql,"Payroll run status could not be updated");
           
}


?>