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

class SalesDeliveryOrder extends CI_Model{
	
	public function SalesDeliveryOrder_all(){
		$this->db->select('a.*');
		$this->db->select('b.customerName');
		$this->db->where('a.companyID', element('compID', $this->session->userdata('logged_in')));
		$this->db->order_by('a.companyID');
		$this->db->from('comp_delivery_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 SalesDeliveryOrder_all_date($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('c.name');
		$this->db->select('d.employeeName');
		$this->db->where('a.companyID', element('compID', $this->session->userdata('logged_in')));
		$this->db->where('a.deliveryOrderDate >=', $startDate);
		$this->db->where('a.deliveryOrderDate <=', $endDate);
		$this->db->order_by('a.companyID');
		$this->db->from('comp_delivery_order a');
		$this->db->join('customer b', 'b.ID = a.customerID');	
		$this->db->join('master_code c', 'c.ID = a.shippingMethodID', 'left outer');	
		$this->db->join('comp_employee d', 'd.ID = a.employeeID', 'left outer');	
		$query = $this->db->get();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return FALSE;
		}
	}
	
	public function SalesByCustomer_all_date($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.attention');
		$this->db->select('b.phoneNumber1');
		$this->db->select('sum(c.amount * a.exchangeRate) as customerTotal');
		$this->db->where('a.companyID', element('compID', $this->session->userdata('logged_in')));
		$this->db->where('a.deliveryOrderDate >=', $startDate);
		$this->db->where('a.deliveryOrderDate <=', $endDate);
		$this->db->order_by('a.companyID');
		$this->db->order_by('a.customerID');
		$this->db->group_by('a.customerID');
		$this->db->from('comp_delivery_order a');
		$this->db->join('customer b', 'b.ID = a.customerID');	
		$this->db->join('comp_delivery_detail c', 'c.deliveryID = a.ID', 'left outer');	
		$query = $this->db->get();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return FALSE;
		}
	}
	public function SalesByItems_all_date($startDate, $endDate){
		$this->db->select('a.*');
                $this->db->select('concat(a.formNo,"[",lpad(a.serialNo,8,"0"),"]") as formNo', false);
		$this->db->select('b.itemCode');
		$this->db->select('b.name');
		$this->db->select('b.description');
		$this->db->select('sum(c.unitPrice * a.exchangeRate) as unitPrice');
		$this->db->select('sum(c.amount * a.exchangeRate) as itemTotal');
		$this->db->select('sum(c.quantitySent) as quantitySent');
		$this->db->where('a.companyID', element('compID', $this->session->userdata('logged_in')));
		$this->db->where('a.deliveryOrderDate >=', $startDate);
		$this->db->where('a.deliveryOrderDate <=', $endDate);
		$this->db->order_by('a.companyID');
		$this->db->order_by('c.itemID');
		$this->db->group_by('c.itemID');
		$this->db->from('comp_delivery_order a');
		$this->db->join('comp_delivery_detail c', 'c.deliveryID = a.ID', 'left outer');	
		$this->db->join('item b', 'b.ID = c.itemID', 'left outer');	
		$query = $this->db->get();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return FALSE;
		}
	}
	public function SalesDeliveryOrder_unpost_date($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('c.name');
		$this->db->select('d.employeeName');
		$this->db->where('a.companyID', element('compID', $this->session->userdata('logged_in')));
		$this->db->where('a.deliveryOrderDate >=', $startDate);
		$this->db->where('a.deliveryOrderDate <=', $endDate);
		$this->db->where('a.updateAble', 1);
		$this->db->order_by('a.companyID');
		$this->db->from('comp_delivery_order a');
		$this->db->join('customer b', 'b.ID = a.customerID');	
		$this->db->join('master_code c', 'c.ID = a.shippingMethodID', 'left outer');	
		$this->db->join('comp_employee d', 'd.ID = a.employeeID', 'left outer');	
		$query = $this->db->get();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return FALSE;
		}
	}
	public function SalesDeliveryOrder_id($ID){
		$this->db->select('a.*,CONCAT(a.formNo," [",lpad(a.serialNo,8,0),"]") as formNo',false);
		$this->db->select('b.customerName');
		$this->db->select('c.name');
		$this->db->select('d.employeeName');
		$this->db->select('e.OrderNo');
		$this->db->select('f.fldcurr_code');
		$this->db->select('g.code as locationCode');
		$this->db->select('h.addressName');
		$this->db->select('l.*');
		$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_delivery_order a');
		$this->db->join('customer b', 'b.ID = a.customerID');	
		$this->db->join('master_code c', 'c.ID = a.shippingMethodID', 'left outer');	
		$this->db->join('comp_employee d', 'd.ID = a.employeeID', 'left outer');	
		$this->db->join('comp_sales_order e', 'd.ID = a.salesOrderID', 'left outer');	
		$this->db->join('_ref_currency f', 'f.fldid = a.currencyID');	
		$this->db->join('tbl_location g', 'g.fldid = a.locationID');
		$this->db->join('customer_address h', 'h.ID = a.deliverToID', 'left outer');	
		$this->db->join('tbl_location l', 'l.fldid = a.locationID');
		$query = $this->db->get();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return FALSE;
		}
	}
	
	public function SalesDeliveryOrder_Insert(){		

		$newData = array(
			'companyID' => element('compID', $this->session->userdata('logged_in')),
			'customerID' => $this->input->post('customerID'),
			'salesOrderID' => $this->input->post('salesOrderID'),
			'deliveryOrderDate' => date("Y-m-d", strtotime($this->input->post('effdate'))),
			'locationID' => $this->input->post('locationID'),
			'toAddress' => $this->input->post('toAddress'),
			'shippingMethodID' => $this->input->post('shippingMethodID'),
			'currencyID' => $this->input->post('currencyID'),
			'exchangeRate' => $this->input->post('exchangeRate'),
			'employeeID' => $this->input->post('salesPerson'),
			'deliverToID' => $this->input->post('deliveryToID'),
			'totalAmount' => $this->input->post('totalAmount'),
			'memo' => $this->input->post('memo'),
			'createdBy' => element('role', $this->session->userdata('logged_in'))
		);
		$query = $this->db->insert('comp_delivery_order', $newData);
		if($query){
			return $this->db->insert_id();
		}else{
			return FALSE;
		}
	}
	public function SalesDeliveryOrder_Update($ID, $data){
		$this->db->where('ID', $ID);
		$query = $this->db->update('comp_delivery_order', $data); 
		if($query === TRUE){
			return $this->db->last_query();
		} else {
			return FALSE;
		}
	}
	
	public function SalesDeliveryOrder_Delete($ID){
		$this->db->where('ID', $ID);
		$query = $this->db->delete('comp_delivery_order'); 
		if($query === TRUE){
			return $this->db->last_query();
		} else {
			return FALSE;
		}
	}
	 public function SalesDeliveryOrderpost($id, $data){
		$this->db->where('ID', $id);
//		$this->db->where('updateAble', 1);
		$query = $this->db->update('comp_delivery_order', $data);
		if($query){
			$this->db->flush_cache();
                        $this->db->select('comp_delivery_order.*');
                        $this->db->select('concat(comp_delivery_order.formNo,"[",lpad(comp_delivery_order.serialNo,8,"0"),"]") as formNo', false);

			$this->db->where('ID', $id);
			$this->db->from('comp_delivery_order');
			$query1 = $this->db->get();
			return $query1->result();
		}else{
			return false;
		}
	 }
	 public function SalesDeliveryOrderdetails_deliveryID($journalID, $header, $detailsData, $year, $period){
//	 public function SalesDeliveryOrderdetails_deliveryID($detailsData){
		if($detailsData){
			$seq = 0;
			$journalAmount = 0;
			foreach($detailsData as $row):
				$i = $row->quantitySent;
				$lineAmount = 0;
				$cogs = 'cogs';
				while($i > 0){ 
					$this->db->flush_cache();
					$this->db->select('a.*');
					$this->db->select('b.exchangeRate');
					$this->db->select('b.currencyID');
					$this->db->select('b.locationID');
					$this->db->select('c.inventoriesID');
					$this->db->select('if(c.cogsID != 0, c.cogsID , d.accountID) as cogsAccountID', false);
					$this->db->select('ifnull(e.unitm,1) as umfrom', false);
					$this->db->select('ifnull(e.factor,1) as umto', false);
//					$this->db->select('c.outputTaxID');
					$this->db->where('a.itemID = ', $row->itemID);
					$this->db->where('d.accountCode = ', $cogs);
					$this->db->where('a.balanceItem >', 0);
					$this->db->from('comp_receive_item_detail a');
					$this->db->join('comp_receive_item b', 'b.ID = a.receiveID and b.companyID = '.element('compID', $this->session->userdata('logged_in')).' and b.locationID = '. $header->locationID);	
					$this->db->join('item c', 'c.ID = a.itemID');	
					$this->db->join('comp_control_acct_setup d', "d.companyID = b.companyID", 'left outer', FALSE);	
					$this->db->join('_sys_um_conv e', "e.from_um = a.umID and e.to_um = " . $row->unitmeasure, 'left outer', FALSE);	
					$this->db->limit(1);
					$query = $this->db->get();
					$itemDet = $query->row_array();
					if($itemDet){
//						echo $itemDet['ID'];
						if(($itemDet['balanceItem'] * $itemDet['umto'] / $itemDet['umfrom'] ) < $i){
							if($itemDet['exchangeRate'] == 0){
								$exchangeRate = 	1;							
							}else{
								$exchangeRate = 	$itemDet['exchangeRate'];							
							}
							$this->db->set('balanceItem', 'balanceItem - '.$itemDet['balanceItem'], false);
							$this->db->where('ID', $itemDet['ID']);
							$this->db->where('balanceItem', $itemDet['balanceItem']);
							$query = $this->db->update('comp_receive_item_detail'); 
							if($query === TRUE){
								$i = $i - ($itemDet['balanceItem'] * $itemDet['umto'] / $itemDet['umfrom']);
								$seq++;
								$journalAmount = $journalAmount + (($itemDet['unitPrice'] * $exchangeRate) * $itemDet['balanceItem']);
								$newData = array(
									'companyID' => element('compID', $this->session->userdata('logged_in')),
									'journalID' => $journalID,
									'sequence' => $seq,
									'description' => $row->itemCode . ' [ '. $row->description . ' ]' . $itemDet['ID'],
									'acctCode' => $itemDet['inventoriesID'],
									'amount_cr' => $itemDet['unitPrice'] * $exchangeRate * $itemDet['balanceItem'],
									'currencyID' => ($itemDet['currencyID'] == 0)? 1: $itemDet['currencyID'],
									'exchange_rate' => $exchangeRate,
									'currency_amount' => $itemDet['unitPrice'] * $itemDet['balanceItem'],
									'period' => $period,
									'year' => $year,
									'line' => $row->ID,
									'formNo' => $header->formNo . '[' . sprintf('%08d', $header->ID) . ']',
									'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')),
									'journalID' => $journalID,
									'sequence' => $seq,
									'description' => $row->itemCode . ' [ '. $row->description . ' ]',
									'acctCode' => $itemDet['cogsAccountID'],
									'amount_dr' => $itemDet['unitPrice'] * $exchangeRate * $itemDet['balanceItem'],
									'currencyID' => ($itemDet['currencyID'] == 0)? 1: $itemDet['currencyID'],
									'exchange_rate' => $exchangeRate,
									'currency_amount' => $itemDet['unitPrice'] * $itemDet['balanceItem'],
									'period' => $period,
									'year' => $year,
									'line' => $row->ID,
									'formNo' => $header->formNo . '[' . sprintf('%08d', $header->ID) . ']',
									'createdBy' => element('role', $this->session->userdata('logged_in'))
								);
								$queryInsert = $this->db->insert('comp_journal_detail', $newData);
							}
						}else{
							$balance = $i * $itemDet['umfrom'] / $itemDet['umto'];
							$this->db->set('balanceItem', 'balanceItem - '.$balance, false );
							$this->db->where('ID', $itemDet['ID']);
							$this->db->where('balanceItem', $itemDet['balanceItem']);
							$query = $this->db->update('comp_receive_item_detail'); 
							if($query === TRUE){
								$seq++;
								$journalAmount = $journalAmount + (($itemDet['unitPrice'] * $exchangeRate) * $itemDet['balanceItem']);
								$newData = array(
									'companyID' => element('compID', $this->session->userdata('logged_in')),
									'journalID' => $journalID,
									'sequence' => $seq,
									'description' => $row->itemCode . ' [ '. $row->description . ' ]',
									'acctCode' => $itemDet['inventoriesID'],
									'amount_cr' => $itemDet['unitPrice'] * $i * $exchangeRate,
									'currencyID' => ($itemDet['currencyID'] == 0)? 1: $itemDet['currencyID'],
									'exchange_rate' => $exchangeRate,
									'currency_amount' => $itemDet['unitPrice'] * $i,
									'period' => $period,
									'year' => $year,
									'line' => $row->ID,
									'formNo' => $header->formNo . '[' . sprintf('%08d', $header->ID) . ']',
									'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')),
									'journalID' => $journalID,
									'sequence' => $seq,
									'description' => $row->itemCode . ' [ '. $row->description . ' ]',
									'acctCode' => $itemDet['cogsAccountID'],
									'amount_dr' => $itemDet['unitPrice'] * $i * $exchangeRate,
									'currencyID' => ($itemDet['currencyID'] == 0)? 1: $itemDet['currencyID'],
									'exchange_rate' => $exchangeRate,
									'currency_amount' => $itemDet['unitPrice'] * $i,
									'period' => $period,
									'year' => $year,
									'line' => $row->ID,
									'formNo' => $header->formNo . '[' . sprintf('%08d', $header->ID) . ']',
									'createdBy' => element('role', $this->session->userdata('logged_in'))
								);
								$queryInsert = $this->db->insert('comp_journal_detail', $newData);
								$i = $i - $i;
							}
						}
					}
				}
			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;
		}
	 }
	
// Sales SalesDeliveryOrder details 
	//Delivery order with Sales Order
	public function SalesOrderDetails_SO_ID($ID){
		$this->db->select('a.*');
		$this->db->select('a.requiredDate');
		$this->db->select('b.ID as doID');
		$this->db->select('b.quantitySent');
		$this->db->select('b.lineNo');
		$this->db->select('b.unitPrice as price');
		$this->db->select('b.amount as shipAmount');
		$this->db->select('c.itemCode');
		$this->db->select('c.description');
		$this->db->select('d.code');
		$this->db->where('a.salesID',$ID);
		$this->db->order_by('a.salesID');
		$this->db->order_by('a.requiredDate');
		$this->db->from('comp_sales_detail a');
		$this->db->join('comp_delivery_detail b', 'b.soDetailsLine = a.ID and b.deliveryID = '.$ID, 'LEFT OUTER' );	
		$this->db->join('item c', 'c.ID = a.itemID');	
		$this->db->join('_sys_um d', 'd.fldid = a.unitmeasure');	
		$query = $this->db->get();
		if($query->num_rows() >= 1){
			return $query->result();
		}else{
			return FALSE;
		}
	}
	public function SalesOrderDetails_withoutSO_ID($ID){
		$this->db->select('a.*');
		$this->db->select('b.itemCode');
		$this->db->select('b.description');
		$this->db->select('c.code');
		$this->db->where('a.deliveryID', $ID);
		$this->db->where('a.salesID', 0);
		$this->db->order_by('a.itemID');
		$this->db->from('comp_delivery_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_ID($ID){
		$this->db->select('a.*');
		$this->db->select('b.itemCode');
		$this->db->select('b.description');
		$this->db->select('c.code');
		$this->db->where('a.ID', $ID);
		$this->db->order_by('a.itemID');
		$this->db->from('comp_delivery_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;
		}
	}
//Delivery orders	
	public function SalesDeliveryOrderDetails_ID($deliveryID){
		$this->db->select('a.*');
		$this->db->select('CONCAT("[",b.itemCode,"] ",name) as itemCodeName,description,',false);
		$this->db->select('b.description');
		$this->db->select('c.code');
		$this->db->where('a.deliveryID', $deliveryID);
		$this->db->order_by('a.itemID');
		$this->db->from('comp_delivery_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 SalesDeliveryOrderDetails_Insert($deliveryID){
		$this->db->where('ID', $this->input->post('sodLineNo'));
		$this->db->from('comp_sales_detail');
		$query = $this->db->get();
		$orderDetails = $query->result();
		$this->db->flush_cache();
		$this->db->set('quantityShip','quantityShip +'.$this->input->post('qtySent'), FALSE);
		$this->db->set('updatedBy',element('role', $this->session->userdata('logged_in')));
		$this->db->where('ID', $this->input->post('sodLineNo'));
		$queryupdate = $this->db->update('comp_sales_detail'); 
		$this->db->flush_cache();
		$newData = array(
			'deliveryID' => $deliveryID,
			'salesID' => $this->input->post('salesOrderID'),
			'soDetailsLine' => $this->input->post('sodLineNo'),
			'itemID' => $orderDetails[0]->itemID,
			'requiredDate' => date("Y-m-d", strtotime($this->input->post('dateRequired'))),
			'amount' => $this->input->post('amount'),
			'unitmeasure' => $orderDetails[0]->unitmeasure,
			'unitPrice' => $orderDetails[0]->unitPrice,
			'quantityOrdered' => $orderDetails[0]->quantityOrder,
			'quantitySent' => $this->input->post('qtySent'),
			'createdBy' => element('role', $this->session->userdata('logged_in'))
		);
		$query = $this->db->insert('comp_delivery_detail', $newData);
		if($query){
			return $this->db->insert_id();
		}else{
			return FALSE;
		}
	}
	public function SalesDeliveryOrderDetailsWo_Insert($deliveryID){
		$newData = array(
			'deliveryID' => $deliveryID,
			'lineNo' => $this->input->post('lineNo'),
			'itemID' => $this->input->post('itemID'),
			'requiredDate' => date("Y-m-d"),
			'amount' => $this->input->post('amount'),
			'unitmeasure' => $this->input->post('unitmeasure'),
			'unitPrice' => $this->input->post('unitPrice'),
			'quantitySent' => $this->input->post('qtySent'),
			'createdBy' => element('role', $this->session->userdata('logged_in'))
		);
		$query = $this->db->insert('comp_delivery_detail', $newData);
		if($query){
			return $this->db->insert_id();
		}else{
			return FALSE;
		}
	}
	public function SalesDeliveryOrderDetails_Update(){
		$this->db->where('ID', $this->input->post('fldid'));
		$this->db->from('comp_delivery_detail');
		$query = $this->db->get();
		$doDetails = $query->result();
		switch ($this->input->post('fieldname')){
			case 'amount':
				$this->db->flush_cache();
				$amountDiffer = $this->input->post('eContent') - $doDetails[0]->amount;
				$this->db->set('totalAmount', 'totalAmount +'.$amountDiffer, FALSE );
				$this->db->where('ID', $this->input->post('deliveryID'));
				$query = $this->db->update('comp_delivery_order');
				$this->db->flush_cache();
			break;
			case 'quantitySent':
				$this->db->flush_cache();
				$amountDiffer = $this->input->post('eContent') - $doDetails[0]->quantitySent;
				$this->db->set('quantityShip', 'quantityShip +'.$amountDiffer, FALSE );
				$this->db->where('ID', $doDetails[0]->soDetailsLine);
				$query = $this->db->update('comp_sales_detail');
				echo $this->db->last_query();
				$this->db->flush_cache();
			break;
		}
		$this->db->flush_cache();
		$dataupdate = array(
			$this->input->post('fieldname') => $this->input->post('eContent')
		);
		$this->db->where('ID', $this->input->post('fldid'));
		$query = $this->db->update('comp_delivery_detail', $dataupdate);
	}
	public function SalesDeliveryOrderDetails_Delete($ID){
		$this->db->where('ID', $ID);
		$query = $this->db->delete('comp_delivery_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 */
