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

class saleInvoice_model extends CI_Model{
	
      public function updateMaster($master,$field,$idValue, $t=false){
        $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();
        // var_dump($result);
        $data =array();
         foreach($result as $row){
             $array = get_object_vars( $row);
             foreach ($field AS $f){
                 $data [$f] = $array [$f];
             }
         }
         
         if($t==true){
             $data["totalReceivable"] = $data["totalAmount"];
         }
        $this->db->where('ID', $idValue);
       
        //var_dump($data);
	$query = $this->db->update($master, $data); 
        
        if($query === TRUE){
                return TRUE;
        } 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 {
			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, 
                                _ref_currency.fldcurr_code As 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, _ref_currency , customer
                          WHERE comp_sales_debitnote.updateAble = TRUE AND customer.ID=comp_sales_debitnote.customerID
                                 AND _ref_currency.fldid=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, item.name,item.ID as itemid,
                            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;
            
                case "comp_sales_debitnote_charges":
                     $sql="SELECT  comp_sales_debitnote_charges.*, comp_sales_debitnote_charges.ID as myid, tax_master.ID as taxId,  
                            tax_master.code, tax_master.taxPercentage,
                            comp_chart_of_acct.* 
                        FROM (comp_sales_debitnote_charges LEFT OUTER JOIN tax_master ON comp_sales_debitnote_charges.taxID = tax_master.ID )
                              LEFT OUTER JOIN  comp_chart_of_acct ON comp_sales_debitnote_charges.accountNoID = comp_chart_of_acct.ID 
                        WHERE comp_sales_debitnote_charges.debitNoteID =  ".$fvalue;
                break;
            
                // credit note 
                 case "comp_sales_creditnote" :
                    $sql="SELECT comp_sales_creditnote.*, customer.customerName, comp_project.project_name, 
                                _ref_currency.fldcurr_code As 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, _ref_currency , customer
                          WHERE comp_sales_creditnote.updateAble = TRUE AND customer.ID=comp_sales_creditnote.customerID
                                 AND _ref_currency.fldid=customer.currencyID AND comp_sales_creditnote.ID = ".$fvalue;
                break;
                
                case "comp_sales_creditnote_charges":
                     $sql="SELECT  comp_sales_creditnote_charges.*, comp_sales_creditnote_charges.ID as myid, tax_master.ID as taxId,  
                            tax_master.code, tax_master.taxPercentage,
                            comp_chart_of_acct.* 
                        FROM (comp_sales_creditnote_charges LEFT OUTER JOIN tax_master ON comp_sales_creditnote_charges.taxID = tax_master.ID )
                              LEFT OUTER JOIN  comp_chart_of_acct ON comp_sales_creditnote_charges.accountNoID = comp_chart_of_acct.ID 
                        WHERE comp_sales_creditnote_charges.creditNoteID =  ".$fvalue;
                break;

                case "comp_sales_creditnote_detail":
                    $sql='SELECT comp_sales_creditnote_detail.* , tax_master.code, tax_master.taxPercentage, item.itemCode, item.name, item.name,item.ID as itemid,
                            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 orders
        public function getOrder($custID){
        	$query = $this->db->select("so.OrderNo,so.ID",false)
				        	->from('comp_sales_order so')
				        	->where(array('companyID'=>element('compID', $this->session->userdata('logged_in')),'customerID'=>$custID,'invoiceID'=>0))
				        	->get();
        	 
        	//["321000100-A12 Wooden Chair", "331000100-A23 Plastic Chair", "331000200-A24 Plastic Chair", "331000300-A25 Plastic Chair"]
        	if($query->num_rows() >= 1){
        		$str = "";
        		foreach($query->result() as $row){
        			echo "<option value=".$row->ID.">".$row->OrderNo."</option>";
        		}
        		return $str;
        	}else{
        		return "";
        	}
        }
        
        //Gathering order detail from sales order table
        public function getOrderByID($ordID){
        	$query = $this->db->select("so.*,DATE_FORMAT(so.salesOrderDate,'%d-%m-%Y') as salesOrderDate,CONCAT(a.line1,a.line2,a.line3) as shipAddress",false)
				        	->from('comp_sales_order so')
				        	->join('comp_address a','a.ID = so.deliveryToID','left')
				        	->where(array('so.ID'=>$ordID))
				        	->get();
        	
        	if($query->num_rows() >= 1){
        		$data["info"] = $query->result();
        		$query = $this->db->select("soDet.*,i.ID as itemID,i.name,i.description,i.outputTaxID,t.taxPercentage")
				        		->from('comp_sales_detail soDet')
				        		->join('item i','i.ID=soDet.itemID')
				        		->join('tax_master t','t.ID=i.outputTaxID')
				        		->where(array('salesID'=>$ordID))
				        		->get();
        		$data['ordDet'] = $query->result();
        		return $data;
        	}else{
        		return false;
        	}
        }
        
        //Gathering all order items belongs to particular customer
        public function getAllOrderByID($custID,$date){
        	$query = $this->db->select("doDet.*,CONCAT(do.formNo, '[', lpad(do.ID,8,'0'), ']')as formNo,i.ID as itemID,i.name,i.description,i.outputTaxID,t.taxPercentage",false)
					        	->from('comp_delivery_order do')
					        	->join('comp_delivery_detail doDet','doDet.deliveryID = do.ID','left')
					        	->join('item i','i.ID=doDet.itemID','left')
					        	->join('tax_master t','t.ID=i.outputTaxID','left')
					        	->where(array(
					        			'do.deliveryOrderDate <='=>$date,
					        			'do.companyID'=>element('compID', $this->session->userdata('logged_in')),
					        			'do.customerID'=>$custID,
					        			'doDet.invoiceID'=>'0'
					        			))
					        	->get();
        	
        	if($query->num_rows() >= 1){
        		$data['detail'] = $query->result();
        		$query = $this->db->select("cur.fldcurr_code")
			        		->from('customer c')
			        		->join('_ref_currency cur','cur.fldid = c.currencyID','left')
			        		->where(array('c.companyID'=>element('compID', $this->session->userdata('logged_in')),'c.ID'=>$custID))
			        		->get();
        		$data['info'] = $query->result();
        		return $data;
        	}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;
        	}
        }
        
        //Save Tax infor for individual items
        public function saveTax($data){
        	$taxInfo = explode(',',$data);
        	$count = 0;
        	foreach($taxInfo as $v){
        		$taxDet = explode('-',$v);
        		$query = $this->db->where('ID',$taxDet[0])->update('item',array('outputTaxID'=>$taxDet[1]));
        		$count += $this->db->affected_rows();
        	}
        	
        	if($count >= 1 ){
        		return "Success";
        	}else{
        		return "Error";
        	}
        }
        
        //update sales order table
        public function updateSalOrd($id,$invID){
        	$this->db->where('ID',$id)->update('comp_sales_order',array('invoiceID'=>$invID));
        	if($this->db->affected_rows() >= 1 ){
        		return true;
        	}else{
        		return false;
        	}
        }
        //saving sale invoice detail
        public function updateSaleInvDet($invID,$invDetail){
        	$arr = json_decode($invDetail);
        	foreach($arr as $v){
        		$data = array(
        			"invoiceID"=>$invID,
        			"doID"=>$v->doID,
        			"lineNo"=>$v->lineNo,
        			"itemID"=>$v->itemID,
        			"quantity"=>$v->quantity,
        			"unitPrice"=>$v->unitPrice,
        			"amountExcludedTax"=>$v->amountExcludedTax,
        			"taxID"=>$v->taxID,
        			"taxAmount"=>$v->taxAmount,
        			"amountIncludedTax"=>$v->amountIncludedTax,
        			"createdBy"=>element('role', $this->session->userdata('logged_in'))
        		);
        		$this->db->insert('comp_sales_invoice_detail', $data);
        		
        		//Update Delivery order
        		$this->db->where(array('deliveryID'=>$v->doID,'itemID'=>$v->itemID))->update('comp_delivery_detail',array('invoiceID'=>$invID));
        		echo $this->db->last_query();
        	}
        }
        
        //Check item exist in invoice
        public function itemExist($tName,$where){
        	$query = $this->db->select('*')->from($tName)->where($where)->get();
        	if($query->num_rows() > 0){
        		return true;
        	}else{
        		return false;
        	}
        }
        
        //Inser table information
        public function insertIt($tName,$data){
        	$this->db->insert($tName,$data);
        	if($this->db->affected_rows() >= 1){
        		return true;
        	}else{
        		return false;
        	}
        	
        }
        //Update table information
        public function update($tName,$where,$data){
        	$this->db->where($where)->update($tName,$data);
        	if($this->db->affected_rows() >= 1){
        		return true;
        	}else{
        		return false;
        	}        	
        }
        /******************************************
         *
        *  - Sayem
        *  end
        *
        */       
		public function get_byCustomerID($id,$companyID=0){
		if ($companyID > 0)
		    $this->db->where('companyID',$companyID); 
					$this->db->select('comp_sales_invoice.*');
					$this->db->select('comp_sales_invoice.ID as custInvoiceID');
					$this->db->where('customerID', $id);
					$this->db->where('totalamount >', 0);
					$query = $this->db->get('comp_sales_invoice');
		if($query->num_rows() >0 ){
			return $query->result();
		}else{
			return false;
		}

	}
}
