<?php

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

class Purchaseinvoice_model extends CI_Model {
    public function __construct() {
        parent::__construct();
    }
    // GET MATERIALREQUEST LISTS FOR MANAGE
    function getpis() {
        ////Fetch data
        $this->db->select("PO.*, S.NAME AS SUPPLIER, E.NAME AS CREATED, E2.NAME AS UPDATED");
        //$this->db->select("PO.*, S.NAME AS SUPPLIER, E.NAME AS CREATED, E2.NAME AS UPDATED, PL.*,P.CODE, P.NAME, ((PL.QUANTITY * PL.UNITPRICE)+PL.TAXAMOUNT-PL.DISCOUNT) AS LINETOTAL");
        $this->db->join('SUPPLIERS S','S.ID=PO.SUPPLIERID','LEFT');
        $this->db->join('PEOPLE E','E.ID=PO.CREATEDBY','LEFT');
        $this->db->join('PEOPLE E2','E2.ID=PO.UPDATEDBY','LEFT');
        // $this->db->join('PURCHASEINVOICELINES PL','PL.PIID=PO.ID','LEFT');
        // $this->db->join("PRODUCTS P","P.ID=PL.PRODUCT");
        // $this->db->where("PL.PIID", 1);
        $rows = $this->db->get("PURCHASEINVOICE PO")->result_array();
        return $rows;
    }

    public function getpilines($id) {
        $table_name = "PURCHASEINVOICELINES PL";
//        $this->db->select("PL.*,P.CODE, P.NAME, ((PL.QUANTITY * PL.UNITPRICE)+PL.TAXAMOUNT-PL.DISCOUNT) AS LINETOTAL")->from($table_name);
        $this->db->select("PL.*,P.CODE, P.NAME, (((PL.QUANTITY * PL.UNITPRICE)+(T.RATE*PL.UNITPRICE))-PL.DISCOUNT) AS LINETOTAL")->from($table_name);
        //$this->db->order_by("P.PRODUCT","ASC");
        $this->db->join("PRODUCTS P",
                "P.ID=PL.PRODUCT");
        $this->db->join("TAXES T",
                "P.TAXCAT=T.CATEGORY",
                'left');
        $this->db->where("PL.PIID",
                $id);
        $rows = $this->db->get()->result_array();
        return $rows;
    }

    function add($form_data) {
        $this->db->insert('PURCHASEINVOICE',
                $form_data);
        if ($this->db->affected_rows() == '1') {
            $this->updategrandtotal($this->db->insert_id());
            return TRUE;
        }
        return FALSE;
    }
    
    function getproductsautocomplete($term) {
        $this->db->select('*');
        $this->db->from('PRODUCTS AS P');
        $where = "P.NAME LIKE '". $term. "%' ";
        $this->db->where($where);
        return $this->db->get()->result();

 }

    function addline($form_data) {
        $this->db->insert('PURCHASEINVOICELINES',
                $form_data);
        if ($this->db->affected_rows() == '1') {
            $this->updategrandtotal($form_data['PIID']);
            return TRUE;
        }
        return FALSE;
    }

    function update($id,
            $form_data) {
        $this->db->where('ID',
                $id);
        $this->db->set('REVISION',
                'REVISION+1',
                FALSE);
        $this->db->update('PURCHASEINVOICE',
                $form_data);
        if ($this->db->affected_rows() == '1') {
            $this->updategrandtotal($id);
            return TRUE;
        } else {
            return FALSE;
        }
    }

    function updatepistatus($id,
            $status) {
        $this->db->where('ID',
                $id);
        $this->db->set('STATUS',
                $status);
        $this->db->update('PURCHASEINVOICE');
    }

    function updatePICreatedValues($id='', $approvedby='') {
        $this->db->where('ID', $id);
        $this->db->set('APPROVEDDATE', date("Y-m-d H:i"));
        $this->db->set('APPROVEDBY', $approvedby);
        $this->db->update('PURCHASEINVOICE');
    }
    
    function updatepisuppliercredit($id='', $curdebt='', $curdate='') {
        if ($curdate){
            $this->db->simple_query("UPDATE SUPPLIERS SET CURDEBT=IF(CURDEBT IS NULL, " . $curdebt . ", CURDEBT+" . $curdebt . "), CURDATE='" . $curdate . "' WHERE ID='" . $id . "';");
        } else {
            $this->db->simple_query("UPDATE SUPPLIERS SET CURDEBT=IF(CURDEBT IS NULL, " . $curdebt . ", CURDEBT+" . $curdebt . ")WHERE ID='" . $id . "';");
        }
    }

    function submitpi($id='') {
        $this->db->where('ID',
                $id);
        $this->db->set('STATUS',
                '2');
        $this->db->update('PURCHASEINVOICE');

        if ($this->db->affected_rows() == '1') {

            $query1 = $this->db->simple_query('INSERT INTO STOCKDIARY SELECT UUID(), PI.DELIVERYDATE, 1, 0, PIL.PRODUCT, NULL, PIL.QUANTITY, PIL.UNITPRICE, PI.SUPPLIERID 
FROM PURCHASEINVOICE PI JOIN PURCHASEINVOICELINES PIL ON PI.ID=PIL.PIID WHERE PI.ID=' . $id . ';');
            if ($this->db->affected_rows() == '1') {
                $query2 = $this->db->simple_query('INSERT INTO STOCKCURRENT SELECT 0, PIL.PRODUCT, NULL, 0 
FROM PURCHASEINVOICELINES PIL LEFT JOIN STOCKCURRENT SC ON SC.PRODUCT=PIL.PRODUCT AND SC.LOCATION=0 
WHERE (SC.PRODUCT IS NULL OR PIL.PRODUCT<>SC.PRODUCT) AND PIL.PIID=' . $id . ';');
                if ($this->db->affected_rows() == '1') {
                    $query3 = $this->db->simple_query('UPDATE STOCKCURRENT SC JOIN PURCHASEINVOICELINES PIL ON PIL.PRODUCT=SC.PRODUCT SET SC.UNITS=SC.UNITS+PIL.QUANTITY
WHERE SC.ATTRIBUTESETINSTANCE_ID IS NULL AND PIL.PIID=' . $id . ';');
                }

                if ($query3) {
                    $query4 = $this->db->simple_query('UPDATE SUPPLIERS S JOIN PURCHASEINVOICE PI ON PI.SUPPLIERID=S.ID SET S.CURDEBT=S.CURDEBT+PI.TOTALAMOUNT, S.CURDATE=PI.DELIVERYDATE WHERE PI.ID' . $id . ';');
                }
            }
        } else {
            return FALSE;
        }
    }

    function updateline($id, $form_data) {
        $this->db->where('ID', $id);
        $this->db->update('PURCHASEINVOICELINES', $form_data);
        if ($this->db->affected_rows() == '1') {
            $this->updategrandtotal($form_data['PIID']);
            return TRUE;
        }
        return FALSE;
    }

    function delete($id) {
        $this->db->delete('PURCHASEINVOICELINES',
                array(
            'PIID' => $id));
        $this->db->delete('PURCHASEINVOICE',
                array(
            'ID' => $id));
        return true;
    }

    function deleteline($id) {
        $this->db->select('PIID');
        $this->db->where('ID',
                $id);
        $q1 = $this->db->get('PURCHASEINVOICELINES');
        $r1 = $q1->row();
        if ($r1) {
            $this->db->delete('PURCHASEINVOICELINES',
                    array(
                'ID' => $id));
            $this->updategrandtotal($r1->QUOTEID);
        }
        return true;
    }

    function getpibyid($id) {
        $sql = "select (COALESCE(FirstSet.TOTAL,0)+COALESCE(ThirdSet.TAXAMOUNT,0)+COALESCE(SecondSet.FREIGHTAMOUNT,0)+COALESCE(SecondSet.DISCOUNT,0)+COALESCE(SecondSet.ADJUSTMENT,0)) AS TOTALAMOUNT,
    FirstSet.PLID,
    SecondSet.*
from (
    SELECT P.*,S.NAME as name,S.TAXID,S.ADDRESS,S.REGION,S.CITY,S.PHONE,S.PHONE2,S.EMAIL FROM PURCHASEINVOICE P LEFT JOIN SUPPLIERS S ON P.SUPPLIERID=S.ID
) as SecondSet
LEFT JOIN(
    SELECT SUM((COALESCE(PL.QUANTITY,0) * COALESCE(PL.UNITPRICE,0))+COALESCE(PL.DISCOUNT,0)) AS TOTAL,PL.PIID AS PLID FROM PURCHASEINVOICELINES PL GROUP BY PL.PIID
) as FirstSet
ON FirstSet.PLID=SecondSet.ID
LEFT JOIN(
    SELECT SUM(TAXAMOUNT) AS TAXAMOUNT,PIID AS TPIID FROM PURCHASEINVOICETAXES GROUP BY PIID
) as ThirdSet
ON FirstSet.PLID=ThirdSet.TPIID";
        if ($id) $sql .= " WHERE SecondSet.ID='" . $id . "'";
        $query = $this->db->query($sql);
        return $query->row();
    }

    //ADDED BY SHAMEER ON 18-7-2011. USED FOR PRINTING
    public function getpilinesbyid($id) {
        $sql = "SELECT PL.*,P.CODE, P.NAME,(T.RATE*PL.UNITPRICE*PL.QUANTITY) as TAXAMOUNT FROM PURCHASEINVOICELINES PL JOIN 
 PRODUCTS P ON PL.PRODUCT = P.ID LEFT JOIN TAXES T ON T.ID=PL.TAXID";
        if ($id) $sql .= " WHERE PL.PIID='" . $id . "'";
        $query = $this->db->query($sql);
        return $query->result();
//        return $this->db->get()->result();
    }

    function updategrandtotal($poid) {
        $gtotal = 0.0;
        $this->db->select('(SUM((QUANTITY * UNITPRICE) + TAXAMOUNT - DISCOUNT)) AS LINETOTAL');
        $this->db->where('PIID',
                $poid);
        $q1 = $this->db->get('PURCHASEINVOICELINES');
        $r1 = $q1->row();
        if ($r1) $gtotal = $r1->LINETOTAL;

        $this->db->select('(FREIGHTAMOUNT + TAXAMOUNT - DISCOUNT + ADJUSTMENT) AS TOTAL');
        $this->db->where('ID',
                $poid);
        $q = $this->db->get('PURCHASEINVOICE');
        $r = $q->row();
        if ($r) $gtotal = $gtotal + $r->TOTAL;
        $this->db->set('TOTALAMOUNT',
                $gtotal);
        $this->db->where('ID',
                $poid);
        $this->db->update('PURCHASEINVOICE');
        if ($this->db->affected_rows() == '1') {
            return TRUE;
        } else {
            return FALSE;
        }
    }

    /*     * ************************** Modified ****************************** */

    // PURCHASE INVOICE LIST
    public function getpurchaseinvoicelist($status = '') {
                $sql    =   "select (COALESCE(FirstSet.TOTAL,0)+COALESCE(ThirdSet.TAXAMOUNT,0)+COALESCE(SecondSet.FREIGHTAMOUNT,0)+COALESCE(SecondSet.DISCOUNT,0)+COALESCE(SecondSet.ADJUSTMENT,0)) AS TOTALAMOUNT,
                COALESCE(FourthSet.PAIDAMOUNT,0) as PAIDAMOUNT,
                IF(((COALESCE(FirstSet.TOTAL,0)+COALESCE(ThirdSet.TAXAMOUNT,0)+COALESCE(SecondSet.FREIGHTAMOUNT,0)+COALESCE(SecondSet.DISCOUNT,0)+COALESCE(SecondSet.ADJUSTMENT,0))+COALESCE(FourthSet.PAIDAMOUNT,0)),3)='-0.000',0,((COALESCE(FirstSet.TOTAL,0)+COALESCE(ThirdSet.TAXAMOUNT,0)+COALESCE(SecondSet.FREIGHTAMOUNT,0)+COALESCE(SecondSet.DISCOUNT,0)+COALESCE(SecondSet.ADJUSTMENT,0))+COALESCE(FourthSet.PAIDAMOUNT,0)) AS BALANCE,
            FirstSet.PLID,
            SecondSet.*
        from 
        (
            SELECT P.*,S.NAME as name FROM PURCHASEINVOICE P LEFT JOIN SUPPLIERS S ON P.SUPPLIERID=S.ID
        ) as SecondSet
        LEFT JOIN
        (
            SELECT SUM((COALESCE(PL.QUANTITY,0) * COALESCE(PL.UNITPRICE,0))+COALESCE(PL.DISCOUNT,0)) AS TOTAL,PL.PIID AS PLID FROM PURCHASEINVOICELINES PL GROUP BY PL.PIID
        ) as FirstSet
        ON FirstSet.PLID=SecondSet.ID
        LEFT JOIN
        (
            SELECT SUM((COALESCE(TAXAMOUNT,0))) AS TAXAMOUNT,PIID AS TPIID FROM PURCHASEINVOICETAXES GROUP BY PIID
        ) as ThirdSet
        ON SecondSet.ID=ThirdSet.TPIID
        LEFT JOIN
        (
            SELECT SUM((COALESCE(T.AMOUNT,0))) AS PAIDAMOUNT,PP.PURCHASEINVOICE_ID FROM TRANSACTIONS T LEFT JOIN PURCHASEINVOICE_PAYMENTS PP ON PP.TRANSACTION_ID=T.ID WHERE (T.PAYMENTHEAD_ID='PURCHASE' OR T.PAYMENTHEAD_ID='PURCHASERETURN') AND T.STATUS=1 GROUP BY PP.PURCHASEINVOICE_ID 
        ) as FourthSet
        ON SecondSet.ID=FourthSet.PURCHASEINVOICE_ID";
        if($status)
            $sql.=" WHERE SecondSet.status=".$status;
        $sql.=" ORDER BY SecondSet.CREATEDDATE DESC";
//        echo $sql;exit;
        $query = $this->db->query($sql);
        return $query->result();
    }
    
    public function getapprovedinvoicebalance($listtype='',$supplierId='',$purchaseflag='') {
        
        $sql    =   "SELECT (COALESCE(FirstSet.TOTAL,0)+COALESCE(ThirdSet.TAXAMOUNT,0)+COALESCE(SecondSet.FREIGHTAMOUNT,0)
            +COALESCE(SecondSet.DISCOUNT,0)+COALESCE(SecondSet.ADJUSTMENT,0)) AS TOTALAMOUNT, COALESCE(FourthSet.PAIDAMOUNT,0) as PAIDAMOUNT, 
            IF(((COALESCE(FirstSet.TOTAL,0)+COALESCE(ThirdSet.TAXAMOUNT,0)+COALESCE(SecondSet.FREIGHTAMOUNT,0)+
            COALESCE(SecondSet.DISCOUNT,0)+COALESCE(SecondSet.ADJUSTMENT,0))+
            COALESCE(FourthSet.PAIDAMOUNT,0))='-0',0,((COALESCE(FirstSet.TOTAL,0)+COALESCE(ThirdSet.TAXAMOUNT,0)+
            COALESCE(SecondSet.FREIGHTAMOUNT,0)+COALESCE(SecondSet.DISCOUNT,0)+COALESCE(SecondSet.ADJUSTMENT,0))+
            COALESCE(FourthSet.PAIDAMOUNT,0))) AS BALANCE,
             FirstSet.PLID, SecondSet.*,CASE SecondSet.status WHEN 1 THEN 'Created' WHEN 2 THEN 'Submitted'
             WHEN -1 THEN 'Returned' WHEN -2 THEN 'Return submitted' ELSE 'Created' END as statustext,TRANSACTIONSTATUS
             from ( SELECT P.*,S.NAME as name,S.ID AS SUPPLIER_ID FROM PURCHASEINVOICE P LEFT JOIN SUPPLIERS S ON P.SUPPLIERID=S.ID )
             as SecondSet LEFT JOIN ( SELECT SUM((COALESCE(PL.QUANTITY,0) * COALESCE(PL.UNITPRICE,0))+COALESCE(PL.DISCOUNT,0)) AS TOTAL,
             PL.PIID AS PLID FROM PURCHASEINVOICELINES PL GROUP BY PL.PIID ) as FirstSet ON FirstSet.PLID=SecondSet.ID
             LEFT JOIN ( SELECT SUM((COALESCE(TAXAMOUNT,0))) AS TAXAMOUNT,PIID AS TPIID FROM PURCHASEINVOICETAXES GROUP BY PIID ) as ThirdSet
             ON SecondSet.ID=ThirdSet.TPIID LEFT JOIN ( SELECT SUM((COALESCE(T.AMOUNT,0))) AS PAIDAMOUNT,PP.PURCHASEINVOICE_ID, T.STATUS,
             T.STATUS AS TRANSACTIONSTATUS FROM TRANSACTIONS T LEFT JOIN PURCHASEINVOICE_PAYMENTS PP ON PP.TRANSACTION_ID=T.ID
             WHERE (T.PAYMENTHEAD_ID='PURCHASE' OR T.PAYMENTHEAD_ID='PURCHASERETURN') GROUP BY PP.PURCHASEINVOICE_ID ) as FourthSet
             ON SecondSet.ID=FourthSet.PURCHASEINVOICE_ID
             HAVING (STATUS='2' || STATUS='-2')";
        
        if($listtype=='NOTPAID'){
            $sql.=   " AND round(BALANCE)!=0";
        }
        if($supplierId){
            $sql.=   " AND SUPPLIER_ID='".$supplierId."'";
        }
        if($purchaseflag=='P'){
            $sql.=   " AND SecondSet.STATUS='2'";
        } else if($purchaseflag=='PR'){
            $sql.=   " AND SecondSet.STATUS='-2'";
        }
        
//        echo $sql;exit;
        $query = $this->db->query($sql);
        return $query->result();
    }
    
    
    // DELETE PURCHASE INVOICE RECORD
    public function deleteinvoicerecmodel($id) {
        $where1 = array( 'PIID' => $id );
        $delPTax = $this->common->deleteall('PURCHASEINVOICETAXES', $where1);
        if($delPTax){
            $delPLine = $this->common->deleteall('PURCHASEINVOICELINES', $where1);
            if ($delPLine) {
                $where = array( 'ID' => $id );
                $this->common->delete('PURCHASEINVOICE', $where);
            }
        }
    }

    public function update_paidamount($id = '',
            $amount = '') {
        $amount = abs($amount);
        $this->db->set('PAIDAMOUNT',
                'PAIDAMOUNT+' . $amount,
                FALSE);
        $this->db->where('ID',
                $id);
        $this->db->update('PURCHASEINVOICETAXES');
    }

    /* ******************* Delete tax record by Purchase Invoice Id **************** */

    public function deletetaxbypinvId($PIID = '') {
        $where = array(
            'PIID' => $PIID);
        $retvalue = $this->common->deleteall('PURCHASEINVOICETAXES', $where);
        if ($retvalue) {
            return true;
        } else {
            return false;
        }
    }

    public function getPurchaseInvoiceListGroupPInv($PIID = '') {
        $sql = "SELECT SUM((PL.QUANTITY*PL.UNITPRICE)*T.RATE) AS TAXAMOUNT,
        SUM(PL.QUANTITY*PL.UNITPRICE) AS BASEAMOUNT
        ,PL.TAXID
         FROM PURCHASEINVOICELINES PL,TAXES T WHERE T.ID=PL.TAXID";
        if ($PIID) $sql .= " AND PL.PIID='" . $PIID . "'";
        $sql .= " GROUP BY TAXID";
        $query = $this->db->query($sql);
        return $query->result();
    }

    public function insertTaxData($taxdata = '') {
        if ($this->common->insert('PURCHASEINVOICETAXES',$taxdata)) {
            return TRUE;
        }else{
            return FALSE;
        }
    }

    public function getPurchaseInvoiceTaxes($PIID=''){
        $sql = "SELECT T.NAME,PT.TAXAMOUNT FROM PURCHASEINVOICETAXES PT,TAXES T WHERE T.ID=PT.TAXID";
        if ($PIID) $sql .= " AND PT.PIID='" . $PIID . "'";
        $query = $this->db->query($sql);
        return $query->result();
    }
    
    function getPurchaseInvoiceDetailsByid($id) {
        $sql = "select (COALESCE(FirstSet.TOTAL,0)+COALESCE(ThirdSet.TAXAMOUNT,0)+COALESCE(SecondSet.FREIGHTAMOUNT,0)+COALESCE(SecondSet.DISCOUNT,0)+COALESCE(SecondSet.ADJUSTMENT,0)) AS TOTALAMOUNT,
	COALESCE(FourthSet.PAIDAMOUNT,0) as PAIDAMOUNT,
	((COALESCE(FirstSet.TOTAL,0)+COALESCE(ThirdSet.TAXAMOUNT,0)+COALESCE(SecondSet.FREIGHTAMOUNT,0)+COALESCE(SecondSet.DISCOUNT,0)+COALESCE(SecondSet.ADJUSTMENT,0))+COALESCE(FourthSet.PAIDAMOUNT,0)) AS BALANCE,
    FirstSet.PLID,
    SecondSet.*,CASE SecondSet.status 
  WHEN 1 THEN 'Created' 
  WHEN 2 THEN 'Submitted'  
  WHEN -1 THEN 'Returned'
  WHEN -2 THEN 'Return submitted'
  ELSE 'Created' 
END as statustext
from 
(
    SELECT P.*,S.NAME as name FROM PURCHASEINVOICE P LEFT JOIN SUPPLIERS S ON P.SUPPLIERID=S.ID
) as SecondSet
LEFT JOIN
(
    SELECT SUM((COALESCE(PL.QUANTITY,0) * COALESCE(PL.UNITPRICE,0))+COALESCE(PL.DISCOUNT,0)) AS TOTAL,PL.PIID AS PLID FROM PURCHASEINVOICELINES PL GROUP BY PL.PIID
) as FirstSet

ON FirstSet.PLID=SecondSet.ID

LEFT JOIN
(
    SELECT SUM((COALESCE(TAXAMOUNT,0))) AS TAXAMOUNT,PIID AS TPIID FROM PURCHASEINVOICETAXES GROUP BY PIID
) as ThirdSet

ON SecondSet.ID=ThirdSet.TPIID

LEFT JOIN
(
    SELECT SUM((COALESCE(T.AMOUNT,0))) AS PAIDAMOUNT,PP.PURCHASEINVOICE_ID FROM TRANSACTIONS T LEFT JOIN PURCHASEINVOICE_PAYMENTS PP ON PP.TRANSACTION_ID=T.ID WHERE T.PAYMENTHEAD_ID='PURCHASE' AND T.STATUS=1 GROUP BY PP.PURCHASEINVOICE_ID 
) as FourthSet

ON SecondSet.ID=FourthSet.PURCHASEINVOICE_ID";
        if ($id) $sql .= " WHERE SecondSet.ID='" . $id . "'";
        $query = $this->db->query($sql);
        return $query->row();
    }
    public function all_purchaseinvoice_count() {
        $sql    =   "select count(*) AS total from (
            SELECT P.*,S.NAME as name FROM PURCHASEINVOICE P LEFT JOIN SUPPLIERS S ON P.SUPPLIERID=S.ID
        ) as SecondSet LEFT JOIN (
            SELECT SUM((COALESCE(PL.QUANTITY,0) * COALESCE(PL.UNITPRICE,0))+COALESCE(PL.DISCOUNT,0)) AS TOTAL,PL.PIID AS PLID FROM PURCHASEINVOICELINES PL GROUP BY PL.PIID
        ) as FirstSet ON FirstSet.PLID=SecondSet.ID
        LEFT JOIN (
            SELECT SUM((COALESCE(TAXAMOUNT,0))) AS TAXAMOUNT,PIID AS TPIID FROM PURCHASEINVOICETAXES GROUP BY PIID
        ) as ThirdSet ON SecondSet.ID=ThirdSet.TPIID
        LEFT JOIN (
            SELECT SUM((COALESCE(T.AMOUNT,0))) AS PAIDAMOUNT,PP.PURCHASEINVOICE_ID FROM TRANSACTIONS T LEFT JOIN PURCHASEINVOICE_PAYMENTS PP ON PP.TRANSACTION_ID=T.ID WHERE (T.PAYMENTHEAD_ID='PURCHASE' OR T.PAYMENTHEAD_ID='PURCHASERETURN') AND T.STATUS=1 GROUP BY PP.PURCHASEINVOICE_ID 
        ) as FourthSet ON SecondSet.ID=FourthSet.PURCHASEINVOICE_ID";
        $query = $this->db->query($sql);
        $numcountrec    =   $query->row();
        return $numcountrec->total;
    }
    
    public function all_purchaseinvoice_list($limit,$start,$dir,$col='date') {
        $sql    =   "select (COALESCE(FirstSet.TOTAL,0)+COALESCE(ThirdSet.TAXAMOUNT,0)+COALESCE(SecondSet.FREIGHTAMOUNT,0)+COALESCE(SecondSet.DISCOUNT,0)+COALESCE(SecondSet.ADJUSTMENT,0)) AS TOTALAMOUNT,
	COALESCE(FourthSet.PAIDAMOUNT,0) as PAIDAMOUNT,
	((COALESCE(FirstSet.TOTAL,0)+COALESCE(ThirdSet.TAXAMOUNT,0)+COALESCE(SecondSet.FREIGHTAMOUNT,0)+COALESCE(SecondSet.DISCOUNT,0)
        +COALESCE(SecondSet.ADJUSTMENT,0))+COALESCE(FourthSet.PAIDAMOUNT,0)) AS BALANCE,
        FirstSet.PLID,
        SecondSet.*,CASE SecondSet.status 
        WHEN 1 THEN 'Created' 
        WHEN 2 THEN 'Submitted'  
        WHEN -1 THEN 'Returned'
        WHEN -2 THEN 'Return submitted'
        ELSE 'Created' 
        END as statustext
        from 
        (
        SELECT P.*,S.NAME as name FROM PURCHASEINVOICE P LEFT JOIN SUPPLIERS S ON P.SUPPLIERID=S.ID
        ) as SecondSet
        LEFT JOIN
        (
        SELECT SUM((COALESCE(PL.QUANTITY,0) * COALESCE(PL.UNITPRICE,0))+COALESCE(PL.DISCOUNT,0)) AS TOTAL,PL.PIID AS PLID FROM PURCHASEINVOICELINES PL GROUP BY PL.PIID
        ) as FirstSet
        ON FirstSet.PLID=SecondSet.ID
        LEFT JOIN
        (
        SELECT SUM((COALESCE(TAXAMOUNT,0))) AS TAXAMOUNT,PIID AS TPIID FROM PURCHASEINVOICETAXES GROUP BY PIID
        ) as ThirdSet
        ON SecondSet.ID=ThirdSet.TPIID
        LEFT JOIN
        (
        SELECT SUM((COALESCE(T.AMOUNT,0))) AS PAIDAMOUNT,PP.PURCHASEINVOICE_ID FROM TRANSACTIONS T LEFT JOIN PURCHASEINVOICE_PAYMENTS PP ON PP.TRANSACTION_ID=T.ID WHERE (T.PAYMENTHEAD_ID='PURCHASE' OR T.PAYMENTHEAD_ID='PURCHASERETURN') AND T.STATUS=1 GROUP BY PP.PURCHASEINVOICE_ID 
        ) as FourthSet
        ON SecondSet.ID=FourthSet.PURCHASEINVOICE_ID";
//        $sql.=" WHERE 1 ".$where;
//        echo $sql;exit;
        if($col)
            $sql.= " ORDER BY {$col} {$dir}";
        if($limit!='' && $start!='')
            $sql.= " Limit {$start}, {$limit}";
        $query = $this->db->query($sql);
        if($query->num_rows()>0) {
            return $query->result(); 
        } else {
            return null;
        }
    }
    
    function purchaseinvoice_search($limit,$start,$search_array,$dir,$col='') {
        if ($col=='date'){
            $col='DELIVERYDATE';
        }
        if($dir=='')
            $dir="DESC";
        $where = ' HAVING 1';
        if(isset($search_array['searchtext'])){
            $where .= ' and (SecondSet.REFERENCE like "%'.$search_array['searchtext'].'%"'
                    . ' ||  SecondSet.name like "%'.$search_array['searchtext'].'%" ||  TOTALAMOUNT like "%'.$search_array['searchtext'].'%"'
                    . ' ||  PAIDAMOUNT like "%'.$search_array['searchtext'].'%" ||  BALANCE like "%'.$search_array['searchtext'].'%")';
        }
        if(isset($search_array['purchaseinvoicetype'])){
            $where .= ' AND SecondSet.STATUS="'.$search_array['purchaseinvoicetype'].'"';
        }
        if(isset($search_array['fromdate'])){
            $where .= ' AND SecondSet.DELIVERYDATE>="'.$search_array['fromdate'].'"';
        }
        if(isset($search_array['todate'])){
            $where .= ' AND SecondSet.DELIVERYDATE<="'.$search_array['todate'].'"';
        }
        
        $sql = "select (COALESCE(FirstSet.TOTAL,0)+COALESCE(ThirdSet.TAXAMOUNT,0)+COALESCE(SecondSet.FREIGHTAMOUNT,0)
            +COALESCE(SecondSet.DISCOUNT,0)+COALESCE(SecondSet.ADJUSTMENT,0)) AS TOTALAMOUNT,
	COALESCE(FourthSet.PAIDAMOUNT,0) as PAIDAMOUNT,
	((COALESCE(FirstSet.TOTAL,0)+COALESCE(ThirdSet.TAXAMOUNT,0)+COALESCE(SecondSet.FREIGHTAMOUNT,0)+COALESCE(SecondSet.DISCOUNT,0)
        +COALESCE(SecondSet.ADJUSTMENT,0))+COALESCE(FourthSet.PAIDAMOUNT,0)) AS BALANCE,
        FirstSet.PLID,
        SecondSet.*,CASE SecondSet.status 
        WHEN 1 THEN 'Created' 
        WHEN 2 THEN 'Submitted'  
        WHEN -1 THEN 'Returned'
        WHEN -2 THEN 'Return submitted'
        ELSE 'Created' 
        END as statustext
        from 
        (
        SELECT P.*,S.NAME as name FROM PURCHASEINVOICE P LEFT JOIN SUPPLIERS S ON P.SUPPLIERID=S.ID
        ) as SecondSet
        LEFT JOIN
        (
        SELECT SUM((COALESCE(PL.QUANTITY,0) * COALESCE(PL.UNITPRICE,0))+COALESCE(PL.DISCOUNT,0)) AS TOTAL,
        PL.PIID AS PLID FROM PURCHASEINVOICELINES PL GROUP BY PL.PIID
        ) as FirstSet
        ON FirstSet.PLID=SecondSet.ID
        LEFT JOIN
        (
        SELECT SUM((COALESCE(TAXAMOUNT,0))) AS TAXAMOUNT,PIID AS TPIID FROM PURCHASEINVOICETAXES GROUP BY PIID
        ) as ThirdSet
        ON SecondSet.ID=ThirdSet.TPIID
        LEFT JOIN
        (
        SELECT SUM((COALESCE(T.AMOUNT,0))) AS PAIDAMOUNT,PP.PURCHASEINVOICE_ID FROM TRANSACTIONS T
        LEFT JOIN PURCHASEINVOICE_PAYMENTS PP ON PP.TRANSACTION_ID=T.ID WHERE (T.PAYMENTHEAD_ID='PURCHASE' OR T.PAYMENTHEAD_ID='PURCHASERETURN')
        AND T.STATUS=1 GROUP BY PP.PURCHASEINVOICE_ID 
        ) as FourthSet
        ON SecondSet.ID=FourthSet.PURCHASEINVOICE_ID".$where;
//        if($col)
            $sql.= " ORDER BY DELIVERYDATE, CREATEDDATE DESC";
        if($limit!='' && $start!='')
            $sql.= " Limit {$start}, {$limit}";
           
//        echo $sql;exit;
            
        $query = $this->db->query($sql);
        if($query->num_rows()>0) {
            return $query->result(); 
        } else {
            return null;
        }
    }
    
    function purchaseinvoice_searchcount($search_array='') {
        $where = ' Where 1';
        if(isset($search_array['searchtext'])){
            $where .= ' and (SecondSet.REFERENCE like "%'.$search_array['searchtext'].'%"'
                    . ' ||  SecondSet.name like "%'.$search_array['searchtext'].'%")';
        }
        if(isset($search_array['purchaseinvoicetype'])){
            $where .= ' AND SecondSet.STATUS="'.$search_array['purchaseinvoicetype'].'"';
        }
        if(isset($search_array['fromdate'])){
            $where .= ' AND SecondSet.DELIVERYDATE>="'.$search_array['fromdate'].'"';
        }
        if(isset($search_array['todate'])){
            $where .= ' AND SecondSet.DELIVERYDATE<="'.$search_array['todate'].'"';
        }
        $sql = "select count(*) as total from (
            SELECT P.*,S.NAME as name FROM PURCHASEINVOICE P LEFT JOIN SUPPLIERS S ON P.SUPPLIERID=S.ID
        ) as SecondSet
        LEFT JOIN (
            SELECT SUM((COALESCE(PL.QUANTITY,0) * COALESCE(PL.UNITPRICE,0))+COALESCE(PL.DISCOUNT,0)) AS TOTAL,
            PL.PIID AS PLID FROM PURCHASEINVOICELINES PL GROUP BY PL.PIID
        ) as FirstSet ON FirstSet.PLID=SecondSet.ID
        LEFT JOIN (
            SELECT SUM((COALESCE(TAXAMOUNT,0))) AS TAXAMOUNT,PIID AS TPIID FROM PURCHASEINVOICETAXES GROUP BY PIID
        ) as ThirdSet
        ON SecondSet.ID=ThirdSet.TPIID
        LEFT JOIN (
            SELECT SUM((COALESCE(T.AMOUNT,0))) AS PAIDAMOUNT,PP.PURCHASEINVOICE_ID FROM TRANSACTIONS T
            LEFT JOIN PURCHASEINVOICE_PAYMENTS PP ON PP.TRANSACTION_ID=T.ID WHERE (T.PAYMENTHEAD_ID='PURCHASE' OR T.PAYMENTHEAD_ID='PURCHASERETURN')
            AND T.STATUS=1 GROUP BY PP.PURCHASEINVOICE_ID 
        ) as FourthSet
        ON SecondSet.ID=FourthSet.PURCHASEINVOICE_ID".$where;
        $query = $this->db->query($sql);
        $resultcnt  =   $query->result();
        return $resultcnt[0]->total;
    }
    
}