<?php
session_start();
if(!session_is_registered(myusername)){
header("location:../index.php");
}
header('Content-Type: text/html; charset=utf-8'); 

include '../conf/config.php';
mysql_query("set character_set_server='utf8'");
mysql_query("set names 'utf8'");
$debug = 1;
$h17=$_GET['pid'];
$s=calc_price($h17,$conn,$debug);

if($s){
echo "Prices has been Generated Succsessfuly";
}else{

echo "cant Generate this package";
}
//calc_all_packages($conn);
//delete_all_packages555555($conn);
//delete_package($conn,$pid);

//archive_one_package(60,$conn,1);

FUNCTION getRoomData($pid,$conn)
{

	$sql = "select 
		`p`.`package_id` AS `package_id`,
		`p`.`package_name` AS `package_name`,
		`p`.`package_type` AS `package_type`,
		`pp`.`place_detail_id` AS `place_detail_id`,
		`p`.`from_date` AS `from_date`,
		`p`.`to_date` AS `to_date`,
		`pl`.`place_name` AS `place_name`,
		`pl`.`ar_name` AS `ar_name`,
		`pl`.`place_id` AS `place_id`,
		`ph`.`hotel_detail_id` AS `hotel_detail_id`,
		`ho`.`hotel_name` AS `hotel_name`,
		`ho`.`hotel_id` AS `hotel_id`,
		`ho`.`hotel_name_ar` AS `hotel_name_ar`,
		`ho`.`area_name` AS `area_name`,
		`ho`.`area_name_en` AS `area_name_en`,
		`ho`.`rating` AS `rating`,
		`ho`.`rating` AS `ratingValue`,
		`ho`.`website` AS `website`,
		`grt`.`room_type_name` AS `room_type_name`,
		`grt`.`number_of_people` AS `number_of_people`,
		`grt`.`type` AS `type`,
		`grv`.`room_view_name` AS `room_view_name`,
		`hp`.`start_date` AS `start_date`,
		`hp`.`end_date` AS `end_date`,
		'' AS `meal_names`,
		'' AS `tour_names`,
		'' AS `transportation_names`,
		'' AS `tickets_name`,
		if(((`hp`.`end_date` > `p`.`from_date`) and (`hp`.`start_date` < `p`.`to_date`)),
			(((`rv`.`additional_price` * 1.5) + if(`hp`.`spatial_price`>0,`hp`.`spatial_price`,`hp`.`base_price`)) * `ph`.`num_of_nights`)
			,0) AS `Total`,
		if(((`hp`.`end_date` > `p`.`from_date`) and (`hp`.`start_date` < `p`.`to_date`) and (`grt`.`general_room_type_id` = 2)),
			(((`rv`.`additional_price` * 1.5) + (if(`hp`.`spatial_price`>0,`hp`.`spatial_price`,`hp`.`base_price`) / 2)) * `ph`.`num_of_nights`)
			,0) AS `child_2_12_with_bed`,
			
			if(((`hp`.`end_date` > `p`.`from_date`) and (`hp`.`start_date` < `p`.`to_date`) and (`grt`.`general_room_type_id` = 2)),
			(((`rv`.`additional_price` * 1.5) + (if(`hp`.`spatial_price`>0,`hp`.`spatial_price`,`hp`.`base_price`) / 3)) * `ph`.`num_of_nights`)
			,0) AS `child_6_12_without_bed` ,
		'' AS `child_2_6_without_bed`
			
		from ((((((((((`Package_Details_Places` `pp` join `Package_Details_Hotels` `ph`) join 
		`Package_Details_Rooms` `pr`) join `Room_View` `rv`) join `Room_Type` `rt`) join 
		`Hotel_Prices` `hp`) join `General_Room_Types` `grt`) join `General_Room_Views` `grv`) join 
		`Packages` `p`) join `Places` `pl`) join `Hotels` `ho` )
		
		where (
				(`ph`.`place_detail_id_f` = `pp`.`place_detail_id`) and 
				(`pr`.`hotel_detail_id_f` = `ph`.`hotel_detail_id`) and 
				(`rv`.`room_view_id` = `pr`.`room_type_view_f`) and 
				(`rt`.`room_type_id` = `rv`.`room_type_id_f`) and 
				(`hp`.`room_type_id_f` = `rt`.`room_type_id`) and 
				(`rv`.`general_room_view_id_f` = `grv`.`general_room_view_id`) and 
				(`rt`.`general_room_type_id_f` = `grt`.`general_room_type_id`) and 
				(`p`.`package_id` = `pp`.`package_id_f`) and 
				(`pp`.`place_id_f` = `pl`.`place_id`) and 
				(`ph`.`hotel_id_f` = `ho`.`hotel_id`) and 
				(`p`.`package_id` = '$pid') and 
				(`hp`.`start_date` < `p`.`to_date`) and 
				(`hp`.`end_date` > `p`.`from_date`) and 
				(`rv`.`report_show` = 1)
				)";
		
		
		$result = mysql_query($sql,$conn);

		$rows = array();

		while(($row = mysql_fetch_array($result))) {
			$rows[] = $row;
		}

	return $rows;
}

FUNCTION getMealData($pid,$conn)
{


$sql = "select `pp`.`package_id_f` AS `package_id_f`,
		`pp`.`place_id_f` AS `place_id_f`,
		`ph`.`hotel_id_f` AS `hotel_id_f`,
		`gm`.`meal_name` AS `meal_names`,
		(`ph`.`num_of_nights` * sum(`m`.`additional_price`)) AS `total_meals` 
		
		from 
			((((`Package_Details_Places` `pp` join
			`Package_Details_Hotels` `ph`) join
			`Package_Details_Meals` `pm`) join
			`Meals` `m`) join
			`General_Meals` `gm`) 
		where ((`ph`.`place_detail_id_f` = `pp`.`place_detail_id`) and 
			(`pm`.`hotel_detail_id_f` = `ph`.`hotel_detail_id`) and 
			(`m`.`meal_id` = `pm`.`meal_id_f`) and 
			(`m`.`general_meal_id_f` = `gm`.`general_meal_id`) and 
			(`pp`.`package_id_f` = '$pid')
			) 
		group by 1,2,3,4";
		
		
	$result = mysql_query($sql,$conn);

	$rows = array();

	while(($row = mysql_fetch_array($result))) {
		$rows[] = $row;
	}

	return $rows;

}

FUNCTION getTicketData($pid,$conn)
{

	$sql = "select `pdt`.`package_id_f` AS `package_id_f`,
				group_concat(`t`.`ticket_name` separator '-') AS `tickets_name`,
				sum(`t`.`additional_price`) AS `total_tickets`,
				sum(`t`.`child_price`) AS `total_tickets_child` 
			from (`Package_Details_Tickets` `pdt` join
				`Tickets` `t`) 
			where (`t`.`ticket_id` = `pdt`.`ticket_id_f`) and 
					(pdt.package_id_f = '$pid')
			group by 1";
			
		
	$result = mysql_query($sql,$conn);

	$rows = array();

	while(($row = mysql_fetch_array($result))) {
		$rows[] = $row;
	}

	return $rows;		

}

FUNCTION getTransportationData($pid,$conn)
{


	$sql_old = "select `pdt`.`package_id_f` AS `package_id_f`,
					t.place_id_f,
					group_concat(`t`.`transportation_name` separator '-') AS `transportation_names`,
					sum(`t`.`additional_price`) AS `total_transportations` 
		from (`Package_Details_Transportation` `pdt` join
				`Transportations` `t`) 
		where (`t`.`transportation_id` = `pdt`.`transportaion_id_f`) and 
				(pdt.package_id_f = '$pid')
		
		group by 1,2";
		
		
	$sql = "select 
			pdp.place_detail_id , 
			group_concat(t.transportation_name separator '-') AS transportation_names,
			sum(`t`.`additional_price`) AS `total_transportations` 

		from Package_Details_Transportation pdt,
		Transportations t,
		Package_Details_Places pdp
		where 
		pdp.place_detail_id = pdt.place_detail_id_f and
		t.transportation_id = pdt.transportaion_id_f and 
		pdp.package_id_f = '$pid' group by 1";	

	$result = mysql_query($sql,$conn);

	$rows = array();

	while(($row = mysql_fetch_array($result))) {
		$rows[] = $row;
	}

	return $rows;	

}

FUNCTION getTourData($pid,$conn)
{
		$sql_old = "select `pdt`.`place_detail_id_f` AS `place_detail_id_f`,
			t.place_id_f,
			group_concat(`t`.`tour_name` separator '-') AS `tour_names`,
			sum(`t`.`additional_price`) AS `total_tours` 
		from (`Package_Details_Tours` `pdt` join `Tours` `t`) 
		
		where (`t`.`tour_id` = `pdt`.`tour_id_f`) and 
				(pdt.package_id_f = '$pid')
		
		group by 1,2";


		$sql = "select 
			pdp.place_detail_id , 
			group_concat(t.tour_name separator '-') AS tour_names,
			sum(`t`.`additional_price`) AS `total_tours` 

		from Package_Details_Tours pdt,
		Tours t,
		Package_Details_Places pdp
		where 
		pdp.place_detail_id = pdt.place_detail_id_f and
		t.tour_id = pdt.tour_id_f and 
		pdp.package_id_f = '$pid' group by 1";

	$result = mysql_query($sql,$conn);

	$rows = array();

	while(($row = mysql_fetch_array($result))) {
		$rows[] = $row;
	}

	return $rows;	

}
FUNCTION getMargin($pid,$conn)
{

	$sql = " select margin_name,type,starts3,starts3DLX,starts4,starts4DLX,starts5,starts5DLX from Margins,Packages p where margin_id = p.margin_id_f and package_id = '$pid' ";
			
	$result = mysql_query($sql,$conn);

	$rows = array();

	while(($row = mysql_fetch_array($result))) {
		$rows[] = $row;
	}

	return $rows;		

}

FUNCTION getCombinationData($pid,$conn)
{
	$sql = " select hotel_detail_id_f_1,hotel_detail_id_f_2,hotel_detail_id_f_3 from Package_Hotels_Comb where package_id_f ='$pid' ";
			
	$result = mysql_query($sql,$conn);

	$rows = array();

	while(($row = mysql_fetch_array($result))) {
		$rows[] = $row;
	}

	return $rows;	
}
FUNCTION isZeroPackage($pid,$conn)
{
	$sql = " select `package_type`,`generation_count` from Packages where package_id ='$pid' ";
			
	$result = mysql_query($sql,$conn);

	$rows = array();

	while(($row = mysql_fetch_array($result))) {
		$rows[] = $row;
	}

	if($rows[0]['package_type'] == 'Zero' || $rows[0]['package_type'] == 'Umra' )
	{	
		if($rows[0]['generation_count'] > 0)
		{	
			return 1;
		}
	}
	return 0;
}
FUNCTION calc_price($pid,$conn,$debug)
{
	if(isZeroPackage($pid,$conn))
	{
		return 0;
	}

	$rooms = (getRoomData($pid,$conn));

	$meals = (getMealData($pid,$conn));

	$tickets = (getTicketData($pid,$conn));

	$trans = (getTransportationData($pid,$conn));

	$tours = (getTourData($pid,$conn));
	
	$margins = getMargin($pid,$conn);

	$combs = getCombinationData($pid,$conn);
	
	if($debug)
	{
		echo "Rooms";
		printTable($rooms);
		
		echo "<br>";
		echo "meals";
		printTable($meals);
		
		echo "<br>";
		echo "tickets";
		printTable($tickets);
		
		echo "<br>";
		echo "trans";
		printTable($trans);
		
		echo "<br>";
		echo "tours";
		printTable($tours);
		
		echo "<br>";
		echo "margins";
		printTable($margins);
	}

	$newRooms = array();
	
	if(aLength($rooms)==0 or aLength($meals)==0)
		echo "error no rooms or no meals";
		
	
	foreach($rooms as $room )
	{
		foreach($meals as $meal )
		{	
			if($meal['hotel_id_f'] != $room['hotel_id'])
				continue;
				
			$newRoom = $room;
			$newRoom['meal_names'] = $meal['meal_names'];
			if($newRoom['number_of_people'] == "1")
			{
				$newRoom['Total'] = $newRoom['Total'] + $meal['total_meals'] * 2;
				$newRoom['child_2_12_with_bed'] = 0;
				$newRoom['child_6_12_without_bed'] = 0;
				$newRoom['child_2_6_without_bed'] = 0;
			}
			
			elseif($newRoom['number_of_people'] == "2")
			
			{
				$newRoom['Total'] = $newRoom['Total'] + $meal['total_meals'];
				$newRoom['child_2_12_with_bed'] = $newRoom['Total']/2 + $meal['total_meals'];
				$newRoom['child_6_12_without_bed'] = $newRoom['Total']/3 + $meal['total_meals'];
				$newRoom['child_2_6_without_bed'] = 0;
			}
			
			else //shouldnt get here
			{
				$newRoom['Total'] = $newRoom['Total'] + $meal['total_meals'];
				$newRoom['child_2_12_with_bed'] = $newRoom['Total'] + $meal['total_meals'];
				$newRoom['child_6_12_without_bed'] = $newRoom['Total'] + $meal['total_meals'];
				$newRoom['child_2_6_without_bed'] = $newRoom['Total'] + $meal['total_meals'];
			}
			
			//print_r($newRoom);
			echo "<br>";
			
			
			if (aLength($tours)>0)
			{
				foreach($tours as $tour)
				{
					if($tour['place_detail_id'] != $room['place_detail_id'])
						continue;
					if($newRoom['Total'] == 0)
						continue;	

					$newRoom['tour_names'] = $tour['tour_names'];
					$newRoom['Total'] = $newRoom['Total'] + $tour['total_tours'];
					if($newRoom['number_of_people'] == "2")
					{
						$newRoom['child_2_12_with_bed'] = $newRoom['child_2_12_with_bed'] + $tour['total_tours'];
						$newRoom['child_6_12_without_bed'] = $newRoom['child_6_12_without_bed'] + $tour['total_tours'];
						$newRoom['child_2_6_without_bed'] = $newRoom['child_2_6_without_bed'];
					}
				}
			}else
			{
				$newRoom['tour_names'] = 'NA';	
			}
			
			
			if (aLength($trans)>0)
			{
				foreach($trans as $tran)
				{
					if($tran['place_detail_id'] != $room['place_detail_id'])
						continue;
						
					if($newRoom['Total'] == 0)
						continue;
						
					$newRoom['transportation_names'] = $tran['transportation_names'];
					$newRoom['Total'] = $newRoom['Total'] + $tran['total_transportations'];
					if($newRoom['number_of_people'] == "2")
					{
						$newRoom['child_2_12_with_bed'] = $newRoom['child_2_12_with_bed'] + $tran['total_transportations'];
						$newRoom['child_6_12_without_bed'] = $newRoom['child_6_12_without_bed'] + $tran['total_transportations'];
						$newRoom['child_2_6_without_bed'] = $newRoom['child_2_6_without_bed'];
					}
				}
			}else
			{
				$newRoom['transportation_names'] = 'NA';	
			}
			
		
			$newRoom['start_date'] = MAX($newRoom['from_date'],$newRoom['start_date']);
			$newRoom['end_date'] = MIN($newRoom['to_date'],$newRoom['end_date']);
			

			$newRooms[] = $newRoom;

		}
		
		
	}
	if($debug)
	{
		echo "New Rooms";
		printTable($newRooms);
	}
	
	
	//Combination Section\
	$sep = " - ";
	$newRoomsC = array();
	if (aLength($combs)>0)
	{
		echo " <table border='1'><tr>";
		$newRooms2 = $newRooms;
		$newRooms3 = $newRooms;
		$x = 0;
		$y = 0;
		foreach($combs as $comb)
			{
				foreach($newRooms as $newRoom1)
				{
					$x++;
					$y = 0;
					foreach($newRooms2 as $newRoom2)
					{
						$y++;
						echo "<tr>Try Combine " . $comb['hotel_detail_id_f_1'] . " and " . $comb['hotel_detail_id_f_2'];
						echo "Rooms are " . $newRoom1['hotel_detail_id'] . " and " . $newRoom2['hotel_detail_id'];
						
						if($comb['hotel_detail_id_f_1'] == $newRoom1['hotel_detail_id'] && $comb['hotel_detail_id_f_2'] == $newRoom2['hotel_detail_id'])
						{
							if($newRoom1['number_of_people'] == $newRoom2['number_of_people'])
							{
								if(($newRoom1['end_date'] < $newRoom2['start_date']) || ($newRoom2['end_date'] < $newRoom1['start_date']))
								{
									echo "Room" . $newRoom1['hotel_name'] . "-" . $newRoom1['room_type_name'] . " Could not be combined with " . $newRoom2['hotel_name'] . "-" . $newRoom2['room_type_name'];
									Continue;
								}
								$newRoomC = $newRoom1;
								$newRoomC['place_name'] = 		$newRoom1['place_name']		 			 . $sep . 		$newRoom2['place_name'];
								$newRoomC['hotel_name'] = 		$newRoom1['hotel_name']					 . $sep .		$newRoom2['hotel_name'];
								$newRoomC['hotel_name_ar'] = 	$newRoom1['hotel_name_ar']				 . $sep . 		$newRoom2['hotel_name_ar'];
								$newRoomC['area_name'] =		$newRoom1['area_name']					 . $sep . 		$newRoom2['area_name'];
								$newRoomC['area_name_en'] =		$newRoom1['area_name_en']				 . $sep . 		$newRoom2['area_name_en'];
								
								$newRoomC['ar_name'] =			$newRoom1['ar_name']					 . $sep . 		$newRoom2['ar_name'];
								
								$newRoomC['rating'] 	= 		$newRoom1['rating']						 . $sep . 		$newRoom2['rating'];
								$newRoomC['ratingValue'] 	= 		MAX($newRoom1['rating'],	$newRoom2['rating']);
								$newRoomC['website'] = 			$newRoom1['website'] 					 . $sep . 		$newRoom2['website'];
								//if($newRoom1['room_type_name']	!= $newRoom2['room_type_name'])
								//{
									$newRoomC['room_type_name'] = 	$newRoom1['room_type_name']				 . $sep . 		$newRoom2['room_type_name'];
								//}
								$newRoomC['room_view_name'] = 	$newRoom1['room_view_name']				 . $sep . 		$newRoom2['room_view_name'];
								$newRoomC['start_date'] = 		MAX($newRoom1['start_date'],	$newRoom2['start_date']);
								$newRoomC['end_date'] = 		MIN($newRoom1['end_date'],	$newRoom2['end_date']);
								$newRoomC['meal_names'] = 		$newRoom1['meal_names']					 . $sep . 		$newRoom2['meal_names'];
								$newRoomC['tour_names'] = 		$newRoom1['tour_names']					 . $sep . 		$newRoom2['tour_names'];	
								$newRoomC['transportation_names']=	$newRoom1['transportation_names']	 . $sep . 		$newRoom2['transportation_names'];
								$newRoomC['Total'] 		= 			$newRoom1['Total']		 	 		 + 		$newRoom2['Total'];
								$newRoomC['child_2_12_with_bed'] = 	$newRoom1['child_2_12_with_bed']	 + 		$newRoom2['child_2_12_with_bed'];
								$newRoomC['child_6_12_without_bed']=$newRoom1['child_6_12_without_bed']	 +		$newRoom2['child_6_12_without_bed'];
								$newRoomC['child_2_6_without_bed'] =$newRoom1['child_2_6_without_bed'] 	 + 		$newRoom2['child_2_6_without_bed'];
								
								if($comb['hotel_detail_id_f_3'] > 0)
								{	
									foreach($newRooms3 as $newRoom3)
									{
										if($comb['hotel_detail_id_f_3'] == $newRoom3['hotel_detail_id'] && $comb['hotel_detail_id_f_2'] == $newRoom2['hotel_detail_id'])
											{
												if($newRoomC['number_of_people'] == $newRoom3['number_of_people'])
												{
													$newRoomC3 = $newRoomC;
													
													$newRoomC3['place_name'] = 		$newRoomC['place_name']		 			 . $sep . 		$newRoom3['place_name'];
													$newRoomC3['hotel_name'] = 		$newRoomC['hotel_name']					 . $sep .		$newRoom3['hotel_name'];
													$newRoomC3['hotel_name_ar'] = 	$newRoomC['hotel_name_ar']				 . $sep . 		$newRoom3['hotel_name_ar'];
													$newRoomC3['area_name'] =		$newRoomC['area_name']					 . $sep . 		$newRoom3['area_name'];
													$newRoomC3['area_name_en'] =	$newRoomC['area_name_en']				 . $sep . 		$newRoom3['area_name_en'];
													$newRoomC3['ar_name'] =			$newRoomC['ar_name']					 . $sep . 		$newRoom3['ar_name'];
													
													$newRoomC3['rating'] 	= 		$newRoomC['rating']						 . $sep .		$newRoom3['rating'];
													$newRoomC3['ratingValue'] 	= 		MAX($newRoomC['rating'],	$newRoom3['rating']);
													
													$newRoomC3['website'] = 			$newRoomC['website'] 					 . $sep . 		$newRoom3['website'];
													//if($newRoomC3['room_type_name']	!= $newRoomC['room_type_name'])
													//{
														$newRoomC3['room_type_name'] = 	$newRoomC['room_type_name']				 . $sep . 		$newRoom3['room_type_name'];
													//}
													$newRoomC3['room_view_name'] = 	$newRoomC['room_view_name']				 . $sep . 		$newRoom3['room_view_name'];
													$newRoomC3['start_date'] = 		MAX($newRoomC['start_date'],	$newRoom3['start_date']);
													$newRoomC3['end_date'] = 		MIN($newRoomC['end_date'],	$newRoom3['end_date']);
													$newRoomC3['meal_names'] = 		$newRoomC['meal_names']					 . $sep . 		$newRoom3['meal_names'];
													$newRoomC3['tour_names'] = 		$newRoomC['tour_names']					 . $sep . 		$newRoom3['tour_names'];	
													$newRoomC3['transportation_names']=	$newRoomC['transportation_names']	 . $sep . 		$newRoom3['transportation_names'];
													$newRoomC3['Total'] 		= 			$newRoomC['Total']		 	 		 + 		$newRoom3['Total'];
													$newRoomC3['child_2_12_with_bed'] = 	$newRoomC['child_2_12_with_bed']	 + 		$newRoom3['child_2_12_with_bed'];
													$newRoomC3['child_6_12_without_bed']=$newRoomC['child_6_12_without_bed']	 +		$newRoom3['child_6_12_without_bed'];
													$newRoomC3['child_2_6_without_bed'] =$newRoomC['child_2_6_without_bed'] 	 + 		$newRoom3['child_2_6_without_bed'];	
													
													$newRoomsC[] = $newRoomC3;
													
													$found = 1;
												}
											}
									}
								}
								
								if($found != 1)
								{
									$newRoomsC[] = $newRoomC;	
								}
								echo "  Succes</tr>";
							}
							else
							{
								echo "Failed1</tr>";
							}
						}else
						{
							echo "Failed2</tr>";
						}
					//echo "EEE:" .$y . "EEE";	
					}
				}	
			}	
			
			echo " </table>";
			
			//echo "x:" .$x;
			//echo "y:" .$y;
		//adding the old rows which are not used in combination to new Rooms Combined
		if(0) // no need - there are only combined packages or regular ones 
		{
			foreach($newRooms as $newRoom)
			{
				$c = 0;
				foreach($combs as $comb)
				{
					if($comb['hotel_detail_id_f_3'] > 0)
					{
						if($comb['hotel_detail_id_f_1'] != $newRoom['hotel_detail_id'] && 
							$comb['hotel_detail_id_f_2'] != $newRoom['hotel_detail_id'] && 
							$comb['hotel_detail_id_f_3'] != $newRoom['hotel_detail_id'])
							{
								$c = $c + 1;
							}
					}
					else
					{
						if($comb['hotel_detail_id_f_1'] != $newRoom['hotel_detail_id'] && $comb['hotel_detail_id_f_2'] != $newRoom['hotel_detail_id'])
						{
							$c = $c + 1;
						}
					}
				}
				if($c==aLength($combs))
				{
					$newRoomsC[] = $newRoom;
				}
			}
		}

		
		$newRooms = $newRoomsC;	
			
	}
	
	//End Combination Section 
	
	//adding tickets and margion
	$newRoomsC = array();
	foreach($newRooms as $newRoom)
	{
		if (aLength($tickets)>0)
		{
			if($newRoom['Total'] == 0)
				continue;
		
			$newRoom['tickets_name'] = $tickets[0]['tickets_name'];
			$newRoom['Total'] = $newRoom['Total'] + $tickets[0]['total_tickets'];
			if($newRoom['number_of_people'] == "2")
			{
				$newRoom['child_2_12_with_bed'] = $newRoom['child_2_12_with_bed'] + $tickets[0]['total_tickets_child'];
				$newRoom['child_6_12_without_bed'] = $newRoom['child_6_12_without_bed'] + $tickets[0]['total_tickets_child'];
				$newRoom['child_2_6_without_bed'] = $newRoom['child_2_6_without_bed'] + $tickets[0]['total_tickets_child'];
			}
		}else
		{
			$newRoom['tickets_name'] = 'NA';	
		}
		
		//Margions
		
		if (aLength($margins)>0)// && $room['package_type'] == 'Normal')
		{
			if($newRoom['ratingValue'] == "5")
			{
				$value = $margins[0]['starts5'];
			}
			elseif ($newRoom['ratingValue'] == "5DLX")
			{
				$value = $margins[0]['starts5DLX'];
			}
			elseif ($newRoom['ratingValue'] == "4")
			{
				$value = $margins[0]['starts4'];
			}
			elseif ($newRoom['ratingValue'] == "4DLX")
			{
				$value = $margins[0]['starts4DLX'];
			}
			elseif ($newRoom['ratingValue'] == "3")
			{
				$value = $margins[0]['starts3'];
			}
			elseif ($newRoom['ratingValue'] == "3DLX")
			{
				$value = $margins[0]['starts3DLX'];
			}
			
			if($margins[0]['type'] == 'FIXED')
			{
				if($newRoom['Total'] == 0)
				{
				}
				else
				{
					if($newRoom['number_of_people'] == "1")
					{
						$newRoom['Total'] = $newRoom['Total'] + $value * 2;
					}
					if($newRoom['number_of_people'] == "2")
					{
						$newRoom['Total'] = $newRoom['Total'] + $value;
						$newRoom['child_2_12_with_bed'] = $newRoom['child_2_12_with_bed'] + $value/2 ;
						$newRoom['child_6_12_without_bed'] = $newRoom['child_6_12_without_bed'] + $value/2;
						$newRoom['child_2_6_without_bed'] = $newRoom['child_2_6_without_bed'] + $value/2;
					}
				}
			}
			else
			{
				if($newRoom['Total'] == 0)
				{
				}
				else
				{
					$newRoom['Total'] = $newRoom['Total'] + $newRoom['Total'] * $value/100;
					if($newRoom['number_of_people'] == "2")
					{
						$newRoom['child_2_12_with_bed'] = $newRoom['child_2_12_with_bed'] + $newRoom['child_2_12_with_bed'] * $value/100;
						$newRoom['child_6_12_without_bed'] = $newRoom['child_6_12_without_bed'] + $newRoom['child_6_12_without_bed'] * $value/100;
						$newRoom['child_2_6_without_bed'] = $newRoom['child_2_6_without_bed'] + $newRoom['child_2_6_without_bed'] * $value/100 ;
					}
				}
			}
		}
		
		if($room['package_type'] == 'Zero' || $room['package_type'] == 'Umra' )//no need for prices 
		{
			$newRoom['Total'] = 0;
			$newRoom['child_2_12_with_bed'] =0;
			$newRoom['child_6_12_without_bed'] = 0;
			$newRoom['child_2_6_without_bed'] =0;
		}elseif($room['package_type'] == 'Normal')
		{
			$newRoom['Total'] = round($newRoom['Total']/5) * 5;
			$newRoom['child_2_12_with_bed'] = round($newRoom['child_2_12_with_bed']/5) * 5;
			$newRoom['child_6_12_without_bed'] = round($newRoom['child_6_12_without_bed']/5) * 5;
			$newRoom['child_2_6_without_bed'] = round($newRoom['child_2_6_without_bed']/5) * 5;
		}
		
		$newRoomsC[] = $newRoom;
		
	}

	$newRooms = $newRoomsC;
	
	if(!mysql_query("DELETE FROM Package_Prices WHERE package_id='$pid'",$conn))
		{
		return 0;
		//echo "DELETE FROM  WHERE package_id='$pid'";
		//echo "Delete Failed";
		};
		
	foreach($newRooms AS $room)
		{
		$execlude = array();
		$execlude[] = "hotel_id";
		$execlude[] = "place_id";
		$execlude[] = "hotel_detail_id";
		$execlude[] = "package_type";
		$execlude[] = "place_detail_id";
		$execlude[] = "ratingValue";
		

		$res = mysql_insert_array("Package_Prices", $room, $execlude,$conn,$pid,$debug);
		if($debug)
			{
			print_r($res);
			}
		}
		
		if(!mysql_query("UPDATE `Packages` SET `last_generate`=NOW() where package_id ='$pid'",$conn))
		{
		//return 0;
		//echo "DELETE FROM  WHERE package_id='$pid'";
		//echo "Delete Failed";
		};
		
		if(!mysql_query("UPDATE `Packages` SET `generation_count` = `generation_count` + 1 WHERE package_id ='$pid'",$conn))
		{
		//return 0;
		//echo "DELETE FROM  WHERE package_id='$pid'";
		//echo "Delete Failed";
		};
		
		return 1;
		
	
}


FUNCTION printTable($table)
{
	foreach ($table as $rows => $row)
	{


		echo "<table border='1'><tr>";
		foreach ($row as $col => $cell)
		{
			echo "<td>" . $cell . "</td>";
		}	
		echo "</tr></table>";
	}	

}

FUNCTION aLength($var)
{
	$counter = 0;
	foreach($var AS $value) {
		$counter++;
	}
	return $counter;
}

function mysql_insert_array($table, $data, $exclude = array(),$conn,$pid,$debug) {
	
    $fields = $values = array();

    if( !is_array($exclude) ) $exclude = array($exclude);

    foreach( array_keys($data) as $key ) {
        if( !in_array($key, $exclude) ) {
			if(is_int($key))
				continue;
            $fields[] = "`$key`";
            $values[] = "'" . mysql_real_escape_string($data[$key]) . "'";
        }
    }

    $fields = implode(",", $fields);
    $values = implode(",", $values);
	
	if($debug)
	{
		print_r($fields);
		print_r($values);
	}

    if( mysql_query("INSERT INTO `$table` ($fields) VALUES ($values)" ,$conn)) {
        return array( "mysql_error" => false,
                      "mysql_insert_id" => mysql_insert_id(),
                      "mysql_affected_rows" => mysql_affected_rows(),
                      "mysql_info" => mysql_info()
                    );
    } else {
        return array( "mysql_error" => mysql_error() );
    }

}

FUNCTION calc_all_packages($conn)
{
	$sql = "select package_id from Packages WHERE `active` = 1 and to_date >= NOW()";
		
		
	$result = mysql_query($sql,$conn);

	$rows = array();

	echo "Calc All Packages Started";
	
	while(($row = mysql_fetch_array($result))) {
		
		echo $row['package_id'];
		calc_price($row['package_id'],$conn,1);
		$rows[] = $row;
	}

	
	echo "Calc All Packages Done";
	return $rows;


}


FUNCTION delete_all_packages($conn)
{
	$sql = "select package_id from Packages";
	
	$result = mysql_query($sql,$conn);

	$rows = array();

	echo "Calc All Packages Started";
	
	while(($row = mysql_fetch_array($result))) {
		
		echo $row['package_id'];
		delete_package($conn,$row['package_id']);
	}
	
	echo "Delete All Packages Done";
	return $rows;
}

function delete_package($conn,$pid)
{
	$sql = "select place_detail_id from Package_Details_Places where package_id_f = '$pid'";
	$result = mysql_query($sql,$conn);
	$rows = array();
	
	while(($row = mysql_fetch_array($result))) {
		$rows[] = $row;
	}
	$places = $rows;
	
	foreach($places as $place)
	{
		$sql = "select hotel_detail_id from Package_Details_Hotels where place_detail_id_f = " . $place['place_detail_id'];
		$result = mysql_query($sql,$conn);
		$rows = array();
	
		while(($row = mysql_fetch_array($result))) {
			$rows[] = $row;
		}
		$hotels = $rows;
		
		foreach($hotels as $hotel)
		{
			$sql = "delete from Package_Details_Meals where hotel_detail_id_f = " . $hotel['hotel_detail_id'] ;
			if(!mysql_query($sql,$conn))
				echo "Failed to delete from meals where hotel_detail_id " . $hotel['hotel_detail_id'];
				
			$sql = "delete from Package_Details_Rooms where hotel_detail_id_f = " . $hotel['hotel_detail_id'] ;
			if(!mysql_query($sql,$conn))
				echo "Failed to delete from rooms where hotel_detail_id " . $hotel['hotel_detail_id'];
		}
		
		$sql = "delete from Package_Details_Hotels where place_detail_id_f = " . $place['place_detail_id'] ;
		if(!mysql_query($sql,$conn))
			echo "Failed to delete from hotels where place_detail_id " . $hotel['place_detail_id'];
			
		$sql = "delete from Package_Details_Transportation where place_detail_id_f = " . $place['place_detail_id'] ;
		if(!mysql_query($sql,$conn))
			echo "Failed to delete from Trans where place_detail_id " . $hotel['place_detail_id'];
		
		$sql = "delete from Package_Details_Tours where place_detail_id_f = " . $place['place_detail_id'] ;
		if(!mysql_query($sql,$conn))
			echo "Failed to delete from Tours where place_detail_id " . $hotel['place_detail_id'];
	}

	$sql = "delete from Packages where package_id =" . $pid;
	if(!mysql_query($sql,$conn))
			echo "Failed to delete from Packages where package_id " .$pid;
	
	$sql = "delete from Package_Details_Tickets where package_id_f =" . $pid;
	if(!mysql_query($sql,$conn))
			echo "Failed to delete from Tickets where package_id " .$pid;
			
	$sql = "delete from Package_Hotels_Comb where package_id_f =" . $pid;
	if(!mysql_query($sql,$conn))
			echo "Failed to delete from Comb where package_id " .$pid;
			
	$sql = "delete from Package_Prices where package_id =" . $pid;
	if(!mysql_query($sql,$conn))
			echo "Failed to delete from Prices where package_id " .$pid;
			
	$sql = "delete from Package_Details_Places where package_id_f =" . $pid;
	if(!mysql_query($sql,$conn))
			echo "Failed to delete from Places where package_id " .$pid;

	echo "Delete Package Done";
	return $rows;
}

function searchArray($array, $index, $search){
         $returnArray = array();
         foreach($array as $k=>$v){
               if($v[$index] == $search){   
                   true;
               }
         }
         return false;
}

FUNCTION archive_packages($conn)
{
	$sql = "select package_id from Packages WHERE `active` != 1 OR to_date < NOW()";
		
		
	$result = mysql_query($sql,$conn);

	$rows = array();

	echo "Archive Packages Started";
	
	while(($row = mysql_fetch_array($result))) {
		
		echo $row['package_id'];
		archive_one_package($row['package_id'],$conn,1);
		$rows[] = $row;
	}

	
	echo "Archive Packages Done";
	return $rows;


}
FUNCTION archive_one_package($pid,$conn,$debug)
{
	if($debug)
	{
		echo "Package ID = " . $pid;
	}
	
	mysql_query("DELETE FROM Package_Prices_Archive WHERE package_id='$pid'",$conn);
	
	$sql = "select * from Package_Prices WHERE package_id =" . $pid;
		
		
	$result = mysql_query($sql,$conn);

	$rows = array();

	echo "Archive Packages Started";
	
	while(($row = mysql_fetch_array($result))) {
		$rows[] = $row;
	}

	foreach($rows AS $row)
	{
		$execlude = array();
		$execlude[] = "hotel_id";
		$execlude[] = "place_id";
		$execlude[] = "hotel_detail_id";
		$execlude[] = "package_type";
		$execlude[] = "place_detail_id";
		$execlude[] = "ratingValue";
		

		$res = mysql_insert_array("Package_Prices_Archive", $row, $execlude,$conn,$pid,0);
	
	}
	
	if(checkCountOfPackage($pid,$conn,$debug))
	{
		delete_package($conn,$pid);
	}
	
	
	echo "Archive Package" . $pid. " Done";
	return $rows;


}
FUNCTION checkCountOfPackage($pid,$conn,$debug)
{
	$sql = "SELECT COUNT(*) AS `count` FROM `Package_Prices_Archive` WHERE package_id = " . $pid;
	$result = mysql_query($sql,$conn);
	while(($row = mysql_fetch_array($result))) {
		$countArchive = $row['count'];
	}
	
	$sql = "SELECT COUNT(*) AS `count` FROM `Package_Prices` WHERE package_id = " . $pid;
	$result = mysql_query($sql,$conn);
	while(($row = mysql_fetch_array($result))) {
		$count = $row['count'];
	}

	
	if($debug)
		{
			echo "Arrchive Count:" . $countArchive;
			echo "Count:" . $count;
		}
		
	if($countArchive > 0 && $countArchive==$count)
	{
		if($debug)
		{
			"Arrchive Success";
		}
		return true;
	}
	
	return false;

}


function delete_hotel($conn,$hid)
{
	//-----------------------------------------------------------------------------------------------------------------
	$sql = "SELECT `place_detail_id_f` FROM `Package_Details_Hotels` WHERE `hotel_id_f`= '$hid'";
	$result = mysql_query($sql,$conn);
	$rows = array();
	
	while(($row = mysql_fetch_array($result))) {
		$rows[] = $row['place_detail_id_f'];
	}
	$places = $rows;
	
	$pidList = getPackageIDsFromPlaceIDs($conn,$places);
	
	if(count($hotels)>0)
	{
		return "Could not delete hotel - used in packages " . getPackageNamesString($conn,$pidList);
	}
	//-----------------------------------------------------------------------------------------------------------------
	$sql = "SELECT DISTINCT  `room_type_id` FROM `Room_Type` WHERE `hotel_id_f`='$hid'" ;
	$result = mysql_query($sql,$conn);
	$rows = array();
	while(($row = mysql_fetch_array($result))) {
		$rows[] = $row;
	}
	$roomTypes = $rows;
	
	foreach($roomTypes as $roomType)
	{
		$sql = "delete from `Room_View` where `room_type_id_f` = '$roomType'" ;
		if(!mysql_query($sql,$conn))
			return "Failed to delete from Room_View";
	}
	//-----------------------------------------------------------------------------------------------------------------
	$sql = "delete from Hotels where hotel_id = '$hid'" ;
	if(!mysql_query($sql,$conn))
		return "Failed to delete from Hotels";
	//-----------------------------------------------------------------------------------------------------------------
	$sql = "delete from `Hotel_Prices` where `hotel_id_f` = '$hid'" ;
	if(!mysql_query($sql,$conn))
		return "Failed to delete from Hotels_Prices";
	//-----------------------------------------------------------------------------------------------------------------
	$sql = "delete from `Meals` where `hotel_id_f` = '$hid'" ;
	if(!mysql_query($sql,$conn))
		return "Failed to delete from Meals";
	//-----------------------------------------------------------------------------------------------------------------	
	$sql = "delete from `Room_Type` where `hotel_id_f` = '$hid'" ;
	if(!mysql_query($sql,$conn))
		return "Failed to delete from Room_Type";
	//-----------------------------------------------------------------------------------------------------------------	
	
	return "Delete Hotel Done";
}

function delete_general_room_view($conn,$grvid)
{
	//-----------------------------------------------------------------------------------------------------------------
	$sql = "SELECT `room_view_id` FROM `Room_View` WHERE `general_room_view_id_f`= '$grvid'";
	$result = mysql_query($sql,$conn);
	$rows = array();
	
	while(($row = mysql_fetch_array($result))) {
		$rows[] = $row;
	}
	$roomViews = $rows;
	
	if(count($roomViews)>0)
	{
		return "Could not delete Room View - used in hotels " ;
	}
	//-----------------------------------------------------------------------------------------------------------------
	$sql = "delete from General_Room_Views where general_room_view_id = '$grvid'" ;
	if(!mysql_query($sql,$conn))
		return "Failed to delete from General_Room_Views";
	//-----------------------------------------------------------------------------------------------------------------
	
	return "Delete General_Room_Views Done";
}

function delete_general_room_type($conn,$grtid)
{
	//-----------------------------------------------------------------------------------------------------------------
	$sql = "SELECT `hotel_id_f` FROM `Room_Type` WHERE `general_room_type_id_f`= '$grtid'";
	$result = mysql_query($sql,$conn);
	$rows = array();
	
	while(($row = mysql_fetch_array($result))) {
		$rows[] = $row['hotel_id_f'];
	}
	$hidList = $rows;
	
	if(count($hidList)>0)
	{
		return "Could not delete Room View - used in hotels " . getHotelNamesString($conn,$hidList);
	}
	//-----------------------------------------------------------------------------------------------------------------
	$sql = "delete from General_Room_Types where general_room_type_id = '$grtid'" ;
	if(!mysql_query($sql,$conn))
		return "Failed to delete from General_Room_Types";
	//-----------------------------------------------------------------------------------------------------------------
	
	return "Delete General_Room_Types Done";
}

function delete_general_meals($conn,$gmid)
{
	//-----------------------------------------------------------------------------------------------------------------
	$sql = "SELECT `hotel_id_f` FROM `Meals` WHERE `general_meal_id_f`= '$gmid'";
	$result = mysql_query($sql,$conn);
	$rows = array();
	
	while(($row = mysql_fetch_array($result))) {
		$rows[] = $row['hotel_id_f'];
	}
	$hidList = $rows;
	
	if(count($hidList)>0)
	{
		return "Could not delete Meals - used " . getHotelNamesString($conn,$hidList);
	}
	//-----------------------------------------------------------------------------------------------------------------
	$sql = "delete from General_Meals where general_meal_id = '$gmid'" ;
	if(!mysql_query($sql,$conn))
		return "Failed to delete from General_Meals";
	//-----------------------------------------------------------------------------------------------------------------
	
	return "Delete General_Meals Done";
}

function delete_tickets($conn,$tid)
{
	//-----------------------------------------------------------------------------------------------------------------
	$sql = "SELECT `package_id_f` FROM `Package_Details_Tickets` WHERE `ticket_id_f`= '$tid'";
	$result = mysql_query($sql,$conn);
	$rows = array();
	
	while(($row = mysql_fetch_array($result))) {
		$rows[] = $row['package_id_f'];
	}
	$pidList = $rows;
	
	if(count($tickets)>0)
	{
		return "Could not delete tickets - used in packages " . getPackageNamesString($conn,$pidList);
	}
	//-----------------------------------------------------------------------------------------------------------------
	$sql = "delete from Tickets where ticket_id = '$tid'" ;
	if(!mysql_query($sql,$conn))
		return "Failed to delete from Tickets";
	//-----------------------------------------------------------------------------------------------------------------
	
	return "Delete Tickets Done";
}

function getHotelNamesString($conn,$hidList)
{
	$outPut = "";
	foreach($hidList as $hid)
	{
		$sql = "SELECT `hotel_name` FROM `Hotels` WHERE `hotel_id`= '$hid'";
		$result = mysql_query($sql,$conn);
		$rows = array();
		
		while(($row = mysql_fetch_array($result))) {
			$rows[] = $row['hotel_name'];
		}
		$outPut = $outPut . $rows[0] . "," ;
	}
	
	return $outPut;
}

function getPackageNamesString($conn,$pidList)
{
	$outPut = "";
	foreach($pidList as $pid)
	{
		$sql = "SELECT `package_name` FROM `Packages` WHERE `package_id`= '$pid'";
		$result = mysql_query($sql,$conn);
		$rows = array();
		
		while(($row = mysql_fetch_array($result))) {
			$rows[] = $row['package_name'];
		}
		$outPut = $outPut . $rows[0] . "," ;
	}
	
	return $outPut;
}

function getPackageIDsFromPlaceIDs($conn,$placesList)
{
	foreach($placesList as $placeid)
	{
		$sql = "SELECT `package_id_f` FROM `Package_Details_Places` WHERE `place_detail_id`= '$placeid'";
		$result = mysql_query($sql,$conn);
		$rows = array();
		
		while(($row = mysql_fetch_array($result))) {
			$rows[] = $row['package_id_f'];
		}
	}
	
	return $rows;
}

?>