INSERT IGNORE INTO meta.itineraryPlaces(`PNR`,`TktNumber`,`Timestamp`) 
SELECT PNR,TktNumber,`Timestamp`
FROM PNR_Trigger.PNR_Ticket_Details
WHERE IFNULL(TktNumber,'') <> '' AND IFNULL(TIMESTAMP,'') <> '' AND Customer = 'Viacom'
GROUP BY PNR,TktNumber
ORDER BY DATE(TIMESTAMP) DESC,PNR ASC,Dep_Date ASC,Dep_Time ASC;

---

INSERT IGNORE INTO meta.toShow SELECT PNR , 0 FROM PNR_Trigger.PNR_Ticket_Details where customer='viacom' group by pnr order by date(TimeStamp) desc,PNR asc,Dep_Date asc,Dep_Time asc;

INSERT IGNORE INTO meta.segmentDetails(`PNR`,`TktNumber`,`Coupon`,`Origin`,`Destination`,`Airline`,`CabinClass`,`RBD`,`DateOfDeparture`,`TimeOfArrival`)
SELECT PNR, TktNumber, IFNULL(CouponNum,'1'), Dep_Airport,Arr_Airport,Airline_Code, Cabin_Class, BIC, CONCAT(DATE(Dep_Date),' ', IFNULL(Start_Time,'')), IFNULL(Arr_Time, IFNULL(End_Time,''))
FROM PNR_Trigger.PNR_Ticket_Details
WHERE Customer='Viacom' AND PNR IN (SELECT PNR FROM meta.toShow WHERE toShow = 0)
ORDER BY PNR ASC,TktNumber ASC, CONCAT(Dep_Date, Start_Time) ASC;

-- INSERT IGNORE INTO meta.itineraryPlaces(`GDS_LCC`,`PNR`,`TktNumber`,`Fare_Construction`,`Timestamp`,`ClassOfBooking`) 
-- SELECT GDS_LCC,PNR, TktNumber, Fare_Construction, `Timestamp`,`Cabin_Class` FROM PNR_Trigger.PNR_Ticket_Details WHERE IFNULL(Fare_Construction,'') <> '' AND GDS_LCC = 'GDS' AND Customer='Viacom' AND CouponNum = '1' GROUP BY PNR,Pax_Name,TktNumber ORDER BY DATE(`Timestamp`) DESC, PNR ASC, TktNumber ASC;

UPDATE 
meta.segmentDetails MSD
INNER JOIN
(
SELECT PNR,StartAirp, EndAirp, STR_TO_DATE(ArrDate, '%m/%d/%Y') AS ArrivalDateTime
FROM 1a.segmentdetails
ORDER BY PNR, StartAirp,EndAirp
) A ON MSD.PNR = A.PNR AND MSD.Origin = A.StartAirp AND MSD.Destination = A.EndAirp
SET
MSD.TimeOfArrival = CONCAT(A.ArrivalDateTime," ",MSD.TimeOfArrival) ;


UPDATE meta.itineraryPlaces MIP INNER JOIN 
( SELECT PNR,TktNumber,CONCAT_WS(',',Origin, GROUP_CONCAT(Destination SEPARATOR ',')) AS SegmentOrderFromSD FROM meta.segmentDetails GROUP BY PNR, TktNumber ORDER BY PNR ASC, TktNumber ASC, Coupon ASC ) A
ON MIP.PNR = A.PNR AND MIP.TktNumber = A.TktNumber
SET MIP.segmentOrderFromSD = A.SegmentOrderFromSD;

UPDATE meta.itineraryPlaces IP INNER JOIN 
1a.connectionMap CMP ON IP.PNR = CMP.PNR and IP.TktNumber = CMP.TktNumber
SET 
IP.segmentOrder = CMP.SegmentOrder,
IP.Connections =  CMP.ConnectionMap;

UPDATE meta.itineraryPlaces MIP INNER JOIN 
( SELECT PNR, SUM(IF(UPPER(Int_Dom) <> 'DOMESTIC',1,0)) AS IntDom FROM PNR_Trigger.PNR_Ticket_Details WHERE Customer='Viacom' GROUP BY PNR  ) A
ON MIP.PNR = A.PNR 
SET MIP.Int_Dom = A.IntDom

UPDATE meta.itineraryPlaces MIP INNER JOIN 
( SELECT PNR, Cabin_Class AS IntDom FROM PNR_Trigger.PNR_Ticket_Details WHERE Customer='Viacom' GROUP BY PNR  ) A
ON MIP.PNR = A.PNR 
SET MIP.ClassOfBooking = A.IntDom