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);
}
/// <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);
}
}