public FhlEntity AddnewHTSFromReader(FhlEntity fhlEntity, IDataReader reader)
{
while (reader.Read())
{
FhlHTSEntity fhlHTSEntity = new FhlHTSEntity(
reader["Descr"].ToString());
fhlEntity.colnewDBHTS.Add(fhlHTSEntity);
}
reader.Close();
reader.Dispose();
disConnect_dbcn_ExcuteReader();
return(fhlEntity);
}
public FhlEntity AddnewOCIFromReader(FhlEntity fhlEntity, IDataReader reader)
{
while (reader.Read())
{
FhlOCIEntity fhlOCIEntity = new FhlOCIEntity(
reader["CountryCode"].ToString(),
reader["Infold"].ToString(),
reader["CustomsId"].ToString(),
reader["CustomsInfo"].ToString()
);
fhlEntity.colnewDBOCI.Add(fhlOCIEntity);
}
reader.Close();
reader.Dispose();
disConnect_dbcn_ExcuteReader();
return(fhlEntity);
}
public FhlEntity AddVolumeFromReader(FhlEntity 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 FhlEntity AddOtherChargeFromReader(FhlEntity fwbEntity, IDataReader reader)
{
while (reader.Read())
{
double chargeAmt = 0.00; try { chargeAmt = Convert.ToDouble(reader["ChargeAmt"].ToString()); }
catch (Exception e)
{
}
FwbOtherChargeEntity fwbOtherChargeEntity = new FwbOtherChargeEntity(
reader["PrepaidIndicator"].ToString(),
reader["ChargeCode"].ToString(),
reader["Entitlement"].ToString(),
chargeAmt);
fwbEntity.colCharge.Add(fwbOtherChargeEntity);
}
reader.Close();
reader.Dispose();
disConnect_dbcn_ExcuteReader();
return(fwbEntity);
}
public string buildUpFHL(FhlEntity msgEntity, string msgType, string subType)
{
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]);
//MBI
strAWB += "MBI" + "/" + msgEntity.prefix + "-" + msgEntity.awb + msgEntity.origin + msgEntity.dest + "/" + shipmentCode + msgEntity.pcs + "K" + msgEntity.weight + "\r\n";
string hawb = msgEntity.HAWB;
if (hawb != null && hawb.Length > 12)
{
hawb = hawb.Substring(0, 12);
}
hawb = hawb.Replace(" ", string.Empty);
//HBS
strAWB += "HBS" + "/" + hawb + "/" + msgEntity.HOrigin + msgEntity.HDest + "/" + msgEntity.HPcs + "/" + "K" + string.Format("{0:0.0}", msgEntity.HWeight) +
(msgEntity.SLAC == 0 ? "/" : "/" + msgEntity.SLAC)
+ "/" + msgEntity.Commodity + "\r\n";
if (msgEntity.SHC.Trim() != "")
{
string temp_SHC = msgEntity.SHC;
temp_SHC = temp_SHC.Replace(" ", "").Trim();
int shcCount = 0;
shcCount = temp_SHC.Length / 3;
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";
}
int index = 1;
//TXT
if (msgEntity.colTXT != null && msgEntity.colTXT.Count > 0)
{
foreach (FhlTXTEntity fhlTXTEntity in msgEntity.colTXT)
{
if (index == 1)
{
strAWB += "TXT";
}
strAWB += "/" + fhlTXTEntity.Descr + "\r\n";
index++;
if (index == 10)
{
break;
}
}
}
else
{
index = 1;
foreach (FhlTXTEntity fhlTXTEntity in msgEntity.colnewDBTXT)
{
if (index == 1)
{
strAWB += "TXT";
}
strAWB += "/" + fhlTXTEntity.Descr + "\r\n";
index++;
if (index == 10)
{
break;
}
}
}
//HTS
if (msgEntity.colHTS != null && msgEntity.colHTS.Count > 0)
{
index = 1;
foreach (FhlHTSEntity fhlHTSEntity in msgEntity.colHTS)
{
if (index == 1)
{
strAWB += "HTS";
}
strAWB += "/" + fhlHTSEntity.Descr + "\r\n";
index++;
}
}
else
{
index = 1;
foreach (FhlHTSEntity fhlHTSEntity in msgEntity.colnewDBHTS)
{
if (index == 1)
{
strAWB += "HTS";
}
strAWB += "/" + fhlHTSEntity.Descr + "\r\n";
index++;
}
}
//OCI
if (msgEntity.colOCI != null && msgEntity.colOCI.Count > 0)
{
index = 1;
foreach (FhlOCIEntity fhlOCIEntity in msgEntity.colOCI)
{
if (index == 1)
{
strAWB += "OCI";
}
strAWB += "/" + fhlOCIEntity.CountryCode + "/" + fhlOCIEntity.Infold + "/" + fhlOCIEntity.CustomsId + "/" + fhlOCIEntity.CustomsInfo + "\r\n";
index++;
}
}
else
{
index = 1;
foreach (FhlOCIEntity fhlOCIEntity in msgEntity.colnewDBOCI)
{
if (index == 1)
{
strAWB += "OCI";
}
strAWB += "/" + fhlOCIEntity.CountryCode + "/" + fhlOCIEntity.Infold + "/" + fhlOCIEntity.CustomsId + "/" + fhlOCIEntity.CustomsInfo + "\r\n";
index++;
}
}
//SHP
if (msgEntity.shipperNm != "")
{
strAWB += "SHP";
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.Trim() != "")
{
strAWB += "/" + truncateString(msgEntity.shipperZip, 25);
}
if (msgEntity.shipperAddrTel != null && msgEntity.shipperAddrTel.Trim() != "")
{
strAWB += "/";
if (msgEntity.shipperContact == null || msgEntity.shipperContact.Trim() == "")
{
msgEntity.shipperContact = "TE";
}
strAWB += "/" + truncateString(msgEntity.shipperContact.Trim(), 25) + "/" + truncateString(msgEntity.shipperAddrTel.Trim(), 25);
}
//strAWB += "/" + truncateString(msgEntity.shipperZip,9);
//strAWB += "/" + truncateString(msgEntity.shipperContact,3);
//strAWB += "/" + truncateString(msgEntity.shipperAddrTel,25);
//if (msgEntity.shipperZip != "")
// strAWB += "/" + truncateString(msgEntity.shipperZip, 9);
strAWB += "\r\n";
}
if (msgEntity.cneeNm != "")
{
//CNE
strAWB += "CNE";
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); //"/" + truncateString(msgEntity.shipperZip, 9) +
if (msgEntity.cneeZip.Trim() != "")
{
strAWB += "/" + truncateString(msgEntity.cneeZip, 25);
}
if (msgEntity.cneeAddrTel.Trim() != "")
{
if (msgEntity.cneeContact == null || msgEntity.cneeContact.Trim() == "")
{
msgEntity.cneeContact = "TE";
}
if (msgEntity.cneeZip == null || msgEntity.cneeZip.Trim() == "")
{
strAWB += "/";
}
strAWB += "/" + truncateString(msgEntity.cneeContact.Trim(), 25) + "/" + truncateString(msgEntity.cneeAddrTel.Trim(), 25);
}
//strAWB += "/" + truncateString(msgEntity.cneeZip, 9);
//strAWB += "/" + truncateString(msgEntity.cneeContact, 3);
//strAWB += "/" + truncateString(msgEntity.cneeAddrTel, 25);
strAWB += "\r\n";
}
//CVD
//strAWB += "CVD" + "/" + msgEntity.currency + "/" + msgEntity.chargeCd + "/";
if (msgEntity.currency != "")
{
strAWB += "CVD" + "/" + msgEntity.currency + "/";
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";
}
}
return(strAWB.ToUpper());
}
public FhlEntity GetFHLfromReader(BaseEntity baseEntity, IDataReader reader)
{
FhlEntity FhlEntity = new FhlEntity();
if (reader.Read())
{
int hpcs = 0; try { hpcs = Convert.ToInt32(reader["HPcs"].ToString()); }
catch (Exception e)
{
}
int SLAC = 0; try { SLAC = Convert.ToInt32(reader["SLAC"].ToString()); }
catch (Exception e)
{
}
decimal hweight = 0; try { hweight = Convert.ToDecimal(reader["HWeight"].ToString()); }
catch (Exception e)
{
}
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)
{
}
FhlEntity = new FhlEntity(
baseEntity,
reader["HAWB"].ToString().Trim(),
hpcs,
SLAC,
hweight,
reader["HDest"].ToString().Trim(),
reader["HOrigin"].ToString().Trim(),
reader["Partial"].ToString().Trim(),
reader["Commodity"].ToString().Trim(),
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["ShpAddrTel"].ToString().Trim(),
reader["ShpContact"].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["CneeAddrTel"].ToString().Trim(),
reader["CneeContact"].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["SHC"].ToString().Trim()
//reader["Acode"].ToString().Trim()
);
reader.Close();
reader.Dispose();
disConnect_dbcn_ExcuteReader();
return(FhlEntity);
}
return(FhlEntity);
}
public FhlEntity GetFHLInfoDAC(int hid, int refID, int flightSeq, string msgType, string subType, int queueId)
{
BaseEntity baseAWB = GetBaseAWBInfoDAC(hid, refID, flightSeq, msgType, subType, queueId);
string strSql = "";
strSql = @" SELECT hid , hawb, Pcs as HPcs, SLAC, Weight as HWeight, Dest as HDest, Origin as HOrigin, Commodity, Shipper ,ShpAddr ,ShpAddr2 ,ShpAddrCity
,ShpAddrState ,ShpAddrCountry ,ShpAddrZipcode , ShpAddrTel, ShpContact, Cnee ,CneeAddr ,CneeAddrCity
,CneeAddrProvince ,CneeAddrCountry ,CneeAddrZipcode , CneeAddrTel, CneeContact
,Currency ,WTVAL ,WTVAL2 ,WTVAL3 ,DVCarriage ,DVCustoms ,Insurance
,'T' as Partial
--, isnull(( Select Partial From Exp_FlightMaster where mid = A.mid and OnPCS is not null group by Partial),'T') as Partial
--,(SELECT TOP 1 Acode FROM Location WHERE Lcode = Exp_Master.Lcode) Acode ,AgentName ,AgentCode ,AgentCity ,AgentCassAddr
,
CASE SHC WHEN 'OTHER' THEN
LTRIM(RTRIM(isnull(SHC,''))) +
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
FROM Exp_House as A
WHERE hid = {0}
";
strSql = string.Format(strSql, hid);
FhlEntity FhlEntity = GetFHLfromReader(baseAWB, ExecuteReader(strSql));
//TXT
#region Original
strSql = @" select MID, HID, Type, Descr from dbo.Exp_FHL_Line
WHERE HID = {0} and Type = 'TXT'
";
strSql = string.Format(strSql, hid);
FhlEntity = AddTXTValFromReader(FhlEntity, ExecuteReader(strSql));
#endregion
// 2015-09-30 If Exp_FHL_Line not exsit, check _TXT
if (FhlEntity.colTXT == null || FhlEntity.colTXT.Count == 0)
{
strSql = @"
SELECT t_txt as Descr FROM Exp_House_TXT
WHERE HID = {0}
";
strSql = string.Format(strSql, hid);
FhlEntity = AddnewTXTFromReader(FhlEntity, ExecuteReader(strSql));
}
//HTS
#region Original
strSql = @" select MID, HID, Type, Descr from dbo.Exp_FHL_Line
WHERE HID = {0} and Type = 'HTS'
";
strSql = string.Format(strSql, hid);
FhlEntity = AddHTSValFromReader(FhlEntity, ExecuteReader(strSql));
#endregion
// 2015-09-30 If Exp_FHL_Line not exsit, check _HTS
if (FhlEntity.colHTS == null || FhlEntity.colHTS.Count == 0)
{
strSql = @"
SELECT h_hccode as Descr FROM Exp_House_HTS
WHERE HID = {0}
";
strSql = string.Format(strSql, hid);
FhlEntity = AddnewHTSFromReader(FhlEntity, ExecuteReader(strSql));
}
//OCI
#region Original
strSql = @" select MID, HID, CountryCode, Infold, CustomsId, CustomsInfo from Exp_OCI
WHERE HID = {0}
";
strSql = string.Format(strSql, hid);
FhlEntity = AddOCIValFromReader(FhlEntity, ExecuteReader(strSql));
#endregion
// 2015-09-30 If Exp_FHL_Line not exsit, check _OCI
if (FhlEntity.colOCI == null || FhlEntity.colOCI.Count == 0)
{
strSql = @" SELECT o_cntcode as CountryCode, o_infoident as Infold, o_cinfoident as CustomsId, o_scinfo as CustomsInfo
FROM Exp_House_OCI
WHERE HID = {0}
";
strSql = string.Format(strSql, hid);
FhlEntity = AddnewOCIFromReader(FhlEntity, ExecuteReader(strSql));
}
#region Comment
// //Obtaining Rate Data
// strSql = @" SELECT MID ,PcsWeight ,Width ,Lenght
// ,Height ,unit ,Pcs ,VolWeight
// FROM Exp_DimWt
// WHERE MID = {0}
// ";
// strSql = string.Format(strSql, hid);
// FhlEntity = AddVolumeFromReader(FhlEntity, 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, hid);
// FhlEntity = AddOtherChargeFromReader(FhlEntity, ExecuteReader(strSql));
#endregion
return(FhlEntity);
}
