Exemplo n.º 1
0
        private bool DealDataImported2(string fileName, out string msg)
        {
            System.Diagnostics.Debug.WriteLine("[Handler]DealDataImported begin...");
            bool success = true;

            msg = "";
            int sucYesCount  = 0; //回访结果:是
            int sucNoCount   = 0; //回访结果:否
            int FailCount    = 0; //回写失败
            int noOrderCount = 0; //未找到匹配订单的电话

            List <ExcelData> excelDataList = new List <ExcelData>();
            ExcelData        data          = new ExcelData();

            //(1)连接EXCEL文件
            string ext     = Path.GetExtension(fileName);
            string connStr = string.Empty;

            if (ext.ToLower() == ".xls")
            {
                connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @fileName + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";
            }
            else
            {
                //throw new Exception("上传文件应为xls或者xlsx格式的文件");
                throw new Exception("上传文件应为xls格式的文件");
            }

            using (OleDbConnection conn = new OleDbConnection(connStr))
            {
                conn.Open();

                //返回Excel的架构,包括各个sheet表的名称,类型,创建时间和修改时间等
                DataTable dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
                //包含excel中表名的字符串数组
                string firstSheetName = dtSheetName.Rows[0]["TABLE_NAME"].ToString();
                //读取第一个sheet填充数据sheetConfig.Name.Replace(".", "#") + "$"
                OleDbCommand command = new OleDbCommand("select * from [" + firstSheetName.Replace(".", "#") + "]", conn);
                IDataReader  idr     = command.ExecuteReader();

                string phone                = Constant.STRING_INVALID_VALUE;
                string IsReturnVisit        = Constant.STRING_INVALID_VALUE;
                GroupOrderDealHelper helper = new GroupOrderDealHelper();
                helper.userId = Convert.ToInt32(RequestUserID);

                List <myData> myDataList = new List <myData>();
                myData        mydataYes  = new myData();
                myData        mydataNo   = new myData();
                string        whereYes   = "";
                string        whereNo    = "";

                int rowNum = 0;
                int ibatch = 0;
                while (idr.Read())
                {
                    if (!string.IsNullOrEmpty(idr[0].ToString().Trim()))
                    {
                        phone = idr[0].ToString().Trim();
                    }

                    if (!string.IsNullOrEmpty(idr[1].ToString().Trim()))
                    {
                        IsReturnVisit = idr[1].ToString().Trim();
                    }

                    if (phone != Constant.STRING_INVALID_VALUE && IsReturnVisit != Constant.STRING_INVALID_VALUE)
                    {
                        if (IsReturnVisit == "是")
                        {
                            whereYes += "'" + phone + "',";
                        }
                        else
                        {
                            whereNo += "'" + phone + "',";
                        }
                    }

                    rowNum++;

                    ibatch = rowNum / 1000;
                    int iremainder = rowNum % 1000;

                    if (ibatch > 0 && iremainder == 0)
                    {
                        if (whereYes.EndsWith(","))
                        {
                            whereYes = whereYes.Substring(0, whereYes.Length - 1);
                        }

                        if (whereNo.EndsWith(","))
                        {
                            whereNo = whereNo.Substring(0, whereNo.Length - 1);
                        }

                        mydataYes.whereSql      = whereYes;
                        mydataYes.IsReturnVisit = "是";
                        mydataNo.whereSql       = whereNo;
                        mydataNo.IsReturnVisit  = "否";

                        myDataList.Add(mydataYes);
                        myDataList.Add(mydataNo);

                        whereYes = "";
                        whereNo  = "";
                    }
                }

                if (whereYes != "")
                {
                    mydataYes.whereSql      = whereYes;
                    mydataYes.IsReturnVisit = "是";
                    myDataList.Add(mydataYes);
                }

                if (whereNo != "")
                {
                    mydataNo.whereSql      = whereNo;
                    mydataNo.IsReturnVisit = "否";
                    myDataList.Add(mydataNo);
                }

                foreach (myData item in myDataList)
                {
                }

                while (idr.Read())
                {
                    rowNum++;
                    System.Diagnostics.Debug.WriteLine("[Handler]DealDataImported rowNum..." + rowNum);
                    System.Diagnostics.Debug.WriteLine("[Handler]DealDataImported 电话号码..." + idr[0].ToString().Trim());


                    if (!string.IsNullOrEmpty(idr[0].ToString().Trim()))
                    {
                        phone = idr[0].ToString().Trim();
                    }

                    if (!string.IsNullOrEmpty(idr[1].ToString().Trim()))
                    {
                        IsReturnVisit = idr[1].ToString().Trim();
                    }


                    //先根据电话获取订单对应任务ID
                    if (phone != Constant.STRING_INVALID_VALUE && IsReturnVisit != Constant.STRING_INVALID_VALUE)
                    {
                        Entities.QueryGroupOrder query = new Entities.QueryGroupOrder();
                        query.CustomerTel = phone;
                        int       total = 0;
                        DataTable dt    = BLL.GroupOrder.Instance.GetGroupOrder(query, "", 1, 999999, out total);

                        if (dt.Rows.Count > 0)
                        {
                            foreach (DataRow row in dt.Rows)
                            {
                                long taskid = Constant.INT_INVALID_VALUE;

                                if (!string.IsNullOrEmpty(row["TaskID"].ToString().Trim()))
                                {
                                    taskid = Convert.ToInt32(row["TaskID"].ToString().Trim());
                                }

                                if (taskid == Constant.INT_INVALID_VALUE)
                                {
                                    msg = "{\"Result\":false,\"Msg\":\"任务不能为空!\"}";
                                    continue;
                                }

                                switch (IsReturnVisit)
                                {
                                case "是":
                                    helper.RequestIsReturnVisit = ((int)Entities.IsReturnVisit.Yes).ToString();
                                    break;

                                case "否":
                                    helper.RequestIsReturnVisit = ((int)Entities.IsReturnVisit.No).ToString();
                                    break;

                                default:
                                    helper.RequestIsReturnVisit = ((int)Entities.IsReturnVisit.UnKnown).ToString();
                                    break;
                                }

                                helper.RequestTaskID = row["TaskID"].ToString().Trim();
                                helper.DealOrder(2, out msg);

                                if (msg == "")
                                {
                                    if (IsReturnVisit == "是")
                                    {
                                        sucYesCount++;
                                    }
                                    else
                                    {
                                        sucNoCount++;
                                    }
                                }
                                else
                                {
                                    FailCount++;
                                    //失败任务,需生成对Excel文件
                                    //字段:电话号码、处理结果、任务ID
                                    //ExcelData data = new ExcelData();
                                    data.Phone  = phone;
                                    data.TaskID = helper.RequestTaskID;
                                    data.Msg    = msg;

                                    excelDataList.Add(data);
                                }
                            }
                        }
                        else
                        {
                            //未找到订单的 电话号码
                            noOrderCount++;
                        }
                    }
                }
                idr.Close();
                idr = null;
            }

            msg = "{\"Result\":true,\"Msg\":\"成功回写处理结果:是,任务" + sucYesCount + "条!\n成功回写处理结果:否,任务" + sucNoCount + "条!\n失败任务" + FailCount + "条!\n未找到订单电话记录" + noOrderCount + "条!}";

            if (FailCount > 0)
            {
                //ExcelHelper excelhelper = new ExcelHelper();
                //excelhelper.ExprotExcel(excelDataList);
            }

            return(success);
        }
Exemplo n.º 2
0
        /// <summary>
        /// 处理导入的数据
        /// </summary>
        private bool DealDataImported(string fileName, out string msg)
        {
            System.Diagnostics.Debug.WriteLine("[HandlerImport]DealDataImported begin...");
            BLL.Loger.Log4Net.Info("[HandlerImport]DealDataImported begin...");
            bool success = true;

            msg = "";
            int sucYesCount = 0; //回访结果:是
            int sucNoCount  = 0; //回访结果:否
            int FailCount   = 0; //回写失败

            List <ExcelData> excelDataList = new List <ExcelData>();

            //(1)连接EXCEL文件
            string ext     = Path.GetExtension(fileName);
            string connStr = string.Empty;

            if (ext.ToLower() == ".xls")
            {
                connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @fileName + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";
            }
            else
            {
                //throw new Exception("上传文件应为xls或者xlsx格式的文件");
                throw new Exception("上传文件应为xls格式的文件");
            }

            using (OleDbConnection conn = new OleDbConnection(connStr))
            {
                conn.Open();

                //返回Excel的架构,包括各个sheet表的名称,类型,创建时间和修改时间等
                DataTable dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
                //包含excel中表名的字符串数组
                string firstSheetName = dtSheetName.Rows[0]["TABLE_NAME"].ToString();
                //读取第一个sheet填充数据sheetConfig.Name.Replace(".", "#") + "$"
                OleDbCommand command = new OleDbCommand("select * from [" + firstSheetName.Replace(".", "#") + "]", conn);
                IDataReader  idr     = command.ExecuteReader();

                string phone                = Constant.STRING_INVALID_VALUE;
                string IsReturnVisit        = Constant.STRING_INVALID_VALUE;
                GroupOrderDealHelper helper = new GroupOrderDealHelper();
                helper.userId = Convert.ToInt32(RequestUserID);

                List <myData> myDataList = new List <myData>();

                string whereYes = "";
                string whereNo  = "";

                int rowNum = 0;
                int ibatch = 0;
                while (idr.Read())
                {
                    if (!string.IsNullOrEmpty(idr[0].ToString().Trim()))
                    {
                        phone = idr[0].ToString().Trim();
                    }

                    if (!string.IsNullOrEmpty(idr[1].ToString().Trim()))
                    {
                        IsReturnVisit = idr[1].ToString().Trim();
                    }

                    if (phone != Constant.STRING_INVALID_VALUE && IsReturnVisit != Constant.STRING_INVALID_VALUE)
                    {
                        if (IsReturnVisit == "是")
                        {
                            whereYes += "'" + phone + "',";
                        }
                        else
                        {
                            whereNo += "'" + phone + "',";
                        }
                    }

                    rowNum++;

                    ibatch = rowNum / 1000;
                    int iremainder = rowNum % 1000;

                    if (ibatch > 0 && iremainder == 0)
                    {
                        if (whereYes.EndsWith(","))
                        {
                            whereYes = whereYes.Substring(0, whereYes.Length - 1);
                        }

                        if (whereNo.EndsWith(","))
                        {
                            whereNo = whereNo.Substring(0, whereNo.Length - 1);
                        }

                        myData mydataYes = new myData();
                        myData mydataNo  = new myData();

                        mydataYes.whereSql      = " AND CustomerTel IN(" + BLL.Util.SqlFilterByInCondition(whereYes) + ")";
                        mydataYes.IsReturnVisit = "是";
                        myDataList.Add(mydataYes);

                        mydataNo.whereSql      = " AND CustomerTel IN(" + BLL.Util.SqlFilterByInCondition(whereNo) + ")";
                        mydataNo.IsReturnVisit = "否";
                        myDataList.Add(mydataNo);

                        whereYes = "";
                        whereNo  = "";
                    }

                    BLL.Loger.Log4Net.Info("[HandlerImport]DealDataImported rowNum..." + rowNum + ",phone is:" + phone + "...");
                }

                if (whereYes != "")
                {
                    if (whereYes.EndsWith(","))
                    {
                        whereYes = whereYes.Substring(0, whereYes.Length - 1);
                    }
                    myData mydataYes = new myData();

                    mydataYes.whereSql      = " AND CustomerTel IN(" + BLL.Util.SqlFilterByInCondition(whereYes) + ")";
                    mydataYes.IsReturnVisit = "是";
                    myDataList.Add(mydataYes);
                }

                if (whereNo != "")
                {
                    if (whereNo.EndsWith(","))
                    {
                        whereNo = whereNo.Substring(0, whereNo.Length - 1);
                    }

                    myData mydataNo = new myData();

                    mydataNo.whereSql      = " AND CustomerTel IN(" + BLL.Util.SqlFilterByInCondition(whereNo) + ")";
                    mydataNo.IsReturnVisit = "否";
                    myDataList.Add(mydataNo);
                }

                int i = 0;
                foreach (myData item in myDataList)
                {
                    i++;
                    BLL.Loger.Log4Net.Info("[HandlerImport]DealDataImported myDataList..批次:" + i + ",数据:" + item.whereSql + "!");

                    DataTable dt = BLL.GroupOrder.Instance.GetGroupOrder(item.whereSql);

                    BLL.Loger.Log4Net.Info("[HandlerImport]DealDataImported myDataList..批次:" + i + ",匹配订单数量:" + dt.Rows.Count + "条!");
                    if (dt.Rows.Count > 0)
                    {
                        foreach (DataRow row in dt.Rows)
                        {
                            switch (item.IsReturnVisit)
                            {
                            case "是":
                                helper.RequestIsReturnVisit = ((int)Entities.IsReturnVisit.Yes).ToString();
                                break;

                            case "否":
                                helper.RequestIsReturnVisit = ((int)Entities.IsReturnVisit.No).ToString();
                                break;

                            default:
                                helper.RequestIsReturnVisit = ((int)Entities.IsReturnVisit.UnKnown).ToString();
                                break;
                            }

                            helper.RequestTaskID = row["TaskID"].ToString().Trim();

                            try
                            {
                                helper.DealOrder(2, out msg);
                            }
                            catch (Exception ex)
                            {
                                msg = ex.Message;
                                BLL.Loger.Log4Net.Info("[HandlerImport]DealDataImported helper.DealOrder errorMessage is:" + ex.Message + "!");
                                BLL.Loger.Log4Net.Info("[HandlerImport]DealDataImported helper.DealOrder errorStackTrace is:" + ex.StackTrace + "!");
                            }

                            if (msg == "")
                            {
                                if (item.IsReturnVisit == "是")
                                {
                                    sucYesCount++;
                                }
                                else
                                {
                                    sucNoCount++;
                                }
                            }
                            else
                            {
                                FailCount++;
                                //失败任务,需生成对Excel文件
                                //字段:电话号码、处理结果、任务ID
                                ExcelData data = new ExcelData();
                                data.Phone  = row["CustomerTel"].ToString().Trim();
                                data.TaskID = helper.RequestTaskID;
                                data.Msg    = msg;

                                excelDataList.Add(data);

                                BLL.Loger.Log4Net.Info("[HandlerImport]DealDataImported FailCount..." + FailCount + ",phone is:" + data.Phone + ",TaskID is:" + data.TaskID + ",errorMsg is:" + data.Msg + "!");
                            }
                        }
                    }
                    else
                    {
                        //未找到订单的 电话号码
                        //noOrderCount++;
                        BLL.Loger.Log4Net.Info("[HandlerImport]未找到订单数据...批次:" + i + ",数据:" + item.whereSql + "!");
                    }
                }

                //msg = "{\"Result\":true,\"Msg\":\"成功回写处理结果:是,任务" + sucYesCount + "条!\n成功回写处理结果:否,任务" + sucNoCount + "条!\n失败任务" + FailCount + "条!\n未找到订单电话记录" + noOrderCount + "条!}";
                //msg = "{\"Result\":true,\"Msg\":\"成功回写处理结果:是,任务" + sucYesCount + "条!\"}";
                msg = "{\"Result\":true,\"Msg\":\"成功回写处理结果:是,任务" + sucYesCount + "条!\\n成功回写处理结果:否,任务" + sucNoCount + "条!\\n失败任务" + FailCount + "条!\",\"FailCount\":\"" + FailCount + "\"}";

                //查看是否有失败数据
                if (FailCount > 0)
                {
                    StringBuilder sb = new StringBuilder();
                    foreach (ExcelData item in excelDataList)
                    {
                        sb.Append(item.Phone + "," + item.TaskID + "," + item.Msg + ";");
                    }

                    string errorData = sb.ToString();
                    if (errorData != "")
                    {
                        errorData = errorData.Substring(0, errorData.Length - 1);
                        msg       = "{\"Result\":true,\"Msg\":\"成功回写处理结果:是,任务" + sucYesCount + "条!\\n成功回写处理结果:否,任务" + sucNoCount + "条!\\n失败任务" + FailCount + "条!\",\"FailCount\":\"" + FailCount + "\",\"ErrorData\":\"" + errorData + "\"}";
                    }
                }

                BLL.Loger.Log4Net.Info("[HandlerImport]DealDataImported bye...");
                return(success);
            }
        }