/// <summary>        /// 处理“店铺竞品销售数据”导入文件        /// </summary>        /// <param name="filePath">文件物理路径</param>        /// <param name="ownerId">品牌Id</param>        /// <param name="saleYM">竞品数据销售年月</param>        /// <param name="msg">处理过程中出现的错误消息</param>        /// <returns></returns>        public bool HandleImportExcel(string filePath, string ownerId, string saleYM, out string msg)        {            using (DaoTransactionScope ts = new DaoTransactionScope(true))            {                msg = "";    // 返回给UI层的错误消息                            #region 1.0 设置连接Excel组件的连接字符串                // 获取导入文件的扩展名                string fileExt = Path.GetExtension(filePath).ToLower();                // Excel组件连接字符串                string excelConnStr = string.Empty;                if (fileExt.Equals(".xlsx"))                {                    // "HDR=Yes;" 表示第一行是标题行, 不是数据。"HDR=No;" 则表示从第一行开始就是数据。                     excelConnStr = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=‘Excel 12.0;HDR=No;IMEX=1‘", filePath);                }                else if (fileExt.Equals(".xls"))                {                    excelConnStr = string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=‘Excel 8.0;HDR=No;IMEX=1‘", filePath);                }                else                {                    // "不是Excel文件，请重新选择文件导入！";                       msg = this.GetResourceString("NotExcelFile");                    return false;                }                 #endregion                string loginUser = this.LoginUser;                // 总部营业日期                string hqAccountDate = Convert.ToDateTime(new BLSystemInfo().GetSysValue(2033, 1)).ToString("yyyyMM");                    DLSalesOtherMakerSearch dal = new DLSalesOtherMakerSearch();                OleDbConnection oleConn = null;     // 连接Excel文件的连接对象                DataSet ds = null;                                  DataTable dtExcel = null;           // 存储Excel文件数据的内存表                DataTable dtInsert = null;          // 存储处理后的Excel文件数据的内存表                try                {                    #region 2.0 通过OleDbConnection对象从Excel文件中获取数据并存放在内存DataTable中（变量：dtExcel）                    oleConn = new OleDbConnection(excelConnStr);                    oleConn.Open();                    DataTable schemaTable = oleConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);                    // 获取Excel中sheet名称                    string tableName = string.Format("[{0}]", schemaTable.Rows[0][2].ToString().Trim());                    string sql = string.Format("select * from {0}", tableName);                    ds = new DataSet();                    using (OleDbDataAdapter adapter = new OleDbDataAdapter(sql, oleConn))                    {                                                adapter.Fill(ds, tableName);                    }                                        // 第1，2,3行都是标题行                    dtExcel = ds.Tables[tableName];                    if (dtExcel.Rows.Count <= 3)                    {                        // "选择的文件没有需要导入的数据，请重新选择文件导入！";                        msg = this.GetResourceString("ExcelFileNoData");                        return false;                    }                    // 判断Excel中是否存在相同的店铺号，若存在则提示用户，不导入                    // 根据店号进行分组                    var storeList = from s in dtExcel.AsEnumerable()                                    where s.Field<string>(0) != null                                    group s by new { f = s.Field<string>(0).ToUpper() } into g                                                                        select new                                    {                                        StoreId = g.Key.f,                                        StoreCount = g.Count()                                    };                    // 查出有多行店号的数据                    storeList = storeList.Where(item => item.StoreCount > 1);                    if (storeList.Count() > 0)                    {                        string storeIds = string.Empty;                        storeList.ToList().ForEach(item => storeIds += item.StoreId + ",");                        // [{0}]店铺存在多行销售数据，请删除多余行的数据，每个店铺只保留一行！                        msg = this.GetResourceString("StoreSalesDataRepeated", storeIds.Substring(0, storeIds.Length - 1));                        return false;                    }                    #endregion                    #region 3.0 判断Excel表中的数据是否合法并转换存放在符合数据库表结构的内存表中（变量：dtInsert）                    // 创建要插入到数据库中的内存表dtInsert                    dtInsert = dal.GetOtherMakerSaleTblScheme();                    // Excel中的第三行是竞品品牌名称，故从第四行开始循环遍历dtExcel（注意下标为3）                    DataRow rowExcel;                    DataRow rowInsert;                    // 获取所有竞品品牌主档数据                    List<OtherMakerMasTbl> otherMakerMasList = dal.GetOtherMakerMasList();                    if (otherMakerMasList == null || otherMakerMasList.Count <= 0)                    {                        // 没有竞品品牌主档数据，请联系系统管理员重新导入！                        msg = this.GetResourceString("OtherMakerMasDataNotExist");                        return false;                    }                    // 循环遍历Excel中的第三行竞品品牌名称，获取对应的竞品品牌代码，并替换掉竞品品牌名称                    // 从第二列开始循环遍历竞品品牌                    OtherMakerMasTbl otherMakerMasModel = null;                    string notExistOtherMakerStr = string.Empty;                    Dictionary<string, string> dicOtherMakerMas = new Dictionary<string, string>();                    for (int j = 1; j < dtExcel.Columns.Count; j++)                    {                        otherMakerMasModel = otherMakerMasList.SingleOrDefault(item => item.OtherMakerNm.Trim().Equals(dtExcel.Rows[2][j].ToString().Trim()));                        if (otherMakerMasModel == null)                        {                            // 在竞品品牌主档中未找到 {0} 该品牌对应的数据，请仔细核对数据重新导入！                            //msg = this.GetResourceString("OtherMakerNotFound", dtExcel.Rows[2][j].ToString().Trim());                            notExistOtherMakerStr += "[" + dtExcel.Rows[2][j].ToString().Trim() + "],";                        }                        else                        {                            dicOtherMakerMas.Add(otherMakerMasModel.OtherMakerCd, otherMakerMasModel.OtherMakerNm.Trim());                            dtExcel.Rows[2][j] = otherMakerMasModel.OtherMakerCd;                        }                    }                    if (!string.IsNullOrEmpty(notExistOtherMakerStr))                    {                        msg = this.GetResourceString("OtherMakerNotFound", notExistOtherMakerStr.Substring(0, notExistOtherMakerStr.Length - 1));                        return false;                    }                    for (int i = 3; i < dtExcel.Rows.Count; i++)                    {                        rowExcel = dtExcel.Rows[i];                        // 从第二列开始循环遍历竞品品牌                        for (int j = 1; j < dtExcel.Columns.Count; j++)                        {                            // 如果店号为空，则Continue                            if (dtExcel.Rows[i][0] == null || string.IsNullOrEmpty(dtExcel.Rows[i][0].ToString()))                            {                                continue;                            }                            // 如果金额数字为空，则Continue                            if (dtExcel.Rows[i][j] == null || string.IsNullOrEmpty(dtExcel.Rows[i][j].ToString()))                            {                                continue;                            }                            rowInsert = dtInsert.NewRow();                            rowInsert["OwnerId"] = ownerId;                            rowInsert["CustomerCd"] = dtExcel.Rows[i][0];                            rowInsert["OtherMakerCd"] = dtExcel.Rows[2][j].ToString();                             rowInsert["OtherSaleYM"] = saleYM;                            try                            {                                rowInsert["OtherSaleMoney"] = Convert.ToDecimal(dtExcel.Rows[i][j]);                            }                            catch (Exception)                            {                                // 店号为{0}的竞品品牌{1}填写的金额数字{2}有误，请重新导入                                msg += this.GetResourceString("SaleAmoutError", dtExcel.Rows[i][0], dicOtherMakerMas[dtExcel.Rows[2][j].ToString()], dtExcel.Rows[i][j]) + "<br/>";                                continue;                            }                            rowInsert["SCHInput"] = "1";                            rowInsert["DayEndNo"] = dtExcel.Rows[i][0] + hqAccountDate;                            rowInsert["BCCD"] = loginUser;                            rowInsert["UpdateMan"] = loginUser;                            rowInsert["UpdateTime"] = DateTime.Now;                            rowInsert["Origsys"] = "2";                            dtInsert.Rows.Add(rowInsert);                        }                    }                    if (!string.IsNullOrEmpty(msg))                    {                        return false;                    }                    #endregion                    #region 4.0 创建数据库临时表#temp，并把dtInsert内存表中的数据插入到临时表，再判断数据有效性并导入到正式表中                    msg = dal.InsertOtherMakerSaleToTempTable(dtInsert);                                        if (!string.IsNullOrEmpty(msg))                    {                        return false;                    }                    #endregion                }                catch (Exception)                {                    throw;                }                finally                {                    #region 资源释放                    if (oleConn != null)                    {                        oleConn.Dispose();                        oleConn.Close();                    }                    if (dtInsert != null)                    {                        dtInsert.Dispose();                    }                    if (dtExcel != null)                    {                        dtExcel.Dispose();                    }                    if (ds != null)                    {                        ds.Dispose();                    }                     #endregion                }                ts.Complete();                                return true;            }        }

/// <summary>        /// 创建“竞品品牌销售数据临时表”，判断数据有效性，导入到正式表中        /// </summary>        /// <param name="dtInsert">店铺竞品品牌销售数据</param>        /// <returns></returns>        public string InsertOtherMakerSaleToTempTable(DataTable dtInsert)        {            string msg = "";            string tempTableName = "#OtherMakerSaleTemp";            StringBuilder sql = new StringBuilder();            // 1 先删除临时表            sql.AppendFormat(" if object_id(N‘{0}‘,N‘U‘) is not null drop table {1}", tempTableName, tempTableName).AppendLine();            this.DataAccessClient.ExecuteNonQuery(sql.ToString());            // 2 创建临时表            sql.Remove(0, sql.Length);                        sql.AppendFormat(" select * into {0} from OtherSaleMonthTbl where 1=0 ;", tempTableName).AppendLine();                        // 2.1 为临时表添加主键            sql.AppendFormat(" alter table {0} add primary key(ownerid,customercd,othermakercd,othersaleym); ", tempTableName).AppendLine();            this.DataAccessClient.ExecuteNonQuery(sql.ToString());            // 3.往临时表中插入数据            #region DeleteCode            /*            string connString = Inf.DevLib.Data.DataAccess.DataAccessFactory.ConnectionString;            using (SqlConnection conn = new SqlConnection(connString))            {                if (conn.State != System.Data.ConnectionState.Open)                {                    conn.Open();                }                                using (SqlBulkCopy bc = new SqlBulkCopy(conn))                {                    bc.BulkCopyTimeout = 300;                    bc.DestinationTableName = tempTableName;                    bc.WriteToServer(dtInsert);                }                conn.Close();            }            */            #endregion            this.DataAccessClient.BatchInsert(dtInsert, tempTableName, null, 100);            // 4.判断临时表中的店号在ShopRelateMakerTBL表中是否存在，若不存在则给予提示并返回            sql.Remove(0, sql.Length);            sql.AppendFormat("select distinct a.CustomerCd from {0} a", tempTableName).AppendLine();            sql.AppendLine("where not exists");            sql.AppendLine("(select b.CustomerCd from ShopRelateMakerTBL b where a.OwnerId = b.OwnerId and a.CustomerCd = b.CustomerCd )");            // 在商店别他品牌对应表（ShopRelateMakerTBL）中不存在的店号            DataTable notExistsTable = this.DataAccessClient.ExecuteDataTable(sql.ToString());            if (notExistsTable != null && notExistsTable.Rows.Count > 0)            {                foreach (DataRow row in notExistsTable.Rows)                {                    msg += row[0] + ",";                }                             // 店号在商店别他品牌对应表中不存在，请仔细核对数据重新导入！                msg = this.GetResourceString("StoreIdNotExist", msg.Substring(0, msg.Length - 1));                return msg;            }            // 5.判断临时表中店铺所有的竞品品牌在ShopRelateMakerTBL表中是否存在，若不存在则给予提示并返回            sql.Remove(0, sql.Length);            sql.AppendLine("select t.*,mst.OtherMakerNm from ");            sql.AppendLine("(");            sql.AppendLine("    select omst.OwnerId, omst.CustomerCd,omst.OtherMakerCd");            sql.AppendFormat("    from {0} omst", tempTableName).AppendLine();            sql.AppendLine("    where not exists");            sql.AppendLine("    (select srmt.OtherMakerCd from ShopRelateMakerTBL srmt ");            sql.AppendLine("    where omst.OwnerId = srmt.OwnerId and omst.CustomerCd = srmt.CustomerCd and omst.OtherMakerCd = srmt.OtherMakerCd )");            sql.AppendLine(") t ");            sql.AppendLine("inner join OtherMakerMasTbl mst");            sql.AppendLine("         on t.OwnerId = mst.OwnerId");            sql.AppendLine("         and t.OtherMakerCd = mst.OtherMakerCd");            List<ShopRelateMakerTBL> errorOtherMakerMasList = new List<ShopRelateMakerTBL>();            this.DataAccessClient.FillQuery(errorOtherMakerMasList, sql.ToString());            if (errorOtherMakerMasList.Count > 0)            {                var q = from e in errorOtherMakerMasList                        group e by new { e.OwnerId, e.CustomerCd } into g                        select new                        {                            StoreId = g.Key.CustomerCd,                            OtherMakerNm = string.Join(",", g.Select(i => i.OtherMakerNm.Trim()))                        };                string str = string.Empty;                q.ToList().ForEach(item => str += string.Format("[{0}：{1}]", item.StoreId, item.OtherMakerNm) + ",");                // 店号{0}的竞品品牌主档数据在商店别他品牌对应表中不存在，请仔细核对数据重新导入！                msg = this.GetResourceString("StoreOtherMakerNotExist", str.Substring(0, str.Length - 1));                return msg;            }            // 6.把临时表中的数据插入到正式表OtherSaleMonthTbl（先删再插）            sql.Remove(0, sql.Length);            // 6.1 先删除 正式表中未确认的店铺精品销售数据            sql.AppendLine("delete A ");            sql.AppendLine("from OtherSaleMonthTbl A");            sql.AppendFormat("inner join {0} B", tempTableName).AppendLine();            sql.AppendLine("        on A.OwnerId = B.OwnerId");            sql.AppendLine("        and A.CustomerCd = B.CustomerCd");            sql.AppendLine("        and A.OtherSaleYM = B.OtherSaleYM");            sql.AppendLine(" where ( A.ConfirmFlg != ‘1‘ or A.ConfirmFlg is null) "); // 未确认            this.DataAccessClient.ExecuteNonQuery(sql.ToString());            // 6.2 再删除 临时表中在正式表中已确认过的店铺销售数据            sql.Remove(0, sql.Length);            sql.AppendLine("delete B ");            sql.AppendFormat("from {0} B", tempTableName).AppendLine();            sql.AppendLine(" inner join OtherSaleMonthTbl A");                        sql.AppendLine("        on A.OwnerId = B.OwnerId");            sql.AppendLine("        and A.CustomerCd = B.CustomerCd");            sql.AppendLine("        and A.OtherSaleYM = B.OtherSaleYM");            sql.AppendLine(" where A.ConfirmFlg = ‘1‘  "); // 已确认            this.DataAccessClient.ExecuteNonQuery(sql.ToString());            // 6.3 最后把临时表中的数据全部插入正式表            sql.Remove(0, sql.Length);            sql.AppendLine("INSERT INTO OtherSaleMonthTbl");            sql.AppendLine("select * ");            sql.AppendFormat("from {0} ", tempTableName).AppendLine();            this.DataAccessClient.ExecuteNonQuery(sql.ToString());            // 7.删除临时表            sql.Remove(0, sql.Length);            sql.AppendFormat("drop table {0}", tempTableName).AppendLine();            this.DataAccessClient.ExecuteNonQuery(sql.ToString());            return msg;        }