1#@!#!123s

: / home / ksclnmuac / public_html / cas / application / models / admin /
Filename : StockAndPurchaseManagement.php
back
<?php

/**
 * Model For Handling All DB Operations Related To Stock & Purchase
 *
 * @author Softpro India Pvt. Ltd.
 */
class StockAndPurchaseManagement extends CI_Model {

    function createNewPurchase(array $newPurchaseInfo) {
        $this->db->insert('purchase_mst', $newPurchaseInfo);
        return $this->db->insert_id();
    }

    function getAllPurchases() {
        $this->db->select("*,"
                . "CONCAT(TPRFLA.tprfl_firstname,' ',TPRFLA.tprfl_lastname) addedByAdmin,"
                . "CONCAT(TPRFLU.tprfl_firstname,' ',TPRFLU.tprfl_lastname) updatedByAdmin");
        $this->db->from('purchase_mst PM');
        $this->db->join('inventory_phead_mst IPHM', 'PM.iphm_id = IPHM.iphm_id');
        $this->db->join('vendors_mst VM', 'PM.ven_id = VM.ven_id');
        $this->db->join('inventory_cat_mst ICM', 'PM.icm_id = ICM.icm_id');
        $this->db->join('tbl_city_master TCM', 'VM.city_id = TCM.city_id');
        $this->db->join('tbl_states_master TSM', 'VM.state_id = TSM.state_id');
        $this->db->join('tbl_staff_members TSMA', 'PM.pm_added_by = TSMA.smember_id');
        $this->db->join('tbl_profile TPRFLA', 'TSMA.smember_id = TPRFLA.tprfl_id');
        $this->db->join('tbl_staff_members TSMU', 'PM.pm_updated_by = TSMU.smember_id');
        $this->db->join('tbl_profile TPRFLU', 'TSMU.smember_id = TPRFLU.tprfl_id');
        $this->db->order_by("PM.pm_updated_on", "desc");
        return $this->db->get();
    }

    function getPurchaseInfoBy($pm_id) {
        $this->db->select("*,"
                . "CONCAT(TPRFLA.tprfl_firstname,' ',TPRFLA.tprfl_lastname) addedByAdmin,"
                . "CONCAT(TPRFLU.tprfl_firstname,' ',TPRFLU.tprfl_lastname) updatedByAdmin");
        $this->db->from('purchase_mst PM');
        $this->db->join('inventory_phead_mst IPHM', 'PM.iphm_id = IPHM.iphm_id');
        $this->db->join('vendors_mst VM', 'PM.ven_id = VM.ven_id');
        $this->db->join('inventory_cat_mst ICM', 'PM.icm_id = ICM.icm_id');
        $this->db->join('tbl_city_master TCM', 'VM.city_id = TCM.city_id');
        $this->db->join('tbl_states_master TSM', 'VM.state_id = TSM.state_id');
        $this->db->join('tbl_staff_members TSMA', 'PM.pm_added_by = TSMA.smember_id');
        $this->db->join('tbl_profile TPRFLA', 'TSMA.smember_id = TPRFLA.tprfl_id');
        $this->db->join('tbl_staff_members TSMU', 'PM.pm_updated_by = TSMU.smember_id');
        $this->db->join('tbl_profile TPRFLU', 'TSMU.smember_id = TPRFLU.tprfl_id');
        $this->db->where("PM.pm_id", $pm_id);
        return $this->db->get();
    }

    function getPurchaseInfoByInvoiceNumber($pm_invoice_number) {
        $this->db->select('*');
        $this->db->from('purchase_mst');
        $this->db->where('pm_invoice_number', $pm_invoice_number);
        return $this->db->get();
    }

    function getPurchaseInfoByPaymentVoucherNumber($pm_pvoucher_number) {
        $this->db->select('*');
        $this->db->from('purchase_mst');
        $this->db->where('pm_pvoucher_number', $pm_pvoucher_number);
        return $this->db->get();
    }

    function getPurchaseInfoByPurchaseHead($iphm_id) {
        $this->db->select('*');
        $this->db->from('purchase_mst');
        $this->db->where('iphm_id', $iphm_id);
        return $this->db->get();
    }

    function isPurchaseInvoiceNumberSafeUpdate($pm_id, $pm_invoice_number) {
        $this->db->select("*");
        $this->db->from('purchase_mst');
        $this->db->where("pm_invoice_number", $pm_invoice_number);
        $this->db->where('pm_id != ' . $pm_id);
        $result = $this->db->get()->result();
        if (sizeof($result)) {
            return FALSE;
        } else {
            return TRUE;
        }
    }

    function isPurchasePVoucherNumberSafeUpdate($pm_id, $pm_pvoucher_number) {
        $this->db->select("*");
        $this->db->from('purchase_mst');
        $this->db->where("pm_pvoucher_number", $pm_pvoucher_number);
        $this->db->where('pm_id != ' . $pm_id);
        $result = $this->db->get()->result();
        if (sizeof($result)) {
            return FALSE;
        } else {
            return TRUE;
        }
    }

    function updatePurchaseInfo(array $purchaseUpdatedInfo) {
        $this->db->where('pm_id', $purchaseUpdatedInfo['pm_id']);
        return $this->db->update('purchase_mst', $purchaseUpdatedInfo);
    }

    function deletePurchaseInfoEntry($pm_id) {
        $this->db->where("pm_id", $pm_id);
        $this->db->delete("purchase_mst");
    }

    /* Functions For Purchase Details */

    function createPurchaseDetailsInfoMulti(array $newPurchaseDetailsInfoBatch) {
        $this->db->insert_batch('purchase_detail_mst', $newPurchaseDetailsInfoBatch);
        return $this->db->insert_id();
    }

    function getAllPurchaseItems() {
        $this->db->distinct("pdm_id,pdm_item_name");
        $this->db->from('purchase_detail_mst PDM');
        $this->db->order_by('PDM.pdm_item_name', 'asc');
        return $this->db->get();
    }

    function getAllPurchaseDetails() {
        $this->db->select("*");
        $this->db->from('purchase_detail_mst PDM');
        $this->db->join('purchase_mst PM', 'PDM.pm_id = PM.pm_id');
        $this->db->order_by('PM.pm_invoice_number,PDM.pdm_id', 'desc');
        return $this->db->get();
    }

    function getPurchaseDetailsInfoBy($pdm_id) {
        $this->db->select('*');
        $this->db->from('purchase_detail_mst PDM');
        $this->db->where('PDM.pdm_id', $pdm_id);
        return $this->db->get();
    }

    function getPurchaseDetailsBy($pm_id) {
        $this->db->select('*');
        $this->db->from('purchase_detail_mst PDM');
        $this->db->where('PDM.pm_id', $pm_id);
        return $this->db->get();
    }

    function updatePurchaseDetailInfo(array $puchaseDetailsUpdatedInfo) {
        $this->db->where('pdm_id', $puchaseDetailsUpdatedInfo['pdm_id']);
        return $this->db->update('purchase_detail_mst', $puchaseDetailsUpdatedInfo);
    }

    function updatePurchaseDetailInfoMulti(array $purchaseDetailInfoBulk) {
        return $this->db->update_batch('purchase_detail_mst', $purchaseDetailInfoBulk, 'pdm_id');
    }

    function deletePurchaseDetailInfoEntry($pm_id) {
        $this->db->where("pm_id", $pm_id);
        $this->db->delete("purchase_detail_mst");
    }

    function deletePurchaseDetailInfoBy($pdm_id) {
        $this->db->where("pdm_id", $pdm_id);
        $this->db->delete("purchase_detail_mst");
    }

    /* Functions For Payment Vouchers */

    function createPurchaseVoucher(array $newVoucherInfo) {
        $this->db->insert('purchase_voucher_mst', $newVoucherInfo);
        return $this->db->insert_id();
    }

    function getPurchaseVouchers() {
        $this->db->select("*,"
                . "CONCAT(TPRFLA.tprfl_firstname,' ',TPRFLA.tprfl_lastname) addedByAdmin,"
                . "CONCAT(TPRFLU.tprfl_firstname,' ',TPRFLU.tprfl_lastname) updatedByAdmin");
        $this->db->from('purchase_voucher_mst PVM');
        $this->db->join('purchase_mst PM', "PVM.pm_id = PM.pm_id");
        $this->db->join('tbl_staff_members TSMA', 'PVM.pvm_added_by = TSMA.smember_id');
        $this->db->join('tbl_profile TPRFLA', 'TSMA.smember_id = TPRFLA.tprfl_id');
        $this->db->join('tbl_staff_members TSMU', 'PVM.pvm_updated_by = TSMU.smember_id');
        $this->db->join('tbl_profile TPRFLU', 'TSMU.smember_id = TPRFLU.tprfl_id');
        $this->db->order_by('PVM.pvm_updated_on', "desc");
        return $this->db->get();
    }

    function getPurchaseVouchersInfoBy($pvm_id) {
        $this->db->select("*,"
                . "CONCAT(TPRFLA.tprfl_firstname,' ',TPRFLA.tprfl_lastname) addedByAdmin,"
                . "CONCAT(TPRFLU.tprfl_firstname,' ',TPRFLU.tprfl_lastname) updatedByAdmin");
        $this->db->from('purchase_voucher_mst PVM');
        $this->db->join('purchase_mst PM', "PVM.pm_id = PM.pm_id");
        $this->db->join('tbl_staff_members TSMA', 'PVM.pvm_added_by = TSMA.smember_id');
        $this->db->join('tbl_profile TPRFLA', 'TSMA.smember_id = TPRFLA.tprfl_id');
        $this->db->join('tbl_staff_members TSMU', 'PVM.pvm_updated_by = TSMU.smember_id');
        $this->db->join('tbl_profile TPRFLU', 'TSMU.smember_id = TPRFLU.tprfl_id');
        $this->db->where('PVM.pvm_id', $pvm_id);
        return $this->db->get();
    }

    function getPurchaseVouchersBy($pm_id) {
        $this->db->select("*,"
                . "CONCAT(TPRFLA.tprfl_firstname,' ',TPRFLA.tprfl_lastname) addedByAdmin,"
                . "CONCAT(TPRFLU.tprfl_firstname,' ',TPRFLU.tprfl_lastname) updatedByAdmin");
        $this->db->from('purchase_voucher_mst PVM');
        $this->db->join('purchase_mst PM', "PVM.pm_id = PM.pm_id");
        $this->db->join('tbl_staff_members TSMA', 'PVM.pvm_added_by = TSMA.smember_id');
        $this->db->join('tbl_profile TPRFLA', 'TSMA.smember_id = TPRFLA.tprfl_id');
        $this->db->join('tbl_staff_members TSMU', 'PVM.pvm_updated_by = TSMU.smember_id');
        $this->db->join('tbl_profile TPRFLU', 'TSMU.smember_id = TPRFLU.tprfl_id');
        $this->db->order_by('PVM.pvm_date', "desc");
        $this->db->where('PVM.pm_id', $pm_id);
        return $this->db->get();
    }

    function getLastPaymentVoucherInfoBy($pm_id) {
        $this->db->select("*,"
                . "CONCAT(TPRFLA.tprfl_firstname,' ',TPRFLA.tprfl_lastname) addedByAdmin,"
                . "CONCAT(TPRFLU.tprfl_firstname,' ',TPRFLU.tprfl_lastname) updatedByAdmin");
        $this->db->from('purchase_voucher_mst PVM');
        $this->db->join('purchase_mst PM', "PVM.pm_id = PM.pm_id");
        $this->db->join('tbl_staff_members TSMA', 'PVM.pvm_added_by = TSMA.smember_id');
        $this->db->join('tbl_profile TPRFLA', 'TSMA.smember_id = TPRFLA.tprfl_id');
        $this->db->join('tbl_staff_members TSMU', 'PVM.pvm_updated_by = TSMU.smember_id');
        $this->db->join('tbl_profile TPRFLU', 'TSMU.smember_id = TPRFLU.tprfl_id');
        $this->db->where("PVM.pvm_id = (select max(pvm_id) from purchase_voucher_mst where pm_id =" . $pm_id . " )");
        return $this->db->get();
    }

    function deletePurchaseVoucher($pvm_id) {
        $this->db->where("pvm_id", $pvm_id);
        $this->db->delete("purchase_voucher_mst");
    }

    /* Functions For Inventories & Stock */

    function createNewStock(array $newStockInfo) {
        $this->db->insert('stock_mst', $newStockInfo);
        return $this->db->insert_id();
    }

    function createNewStockInfoMulti(array $newStockInfoBatch) {
        $this->db->insert_batch('stock_mst', $newStockInfoBatch);
        return $this->db->insert_id();
    }

    function getAllStocks() {
        $this->db->select('*');
        $this->db->from('stock_mst SM');
        $this->db->join('purchase_detail_mst PDM', "SM.pdm_id = PDM.pdm_id");
        $this->db->join('purchase_mst PM', "PDM.pm_id = PM.pm_id");
        $this->db->order_by('SM.sm_updated_on', 'desc');
        return $this->db->get();
    }

    function getAvailableStocksForIssue($pHead = '', $invoice = '', $invCategory = '', $stockItem = '') {
        $this->db->select('*');
        $this->db->from('stock_mst SM');
        $this->db->join('purchase_detail_mst PDM', "SM.pdm_id = PDM.pdm_id");
        $this->db->join('purchase_mst PM', "PDM.pm_id = PM.pm_id");
        $this->db->where("sm_id NOT IN (SELECT sm_id FROM issue_return_mst WHERE irm_return_date IS NULL)");
        if ($pHead != "") {
            $this->db->where("PM.iphm_id", $pHead);
        }
        if ($invoice != "") {
            $this->db->where("PM.pm_id", $invoice);
        }
        if ($invCategory != "") {
            $this->db->where("PM.icm_id", $invCategory);
        }
        if ($stockItem != "") {
            $this->db->where("PDM.pdm_id", $stockItem);
        }
        $this->db->order_by('SM.sm_updated_on', 'desc');
        return $this->db->get();
    }

    function getStockInfoBy($sm_id) {
        $this->db->select('*');
        $this->db->from('stock_mst SM');
        $this->db->join('purchase_detail_mst PDM', "SM.pdm_id = PDM.pdm_id");
        $this->db->join('purchase_mst PM', "PDM.pm_id = PM.pm_id");
        $this->db->where('SM.sm_id', $sm_id);
        return $this->db->get();
    }

    function getStockInfoByUID($sm_uid) {
        $this->db->select('*');
        $this->db->from('stock_mst SM');
        $this->db->join('purchase_detail_mst PDM', "SM.pdm_id = PDM.pdm_id");
        $this->db->join('purchase_mst PM', "PDM.pm_id = PM.pm_id");
        $this->db->where('SM.sm_uid', $sm_uid);
        return $this->db->get();
    }

    function getStockInfoByUIDs(array $sm_uids) {
        $this->db->select('*');
        $this->db->from('stock_mst SM');
        $this->db->join('purchase_detail_mst PDM', "SM.pdm_id = PDM.pdm_id");
        $this->db->join('purchase_mst PM', "PDM.pm_id = PM.pm_id");
        $this->db->where_in('SM.sm_uid', $sm_uids);
        return $this->db->get();
    }

    function getStockInfoBySrNo($sm_srno) {
        $this->db->select('*');
        $this->db->from('stock_mst SM');
        $this->db->join('purchase_detail_mst PDM', "SM.pdm_id = PDM.pdm_id");
        $this->db->join('purchase_mst PM', "PDM.pm_id = PM.pm_id");
        $this->db->where('SM.sm_sr_no', $sm_srno);
        return $this->db->get();
    }

    function getStockInfoBySrNos(array $sm_srnos) {
        $this->db->select('*');
        $this->db->from('stock_mst SM');
        $this->db->join('purchase_detail_mst PDM', "SM.pdm_id = PDM.pdm_id");
        $this->db->join('purchase_mst PM', "PDM.pm_id = PM.pm_id");
        $this->db->where_in('SM.sm_sr_no', $sm_srnos);
        return $this->db->get();
    }

    function getAllStocksBy($pdm_id) {
        $this->db->select("*,CONCAT(TPRFLA.tprfl_firstname,' ',TPRFLA.tprfl_lastname) addedByAdmin");
        $this->db->from('stock_mst SM');
        $this->db->join('purchase_detail_mst PDM', "SM.pdm_id = PDM.pdm_id");
        $this->db->join('tbl_staff_members TSMA', 'SM.sm_updated_by = TSMA.smember_id');
        $this->db->join('tbl_profile TPRFLA', 'TSMA.smember_id = TPRFLA.tprfl_id');
        $this->db->where('SM.pdm_id', $pdm_id);
        return $this->db->get();
    }

    function isStockUIDSafeUpdate($sm_id, $sm_uid) {
        $this->db->select("*");
        $this->db->from('stock_mst');
        $this->db->where('sm_uid', $sm_uid);
        $this->db->where('sm_id != ' . $sm_id);
        $result = $this->db->get()->result();
        if (sizeof($result)) {
            return FALSE;
        } else {
            return TRUE;
        }
    }

    function isStockSerialNumberSafeUpdate($sm_id, $sm_srno) {
        $this->db->select("*");
        $this->db->from('stock_mst');
        $this->db->where('sm_sr_no', $sm_srno);
        $this->db->where('sm_id != ' . $sm_id);
        $result = $this->db->get()->result();
        if (sizeof($result)) {
            return FALSE;
        } else {
            return TRUE;
        }
    }

    function updateStockInfo(array $stockUpdatedInfo) {
        $this->db->where('sm_id', $stockUpdatedInfo['sm_id']);
        return $this->db->update('stock_mst', $stockUpdatedInfo);
    }

    function deleteStockInfoByPurchaseDetail($pdm_id) {
        $this->db->where("pdm_id", $pdm_id);
        $this->db->delete("stock_mst");
    }

    function deleteStockInfoBy($sm_id) {
        $this->db->where("sm_id", $sm_id);
        $this->db->delete("stock_mst");
    }

    /* Functions For Issue & Returns */

    function createNewItemIssue($newItemIssueInfo) {
        $this->db->insert('issue_return_mst', $newItemIssueInfo);
        return $this->db->insert_id();
    }

    function isStockItemIussed($sm_id) {
        $this->db->select("*");
        $this->db->from('issue_return_mst');
        $this->db->where("(irm_return_date = '' OR irm_return_date IS NULL)");
        $this->db->where('sm_id', $sm_id);
        $result = $this->db->get()->result();
        if (sizeof($result)) {
            return TRUE;
        } else {
            return FALSE;
        }
    }

    function updateIssueInfo(array $issueItemUpdatedInfo) {
        $this->db->where('irm_id', $issueItemUpdatedInfo['irm_id']);
        return $this->db->update('issue_return_mst', $issueItemUpdatedInfo);
    }

    function updateIssueInfoMulti(array $issueItemUpdatedInfo) {
        return $this->db->update_batch('issue_return_mst', $issueItemUpdatedInfo, 'irm_id');
    }

    function deleteIssueReturnInfoBy($sm_id) {
        $this->db->where("sm_id", $sm_id);
        $this->db->delete("issue_return_mst");
    }

    function deleteIssueReturnInfo($irm_id) {
        $this->db->where("irm_id", $irm_id);
        $this->db->delete("issue_return_mst");
    }

    /* Functions For Verification & Related Stuff */

    function createStockVerificationTitle(array $newVerificationTitle) {
        $this->db->insert('stock_verification_title_mst', $newVerificationTitle);
        return $this->db->insert_id();
    }

    function getAllVerificationTitles() {
        $this->db->select("*,"
                . "CONCAT(TPRFLA.tprfl_firstname,' ',TPRFLA.tprfl_lastname) addedByAdmin,"
                . "CONCAT(TPRFLU.tprfl_firstname,' ',TPRFLU.tprfl_lastname) updatedByAdmin");
        $this->db->from('stock_verification_title_mst SVT');
        $this->db->join('tbl_staff_members TSMA', 'SVT.svtm_created_by = TSMA.smember_id');
        $this->db->join('tbl_profile TPRFLA', 'TSMA.smember_id = TPRFLA.tprfl_id');
        $this->db->join('tbl_staff_members TSMU', 'SVT.svtm_updated_by = TSMU.smember_id');
        $this->db->join('tbl_profile TPRFLU', 'TSMU.smember_id = TPRFLU.tprfl_id');
        $this->db->order_by("SVT.svtm_updated_on", "desc");
        return $this->db->get();
    }

    function getNonDeletedActiveVerificationTitles() {
        $this->db->select("*,"
                . "CONCAT(TPRFLA.tprfl_firstname,' ',TPRFLA.tprfl_lastname) addedByAdmin,"
                . "CONCAT(TPRFLU.tprfl_firstname,' ',TPRFLU.tprfl_lastname) updatedByAdmin");
        $this->db->from('stock_verification_title_mst SVT');
        $this->db->join('tbl_staff_members TSMA', 'SVT.svtm_created_by = TSMA.smember_id');
        $this->db->join('tbl_profile TPRFLA', 'TSMA.smember_id = TPRFLA.tprfl_id');
        $this->db->join('tbl_staff_members TSMU', 'SVT.svtm_updated_by = TSMU.smember_id');
        $this->db->join('tbl_profile TPRFLU', 'TSMU.smember_id = TPRFLU.tprfl_id');
        $this->db->where("SVT.svtm_active_status", "T");
        $this->db->where("SVT.svtm_delete_status", "F");
        $this->db->order_by("SVT.svtm_updated_on", "desc");
        return $this->db->get();
    }

    function getVerificationTitleInfoBy($svtm_id) {
        $this->db->select("*");
        $this->db->from('stock_verification_title_mst SVT');
        $this->db->where("SVT.svtm_id", $svtm_id);
        return $this->db->get();
    }

    function getVerificationTitleInfoByTitle($svtm_name) {
        $this->db->select("*");
        $this->db->from('stock_verification_title_mst SVT');
        $this->db->where("SVT.svtm_name", $svtm_name);
        return $this->db->get();
    }

    function isVerificationTitleSafeUpdate($svtm_id, $svtm_name) {
        $this->db->select("*");
        $this->db->from('stock_verification_title_mst');
        $this->db->where('svtm_name', $svtm_name);
        $this->db->where('svtm_id != ' . $svtm_id);
        $result = $this->db->get()->result();
        if (sizeof($result)) {
            return FALSE;
        } else {
            return TRUE;
        }
    }

    function updateStockVerificationInfo(array $verificationTitleUpdatedInfo) {
        $this->db->where('svtm_id', $verificationTitleUpdatedInfo['svtm_id']);
        return $this->db->update('stock_verification_title_mst', $verificationTitleUpdatedInfo);
    }

    function createStockVerificationInfoMulti(array $newVerificationInfoBatch) {
        $this->db->insert_batch('stock_verification', $newVerificationInfoBatch);
        return $this->db->insert_id();
    }

    function stocksForVerification($svtm_id, $uidOrSrno = '', $pHead = '', $invoice = '', $dept = '', $subDept = '', $emp = '', $invCategory = '', $loc = '', $stockItem = '', $startDate = '', $endDate = '') {
        $query = "select *,IRM.irm_id from stock_mst SM, tbl_department_master TDEPT, tbl_sub_departments_master TSUBDEPT, location_mst LM, "
                . "purchase_mst PM, purchase_detail_mst PDM, (select * from issue_return_mst WHERE irm_id NOT IN "
                . "(SELECT irm_id FROM stock_verification WHERE svtm_id = " . $svtm_id . ")) as IRM left join "
                . "(select irm_id,sv_date from  stock_verification where sv_id in "
                . "(select MAX(SVV.sv_id) from stock_verification SVV where SVV.svtm_id < " . $svtm_id . " group by irm_id)) AS SV on "
                . "IRM.irm_id = SV.irm_id left join "
                . "(SELECT TPRFL.tprfl_id, TPRFL.tprfl_firstname,TPRFL.tprfl_lastname, TSMI.smember_id FROM tbl_staff_members TSMI, "
                . "tbl_profile TPRFL WHERE TSMI.tprfl_id = TPRFL.tprfl_id) TT "
                . "ON IRM.smember_id = TT.smember_id WHERE IRM.sm_id = SM.sm_id AND SM.pdm_id = PDM.pdm_id "
                . "AND PDM.pm_id = PM.pm_id AND IRM.lm_id = LM.lm_id AND IRM.dept_id = TDEPT.dept_id "
                . "AND IRM.sub_dept_id = TSUBDEPT.sub_dept_id AND (IRM.irm_return_date = '' OR IRM.irm_return_date IS NULL) "
                . "AND (SM.sm_uid LIKE '%" . $uidOrSrno . "%' OR SM.sm_sr_no LIKE '%" . $uidOrSrno . "%')";
        if ($pHead != "") {
            $query .= " AND PM.iphm_id = " . $pHead;
        }
        if ($invoice != "") {
            $query .= " AND PM.pm_id = " . $invoice;
        }
        if ($dept != "") {
            $query .= " AND IRM.dept_id = " . $dept;
        }
        if ($subDept != "") {
            $query .= " AND IRM.sub_dept_id = " . $subDept;
        }
        if ($invCategory != "") {
            $query .= " AND PM.icm_id = " . $invCategory;
        }
        if ($loc != "") {
            $query .= " AND LM.lm_id = " . $loc;
        }
        if ($stockItem != "") {
            $query .= " AND PDM.pdm_id = " . $stockItem;
        }
        if ($emp != "") {
            $query .= " AND IRM.smember_id = " . $emp;
        }
        /* Dates Combination */
        if ($startDate != '' && $endDate != '') {
            $query .= " AND IRM.irm_issue_date >= '" . $startDate . "' && IRM.irm_issue_date <= '" . $endDate . "'";
        } else if ($startDate == '' && $endDate != '') {
            $query .= " AND IRM.irm_issue_date <= '" . $endDate . "'";
        } else if ($startDate != '' && $endDate == '') {
            $query .= " AND IRM.irm_issue_date >= '" . $startDate . "'";
        }
        /* Dates Combination */
        return $this->db->query($query);
    }
    
    function deleteVerificationInfo($sv_id) {
        $this->db->where("sv_id", $sv_id);
        $this->db->delete("stock_verification");
    }

    /* Functions For Reporting */

    function pendingPaymentsReports() {
        $this->db->select("*,PENDING_INVOICE_PAYMENT(PM.pm_id) AS pending_amount,PAID_INVOICE_PAYMENT(PM.pm_id) AS paid_amount,");
        $this->db->from('purchase_mst PM');
        $this->db->join('inventory_phead_mst IPHM', 'PM.iphm_id = IPHM.iphm_id');
        $this->db->join('vendors_mst VM', 'PM.ven_id = VM.ven_id');
        $this->db->join('inventory_cat_mst ICM', 'PM.icm_id = ICM.icm_id');
        $this->db->join('tbl_city_master TCM', 'VM.city_id = TCM.city_id');
        $this->db->join('tbl_states_master TSM', 'VM.state_id = TSM.state_id');
        $this->db->order_by("PM.pm_updated_on", "desc");
        return $this->db->get();
    }

    function pendingPaymentsReport() {
        $this->db->select("*,PENDING_INVOICE_PAYMENT(PM.pm_id) AS pending_amount,PAID_INVOICE_PAYMENT(PM.pm_id) AS paid_amount,");
        $this->db->from('purchase_mst PM');
        $this->db->join('inventory_phead_mst IPHM', 'PM.iphm_id = IPHM.iphm_id');
        $this->db->join('vendors_mst VM', 'PM.ven_id = VM.ven_id');
        $this->db->join('inventory_cat_mst ICM', 'PM.icm_id = ICM.icm_id');
        $this->db->join('tbl_city_master TCM', 'VM.city_id = TCM.city_id');
        $this->db->join('tbl_states_master TSM', 'VM.state_id = TSM.state_id');
        $this->db->order_by("PM.pm_updated_on", "desc");
        return $this->db->get();
    }

    function issuedStocksReport($uidOrSrno = '', $pHead = '', $invoice = '', $dept = '', $subDept = '', $emp = '', $invCategory = '', $loc = '', $stockItem = '', $startDate = '', $endDate = '') {
        $this->db->select("*,IRM.smember_id issuedToId,CONCAT(TPRFLI.tprfl_firstname,' ',TPRFLI.tprfl_lastname) issuedBy,CONCAT(TPRFLIT.tprfl_firstname,' ',TPRFLIT.tprfl_lastname) issuedTo");
        $this->db->from('issue_return_mst IRM');
        $this->db->join('stock_mst SM', 'IRM.sm_id = SM.sm_id');
        $this->db->join('purchase_detail_mst PDM', 'SM.pdm_id = PDM.pdm_id');
        $this->db->join('purchase_mst PM', 'PDM.pm_id = PM.pm_id');
        $this->db->join('location_mst LM', 'IRM.lm_id = LM.lm_id');
        $this->db->join('tbl_department_master TDEPT', 'IRM.dept_id = TDEPT.dept_id');
        $this->db->join('tbl_sub_departments_master TSUBDEPT', 'IRM.sub_dept_id = TSUBDEPT.sub_dept_id');
        $this->db->join('tbl_staff_members TSMIT', 'IRM.smember_id = TSMIT.smember_id', 'left');
        $this->db->join('tbl_profile TPRFLIT', 'IRM.smember_id = TPRFLIT.tprfl_id', 'left');
        $this->db->join('tbl_staff_members TSMI', 'IRM.irm_issued_by = TSMI.smember_id');
        $this->db->join('tbl_profile TPRFLI', 'TSMI.smember_id = TPRFLI.tprfl_id');
        $this->db->where("(IRM.irm_return_date = '' OR IRM.irm_return_date IS NULL)");
        $this->db->where("(SM.sm_uid LIKE '%" . $uidOrSrno . "%' OR SM.sm_sr_no LIKE '%" . $uidOrSrno . "%')");
        if ($pHead != "") {
            $this->db->where("PM.iphm_id", $pHead);
        }
        if ($invoice != "") {
            $this->db->where("PM.pm_id", $invoice);
        }
        if ($dept != "") {
            $this->db->where("IRM.dept_id", $dept);
        }
        if ($subDept != "") {
            $this->db->where("IRM.sub_dept_id", $subDept);
        }
        if ($invCategory != "") {
            $this->db->where("PM.icm_id", $invCategory);
        }
        if ($loc != "") {
            $this->db->where("LM.lm_id", $loc);
        }
        if ($stockItem != "") {
            $this->db->where("PDM.pdm_id", $stockItem);
        }
        if ($emp != "") {
            $this->db->where("IRM.smember_id", $emp);
        }
        /* Dates Combination */
        if ($startDate != '' && $endDate != '') {
            $this->db->where("IRM.irm_issue_date >= '" . $startDate . "' && IRM.irm_issue_date <= '" . $endDate . "'");
        } else if ($startDate == '' && $endDate != '') {
            $this->db->where("IRM.irm_issue_date <= '" . $endDate . "'");
        } else if ($startDate != '' && $endDate == '') {
            $this->db->where("IRM.irm_issue_date >= '" . $startDate . "'");
        }
        /* Dates Combination */
        return $this->db->get();
    }

    function verifiedStocksReport($svtm_id, $uidOrSrno = '', $pHead = '', $invoice = '', $dept = '', $subDept = '', $emp = '', $invCategory = '', $loc = '', $stockItem = '', $startDate = '', $endDate = '') {
        $this->db->select("*,IRM.smember_id issuedToId,"
                . "CONCAT(TPRFLVB.tprfl_firstname,' ',TPRFLVB.tprfl_lastname) verifiedBy,"
                . "CONCAT(TPRFLI.tprfl_firstname,' ',TPRFLI.tprfl_lastname) issuedBy,"
                . "CONCAT(TPRFLIT.tprfl_firstname,' ',TPRFLIT.tprfl_lastname) issuedTo");
        $this->db->from('stock_verification SV');
        $this->db->join('issue_return_mst IRM', 'SV.irm_id = IRM.irm_id');
        $this->db->join('stock_mst SM', 'IRM.sm_id = SM.sm_id');
        $this->db->join('purchase_detail_mst PDM', 'SM.pdm_id = PDM.pdm_id');
        $this->db->join('purchase_mst PM', 'PDM.pm_id = PM.pm_id');
        $this->db->join('location_mst LM', 'IRM.lm_id = LM.lm_id');
        $this->db->join('tbl_department_master TDEPT', 'IRM.dept_id = TDEPT.dept_id');
        $this->db->join('tbl_sub_departments_master TSUBDEPT', 'IRM.sub_dept_id = TSUBDEPT.sub_dept_id');
        $this->db->join('tbl_staff_members TSMVB', 'SV.sv_by_id = TSMVB.smember_id', 'left');
        $this->db->join('tbl_profile TPRFLVB', 'TSMVB.smember_id = TPRFLVB.tprfl_id', 'left');
        $this->db->join('tbl_staff_members TSMIT', 'IRM.smember_id = TSMIT.smember_id', 'left');
        $this->db->join('tbl_profile TPRFLIT', 'IRM.smember_id = TPRFLIT.tprfl_id', 'left');
        $this->db->join('tbl_staff_members TSMI', 'IRM.irm_issued_by = TSMI.smember_id');
        $this->db->join('tbl_profile TPRFLI', 'TSMI.smember_id = TPRFLI.tprfl_id');
        $this->db->where("(IRM.irm_return_date = '' OR IRM.irm_return_date IS NULL)");
        $this->db->where("(SM.sm_uid LIKE '%" . $uidOrSrno . "%' OR SM.sm_sr_no LIKE '%" . $uidOrSrno . "%')");
        $this->db->where("SV.svtm_id", $svtm_id);
        if ($pHead != "") {
            $this->db->where("PM.iphm_id", $pHead);
        }
        if ($invoice != "") {
            $this->db->where("PM.pm_id", $invoice);
        }
        if ($dept != "") {
            $this->db->where("IRM.dept_id", $dept);
        }
        if ($subDept != "") {
            $this->db->where("IRM.sub_dept_id", $subDept);
        }
        if ($invCategory != "") {
            $this->db->where("PM.icm_id", $invCategory);
        }
        if ($loc != "") {
            $this->db->where("LM.lm_id", $loc);
        }
        if ($stockItem != "") {
            $this->db->where("PDM.pdm_id", $stockItem);
        }
        if ($emp != "") {
            $this->db->where("IRM.smember_id", $emp);
        }
        /* Dates Combination */
        if ($startDate != '' && $endDate != '') {
            $this->db->where("IRM.irm_issue_date >= '" . $startDate . "' && IRM.irm_issue_date <= '" . $endDate . "'");
        } else if ($startDate == '' && $endDate != '') {
            $this->db->where("IRM.irm_issue_date <= '" . $endDate . "'");
        } else if ($startDate != '' && $endDate == '') {
            $this->db->where("IRM.irm_issue_date >= '" . $startDate . "'");
        }
        /* Dates Combination */
        return $this->db->get();
    }

}