public List<SelectedFlights> FillListBoxSelectedFlightsFFMfromAMS(string Occupation, int IsDepOrArr)
{
List<SelectedFlights> messageList = new List<SelectedFlights>();
try
{
if (Occupation == "FFM")
command2.CommandText = "SELECT 'D' AS ArrDep, flt.Departure_Qualifier AS Qual, comp.[Iata Code] AS cIata, flt.[Departure_FFM NTS], comp.[Icao Code] AS cIcao, " +
"flt.[Departure Flight Identifier] AS fltNum, flt.[Departure_Flight identifier 2] " +
"AS fltNum2, LEFT(flt.[Departure Scheduled Time Id], 5) AS STO, flt.[Movement Date Id] " +
"AS SDO, LEFT(flt.[Departure_ESTIMATED TIME OF DEPARTURE_Time], 5) AS ATO, flt.[Departure_ESTIMATED TIME OF DEPARTURE_Date] " +
"AS ADO, ac.[Iata Code] AS acIata, ac.[Icao Code] AS acIcao, flt.[Departure_UPDATE FFM], " +
"flt.[Departure_SOGP Count Cargo], flt.[Departure_SOGP Count Mail], " +
"flt.[Departure_SOP Cargo Weight Departure], flt.[Departure_SOP Mail Departure], " +
"flt.[Departure_R2_STA], " +
"comp.[Coordinator email address], comp.[TYPE B ADDRESS1], comp.[Email Cargo Managers], amsdw.DimAircraft.registration, flt.[Movement Id], " +
"Left(ruta.Name,3) destination " +
"FROM amsdwView.FactMovement AS flt INNER JOIN amsdwView.DimAircraftType AS ac ON flt.[Aircraft Type Id] = ac.[Aircraft Type Id] INNER JOIN " +
"amsdwView.DimAirline AS comp ON flt.[Departure Airline Id] = comp.[Airline Id] INNER JOIN " +
"amsdw.DimAircraft ON flt.[Aircraft Type Id] = amsdw.DimAircraft.dimAircraftId INNER JOIN " +
"amsdwView.DimRoute ruta ON flt.[Departure Route Id] = ruta.[Route Id] " +
"WHERE (flt.[Departure Flight Identifier] IS NOT NULL) AND(flt.[Movement Is Deleted] <> 1) AND(flt.[Departure_FFM NTS] = 1) AND (flt.[Departure_UPDATE FFM] = 0) " +
" AND ((FLT.[Departure_FFM is SEND] is null) or (FLT.[Departure_FFM is SEND] = 0)) ORDER BY sto";
else if (Occupation == "FSULITE")
command2.CommandText = "SELECT 'D' AS ArrDep, flt.Departure_Qualifier AS Qual, comp.[Iata Code] AS cIata, flt.[Departure_FFM NTS], comp.[Icao Code] AS cIcao, " +
"flt.[Departure Flight Identifier] AS fltNum, flt.[Departure_Flight identifier 2] " +
"AS fltNum2, LEFT(flt.[Departure Scheduled Time Id], 5) AS STO, flt.[Movement Date Id] " +
"AS SDO, LEFT(flt.[Departure_ESTIMATED TIME OF DEPARTURE_Time], 5) AS ATO, flt.[Departure_ESTIMATED TIME OF DEPARTURE_Date] " +
"AS ADO, ac.[Iata Code] AS acIata, ac.[Icao Code] AS acIcao, flt.[Departure_UPDATE FFM], " +
"flt.[Departure_SOGP Count Cargo], flt.[Departure_SOGP Count Mail], " +
"flt.[Departure_SOP Cargo Weight Departure], flt.[Departure_SOP Mail Departure], " +
"flt.[Departure_R2_STA], " +
"comp.[Coordinator email address], comp.[TYPE B ADDRESS1], comp.[Email Cargo Managers], amsdw.DimAircraft.registration, flt.[Movement Id], " +
"Left(ruta.Name,3) destination " +
"FROM amsdwView.FactMovement AS flt INNER JOIN amsdwView.DimAircraftType AS ac ON flt.[Aircraft Type Id] = ac.[Aircraft Type Id] INNER JOIN " +
"amsdwView.DimAirline AS comp ON flt.[Departure Airline Id] = comp.[Airline Id] INNER JOIN " +
"amsdw.DimAircraft ON flt.[Aircraft Type Id] = amsdw.DimAircraft.dimAircraftId INNER JOIN " +
"amsdwView.DimRoute ruta ON flt.[Departure Route Id] = ruta.[Route Id] " +
"WHERE (flt.[Departure Flight Identifier] IS NOT NULL) AND(flt.[Movement Is Deleted] <> 1) AND (flt.[Departure_KC Lite FSU NTS] = 1) AND ([Departure_KC Lite FSU] = 0) " +
"ORDER BY sto";
else if (Occupation == "FSU14")
command2.CommandText = "SELECT 'D' AS ArrDep, flt.Departure_Qualifier AS Qual, comp.[Iata Code] AS cIata, flt.[Departure_FFM NTS], comp.[Icao Code] AS cIcao, " +
"flt.[Departure Flight Identifier] AS fltNum, flt.[Departure_Flight identifier 2] " +
"AS fltNum2, LEFT(flt.[Departure Scheduled Time Id], 5) AS STO, flt.[Movement Date Id] " +
"AS SDO, LEFT(flt.[Departure_ESTIMATED TIME OF DEPARTURE_Time], 5) AS ATO, flt.[Departure_ESTIMATED TIME OF DEPARTURE_Date] " +
"AS ADO, ac.[Iata Code] AS acIata, ac.[Icao Code] AS acIcao, flt.[Departure_UPDATE FFM], " +
"flt.[Departure_SOGP Count Cargo], flt.[Departure_SOGP Count Mail], " +
"flt.[Departure_SOP Cargo Weight Departure], flt.[Departure_SOP Mail Departure], " +
"flt.[Departure_R2_STA], " +
"comp.[Coordinator email address], comp.[TYPE B ADDRESS1], comp.[Email Cargo Managers], amsdw.DimAircraft.registration, flt.[Movement Id], " +
"Left(ruta.Name,3) destination " +
"FROM amsdwView.FactMovement AS flt INNER JOIN amsdwView.DimAircraftType AS ac ON flt.[Aircraft Type Id] = ac.[Aircraft Type Id] INNER JOIN " +
"amsdwView.DimAirline AS comp ON flt.[Departure Airline Id] = comp.[Airline Id] INNER JOIN " +
"amsdw.DimAircraft ON flt.[Aircraft Type Id] = amsdw.DimAircraft.dimAircraftId INNER JOIN " +
"amsdwView.DimRoute ruta ON flt.[Departure Route Id] = ruta.[Route Id] " +
"WHERE (flt.[Departure Flight Identifier] IS NOT NULL) AND(flt.[Movement Is Deleted] <> 1) AND (flt.[Departure_FSU NTS] = 1) AND ([Departure_FSU] = 0) " +
"ORDER BY sto";
connection2.Open();
using (SqlDataReader dr = command2.ExecuteReader(CommandBehavior.CloseConnection))
{
while (dr.Read())
{
SelectedFlights selFlight = new SelectedFlights();
if (IsDepOrArr == 1)
{
//selFlight.FlightATO = dr["ATO"].ToString().Equals("") ? dr["scheduledTime"].ToString() : dr["ACTUAL"].ToString();
selFlight.FlightId = dr["Movement Id"].ToString();
selFlight.Registration = dr["registration"].ToString();
selFlight.TypeBADDRESS = dr["TYPE B ADDRESS1"].ToString();
selFlight.Email = dr["Coordinator email address"].ToString();
selFlight.EmailCargo = dr["Email Cargo Managers"].ToString();
selFlight.FlightIdentifier = dr["fltNum"].ToString();
selFlight.Qual = dr["Qual"].ToString();
selFlight.CIata = dr["cIata"].ToString();
selFlight.CIcao = dr["cIcao"].ToString();
selFlight.AcIata = dr["acIata"].ToString();
selFlight.AcIcao = dr["acIcao"].ToString();
selFlight.FlightSTO = Convert.ToDateTime(dr["STO"].ToString());
selFlight.FlightSDO = Convert.ToDateTime(dr["SDO"].ToString());
selFlight.FlightATO = dr["ATO"].Equals(DBNull.Value) ? selFlight.FlightATO : Convert.ToDateTime(dr["ATO"].ToString());
selFlight.FlightADO = dr["ADO"].Equals(DBNull.Value) ? selFlight.FlightADO : Convert.ToDateTime(dr["ADO"].ToString());
selFlight.CountCargo = dr["Departure_SOGP Count Cargo"].Equals(System.DBNull.Value) ? 0: Convert.ToInt32(dr["Departure_SOGP Count Cargo"]);
selFlight.CountMail = dr["Departure_SOGP Count Mail"].Equals(System.DBNull.Value) ? 0 : Convert.ToInt32(dr["Departure_SOGP Count Mail"]);
selFlight.WeightCargo = dr["Departure_SOP Cargo Weight Departure"].Equals(System.DBNull.Value) ? 0 : Convert.ToInt32(dr["Departure_SOP Cargo Weight Departure"]);
selFlight.WeightMail = dr["Departure_SOP Mail Departure"].Equals(System.DBNull.Value) ? 0 : Convert.ToInt32(dr["Departure_SOP Mail Departure"]);
selFlight.Destination = dr["destination"].ToString();
selFlight.ArrTime = dr["Departure_R2_STA"].ToString();
//selFlight.Departure_FFM_NTS1 = Convert.ToBoolean(dr["Departure_UPDATE FFM"]);
//Sdhed = dataUshipKelu.Hour*100 + dataUshipKelu.Minute; //hhmm
//int Ushu = dr["R1ADT"].ToString().Equals("") ? Convert.ToInt32(dr["R1STD"]) : Convert.ToInt32(dr["R1ADT"]);
}
else command.CommandText = "UPDATE Daily_Values SET Daily_Values.value = '" + dr["arrivalFlightId"].ToString() + "' WHERE Field='auaraiu' and Daily_ID=";
messageList.Add(selFlight);
}
}
if (command2.CommandText.StartsWith("UPDATE"))
{
command2.Connection.Open();
command2.ExecuteNonQuery();
//messageList.Add(command.CommandText);
}
return messageList;
}
catch (Exception)
{
throw;
}
finally
{
if (connection2 != null)
{
connection2.Close();
}
}
}
public List<FFM> FillListBoxFFMfromAMS(SelectedFlights selF)
{
List<FFM> messageList = new List<FFM>();
try
{
command2.CommandText = "SELECT * FROM [AMSDatawarehouse].[amsdwView].[factmovement_TableAttribute_4] where Id = " + selF.FlightId;
connection2.Open();
using (SqlDataReader dr = command2.ExecuteReader(CommandBehavior.CloseConnection))
{
while (dr.Read())
{
FFM selFFM = new FFM();
//selFlight.FlightATO = dr["ATO"].ToString().Equals("") ? dr["scheduledTime"].ToString() : dr["ACTUAL"].ToString();
selFFM.FlightIdentifier = dr["Id"].ToString();
selFFM.Pieces = dr["Oryn"].ToString();
selFFM.Weight = dr["Weight"].ToString();
selFFM.Goods = dr["Remarks"].ToString();
selFFM.AwbSerialNumber = dr["AWB Number"].ToString();
selFFM.VolumeCode = dr["Measument type"].ToString();
selFFM.Volume = dr["Measument Value"].ToString();
selFFM.Origin = dr["Origin IATA"].ToString();
selFFM.Destination = dr["Destination IATA"].ToString();
selFFM.ShipmentDescriptionCode = dr["AWB Types"].ToString();
//selFlight.Departure_FFM_NTS1 = Convert.ToBoolean(dr["Departure_UPDATE FFM"]);
//Sdhed = dataUshipKelu.Hour*100 + dataUshipKelu.Minute; //hhmm
//int Ushu = dr["R1ADT"].ToString().Equals("") ? Convert.ToInt32(dr["R1STD"]) : Convert.ToInt32(dr["R1ADT"]);
messageList.Add(selFFM);
}
}
if (command2.CommandText.StartsWith("UPDATE"))
{
command2.Connection.Open();
command2.ExecuteNonQuery();
//messageList.Add(command.CommandText);
}
return messageList;
}
catch (Exception)
{
throw;
}
finally
{
if (connection2 != null)
{
connection2.Close();
}
}
}
private void button9_Click(object sender, EventArgs e)
{
SelectedFlights selF = new SelectedFlights();
selF = listBox1.SelectedItem as SelectedFlights;
List<FFM> ffmList = new List<FFM>();
ffmList = b.FillListBoxFFMfromAMS(selF);
//Толтырамыз
string text1, copyEmail, emailCC;
text1 = b.LoadFFMBlank("fsu_lite_blank_email");
emailCC = "";
copyEmail = "";
string[] split;
if (selF.Email.Contains(","))
{
split = selF.Email.Split(',');
for (int i = 1; i < split.Length; i++)
{
emailCC = emailCC + "," + split[i];
}
copyEmail = split[0];
emailCC = emailCC.Substring(1,emailCC.Length - 1);
}
else if (selF.Email.Equals("")) copyEmail = selF.Email;
else copyEmail = selF.Email;
text1 = text1.Replace("PS102", selF.FlightIdentifier);
text1 = text1.Replace("27MAY", selF.FlightSDO.ToString("ddMMM").ToUpper());
text1 = text1.Replace("1930", selF.FlightSTO.ToString("HHmm"));
text1 = text1.Replace("URGAU", selF.Registration);
text1 = text1.Replace("[DESTINATION]", selF.Destination);
text1 = text1.Replace("[CARGOCOUNT]", selF.CountCargo.ToString());
text1 = text1.Replace("[CARGOWEIGHT]", selF.WeightCargo.ToString());
text1 = text1.Replace("[MAILCOUNT]", selF.CountMail.ToString());
text1 = text1.Replace("[MAILWEIGHT]", selF.WeightMail.ToString());
string textFFM = "";
foreach (var itemf in ffmList) //555-92050641SVOTSE/T5K38MC0.53/COURIER CARGO/ XPS
{
textFFM = textFFM + itemf.AwbSerialNumber + itemf.Origin + itemf.Destination + "/T" + itemf.Pieces + "K" + itemf.Weight + "MC" + itemf.Volume + "/" + itemf.Goods + Environment.NewLine;
if (itemf.ShipmentDescriptionCode.Trim().Length > 0)
{
textFFM = textFFM + itemf.ShipmentDescriptionCode + Environment.NewLine;
}
}
text1 = text1.Replace("[text]", textFFM);
text1 = text1 + "LAST";
b.WriteMyLOG("SEND Flight FSULITE: Start send email");
//Жібереміз Почтамен
SendMail("57.31.17.138", "*****@*****.**", "B#7313e", copyEmail, emailCC, "FSU LITE TSE", text1);
}
private void button7_Click(object sender, EventArgs e)
{
SelectedFlights selF = new SelectedFlights();
selF = listBox1.SelectedItem as SelectedFlights;
List<FFM> ffmList = new List<FFM>();
ffmList = b.FillListBoxFFMfromAMS(selF);
//Толтырамыз
//string text1 = "=PRIORITY\nQU\n=DESTINATION TYPE B\nSTX,TSEAPXH\n=ORIGIN\nTSEDBXH\n= TEXT\nFFM/8\n1/PS102/27MAY1930/KBP/URGAU\nTSE\nLAST";
//Толтырамыз
string text1 = "";
int sou = 0;
string textFFM = "";
foreach (var item in ffmList) //555-92050641SVOTSE/T5K38MC0.53/COURIER CARGO/ XPS
{
textFFM = "";
sou++;
text1 = "";
text1 = b.LoadFFMBlank("fsu_blank");
text1 = text1.Replace("TSEAPXH", selF.TypeBADDRESS);
text1 = text1.Replace("PS102", selF.FlightIdentifier);
text1 = text1.Replace("27MAY", selF.FlightSDO.ToString("ddMMM").ToUpper());
text1 = text1.Replace("[DESTINATION]", selF.Destination);
text1 = text1.Replace("[CARGOCOUNT]", selF.CountCargo.ToString());
text1 = text1.Replace("[CARGOWEIGHT]", selF.WeightCargo.ToString());
text1 = text1.Replace("[FSA89]", selF.TypeTimeIndicator.ToString());
text1 = text1.Replace("[TIME_DEP]", selF.DepTime.ToString());
text1 = text1.Replace("[TIME_ARR]", selF.ArrTime.ToString());
text1 = text1.Replace("[FSA72]", selF.DayChangeIndicator.ToString());
textFFM = textFFM + Environment.NewLine + item.AwbSerialNumber + "TSE" + item.Destination + "/T" + item.Pieces + "K" + item.Weight;
text1 = text1.Replace("[FSUAWB]", textFFM);
//Cақтаймыз
SaveFFM(text1, "FSU14-" + Convert.ToString(sou));
}
richTextBox1.Text = text1;
listBox2.DataSource = ffmList;
}
private void button5_Click(object sender, EventArgs e)
{
SelectedFlights item = new SelectedFlights();
item = listBox1.SelectedItem as SelectedFlights;
XNamespace am = "http://www.sita.aero/ams6-xml-api-messages";
XNamespace ad = "http://www.sita.aero/ams6-xml-api-datatypes";
XElement root = null;
root = new XElement(am + "Envelope",
new XAttribute(XNamespace.Xmlns + "amsx-messages", "http://www.sita.aero/ams6-xml-api-messages"),
new XAttribute("apiVersion", "1.2"),
new XAttribute(XNamespace.Xmlns + "amsx-datatypes", "http://www.sita.aero/ams6-xml-api-datatypes"),
new XElement(am + "Content",
new XElement(am + "FlightUpdateRequest",
new XElement(am + "FlightId",
new XElement(ad + "FlightKind", "Departure"),
new XElement(ad + "AirlineDesignator", item.CIata, new XAttribute("codeContext", "IATA")),
new XElement(ad + "FlightNumber", item.FlightNumber),
new XElement(ad + "ScheduledDate", item.FlightSDO.ToString("yyyy-MM-dd")),
new XElement(ad + "AirportCode", "TSE", new XAttribute("codeContext", "IATA"))
),
new XElement(am + "FlightUpdates",
//new XElement(am + "Update", "2015-11-13T12:25:00", new XAttribute("propertyName", "ScheduledTime")),
//new XElement(am + "Update", "FRU", new XAttribute("propertyName", "Route"), new XAttribute("codeContext", "IATA")),
//new XElement(am + "Update", "", new XAttribute("propertyName", "Arr_Codeshares"), new XAttribute("codeContext", "IATA")),
//new XElement(am + "Update", item.Pieces, new XAttribute("propertyName", "sogp_cargo_count_departure")),
new XElement(am + "Update", "True", new XAttribute("propertyName", "KC_LITE_FSU"))
)
)
));
comboBox1.Items.Add(root);
}
private void button4_Click_1(object sender, EventArgs e)
{
SelectedFlights selF = new SelectedFlights();
selF = listBox1.SelectedItem as SelectedFlights;
List<FFM> ffmList = new List<FFM>();
ffmList = b.FillListBoxFFMfromAMS(selF);
//Толтырамыз
//string text1 = "=PRIORITY\nQU\n=DESTINATION TYPE B\nSTX,TSEAPXH\n=ORIGIN\nTSEDBXH\n= TEXT\nFFM/8\n1/PS102/27MAY1930/KBP/URGAU\nTSE\nLAST";
string text1, copyEmail;
text1 = b.LoadFFMBlank("fsu_lite_blank");
text1 = text1.Replace("TSEAPXH", selF.TypeBADDRESS);
string[] split;
if (selF.Email.Contains(","))
{
copyEmail = "";
split = selF.Email.Split(',');
for (int i = 1; i < split.Length; i++)
{
copyEmail = Environment.NewLine + "INTERNET,EMAILXS," + split[i] + copyEmail;
}
copyEmail = copyEmail.Insert(0, split[0] + Environment.NewLine + "STX,CPYXXXX");
}
else if (selF.Email.Equals("")) copyEmail = "*****@*****.**";
else copyEmail = selF.Email;
text1 = text1.Replace("[EMAIL]", copyEmail);
text1 = text1.Replace("PS102", selF.FlightIdentifier);
text1 = text1.Replace("27MAY", selF.FlightSDO.ToString("ddMMM"));
text1 = text1.Replace("1930", selF.FlightSTO.ToString("HHmm"));
text1 = text1.Replace("KBP", selF.Destination);
text1 = text1.Replace("KBP", selF.Destination);
text1 = text1.Replace("URGAU", selF.Registration);
text1 = text1.Replace("[DESTINATION]", selF.Destination);
text1 = text1.Replace("[CARGOCOUNT]", selF.CountCargo.ToString());
text1 = text1.Replace("[CARGOWEIGHT]", selF.WeightCargo.ToString());
text1 = text1.Replace("[MAILCOUNT]", selF.CountMail.ToString());
text1 = text1.Replace("[MAILWEIGHT]", selF.WeightMail.ToString());
string textFFM = "";
foreach (var item in ffmList) //555-92050641SVOTSE/T5K38MC0.53/COURIER CARGO/ XPS
{
textFFM = textFFM + item.AwbSerialNumber + item.Destination + "TSE/T" + item.Pieces + "K" + item.Weight + "MC" + item.Volume + "/" + item.Goods + "/" + item.ShipmentDescriptionCode + "\n";
}
text1 = text1.Replace("[text]", textFFM);
text1 = text1 + "LAST";
richTextBox1.Text = text1;
listBox2.DataSource = ffmList;
}
