DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_a_driver_email_history` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_a_driver_email_history`(IN `did` INT)
BEGIN
SELECT * FROM `email_history` WHERE `uid`=did;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_a_driver_feature_update` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_a_driver_feature_update`(IN `feat` VARCHAR(50), IN `dids` INT)
BEGIN
UPDATE `driver` SET `driver_feature`=feat WHERE `UID`=dids;
SELECT ROW_COUNT() AS features;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_a_driver_groups_remove` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_a_driver_groups_remove`(IN `groups` VARCHAR(50))
BEGIN
SELECT `id`,`group_name` FROM `vendor` WHERE `group_name` LIKE CONCAT('%', groups , '%') LIMIT 0,10; 
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_a_driver_group_list` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_a_driver_group_list`(IN `vids` INT)
BEGIN
SELECT a.UID,a.FirstName,a.ContactNo,a.Email,a.is_active,c.loginStatus,d.name,e.status FROM `driver` a INNER JOIN `vendor` b ON a.vendor_id=b.id INNER JOIN `user` c ON a.UID=c.ID INNER JOIN `cab_list_mgmt` d ON a.vehicleId=d.id INNER JOIN `cab_status` e ON a.is_active=e.status_id WHERE b.UID = vids AND e.`type`='driverS';
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_a_driver_group_name` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_a_driver_group_name`(IN `driverid` INT)
BEGIN
SELECT a.group_name FROM vendor a INNER JOIN driver b ON a.id=b.vendor_id WHERE b.UID=driverid;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_a_driver_image_verf` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_a_driver_image_verf`(IN `image` VARCHAR(50), IN `dids` INT)
BEGIN
UPDATE `driver` SET `photo_verify`=image WHERE `uid`=dids;
SELECT ROW_COUNT() AS img;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_a_driver_list` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_a_driver_list`()
BEGIN
SELECT
  (
  SELECT
    AVG(rating)
  FROM
    driver_rating
  WHERE
    driver_rating.DID = driver.id
  GROUP BY
    driver_rating.DID
) AS DriverRating,
driver.login_status AS LoginStatus,
driver.is_active AS IsActive,
driver.email,
driver.id as uid,
driver.first_name,
driver.mobile AS contact_no,
city.name as city_name,
vm.registration_number as vehicle_reg_no, 
driver2.driving_license_no,
ignition_type.fuel_type as CabIgnitionType,
driver2.is_active,
driver2.status, 
active.status AS approvedStatus,
driver2.created_date,
mvm.name AS vehicleName,
company.company_name as CompanyName,
COUNT(bk.driver_id) AS booking
FROM
  user AS driver
Left JOIN 
      driver AS driver2 on driver.id =  driver2.user_id
LEFT JOIN
  user_info AS userInfo ON driver.id = userInfo.user_id
LEFT JOIN 
  master_city AS city ON  userInfo.city_id = city.id
LEFT JOIN
  user_vehicle_mapping AS uvm ON driver.id = uvm.user_id
LEFT JOIN
 vehicle_master AS vm ON uvm.vehicle_master_id = vm.vehicle_master_id
LEFT JOIN 
 master_fuel_type AS ignition_type ON vm.ignition_type_id = ignition_type.id
LEFT JOIN 
 master_vehicle_model AS mvm ON  vm.id = mvm.id
LEFT JOIN
  booking AS bk ON driver.id = bk.driver_id
LEFT JOIN 
  company ON driver.id = company.user_id
LEFT JOIN
  active ON driver2.is_active = active.status_id
where driver.user_type_id = 3
GROUP BY  (driver.id);
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_a_driver_list_old` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_a_driver_list_old`()
BEGIN
SELECT (SELECT AVG(rating) FROM driver_rating WHERE driver_rating.DID = driver.UID GROUP BY driver_rating.DID) AS DriverRating, user.login_status as LoginStatus,user.is_active as IsActive,driver.email,driver.uid,driver.first_name,driver.status,driver.contact_no,driver.vehicle_reg_no,driver.driving_license_no,
driver.is_active,active.status AS approvedStatus,driver.created_date,cab_master.CabName AS vehicleName, ignition_type.CabIgnitionType ,
count(pickup) AS booking 
FROM driver  
INNER JOIN cab_master ON driver.vehicle_id = cab_master.cabId 
INNER JOIN ignition_type ON ignition_type.id = cab_master.CabIgnitionTypeId
INNER JOIN vehicle_type ON vehicle_type.Id = cab_master.CabType
LEFT JOIN cab_booking ON driver.uid=cab_booking.pickup 
INNER JOIN user ON driver.uid = user.id 
INNER join active on driver.is_active = active.status_id  group by(uid);
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_a_driver_remove_group` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_a_driver_remove_group`(IN `grName` VARCHAR(50))
BEGIN
DECLARE gid INT;
DECLARE lo INT;
SELECT `id` INTO gid FROM `vendor` WHERE `group_name`=grName;
IF gid > 0 THEN
UPDATE `driver` SET `vendor_id`=0 where vendor_id = gid;
SELECT ROW_COUNT() INTO lo;
END IF;
SELECT lo, gid;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_a_driver_search` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_a_driver_search`(IN `data` TEXT)
BEGIN
SELECT `UID`,driver.VehicleRegistrationNo name FROM `driver` JOIN user ON driver.UID=user.id WHERE driver.`status`=0 and user.loginStatus=1 and driver.VehicleRegistrationNo LIKE CONCAT('%', data , '%');
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_a_driver_sms` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_a_driver_sms`(IN `ids` INT, IN `sms` LONGTEXT, IN `cont` VARCHAR(50))
BEGIN
INSERT INTO `tblsmsstatus` (`UID`,`mesg`,`ContactNo`,`status`,`sent_time`) VALUES (ids,sms,cont,'1',NOW());
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_a_driver_sms_history` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_a_driver_sms_history`(IN `ids` VARCHAR(50))
BEGIN
SELECT * FROM `sms_status` WHERE `uid`=ids;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_a_driver_tracking` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_a_driver_tracking`(IN `bookingId` INT)
BEGIN
SELECT user.Latitude, user.Longtitude1 from user inner join cab_booking on user.ID = cab_booking.pickup where user.UserType = '3' and cab_booking.ID = bookingId;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_a_driver_update_profile` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_a_driver_update_profile`(IN `dids` INT, IN `name` VARCHAR(50), IN `lname` VARCHAR(50), IN `father` VARCHAR(50), IN `cityname` VARCHAR(50), IN `emaild` VARCHAR(50), IN `dverifyE` INT, IN `uname` VARCHAR(50), IN `dadds` VARCHAR(50), IN `doffadds` VARCHAR(50), IN `dphone` VARCHAR(50), IN `dverifyP` INT, IN `dpannos` VARCHAR(50), IN `dlicence` VARCHAR(50), IN `dvehicleno` VARCHAR(50), IN `dfleet` VARCHAR(50), IN `dmodel` VARCHAR(50), IN `dlstate` VARCHAR(50), IN `eyesite` VARCHAR(50), IN `d_zone` VARCHAR(50), IN `dsmount` VARCHAR(50), IN `d_route` VARCHAR(50), IN `d_pref` VARCHAR(50), IN `doff` VARCHAR(50), IN `dimei` VARCHAR(50), IN `dgps` VARCHAR(50), IN `dlang_w` TEXT, IN `dlan_s` TEXT, IN `pref_t` TEXT, IN `dactivetion` INT, IN `reci_port` TEXT, IN `dacc_cash` VARCHAR(50), IN `dsigupC` LONGTEXT, IN `dinternalC` LONGTEXT, IN `driveBirth` VARCHAR(100), IN `d_ac_nonac` VARCHAR(10), IN `operational_company_name` VARCHAR(150), IN `vehicle_owned` VARCHAR(100), IN `vehicle_owner_name` VARCHAR(100), IN `vehicle_owner_cnt_no` VARCHAR(100), IN `vehicle_owner_alt_cnt_no` VARCHAR(100), IN `license_validity` VARCHAR(100), IN `insurance_validity` VARCHAR(100), IN `rc_no` VARCHAR(100), IN `com_pincode` VARCHAR(100))
BEGIN
DECLARE tablecount INT;
DECLARE usercount INT;
UPDATE `driver` SET `first_name`=name,`last_name`=lname,`father_name`=father,`city`=cityname,`email`=emaild,`address`=dadds,`office_address`=doffadds,`contact_no`=dphone,`pan_card_no`=dpannos,`driving_license_no`=dlicence,`vehicle_reg_no`=dvehicleno,`total_fleet_no`=dfleet,`vehicle_mode`=dmodel,`licence_state`=dlstate,`eye_test`=eyesite,`zone`=d_zone,`security_amt`=dsmount,`route_know`=d_route,`pref_location`=d_pref,`week_off`=doff,`imei`=dimei,`gps`=dgps,`lang_write`=dlang_w,`lang_speak`=dlan_s,`pref_timing`=pref_t,`is_active`=dactivetion,`receive_airport_trns`=reci_port,`accept_cash`=dacc_cash,`signup_comment`=dsigupC,`internal_comment`=dinternalC, `dob`=driveBirth, `ac_nonac`=d_ac_nonac,`operational_company_name`=operational_company_name,`vehicle_owned`=vehicle_owned,`vehicle_owner_name`=vehicle_owner_name,`vehicle_owner_cnt_no`=vehicle_owner_cnt_no,`vehicle_owner_alt_cnt_no`=vehicle_owner_alt_cnt_no,`license_validity`=license_validity,`insurance_validity`=insurance_validity,`rc_no`=rc_no,`company_pincode`=com_pincode WHERE `UID`=dids;
SELECT ROW_COUNT() INTO tablecount;
UPDATE `user` SET `login_name`=uname,`is_verified`=dverifyE WHERE `id`=dids;
SELECT ROW_COUNT() as tablecountuser, tablecount;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_a_driver_vehicle` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_a_driver_vehicle`(IN `vids` INT)
BEGIN
SELECT a.license_plate,a.name,a.id FROM `cab_list_mgmt` a INNER JOIN `driver` b ON a.id=b.vehicleId INNER JOIN `vendor` c ON c.id=b.vendor_id WHERE c.UID=vids;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_a_edit_vehicle` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_a_edit_vehicle`(IN `vehicle_type` INT, IN `vehicle_model` VARCHAR(50), IN `vehicle_externalref` VARCHAR(200), IN `vehicle_year` INT, IN `vehicle_licenseplate` VARCHAR(50), IN `vehicle_color` VARCHAR(50), IN `vehicle_engine_no` VARCHAR(50), IN `vehicle_fuel_type` VARCHAR(50), IN `vehicle_capacity` INT, IN `vehicle_is_active` INT, IN `vehicleId` INT)
    NO SQL
BEGIN
UPDATE cab_list_mgmt SET CabType = vehicle_type,VehicleModel = vehicle_model,external_reference = vehicle_externalref,year = vehicle_year,	license_plate=vehicle_licenseplate,colour = vehicle_color,Engine_No = vehicle_engine_no, fuelType = vehicle_fuel_type,capacity = vehicle_capacity,status = vehicle_is_active WHERE id = vehicleId;
SELECT ROW_COUNT() as affectedrow ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_a_edit_vehicle_type` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_a_edit_vehicle_type`(IN `cabTypeName` VARCHAR(50), IN `cityId` INT, IN `cabTypeId` INT)
    NO SQL
BEGIN
Update booking_bill SET CabName = cabTypeName,CityId = cityId where Id = cabTypeId;
SELECT ROW_COUNT() as affectedrow ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_a_enquiry_type_info_by_id` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_a_enquiry_type_info_by_id`(IN `enquiry_id_i` INT)
BEGIN
SELECT * FROM enquiry_type WHERE id = enquiry_id_i;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_a_fair_manage_for_basic` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_a_fair_manage_for_basic`(IN `taxText` FLOAT, IN `taxType` VARCHAR(50), IN `rounding` VARCHAR(50), IN `leveli` VARCHAR(50), IN `Direction` VARCHAR(50), IN `cabTypeId` INT, IN `BookingTypeId` INT, IN `RecId` INT, IN `CancelFee` VARCHAR(500), IN `hourlyRate` VARCHAR(500), IN `prewaitingRate` VARCHAR(500), IN `krishikalyanText` FLOAT, IN `swachhbharatText` FLOAT)
    NO SQL
BEGIN
UPDATE booking_bill SET basic_tax = taxText,
								  basic_tax_type = taxType,
								  rounding = rounding,
								  `level` = leveli,
								  direction = Direction,
                                  cancellation_fees = CancelFee,
                                  waitingfee_upto_minutes = hourlyRate,
                                  prewaitingfee_upto_minutes=prewaitingRate,
								  krishikalyan_tax = krishikalyanText,
								  swachhbharat_tax = swachhbharatText
WHERE Id = RecId;
UPDATE cab_list_mgmt SET UpdatedStatus = 0, 
								  basic_tax = taxText,
								  basic_tax_type = taxType,
								  rounding = rounding,
								  `level` = leveli,
								  direction = Direction,
                                  cancellation_fees = CancelFee,
                                  waitingfee_upto_minutes = hourlyRate,
                                  prewaitingfee_upto_minutes=prewaitingRate,
								  krishikalyan_tax = krishikalyanText,
								  swachhbharat_tax = swachhbharatText
WHERE BookingType = BookingTypeId AND CabType = cabTypeId;
SELECT ROW_COUNT() as affectedrow, BookingTypeId, cabTypeId, RecId, taxText, taxType, rounding, leveli, Direction, cancellation_fees;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_a_fair_manage_for_basic_status` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_a_fair_manage_for_basic_status`(IN `taxText` FLOAT, IN `taxType` VARCHAR(50), IN `rounding` VARCHAR(50), IN `leveli` VARCHAR(50), IN `Direction` VARCHAR(50), IN `cabTypeId` INT, IN `BookingTypeId` INT, IN `RecId` INT, IN `CancelFee` VARCHAR(500), IN `hourlyRate` VARCHAR(500), IN `prewaitingRate` VARCHAR(500), IN `krishikalyanText` FLOAT, IN `swachhbharatText` FLOAT)
BEGIN
UPDATE booking_bill SET basic_tax = taxText,
								  basic_tax_type = taxType,
								  rounding = rounding,
								  `level` = leveli,
								  direction = Direction,
                                  cancellation_fees = CancelFee,
                                  waitingfee_upto_minutes = hourlyRate,
                                  prewaitingfee_upto_minutes=prewaitingRate,
								  krishikalyan_tax = krishikalyanText,
								  swachhbharat_tax = swachhbharatText
WHERE Id = RecId;
UPDATE cab_list_mgmt SET basic_tax = taxText,
								  basic_tax_type = taxType,
								  rounding = rounding,
								  `level` = leveli,
								  direction = Direction,
                                  cancellation_fees = CancelFee,
                                  waitingfee_upto_minutes = hourlyRate,
                                  prewaitingfee_upto_minutes=prewaitingRate,
								  krishikalyan_tax = krishikalyanText,
								  swachhbharat_tax = swachhbharatText
WHERE BookingType = BookingTypeId AND CabType = cabTypeId AND UpdatedStatus = 0;
SELECT ROW_COUNT() as affectedrow, BookingTypeId, cabTypeId, RecId, taxText, taxType, rounding, leveli, Direction;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_a_fair_manage_for_basic_vehicle` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_a_fair_manage_for_basic_vehicle`(IN `taxText` FLOAT, IN `taxType` VARCHAR(50), IN `rounding` VARCHAR(50), IN `leveli` VARCHAR(50), IN `Direction` VARCHAR(50), IN `vehicleId` INT)
    NO SQL
BEGIN
UPDATE cab_list_mgmt SET basic_tax = taxText,basic_tax_type = taxType,rounding = rounding,level = leveli,direction = Direction WHERE Id = vehicleId;
SELECT ROW_COUNT() as affectedrow ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_a_fair_manage_for_distance` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_a_fair_manage_for_distance`(IN `rate_per_km` INT, IN `first_km_rate` INT, IN `vehicle_minmum_distance` INT, IN `vehicle_minmum_fare` INT, IN `CancelFee` VARCHAR(500), IN `rate_upto_distance` VARCHAR(500), IN `RecId` INT, IN `BookingTypeId` INT, IN `cabTypeId` INT, IN `roundupkm_i` VARCHAR(50), IN `accum_instance_i` VARCHAR(50), IN `package_info_distance` TEXT)
    NO SQL
BEGIN
UPDATE booking_bill SET Per_Km_Charge=rate_per_km,first_km_rate = first_km_rate,Min_Distance = vehicle_minmum_distance, MinimumCharge = vehicle_minmum_fare,rate_upto_distance= rate_upto_distance, round_up_km = roundupkm_i, accumulated_instance = accum_instance_i,package_info_distance=package_info_distance  
WHERE Id = RecId;
UPDATE cab_list_mgmt SET UpdatedStatus = 0, Per_Km_Charge=rate_per_km,first_km_rate = first_km_rate,Min_Distance = vehicle_minmum_distance, MinimumCharge = vehicle_minmum_fare, rate_upto_distance= rate_upto_distance, round_up_km = roundupkm_i, accumulated_instance = accum_instance_i,package_info_distance=package_info_distance 
WHERE CabType = cabTypeId AND BookingType = BookingTypeId;
SELECT ROW_COUNT() as affectedrow ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_a_fair_manage_for_distance_status` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_a_fair_manage_for_distance_status`(IN `rate_per_km` INT, IN `irst_km_rate` INT, IN `vehicle_minmum_distance` INT, IN `vehicle_minmum_fare` INT, IN `CancelFee` VARCHAR(500), IN `rate_upto_distance` VARCHAR(500), IN `RecId` INT, IN `BookingTypeId` INT, IN `cabTypeId` INT, IN `roundupkm_i` VARCHAR(50), IN `accum_instance_i` VARCHAR(50), IN `package_info_distance` TEXT)
BEGIN
UPDATE booking_bill SET Per_Km_Charge=rate_per_km,first_km_rate = first_km_rate,Min_Distance = vehicle_minmum_distance, MinimumCharge = vehicle_minmum_fare, rate_upto_distance= rate_upto_distance, round_up_km = roundupkm_i, accumulated_instance = accum_instance_i,package_info_distance=package_info_distance  
WHERE Id = RecId;
UPDATE cab_list_mgmt SET Per_Km_Charge=rate_per_km,first_km_rate = first_km_rate,Min_Distance = vehicle_minmum_distance, MinimumCharge = vehicle_minmum_fare, rate_upto_distance= rate_upto_distance, round_up_km = roundupkm_i, accumulated_instance = accum_instance_i,package_info_distance=package_info_distance 
WHERE CabType = cabTypeId AND BookingType = BookingTypeId AND UpdatedStatus = 0;
SELECT ROW_COUNT() as affectedrow ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_a_fair_manage_for_distance_vehicle` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_a_fair_manage_for_distance_vehicle`(IN `rate_per_km` INT, IN `first_km_rate` INT, IN `vehicle_minmum_distance` INT, IN `vehicle_minmum_fare` INT, IN `CancelFee` VARCHAR(500), IN `rate_upto_distance` VARCHAR(500), IN `vehicleId` INT, IN `roundupkm_val_i` VARCHAR(50), IN `accum_instance_val_i` VARCHAR(50))
    NO SQL
BEGIN
UPDATE cab_list_mgmt SET Per_Km_Charge=rate_per_km,first_km_rate = first_km_rate,Min_Distance = vehicle_minmum_distance, MinimumCharge = vehicle_minmum_fare,cancellation_fees = CancelFee, rate_upto_distance= rate_upto_distance, round_up_km = roundupkm_val_i, accumulated_instance = accum_instance_val_i  WHERE Id = vehicleId;
SELECT ROW_COUNT() as affectedrow ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_a_fair_manage_for_fixed_route` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_a_fair_manage_for_fixed_route`(IN `frequent_location` VARCHAR(500), IN `postCodeArr` VARCHAR(500), IN `RecId` INT, IN `BookingTypeId` INT, IN `cabTypeId` INT)
    NO SQL
BEGIN
	UPDATE booking_bill 
	SET frequent_location=frequent_location,postcode_to_postcode_fair=postCodeArr 
	WHERE Id = RecId;
	UPDATE cab_list_mgmt 
	SET UpdatedStatus = 0,frequent_location=frequent_location,postcode_to_postcode_fair=postCodeArr 
	WHERE CabType = cabTypeId AND BookingType = BookingTypeId;
	SELECT ROW_COUNT() as affectedrow ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_a_fair_manage_for_fixed_route_status` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_a_fair_manage_for_fixed_route_status`(IN `frequent_location` VARCHAR(500), IN `postCodeArr` VARCHAR(500), IN `RecId` INT, IN `BookingTypeId` INT, IN `cabTypeId` INT)
BEGIN
	UPDATE booking_bill 
	SET frequent_location=frequent_location,postcode_to_postcode_fair=postCodeArr 
	WHERE Id = RecId;
	UPDATE cab_list_mgmt 
	SET frequent_location=frequent_location,postcode_to_postcode_fair=postCodeArr 
	WHERE CabType = cabTypeId AND BookingType = BookingTypeId AND UpdatedStatus = 0;
	SELECT ROW_COUNT() as affectedrow ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_a_fair_manage_for_fixed_route_vehicle` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_a_fair_manage_for_fixed_route_vehicle`(IN `frequent_location` VARCHAR(500), IN `postCodeArr` VARCHAR(500), IN `vehicleId` INT)
    NO SQL
BEGIN
UPDATE cab_list_mgmt SET frequent_location=frequent_location,
								  postcode_to_postcode_fair=postCodeArr,
								  UpdatedStatus = 1 
WHERE Id = vehicleId;
SELECT ROW_COUNT() as affectedrow;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_a_fair_manage_for_premium` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_a_fair_manage_for_premium`(IN `nightChrge` VARCHAR(50), IN `nightChStrtTime` VARCHAR(50), IN `nightChEndTime` VARCHAR(50), IN `extraArray` VARCHAR(500), IN `premiumofVehicle` VARCHAR(50), IN `RecId` INT, IN `BookingTypeId` INT, IN `cabTypeId` INT, IN `night_charge_unit` VARCHAR(50), IN `premiumofVehicleUnit` VARCHAR(50))
    NO SQL
BEGIN
UPDATE booking_bill 
SET NightCharges = nightChrge,nightCharge_unit=night_charge_unit,night_rate_begins =  nightChStrtTime,night_rate_ends = nightChEndTime,extras = extraArray,premiums=premiumofVehicle,premiums_unit=premiumofVehicleUnit
WHERE Id = RecId;
UPDATE cab_list_mgmt 
SET UpdatedStatus = 0, NightCharges = nightChrge,nightCharge_unit=night_charge_unit,night_rate_begins =  nightChStrtTime,night_rate_ends = nightChEndTime,extras = extraArray,premiums=premiumofVehicle,premiums_unit=premiumofVehicleUnit 
WHERE CabType = cabTypeId AND BookingType = BookingTypeId;
SELECT ROW_COUNT() as affectedrow ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_a_fair_manage_for_premium_status` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_a_fair_manage_for_premium_status`(IN `nightChrge` VARCHAR(50), IN `nightChStrtTime` VARCHAR(50), IN `nightChEndTime` VARCHAR(50), IN `extraArray` VARCHAR(500), IN `premiumofVehicle` VARCHAR(50), IN `RecId` INT, IN `BookingTypeId` INT, IN `cabTypeId` INT, IN `night_charge_unit` VARCHAR(50), IN `premiumofVehicleUnit` VARCHAR(50))
BEGIN
UPDATE booking_bill 
SET NightCharges = nightChrge,nightCharge_unit=night_charge_unit,night_rate_begins =  nightChStrtTime,night_rate_ends = nightChEndTime,extras = extraArray,premiums=premiumofVehicle,premiums_unit=premiumofVehicleUnit 
WHERE Id = RecId;
UPDATE cab_list_mgmt 
SET NightCharges = nightChrge,nightCharge_unit=night_charge_unit,night_rate_begins =  nightChStrtTime,night_rate_ends = nightChEndTime,extras = extraArray,premiums=premiumofVehicle,premiums_unit=premiumofVehicleUnit 
WHERE CabType = cabTypeId AND BookingType = BookingTypeId AND UpdatedStatus = 0;
SELECT ROW_COUNT() as affectedrow ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_a_fair_manage_for_premium_vehicle` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_a_fair_manage_for_premium_vehicle`(IN `nightChrge` VARCHAR(50), IN `nightChStrtTime` VARCHAR(50), IN `nightChEndTime` VARCHAR(50), IN `extraArray` VARCHAR(500), IN `premium` VARCHAR(50), IN `vehicleId` INT, IN `night_charge_unit` VARCHAR(50))
    NO SQL
BEGIN
UPDATE cab_list_mgmt SET NightCharges = nightChrge,nightCharge_unit=night_charge_unit,night_rate_begins =  nightChStrtTime,night_rate_ends = nightChEndTime,extras = extraArray,premiums=premium WHERE Id = vehicleId;
SELECT ROW_COUNT() as affectedrow ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_a_fair_manage_for_vehicle_type` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_a_fair_manage_for_vehicle_type`(IN `taxText` FLOAT, IN `rounding` VARCHAR(50), IN `leveli` VARCHAR(50), IN `Direction` VARCHAR(50), IN `rate_per_km` INT, IN `first_km_rate` INT, IN `vehicle_minmum_distance` INT, IN `vehicle_minmum_fare` INT, IN `CancelFee` VARCHAR(50), IN `nightChrge` VARCHAR(50), IN `nightChStrtTime` VARCHAR(50), IN `nightChEndTime` VARCHAR(50), IN `vehicle_rate_hour` INT, IN `rate_upto_distance` VARCHAR(500), IN `extraArray` VARCHAR(500), IN `hourlyRate` VARCHAR(500), IN `RecId` INT, IN `BookingTypeId` INT, IN `cabTypeId` INT, IN `premium` VARCHAR(50), IN `frequent_location` VARCHAR(500), IN `postCodeArr` VARCHAR(500), IN `vehicle_ignore_hour` INT, IN `vehicle_hour_minimum_fare` INT, IN `package_info_hourly` TEXT)
    NO SQL
BEGIN
UPDATE booking_bill SET basic_tax = taxText,rounding = rounding,level = leveli,direction = Direction,	Per_Km_Charge=rate_per_km,first_km_rate = first_km_rate,Min_Distance = vehicle_minmum_distance, MinimumCharge = vehicle_minmum_fare,cancellation_fees = CancelFee,NightCharges = nightChrge,night_rate_begins =  nightChStrtTime,night_rate_ends = nightChEndTime,	tripCharge_per_minute = vehicle_rate_hour, rate_upto_distance= rate_upto_distance,extras = extraArray,waitingfee_upto_minutes = hourlyRate,premiums=premium,frequent_location=frequent_location,postcode_to_postcode_fair=postCodeArr ,ignore_first_hrs = vehicle_ignore_hour,minimum_hourly_Charge = vehicle_hour_minimum_fare,package_info_hourly=package_info_hourly
WHERE Id = RecId;
UPDATE cab_list_mgmt SET UpdatedStatus = 0, basic_tax = taxText,rounding = rounding,level = leveli,direction = Direction,	Per_Km_Charge=rate_per_km,first_km_rate = first_km_rate,Min_Distance = vehicle_minmum_distance, MinimumCharge = vehicle_minmum_fare,cancellation_fees = CancelFee,NightCharges = nightChrge,night_rate_begins =  nightChStrtTime,night_rate_ends = nightChEndTime,	tripCharge_per_minute = vehicle_rate_hour, rate_upto_distance= rate_upto_distance,extras = extraArray,waitingfee_upto_minutes = hourlyRate,premiums=premium,frequent_location=frequent_location,postcode_to_postcode_fair=postCodeArr ,ignore_first_hrs = vehicle_ignore_hour,minimum_hourly_Charge = vehicle_hour_minimum_fare,package_info_hourly=package_info_hourly
WHERE Cabtype = cabTypeId AND BookingType = BookingTypeId;
SELECT ROW_COUNT() as affectedrow ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_a_fair_manage_for_vehicle_type_status` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_a_fair_manage_for_vehicle_type_status`(IN `taxText` FLOAT, IN `rounding` VARCHAR(50), IN `leveli` VARCHAR(50), IN `Direction` VARCHAR(50), IN `rate_per_km` INT, IN `first_km_rate` INT, IN `vehicle_minmum_distance` INT, IN `vehicle_minmum_fare` INT, IN `CancelFee` VARCHAR(50), IN `nightChrge` VARCHAR(50), IN `nightChStrtTime` VARCHAR(50), IN `nightChEndTime` VARCHAR(50), IN `vehicle_rate_hour` INT, IN `rate_upto_distance` TEXT, IN `extraArray` TEXT, IN `hourlyRate` TEXT, IN `RecId` INT, IN `BookingTypeId` INT, IN `cabTypeId` INT, IN `premium` VARCHAR(50), IN `frequent_location` VARCHAR(500), IN `postCodeArr` VARCHAR(50), IN `vehicle_ignore_hour` INT, IN `vehicle_hour_minimum_fare` INT, IN `package_info_hourly` TEXT)
BEGIN
UPDATE booking_bill SET basic_tax = taxText,rounding = rounding,level = leveli,direction = Direction,	Per_Km_Charge=rate_per_km,first_km_rate = first_km_rate,Min_Distance = vehicle_minmum_distance, MinimumCharge = vehicle_minmum_fare,cancellation_fees = CancelFee,NightCharges = nightChrge,night_rate_begins =  nightChStrtTime,night_rate_ends = nightChEndTime,	tripCharge_per_minute = vehicle_rate_hour, rate_upto_distance= rate_upto_distance,extras = extraArray,waitingfee_upto_minutes = hourlyRate,premiums=premium,frequent_location=frequent_location,postcode_to_postcode_fair=postCodeArr, ignore_first_hrs = vehicle_ignore_hour, minimum_hourly_Charge = vehicle_hour_minimum_fare,package_info_hourly=package_info_hourly
WHERE Id = RecId;
UPDATE cab_list_mgmt SET basic_tax = taxText,rounding = rounding,level = leveli,direction = Direction,	Per_Km_Charge=rate_per_km,first_km_rate = first_km_rate,Min_Distance = vehicle_minmum_distance, MinimumCharge = vehicle_minmum_fare,cancellation_fees = CancelFee,NightCharges = nightChrge,night_rate_begins =  nightChStrtTime,night_rate_ends = nightChEndTime,	tripCharge_per_minute = vehicle_rate_hour, rate_upto_distance= rate_upto_distance,extras = extraArray,waitingfee_upto_minutes = hourlyRate,premiums=premium,frequent_location=frequent_location,postcode_to_postcode_fair=postCodeArr, ignore_first_hrs = vehicle_ignore_hour, minimum_hourly_Charge = vehicle_hour_minimum_fare,package_info_hourly=package_info_hourly
WHERE Cabtype = cabTypeId AND BookingType = BookingTypeId AND UpdatedStatus = 0;
SELECT ROW_COUNT() as affectedrow ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_a_fetch_cab_package` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_a_fetch_cab_package`()
BEGIN
DECLARE localPackage varchar(50);
DECLARE pointToPointPackage varchar(50);
DECLARE airportPackage varchar(50);
DECLARE outstationPackage varchar(50);
DECLARE driverShare varchar(50);
DECLARE companyShare varchar(50);
DECLARE partnerShare varchar(50);
SELECT tblsubpackage.Sub_Package_Name INTO localPackage FROM tblsubpackage INNER JOIN master_package ON tblsubpackage.Sub_Package_Id = master_package.Sub_Package_Id where master_package.Master_Package = 'Local';
SELECT tblsubpackage.Sub_Package_Name INTO pointToPointPackage FROM tblsubpackage INNER JOIN master_package ON tblsubpackage.Sub_Package_Id = master_package.Sub_Package_Id where master_package.Master_Package = 'Point To Point';
SELECT tblsubpackage.Sub_Package_Name INTO airportPackage FROM tblsubpackage INNER JOIN master_package ON tblsubpackage.Sub_Package_Id = master_package.Sub_Package_Id where master_package.Master_Package = 'Airport Terminal';
SELECT tblsubpackage.Sub_Package_Name INTO outstationPackage FROM tblsubpackage INNER JOIN master_package ON tblsubpackage.Sub_Package_Id = master_package.Sub_Package_Id where master_package.Master_Package = 'Outstation';
SELECT client.driver_share , client.company_share , client.partner_share INTO driverShare,companyShare,partnerShare FROM client where client.clientName = 'Hello42';
SELECT localPackage,pointToPointPackage,airportPackage,outstationPackage,driverShare,companyShare,partnerShare;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_a_get_all_action_type` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_a_get_all_action_type`()
BEGIN
SELECT * FROM tblactiontype order by id desc;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_a_get_all_cities` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_a_get_all_cities`(IN `stateId` VARCHAR(50))
    NO SQL
BEGIN
select cities.id,cities.city_name,states.id as stateId from cities INNER JOIN states ON states.id = cities.state_id where states.state = stateId;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_a_get_all_complaint` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_a_get_all_complaint`()
BEGIN
SELECT sub_complaint.id, enquiry_type.enquiry_type, sub_complaint.sub_complaint, sub_complaint.created_by, sub_complaint.date, sub_complaint.updated_by, sub_complaint.updated_on FROM enquiry_type INNER JOIN sub_complaint ON enquiry_type.id = sub_complaint.complaint_type;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_a_get_all_country` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_a_get_all_country`()
    NO SQL
BEGIN
select id, country_name from country;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_a_get_all_enquiry` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_a_get_all_enquiry`()
BEGIN
SELECT * FROM enquiry order by Enquirydate desc;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_a_get_all_enquiry_type` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_a_get_all_enquiry_type`()
BEGIN
SELECT * FROM enquiry_type;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_a_get_all_state` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_a_get_all_state`(IN `countryName` VARCHAR(50))
    NO SQL
BEGIN
select states.state,states.id from states INNER JOIN country ON states.country_code = country.id where country.country_name = countryName;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_a_get_all_vehicle_type` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_a_get_all_vehicle_type`(IN `city` VARCHAR(50), IN `state` VARCHAR(50))
    NO SQL
BEGIN
SELECT booking_bill.Id, 
		 master_package.master_package AS FormName, 
		 master_package.package_id AS BookingTypeId,
       master_package.sub_package_id AS RateCatId, 
       sub_package.sub_package_name AS CalculateBy, 
		 vehicle_type.vehicle_type AS CabName,
		 vehicle_type.id AS CabTypeId,
		 country.country_name,
		 states.state,
		 cities.city_name,
         cities.id AS CityId,
		 booking_bill.Per_Km_Charge,
		 booking_bill.rate_per_km_dh,
		 booking_bill.rate_per_km_dw,
		 booking_bill.tripCharge_per_minute,
		 booking_bill.rate_per_hour_dh, 
		 booking_bill.MinimumCharge,
		 booking_bill.minimum_fare_dh,
		 booking_bill.minimum_fare_dw
FROM booking_bill 
INNER JOIN master_package ON booking_bill.BookingTypeId = master_package.package_id 
INNER JOIN sub_package ON master_package.sub_package_id = sub_package.id 
INNER JOIN cities ON booking_bill.CityId = cities.state_id 
INNER JOIN states ON cities.state_id = states.id 
INNER JOIN country ON country.id = states.country_code 
INNER JOIN vehicle_type ON booking_bill.CabType = vehicle_type.id 
WHERE states.state = state AND cities.city_name = city AND master_package.package_id IN (101,102,103,104,105) 
ORDER BY booking_bill.BookingTypeId, booking_bill.Cabtype ASC ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_a_get_status` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_a_get_status`()
BEGIN
SELECT * FROM status;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_a_get_vehicle_info` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_a_get_vehicle_info`(IN `BookingTypeId` INT, IN `cabId` INT, IN `city` VARCHAR(50))
    NO SQL
BEGIN
select 
cab_list_mgmt.id,
cab_list_mgmt.BookingType AS BookingTypeId,
cab_master.CabName, 
cab_master.CabManufacturer, 
cab_master.CabModel, 
cab_master.CabRegistrationNumber, 
cab_list_mgmt.name,
vehicle_type.CabType,
cab_list_mgmt.capacity,
cab_list_mgmt.no_of_cabs,
cab_list_mgmt.VehicleModel,
cab_list_mgmt.Registration_Date,
cab_list_mgmt.Engine_No,
cab_list_mgmt.vehicleNumber,
cab_list_mgmt.status,
cab_list_mgmt.fuelType,
cab_list_mgmt.Manufacturer,
master_package.Sub_Package_Id AS RateCatId,
cab_list_mgmt.Per_Km_Charge,
cab_list_mgmt.MinimumCharge,
cab_list_mgmt.tripCharge_per_minute,
cab_list_mgmt.rate_per_km_dh,
cab_list_mgmt.rate_per_hour_dh,
cab_list_mgmt.minimum_fare_dh,
cab_list_mgmt.rate_per_km_dw,
cab_list_mgmt.minimum_fare_dw,
driver.UID AS DriverId,
concat(driver.FirstName,' ',driver.LastName) As DriverName 
from cab_list_mgmt 
INNER JOIN cab_master ON cab_list_mgmt.CabId = cab_master.CabId  
INNER JOIN driver ON cab_master.CabId = driver.vehicleId
INNER JOIN tblcity ON cab_list_mgmt.cityId = tblcity.id 
INNER JOIN vehicle_type ON vehicle_type.Id = cab_list_mgmt.CabType 
INNER JOIN master_package ON cab_list_mgmt.BookingType = master_package.Package_Id
WHERE cab_list_mgmt.BookingType = BookingTypeId AND cab_list_mgmt.CabType = cabId AND tblcity.name = city;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_a_get_vehicle_info_by_id` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_a_get_vehicle_info_by_id`(IN `vehicleId` INT)
    NO SQL
BEGIN 
SELECT * FROM cab_list_mgmt WHERE id = vehicleId;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_a_get_vehicle_type_info_by_id` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_a_get_vehicle_type_info_by_id`(IN `RowId` INT)
    NO SQL
BEGIN 
SELECT booking_bill.*, master_package.Sub_Package_Id FROM booking_bill
INNER JOIN master_package ON booking_bill.BookingTypeId=master_package.Package_Id 
 WHERE booking_bill.Id = RowId;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_a_group` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_a_group`()
BEGIN
SELECT vendor.UID,
		 vendor.group_name,
		 vendor.vendor_name 
FROM vendor 
INNER JOIN driver ON vendor.id=driver.vendor_id 
WHERE vendor.`status`='1' 
GROUP BY vendor.id ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_a_group_delete` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_a_group_delete`(IN `groupids` INT)
BEGIN
UPDATE `vendor` SET `status`='0' WHERE `UID`=groupids;
SELECT ROW_COUNT() as del;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_a_group_edit` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_a_group_edit`(IN `groupId` INT)
BEGIN
SELECT * FROM `vendor` WHERE `UID` = groupId;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_a_group_update` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_a_group_update`(IN `groupId` INT, IN `groupName` VARCHAR(50), IN `groupBoss` VARCHAR(50))
BEGIN
UPDATE `vendor` SET `group_name`=groupName,`vendor_name`=groupBoss WHERE `UID` = groupId;
SELECT ROW_COUNT() as aftrow;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_a_insert_action_type` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_a_insert_action_type`(IN `action_type_i` VARCHAR(50), IN `action_type_status_i` VARCHAR(50), IN `action_type_created_by_i` VARCHAR(50))
BEGIN
DECLARE actionType_exists int;
SELECT COUNT(*) INTO actionType_exists FROM tblactiontype WHERE `action` = action_type_i ;
IF actionType_exists =0 THEN
INSERT INTO tblactiontype (`action`,`created_by`,`created_on`,`status`) values (`action_type_i`,`action_type_created_by_i`,NOW(),`action_type_status_i`);
END IF;
SELECT actionType_exists;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_a_insert_enquiry_type` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_a_insert_enquiry_type`(IN `enquiry_type_i` VARCHAR(50), IN `enquiry_type_status` VARCHAR(50), IN `enquiry_type_created_by` VARCHAR(50))
BEGIN
DECLARE enquiryType_exists int;
SELECT COUNT(*) INTO enquiryType_exists FROM enquiry_type WHERE `enquiry_type` = enquiry_type_i ;
IF enquiryType_exists =0 THEN
INSERT INTO enquiry_type (`enquiry_type`,`created_by`,`created_on`,`status`) values (`enquiry_type_i`,`enquiry_type_created_by`,NOW(),`enquiry_type_status`);
END IF;
SELECT enquiryType_exists;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_a_rate_card` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_a_rate_card`(IN `book_ref` INT)
BEGIN
SELECT cab_booking.CarType, cab_booking.ID, booking_bill.MinimumCharge, booking_bill.Min_Distance, booking_bill.Per_Km_Charge, booking_bill.waitingfee_upto_minutes from cab_booking inner join booking_bill on cab_booking.CarType = booking_bill.CabType where cab_booking.ID = book_ref;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_a_save_distance_hour` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_a_save_distance_hour`(IN `dh_rate_per_km` INT, IN `dh_minmum_distance` INT, IN `dh_minmum_fare` INT, IN `dh_rate_per_hour` INT, IN `RecId` INT, IN `BookingTypeId` INT, IN `cabTypeId` INT, IN `dh_ignore_first_hrs` INT, IN `package_info_distance_hourly` TEXT)
BEGIN
UPDATE booking_bill SET rate_per_km_dh = dh_rate_per_km, minimum_distance_dh = dh_minmum_distance, minimum_fare_dh = dh_minmum_fare, rate_per_hour_dh = dh_rate_per_hour, ignore_first_hrs_dh=dh_ignore_first_hrs,package_info_distance_hourly=package_info_distance_hourly  
WHERE Id = RecId;
UPDATE cab_list_mgmt SET UpdatedStatus = 0, rate_per_km_dh = dh_rate_per_km, minimum_distance_dh = dh_minmum_distance, minimum_fare_dh = dh_minmum_fare, rate_per_hour_dh = dh_rate_per_hour, ignore_first_hrs_dh=dh_ignore_first_hrs,package_info_distance_hourly=package_info_distance_hourly  
WHERE CabType = cabTypeId AND BookingType = BookingTypeId;
SELECT ROW_COUNT() as affectedrow ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_a_save_distance_hour_status` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_a_save_distance_hour_status`(IN `dh_rate_per_km` INT, IN `dh_minmum_distance` INT, IN `dh_minmum_fare` INT, IN `dh_rate_per_hour` INT, IN `RecId` INT, IN `BookingTypeId` INT, IN `cabTypeId` INT, IN `dh_ignore_first_hrs` INT, IN `package_info_distance_hourly` TEXT)
BEGIN
UPDATE booking_bill SET rate_per_km_dh = dh_rate_per_km, minimum_distance_dh = dh_minmum_distance, minimum_fare_dh = dh_minmum_fare, rate_per_hour_dh = dh_rate_per_hour,
ignore_first_hrs_dh=dh_ignore_first_hrs,package_info_distance_hourly=package_info_distance_hourly
WHERE Id = RecId;
UPDATE cab_list_mgmt SET rate_per_km_dh = dh_rate_per_km, minimum_distance_dh = dh_minmum_distance, minimum_fare_dh = dh_minmum_fare, rate_per_hour_dh = dh_rate_per_hour,
ignore_first_hrs_dh=dh_ignore_first_hrs,package_info_distance_hourly=package_info_distance_hourly
WHERE CabType = cabTypeId AND BookingType = BookingTypeId AND UpdatedStatus = 0;
SELECT ROW_COUNT() as affectedrow ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_a_save_distance_hour_vehicle` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_a_save_distance_hour_vehicle`(IN `dh_rate_per_km` INT, IN `dh_minmum_distance` INT, IN `dh_minmum_fare` INT, IN `dh_rate_per_hour` INT, IN `vehicleId` INT)
BEGIN
UPDATE cab_list_mgmt SET rate_per_km_dh = dh_rate_per_km, minimum_distance_dh = dh_minmum_distance, minimum_fare_dh = dh_minmum_fare, rate_per_hour_dh = dh_rate_per_hour  WHERE id = vehicleId;
SELECT ROW_COUNT() as affectedrow ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_a_save_distance_waiting` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_a_save_distance_waiting`(IN `dw_rate_per_km` INT, IN `dw_minmum_distance` INT, IN `dw_minmum_fare` INT, IN `waiting_fees_per_min` VARCHAR(50), IN `RecId` INT, IN `BookingTypeId` INT, IN `cabTypeId` INT, IN `package_info_distance_waiting` TEXT)
BEGIN
UPDATE booking_bill 
SET rate_per_km_dw = dw_rate_per_km, minimum_distance_dw = dw_minmum_distance, minimum_fare_dw = dw_minmum_fare, waiting_fees = waiting_fees_per_min,package_info_distance_waiting=package_info_distance_waiting  
WHERE Id = RecId;
UPDATE cab_list_mgmt 
SET UpdatedStatus = 0, rate_per_km_dw = dw_rate_per_km, minimum_distance_dw = dw_minmum_distance, minimum_fare_dw = dw_minmum_fare, waiting_fees = waiting_fees_per_min, package_info_distance_waiting=package_info_distance_waiting  
WHERE CabType = cabTypeId AND BookingType = BookingTypeId;
SELECT ROW_COUNT() as affectedrow ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_a_save_distance_waiting_status` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_a_save_distance_waiting_status`(IN `dw_rate_per_km` INT, IN `dw_minmum_distance` INT, IN `dw_minmum_fare` INT, IN `waiting_fees_per_min` VARCHAR(50), IN `RecId` INT, IN `BookingTypeId` INT, IN `cabTypeId` INT, IN `package_info_distance_waiting` TEXT)
BEGIN
UPDATE booking_bill 
SET rate_per_km_dw = dw_rate_per_km, minimum_distance_dw = dw_minmum_distance, minimum_fare_dw = dw_minmum_fare, waiting_fees = waiting_fees_per_min,package_info_distance_waiting=package_info_distance_waiting  
WHERE Id = RecId;
UPDATE cab_list_mgmt 
SET rate_per_km_dw = dw_rate_per_km, minimum_distance_dw = dw_minmum_distance, minimum_fare_dw = dw_minmum_fare, waiting_fees = waiting_fees_per_min,package_info_distance_waiting=package_info_distance_waiting  
WHERE CabType = cabTypeId AND BookingType = BookingTypeId AND UpdatedStatus = 0;
SELECT ROW_COUNT() as affectedrow ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_a_save_distance_waiting_vehicle` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_a_save_distance_waiting_vehicle`(IN `dw_rate_per_km` INT, IN `dw_minmum_distance` INT, IN `dw_minmum_fare` INT, IN `waiting_fees_per_min` VARCHAR(50), IN `vehicleId` INT)
BEGIN
UPDATE cab_list_mgmt SET rate_per_km_dw = dw_rate_per_km, minimum_distance_dw = dw_minmum_distance, minimum_fare_dw = dw_minmum_fare, waiting_fees = waiting_fees_per_min  WHERE id = vehicleId;
SELECT ROW_COUNT() as affectedrow ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_a_save_fair_manage` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_a_save_fair_manage`(IN `taxText` FLOAT, IN `rounding` VARCHAR(50), IN `leveli` VARCHAR(50), IN `Direction` VARCHAR(50), IN `rate_per_km` INT, IN `first_km_rate` INT, IN `vehicle_minmum_distance` INT, IN `vehicle_minmum_fare` INT, IN `CancelFee` VARCHAR(50), IN `nightChrge` VARCHAR(50), IN `nightChStrtTime` VARCHAR(50), IN `nightChEndTime` VARCHAR(50), IN `vehicle_rate_hour` INT, IN `rate_upto_distance` VARCHAR(500), IN `extraArray` VARCHAR(500), IN `hourlyRate` VARCHAR(500), IN `vehicleId` INT, IN `premium` VARCHAR(50), IN `frequent_location` VARCHAR(500), IN `postCodeArr` VARCHAR(500))
    NO SQL
BEGIN
UPDATE cab_list_mgmt SET basic_tax = taxText,rounding = rounding,level = leveli,direction = Direction,	Per_Km_Charge=rate_per_km,first_km_rate = first_km_rate,Min_Distance = vehicle_minmum_distance, MinimumCharge = vehicle_minmum_fare,cancellation_fees = CancelFee,NightCharges = nightChrge,night_rate_begins =  nightChStrtTime,night_rate_ends = nightChEndTime,	tripCharge_per_minute = vehicle_rate_hour, rate_upto_distance= rate_upto_distance,extras = extraArray,waitingfee_upto_minutes = hourlyRate,premiums=premium,frequent_location=frequent_location,postcode_to_postcode_fair=postCodeArr WHERE id = vehicleId;
SELECT ROW_COUNT() as affectedrow ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_a_search_cab_type` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_a_search_cab_type`(IN `city` VARCHAR(50), IN `state` VARCHAR(50), IN `country` VARCHAR(50), IN `vehicleNo` VARCHAR(50), IN `vehicleModel` VARCHAR(50), IN `since_i` DATE, IN `to_i` DATE)
BEGIN
SELECT booking_bill.Id, cab_list_mgmt.Manufacturer, cab_list_mgmt.Registration_Date, booking_bill.CabName,tblcountry.CountryName,
booking_bill.CabType,tblstates.state,tblcity.name,booking_bill.MinimumCharge,booking_bill.Per_Km_Charge,booking_bill.tripCharge_per_minute, 
cab_list_mgmt.VehicleModel, cab_list_mgmt.vehicleNumber FROM booking_bill INNER JOIN tblcity ON booking_bill.CityId = tblcity.id 
INNER JOIN tblstates ON tblcity.state = tblstates.id INNER JOIN tblcountry ON tblcountry.id = tblstates.country_code 
INNER JOIN cab_list_mgmt ON cab_list_mgmt.CabType = booking_bill.CabType 
INNER JOIN driver ON cab_list_mgmt.id = driver.vehicleId
where tblstates.state LIKE concat('%',state,'%') and tblcity.name LIKE concat('%',city,'%') and tblcountry.CountryName LIKE concat('%',country,'%') 
and cab_list_mgmt.VehicleModel LIKE concat('%',vehicleModel,'%') and cab_list_mgmt.vehicleNumber LIKE concat('%',vehicleNo,'%') 
and  driver.created_date BETWEEN since_i AND to_i ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_a_search_complaint_type` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_a_search_complaint_type`(IN `booking_id_i` VARCHAR(50), IN `complaint_type_i` VARCHAR(50), IN `created_since_i` DATETIME, IN `created_to_i` DATETIME, IN `closed_since_i` DATETIME, IN `closed_to_i` DATETIME, IN `status_i` VARCHAR(50))
BEGIN
SELECT complaint.ID as com_id,complaint.StatusID as com_status, cab_booking.CSR_ID as emp,CONCAT(cab_booking.DropAddress,cab_booking.DropArea) as drop_area,cab_booking.ID as id,booking_charges.totalBill, cab_booking.booking_reference as ref,master_package.Master_Package as booking_type,CONCAT(cab_booking.PickupDate," ",cab_booking.PickupTime) as ordertime,concat(client.clientName,'<br>(',appid.`type`,')') as partner,REPLACE(rt_cabmanager_cabs.name,'·','') as vehicle,CONCAT(user_info.FirstName," ",user_info.LastName) as clientname,CONCAT(driver.FirstName," ",driver.LastName) as driver_name, cab_booking.MobileNo as mob_no,user_info.UID as client_id,driver.UID as driver_id,CONCAT(cab_booking.PickupAddress,' ',cab_booking.PickupArea) as departure,driver.VehicleRegistrationNo as reg,cab_status.`status` as `status` FROM cab_booking JOIN cab_status ON cab_booking.`status`=cab_status.`status_id` JOIN user_info ON user_info.UID=cab_booking.ClientID JOIN master_package ON cab_booking.BookingType=master_package.Package_Id LEFT JOIN driver ON cab_booking.pickup=driver.uid LEFT JOIN rt_cabmanager_cabs ON driver.TypeOfvehicle=rt_cabmanager_cabs.id JOIN appid ON appid.id=cab_booking.partner JOIN client ON appid.clientId=client.id LEFT JOIN booking_charges ON cab_booking.ID=booking_charges.BookingID INNER JOIN complaint ON cab_booking.booking_reference=complaint.BookingID WHERE cab_status.`type`='cab' AND cab_booking.booking_reference LIKE concat('%',booking_id_i,'%') AND complaint.ComplaintType LIKE concat('%',complaint_type_i,'%') AND DATE(complaint.ComplaintDate) BETWEEN created_since_i AND created_to_i AND DATE(complaint.updated_date) BETWEEN closed_since_i AND closed_to_i AND complaint.StatusID LIKE concat('%',status_i,'%') order by cab_booking.ID desc;   
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_a_search_driver` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_a_search_driver`(IN `did` VARCHAR(50), IN `vdriver` VARCHAR(50), IN `dname` VARCHAR(50), IN `dext` VARCHAR(50), IN `drivername` VARCHAR(50), IN `extref` VARCHAR(50))
BEGIN
SELECT driver.UID,driver.FirstName,driver.Email,driver.`status`,driver.ContactNo,driver.VehicleRegistrationNo,driver.DrivingLicenceNo,
driver.is_active,active.`status` as approvedStatus,
driver.created_date,cab_master.CabName as vehicleName, ignition_type.CabIgnitionType,
count(pickup) as booking 
FROM driver  
INNER JOIN cab_list_mgmt ON driver.vehicleId = cab_list_mgmt.id 
INNER JOIN cab_master ON driver.vehicleId = cab_master.cabId 
INNER JOIN `ignition_type` ON ignition_type.id = cab_master.CabIgnitionTypeId
LEFT JOIN cab_booking ON driver.UID=cab_booking.pickup 
INNER JOIN user ON driver.UID = user.ID 
INNER JOIN active ON driver.is_active = active.status_id 
LEFT JOIN vendor on driver.vendor_id = vendor.id 
WHERE  driver.UID LIKE CONCAT('%',did,'%') 
AND driver.DrivingLicenceNo LIKE CONCAT('%',vdriver,'%') 
AND driver.FirstName LIKE CONCAT('%',dname,'%') 
AND driver.refname LIKE CONCAT('%',extref,'%') 
AND cab_list_mgmt.external_reference LIKE CONCAT('%',dext,'%') 
group by(UID);
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_a_search_driver_group` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_a_search_driver_group`(IN `group_id` VARCHAR(50), IN `group_name` VARCHAR(50), IN `veh_id` VARCHAR(50), IN `veh_ext_ref` VARCHAR(50), IN `drivar_name` VARCHAR(50), IN `driver_ext_ref` VARCHAR(50))
BEGIN
SELECT vendor.id,vendor.UID,vendor.group_name,vendor.vendor_name FROM vendor INNER JOIN driver ON vendor.id=driver.vendor_id 
INNEr JOIN cab_list_mgmt ON driver.vehicleId = cab_list_mgmt.id
WHERE CASE WHEN group_id = '' THEN 
0=0 ELSE
vendor.id = group_id
END AND vendor.`status`='1' AND vendor.group_name LIKE CONCAT ('%',group_name,'%') AND driver.VehicleRegistrationNo LIKE CONCAT ('%',veh_id,'%')
AND cab_list_mgmt.external_reference LIKE CONCAT ('%',veh_ext_ref,'%') AND driver.FirstName LIKE CONCAT ('%',drivar_name,'%') 
AND driver.refname LIKE CONCAT ('%',driver_ext_ref,'%') 
group by (vendor.id);
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_a_search_enquiry_list` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_a_search_enquiry_list`(IN `created_since_i` DATETIME, IN `created_to_i` DATETIME, IN `closed_since_i` DATETIME, IN `closed_to_i` DATETIME, IN `enq_type_i` VARCHAR(50), IN `status_i` VARCHAR(50))
BEGIN
SELECT enquiry.Id, enquiry.CallerName, enquiry.EmailId, enquiry.CallerName, enquiry.CSRID, enquiry.Message, enquiry.EnqType,
 enquiry.EnquiryDate, enquiry.`Status`, enquiry.ClosedDate FROM enquiry where DATE(enquiry.EnquiryDate) BETWEEN created_since_i 
 AND created_to_i and DATE(enquiry.ClosedDate)  BETWEEN closed_since_i AND closed_to_i and enquiry.EnqType LIKE concat('%',enq_type_i,'%')
  and enquiry.`Status` LIKE concat('%',status_i,'%') ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_a_search_group_driver` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_a_search_group_driver`(IN `driver_id` VARCHAR(50), IN `driver_name` VARCHAR(50), IN `veh_id` VARCHAR(50), IN `veh_ext_ref` VARCHAR(50), IN `driverName` VARCHAR(50), IN `driver_ext_ref` VARCHAR(50))
BEGIN
SELECT vendor.id,vendor.UID,vendor.group_name,vendor.vendor_name FROM vendor INNER JOIN driver ON vendor.id=driver.vendor_id 
INNEr JOIN cab_list_mgmt ON driver.vehicleId = cab_list_mgmt.id
WHERE CASE WHEN driver_id = '' THEN 
0=0 ELSE
driver.UID = driver_id
END AND vendor.`status`='1' AND driver.FirstName LIKE CONCAT ('%',driver_name,'%') AND driver.VehicleRegistrationNo LIKE CONCAT ('%',veh_id,'%')
AND cab_list_mgmt.external_reference LIKE CONCAT ('%',veh_ext_ref,'%') AND driver.FirstName LIKE CONCAT ('%',driverName,'%') 
AND driver.refname LIKE CONCAT ('%',driver_ext_ref,'%') 
group by (vendor.id);
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_a_search_vehicle_driver` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_a_search_vehicle_driver`(IN `lplate` VARCHAR(50), IN `vehmake` VARCHAR(50), IN `vehmodel` VARCHAR(50), IN `vehexternal` VARCHAR(50))
BEGIN
SELECT driver.UID,driver.FirstName,driver.Email,driver.status,driver.ContactNo,driver.VehicleRegistrationNo,driver.DrivingLicenceNo,
driver.is_active,driver.created_date,cab_list_mgmt.name,count(cab_booking.pickup) as booking, user.isVerified,active.status as vehicleName 
FROM `driver` 
INNER JOIN cab_list_mgmt ON driver.vehicleId = cab_list_mgmt.id 
LEFT JOIN cab_booking ON driver.UID=cab_booking.pickup 
INNER JOIN user ON driver.UID = user.ID 
INNER JOIN active ON driver.is_active=active.status_id 
LEFT JOIN vendor on driver.vendor_id = vendor.id 
WHERE driver.DrivingLicenceNo LIKE CONCAT('%',lplate,'%') 
AND driver.MakeOfVehicle LIKE CONCAT('%',vehmake,'%') 
AND driver.ModelOfVehicle LIKE CONCAT('%',vehmodel,'%') 
AND driver.refname LIKE CONCAT('%',vehexternal,'%') group by(UID);
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_a_search_vehicle_group` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_a_search_vehicle_group`(IN `plate_no` VARCHAR(50), IN `veh_make` INT, IN `veh_model` INT, IN `ext_ref` VARCHAR(50))
BEGIN
SELECT vendor.id,vendor.UID,vendor.group_name,vendor.vendor_name,driver.VehicleRegistrationNo ,driver.ModelOfVehicle, driver.MakeOfVehicle ,cab_list_mgmt.external_reference
FROM driver INNER JOIN vendor ON vendor.id=driver.vendor_id 
INNER JOIN cab_list_mgmt ON driver.vehicleId = cab_list_mgmt.id
WHERE 
CASE WHEN veh_model = '' THEN 
0=0 ELSE
driver.ModelOfVehicle LIKE CONCAT ('%',veh_model,'%') END
AND CASE WHEN veh_make = '' THEN 
0=0 ELSE
driver.MakeOfVehicle LIKE CONCAT ('%',veh_make,'%') END
AND  CASE WHEN plate_no = '' THEN 
0=0 ELSE
driver.VehicleRegistrationNo LIKE CONCAT ('%',plate_no,'%') END
AND cab_list_mgmt.external_reference LIKE CONCAT ('%',ext_ref,'%')
group by (vendor.id);
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_a_search_vehicle_type` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_a_search_vehicle_type`(IN `vehicle_no_i` VARCHAR(50), IN `vehicle_type_i` VARCHAR(50), IN `city_i` VARCHAR(50), IN `state_i` VARCHAR(50), IN `country_i` VARCHAR(50), IN `vehicle_owner_i` VARCHAR(50))
BEGIN
SELECT cab_list_mgmt.id, cab_list_mgmt.vehicleNumber, cab_list_mgmt.name, cab_list_mgmt.VehicleModel, booking_bill.CabName, cab_list_mgmt.Manufacturer, cab_list_mgmt.capacity ,cab_list_mgmt.fuelType,cab_list_mgmt.colour,cab_list_mgmt.Registration_Date, driver.FirstName, tblcity.name, tblstates.state, tblcountry.CountryName  FROM cab_list_mgmt INNER JOIN booking_bill ON cab_list_mgmt.CabType = booking_bill.Id INNER JOIN  tblcity ON tblcity.id = booking_bill.CityId INNER JOIN tblstates ON tblcity.state = tblstates.id INNER JOIN tblcountry ON tblstates.country_code = tblcountry.ID  LEFT JOIN driver ON cab_list_mgmt.id = driver.vehicleId where cab_list_mgmt.vehicleNumber LIKE concat('%',vehicle_no_i,'%') and booking_bill.CabName LIKE concat('%',vehicle_type_i,'%') and tblcity.name LIKE concat('%',city_i,'%') and tblstates.state LIKE concat('%',state_i,'%') and tblcountry.CountryName LIKE concat('%',country_i,'%') and driver.FirstName LIKE concat('%',vehicle_owner_i,'%') ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_a_share_calculation` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_a_share_calculation`(IN `companyShare` VARCHAR(50), IN `partnerShare` VARCHAR(50), IN `driverShare` VARCHAR(50))
BEGIN
UPDATE client SET company_share = companyShare, partner_share = partnerShare, driver_share = driverShare where clientName = 'Hello42';
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_a_sub_complaint_type` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_a_sub_complaint_type`(IN `complaintType_i` VARCHAR(100), IN `subComplaint_i` VARCHAR(200), IN `created_by_i` VARCHAR(100))
BEGIN
DECLARE subComplaint_exists int;
SELECT COUNT(*) INTO subComplaint_exists FROM sub_complaint WHERE `complaint_type` = complaintType_i AND sub_complaint = subComplaint_i;
IF subComplaint_exists =0 THEN
INSERT INTO sub_complaint (`complaint_type`,`sub_complaint`,`created_by`,`date`) values(complaintType_i,subComplaint_i,created_by_i,NOW());
END IF;
SELECT subComplaint_exists;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_a_update_action_type` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_a_update_action_type`(IN `action_id_i` INT, IN `manage_action_i` VARCHAR(50), IN `actionType_UpdatedBy_i` VARCHAR(50), IN `action_Status_i` VARCHAR(50))
BEGIN
DECLARE actionType_exists int;
SELECT COUNT(*) INTO actionType_exists FROM tblactiontype WHERE `action` = manage_action_i AND `status` = action_Status_i;
IF actionType_exists =0 THEN
UPDATE tblactiontype SET `action` = manage_action_i, updated_by = actionType_UpdatedBy_i, updated_on = NOW() ,`status` = action_Status_i WHERE id = action_id_i;
END IF;
SELECT actionType_exists;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_a_update_complaint_status` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_a_update_complaint_status`(IN `complaint_Text_i` VARCHAR(50), IN `ComplaintId_i` INT, IN `updated_by_i` VARCHAR(50))
BEGIN
UPDATE complaint SET StatusID = complaint_Text_i, updated_by = updated_by_i, updated_date = NOW() WHERE ID = ComplaintId_i;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_a_update_complaint_type` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_a_update_complaint_type`(IN `complaint_id_i` INT, IN `complaintType_UpdatedBy_i` VARCHAR(50), IN `desc_i` VARCHAR(50))
BEGIN
DECLARE complaintType_exists int;
SELECT COUNT(*) INTO complaintType_exists FROM sub_complaint WHERE `sub_complaint` = desc_i;
IF complaintType_exists =0 THEN
UPDATE sub_complaint SET updated_by = complaintType_UpdatedBy_i, updated_on = NOW() ,`sub_complaint` = desc_i WHERE id = complaint_id_i;
END IF;
SELECT complaintType_exists;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_a_update_enquiry_status` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_a_update_enquiry_status`(IN `enquiry_Text_i` VARCHAR(50), IN `EnquiryId_i` INT, IN `updated_by_i` VARCHAR(500))
BEGIN
Update enquiry SET `Status` = enquiry_Text_i, ClosedDate = NOW(), updated_by = updated_by_i WHERE Id = EnquiryId_i;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_a_update_enquiry_type` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_a_update_enquiry_type`(IN `enquiry_id_i` INT, IN `manage_enquery_i` VARCHAR(50), IN `enquiryType_UpdatedBy_i` VARCHAR(50), IN `Enquiry_Status_i` VARCHAR(50))
BEGIN
DECLARE enquiryType_exists int;
SELECT COUNT(*) INTO enquiryType_exists FROM enquiry_type WHERE enquiry_type = manage_enquery_i AND `status` = Enquiry_Status_i;
IF enquiryType_exists =0 THEN
UPDATE enquiry_type SET enquiry_type = manage_enquery_i, updated_by = enquiryType_UpdatedBy_i, updated_on = NOW() ,`status` = Enquiry_Status_i WHERE id = enquiry_id_i;
END IF;
SELECT enquiryType_exists;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_a_username_check` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_a_username_check`(IN `duserName` VARCHAR(50))
BEGIN
DECLARE uCount INT;
SELECT COUNT(*) INTO uCount FROM `user` WHERE `LoginName` collate latin1_general_ci	=duserName ;
SELECT uCount;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_a_user_bank_detail` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_a_user_bank_detail`(IN `userId` INT)
BEGIN
Select name as bank_account_name,ac_holder_name as account_holder, address as bank_adds, ac_no AS account_no, ifsc_code,ibn_no from bank_details where user_id = userId;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_a_user_feedback` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_a_user_feedback`(IN `booking_id` INT, IN `feedback` VARCHAR(500))
BEGIN
UPDATE
  booking
SET
  user_feedback = feedback
WHERE
  booking_id = booking_id ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_a_user_feedback_old` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_a_user_feedback_old`(IN `booking_id` INT, IN `feedback` VARCHAR(500))
BEGIN
Update cab_booking set user_feedback = feedback where ID = booking_id;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_a_vehicle_edit` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_a_vehicle_edit`(IN `vids` INT)
BEGIN
SELECT * FROM `cab_list_mgmt` WHERE `id`=vids;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_a_vehicle_search_driver` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_a_vehicle_search_driver`(IN `groupId` VARCHAR(50), IN `groupV` VARCHAR(50), IN `groupName` VARCHAR(50), IN `groupVref` VARCHAR(50), IN `groupDname` VARCHAR(50), IN `groupDext` VARCHAR(50))
BEGIN
SELECT driver.UID,driver.FirstName,driver.Email,driver.`status`,driver.ContactNo,driver.VehicleRegistrationNo,driver.DrivingLicenceNo,
driver.is_active,active.`status` as approvedStatus,driver.created_date,cab_list_mgmt.name as vehicleName,
count(pickup) as booking FROM driver  
INNER JOIN cab_list_mgmt ON driver.vehicleId = cab_list_mgmt.id 
LEFT JOIN cab_booking ON driver.UID=cab_booking.pickup 
INNER JOIN user ON driver.UID = user.ID 
INNER JOIN active ON driver.is_active = active.status_id 
LEFT JOIN vendor on driver.vendor_id = vendor.id  
WHERE driver.vendor_id LIKE CONCAT('%',groupId,'%') 
AND driver.DrivingLicenceNo LIKE CONCAT('%',groupV,'%')  
AND driver.refname LIKE CONCAT('%',groupVref,'%') 
AND driver.FirstName LIKE CONCAT('%',groupDname,'%') 
AND driver.refname LIKE CONCAT('%',groupDext,'%') 
AND CASE WHEN groupName!='' THEN 
vendor.group_name LIKE CONCAT ('%',groupName,'%')  
ELSE
0=0
END
group by(driver.UID);
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_a_vehicle_update` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_a_vehicle_update`(IN `ids` INT, IN `vtype` VARCHAR(50), IN `model` VARCHAR(50), IN `ref` VARCHAR(50), IN `vyear` VARCHAR(50), IN `lplate` VARCHAR(50), IN `col` VARCHAR(50), IN `act` INT)
BEGIN
UPDATE `cab_list_mgmt` SET `CabType`=vtype,`VehicleModel`=model,`external_reference`=ref,`year`=vyear,`license_plate`=lplate,`colour`=col,`is_active`=act WHERE `id`=ids;
SELECT ROW_COUNT() AS count;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_booking_data` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_booking_data`(IN `param` VARCHAR(255))
BEGIN
  SET @param = param;
  SET @query = CONCAT( 'SELECT * FROM booking WHERE booking_id=' );
  SET @query = CONCAT( @query, ' IF( '', @param, '' IS NOT NULL, '' );
  SET @query = CONCAT( @query, @param, '', TRUE )' );
  PREPARE stmt FROM @query;
  EXECUTE stmt;
  DROP PREPARE stmt;  
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_booking_info` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_booking_info`(IN `booking_id` INT(2))
BEGIN
DECLARE distance_n varchar(50); 
SELECT distinct(booking.booking_id),currency.name as currency_n,mp.name as BookingType,mvt.vehicle_type as CarType,CONCAT(user.first_name," ",user.last_name," (",user.mobile,")") as driver_name,user.mobile as driver_mobile,user.email as driver_email,uvm.vehicle_master_id,vm.id,CONCAT(vm.vehicle_no," ",mvm.name) as driver_details,vm.vehicle_no,mvm.name as vehicle_name,
bc.cancellation_price,bc.waitingCharge,bc.tripCharge,bc.total_tax_price,bc.totalBill,bc.is_paid,bc.paid_at,bc.currency,bc.invoice_number,bc.payment_type,bc.fees,bc.total_price,bc.total_tax_price,bc.duration_rate,bc.starting_rate,bc.base_price,bc.tax_price,bc.starting_charge,bc.distance_charge,bc.duration_charge,bc.minimum_price,bc.minimumCharge,bc.minimum_distance,bc.distance_rate,bc.nightcharge_price as night_charge,bc.peak_time_price as peakTimePrice,bc.extracharges,be.*,bpd.*,cs.status as booking_status_name,booking.status as booking_status,concat(client.first_name,' ',client.last_name) as clientname,
client.id AS client_id,
client.mobile as client_mobile,
client.email as  client_email,
registeruser.id as user_id,
concat(registeruser.first_name,' ',registeruser.last_name) as username,
registeruser.mobile as user_mobile,
registeruser.email as user_email,
booking.*,
COALESCE(bapd.drop_address,bpd.drop_address) as drop_address,
COALESCE(bapd.drop_area,bpd.drop_area) as drop_area,  bad.arrival_time_pre,
  bad.arrival_time_post,
  bad.arrival_time_actual,
  bad.actual_waiting_distance,
  bad.actual_driven_distance,
  bad.actual_distance,
  bad.actual_time,
  bad.actual_driven_duration,
dispatch.type_of_dispatch,
if(dispatch.type_of_dispatch=1,'Point To Point','Garage To garage') As type_of_dispatch_name,
dispatch.garage_type,
dispatch.address as garage_address,
dispatch.city as garage_city,
dispatch.pincode as garage_pincode,
dispatch.latitude as garage_latitude,
dispatch.longitude as garage_longitude
FROM booking
LEFT JOIN user ON booking.driver_id=user.id
Left join user as client ON booking.client_id = client.id
LEFT JOIN user as registeruser ON booking.user_id = registeruser.id
LEFT JOIN master_package as mp ON booking.master_package_id=mp.id
LEFT JOIN master_vehicle_type as mvt ON booking.master_vehicle_type_id=mvt.id
LEFT JOIN base_vehicle_type AS bvt ON booking.base_vehicle_id = bvt.base_vehicle_id
LEFT JOIN dispatch_location AS dispatch ON bvt.base_comb_id = dispatch.base_comb_id    
LEFT JOIN user_vehicle_mapping as uvm ON booking.driver_id=uvm.user_id
LEFT JOIN vehicle_master as vm ON uvm.vehicle_master_id=vm.vehicle_master_id
LEFT JOIN master_vehicle_model as mvm ON vm.id=mvm.id
LEFT JOIN booking_charges as bc ON booking.booking_id=bc.BookingID
LEFT JOIN booking_estimation as be ON booking.booking_id=be.booking_id
LEFT JOIN booking_pickdrop_details as bpd ON booking.booking_id=bpd.booking_id
LEFT JOIN cab_status as cs ON booking.status=cs.status_id AND cs.type='cab'
LEFT JOIN booking_actual_pickdrop_details AS bapd ON booking.booking_id= bapd.booking_id
LEFT JOIN booking_actual_details AS bad ON booking.booking_id = bad.booking_id
LEFT JOIN master_currency AS currency ON be.currency_id = currency.id
WHERE booking.booking_id=booking_id;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_booking_info_23-06-20` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_booking_info_23-06-20`(IN `booking_id` INT(2))
BEGIN
DECLARE distance_n varchar(50); 
SELECT distinct(booking.booking_id),currency.name as currency_n,mp.name as BookingType,mvt.vehicle_type as CarType,CONCAT(user.first_name," ",user.last_name," (",user.mobile,")") as driver_name,user.mobile as driver_mobile,user.email as driver_email,uvm.vehicle_master_id,vm.id,CONCAT(vm.vehicle_no," ",mvm.name) as driver_details,vm.vehicle_no,mvm.name as vehicle_name,
bc.cancellation_price,bc.waitingCharge,bc.tripCharge,bc.total_tax_price,bc.totalBill,bc.is_paid,bc.paid_at,bc.currency,bc.invoice_number,bc.payment_type,bc.fees,bc.total_price,bc.total_tax_price,bc.duration_rate,bc.starting_rate,bc.base_price,bc.tax_price,bc.starting_charge,bc.distance_charge,bc.duration_charge,bc.minimum_price,bc.minimumCharge,bc.minimum_distance,bc.distance_rate,bc.nightcharge_price as night_charge,bc.peak_time_price as peakTimePrice,bc.extracharges,be.*,bpd.*,cs.status as booking_status_name,booking.status as booking_status,concat(client.first_name,' ',client.last_name) as clientname,
client.mobile as client_mobile,
client.email as  client_email,
concat(registeruser.first_name,' ',registeruser.last_name) as username,
registeruser.mobile as user_mobile,
registeruser.email as user_email,
booking.*,
COALESCE(bapd.drop_address,bpd.drop_address) as drop_address,
COALESCE(bapd.drop_area,bpd.drop_area) as drop_area,  bad.arrival_time_pre,
  bad.arrival_time_post,
  bad.arrival_time_actual,
  bad.actual_waiting_distance,
  bad.actual_driven_distance,
  bad.actual_distance,
  bad.actual_time,
  bad.actual_driven_duration
FROM booking
LEFT JOIN user ON booking.driver_id=user.id
Left join user as client ON booking.client_id = client.id
LEFT JOIN user as registeruser ON booking.user_id = registeruser.id
LEFT JOIN master_package as mp ON booking.master_package_id=mp.id
LEFT JOIN master_vehicle_type as mvt ON booking.master_vehicle_type_id=mvt.id
LEFT JOIN user_vehicle_mapping as uvm ON booking.driver_id=uvm.user_id
LEFT JOIN vehicle_master as vm ON uvm.vehicle_master_id=vm.vehicle_master_id
LEFT JOIN master_vehicle_model as mvm ON vm.id=mvm.id
LEFT JOIN booking_charges as bc ON booking.booking_id=bc.BookingID
LEFT JOIN booking_estimation as be ON booking.booking_id=be.booking_id
LEFT JOIN booking_pickdrop_details as bpd ON booking.booking_id=bpd.booking_id
LEFT JOIN cab_status as cs ON booking.status=cs.status_id AND cs.type='cab'
LEFT JOIN 
booking_actual_pickdrop_details AS bapd ON booking.booking_id= bapd.booking_id
LEFT JOIN
booking_actual_details AS bad ON booking.booking_id = bad.booking_id
LEFT JOIN 
master_currency AS currency ON be.currency_id = currency.id
WHERE booking.booking_id=booking_id;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_booking_info_old` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_booking_info_old`(IN `booking_id` INT)
BEGIN
DECLARE currency_n varchar(50);
DECLARE distance_n varchar(50);
SELECT tblcountry.currency ,tblcountry.distance_unit into currency_n,distance_n FROM tblcountry WHERE tblcountry.CountryName='India';
SELECT distinct(cab_booking.ID),distance_n,currency_n, CONCAT(driver.FirstName," ",driver.LastName," (",driver.ContactNo,")") as driver_name,CONCAT(cab_master.CabRegistrationNumber," ",cab_master.CabName) as driver_details,cancellation_price,waitingCharge,tripCharge,total_tax_price,totalBill,is_paid,paid_at,currency,invoice_number,payment_type,fees,total_price,total_tax_price,duration_rate,starting_rate,base_price,tax_price,starting_charge,distance_charge,duration_charge,minimum_price,booking_charges.minimumCharge,booking_charges.minimum_distance,booking_charges.distance_rate,cab_booking.*
FROM cab_booking
LEFT JOIN driver ON cab_booking.pickup=driver.UID 
LEFT JOIN cab_master ON cab_master.CabId=driver.vehicleId 
LEFT JOIN booking_charges ON cab_booking.id=booking_charges.bookingid 
WHERE cab_booking.id=booking_id; 
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_cab_booking_bill` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_cab_booking_bill`(IN `BookingId_i` INT)
BEGIN
DECLARE PickDate VARCHAR(20);
DECLARE PickTime VARCHAR(20);
DECLARE Date_Timei VARCHAR(20);
DECLARE CabTyp INT;
DECLARE pickupDr INT;
DECLARE configPackageNo INT;
DECLARE booking_type INT;
DECLARE DropPlace VARCHAR(100);
DECLARE userEmailId VARCHAR(100);
select LoginName INTO userEmailId FROM user INNER JOIN cab_booking ON user.ID = cab_booking.ClientID WHERE cab_booking.ID = BookingId_i LIMIT 0,1;
select PickupDate,PickupTime,CarType,pickup,PickupArea,bookingtype INTO PickDate,PickTime,CabTyp,pickupDr,DropPlace,booking_type from cab_booking where ID = BookingId_i LIMIT 0,1;
select Date_Time INTO Date_Timei from booking_tracker where BookingID = BookingId_i and CabStatus = 4 LIMIT 0,1;
select master_package.Sub_Package_Id into configPackageNo from master_package WHERE master_package.Package_Id=booking_type;
select MinimumCharge,WaitingCharge_per_minute,tripCharge_per_minute,Per_Km_Charge,NightCharges,booking_bill.night_rate_begins,booking_bill.night_rate_ends,speed_per_km,	Waitning_minutes,Min_Distance, configPackageNo,PickDate,PickTime,Date_Timei,DropPlace,userEmailId from cab_booking INNER JOIN booking_bill ON cab_booking.CarType = booking_bill.id where cab_booking.ID=BookingId_i;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_cab_booking_complete_bill` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_cab_booking_complete_bill`(IN `BookingId_i` INT)
    NO SQL
BEGIN
DECLARE PickDate VARCHAR(20);
DECLARE PickTime VARCHAR(20);
DECLARE Date_Timei VARCHAR(20);
DECLARE CabTyp INT;
DECLARE pickupDr INT;
DECLARE configPackageNo INT;
DECLARE booking_type INT;
DECLARE DropPlace VARCHAR(100);
DECLARE userEmailId VARCHAR(100);
select LoginName INTO userEmailId FROM user INNER JOIN cab_booking ON user.ID = cab_booking.ClientID WHERE cab_booking.ID = BookingId_i LIMIT 0,1;
select PickupDate,PickupTime,CarType,pickup,PickupArea,bookingtype INTO PickDate,PickTime,CabTyp,pickupDr,DropPlace,booking_type from cab_booking where ID = BookingId_i LIMIT 0,1;
select Date_Time INTO Date_Timei from booking_tracker where BookingID = BookingId_i and CabStatus = 4 LIMIT 0,1;
select master_package.Sub_Package_Id into configPackageNo from master_package WHERE master_package.Package_Id=booking_type;
select minimum_fare_dw,WaitingCharge_per_minute,tripCharge_per_minute,rate_per_km_dw,NightCharges,waiting_fees,basic_tax,night_rate_begins,night_rate_ends,Date_Timei,
speed_per_km,Waitning_minutes,minimum_distance_dw, configPackageNo,PickDate,PickTime,Date_Timei,DropPlace,userEmailId,CabFor,booking_reference
from cab_booking 
INNER JOIN driver ON cab_booking.pickup = driver.UID
INNER JOIN cab_list_mgmt ON driver.vehicleId = cab_list_mgmt.CabId AND cab_booking.BookingType = cab_list_mgmt.BookingType AND cab_booking.CarType = cab_list_mgmt.CabType
where cab_booking.ID=BookingId_i;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_cab_booking_complete_bill_new` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_cab_booking_complete_bill_new`(IN `BookingId_i` INT, IN `bookingType` VARCHAR(50))
    NO SQL
BEGIN
DECLARE PickDate VARCHAR(20);
DECLARE PickTime VARCHAR(20);
DECLARE Date_Timei VARCHAR(20);
DECLARE CabTyp INT;
DECLARE pickupDr INT;
DECLARE configPackageNo INT;
DECLARE booking_type INT;
DECLARE DropPlace VARCHAR(100);
DECLARE userEmailId VARCHAR(100);
select email INTO userEmailId FROM user INNER JOIN booking ON user.id = booking.client_id WHERE booking.booking_id = BookingId_i LIMIT 0,1;
select bpd.pickup_date,bpd.pickup_time,booking.master_vehicle_type_id,booking.driver_id,bpd.pickup_area,booking.master_package_id INTO PickDate,PickTime,CabTyp,pickupDr,DropPlace,booking_type from booking_pickdrop_details AS bpd INNER JOIN booking ON bpd.booking_id = booking.booking_id  where booking.booking_id = BookingId_i LIMIT 0,1;
select Date_Time INTO Date_Timei from booking_tracker where BookingID = BookingId_i and CabStatus = 4 LIMIT 0,1;
select master_package.id into configPackageNo from master_package WHERE master_package.id=booking_type;
IF bookingType=1 THEN
Select Date_Timei,configPackageNo,bpd.pickup_date as PickDate,bpd.pickup_time AS PickTime,Date_Timei,bpd.drop_area as DropPlace,userEmailId,booking.reference_number as booking_reference,bpd.pickup_state as state
from booking
LEFT JOIN booking_pickdrop_details AS bpd ON bpd.booking_id = booking.booking_id
INNER JOIN driver ON booking.driver_id = driver.user_id
where booking.booking_id=BookingId_i;
ELSE
select Date_Timei, configPackageNo,bpd.pickup_date as PickDate,bpd.pickup_time AS PickTime,bpd.drop_area as DropPlace,userEmailId,booking.reference_number as booking_reference,bpd.pickup_state as state
from booking 
INNER JOIN booking_pickdrop_details AS bpd ON bpd.booking_id = booking.booking_id
INNER JOIN driver ON booking.driver_id = driver.user_id
where booking.booking_id=BookingId_i;
END IF;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_cab_booking_complete_bill_new_old` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_cab_booking_complete_bill_new_old`(IN `BookingId_i` INT, IN `bookingType` VARCHAR(50))
    NO SQL
BEGIN
DECLARE PickDate VARCHAR(20);
DECLARE PickTime VARCHAR(20);
DECLARE Date_Timei VARCHAR(20);
DECLARE CabTyp INT;
DECLARE pickupDr INT;
DECLARE configPackageNo INT;
DECLARE booking_type INT;
DECLARE DropPlace VARCHAR(100);
DECLARE userEmailId VARCHAR(100);
select LoginName INTO userEmailId FROM user INNER JOIN cab_booking ON user.ID = cab_booking.ClientID WHERE cab_booking.ID = BookingId_i LIMIT 0,1;
select PickupDate,PickupTime,CarType,pickup,PickupArea,bookingtype INTO PickDate,PickTime,CabTyp,pickupDr,DropPlace,booking_type from cab_booking where ID = BookingId_i LIMIT 0,1;
select Date_Time INTO Date_Timei from booking_tracker where BookingID = BookingId_i and CabStatus = 4 LIMIT 0,1;
select master_package.Sub_Package_Id into configPackageNo from master_package WHERE master_package.Package_Id=booking_type;
IF bookingType=101 THEN
select cab_list_mgmt.*, Date_Timei,configPackageNo,PickDate,PickTime,Date_Timei,DropPlace,userEmailId,cab_booking.CabFor,cab_booking.booking_reference,tblcity.state
from cab_booking 
INNER JOIN driver ON cab_booking.pickup = driver.UID
INNER JOIN cab_list_mgmt ON driver.vehicleId = cab_list_mgmt.CabId AND cab_booking.BookingType = cab_list_mgmt.BookingType AND cab_booking.CarType = cab_list_mgmt.CabType
INNER JOIN tblcity ON tblcity.name = cab_booking.CabIn
where cab_booking.ID=BookingId_i;
ELSE
select cab_list_mgmt.*,Date_Timei, configPackageNo,PickDate,PickTime,Date_Timei,DropPlace,userEmailId,cab_booking.CabFor,cab_booking.booking_reference,tblcity.state
from cab_booking 
INNER JOIN driver ON cab_booking.pickup = driver.UID
INNER JOIN cab_list_mgmt ON driver.vehicleId = cab_list_mgmt.CabId AND cab_booking.BookingType = cab_list_mgmt.BookingType AND cab_booking.CarType = cab_list_mgmt.CabType
INNER JOIN tblcity ON tblcity.name = cab_booking.CabIn
where cab_booking.ID=BookingId_i;
END IF;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_cab_booking_complete_bill_not_matched` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_cab_booking_complete_bill_not_matched`(IN `BookingId_i` INT, IN `bookingType` VARCHAR(50))
    NO SQL
BEGIN
DECLARE PickDate VARCHAR(20);
DECLARE PickTime VARCHAR(20);
DECLARE Date_Timei VARCHAR(20);
DECLARE CabTyp INT;
DECLARE pickupDr INT;
DECLARE configPackageNo INT;
DECLARE booking_type INT;
DECLARE DropPlace VARCHAR(100);
DECLARE userEmailId VARCHAR(100);
select email INTO userEmailId FROM user INNER JOIN booking ON user.id = booking.client_id WHERE booking.booking_id = BookingId_i LIMIT 0,1;
select bpd.pickup_date,bpd.pickup_time,booking.master_vehicle_type_id,booking.driver_id,bpd.pickup_area,booking.master_package_id INTO PickDate,PickTime,CabTyp,pickupDr,DropPlace,booking_type from booking_pickdrop_details AS bpd INNER JOIN booking ON bpd.booking_id = booking.booking_id  where booking.booking_id = BookingId_i LIMIT 0,1;
select Date_Time INTO Date_Timei from booking_tracker where BookingID = BookingId_i and CabStatus = 4 LIMIT 0,1;
select master_package.id into configPackageNo from master_package WHERE master_package.id=booking_type;
IF bookingType=1 THEN
select booking_estimation.*, Date_Timei,configPackageNo,PickDate,PickTime,DropPlace,userEmailId,cab_booking.CabFor,booking.reference_number as booking_reference,pickdrop.state_id as state
from booking 
INNER JOIN booking_pickdrop_details AS pickdrop ON booking.booking_id = pickdrop.booking_id
INNER JOIN driver ON booking.driver_id = driver.user_id
INNER JOIN booking_estimation ON booking.booking_id = booking_estimation.booking_id
where booking.booking_id=BookingId_i;
ELSE
select booking_estimation.*, Date_Timei,configPackageNo,PickDate,PickTime,Date_Timei,DropPlace,userEmailId,booking.reference_number as booking_reference,pickdrop.pickup_state as state
from booking 
INNER JOIN booking_pickdrop_details AS pickdrop ON booking.booking_id = pickdrop.booking_id
INNER JOIN booking_estimation ON booking.booking_id = booking_estimation.booking_id
INNER JOIN driver ON booking.driver_id = driver.user_id
where booking.booking_id=BookingId_i;
END IF;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_cab_booking_complete_bill_not_matched_old` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_cab_booking_complete_bill_not_matched_old`(IN `BookingId_i` INT, IN `bookingType` VARCHAR(50))
    NO SQL
BEGIN
DECLARE PickDate VARCHAR(20);
DECLARE PickTime VARCHAR(20);
DECLARE Date_Timei VARCHAR(20);
DECLARE CabTyp INT;
DECLARE pickupDr INT;
DECLARE configPackageNo INT;
DECLARE booking_type INT;
DECLARE DropPlace VARCHAR(100);
DECLARE userEmailId VARCHAR(100);
select LoginName INTO userEmailId FROM user INNER JOIN cab_booking ON user.ID = cab_booking.ClientID WHERE cab_booking.ID = BookingId_i LIMIT 0,1;
select PickupDate,PickupTime,CarType,pickup,PickupArea,bookingtype INTO PickDate,PickTime,CabTyp,pickupDr,DropPlace,booking_type from cab_booking where ID = BookingId_i LIMIT 0,1;
select Date_Time INTO Date_Timei from booking_tracker where BookingID = BookingId_i and CabStatus = 4 LIMIT 0,1;
select master_package.Sub_Package_Id into configPackageNo from master_package WHERE master_package.Package_Id=booking_type;
IF bookingType=101 THEN
select booking_bill.*, Date_Timei,configPackageNo,PickDate,PickTime,DropPlace,userEmailId,cab_booking.CabFor,cab_booking.booking_reference,tblcity.state
from cab_booking 
INNER JOIN driver ON cab_booking.pickup = driver.UID
INNER JOIN booking_bill ON cab_booking.BookingType = booking_bill.BookingTypeId AND cab_booking.CarType = booking_bill.CabType
INNER JOIN tblcity ON tblcity.name = cab_booking.CabIn AND tblcity.id = booking_bill.CityId
where cab_booking.ID=BookingId_i;
ELSE
select booking_bill.*, Date_Timei,configPackageNo,PickDate,PickTime,Date_Timei,DropPlace,userEmailId,cab_booking.CabFor,cab_booking.booking_reference,tblcity.state
from cab_booking 
INNER JOIN driver ON cab_booking.pickup = driver.UID
INNER JOIN booking_bill ON cab_booking.BookingType = booking_bill.BookingTypeId AND cab_booking.CarType = booking_bill.CabType
INNER JOIN tblcity ON tblcity.name = cab_booking.CabIn AND tblcity.id = booking_bill.CityId
where cab_booking.ID=BookingId_i;
END IF;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_driver_cash` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_driver_cash`(IN `bookingid_i` INT, IN `driverid_i` INT, IN `paid_i` VARCHAR(50), IN `amount` INT, IN `toll` INT, IN `road` INT, IN `other` INT)
BEGIN
DECLARE totalbill_i DOUBLE;
DECLARE bill_deducted DOUBLE;
DECLARE share_deducted INT;
Declare comp_share INT;
DECLARE partner_share_i INT;
Declare security_amt_i INT;
DECLARE book_ref TEXT;
DECLARE basic_tax_price TEXT;
DECLARE extra INT;
SET extra=toll+road+other;
SET totalbill_i=amount;
IF paid_i='cash' THEN
SELECT total_tax_price into basic_tax_price FROM booking_charges WHERE BookingID=bookingid_i; 
SELECT cab_booking.booking_reference into book_ref FROM cab_booking WHERE id=bookingid_i; 
SELECT company_share,driver_share,partner_share into comp_share,share_deducted,partner_share_i FROM client WHERE client.id=1; 
UPDATE driver SET `status`=0 WHERE UID=driverid_i;
UPDATE cab_booking SET `status`=11 WHERE id=bookingid_i;
INSERT INTO booking_logs (`bookingid`,`status`,`time`) VALUES(bookingid_i,11,NOW());
SET  bill_deducted = (((totalbill_i-(extra+basic_tax_price))*(100-share_deducted))/100);
UPDATE driver SET SecurityAmt = (SecurityAmt-(bill_deducted+basic_tax_price)) WHERE UID=driverid_i;
SELECT SecurityAmt into security_amt_i FROM driver WHERE UID=driverid_i;
UPDATE booking_charges SET totalBill=amount,partnershare=partner_share_i,companyshare=comp_share,drivershare=share_deducted,is_paid=1,paid_at=NOW(),payment_type='CASH' WHERE BookingID=bookingid_i;
INSERT INTO driver_transaction (`user_id`,`amount`,`currentbalance`,`status`,`booking_ref`,`reason`) VALUES (driverid_i,bill_deducted,security_amt_i,'Debit',book_ref,'Deducted from Booking');
ELSE
UPDATE driver SET `status`=0 WHERE UID=driverid_i;
UPDATE cab_booking SET `status`=12 WHERE id=bookingid_i;
INSERT INTO booking_logs (`bookingid`,`status`) VALUES(bookingid_i,12);
UPDATE booking_charges SET is_paid=1,paid_at=NOW(),payment_type='CREDIT',totalBill=amount,extracharges=extra WHERE BookingID=bookingid_i;
END IF;
SELECT SecurityAmt,totalbill_i,security_amt_i from driver WHERE UID=driverid_i;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_driver_rating` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_driver_rating`(IN `dids` VARCHAR(50), IN `uids` VARCHAR(50), IN `drate` VARCHAR(50), IN `booking_id` INT)
BEGIN
DECLARE avgamount INT;
INSERT INTO `driver_rating` (`UID`,`DID`,`rating`) VALUES (uids,dids,drate);
SELECT AVG(rating) INTO avgamount FROM driver_rating WHERE DID= dids;
Update cab_booking set driver_rating = drate where ID = booking_id;
UPDATE user SET rating=avgamount WHERE ID=dids;
SELECT avgamount;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_estimated_bill` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_estimated_bill`(IN `car_type` INT, IN `bookingid_i` INT)
    NO SQL
BEGIN
DECLARE currency VARCHAR(50);
DECLARE measure VARCHAR(50);
DECLARE book_type INT;
DECLARE cab_for INT;
DECLARE approx_distance_charge INT;
select tblcountry.currency ,tblcountry.distance_unit into currency, measure from tblcountry where CountryName= 'India';
SELECT cab_booking.BookingType,cab_booking.CabFor,cab_booking.approx_distance_charge,cab_booking.estimated_price,cab_booking.min_Distance as MinDistance into book_type,cab_for,approx_distance_charge FROM cab_booking WHERE id=bookingid_i;
SELECT booking_bill.night_rate_begins,booking_bill.night_rate_ends,booking_bill.MinimumCharge,booking_bill.WaitingCharge_per_minute,booking_bill.tripCharge_per_minute,booking_bill.Per_Km_Charge,booking_bill.NightCharges,
booking_bill.waiting_fees,booking_bill.speed_per_km,booking_bill.Waitning_minutes,booking_bill.Min_Distance,book_type,cab_for,approx_distance_charge,currency,measure,booking_bill.basic_tax 
from booking_bill 
where booking_bill.Id = car_type;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_estimated_bill_new` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_estimated_bill_new`(IN `car_type` INT, IN `cab_id` INT, IN `bookingid_i` INT, IN `cabIn` VARCHAR(255), IN `bookingType` VARCHAR(50))
    NO SQL
BEGIN
IF bookingType=101 THEN
SELECT 
cab_booking.BookingType,cab_booking.CabFor,cab_booking.approx_distance_charge,cab_booking.estimated_price,cab_booking.min_Distance,cab_booking.CabIn,cab_booking.local_subpackage,
cab_list_mgmt.night_rate_begins,cab_list_mgmt.night_rate_ends,cab_list_mgmt.MinimumCharge,cab_list_mgmt.WaitingCharge_per_minute,cab_list_mgmt.tripCharge_per_minute,cab_list_mgmt.Per_Km_Charge,cab_list_mgmt.NightCharges,
cab_list_mgmt.waiting_fees,cab_list_mgmt.Waitning_minutes,cab_list_mgmt.Min_Distance,cab_list_mgmt.basic_tax,tblcity.state,tbllocalpackage.Economy_Price,tbllocalpackage.Sidan_Price,tbllocalpackage.Prime_Price,tblcountry.currency ,tblcountry.distance_unit 
from cab_booking 
INNER JOIN cab_list_mgmt ON cab_list_mgmt.BookingType = cab_booking.BookingType
INNER JOIN tblcity ON tblcity.name = cab_booking.CabIn
INNER JOIN tbllocalpackage ON tbllocalpackage.Package = cab_booking.local_subpackage AND tbllocalpackage.City_ID = tblcity.state
INNER JOIN tblcountry ON tblcountry.CountryName = 'India'
where  cab_list_mgmt.CabType= cab_booking.CarType AND cab_list_mgmt.CabId= cab_id AND cab_booking.id=bookingid_i;
ELSE
SELECT 
cab_booking.BookingType,cab_booking.CabFor,cab_booking.approx_distance_charge,cab_booking.estimated_price,cab_booking.min_Distance,cab_booking.CabIn,cab_booking.local_subpackage,
cab_list_mgmt.night_rate_begins,cab_list_mgmt.night_rate_ends,cab_list_mgmt.MinimumCharge,cab_list_mgmt.WaitingCharge_per_minute,cab_list_mgmt.tripCharge_per_minute,cab_list_mgmt.Per_Km_Charge,cab_list_mgmt.NightCharges,
cab_list_mgmt.waiting_fees,cab_list_mgmt.Waitning_minutes,cab_list_mgmt.Min_Distance,cab_list_mgmt.basic_tax,tblcity.state,tblcountry.currency ,tblcountry.distance_unit 
from cab_booking 
INNER JOIN cab_list_mgmt ON cab_list_mgmt.BookingType = cab_booking.BookingType
INNER JOIN tblcity ON tblcity.name = cab_booking.CabIn
INNER JOIN tblcountry ON tblcountry.CountryName = 'India'
where  cab_list_mgmt.CabType= cab_booking.CarType AND cab_list_mgmt.CabId= cab_id AND cab_booking.id=bookingid_i;
END IF;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_estimated_price` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_estimated_price`(IN `BookingId_i` INT)
BEGIN
DECLARE PickDate VARCHAR(20);
DECLARE PickTime VARCHAR(20);
DECLARE Date_Timei VARCHAR(20);
DECLARE CabTyp INT;
DECLARE pickupDr INT;
DECLARE configPackageNo INT;
DECLARE booking_type INT;
DECLARE DropPlace VARCHAR(100);
DECLARE userEmailId VARCHAR(100);
select LoginName INTO userEmailId FROM user 
INNER JOIN cab_booking ON user.ID = cab_booking.ClientID 
WHERE cab_booking.ID = BookingId_i 
LIMIT 0,1;
select PickupDate,
		 PickupTime,
		 CarType,
		 pickup,
		 PickupArea,
		 bookingtype 
		 INTO 
		 PickDate,
		 PickTime,
		 CabTyp,
		 pickupDr,
		 DropPlace,
		 booking_type 
from cab_booking 
where ID = BookingId_i 
LIMIT 0,1;
select Date_Time INTO Date_Timei 
from booking_tracker 
where BookingID = BookingId_i and CabStatus = 4 
LIMIT 0,1;
select master_package.Sub_Package_Id into configPackageNo from master_package 
WHERE master_package.Package_Id=booking_type;
select MinimumCharge,
		 WaitingCharge_per_minute,
		 tripCharge_per_minute,
		 Per_Km_Charge,
		 NightCharges,
		 booking_bill.night_rate_begins,
		 booking_bill.night_rate_ends,
		 speed_per_km,
		 Waitning_minutes,
		 Min_Distance,
		 booking_bill.basic_tax, 
		 configPackageNo,
		 PickDate,
		 PickTime,
		 Date_Timei,
		 DropPlace,
		 userEmailId,
		 cab_booking.BookingType,
		 cabfor.Hrs as CabFor
FROM cab_booking
INNER JOIN booking_bill ON cab_booking.BookingType = booking_bill.BookingTypeId AND cab_booking.CarType = booking_bill.CabType
LEFT JOIN cabfor ON cab_booking.CabFor = cabfor.id
where cab_booking.ID=BookingId_i;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_forget_password` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_forget_password`(IN `email` VARCHAR(50), IN `token` VARCHAR(200))
BEGIN
DECLARE id_count INT;
DECLARE ids INT;
SELECT `ID` INTO id_count FROM user WHERE `LoginName` COLLATE latin1_general_ci=email or `UserNo` COLLATE latin1_general_cs =email LIMIT 0,1;
IF id_count> 1 THEN
INSERT INTO tblactivation(UID,Verification_code) VALUES(id_count,token);
END IF;		
SELECT ids,id_count;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_get_fare_mode` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_get_fare_mode`(IN `packagemodeid` INT , IN `basevehicleid` INT )
BEGIN
IF packagemodeid=1 THEN
SELECT * FROM distance_fare WHERE base_vehicle_id=basevehicleid;
ELSEIF packagemodeid = 2 THEN
SELECT * FROM hourly_fare WHERE base_vehicle_id = basevehicleid;
ELSEIF packagemodeid = 3 THEN
SELECT * FROM distance_hour_fare WHERE base_vehicle_id=basevehicleid;
ELSEIF packagemodeid = 4 THEN
SELECT * FROM distance_waiting_fare WHERE base_vehicle_id=basevehicleid;
END IF;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_get_user_profile` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_get_user_profile`(IN `user_id` INT(16))
BEGIN
SELECT user.first_name,user.email,user.mobile,user_info.image,user_info.address AS Address1,vm.image AS vehicleimg,vm.ignition_type_id ,fueltype.fuel_type AS fuelType,vm.registration_number as CabRegistrationNumber,vtype.id as vehicleId, vtype.vehicle_type as TypeOfvehicle FROM user LEFT JOIN user_info ON user.id=user_info.user_id LEFT JOIN user_vehicle_mapping as vmap ON user.id = vmap.user_id LEFT JOIN vehicle_master AS vm ON vmap.vehicle_master_id =  vm.vehicle_master_id LEFT JOIN master_vehicle_model as vmodel ON vm.id = vmodel.id  LEFT JOIN master_vehicle_type AS vtype ON vmodel.vehicle_type_id =vtype.id LEFT JOIN master_fuel_type AS fueltype ON vm.ignition_type_id=fueltype.id  WHERE user.id=user_id;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_get_user_profile_old` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_get_user_profile_old`(IN `user_id` INT)
BEGIN
SELECT * FROM user_info WHERE UID=user_id;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_list_of_vehicles` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_list_of_vehicles`()
BEGIN
SELECT `id`,`name` FROM `cab_list_mgmt`;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_local_booking` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_local_booking`(IN `DeviceType` VARCHAR(50), IN `bookingtype` VARCHAR(50), IN `cablocation` VARCHAR(50), IN `cablocationfor` VARCHAR(50), IN `cartypes` INT, IN `cablocationnationality` VARCHAR(50), IN `cablocaladults` INT, IN `cablocalchild` INT, IN `cablocalluggages` INT, IN `cablocalpickuparea` VARCHAR(50), IN `cablocaladdress` VARCHAR(50), IN `pickuplatlat` DOUBLE, IN `pickuplatlng` DOUBLE, IN `pickupdate` VARCHAR(50), IN `pickuptime` VARCHAR(50), IN `datebooking` DATETIME, IN `name` VARCHAR(100), IN `phone` VARCHAR(50), IN `email` VARCHAR(100), IN `user_agent` TEXT, IN `peakTime` INT, IN `alt_phone` VARCHAR(50))
BEGIN
	DECLARE userId INT;
	DECLARE userExists INT;
	DECLARE normailUserId INT;
	DECLARE userNewId INT;
	SELECT COUNT(*) , `ID` INTO userExists,normailUserId 
	FROM `user` 
	WHERE `LoginName` COLLATE latin1_general_ci = email AND `UserNo` COLLATE latin1_general_cs = phone ;
	IF userExists=1 THEN
		INSERT INTO `cab_booking` (`DeviceType`, `BookingType`,`ClientId`,`UserName`,`EmailId`,`CabIn`,`MobileNo`,`CabFor`,`CarType`,`Nationality`,`No_Of_Adults`,`No_Of_Childs`,`No_Of_Luggages`,`PickupArea`,`PickupAddress`,`PickupLatitude`,`PickupLongtitude`,`PickupDate`,`PickupTime`,`BookingDate`,`Status`,`useragent`,`partner`,`peakTime`) 
		VALUES (DeviceType, bookingtype,normailUserId,name,email,cablocation,phone,cablocationfor,cartypes,cablocationnationality,cablocaladults,cablocalchild,cablocalluggages,cablocalpickuparea,cablocaladdress,pickuplatlat,pickuplatlng,pickupdate,pickuptime,datebooking,1,user_agent,1,peakTime);
		SELECT last_insert_id() INTO userId;
		INSERT INTO `booking_logs` (bookingid,`status`,message,`time`) VALUES (userId,1,'Requesting car',datebooking);			
		INSERT INTO `booking_stack`(`booking_id`,`lat`,`long`,`status`) VALUES (userId,pickuplatlat,pickuplatlng,'');
	ELSE
		INSERT INTO `user` (`LoginName`,`UserNo`,`UserType`) VALUES (email,phone,2);
		SELECT last_insert_id() INTO userNewId;
		INSERT INTO  user_info(`uid`,`firstname`,`email`,`mobno`,`AlternateContactNo`) VALUES (userNewId,name,email,phone,alt_phone);
		INSERT INTO `cab_booking` (`DeviceType`, `BookingType`,`ClientId`,`UserName`,`EmailId`,`MobileNo`,`CabIn`,`CabFor`,`CarType`,`Nationality`,`No_Of_Adults`,`No_Of_Childs`,`No_Of_Luggages`,`PickupArea`,`PickupAddress`,`PickupLatitude`,`PickupLongtitude`,`PickupDate`,`PickupTime`,`BookingDate`,`Status`,`Partner`,`peakTime`) 
		VALUES (DeviceType, bookingtype,userNewId,name,email,phone,cablocation,cablocationfor,cartypes,cablocationnationality,cablocaladults,cablocalchild,cablocalluggages,cablocalpickuparea,cablocaladdress,pickuplatlat,pickuplatlng,pickupdate,pickuptime,datebooking,1,1,peakTime);
		SELECT last_insert_id() INTO userId;
		INSERT INTO `booking_logs` (bookingid,`status`,message,`time`) VALUES (userId,1,'Requesting car',datebooking);			
		INSERT INTO `booking_stack`(`booking_id`,`lat`,`long`,`status`) VALUES (userId,pickuplatlat,pickuplatlng,'');
	END IF;
	SELECT userId,userNewId,userExists,normailUserId;	
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_login` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_login`(IN `username` VARCHAR(500), IN `userpass` VARCHAR(500), IN `gcmid` TEXT, IN `token` VARCHAR(50))
BEGIN
DECLARE checklogin VARCHAR(100) ; 
DECLARE id_s INT ; 
DECLARE type_data INT ; 
DECLARE login_state INT ;
  SELECT
    COUNT(*),
    login_status
  INTO
    checklogin,
    login_state
  FROM
    `user`
  WHERE
    (
      `email` COLLATE latin1_general_ci = username OR `mobile` COLLATE latin1_general_cs = username
    ) AND `password` COLLATE latin1_general_ci = userpass AND `is_verified` = 1 ; 
IF checklogin = 1 THEN
  SELECT
    user_type_id,
    id
  INTO
    type_data,
    id_s
  FROM
    `user`
  WHERE
    (
      `email` COLLATE latin1_general_ci = username OR `mobile` COLLATE latin1_general_cs = username
    )
  LIMIT 0, 1 ; 
IF type_data = 3 THEN
UPDATE
  user
SET
  gcm_id = ''
WHERE
  `gcm_id` COLLATE latin1_general_cs = gcmid AND user_type_id = 3 ;
SELECT
  token,
  type_data,
  checklogin,
  login_state,
  b.id,
  b.email,
  b.first_name,
  c.city_id,
  b.mobile,
  b.imei,
  a.security_amt,
  c.image AS prof_img,
  mc.name as city_name
FROM
  user b
left JOIN
  user_info c ON b.id = c.user_id
Left Join 
  driver a ON b.id = a.user_id
left join 
  master_city mc ON c.city_id = mc.id
WHERE
  b.id = id_s ;
INSERT
INTO
  `driver_status`(`uid`,
  `status`,
  `login_time`)
VALUE
  (id_s,
  '1',
  NOW()) ; 
ELSE
UPDATE
  user
SET
  gcm_id = ''
WHERE
  `gcm_id` COLLATE latin1_general_cs = gcmid AND user_type_id != 3 ;
SELECT
  token,
  login_state,
  b.referral_key,
  type_data,
  checklogin,
  b.id,
  b.email,
  b.first_name,
  a.city_id,
  b.mobile,
  a.image AS prof_img,
  mc.name as city_name
FROM
  user b
Left JOIN
  user_info a ON b.id = a.user_id
left join 
  master_city mc ON a.city_id = mc.id
WHERE
  b.id = id_s ;
END IF ; 
IF login_state = 0 THEN
UPDATE
  user
SET
  `login_status` = 1,
  is_active = 1,
  `gcm_id` = gcmid,
  `token` = token,
  login_time = NOW()
WHERE
  `id` = id_s ;
INSERT
INTO
  log_in_log(
    userId,
    `login_time`,
    login_date,
    lat,
    `log`,
    `status`
  )
VALUES(
  id_s,
  NOW(), DATE(login_time), '', '', token) ;
END IF ;
  END IF ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_login_old` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_login_old`(IN `username` VARCHAR(500), IN `userpass` VARCHAR(500), IN `gcmid` TEXT, IN `token` VARCHAR(50))
BEGIN
DECLARE checklogin VARCHAR(100);
DECLARE id_s INT;
DECLARE type_data INT;
DECLARE login_state INT;
SELECT COUNT(*),loginStatus INTO checklogin,login_state FROM `user` WHERE (`LoginName` COLLATE latin1_general_ci = username OR `UserNo` COLLATE latin1_general_cs = username) AND `Password` COLLATE latin1_general_ci =userpass AND `isVerified`=1;
IF checklogin = 1 THEN
SELECT UserType,ID INTO type_data,id_s FROM `user` WHERE (`LoginName`COLLATE latin1_general_ci = username OR `UserNo`COLLATE latin1_general_cs = username) LIMIT 0,1;
IF type_data=3 THEN
UPDATE user SET gcm_id='' WHERE `gcm_id` COLLATE latin1_general_cs =gcmid and UserType=3;
SELECT token,type_data,checklogin,login_state,b.ID,a.FirstName,a.City,a.ContactNo,a.imei,a.SecurityAmt,b.loginname,a.Email,c.image as prof_img FROM driver a 
INNER JOIN user_info c ON a.UID = c.UID
INNER JOIN user b ON a.UID = b.ID WHERE b.ID=id_s;
INSERT INTO `driverstatus` (`uid`,`status`,`login_time`) VALUE (id_s,'1',NOW());
ELSE
UPDATE user SET gcm_id='' WHERE `gcm_id`COLLATE latin1_general_cs =gcmid and UserType!=3;
SELECT token,login_state,b.referralKey,type_data,checklogin,b.ID, a.FirstName,a.City,a.MobNo,a.Email,a.image as prof_img FROM user_info a INNER JOIN user b ON a.UID = b.ID WHERE b.ID=id_s;
END IF;
IF login_state=0 THEN
UPDATE user SET `loginStatus`=1,is_active=1,`gcm_id` =gcmid,`token`=token ,loginTime=NOW() WHERE `ID`=id_s;
INSERT INTO log_in_log (userId,`login_time`,login_date,lat,`log`,`status`) VALUES (id_s,NOW(),DATE(login_time),'','',token);
END IF;
END IF;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_reject_booking` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_reject_booking`(IN `id_i` INT, IN `book_i` INT)
BEGIN
DECLARE update_id INT;
SELECT booking_register.id into update_id FROM booking_register WHERE booking_register.driverId=id_i and booking_register.bookingid=book_i order by id desc LIMIT 0,1;
UPDATE booking_register SET `status`='R' WHERE id=update_id ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_reporting` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_reporting`(IN `cabBookingId` VARCHAR(50), IN `driverLat` VARCHAR(50), IN `driverLong` VARCHAR(50), IN `cabDriverId` INT)
BEGIN
DECLARE pickdatebook VARCHAR(50);
DECLARE picktimebook VARCHAR(50);
DECLARE clientIdbook INT;
DECLARE usernumber VARCHAR(50);
SELECT bpd.`pickup_date`,bpd.`pickup_time`,booking.`client_id`,user.mobile  INTO pickdatebook,picktimebook,clientIdbook,usernumber FROM `booking_pickdrop_details` as bpd left join booking ON bpd.booking_id=booking.booking_id join user ON booking.client_id = user.id  WHERE booking.`booking_id`= cabBookingId and booking.booking_status='1' ;
INSERT INTO `booking_logs` (`bookingid`,`user_id`,`status`,`message`,`time`) VALUES (cabBookingId,cabDriverId,'5','Reported',NOW());
UPDATE booking Left Join booking_actual_details as bad ON booking.booking_id = bad.booking_id SET booking.`status`=5,bad.arrival_time_actual=NOW() WHERE booking.booking_id=CabBookingId and booking.booking_status='1';
INSERT INTO `booking_tracker` (`BookingID`,`DriverId`,`Latitutude`,`Logitude`,`Date_Time`,`CabStatus`) VALUES (cabBookingId,cabDriverId,driverLat,driverLong,NOW(),'5');
SELECT  pickdatebook,picktimebook,usernumber;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_reporting_old` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_reporting_old`(IN `cabBookingId` VARCHAR(50), IN `driverLat` VARCHAR(50), IN `driverLong` VARCHAR(50), IN `cabDriverId` INT)
BEGIN
DECLARE pickdatebook VARCHAR(50);
DECLARE picktimebook VARCHAR(50);
DECLARE clientIdbook INT;
DECLARE usernumber VARCHAR(50);
SELECT `PickupDate`,`PickupTime`,`ClientID` INTO pickdatebook,picktimebook,clientIdbook FROM `cab_booking` WHERE `ID`= cabBookingId and BookingStatus='1';
SELECT `UserNo` INTO usernumber FROM `user` WHERE `ID` =clientIdbook;
INSERT INTO `booking_logs` (`bookingid`,`status`,`message`,`time`) VALUES (cabBookingId,'5','Reported',NOW());
UPDATE cab_booking SET `status`=5,arrival_time_actual=NOW() WHERE id=CabBookingId and BookingStatus='1';
INSERT INTO `booking_tracker` (`BookingID`,`DriverId`,`Latitutude`,`Logitude`,`Date_Time`,`CabStatus`) VALUES (cabBookingId,cabDriverId,driverLat,driverLong,NOW(),'5');
SELECT  pickdatebook,picktimebook,usernumber;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_reset_password` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_reset_password`(IN `newPass` VARCHAR(100), IN `cPass` VARCHAR(100), IN `code` VARCHAR(100))
BEGIN
DECLARE id_user INT;
SELECT `UID` INTO id_user FROM `tblactivation` WHERE BINARY Verification_code=code AND `isUsed`=0 ORDER BY id DESC LIMIT 0,1;
IF id_user>0 THEN
UPDATE `user` SET `Password`=newPass WHERE id=id_user;
UPDATE `tblactivation` SET `isUsed`=1 WHERE BINARY Verification_code=code;
END IF;
SELECT id_user;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_send_message` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_send_message`(IN `id_i` INT, IN `booking_id` INT, IN `message` TEXT)
BEGIN
DECLARE user_id int;
DECLARE driver_id int;
DECLARE gcm text;
SELECT cab_booking.ClientID,cab_booking.pickup into user_id,driver_id from cab_booking WHERE id=booking_id;
IF user_id = id_i THEN
SELECT user.gcm_id into gcm FROM user WHERE id=driver_id;
INSERT INTO booking_msg(booking_msg.bookingid,booking_msg.sender,booking_msg.receiver,booking_msg.msg,booking_msg.`datetime`) VALUES (booking_id,id_i,driver_id,message,NOW());
ELSEIF driver_id=id_i THEN 
SELECT user.gcm_id into gcm FROM user WHERE id=user_id;
INSERT INTO booking_msg(booking_msg.bookingid,booking_msg.sender,booking_msg.receiver,booking_msg.msg,booking_msg.`datetime`) VALUES (booking_id,id_i,user_id,message,NOW());
END IF;
SELECT gcm;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_signup` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_signup`(IN `userEmailIdLog` VARCHAR(50), IN `userName` VARCHAR(50), IN `mobileNo` VARCHAR(50), IN `landNo` VARCHAR(50), IN `cities` VARCHAR(50), IN `adds` VARCHAR(50), IN `emailId` VARCHAR(50), IN `altEmails` VARCHAR(50), IN `companyuserName` VARCHAR(50), IN `userMobile` VARCHAR(50), IN `UserLandline` VARCHAR(50), IN `userEmails` VARCHAR(50), IN `altUserEmails` VARCHAR(50), IN `userAddress` VARCHAR(50), IN `usersCity` VARCHAR(50), IN `userPins` VARCHAR(50), IN `image` TEXT, IN `userpassword` VARCHAR(250), IN `userRoles` VARCHAR(50), IN `UserNos` VARCHAR(50), IN `activationcode` VARCHAR(250), IN `userlang` VARCHAR(50))
BEGIN
DECLARE userexists INT;
DECLARE lastusersid INT;
DECLARE idcom INT;
DECLARE afectactive INT;
DECLARE num INT;
DECLARE lastcompanyid INT;
SELECT COUNT(*) INTO userexists FROM `user` WHERE `LoginName` COLLATE latin1_general_ci =userEmailIdLog  or LoginName COLLATE latin1_general_ci=UserNos or 
UserNo COLLATE latin1_general_ci=userEmailIdLog or UserNo COLLATE latin1_general_ci=UserNos;
IF userexists <1 THEN
	INSERT INTO `user`(`LoginName`,`Password`,`UserType`,`amount`,`UserNo`,`create_date`) VALUES (userEmailIdLog,userpassword,userRoles,0,mobileNo,NOW());
	SELECT ROW_COUNT() INTO num;
	SELECT LAST_INSERT_ID() INTO lastusersid;
	IF lastusersid >0 THEN
		INSERT INTO `user_info`(`UID`,`FirstName`,`City`,`Address1`,`Email`,`AltEmail`,`MobNo`,`LandNo`,`image`,`language`) VALUES 
		(lastusersid,userName,cities,adds,emailId,altEmails,mobileNo,landNo,image,userlang);
		SELECT ROW_COUNT() INTO idcom;
		IF idcom >0 THEN
			INSERT INTO `company`(`CompanyName`,`CompanyCity`,`CompanyAddress`,`CompanyPIN`,`ComMob`,`CompLand`,`Email`,`AltEmail`) VALUES 
			(companyuserName,cities,adds,userPins,userMobile,UserLandline,userEmails,altUserEmails);
			SELECT ROW_COUNT() INTO afectactive;
			SELECT LAST_INSERT_ID() INTO lastcompanyid;
			IF afectactive >0 THEN
			INSERT INTO `tblactivation` (`UID`,`Verification_code`) VALUES (lastusersid,activationcode) ;
			END IF;
		END IF;
	END IF;
END IF;
SELECT num,lastusersid,userexists,afectactive,lastcompanyid;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_start_trip` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_start_trip`(IN `bookid` INT, IN `driverid` INT)
BEGIN
DECLARE lastid INT;
UPDATE booking SET `status`=7 WHERE booking_id=bookid;
INSERT INTO booking_tracker (`BookingID`,`DriverId`,`Date_Time`,`CabStatus`) VALUES (bookid,driverid,NOW(),'7');
INSERT INTO booking_logs (`bookingid`,`user_id`,`status`,`message`,`time`) VALUES (bookid,driverid,'7','Trip Start',NOW()); 
SELECT LAST_INSERT_ID() INTO lastid;
SELECT lastid;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_start_trip_old` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_start_trip_old`(IN `bookid` INT, IN `driverid` INT)
BEGIN
DECLARE lastid INT;
UPDATE cab_booking SET `status`=7 WHERE id=bookid;
INSERT INTO booking_tracker (`BookingID`,`DriverId`,`Date_Time`,`CabStatus`) VALUES (bookid,driverid,NOW(),'7');
INSERT INTO booking_logs (`bookingid`,`status`,`message`,`time`) VALUES (bookid,'7','Trip Start',NOW()); 
SELECT LAST_INSERT_ID() INTO lastid;
SELECT lastid;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_total_payment_charges` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_total_payment_charges`(IN `TripCharge` INT, IN `waitingCh` INT, IN `TotalBill` INT, IN `MinimumChrage` INT, IN `BookingID_N` INT, IN `distance_i` DOUBLE, IN `dist_rate` INT, IN `trip_charge` INT, IN `tax_i` INT, IN `duration_i` INT, IN `address` TEXT, IN `lat` VARCHAR(50), IN `lon` VARCHAR(50), IN `time_i` TIME)
    NO SQL
BEGIN
DECLARE book_ref TEXT;
SELECT cab_booking.booking_reference into book_ref FROM cab_booking WHERE id=BookingID_N;
INSERT INTO booking_charges (tripCharge,waitingCharge,minimumCharge,BookingID,AddedTime,distance_rate,distance_charge,minimum_distance,total_tax_price,currency,invoice_number,total_price,tax_price,starting_rate,starting_charge,minimum_price,duration_rate,duration_charge)VALUES(TripCharge,waitingCh,MinimumChrage,BookingID_N,NOW(),dist_rate,TripCharge,trip_charge,tax_i,'RS',CONCAT('IN-',book_ref),TotalBill,tax_i,dist_rate,MinimumCharge,MinimumCharge,duration_i,trip_charge);
INSERT INTO booking_logs(bookingid,`status`,message,`time`) VALUES(BookingID_N,8,'Completed',NOW());
UPDATE cab_booking SET `status`=8,DropAddress=address,final_lat=lat,final_long=lon,actual_distance=distance_i,actual_time=time_i,expiration_time=NOW() WHERE id=BookingID_N;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_user_booking_details` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_user_booking_details`(IN `ids` VARCHAR(50))
BEGIN
SELECT booking_charges.minimum_distance,cab_booking.appox_waiting_minute,
booking_charges.distance_charge,booking_charges.duration_charge,cab_booking.actual_distance,cab_booking.actual_driven_duration
 FROM `cab_booking` INNER JOIN driver ON cab_booking.pickup=driver.UID INNER JOIN booking_charges ON booking_charges.BookingID =cab_booking.ID WHERE cab_booking.ID=ids;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_user_distance` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_user_distance`()
BEGIN
SELECT `Latitude`,`Longtitude1` FROM user WHERE `UserType`=3 ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_user_helo_money` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_user_helo_money`(IN `usersids` VARCHAR(50), IN `rocommendvis` VARCHAR(50))
BEGIN
DECLARE ids INT;
SELECT COUNT(ID) INTO ids FROM `user` WHERE `ID`= usersids;
IF ids>0 THEN
UPDATE `user` SET `recvia`=rocommendvis , amount=amount+50  WHERE `ID`=usersids;
SELECT ROW_COUNT() AS counter,ids;
END IF;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_user_history_all` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_user_history_all`(IN `userids` VARCHAR(50))
BEGIN
SELECT `PickupDate`,`PickupTime`,`PickupArea`,`ID` FROM `cab_booking` WHERE `ClientID`=userids;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_user_history_completed` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_user_history_completed`(IN `userids` VARCHAR(50))
BEGIN
SELECT `PickupDate`,`PickupTime`,`PickupArea`,`ID` FROM `cab_booking` WHERE `ClientID`=userids AND `status`>3;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_user_history_pending` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_user_history_pending`(IN `userIds` VARCHAR(50), IN `tomorrowdate` VARCHAR(50))
BEGIN
SELECT `PickupDate`,`PickupTime`,`PickupArea`,`ID` FROM `cab_booking` WHERE `ClientID`=userids AND CONCAT(`PickupDate`,' ',`PickupTime`) >= tomorrowdate;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_validation` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_validation`(IN `userEmailIdLog` VARCHAR(100))
BEGIN
DECLARE checkusers INT;
SELECT  COUNT(*) INTO  checkusers FROM `user` WHERE  (BINARY `LoginName` =userEmailIdLog or BINARY `UserNo`=userEmailIdLog) AND UserType=3;
SELECT checkusers;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `wp_vehicles` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `wp_vehicles`(IN `datanames` VARCHAR(50))
BEGIN
DECLARE vehicleNames VARCHAR(50);
SELECT `id`  FROM `cab_list_mgmt` WHERE `name` LIKE CONCAT('%', datanames, '%') LIMIT 0,10;
END ;;
DELIMITER ;

--
-- Current Database: `bookingcabs_demo2`
--


--
-- Final view structure for view `user_credit_balance`
--

/*!50001 DROP VIEW IF EXISTS `user_credit_balance`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`bookingc_cabs`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `user_credit_balance` AS select `user`.`id` AS `id`,concat(`user`.`first_name`,' ',`user`.`last_name`) AS `username`,`user`.`email` AS `email`,`user`.`mobile` AS `mobile`,`user`.`is_active` AS `is_active`,`user`.`user_type_id` AS `user_type_id`,`utype`.`RoleName` AS `user_type`,`credit_limit`.`from_date` AS `from_date`,`credit_limit`.`to_date` AS `to_date`,`credit_limit`.`credit_limit_amount` AS `credit_limit_amount`,sum(`booking_estimation`.`estimated_final_price`) AS `booking_amount`,(`credit_limit`.`credit_limit_amount` - sum(`booking_estimation`.`estimated_final_price`)) AS `credit_balance` from ((((`user` join `user_credit_limit` `credit_limit` on((`user`.`id` = `credit_limit`.`user_id`))) join `booking` on((`user`.`id` = `booking`.`user_id`))) join `booking_estimation` on((`booking`.`booking_id` = `booking_estimation`.`booking_id`))) join `user_role` `utype` on((`user`.`user_type_id` = `utype`.`Role_ID`))) group by `user`.`id` */;

--
-- Final view structure for view `view_booking_charge`
--

/*!50001 DROP VIEW IF EXISTS `view_booking_charge`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`bookingc_cabs`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `view_booking_charge` AS select 1 AS `id`,1 AS `BookingID`,1 AS `waitingCharge`,1 AS `tripCharge`,1 AS `minimumCharge`,1 AS `totalBill`,1 AS `AddedTime`,1 AS `is_paid`,1 AS `billing_status`,1 AS `paid_at`,1 AS `currency`,1 AS `booking_invoice_id`,1 AS `invoice_number`,1 AS `payment_type`,1 AS `fees`,1 AS `total_price`,1 AS `total_tax_price`,1 AS `distance_rate`,1 AS `duration_rate`,1 AS `starting_rate`,1 AS `base_price`,1 AS `tax_price`,1 AS `starting_charge`,1 AS `distance_charge`,1 AS `duration_charge`,1 AS `minimum_distance`,1 AS `cancellation_price`,1 AS `companyshare`,1 AS `drivershare`,1 AS `partnershare`,1 AS `minimum_price`,1 AS `extracharges`,1 AS `nightcharge_unit`,1 AS `nightcharge`,1 AS `nightcharge_price`,1 AS `night_rate_begins`,1 AS `night_rate_ends`,1 AS `extras`,1 AS `extraPrice`,1 AS `peakTimePrice`,1 AS `peak_time_value`,1 AS `basic_tax`,1 AS `basic_tax_type`,1 AS `basic_tax_price`,1 AS `pre_waiting_time`,1 AS `pre_waiting_charge`,1 AS `waiting_time`,1 AS `waiting_charge`,1 AS `igst_tax`,1 AS `cgst_tax`,1 AS `sgst_tax`,1 AS `estimated_price_before_markup`,1 AS `markup_type`,1 AS `markup_value`,1 AS `markup_price`,1 AS `driver_share_amt`,1 AS `comp_share_amt`,1 AS `partner_share_amt`,1 AS `reference_number`,1 AS `user_id`,1 AS `user_type_id`,1 AS `email`,1 AS `mobile`,1 AS `address`,1 AS `pincode`,1 AS `landline_number`,1 AS `gst_registration_number`,1 AS `pancard_no`,1 AS `user_type`,1 AS `username`,1 AS `client_id`,1 AS `client_name`,1 AS `package_name`,1 AS `package_mode`,1 AS `outstation_module_type`,1 AS `estimateprice_before_discount`,1 AS `discount_price`,1 AS `estimated_final_price`,1 AS `approx_distance_charge`,1 AS `approx_after_km`,1 AS `approx_waiting_charge`,1 AS `approx_waiting_minute`,1 AS `vehicle_name`,1 AS `vehicle_type`,1 AS `actual_distance`,1 AS `actual_time`,1 AS `booking_amt_paid`,1 AS `booking_amt_balance` */;

--
-- Final view structure for view `view_company_vendor_driver`
--

/*!50001 DROP VIEW IF EXISTS `view_company_vendor_driver`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`bookingc_cabs`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `view_company_vendor_driver` AS select 1 AS `id`,1 AS `user_type_id`,1 AS `user_type`,1 AS `first_name`,1 AS `last_name`,1 AS `email`,1 AS `mobile`,1 AS `parent_id`,1 AS `company_id`,1 AS `is_active`,1 AS `com_name`,1 AS `child_user_id`,1 AS `child_name`,1 AS `child_email`,1 AS `child_mobile`,1 AS `child_usertype_id`,1 AS `child_user_type` */;

--
-- Final view structure for view `view_driver_vehicle_map`
--

/*!50001 DROP VIEW IF EXISTS `view_driver_vehicle_map`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`bookingc_cabs`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `view_driver_vehicle_map` AS select 1 AS `user_id`,1 AS `gcm_id`,1 AS `driver_name`,1 AS `company_id`,1 AS `vendor_id`,1 AS `mobile`,1 AS `email`,1 AS `state_name`,1 AS `state_id`,1 AS `city_name`,1 AS `city_id`,1 AS `driver_status`,1 AS `mapping_id`,1 AS `vehicle_type`,1 AS `vehicle_type_color`,1 AS `vehicle_type_id`,1 AS `vehicle_name`,1 AS `vehicle_model_id`,1 AS `vehicle_color`,1 AS `vehicle_master_id`,1 AS `created_by`,1 AS `vehicle_no`,1 AS `model_year`,1 AS `color`,1 AS `vehicle_owner_type`,1 AS `owner_name`,1 AS `owner_mobile`,1 AS `permit_expiry_date`,1 AS `passenger`,1 AS `large_suitcase`,1 AS `small_suitcase`,1 AS `fuel_type` */;

--
-- Final view structure for view `view_vehicle_fare_details`
--

/*!50001 DROP VIEW IF EXISTS `view_vehicle_fare_details`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`bookingc_cabs`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `view_vehicle_fare_details` AS select 1 AS `base_combination_id`,1 AS `bs_status`,1 AS `company_id`,1 AS `vendor_id`,1 AS `company_setup_name`,1 AS `country_id`,1 AS `country_name`,1 AS `state_id`,1 AS `state_name`,1 AS `city_id`,1 AS `city_name`,1 AS `master_package_id`,1 AS `package_name`,1 AS `master_package_mode_id`,1 AS `package_mode`,1 AS `date_from`,1 AS `date_to`,1 AS `rate_type`,1 AS `rate_value`,1 AS `week_days`,1 AS `vendor_name`,1 AS `client_name`,1 AS `vehicle_type`,1 AS `dispatch_location_id`,1 AS `user_id`,1 AS `base_comb_id`,1 AS `type_of_dispatch`,1 AS `garage_type`,1 AS `address`,1 AS `city`,1 AS `pincode`,1 AS `created_date`,1 AS `created_by`,1 AS `status`,1 AS `distance_minimum_charge`,1 AS `distance_minimum_distance`,1 AS `distance_per_km_charge`,1 AS `hourly_minimum_charge`,1 AS `hourly_minimum_hrs`,1 AS `hourly_per_hr_charge`,1 AS `dist_waiting_minimum_charge`,1 AS `dist_waiting_minimum_distance`,1 AS `dist_waiting_per_km_charge`,1 AS `dist_waiting_waiting_fees`,1 AS `dist_hour_minimum_charge`,1 AS `dist_hour_minimum_distance`,1 AS `dist_hour_minimum_hrs`,1 AS `dist_hour_per_km_charge`,1 AS `dist_hour_per_hr_charge`,1 AS `company_share_type`,1 AS `company_share` */;

--
-- Final view structure for view `view_wallet_point`
--

/*!50001 DROP VIEW IF EXISTS `view_wallet_point`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`bookingc_cabs`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `view_wallet_point` AS select 1 AS `user_id`,1 AS `username`,1 AS `referral_key`,1 AS `referal_id`,1 AS `referal_by`,1 AS `user_amount`,1 AS `referer_amount`,1 AS `created` */;


