<?php

if (!defined('BASEPATH'))
    exit('No direct script access allowed');

class AccountPayable_model extends CI_Model {

    public function getPendingDebit($supplier) {
        $sql = "Select d.ID, CONCAT(d.formNo, '[', lpad(d.serialNo,8,'0'), ']') as formNo, "
                . " date_format(debitNoteDate,'%d-%m-%Y') AS debitNoteDate, d.totalAmount, d.total,"
                . " CONCAT(i.formNo, '[', lpad(i.serialNo,8,'0'), ']') as invNo "
                . " From comp_supplier_invoice i, comp_supplier_debitnote d "
                . " WHERE d.supplierInvoiceID=i.ID AND d.total > 0 and d.updateAble = 0 AND d.supplierID=" . $supplier;
        $query = $this->db->query($sql);

        if ($query->num_rows() >= 1) {
            return $query->result();
        } else {
            return FALSE;
        }
    }

    public function getPendingCredit($supplier) {
        $sql = "Select d.ID, CONCAT(d.formNo, '[', lpad(d.serialNo,8,'0'), ']') as formNo, "
                . " date_format(creditNoteDate,'%d-%m-%Y') AS creditNoteDate, d.totalAmount, d.total,"
                . " CONCAT(i.formNo, '[', lpad(i.serialNo,8,'0'), ']') as invNo "
                . " From comp_supplier_invoice i, comp_supplier_creditnote d "
                . " WHERE d.supplierInvoiceID=i.ID AND d.total > 0 and d.updateAble = 0 AND d.supplierID=" . $supplier;
        $query = $this->db->query($sql);
        if ($query->num_rows() >= 1) {
            return $query->result();
        } else {
            return FALSE;
        }
    }

    public function getPendingHistoricalInvoice($supplier) {
        $sql = "Select h.ID, CONCAT(h.formNo, '[', lpad(h.serialNo,8,'0'), ']') as formNo, h.totalPayable, "
                . " date_format(invoiceDate,'%d-%m-%Y') AS invoiceDate, h.totalAmount, h.total,"
                . " h.supplierInvoiceNo as invNo "
                . " From  comp_historical_supplier_invoice h "
                . " WHERE h.is_delete = 0 and h.totalPayable > 0 AND h.supplierID=" . $supplier;
        $query = $this->db->query($sql);

        if ($query->num_rows() >= 1) {
            return $query->result();
        } else {
            return FALSE;
        }
    }

    public function updateMaster($master, $field, $idValue) {
        $selectField = implode(",", $field);
        $myView = "view_total_" . $master;
        $sql = 'Select ' . $selectField . ' From ' . $myView . ' WHERE ID = ' . $idValue . ' LIMIT 1';
        $query = $this->db->query($sql);
        $result = $query->result();

        $data = array();
        foreach ($result as $row) {
            $array = get_object_vars($row);
            foreach ($field AS $f) {
                $data [$f] = $array [$f];
            }
        }
        // var_dump($data);
        $this->db->where('ID', $idValue);
        $query = $this->db->update($master, $data);
        if ($query === TRUE) {
            return TRUE;
        } else {
            return FALSE;
        }
    }

    public function update_total_note($master, $idValue) {
        $myView = "view_total_" . $master;
        $sql = 'Select totalTaxAmount, totalAmount From ' . $myView . ' WHERE ID = ' . $idValue . ' LIMIT 1';

        $query = $this->db->query($sql);
        $result = $query->result();
        $data = array();

        if ($query->num_rows()) {
            $data["totalTaxAmount"] = $result[0]->totalTaxAmount;
            $data["totalAmount"] = $result[0]->totalAmount;
            $data["total"] = $result[0]->totalAmount;
            $this->db->where('ID', $idValue);
            $query = $this->db->update($master, $data);
        }

        if ($query === TRUE) {
            return TRUE;
        } else {
            return FALSE;
        }
    }

    public function setTotalPayable($master, $id) {
        $query = $this->db->query("Update " . $master . " set totalPayable = totalAmount  where id =" . $id);
    }

    public function getCompanyInvoiceListBySup($supID) {
        $sql = "SELECT CONCAT(formNo, '[', lpad(serialNo,8,'0'), ']') as formNO, ID as id, supplierInvoiceNo ";
        $sql .= ' from comp_supplier_invoice ';
        $sql .= ' WHERE supplierID=' . $supID;
        $sql .= ' And companyID=' . element('compID', $this->session->userdata('logged_in'));
        $sql .= ' and updateAble = 0';
        $sql .= ' and totalPayable > 0';
        $query = $this->db->query($sql);
        // echo $this->db->last_query();
        if ($query->num_rows() > 0) {
            echo json_encode(array("status" => "success", "recordSet" => $query->result()));
        } else {
            echo json_encode(array("status" => "false", "message" => $query->result()));
        }
    }

    public function loadSupplierDefaultValue2($id) {
        $this->db->select('b.fldcurr_code as currencyWord');
        $this->db->select('c.currencyID as compCurrency');
        $this->db->select('a.currencyID as suppCurrency');
        $this->db->select('d.startDate');
        $this->db->select('d.endDate');
        $this->db->select('ifnull(d.rateConv,"") as exchangeRate', false);
        $this->db->where('a.ID', $id);
        $this->db->from('supplier a');
        $this->db->join('_ref_currency b', 'b.fldid = a.currencyID', 'left outer');
        $this->db->join('company c', 'c.ID = a.companyID', 'left outer');
        $this->db->join('comp_currency_conv d', 'd.currencyFrom = c.currencyID and d.currencyTo = a.currencyID and d.startDate <="' . date("Y-m-d", strtotime($this->input->post('effdate'))) . '" and d.endDate >="' . date("Y-m-d", strtotime($this->input->post('effdate'))) . '"', 'left');
        $query = $this->db->get();
        if ($query->num_rows() >= 1) {
            return $query->result();
        } else {
            return FALSE;
        }
    }

    public function loadSupplierDefaultValue($id) {
        $this->db->select('b.fldcurr_code as currencyWord');
        //$this->db->select('com.currencyID as compCurrency,a.currencyID as suppCurrency,c.startDate,c.endDate');
        $this->db->select('ifnull(c.rateConv,"") as exchangeRate', false);
        $this->db->select('a.currencyID, a.termsID');
        $this->db->where('a.ID', $id);
        $this->db->from('supplier a');
        $this->db->join('_ref_currency b', 'b.fldid = a.currencyID', 'left outer');
        $this->db->join('company com', 'com.ID = a.companyID', 'left');
        $this->db->join('comp_currency_conv c', 'c.currencyFrom = com.currencyID and c.currencyTo = a.currencyID and c.startDate <="' . date("Y-m-d", strtotime($this->input->post('effdate'))) . '" and c.endDate >="' . date("Y-m-d", strtotime($this->input->post('effdate'))) . '"  and c.companyID = a.companyID', 'left');
        $query = $this->db->get();
//		$sql='SELECT cu.fldcurr_code as currencyWord, conv.rateConv as exchangeRate, su.currencyID FROM supplier su, _ref_currency cu, comp_currency_conv conv WHERE   conv.currencyTo = cu.fldid And su.ID = '.$id.' AND su.currencyID = cu.fldid ';
        //echo $this->db->last_query();		
        if ($query->num_rows() >= 1) {
            return $query->result();
        } else {
            return FALSE;
        }
    }

    //Gather supplier invoice information
    public function getSupplierInvoiceAll($dateForm = '', $dateTo = '') {//
        $query = $this->db->select('si.ID,CONCAT(si.formNo,"[",lpad(si.serialNo,8,0),"]") as formNo,si.supplierInvoiceNo,s.supplierName,si.totalAmount,DATE_FORMAT(si.invoiceDate,"%d-%m-%Y") as date', false)
                ->from('comp_supplier_invoice as si')
                ->join('supplier as s', 's.ID = si.supplierID', 'left')
                ->where(array('si.companyID' => element('compID', $this->session->userdata('logged_in')), 'si.updateAble' => 1, 'si.is_delete' => 0, 'si.invoiceDate >=' => $dateForm, 'si.invoiceDate <=' => $dateTo))
                ->get();
        if ($query->num_rows() >= 1) {
            return $query->result();
        } else {
            return false;
        }
    }

    public function insert($tname, $newData) {
        $sql = $this->db->insert_string($tname, $newData);
        $query = $this->db->query($sql);
        if ($query === TRUE) {
            return $this->db->insert_id();
        } else {
            //		$last_query = $this->db->last_query();
            //		return $last_query;
            return 0;
        }
    }

    public function delete($id, $tname) {
        $this->db->where('ID', $id);
        $query = $this->db->delete($tname);
        if ($query === TRUE) {
            return TRUE;
        } else {
            return FALSE;
        }
    }

    public function getCrditNotDetailById($id) {
        $companyID = element('compID', $this->session->userdata('logged_in'));

        $sql = 'SELECT DISTINCT comp_supplier_invoice_detail.quantity as qytInvoice,
                                comp_supplier_creditnote_detail.*, tax_master.code,
                                  tax_master.taxPercentage, _sys_um.code as unitOfMeasure, 
                                item.itemCode,item.name as name, item.itemcode, item.id as itemid, 
                                comp_supplier_creditnote_detail.itemdescription as description
		  FROM comp_supplier_creditnote, (((comp_supplier_creditnote_detail 
                        LEFT OUTER JOIN tax_master ON tax_master.ID = comp_supplier_creditnote_detail.taxID)
			LEFT JOIN item ON comp_supplier_creditnote_detail.itemID=item.ID ) 
                        left outer join _sys_um on _sys_um.fldid = comp_supplier_creditnote_detail.umID) 
                        left outer join comp_supplier_invoice_detail on comp_supplier_invoice_detail.itemID = comp_supplier_creditnote_detail.itemID
                 where  comp_supplier_creditnote_detail.invoiceDetailID = comp_supplier_invoice_detail.ID 
                        and comp_supplier_creditnote.ID = comp_supplier_creditnote_detail.creditnoteID AND creditnoteID =' . $id . " and comp_supplier_creditnote.companyID = $companyID ";
        ;
        $query = $this->db->query($sql);

        if ($query->num_rows() >= 1) {
            return $query->result();
        } else {
            return FALSE;
        }
    }

// to be combined with get_detail_byid using tname as paramater

    public function get_DebitNoteDetail_byID($id) {
        $companyID = element('compID', $this->session->userdata('logged_in'));
        $sql = 'SELECT DISTINCT _sys_um.code as unitOfMeasure, comp_supplier_debitnote_detail.*, comp_supplier_invoice_detail.quantity as qytInvoice,
                      tax_master.code, tax_master.taxPercentage,item.name, item.itemCode,item.ID AS itemid, comp_supplier_debitnote_detail.itemdescription as description
			FROM comp_supplier_debitnote, (((comp_supplier_debitnote_detail LEFT OUTER JOIN tax_master ON tax_master.ID = comp_supplier_debitnote_detail.taxID)
			LEFT JOIN item ON comp_supplier_debitnote_detail.itemID=item.ID ) 
                        left outer join _sys_um on _sys_um.fldid = comp_supplier_debitnote_detail.umID)
                        left outer join comp_supplier_invoice_detail on comp_supplier_invoice_detail.ID = comp_supplier_debitnote_detail.invoiceDetailID
                        
                      WHERE comp_supplier_debitnote.supplierInvoiceID = comp_supplier_invoice_detail.invoiceID 
                        and comp_supplier_debitnote.id = comp_supplier_debitnote_detail.debitnoteID AND debitnoteID =' . $id . " and comp_supplier_debitnote.companyID = $companyID ";
        $query = $this->db->query($sql);

        if ($query->num_rows() >= 1) {
            return $query->result();
        } else {
            return FALSE;
        }
    }

    public function get_Detail_byID($id) {
        $sql = 'SELECT comp_supplier_invoice_detail.*,_sys_um.code as unitOfMeasure, comp_supplier_invoice_detail.receiveID AS receiveDetailID, tax_master.code, item.itemCode, item.name,comp_supplier_invoice_detail.itemdescription as description
			FROM ((comp_supplier_invoice_detail LEFT OUTER JOIN tax_master ON tax_master.ID = comp_supplier_invoice_detail.taxID)
			LEFT JOIN item
			ON comp_supplier_invoice_detail.itemID=item.ID ) left outer join _sys_um on _sys_um.fldid = comp_supplier_invoice_detail.umID
			where invoiceID =' . $id;
        $query = $this->db->query($sql);
        if ($query->num_rows() >= 1) {
            return $query->result();
        } else {
            return FALSE;
        }
    }

    public function get_byID($tname, $id) {
        $sql = "";
        switch ($tname) {
            case "comp_supplier_invoice":
                $sql = 'SELECT comp_employee.employeeName,master_code.name as shippingmethod, comp_supplier_invoice.*, comp_supplier_invoice.formNo as frmNo, comp_supplier_invoice.ID as InvID,tbl_location.*, comp_address.*,';
                $sql.=' supplier.supplierName, comp_project.project_name, terms.termName, terms.termDescription, _ref_currency.fldcurr_code as currencyWord, CONCAT(comp_supplier_invoice.formNo, "[", lpad(comp_supplier_invoice.serialNo,8,"0"), "]") as formNo, supplier.commenceDate, supplier.ceaseDate ';
                $sql.=' FROM ((((((comp_supplier_invoice';
                $sql.=' left outer join comp_project on comp_supplier_invoice.projectID=comp_project.ID)';
                $sql.=' left outer join terms on comp_supplier_invoice.termsID = terms.ID)';
                $sql.=' LEFT OUTER JOIN master_code ON master_code.ID=comp_supplier_invoice.shippingMethodID)';
                $sql.=' LEFT OUTER JOIN comp_employee ON comp_employee.ID=comp_supplier_invoice.purchaserID)';
                $sql.=' LEFT OUTER JOIN tbl_location on comp_supplier_invoice.locationID=tbl_location.fldid )';
                $sql.=' LEFT OUTER JOIN comp_address ON comp_address.ID=comp_supplier_invoice.shipToID),';
                $sql.=' supplier LEFT OUTER JOIN _ref_currency ON _ref_currency.fldid=supplier.currencyID';
                $sql.=' where comp_supplier_invoice.updateAble = TRUE AND ';
                $sql.='  supplier.ID=comp_supplier_invoice.supplierID ';
                $sql.=' AND  comp_supplier_invoice.ID =  ' . $id;
                break;
            case "comp_supplier_debitnote" :
                $sql = 'SELECT comp_supplier_debitnote.*,comp_supplier_debitnote.formNo as frmNo, CONCAT(comp_supplier_debitnote.formNo, "[", lpad(comp_supplier_debitnote.serialNo,8,"0"), "]")as formNo,  supplier.supplierName, supplier.line1, supplier.line2, supplier.line3, supplier.city, supplier.supplierPostCode, comp_project.project_name,  _ref_currency.fldcurr_code, CONCAT(comp_supplier_invoice.formNo, "[", lpad(comp_supplier_invoice.serialNo,8,"0"), "]")as IformNo,  comp_supplier_invoice.supplierInvoiceNo ';
                $sql .= ' FROM (comp_supplier_debitnote left outer join comp_project on comp_supplier_debitnote.projectID=comp_project.ID) left outer join comp_supplier_invoice ON comp_supplier_invoice.ID = comp_supplier_debitnote.supplierInvoiceID, _ref_currency , supplier ';
                $sql .= ' where comp_supplier_debitnote.updateAble = TRUE ';
                $sql .= ' AND supplier.ID=comp_supplier_debitnote.supplierID ';
                $sql .= ' AND _ref_currency.fldid=supplier.currencyID AND comp_supplier_debitnote.ID =  ' . $id;
                break;
            /* case "comp_supplier_creditnote_detail" :
              $sql='SELECT comp_supplier_creditnote.*,  '
              . 'supplier.supplierName, comp_project.project_name, '
              . 'currency.currencyWord FROM (comp_supplier_creditnote '
              . 'left outer join comp_project on'
              . ' comp_supplier_creditnote.projectID=comp_project.ID) LEFT OUTER JOIN  comp_supplier_invoice'
              . 'ON  comp_supplier_invoice.ID = , currency , '
              . 'supplier where comp_supplier_creditnote.updateAble ='
              . ' TRUE AND supplier.ID=comp_supplier_creditnote.supplierID'
              . ' AND currency.ID=supplier.currencyID AND comp_supplier_creditnote.ID =  '.$id;
              break; */
            case "comp_supplier_creditnote" :
                $sql = 'SELECT comp_supplier_creditnote.*, comp_supplier_creditnote.formNo as frmNo, CONCAT(comp_supplier_creditnote.formNo, "[", lpad(comp_supplier_creditnote.serialNo,8,"0"), "]")as formNo, supplier.supplierName, supplier.line1, supplier.line2, supplier.line3, supplier.city, supplier.supplierPostCode, comp_project.project_name,  _ref_currency.fldcurr_code, CONCAT(comp_supplier_invoice.formNo, "[", lpad(comp_supplier_invoice.serialNo,8,"0"), "]")as IformNo, comp_supplier_invoice.supplierInvoiceNo ';
                $sql .= 'FROM (comp_supplier_creditnote left outer join comp_project on comp_supplier_creditnote.projectID=comp_project.ID) left outer join comp_supplier_invoice ON comp_supplier_invoice.ID = comp_supplier_creditnote.supplierInvoiceID,';
                $sql .= '  _ref_currency , supplier where comp_supplier_creditnote.updateAble = TRUE ';
                $sql .= 'AND supplier.ID=comp_supplier_creditnote.supplierID ';
                $sql .= 'AND  _ref_currency.fldid=supplier.currencyID AND comp_supplier_creditnote.ID =  ' . $id;
                break;
        }
        $query = $this->db->query($sql);

        if ($query->num_rows() > 0) {
            return $query->result();
        } else {
            return FALSE;
        }
    }

    public function update_byTname($tname, $id, $data) {
        $this->db->where('ID', $id);
        $query = $this->db->update($tname, $data);
        if ($query === TRUE) {
            return TRUE;
        } else {
            return FALSE;
        }
    }

    //get credit note by id. abaikan updateable
    public function get_rpt_byID($tname, $id) {
        $sql = "";
        switch ($tname) {
            case "comp_supplier_invoice":
                $sql = 'SELECT comp_supplier_invoice.*, CONCAT(comp_supplier_invoice.formNo, "[", lpad(comp_supplier_invoice.serialNo,8,"0"), "]")as formNo, comp_supplier_invoice.ID as InvID,tbl_location.*, comp_address.*,';
                $sql.=' supplier.supplierName, comp_project.project_name, terms.termName, terms.termDescription, currency.currencyWord ';
                $sql.=' FROM ((((comp_supplier_invoice';
                $sql.=' left outer join comp_project on comp_supplier_invoice.projectID=comp_project.ID)';
                $sql.=' left outer join terms on comp_supplier_invoice.termsID = terms.ID)';
                $sql.=' LEFT OUTER JOIN tbl_location on comp_supplier_invoice.locationID=tbl_location.fldid )';
                $sql.=' LEFT OUTER JOIN comp_address ON comp_address.ID=comp_supplier_invoice.shipToID),';
                $sql.=' currency , supplier';
                $sql.=' where ';
                $sql.=' supplier.ID=comp_supplier_invoice.supplierID ';
                $sql.=' AND currency.ID=supplier.currencyID';
                $sql.=' AND comp_supplier_invoice.ID =  ' . $id;
                break;
            case "comp_supplier_debitnote" :
                $sql = 'SELECT comp_supplier_debitnote.*, comp_supplier_debitnote.formNo as frmNo,  CONCAT(comp_supplier_debitnote.formNo, "[", lpad(comp_supplier_debitnote.serialNo,8,"0"), "]")as formNo, supplier.supplierName, supplier.line1, supplier.line2,'
                        . ' supplier.line3, supplier.city, supplier.supplierPostCode, comp_project.project_name, '
                        . 'currency.currencyWord FROM (comp_supplier_debitnote '
                        . 'left outer join comp_project on comp_supplier_debitnote.projectID=comp_project.ID),'
                        . ' currency , supplier where supplier.ID=comp_supplier_debitnote.supplierID AND '
                        . 'currency.ID=supplier.currencyID AND comp_supplier_debitnote.ID =  ' . $id;
                break;
            /* case "comp_supplier_creditnote_detail" :
              $sql='SELECT comp_supplier_creditnote.*,  supplier.supplierName, comp_project.project_name, currency.currencyWord FROM (comp_supplier_creditnote left outer join comp_project on comp_supplier_creditnote.projectID=comp_project.ID), currency , supplier where comp_supplier_creditnote.updateAble = TRUE AND supplier.ID=comp_supplier_creditnote.supplierID AND currency.ID=supplier.currencyID AND comp_supplier_creditnote.ID =  '.$id;
              break; */
            case "comp_supplier_creditnote" :
                $sql = 'SELECT comp_supplier_creditnote.*, supplier.supplierName, supplier.line1, supplier.line2, CONCAT(comp_supplier_creditnote.formNo, "[", lpad(comp_supplier_creditnote.serialNo,8,"0"), "]")as formNo, '
                        . 'supplier.line3, supplier.city, supplier.supplierPostCode, comp_project.project_name, currency.currencyWord ';
                $sql .= 'FROM (comp_supplier_creditnote left outer join comp_project on comp_supplier_creditnote.projectID=comp_project.ID),';
                $sql .= ' currency , supplier where  ';
                $sql .= ' supplier.ID=comp_supplier_creditnote.supplierID ';
                $sql .= 'AND currency.ID=supplier.currencyID AND comp_supplier_creditnote.ID =  ' . $id;
                break;
        }
        $query = $this->db->query($sql);

        if ($query->num_rows() > 0) {
            return $query->result();
        } else {
            return FALSE;
        }
    }

    public function get_bySupplierID($id, $companyID = 0) {
        /* if ($companyID > 0)

          $this->db->select('comp_supplier_invoice.*, comp_supplier_invoice.ID as suppInvoiceID, CONCAT(comp_supplier_invoice.formNo, "[", lpad(comp_supplier_invoice.serialNo,8,"0"), "]")as formNo');

          //$this->db->select('');
          //$this->db->select("CONCAT(comp_supplier_invoice.formNo, '[', lpad(comp_supplier_invoice.ID,8,'0'), ']') as formNo'");
          $this->db->where('supplierID', $id);
          $this->db->where('companyID',$companyID);
          $this->db->where('totalPayable >', 0);
          $query = $this->db->get('comp_supplier_invoice');

         * 
         * 
         */

        /* to avoid duplicate data using left outer join. akmal 20141223
         *  $sql = "Select comp_supplier_invoice.*, comp_supplier_invoice.totalPayable + ifnull(b.totalAmount,0) - ifnull(c.totalAmount,0) as totalPayable,  comp_supplier_invoice.ID as suppInvoiceID, CONCAT(comp_supplier_invoice.formNo, '[', lpad(comp_supplier_invoice.serialNo,8,'0'), ']') as formNo "
          . " from comp_supplier_invoice  left outer join comp_supplier_creditnote b on comp_supplier_invoice.ID = b.supplierInvoiceID "
          . " left outer join comp_supplier_debitnote c on comp_supplier_invoice.ID = c.supplierInvoiceID "
          . " where comp_supplier_invoice.supplierID =$id AND comp_supplier_invoice.companyID = $companyID "
          . " AND comp_supplier_invoice.totalPayable + ifnull(b.totalAmount,0) - ifnull(c.totalAmount,0)  > 0";
         */
        $sql = "Select comp_supplier_invoice.*, comp_supplier_invoice.totalPayable,";
//akmal 20151106. supplier credit note will not effect total invoice payable. it will appear in the payment screen. 
        //supplier debitnote will reduce invoice payable and not appear in the payment
//        $sql .= "(select ifnull(sum(totalAmount),0) from comp_supplier_creditnote where updateAble = 0 and  supplierInvoiceID = comp_supplier_invoice.ID)";
//       $sql .= "- (select ifnull(sum(totalAmount),0) from comp_supplier_debitnote where updateAble = 0 and supplierInvoiceID = comp_supplier_invoice.ID) as totalPayable,";
        $sql .= " comp_supplier_invoice.ID as suppInvoiceID, CONCAT(comp_supplier_invoice.formNo, '[', lpad(comp_supplier_invoice.serialNo,8,'0'), ']') as formNo ";
        $sql .= " from comp_supplier_invoice  ";
        $sql .= " where comp_supplier_invoice.supplierID =$id AND comp_supplier_invoice.companyID = $companyID AND  comp_supplier_invoice.is_delete = 0 ";
        $sql .= " AND comp_supplier_invoice.totalPayable + ";
        $sql .= "(select ifnull(sum(totalAmount),0) from comp_supplier_creditnote where updateAble = 0 and supplierInvoiceID = comp_supplier_invoice.ID)";
//        $sql .= "- (select ifnull(sum(totalAmount),0) from comp_supplier_debitnote where updateAble = 0 and supplierInvoiceID = comp_supplier_invoice.ID)";
        $sql .= " > 0";
//		echo $sql; 
        $query = $this->db->query($sql);
        if ($query->num_rows() > 0) {
            return $query->result();
        } else {
            return false;
        }
    }

    function get_unpaid_debit($id) {
          $sql = "Select c.ID, CONCAT(c.formNo, '[', lpad(c.serialNo,8,'0'), ']') as formNo, date_format(creditNoteDate,'%d-%m-%Y') AS creditNoteDate, c.totalAmount, c.total,"
                . " CONCAT(i.formNo, '[', lpad(i.serialNo,8,'0'), ']') as invNo "
                . " From comp_sales_invoice i, comp_supplier_debitnote c "
                . " WHERE c.updateAble = 0 and c.supplierInvoiceID=i.ID AND c.total > 0 AND c.supplierID=" . $id;
        $query = $this->db->query($sql);
        if ($query->num_rows() > 0) {
            return $query->result();
        } else {
            return false;
        }
    }

    function get_unpaid_credit($id) {
        $sql = "Select c.ID, CONCAT(c.formNo, '[', lpad(c.serialNo,8,'0'), ']') as formNo, date_format(creditNoteDate,'%d-%m-%Y') AS creditNoteDate, c.totalAmount, c.total,"
                . " CONCAT(i.formNo, '[', lpad(i.serialNo,8,'0'), ']') as invNo "
                . " From comp_sales_invoice i, comp_supplier_creditnote c "
                . " WHERE c.updateAble = 0 and c.supplierInvoiceID=i.ID AND c.total > 0 AND c.supplierID=" . $id;
        $query = $this->db->query($sql);
        if ($query->num_rows() > 0) {
            return $query->result();
        } else {
            return false;
        }
    }

    function get_unpaid_invoice($id) {

        $sql = "Select comp_supplier_invoice.*, comp_supplier_invoice.totalPayable, ";
        $sql .= " comp_supplier_invoice.ID as suppInvoiceID, CONCAT(comp_supplier_invoice.formNo, '[', lpad(comp_supplier_invoice.serialNo,8,'0'), ']') as formNo ";
        $sql .= " from comp_supplier_invoice  ";
        $sql .= " where updateAble = 0 and comp_supplier_invoice.supplierID =$id AND  comp_supplier_invoice.updateAble = 0 ";
        $sql .= " AND comp_supplier_invoice.totalPayable > 0";
        $query = $this->db->query($sql);
        if ($query->num_rows() > 0) {
            return $query->result();
        } else {
            return false;
        }
    }

    public function getChargeByid($id) {

        $sql = 'SELECT  comp_supplier_invoice_charges.*, comp_supplier_invoice_charges.ID as myid, tax_master.ID as taxId,  tax_master.code, tax_master.taxPercentage,
                        comp_chart_of_acct.* 
                From ((comp_supplier_invoice_charges, comp_chart_of_acct) 
                    left outer join tax_master on comp_supplier_invoice_charges.taxID = tax_master.ID)
		Where  comp_supplier_invoice_charges.accountNoID = 
		comp_chart_of_acct.ID AND 
		 comp_supplier_invoice_charges.invoiceID =  ' . $id;
        $query = $this->db->query($sql);
       
        if ($query->num_rows() > 0) {
            return $query->result();
        } else {
            return false;
        }
    }

    public function getDebitNoteChargeByid($id) {

        $sql = 'SELECT  comp_supplier_debitnote_charges.*, comp_supplier_debitnote_charges.ID as myid, tax_master.ID as taxId,  tax_master.code, tax_master.taxPercentage,
		comp_chart_of_acct.* From comp_supplier_debitnote_charges, tax_master, comp_chart_of_acct
		Where comp_supplier_debitnote_charges.taxID = tax_master.ID and  comp_supplier_debitnote_charges.accountNoID = 
		comp_chart_of_acct.ID AND 
		 comp_supplier_debitnote_charges.debitNoteId =  ' . $id;
        $query = $this->db->query($sql);
       
        if ($query->num_rows() > 0) {
            return $query->result();
        } else {
            return false;
        }
    }

    public function getCreditNoteChargeByid($id) {

        $sql = 'SELECT  comp_supplier_creditnote_charges.*, comp_supplier_creditnote_charges.ID as myid, tax_master.ID as taxId,  tax_master.code, tax_master.taxPercentage,
		comp_chart_of_acct.* From comp_supplier_creditnote_charges, tax_master, comp_chart_of_acct
		Where comp_supplier_creditnote_charges.taxID = tax_master.ID and  comp_supplier_creditnote_charges.accountNoID = 
		comp_chart_of_acct.ID AND 
		 comp_supplier_creditnote_charges.creditNoteId =  ' . $id;
        $query = $this->db->query($sql);
       
        if ($query->num_rows() > 0) {
            return $query->result();
        } else {
            return false;
        }
    }

    public function checkformNumber_model($t, $fN) {
        $sql = 'SELECT id from ' . $t . ' where formNo = "' . $fN . '"';
        $query = $this->db->query($sql);

        if ($query->num_rows() >= 1) {
            return "yes";
        } else {
            return "no";
        }
    }

    public function deleteCharge($idCharge) {
        $sql = 'Delete from comp_supplier_invoice_charges where ID = ' . $idCharge;
        $query = $this->db->query($sql);
        $return = NULL;

        if ($query->num_rows() >= 1) {
            $return = "true";
        }

        return $return;
    }

    public function deleteCompanyDetail($id) {
        $sql = 'Delete from comp_supplier_invoice_detail where ID = ' . $id;
        $query = $this->db->query($sql);
        $return = NULL;

        if ($query->num_rows() >= 1) {
            $return = "true";
        }

        return $return;
    }

    public function getLasIdInsert($tname) {
        $sql = 'SELECT LAST_INSERT_ID() as myId from ' . $tname;
        $query = $this->db->query($sql);
        $return = NULL;

        if ($query->num_rows() >= 1) {
            foreach ($query->result() as $row)
                $return = $row->myId;
        }

        return $return;
    }

    public function update_charge($id, $data) {
        $this->db->where('ID', $id);
        $query = $this->db->update('comp_supplier_invoice_charges', $data);
        if ($query === TRUE) {
            return TRUE;
        } else {
            return FALSE;
        }
    }

    public function update_byID($id, $data) {
        $this->db->where('ID', $id);
        $query = $this->db->update('comp_supplier_invoice_detail', $data);
        if ($query === TRUE) {
            return TRUE;
        } else {
            return FALSE;
        }
    }

    public function update_by_tname($tname, $id, $data) {
        // echo $tname."<->".$id."<->";
        //print_r($data);
        // exit();
        $this->db->where('ID', $id);
        $query = $this->db->update($tname, $data);
        // echo $this->db->last_query();
        if ($query === TRUE) {
            return TRUE;
        } else {
            return FALSE;
        }
    }

    public function update_debitNoteDetail($id, $data) {
        $this->db->where('ID', $id);
        $query = $this->db->update('comp_supplier_debitnote_detail', $data);
        if ($query === TRUE) {
            return TRUE;
        } else {
            return FALSE;
        }
    }

    public function update_CreditNoteDetail($id, $data) {
        $this->db->where('ID', $id);
        $query = $this->db->update('comp_supplier_creditnote_detail', $data);
        if ($query === TRUE) {
            return TRUE;
        } else {
            return FALSE;
        }
    }

    public function updateInvoice($id, $data) {
        $this->db->where('ID', $id);
        $query = $this->db->update('comp_supplier_invoice', $data);
        if ($query === TRUE) {
            return TRUE;
        } else {
            return FALSE;
        }
    }

// invoice details
    public function purchaseInvoiceDetails_ID($ID) {
        $this->db->where('invoiceID', $ID);
        $this->db->from('view_unpost_ap_invoice');
        $query = $this->db->get();
        if ($query->num_rows() >= 1) {
            return $query->result();
        } else {
            return false;
        }
    }

    public function purchaseInvoice_post($id, $data) {
        $this->db->where('ID', $id);
        $this->db->where('updateAble', 1);
        $query = $this->db->update('comp_supplier_invoice', $data);
        if ($query) {
            $this->db->flush_cache();
            $this->db->where('ID', $id);
            $this->db->from('comp_supplier_invoice');
            $query1 = $this->db->get();
            return $query1->result();
        } else {
            return false;
        }
    }

    public function purchaseInvoiceDetails_invoiceID($journalID, $header, $detailsData, $year, $period) {
        if ($detailsData) {
            $seq = 0;
            $journalAmount = 0;
            foreach ($detailsData as $row):
                if(!$header->exchangeRate)  $header->exchangeRate = 1;

                if ($row->amountExcludedtax != 0) {
                    $seq++;
                    $newData = array(
                        'companyID' => element('compID', $this->session->userdata('logged_in')),
                        'journalID' => $journalID,
                        'sequence' => $seq,
                        'description' => $row->itemName,
                        'acctCode' => $row->crAccountID,
                        'supplier' => $row->supplier,
                        'amount_cr' => $row->amountExcludedtax * $header->exchangeRate,
                        'currencyID' => ($header->currencyID == 0) ? 1 : $header->currencyID,
                        'exchange_rate' => $header->exchangeRate,
                        'currency_amount' => $row->amountExcludedtax,
                        'period' => $period,
                        'year' => $year,
                        'line' => $row->lineNo,
                        'formNo' => $row->formNo,
                        'projectID' => $row->projectID,
                        'createdBy' => element('role', $this->session->userdata('logged_in'))
                    );
                    $queryInsert = $this->db->insert('comp_journal_detail', $newData);
                    $seq++;
                    $newData = array(
                        'companyID' => element('compID', $this->session->userdata('logged_in')),
                        'sequence' => $seq,
                        'journalID' => $journalID,
                        'description' => $row->itemName,
                        'acctCode' => $row->drAccountID,
                        'supplier' => $row->supplier,
                        'amount_dr' => $row->amountExcludedtax * $header->exchangeRate,
                        'currencyID' => ($header->currencyID == 0) ? 1 : $header->currencyID,
                        'exchange_rate' => $header->exchangeRate,
                        'currency_amount' => $row->amountExcludedtax,
                        'period' => $period,
                        'year' => $year,
                        'line' => $row->lineNo,
                        'formNo' => $row->formNo,
                        'projectID' => $row->projectID,
                        'createdBy' => element('role', $this->session->userdata('logged_in'))
                    );
                    $queryInsert = $this->db->insert('comp_journal_detail', $newData);
                    $journalAmount = $journalAmount + ($row->amountExcludedtax * $header->exchangeRate);
                }
                if ($row->taxamount != 0) {
                    $seq++;
                    $newData = array(
                        'companyID' => element('compID', $this->session->userdata('logged_in')),
                        'sequence' => $seq,
                        'journalID' => $journalID,
                        'description' => $row->itemName,
                        'acctCode' => $row->drTaxAccountID,
                        'supplier' => $row->supplier,
                        'amount_dr' => $row->taxamount * $header->exchangeRate,
                        'currencyID' => ($header->currencyID == 0) ? 1 : $header->currencyID,
                        'exchange_rate' => $header->exchangeRate,
                        'currency_amount' => $row->taxamount,
                        'period' => $period,
                        'year' => $year,
                        'line' => $row->lineNo,
                        'formNo' => $row->formNo,
                        'projectID' => $row->projectID,
                        'createdBy' => element('role', $this->session->userdata('logged_in'))
                    );
                    $queryInsert = $this->db->insert('comp_journal_detail', $newData);
                    $seq++;
                    $newData = array(
                        'companyID' => element('compID', $this->session->userdata('logged_in')),
                        'sequence' => $seq,
                        'journalID' => $journalID,
                        'description' => $row->itemName,
                        'acctCode' => $row->crTaxAccountID,
                        'supplier' => $row->supplier,
                        'amount_cr' => $row->taxamount * $header->exchangeRate,
                        'currencyID' => ($header->currencyID == 0) ? 1 : $header->currencyID,
                        'exchange_rate' => $header->exchangeRate,
                        'currency_amount' => $row->taxamount,
                        'period' => $period,
                        'year' => $year,
                        'line' => $row->lineNo,
                        'formNo' => $row->formNo,
                        'projectID' => $row->projectID,
                        'createdBy' => element('role', $this->session->userdata('logged_in'))
                    );
                    $queryInsert = $this->db->insert('comp_journal_detail', $newData);
                    $journalAmount = $journalAmount + ($row->taxamount * $header->exchangeRate);
                }
            endforeach;
            if ($journalAmount != 0) {
                $data = array(
                    'total_amount_dr' => $journalAmount,
                    'total_amount_cr' => $journalAmount,
                    'total_amount' => 0
                );
                $this->db->where('companyID', element('compID', $this->session->userdata('logged_in')));
                $this->db->where('journalID', $journalID);
                $queryJournal = $this->db->update('comp_journal', $data);
            }
        } else {
            return false;
        }
    }

    //Auto add receive item 
    public function saveReceiveItemFromDirect($id) {

        $query = $this->db->query("Select a.*, b.*, b.ID as bid from comp_supplier_invoice a, comp_supplier_invoice_detail b "
                . " WHERE a.ID = b.invoiceID AND purchaseInvoiceCodeID = 2 AND a.ID = $id");

        if ($query->num_rows() > 0) {
            $return = $query->result();
            $this->load->model('company/formSetup_Model');

            $filter['companyID'] = element('compID', $this->session->userdata('logged_in'));
            $filter['formID'] = 45; // for GRN  
            $inserReceData = array(
                'companyID' => $return[0]->companyID,
                'createdBy' => $return[0]->createdBy,
                'supplierID' => $return[0]->supplierID,
                'employeeID' => $return[0]->companyID,
                'exchangeRate' => $return[0]->exchangeRate,
                'currencyID' => $return[0]->currencyID,
                'formNo' => $this->formSetup_Model->getFormNo($filter),
                'deliveryDate' => $return[0]->invoiceDate,
                'shippingMethodID' => $return[0]->shippingMethodID,
                'supplierDoNo' => $return[0]->supplierInvoiceNo,
                'locationID' => $return[0]->locationID,
            );

            $reci = $this->insert("comp_receive_item", $inserReceData);
            foreach ($return as $row) {
                $datinsertDetails = array(
                    'receiveID' => $reci,
                    'invoiceID' => $id,
                    'createdBy' => element('role', $this->session->userdata('logged_in')),
                    'itemID' => $row->itemID,
                    'lineNo' => $row->lineNo,
                    'poDetailID' => -1,
                    'description' => $row->itemdescription,
                    'quantityOrder' => $row->quantity,
                    'quantityReceived' => $row->quantity,
                    'balanceItem' => $row->quantity,
                    'unitPrice' => $row->unitPrice,
                    'amount' => $row->unitPrice * $row->quantity
                );
                $recieveID = $this->insert("comp_receive_item_detail", $datinsertDetails);
                $this->update_byTname("comp_supplier_invoice_detail", $row->bid, array("receiveID" => $recieveID));
            }

            return $reci;
        } else {
            return FALSE;
        }
    }

    public function get_xInv() {
        $this->db->select('count(ID) as xInv');
        $this->db->where('updateAble', 1);
        $this->db->where('is_delete', 0);
        $this->db->where('companyID', element('compID', $this->session->userdata('logged_in')));
        $query = $this->db->get('comp_supplier_invoice');
        if ($query->num_rows() > 0) {
            return $query->result();
            ;
        } else {
            return FALSE;
        }
    }

    public function get_xDN() {
        $this->db->select('count(ID) as xDN');
        $this->db->where('updateAble', 1);
        $this->db->where('is_delete', 0);
        $this->db->where('companyID', element('compID', $this->session->userdata('logged_in')));
        $query = $this->db->get('comp_supplier_debitnote');
        if ($query->num_rows() > 0) {
            return $query->result();
            ;
        } else {
            return FALSE;
        }
    }

    public function get_xCN() {
        $this->db->select('count(ID) as xCN');
        $this->db->where('updateAble', 1);
        $this->db->where('is_delete', 0);
        $this->db->where('companyID', element('compID', $this->session->userdata('logged_in')));
        $query = $this->db->get('comp_supplier_creditnote');
        if ($query->num_rows() > 0) {
            return $query->result();
            ;
        } else {
            return FALSE;
        }
    }

    public function get_xPayment() {
        $this->db->select('count(ID) as xPayment');
        $this->db->where('updateAble', 1);
        $this->db->where('is_delete', 0);
        $this->db->where('companyID', element('compID', $this->session->userdata('logged_in')));
        $query = $this->db->get('comp_payment');
        if ($query->num_rows() > 0) {
            return $query->result();
            ;
        } else {
            return FALSE;
        }
    }

    public function get_bilSuppInvPost() {
        $this->db->select('month(invoiceDate) as mnth');
        $this->db->select('count(ID) as bil');
        $this->db->where('updateAble', 1);
        $this->db->where('is_delete', 0);
        $this->db->where('year(invoiceDate)', 'year(now())', false);
        $this->db->where('companyID', element('compID', $this->session->userdata('logged_in')));
        $this->db->group_by('month(invoiceDate)');
        $query = $this->db->get('comp_supplier_invoice');
//		echo $this->db->last_query();
        if ($query->num_rows() > 0) {
            return $query->result();
            ;
        } else {
            return FALSE;
        }
    }

    public function get_bilPaymentPost() {
        $this->db->select('month(paymentDate) as mnth');
        $this->db->select('count(ID) as bil');
        $this->db->where('updateAble', 1);
        $this->db->where('is_delete', 0);
        $this->db->where('year(paymentDate)', 'year(now())', false);
        $this->db->where('companyID', element('compID', $this->session->userdata('logged_in')));
        $this->db->group_by('month(paymentDate)');
        $query = $this->db->get('comp_payment');
//		echo $this->db->last_query();
        if ($query->num_rows() > 0) {
            return $query->result();
            ;
        } else {
            return FALSE;
        }
    }

    public function get_bilOtherPaymentPost() {
        $this->db->select('month(paymentDate) as mnth');
        $this->db->select('count(ID) as bil');
        $this->db->where('updateAble', 1);
        $this->db->where('is_delete', 0);
        $this->db->where('year(paymentDate)', 'year(now())', false);
        $this->db->where('companyID', element('compID', $this->session->userdata('logged_in')));
        $this->db->group_by('month(paymentDate)');
        $query = $this->db->get('comp_payment_others');
//		echo $this->db->last_query();
        if ($query->num_rows() > 0) {
            return $query->result();
            ;
        } else {
            return FALSE;
        }
    }

    public function get_bilPayment() {
        $this->db->select('month(paymentDate) as mnth');
        $this->db->select('count(ID) as bil');
        $this->db->where('updateAble', 0);
        $this->db->where('is_delete', 0);
        $this->db->where('year(paymentDate)', 'year(now())', false);
        $this->db->where('companyID', element('compID', $this->session->userdata('logged_in')));
        $this->db->group_by('month(paymentDate)');
        $query = $this->db->get('comp_payment');
//		echo $this->db->last_query();
        if ($query->num_rows() > 0) {
            return $query->result();
            ;
        } else {
            return FALSE;
        }
    }

    public function get_bilSuppInv() {
        $this->db->select('month(invoiceDate) as mnth');
        $this->db->select('count(ID) as bil');
        $this->db->where('updateAble', 0);
        $this->db->where('is_delete', 0);
        $this->db->where('year(invoiceDate)', 'year(now())', false);
        $this->db->where('companyID', element('compID', $this->session->userdata('logged_in')));
        $this->db->group_by('month(invoiceDate)');
        $query = $this->db->get('comp_supplier_invoice');
//		echo $this->db->last_query();
        if ($query->num_rows() > 0) {
            return $query->result();
            ;
        } else {
            return FALSE;
        }
    }

    public function getSupplierInvoiceItemList($invoiceID) {
        $sql = "Select * from view_suppliercreditnote where invoiceID = $invoiceID";

        $query = $this->db->query($sql);


        if ($query->num_rows() > 0) {
            return $query->result();
        } else {
            return false;
        }
    }

    public function getSupplierInvoiceItemListDebit($invoiceID) {
        $sql = "Select a.*,i.unitOfMeasureID defaultUM,um.code umCode from view_supplierdebitnote a 
					LEFT JOIN `item` i ON i.ID=a.itemID
					LEFT JOIN `_sys_um` um ON um.fldid=i.unitOfMeasureID
					where invoiceID = $invoiceID";
        $query = $this->db->query($sql);


        if ($query->num_rows() > 0) {
            return $query->result();
        } else {
            return false;
        }
    }

}
