| Server IP : 103.234.187.230 / Your IP : 216.73.216.216 Web Server : Apache System : Linux lserver42043-ind.megavelocity.net 3.10.0-1160.108.1.el7.x86_64 #1 SMP Thu Jan 25 16:17:31 UTC 2024 x86_64 User : apache ( 48) PHP Version : 7.4.33 Disable Function : NONE MySQL : OFF | cURL : ON | WGET : ON | Perl : ON | Python : ON | Sudo : ON | Pkexec : ON Directory : /var/www/html/b2bzend/application/models/ |
Upload File : |
<?php
class Travel_Model_TblFlight
{
protected $db = NULL;
public $intId = NULL;
public $AgencySysId = NULL;
public $strName = NULL;
public $intCountry = NULL;
public $intState = NULL;
public $intCity = NULL;
public $strAddress1 = NULL;
public $strAddress2 = NULL;
public $intRating = NULL;
public $location = NULL;
public $description = NULL;
public $postalCode = NULL;
public $services = NULL;
public $strHotelImage = NULL;
public $intLoggedinUserAgencySysId;
public $getMasterAgencySysId;
public $agencyType;
public $baseUrl;
/* Standard, Deluxe and Luxury */
public $strCategory = NULL;
/* From database standard, deluxe and luxury */
public $intCategoryId = NULL;
public $isBackend = 'Y';
/* Common to all models */
public $strCondition = NULL;
public $deleted = 'N';
public $status = '1';
public $strSelectedView = '';
public $intQueryOffset = 0;
public $intListPerPage = 10;
public $strOrderBy = 'hotel_name';
public $orderBy = '';
public $isVerified = 'Y';
public $imageServerUrl = '';
public $intJourneyType = '';
public $intTripType = '';
public $intPageNo = 1;
/* * ************************************ */
public function __construct()
{
$request = Zend_Controller_Front::getInstance()->getRequest();
//$this->baseUrl = $request->getScheme() . '://' . $request->getHttpHost();
$this->db = Zend_Db_Table::getDefaultAdapter();
$sessionLogin_user = new Zend_Session_Namespace('sessionLogin_user');
$this->intLoggedinUserAgencySysId = $sessionLogin_user->intLoggedinUserAgencySysId;
$this->getMasterAgencySysId = $sessionLogin_user->getMasterAgencySysId;
$this->agencyType = isset($sessionLogin_user->agencyDetails['AgencyType']) ? $sessionLogin_user->agencyDetails['AgencyType'] : '';
}
public function __destruct()
{
$this->db->closeConnection();
}
public function getAllFlightList($intLoggedinAgencyId, $searchArr)
{
$sql = " SELECT InvnItemSysId, XRefFlightSysId, IsItTwoWay, IsItParent, PairItemSysId, EconomyType, FromDate, ToDate, PurchasedQty, PublishQty,CostCurrency, TB_Master_Currency.Symbol AS Currency, TotalCost, TB_IC_Supplier.SupplierName AS SupplierType, TB_Master_Geo_City.Title AS SupplierCityName, TB_MP_Inventory_Air.StartPlaceSysId, TB_MP_Inventory_Air.DestPlaceSysId, "
. "TB_IC_Trans_Air.FlightNumber, TB_Master_Airline.Title As Airline, TB_Master_Airline.AirlineLogo, TMSP.Title AS FromPlace, TMP.Title AS ToPlace, TB_Master_Airline.AirlineSysId, TB_Master_Airline.Code AS AirlineCode, "
. "TB_IC_Trans_Air.StopCount, CAST( CASE WHEN TB_IC_Trans_Air.IsDirect = 0 THEN 'Direct' ELSE 'Connecting' END AS char(15)) AS flightType, TB_MP_Inventory_Air.IsActive, TB_MP_Inventory_Air.IsSaleStart "
. "FROM TB_MP_Inventory_Air "
. "JOIN TB_IC_Trans_Air ON TB_MP_Inventory_Air.XRefFlightSysId=TB_IC_Trans_Air.AirInvenSysId "
. "JOIN TB_Master_Currency ON TB_MP_Inventory_Air.CostCurrency=TB_Master_Currency.CurrencyType "
. "JOIN TB_IC_Supplier ON TB_MP_Inventory_Air.SupplierSysId=TB_IC_Supplier.SupplierSysId "
. "JOIN TB_Master_Geo_City ON TB_Master_Geo_City.CityId=TB_IC_Supplier.CitySysId "
. "JOIN TB_Master_Airline ON TB_IC_Trans_Air.AirlineSysId=TB_Master_Airline.AirlineSysId "
. "JOIN TB_Master_Geo_City AS TMSP ON TB_MP_Inventory_Air.StartPlaceSysId=TMSP.CityId "
. "JOIN TB_Master_Geo_City AS TMP ON TB_MP_Inventory_Air.DestPlaceSysId=TMP.CityId "
. "WHERE 1=1 ";
if (!empty($intLoggedinAgencyId)) {
$sql .= " AND AgencySysId = " . $intLoggedinAgencyId;
}
if (!empty($searchArr)) {
//echo "<pre>"; print_r($searchArr); die;
if (($searchArr['DestinationId'] != 0 && $searchArr['toDestinationId'] != 0) && ($searchArr['isWay'] == 1)) {
$sql .= " AND (TB_MP_Inventory_Air.StartPlaceSysId = '" . $searchArr['DestinationId'] . "' AND TB_MP_Inventory_Air.DestPlaceSysId = '" . $searchArr['toDestinationId'] . "')";
}
if (($searchArr['DestinationId'] != 0 && $searchArr['toDestinationId'] != 0) && ($searchArr['isWay'] == 2)) {
$sql .= " AND (TB_MP_Inventory_Air.StartPlaceSysId = '" . $searchArr['DestinationId'] . "' AND TB_MP_Inventory_Air.DestPlaceSysId = '" . $searchArr['toDestinationId'] . "') "
. " AND (TB_MP_Inventory_Air.StartPlaceSysId = '" . $searchArr['toDestinationId'] . "' AND TB_MP_Inventory_Air.DestPlaceSysId = '" . $searchArr['DestinationId'] . "')";
}
if ($searchArr['airlineId']) {
$sql .= " AND TB_IC_Trans_Air.AirlineSysId = '" . $searchArr['airlineId'] . "' ";
}
if ($searchArr['date']) {
$sql .= " AND TB_MP_Inventory_Air.FromDate = '" . $searchArr['date'] . "' ";
}
if ($searchArr['supplierId']) {
$sql .= " AND TB_MP_Inventory_Air.SupplierSysId = '" . $searchArr['supplierId'] . "'";
}
if ($searchArr['isWay'] == 1) {
$sql .= " AND TB_MP_Inventory_Air.IsItTwoWay = '0'";
}
if ($searchArr['isWay'] == 2) {
$sql .= " AND TB_MP_Inventory_Air.IsItTwoWay = '1'";
}
}
//ORDER BY TB_MP_Inventory_Air.IsItTwoWay DESC, TB_MP_Inventory_Air.UpdateDate DESC, IsItParent
$sql .= " ORDER BY TB_MP_Inventory_Air.UpdateDate DESC ";
//echo $sql;exit;
return $this->db->query($sql)->fetchAll();
}
public function getAirline($airlineID)
{
$sql = "Select Title From TB_Master_Airline where AirlineSysId = '" . $airlineID . "'";
$rowset = $this->db->query($sql)->fetchAll();
return $rowset;
}
public function getMultipleAirlineByCode($AirlineCodes)
{
$sql = "Select AirlineSysId,Title,Code From TB_Master_Airline where Code IN(" . $AirlineCodes . ")";
$rowset = $this->db->query($sql)->fetchAll();
return $rowset;
}
public function getFlightList($strFlightNo, $strFlightDateOnward, $strMode, $strModeType)
{
$sql = "Select distinct ltrim(rtrim(FlightNumber)) FlightNumber From TB_IC_Trans_Air where 1 = 1 AND FlightCabinClass = '2'";
if (!empty($strFlightNo)) {
$sql .= " AND FlightNumber LIKE '$strFlightNo%' ";
}
if ($strModeType == "onward") {
if (!empty($strFlightDateOnward) && $strFlightDateOnward == "____-__-__") {
$date = @date('Y-m-d');
//$next_date = date('Y-m-d', strtotime($date . ' +1 day'));
$next_date = '';
} else if (!empty($strFlightDateOnward) && $strFlightDateOnward != "____-__-__") {
$date = $strFlightDateOnward;
$next_date = date('Y-m-d', strtotime($strFlightDateOnward . ' +1 day'));
}
} else {
if (!empty($strFlightDateOnward) && $strFlightDateOnward == "____-__-__") {
$date = @date('Y-m-d');
//$next_date = date('Y-m-d', strtotime($date . ' +1 day'));
$next_date = '';
} else if (!empty($strFlightDateOnward) && $strFlightDateOnward != "____-__-__") {
$date = $strFlightDateOnward;
$next_date = date('Y-m-d', strtotime($strFlightDateOnward . ' +1 day'));
}
}
if ($strMode == "flight") {
//$sql .= " AND LocalFromTime >= '".$date."' ";
} else {
//$sql .= " AND LocalFromTime >= '".$date."' AND LocalFromTime < '".$next_date."' ";
}
//$sql .= " order by LocalFromTime ";
//echo $sql;
//die;
//$sql .= " DATE(LocalFromTime)=";
//echo $sql;
//$sql .= " GROUP BY FlightNumber, AirInvenSysId, StopCount, IsDirect";
//echo $sql; die;
$rowset = $this->db->query($sql)->fetchAll();
//echo count($rowset);
if (count($rowset) > 0) {
foreach ($rowset as $row) {
$sql_query = " SELECT TOP 1 AirInvenSysId, LocalFromTime FROM TB_IC_Trans_Air WHERE 1=1 AND FlightCabinClass = '2' ";
if ($row['FlightNumber']) {
$sql_query .= " AND FlightNumber='" . $row['FlightNumber'] . "' ";
}
if ($strMode == "flight") {
//$sql_query .= " AND LocalFromTime >= '".$date."' ";
} else {
//$sql_query .= " AND LocalFromTime >= '".$date."' AND LocalFromTime < '".$next_date."' ";
}
$sql_query .= " order by LocalFromTime ";
// echo $sql_query;
$rowres_set = $this->db->query($sql_query)->fetch();
$AirInvenSysId = $rowres_set['AirInvenSysId'];
$FromDateTime = (array) $rowres_set['LocalFromTime'];
$date_time = date("d/m/Y", strtotime($FromDateTime['date']));
$response[] = array('value' => $row['FlightNumber'], 'label' => $row['FlightNumber'], 'flightId' => $AirInvenSysId, 'flightDate' => $date_time);
//$response[] = array('value' => $row['FlightNumber'], 'label' => $row['FlightNumber'], 'flightId' => $row['AirInvenSysId']);
}
} else {
$response[] = array('flightId' => 0, 'value' => 'Add New Flight', 'label' => 'Add New Flight');
}
return $response;
}
public function getFlightListBYO($strFlightNo, $strFlightDateOnward, $strMode, $strModeType)
{
$sql = "Select distinct ltrim(rtrim(FlightNumber)) FlightNumber From TB_IC_Trans_Air where 1 = 1 AND FlightCabinClass = '2'";
if (!empty($strFlightNo)) {
$sql .= " AND FlightNumber LIKE '$strFlightNo%' ";
}
if ($strModeType == "onward") {
if (!empty($strFlightDateOnward) && $strFlightDateOnward == "____-__-__") {
$date = @date('Y-m-d');
//$next_date = date('Y-m-d', strtotime($date . ' +1 day'));
$next_date = '';
} else if (!empty($strFlightDateOnward) && $strFlightDateOnward != "____-__-__") {
$date = $strFlightDateOnward;
$next_date = date('Y-m-d', strtotime($strFlightDateOnward . ' +1 day'));
}
} else {
if (!empty($strFlightDateOnward) && $strFlightDateOnward == "____-__-__") {
$date = @date('Y-m-d');
//$next_date = date('Y-m-d', strtotime($date . ' +1 day'));
$next_date = '';
} else if (!empty($strFlightDateOnward) && $strFlightDateOnward != "____-__-__") {
$date = $strFlightDateOnward;
$next_date = date('Y-m-d', strtotime($strFlightDateOnward . ' +1 day'));
}
}
if ($strMode == "flight") {
//$sql .= " AND LocalFromTime >= '".$date."' ";
} else {
//$sql .= " AND LocalFromTime >= '".$date."' AND LocalFromTime < '".$next_date."' ";
}
//$sql .= " order by LocalFromTime ";
//echo $sql;
//die;
//$sql .= " DATE(LocalFromTime)=";
//echo $sql;
//$sql .= " GROUP BY FlightNumber, AirInvenSysId, StopCount, IsDirect";
//echo $sql; die;
$rowset = $this->db->query($sql)->fetchAll();
// echo '<pre>';
// print_r($rowset);
//echo count($rowset);
if (count($rowset) > 0) {
foreach ($rowset as $row) {
$sql_query = " SELECT TOP 1 AirInvenSysId,AirlineSysId,StopCount, LocalFromTime FROM TB_IC_Trans_Air WHERE 1=1 AND FlightCabinClass = '2' ";
if ($row['FlightNumber']) {
$sql_query .= " AND FlightNumber='" . $row['FlightNumber'] . "' ";
}
if ($strMode == "flight") {
//$sql_query .= " AND LocalFromTime >= '".$date."' ";
} else {
//$sql_query .= " AND LocalFromTime >= '".$date."' AND LocalFromTime < '".$next_date."' ";
}
$sql_query .= " order by LocalFromTime ";
// echo $sql_query;
$rowres_set = $this->db->query($sql_query)->fetch();
$AirInvenSysId = $rowres_set['AirInvenSysId'];
$AirlineSysId = $rowres_set['AirlineSysId'];
$stopcount = $rowres_set['StopCount'];
$FromDateTime = (array) $rowres_set['LocalFromTime'];
$date_time = date("d/m/Y", strtotime($FromDateTime['date']));
$response[] = array('value' => $row['FlightNumber'], 'label' => $row['FlightNumber'], 'StopCount' => $stopcount, 'flightId' => $AirInvenSysId, 'AirlineSysId' => $AirlineSysId, 'flightDate' => $date_time);
//$response[] = array('value' => $row['FlightNumber'], 'label' => $row['FlightNumber'], 'flightId' => $row['AirInvenSysId']);
}
} else {
$response[] = array('flightId' => 0, 'value' => 'Add New Flight', 'label' => 'Add New Flight');
}
return $response;
}
public function getAllPlacelList()
{
$sql = "Select CityId as PlaceSysId, Title From TB_Master_Geo_City where 1 = 1 AND IsActive='1' AND Code IS NOT NULL";
$sql .= " ORDER BY Title ";
$rowset = $this->db->query($sql)->fetchAll();
return $rowset;
}
public function getPlacelDetailsByPlaceId($placeId)
{
$sql = "Select CityId as PlaceSysId, Code, Title From TB_Master_Geo_City where 1 = 1 AND IsActive='1'";
if (!empty($placeId)) {
$sql .= " AND CityId = '" . $placeId . "' ";
}
//echo $sql; die;
$rowset = $this->db->query($sql)->fetch();
return $rowset;
}
public function addFlightDetails($data)
{
try {
$this->db->insert('TB_IC_Trans_API_Air', $data);
return $this->db->lastInsertId();
} catch (Zend_Exception $e) {
$error_Message = $e->getMessage();
print_r($error_Message);
}
}
public function updateBookingStatusTravelPlan($data, $TPSysId, $MasterTPSysId = 0)
{
$querystatus = (isset($data['querystatus']) && !empty($data['querystatus'])) ? $data['querystatus'] : 0;
unset($data['querystatus']);
$where = array('TPSysId = ? ' => $TPSysId);
$this->db->update('TB_Agency_Customer_TravelPlan', $data, $where);
$customHelper = Zend_Controller_Action_HelperBroker::getStaticHelper("Custom");
$statusArray = $customHelper->changeQueryStatusByProposal();
if (empty($querystatus)) {
$querystatus = $statusArray[$data['StatusType']];
}
$updatetravelplanquery = array(
'UpdateDate' => date('Y-m-d H:i:s')
);
if (!empty($MasterTPSysId)) {
$whereMaster = array('TPSysId = ? ' => $MasterTPSysId);
if (isset($querystatus) && !empty($querystatus)) {
$updatetravelplanquery['StatusType'] = $querystatus;
}
if (isset($data['PaymentStatus']) && !empty($data['PaymentStatus'])) {
$updatetravelplanquery['PaymentStatus'] = $data['PaymentStatus'];
}
if (isset($data['BookingStatus']) && !empty($data['BookingStatus'])) {
$updatetravelplanquery['BookingStatus'] = $data['BookingStatus'];
}
$this->db->update('TB_Agency_Customer_TravelPlan_Query', $updatetravelplanquery, $whereMaster);
}
}
public function updateStatusTravelPlanAbort($data, $TPSysId, $MasterTPSysId = NULL)
{
$where = array('TPSysId = ? ' => $TPSysId);
$this->db->update('TB_Agency_Customer_TravelPlan', $data, $where);
if (!empty($MasterTPSysId)) {
$whereMaster = array('TPSysId = ? ' => $MasterTPSysId);
$updatetravelplanquery = array(
'BookingStatus' => 144,
'UpdateDate' => date('Y-m-d H:i:s')
);
$this->db->update('TB_Agency_Customer_TravelPlan_Query', $updatetravelplanquery, $whereMaster);
}
}
public function updateBookingStatusTravelPlanFailed($data, $TPSysId, $MasterTPSysId = NULL, $field = NULL)
{
$where = array('TPSysId = ? ' => $TPSysId);
$this->db->update('TB_Agency_Customer_TravelPlan', $data, $where);
$customHelper = Zend_Controller_Action_HelperBroker::getStaticHelper("Custom");
$statusArray = $customHelper->changeQueryStatusByProposal();
$querystatus = $statusArray[$data['' . $field . '']];
if (isset($querystatus) && !empty($querystatus) && !empty($MasterTPSysId)) {
$whereMaster = array('TPSysId = ? ' => $MasterTPSysId);
$updatetravelplanquery = array(
'' . $field . '' => $querystatus,
'UpdateDate' => date('Y-m-d H:i:s')
);
if ($field == 'PaymentStatus' && $data['PaymentStatus'] == 22) {
$updatetravelplanquery['StatusType'] = 143;
}
if (isset($data['StatusType']) && $data['StatusType'] > 0) {
$updatetravelplanquery['StatusType'] = 143;
}
return $this->db->update('TB_Agency_Customer_TravelPlan_Query', $updatetravelplanquery, $whereMaster);
}
}
public function updateBookingStatusTravelPlanAPI($data, $TPSysId, $MasterTPSysId = NULL)
{
$where = array('TPSysId = ? ' => $TPSysId);
$this->db->update('TB_Agency_Customer_TravelPlan', $data, $where);
// update main query status created by Er Amit Kumar Dubey on 11 may 2017 at 01:26 PM
$customHelper = Zend_Controller_Action_HelperBroker::getStaticHelper("Custom");
$statusArray = $customHelper->changeQueryStatusByProposal();
$querystatus = $statusArray[$data['StatusType']];
if (isset($querystatus) && !empty($querystatus) && !empty($MasterTPSysId)) {
$whereMaster = array('TPSysId = ? ' => $MasterTPSysId);
$updatetravelplanquery = array(
'StatusType' => $querystatus,
'Price' => $data['Price'],
'NetPrice' => $data['NetPrice'],
'AgencyMarkUp' => $data['AgencyMarkUp'],
'AgentServiceTaxAmount' => $data['AgentServiceTaxAmount'],
'MarkUp' => $data['MarkUp'],
'AgentsCustomServiceTaxAmount' => $data['AgentsCustomServiceTaxAmount'],
'GTXMarkup' => $data['GTXMarkup'],
'GTXServiceTaxAmount' => $data['GTXServiceTaxAmount'],
'TotalCost' => $data['TotalCost'],
'UpdateDate' => date('Y-m-d H:i:s')
);
if (isset($data['PaymentStatus']) && !empty($data['PaymentStatus'])) {
$updatetravelplanquery['PaymentStatus'] = $data['PaymentStatus'];
}
if (isset($data['BookingStatus']) && !empty($data['BookingStatus'])) {
$updatetravelplanquery['BookingStatus'] = $data['BookingStatus'];
}
$this->db->update('TB_Agency_Customer_TravelPlan_Query', $updatetravelplanquery, $whereMaster);
}
}
public function updateBookingStatusTravelPlanOnly($data, $TPSysId, $MasterTPSysId = NULL)
{
$where = array('TPSysId = ? ' => $TPSysId, '"MasterTPSysId" = ? ' => $MasterTPSysId);
$this->db->update('TB_Agency_Customer_TravelPlan', $data, $where);
}
public function updateBookingDetails($data, $TPintSysId, $AirInvenSysId = null)
{
if (!empty($AirInvenSysId)) {
$where = array('InvnItemSysId = ? ' => $AirInvenSysId);
}
$where = array('VersionId = ? ' => $TPintSysId);
$update = $this->db->update('TB_Agency_Customer_TravelPlan_Air', $data, $where);
return $update;
}
public function updateWalletData($data, $TrxId)
{
$where = array('TrxId = ? ' => $TrxId);
$this->db->update('TB_Agency_Credit_History', $data, $where);
}
public function updateBookingDetailsByInvnItemSysId($data, $InvnItemSysId)
{
$where = array('InvnItemSysId = ? ' => $InvnItemSysId);
$this->db->update('TB_Agency_Customer_TravelPlan_Air', $data, $where);
}
public function updateAgencyCustomer($data, $CustomerSysId, $AgencySysId = null)
{
if (!empty($AgencySysId) && $AgencySysId > 0) {
$where = array('CustomerSysId = ? ' => $CustomerSysId, 'AgencySysId = ? ' => $AgencySysId);
} else {
$where = array('CustomerSysId = ? ' => $CustomerSysId);
}
$update = $this->db->update('TB_Agency_Customer', $data, $where);
return $update;
}
public function updateInvnItemSysIdDetails($data, $TPIntSysId)
{
if ($TPIntSysId) {
foreach ($TPIntSysId as $value) {
$where = array('TPIntSysId = ? ' => $value);
$this->db->update('TB_Agency_Customer_TravelPlan_Air', $data, $where);
}
}
}
public function updateInvnItemSysIdData($data, $TPIntSysId)
{
if ($TPIntSysId) {
$where = array('TPIntSysId = ? ' => $TPIntSysId);
return $this->db->update('TB_Agency_Customer_TravelPlan_Air', $data, $where);
}
}
public function updateApiFlightDetails($AirInvenSysId, $data)
{
$where = array('TBQAirInvenSysId = ? ' => $AirInvenSysId);
$this->db->update('TB_IC_Trans_API_Air', $data, $where);
}
public function addFlightStopsDetails($data)
{
$this->db->insert('TB_IC_Trans_API_Air_Stop', $data);
return $this->db->lastInsertId();
}
public function addFlightFareBreakdown($data)
{
$this->db->insert('TB_IC_Trans_API_Air_FareBreakdown', $data);
return $this->db->lastInsertId();
}
public function updateFlightFareBreakdown($data, $AirInvenSysId)
{
$where = array('TBQAirInvenAPISysId = ? ' => $AirInvenSysId, 'PassengerType = ? ' => $data['PassengerType']);
$this->db->update('TB_IC_Trans_API_Air_FareBreakdown', $data, $where);
}
public function updateFlightFareBreakdownFareBreakdownSysId($data, $FareBreakdownSysId)
{
$where = array('FareBreakdownSysId = ? ' => $FareBreakdownSysId);
$this->db->update('TB_IC_Trans_API_Air_FareBreakdown', $data, $where);
}
public function addFlightFairRuleDetails($data)
{
$this->db->insert('TB_IC_Trans_API_Air_Fare', $data);
return $this->db->lastInsertId();
}
public function addCustomerMember($data)
{
$this->db->insert('TB_Agency_Customer_Members', $data);
return $this->db->lastInsertId();
}
public function updateCustomerMember($data, $MemberSysId)
{
$where = array('MemberSysId = ? ' => $MemberSysId);
$this->db->update('TB_Agency_Customer_Members', $data, $where);
}
public function addFlightSegmentsDetails($data)
{
$this->db->insert('TB_IC_Trans_API_Air_Segment', $data);
return $this->db->lastInsertId();
}
public function updateFlightFareDetails($data, $intAirInvenSysId)
{
$where = array('TBOAirInvenAPISysId = ? ' => $intAirInvenSysId);
$this->db->update('TB_IC_Trans_API_Air_Fare', $data, $where);
}
public function getFlightSourcePlaceList($flightId)
{
$sql1 = "Select cty.CityId as PlaceSysId, cty.Title, TB_IC_Trans_Air.LocalFromTime as DepartureTime, IsDirect From TB_IC_Trans_Air JOIN TB_Master_Geo_City [cty] ON TB_IC_Trans_Air.SourcePlaceSysId=cty.CityId where 1 = 1 ";
if (!empty($flightId)) {
$sql1 .= " AND TB_IC_Trans_Air.AirInvenSysId = '" . $flightId . "' ";
}
//echo $sql1; die;
$fetchData = $this->db->query($sql1)->fetchAll();
if ($fetchData[0]['IsDirect'] == 0) {
$sql = "Select cty.CityId as PlaceSysId, cty.Title, TB_IC_Trans_Air_Stop.DepartureTime From TB_IC_Trans_Air_Stop JOIN TB_Master_Geo_City [cty] ON TB_IC_Trans_Air_Stop.SourcePlaceSysId=cty.CityId where 1 = 1 ";
if (!empty($flightId)) {
$sql .= " AND TB_IC_Trans_Air_Stop.AirInvenSysId = '" . $flightId . "' ";
}
$fetchData = $this->db->query($sql)->fetchAll();
}
//$sql .= " ORDER BY TB_IC_Trans_Air.SourcePlaceSysId ";
// echo $sql;
// die;
return $fetchData;
}
public function getFlightSourcePlaceListBYO($flightId)
{
$sql1 = "Select cty.CityId as PlaceSysId, cty.Title, TB_IC_Trans_Air.LocalFromTime as DepartureTime, IsDirect From TB_IC_Trans_Air JOIN TB_Master_Geo_City [cty] ON TB_IC_Trans_Air.SourcePlaceSysId=cty.CityId where 1 = 1 ";
if (!empty($flightId)) {
$sql1 .= " AND TB_IC_Trans_Air.AirInvenSysId = '" . $flightId . "' ";
}
//echo $sql1; die;
$fetchData = $this->db->query($sql1)->fetchAll();
if ($fetchData[0]['IsDirect'] == 0) {
$sql = "Select cty.CityId as PlaceSysId, cty.Title, TB_IC_Trans_Air_Stop.DepartureTime From TB_IC_Trans_Air_Stop JOIN TB_Master_Geo_City [cty] ON TB_IC_Trans_Air_Stop.SourcePlaceSysId=cty.CityId where 1 = 1 ";
if (!empty($flightId)) {
$sql .= " AND TB_IC_Trans_Air_Stop.AirInvenSysId = '" . $flightId . "' ";
}
$fetchData = $this->db->query($sql)->fetchAll();
}
//$sql .= " ORDER BY TB_IC_Trans_Air.SourcePlaceSysId ";
// echo $sql;
// die;
return $fetchData;
}
public function getFlightDestinationPlaceListBYO($flightNumber)
{
$sql2 = "Select cty.CityId as PlaceSysId, cty.Title, TB_IC_Trans_Air.LocalToTime as ArrivalTime, IsDirect From TB_IC_Trans_Air JOIN TB_Master_Geo_City [cty] ON TB_IC_Trans_Air.DestPlaceSysId=cty.CityId where 1 = 1 ";
if (!empty($flightNumber)) {
$sql2 .= " AND TB_IC_Trans_Air.AirInvenSysId = '" . $flightNumber . "' ";
}
$fetchData = $this->db->query($sql2)->fetchAll();
if ($fetchData[0]['IsDirect'] == 0) {
$sql = "Select cty.CityId as PlaceSysId , cty.Title, TB_IC_Trans_Air_Stop.ArrivalTime "
. "From TB_IC_Trans_Air_Stop JOIN TB_Master_Geo_City [cty] ON TB_IC_Trans_Air_Stop.DestPlaceSysId=cty.CityId where 1 = 1";
if (!empty($flightNumber)) {
$sql .= " AND TB_IC_Trans_Air_Stop.AirInvenSysId = '" . $flightNumber . "' ";
}
$fetchData = $this->db->query($sql)->fetchAll();
}
//$sql .= " ORDER BY TB_IC_Trans_Air.DestPlaceSysId ";
return $fetchData;
}
public function getFlightDestinationPlaceList($flightNumber)
{
$sql2 = "Select cty.CityId as PlaceSysId, cty.Title, TB_IC_Trans_Air.LocalToTime as ArrivalTime, IsDirect From TB_IC_Trans_Air JOIN TB_Master_Geo_City [cty] ON TB_IC_Trans_Air.DestPlaceSysId=cty.CityId where 1 = 1 ";
if (!empty($flightNumber)) {
$sql2 .= " AND TB_IC_Trans_Air.AirInvenSysId = '" . $flightNumber . "' ";
}
$fetchData = $this->db->query($sql2)->fetchAll();
if ($fetchData[0]['IsDirect'] == 0) {
$sql = "Select cty.CityId as PlaceSysId , cty.Title, TB_IC_Trans_Air_Stop.ArrivalTime "
. "From TB_IC_Trans_Air_Stop JOIN TB_Master_Geo_City [cty] ON TB_IC_Trans_Air_Stop.DestPlaceSysId=cty.CityId where 1 = 1";
if (!empty($flightNumber)) {
$sql .= " AND TB_IC_Trans_Air_Stop.AirInvenSysId = '" . $flightNumber . "' ";
}
$fetchData = $this->db->query($sql)->fetchAll();
}
//$sql .= " ORDER BY TB_IC_Trans_Air.DestPlaceSysId ";
return $fetchData;
}
public function getFlightChangeDetailsList($FlightNumber, $OnwardSourcePlaceId, $OnwardDestinationPlaceId)
{
if ($FlightNumber != "") {
$sql1 = "Select TB_IC_Trans_Air.StopCount, CAST( CASE WHEN TB_IC_Trans_Air.IsDirect = 1 THEN 'Direct' ELSE 'Connecting' END AS char(15)) AS flightType, CONVERT(VARCHAR(10), LocalFromTime, 103) AS LocalFromTime "
. "From TB_IC_Trans_Air WHERE 1 = 1";
if (!empty($FlightNumber)) {
$sql1 .= " AND TB_IC_Trans_Air.AirInvenSysId = '" . $FlightNumber . "' ";
}
if (!empty($OnwardSourcePlaceId)) {
$sql1 .= " AND TB_IC_Trans_Air.SourcePlaceSysId = '" . $OnwardSourcePlaceId . "' ";
}
if (!empty($OnwardDestinationPlaceId)) {
$sql1 .= " AND TB_IC_Trans_Air.DestPlaceSysId = '" . $OnwardDestinationPlaceId . "' ";
}
$resData = $this->db->query($sql1)->fetch();
//echo "<pre>"; print_r($resData);
// echo $sql1;
// die;
if (empty($resData)) {
$sql = "Select AirInvenSysId, SourcePlaceSysId, DestPlaceSysId, Sequance, CONVERT(VARCHAR(10), DepartureTime, 103) AS LocalFromTime From TB_IC_Trans_Air_Stop WHERE 1 = 1 ";
if (!empty($FlightNumber)) {
$sql .= " AND TB_IC_Trans_Air_Stop.AirInvenSysId = '" . $FlightNumber . "' ";
}
// if(!empty($OnwardSourcePlaceId)) {
// $sql .= " AND TB_IC_Trans_Air_Stop.SourcePlaceSysId = '".$OnwardSourcePlaceId."' ";
// }
//
// if(!empty($OnwardDestinationPlaceId)) {
// $sql .= " AND TB_IC_Trans_Air_Stop.DestPlaceSysId = '".$OnwardDestinationPlaceId."' ";
// }
//echo $sql; die;
$resDataStopDet = $this->db->query($sql)->fetchAll();
$response = array();
$isSourceExist = false;
$isDirect = "Connecting";
$isSourceSeq = 0;
foreach ($resDataStopDet as $resDataStop) {
$response[] = array('AirInvenSysId' => $resDataStop['AirInvenSysId'], 'SourcePlaceSysId' => $resDataStop['SourcePlaceSysId'], 'DestPlaceSysId' => $resDataStop['DestPlaceSysId'], 'Sequance' => $resDataStop['Sequance'], 'LocalFromTime' => $resDataStop['LocalFromTime']);
if (!$isSourceExist) {
if ($OnwardSourcePlaceId == $resDataStop['SourcePlaceSysId']) {
$isSourceExist = true;
$isSourceSeq = $resDataStop['Sequance'];
$isSourceDate = $resDataStop['LocalFromTime'];
}
}
}
$isDestExist = false;
$isDestSeq = 0;
if ($isSourceExist) {
foreach ($resDataStopDet as $resDataStop) {
$response[] = array('AirInvenSysId' => $resDataStop['AirInvenSysId'], 'SourcePlaceSysId' => $resDataStop['SourcePlaceSysId'], 'DestPlaceSysId' => $resDataStop['DestPlaceSysId'], 'Sequance' => $resDataStop['Sequance'], 'LocalFromTime' => $resDataStop['LocalFromTime']);
if (!$isDestExist) {
if ($OnwardDestinationPlaceId == $resDataStop['DestPlaceSysId']) {
$isDestExist = true;
$isDestSeq = $resDataStop['Sequance'];
}
}
}
}
if ($isSourceExist && $isDestExist) {
$stopCount = $isDestSeq - $isSourceSeq;
if ($stopCount == 0) {
$isDirect = "Direct";
} else if ($stopCount < 0) {
$isDirect = "N/A";
}
} else {
$isDirect = "N/A";
}
//echo "<pre>"; print_r($response);die;
$resData = array();
$resData = array('StopCount' => $stopCount, 'flightType' => $isDirect, 'LocalFromTime' => $isSourceDate);
}
} else {
$resData = array();
$resData = array('StopCount' => 'N/A', 'flightType' => 'N/A', 'LocalFromTime' => 'N/A');
}
// //echo "<pre>"; print_r($resData);die;
// $resData = $response;
//}
//echo $sql; die;
//$sql .= " ORDER BY TB_IC_Trans_Air.DestPlaceSysId ";
return $resData;
}
public function addFlightInventory($data)
{
try {
$this->db->insert('TB_MP_Inventory_Air', $data);
return $this->db->lastInsertId('TB_MP_Inventory_Air');
} catch (Exception $e) {
die('There has been an error. ' . $e->getMessage());
}
return 0;
}
public function getAllFlightInventoryList($intLoggedinAgencyId)
{
$sql = " SELECT TOP 10 InvnItemSysId, XRefFlightSysId, AgencySysId, IsItTwoWay, IsItParent, PairItemSysId, EconomyType, FromDate, ToDate, PurchasedQty, PublishQty, CostCurrency, TB_Master_Currency.Symbol AS Currency, TotalCost, TB_IC_Supplier.SupplierName AS SupplierType, TB_MP_Inventory_Air.StartPlaceSysId, TB_MP_Inventory_Air.DestPlaceSysId, "
. "TB_IC_Trans_Air.FlightNumber, TB_Master_Airline.Title As Airline, TB_Master_Geo_City.Title AS FromPlace, TMP.Title AS ToPlace, "
. "TB_IC_Trans_Air.StopCount, CAST( CASE WHEN TB_IC_Trans_Air.IsDirect = 1 THEN 'Direct' ELSE 'Connecting' END AS char(15)) AS flightType, TB_MP_Inventory_Air.IsActive, TB_MP_Inventory_Air.IsSaleStart "
. "FROM TB_MP_Inventory_Air "
. "JOIN TB_IC_Trans_Air ON TB_MP_Inventory_Air.XRefFlightSysId=TB_IC_Trans_Air.AirInvenSysId "
. "JOIN TB_Master_Currency ON TB_MP_Inventory_Air.CostCurrency=TB_Master_Currency.CurrencyType "
. "JOIN TB_IC_Supplier ON TB_MP_Inventory_Air.SupplierSysId=TB_IC_Supplier.SupplierSysId "
. "JOIN TB_Master_Airline ON TB_IC_Trans_Air.AirlineSysId=TB_Master_Airline.AirlineSysId "
. "JOIN TB_Master_Geo_City ON TB_MP_Inventory_Air.StartPlaceSysId=TB_Master_Geo_City.CityId "
. "JOIN TB_Master_Geo_City AS TMP ON TB_MP_Inventory_Air.DestPlaceSysId=TMP.CityId "
. "WHERE 1=1 ";
if (!empty($intLoggedinAgencyId)) {
$sql .= " AND AgencySysId = " . $intLoggedinAgencyId;
}
$sql .= " ORDER BY TB_MP_Inventory_Air.UpdateDate DESC ";
//echo $sql;exit;
return $this->db->query($sql)->fetchAll();
}
public function getFlightTimeInventoryDetailsByFlightId($XRefFlightSysId, $StartPlaceSysId, $DestPlaceSysId)
{
$sql = " SELECT AirInvenSysId, LocalFromTime as DepartureTime, LocalToTime as ArrivalTime FROM TB_IC_Trans_Air WHERE 1=1 ";
if (!empty($XRefFlightSysId)) {
$sql .= " AND AirInvenSysId = " . $XRefFlightSysId;
}
if (!empty($StartPlaceSysId)) {
$sql .= " AND SourcePlaceSysId = " . $StartPlaceSysId;
}
if (!empty($DestPlaceSysId)) {
$sql .= " AND DestPlaceSysId = " . $DestPlaceSysId;
}
//echo $sql;exit;
return $this->db->query($sql)->fetch();
}
public function getFlightInventoryList($InvnItemSysId, $intLoggedinAgencyId)
{
$sql = " SELECT * FROM TB_MP_Inventory_Air WHERE 1 = 1 ";
if (!empty($InvnItemSysId)) {
$sql .= " AND InvnItemSysId = " . $InvnItemSysId;
}
if (!empty($intLoggedinAgencyId)) {
$sql .= " AND AgencySysId = " . $intLoggedinAgencyId;
}
$sql .= " ORDER BY InvnItemSysId DESC ";
//echo $sql;exit;
return $this->db->query($sql)->fetchAll();
}
public function getStandardMarkupsAir($intAgencySysId)
{
$sql = " SELECT AgencySysId, TB_MP_StdMarkup_Air.MPType AS MPType_id, TB_Master_MPType.Title AS MPType, CustGrSysId,
Currency, StdMarkUpPer, TaxPer, DiscountPer, DiscountMsg,IsActive
FROM TB_MP_StdMarkup_Air INNER JOIN TB_Master_MPType
ON TB_MP_StdMarkup_Air.MPType = TB_Master_MPType.MPType
WHERE 1 = 1 AND ItemSourceType = '0' ";
if (!empty($intAgencySysId)) {
$sql .= " AND AgencySysId = " . $intAgencySysId;
}
//echo $sql; die;
return $this->db->query($sql)->fetchAll();
}
public function getAirMarketPlacesByAgencySysId($intAgencySysId, $InvnItemSysId)
{
$sql = "Select MPType From TB_MP_Inventory_Air_Alloc where 1 = 1";
if (!empty($intAgencySysId)) {
$sql .= " AND AgencySysId = '" . $intAgencySysId . "' ";
}
if (!empty($InvnItemSysId)) {
$sql .= " AND InvnItemSysId = '" . $InvnItemSysId . "' ";
}
$sql .= " ORDER BY MPType";
$rowset = $this->db->query($sql)->fetchAll();
if (count($rowset) > 0) {
foreach ($rowset as $row) {
$response[] = $row['MPType'];
}
} else {
$response = array();
}
return array_values($response);
}
public function getCustomizedMarkups($InvnItemSysId, $MPType = '', $intLoggedinAgencyId = '')
{
$sql = " SELECT InvnItemSysId, TB_MP_Inventory_Air_Alloc.MPType AS MPType_id, TB_Master_MPType.Title as MPType,
CustGrSysId, ItemType, AgencySysId, AllocateQnt, IsVirtualInven
,SoldQnt, RetrunQty, NetQnt, BlockQnt, MarkUpType, MarkUp, Currency, Price, Tax, Discount, DiscountType, DiscountAmount,
DiscountMsg, NetPrice, IsPublish, PublishDate, TB_MP_Inventory_Air_Alloc.Details, RateRuleSysId, TCDocSysId, ValidTill,
ApproveDate, CreateDate, UpdateDate, IsApproved, IsMarkForDel, IsActive
FROM TB_MP_Inventory_Air_Alloc INNER JOIN TB_Master_MPType
ON TB_MP_Inventory_Air_Alloc.MPType = TB_Master_MPType.MPType
WHERE 1 = 1 ";
if (!empty($MPType)) {
$sql .= " AND TB_MP_Inventory_Air_Alloc.MPType = " . $MPType;
}
if (!empty($InvnItemSysId)) {
$sql .= " AND InvnItemSysId = " . $InvnItemSysId;
}
if (!empty($intLoggedinAgencyId)) {
$sql .= " AND AgencySysId = " . $intLoggedinAgencyId;
}
$sql .= " ORDER BY InvnItemSysId, UpdateDate DESC ";
return $this->db->query($sql)->fetchAll();
}
public function getStandardMarkupsPolicies($intAgencySysId)
{
$sql = " SELECT AgencySysId, ArtifectSysId, Title, Writeup FROM TB_MP_Inventory_Air_Artifect_Std WHERE 1 = 1 ";
if (!empty($intAgencySysId)) {
$sql .= " AND AgencySysId = " . $intAgencySysId;
}
$sql .= " ORDER BY AgencySysId";
return $this->db->query($sql)->fetch();
}
public function addMarkupSettings($data)
{
$this->db->insert('TB_MP_Inventory_Air_Alloc', $data);
return $this->db->lastInsertId();
}
public function updateIsItParentFlight($intLastInsertInwardId, $lastInvnItemSysId)
{
$where = array('InvnItemSysId = ? ' => $lastInvnItemSysId);
$update = array('PairItemSysId' => $intLastInsertInwardId);
$this->db->update('TB_MP_Inventory_Air', $update, $where);
}
public function deleteCustomizedMarkupSetting($InvnItemSysId, $MPType, $intLoggedinAgencyId)
{
$where = array('InvnItemSysId = ? ' => $InvnItemSysId, 'MPType = ? ' => $MPType, 'AgencySysId = ? ' => $intLoggedinAgencyId);
$this->db->delete('TB_MP_Inventory_Air_Alloc', $where);
}
public function getCustomizedMarkupsPolicies($InvnItemSysId)
{
$sql = " SELECT * FROM TB_MP_Inventory_Air_Artifect WHERE 1 = 1 ";
if (!empty($InvnItemSysId)) {
$sql .= " AND InvnItemSysId = " . $InvnItemSysId;
}
$sql .= " ORDER BY InvnItemSysId, UpdateDate DESC ";
//echo $sql; die;
return $this->db->query($sql)->fetch();
}
public function addMarkupPoliciesSettings($data)
{
$this->db->insert('TB_MP_Inventory_Air_Artifect', $data);
return $this->db->lastInsertId();
}
public function editMarkupPoliciesSettings($update, $InvnItemSysId)
{
$where = array('InvnItemSysId = ? ' => $InvnItemSysId);
$this->db->update('TB_MP_Inventory_Air_Artifect', $update, $where);
}
public function getAllFlightInventoryDetailsList($InvnItemSysId, $intLoggedinAgencyId)
{
$sql = " SELECT InvnItemSysId, XRefFlightSysId, IsItTwoWay, IsItParent, PairItemSysId, EconomyType, FromDate, ToDate, PurchasedQty, PublishQty,CostCurrency as Currency, TB_MP_Inventory_Air.TrxCurrency, TB_Master_Currency.Symbol AS CurrencySymbol, TB_MP_Inventory_Air.SupplierSysId, TB_IC_Supplier.SupplierName AS SupplierType, TotalCost, TB_MP_Inventory_Air.StartPlaceSysId, TB_MP_Inventory_Air.DestPlaceSysId, "
. "ltrim(rtrim(TB_IC_Trans_Air.FlightNumber)) FlightNumber, TB_IC_Trans_Air.StopCount, CAST( CASE WHEN TB_IC_Trans_Air.IsDirect = 1 THEN 'Direct' ELSE 'Connectiing' END AS char(15)) AS flightType, TB_Master_Geo_City.Title AS FromPlace, TMP.Title AS ToPlace "
. "FROM TB_MP_Inventory_Air "
. "JOIN TB_IC_Trans_Air ON TB_MP_Inventory_Air.XRefFlightSysId=TB_IC_Trans_Air.AirInvenSysId "
. "JOIN TB_Master_Currency ON TB_MP_Inventory_Air.CostCurrency=TB_Master_Currency.CurrencyType "
. "JOIN TB_IC_Supplier ON TB_MP_Inventory_Air.SupplierSysId=TB_IC_Supplier.SupplierSysId "
. "JOIN TB_Master_Geo_City ON TB_MP_Inventory_Air.StartPlaceSysId=TB_Master_Geo_City.CityId "
. "JOIN TB_Master_Geo_City AS TMP ON TB_MP_Inventory_Air.DestPlaceSysId=TMP.CityId "
. "WHERE 1=1 ";
if (!empty($InvnItemSysId)) {
$sql .= " AND InvnItemSysId = " . $InvnItemSysId;
}
if (!empty($intLoggedinAgencyId)) {
$sql .= " AND AgencySysId = " . $intLoggedinAgencyId;
}
//echo $sql;exit;
return $this->db->query($sql)->fetch();
}
public function updateFlightInventory($InvnItemSysId, $updateOnwardData)
{
$where = array('InvnItemSysId = ? ' => $InvnItemSysId);
$this->db->update('TB_MP_Inventory_Air', $updateOnwardData, $where);
}
public function updateFlightSaleInventory($InvnItemSysId, $actionValue, $intLoggedinAgencyId)
{
$update = array('IsSaleStart' => $actionValue);
$where = array('InvnItemSysId = ? ' => $InvnItemSysId, 'AgencySysId = ? ' => $intLoggedinAgencyId);
$this->db->update('TB_MP_Inventory_Air', $update, $where);
}
public function updateIsPairFlightSaleInventory($InvnItemSysId, $actionValue, $intLoggedinUserId)
{
$update = array('IsSaleStart' => $actionValue);
$where = array('InvnItemSysId = ? ' => $InvnItemSysId, 'AgencySysId = ? ' => $intLoggedinUserId);
$this->db->update('TB_MP_Inventory_Air', $update, $where);
}
public function updateFlightActivateAndDeactivateInventory($InvnItemSysId, $actionValue, $intLoggedinAgencyId)
{
$update = array('IsActive' => $actionValue);
$where = array('InvnItemSysId = ? ' => $InvnItemSysId, 'AgencySysId = ? ' => $intLoggedinAgencyId);
$this->db->update('TB_MP_Inventory_Air', $update, $where);
}
public function updateIsPairFlightActivateAndDeactivateInventory($InvnItemSysId, $actionValue, $intLoggedinUserId)
{
$update = array('IsActive' => $actionValue);
$where = array('InvnItemSysId = ? ' => $InvnItemSysId, 'AgencySysId = ? ' => $intLoggedinUserId);
$this->db->update('TB_MP_Inventory_Air', $update, $where);
}
public function getCurrencyConversionPrice($agentBaseCurrency, $inventoryCurrency)
{
$sql = " SELECT * FROM TB_IC_CurrencyConversion WHERE 1=1 AND IsActive='1' ";
if (!empty($agentBaseCurrency)) {
$sql .= " AND FCurrencyType = " . $agentBaseCurrency;
}
if (!empty($inventoryCurrency)) {
$sql .= " AND TCurrencyType = " . $inventoryCurrency;
}
return $this->db->query($sql)->fetch();
}
public function getCurrencyConversionPaddingPrice($agentBaseCurrency)
{
$sql = " SELECT * FROM TB_System_CurrencyExcPadding WHERE 1=1 AND IsActive='1' ";
if (!empty($agentBaseCurrency)) {
$sql .= " AND CurrencyType = " . $agentBaseCurrency;
}
return $this->db->query($sql)->fetch();
}
public function getSearchResult($data)
{
$sql = " SELECT XRefFlightSysId, FlightNumber FROM TB_MP_Inventory_Air
INNER JOIN TB_IC_Trans_Air ON TB_MP_Inventory_Air.XRefFlightSysId = TB_IC_Trans_Air.AirInvenSysId ";
$result = $this->db->query($sql)->fetchAll();
return $result;
}
public function getFilteredList()
{
$sql = " SELECT XRefFlightSysId, FlightNumber FROM TB_MP_Inventory_Air
INNER JOIN TB_IC_Trans_Air ON TB_MP_Inventory_Air.XRefFlightSysId = TB_IC_Trans_Air.AirInvenSysId ";
if (!empty($this->strCondition)) {
$sql .= $this->strCondition;
}
$result = $this->db->query($sql)->fetchAll();
return $result;
}
public function getFlightSourceCities()
{
$sql = " SELECT DISTINCT TB_IC_Trans_Air.sourcePlaceSysId, sourceCity.Title as SourceCityName, SourceAirportCode as AirportCode, sourceCountry.Title as Country
from TB_IC_Trans_Air INNER JOIN TB_MP_Inventory_Air
on TB_IC_Trans_Air.AirInvenSysId = TB_MP_Inventory_Air.InvnItemSysId
LEFT JOIN TB_Master_Geo_City as sourceCity on TB_IC_Trans_Air.sourcePlaceSysId = sourceCity.CityId
LEFT JOIN TB_Master_Geo_Country as sourceCountry on sourceCity.ContSysId = sourceCountry.ContId
";
$result = $this->db->query($sql)->fetchAll();
return $result;
}
public function getFlightDestinationCities()
{
$sql = " SELECT DISTINCT TB_IC_Trans_Air.DestPlaceSysId, destinationCity.Title as DestinationCityName, DestAirportCode as AirportCode,
destinationCountry.Title as Country
from TB_IC_Trans_Air INNER JOIN TB_MP_Inventory_Air
on TB_IC_Trans_Air.AirInvenSysId = TB_MP_Inventory_Air.InvnItemSysId
LEFT JOIN TB_Master_Geo_City as destinationCity on TB_IC_Trans_Air.DestPlaceSysId = destinationCity.CityId
LEFT JOIN TB_Master_Geo_Country as destinationCountry on destinationCity.ContSysId = destinationCountry.ContId
";
$result = $this->db->query($sql)->fetchAll();
return $result;
}
public function createAgencyCustomer($data)
{
//unset($data['paxType']); // Becoz paxType is not in table...
//unset($data['DOB']);// Becoz DOB is not in table...
$ContNo = $data['Contacts'];
$EmailId = $data['EmailId'];
$intAgencySysId = $data['AgencySysId'];
/* Check if already added or not */
$sql = " SELECT CustomerSysId FROM TB_Agency_Customer WHERE AgencySysId = '" . $intAgencySysId . "' AND ( EmailId = '" . $EmailId . "' OR Contacts = '" . $ContNo . "')";
$result = $this->db->query($sql)->fetch();
// echo "<pre>";
// print_r($result); exit;
if (count($result) == 0) {
$currentDate = date('Y-m-d H:i:s');
$dataCrm = array();
$dataCrm['EmailId'] = $data['EmailId'];
$dataCrm['Salutation'] = '1';
$dataCrm['PrimaryContactNumber'] = $data['Contacts'];
$dataCrm['FullName'] = $data['FirstName'] . " " . $data['LastName'];
$dataCrm['FirstName'] = $data['FirstName'];
$dataCrm['LastName'] = $data['LastName'];
$dataCrm['ActiveDate'] = $currentDate;
$dataCrm['IsApproved'] = '1';
$dataCrm['IsActive'] = '1';
$dataCrm['IsMarkForDelete'] = '0';
$this->db->insert('TB_CRM_CustomerAccount', $dataCrm);
$CRMCustSysId = $this->db->lastInsertId('TB_CRM_CustomerAccount');
if (!empty($CRMCustSysId)) {
//$intCustomerId = $this->getNextAgencyCustomerId($intAgencySysId);
//$data['CustomerSysId'] = $intCustomerId;
$data['CRMCustSysId'] = $CRMCustSysId;
$data['JoinDate'] = date('Y-m-d H:i:s');
// echo "<pre>";
// print_r($data); exit;
//$this->db->getProfiler()->setEnabled(true);
$this->db->insert('TB_Agency_Customer', $data);
// Zend_Debug::dump($this->db->getProfiler()->getLastQueryProfile()->getQuery());
// Zend_Debug::dump($this->db->getProfiler()->getLastQueryProfile()->getQueryParams());
// $this->db->getProfiler()->setEnabled(false);
// exit;
return $this->db->lastInsertId('TB_Agency_Customer');
//return $intCustomerId;
}
} else {
$where = array('CustomerSysId = ? ' => $result['CustomerSysId']);
$this->db->update('TB_Agency_Customer', $data, $where);
return trim($result['CustomerSysId']);
}
return 0;
}
public function getCRMCustSysId($data)
{
$ContNo = $data['Contacts'];
$EmailId = $data['EmailId'];
$sql = " SELECT CRMCustSysId FROM TB_CRM_CustomerAccount WHERE EmailId = '" . $EmailId . "' ";
$result = $this->db->query($sql)->fetch();
return $result['CRMCustSysId'];
}
public function createAgencyCustomerMembers($arrCustomerMembers, $AgencySysId, $intCustomerSysId, $intTPSysId = 0, $IsB2BProposal = 0)
{
if (count($arrCustomerMembers) == 0 || empty($AgencySysId) || empty($intCustomerSysId)) {
throw new Exception('Information missing');
}
$arrCustomerMembersIds = array();
foreach ($arrCustomerMembers as $member) {
if (trim($member['FirstName']) != '') {
//unset($member['paxType']); // Becoz paxType is not in table...
//unset($member['DOB']);// Becoz DOB is not in table...
// echo "<pre>";print_r($member);die;
//$intMemberSysId = $this->getNextAgencyCustomerMemberId($AgencySysId, $intCustomerSysId);
$member['AgencySysId'] = $AgencySysId;
$member['CustomerSysId'] = $intCustomerSysId;
//$member['MemberSysId'] = $intMemberSysId;
$MemberSysId = (isset($member['MemberSysId'])) ? (int) $member['MemberSysId'] : 0;
if ($IsB2BProposal == 0 && $MemberSysId == 0) {
$MemberSysId = $this->checkIfMemberExists($member);
}
if (empty($MemberSysId)) {
$member['JoinDate'] = date('Y-m-d H:i:s');
$this->db->insert('TB_Agency_Customer_Members', $member);
$NewMemberSysId = $this->db->lastInsertId('TB_Agency_Customer_Members');
} else {
if (isset($member['MemberSysId'])) {
unset($member['MemberSysId']);
}
$where = array('MemberSysId = ? ' => $MemberSysId);
$this->db->update('TB_Agency_Customer_Members', $member, $where);
$NewMemberSysId = $MemberSysId;
}
$arrCustomerMembersIds[] = $NewMemberSysId;
}
}
return $arrCustomerMembersIds;
}
public function checkIfMemberExists($member)
{
$EmailId = (isset($member['EmailId']) && trim($member['EmailId']) != '') ? "EmailId = '" . trim($member['EmailId']) . "' AND" : '';
$Relation = (isset($member['Relation']) && trim($member['Relation']) != '') ? "Relation = '" . trim($member['Relation']) . "' AND" : '';
$sql = " SELECT * from TB_Agency_Customer_Members "
. " WHERE " . $EmailId . " FirstName = '" . trim($member['FirstName']) . "' AND LastName = '" . trim($member['LastName']) . "' AND CustomerSysId = '" . $member['CustomerSysId'] . "' AND AgencySysId = '" . $member['AgencySysId'] . "' AND IsMarkForDelete = '0'";
$result = $this->db->query($sql)->fetch();
// echo "<pre>";print_r($result);die;
return $result['MemberSysId'];
}
public function checkMemberWithName($member)
{
$sql = " SELECT * from TB_Agency_Customer_Members "
. " WHERE FirstName = '" . trim($member['FirstName']) . "' AND LastName = '" . trim($member['LastName']) . "' AND CustomerSysId = '" . $member['CustomerSysId'] . "' AND AgencySysId = '" . $member['AgencySysId'] . "' AND IsMarkForDelete = '0'";
$result = $this->db->query($sql)->fetch();
// echo "<pre>";print_r($result);die;
return $result['MemberSysId'];
}
public function getNextAgencyCustomerId($intAgencySysId)
{
//$sql = " select (isnull(MAX(CustomerSysId),0) + 1 ) as CustomerSysId from TB_Agency_Customer where AgencySysId = " . $intAgencySysId;
$sql = " select (isnull(MAX(CustomerSysId),0) + 1 ) as CustomerSysId from TB_Agency_Customer ";
$result = $this->db->query($sql)->fetch();
return $result['CustomerSysId'];
}
public function getNextAgencyCustomerMemberId($intAgencySysId, $intCustomerSysId)
{
$sql = " select (isnull(MAX(MemberSysId),0) + 1 ) as MemberSysId from TB_Agency_Customer_Members "
. " where ( AgencySysId = " . $intAgencySysId . " AND CustomerSysId = " . $intCustomerSysId . " ) ";
$result = $this->db->query($sql)->fetch();
return $result['MemberSysId'];
}
public function booking($data)
{
$this->db->insert('TB_Agency_Customer_Trx', $data);
}
public function getTransactionDetail($AgencySysId, $CustomerSysId, $XRefTrxNo)
{
$sql = " SELECT * FROM TB_Agency_Customer_Trx "
. " WHERE AgencySysId = " . $AgencySysId . " AND CustomerSysId = " . $CustomerSysId . " AND XRefTrxNo = '" . $XRefTrxNo . "'";
return $this->db->query($sql)->fetchAll();
}
public function getAllFlightInventoryMarkupList($InvnItemSysId, $intLoggedinUserAgencySysId)
{
$sql = " SELECT * FROM TB_MP_Inventory_Air_Alloc WHERE 1 = 1 ";
if (!empty($InvnItemSysId)) {
$sql .= " AND InvnItemSysId = " . $InvnItemSysId;
}
if (!empty($intLoggedinUserAgencySysId)) {
$sql .= " AND AgencySysId = '" . $intLoggedinUserAgencySysId . "' ";
}
//echo $sql;exit;
return $this->db->query($sql)->fetchAll();
}
public function cloneMPInventoryFlight($InvnItemSysId, $updateCopyData, $intNewInvnItemSysId)
{
$currentDate = date('Y-m-d H:i:s');
if ($intNewInvnItemSysId != 0) {
$PairItemSysId = $intNewInvnItemSysId;
} else {
$PairItemSysId = 0;
}
$sql = " INSERT INTO TB_MP_Inventory_Air (InventoryType, XRefFlightSysId, IsItTwoWay, IsItParent, PairItemSysId
,AgencySysId, EconomyType, FromDate, ToDate, PurchasedQty, PublishQty
,BlockQty, SoldQty, ReturnQty, NetInHandQty, CostCurrency
,TrxCurrency, SupplierSysId, TotalCost, Tax, SaleAmount,DefaultPrice
,Brief,IfAnyOffer,OfferMsg,OfferImage
,StartPlaceSysId, DestPlaceSysId, SearchTags, IxFixedDateInven
,CreatorSysId
,CreateDate
,UpdateDate
,IsActive
,IsMarkForDelete
,IsApproved)
SELECT InventoryType, XRefFlightSysId, IsItTwoWay, IsItParent, '" . $PairItemSysId . "'
,AgencySysId, EconomyType, '" . $updateCopyData['strDateOnward'] . "', '" . $updateCopyData['strDateOnward'] . "', '" . $updateCopyData['PurchasedQty'] . "', '" . $updateCopyData['PublishQty'] . "'
,BlockQty, SoldQty, ReturnQty, '" . $updateCopyData['NetInHandQty'] . "', '" . $updateCopyData['CostCurrency'] . "'
,TrxCurrency, '" . $updateCopyData['SupplierSysId'] . "', '" . $updateCopyData['TotalCost'] . "', Tax, SaleAmount,DefaultPrice
,Brief,IfAnyOffer,OfferMsg,OfferImage
,'" . $updateCopyData['StartPlaceSysId'] . "', '" . $updateCopyData['DestPlaceSysId'] . "', SearchTags, IxFixedDateInven
,CreatorSysId
,'" . $currentDate . "'
,'" . $currentDate . "'
,'0'
,'0'
,'0' FROM TB_MP_Inventory_Air WHERE InvnItemSysId = " . $InvnItemSysId;
//echo $sql; die;
$this->db->query($sql);
return $this->db->lastInsertId('TB_MP_Inventory_Air');
}
public function getAirlineName($where)
{
$where = trim($where);
$response = array();
$arrAirline = array('AirlineSysId', 'Code', 'Title');
$select = $this->db->select();
$select->from(array('tbl' => "TB_Master_Airline"), $arrAirline);
$select->where($where);
$select->where("tbl.IsActive = ?", 1);
$select->where("tbl.IsMarkForDelete = ?", 0);
$result = $this->db->fetchAll($select);
if (count($result) > 0) {
foreach ($result as $row) {
$response[] = array('AirlineSysId' => trim($row['AirlineSysId']), 'Code' => trim($row['Code']), 'label' => $row['Title']);
}
}
return $response;
}
public function getAirPortAutoSuggest($where)
{
$where = trim($where);
$response = array();
$arrAirPort = array('AirportCode', 'AirportTitle', 'CityId as AirportCityId', 'CountryCode', 'ISO');
$arrCity = array('CityId', 'TBBCityId', 'Title as cityTitle', 'Alias as cityAlias', 'Code as cityCode', 'ContSysId');
$select = $this->db->select();
$select->from(array('tbl' => "TB_Master_Geo_City"), $arrCity);
$select->joinInner(array('tb2' => "TB_Master_Airport"), "tbl.CityId = tb2.CityId", $arrAirPort);
$select->where($where);
$select->where("tbl.IsActive = ?", 1);
$select->where("tbl.IsApproved = ?", 1);
$select->where("tb2.IsActive = ?", 1);
$select->where("tb2.IsMarkForDel = ?", 0);
// echo $select;
$result = $this->db->fetchAll($select);
if (count($result) > 0) {
foreach ($result as $row) {
if (!empty(trim($row['CountryCode'])) && !empty(trim($row['AirportCode']))) {
$response[] = array('AirportCode' => $row['AirportCode'], 'ISO' => $row['ISO'], 'CountryCode' => trim($row['CountryCode']), 'AirportTitle' => trim($row['AirportTitle']), 'CityID' => $row['CityId'], 'CityTitle' => $row['cityTitle'], 'ContSysId' => $row['ContSysId'], 'label' => trim($row['cityTitle']) . ", " . trim($row['CountryCode']) . " - " . trim($row['AirportTitle']) . " (" . trim($row['AirportCode']) . ")");
} else {
$response[] = array('AirportCode' => $row['AirportCode'], 'ISO' => $row['ISO'], 'CountryCode' => trim($row['CountryCode']), 'AirportTitle' => trim($row['AirportTitle']), 'CityID' => $row['CityId'], 'CityTitle' => $row['cityTitle'], 'ContSysId' => $row['ContSysId'], 'label' => trim($row['cityTitle']) . ", " . trim($row['AirportTitle']) . " (" . trim($row['AirportCode']) . ")");
}
}
}
return $response;
}
// Added By Pardeep Panchal Ends Here ...
public function getFlightSearchList()
{
if ($this->strSelectedView == 'Count') {
$arrTransAPIAir = array('COUNT(*) AS total');
$arrTAAF = array('min("TAAF".OfferedFare) as minPrice,max("TAAF".OfferedFare) as maxPrice');
$arrAirLine = array('');
} else {
$arrTransAPIAir = array(
'TBQAirInvenSysId as AirInvenSysId',
'FlightNumber',
'AirlineSysId',
'FlyingMinutes as FlightDuration',
'SourceAirportCode',
'JourneyType',
'DestAirportCode',
'CurrencyType',
'ApiResultIndex',
'apiTraceId',
'FareClass',
'FromUTCTime as ArrivalTime',
'ToUTCTime as DepartureTime',
'StopCount',
'TripType',
'IsRefundable',
'NoOfSeatAvailable',
new Zend_Db_Expr('1 as IsFromAPI')
);
$arrTAAF = array('PublishedFare', 'CommissionEarned', 'PLBEarned', 'IncentiveEarned', 'OfferedFare');
$arrAirLine = array('Code as AirlineCode', 'Title as AirlineName');
}
// For Api Flights DB Results...
$select = $this->db->select();
$select->from(array('t1' => 'TB_IC_Trans_API_Air'), $arrTransAPIAir);
$select->joinLeft(array('TAAF' => 'TB_IC_Trans_API_Air_Fare'), 'TAAF.TBOAirInvenAPISysId = t1.TBQAirInvenSysId ', $arrTAAF);
$select->joinLeft(array('t2' => 'TB_Master_Airline'), 't2.AirlineSysId = t1.AirlineSysId ', $arrAirLine);
$select->where("t1.IsActive = ?", 1);
$select->where("t1.IsExpired = ?", 0);
if (!empty($this->intJourneyType)) {
$select->where("t1.JourneyType = ?", $this->intJourneyType);
}
if (!empty($this->intTripType)) {
$select->where("t1.TripType = ?", $this->intTripType);
}
if (!empty($this->strCondition)) {
$select->where($this->strCondition);
}
if ($this->strSelectedView == 'list') {
if (!empty($this->orderBy)) {
$select->order('PublishedFare ' . $this->orderBy);
} else {
$select->order(array('t1.PublishedFare ASC'));
}
$select->limitPage($this->intPageNo, $this->intListPerPage);
}
//echo $select;exit;
$result = $this->db->fetchAll($select);
return $result;
}
public function getInterNationalFlightSearchList()
{
if ($this->strSelectedView == 'Count') {
$arrTransAPIAir = array('COUNT(*) AS total');
$arrTAAF = array('min("TAAF".OfferedFare) as minPrice,max("TAAF".OfferedFare) as maxPrice');
//$arrTransAPIAir2 = array('');
//$arrAirLine = array('');
} else {
$arrTransAPIAir = array('TBQAirInvenSysId', 'PublishedFare', 'ApiResultIndex', 'apiTraceId');
$arrTAAF = array('');
//$arrAirLine = array('Code as AirlineCode','Title as AirlineName');
}
// For Api Flights DB Results...
$select = $this->db->select();
$select->from(array('t1' => 'TB_IC_Trans_API_Air'), $arrTransAPIAir);
$select->joinLeft(array('TAAF' => 'TB_IC_Trans_API_Air_Fare'), 'TAAF.TBOAirInvenAPISysId = t1.TBQAirInvenSysId ', $arrTAAF);
// $select->joinLeft(array('t3' => 'TB_Master_Airline'),'t3.AirlineSysId = t1.AirlineSysId ',$arrAirLine);
$select->where("t1.IsInternational = ?", 1);
$select->where("t1.AirInvenSysId = ?", 0);
$select->where("t1.IsActive = ?", 1);
$select->where("t1.IsExpired = ?", 0);
if (!empty($this->intJourneyType)) {
$select->where("t1.JourneyType = ?", $this->intJourneyType);
}
if (!empty($this->intTripType)) {
$select->where("t1.TripType = ?", $this->intTripType);
}
if (!empty($this->strCondition)) {
$select->where($this->strCondition);
}
if ($this->strSelectedView == 'list') {
if (!empty($this->orderBy)) {
$select->order('t1.PublishedFare ' . $this->orderBy);
} else {
$select->order(array('t1.PublishedFare ASC'));
}
$select->limitPage($this->intPageNo, $this->intListPerPage);
}
//echo $select;exit;
$result = $this->db->fetchAll($select);
//echo "<pre>";print_r($result);exit;
return $result;
}
public function getInterNationalFlightByMasterId($strIds)
{
$arrTransAPIAir = array(
'TBQAirInvenSysId',
'AirInvenSysId',
'SourceAirportCode',
'TripType',
'DestAirportCode',
'FlightNumber',
'TotalFlightMembers',
'PublishedFare as PublishedFareTotal',
'AirlineSysId',
'CurrencyType',
'FlyingMinutes as FlightDuration',
'FromUTCTime as OriginDepartureTime',
'ToUTCTime as DestArrivalTime',
'StopCount',
'IsRefundable',
'NoOfSeatAvailable',
'ApiResultIndex',
'apiTraceId',
'FareClass'
);
$arrTAAF = array('PublishedFare', 'CommissionEarned', 'PLBEarned', 'IncentiveEarned', 'OfferedFare');
$arrAirLine = array('Code as AirlineCode', 'Title as AirlineName');
$arrCurrency = array('Symbol AS CurrencySymbol');
// For Api Flights DB Results...
$select = $this->db->select();
$select->from(array('t1' => 'TB_IC_Trans_API_Air'), $arrTransAPIAir);
$select->joinLeft(array('t2' => 'TB_IC_Trans_API_Air_Fare'), 't2.TBOAirInvenAPISysId = t1.AirInvenSysId ', $arrTAAF);
$select->joinLeft(array('t3' => 'TB_Master_Airline'), 't3.AirlineSysId = t1.AirlineSysId ', $arrAirLine);
$select->joinLeft(array('t6' => 'TB_Master_Currency'), 't1.CurrencyType = t6.CurrencyType ', $arrCurrency);
$select->where("t1.IsInternational = ?", 1);
$select->where("t1.AirInvenSysId IN( $strIds )");
$select->where("t1.IsActive = ?", 1);
//echo $select->where("t1.IsExpired = ?",0);
$select->order("t1.TBQAirInvenSysId ASC");
$result = $this->db->fetchAll($select);
return $result;
}
public function getFlightSearchInvList()
{
if ($this->strSelectedView == 'Count') {
$arrAirAlloc = array('COUNT(*) AS total', 'min("t2".NetPrice) as minPrice,max("t2".NetPrice) as maxPrice');
} else {
$arrMpInventory = array('InventoryType');
$arrTransAir = array(
'AirInvenSysId',
'FlightNumber',
'AirlineSysId',
'FlyingMinutes as FlightDuration',
'SourceAirportCode',
'JourneyType',
'DestAirportCode',
'CurrencyType',
'CommissionEarned',
//'ApiResultIndex','apiTraceId','PublishedFare',
'FromUTCTime as ArrivalTime',
'ToUTCTime as DepartureTime',
//'StopCount','TripType',
new Zend_Db_Expr('0 as IsFromAPI')
);
$arrAirAlloc = array('NetPrice as PublishedFare');
$arrAirline = array('Code as AirlineCode', 'Title as AirlineName');
}
// For Api Flights DB Results...
$select = $this->db->select();
$select->from(array('t1' => 'TB_MP_Inventory_Air'), $arrMpInventory);
$select->joinInner(array('t2' => 'TB_IC_Trans_Air'), 't2.AirInvenSysId =t1.XRefFlightSysId', $arrTransAir);
$select->joinInner(array('t3' => 'TB_MP_Inventory_Air_Alloc'), 't3.InvnItemSysId = t1.InvnItemSysId ', $arrAirAlloc);
$select->joinInner(array('t4' => 'TB_Master_Airline'), 't4.AirlineSysId = t2.AirlineSysId', $arrAirline);
$select->where("t1.IsActive = ?", 1);
$select->where("t1.IsSaleStart = ?", 1);
$select->where("t1.IsMarkForDelete = ?", 0);
$select->where("t1.AgencySysId = ?", $this->intLoggedinUserAgencySysId);
if (!empty($this->strCondition)) {
$select->where($this->strCondition);
}
$select->limitPage($this->intPageNo, $this->intListPerPage);
//echo $select;exit;
$result = $this->db->fetchAll($select);
return $result;
}
public function getSelectedFlightDetails()
{
$arrTransAPIAir = array(
'apiTraceId',
'ApiResultIndex',
'IsLCC',
'TBQAirInvenSysId as AirInvenSysId',
'FlightNumber',
'AirlineSysId',
'FlyingMinutes as FlightDuration',
'SourceAirportCode',
'JourneyType',
'DestAirportCode',
'CurrencyType',
'ApiResultIndex',
'apiTraceId',
'ToUTCTime as ArrivalTime',
'FromUTCTime as DepartureTime',
'StopCount',
'TripType',
'FareClass',
'SourcePlaceSysId',
'DestPlaceSysId',
'IsRefundable',
'IsInternational',
'IsQuickProposal'
);
$arrAirLine = array('Code as AirlineCode', 'Title as AirlineName');
$arrAirCitySource = array('Title as SourcePlaceName');
$arrAirDestSource = array('Title as DestPlaceName');
$arrAirCurrency = array('Symbol as CurrencySymbol');
$arrTransAPIAirFare = array(
'PublishedFare',
'OtherCharges',
'Discount',
'ServiceFee',
'PublishedFare',
'CommissionEarned',
'IsGSTMandatory',
'PLBEarned',
'IncentiveEarned',
'OfferedFare',
'TdsOnCommission',
'TdsOnPLB',
'TdsOnIncentive'
);
$select = $this->db->select();
$select->from(array('t1' => 'TB_IC_Trans_API_Air'), $arrTransAPIAir);
$select->joinInner(array('t2' => 'TB_IC_Trans_API_Air_Fare'), 't2.TBOAirInvenAPISysId = t1.TBQAirInvenSysId ', $arrTransAPIAirFare);
$select->joinLeft(array('t3' => 'TB_Master_Airline'), 't3.AirlineSysId = t1.AirlineSysId ', $arrAirLine);
$select->joinLeft(array('t4' => 'TB_Master_Geo_City'), 't1.SourcePlaceSysId = t4.CityId ', $arrAirCitySource);
$select->joinLeft(array('t5' => 'TB_Master_Geo_City'), 't1.DestPlaceSysId = t5.CityId ', $arrAirDestSource);
$select->joinLeft(array('t6' => 'TB_Master_Currency'), 't1.CurrencyType = t6.CurrencyType ', $arrAirCurrency);
$select->where("t1.IsActive = ?", 1);
//$select->where("t1.IsExpired = ?",0);
if (!empty($this->strCondition)) {
$select->where($this->strCondition);
}
//echo $select;
$result = $this->db->fetchAll($select);
return $result;
}
public function FlightBookingResponse($intTpAirVersionId)
{
$arrTPAir = array('*');
$select = $this->db->select();
$select->from(array('t1' => 'TB_Agency_Customer_TravelPlan_Air'), $arrTPAir);
$select->where("t1.VersionId IN ($intTpAirVersionId)");
$result = $this->db->fetchAll($select);
return $result;
}
public function getSlectedFlightBookFareDetails()
{
$arrTPAir = array('Cost', 'TotalCost', 'TPIntSysId', 'InvnItemSysId');
$select = $this->db->select();
$select->from(array('t1' => 'TB_Agency_Customer_TravelPlan_Air'), $arrTPAir);
if (!empty($this->strConditionNew)) {
$select->where($this->strConditionNew);
}
if (!empty($this->strConditionNewVersionId)) {
$select->where($this->strConditionNewVersionId);
}
$select->order('InvnItemSysId ASC');
$result = $this->db->fetchAll($select);
return $result;
}
public function getSelectedFlightFareDetails()
{
$arrTransAPIFare = array(
'Discount',
'ServiceFee',
'PublishedFare',
'PLBEarned',
'IncentiveEarned',
'OfferedFare',
'TdsOnCommission',
'TdsOnPLB',
'TdsOnIncentive',
'IsGSTMandatory'
);
$arrAirCurrency = array('Symbol as CurrencySymbol');
$arrTransAPIFareBreakDown = array(
'FareBreakdownSysId',
'TBQAirInvenAPISysId',
'Currency',
'PassengerType',
'PassengerCount',
'BaseFare',
'Tax',
'YQTax',
'ServiceFee',
'Discount',
'AdditionalTxnFeeOfrd',
'AdditionalTxnFeePub',
'CommissionEarned',
'OtherCharges',
'FixedMarkUp',
'GSTOnMarkUp',
'BaggageCharges',
'MealCharges',
'SeatCharges'
);
$select = $this->db->select();
$select->from(array('t1' => 'TB_IC_Trans_API_Air_Fare'), $arrTransAPIFare);
$select->joinInner(array('t2' => 'TB_IC_Trans_API_Air_FareBreakdown'), "t2.TBQAirInvenAPISysId = t1.TBOAirInvenAPISysId", $arrTransAPIFareBreakDown);
$select->joinInner(array('t3' => 'TB_IC_Trans_API_Air'), "t3.TBQAirInvenSysId = t1.TBOAirInvenAPISysId", array('JourneyType', 'TripType'));
$select->joinLeft(array('t6' => 'TB_Master_Currency'), 't3.CurrencyType = t6.CurrencyType ', $arrAirCurrency);
if (!empty($this->strCondition)) {
$select->where($this->strCondition);
}
$select->order('TBQAirInvenAPISysId ASC');
//echo $select; exit;
$result = $this->db->fetchAll($select);
return $result;
}
public function getTempFlightSearchResultList()
{
$arrTransAPIAir = array(
'AirlineSysId',
'SourceAirportCode',
'JourneyType',
'DestAirportCode',
'CurrencyType',
'CommissionEarned',
'ApiResultIndex',
'apiTraceId'
);
$arrTransAPIAirStop = array(
'PublishedFare',
'TBQAirInvenSysId as AirInvenSysId',
'SourcePlaceSysId',
'DestPlaceSysId',
'ArrivalTime',
'DepartureTime',
'FlightDuration',
'AirlineCode',
'FlightNumber',
'OriginAirportCode',
'DestinationAirportCode',
'AirlineName'
);
// For Api Flights DB Results...
$select = $this->db->select();
$select->from(array('t1' => 'TB_IC_Trans_API_Air'), $arrTransAPIAir);
$select->joinInner(array('t2' => 'TB_IC_Trans_API_Air_Stop'), 't2.TBQAirInvenSysId = t1.TBQAirInvenSysId ', $arrTransAPIAirStop);
$select->where("t1.IsActive = ?", 1);
if (!empty($this->strCondition)) {
$select->where($this->strCondition);
}
$select->order("PublishedFare ASC");
$select->limitPage(1, 5);
$result = $this->db->fetchAll($select);
return $result;
}
public function getFlightItinerary($AirInvenSysId, $IsInterNational = false)
{
if (!$IsInterNational) {
$arrTransAirApiStop = array(
'OriginAirportCode',
'DestinationAirportCode',
'ArrivalTime',
'DepartureTime',
'FlightDuration',
'AirlineCode',
'AirlineName',
'FlightNumber',
'FareClass'
);
$select = $this->db->select();
$select->from(array('t1' => 'TB_IC_Trans_API_Air_Stop'), $arrTransAirApiStop);
$select->joinLeft(array('t2' => 'TB_Master_Geo_City'), 't2.CityId = t1.SourcePlaceSysId ', array('Title as OriginCity', 'CityId as SourcePlaceSysId'));
$select->joinLeft(array('t3' => 'TB_Master_Geo_City'), 't3.CityId = t1.DestPlaceSysId ', array('Title as DestinationCity', 'CityId as DestPlaceSysId'));
$select->where("t1.TBQAirInvenSysId = ?", $AirInvenSysId);
$select->order("Sequance");
//echo $select; exit;
$result = $this->db->fetchAll($select);
if (count($result) > 0) {
return $result;
} else {
$arrTransAirApi = array(
'SourceAirportCode as OriginAirportCode',
'DestAirportCode as DestinationAirportCode',
'FromUTCTime as ArrivalTime',
'ToUTCTime as DepartureTime',
'FlyingMinutes as FlightDuration',
'FlightNumber',
'FareClass'
);
$arrAirLine = array('Code as AirlineCode', 'Title as AirlineName');
$select = $this->db->select();
$select->from(array('t1' => 'TB_IC_Trans_API_Air'), $arrTransAirApi);
$select->joinLeft(array('t2' => 'TB_Master_Airline'), 't2.AirlineSysId = t1.AirlineSysId ', $arrAirLine);
$select->joinLeft(array('t3' => 'TB_Master_Geo_City'), 't3.CityId = t1.SourcePlaceSysId ', array('Title as OriginCity', 'CityId as SourcePlaceSysId'));
$select->joinLeft(array('t4' => 'TB_Master_Geo_City'), 't4.CityId = t1.DestPlaceSysId ', array('Title as DestinationCity', 'CityId as DestPlaceSysId'));
$select->where("t1.TBQAirInvenSysId = ?", $AirInvenSysId);
//echo $select; exit;
$result = $this->db->fetchAll($select);
}
} else {
$arrTransAirApiStop = array(
'OriginAirportCode',
'DestinationAirportCode',
'ArrivalTime',
'DepartureTime',
'FlightDuration',
'FlightNumber',
'FareClass',
'AirlineCode',
'AirlineName'
);
$select = $this->db->select();
$select->from(array('t1' => 'TB_IC_Trans_API_Air_Stop'), $arrTransAirApiStop);
//$select->joinLeft(array('t2' => 'TB_IC_Trans_API_Air'),"t2.TBQAirInvenSysId = t1.TBQAirInvenSysId",array('SourcePlaceSysId','DestPlaceSysId'));
$select->joinLeft(array('t2' => 'TB_IC_Trans_API_Air'), "t2.TBQAirInvenSysId = t1.TBQAirInvenSysId", array('TripType'));
$select->joinLeft(array('t3' => 'TB_Master_Geo_City'), 't3.CityId = t2.SourcePlaceSysId ', array('Title as OriginCity', 'CityId as SourcePlaceSysId'));
$select->joinLeft(array('t4' => 'TB_Master_Geo_City'), 't4.CityId = t2.DestPlaceSysId ', array('Title as DestinationCity', 'CityId as DestPlaceSysId'));
$select->where("t2.AirInvenSysId = ?", $AirInvenSysId);
$select->order("t1.TBQAirInvenSysId");
$select->order("t1.Sequance");
//echo $select; exit;
$result = $this->db->fetchAll($select);
//echo "<pre>";print_r($result); exit;
if (count($result) > 0) {
if (count($result) < 4) {
$arrTransAirApi = array(
'SourceAirportCode as OriginAirportCode',
'DestAirportCode as DestinationAirportCode',
'FromUTCTime as ArrivalTime',
'ToUTCTime as DepartureTime',
'FlyingMinutes as FlightDuration',
'FlightNumber',
'FareClass'
);
$arrAirLine = array('Code as AirlineCode', 'Title as AirlineName');
$select = $this->db->select();
$select->from(array('t1' => 'TB_IC_Trans_API_Air'), $arrTransAirApi);
$select->joinLeft(array('t2' => 'TB_Master_Airline'), 't2.AirlineSysId = t1.AirlineSysId ', $arrAirLine);
$select->joinLeft(array('t3' => 'TB_Master_Geo_City'), 't3.CityId = t1.SourcePlaceSysId ', array('Title as OriginCity', 'CityId as SourcePlaceSysId'));
$select->joinLeft(array('t4' => 'TB_Master_Geo_City'), 't4.CityId = t1.DestPlaceSysId ', array('Title as DestinationCity', 'CityId as DestPlaceSysId'));
$select->where("t1.AirInvenSysId = ?", $AirInvenSysId);
if ($result[0]['TripType'] == 1) {
$select->where("t1.TripType = ?", 2);
} else {
$select->where("t1.TripType = ?", 1);
}
$select->order("t1.TBQAirInvenSysId ASC");
$select->limit(1);
$result1 = $this->db->fetchAll($select);
if ($result[0]['TripType'] == 2) {
$result = array_merge($result1, $result);
} else {
$result = array_merge($result, $result1);
}
}
return $result;
} else {
$arrTransAirApi = array(
'SourceAirportCode as OriginAirportCode',
'DestAirportCode as DestinationAirportCode',
'FromUTCTime as ArrivalTime',
'ToUTCTime as DepartureTime',
'FlyingMinutes as FlightDuration',
'FlightNumber',
'FareClass',
'SourcePlaceSysId',
'DestPlaceSysId'
);
$arrAirLine = array('Code as AirlineCode', 'Title as AirlineName');
$select = $this->db->select();
$select->from(array('t1' => 'TB_IC_Trans_API_Air'), $arrTransAirApi);
$select->joinLeft(array('t2' => 'TB_Master_Airline'), 't2.AirlineSysId = t1.AirlineSysId ', $arrAirLine);
$select->joinLeft(array('t3' => 'TB_Master_Geo_City'), 't3.CityId = t1.SourcePlaceSysId ', array('Title as OriginCity'));
$select->joinLeft(array('t4' => 'TB_Master_Geo_City'), 't4.CityId = t1.DestPlaceSysId ', array('Title as DestinationCity'));
$select->where("t1.AirInvenSysId = ?", $AirInvenSysId);
//echo $select; exit;
$result = $this->db->fetchAll($select);
}
}
return $result;
}
public function getFlightDetailsByTBQAirInvenSysId($TBQAirInvenSysId, $IsInterNational = NULL)
{
$arrTransAirApi = array(
'TBQAirInvenSysId',
'SourceAirportCode as OriginAirportCode',
'DestAirportCode as DestinationAirportCode',
'FromUTCTime as ArrivalTime',
'ToUTCTime as DepartureTime',
'PublishedFare',
'FlightNumber',
'FareClass',
'SourcePlaceSysId',
'DestPlaceSysId',
'JourneyType',
'apiTraceId',
'ApiResultIndex',
'IsRefundable',
'IsLCC'
);
$arrAirLine = array('Code as AirlineCode', 'Title as AirlineName');
$select = $this->db->select();
$select->from(array('t1' => 'TB_IC_Trans_API_Air'), $arrTransAirApi);
$select->joinLeft(array('t2' => 'TB_Master_Airline'), 't2.AirlineSysId = t1.AirlineSysId ', $arrAirLine);
if (!$IsInterNational) {
$select->where("t1.TBQAirInvenSysId = ?", $TBQAirInvenSysId);
//echo $select; exit;
$result = $this->db->fetchRow($select);
} else {
$select->where("t1.AirInvenSysId = ?", $TBQAirInvenSysId);
$select->order("t1.TBQAirInvenSysId");
//echo $select; exit;
$result = $this->db->fetchRow($select);
}
return $result;
}
public function getFlightFareRuleInformation($intAirInvenAPISysId)
{
$arrTransAirApi = array('FlightNumber', 'SourceAirportCode', 'DestAirportCode', 'AirlineSysId', 'SourcePlaceSysId', 'DestPlaceSysId');
$arrAirFare = array('Currency', 'BaseFare', 'Tax', 'PublishedFare', 'OfferedFare', 'CommissionEarned', 'PLBEarned', 'IncentiveEarned');
$select = $this->db->select();
$select->from(array('t1' => 'TB_IC_Trans_API_Air'), $arrTransAirApi);
$select->joinLeft(array('t2' => 'TB_IC_Trans_API_Air_Fare'), 't2.TBOAirInvenAPISysId = t1.TBQAirInvenSysId ', $arrAirFare);
$select->where("t1.TBQAirInvenSysId = ?", $intAirInvenAPISysId);
//echo $select; exit;
$result = $this->db->fetchAll($select);
return $result;
}
public function getFlightBaggageInformation($intAirInvenAPISysId, $IsInterNational = false)
{
if ($IsInterNational) {
$arrAirSegment = array('Baggage', 'CabinBaggage', 'OriginAirportCode as SourceAirportCode', 'DestAirportCode');
$select = $this->db->select();
$select->from(array('t1' => 'TB_IC_Trans_API_Air'), array());
$select->joinLeft(array('t2' => 'TB_IC_Trans_API_Air'), 't2.AirInvenSysId = t1.TBQAirInvenSysId ', array('FlightNumber', 'SourcePlaceSysId', 'DestPlaceSysId'));
$select->joinLeft(array('t3' => 'TB_IC_Trans_API_Air_Segment'), 't3.AirInvenAPISysId = t2.TBQAirInvenSysId ', $arrAirSegment);
$select->joinLeft(array('t4' => 'TB_Master_Geo_City'), 't4.CityId = t2.SourcePlaceSysId ', array('Title as OriginCity'));
$select->joinLeft(array('t5' => 'TB_Master_Geo_City'), 't5.CityId = t2.DestPlaceSysId ', array('Title as DestinationCity'));
$select->where("t1.TBQAirInvenSysId = ?", $intAirInvenAPISysId);
//echo $select; exit;
$result = $this->db->fetchAll($select);
//echo "<pre>";print_r($result);exit;
} else {
$arrTransAirApi = array('FlightNumber', 'SourceAirportCode', 'DestAirportCode', 'AirlineSysId', 'SourcePlaceSysId', 'DestPlaceSysId');
$arrAirSegment = array('Baggage', 'CabinBaggage');
$select = $this->db->select();
$select->from(array('t1' => 'TB_IC_Trans_API_Air'), $arrTransAirApi);
$select->joinLeft(array('t2' => 'TB_IC_Trans_API_Air_Segment'), 't2.AirInvenAPISysId = t1.TBQAirInvenSysId ', $arrAirSegment);
$select->joinLeft(array('t4' => 'TB_Master_Geo_City'), 't4.CityId = t1.SourcePlaceSysId ', array('Title as OriginCity'));
$select->joinLeft(array('t5' => 'TB_Master_Geo_City'), 't5.CityId = t1.DestPlaceSysId ', array('Title as DestinationCity'));
$select->where("t1.TBQAirInvenSysId = ?", $intAirInvenAPISysId);
//echo $select; exit;
$result = $this->db->fetchAll($select);
}
return $result;
}
public function updateTraceIdExpired($strTraceId)
{
$data = array();
$data['IsExpired'] = 1;
$where = array('apiTraceId = ? ' => $strTraceId);
return $this->db->update('TB_IC_Trans_API_Air', $data, $where);
}
public function getFlightDetailsForRoundTripHeader($AirInvenSysId)
{
$arrTransAPIAir = array(
'SourceAirportCode',
'DestAirportCode',
'FromUTCTime as ArrivalTime',
'ToUTCTime as DepartureTime',
'FlightNumber',
'FareClass',
'FlyingMinutes as FlightDuration',
'StopCount'
);
$arrTAAF = array('PublishedFare', 'CommissionEarned', 'PLBEarned', 'IncentiveEarned', 'OfferedFare');
$arrAirLine = array('Title as AirlineName');
$select = $this->db->select();
$select->from(array('t1' => 'TB_IC_Trans_API_Air'), $arrTransAPIAir);
$select->joinLeft(array('TAAF' => 'TB_IC_Trans_API_Air_Fare'), 'TAAF.TBOAirInvenAPISysId = t1.TBQAirInvenSysId ', $arrTAAF);
$select->joinLeft(array('t2' => 'TB_Master_Airline'), "t1.AirlineSysId = t2.AirlineSysId", $arrAirLine);
$select->where("t1.IsActive = ?", 1);
$select->where("t1.IsExpired = ?", 0);
$select->where("t1.TBQAirInvenSysId = ?", $AirInvenSysId);
$result = $this->db->fetchAll($select);
return $result;
}
public function GetAgencyCustomerDetailsForBookingQuery($TPSysId)
{
$agenyCustomer = array('Title', 'AgencySysId as B2BAgencySysId', 'Relation', 'Contacts', 'EmailId', 'FirstName', 'LastName', 'DOB', 'Address', 'CitySysId', 'PassportNo', 'PassportExpiry', 'PassportIssue', 'PassportNationality', 'countrycode as countrycodeISD', 'paxType', 'paxType as mpaxType', 'IsB2bAgent');
$arrGeoCity = array('Title as CityTitle');
$arrGeoCountry = array('Title as CountryTitle', 'Code as CountryCode');
$arrCustomerTravelPlan = array('StartDate', 'AgentSysId', 'SupplierSysId', 'StatusType', 'TPSysId', 'MasterTPSysId', 'CustomerSysId', 'RoomInfoJson', 'ChangeRequestId', 'IsB2BProposal');
$select = $this->db->select();
$select->from(array('tbl' => "TB_Agency_Customer_TravelPlan_Query"), $arrCustomerTravelPlan);
$select->joinLeft(array('tb4' => "TB_Agency_Customer"), "tbl.CustomerSysId = tb4.CustomerSysId", $agenyCustomer);
$select->joinLeft(array('tb2' => "TB_Master_Geo_City"), "tb2.CityId = tb4.CitySysId", $arrGeoCity);
$select->joinLeft(array('tb3' => "TB_Master_Geo_Country"), "tb3.ContId = tb2.ContSysId", $arrGeoCountry);
$select->where("tbl.TPSysId = ?", $TPSysId);
//echo $select; exit;
$arrResultPax = $this->db->fetchAll($select);
return $arrResultPax;
}
public function GetAgencyCustomerDetailsForBooking($id, $TPSysId, $IsReturn = 0)
{
$agenyCustomerTravelPlan_Air = array(new Zend_Db_Expr('DISTINCT(CustomerSysId) as CustomerSysId'), 'MemberSysId', 'IsLeadPax', 'TPSysId', 'paxType', 'VersionId');
$select = $this->db->select();
$select->from(array('tbl' => "TB_Agency_Customer_TravelPlan_Pax"), $agenyCustomerTravelPlan_Air);
//$select->distinct();
$select->where("tbl.TPSysId = ?", $TPSysId);
$select->order("tbl.VersionId ASC");
$arrResultPax = $this->db->fetchAll($select);
$arrTrevllerDetails = array();
if (count($arrResultPax) > 0) {
foreach ($arrResultPax as $result) {
$intCustomerSysId = trim($result['CustomerSysId']);
$intMemberSysId = trim($result['MemberSysId']);
if (!empty($intMemberSysId)) {
$agenyCustomerMember = array('MemberSysId', 'Relation', 'Address', 'CitySysId', 'paxType as mpaxType', 'Contacts as mContacts', 'EmailId as mEmailId');
$arrGeoCity = array('Title as CityTitle');
$arrGeoCountry = array('Title as CountryTitle', 'Code as CountryCode');
$agenyCustomer = array('Contacts', 'EmailId', 'countrycode as countrycodeISD', 'paxType', 'Address');
$TravelPlan_Pax = array('TPSysId', 'TPPaxSysId', 'GstNumber', 'GstCompany', 'GstEmail', 'GstPhone', 'GstAddress', 'isgstapply', 'IsLeadPax', 'Title', 'FirstName', 'LastName', 'DOB', 'passportno as PassportNo', 'passportnoexpiry as PassportExpiry', 'passporIssue as PassportIssue', 'PassportNationality as PassportNationality');
$select = $this->db->select();
$select->from(array('tbl' => "TB_Agency_Customer_Members"), $agenyCustomerMember);
$select->joinInner(array('tb2' => "TB_Agency_Customer"), "tb2.CustomerSysId = tbl.CustomerSysId", $agenyCustomer);
$select->joinLeft(array('tb3' => "TB_Master_Geo_City"), "tb3.CityId = tbl.CitySysId", $arrGeoCity);
$select->joinLeft(array('tb4' => "TB_Master_Geo_Country"), "tb4.ContId = tb3.ContSysId", $arrGeoCountry);
$select->joinRight(array('tb5' => "TB_Agency_Customer_TravelPlan_Pax"), "tb5.MemberSysId = tbl.MemberSysId", $TravelPlan_Pax);
$select->where("tbl.MemberSysId = ?", $intMemberSysId);
$select->where("tb5.TPSysId = ?", $TPSysId);
if ($IsReturn == 0) {
$select->where("tb5.IsReturn = 0 or tb5.IsReturn is null");
}
$select->order("tbl.MemberSysId ASC");
$result = $this->db->fetchAll($select);
} else {
$agenyCustomer = array('CustomerSysId', 'CustomerSysId as MemberSysId', 'Relation', 'countrycode as countrycodeISD', 'paxType', 'Contacts', 'EmailId', 'Address', 'CitySysId', 'paxType as mpaxType');
$arrGeoCity = array('Title as CityTitle');
$arrGeoCountry = array('Title as CountryTitle', 'Code as CountryCode');
$TravelPlan_Pax = array('TPSysId', 'TPPaxSysId', 'GstNumber', 'GstCompany', 'GstEmail', 'GstPhone', 'GstAddress', 'isgstapply', 'IsLeadPax', 'Title', 'FirstName', 'LastName', 'DOB', 'passportno as PassportNo', 'passportnoexpiry as PassportExpiry', 'passporIssue as PassportIssue', 'PassportNationality as PassportNationality');
$select = $this->db->select();
$select->from(array('tbl' => "TB_Agency_Customer"), $agenyCustomer);
$select->joinLeft(array('tb2' => "TB_Master_Geo_City"), "tb2.CityId = tbl.CitySysId", $arrGeoCity);
$select->joinLeft(array('tb3' => "TB_Master_Geo_Country"), "tb3.ContId = tb2.ContSysId", $arrGeoCountry);
$select->joinRight(array('tb4' => "TB_Agency_Customer_TravelPlan_Pax"), "tb4.CustomerSysId = tbl.CustomerSysId", $TravelPlan_Pax);
$select->where("tbl.CustomerSysId = ?", $intCustomerSysId);
$select->where("tb4.TPSysId = ?", $TPSysId);
if ($IsReturn == 0) {
$select->where("tb4.IsReturn = 0 or tb4.IsReturn is null");
}
$select->limit(1);
//echo $select;
$result = $this->db->fetchAll($select);
//$result[0]['MemberSysId'] = 0;
}
$arrTrevllerDetails[] = $result;
}
}
//echo "<pre>";print_r($result);exit;
return $arrTrevllerDetails;
}
public function GetAgencyCustomerOffline($id, $TPSysId, $IsReturn = 0)
{
$agenyCustomerTravelPlan_Air = array('CustomerSysId', 'MemberSysId', 'IsLeadPax', 'TPSysId', 'paxType');
$select = $this->db->select();
$select->from(array('tbl' => "TB_Agency_Customer_TravelPlan_Pax"), $agenyCustomerTravelPlan_Air);
//$select->distinct();
$select->where("tbl.TPSysId = ?", $TPSysId);
$select->order("tbl.paxType ASC");
$arrResultPax = $this->db->fetchAll($select);
// echo "<pre>";
// print_r($arrResultPax);
// die;
$arrTrevllerDetails = array();
if (count($arrResultPax) > 0) {
foreach ($arrResultPax as $result) {
$intCustomerSysId = trim($result['CustomerSysId']);
$intMemberSysId = trim($result['MemberSysId']);
if (!empty($intMemberSysId)) {
$agenyCustomerMember = array('MemberSysId', 'Title', 'Relation', 'FirstName', 'LastName', 'DOB', 'Address', 'CitySysId', 'PassportNo', 'PassportExpiry', 'PassportIssue', 'PassportNationality', 'paxType as mpaxType', 'Contacts as mContacts', 'EmailId as mEmailId');
$arrGeoCity = array('Title as CityTitle');
$arrGeoCountry = array('Title as CountryTitle', 'Code as CountryCode');
$agenyCustomer = array('Contacts', 'EmailId', 'countrycode as countrycodeISD', 'paxType');
$TravelPlan_Pax = array('TPPaxSysId', 'GstNumber', 'GstCompany', 'GstEmail', 'GstPhone', 'GstAddress', 'isgstapply', 'IsLeadPax');
$select = $this->db->select();
$select->from(array('tbl' => "TB_Agency_Customer_Members"), $agenyCustomerMember);
$select->joinInner(array('tb2' => "TB_Agency_Customer"), "tb2.CustomerSysId = tbl.CustomerSysId", $agenyCustomer);
$select->joinLeft(array('tb3' => "TB_Master_Geo_City"), "tb3.CityId = tbl.CitySysId", $arrGeoCity);
$select->joinLeft(array('tb4' => "TB_Master_Geo_Country"), "tb4.ContId = tb3.ContSysId", $arrGeoCountry);
$select->joinRight(array('tb5' => "TB_Agency_Customer_TravelPlan_Pax"), "tb5.MemberSysId = tbl.MemberSysId", $TravelPlan_Pax);
$select->where("tbl.MemberSysId = ?", $intMemberSysId);
$select->where("tb5.TPSysId = ?", $TPSysId);
if ($IsReturn == 0) {
$select->where("tb5.IsReturn = 0 or tb5.IsReturn is null");
}
$select->order("tbl.MemberSysId ASC");
$result = $this->db->fetchAll($select);
} else {
$agenyCustomer = array('Title', 'CustomerSysId', 'CustomerSysId as MemberSysId', 'Relation', 'Contacts', 'EmailId', 'FirstName', 'LastName', 'DOB', 'Address', 'CitySysId', 'PassportNo', 'PassportExpiry', 'PassportIssue', 'PassportNationality', 'countrycode as countrycodeISD', 'paxType', 'paxType as mpaxType');
$arrGeoCity = array('Title as CityTitle');
$arrGeoCountry = array('Title as CountryTitle', 'Code as CountryCode');
$TravelPlan_Pax = array('TPPaxSysId', 'GstNumber', 'GstCompany', 'GstEmail', 'GstPhone', 'GstAddress', 'isgstapply', 'IsLeadPax');
$select = $this->db->select();
$select->from(array('tbl' => "TB_Agency_Customer"), $agenyCustomer);
$select->joinLeft(array('tb2' => "TB_Master_Geo_City"), "tb2.CityId = tbl.CitySysId", $arrGeoCity);
$select->joinLeft(array('tb3' => "TB_Master_Geo_Country"), "tb3.ContId = tb2.ContSysId", $arrGeoCountry);
$select->joinRight(array('tb4' => "TB_Agency_Customer_TravelPlan_Pax"), "tb4.CustomerSysId = tbl.CustomerSysId", $TravelPlan_Pax);
$select->where("tbl.CustomerSysId = ?", $intCustomerSysId);
$select->where("tb4.TPSysId = ?", $TPSysId);
if ($IsReturn == 0) {
$select->where("tb4.IsReturn = 0 or tb4.IsReturn is null");
}
$select->limit(1);
//echo $select;
$result = $this->db->fetchAll($select);
//$result[0]['MemberSysId'] = 0;
}
$arrTrevllerDetails[] = $result;
}
}
//echo "<pre>";print_r($result);exit;
return $arrTrevllerDetails;
}
public function GetCustomerListForFlightBooking($TPSysId, $IsReturn = null, $VersionId = null, $IsApproved = null)
{
if ($IsReturn == '0') {
$IsReturnCheck = 2;
} else {
$IsReturnCheck = '';
}
$agenyCustomerTravelPlan_Air = array(
'TPPaxSysId',
'VersionId',
'MemberSysId',
'IsLeadPax',
'TicketId',
'paxType',
'Salutation',
'Title',
'TPSysId',
'CheckinBaggage',
'CabinBaggage',
'IsReturn',
'TicketNumber',
'TicketFullJson',
'ChangeRequestId',
'ChangeRequestRes',
'servicefee',
'amendmentCharges',
'refundableamount',
'ChangeRequestStatus',
'CustomerSysId',
'FirstName',
'LastName',
'DOB',
'passportnoexpiry',
'passportno',
'passporIssue',
'Sectors',
'BagPrice',
'MealPrice',
'SeatPrice',
'SelectedBag',
'SelectedMeal',
'SelectedSeat',
'isWheelChair',
'isgstapply',
'GstNumber',
'GstCompany',
'GstEmail',
'GstPhone',
'GstAddress'
);
$arrCustomerTravelPlan = array('AgencySysId', 'CustomerSysId as TPCustomerSysId');
$select = $this->db->select();
$select->from(array('tbl' => "TB_Agency_Customer_TravelPlan_Pax"), $agenyCustomerTravelPlan_Air);
$select->joinInner(array('tb2' => "TB_Agency_Customer_TravelPlan"), "tb2.TPSysId = tbl.TPSysId", $arrCustomerTravelPlan);
//$select->distinct();
$select->where("tbl.TPSysId = ?", $TPSysId);
if (!empty($IsApproved) && $IsApproved > 0) {
$select->where("tbl.IsApproved = ?", 1);
}
if (!empty($IsReturnCheck) || $IsReturn == 1) {
$select->where("tbl.IsReturn = ?", $IsReturn);
}
if (!empty($VersionId)) {
$select->where("tbl.VersionId = ?", $VersionId);
}
$select->order("tbl.TPPaxSysId ASC");
$arrResultPax = $this->db->fetchAll($select);
//echo "<pre>";print_r($result);exit;
return $arrResultPax;
}
public function getConnectingFlightInSearchList($AirInvenSysId)
{
$sql = " SELECT OriginAirportCode,DestinationAirportCode,ArrivalTime,DepartureTime,FlightDuration,"
. " AirlineCode,AirlineName,FlightNumber,FareClass,PublishedFare FROM TB_IC_Trans_API_Air_Stop WHERE 1=1";
$sql .= " AND TBQAirInvenSysId = '" . $AirInvenSysId . "' AND OriginAirportCode !='' AND DestinationAirportCode != '' ORDER BY Sequance";
//echo $sql; //exit;
$result = $this->db->query($sql)->fetchAll();
return $result;
}
public function getConnectingFlightInVentorySearchList($AirInvenSysId)
{
$sql = " SELECT OriginAirportCode,DestinationAirportCode,ArrivalTime,DepartureTime,FlightDuration,"
. " AirlineCode,AirlineName,FlightNumber,FareClass,PublishedFare FROM TB_IC_Trans_Air_Stop WHERE 1=1";
$sql .= " AND AirInvenSysId = '" . $AirInvenSysId . "' AND OriginAirportCode !='' AND DestinationAirportCode != '' ORDER BY Sequance";
//echo $sql; //exit;
$result = $this->db->query($sql)->fetchAll();
return $result;
}
public function insertTravelPlan($data)
{
$this->db->insert('TB_Agency_Customer_TravelPlan', $data);
return $this->db->lastInsertId('TB_Agency_Customer_TravelPlan');
}
public function insertTravelPlanPax($data)
{
$this->db->insert('TB_Agency_Customer_TravelPlan_Pax', $data);
return $this->db->lastInsertId('TB_Agency_Customer_TravelPlan_Pax');
}
public function deleteTravelPlanPax($TPSysId)
{
$where = array('TPSysId = ? ' => $TPSysId);
$this->db->delete('TB_Agency_Customer_TravelPlan_Pax', $where);
}
// by SIBO
public function updateTravelPlanPax($data, $TPPaxSysId)
{
$where = array('TPPaxSysId = ? ' => $TPPaxSysId);
$this->db->update('TB_Agency_Customer_TravelPlan_Pax', $data, $where);
}
public function updateTravelPlanPaxByTPPaxSysId($data, $TPPaxSysId)
{
if ($TPPaxSysId != '') {
$where = array("TPPaxSysId IN (" . $TPPaxSysId . ")");
$this->db->update('TB_Agency_Customer_TravelPlan_Pax', $data, $where);
}
}
public function updatetbtrx($data, $TrxSysId)
{
$where = array('TrxSysId = ? ' => $TrxSysId);
$this->db->update('TB_Trx', $data, $where);
}
public function updateCustomerTrx($data, $TrxSysId)
{
$where = array('TrxSysId = ? ' => $TrxSysId);
$this->db->update('TB_Agency_Customer_Trx', $data, $where);
}
public function updateAgencyWalletTrx($data, $TrxSysId)
{
$where = array('TrxId = ? ' => $TrxSysId);
$this->db->update('TB_Agency_Wallet_Trx', $data, $where);
}
public function updateTravelPlanPaxByTicketId($data, $TicketId)
{
//var_dump($data);
//var_dump($TicketId);die;
$where = array('TicketId = ? ' => $TicketId);
$this->db->update('TB_Agency_Customer_TravelPlan_Pax', $data, $where);
}
public function insertTravelPlanItenary($data)
{
$this->db->insert('TB_Agency_Customer_TravelPlan_Itenary', $data);
return $this->db->lastInsertId('TB_Agency_Customer_TravelPlan_Itenary');
}
public function insertTravelPlanIAir($data)
{
$this->db->insert('TB_Agency_Customer_TravelPlan_Air', $data);
return $this->db->lastInsertId('TB_Agency_Customer_TravelPlan_Air');
}
public function getInventoryFlightDetails($intInvnItemSysId)
{ // For Search Result Page
$sql = " SELECT t1.TotalCost,t1.StartPlaceSysId,t1.DestPlaceSysId,t2.MarkUp,t1.TrxCurrency,
t2.NetPrice
FROM TB_MP_Inventory_Air t1
INNER JOIN TB_MP_Inventory_Air_Alloc t2 on t2.InvnItemSysId = t1.InvnItemSysId
WHERE 1=1
AND t1.InvnItemSysId = '" . $intInvnItemSysId . "'";
//echo $sql; //exit;
$result = $this->db->query($sql)->fetchAll();
return $result;
}
//get customer details if already exists....
public function getAgencyCustomer($strSearchString)
{
/* Check if already added or not */
$sql = " SELECT TOP 1 EmailId,AgencySysId,CustomerSysId,Contacts,DOB,FirstName,LastName,Gender,Address,CitySysId FROM TB_Agency_Customer"
. " WHERE 1=1 AND ( EmailId = '" . $strSearchString . "' OR Contacts = '" . $strSearchString . "')";
//echo $sql; exit;
$result = $this->db->query($sql)->fetch();
return $result;
}
public function getGTXMarkups($intAirType, $AgencySysId = NULL)
{
$arrAirMarkups = array('Currency', 'AirType', 'MarkUpType', 'MarkUp');
$select = $this->db->select();
$select->from(array('tbl' => "TB_System_StdMarkup_Air_Alloc"), $arrAirMarkups);
if (!empty($AgencySysId)) {
$select->where("tbl.AgencySysId = ?", $AgencySysId);
} else {
$select->where("tbl.AgencySysId = ?", $this->intLoggedinUserAgencySysId);
}
$select->where("tbl.ItemSourceType = ?", 1);
$select->where("tbl.AirType = ?", $intAirType);
$select->where("tbl.MPType = ?", 1);
$select->where("tbl.IsApproved = ?", 1);
$select->where("tbl.IsMarkForDel = ?", 0);
$select->where("tbl.IsActive = ?", 1);
$select->limit(1);
//echo $select;exit;
$result = $this->db->fetchAll($select);
if (count($result) > 0) {
return $result;
} else {
$arrAirMarkups = array('Currency', 'AirType', 'MarkUpType', 'MarkUp');
$select = $this->db->select();
$select->from(array('tbl' => "TB_System_StdMarkup_Air"), $arrAirMarkups);
//$select->where("tbl.AgencySysId = ?", $this->intLoggedinUserAgencySysId);
$select->where("tbl.ItemSourceType = ?", 1);
$select->where("tbl.AirType = ?", $intAirType);
$select->where("tbl.MPType = ?", 1);
$select->where("tbl.IsApproved = ?", 1);
$select->where("tbl.IsMarkForDel = ?", 0);
$select->where("tbl.IsActive = ?", 1);
$select->limit(1);
$result = $this->db->fetchAll($select);
}
return $result;
}
public function getAgencyMarkups($intAirType, $AgencySysId = NULL)
{
$arrAirMarkups = array('Currency', 'AirType', 'MarkUpType', 'StdMarkUpPer', 'TaxPer', 'CommssionType', 'CommssionVal');
$select = $this->db->select();
$select->from(array('tbl' => "TB_MP_StdMarkup_Air"), $arrAirMarkups);
$select->joinleft(array('CUM' => "TB_Master_Currency"), "CUM.CurrencyType = tbl.Currency", ['Symbol']);
if (!empty($AgencySysId)) {
$select->where("tbl.AgencySysId = ?", $AgencySysId);
} else {
$select->where("tbl.AgencySysId = ?", $this->intLoggedinUserAgencySysId);
}
$select->where("tbl.ItemSourceType = ?", 1);
$select->where("tbl.AirType = ?", $intAirType);
$select->where("tbl.MPType = ?", 1);
$select->where("tbl.IsApproved = ?", 1);
$select->where("tbl.IsMarkForDel = ?", 0);
$select->where("tbl.IsActive = ?", 1);
$select->limit(1);
//echo $select; exit;
$result = $this->db->fetchAll($select);
return $result;
}
public function getAgencyMarkupsHotels($intAccomType, $AgencySysId = NULL)
{
$arrAirMarkups = array('Currency', 'AccomType', 'MarkUp', 'MarkUpType');
$select = $this->db->select();
$select->from(array('tbl' => "TB_MP_StdMarkup_Accom"), $arrAirMarkups);
$select->joinleft(array('CUM' => "TB_Master_Currency"), "CUM.CurrencyType = tbl.Currency", ['Symbol']);
if (!empty($AgencySysId)) {
$select->where("tbl.AgencySysId = ?", $AgencySysId);
} else {
$select->where("tbl.AgencySysId = ?", $this->intLoggedinUserAgencySysId);
}
$select->where("tbl.ItemSourceType = ?", 1);
$select->where("tbl.AccomType = ?", $intAccomType);
$select->where("tbl.MPType = ?", 1);
$select->where("tbl.IsApproved = ?", 1);
$select->where("tbl.IsMarkForDel = ?", 0);
$select->where("tbl.IsActive = ?", 1);
$select->limit(1);
//echo $select; exit;
$result = $this->db->fetchAll($select);
return $result;
}
public function getFlightBookingListCount($ReportingToSysIds = null, $Actioncronjob = null)
{
$arrCustomerTravelPlan = array('count(TP.TPSysId) AS TotalRecord');
$arrCustomerTravelPlanItenary = array();
$arrCustomerTravelPlanAir = array();
$arrAgency = array();
$arrAgencyOwner = array();
$arrCustomer = array();
$arrAirLine = array();
$LeadSource = array();
$select = $this->db->select();
$select->from(array('TP' => "TB_Agency_Customer_TravelPlan"), $arrCustomerTravelPlan);
$select->joinInner(array('TPQ' => "TB_Agency_Customer_TravelPlan_Query"), "TPQ.TPSysId = TP.MasterTPSysId", array());
$select->joinInner(array('TPI' => "TB_Agency_Customer_TravelPlan_Itenary"), "TPI.TPSysId = TP.TPSysId", $arrCustomerTravelPlanItenary);
$select->joinInner(array('TPA' => "TB_Agency_Customer_TravelPlan_Air"), "TPA.TPIntSysId = TPI.TPIntSysId", $arrCustomerTravelPlanAir);
$select->joinleft(array('ITAA' => "TB_IC_Trans_API_Air"), "ITAA.TBQAirInvenSysId = TPA.InvnItemSysId", array());
$select->join(array('AG' => "TB_Agency_User"), "AG.UserSysId = TP.AgentSysId", $arrAgency);
$select->join(array('AGU' => "TB_Agency_User"), "AGU.UserSysId = TPQ.AgentSysId", $arrAgencyOwner);
$select->joinleft(array('AGCY' => "TB_Agency"), "AGCY.AgencySysId = TP.AgencySysId", array());
$select->joinleft(array('AGCYT' => "TB_Agency"), "AGCYT.AgencySysId = TP.B2BAgencySysId", array());
$select->joinLeft(array('AC' => "TB_Agency_Customer"), "AC.CustomerSysId = TP.CustomerSysId", $arrCustomer);
$select->joinLeft(array('TMA' => 'TB_Master_Airline'), 'TMA.AirlineSysId = ITAA.AirlineSysId ', $arrAirLine);
$select->joinLeft(array('LDS' => 'TB_Master_LeadSource'), 'LDS.LeadSourceSysId = TP.LeadSourceSysId ', $LeadSource);
$select->joinLeft(array('TIS' => 'TB_IC_Supplier'), 'TIS.SupplierSysId = TP.SupplierSysId ', array());
if (!empty($this->intLoggedinUserAgencySysId)) {
$select->where("TP.AgencySysId = ?", $this->intLoggedinUserAgencySysId);
}
if ($ReportingToSysIds) {
$select->where("TP.AgentSysId IN (" . $ReportingToSysIds . ")");
}
$select->where("TPA.XRefPNR = ?", 1);
$select->where("TP.PlanType = ?", 1);
$select->where("TPA.IsBookingStatus = ?", 1);
$select->where('TP.StatusType IN(?)', array(7, 13, 17, 71, 124, 123, 104));
$select->where("TPA.AirlineRefPNR !=?", '');
//$select->order('TP.TPSysId DESC');
//echo $select; exit;
$result = $this->db->fetchRow($select);
return $result;
}
public function getFlightBookingList($ReportingToSysIds = null, $Actioncronjob = null)
{
$arrCustomerTravelPlan = array('IsB2BProposal', 'AgentSysId', 'RoomInfoJson', 'AgencySysId', 'StatusType', 'TPSysId', 'MasterTPSysId', 'CurrencyType', 'CustomerSysId', 'SourcePlaces', 'DestinationPlaces', 'PlanBookingId', 'VersionId', 'LeadSourceSysId', "((select SUM(adT1.Tax) FROM TB_Agency_Customer_TravelPlan_AddonServices adT1 where adT1.IsMarkForDelete = 0 AND TP.TPSysId = adT1.TPSysId AND (adT1.PlanType = 19))) as TotalTcs", "((select SUM(adT2.TotalCost) FROM TB_Agency_Customer_TravelPlan_AddonServices adT2 where adT2.IsMarkForDelete = 0 AND TP.TPSysId = adT2.TPSysId AND (adT2.PlanType = 23)) / (select COUNT(adT3.AddOnServices) FROM TB_Agency_Customer_TravelPlan_AddonServices adT3 where adT3.IsMarkForDelete = 0 AND TP.TPSysId = adT3.TPSysId AND (adT3.PlanType = 23)) ) as AddTotalCost");
$arrCustomerTravelPlanItenary = array('TPIntSysId');
$arrCustomerTravelPlanAir = array(
'FlightNumber',
'TotalCost',
'Cost',
'Discount',
'GTXMarkup',
'AgencyCommission',
'AgencyMarkUp',
'AgentServiceTaxAmount',
'SourceAirportCode',
'DestAirportCode',
'FromUTCTime',
'VersionId as AirVersionId',
'CreateDate as bookingDate',
'ToUTCTime',
'XRefBookingId',
'LocalToTime as returnDate',
'AgentsCustomServiceTaxAmount',
'MarkUp',
'APIBookingRes',
'TripType',
'XRefPNR',
'AirlineRefPNR',
'IsBookingStatus',
'flexiPayment',
'DeadLineDate',
'ApprovalDate',
'IsFixedTransport as IsHoldTicket',
);
//$arrCustomerTrx = array('TrxDate AS bookingDate');
$arrAgency = array('FirstName AS agtFirstName', 'LastName AS agtLastName', 'AgencyName as agtAgencyName');
$arrAgencyOwner = array('FirstName AS ownerFirstName', 'LastName AS ownerLastName', 'AgencyName as ownerAgencyName');
$arrCustomer = array('FirstName AS custFirstName', 'LastName as custLastName', 'EmailId as custEmailId', 'Contacts as custContacts', 'countrycode');
$arrAirLine = array('Code AS AirLineCode', 'Title as AirLineTitle');
$LeadSource = array('Title AS LeadSource');
$select = $this->db->select();
$select->from(array('TP' => "TB_Agency_Customer_TravelPlan"), $arrCustomerTravelPlan);
$select->joinInner(array('TPQ' => "TB_Agency_Customer_TravelPlan_Query"), "TPQ.TPSysId = TP.MasterTPSysId", array('TPSysId as QTPSysId'));
$select->joinInner(array('TPI' => "TB_Agency_Customer_TravelPlan_Itenary"), "TPI.TPSysId = TP.TPSysId", $arrCustomerTravelPlanItenary);
$select->joinInner(array('TPA' => "TB_Agency_Customer_TravelPlan_Air"), "TPA.TPIntSysId = TPI.TPIntSysId", $arrCustomerTravelPlanAir);
$select->joinleft(array('ITAA' => "TB_IC_Trans_API_Air"), "ITAA.TBQAirInvenSysId = TPA.InvnItemSysId", array('TBQAirInvenSysId', 'IsInternational', 'IsQuickProposal', 'ICSourceSysId', 'IsNewMigration'));
//$select->joinInner(array('TRX' => "TB_Agency_Customer_Trx"), "TRX.TPSysId = TP.TPSysId", $arrCustomerTrx);
// $select->joinInner(array('TRX' => "TB_Agency_Customer_Trx"), "TRX.TPSysId = TP.TPSysId AND TRX.TrxStatus = 1", $arrCustomerTrx);
$select->join(array('AG' => "TB_Agency_User"), "AG.UserSysId = TP.AgentSysId", $arrAgency);
$select->join(array('AGU' => "TB_Agency_User"), "AGU.UserSysId = TPQ.AgentSysId", $arrAgencyOwner);
$select->joinleft(array('AGCY' => "TB_Agency"), "AGCY.AgencySysId = TP.AgencySysId", ['DisplayName AS AgencyName']);
$select->joinleft(array('AGCYT' => "TB_Agency"), "AGCYT.AgencySysId = TP.B2BAgencySysId", ['Title AS B2BAgencyName', 'PrimaryEmail as B2BPrimaryEmail', 'ContactInfo as B2BContact', 'PrimaryContactName as B2BContactName']);
$select->joinLeft(array('AC' => "TB_Agency_Customer"), "AC.CustomerSysId = TP.CustomerSysId", $arrCustomer);
//$select->joinLeft(array('TPCTP' => "TB_Agency_Customer_TravelPlan_Pax"), "TP.TPSysId = TPCTP.TPSysId AND TPCTP.IsLeadPax = 1", array());
//$select->joinLeft(array('TPCM' => "TB_Agency_Customer_Members"), "TPCTP.MemberSysId = TPCM.MemberSysId AND TPCTP.IsLeadPax = 1", array());
$select->joinLeft(array('TMA' => 'TB_Master_Airline'), 'TMA.AirlineSysId = ITAA.AirlineSysId ', $arrAirLine);
$select->joinLeft(array('LDS' => 'TB_Master_LeadSource'), 'LDS.LeadSourceSysId = TP.LeadSourceSysId ', $LeadSource);
$select->joinLeft(array('TIS' => 'TB_IC_Supplier'), 'TIS.SupplierSysId = TP.SupplierSysId ', array('SupplierName'));
//$select->joinLeft(array('TBADDON' => "TB_Agency_Customer_TravelPlan_AddonServices"), "TP.TPSysId = TBADDON.TPSysId AND TBADDON.PlanType = 23", array('Cost as AddCost', 'Tax as AddTax', 'TotalCost as AddTotalCost'));
if (!empty($this->intLoggedinUserAgencySysId)) {
$select->where("TP.AgencySysId = ?", $this->intLoggedinUserAgencySysId);
}
if ($ReportingToSysIds) {
$select->where("TP.AgentSysId IN (" . $ReportingToSysIds . ")");
}
$select->where("TPA.XRefPNR = ?", 1);
$select->where("TP.PlanType = ?", 1);
$select->where("TPA.IsBookingStatus = ?", 1);
//$select->where("TPCTP.IsReturn = 0 OR TPCTP.IsReturn is null");
//$select->where("TRX.IsActive = ?", 1);
$select->where('TP.StatusType IN(?)', array(7, 13, 17, 71, 124, 123, 104));
$select->where("TPA.AirlineRefPNR !=?", '');
// $select->where('TP.StatusType IN(?)', array(17, 9, 18, 19, 71, 72));
//filter for date search =======================added BY SANDEEP NEGI 19-11-2020 START============================
if (!empty($this->searchArr) && isset($this->searchArr)) {
//echo "<pre>";print_r($this->searchArr); die("test");
$searchArr = $this->searchArr;
if (!empty($searchArr['dateFrom']) && !empty($searchArr['dateTo']) && trim($searchArr['dateFrom']) != '__-__-____' && trim($searchArr['dateTo']) != '__-__-____') {
$dateFrom = (isset($searchArr['dateFrom']) && !empty($searchArr['dateFrom'])) ? date("Y-m-d", strtotime($searchArr['dateFrom'])) : '';
$dateTo = (isset($searchArr['dateTo']) && !empty($searchArr['dateTo'])) ? date("Y-m-d", strtotime($searchArr['dateTo'])) : '';
if ($dateFrom != '1970-01-01' && $dateTo != '1970-01-01') {
$select->where("CAST(TPA.CreateDate AS DATE) BETWEEN '$dateFrom' AND '$dateTo'");
}
}
if (!empty($searchArr['TravelToDate']) && !empty($searchArr['TravelFromDate']) && trim($searchArr['TravelFromDate']) != '__-__-____' && trim($searchArr['TravelToDate']) != '__-__-____') {
$TravelFromDate = (isset($searchArr['TravelFromDate']) && !empty($searchArr['TravelFromDate'])) ? date("Y-m-d", strtotime($searchArr['TravelFromDate'])) : '';
$TravelToDate = (isset($searchArr['TravelToDate']) && !empty($searchArr['TravelToDate'])) ? date("Y-m-d", strtotime($searchArr['TravelToDate'])) : '';
if ($TravelFromDate != '1970-01-01' && $TravelToDate != '1970-01-01') {
$select->where("CONVERT(date,TPA.FromUTCTime) >= '$TravelFromDate' AND CONVERT(date,TPA.FromUTCTime) <= '$TravelToDate'");
}
}
$ProposalId = trim($searchArr['ProposalId']);
$ProposalId_filt = explode("/", $ProposalId);
$Flight = trim($searchArr['Flight']);
$BookingId = trim($searchArr['BookingId']);
$orgDest = trim($searchArr['orgDest']);
$CustomerName = trim($searchArr['CustomerName']);
$CustomerEmail = trim($searchArr['CustomerEmail']);
$CustomerPhone = trim($searchArr['CustomerPhone']);
$B2bagencyAgentId = trim($searchArr['hidden_b2bagency_agentId']);
$source = trim($searchArr['source']);
$pnrnumber = trim($searchArr['pnrnumber']);
$agencyUserFilter = (isset($searchArr['agencyUserFilter'])) ? (int) $searchArr['agencyUserFilter'] : 0;
$SupplierSysId = trim($searchArr['supplier_type_id']);
// echo $Flight; die();
// if ($dateFrom != '' && $dateTo != '') {
// $select->where("CAST(TRX.TrxDate AS DATE) BETWEEN '$dateFrom' AND '$dateTo'");
// }
// if (!empty($TravelDate) && isset($TravelDate)) {
// // $select->where('TP.TPSysId LIKE ?', "%$TravelDate%");
// }
if (!empty($ProposalId) && isset($ProposalId)) {
if (isset($ProposalId_filt[2])) {
$select->where('TP.TPSysId LIKE ?', $ProposalId_filt[2]);
} else {
$select->where('TP.TPSysId LIKE ?', $ProposalId);
}
}
if (!empty($SupplierSysId) && isset($SupplierSysId)) {
$select->where("TP.SupplierSysId IN (" . $SupplierSysId . ")");
}
if (!empty($Flight) && isset($Flight)) {
$whereConditions = " TPA.FlightNumber = '" . $Flight . "' OR TMA.Title like '%" . $Flight . "%'";
$select->where($whereConditions);
}
//echo $select; die();
if (!empty($BookingId) && isset($BookingId)) {
//$select->where('TPA.XRefBookingId = ?', $BookingId);
$select->where('TPA.XRefBookingId LIKE ?', "%$BookingId%");
}
if (!empty($pnrnumber) && isset($pnrnumber)) {
//$select->where('TPA.XRefBookingId = ?', $BookingId);
$select->where('TPA.AirlineRefPNR LIKE ?', "%$pnrnumber%");
}
if (!empty($orgDest) && isset($orgDest)) {
$whereCondition_fordes = " TP.SourcePlaces = '" . $orgDest . "' OR TP.DestinationPlaces like '%" . $orgDest . "%'";
$select->where($whereCondition_fordes);
// echo $select; die();
}
if (!empty($CustomerName) && isset($CustomerName)) {
$select->where('AC.FirstName LIKE ?', "%$CustomerName%");
//echo $select; die();
}
if (!empty($CustomerEmail) && isset($CustomerEmail)) {
$select->where('AC.EmailId = ?', $CustomerEmail);
}
if (!empty($CustomerPhone) && isset($CustomerPhone)) {
$select->where('AC.Contacts = ?', $CustomerPhone);
}
if (!empty($B2bagencyAgentId) && isset($B2bagencyAgentId)) {
$select->where('TP.B2BAgencySysId = ?', $B2bagencyAgentId);
}
if (isset($source) && $source != '') {
$select->where('TP.IsB2BProposal = ?', $source);
}
if ($agencyUserFilter > 0) {
$select->where('TPQ.AgentSysId = ?', $agencyUserFilter);
}
//echo $select; die();
}
// echo $select; die();
//filter for date search =======================added BY SANDEEP NEGI 19-11-2020 END============================
if (!empty($Actioncronjob)) {
$select->order('TP.TPSysId DESC');
$result = $this->db->fetchAll($select);
return $result;
} else {
$select->order('TP.TPSysId DESC');
// $result = $this->db->fetchAll($select);
// echo "<pre>" ; print_r($result) ; die();
return $select;
}
}
public function getFlightBookingListBNPL($AgencySysId, $ReportingToSysIds = null, $Actioncronjob = null, $ActionTPSysId = null)
{
$arrCustomerTravelPlan = array('IsB2BProposal', 'AgentSysId', 'RoomInfoJson', 'AgencySysId', 'StatusType', 'TPSysId', 'MasterTPSysId', 'CurrencyType', 'CustomerSysId', 'SourcePlaces', 'DestinationPlaces', 'PlanBookingId', 'VersionId', 'LeadSourceSysId', "((select SUM(TACT.PaidAmount) FROM TB_Agency_Customer_Trx TACT where TACT.TrxStatus = 1 AND TP.TPSysId = TACT.TPSysId)) as TotalPaidAmount", "((select SUM(adT1.Tax) FROM TB_Agency_Customer_TravelPlan_AddonServices adT1 where adT1.IsMarkForDelete = 0 AND TP.TPSysId = adT1.TPSysId AND (adT1.PlanType = 19))) as TotalTcs", "((select SUM(adT2.TotalCost) FROM TB_Agency_Customer_TravelPlan_AddonServices adT2 where adT2.IsMarkForDelete = 0 AND TP.TPSysId = adT2.TPSysId AND (adT2.PlanType = 23)) / (select COUNT(adT3.AddOnServices) FROM TB_Agency_Customer_TravelPlan_AddonServices adT3 where adT3.IsMarkForDelete = 0 AND TP.TPSysId = adT3.TPSysId AND (adT3.PlanType = 23)) ) as TotalProcessingFee");
$arrCustomerTravelPlanItenary = array('TPIntSysId');
$arrCustomerTravelPlanAir = array(
'FlightNumber',
'TotalCost',
'Cost',
'Discount',
'GTXMarkup',
'AgencyCommission',
'AgencyMarkUp',
'AgentServiceTaxAmount',
'SourceAirportCode',
'DestAirportCode',
'DeadLineDate',
'FromUTCTime',
'VersionId as AirVersionId',
'CreateDate as bookingDate',
'ToUTCTime',
'XRefBookingId',
'LocalToTime as returnDate',
'AgentsCustomServiceTaxAmount',
'MarkUp',
'APIBookingRes',
'TripType',
'XRefPNR',
'AirlineRefPNR',
'IsBookingStatus'
);
$arrAgency = array('FirstName AS agtFirstName', 'LastName AS agtLastName', 'AgencyName as agtAgencyName');
$arrAgencyOwner = array('FirstName AS ownerFirstName', 'LastName AS ownerLastName', 'AgencyName as ownerAgencyName');
$arrCustomer = array('FirstName AS custFirstName', 'LastName as custLastName', 'EmailId as custEmailId', 'Contacts as custContacts', 'countrycode');
$arrAirLine = array('Code AS AirLineCode', 'Title as AirLineTitle');
$LeadSource = array('Title AS LeadSource');
$select = $this->db->select();
$select->from(array('TP' => "TB_Agency_Customer_TravelPlan"), $arrCustomerTravelPlan);
$select->joinInner(array('TPQ' => "TB_Agency_Customer_TravelPlan_Query"), "TPQ.TPSysId = TP.MasterTPSysId", array('TPSysId as QTPSysId'));
$select->joinInner(array('TPI' => "TB_Agency_Customer_TravelPlan_Itenary"), "TPI.TPSysId = TP.TPSysId", $arrCustomerTravelPlanItenary);
$select->joinInner(array('TPA' => "TB_Agency_Customer_TravelPlan_Air"), "TPA.TPIntSysId = TPI.TPIntSysId", $arrCustomerTravelPlanAir);
$select->joinleft(array('ITAA' => "TB_IC_Trans_API_Air"), "ITAA.TBQAirInvenSysId = TPA.InvnItemSysId", array('TBQAirInvenSysId', 'IsInternational', 'IsQuickProposal', 'ICSourceSysId', 'IsNewMigration'));
$select->join(array('AG' => "TB_Agency_User"), "AG.UserSysId = TP.AgentSysId", $arrAgency);
$select->join(array('AGU' => "TB_Agency_User"), "AGU.UserSysId = TPQ.AgentSysId", $arrAgencyOwner);
$select->joinleft(array('AGCY' => "TB_Agency"), "AGCY.AgencySysId = TP.AgencySysId", ['DisplayName AS AgencyName', 'SecurityKey']);
$select->joinleft(array('AGCYT' => "TB_Agency"), "AGCYT.AgencySysId = TP.B2BAgencySysId", ['Title AS B2BAgencyName', 'SecurityKey AS B2BSecurityKey', 'PrimaryEmail as B2BPrimaryEmail']);
$select->joinLeft(array('AC' => "TB_Agency_Customer"), "AC.CustomerSysId = TP.CustomerSysId", $arrCustomer);
$select->joinLeft(array('TMA' => 'TB_Master_Airline'), 'TMA.AirlineSysId = ITAA.AirlineSysId ', $arrAirLine);
$select->joinLeft(array('LDS' => 'TB_Master_LeadSource'), 'LDS.LeadSourceSysId = TP.LeadSourceSysId ', $LeadSource);
$select->joinLeft(array('TIS' => 'TB_IC_Supplier'), 'TIS.SupplierSysId = TP.SupplierSysId ', array('SupplierName'));
$select->joinLeft(array('INV' => "TB_Agency_Miscellaneous"), "TP.TPSysId = INV.TPSysId", array('InvoiceNumber', 'MasterMiscSysId', 'CreateDate as InvoiceDate'));
$select->where("TP.AgencySysId = ?", $AgencySysId);
if ($ReportingToSysIds) {
$select->where("TP.AgentSysId IN (" . $ReportingToSysIds . ")");
}
$select->where("TPA.XRefPNR = ?", 1);
$select->where("TP.PlanType = ?", 1);
$select->where("TPA.IsBookingStatus = ?", 1);
$select->where("TPA.flexiPayment = ?", 1);
$select->where('TP.StatusType IN(?)', array(7, 13, 17, 71, 124, 123, 104, 70));
$select->where('TP.PaymentStatus != 130');
// $select->where("TPA.AirlineRefPNR !=?", '');
if (!empty($this->searchArr) && isset($this->searchArr)) {
$searchArr = $this->searchArr;
if (!empty($searchArr['dateFrom']) && !empty($searchArr['dateTo']) && trim($searchArr['dateFrom']) != '__-__-____' && trim($searchArr['dateTo']) != '__-__-____') {
$dateFrom = (isset($searchArr['dateFrom']) && !empty($searchArr['dateFrom'])) ? date("Y-m-d", strtotime($searchArr['dateFrom'])) : '';
$dateTo = (isset($searchArr['dateTo']) && !empty($searchArr['dateTo'])) ? date("Y-m-d", strtotime($searchArr['dateTo'])) : '';
if ($dateFrom != '1970-01-01' && $dateTo != '1970-01-01') {
$select->where("CAST(TPA.CreateDate AS DATE) BETWEEN '$dateFrom' AND '$dateTo'");
}
}
if (!empty($searchArr['TravelToDate']) && !empty($searchArr['TravelFromDate']) && trim($searchArr['TravelFromDate']) != '__-__-____' && trim($searchArr['TravelToDate']) != '__-__-____') {
$TravelFromDate = (isset($searchArr['TravelFromDate']) && !empty($searchArr['TravelFromDate'])) ? date("Y-m-d", strtotime($searchArr['TravelFromDate'])) : '';
$TravelToDate = (isset($searchArr['TravelToDate']) && !empty($searchArr['TravelToDate'])) ? date("Y-m-d", strtotime($searchArr['TravelToDate'])) : '';
if ($TravelFromDate != '1970-01-01' && $TravelToDate != '1970-01-01') {
$select->where("CONVERT(date,TPA.FromUTCTime) >= '$TravelFromDate' AND CONVERT(date,TPA.FromUTCTime) <= '$TravelToDate'");
}
}
if (!empty($searchArr['ToDeadLineDate']) && !empty($searchArr['FromDeadLineDate']) && trim($searchArr['FromDeadLineDate']) != '__-__-____' && trim($searchArr['ToDeadLineDate']) != '__-__-____') {
$TravelFromDate = (isset($searchArr['FromDeadLineDate']) && !empty($searchArr['FromDeadLineDate'])) ? date("Y-m-d", strtotime($searchArr['FromDeadLineDate'])) : '';
$TravelToDate = (isset($searchArr['ToDeadLineDate']) && !empty($searchArr['ToDeadLineDate'])) ? date("Y-m-d", strtotime($searchArr['ToDeadLineDate'])) : '';
if ($TravelFromDate != '1970-01-01' && $TravelToDate != '1970-01-01') {
$select->where("CONVERT(date,TPA.DeadLineDate) >= '$TravelFromDate' AND CONVERT(date,TPA.DeadLineDate) <= '$TravelToDate'");
}
}
if (isset($searchArr['LeadFilter']) == 'dueexpiry') {
$CurrentDate = date("Y-m-d");
$select->where("CONVERT(date,TPA.DeadLineDate) <= '$CurrentDate'");
}
$ProposalId = trim($searchArr['ProposalId']);
$ProposalId_filt = explode("/", $ProposalId);
$Flight = trim($searchArr['Flight']);
$BookingId = trim($searchArr['BookingId']);
$orgDest = trim($searchArr['orgDest']);
$CustomerName = trim($searchArr['CustomerName']);
$CustomerEmail = trim($searchArr['CustomerEmail']);
$CustomerPhone = trim($searchArr['CustomerPhone']);
$B2bagencyAgentId = trim($searchArr['hidden_b2bagency_agentId']);
$source = trim($searchArr['source']);
$pnrnumber = trim($searchArr['pnrnumber']);
$agencyUserFilter = (isset($searchArr['agencyUserFilter'])) ? (int) $searchArr['agencyUserFilter'] : 0;
if (!empty($ProposalId) && isset($ProposalId)) {
if (isset($ProposalId_filt[2])) {
$select->where('TP.TPSysId LIKE ?', $ProposalId_filt[2]);
} else {
$select->where('TP.TPSysId LIKE ?', $ProposalId);
}
}
if (!empty($Flight) && isset($Flight)) {
$whereConditions = " TPA.FlightNumber = '" . $Flight . "' OR TMA.Title like '%" . $Flight . "%'";
$select->where($whereConditions);
}
if (!empty($BookingId) && isset($BookingId)) {
$select->where('TPA.XRefBookingId LIKE ?', "%$BookingId%");
}
if (!empty($pnrnumber) && isset($pnrnumber)) {
$select->where('TPA.AirlineRefPNR LIKE ?', "%$pnrnumber%");
}
if (!empty($orgDest) && isset($orgDest)) {
$whereCondition_fordes = " TP.SourcePlaces = '" . $orgDest . "' OR TP.DestinationPlaces like '%" . $orgDest . "%'";
$select->where($whereCondition_fordes);
}
if (!empty($CustomerName) && isset($CustomerName)) {
$select->where('AC.FirstName LIKE ?', "%$CustomerName%");
}
if (!empty($CustomerEmail) && isset($CustomerEmail)) {
$select->where('AC.EmailId = ?', $CustomerEmail);
}
if (!empty($CustomerPhone) && isset($CustomerPhone)) {
$select->where('AC.Contacts = ?', $CustomerPhone);
}
if (!empty($B2bagencyAgentId) && isset($B2bagencyAgentId)) {
$select->where('TP.B2BAgencySysId = ?', $B2bagencyAgentId);
}
if (isset($source) && $source != '') {
$select->where('TP.IsB2BProposal = ?', $source);
}
if ($agencyUserFilter > 0) {
$select->where('TPQ.AgentSysId = ?', $agencyUserFilter);
}
}
if (!empty($Actioncronjob)) {
$select->order('TP.TPSysId DESC');
$result = $this->db->fetchAll($select);
return $result;
} elseif (!empty($ActionTPSysId) && $ActionTPSysId > 0) {
$select->where("TP.TPSysId = ?", $ActionTPSysId);
$select->order('TP.TPSysId DESC');
$result = $this->db->fetchAll($select);
return $result;
} else {
$select->order('TP.TPSysId DESC');
return $select;
}
}
public function getFlightBookingListHold($ReportingToSysIds = null, $Actioncronjob = null)
{
$arrCustomerTravelPlan = array('IsB2BProposal', 'AgentSysId', 'AgencySysId', 'StatusType', 'TPSysId', 'MasterTPSysId', 'CurrencyType', 'CustomerSysId', 'SourcePlaces', 'DestinationPlaces', 'PlanBookingId', 'VersionId', 'LeadSourceSysId', "((select SUM(adT1.Tax) FROM TB_Agency_Customer_TravelPlan_AddonServices adT1 where adT1.IsMarkForDelete = 0 AND TP.TPSysId = adT1.TPSysId AND (adT1.PlanType = 19))) as TotalTcs");
$arrCustomerTravelPlanItenary = array('TPIntSysId');
$arrCustomerTravelPlanAir = array(
'FlightNumber',
'TotalCost',
'Cost',
'Discount',
'GTXMarkup',
'AgencyCommission',
'AgencyMarkUp',
'AgentServiceTaxAmount',
'SourceAirportCode',
'DestAirportCode',
'FromUTCTime',
'VersionId as AirVersionId',
'CreateDate as bookingDate',
'ToUTCTime',
'XRefBookingId',
'LocalToTime as returnDate',
'AgentsCustomServiceTaxAmount',
'MarkUp',
'APIBookingRes',
'TripType',
'XRefPNR',
'AirlineRefPNR',
'IsBookingStatus',
'DeadLineDate as LastTicketDate',
'ApprovalDate',
'IsFixedTransport as IsHoldTicket',
);
//$arrCustomerTrx = array('TrxDate AS bookingDate');
$arrAgency = array('FirstName AS agtFirstName', 'LastName AS agtLastName', 'AgencyName as agtAgencyName');
$arrAgencyOwner = array('FirstName AS ownerFirstName', 'LastName AS ownerLastName', 'AgencyName as ownerAgencyName');
$arrCustomer = array('FirstName AS custFirstName', 'LastName as custLastName', 'EmailId as custEmailId', 'Contacts as custContacts', 'countrycode');
$arrAirLine = array('Code AS AirLineCode', 'Title as AirLineTitle');
$LeadSource = array('Title AS LeadSource');
$select = $this->db->select();
$select->from(array('TP' => "TB_Agency_Customer_TravelPlan"), $arrCustomerTravelPlan);
$select->joinInner(array('TPQ' => "TB_Agency_Customer_TravelPlan_Query"), "TPQ.TPSysId = TP.MasterTPSysId", array('TPSysId as QTPSysId'));
$select->joinInner(array('TPI' => "TB_Agency_Customer_TravelPlan_Itenary"), "TPI.TPSysId = TP.TPSysId", $arrCustomerTravelPlanItenary);
$select->joinInner(array('TPA' => "TB_Agency_Customer_TravelPlan_Air"), "TPA.TPIntSysId = TPI.TPIntSysId", $arrCustomerTravelPlanAir);
$select->joinleft(array('ITAA' => "TB_IC_Trans_API_Air"), "ITAA.TBQAirInvenSysId = TPA.InvnItemSysId", array('TBQAirInvenSysId', 'IsInternational', 'IsQuickProposal', 'ICSourceSysId', 'IsNewMigration'));
//$select->joinInner(array('TRX' => "TB_Agency_Customer_Trx"), "TRX.TPSysId = TP.TPSysId", $arrCustomerTrx);
// $select->joinInner(array('TRX' => "TB_Agency_Customer_Trx"), "TRX.TPSysId = TP.TPSysId AND TRX.TrxStatus = 1", $arrCustomerTrx);
$select->join(array('AG' => "TB_Agency_User"), "AG.UserSysId = TP.AgentSysId", $arrAgency);
$select->join(array('AGU' => "TB_Agency_User"), "AGU.UserSysId = TPQ.AgentSysId", $arrAgencyOwner);
$select->joinleft(array('AGCY' => "TB_Agency"), "AGCY.AgencySysId = TP.AgencySysId", ['DisplayName AS AgencyName']);
$select->joinleft(array('AGCYT' => "TB_Agency"), "AGCYT.AgencySysId = TP.B2BAgencySysId", ['Title AS B2BAgencyName']);
$select->joinLeft(array('AC' => "TB_Agency_Customer"), "AC.CustomerSysId = TP.CustomerSysId", $arrCustomer);
$select->joinLeft(array('TPCTP' => "TB_Agency_Customer_TravelPlan_Pax"), "TP.TPSysId = TPCTP.TPSysId AND TPCTP.IsLeadPax = 1", array());
$select->joinLeft(array('TPCM' => "TB_Agency_Customer_Members"), "TPCTP.MemberSysId = TPCM.MemberSysId AND TPCTP.IsLeadPax = 1", array());
$select->joinLeft(array('TMA' => 'TB_Master_Airline'), 'TMA.AirlineSysId = ITAA.AirlineSysId ', $arrAirLine);
$select->joinLeft(array('LDS' => 'TB_Master_LeadSource'), 'LDS.LeadSourceSysId = TP.LeadSourceSysId ', $LeadSource);
if (!empty($this->intLoggedinUserAgencySysId)) {
$select->where("TP.AgencySysId = ?", $this->intLoggedinUserAgencySysId);
}
if ($ReportingToSysIds) {
$select->where("TP.AgentSysId IN (" . $ReportingToSysIds . ")");
}
$select->where("TPA.XRefPNR = ?", 1);
$select->where("TP.PlanType = ?", 1);
$select->where("TPA.IsBookingStatus = ?", 1);
//$select->where("TRX.IsActive = ?", 1);
$select->where('TP.StatusType IN(?)', array(148, 149));
// $select->where("TPA.AirlineRefPNR !=?", '');
// $select->where('TP.StatusType IN(?)', array(17, 9, 18, 19, 71, 72));
//filter for date search =======================added BY SANDEEP NEGI 19-11-2020 START============================
if (!empty($this->searchArr) && isset($this->searchArr)) {
//echo "<pre>";print_r($this->searchArr); die("test");
$searchArr = $this->searchArr;
if (!empty($searchArr['dateFrom']) && !empty($searchArr['dateTo']) && trim($searchArr['dateFrom']) != '__-__-____' && trim($searchArr['dateTo']) != '__-__-____') {
$dateFrom = (isset($searchArr['dateFrom']) && !empty($searchArr['dateFrom'])) ? date("Y-m-d", strtotime($searchArr['dateFrom'])) : '';
$dateTo = (isset($searchArr['dateTo']) && !empty($searchArr['dateTo'])) ? date("Y-m-d", strtotime($searchArr['dateTo'])) : '';
if ($dateFrom != '1970-01-01' && $dateTo != '1970-01-01') {
$select->where("CAST(TPA.CreateDate AS DATE) BETWEEN '$dateFrom' AND '$dateTo'");
}
}
if (!empty($searchArr['TravelToDate']) && !empty($searchArr['TravelFromDate']) && trim($searchArr['TravelFromDate']) != '__-__-____' && trim($searchArr['TravelToDate']) != '__-__-____') {
$TravelFromDate = (isset($searchArr['TravelFromDate']) && !empty($searchArr['TravelFromDate'])) ? date("Y-m-d", strtotime($searchArr['TravelFromDate'])) : '';
$TravelToDate = (isset($searchArr['TravelToDate']) && !empty($searchArr['TravelToDate'])) ? date("Y-m-d", strtotime($searchArr['TravelToDate'])) : '';
if ($TravelFromDate != '1970-01-01' && $TravelToDate != '1970-01-01') {
$select->where("CONVERT(date,TPA.FromUTCTime) >= '$TravelFromDate' AND CONVERT(date,TPA.FromUTCTime) <= '$TravelToDate'");
}
}
$ProposalId = trim($searchArr['ProposalId']);
$ProposalId_filt = explode("/", $ProposalId);
$Flight = trim($searchArr['Flight']);
$BookingId = trim($searchArr['BookingId']);
$orgDest = trim($searchArr['orgDest']);
$CustomerName = trim($searchArr['CustomerName']);
$CustomerEmail = trim($searchArr['CustomerEmail']);
$CustomerPhone = trim($searchArr['CustomerPhone']);
$B2bagencyAgentId = trim($searchArr['hidden_b2bagency_agentId']);
$source = trim($searchArr['source']);
$pnrnumber = trim($searchArr['pnrnumber']);
$agencyUserFilter = (isset($searchArr['agencyUserFilter'])) ? (int) $searchArr['agencyUserFilter'] : 0;
// echo $Flight; die();
// if ($dateFrom != '' && $dateTo != '') {
// $select->where("CAST(TRX.TrxDate AS DATE) BETWEEN '$dateFrom' AND '$dateTo'");
// }
// if (!empty($TravelDate) && isset($TravelDate)) {
// // $select->where('TP.TPSysId LIKE ?', "%$TravelDate%");
// }
if (!empty($ProposalId) && isset($ProposalId)) {
if (isset($ProposalId_filt[2])) {
$select->where('TP.TPSysId LIKE ?', $ProposalId_filt[2]);
} else {
$select->where('TP.TPSysId LIKE ?', $ProposalId);
}
}
if (!empty($Flight) && isset($Flight)) {
$whereConditions = " TPA.FlightNumber = '" . $Flight . "' OR TMA.Title like '%" . $Flight . "%'";
$select->where($whereConditions);
}
//echo $select; die();
if (!empty($BookingId) && isset($BookingId)) {
//$select->where('TPA.XRefBookingId = ?', $BookingId);
$select->where('TPA.XRefBookingId LIKE ?', "%$BookingId%");
}
if (!empty($pnrnumber) && isset($pnrnumber)) {
//$select->where('TPA.XRefBookingId = ?', $BookingId);
$select->where('TPA.AirlineRefPNR LIKE ?', "%$pnrnumber%");
}
if (!empty($orgDest) && isset($orgDest)) {
$whereCondition_fordes = " TP.SourcePlaces = '" . $orgDest . "' OR TP.DestinationPlaces like '%" . $orgDest . "%'";
$select->where($whereCondition_fordes);
// echo $select; die();
}
if (!empty($CustomerName) && isset($CustomerName)) {
$select->where('AC.FirstName LIKE ?', "%$CustomerName%");
//echo $select; die();
}
if (!empty($CustomerEmail) && isset($CustomerEmail)) {
$select->where('AC.EmailId = ?', $CustomerEmail);
}
if (!empty($CustomerPhone) && isset($CustomerPhone)) {
$select->where('AC.Contacts = ?', $CustomerPhone);
}
if (!empty($B2bagencyAgentId) && isset($B2bagencyAgentId)) {
$select->where('TP.B2BAgencySysId = ?', $B2bagencyAgentId);
}
if (isset($source) && $source != '') {
$select->where('TP.IsB2BProposal = ?', $source);
}
if ($agencyUserFilter > 0) {
$select->where('TPQ.AgentSysId = ?', $agencyUserFilter);
}
//echo $select; die();
}
// echo $select; die();
//filter for date search =======================added BY SANDEEP NEGI 19-11-2020 END============================
if (!empty($Actioncronjob)) {
$select->order('TP.TPSysId DESC');
$result = $this->db->fetchAll($select);
return $result;
} else {
$select->order('TP.TPSysId DESC');
// $result = $this->db->fetchAll($select);
// echo "<pre>" ; print_r($result) ; die();
return $select;
}
}
public function getFlightBookingListCron($ReportingToSysIds = null, $Actioncronjob = null)
{
$arrCustomerTravelPlan = array('IsB2BProposal', 'AgentSysId', 'AgencySysId', 'StatusType', 'TPSysId', 'MasterTPSysId', 'CurrencyType', 'CustomerSysId', 'SourcePlaces', 'DestinationPlaces', 'PlanBookingId', 'VersionId', 'LeadSourceSysId');
$arrCustomerTravelPlanItenary = array('TPIntSysId');
$arrCustomerTravelPlanAir = array(
'FlightNumber',
'TotalCost',
'Cost',
'GTXMarkup',
'AgencyCommission',
'AgencyMarkUp',
'AgentServiceTaxAmount',
'SourceAirportCode',
'DestAirportCode',
'FromUTCTime',
'VersionId as AirVersionId',
'ToUTCTime',
'XRefBookingId',
'LocalToTime as returnDate',
'AgentsCustomServiceTaxAmount',
'MarkUp',
'APIBookingRes',
'TripType',
'XRefPNR',
'AirlineRefPNR',
'IsBookingStatus'
);
$arrCustomerTrx = array('TrxDate AS bookingDate');
$arrAgency = array('FirstName AS agtFirstName', 'LastName AS agtLastName', 'AgencyName as agtAgencyName');
$arrCustomer = array('FirstName AS custFirstName', 'LastName as custLastName', 'EmailId as custEmailId', 'Contacts as custContacts');
$arrAirLine = array('Code AS AirLineCode', 'Title as AirLineTitle');
$LeadSource = array('Title AS LeadSource');
$select = $this->db->select();
$select->from(array('TP' => "TB_Agency_Customer_TravelPlan"), $arrCustomerTravelPlan);
$select->joinInner(array('TPI' => "TB_Agency_Customer_TravelPlan_Itenary"), "TPI.TPSysId = TP.TPSysId", $arrCustomerTravelPlanItenary);
$select->joinInner(array('TPA' => "TB_Agency_Customer_TravelPlan_Air"), "TPA.TPIntSysId = TPI.TPIntSysId", $arrCustomerTravelPlanAir);
$select->joinleft(array('ITAA' => "TB_IC_Trans_API_Air"), "ITAA.TBQAirInvenSysId = TPA.InvnItemSysId", array('TBQAirInvenSysId', 'IsInternational', 'IsQuickProposal', 'ICSourceSysId'));
$select->joinInner(array('TRX' => "TB_Agency_Customer_Trx"), "TRX.TPSysId = TP.TPSysId AND TRX.TrxStatus = 1", $arrCustomerTrx);
$select->join(array('AG' => "TB_Agency_User"), "AG.UserSysId = TP.AgentSysId", $arrAgency);
$select->joinleft(array('AGCY' => "TB_Agency"), "AGCY.AgencySysId = TP.AgencySysId", ['DisplayName AS AgencyName']);
$select->joinleft(array('AGCYT' => "TB_Agency"), "AGCYT.AgencySysId = TP.B2BAgencySysId", ['Title AS B2BAgencyName']);
$select->joinLeft(array('AC' => "TB_Agency_Customer"), "AC.CustomerSysId = TP.CustomerSysId", $arrCustomer);
$select->joinLeft(array('TMA' => 'TB_Master_Airline'), 'TMA.AirlineSysId = ITAA.AirlineSysId ', $arrAirLine);
$select->joinLeft(array('LDS' => 'TB_Master_LeadSource'), 'LDS.LeadSourceSysId = TP.LeadSourceSysId ', $LeadSource);
if ($ReportingToSysIds) {
$select->where("TP.AgentSysId IN (" . $ReportingToSysIds . ")");
}
$select->where("TPA.XRefPNR = ?", 1);
$select->where("TP.PlanType = ?", 1);
$select->where("TPA.IsBookingStatus = ?", 1);
//$select->where("TRX.IsActive = ?", 1);
$select->where('TP.StatusType IN(?)', array(17, 13, 9, 18, 71, 72, 70, 124, 143, 104));
//filter for date search =======================added BY SANDEEP NEGI 19-11-2020 START============================
if (!empty($Actioncronjob)) {
// $select->where("TPA.ICSourceSysId = 7");
$select->where("TPA.AirLineRefPNR = '' OR TPA.AirLineRefPNR is null");
$select->where("CONVERT(date,TPA.FromUTCTime) > ?", date('Y-m-d'));
$select->order('TP.TPSysId DESC');
// echo $select; exit;
$result = $this->db->fetchAll($select);
return $result;
} else {
$select->order('TP.TPSysId DESC');
// $result = $this->db->fetchAll($select);
// echo "<pre>" ; print_r($result) ; die();
return $select;
}
}
public function getFlightBookingListModificationBNPL($ReportingToSysIds = null, $StatusType = null)
{
$arrCustomerTravelPlan = array('AgentSysId', 'StatusType', 'TPSysId', 'MasterTPSysId', 'CurrencyType', 'CustomerSysId', 'SourcePlaces', 'DestinationPlaces', 'PlanBookingId', 'VersionId', 'LeadSourceSysId', 'ChangeRequestId');
$arrCustomerTravelPlanItenary = array('TPIntSysId');
$arrCustomerTravelPlanAir = array(
'FlightNumber',
'TotalCost',
'Cost',
'GTXMarkup',
'AgencyCommission',
'AgencyMarkUp',
'AgentServiceTaxAmount',
'SourceAirportCode',
'DestAirportCode',
'FromUTCTime',
'VersionId as AirVersionId',
'ToUTCTime',
'XRefBookingId',
'LocalToTime as returnDate',
'AgentsCustomServiceTaxAmount',
'MarkUp',
'APIBookingRes',
'TripType',
'XRefPNR',
'IsBookingStatus'
);
$arrCustomerTrx = array('TrxDate AS bookingDate');
$arrAgency = array('FirstName AS agtFirstName', 'LastName AS agtLastName');
$arrCustomer = array('FirstName AS custFirstName', 'LastName as custLastName', 'EmailId as custEmailId', 'Contacts as custContacts', 'countrycode');
$arrAirLine = array('Code AS AirLineCode', 'Title as AirLineTitle');
$LeadSource = array('Title AS LeadSource');
$select = $this->db->select();
$select->from(array('TP' => "TB_Agency_Customer_TravelPlan"), $arrCustomerTravelPlan);
$select->joinInner(array('TPI' => "TB_Agency_Customer_TravelPlan_Itenary"), "TPI.TPSysId = TP.TPSysId", $arrCustomerTravelPlanItenary);
$select->joinInner(array('TPA' => "TB_Agency_Customer_TravelPlan_Air"), "TPA.TPIntSysId = TPI.TPIntSysId", $arrCustomerTravelPlanAir);
$select->joinleft(array('ITAA' => "TB_IC_Trans_API_Air"), "ITAA.TBQAirInvenSysId = TPA.InvnItemSysId", array('TBQAirInvenSysId', 'IsInternational', 'IsQuickProposal', 'ICSourceSysId'));
$select->joinInner(array('TRX' => "TB_Agency_Customer_Trx"), "TRX.TPSysId = TP.TPSysId", $arrCustomerTrx);
$select->join(array('AG' => "TB_Agency_User"), "AG.UserSysId = TP.AgentSysId", $arrAgency);
$select->joinleft(array('AGCY' => "TB_Agency"), "AGCY.AgencySysId = TP.AgencySysId", ['DisplayName AS AgencyName']);
$select->joinLeft(array('AC' => "TB_Agency_Customer"), "AC.CustomerSysId = TP.CustomerSysId", $arrCustomer);
$select->joinLeft(array('TMA' => 'TB_Master_Airline'), 'TMA.AirlineSysId = ITAA.AirlineSysId ', $arrAirLine);
$select->joinLeft(array('LDS' => 'TB_Master_LeadSource'), 'LDS.LeadSourceSysId = TP.LeadSourceSysId ', $LeadSource);
if (!empty($this->intLoggedinUserAgencySysId)) {
$select->where("TP.AgencySysId = ?", $this->intLoggedinUserAgencySysId);
}
if ($ReportingToSysIds) {
$select->where("TP.AgentSysId IN (" . $ReportingToSysIds . ")");
}
$select->where("TPA.XRefPNR = ?", 1);
$select->where("TPA.flexiPayment =?", 1);
$select->where("TP.PlanType = ?", 1);
$select->where("TPA.IsBookingStatus = ?", 1);
$select->where("TP.StatusType IN (" . $StatusType . ")");
if (!empty($this->searchArr) && isset($this->searchArr)) {
$searchArr = $this->searchArr;
if (isset($searchArr['dateFrom']) && ($searchArr['dateFrom'] != '__/__/____')) {
$dateFrom = explode('/', $searchArr['dateFrom']);
$dateFrom = $dateFrom[2] . '-' . $dateFrom[1] . '-' . $dateFrom[0];
}
if (isset($searchArr['dateTo']) && ($searchArr['dateTo'] != '__/__/____')) {
$dateTo = explode('/', $searchArr['dateTo']);
$dateTo = $dateTo[2] . '-' . $dateTo[1] . '-' . $dateTo[0];
}
$ProposalId = trim($searchArr['ProposalId']);
$ProposalId_filt = explode("/", $ProposalId);
$Flight = trim($searchArr['Flight']);
$BookingId = trim($searchArr['BookingId']);
$orgDest = trim($searchArr['orgDest']);
$CustomerName = trim($searchArr['CustomerName']);
$CustomerEmail = trim($searchArr['CustomerEmail']);
$CustomerPhone = trim($searchArr['CustomerPhone']);
if ($dateFrom != '' && $dateTo != '') {
$select->where("CAST(TRX.TrxDate AS DATE) BETWEEN '$dateFrom' AND '$dateTo'");
}
if (!empty($ProposalId) && isset($ProposalId)) {
$select->where('TP.TPSysId LIKE ?', "%$ProposalId_filt[2]%");
}
if (!empty($Flight) && isset($Flight)) {
$whereConditions = " TPA.FlightNumber = '" . $Flight . "' OR TMA.Title like '%" . $Flight . "%'";
$select->where($whereConditions);
}
if (!empty($BookingId) && isset($BookingId)) {
$select->where('TPA.XRefBookingId LIKE ?', "%$BookingId%");
}
if (!empty($orgDest) && isset($orgDest)) {
$whereCondition_fordes = " TP.SourcePlaces = '" . $orgDest . "' OR TP.DestinationPlaces like '%" . $orgDest . "%'";
$select->where($whereCondition_fordes);
}
if (!empty($CustomerName) && isset($CustomerName)) {
$select->where('AC.FirstName LIKE ?', "%$CustomerName%");
}
if (!empty($CustomerEmail) && isset($CustomerEmail)) {
$select->where('AC.EmailId = ?', $CustomerEmail);
}
if (!empty($CustomerPhone) && isset($CustomerPhone)) {
$select->where('AC.Contacts = ?', $CustomerPhone);
}
}
$select->order('TP.TPSysId DESC');
return $select;
}
public function getFlightBookingListModification($ReportingToSysIds = null, $StatusType = null)
{
$arrCustomerTravelPlan = array('IsB2BProposal', 'AgentSysId', 'StatusType', 'TPSysId', 'MasterTPSysId', 'CurrencyType', 'CustomerSysId', 'SourcePlaces', 'DestinationPlaces', 'PlanBookingId', 'VersionId', 'LeadSourceSysId', 'ChangeRequestId', 'UpdateDate');
$arrCustomerTravelPlanItenary = array('TPIntSysId');
$arrCustomerTravelPlanAir = array(
'FlightNumber',
'TotalCost',
'Cost',
'Discount',
'GTXMarkup',
'AgencyCommission',
'AgencyMarkUp',
'AgentServiceTaxAmount',
'SourceAirportCode',
'DestAirportCode',
'FromUTCTime',
'VersionId as AirVersionId',
'CreateDate as bookingDate1',
'ToUTCTime',
'XRefBookingId',
'LocalToTime as returnDate',
'AgentsCustomServiceTaxAmount',
'MarkUp',
'APIBookingRes',
'APIBookingId',
'TripType',
'XRefPNR',
'AirlineRefPNR',
'IsBookingStatus',
'flexiPayment',
'DeadLineDate'
);
$arrCustomerTrx = array('TrxDate AS bookingDate');
$arrAgency = array('FirstName AS agtFirstName', 'LastName AS agtLastName');
$arrCustomer = array('FirstName AS custFirstName', 'LastName as custLastName', 'EmailId as custEmailId', 'Contacts as custContacts', 'countrycode');
$arrAirLine = array('Code AS AirLineCode', 'Title as AirLineTitle');
$LeadSource = array('Title AS LeadSource');
$select = $this->db->select();
$select->from(array('TP' => "TB_Agency_Customer_TravelPlan"), $arrCustomerTravelPlan);
$select->joinInner(array('TPI' => "TB_Agency_Customer_TravelPlan_Itenary"), "TPI.TPSysId = TP.TPSysId", $arrCustomerTravelPlanItenary);
$select->joinInner(array('TPA' => "TB_Agency_Customer_TravelPlan_Air"), "TPA.TPIntSysId = TPI.TPIntSysId", $arrCustomerTravelPlanAir);
$select->joinleft(array('ITAA' => "TB_IC_Trans_API_Air"), "ITAA.TBQAirInvenSysId = TPA.InvnItemSysId", array('TBQAirInvenSysId', 'IsInternational', 'IsQuickProposal', 'ICSourceSysId', 'IsNewMigration'));
$select->joinInner(array('TRX' => "TB_Agency_Customer_Trx"), "TRX.TPSysId = TP.TPSysId", $arrCustomerTrx);
$select->join(array('AG' => "TB_Agency_User"), "AG.UserSysId = TP.AgentSysId", $arrAgency);
$select->joinleft(array('AGCY' => "TB_Agency"), "AGCY.AgencySysId = TP.AgencySysId", ['DisplayName AS AgencyName']);
$select->joinLeft(array('AC' => "TB_Agency_Customer"), "AC.CustomerSysId = TP.CustomerSysId", $arrCustomer);
$select->joinLeft(array('TMA' => 'TB_Master_Airline'), 'TMA.AirlineSysId = ITAA.AirlineSysId ', $arrAirLine);
$select->joinleft(array('AGCYT' => "TB_Agency"), "AGCYT.AgencySysId = TP.B2BAgencySysId", ['Title AS B2BAgencyName']);
$select->joinLeft(array('LDS' => 'TB_Master_LeadSource'), 'LDS.LeadSourceSysId = TP.LeadSourceSysId ', $LeadSource);
if (!empty($this->intLoggedinUserAgencySysId)) {
$select->where("TP.AgencySysId = ?", $this->intLoggedinUserAgencySysId);
}
if ($ReportingToSysIds) {
$select->where("TP.AgentSysId IN (" . $ReportingToSysIds . ")");
}
$select->where("TPA.XRefPNR = ?", 1);
$select->where("TP.PlanType = ?", 1);
$select->where("TPA.IsBookingStatus = ?", 1);
$select->where("TP.StatusType IN (" . $StatusType . ")");
if (!empty($this->searchArr) && isset($this->searchArr)) {
$searchArr = $this->searchArr;
if (isset($searchArr['dateFrom']) && ($searchArr['dateFrom'] != '__/__/____')) {
$dateFrom = explode('/', $searchArr['dateFrom']);
$dateFrom = $dateFrom[2] . '-' . $dateFrom[1] . '-' . $dateFrom[0];
}
if (isset($searchArr['dateTo']) && ($searchArr['dateTo'] != '__/__/____')) {
$dateTo = explode('/', $searchArr['dateTo']);
$dateTo = $dateTo[2] . '-' . $dateTo[1] . '-' . $dateTo[0];
}
$ProposalId = trim($searchArr['ProposalId']);
$ProposalId_filt = explode("/", $ProposalId);
$Flight = trim($searchArr['Flight']);
$BookingId = trim($searchArr['BookingId']);
$orgDest = trim($searchArr['orgDest']);
$CustomerName = trim($searchArr['CustomerName']);
$CustomerEmail = trim($searchArr['CustomerEmail']);
$CustomerPhone = trim($searchArr['CustomerPhone']);
if ($dateFrom != '' && $dateTo != '') {
$select->where("CAST(TRX.TrxDate AS DATE) BETWEEN '$dateFrom' AND '$dateTo'");
}
if (!empty($ProposalId) && isset($ProposalId)) {
$select->where('TP.TPSysId LIKE ?', "%$ProposalId_filt[2]%");
}
if (!empty($Flight) && isset($Flight)) {
$whereConditions = " TPA.FlightNumber = '" . $Flight . "' OR TMA.Title like '%" . $Flight . "%'";
$select->where($whereConditions);
}
if (!empty($BookingId) && isset($BookingId)) {
$select->where('TPA.XRefBookingId LIKE ?', "%$BookingId%");
}
if (!empty($orgDest) && isset($orgDest)) {
$whereCondition_fordes = " TP.SourcePlaces = '" . $orgDest . "' OR TP.DestinationPlaces like '%" . $orgDest . "%'";
$select->where($whereCondition_fordes);
}
if (!empty($CustomerName) && isset($CustomerName)) {
$select->where('AC.FirstName LIKE ?', "%$CustomerName%");
}
if (!empty($CustomerEmail) && isset($CustomerEmail)) {
$select->where('AC.EmailId = ?', $CustomerEmail);
}
if (!empty($CustomerPhone) && isset($CustomerPhone)) {
$select->where('AC.Contacts = ?', $CustomerPhone);
}
}
$select->order('TP.UpdateDate DESC');
return $select;
}
public function getFlightBookingListQueue($ReportingToSysIds = null)
{
// $arrCustomerTravelPlan = array('AgentSysId', 'StatusType', 'TPSysId', 'MasterTPSysId', 'CurrencyType', 'CustomerSysId', 'SourcePlaces', 'DestinationPlaces', 'PlanBookingId', 'VersionId');
// $arrCustomerTravelPlanItenary = array('TPIntSysId');
// $arrCustomerTravelPlanAir = array(
// 'FlightNumber', 'TotalCost', 'Cost', 'GTXMarkup', 'AgencyCommission', 'AgencyMarkUp', 'AgentServiceTaxAmount', 'SourceAirportCode', 'DestAirportCode',
// 'FromUTCTime', 'VersionId as AirVersionId', 'ToUTCTime', 'XRefBookingId', 'LocalToTime as returnDate', 'AgentsCustomServiceTaxAmount', 'MarkUp', 'APIBookingRes', 'TripType', 'XRefPNR', 'IsBookingStatus'
// );
// $arrCustomerTrx = array('TrxDate AS bookingDate');
// $arrAgency = array('FirstName AS agtFirstName', 'LastName AS agtLastName');
// $arrCustomer = array('FirstName AS custFirstName', 'LastName as custLastName');
// $select = $this->db->select();
// $select->from(array('TP' => "TB_Agency_Customer_TravelPlan"), $arrCustomerTravelPlan);
// $select->joinInner(array('TPI' => "TB_Agency_Customer_TravelPlan_Itenary"), "TPI.TPSysId = TP.TPSysId", $arrCustomerTravelPlanItenary);
// $select->joinInner(array('TPA' => "TB_Agency_Customer_TravelPlan_Air"), "TPA.TPIntSysId = TPI.TPIntSysId", $arrCustomerTravelPlanAir);
// $select->joinleft(array('ITAA' => "TB_IC_Trans_API_Air"), "ITAA.TBQAirInvenSysId = TPA.InvnItemSysId", array('TBQAirInvenSysId', 'IsInternational', 'IsQuickProposal'));
// $select->joinInner(array('TRX' => "TB_Agency_Customer_Trx"), "TRX.TPSysId = TP.TPSysId", $arrCustomerTrx);
// $select->join(array('AG' => "TB_Agency_User"), "AG.UserSysId = TP.AgentSysId", $arrAgency);
// $select->joinleft(array('AGCY' => "TB_Agency"), "AGCY.AgencySysId = TP.AgencySysId", ['DisplayName AS AgencyName']);
// $select->joinLeft(array('AC' => "TB_Agency_Customer"), "AC.CustomerSysId = TP.CustomerSysId", $arrCustomer);
// if (!empty($this->intLoggedinUserAgencySysId)) {
// $select->where("TP.AgencySysId = ?", $this->intLoggedinUserAgencySysId);
// }
// if ($ReportingToSysIds) {
// $select->where("TP.AgentSysId IN (" . $ReportingToSysIds . ")");
// }
$arrCustomerTravelPlan = array('IsB2BProposal', 'AgentSysId', 'AgencySysId', 'StatusType', 'TPSysId', 'MasterTPSysId', 'CurrencyType', 'CustomerSysId', 'SourcePlaces', 'DestinationPlaces', 'PlanBookingId', 'VersionId', 'LeadSourceSysId', 'PackSpecType', 'EconomyMask');
$arrCustomerTravelPlanItenary = array('TPIntSysId');
$arrCustomerTravelPlanAir = array(
'FlightNumber',
'TotalCost',
'Cost',
'GTXMarkup',
'AgencyCommission',
'AgencyMarkUp',
'AgentServiceTaxAmount',
'SourceAirportCode',
'DestAirportCode',
'FromUTCTime',
'VersionId as AirVersionId',
'ToUTCTime',
'XRefBookingId',
'LocalToTime as returnDate',
'AgentsCustomServiceTaxAmount',
'MarkUp',
'APIBookingRes',
'TripType',
'XRefPNR',
'AirlineRefPNR',
'IsBookingStatus'
);
$arrCustomerTrx = array('TrxDate AS bookingDate', 'TrxSysId');
$arrAgency = array('FirstName AS agtFirstName', 'LastName AS agtLastName', 'AgencyName as agtAgencyName');
$arrAgencyOwner = array('FirstName AS ownerFirstName', 'LastName AS ownerLastName', 'AgencyName as ownerAgencyName');
$arrCustomer = array('FirstName AS custFirstName', 'LastName as custLastName', 'EmailId as custEmailId', 'Contacts as custContacts', 'countrycode');
$arrAirLine = array('Code AS AirLineCode', 'Title as AirLineTitle');
$LeadSource = array('Title AS LeadSource');
$select = $this->db->select();
$select->from(array('TP' => "TB_Agency_Customer_TravelPlan"), $arrCustomerTravelPlan);
$select->joinInner(array('TPQ' => "TB_Agency_Customer_TravelPlan_Query"), "TPQ.TPSysId = TP.MasterTPSysId", array('TPSysId as QTPSysId'));
$select->joinInner(array('TPI' => "TB_Agency_Customer_TravelPlan_Itenary"), "TPI.TPSysId = TP.TPSysId", $arrCustomerTravelPlanItenary);
$select->joinInner(array('TPA' => "TB_Agency_Customer_TravelPlan_Air"), "TPA.TPIntSysId = TPI.TPIntSysId", $arrCustomerTravelPlanAir);
$select->joinleft(array('ITAA' => "TB_IC_Trans_API_Air"), "ITAA.TBQAirInvenSysId = TPA.InvnItemSysId", array('TBQAirInvenSysId', 'IsInternational', 'IsQuickProposal', 'ICSourceSysId', 'IsNewMigration'));
$select->joinInner(array('TRX' => "TB_Agency_Customer_Trx"), "TRX.TPSysId = TP.TPSysId", $arrCustomerTrx);
// $select->joinInner(array('TRX' => "TB_Agency_Customer_Trx"), "TRX.TPSysId = TP.TPSysId AND TRX.TrxStatus = 1", $arrCustomerTrx);
$select->join(array('AG' => "TB_Agency_User"), "AG.UserSysId = TP.AgentSysId", $arrAgency);
$select->join(array('AGU' => "TB_Agency_User"), "AGU.UserSysId = TPQ.AgentSysId", $arrAgencyOwner);
$select->joinleft(array('AGCY' => "TB_Agency"), "AGCY.AgencySysId = TP.AgencySysId", ['DisplayName AS AgencyName']);
$select->joinleft(array('AGCYT' => "TB_Agency"), "AGCYT.AgencySysId = TP.B2BAgencySysId", ['Title AS B2BAgencyName']);
$select->joinLeft(array('AC' => "TB_Agency_Customer"), "AC.CustomerSysId = TP.CustomerSysId", $arrCustomer);
$select->joinLeft(array('TMA' => 'TB_Master_Airline'), 'TMA.AirlineSysId = ITAA.AirlineSysId ', $arrAirLine);
$select->joinLeft(array('LDS' => 'TB_Master_LeadSource'), 'LDS.LeadSourceSysId = TP.LeadSourceSysId ', $LeadSource);
$select->joinLeft(array('TIS' => 'TB_IC_Supplier'), 'TIS.SupplierSysId = TP.SupplierSysId ', array('SupplierName'));
if (!empty($this->intLoggedinUserAgencySysId)) {
$select->where("TP.AgencySysId = ?", $this->intLoggedinUserAgencySysId);
}
if ($ReportingToSysIds) {
$select->where("TP.AgentSysId IN (" . $ReportingToSysIds . ")");
}
//$select->where("TPA.XRefPNR IN(?)", array('', 0));
$select->where("TP.PlanType = ?", 1);
//$select->where("TPA.IsBookingStatus = ?", 1);
//$select->where("TPA.IsBookingStatus IN(?)", array('', 1, 0)); // Booking status = 2 (Aborted) condition has been removed because records with status = 2 were not showing by prashant
$select->where("TPA.IsBookingStatus is not null");
$select->where("TPA.AirlineRefPNR = '' OR TPA.AirlineRefPNR is NULL OR TPA.AirlineRefPNR = 'pending'");
// $select->where("TPA.AirlineRefPNR IN(?)", array('', 'NULL'));
$select->where('TP.StatusType IN(?)', array(17, 13, 9, 18, 71, 72, 70, 124, 143, 141, 142, 104));
//$select->where('TP.StatusType IN(?)', array(9, 18, 72, 70, 143, 141, 142));
// $select->where("');
// $select->where("TP.PlanType = ?", 1);
// $select->where("TPA.IsBookingStatus = ?", 1);
// $select->where('TP.StatusType IN(?)', array(17, 9, 18, 19, 71, 72));
//filter for date search =======================added BY SANDEEP NEGI 19-11-2020 START============================
if (!empty($this->searchArr) && isset($this->searchArr)) {
$searchArr = $this->searchArr;
if (!empty($searchArr['dateFrom']) && !empty($searchArr['dateTo']) && trim($searchArr['dateFrom']) != '__-__-____' && trim($searchArr['dateTo']) != '__-__-____') {
$dateFrom = (isset($searchArr['dateFrom']) && !empty($searchArr['dateFrom'])) ? date("Y-m-d", strtotime($searchArr['dateFrom'])) : '';
$dateTo = (isset($searchArr['dateTo']) && !empty($searchArr['dateTo'])) ? date("Y-m-d", strtotime($searchArr['dateTo'])) : '';
if ($dateFrom != '1970-01-01' && $dateTo != '1970-01-01') {
$select->where("CAST(TRX.TrxDate AS DATE) BETWEEN '$dateFrom' AND '$dateTo'");
}
}
if (!empty($searchArr['TravelToDate']) && !empty($searchArr['TravelFromDate']) && trim($searchArr['TravelFromDate']) != '__-__-____' && trim($searchArr['TravelToDate']) != '__-__-____') {
$TravelFromDate = (isset($searchArr['TravelFromDate']) && !empty($searchArr['TravelFromDate'])) ? date("Y-m-d", strtotime($searchArr['TravelFromDate'])) : '';
$TravelToDate = (isset($searchArr['TravelToDate']) && !empty($searchArr['TravelToDate'])) ? date("Y-m-d", strtotime($searchArr['TravelToDate'])) : '';
if ($TravelFromDate != '1970-01-01' && $TravelToDate != '1970-01-01') {
$select->where("CONVERT(date,TPA.FromUTCTime) >= '$TravelFromDate' AND CONVERT(date,TPA.FromUTCTime) <= '$TravelToDate'");
}
}
$ProposalId = trim($searchArr['ProposalId']);
$ProposalId_filt = explode("/", $ProposalId);
$Flight = trim($searchArr['Flight']);
$BookingId = trim($searchArr['BookingId']);
$orgDest = trim($searchArr['orgDest']);
$CustomerName = trim($searchArr['CustomerName']);
$CustomerEmail = trim($searchArr['CustomerEmail']);
$CustomerPhone = trim($searchArr['CustomerPhone']);
$B2bagencyAgentId = trim($searchArr['hidden_b2bagency_agentId']);
$source = trim($searchArr['source']);
$pnrnumber = trim($searchArr['pnrnumber']);
if (!empty($ProposalId) && isset($ProposalId)) {
if (isset($ProposalId_filt[2])) {
$select->where('TP.TPSysId LIKE ?', $ProposalId_filt[2]);
} else {
$select->where('TP.TPSysId LIKE ?', $ProposalId);
}
}
if (!empty($Flight) && isset($Flight)) {
$whereConditions = " TPA.FlightNumber = '" . $Flight . "' OR TMA.Title like '%" . $Flight . "%'";
$select->where($whereConditions);
}
//echo $select; die();
if (!empty($BookingId) && isset($BookingId)) {
//$select->where('TPA.XRefBookingId = ?', $BookingId);
$select->where('TPA.XRefBookingId LIKE ?', "%$BookingId%");
}
if (!empty($pnrnumber) && isset($pnrnumber)) {
//$select->where('TPA.XRefBookingId = ?', $BookingId);
$select->where('TPA.AirlineRefPNR LIKE ?', "%$pnrnumber%");
}
if (!empty($orgDest) && isset($orgDest)) {
$whereCondition_fordes = " TP.SourcePlaces = '" . $orgDest . "' OR TP.DestinationPlaces like '%" . $orgDest . "%'";
$select->where($whereCondition_fordes);
// echo $select; die();
}
if (!empty($CustomerName) && isset($CustomerName)) {
$select->where('AC.FirstName LIKE ?', "%$CustomerName%");
//echo $select; die();
}
if (!empty($CustomerEmail) && isset($CustomerEmail)) {
$select->where('AC.EmailId = ?', $CustomerEmail);
}
if (!empty($CustomerPhone) && isset($CustomerPhone)) {
$select->where('AC.Contacts = ?', $CustomerPhone);
}
if (!empty($B2bagencyAgentId) && isset($B2bagencyAgentId)) {
$select->where('TP.B2BAgencySysId = ?', $B2bagencyAgentId);
}
if (isset($source) && $source != '') {
$select->where('TP.IsB2BProposal = ?', $source);
}
// echo $select; die();
}
$select->order('TP.TPSysId DESC');
// echo $select;
// exit;
return $select;
}
public function getFlightBookingQueueList($ReportingToSysIds = null)
{
$arrCustomerTravelPlan = array('AgentSysId', 'StatusType', 'TPSysId', 'MasterTPSysId', 'CurrencyType', 'CustomerSysId', 'SourcePlaces', 'DestinationPlaces', 'PlanBookingId', 'VersionId');
$arrCustomerTravelPlanItenary = array('TPIntSysId');
$arrCustomerTravelPlanAir = array(
'FlightNumber',
'TotalCost',
'Cost',
'GTXMarkup',
'AgencyCommission',
'AgencyMarkUp',
'AgentServiceTaxAmount',
'SourceAirportCode',
'DestAirportCode',
'FromUTCTime',
'VersionId as AirVersionId',
'ToUTCTime',
'XRefBookingId',
'LocalToTime as returnDate',
'AgentsCustomServiceTaxAmount',
'MarkUp',
'APIBookingRes',
'TripType',
'XRefPNR',
'IsBookingStatus'
);
$arrCustomerTrx = array('TrxDate AS bookingDate');
$arrAgency = array('FirstName AS agtFirstName', 'LastName AS agtLastName');
$arrCustomer = array('FirstName AS custFirstName', 'LastName as custLastName');
$select = $this->db->select();
$select->from(array('TP' => "TB_Agency_Customer_TravelPlan"), $arrCustomerTravelPlan);
$select->joinInner(array('TPI' => "TB_Agency_Customer_TravelPlan_Itenary"), "TPI.TPSysId = TP.TPSysId", $arrCustomerTravelPlanItenary);
$select->joinInner(array('TPA' => "TB_Agency_Customer_TravelPlan_Air"), "TPA.TPIntSysId = TPI.TPIntSysId", $arrCustomerTravelPlanAir);
$select->joinleft(array('ITAA' => "TB_IC_Trans_API_Air"), "ITAA.TBQAirInvenSysId = TPA.InvnItemSysId", array('TBQAirInvenSysId', 'IsInternational', 'IsQuickProposal'));
$select->joinInner(array('TRX' => "TB_Agency_Customer_Trx"), "TRX.TPSysId = TP.TPSysId", $arrCustomerTrx);
$select->join(array('AG' => "TB_Agency_User"), "AG.UserSysId = TP.AgentSysId", $arrAgency);
$select->joinleft(array('AGCY' => "TB_Agency"), "AGCY.AgencySysId = TP.AgencySysId", ['DisplayName AS AgencyName']);
$select->joinLeft(array('AC' => "TB_Agency_Customer"), "AC.CustomerSysId = TP.CustomerSysId", $arrCustomer);
if (!empty($this->intLoggedinUserAgencySysId)) {
$select->where("TP.AgencySysId = ?", $this->intLoggedinUserAgencySysId);
}
if ($ReportingToSysIds) {
$select->where("TP.AgentSysId IN (" . $ReportingToSysIds . ")");
}
$select->where("TP.PlanType = ?", 1);
$select->where('TP.StatusType IN(?)', array(17, 10, 9, 18, 19, 71, 72, 123));
$select->order('TP.TPSysId DESC');
return $select;
}
public function viewFlightBookingDetails($intTPSysId, $version = null)
{
$arrCustomerTravelPlan = array('AgentSysId', 'StatusType', 'TPSysId', 'CustomerSysId', 'SupplierSysId', 'PackSpecType', 'EconomyMask');
$arrCustomerTravelPlanItenary = array('TPIntSysId');
$arrCustomerTravelPlanAir = array(
'VersionId',
'FlightNumber',
'Cost',
'TotalCost',
'GTXMarkup',
'AgencyCommission',
'AgencyMarkUp',
'AgentServiceTaxAmount',
'MarkUp',
'AgentsCustomServiceTaxAmount',
'SourceAirportCode',
'DestAirportCode',
'XRefBookingId',
'APIBookingRes',
'encodedata',
'TripType'
);
$arrCustomerTrx = array('TrxDate AS bookingDate');
$arrAgency = array('Title AS agentName', 'PrimaryContactName');
$select = $this->db->select();
$select->from(array('TP' => "TB_Agency_Customer_TravelPlan"), $arrCustomerTravelPlan);
$select->joinInner(array('TPI' => "TB_Agency_Customer_TravelPlan_Itenary"), "TPI.TPSysId = TP.TPSysId", $arrCustomerTravelPlanItenary);
$select->joinInner(array('TPA' => "TB_Agency_Customer_TravelPlan_Air"), "TPA.TPIntSysId = TPI.TPIntSysId", $arrCustomerTravelPlanAir);
$select->joinleft(array('ITAA' => "TB_IC_Trans_API_Air"), "ITAA.TBQAirInvenSysId = TPA.InvnItemSysId", array('TBQAirInvenSysId', 'IsInternational', 'FromUTCTime', 'ToUTCTime'));
$select->joinInner(array('TRX' => "TB_Agency_Customer_Trx"), "TRX.TPSysId = TP.TPSysId", $arrCustomerTrx);
$select->join(array('AG' => "TB_Agency"), "AG.AgencySysId = TP.AgencySysId", $arrAgency);
if (!empty($this->intLoggedinUserAgencySysId)) {
$select->where("TP.AgencySysId = ?", $this->intLoggedinUserAgencySysId);
}
$select->where("TP.PlanType = ?", 1);
$select->where('TP.TPSysId = ?', $intTPSysId);
if (!empty($version)) {
$select->where('TPA.VersionId = ?', $version);
}
//$select->limit(1);
$result = $this->db->fetchAll($select);
return $result;
}
public function viewFlightBookingDetailsPro($intTPSysId, $version = null)
{
$arrCustomerTravelPlan = array('AgentSysId', 'StatusType', 'TPSysId', 'CustomerSysId', 'SupplierSysId', 'PackSpecType', 'EconomyMask');
$arrCustomerTravelPlanItenary = array('TPIntSysId');
$arrCustomerTravelPlanAir = array(
'VersionId',
'FlightNumber',
'Cost',
'TotalCost',
'GTXMarkup',
'AgencyCommission',
'AgencyMarkUp',
'AgentServiceTaxAmount',
'MarkUp',
'AgentsCustomServiceTaxAmount',
'SourceAirportCode',
'DestAirportCode',
'XRefBookingId',
'APIBookingRes',
'encodedata',
'TripType'
);
$arrAgency = array('Title AS agentName', 'PrimaryContactName');
$select = $this->db->select();
$select->from(array('TP' => "TB_Agency_Customer_TravelPlan"), $arrCustomerTravelPlan);
$select->joinInner(array('TPI' => "TB_Agency_Customer_TravelPlan_Itenary"), "TPI.TPSysId = TP.TPSysId", $arrCustomerTravelPlanItenary);
$select->joinInner(array('TPA' => "TB_Agency_Customer_TravelPlan_Air"), "TPA.TPIntSysId = TPI.TPIntSysId", $arrCustomerTravelPlanAir);
$select->join(array('AG' => "TB_Agency"), "AG.AgencySysId = TP.AgencySysId", $arrAgency);
if (!empty($this->intLoggedinUserAgencySysId)) {
$select->where("TP.AgencySysId = ?", $this->intLoggedinUserAgencySysId);
}
$select->where("TP.PlanType = ?", 1);
$select->where('TP.TPSysId = ?', $intTPSysId);
if (!empty($version)) {
$select->where('TPA.VersionId = ?', $version);
}
//$select->limit(1);
$result = $this->db->fetchAll($select);
return $result;
}
public function GetBookingAmountData($AgencySysId, $arrIntTpAirId)
{
$arr = array('SUM(TotalCost) AS TotalCost');
$select = $this->db->select();
$select->from(array('TP' => "TB_Agency_Customer_TravelPlan_Air"), $arr);
$select->where("TP.AgencySysId = ?", $AgencySysId);
$select->where('TP.VersionId IN(?)', $arrIntTpAirId);
//echo $select->where('TPA.InvnItemSysId IN (28533,28573)');
//echo $select->limit(1); exit;
$result = $this->db->fetchRow($select);
return $result;
}
public function customerInvoiceData($intTPSysId, $AgencySysId = NULL)
{
$arrCustomerTravelPlan = array('AgentSysId', 'StatusType', 'TPSysId', 'CustomerSysId', 'RoomInfoJson');
$arrCustomerTravelPlanItenary = array('TPIntSysId');
$arrCustomerTravelPlanAir = array(
'VersionId',
'FlightNumber',
'Cost',
'TotalCost',
'GTXMarkup',
'AgencyCommission',
'AgencyMarkUp',
'AgentServiceTaxAmount',
'MarkUp',
'AgentsCustomServiceTaxAmount',
'XServiceTaxAmount',
'GTXServiceTaxAmount',
'Taxes',
'SourceAirportCode',
'DestAirportCode',
'XRefBookingId',
'APIBookingRes',
'VersionId as AirVersionId',
'encodedata'
);
$arrAirLine = array('Code AS AirLineCode', 'Title as AirLineTitle');
$arrCustomerTrx = array('TrxDate AS bookingDate');
$arrAgency = array('AgencySysId', 'Title AS CompanyName', 'PrimaryContactName', 'PrimaryEmail', 'PrimaryContactNo', 'PrimaryMobileNo', 'Logo');
$arrAgencyAddress = array('Address as CompanyAddress', 'Pincode');
$arrGC = array('Title AS CityTitle', 'Country');
$select = $this->db->select();
$select->from(array('TP' => "TB_Agency_Customer_TravelPlan"), $arrCustomerTravelPlan);
$select->joinLeft(array('TPI' => "TB_Agency_Customer_TravelPlan_Itenary"), "TPI.TPSysId = TP.TPSysId", $arrCustomerTravelPlanItenary);
$select->joinLeft(array('TPA' => "TB_Agency_Customer_TravelPlan_Air"), "TPA.TPIntSysId = TPI.TPIntSysId", $arrCustomerTravelPlanAir);
$select->joinleft(array('ITAA' => "TB_IC_Trans_API_Air"), "ITAA.TBQAirInvenSysId = TPA.InvnItemSysId", array('TBQAirInvenSysId', 'IsInternational', 'FromUTCTime', 'ToUTCTime', 'IsQuickProposal'));
$select->joinLeft(array('TMA' => "TB_Master_Airline"), "TMA.AirlineSysId = TPA.AirlineSysId", $arrAirLine);
$select->joinLeft(array('TRX' => "TB_Agency_Customer_Trx"), "TRX.TPSysId = TP.TPSysId", $arrCustomerTrx);
$select->joinLeft(array('WTRX' => "TB_Agency_Wallet_Trx"), "WTRX.XRefTPSysId = TP.TPSysId", array("InvoiceId"));
$select->joinLeft(array('AG' => "TB_Agency"), "AG.AgencySysId = TP.AgencySysId", $arrAgency);
$select->joinLeft(array('AGA' => "TB_Agency_Addresses"), "AGA.AgencySysId = AG.AgencySysId", $arrAgencyAddress);
$select->joinLeft(array('GC' => "TB_Master_Geo_City"), "GC.CityId = AG.CitySysId", $arrGC);
if (!empty($AgencySysId)) {
$select->where("TP.AgencySysId = ?", $AgencySysId);
} else if (!empty($this->intLoggedinUserAgencySysId)) {
$select->where("TP.AgencySysId = ?", $this->intLoggedinUserAgencySysId);
}
$select->where("TP.PlanType = ?", 1);
$select->where("TPA.IsBookingStatus = ?", 1);
$select->where('TP.TPSysId = ?', $intTPSysId);
//echo $select->where('TPA.InvnItemSysId IN (28533,28573)');
//echo $select->limit(1); exit;
$result = $this->db->fetchAll($select);
return $result;
}
// By SIBO
public function customerInvoiceDataBySabir($intTPSysId, $versionId = NULL, $AgencySysId = NULL)
{
// $sql = " SELECT * FROM TB_IC_Trans_API_Air";
// $result = $this->db->query($sql)->fetch();
// echo '<pre>';
// print_r($result); AgentEmailId
// die('dd');
$arrCustomerTravelPlan = array('AgentSysId', 'SupplierSysId', 'StatusType', 'TPSysId', 'MasterTPSysId', 'CustomerSysId', 'RoomInfoJson', 'ChangeRequestId', 'IsB2BProposal', "((select SUM(adT1.Tax) FROM TB_Agency_Customer_TravelPlan_AddonServices adT1 where adT1.IsMarkForDelete = 0 AND TP.TPSysId = adT1.TPSysId AND (adT1.PlanType = 19))) as TotalTcs", "((select SUM(adT2.TotalCost) FROM TB_Agency_Customer_TravelPlan_AddonServices adT2 where adT2.IsMarkForDelete = 0 AND TP.TPSysId = adT2.TPSysId AND (adT2.PlanType = 23)) / (select COUNT(adT3.AddOnServices) FROM TB_Agency_Customer_TravelPlan_AddonServices adT3 where adT3.IsMarkForDelete = 0 AND TP.TPSysId = adT3.TPSysId AND (adT3.PlanType = 23)) ) as AddTotalCost");
$arrCustomerTravelPlanItenary = array('TPIntSysId');
$arrCustomerTravelPlanAir = array(
'SeqId',
'VersionId',
'IsBookingStatus',
'FlightNumber',
'Cost',
'TotalCost',
'GTXMarkup',
'AgencyCommission',
'AgencyMarkUp',
'AgentServiceTaxAmount',
'MarkUp',
'AgentsCustomServiceTaxAmount',
'XServiceTaxAmount',
'GTXServiceTaxAmount',
'Taxes',
'SourceAirportCode',
'DestAirportCode',
'CreateDate as bookingDate',
'XRefBookingId',
'AirlineRefPNR',
'VersionId as AirVersionId',
'APIBookingRes',
'TripType',
'ExtraMarkup',
'ExtraMarkupOnGST',
'LocalFromTime'
);
$arrAirLine = array('Code AS AirLineCode', 'Title as AirLineTitle');
//$arrCustomerTrx = array('TrxDate AS bookingDate', 'XRefTrxNo');
$arrAgency = array('AgencySysId', 'DisplayName', 'Title AS CompanyName', 'PrimaryContactName', 'PrimaryEmail', 'PrimaryContactNo', 'PrimaryMobileNo', 'Logo', 'countrycode', 'IsB2bSiteurl', 'ServiceTaxNo as AgencyGst');
$arrAgencyAddress = array('Address as CompanyAddress', 'Pincode');
$arrGC = array('Title AS CityTitle', 'Country');
$arrGCAgent = array('Title AS agentCityTitle', 'Country as agentCountry');
$arrCurrency = array('Symbol AS CurrencySymbol', 'Title as CurrencyTitle');
$LeadSource = array('Title AS LeadSource', 'LeadSourceSysId');
$select = $this->db->select();
$select->from(array('TP' => "TB_Agency_Customer_TravelPlan"), $arrCustomerTravelPlan);
$select->joinLeft(array('TPI' => "TB_Agency_Customer_TravelPlan_Itenary"), "TPI.TPSysId = TP.TPSysId", $arrCustomerTravelPlanItenary);
$select->joinLeft(array('TPA' => "TB_Agency_Customer_TravelPlan_Air"), "TPA.TPIntSysId = TPI.TPIntSysId", $arrCustomerTravelPlanAir);
$select->joinleft(array('ITAA' => "TB_IC_Trans_API_Air"), "ITAA.TBQAirInvenSysId = TPA.InvnItemSysId", array('TBQAirInvenSysId', 'JourneyType', 'IsInternational', 'FromUTCTime', 'ToUTCTime', 'IsRefundable', 'ICSourceSysId', 'IsNewMigration'));
$select->joinLeft(array('TMA' => "TB_Master_Airline"), "TMA.AirlineSysId = TPA.AirlineSysId", $arrAirLine);
//$select->joinLeft(array('TRX' => "TB_Agency_Customer_Trx"), "TRX.TPSysId = TP.TPSysId AND TRX.TrxStatus = 1", $arrCustomerTrx);
$select->joinLeft(array('WTRX' => "TB_Agency_Wallet_Trx"), "WTRX.XRefTPSysId = TP.TPSysId AND WTRX.IsApproved = 1", array("InvoiceId"));
$select->joinLeft(array('AG' => "TB_Agency"), "AG.AgencySysId = TP.AgencySysId", $arrAgency);
$select->joinLeft(array('AGA' => "TB_Agency_Addresses"), "AGA.AgencySysId = AG.AgencySysId", $arrAgencyAddress);
$select->joinLeft(array('GC' => "TB_Master_Geo_City"), "GC.CityId = AG.CitySysId", $arrGC);
$select->joinLeft(array('CU' => "TB_Master_Currency"), "CU.CurrencyType = ITAA.CurrencyType", $arrCurrency);
$select->joinLeft(array('TAC' => "TB_Agency_Customer"), "TP.CustomerSysId = TAC.CustomerSysId", array('CustomerSysId', 'EmailId', 'GstNumber'));
$select->joinLeft(array('TAU' => "TB_Agency_User"), "TAU.UserSysId = TAC.CreatedByUserSysId", array('FirstName as agentName', 'Address as agentAdddress', 'PinCode as agentPinCode', 'EmailId as AgentEmailId', 'AgencyName as AgentAgencyName', 'UserPicPath', 'AgencySysId as B2BAgencySysId', 'UserSysId', 'ContactNo1 as agentmobile'));
$select->joinLeft(array('GCA' => "TB_Master_Geo_City"), "GCA.CityId = TAU.CitySysId", $arrGCAgent);
$select->joinLeft(array('TB' => 'TB_Agency'), 'TB.AgencySysId=TAU.AgencySysId', array('TB.AdharCardNo', 'TB.MasterAgencySysId', 'Logo as UserAgencyLogo'));
$select->joinLeft(array('TAUU' => "TB_Agency_User"), "AG.PrimaryUserSysId = TAUU.UserSysId", array('ContactNo2', 'countrycode2', 'SecondaryEmail'));
$select->joinLeft(array('TACL' => "TB_Agency_CRM_LeadAccount"), "TACL.AgencyCRMLeadSysId = TAC.AgencyCRMLeadSysId", array("B2BType"));
$select->joinLeft(array('LDS' => 'TB_Master_LeadSource'), 'LDS.LeadSourceSysId = TP.LeadSourceSysId ', $LeadSource);
$select->joinLeft(array('INV' => "TB_Agency_Miscellaneous"), "TP.TPSysId = INV.TPSysId", array('InvoiceNumber', 'CreateDate as InvoiceDate'));
//$select->joinLeft(array('TBADDON' => "TB_Agency_Customer_TravelPlan_AddonServices"), "TP.TPSysId = TBADDON.TPSysId AND TBADDON.PlanType = 23", array('Cost as AddCost', 'Tax as AddTax', 'TotalCost as AddTotalCost'));
if (!empty($AgencySysId)) {
$select->where("TP.AgencySysId = ?", $AgencySysId);
} else if (!empty($this->intLoggedinUserAgencySysId)) {
if ($this->agencyType == 2) {
$select->where("TP.AgencySysId = $this->intLoggedinUserAgencySysId OR TP.AgencySysId = $this->getMasterAgencySysId");
} else {
$select->where("TP.AgencySysId = ?", $this->intLoggedinUserAgencySysId);
}
}
$select->where("TP.PlanType = ?", 1);
//$select->where("TRX.IsActive = ?", 1);
$select->where('TP.TPSysId = ?', $intTPSysId);
if (!empty($versionId)) {
$select->where('TPA.VersionId = ?', $versionId);
}
//echo $select->where('TPA.InvnItemSysId IN (28533,28573)');
//echo $select;
// exit;
$result = $this->db->fetchAll($select);
return $result;
}
public function bookingDataByAPI($intTPSysId, $versionId = NULL, $AgencySysId = NULL, $SecurityKey = NULL, $IsPackage = 0)
{
$arrCustomerTravelPlan = array('AgentSysId', 'StatusType', 'BillingType', 'PaymentStatus', 'ChangeRequestStatus', 'ExchangeCurrencyRate', 'BookingStatus as CurrentStatus', 'TPSysId', 'MasterTPSysId', 'CustomerSysId', 'ChangeRequestId', 'IsB2BProposal', "((select SUM(adT1.Tax) FROM TB_Agency_Customer_TravelPlan_AddonServices adT1 where adT1.IsMarkForDelete = 0 AND TP.TPSysId = adT1.TPSysId AND (adT1.PlanType = 19))) as TotalTcs", "((select SUM(TACT.PaidAmount) FROM TB_Agency_Customer_Trx TACT where TACT.TrxStatus = 1 AND TP.TPSysId = TACT.TPSysId)) as TotalPaidAmount", "((select SUM(adT2.TotalCost) FROM TB_Agency_Customer_TravelPlan_AddonServices adT2 where adT2.IsMarkForDelete = 0 AND TP.TPSysId = adT2.TPSysId AND (adT2.PlanType = 23)) / (select COUNT(adT3.AddOnServices) FROM TB_Agency_Customer_TravelPlan_AddonServices adT3 where adT3.IsMarkForDelete = 0 AND TP.TPSysId = adT3.TPSysId AND (adT3.PlanType = 23)) ) as AddTotalCost");
$arrCustomerTravelPlanItenary = array('TPIntSysId');
$arrCustomerTravelPlanAir = array(
'FlightNumber',
'Cost',
'TotalCost',
'AgencyMarkUp',
'AgentServiceTaxAmount',
'AgencyCommission',
'SourceAirportCode',
'DestAirportCode',
'CreateDate as bookingDate',
'XRefBookingId',
'AirlineRefPNR',
'AirlineRefGDSPNR',
'AgentMarkup',
'VersionId as AirVersionId',
'TripType',
'ExtraMarkup',
'ExtraMarkupOnGST',
'LocalFromTime',
'LocalToTime',
'IsBookingStatus',
'APIBookingRes',
'APIBookingId',
'DeadLineDate as LastTicketDate',
'ApprovalDate as ConfirmDate',
'IsFixedTransport as IsHoldTicket',
'flexiPayment',
'DeadLineDate',
'mobile',
'emailid',
'SupplierSysId',
'ImgeDetails as errorCode',
'encodedata',
);
$arrAirLine = array('Code AS AirLineCode', 'Title as AirLineTitle');
$arrAgency = array('AgencySysId', 'DisplayName', 'Title AS CompanyName', 'PrimaryContactName', 'PrimaryEmail', 'PrimaryContactNo', 'PrimaryMobileNo', 'Logo', 'countrycode', 'IsB2bSiteurl');
$arrAgencyAddress = array('Address as CompanyAddress', 'Pincode');
$arrGC = array('Title AS CityTitle', 'Country');
$arrGCMaster = array('Title AS MasterCityTitle', 'Country as MasterCountry');
$arrCurrency = array('Symbol AS CurrencySymbol', 'Title as CurrencyTitle', 'CurrencyType');
$LeadSource = array('Title AS LeadSource', 'LeadSourceSysId');
$select = $this->db->select();
$select->from(array('TP' => "TB_Agency_Customer_TravelPlan"), $arrCustomerTravelPlan);
$select->joinLeft(array('TPI' => "TB_Agency_Customer_TravelPlan_Itenary"), "TPI.TPSysId = TP.TPSysId", $arrCustomerTravelPlanItenary);
$select->joinLeft(array('TPA' => "TB_Agency_Customer_TravelPlan_Air"), "TPA.TPIntSysId = TPI.TPIntSysId", $arrCustomerTravelPlanAir);
$select->joinleft(array('ITAA' => "TB_IC_Trans_API_Air"), "ITAA.TBQAirInvenSysId = TPA.InvnItemSysId", array('TBQAirInvenSysId', 'JourneyType', 'IsInternational', 'IsRefundable', 'ICSourceSysId', 'IsNewMigration'));
$select->joinLeft(array('TMA' => "TB_Master_Airline"), "TMA.AirlineSysId = TPA.AirlineSysId", $arrAirLine);
$select->joinLeft(array('WTRX' => "TB_Agency_Wallet_Trx"), "WTRX.XRefTPSysId = TP.TPSysId AND WTRX.IsApproved = 1", array("InvoiceId"));
$select->joinLeft(array('TAC' => "TB_Agency_Customer"), "TP.CustomerSysId = TAC.CustomerSysId", array('CustomerSysId', 'AgencySysId as CustomerAgencySysId', 'CreatedByUserSysId', 'EmailId', 'GstNumber', 'GstCompany', 'GstPhone', 'GstEmail', 'GstState', 'GstAddress'));
if (!empty($SecurityKey)) {
$select->joinLeft(array('AG' => "TB_Agency"), "AG.AgencySysId = TAC.AgencySysId and TP.IsB2BProposal = 1", $arrAgency);
$select->joinLeft(array('TAU' => "TB_Agency_User"), "TAU.UserSysId = AG.PrimaryUserSysId", array('FirstName as agentName', 'LastName as agentLastName', 'Salutation as agentTitle', 'ContactNo1 as agentContactNo1', 'EmailId as AgentEmailId', 'AgencyName as AgentAgencyName', 'UserPicPath', 'UserSysId', 'AgencySysId as B2BAgencySysId'));
} else {
$select->joinLeft(array('AG' => "TB_Agency"), "AG.AgencySysId = TP.AgencySysId", $arrAgency);
$select->joinLeft(array('TAU' => "TB_Agency_User"), "TAU.UserSysId = TAC.CreatedByUserSysId", array('FirstName as agentName', 'LastName as agentLastName', 'Salutation as agentTitle', 'ContactNo1 as agentContactNo1', 'EmailId as AgentEmailId', 'AgencyName as AgentAgencyName', 'UserPicPath', 'UserSysId', 'AgencySysId as B2BAgencySysId'));
}
$select->joinLeft(array('MAG' => "TB_Agency"), "MAG.AgencySysId = TP.AgencySysId", array('DisplayName as MasterDisplayName', 'AgencySysId as MasterAgencySysId', 'ServiceTaxNo as AgencyGst', 'PrimaryEmail as MasterPrimaryEmail', 'PrimaryContactNo as MasterPrimaryContactNo', 'PrimaryMobileNo as MasterPrimaryMobileNo', 'Logo as MasterLogo', 'countrycode as Mastercountrycode',));
$select->joinLeft(array('MAGA' => "TB_Agency_Addresses"), "MAGA.AgencySysId = MAG.AgencySysId", array('Address as MasterAddress', 'Pincode as MasterPincode'));
$select->joinLeft(array('AGA' => "TB_Agency_Addresses"), "AGA.AgencySysId = AG.AgencySysId", $arrAgencyAddress);
$select->joinLeft(array('MGC' => "TB_Master_Geo_City"), "MGC.CityId = MAG.CitySysId", $arrGCMaster);
$select->joinLeft(array('GC' => "TB_Master_Geo_City"), "GC.CityId = AG.CitySysId", $arrGC);
$select->joinLeft(array('CU' => "TB_Master_Currency"), "CU.CurrencyType = ITAA.CurrencyType", $arrCurrency);
$select->joinLeft(array('TB' => 'TB_Agency'), 'TB.AgencySysId=TAU.AgencySysId', array('TB.AdharCardNo', 'Logo as UserAgencyLogo'));
$select->joinLeft(array('TAUU' => "TB_Agency_User"), "AG.PrimaryUserSysId = TAUU.UserSysId", array('ContactNo2', 'countrycode2', 'SecondaryEmail'));
$select->joinLeft(array('TACL' => "TB_Agency_CRM_LeadAccount"), "TACL.AgencyCRMLeadSysId = TAC.AgencyCRMLeadSysId", array("B2BType"));
$select->joinLeft(array('LDS' => 'TB_Master_LeadSource'), 'LDS.LeadSourceSysId = TP.LeadSourceSysId ', $LeadSource);
$select->joinLeft(array('INV' => "TB_Agency_Miscellaneous"), "TP.TPSysId = INV.TPSysId", array('InvoiceNumber', 'MasterMiscSysId', 'CreateDate as InvoiceDate'));
//$select->joinLeft(array('TBADDON' => "TB_Agency_Customer_TravelPlan_AddonServices"), "TP.TPSysId = TBADDON.TPSysId AND TBADDON.PlanType = 23", array('Cost as AddCost', 'Tax as AddTax', 'TotalCost as AddTotalCost'));
//echo $select; CurrencySymbol
// die;
if (!empty($AgencySysId)) {
if (!empty($SecurityKey)) {
$select->where("TAC.AgencySysId = ?", $AgencySysId);
} else {
$select->where("TP.AgencySysId = ?", $AgencySysId);
}
//$select->where("TP.AgencySysId = ?", $AgencySysId);
} else if (!empty($this->intLoggedinUserAgencySysId)) {
if ($this->agencyType == 2) {
$select->where("TP.AgencySysId = $this->intLoggedinUserAgencySysId OR TP.AgencySysId = $this->getMasterAgencySysId");
} else {
$select->where("TP.AgencySysId = ?", $this->intLoggedinUserAgencySysId);
}
}
$select->where("ITAA.TBQAirInvenSysId != ''");
//$select->where("TP.PlanType = ?", 1);
$select->where(new Zend_Db_Expr(
'TP.PlanType = CASE WHEN EXISTS (SELECT 1 FROM TB_Agency_Customer_TravelPlan WHERE TP.TPSysId = ' . $intTPSysId . ' AND TP.PlanType = 1) THEN 1 ELSE 5 END',
$intTPSysId
));
$select->where('TP.TPSysId = ?', $intTPSysId);
if (!empty($versionId)) {
$select->where('TPA.VersionId = ?', $versionId);
}
if ($IsPackage == 1) {
$select->order('TPA.VersionId ASC');
}
// echo $select;
// exit;
$result = $this->db->fetchAll($select);
$JourneyType = isset($result[0]['JourneyType']) ? $result[0]['JourneyType'] : 0;
$IsInternational = isset($result[0]['IsInternational']) ? $result[0]['IsInternational'] : 0;
$dataSet = [];
if ($result) {
foreach ($result as $key => $value) {
$dataSet[$key] = $value;
$arrAirSegment = array(
"AirInvenAPISysId",
"TripIndicator",
"Baggage",
"CabinBaggage",
"SegmentIndicator",
"OriginAirportCode",
"DestAirportCode",
"Duration",
"GroundTime",
"StopPoint",
"StopPointArrivalTime",
"Craft",
"Remark",
"IsETicketEligible",
"FlightStatus",
"Status",
"segmentid",
"arrivingnextday",
"originDepTime",
"destinationArrTime",
"FlightNumber",
"AirlineCode",
"AirlineName",
"FareClass ",
"cabinClass",
"IsMealIncludes",
"LAYOVERDuration",
"logo",
"DepTerminal",
"ArrTerminal"
);
$arrFareBreakdown = array(
"TBQAirInvenAPISysId",
"Currency",
"PassengerType",
"PassengerCount",
"BaseFare",
"Tax",
"YQTax",
"AdditionalTxnFeeOfrd",
"AdditionalTxnFeePub",
"CommissionEarned",
"OtherCharges",
"FixedMarkUp",
"GSTOnMarkUp",
"ServiceFee",
"Discount",
"OfferedFare",
"TotalFare",
"TDSEarn",
"MF",
"AGST",
"MFT",
"YR",
"couponVal"
);
$arrFareRule = array(
"TBOAirInvenAPISysId",
"Currency",
"BaseFare",
"Tax",
"couponVal",
"Discount",
"ServiceFee",
"PublishedFare",
"CommissionEarned",
"OfferedFare",
"TdsOnCommission",
"fareIdentifier",
"classOfBooking",
"Refundable",
"IsActive",
);
$arrAirPort = array('AirportTitle as originAirportName', 'CountryCode as originCountryCode');
$arrCity = array('Title as originCityName');
$arrAirPortD = array('AirportTitle as destinationAirportName', 'CountryCode as destinationCountryCode');
$arrCityD = array('Title as destinationCityName');
$selectSG = $this->db->select();
$selectSG->from(array('t1' => 'TB_IC_Trans_API_Air_Segment'), $arrAirSegment);
$selectSG->joinLeft(array('tb2' => "TB_Master_Airport"), "t1.OriginAirportCode = tb2.AirportCode", $arrAirPort);
$selectSG->joinLeft(array('tb3' => "TB_Master_Geo_City"), "tb3.CityId = tb2.CityId", $arrCity);
$selectSG->joinLeft(array('tb4' => "TB_Master_Airport"), "t1.DestAirportCode = tb4.AirportCode", $arrAirPortD);
$selectSG->joinLeft(array('tb5' => "TB_Master_Geo_City"), "tb5.CityId = tb4.CityId", $arrCityD);
$selectSG->where("t1.AirInvenAPISysId = ?", $value['TBQAirInvenSysId']);
if ($IsInternational != '1' && $JourneyType != '2') {
//$selectSG->where("t1.TripIndicator = ?", $key);
}
// $selectSG->where("tb2.IsActive = ?", 1);
// $selectSG->where("tb2.IsMarkForDel = ?", 0);
// $selectSG->where("tb3.IsActive = ?", 1);
// $selectSG->where("tb3.IsApproved = ?", 1);
// $selectSG->where("tb4.IsActive = ?", 1);
// $selectSG->where("tb4.IsMarkForDel = ?", 0);
// $selectSG->where("tb5.IsActive = ?", 1);
// $selectSG->where("tb5.IsApproved = ?", 1);
$selectSG->order('t1.SegmentSysId ASC');
$resultSeg = $this->db->fetchAll($selectSG);
$selectFBR = $this->db->select();
$selectFBR->from(array('t1' => 'TB_IC_Trans_API_Air_FareBreakdown'), $arrFareBreakdown);
$selectFBR->where("t1.TBQAirInvenAPISysId = ?", $value['TBQAirInvenSysId']);
$resultFbr = $this->db->fetchAll($selectFBR);
$selectFBRULE = $this->db->select();
$selectFBRULE->from(array('t1' => 'TB_IC_Trans_API_Air_Fare'), $arrFareRule);
$selectFBRULE->where("t1.TBOAirInvenAPISysId = ?", $value['TBQAirInvenSysId']);
$resultFbrule = $this->db->fetchRow($selectFBRULE);
$arrTRX = array('SUM(tb2.NetAmount) AS TrxNetAmount', 'SUM(tb2.PaidAmount) AS TrxPaidAmount');
$selectTRX = $this->db->select();
$selectTRX->from(array('t1' => 'TB_Agency_Customer_Trx'), ['TPSysId']);
$selectTRX->joinLeft(array('tb2' => "TB_Trx"), "t1.TrxSysId = tb2.TrxSysId", $arrTRX);
$selectTRX->where("t1.TPSysId = ?", $value['TPSysId']);
$selectTRX->where("t1.TrxStatus = ?", 1);
$selectTRX->where("t1.IsActive = ?", 1);
$selectTRX->where("tb2.TrxStatus = ?", 1);
$selectTRX->where("tb2.IsActive = ?", 1);
$selectTRX->group(array("t1.TPSysId"));
$resultTRX = $this->db->fetchRow($selectTRX);
$TRX = [];
if ($resultTRX) {
$TRX = [
'TrxNetAmount' => ($resultTRX['TrxNetAmount'] / count($result)),
'TrxPaidAmount' => ($resultTRX['TrxPaidAmount'] / count($result)),
];
}
$dataSet[$key]['resultTRX'] = $TRX;
$dataSet[$key]['FairRules'] = $resultFbrule;
$dataSet[$key]['FareBreakdown'] = $resultFbr;
$dataSet[$key]['Segments'] = $resultSeg;
}
}
return $dataSet;
}
public function bookingDataByAPIOffline($intTPSysId, $versionId = NULL, $AgencySysId = NULL, $SecurityKey = NULL)
{
$arrCustomerTravelPlan = array('AgentSysId', 'StatusType', 'BillingType', 'PaymentStatus', 'ChangeRequestStatus', 'ExchangeCurrencyRate', 'BookingStatus as CurrentStatus', 'TPSysId', 'MasterTPSysId', 'CustomerSysId', 'ChangeRequestId', 'IsB2BProposal', "((select SUM(adT1.Tax) FROM TB_Agency_Customer_TravelPlan_AddonServices adT1 where adT1.IsMarkForDelete = 0 AND TP.TPSysId = adT1.TPSysId AND (adT1.PlanType = 19))) as TotalTcs", "((select SUM(TACT.PaidAmount) FROM TB_Agency_Customer_Trx TACT where TACT.TrxStatus = 1 AND TP.TPSysId = TACT.TPSysId)) as TotalPaidAmount", "((select SUM(adT2.TotalCost) FROM TB_Agency_Customer_TravelPlan_AddonServices adT2 where adT2.IsMarkForDelete = 0 AND TP.TPSysId = adT2.TPSysId AND (adT2.PlanType = 23)) / (select COUNT(adT3.AddOnServices) FROM TB_Agency_Customer_TravelPlan_AddonServices adT3 where adT3.IsMarkForDelete = 0 AND TP.TPSysId = adT3.TPSysId AND (adT3.PlanType = 23)) ) as AddTotalCost");
$arrCustomerTravelPlanItenary = array('TPIntSysId');
$arrCustomerTravelPlanAir = array(
'FlightNumber',
'Cost',
'TotalCost',
'AgencyMarkUp',
'AgentServiceTaxAmount',
'AgencyCommission',
'SourceAirportCode',
'DestAirportCode',
'CreateDate as bookingDate',
'XRefBookingId',
'AirlineRefPNR',
'AirlineRefGDSPNR',
'AgentMarkup',
'VersionId as AirVersionId',
'TripType',
'ExtraMarkup',
'ExtraMarkupOnGST',
'LocalFromTime',
'LocalToTime',
'IsBookingStatus',
'APIBookingRes',
'APIBookingId',
'DeadLineDate as LastTicketDate',
'ApprovalDate as ConfirmDate',
'IsFixedTransport as IsHoldTicket',
'flexiPayment',
'DeadLineDate',
'mobile',
'emailid',
'SupplierSysId',
'TPIntSysId',
'ImgeDetails as errorCode',
);
$arrAirLine = array('Code AS AirLineCode', 'Title as AirLineTitle');
$arrAgency = array('AgencySysId', 'DisplayName', 'Title AS CompanyName', 'PrimaryContactName', 'PrimaryEmail', 'PrimaryContactNo', 'PrimaryMobileNo', 'Logo', 'countrycode', 'IsB2bSiteurl');
$arrAgencyAddress = array('Address as CompanyAddress', 'Pincode');
$arrGC = array('Title AS CityTitle', 'Country');
$arrGCMaster = array('Title AS MasterCityTitle', 'Country as MasterCountry');
$arrCurrency = array('Symbol AS CurrencySymbol', 'Title as CurrencyTitle', 'CurrencyType');
$LeadSource = array('Title AS LeadSource', 'LeadSourceSysId');
$select = $this->db->select()
->from(array('TP' => 'TB_Agency_Customer_TravelPlan'), array(
'AgentSysId',
'StatusType',
'BillingType',
'PaymentStatus',
'ChangeRequestStatus',
'ExchangeCurrencyRate',
'BookingStatus as CurrentStatus',
'TPSysId',
'MasterTPSysId',
'CustomerSysId',
'ChangeRequestId',
'IsB2BProposal'
));
$select->columns(array(
'TotalTcs' => new Zend_Db_Expr("(SELECT SUM(adT1.Tax)
FROM TB_Agency_Customer_TravelPlan_AddonServices adT1
WHERE adT1.IsMarkForDelete = 0
AND TP.TPSysId = adT1.TPSysId
AND adT1.PlanType = 19)"),
'TotalPaidAmount' => new Zend_Db_Expr("(SELECT SUM(TACT.PaidAmount)
FROM TB_Agency_Customer_Trx TACT
WHERE TACT.TrxStatus = 1
AND TP.TPSysId = TACT.TPSysId)"),
'AddTotalCost' => new Zend_Db_Expr("(SELECT SUM(adT2.TotalCost)
FROM TB_Agency_Customer_TravelPlan_AddonServices adT2
WHERE adT2.IsMarkForDelete = 0
AND TP.TPSysId = adT2.TPSysId
AND adT2.PlanType = 23) /
(SELECT COUNT(adT3.AddOnServices)
FROM TB_Agency_Customer_TravelPlan_AddonServices adT3
WHERE adT3.IsMarkForDelete = 0
AND TP.TPSysId = adT3.TPSysId
AND adT3.PlanType = 23)")
));
$tpiSubquery = $this->db->select()
->from('TB_Agency_Customer_TravelPlan_Itenary', array('TPIntSysId', 'TPSysId'))
->distinct();
$select->joinLeft(
array('TPI' => new Zend_Db_Expr('(' . $tpiSubquery . ')')),
'TPI.TPSysId = TP.TPSysId',
array()
);
$select->joinInner(
array('TPA' => 'TB_Agency_Customer_TravelPlan_Air'),
'TPA.TPIntSysId = TPI.TPIntSysId',
$arrCustomerTravelPlanAir
);
$select->joinLeft(
array('TMA' => 'TB_Master_Airline'),
'TMA.AirlineSysId = TPA.AirlineSysId',
$arrAirLine
);
$select->joinLeft(
array('WTRX' => 'TB_Agency_Wallet_Trx'),
'WTRX.XRefTPSysId = TP.TPSysId AND WTRX.IsApproved = 1',
array("InvoiceId")
);
$select->joinLeft(
array('TAC' => 'TB_Agency_Customer'),
'TP.CustomerSysId = TAC.CustomerSysId',
array('CustomerSysId', 'AgencySysId as CustomerAgencySysId', 'CreatedByUserSysId', 'EmailId', 'GstNumber', 'GstCompany', 'GstPhone', 'GstEmail', 'GstState', 'GstAddress')
);
$select->joinLeft(
array('AG' => 'TB_Agency'),
'AG.AgencySysId = TP.AgencySysId',
$arrAgency
);
$select->joinLeft(
array('TAU' => 'TB_Agency_User'),
'TAU.UserSysId = TAC.CreatedByUserSysId',
array('FirstName as agentName', 'LastName as agentLastName', 'Salutation as agentTitle', 'ContactNo1 as agentContactNo1', 'EmailId as AgentEmailId', 'AgencyName as AgentAgencyName', 'UserPicPath', 'UserSysId', 'AgencySysId as B2BAgencySysId')
);
$select->joinLeft(array('MAG' => "TB_Agency"), "MAG.AgencySysId = TP.AgencySysId", array('DisplayName as MasterDisplayName', 'AgencySysId as MasterAgencySysId', 'ServiceTaxNo as AgencyGst', 'PrimaryEmail as MasterPrimaryEmail', 'PrimaryContactNo as MasterPrimaryContactNo', 'PrimaryMobileNo as MasterPrimaryMobileNo', 'Logo as MasterLogo', 'countrycode as Mastercountrycode',));
$select->joinLeft(array('MAGA' => "TB_Agency_Addresses"), "MAGA.AgencySysId = MAG.AgencySysId", array('Address as MasterAddress', 'Pincode as MasterPincode'));
$select->joinLeft(array('AGA' => "TB_Agency_Addresses"), "AGA.AgencySysId = AG.AgencySysId", $arrAgencyAddress);
$select->joinLeft(array('MGC' => "TB_Master_Geo_City"), "MGC.CityId = MAG.CitySysId", $arrGCMaster);
$select->joinLeft(array('GC' => "TB_Master_Geo_City"), "GC.CityId = AG.CitySysId", $arrGC);
$select->joinLeft(array('TB' => 'TB_Agency'), 'TB.AgencySysId=TAU.AgencySysId', array('TB.AdharCardNo', 'Logo as UserAgencyLogo'));
$select->joinLeft(array('TAUU' => "TB_Agency_User"), "AG.PrimaryUserSysId = TAUU.UserSysId", array('ContactNo2', 'countrycode2', 'SecondaryEmail'));
$select->joinLeft(array('TACL' => "TB_Agency_CRM_LeadAccount"), "TACL.AgencyCRMLeadSysId = TAC.AgencyCRMLeadSysId", array("B2BType"));
$select->joinLeft(array('LDS' => 'TB_Master_LeadSource'), 'LDS.LeadSourceSysId = TP.LeadSourceSysId ', $LeadSource);
$select->joinLeft(array('INV' => "TB_Agency_Miscellaneous"), "TP.TPSysId = INV.TPSysId", array('InvoiceNumber', 'MasterMiscSysId', 'CreateDate as InvoiceDate'));
$caseCondition = $this->db->quoteInto(
'TP.PlanType = CASE WHEN EXISTS (SELECT 1 FROM TB_Agency_Customer_TravelPlan WHERE TPSysId = ? AND PlanType = 1) THEN 1 ELSE 5 END',
$intTPSysId
);
$select->where(new Zend_Db_Expr($caseCondition));
if (!empty($AgencySysId)) {
if (!empty($SecurityKey)) {
$select->where("TAC.AgencySysId = ?", $AgencySysId);
} else {
$select->where("TP.AgencySysId = ?", $AgencySysId);
}
} else if (!empty($this->intLoggedinUserAgencySysId)) {
if ($this->agencyType == 2) {
$select->where("TP.AgencySysId = $this->intLoggedinUserAgencySysId OR TP.AgencySysId = $this->getMasterAgencySysId");
} else {
$select->where("TP.AgencySysId = ?", $this->intLoggedinUserAgencySysId);
}
}
$select->where('TP.TPSysId = ?', $intTPSysId);
if (!empty($versionId)) {
$select->where('TPA.VersionId = ?', $versionId);
}
$result = $this->db->fetchAll($select);
// echo '<pre>';
// print_r($result);
// die('dd');
$JourneyType = isset($result[0]['JourneyType']) ? $result[0]['JourneyType'] : 0;
$IsInternational = isset($result[0]['IsInternational']) ? $result[0]['IsInternational'] : 0;
$dataSet = [];
if ($result) {
foreach ($result as $key => $value) {
$dataSet[$key] = $value;
if ($value['TBQAirInvenSysId'] && $value['TBQAirInvenSysId'] > 0) {
$arrAirSegment = array(
"AirInvenAPISysId",
"TripIndicator",
"Baggage",
"CabinBaggage",
"SegmentIndicator",
"OriginAirportCode",
"DestAirportCode",
"Duration",
"GroundTime",
"StopPoint",
"StopPointArrivalTime",
"Craft",
"Remark",
"IsETicketEligible",
"FlightStatus",
"Status",
"segmentid",
"arrivingnextday",
"originDepTime",
"destinationArrTime",
"FlightNumber",
"AirlineCode",
"AirlineName",
"FareClass ",
"cabinClass",
"IsMealIncludes",
"LAYOVERDuration",
"logo",
"DepTerminal",
"ArrTerminal"
);
$arrFareBreakdown = array(
"TBQAirInvenAPISysId",
"Currency",
"PassengerType",
"PassengerCount",
"BaseFare",
"Tax",
"YQTax",
"AdditionalTxnFeeOfrd",
"AdditionalTxnFeePub",
"CommissionEarned",
"OtherCharges",
"FixedMarkUp",
"GSTOnMarkUp",
"ServiceFee",
"Discount",
"OfferedFare",
"TotalFare",
"TDSEarn",
"MF",
"AGST",
"MFT",
"YR",
"couponVal"
);
$arrFareRule = array(
"TBOAirInvenAPISysId",
"Currency",
"BaseFare",
"Tax",
"couponVal",
"Discount",
"ServiceFee",
"PublishedFare",
"CommissionEarned",
"OfferedFare",
"TdsOnCommission",
"fareIdentifier",
"classOfBooking",
"Refundable",
"IsActive",
);
$arrAirPort = array('AirportTitle as originAirportName', 'CountryCode as originCountryCode');
$arrCity = array('Title as originCityName');
$arrAirPortD = array('AirportTitle as destinationAirportName', 'CountryCode as destinationCountryCode');
$arrCityD = array('Title as destinationCityName');
$selectSG = $this->db->select();
$selectSG->from(array('t1' => 'TB_IC_Trans_API_Air_Segment'), $arrAirSegment);
$selectSG->joinInner(array('tb2' => "TB_Master_Airport"), "t1.OriginAirportCode = tb2.AirportCode", $arrAirPort);
$selectSG->joinInner(array('tb3' => "TB_Master_Geo_City"), "tb3.CityId = tb2.CityId", $arrCity);
$selectSG->joinInner(array('tb4' => "TB_Master_Airport"), "t1.DestAirportCode = tb4.AirportCode", $arrAirPortD);
$selectSG->joinInner(array('tb5' => "TB_Master_Geo_City"), "tb5.CityId = tb4.CityId", $arrCityD);
$selectSG->where("t1.AirInvenAPISysId = ?", $value['TBQAirInvenSysId']);
if ($IsInternational != '1' && $JourneyType != '2') {
//$selectSG->where("t1.TripIndicator = ?", $key);
}
$selectSG->where("tb2.IsActive = ?", 1);
$selectSG->where("tb2.IsMarkForDel = ?", 0);
$selectSG->where("tb3.IsActive = ?", 1);
$selectSG->where("tb3.IsApproved = ?", 1);
$selectSG->where("tb4.IsActive = ?", 1);
$selectSG->where("tb4.IsMarkForDel = ?", 0);
$selectSG->where("tb5.IsActive = ?", 1);
$selectSG->where("tb5.IsApproved = ?", 1);
$selectSG->order('t1.SegmentSysId ASC');
$resultSeg = $this->db->fetchAll($selectSG);
$selectFBR = $this->db->select();
$selectFBR->from(array('t1' => 'TB_IC_Trans_API_Air_FareBreakdown'), $arrFareBreakdown);
$selectFBR->where("t1.TBQAirInvenAPISysId = ?", $value['TBQAirInvenSysId']);
$resultFbr = $this->db->fetchAll($selectFBR);
$selectFBRULE = $this->db->select();
$selectFBRULE->from(array('t1' => 'TB_IC_Trans_API_Air_Fare'), $arrFareRule);
$selectFBRULE->where("t1.TBOAirInvenAPISysId = ?", $value['TBQAirInvenSysId']);
$resultFbrule = $this->db->fetchRow($selectFBRULE);
$arrTRX = array('SUM(tb2.NetAmount) AS TrxNetAmount', 'SUM(tb2.PaidAmount) AS TrxPaidAmount');
$selectTRX = $this->db->select();
$selectTRX->from(array('t1' => 'TB_Agency_Customer_Trx'), ['TPSysId']);
$selectTRX->joinInner(array('tb2' => "TB_Trx"), "t1.TrxSysId = tb2.TrxSysId", $arrTRX);
$selectTRX->where("t1.TPSysId = ?", $value['TPSysId']);
$selectTRX->where("t1.TrxStatus = ?", 1);
$selectTRX->where("t1.IsActive = ?", 1);
$selectTRX->where("tb2.TrxStatus = ?", 1);
$selectTRX->where("tb2.IsActive = ?", 1);
$selectTRX->group(array("t1.TPSysId"));
$resultTRX = $this->db->fetchRow($selectTRX);
$TRX = [];
if ($resultTRX) {
$TRX = [
'TrxNetAmount' => ($resultTRX['TrxNetAmount'] / count($result)),
'TrxPaidAmount' => ($resultTRX['TrxPaidAmount'] / count($result)),
];
}
$dataSet[$key]['resultTRX'] = $TRX;
$dataSet[$key]['FairRules'] = $resultFbrule;
$dataSet[$key]['FareBreakdown'] = $resultFbr;
$dataSet[$key]['Segments'] = $resultSeg;
} else {
$dataSet[$key]['resultTRX'] = [];
$dataSet[$key]['FairRules'] = [];
$dataSet[$key]['FareBreakdown'] = [];
$dataSet[$key]['Segments'] = [];
}
}
}
return $dataSet;
}
public function getTBTRXRow($intTPSysId, $AgencySysId)
{
$selectTRX = $this->db->select();
$selectTRX->from(array('t1' => 'TB_Agency_Customer_Trx'), ['*']);
$selectTRX->where("t1.AgencySysId = ?", $AgencySysId);
$selectTRX->where("t1.TPSysId = ?", $intTPSysId);
$selectTRX->where("t1.TrxStatus = ?", 1);
$selectTRX->where("t1.IsActive = ?", 1);
$resultTRX = $this->db->fetchRow($selectTRX);
return $resultTRX;
}
public function customerInvoiceDataByCron($intTPSysId, $versionId = NULL, $AgencySysId = NULL)
{
// $sql = " SELECT * FROM TB_IC_Trans_API_Air";
// $result = $this->db->query($sql)->fetch();
// echo '<pre>';
// print_r($result);
// die('dd');
$arrCustomerTravelPlan = array('AgentSysId', 'StatusType', 'TPSysId', 'MasterTPSysId', 'CustomerSysId', 'RoomInfoJson', 'ChangeRequestId');
$arrCustomerTravelPlanItenary = array('TPIntSysId');
$arrCustomerTravelPlanAir = array(
'VersionId',
'FlightNumber',
'Cost',
'TotalCost',
'GTXMarkup',
'AgencyCommission',
'AgencyMarkUp',
'AgentServiceTaxAmount',
'MarkUp',
'AgentsCustomServiceTaxAmount',
'XServiceTaxAmount',
'GTXServiceTaxAmount',
'Taxes',
'SourceAirportCode',
'DestAirportCode',
'XRefBookingId',
'VersionId as AirVersionId',
'APIBookingRes',
'TripType',
'ExtraMarkup',
'ExtraMarkupOnGST'
);
$arrAirLine = array('Code AS AirLineCode', 'Title as AirLineTitle');
$arrCustomerTrx = array('TrxDate AS bookingDate', 'XRefTrxNo');
$arrAgency = array('AgencySysId', 'Title AS CompanyName', 'PrimaryContactName', 'PrimaryEmail', 'PrimaryContactNo', 'PrimaryMobileNo', 'Logo', 'countrycode');
$arrAgencyAddress = array('Address as CompanyAddress', 'Pincode');
$arrGC = array('Title AS CityTitle', 'Country');
$arrCurrency = array('Symbol AS CurrencySymbol', 'Title as CurrencyTitle');
$select = $this->db->select();
$select->from(array('TP' => "TB_Agency_Customer_TravelPlan"), $arrCustomerTravelPlan);
$select->joinLeft(array('TPI' => "TB_Agency_Customer_TravelPlan_Itenary"), "TPI.TPSysId = TP.TPSysId", $arrCustomerTravelPlanItenary);
$select->joinLeft(array('TPA' => "TB_Agency_Customer_TravelPlan_Air"), "TPA.TPIntSysId = TPI.TPIntSysId", $arrCustomerTravelPlanAir);
$select->joinleft(array('ITAA' => "TB_IC_Trans_API_Air"), "ITAA.TBQAirInvenSysId = TPA.InvnItemSysId", array('TBQAirInvenSysId', 'TripType', 'JourneyType', 'IsInternational', 'FromUTCTime', 'ToUTCTime', 'IsRefundable', 'ICSourceSysId'));
$select->joinLeft(array('TMA' => "TB_Master_Airline"), "TMA.AirlineSysId = TPA.AirlineSysId", $arrAirLine);
$select->joinLeft(array('TRX' => "TB_Agency_Customer_Trx"), "TRX.TPSysId = TP.TPSysId AND TRX.TrxStatus = 1", $arrCustomerTrx);
$select->joinLeft(array('WTRX' => "TB_Agency_Wallet_Trx"), "WTRX.XRefTPSysId = TP.TPSysId AND WTRX.IsApproved = 1", array("InvoiceId"));
$select->joinLeft(array('AG' => "TB_Agency"), "AG.AgencySysId = TP.AgencySysId", $arrAgency);
$select->joinLeft(array('AGA' => "TB_Agency_Addresses"), "AGA.AgencySysId = AG.AgencySysId", $arrAgencyAddress);
$select->joinLeft(array('GC' => "TB_Master_Geo_City"), "GC.CityId = AG.CitySysId", $arrGC);
$select->joinLeft(array('CU' => "TB_Master_Currency"), "CU.CurrencyType = ITAA.CurrencyType", $arrCurrency);
$select->joinLeft(array('TAC' => "TB_Agency_Customer"), "TP.CustomerSysId = TAC.CustomerSysId", array('CustomerSysId'));
$select->joinLeft(array('TAU' => "TB_Agency_User"), "TAU.UserSysId = TAC.CreatedByUserSysId", array('FirstName as agentName', 'EmailId as AgentEmailId', 'UserPicPath'));
$select->joinLeft(array('TAUU' => "TB_Agency"), "TAUU.AgencySysId = TAC.AgencySysId", array('Title as AgentAgencyName'));
$select->joinLeft(array('TACL' => "TB_Agency_CRM_LeadAccount"), "TACL.AgencyCRMLeadSysId = TAC.AgencyCRMLeadSysId", array("B2BType"));
// if (!empty($AgencySysId)) {
// $select->where("TP.AgencySysId = ?", $AgencySysId);
// } else if (!empty($this->intLoggedinUserAgencySysId)) {
// $select->where("TP.AgencySysId = ?", $this->intLoggedinUserAgencySysId);
// }
$select->where("TP.PlanType = ?", 1);
//$select->where("TRX.IsActive = ?", 1);
$select->where('TP.TPSysId = ?', $intTPSysId);
if (!empty($versionId)) {
$select->where('TPA.VersionId = ?', $versionId);
}
//echo $select->where('TPA.InvnItemSysId IN (28533,28573)');
//echo $select;
// exit;
// if($intTPSysId == 295194) {
// echo $select;exit;
// }
$result = $this->db->fetchAll($select);
return $result;
}
public function travellerListBySabir($intTPSysId, $AgencySysId = NULL)
{
$arrCustomerTravelPlan = array('TPPaxSysId', 'TicketId', 'TPSysId', 'paxType', 'TicketNumber', 'TicketFullJson', 'ChangeRequestId', 'ChangeRequestRes', 'ChangeRequestStatus', 'CustomerSysId', 'FirstName', 'LastName');
$select = $this->db->select();
$select->from(array('TP' => "TB_Agency_Customer_TravelPlan_Pax"), $arrCustomerTravelPlan);
$select->where("TP.IsActive = ?", 1);
$select->where('TP.TPSysId = ?', $intTPSysId);
$result = $this->db->fetchAll($select);
$data = [];
if ($result) {
foreach ($result as $key => $value) {
$data[$value['TicketId']] = $value;
}
}
return $data;
}
public function travellerListForProposal($intTPSysId, $AgencySysId = NULL)
{
$arrCustomerTravelPlan = array('TPPaxSysId', 'TicketId', 'TPSysId', 'TicketNumber', 'TicketFullJson', 'ChangeRequestId', 'ChangeRequestRes', 'ChangeRequestStatus', 'CustomerSysId', 'FirstName', 'LastName');
$select = $this->db->select();
$select->from(array('TP' => "TB_Agency_Customer_TravelPlan_Pax"), $arrCustomerTravelPlan);
$select->where("TP.IsActive = ?", 1);
$select->where('TP.TPSysId = ?', $intTPSysId);
$result = $this->db->fetchAll($select);
$data = [];
if ($result) {
foreach ($result as $key => $value) {
$data[$key] = $value;
}
}
return $data;
}
public function travellerListForCancel($intTPSysId, $IsReturn = NULL)
{
$arrCustomerTravelPlan = array('TPPaxSysId', 'TicketId', 'TPSysId', 'TicketNumber', 'TicketFullJson', 'ChangeRequestId', 'ChangeRequestRes', 'ChangeRequestStatus');
$select = $this->db->select();
$select->from(array('TP' => "TB_Agency_Customer_TravelPlan_Pax"), $arrCustomerTravelPlan);
if ($IsReturn && ($IsReturn == 0 || $IsReturn == 1)) {
$select->where("TP.IsReturn = ?", $IsReturn);
}
$select->where("TP.IsActive = ?", 1);
$select->where('TP.TPSysId = ?', $intTPSysId);
$result = $this->db->fetchAll($select);
return $result;
}
public function flightFairDetailsForEmail($VersionId, $AgencySysId)
{
$arrCustomerTravelPlan = array('AgentSysId', 'StatusType', 'TPSysId', 'CustomerSysId', 'RoomInfoJson');
$arrCustomerTravelPlanItenary = array('TPIntSysId');
$arrCustomerTravelPlanAir = array(
'VersionId',
'FlightNumber',
'Cost',
'TotalCost',
'GTXMarkup',
'AgencyCommission',
'AgencyMarkUp',
'AgentServiceTaxAmount',
'MarkUp',
'AgentsCustomServiceTaxAmount',
'XServiceTaxAmount',
'GTXServiceTaxAmount',
'Taxes',
'SourceAirportCode',
'DestAirportCode',
'XRefBookingId',
'APIBookingRes'
);
$arrAirLine = array('Code AS AirLineCode', 'Title as AirLineTitle');
$arrCustomerTrx = array('TrxDate AS bookingDate');
$arrAgency = array('AgencySysId', 'Title AS CompanyName', 'PrimaryContactName', 'PrimaryEmail', 'PrimaryContactNo', 'PrimaryMobileNo', 'Logo');
$arrAgencyAddress = array('Address as CompanyAddress', 'Pincode');
$arrGC = array('Title AS CityTitle', 'Country');
$select = $this->db->select();
$select->from(array('TP' => "TB_Agency_Customer_TravelPlan"), $arrCustomerTravelPlan);
$select->joinLeft(array('TPI' => "TB_Agency_Customer_TravelPlan_Itenary"), "TPI.TPSysId = TP.TPSysId", $arrCustomerTravelPlanItenary);
$select->joinLeft(array('TPA' => "TB_Agency_Customer_TravelPlan_Air"), "TPA.TPIntSysId = TPI.TPIntSysId", $arrCustomerTravelPlanAir);
$select->joinleft(array('ITAA' => "TB_IC_Trans_API_Air"), "ITAA.TBQAirInvenSysId = TPA.InvnItemSysId", array('TBQAirInvenSysId', 'IsInternational', 'FromUTCTime', 'ToUTCTime',));
$select->joinLeft(array('TMA' => "TB_Master_Airline"), "TMA.AirlineSysId = TPA.AirlineSysId", $arrAirLine);
$select->joinLeft(array('TRX' => "TB_Agency_Customer_Trx"), "TRX.TPSysId = TP.TPSysId", $arrCustomerTrx);
$select->joinLeft(array('WTRX' => "TB_Agency_Wallet_Trx"), "WTRX.XRefTPSysId = TP.TPSysId", array("InvoiceId"));
$select->joinLeft(array('AG' => "TB_Agency"), "AG.AgencySysId = TP.AgencySysId", $arrAgency);
$select->joinLeft(array('AGA' => "TB_Agency_Addresses"), "AGA.AgencySysId = AG.AgencySysId", $arrAgencyAddress);
$select->joinLeft(array('GC' => "TB_Master_Geo_City"), "GC.CityId = AG.CitySysId", $arrGC);
if (!empty($AgencySysId)) {
$select->where("TP.AgencySysId = ?", $AgencySysId);
}
$select->where("TP.PlanType = ?", 1);
$select->where('TPA.VersionId = ?', $VersionId);
$select->limit(1);
$result = $this->db->fetchAll($select);
return $result;
}
// For Flight Booking Details....
// Added By Pardeep Panchal Ends Here ...
public function getFlightSearchListForProposal($flightdetails)
{
$sql = "SELECT t1.AirlineSysId,t1.IsDirect,t1.FlyingMinutes,t1.SourceAirportCode,t1.JourneyType, t1.DestAirportCode,t1.CurrencyType,t1.PublishedFare,
t1.CommissionEarned,t1.ApiResultIndex, t1.apiTraceId, t1.TBQAirInvenSysId,t1.SourcePlaceSysId,t1.DestPlaceSysId,t1.FromUTCTime
As DepartureTime,t1.ToUTCTime As ArrivalTime,t1.FlyingMinutes as FlightDuration, t3.Code As AirlineCode,t1.FlightNumber, t1.SourceAirportCode
as OriginAirportCode,t1.DestAirportCode as DestinationAirportCode,t3.Title as AirlineName, t3.Title as AirLineName,t4.Title as SourcePlaceName,t5.Title
as DestPlaceName FROM TB_IC_Trans_API_Air t1
LEFT JOIN TB_Master_Airline t3 ON t1.AirlineSysId = t3.AirlineSysId LEFT JOIN TB_Master_Geo_City t4 ON t1.SourcePlaceSysId = t4.CityId
LEFT JOIN TB_Master_Geo_City t5 ON t1.DestPlaceSysId = t5.CityId WHERE t1.TBQAirInvenSysId IN ($flightdetails)
ORDER BY t1.PublishedFare,t1.TBQAirInvenSysId DESC";
/* $sql = "SELECT t1.AirlineSysId,t1.IsDirect,t1.FlyingMinutes,t1.SourceAirportCode,t1.JourneyType,
t1.DestAirportCode,t1.CurrencyType,t1.PublishedFare,t1.CommissionEarned,t1.ApiResultIndex,
t1.apiTraceId, t2.TBQAirInvenSysId,t2.SourcePlaceSysId,t2.DestPlaceSysId,t2.StayDuration,t1.LocalFromTime As ArrivalTime,t1.LocalToTime As DepartureTime,t1.FlyingMinutes as FlightDuration,t2.AirlineCode,t1.FlightNumber,t2.Sequance,
t1.SourceAirportCode as OriginAirportCode,t1.DestAirportCode as DestinationAirportCode,t3.Title as AirlineName,
t3.Title as AirLineName,t4.Title as SourcePlaceName,t5.Title as DestPlaceName FROM TB_IC_Trans_API_Air t1
LEFT JOIN TB_IC_Trans_API_Air_Stop t2 ON t2.TBQAirInvenSysId = t1.TBQAirInvenSysId
LEFT JOIN TB_Master_Airline t3 ON t1.AirlineSysId = t3.AirlineSysId LEFT JOIN TB_Master_Geo_City t4 ON t1.SourcePlaceSysId = t4.CityId
LEFT JOIN TB_Master_Geo_City t5 ON t1.DestPlaceSysId = t5.CityId WHERE t1.TBQAirInvenSysId IN ($flightdetails) ORDER BY t2.PublishedFare,t1.TBQAirInvenSysId DESC";
$sql = "SELECT t1.AirlineSysId,t1.IsDirect,t1.FlyingMinutes,t1.SourceAirportCode,t1.JourneyType,
t1.DestAirportCode,t1.CurrencyType,t2.PublishedFare,t1.CommissionEarned,t1.ApiResultIndex,t1.apiTraceId,
t2.AirInvenSysId,t2.SourcePlaceSysId,t2.DestPlaceSysId,t2.StayDuration,t2.ArrivalTime,t2.DepartureTime,
t2.FlightDuration,t2.AirlineCode,t2.FlightNumber,t2.Sequance,t2.OriginAirportCode,t2.DestinationAirportCode,t2.AirlineName
FROM TB_IC_Trans_Air t1
INNER JOIN TB_IC_Trans_Air_Stop t2 ON t2.AirInvenSysId = t1.AirInvenSysId WHERE t1.AirInvenSysId IN ($flightdetails) AND t2.DestinationAirportCode <> '' AND t2.IsFlightChange = '0' ORDER BY t2.PublishedFare,t1.AirInvenSysId DESC"; */
//echo $sql; exit;
$result = $this->db->query($sql)->fetchAll();
return $result;
}
public function getAirInvenSysDetails($AirInvenSysId)
{
$sql = " SELECT AirInvenSysId,FlightNumber,AirlineSysId FROM TB_IC_Trans_Air WHERE 1=1 AND IsActive='1' ";
if (!empty($AirInvenSysId)) {
$sql .= " AND AirInvenSysId = " . $AirInvenSysId;
}
return $this->db->query($sql)->fetch();
}
public function getApiResultIndexDetails()
{
$sql = " SELECT apiTraceId,ApiResultIndex,IsLCC FROM TB_IC_Trans_API_Air WHERE 1=1 AND IsActive='1' ";
if (!empty($this->strCondition)) {
$sql .= $this->strCondition;
}
$sql .= " ORDER BY ApiResultIndex DESC";
return $this->db->query($sql)->fetchAll();
}
public function getSuppliersList($cityId, $intLoggedinUserId)
{
$sql = " SELECT distinct(TB_IC_Supplier.SupplierSysId), TB_IC_Supplier.SupplierName FROM TB_IC_Supplier "
. " RIGHT JOIN TB_MP_Inventory_Air ON TB_MP_Inventory_Air.SupplierSysId=TB_IC_Supplier.SupplierSysId WHERE 1 =1 AND TB_IC_Supplier.IsDelete='0' ";
if (!empty($cityId)) {
$sql .= " AND TB_IC_Supplier.CitySysId = " . $cityId;
}
if (!empty($intLoggedinUserId)) {
$sql .= " AND TB_IC_Supplier.AgentSysId = " . $intLoggedinUserId;
}
$sql .= " AND TB_IC_Supplier.Services LIKE '__1%' ";
$sql .= " ORDER BY TB_IC_Supplier.SupplierName ";
//echo $sql; exit;
return $this->db->query($sql)->fetchAll();
}
public function getFlightTotalSeat($intLoggedinAgencyId)
{
$sql = " SELECT SUM(PurchasedQty) AS total_seat, SUM(SoldQty) AS sold_seat FROM TB_MP_Inventory_Air
WHERE 1 =1 AND IsMarkForDelete='0' ";
if (!empty($intLoggedinAgencyId)) {
$sql .= " AND AgencySysId = " . $intLoggedinAgencyId;
}
//echo $sql; exit;
return $this->db->query($sql)->fetch();
}
public function getFlightTotalCost($intLoggedinAgencyId)
{
$sql = " SELECT SUM(TMIA.PurchasedQty * TMIAA.NetPrice) AS total_cost FROM TB_MP_Inventory_Air AS TMIA
LEFT OUTER JOIN TB_MP_Inventory_Air_Alloc AS TMIAA ON TMIA.InvnItemSysId=TMIAA.InvnItemSysId
WHERE 1 =1 AND IsMarkForDelete='0' ";
if (!empty($intLoggedinAgencyId)) {
$sql .= " AND TMIA.AgencySysId = " . $intLoggedinAgencyId;
}
$sql .= " AND TMIAA.MPType = '1' AND TMIA.IsItParent!='1' ";
//echo $sql; exit;
return $this->db->query($sql)->fetch();
}
public function getFlightTotalSoldCost($intLoggedinAgencyId)
{
$sql = " SELECT SUM(TMIA.SoldQty * TMIAA.NetPrice) AS total_sold_cost FROM TB_MP_Inventory_Air AS TMIA
LEFT OUTER JOIN TB_MP_Inventory_Air_Alloc AS TMIAA ON TMIA.InvnItemSysId=TMIAA.InvnItemSysId
WHERE 1 =1 AND IsMarkForDelete='0' ";
if (!empty($intLoggedinAgencyId)) {
$sql .= " AND TMIA.AgencySysId = " . $intLoggedinAgencyId;
}
$sql .= " AND TMIAA.MPType = '1' AND TMIA.IsItParent!='1' AND TMIA.SoldQty!='0' ";
//echo $sql; exit;
return $this->db->query($sql)->fetch();
}
public function getFlightStandardTotalCost($intLoggedinAgencyId)
{
$sql = " SELECT TMIA.InvnItemSysId, TMIA.TotalCost, TMIA.PurchasedQty, TMIA.CostCurrency, TMIA.TrxCurrency, TMSI.StdMarkUpPer, TMSI.TaxPer, TMSI.DiscountVal, TMSI.DiscountPer
FROM TB_MP_Inventory_Air AS TMIA
LEFT JOIN TB_MP_StdMarkup_Air AS TMSI ON TMIA.AgencySysId=TMSI.AgencySysId
WHERE 1=1 AND IsMarkForDelete='0' AND TMIA.IsItParent!='1' AND TMIA.InvnItemSysId NOT IN
(SELECT InvnItemSysId FROM TB_MP_Inventory_Air_Alloc AS TMIAA
WHERE TMIA.AgencySysId='" . $intLoggedinAgencyId . "' AND TMIAA.MPType = '1') ";
if (!empty($intLoggedinAgencyId)) {
$sql .= " AND TMSI.MPType = '1' AND TMSI.ItemSourceType = '0' AND TMIA.AgencySysId = " . $intLoggedinAgencyId;
}
//echo $sql; exit;
$resData = $this->db->query($sql)->fetchAll();
$total_st_cost = 0;
foreach ($resData as $arrFlightInventory) {
//Cost Conversion
if ($arrFlightInventory['CostCurrency'] == 2 && $arrFlightInventory['TrxCurrency'] == 1) {
$objFlight = new Travel_Model_TblFlight();
$currencyDetails = $objFlight->getCurrencyConversionPrice($arrFlightInventory['CostCurrency'], $arrFlightInventory['TrxCurrency']);
//echo "<pre>";print_r($currencyDetails); die;
$currencyPaddingDetails = $objFlight->getCurrencyConversionPaddingPrice($arrFlightInventory['TrxCurrency']);
$TotalCost = ($currencyDetails['Rate'] * $arrFlightInventory['TotalCost']);
$TotalConversionCost = ($currencyDetails['Rate'] * $arrFlightInventory['TotalCost']);
if ($currencyPaddingDetails['IsInPercentage'] == 1) {
$TotalPaddingCost = ($TotalConversionCost * $currencyPaddingDetails['Value'] / 100);
$TotalCost = $TotalConversionCost + $TotalPaddingCost;
} else {
$TotalCost = $currencyPaddingDetails['Value'] + $TotalPaddingCost;
}
} else {
$TotalCost = $arrFlightInventory['TotalCost'];
}
if ($arrFlightInventory['TotalCost'] && $arrFlightInventory['StdMarkUpPer'] && $arrFlightInventory['TaxPer']) {
$totalPrice = $TotalCost + $arrFlightInventory['StdMarkUpPer'];
$taxPrice = (($totalPrice * $arrFlightInventory['TaxPer']) / 100);
$totalCoseMarkupAndtaxPrice = $totalPrice + $taxPrice;
if ($arrFlightInventory['DiscountPer'] != 0) {
$discountPrice = (($totalCoseMarkupAndtaxPrice * $arrFlightInventory['DiscountPer']) / 100);
} else {
$discountPrice = 0;
}
$totalNetPrice = $totalCoseMarkupAndtaxPrice - $discountPrice;;
}
$total_st_price = ($arrFlightInventory['PurchasedQty'] * @$totalNetPrice);
$total_st_cost = $total_st_cost + $total_st_price;
}
return $total_st_cost;
}
public function getFlightStandardTotalSoldCost($intLoggedinAgencyId)
{
$sql = " SELECT TMIA.InvnItemSysId, TMIA.TotalCost, TMIA.SoldQty, TMIA.CostCurrency, TMIA.TrxCurrency, TMSI.StdMarkUpPer, TMSI.TaxPer, TMSI.DiscountVal, TMSI.DiscountPer
FROM TB_MP_Inventory_Air AS TMIA
LEFT JOIN TB_MP_StdMarkup_Air AS TMSI ON TMIA.AgencySysId=TMSI.AgencySysId
WHERE 1=1 AND IsMarkForDelete='0' AND TMIA.SoldQty!='0' AND TMIA.IsItParent!='1' AND TMIA.InvnItemSysId NOT IN
(SELECT InvnItemSysId FROM TB_MP_Inventory_Air_Alloc AS TMIAA
WHERE TMIA.AgencySysId='" . $intLoggedinAgencyId . "' AND TMIAA.MPType = '1') ";
if (!empty($intLoggedinAgencyId)) {
$sql .= " AND TMSI.MPType = '1' AND TMIA.AgencySysId = " . $intLoggedinAgencyId;
}
//echo $sql; exit;
$resData = $this->db->query($sql)->fetchAll();
$total_st_cost = 0;
foreach ($resData as $arrFlightInventory) {
//Cost Conversion
if ($arrFlightInventory['CostCurrency'] == 2 && $arrFlightInventory['TrxCurrency'] == 1) {
$objFlight = new Travel_Model_TblFlight();
$currencyDetails = $objFlight->getCurrencyConversionPrice($arrFlightInventory['CostCurrency'], $arrFlightInventory['TrxCurrency']);
//echo "<pre>";print_r($currencyDetails); die;
$currencyPaddingDetails = $objFlight->getCurrencyConversionPaddingPrice($arrFlightInventory['TrxCurrency']);
$TotalCost = ($currencyDetails['Rate'] * $arrFlightInventory['TotalCost']);
$TotalConversionCost = ($currencyDetails['Rate'] * $arrFlightInventory['TotalCost']);
if ($currencyPaddingDetails['IsInPercentage'] == 1) {
$TotalPaddingCost = ($TotalConversionCost * $currencyPaddingDetails['Value'] / 100);
$TotalCost = $TotalConversionCost + $TotalPaddingCost;
} else {
$TotalCost = $currencyPaddingDetails['Value'] + $TotalPaddingCost;
}
} else {
$TotalCost = $arrFlightInventory['TotalCost'];
}
if ($arrFlightInventory['TotalCost'] && $arrFlightInventory['StdMarkUpPer'] && $arrFlightInventory['TaxPer']) {
$totalPrice = $TotalCost + $arrFlightInventory['StdMarkUpPer'];
$taxPrice = (($totalPrice * $arrFlightInventory['TaxPer']) / 100);
$totalCoseMarkupAndtaxPrice = $totalPrice + $taxPrice;
if ($arrFlightInventory['DiscountPer'] != 0) {
$discountPrice = (($totalCoseMarkupAndtaxPrice * $arrFlightInventory['DiscountPer']) / 100);
} else {
$discountPrice = 0;
}
$totalNetPrice = $totalCoseMarkupAndtaxPrice - $discountPrice;;
}
$total_st_price = ($arrFlightInventory['SoldQty'] * $totalNetPrice);
$total_st_cost = $total_st_cost + $total_st_price;
}
return $total_st_cost;
}
public function addStdMarkupFlight($data = array())
{
try {
$select = $this->db->select()
->from("TB_MP_StdMarkup_Air", array('count(*) AS totalRecords'))
->where("ItemSourceType =?", 0)
->where("AgencySysId =?", $data['AgencySysId'])
->where("MpType =?", $data['MPType']);
$resultData = $this->db->fetchOne($select);
// echo $resultData; die;
if ($resultData == 1) {
$where['AgencySysId = ?'] = $data['AgencySysId'];
$where['MpType = ?'] = $data['MPType'];
return $this->db->update('TB_MP_StdMarkup_Air', $data, $where);
} else {
$this->db->insert('TB_MP_StdMarkup_Air', $data);
return $this->db->lastInsertId('TB_MP_StdMarkup_Air');
}
} catch (Exception $e) {
die('There has been an error. ' . $e->getMessage());
}
}
public function getCityList($strCityName)
{
$sql = "Select CityId, Title From TB_Master_Geo_City where 1 = 1 AND IsActive='1' ";
if (!empty($strCityName)) {
$sql .= " AND Title LIKE '$strCityName%' ";
}
$sql .= " ORDER BY Title";
//echo $sql; die;
$rowset = $this->db->query($sql)->fetchAll();
if (count($rowset) > 0) {
foreach ($rowset as $row) {
$response[] = array('CityId' => $row['CityId'], 'value' => $row['Title'], 'label' => $row['Title']);
}
} else {
$response[] = array('CityId' => 0);
}
return $response;
}
public function getAgencyMarkupSettingDetails($intLoggedinUserAgencySysId, $maketPlaceId)
{
$agencyMarkup = array('AgencySysId', 'MPType', 'Currency', 'MarkUpType', 'StdMarkUpPer', 'IsSameForAll', 'TaxPer', 'IsActive');
$select = $this->db->select();
$select->from(array('tbl' => "TB_MP_StdMarkup_Air"), $agencyMarkup);
$select->where("tbl.ItemSourceType = ?", 0);
$select->where("tbl.AgencySysId = ?", $intLoggedinUserAgencySysId);
$select->where("tbl.MPType = ?", $maketPlaceId);
//echo $select; die;
$result = $this->db->fetchRow($select);
return $result;
}
public function getTravelPlanAirData($TPSysId, $VersionId = null)
{
$TravelPlanAir = array('VersionId', 'XRefPNR', 'SourceAirportCode', 'DestAirportCode', 'TripType');
$select = $this->db->select();
$select->from(array('tb1' => "TB_Agency_Customer_TravelPlan_Air"), $TravelPlanAir);
$select->joinLeft(array('tb2' => "TB_Agency_Customer_TravelPlan_Itenary"), "tb2.TPIntSysId = tb1.TPIntSysId", []);
if (!empty($VersionId)) {
$select->where("tb1.VersionId = ?", $VersionId);
}
$select->where("tb2.TPSysId = ?", $TPSysId);
$result = $this->db->fetchAll($select);
return $result;
}
public function getAllTPIntSysIdAirData($TPSysId)
{
$TravelPlanAir = array('TPIntSysId');
$select = $this->db->select();
$select->from(array('tb1' => "TB_Agency_Customer_TravelPlan_Itenary"), $TravelPlanAir);
$select->where("tb1.TPSysId = ?", $TPSysId);
$result = $this->db->fetchAll($select);
$data = [];
if ($result) {
foreach ($result as $key => $value) {
$data[] = $value['TPIntSysId'];
}
}
return $data;
}
public function insertFlightTempData($data)
{
$this->db->insert('Flight_TempData', $data);
return $this->db->lastInsertId('Flight_TempData');
}
public function insertFlightModification($data)
{
$this->db->insert('TB_Flight_Modification', $data);
return $this->db->lastInsertId('TB_Flight_Modification');
}
public function updateModification($data, $ModificationId)
{
$where = array('ModificationId = ? ' => $ModificationId);
$this->db->update('TB_Flight_Modification', $data, $where);
}
public function updateModificationChangeRequestId($data, $ChangeRequestId)
{
$where = array('ChangeRequestId = ? ' => $ChangeRequestId);
$this->db->update('TB_Flight_Modification', $data, $where);
}
public function CreditNoteModificationData($ChangeRequestId)
{
$select = $this->db->select();
$select->from(array('TP' => "TB_Flight_Modification"));
$select->where("TP.ChangeRequestId = ?", $ChangeRequestId);
$select->where('TP.status IN(?)', array(19, 83));
$result = $this->db->fetchRow($select);
return $result;
}
public function ModificationData($TPSysId, $BookingId = null, $ChangeRequestId = null)
{
$select = $this->db->select();
$select->from(array('TP' => "TB_Flight_Modification"));
if (!empty($ChangeRequestId)) {
$select->where("TP.ChangeRequestId = ?", $ChangeRequestId);
}
if (!empty($ChangeRequestId)) {
$select->where("TP.BookingId = ?", $BookingId);
}
$select->where("TP.TPSysId = ?", $TPSysId);
// $select->where('TP.status IN(?)', array(19, 83));
$result = $this->db->fetchAll($select);
return $result;
}
public function GeModificationData($ChangeRequestId, $All = null)
{
$select = $this->db->select();
$select->from(array('TP' => "TB_Flight_Modification"));
if (!empty($ChangeRequestId)) {
$select->where("TP.ChangeRequestId = ?", $ChangeRequestId);
}
if (!empty($All)) {
$select->where("TP.status = ?", 0);
// $select->where("TP.AgencySysId = ?", 6134);
// $select->where("TP.BookingId = ?", 'TJ104122871928');
}
//$select->order("ModificationId DESC");
$result = $this->db->fetchAll($select);
return $result;
}
public function GeModificationDataRequest($ChangeRequestId, $All = null)
{
$select = $this->db->select();
$select->from(array('TP' => "TB_Flight_Modification"));
$select->where("TP.ChangeRequestId = ?", $ChangeRequestId);
$result = $this->db->fetchAll($select);
$DataArray = [];
if ($result) {
foreach ($result as $key => $value) {
$RequestData = json_decode($value['RequestData'], true);
$DataArray[$key] = $value;
$DataArray[$key]['pax'] = $RequestData;
unset($DataArray[$key]['RequestData']);
}
}
return $DataArray;
}
public function GetFlightTempData($traceId)
{
$arr = array('TempId', 'APIBookingData', 'TPSysId');
$select = $this->db->select();
$select->from(array('TP' => "Flight_TempData"), $arr);
$select->where("TP.TraceId = ?", $traceId);
$select->order("TempId DESC");
$result = $this->db->fetchRow($select);
return $result;
}
public function GetFlightTempDataByTPSysId($TPSysId)
{
$arr = array('TempId', 'APIBookingData', 'TPSysId', 'TraceId');
$select = $this->db->select();
$select->from(array('TP' => "Flight_TempData"), $arr);
$select->where("TP.TPSysId = ?", $TPSysId);
$select->order("TempId DESC");
$result = $this->db->fetchRow($select);
return $result;
}
public function GetFlightTempDataAll($TPSysId)
{
$arr = array('APIBookingData', 'TPSysId', 'TraceId');
$select = $this->db->select();
$select->from(array('TP' => "Flight_TempData"), $arr);
if (!empty($TPSysId)) {
$select->where("TP.TPSysId = ?", $TPSysId);
}
$result = $this->db->fetchAll($select);
return $result;
}
public function deleteFlightTempData($TraceId)
{
$where = array('TraceId = ? ' => $TraceId);
$this->db->delete('Flight_TempData', $where);
}
public function deleteToken($AgencySysId, $apiType)
{
$where = array('AgencySysId = ? ' => $AgencySysId, 'apiType = ? ' => $apiType);
$this->db->delete('tbl_token', $where);
}
public function DeleteTravelPlanItenary($TPSysId)
{
$where = array('TPSysId = ? ' => $TPSysId);
$this->db->delete('TB_Agency_Customer_TravelPlan_Itenary', $where);
}
public function GetGeoCountry()
{
$arrGeoCountry = array('Title as CountryTitle', 'Code as CountryCode');
$select = $this->db->select();
$select->from(array('tbl' => "TB_Master_Geo_Country"), $arrGeoCountry);
$select->where("tbl.IsApprove = ?", 1);
$select->where("tbl.IsMarkForDel = ?", 0);
$select->order("tbl.Title ASC");
$result = $this->db->fetchAll($select);
return $result;
}
public function getIsAPIProdFlight($AgencySysId)
{
$select = $this->db->select()
->from(array("t1" => "TB_Agency"), array("IsAPIProd"))
->where("t1.AgencySysId=?", $AgencySysId)
->where("t1.IsActive=?", 1)
->where("t1.IsApproved=?", 1)
->where("t1.IsMarkForDelete=?", 0)
->where("t1.IsVarified=?", 1);
$result = $this->db->fetchRow($select);
$IsAPIProd = isset($result['IsAPIProd']) ? $result['IsAPIProd'] : 0;
return $IsAPIProd;
}
public function getFlightBookingListByBookingids($biikingIds, $ReportingToSysIds = null)
{
// ini_set('display_errors', 1);
// ini_set('display_startup_errors', 1);
// error_reporting(E_ALL);
$arrCustomerTravelPlan = array('AgencySysId', 'AgentSysId', 'StatusType', 'TPSysId', 'MasterTPSysId', 'CurrencyType', 'CustomerSysId', 'SourcePlaces', 'DestinationPlaces', 'PlanBookingId', 'VersionId', 'LeadSourceSysId');
$arrCustomerTravelPlanItenary = array('TPIntSysId');
$arrCustomerTravelPlanAir = array(
'FlightNumber',
'TotalCost',
'Cost',
'Discount',
'GTXMarkup',
'AgencyCommission',
'AgencyMarkUp',
'AgentServiceTaxAmount',
'SourceAirportCode',
'DestAirportCode',
'FromUTCTime',
'VersionId as AirVersionId',
'ToUTCTime',
'XRefBookingId',
'LocalToTime as returnDate',
'AgentsCustomServiceTaxAmount',
'MarkUp',
'APIBookingRes',
'TripType',
'XRefPNR',
'AirlineRefPNR',
'APIBookingId',
'IsBookingStatus'
);
$arrCustomerTrx = array('TrxDate AS bookingDate');
$arrAgency = array('FirstName AS agtFirstName', 'LastName AS agtLastName');
$arrCustomer = array('FirstName AS custFirstName', 'LastName as custLastName', 'EmailId as custEmailId', 'Contacts as custContacts');
$arrAirLine = array('Code AS AirLineCode', 'Title as AirLineTitle');
$LeadSource = array('Title AS LeadSource');
$CustomerTravelPlanPax = array('FirstName', 'LastName');
$select = $this->db->select();
$select->from(array('TP' => "TB_Agency_Customer_TravelPlan"), $arrCustomerTravelPlan);
$select->joinInner(array('TPI' => "TB_Agency_Customer_TravelPlan_Itenary"), "TPI.TPSysId = TP.TPSysId", $arrCustomerTravelPlanItenary);
$select->joinInner(array('TPA' => "TB_Agency_Customer_TravelPlan_Air"), "TPA.TPIntSysId = TPI.TPIntSysId", $arrCustomerTravelPlanAir);
$select->joinleft(array('ITAA' => "TB_IC_Trans_API_Air"), "ITAA.TBQAirInvenSysId = TPA.InvnItemSysId", array('TBQAirInvenSysId', 'IsNewMigration', 'IsInternational', 'IsQuickProposal', 'ICSourceSysId'));
$select->joinLeft(array('TRPX' => "TB_Agency_Customer_TravelPlan_Pax"), "TRPX.TPSysId = TP.TPSysId", $CustomerTravelPlanPax);
$select->joinInner(array('TRX' => "TB_Agency_Customer_Trx"), "TRX.TPSysId = TP.TPSysId", $arrCustomerTrx);
$select->join(array('AG' => "TB_Agency_User"), "AG.UserSysId = TP.AgentSysId", $arrAgency);
$select->joinleft(array('AGCY' => "TB_Agency"), "AGCY.AgencySysId = TP.AgencySysId", ['DisplayName AS AgencyName']);
$select->joinLeft(array('AC' => "TB_Agency_Customer"), "AC.CustomerSysId = TP.CustomerSysId", $arrCustomer);
$select->joinLeft(array('TMA' => 'TB_Master_Airline'), 'TMA.AirlineSysId = ITAA.AirlineSysId ', $arrAirLine);
$select->joinLeft(array('LDS' => 'TB_Master_LeadSource'), 'LDS.LeadSourceSysId = TP.LeadSourceSysId ', $LeadSource);
$select->joinleft(array('AGCY1' => "TB_Agency"), "AGCY1.AgencySysId = AC.AgencySysId", ['Title AS B2bAgencyName']);
$select->joinleft(array('ABMTS' => "TB_Master_TravelPlan_Status"), "ABMTS.TPStatusSysId = TP.StatusType", ['TPStatus']);
if (!empty($this->intLoggedinUserAgencySysId)) {
// $select->where("TP.AgencySysId = ?", $this->intLoggedinUserAgencySysId);
}
if ($ReportingToSysIds) {
$select->where("TP.AgentSysId IN (" . $ReportingToSysIds . ")");
}
$select->where("TPA.XRefPNR = ?", 1);
$select->where("TP.PlanType = ?", 1);
$select->where("TPA.IsBookingStatus = ?", 1);
$select->where('TP.StatusType IN(?)', array(17, 10, 9, 18, 19, 71, 72, 70, 123, 13, 104));
if (isset($biikingIds) && !empty($biikingIds)) {
$biikingIdsstr = implode(',', $biikingIds);
$select->where('TPA.XRefBookingId IN (' . $biikingIdsstr . ')');
$select->orWhere('TPA.APIBookingId IN (' . $biikingIdsstr . ')');
$select->order('TP.TPSysId DESC');
$result = $this->db->fetchAll($select);
return $result;
} else {
if (!empty($this->searchArr) && isset($this->searchArr)) {
$searchArr = $this->searchArr;
if (!empty($searchArr['dateFrom']) && !empty($searchArr['dateTo']) && trim($searchArr['dateFrom']) != '__-__-____' && trim($searchArr['dateTo']) != '__-__-____') {
$dateFrom = (isset($searchArr['dateFrom']) && !empty($searchArr['dateFrom'])) ? date("Y-m-d", strtotime($searchArr['dateFrom'])) : '';
$dateTo = (isset($searchArr['dateTo']) && !empty($searchArr['dateTo'])) ? date("Y-m-d", strtotime($searchArr['dateTo'])) : '';
if ($dateFrom != '1970-01-01' && $dateTo != '1970-01-01') {
$select->where("CAST(TPA.CreateDate AS DATE) BETWEEN '$dateFrom' AND '$dateTo'");
}
}
if (!empty($searchArr['TravelToDate']) && !empty($searchArr['TravelFromDate']) && trim($searchArr['TravelFromDate']) != '__-__-____' && trim($searchArr['TravelToDate']) != '__-__-____') {
$TravelFromDate = (isset($searchArr['TravelFromDate']) && !empty($searchArr['TravelFromDate'])) ? date("Y-m-d", strtotime($searchArr['TravelFromDate'])) : '';
$TravelToDate = (isset($searchArr['TravelToDate']) && !empty($searchArr['TravelToDate'])) ? date("Y-m-d", strtotime($searchArr['TravelToDate'])) : '';
if ($TravelFromDate != '1970-01-01' && $TravelToDate != '1970-01-01') {
$select->where("CONVERT(date,TPA.FromUTCTime) >= '$TravelFromDate' AND CONVERT(date,TPA.FromUTCTime) <= '$TravelToDate'");
}
}
$ProposalId = trim($searchArr['ProposalId']);
$ProposalId_filt = explode("/", $ProposalId);
$Flight = trim($searchArr['Flight']);
$BookingId = trim($searchArr['BookingId']);
$orgDest = trim($searchArr['orgDest']);
$CustomerName = trim($searchArr['CustomerName']);
$CustomerEmail = trim($searchArr['CustomerEmail']);
$CustomerPhone = trim($searchArr['CustomerPhone']);
$B2bagencyAgentId = trim($searchArr['hidden_b2bagency_agentId']);
$source = trim($searchArr['source']);
$pnrnumber = trim($searchArr['pnrnumber']);
$agencyUserFilter = (isset($searchArr['agencyUserFilter'])) ? (int) $searchArr['agencyUserFilter'] : 0;
if (!empty($ProposalId) && isset($ProposalId)) {
if (isset($ProposalId_filt[2])) {
$select->where('TP.TPSysId LIKE ?', $ProposalId_filt[2]);
} else {
$select->where('TP.TPSysId LIKE ?', $ProposalId);
}
}
if (!empty($Flight) && isset($Flight)) {
$whereConditions = " TPA.FlightNumber = '" . $Flight . "' OR TMA.Title like '%" . $Flight . "%'";
$select->where($whereConditions);
}
if (!empty($BookingId) && isset($BookingId)) {
$select->where('TPA.XRefBookingId LIKE ?', "%$BookingId%");
}
if (!empty($pnrnumber) && isset($pnrnumber)) {
$select->where('TPA.AirlineRefPNR LIKE ?', "%$pnrnumber%");
}
if (!empty($orgDest) && isset($orgDest)) {
$whereCondition_fordes = " TP.SourcePlaces = '" . $orgDest . "' OR TP.DestinationPlaces like '%" . $orgDest . "%'";
$select->where($whereCondition_fordes);
}
if (!empty($CustomerName) && isset($CustomerName)) {
$select->where('AC.FirstName LIKE ?', "%$CustomerName%");
}
if (!empty($CustomerEmail) && isset($CustomerEmail)) {
$select->where('AC.EmailId = ?', $CustomerEmail);
}
if (!empty($CustomerPhone) && isset($CustomerPhone)) {
$select->where('AC.Contacts = ?', $CustomerPhone);
}
if (!empty($B2bagencyAgentId) && isset($B2bagencyAgentId)) {
$select->where('TP.B2BAgencySysId = ?', $B2bagencyAgentId);
}
if (isset($source) && $source != '') {
$select->where('TP.IsB2BProposal = ?', $source);
}
if ($agencyUserFilter > 0) {
$select->where('TPQ.AgentSysId = ?', $agencyUserFilter);
}
}
$select->where('TPA.ICSourceSysId = 8');
$select->where("TP.AgencySysId = ?", $this->intLoggedinUserAgencySysId);
$select->order('TP.TPSysId DESC');
return $select;
}
}
public function getFlightBookingCustomerListByBookingids($biikingIds, $ReportingToSysIds = null, $notcancelled = 0, $pnrnumber = null, $phone = null, $name = null, $email = null)
{
$arrCustomerTravelPlan = array('AgencySysId', 'AgentSysId', 'StatusType', 'TPSysId', 'MasterTPSysId', 'CurrencyType', 'CustomerSysId', 'SourcePlaces', 'DestinationPlaces', 'PlanBookingId', 'VersionId', 'LeadSourceSysId');
$arrCustomerTravelPlanItenary = array('TPIntSysId');
$arrCustomerTravelPlanAir = array(
'AirlineSysId',
'FlightNumber',
'TotalCost',
'Cost',
'Discount',
'GTXMarkup',
'AgencyCommission',
'AgencyMarkUp',
'AgentServiceTaxAmount',
'SourceAirportCode',
'DestAirportCode',
'FromUTCTime',
'VersionId as AirVersionId',
'ToUTCTime',
'XRefBookingId',
'LocalToTime as returnDate',
'AgentsCustomServiceTaxAmount',
'MarkUp',
'APIBookingRes',
'TripType',
'XRefPNR',
'AirlineRefPNR',
'IsBookingStatus'
);
$arrCustomerTrx = array('TrxDate AS bookingDate');
$arrAgency = array('FirstName AS agtFirstName', 'LastName AS agtLastName');
$arrCustomer = array('Title as custTitle', 'FirstName AS custFirstName', 'LastName as custLastName', 'EmailId as custEmailId', 'Contacts as custContacts', 'paxType as mpaxType');
$arrAirLine = array('Code AS AirLineCode', 'Title as AirLineTitle');
$LeadSource = array('Title AS LeadSource');
$agenyCustomerTravelPlan_Air = array('CustomerSysId as PaxCustomerSysId', 'MemberSysId', 'IsLeadPax', 'TPSysId', 'paxType', 'ChangeRequestStatus', 'TicketFullJson', 'VersionId as PaxVersionId', 'Title', 'FirstName', 'LastName', 'DOB', 'passportno', 'isWheelChair');
$select = $this->db->select();
$select->from(array('TP' => "TB_Agency_Customer_TravelPlan"), $arrCustomerTravelPlan);
$select->joinInner(array('TPI' => "TB_Agency_Customer_TravelPlan_Itenary"), "TPI.TPSysId = TP.TPSysId", $arrCustomerTravelPlanItenary);
$select->joinInner(array('TPA' => "TB_Agency_Customer_TravelPlan_Air"), "TPA.TPIntSysId = TPI.TPIntSysId", $arrCustomerTravelPlanAir);
$select->joinleft(array('ITAA' => "TB_IC_Trans_API_Air"), "ITAA.TBQAirInvenSysId = TPA.InvnItemSysId", array('TBQAirInvenSysId', 'IsNewMigration', 'IsInternational', 'IsQuickProposal', 'ICSourceSysId'));
$select->joinInner(array('TRX' => "TB_Agency_Customer_Trx"), "TRX.TPSysId = TP.TPSysId", $arrCustomerTrx);
$select->joinLeft(array('AC' => "TB_Agency_Customer"), "AC.CustomerSysId = TP.CustomerSysId", $arrCustomer);
$select->joinleft(array('AGCY1' => "TB_Agency"), "AGCY1.AgencySysId = AC.AgencySysId", ['Title AS B2bAgencyName']);
$select->joinleft(array('AGCTP' => "TB_Agency_Customer_TravelPlan_Pax"), "AGCTP.TPSysId = TP.TPSysId", $agenyCustomerTravelPlan_Air);
$select->joinLeft(array('TMA' => "TB_Master_Airline"), "TMA.AirlineSysId = TPA.AirlineSysId", $arrAirLine);
if (!empty($this->intLoggedinUserAgencySysId)) {
// $select->where("TP.AgencySysId = ?", $this->intLoggedinUserAgencySysId);
}
if ($ReportingToSysIds) {
$select->where("TP.AgentSysId IN (" . $ReportingToSysIds . ")");
}
$select->where("TPA.XRefPNR = ?", 1);
$select->where("TP.PlanType = ?", 1);
$select->where("TPA.IsBookingStatus = ?", 1);
$select->where('TP.StatusType IN(?)', array(17, 10, 9, 18, 19, 71, 72, 70, 123, 13, 104));
if (isset($biikingIds) && !empty($biikingIds)) {
$biikingIdsstr = implode(',', $biikingIds);
$select->where('TPA.XRefBookingId IN (' . $biikingIdsstr . ')');
$select->orWhere('TPA.APIBookingId IN (' . $biikingIdsstr . ')');
} else {
$select->where('TPA.ICSourceSysId = 8');
$select->where("TP.AgencySysId = ?", $this->intLoggedinUserAgencySysId);
if ($pnrnumber) {
$select->where('TPA.AirlineRefPNR LIKE ?', "%$pnrnumber%");
}
if ($name) {
$select->where('AC.FirstName LIKE ?', "%$name%");
}
if ($email) {
$select->where('AC.EmailId = ?', $email);
}
if ($phone) {
$select->where('AC.Contacts = ?', $phone);
}
}
if ($notcancelled == 1) {
$select->where("AGCTP.ChangeRequestStatus != '1' or AGCTP.ChangeRequestStatus is null");
}
$select->order('TP.TPSysId DESC');
$select->order('AGCTP.TPPaxSysId ASC');
$arrResultPax = $this->db->fetchAll($select);
return $arrResultPax;
}
public function CheckFlightToken($tablename, $AgencySysId, $apiType = null)
{
$date1 = date('Y-m-d') . " 00:00:00";
$date2 = date('Y-m-d') . " 23:59:00";
$select = $this->db->select()->from("$tablename", "*");
if ($apiType) {
$select->where('apiType = ?', $apiType);
} else {
$select->where('apiType = ?', 'TBO');
}
$select->where('AgencySysId = ?', $AgencySysId);
$select->where('created_at >= ?', $date1);
$select->where('created_at <= ?', $date2);
$result = $this->db->fetchRow($select);
return $result;
}
public function InsertToken($data)
{
$this->db->insert('tbl_token', $data);
return $this->db->lastInsertId();
}
public function GetFlightProposalByTPSysId($TPSysId)
{
$travel = array('TPSysId', 'StatusType', 'MasterTPSysId', 'PlanType', 'PlanBookingId', 'AgencySysId', 'CustomerSysId', 'CreateDate', 'AdditionalReq', 'RoomInfoJson', 'UpdateDate', 'CurrencyType', 'Cities', 'SupplierSysId', 'ProposalEmailTo', 'AgentSysId', 'hasFollowUp', 'hasIteration', 'IsGTXNetwork', 'IsB2BProposal');
$travel_itenary = array('TPIntSysId as itenaryID', 'TPSysId');
$travel_air = array('TPIntSysId', 'AdultPax', 'Cost', 'TotalCost', 'LocalFromTime as Departuredate', 'LocalToTime as returnDate', 'FlightCabinClass', 'ChildPax', 'InfentPax', 'SourceAirportCode', 'DestAirportCode', 'XServiceTaxAmount', 'MarkUp', 'AgencyMarkUp', 'GTXMarkup', 'XServiceTaxAmount', 'GTXServiceTaxAmount', 'AgentServiceTaxAmount', 'AgentsCustomServiceTaxAmount', 'AgencyCommission', 'ICSourceSysId');
$airport_code = array('AirportCode', 'AirportTitle');
$customer_details = array('CRMCustSysId', 'EmailId');
$Trans_API_Air = array('IsQuickProposal');
$select = $this->db->select();
$select->from(array('tbl' => "TB_Agency_Customer_TravelPlan"), $travel);
$select->joinLeft(array('tb2' => "TB_Agency_Customer_TravelPlan_Itenary"), "tbl.TPSysId = tb2.TPSysId", $travel_itenary);
$select->joinLeft(array('tb3' => "TB_Agency_Customer_TravelPlan_Air"), "tb2.TPIntSysId = tb3.TPIntSysId", $travel_air);
$select->joinLeft(array('tb4' => "TB_Agency_Customer"), "tbl.CustomerSysId = tb4.CustomerSysId", $customer_details);
$select->joinLeft(array('tb5' => "TB_Agency_User"), "tb5.UserSysId = tb4.CreatedByUserSysId", array(new Zend_Db_Expr('tb5.FirstName as agentName'), 'Designation', 'EmailId as AgentEmailId', 'AgencyName as AgentAgencyName', 'AgencySysId as B2BAgencySysId', 'UserSysId'));
$select->joinLeft(array('tb6' => "TB_IC_Trans_API_Air"), "tb3.InvnItemSysId = tb6.TBQAirInvenSysId", $Trans_API_Air);
$select->joinLeft(array('tb7' => "TB_Agency"), "tbl.AgencySysId = tb7.AgencySysId", array('PrimaryEmail as AgencyEmailId', 'Title', 'DisplayName', 'IsB2bSiteurl', 'Url', 'IsSSL'));
$select->where('tbl.TPSysId = ?', $TPSysId);
$select->where("tbl.IsActive = ?", 1);
$select->where("tb3.IsActive = ?", 1);
$select->where("tb4.IsActive = ?", 1);
$select->where("tbl.IsMarkForDel = ?", 0);
$select->where("tb3.IsMarkForDel = ?", 0);
$select->where("tb4.IsMarkForDelete = ?", 0);
$result = $this->db->fetchAll($select);
return $result;
}
public function updatePNR($data, $VersionId)
{
$where = array('VersionId = ? ' => $VersionId);
$update = $this->db->update('TB_Agency_Customer_TravelPlan_Air', $data, $where);
return $update;
}
public function updateQueryB2B($data, $VersionId)
{
if ($VersionId) {
foreach ($VersionId as $ids) {
$where = array('VersionId = ? ' => $ids);
$update = $this->db->update('TB_Agency_Customer_TravelPlan_Air', $data, $where);
}
}
return $update;
}
public function checkExistBooking($bookingId)
{
$select = $this->db->select();
$select->from(array('TP' => "TB_Agency_Customer_TravelPlan_Air"));
$select->where("TP.XRefBookingId = ?", $bookingId);
$result = $this->db->fetchRow($select);
return $result;
}
public function runQueryAllData($select)
{
$result = $this->db->fetchAll($select);
return $result;
}
public function getFlightBookingAPIBookingResByBookingids($biikingIds, $ReportingToSysIds = null)
{
$arrCustomerTravelPlan = array('TPSysId', 'MasterTPSysId');
$arrCustomerTravelPlanItenary = array('TPIntSysId');
$arrCustomerTravelPlanAir = array('APIBookingRes');
$select = $this->db->select();
$select->from(array('TP' => "TB_Agency_Customer_TravelPlan"), $arrCustomerTravelPlan);
$select->joinInner(array('TPI' => "TB_Agency_Customer_TravelPlan_Itenary"), "TPI.TPSysId = TP.TPSysId", $arrCustomerTravelPlanItenary);
$select->joinInner(array('TPA' => "TB_Agency_Customer_TravelPlan_Air"), "TPA.TPIntSysId = TPI.TPIntSysId", $arrCustomerTravelPlanAir);
if (!empty($this->intLoggedinUserAgencySysId)) {
$select->where("TP.AgencySysId = ?", $this->intLoggedinUserAgencySysId);
}
if ($ReportingToSysIds) {
$select->where("TP.AgentSysId IN (" . $ReportingToSysIds . ")");
}
$select->where("TPA.XRefPNR = ?", 1);
$select->where("TP.PlanType = ?", 1);
$select->where("TPA.IsBookingStatus = ?", 1);
$select->where('TP.StatusType IN(?)', array(17, 10, 9, 18, 19, 71, 72, 123));
if (isset($biikingIds) && !empty($biikingIds)) {
$biikingIdsstr = implode(',', $biikingIds);
$select->where('TPA.XRefBookingId IN (' . $biikingIdsstr . ')');
}
$select->order('TP.TPSysId DESC');
// echo $select;die;
$result = $this->db->fetchAll($select);
return $result;
}
public function getAgencyDataById($AgencySysId)
{
$select = $this->db->select()
->from(array("TBA" => "TB_Agency_User"), array('IsAllowRandomCodeAccess', 'IsB2BAgent', 'AgencySysId', 'UserSysId', 'FirstName', 'LastName', 'UserRole', 'ContactNo1', 'IsInsurance', 'EmailId', 'IsActive', 'IsApproved', 'IsMarkForDel', 'UserPicPath'))
->where("TBA.AgencySysId =?", $AgencySysId);
$result = $this->db->fetchRow($select);
return $result;
}
public function getBooingOTP($intTPSysId)
{
$arrCustomerTravelPlan = array('AgentSysId', 'CustomerRemark');
$select = $this->db->select();
$select->from(array('TP' => "TB_Agency_Customer_TravelPlan"), $arrCustomerTravelPlan);
if (!empty($this->intLoggedinUserAgencySysId)) {
$select->where("TP.AgencySysId = ?", $this->intLoggedinUserAgencySysId);
}
$select->where('TP.TPSysId = ?', $intTPSysId);
$result = $this->db->fetchRow($select);
if ($result) {
return trim($result['CustomerRemark']);
} else {
return 0;
}
}
public function checkActiveAPI($AgencySysId, $APISourceSysId)
{
$select = $this->db->select()
->from("TB_Agency_API")
->where("AgencySysId =?", $AgencySysId)
->where("APISourceSysId =?", $APISourceSysId)
->where("IsmarkForDel =?", 0)
->where("IsActive =?", 1);
$result = $this->db->fetchRow($select);
return $result;
}
public function getTravelPlanOnlyPrice($MasterTPSysId)
{
$arrCustomerTravelPlan = array('TPSysId', 'Price', 'NetPrice', 'AgencyMarkUp', 'AgentServiceTaxAmount', 'MarkUp', 'AgentsCustomServiceTaxAmount', 'GTXMarkup', 'GTXServiceTaxAmount', 'TotalCost');
$select = $this->db->select();
$select->from(array('TP' => "TB_Agency_Customer_TravelPlan"), $arrCustomerTravelPlan);
if (!empty($this->intLoggedinUserAgencySysId)) {
$select->where("TP.AgencySysId = ?", $this->intLoggedinUserAgencySysId);
}
$select->where("TP.PlanType = ?", 1);
$select->where('TP.TPSysId = ?', $MasterTPSysId);
$result = $this->db->fetchRow($select);
return $result;
}
public function GetCustomersById($CustomerSysId, $AgencySysId)
{
$result = '';
$arrCity = array("Title as cityTitle");
$select = $this->db->select();
$select->from(array('tb1' => "TB_Agency_Customer"));
$select->joinLeft(array('tb2' => "TB_Master_Geo_City"), "tb1.CitySysId = tb2.CityId", $arrCity);
$select->where('tb1.CustomerSysId = ?', $CustomerSysId);
$select->where("tb1.IsMarkForDelete = ?", 0);
$select->where("tb1.AgencySysId = ?", $AgencySysId);
//echo $select; exit;
$result = $this->db->fetchRow($select);
return $result;
}
public function GetCustomerMembersById($memberId, $agencySysID, $CustomerSysId)
{
$arrCity = array("Title as cityTitle");
$arrState = array("Title as stateTitle");
$countryArray = array('Title as CountryTitle');
$select = $this->db->select();
$select->from('TB_Agency_Customer_Members');
$select->joinLeft(array('tb2' => "TB_Master_Geo_City"), "tb2.CityId = TB_Agency_Customer_Members.CitySysId", $arrCity);
$select->joinLeft(array('tb3' => "TB_Master_Geo_State"), "tb3.StateId = TB_Agency_Customer_Members.StateOrZoneSysId", $arrState);
$select->joinLeft(array('tb4' => "TB_Master_Geo_Country"), "tb4.ContId = TB_Agency_Customer_Members.CountrySysId", $countryArray);
$select->where("TB_Agency_Customer_Members.CustomerSysId = ?", $CustomerSysId);
$select->where('TB_Agency_Customer_Members.AgencySysId = ?', $agencySysID);
$memberId ? $select->where('TB_Agency_Customer_Members.MemberSysId = ?', $memberId) : '';
$select->where('TB_Agency_Customer_Members.IsActive = ?', 1);
$select->where('TB_Agency_Customer_Members.Relation != ?', 0);
$select->where('TB_Agency_Customer_Members.IsMarkForDelete = ?', 0);
$select->order("UpdateDate DESC");
//echo $select;exit;
$select->limit(100);
$result = $this->db->fetchRow($select);
return $result;
}
public function getSeriesFaredefaultMarkup($getMasterAgencySysId)
{
$select = $this->db->select();
$select->from('TB_Agency_Series_Fare_Markup', array("MarkUpType", "B2CMarkUp", "B2BMarkUp", "InfantMarkUp"));
$select->where('IsActive = ?', 1);
$select->where('AgencySysId = ?', $getMasterAgencySysId);
$select->where('IsMarkForDel = ?', 0);
$result = $this->db->fetchRow($select);
return $result;
}
public function checkNonLccBooking($VersionId)
{
$select = $this->db->select();
$select->from(array('TP' => "TB_Agency_Customer_TravelPlan_Air"), array('VersionId', 'IsBookingStatus', 'APIBookingRes'));
$select->where("TP.VersionId = ?", $VersionId);
$result = $this->db->fetchRow($select);
return $result;
}
public function gststatecodelist()
{
$select = $this->db->select();
$select->from(array('TP' => "TB_Master_Geo_State"), array('GSTNo as tin'));
$select->where("TP.IsApprove = ?", 1);
$select->where("TP.IsActive = ?", 1);
$select->where("TP.IsMarkForDelete = ?", 0);
$result = $this->db->fetchAll($select);
return $result;
}
public function getCustomerTravelPlanPax($TPSysId)
{
$select = $this->db->select();
$select->from(array('TP' => "TB_Agency_Customer_TravelPlan_Pax"), array('TPPaxSysId', 'CustomerSysId', 'MemberSysId', 'VersionId'));
$select->where("TP.IsActive = ?", 1);
$select->where('TP.TPSysId = ?', $TPSysId);
$select->order(array('TP.TPPaxSysId ASC'));
$result = $this->db->fetchAll($select);
return $result;
}
public function SendBNPLReminderMail($TPSysId, $EmailIds)
{
$objFlight = new Travel_Model_TblFlight();
$objAgency = new Travel_Model_TblAgency();
$bookingDataByAPI = $objFlight->bookingDataByAPI($TPSysId, null);
$arrtravellerList = $objFlight->GetCustomerListForFlightBooking($TPSysId);
$IsB2bSiteurl = $bookingDataByAPI[0]['IsB2bSiteurl'];
$AgentEmailId = $bookingDataByAPI[0]['AgentEmailId'];
$AgencySysId = $bookingDataByAPI[0]['AgencySysId'];
$AgentSysId = $bookingDataByAPI[0]['AgentSysId'];
$DisplayName = $bookingDataByAPI[0]['DisplayName'];
$PrimaryEmail = $bookingDataByAPI[0]['PrimaryEmail'];
$AirlineRefPNR = [];
if ($bookingDataByAPI) {
foreach ($bookingDataByAPI as $k => $value) {
$AirlineRefPNR[] = $value['AirlineRefPNR'];
}
}
$AirlinePNR = implode(', ', array_values(array_unique($AirlineRefPNR)));
$html = new Zend_View();
$html->setScriptPath(APPLICATION_PATH . '/views/scripts/booking-flight/');
$html->assign(array('bookingDataByAPI' => $bookingDataByAPI, 'arrtravellerList' => $arrtravellerList));
$bodyText = $html->render('bnpl-reminder-mail.phtml');
$Subject = 'Payment Due for PNR Number ' . $AirlinePNR;
$emailData = array(
'fromEmail' => $PrimaryEmail,
'fromName' => $DisplayName,
'subject' => $Subject,
'to' => $EmailIds,
'bodyHtml' => $bodyText,
'bodyText' => ''
);
// echo "<pre>";
// print_r($IsB2bSiteurl);
// print_r($bodyText);
// die('ddd');
$arrEmailStatisticsType = array_keys(unserialize(ARR_EMAIL_STATISTICS_TYPE));
$arrEmailStatistics = array(
"TPSysId" => 0,
"TypeSysId" => 1, // 1 For Email 2 For SMS
"AgencySysId" => $AgencySysId,
"AgentSysId" => $AgentSysId,
"Title" => $bodyText,
"Source" => $arrEmailStatisticsType[1], // 1 For Vouchers
"Status" => 0,
"RefSysId" => "",
"RefSysStatus" => "",
"CreateDate" => date('Y-m-d H:i:s')
);
$emailResponse = json_decode(Catabatic_ValidateCustomer::mailSentByElastice($emailData, $arrEmailStatistics), true);
return $emailResponse;
}
}