<?php  
class ModelSaleShipping extends Model {
	public function addShipping($data) {
		foreach ($data['stock_status'] as $language_id => $value) {
			if (isset($stock_status_id)) {
				$this->db->query("INSERT INTO " . DB_PREFIX . "stock_status SET stock_status_id = '" . (int)$stock_status_id . "', language_id = '" . (int)$language_id . "', name = '" . $this->db->escape($value['name']) . "'");
			} else {
				$this->db->query("INSERT INTO " . DB_PREFIX . "stock_status SET language_id = '" . (int)$language_id . "', name = '" . $this->db->escape($value['name']) . "'");
				
				$stock_status_id = $this->db->getLastId();
			}
		}
		
		$this->cache->delete('stock_status');
	}

	public function editShipping($stock_status_id, $data) {
		$this->db->query("DELETE FROM " . DB_PREFIX . "stock_status WHERE stock_status_id = '" . (int)$stock_status_id . "'");

		foreach ($data['stock_status'] as $language_id => $value) {
			$this->db->query("INSERT INTO " . DB_PREFIX . "stock_status SET stock_status_id = '" . (int)$stock_status_id . "', language_id = '" . (int)$language_id . "', name = '" . $this->db->escape($value['name']) . "'");
		}
				
		$this->cache->delete('stock_status');
	}
	
	public function getCustomerInfo($customer_id) {
		$query = $this->db->query("select * from " . DB_PREFIX . "custom_customer_details  WHERE customerid = '" . (int)$customer_id . "'");
		return $query->row;
	}
	
	public function updateTrackingNumber($trackingnumber,$order_id) {
		$this->db->query("UPDATE " . DB_PREFIX . "order_stockist SET CustTrackingNum = '". $trackingnumber ."' WHERE order_id = '" . (int)$order_id . "'");
	
	}
	public function updateBillInformation($data,$OrderIds) {

		
		$Seq_query = $this->db->query("select invoice_sequence+1 as sequence from sequence_tbl where id = 1 ");
		//$Seq_query = $this->db->query("select invoice_sequence as sequence from sequence_tbl where id = 1 ");
		$InvoiceID = $Seq_query->row['sequence'];
		
		if($data['membershipno']!=""){
			//$query = $this->db->query("select a.membershipno,a.storebillingshippingname,a.discount, b.retailedgeidentifier as retailedgeidentifier,c.gstrate as gstrate from custom_customer_details a, custom_buying_group b, custom_currency_tax c where a.membershipno = '". $data['membershipno'] ."' and a.buyinggroupcode = b.code and c.billingcurrency = a.billingcurrency ");
			$query = $this->db->query("select a.membershipno,a.billingemail,a.customercode,a.storebillingshippingname,a.discount,c.entitybillingemail , b.retailedgeidentifier as retailedgeidentifier,c.gstrate as gstrate from custom_customer_details a left join custom_buying_group b on a.buyinggroupcode = b.code left join custom_currency_tax c on c.billingcurrency = a.billingcurrency where a.membershipno = '". $data['membershipno'] ."' limit 1 ");

		}else{
			$query = $this->db->query("select a.membershipno,a.billingemail,a.customercode,a.storebillingshippingname,a.discount,c.entitybillingemail , b.retailedgeidentifier as retailedgeidentifier,c.gstrate as gstrate from custom_customer_details a, custom_buying_group b, custom_currency_tax c, order_stockist d where d.order_id in (". $OrderIds . ") and a.customerid=d.customer_id and a.buyinggroupcode = b.code and c.billingcurrency = a.billingcurrency ");
		}
		
		$discountPer = $query->row['discount'];
		$BuyingGroup = $query->row['retailedgeidentifier'];		
		$gstrate = $query->row['gstrate'];	
		
		$itemCount = $data['itemCount'];
		$itemTotal = $data['itemTotal'];
		$GSTTotal = $data['GSTTotal'];
		//$TotalPrice = $data['itemTotal'] + $data['shipping'] * (100+$gstrate)/100 + $data['others'] * (100+$gstrate)/100 ;
		$Discount = Round(($data['itemTotal'] - $data['GSTTotal']) * $discountPer/100,0) ;
		$ExpensesGST = ($data['shipping'] + $data['others']) * $gstrate / 100;
		$TotalGST = $ExpensesGST + $GSTTotal - ( $Discount * $gstrate/100 );
		$TotalPrice = $data['itemTotal'] + $data['shipping'] * (100+$gstrate)/100 + $data['others'] * (100+$gstrate)/100 - $Discount - ($Discount*$gstrate/100);

		//$query = $this->db->query("select a.membershipno,a.storebillingshippingname,a.discount, c.gstrate as gstrate from custom_customer_details a, custom_currency_tax c where a.membershipno = '". $data['membershipno'] ."' and c.billingcurrency = a.billingcurrency ");

		$InvoiceNo = $data['membershipno'] . $InvoiceID;
		
		$invoicedata['InvoiceNo']= $InvoiceNo;
		$invoicedata['customercode']= $query->row['customercode'];
		$invoicedata['TotalPrice']= $TotalPrice;
		$invoicedata['TrackingNum']= $data['TrackingNum'];
		$invoicedata['billingemail']= $query->row['billingemail'];
		$invoicedata['entitybillingemail']= $query->row['entitybillingemail'];
		$invoicedata['entitybillingemail']= $query->row['entitybillingemail'];
		
		$this->db->query("INSERT INTO " . DB_PREFIX . "custom_invoice_header SET InvoiceNo  = '". $InvoiceNo  ."', InvoiceDate = CURDATE(), BuyingGroup = '". $BuyingGroup ."', 
		BuyingGroupMemberId = '" . $data['membershipno'] . "', SoldToCity = '" . $query->row['storebillingshippingname'] . "', TotalItems = '" . $itemCount  . "', TotalPrice = '". $TotalPrice  ."',  SumGST  = '". $GSTTotal  ."',  DiscountAmount = '". $Discount  ."', Exp1Desc = 'Shipping', Exp1Amount = '". $data['shipping']  ."', 
		Exp2to4Desc = '". $data['descr'] ."', Exp2to4Amount = '". $data['others']  ."', ExpensesGST = '". $ExpensesGST  ."', TotalGST = '". $TotalGST  ."', invstatus = '', debugMode = '0', comment = '". $OrderIds ."' "); 
		
		$this->db->query("UPDATE " . DB_PREFIX . "order_stockist SET InvoiceNum = '". $InvoiceNo ."' WHERE order_id in (" . $OrderIds . ")");

		$this->db->query("update sequence_tbl set invoice_sequence = invoice_sequence+1 where id = 1 ");
		
		return $invoicedata; 
	
	}	
	
	
	
	public function deleteShipping($stock_status_id) {
		$this->db->query("DELETE FROM " . DB_PREFIX . "stock_status WHERE stock_status_id = '" . (int)$stock_status_id . "'");
	
		$this->cache->delete('stock_status');
	}
	public function getGemstones() {
		$query = $this->db->query("select gemstone_id, gemcode FROM " . DB_PREFIX . "gemstone");
	
		return $query->rows;
	}
	public function getMetals() {
		$query = $this->db->query("select metal_id, metal_code FROM " . DB_PREFIX . "metal");
	
		return $query->rows;
	}
		
	public function getShippings() {
		//$query = $this->db->query("select ccd.customercode,customerparentcode, count(*) as total from custom_customer_details ccd, order_stockist os where ccd.customerid=os.customer_id and trim(custtrackingnum) = '' group by ccd.customerparentcode,ccd.customercode ");
		
		$query = $this->db->query("select '' as customercode,customerparentcode, count(*) as total from custom_customer_details ccd, order_stockist os where ccd.customerid=os.customer_id and trim(custtrackingnum) = '' and customerparentcode <> '' group by ccd.customerparentcode union all select ccd.customercode,customerparentcode, count(*) as total from custom_customer_details ccd, order_stockist os where ccd.customerid=os.customer_id and trim(custtrackingnum) = '' and customerparentcode = '' group by ccd.customercode,ccd.customerparentcode");
		
		return $query->rows;
	}
	public function getBillings() {
		
		$query = $this->db->query("select '' as customercode, customerparentcode,ccd.membershipno,CustTrackingNum , count(*) as total from custom_customer_details ccd, order_stockist os where ccd.customerid=os.customer_id and trim(custtrackingnum) <> '' and trim(InvoiceNum ) = '' and customerparentcode <> '' group by  customerparentcode,ccd.membershipno,CustTrackingNum  
		union all select ccd.customercode, customerparentcode, ccd.membershipno,  CustTrackingNum , count(*) as total from custom_customer_details ccd, order_stockist os where ccd.customerid=os.customer_id and trim(custtrackingnum) <> '' and trim(InvoiceNum ) = '' and customerparentcode = '' group by customercode, customerparentcode,ccd.membershipno,CustTrackingNum   ");
		
		/* Testing */
		//$query = $this->db->query("select '' as customercode, customerparentcode,ccd.membershipno,CustTrackingNum , count(*) as total from custom_customer_details ccd, order_stockist os where ccd.customerid=os.customer_id and trim(custtrackingnum) <> ''   and customerparentcode <> '' group by  customerparentcode,ccd.membershipno,CustTrackingNum  
		//union all select ccd.customercode, customerparentcode, ccd.membershipno,  CustTrackingNum , count(*) as total from custom_customer_details ccd, order_stockist os where ccd.customerid=os.customer_id and trim(custtrackingnum) <> ''   and customerparentcode = '' group by customercode, customerparentcode,ccd.membershipno,CustTrackingNum   ");
		
		return $query->rows;
	}
	
	public function getShippingLists($customerparentcode,$customercode) {
		//$query = $this->db->query("select ccd.customercode,customerparentcode, count(*) as total from custom_customer_details ccd, order_stockist os where ccd.customerid=os.customer_id and trim(custtrackingnum) = '' group by ccd.customerparentcode,ccd.customercode ");
		
		if($customerparentcode<>""){
			$query = $this->db->query("select ccd.*,os.*,( SELECT  UPPER(left(ib.name,1)) AS CATEGORYLETTER FROM `product_to_category` ia, category_description ib where ia.product_id = os.product_id and ia.category_id in (59,60,62,138,139,140,141,153,186) and ia.category_id=ib.category_id and ib.language_id = 1 limit 1) as itemtype 
			from custom_customer_details ccd, order_stockist os where ccd.customerid=os.customer_id and trim(custtrackingnum) = '' and customerparentcode = '" . $customerparentcode . "'  order by ccd.customercode,Customized desc, os.descr ");
		}
		if($customerparentcode==""){
			$query = $this->db->query("select ccd.*,os.*,( SELECT  UPPER(left(ib.name,1)) AS CATEGORYLETTER FROM `product_to_category` ia, category_description ib where ia.product_id = os.product_id and ia.category_id in (59,60,62,138,139,140,141,153,186) and ia.category_id=ib.category_id and ib.language_id = 1 limit 1) as itemtype
 from custom_customer_details ccd, order_stockist os where ccd.customerid=os.customer_id and trim(custtrackingnum) = '' and customercode = '" . $customercode . "'  order by ccd.customercode,Customized desc, os.descr  ");
		}
		
		return $query->rows;
	}
	
	public function getBillingLists($customerparentcode,$customercode,$membershipno) {
		// old ignore $query = $this->db->query("select ccd.customercode,customerparentcode, count(*) as total from custom_customer_details ccd, order_stockist os where ccd.customerid=os.customer_id and trim(custtrackingnum) = '' group by ccd.customerparentcode,ccd.customercode ");


		if($customerparentcode<>""){
			$query = $this->db->query("select ccd.*,os.*,( SELECT  UPPER(left(ib.name,1)) AS CATEGORYLETTER FROM `product_to_category` ia, category_description ib where ia.product_id = os.product_id and ia.category_id in (59,60,62,138,139,140,141,153,186) and ia.category_id=ib.category_id and ib.language_id = 1 limit 1) as itemtype
			from custom_customer_details ccd, order_stockist os where ccd.customerid=os.customer_id and membershipno = '". $membershipno ."' and trim(custtrackingnum) <> '' and trim(InvoiceNum ) = '' and customerparentcode = '" . $customerparentcode . "'  order by ccd.customercode,membershipno,CustTrackingNum,Customized desc ");
		}
		if($customerparentcode==""){
			$query = $this->db->query("select  ccd.*,os.*,( SELECT  UPPER(left(ib.name,1)) AS CATEGORYLETTER FROM `product_to_category` ia, category_description ib where ia.product_id = os.product_id and ia.category_id in (59,60,62,138,139,140,141,153,186) and ia.category_id=ib.category_id and ib.language_id = 1 limit 1) as itemtype
			from custom_customer_details ccd, order_stockist os where ccd.customerid=os.customer_id and membershipno = '". $membershipno ."' and trim(custtrackingnum) <> '' and trim(InvoiceNum ) = '' and customercode = '" . $customercode . "'  order by ccd.customercode,membershipno,CustTrackingNum,Customized desc  ");
		}

		/* Testing 
		if($customerparentcode<>""){
			$query = $this->db->query("select * from custom_customer_details ccd, order_stockist os where ccd.customerid=os.customer_id and membershipno = '". $membershipno ."' and trim(custtrackingnum) <> '' and customerparentcode = '" . $customerparentcode . "'  order by ccd.customercode,membershipno,CustTrackingNum,Customized desc ");
		}
		if($customerparentcode==""){
			$query = $this->db->query("select * from custom_customer_details ccd, order_stockist os where ccd.customerid=os.customer_id and membershipno = '". $membershipno ."' and trim(custtrackingnum) <> '' and customercode = '" . $customercode . "'  order by ccd.customercode,membershipno,CustTrackingNum,Customized desc  ");
		}
		*/
		return $query->rows;
	}
	
	
	
	public function getShipping($data = array()) {
		if ($data) {
			$sql = "SELECT * FROM " . DB_PREFIX . "stock_status WHERE language_id = '" . (int)$this->config->get('config_language_id') . "'";
      		
			$sql .= " ORDER BY name";	
			
			if (isset($data['order']) && ($data['order'] == 'DESC')) {
				$sql .= " DESC";
			} else {
				$sql .= " ASC";
			}
			
			if (isset($data['start']) || isset($data['limit'])) {
				if ($data['start'] < 0) {
					$data['start'] = 0;
				}					
	
				if ($data['limit'] < 1) {
					$data['limit'] = 20;
				}	
			
				$sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
			}			
			
			$query = $this->db->query($sql);
		
			return $query->rows;
		} else {
			$stock_status_data = $this->cache->get('stock_status.' . (int)$this->config->get('config_language_id'));
		
			if (!$stock_status_data) {
				$query = $this->db->query("SELECT stock_status_id, name FROM " . DB_PREFIX . "stock_status WHERE language_id = '" . (int)$this->config->get('config_language_id') . "' ORDER BY name");
	
				$stock_status_data = $query->rows;
			
				$this->cache->set('stock_status.' . (int)$this->config->get('config_language_id'), $stock_status_data);
			}	
	
			return $stock_status_data;			
		}
	}
	
	public function getShippingDetail($stock_status_id) {
		$stock_status_data = array();
		
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "stock_status WHERE stock_status_id = '" . (int)$stock_status_id . "'");
		
		foreach ($query->rows as $result) {
			$stock_status_data[$result['language_id']] = array('name' => $result['name']);
		}
		
		return $stock_status_data;
	}
	
	public function getShippingCount() {
      	$query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "stock_status WHERE language_id = '" . (int)$this->config->get('config_language_id') . "'");
		
		return $query->row['total'];
	}	
	
	public function getProductDescription($NewInvoiceNo) {
		$query = $this->db->query("SELECT concat(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(descr,'<br>','|'),' Gemstone',''),'Primary Metal 1:',''),'CUBIC ZIRCONIA','CZ'),' GemStone',''),' 925 Sterling ',''),'CubicZirconia','CZ'),'Center:',''),'Accent 1:',''),'Accent 2:',''),'Accent 3:',''),'Accent 4:',''),'Accent 5:',''),' Karat','k'),'Ring Size : RESTOCK(AUS)','Size:Restock'),'Ring Size : ','Size:'),' |','|'),'| ','|')) 'Label',order_id,invoicenum FROM order_stockist os where invoicenum = '" . $NewInvoiceNo . "'");
		return $query->row['Label'];
	}	

	public function getInvoiceHeader($NewInvoiceNo) {

		$query = $this->db->query("select cct.billerentity,cct.billerentity1,cct.billerbusinessidentifier,ccd.billingemail,cct.billeraddress,cct.billerbankdetails,cct.image,cct.gstrate,
cih.InvoiceNo,cih.InvoiceDate,cih.totalitems,cih.totalprice,cih.sumgst,cih.discountamount,cih.exp1desc,cih.exp1amount,cih.exp2to4desc,cih.exp2to4amount,cih.expensesgst,cih.totalgst,
cbg.name,cbg.billingaddress, cbg.billingcountry, cbg.billingcurrency, 
ccd.storebillingshippingname,ccd.membershipno,ccd.discount, adr.address_1,adr.address_2,adr.city,adr.postcode,zon.name as zonname,zon.code, cou.name as couname
from  custom_invoice_header cih,custom_customer_details ccd,custom_buying_group cbg,zone zon,address adr,country cou,custom_currency_tax cct
where cih.BuyingGroupMemberId=ccd.membershipno and ccd.buyinggroupcode=cbg.code and ccd.shipping_address_id=adr.address_id and adr.zone_id=zon.zone_id and zon.country_id=cou.country_id and cbg.billingcurrency=cct.billingcurrency
 and cih.InvoiceNo = '" . $NewInvoiceNo . "'");

		// If custom_buying_group is not matched with the valid  cbg.code then use the another sql to find the  billing address
		if($query->num_rows){
			$this->log->write("rows ".$query->num_rows);			
			return $query->row;
		}else{
			
		$query1 = $this->db->query("select distinct cct.billerentity,cct.billerentity1,cct.billerbusinessidentifier,ccd.billingemail,cct.billeraddress,cct.billerbankdetails,cct.image,cct.gstrate,
cih.InvoiceNo,cih.InvoiceDate,cih.totalitems,cih.totalprice,cih.sumgst,cih.discountamount,cih.exp1desc,cih.exp1amount,cih.exp2to4desc,cih.exp2to4amount,cih.expensesgst,cih.totalgst,
ccd.storebillingshippingname as name,  CONCAT(cbg.address_1,',',cbg.address_2) as billingaddress, CONCAT(cbg.city,',',cbg.postcode,',',cbgzon.name,',',cbgzon.code,',',cbgcou.name) as billingcountry,  ccd.billingcurrency ,
ccd.storebillingshippingname,ccd.membershipno,ccd.discount, adr.address_1,adr.address_2,adr.city,adr.postcode,zon.name as zonname,zon.code, cou.name as couname
from  custom_invoice_header cih,custom_customer_details ccd,address cbg,zone cbgzon,zone zon,country cbgcou,address adr,country cou,custom_currency_tax cct
where cih.BuyingGroupMemberId=ccd.membershipno and ccd.billing_address_id=cbg.address_id and cbg.zone_id=cbgzon.zone_id and cbgzon.country_id=cbgcou.country_id  
and ccd.shipping_address_id=adr.address_id and adr.zone_id=zon.zone_id and zon.country_id=cou.country_id 
and ccd.billingcurrency=cct.billingcurrency 
and cih.InvoiceNo = '" . $NewInvoiceNo . "' limit 1");
			$this->log->write($query1->num_rows . ' - image ' . $query1->row['image']);
			return $query1->row;
		}
		
	}
	
	public function getInvoiceDetails($NewInvoiceNo) {

		$OSquery = $this->db->query("select os.order_id,os.descr,os.Customized,os.aj_price,os.aj_price-os.gst as price,os.gst,os.SupplierOrderRef,os.custtrackingnum , os.RRP,os.customer_id
		,concat(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(descr,'<br>','|'),' Gemstone',''),'Primary Metal 1:',''),'CUBIC ZIRCONIA','CZ'),' GemStone',''),' 925 Sterling ',''),'CubicZirconia','CZ'),'Center:',''),'Accent 1:',''),'Accent 2:',''),'Accent 3:',''),'Accent 4:',''),'Accent 5:',''),' Karat','k'),'Ring Size :  RESTOCK(AUS)','Size:Restock'),'Ring Size : ','Size:'),' |','|'),'| ','|')) as Label		
		from  custom_invoice_header cih, order_stockist os where os.invoicenum=cih.invoiceno
		and cih.InvoiceNo = '" . $NewInvoiceNo . "'");

		return $OSquery->rows;
	}
	
}

?>