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

class Reports_model extends CI_Model {

	public function show_supplier_items_report(){
		extract($_POST);

		if(!empty($store_id)){
			$this->db->where("store_id",$store_id);
		}
		if($item_id!=''){
			$this->db->where("id=$item_id");
		}
		$q1 = $this->db->select("*")->from("db_items");
		
		$q1=$this->db->get();
		if($q1->num_rows()>0){
			$i=0;
			$tot_grand_total=0;
			$tot_paid_amount=0;
			$tot_due_amount=0;
			foreach ($q1->result() as $res1) {
				

				$this->db->select("a.unit_total_cost,b.store_id,b.warehouse_id, b.id,a.item_id,b.supplier_id,b.purchase_code,b.purchase_date");
				$this->db->from("db_purchaseitems a");
				$this->db->where("a.item_id",$res1->id);
				$this->db->from("db_purchase b");
				$this->db->where("a.purchase_id=b.id");
				

				if($supplier_id!=''){
					$this->db->where("b.supplier_id=$supplier_id");
				}
				if(!empty($store_id)){
					$this->db->where("b.store_id",$store_id);
				}
				$this->db->order_by("a.id","desc");
				//$this->db->limit(1);

				$q2=$this->db->get();
				if($q2->num_rows()>0){
					foreach ($q2->result() as $res2){
						$supplier_name = $this->db->select("supplier_name")->from("db_suppliers")->where("id",$res2->supplier_id)->get()->row()->supplier_name;
						
						$q3 = $this->db->select("*")->from("db_items")->where("id",$res2->item_id)->get()->row();
						$item_code = $q3->item_code;
						$item_name = $q3->item_name;

						echo "<tr>";
						echo "<td>".++$i."</td>";
						
						if(store_module() && is_admin()){
							echo "<td>".get_store_name($res2->store_id)."</td>";	
						}
						if(warehouse_module() && warehouse_count()>0){
							echo "<td>".get_warehouse_name($res2->warehouse_id)."</td>";	
						}
						echo "<td><a title='View Invoice' href='".base_url("purchase/invoice/$res2->id")."'>".$res2->purchase_code."</a></td>";
						echo "<td>".show_date($res2->purchase_date)."</td>";
						echo "<td>".$supplier_name."</td>";
						echo "<td>".$item_code."</td>";
						echo "<td>".$item_name."</td>";
						echo "<td class='text-right'>".store_number_format($res2->unit_total_cost)."</td>";
						echo "</tr>";

					}
				}


			}

		}
		else{
			echo "<tr>";
			echo "<td class='text-center text-danger' colspan=8>No Records Found</td>";
			echo "</tr>";
		}
		
		exit;
	}

	public function show_sales_report(){
		extract($_POST);

		/*$from_date=date("Y-m-d",strtotime($from_date));
		$to_date=date("Y-m-d",strtotime($to_date));*/
		
		$from_date = system_fromatted_date($from_date);
		$to_date = system_fromatted_date($to_date);


		if(warehouse_module() && warehouse_count()>0 && !empty($warehouse_id)){
				$this->db->where("a.warehouse_id",$warehouse_id);
		}

		$this->db->select("a.id,a.warehouse_id,a.sales_code,a.sales_date,b.customer_name,b.customer_code,a.grand_total,a.paid_amount,a.store_id,a.created_by");
		
		if($customer_id!=''){
			
			$this->db->where("a.customer_id=$customer_id");
		}
		if($view_all=="no"){
			$this->db->where("(a.sales_date>='$from_date' and a.sales_date<='$to_date')");
		}
		$this->db->where("b.`id`= a.`customer_id`");
		$this->db->from("db_sales as a");
		$this->db->where("a.`sales_status`= 'Final'");

		if(!empty($created_by)){
			$this->db->where("upper(a.created_by)=upper('$created_by')");
		}
		
		if(!empty($store_id)){
			$this->db->where("a.store_id",$store_id);
		}
		
		$this->db->from("db_customers as b");
		if($show_account_receivable==1){
			$this->db->where("a.grand_total!=a.paid_amount");
		}
		
		//echo $this->db->get_compiled_select();exit();
		
		$q1=$this->db->get();
		if($q1->num_rows()>0){
			$i=0;
			$tot_grand_total=0;
			$tot_paid_amount=0;
			$tot_due_amount=0;
			foreach ($q1->result() as $res1) {
				echo "<tr>";
				echo "<td>".++$i."</td>";
				if(store_module() && is_admin()){
					echo "<td>".get_store_name($res1->store_id)."</td>";	
				}
				if(warehouse_module() && warehouse_count()>0){
					echo "<td>".get_warehouse_name($res1->warehouse_id)."</td>";	
				}
				if($store_id==get_current_store_id()){
				echo "<td><a title='View Invoice' href='".base_url("sales/invoice/$res1->id")."'>".$res1->sales_code."</a></td>";
				}
				else{
				echo "<td>".$res1->sales_code."</td>";	
				}
				echo "<td>".show_date($res1->sales_date)."</td>";
				echo "<td>".$res1->customer_code."</td>";
				echo "<td>".$res1->customer_name."</td>";
				echo "<td class='text-right'>".store_number_format($res1->grand_total)."</td>";
				echo "<td class='text-right'>".store_number_format($res1->paid_amount)."</td>";
				echo "<td class='text-right'>".store_number_format(($res1->grand_total-$res1->paid_amount))."</td>";
				echo "<td>".$res1->created_by."</td>";
				echo "</tr>";
				$tot_grand_total+=$res1->grand_total;
				$tot_paid_amount+=$res1->paid_amount;
				$tot_due_amount+=($res1->grand_total-$res1->paid_amount);

			}

			$total_columns_count=5;
			if(store_module() && is_admin()){
				$total_columns_count ++;
			}
			if(warehouse_module() && warehouse_count()>0){
				$total_columns_count ++;
			}
			echo "<tr>
					  <td class='text-right text-bold' colspan='$total_columns_count'><b>Total :</b></td>
					  <td class='text-right text-bold'>".store_number_format($tot_grand_total)."</td>
					  <td class='text-right text-bold'>".store_number_format($tot_paid_amount)."</td>
					  <td class='text-right text-bold'>".store_number_format($tot_due_amount)."</td>
					  <td></td>
				  </tr>";
		}
		else{
			$total_columns_count=9;
			if(store_module() && is_admin()){
				$total_columns_count ++;
			}
			if(warehouse_module() && warehouse_count()>0){
				$total_columns_count ++;
			}
			echo "<tr>";
			echo "<td class='text-center text-danger' colspan='$total_columns_count'>No Records Found</td>";
			echo "</tr>";
		}
		
		exit;
	}

	public function show_sales_return_report(){
		extract($_POST);

		/*$from_date=date("Y-m-d",strtotime($from_date));
		$to_date=date("Y-m-d",strtotime($to_date));*/
		
		$from_date = system_fromatted_date($from_date);
		$to_date = system_fromatted_date($to_date);

		if(warehouse_module() && warehouse_count()>0 && !empty($warehouse_id)){
			$this->db->where("a.warehouse_id",$warehouse_id);
		}

		$this->db->select("a.id,a.warehouse_id,a.return_code,a.return_date,b.customer_name,b.customer_code,a.grand_total,a.paid_amount,a.store_id");
		
		if($customer_id!=''){
			
			$this->db->where("a.customer_id=$customer_id");
		}
		if($view_all=="no"){
			$this->db->where("(a.return_date>='$from_date' and a.return_date<='$to_date')");
		}
		$this->db->where("b.`id`= a.`customer_id`");
		$this->db->from("db_salesreturn as a");
		$this->db->from("db_customers as b");
		$this->db->select("CASE WHEN c.sales_code IS NULL THEN '' ELSE c.sales_code END AS sales_code");
		$this->db->join('db_sales as c','c.id=a.sales_id','left');
		
		
		if(!empty($store_id)){
			$this->db->where("a.store_id",$store_id);
		}
		//echo $this->db->get_compiled_select();exit();
		
		$q1=$this->db->get();
		if($q1->num_rows()>0){
			$i=0;
			$tot_grand_total=0;
			$tot_paid_amount=0;
			$tot_due_amount=0;
			foreach ($q1->result() as $res1) {
				echo "<tr>";
				echo "<td>".++$i."</td>";
				if(store_module() && is_admin()){
					echo "<td>".get_store_name($res1->store_id)."</td>";	
				}
				if(warehouse_module() && warehouse_count()>0){
					echo "<td>".get_warehouse_name($res1->warehouse_id)."</td>";	
				}
				if($store_id==get_current_store_id()){
				echo "<td><a title='View Invoice' href='".base_url("sales_return/invoice/$res1->id")."'>".$res1->return_code."</a></td>";
				}
				else{
				echo "<td>".$res1->return_code."</td>";	
				}

				
				echo "<td>".show_date($res1->return_date)."</td>";
				
				echo (!empty($res1->sales_code)) ? "<td><a title='Return Raised Against this Invoice' href='".base_url("sales/invoice/$res1->id")."'>".$res1->sales_code."</a></td>" : '<td>-NA-</td>';
				echo "<td>".$res1->customer_name."</td>";
				echo "<td class='text-right'>".store_number_format($res1->grand_total)."</td>";
				echo "<td class='text-right'>".store_number_format($res1->paid_amount)."</td>";
				echo "<td class='text-right'>".store_number_format(($res1->grand_total-$res1->paid_amount))."</td>";
				echo "</tr>";
				$tot_grand_total+=$res1->grand_total;
				$tot_paid_amount+=$res1->paid_amount;
				$tot_due_amount+=($res1->grand_total-$res1->paid_amount);

			}

			$total_columns_count=5;
			if(store_module() && is_admin()){
				$total_columns_count ++;
			}
			if(warehouse_module() && warehouse_count()>0){
				$total_columns_count ++;
			}
			echo "<tr>
					  <td class='text-right text-bold' colspan='$total_columns_count'><b>Total :</b></td>
					  <td class='text-right text-bold'>".store_number_format($tot_grand_total)."</td>
					  <td class='text-right text-bold'>".store_number_format($tot_paid_amount)."</td>
					  <td class='text-right text-bold'>".store_number_format($tot_due_amount)."</td>
				  </tr>";
		}
		else{
			$total_columns_count=8;
			if(store_module() && is_admin()){
				$total_columns_count ++;
			}
			if(warehouse_module() && warehouse_count()>0){
				$total_columns_count ++;
			}
			echo "<tr>";
			echo "<td class='text-center text-danger' colspan='$total_columns_count'>No Records Found</td>";
			echo "</tr>";
		}
		
		exit;
	}

	public function show_purchase_report(){
		extract($_POST);
		
		/*$from_date=date("Y-m-d",strtotime($from_date));
		$to_date=date("Y-m-d",strtotime($to_date));*/
		
		$from_date = system_fromatted_date($from_date);
		$to_date = system_fromatted_date($to_date);

		if(warehouse_module() && warehouse_count()>0 && !empty($warehouse_id)){
			$this->db->where("a.warehouse_id",$warehouse_id);
		}

		$this->db->select("a.id,a.warehouse_id,a.purchase_code,a.purchase_date,b.supplier_name,b.supplier_code,a.grand_total,a.paid_amount,a.store_id");
		
		if($supplier_id!=''){
			$this->db->where("a.supplier_id=$supplier_id");
		}
		if($view_all=="no"){
			$this->db->where("(a.purchase_date>='$from_date' and a.purchase_date<='$to_date')");
		}
		$this->db->where("b.`id`= a.`supplier_id`");
		$this->db->from("db_purchase as a");
		$this->db->where("a.`purchase_status`= 'Received'");
		$this->db->from("db_suppliers as b");
		
		
		if(!empty($store_id)){
			$this->db->where("a.store_id",$store_id);
		}
		if($show_account_payble==1){
			$this->db->where("a.grand_total!=a.paid_amount");
		}
		//echo $this->db->get_compiled_select();
		
		$q1=$this->db->get();
		if($q1->num_rows()>0){
			$i=0;
			$tot_grand_total=0;
			$tot_paid_amount=0;
			$tot_due_amount=0;
			foreach ($q1->result() as $res1) {
				echo "<tr>";
				echo "<td>".++$i."</td>";
				if(store_module() && is_admin()){
					echo "<td>".get_store_name($res1->store_id)."</td>";	
				}
				if(warehouse_module() && warehouse_count()>0){
					echo "<td>".get_warehouse_name($res1->warehouse_id)."</td>";	
				}
				if($store_id==get_current_store_id()){
				echo "<td><a title='View Invoice' href='".base_url("purchase/invoice/$res1->id")."'>".$res1->purchase_code."</a></td>";
				}
				else{
				echo "<td>".$res1->purchase_code."</td>";	
				}

				
				echo "<td>".show_date($res1->purchase_date)."</td>";
				echo "<td>".$res1->supplier_code."</td>";
				echo "<td>".$res1->supplier_name."</td>";
				echo "<td class='text-right'>".store_number_format($res1->grand_total)."</td>";
				echo "<td class='text-right'>".store_number_format($res1->paid_amount)."</td>";
				echo "<td class='text-right'>".store_number_format(($res1->grand_total-$res1->paid_amount))."</td>";
				echo "</tr>";
				$tot_grand_total+=$res1->grand_total;
				$tot_paid_amount+=$res1->paid_amount;
				$tot_due_amount+=($res1->grand_total-$res1->paid_amount);

			}
			$total_columns_count=5;
			if(store_module() && is_admin()){
				$total_columns_count ++;
			}
			if(warehouse_module() && warehouse_count()>0){
				$total_columns_count ++;
			}
			echo "<tr>
					  <td class='text-right text-bold' colspan='$total_columns_count'><b>Total :</b></td>
					  <td class='text-right text-bold'>".store_number_format($tot_grand_total)."</td>
					  <td class='text-right text-bold'>".store_number_format($tot_paid_amount)."</td>
					  <td class='text-right text-bold'>".store_number_format($tot_due_amount)."</td>
				  </tr>";
		}
		else{
			$total_columns_count=8;
			if(store_module() && is_admin()){
				$total_columns_count ++;
			}
			if(warehouse_module() && warehouse_count()>0){
				$total_columns_count ++;
			}
			echo "<tr>";
			echo "<td class='text-center text-danger' colspan='$total_columns_count'>No Records Found</td>";
			echo "</tr>";
		}
		
		exit;
	}

	public function show_purchase_return_report(){
		extract($_POST);
		
		/*$from_date=date("Y-m-d",strtotime($from_date));
		$to_date=date("Y-m-d",strtotime($to_date));*/
		
		$from_date = system_fromatted_date($from_date);
		$to_date = system_fromatted_date($to_date);

		if(warehouse_module() && warehouse_count()>0 && !empty($warehouse_id)){
			$this->db->where("a.warehouse_id",$warehouse_id);
		}
		
		$this->db->select("a.id,a.warehouse_id,a.return_code,a.return_date,b.supplier_name,a.grand_total,a.paid_amount,a.store_id");
		
		if($supplier_id!=''){
			$this->db->where("a.supplier_id=$supplier_id");
		}
		if($view_all=="no"){
			$this->db->where("(a.return_date>='$from_date' and a.return_date<='$to_date')");
		}
		$this->db->where("b.`id`= a.`supplier_id`");
		$this->db->from("db_purchasereturn as a");
		$this->db->from("db_suppliers as b");
		$this->db->select("CASE WHEN c.purchase_code IS NULL THEN '' ELSE c.purchase_code END AS purchase_code");
		$this->db->join('db_purchase as c','c.id=a.purchase_id','left');

		
		
		if(!empty($store_id)){
			$this->db->where("a.store_id",$store_id);
		}
		//echo $this->db->get_compiled_select();
		
		$q1=$this->db->get();
		if($q1->num_rows()>0){
			$i=0;
			$tot_grand_total=0;
			$tot_paid_amount=0;
			$tot_due_amount=0;
			foreach ($q1->result() as $res1) {
				echo "<tr>";
				echo "<td>".++$i."</td>";
				if(store_module() && is_admin()){
					echo "<td>".get_store_name($res1->store_id)."</td>";	
				}
				if(warehouse_module() && warehouse_count()>0){
					echo "<td>".get_warehouse_name($res1->warehouse_id)."</td>";	
				}
				if($store_id==get_current_store_id()){
				echo "<td><a title='View Invoice' href='".base_url("purchase_return/invoice/$res1->id")."'>".$res1->return_code."</a></td>";
				}
				else{
				echo "<td>".$res1->return_code."</td>";	
				}

				
				echo "<td>".show_date($res1->return_date)."</td>";
				echo (!empty($res1->purchase_code)) ? "<td><a title='Return Raised Against this Invoice' href='".base_url("purchase/invoice/$res1->id")."'>".$res1->purchase_code."</a></td>" : '<td>-NA-</td>';
				
				echo "<td>".$res1->supplier_name."</td>";
				echo "<td class='text-right'>".store_number_format($res1->grand_total)."</td>";
				echo "<td class='text-right'>".store_number_format($res1->paid_amount)."</td>";
				echo "<td class='text-right'>".store_number_format(($res1->grand_total-$res1->paid_amount))."</td>";
				echo "</tr>";
				$tot_grand_total+=$res1->grand_total;
				$tot_paid_amount+=$res1->paid_amount;
				$tot_due_amount+=($res1->grand_total-$res1->paid_amount);

			}
			$total_columns_count=5;
			if(store_module() && is_admin()){
				$total_columns_count ++;
			}
			if(warehouse_module() && warehouse_count()>0){
				$total_columns_count ++;
			}
			echo "<tr>
					  <td class='text-right text-bold' colspan='$total_columns_count'><b>Total :</b></td>
					  <td class='text-right text-bold'>".store_number_format($tot_grand_total)."</td>
					  <td class='text-right text-bold'>".store_number_format($tot_paid_amount)."</td>
					  <td class='text-right text-bold'>".store_number_format($tot_due_amount)."</td>
				  </tr>";
		}
		else{
			$total_columns_count=8;
			if(store_module() && is_admin()){
				$total_columns_count ++;
			}
			if(warehouse_module() && warehouse_count()>0){
				$total_columns_count ++;
			}
			echo "<tr>";
			echo "<td class='text-center text-danger' colspan='$total_columns_count'>No Records Found</td>";
			echo "</tr>";
		}
		
		exit;
	}
	
	public function show_expense_report(){
		extract($_POST);
		/*$from_date=date("Y-m-d",strtotime($from_date));
		$to_date=date("Y-m-d",strtotime($to_date));*/

		$from_date = system_fromatted_date($from_date);
		$to_date = system_fromatted_date($to_date);

		/*$q1=$this->db->query("SELECT a.*,b.category_name from db_expense as a,db_expense_category as b where b.id=a.category_id and a.expense_date>='$from_date' and expense_date<='$to_date'");*/
		
		$this->db->select("a.*,b.category_name");
		
		if($category_id!=''){
			$this->db->where("a.category_id=$category_id");
		}
		if($view_all=="no"){
			$this->db->where("(a.expense_date>='$from_date' and a.expense_date<='$to_date')");
		}
		$this->db->where("b.`id`= a.`category_id`");
		$this->db->from("db_expense as a");
		$this->db->from("db_expense_category as b");
		if(!empty($store_id)){
			$this->db->where("a.store_id",$store_id);
		}
		//echo $this->db->get_compiled_select();
		
		$q1=$this->db->get();
		
		if($q1->num_rows()>0){
			$i=0;
			$tot_expense_amt=0;
			foreach ($q1->result() as $res1) {
				echo "<tr>";
				echo "<td>".++$i."</td>";
				if(store_module() && is_admin()){
					echo "<td>".get_store_name($res1->store_id)."</td>";	
				}
				echo "<td>".$res1->expense_code."</td>";
				echo "<td>".show_date($res1->expense_date)."</td>";
				echo "<td>".$res1->category_name."</td>";
				echo "<td>".$res1->reference_no."</td>";
				echo "<td>".$res1->expense_for."</td>";
				echo "<td class='text-right'>".store_number_format($res1->expense_amt)."</td>";
				echo "<td>".$res1->note."</td>";
				echo "<td>".ucfirst($res1->created_by)."</td>";
				echo "</tr>";
				$tot_expense_amt+=$res1->expense_amt;
			}
			$total_columns_count=5;
			if(store_module() && is_admin()){
				$total_columns_count ++;
			}
			if(warehouse_module() && warehouse_count()>0){
				$total_columns_count ++;
			}
			echo "<tr>
					  <td class='text-right text-bold' colspan='$total_columns_count'><b>Total Expense :</b></td>
					  <td class='text-right text-bold'>".store_number_format($tot_expense_amt)."</td>
					  <td colspan='2'></td>
				  </tr>";
		}
		else{
			$total_columns_count=8;
			if(store_module() && is_admin()){
				$total_columns_count ++;
			}
			if(warehouse_module() && warehouse_count()>0){
				$total_columns_count ++;
			}
			echo "<tr>";
			echo "<td class='text-center text-danger' colspan='$total_columns_count'>No Records Found</td>";
			echo "</tr>";
		}
		
		exit;
	}
	public function show_stock_report(){
		extract($_POST);
		

		if(!empty($store_id)){
			$this->db->where("a.store_id",$store_id);
		}
		if(!is_admin()){
			$this->db->where("a.store_id",get_current_store_id());
		}
		$this->db->select("a.sales_price,a.item_code,a.price,a.purchase_price,a.item_name,a.tax_type,a.store_id,a.id as item_id,a.item_group,
			d.category_name,
			c.brand_name,
			");
		$this->db->select("b.tax_name");
		$this->db->from("db_items as a");

		$this->db->from("db_tax as b");
		$this->db->where("b.id=a.tax_id");
		$this->db->where("a.service_bit=0");
		$this->db->join("db_brands as c","c.id=a.brand_id","left");
		$this->db->join("db_category as d","d.id=a.category_id","left");

		if(!empty($brand_id)){
			$this->db->where("a.brand_id",$brand_id);
		}
		if(!empty($category_id)){
			$this->db->where("a.category_id",$category_id);
		}

		//echo $this->db->get_compiled_select();exit;
		$q1=$this->db->get();
		$str='';


		if($q1->num_rows()>0){
			$i=0;
			$tot_stock = 0;
			$tot_value = 0;
			$tot_stock_value_by_purchase_price = 0;
			foreach ($q1->result() as $res1) {

				if($res1->item_group=='Variants'){continue;}

					$available_qty_wh = total_available_qty_items_of_warehouse($warehouse_id,$res1->store_id,$res1->item_id);

					

					$value = $available_qty_wh * $res1->sales_price;

					$stock_value_by_purchase_price = $available_qty_wh * $res1->price;

					/*if($available_qty_wh>0){*/
						$tax_type = ($res1->tax_type=='Inclusive') ? 'Inc.' : 'Exc.';
						$str .= "<tr>";
						$str .= "<td>".++$i."</td>";
						if(store_module() && is_admin()){
							$str .= "<td>".get_store_name($res1->store_id)."</td>";	
						}
						$str .= "<td>".$res1->item_code."</td>";
						$str .= "<td>".$res1->item_name."</td>";
						$str .= "<td>".$res1->brand_name."</td>";
						$str .= "<td>".$res1->category_name."</td>";
						$str .= "<td class='text-right'>".store_number_format($res1->purchase_price)."</td>";
						$str .= "<td>".$res1->tax_name."[".$tax_type."]</td>";
						$str .= "<td class='text-right'>".store_number_format($res1->price)."</td>";
						$str .= "<td class='text-right'>".store_number_format($res1->sales_price)."</td>";
						$str .= "<td>".format_qty($available_qty_wh)."</td>";
						$str .= "<td class='text-right'>".store_number_format($value)."</td>";
						$str .= "<td class='text-right'>".store_number_format($stock_value_by_purchase_price)."</td>";
						$str .= "</tr>";
						$tot_stock +=$available_qty_wh;
						$tot_value +=$value;
						$tot_stock_value_by_purchase_price += $stock_value_by_purchase_price;

					/*}*/

			}
			$total_columns_count=9;
			if(store_module() && is_admin()){
				$total_columns_count ++;
			}
			$str .= "<tr>
					  <td class='text-right text-bold' colspan='$total_columns_count'><b>Total :</b></td>
					  <td class='text-left text-bold'>".format_qty($tot_stock)."</td>
					  <td class='text-right text-bold'>".store_number_format($tot_value)."</td>
					  <td class='text-right text-bold'>".store_number_format($tot_stock_value_by_purchase_price)."</td>
				  </tr>";
		}
		else{
			$total_columns_count=12;
			if(store_module() && is_admin()){
				$total_columns_count ++;
			}
			$str .= "<tr>";
			$str .= "<td class='text-center text-danger' colspan='$total_columns_count'>No Records Found</td>";
			$str .= "</tr>";
		}
		
		return $str;
		
		exit;
	}
	public function brand_wise_stock(){
		extract($_POST);

		if(!empty($store_id)){
			$this->db->where("b.store_id",$store_id);
		}
		if(!is_admin()){
			$this->db->where("b.store_id",get_current_store_id());
		}
		$this->db->from("db_brands as b");
		$this->db->select("b.id,b.brand_name,b.store_id");
		if(!empty($brand_id)){
			$this->db->where("b.id",$brand_id);
		}


		//echo $this->db->get_compiled_select();exit();
		$q1=$this->db->get();
		$str1='';
		if($q1->num_rows()>0){
			$i=0;
			$tot_stock=0;
			foreach ($q1->result() as $res1) {
					$available_qty=0;
					$brand_id=$res1->id;
					$store_id=$res1->store_id;

					$str='';
					if(empty($store_id)){
						 $str =" and store_id= $store_id ";
					}
					if(!empty($warehouse_id)){
						 $str =" and warehouse_id= $warehouse_id ";
					}
					$q3 = "select COALESCE(sum(available_qty),0) as available_qty from 
							db_warehouseitems where 
							item_id in (select id from db_items where brand_id=$brand_id)
							$str
							";

					$q3=$this->db->query($q3);

					$available_qty = $q3->row()->available_qty;
					$str1 .= "<tr>";
					$str1 .= "<td>".++$i."</td>";
					if(store_module() && is_admin()){
						$str1 .= "<td>".get_store_name($res1->store_id)."</td>";	
					}
					$str1 .= "<td>".$res1->brand_name."</td>";
					$str1 .= "<td>".format_qty($available_qty)."</td>";
					$str1 .= "</tr>";
					$tot_stock+=$available_qty;
			}
			$total_columns_count=2;
			if(store_module() && is_admin()){
				$total_columns_count ++;
			}
			$str1 .= "<tr>
					  <td class='text-right text-bold' colspan='$total_columns_count'><b>Total :</b></td>
					  <td class='text-left text-bold'>".format_qty($tot_stock)."</td>
				  </tr>";

		}
		else{
			$total_columns_count=3;
			if(store_module() && is_admin()){
				$total_columns_count ++;
			}
			$str1 .= "<tr>";
			$str1 .= "<td class='text-center text-danger' colspan='$total_columns_count'>No Records Found</td>";
			$str1 .= "</tr>";
		}
		
		return $str1;
		exit;
	}
	public function show_item_sales_report(){
		extract($_POST);

		
		$from_date = system_fromatted_date($from_date);
		$to_date = system_fromatted_date($to_date);

		if(warehouse_module() && warehouse_count()>0 && !empty($warehouse_id)){
			$this->db->where("a.warehouse_id",$warehouse_id);
		}
		
		$this->db->select("a.id,a.sales_code,a.sales_date,b.customer_name,b.customer_code,a.grand_total,a.paid_amount,a.store_id,c.unit_total_cost,c.total_cost,e.category_name");
		$this->db->select("c.sales_qty,d.item_name");
		
		
		$this->db->from("db_sales as a");
		//$this->db->where("a.`id`= c.`sales_id`");
		$this->db->where("a.`sales_status`= 'Final'");
		
		if($view_all=="no"){
			$this->db->where("(a.sales_date>='$from_date' and a.sales_date<='$to_date')");
		}
		if(!empty($store_id)){
			$this->db->where("a.store_id",$store_id);
		}
		$this->db->order_by("a.`sales_date`,a.sales_code",'desc');

		$str = "" ; 
		if($item_id!=''){
			$str = " and c.item_id=$item_id"; 
		}
		$this->db->join("db_salesitems as c","c.sales_id = a.id $str","left");

		//$this->db->join("db_category as e","e.`id`= d.category_id","left");
		$this->db->join("db_items as d","d.`id`= c.`item_id`","left");

		$str = "" ; 
		if($category_id!=''){
			$str = " and e.id=$category_id"; 
		}

		$this->db->join("db_category as e","e.`id`= d.`category_id` $str","right");
		$this->db->join("db_customers as b","b.`id`= a.`customer_id`","left");

		
		//echo $this->db->get_compiled_select();exit();
		
		$q1=$this->db->get();
		if($q1->num_rows()>0){
			$i=0;
			$tot_total_cost=0;
			$tot_sales_qty=0;
			foreach ($q1->result() as $res1) {
				echo "<tr>";
				echo "<td>".++$i."</td>";
				if(store_module() && is_admin()){
					echo "<td>".get_store_name($res1->store_id)."</td>";	
				}
				if($store_id==get_current_store_id()){
				echo "<td><a title='View Invoice' href='".base_url("sales/invoice/$res1->id")."'>".$res1->sales_code."</a></td>";
				}
				else{
				echo "<td>".$res1->sales_code."</td>";	
				}

				
				echo "<td>".show_date($res1->sales_date)."</td>";
				echo "<td>".$res1->customer_name."</td>";
				echo "<td>".$res1->item_name."</td>";
				echo "<td>".$res1->category_name."</td>";
				echo "<td>".format_qty($res1->sales_qty)."</td>";
				echo "<td class='text-right'>".store_number_format($res1->unit_total_cost)."</td>";
				echo "<td class='text-right'>".store_number_format($res1->total_cost)."</td>";
				echo "</tr>";
				$tot_sales_qty+=$res1->sales_qty;
				$tot_total_cost+=$res1->total_cost;
			}

			$total_columns_count=5;
			if(store_module() && is_admin()){
				$total_columns_count ++;
			}
			if(warehouse_module() && warehouse_count()>0){
				$total_columns_count ++;
			}

			echo "<tr>
					  <td class='text-right text-bold' colspan='$total_columns_count'><b>Total :</b></td>
					  <td class='text-left text-bold'>".format_qty($tot_sales_qty)."</td>
					  <td class='text-right text-bold'></td>
					  <td class='text-right text-bold'>".store_number_format($tot_total_cost)."</td>
				  </tr>";
		}
		else{
			$total_columns_count=8;
			if(store_module() && is_admin()){
				$total_columns_count ++;
			}
			if(warehouse_module() && warehouse_count()>0){
				$total_columns_count ++;
			}
			echo "<tr>";
			echo "<td class='text-center text-danger' colspan='$total_columns_count'>No Records Found</td>";
			echo "</tr>";
		}
		
		exit;
	}
	public function show_purchase_payments_report(){
		extract($_POST);
		$supplier_id = $this->input->post('supplier_id');
		/*$from_date=date("Y-m-d",strtotime($from_date));
		$to_date=date("Y-m-d",strtotime($to_date));*/

		$from_date = system_fromatted_date($from_date);
		$to_date = system_fromatted_date($to_date);
		
		$this->db->select("c.id,c.purchase_code,a.payment_date,b.supplier_name,b.supplier_code,a.payment_type,a.payment_note,a.payment,a.store_id");
		
		if($supplier_id!=''){
			$this->db->where("c.supplier_id=$supplier_id");
		}
		$this->db->where("b.id=c.`supplier_id`");
		$this->db->where("(a.payment_date>='$from_date' and a.payment_date<='$to_date')");
		
		$this->db->where("c.id=a.purchase_id");

		$this->db->from("db_purchasepayments as a");
		$this->db->from("db_suppliers as b");
		$this->db->from("db_purchase as c");
		$this->db->where("c.`purchase_status`= 'Received'");
		if(!empty($store_id)){
			$this->db->where("a.store_id",$store_id);
		}
		//$this->db->group_by("c.purchase_code");
		
		//echo $this->db->get_compiled_select();
		
		$q1=$this->db->get();
		if($q1->num_rows()>0){
			$i=0;
			$tot_payment=0;
			foreach ($q1->result() as $res1) {
				echo "<tr>";
				echo "<td>".++$i."</td>";
				if(store_module() && is_admin()){
					echo "<td>".get_store_name($res1->store_id)."</td>";	
				}
				if($res1->store_id==get_current_store_id()){
				echo "<td><a title='View Invoice' href='".base_url("purchase/invoice/$res1->id")."'>".$res1->purchase_code."</a></td>";
				}
				else{
				echo "<td>".$res1->purchase_code."</td>";	
				}

				
				echo "<td>".show_date($res1->payment_date)."</td>";
				echo "<td>".$res1->supplier_code."</td>";
				echo "<td>".$res1->supplier_name."</td>";
				echo "<td>".$res1->payment_type."</td>";
				echo "<td>".$res1->payment_note."</td>";
				echo "<td class='text-right'>".store_number_format(($res1->payment))."</td>";
				echo "</tr>";
				$tot_payment+=$res1->payment;
			}
			$total_columns_count=7;
			if(store_module() && is_admin()){
				$total_columns_count ++;
			}
			echo "<tr>
					  <td class='text-right text-bold' colspan='$total_columns_count'><b>Total :</b></td>
					  <td class='text-right text-bold'>".store_number_format($tot_payment)."</td>
				  </tr>";
		}
		else{
			$total_columns_count=8;
			if(store_module() && is_admin()){
				$total_columns_count ++;
			}
			echo "<tr>";
			echo "<td class='text-center text-danger' colspan='$total_columns_count'>No Records Found</td>";
			echo "</tr>";
		}
		
		exit;
	}
	public function show_sales_payments_report(){
		extract($_POST);
		
		$from_date = system_fromatted_date($from_date);
		$to_date = system_fromatted_date($to_date);
		
		
		$this->db->select("c.id,c.sales_code,a.payment_date,b.customer_name,b.customer_code,a.payment_type,a.payment_note,a.payment,c.store_id,c.created_by");
		
		$this->db->from("db_sales as c");
		$this->db->where("c.id=a.sales_id and c.`sales_status`= 'Final'");
		

		if($customer_id!=''){
			$this->db->where("c.customer_id",$customer_id);
		}
		if(!empty($created_by)){
			$this->db->where("upper(c.created_by)=upper('$created_by')");
		}
		if(!empty($store_id)){
			$this->db->where("c.store_id",$store_id);
		}

		$str = '';
		if(!empty($payment_type)){
			$str .= " and a.payment_type = '$payment_type'";
		}

		$str .=(" and (a.payment_date>='$from_date' and a.payment_date<='$to_date')");

		$this->db->join("db_salespayments as a","a.sales_id = c.id $str","left");

		$this->db->join("db_customers as b","b.id=c.`customer_id`","left");
		
		$q1=$this->db->get();
		if($q1->num_rows()>0){
			$i=0;
			$tot_payment=0;
			foreach ($q1->result() as $res1) {
				echo "<tr>";
				echo "<td>".++$i."</td>";
				if(store_module() && is_admin()){
					echo "<td>".get_store_name($res1->store_id)."</td>";	
				}
				if($res1->store_id==get_current_store_id()){
				echo "<td><a title='View Invoice' href='".base_url("sales/invoice/$res1->id")."'>".$res1->sales_code."</a></td>";
				}
				else{
				echo "<td>".$res1->sales_code."</td>";	
				}
				
				echo "<td>".show_date($res1->payment_date)."</td>";
				echo "<td>".$res1->customer_code."</td>";
				echo "<td>".$res1->customer_name."</td>";
				echo "<td>".$res1->payment_type."</td>";
				echo "<td>".$res1->payment_note."</td>";
				echo "<td class='text-right'>".store_number_format(($res1->payment))."</td>";
				echo "<td>".$res1->created_by."</td>";
				echo "</tr>";
				$tot_payment+=$res1->payment;
			}
			$total_columns_count=7;
			if(store_module() && is_admin()){
				$total_columns_count ++;
			}
			echo "<tr>
					  <td class='text-right text-bold' colspan='$total_columns_count'><b>Total :</b></td>
					  <td class='text-right text-bold'>".store_number_format($tot_payment)."</td>
				  </tr>";
		}
		else{
			$total_columns_count=8;
			if(store_module() && is_admin()){
				$total_columns_count ++;
			}
			echo "<tr>";
			echo "<td class='text-center text-danger' colspan='$total_columns_count'>No Records Found</td>";
			echo "</tr>";
		}
		
		exit;
	}



	public function show_sales_return_payments_report(){
		extract($_POST);
		
		$from_date = system_fromatted_date($from_date);
		$to_date = system_fromatted_date($to_date);
		
		
		$this->db->select("c.id,c.return_code,a.payment_date,b.customer_name,b.customer_code,a.payment_type,a.payment_note,a.payment,c.store_id,c.created_by");
		
		$this->db->from("db_salesreturn as c");
		

		if($customer_id!=''){
			$this->db->where("c.customer_id",$customer_id);
		}
		if(!empty($created_by)){
			$this->db->where("upper(c.created_by)=upper('$created_by')");
		}
		if(!empty($store_id)){
			$this->db->where("c.store_id",$store_id);
		}

		$str = '';
		if(!empty($payment_type)){
			$str .= " and a.payment_type = '$payment_type'";
		}

		$str .=(" and (a.payment_date>='$from_date' and a.payment_date<='$to_date')");

		$this->db->join("db_salespaymentsreturn as a","c.id = a.return_id $str","inner");

		$this->db->join("db_customers as b","b.id=c.`customer_id`","left");
		$this->db->where("a.payment IS NOT NULL AND a.payment > 0"); // Only show records with actual payments
		
		$q1=$this->db->get();
		if($q1->num_rows()>0){
			$i=0;
			$tot_payment=0;
			foreach ($q1->result() as $res1) {
				echo "<tr>";
				echo "<td>".++$i."</td>";
				if(store_module() && is_admin()){
					echo "<td>".get_store_name($res1->store_id)."</td>";	
				}
				if($res1->store_id==get_current_store_id()){
				echo "<td><a title='View Invoice' href='".base_url("sales_return/invoice/$res1->id")."'>".$res1->return_code."</a></td>";
				}
				else{
				echo "<td>".$res1->return_code."</td>";	
				}
				
				echo "<td>".show_date($res1->payment_date)."</td>";
				echo "<td>".$res1->customer_code."</td>";
				echo "<td>".$res1->customer_name."</td>";
				echo "<td>".$res1->payment_type."</td>";
				
				// Enhanced payment note display
				$payment_note_display = $res1->payment_note;
				if(strpos($payment_note_display, '[Applied to outstanding balance:') !== false){
					$payment_note_display = '<span class="text-info" title="Payment applied to reduce customer debt">' . $payment_note_display . '</span>';
				} elseif(strpos($payment_note_display, '[Balance adjustment:') !== false){
					$payment_note_display = '<span class="text-warning" title="Payment partially applied to debt, remainder as cash refund">' . $payment_note_display . '</span>';
				} elseif(strpos($payment_note_display, '[Full cash refund:') !== false){
					$payment_note_display = '<span class="text-success" title="Full amount given as cash refund">' . $payment_note_display . '</span>';
				}
				echo "<td>".$payment_note_display."</td>";
				
				echo "<td class='text-right'>".store_number_format(($res1->payment))."</td>";
				echo "<td>".$res1->created_by."</td>";
				echo "</tr>";
				$tot_payment+=$res1->payment;
			}
			$total_columns_count=7;
			if(store_module() && is_admin()){
				$total_columns_count ++;
			}
			echo "<tr>
					  <td class='text-right text-bold' colspan='$total_columns_count'><b>Total :</b></td>
					  <td class='text-right text-bold'>".store_number_format($tot_payment)."</td>
				  </tr>";
		}
		else{
			$total_columns_count=8;
			if(store_module() && is_admin()){
				$total_columns_count ++;
			}
			echo "<tr>";
			echo "<td class='text-center text-danger' colspan='$total_columns_count'>No Records Found</td>";
			echo "</tr>";
		}
		
		exit;
	}


	/*Expired Items Report*/
	public function show_expired_items_report(){
		extract($_POST);

		
		//$to_date=date("Y-m-d",strtotime($to_date));
		$to_date = system_fromatted_date($to_date);

		$this->db->select("id,item_code,item_name,expire_date,stock,lot_number,store_id");
		
		if($item_id!=''){
			
			$this->db->where("id=$item_id");
		}
		if($view_all=="no"){
			$this->db->where("(expire_date<='$to_date')");
		}
		$this->db->from("db_items");
		if(!empty($store_id)){
			$this->db->where("store_id",$store_id);
		}
		//echo $this->db->get_compiled_select();exit();
		
		$q1=$this->db->get();
		if($q1->num_rows()>0){
			$i=0;
			foreach ($q1->result() as $res1) {
				if(get_total_qty_of_warehouse_item($res1->id,$warehouse_id,$res1->store_id) > 0 && !empty($warehouse_id)){
					echo "<tr>";
					echo "<td>".++$i."</td>";
					if(store_module() && is_admin()){
						echo "<td>".get_store_name($res1->store_id)."</td>";	
					}
					echo "<td>".$res1->item_code."</td>";
					echo "<td>".$res1->item_name."</td>";
					echo "<td>".$res1->lot_number."</td>";
					echo "<td>".show_date($res1->expire_date)."</td>";
					echo "<td>".format_qty(get_total_qty_of_warehouse_item($res1->id,$warehouse_id,$res1->store_id))."</td>";

				}
				else{
					echo "<tr>";
					echo "<td>".++$i."</td>";
					if(store_module() && is_admin()){
						echo "<td>".get_store_name($res1->store_id)."</td>";	
					}
					echo "<td>".$res1->item_code."</td>";
					echo "<td>".$res1->item_name."</td>";
					echo "<td>".$res1->lot_number."</td>";
					echo "<td>".show_date($res1->expire_date)."</td>";
					echo "<td>".format_qty($res1->stock)."</td>";
				}

			}
		}
		else{
			$total_columns_count=6;
			if(store_module() && is_admin()){
				$total_columns_count ++;
			}
			echo "<tr>";
			echo "<td class='text-center text-danger' colspan='$total_columns_count'>No Records Found</td>";
			echo "</tr>";
		}
		
		exit;
	}

	function _create_query($store_id='',$table_name,$table_column,$from_date,$to_date){
		$ids = array();

		if($table_column=='db_purchase'){
			$this->db->where("purchase_status='Received'");
		}
		else if($table_column=='db_sales'){
			$this->db->where("sales_status='Final'");
		}

		$this->db->where("(".$table_column.">='$from_date' and ".$table_column."<='$to_date')");

		if(is_admin()){
			if(!empty($store_id)){
				$this->db->where("store_id",$store_id);
			}
		}else{
			$this->db->where("store_id",get_current_store_id());
		}

		$this->db->select("id");
		$this->db->from($table_name);
		$query = $this->db->get();
		if($query->num_rows()>0){
			foreach ($query->result() as $res) {
				$ids[] = $res->id;
			}	
		}
		return (count($ids)>0) ? implode (", ", $ids) : 'null';
	}
	function _get_db_sales_ids($store_id='',$from_date,$to_date){

		return $this->_create_query($store_id,'db_sales','sales_date',$from_date,$to_date);

	}
	function _get_db_sales_return_ids($store_id='',$from_date,$to_date){

		return $this->_create_query($store_id,'db_salesreturn','return_date',$from_date,$to_date);

	}
	function _get_db_purchase_ids($store_id='',$from_date,$to_date){

		return $this->_create_query($store_id,'db_purchase','purchase_date',$from_date,$to_date);

	}
	function _get_db_purchase_return_ids($store_id='',$from_date,$to_date){

		return $this->_create_query($store_id,'db_purchasereturn','return_date',$from_date,$to_date);

	}
	function _get_db_expense_ids($store_id='',$from_date,$to_date){
		
		return $this->_create_query($store_id,'db_expense','expense_date',$from_date,$to_date);

	}

	public function get_profit_loss_report(){

			$store_id=$this->input->post('store_id');
			$from_date=$this->input->post('from_date');
			$to_date=$this->input->post('to_date');
			$from_date = system_fromatted_date($from_date);
			$to_date = system_fromatted_date($to_date);

			$sales_ids = $this->_get_db_sales_ids($store_id,$from_date,$to_date);
			$sales_return_ids = $this->_get_db_sales_return_ids($store_id,$from_date,$to_date);
			$purchase_ids = $this->_get_db_purchase_ids($store_id,$from_date,$to_date);
			$purchase_return_ids = $this->_get_db_purchase_return_ids($store_id,$from_date,$to_date);
			$expense_ids = $this->_get_db_expense_ids($store_id,$from_date,$to_date);

			
			
			$info=array();

			//Get opening Balance
			if(store_module() && is_admin()){if(!empty($store_id)){ 
						$this->db->where("a.store_id",$store_id);}
					}else{ 
						$this->db->where("a.store_id",get_current_store_id());	
				}
			$this->db->select("SUM(b.adjustment_qty * a.purchase_price) AS  opening_stock_price");
			$this->db->from("db_items AS a , db_stockadjustmentitems AS b");
			$this->db->where("a.id=b.item_id");
			$query = $this->db->get()->row();
			$opening_stock_price=$query->opening_stock_price;
			$info['opening_stock_price']=(store_number_format($opening_stock_price));
			


			//total purchase amt
			$this->db->select("COALESCE(SUM(a.tax_amt),0) AS tax_amt");
			$this->db->select("COALESCE(SUM(a.discount_amt),0) AS discount_amt");
			$this->db->from("db_purchaseitems as a");
			$this->db->where("a.purchase_id in (".$purchase_ids.")");
			$query = $this->db->get()->row();
			$purchase_discount_amt=$query->discount_amt;
			$purchase_tax_amt=$query->tax_amt;
			$info['purchase_tax_amt']=(store_number_format($purchase_tax_amt));
			

			//total purchase amt
			$this->db->select("COALESCE(SUM(grand_total),0) AS pur_total");
			$this->db->select("COALESCE(SUM(other_charges_amt),0) AS other_charges_amt");
			$this->db->select("COALESCE(SUM(tot_discount_to_all_amt),0) AS tot_discount_to_all_amt");
			$this->db->select("COALESCE(SUM(paid_amount),0) AS paid_amount");
			$this->db->from("db_purchase");
			$this->db->where("id in (".$purchase_ids.")");

			$query = $this->db->get()->row();

			$pur_total=$query->pur_total;
			$pur_total-=$purchase_tax_amt;
			$info['pur_total']=(store_number_format($pur_total));

			//Other Charge of Purchase entry
			$pur_other_charges_amt=$query->other_charges_amt;
			$info['pur_other_charges_amt']=(store_number_format($pur_other_charges_amt));

			//Discount purchase entry
			$purchase_discount_amt+=$query->tot_discount_to_all_amt;
			$info['purchase_discount_amt']=(store_number_format($purchase_discount_amt));

			//purchase Paid Amount
			$purchase_paid_amount=$query->paid_amount;
			$info['purchase_paid_amount']=(store_number_format($purchase_paid_amount));
			
			//total purchase due
			$purchase_due_total=$pur_total - $query->paid_amount;
			$info['purchase_due_total']=(store_number_format($purchase_due_total));





			/***********Purchase Return**********/
			//return tax amt
			$this->db->select("COALESCE(SUM(tax_amt),0) AS tax_amt");
			$this->db->select("COALESCE(SUM(discount_amt),0) AS discount_amt");
			$this->db->where("return_id in (".$purchase_return_ids.")");
			$this->db->from("db_purchaseitemsreturn");
			$query = $this->db->get()->row();
			
			//Disount purchase return entry
			$purchase_return_discount_amt=$query->discount_amt;

			$purchase_return_tax_amt=$query->tax_amt;
			$info['purchase_return_tax_amt']=(store_number_format($purchase_return_tax_amt));
			
			//total purchase return amt
			$this->db->select("COALESCE(SUM(grand_total),0) AS pur_total");
			$this->db->select("COALESCE(SUM(other_charges_amt),0) AS other_charges_amt");
			$this->db->select("COALESCE(SUM(tot_discount_to_all_amt),0) AS tot_discount_to_all_amt");
			$this->db->select("COALESCE(SUM(paid_amount),0) AS paid_amount");
			$this->db->where("id in (".$purchase_return_ids.")");
			$this->db->from("db_purchasereturn");
			$query = $this->db->get()->row();
			
			$pur_return_total=$query->pur_total;
			$pur_return_total-=$purchase_return_tax_amt;
			$info['pur_return_total']=(store_number_format($pur_return_total));

			//Due
			$purchase_return_due_total=$pur_return_total - $query->paid_amount;
			$info['purchase_return_due_total']=(store_number_format($purchase_return_due_total));

			//Other Charge of Purchase return entry
			$pur_return_other_charges_amt=$query->other_charges_amt;
			$info['pur_return_other_charges_amt']=(store_number_format($pur_return_other_charges_amt));
			
			//Disount purchase return entry
			$purchase_return_discount_amt+=$query->tot_discount_to_all_amt;
			$info['purchase_return_discount_amt']=(store_number_format($purchase_return_discount_amt));

			//Purchase Return Paid Amount
			$purchase_return_paid_amount=$query->paid_amount;
			$info['purchase_return_paid_amount']=(store_number_format($purchase_return_paid_amount));
			
			
			/***********Sales Entry**********/
			$this->db->select("COALESCE(SUM(tax_amt),0) AS tax_amt");
			$this->db->select("COALESCE(SUM(discount_amt),0) AS discount_amt");
			$this->db->where("sales_id in (".$sales_ids.")");
			$this->db->from("db_salesitems");
			$query = $this->db->get()->row();
			
			
			//total sales amt
			$sales_tax_amt=$query->tax_amt;
			$info['sales_tax_amt']=(store_number_format($sales_tax_amt));
			
			
			
			//Disount sales entry
			$sales_discount_amt=$query->discount_amt;
			

			//Coupon Disount sales entry
			$this->db->select("COALESCE(SUM(b.coupon_amt),0) AS coupon_amt");
			$this->db->select("COALESCE(SUM(other_charges_amt),0) AS other_charges_amt");
			$this->db->select("COALESCE(SUM(tot_discount_to_all_amt),0) AS tot_discount_to_all_amt");
			$this->db->select("COALESCE(sum(grand_total),0) AS tot_sal_grand_total");
			$this->db->select("COALESCE(SUM(paid_amount),0) AS paid_amount");
			$this->db->from("db_sales as b");
			$this->db->where("id in (".$sales_ids.")");
			$this->db->where("b.sales_status='Final'");
			$query = $this->db->get()->row();

			$coupon_discount_amt=$query->coupon_amt;
			$info['coupon_discount_amt']=(store_number_format($coupon_discount_amt));

			//Other Charge of Sales entry
			$sal_other_charges_amt=$query->other_charges_amt;
			$info['sal_other_charges_amt']=(store_number_format($sal_other_charges_amt));

			//Discount Amount
			$sales_discount_amt+=$query->tot_discount_to_all_amt;
			$info['sales_discount_amt']=(store_number_format($sales_discount_amt));
			
			
			//Total SAles amount
			$sal_total=$query->tot_sal_grand_total;
			$tot_sal_total=($sal_total-$sales_tax_amt+$coupon_discount_amt);
			$info['sal_total']=(store_number_format($tot_sal_total));

			//Due
			$sales_due_total=$sal_total - $query->paid_amount;
			$info['sales_due_total']=(store_number_format($sales_due_total));
		
			//sales Paid Amount
			$sales_paid_amount=$query->paid_amount;
			$info['sales_paid_amount']=(store_number_format($sales_paid_amount));
			


			/***********Sales Return**********/
			$this->db->select("COALESCE(SUM(tax_amt),0) AS tax_amt");
			$this->db->select("COALESCE(SUM(discount_amt),0) AS discount_amt");
			$this->db->from("db_salesitemsreturn");
			$this->db->where("return_id in (".$sales_return_ids.")");
			$query = $this->db->get()->row();

			//Disount sales return entry
			$sales_return_discount_amt=$query->discount_amt;

			//total sales return amt
			$sales_return_tax_amt=$query->tax_amt;
			$info['sales_return_tax_amt']=(store_number_format($sales_return_tax_amt));
			
			
			$this->db->select("COALESCE(SUM(grand_total),0) AS sal_total");
			$this->db->select("COALESCE(SUM(other_charges_amt),0) AS other_charges_amt");
			$this->db->select("COALESCE(SUM(coupon_amt),0) AS coupon_amt");
			$this->db->select("COALESCE(SUM(tot_discount_to_all_amt),0) AS tot_discount_to_all_amt");
			$this->db->select("COALESCE(SUM(paid_amount),0) AS paid_amount");
			$this->db->where("id in (".$sales_return_ids.")");
			$this->db->from("db_salesreturn");
			$query = $this->db->get()->row();
			

			//total sales return amt
			$sal_return_total=$query->sal_total;
			$sal_return_total-=$sales_return_tax_amt;
			$info['sal_return_total']=(store_number_format($sal_return_total));

			//Due
			$sales_return_due_total=$sal_return_total - $query->paid_amount;
			$info['sales_return_due_total']=(store_number_format($sales_return_due_total));

			//Other Charge of Sales return entry
			$sal_return_other_charges_amt=$query->other_charges_amt;
			$info['sal_return_other_charges_amt']=(store_number_format($sal_return_other_charges_amt));
			
			//Coupon Disount sales entry
			$coupon_discount_amt=$query->coupon_amt;
			$info['return_coupon_discount_amt']=(store_number_format($coupon_discount_amt));

			//Discount 
			$sales_return_discount_amt+=$query->tot_discount_to_all_amt;
			$info['sales_return_discount_amt']=(store_number_format($sales_return_discount_amt));
			
			//sales Return Paid Amount;
			$sales_return_paid_amount=$query->paid_amount;
			$info['sales_return_paid_amount']=(store_number_format($sales_return_paid_amount));
			
			
			/***********Sales Return**********/
			$this->db->select("COALESCE(SUM(expense_amt),0) AS exp_total");
			$this->db->where("id in (".$expense_ids.")");
			$this->db->from("db_expense");
			$query = $this->db->get()->row();

			//expense total
			$exp_total=$query->exp_total;
			$info['exp_total']=(store_number_format($exp_total));;
						
			//GROSS PROFIT & NET PROFIT
			$sales_details = $this->get_sales_item_sum($sales_ids);
			$sal_item_pur_price = $sales_details['purchase_price'];
			$sal_cost = $sales_details['sales_price'];
			$sal_tax_amt = $sales_details['tax_amt'];
			$net_sales = $sal_cost-$sal_item_pur_price;//ACTUAL SALES VALUE


			$return_details = $this->get_sales_return_item_sum($sales_return_ids);
			$ret_item_pur_price = $return_details['purchase_price'];
			$ret_cost = $return_details['return_price'];
			$ret_tax_amt = $return_details['tax_amt'];
			$net_return = $ret_cost - $ret_item_pur_price;//ACTUAL RETURN VALUE
			

			//TO FIND GROSS PROFIT = (SALES PRICE - PURCHASE PRICE OF ITEM) - (SALES RETURN - PURCHASE PRICE OF ITEM)
			/**
			 * Gorss prfit = Sales - Purchase price
			 * To find gross prfit we are also want deduct return sales records
			*/
			$gross_profit = $net_sales - $net_return;

			$info['net_sales']=(store_number_format($sal_cost));
			$info['sales_return_total']=(store_number_format($ret_cost));
			$info['gross_profit']=(store_number_format($gross_profit));

			
			//Tax
			//Sales & Retuern tax, because we are calculating sum
			$tot_tax = $sal_tax_amt - $ret_tax_amt;
			//Now you got valid tax

			//To find net profit now need to deduct valid tax from gross profit
			//also deduct expenses
			$net_profit = ($gross_profit - $tot_tax) - $exp_total;
			$info['tot_net_profit']=(store_number_format($net_profit));

			//Find purchase price of the sales item
			

			return $info;
	}


	public function get_sales_item_sum($sales_ids){
		$this->db->select("a.sales_qty,a.purchase_price,a.total_cost,a.tax_amt,a.tax_type");
		$this->db->select("b.tax");
		$this->db->from('db_salesitems a');
		$this->db->where("a.sales_id in (".$sales_ids.")");

		$this->db->join('db_tax b','b.id=a.tax_id','left');
		

		$Q1 = $this->db->get();
		$sum_pur_price = 0;
		$sum_sal_price = 0;
		$sum_tax_amt = 0;
		if($Q1->num_rows()>0){
			foreach ($Q1->result() as $res1){

				$sales_tax_amt = $res1->tax_amt;
				$sales_qty = $res1->sales_qty;

				$purchase_price = ($res1->tax_type == 'Exclusive') ? $res1->purchase_price : original_cost($res1->purchase_price,$res1->tax,'Inclusive');

				

				$sales_price = $res1->total_cost;

				$tot_pur_price = $sales_qty * $purchase_price;

				$sum_pur_price+=$tot_pur_price;
				$sum_sal_price+=$sales_price;
				$sum_tax_amt+=$sales_tax_amt;
			}
		}

		return array(
					'purchase_price' => $sum_pur_price,
					'sales_price' => $sum_sal_price,
					'tax_amt' => $sum_tax_amt,
				);
	}
	public function get_sales_return_item_sum($sales_return_ids){
		$this->db->select("a.return_qty,a.purchase_price,a.total_cost,a.tax_amt,a.tax_type");
		$this->db->select("b.tax");
		
		$this->db->from('db_salesitemsreturn a');
		$this->db->where("a.return_id in (".$sales_return_ids.")");

		$this->db->join('db_tax b','b.id=a.tax_id','left');

		$Q1 = $this->db->get();
		$sum_pur_price = 0;
		$sum_return_price = 0;
		$sum_tax_amt = 0;
		if($Q1->num_rows()>0){
			foreach ($Q1->result() as $res1){
				$return_qty = $res1->return_qty;

				$purchase_price = ($res1->tax_type == 'Exclusive') ? $res1->purchase_price : original_cost($res1->purchase_price,$res1->tax,'Inclusive');


				$return_price = $res1->total_cost;
				$tax_amt = $res1->tax_amt;

				$tot_pur_price = $return_qty * $purchase_price;

				$sum_pur_price+=$tot_pur_price;
				$sum_return_price+=$return_price;
				$sum_tax_amt+=$tax_amt;
			}
		}

		return array(
					'purchase_price' => $sum_pur_price,
					'return_price' => $sum_return_price,
					'tax_amt' => $sum_tax_amt,
				);
	}
	
	
	public function get_profit_by_item(){
		
		extract($_POST);
	
		$from_date = system_fromatted_date($from_date);
		$to_date = system_fromatted_date($to_date);

		
		if(warehouse_module() && warehouse_count()>0 && !empty($warehouse_id)){
			$this->db->where("c.warehouse_id",$warehouse_id);
		}
		if(store_module() && is_admin()){
			if(!empty($store_id)){ $this->db->where("c.store_id",$store_id);}
			}else{ $this->db->where("c.store_id",get_current_store_id());	
		}
		$this->db->select("a.service_bit, b.tax_amt,b.item_id,a.item_name,COALESCE(sum(b.sales_qty),0) as sales_qty,a.purchase_price,
						COALESCE(SUM(total_cost),0) as total_cost");
		$this->db->from("db_items as a, db_salesitems as b, db_sales as c");
		$this->db->where("c.id=b.sales_id and a.id=b.item_id and c.sales_status='Final'");
		$this->db->where("( c.sales_date>='".$from_date."' and  c.sales_date<='".$to_date."')");
		$this->db->group_by("item_id");
		//echo $this->db->get_compiled_select();exit();
		$q1=$this->db->get();
		
		if($q1->num_rows()>0){
			$i=0;
			$tot_purchase_price=0;
			$tot_sales_cost=0;
			$gross_profit=0;
			$tot_purchase_return_price=0;
			$tot_sales_return_price=0;
			$tot_sales_qty=0;
			$tot_purchase_return_qty=0;
			$tot_sales_return_qty=0;
			$grand_profit=0;
			$tot_net_profit=0;
			foreach ($q1->result() as $res1) {
				/*Purchase Return Quantity*/
				$purchase_return_qty=$this->db->query("
						SELECT COALESCE(sum(return_qty),0) as return_qty
						FROM db_purchaseitemsreturn
						WHERE 
						item_id =".$res1->item_id)->row()->return_qty;

				/*Sales Return Quantity*/
				$q3=$this->db->query("
						SELECT COALESCE(sum(total_cost),0) as total_cost,COALESCE(sum(return_qty),0) as return_qty
						FROM db_salesitemsreturn
						WHERE 
						item_id =".$res1->item_id);
				$sales_return_total_cost=$q3->row()->total_cost;
				$sales_return_qty=$q3->row()->return_qty;
				
				$qty = $res1->sales_qty-$sales_return_qty;
				//$purchase_price =  $res1->purchase_price * $qty;
				$purchase_price = ($res1->service_bit==0) ? $res1->purchase_price * $qty : 0;

				$total_cost = ($res1->total_cost - $sales_return_total_cost);
				//$purchase_return_price = $res1->purchase_price*$purchase_return_qty;
				$profit = $total_cost - $purchase_price;

				$tax_amt = $res1->tax_amt/$res1->sales_qty;

				$net_profit =$profit-($tax_amt*$qty);

				echo "<tr>";
				echo "<td>".++$i."</td>";
				echo "<td>".$res1->item_name."</td>";
				echo "<td>".format_qty($qty)."</td>";
				echo "<td style='text-align:right;'>".store_number_format($total_cost)."</td>";
				echo "<td style='text-align:right;'>".(store_number_format($purchase_price))."</td>";
				/*echo "<td style=''>".$purchase_return_qty."</td>";
				echo "<td style='text-align:right;'>".(store_number_format($purchase_return_price))."</td>";*/
				/*echo "<td style=''>".$sales_return_qty."</td>";
				echo "<td style='text-align:right;'>".($sales_return_total_cost)."</td>";*/
				echo "<td style='text-align:right;'>".(store_number_format($profit))."</td>";
				//echo "<td style='text-align:right;'>".(store_number_format($net_profit))."</td>";
				echo "</tr>";
				$tot_purchase_price+=$purchase_price;
				//$tot_purchase_return_price+=$purchase_return_price;
				$tot_sales_cost+=$total_cost;
				//$tot_sales_return_cost+=$sales_return_total_cost;
				//$gross_profit+=(($profit + $purchase_return_price)-$sales_return_total_cost);
				$tot_sales_qty+=($res1->sales_qty-$sales_return_qty);
				$tot_purchase_return_qty+=$purchase_return_qty;
				$tot_sales_return_qty+=$sales_return_qty;
				$gross_profit+=$profit;
				$tot_net_profit+=$net_profit;
			}
			echo "<tr>
					  <td class='text-right text-bold' colspan='2'><b>Total :</b></td>
					  <td class='text-bold'>".format_qty($tot_sales_qty)."</td>
					  <td class='text-right text-bold'>".(store_number_format($tot_sales_cost))."</td>
					  <td class='text-right text-bold'>".(store_number_format($tot_purchase_price))."</td>
					  
					  <td class='text-right text-bold'>".(store_number_format($gross_profit))."</td>
				  </tr>";
				  /*<td class='text-bold'>".$tot_purchase_return_qty."</td>
					  <td class='text-right text-bold'>".(store_number_format($tot_purchase_return_price))."</td>
					  <td class='text-bold'>".$tot_sales_return_qty."</td>
					  <td class='text-right text-bold'>".(store_number_format($tot_sales_return_cost))."</td>
					  */
		}
		else{
			echo "<tr>";
			echo "<td class='text-center text-danger' colspan=7>No Records Found</td>";
			echo "</tr>";
		}
		
		exit;
	}
	public function get_profit_by_invoice(){
		
		extract($_POST);
		/*$from_date=date("Y-m-d",strtotime($from_date));
		$to_date=date("Y-m-d",strtotime($to_date));*/

		$from_date = system_fromatted_date($from_date);
		$to_date = system_fromatted_date($to_date);
		

		
		if(warehouse_module() && warehouse_count()>0 && !empty($warehouse_id)){
			$this->db->where("a.warehouse_id",$warehouse_id);
		}
		if(store_module() && is_admin()){
			if(!empty($store_id)){ $this->db->where("a.store_id",$store_id);}
			}else{ $this->db->where("a.store_id",get_current_store_id());	
		}
		$this->db->select("a.coupon_amt,a.id,a.sales_date,a.sales_code,b.customer_name");
		$this->db->from("db_sales as a,db_customers as b");
		$this->db->where("a.sales_status='Final' and b.id=a.customer_id");

		$q1=$this->db->get();

		if($q1->num_rows()>0){
			$i=0;
			$tot_purchase_price=0;
			$tot_sales_cost=0;
			$tot_profit=0;
			$net_profit=0;
			$tot_net_profit=0;

			foreach ($q1->result() as $res1) {
				$q2=$this->db->query("SELECT b.sales_qty,COALESCE(SUM(a.purchase_price*sales_qty),0) AS purchase_price, COALESCE(SUM(total_cost),0) AS total_cost FROM db_items AS a, db_salesitems AS b, db_sales AS c WHERE c.id=b.sales_id AND a.id=b.item_id and c.sales_status='Final'
					AND b.sales_id=".$res1->id);

				$q3=$this->db->query("SELECT COALESCE(SUM(a.purchase_price*return_qty),0) AS purchase_price, COALESCE(SUM(total_cost),0) AS total_cost FROM db_items AS a, db_salesitemsreturn AS b, db_salesreturn AS c WHERE c.id=b.return_id AND a.id=b.item_id and c.return_status!='Final'
					AND b.sales_id=".$res1->id);
				$purchase_return_price=$q3->row()->purchase_price;



				//Total price item_purchase_price * qty
				$purchase_price = ($q2->row()->purchase_price-$purchase_return_price);
				//Total price item_sales_price * qty
				$sales_price = ($q2->row()->total_cost-$q3->row()->total_cost);

				$coupon_amt = $res1->coupon_amt;
				$profit = $sales_price - $purchase_price;
				
				/*$sales_tax_amt =$this->db->query("select COALESCE(SUM(tax_amt),0) AS tax_amt from db_salesitems where sales_id=".$res1->id)->row()->tax_amt;
				
				$sales_return_tax_amt =$this->db->query("select COALESCE(SUM(tax_amt),0) AS tax_amt from db_salesitemsreturn where sales_id=".$res1->id)->row()->tax_amt;

				$net_profit = $profit + ($sales_tax_amt-$sales_return_tax_amt);*/
				echo "<tr>";
				echo "<td>".++$i."</td>";
				echo "<td>".$res1->sales_code."</td>";
				echo "<td>".show_date($res1->sales_date)."</td>";
				echo "<td>".$res1->customer_name."</td>";
				echo "<td style='text-align:right;'>".store_number_format($sales_price)."</td>";
				echo "<td style='text-align:right;'>".store_number_format($purchase_price)."</td>";
				echo "<td style='text-align:right;'>".store_number_format($profit)."</td>";
				//echo "<td style='text-align:right;'>".(store_number_format($net_profit))."</td>";
				echo "</tr>";
				$tot_purchase_price+=$purchase_price;
				$tot_sales_cost+=$sales_price;
				$tot_profit+=$profit;
				$tot_net_profit+=$net_profit;
			}
			echo "<tr>
					  <td class='text-right text-bold' colspan='4'><b>Total :</b></td>
					  <td class='text-right text-bold'>".(store_number_format($tot_sales_cost))."</td>
					  <td class='text-right text-bold'>".(store_number_format($tot_purchase_price))."</td>
					  <td class='text-right text-bold'>".(store_number_format($tot_profit))."</td>
					  
				  </tr>";
		}
		else{
			echo "<tr>";
			echo "<td class='text-center text-danger' colspan=7>No Records Found</td>";
			echo "</tr>";
		}
		
		exit;
	}

	public function show_seller_points_report(){
		extract($_POST);

		/*$from_date=date("Y-m-d",strtotime($from_date));
		$to_date=date("Y-m-d",strtotime($to_date));*/
		
		$from_date = system_fromatted_date($from_date);
		$to_date = system_fromatted_date($to_date);

		if(warehouse_module() && warehouse_count()>0 && !empty($warehouse_id)){
			$this->db->where("a.warehouse_id",$warehouse_id);
		}
		
		$this->db->select("a.created_by,c.seller_points, a.id,a.sales_code,a.sales_date,b.customer_name,b.customer_code,a.grand_total,a.paid_amount,a.store_id");
		$this->db->select("c.sales_qty,d.item_name");
		
		
		if($view_all=="no"){
			$this->db->where("(a.sales_date>='$from_date' and a.sales_date<='$to_date')");
		}
//		$this->db->group_by("c.`item_id`");
		$this->db->order_by("a.`sales_date`,a.sales_code",'desc');
		$this->db->from("db_sales as a");
		$this->db->where("a.`id`= c.`sales_id`");
		$this->db->where("a.`sales_status`= 'Final'");
		$this->db->from("db_items as d");
		$this->db->where("d.`id`= c.`item_id`");
		$this->db->from("db_customers as b");
		$this->db->where("b.`id`= a.`customer_id`");
		$this->db->from("db_salesitems as c");
		if(!empty($created_by)){
			$this->db->where("upper(a.created_by)=upper('$created_by')");
		}
		if($item_id!=''){
			$this->db->where("c.item_id=$item_id");
		}
		if(!empty($store_id)){
			$this->db->where("a.store_id",$store_id);
		}
		
		
		//echo $this->db->get_compiled_select();exit();
		
		$q1=$this->db->get();
		if($q1->num_rows()>0){
			$i=0;
			$tot_seller_points=0;
			foreach ($q1->result() as $res1) {
				echo "<tr>";
				echo "<td>".++$i."</td>";
				if(store_module() && is_admin()){
					echo "<td>".get_store_name($res1->store_id)."</td>";	
				}
				if($store_id==get_current_store_id()){
				echo "<td><a title='View Invoice' href='".base_url("sales/invoice/$res1->id")."'>".$res1->sales_code."</a></td>";
				}
				else{
				echo "<td>".$res1->sales_code."</td>";	
				}

				
				echo "<td>".show_date($res1->sales_date)."</td>";
				echo "<td>".$res1->customer_name."</td>";
				echo "<td>".$res1->item_name."</td>";
				echo "<td>".format_qty($res1->sales_qty)."</td>";
				echo "<td>".ucfirst($res1->created_by)."</td>";
				echo "<td>".$res1->seller_points."</td>";
				echo "</tr>";
				$tot_seller_points+=$res1->seller_points;
			}

			$total_columns_count=6;
			if(store_module() && is_admin()){
				$total_columns_count ++;
			}
			if(warehouse_module() && warehouse_count()>0){
				$total_columns_count ++;
			}

			echo "<tr>
					  <td class='text-right text-bold' colspan='$total_columns_count'><b>Total :</b></td>
					  <td class='text-left text-bold'>".number_format($tot_seller_points,2)."</td>
				  </tr>";
		}
		else{
			$total_columns_count=7;
			if(store_module() && is_admin()){
				$total_columns_count ++;
			}
			if(warehouse_module() && warehouse_count()>0){
				$total_columns_count ++;
			}
			echo "<tr>";
			echo "<td class='text-center text-danger' colspan='$total_columns_count'>No Records Found</td>";
			echo "</tr>";
		}
		
		exit;
	}//report end

	public function show_sales_tax_report(){
		extract($_POST);
		
		$from_date = system_fromatted_date($from_date);
		$to_date = system_fromatted_date($to_date);


		if(warehouse_module() && warehouse_count()>0 && !empty($warehouse_id)){
				$this->db->where("a.warehouse_id",$warehouse_id);
		}

		$this->db->select("a.warehouse_id,a.store_id,");
		$this->db->select("a.id,a.sales_code,a.sales_date,b.customer_name,a.grand_total,b.tax_number");
		$this->db->select("a.tot_discount_to_all_amt");
		$this->db->select("a.round_off");
		
		/*if($customer_id!=''){	
			$this->db->where("a.customer_id=$customer_id");
		}*/
		
		$this->db->where("(a.sales_date>='$from_date' and a.sales_date<='$to_date')");
		
		$this->db->from("db_sales as a");

		if(!empty($store_id)){
			$this->db->where("a.store_id",$store_id);
		}
		
		$this->db->from("db_customers as b");
		$this->db->where("b.`id`= a.`customer_id`");
		
		//echo $this->db->get_compiled_select();exit();
		
		$q1=$this->db->get();
		if($q1->num_rows()>0){
			$i=0;
			$tot_price_per_unit=0;
			$tot_discount_amt=0;
			$tot_tax_amt=0;
			$tot_round_off=0;
			$tot_grand_total=0;
			foreach ($q1->result() as $res1) {

				/*Find Tax Amount*/
				$q2 = $this->db->select("COALESCE(sum(tax_amt),0) as tax_amt")
								->select("COALESCE(sum(price_per_unit),0) as price_per_unit")
								->select("COALESCE(sum(discount_amt),0) as discount_amt")
								->where("sales_id",$res1->id)->get("db_salesitems")->row();
				$tax_amt = $q2->tax_amt;
				$discount_amt = $q2->discount_amt;
				$price_per_unit = $q2->price_per_unit;

				echo "<tr>";
				echo "<td>".++$i."</td>";
				if(store_module() && is_admin()){
					echo "<td>".get_store_name($res1->store_id)."</td>";	
				}

				if($res1->store_id==get_current_store_id()){
				echo "<td><a data-toggle='tooltip' target='_blank' title='View Invoice' href='".base_url("sales/invoice/$res1->id")."'>".$res1->sales_code."</a></td>";
				}
				else{
				echo "<td>".$res1->sales_code."</td>";	
				}

				echo "<td>".show_date($res1->sales_date)."</td>";
				echo "<td>".$res1->customer_name."</td>";
				echo "<td>".$res1->tax_number."</td>";
				echo "<td class='text-right'>".store_number_format($price_per_unit)."</td>";
				echo "<td class='text-right'>".store_number_format($discount_amt)."</td>";
				echo "<td class='text-right'>".store_number_format($tax_amt)."</td>";
				echo "<td class='text-right'>".store_number_format($res1->round_off)."</td>";
				echo "<td class='text-right'>".store_number_format($res1->grand_total)."</td>";
				echo "</tr>";
				$tot_price_per_unit+=$price_per_unit;
				$tot_discount_amt+=$discount_amt;
				$tot_tax_amt+=$tax_amt;
				$tot_round_off+=$res1->round_off;
				$tot_grand_total+=$res1->grand_total;

			}

			$total_columns_count=5;
			if(store_module() && is_admin()){
				$total_columns_count ++;
			}
			
			echo "<tr>
					  <td class='text-right text-bold' colspan='$total_columns_count'><b>Total :</b></td>
					  <td class='text-right text-bold'>".store_number_format($tot_price_per_unit)."</td>
					  <td class='text-right text-bold'>".store_number_format($tot_discount_amt)."</td>
					  <td class='text-right text-bold'>".store_number_format($tot_tax_amt)."</td>
					  <td class='text-right text-bold'>".store_number_format($tot_round_off)."</td>
					  <td class='text-right text-bold'>".store_number_format($tot_grand_total)."</td>
				  </tr>";
		}
		else{
			$total_columns_count=10;
			if(store_module() && is_admin()){
				$total_columns_count ++;
			}
			echo "<tr>";
			echo "<td class='text-center text-danger' colspan='$total_columns_count'>No Records Found</td>";
			echo "</tr>";
		}
		
		exit;
	}//end

	public function show_purchase_tax_report(){
		extract($_POST);
		
		$from_date = system_fromatted_date($from_date);
		$to_date = system_fromatted_date($to_date);


		if(warehouse_module() && warehouse_count()>0 && !empty($warehouse_id)){
				$this->db->where("a.warehouse_id",$warehouse_id);
		}

		$this->db->select("a.warehouse_id,a.store_id,");
		$this->db->select("a.id,a.purchase_code,a.purchase_date,b.supplier_name,a.grand_total,b.tax_number");
		$this->db->select("a.tot_discount_to_all_amt");
		$this->db->select("a.round_off");
		
		/*if($supplier_id!=''){	
			$this->db->where("a.supplier_id=$supplier_id");
		}*/
		
		$this->db->where("(a.purchase_date>='$from_date' and a.purchase_date<='$to_date')");
		
		$this->db->from("db_purchase as a");

		if(!empty($store_id)){
			$this->db->where("a.store_id",$store_id);
		}
		
		$this->db->from("db_suppliers as b");
		$this->db->where("b.`id`= a.`supplier_id`");
		
		//echo $this->db->get_compiled_select();exit();
		
		$q1=$this->db->get();
		if($q1->num_rows()>0){
			$i=0;
			$tot_price_per_unit=0;
			$tot_discount_amt=0;
			$tot_tax_amt=0;
			$tot_round_off=0;
			$tot_grand_total=0;
			foreach ($q1->result() as $res1) {

				/*Find Tax Amount*/
				$q2 = $this->db->select("COALESCE(sum(tax_amt),0) as tax_amt")
								->select("COALESCE(sum(price_per_unit),0) as price_per_unit")
								->select("COALESCE(sum(discount_amt),0) as discount_amt")
								->where("purchase_id",$res1->id)->get("db_purchaseitems")->row();
				$tax_amt = $q2->tax_amt;
				$discount_amt = $q2->discount_amt;
				$price_per_unit = $q2->price_per_unit;

				echo "<tr>";
				echo "<td>".++$i."</td>";
				if(store_module() && is_admin()){
					echo "<td>".get_store_name($res1->store_id)."</td>";	
				}

				if($res1->store_id==get_current_store_id()){
				echo "<td><a data-toggle='tooltip' target='_blank' title='View Invoice' href='".base_url("purchase/invoice/$res1->id")."'>".$res1->purchase_code."</a></td>";
				}
				else{
				echo "<td>".$res1->purchase_code."</td>";	
				}

				echo "<td>".show_date($res1->purchase_date)."</td>";
				echo "<td>".$res1->supplier_name."</td>";
				echo "<td>".$res1->tax_number."</td>";
				echo "<td class='text-right'>".store_number_format($price_per_unit)."</td>";
				echo "<td class='text-right'>".store_number_format($discount_amt)."</td>";
				echo "<td class='text-right'>".store_number_format($tax_amt)."</td>";
				echo "<td class='text-right'>".store_number_format($res1->round_off)."</td>";
				echo "<td class='text-right'>".store_number_format($res1->grand_total)."</td>";
				echo "</tr>";
				$tot_price_per_unit+=$price_per_unit;
				$tot_discount_amt+=$discount_amt;
				$tot_tax_amt+=$tax_amt;
				$tot_round_off+=$res1->round_off;
				$tot_grand_total+=$res1->grand_total;

			}

			$total_columns_count=5;
			if(store_module() && is_admin()){
				$total_columns_count ++;
			}
			
			echo "<tr>
					  <td class='text-right text-bold' colspan='$total_columns_count'><b>Total :</b></td>
					  <td class='text-right text-bold'>".store_number_format($tot_price_per_unit)."</td>
					  <td class='text-right text-bold'>".store_number_format($tot_discount_amt)."</td>
					  <td class='text-right text-bold'>".store_number_format($tot_tax_amt)."</td>
					  <td class='text-right text-bold'>".store_number_format($tot_round_off)."</td>
					  <td class='text-right text-bold'>".store_number_format($tot_grand_total)."</td>
				  </tr>";
		}
		else{
			$total_columns_count=10;
			if(store_module() && is_admin()){
				$total_columns_count ++;
			}
			echo "<tr>";
			echo "<td class='text-center text-danger' colspan='$total_columns_count'>No Records Found</td>";
			echo "</tr>";
		}
		
		exit;
	}//end

	public function show_gstr_1_report(){
		extract($_POST);
		
		$from_date = system_fromatted_date($from_date);
		$to_date = system_fromatted_date($to_date);


		if(warehouse_module() && warehouse_count()>0 && !empty($warehouse_id)){
				$this->db->where("a.warehouse_id",$warehouse_id);
		}

		$this->db->select("a.warehouse_id,a.store_id,a.return_bit");
		$this->db->select("a.id,a.sales_code,a.sales_date,b.customer_name,a.grand_total,b.gstin,a.customer_id,b.state_id,a.coupon_amt");
		$this->db->select("a.tot_discount_to_all_amt");
		$this->db->select("a.round_off");
		
		
		$this->db->where("(a.sales_date>='$from_date' and a.sales_date<='$to_date')");
		
		$this->db->from("db_sales as a");

		if(!empty($store_id)){
			$this->db->where("a.store_id",$store_id);
		}
		
		$this->db->join("db_customers as b","b.`id`= a.`customer_id`","left");
		
		
		//echo $this->db->get_compiled_select();exit();
		
		$q1=$this->db->get();
		if($q1->num_rows()>0){
			$i=0;
			$tot_price_per_unit=0;
			$tot_discount_amt=0;
			$tot_tax_amt=0;
			$tot_round_off=0;
			$tot_grand_total=0;

			$tot_cgst_amt=0;
			$tot_sgst_amt=0;
			$tot_igst_amt=0;

			foreach ($q1->result() as $res1) {
				$coupon_amt = $res1->coupon_amt;

				/*Find Tax Amount*/
				$q2 = $this->db->select("COALESCE(sum(tax_amt),0) as tax_amt,tax_id")
								->select("COALESCE(sum(price_per_unit),0) as price_per_unit")
								->select("COALESCE(sum(discount_amt),0) as discount_amt")
								->where("sales_id",$res1->id)->get("db_salesitems")->row();
				$tax_amt = $q2->tax_amt;
				$discount_amt = $q2->discount_amt;
				$discount_amt += $coupon_amt;
				$price_per_unit = $q2->price_per_unit;


				/*Find Customer State*/
				$customer_state='';
				if(!empty($res1->state_id)){
					$customer_state=$this->db->query("select state from db_states where id='".$res1->state_id."'")->row()->state;
				}

				/*Set GST type*/
				$sgst_amt =$cgst_amt=$igst_amt = 0;

				$total_before_tax = $res1->grand_total - $discount_amt - $tax_amt;

				$total_after_tax = $total_before_tax + $tax_amt;

				

				if(empty($customer_state) || (strtoupper($customer_state) == strtoupper(get_store_details($res1->store_id)->state))){
					$sgst_amt = $cgst_amt = $tax_amt / 2;
				}else{
					$sgst_amt = $cgst_amt = 0;
					$igst_amt = $tax_amt;
				}


				echo "<tr>";
				echo "<td>".++$i."</td>";
				if(store_module() && is_admin()){
					echo "<td>".get_store_name($res1->store_id)."</td>";	
				}

				$return_label = ($res1->return_bit==1) ? '<br><span class="text-danger">(Returned)</span>' : '';
				if($res1->store_id==get_current_store_id()){
				echo "<td><a data-toggle='tooltip' target='_blank' title='View Invoice' href='".base_url("sales/invoice/$res1->id")."'>".$res1->sales_code."</a>".$return_label."</td>";
				}
				else{
				echo "<td>".$res1->sales_code.$return_label."</td>";	
				}

				echo "<td>".show_date($res1->sales_date)."</td>";
				echo "<td>".$res1->customer_name."</td>";
				echo "<td>".$res1->gstin."</td>";
				echo "<td class='text-right'>".store_number_format($price_per_unit)."</td>";
				echo "<td class='text-right'>".store_number_format($discount_amt)."</td>";
				echo "<td class='text-right'>".get_tax_details($q2->tax_id)->tax_name."</td>";
				echo "<td class='text-right'>".store_number_format($cgst_amt)."</td>";
				echo "<td class='text-right'>".store_number_format($sgst_amt)."</td>";
				echo "<td class='text-right'>".store_number_format($igst_amt)."</td>";

				echo "<td class='text-right'>".store_number_format($res1->round_off)."</td>";
				echo "<td class='text-right'>".store_number_format($res1->grand_total)."</td>";
				echo "</tr>";
				$tot_price_per_unit+=$price_per_unit;
				$tot_discount_amt+=$discount_amt;
				$tot_tax_amt+=$tax_amt;
				$tot_round_off+=$res1->round_off;
				$tot_grand_total+=$res1->grand_total;

				$tot_cgst_amt+=$cgst_amt;
				$tot_sgst_amt+=$sgst_amt;
				$tot_igst_amt+=$igst_amt;


			}

			$total_columns_count=5;
			if(store_module() && is_admin()){
				$total_columns_count ++;
			}
			
			echo "<tr>
					  <td class='text-right text-bold' colspan='$total_columns_count'><b>Total :</b></td>
					  <td class='text-right text-bold'>".store_number_format($tot_price_per_unit)."</td>
					  <td class='text-right text-bold'>".store_number_format($tot_discount_amt)."</td>
					  <td class='text-right text-bold'></td>
					  <td class='text-right text-bold'>".store_number_format($tot_cgst_amt)."</td>
					  <td class='text-right text-bold'>".store_number_format($tot_sgst_amt)."</td>
					  <td class='text-right text-bold'>".store_number_format($tot_igst_amt)."</td>
					  <td class='text-right text-bold'>".store_number_format($tot_round_off)."</td>
					  <td class='text-right text-bold'>".store_number_format($tot_grand_total)."</td>
				  </tr>";
		}
		else{
			$total_columns_count=13;
			if(store_module() && is_admin()){
				$total_columns_count ++;
			}
			echo "<tr>";
			echo "<td class='text-center text-danger' colspan='$total_columns_count'>No Records Found</td>";
			echo "</tr>";
		}
		
		exit;
	}//end
	
	public function show_gstr_2_report(){
		extract($_POST);
		
		$from_date = system_fromatted_date($from_date);
		$to_date = system_fromatted_date($to_date);


		if(warehouse_module() && warehouse_count()>0 && !empty($warehouse_id)){
				$this->db->where("a.warehouse_id",$warehouse_id);
		}

		$this->db->select("a.warehouse_id,a.store_id,");
		$this->db->select("a.id,a.purchase_code,a.purchase_date,b.supplier_name,a.grand_total,b.tax_number,a.supplier_id,b.state_id");
		$this->db->select("a.tot_discount_to_all_amt");
		$this->db->select("a.round_off");
		
		
		$this->db->where("(a.purchase_date>='$from_date' and a.purchase_date<='$to_date')");
		
		$this->db->from("db_purchase as a");

		if(!empty($store_id)){
			$this->db->where("a.store_id",$store_id);
		}
		
		$this->db->from("db_suppliers as b");
		$this->db->where("b.`id`= a.`supplier_id`");
		
		//echo $this->db->get_compiled_select();exit();
		
		$q1=$this->db->get();
		if($q1->num_rows()>0){
			$i=0;
			$tot_price_per_unit=0;
			$tot_discount_amt=0;
			$tot_tax_amt=0;
			$tot_round_off=0;
			$tot_grand_total=0;

			$tot_cgst_amt=0;
			$tot_sgst_amt=0;
			$tot_igst_amt=0;

			foreach ($q1->result() as $res1) {

				/*Find Tax Amount*/
				$q2 = $this->db->select("COALESCE(sum(tax_amt),0) as tax_amt,tax_id")
								->select("COALESCE(sum(price_per_unit),0) as price_per_unit")
								->select("COALESCE(sum(discount_amt),0) as discount_amt")
								->where("purchase_id",$res1->id)->get("db_purchaseitems")->row();
				$tax_amt = $q2->tax_amt;
				$discount_amt = $q2->discount_amt;
				$price_per_unit = $q2->price_per_unit;


				/*Find supplier State*/
				$supplier_state='';
				if(!empty($res1->state_id)){
					$supplier_state=$this->db->query("select state from db_states where id='".$res1->state_id."'")->row()->state;
				}

				/*Set GST type*/
				$sgst_amt =$cgst_amt=$igst_amt = 0;

				$total_before_tax = $res1->grand_total - $discount_amt - $tax_amt;

				$total_after_tax = $total_before_tax + $tax_amt;

				
				
				if(empty($supplier_state) || (strtoupper($supplier_state) == strtoupper(get_store_details($res1->store_id)->state))){
					$sgst_amt = $cgst_amt = $tax_amt / 2;
				}else{
					$sgst_amt = $cgst_amt = 0;
					$igst_amt = $tax_amt;
				}


				echo "<tr>";
				echo "<td>".++$i."</td>";
				if(store_module() && is_admin()){
					echo "<td>".get_store_name($res1->store_id)."</td>";	
				}

				if($res1->store_id==get_current_store_id()){
				echo "<td><a data-toggle='tooltip' target='_blank' title='View Invoice' href='".base_url("purchase/invoice/$res1->id")."'>".$res1->purchase_code."</a></td>";
				}
				else{
				echo "<td>".$res1->purchase_code."</td>";	
				}

				echo "<td>".show_date($res1->purchase_date)."</td>";
				echo "<td>".$res1->supplier_name."</td>";
				echo "<td>".$res1->tax_number."</td>";
				echo "<td class='text-right'>".store_number_format($price_per_unit)."</td>";
				echo "<td class='text-right'>".store_number_format($discount_amt)."</td>";
				echo "<td class='text-right'>".get_tax_details($q2->tax_id)->tax_name."</td>";
				echo "<td class='text-right'>".store_number_format($cgst_amt)."</td>";
				echo "<td class='text-right'>".store_number_format($sgst_amt)."</td>";
				echo "<td class='text-right'>".store_number_format($igst_amt)."</td>";

				echo "<td class='text-right'>".store_number_format($res1->round_off)."</td>";
				echo "<td class='text-right'>".store_number_format($res1->grand_total)."</td>";
				echo "</tr>";
				$tot_price_per_unit+=$price_per_unit;
				$tot_discount_amt+=$discount_amt;
				$tot_tax_amt+=$tax_amt;
				$tot_round_off+=$res1->round_off;
				$tot_grand_total+=$res1->grand_total;

				$tot_cgst_amt+=$cgst_amt;
				$tot_sgst_amt+=$sgst_amt;
				$tot_igst_amt+=$igst_amt;


			}

			$total_columns_count=5;
			if(store_module() && is_admin()){
				$total_columns_count ++;
			}
			
			echo "<tr>
					  <td class='text-right text-bold' colspan='$total_columns_count'><b>Total :</b></td>
					  <td class='text-right text-bold'>".store_number_format($tot_price_per_unit)."</td>
					  <td class='text-right text-bold'>".store_number_format($tot_discount_amt)."</td>
					  <td class='text-right text-bold'></td>
					  <td class='text-right text-bold'>".store_number_format($tot_cgst_amt)."</td>
					  <td class='text-right text-bold'>".store_number_format($tot_sgst_amt)."</td>
					  <td class='text-right text-bold'>".store_number_format($tot_igst_amt)."</td>
					  <td class='text-right text-bold'>".store_number_format($tot_round_off)."</td>
					  <td class='text-right text-bold'>".store_number_format($tot_grand_total)."</td>
				  </tr>";
		}
		else{
			$total_columns_count=13;
			if(store_module() && is_admin()){
				$total_columns_count ++;
			}
			echo "<tr>";
			echo "<td class='text-center text-danger' colspan='$total_columns_count'>No Records Found</td>";
			echo "</tr>";
		}
		
		exit;
	}//end


	public function show_customer_orders(){
		extract($_POST);

		
		$within_date = (!empty($within_date)) ? system_fromatted_date($within_date) : '';
	
		$this->db->select("*");
		$this->db->from("db_customers");
		if(!empty($store_id)){
			$this->db->where("store_id",$store_id);
		}
		if($customer_id!=''){
			$this->db->where("id=$customer_id");
		}
		//echo $this->db->get_compiled_select();exit();
		
		$q1=$this->db->get();

		//print_r($q1);exit;
		if($q1->num_rows()>0){

			$i=0;
			foreach ($q1->result() as $res1) {

				$this->db->select("sales_date,sales_code,id")
						->from("db_sales")
						->where("customer_id",$res1->id);
				if(!empty($within_date)){
					$this->db->where("sales_date<='$within_date'");
				}
				$this->db->order_by("sales_date",'asc');
						/*echo "<br> ".$this->db->get_compiled_select();
						continue;*/
						
						$q2 = $this->db->get();

						//echo "<br> ".$q2->num_rows();
						//continue;
				if($q2->num_rows()>0){
					
						//$res2 = $q2->row();

						foreach($q2->result() as $res2){
							$date_difference = date_difference($res2->sales_date,date("Y-m-d"));

							echo "<tr>";
							echo "<td>".++$i."</td>";
							if(store_module() && is_admin()){
								echo "<td>".get_store_name($res1->store_id)."</td>";	
							}
							echo "<td>".$res1->customer_name."</td>";
							echo "<td>".show_date($res2->sales_date)."</td>";
							
							if($store_id==get_current_store_id()){
							echo "<td><a title='View Invoice' href='".base_url("sales/invoice/$res2->id")."'>".$res2->sales_code."</a></td>";
							}
							else{
							echo "<td>".$res2->sales_code."</td>";	
							}
							echo "<td>".$date_difference."</td>";
							echo "</tr>";
						}
						
				}
				

			}

		}
		else{
			$total_columns_count=5;
			if(store_module() && is_admin()){
				$total_columns_count ++;
			}
			if(warehouse_module() && warehouse_count()>0){
				$total_columns_count ++;
			}
			echo "<tr>";
			echo "<td class='text-center text-danger' colspan='$total_columns_count'>No Records Found</td>";
			echo "</tr>";
		}
		
		exit;
	}

	public function show_delivery_sheet(){
		extract($_POST);

		/*$from_date=date("Y-m-d",strtotime($from_date));
		$to_date=date("Y-m-d",strtotime($to_date));*/
		
		$from_date = system_fromatted_date($from_date);
		$to_date = system_fromatted_date($to_date);

		$this->db->select("a.id,a.sales_code,a.sales_date,b.customer_name,a.grand_total,a.paid_amount,a.store_id,a.created_time");
		
		if($customer_id!=''){
			
			$this->db->where("a.customer_id=$customer_id");
		}
		
		$this->db->where("(a.sales_date>='$from_date' and a.sales_date<='$to_date')");
		
		$this->db->where("b.`id`= a.`customer_id`");
		$this->db->from("db_sales as a");
		$this->db->where("a.`sales_status`= 'Final'");

		if(!empty($store_id)){
			$this->db->where("a.store_id",$store_id);
		}
		
		$this->db->from("db_customers as b");
		
		//echo $this->db->get_compiled_select();exit();
		
		$q1=$this->db->get();
		if($q1->num_rows()>0){
			$i=0;
			$tot_grand_total=0;
			$tot_paid_amount=0;
			$due_amount=0;
			$tot_due_amount=0;
			$tot_sales_qty=0;
			$tot_return_qty=0;
			foreach ($q1->result() as $res1) {

				$tot_grand_total+=$res1->grand_total;
				$tot_paid_amount+=$res1->paid_amount;
				

				//Find the Sales items of the item
				$q3 = $this->db->select("coalesce(sum(sales_qty),0) as sales_qty")
								->where("sales_id",$res1->id)
								->get("db_salesitems")->row();
				$sales_qty = $q3->sales_qty;

				$q4 = $this->db->select("coalesce(sum(grand_total),0) as grand_total")
								->select("coalesce(sum(paid_amount),0) as paid_amount")
								->where("sales_id",$res1->id)
								->get("db_salesreturn")->row();

				//Find the return of the item
				$q2 = $this->db->select("coalesce(sum(return_qty),0) as return_qty")
								->where("sales_id",$res1->id)
								->get("db_salesitemsreturn")->row();

				$return_qty = $q2->return_qty;

				$return_tot = $q4->grand_total;

				$invoices_tot = $res1->grand_total - $return_tot;

				$due_amount=($res1->grand_total-$res1->paid_amount);
				$due_amount-=($q4->paid_amount);
				$tot_due_amount+=$due_amount;


				echo "<tr>";
				echo "<td>".++$i."</td>";
				if(store_module() && is_admin()){
					echo "<td>".get_store_name($res1->store_id)."</td>";	
				}
				
				echo "<td>".show_date($res1->sales_date)."</td>";
				echo "<td>".$res1->created_time."</td>";

				if($store_id==get_current_store_id()){
				echo "<td><a title='View Invoice' href='".base_url("sales/invoice/$res1->id")."'>".$res1->sales_code."</a></td>";
				}
				else{
				echo "<td>".$res1->sales_code."</td>";	
				}
				
				echo "<td>".$res1->customer_name."</td>";
				echo "<td class='text-left'>".format_qty($sales_qty)."</td>";
				echo "<td class='text-left'>".format_qty($return_qty)."</td>";
				echo "<td class='text-right'>".store_number_format($invoices_tot)."</td>";
				echo "<td class='text-right'>".store_number_format($due_amount)."</td>";
				
				echo "</tr>";

				$tot_return_qty+=$return_qty;
				$tot_sales_qty+=$sales_qty;
				

			}

			$total_columns_count=5;
			if(store_module() && is_admin()){
				$total_columns_count ++;
			}
			
			echo "<tr>
					  <td class='text-right text-bold' colspan='$total_columns_count'><b>Total :</b></td>
					  <td class='text-left text-bold'>".format_qty($tot_sales_qty)."</td>
					  <td class='text-left text-bold'>".format_qty($tot_return_qty)."</td>
					  <td class='text-right text-bold'>".store_number_format($tot_grand_total)."</td>
					  <td class='text-right text-bold'>".store_number_format($tot_due_amount)."</td>
					  
				  </tr>";
		}
		else{
			$total_columns_count=9;
			if(store_module() && is_admin()){
				$total_columns_count ++;
			}
			
			echo "<tr>";
			echo "<td class='text-center text-danger' colspan='$total_columns_count'>No Records Found</td>";
			echo "</tr>";
		}
		
		exit;
	}


	public function show_load_sheet(){
		extract($_POST);

		
		$from_date = system_fromatted_date($from_date);
		$to_date = system_fromatted_date($to_date);

		
		$this->db->select("a.id,a.sales_code,a.sales_date,b.customer_name,b.customer_code,a.grand_total,a.paid_amount,a.store_id");
		$this->db->select("sum(c.sales_qty) as sales_qty");
		$this->db->select("d.item_name,d.brand_id");
		
		
			$this->db->where("(a.sales_date>='$from_date' and a.sales_date<='$to_date')");
		$this->db->order_by("a.`sales_date`,a.sales_code",'desc');
		$this->db->from("db_sales as a");
		$this->db->where("a.`id`= c.`sales_id`");
		$this->db->where("a.`sales_status`= 'Final'");
		$this->db->from("db_items as d");
		$this->db->where("d.`id`= c.`item_id`");
		$this->db->from("db_customers as b");
		$this->db->where("b.`id`= a.`customer_id`");
		$this->db->from("db_salesitems as c");
		$this->db->group_by("c.item_id");
		
		if(!empty($store_id)){
			$this->db->where("a.store_id",$store_id);
		}
		
		
		//echo $this->db->get_compiled_select();exit();
		
		$q1=$this->db->get();
		if($q1->num_rows()>0){
			$i=0;
			
			$tot_qty=0;
			foreach ($q1->result() as $res1) {

				$brand_name = (!empty($res1->brand_id)) ? get_brand_details($res1->brand_id)->brand_name : '';

				echo "<tr>";
				echo "<td>".++$i."</td>";
				if(store_module() && is_admin()){
					echo "<td>".get_store_name($res1->store_id)."</td>";	
				}
							
				echo "<td>".show_date($res1->sales_date)."</td>";
				echo "<td>".$brand_name."</td>";
				echo "<td>".$res1->item_name."</td>";
				echo "<td>".format_qty($res1->sales_qty)."</td>";
				echo "</tr>";

				$tot_qty+=$res1->sales_qty;

			}

			$total_columns_count=4;
			if(store_module() && is_admin()){
				$total_columns_count ++;
			}
		

			echo "<tr>
					  <td class='text-right text-bold' colspan='$total_columns_count'><b>Total :</b></td>
					  <td class='text-left text-bold'>".format_qty($tot_qty)."</td>
				  </tr>";
		}
		else{
			$total_columns_count=5;
			if(store_module() && is_admin()){
				$total_columns_count ++;
			}
			
			echo "<tr>";
			echo "<td class='text-center text-danger' colspan='$total_columns_count'>No Records Found</td>";
			echo "</tr>";
		}
		
		exit;
	}
	public function show_sales_gst_report(){
		extract($_POST);
		
		$from_date = system_fromatted_date($from_date);
		$to_date = system_fromatted_date($to_date);


		/*if(warehouse_module() && warehouse_count()>0 && !empty($warehouse_id)){
				$this->db->where("a.warehouse_id",$warehouse_id);
		}*/

		if(!empty($customer_id)){
			$this->db->where("a.customer_id",$customer_id);
		}
		if(!empty($store_id)){
			$this->db->where("a.store_id",$store_id);
		}
		if(!empty($sales_type_id)){
			$this->db->where("a.sales_type_id",$sales_type_id);
		}

		$this->db->select("
							a.store_id,
							a.id,
							a.sales_code,
							a.sales_date,
							a.customer_id,
							a.round_off,
							a.coupon_amt,

							b.state_id,
							b.gstin,
							b.customer_name,

							c.item_id,
							c.sales_qty,
							c.price_per_unit,
							c.tax_type,
							c.tax_id,
							c.tax_amt,
							c.discount_type,
							c.discount_input,
							c.discount_amt,
							c.unit_total_cost,
							c.total_cost,

							d.item_name,
							d.hsn,

							t.tax_name,
							t.tax,

							s.state,
							
							st.sales_type_name,
							st.sales_type_initial,
						");
		
		
		$this->db->from("db_sales as a");
		$this->db->where("(a.sales_date>='$from_date' and a.sales_date<='$to_date')");
		//Customer - Join
		$this->db->join("db_customers as b","b.`id`= a.`customer_id`","left");
		//Sales Items - Join
		$this->db->join("db_salesitems as c","c.`sales_id`= a.id","left");
		//Item Details - Join
		$this->db->join("db_items as d","d.`id`= c.item_id","left");
		//Tax - Join
		$this->db->join("db_tax as t","t.`id`= c.tax_id","left");
		//Store - Join
		$this->db->join("db_store as s","s.`id`= a.store_id","left");
		//Sales Type - Join
		$this->db->join("db_sales_types as st","st.`id`= a.sales_type_id","left");

		//echo $this->db->get_compiled_select();exit();
		
		$q1=$this->db->get();
		if($q1->num_rows()>0){
			$i=0;
			$tot_price_per_unit=0;
			$tot_discount_amt=0;
			$tot_tax_amt=0;
			$tot_round_off=0;
			$tot_total_cost=0;
			$tot_sales_qty=0;
			$tot_taxable=0;

			$tot_cgst_amt = $tot_sgst_amt = $tot_igst_amt = 0;

			foreach ($q1->result() as $res1) {
				//Coupon amount
				$coupon_amt = $res1->coupon_amt;
				//Taxable
				$taxable = $res1->price_per_unit-$res1->tax_amt;

				//Customer State
				/*Find Customer State*/
				$customer_state='';
				if(!empty($res1->state_id)){
					$customer_state=$this->db->query("select state from db_states where id='".$res1->state_id."'")->row()->state;
				}

				//validate GST Type
				$gst_type = (strtoupper($customer_state)==strtoupper($res1->state)) ? "GST" : "IGST";

				//Set GST type
				//if($gst_type=='GST'){//Within state
				if(empty($customer_state) || (strtoupper($customer_state) == strtoupper(get_store_details($res1->store_id)->state))){
					$gst_tax_per = ($res1->tax/2);
					$cgst_amt = ($res1->tax_amt / 2);
					$sgst_amt = $cgst_amt;
					$igst_amt =0;
					$igst_tax_per = 0;
				}else{//IGST
					$igst_tax_per = $res1->tax;
					$igst_amt = $res1->tax_amt;
					$cgst_amt = 0;
					$sgst_amt = 0;
					$gst_tax_per=0;
				}


				echo "<tr>";
				echo "<td>".++$i."</td>";
				if(store_module() && is_admin()){
					echo "<td>".get_store_name($res1->store_id)."</td>";	
				}

				if($res1->store_id==get_current_store_id()){
				echo "<td><a data-toggle='tooltip' target='_blank' title='View Invoice' href='".base_url("sales/invoice/$res1->id")."'>".$res1->sales_code."</a></td>";
				}
				else{
				echo "<td>".$res1->sales_code."</td>";	
				}

				echo "<td>".($res1->sales_type_name ? $res1->sales_type_name : '-')."</td>";
				echo "<td>".$res1->customer_name."</td>";
				echo "<td>".$res1->gstin."</td>";
				echo "<td>".show_date($res1->sales_date)."</td>";
				echo "<td>".$res1->item_name."</td>";
				echo "<td>".format_qty($res1->sales_qty)."</td>";
				echo "<td>".$res1->hsn."</td>";
				echo "<td class='text-right'>".store_number_format($res1->price_per_unit)."</td>";
				echo "<td class='text-right'>".store_number_format($res1->discount_amt+$coupon_amt)."</td>";
				echo "<td class='text-right'>".store_number_format($taxable)."</td>";
				echo "<td class='text-right'>".$res1->tax_name."</td>";
				echo "<td class='text-right'>".store_number_format($gst_tax_per)."</td>";
				echo "<td class='text-right'>".store_number_format($cgst_amt)."</td>";
				echo "<td class='text-right'>".store_number_format($gst_tax_per)."</td>";
				echo "<td class='text-right'>".store_number_format($sgst_amt)."</td>";
				echo "<td class='text-right'>".store_number_format($igst_tax_per)."</td>";
				echo "<td class='text-right'>".store_number_format($igst_amt)."</td>";

				echo "<td class='text-right'>".store_number_format($res1->round_off)."</td>";
				echo "<td class='text-right'>".store_number_format($res1->total_cost)."</td>";
				echo "</tr>";
				$tot_price_per_unit+=$res1->price_per_unit;
				$tot_discount_amt+=$res1->discount_amt+$coupon_amt;
				$tot_tax_amt+=$res1->tax_amt;
				$tot_round_off+=$res1->round_off;
				$tot_total_cost+=$res1->total_cost;
				$tot_sales_qty+=$res1->sales_qty;
				$tot_taxable+=$taxable;

				$tot_cgst_amt+=$cgst_amt;
				$tot_sgst_amt+=$sgst_amt;
				$tot_igst_amt+=$igst_amt;


			}

			$total_columns_count=6;
			if(store_module() && is_admin()){
				$total_columns_count ++;
			}
			
			echo "<tr>
					  <td class='text-right text-bold' colspan='$total_columns_count'><b>Total :</b></td>
					  <td class='text-right text-bold'>".format_qty($tot_sales_qty)."</td>
					  <td class='text-right text-bold'></td>
					  <td class='text-right text-bold'>".store_number_format($tot_price_per_unit)."</td>
					  <td class='text-right text-bold'>".store_number_format($tot_discount_amt)."</td>
					  <td class='text-right text-bold'>".store_number_format($tot_taxable)."</td>
					  <td class='text-right text-bold'></td>
					  <td class='text-right text-bold'></td>
					  <td class='text-right text-bold'>".store_number_format($tot_cgst_amt)."</td>
					  <td class='text-right text-bold'></td>
					  <td class='text-right text-bold'>".store_number_format($tot_sgst_amt)."</td>
					  <td class='text-right text-bold'></td>
					  <td class='text-right text-bold'>".store_number_format($tot_igst_amt)."</td>

					  <td class='text-right text-bold'>".store_number_format($tot_round_off)."</td>
					  <td class='text-right text-bold'>".store_number_format($tot_total_cost)."</td>
				  </tr>";
		}
		else{
			$total_columns_count=21;
			if(store_module() && is_admin()){
				$total_columns_count ++;
			}
			echo "<tr>";
			echo "<td class='text-center text-danger' colspan='$total_columns_count'>No Records Found</td>";
			echo "</tr>";
		}
		
		exit;
	}//end

	public function show_purchase_gst_report(){
		extract($_POST);
		
		$from_date = system_fromatted_date($from_date);
		$to_date = system_fromatted_date($to_date);


		/*if(warehouse_module() && warehouse_count()>0 && !empty($warehouse_id)){
				$this->db->where("a.warehouse_id",$warehouse_id);
		}*/

		if(!empty($supplier_id)){
			$this->db->where("a.supplier_id",$supplier_id);
		}
		if(!empty($store_id)){
			$this->db->where("a.store_id",$store_id);
		}

		$this->db->select("
							a.store_id,
							a.id,
							a.purchase_code,
							a.purchase_date,
							a.supplier_id,
							a.round_off,

							b.state_id,
							b.gstin,
							b.supplier_name,

							c.item_id,
							c.purchase_qty,
							c.price_per_unit,
							c.tax_type,
							c.tax_id,
							c.tax_amt,
							c.discount_type,
							c.discount_input,
							c.discount_amt,
							c.unit_total_cost,
							c.total_cost,

							d.item_name,
							d.hsn,

							t.tax_name,
							t.tax,

							s.state,
						");
		
		
		$this->db->from("db_purchase as a");
		$this->db->where("(a.purchase_date>='$from_date' and a.purchase_date<='$to_date')");
		//supplier - Join
		$this->db->join("db_suppliers as b","b.`id`= a.`supplier_id`","left");
		//purchase Items - Join
		$this->db->join("db_purchaseitems as c","c.`purchase_id`= a.id","left");
		//Item Details - Join
		$this->db->join("db_items as d","d.`id`= c.item_id","left");
		//Tax - Join
		$this->db->join("db_tax as t","t.`id`= c.tax_id","left");
		//Store - Join
		$this->db->join("db_store as s","s.`id`= a.store_id","left");

		//echo $this->db->get_compiled_select();exit();
		
		$q1=$this->db->get();
		if($q1->num_rows()>0){
			$i=0;
			$tot_price_per_unit=0;
			$tot_discount_amt=0;
			$tot_tax_amt=0;
			$tot_round_off=0;
			$tot_total_cost=0;
			$tot_purchase_qty=0;
			$tot_taxable=0;

			$tot_cgst_amt = $tot_sgst_amt = $tot_igst_amt = 0;

			foreach ($q1->result() as $res1) {

				//Taxable
				$taxable = $res1->price_per_unit-$res1->tax_amt;

				//validate GST Type
				$supplier_state='';
				if(!empty($res1->state_id)){
					$supplier_state=$this->db->query("select state from db_states where id='".$res1->state_id."'")->row()->state;
				}

				//$gst_type = (strtoupper($supplier_state)==strtoupper($res1->state)) ? "GST" : "IGST";

				//Set GST type
				//if($gst_type=='GST'){//Within state

				if(empty($supplier_state) || (strtoupper($supplier_state) == strtoupper(get_store_details($res1->store_id)->state))){
					$gst_tax_per = ($res1->tax/2);
					$cgst_amt = ($res1->tax_amt / 2);
					$sgst_amt = $cgst_amt;
					$igst_amt =0;
					$igst_tax_per =0;
				}else{//IGST
					$igst_tax_per = $res1->tax;
					$igst_amt = $res1->tax_amt;
					$cgst_amt = 0;
					$sgst_amt = 0;
					$gst_tax_per = 0;
				}


				echo "<tr>";
				echo "<td>".++$i."</td>";
				if(store_module() && is_admin()){
					echo "<td>".get_store_name($res1->store_id)."</td>";	
				}

				if($res1->store_id==get_current_store_id()){
				echo "<td><a data-toggle='tooltip' target='_blank' title='View Invoice' href='".base_url("purchase/invoice/$res1->id")."'>".$res1->purchase_code."</a></td>";
				}
				else{
				echo "<td>".$res1->purchase_code."</td>";	
				}

				echo "<td>".$res1->supplier_name."</td>";
				echo "<td>".$res1->gstin."</td>";
				echo "<td>".show_date($res1->purchase_date)."</td>";
				echo "<td>".$res1->item_name."</td>";
				echo "<td>".format_qty($res1->purchase_qty)."</td>";
				echo "<td>".$res1->hsn."</td>";
				echo "<td class='text-right'>".store_number_format($res1->price_per_unit)."</td>";
				echo "<td class='text-right'>".store_number_format($res1->discount_amt)."</td>";
				echo "<td class='text-right'>".store_number_format($taxable)."</td>";
				echo "<td class='text-right'>".$res1->tax_name."</td>";
				echo "<td class='text-right'>".store_number_format($gst_tax_per)."</td>";
				echo "<td class='text-right'>".store_number_format($cgst_amt)."</td>";
				echo "<td class='text-right'>".store_number_format($gst_tax_per)."</td>";
				echo "<td class='text-right'>".store_number_format($sgst_amt)."</td>";
				echo "<td class='text-right'>".store_number_format($igst_tax_per)."</td>";
				echo "<td class='text-right'>".store_number_format($igst_amt)."</td>";

				echo "<td class='text-right'>".store_number_format($res1->round_off)."</td>";
				echo "<td class='text-right'>".store_number_format($res1->total_cost)."</td>";
				echo "</tr>";
				$tot_price_per_unit+=$res1->price_per_unit;
				$tot_discount_amt+=$res1->discount_amt;
				$tot_tax_amt+=$res1->tax_amt;
				$tot_round_off+=$res1->round_off;
				$tot_total_cost+=$res1->total_cost;
				$tot_purchase_qty+=$res1->purchase_qty;
				$tot_taxable+=$taxable;

				$tot_cgst_amt+=$cgst_amt;
				$tot_sgst_amt+=$sgst_amt;
				$tot_igst_amt+=$igst_amt;


			}

			$total_columns_count=6;
			if(store_module() && is_admin()){
				$total_columns_count ++;
			}
			
			echo "<tr>
					  <td class='text-right text-bold' colspan='$total_columns_count'><b>Total :</b></td>
					  <td class='text-left text-bold'>".format_qty($tot_purchase_qty)."</td>
					  <td class='text-right text-bold'></td>
					  <td class='text-right text-bold'>".store_number_format($tot_price_per_unit)."</td>
					  <td class='text-right text-bold'>".store_number_format($tot_discount_amt)."</td>
					  <td class='text-right text-bold'>".store_number_format($tot_taxable)."</td>
					  <td class='text-right text-bold'></td>
					  <td class='text-right text-bold'></td>
					  <td class='text-right text-bold'>".store_number_format($tot_cgst_amt)."</td>
					  <td class='text-right text-bold'></td>
					  <td class='text-right text-bold'>".store_number_format($tot_sgst_amt)."</td>
					  <td class='text-right text-bold'></td>
					  <td class='text-right text-bold'>".store_number_format($tot_igst_amt)."</td>
					  
					  <td class='text-right text-bold'>".store_number_format($tot_round_off)."</td>
					  <td class='text-right text-bold'>".store_number_format($tot_total_cost)."</td>
				  </tr>";
		}
		else{
			$total_columns_count=20;
			if(store_module() && is_admin()){
				$total_columns_count ++;
			}
			echo "<tr>";
			echo "<td class='text-center text-danger' colspan='$total_columns_count'>No Records Found</td>";
			echo "</tr>";
		}
		
		exit;
	}//end


	public function show_return_items_report(){
		extract($_POST);
		

		
		$from_date = system_fromatted_date($from_date);
		$to_date = system_fromatted_date($to_date);

		
		$this->db->select("a.id,a.return_date,a.return_code,b.customer_name,a.store_id,a.return_status");
		$this->db->from("db_salesreturn a");
		$this->db->where("(a.return_date>='$from_date' and a.return_date<='$to_date')");
		if(!empty($warehouse_id)){
			$this->db->where("a.warehouse_id",$warehouse_id);
		}
		if(!empty($store_id)){
			$this->db->where("a.store_id",$store_id);
		}
		$this->db->join("db_customers b","b.`id`= a.`customer_id`",'left');
		$this->db->order_by("a.`return_date`,a.return_code",'desc');
		//echo $this->db->get_compiled_select();exit();
		$q1 = $this->db->get();
		if($q1->num_rows()>0){
			$tot_total_cost=0;
			
			foreach ($q1->result() as $res1) {
				$sales_id = $res1->id;

				$this->db->select(" c.total_cost,
									c.return_qty,
									d.item_name,
								");
				//Join
				$this->db->from("db_salesitemsreturn c");
				$this->db->where("c.return_id",$sales_id);
				if($item_id!=''){
					$this->db->where("c.item_id=$item_id");
				}
				$this->db->join("db_items d","d.`id`= c.`item_id`","left");
				//echo $this->db->get_compiled_select();exit();
				$q2=$this->db->get();

					if($q2->num_rows()>0){
					$i=0;
					
					
					foreach ($q2->result() as $res2) {
							echo "<tr>";
							echo "<td>".++$i."</td>";
							if(store_module() && is_admin()){
								echo "<td>".get_store_name($res1->store_id)."</td>";	
							}
							if($store_id==get_current_store_id()){
							echo "<td><a title='View Invoice' href='".base_url("sales_return/invoice/$res1->id")."'>".$res1->return_code."</a></td>";
							}
							else{
							echo "<td>".$res1->return_code."</td>";	
							}

							
							echo "<td>".show_date($res1->return_date)."</td>";
							echo "<td>".$res1->return_status."</td>";
							echo "<td>".$res1->customer_name."</td>";
							echo "<td>".$res2->item_name."</td>";
							echo "<td>".format_qty($res2->return_qty)."</td>";
							echo "<td class='text-right'>".store_number_format($res2->total_cost)."</td>";
							echo "</tr>";
							
							$tot_total_cost+=$res2->total_cost;

					}// foreach $res2
					//Print Total
					$total_columns_count=6;
					if(store_module() && is_admin()){
						$total_columns_count ++;
					}
					if(warehouse_module() && warehouse_count()>0){
						$total_columns_count ++;
					}

					
				}//num_rows $q2
				else{
					echo "No Records Found";
				}


			}//foreach $res1

			echo "<tr>
							  <td class='text-right text-bold' colspan='$total_columns_count'><b>Total :</b></td>
							  <td class='text-right text-bold'>".store_number_format($tot_total_cost)."</td>
							  
						  </tr>";

		}//num_rows
		else{
			$total_columns_count=8;
			if(store_module() && is_admin()){
				$total_columns_count ++;
			}
			if(warehouse_module() && warehouse_count()>0){
				$total_columns_count ++;
			}
			echo "<tr>";
			echo "<td class='text-center text-danger' colspan='$total_columns_count'>No Records Found</td>";
			echo "</tr>";
		}
	}

	public function sales_and_payments_report(){
		extract($_POST);

		$from_date = (!empty($from_date)) ? system_fromatted_date($from_date) : '';
		$to_date = (!empty($to_date)) ? system_fromatted_date($to_date) : '';

		$i=0;
			  $tot_qty=0;
			  $tot_sales_price=0;
			  
			  $tot_total_cost=0;
			  $tot_payment=0;

			  $str1= " SELECT 
									a.id as id,
									a.`sales_date` AS tr_date,
									'invoice' as tr_type,
									a.sales_code as tr_code,
									a.`created_time` AS created_time
								FROM 
									db_sales a";
						$str1.=" WHERE 
						a.customer_id =".$customer_id ;
					if(!empty($from_date) && !empty($to_date)){
						$str1.="  and
									(sales_date>='".$from_date."' and sales_date<='".$to_date."') ";
					}
					$str1.=" UNION ";

					$str1.= " SELECT 
									b.id as id, 
									b.`payment_date` AS tr_date,
									'payments' as tr_type,
									b.payment_code as tr_code,
									b.`created_time` AS created_time
								FROM 
									`db_salespayments` b";
									$str1.=" WHERE 
						b.customer_id =".$customer_id ;
					if(!empty($from_date) && !empty($to_date)){
						$str1.="  and
									(b.payment_date>='".$from_date."' and b.payment_date<='".$to_date."') ";
					}

					//$str1.=" ORDER BY `tr_date`,`id`,`created_time` ";
					$str1.=" ORDER BY `tr_date`,`created_time` ";
				//echo $str1;exit;
			  $q1 = $this->db->query($str1);

			$tot_qty = 0;
			$tot_total_cost = 0;
			$tot_payment=0;
			$tot_bal=0;
			if($q1->num_rows()>0){
				foreach ($q1->result() as $res2){

					$details = ($res2->tr_type ==	'invoice') ? 
									get_sales_details($res2->id)
									: get_sales_payment_details($res2->id);
					
					if($res2->tr_type=='invoice'){
						$this->db->select("b.item_name");
						$this->db->from("db_salesitems a");
						$this->db->where("a.sales_id=",$res2->id);	
						$this->db->join("db_items b","b.id=a.item_id","left");
						//echo $this->db->get_compiled_select();
						$item_name = $this->db->get()->row()->item_name;	

					}
					else{
						$item_name = '';
					}
					

					if($res2->tr_type=='payments'){
						if($details->payment==0){
							continue;
						}

					}

					
					echo "<tr>";  
					  echo "<td colspan='1' class='text-center'>".++$i."</td>";

					  echo "<td colspan='1'>";
						echo show_date($res2->tr_date);
					  echo "</td>";

					  echo "<td colspan='1'>";
					  $str2 = '';
					  if($res2->tr_type!='invoice'){
						$str2 = "<br><b>Bill Ref.No :<b>".get_sales_details($details->sales_id)->reference_no;
					  }
						echo ($res2->tr_type=='invoice') ? $res2->tr_code : 'Receive'.$str2;
					  echo "</td>";

					  echo "<td colspan='1'>";
						//echo $res2->tr_code;
						//Show sales code for the payment
						if($res2->tr_type=='payments'){
							echo get_sales_details(get_sales_payment_details($res2->id)->sales_id)->sales_code;
						}
						  if(!empty(isset($details->reference_no))){
							echo $details->reference_no."<br>";          
						  }
					  echo "</td>";

					  echo "<td colspan='1'>";
						//echo $res2->item_name;  
						if($res2->tr_type=='payments')  {
						  echo "Payment Type: ".$details->payment_type."<br>";
						  if(!empty(trim($details->payment_note))){
							  echo "<b>Note:</b><i>".nl2br($details->payment_note)."</i>";
						  }	
						}
						else{
							echo "<b>Item:</b><i>".$item_name."</i><br>";
						}
						
					  echo "</td>";

					  echo "<td colspan='1'>";
						if($res2->tr_type=='invoice'){
							$res3 = $this->db->query("select coalesce(sum(sales_qty),0) as tot_sales_qty from db_salesitems where sales_id=".$res2->id);
							echo format_qty($res3->row()->tot_sales_qty) ;
						}
						else{
							echo format_qty(0);
						}
					  echo "</td>";

					  echo "<td class='text-right' colspan='1'>";
						if($res2->tr_type=='invoice'){
							echo store_number_format($details->grand_total);
						}
						else{
							echo store_number_format(0);
						}
						
					  echo "</td>";

					  echo "<td class='text-right' colspan='1'>";
						if($res2->tr_type=='payments'){
							echo store_number_format($details->payment);
						}
						else{
							echo store_number_format(0);
						}
					  echo "</td>";

					  /*echo "<td  class='text-right' colspan='1'>";
						echo store_number_format(0);
					  echo "</td>";*/

					  echo "<td  class='text-right' colspan='1'>";
							$tot_bal = ($res2->tr_type=='invoice') ? $details->grand_total+$tot_bal : $tot_bal-$details->payment ;
						echo store_number_format($tot_bal);
					  echo "</td>";

				   
					  echo "</tr>";
					  if($res2->tr_type=='invoice'){
						$tot_qty +=$res3->row()->tot_sales_qty;
						$tot_total_cost +=$details->grand_total;
					  }
					  else{
						$tot_payment +=$details->payment;
					  }
					

				}
			}
			else{
				echo "<tr><td colspan='8'>No Records Found</td></tr>";
			}

			/*************************************/
			//Total
			  echo "<tr>";
				echo "<td class='text-right' colspan='5'>";
					echo "Total";
				echo "</td>";
				echo "<td>";
					echo format_qty($tot_qty);
				echo "</td>";
				echo "<td class='text-right'>";
					echo store_number_format($tot_total_cost);
				echo "</td>";
				echo "<td class='text-right'>";
					echo store_number_format($tot_payment);
				echo "</td>";
				/*echo "<td class='text-right'>";
					echo store_number_format($tot_total_cost-$tot_payment);
				echo "</td>";*/
				echo "<td class='text-right'>";
					echo store_number_format($tot_bal);
				echo "</td>";

			  echo "</tr>";


	}

	public function show_stock_transfer_report(){
		extract($_POST);

		
		$from_date = system_fromatted_date($from_date);
		$to_date = system_fromatted_date($to_date);

		$item_id = isset($item_id) ? $item_id : "";

		$this->db->where("a.warehouse_from",$from_warehouse);
		$this->db->where("a.warehouse_to",$to_warehouse);
		
		$this->db->select("a.id,a.transfer_date,a.created_by,a.store_id,e.category_name,f.brand_name");
		$this->db->select("c.transfer_qty,d.item_name");
		$this->db->select("w1.warehouse_name as from_warehouse");
		$this->db->select("w2.warehouse_name as to_warehouse");
		
		
		$this->db->from("db_stocktransfer as a");
		
		
		$this->db->where("(a.transfer_date>='$from_date' and a.transfer_date<='$to_date')");
		
		if(!empty($store_id)){
			$this->db->where("a.store_id",$store_id);
		}
		$this->db->order_by("a.`transfer_date`",'desc');

		$str = "" ; 


		if($item_id!=''){
			$str = " and c.item_id=$item_id"; 
		}
		$this->db->join("db_stocktransferitems as c","c.stocktransfer_id = a.id $str","left");

		
		$this->db->join("db_items as d","d.`id`= c.`item_id`","left");

		$str = "" ; 
		if($category_id!=''){
			$str = " and e.id=$category_id"; 
		}

		$this->db->join("db_category as e","e.`id`= d.`category_id` $str","right");
			
		$str ="";

		if($brand_id!=''){
			$str = " and f.id=$brand_id"; 
		}

		$this->db->join("db_brands as f","f.`id`= d.`brand_id` $str","right");

		$this->db->join("db_warehouse as w1","w1.`id`= c.`warehouse_from`","right");
		$this->db->join("db_warehouse as w2","w2.`id`= c.`warehouse_to`","right");


		
		//echo $this->db->get_compiled_select();exit();
		
		$q1=$this->db->get();
		if($q1->num_rows()>0){
			$i=0;
			
			$tot_transfer_qty=0;
			foreach ($q1->result() as $res1) {
				echo "<tr>";
				echo "<td>".++$i."</td>";
				if(store_module() && is_admin()){
					echo "<td>".get_store_name($res1->store_id)."</td>";	
				}
				
				echo "<td>".show_date($res1->transfer_date)."</td>";
				echo "<td>".$res1->from_warehouse."</td>";
				echo "<td>".$res1->to_warehouse."</td>";
				echo "<td>".$res1->item_name."</td>";
				echo "<td>".$res1->category_name."</td>";
				echo "<td>".$res1->brand_name."</td>";
				echo "<td>".format_qty($res1->transfer_qty)."</td>";
				
				echo "</tr>";
				$tot_transfer_qty+=$res1->transfer_qty;
				
			}

			$total_columns_count=7;
			if(store_module() && is_admin()){
				$total_columns_count ++;
			}
			

			echo "<tr>
					  <td class='text-right text-bold' colspan='$total_columns_count'><b>Total :</b></td>
					  <td class='text-left text-bold'>".format_qty($tot_sales_qty)."</td>
				  </tr>";
		}
		else{
			$total_columns_count=9;
			if(store_module() && is_admin()){
				$total_columns_count ++;
			}
			
			echo "<tr>";
			echo "<td class='text-center text-danger' colspan='$total_columns_count'>No Records Found</td>";
			echo "</tr>";
		}
		
		exit;
	}


	public function show_sales_summary_report(){
		extract($_POST);

		
		$from_date = system_fromatted_date($from_date);
		$to_date = system_fromatted_date($to_date);

		$item_id = isset($item_id) ? $item_id : "";
		$item_type = isset($item_type) ? $item_type : "";

		if(warehouse_module() && warehouse_count()>0 && !empty($warehouse_id)){
			$this->db->where("a.warehouse_id",$warehouse_id);
		}
		
		$this->db->select("a.id,a.store_id,e.category_name");
		$this->db->select("sum(c.sales_qty) as sales_qty,d.item_name");
		
		
		$this->db->from("db_sales as a");
		
		$this->db->where("a.`sales_status`= 'Final'");
		
		
		$this->db->where("(a.sales_date>='$from_date' and a.sales_date<='$to_date')");

		if(!empty($store_id)){
			$this->db->where("a.store_id",$store_id);
		}

		$this->db->group_by("c.`item_id`");

		$str = "" ; 
		if($item_id!=''){
			$str = " and c.item_id=$item_id"; 
		}
		$this->db->join("db_salesitems as c","c.sales_id = a.id $str","left");

		$this->db->join("db_items as d","d.`id`= c.`item_id`","left");

		if(!empty($item_type)){
			$this->db->where("service_bit", (($item_type=="Items") ? 0 : 1) );
		}

		$str = "" ; 
		if($category_id!=''){
			$str = " and e.id=$category_id"; 
		}

		$this->db->join("db_category as e","e.`id`= d.`category_id` $str","right");
		
		//echo $this->db->get_compiled_select();exit();
		
		$q1=$this->db->get();
		if($q1->num_rows()>0){
			$i=0;
			
			$tot_sales_qty=0;
			foreach ($q1->result() as $res1) {
				echo "<tr>";
				echo "<td>".++$i."</td>";
				if(store_module() && is_admin()){
					echo "<td>".get_store_name($res1->store_id)."</td>";	
				}
				
				echo "<td>".$res1->item_name."</td>";
				echo "<td>".$res1->category_name."</td>";
				echo "<td>".format_qty($res1->sales_qty)."</td>";
				
				echo "</tr>";
				$tot_sales_qty+=$res1->sales_qty;
				
			}

			$total_columns_count=2;
			if(store_module() && is_admin()){
				$total_columns_count ++;
			}
			if(warehouse_module() && warehouse_count()>0){
				$total_columns_count ++;
			}

			echo "<tr>
					  <td class='text-right text-bold' colspan='$total_columns_count'><b>Total :</b></td>
					  <td class='text-left text-bold'>".format_qty($tot_sales_qty)."</td>
				  </tr>";
		}
		else{
			$total_columns_count=3;
			if(store_module() && is_admin()){
				$total_columns_count ++;
			}
			if(warehouse_module() && warehouse_count()>0){
				$total_columns_count ++;
			}
			echo "<tr>";
			echo "<td class='text-center text-danger' colspan='$total_columns_count'>No Records Found</td>";
			echo "</tr>";
		}
		
		exit;
	}

	public function show_billwise_report(){
		extract($_POST);
		
		// Set default values
		$customer_id = isset($customer_id) ? $customer_id : '';
		$store_id = isset($store_id) ? $store_id : '';
		$warehouse_id = isset($warehouse_id) ? $warehouse_id : '';
		$created_by = isset($created_by) ? $created_by : '';
		$reference_no = isset($reference_no) ? $reference_no : '';
		$from_time = isset($from_time) ? $from_time : '00:00';
		$to_time = isset($to_time) ? $to_time : '23:59';
		
		// Validate required fields
		if(!isset($from_date) || !isset($to_date)){
			echo "<tr><td colspan='12' class='text-center text-danger'>Date range is required</td></tr>";
			exit;
		}

		$from_date = system_fromatted_date($from_date);
		$to_date = system_fromatted_date($to_date);

		$this->db->select("a.id, a.warehouse_id, a.sales_code, a.sales_date, a.created_time, a.reference_no, b.customer_name, b.customer_code, 
						   a.grand_total, a.paid_amount, a.store_id, a.created_by, a.sales_note,
						   (a.grand_total - a.paid_amount) as due_amount");
		
		if($customer_id != ''){
			$this->db->where("a.customer_id",$customer_id);
		}
		if($store_id != ''){
			$this->db->where("a.store_id",$store_id);
		}
		if($warehouse_id != ''){
			$this->db->where("a.warehouse_id",$warehouse_id);
		}
		if($created_by != ''){
			$this->db->where("a.created_by",$created_by);
		}
		if($reference_no != ''){
			$this->db->like("a.reference_no",$reference_no);
		}
		
		$this->db->from("db_sales a");
		$this->db->from("db_customers b");
		$this->db->where("(a.sales_date >= '$from_date' and a.sales_date <= '$to_date')");
		
		// Add time filter based on created_time field (format: "02:32:14 pm")
		if($from_time != '00:00' || $to_time != '23:59'){
			// Convert 24-hour format to 12-hour format for comparison
			$from_time_12 = date("h:i:s A", strtotime($from_time));
			$to_time_12 = date("h:i:s A", strtotime($to_time));
			$this->db->where("(STR_TO_DATE(a.created_time, '%h:%i:%s %p') >= STR_TO_DATE('$from_time_12', '%h:%i:%s %p') 
			                   AND STR_TO_DATE(a.created_time, '%h:%i:%s %p') <= STR_TO_DATE('$to_time_12', '%h:%i:%s %p'))");
		}
		
		$this->db->where("a.customer_id=b.id");
		$this->db->where("a.status", 1);
		$this->db->order_by("a.sales_date", "desc");
		$this->db->order_by("a.id", "desc");

		$q1=$this->db->get();
		
		if($q1->num_rows()>0){
			$i=0;
			$tot_grand_total=0;
			$tot_paid_amount=0;
			$tot_due_amount=0;
			$tot_profit=0;
			
			foreach ($q1->result() as $res1) {
				
				// Calculate profit for this sales record
				$this->db->select("COALESCE(SUM(si.purchase_price * si.sales_qty), 0) as total_cost");
				$this->db->from("db_salesitems si");
				$this->db->where("si.sales_id", $res1->id);
				$this->db->where("si.status", 1);
				$cost_query = $this->db->get();
				$cost_result = $cost_query->row();
				$total_cost = $cost_result ? $cost_result->total_cost : 0;
				$profit = $res1->grand_total - $total_cost;
				
				echo "<tr>";
				echo "<td>".++$i."</td>";
				
				if(store_module() && is_admin()){
					echo "<td>".get_store_name($res1->store_id)."</td>";	
				}
				if(warehouse_module() && warehouse_count()>0){
					echo "<td>".get_warehouse_name($res1->warehouse_id)."</td>";	
				}
				
				echo "<td><a title='View Invoice' href='".base_url("sales/invoice/$res1->id")."'>".$res1->sales_code."</a></td>";
				echo "<td>".show_date($res1->sales_date)."</td>";
				echo "<td>".(!empty($res1->reference_no) ? $res1->reference_no : '-')."</td>";
				echo "<td>".$res1->customer_name."</td>";
				echo "<td>".$res1->customer_code."</td>";
				echo "<td class='text-right'>".store_number_format($res1->grand_total)."</td>";
				echo "<td class='text-right'>".store_number_format($res1->paid_amount)."</td>";
				echo "<td class='text-right'>".store_number_format($res1->due_amount)."</td>";
				echo "<td class='text-right'>".store_number_format($profit)."</td>";
				echo "<td>".(!empty($res1->sales_note) ? ucfirst($res1->sales_note) : '-')."</td>";
			   echo "<td>".ucfirst($res1->created_by)."</td>";
				echo "</tr>";

				$tot_grand_total+=$res1->grand_total;
				$tot_paid_amount+=$res1->paid_amount;
				$tot_due_amount+=$res1->due_amount;
				$tot_profit+=$profit;
			}

			$total_columns_count=6; // # + Sales Code + Date + Reference No + Customer Name + Customer Code (base 6 columns)
			if(store_module() && is_admin()){
				$total_columns_count ++; // Add Store column
			}
			if(warehouse_module() && warehouse_count()>0){
				$total_columns_count ++; // Add Warehouse column  
			}

			echo "<tr class='bg-gray'>
					  <td class='text-right text-bold' colspan='$total_columns_count'><b>Total :</b></td>
					  <td class='text-right text-bold'>".store_number_format($tot_grand_total)."</td>
					  <td class='text-right text-bold'>".store_number_format($tot_paid_amount)."</td>
					  <td class='text-right text-bold'>".store_number_format($tot_due_amount)."</td>
					  <td class='text-right text-bold'>".store_number_format($tot_profit)."</td>
					  <td colspan='2'></td>
				  </tr>";
		}
		else{
			$total_columns_count=13; // Updated to include Reference No column
			if(store_module() && is_admin()){
				$total_columns_count ++;
			}
			if(warehouse_module() && warehouse_count()>0){
				$total_columns_count ++;
			}
			echo "<tr>";
			echo "<td class='text-center text-danger' colspan='$total_columns_count'>No Records Found</td>";
			echo "</tr>";
		}
		
		exit;
	}

	public function show_itemwise_report(){
		extract($_POST);
		
		// Set default values
		$customer_id = isset($customer_id) ? $customer_id : '';
		$store_id = isset($store_id) ? $store_id : '';
		$warehouse_id = isset($warehouse_id) ? $warehouse_id : '';
		$created_by = isset($created_by) ? $created_by : '';
		$reference_no = isset($reference_no) ? $reference_no : '';
		$item_id = isset($item_id) ? $item_id : '';
		$category_id = isset($category_id) ? $category_id : '';
		$brand_id = isset($brand_id) ? $brand_id : '';
		$from_time = isset($from_time) ? $from_time : '00:00';
		$to_time = isset($to_time) ? $to_time : '23:59';
		
		// Validate required fields
		if(!isset($from_date) || !isset($to_date)){
			echo "<tr><td colspan='15' class='text-center text-danger'>Date range is required</td></tr>";
			exit;
		}

		$from_date = system_fromatted_date($from_date);
		$to_date = system_fromatted_date($to_date);

		$this->db->select("si.item_id, 
						   SUM(si.sales_qty) as total_qty, 
						   SUM(si.unit_total_cost) as total_amount, 
						   SUM(si.tax_amt) as total_tax, 
						   SUM(si.discount_amt) as total_discount, 
						   SUM(si.purchase_price * si.sales_qty) as total_cost,
						   i.item_name, i.item_code, i.category_id, i.brand_id,
						   cat.category_name, br.brand_name,
						   COUNT(DISTINCT a.id) as invoice_count");
		
		if($customer_id != ''){
			$this->db->where("a.customer_id",$customer_id);
		}
		if($store_id != ''){
			$this->db->where("a.store_id",$store_id);
		}
		if($warehouse_id != ''){
			$this->db->where("a.warehouse_id",$warehouse_id);
		}
		if($created_by != ''){
			$this->db->where("a.created_by",$created_by);
		}
		if($reference_no != ''){
			$this->db->like("a.reference_no",$reference_no);
		}
		if($item_id != ''){
			$this->db->where("si.item_id",$item_id);
		}
		if($category_id != ''){
			$this->db->where("i.category_id",$category_id);
		}
		if($brand_id != ''){
			$this->db->where("i.brand_id",$brand_id);
		}
		
		$this->db->from("db_sales a");
		$this->db->join("db_customers b", "a.customer_id = b.id", "left");
		$this->db->join("db_salesitems si", "a.id = si.sales_id", "left");
		$this->db->join("db_items i", "si.item_id = i.id", "left");
		$this->db->join("db_category cat", "i.category_id = cat.id", "left");
		$this->db->join("db_brands br", "i.brand_id = br.id", "left");
		
		$this->db->where("(a.sales_date >= '$from_date' and a.sales_date <= '$to_date')");
		
		// Add time filter based on created_time field (format: "02:32:14 pm")
		if($from_time != '00:00' || $to_time != '23:59'){
			// Convert 24-hour format to 12-hour format for comparison
			$from_time_12 = date("h:i:s A", strtotime($from_time));
			$to_time_12 = date("h:i:s A", strtotime($to_time));
			$this->db->where("(STR_TO_DATE(a.created_time, '%h:%i:%s %p') >= STR_TO_DATE('$from_time_12', '%h:%i:%s %p') 
			                   AND STR_TO_DATE(a.created_time, '%h:%i:%s %p') <= STR_TO_DATE('$to_time_12', '%h:%i:%s %p'))");
		}
		
		$this->db->where("a.status", 1);
		$this->db->where("si.status", 1);
		$this->db->group_by("si.item_id");
		$this->db->order_by("i.item_code", "asc");

		$q1=$this->db->get();
		
		if($q1->num_rows()>0){
			$i=0;
			$tot_qty=0;
			$tot_amount=0;
			$tot_tax=0;
			$tot_discount=0;
			$tot_cost=0;
			$tot_profit=0;
			
			foreach ($q1->result() as $res1) {
				
				$profit = $res1->total_amount - $res1->total_cost;
				
				echo "<tr>";
				echo "<td>".++$i."</td>";
				
				if(store_module() && is_admin()){
					echo "<td>All Stores</td>";	// Since we're grouping by item, we can't show specific store
				}
				if(warehouse_module() && warehouse_count()>0){
					echo "<td>All Warehouses</td>";	// Since we're grouping by item, we can't show specific warehouse
				}
				
				echo "<td>".$res1->item_code."</td>";
				echo "<td>".$res1->item_name."</td>";
				echo "<td>".(!empty($res1->category_name) ? $res1->category_name : '-')."</td>";
				echo "<td>".(!empty($res1->brand_name) ? $res1->brand_name : '-')."</td>";
				echo "<td class='text-right'>".store_number_format($res1->total_qty)."</td>";
				echo "<td class='text-right'>".store_number_format($res1->total_amount)."</td>";
				echo "<td class='text-right'>".store_number_format($res1->total_tax)."</td>";
				echo "<td class='text-right'>".store_number_format($res1->total_discount)."</td>";
				echo "<td class='text-right'>".store_number_format($profit)."</td>";
				echo "<td class='text-right'>".$res1->invoice_count."</td>";
				echo "</tr>";

				$tot_qty+=$res1->total_qty;
				$tot_amount+=$res1->total_amount;
				$tot_tax+=$res1->total_tax;
				$tot_discount+=$res1->total_discount;
				$tot_cost+=$res1->total_cost;
				$tot_profit+=$profit;
			}

			$total_columns_count=5; // Base columns: # + Item Code + Item Name + Category + Brand minus 2 to align properly
			if(store_module() && is_admin()){
				$total_columns_count ++; // Add Store column
			}
			if(warehouse_module() && warehouse_count()>0){
				$total_columns_count ++; // Add Warehouse column  
			}

			echo "<tr class='bg-gray'>
					  <td class='text-right text-bold' colspan='$total_columns_count'><b>Total :</b></td>
					  <td class='text-right text-bold'>".store_number_format($tot_qty)."</td>
					  <td class='text-right text-bold'>".store_number_format($tot_amount)."</td>
					  <td class='text-right text-bold'>".store_number_format($tot_tax)."</td>
					  <td class='text-right text-bold'>".store_number_format($tot_discount)."</td>
					  <td class='text-right text-bold'>".store_number_format($tot_profit)."</td>
					  <td></td>
				  </tr>";
		}
		else{
			$total_columns_count=12; // Updated to match new column structure
			if(store_module() && is_admin()){
				$total_columns_count ++;
			}
			if(warehouse_module() && warehouse_count()>0){
				$total_columns_count ++;
			}
			echo "<tr>";
			echo "<td class='text-center text-danger' colspan='$total_columns_count'>No Records Found</td>";
			echo "</tr>";
		}
		
		exit;
	}

	public function show_categorywise_report(){
		extract($_POST);
		
		// Set default values
		$customer_id = isset($customer_id) ? $customer_id : '';
		$store_id = isset($store_id) ? $store_id : '';
		$warehouse_id = isset($warehouse_id) ? $warehouse_id : '';
		$created_by = isset($created_by) ? $created_by : '';
		$reference_no = isset($reference_no) ? $reference_no : '';
		$item_id = isset($item_id) ? $item_id : '';
		$category_id = isset($category_id) ? $category_id : '';
		$brand_id = isset($brand_id) ? $brand_id : '';
		$from_time = isset($from_time) ? $from_time : '00:00';
		$to_time = isset($to_time) ? $to_time : '23:59';
		
		// Validate required fields
		if(!isset($from_date) || !isset($to_date)){
			echo "<tr><td colspan='12' class='text-center text-danger'>Date range is required</td></tr>";
			exit;
		}

		$from_date = system_fromatted_date($from_date);
		$to_date = system_fromatted_date($to_date);

		$this->db->select("cat.id as category_id, 
						   cat.category_name,
						   SUM(si.sales_qty) as total_qty, 
						   SUM(si.unit_total_cost) as total_amount, 
						   SUM(si.tax_amt) as total_tax, 
						   SUM(si.discount_amt) as total_discount, 
						   SUM(si.purchase_price * si.sales_qty) as total_cost,
						   COUNT(DISTINCT a.id) as invoice_count,
						   COUNT(DISTINCT si.item_id) as item_count");
		
		if($customer_id != ''){
			$this->db->where("a.customer_id",$customer_id);
		}
		if($store_id != ''){
			$this->db->where("a.store_id",$store_id);
		}
		if($warehouse_id != ''){
			$this->db->where("a.warehouse_id",$warehouse_id);
		}
		if($created_by != ''){
			$this->db->where("a.created_by",$created_by);
		}
		if($reference_no != ''){
			$this->db->like("a.reference_no",$reference_no);
		}
		if($item_id != ''){
			$this->db->where("si.item_id",$item_id);
		}
		if($category_id != ''){
			$this->db->where("cat.id",$category_id);
		}
		if($brand_id != ''){
			$this->db->where("i.brand_id",$brand_id);
		}
		
		$this->db->from("db_sales a");
		$this->db->join("db_customers b", "a.customer_id = b.id", "left");
		$this->db->join("db_salesitems si", "a.id = si.sales_id", "left");
		$this->db->join("db_items i", "si.item_id = i.id", "left");
		$this->db->join("db_category cat", "i.category_id = cat.id", "left");
		$this->db->join("db_brands br", "i.brand_id = br.id", "left");
		
		$this->db->where("(a.sales_date >= '$from_date' and a.sales_date <= '$to_date')");
		
		// Add time filter based on created_time field (format: "02:32:14 pm")
		if($from_time != '00:00' || $to_time != '23:59'){
			// Convert 24-hour format to 12-hour format for comparison
			$from_time_12 = date("h:i:s A", strtotime($from_time));
			$to_time_12 = date("h:i:s A", strtotime($to_time));
			$this->db->where("(STR_TO_DATE(a.created_time, '%h:%i:%s %p') >= STR_TO_DATE('$from_time_12', '%h:%i:%s %p') 
			                   AND STR_TO_DATE(a.created_time, '%h:%i:%s %p') <= STR_TO_DATE('$to_time_12', '%h:%i:%s %p'))");
		}
		
		$this->db->where("a.status", 1);
		$this->db->where("si.status", 1);
		$this->db->group_by("cat.id");
		$this->db->order_by("cat.category_name", "asc");

		$q1=$this->db->get();
		
		if($q1->num_rows()>0){
			$i=0;
			$tot_qty=0;
			$tot_amount=0;
			$tot_tax=0;
			$tot_discount=0;
			$tot_cost=0;
			$tot_profit=0;
			$tot_item_count=0;
			
			foreach ($q1->result() as $res1) {
				
				$profit = $res1->total_amount - $res1->total_cost;
				
				echo "<tr>";
				echo "<td>".++$i."</td>";
				
				if(store_module() && is_admin()){
					echo "<td>All Stores</td>";	// Since we're grouping by category, we can't show specific store
				}
				if(warehouse_module() && warehouse_count()>0){
					echo "<td>All Warehouses</td>";	// Since we're grouping by category, we can't show specific warehouse
				}
				
				echo "<td>".$res1->category_name."</td>";
				echo "<td class='text-right'>".$res1->item_count."</td>";
				echo "<td class='text-right'>".store_number_format($res1->total_qty)."</td>";
				echo "<td class='text-right'>".store_number_format($res1->total_amount)."</td>";
				echo "<td class='text-right'>".store_number_format($res1->total_tax)."</td>";
				echo "<td class='text-right'>".store_number_format($res1->total_discount)."</td>";
				echo "<td class='text-right'>".store_number_format($profit)."</td>";
				echo "<td class='text-right'>".$res1->invoice_count."</td>";
				echo "</tr>";

				$tot_qty+=$res1->total_qty;
				$tot_amount+=$res1->total_amount;
				$tot_tax+=$res1->total_tax;
				$tot_discount+=$res1->total_discount;
				$tot_cost+=$res1->total_cost;
				$tot_profit+=$profit;
				$tot_item_count+=$res1->item_count;
			}

			$total_columns_count=2; // Base columns: # + Category minus 2 to align properly
			if(store_module() && is_admin()){
				$total_columns_count ++; // Add Store column
			}
			if(warehouse_module() && warehouse_count()>0){
				$total_columns_count ++; // Add Warehouse column  
			}

			echo "<tr class='bg-gray'>
					  <td class='text-right text-bold' colspan='$total_columns_count'><b>Total :</b></td>
					  <td class='text-right text-bold'>".$tot_item_count."</td>
					  <td class='text-right text-bold'>".store_number_format($tot_qty)."</td>
					  <td class='text-right text-bold'>".store_number_format($tot_amount)."</td>
					  <td class='text-right text-bold'>".store_number_format($tot_tax)."</td>
					  <td class='text-right text-bold'>".store_number_format($tot_discount)."</td>
					  <td class='text-right text-bold'>".store_number_format($tot_profit)."</td>
					  <td></td>
				  </tr>";
		}
		else{
			$total_columns_count=10; // Updated to match new column structure
			if(store_module() && is_admin()){
				$total_columns_count ++;
			}
			if(warehouse_module() && warehouse_count()>0){
				$total_columns_count ++;
			}
			echo "<tr>";
			echo "<td class='text-center text-danger' colspan='$total_columns_count'>No Records Found</td>";
			echo "</tr>";
		}
		
		exit;
	}

	public function show_woocommerce_orders(){
		extract($_POST);
		
		try {
			// Multi-host resilient connection (handles Access denied on specific host)
			$connection_errors = [];
			$woo_db = $this->woo_multi_connect($connection_errors);
			if(!$woo_db){
				echo "<tr><td colspan='10' class='text-center text-danger'>";
				echo "<strong>WooCommerce Database Connection Failed</strong><br>";
				echo "MySQL user <code>porpatas_smacam_usr25</code> was denied on all attempted hosts.<br>";
				echo "<small>Attempts:<br>".implode('<br>', array_map('htmlspecialchars',$connection_errors))."</small>";
				echo "<div style='margin-top:8px;text-align:left;display:inline-block;font-size:12px;max-width:640px;'>";
				echo "<b>Fix (run in MySQL as root):</b><pre style='white-space:pre;margin:4px 0;padding:6px;background:#f9f9f9;border:1px solid #ddd;'>CREATE USER IF NOT EXISTS 'porpatas_smacam_usr25'@'%' IDENTIFIED BY 'C%Scl%RQvFxVFR2{';\nGRANT SELECT, INSERT, UPDATE, DELETE ON porpatas_smacam_db25.* TO 'porpatas_smacam_usr25'@'%';\nFLUSH PRIVILEGES; </pre>";
				echo "Or in cPanel: Add Remote MySQL Host (your WAMP public IP) and ensure privileges granted.<br>";
				echo "After granting, re-run this report.";
				echo "</div>";
				echo "</td></tr>";
				return;
			}
			
			// Use the exact same query structure as the working SMACamel project
			$sql = "
				SELECT 
					o.id as order_id,
					o.date_created_gmt as post_date,
					o.status as post_status,
					o.total_amount as order_total,
					o.currency as currency,
					o.payment_method_title as payment_method,
					oa_billing.first_name as billing_first_name,
					oa_billing.last_name as billing_last_name,
					oa_billing.email as billing_email,
					oa_billing.phone as billing_phone
				FROM wp_wc_orders o
				LEFT JOIN wp_wc_order_addresses oa_billing ON o.id = oa_billing.order_id AND oa_billing.address_type = 'billing'
				WHERE 1=1
			";

			// Optional: force only shop_order type (exclude refunds etc.)
			// Uncomment if needed: $sql .= " AND o.type='shop_order'";
			
			// Add date filter only if both dates are provided
			if(!empty($from_date) && !empty($to_date)) {
				$from_date_formatted = system_fromatted_date($from_date);
				$to_date_formatted = system_fromatted_date($to_date);
				$sql .= " AND DATE(o.date_created_gmt) >= '" . $from_date_formatted . "' AND DATE(o.date_created_gmt) <= '" . $to_date_formatted . "'";
			}
			
			$sql .= "
				ORDER BY o.date_created_gmt DESC
				LIMIT 500
			";
			
			$q1 = $woo_db->query($sql);

			// If the main query failed, surface the DB error for debugging
			if($q1 === FALSE){
				$db_error = $woo_db->error(); // ['code'=>, 'message'=>]
				echo "<tr><td colspan='10' class='text-center text-danger'>";
				echo "<strong>WooCommerce Orders Query Failed</strong><br>";
				echo "<small>Error Code: ".htmlspecialchars($db_error['code'])." | Message: ".htmlspecialchars($db_error['message'])."</small><br>";
				// Minimal simplified probe query to test table/columns
				$probe_messages = [];
				if($probe = $woo_db->query("SHOW TABLES LIKE 'wp_wc_orders'")){
					$probe_messages[] = ($probe->num_rows()>0?"Table wp_wc_orders exists":"Table wp_wc_orders MISSING");
				}
				if($probe = $woo_db->query("SHOW COLUMNS FROM wp_wc_orders LIKE 'total_amount'")){
					$probe_messages[] = ($probe->num_rows()>0?"Column total_amount exists":"Column total_amount MISSING");
				}
				if($probe = $woo_db->query("SHOW COLUMNS FROM wp_wc_orders LIKE 'payment_method_title'")){
					$probe_messages[] = ($probe->num_rows()>0?"Column payment_method_title exists":"Column payment_method_title MISSING");
				}
				if(!empty($probe_messages)){
					echo "<div style='margin-top:6px;font-size:12px;'>".implode(" | ",$probe_messages)."</div>";
				}
				if(isset($_POST['debug']) && $_POST['debug']=='1'){
					echo "<div style='margin-top:8px;font-size:11px;text-align:left;white-space:pre-wrap;background:#fff5f5;padding:6px;border:1px solid #f1c0c0;'>".htmlspecialchars($sql)."</div>";
				}
				echo "</td></tr>";
				// Attempt legacy fallback even if primary failed
				$this->try_legacy_tables($woo_db, $from_date, $to_date);
				exit; // stop further processing in this branch
			}

			if($q1->num_rows() > 0){
				$this->display_orders_from_db($q1->result(), $from_date, $to_date);
			} else {
				// Collect debug counts before falling back (safely, avoid fatal errors)
				$woo_db->db_debug = FALSE; // suppress CI error output for these optional debug queries
				$debug_counts = [
					'hpos_total' => 0,
					'hpos_shop_orders' => 0,
					'legacy_total' => 0,
				];
				if($cnt = $woo_db->query("SELECT COUNT(*) c FROM wp_wc_orders")) {
					$row = $cnt->row(); if($row) $debug_counts['hpos_total'] = (int)$row->c;
				}
				if($cnt = $woo_db->query("SELECT COUNT(*) c FROM wp_wc_orders WHERE type='shop_order'")) {
					$row = $cnt->row(); if($row) $debug_counts['hpos_shop_orders'] = (int)$row->c;
				}
				if($cnt = $woo_db->query("SELECT COUNT(*) c FROM wp_posts WHERE post_type='shop_order'")) {
					$row = $cnt->row(); if($row) $debug_counts['legacy_total'] = (int)$row->c;
				}

				// If there ARE orders in DB but filtered query empty, show a hint (do not break legacy fallback)
				if(($debug_counts['hpos_total'] + $debug_counts['legacy_total']) > 0 && !empty($from_date) && !empty($to_date)) {
					echo "<tr><td colspan='10' class='text-center text-warning'>";
					echo "<strong>No orders found for date range: $from_date to $to_date</strong><br>";
					echo "<small>HPOS Orders: {$debug_counts['hpos_total']} (shop_order: {$debug_counts['hpos_shop_orders']}) | Legacy Orders: {$debug_counts['legacy_total']}</small>";
					if(isset($_POST['debug']) && $_POST['debug']=='1'){
						echo "<div style='margin-top:6px;font-size:11px;text-align:left;white-space:pre-wrap;background:#f9f9f9;padding:6px;border:1px solid #ddd;'>".htmlspecialchars($sql)."</div>";
					}
					echo "</td></tr>";
				}
				// No results found, try legacy tables as fallback
				$this->try_legacy_tables($woo_db, $from_date, $to_date);
			}
			
		} catch(Exception $e) {
			// Database connection failed, show error with debugging info
			echo "<tr>";
			echo "<td class='text-center text-danger' colspan='10'>";
			echo "<i class='fa fa-exclamation-triangle'></i> ";
			echo "<strong>Database Connection Error</strong><br>";
			echo "Cannot connect to WooCommerce database.<br>";
			echo "<small>Error: " . $e->getMessage() . "</small><br>";
			echo "<div style='margin-top: 10px; padding: 10px; background: #fff3cd; border: 1px solid #ffeaa7; border-radius: 4px;'>";
			echo "<strong>Debugging Help:</strong><br>";
			echo "• <a href='" . base_url('analyze_database.php') . "' target='_blank'>Analyze database structure</a><br>";
			echo "• <a href='" . base_url('test_smacamel_query.php') . "' target='_blank'>Test SMACamel query</a><br>";
			echo "• <a href='" . base_url('create_sample_orders.php') . "' target='_blank'>Create sample orders</a>";
			echo "</div>";
			echo "</td>";
			echo "</tr>";
		}
		
		exit;
	}
	
	private function try_legacy_tables($woo_db, $from_date = '', $to_date = '') {
		try {
			// Fallback to legacy wp_posts table structure
			$sql = "
				SELECT 
					o.ID as order_id,
					o.post_date,
					o.post_status,
					MAX(CASE WHEN pm.meta_key = '_billing_first_name' THEN pm.meta_value END) as billing_first_name,
					MAX(CASE WHEN pm.meta_key = '_billing_last_name' THEN pm.meta_value END) as billing_last_name,
					MAX(CASE WHEN pm.meta_key = '_billing_email' THEN pm.meta_value END) as billing_email,
					MAX(CASE WHEN pm.meta_key = '_billing_phone' THEN pm.meta_value END) as billing_phone,
					MAX(CASE WHEN pm.meta_key = '_order_total' THEN pm.meta_value END) as order_total,
					MAX(CASE WHEN pm.meta_key = '_payment_method_title' THEN pm.meta_value END) as payment_method,
					MAX(CASE WHEN pm.meta_key = '_order_currency' THEN pm.meta_value END) as currency
				FROM wp_posts o
				LEFT JOIN wp_postmeta pm ON o.ID = pm.post_id
				WHERE o.post_type = 'shop_order'
			";
			
			// Add date filter only if both dates are provided
			if(!empty($from_date) && !empty($to_date)) {
				$from_date_formatted = system_fromatted_date($from_date);
				$to_date_formatted = system_fromatted_date($to_date);
				$sql .= " AND DATE(o.post_date) >= '" . $from_date_formatted . "' AND DATE(o.post_date) <= '" . $to_date_formatted . "'";
			}
			
			$sql .= "
				GROUP BY o.ID
				ORDER BY o.post_date DESC
				LIMIT 500
			";
			
			$q1 = $woo_db->query($sql);
			
			if($q1 && $q1->num_rows() > 0){
				$this->display_orders_from_db($q1->result(), $from_date, $to_date);
			} else {
				// Check if any orders exist at all
				$count_query = $woo_db->query("SELECT COUNT(*) as total FROM wp_wc_orders");
				$hpos_count = $count_query ? $count_query->row()->total : 0;
				
				$legacy_count_query = $woo_db->query("SELECT COUNT(*) as total FROM wp_posts WHERE post_type = 'shop_order'");
				$legacy_count = $legacy_count_query ? $legacy_count_query->row()->total : 0;
				
				echo "<tr>";
				echo "<td class='text-center text-info' colspan='10'>";
				echo "<i class='fa fa-info-circle'></i> ";
				if(!empty($from_date) && !empty($to_date)) {
					echo "<strong>No orders found for date range: $from_date to $to_date</strong><br>";
				} else {
					echo "<strong>No WooCommerce orders found</strong><br>";
				}
				echo "<small>HPOS Orders: $hpos_count | Legacy Orders: $legacy_count</small><br>";
				
				if ($hpos_count == 0 && $legacy_count == 0) {
					echo "<div style='margin-top: 10px; padding: 10px; background: #fff3cd; border: 1px solid #ffeaa7; border-radius: 4px;'>";
					echo "<strong>Database is empty!</strong><br>";
					echo "• <a href='" . base_url('create_sample_orders.php') . "' target='_blank'>Create sample orders</a><br>";
					echo "• <a href='" . base_url('debug_orders.php') . "' target='_blank'>Debug database content</a><br>";
					echo "• Place test orders in your WooCommerce store";
					echo "</div>";
				} else {
					echo "<small><a href='javascript:void(0)' onclick='$(\"#from_date\").val(\"\"); $(\"#to_date\").val(\"\"); show_report();'>Show all orders</a></small>";
				}
				echo "</td>";
				echo "</tr>";
			}
			
		} catch(Exception $e) {
			echo "<tr>";
			echo "<td class='text-center text-danger' colspan='10'>";
			echo "<i class='fa fa-exclamation-triangle'></i> ";
			echo "<strong>Database Query Error</strong><br>";
			echo "<small>Error: " . $e->getMessage() . "</small>";
			echo "</td>";
			echo "</tr>";
		}
	}
	
	private function display_orders_from_db($orders, $from_date = '', $to_date = '') {
		$i = 0;
		$total_amount = 0;
		
		foreach ($orders as $res1) {
			
			// Clean up order status - handle both HPOS and legacy formats
			$status = isset($res1->post_status) ? $res1->post_status : (isset($res1->status) ? $res1->status : 'unknown');
			$status = str_replace('wc-', '', $status);
			$status = ucfirst($status);
			
			// Status color coding
			$status_class = 'default';
			switch(strtolower($status)) {
				case 'completed': $status_class = 'success'; break;
				case 'processing': $status_class = 'warning'; break;
				case 'on-hold': $status_class = 'info'; break;
				case 'pending': $status_class = 'primary'; break;
				case 'cancelled': 
				case 'refunded': 
				case 'failed': $status_class = 'danger'; break;
			}
			
			// Format customer name
			$customer_name = trim(($res1->billing_first_name ?: '') . ' ' . ($res1->billing_last_name ?: ''));
			if(empty($customer_name)) {
				$customer_name = 'Guest Customer';
			}
			
			// Format order total
			$order_total = floatval($res1->order_total ?: 0);
			$total_amount += $order_total;
			
			// Handle date format - HPOS uses date_created_gmt, legacy uses post_date
			$order_date = isset($res1->post_date) ? $res1->post_date : (isset($res1->date_created_gmt) ? $res1->date_created_gmt : date('Y-m-d H:i:s'));
			
			echo "<tr>";
			echo "<td>".++$i."</td>";
			echo "<td><strong>#".$res1->order_id."</strong></td>";
			echo "<td>".date('d-m-Y', strtotime($order_date))."</td>";
			echo "<td>".date('h:i A', strtotime($order_date))."</td>";
			echo "<td>".$customer_name."</td>";
			echo "<td>".($res1->billing_email ?: 'N/A')."</td>";
			echo "<td>".($res1->billing_phone ?: 'N/A')."</td>";
			echo "<td><span class='label label-".$status_class."'>".$status."</span></td>";
			echo "<td class='text-right'><strong>".($res1->currency ?: 'INR')." ".number_format($order_total, 2)."</strong></td>";
			echo "<td>".($res1->payment_method ?: 'N/A')."</td>";
			echo "</tr>";
		}
		
		$date_range = '';
		if(!empty($from_date) && !empty($to_date)) {
			$date_range = " (From: $from_date to $to_date)";
		} else {
			$date_range = " (Latest 500 Orders)";
		}
		
		echo "<tr class='bg-gray'>
				  <td class='text-right text-bold' colspan='8'><b>Showing: $i orders$date_range | Total Amount:</b></td>
				  <td class='text-right text-bold'><strong>INR ".number_format($total_amount, 2)."</strong></td>
				  <td></td>
			  </tr>";
	}
	
	private function try_proxy_connection($from_date = '', $to_date = '') {
		// Try to fetch via proxy/API
		$proxy_urls = [
			'https://www.smacamel.com/woo_proxy.php',
			'https://smacamel.com/woo_proxy.php'
		];
		
		$params = [];
		if(!empty($from_date) && !empty($to_date)) {
			$params['from_date'] = $from_date;
			$params['to_date'] = $to_date;
		}
		$params['limit'] = 500;
		
		$success = false;
		
		foreach($proxy_urls as $url) {
			$full_url = $url . '?' . http_build_query($params);
			
			$context = stream_context_create([
				'http' => [
					'timeout' => 15,
					'method' => 'GET',
					'header' => 'User-Agent: WooCommerce-Report-Client/1.0'
				]
			]);
			
			$response = @file_get_contents($full_url, false, $context);
			
			if($response !== false) {
				$data = json_decode($response, true);
				if($data && $data['status'] == 'success' && isset($data['orders'])) {
					$this->display_orders_from_proxy($data['orders'], $from_date, $to_date);
					$success = true;
					break;
				}
			}
		}
		
		if(!$success) {
			echo "<tr>";
			echo "<td class='text-center text-danger' colspan='10'>";
			echo "<i class='fa fa-exclamation-triangle'></i> ";
			echo "<strong>Database Connection Error</strong><br>";
			echo "Cannot connect to WooCommerce database. Please:<br>";
			echo "1. Add your IP to Remote MySQL in cPanel<br>";
			echo "2. Upload the proxy script to your server<br>";
			echo "3. Check your database credentials<br>";
			echo "<small>Error: Access denied for database connection</small>";
			echo "</td>";
			echo "</tr>";
		}
	}
	
	private function display_orders_from_proxy($orders, $from_date = '', $to_date = '') {
		$i = 0;
		$total_amount = 0;
		
		foreach ($orders as $order) {
			
			// Clean up order status
			$status = str_replace('wc-', '', $order['post_status']);
			$status = ucfirst($status);
			
			// Status color coding
			$status_class = 'default';
			switch(strtolower($status)) {
				case 'completed': $status_class = 'success'; break;
				case 'processing': $status_class = 'warning'; break;
				case 'on-hold': $status_class = 'info'; break;
				case 'pending': $status_class = 'primary'; break;
				case 'cancelled': 
				case 'refunded': 
				case 'failed': $status_class = 'danger'; break;
			}
			
			$order_total = floatval($order['order_total']);
			$total_amount += $order_total;
			
			echo "<tr>";
			echo "<td>".++$i."</td>";
			echo "<td><strong>#".$order['order_id']."</strong></td>";
			echo "<td>".date('d-m-Y', strtotime($order['post_date']))."</td>";
			echo "<td>".date('h:i A', strtotime($order['post_date']))."</td>";
			echo "<td>".$order['customer_name']."</td>";
			echo "<td>".($order['billing_email'] ?: 'N/A')."</td>";
			echo "<td>".($order['billing_phone'] ?: 'N/A')."</td>";
			echo "<td><span class='label label-".$status_class."'>".$status."</span></td>";
			echo "<td class='text-right'><strong>".$order['currency']." ".number_format($order_total, 2)."</strong></td>";
			echo "<td>".($order['payment_method'] ?: 'N/A')."</td>";
			echo "</tr>";
		}
		
		$date_range = '';
		if(!empty($from_date) && !empty($to_date)) {
			$date_range = " (From: $from_date to $to_date)";
		} else {
			$date_range = " (Via Proxy - Last 500)";
		}
		
		echo "<tr class='bg-info'>
				  <td class='text-right text-bold' colspan='8'><b>Showing: $i orders$date_range | Total Amount:</b></td>
				  <td class='text-right text-bold'><strong>INR ".number_format($total_amount, 2)."</strong></td>
				  <td></td>
			  </tr>";
	}

	// Attempt to connect to WooCommerce DB using multiple host options
	private function woo_multi_connect(&$errors){
		$errors = [];
		include APPPATH.'config/database.php';
		if(!isset($db['woocommerce'])){ $errors[]='Config group "woocommerce" missing'; return null; }
		$conf = $db['woocommerce'];
		$hosts = array_unique([
			$conf['hostname'],
			'localhost',
			'127.0.0.1',
			'lightning.megpanel.com'
		]);
		$CI = &get_instance();
		foreach($hosts as $h){
			$params = $conf; $params['hostname']=$h; $params['db_debug']=FALSE; // suppress fatal output
			$conn = $CI->load->database($params, TRUE);
			$q = @$conn->query("SHOW TABLES LIKE 'wp_wc_orders'");
			if($q!==FALSE){
				return $conn; // success
			}
			$err = $conn->error();
			$errors[] = $h.' => '.(isset($err['code'])?$err['code']:'?').' '.(isset($err['message'])?$err['message']:'Unknown error');
		}
		return null;
	}


}
