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

class Billtest extends CI_Controller {

	function __construct()
    {
        parent::__construct();
        error_reporting(E_ALL ^ (E_NOTICE | E_WARNING));
        $this->load->model('common_model');
        $this->clientid = 1;
    }
    
    function index()
    {
		$currdate = $this->input->get("currdate");
		$outletid = $this->input->get("outletid");
		if($outletid=="") $outletid = 0;
		if($currdate!="")
		{
			$this->db->select("*");
			$this->db->where("fld_billdate", $currdate);
			if($outletid>0) $this->db->where("fld_outletid", $outletid);
			$this->db->order_by('fld_id', 'asc');
			$results = $this->db->get("tbl_billing");
			//echo $this->db->last_query(); exit;

			$this->db->select("fld_billingid, sum(fld_qty*fld_price) as subtotal");
			$this->db->from("tbl_billinginfo BII");
			$this->db->join("tbl_billing BI", "BII.fld_billingid=BI.fld_id");
			$this->db->where("BI.fld_billdate", $currdate);
			if($outletid>0) $this->db->where("BI.fld_outletid", $outletid);
			$this->db->group_by("BII.fld_billingid");
			$results2 = $this->db->get();
			//echo $this->db->last_query(); exit;
			$arrbillsinfo = array();
			foreach($results2->result() as $row)
			{
				$arrbillsinfo[$row->fld_billingid] = $row->subtotal;
			}
			
			$gtotal = 0;
			$gtotal2 = 0;
			$index = 0;
			$missingcounter = 0;
			//predata($arrbillsinfo2);
			$billstotal = 0;
			$itemstotal = 0;
			$cancelledtotal = 0;
			$completedtotal = 0;
			$difftotal = 0;
			echo '<table border="1">';
			echo '<tr><td>Serial</td><td>Bill No.</td><td>Date</td><td>Status</td><td>Bill Amount</td><td>Items Total</td><td>Discount</td><td>Diff</td></tr>';
			foreach($results->result() as $row)
			{
				$total = 0;
				if($arrbillsinfo[$row->fld_id]) $total = $arrbillsinfo[$row->fld_id];
				$billstotal += $row->fld_netamount;
				$gtotal += $row->fld_netamount;
				$gtotal02 += $total;    
				$diff = $row->fld_netamount - $total;
				$diffamt = "";
				if($diff>=1) $diffamt = $diff;
				if($row->fld_status=="cancelled") $cancelledtotal += $row->fld_netamount;
				elseif($row->fld_status=="completed") $completedtotal += $row->fld_netamount;
				
				$itemstotal += $total;
				$difftotal += $diff;
				$discounttotal += $row->fld_discount;

				$index++;
				echo '<tr>';
				echo '<td>' . $index . '</td>';
				echo '<td>' . $row->fld_billno . '</td>';
				echo '<td>' . $row->fld_billdate . '</td>';
				echo '<td>' . $row->fld_status . '</td>';
				echo '<td>' . $row->fld_netamount . '</td>';
				

				if($total>0)
				{
					echo '<td>' . $total . '</td>';
				}
				else
				{
					$missingcounter++;
					echo '<td>Missting: ' . $missingcounter . ' : ' . $row->fld_billno . '</td>';
					
				}
				if($row->fld_discount>0)
				{
					$total = ($total - $row->fld_discount);
				}
				
	echo '<td>'.$row->fld_discount.'</td>';
			echo '<td>' . $diff . ' </td>';
				
				
				echo '</tr>';
			}
			echo '<tr><td></td><td></td><td></td><td></td><td>' . $billstotal . '</td><td>' . $itemstotal . '</td><td>' . $discounttotal . '</td><td>' . $difftotal . '</td></tr>';
			echo '</table>';

			echo "<br><br>Total Bills: " . $index;
			echo "<br><br>Cancelled: " . $cancelledtotal;
			echo "<br><br>Completed: " . $completedtotal;

			$sql = "select fld_id, fld_billno from tbl_billing where fld_billno=any(SELECT fld_billno FROM tbl_billing where fld_billdate='" . $currdate . "' group by fld_billno having count(*)>1)";
			$result02 = $this->db->query($sql);
			if($result02->num_rows()>0)
			{
				echo "<br><br>Duplicate Bills:";
				foreach($result02->result() as $row)
				{
					echo "<br>" . $row->fld_id . " " . $row->fld_billno;
				}
			}
			else
			{
				echo "<br><br>No duplicate bills found";
			}
		}
		else
		{
			$data["outletlist"] = $this->common_model->getlist('outlets', 'fld_id,fld_name','fld_name');
			$this->load->view('account/billtest', $data);
		}
	}

    function itemwise()
    {
		$currdate = $this->input->get("currdate");
		$outletid = $this->input->get("outletid");
		if($outletid=="") $outletid = 0;
		if($currdate!="")
		{
			$this->db->select("BI.fld_billno, BI.fld_billdate, BI.fld_status, BI.fld_datetime, BI.fld_updatedon, BI.fld_netamount, BI.fld_discount, PI.fld_name, BII.fld_productid, BII.fld_qty, BII.fld_price, BII.fld_mrp, (BII.fld_qty*BII.fld_price) as subtotal, PI.fld_del");
			$this->db->from("tbl_billinginfo BII");
			$this->db->join("tbl_billing BI", "BII.fld_billingid=BI.fld_id");
			$this->db->join("tbl_products PI", "BII.fld_productid=PI.fld_id");
			$this->db->where("BI.fld_billdate", $currdate);
			$this->db->order_by("PI.fld_name", "asc");
			if($outletid>0) $this->db->where("BI.fld_outletid", $outletid);
			$results = $this->db->get();
			//echo $this->db->last_query(); exit;
			$gtotal = 0;
			$gtotal2 = 0;
			$index = 0;
			$missingcounter = 0;
			//predata($arrbillsinfo2);
			$billstotal = 0;
			$itemstotal = 0;
			$cancelledtotal = 0;
			$completedtotal = 0;
			$difftotal = 0;
			echo '<table border="1">';
			echo '<tr><td>Serial</td><td>Bill No.</td><td>Date</td><td>Status</td><td>Item Name</td><td>Qty</td><td>Price</td><td>Items Total</td><td>Discount</td><td>Delete Status</td></tr>';
			$itemstotal = 0;
			foreach($results->result() as $row)
			{
				$total = 0;
				$billstotal += $row->fld_netamount;
				$gtotal += $row->fld_netamount;
				$gtotal02 += $row->subtotal;    
				$diff = $row->fld_netamount - $row->subtotal;
				$diffamt = "";
				if($diff>=1) $diffamt = $diff;
				if($row->fld_status=="cancelled") $cancelledtotal += $row->fld_netamount;
				elseif($row->fld_status=="completed") $completedtotal += $row->fld_netamount;
				
				$itemstotal += $row->subtotal;
				
				$difftotal += $diff;
				$discounttotal += $row->fld_discount;

				$index++;
				$delstatus = "";
				if($row->fld_del==1) $delstatus = "DELETED";
				echo '<tr>';
				echo '<td>' . $index . '</td>';
				echo '<td>' . $row->fld_billno . '</td>';
				echo '<td>' . $row->fld_billdate . '</td>';
				echo '<td>' . $row->fld_status . '</td>';
				echo '<td>' . $row->fld_name . '</td>';
				echo '<td>' . $row->fld_qty . '</td>';
				echo '<td>' . $row->fld_price . '</td>';
				echo '<td>' . $row->subtotal . '</td>';
				echo '<td>'.$row->fld_discount.'</td>';
				echo '<td>'.$delstatus.'</td>';
				
				echo '</tr>';
			}
			echo '<tr><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td>' . $itemstotal . '</td><td>' . $discounttotal . '</td><td></td></tr>';
			echo '</table>';

			echo "<br><br>Total: " . $itemstotal;
			echo "<br><br>Cancelled: " . $cancelledtotal;
			echo "<br><br>Completed: " . ceil($itemstotal - $cancelledtotal);

			$sql = "select fld_id, fld_billno from tbl_billing where fld_billno=any(SELECT fld_billno FROM tbl_billing where fld_billdate='" . $currdate . "' group by fld_billno having count(*)>1)";
			$result02 = $this->db->query($sql);
			if($result02->num_rows()>0)
			{
				echo "<br><br>Duplicate Bills:";
				foreach($result02->result() as $row)
				{
					echo "<br>" . $row->fld_id . " " . $row->fld_billno;
				}
			}
			else
			{
				echo "<br><br>No duplicate bills found";
			}
		}
		else
		{
			$data["outletlist"] = $this->common_model->getlist('outlets', 'fld_id,fld_name','fld_name');
			$this->load->view('account/billtest', $data);
		}
	}
	
	function rmdupbills()
	{
		$arrbillids = array();
		$currdate = $this->input->get("currdate");
		$sql = "select fld_id, fld_billno from tbl_billing where fld_billno=any(SELECT fld_billno FROM tbl_billing where fld_billdate='" . $currdate . "' group by fld_billno having count(*)>1)";
		$result02 = $this->db->query($sql);
		if($result02->num_rows()>0)
		{
			echo "<br><br>Duplicate Bills:";
			foreach($result02->result() as $row)
			{
				echo "<br>" . $row->fld_id . " " . $row->fld_billno;
				$arrbillids[$row->fld_billno] = $row->fld_id;
			}
			//predata($arrbillids);
			$billids = implode(",", $arrbillids);
			$billids2compare = explode(",", $billids);
			//predata($billids2compare);
			if(is_array($billids2compare))
			{
				## del bill info
				$this->db->where_in("fld_billingid", $billids2compare);
				$this->db->delete("tbl_billinginfo");
				echo "<br><br>" . $this->db->last_query();

				#3 del main bills
				$this->db->where_in("fld_id", $billids2compare);
				$this->db->delete("tbl_billing");
				echo "<br><br>" . $this->db->last_query();
			}
			echo "<br>DONE";
		}
		else
		{
			echo "<br><br>No duplicate bills found";
		}
	}
	
	function updatepricing()
	{
		## update kararia chowk single bottle price in other pricing zones

		$arrproducts = array();
		$zoneid = 1; // for kataria chowk
		$this->db->select("fld_productid, fld_price");
		$this->db->where("fld_zoneid", $zoneid);
		$result0 = $this->db->get("tbl_price");
		echo "<br>" . $this->db->last_query() . " => " . $result0->num_rows() . "<br>";
		if($result0->num_rows()>0)
		{
			while ($row0 = $result0->unbuffered_row())
			{
				$arrproducts[$row0->fld_productid] = $row0->fld_price;
				echo "<br>" . $row0->fld_productid . " => " . $row0->fld_price;

				#update database
				//$arrdata = array("fld_price" => $row0->fld_price, "fld_updatedon" => "2023-06-27 18:10:30");
				//$this->db->where("fld_productid", $row0->fld_productid);
				//$this->db->where("fld_zoneid!=", $zoneid);
				//$this->db->update('tbl_price', $arrdata);
				//echo "<br>" . $this->db->last_query();
			}
		}
	}
	

}
