Sales Report';
echo '';
echo 'Check In : ';
echo 'Daily Check In Sales Report';
echo 'Monthly Check In Sales Report';
echo 'Annually Check In Sales Report';
echo '
';
echo 'Check Out : ';
echo 'Daily Check Out Sales Report';
echo 'Monthly Check Out Sales Report';
echo 'Annually Check Out Sales Report';
echo '
';
echo 'Custom : ';
echo 'Custom Sales Report';
echo '
';
//echo $chk_level;
if ($chk_level < 2 && $chk_level != 0)
{
$view = $_REQUEST['view'];
//echo $view;
switch($view)
{
case 'custom' :
if($_POST['datefrom'] != NULL)
{
$cur_sel_date_from = strtotime($_POST['datefrom']);
} else {
$cur_sel_date_from = strtotime(date('Y-m-d H:i')); //abb 14-5-12 H:i:s needed for day use function
}
if($_POST['dateto'] != NULL)
{
$cur_sel_date_to = strtotime($_POST['dateto']);
} else {
$cur_sel_date_to = strtotime(date('Y-m-d H:i')); //abb 14-5-12 H:i:s needed for day use function
}
$ciorco = $_POST['ciorco'];
$paymenttype = $_POST['paymenttype'];
if($_POST['ciorco'] == '')
{
$ciorco = 'checkin';
}
if($_POST['ciorco'] == 'checkin')
{
$ciorcoval = 'Check In';
$ciorcotype = 'ci';
} else {
$ciorcoval = 'Check Out';
$ciorcotype = 'co';
}
$_SESSION['ciorco'] = $ciorco;
//$_SESSION['chosen_date_from'] = $cur_sel_date_from;
//$_SESSION['chosen_date_to'] = $cur_sel_date_to;
$_SESSION['cur_sel_date_from'] = date('d-m-y', $cur_sel_date_from);
$_SESSION['cur_sel_date_to'] = date('d-m-y', $cur_sel_date_to);
//abb 9-4-15 form use
$_SESSION['cur_sel_date_from2'] = date('Y-m-d H:i:s', $cur_sel_date_from);
$_SESSION['cur_sel_date_to2'] = date('Y-m-d H:i:s', $cur_sel_date_to);
if($_SESSION['cur_sel_date_from2'] != NULL)
{
//echo $_SESSION['cur_sel_date_from2'];
$cur_sel_date_from2 = strtotime($_SESSION['cur_sel_date_from2']);
} else {
$cur_sel_date_from2 = strtotime(date('Y-m-d H:i')); //abb 14-5-12 H:i:s needed for day use function
}
if($_SESSION['cur_sel_date_to2'] != NULL)
{
//echo $_SESSION['cur_sel_date_to2'];
$cur_sel_date_to2 = strtotime($_SESSION['cur_sel_date_to2']);
} else {
$cur_sel_date_to2 = strtotime(date('Y-m-d H:i')); //abb 14-5-12 H:i:s needed for day use function
}
$today = strtotime(date('Y-m-d'));
echo 'Custom ',$ciorcoval, ' Sales Report from ',date('d-M-y H:i', $cur_sel_date_from2),' to ',date('d-M-y H:i', $cur_sel_date_to2),'
';
echo '';
//echo date('Y-m-d H:i:s',$cur_sel_date);
$cur_sel_date_from_Ymd = date('Y-m-d H:i:s', $cur_sel_date_from);
$cur_sel_date_to_Ymd = date('Y-m-d H:i:s', $cur_sel_date_to);
if ($paymenttype == 'all')
{
$paymenttype_condition = '';
} else {
$paymenttype_condition = " AND payment = '". $paymenttype."'";
}
//echo "WHERE (DATE_FORMAT($ciorco, '%Y-%m-%d %H:%i') >= '$cur_sel_date_from_Ymd') AND (DATE_FORMAT($ciorco, '%Y-%m-%d %H:%i') <= '$cur_sel_date_to_Ymd') AND (type!='cleaning' AND type!='maintenance') $paymenttype_condition";
sales_report("WHERE (DATE_FORMAT($ciorco, '%Y-%m-%d %H:%i') >= '$cur_sel_date_from_Ymd') AND (DATE_FORMAT($ciorco, '%Y-%m-%d %H:%i') <= '$cur_sel_date_to_Ymd') AND (type!='cleaning' AND type!='maintenance') $paymenttype_condition", $cur_sel_date_from, 'custom', $ciorcotype);
break;
case 'dailyci' :
if($_POST['date'] != NULL)
{
$cur_sel_date = strtotime($_POST['date']);
} else {
$cur_sel_date = strtotime(date('Y-m-d H:i:s')); //abb 14-5-12 H:i:s needed for day use function
}
$_SESSION['chosen_date'] = $cur_sel_date;
$_SESSION['cur_sel_date'] = date('d-m-y', $cur_sel_date);
$today = strtotime(date('Y-m-d'));
echo 'Daily Check In Sales Report
';
?>
fetch_object();
$first_month = date('1-m-Y', strtotime($row-> firstMonth));
$result = db_query("SELECT MAX(checkin) as lastMonth FROM room_log LIMIT 1");
$row = $result-> fetch_object();
$last_month = date('1-m-Y', strtotime($row-> lastMonth));
// Create DateTime objects
$dt1 = new DateTime($first_month);
$dt2 = new DateTime($last_month);
//echo $first_month , ' + ' , $last_month;
$totalmonth = $dt1->diff($dt2)->m + 1; // int(4)
//$totalmonth = $totalmonth + 1;
//echo $totalmonth;
$month = strtotime($first_month);
$start = $month = strtotime($first_month);
$end = strtotime($last_month);
echo 'Monthly Check In Sales Report
';
echo '';
if($selected_month == ''){ $selected_month = $end; }
//echo $selected_month;
$m_month = date('Y-m', $selected_month);
//echo $m_month;
if($selected_month != ''){
sales_report("WHERE DATE_FORMAT(checkin, '%Y-%m') = '$m_month' AND final_status='1' AND (type!='cleaning' AND type!='maintenance')", $selected_month, 'monthlyci');
}
break;
case 'annualci' :
$selected_year = $_POST['year'];
//echo 'selected month = ' . $selected_month.'
';
$result = db_query("SELECT MIN(checkin) as firstYear FROM room_log");
$row = $result-> fetch_object();
//echo $row-> checkin;
$first_year = $row-> firstYear;
$result = db_query("SELECT MAX(checkin) as lastYear FROM room_log");
$row = $result-> fetch_object();
$last_year = $row-> lastYear;
// Create DateTime objects
$dt1 = new DateTime($first_year);
$dt2 = new DateTime($last_year);
//echo $first_year , ' + ' , $last_year;
$totalyear = $dt1->diff($dt2)->m + 1; // int(4)
//$totalyear = $totalyear + 1;
//echo $totalyear;
$year = strtotime($first_year);
$start = $year = strtotime($first_year);
$end = strtotime($last_year);
echo 'Annually Check In Sales Report
';
echo '';
if($selected_year == ''){ $selected_year = $end; }
//echo $selected_month;
$y_year = date('Y-m', $selected_year);
//echo $m_month;
if($selected_year != ''){
sales_report("WHERE DATE_FORMAT(checkin, '%Y') = '$y_year' AND final_status='1'", $selected_year, 'annualci');
}
break;
//sales report check out below abb 7-4-15
case 'dailyco' :
if($_POST['date'] != NULL)
{
$cur_sel_date = strtotime($_POST['date']);
} else {
$cur_sel_date = strtotime(date('Y-m-d H:i:s')); //abb 14-5-12 H:i:s needed for day use function
}
$_SESSION['chosen_date'] = $cur_sel_date;
$_SESSION['cur_sel_date'] = date('d-m-y', $cur_sel_date);
$today = strtotime(date('Y-m-d'));
echo 'Daily Check Out Sales Report
';
?>
fetch_object();
$first_month = date('1-m-Y', strtotime($row-> firstMonth));
$result = db_query("SELECT MAX(checkout) as lastMonth FROM room_log LIMIT 1");
$row = $result-> fetch_object();
$last_month = date('1-m-Y', strtotime($row-> lastMonth));
// Create DateTime objects
$dt1 = new DateTime($first_month);
$dt2 = new DateTime($last_month);
//echo $first_month , ' + ' , $last_month;
$totalmonth = $dt1->diff($dt2)->m + 1; // int(4)
//$totalmonth = $totalmonth + 1;
//echo $totalmonth;
$month = strtotime($first_month);
$start = $month = strtotime($first_month);
$end = strtotime($last_month);
echo 'Monthly Check Out Sales Report
';
echo '';
if($selected_month == ''){ $selected_month = $end; }
//echo $selected_month;
$m_month = date('Y-m', $selected_month);
//echo $m_month;
if($selected_month != ''){
sales_report("WHERE DATE_FORMAT(checkout, '%Y-%m') = '$m_month' AND final_status='1' AND (type!='cleaning' AND type!='maintenance')", $selected_month, 'monthlyco');
}
break;
case 'annualco' :
$selected_year = $_POST['year'];
//echo 'selected month = ' . $selected_month.'
';
$result = db_query("SELECT MIN(checkout) as firstYear FROM room_log");
$row = $result-> fetch_object();
//echo $row-> checkout;
$first_year = $row-> firstYear;
$result = db_query("SELECT MAX(checkout) as lastYear FROM room_log");
$row = $result-> fetch_object();
$last_year = $row-> lastYear;
// Create DateTime objects
$dt1 = new DateTime($first_year);
$dt2 = new DateTime($last_year);
//echo $first_year , ' + ' , $last_year;
$totalyear = $dt1->diff($dt2)->m + 1; // int(4)
//$totalyear = $totalyear + 1;
//echo $totalyear;
$year = strtotime($first_year);
$start = $year = strtotime($first_year);
$end = strtotime($last_year);
echo 'Annually Check Out Sales Report
';
echo '';
if($selected_year == ''){ $selected_year = $end; }
//echo $selected_month;
$y_year = date('Y-m', $selected_year);
//echo $m_month;
if($selected_year != ''){
sales_report("WHERE DATE_FORMAT(checkout, '%Y') = '$y_year' AND final_status='1'", $selected_year, 'annualco');
}
break;
}
} //end chk level abb 23-4-15
break;
///************************ sales report above abb 7-4-15 ***********************************//
case 'staff' :
$view = $_REQUEST['view'];
//$selected_staff_id = $_POST['staff'];
//$_SESSION['selected_staff_id'] = $selected_staff_id;
if($_POST['staff'] != ''){ $_SESSION['selected_staff_id'] = $_POST['staff']; }
echo 'Staff Report
';
echo '';
echo 'Daily Report';
echo 'Monthly Report';
echo 'Shift Report';
//echo 'Daily Shift Report';
echo '
';
/*echo '';*/
switch($view){
case 'daily' :
$selected_staff_id = $_POST['staff'];
if($_POST['date'] != NULL)
{
$cur_sel_date = strtotime($_POST['date']);
} else {
$cur_sel_date = strtotime(date('Y-m-d H:i:s')); //abb 14-5-12 H:i:s needed for day use function
}
$_SESSION['chosen_date'] = $cur_sel_date;
$_SESSION['cur_sel_date'] = date('d-m-y', $cur_sel_date);
$today = strtotime(date('Y-m-d'));
echo '';
//echo date('Y-m-d H:i:s',$cur_sel_date);
$ymd_cur_sel_date = date('Y-m-d', $cur_sel_date);
if($selected_staff_id == ''){
$selected_staff_id = $_SESSION['selected_staff_id'];
//echo 'selected from session='.$selected_staff_id.'
';
}
//sales_report("WHERE DATE_FORMAT(checkin, '%m') = '$m_month' AND final_status='1'", $selected_month, 'monthly');
$condition_view = " WHERE DATE_FORMAT(reservation_time, '%Y-%m-%d') = '$ymd_cur_sel_date' AND (type != 'cleaning' AND type != 'maintenance') AND final_status = '1'";
$condition_staff = "AND user_id = '$selected_staff_id' ";
//echo $selected_staff_id.'
';
staff_report($condition_view, $condition_staff, $selected_staff_id, $ymd_cur_sel_date, 'daily', 1);
//abb 15-6-12 show cancelled
$condition_view = " WHERE DATE_FORMAT(reservation_time, '%Y-%m-%d') = '$ymd_cur_sel_date' AND (type != 'cleaning' AND type != 'maintenance') AND final_status = '3'";
$condition_staff = "AND user_id = '$selected_staff_id' ";
//echo $selected_staff_id.'
';
staff_report($condition_view, $condition_staff, $selected_staff_id, $ymd_cur_sel_date, 'daily', 3);
break;
case 'monthly' :
$selected_staff_id = $_POST['staff'];
$selected_month = $_POST['month'];
//echo 'selected month = ' . $selected_month.'
';
$result = db_query("SELECT MIN(checkin) as firstMonth FROM room_log");
$row = $result-> fetch_object();
//echo $row-> checkin;
$first_month = $row-> firstMonth;
$result = db_query("SELECT MAX(checkin) as lastMonth FROM room_log");
$row = $result-> fetch_object();
$last_month = $row-> lastMonth;
//if($selected_month == ''){ $selected_month = $last_month; }
//echo $first_month .' - '. $last_month;
echo 'View monthly sales report.
';
echo '';
if($selected_month == ''){ $selected_month = $end; }
//echo $selected_month;
$m_month = date('Y-m', $selected_month);
//echo $m_month;
if($selected_month != '' && $selected_staff_id != ''){
//sales_report("WHERE DATE_FORMAT(checkin, '%m') = '$m_month' AND final_status='1'", $selected_month, 'monthly');
$condition_view = " WHERE DATE_FORMAT(reservation_time, '%Y-%m') = '$m_month' AND (type != 'cleaning' AND type != 'maintenance') AND final_status = '1'";
$condition_staff = "AND user_id = '$selected_staff_id' ";
//echo $selected_staff_id.'
';
staff_report($condition_view, $condition_staff, $selected_staff_id, $m_month, 'monthly', 1);
//abb 15-6-12 show cancelled
$condition_view = " WHERE DATE_FORMAT(reservation_time, '%Y-%m') = '$m_month' AND (type != 'cleaning' AND type != 'maintenance') AND final_status = '3'";
$condition_staff = "AND user_id = '$selected_staff_id' ";
//echo $selected_staff_id.'
';
staff_report($condition_view, $condition_staff, $selected_staff_id, $m_month, 'monthly', 3);
}
break;
case 'shift':
$sid = $_REQUEST['sid'];
if($_POST['search_date']){
$date = $_POST['date'];
$selected_date = strtotime($date);
$date = date('Y-m-d', $selected_date);
$_SESSION['date'] = $date;
//echo $selected_date;
}else{
$date = date('Y-m-d');
}
if($_SESSION['date'] != '')
{
$date = $_SESSION['date'];
}
else
{
$date = date('Y-m-d');
}
shift_report($user_id, $sid, $date, $topic, "report.php?report=staff&view=shift");
break;
} // end switch view
break;
case 'guest' :
$guest_ic = $_POST['idsearch'];
$guest_name = $_POST['gname'];
$guest_phone = $_POST['gphone'];
$guest_phone = clr_phone_format($guest_phone); // remove the hyphen to 0123456789
//echo $guest_phone;
//echo $guest_name;
echo '';
echo '';
$query = "SELECT guest1, guest_id, id_number1, address1, address2, city, postcode, state, phone1, email1, log_id, blacklist, member, salesman, guest_type, blacklist_why FROM guest WHERE (type != 'cleaning' AND type != 'maintenance') ";
// add condition
if($view == 'whitelist'){
$condition = " AND blacklist='0'";
} else if($view == 'blacklist') {
$condition = " AND blacklist='1'";
}
if($guest_ic != ''){
$condition = $condition . " AND id_number1 LIKE '%$guest_ic%'";
}
if($guest_name != ''){
$condition = $condition . " AND guest1 LIKE '%$guest_name%'";
}
if($guest_phone != ''){
$condition = $condition . " AND phone1 LIKE '%$guest_phone%'";
}
$groupby = " GROUP BY guest1 ";
// combine query + condition
$query = $query . $condition ;
// preserve query for paging
if($_REQUEST['page'] == '') // reset session if report link on the top is clicked
{
$_SESSION['sess_query'] = $query;
}
if($_SESSION['sess_query'] == '') // if session is empty
{
$_SESSION['sess_query'] = $query;
}
if(isset($_POST['gname']) || isset($_POST['gphone'])) // if got post for name / phone
{
$_SESSION['sess_query'] = $query;
}
//if(!isset($_POST['gname']) || !isset($_POST['gphone']) ) // else if no post
else
{
$query = $_SESSION['sess_query'];
}
//echo $query;
//guest_report($query . $condition, $groupby, $chk_level, $view, $pageno);
guest_report($query, $groupby, $chk_level, $view, $pageno);
break;
/* case 'member' :
$member_name = $_POST['mname'];
$member_phone = $_POST['mphone'];
$member_phone = clr_phone_format($member_phone); // remove the hyphen to 0123456789
echo '';
echo '
Member List
';
echo '';
echo '';
echo '';
echo '';
echo 'View All';
echo 'View White List';
echo 'View Black Listed';
echo 'Add New Member';
echo '
';
$view = $_REQUEST['view'];
$query = "SELECT mid, name, id_number, address1, address2, city, postcode, state, country, phone, email, group_id, company_name, date_added, user_id, blacklist FROM member ";
// add condition
if($view == 'whitelist'){
$condition = " WHERE blacklist='0' AND status = 1 ";
} else if($view == 'blacklist') {
$condition = " WHERE blacklist='1' AND status = 1 ";
}
if($member_name != ''){
$condition = $condition . " WHERE name LIKE '%$member_name%' AND status = 1 ";
}
if($member_phone != ''){
$condition = $condition . " WHERE phone LIKE '%$member_phone%' ";
}
member_report($query . $condition , $chk_level, $view, $pageno);
break;*/
case 'salesman' :
$salesman_name = $_POST['sname'];
$salesman_phone = $_POST['sphone'];
$salesman_phone = clr_phone_format($salesman_phone); // remove the hyphen to 0123456789
echo '';
//echo '
Salesman List
';
/*echo '';
echo '';*/
echo '';
echo 'Salesman List
';
$view = $_REQUEST['view'];
//$query = "SELECT smid, name, id_number, address1, address2, city, postcode, state, country, phone, email, group_id, company_name, date_added, user_id, blacklist FROM salesman ";
$query = "SELECT * FROM guest WHERE smid != 0 ";
//$query = "SELECT smid, name FROM salesman WHERE name LIKE '%a%' ";
if($view == 'not_active'){
//$blacklist = '0';
$today_minus_6_mth = strtotime(date('Y-m-d H:i:s').' -6 months');
$last_active_checkout_YmdHis = date('Y-m-d H:i:s', $today_minus_6_mth);
$condition .= " AND checkout < '$last_active_checkout_YmdHis'";
}
echo '';
//echo 'View All';
//echo 'View White List';
//echo 'View Black Listed';
//echo 'Add New Salesman';
echo 'Show Salesman Not Active For Last 6 Months ',$last_active_checkout_YmdHis,'';
echo '
';
if($view == 'whitelist'){
//$blacklist = '0';
$condition = " AND blacklist='0'";
} else if($view == 'blacklist') {
//$blacklist = '1';
$condition = " AND blacklist='1'";
}
if($salesman_name != ''){
$condition = $condition . " name LIKE '%$salesman_name%' ";
}
if($salesman_phone != ''){
$condition = $condition . " phone LIKE '%$salesman_phone%'";
}
//echo $query . $condition;
//salesman_report($query . $condition, $chk_level, $view, $pageno);
salesman_report($query , $condition, $chk_level, $view, $pageno);
break;
case 'police':
if($_POST['date']){
$date = $_POST['date'];
$date = date('Y-m-d', strtotime($date));
}else{
$date = date('Y-m-d');
}
if($date == date('Y-m-d')){
echo 'Listing Today\'s Police Report
';
}else{
echo 'Listing Police Report on '.date('d M Y', strtotime($date)).'
';
}
echo '';
$query = "SELECT log_id, room_id, guest_id, checkin, checkout, user_id, reservation_time FROM room_log ";
$today = date('Y-m-d');
if ($date != $today)
{
$condition = "WHERE (DATE_FORMAT(checkin, '%Y-%m-%d') <= '$date') AND (DATE_FORMAT(checkout, '%Y-%m-%d') >= '$date') AND (status='1' OR status='6' OR status='0') AND (final_status != '30' AND final_status != '40')"; //
} else {
$condition = "WHERE (DATE_FORMAT(checkin, '%Y-%m-%d') <= '$date') AND (DATE_FORMAT(checkout, '%Y-%m-%d') >= '$date') AND (status='1' OR status='6') AND (final_status = '0')"; // final_status = 0 is currently booking
}
//echo $query.$condition;
police_report($query, $condition);
break;
default :
break;
}// end switch report
?>