<?php
class classStoreItemSaleInvoice
{
	
	private $saleInvoiceID=NULL;
	private $userID=NULL;
	private $locationID=NULL;
	
	private $saleItemID=NULL;
	private $saleItemDate=NULL;
	private $storeItemID=NULL;
	private $expiryDate=NULL;
	private $saleQty=NULL;
	
	private $staffName=NULL;
	private $departmentName=NULL;
	private $description=NULL;
	
	private $delivereByName=NULL;
	private $delivereByID=NULL;
	private $delivereDate=NULL;
	private $delivereStatus=NULL;
	
	private $refundStatus=NULL;
	private $refundQty=NULL;
	
	
	################################# SET FUNCTIONS ############################################
	
	function setSaleInvoiceID($saleInvoiceID)
	{
		if($saleInvoiceID!='')
		{
			$this->saleInvoiceID = $saleInvoiceID;
		}
		else
		{
			throw new MyException(121100);
		}
	}
	function setUserID($userID)
	{
		if($userID!='')
		{
			$this->userID = $userID;
		}
		else
		{
			throw new MyException(121101);
		}
	}
	function setLocationID($locationID)
	{
		if($locationID!='')
		{
			$this->locationID = $locationID;
		}
	}
	
	function setSaleItemID($saleItemID)
	{
		if($saleItemID!='')
		{
			$this->saleItemID = $saleItemID;
		}
		else
		{
			throw new MyException(121102);
		}
	}
	function setSaleItemDate($saleItemDate)
	{
		if($receiveItemDate!='')
		{
			$this->receiveItemDate = $receiveItemDate;
		}
	}
	function setStoreItemID($storeItemID)
	{
		if($storeItemID!='')
		{
			$this->storeItemID = $storeItemID;
		}
		else
		{
			throw new MyException(121103);
		}
	}
	function setExpiryDate($expiryDate)
	{
		if($expiryDate!='')
		{
			$this->expiryDate = $expiryDate;
		}
	}
	function setSaleQty($saleQty)
	{
		if($saleQty!='')
		{
			$this->saleQty = $saleQty;
		}
		else
		{
			throw new MyException(121104);
		}
	}
	
	function setStaffName($staffName)
	{
		if($staffName!='')
		{
			$this->staffName = $staffName;
		}
		else
		{
			throw new MyException(121105);
		}
	}
	function setDepartmentName($departmentName)
	{
		if($departmentName!='')
		{
			$this->departmentName = $departmentName;
		}
		else
		{
			throw new MyException(121106);
		}
	}
	function setDescription($description)
	{
		if($description!='')
		{
			$this->description = $description;
		}
	}
	
	
	function setDelivereByName($delivereByName)
	{
		if($delivereByName!='')
		{
			$this->delivereByName = $delivereByName;
		}
		else
		{
			throw new MyException(121116);
		}
	}
	function setDelivereByID($delivereByID)
	{
		if($delivereByID!='')
		{
			$this->delivereByID = $delivereByID;
		}
		else
		{
			throw new MyException(121117);
		}
	}
	function setDelivereDate($delivereDate)
	{
		if($delivereDate!='')
		{
			$this->delivereDate = $delivereDate;
		}
	}
	function setDelivereStatus($delivereStatus)
	{
		if($delivereStatus!='')
		{
			$this->delivereStatus = $delivereStatus;
		}
	}
	
	
	function setRefundStatus($refundStatus)
	{
		if($refundStatus!='')
		{
			$this->refundStatus = $refundStatus;
		}
	}
	function setRefundQty($refundQty)
	{
		if($refundQty!='')
		{
			$this->refundQty = $refundQty;
		}
		else
		{
			throw new MyException(121118);
		}
	}
	
	################################# GET FUNCTIONS ############################################
	
	function getSaleInvoiceID()
	{
		if($this->saleInvoiceID!='')
		{
			return $this->saleInvoiceID ;
		}
		else
		{
			throw new MyException(121107);
		}
	}
	function getUserID()
	{
		if($this->userID!='')
		{
			return $this->userID ;
		}
		else
		{
			throw new MyException(121108);
		}
	}
	function getLocationID()
	{
		if($this->locationID!='')
		{
			return $this->locationID ;
		}
	}
	
	function getSaleItemID()
	{
		if($this->saleItemID!='')
		{
			return $this->saleItemID ;
		}
		else
		{
			throw new MyException(121109);
		}
	}
	function getSaleItemDate()
	{
		if($this->receiveItemDate!='')
		{
			return $this->receiveItemDate ;
		}
	}
	function getStoreItemID()
	{
		if($this->storeItemID!='')
		{
			return $this->storeItemID ;
		}
		else
		{
			throw new MyException(121110);
		}
	}
	function getExpiryDate()
	{
		if($this->expiryDate!='')
		{
			return $this->expiryDate ;
		}
	}
	function getSaleQty()
	{
		if($this->saleQty!='')
		{
			return $this->saleQty ;
		}
		else
		{
			throw new MyException(121111);
		}
	}
	
	function getStaffName()
	{
		if($this->staffName!='')
		{
			return $this->staffName ;
		}
		else
		{
			throw new MyException(121112);
		}
	}
	function getDepartmentName()
	{
		if($this->departmentName!='')
		{
			return $this->departmentName ;
		}
		else
		{
			throw new MyException(121113);
		}
	}
	function getDescription()
	{
		if($this->description!='')
		{
			return $this->description;
		}
	}
	
	
	function getDelivereByName()
	{
		if($this->delivereByName!='')
		{
			return $this->delivereByName ;
		}
		else
		{
			throw new MyException(121119);
		}
	}
	function getDelivereByID()
	{
		if($this->delivereByID!='')
		{
			return $this->delivereByID ;
		}
		else
		{
			throw new MyException(121120);
		}
	}
	function getDelivereDate()
	{
		if($this->delivereDate!='')
		{
			return $this->delivereDate ;
		}
	}
	function getDelivereStatus()
	{
		if($this->delivereStatus!='')
		{
			return $this->delivereStatus ;
		}
	}
	
	function getRefundStatus()
	{
		if($this->refundStatus!='')
		{
			return $this->refundStatus;
		}
	}
	function getRefundQty()
	{
		if($this->refundQty!='')
		{
			return $this->refundQty;
		}
		else
		{
			throw new MyException(121121);
		}
	}
	
	################################# MAIN CHECK FUNCTIONS ############################################
	
	function checkItemExistInSaleInvoice($storeitem_id,$invoice_id)
	{
		$query = "SELECT saleitems_id FROM store_items_sale WHERE storeitem_id = '$storeitem_id' AND saleinvoice_id = '$invoice_id' ";
		$queryOb = new classQueryGlobal;
		$product_invoice_relationship = $queryOb->check_fetch_query($query,'select',$server="",$debug_mode="off");
		
		if(isset($product_invoice_relationship[0]['saleitems_id'])&&$product_invoice_relationship[0]['saleitems_id']!='')
		{
			return 0;
		}
		else
		{
			return 1;
		}
		
	}
	
	################################# MAIN INSERT FUNCTIONS ############################################
	
	function createSaleInvoice()
	{
		$saleinvoice_date  = time();
		$user_id = $this->getUserID();
		$location_id = $this->getLocationID();
		$staff_name = $this->getStaffName();
		$department_name = $this->getDepartmentName();
		$description = mysql_real_escape_string($this->getDescription());
		
		
		$query = "INSERT INTO store_items_saleinvoice 
		(
			saleinvoice_date,
			user_id,
			location_id,
			staff_name,
			department_name,
			description
		) 
		VALUES 
		(
			'$saleinvoice_date',
			'$user_id',
			'$location_id',
			'$staff_name',
			'$department_name',
			'$description'
		)";
		$queryOb = new classQueryGlobal;
		$this->saleInvoiceID = $queryOb->check_fetch_query($query,'insert',$server="",$debug_mode="off");
	}
	
	function addSaleItemsInInvoice()
	{
		if($this->checkItemExistInSaleInvoice($this->getStoreItemID(),$this->getSaleInvoiceID()))
		{
			$sale_date  = time();
			$saleinvoice_id = $this->getSaleInvoiceID();
			$storeitem_id = $this->getStoreItemID();
			
			$query = "INSERT INTO  store_items_sale (sale_date,saleinvoice_id,storeitem_id) VALUES ('$sale_date','$saleinvoice_id','$storeitem_id')";
			$queryOb = new classQueryGlobal;
			$this->saleItemID = $queryOb->check_fetch_query($query,'insert',$server="",$debug_mode="off");
		}
		else
		{
			throw new MyException(121114);
		}
	}
	
	################################# MAIN UPDATE FUNCTIONS #############################################
	
	function updateSaleItemsInInvoice()
	{
		$qty_sale = $this->getSaleQty();
		$saleitems_id = $this->getSaleItemID();
		$expiry_date = strtotime($this->getExpiryDate());
		$storeitem_id = $this->getStoreItemID();
		$location_id = $this->getLocationID();
		
		
		######## retrieve product unit quantity according to location detail ########
		
		$singleItemOb = new classStoreItem;
		$qty_in_stock = $singleItemOb->retrieveQtyInStock($storeitem_id,$location_id);
		
		######## retrieve old unit quantity ########
		
		$old_qty = $this->retrieveQtySaleByItemID();
		
		######## check total unit quantity ########
		
		$check_total_qty = $qty_in_stock+$old_qty;
		if($qty_sale>$check_total_qty)
		{
			$token = array('var1'=>$check_total_qty);
			throw new MyException(121115,$token);
		}
		
		$query = "UPDATE store_items_sale set qty_sale = '$qty_sale' WHERE saleitems_id = '$saleitems_id'";
		$queryOb = new classQueryGlobal;
		$queryOb->check_fetch_query($query,'update',$server="",$debug_mode="off");
		
		
		######## update unit quantity in stock ########
		$qty_in_stock = $qty_in_stock - $qty_sale + $old_qty;
		
		$singleItemLocaOb = new classStoreItem;
		$singleItemLocaOb->setStoreItemID($storeitem_id);
		$singleItemLocaOb->setLocationID($location_id);
		$singleItemLocaOb->setStoreItemQtyInStock($qty_in_stock);
		$singleItemLocaOb->updateQtyInStock();
		
	}
	
	function delivereSaleInvoice()
	{
		$saleinvoice_id = $this->getSaleInvoiceID();
		$delivere_by_name = $this->getDelivereByName();
		$delivere_by_id = $this->getDelivereByID();
		$delivere_date = time();
		$delivere_status = 'delivered';
		
		$query = "UPDATE store_items_saleinvoice set 
		 delivere_by_name='$delivere_by_name',
		 delivere_by_id='$delivere_by_id',
		 delivere_date='$delivere_date',
		 delivere_status='$delivere_status'
		 WHERE saleinvoice_id = '$saleinvoice_id'";
		$queryOb = new classQueryGlobal;
		$queryOb->check_fetch_query($query,'update',$server="",$debug_mode="off");
		
	}
	
	function refundSaleItemsInInvoice()
	{
		$saleitems_id = $this->getSaleItemID();
		$storeitem_id = $this->getStoreItemID();
		$refund_qty = $this->getRefundQty();
		$location_id = $this->getLocationID();
		
		######## retrieve item detail ########
		$single_item_detail = $this->retrieveInvoiceItemsByItemID();
		$saleinvoice_id = $single_item_detail[0]['saleinvoice_id'];
		
		$available_qty = $single_item_detail[0]['qty_sale']-$single_item_detail[0]['refund_qty'];
		
		if($refund_qty>0&&$refund_qty<=$available_qty)
		{
			$query = "UPDATE store_items_sale SET refund_status = 'refund' , refund_qty = refund_qty + '$refund_qty' where saleitems_id='$saleitems_id'";
			$queryOb = new classQueryGlobal;
			$queryOb->check_fetch_query($query,'update',$server="",$debug_mode="off");
			
			
			######## retrieve store unit quantity according to location detail ########
			
			$singleItemOb = new classStoreItem;
			$qty_in_stock = $singleItemOb->retrieveQtyInStock($storeitem_id,$location_id);
			
			######## update unit quantity in stock ########
			
			$qty_in_stock = $qty_in_stock + $refund_qty;
			
			$singleItemLocaOb = new classStoreItem;
			$singleItemLocaOb->setStoreItemID($storeitem_id);
			$singleItemLocaOb->setLocationID($location_id);
			$singleItemLocaOb->setStoreItemQtyInStock($qty_in_stock);
			$singleItemLocaOb->updateQtyInStock();
			
			######## insert in refund items ########
			
			$date = time();
			$query = "INSERT INTO store_items_refund (refund_time,saleinvoice_id,storeitem_id,qty_refund) VALUES ('$date', '$saleinvoice_id','$storeitem_id','$refund_qty')";
			$queryOb = new classQueryGlobal;
			$queryOb->check_fetch_query($query,'insert',$server="",$debug_mode="off");
			
		}
		else
		{
			throw new MyException(121122);
		}
		
		
		
		
	}
	
	################################# MAIN DELETE FUNCTIONS #############################################
	
	function deleteSaleItemsInInvoice()
	{
		$saleitems_id = $this->getSaleItemID();
		$saleitem_array = $this->retrieveInvoiceItemsByItemID();
		
		
		if(isset($saleitem_array[0]['saleitems_id'])&&$saleitem_array[0]['saleitems_id']!='') 
		{
			
			$this->saleInvoiceID = $saleitem_array[0]['saleinvoice_id'];
			$invoice_info = $this->retrieveInvoiceByID();
			
			$storeitem_id = $saleitem_array[0]['storeitem_id'];
			$location_id = $invoice_info[0]['location_id'];
			
			############## update product unit qty #########################
			

			$singleItemOb = new classStoreItem;
			$qty_in_stock = $singleItemOb->retrieveQtyInStock($storeitem_id,$location_id);
				
			$qty_in_stock = $qty_in_stock+$saleitem_array[0]['qty_sale'];
			
			$singleItemLocaOb = new classStoreItem;
			$singleItemLocaOb->setStoreItemID($storeitem_id);
			$singleItemLocaOb->setLocationID($location_id);
			$singleItemLocaOb->setStoreItemQtyInStock($qty_in_stock);
			$singleItemLocaOb->updateQtyInStock();
		
			
			############## Delete invoice items #########################
			
			$query = "DELETE FROM store_items_sale WHERE saleitems_id='$saleitems_id'";
			$queryOb = new classQueryGlobal;
			$queryOb->check_fetch_query($query,'delete',$server="",$debug_mode="off");
		}
		
	}
	
	function deleteSaleInvoice()
	{
		$saleinvoice_id = $this->getSaleInvoiceID();
		$invoice_info_array = $this->retrieveInvoiceItems();
		
		for($di=0;$di<count($invoice_info_array);$di++)
		{
			$this->saleItemID = $invoice_info_array[$di]['saleitems_id'];
			$this->deleteSaleItemsInInvoice();
		}
		############## Delete invoice #########################
		$query = "DELETE FROM store_items_saleinvoice WHERE saleinvoice_id='$saleinvoice_id'";
		$queryOb = new classQueryGlobal;
		$queryOb->check_fetch_query($query,'delete',$server="",$debug_mode="off");
		
	}
	
	################################# MAIN RETRIEVE FUNCTIONS ############################################
	
	function retrieveAllInvoices()
	{
		$location_id = $this->getLocationID();
		
		$query = "SELECT * FROM  store_items_saleinvoice WHERE location_id = '$location_id' ORDER BY saleinvoice_date DESC";
		$queryOb = new classQueryGlobal;
		$invoice_array = $queryOb->check_fetch_query($query,'select',$server="",$debug_mode="off");
		
		if(isset($invoice_array[0])&&$invoice_array[0]!='')
		{
			return $invoice_array;
		}
		
	}
	
	function retrieveInvoiceByID()
	{
		$saleinvoice_id = $this->getSaleInvoiceID();
		
		$query = "SELECT * FROM store_items_saleinvoice WHERE saleinvoice_id = '$saleinvoice_id'";
		$queryOb = new classQueryGlobal;
		$invoice_array = $queryOb->check_fetch_query($query,'select',$server="",$debug_mode="off");
		
		if(isset($invoice_array[0])&&$invoice_array[0]!='')
		{
			return $invoice_array;
		}
		
	}
	function retrieveInvoiceByInvoiceIDRanges($range,$location_id)
	{
		$range_array = explode('-',$range);
		if(isset($range_array[0])&&$range_array[0]!=''&&isset($range_array[1])&&$range_array[1]!='')
		{
			$min = $range_array[0];
			$max = $range_array[1];
		}
		else
		{
			$min = '';
			$max = '';
			$id = $range;
		}
		
		$query = "SELECT * FROM  store_items_saleinvoice WHERE location_id = '$location_id'";
		
		if($min!=''&&$max!='')
		{
			$query .= " AND saleinvoice_id BETWEEN $min and $max";
		}
		else
		{
			$query .= " AND saleinvoice_id = '$id'";
		}
		
		$query .= " ORDER BY saleinvoice_date DESC";
		
		$queryOb = new classQueryGlobal;
		$invoice_array = $queryOb->check_fetch_query($query,'select',$server="",$debug_mode="off");
		

		if(isset($invoice_array[0])&&$invoice_array[0]!='')
		{
			return $invoice_array;
		}
		
	}
	function retrieveInvoiceByDate($by_date,$location_id)
	{
		$query = "SELECT * FROM store_items_saleinvoice WHERE location_id = '$location_id'";
	
		$startDateArray = explode('-',$by_date);
		$startTime = mktime(0,0,0,$startDateArray[1],$startDateArray[2],$startDateArray[0]);
		
		$endDateArray = explode('-',$by_date);
		$endTime = mktime(23,59,59,$endDateArray[1],$endDateArray[2],$endDateArray[0]);
		
		if($startTime!="")
		{
			if(($endTime!="" && $endTime > $startTime) || $endTime="")
			{
				$query .= " AND saleinvoice_date  > $startTime";
			}
		}
		
		if($endTime!="")
		{
			if(($startTime!="" && $startTime < $endTime) || $startTime="")
			{
				$query .= " AND saleinvoice_date  <= $endTime";
			}
		}
		
		$queryOb = new classQueryGlobal;
		$invoice_array = $queryOb->check_fetch_query($query,'select',$server="",$debug_mode="off");
		
		if(isset($invoice_array[0])&&$invoice_array[0]!='')
		{
			return $invoice_array;
		}
		
	}
	
	function retrieveInvoiceItems()
	{
		$saleinvoice_id = $this->getSaleInvoiceID();
		
		$query = "SELECT * FROM  store_items_sale WHERE saleinvoice_id = '$saleinvoice_id'";
		$queryOb = new classQueryGlobal;
		$invoice_array = $queryOb->check_fetch_query($query,'select',$server="",$debug_mode="off");
		
		if(isset($invoice_array[0])&&$invoice_array[0]!='')
		{
			return $invoice_array;
		}
		
	}
	function retrieveInvoiceItemsAndQty()
	{
		$saleinvoice_id = $this->getSaleInvoiceID();
		
		$query = "SELECT storeitem_name , qty_sale FROM store_items_sale JOIN store_items ON store_items_sale.storeitem_id = store_items.storeitem_id  WHERE saleinvoice_id	 = '$saleinvoice_id	' ORDER BY storeitem_name DESC";
		$queryOb = new classQueryGlobal;
		$invoice_items_array = $queryOb->check_fetch_query($query,'select',$server="",$debug_mode="off");
		
		if(isset($invoice_items_array[0])&&$invoice_items_array[0]!='')
		{
			$store_items_names='';
			$store_items_qty='';
			
			for($ii=0;$ii<count($invoice_items_array);$ii++)
			{
				if($invoice_items_array[$ii]['storeitem_name'])
				{
					$store_items_names .= ucwords($invoice_items_array[$ii]['storeitem_name']).'<br/>';
				}
				if($invoice_items_array[$ii]['qty_sale'])
				{
					$store_items_qty .= ucwords($invoice_items_array[$ii]['qty_sale']).'<br/>';
				}
				
			}
			$store_items[0]['store_items_names']=$store_items_names;
			$store_items[0]['store_items_qty']=$store_items_qty;
			
			return $store_items;
		}
		
	}
	function retrieveInvoiceItemsByItemID()
	{
		$saleitems_id = $this->getSaleItemID();
		
		$query = "SELECT * FROM store_items_sale WHERE saleitems_id = '$saleitems_id'";
		$queryOb = new classQueryGlobal;
		$invoice_array = $queryOb->check_fetch_query($query,'select',$server="",$debug_mode="off");
		
		if(isset($invoice_array[0])&&$invoice_array[0]!='')
		{
			return $invoice_array;
		}
		
	}
	function retrieveQtySaleByItemID()
	{
		$saleitems_id = $this->getSaleItemID();
		
		$query = "SELECT qty_sale FROM store_items_sale WHERE saleitems_id = '$saleitems_id'";
		$queryOb = new classQueryGlobal;
		$invoice_array = $queryOb->check_fetch_query($query,'select',$server="",$debug_mode="off");
		
		if(isset($invoice_array[0]['qty_sale'])&&$invoice_array[0]['qty_sale']!='')
		{
			return $invoice_array[0]['qty_sale'];
		}
		
	}
	
	
	function sumOfInvoiceItemsByStoreItemID($storeitem_id,$location_id,$startTime,$endTime)
	{
		/*$month = date('m',$date);
		$year = date('Y',$date);
		$day = cal_days_in_month(CAL_GREGORIAN, $month, $year);
		
		$startTime = mktime(0,0,0,$month,1,$year);
		$endTime = mktime(23,59,59,$month,$day,$year);*/
		
		$query = "SELECT SUM(qty_sale) FROM store_items_sale JOIN store_items_saleinvoice ON  store_items_sale.saleinvoice_id = store_items_saleinvoice.saleinvoice_id WHERE store_items_sale.storeitem_id = '$storeitem_id' AND store_items_saleinvoice.location_id = '$location_id'"; 
		
		if($startTime!="")
		{
			if(($endTime!="" && $endTime > $startTime) || $endTime="")
			{
				$query .= " AND product_saleitems.sale_date  >= $startTime";
			}
		}
		
		if($endTime!="")
		{
			if(($startTime!="" && $startTime < $endTime) || $startTime="")
			{
				$query .= " AND product_saleitems.sale_date  <= $endTime";
			}
		}
		
		$queryOb = new classQueryGlobal;
		$invoice_array = $queryOb->check_fetch_query($query,'select',$server="",$debug_mode="off");
		
		if(isset($invoice_array[0])&&$invoice_array[0]!='')
		{
			return $invoice_array;
		}
	}
	
	
	
	################################# MAIN RETRIEVE REPORTS FUNCTIONS ############################################
	
	function productsSalesReport($from,$to,$location_id)
	{
		$query = "select product_id, sum(unitsaleqty), sum(net_total), unitretail_price from product_saleitems JOIN product_saleinvoice ON product_saleitems.saleinvoice_id = product_saleinvoice.saleinvoice_id WHERE product_saleinvoice.location_id = '$location_id'";
		
		$startDateArray = explode('-',$from);
		$startTime = mktime(0,0,0,$startDateArray[1],$startDateArray[2],$startDateArray[0]);
		
		$endDateArray = explode('-',$to);
		$endTime = mktime(23,59,59,$endDateArray[1],$endDateArray[2],$endDateArray[0]);
		
		if($startTime!="")
		{
			if(($endTime!="" && $endTime > $startTime) || $endTime="")
			{
				$query .= " AND sale_date  > $startTime";
			}
		}
		
		if($endTime!="")
		{
			if(($startTime!="" && $startTime < $endTime) || $startTime="")
			{
				$query .= " AND sale_date  <= $endTime";
			}
		}
		
		$query .= "  group by product_id order by product_id ASC ";
		
		$queryOb = new classQueryGlobal;
		$sale_report_array = $queryOb->check_fetch_query($query,'select',$server="",$debug_mode="off");
		
		if(isset($sale_report_array[0])&&$sale_report_array[0]!='')
		{
			return $sale_report_array;
		}
	}
	
	function productsInvoiceTotalSalesReport($from,$to,$location_id)
	{
		$query = "select sum(invoice_amount), sum(amount_payable),sum(payment),sum(balance) from product_saleinvoice  WHERE location_id = '$location_id'";
		
		$startDateArray = explode('-',$from);
		$startTime = mktime(0,0,0,$startDateArray[1],$startDateArray[2],$startDateArray[0]);
		
		$endDateArray = explode('-',$to);
		$endTime = mktime(23,59,59,$endDateArray[1],$endDateArray[2],$endDateArray[0]);
		
		if($startTime!="")
		{
			if(($endTime!="" && $endTime > $startTime) || $endTime="")
			{
				$query .= " AND saleinvoice_date  > $startTime";
			}
		}
		
		if($endTime!="")
		{
			if(($startTime!="" && $startTime < $endTime) || $startTime="")
			{
				$query .= " AND saleinvoice_date  <= $endTime";
			}
		}
		
		//$query .= "  group by product_id order by product_id ASC ";
		
		$queryOb = new classQueryGlobal;
		$sale_report_array = $queryOb->check_fetch_query($query,'select',$server="",$debug_mode="off");
		
		if(isset($sale_report_array[0])&&$sale_report_array[0]!='')
		{
			return $sale_report_array;
		}
	}
	
	function productsSalesDetailReport($from,$to,$location_id)
	{
		$query = "select product_id, product_saleitems.saleinvoice_id, unitretail_price, unitsaleqty, sale_date , net_total from product_saleitems JOIN product_saleinvoice ON product_saleitems.saleinvoice_id = product_saleinvoice.saleinvoice_id WHERE product_saleinvoice.location_id = '$location_id'";
		
		$startDateArray = explode('-',$from);
		$startTime = mktime(0,0,0,$startDateArray[1],$startDateArray[2],$startDateArray[0]);
		
		$endDateArray = explode('-',$to);
		$endTime = mktime(23,59,59,$endDateArray[1],$endDateArray[2],$endDateArray[0]);
		
		if($startTime!="")
		{
			if(($endTime!="" && $endTime > $startTime) || $endTime="")
			{
				$query .= " AND sale_date  > $startTime";
			}
		}
		
		if($endTime!="")
		{
			if(($startTime!="" && $startTime < $endTime) || $startTime="")
			{
				$query .= " AND sale_date  <= $endTime";
			}
		}
		
		$query .= "  order by product_id ASC ";
		
		$queryOb = new classQueryGlobal;
		$sale_report_array = $queryOb->check_fetch_query($query,'select',$server="",$debug_mode="off");
		
		if(isset($sale_report_array[0])&&$sale_report_array[0]!='')
		{
			return $sale_report_array;
		}
	}
	
	function invoiceWiseSalesReport($from,$to,$location_id)
	{
		$query = "select * from product_saleinvoice WHERE location_id = '$location_id'";
		
		$startDateArray = explode('-',$from);
		$startTime = mktime(0,0,0,$startDateArray[1],$startDateArray[2],$startDateArray[0]);
		
		$endDateArray = explode('-',$to);
		$endTime = mktime(23,59,59,$endDateArray[1],$endDateArray[2],$endDateArray[0]);
		
		if($startTime!="")
		{
			if(($endTime!="" && $endTime > $startTime) || $endTime="")
			{
				$query .= " AND saleinvoice_date  > $startTime";
			}
		}
		
		if($endTime!="")
		{
			if(($startTime!="" && $startTime < $endTime) || $startTime="")
			{
				$query .= " AND saleinvoice_date  <= $endTime";
			}
		}
		
		$query .= "  order by saleinvoice_date DESC ";
		
		$queryOb = new classQueryGlobal;
		$sale_report_array = $queryOb->check_fetch_query($query,'select',$server="",$debug_mode="off");
		
		if(isset($sale_report_array[0])&&$sale_report_array[0]!='')
		{
			return $sale_report_array;
		}
	}
	
	function productsSaleReportOfMonth($date,$location_id)
	{
		$query = "select product_id, sum(net_total) as sales_total, sum(unitsaleqty) as sales_qty 
		
		from product_saleitems s JOIN product_saleinvoice inv ON s.saleinvoice_id = inv.saleinvoice_id 
		
		WHERE inv.location_id = '$location_id'";
		
		$startDateArray = explode('-',$date);
		
		$month = date('m', strtotime($startDateArray[0]));
		$year = $startDateArray[1];
		$day = cal_days_in_month(CAL_GREGORIAN, $month, $year);
		
		$startTime = mktime(0,0,0,$month,1,$year);
		$endTime = mktime(23,59,59,$month,$day,$year);
		
		if($startTime!="")
		{
			if(($endTime!="" && $endTime > $startTime) || $endTime="")
			{
				$query .= " AND sale_date  >= $startTime";
			}
		}
		
		if($endTime!="")
		{
			if(($startTime!="" && $startTime < $endTime) || $startTime="")
			{
				$query .= " AND sale_date  <= $endTime";
			}
		}
		
		
		$query .= " GROUP BY product_id ORDER BY product_id ASC ";
		
		$queryOb = new classQueryGlobal;
		$sale_report_array = $queryOb->check_fetch_query($query,'select',$server="",$debug_mode="off");
		
		//if(isset($sale_report_array[0])&&$sale_report_array[0]!='')
		//{
			return $sale_report_array;
		//}
	}
	
	function singleProductSalesDetailReport($from,$to,$location_id,$product_id)
	{
		$query = "SELECT product_id , saleitems_id , product_saleitems.saleinvoice_id , product_saleinvoice.patient_id , retail_price , unitsaleqty , sale_date 
		FROM product_saleitems JOIN product_saleinvoice 
		ON product_saleitems.saleinvoice_id = product_saleinvoice.saleinvoice_id 
		WHERE product_id = '$product_id' AND product_saleinvoice.location_id = '$location_id'";
		
		$startDateArray = explode('-',$from);
		$startTime = mktime(0,0,0,$startDateArray[1],$startDateArray[2],$startDateArray[0]);
		
		$endDateArray = explode('-',$to);
		$endTime = mktime(23,59,59,$endDateArray[1],$endDateArray[2],$endDateArray[0]);
		
		if($startTime!="")
		{
			if(($endTime!="" && $endTime > $startTime) || $endTime="")
			{
				$query .= " AND sale_date  > $startTime";
			}
		}
		
		if($endTime!="")
		{
			if(($startTime!="" && $startTime < $endTime) || $startTime="")
			{
				$query .= " AND sale_date  <= $endTime";
			}
		}
		
		$query .= "  order by sale_date DESC ";
		
		$queryOb = new classQueryGlobal;
		$sale_report_array = $queryOb->check_fetch_query($query,'select',$server="",$debug_mode="off");
		
		//if(isset($sale_report_array[0])&&$sale_report_array[0]!='')
		//{
			return $sale_report_array;
		//}
	}
	
	function refundDetails($from,$to,$product_id)
	{
		$query = "SELECT * FROM product_refunditems WHERE product_id = '$product_id'";
		
		$startDateArray = explode('-',$from);
		$startTime = mktime(0,0,0,$startDateArray[1],$startDateArray[2],$startDateArray[0]);
		
		$endDateArray = explode('-',$to);
		$endTime = mktime(23,59,59,$endDateArray[1],$endDateArray[2],$endDateArray[0]);
		
		if($startTime!="")
		{
			if(($endTime!="" && $endTime > $startTime) || $endTime="")
			{
				$query .= " AND refund_time  > $startTime";
			}
		}
		
		if($endTime!="")
		{
			if(($startTime!="" && $startTime < $endTime) || $startTime="")
			{
				$query .= " AND refund_time  <= $endTime";
			}
		}
		$queryOb = new classQueryGlobal;
		$refund_array = $queryOb->check_fetch_query($query,'select',$server="",$debug_mode="off");
		
		return $refund_array;
	}
	
	
	
	
	
	
	
}
?>