public FwbEntity AddOCIFromReader(FwbEntity fwbEntity, IDataReader reader)
{
while (reader.Read())
{
Fwb_newDB_OCIEntity Fwb_newDB_OCIEntity = new Fwb_newDB_OCIEntity(
reader["CountryCode"].ToString(),
reader["Infold"].ToString(),
reader["CustomsId"].ToString(),
reader["CustomsInfo"].ToString()
);
fwbEntity.colnewDBOCI.Add(Fwb_newDB_OCIEntity);
}
reader.Close();
reader.Dispose();
disConnect_dbcn_ExcuteReader();
return(fwbEntity);
}
public FwbEntity AddVolumeFromReader(FwbEntity fwbEntity, IDataReader reader)
{
while (reader.Read())
{
int pcs = 0; try { pcs = Convert.ToInt32(reader["pcs"]); }
catch { }
double pcsWeight = 0.00; try { pcsWeight = Convert.ToDouble(reader["pcsWeight"].ToString()); }
catch (Exception e)
{
}
double length = 0.00; try { length = Convert.ToDouble(reader["Lenght"].ToString()); }
catch (Exception e)
{
}
double width = 0.00; try { width = Convert.ToDouble(reader["width"].ToString()); }
catch (Exception e)
{
}
double height = 0.00; try { height = Convert.ToDouble(reader["height"].ToString()); }
catch (Exception e)
{
}
double volWeight = 0.00; try { volWeight = Convert.ToDouble(reader["volWeight"].ToString()); }
catch (Exception e)
{
}
FwbVolumeEntity fwbVolEntity = new FwbVolumeEntity(
pcsWeight,
width,
length,
height,
reader["unit"].ToString(),
pcs,
volWeight);
fwbEntity.colVol.Add(fwbVolEntity);
}
reader.Close();
reader.Dispose();
disConnect_dbcn_ExcuteReader();
return(fwbEntity);
}
public FwbEntity AddRTD_NGFromReader(FwbEntity fwbEntity, IDataReader reader)
{
while (reader.Read())
{
int seq = 0; try { seq = Convert.ToInt32(reader["seq"]); }
catch { }
FwbNGEntity fwbNGEntity = new FwbNGEntity(
seq,
reader["Type"].ToString(),
reader["NatureGoods"].ToString());
fwbEntity.colNG.Add(fwbNGEntity);
}
reader.Close();
reader.Dispose();
disConnect_dbcn_ExcuteReader();
return(fwbEntity);
}
public FwbEntity AddRTDFromReader(FwbEntity fwbEntity, IDataReader reader)
{
while (reader.Read())
{
int pcs = 0; try { pcs = Convert.ToInt32(reader["pcs"]); }
catch { }
double weight = 0.00; try { weight = Convert.ToDouble(reader["Weight"].ToString()); }
catch (Exception e)
{
}
double chgWeight = 0.00; try { chgWeight = Convert.ToDouble(reader["ChargeWeight"].ToString()); }
catch (Exception e)
{
}
double rateChg = 0.00; try { rateChg = Convert.ToDouble(reader["Rate"].ToString()); }
catch (Exception e)
{
}
double total = 0.00; try { total = Convert.ToDouble(reader["Total"].ToString()); }
catch (Exception e)
{
}
Fwb_newDB_RTDEntity fwb_newDB_RTDEntity = new Fwb_newDB_RTDEntity(
pcs,
weight,
reader["Class"].ToString(),
chgWeight,
rateChg,
total,
reader["Type"].ToString(),
reader["NatureGoods"].ToString());
fwbEntity.colnewDBRTD.Add(fwb_newDB_RTDEntity);
}
reader.Close();
reader.Dispose();
disConnect_dbcn_ExcuteReader();
return(fwbEntity);
}
public FwbEntity AddOTHFromReader(FwbEntity fwbEntity, IDataReader reader)
{
while (reader.Read())
{
double chargeAmt = 0.00; try { chargeAmt = Convert.ToDouble(reader["ChargeAmt"].ToString()); }
catch (Exception e)
{
}
Fwb_newDB_OTHEntity Fwb_newDB_OTHEntity = new Fwb_newDB_OTHEntity(
reader["PrepaidIndicator"].ToString(),
reader["ChargeCode"].ToString(),
reader["Entitlement"].ToString(),
chargeAmt);
fwbEntity.colnewDBOTH.Add(Fwb_newDB_OTHEntity);
}
reader.Close();
reader.Dispose();
disConnect_dbcn_ExcuteReader();
return(fwbEntity);
}
public FwbEntity AddOSIFromReader(FwbEntity fwbEntity, IDataReader reader)
{
while (reader.Read())
{
int mid = 0; try { mid = Convert.ToInt32(reader["MID"].ToString()); }
catch (Exception e)
{
}
int seq = 0; try { seq = Convert.ToInt32(reader["Seq"].ToString()); }
catch (Exception e)
{
}
Fwb_newDB_OSIEntity Fwb_newDB_OSIEntity = new Fwb_newDB_OSIEntity(
reader["SSRtext"].ToString()
);
fwbEntity.colnewDBOSI.Add(Fwb_newDB_OSIEntity);
}
reader.Close();
reader.Dispose();
disConnect_dbcn_ExcuteReader();
return(fwbEntity);
}
public string buildUpFWB(FwbEntity msgEntity, string msgType, string subType)
{
//TIMEZONE UPDATE TEST ==> HOLD!!!
//int timezone = getEXPTimezone(msgEntity.mid, null);
string strAWB = "";
strAWB = base.buildUpBase(msgEntity, msgType, subType);
DateTime nowDate = DateTime.Now;
string msgDate = nowDate.ToString("dd") + transMonth(nowDate.ToString("MM")) + nowDate.ToString("yy");
string weightFormatted = string.Format("{0:0.0}", msgEntity.weight);
char shipmentCode = replaceShipmentIndicator(msgEntity.shipmentIndicator[0]);
//RTG
// added. if Exp_Master.AWBPOU <> Exp_Master.Dest then add RTG. requested by Cecile 2017-12-13 10:46AM
//strAWB += "RTG" + "/" + msgEntity.destFlight + msgEntity.carrier + "\r\n"; // original.
strAWB += "RTG" + "/" + msgEntity.destFlight + msgEntity.carrier;
if (msgEntity.carrier == "BA" && msgEntity.destFlight != msgEntity.dest)
strAWB += "/" + msgEntity.dest + msgEntity.carrier;
strAWB += "\r\n";
//SHP
strAWB += "SHP" + "\r\n";
strAWB += "/" + truncateString(msgEntity.shipperNm, 35).Replace('/', '-') + "\r\n";
strAWB += "/" + truncateString(msgEntity.shipperAddr + msgEntity.shipperAddr2, 35).Replace('/', '-') + "\r\n";
strAWB += "/" + truncateString(msgEntity.shipperCity, 17);
if (msgEntity.shipperState != "")
strAWB += "/" + truncateString(msgEntity.shipperState, 9);
strAWB += "\r\n";
//strAWB += "/" + "US";
strAWB += "/" + truncateString(msgEntity.shipperCountry == "" ? "US" : msgEntity.shipperCountry, 2);
if (msgEntity.shipperZip != "")
strAWB += "/" + truncateString(msgEntity.shipperZip, 9);
if (!string.IsNullOrEmpty(msgEntity.shipperAddrTel))
{
if (msgEntity.shipperZip == "")
strAWB += "/";
msgEntity.shipperAddrTel = string.Join("", msgEntity.shipperAddrTel.ToCharArray().Where(Char.IsDigit));
strAWB += "/TE/" + truncateString(msgEntity.shipperAddrTel, 25);
}
strAWB += "\r\n";
//CNE
strAWB += "CNE" + "\r\n";
strAWB += "/" + truncateString(msgEntity.cneeNm, 35).Replace('/', '-') + "\r\n";
strAWB += "/" + truncateString(msgEntity.cneeAddr, 35).Replace('/', '-') + "\r\n";
strAWB += "/" + truncateString(msgEntity.cneeCity, 17);
if (msgEntity.cneeProv != "")
strAWB += "/" + truncateString(msgEntity.cneeProv, 9);
strAWB += "\r\n";
strAWB += "/" + truncateString(msgEntity.cneeCountry, 2);
if (msgEntity.cneeZip != "")
strAWB += "/" + truncateString(msgEntity.cneeZip, 9);
if (!string.IsNullOrEmpty(msgEntity.cneeAddrTel))
{
if (msgEntity.cneeZip == "")
strAWB += "/";
msgEntity.cneeAddrTel = string.Join("", msgEntity.cneeAddrTel.ToCharArray().Where(Char.IsDigit));
strAWB += "/TE/" + truncateString(msgEntity.cneeAddrTel, 25);
}
strAWB += "\r\n"; //"/" + truncateString(msgEntity.shipperZip, 9) +
//AGT
strAWB += "AGT" + "/" + "/" + msgEntity.agentIATACd;
if (msgEntity.agentCASSaddr.Length == 4)
{
try
{
//CASS Address Numbers Only
Convert.ToInt16(msgEntity.agentCASSaddr);
strAWB += "/" + msgEntity.agentCASSaddr;
}
catch { }
}
strAWB += "\r\n";
strAWB += "/" + truncateString(msgEntity.agentNm, 35) + "\r\n";
strAWB += "/" + truncateString(msgEntity.agentCity, 17) + "\r\n";
//2015-10-06 SSR
if (msgEntity.colnewDBSSR != null && msgEntity.colnewDBSSR.Count > 0)
{
if (msgEntity.colnewDBSSR.First().SSRtext != null && msgEntity.colnewDBSSR.First().SSRtext != string.Empty)
{
strAWB += "SSR";
foreach (Fwb_newDB_SSREntity entity in msgEntity.colnewDBSSR)
{
strAWB += "/" + entity.SSRtext + "\r\n";
}
}
}
//NFY
if(!string.IsNullOrEmpty(msgEntity.nfyNm) && !string.IsNullOrEmpty(msgEntity.nfyAddr) && !string.IsNullOrEmpty(msgEntity.nfyPlace) && !string.IsNullOrEmpty(msgEntity.nfyCountry))
{
strAWB += "NFY/" + truncateString(msgEntity.nfyNm, 35) + "\r\n";
strAWB += "/" + truncateString(msgEntity.nfyAddr, 35) + "\r\n";
strAWB += "/" + truncateString(msgEntity.nfyPlace, 17);
if (!string.IsNullOrEmpty(msgEntity.nfyState))
strAWB += "/" + truncateString(msgEntity.nfyState, 9);
strAWB += "\r\n";
strAWB += "/" + truncateString(msgEntity.nfyCountry, 2);
if(!string.IsNullOrEmpty(msgEntity.nfyZip))
strAWB += "/" + truncateString(msgEntity.nfyZip, 9);
strAWB += "\r\n";
if(!string.IsNullOrEmpty(msgEntity.nfyAddrTel))
}
//CVD
strAWB += "CVD" + "/" + msgEntity.currency + "/" + msgEntity.chargeCd + "/";
strAWB += msgEntity.preChargeWeightCd + msgEntity.preChargeOtherCd + "/";
if (msgEntity.carriageVal == 0.00)
strAWB += "NVD" + "/";
else
strAWB += string.Format("{0:0.00}", msgEntity.carriageVal) + "/";
if (msgEntity.customVal == 0.00)
strAWB += "NCV" + "/";
else
strAWB += string.Format("{0:0.00}", msgEntity.customVal) + "/";
if (msgEntity.insuranceVal == 0.00)
strAWB += "XXX" + "\r\n";
else
strAWB += string.Format("{0:0.00}", msgEntity.insuranceVal) + "\r\n";
int index = 1;
double total = 0;
int ngIndex = 1;
//RTD
if (msgEntity.colRTD != null && msgEntity.colRTD.Count > 0)
{
#region Original
foreach (FwbRateEntity fwbRateEntity in msgEntity.colRTD)
{
if (index == 1)
strAWB += "RTD";
if (index <= 11)
{
strAWB += "/" + index++ + "/" + "P" + fwbRateEntity.pcsRTD + "/";
strAWB += "K" + string.Format("{0:0.0}", fwbRateEntity.weightRTD) + "/";
if (fwbRateEntity.classRTD != null && fwbRateEntity.classRTD.Trim() != string.Empty)
strAWB += "C" + fwbRateEntity.classRTD + "/";
strAWB += "W" + string.Format("{0:0.0}", fwbRateEntity.chargeWeight) + "/";
strAWB += "R" + string.Format("{0:0.00}", fwbRateEntity.rateCharge) + "/";
strAWB += "T" + string.Format("{0:0.0}", fwbRateEntity.total) + "\r\n";
total = fwbRateEntity.total;
if (fwbRateEntity.natureOfGoods != "")
{
strAWB += "/" + "N" + fwbRateEntity.goodsType + "/";
strAWB += truncateString(fwbRateEntity.natureOfGoods, 20) + "\r\n";
}
}
}
#endregion
}
else
{
#region newDB RTD :: Exp_Master_RTD && Exp_Master_RTD_NG||NC
if (msgEntity.colnewDBRTD != null && msgEntity.colnewDBRTD.Count > 0)
{
// 2015-09-29 new RTD
foreach (Fwb_newDB_RTDEntity fwb_newDB_RTDEntity in msgEntity.colnewDBRTD)
{
if (index == 1)
strAWB += "RTD";
if (index <= 11)
{
strAWB += "/" + index++ + "/" + "P" + fwb_newDB_RTDEntity.pcsRTD + "/";
strAWB += "K" + string.Format("{0:0.0}", fwb_newDB_RTDEntity.weightRTD) + "/";
if (fwb_newDB_RTDEntity.classRTD != null && fwb_newDB_RTDEntity.classRTD.Trim() != string.Empty)
strAWB += "C" + fwb_newDB_RTDEntity.classRTD + "/";
strAWB += "W" + string.Format("{0:0.0}", fwb_newDB_RTDEntity.chargeWeight) + "/";
string rateCharge = string.Format("{0:0.00}", fwb_newDB_RTDEntity.rateCharge);
if (rateCharge.Length > 8)
{
rateCharge = fwb_newDB_RTDEntity.rateCharge.ToString("G29");
}
strAWB += "R" + rateCharge + "/";
strAWB += "T" + string.Format("{0:0.00}", fwb_newDB_RTDEntity.total) + "\r\n";
total = fwb_newDB_RTDEntity.total;
}
}
foreach (FwbNGEntity fwbNGEntity in msgEntity.colNG)
{
if (ngIndex != 1)
strAWB += "/" + ngIndex;
strAWB += "/" + "N" + fwbNGEntity.type + "/" + truncateString(fwbNGEntity.naturegoods, 20) + "\r\n";
ngIndex++;
}
}
#endregion
}
// IF Turkish, NO DIM info. requested by Cecile on 2018-1-8. ==> HOLD.
//if(msgEntity.Ccode != "TURKJFK" && msgEntity.Ccode != "X TURKIAH" && msgEntity.Ccode != "WFSTKIAD")
if (msgEntity.Ccode != "X TURKIAH")
{
// 2016-3-18. added.
int cnt_volWeight = msgEntity.colVol.Where(x => x.volWeight > 0).Count();
if (msgEntity.colVol.Count + (ngIndex - 1) + cnt_volWeight > 12)
{
strAWB += "/2/ND//NDA" + "\r\n";
}
else
{
index = ngIndex;
foreach (FwbVolumeEntity fwbVolEntity in msgEntity.colVol)
{
if (index <= 12)
{
strAWB += "/" + index++ + "/" + "ND" + "/";
if (fwbVolEntity.pcsWeight > 0)
{
//2015-09-15 BA&IB and others. also, decimal 1 (not 2)
if (msgEntity.carrier == "BA" || msgEntity.carrier == "IB")
{
strAWB += "K" + Math.Round(fwbVolEntity.pcsWeight);
}
else
{
strAWB += "K" + string.Format("{0:0.0}", fwbVolEntity.pcsWeight);
}
}
else
{
strAWB += "";
}
strAWB += "/";
if (fwbVolEntity.length > 0 && fwbVolEntity.width > 0 && fwbVolEntity.height > 0)
strAWB += fwbVolEntity.unitCode + Math.Round(fwbVolEntity.length) + "-" + Math.Round(fwbVolEntity.width) + "-" + Math.Round(fwbVolEntity.height) + "/" + fwbVolEntity.pcsDim;
else
{
if (msgEntity.msgVersion == 9)
{
strAWB += "CMT0-0-0/" + msgEntity.pcs;
}
else
{
strAWB += "NDA";
}
}
strAWB += "\r\n";
if (fwbVolEntity.volWeight > 0 && index <= 12)
{
strAWB += "/" + index++ + "/" + "NV" + "/";
// Always MC. 2016-05-18
strAWB += "MC";
if (fwbVolEntity.unitCode == "INH")
{
strAWB += string.Format("{0:0.00}", (fwbVolEntity.volWeight / 166)) + "\r\n";
}
else
{
strAWB += string.Format("{0:0.00}", fwbVolEntity.volWeight) + "\r\n";
}
}
}
}
}
} // IF NOT TURKISH airline. END.
//OTH
if (msgEntity.colCharge != null && msgEntity.colCharge.Count > 0)
{
index = 1;
//2017-12-21 OTH modified
foreach (FwbOtherChargeEntity fwbOtherChargeEntity in msgEntity.colCharge.Where(x => x.chargeAmt > 0))
{
if (index == 1)
strAWB += "OTH";
strAWB += "/" + fwbOtherChargeEntity.prepaidIndicator + "/"
+ fwbOtherChargeEntity.chargeCode + fwbOtherChargeEntity.entitlement
+ string.Format("{0:0.00}", fwbOtherChargeEntity.chargeAmt) + "\r\n";
index++;
}
}
else
{
if (msgEntity.colnewDBOTH != null && msgEntity.colnewDBOTH.Count > 0)
{
int OTHindex = 1;
foreach (Fwb_newDB_OTHEntity fwb_newDB_OTHEntity in msgEntity.colnewDBOTH.Where(x => x.chargeAmt > 0))
{
if (OTHindex == 1)
strAWB += "OTH";
strAWB += "/" + fwb_newDB_OTHEntity.prepaidIndicator + "/"
+ fwb_newDB_OTHEntity.chargeCode + fwb_newDB_OTHEntity.entitlement
+ string.Format("{0:0.00}", fwb_newDB_OTHEntity.chargeAmt) + "\r\n";
OTHindex++;
}
}
}
//PPD or COL
if (msgEntity.colnewDBPPDCOL.hasPPDCOL)
{
if (msgEntity.colnewDBPPDCOL.ChargeType == "P")
{
strAWB += "PPD";
}
else
{
strAWB += "COL";
}
int tempcnt = 0;
if (msgEntity.colnewDBPPDCOL.TotalWeight != 0)
{
tempcnt = 1;
strAWB += "/";
strAWB += "WT" + msgEntity.colnewDBPPDCOL.TotalWeight;
}
else
{
tempcnt = 1;
strAWB += "/";
strAWB += "WT0.00";
}
if (msgEntity.colnewDBPPDCOL.Valuation != 0)
{
tempcnt = 1;
strAWB += "/";
strAWB += "VC" + msgEntity.colnewDBPPDCOL.Valuation;
}
if (msgEntity.colnewDBPPDCOL.Taxes != 0)
{
tempcnt = 1;
strAWB += "/";
strAWB += "TX" + msgEntity.colnewDBPPDCOL.Taxes;
}
if (tempcnt == 0)
strAWB += "/";
strAWB += "\r\n";
tempcnt = 0;
if (msgEntity.colnewDBPPDCOL.DueAgent != 0)
{
tempcnt = 1;
strAWB += "/";
strAWB += "OA" + msgEntity.colnewDBPPDCOL.DueAgent;
}
if (msgEntity.colnewDBPPDCOL.DueCarrier != 0)
{
tempcnt = 1;
strAWB += "/";
strAWB += "OC" + msgEntity.colnewDBPPDCOL.DueCarrier;
}
if (msgEntity.colnewDBPPDCOL.Total != 0)
{
tempcnt = 1;
strAWB += "/";
strAWB += "CT" + msgEntity.colnewDBPPDCOL.Total;
}
if (tempcnt == 1)
strAWB += "\r\n";
}
else
{
//updated Original. 2015-10-19. PPD / COL check logic
//strAWB += string.Format("PPD" + "/WT{0:0.0}", total) + "\r\n";
//strAWB += string.Format("/CT{0:0.0}", total) + "\r\n";
double chgDueCarrier = 0;
double chgtotal = 0;
if (msgEntity.colRTD != null && msgEntity.colRTD.Count > 0)
{
chgtotal = msgEntity.colRTD.Sum(x => x.total);
}
if (msgEntity.colCharge != null && msgEntity.colCharge.Count > 0)
{
chgDueCarrier = msgEntity.colCharge.Sum(x => x.chargeAmt);
}
if (chgtotal != 0 || chgDueCarrier != 0)
{
if (msgEntity.chargeCd == "CC")
{
strAWB += "COL";
}
else
{
strAWB += "PPD";
}
if (chgtotal != 0)
strAWB += "/WT" + chgtotal + "\r\n";
else
strAWB += "\r\n";
if (chgDueCarrier != 0)
{
strAWB += "/OC" + chgDueCarrier;
}
strAWB += "/CT" + (chgtotal + chgDueCarrier) + "\r\n";
}
}
//ISU
strAWB += "ISU" + "/" + msgDate + "/" + msgEntity.awbPlace + "\r\n";
//OSI
if (msgEntity.colnewOSI != null && msgEntity.colnewOSI.Count() > 0)
{
index = 1;
foreach (Fwb_newDB_OSIEntity fwbOSIEntity in msgEntity.colnewOSI)
{
if (index > 3)
break; // maximum 3 line
if (!string.IsNullOrEmpty(fwbOSIEntity.OtherInfo))
{
if (index == 1)
strAWB += "OSI";
strAWB += "/" + truncateString(fwbOSIEntity.OtherInfo, 65) + "\r\n";
index++;
}
}
}
//REF
strAWB += "REF" + "/" + "JFKCDCR" + "\r\n";
#region SHC Original
if (msgEntity.SHC.Trim() != "")
{
string temp_SHC = msgEntity.SHC;
temp_SHC = temp_SHC.Replace(" ", "").Trim();
strAWB += "SPH";
int shcCount = 0;
shcCount = temp_SHC.Length / 3;
if (shcCount > 9)
shcCount = 9;
try
{
for (int c = 0; c < shcCount; c++)
{
strAWB += "/" + temp_SHC.Substring(c * 3, 3);
}
}
catch
{
throw new Exception("SHC data Error : MID (" + msgEntity.mid + ")");
}
strAWB += "\r\n";
}
#endregion
////SHC
//if (msgEntity.colnewSHC != null && msgEntity.colnewSHC.Count > 0)
//{
// index = 1;
// foreach (Fwb_newDB_SHCEntity fwbSHCEntity in msgEntity.colnewSHC)
// {
// if (fwbSHCEntity.SHCvalue != null && fwbSHCEntity.SHCvalue != string.Empty)
// {
// if (index == 1)
// strAWB += "SPH";
// strAWB += "/" + fwbSHCEntity.SHCvalue;
// }
// index++;
// }
// strAWB += "\r\n";
//}
//OCI
if (msgEntity.colOCI != null && msgEntity.colOCI.Count > 0)
{
index = 1;
foreach (FwbOCIEntity fwbOCIEntity in msgEntity.colOCI)
{
if (index == 1)
strAWB += "OCI";
strAWB += "/" + fwbOCIEntity.CountryCode + "/" + fwbOCIEntity.Infold + "/" + fwbOCIEntity.CustomsId + "/" + fwbOCIEntity.CustomsInfo + "\r\n";
index++;
}
}
else
{
if (msgEntity.colnewDBOCI != null && msgEntity.colnewDBOCI.Count > 0)
{
if (msgEntity.colnewDBOCI.First().CustomsInfo != null && msgEntity.colnewDBOCI.First().CustomsInfo != string.Empty)
{
int OCIindex = 1;
foreach (Fwb_newDB_OCIEntity Fwb_newDB_OCIEntity in msgEntity.colnewDBOCI)
{
if (OCIindex == 1)
strAWB += "OCI";
strAWB += "/" + Fwb_newDB_OCIEntity.CountryCode + "/" + Fwb_newDB_OCIEntity.Infold + "/" + Fwb_newDB_OCIEntity.CustomsId + "/" + Fwb_newDB_OCIEntity.CustomsInfo + "\r\n";
OCIindex++;
}
}
}
}
return strAWB.ToUpper();
}
public FwbEntity AddSHCFromReader(FwbEntity fwbEntity, IDataReader reader)
{
if (reader.Read())
{
if (reader["SHC"].ToString() != null && reader["SHC"].ToString() != string.Empty)
{
Fwb_newDB_SHCEntity temp = new Fwb_newDB_SHCEntity(
reader["SHC"].ToString()
);
fwbEntity.colnewSHC.Add(temp);
}
if (reader["SHC2"].ToString() != null && reader["SHC2"].ToString() != string.Empty)
{
Fwb_newDB_SHCEntity temp = new Fwb_newDB_SHCEntity(
reader["SHC2"].ToString()
);
fwbEntity.colnewSHC.Add(temp);
}
if (reader["SHC3"].ToString() != null && reader["SHC3"].ToString() != string.Empty)
{
Fwb_newDB_SHCEntity temp = new Fwb_newDB_SHCEntity(
reader["SHC3"].ToString()
);
fwbEntity.colnewSHC.Add(temp);
}
if (reader["SHC4"].ToString() != null && reader["SHC4"].ToString() != string.Empty)
{
Fwb_newDB_SHCEntity temp = new Fwb_newDB_SHCEntity(
reader["SHC4"].ToString()
);
fwbEntity.colnewSHC.Add(temp);
}
if (reader["SHC5"].ToString() != null && reader["SHC5"].ToString() != string.Empty)
{
Fwb_newDB_SHCEntity temp = new Fwb_newDB_SHCEntity(
reader["SHC5"].ToString()
);
fwbEntity.colnewSHC.Add(temp);
}
if (reader["SHC6"].ToString() != null && reader["SHC6"].ToString() != string.Empty)
{
Fwb_newDB_SHCEntity temp = new Fwb_newDB_SHCEntity(
reader["SHC6"].ToString()
);
fwbEntity.colnewSHC.Add(temp);
}
if (reader["SHC7"].ToString() != null && reader["SHC7"].ToString() != string.Empty)
{
Fwb_newDB_SHCEntity temp = new Fwb_newDB_SHCEntity(
reader["SHC8"].ToString()
);
fwbEntity.colnewSHC.Add(temp);
}
if (reader["SHC8"].ToString() != null && reader["SHC8"].ToString() != string.Empty)
{
Fwb_newDB_SHCEntity temp = new Fwb_newDB_SHCEntity(
reader["SHC8"].ToString()
);
fwbEntity.colnewSHC.Add(temp);
}
if (reader["SHC9"].ToString() != null && reader["SHC9"].ToString() != string.Empty)
{
Fwb_newDB_SHCEntity temp = new Fwb_newDB_SHCEntity(
reader["SHC9"].ToString()
);
fwbEntity.colnewSHC.Add(temp);
}
}
reader.Close();
reader.Dispose();
disConnect_dbcn_ExcuteReader();
return(fwbEntity);
}
public FwbEntity GetFWBInfoDAC(int mid, int refID, int flightSeq, string msgType, string subType, int queueId)
{
BaseEntity baseAWB = GetBaseAWBInfoDAC(mid, refID, flightSeq, msgType, subType, queueId);
string strSql = "";
strSql = @" SELECT MID ,Shipper ,ShpAddr ,ShpAddr2 ,ShpAddrCity
,ShpAddrState ,ShpAddrCountry ,ShpAddrZipcode ,Cnee ,CneeAddr ,CneeAddrCity
,CneeAddrProvince ,CneeAddrCountry ,CneeAddrZipcode ,Replace(AgentName,'''','') AgentName ,AgentCode
,AgentCity ,Currency ,WTVAL ,WTVAL2 ,WTVAL3 ,DVCarriage
,DVCustoms ,Insurance ,AgentCassAddr
,
CASE SHC WHEN 'OTHER' THEN
LTRIM(RTRIM(isnull(SHCNote,''))) +
LTRIM(RTRIM(isnull(SHC2,''))) +
LTRIM(RTRIM(isnull(SHC3,''))) +
LTRIM(RTRIM(isnull(SHC4,''))) +
LTRIM(RTRIM(isnull(SHC5,'')))
ELSE
LTRIM(RTRIM(isnull(SHC,''))) +
LTRIM(RTRIM(isnull(SHC2,''))) +
LTRIM(RTRIM(isnull(SHC3,''))) +
LTRIM(RTRIM(isnull(SHC4,''))) +
LTRIM(RTRIM(isnull(SHC5,'')))
END SHC
,(SELECT TOP 1 Acode FROM Location WHERE Lcode = Exp_Master.Lcode) Acode
FROM Exp_Master
WHERE MID = {0}
";
strSql = string.Format(strSql, mid);
FwbEntity fwbEntity = GetFWBfromReader(baseAWB, ExecuteReader(strSql));
//Obtaining Rate Data
#region Original
strSql = @" SELECT MID ,PCs ,Weight ,Class ,ChargeWeight
,Rate ,Total ,Type ,NatureGoods
FROM Exp_IATA
WHERE MID = {0}
";
strSql = string.Format(strSql, mid);
fwbEntity = AddRateValFromReader(fwbEntity, ExecuteReader(strSql));
#endregion
// 2015-09-29 if Exp_IATA doesn't exist.. check new Table :: Exp_Master_RTD && _NG || _NC
if (fwbEntity.colRTD == null || fwbEntity.colRTD.Count == 0)
{
#region 2015-09-29 new added
// 2015-09-29 new RTD.
strSql = @" select MID, r_numpieces as PCs, r_weight_p as [Weight], r_rateclasscd as Class, r_weight_w as ChargeWeight,
r_ratecharge as Rate,
CASE WHEN ISNUMERIC(r_chargeamt) = 1 then CONVERT(DECIMAL(10,2), r_chargeamt) else
(
(
(CASE WHEN (ISNUMERIC(r_weight_w) = 1) THEN r_weight_w ELSE 0 END)
*
(CASE WHEN (ISNUMERIC(r_ratecharge) = 1) THEN CONVERT(DECIMAL(10,2), r_ratecharge) ELSE 0 end)
)
) end as Total,
'' as [Type], '' as NatureGoods
From exp_master_rtd
where mid = {0}
";
strSql = string.Format(strSql, mid);
fwbEntity = AddRTDFromReader(fwbEntity, ExecuteReader(strSql));
// 2015-09-29 RTD_NG || NC
strSql = @"
select seq, r_goodsdataidc as Type, r_naturenqog as NatureGoods from exp_master_rtd_nc
where mid = {0}
union
select seq, r_goodsdataidg as Type, r_naturenqog as NatureGoods from exp_master_rtd_ng
where mid = {0}
";
strSql = string.Format(strSql, mid);
fwbEntity = AddRTD_NGFromReader(fwbEntity, ExecuteReader(strSql));
#endregion
}
//Obtaining Rate Data
strSql = @" SELECT MID ,PcsWeight ,Width ,Lenght
,Height ,unit ,Pcs ,VolWeight
FROM Exp_DimWt
WHERE MID = {0}
";
strSql = string.Format(strSql, mid);
fwbEntity = AddVolumeFromReader(fwbEntity, ExecuteReader(strSql));
//Obtaining Rate Data
strSql = @" SELECT MID ,ChargeCode ,ChargeTo Entitlement, Amount ChargeAmt, Type PrepaidIndicator
FROM EXP_OtherCharge
WHERE MID = {0}
";
strSql = string.Format(strSql, mid);
fwbEntity = AddOtherChargeFromReader(fwbEntity, ExecuteReader(strSql));
// 2015-09-29 if EXP_OtherCharge doesn't exist.. check new Table :: Exp_Master_OTH
if (fwbEntity.colCharge == null || fwbEntity.colCharge.Count == 0)
{
#region 2015-09-29 new added
strSql = @" SELECT MID, o_otherchcd as ChargeCode, o_entitlementcd as Entitlement, o_chargeamt as ChargeAmt, o_pcother as PrepaidIndicator
FROM Exp_Master_OTH
WHERE MID = {0}
";
strSql = string.Format(strSql, mid);
fwbEntity = AddOTHFromReader(fwbEntity, ExecuteReader(strSql));
#endregion
}
//OCI
strSql = @" select MID, HID, CountryCode, Infold, CustomsId, CustomsInfo from Exp_OCI
WHERE MID = {0}
";
strSql = string.Format(strSql, mid);
fwbEntity = AddOCIValFromReader(fwbEntity, ExecuteReader(strSql));
if (fwbEntity.colOCI == null || fwbEntity.colOCI.Count == 0)
{
strSql = @" SELECT MID, o_isocountrycd as CountryCode, o_informationid as Infold, o_customsinfoid as CustomsId, o_suppcustomsinfo as CustomsInfo
FROM Exp_Master_OCI
WHERE MID = {0}
";
strSql = string.Format(strSql, mid);
fwbEntity = AddOCIFromReader(fwbEntity, ExecuteReader(strSql));
}
//2015-10-06 _SSR
strSql = @" SELECT MID, Seq, s_ssr as SSRtext
FROM Exp_Master_SSR
WHERE MID = {0}
ORDER by Seq
";
strSql = string.Format(strSql, mid);
fwbEntity = AddSSRFromReader(fwbEntity, ExecuteReader(strSql));
//2015-10-06. PPD
//2017-11-21. add PPD for WTVAL is NC. Requested by Cecile 2017-11-21 9:12am.
strSql = @"
DECLARE @typeSelector VARCHAR(5)
DECLARE @MID INT = {0}
DECLARE @ppdTotal INT
DECLARE @colTotal INT
SET @typeSelector = ( SELECT RTRIM(LTRIM(ISNULL(WTVAL, ''))) FROM Exp_Master WHERE MID = @MID)
IF(@typeselector = 'NC' or SUBSTRING(@typeselector, 1, 1) = 'P')
BEGIN
SELECT MID, 'P' as ChargeType, PPDTotalWeight as TotalWeight, PPDValuation as Valuation, PPDTaxes as Taxes, PPDDueAgent as DueAgent, PPDDueCarrier as DueCarrier,
CASE WHEN (PPDTotal is NULL or PPDTotal = 0) THEN (ISNULL(PPDTotalWeight, 0) + ISNULL(PPDValuation, 0) + ISNULL(PPDTaxes, 0) + ISNULL(PPDDueAgent, 0) + ISNULL(PPDDueCarrier, 0))
ELSE PPDTotal END as Total
FROM Exp_Master
WHERE MID = @MID
END
ElSE IF(SUBSTRING(@typeselector, 1, 1) = 'C')
BEGIN
SELECT MID, 'C' as ChargeType, COLTotalWeight as TotalWeight, COLValuation as Valuation, COLTaxes as Taxes, COLDueAgent as DueAgent, COLDueCarrier as DueCarrier,
CASE WHEN (COLTotal is NULL or COLTotal = 0) THEN (ISNULL(COLTotalWeight, 0) + ISNULL(COLValuation, 0) + ISNULL(COLTaxes, 0) + ISNULL(COLDueAgent, 0) + ISNULL(COLDueCarrier, 0))
ELSE COLTotal END as Total
FROM Exp_Master
WHERE MID = @MID
END
";
strSql = string.Format(strSql, mid);
fwbEntity = AddPPDFromReader(fwbEntity, ExecuteReader(strSql));
// //SHC added. 2015-10-21
// strSql = @"
// SELECT CASE WHEN UPPER(SHC) = 'OTHER' THEN SHCnote ELSE SHC END as SHC,
// SHC2, SHC3, SHC4, SHC5, SHC6, SHC7, SHC8, SHC9 FROM Exp_Master WHERE MID = {0}
// ";
// strSql = string.Format(strSql, mid);
// fwbEntity = AddSHCFromReader(fwbEntity, ExecuteReader(strSql));
//2018-05-30. OSI
strSql = @"
SELECT * FROM Exp_Master_OSI WHERE MID = {0} ORDER BT Seq
";
strSql = string.Format(strSql, mid);
fwbEntity = AddPPDFromReader(fwbEntity, ExecuteReader(strSql));
return(fwbEntity);
}
public FwbEntity AddOSIFromReader(FwbEntity fwbEntity, IDataReader reader)
{
}
public FwbEntity AddPPDFromReader(FwbEntity fwbEntity, IDataReader reader)
{
if (reader.Read())
{
int mid = 0; try { mid = Convert.ToInt32(reader["MID"].ToString()); }
catch (Exception e)
{
}
decimal totalWeightPPD = 0; try { totalWeightPPD = Convert.ToDecimal(reader["TotalWeightPPD"].ToString()); }
catch (Exception e)
{
}
decimal totalWeightCOL = 0; try { totalWeightCOL = Convert.ToDecimal(reader["TotalWeightCOL"].ToString()); }
catch (Exception e)
{
}
decimal valuationPPD = 0; try { valuationPPD = Convert.ToDecimal(reader["ValuationPPD"].ToString()); }
catch (Exception e)
{
}
decimal valuationCOL = 0; try { valuationCOL = Convert.ToDecimal(reader["ValuationCOL"].ToString()); }
catch (Exception e)
{
}
decimal taxesPPD = 0; try { taxesPPD = Convert.ToDecimal(reader["TaxesCOL"].ToString()); }
catch (Exception e)
{
}
decimal taxesCOL = 0; try { taxesCOL = Convert.ToDecimal(reader["TaxesCOL"].ToString()); }
catch (Exception e)
{
}
decimal dueAgentPPD = 0; try { dueAgentPPD = Convert.ToDecimal(reader["DueAgentPPD"].ToString()); }
catch (Exception e)
{
}
decimal dueAgentCOL = 0; try { dueAgentCOL = Convert.ToDecimal(reader["DueAgentCOL"].ToString()); }
catch (Exception e)
{
}
decimal dueCarrierPPD = 0; try { dueCarrierPPD = Convert.ToDecimal(reader["DueCarrierPPD"].ToString()); }
catch (Exception e)
{
}
decimal dueCarrierCOL = 0; try { dueCarrierCOL = Convert.ToDecimal(reader["DueCarrierCOL"].ToString()); }
catch (Exception e)
{
}
decimal totalPPD = 0; try { totalPPD = Convert.ToDecimal(reader["TotalPPD"].ToString()); }
catch (Exception e)
{
}
decimal totalCOL = 0; try { totalCOL = Convert.ToDecimal(reader["TotalCOL"].ToString()); }
catch (Exception e)
{
}
//fwbEntity.colnewDBPPDCOL.ChargeType = reader["ChargeType"].ToString();
fwbEntity.colnewDBPPDCOL.MID = mid;
fwbEntity.colnewDBPPDCOL.TotalWeightPPD = totalWeightPPD;
fwbEntity.colnewDBPPDCOL.TotalWeightCOL = totalWeightCOL;
fwbEntity.colnewDBPPDCOL.ValuationPPD = valuationPPD;
fwbEntity.colnewDBPPDCOL.ValuationCOL = valuationCOL;
fwbEntity.colnewDBPPDCOL.TaxesPPD = taxesPPD;
fwbEntity.colnewDBPPDCOL.TaxesCOL = taxesCOL;
fwbEntity.colnewDBPPDCOL.DueAgentPPD = dueAgentPPD;
fwbEntity.colnewDBPPDCOL.DueAgentCOL = dueAgentCOL;
fwbEntity.colnewDBPPDCOL.DueCarrierPPD = dueCarrierPPD;
fwbEntity.colnewDBPPDCOL.DueCarrierCOL = dueCarrierCOL;
fwbEntity.colnewDBPPDCOL.TotalPPD = totalPPD;
fwbEntity.colnewDBPPDCOL.TotalCOL = totalCOL;
}
reader.Close();
reader.Dispose();
disConnect_dbcn_ExcuteReader();
return(fwbEntity);
}
public FwbEntity GetFWBfromReader(BaseEntity baseEntity, IDataReader reader)
{
FwbEntity fwbEntity = new FwbEntity();
if (reader.Read())
{
double carriageVal = 0.00; try { carriageVal = Convert.ToDouble(reader["DVCarriage"].ToString()); }
catch (Exception e)
{
}
double customVal = 0.00; try { customVal = Convert.ToDouble(reader["DVCustoms"].ToString()); }
catch (Exception e)
{
}
double insuranceVal = 0.00; try { insuranceVal = Convert.ToDouble(reader["Insurance"].ToString()); }
catch (Exception e)
{
}
fwbEntity = new FwbEntity(
baseEntity,
reader["Shipper"].ToString().Trim(),
reader["ShpAddr"].ToString().Trim(),
reader["ShpAddr2"].ToString().Trim(),
reader["ShpAddrCity"].ToString().Trim(),
reader["ShpAddrState"].ToString().Trim(),
reader["ShpAddrCountry"].ToString().Trim(),
reader["ShpAddrZipcode"].ToString().Trim(),
reader["ShipperTel"].ToString().Trim(),
reader["Cnee"].ToString().Trim(),
reader["CneeAddr"].ToString().Trim(),
reader["CneeAddrCity"].ToString().Trim(),
reader["CneeAddrProvince"].ToString().Trim(),
reader["CneeAddrCountry"].ToString().Trim(),
reader["CneeAddrZipcode"].ToString().Trim(),
reader["CneeTel"].ToString().Trim(),
reader["NfyName"].ToString().Trim(),
reader["NfyAddr"].ToString().Trim(),
reader["NfyPlace"].ToString().Trim(),
reader["NfyState"].ToString().Trim(),
reader["NfyCountry"].ToString().Trim(),
reader["NfyZipcode"].ToString().Trim(),
reader["NfyTel"].ToString().Trim(),
reader["AgentName"].ToString().Trim(),
reader["AgentCode"].ToString().Trim(),
reader["AgentCassAddr"].ToString().Trim(),
reader["AgentCity"].ToString().Trim(),
reader["Currency"].ToString().Trim(),
reader["WTVAL"].ToString().Trim(),
reader["WTVAL2"].ToString().Trim(),
reader["WTVAL3"].ToString().Trim(),
carriageVal,
customVal,
insuranceVal,
reader["Acode"].ToString().Trim(),
reader["SHC"].ToString().Trim()
);
reader.Close();
reader.Dispose();
disConnect_dbcn_ExcuteReader();
return(fwbEntity);
}
return(fwbEntity);
}
