/booking/reservations/passlistrpt.php

https://bitbucket.org/anneivycat/california-tour · PHP · 226 lines · 162 code · 39 blank · 25 comment · 19 complexity · 4d3336d8d3a0cf5b6cce1562b67fbe15 MD5 · raw file 

    

  1. <?php
    2. 

  2. 

  3.    include_once '../sqlfuncs.php';
    4. 

  4.   
    5. 

  5.   /* $query = "select 
    6. 

  6.              S.SCHEDULEDTOUR_CODE, S.SCHEDULEDTOUR_PRICE,
    7. 

  7. 	     
    8. 

  8. 	     SB.SCHEDULEDTOURBOOK_ID, SB.FIRST_NAME, SB.LAST_NAME, SB.GENDER, SB.TOTALPAYTODLT, 
    9. 

  9. 	     SB.TOTALPAYTOAGENT, SB.TOTALPAYCC, SB.OCCUPANCY, SB.SCHEDULEDTOURBOOK_ID,
    10. 

  10. 	     
    11. 

  11. 	     T.TOUR_NAME, T.TOUR_SUPP_SNG, T.TOUR_SUPP_DBL, T.TOUR_SUPP_TRP, 
    12. 

  12. 	     T.TOUR_DISC1, T.TOUR_DISC2, T.TOUR_DISC3, T.TOUR_DISC4,  
    13. 

  13. 	     T.TOUR_DISC5, T.TOUR_DISC6, T.TOUR_DISC7, T.TOUR_DISC8, 
    14. 

  14. 	     
    15. 

  15. 	     A.AGENT_CODE, A.AGENT_COMMISIONTYPE, 
    16. 

  16. 	     
    17. 

  17. 	     P.PULOCATION_LOCATION, P.PULOCATION_TIME, P.PULOCATION_CODE,
    18. 

  18. 	     
    19. 

  19. 	     OT.OPTIONALTOUR_PRICE 
    20. 

  20. 	     
    21. 

  21. 	     FROM SCHEDULEDTOUR AS S, SCHEDULEDTOURBOOK AS SB, TOUR AS T,
    22. 

  22. 	     AGENT AS A, PULOCATION AS P, OPTIONALTOUR AS OT WHERE 
    23. 

  23. 	     
    24. 

  24. 	     S.TOUR_ID = T.TOUR_ID AND S.SCHEDULEDTOUR_ID = SB.SCHEDULEDTOUR_ID AND
    25. 

  25. 	     SB.PULOCATION_ID = P.PULOCATION_ID AND SB.AGENT_ID = A.AGENT_ID ";*/
    26. 

  26. 		 
    27. 

  27. 	// ===========	 
    28. 

  28. 	// 10/12/2008 - query update 
    29. 

  29. 	//	above query limits data when SB.PULOCATION_ID contains 0 using left join to get full data list
    30. 

  30. 	
    31. 

  31. 	$query = "select S.SCHEDULEDTOUR_CODE, S.SCHEDULEDTOUR_PRICE, 
    32. 

  32. 				SB.SCHEDULEDTOURBOOK_ID, SB.FIRST_NAME, SB.LAST_NAME, SB.GENDER, SB.TOTALPAYTODLT, 
    33. 

  33. 				SB.TOTALPAYTOAGENT, SB.TOTALPAYCC, SB.OCCUPANCY, SB.SCHEDULEDTOURBOOK_ID,
    34. 

  34. 				T.TOUR_NAME, T.TOUR_SUPP_SNG, T.TOUR_SUPP_DBL, T.TOUR_SUPP_TRP, 
    35. 

  35. 				T.TOUR_DISC1, T.TOUR_DISC2, T.TOUR_DISC3, T.TOUR_DISC4,  
    36. 

  36. 				T.TOUR_DISC5, T.TOUR_DISC6, T.TOUR_DISC7, T.TOUR_DISC8, 
    37. 

  37. 				A.AGENT_CODE, A.AGENT_COMMISIONTYPE, 
    38. 

  38. 				P.PULOCATION_LOCATION, P.PULOCATION_TIME, P.PULOCATION_CODE,
    39. 

  39. 				OT.OPTIONALTOUR_PRICE 
    40. 

  40. 				FROM SCHEDULEDTOUR AS S inner join SCHEDULEDTOURBOOK AS SB on S.SCHEDULEDTOUR_ID = SB.SCHEDULEDTOUR_ID 
    41. 

  41. 				inner join TOUR AS T on S.TOUR_ID = T.TOUR_ID 
    42. 

  42. 				inner join AGENT AS A on SB.AGENT_ID = A.AGENT_ID
    43. 

  43. 				left join OPTIONALTOUR AS OT on SB.OPTIONALTOUR_ID = OT.OPTIONALTOUR_ID
    44. 

  44. 				left join PULOCATION AS P on SB.PULOCATION_ID = P.PULOCATION_ID WHERE 1=1 ";
    45. 

  45. 	     
    46. 

  46.    if(isset($_POST['ScheduledTourId']) &&  $_POST['ScheduledTourId'] != '') {
    47. 

  47.      $query .= " AND S.SCHEDULEDTOUR_ID = '" . $_POST['ScheduledTourId'] . "' ";
    48. 

  48.    }
    49. 

  49.    
    50. 

  50.    if(isset($_POST['AgentId']) &&  $_POST['AgentId'] != '') {
    51. 

  51.      $query .= " AND A.AGENT_ID = '" . $_POST['AgentId'] . "' ";
    52. 

  52.    }
    53. 

  53.    
    54. 

  54.    if(isset($_POST['PULocationId']) &&  $_POST['PULocationId'] != '') {
    55. 

  55.      $query .= " AND P.PULOCATION_ID = '" . $_POST['PULocationId'] . "' ";
    56. 

  56.    }
    57. 

  57.    
    58. 

  58.    $query .= " AND SB.SCHEDULEDTOURBOOK_TIMESTAMP > '2007-01-01 01:00:00' ";
    59. 

  59.    
    60. 

  60.    $query .= " ORDER BY SCHEDULEDTOUR_CODE, PULOCATION_CODE, AGENT_CODE, LAST_NAME, FIRST_NAME ";
    61. 

  61.    
    62. 

  62.  //  echo "<p>$query</p>";
    63. 

  63.    $mysql_link   = connect_to_db();
    64. 

  64.    $mysql_result = select_db($mysql_link);
    65. 

  65.    $result       = mysql_query($query) or die("error occures: " . mysql_error());
    66. 

  66.    
    67. 

  67.    $data_array = array();
    68. 

  68.    while($row = mysql_fetch_array($result)) {
    69. 

  69. 

  70.      $data_array[$row['SCHEDULEDTOUR_CODE']]['TOUR_NAME'] = $row['TOUR_NAME'];
    71. 

  71.      
    72. 

  72.      $data_array[$row['SCHEDULEDTOUR_CODE']]['PULOCATION'][$row['PULOCATION_CODE']]['PULOCATION_LOCATION'] = $row['PULOCATION_LOCATION'];
    73. 

  73.      $data_array[$row['SCHEDULEDTOUR_CODE']]['PULOCATION'][$row['PULOCATION_CODE']]['PULOCATION_TIME'] = $row['PULOCATION_TIME'];
    74. 

  74.      
    75. 

  75.      $data_array[$row['SCHEDULEDTOUR_CODE']]['PULOCATION'][$row['PULOCATION_CODE']]['AGENT'][$row['AGENT_CODE']][$row['SCHEDULEDTOURBOOK_ID']]['AGENT_CODE'] = $row['AGENT_CODE'];
    76. 

  76.      $data_array[$row['SCHEDULEDTOUR_CODE']]['PULOCATION'][$row['PULOCATION_CODE']]['AGENT'][$row['AGENT_CODE']][$row['SCHEDULEDTOURBOOK_ID']]['LAST_NAME']  = $row['LAST_NAME'];
    77. 

  77.      $data_array[$row['SCHEDULEDTOUR_CODE']]['PULOCATION'][$row['PULOCATION_CODE']]['AGENT'][$row['AGENT_CODE']][$row['SCHEDULEDTOURBOOK_ID']]['FIRST_NAME'] = $row['FIRST_NAME'];
    78. 

  78.      $data_array[$row['SCHEDULEDTOUR_CODE']]['PULOCATION'][$row['PULOCATION_CODE']]['AGENT'][$row['AGENT_CODE']][$row['SCHEDULEDTOURBOOK_ID']]['GENDER']     = $row['GENDER'];
    79. 

  79.      
    80. 

  80.      $data_array[$row['SCHEDULEDTOUR_CODE']]['PULOCATION'][$row['PULOCATION_CODE']]['AGENT'][$row['AGENT_CODE']][$row['SCHEDULEDTOURBOOK_ID']]['TOTALPAYTODLT']   = $row['TOTALPAYTODLT'];
    81. 

  81.      $data_array[$row['SCHEDULEDTOUR_CODE']]['PULOCATION'][$row['PULOCATION_CODE']]['AGENT'][$row['AGENT_CODE']][$row['SCHEDULEDTOURBOOK_ID']]['TOTALPAYTOAGENT'] = $row['TOTALPAYTOAGENT'];
    82. 

  82.      $data_array[$row['SCHEDULEDTOUR_CODE']]['PULOCATION'][$row['PULOCATION_CODE']]['AGENT'][$row['AGENT_CODE']][$row['SCHEDULEDTOURBOOK_ID']]['TOTALPAYCC']      = $row['TOTALPAYCC'];
    83. 

  83.      
    84. 

  84.      $TotalCharge = $row['SCHEDULEDTOUR_PRICE'];
    85. 

  85.      
    86. 

  86.      if($row['OCCUPANCY'] == 1) 
    87. 

  87.        $TotalCharge +=  $row['TOUR_SUPP_SNG'];
    88. 

  88.      if($row['OCCUPANCY'] == 2) 
    89. 

  89.        $TotalCharge +=  $row['TOUR_SUPP_DBL'];
    90. 

  90.      if($row['OCCUPANCY'] == 3) 
    91. 

  91.        $TotalCharge +=  $row['TOUR_SUPP_TRP'];
    92. 

  92.        
    93. 

  93.      switch($row['AGENT_COMMISIONTYPE']) {
    94. 

  94.          case 1:
    95. 

  95.              $TotalCharge -= $row['TOUR_DISC1'];
    96. 

  96.          break;
    97. 

  97. 	 
    98. 

  98.          case 2:
    99. 

  99.              $TotalCharge -= $row['TOUR_DISC2'];
    100. 

  100.          break;
    101. 

  101.        
    102. 

  102. 	 case 3:
    103. 

  103. 	     $TotalCharge -= $row['TOUR_DISC3'];
    104. 

  104. 	 break;
    105. 

  105. 	 
    106. 

  106. 	 case 4:
    107. 

  107. 	     $TotalCharge -= $row['TOUR_DISC4'];
    108. 

  108. 	 break;
    109. 

  109. 	 
    110. 

  110. 	 case 5:
    111. 

  111. 	     $TotalCharge -= $row['TOUR_DISC5'];
    112. 

  112. 	 break;
    113. 

  113. 	 
    114. 

  114. 	 case 6:
    115. 

  115. 	     $TotalCharge -= $row['TOUR_DISC6'];
    116. 

  116. 	 break;
    117. 

  117. 	 
    118. 

  118. 	 case 7:
    119. 

  119. 	     $TotalCharge -= $row['TOUR_DISC7'];
    120. 

  120. 	 break;
    121. 

  121. 	 
    122. 

  122. 	 case 8:
    123. 

  123. 	     $TotalCharge -= $row['TOUR_DISC8'];
    124. 

  124. 	 break;
    125. 

  125. 	 
    126. 

  126. 	 default:
    127. 

  127. 	 
    128. 

  128. 	 break;
    129. 

  129.      }
    130. 

  130.         
    131. 

  131.      $WebDiscount = $TotalCharge - ($row['TOTALPAYTODLT'] + $row['TOTALPAYTOAGENT'] + $row['TOTALPAYCC']);
    132. 

  132.      $data_array[$row['SCHEDULEDTOUR_CODE']]['PULOCATION'][$row['PULOCATION_CODE']]['AGENT'][$row['AGENT_CODE']][$row['SCHEDULEDTOURBOOK_ID']]['TOTALCHARGE']  = $TotalCharge;
    133. 

  133.      $data_array[$row['SCHEDULEDTOUR_CODE']]['PULOCATION'][$row['PULOCATION_CODE']]['AGENT'][$row['AGENT_CODE']][$row['SCHEDULEDTOURBOOK_ID']]['WEBDISCOUNT']  = $WebDiscount;
    134. 

  134.      $data_array[$row['SCHEDULEDTOUR_CODE']]['PULOCATION'][$row['PULOCATION_CODE']]['AGENT'][$row['AGENT_CODE']][$row['SCHEDULEDTOURBOOK_ID']]['TOTALBALANCE'] = 0;
    135. 

  135. 

  136.    }
    137. 

  137. 

  138.    $sum_people_count = 0;
    139. 

  139.    $sum_total_charge = 0;
    140. 

  140.    $sum_web_discount = 0;
    141. 

  141.    $sum_pay_to_dlt   = 0;
    142. 

  142.    $sum_pay_to_agent = 0;
    143. 

  143.    $sum_pay_to_cc    = 0;
    144. 

  144.    $sum_balance      = 0;
    145. 

  145.    $no_record = true;
    146. 

  146.    foreach($data_array AS $s_key => $s_value) {
    147. 

  147.       $no_record = false;
    148. 

  148.       echo "<table width='900' style='font-size: 11px;font-family: arial, sans-serif;'>
    149. 

  149.               <tr><td colspan='9'><hr></td></tr>
    150. 

  150.               <tr><td colspan='9'><b>" . $s_key . " " .$s_value['TOUR_NAME'] . "</b></td></tr>
    151. 

  151.               <tr><td width='60'>Agents</td><td width='250'>Name(Last First)</td><td width='50'>Gender</td><td width='90'>Total Chg</td><td width='90'>Web Disc</td><td width='90'>Pd/DLT</td><td width='90'>Pd/AG</td><td width='90'>Pd/CC</td><td width='90'>Bal.</td></tr>
    152. 

  152. 	      <tr><td colspan='9'><hr></td></tr>";
    153. 

    154. 

  154.       $tour_people_count = 0;
    155. 

  155.       $tour_total_charge = 0;
    156. 

  156.       $tour_web_discount = 0;
    157. 

  157.       $tour_pay_to_dlt   = 0;
    158. 

  158.       $tour_pay_to_agent = 0;
    159. 

  159.       $tour_pay_to_cc    = 0;
    160. 

  160.       $tour_balance      = 0;
    161. 

  161.       foreach($s_value['PULOCATION'] AS $p_key => $p_value) {
    162. 

  162. 	 $pu_people_count = 0;
    163. 

  163.          $pu_total_charge = 0;
    164. 

  164.          $pu_web_discount = 0;
    165. 

  165.          $pu_pay_to_dlt   = 0;
    166. 

  166.          $pu_pay_to_agent = 0;
    167. 

  167.          $pu_pay_to_cc    = 0;
    168. 

  168.          $pu_balance      = 0;     
    169. 

  169. 	           
    170. 

  170. 	 $count = 0;
    171. 

  171. 	 echo "<tr><td colspan='9'><b>" . $p_key . " " .  $p_value['PULOCATION_LOCATION'] . "(" . $p_value['PULOCATION_TIME'] . ")" . "</b></td></tr>";
    172. 

  172. 	 
    173. 

  173. 	 foreach($p_value['AGENT'] AS $a_key => $a_value) {
    174. 

  174. 	     foreach($a_value AS $sb_key => $sb_value) {
    175. 

  175. 		 if(++$count <= 5) {
    176. 

  176. 	           echo "<tr><td>" . $a_key . "</td><td>" . $sb_value['LAST_NAME'] . " " . $sb_value['FIRST_NAME'] . "</td><td>" . $sb_value['GENDER'] . "</td><td>" . sprintf("%5.2f",$sb_value['TOTALCHARGE']) . "</td><td>" . sprintf("%5.2f",$sb_value['WEBDISCOUNT']) . "</td><td>" . sprintf("%5.2f",$sb_value['TOTALPAYTODLT']) . "</td><td>" . sprintf("%5.2f",$sb_value['TOTALPAYTOAGENT']) . "</td><td>" . sprintf("%5.2f",$sb_value['TOTALPAYCC']) . "</td><td>" . sprintf("%5.2f",$sb_value['TOTALBALANCE']) . "</td></tr>\n";
    177. 

  177. 		 }
    178. 

  178. 		 else {
    179. 

  179. 		   echo "<tr><td colspan='9'>&nbsp;</td></tr>\n";
    180. 

  180. 		   echo "<tr><td>" . $a_key . "</td><td>" . $sb_value['LAST_NAME'] . " " . $sb_value['FIRST_NAME'] . "</td><td>" . $sb_value['GENDER'] . "</td><td>" . sprintf("%5.2f",$sb_value['TOTALCHARGE']) . "</td><td>" . sprintf("%5.2f",$sb_value['WEBDISCOUNT']) . "</td><td>" . sprintf("%5.2f",$sb_value['TOTALPAYTODLT']) . "</td><td>" . sprintf("%5.2f",$sb_value['TOTALPAYTOAGENT']) . "</td><td>" . sprintf("%5.2f",$sb_value['TOTALPAYCC']) . "</td><td>" . sprintf("%5.2f",$sb_value['TOTALBALANCE']) . "</td></tr>\n";
    181. 

  181. 		   $count = 1;
    182. 

  182. 		 }
    183. 

  183. 		 $tour_people_count++;
    184. 

  184. 	         $tour_total_charge += $sb_value['TOTALCHARGE'];
    185. 

  185. 	         $tour_web_discount += $sb_value['WEBDISCOUNT'];
    186. 

  186. 	         $tour_pay_to_dlt   += $sb_value['TOTALPAYTODLT'];
    187. 

  187. 	         $tour_pay_to_agent += $sb_value['TOTALPAYTOAGENT'];
    188. 

  188. 	         $tour_pay_to_cc    += $sb_value['TOTALPAYCC'];
    189. 

  189. 	         $tour_balance      += $sb_value['TOTALBALANCE'];
    190. 

  190. 		 
    191. 

  191. 		 $pu_people_count++;
    192. 

  192. 	         $pu_total_charge   += $sb_value['TOTALCHARGE'];
    193. 

  193. 	         $pu_web_discount   += $sb_value['WEBDISCOUNT'];
    194. 

  194. 	         $pu_pay_to_dlt     += $sb_value['TOTALPAYTODLT'];
    195. 

  195. 	         $pu_pay_to_agent   += $sb_value['TOTALPAYTOAGENT'];
    196. 

  196. 	         $pu_pay_to_cc      += $sb_value['TOTALPAYCC'];
    197. 

  197. 	         $pu_balance        += $sb_value['TOTALBALANCE'];
    198. 

  198. 	     }
    199. 

  199. 	 }
    200. 

  200. 	 echo "<tr><td>Total:</td><td></td><td>" . $pu_people_count . "</td><td>" . sprintf("%5.2f",$pu_total_charge) . "</td><td>" . sprintf("%5.2f",$pu_web_discount) . "</td><td>" . sprintf("%5.2f",$pu_pay_to_dlt) . "</td><td>" . sprintf("%5.2f",$pu_pay_to_agent) . "</td><td>" . sprintf("%5.2f",$pu_pay_to_cc) . "</td><td>" . sprintf("%5.2f",$pu_balance) . "</td></tr>";
    201. 

  201.       }
    202. 

  202.       echo "<tr><td><b>Total:</b></td><td></td><td><b>" . $tour_people_count . "</b></td><td><b>" . sprintf("%5.2f",$tour_total_charge) . "</b></td><td><b>" . sprintf("%5.2f",$tour_web_discount) . "</b></td><td><b>" . sprintf("%5.2f",$tour_pay_to_dlt) . "</b></td><td><b>" . sprintf("%5.2f",$tour_pay_to_agent) . "</b></td><td><b>" . sprintf("%5.2f",$tour_pay_to_cc) . "</b></td><td><b>" . sprintf("%5.2f",$tour_balance) . "</b></td></tr>";
    203. 

  203.            
    204. 

  204.       echo  "</table><br><br>";
    205. 

  205.       
    206. 

  206.       $sum_people_count += $tour_people_count;
    207. 

  207.       $sum_total_charge += $tour_total_charge;
    208. 

  208.       $sum_web_discount += $tour_web_discount;
    209. 

  209.       $sum_pay_to_dlt   += $tour_pay_to_dlt;
    210. 

  210.       $sum_pay_to_agent += $tour_pay_to_agent;
    211. 

  211.       $sum_pay_to_cc    += $tour_pay_to_cc;
    212. 

  212.       $sum_balance      += $tour_balance;
    213. 

  213.    
    214. 

  214.    }
    215. 

  215.    
    216. 

  216.   if($no_record) {
    217. 

  217.      echo "<div align='center'>No Match is found</div>";
    218. 

  218.   }
    219. 

  219.   else {
    220. 

  220.      echo "<table width='900' style='font-size: 11px;font-family: arial, sans-serif;'>";
    221. 

  221.      echo "<tr><td colspan='2' width='310'><b>GRAND
    222. 

  222.            TOTAL:</b></td><td width='50'><b>$sum_people_count</b></td><td width='90'><b>" . sprintf("%5.2f", (int)$sum_total_charge) . "</b></td><td width='90'><b>" . sprintf("%5.2f",(int)$sum_web_discount) . "</b></td><td width='90'><b>" . sprintf("%5.2f",(int)$sum_pay_to_dlt) . "</b></td><td width='90'><b>" . sprintf("%5.2f",(int)$sum_pay_to_agent) . "</b></td><td width='90'><b>" . sprintf("%5.2f",(int)$sum_pay_to_cc) . "</b></td><td width='90'><b>" . sprintf("%5.2f",(int)$sum_balance) . "</b></td></tr>"; 
    223. 

  223.      echo "</table>";
    224. 

  224.   }
    225. 

  225. 

  226. ?>
    227.