<?php
require ("../app/dbconnect.php");
	$dbconnect = new dbconnect;
	$db = $dbconnect->connect();

	$frm = $_POST['frm'];

if($frm === "get_sales_enq_info"){
	$cur_month = $_POST['cur_month'];
	$sts   = $_POST['sts'];
	$month = date("Y-m");
	$date = date("Y-m-d");
	$qry = "";

	if($cur_month == $date){
			$qry = "DATE_FORMAT(sblt_enquiry.created_date, '%Y-%m-%d')";
	}else
	if($cur_month == $month){
		$qry = "DATE_FORMAT(sblt_enquiry.created_date, '%Y-%m')";
	}else{
		$qry = "DATE_FORMAT(sblt_enquiry.created_date, '%Y')";
	}
	
	$enquire_sales_info = mysql_query("SELECT customer_name,sblt_customers.phone_number,sblt_customers.alt_number,sblt_enquiry.ncd,sblt_customers.cust_address,sblt_customers.cust_email,cust_sts,sblt_enquiry.trip_days,sblt_enquiry.bus_count,sblt_enquiry.report_address,sblt_enquiry.billing_name,first_name,sblt_enquiry.avg_km,booking_type_name,sblt_enquiry.from_date,sblt_enquiry.to_date,trip_type_name,veh_type,enquiry_sts,prospect,sblt_enquiry.trip_details,sblt_enquiry.booking_amount,sblt_enquiry.cancel_reson,f_city.city_name as source,dest.city_name as destination from sblt_enquiry left join sblt_booking on sblt_booking.enquiry_id = sblt_enquiry.enquiry_id left join sblt_customers on sblt_customers.cust_id = sblt_enquiry.cust_id left join sblt_trip_type on sblt_trip_type.trip_type_id = sblt_enquiry.trip_type left join sblt_veh_type on sblt_veh_type.veh_type_id = sblt_enquiry.vehicle_type left join sblt_employees on sblt_employees.id = sblt_enquiry.referral left join sblt_people on sblt_people.person_id = sblt_employees.person_id left join sblt_booking_type on sblt_booking_type.booking_type_id = sblt_enquiry.cust_type left join sblt_city as f_city on f_city.city_id = sblt_enquiry.orgin left join sblt_city as dest on dest.city_id = sblt_enquiry.destination where sblt_enquiry.status = '1' and enquiry_sts = '$sts' and $qry like '$cur_month%'");

    $tr_line = "";
	$count = 0;
	while($sale_info = mysql_fetch_array($enquire_sales_info)){
		$count++;
		$from_date       = date('d-m-Y', strtotime($sale_info['from_date']));
		$to_date         = date('d-m-Y', strtotime($sale_info['to_date']));
		$name            = $sale_info['customer_name'];
		$mob             = $sale_info['phone_number'];
		$alt_number      = $sale_info['alt_number'];
		$cust_sts        = $sale_info['cust_sts'];
		$cust_email      = $sale_info['cust_email'];
		$cust_address    = $sale_info['cust_address'];
		$trip_days       = $sale_info['trip_days'];
		$bus_count       = $sale_info['bus_count'];
		$report_address  = $sale_info['report_address'];
		$source          = $sale_info['source'];
		$destination     = $sale_info['destination'];
		$billing_name    = $sale_info['billing_name'];
		$first_name      = $sale_info['first_name'];
		$avg_km          = $sale_info['avg_km'];
		$trip_type_name  = $sale_info['trip_type_name'];
		$veh_type        = $sale_info['veh_type'];
		$type            = $sale_info['booking_type_name'];
		$trip_details    = $sale_info['trip_details'];
		$enquiry_sts     = $sale_info['enquiry_sts'];
		$booking_amount  = $sale_info['booking_amount'];
		$ncd             = $sale_info['ncd'];
		
		//enquiry_sts details
		if($enquiry_sts === "1"){
			$enquiry_sts = "Follow Up";
		}else
		if($enquiry_sts === "2"){
			$enquiry_sts = "Confirm";
		}else
		if($enquiry_sts === "3"){
			$enquiry_sts = "Cancelled";
		}
		//tbody details
		$tr_line  .= "<tr>
					<td style='text-align:center !important;'>$count</td>
					<td>$name</td>
					<td>$billing_name</td>
					<td>$mob</td>
					<td>$alt_number</td>
					<td>$cust_email</td>
					<td>$cust_address</td>
					<td>$trip_type_name</td>
					<td>$source</td>
					<td>$destination</td>
					<td>$avg_km</td>
					<td style='white-space: nowrap'>$from_date</td>
					<td style='white-space: nowrap'>$to_date</td>
					<td>$trip_days</td>
					<td>$veh_type</td>
					<td>$bus_count</td>
					<td>$booking_amount</td>
					<td>$type</td>
					<td>$cust_sts</td>
					<td>$first_name</td>
					<td>$enquiry_sts</td>
					<td style='white-space: nowrap'>$ncd</td>
					<td>$report_address</td>
					<td>$trip_details</td>
				</tr>";
				}
		
		//thead details
	echo "<table id='detail' class='table table-bordered col-style'>
			<thead>
				<tr style='font-weight: bold;
    text-align: center !important;
    background: #d84f57;color: #FFFFFF;'>
					<th>Sl.No</th>	
					<th>Name</th>
					<th>Billing Name</th>
					<th>Mobile</th>	
					<th>Alt Mobile</th>	
					<th>Email</th>	
					<th>Address</th>	
					<th>Trip Type</th>
					<th>Orgin</th>
					<th>Destination</th>
					<th>Avg Km</th>
					<th>From</th>							
					<th>To</th>
					<th>Trip Days</th>
					<th>Veh Type</th>
					<th>Bus Count</th>
					<th>Booking Amount</th>				
					<th>Booking Type</th>
					<th>Cust Status</th>
					<th>Referred By</th>
					<th>Status</th>
					<th>NCD</th>
					<th>Report Address</th>
					<th>Trip Details</th>
				</tr>
			</thead>
			<tbody>	$tr_line</tbody>
		</table>";
}else
	if($frm === "get_sales_book_info"){
	$cur_month = $_POST['cur_month'];
	$sts   = $_POST['sts'];
	$month = date("Y-m");
	$date = date("Y-m-d");
	$qry = "";
	if($cur_month == $date){
		$qry = "DATE_FORMAT(sblt_booking.from_date, '%Y-%m-%d')";
	}else
	if($cur_month == $month){
		$qry = "DATE_FORMAT(sblt_booking.from_date, '%Y-%m')";
	}else{
		$qry = "DATE_FORMAT(sblt_booking.from_date, '%Y')";
	}
	$booking_sales_info = mysql_query("SELECT customer_name,sblt_customers.phone_number,sblt_customers.alt_number,sblt_customers.cust_address,sblt_customers.cust_email,cust_sts,sblt_booking.trip_days,sblt_booking.bus_count,sblt_booking.report_address,sblt_booking.billing_name,first_name,sblt_booking.avg_km,booking_type_name,sblt_booking.from_date,sblt_booking.to_date,trip_type_name,veh_type,booking_sts,sblt_booking.trip_details,sblt_booking.booking_amount,sblt_booking.cancel_reson,f_city.city_name as source,dest.city_name as destination from sblt_booking left join sblt_customers on sblt_customers.cust_id = sblt_booking.cust_id left join sblt_trip_type on sblt_trip_type.trip_type_id = sblt_booking.trip_type left join sblt_veh_type on sblt_veh_type.veh_type_id = sblt_booking.vehicle_type left join sblt_employees on sblt_employees.id = sblt_booking.referral left join sblt_people on sblt_people.person_id = sblt_employees.person_id left join sblt_booking_type on sblt_booking_type.booking_type_id = sblt_booking.cust_type left join sblt_city as f_city on f_city.city_id = sblt_booking.orgin left join sblt_city as dest on dest.city_id = sblt_booking.destination where sblt_booking.status = '1' and booking_sts = '$sts' and $qry like '$cur_month%'");

    $tr_line = "";
	$count = 0;
	while($booking_info = mysql_fetch_array($booking_sales_info)){
		$count++;
		$from_date       = date('d-m-Y', strtotime($booking_info['from_date']));
		$to_date         = date('d-m-Y', strtotime($booking_info['to_date']));
		$name            = $booking_info['customer_name'];
		$mob             = $booking_info['phone_number'];
		$alt_number      = $booking_info['alt_number'];
		$cust_sts        = $booking_info['cust_sts'];
		$cust_email      = $booking_info['cust_email'];
		$cust_address    = $booking_info['cust_address'];
		$trip_days       = $booking_info['trip_days'];
		$bus_count       = $booking_info['bus_count'];
		$report_address  = $booking_info['report_address'];
		$source          = $booking_info['source'];
		$destination     = $booking_info['destination'];
		$billing_name    = $booking_info['billing_name'];
		$first_name      = $booking_info['first_name'];
		$avg_km          = $booking_info['avg_km'];
		$trip_type_name  = $booking_info['trip_type_name'];
		$veh_type        = $booking_info['veh_type'];
		$type            = $booking_info['booking_type_name'];
		$trip_details    = $booking_info['trip_details'];
		$booking_sts     = $booking_info['booking_sts'];
		$booking_amount  = $booking_info['booking_amount'];
		
		//Booking sts details
		
		if($booking_sts === "1"){
			$booking_sts = "Follow Up";
		}else
		if($booking_sts === "2"){
			$booking_sts = "Confirm";
		}else
		if($booking_sts === "3"){
			$booking_sts = "Cancelled";
		}
		
		//tbody details
		$tr_line  .= "<tr>
					<td style='text-align:center !important;'>$count</td>
					<td>$name</td>
					<td>$billing_name</td>
					<td>$mob</td>
					<td>$alt_number</td>
					<td>$cust_email</td>
					<td>$cust_address</td>
					<td>$trip_type_name</td>
					<td>$source</td>
					<td>$destination</td>
					<td>$avg_km</td>
					<td style='white-space: nowrap'>$from_date</td>
					<td style='white-space: nowrap'>$to_date</td>
					<td>$trip_days</td>
					<td>$veh_type</td>
					<td>$bus_count</td>
					<td>$booking_amount</td>
					<td>$type</td>
					<td>$cust_sts</td>
					<td>$first_name</td>
					<td>$booking_sts</td>
					<td>$report_address</td>
					<td>$trip_details</td>
				</tr>";
				}
		
		//thead Details
	echo "<table id='detail' class='table table-bordered col-style'>
			<thead>
				<tr style='font-weight: bold;
    text-align: center !important;
    background: #d84f57;color: #FFFFFF;'>
					<th>Sl.No</th>	
					<th>Name</th>
					<th>Billing Name</th>
					<th>Mobile</th>	
					<th>Alt Mobile</th>	
					<th>Email</th>	
					<th>Address</th>	
					<th>Trip Type</th>
					<th>Orgin</th>
					<th>Destination</th>
					<th>Avg Km</th>
					<th>From</th>							
					<th>To</th>
					<th>Trip Days</th>
					<th>Veh Type</th>
					<th>Bus Count</th>
					<th>Booking Amount</th>				
					<th>Booking Type</th>
					<th>Cust Status</th>
					<th>Referred By</th>
					<th>Status</th>
					<th>Report Address</th>
					<th>Trip Details</th>
				</tr>
			</thead>
			<tbody>$tr_line</tbody>
		</table>";

}else

	//Sales package details

	if($frm === "get_package_enq_info"){
	$cur_month = $_POST['cur_month'];
	$sts   = $_POST['sts'];
	$month = date("Y-m");
	$qry = "";

	if($cur_month == $month)
		{
			$qry = "DATE_FORMAT(sblt_temple_enquiry.enquiry_date, '%Y-%m')";
		}else
		{
			$qry = "DATE_FORMAT(sblt_temple_enquiry.enquiry_date, '%Y')";
		}

		$package_enq_info = mysql_query("SELECT ticket_count,sblt_temple_enquiry.sourcing,sblt_temple_enquiry.pickup_point, package_name,enquiry_date,customer_name,sblt_customers.phone_number,sblt_customers.alt_number,sblt_customers.cust_address,sblt_customers.cust_email,cust_sts,sblt_temple_enquiry.enquiry_sts FROM sblt_temple_enquiry left join sblt_customers on sblt_customers.cust_id= sblt_temple_enquiry.cust_id left join sblt_package_management on sblt_package_management.package_id = sblt_temple_enquiry.package_id WHERE sblt_temple_enquiry.status = '1' and sblt_temple_enquiry.enquiry_sts = '$sts' and $qry like '$cur_month%'");

    $tr_line = "";
	$count = 0;
	while($package_info = mysql_fetch_array($package_enq_info)){
		$count++;
		$enquiry_date    = date('d-m-Y', strtotime($package_info['enquiry_date']));
		//$to_date       = date('d-m-Y', strtotime($package_info['to_date']));
		$name            = $package_info['customer_name'];
		$mob             = $package_info['phone_number'];
		$alt_number      = $package_info['alt_number'];
		$cust_sts        = $package_info['cust_sts'];
		$cust_email      = $package_info['cust_email'];
		$cust_address    = $package_info['cust_address'];
		$source          = $package_info['sourcing'];
		$package_name    = $package_info['package_name'];
		$pickup_point    = $package_info['pickup_point'];
		$enquiry_sts     = $package_info['enquiry_sts'];
		$ticket_count    = $package_info['ticket_count'];
		
		//Enquiry Status
		if($enquiry_sts === "1"){
			$enquiry_sts = "Pending";
		}else
		if($enquiry_sts === "2"){
			$enquiry_sts = "Confirmed";
		}else
		if($enquiry_sts === "3"){
			$enquiry_sts = "Cancelled";
		}
		
		//tbody details
		$tr_line  .= "<tr>
					<td style='text-align:center !important;'>$count</td>
					<td>$name</td>
					<td>$mob</td>
					<td>$alt_number</td>
					<td>$cust_email</td>
					<td>$cust_address</td>
					<td>$package_name</td>
					<td>$source</td>
					<td>$pickup_point</td>
					<td style='white-space: nowrap'>$enquiry_date</td>
					<td>$ticket_count</td>
					<td>$cust_sts</td>
					<td>$enquiry_sts</td>
				</tr>";
				}
				
		//thead details
	echo "<table id='detail' class='table table-bordered col-style'>
			<thead>
				<tr style='font-weight: bold;
    text-align: center !important;
    background: #d84f57;color: #FFFFFF;'>
					<th>Sl.No</th>	
					<th>Name</th>
					<th>Mobile</th>	
					<th>Alt Mobile</th>	
					<th>Email</th>	
					<th>Address</th>	
					<th>Package Name</th>
					<th>Source</th>
					<th>Pickup Point</th>
					<th>Enquiry Date</th>
					<th>Ticket Count</th>
					<th>Cust Status</th>
					<th>Status</th>
				</tr>
			</thead>
			<tbody>$tr_line</tbody>
		</table>";
}else
	if($frm === "get_package_book_info"){
	$cur_month = $_POST['cur_month'];
	$sts   = $_POST['sts'];
	$month = date("Y-m");
	$qry = "";
    //$year = date("Y");
	if($cur_month == $month)
		{
			$qry = "DATE_FORMAT(from_date, '%Y-%m')";
		}else
		{
			$qry = "DATE_FORMAT(from_date, '%Y')";
		}

	$package_book_info = mysql_query("SELECT customer_name,phone_number,alt_number,cust_address,cust_email,package_name,cust_sts,from_date,to_date,trip_days,bus_count,person_count,package_sts FROM `sblt_package_management`  LEFT join sblt_package_line on sblt_package_line.package_id=sblt_package_management.package_id LEFT join sblt_customers on sblt_customers.cust_id= sblt_package_line.cust_id where package_sts='$sts' AND sblt_package_management.status ='1' AND $qry like '$cur_month%' GROUP by sblt_package_management.package_id");

	$tr_line = "";
	$count = 0;
	while($package_info = mysql_fetch_array($package_book_info)){
		$count++;
		$from_date       = date('d-m-Y', strtotime($package_info['from_date']));
		$to_date         = date('d-m-Y', strtotime($package_info['to_date']));
		$name            = $package_info['customer_name'];
		$mob             = $package_info['phone_number'];
		$alt_number      = $package_info['alt_number'];
		$cust_sts        = $package_info['cust_sts'];
		$cust_email      = $package_info['cust_email'];
		$cust_address    = $package_info['cust_address'];
		$trip_days       = $package_info['trip_days'];
		$bus_count       = $package_info['bus_count'];
		$person_count    = $package_info['person_count'];
		$package_sts     = $package_info['package_sts'];
		$package_name    = $package_info['package_name'];
		
		//package status
		if($package_sts === "1"){
			$package_sts = "Pending";
		}else
		if($package_sts === "2"){
			$package_sts = "Completed";
		}else
		if($package_sts === "3"){
			$package_sts = "Cancelled";
		}
		
		//tbody Details
		$tr_line  .= "<tr>
					<td style='text-align:center !important;'>$count</td>
					<td>$name</td>
					<td>$mob</td>
					<td>$alt_number</td>
					<td>$cust_email</td>
					<td>$cust_address</td>
					<td>$package_name</td>
					<td style='white-space: nowrap'>$from_date</td>
					<td style='white-space: nowrap'>$to_date</td>
					<td>$trip_days</td>
					<td>$bus_count</td>
					<td>$person_count</td>
					<td>$cust_sts</td>
					<td>$package_sts</td>
				</tr>";
				}
		
			//thead details
	echo "<table id='detail' class='table table-bordered col-style'>
			<thead>
				<tr style='font-weight: bold;
    text-align: center !important;
    background: #d84f57;color: #FFFFFF;'>
					<th>Sl.No</th>	
					<th>Name</th>
					<th>Mobile</th>	
					<th>Alt Mobile</th>	
					<th>Email</th>	
					<th>Address</th>	
					<th>Package Name</th>
					<th>From Date</th>
					<th>To Date</th>
					<th>Trip Days</th>
					<th>Bus Count</th>
					<th>Person Count</th>
					<th>Cust Status</th>
					<th>Status</th>
				</tr>
			</thead>
			<tbody>$tr_line</tbody>
		</table>";
}else
	//catering part callback function

	if($frm === "get_catering_book_info"){
	$cur_month = $_POST['cur_month'];
	$sts   = $_POST['sts'];
	$month = date("Y-m");
	$qry = "";
    //$year = date("Y");
	if($cur_month == $month){
			$qry = "DATE_FORMAT(from_date, '%Y-%m')";
		}else
		{
			$qry = "DATE_FORMAT(from_date, '%Y')";
		}
	
		if($sts==1){
			$sts = "Enquiry";
		}else
		if($sts==2)
		{
			$sts = "Confirm";
		}else
		if($sts==3)
		{
			$sts = "Cancelled";
		}else
		if($sts==4)
		{
			$sts = "Completed";
		}
	$catering_book_info = mysql_query("SELECT customer_name,phone_number,alt_number,cust_address,cust_email,referral_type,allow_contract,cust_sts,cust_type,catering_type,from_date,to_date,days_count,fucntion_name,fucntion_place,pax,catering_sts,tax_precentage,catering_amount,balance,catreing_information FROM `sblt_catering_outside` inner join sblt_customers on sblt_customers.cust_id= sblt_catering_outside.cust_id WHERE catering_sts='$sts' and status='1' and catering_type = '2' and $qry like '$cur_month%'");
	$tr_line = "";
	$count = 0;
	while($catering_info = mysql_fetch_array($catering_book_info)){
		$count++;
		$from_date               = date('d-m-Y', strtotime($catering_info['from_date']));
		$to_date                 = date('d-m-Y', strtotime($catering_info['to_date']));
		$name                    = $catering_info['customer_name'];
		$mob                     = $catering_info['phone_number'];
		$alt_number              = $catering_info['alt_number'];
		$cust_sts                = $catering_info['cust_sts'];
		$cust_email              = $catering_info['cust_email'];
		$cust_address            = $catering_info['cust_address'];
		$cust_type               = $catering_info['cust_type'];
		$days_count              = $catering_info['days_count'];
		$function_name           = $catering_info['fucntion_name'];
		$function_place          = $catering_info['fucntion_place'];
		$catering_sts            = $catering_info['catering_sts'];
		$referral_type           = $catering_info['referral_type'];
		$pax                     = $catering_info['pax'];
		$allow_contract          = $catering_info['allow_contract'];
		$catering_type           = $catering_info['catering_type'];
		$tax_precentage          = $catering_info['tax_precentage'];
		$catering_amount         = $catering_info['catering_amount'];
		$balance                 = $catering_info['balance'];
		$catreing_information    = $catering_info['catreing_information'];

		//catering status
		
		if($catering_sts === "Enquiry"){
			$catering_sts = "Enquiry";
		}else
		if($catering_sts === "Confirm"){
			$catering_sts = "Confirm";
		}else
		if($catering_sts === "Cancelled"){
			$catering_sts = "Cancelled";
		}else
		if($catering_sts === "Completed"){
			$catering_sts = "Completed";
		}
		
		//customer type
		
		if($cust_type == 1){
			$cust_type = "Aggregate";
		}else
		if($cust_type == 2){
			$cust_type = "Corporate";
		}else
		if($cust_type == 3){
			$cust_type = "Individual";
		}else
		if($cust_type == 4){
			$cust_type = "Package";
		}else
		if($cust_type == 5){
			$cust_type = "Travel Agents";
		}
		
		//tbody details
		
		$tr_line  .= "<tr>
					<td style='text-align:center !important;'>$count</td>
					<td>$name</td>
					<td>$mob</td>
					<td>$alt_number</td>
					<td>$cust_email</td>
					<td>$cust_address</td>
					<td style='white-space: nowrap'>$from_date</td>
					<td style='white-space: nowrap'>$to_date</td>
					<td>$days_count</td>
					<td>$pax</td>
					<td>$catreing_information</td>
					<td>$cust_sts</td>
					<td>$cust_type</td>
					<td>$tax_precentage</td>
					<td>$catering_amount</td>
					<td>$balance</td>
					<td>$catering_sts</td>
				</tr>";
				}
		//thead details
		
	echo "<table id='detail' class='table table-bordered col-style'>
			<thead>
				<tr style='font-weight: bold;
    text-align: center !important;
    background: #d84f57;color: #FFFFFF;'>
					<th>Sl.No</th>	
					<th>Name</th>
					<th>Mobile</th>	
					<th>Alt Mobile</th>	
					<th>Email</th>	
					<th>Address</th>	
					<th>From Date</th>
					<th>To Date</th>
					<th>Days Count</th>
					<th>Person Count</th>
					<th>Catering Information</th>
					<th>Customer Status</th>
					<th>Customer type</th>
					<th>Tax (%)</th>
					<th>Catering Amount</th>
					<th>Balance</th>
					<th>Catering Status</th>
				</tr>
			</thead>
			<tbody>$tr_line</tbody>
		</table>";
}

?>