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

class SalesOrder extends CI_Model{
	
	public function SalesOrder_all(){
		$this->db->select('a.*');
                $this->db->select('concat(a.formNo,"[",lpad(a.serialNo,8,"0"),"]") as formNo', false);
		$this->db->select('b.customerName');
		$this->db->select('c.fldcurr_code');
		$this->db->where('a.companyID', element('compID', $this->session->userdata('logged_in')));
		$this->db->where('a.status', 1);
		$this->db->order_by('a.companyID');
		$this->db->from('comp_sales_order a');
		$this->db->join('customer b', 'b.ID = a.customerID');	
		$this->db->join('_ref_currency c', 'c.fldid = a.currencyID');	
		$query = $this->db->get();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return FALSE;
		}
	}
	public function SalesOrder_byID($ID){
		$this->db->select('a.*');
                $this->db->select('concat(a.formNo,"[",lpad(a.serialNo,8,"0"),"]") as formNo', false);
		$this->db->select('b.customerName');
		$this->db->select('c.fldcurr_code');
		$this->db->where('a.companyID', element('compID', $this->session->userdata('logged_in')));
		$this->db->where('a.ID',$ID);
		$this->db->order_by('a.companyID');
		$this->db->from('comp_sales_order a');
		$this->db->join('customer b', 'b.ID = a.customerID');	
		$this->db->join('_ref_currency c', 'c.fldid = a.currencyID');	
		$query = $this->db->get();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return FALSE;
		}
	}
	public function SalesOrder_all_date($startDate, $endDate){
		$this->db->select('a.*,DATE_FORMAT(a.salesOrderDate,"%d-%m-%Y") as salesOrderDate',false);
                $this->db->select('concat(a.formNo,"[",lpad(a.serialNo,8,"0"),"]") as formNo', false);
		$this->db->select('b.customerName');
		$this->db->select('b.phoneNumber1');
		$this->db->select('c.fldcurr_code');
		$this->db->where('a.companyID', element('compID', $this->session->userdata('logged_in')));
		$this->db->where('a.status', 1);
		$this->db->where('a.salesOrderDate >=', $startDate);
		$this->db->where('a.salesOrderDate <=', $endDate);
		$this->db->order_by('a.companyID');
		$this->db->from('comp_sales_order a');
		$this->db->join('customer b', 'b.ID = a.customerID');	
		$this->db->join('_ref_currency c', 'c.fldid = a.currencyID');	
		$query = $this->db->get();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return FALSE;
		}
	}
	public function SalesOrder_all_date1($startDate, $endDate){
		$this->db->select('a.*');
                $this->db->select('concat(a.formNo,"[",lpad(a.serialNo,8,"0"),"]") as formNo', false);
		$this->db->select('b.customerName');
		$this->db->select('b.phoneNumber1');
		$this->db->select('c.fldcurr_code');
		$this->db->where('a.companyID', element('compID', $this->session->userdata('logged_in')));
		$this->db->where('a.status', 1);
		$this->db->where('a.salesOrderDate >=', $startDate);
		$this->db->where('a.salesOrderDate <=', $endDate);
		$this->db->order_by('a.companyID');
		$this->db->from('comp_sales_order a');
		$this->db->join('customer b', 'b.ID = a.customerID');	
		$this->db->join('_ref_currency c', 'c.fldid = a.currencyID');	
		$query = $this->db->get();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return FALSE;
		}
	}
	public function SalesOrder_all_date_byCustomer($startDate, $endDate){
		$this->db->select('a.customerName');
		$this->db->select('a.attention');
		$this->db->select('a.phoneNumber1');
		$this->db->select('c.fldname');
		$this->db->select('d.fldcountry');
		$this->db->select('sum(b.totalAmount * exchangeRate) as customerTotal');
		$this->db->where('a.companyID', element('compID', $this->session->userdata('logged_in')));
		$this->db->order_by('a.customerName');
		$this->db->group_by('a.customerName');
		$this->db->from('customer a');
		$this->db->join('comp_sales_order b', 'b.customerID = a.ID AND b.salesOrderDate >= "'.$startDate.'" AND b.salesOrderDate <= "'.$endDate.'"');	
		$this->db->join('_ref_state c', 'c.fldid = a.customerStateID');	
		$this->db->join('_ref_country d', 'd.fldid = a.customerCountryID');	
//		$this->db->join('comp_sales_order b', 'b.customerID = a.ID AND salesOrderDate >='.$startDate.' AND salesOrderDate >='.$endDate);	
		$query = $this->db->get();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return FALSE;
		}
	}
	public function SalesOrder_all_date_byItem($startDate, $endDate){
		$this->db->select('a.itemCode');
		$this->db->select('a.name');
		$this->db->select('a.description');
		$this->db->select('sum(b.quantityOrder) as qtyOrdered');
		$this->db->select('sum(b.quantityShip) as qtyShipped');
		$this->db->select('sum(b.amount * c.exchangeRate) as itemTotal');
		$this->db->where('a.companyID', element('compID', $this->session->userdata('logged_in')));
		$this->db->order_by('a.itemCode');
		$this->db->group_by('a.itemCode');
		$this->db->from('item a');
		$this->db->join('comp_sales_detail b', 'b.itemID = a.ID');	
		$this->db->join('comp_sales_order c', 'c.ID = b.salesID AND c.salesOrderDate >= "'.$startDate.'" AND c.salesOrderDate <= "'.$endDate.'"');	
		$query = $this->db->get();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return FALSE;
		}
	}
	
	public function SalesOrder_all_outstanding_byItem(){
		$this->db->select('a.itemCode');
		$this->db->select('a.name');
		$this->db->select('a.description');
		$this->db->select('sum(b.quantityOrder) as qtyOrdered');
		$this->db->select('sum(b.quantityShip) as qtyShipped');
		$this->db->select('sum(b.quantityOrder - b.quantityShip) as qtyOut');
		$this->db->select('sum(b.amount * c.exchangeRate) as itemTotal');
		$this->db->where('a.companyID', element('compID', $this->session->userdata('logged_in')));
		$this->db->order_by('a.itemCode');
		$this->db->group_by('a.itemCode');
		$this->db->from('item a');
		$this->db->join('comp_sales_detail b', 'b.itemID = a.ID AND (b.quantityOrder - b.quantityShip) > 0');	
		$this->db->join('comp_sales_order c', 'c.ID = b.salesID');	
		$query = $this->db->get();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return FALSE;
		}
	}
	public function SalesOrder_all_byItem(){
		$this->db->select('a.itemCode');
		$this->db->select('a.description');
		$this->db->select('b.unitPrice');
		$this->db->select('b.quantityOrder');
		$this->db->select('sum(b.amount) as totalAmount');
		$this->db->select('b.requiredDate');
		$this->db->where('companyID', element('compID', $this->session->userdata('logged_in')));
		$this->db->order_by('itemCode');
		$this->db->group_by('itemCode');
		$this->db->from('item a');
		$this->db->join('comp_sales_detail b', 'a.ID = b.itemID');
		$query = $this->db->get();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return FALSE;
		}
	}
	
	public function SalesOrder_all_customerID($customerID){
		$this->db->select('a.*');
                $this->db->select('concat(a.formNo,"[",lpad(a.serialNo,8,"0"),"]") as formNo', false);
		$this->db->select('b.customerName');
		$this->db->where('a.companyID', element('compID', $this->session->userdata('logged_in')));
		$this->db->where('a.status', 1);
		$this->db->where('a.customerID', $customerID);
		$this->db->order_by('a.companyID');
		$this->db->from('comp_sales_order a');
		$this->db->join('customer b', 'b.ID = a.customerID');	
		$query = $this->db->get();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return FALSE;
		}
	}
	
	public function SalesOrder_detail_byID($id) {
		/*$sql =' SELECT a.* , b.customerName, c.employeename as salesPerson 
FROM comp_sales_order a,
customer b, comp_employee c
where a.customerID = b.id
and a.salesPerson = c.id
and a.id='.$id;*/
		$this->db->select('a.*');
                $this->db->select('concat(a.formNo,"[",lpad(a.serialNo,8,"0"),"]") as formNo', false);
		$this->db->select('b.customerName');
		$this->db->select('c.employeename as salesPerson');
		$this->db->select('d.currencyCode');
		$this->db->select('d.exchangeRate');
		$this->db->select('e.deliveryOrderDate');
		$this->db->select('f.city');
		$this->db->select('f.address');
		$this->db->from('comp_sales_order a');
		$this->db->from('customer b');
		$this->db->from('comp_employee c');
		$this->db->from('currency d');
		$this->db->from('comp_delivery_order e');
		$this->db->from('tbl_location f');
		$this->db->where('a.companyID', element('compID', $this->session->userdata('logged_in')));
		$this->db->where('a.customerID = b.id');
		$this->db->where('a.salesPerson = c.id');
		$this->db->where('a.currencyID = d.id');
		$this->db->where('a.companyID = e.id');
		$this->db->where('a.companyID = f.fldid');
		$this->db->where('a.id', $id);
		$query = $this->db->get();
		//$query = $this->db->query($sql);
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return FALSE;
		}
	}
	
	public function SalesOrder_ID($ID){
		$this->db->select('a.*,CONCAT(a.OrderNo," [",lpad(a.serialNo,8,0),"]") as formNo',false);
		$this->db->select('b.customerName,b.*');
		$this->db->where('a.companyID', element('compID', $this->session->userdata('logged_in')));
		$this->db->where('a.status', 1);
		$this->db->where('a.ID', $ID);
		$this->db->order_by('a.companyID');
		$this->db->from('comp_sales_order a');
		$this->db->join('customer b', 'b.ID = a.customerID');	
		$query = $this->db->get();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return FALSE;
		}
	}
	
	public function SalesOrder_Insert(){
		$newData = array(
			'companyID' => element('compID', $this->session->userdata('logged_in')),
			'customerID' => $this->input->post('customerID'),
			'OrderNo' => $this->input->post('OrderNo'),
			'currencyID' => $this->input->post('currencyID'),
			'salesOrderDate' => date("Y-m-d", strtotime($this->input->post('effdate'))),
			'exchangeRate' => $this->input->post('exchangeRate'),
			'salesPerson' => $this->input->post('salesPerson'),
			'deliveryToID' => $this->input->post('deliveryToID'),
			'totalAmount' => $this->input->post('totalAmount'),
			'memo' => $this->input->post('memo'),
			'status' => 1,
			'createdBy' => element('role', $this->session->userdata('logged_in'))
		);
		$query = $this->db->insert('comp_sales_order', $newData);
		if($query){
			return $this->db->insert_id();
		}else{
			return FALSE;
		}
	}
	public function SalesOrder_Update($ID, $data){
		$this->db->where('ID', $ID);
		$query = $this->db->update('comp_sales_order', $data); 
		if($query === TRUE){
			return $this->db->last_query();
		} else {
			return FALSE;
		}
	}
// Sales order details 
	public function SalesOrderDetails_all_ID($ID){
		$this->db->select('a.*');
		$this->db->select('DATE_FORMAT(a.requiredDate,"%d-%m-%Y") as requiredDate',false);
		$this->db->select('b.itemCode,CONCAT("[",b.itemCode,"]",name) as itemCodeName,b.description',false);
		$this->db->select('b.description');
		$this->db->select('c.code');
		$this->db->where('a.salesID', $ID);
		$this->db->order_by('a.itemID');
		$this->db->from('comp_sales_detail a');
		$this->db->join('item b', 'b.ID = a.itemID');
		$this->db->join('_sys_um c', 'c.fldid = a.unitmeasure');	
		$query = $this->db->get();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return FALSE;
		}
	}
	public function SalesOrderDetails_Insert($salesID){
		$newData = array(
			'salesID' => $salesID,
			'itemID' => $this->input->post('itemID'),
			'amount' => $this->input->post('amount'),
			'unitmeasure' => $this->input->post('unitmeasure'),
			'unitPrice' => $this->input->post('unitPrice'),
			'quantityOrder' => $this->input->post('ordQty'),
			'description' => $this->input->post('description'),
			'requiredDate' => date("Y-m-d", strtotime($this->input->post('requiredDate'))),
			'createdBy' => element('role', $this->session->userdata('logged_in'))
		);
		$query = $this->db->insert('comp_sales_detail', $newData);
		if($query){
			return $this->db->insert_id();
		}else{
			return FALSE;
		}
	}
	public function SalesOrderDetails_Delete($ID){
		$this->db->where('ID', $ID);
		$query = $this->db->delete('comp_sales_detail'); 
		if($query === TRUE){
			return TRUE;
		} else {
			return FALSE;
		}
	}
	

// Sequence number
	public function seqNumber(){
		$this->db->where('companyID', element('compID', $this->session->userdata('logged_in')));
		$this->db->where('formID', 48);
		$this->db->from('comp_form_control');
		$seqFormat = $this->db->get();
		if($seqFormat->num_rows() >= 1){
			$temp = $seqFormat->result();
			
		}else{
			
		}
	}

}

/* End of file SalesOrder.php */
/* Location: ./application/models/SalesOrder.php */
/* Created By : Yahaya Abdollah */
