excel写进数据库

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Data;
using System.Data.SqlClient;
using BroadText.Common;
using System.IO;

namespace StaplesBiz
{
    public class InputExcel
    {
        private int longCommandTimeout = 1200;
        public DataTable ReadExcel(String fileName, String sheetName)
        {
            String strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Macro;HDR=YES;IMEX=1\";";
            strConn = String.Format(strConn, fileName);

            OleDbConnection conn = new OleDbConnection(strConn);
            System.Data.DataTable table = new System.Data.DataTable();
            try
            {
                //1. 建立连接
                //注意，"Extended Properties"是必需的               
                conn.Open();
                //2. 读取数据
                string query = "select * from [{0}$]";
                query = String.Format(query, sheetName);
                OleDbCommand oleCommand = new OleDbCommand(query, conn);
                OleDbDataAdapter da = new OleDbDataAdapter(oleCommand);
                da.Fill(table);
                //table中的数据就是Excel中的内容
            }
            finally
            {
                conn.Close();
            }

            return table;
        }

        public DataTable ReadCSV(String fileName)
        {
            string sheet = fileName.Substring(fileName.LastIndexOf(@"\") + 1);
            sheet = sheet.Substring(0, sheet.LastIndexOf(@"."));
            return ReadCSV(fileName, sheet);
        }

        public DataTable ReadCSV(String fileName, String sheetName)
        {

            String strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Directory.GetParent(fileName) + ";Extended Properties='Text;FMT=Delimited;HDR=YES;'";

            DataTable dtTable = new DataTable();
            using (OleDbConnection oldCon = new OleDbConnection(strConn))
            {
                try
                {
                    oldCon.Open();
                    string query = "select * from {0}.csv";
                    query = String.Format(query, sheetName);

                    OleDbDataAdapter oldData = new OleDbDataAdapter(query, oldCon);
                    oldData.Fill(dtTable);
                    return dtTable;
                }
                catch (OleDbException err)
                {
                    throw err;
                }
                finally
                {
                    dtTable.Dispose();
                    oldCon.Close();
                }
            }

 

            // "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Text;FMT=Delimited;\";";
            //strConn = String.Format(strConn, fileName);

            //OleDbConnection conn = new OleDbConnection(strConn);

            //DataTable dtTable = new DataTable();
            OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM [Book1.csv]", conn);

            //string query = "select * from {0}";
            //query = String.Format(query, sheetName);
            //OleDbCommand oleCommand = new OleDbCommand(query, conn);
            //OleDbDataAdapter adapter = new OleDbDataAdapter(query, conn);

            //try
            //{
            //    adapter.Fill(dtTable);
            //}
            //catch (Exception ex)
            //{
            //    dtTable = new DataTable();

            //}
            //return dtTable;

            //String strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Text;FMT=Delimited;\";";
            //strConn = String.Format(strConn, fileName);

            //OleDbConnection conn = new OleDbConnection(strConn);
            //System.Data.DataTable table = new System.Data.DataTable();
            //try
            //{
            //    //1. 建立连接
            //    //注意，"Extended Properties"是必需的               
            //    conn.Open();
            //    //2. 读取数据
            //    string query = "select * from [{0}$]";
            //    query = String.Format(query, sheetName);
            //    OleDbCommand oleCommand = new OleDbCommand(query, conn);
            //    OleDbDataAdapter da = new OleDbDataAdapter(oleCommand);
            //    da.Fill(table);
            //    //table中的数据就是Excel中的内容
            //}
            //finally
            //{
            //    conn.Close();
            //}

            //return table;
        }

        public DataTable ReadCSV2(String fileName)
        {
            StreamReader reader = new StreamReader(fileName);
            String[] row;
            String temp = "";
            Boolean isTitle = true;
            DataTable dt = new DataTable();
            for (; !reader.EndOfStream; )
            {
                if (isTitle)
                {
                    temp = reader.ReadLine();
                    row = temp.Split(',');

                    for (int i = 0; i < row.Length; i++)
                    {
                        DataColumn col = new DataColumn(row[i].ToString(), Type.GetType("System.String"));
                        dt.Columns.Add(col);
                    }
                    isTitle = false;
                }
                else
                {
                    temp = reader.ReadLine();
                    if (temp.IndexOf("\",\"") > 0)
                    {
                        temp = temp.Replace("\",\"", "^");
                        temp = temp.Replace("\"", "");
                        row = temp.Split('^');
                    }
                    else
                    {
                        row = temp.Split(',');
                    }

                    try
                    {
                        DataRow dataRow = dt.NewRow();
                        for (int i = 0; i < row.Length; i++)
                        {
                            dataRow[i] = row[i];
                        }
                        //dt.Rows.Add(row);
                        dt.Rows.Add(dataRow);
                    }
                    catch (Exception ex)
                    {
                        Log.SaveException(ex);
                    }
                }
            }
            reader.Close();
            return dt;
        }

        public DataTable ReadCSVforFTP945(String fileName)
        {
            StreamReader reader = new StreamReader(fileName);
            String[] row;
            String temp = "";
            DataTable dt = new DataTable();

            for (int i = 0; i < 30; i++)
            {
                DataColumn col = new DataColumn(i.ToString(), Type.GetType("System.String"));
                dt.Columns.Add(col);
            }

            for (; !reader.EndOfStream; )
            {
                temp = reader.ReadLine();
                if (temp.IndexOf("\",\"") > 0)
                {
                    temp = temp.Replace("\",\"", "^");
                    temp = temp.Replace("\"", "");
                    row = temp.Split('^');
                }
                else
                {
                    row = temp.Split(',');
                }

                try
                {
                    DataRow dataRow = dt.NewRow();
                    for (int i = 0; i < row.Length; i++)
                    {
                        dataRow[i] = row[i];
                    }
                    //dt.Rows.Add(row);
                    dt.Rows.Add(dataRow);
                }
                catch (Exception ex)
                {
                    Log.SaveException(ex);
                }
            }
            reader.Close();
            return dt;
        }

        public void MoveExcel(String fileName)
        {
            String path = FileManager.ApplicationPath + @"Excel\" + System.DateTime.Now.ToString("yyyyMMdd_hhmmss");
            if (!Directory.Exists(path))
            {
                Directory.CreateDirectory(path);
            }
            String temp = fileName.Substring(fileName.LastIndexOf(@"\"));

            try
            {
                File.Move(fileName, path + temp);
            }
            catch
            { }
        }

        public void MoveExcelForAutoReport(String fileName)
        {
            String path = fileName.Substring(0, fileName.LastIndexOf("\\")) + "\\" + System.DateTime.Now.ToString("yyyyMMdd_hhmmss");
            if (!Directory.Exists(path))
            {
                Directory.CreateDirectory(path);
            }
            String temp = fileName.Substring(fileName.LastIndexOf(@"\"));

            try
            {
                File.Move(fileName, path + temp);
            }
            catch
            { }
        }

        public String Import944()
        {
            String fileName = FileManager.ApplicationPath + @"Excel\Receiving Macro.xlsm";

            //1 清除临时数据
            String deleteAll = @"Truncate Table TempEDI944";
            SqlConnection conn = new SqlConnection(Common.GetConnectionString());
            SqlCommand cmd = new SqlCommand(deleteAll, conn);
            cmd.CommandTimeout = longCommandTimeout;
            conn.Open();
            cmd.ExecuteNonQuery();

            if (!File.Exists(fileName))
            {
                return "Receiving Macro.xlsm not exists, SKIP......";
            }

            //2 导入新数据
            DataTable dt = ReadExcel(fileName, "Sheet1");

            String sql = "select * from TempEDI944";

            SqlDataAdapter da = new SqlDataAdapter(sql, conn);
            SqlCommandBuilder cb = new SqlCommandBuilder(da);
            DataSet ds = new DataSet();
            da.Fill(ds);

            foreach (DataRow row in dt.Rows)
            {
                if (row[1] == System.DBNull.Value) continue;

                DataRow newRow = ds.Tables[0].NewRow();
                newRow["Location_EDC"] = row[0];
                newRow["Order_No_EDC"] = row[1];
                newRow["Item_EDC"] = row[2];
                newRow["Link"] = row[3];
                newRow["Variance"] = row[4];
                newRow["EDC_Qty_Oh"] = row[5];
                newRow["Qty_Oh"] = row[6];
                newRow["Variance1"] = row[7];
                newRow["Comments"] = row[8];
                newRow["Total_Received_QTY"] = row[9];
                newRow["Multiple_SO"] = row[10];

                ds.Tables[0].Rows.Add(newRow);

            }

            da.Update(ds);

            da.Dispose();
            ds.Dispose();
            conn.Close();

            MoveExcel(fileName);
            return "OK";
        }

        public String Import945()
        {
            String fileName = FileManager.ApplicationPath + @"Excel\Staples_945ship.xls";

            if (!File.Exists(fileName))
            {
                return "Staples_945ship.xls not exists, SKIP......";
            }

            String temp = Import945(fileName);
            if (temp == "OK")
            {
                MoveExcel(fileName);
            }
            return temp;
        }
        public String Import945(String fileName)
        {
            if (!File.Exists(fileName))
            {
                return "Staples_945ship.xls not exists, SKIP......";
            }

            //1 导入新数据
            DataTable dt = ReadExcel(fileName, "DATA");
            if (dt.Rows.Count < 2000)
            {
                return "unavailable Staples_945ship.xls , SKIP......";
            }

            //fileName = FileManager.ApplicationPath + @"Excel\Staples_945ship.xls";

            //2 清除临时数据
            String deleteAll = @"Truncate Table TempEDI945";
            SqlConnection conn = new SqlConnection(Common.GetConnectionString());
            SqlCommand cmd = new SqlCommand(deleteAll, conn);
            cmd.CommandTimeout = longCommandTimeout;
            conn.Open();
            cmd.ExecuteNonQuery();

            String sql = "select * from TempEDI945";

            SqlDataAdapter da = new SqlDataAdapter(sql, conn);
            SqlCommandBuilder cb = new SqlCommandBuilder(da);

            DataSet ds = new DataSet();
            da.Fill(ds);

            foreach (DataRow row in dt.Rows)
            {
                if (row[1] == System.DBNull.Value) continue;

                DataRow newRow = ds.Tables[0].NewRow();
                newRow["STUFF_DATE"] = row[0];
                newRow["ORIGIN"] = row[1];
                newRow["SEAL1"] = row[2];
                newRow["SEAL3"] = row[3];
                newRow["CS_NUM"] = row[4];
                newRow["CONTAINER_NUM"] = row[5];
                newRow["LOT"] = row[6];
                newRow["PO_CREATION_DATE"] = row[7];
                newRow["QTY_LOADED"] = row[8];
                newRow["ITEM"] = row[9];
                newRow["NET_WEIGHT"] = row[10];
                newRow["VOLUME"] = row[11];
                newRow["EDC_PRICE"] = row[12];
                newRow["ITEM_PRICE"] = row[13];
                newRow["CARRIER"] = row[14];
                newRow["PO"] = row[15];
                newRow["SKU"] = row[16];
                newRow["L1"] = row[17];
                newRow["L2"] = row[18];
                newRow["L3"] = row[19];
                newRow["L4"] = row[20];
                newRow["PRICE"] = row[21];
                newRow["P1"] = row[22];
                newRow["O3"] = row[23];
                newRow["WEIGHT"] = row[24];

                ds.Tables[0].Rows.Add(newRow);
            }

            da.Update(ds);

            da.Dispose();
            ds.Dispose();
            conn.Close();

            //MoveExcel(fileName);
            return "OK";
        }

        public String ImportBOH()
        {
            String fileName = FileManager.ApplicationPath + @"Excel\BOH.xlsx";

            //1 清除临时数据
            String deleteAll = @"Truncate Table TempWeeklyBOH";
            SqlConnection conn = new SqlConnection(Common.GetConnectionString());
            SqlCommand cmd = new SqlCommand(deleteAll, conn);
            cmd.CommandTimeout = longCommandTimeout;
            conn.Open();
            cmd.ExecuteNonQuery();

            if (!File.Exists(fileName))
            {
                return "BOH.xls not exists, SKIP......";
            }

            //2 导入新数据
            DataTable dt = ReadExcel(fileName, "Sheet1");

            String sql = "select * from TempWeeklyBOH";

            SqlDataAdapter da = new SqlDataAdapter(sql, conn);
            SqlCommandBuilder cb = new SqlCommandBuilder(da);

            DataSet ds = new DataSet();
            da.Fill(ds);

            foreach (DataRow row in dt.Rows)
            {
                if (row[1] == System.DBNull.Value) continue;

                DataRow newRow = ds.Tables[0].NewRow();

                newRow["Location_EDC"] = row[0];
                newRow["Order_No_EDC"] = row[1];
                newRow["Item_EDC"] = row[2];
                newRow["Link"] = row[3];
                newRow["EDC_Qty_Oh"] = row[4];
                newRow["Qty_Oh"] = row[5];
                newRow["Orginal_Variance"] = row[6];
                //newRow["Updated_Variance"] = row[7];
                //newRow["Old"] = row[8];
                //newRow["ISA_TK_LoadID"] = row[9];
                //newRow["Next_Action"] = row[10];
                //newRow["Comment"] = row[11];
                //newRow["Category1"] = row[12];
                //newRow["Reference1"] = row[13];
                //newRow["Gap1"] = row[14];
                //newRow["Category2"] = row[15];
                //newRow["Reference2"] = row[16];
                //newRow["Gap2"] = row[17];
                //newRow["Category3"] = row[18];
                //newRow["Reference3"] = row[19];
                //newRow["Gap3"] = row[20];
                //newRow["Category4"] = row[21];
                //newRow["Reference4"] = row[22];
                //newRow["Gap4"] = row[23];
                //newRow["Category5"] = row[24];
                //newRow["Reference5"] = row[25];
                //newRow["Gap5"] = row[26];

                ds.Tables[0].Rows.Add(newRow);
            }

            da.Update(ds);

            da.Dispose();
            ds.Dispose();
            conn.Close();

            MoveExcel(fileName);
            return "OK";
        }

        public String ImportWeeklyEDCIn()
        {
            String fileName = FileManager.ApplicationPath + @"Excel\DAMCO_WEEKLYHJ_EDCIN.csv";

            if (!File.Exists(fileName))
            {
                return "DAMCO_WEEKLYHJ_EDCIN.csv not exists, SKIP......";
            }

            //1 清除临时数据
            String deleteAll = @"Truncate Table TempWeeklyEDCIn";
            SqlConnection conn = new SqlConnection(Common.GetConnectionString());
            SqlCommand cmd = new SqlCommand(deleteAll, conn);
            cmd.CommandTimeout = longCommandTimeout;
            conn.Open();
            cmd.ExecuteNonQuery();

            //2 导入新数据
            DataTable dt = ReadCSV(fileName, "DAMCO_WEEKLYHJ_EDCIN");

            String sql = "select * from TempWeeklyEDCIn";

            SqlDataAdapter da = new SqlDataAdapter(sql, conn);
            SqlCommandBuilder cb = new SqlCommandBuilder(da);

            DataSet ds = new DataSet();
            da.Fill(ds);

            foreach (DataRow row in dt.Rows)
            {
                if (row[1] == System.DBNull.Value) continue;

                DataRow newRow = ds.Tables[0].NewRow();

                newRow["DeliveryTo"] = row[0];
                newRow["PO"] = row[1];
                newRow["MFGID"] = row[2];
                newRow["Item"] = row[3];
                newRow["Qty"] = Convert.ToInt32(Convert.ToDecimal(row[4]));
                newRow["ShipDate"] = row[5];
                newRow["DueDate"] = row[6];

                ds.Tables[0].Rows.Add(newRow);
            }

            da.Update(ds);

            da.Dispose();
            ds.Dispose();
            conn.Close();

            MoveExcel(fileName);
            return "OK";
        }

        public String ImportWeeklyEDCOut()
        {
            String fileName = FileManager.ApplicationPath + @"Excel\DAMCO_WEEKLYHJ_EDCOUT.csv";

            if (!File.Exists(fileName))
            {
                return "DAMCO_WEEKLYHJ_EDCOUT.csv not exists, SKIP......";
            }

            //1 清除临时数据
            String deleteAll = @"Truncate Table TempWeeklyEDCOut";
            SqlConnection conn = new SqlConnection(Common.GetConnectionString());
            SqlCommand cmd = new SqlCommand(deleteAll, conn);
            cmd.CommandTimeout = longCommandTimeout;
            conn.Open();
            cmd.ExecuteNonQuery();

            //2 导入新数据
            DataTable dt = ReadCSV(fileName, "DAMCO_WEEKLYHJ_EDCOUT");

            String sql = "select * from TempWeeklyEDCOut";

            SqlDataAdapter da = new SqlDataAdapter(sql, conn);
            SqlCommandBuilder cb = new SqlCommandBuilder(da);

            DataSet ds = new DataSet();
            da.Fill(ds);

            foreach (DataRow row in dt.Rows)
            {
                if (row[1] == System.DBNull.Value) continue;

                DataRow newRow = ds.Tables[0].NewRow();

                newRow["MFGID"] = row[0];
                newRow["Customer"] = row[1];
                newRow["PO"] = row[2];
                newRow["Item"] = row[3];
                newRow["Qty"] = Convert.ToInt32(Convert.ToDecimal(row[4]));
                newRow["ShipDate"] = row[5];
                newRow["DueDate"] = row[6];
                newRow["DeliverTo"] = row[7];

                ds.Tables[0].Rows.Add(newRow);
            }

            da.Update(ds);

            da.Dispose();
            ds.Dispose();
            conn.Close();

            MoveExcel(fileName);
            return "OK";
        }

        /// <summary>
        /// /
        /// </summary>
        ///

        public void ImportReceipt()
        {
            //1 取得新数据
            String sqlProd = "select * from t_receipt where wh_id = 'NAS05' OR wh_id = 'NAS06'";

            SqlConnection connProd = new SqlConnection(Common.GetConnectionString("SQLConnectionProdAAD"));
            SqlDataAdapter daProd = new SqlDataAdapter(sqlProd, connProd);
            SqlCommandBuilder cbProd = new SqlCommandBuilder(daProd);
            DataSet dsProd = new DataSet();
            daProd.Fill(dsProd);
            daProd.Dispose();
            //dsProd.Dispose();
            connProd.Close();

            //2 清除临时表数据
            String deleteAll = @"Truncate Table TempReceipt";
            SqlConnection connTemp = new SqlConnection(Common.GetConnectionString());
            SqlCommand cmdTemp = new SqlCommand(deleteAll, connTemp);
            cmdTemp.CommandTimeout = longCommandTimeout;
            connTemp.Open();
            cmdTemp.ExecuteNonQuery();

            //3 导入到临时表

            String sqlTemp = "select * from TempReceipt";
            SqlDataAdapter daTemp = new SqlDataAdapter(sqlTemp, connTemp);
            SqlCommandBuilder cbTemp = new SqlCommandBuilder(daTemp);
            DataSet dsTemp = new DataSet();
            daTemp.Fill(dsTemp);

            DateTime now = System.DateTime.Now;

            foreach (DataRow row in dsProd.Tables[0].Rows)
            {
                //insert
                DataRow newRow = dsTemp.Tables[0].NewRow();
                newRow["receipt_id"] = row[0];
                newRow["vendor_code"] = row[1];
                newRow["po_number"] = row[2];
                newRow["receipt_date"] = row[3];
                newRow["scac_code"] = row[4];
                newRow["status"] = row[5];
                newRow["item_number"] = row[6];
                newRow["lot_number"] = row[7];
                newRow["line_number"] = row[8];
                newRow["schedule_number"] = row[9];
                newRow["qty_received"] = row[10];
                newRow["qty_damaged"] = row[11];
                newRow["hu_id"] = row[12];
                newRow["pack_slip"] = row[13];
                newRow["fork_id"] = row[14];
                newRow["tran_status"] = row[15];
                newRow["receipt_uom"] = row[16];
                newRow["shipment_number"] = row[17];
                newRow["wh_id"] = row[18];
                newRow["stored_attribute_id"] = row[19];
                newRow["received_cbm"] = row[20];
                newRow["received_wgt"] = row[21];
                newRow["end_receipt_date"] = row[22];
                newRow["customs_unique_no"] = row[23];
                newRow["opentruck"] = row[24];
                newRow["remark"] = row[25];
                newRow["individual"] = row[26];
                newRow["serial_key"] = row[27];
                newRow["truck_number"] = row[28];

                dsTemp.Tables[0].Rows.Add(newRow);
                //insertCount++;
            }
            daTemp.Update(dsTemp);
            //Console.WriteLine(code + " updating");
            daTemp.Dispose();
            dsTemp.Dispose();
            connTemp.Close();
        }

        public void ImportTempShipment()
        {
            //1 取得新数据
            String sqlProd = "select * from tbl_shipment where wh_id = 'NAS05' OR wh_id = 'NAS06'";

            SqlConnection connProd = new SqlConnection(Common.GetConnectionString("SQLConnectionProdAAD"));
            SqlDataAdapter daProd = new SqlDataAdapter(sqlProd, connProd);
            SqlCommandBuilder cbProd = new SqlCommandBuilder(daProd);
            DataSet dsProd = new DataSet();
            daProd.Fill(dsProd);
            daProd.Dispose();
            //dsProd.Dispose();
            connProd.Close();

            //2 清除临时表数据
            String deleteAll = @"Truncate Table TempShipment";
            SqlConnection connTemp = new SqlConnection(Common.GetConnectionString());
            SqlCommand cmdTemp = new SqlCommand(deleteAll, connTemp);
            cmdTemp.CommandTimeout = longCommandTimeout;
            connTemp.Open();
            cmdTemp.ExecuteNonQuery();

            //3 导入到临时表

            String sqlTemp = "select * from TempShipment";
            SqlDataAdapter daTemp = new SqlDataAdapter(sqlTemp, connTemp);
            SqlCommandBuilder cbTemp = new SqlCommandBuilder(daTemp);
            DataSet dsTemp = new DataSet();
            daTemp.Fill(dsTemp);

            DateTime now = System.DateTime.Now;

            foreach (DataRow row in dsProd.Tables[0].Rows)
            {
                //insert
                DataRow newRow = dsTemp.Tables[0].NewRow();
                newRow["shipment_id"] = row[0];
                newRow["wh_id"] = row[1];
                newRow["load_id"] = row[2];
                newRow["carrier"] = row[3];
                newRow["pro_number"] = row[4];
                newRow["seal_number"] = row[5];
                newRow["carrier_scac"] = row[6];
                newRow["route"] = row[7];
                newRow["shipped_date"] = row[8];
                newRow["customs_send_status"] = row[9];
                newRow["user_id"] = row[10];
                newRow["message_type"] = row[11];
                newRow["client_code"] = row[12];
                newRow["customer_code"] = row[13];
                newRow["allocation_date"] = row[14];
                newRow["container_type"] = row[15];

                dsTemp.Tables[0].Rows.Add(newRow);
                //insertCount++;
            }
            daTemp.Update(dsTemp);
            //Console.WriteLine(code + " updating");
            daTemp.Dispose();
            dsTemp.Dispose();
            connTemp.Close();
        }

        public void ImportTempShipmentDetail()
        {
            //1 取得新数据
            String sqlProd = "select * from tbl_shipment_detail where wh_id = 'NAS05' OR wh_id = 'NAS06'";

            SqlConnection connProd = new SqlConnection(Common.GetConnectionString("SQLConnectionProdAAD"));
            SqlDataAdapter daProd = new SqlDataAdapter(sqlProd, connProd);
            SqlCommandBuilder cbProd = new SqlCommandBuilder(daProd);
            DataSet dsProd = new DataSet();
            daProd.Fill(dsProd);
            daProd.Dispose();
            //dsProd.Dispose();
            connProd.Close();

            //2 清除临时表数据
            String deleteAll = @"Truncate Table TempShipmentDetail";
            SqlConnection connTemp = new SqlConnection(Common.GetConnectionString());
            SqlCommand cmdTemp = new SqlCommand(deleteAll, connTemp);
            cmdTemp.CommandTimeout = longCommandTimeout;
            connTemp.Open();
            cmdTemp.ExecuteNonQuery();

            //3 导入到临时表

            String sqlTemp = "select * from TempShipmentDetail";
            SqlDataAdapter daTemp = new SqlDataAdapter(sqlTemp, connTemp);
            SqlCommandBuilder cbTemp = new SqlCommandBuilder(daTemp);
            DataSet dsTemp = new DataSet();
            daTemp.Fill(dsTemp);

            DateTime now = System.DateTime.Now;

            foreach (DataRow row in dsProd.Tables[0].Rows)
            {
                //insert
                DataRow newRow = dsTemp.Tables[0].NewRow();
                newRow["shipment_detail_id"] = row[0];
                newRow["shipment_id"] = row[1];
                newRow["wh_id"] = row[2];
                newRow["order_number"] = row[3];
                newRow["line_number"] = row[4];
                newRow["item_number"] = row[5];
                newRow["lot_number"] = row[6];
                newRow["stored_attribute_id"] = row[7];
                newRow["shipped_qty"] = row[8];
                newRow["wave_id"] = row[9];
                newRow["load_id"] = row[10];
                newRow["pick_id"] = row[11];
                newRow["hu_id"] = row[12];
                newRow["fifo_date"] = row[13];
                newRow["user_id"] = row[14];
                newRow["client_code"] = row[15];
                newRow["uom"] = row[16];
                newRow["tracking_number"] = row[17];
                newRow["origin"] = row[18];
                newRow["destination"] = row[19];
                newRow["customer_item_number"] = row[20];
                newRow["net_Weight"] = row[21];
                newRow["edc_price"] = row[22];
                newRow["po_item_price"] = row[23];
                newRow["item_price"] = row[24];
                newRow["po_creation_date"] = row[25];
                newRow["weight"] = row[26];
                newRow["volume"] = row[27];
                newRow["package"] = row[28];

                dsTemp.Tables[0].Rows.Add(newRow);
                //insertCount++;
            }
            daTemp.Update(dsTemp);
            //Console.WriteLine(code + " updating");
            daTemp.Dispose();
            dsTemp.Dispose();
            connTemp.Close();
        }

        public void ImportTempStoredItem()
        {
            //1 取得新数据
            String sqlProd = "select * from t_stored_item where wh_id = 'NAS05' OR wh_id = 'NAS06'";

            SqlConnection connProd = new SqlConnection(Common.GetConnectionString("SQLConnectionProdAAD"));
            SqlDataAdapter daProd = new SqlDataAdapter(sqlProd, connProd);
            SqlCommandBuilder cbProd = new SqlCommandBuilder(daProd);
            DataSet dsProd = new DataSet();
            daProd.Fill(dsProd);
            daProd.Dispose();
            //dsProd.Dispose();
            connProd.Close();

            //2 清除临时表数据
            String deleteAll = @"Truncate Table TempStoredItem";
            SqlConnection connTemp = new SqlConnection(Common.GetConnectionString());
            SqlCommand cmdTemp = new SqlCommand(deleteAll, connTemp);
            cmdTemp.CommandTimeout = longCommandTimeout;
            connTemp.Open();
            cmdTemp.ExecuteNonQuery();

            //3 导入到临时表

            String sqlTemp = "select * from TempStoredItem";
            SqlDataAdapter daTemp = new SqlDataAdapter(sqlTemp, connTemp);
            SqlCommandBuilder cbTemp = new SqlCommandBuilder(daTemp);
            DataSet dsTemp = new DataSet();
            daTemp.Fill(dsTemp);

            DateTime now = System.DateTime.Now;

            foreach (DataRow row in dsProd.Tables[0].Rows)
            {
                //insert
                DataRow newRow = dsTemp.Tables[0].NewRow();
                newRow["sequence"] = row[0];
                newRow["item_number"] = row[1];
                newRow["actual_qty"] = row[2];
                newRow["unavailable_qty"] = row[3];
                newRow["status"] = row[4];
                newRow["wh_id"] = row[5];
                newRow["location_id"] = row[6];
                newRow["fifo_date"] = row[7];
                newRow["expiration_date"] = row[8];
                newRow["reserved_for"] = row[9];
                newRow["lot_number"] = row[10];
                newRow["inspection_code"] = row[11];
                newRow["serial_number"] = row[12];
                newRow["type"] = row[13];
                newRow["put_away_location"] = row[14];
                newRow["owner_id"] = row[15];
                newRow["stored_attribute_id"] = row[16];
                newRow["hu_id"] = row[17];

                dsTemp.Tables[0].Rows.Add(newRow);
                //insertCount++;
            }
            daTemp.Update(dsTemp);
            //Console.WriteLine(code + " updating");
            daTemp.Dispose();
            dsTemp.Dispose();
            connTemp.Close();
        }

        public void ImportTempOrderDetail()
        {
            //1 取得新数据
            String sqlProd = "select * from t_order_detail where wh_id = 'NAS05' OR wh_id = 'NAS06'";

            SqlConnection connProd = new SqlConnection(Common.GetConnectionString("SQLConnectionProdAAD"));
            SqlDataAdapter daProd = new SqlDataAdapter(sqlProd, connProd);
            SqlCommandBuilder cbProd = new SqlCommandBuilder(daProd);
            DataSet dsProd = new DataSet();
            daProd.Fill(dsProd);
            daProd.Dispose();
            //dsProd.Dispose();
            connProd.Close();

            //2 清除临时表数据
            String deleteAll = @"Truncate Table TempOrderDetail";
            SqlConnection connTemp = new SqlConnection(Common.GetConnectionString());
            SqlCommand cmdTemp = new SqlCommand(deleteAll, connTemp);
            cmdTemp.CommandTimeout = longCommandTimeout;
            connTemp.Open();
            cmdTemp.ExecuteNonQuery();

            //3 导入到临时表

            String sqlTemp = "select * from TempOrderDetail";
            SqlDataAdapter daTemp = new SqlDataAdapter(sqlTemp, connTemp);
            SqlCommandBuilder cbTemp = new SqlCommandBuilder(daTemp);
            DataSet dsTemp = new DataSet();
            daTemp.Fill(dsTemp);

            DateTime now = System.DateTime.Now;

            foreach (DataRow row in dsProd.Tables[0].Rows)
            {
                //insert
                DataRow newRow = dsTemp.Tables[0].NewRow();
                newRow["order_detail_id"] = row[0];
                newRow["order_id"] = row[1];
                newRow["item_master_id"] = row[2];
                newRow["wh_id"] = row[3];
                newRow["order_number"] = row[4];
                newRow["line_number"] = row[5];
                newRow["item_number"] = row[6];
                newRow["bo_qty"] = row[7];
                newRow["bo_description"] = row[8];
                newRow["bo_weight"] = row[9];
                newRow["qty"] = row[10];
                newRow["afo_plan_qty"] = row[11];
                newRow["unit_pack"] = row[12];
                newRow["item_weight"] = row[13];
                newRow["item_tare_weight"] = row[14];
                newRow["haz_material"] = row[15];
                newRow["b_o_l_class"] = row[16];
                newRow["b_o_l_line1"] = row[17];
                newRow["b_o_l_line2"] = row[18];
                newRow["b_o_l_line3"] = row[19];
                newRow["b_o_l_plac_code"] = row[20];
                newRow["b_o_l_plac_desc"] = row[21];
                newRow["b_o_l_code"] = row[22];
                newRow["qty_shipped"] = row[23];
                newRow["line_type"] = row[24];
                newRow["item_description"] = row[25];
                newRow["stacking_seq"] = row[26];
                newRow["cust_part"] = row[27];
                newRow["lot_number"] = row[28];
                newRow["picking_flow"] = row[29];
                newRow["unit_weight"] = row[30];
                newRow["unit_volume"] = row[31];
                newRow["extended_weight"] = row[32];
                newRow["extended_volume"] = row[33];
                newRow["over_alloc_qty"] = row[34];
                newRow["date_expected"] = row[35];
                newRow["order_uom"] = row[36];
                newRow["host_wave_id"] = row[37];
                newRow["tran_plan_qty"] = row[38];
                newRow["use_shippable_uom"] = row[39];
                newRow["unit_insurance_amount"] = row[40];
                newRow["stored_attribute_id"] = row[41];
                newRow["client_po_number"] = row[42];
                newRow["pcs"] = row[43];
                newRow["color"] = row[44];
                newRow["size"] = row[45];
                newRow["style"] = row[46];
                newRow["origin"] = row[47];
                newRow["destination"] = row[48];
                newRow["reference_attribute_1"] = row[49];
                newRow["reference_attribute_2"] = row[50];
                newRow["reference_attribute_3"] = row[51];
                newRow["reference_attribute_4"] = row[52];
                newRow["sale_price"] = row[53];
                newRow["customer_item_number"] = row[54];
                newRow["edc_price"] = row[55];
                newRow["inspection_flag"] = row[56];
                newRow["hs_code"] = row[57];
                newRow["shipment_flow"] = row[58];
                newRow["product_type"] = row[59];

                dsTemp.Tables[0].Rows.Add(newRow);
                //insertCount++;
            }
            daTemp.Update(dsTemp);
            //Console.WriteLine(code + " updating");
            daTemp.Dispose();
            dsTemp.Dispose();
            connTemp.Close();
        }

        public void ImportTempHostOrderMaster()
        {
            //1 取得新数据
            String sqlProd = "select * from t_al_host_order_master where wh_id = 'NAS05' OR wh_id = 'NAS06'";

            SqlConnection connProd = new SqlConnection(Common.GetConnectionString("SQLConnectionProdAAD"));
            SqlDataAdapter daProd = new SqlDataAdapter(sqlProd, connProd);
            SqlCommandBuilder cbProd = new SqlCommandBuilder(daProd);
            DataSet dsProd = new DataSet();
            daProd.Fill(dsProd);
            daProd.Dispose();
            //dsProd.Dispose();
            connProd.Close();

            //2 清除临时表数据
            String deleteAll = @"Truncate Table TempHostOrderMaster";
            SqlConnection connTemp = new SqlConnection(Common.GetConnectionString());
            SqlCommand cmdTemp = new SqlCommand(deleteAll, connTemp);
            cmdTemp.CommandTimeout = longCommandTimeout;
            connTemp.Open();
            cmdTemp.ExecuteNonQuery();

            //3 导入到临时表

            String sqlTemp = "select * from TempHostOrderMaster";
            SqlDataAdapter daTemp = new SqlDataAdapter(sqlTemp, connTemp);
            SqlCommandBuilder cbTemp = new SqlCommandBuilder(daTemp);
            DataSet dsTemp = new DataSet();
            daTemp.Fill(dsTemp);

            DateTime now = System.DateTime.Now;

            foreach (DataRow row in dsProd.Tables[0].Rows)
            {
                //insert
                DataRow newRow = dsTemp.Tables[0].NewRow();
                newRow["host_order_master_id"] = row[0];
                newRow["host_group_id"] = row[1];
                newRow["record_create_date"] = row[2];
                newRow["processing_code"] = row[3];
                newRow["wh_id"] = row[4];
                newRow["order_number"] = row[5];
                newRow["store_order_number"] = row[6];
                newRow["order_type"] = row[7];
                newRow["customer_code"] = row[8];
                newRow["customer_po_number"] = row[9];
                newRow["department"] = row[10];
                newRow["load_id"] = row[11];
                newRow["load_seq"] = row[12];
                newRow["bol_number"] = row[13];
                newRow["master_bol_number"] = row[14];
                newRow["pro_number"] = row[15];
                newRow["carrier"] = row[16];
                newRow["carrier_scac"] = row[17];
                newRow["freight_terms"] = row[18];
                newRow["rush"] = row[19];
                newRow["order_date"] = row[20];
                newRow["arrive_date"] = row[21];
                newRow["date_expected"] = row[22];
                newRow["promised_date"] = row[23];
                newRow["weight"] = row[24];
                newRow["cubic_volume"] = row[25];
                newRow["containers"] = row[26];
                newRow["backorder"] = row[27];
                newRow["pre_paid"] = row[28];
                newRow["cod_amount"] = row[29];
                newRow["insurance_amount"] = row[30];
                newRow["pip_amount"] = row[31];
                newRow["freight_cost"] = row[32];
                newRow["ship_to_code"] = row[33];
                newRow["ship_to_name"] = row[34];
                newRow["ship_to_addr1"] = row[35];
                newRow["ship_to_addr2"] = row[36];
                newRow["ship_to_addr3"] = row[37];
                newRow["ship_to_city"] = row[38];
                newRow["ship_to_state"] = row[39];
                newRow["ship_to_zip"] = row[40];
                newRow["ship_to_country_code"] = row[41];
                newRow["ship_to_country_name"] = row[42];
                newRow["ship_to_phone"] = row[43];
                newRow["bill_to_code"] = row[44];
                newRow["bill_to_name"] = row[45];
                newRow["bill_to_addr1"] = row[46];
                newRow["bill_to_addr2"] = row[47];
                newRow["bill_to_addr3"] = row[48];
                newRow["bill_to_city"] = row[49];
                newRow["bill_to_state"] = row[50];
                newRow["bill_to_zip"] = row[51];
                newRow["bill_to_country_code"] = row[52];
                newRow["bill_to_country_name"] = row[53];
                newRow["bill_to_phone"] = row[54];
                newRow["delivery_name"] = row[55];
                newRow["delivery_addr1"] = row[56];
                newRow["delivery_addr2"] = row[57];
                newRow["delivery_addr3"] = row[58];
                newRow["delivery_city"] = row[59];
                newRow["delivery_state"] = row[60];
                newRow["delivery_zip"] = row[61];
                newRow["delivery_country_code"] = row[62];
                newRow["delivery_country_name"] = row[63];
                newRow["delivery_phone"] = row[64];
                newRow["bill_frght_to_code"] = row[65];
                newRow["bill_frght_to_name"] = row[66];
                newRow["bill_frght_to_addr1"] = row[67];
                newRow["bill_frght_to_addr2"] = row[68];
                newRow["bill_frght_to_addr3"] = row[69];
                newRow["bill_frght_to_city"] = row[70];
                newRow["bill_frght_to_state"] = row[71];
                newRow["bill_frght_to_zip"] = row[72];
                newRow["bill_frght_to_country_code"] = row[73];
                newRow["bill_frght_to_country_name"] = row[74];
                newRow["bill_frght_to_phone"] = row[75];
                newRow["carton_label"] = row[76];
                newRow["ver_flag"] = row[77];
                newRow["partial_order_flag"] = row[78];
                newRow["earliest_ship_date"] = row[79];
                newRow["latest_ship_date"] = row[80];
                newRow["earliest_delivery_date"] = row[81];
                newRow["latest_delivery_date"] = row[82];
                newRow["temp_link_id"] = row[83];
                newRow["owner_id"] = row[84];
                newRow["service_level"] = row[85];
                newRow["ship_via"] = row[86];
                newRow["ship_to_attention"] = row[87];
                newRow["sat_delivery_flag"] = row[88];
                newRow["residential_flag"] = row[89];
                newRow["registered_mail_flag"] = row[90];
                newRow["restricted_mail_flag"] = row[91];
                newRow["cod_flag"] = row[92];
                newRow["cod_pay_type"] = row[93];
                newRow["cod_option"] = row[94];
                newRow["insurance_flag"] = row[95];
                newRow["bill_frght_to_attention"] = row[96];
                newRow["client_code"] = row[97];
                newRow["display_order_number"] = row[98];
                newRow["ship_to_residential_flag"] = row[99];
                newRow["carrier_mode"] = row[100];
                newRow["earliest_appt_time"] = row[101];
                newRow["latest_appt_time"] = row[102];
                newRow["customer_order"] = row[103];
                newRow["transmode"] = row[104];
                newRow["specialist"] = row[105];
                newRow["customer_name"] = row[106];
                newRow["customer_number"] = row[107];

                dsTemp.Tables[0].Rows.Add(newRow);
                //insertCount++;
            }
            daTemp.Update(dsTemp);
            //Console.WriteLine(code + " updating");
            daTemp.Dispose();
            dsTemp.Dispose();
            connTemp.Close();
        }

        public void ImportTempHostOrderDetail()
        {
            //1 取得新数据
            String sqlProd = "select * from t_al_host_order_detail where wh_id = 'NAS05' OR wh_id = 'NAS06'";

            SqlConnection connProd = new SqlConnection(Common.GetConnectionString("SQLConnectionProdAAD"));
            SqlDataAdapter daProd = new SqlDataAdapter(sqlProd, connProd);
            SqlCommandBuilder cbProd = new SqlCommandBuilder(daProd);
            DataSet dsProd = new DataSet();
            daProd.Fill(dsProd);
            daProd.Dispose();
            //dsProd.Dispose();
            connProd.Close();

            //2 清除临时表数据
            String deleteAll = @"Truncate Table TempHostOrderDetail";
            SqlConnection connTemp = new SqlConnection(Common.GetConnectionString());
            SqlCommand cmdTemp = new SqlCommand(deleteAll, connTemp);
            cmdTemp.CommandTimeout = longCommandTimeout;
            connTemp.Open();
            cmdTemp.ExecuteNonQuery();

            //3 导入到临时表

            String sqlTemp = "select * from TempHostOrderDetail";
            SqlDataAdapter daTemp = new SqlDataAdapter(sqlTemp, connTemp);
            SqlCommandBuilder cbTemp = new SqlCommandBuilder(daTemp);
            DataSet dsTemp = new DataSet();
            daTemp.Fill(dsTemp);

            DateTime now = System.DateTime.Now;

            foreach (DataRow row in dsProd.Tables[0].Rows)
            {
                //insert
                DataRow newRow = dsTemp.Tables[0].NewRow();
                newRow["host_order_detail_id"] = row[0];
                newRow["host_order_master_id"] = row[1];
                newRow["host_group_id"] = row[2];
                newRow["record_create_date"] = row[3];
                newRow["processing_code"] = row[4];
                newRow["wh_id"] = row[5];
                newRow["order_number"] = row[6];
                newRow["line_number"] = row[7];
                newRow["item_number"] = row[8];
                newRow["item_description"] = row[9];
                newRow["cust_part"] = row[10];
                newRow["lot_number"] = row[11];
                newRow["qty"] = row[12];
                newRow["unit_weight"] = row[13];
                newRow["unit_volume"] = row[14];
                newRow["extended_weight"] = row[15];
                newRow["extended_volume"] = row[16];
                newRow["haz_material"] = row[17];
                newRow["bol_class"] = row[18];
                newRow["bol_code"] = row[19];
                newRow["order_uom"] = row[20];
                newRow["host_wave_id"] = row[21];
                newRow["owner_id"] = row[22];
                newRow["temp_link_id"] = row[23];
                newRow["unit_insurance_amount"] = row[24];
                newRow["gen_attribute_value1"] = row[25];
                newRow["gen_attribute_value2"] = row[26];
                newRow["gen_attribute_value3"] = row[27];
                newRow["gen_attribute_value4"] = row[28];
                newRow["gen_attribute_value5"] = row[29];
                newRow["gen_attribute_value6"] = row[30];
                newRow["gen_attribute_value7"] = row[31];
                newRow["gen_attribute_value8"] = row[32];
                newRow["gen_attribute_value9"] = row[33];
                newRow["gen_attribute_value10"] = row[34];
                newRow["gen_attribute_value11"] = row[35];
                newRow["client_code"] = row[36];
                newRow["display_item_number"] = row[37];
                newRow["display_order_number"] = row[38];
                newRow["origin"] = row[39];
                newRow["destination"] = row[40];
                newRow["reference_attribute_1"] = row[41];
                newRow["reference_attribute_2"] = row[42];
                newRow["reference_attribute_3"] = row[43];
                newRow["reference_attribute_4"] = row[44];
                newRow["sale_price"] = row[45];
                newRow["customer_item_number"] = row[46];
                newRow["edc_price"] = row[47];
                newRow["inspection_flag"] = row[48];
                newRow["hs_code"] = row[49];
                newRow["shipment_flow"] = row[50];
                newRow["product_type"] = row[51];

                dsTemp.Tables[0].Rows.Add(newRow);
                //insertCount++;
            }
            daTemp.Update(dsTemp);
            //Console.WriteLine(code + " updating");
            daTemp.Dispose();
            dsTemp.Dispose();
            connTemp.Close();
        }

        public void ImportTempTranLog()
        {
            //1 取得新数据
            String sqlProd = "select * from t_tran_log where tran_type='156'";

            SqlConnection connProd = new SqlConnection(Common.GetConnectionString("SQLConnectionProdAAD"));
            SqlDataAdapter daProd = new SqlDataAdapter(sqlProd, connProd);
            SqlCommandBuilder cbProd = new SqlCommandBuilder(daProd);
            DataSet dsProd = new DataSet();
            daProd.Fill(dsProd);
            daProd.Dispose();
            //dsProd.Dispose();
            connProd.Close();

            //2 清除临时表数据
            String deleteAll = @"Truncate Table TempTranLog";
            SqlConnection connTemp = new SqlConnection(Common.GetConnectionString());
            SqlCommand cmdTemp = new SqlCommand(deleteAll, connTemp);
            cmdTemp.CommandTimeout = longCommandTimeout;
            connTemp.Open();
            cmdTemp.ExecuteNonQuery();

            //3 导入到临时表

            String sqlTemp = "select * from TempTranLog";
            SqlDataAdapter daTemp = new SqlDataAdapter(sqlTemp, connTemp);
            SqlCommandBuilder cbTemp = new SqlCommandBuilder(daTemp);
            DataSet dsTemp = new DataSet();
            daTemp.Fill(dsTemp);

            DateTime now = System.DateTime.Now;

            foreach (DataRow row in dsProd.Tables[0].Rows)
            {
                //insert
                DataRow newRow = dsTemp.Tables[0].NewRow();

                newRow["tran_log_id"] = row[0];
                newRow["tran_type"] = row[1];
                newRow["description"] = row[2];
                newRow["start_tran_date"] = row[3];
                newRow["start_tran_time"] = row[4];
                newRow["end_tran_date"] = row[5];
                newRow["end_tran_time"] = row[6];
                newRow["employee_id"] = row[7];
                newRow["control_number"] = row[8];
                newRow["line_number"] = row[9];
                newRow["control_number_2"] = row[10];
                newRow["outside_id"] = row[11];
                newRow["wh_id"] = row[12];
                newRow["location_id"] = row[13];
                newRow["hu_id"] = row[14];
                newRow["num_items"] = row[15];
                newRow["item_number"] = row[16];
                newRow["lot_number"] = row[17];
                newRow["uom"] = row[18];
                newRow["tran_qty"] = row[19];
                newRow["wh_id_2"] = row[20];
                newRow["location_id_2"] = row[21];
                newRow["verify_status"] = row[22];
                newRow["employee_id_2"] = row[23];
                newRow["routing_code"] = row[24];
                newRow["hu_id_2"] = row[25];
                newRow["return_disposition"] = row[26];
                newRow["elapsed_time"] = row[27];
                newRow["source_storage_type"] = row[28];
                newRow["destination_storage_type"] = row[29];
                newRow["generic_attribute_1"] = row[30];
                newRow["generic_attribute_2"] = row[31];
                newRow["generic_attribute_3"] = row[32];
                newRow["generic_attribute_4"] = row[33];
                newRow["generic_attribute_5"] = row[34];
                newRow["generic_attribute_6"] = row[35];
                newRow["generic_attribute_7"] = row[36];
                newRow["generic_attribute_8"] = row[37];
                newRow["generic_attribute_9"] = row[38];
                newRow["generic_attribute_10"] = row[39];
                newRow["generic_attribute_11"] = row[40];
                newRow["owner_id"] = row[41];
                newRow["client_code"] = row[42];
                newRow["display_item_number"] = row[43];

                dsTemp.Tables[0].Rows.Add(newRow);
                //insertCount++;
            }
            daTemp.Update(dsTemp);
            //Console.WriteLine(code + " updating");
            daTemp.Dispose();
            dsTemp.Dispose();
            connTemp.Close();
        }

        public void ImportTempAlHostDcClpDetail()
        {
            //1 取得新数据
            String sqlProd = "select * from tbl_al_host_dc_clp_detail where wh_id = 'NAS05' OR wh_id = 'NAS06'";

            SqlConnection connProd = new SqlConnection(Common.GetConnectionString("SQLConnectionProdAAD"));
            SqlDataAdapter daProd = new SqlDataAdapter(sqlProd, connProd);
            SqlCommandBuilder cbProd = new SqlCommandBuilder(daProd);
            DataSet dsProd = new DataSet();
            daProd.Fill(dsProd);
            daProd.Dispose();
            //dsProd.Dispose();
            connProd.Close();

            //2 清除临时表数据
            String deleteAll = @"Truncate Table TempAlHostDcClpDetail";
            SqlConnection connTemp = new SqlConnection(Common.GetConnectionString());
            SqlCommand cmdTemp = new SqlCommand(deleteAll, connTemp);
            cmdTemp.CommandTimeout = longCommandTimeout;
            connTemp.Open();
            cmdTemp.ExecuteNonQuery();

            //3 导入到临时表

            String sqlTemp = "select * from TempAlHostDcClpDetail";
            SqlDataAdapter daTemp = new SqlDataAdapter(sqlTemp, connTemp);
            SqlCommandBuilder cbTemp = new SqlCommandBuilder(daTemp);
            DataSet dsTemp = new DataSet();
            daTemp.Fill(dsTemp);

            DateTime now = System.DateTime.Now;

            foreach (DataRow row in dsProd.Tables[0].Rows)
            {
                //insert
                DataRow newRow = dsTemp.Tables[0].NewRow();
                newRow["host_clp_dt_id"] = row[0];
                newRow["host_group_id"] = row[1];
                newRow["record_create_date"] = row[2];
                newRow["wh_id"] = row[3];
                newRow["load_id"] = row[4];
                newRow["client_code"] = row[5];
                newRow["order_number"] = row[6];
                newRow["line_number"] = row[7];
                newRow["item_number"] = row[8];
                newRow["lot_number"] = row[9];
                newRow["load_plan_qty"] = row[10];

                dsTemp.Tables[0].Rows.Add(newRow);
                //insertCount++;
            }
            daTemp.Update(dsTemp);
            //Console.WriteLine(code + " updating");
            daTemp.Dispose();
            dsTemp.Dispose();
            connTemp.Close();
        }

        public void ImportTempInventorySnapshotResult(DateTime lastSat)
        {
            if (lastSat == null || lastSat == new DateTime())
            {
                return;
            }

            //1 取得新数据
            String sqlProd = "select * from tbl_inventory_snapshot_result where record_create_date > '{0}'";
            sqlProd = String.Format(sqlProd, lastSat.ToString("yyyy-MM-dd"));

            SqlConnection connProd = new SqlConnection(Common.GetConnectionString("SQLConnectionProdAAD"));
            SqlDataAdapter daProd = new SqlDataAdapter(sqlProd, connProd);
            SqlCommandBuilder cbProd = new SqlCommandBuilder(daProd);
            DataSet dsProd = new DataSet();
            daProd.Fill(dsProd);
            daProd.Dispose();
            //dsProd.Dispose();
            connProd.Close();

            //2 清除临时表数据
            String deleteAll = @"Truncate Table TempInventorySnapshotResult";
            SqlConnection connTemp = new SqlConnection(Common.GetConnectionString());
            SqlCommand cmdTemp = new SqlCommand(deleteAll, connTemp);
            cmdTemp.CommandTimeout = longCommandTimeout;
            connTemp.Open();
            cmdTemp.ExecuteNonQuery();

            //3 导入到临时表

            String sqlTemp = "select * from TempInventorySnapshotResult";
            SqlDataAdapter daTemp = new SqlDataAdapter(sqlTemp, connTemp);
            SqlCommandBuilder cbTemp = new SqlCommandBuilder(daTemp);
            DataSet dsTemp = new DataSet();
            daTemp.Fill(dsTemp);

            DateTime now = System.DateTime.Now;

            foreach (DataRow row in dsProd.Tables[0].Rows)
            {
                //insert
                DataRow newRow = dsTemp.Tables[0].NewRow();

                newRow["inventory_snapshot_id"] = row[0];
                newRow["record_create_date"] = row[1];
                newRow["schedule_id"] = row[2];
                newRow["schedule_name"] = row[3];
                newRow["wh_id"] = row[4];
                newRow["client_code"] = row[5];
                newRow["item_number"] = row[6];
                newRow["display_item_number"] = row[7];
                newRow["lot_number"] = row[8];
                newRow["quantity"] = row[9];
                newRow["unavailable_quantity"] = row[10];
                newRow["location_id"] = row[11];
                newRow["hu_id"] = row[12];
                newRow["inventory_status"] = row[13];
                newRow["inventory_type"] = row[14];
                newRow["fifo_date"] = row[15];
                newRow["uom"] = row[16];
                newRow["gen_attribute_value1"] = row[17];
                newRow["gen_attribute_value2"] = row[18];
                newRow["gen_attribute_value3"] = row[19];
                newRow["gen_attribute_value4"] = row[20];
                newRow["gen_attribute_value5"] = row[21];
                newRow["gen_attribute_value6"] = row[22];
                newRow["gen_attribute_value7"] = row[23];
                newRow["gen_attribute_value8"] = row[24];
                newRow["gen_attribute_value9"] = row[25];
                newRow["gen_attribute_value10"] = row[26];
                newRow["gen_attribute_value11"] = row[27];
                newRow["user_id"] = row[28];
                newRow["send_flag"] = row[29];

                dsTemp.Tables[0].Rows.Add(newRow);
                //insertCount++;
            }
            daTemp.Update(dsTemp);
            //Console.WriteLine(code + " updating");
            daTemp.Dispose();
            dsTemp.Dispose();
            connTemp.Close();
        }

        public void ImportTempOrderDetail2()
        {
            //1 取得新数据
            String sqlProd = @"
            select o.wh_id,o.order_number,d.item_number,o.latest_delivery_date,o.latest_ship_date,d.line_number,d.qty,d.customer_item_number,
o.ship_to_code,o.ship_to_name,o.ship_to_addr1,o.ship_to_addr2,o.ship_to_addr3,o.ship_to_city,o.ship_to_state,
o.ship_to_zip,o.ship_to_country_code,o.ship_to_country_name,o.ship_to_phone,
o.delivery_name,o.delivery_addr1,o.delivery_addr2,o.delivery_addr3,o.delivery_city,o.delivery_state,o.delivery_zip,
o.delivery_country_code,o.delivery_country_name,delivery_phone

from t_order o,t_order_detail d
where o.order_number = d.order_number
 AND o.wh_id =d.wh_id
and (o.wh_id = 'NAS06' OR o.wh_id = 'NAS05')
and not exists ( select 1 from t_afa_load_detail a
              inner join t_afa_load_detail_line b on a.load_detail_id = b.load_detail_id
              where a.wh_id = d.wh_id
                and a.order_number = d.order_number
                 and b.line_number = d.line_number
                 and b.item_number = d.item_number
                 )
 
 
and not exists ( select 1 from t_afo_wave_detail a
              inner join t_afo_wave_detail_line b on a.wave_detail_id = b.wave_detail_id
              where a.wh_id = d.wh_id
                and a.order_number = d.order_number
                 and b.line_number = d.line_number
                 and b.item_number = d.item_number
                 )
and not exists ( select 1 from t_pick_detail a
                  where a.wh_id = d.wh_id
                and a.order_number = d.order_number
                 and a.line_number = d.line_number
                 and a.item_number = d.item_number)
               
ORDER BY o.latest_delivery_date,o.wh_id,o.order_number,d.item_number
            ";

            SqlConnection connProd = new SqlConnection(Common.GetConnectionString("SQLConnectionProdAAD"));
            SqlDataAdapter daProd = new SqlDataAdapter(sqlProd, connProd);
            SqlCommandBuilder cbProd = new SqlCommandBuilder(daProd);
            DataSet dsProd = new DataSet();
            daProd.Fill(dsProd);
            daProd.Dispose();
            //dsProd.Dispose();
            connProd.Close();

            //2 清除临时表数据
            String deleteAll = @"Truncate Table TempOrderDetail2";
            SqlConnection connTemp = new SqlConnection(Common.GetConnectionString());
            SqlCommand cmdTemp = new SqlCommand(deleteAll, connTemp);
            cmdTemp.CommandTimeout = longCommandTimeout;
            connTemp.Open();
            cmdTemp.ExecuteNonQuery();

            //3 导入到临时表

            String sqlTemp = "select * from TempOrderDetail2";
            SqlDataAdapter daTemp = new SqlDataAdapter(sqlTemp, connTemp);
            SqlCommandBuilder cbTemp = new SqlCommandBuilder(daTemp);
            DataSet dsTemp = new DataSet();
            daTemp.Fill(dsTemp);

            DateTime now = System.DateTime.Now;

            foreach (DataRow row in dsProd.Tables[0].Rows)
            {
                //insert
                DataRow newRow = dsTemp.Tables[0].NewRow();
                newRow["wh_id"] = row[0];
                newRow["order_number"] = row[1];
                newRow["item_number"] = row[2];
                newRow["latest_delivery_date"] = row[3];
                newRow["latest_ship_date"] = row[4];
                newRow["line_number"] = row[5];
                newRow["qty"] = row[6];
                newRow["customer_item_number"] = row[7];
                newRow["ship_to_code"] = row[8];
                newRow["ship_to_name"] = row[9];
                newRow["ship_to_addr1"] = row[10];
                newRow["ship_to_addr2"] = row[11];
                newRow["ship_to_addr3"] = row[12];
                newRow["ship_to_city"] = row[13];
                newRow["ship_to_state"] = row[14];
                newRow["ship_to_zip"] = row[15];
                newRow["ship_to_country_code"] = row[16];
                newRow["ship_to_country_name"] = row[17];
                newRow["ship_to_phone"] = row[18];
                newRow["delivery_name"] = row[19];
                newRow["delivery_addr1"] = row[20];
                newRow["delivery_addr2"] = row[21];
                newRow["delivery_addr3"] = row[22];
                newRow["delivery_city"] = row[23];
                newRow["delivery_state"] = row[24];
                newRow["delivery_zip"] = row[25];
                newRow["delivery_country_code"] = row[26];
                newRow["delivery_country_name"] = row[27];
                newRow["delivery_phone"] = row[28];

                dsTemp.Tables[0].Rows.Add(newRow);
                //insertCount++;
            }
            daTemp.Update(dsTemp);
            //Console.WriteLine(code + " updating");
            daTemp.Dispose();
            dsTemp.Dispose();
            connTemp.Close();
        }

        public void ImportTempDcEdiAck()
        {
            //1 取得新数据
            String sqlProd = "select * from tbl_dc_edi_ack";

            SqlConnection connProd = new SqlConnection(Common.GetConnectionString("SQLConnectionProdAAD"));
            SqlDataAdapter daProd = new SqlDataAdapter(sqlProd, connProd);
            SqlCommandBuilder cbProd = new SqlCommandBuilder(daProd);
            DataSet dsProd = new DataSet();
            daProd.Fill(dsProd);
            daProd.Dispose();
            //dsProd.Dispose();
            connProd.Close();

            //2 清除临时表数据
            String deleteAll = @"Truncate Table TempDcEdiAck";
            SqlConnection connTemp = new SqlConnection(Common.GetConnectionString());
            SqlCommand cmdTemp = new SqlCommand(deleteAll, connTemp);
            cmdTemp.CommandTimeout = longCommandTimeout;
            connTemp.Open();
            cmdTemp.ExecuteNonQuery();

            //3 导入到临时表

            String sqlTemp = "select * from TempDcEdiAck";
            SqlDataAdapter daTemp = new SqlDataAdapter(sqlTemp, connTemp);
            SqlCommandBuilder cbTemp = new SqlCommandBuilder(daTemp);
            DataSet dsTemp = new DataSet();
            daTemp.Fill(dsTemp);

            DateTime now = System.DateTime.Now;

            foreach (DataRow row in dsProd.Tables[0].Rows)
            {
                //insert
                DataRow newRow = dsTemp.Tables[0].NewRow();
                newRow["edi_ack_id"] = row[0];
                newRow["ack_id"] = row[1];
                newRow["client_code"] = row[2];
                newRow["wh_id"] = row[3];
                newRow["message_type"] = row[4];
                newRow["primary_key"] = row[5];
                newRow["primary_key_type"] = row[6];
                newRow["comments"] = row[7];
                newRow["reference_key_1"] = row[8];
                newRow["reference_key_2"] = row[9];
                newRow["reference_key_3"] = row[10];
                newRow["reference_key_4"] = row[11];
                newRow["reference_key_5"] = row[12];
                newRow["reference_key_6"] = row[13];
                newRow["reference_key_7"] = row[14];
                newRow["reference_key_8"] = row[15];
                newRow["reference_key_9"] = row[16];
                newRow["reference_key_10"] = row[17];

                dsTemp.Tables[0].Rows.Add(newRow);
                //insertCount++;
            }
            daTemp.Update(dsTemp);
            //Console.WriteLine(code + " updating");
            daTemp.Dispose();
            dsTemp.Dispose();
            connTemp.Close();
        }

        public void ImportTempDcEdiAckDetail()
        {
            //1 取得新数据
            String sqlProd = "select * from tbl_dc_edi_ack_detail";

            SqlConnection connProd = new SqlConnection(Common.GetConnectionString("SQLConnectionProdAAD"));
            SqlDataAdapter daProd = new SqlDataAdapter(sqlProd, connProd);
            SqlCommandBuilder cbProd = new SqlCommandBuilder(daProd);
            DataSet dsProd = new DataSet();
            daProd.Fill(dsProd);
            daProd.Dispose();
            //dsProd.Dispose();
            connProd.Close();

            //2 清除临时表数据
            String deleteAll = @"Truncate Table TempDcEdiAckDetail";
            SqlConnection connTemp = new SqlConnection(Common.GetConnectionString());
            SqlCommand cmdTemp = new SqlCommand(deleteAll, connTemp);
            cmdTemp.CommandTimeout = longCommandTimeout;
            connTemp.Open();
            cmdTemp.ExecuteNonQuery();

            //3 导入到临时表

            String sqlTemp = "select * from TempDcEdiAckDetail";
            SqlDataAdapter daTemp = new SqlDataAdapter(sqlTemp, connTemp);
            SqlCommandBuilder cbTemp = new SqlCommandBuilder(daTemp);
            DataSet dsTemp = new DataSet();
            daTemp.Fill(dsTemp);

            DateTime now = System.DateTime.Now;

            foreach (DataRow row in dsProd.Tables[0].Rows)
            {
                //insert
                DataRow newRow = dsTemp.Tables[0].NewRow();

                newRow["edi_ack_id"] = row[0];
                newRow["edi_ack_detail_id"] = row[1];
                newRow["wh_id"] = row[2];
                newRow["control_number1"] = row[3];
                newRow["control_number2"] = row[4];
                newRow["line_number"] = row[5];
                newRow["item_number"] = row[6];
                newRow["lot_number"] = row[7];
                newRow["location_id"] = row[8];
                newRow["hu_id"] = row[9];
                newRow["generic_attribute_1"] = row[10];
                newRow["generic_attribute_2"] = row[11];
                newRow["generic_attribute_3"] = row[12];
                newRow["generic_attribute_4"] = row[13];
                newRow["generic_attribute_5"] = row[14];
                newRow["generic_attribute_6"] = row[15];
                newRow["generic_attribute_7"] = row[16];
                newRow["generic_attribute_8"] = row[17];
                newRow["generic_attribute_9"] = row[18];
                newRow["generic_attribute_10"] = row[19];
                newRow["generic_attribute_11"] = row[20];
                newRow["qty"] = row[21];
                newRow["weight"] = row[22];
                newRow["volume"] = row[23];
                newRow["customer_item_number"] = row[24];
                newRow["reference_key_1"] = row[25];
                newRow["reference_key_2"] = row[26];
                newRow["reference_key_3"] = row[27];
                newRow["reference_key_4"] = row[28];
                newRow["reference_key_5"] = row[29];
                newRow["reference_key_6"] = row[30];
                newRow["reference_key_7"] = row[31];
                newRow["reference_key_8"] = row[32];
                newRow["reference_key_9"] = row[33];
                newRow["reference_key_10"] = row[34];
                newRow["reference_key_11"] = row[35];
                newRow["reference_key_12"] = row[36];
                newRow["reference_key_13"] = row[37];
                newRow["reference_key_14"] = row[38];
                newRow["reference_key_15"] = row[39];

 

                dsTemp.Tables[0].Rows.Add(newRow);
                //insertCount++;
            }
            daTemp.Update(dsTemp);
            //Console.WriteLine(code + " updating");
            daTemp.Dispose();
            dsTemp.Dispose();
            connTemp.Close();
        }

        public void ImportTempOrder()
        {
            //1 取得新数据
            String sqlProd = "select * from t_order";

            SqlConnection connProd = new SqlConnection(Common.GetConnectionString("SQLConnectionProdAAD"));
            SqlDataAdapter daProd = new SqlDataAdapter(sqlProd, connProd);
            SqlCommandBuilder cbProd = new SqlCommandBuilder(daProd);
            DataSet dsProd = new DataSet();
            daProd.Fill(dsProd);
            daProd.Dispose();
            //dsProd.Dispose();
            connProd.Close();

            //2 清除临时表数据
            String deleteAll = @"Truncate Table TempOrder";
            SqlConnection connTemp = new SqlConnection(Common.GetConnectionString());
            SqlCommand cmdTemp = new SqlCommand(deleteAll, connTemp);
            cmdTemp.CommandTimeout = longCommandTimeout;
            connTemp.Open();
            cmdTemp.ExecuteNonQuery();

            //3 导入到临时表

            String sqlTemp = "select * from TempOrder";
            SqlDataAdapter daTemp = new SqlDataAdapter(sqlTemp, connTemp);
            SqlCommandBuilder cbTemp = new SqlCommandBuilder(daTemp);
            DataSet dsTemp = new DataSet();
            daTemp.Fill(dsTemp);

            DateTime now = System.DateTime.Now;

            foreach (DataRow row in dsProd.Tables[0].Rows)
            {
                //insert
                DataRow newRow = dsTemp.Tables[0].NewRow();

                newRow["order_id"] = row[0];
                newRow["wh_id"] = row[1];
                newRow["order_number"] = row[2];
                newRow["store_order_number"] = row[3];
                newRow["type_id"] = row[4];
                newRow["customer_id"] = row[5];
                newRow["cust_po_number"] = row[6];
                newRow["customer_name"] = row[7];
                newRow["customer_phone"] = row[8];
                newRow["customer_fax"] = row[9];
                newRow["customer_email"] = row[10];
                newRow["department"] = row[11];
                newRow["load_id"] = row[12];
                newRow["load_seq"] = row[13];
                newRow["bol_number"] = row[14];
                newRow["pro_number"] = row[15];
                newRow["master_bol_number"] = row[16];
                newRow["carrier"] = row[17];
                newRow["carrier_scac"] = row[18];
                newRow["freight_terms"] = row[19];
                newRow["rush"] = row[20];
                newRow["priority"] = row[21];
                newRow["order_date"] = row[22];
                newRow["arrive_date"] = row[23];
                newRow["actual_arrival_date"] = row[24];
                newRow["date_picked"] = row[25];
                newRow["date_expected"] = row[26];
                newRow["promised_date"] = row[27];
                newRow["weight"] = row[28];
                newRow["cubic_volume"] = row[29];
                newRow["containers"] = row[30];
                newRow["backorder"] = row[31];
                newRow["pre_paid"] = row[32];
                newRow["cod_amount"] = row[33];
                newRow["insurance_amount"] = row[34];
                newRow["pip_amount"] = row[35];
                newRow["freight_cost"] = row[36];
                newRow["region"] = row[37];
                newRow["bill_to_code"] = row[38];
                newRow["bill_to_name"] = row[39];
                newRow["bill_to_addr1"] = row[40];
                newRow["bill_to_addr2"] = row[41];
                newRow["bill_to_addr3"] = row[42];
                newRow["bill_to_city"] = row[43];
                newRow["bill_to_state"] = row[44];
                newRow["bill_to_zip"] = row[45];
                newRow["bill_to_country_code"] = row[46];
                newRow["bill_to_country_name"] = row[47];
                newRow["bill_to_phone"] = row[48];
                newRow["ship_to_code"] = row[49];
                newRow["ship_to_name"] = row[50];
                newRow["ship_to_addr1"] = row[51];
                newRow["ship_to_addr2"] = row[52];
                newRow["ship_to_addr3"] = row[53];
                newRow["ship_to_city"] = row[54];
                newRow["ship_to_state"] = row[55];
                newRow["ship_to_zip"] = row[56];
                newRow["ship_to_country_code"] = row[57];
                newRow["ship_to_country_name"] = row[58];
                newRow["ship_to_phone"] = row[59];
                newRow["delivery_name"] = row[60];
                newRow["delivery_addr1"] = row[61];
                newRow["delivery_addr2"] = row[62];
                newRow["delivery_addr3"] = row[63];
                newRow["delivery_city"] = row[64];
                newRow["delivery_state"] = row[65];
                newRow["delivery_zip"] = row[66];
                newRow["delivery_country_code"] = row[67];
                newRow["delivery_country_name"] = row[68];
                newRow["delivery_phone"] = row[69];
                newRow["bill_frght_to_code"] = row[70];
                newRow["bill_frght_to_name"] = row[71];
                newRow["bill_frght_to_addr1"] = row[72];
                newRow["bill_frght_to_addr2"] = row[73];
                newRow["bill_frght_to_addr3"] = row[74];
                newRow["bill_frght_to_city"] = row[75];
                newRow["bill_frght_to_state"] = row[76];
                newRow["bill_frght_to_zip"] = row[77];
                newRow["bill_frght_to_country_code"] = row[78];
                newRow["bill_frght_to_country_name"] = row[79];
                newRow["bill_frght_to_phone"] = row[80];
                newRow["return_to_code"] = row[81];
                newRow["return_to_name"] = row[82];
                newRow["return_to_addr1"] = row[83];
                newRow["return_to_addr2"] = row[84];
                newRow["return_to_addr3"] = row[85];
                newRow["return_to_city"] = row[86];
                newRow["return_to_state"] = row[87];
                newRow["return_to_zip"] = row[88];
                newRow["return_to_country_code"] = row[89];
                newRow["return_to_country_name"] = row[90];
                newRow["return_to_phone"] = row[91];
                newRow["rma_number"] = row[92];
                newRow["rma_expiration_date"] = row[93];
                newRow["carton_label"] = row[94];
                newRow["ver_flag"] = row[95];
                newRow["full_pallets"] = row[96];
                newRow["haz_flag"] = row[97];
                newRow["order_wgt"] = row[98];
                newRow["status"] = row[99];
                newRow["zone"] = row[100];
                newRow["drop_ship"] = row[101];
                newRow["lock_flag"] = row[102];
                newRow["partial_order_flag"] = row[103];
                newRow["earliest_ship_date"] = row[104];
                newRow["latest_ship_date"] = row[105];
                newRow["actual_ship_date"] = row[106];
                newRow["earliest_delivery_date"] = row[107];
                newRow["latest_delivery_date"] = row[108];
                newRow["actual_delivery_date"] = row[109];
                newRow["route"] = row[110];
                newRow["baseline_rate"] = row[111];
                newRow["planning_rate"] = row[112];
                newRow["carrier_id"] = row[113];
                newRow["manifest_carrier_id"] = row[114];
                newRow["ship_via_id"] = row[115];
                newRow["destination_port"] = row[116];
                newRow["vendor_code"] = row[117];
                newRow["owner_id"] = row[118];
                newRow["forwarder_code"] = row[119];
                newRow["pod_code"] = row[120];
                newRow["pod_name"] = row[121];
                newRow["dst_code"] = row[122];
                newRow["dst_name"] = row[123];
                newRow["voyage"] = row[124];
                newRow["vessel_name"] = row[125];
                newRow["last_update_date"] = row[126];
                newRow["fcl_lcl_flag"] = row[127];
                newRow["early_receipt_date"] = row[128];
                newRow["late_receipt_date"] = row[129];
                newRow["import_carrier"] = row[130];
                newRow["import_container_number"] = row[131];
                newRow["import_seal_no"] = row[132];
                newRow["import_master_B_L_number"] = row[133];
                newRow["import_house_B_L_number"] = row[134];
                newRow["so_remarks"] = row[135];
                newRow["created_by"] = row[136];
                newRow["created_date"] = row[137];
                newRow["last_modified_by"] = row[138];
                newRow["last_modified_date"] = row[139];
                newRow["shipment_type_flag"] = row[140];
                newRow["close_flag"] = row[141];
                newRow["multiple_so"] = row[142];
                newRow["cbol"] = row[143];
                newRow["consignee"] = row[144];
                newRow["close_so_time"] = row[145];
                newRow["custsheetno_enter_dt"] = row[146];
                newRow["asn_closed_by"] = row[147];
                newRow["customs_sheet_no"] = row[148];
                newRow["mods_received_date"] = row[149];
                newRow["display_order_number"] = row[150];
                newRow["client_code"] = row[151];
                newRow["ship_to_residential_flag"] = row[152];
                newRow["carrier_mode"] = row[153];
                newRow["service_level"] = row[154];
                newRow["ship_to_attention"] = row[155];
                newRow["earliest_appt_time"] = row[156];
                newRow["latest_appt_time"] = row[157];
                newRow["so_type"] = row[158];
                newRow["customer_order"] = row[159];
                newRow["customer_code"] = row[160];
                newRow["transmode"] = row[161];
                newRow["specialist"] = row[162];
                newRow["customer_number"] = row[163];

                dsTemp.Tables[0].Rows.Add(newRow);
                //insertCount++;
            }
            daTemp.Update(dsTemp);
            //Console.WriteLine(code + " updating");
            daTemp.Dispose();
            dsTemp.Dispose();
            connTemp.Close();
        }

        public void CutUSST()
        {
            //String sql= "DoCutUSST";

            //SqlConnection connTemp = new SqlConnection(Common.GetConnectionString());
            //SqlCommand cmdTemp = new SqlCommand(sql, connTemp);cmdTemp.CommandTimeout = longCommandTimeout;
            //connTemp.Open();
            //cmdTemp.ExecuteNonQuery();
            //connTemp.Close();

            String sql = "DoCutUSST_01";
            SqlConnection connTemp = new SqlConnection(Common.GetConnectionString());
            SqlCommand cmdTemp = new SqlCommand(sql, connTemp);
            cmdTemp.CommandTimeout = longCommandTimeout;
            connTemp.Open();
            cmdTemp.ExecuteNonQuery();
            connTemp.Close();

            sql = "DoCutUSST_02";
            connTemp = new SqlConnection(Common.GetConnectionString());
            cmdTemp = new SqlCommand(sql, connTemp);
            cmdTemp.CommandTimeout = longCommandTimeout;
            connTemp.Open();
            cmdTemp.ExecuteNonQuery();
            connTemp.Close();

            sql = "DoCutUSST_03";
            connTemp = new SqlConnection(Common.GetConnectionString());
            cmdTemp = new SqlCommand(sql, connTemp);
            cmdTemp.CommandTimeout = longCommandTimeout;
            connTemp.Open();
            cmdTemp.ExecuteNonQuery();
            connTemp.Close();

            sql = "DoCutUSST_04";
            connTemp = new SqlConnection(Common.GetConnectionString());
            cmdTemp = new SqlCommand(sql, connTemp);
            cmdTemp.CommandTimeout = longCommandTimeout;
            connTemp.Open();
            cmdTemp.ExecuteNonQuery();
            connTemp.Close();

            sql = "DoCutUSST_05";
            connTemp = new SqlConnection(Common.GetConnectionString());
            cmdTemp = new SqlCommand(sql, connTemp);
            cmdTemp.CommandTimeout = longCommandTimeout;
            connTemp.Open();
            cmdTemp.ExecuteNonQuery();
            connTemp.Close();

            sql = "DoCutUSST_06";
            connTemp = new SqlConnection(Common.GetConnectionString());
            cmdTemp = new SqlCommand(sql, connTemp);
            cmdTemp.CommandTimeout = longCommandTimeout;
            connTemp.Open();
            cmdTemp.ExecuteNonQuery();
            connTemp.Close();

            sql = "DoCutUSST_07";
            connTemp = new SqlConnection(Common.GetConnectionString());
            cmdTemp = new SqlCommand(sql, connTemp);
            cmdTemp.CommandTimeout = longCommandTimeout;
            connTemp.Open();
            cmdTemp.ExecuteNonQuery();
            connTemp.Close();

            sql = "DoCutUSST_08";
            connTemp = new SqlConnection(Common.GetConnectionString());
            cmdTemp = new SqlCommand(sql, connTemp);
            cmdTemp.CommandTimeout = longCommandTimeout;
            connTemp.Open();
            cmdTemp.ExecuteNonQuery();
            connTemp.Close();

            sql = "DoCutUSST_09";
            connTemp = new SqlConnection(Common.GetConnectionString());
            cmdTemp = new SqlCommand(sql, connTemp);
            cmdTemp.CommandTimeout = longCommandTimeout;
            connTemp.Open();
            cmdTemp.ExecuteNonQuery();
            connTemp.Close();

        }

        public void Do944A()
        {
            String sql = "Do944A";

            SqlConnection connTemp = new SqlConnection(Common.GetConnectionString());
            SqlCommand cmdTemp = new SqlCommand(sql, connTemp);
            cmdTemp.CommandTimeout = longCommandTimeout;
            connTemp.Open();
            cmdTemp.ExecuteNonQuery();

            connTemp.Close();
        }

        public void Do944B()
        {
            //String sql = "Do944B '{0}'";
            String sql = "Do944B";

            //sql = String.Format(sql, System.DateTime.Now.ToString("yyyy-MM-dd"));

            SqlConnection connTemp = new SqlConnection(Common.GetConnectionString());
            SqlCommand cmdTemp = new SqlCommand(sql, connTemp);
            cmdTemp.CommandTimeout = longCommandTimeout;
            connTemp.Open();
            cmdTemp.ExecuteNonQuery();

            connTemp.Close();
        }

        public void DoTaskC()
        {
            String sql = "DoTaskC";

            SqlConnection connTemp = new SqlConnection(Common.GetConnectionString());
            SqlCommand cmdTemp = new SqlCommand(sql, connTemp);
            cmdTemp.CommandTimeout = longCommandTimeout;
            connTemp.Open();
            cmdTemp.ExecuteNonQuery();

            connTemp.Close();
        }

        public void DoBOH()
        {
            return;

            String sql = "DoBOH";

            SqlConnection connTemp = new SqlConnection(Common.GetConnectionString());
            SqlCommand cmdTemp = new SqlCommand(sql, connTemp);
            cmdTemp.CommandTimeout = longCommandTimeout;
            connTemp.Open();
            cmdTemp.ExecuteNonQuery();

            connTemp.Close();
        }

        public void Do945()
        {
            String sql = "Do945 '2013-5-1'";

            SqlConnection connTemp = new SqlConnection(Common.GetConnectionString());
            SqlCommand cmdTemp = new SqlCommand(sql, connTemp);
            cmdTemp.CommandTimeout = longCommandTimeout;
            connTemp.Open();
            cmdTemp.ExecuteNonQuery();

            connTemp.Close();
        }

        public void DoWeeklyEDIOut()
        {
            String sql = "DoWeeklyEDIOut";

            SqlConnection connTemp = new SqlConnection(Common.GetConnectionString());
            SqlCommand cmdTemp = new SqlCommand(sql, connTemp);
            cmdTemp.CommandTimeout = longCommandTimeout;
            connTemp.Open();
            cmdTemp.ExecuteNonQuery();

            connTemp.Close();
        }

        public void DoWeeklyEDIOut2()
        {
            String sql = "DoWeeklyEDIOut2";

            SqlConnection connTemp = new SqlConnection(Common.GetConnectionString());
            SqlCommand cmdTemp = new SqlCommand(sql, connTemp);
            cmdTemp.CommandTimeout = longCommandTimeout;
            connTemp.Open();
            cmdTemp.ExecuteNonQuery();

            connTemp.Close();
        }

        public void DoEDCOpenOrder()
        {
            String sql = "DoEDCOpenOrder";

            SqlConnection connTemp = new SqlConnection(Common.GetConnectionString());
            SqlCommand cmdTemp = new SqlCommand(sql, connTemp);
            cmdTemp.CommandTimeout = longCommandTimeout;
            connTemp.Open();
            cmdTemp.ExecuteNonQuery();

            connTemp.Close();
        }

        public void DoWeeklyBOH()
        {
            String sql = "DoWeeklyBOH944A";

            SqlConnection connTemp = new SqlConnection(Common.GetConnectionString());
            SqlCommand cmdTemp = new SqlCommand(sql, connTemp);
            cmdTemp.CommandTimeout = longCommandTimeout;
            connTemp.Open();
            cmdTemp.ExecuteNonQuery();

            sql = "DoWeeklyBOH944B";
            connTemp = new SqlConnection(Common.GetConnectionString());
            cmdTemp = new SqlCommand(sql, connTemp);
            cmdTemp.CommandTimeout = longCommandTimeout;
            connTemp.Open();
            cmdTemp.ExecuteNonQuery();

            sql = "DoWeeklyBOH945";
            connTemp = new SqlConnection(Common.GetConnectionString());
            cmdTemp = new SqlCommand(sql, connTemp);
            cmdTemp.CommandTimeout = longCommandTimeout;
            connTemp.Open();
            cmdTemp.ExecuteNonQuery();

            sql = "DoWeeklyBOH_Temp";
            connTemp = new SqlConnection(Common.GetConnectionString());
            cmdTemp = new SqlCommand(sql, connTemp);
            cmdTemp.CommandTimeout = longCommandTimeout;
            connTemp.Open();
            cmdTemp.ExecuteNonQuery();

            connTemp.Close();
        }

        public String CutUSST(String temp)
        {
            return temp;
            //在DB中解决
            //return temp.Substring(5).Trim();
        }

        public void CoverOrderDetail(DataRow rowTemp, DataRow row, Boolean flag)
        {
            rowTemp["order_detail_id"] = row["order_detail_id"];
            rowTemp["order_id"] = row["order_id"];
            rowTemp["item_master_id"] = row["item_master_id"];
            rowTemp["wh_id"] = row["wh_id"];
            if (flag)
            {
                rowTemp["order_number"] = CutUSST(row["order_number"].ToString());
            }
            else
            {
                rowTemp["order_number"] = row["order_number"];
            }
            rowTemp["line_number"] = row["line_number"];
            if (flag)
            {
                rowTemp["item_number"] = CutUSST(row["item_number"].ToString());
            }
            else
            {
                rowTemp["item_number"] = row["item_number"].ToString();
            }
            rowTemp["bo_qty"] = row["bo_qty"];
            rowTemp["bo_description"] = row["bo_description"];
            rowTemp["bo_weight"] = row["bo_weight"];
            rowTemp["qty"] = row["qty"];
            rowTemp["afo_plan_qty"] = row["afo_plan_qty"];
            rowTemp["unit_pack"] = row["unit_pack"];
            rowTemp["item_weight"] = row["item_weight"];
            rowTemp["item_tare_weight"] = row["item_tare_weight"];
            rowTemp["haz_material"] = row["haz_material"];
            rowTemp["b_o_l_class"] = row["b_o_l_class"];
            rowTemp["b_o_l_line1"] = row["b_o_l_line1"];
            rowTemp["b_o_l_line2"] = row["b_o_l_line2"];
            rowTemp["b_o_l_line3"] = row["b_o_l_line3"];
            rowTemp["b_o_l_plac_code"] = row["b_o_l_plac_code"];
            rowTemp["b_o_l_plac_desc"] = row["b_o_l_plac_desc"];
            rowTemp["b_o_l_code"] = row["b_o_l_code"];
            rowTemp["qty_shipped"] = row["qty_shipped"];
            rowTemp["line_type"] = row["line_type"];
            rowTemp["item_description"] = row["item_description"];
            rowTemp["stacking_seq"] = row["stacking_seq"];
            rowTemp["cust_part"] = row["cust_part"];
            rowTemp["lot_number"] = row["lot_number"];
            rowTemp["picking_flow"] = row["picking_flow"];
            rowTemp["unit_weight"] = row["unit_weight"];
            rowTemp["unit_volume"] = row["unit_volume"];
            rowTemp["extended_weight"] = row["extended_weight"];
            rowTemp["extended_volume"] = row["extended_volume"];
            rowTemp["over_alloc_qty"] = row["over_alloc_qty"];
            rowTemp["date_expected"] = row["date_expected"];
            rowTemp["order_uom"] = row["order_uom"];
            rowTemp["host_wave_id"] = row["host_wave_id"];
            rowTemp["tran_plan_qty"] = row["tran_plan_qty"];
            rowTemp["use_shippable_uom"] = row["use_shippable_uom"];
            rowTemp["unit_insurance_amount"] = row["unit_insurance_amount"];
            rowTemp["stored_attribute_id"] = row["stored_attribute_id"];
            rowTemp["client_po_number"] = row["client_po_number"];
            rowTemp["pcs"] = row["pcs"];
            rowTemp["color"] = row["color"];
            rowTemp["size"] = row["size"];
            rowTemp["style"] = row["style"];
            rowTemp["origin"] = row["origin"];
            rowTemp["destination"] = row["destination"];
            rowTemp["reference_attribute_1"] = row["reference_attribute_1"];
            rowTemp["reference_attribute_2"] = row["reference_attribute_2"];
            rowTemp["reference_attribute_3"] = row["reference_attribute_3"];
            rowTemp["reference_attribute_4"] = row["reference_attribute_4"];
            rowTemp["sale_price"] = row["sale_price"];
            rowTemp["customer_item_number"] = row["customer_item_number"];
            rowTemp["edc_price"] = row["edc_price"];
            rowTemp["inspection_flag"] = row["inspection_flag"];
            rowTemp["hs_code"] = row["hs_code"];
            rowTemp["shipment_flow"] = row["shipment_flow"];
            rowTemp["product_type"] = row["product_type"];
        }

        public void CoverHostOrderDetail(DataRow rowTemp, DataRow row, Boolean flag)
        {
            rowTemp["host_order_detail_id"] = row["host_order_detail_id"];
            rowTemp["host_order_master_id"] = row["host_order_master_id"];
            rowTemp["host_group_id"] = row["host_group_id"];
            rowTemp["record_create_date"] = row["record_create_date"];
            rowTemp["processing_code"] = row["processing_code"];
            rowTemp["wh_id"] = row["wh_id"];
            if (flag)
            {
                rowTemp["order_number"] = CutUSST(row["order_number"].ToString());
            }
            else
            {
                rowTemp["order_number"] = row["order_number"].ToString();
            }
            rowTemp["line_number"] = row["line_number"];
            if (flag)
            {
                rowTemp["item_number"] = CutUSST(row["item_number"].ToString());
            }
            else
            {
                rowTemp["item_number"] = row["item_number"].ToString();
            }
            rowTemp["item_description"] = row["item_description"];
            rowTemp["cust_part"] = row["cust_part"];
            rowTemp["lot_number"] = row["lot_number"];
            rowTemp["qty"] = row["qty"];
            rowTemp["unit_weight"] = row["unit_weight"];
            rowTemp["unit_volume"] = row["unit_volume"];
            rowTemp["extended_weight"] = row["extended_weight"];
            rowTemp["extended_volume"] = row["extended_volume"];
            rowTemp["haz_material"] = row["haz_material"];
            rowTemp["bol_class"] = row["bol_class"];
            rowTemp["bol_code"] = row["bol_code"];
            rowTemp["order_uom"] = row["order_uom"];
            rowTemp["host_wave_id"] = row["host_wave_id"];
            rowTemp["owner_id"] = row["owner_id"];
            rowTemp["temp_link_id"] = row["temp_link_id"];
            rowTemp["unit_insurance_amount"] = row["unit_insurance_amount"];
            rowTemp["gen_attribute_value1"] = row["gen_attribute_value1"];
            rowTemp["gen_attribute_value2"] = row["gen_attribute_value2"];
            rowTemp["gen_attribute_value3"] = row["gen_attribute_value3"];
            rowTemp["gen_attribute_value4"] = row["gen_attribute_value4"];
            rowTemp["gen_attribute_value5"] = row["gen_attribute_value5"];
            rowTemp["gen_attribute_value6"] = row["gen_attribute_value6"];
            rowTemp["gen_attribute_value7"] = row["gen_attribute_value7"];
            rowTemp["gen_attribute_value8"] = row["gen_attribute_value8"];
            rowTemp["gen_attribute_value9"] = row["gen_attribute_value9"];
            rowTemp["gen_attribute_value10"] = row["gen_attribute_value10"];
            rowTemp["gen_attribute_value11"] = row["gen_attribute_value11"];
            rowTemp["client_code"] = row["client_code"];
            rowTemp["display_item_number"] = row["display_item_number"];
            rowTemp["display_order_number"] = row["display_order_number"];
            rowTemp["origin"] = row["origin"];
            rowTemp["destination"] = row["destination"];
            rowTemp["reference_attribute_1"] = row["reference_attribute_1"];
            rowTemp["reference_attribute_2"] = row["reference_attribute_2"];
            rowTemp["reference_attribute_3"] = row["reference_attribute_3"];
            rowTemp["reference_attribute_4"] = row["reference_attribute_4"];
            rowTemp["sale_price"] = row["sale_price"];
            rowTemp["customer_item_number"] = row["customer_item_number"];
            rowTemp["edc_price"] = row["edc_price"];
            rowTemp["inspection_flag"] = row["inspection_flag"];
            rowTemp["hs_code"] = row["hs_code"];
            rowTemp["shipment_flow"] = row["shipment_flow"];
            rowTemp["product_type"] = row["product_type"];
        }

        public void CoverHostOrderMaster(DataRow rowTemp, DataRow row, Boolean flag)
        {
            rowTemp["host_order_master_id"] = row["host_order_master_id"];
            rowTemp["host_group_id"] = row["host_group_id"];
            rowTemp["record_create_date"] = row["record_create_date"];
            rowTemp["processing_code"] = row["processing_code"];
            rowTemp["wh_id"] = row["wh_id"];
            rowTemp["order_number"] = row["order_number"];
            rowTemp["store_order_number"] = row["store_order_number"];
            rowTemp["order_type"] = row["order_type"];
            rowTemp["customer_code"] = row["customer_code"];
            rowTemp["customer_po_number"] = row["customer_po_number"];
            rowTemp["department"] = row["department"];
            rowTemp["load_id"] = row["load_id"];
            rowTemp["load_seq"] = row["load_seq"];
            rowTemp["bol_number"] = row["bol_number"];
            rowTemp["master_bol_number"] = row["master_bol_number"];
            rowTemp["pro_number"] = row["pro_number"];
            rowTemp["carrier"] = row["carrier"];
            rowTemp["carrier_scac"] = row["carrier_scac"];
            rowTemp["freight_terms"] = row["freight_terms"];
            rowTemp["rush"] = row["rush"];
            rowTemp["order_date"] = row["order_date"];
            rowTemp["arrive_date"] = row["arrive_date"];
            rowTemp["date_expected"] = row["date_expected"];
            rowTemp["promised_date"] = row["promised_date"];
            rowTemp["weight"] = row["weight"];
            rowTemp["cubic_volume"] = row["cubic_volume"];
            rowTemp["containers"] = row["containers"];
            rowTemp["backorder"] = row["backorder"];
            rowTemp["pre_paid"] = row["pre_paid"];
            rowTemp["cod_amount"] = row["cod_amount"];
            rowTemp["insurance_amount"] = row["insurance_amount"];
            rowTemp["pip_amount"] = row["pip_amount"];
            rowTemp["freight_cost"] = row["freight_cost"];
            rowTemp["ship_to_code"] = row["ship_to_code"];
            rowTemp["ship_to_name"] = row["ship_to_name"];
            rowTemp["ship_to_addr1"] = row["ship_to_addr1"];
            rowTemp["ship_to_addr2"] = row["ship_to_addr2"];
            rowTemp["ship_to_addr3"] = row["ship_to_addr3"];
            rowTemp["ship_to_city"] = row["ship_to_city"];
            rowTemp["ship_to_state"] = row["ship_to_state"];
            rowTemp["ship_to_zip"] = row["ship_to_zip"];
            rowTemp["ship_to_country_code"] = row["ship_to_country_code"];
            rowTemp["ship_to_country_name"] = row["ship_to_country_name"];
            rowTemp["ship_to_phone"] = row["ship_to_phone"];
            rowTemp["bill_to_code"] = row["bill_to_code"];
            rowTemp["bill_to_name"] = row["bill_to_name"];
            rowTemp["bill_to_addr1"] = row["bill_to_addr1"];
            rowTemp["bill_to_addr2"] = row["bill_to_addr2"];
            rowTemp["bill_to_addr3"] = row["bill_to_addr3"];
            rowTemp["bill_to_city"] = row["bill_to_city"];
            rowTemp["bill_to_state"] = row["bill_to_state"];
            rowTemp["bill_to_zip"] = row["bill_to_zip"];
            rowTemp["bill_to_country_code"] = row["bill_to_country_code"];
            rowTemp["bill_to_country_name"] = row["bill_to_country_name"];
            rowTemp["bill_to_phone"] = row["bill_to_phone"];
            rowTemp["delivery_name"] = row["delivery_name"];
            rowTemp["delivery_addr1"] = row["delivery_addr1"];
            rowTemp["delivery_addr2"] = row["delivery_addr2"];
            rowTemp["delivery_addr3"] = row["delivery_addr3"];
            rowTemp["delivery_city"] = row["delivery_city"];
            rowTemp["delivery_state"] = row["delivery_state"];
            rowTemp["delivery_zip"] = row["delivery_zip"];
            rowTemp["delivery_country_code"] = row["delivery_country_code"];
            rowTemp["delivery_country_name"] = row["delivery_country_name"];
            rowTemp["delivery_phone"] = row["delivery_phone"];
            rowTemp["bill_frght_to_code"] = row["bill_frght_to_code"];
            rowTemp["bill_frght_to_name"] = row["bill_frght_to_name"];
            rowTemp["bill_frght_to_addr1"] = row["bill_frght_to_addr1"];
            rowTemp["bill_frght_to_addr2"] = row["bill_frght_to_addr2"];
            rowTemp["bill_frght_to_addr3"] = row["bill_frght_to_addr3"];
            rowTemp["bill_frght_to_city"] = row["bill_frght_to_city"];
            rowTemp["bill_frght_to_state"] = row["bill_frght_to_state"];
            rowTemp["bill_frght_to_zip"] = row["bill_frght_to_zip"];
            rowTemp["bill_frght_to_country_code"] = row["bill_frght_to_country_code"];
            rowTemp["bill_frght_to_country_name"] = row["bill_frght_to_country_name"];
            rowTemp["bill_frght_to_phone"] = row["bill_frght_to_phone"];
            rowTemp["carton_label"] = row["carton_label"];
            rowTemp["ver_flag"] = row["ver_flag"];
            rowTemp["partial_order_flag"] = row["partial_order_flag"];
            rowTemp["earliest_ship_date"] = row["earliest_ship_date"];
            rowTemp["latest_ship_date"] = row["latest_ship_date"];
            rowTemp["earliest_delivery_date"] = row["earliest_delivery_date"];
            rowTemp["latest_delivery_date"] = row["latest_delivery_date"];
            rowTemp["temp_link_id"] = row["temp_link_id"];
            rowTemp["owner_id"] = row["owner_id"];
            rowTemp["service_level"] = row["service_level"];
            rowTemp["ship_via"] = row["ship_via"];
            rowTemp["ship_to_attention"] = row["ship_to_attention"];
            rowTemp["sat_delivery_flag"] = row["sat_delivery_flag"];
            rowTemp["residential_flag"] = row["residential_flag"];
            rowTemp["registered_mail_flag"] = row["registered_mail_flag"];
            rowTemp["restricted_mail_flag"] = row["restricted_mail_flag"];
            rowTemp["cod_flag"] = row["cod_flag"];
            rowTemp["cod_pay_type"] = row["cod_pay_type"];
            rowTemp["cod_option"] = row["cod_option"];
            rowTemp["insurance_flag"] = row["insurance_flag"];
            rowTemp["bill_frght_to_attention"] = row["bill_frght_to_attention"];
            rowTemp["client_code"] = row["client_code"];
            rowTemp["display_order_number"] = row["display_order_number"];
            rowTemp["ship_to_residential_flag"] = row["ship_to_residential_flag"];
            rowTemp["carrier_mode"] = row["carrier_mode"];
            rowTemp["earliest_appt_time"] = row["earliest_appt_time"];
            rowTemp["latest_appt_time"] = row["latest_appt_time"];
            rowTemp["customer_order"] = row["customer_order"];
            rowTemp["transmode"] = row["transmode"];
            rowTemp["specialist"] = row["specialist"];
            rowTemp["customer_name"] = row["customer_name"];
            rowTemp["customer_number"] = row["customer_number"];

        }

        public String Import846(String fileName)
        {
            //1 清除临时数据
            String deleteAll = @"Truncate Table TempEDI846";
            SqlConnection conn = new SqlConnection(Common.GetConnectionString());
            SqlCommand cmd = new SqlCommand(deleteAll, conn);
            cmd.CommandTimeout = longCommandTimeout;
            conn.Open();
            cmd.ExecuteNonQuery();

            //2 导入新数据
            DataTable dt = ReadCSV2(fileName);

            String sql = "select * from TempEDI846";

            SqlDataAdapter da = new SqlDataAdapter(sql, conn);
            SqlCommandBuilder cb = new SqlCommandBuilder(da);

            DataSet ds = new DataSet();
            da.Fill(ds);

            foreach (DataRow row in dt.Rows)
            {
                if (row[1] == System.DBNull.Value) continue;

                DataRow newRow = ds.Tables[0].NewRow();

                newRow["ISA"] = row[0];
                newRow["WH_ID"] = row[1];
                newRow["ITEM"] = row[2];
                newRow["LOT"] = row[3];
                newRow["QTY"] = Convert.ToInt32(Convert.ToDecimal(row[4]));

                ds.Tables[0].Rows.Add(newRow);
            }

            da.Update(ds);

            da.Dispose();
            ds.Dispose();
            conn.Close();

            //MoveExcel(fileName);
            return "OK";

        }

        public String ImportStaplesBOH(String fileName)
        {
            //1 清除临时数据
            String deleteAll = @"Truncate Table TempStaplesBOHTemp";
            SqlConnection conn = new SqlConnection(Common.GetConnectionString());
            SqlCommand cmd = new SqlCommand(deleteAll, conn);
            cmd.CommandTimeout = longCommandTimeout;
            conn.Open();
            cmd.ExecuteNonQuery();

            //2 导入新数据
            DataTable dt = ReadCSV2(fileName);

            String sql = "select * from TempStaplesBOHTemp";

            SqlDataAdapter da = new SqlDataAdapter(sql, conn);
            SqlCommandBuilder cb = new SqlCommandBuilder(da);

            DataSet ds = new DataSet();
            da.Fill(ds);

            foreach (DataRow row in dt.Rows)
            {
                if (row[1] == System.DBNull.Value) continue;

                DataRow newRow = ds.Tables[0].NewRow();

                newRow["ITEM"] = row[0];
                newRow["ORDER_NO"] = row[1];
                newRow["INVENTORY_DATE"] = row[2];
                newRow["LOCATION"] = row[3];
                newRow["QTY_OH"] = Convert.ToInt32(Convert.ToDecimal(row[4]));

                ds.Tables[0].Rows.Add(newRow);
            }

            da.Update(ds);

            da.Dispose();
            ds.Dispose();
            conn.Close();

            //MoveExcel(fileName);
            return "OK";

        }

        public String ImportUpdatedWeeklyBOHNew(String fileName, Int32 week)
        {
            //1 清除临时数据
            String deleteAll = @"delete TblUpdatedWeeklyBOHNew where [week]=" + week.ToString();
            SqlConnection conn = new SqlConnection(Common.GetConnectionString());
            SqlCommand cmd = new SqlCommand(deleteAll, conn);
            cmd.CommandTimeout = longCommandTimeout;
            conn.Open();
            cmd.ExecuteNonQuery();

            //2 导入新数据
            DataTable dt = ReadExcel(fileName, "00_BOH_New");

            String sql = "select * from TblUpdatedWeeklyBOHNew where [week]=" + week.ToString();

            SqlDataAdapter da = new SqlDataAdapter(sql, conn);
            SqlCommandBuilder cb = new SqlCommandBuilder(da);

            DataSet ds = new DataSet();
            da.Fill(ds);

            foreach (DataRow row in dt.Rows)
            {
                if (row[1] == System.DBNull.Value) continue;

                DataRow newRow = ds.Tables[0].NewRow();

                newRow["Location"] = row[0];
                newRow["OrderNo"] = row[1];
                newRow["Item"] = row[2];
                newRow["Link"] = row[3];
                newRow["EDCQty"] = row[4];
                newRow["Qty"] = row[5];
                newRow["Variance"] = row[6];
                newRow["Old"] = row[7];
                newRow["CombinedCategory"] = row[8];
                newRow["OldCategory"] = row[9];

                newRow["Category"] = (row[10] == System.DBNull.Value ? "" : row[10]);
                newRow["Status"] = (row[11] == System.DBNull.Value ? "" : row[11]);
                newRow["ISA_TK_Load_ID"] = (row[12] == System.DBNull.Value ? "" : row[12]);
                newRow["NextStep"] = (row[13] == System.DBNull.Value ? "" : row[13]);
                newRow["TargetResolutionTime"] = (row[14] == System.DBNull.Value ? "" : row[14]);
                newRow["DamcoNextStep"] = (row[15] == System.DBNull.Value ? "" : row[15]);
                newRow["DamcoReference"] = (row[16] == System.DBNull.Value ? "" : row[16]);
                newRow["DamcoComment"] = (row[17] == System.DBNull.Value ? "" : row[17]);

                newRow["OldGap"] = row[18];
                newRow["Week"] = week;

                ds.Tables[0].Rows.Add(newRow);
            }

            da.Update(ds);

            da.Dispose();
            ds.Dispose();
            conn.Close();

            //MoveExcel(fileName);
            return "OK";

        }

        public String Imp_hjopenorderlist_0414()
        {
            String sqlProd = @"sp_xinong_hjopenorderlist";

            SqlConnection connProd = new SqlConnection(Common.GetConnectionString("SQLConnectionProdAAA"));
            SqlCommand cmdProd = new SqlCommand(sqlProd, connProd);

            cmdProd.CommandTimeout = longCommandTimeout;
            connProd.Open();
            cmdProd.ExecuteNonQuery();

            cmdProd.Dispose();
            connProd.Close();

            return "OK";
        }

        public String Imp_hjnoopenorderlist_0414()
        {
            String sqlProd = @"sp_xinong_hjnoopenorderlist";

            SqlConnection connProd = new SqlConnection(Common.GetConnectionString("SQLConnectionProdAAA"));
            SqlCommand cmdProd = new SqlCommand(sqlProd, connProd);
            cmdProd.CommandTimeout = longCommandTimeout;
            connProd.Open();
            cmdProd.ExecuteNonQuery();

            cmdProd.Dispose();
            connProd.Close();

            return "OK";
        }

        public String Imp_hjedidelorderline_0414()
        {
            String sqlProd = @"sp_xinong_hjedidelorderline";

            SqlConnection connProd = new SqlConnection(Common.GetConnectionString("SQLConnectionProdAAA"));
            SqlCommand cmdProd = new SqlCommand(sqlProd, connProd);
            cmdProd.CommandTimeout = longCommandTimeout;
            connProd.Open();
            cmdProd.ExecuteNonQuery();

            cmdProd.Dispose();
            connProd.Close();

            return "OK";
        }

        public String Imp_hjinventory_0423()
        {
            String sqlProd = @"sp_xinong_hjinventory";

            SqlConnection connProd = new SqlConnection(Common.GetConnectionString("SQLConnectionProdAAA"));
            SqlCommand cmdProd = new SqlCommand(sqlProd, connProd);
            cmdProd.CommandTimeout = longCommandTimeout;
            connProd.Open();
            cmdProd.ExecuteNonQuery();

            cmdProd.Dispose();
            connProd.Close();

            return "OK";
        }

 

 

    }
}