<?php

function update_personal_material_issue_line($trans_id, $loc_code, $tran_date, $tran_unit, $item_type, $item_description, $tran_qty, $qty, $conversion_factor, $task_id,
                                             $standard_cost, $project_site_id, $line_comment) {   
    $updated_by = $_SESSION['wa_current_user']->user;
    $updated_on = date('Y-m-d H:i:s');
    
        $sql = "UPDATE ".TB_PREF."job_material_issue_details 
                SET loc_code = ".db_escape($loc_code).", 
                    tran_date = ".db_escape($tran_date).", 
                    tran_unit = ".db_escape($tran_unit).", 
                    item_type = ".db_escape($item_type).", 
                    item_description = ".db_escape($item_description).", 
                    tran_qty = ".db_escape($tran_qty).", 
                    qty = ".db_escape($qty).", 
                    conversion_factor = ".db_escape($conversion_factor).", 
                    task_id = ".db_escape($task_id).", 
                    standard_cost = ".db_escape($standard_cost).", 
                    project_site_id = ".db_escape($project_site_id).", 
                    line_comment = ".db_escape($line_comment).", 
                    updated_by = ".db_escape($updated_by).", 
                    updated_on = ".db_escape($updated_on)." 
                WHERE trans_id = ".db_escape($trans_id);   

            db_query($sql, "update_personal_material_issue_line");
            return db_num_affected_rows();
}

function add_personal_stock_move($type, $stock_id, $trans_no, $location, $date_, $reference, $qty, $std_cost, $person_id=0, $show_or_hide=1, $price=0, $discount_percent=0, 
                                 $msg="", $tran_unit = "", $tran_qty = 0, $convertion_factor = 1, $task_id = 0, $project_site_id = 0, $reference_line_id = 0, $project_id=0, 
                                 $employee_code='') {
    $date = date2sql($date_);

	$sql = "INSERT INTO ".TB_PREF."personal_stock_moves 
                   (stock_id, trans_no, 
                    type, loc_code,
                    tran_date, person_id, 
                    reference, qty, 
                    standard_cost, visible, 
                    price, discount_percent, 
                    tran_unit, tran_qty, 
                    convertion_factor, task_id,
                    project_site_id, ref_line_id,
                    project_id, employee_code) 
            VALUES (".db_escape($stock_id).", ".db_escape($trans_no).", "
                    .db_escape($type).",".db_escape($location).", "
                    .db_escape($date).", ".db_escape($person_id).", "
                    .db_escape($reference).", ".db_escape($qty).", "
                    .db_escape($std_cost).",".db_escape($show_or_hide).", "
                    .db_escape($price).", ".db_escape($discount_percent).","
                    .db_escape($tran_unit).", ".db_escape($tran_qty).","
                    .db_escape($convertion_factor).", ".db_escape($task_id).", "
                    .db_escape($project_site_id).",".db_escape($reference_line_id).", "
                    .db_escape($project_id).", ".db_escape($employee_code).")";

	db_query($sql, "add_personal_stock_move");
	return db_insert_id();
}

function add_personal_non_stock_move($type, $stock_id, $trans_no, $location, $date_, $reference, $quantity, $std_cost, $person_id=0, $show_or_hide=1, $price=0, 
                                     $discount_percent=0, $tran_unit="", $tran_qty=0, $conversion_factor=1, $task_id=0, $project_site_id=0, $reference_line_id=0, 
                                     $item_description='', $project_id=0, $employee_code='') {
    $date = date2sql($date_);
    
	$sql = "INSERT INTO ".TB_PREF."personal_non_stock_moves     
                   (stock_id, trans_no, 
                    type, loc_code,
                    tran_date, person_id, 
                    reference, qty, 
                    standard_cost, visible, 
                    price, discount_percent, 
                    tran_unit, tran_qty, 
                    conversion_factor, task_id,
                    project_site_id, ref_line_id, 
                    item_description, project_id, employee_code) 
            VALUES (".db_escape($stock_id).", ".db_escape($trans_no).", "
                    .db_escape($type).",".db_escape($location).", "
                    .db_escape($date).", ".db_escape($person_id).", "
                    .db_escape($reference).", ".db_escape($quantity).", "
                    .db_escape($std_cost).",".db_escape($show_or_hide).", "
                    .db_escape($price).", ".db_escape($discount_percent).","
                    .db_escape($tran_unit).", ".db_escape($tran_qty).","
                    .db_escape($conversion_factor).", ".db_escape($task_id).", "
                    .db_escape($project_site_id).", ".db_escape($reference_line_id).", "
                    .db_escape($item_description).", ".db_escape($project_id).", ".db_escape($employee_code).")";

	db_query($sql, 'add_personal_non_stock_move');
	return db_insert_id();
}

function get_sql_for_personal_material_issues_returns_inquiry($trans_no, $jo_id, $reference, $type, $project_id, $from_date, $to_date, $employee_code, $ap_status, 
                                                              $source_id=0) {
    $sql = "SELECT sa.jo_id, 
                sa.trans_no,
                sa.reference,
                sa.type,
                sa.tran_date,
                l.location_name,
                sa.employee_code,
                sa.project_id, 
                pm.description project_description,
                IF(sa.increase = 1 , 'Yes', 'No') increase,  
                sa.comments,
                sa.approval_status, 
                sa.id,  
                sa2.type issue_type,   
                sa2.trans_no issue_trans_no  
            FROM ".TB_PREF."job_material_issues sa  
                JOIN ".TB_PREF."projects_master pm 
                    ON sa.project_id = pm.project_id 
                JOIN ".TB_PREF."locations l 
                    ON sa.loc_code = l.loc_code 
                LEFT JOIN ".TB_PREF."job_material_issues sa2 
                    ON sa.jmr_no = sa2.id 
                        AND sa2.type = ".db_escape(ST_PERSONAL_MATERIAL_ISSUE)." 
            WHERE sa.type IN (".implode(',', array($type)).")";
    
        if ($trans_no > 0 || $jo_id > 0 || $source_id > 0) {
            if ($trans_no > 0) {
                $sql .= " AND sa.trans_no = ".db_escape($trans_no);
            }
            
            if ($jo_id > 0) {
                $sql .= " AND sa.jo_id = ".db_escape($jo_id);
            }
            
            if ($source_id > 0) {
                $sql .= " AND sa2.trans_no = ".db_escape($source_id);
            }
        } else {
            if (strlen($reference) > 0) {
                $sql .= " AND sa.reference = ".db_escape($reference);
            }
            
            $sql .= " AND sa.tran_date >= ".db_escape(date2sql($from_date))." AND sa.tran_date <= ".db_escape(date2sql($to_date)); 

            if($project_id > 0){
                $sql .= " AND sa.project_id = ".db_escape($project_id);
            }

            if ($employee_code != 0) {        
                $sql .= " AND sa.employee_code = ".db_escape($employee_code);
            }
            
            if ($ap_status > -1) {        
                $sql .= " AND sa.approval_status = ".db_escape($ap_status);
            }  
        }
    
        $sql .= " GROUP BY sa.id ORDER BY sa.tran_date DESC, sa.trans_no DESC";
    
        return $sql;
}

function get_sql_for_approve_personal_material_issues($current_user_id, $type) {
    $sql = "SELECT jmi.id,
                jmi.trans_no,
                jmi.type,
                jmi.jo_id,
                jmi.reference,
                jmi.tran_date,                   
                l.location_name, 
                jmi.employee_code,
                jmi.project_id,                   
                pm.description project_description,
                c.real_name,
                apgp.approval_group_description,
                jmi.created_on,
                jmi.approval_status,  
                jmi.next_approval_group_id  
            FROM ".TB_PREF."job_material_issues jmi 
                JOIN ".TB_PREF."projects_master pm 
                    ON jmi.project_id = pm.project_id
                JOIN ".TB_PREF."users c 
                    ON jmi.created_by = c.id  
                JOIN ".TB_PREF."approvers apr 
                    ON jmi.next_approval_group_id = apr.approval_group_id  
                JOIN ".TB_PREF."user_data_access_groups udag 
                    ON pm.dag_id = udag.dag_id 
                        AND udag.user_id = apr.user_id  
                JOIN ".TB_PREF."users u 
                    ON apr.user_id = u.id 
                LEFT JOIN ".TB_PREF."approval_group apgp  
                    ON jmi.next_approval_group_id = apgp.approval_group_id  
                LEFT JOIN ".TB_PREF."locations l 
                    ON jmi.loc_code = l.loc_code   
            WHERE jmi.approval_status IN (".implode(',', array(AS_AWAITING_APPROVAL, AS_WITH_THE_APPROVER)).") 
                AND apr.user_id = ".db_escape($current_user_id)." 
                AND jmi.type = ".db_escape($type);
      
        return $sql;
}

function get_personal_material_issue_lines_with_persoanal_material_return_details($issue_trans_no, $return_trans_no) {
    $sql = "SELECT ard.*,
                am.description stock_description,
                am.stock_id,
                am.mb_flag,
                un.name unit_name,
                ps.description site_description, 
                pt.description task_description, 
                am.stock_control_method,  
                pmrt.trans_id return_line_id,  
                pmrt.tran_unit return_tran_unit,  
                pmrt.tran_qty return_tran_qty,  
                pmrt.project_site_id return_project_site_id,    
                pmrt.line_comment return_line_comment
            FROM ".TB_PREF."job_material_issue_details ard 
                JOIN ".TB_PREF."locations l 
                    ON ard.loc_code = l.loc_code 
                LEFT JOIN ".TB_PREF."stock_master am 
                    ON ard.stock_id = am.stock_id 
                LEFT JOIN ".TB_PREF."item_units un  
                    ON ard.tran_unit = un.abbr  
                LEFT JOIN ".TB_PREF."project_sites ps 
                    ON ard.project_site_id = ps.id 
                LEFT JOIN ".TB_PREF."project_tasks pt 
                    ON ard.task_id = pt.task_id 
                LEFT JOIN ".TB_PREF."job_material_issue_details pmrt  
                    ON ard.trans_id = pmrt.source_line_id  
                        AND pmrt.trans_no = ".db_escape($return_trans_no)."
                        AND pmrt.type = ".db_escape(ST_PERSONAL_MATERIAL_RETURN)."
            WHERE ard.trans_no = ".db_escape($issue_trans_no)." 
                AND ard.type = ".db_escape(ST_PERSONAL_MATERIAL_ISSUE);
    
        return db_query($sql, "get_personal_material_issue_lines_with_persoanal_material_return_details");
}

function save_personal_material_issue_lines_return_alloc_return_qty_total($type, $trans_no) {
    global $systypes_array;
    
    $result = get_job_material_issue_lines($trans_no, $type);
        while ($row = db_fetch($result)) {
            $id = $row['trans_id'];
            $row_state = $row['row_state'];
            
            $up_af_rw = update_personal_material_issue_line_returned_alloc_returned($id, $row_state);
                if ($up_af_rw != 1) {
                    return array("success" => false, "message" => 'Could not update '.$systypes_array[$type].' total return/ return alloc qty.');
                }
        }
        
        return array("success" => true, "message" => '');
}

function update_personal_material_issue_line_returned_alloc_returned($trans_id, $line_row_state=0) {
    $updated_by = $_SESSION['wa_current_user']->user;
    $updated_on = date('Y-m-d H:i:s');
    
        $ap_array = array(AS_NEW, AS_WITH_THE_APPROVER, AS_AWAITING_APPROVAL, AS_BACK_TO_ORIGINATOR);
        $total_returned_alloc_qty_ = get_personal_material_transfer_lines_total_qty(ST_PERSONAL_MATERIAL_RETURN, $trans_id, $ap_array);
        $total_returned_alloc_qty = abs($total_returned_alloc_qty_);

        $total_returned_qty_ = get_personal_material_transfer_lines_total_qty(ST_PERSONAL_MATERIAL_RETURN, $trans_id, array(AS_APPROVED));
        $total_returned_qty = abs($total_returned_qty_);
        
        $all_returned_qty = $total_returned_alloc_qty + $total_returned_qty;

            $sql = "UPDATE ".TB_PREF."job_material_issue_details   
                    SET returned_alloc = ".db_escape($total_returned_alloc_qty).",  
                        updated_by = ".db_escape($updated_by).", 
                        updated_on = ".db_escape($updated_on).",  
                        row_state = ".db_new_row_state($line_row_state)." 
                    WHERE trans_id = ".db_escape($trans_id)." 
                        AND qty >= $all_returned_qty";
            
            db_query($sql, 'update_personal_material_issue_line_returned_alloc_returned');
                $af_row = db_num_affected_rows();
            
                    if ($af_row != 1) {
                        return 0;
                    }
            
    $nw_line_row_state = db_new_row_state($line_row_state);
    $nw_updated_on = date('Y-m-d H:i:s');
            
            $sql_1 = "UPDATE ".TB_PREF."job_material_issue_details   
                    SET returned_quantity = ".db_escape($total_returned_qty).",  
                        updated_by = ".db_escape($updated_by).", 
                        updated_on = ".db_escape($nw_updated_on).",  
                        row_state = ".db_new_row_state($nw_line_row_state)." 
                    WHERE trans_id = ".db_escape($trans_id)." 
                        AND qty >= $all_returned_qty";
            
            db_query($sql_1, 'update_personal_material_issue_line_returned_alloc_returned');
                $af_row_2 = db_num_affected_rows();

                    if ($af_row_2 != 1) {
                        return 0;
                    }

        return 1;
}


?>