<?php
session_start();
// header('Content-Type: text/html; charset=utf-8');
if(!isset($_SESSION['user'])) {
	header("location: ../index.php");
	exit();
}

include "../dbconnect.php";

/* customers each order detail */
if ($_GET['act'] == 'vieworder') {
	$orderid = $_GET['orderid'];
	$unitid = $_GET['unitid'];
	$invoiceno = $_GET['invoiceno'];
	$orderdate = $_GET['orderdate'];

	$order_detail = '';
	$orderdetail = array();
	
	// echo "SELECT `UnitId`, `InvoiceNumber`, `SaleDate`, `SaleTime`, `SourceOfOrder` FROM `guest_order_history` WHERE `UnitId` = '$unitid' AND `OrderId` = '$orderid' AND `InvoiceNumber` = '$invoiceno' AND `OrderDate` = '$orderdate'";
	$st_qry1 = mysql_query("SELECT `UnitId`, `InvoiceNumber`, `SaleDate`, `SaleTime`, `SourceOfOrder` FROM `guest_order_history` WHERE `UnitId` = '$unitid' AND `OrderId` = '$orderid' AND `InvoiceNumber` = '$invoiceno' AND `OrderDate` = '$orderdate'");
	$st_row1 = mysql_fetch_array($st_qry1);

	$UnitId = $st_row1['UnitId'];
	$saledate = $st_row1['SaleDate'];

	$unit_qry = mysql_query("SELECT `UnitName` FROM `unit_master` WHERE `UnitId` = '$UnitId'");
	$unit_row = mysql_fetch_assoc($unit_qry);

	$sale_qry = mysql_query("SELECT `DiscAmt`, `InvAmt`, `PayMode`, `CGSTAmt`, `SGSTAmt`, `TotalAmt` FROM `sale_record` WHERE `UnitId` = '$unitid' AND `InvoiceNumber` = '$invoiceno' AND `SaleDate` = '$saledate'");
	$sale_row = mysql_fetch_array($sale_qry);
	$TaxAmt = $sale_row['CGSTAmt'] + $sale_row['SGSTAmt'];

	$order_detail = '<div class="row"><div class="col-lg-6 col-md-6 fb-detail-left"><p class="detail-right text-logs">Outlet Name - <span class="text-bold">'.$unit_row['UnitName'].'</span></p><p class="detail-left text-logs">Sale Date <span class="text-bold">'.date('d-m-Y', strtotime($st_row1['SaleDate'])).' '.$st_row1['SaleTime'].'</span></p></div><div class="col-lg-6 col-md-6 fb-detail-right text-right"><p class="detail-right text-logs">Invoice No. - <span class="text-bold">'.$st_row1['InvoiceNumber'].'</span></p><p class="detail-right text-logs">Order Source - <span class="text-bold">'.$st_row1['SourceOfOrder'].'</span></p><p class="detail-right text-logs">Payment Mode - <span class="text-bold">'.$sale_row['PayMode'].'</span></p></div></div>';
	
	$order_detail .= '<table class="table table-striped table-sm table-fb-order"><thead class="thead-default"><tr><th>Item</th><th>Qty</th><th>Price</th></tr></thead><tbody>';

	$odr_qry = mysql_query("SELECT `ItemName`, `Quantity`, `Price` FROM `sale_item_record` WHERE `UnitId` = '$unitid' AND `InvoiceNumber` = '$invoiceno' AND `SaleDate` = '$saledate'");
	while ($odr_row = mysql_fetch_array($odr_qry)) {
		$order_detail .= '<tr><td>'.utf8_encode($odr_row['ItemName']).'</td><td>'.$odr_row['Quantity'].'</td><td>'.$odr_row['Price'].'</td></tr>';
	}

	$order_detail .= '</tbody></table>';

	$order_detail .= '<div class="row"><div class="col-lg-12 text-right"><p>Sub Total: <span class="text-bold">'.$sale_row['InvAmt'].'</span></p><p>Discount: <span class="text-bold">'.$sale_row['DiscAmt'].'</span></p><p>Tax: <span class="text-bold">'.$TaxAmt.'</span></p><p>Total Bill Amount: <span class="text-bold">'.$sale_row['TotalAmt'].'</span></p></div></div>';
	// echo $order_detail;
	$orderdetail[] = $order_detail;

	$data = array('orderDetail' => $order_detail);
	echo json_encode($data);
}

 
if ($_GET['act'] == 'daily') {
	$date = date('Y-m-d');

	/*$qry = mysql_query("SELECT count(DISTINCT(`GuestPhone`)) AS newcustomer, SUM(`TotalAmt`) AS TotalAmt FROM `guest_order_history` WHERE `SaleDate` = '$date'");
	$row = mysql_fetch_assoc($qry);
	print_r($row);*/
}


if ($_GET['act'] == 'weekly') {
	$date = date('Y-m-d');
	$ldate = date('Y-m-d', strtotime('-6 days'));
}


if ($_GET['act'] == 'monthly') {
	$date = date('Y-m-d');
	$exp = explode('-', $date);
	$ldate = $exp[0].'-'.$exp[1].'-01';
}

/*order insight page on change of date range*/
if ($_GET['act'] == 'ordinsDate') {
	$dateran = $_GET['daterange'];

	$date = explode('-', $dateran);
	$fst_date = trim($date[0], " ");
	$expdate1 = explode('/', $fst_date);
	$fdate = $expdate1[2]."-".$expdate1[1]."-".$expdate1[0];
	
	$lst_date = trim($date[1], " ");
	$expdate2 = explode('/', $lst_date);
	$ldate = $expdate2[2]."-".$expdate2[1]."-".$expdate2[0];

	// $totalorders = 0;
	$totalamt = 0;
	$repeatcustomer = 0;
	$newcustomer = 0;

	$qry = mysql_query("SELECT count(*) AS cnt, `GuestPhone`, `TotalAmt` FROM `guest_order_history` WHERE `OrderDate` >= '$fdate' AND `OrderDate` <= '$ldate' GROUP By `GuestPhone`");
	while($row = mysql_fetch_assoc($qry)){
		// print_r($row);
		// $totalorders += $row['cnt'];
		$totalamt += number_format($row['TotalAmt'], 2);
		$gphone = $row['GuestPhone'];
		
		
		$qry1 = mysql_query("SELECT `GuestPhone` FROM `guest_order_history` WHERE `OrderDate` < '$fdate' AND `GuestPhone` = '$gphone'");
		if (mysql_num_rows($qry1) > 0) {
			$repeatcustomer += 1;
		} else {
			$newcustomer += 1;
		}
		
	}

	$grdate = array();
	$neworders = array();
	$totalorders = array();
	$repeatorders = array();
	$qry2 = mysql_query("SELECT DISTINCT(`OrderDate`) AS OrderDate FROM `guest_order_history` WHERE `OrderDate` >= '$fdate' AND `OrderDate` <= '$ldate'");
	while ($row2 = mysql_fetch_assoc($qry2)) {
		$ord_date = $row2['OrderDate'];

		$qry3 = mysql_query("SELECT count(`GuestPhone`) AS totalorders FROM `guest_order_history` WHERE `OrderDate` = '$ord_date'");
		$row3 = mysql_fetch_assoc($qry3);
		
		// $qry4 = mysql_query("SELECT COUNT(DISTINCT(`GuestPhone`)) AS repeatorders FROM `guest_order_history` WHERE `OrderDate` = '$ord_date' GROUP BY `GuestPhone` HAVING COUNT(`GuestPhone`) > 1");
		// $row4 = mysql_fetch_assoc($qry4);

		$grdate[] = date('d M', strtotime($ord_date));
		// $neworders[] = (int)$row3['neworders'];
		$totalorders[] = (int)$row3['totalorders'];
		// $repeatorders[] = (int)$row4['repeatorders'];
	}

	// $data = array('newcustomer' => $newcustomer, 'repeatcustomer' => $repeatcustomer, 'TotalAmt' => $totalamt, 'date' => $grdate, 'neworders' => $neworders, 'totalorders' => $totalorders, 'repeatorders' => $repeatorders);
	$data = array('newcustomer' => $newcustomer, 'repeatcustomer' => $repeatcustomer, 'TotalAmt' => $totalamt, 'date' => $grdate, 'totalorders' => $totalorders);
	echo json_encode($data);


	

	/*$qry1 = mysql_query("SELECT COUNT(DISTINCT(`GuestPhone`)) AS repeatcustomer FROM `guest_order_history` WHERE `OrderDate` >= '$fdate' AND `OrderDate` <= '$ldate' GROUP BY `GuestPhone` HAVING COUNT(`GuestPhone`) > 1");
	// $row1 = mysql_fetch_assoc($qry1);
	$repeatcustomer = 0;
	while ($row1 = mysql_fetch_assoc($qry1)) {
		$repeatcustomer += $row1['repeatcustomer'];
	}

	$grdate = array();
	$neworders = array();
	$totalorders = array();
	$repeatorders = array();
	$qry2 = mysql_query("SELECT DISTINCT(`OrderDate`) FROM `guest_order_history` WHERE `OrderDate` >= '$fdate' AND `OrderDate` <= '$ldate'");
	while ($row2 = mysql_fetch_assoc($qry2)) {
		$OrderDate = $row2['OrderDate'];

		$qry3 = mysql_query("SELECT count(DISTINCT(`GuestPhone`)) AS neworders, count(`GuestPhone`) AS totalorders FROM `guest_order_history` WHERE `OrderDate` = '$OrderDate'");
		$row3 = mysql_fetch_assoc($qry3);
		
		$qry4 = mysql_query("SELECT COUNT(DISTINCT(`GuestPhone`)) AS repeatorders FROM `guest_order_history` WHERE `OrderDate` = '$OrderDate' GROUP BY `GuestPhone` HAVING COUNT(`GuestPhone`) > 1");
		$row4 = mysql_fetch_assoc($qry4);

		$neworders[] = $row3['neworders'];
		$totalorders[] = $row3['totalorders'];
		$repeatorders[] = $row4['repeatorders'];
		$grdate[] = date('d M', strtotime($OrderDate));
	}

	$data = array('newcustomer' => $newcustomer, 'repeatcustomer' => $repeatcustomer, 'TotalAmt' => $TotalAmt, 'date' => $grdate, 'neworders' => $neworders, 'totalorders' => $totalorders, 'repeatorders' => $repeatorders);
	echo json_encode($data);*/
}

/*order information on search by fin year, outlet, order id and invoice no*/
if ($_GET['act'] == 'orderinfo') {
	$fin = $_GET['fin'];
	$outlet = $_GET['outlet'];
	$orderid = $_GET['orderid'];
	$invoiceno = $_GET['invoiceno'];

	if ($fin != '') {
		$fin_sql = '`Fin` = \''.$fin.'\'';
	}

	if ($outlet != '') {
		$outlet_sql = ' AND `UnitId` = \''.$outlet.'\'';
	}

	if ($orderid != '') {
		$orderid_sql = ' AND `OrderId` = \''.$orderid.'\'';
	}

	if ($invoiceno != '') {
		$invoice_sql = ' AND `InvoiceNumber` = \''.$invoiceno.'\'';
	}

	$order_information = '';
	$orderinfo = array();
	$productItem = array();
	$itemQuantity = array();

	$qry = mysql_query("SELECT `UnitId`, `OrderId`, `OrderDate`, `InvoiceId`, `InvoiceNumber`, `SaleDate`, `GuestPhone`, `FirstName`, `LastName`, `Locality`, `TotalAmt`, `OrderStatus`, `PaymentOption`, `SourceOfOrder` FROM `guest_order_history` WHERE $fin_sql $outlet_sql $orderid_sql $invoice_sql");
	$row = mysql_fetch_assoc($qry);
	$unitid = $row['UnitId'];
	$invno = $row['InvoiceNumber'];
	$SaleDate = $row['SaleDate'];
	$locality = $row['Locality'];

	$unitqry = mysql_query("SELECT `UnitName` FROM `unit_master` WHERE `UnitId` = '$unitid'");
	$unitrow = mysql_fetch_assoc($unitqry);

	$locqry = mysql_query("SELECT `locality_name` FROM `locality` WHERE `licality_id` = '$locality'");
	$locrow = mysql_fetch_assoc($locqry);

	$sale_qry = mysql_query("SELECT `ItemName`, `Quantity` FROM `sale_item_record` WHERE $fin_sql $outlet_sql $invoice_sql AND `SaleDate` = '$SaleDate'");
	while ($sale_row = mysql_fetch_assoc($sale_qry)) {
		$productItem[] = '<input type="text" name="" class="form-control mb-lg" value="'.$sale_row['ItemName'].'">';
		$itemQuantity[] = '<input type="text" name="" class="form-control mb-lg pro-qnt" value="'.$sale_row['Quantity'].'">';
	}

	$data = array('name' => $row['FirstName'].' '.$row['LastName'], 'mobile' => $row['GuestPhone'], 'orderid' => $row['OrderId'], 'invoicenum' => $row['InvoiceNumber'], 'orderdate' => date('d-m-Y', strtotime($row['OrderDate'])), 'ordertotalbill' => $row['TotalAmt'], 'paymentmode' => $row['PaymentOption'], 'orderstatus' => $row['OrderStatus'], 'ordersource' => $row['SourceOfOrder'], 'outlet' => $unitrow['UnitName'], 'locality' => $locrow['locality_name'], 'productname' => $productItem, 'quantity' => $itemQuantity);
	echo json_encode($data);
}

/*get groupname and itemname for drilldown graph in product insight page*/
if ($_GET['act'] == 'proinsDate') {
	$daterange = $_GET['daterange'];

	$date = explode('-', $daterange);
	$fst_date = trim($date[0], " ");
	$expdate1 = explode('/', $fst_date);
	$fdate = $expdate1[2]."-".$expdate1[1]."-".$expdate1[0];
	// $fdate = "2017-04-01";
	
	$lst_date = trim($date[1], " ");
	$expdate2 = explode('/', $lst_date);
	$ldate = $expdate2[2]."-".$expdate2[1]."-".$expdate2[0];
	// $ldate = "2017-04-02";

	$groupname = array();
	$cntgrpname = array();
	
	
	$itm_group = array();

	$qry = mysql_query("SELECT COUNT(`GroupName`) AS cntGroupName, `GroupName` FROM `sale_item_record` WHERE `SaleDate` >= '$fdate' AND `SaleDate` <= '$ldate' GROUP BY `GroupName`");
	while ($row = mysql_fetch_assoc($qry)) {
		$grpname = $row['GroupName'];
		$groupname[] = array('name' => $row['GroupName'], 'y'=>$row['cntGroupName'], 'drilldown'=>strtolower(str_replace(' ', '', $row['GroupName'])));
		// $cntgrpname[] = $row['cntGroupName'];

		/*[{
            id: 'nonvegbiryani',
            name: 'Sub-Categories',
            data: [{
                name: 'Hyderabadi',
                y: 4                
            }]
        }]*/
        $itemname = array();
		$qry1 = mysql_query("SELECT COUNT(`ItemName`) AS cntItemName, `ItemName` FROM `sale_item_record` WHERE `SaleDate` >= '$fdate' AND `SaleDate` <= '$ldate' AND `GroupName` = '$grpname' GROUP BY `ItemName`");
		while ($row1 = mysql_fetch_assoc($qry1)) {
			
			$itemname[] = array('name' => $row1['ItemName'], 'y' => (int) $row1['cntItemName']);
		}
		$itm_group[] = array("id"=> strtolower(str_replace(' ', '', $row['GroupName'])), "name"=> $row['GroupName'], "data"=> $itemname);
	}

	// $data = array('groupname' => $groupname, 'totalgrpname' => $cntgrpname);
	$data = array('groupname' => $groupname, 'itemname' => $itm_group);
	echo json_encode($data);
}


/*get new customer detail for all channels*/
if ($_GET['act'] == 'channelDate') {
	$dateran = $_GET['daterange'];

	$date = explode('-', $dateran);
	$fst_date = trim($date[0], " ");
	$expdate1 = explode('/', $fst_date);
	$fdate = $expdate1[2]."-".$expdate1[1]."-".$expdate1[0];
	
	$lst_date = trim($date[1], " ");
	$expdate2 = explode('/', $lst_date);
	$ldate = $expdate2[2]."-".$expdate2[1]."-".$expdate2[0];

	$newcustomer = array();
	$source_order = array();

	$qry = mysql_query("SELECT `SourceOfOrder` FROM `guest_order_history` WHERE `OrderDate` >= '$fdate' and `OrderDate` >= '$ldate' group by `SourceOfOrder`");
	while ($row = mysql_fetch_assoc($qry)) {
		$srcorder = $row['SourceOfOrder'];

		$qry1 = mysql_query("SELECT `GuestPhone` FROM `guest_order_history` WHERE `OrderDate` < '$fdate' AND `SourceOfOrder` = '$srcorder'");
		if (mysql_num_rows($qry1) < 0) {
			
			while ($row1 = mysql_fetch_assoc($qry1)) {
				$newcustomer[] = $row1['GuestPhone'];
			}
		}
		$source_order[] = $row['SourceOfOrder'];
	}
	$data = array('sourceorder' => $source_order, 'newcustomer' => $newcustomer);
	echo json_encode($data);
}