<?php
session_start();
if(!isset($_SESSION['user'])) {
	header("location: ../index.php");
	exit;
}

include "../dbconnect.php";
require("../plugins/Classes/PHPExcel.php");
include '../plugins/Classes/PHPExcel/IOFactory.php';

$client_id = '62';

$city = $_GET['city'];
$outlet = $_GET['outlet'];
$ordtype = $_GET['ordertype'];
$fdate = $_GET['fdate'];
$ldate = $_GET['ldate'];
$operator = $_GET['operator'];
$starnum = $_GET['starnum'];

if($city == 'all'){
	$city_sql = '';
} else {
	$city_sql = 'AND fbr.`city_name` = \''.$city.'\'';
}

if($outlet == 'all'){
	$outlet_sql = '';
} else {
	$outlet_sql = 'AND  fbr.`outlet_name` = \''.$outlet.'\'';
}

if($ordtype == 'all'){
	$ordtype_sql = '';
} else {
	$ordtype_sql = 'AND fbr.`order_type` = \''.$ordtype.'\'';
}

$file_name="customer_log_data.xls";

function cellColor($cells,$color){
  	global $objPHPExcel;
  	$objPHPExcel->getActiveSheet()->getStyle($cells)->getFill()
  	->applyFromArray(array('type' => PHPExcel_Style_Fill::FILL_SOLID,
  		'startcolor' => array('rgb' => $color)
  	));
}

$styleArray = array(
	'font'  => array(
    'bold'  => true,
));

$objPHPExcel = new PHPExcel();

$xlsrow = 1;
$xlscol = 'A';

$objPHPExcel->getActiveSheet()->setCellValue($xlscol.$xlsrow, "Customer Name");
$objPHPExcel->getActiveSheet()->getStyle($xlscol.$xlsrow)->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->getColumnDimension($xlscol)->setWidth(25);
$objPHPExcel->getActiveSheet()->getStyle($xlscol.$xlsrow)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); 
cellColor($xlscol.$xlsrow, 'FFCC00');
$xlscol++;

$objPHPExcel->getActiveSheet()->setCellValue($xlscol.$xlsrow, "Invoice No.");
$objPHPExcel->getActiveSheet()->getStyle($xlscol.$xlsrow)->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->getColumnDimension($xlscol)->setWidth(25);
$objPHPExcel->getActiveSheet()->getStyle($xlscol.$xlsrow)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); 
cellColor($xlscol.$xlsrow, 'FFCC00');
$xlscol++;

$objPHPExcel->getActiveSheet()->setCellValue($xlscol.$xlsrow, "Order Source");
$objPHPExcel->getActiveSheet()->getStyle($xlscol.$xlsrow)->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->getColumnDimension($xlscol)->setWidth(25);
$objPHPExcel->getActiveSheet()->getStyle($xlscol.$xlsrow)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); 
cellColor($xlscol.$xlsrow, 'FFCC00');
$xlscol++;

$objPHPExcel->getActiveSheet()->setCellValue($xlscol.$xlsrow, "Outlet Name");
$objPHPExcel->getActiveSheet()->getStyle($xlscol.$xlsrow)->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->getColumnDimension($xlscol)->setWidth(25);
$objPHPExcel->getActiveSheet()->getStyle($xlscol.$xlsrow)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); 
cellColor($xlscol.$xlsrow, 'FFCC00');
$xlscol++;

$objPHPExcel->getActiveSheet()->setCellValue($xlscol.$xlsrow, "Sale Date");
$objPHPExcel->getActiveSheet()->getStyle($xlscol.$xlsrow)->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->getColumnDimension($xlscol)->setWidth(25);
$objPHPExcel->getActiveSheet()->getStyle($xlscol.$xlsrow)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); 
cellColor($xlscol.$xlsrow, 'FFCC00');
$xlscol++;

$objPHPExcel->getActiveSheet()->setCellValue($xlscol.$xlsrow, "Response Date/Time");
$objPHPExcel->getActiveSheet()->getStyle($xlscol.$xlsrow)->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->getColumnDimension($xlscol)->setWidth(25);
$objPHPExcel->getActiveSheet()->getStyle($xlscol.$xlsrow)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); 
cellColor($xlscol.$xlsrow, 'FFCC00');
$xlscol++;

$objPHPExcel->getActiveSheet()->setCellValue($xlscol.$xlsrow, "Email");
$objPHPExcel->getActiveSheet()->getStyle($xlscol.$xlsrow)->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->getColumnDimension($xlscol)->setWidth(25);
$objPHPExcel->getActiveSheet()->getStyle($xlscol.$xlsrow)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); 
cellColor($xlscol.$xlsrow, 'FFCC00');
$xlscol++;

$objPHPExcel->getActiveSheet()->setCellValue($xlscol.$xlsrow, "Mobile No.");
$objPHPExcel->getActiveSheet()->getStyle($xlscol.$xlsrow)->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->getColumnDimension($xlscol)->setWidth(25);
$objPHPExcel->getActiveSheet()->getStyle($xlscol.$xlsrow)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); 
cellColor($xlscol.$xlsrow, 'FFCC00');
$xlscol++;
// echo "SELECT DISTINCT(frd.`fb_ques`), (SUM(frd.`ans_wt`) / count(frd.`fb_no`)) AS rate FROM `feedback_response_details_custom` AS frd INNER JOIN `feedback_response` AS fbr ON frd.`fb_no` = fbr.`fb_no` WHERE (fbr.`sale_date` >= '$fdate' AND fbr.`sale_date` <= '$ldate') AND fbr.`client_id` = '$client_id' $city_sql $outlet_sql $ordtype_sql group by frd.`fb_ques_id` HAVING rate $operator '$starnum' ORDER BY frd.`fb_ques_id` ASC";
// exit();
$qry1 = mysql_query("SELECT DISTINCT(frd.`fb_ques`), (SUM(frd.`ans_wt`) / count(frd.`fb_no`)) AS rate FROM `feedback_response_details_custom` AS frd INNER JOIN `feedback_response` AS fbr ON frd.`fb_no` = fbr.`fb_no` WHERE (fbr.`sale_date` >= '$fdate' AND fbr.`sale_date` <= '$ldate') AND fbr.`client_id` = '$client_id' $city_sql $outlet_sql $ordtype_sql group by frd.`fb_ques_id` HAVING rate $operator '$starnum' ORDER BY frd.`fb_ques_id` ASC");
while ($row1 = mysql_fetch_array($qry1)) {
	//$qry_fbr1 = mysql_query("SELECT DISTINCT(`fb_ques`) FROM `feedback_response_details_custom` WHERE `fb_no`= '".$row1['fb_no']."' ORDER BY `fb_ques_id` ASC");
	//while ($row_fbr1 = mysql_fetch_array($qry_fbr1)) {
		$objPHPExcel->getActiveSheet()->setCellValue($xlscol.$xlsrow, strtoupper(str_replace('_', ' ', $row1['fb_ques'])));
		$objPHPExcel->getActiveSheet()->getStyle($xlscol.$xlsrow)->applyFromArray($styleArray);
		$objPHPExcel->getActiveSheet()->getColumnDimension($xlscol)->setWidth(25);
		$objPHPExcel->getActiveSheet()->getStyle($xlscol.$xlsrow)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); 
		cellColor($xlscol.$xlsrow, 'FFCC00');
		$xlscol++;
	//}
}

$xlsrow++;


// $qry = mysql_query("SELECT `fb_no`, `outlet_id`, `invoice_no`, `sale_date`, `response_date_time`, `outlet_name`, `order_source_name`, `guest_name`, `guest_mob_no`, `guest_email`, `fb_comments` FROM `feedback_response`");
// echo "SELECT (SUM(frd.`ans_wt`) / count(frd.`fb_no`)) AS rate, fbr.`guest_name`, fbr.`invoice_no`, fbr.`order_source_name`, fbr.`outlet_name`, fbr.`response_date_time`, fbr.`fb_comments`, fbr.`fb_no`, fbr.`outlet_id`, fbr.`sale_date`, fbr.`guest_mob_no`, fbr.`guest_email` FROM `feedback_response_details` AS frd INNER JOIN `feedback_response` AS fbr ON frd.`fb_no` = fbr.`fb_no` WHERE (fbr.`sale_date` >= '$fdate' AND fbr.`sale_date` <= '$ldate') AND fbr.`client_id` = '$client_id' $city_sql $outlet_sql $ordtype_sql group by frd.`fb_no` HAVING rate $operator '$starnum' ORDER BY fbr.`response_date_time` DESC";
// exit();
$qry = mysql_query("SELECT (SUM(frd.`ans_wt`) / count(frd.`fb_no`)) AS rate, fbr.`guest_name`, fbr.`invoice_no`, fbr.`order_source_name`, fbr.`outlet_name`, fbr.`response_date_time`, fbr.`fb_comments`, fbr.`fb_no`, fbr.`outlet_id`, fbr.`sale_date`, fbr.`guest_mob_no`, fbr.`guest_email` FROM `feedback_response_details_custom` AS frd INNER JOIN `feedback_response` AS fbr ON frd.`fb_no` = fbr.`fb_no` WHERE (fbr.`sale_date` >= '$fdate' AND fbr.`sale_date` <= '$ldate') AND fbr.`client_id` = '$client_id' $city_sql $outlet_sql $ordtype_sql group by frd.`fb_no` HAVING rate $operator '$starnum' ORDER BY fbr.`response_date_time` DESC");
while ($row = mysql_fetch_array($qry)) {
	$xlscol = 'A';
	$objPHPExcel->getActiveSheet()->setCellValue($xlscol.$xlsrow, $row['guest_name']);
	$xlscol++;

	$objPHPExcel->getActiveSheet()->setCellValue($xlscol.$xlsrow, $row['invoice_no']);
	$xlscol++;

	$objPHPExcel->getActiveSheet()->setCellValue($xlscol.$xlsrow, $row['order_source_name']);
	$xlscol++;

	$objPHPExcel->getActiveSheet()->setCellValue($xlscol.$xlsrow, $row['outlet_name']);
	$xlscol++;

	$objPHPExcel->getActiveSheet()->setCellValue($xlscol.$xlsrow, $row['sale_date']);
	$xlscol++;

	$objPHPExcel->getActiveSheet()->setCellValue($xlscol.$xlsrow, $row['response_date_time']);
	$xlscol++;

	$objPHPExcel->getActiveSheet()->setCellValue($xlscol.$xlsrow, $row['guest_email']);
	$xlscol++;

	$objPHPExcel->getActiveSheet()->setCellValue($xlscol.$xlsrow, $row['guest_mob_no']);
	$xlscol++;

	$qry_fbr = mysql_query("SELECT `ans_wt` FROM `feedback_response_details_custom` WHERE `fb_no`= '".$row['fb_no']."' ORDER BY `fb_ques_id` ASC");
	while ($row_fbr = mysql_fetch_array($qry_fbr)) {
		$objPHPExcel->getActiveSheet()->setCellValue($xlscol.$xlsrow, $row_fbr['ans_wt']);
		$xlscol++;
	}

	$xlsrow++;
}


header('Content-Type: application/vnd.ms-excel'); 
header('Content-Disposition: attachment;filename="customer_log_data.xls"'); 
header('Cache-Control: max-age=0'); 
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); 
$objWriter->save('php://output');