DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `EmailNOTExIstsTHENInsert`(IN `email_id` VARCHAR(100))
BEGIN 
	
    declare countusersId int(11);
    
     select count(*) into countusersId from users where email = email_id limit 0,1;
    
    if countusersId > 0 THEN
     select id from users where email = email_id limit 0,1;
    else 
        insert into users(email) VALUES (email_id);
       select LAST_INSERT_ID() as id;
    end if;
    
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `frontGetArrivalCities`(IN `trip_type` INT, IN `departure_city_id` INT, IN `other_1` VARCHAR(100), IN `other_2` VARCHAR(100), IN `other_3` VARCHAR(100), IN `other_4` VARCHAR(100), IN `other_5` VARCHAR(100))
BEGIN

    DECLARE SQL_QUERY  varchar(1000);
    
  
   set @SQL_QUERY = CONCAT('select SINVEN.id as shared_inventory_plan_Id,flights.id, flights.arrival_city_id , flights.arrival_airport_id , 
airport.id AS airport_id, airport.name AS airport_name,airport.airport_code AS airport_code, arrival_city.name AS arrival_city_name, 
 arrival_city.city_code AS arrival_city_code,SINVEN.share_plan as vendor_share_plan
 FROM flights AS flights 
INNER JOIN airports AS airport ON  flights.arrival_airport_id = airport.id 
INNER JOIN cities AS arrival_city ON  flights.arrival_city_id = arrival_city.id
INNER join share_inventories as SINVEN on SINVEN.share_by_user_id = flights.user_id
 INNER join planwise_seat_availabilities as PWSA on PWSA.flight_id = flights.id
 WHERE flights.status = 1 
 and PWSA.plan_no=SINVEN.share_plan 
 and SINVEN.share_to_user_id IN (select GROUP_CONCAT(id) from users where role_id=1) and  ADDTIME(DATE_SUB(PWSA.flight_date,INTERVAL flights.close_before_days DAY),flights.close_before_time) >=  now() 
 and  PWSA.available_seats > 0 
 AND flights.return_flight = ',trip_type,'
 AND flights.departure_city_id = ',departure_city_id,'
 and SINVEN.status = 1 
 and SINVEN.accepted_status=1
 and flights.user_id IN (',frontGetsharedUserID(),') 
 GROUP BY arrival_city_id, arrival_airport_id ORDER BY flights.id DESC');
  /*INNER join flight_pnrs as FPNR on FPNR.flight_id = flights.id*/
 /*and ',getSharedInventoryPlan('shared_inventory_plan_Id'),' > 0 */
 
		 insert into test(table_key,table_value) VALUES ('front_frontGetArrivalCities',@SQL_QUERY);
         
       PREPARE stmt1 FROM @SQL_QUERY ;
       
       EXECUTE stmt1;
        
        DEALLOCATE PREPARE stmt1;
    

END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `checkFareandTaxesShared`(IN `booking_user_id` INT, IN `user_id` INT)
BEGIN

    DECLARE SQL_QUERY  varchar(1000);
    
   set @SQL_QUERY = CONCAT('SELECT * from share_inventories where share_by_user_id=',user_id,' and share_to_user_id=',booking_user_id,' ');
                             insert into test(table_key,table_value) VALUES ('main_checkFareandTaxesShared',@SQL_QUERY);
                             
 insert into test(table_key,table_value) VALUES ('website_return_getSearchData',@SQL_QUERY);
     
       PREPARE stmt1 FROM @SQL_QUERY ;
       
       EXECUTE stmt1;
        
        DEALLOCATE PREPARE stmt1;
    

END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `companyAccountDetails`(IN `user_id` INT, IN `share_to_user_id` INT)
BEGIN

    DECLARE SQL_QUERY  varchar(1000);
   
   set @SQL_QUERY = CONCAT('select available_balance as credit_limit,
(SELECT company_name from users where id=',share_to_user_id,') as company_name,
(select amount from accounts where share_to_user_id=',share_to_user_id,' and share_by_user_id=',user_id,'  and account_type="balance" order by createdAt DESC limit 0,1) as last_debited_amount,
(select DATE_FORMAT(createdAt,"%d-%m-%Y") from accounts where share_to_user_id=',share_to_user_id,' and share_by_user_id=',user_id,'  and account_type="balance" order by createdAt DESC limit 0,1) as last_debited_date,   
(select amount from accounts where share_to_user_id=',share_to_user_id,' and share_by_user_id=',user_id,' and account_type="booking" order by createdAt DESC limit 0,1) as last_transaction_amount,
(select DATE_FORMAT(createdAt,"%d-%m-%Y") from accounts where share_to_user_id=',share_to_user_id,' and share_by_user_id=',user_id,' and account_type="booking" order by createdAt DESC limit 0,1) as last_transaction_date
from accounts where share_to_user_id=',share_to_user_id,' and share_by_user_id=',user_id,' order by createdAt DESC limit 0,1');
   
   

   
                             insert into test(table_key,table_value) VALUES ('main_companyAccountDetails',@SQL_QUERY);
                             
     
       PREPARE stmt1 FROM @SQL_QUERY ;
       
       EXECUTE stmt1;
        
        DEALLOCATE PREPARE stmt1;
    
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `frontGetDepartureCities`(IN `trip_type` INT, IN `other_1` VARCHAR(100), IN `other_2` VARCHAR(100), IN `other_3` VARCHAR(100), IN `other_4` VARCHAR(100), IN `other_5` VARCHAR(100))
BEGIN

    DECLARE SQL_QUERY  varchar(1000);
  
    set @SQL_QUERY = CONCAT('SELECT SINVEN.id as shared_inventory_plan_Id,flights.id, flights.departure_city_id , flights.departure_airport_id , 
airport.id AS airport_id, airport.name AS airport_name, airport.airport_code AS airport_code,departure_city.name AS departure_city_name, departure_city.city_code AS departure_city_code,SINVEN.share_plan as vendor_share_plan
 FROM flights AS flights 
 INNER JOIN airports AS airport ON  flights.departure_airport_id = airport.id 
INNER JOIN cities AS departure_city ON  flights.departure_city_id = departure_city.id 
INNER join share_inventories as SINVEN on SINVEN.share_by_user_id = flights.user_id 
 INNER join planwise_seat_availabilities as PWSA on PWSA.flight_id = flights.id
 WHERE flights.status = 1 
 and PWSA.plan_no=SINVEN.share_plan 
 and  SINVEN.share_to_user_id IN (select GROUP_CONCAT(id) from users where role_id=1)
and  ADDTIME(DATE_SUB(PWSA.flight_date,INTERVAL flights.close_before_days DAY),flights.close_before_time) >=  now() 
 and  PWSA.available_seats > 0 
 AND flights.return_flight = ',trip_type,'
 and SINVEN.status = 1 
 and SINVEN.accepted_status=1
 and flights.user_id IN (',frontGetsharedUserID(),') 
 GROUP BY departure_city_id, departure_airport_id ORDER BY flights.id DESC ');
  /*INNER join flight_pnrs as FPNR on FPNR.flight_id = flights.id*/
 /*and ',getSharedInventoryPlan('shared_inventory_plan_Id'),' > 0 */
 
		 insert into test(table_key,table_value) VALUES ('front_frontGetDepartureCities',@SQL_QUERY);
         
       PREPARE stmt1 FROM @SQL_QUERY ;
       
       EXECUTE stmt1;
        
        DEALLOCATE PREPARE stmt1;
    

END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `frontGetSearchData`(IN `trip_type` INT, IN `departure_city_id` INT, IN `departure_airport_id` INT, IN `arrival_city_id` INT, IN `arrival_airport_id` INT, IN `departure_date` DATE, IN `return_date` DATE, IN `other_1` VARCHAR(100), IN `other_2` VARCHAR(100), IN `other_3` VARCHAR(100), IN `other_4` VARCHAR(100), IN `other_5` VARCHAR(100))
BEGIN

    DECLARE SQL_QUERY  varchar(1000);
    
    	/*insert into test(table_key,table_value) VALUES ('conditions', CONCAT(user_logged_id,trip_type,departure_city_id,departure_airport_id,arrival_city_id,arrival_airport_id,departure_date));*/
     
     /*CALL getShareUserIDwithAdmin(user_logged_id); */
    
   IF trip_type = 0 THEN
   set @SQL_QUERY = CONCAT('SELECT (select distinct role_id from users where id=SINVEN.share_to_user_id) as share_to_user_role_id, FAS.add_margin,FAS.margin,FAS.client_discount,FAS.vendor_discount,USER.company_name, USER.role_id, FARETAX.base_fare, FARETAX.fee_taxes, SINVEN.credit_limit as inventory_credit_limit, SINVEN.share_price_breakup as inventory_price_breckup, SINVEN.share_plan,SINVEN.share_by_user_id,SINVEN.share_to_user_id,FGT.id as flight_id,FGT.user_id,(select option_value from options where user_id=FGT.user_id and option_key="special_info" limit 0,1) as special_info, FGT.re_flight_number,FGT.flight_number,FGT.name,FGT.airline_id, FGT.departure_city_id,FGT.departure_airport_id,FGT.dep_terminal_no_id, FGT.departure_time,FGT.arrival_city_id,FGT.arrival_airport_id,FGT.arrival_terminal_no_id,FGT.arrival_date,FGT.arrival_time,FGT.return_flight,FGT.checkin_baggages_adult,FGT.checkin_baggages_infant,FGT.cabin_baggages_adult,FGT.cabin_baggages_children,FGT.checkin_baggages_children,FGT.cabin_baggages_children,FGT.cabin_baggages_infant,departure_city.country_id as departure_country_id,departure_city.name as departure_city_name,arrival_city.name as arrival_city_name, arrival_city.country_id as arrival_country_id,departure_airports.name as departure_airport_name,departure_airports.airport_code as departure_airport_code,arrival_airports.name as arrival_airport_name ,arrival_airports.airport_code as arrival_airport_code ,airlines.name as airlines_name,airlines.logo as airlines_logo,airlines.airline_no,airlines.airline_code,FPNR.date as ticket_date ,FPNR.return_dep_date as return_dep_date,FPNR.pnr_number as pnr_number,FPNR.infant_price as infant_price,FPNR.block_seat as block_seat,PSA.available_seats as total_available_seats FROM flights as FGT
                            INNER join planwise_seat_availabilities as PSA on PSA.flight_id = FGT.id 
                            INNER join flight_pnrs as FPNR on FPNR.flight_id = FGT.id 
                            INNER join flight_fare_taxes as FARETAX on FARETAX.flight_id = FGT.id 
                            INNER join users as USER on USER.id = FGT.user_id 
                            INNER join flght_api_settings as FAS on FAS.user_id = 1 
                            INNER join share_inventories as SINVEN on SINVEN.share_by_user_id = FGT.user_id 
                            INNER join cities as  departure_city on departure_city.id = FGT.departure_city_id
                            INNER join cities as  arrival_city on arrival_city.id = FGT.arrival_city_id 
                            INNER join airports as  departure_airports on departure_airports.id = FGT.departure_airport_id 
                            INNER join airports as  arrival_airports on arrival_airports.id = FGT.arrival_airport_id 
                            INNER join airlines as  airlines on airlines.id = FGT.airline_id 
                            WHERE FGT.status = 1 
                            and now() <=  ADDTIME(DATE_SUB(',"'", departure_date,"'",',INTERVAL FGT.close_before_days DAY),FGT.close_before_time) 
                            and return_flight=',trip_type,' 
                            and departure_city_id = ',departure_city_id,'
                            and departure_airport_id=',departure_airport_id,'
                            and  arrival_city_id=',arrival_city_id,'
                            and arrival_airport_id=', arrival_airport_id,'
                            and FGT.user_id IN (',frontGetsharedUserID(),') 
                            and SINVEN.id IN  (',frontGetsharedInventoryID(),')  
                            and PSA.plan_no = share_plan
							and PSA.flight_date = ',"'", departure_date,"'",' 
                            and PSA.available_seats > 0
                            and FPNR.available_seat > 0 
                            and FPNR.date =',"'", departure_date,"'",' 
                            and SINVEN.status = 1
                            and SINVEN.accepted_status=1 
                            and FARETAX.date =',"'", departure_date,"'",' 
                            group by FPNR.flight_id ORDER by USER.role_id desc');
                             insert into test(table_key,table_value) VALUES ('main_one_getSearchData',@SQL_QUERY);
   ELSE 
          set @SQL_QUERY = CONCAT('SELECT  (select distinct role_id from users where id=SINVEN.share_to_user_id) as share_to_user_role_id,USER.company_name, USER.role_id,FAS.add_margin,FAS.margin,FAS.client_discount,FAS.vendor_discount,FARETAX.base_fare,FARETAX.fee_taxes,SINVEN.credit_limit as inventory_credit_limit,SINVEN.share_price_breakup as inventory_price_breckup,SINVEN.share_plan,SINVEN.share_by_user_id,SINVEN.share_to_user_id,(select option_value from options where user_id=FGT.user_id and option_key="special_info" limit 0,1) as special_info,FGT.re_flight_number,FGT.id as flight_id,FGT.user_id,FGT.flight_number,FGT.ret_dep_ter_no_id,FGT.ret_arv_ter_no_id,FGT.name,FGT.airline_id,FGT.departure_city_id,FGT.departure_airport_id,FGT.dep_terminal_no_id,FGT.departure_time,FGT.arrival_city_id,FGT.arrival_airport_id,FGT.arrival_terminal_no_id,FGT.arrival_date,FGT.arrival_time,FGT.return_dep_time as return_dep_time,FGT.ret_arv_time as ret_arv_time,FGT.return_flight,FGT.checkin_baggages_adult,FGT.checkin_baggages_infant,FGT.cabin_baggages_adult,FGT.checkin_baggages_children,FGT.cabin_baggages_children,FGT.cabin_baggages_children,FGT.cabin_baggages_infant,departure_city.country_id as departure_country_id,departure_city.name as departure_city_name,arrival_city.name as arrival_city_name, arrival_city.country_id as arrival_country_id,departure_airports.name as departure_airport_name,departure_airports.airport_code as departure_airport_code,arrival_airports.name as arrival_airport_name,arrival_airports.airport_code as arrival_airport_code,airlines.name as airlines_name,airlines.logo as airlines_logo,airlines.airline_no,airlines.airline_code ,PSA.available_seats as total_available_seats,FPNR.date as ticket_date ,FPNR.return_dep_date as return_dep_date ,FPNR.pnr_number as pnr_number,FPNR.infant_price as infant_price,FPNR.block_seat as block_seat,return_departure_city.name as return_departure_city_name,return_arrival_city.name as return_arrival_city_name,return_departure_airports.name as return_departure_airport_name,return_departure_airports.airport_code as return_departure_airport_code,return_arrival_airports.name as return_arrival_airport_name,return_arrival_airports.airport_code as return_arrival_airport_code FROM flights as FGT 
INNER join planwise_seat_availabilities as PSA on PSA.flight_id = FGT.id 
INNER join cities as return_departure_city on return_departure_city.id = FGT.re_dep_city 
INNER join cities as  return_arrival_city on return_arrival_city.id = FGT.re_arr_city 
INNER join airports as  return_departure_airports on return_departure_airports.id = FGT.re_dep_airport_id
INNER join airports as  return_arrival_airports on return_arrival_airports.id = FGT.re_arr_airport_id 
INNER join flight_pnrs as FPNR on FPNR.flight_id = FGT.id 
INNER join users as USER on USER.id = FGT.user_id
INNER join flght_api_settings as FAS on FAS.user_id =  1
INNER join flight_fare_taxes as FARETAX on FARETAX.flight_id = FGT.id 
INNER join share_inventories as SINVEN on SINVEN.share_by_user_id = FGT.user_id
INNER join cities as  departure_city on departure_city.id = FGT.departure_city_id 
INNER join cities as  arrival_city on arrival_city.id = FGT.arrival_city_id
INNER join airports as  departure_airports on departure_airports.id = FGT.departure_airport_id
INNER join airports as  arrival_airports on arrival_airports.id = FGT.arrival_airport_id
INNER join airlines as  airlines on airlines.id = FGT.airline_id 
                            WHERE FGT.status = 1 
                            and now() <=  ADDTIME(DATE_SUB(',"'", departure_date,"'",',INTERVAL FGT.close_before_days DAY),FGT.close_before_time) 
                            and return_flight=',trip_type,' 
                            and departure_city_id = ',departure_city_id,'
                            and departure_airport_id=',departure_airport_id,'
                            and  arrival_city_id=',arrival_city_id,'
                            and arrival_airport_id=', arrival_airport_id,'
                         	and FGT.user_id IN (',frontGetsharedUserID(),') 
                            and SINVEN.id IN  (',frontGetsharedInventoryID(),')  
                            and PSA.plan_no = share_plan
							and PSA.flight_date = ',"'", departure_date,"'",' 
 and PSA.available_seats > 0
and FPNR.available_seat > 0 
and FPNR.date =',"'", departure_date,"'",'
 and FPNR.return_dep_date=',"'",return_date,"'",'
 and SINVEN.status = 1
and SINVEN.accepted_status=1 
and FARETAX.date =',"'", departure_date,"'",' 
group by FPNR.flight_id ORDER by USER.role_id desc');
 insert into test(table_key,table_value) VALUES ('main_return_getSearchData',@SQL_QUERY);
                         
   END IF;
	
     
       PREPARE stmt1 FROM @SQL_QUERY ;
       
       EXECUTE stmt1;
        
        DEALLOCATE PREPARE stmt1;
    

END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `frontGetSearchDetailsData`(IN `trip_type` INT, IN `flight_id` INT, IN `departure_date` DATE, IN `return_date` DATE, IN `other_1` VARCHAR(100), IN `other_2` VARCHAR(100), IN `other_3` VARCHAR(100), IN `other_4` VARCHAR(100), IN `other_5` VARCHAR(100))
BEGIN

    DECLARE SQL_QUERY  varchar(1000);
     
    
   IF trip_type = 0 THEN
    set @SQL_QUERY = CONCAT('SELECT (select distinct role_id from users where id=SINVEN.share_to_user_id) as share_to_user_role_id,FAS.add_margin,FAS.margin,FAS.client_discount,FAS.vendor_discount,
FAS.payment_gatway_charges,FAS.amount,FAS.percentage, USER.company_name,USER.role_id,FARETAX.base_fare,FARETAX.fee_taxes,SINVEN.credit_limit as inventory_credit_limit,
SINVEN.share_price_breakup as inventory_price_breckup,SINVEN.share_plan,SINVEN.share_by_user_id,SINVEN.share_to_user_id,FGT.id as flight_id,FGT.user_id,
(select option_value from options where user_id=FGT.user_id and option_key="special_info" limit 0,1) as special_info,FGT.re_flight_number,FGT.flight_number,FGT.name,FGT.airline_id,
FGT.departure_city_id,FGT.departure_airport_id,
FGT.dep_terminal_no_id,FGT.departure_time,FGT.arrival_city_id,FGT.arrival_airport_id,FGT.arrival_terminal_no_id,FGT.arrival_date,FGT.arrival_time,FGT.return_flight,FGT.checkin_baggages_adult,
FGT.checkin_baggages_infant,FGT.cabin_baggages_adult,FGT.checkin_baggages_children,FGT.cabin_baggages_children,FGT.cabin_baggages_children,FGT.cabin_baggages_infant,departure_city.country_id as departure_country_id,departure_city.name as departure_city_name,arrival_city.name as arrival_city_name, arrival_city.country_id as arrival_country_id,departure_airports.name as departure_airport_name,departure_airports.airport_code as departure_airport_code,arrival_airports.name as arrival_airport_name, arrival_airports.airport_code as arrival_airport_code, airlines.name as airlines_name,airlines.logo as airlines_logo,airlines.airline_no,airlines.airline_code,FPNR.date as ticket_date ,FPNR.return_dep_date as return_dep_date,FPNR.pnr_number as pnr_number,FPNR.infant_price as infant_price,FPNR.block_seat as block_seat,PSA.available_seats as total_available_seats FROM flights as FGT 
INNER join planwise_seat_availabilities as PSA on PSA.flight_id = FGT.id
INNER join flight_pnrs as FPNR on FPNR.flight_id = FGT.id 
INNER join users as USER on USER.id = FGT.user_id 
INNER join flght_api_settings as FAS on FAS.user_id =1
INNER join flight_fare_taxes as FARETAX on FARETAX.flight_id = FGT.id 
INNER join share_inventories as SINVEN on SINVEN.share_by_user_id = FGT.user_id
INNER join cities as  departure_city on departure_city.id = FGT.departure_city_id 
INNER join cities as  arrival_city on arrival_city.id = FGT.arrival_city_id
INNER join airports as  departure_airports on departure_airports.id = FGT.departure_airport_id 
INNER join airports as  arrival_airports on arrival_airports.id = FGT.arrival_airport_id
INNER join airlines as  airlines on airlines.id = FGT.airline_id 
WHERE FGT.status = 1
and return_flight=',trip_type,'
and FGT.id=',flight_id,'
and FGT.user_id IN (',frontGetsharedUserID(),') 
and SINVEN.id IN  (',frontGetsharedInventoryID(),') 
and SINVEN.status = 1
and SINVEN.accepted_status=1 
and FPNR.available_seat > 0
and PSA.plan_no = share_plan 
and PSA.flight_date = ',"'", departure_date,"'",'
and PSA.available_seats > 0 
and FPNR.date =',"'", departure_date,"'",'
and FARETAX.date =',"'", departure_date,"'",' group by FGT.id');
 insert into test(table_key,table_value) VALUES ('front_oneway_frontGetSearchDetailsData',@SQL_QUERY);
   ELSE 
            set @SQL_QUERY = CONCAT('SELECT  (select distinct role_id from users where id=SINVEN.share_to_user_id) as share_to_user_role_id,USER.company_name, USER.role_id,FAS.add_margin,FAS.margin,FAS.client_discount,FAS.vendor_discount,FARETAX.base_fare,FARETAX.fee_taxes,SINVEN.credit_limit as inventory_credit_limit,SINVEN.share_price_breakup as inventory_price_breckup,SINVEN.share_plan,SINVEN.share_by_user_id,SINVEN.share_to_user_id,(select option_value from options where user_id=FGT.user_id and option_key="special_info" limit 0,1) as special_info,FGT.re_flight_number,FGT.id as flight_id,FGT.user_id,FGT.flight_number,FGT.ret_dep_ter_no_id,FGT.ret_arv_ter_no_id,FGT.name,FGT.airline_id,FGT.departure_city_id,FGT.departure_airport_id,FGT.dep_terminal_no_id,FGT.departure_time,FGT.arrival_city_id,FGT.arrival_airport_id,FGT.arrival_terminal_no_id,FGT.arrival_date,FGT.arrival_time,FGT.return_dep_time as return_dep_time,FGT.ret_arv_time as ret_arv_time,FGT.return_flight,FGT.checkin_baggages_adult,FGT.checkin_baggages_infant,FGT.cabin_baggages_adult,FGT.checkin_baggages_children,FGT.cabin_baggages_children,FGT.cabin_baggages_children,FGT.cabin_baggages_infant,departure_city.country_id as departure_country_id,departure_city.name as departure_city_name,arrival_city.name as arrival_city_name, arrival_city.country_id as arrival_country_id,departure_airports.name as departure_airport_name,departure_airports.airport_code as departure_airport_code,arrival_airports.name as arrival_airport_name,arrival_airports.airport_code as arrival_airport_code,airlines.name as airlines_name,airlines.logo as airlines_logo,airlines.airline_no,airlines.airline_code ,PSA.available_seats as total_available_seats,FPNR.date as ticket_date ,FPNR.return_dep_date as return_dep_date ,FPNR.pnr_number as pnr_number,FPNR.infant_price as infant_price,FPNR.block_seat as block_seat,return_departure_city.name as return_departure_city_name,return_arrival_city.name as return_arrival_city_name,return_departure_airports.name as return_departure_airport_name,return_departure_airports.airport_code as return_departure_airport_code,return_arrival_airports.name as return_arrival_airport_name,return_arrival_airports.airport_code as return_arrival_airport_code FROM flights as FGT 
INNER join planwise_seat_availabilities as PSA on PSA.flight_id = FGT.id 
INNER join cities as return_departure_city on return_departure_city.id = FGT.re_dep_city 
INNER join cities as  return_arrival_city on return_arrival_city.id = FGT.re_arr_city 
INNER join airports as  return_departure_airports on return_departure_airports.id = FGT.re_dep_airport_id
INNER join airports as  return_arrival_airports on return_arrival_airports.id = FGT.re_arr_airport_id 
INNER join flight_pnrs as FPNR on FPNR.flight_id = FGT.id 
INNER join users as USER on USER.id = FGT.user_id
INNER join flght_api_settings as FAS on FAS.user_id = 1
INNER join flight_fare_taxes as FARETAX on FARETAX.flight_id = FGT.id 
INNER join share_inventories as SINVEN on SINVEN.share_by_user_id = FGT.user_id
INNER join cities as  departure_city on departure_city.id = FGT.departure_city_id 
INNER join cities as  arrival_city on arrival_city.id = FGT.arrival_city_id
INNER join airports as  departure_airports on departure_airports.id = FGT.departure_airport_id
INNER join airports as  arrival_airports on arrival_airports.id = FGT.arrival_airport_id
INNER join airlines as  airlines on airlines.id = FGT.airline_id 
WHERE FGT.status = 1
and return_flight=',trip_type,'
and FGT.id=',flight_id,'
and FGT.user_id IN (',frontGetsharedUserID(),') 
and SINVEN.id IN  (',frontGetsharedInventoryID(),') 
and SINVEN.status = 1
and SINVEN.accepted_status=1 
and FPNR.available_seat > 0
and PSA.plan_no = share_plan 
and PSA.flight_date = ',"'", departure_date,"'",'
and PSA.available_seats > 0 
and FPNR.date =',"'", departure_date,"'",'
and FPNR.return_dep_date =',"'",return_date,"'",' 
and FARETAX.date =',"'", departure_date,"'",' group by FGT.id');
 insert into test(table_key,table_value) VALUES ('front_return_frontGetSearchDetailsData',@SQL_QUERY);
                         
   END IF;
	
       PREPARE stmt1 FROM @SQL_QUERY ;
       
       EXECUTE stmt1;
        
        DEALLOCATE PREPARE stmt1;
    

END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `frontGetTicketAvailableDate`(IN `trip_type` INT, IN `departure_city_id` INT, IN `arrival_city_id` INT, IN `other_1` VARCHAR(100), IN `other_2` VARCHAR(100), IN `other_3` VARCHAR(100), IN `other_4` VARCHAR(100), IN `other_5` VARCHAR(100))
BEGIN

    DECLARE SQL_QUERY  varchar(1000);
  
   set @SQL_QUERY = CONCAT('select  distinct PWSA.flight_date from planwise_seat_availabilities as PWSA 
INNER join flights AS flights on PWSA.flight_id = flights.id
INNER join share_inventories as SINVEN on SINVEN.share_by_user_id = flights.user_id
where flights.status = 1  
and flights.departure_city_id = ',departure_city_id,' 
and flights.arrival_city_id = ',arrival_city_id,'
and ADDTIME(DATE_SUB(PWSA.flight_date,INTERVAL flights.close_before_days DAY),flights.close_before_time) >=  now()
and PWSA.plan_no=SINVEN.share_plan
and PWSA.available_seats > 0 
AND flights.return_flight = ',trip_type,'
and SINVEN.status = 1
and SINVEN.accepted_status =1 
and flights.user_id IN (',frontGetsharedUserID(),') ORDER BY PWSA.flight_date ASC');
 
		 insert into test(table_key,table_value) VALUES ('front_frontGetTicketAvailableDate',@SQL_QUERY);
         
       PREPARE stmt1 FROM @SQL_QUERY ;
       
       EXECUTE stmt1;
        
        DEALLOCATE PREPARE stmt1;
    

END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `frontGetTicketReturnAvailableDate`(IN `departure_city_id` INT, IN `arrival_city_id` INT, IN `other_1` VARCHAR(100), IN `other_2` VARCHAR(100), IN `other_3` VARCHAR(100), IN `other_4` VARCHAR(100), IN `other_5` VARCHAR(100))
BEGIN

    DECLARE SQL_QUERY  varchar(1000);
  
   set @SQL_QUERY = CONCAT('select flights.trip_duration as return_in_days from flights as flights
where flights.status = 1  
and flights.departure_city_id = ',departure_city_id,' 
and flights.arrival_city_id = ',arrival_city_id,'
AND flights.return_flight = 1
and flights.status = 1 group by flights.trip_duration order by trip_duration ASC');

		 insert into test(table_key,table_value) VALUES ('main_frontGetTicketReturnAvailableDate',@SQL_QUERY);
         
       PREPARE stmt1 FROM @SQL_QUERY ;
       
       EXECUTE stmt1;
        
        DEALLOCATE PREPARE stmt1;
    

END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `frontGetsharedInventoryID`() RETURNS varchar(1000) CHARSET utf8mb4
    DETERMINISTIC
begin 
	declare user_ID varchar(1000);

                 select GROUP_CONCAT(id) as id  from (select SI.id  FROM  share_inventories SI 
                inner join users USER on USER.id = SI.share_to_user_id
                where SI.accepted_status=1 and SI.status=1 and share_plan !=0 and USER.role_id=1) aS shared_inventory_id into user_ID;
        return user_ID;
        
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `frontGetsharedUserID`() RETURNS varchar(1000) CHARSET utf8mb4
    DETERMINISTIC
begin 
	declare user_ID varchar(1000);
    
  select GROUP_CONCAT(share_by_user_id) as share_by_user_id  from (SELECT  GROUP_CONCAT(share_by_user_id) as share_by_user_id FROM share_inventories SI inner join users US on US.id = SI.share_to_user_id  where SI.status=1 and  SI.accepted_status=1 and share_plan !=0 and US.role_id=1) as share_by_user_id into user_ID;          
     return user_ID;
        
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `getArrivalCities`(IN `user_logged_id` INT, IN `trip_type` INT, IN `departure_city_id` INT, IN `other_1` VARCHAR(100), IN `other_2` VARCHAR(100), IN `other_3` VARCHAR(100), IN `other_4` VARCHAR(100), IN `other_5` VARCHAR(100))
BEGIN

    DECLARE SQL_QUERY  varchar(1000);
  
   set @SQL_QUERY = CONCAT('select SINVEN.id as shared_inventory_plan_Id,flights.id, flights.arrival_city_id , flights.arrival_airport_id , 
airport.id AS airport_id, airport.name AS airport_name,airport.airport_code AS airport_code, arrival_city.name AS arrival_city_name, 
 arrival_city.city_code AS arrival_city_code,SINVEN.share_plan as vendor_share_plan
 FROM flights AS flights 
INNER JOIN airports AS airport ON  flights.arrival_airport_id = airport.id 
INNER JOIN cities AS arrival_city ON  flights.arrival_city_id = arrival_city.id
INNER join share_inventories as SINVEN on SINVEN.share_by_user_id = flights.user_id
 INNER join planwise_seat_availabilities as PWSA on PWSA.flight_id = flights.id
 WHERE flights.status = 1 
 and PWSA.plan_no=SINVEN.share_plan 
 and (SINVEN.share_to_user_id=',user_logged_id,'  OR SINVEN.share_to_user_id IN (select GROUP_CONCAT(id) from users where role_id=1)) and  ADDTIME(DATE_SUB(PWSA.flight_date,INTERVAL flights.close_before_days DAY),flights.close_before_time) >=  now() 
 /*and SINVEN.id IN  (2,22) */
 and  PWSA.available_seats > 0 
 AND flights.return_flight = ',trip_type,'
 AND flights.departure_city_id = ',departure_city_id,'
 and SINVEN.status = 1 
 and SINVEN.accepted_status=1
 and flights.user_id IN (',getsharedUserID(user_logged_id),') 
 GROUP BY arrival_city_id, arrival_airport_id ORDER BY flights.id DESC');
  /*INNER join flight_pnrs as FPNR on FPNR.flight_id = flights.id*/
 /*and ',getSharedInventoryPlan('shared_inventory_plan_Id'),' > 0 */
 
		 insert into test(table_key,table_value) VALUES ('main_getArrivalCities',@SQL_QUERY);
         
       PREPARE stmt1 FROM @SQL_QUERY ;
       
       EXECUTE stmt1;
        
        DEALLOCATE PREPARE stmt1;
    

END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `getDepartureCities`(IN `user_logged_id` INT, IN `trip_type` INT, IN `other_1` VARCHAR(100), IN `other_2` VARCHAR(100), IN `other_3` VARCHAR(100), IN `other_4` VARCHAR(100), IN `other_5` VARCHAR(100))
BEGIN

    DECLARE SQL_QUERY  varchar(1000);
  
    set @SQL_QUERY = CONCAT('SELECT SINVEN.id as shared_inventory_plan_Id,flights.id, flights.departure_city_id , flights.departure_airport_id , 
airport.id AS airport_id, airport.name AS airport_name, airport.airport_code AS airport_code,departure_city.name AS departure_city_name, departure_city.city_code AS departure_city_code,SINVEN.share_plan as vendor_share_plan
 FROM flights AS flights 
 INNER JOIN airports AS airport ON  flights.departure_airport_id = airport.id 
INNER JOIN cities AS departure_city ON  flights.departure_city_id = departure_city.id 
INNER join share_inventories as SINVEN on SINVEN.share_by_user_id = flights.user_id 
 INNER join planwise_seat_availabilities as PWSA on PWSA.flight_id = flights.id
 WHERE flights.status = 1 
 and PWSA.plan_no=SINVEN.share_plan 
 and (SINVEN.share_to_user_id=',user_logged_id,'  OR SINVEN.share_to_user_id IN (select GROUP_CONCAT(id) from users where role_id=1)) and  ADDTIME(DATE_SUB(PWSA.flight_date,INTERVAL flights.close_before_days DAY),flights.close_before_time) >=  now() 
 /*and SINVEN.id IN  (2,22) */
 and  PWSA.available_seats > 0 
 AND flights.return_flight = ',trip_type,'
 and SINVEN.status = 1 
 and SINVEN.accepted_status=1
 and flights.user_id IN (',getsharedUserID(user_logged_id),') 
 GROUP BY departure_city_id, departure_airport_id ORDER BY flights.id DESC ');
  /*INNER join flight_pnrs as FPNR on FPNR.flight_id = flights.id*/
 /*and ',getSharedInventoryPlan('shared_inventory_plan_Id'),' > 0 */
 
		 insert into test(table_key,table_value) VALUES ('main_getDepartureCities',@SQL_QUERY);
         
       PREPARE stmt1 FROM @SQL_QUERY ;
       
       EXECUTE stmt1;
        
        DEALLOCATE PREPARE stmt1;
    

END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `getSearchData`(IN `user_logged_id` INT, IN `trip_type` INT, IN `departure_city_id` INT, IN `departure_airport_id` INT, IN `arrival_city_id` INT, IN `arrival_airport_id` INT, IN `departure_date` DATE, IN `return_date` DATE, IN `other_1` VARCHAR(100), IN `other_2` VARCHAR(100), IN `other_3` VARCHAR(100), IN `other_4` VARCHAR(100), IN `other_5` VARCHAR(100))
BEGIN

    DECLARE SQL_QUERY  varchar(1000);
    
    	/*insert into test(table_key,table_value) VALUES ('conditions', CONCAT(user_logged_id,trip_type,departure_city_id,departure_airport_id,arrival_city_id,arrival_airport_id,departure_date));*/
     
     /*CALL getShareUserIDwithAdmin(user_logged_id); */
    
   IF trip_type = 0 THEN
   set @SQL_QUERY = CONCAT('SELECT (select distinct role_id from users where id=SINVEN.share_to_user_id) as share_to_user_role_id, FAS.add_margin,FAS.margin,FAS.client_discount,FAS.vendor_discount,USER.company_name, USER.role_id, FARETAX.base_fare, FARETAX.fee_taxes, SINVEN.credit_limit as inventory_credit_limit, SINVEN.share_price_breakup as inventory_price_breckup, SINVEN.share_plan,SINVEN.share_by_user_id,SINVEN.share_to_user_id,FGT.id as flight_id,FGT.user_id,(select option_value from options where user_id=FGT.user_id and option_key="special_info" limit 0,1) as special_info, FGT.re_flight_number,FGT.flight_number,FGT.name,FGT.airline_id, FGT.departure_city_id,FGT.departure_airport_id,FGT.dep_terminal_no_id, FGT.departure_time,FGT.arrival_city_id,FGT.arrival_airport_id,FGT.arrival_terminal_no_id,FGT.arrival_date,FGT.arrival_time,FGT.return_flight,FGT.checkin_baggages_adult,FGT.checkin_baggages_infant,FGT.cabin_baggages_adult,FGT.cabin_baggages_children,FGT.checkin_baggages_children,FGT.cabin_baggages_children,FGT.cabin_baggages_infant,departure_city.country_id as departure_country_id,departure_city.name as departure_city_name,arrival_city.name as arrival_city_name, arrival_city.country_id as arrival_country_id,departure_airports.name as departure_airport_name,departure_airports.airport_code as departure_airport_code,arrival_airports.name as arrival_airport_name ,arrival_airports.airport_code as arrival_airport_code ,airlines.name as airlines_name,airlines.logo as airlines_logo,airlines.airline_no,airlines.airline_code,FPNR.date as ticket_date ,FPNR.return_dep_date as return_dep_date,FPNR.pnr_number as pnr_number,FPNR.infant_price as infant_price,FPNR.block_seat as block_seat,PSA.available_seats as total_available_seats FROM flights as FGT
                            INNER join planwise_seat_availabilities as PSA on PSA.flight_id = FGT.id 
                            INNER join flight_pnrs as FPNR on FPNR.flight_id = FGT.id 
                            INNER join flight_fare_taxes as FARETAX on FARETAX.flight_id = FGT.id 
                            INNER join users as USER on USER.id = FGT.user_id 
                            INNER join flght_api_settings as FAS on FAS.user_id = 1 
                            INNER join share_inventories as SINVEN on SINVEN.share_by_user_id = FGT.user_id 
                            INNER join cities as  departure_city on departure_city.id = FGT.departure_city_id
                            INNER join cities as  arrival_city on arrival_city.id = FGT.arrival_city_id 
                            INNER join airports as  departure_airports on departure_airports.id = FGT.departure_airport_id 
                            INNER join airports as  arrival_airports on arrival_airports.id = FGT.arrival_airport_id 
                            INNER join airlines as  airlines on airlines.id = FGT.airline_id 
                            WHERE FGT.status = 1 
                            and now() <=  ADDTIME(DATE_SUB(',"'", departure_date,"'",',INTERVAL FGT.close_before_days DAY),FGT.close_before_time) 
                            and return_flight=',trip_type,' 
                            and departure_city_id = ',departure_city_id,'
                            and departure_airport_id=',departure_airport_id,'
                            and  arrival_city_id=',arrival_city_id,'
                            and arrival_airport_id=', arrival_airport_id,'
                            and FGT.user_id IN (',getsharedUSerID(user_logged_id),') 
                            and SINVEN.id IN  (',getsharedInventoryID(user_logged_id),')  
                            and PSA.plan_no = share_plan
							and PSA.flight_date = ',"'", departure_date,"'",' 
                            and PSA.available_seats > 0
                            and FPNR.available_seat > 0 
                            and FPNR.date =',"'", departure_date,"'",' 
                            and SINVEN.status = 1
                            and SINVEN.accepted_status=1 
                            and FARETAX.date =',"'", departure_date,"'",' 
                            group by FPNR.flight_id ORDER by USER.role_id desc');
                             insert into test(table_key,table_value) VALUES ('main_oneway_getSearchData',@SQL_QUERY);
   ELSE 
          set @SQL_QUERY = CONCAT('SELECT  (select distinct role_id from users where id=SINVEN.share_to_user_id) as share_to_user_role_id,USER.company_name, USER.role_id,FAS.add_margin,FAS.margin,FAS.client_discount,FAS.vendor_discount,FARETAX.base_fare,FARETAX.fee_taxes,SINVEN.credit_limit as inventory_credit_limit,SINVEN.share_price_breakup as inventory_price_breckup,SINVEN.share_plan,SINVEN.share_by_user_id,SINVEN.share_to_user_id,(select option_value from options where user_id=FGT.user_id and option_key="special_info" limit 0,1) as special_info,FGT.re_flight_number,FGT.id as flight_id,FGT.user_id,FGT.flight_number,FGT.ret_dep_ter_no_id,FGT.ret_arv_ter_no_id,FGT.name,FGT.airline_id,FGT.departure_city_id,FGT.departure_airport_id,FGT.dep_terminal_no_id,FGT.departure_time,FGT.arrival_city_id,FGT.arrival_airport_id,FGT.arrival_terminal_no_id,FGT.arrival_date,FGT.arrival_time,FGT.return_dep_time as return_dep_time,FGT.ret_arv_time as ret_arv_time,FGT.return_flight,FGT.checkin_baggages_adult,FGT.checkin_baggages_infant,FGT.cabin_baggages_adult,FGT.checkin_baggages_children,FGT.cabin_baggages_children,FGT.cabin_baggages_children,FGT.cabin_baggages_infant,departure_city.country_id as departure_country_id,departure_city.name as departure_city_name,arrival_city.name as arrival_city_name, arrival_city.country_id as arrival_country_id,departure_airports.name as departure_airport_name,departure_airports.airport_code as departure_airport_code,arrival_airports.name as arrival_airport_name,arrival_airports.airport_code as arrival_airport_code,airlines.name as airlines_name,airlines.logo as airlines_logo,airlines.airline_no,airlines.airline_code ,PSA.available_seats as total_available_seats,FPNR.date as ticket_date ,FPNR.return_dep_date as return_dep_date ,FPNR.pnr_number as pnr_number,FPNR.infant_price as infant_price,FPNR.block_seat as block_seat,return_departure_city.name as return_departure_city_name,return_arrival_city.name as return_arrival_city_name,return_departure_airports.name as return_departure_airport_name,return_departure_airports.airport_code as return_departure_airport_code,return_arrival_airports.name as return_arrival_airport_name,return_arrival_airports.airport_code as return_arrival_airport_code FROM flights as FGT 
INNER join planwise_seat_availabilities as PSA on PSA.flight_id = FGT.id 
INNER join cities as return_departure_city on return_departure_city.id = FGT.re_dep_city 
INNER join cities as  return_arrival_city on return_arrival_city.id = FGT.re_arr_city 
INNER join airports as  return_departure_airports on return_departure_airports.id = FGT.re_dep_airport_id
INNER join airports as  return_arrival_airports on return_arrival_airports.id = FGT.re_arr_airport_id 
INNER join flight_pnrs as FPNR on FPNR.flight_id = FGT.id 
INNER join users as USER on USER.id = FGT.user_id
INNER join flght_api_settings as FAS on FAS.user_id =  1
INNER join flight_fare_taxes as FARETAX on FARETAX.flight_id = FGT.id 
INNER join share_inventories as SINVEN on SINVEN.share_by_user_id = FGT.user_id
INNER join cities as  departure_city on departure_city.id = FGT.departure_city_id 
INNER join cities as  arrival_city on arrival_city.id = FGT.arrival_city_id
INNER join airports as  departure_airports on departure_airports.id = FGT.departure_airport_id
INNER join airports as  arrival_airports on arrival_airports.id = FGT.arrival_airport_id
INNER join airlines as  airlines on airlines.id = FGT.airline_id
                            WHERE FGT.status = 1 
                            and now() <=  ADDTIME(DATE_SUB(',"'", departure_date,"'",',INTERVAL FGT.close_before_days DAY),FGT.close_before_time) 
                            and return_flight=',trip_type,' 
                            and departure_city_id = ',departure_city_id,'
                            and departure_airport_id=',departure_airport_id,'
                            and  arrival_city_id=',arrival_city_id,'
                            and arrival_airport_id=', arrival_airport_id,'
                            and FGT.user_id IN (',getsharedUSerID(user_logged_id),') 
                            and SINVEN.id IN  (',getsharedInventoryID(user_logged_id),')  
                            and PSA.plan_no = share_plan
							and PSA.flight_date = ',"'", departure_date,"'",' 
 and PSA.available_seats > 0
and FPNR.available_seat > 0 
and FPNR.date =',"'", departure_date,"'",'
 and FPNR.return_dep_date=',"'",return_date,"'",'
 and SINVEN.status = 1
and SINVEN.accepted_status=1 
and FARETAX.date =',"'", departure_date,"'",' 
group by FPNR.flight_id ORDER by USER.role_id desc');

 insert into test(table_key,table_value) VALUES ('main_return_getSearchData',@SQL_QUERY);
                         
   END IF;
	
     
       PREPARE stmt1 FROM @SQL_QUERY ;
       
       EXECUTE stmt1;
        
        DEALLOCATE PREPARE stmt1;
    

END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `getSearchDetailsData`(IN `user_logged_id` INT, IN `trip_type` INT, IN `flight_id` INT, IN `departure_date` DATE, IN `return_date` DATE, IN `other_1` VARCHAR(100), IN `other_2` VARCHAR(100), IN `other_3` VARCHAR(100), IN `other_4` VARCHAR(100), IN `other_5` VARCHAR(100))
BEGIN

    DECLARE SQL_QUERY  varchar(1000);
     
    
   IF trip_type = 0 THEN
    set @SQL_QUERY = CONCAT('SELECT (select distinct role_id from users where id=SINVEN.share_to_user_id) as share_to_user_role_id,FAS.add_margin,FAS.margin,FAS.client_discount,FAS.vendor_discount,
FAS.payment_gatway_charges,FAS.amount,FAS.percentage, USER.company_name,USER.role_id,FARETAX.base_fare,FARETAX.fee_taxes,SINVEN.credit_limit as inventory_credit_limit,
SINVEN.share_price_breakup as inventory_price_breckup,SINVEN.share_plan,SINVEN.share_by_user_id,SINVEN.share_to_user_id,FGT.id as flight_id,FGT.user_id,
(select option_value from options where user_id=FGT.user_id and option_key="special_info" limit 0,1) as special_info,FGT.re_flight_number,FGT.flight_number,FGT.name,FGT.airline_id,
FGT.departure_city_id,FGT.departure_airport_id,
FGT.dep_terminal_no_id,FGT.departure_time,FGT.arrival_city_id,FGT.arrival_airport_id,FGT.arrival_terminal_no_id,FGT.arrival_date,FGT.arrival_time,FGT.return_flight,FGT.checkin_baggages_adult,
FGT.checkin_baggages_infant,FGT.cabin_baggages_adult,FGT.checkin_baggages_children,FGT.cabin_baggages_children,FGT.cabin_baggages_children,FGT.cabin_baggages_infant,departure_city.country_id as departure_country_id,departure_city.name as departure_city_name,arrival_city.name as arrival_city_name, arrival_city.country_id as arrival_country_id,departure_airports.name as departure_airport_name,departure_airports.airport_code as departure_airport_code,arrival_airports.name as arrival_airport_name, arrival_airports.airport_code as arrival_airport_code, airlines.name as airlines_name,airlines.logo as airlines_logo,airlines.airline_no,airlines.airline_code,FPNR.date as ticket_date ,FPNR.return_dep_date as return_dep_date,FPNR.pnr_number as pnr_number,FPNR.infant_price as infant_price,FPNR.block_seat as block_seat,PSA.available_seats as total_available_seats FROM flights as FGT 
INNER join planwise_seat_availabilities as PSA on PSA.flight_id = FGT.id
INNER join flight_pnrs as FPNR on FPNR.flight_id = FGT.id 
INNER join users as USER on USER.id = FGT.user_id 
INNER join flght_api_settings as FAS on FAS.user_id = 1
INNER join flight_fare_taxes as FARETAX on FARETAX.flight_id = FGT.id 
INNER join share_inventories as SINVEN on SINVEN.share_by_user_id = FGT.user_id
INNER join cities as  departure_city on departure_city.id = FGT.departure_city_id 
INNER join cities as  arrival_city on arrival_city.id = FGT.arrival_city_id
INNER join airports as  departure_airports on departure_airports.id = FGT.departure_airport_id 
INNER join airports as  arrival_airports on arrival_airports.id = FGT.arrival_airport_id
INNER join airlines as  airlines on airlines.id = FGT.airline_id 
WHERE FGT.status = 1
and return_flight=',trip_type,'
and FGT.id=',flight_id,'
and FGT.user_id IN (',getsharedUSerID(user_logged_id),') 
and SINVEN.id IN  (',getsharedInventoryID(user_logged_id),') 
and SINVEN.status = 1
and SINVEN.accepted_status=1 
and FPNR.available_seat > 0
and PSA.plan_no = share_plan 
and PSA.flight_date = ',"'", departure_date,"'",'
and PSA.available_seats > 0 
and FPNR.date =',"'", departure_date,"'",'
and FARETAX.date =',"'", departure_date,"'",' group by FGT.id');
  insert into test(table_key,table_value) VALUES ('main_one_way_getSearchDetailsData',@SQL_QUERY);  
   ELSE 
            set @SQL_QUERY = CONCAT('SELECT (select distinct role_id from users where id=SINVEN.share_to_user_id) as share_to_user_role_id, USER.company_name,USER.role_id,FAS.add_margin,FAS.margin,FAS.client_discount,FAS.vendor_discount,FAS.payment_gatway_charges,FAS.amount,FAS.percentage,  FARETAX.base_fare,FARETAX.fee_taxes,SINVEN.credit_limit as inventory_credit_limit,SINVEN.share_price_breakup as inventory_price_breckup,SINVEN.share_plan,SINVEN.share_by_user_id,SINVEN.share_to_user_id,FGT.id as flight_id,FGT.user_id,(select option_value from options where user_id=FGT.user_id and option_key="special_info" limit 0,1) as special_info,FGT.re_flight_number,FGT.flight_number,FGT.return_dep_time as return_dep_time,FGT.ret_arv_time as ret_arv_time,FGT.name,FGT.airline_id,FGT.departure_city_id,FGT.ret_dep_ter_no_id,FGT.ret_arv_ter_no_id,FGT.departure_airport_id,FGT.dep_terminal_no_id,FGT.departure_time,FGT.arrival_city_id,FGT.arrival_airport_id,FGT.arrival_terminal_no_id,FGT.arrival_date,FGT.arrival_time,FGT.return_flight,FGT.checkin_baggages_adult,FGT.checkin_baggages_infant,FGT.cabin_baggages_adult,FGT.checkin_baggages_children,FGT.cabin_baggages_children,FGT.cabin_baggages_children,FGT.cabin_baggages_infant,departure_city.country_id as departure_country_id,departure_city.name as departure_city_name,arrival_city.name as arrival_city_name, arrival_city.country_id as arrival_country_id,departure_airports.name as departure_airport_name,arrival_airports.name as arrival_airport_name, airlines.name as airlines_name,airlines.logo as airlines_logo,airlines.airline_no,airlines.airline_code ,PSA.available_seats as total_available_seats,FPNR.date as ticket_date ,FPNR.return_dep_date as return_dep_date ,FPNR.pnr_number as pnr_number,FPNR.infant_price as infant_price,FPNR.block_seat as block_seat,return_departure_city.name as return_departure_city_name,return_arrival_city.name as return_arrival_city_name,return_departure_airports.name as return_departure_airport_name,return_departure_airports.airport_code as return_departure_airport_code,return_arrival_airports.name as return_arrival_airport_name,return_arrival_airports.airport_code as return_arrival_airport_code FROM flights as FGT
INNER join planwise_seat_availabilities as PSA on PSA.flight_id = FGT.id
INNER join cities as return_departure_city on return_departure_city.id = FGT.re_dep_city
INNER join cities as  return_arrival_city on return_arrival_city.id = FGT.re_arr_city
INNER join airports as  return_departure_airports on return_departure_airports.id = FGT.re_dep_airport_id
INNER join airports as  return_arrival_airports on return_arrival_airports.id = FGT.re_arr_airport_id
INNER join flight_pnrs as FPNR on FPNR.flight_id = FGT.id
INNER join users as USER on USER.id = FGT.user_id
INNER join flght_api_settings as FAS on FAS.user_id = 1 
INNER join flight_fare_taxes as FARETAX on FARETAX.flight_id = FGT.id
INNER join share_inventories as SINVEN on SINVEN.share_by_user_id = FGT.user_id
INNER join cities as  departure_city on departure_city.id = FGT.departure_city_id
INNER join cities as  arrival_city on arrival_city.id = FGT.arrival_city_id
INNER join airports as  departure_airports on departure_airports.id = FGT.departure_airport_id
INNER join airports as  arrival_airports on arrival_airports.id = FGT.arrival_airport_id
INNER join airlines as  airlines on airlines.id = FGT.airline_id 
WHERE FGT.status = 1
and return_flight=',trip_type,'
and FGT.id=',flight_id,'
and FGT.user_id IN (',getsharedUSerID(user_logged_id),') 
and SINVEN.id IN  (',getsharedInventoryID(user_logged_id),') 
and SINVEN.status = 1
and SINVEN.accepted_status=1 
and FPNR.available_seat > 0
and PSA.plan_no = share_plan 
and PSA.flight_date = ',"'", departure_date,"'",'
and PSA.available_seats > 0 
and FPNR.date =',"'", departure_date,"'",'
and FPNR.return_dep_date =',"'",return_date,"'",' 
and FARETAX.date =',"'", departure_date,"'",' group by FGT.id');
              insert into test(table_key,table_value) VALUES ('main_return_getSearchDetailsData',@SQL_QUERY);            
   END IF;
	
       PREPARE stmt1 FROM @SQL_QUERY ;
       
       EXECUTE stmt1;
        
        DEALLOCATE PREPARE stmt1;
    

END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `getTicketAvailableDate`(IN `user_logged_id` INT, IN `trip_type` INT, IN `departure_city_id` INT, IN `arrival_city_id` INT, IN `other_1` VARCHAR(100), IN `other_2` VARCHAR(100), IN `other_3` VARCHAR(100), IN `other_4` VARCHAR(100), IN `other_5` VARCHAR(100))
BEGIN

    DECLARE SQL_QUERY  varchar(1000);
  
   set @SQL_QUERY = CONCAT('select  distinct PWSA.flight_date from planwise_seat_availabilities as PWSA 
INNER join flights AS flights on PWSA.flight_id = flights.id
INNER join share_inventories as SINVEN on SINVEN.share_by_user_id = flights.user_id
where flights.status = 1  
and flights.departure_city_id = ',departure_city_id,' 
and flights.arrival_city_id = ',arrival_city_id,'
and ADDTIME(DATE_SUB(PWSA.flight_date,INTERVAL flights.close_before_days DAY),flights.close_before_time) >=  now()
and PWSA.plan_no=SINVEN.share_plan
and PWSA.available_seats > 0 
AND flights.return_flight = ',trip_type,'
and SINVEN.status = 1
and SINVEN.accepted_status =1 
and flights.user_id IN (',getsharedUserID(user_logged_id),') ORDER BY PWSA.flight_date ASC');
 
		 insert into test(table_key,table_value) VALUES ('main_getTicketAvailableDate',@SQL_QUERY);
         
       PREPARE stmt1 FROM @SQL_QUERY ;
       
       EXECUTE stmt1;
        
        DEALLOCATE PREPARE stmt1;
    

END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `getTicketReturnAvailableDate`(IN `user_logged_id` INT, IN `departure_city_id` INT, IN `arrival_city_id` INT, IN `other_1` VARCHAR(100), IN `other_2` VARCHAR(100), IN `other_3` VARCHAR(100), IN `other_4` VARCHAR(100), IN `other_5` VARCHAR(100))
BEGIN

    DECLARE SQL_QUERY  varchar(1000);
  
   set @SQL_QUERY = CONCAT('select flights.trip_duration as return_in_days from flights as flights
where flights.status = 1  
and flights.departure_city_id = ',departure_city_id,' 
and flights.arrival_city_id = ',arrival_city_id,'
AND flights.return_flight = 1
and flights.status = 1 group by flights.trip_duration order by trip_duration ASC');

		 insert into test(table_key,table_value) VALUES ('main_getTicketReturnAvailableDate',@SQL_QUERY);
         
       PREPARE stmt1 FROM @SQL_QUERY ;
       
       EXECUTE stmt1;
        
        DEALLOCATE PREPARE stmt1;
    

END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `getsharedInventoryID`(`logged_user_id` INT) RETURNS varchar(1000) CHARSET utf8mb4
    DETERMINISTIC
begin 
	declare user_ID varchar(1000);

                 select GROUP_CONCAT(id) as id  from (select id FROM  share_inventories SI  where share_to_user_id= logged_user_id  and  SI.accepted_status=1 and SI.status=1 and  share_plan !=0
                UNION ALL
                select SI.id  FROM  share_inventories SI 
                inner join users USER on USER.id = SI.share_to_user_id
                where SI.accepted_status=1 and SI.status=1 and share_plan !=0 and USER.role_id=1 
                and share_by_user_id NOT IN(select share_by_user_id FROM  share_inventories SI  where share_to_user_id= logged_user_id  and  SI.accepted_status=1 and SI.status=1 and  share_plan !=0)) aS shared_inventory_id into user_ID;

                 
        return user_ID;
        
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `getsharedUserID`(`logged_user_id` INT) RETURNS varchar(1000) CHARSET utf8mb4
    DETERMINISTIC
begin 
	declare user_ID varchar(1000);

                select GROUP_CONCAT(share_by_user_id) as share_by_user_id  from ( SELECT GROUP_CONCAT(share_by_user_id)  as share_by_user_id  FROM  share_inventories SI 
				 where share_to_user_id= logged_user_id  and  SI.accepted_status=1 and SI.status=1 and 
				 share_plan !=0   
			   	 UNION ALL 
				 SELECT  GROUP_CONCAT(share_by_user_id) as share_by_user_id FROM share_inventories SI
				inner join users US on US.id = SI.share_to_user_id 
				 where SI.status=1 and  SI.accepted_status=1 and share_plan !=0 and US.role_id=1 
				and  share_by_user_id NOT IN ( SELECT  share_by_user_id FROM  share_inventories SI 
				where share_to_user_id=logged_user_id  and  SI.accepted_status=1 and SI.status=1 and 
				 share_plan !=0 )) as share_by_user_id into user_ID;          
        return user_ID;
        
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `patnercompanyAccountDetails`(IN `user_id` INT, IN `share_by_user_id` INT)
BEGIN

    DECLARE SQL_QUERY  varchar(1000);
   
   set @SQL_QUERY = CONCAT('select available_balance as credit_limit,
(SELECT company_name from users where id=',share_by_user_id,') as company_name,
(select amount from accounts where share_by_user_id=',share_by_user_id,' and share_to_user_id=',user_id,'  and account_type="balance" order by createdAt DESC limit 0,1) as last_credited_amount,
(select DATE_FORMAT(createdAt,"%d-%m-%Y") from accounts where share_by_user_id=',share_by_user_id,' and share_to_user_id=',user_id,'  and account_type="balance" order by createdAt DESC limit 0,1) as last_debited_date,   
(select amount from accounts where share_by_user_id=',share_by_user_id,' and share_to_user_id=',user_id,' and account_type="booking" order by createdAt DESC limit 0,1) as last_transaction_amount,
(select DATE_FORMAT(createdAt,"%d-%m-%Y") from accounts where share_by_user_id=',share_by_user_id,' and share_to_user_id=',user_id,' and account_type="booking" order by createdAt DESC limit 0,1) as last_transaction_date
from accounts where share_by_user_id=',share_by_user_id,' and share_to_user_id=',user_id,' order by createdAt DESC limit 0,1');
   
   

   
                             insert into test(table_key,table_value) VALUES ('main_patnercompanyAccountDetails',@SQL_QUERY);
                             
     
       PREPARE stmt1 FROM @SQL_QUERY ;
       
       EXECUTE stmt1;
        
        DEALLOCATE PREPARE stmt1;
    
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `websiteGetArrivalCities`(IN `websiter_user_id` INT, IN `trip_type` INT, IN `departure_city_id` INT, IN `other_1` VARCHAR(100), IN `other_2` VARCHAR(100), IN `other_3` VARCHAR(100), IN `other_4` VARCHAR(100), IN `other_5` VARCHAR(100))
BEGIN

    DECLARE SQL_QUERY  varchar(1000);
  
   set @SQL_QUERY = CONCAT('select SINVEN.id as shared_inventory_plan_Id,flights.id, flights.arrival_city_id , flights.arrival_airport_id , 
airport.id AS airport_id, airport.name AS airport_name,airport.airport_code AS airport_code, arrival_city.name AS arrival_city_name, 
 arrival_city.city_code AS arrival_city_code,SINVEN.share_plan as vendor_share_plan
 FROM flights AS flights 
INNER JOIN airports AS airport ON  flights.arrival_airport_id = airport.id 
INNER JOIN cities AS arrival_city ON  flights.arrival_city_id = arrival_city.id
INNER join share_inventories as SINVEN on SINVEN.share_by_user_id = flights.user_id
 INNER join planwise_seat_availabilities as PWSA on PWSA.flight_id = flights.id
 WHERE flights.status = 1 
 and PWSA.plan_no=SINVEN.share_plan 
 and SINVEN.share_to_user_id=',websiter_user_id,' and  ADDTIME(DATE_SUB(PWSA.flight_date,INTERVAL flights.close_before_days DAY),flights.close_before_time) >=  now() 
 /*and SINVEN.id IN  (2,22) */
 and  PWSA.available_seats > 0 
 AND flights.return_flight = ',trip_type,'
 AND flights.departure_city_id = ',departure_city_id,'
 and SINVEN.status = 1 
 and SINVEN.accepted_status=1
 and flights.user_id IN (',websiteGetsharedUserID(websiter_user_id),') 
 GROUP BY arrival_city_id, arrival_airport_id ORDER BY flights.id DESC');
  /*INNER join flight_pnrs as FPNR on FPNR.flight_id = flights.id*/
 /*and ',getSharedInventoryPlan('shared_inventory_plan_Id'),' > 0 */
 
		 insert into test(table_key,table_value) VALUES ('website_websiteGetArrivalCities',@SQL_QUERY);
         
       PREPARE stmt1 FROM @SQL_QUERY ;
       
       EXECUTE stmt1;
        
        DEALLOCATE PREPARE stmt1;
    

END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `websiteGetDepartureCities`(IN `websiter_user_id` INT, IN `trip_type` INT, IN `other_1` VARCHAR(100), IN `other_2` VARCHAR(100), IN `other_3` VARCHAR(100), IN `other_4` VARCHAR(100), IN `other_5` VARCHAR(100))
BEGIN

    DECLARE SQL_QUERY  varchar(1000);
  
    set @SQL_QUERY = CONCAT('SELECT SINVEN.id as shared_inventory_plan_Id,flights.id, flights.departure_city_id , flights.departure_airport_id , 
airport.id AS airport_id, airport.name AS airport_name, airport.airport_code AS airport_code,departure_city.name AS departure_city_name, departure_city.city_code AS departure_city_code,SINVEN.share_plan as vendor_share_plan
 FROM flights AS flights 
 INNER JOIN airports AS airport ON  flights.departure_airport_id = airport.id 
INNER JOIN cities AS departure_city ON  flights.departure_city_id = departure_city.id 
INNER join share_inventories as SINVEN on SINVEN.share_by_user_id = flights.user_id 
 INNER join planwise_seat_availabilities as PWSA on PWSA.flight_id = flights.id
 WHERE flights.status = 1 
 and PWSA.plan_no=SINVEN.share_plan 
 and SINVEN.share_to_user_id=',websiter_user_id,' and  ADDTIME(DATE_SUB(PWSA.flight_date,INTERVAL flights.close_before_days DAY),flights.close_before_time) >=  now() 
 /*and SINVEN.id IN  (2,22) */
 and  PWSA.available_seats > 0 
 AND flights.return_flight = ',trip_type,'
 and SINVEN.status = 1 
 and SINVEN.accepted_status=1
 and flights.user_id IN (',websiteGetsharedUserID(websiter_user_id),') 
 GROUP BY departure_city_id, departure_airport_id ORDER BY flights.id DESC ');
  /*INNER join flight_pnrs as FPNR on FPNR.flight_id = flights.id*/
 /*and ',getSharedInventoryPlan('shared_inventory_plan_Id'),' > 0 */
 
		 insert into test(table_key,table_value) VALUES ('website_websiteGetDepartureCities',@SQL_QUERY);
         
       PREPARE stmt1 FROM @SQL_QUERY ;
       
       EXECUTE stmt1;
        
        DEALLOCATE PREPARE stmt1;
    

END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `websiteGetSearchData`(IN `websiter_user_id` INT, IN `trip_type` INT, IN `departure_city_id` INT, IN `departure_airport_id` INT, IN `arrival_city_id` INT, IN `arrival_airport_id` INT, IN `departure_date` DATE, IN `return_date` DATE, IN `other_1` VARCHAR(100), IN `other_2` VARCHAR(100), IN `other_3` VARCHAR(100), IN `other_4` VARCHAR(100), IN `other_5` VARCHAR(100))
BEGIN

    DECLARE SQL_QUERY  varchar(1000);
    
    	/*insert into test(table_key,table_value) VALUES ('conditions', CONCAT(user_logged_id,trip_type,departure_city_id,departure_airport_id,arrival_city_id,arrival_airport_id,departure_date));*/
     
     /*CALL getShareUserIDwithAdmin(user_logged_id); */
    
   IF trip_type = 0 THEN
   set @SQL_QUERY = CONCAT('SELECT (select distinct role_id from users where id=SINVEN.share_to_user_id) as share_to_user_role_id, FAS.add_margin,FAS.margin,FAS.client_discount,FAS.vendor_discount,USER.company_name, USER.role_id, FARETAX.base_fare, FARETAX.fee_taxes, SINVEN.credit_limit as inventory_credit_limit, SINVEN.share_price_breakup as inventory_price_breckup, SINVEN.share_plan,SINVEN.share_by_user_id,SINVEN.share_to_user_id,FGT.id as flight_id,FGT.user_id,(select option_value from options where user_id=FGT.user_id and option_key="special_info" limit 0,1) as special_info, FGT.re_flight_number,FGT.flight_number,FGT.name,FGT.airline_id, FGT.departure_city_id,FGT.departure_airport_id,FGT.dep_terminal_no_id, FGT.departure_time,FGT.arrival_city_id,FGT.arrival_airport_id,FGT.arrival_terminal_no_id,FGT.arrival_date,FGT.arrival_time,FGT.return_flight,FGT.checkin_baggages_adult,FGT.checkin_baggages_infant,FGT.cabin_baggages_adult,FGT.cabin_baggages_children,FGT.checkin_baggages_children,FGT.cabin_baggages_children,FGT.cabin_baggages_infant,departure_city.country_id as departure_country_id,departure_city.name as departure_city_name,arrival_city.name as arrival_city_name, arrival_city.country_id as arrival_country_id,departure_airports.name as departure_airport_name,departure_airports.airport_code as departure_airport_code,arrival_airports.name as arrival_airport_name ,arrival_airports.airport_code as arrival_airport_code ,airlines.name as airlines_name,airlines.logo as airlines_logo,airlines.airline_no,airlines.airline_code,FPNR.date as ticket_date ,FPNR.return_dep_date as return_dep_date,FPNR.pnr_number as pnr_number,FPNR.infant_price as infant_price,FPNR.block_seat as block_seat,PSA.available_seats as total_available_seats FROM flights as FGT
                            INNER join planwise_seat_availabilities as PSA on PSA.flight_id = FGT.id 
                            INNER join flight_pnrs as FPNR on FPNR.flight_id = FGT.id 
                            INNER join flight_fare_taxes as FARETAX on FARETAX.flight_id = FGT.id 
                            INNER join users as USER on USER.id = FGT.user_id 
                            INNER join flght_api_settings as FAS on FAS.user_id = USER.id
                            INNER join share_inventories as SINVEN on SINVEN.share_by_user_id = FGT.user_id 
                            INNER join cities as  departure_city on departure_city.id = FGT.departure_city_id
                            INNER join cities as  arrival_city on arrival_city.id = FGT.arrival_city_id 
                            INNER join airports as  departure_airports on departure_airports.id = FGT.departure_airport_id 
                            INNER join airports as  arrival_airports on arrival_airports.id = FGT.arrival_airport_id 
                            INNER join airlines as  airlines on airlines.id = FGT.airline_id 
                            WHERE FGT.status = 1 
                            and now() <=  ADDTIME(DATE_SUB(',"'", departure_date,"'",',INTERVAL FGT.close_before_days DAY),FGT.close_before_time) 
                            and return_flight=',trip_type,' 
                            and departure_city_id = ',departure_city_id,'
                            and departure_airport_id=',departure_airport_id,'
                            and  arrival_city_id=',arrival_city_id,'
                            and arrival_airport_id=', arrival_airport_id,'
                            and FGT.user_id IN (',websiteGetsharedUserID(websiter_user_id),') 
                            and SINVEN.id IN  (',websiteGetsharedInventoryID(websiter_user_id),')  
                            and PSA.plan_no = share_plan
							and PSA.flight_date = ',"'", departure_date,"'",' 
                            and PSA.available_seats > 0
                            and FPNR.available_seat > 0 
                            and FPNR.date =',"'", departure_date,"'",' 
                            and SINVEN.status = 1
                            and SINVEN.accepted_status=1 
                            and FARETAX.date =',"'", departure_date,"'",' 
                            group by FPNR.flight_id ORDER by USER.role_id desc');
                             insert into test(table_key,table_value) VALUES ('website_oneway_getSearchData',@SQL_QUERY);
   ELSE 
          set @SQL_QUERY = CONCAT('SELECT  (select distinct role_id from users where id=SINVEN.share_to_user_id) as share_to_user_role_id,USER.company_name, USER.role_id,FAS.add_margin,FAS.margin,FAS.client_discount,FAS.vendor_discount,FARETAX.base_fare,FARETAX.fee_taxes,SINVEN.credit_limit as inventory_credit_limit,SINVEN.share_price_breakup as inventory_price_breckup,SINVEN.share_plan,SINVEN.share_by_user_id,SINVEN.share_to_user_id,(select option_value from options where user_id=FGT.user_id and option_key="special_info" limit 0,1) as special_info,FGT.re_flight_number,FGT.id as flight_id,FGT.user_id,FGT.flight_number,FGT.ret_dep_ter_no_id,FGT.ret_arv_ter_no_id,FGT.name,FGT.airline_id,FGT.departure_city_id,FGT.departure_airport_id,FGT.dep_terminal_no_id,FGT.departure_time,FGT.arrival_city_id,FGT.arrival_airport_id,FGT.arrival_terminal_no_id,FGT.arrival_date,FGT.arrival_time,FGT.return_dep_time as return_dep_time,FGT.ret_arv_time as ret_arv_time,FGT.return_flight,FGT.checkin_baggages_adult,FGT.checkin_baggages_infant,FGT.cabin_baggages_adult,FGT.checkin_baggages_children,FGT.cabin_baggages_children,FGT.cabin_baggages_children,FGT.cabin_baggages_infant,departure_city.country_id as departure_country_id,departure_city.name as departure_city_name,arrival_city.name as arrival_city_name, arrival_city.country_id as arrival_country_id,departure_airports.name as departure_airport_name,departure_airports.airport_code as departure_airport_code,arrival_airports.name as arrival_airport_name,arrival_airports.airport_code as arrival_airport_code,airlines.name as airlines_name,airlines.logo as airlines_logo,airlines.airline_no,airlines.airline_code ,PSA.available_seats as total_available_seats,FPNR.date as ticket_date ,FPNR.return_dep_date as return_dep_date ,FPNR.pnr_number as pnr_number,FPNR.infant_price as infant_price,FPNR.block_seat as block_seat,return_departure_city.name as return_departure_city_name,return_arrival_city.name as return_arrival_city_name,return_departure_airports.name as return_departure_airport_name,return_departure_airports.airport_code as return_departure_airport_code,return_arrival_airports.name as return_arrival_airport_name,return_arrival_airports.airport_code as return_arrival_airport_code FROM flights as FGT 
INNER join planwise_seat_availabilities as PSA on PSA.flight_id = FGT.id 
INNER join cities as return_departure_city on return_departure_city.id = FGT.re_dep_city 
INNER join cities as  return_arrival_city on return_arrival_city.id = FGT.re_arr_city 
INNER join airports as  return_departure_airports on return_departure_airports.id = FGT.re_dep_airport_id
INNER join airports as  return_arrival_airports on return_arrival_airports.id = FGT.re_arr_airport_id 
INNER join flight_pnrs as FPNR on FPNR.flight_id = FGT.id 
INNER join users as USER on USER.id = FGT.user_id
INNER join flght_api_settings as FAS on FAS.user_id =  USER.id
INNER join flight_fare_taxes as FARETAX on FARETAX.flight_id = FGT.id 
INNER join share_inventories as SINVEN on SINVEN.share_by_user_id = FGT.user_id
INNER join cities as  departure_city on departure_city.id = FGT.departure_city_id 
INNER join cities as  arrival_city on arrival_city.id = FGT.arrival_city_id
INNER join airports as  departure_airports on departure_airports.id = FGT.departure_airport_id
INNER join airports as  arrival_airports on arrival_airports.id = FGT.arrival_airport_id
INNER join airlines as  airlines on airlines.id = FGT.airline_id
                            WHERE FGT.status = 1 
                            and now() <=  ADDTIME(DATE_SUB(',"'", departure_date,"'",',INTERVAL FGT.close_before_days DAY),FGT.close_before_time) 
                            and return_flight=',trip_type,' 
                            and departure_city_id = ',departure_city_id,'
                            and departure_airport_id=',departure_airport_id,'
                            and  arrival_city_id=',arrival_city_id,'
                            and arrival_airport_id=', arrival_airport_id,'
                            and FGT.user_id IN (',websiteGetsharedUserID(websiter_user_id),') 
                            and SINVEN.id IN  (',websiteGetsharedInventoryID(websiter_user_id),')  
                            and PSA.plan_no = share_plan
							and PSA.flight_date = ',"'", departure_date,"'",' 
 and PSA.available_seats > 0
and FPNR.available_seat > 0 
and FPNR.date =',"'", departure_date,"'",'
 and FPNR.return_dep_date=',"'",return_date,"'",'
 and SINVEN.status = 1
and SINVEN.accepted_status=1 
and FARETAX.date =',"'", departure_date,"'",' 
group by FPNR.flight_id ORDER by USER.role_id desc');

 insert into test(table_key,table_value) VALUES ('website_return_getSearchData',@SQL_QUERY);
                         
   END IF;
	
     
       PREPARE stmt1 FROM @SQL_QUERY ;
       
       EXECUTE stmt1;
        
        DEALLOCATE PREPARE stmt1;
    

END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `websiteGetSearchDetailsData`(IN `websiter_user_id` INT, IN `trip_type` INT, IN `flight_id` INT, IN `departure_date` DATE, IN `return_date` DATE, IN `other_1` VARCHAR(100), IN `other_2` VARCHAR(100), IN `other_3` VARCHAR(100), IN `other_4` VARCHAR(100), IN `other_5` VARCHAR(100))
BEGIN

    DECLARE SQL_QUERY  varchar(1000);
     
    
   IF trip_type = 0 THEN
    set @SQL_QUERY = CONCAT('SELECT (select distinct role_id from users where id=SINVEN.share_to_user_id) as share_to_user_role_id,FAS.add_margin,FAS.margin,FAS.client_discount,FAS.vendor_discount,
FAS.payment_gatway_charges,FAS.amount,FAS.percentage, USER.company_name,USER.role_id,FARETAX.base_fare,FARETAX.fee_taxes,SINVEN.credit_limit as inventory_credit_limit,
SINVEN.share_price_breakup as inventory_price_breckup,SINVEN.share_plan,SINVEN.share_by_user_id,SINVEN.share_to_user_id,FGT.id as flight_id,FGT.user_id,
(select option_value from options where user_id=FGT.user_id and option_key="special_info" limit 0,1) as special_info,FGT.re_flight_number,FGT.flight_number,FGT.name,FGT.airline_id,
FGT.departure_city_id,FGT.departure_airport_id,
FGT.dep_terminal_no_id,FGT.departure_time,FGT.arrival_city_id,FGT.arrival_airport_id,FGT.arrival_terminal_no_id,FGT.arrival_date,FGT.arrival_time,FGT.return_flight,FGT.checkin_baggages_adult,
FGT.checkin_baggages_infant,FGT.cabin_baggages_adult,FGT.checkin_baggages_children,FGT.cabin_baggages_children,FGT.cabin_baggages_children,FGT.cabin_baggages_infant,departure_city.country_id as departure_country_id,departure_city.name as departure_city_name,arrival_city.name as arrival_city_name, arrival_city.country_id as arrival_country_id,departure_airports.name as departure_airport_name,departure_airports.airport_code as departure_airport_code,arrival_airports.name as arrival_airport_name, arrival_airports.airport_code as arrival_airport_code, airlines.name as airlines_name,airlines.logo as airlines_logo,airlines.airline_no,airlines.airline_code,FPNR.date as ticket_date ,FPNR.return_dep_date as return_dep_date,FPNR.pnr_number as pnr_number,FPNR.infant_price as infant_price,FPNR.block_seat as block_seat,PSA.available_seats as total_available_seats FROM flights as FGT 
INNER join planwise_seat_availabilities as PSA on PSA.flight_id = FGT.id
INNER join flight_pnrs as FPNR on FPNR.flight_id = FGT.id 
INNER join users as USER on USER.id = FGT.user_id 
INNER join flght_api_settings as FAS on FAS.user_id = USER.id 
INNER join flight_fare_taxes as FARETAX on FARETAX.flight_id = FGT.id 
INNER join share_inventories as SINVEN on SINVEN.share_by_user_id = FGT.user_id
INNER join cities as  departure_city on departure_city.id = FGT.departure_city_id 
INNER join cities as  arrival_city on arrival_city.id = FGT.arrival_city_id
INNER join airports as  departure_airports on departure_airports.id = FGT.departure_airport_id 
INNER join airports as  arrival_airports on arrival_airports.id = FGT.arrival_airport_id
INNER join airlines as  airlines on airlines.id = FGT.airline_id 
WHERE FGT.status = 1
and return_flight=',trip_type,'
and FGT.id=',flight_id,'
and FGT.user_id IN (',websiteGetsharedUserID(websiter_user_id),') 
and SINVEN.id IN  (',websiteGetsharedInventoryID(websiter_user_id),') 
and SINVEN.status = 1
and SINVEN.accepted_status=1 
and FPNR.available_seat > 0
and PSA.plan_no = share_plan 
and PSA.flight_date = ',"'", departure_date,"'",'
and PSA.available_seats > 0 
and FPNR.date =',"'", departure_date,"'",'
and FARETAX.date =',"'", departure_date,"'",' group by FGT.id');
  insert into test(table_key,table_value) VALUES ('website_one_way_getSearchDetailsData',@SQL_QUERY);  
   ELSE 
            set @SQL_QUERY = CONCAT('SELECT (select distinct role_id from users where id=SINVEN.share_to_user_id) as share_to_user_role_id, USER.company_name,USER.role_id,FAS.add_margin,FAS.margin,FAS.client_discount,FAS.vendor_discount,FAS.payment_gatway_charges,FAS.amount,FAS.percentage,  FARETAX.base_fare,FARETAX.fee_taxes,SINVEN.credit_limit as inventory_credit_limit,SINVEN.share_price_breakup as inventory_price_breckup,SINVEN.share_plan,SINVEN.share_by_user_id,SINVEN.share_to_user_id,FGT.id as flight_id,FGT.user_id,(select option_value from options where user_id=FGT.user_id and option_key="special_info" limit 0,1) as special_info,FGT.re_flight_number,FGT.flight_number,FGT.return_dep_time as return_dep_time,FGT.ret_arv_time as ret_arv_time,FGT.name,FGT.airline_id,FGT.departure_city_id,FGT.ret_dep_ter_no_id,FGT.ret_arv_ter_no_id,FGT.departure_airport_id,FGT.dep_terminal_no_id,FGT.departure_time,FGT.arrival_city_id,FGT.arrival_airport_id,FGT.arrival_terminal_no_id,FGT.arrival_date,FGT.arrival_time,FGT.return_flight,FGT.checkin_baggages_adult,FGT.checkin_baggages_infant,FGT.cabin_baggages_adult,FGT.checkin_baggages_children,FGT.cabin_baggages_children,FGT.cabin_baggages_children,FGT.cabin_baggages_infant,departure_city.country_id as departure_country_id,departure_city.name as departure_city_name,arrival_city.name as arrival_city_name, arrival_city.country_id as arrival_country_id,departure_airports.name as departure_airport_name,arrival_airports.name as arrival_airport_name, airlines.name as airlines_name,airlines.logo as airlines_logo,airlines.airline_no,airlines.airline_code ,PSA.available_seats as total_available_seats,FPNR.date as ticket_date ,FPNR.return_dep_date as return_dep_date ,FPNR.pnr_number as pnr_number,FPNR.infant_price as infant_price,FPNR.block_seat as block_seat,return_departure_city.name as return_departure_city_name,return_arrival_city.name as return_arrival_city_name,return_departure_airports.name as return_departure_airport_name,return_departure_airports.airport_code as return_departure_airport_code,return_arrival_airports.name as return_arrival_airport_name,return_arrival_airports.airport_code as return_arrival_airport_code FROM flights as FGT
INNER join planwise_seat_availabilities as PSA on PSA.flight_id = FGT.id
INNER join cities as return_departure_city on return_departure_city.id = FGT.re_dep_city
INNER join cities as  return_arrival_city on return_arrival_city.id = FGT.re_arr_city
INNER join airports as  return_departure_airports on return_departure_airports.id = FGT.re_dep_airport_id
INNER join airports as  return_arrival_airports on return_arrival_airports.id = FGT.re_arr_airport_id
INNER join flight_pnrs as FPNR on FPNR.flight_id = FGT.id
INNER join users as USER on USER.id = FGT.user_id
INNER join flght_api_settings as FAS on FAS.user_id = USER.id
INNER join flight_fare_taxes as FARETAX on FARETAX.flight_id = FGT.id
INNER join share_inventories as SINVEN on SINVEN.share_by_user_id = FGT.user_id
INNER join cities as  departure_city on departure_city.id = FGT.departure_city_id
INNER join cities as  arrival_city on arrival_city.id = FGT.arrival_city_id
INNER join airports as  departure_airports on departure_airports.id = FGT.departure_airport_id
INNER join airports as  arrival_airports on arrival_airports.id = FGT.arrival_airport_id
INNER join airlines as  airlines on airlines.id = FGT.airline_id 
WHERE FGT.status = 1
and return_flight=',trip_type,'
and FGT.id=',flight_id,'
and FGT.user_id IN (',websiteGetsharedUserID(websiter_user_id),') 
and SINVEN.id IN  (',websiteGetsharedInventoryID(websiter_user_id),') 
and SINVEN.status = 1
and SINVEN.accepted_status=1 
and FPNR.available_seat > 0
and PSA.plan_no = share_plan 
and PSA.flight_date = ',"'", departure_date,"'",'
and PSA.available_seats > 0 
and FPNR.date =',"'", departure_date,"'",'
and FPNR.return_dep_date =',"'",return_date,"'",' 
and FARETAX.date =',"'", departure_date,"'",' group by FGT.id');
              insert into test(table_key,table_value) VALUES ('website_return_getSearchDetailsData',@SQL_QUERY);            
   END IF;
	
       PREPARE stmt1 FROM @SQL_QUERY ;
       
       EXECUTE stmt1;
        
        DEALLOCATE PREPARE stmt1;
    

END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `websiteGetTicketAvailableDate`(IN `websiter_user_id` INT, IN `trip_type` INT, IN `departure_city_id` INT, IN `arrival_city_id` INT, IN `other_1` VARCHAR(100), IN `other_2` VARCHAR(100), IN `other_3` VARCHAR(100), IN `other_4` VARCHAR(100), IN `other_5` VARCHAR(100))
BEGIN

    DECLARE SQL_QUERY  varchar(1000);
  
   set @SQL_QUERY = CONCAT('select  distinct PWSA.flight_date from planwise_seat_availabilities as PWSA 
INNER join flights AS flights on PWSA.flight_id = flights.id
INNER join share_inventories as SINVEN on SINVEN.share_by_user_id = flights.user_id
where flights.status = 1  
and flights.departure_city_id = ',departure_city_id,' 
and flights.arrival_city_id = ',arrival_city_id,'
and ADDTIME(DATE_SUB(PWSA.flight_date,INTERVAL flights.close_before_days DAY),flights.close_before_time) >=  now()
and PWSA.plan_no=SINVEN.share_plan
and PWSA.available_seats > 0 
AND flights.return_flight = ',trip_type,'
and SINVEN.status = 1
and SINVEN.accepted_status =1 
and flights.user_id IN (',websiteGetsharedUserID(websiter_user_id),') ORDER BY PWSA.flight_date ASC');
 
		 insert into test(table_key,table_value) VALUES ('webiste_websiteGetTicketAvailableDate',@SQL_QUERY);
         
       PREPARE stmt1 FROM @SQL_QUERY ;
       
       EXECUTE stmt1;
        
        DEALLOCATE PREPARE stmt1;
    

END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `websiteGetTicketReturnAvailableDate`(IN `departure_city_id` INT, IN `arrival_city_id` INT, IN `other_1` VARCHAR(100), IN `other_2` VARCHAR(100), IN `other_3` VARCHAR(100), IN `other_4` VARCHAR(100), IN `other_5` VARCHAR(100))
BEGIN

    DECLARE SQL_QUERY  varchar(1000);
  
   set @SQL_QUERY = CONCAT('select flights.trip_duration as return_in_days from flights as flights
where flights.status = 1  
and flights.departure_city_id = ',departure_city_id,' 
and flights.arrival_city_id = ',arrival_city_id,'
AND flights.return_flight = 1
and flights.status = 1 group by flights.trip_duration order by trip_duration ASC');

		 insert into test(table_key,table_value) VALUES ('website_websiteGetTicketReturnAvailableDate',@SQL_QUERY);
         
       PREPARE stmt1 FROM @SQL_QUERY ;
       
       EXECUTE stmt1;
        
        DEALLOCATE PREPARE stmt1;
    

END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `websiteGetsharedInventoryID`(`website_user_id` INT) RETURNS varchar(1000) CHARSET utf8mb4
    DETERMINISTIC
begin 
	declare user_ID varchar(1000);

                
                 select GROUP_CONCAT(id) as id  from (select SI.id  FROM  share_inventories SI 
                inner join users USER on USER.id = SI.share_by_user_id
                where SI.accepted_status=1 and SI.status=1 and SI.share_to_user_id=website_user_id and share_plan !=0 and USER.role_id IN(3,4)) aS shared_inventory_id into user_ID;

                 
        return user_ID;
        
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `websiteGetsharedUserID`(`website_user_id` INT) RETURNS varchar(1000) CHARSET utf8mb4
    DETERMINISTIC
begin 
	declare user_ID varchar(1000);
  				select GROUP_CONCAT(share_by_user_id) as share_by_user_id  from (SELECT  GROUP_CONCAT(share_by_user_id) as share_by_user_id FROM share_inventories SI inner join users US on US.id = SI.share_by_user_id 				 where SI.status=1 and  SI.accepted_status=1 and share_plan !=0 and US.role_id IN (3,5) and SI.share_to_user_id =website_user_id ) as share_by_user_id into user_ID;      
  
        return user_ID;
        
END$$
DELIMITER ;
