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_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 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_CODECONSOL_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_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);
                    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");
        }