<?
ob_start();
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
include 'include/db.php';
include 'include/connect.php';
require_once dirname(__FILE__) . '/PHPExcel/Classes/PHPExcel.php';


$id = $_GET['id'];
    $rs = $db->findAll('tbbooking')->execute();
        function DateThai($strDate){
      $strYear = date("Y",strtotime($strDate))+543;
      $strMonth= date("n",strtotime($strDate));
      $strDay= date("j",strtotime($strDate));
      $strHour= date("H",strtotime($strDate));
      $strMinute= date("i",strtotime($strDate));
      $strSeconds= date("s",strtotime($strDate));
      $strMonthCut = Array("","ม.ค.","ก.พ.","มี.ค.","เม.ย.","พ.ค.","มิ.ย.","ก.ค.","ส.ค.","ก.ย.","ต.ค.","พ.ย.","ธ.ค.");
      $strMonthThai=$strMonthCut[$strMonth];
      return "$strDay $strMonthThai $strYear";
    }

          function DateGoBack($DateGo,$DateBack){
      $strYear = date("Y",strtotime($DateBack))+543;
      $strMonth= date("n",strtotime($DateBack));
      $strDay = date("j",strtotime($DateBack));
      $strDay2 = date("j",strtotime($DateGo));
      // $strMonthCut = Array("","มกราคม","กุมภาพันธ์","มีนาคม","เมษายน","พฤษภาคม","มิถุนายน","กรกฎาคม","สิงหาคม","กันยายน","ตุลาคม","พฤศจิกายน","ธันวาคม");
      $strMonthCut = Array("","ม.ค.","ก.พ.","มี.ค.","เม.ย.","พ.ค.","มิ.ย.","ก.ค.","ส.ค.","ก.ย.","ต.ค.","พ.ย.","ธ.ค.");
      $strMonthThai=$strMonthCut[$strMonth];
      return "$strDay2 - $strDay $strMonthThai $strYear";
    }


$start = $_GET['m1'];
$td_date_chk = strtotime($start);
$td_date_st = date('Y-m-d', $td_date_chk);

$last = $_GET['m2'];
$lt_date_chk = strtotime($last);
$lt_date_st = date('Y-m-d', $lt_date_chk);


date_default_timezone_set('Asia/Bangkok');
ini_set('date.timezone', 'Asia/Bangkok');
$today_print = date('d M Y');
$time_print = date('H:i:s');


   // $todays = date("m/d/Y");
   $todays = date("Y-m-d");

  $todays2 = date("Y-m-d");

  $todays1 = date("m/d/Y");

   $book_today = $db->findByAttributes('tbbooking',array('book_date =' => $todays1))->execute();


    // $book_t = $db->findByAttributes('tbiv',array('iv_date =' => $todays))->execute();
    if($_GET['id'] == 49){
    $rstime = $db->findByAttributes('tbiv',array('iv_admin =' => 'เฟิร์น'))->execute();
    }else if($_GET['id'] == 51){
    $rstime = $db->findByAttributes('tbiv',array('iv_admin =' => 'หนุงหนิง'))->execute();
    }else if($_GET['id'] == 52){
    $rstime = $db->findByAttributes('tbiv',array('iv_admin =' => 'Patra'))->execute();


    }else{
    // $book_ad = $db->findByAttributes('tbiv',array('admin_id =' => $admin_id))->execute();
    $rstime = $db->findAll('tbiv')->execute();
    $rs = $db->findByAttributes('tbiv',array('admin_id =' => $admin_id))->execute();
   }



 if(!empty($_GET['m1']) || !empty($_GET['m2'])){
   $date_cc = DateGoBack($_GET['m1'],$_GET['m2']);
 }else{
   $date_cc = 'ALL';
 }

 $admin_head = $db->findByPK('tbadmin','admin_id',$id)->executeRow();
// Create new PHPExcel object
$objPHPExcel = new PHPExcel();
$objPHPExcel->setActiveSheetIndex(0);

// Set Styles
$center = array(
        'alignment' => array(
            'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
        )
    );

$left = array(
        'alignment' => array(
            'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT,
        )
    );

$right = array(
        'alignment' => array(
            'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT,
        )
    );

$middle = array(
        'alignment' => array(
            'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
        )
    );

$bgorange = array(
        'fill' => array(
            'type' => PHPExcel_Style_Fill::FILL_SOLID,
            'color' => array('rgb' => 'FFD966')
          )
    );

$bgyellow = array(
        'fill' => array(
            'type' => PHPExcel_Style_Fill::FILL_SOLID,
            'color' => array('rgb' => 'FFFF00')
          )
    );

$objPHPExcel->getDefaultStyle()->getFont()
            ->setName('Tahoma')
            ->setSize(12)
            ->setBold(false);

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


$header = array(
    'font'  => array(
    'bold' => true,
    'size' => 22,
    ));


$objPHPExcel->getDefaultStyle()->applyFromArray($center);
$objPHPExcel->getDefaultStyle()->applyFromArray($middle);;
// custom style with different font than the global one

$objPHPExcel->setActiveSheetIndex(0)->mergeCells('A5:O5');

// Add some data
$objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A1', '')
            ->setCellValue('B1', 'REPORT'.DateGoBack($start,$last).'')
            ->setCellValue('C1', '')
            ->setCellValue('D1', '');

$objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A2', '')
            ->setCellValue('B2', '')
            ->setCellValue('C2', '')
            ->setCellValue('D2', '');

$objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A3', 'ผู้ทำรายการ')
            ->setCellValue('B3', ''.$admin_head['admin_nickname'].'')
            ->setCellValue('C3', 'เวลาทำรายการ')
            ->setCellValue('D3', ''.$today_print.' '.$time_print.'');

$objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A5', 'PROGRAME LIST');

$objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A6', 'No.')
            ->setCellValue('B6', 'เลขที่ใบจอง')
            ->setCellValue('C6', 'ชื่อลูกค้า')
            ->setCellValue('D6', 'Wholesale')
            ->setCellValue('E6', 'รหัสทัวร์')
            ->setCellValue('F6', 'โปรแกรมทัวร์')
            ->setCellValue('G6', 'วันเดินทาง')
            ->setCellValue('H6', 'จำนวน')
            ->setCellValue('I6', 'ราคา/ท่าน')
            ->setCellValue('J6', 'ยอดลูกค้าชำระ')
            ->setCellValue('K6', 'ยอดชำระ Wholesale')
            ->setCellValue('L6', 'ส่วนลด/ส่วนเพิ่ม')
            ->setCellValue('M6', 'Com')
            ->setCellValue('N6', 'ค่าคอม')
            ->setCellValue('O6', 'หมายเหตุ');
            // ->setCellValue('G6', 'รายรับ')
            // ->setCellValue('H6', 'ต้นทุน')
            // ->setCellValue('I6', 'ยอดรวม')
            // ->setCellValue('J6', 'ผู้ดูแลกรุ๊ป')
            // ->setCellValue('K6', 'NOTE')

$i = 0;
$rox = 6;
while ($dbarr= mysql_fetch_array($rstime)){
  $g_date = $dbarr['pro_datego'];
            $g_explode = explode("-", $g_date);
            $g_year = $g_explode[0];
            $g_month = $g_explode[1];
            $g_day = $g_explode[2];

$sum_ad= $dbarr['adult_price'] * $dbarr['adult_num'];
$sum_c= $dbarr['c_price'] * $dbarr['c_num'];
$sum_nb= $dbarr['nb_price'] * $dbarr['nb_num'];
$sum_s= $dbarr['s_price'] * $dbarr['s_num'];


$custom= $dbarr['custom1_price'] * $dbarr['custom1_num'];
$custom2= $dbarr['custom2_price'] * $dbarr['custom2_num'];

$custom3= $dbarr['custom3_price'] * $dbarr['custom3_num'];
$custom4= $dbarr['custom4_price'] * $dbarr['custom4_num'];


if($dbarr['custom1_val'] == '0' ){
$ccustom= $dbarr['custom1_price'] * $dbarr['custom1_num'];
$ccustom_name_1= $dbarr['custom1_name'];
}else{
$ccustom= '0';
$ccustom_name_1= '';
}
if($dbarr['custom2_val'] == '0' ){
$ccustom2= $dbarr['custom2_price'] * $dbarr['custom2_num'];
$ccustom_name_2= $dbarr['custom2_name'];
}else{
$ccustom2= '0';
$ccustom_name_2= '';
}
if($dbarr['custom3_val'] == '0' ){
$ccustom3= $dbarr['custom3_price'] * $dbarr['custom3_num'];
$ccustom_name_3= $dbarr['custom3_name'];
}else{
$ccustom3= '0';
$ccustom_name_2= '';
}
if($dbarr['custom4_val'] == '0' ){
$ccustom4= $dbarr['custom4_price'] * $dbarr['custom4_num'];
$ccustom_name_4= $dbarr['custom4_name'];
}else{
$ccustom4= '0';
$ccustom_name_4= $dbarr['custom4_name'];
}


// $custom= $dbarr['custom1_price'] * $dbarr['custom1_num'];
// $custom2= $dbarr['custom2_price'] * $dbarr['custom2_num'];

// $custom3= $dbarr['custom3_price'] * $dbarr['custom3_num'];
// $custom4= $dbarr['custom4_price'] * $dbarr['custom4_num'];


$sum_dis= $dbarr['discount_price'] * $dbarr['discount_sum'];
$sum_dis2= $dbarr['cus_de_price'] * $dbarr['cus_de_num'];
$sum_dis3= $dbarr['cus_de_price2'] * $dbarr['cus_de_num2'];

$sum_dis4= $dbarr['cus_de_price3'] * $dbarr['cus_de_num3'];
// $sum_nb
// $sum_s

$total=$sum_ad + $sum_c + $sum_nb + $sum_s + $custom + $custom2 + $custom3 + $custom4 - $sum_dis - $sum_dis2 - $sum_dis3 - $sum_dis4;

// $total_pay = $dbarr['iv_pay_total'] - $total;

$gp222 = $db->findByPK('tbpayment','payment_iv',$dbarr['iv_id'])->execute();
while($gpm2 = mysql_fetch_array($gp222)){
          $re_custom = $db->findByPK('tbreceipt','receipt_pay',$gpm2['payment_id'])->executeRow();
          $p_c += $re_custom['receipt_total'];
      }

// if($gpm['payment_type'] == 'DEP'){

$gp_dep = $db->findByPK('tbpayment','payment_iv',$dbarr['iv_id'])->execute();
while($gp_dep2 = mysql_fetch_array($gp_dep)){
          $re_dep = $db->findByPK('tbreceipt','receipt_pay',$gp_dep2['payment_id'])->executeRow();
          if($gp_dep2['payment_type'] == 'DEP'){
          $p_dep += $re_dep['receipt_total'];
      }
      }

 $total_pay = $total - $p_c;
$dis_c = $sum_dis - $sum_dis2 - $sum_dis3 - $sum_dis4;
$plus_c = $custom + $custom2 + $custom3 + $custom4;

$book_all = $dbarr['adult_num']+$dbarr['c_num']+$dbarr['nb_num'];


$gpland = $db->findByPK('tbpayment','payment_iv',$dbarr['iv_id'])->execute();
while($land_pay = mysql_fetch_array($gpland)){
     if ($land_pay['payment_ref'] == '1') {
       $p_land += $land_pay['payment_total'];
     }
      }
   $total_pay_land = $r['iv_land_total'] - $p_land;

   $total_land_sent = $p_c - $p_land;


$com_sale = $r['iv_com_sale'] * $book_all;

$total_sum_sale += $com_sale;


if ($dbarr['iv_status_2'] == '0') {

if($g_date >= $td_date_st && $g_date <= $lt_date_st){
  $i++;
  $rox++;
  $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A'.$rox, $i)
            ->setCellValue('B'.$rox, $dbarr['iv_id'])
            ->setCellValue('C'.$rox, $dbarr['cus_name'])
            ->setCellValue('D'.$rox, $dbarr['iv_wholesale'])
            ->setCellValue('E'.$rox, $dbarr['iv_codepro'])
            ->setCellValue('F'.$rox, $dbarr['iv_namepro'])
            ->setCellValue('G'.$rox, ''.DateGoBack($dbarr['pro_datego'],$dbarr['pro_dateback']).'')
            ->setCellValue('H'.$rox, ''.$book_all.'')
            ->setCellValue('I'.$rox, ''.number_format($dbarr['adult_price']).'')
            ->setCellValue('J'.$rox, ''.number_format($p_c).'')
            ->setCellValue('K'.$rox, ''.number_format($p_land).'')
            ->setCellValue('L'.$rox, '('.number_format($dis_c).') + '.number_format($plus_c).'')
            ->setCellValue('M'.$rox, ''.number_format($dbarr['iv_com']).'')
            ->setCellValue('N'.$rox, ''.number_format($com_sale).'')
            ->setCellValue('O'.$rox, $dbarr['iv_admin']);

}$p_c = 0;$p_land = 0;
}
}

//   $i++;
//   $rox++;
//   if($trs['tl_sex'] == '0'){
//     $sex = 'M';
//   } else {
//     $sex = 'F';
//   }
//   $objPHPExcel->setActiveSheetIndex(0)
//             ->setCellValue('A'.$rox, $i)
//             ->setCellValue('B'.$rox, 'ADT')
//             ->setCellValue('C'.$rox, $sex)
//             ->setCellValue('D'.$rox, $trs['tl_titlename'])
//             ->setCellValue('E'.$rox, $trs['tl_lastname'])
//             ->setCellValue('F'.$rox, $trs['tl_firstname']);

// if(!empty($tl2id)){
//   $i++;
//   $rox++;
//   if($trss['tl_sex'] == '0'){
//     $sex = 'M';
//   } else {
//     $sex = 'F';
//   }
//   $objPHPExcel->setActiveSheetIndex(0)
//             ->setCellValue('A'.$rox, $i)
//             ->setCellValue('B'.$rox, $adt)
//             ->setCellValue('C'.$rox, $sex)
//             ->setCellValue('D'.$rox, $trss['tl_titlename'])
//             ->setCellValue('E'.$rox, $trss['tl_lastname'])
//             ->setCellValue('F'.$rox, $trss['tl_firstname']);
// }

$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(9.38);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(15.13);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(40.25);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(13.13);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(10.5);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(12.25);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(20.25);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(22.25);
$objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(18.25);
$objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(18.25);
$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(12.25);
$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(18.25);
$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(18.25);
$objPHPExcel->getActiveSheet()->getStyle('A1:D1')->applyFromArray($header);
$objPHPExcel->getActiveSheet()->getStyle('A5:F5')->applyFromArray($header);
$objPHPExcel->getActiveSheet()->getStyle('A2:C3')->applyFromArray($left);
$objPHPExcel->getActiveSheet()->getStyle('D2:D3')->applyFromArray($left);
$objPHPExcel->getActiveSheet()->getStyle('A5:O5')->applyFromArray($bgorange);
$objPHPExcel->getActiveSheet()->getStyle('A6:O6')->applyFromArray($bgyellow);
$objPHPExcel->getActiveSheet()->getStyle('C6:D6')->getAlignment()->setWrapText(true);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('D2')->getNumberFormat()->setFormatCode('B1d-mmm-yy');
$objPHPExcel->setActiveSheetIndex(0)->getStyle('D3')->getNumberFormat()->setFormatCode('B1d-mmm');

ob_clean();
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
PHPExcel_Settings::setZipClass(PHPExcel_Settings::PCLZIP);
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="REPORT_'.DateGoBack($start,$last).'.xlsx"');
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');

header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header ('Pragma: public'); // HTTP/1.0


$objWriter->save('php://output');
exit;
?>