<?php

function write_pos_invoice_header($type, $dii_no, $reference, $project_id, $debtor_no, $branch_code, $total_tran_qty, $total_amount, $location, $comments, $date, $float_id, 
                                  $source_id=0, $entry_type_id=0, $paid_amount=0) {
    global $Refs;
     
        if ($dii_no > 0) {
            $af_row = update_pos_invoice_header($dii_no, $reference, $debtor_no, $branch_code, $total_tran_qty, $total_amount, $location, $comments, $date, $float_id, 
                                                $entry_type_id, $paid_amount);
                if (! ($af_row > 0)) {
                    return 0;
                }
        } else {
            $trans_no = get_next_trans_no($type);
            $dii_no = insert_pos_invoice_header($type, $trans_no, $reference, $project_id, $debtor_no, $branch_code, $total_tran_qty, $total_amount, $location, $comments, 
                                                $date, $float_id, $source_id, $entry_type_id, $paid_amount);
        }
    
        $Refs->save($type, $dii_no, $reference);

        if (! ($dii_no > 0)) {
            return 0;
        }

        return $dii_no;
}

function write_pos_invoice_header_by_type_trans_no($type, $trans_no, $reference, $project_id, $movement_type, $linked_field, $linked_field_id, 
                                                        $group_company_code, $location, $comments, $date, $source_id=0) {
    global $Refs;
     
        if ($trans_no > 0) {
            $af_row = update_pos_invoice_header_by_type_trans_no($type, $trans_no, $reference, $group_company_code, $movement_type, $linked_field, 
                                                                      $linked_field_id, $location, $comments, $date);
                if (! ($af_row > 0)) {
                    return 0;
                }
        } else {
            $trans_no = get_next_trans_no($type);
            $inser_id = insert_pos_invoice_header($type, $trans_no, $reference, $project_id, $group_company_code, $movement_type, $linked_field, 
                                                       $linked_field_id, $location, $comments, $date, $source_id);
                if (! ($inser_id > 0)) {
                    return 0;
                }
        }
    
        $Refs->save($type, $trans_no, $reference);

        return $trans_no;
}
     
function insert_pos_invoice_header($type, $trans_no, $reference, $project_id, $debtor_no, $branch_code, $total_tran_qty, $total_amount, $location, $comments, $date, 
                                   $float_id, $source_id=0, $entry_type_id=0, $paid_amount=0) {
    
    $created_by = $_SESSION['wa_current_user']->user;
    $created_on = date('Y-m-d H:i:s');
    
        $sql = "INSERT INTO ".TB_PREF."pos_invoices
                      ( type, trans_no, reference, project_id, 
                        debtor_no, branch_code, 
                        total_tran_qty, total, 
                        loc_code, comments, 
                        tran_date, float_id,
                        created_by, created_on, 
                        source_id, entry_type_id, paid_amount )         
                VALUES( ".db_escape($type).", ".db_escape($trans_no).", ".db_escape($reference).", ".db_escape($project_id).",  
                        ".db_escape($debtor_no).", ".db_escape($branch_code).",  
                        ".db_escape($total_tran_qty).", ".db_escape($total_amount).",  
                        ".db_escape($location).", ".db_escape($comments).", 
                        ".db_escape(date2sql($date)).", ".db_escape($float_id).",
                        ".db_escape($created_by).", ".db_escape($created_on).", 
                        ".db_escape($source_id).", ".db_escape($entry_type_id).", ".db_escape($paid_amount).")";

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

function update_pos_invoice_header($dii_no, $reference, $debtor_no, $branch_code, $total_tran_qty, $total_amount, $location, $comments, $date, $float_id, $entry_type_id=0,
                                   $paid_amount=0) {   
    
    $updated_by = $_SESSION['wa_current_user']->user;
    $updated_on = date('Y-m-d H:i:s');
    
        $sql = "UPDATE ".TB_PREF."pos_invoices
                SET entry_type_id = ".db_escape($entry_type_id).", 
                    reference = ".db_escape($reference).",
                    debtor_no = ".db_escape($debtor_no).",                          
                    branch_code = ".db_escape($branch_code).",                          
                    total_tran_qty = ".db_escape($total_tran_qty).",                          
                    total = ".db_escape($total_amount).",                          
                    loc_code = ".db_escape($location).",                          
                    tran_date = ".db_escape(date2sql($date)).",
                    comments = ".db_escape($comments).",  
                    float_id = ".db_escape($float_id).",  
                    paid_amount = ".db_escape($paid_amount).",  
                    updated_by = ".db_escape($updated_by).", 
                    updated_on = ".db_escape($updated_on)." 
                WHERE id = ".db_escape($dii_no);    

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

function update_pos_invoice_header_by_type_trans_no($type, $trans_no, $reference, $group_company_code, $movement_type, $linked_field_type, $linked_field_id, $location, 
                                                    $comments, $date) {   
    $updated_by = $_SESSION['wa_current_user']->user;
    $updated_on = date('Y-m-d H:i:s');
    
        $sql = "UPDATE ".TB_PREF."pos_invoices
                SET reference = ".db_escape($reference).",
                    loc_code = ".db_escape($location).",                          
                    tran_date = ".db_escape(date2sql($date)).",
                    comments = ".db_escape($comments).", 
                    group_company_code = ".db_escape($group_company_code).", 
                    movement_type = ".db_escape($movement_type).", 
                    linked_field_type = ".db_escape($linked_field_type).", 
                    linked_field_id = ".db_escape($linked_field_id).", 
                    updated_by = ".db_escape($updated_by).", 
                    updated_on = ".db_escape($updated_on)." 
                WHERE type = ".db_escape($type)."     
                    AND trans_no = ".db_escape($trans_no);    

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

function write_pos_invoice_line($trans_no, $line_id, $type, $location, $tran_date, $stock_id, $daily_item_type, $item_description, $tran_unit, 
                                     $tran_qty, $qty, $conversion_factor, $standard_cost, $tran_unit_price, $project_site_id, $line_comment, $source_line_id=0) {
    if ($line_id > 0) {
        return update_pos_invoice_line($line_id, $location, $tran_date, $stock_id, $daily_item_type, $item_description, $tran_unit, $tran_qty, $qty, 
                                            $conversion_factor, $standard_cost, $tran_unit_price, $project_site_id, $line_comment, $source_line_id);
    } else {
        return insert_pos_invoice_line($trans_no, $type, $location, $tran_date, $stock_id, $daily_item_type, $item_description, $tran_unit, 
                                            $tran_qty, $qty, $conversion_factor, $standard_cost, $tran_unit_price, $project_site_id, $line_comment, $source_line_id);
    }
}

function update_pos_invoice_line($line_id, $location, $tran_date, $stock_id, $daily_item_type, $item_description, $tran_unit, $tran_qty, $qty, 
                                      $conversion_factor, $standard_cost, $tran_unit_price, $project_site_id, $line_comment, $source_line_id=0) {
    
    $updated_by = $_SESSION['wa_current_user']->user;
    $updated_on = date('Y-m-d H:i:s');
    
        $sql = "UPDATE ".TB_PREF."pos_invoice_details
                SET loc_code = ".db_escape($location).",
                    tran_date = ".db_escape(date2sql($tran_date)).",
                    stock_id = ".db_escape($stock_id).", 
                    daily_item_type = ".db_escape($daily_item_type).", 
                    item_description = ".db_escape($item_description).", 
                    tran_unit = ".db_escape($tran_unit).",
                    tran_qty = ".db_escape($tran_qty).",
                    qty = ".db_escape($qty).",
                    conversion_factor = ".db_escape($conversion_factor).",
                    standard_cost = ".db_escape($standard_cost).",
                    tran_unit_price = ".db_escape($tran_unit_price).",
                    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).",  
                    source_line_id = ".db_escape($source_line_id)." 
                WHERE trans_id = ".db_escape($line_id);

            db_query($sql, 'update_pos_invoice_line');
            return db_num_affected_rows();
}
        
function insert_pos_invoice_line($trans_no, $type, $location, $tran_date, $stock_id, $daily_item_type, $item_description, $tran_unit, $tran_qty, 
                                      $qty, $conversion_factor, $standard_cost, $tran_unit_price, $project_site_id, $line_comment, $source_line_id=0) {
    
    $created_by = $_SESSION['wa_current_user']->user;
    $created_on = date('Y-m-d H:i:s');
    
        $sql = "INSERT INTO ".TB_PREF."pos_invoice_details
                    (   trans_no, type, loc_code, 
                        tran_date, stock_id, tran_unit, 
                        daily_item_type, item_description, 
                        tran_qty, qty, conversion_factor, 
                        tran_unit_price,
                        standard_cost, project_site_id, line_comment, 
                        created_by, created_on, source_line_id)        
                VALUE ( ".db_escape($trans_no).", ".db_escape($type).", ".db_escape($location).",
                        ".db_escape(date2sql($tran_date)).", ".db_escape($stock_id).", ".db_escape($tran_unit).",  
                        ".db_escape($daily_item_type).", ".db_escape($item_description).",  
                        ".db_escape($tran_qty).", ".db_escape($qty).", ".db_escape($conversion_factor).", 
                        ".db_escape($tran_unit_price).",
                        ".db_escape($standard_cost).", ".db_escape($project_site_id).", ".db_escape($line_comment).", 
                        ".db_escape($created_by).", ".db_escape($created_on).", ".db_escape($source_line_id)." )";      

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

function get_pos_invoice_header_record($dii_no) {
    $sql = "SELECT * 
            FROM ".TB_PREF."pos_invoices    
            WHERE id = ".db_escape($dii_no);
    
        $result = db_query($sql, "get_pos_invoice_header_record");
        return db_fetch($result);
}

function get_pos_invoice_header($dii_no) {
    $sql = "SELECT sa.*,
                l.location_name,
                pm.description project_description, 
                u.real_name requestor_name 
               
            FROM ".TB_PREF."pos_invoices 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 
                JOIN ".TB_PREF."users u 
                    ON sa.created_by = u.id  
            WHERE sa.id = ".db_escape($dii_no);
    
        $result = db_query($sql, "get_pos_invoice_header");
        return db_fetch($result);
}

function get_pos_invoice_header_type_trans_no($type, $trans_no) {
    $sql = "SELECT sa.*,
                l.location_name,
                pm.description project_description, 
                u.real_name requestor_name, 
                et.value_name entry_type_name, 
                flt.name float_name 
            FROM ".TB_PREF."pos_invoices 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 
                JOIN ".TB_PREF."users u 
                    ON sa.created_by = u.id  
                LEFT JOIN ".TB_PREF."list_values et 
                    ON sa.entry_type_id = et.id 
                LEFT JOIN ".TB_PREF."petty_cash_floats flt  
                    ON sa.float_id = flt.id  
            WHERE sa.type = ".db_escape($type)." 
                AND sa.trans_no = ".db_escape($trans_no);
    
        $result = db_query($sql, "get_pos_invoice_header_type_trans_no");
        return db_fetch($result);
}

function get_pos_invoice_header_record_type_trans_no($type, $trans_no) {
    $sql = "SELECT * 
            FROM ".TB_PREF."pos_invoices    
            WHERE type = ".db_escape($type)." 
                AND trans_no = ".db_escape($trans_no);
    
        $result = db_query($sql, "get_pos_invoice_header_record_type_trans_no");
        return db_fetch($result);
}

function delete_pos_invoice_details_record($selected_id) {
    $sql = "DELETE 
            FROM ".TB_PREF."pos_invoice_details 
            WHERE trans_id = ".db_escape($selected_id);
    
        db_query($sql, 'delete_pos_invoice_details_record');
        return db_num_affected_rows();
}

function get_pos_invoice_details($trans_no, $type) {
    $sql = "SELECT ard.*,
                am.description stock_description,
                am.stock_id,
                am.mb_flag,
                un.name unit_name,
                ps.description site_description 
            FROM ".TB_PREF."pos_invoice_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  
            WHERE ard.trans_no = ".db_escape($trans_no)." 
                AND ard.type = ".db_escape($type);
    
        return db_query($sql, "get_pos_invoice_details");
}

function get_pos_invoice_and_return_details($dii_trans_no, $dir_trans_no) {
    $sql = "SELECT ard.*,
                am.description stock_description,
                am.stock_id,
                un.name unit_name,
                ps.description site_description, 
                pt.description task_description, 
                rtd.trans_id rtn_trans_id,
                rtd.trans_no rtn_trans_no,
                rtd.qty rtn_qty,
                rtd.tran_unit rtn_tran_unit,
                rtd.tran_qty rtn_tran_qty, 
                rtd.conversion_factor rtn_conversion_factor, 
                rtd.project_site_id rtn_project_site_id,
                rtd.line_comment rtn_line_comment,
                l2.location_name rtn_location_name 
            FROM ".TB_PREF."pos_invoice_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."pos_invoice_details rtd  
                    ON ard.trans_id = rtd.source_line_id   
                        AND rtd.type = ".db_escape(ST_DAILY_ITEM_RETURN)." 
                        AND rtd.trans_no = ".db_escape($dir_trans_no)." 
                LEFT JOIN ".TB_PREF."locations l2  
                    ON rtd.loc_code = l2.loc_code 
            WHERE ard.trans_no = ".db_escape($dii_trans_no)." 
                AND ard.type = ".db_escape(ST_DAILY_ITEM_ISSUE);
    
        return db_query($sql, "get_pos_invoice_and_return_details");
}

function get_pos_invoice_details_record($id) {
    $sql = "SELECT * 
            FROM ".TB_PREF."pos_invoice_details  
            WHERE trans_id = ".db_escape($id);
    
        $result = db_query($sql, "get_pos_invoice_details_record");
        return db_fetch($result);
}

function get_pos_invoice_details_lines_count($trans_no, $type) {
    $sql = "SELECT COUNT(trans_id) lines_count 
            FROM ".TB_PREF."pos_invoice_details 
            WHERE trans_no = ".db_escape($trans_no)." 
                AND type = ".db_escape($type);
    
        $result = db_query($sql, 'get_pos_invoice_details_lines_count');
        return db_result_value($result, 'lines_count');
}


function get_sql_for_pos_invoices_inquiry($trans_no, $reference, $project_id, $from_date, $to_date, $loc_code, $trans_status, $entry_type_id, $tran_type) {
    $sql = "SELECT  sa.id,
                    sa.trans_no,
                    sa.reference,
                    sa.type,
                    sa.debtor_no,
                    sa.total,
                    l.location_name,
                    sa.tran_date,
                    pm.description project_description, 
                    sa.comments,  
                    sa.approval_status, 
                    et.value_name entry_type_name,  
                    sa.paid_amount 
            FROM ".TB_PREF."pos_invoices sa  
                JOIN ".TB_PREF."debtors_master dm   
                    ON dm.debtor_no = sa.debtor_no  
                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."list_values et 
                    ON sa.entry_type_id = et.id               
            WHERE sa.type IN (".implode(',', array(ST_POS_INVOICE, ST_POS_CREDIT_NOTE)).") ";
    
        if ($trans_no > 0){
            $sql .= " AND sa.trans_no = ".db_escape($trans_no);
        } else {

            if ($tran_type > 0) {
                $sql .= " AND sa.type = ".db_escape($tran_type);
            }
        
            if (strlen($reference) > 0) {
                $sql .= " AND sa.reference = ".db_escape($reference);
            }

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

            $sql .= " AND sa.tran_date >= ".db_escape(date2sql($from_date))." AND sa.tran_date <= ".db_escape(date2sql($to_date)); 

            if ($loc_code != '') {        
                $sql .= " AND sa.loc_code = ".db_escape($loc_code);
            }
            
            if ($trans_status > -1) {        
                $sql .= " AND sa.approval_status = ".db_escape($trans_status);
            } 
            
            if ($entry_type_id > 0) {
                $sql .= " AND sa.entry_type_id = ".db_escape($entry_type_id);
            }
        }
    
        $sql .= " ORDER BY tran_date DESC, sa.id DESC";
    
    return $sql;
}


function update_pos_invoice_header_approval_status($dii_no, $pre_status, $new_status) {
    $approved_by = $_SESSION['wa_current_user']->user;
    $approved_on = date('Y-m-d H:i:s');

        $sql = "UPDATE ".TB_PREF."pos_invoices
                SET approval_status = ".db_escape($new_status).",
                    approved_by = ".db_escape($approved_by).", 
                    approved_on = ".db_escape($approved_on)." 
                WHERE id = ".db_escape($dii_no)."   
                    AND approval_status = ".db_escape($pre_status);    

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

function get_pos_invoice_lines_total($dii_no) {
    $sql_sum = "SELECT SUM(d.tran_qty * d.tran_unit_price) total  
                FROM ".TB_PREF."pos_invoice_details d 
                    JOIN ".TB_PREF."pos_invoices h 
                        ON h.type = d.type 
                        AND h.trans_no = d.trans_no 
                WHERE h.id = ".db_escape($dii_no);    

        $result = db_query($sql_sum, "update_pos_invoice_header_total");
        return db_result_value($result, "total");
}

function update_pos_invoice_header_total($dii_no) {
    $user_id = $_SESSION['wa_current_user']->user;
    $time = date('Y-m-d H:i:s');
    
    $invoice_total = get_pos_invoice_lines_total($dii_no);

        $sql = "UPDATE ".TB_PREF."pos_invoices
                SET total = ".db_escape($invoice_total).",
                    updated_by = ".db_escape($user_id).", 
                    updated_on = ".db_escape($time)." 
                WHERE id = ".db_escape($dii_no);    

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

function update_pos_invoice_header_row_state_by_id($id, $row_state) {
    $sql = "UPDATE ".TB_PREF."pos_invoices
            SET row_state = ".db_escape(db_new_row_state($row_state))." 
            WHERE id = ".db_escape($id)."   
                AND row_state = ".db_escape($row_state);    

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

function get_pos_invoice_line_returned_total($dii_trans_id, $not_rtn_trans_id=0) {
    $sql = "SELECT SUM(qty) returned_total 
            FROM ".TB_PREF."pos_invoice_details 
            WHERE source_line_id = ".db_escape($dii_trans_id)." 
                AND type = ".db_escape(ST_DAILY_ITEM_RETURN);
    
        if ($not_rtn_trans_id > 0) {
            $sql .= " AND trans_id <> ".db_escape($not_rtn_trans_id);
        }
    
        $result = db_query($sql, 'get_pos_invoice_line_returned_total');
        return db_result_value($result, 'returned_total');
}

function update_pos_invoice_line_returned_total($trans_id) {
    $returned_total_ = get_pos_invoice_line_returned_total($trans_id);
    $returned_total = abs($returned_total_);
    
    $sql = "UPDATE ".TB_PREF."pos_invoice_details 
            SET returned_quantity = ".db_escape($returned_total)." 
            WHERE trans_id = ".db_escape($trans_id);
    
        db_query($sql, 'update_pos_invoice_line_returned_total');
        return db_num_affected_rows();
}

function get_pos_invoice_details_lines($trans_no, $type) {
    $sql = "SELECT * 
            FROM ".TB_PREF."pos_invoice_details 
            WHERE trans_no = ".db_escape($trans_no)." 
                AND type = ".db_escape($type);
    
        return db_query($sql, 'get_pos_invoice_details_lines');
}

function get_pos_invoices_detail_inquiry($trans_no, $tran_type, $reference, $entry_type_id, $from_date, $to_date, $description, $loc_code, $float_id) {
    $sql = "SELECT sa.type,
                sa.trans_no,
                sa.reference,
                et.value_name entry_type_name, 
                sa.debtor_no,
                l.location_name,
                sa.tran_date,
                flt.name float_name,
                dt.trans_id,
                dt.daily_item_type,
                dt.item_description,
                un.name unit_name, 
                dt.tran_qty, 
                dt.tran_unit_price,   
                dt.stock_id   
            FROM ".TB_PREF."pos_invoices sa  
                JOIN ".TB_PREF."pos_invoice_details dt    
                    ON sa.type = dt.type   
                        AND sa.trans_no = dt.trans_no   
                JOIN ".TB_PREF."debtors_master dm   
                    ON dm.debtor_no = sa.debtor_no  
                JOIN ".TB_PREF."locations l 
                    ON sa.loc_code = l.loc_code     
                JOIN ".TB_PREF."petty_cash_floats flt  
                    ON sa.float_id = flt.id  
                LEFT JOIN ".TB_PREF."list_values et 
                    ON sa.entry_type_id = et.id  
                LEFT JOIN ".TB_PREF."item_units un  
                    ON dt.tran_unit = un.abbr  
            WHERE 1 = 1 ";
    
        if ($trans_no > 0){
            $sql .= " AND sa.trans_no = ".db_escape($trans_no);
        } else {
            if ($tran_type > 0) {
                $sql .= " AND sa.type = ".db_escape($tran_type);
            }
            
            if (strlen($reference) > 0) {
                $sql .= " AND sa.reference = ".db_escape($reference);
            }
            
            if ($entry_type_id > 0) {
                $sql .= " AND sa.entry_type_id = ".db_escape($entry_type_id);
            }

            $sql .= " AND sa.tran_date >= ".db_escape(date2sql($from_date))." AND sa.tran_date <= ".db_escape(date2sql($to_date)); 

            if (strlen($description) > 0) {
                $sql .= " AND dt.item_description LIKE ".db_escape("%$description%");
            }
            
            if ($loc_code != '') {        
                $sql .= " AND sa.loc_code = ".db_escape($loc_code);
            }

            if ($float_id > 0) {
                $sql .= " AND sa.float_id = ".db_escape($float_id);
            }
        }
    
        $sql .= " ORDER BY sa.tran_date, sa.type, sa.trans_no, dt.trans_id ";
    
        return db_query($sql, 'get_pos_invoices_detail_inquiry');
}

function update_pos_invoice_header_paid_amount($type, $trans_no, $paid_amount, $row_state) {
    $sql = "UPDATE ".TB_PREF."pos_invoices 
            SET paid_amount = ".db_escape($paid_amount).", 
                row_state = ".db_escape(db_new_row_state($row_state))." 
            WHERE type = ".db_escape($type)." 
                AND trans_no = ".db_escape($trans_no)." 
                AND row_state = ".db_escape($row_state)." 
                AND total >= ".db_escape($paid_amount);
    
        db_query($sql, 'update_pos_invoice_header_paid_amount');
        return db_num_affected_rows();
}

?>