<?php
class classSaleInvoice
{
	
	private $saleInvoiceID=NULL;
	private $userID=NULL;
	private $locationID=NULL;
	private $patientID=NULL;
	private $visitID=NULL;
	private $IPDPrescID=NULL;
	private $saleInvoiceAmount=NULL;
	private $discount=NULL;
	private $saleInvoiceAmountPayable=NULL;
	private $payment=NULL;
	private $balance=NULL;
	private $paymentStatus=NULL;
	
	private $saleItemID=NULL;
	private $saleItemDate=NULL;
	private $productID=NULL;
	private $productExpiryDate=NULL;
	private $productTradePrice=NULL;
	private $productRetailPrice=NULL;
	private $packingType=NULL;
	private $salePackingQty=NULL;
	private $saleUnitQty=NULL;
	private $saleUnitOldQty=NULL;
	private $netTotal=NULL;
	
	private $refundStatus=NULL;
	private $refundQty=NULL;
	private $refundAmount=NULL;
	
	################################# SET FUNCTIONS ############################################
	
	function setSaleInvoiceID($saleInvoiceID)
	{
		if($saleInvoiceID!='')
		{
			$this->saleInvoiceID = $saleInvoiceID;
		}
		else
		{
			throw new MyException(110100);
		}
	}
	function setUserID($userID)
	{
		if($userID!='')
		{
			$this->userID = $userID;
		}
		else
		{
			throw new MyException(110101);
		}
	}
	function setLocationID($locationID)
	{
		if($locationID!='')
		{
			$this->locationID = $locationID;
		}
		else
		{
			throw new MyException(110102);
		}
	}
	function setPatientID($patientID)
	{
		if($patientID!='')
		{
			$this->patientID = $patientID;
		}
		else
		{
			throw new MyException(110103);
		}
	}
	function setVisitID($visitID)
	{
		if($visitID!='')
		{
			$this->visitID = $visitID;
		}
		else
		{
			
		}
	}
	
	function setIPDPrescID($IPDPrescID)
	{
		if($IPDPrescID!='')
		{
			$this->IPDPrescID = $IPDPrescID;
		}
		else
		{
			
		}
	}
	function setSaleInvoiceAmount($saleInvoiceAmount)
	{
		if($saleInvoiceAmount!='')
		{
			$this->saleInvoiceAmount = $saleInvoiceAmount;
		}
		else
		{
			throw new MyException(110104);
		}
	}
	function setDiscount($discount)
	{
		if($discount!='')
		{
			$this->discount = $discount;
		}
		else
		{
			throw new MyException(110105);
		}
	}
	function setSaleInvoiceAmountPayable($saleInvoiceAmountPayable)
	{
		if($saleInvoiceAmountPayable!='')
		{
			$this->saleInvoiceAmountPayable = $saleInvoiceAmountPayable;
		}
		else
		{
			throw new MyException(110106);
		}
	}
	function setPayment($payment)
	{
		if($payment!='')
		{
			$this->payment = $payment;
		}
		else
		{
			throw new MyException(110107);
		}
	}
	function setBalance($balance)
	{
		if($balance!='')
		{
			$this->balance = $balance;
		}
		else
		{
			//throw new MyException(110108);
		}
	}
	function setPaymentStatus($paymentStatus)
	{
		if($paymentStatus!='')
		{
			$this->paymentStatus = $paymentStatus;
		}
		else
		{
			throw new MyException(110109);
		}
	}
	
	
	function setSaleItemID($saleItemID)
	{
		if($saleItemID!='')
		{
			$this->saleItemID = $saleItemID;
		}
		else
		{
			throw new MyException(110110);
		}
	}
	function setSaleItemDate($saleItemDate)
	{
		if($receiveItemDate!='')
		{
			$this->receiveItemDate = $receiveItemDate;
		}
		else
		{
			throw new MyException(110111);
		}
	}
	function setProductID($productID)
	{
		if($productID!='')
		{
			$this->productID = $productID;
		}
		else
		{
			throw new MyException(110112);
		}
	}
	function setProductExpiryDate($productExpiryDate)
	{
		if($productExpiryDate!='')
		{
			$this->productExpiryDate = $productExpiryDate;
		}
		
	}
	
	function setProductRetailPrice($productRetailPrice)
	{
		if($productRetailPrice!='')
		{
			$this->productRetailPrice = $productRetailPrice;
		}
		else
		{
			throw new MyException(110113);
		}
	}
	function setPackingType($packingType)
	{
		if($packingType!='')
		{
			$this->packingType = $packingType;
		}
		else
		{
			throw new MyException(110114);
		}
	}
	function setSalePackingQty($salePackingQty)
	{
		if($salePackingQty!='')
		{
			if($salePackingQty==0)
			{
				$this->salePackingQty = $salePackingQty;
			}
			else
			{
				if(classFilter::validateInteger($salePackingQty))
				{
					$this->salePackingQty = $salePackingQty;
				}
				else
				{
					throw new MyException(105147);	
				}
			}
		}
		else
		{
			throw new MyException(110144);
		}
	}
	function setSaleUnitQty($saleUnitQty)
	{
		if($saleUnitQty!='')
		{
			$this->saleUnitQty = $saleUnitQty;
		}
		else
		{
			throw new MyException(110116);
		}
	}
	function setSaleUnitOldQty($saleUnitOldQty)
	{
		if($saleUnitOldQty!='')
		{
			$this->saleUnitOldQty = $saleUnitOldQty;
		}
	}
	function setNetTotal($netTotal)
	{
		if($netTotal!='')
		{
			$this->netTotal = $netTotal;
		}
		else
		{
			throw new MyException(110117);
		}
	}
	
	function setRefundStatus($refundStatus)
	{
		if($refundStatus!='')
		{
			$this->refundStatus = $refundStatus;
		}
		else
		{
			throw new MyException(110118);
		}
	}
	function setRefundQty($refundQty)
	{
		if($refundQty!='')
		{
			$this->refundQty = $refundQty;
		}
		else
		{
			throw new MyException(110119);
		}
	}
	function setRefundAmount($refundAmount)
	{
		if($refundAmount!='')
		{
			$this->refundAmount = $refundAmount;
		}
		else
		{
			throw new MyException(110120);
		}
	}
	
	
	################################# GET FUNCTIONS ############################################
	
	function getSaleInvoiceID()
	{
		if($this->saleInvoiceID!='')
		{
			return $this->saleInvoiceID;
		}
		else
		{
			throw new MyException(110121);
		}
	}
	function getUserID()
	{
		if($this->userID!='')
		{
			return $this->userID;
		}
		else
		{
			throw new MyException(110122);
		}
	}
	function getLocationID()
	{
		if($this->locationID!='')
		{
			return $this->locationID;
		}
		else
		{
			throw new MyException(110123);
		}
	}
	function getPatientID()
	{
		if($this->patientID!='')
		{
			return $this->patientID;
		}
		else
		{
			throw new MyException(110124);
		}
	}
	function getVisitID()
	{
		if($this->visitID!='')
		{
			return $this->visitID;
		}
		else
		{
			
		}
	}
	
	function getIPDPrescID()
	{
		if($this->IPDPrescID!='')
		{
			return $this->IPDPrescID;
		}
		else
		{
			
		}
	}
	function getSaleInvoiceAmount()
	{
		if($this->saleInvoiceAmount!='')
		{
			return $this->saleInvoiceAmount;
		}
		else
		{
			throw new MyException(110125);
		}
	}
	function getDiscount()
	{
		if($this->discount!='')
		{
			return $this->discount;
		}
		else
		{
			throw new MyException(110126);
		}
	}
	function getSaleInvoiceAmountPayable()
	{
		if($this->saleInvoiceAmountPayable!='')
		{
			return $this->saleInvoiceAmountPayable;
		}
		else
		{
			throw new MyException(110127);
		}
	}
	function getPayment()
	{
		if($this->payment!='')
		{
			return $this->payment;
		}
		else
		{
			throw new MyException(110128);
		}
	}
	function getBalance()
	{
		if($this->balance!='')
		{
			return $this->balance;
		}
		else
		{
			//throw new MyException(110129);
		}
	}
	function getPaymentStatus()
	{
		if($this->paymentStatus!='')
		{
			return $this->paymentStatus;
		}
		else
		{
			throw new MyException(110130);
		}
	}
	
	
	function getSaleItemID()
	{
		if($this->saleItemID!='')
		{
			return $this->saleItemID;
		}
		else
		{
			throw new MyException(110131);
		}
	}
	function getSaleItemDate()
	{
		if($this->saleItemDate!='')
		{
			return $this->saleItemDate;
		}
		else
		{
			throw new MyException(110132);
		}
	}
	function getProductID()
	{
		if($this->productID!='')
		{
			return $this->productID;
		}
		else
		{
			throw new MyException(110133);
		}
	}
	function getProductExpiryDate()
	{
		if($this->productExpiryDate!='')
		{
			return $this->productExpiryDate;
		}
		
	}
	
	function getProductRetailPrice()
	{
		if($this->productRetailPrice!='')
		{
			return $this->productRetailPrice;
		}
		else
		{
			throw new MyException(110134);
		}
	}
	function getPackingType()
	{
		if($this->packingType!='')
		{
			return $this->packingType;
		}
		else
		{
			throw new MyException(110135);
		}
	}
	function getSalePackingQty()
	{
		if($this->salePackingQty!='')
		{
			return $this->salePackingQty;
		}
		else
		{
			throw new MyException(110136);
		}
	}
	function getSaleUnitQty()
	{
		if($this->saleUnitQty!='')
		{
			return $this->saleUnitQty;
		}
		else
		{
			throw new MyException(110137);
		}
	}
	function getSaleUnitOldQty()
	{
		if($this->saleUnitOldQty!='')
		{
			return $this->saleUnitOldQty;
		}
	}
	function getNetTotal()
	{
		if($this->netTotal!='')
		{
			return $this->netTotal;
		}
		else
		{
			throw new MyException(110138);
		}
	}

	function getRefundStatus()
	{
		if($this->refundStatus!='')
		{
			return $this->refundStatus;
		}
		else

		{
			throw new MyException(110139);
		}
	}
	function getRefundQty()
	{
		if($this->refundQty!='')
		{
			return $this->refundQty;
		}
		else
		{
			throw new MyException(110140);
		}
	}
	function getRefundAmount()
	{
		if($this->refundAmount!='')
		{
			return $this->refundAmount;
		}
		else
		{
			throw new MyException(110141);
		}
	}
	
	################################# MAIN CHECK FUNCTIONS ############################################
	
	function checkProductExistInSaleInvoice($product_id,$invoice_id)
	{
		$query = "SELECT saleitems_id FROM product_saleitems WHERE product_id = '$product_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();
		$patient_id = $this->getPatientID();
		$visit_id = $this->getVisitID();
		$ipd_presc_id = $this->getIPDPrescID();
		
		$query = "INSERT INTO product_saleinvoice (saleinvoice_date,user_id,location_id,patient_id,visit_id,ipd_presc_id) VALUES ('$saleinvoice_date','$user_id','$location_id','$patient_id','$visit_id','$ipd_presc_id')";
		$queryOb = new classQueryGlobal;
		$this->saleInvoiceID = $queryOb->check_fetch_query($query,'insert',$server="",$debug_mode="off");
	}
	
	function addSaleItemsInInvoice()
	{
		if($this->checkProductExistInSaleInvoice($this->getProductID(),$this->getSaleInvoiceID()))
		{
			$sale_date  = time();
			$saleinvoice_id = $this->getSaleInvoiceID();
			$product_id = $this->getProductID();
			
			$query = "INSERT INTO  product_saleitems (sale_date,saleinvoice_id,product_id) VALUES ('$sale_date','$saleinvoice_id','$product_id')";
			$queryOb = new classQueryGlobal;
			$this->saleItemID = $queryOb->check_fetch_query($query,'insert',$server="",$debug_mode="off");
		}
		else
		{
			throw new MyException(110142);
		}
	}
	
	################################# MAIN UPDATE FUNCTIONS #############################################
	
	function updateSaleInvoice()
	{
		$saleinvoice_id = $this->getSaleInvoiceID();
		$discount = $this->getDiscount();
		$invoice_amount = $this->getPayment();
		if($discount!='' && $discount!='0')
		{		
			$amount_payable = $invoice_amount-($invoice_amount*$discount/100);
		}
		else
		{
			$amount_payable = $invoice_amount;
		}
		
		$payment = 0;
		$balance = $amount_payable-$payment;
		
		
		$query = "UPDATE product_saleinvoice SET 
		 invoice_amount='$invoice_amount',
		 discount='$discount',
		 amount_payable='$amount_payable',
		 payment = '$payment',
		 balance = '$balance'
		 where saleinvoice_id = '$saleinvoice_id'";
		$queryOb = new classQueryGlobal;
		$queryOb->check_fetch_query($query,'update',$server="",$debug_mode="off");
	}
	
	function updateSaleInvoiceAfterTransaction()
	{
		$saleinvoice_id = $this->getSaleInvoiceID();
		$payment_status = $this->getPaymentStatus();
		$payment = $this->getPayment();
		$balance = $this->getBalance();
		
		
		$query = "UPDATE product_saleinvoice SET 
		 payment_status='$payment_status',
		 payment = '$payment',
		 balance = '$balance'
		 where saleinvoice_id = '$saleinvoice_id'";
		$queryOb = new classQueryGlobal;
		$queryOb->check_fetch_query($query,'update',$server="",$debug_mode="off");
	}
	
	function updateSaleItemsInInvoice()
	{
		$packing_type = $this->getPackingType();
		$packing_qty = $this->getSalePackingQty();
		$discount = $this->getDiscount();
		$saleitems_id = $this->getSaleItemID();
		$expiry_date = strtotime($this->getProductExpiryDate());
		$product_id = $this->getProductID();
		$patient_id = $this->getPatientID();
		$location_id = $this->getLocationID();
		
		
		######## retrieve product unit quantity according to location detail ########
		$singleProdOb = new classProduct;
		$prod_loc_relationship = $singleProdOb->retrieveProductLocationRelationship($product_id,$location_id);
		$unit_qtyinstock = $prod_loc_relationship[0]['unit_qtyinstock'];
		
		######## retrieve product detail ########
		$singleProdOb->setProductID($product_id);
		$prod_detail = $singleProdOb->retrieveProductByProductID();
		
		######## retrieve old unit quantity ########
		$single_item_detail = $this->retrieveInvoiceItemsDetailByItemID();
		$unitold_qty = $single_item_detail[0]['unitsaleqty'];
		
		######## calculate unit quantity ########
		if($packing_type=='Unit/Tablets') 
		{
			$retail_price = $prod_loc_relationship[0]['unit_retailprice'];
			
			$unitretail_price = $prod_loc_relationship[0]['unit_retailprice'];
			
			$total = $retail_price*$packing_qty;
			$net_total = $total-($total*$discount/100);
			$unitqty_sale = $packing_qty;
			
		} 
		elseif($packing_type=='Leaf') 
		{
			$retail_price = $prod_loc_relationship[0]['leaf_retailprice'];
			
			$unitretail_price = $prod_loc_relationship[0]['unit_retailprice'];
			
			$total = $retail_price*$packing_qty;
			$net_total = $total-($total*$discount/100);
			$unitqty_sale= $packing_qty*($prod_detail[0]['unitperbox']/$prod_detail[0]['leafperbox']);
			
		} 
		elseif($packing_type=='Box/Pack') 
		{
			$retail_price = $prod_loc_relationship[0]['box_retailprice'];
			
			$unitretail_price = $prod_loc_relationship[0]['unit_retailprice'];
			
			$total = $retail_price*$packing_qty;
			$net_total = $total-($total*$discount/100);
			$unitqty_sale= $packing_qty*($prod_detail[0]['unitperbox']);
			
		}
		else
		{
			$retail_price="0";
			$total="0";
			$net_total ="0";
			$unitqty_sale = "0";
		}
		
		######## check total unit quantity ########
		$check_total_qty = $unit_qtyinstock+$unitold_qty;
		if($unitqty_sale>$check_total_qty)
		{
			$token = array('var1'=>$check_total_qty);
			throw new MyException(110143,$token);
		}
		
	
		
		$query = "UPDATE product_saleitems set 
		 packing_type='$packing_type',
		 qty='$packing_qty',
		 unitsaleqty = '$unitqty_sale',
		 discount='$discount',
		 retail_price = '$retail_price',
		 unitretail_price = '$unitretail_price',
		 net_total='$net_total'
		 WHERE saleitems_id = '$saleitems_id'";
		$queryOb = new classQueryGlobal;
		$queryOb->check_fetch_query($query,'update',$server="",$debug_mode="off");
		
		
		######## update payemts in sale invoice ########
		$this->payment = $this->retrieveInvoiceItemsTotal();
		$invoice_info = $this->retrieveInvoiceDetail();
		$discount = $invoice_info[0]['discount'];
		if($discount==0)
		{
			$this->discount = '0';
		}
		else
		{
			$this->discount = $discount;
		}
		$this->updateSaleInvoice();
		
		######## update unit quantity in stock ########
		$unit_qtyinstock = $unit_qtyinstock - $unitqty_sale + $unitold_qty;
		
		$singleProdLocaOb = new classProduct;
		$singleProdLocaOb->setProductID($product_id);
		$singleProdLocaOb->setLocationID($location_id);
		$singleProdLocaOb->setProductUnitQtyInStock($unit_qtyinstock);
		$singleProdLocaOb->updateProductUnitQtyInStock();
		
	}
	
	function refundSaleItemsInInvoice()
	{
		$saleitems_id = $this->getSaleItemID();
		$product_id = $this->getProductID();
		$refund_qty = $this->getRefundQty();
		$patient_id = $this->getPatientID();
		$location_id = $this->getLocationID();
		
		######## retrieve item detail ########
		$single_item_detail = $this->retrieveInvoiceItemsDetailByItemID();
		$saleinvoice_id = $single_item_detail[0]['saleinvoice_id'];
		$discount = $single_item_detail[0]['discount'];
		$packing_type = $single_item_detail[0]['packing_type'];
		$available_qty = $single_item_detail[0]['qty']-$single_item_detail[0]['refund_qty'];
		$total = $single_item_detail[0]['retail_price']*$refund_qty;
		
		$refund_amount = $total-($total*$discount/100);
		
		if($refund_qty>0&&$refund_qty<=$available_qty)
		{
			$query = "UPDATE product_saleitems SET refund_status = 'refund' , refund_qty = refund_qty + '$refund_qty' , refund_amount = refund_amount + '$refund_amount' where saleitems_id='$saleitems_id'";
			$queryOb = new classQueryGlobal;
			$queryOb->check_fetch_query($query,'update',$server="",$debug_mode="off");
			
			
			$singleProdOb = new classProduct;
			
			######## retrieve product unit quantity according to location detail ########
			$prod_loc_relationship = $singleProdOb->retrieveProductLocationRelationship($product_id,$location_id);
			$unit_qtyinstock = $prod_loc_relationship[0]['unit_qtyinstock'];
			
			######## retrieve product detail ########
			
			$singleProdOb->setProductID($product_id);
			$prod_detail = $singleProdOb->retrieveProductByProductID();
			
			######## calculate unit quantity ########
			
			if($packing_type=='Unit/Tablets') 
			{
				$unitqty_recieved = $refund_qty;
			} 
			elseif($packing_type=='Leaf') 
			{
				$unitqty_recieved= $refund_qty*($prod_detail[0]['unitperbox']/$prod_detail[0]['leafperbox']);
			} 
			elseif($packing_type=='Box/Pack') 
			{
				$unitqty_recieved= $refund_qty*($prod_detail[0]['unitperbox']);
			}
			
			######## update unit quantity in stock ########
			
			$unit_qtyinstock = $unit_qtyinstock + $unitqty_recieved;
			
			$singleProdLocaOb = new classProduct;
			$singleProdLocaOb->setProductID($product_id);
			$singleProdLocaOb->setLocationID($location_id);
			$singleProdLocaOb->setProductUnitQtyInStock($unit_qtyinstock);
			$singleProdLocaOb->updateProductUnitQtyInStock();
			
			######## insert in refund items ########
			
			$date = time();
			$query = "INSERT INTO product_refunditems (refund_time,saleinvoice_id,product_id,packing_type,qty,refund_amount) VALUES ('$date', '$saleinvoice_id','$product_id','$packing_type','$refund_qty','$refund_amount')";
			$queryOb = new classQueryGlobal;
			$queryOb->check_fetch_query($query,'insert',$server="",$debug_mode="off");
			
		}
		else
		{
			throw new MyException(110145);
		}
		
		
		
		
	}
	
	################################# MAIN DELETE FUNCTIONS #############################################
	
	function deleteSaleItemsInInvoice()
	{
		$saleitems_id = $this->getSaleItemID();
		$saleitem_array = $this->retrieveInvoiceItemsDetailByItemID();
		
		
		if(isset($saleitem_array[0]['saleitems_id'])&&$saleitem_array[0]['saleitems_id']!='') 
		{
			$product_id = $saleitem_array[0]['product_id'];
			
			$this->saleInvoiceID = $saleitem_array[0]['saleinvoice_id'];
			$invoice_info = $this->retrieveInvoiceDetail();
			
			$location_id = $invoice_info[0]['location_id'];
			
			############## update product unit qty #########################
			

			$retrieveSingleProdOb = new classProduct;
			$prod_detail = $retrieveSingleProdOb->retrieveProductLocationRelationship($product_id,$location_id);
				
			$unitqty_sale = $prod_detail[0]['unit_qtyinstock']+$saleitem_array[0]['unitsaleqty'];
			
			$updateSingleProdOb = new classProduct;
			$updateSingleProdOb->setProductID($product_id);
			$updateSingleProdOb->setLocationID($location_id);
			$updateSingleProdOb->setProductUnitQtyInStock($unitqty_sale);
			$updateSingleProdOb->updateProductUnitQtyInStock();
			
			############## update invoice amount #########################
			
			
			$payment = $invoice_info[0]['invoice_amount']-$saleitem_array[0]['net_total'];
			if($payment==0)
			{
				$this->payment = "0";
			}
			else
			{
				$this->payment = $payment;
			}
			
			$this->discount = $invoice_info[0]['discount'];
			$this->updateSaleInvoice();
			
			
			############## Delete invoice items #########################
			$query = "DELETE FROM product_saleitems 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->retrieveInvoiceItemsDetail();
		
		for($di=0;$di<count($invoice_info_array);$di++)
		{
			$this->saleItemID = $invoice_info_array[$di]['saleitems_id'];
			$this->deleteSaleItemsInInvoice();
		}
		############## Delete invoice #########################
		$query = "DELETE FROM product_saleinvoice WHERE saleinvoice_id='$saleinvoice_id'";
		$queryOb = new classQueryGlobal;
		$queryOb->check_fetch_query($query,'delete',$server="",$debug_mode="off");
		
	}
	
	################################# MAIN RETRIEVE FUNCTIONS ############################################
	
	function retrieveInvoiceDetail()
	{
		$saleinvoice_id = $this->getSaleInvoiceID();
		
		$query = "SELECT * FROM product_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 retrieveInvoiceDetailByInvoiceIDRanges($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  product_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 retrieveInvoiceDetailByLocationID()
	{
		$location_id = $this->getLocationID();
		
		$query = "SELECT * FROM  product_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 retrieveInvoiceDetailByPatientID()
	{
		$patient_id = $this->getPatientID();
		$query = "SELECT * FROM  product_saleinvoice WHERE patient_id = '$patient_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 retrieveInvoiceDetailByPatientIDRanges($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  product_saleinvoice WHERE location_id = '$location_id'";
		
		if($min!=''&&$max!='')
		{
			$query .= " AND patient_id BETWEEN $min and $max";
		}
		else
		{
			$query .= " AND patient_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 retrieveInvoiceDetailByVisitID($visit_id)
	{
		
		$query = "SELECT * FROM  product_saleinvoice WHERE visit_id = '$visit_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 retrieveInvoiceDetailByIPDPrescID($ipd_presc_id)
	{
		
		$query = "SELECT * FROM  product_saleinvoice WHERE ipd_presc_id = '$ipd_presc_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 retrieveInvoiceDetailHavingBalanceByLocationID()
	{
		$location_id = $this->getLocationID();
		
		$query = "SELECT * FROM  product_saleinvoice WHERE location_id = '$location_id' AND balance != 0  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 countInvoiceDetailPaidByLocationID()
	{
		$location_id = $this->getLocationID();
		
		$query = "SELECT count(*) FROM  product_saleinvoice WHERE location_id = '$location_id' AND payment_status ='paid'";
		$queryOb = new classQueryGlobal;
		$invoice_array = $queryOb->check_fetch_query($query,'select',$server="",$debug_mode="off");
		
		if(isset($invoice_array[0]['count(*)'])&&$invoice_array[0]['count(*)']!='')
		{
			return $invoice_array[0]['count(*)'];
		}
		
	}
	function retrieveInvoiceDetailPaidByLocationID($limit=NULL)
	{
		$location_id = $this->getLocationID();
		
		$query = "SELECT * FROM  product_saleinvoice WHERE location_id = '$location_id' AND payment_status ='paid'  ORDER BY saleinvoice_date DESC";
		if(!is_null($limit))
		{
			$query .= " LIMIT $limit";
		}
		$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 retrieveInvoiceDetailHavingBalanceByDate($by_date,$location_id)
	{
		$query = "SELECT * FROM product_saleinvoice WHERE location_id = '$location_id' AND balance != 0";
	
		$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 retrieveInvoiceDetailByDate($by_date,$location_id)
	{
		$query = "SELECT * FROM product_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 retrieveInvoiceItemsDetail()
	{
		$saleinvoice_id = $this->getSaleInvoiceID();
		
		$query = "SELECT * FROM  product_saleitems 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 retrieveInvoiceItemsTotal()
	{
		$saleinvoice_id = $this->getSaleInvoiceID();
		
		$query = "SELECT sum(net_total) FROM product_saleitems 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]['sum(net_total)'])&&$invoice_array[0]['sum(net_total)']!='')
		{
			return $invoice_array[0]['sum(net_total)'];
		}
		
	}
	
	function retrieveInvoiceItemsDetailByItemID()
	{
		$saleitems_id = $this->getSaleItemID();
		
		$query = "SELECT * FROM product_saleitems 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 retrieveSumOfInvoiceItemsByProductID($product_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(unitsaleqty) , SUM(net_total) FROM product_saleitems JOIN product_saleinvoice ON  product_saleitems.saleinvoice_id = product_saleinvoice.saleinvoice_id WHERE product_saleitems. product_id = '$product_id' AND product_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;
		}
	}
	
	function retrieveSumOfRefundItems()
	{
		$saleinvoice_id = $this->getSaleInvoiceID();
		$query = "SELECT sum(refund_amount) FROM product_saleitems where saleinvoice_id='$saleinvoice_id'"; 
		$queryOb = new classQueryGlobal;
		$refund_array = $queryOb->check_fetch_query($query,'select',$server="",$debug_mode="off");
		
		if(isset($refund_array[0]['sum(refund_amount)'])&&$refund_array[0]['sum(refund_amount)']!='')
		{
			return $refund_array[0]['sum(refund_amount)'];
		}
	}
	
	function retrieveSumOfInvoiceRefundItemsByProductID($product_id,$startTime,$endTime)
	{
		
		$query = "SELECT SUM(qty) , SUM(refund_amount) FROM product_refunditems  WHERE product_id = '$product_id' AND saleinvoice_id!=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;
		$invoice_array = $queryOb->check_fetch_query($query,'select',$server="",$debug_mode="off");
		
		if(isset($invoice_array[0])&&$invoice_array[0]!='')
		{
			return $invoice_array;
		}
	}
	
	function retrievePatientIDOfInvoice($saleinvoice_id)
	{
		$query = "SELECT patient_id FROM product_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]['patient_id'])&&$invoice_array[0]['patient_id']!='')
		{
			return $invoice_array[0]['patient_id'];
		}
	}
	
	################################# 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;
	}
	
	
	
	
	
	
	
}
?>