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

class saleInvoiceTmp_model extends CI_Model{
	
	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_byID($tname,$id){
		$this->db->where('ID', $id);
		$query = $this->db->delete($tname); 
		if($query === TRUE){
			return TRUE;
		} else {
			return FALSE;
		}
	}
	
	public function update_byID($tname,$id, $data){
		$this->db->where('ID', $id);
		$query = $this->db->update($tname, $data); 
		if($query === TRUE){
			return TRUE;
		} else {
			return FALSE;
		}
	}
	
	public function update_byFieldName($fName, $fName, $data, $tname){
		$this->db->where($fName, $fValue);
		$query = $this->db->update($tname, $data); 
		if($query === TRUE){
			return TRUE;
		} else {
			return FALSE;
		}
	}
	
        public function sqlStatementBuilder($tname,$fvalue){
            $sql ="";
            switch ($tname) {

                // SALE INVOICE
                case "comp_sales_invoice":
                     $sql="SELECT comp_sales_invoice.*, comp_sales_invoice.ID as InvID,tbl_location.address as location, comp_address.*,
                                comp_sales_agent.agentName,comp_employee.employeeName,master_code.name as shippingmethod,
                                customer.customerName, comp_project.project_name, terms.termName, terms.termDescription, _ref_currency.fldcurr_code As currencyWord
                           FROM (((((((comp_sales_invoice
                                 left outer join comp_project on comp_sales_invoice.projectID=comp_project.ID)
                                 left outer join terms on comp_sales_invoice.termsID = terms.ID)
                                 LEFT OUTER JOIN tbl_location on comp_sales_invoice.locationID=tbl_location.fldid )
                                 LEFT OUTER JOIN comp_address ON comp_address.ID=comp_sales_invoice.shipToID)
                                 LEFT OUTER JOIN comp_employee ON comp_employee.ID=comp_sales_invoice.employeeID)
                                 LEFT OUTER JOIN master_code ON master_code.ID=comp_sales_invoice.shippingMethodID)
                                 LEFT OUTER JOIN comp_sales_agent ON comp_sales_agent.ID=comp_sales_invoice.agentID),
                                 customer LEFT OUTER JOIN _ref_currency ON _ref_currency.fldid = customer.currencyID
                            WHERE customer.ID = comp_sales_invoice.customerId And  comp_sales_invoice.updateAble = TRUE AND comp_sales_invoice.ID =  ".$fvalue;
                break;

                case "comp_sales_invoice_detail":
                  $sql="SELECT comp_sales_invoice_detail.*, tax_master.code, item.itemCode,item.name, item.ID AS itemID, item.description
                        FROM (comp_sales_invoice_detail LEFT OUTER JOIN tax_master ON tax_master.ID = comp_sales_invoice_detail.taxID)
                        LEFT JOIN item ON comp_sales_invoice_detail.itemID=item.ID
                        WHERE invoiceID =".$fvalue;
                break;

                case "comp_sales_invoice_charges":
                  $sql="SELECT  comp_sales_invoice_charges.*, comp_sales_invoice_charges.ID as myid, tax_master.ID as taxId,  
                            tax_master.code, tax_master.taxPercentage,
                            comp_chart_of_acct.* 
                        FROM (comp_sales_invoice_charges LEFT OUTER JOIN tax_master ON comp_sales_invoice_charges.taxID = tax_master.ID )
                              LEFT OUTER JOIN  comp_chart_of_acct ON comp_sales_invoice_charges.accountNoID = comp_chart_of_acct.ID 
                        WHERE comp_sales_invoice_charges.invoiceID =  ".$fvalue;
                break;


                   //  DEBIT NOTE
                case "comp_sales_debitnote" :
                    $sql="SELECT comp_sales_debitnote.*,  customer.customerName, comp_project.project_name, 
                                 currency.currencyWord, CONCAT(comp_sales_invoice.formNo, '[', lpad(comp_sales_invoice.ID,8,'0'), ']')as IformNo,  comp_sales_invoice.customerPoNo 
                          FROM (comp_sales_debitnote left outer join comp_project on comp_sales_debitnote.projectID=comp_project.ID) 
                                left outer join comp_sales_invoice ON comp_sales_invoice.ID = comp_sales_debitnote.salesInvoiceID, currency , customer
                          WHERE comp_sales_debitnote.updateAble = TRUE AND customer.ID=comp_sales_debitnote.customerID
                                 AND currency.ID=customer.currencyID AND comp_sales_debitnote.ID = ".$fvalue;
                break;

                case "comp_sales_debitnote_detail":
                    $sql='SELECT comp_sales_debitnote_detail.* , tax_master.code, tax_master.taxPercentage, item.itemCode, comp_sales_debitnote_detail.itemname as name,
                            comp_sales_debitnote_detail.itemdescription as description, comp_sales_debitnote_detail.id AS myid
                          FROM (comp_sales_debitnote_detail LEFT OUTER JOIN tax_master ON tax_master.ID = comp_sales_debitnote_detail.taxID)
                            LEFT JOIN item   ON comp_sales_debitnote_detail.itemID=item.ID
                         WHERE debitnoteID ='.$fvalue;
                break;
            
                // credit note 
                 case "comp_sales_creditnote" :
                    $sql="SELECT comp_sales_creditnote.*,  customer.customerName, comp_project.project_name, 
                                 currency.currencyWord, CONCAT(comp_sales_invoice.formNo, '[', lpad(comp_sales_invoice.ID,8,'0'), ']')as IformNo,  comp_sales_invoice.customerPoNo 
                          FROM (comp_sales_creditnote left outer join comp_project on comp_sales_creditnote.projectID=comp_project.ID) 
                                left outer join comp_sales_invoice ON comp_sales_invoice.ID = comp_sales_creditnote.salesInvoiceID, currency , customer
                          WHERE comp_sales_creditnote.updateAble = TRUE AND customer.ID=comp_sales_creditnote.customerID
                                 AND currency.ID=customer.currencyID AND comp_sales_creditnote.ID = ".$fvalue;
                break;

                case "comp_sales_creditnote_detail":
                    $sql='SELECT comp_sales_creditnote_detail.* , tax_master.code, tax_master.taxPercentage, item.itemCode, comp_sales_creditnote_detail.itemname as name,
                            comp_sales_creditnote_detail.itemdescription as description, comp_sales_creditnote_detail.id AS myid
                          FROM (comp_sales_creditnote_detail LEFT OUTER JOIN tax_master ON tax_master.ID = comp_sales_creditnote_detail.taxID)
                            LEFT JOIN item   ON comp_sales_creditnote_detail.itemID=item.ID
                         WHERE creditnoteID ='.$fvalue;
                break;
                
                
            }
            return $sql;
        }
        
        public function getDataByTname ($tname, $fvalue){
            
		$sql = $this->sqlStatementBuilder($tname,$fvalue);
                
		$query = $this->db->query($sql);

		if($query->num_rows() >0){
			return $query->result();
		}else{
			return FALSE;
		}
	}
	
	
	public function getJsonDataByTname(){
		$sql ="";
		switch ($tname) {
    		case "":
			break;
		}
		
		$query = $this->db->query($sql);

		if($query->num_rows() >0){
			return json_encode(array("status"=>"success", "recordSet"=>$query));
		}
		else{
			print json_encode(array("status"=>"false", "message"=>$query,));
		}
	}
        
        
        public function getCompanyInvoiceListByCust($customerID){
             $sql="SELECT CONCAT(formNo, '[', lpad(ID,8,'0'), ']') as formNO, ID as id, customerPoNo from comp_sales_invoice
              WHERE customerID=".$customerID;
            $query = $this->db->query($sql);

            if($query->num_rows() >0){
                echo json_encode(array("status"=>"success", "recordSet"=>$query->result()));
            }
            else{
                echo json_encode(array("status"=>"false", "message"=>$query->result()));
            }
        }









        // for copy paste. to be remove later
    /**
     * 
     <?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class saleInvoice_model extends CI_Model{
	
	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_byID($id,$tname){
		$this->db->where('ID', $id);
		$query = $this->db->delete($tname); 
		if($query === TRUE){
			return TRUE;
		} else {
			return FALSE;
		}
	}
	
	public function update_byID($id, $data, $tname){
		$this->db->where('ID', $id);
		$query = $this->db->update($tname, $data); 
		if($query === TRUE){
			return TRUE;
		} else {
			return FALSE;
		}
	}
	
	public function update_byFieldName($fName, $fName, $data, $tname){
		$this->db->where($fName, $fValue);
		$query = $this->db->update($tname, $data); 
		if($query === TRUE){
			return TRUE;
		} else {
			return FALSE;
		}
	}
	
	
	public function getDataByTname ($tname){
		$sql ="";
		switch ($tname) {
    		case "":
			break;
		}
		
		$query = $this->db->query($sql);

		if($query->num_rows() >0){
			return $query->result();
		}else{
			return FALSE;
		}
	}
	
	public function getJsonDataByTname(){
		$sql ="";
		switch ($tname) {
    		case "":
			break;
		}
		
		$query = $this->db->query($sql);

		if($query->num_rows() >0){
			return json_encode(array("status"=>"success", "recordSet"=>$query));
		}
		else{
			print json_encode(array("status"=>"false", "message"=>$query,));
		}
	}
	
	
	
	
} 
    
     
     */    
     /******************************************
      * 
      *  - Sayem
      *  start
      * 
      */
        
	//Gather sales invoice list
	public function getSalesInvoice(){
		$query = $this->db->select('*')
						->from('comp_sales_invoice')
						->where(array('companyID'=>element('compID', $this->session->userdata('logged_in'))))
						->get();
		
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return false;
		}
	}
	
	//Gather customer wise sale invoices
	public function getInvoice($custID){
		$query = $this->db->select("CONCAT(si.formNo, '[', lpad(si.ID,8,'0'), ']')as formNo",false)
						->from('comp_sales_invoice si')
						->where(array('companyID'=>element('compID', $this->session->userdata('logged_in')),'customerID'=>$custID))
						->get();
		//["321000100-A12 Wooden Chair", "331000100-A23 Plastic Chair", "331000200-A24 Plastic Chair", "331000300-A25 Plastic Chair"]
		if($query->num_rows() >= 1){
			$str = "[";
			$i = 0;
			foreach($query->result() as $row){
				if(!$i)	$str .= '"'.$row->formNo.'"';
				else	$str .= ',"'.$row->formNo.'"';
				$i++;
			}
			$str .= "]";
			return $str;
		}else{
			return "";
		}
	}
	
	//Gathering invoice detail from sales invoice table
	public function getInvoiceByID($invID){
		$query = $this->db->select("si.*,DATE_FORMAT(si.salesInvoiceDate,'%d-%m-%Y') as salesInvoiceDate,t.termDescription,CONCAT(a.line1,a.line2,a.line3) as shipAddress",false)
							->from('comp_sales_invoice si')
							->join('terms t','t.ID = si.termsID','left')
							->join('comp_address a','a.ID = si.shipToID','left')
							->where(array('si.ID'=>$invID))
							->get();
		if($query->num_rows() >= 1){
			$data["info"] = $query->result();
			$query = $this->db->select("siDet.*,i.name,i.description")
							->from('comp_sales_invoice_detail siDet')
							->join('item i','i.ID=siDet.itemID')
							->where(array('invoiceID'=>$invID))
							->get();
			$data['invDet'] = $query->result();
			$query = $this->db->select("siCrg.*,CONCAT(ca.acctCode,' | ',ca.acctName) as accountNoID",false)
							->from('comp_sales_invoice_charges siCrg')
							->join('comp_chart_of_acct ca','ca.ID=siCrg.accountNoID','left')
							->where(array('invoiceID'=>$invID))
							->get();
			$data['otherCharge'] = $query->result();
			return $data;
		}else{
			return false;
		}
	}
	/******************************************
	 *
	*  - Sayem
	*  end
	*
	*/
        
}
