public void LoadWayBillDatabaseData(string id) { //--------------------- add by fan 20100107 ---------- /* string Type = ""; string whereTDcheck = "'" + id + "','1'"; DataTable TDcheckTable = DBFactory.PopulateTempTable_BySp("[op_TwoDCode_Check]", whereTDcheck); Type = TDcheckTable.Rows[0][0].ToString().Trim();*/ //-------------------- end ----------------------------- //string Consolidate = "";//modify by 20120801 string WAYBILL_NUMBER = ""; //fill boxList table WAYBILL_ADDITION wayBillAddition = new WAYBILL_ADDITION(); string whereConsolidateClause = "WHERE InternalID='" + id + "'"; List<string> fields11 = new List<string>(); fields11.Add("*"); DataTable ConsolidateTable = DBFactory.PopulateTempTable("[v_Shipment_PAKComn]", whereConsolidateClause, fields11);//根据船务抓取相应信息 //Consolidate = ConsolidateTable.Rows[0]["CONSOL_INVOICE"].ToString().Trim(); //Modify by 20120801 WAYBILL_NUMBER = ConsolidateTable.Rows[0]["C46"].ToString().Trim();//获得waybill_number //if (Consolidate.Trim() != "")// modify by 20120801 if (WAYBILL_NUMBER.Trim() != "") { List<string> fields = new List<string>(); fields.Add("*"); //DISTINCT CONSOL_INVOICE,SUB_REGION,WAYBILL_NUMBER,INTL_CARRIER,ACTUAL_SHIPDATE List<string> fieldssubregion = new List<string>(); fieldssubregion.Add("distinct CONSOL_INVOICE"); fieldssubregion.Add("SUB_REGION"); fieldssubregion.Add("MASTER_WAYBILL_NUMBER"); fieldssubregion.Add("WAYBILL_NUMBER"); fieldssubregion.Add("PREF_GATEWAY"); // add by Fan 20090912 // fieldssubregion.Add("DUTY_CODE"); fieldssubregion.Add("INTL_CARRIER"); fieldssubregion.Add("ACTUAL_SHIPDATE"); fieldssubregion.Add("SHIP_MODE"); //fieldssubregion.Add("INCOTERM"); // Modify by 20130304 //fieldssubregion.Add("CONTAINER_ID"); //fieldssubregion.Add("SEAL_NUMBER"); //fieldssubregion.Add("TRUCK_NUMBER"); fieldssubregion.Add("SHIP_FROM_ID"); fieldssubregion.Add("SHIP_FROM_NAME"); fieldssubregion.Add("SHIP_FROM_NAME_2"); fieldssubregion.Add("SHIP_FROM_NAME_3"); fieldssubregion.Add("SHIP_FROM_STREET"); fieldssubregion.Add("SHIP_FROM_STREET_2"); fieldssubregion.Add("SHIP_FROM_CITY"); fieldssubregion.Add("SHIP_FROM_STATE"); fieldssubregion.Add("SHIP_FROM_ZIP"); fieldssubregion.Add("SHIP_FROM_COUNTRY_CODE"); fieldssubregion.Add("SHIP_FROM_COUNTRY_NAME"); fieldssubregion.Add("SHIP_FROM_CONTACT"); fieldssubregion.Add("SHIP_FROM_TELEPHONE"); //string whereMainClause = "WHERE CONSOL_INVOICE='" + Consolidate + "'"; //modify by 20120801 string whereMainClause = "WHERE C46='" + WAYBILL_NUMBER + "'"; DataTable packIDTable = DBFactory.PopulateTempTable("[v_Shipment_WayBillRegion]", whereMainClause, fieldssubregion); // DataTable packIDTable = DBFactory.PopulateTempTable("[v_Shipment_WayBillList]", whereMainClause, fieldssubregion); int index = 0; int index3 = 0; int index2 = 0; int index4 = 0; foreach (DataRow readRow in packIDTable.Rows) { WAYBILL_ADDITION.SUBREGIONRow newRow = wayBillAddition.SUBREGION.NewSUBREGIONRow(); newRow.SUBREGION_Id = index; foreach (DataColumn column in wayBillAddition.SUBREGION.Columns) { if (packIDTable.Columns.Contains(column.ColumnName) && (column.ColumnName != "BOX_UNIT_QTY")) { //if table contains name, then populate if (column.DataType != packIDTable.Columns[column.ColumnName].DataType) { if (column.DataType == typeof(Double)) { newRow[column] = Convert.ToDouble(readRow[column.ColumnName]); } // else if (column.DataType == typeof(String)) // { // newRow[column] = Convert.ToString(readRow[column.ColumnName]); // } } else newRow[column] = readRow[column.ColumnName]; } else { if (column.DataType == typeof(string)) newRow[column] = string.Empty; else if (column.DataType == typeof(Double)) newRow[column] = 0; // else if (column.DataType == typeof(DateTime)) // newRow[column] = string.Empty; } } //string whereHAWBClause_ACT = "'" + readRow["CONSOL_INVOICE"].ToString() + "','1'";// modify by 20120801 string whereHAWBClause_ACT = "'" + readRow["WAYBILL_NUMBER"].ToString() + "','3'"; DataTable hawbTable_ACT = DBFactory.PopulateTempTable_BySp("[op_PAK_ShipmentWeight_FIS]", whereHAWBClause_ACT); newRow.HAWB_ACT_WEIGHT = double.Parse(hawbTable_ACT.Rows[0]["Weight"].ToString()); //HAWB_ACT_WEIGHT /*string whereHAWBClause_ACT = "WHERE Shipment='" + readRow["CONSOL_INVOICE"].ToString() + "'"; DataTable hawbTable_ACT = DBFactory.PopulateTempTable("[PAK_ShipmentWeight_FIS]", whereHAWBClause_ACT, fields); newRow.HAWB_ACT_WEIGHT = double.Parse(hawbTable_ACT.Rows[0]["Weight"].ToString());*/ //HAWB_BOX_QTY //string whereHAWBClause = "WHERE WAYBILL_NUMBER='" + readRow["WAYBILL_NUMBER"].ToString() + "'"; //DataTable hawbTable = DBFactory.PopulateTempTable("[v_PAKComn]", whereHAWBClause, fields); List<string> fields2 = new List<string>(); fields2.Add("sum(DEST_CODE_BOX_QTY) as DEST_CODE_BOX_QTY"); // string whereHAWBClause = "WHERE CONSOL_INVOICE='" + readRow["CONSOL_INVOICE"].ToString() + "'"; //modify by 20120801 string whereHAWBClause = "WHERE C46='" + readRow["WAYBILL_NUMBER"].ToString() + "'"; DataTable hawbTable = DBFactory.PopulateTempTable("[v_Shipment_WayBillList]", whereHAWBClause, fields2); //add up PACK_ID_UNIT_QTY newRow.HAWB_BOX_QTY = double.Parse(hawbTable.Rows[0]["DEST_CODE_BOX_QTY"].ToString()); //HAWB_PALLET_QTY List<string> fieldspallet = new List<string>(); fieldspallet.Add("distinct PLT"); string whereHAWB2Clause = "WHERE Consolidate = '" + readRow["CONSOL_INVOICE"].ToString() + "'"; //modify by 20120801 //string whereHAWB2Clause = "WHERE C46 = '" + readRow["C46"].ToString() + "'"; DataTable hawb2Table = DBFactory.PopulateTempTable("[P_PoPlt]", whereHAWB2Clause, fieldspallet); newRow.HAWB_PALLET_QTY = hawb2Table.Rows.Count; //CUR DATA newRow.CURRENT_DATE = DateTime.Today.ToString("yyyy-MM-dd"); // ----modeify 20090911 //newRow.CURRENT_DATE = DateTime.Today; //newRow.ACTUAL_SHIPDATE = Convert.ToDateTime(packIDTable.Rows[0]["ACTUAL_SHIPDATE"]); DateTime test = Convert.ToDateTime(packIDTable.Rows[0]["ACTUAL_SHIPDATE"]); newRow.ACTUAL_SHIPDATE = test.ToString("yyyy-MM-dd"); // (Convert.ToDateTime(dr["Date"])).ToString(" wayBillAddition.SUBREGION.Rows.Add(newRow); // DEST_CODECONSOL_INVOICE 耞碻吏Ω计 DEST_CODECONSOL_INVOICE List<string> fields1 = new List<string>(); fields1.Add("*"); //string whereHAWB3Clause = "WHERE InternalID in (SELECT InternalID FROM [v_PAKComn] " + whereHAWBClause + ") Group by " + fields1[0].ToString() + "," + fields1[1].ToString(); //string whereHAWB3Clause = "WHERE CONSOL_INVOICE = '" + Consolidate + "' and SUB_REGION = '" + packIDTable.Rows[index]["SUB_REGION"] + "'"; //modify by 20120801 string whereHAWB3Clause = "WHERE C46 = '" + WAYBILL_NUMBER + "' and SUB_REGION = '" + packIDTable.Rows[index]["SUB_REGION"] + "'"; DataTable hawb3Table = DBFactory.PopulateTempTable("[v_Shipment_WayBillList]", whereHAWB3Clause, fields1); int indexcount3 = 0; //DESTINATION_CODE_Id int indexcount2 = 0; //boxid foreach (DataRow pakRow in hawb3Table.Rows) { //newRow.HAWB_BOX_QTY += Convert.ToDouble(pakRow["PACK_ID_UNIT_QTY"]); WAYBILL_ADDITION.DESTINATION_CODERow newDestRow = wayBillAddition.DESTINATION_CODE.NewDESTINATION_CODERow(); newDestRow.DESTINATION_CODE_Id = index3; newDestRow.DEST_CODE = pakRow["DEST_CODE"].ToString(); newDestRow.DEST_CODE_EXTD_BOX_WGT = double.Parse(pakRow["DEST_CODE_EXTD_BOX_WGT"].ToString()); newDestRow.DEST_CODE_BOX_QTY = double.Parse(pakRow["DEST_CODE_BOX_QTY"].ToString()); newDestRow.SUBREGION_Id = index; wayBillAddition.DESTINATION_CODE.Rows.Add(newDestRow); /* List<string> fieldscon = new List<string>(); fieldscon.Add("Sum(DEST_CODE_BOX_QTY) as DEST_CODE_BOX_QTY"); fieldscon.Add("Sum(DEST_CODE_EXTD_BOX_WGT) as DEST_CODE_EXTD_BOX_WGT"); string whereHAWBClause_Weight = "WHERE CONSOL_INVOICE = '" + hawb3Table.Rows[index3]["CONSOL_INVOICE"] + "' and DEST_CODE='" + hawb3Table.Rows[index3]["DEST_CODE"] + "' group by CONSOL_INVOICE "; DataTable hawb3Table_Weight = DBFactory.PopulateTempTable("[v_WayBillWeight]", whereHAWBClause_Weight, fieldscon); newDestRow.DEST_CODE_EXTD_BOX_WGT = double.Parse(hawb3Table_Weight.Rows[0]["DEST_CODE_EXTD_BOX_WGT"].ToString()); newDestRow.DEST_CODE_BOX_QTY = double.Parse(hawb3Table_Weight.Rows[0]["DEST_CODE_BOX_QTY"].ToString()); newDestRow.SUBREGION_Id = index; wayBillAddition.DESTINATION_CODE.Rows.Add(newDestRow); */ //眔 DEST_CODECONSOL_INVOICE 差叭 string whereHAWB4Clause = "WHERE CONSOL_INVOICE = '" + hawb3Table.Rows[indexcount3]["CONSOL_INVOICE"] + "' and DEST_CODE='" + hawb3Table.Rows[indexcount3]["DEST_CODE"] + "' and SUB_REGION ='" + hawb3Table.Rows[indexcount3]["SUB_REGION"] + "'"; //20120801 //string whereHAWB4Clause = "WHERE C46 = '" + hawb3Table.Rows[indexcount3]["WAYBILL_NUMBER"] + "' and DEST_CODE='" + hawb3Table.Rows[indexcount3]["DEST_CODE"] + "' and SUB_REGION ='" + hawb3Table.Rows[indexcount3]["SUB_REGION"] + "'"; //20120801 DataTable hawb4Table = DBFactory.PopulateTempTable("[v_Shipment_PAKComn]", whereHAWB4Clause, fields); int indexcount4 = 0; foreach (DataRow pakRow4 in hawb4Table.Rows) { //–掸差叭boxid,weight string whereBoxClause = "WHERE InternalID='" + hawb4Table.Rows[indexcount4]["InternalID"].ToString() + "'"; DataTable boxTable = DBFactory.PopulateTempTable("[PAK_PackkingData]", whereBoxClause, fields); //string whereWeightClause = "WHERE Model='" + readRow["Model"].ToString() + "'"; DataTable weightTable = DBFactory.PopulateTempTable("[v_Shipment_PAKComnWeight]", whereBoxClause, fields); string whereDNClause = "WHERE InternalID='" + hawb4Table.Rows[indexcount4]["InternalID"].ToString() + "'"; DataTable DNTable = DBFactory.PopulateTempTable("[v_Shipment_PAKComn]", whereDNClause, fields); foreach (DataRow readRow3 in boxTable.Rows) { WAYBILL_ADDITION.BOXRow newBoxRow = wayBillAddition.BOX.NewBOXRow(); newBoxRow.BOX_ID = readRow3["BOX_ID"].ToString().Trim(); newBoxRow.BOX_WEIGHT = Convert.ToDouble(weightTable.Rows[0]["Weight"]); newBoxRow.BOX_WEIGHT_UOM = "KG";//string.Empty; newBoxRow.PACK_ID = DNTable.Rows[0]["PACK_ID"].ToString(); newBoxRow.HP_SO = DNTable.Rows[0]["CUSTOMER_ID"].ToString(); newBoxRow.DUTY_CODE = DNTable.Rows[0]["DUTY_CODE"].ToString(); newBoxRow.DESTINATION_CODE_Id = index3; newBoxRow.BOX_Id_0 = index2; wayBillAddition.BOX.Rows.Add(newBoxRow); // DataSet ds = LoadTwoD(internalID);------------------------encoding--------------------- /* if (Type == "Y") { string whereTwoDW = "'" + id + "','" + readRow3["BOX_ID"].ToString() + "'"; DataTable TwoDWTable = DBFactory.PopulateTempTable_BySp("[op_TwoDCode_Solution_waybill]", whereTwoDW); string EnCoding = TwoDWTable.Rows[0]["EnCoding"].ToString(); // Y/N string EnCoder = TwoDWTable.Rows[0]["EnCoder"].ToString(); //PDF417/MaxICode string StringIDValue = ""; if (EnCoding == "Y") { //encoder if (EnCoder == "PDF417") { //PDF417 generator //StringIDValue = TransferCode.Encoder(TwoDTable.Rows[0]["StringIDValue"].ToString()); foreach (DataRow readRowtd in TwoDWTable.Rows) { WAYBILL_ADDITION.UDF_BOXRow newRowtwod = wayBillAddition.UDF_BOX.NewUDF_BOXRow(); StringIDValue = TransferCode.Encoder(readRowtd["StringIDValue"].ToString()); newRowtwod.KEY = readRowtd["StringIDKey"].ToString(); newRowtwod.VALUE = StringIDValue.ToString(); newRowtwod.BOX_Id_0 = index2; wayBillAddition.UDF_BOX.Rows.Add(newRowtwod); } } else //MaxICode { //MaxICode generator } } else { foreach (DataRow readRowtd in TwoDWTable.Rows) { WAYBILL_ADDITION.UDF_BOXRow newRowtwod = wayBillAddition.UDF_BOX.NewUDF_BOXRow(); newRowtwod.KEY = readRowtd["StringIDKey"].ToString(); newRowtwod.VALUE = readRowtd["StringIDValue"].ToString(); wayBillAddition.UDF_BOX.Rows.Add(newRowtwod); newRowtwod.BOX_Id_0 = index2; } } }*/ //----------------------- end -------------------------------------------------------------- index2++; indexcount2++; } index4++; indexcount4++; } index3++; indexcount3++; } index++; } this.m_DataSet = wayBillAddition; } //else if (Consolidate.Trim() == "") //modify by 20120801 else if (WAYBILL_NUMBER.Trim() == "") { string whereMainClause = "WHERE InternalID='" + id + "'"; List<string> fields = new List<string>(); fields.Add("*"); DataTable packIDTable = DBFactory.PopulateTempTable("[v_Shipment_PAKComn]", whereMainClause, fields); int index = 0; foreach (DataRow readRow in packIDTable.Rows) { WAYBILL_ADDITION.SUBREGIONRow newRow = wayBillAddition.SUBREGION.NewSUBREGIONRow(); index++; newRow.SUBREGION_Id = index; foreach (DataColumn column in wayBillAddition.SUBREGION.Columns) { if (packIDTable.Columns.Contains(column.ColumnName) && (column.ColumnName != "BOX_UNIT_QTY")) { if (column.DataType != packIDTable.Columns[column.ColumnName].DataType) { if (column.DataType == typeof(Double)) { newRow[column] = Convert.ToDouble(readRow[column.ColumnName]); } // else if (column.DataType == typeof(String)) // { // newRow[column] = Convert.ToString(readRow[column.ColumnName]); // } } else newRow[column] = readRow[column.ColumnName]; } else { if (column.DataType == typeof(string)) newRow[column] = string.Empty; else if (column.DataType == typeof(Double)) newRow[column] = 0; else if (column.DataType == typeof(DateTime)) newRow[column] = string.Empty; } } //HAWB_ACT_WEIGHT /* string whereHAWBClause_ACT = "WHERE Shipment='" + id.Substring(0, 10) + "'"; DataTable hawbTable_ACT = DBFactory.PopulateTempTable("[PAK_ShipmentWeight_FIS]", whereHAWBClause_ACT, fields); newRow.HAWB_ACT_WEIGHT = double.Parse(hawbTable_ACT.Rows[0]["Weight"].ToString());*/ string whereHAWBClause_ACT = "'" + id.Substring(0, 10) + "','2'"; DataTable hawbTable_ACT = DBFactory.PopulateTempTable_BySp("[op_PAK_ShipmentWeight_FIS]", whereHAWBClause_ACT); newRow.HAWB_ACT_WEIGHT = double.Parse(hawbTable_ACT.Rows[0]["Weight"].ToString()); //HAWB_BOX_QTY List<string> fields2 = new List<string>(); fields2.Add("Qty as DEST_CODE_BOX_QTY"); string whereHAWBClause = "WHERE Consolidate='" + id.Substring(0, 10) + "'"; DataTable hawbTable = DBFactory.PopulateTempTable("[P_PoPlt]", whereHAWBClause, fields2); //add up PACK_ID_UNIT_QTY newRow.HAWB_BOX_QTY = double.Parse(hawbTable.Rows[0]["DEST_CODE_BOX_QTY"].ToString()); //HAWB_PALLET_QTY List<string> fieldspallet = new List<string>(); fieldspallet.Add("distinct PLT as PALLET_ID"); string whereHAWB2Clause = "WHERE Consolidate = '" + id.Substring(0, 10) + "'"; DataTable hawb2Table = DBFactory.PopulateTempTable("[P_PoPlt]", whereHAWB2Clause, fieldspallet); newRow.HAWB_PALLET_QTY = hawb2Table.Rows.Count; //CUR DATA newRow.CURRENT_DATE = DateTime.Today.ToString("yyyy-MM-dd"); //newRow.CURRENT_DATE = DateTime.Today; //newRow.ACTUAL_SHIPDATE = Convert.ToDateTime(packIDTable.Rows[0]["ACTUAL_SHIPDATE"]); wayBillAddition.SUBREGION.Rows.Add(newRow); // DEST_CODECONSOL_INVOICE 耞碻吏Ω计 DEST_CODECONSOL_INVOICE List<string> fields1 = new List<string>(); fields1.Add("DEST_CODE"); fields1.Add("CONSOL_INVOICE"); string whereHAWB3Clause = "WHERE InternalID = '" + id + "'"; DataTable hawb3Table = DBFactory.PopulateTempTable("[v_Shipment_PAKComn]", whereHAWB3Clause, fields1); int index3 = 0; //DESTINATION_CODE_Id int index2 = 0; //boxid foreach (DataRow pakRow in hawb3Table.Rows) { //newRow.HAWB_BOX_QTY += Convert.ToDouble(pakRow["PACK_ID_UNIT_QTY"]); WAYBILL_ADDITION.DESTINATION_CODERow newDestRow = wayBillAddition.DESTINATION_CODE.NewDESTINATION_CODERow(); newDestRow.DESTINATION_CODE_Id = index3; newDestRow.DEST_CODE = pakRow["DEST_CODE"].ToString(); newDestRow.DEST_CODE_EXTD_BOX_WGT = double.Parse(hawbTable_ACT.Rows[0]["Weight"].ToString()); newDestRow.DEST_CODE_BOX_QTY = double.Parse(hawbTable.Rows[0]["DEST_CODE_BOX_QTY"].ToString()); newDestRow.SUBREGION_Id = index; wayBillAddition.DESTINATION_CODE.Rows.Add(newDestRow); int index4 = 0; //–掸差叭boxid,weight string whereBoxClause = "WHERE InternalID='" + id + "'"; DataTable boxTable = DBFactory.PopulateTempTable("[PAK_PackkingData]", whereBoxClause, fields); //string whereWeightClause = "WHERE Model='" + readRow["Model"].ToString() + "'"; DataTable weightTable = DBFactory.PopulateTempTable("[v_Shipment_PAKComnWeight]", whereBoxClause, fields); string whereDNClause = "WHERE InternalID='" + id + "'"; DataTable DNTable = DBFactory.PopulateTempTable("[v_Shipment_PAKComn]", whereDNClause, fields); foreach (DataRow readRow3 in boxTable.Rows) { WAYBILL_ADDITION.BOXRow newBoxRow = wayBillAddition.BOX.NewBOXRow(); newBoxRow.BOX_ID = readRow3["BOX_ID"].ToString().Trim(); newBoxRow.BOX_WEIGHT = Convert.ToDouble(weightTable.Rows[0]["Weight"]); newBoxRow.BOX_WEIGHT_UOM = string.Empty; newBoxRow.PACK_ID = DNTable.Rows[0]["PACK_ID"].ToString(); newBoxRow.HP_SO = DNTable.Rows[0]["CUSTOMER_ID"].ToString(); newBoxRow.DUTY_CODE = DNTable.Rows[0]["DUTY_CODE"].ToString(); newBoxRow.DESTINATION_CODE_Id = index3; newBoxRow.BOX_Id_0 = index2; index2++; wayBillAddition.BOX.Rows.Add(newBoxRow); //get line item string whereLneItemClause = "WHERE PO_NUM='" + readRow["PO_NUM"].ToString() + "'"; DataTable lineItemTable = DBFactory.PopulateTempTable("[PAK.PAKEdi850raw]", whereLneItemClause, fields); foreach (DataRow readRow2 in lineItemTable.Rows) { LoadKeyValuePairs(wayBillAddition.UDF_HEADER, readRow2, "UDF_KEY_HEADER", "UDF_VALUE_HEADER", "BOX_Id_0", newBoxRow.BOX_Id_0); LoadKeyValuePairs(wayBillAddition.UDF_DETAIL, readRow2, "UDF_KEY_DETAIL", "UDF_VALUE_DETAIL", "BOX_Id_0", newBoxRow.BOX_Id_0); } // DataSet ds = LoadTwoD(internalID);------------------------encoding--------------------- /* string whereTwoDW = "'" + id + "','"+readRow3["BOX_ID"].ToString()+"'"; DataTable TwoDWTable = DBFactory.PopulateTempTable_BySp("[op_TwoDCode_Solution_waybill]", whereTwoDW); string EnCoding = TwoDWTable.Rows[0]["EnCoding"].ToString(); // Y/N string EnCoder = TwoDWTable.Rows[0]["EnCoder"].ToString(); //PDF417/MaxICode string StringIDValue = ""; if (EnCoding == "Y") { //encoder if (EnCoder == "PDF417") { //PDF417 generator //StringIDValue = TransferCode.Encoder(TwoDTable.Rows[0]["StringIDValue"].ToString()); foreach (DataRow readRowtd in TwoDWTable.Rows) { WAYBILL_ADDITION.UDF_BOXRow newRowtwod = wayBillAddition.UDF_BOX.NewUDF_BOXRow(); StringIDValue = TransferCode.Encoder(readRowtd["StringIDValue"].ToString()); newRowtwod.KEY = readRowtd["StringIDKey"].ToString(); newRowtwod.VALUE = StringIDValue.ToString(); wayBillAddition.UDF_BOX.Rows.Add(newRowtwod); } } else //MaxICode { //MaxICode generator } } else { foreach (DataRow readRowtd in TwoDWTable.Rows) { WAYBILL_ADDITION.UDF_BOXRow newRowtwod = wayBillAddition.UDF_BOX.NewUDF_BOXRow(); newRowtwod.KEY = readRowtd["StringIDKey"].ToString(); newRowtwod.VALUE = readRowtd["StringIDValue"].ToString(); wayBillAddition.UDF_BOX.Rows.Add(newRowtwod); } } */ //----------------------- end -------------------------------------------------------------- } index4++; index3++; } } //populate mini tables //set this dataset to packinglist so we can outputXML this.m_DataSet = wayBillAddition; } }
public void LoadWayBillDatabaseData(string id) { logger.Debug("LoadWayBillDatabaseData begin id="+id); //--------------------- add by fan 20100107 ---------- /* string Type = ""; string whereTDcheck = "'" + id + "','1'"; DataTable TDcheckTable = DBFactory.PopulateTempTable_BySp("[op_TwoDCode_Check]", whereTDcheck); Type = TDcheckTable.Rows[0][0].ToString().Trim();*/ //-------------------- end ----------------------------- string Consolidate = ""; //fill boxList table WAYBILL_ADDITION wayBillAddition = new WAYBILL_ADDITION(); /* string whereConsolidateClause = "WHERE InternalID='" + id + "'"; List<string> fields11 = new List<string>(); fields11.Add("*"); DataTable ConsolidateTable = DBFactory.PopulateTempTable("[v_Shipment_PAKComn]", whereConsolidateClause, fields11); */ logger.Debug("LoadWayBillDatabaseData [v_Shipment_PAKComn] begin"); DataTable ConsolidateTable = EditsSqlProc.v_Shipment_PAKComn__by__InternalID(id); logger.Debug("LoadWayBillDatabaseData [v_Shipment_PAKComn] end"); ChkRowNotNull("WayBillShipmentXmlCreator id=" + id + ", v_Shipment_PAKComn internalID=" + id, ConsolidateTable); Consolidate = ConsolidateTable.Rows[0]["CONSOL_INVOICE"].ToString().Trim(); if (Consolidate.Trim() != "") { string HAWB = ConsolidateTable.Rows[0]["C46"].ToString().Trim(); List<string> fields = new List<string>(); fields.Add("*"); //DISTINCT CONSOL_INVOICE,SUB_REGION,WAYBILL_NUMBER,INTL_CARRIER,ACTUAL_SHIPDATE /* List<string> fieldssubregion = new List<string>(); fieldssubregion.Add("distinct SUB_REGION"); fieldssubregion.Add("MASTER_WAYBILL_NUMBER"); fieldssubregion.Add("WAYBILL_NUMBER"); fieldssubregion.Add("PREF_GATEWAY"); //fieldssubregion.Add("DUTY_CODE"); // mark by Shao 20120730 fieldssubregion.Add("INTL_CARRIER"); fieldssubregion.Add("ACTUAL_SHIPDATE"); //string whereMainClause = "WHERE CONSOL_INVOICE='" + Consolidate + "'"; //HP Jennifer reuest the scanning list should calculate by the HAWB,instead of the consolidate string whereMainClause = "WHERE C46='" + HAWB + "'"; //modified by Shao 20120730 DataTable packIDTable = DBFactory.PopulateTempTable("[v_Shipment_WayBillRegion]", whereMainClause, fieldssubregion); */ logger.Debug("LoadWayBillDatabaseData [v_Shipment_WayBillRegion] begin"); DataTable packIDTable = EditsSqlProc.SUB_REGION__MASTER_WAYBILL_NUMBER__WAYBILL_NUMBER__PREF_GATEWAY__INTL_CARRIER__ACTUAL_SHIPDATE__from__v_Shipment_WayBillRegion__by__C46(HAWB); logger.Debug("LoadWayBillDatabaseData [v_Shipment_WayBillRegion] end"); // DataTable packIDTable = DBFactory.PopulateTempTable("[v_Shipment_WayBillList]", whereMainClause, fieldssubregion); int index = 0; int index3 = 0; int index2 = 0; int index4 = 0; foreach (DataRow readRow in packIDTable.Rows) { WAYBILL_ADDITION.SUBREGIONRow newRow = wayBillAddition.SUBREGION.NewSUBREGIONRow(); newRow.SUBREGION_Id = index; foreach (DataColumn column in wayBillAddition.SUBREGION.Columns) { if (packIDTable.Columns.Contains(column.ColumnName) && (column.ColumnName != "BOX_UNIT_QTY")) { //if table contains name, then populate if (column.DataType != packIDTable.Columns[column.ColumnName].DataType) { if (column.DataType == typeof(Double)) { newRow[column] = Convert.ToDouble(readRow[column.ColumnName]); } // else if (column.DataType == typeof(String)) // { // newRow[column] = Convert.ToString(readRow[column.ColumnName]); // } } else newRow[column] = readRow[column.ColumnName]; } else { if (column.DataType == typeof(string)) newRow[column] = string.Empty; else if (column.DataType == typeof(Double)) newRow[column] = 0; // else if (column.DataType == typeof(DateTime)) // newRow[column] = string.Empty; } } /* string whereHAWBClause_ACT = "'" + HAWB + "','3'"; DataTable hawbTable_ACT = DBFactory.PopulateTempTable_BySp("[op_PAK_ShipmentWeight_FIS]", whereHAWBClause_ACT); */ logger.Debug("LoadWayBillDatabaseData [op_PAK_ShipmentWeight_FIS] begin"); DataTable hawbTable_ACT = EditsSqlProc.sp__op_PAK_ShipmentWeight_FIS(HAWB, "3"); logger.Debug("LoadWayBillDatabaseData [op_PAK_ShipmentWeight_FIS] end"); ChkRowNotNull("WayBillShipmentXmlCreator id=" + id + ", op_PAK_ShipmentWeight_FIS shipment=" + HAWB + ", tp=3", hawbTable_ACT); newRow.HAWB_ACT_WEIGHT = double.Parse(hawbTable_ACT.Rows[0]["Weight"].ToString()); //HAWB_ACT_WEIGHT /*string whereHAWBClause_ACT = "WHERE Shipment='" + readRow["CONSOL_INVOICE"].ToString() + "'"; DataTable hawbTable_ACT = DBFactory.PopulateTempTable("[PAK_ShipmentWeight_FIS]", whereHAWBClause_ACT, fields); newRow.HAWB_ACT_WEIGHT = double.Parse(hawbTable_ACT.Rows[0]["Weight"].ToString());*/ //HAWB_BOX_QTY //string whereHAWBClause = "WHERE WAYBILL_NUMBER='" + readRow["WAYBILL_NUMBER"].ToString() + "'"; //DataTable hawbTable = DBFactory.PopulateTempTable("[v_PAKComn]", whereHAWBClause, fields); /* List<string> fields2 = new List<string>(); fields2.Add("sum(DEST_CODE_BOX_QTY) as DEST_CODE_BOX_QTY"); string whereHAWBClause = "WHERE C46='" + HAWB + "'"; DataTable hawbTable = DBFactory.PopulateTempTable("[v_Shipment_WayBillList]", whereHAWBClause, fields2); */ logger.Debug("LoadWayBillDatabaseData [v_Shipment_WayBillList] begin"); DataTable hawbTable = EditsSqlProc.sum_DEST_CODE_BOX_QTY__from__v_Shipment_WayBillList__by__C46(HAWB); logger.Debug("LoadWayBillDatabaseData [v_Shipment_WayBillList] end"); ChkRowNotNull("WayBillShipmentXmlCreator id=" + id + ", v_Shipment_WayBillList C46=" + HAWB, hawbTable); //add up PACK_ID_UNIT_QTY newRow.HAWB_BOX_QTY = double.Parse(hawbTable.Rows[0]["DEST_CODE_BOX_QTY"].ToString()); //SELECT distinct PALLET_ID from [PAK.PAKPaltno] WHERE InternalID in (select InternalID from PAK_PAKComn WHERE C46='SHA-62910465') //HAWB_PALLET_QTY /* List<string> fieldspallet = new List<string>(); fieldspallet.Add("distinct PALLET_ID"); string whereHAWB2Clause = "WHERE InternalID in (select InternalID from PAK_PAKComn WHERE C46= '" + HAWB + "')"; DataTable hawb2Table = DBFactory.PopulateTempTable("[PAK.PAKPaltno]", whereHAWB2Clause, fieldspallet); */ logger.Debug("LoadWayBillDatabaseData [PAK.PAKPaltno] begin"); DataTable hawb2Table = EditsSqlProc.PALLET_ID__from__PAK_PAKPaltno__by__C46(HAWB); logger.Debug("LoadWayBillDatabaseData [PAK.PAKPaltno] end"); newRow.HAWB_PALLET_QTY = hawb2Table.Rows.Count; //CUR DATA DateTime T1 = DateTime.Today; //newRow.CURRENT_DATE = DateTime.Today;// ----modeify 20090911 newRow.CURRENT_DATE = DateTime.Today.ToString("yyyy-MM-dd"); //newRow.CURRENT_DATE = DateTime.Today; //newRow.ACTUAL_SHIPDATE = Convert.ToDateTime(packIDTable.Rows[0]["ACTUAL_SHIPDATE"]); // (Convert.ToDateTime(dr["Date"])).ToString(" wayBillAddition.SUBREGION.Rows.Add(newRow); // 取DEST_CODE,CONSOL_INVOICE 判斷循環次數 同一DEST_CODE,CONSOL_INVOICE /* List<string> fields1 = new List<string>(); fields1.Add("*"); //string whereHAWB3Clause = "WHERE InternalID in (SELECT InternalID FROM [v_PAKComn] " + whereHAWBClause + ") Group by " + fields1[0].ToString() + "," + fields1[1].ToString(); string whereHAWB3Clause = "WHERE C46 = '" + HAWB + "' and SUB_REGION = '" + packIDTable.Rows[index]["SUB_REGION"] + "'"; DataTable hawb3Table = DBFactory.PopulateTempTable("[v_Shipment_WayBillDest]", whereHAWB3Clause, fields1); */ logger.Debug("LoadWayBillDatabaseData [v_Shipment_WayBillDest] begin"); DataTable hawb3Table = EditsSqlProc.v_Shipment_WayBillDest__by__C46__SUB_REGION(HAWB, packIDTable.Rows[index]["SUB_REGION"].ToString()); logger.Debug("LoadWayBillDatabaseData [v_Shipment_WayBillDest] end"); int indexcount3 = 0; //DESTINATION_CODE_Id int indexcount2 = 0; //boxid foreach (DataRow pakRow in hawb3Table.Rows) { //newRow.HAWB_BOX_QTY += Convert.ToDouble(pakRow["PACK_ID_UNIT_QTY"]); WAYBILL_ADDITION.DESTINATION_CODERow newDestRow = wayBillAddition.DESTINATION_CODE.NewDESTINATION_CODERow(); newDestRow.DESTINATION_CODE_Id = index3; newDestRow.DEST_CODE = pakRow["DEST_CODE"].ToString(); newDestRow.DEST_CODE_EXTD_BOX_WGT = double.Parse(pakRow["DEST_CODE_EXTD_BOX_WGT"].ToString()); newDestRow.DEST_CODE_BOX_QTY = double.Parse(pakRow["DEST_CODE_BOX_QTY"].ToString()); newDestRow.SUBREGION_Id = index; wayBillAddition.DESTINATION_CODE.Rows.Add(newDestRow); /* List<string> fieldscon = new List<string>(); fieldscon.Add("Sum(DEST_CODE_BOX_QTY) as DEST_CODE_BOX_QTY"); fieldscon.Add("Sum(DEST_CODE_EXTD_BOX_WGT) as DEST_CODE_EXTD_BOX_WGT"); string whereHAWBClause_Weight = "WHERE CONSOL_INVOICE = '" + hawb3Table.Rows[index3]["CONSOL_INVOICE"] + "' and DEST_CODE='" + hawb3Table.Rows[index3]["DEST_CODE"] + "' group by CONSOL_INVOICE "; DataTable hawb3Table_Weight = DBFactory.PopulateTempTable("[v_WayBillWeight]", whereHAWBClause_Weight, fieldscon); newDestRow.DEST_CODE_EXTD_BOX_WGT = double.Parse(hawb3Table_Weight.Rows[0]["DEST_CODE_EXTD_BOX_WGT"].ToString()); newDestRow.DEST_CODE_BOX_QTY = double.Parse(hawb3Table_Weight.Rows[0]["DEST_CODE_BOX_QTY"].ToString()); newDestRow.SUBREGION_Id = index; wayBillAddition.DESTINATION_CODE.Rows.Add(newDestRow); */ //取得 同一DEST_CODE,CONSOL_INVOICE 的船務 /* string whereHAWB4Clause = "WHERE C46 = '" + HAWB + "' and DEST_CODE='" + hawb3Table.Rows[indexcount3]["DEST_CODE"] + "' and SUB_REGION ='" + hawb3Table.Rows[indexcount3]["SUB_REGION"] + "'"; DataTable hawb4Table = DBFactory.PopulateTempTable("[v_Shipment_PAKComn]", whereHAWB4Clause, fields); */ logger.Debug("LoadWayBillDatabaseData [v_Shipment_PAKComn] begin"); DataTable hawb4Table = EditsSqlProc.v_Shipment_PAKComn__by__C46__DEST_CODE__SUB_REGION(HAWB, hawb3Table.Rows[indexcount3]["DEST_CODE"].ToString(), hawb3Table.Rows[indexcount3]["SUB_REGION"].ToString()); logger.Debug("LoadWayBillDatabaseData [v_Shipment_PAKComn] end"); int indexcount4 = 0; foreach (DataRow pakRow4 in hawb4Table.Rows) { //每筆船務的boxid,weight /* string whereBoxClause = "WHERE InternalID='" + hawb4Table.Rows[indexcount4]["InternalID"].ToString() + "'"; DataTable boxTable = DBFactory.PopulateTempTable("[PAK_PackkingData]", whereBoxClause, fields); */ logger.Debug("LoadWayBillDatabaseData [PAK_PackkingData] begin"); DataTable boxTable = EditsSqlProc.PAK_PackkingData__by__InternalID(hawb4Table.Rows[indexcount4]["InternalID"].ToString()); logger.Debug("LoadWayBillDatabaseData [PAK_PackkingData] end"); //string whereWeightClause = "WHERE Model='" + readRow["Model"].ToString() + "'"; /* DataTable weightTable = DBFactory.PopulateTempTable("[v_Shipment_PAKComnWeight]", whereBoxClause, fields); */ logger.Debug("LoadWayBillDatabaseData [v_Shipment_PAKComnWeight] begin"); DataTable weightTable = EditsSqlProc.v_Shipment_PAKComnWeight__by__InternalID(hawb4Table.Rows[indexcount4]["InternalID"].ToString()); logger.Debug("LoadWayBillDatabaseData [v_Shipment_PAKComnWeight] end"); //ChkRowNotNull("WayBillShipmentXmlCreator id=" + id + ", v_Shipment_PAKComnWeight", weightTable); /* string whereDNClause = "WHERE InternalID='" + hawb4Table.Rows[indexcount4]["InternalID"].ToString() + "'"; DataTable DNTable = DBFactory.PopulateTempTable("[v_Shipment_PAKComn]", whereDNClause, fields); */ logger.Debug("LoadWayBillDatabaseData [v_Shipment_PAKComn] begin"); DataTable DNTable = EditsSqlProc.v_Shipment_PAKComn__by__InternalID(hawb4Table.Rows[indexcount4]["InternalID"].ToString()); logger.Debug("LoadWayBillDatabaseData [v_Shipment_PAKComn] end"); //ChkRowNotNull("WayBillShipmentXmlCreator id=" + id + ", v_Shipment_PAKComn", DNTable); if (boxTable.Rows.Count > 0) { ChkRowNotNull("WayBillShipmentXmlCreator id=" + id + ", v_PAKComnWeight internalID=" + hawb4Table.Rows[indexcount4]["InternalID"].ToString(), weightTable); ChkRowNotNull("WayBillShipmentXmlCreator id=" + id + ", v_PAKComn internalID=" + hawb4Table.Rows[indexcount4]["InternalID"].ToString(), DNTable); } foreach (DataRow readRow3 in boxTable.Rows) { WAYBILL_ADDITION.BOXRow newBoxRow = wayBillAddition.BOX.NewBOXRow(); newBoxRow.BOX_ID = readRow3["BOX_ID"].ToString().Trim(); newBoxRow.BOX_WEIGHT = Convert.ToDouble(weightTable.Rows[0]["Weight"]); newBoxRow.BOX_WEIGHT_UOM = "KG";//string.Empty; newBoxRow.PACK_ID = DNTable.Rows[0]["PACK_ID"].ToString(); newBoxRow.HP_SO = DNTable.Rows[0]["CUSTOMER_ID"].ToString(); //ChkRowNotNull("WayBillShipmentXmlCreator id=" + id + ", v_Shipment_PAKComn", hawb4Table); newBoxRow.DUTY_CODE = hawb4Table.Rows[0]["DUTY_CODE"].ToString(); newBoxRow.DESTINATION_CODE_Id = index3; newBoxRow.BOX_Id_0 = index2; wayBillAddition.BOX.Rows.Add(newBoxRow); // DataSet ds = LoadTwoD(internalID);------------------------encoding--------------------- /* if (Type == "Y") { string whereTwoDW = "'" + id + "','" + readRow3["BOX_ID"].ToString() + "'"; DataTable TwoDWTable = DBFactory.PopulateTempTable_BySp("[op_TwoDCode_Solution_waybill]", whereTwoDW); string EnCoding = TwoDWTable.Rows[0]["EnCoding"].ToString(); // Y/N string EnCoder = TwoDWTable.Rows[0]["EnCoder"].ToString(); //PDF417/MaxICode string StringIDValue = ""; if (EnCoding == "Y") { //encoder if (EnCoder == "PDF417") { //PDF417 generator //StringIDValue = TransferCode.Encoder(TwoDTable.Rows[0]["StringIDValue"].ToString()); foreach (DataRow readRowtd in TwoDWTable.Rows) { WAYBILL_ADDITION.UDF_BOXRow newRowtwod = wayBillAddition.UDF_BOX.NewUDF_BOXRow(); StringIDValue = TransferCode.Encoder(readRowtd["StringIDValue"].ToString()); newRowtwod.KEY = readRowtd["StringIDKey"].ToString(); newRowtwod.VALUE = StringIDValue.ToString(); newRowtwod.BOX_Id_0 = index2; wayBillAddition.UDF_BOX.Rows.Add(newRowtwod); } } else //MaxICode { //MaxICode generator } } else { foreach (DataRow readRowtd in TwoDWTable.Rows) { WAYBILL_ADDITION.UDF_BOXRow newRowtwod = wayBillAddition.UDF_BOX.NewUDF_BOXRow(); newRowtwod.KEY = readRowtd["StringIDKey"].ToString(); newRowtwod.VALUE = readRowtd["StringIDValue"].ToString(); wayBillAddition.UDF_BOX.Rows.Add(newRowtwod); newRowtwod.BOX_Id_0 = index2; } } }*/ //----------------------- end -------------------------------------------------------------- index2++; indexcount2++; } index4++; indexcount4++; } index3++; indexcount3++; } index++; } this.m_DataSet = wayBillAddition; } else if (Consolidate.Trim() == "") { /* string whereMainClause = "WHERE InternalID='" + id + "'"; List<string> fields = new List<string>(); fields.Add("*"); DataTable packIDTable = DBFactory.PopulateTempTable("[v_Shipment_PAKComn]", whereMainClause, fields); */ logger.Debug("LoadWayBillDatabaseData [v_Shipment_PAKComn] begin"); DataTable packIDTable = EditsSqlProc.v_Shipment_PAKComn__by__InternalID(id); logger.Debug("LoadWayBillDatabaseData [v_Shipment_PAKComn] end"); int index = 0; foreach (DataRow readRow in packIDTable.Rows) { WAYBILL_ADDITION.SUBREGIONRow newRow = wayBillAddition.SUBREGION.NewSUBREGIONRow(); index++; newRow.SUBREGION_Id = index; foreach (DataColumn column in wayBillAddition.SUBREGION.Columns) { if (packIDTable.Columns.Contains(column.ColumnName) && (column.ColumnName != "BOX_UNIT_QTY")) { if (column.DataType != packIDTable.Columns[column.ColumnName].DataType) { if (column.DataType == typeof(Double)) { newRow[column] = Convert.ToDouble(readRow[column.ColumnName]); } // else if (column.DataType == typeof(String)) // { // newRow[column] = Convert.ToString(readRow[column.ColumnName]); // } } else newRow[column] = readRow[column.ColumnName]; } else { if (column.DataType == typeof(string)) newRow[column] = string.Empty; else if (column.DataType == typeof(Double)) newRow[column] = 0; else if (column.DataType == typeof(DateTime)) newRow[column] = string.Empty; } } //HAWB_ACT_WEIGHT /* string whereHAWBClause_ACT = "WHERE Shipment='" + id.Substring(0, 10) + "'"; DataTable hawbTable_ACT = DBFactory.PopulateTempTable("[PAK_ShipmentWeight_FIS]", whereHAWBClause_ACT, fields); newRow.HAWB_ACT_WEIGHT = double.Parse(hawbTable_ACT.Rows[0]["Weight"].ToString());*/ /* string whereHAWBClause_ACT = "'" + id.Substring(0, 10) + "','2'"; DataTable hawbTable_ACT = DBFactory.PopulateTempTable_BySp("[op_PAK_ShipmentWeight_FIS]", whereHAWBClause_ACT); */ logger.Debug("LoadWayBillDatabaseData [op_PAK_ShipmentWeight_FIS] begin"); DataTable hawbTable_ACT = EditsSqlProc.sp__op_PAK_ShipmentWeight_FIS(id.Substring(0, 10), "2"); logger.Debug("LoadWayBillDatabaseData [op_PAK_ShipmentWeight_FIS] end"); ChkRowNotNull("WayBillShipmentXmlCreator id=" + id + ", op_PAK_ShipmentWeight_FIS shipment=" + id.Substring(0, 10) + ", tp=2", hawbTable_ACT); newRow.HAWB_ACT_WEIGHT = double.Parse(hawbTable_ACT.Rows[0]["Weight"].ToString()); //HAWB_BOX_QTY /* List<string> fields2 = new List<string>(); fields2.Add("Qty as DEST_CODE_BOX_QTY"); string whereHAWBClause = "WHERE Consolidate='" + id.Substring(0, 10) + "'"; DataTable hawbTable = DBFactory.PopulateTempTable("[P_PoPlt]", whereHAWBClause, fields2); */ logger.Debug("LoadWayBillDatabaseData [P_PoPlt] begin"); DataTable hawbTable = EditsSqlProc.Qty__from__P_PoPlt__by__Consolidate(id.Substring(0, 10)); logger.Debug("LoadWayBillDatabaseData [P_PoPlt] end"); //add up PACK_ID_UNIT_QTY ChkRowNotNull("WayBillShipmentXmlCreator id=" + id + ", P_PoPlt Consolidate=" + id.Substring(0, 10), hawbTable); newRow.HAWB_BOX_QTY = double.Parse(hawbTable.Rows[0]["DEST_CODE_BOX_QTY"].ToString()); //HAWB_PALLET_QTY /* List<string> fieldspallet = new List<string>(); fieldspallet.Add("distinct PLT as PALLET_ID"); string whereHAWB2Clause = "WHERE Consolidate = '" + id.Substring(0, 10) + "'"; DataTable hawb2Table = DBFactory.PopulateTempTable("[P_PoPlt]", whereHAWB2Clause, fieldspallet); */ logger.Debug("LoadWayBillDatabaseData [P_PoPlt] begin"); DataTable hawb2Table = EditsSqlProc.PLT__from__P_PoPlt__by__Consolidate(id.Substring(0, 10)); logger.Debug("LoadWayBillDatabaseData [P_PoPlt] end"); newRow.HAWB_PALLET_QTY = hawb2Table.Rows.Count; //CUR DATA newRow.CURRENT_DATE = DateTime.Today.ToString("yyyy-MM-dd"); //newRow.CURRENT_DATE = DateTime.Today; //newRow.ACTUAL_SHIPDATE = Convert.ToDateTime(packIDTable.Rows[0]["ACTUAL_SHIPDATE"]); wayBillAddition.SUBREGION.Rows.Add(newRow); // 取DEST_CODE,CONSOL_INVOICE 判斷循環次數 同一DEST_CODE,CONSOL_INVOICE /* List<string> fields1 = new List<string>(); fields1.Add("DEST_CODE"); fields1.Add("CONSOL_INVOICE"); string whereHAWB3Clause = "WHERE InternalID = '" + id + "'"; DataTable hawb3Table = DBFactory.PopulateTempTable("[v_Shipment_PAKComn]", whereHAWB3Clause, fields1); */ logger.Debug("LoadWayBillDatabaseData [v_Shipment_PAKComn] begin"); DataTable hawb3Table = EditsSqlProc.DEST_CODE__CONSOL_INVOICE__from__v_Shipment_PAKComn__by__InternalID(id); logger.Debug("LoadWayBillDatabaseData [v_Shipment_PAKComn] end"); int index3 = 0; //DESTINATION_CODE_Id int index2 = 0; //boxid foreach (DataRow pakRow in hawb3Table.Rows) { //newRow.HAWB_BOX_QTY += Convert.ToDouble(pakRow["PACK_ID_UNIT_QTY"]); WAYBILL_ADDITION.DESTINATION_CODERow newDestRow = wayBillAddition.DESTINATION_CODE.NewDESTINATION_CODERow(); newDestRow.DESTINATION_CODE_Id = index3; newDestRow.DEST_CODE = pakRow["DEST_CODE"].ToString(); newDestRow.DEST_CODE_EXTD_BOX_WGT = double.Parse(hawbTable_ACT.Rows[0]["Weight"].ToString()); newDestRow.DEST_CODE_BOX_QTY = double.Parse(hawbTable.Rows[0]["DEST_CODE_BOX_QTY"].ToString()); newDestRow.SUBREGION_Id = index; wayBillAddition.DESTINATION_CODE.Rows.Add(newDestRow); int index4 = 0; //每筆船務的boxid,weight /* string whereBoxClause = "WHERE InternalID='" + id + "'"; DataTable boxTable = DBFactory.PopulateTempTable("[PAK_PackkingData]", whereBoxClause, fields); */ logger.Debug("LoadWayBillDatabaseData [PAK_PackkingData] begin"); DataTable boxTable = EditsSqlProc.PAK_PackkingData__by__InternalID(id); logger.Debug("LoadWayBillDatabaseData [PAK_PackkingData] end"); //string whereWeightClause = "WHERE Model='" + readRow["Model"].ToString() + "'"; /* DataTable weightTable = DBFactory.PopulateTempTable("[v_Shipment_PAKComnWeight]", whereBoxClause, fields); */ logger.Debug("LoadWayBillDatabaseData [v_Shipment_PAKComnWeight] begin"); DataTable weightTable = EditsSqlProc.v_Shipment_PAKComnWeight__by__InternalID(id); logger.Debug("LoadWayBillDatabaseData [v_Shipment_PAKComnWeight] end"); string whereDNClause = "WHERE InternalID='" + id + "'"; /* DataTable DNTable = DBFactory.PopulateTempTable("[v_Shipment_PAKComn]", whereDNClause, fields); */ logger.Debug("LoadWayBillDatabaseData [v_Shipment_PAKComn] begin"); DataTable DNTable = EditsSqlProc.v_Shipment_PAKComn__by__InternalID(id); logger.Debug("LoadWayBillDatabaseData [v_Shipment_PAKComn] end"); ChkRowNotNull("WayBillShipmentXmlCreator id=" + id + ", v_Shipment_PAKComn internalID=" + id, DNTable); if (boxTable.Rows.Count > 0) { ChkRowNotNull("WayBillShipmentXmlCreator id=" + id + ", v_Shipment_PAKComnWeight internalID=" + id, weightTable); ChkRowNotNull("WayBillShipmentXmlCreator id=" + id + ", v_Shipment_PAKComn internalID=" + id, DNTable); } foreach (DataRow readRow3 in boxTable.Rows) { WAYBILL_ADDITION.BOXRow newBoxRow = wayBillAddition.BOX.NewBOXRow(); newBoxRow.BOX_ID = readRow3["BOX_ID"].ToString().Trim(); newBoxRow.BOX_WEIGHT = Convert.ToDouble(weightTable.Rows[0]["Weight"]); newBoxRow.BOX_WEIGHT_UOM = string.Empty; newBoxRow.PACK_ID = DNTable.Rows[0]["PACK_ID"].ToString(); newBoxRow.HP_SO = DNTable.Rows[0]["CUSTOMER_ID"].ToString(); newBoxRow.DESTINATION_CODE_Id = index3; newBoxRow.BOX_Id_0 = index2; index2++; wayBillAddition.BOX.Rows.Add(newBoxRow); //get line item /* string whereLneItemClause = "WHERE PO_NUM='" + readRow["PO_NUM"].ToString() + "'"; DataTable lineItemTable = DBFactory.PopulateTempTable("[PAK.PAKEdi850raw]", whereLneItemClause, fields); */ logger.Debug("LoadWayBillDatabaseData [PAK.PAKEdi850raw] begin"); DataTable lineItemTable = EditsSqlProc.PAK_PAKEdi850raw__by__PO_NUM(readRow["PO_NUM"].ToString()); logger.Debug("LoadWayBillDatabaseData [PAK.PAKEdi850raw] end"); foreach (DataRow readRow2 in lineItemTable.Rows) { LoadKeyValuePairs(wayBillAddition.UDF_HEADER, readRow2, "UDF_KEY_HEADER", "UDF_VALUE_HEADER", "BOX_Id_0", newBoxRow.BOX_Id_0); LoadKeyValuePairs(wayBillAddition.UDF_DETAIL, readRow2, "UDF_KEY_DETAIL", "UDF_VALUE_DETAIL", "BOX_Id_0", newBoxRow.BOX_Id_0); } // DataSet ds = LoadTwoD(internalID);------------------------encoding--------------------- /* string whereTwoDW = "'" + id + "','"+readRow3["BOX_ID"].ToString()+"'"; DataTable TwoDWTable = DBFactory.PopulateTempTable_BySp("[op_TwoDCode_Solution_waybill]", whereTwoDW); string EnCoding = TwoDWTable.Rows[0]["EnCoding"].ToString(); // Y/N string EnCoder = TwoDWTable.Rows[0]["EnCoder"].ToString(); //PDF417/MaxICode string StringIDValue = ""; if (EnCoding == "Y") { //encoder if (EnCoder == "PDF417") { //PDF417 generator //StringIDValue = TransferCode.Encoder(TwoDTable.Rows[0]["StringIDValue"].ToString()); foreach (DataRow readRowtd in TwoDWTable.Rows) { WAYBILL_ADDITION.UDF_BOXRow newRowtwod = wayBillAddition.UDF_BOX.NewUDF_BOXRow(); StringIDValue = TransferCode.Encoder(readRowtd["StringIDValue"].ToString()); newRowtwod.KEY = readRowtd["StringIDKey"].ToString(); newRowtwod.VALUE = StringIDValue.ToString(); wayBillAddition.UDF_BOX.Rows.Add(newRowtwod); } } else //MaxICode { //MaxICode generator } } else { foreach (DataRow readRowtd in TwoDWTable.Rows) { WAYBILL_ADDITION.UDF_BOXRow newRowtwod = wayBillAddition.UDF_BOX.NewUDF_BOXRow(); newRowtwod.KEY = readRowtd["StringIDKey"].ToString(); newRowtwod.VALUE = readRowtd["StringIDValue"].ToString(); wayBillAddition.UDF_BOX.Rows.Add(newRowtwod); } } */ //----------------------- end -------------------------------------------------------------- } index4++; index3++; } } //populate mini tables //set this dataset to packinglist so we can outputXML this.m_DataSet = wayBillAddition; } logger.Debug("LoadWayBillDatabaseData end"); }