//绑定数据
private void BindData()
{
if (RequestTaskID != "")
{
DataTable dt = new DataTable();
QueryCustHistoryInfo query = new QueryCustHistoryInfo();
query.TaskID = RequestTaskID;
int count;
dt = BLL.CustHistoryInfo.Instance.GetCustHistoryInfo(query, "", 1, 10000, Entities.CustHistoryInfo.SelectFieldStr, out count);
if (dt.Rows.Count > 0)
{
switch (int.Parse(dt.Rows[0]["QuestionQuality"].ToString()))
{
case (int)Entities.QuestionNature.NatureCommon:
QuestionQuality.InnerText = "普通";
break;
case (int)Entities.QuestionNature.NatureUrgent:
QuestionQuality.InnerText = "紧急";
break;
}
LastTreatmentTime.InnerText = dt.Rows[0]["LastTreatmentTime"].ToString();
if (dt.Rows[0]["IsComplaint"].ToString().ToLower() == "true")
{
IsComplaint.InnerText = "是";
}
else if (dt.Rows[0]["IsComplaint"].ToString().ToLower() == "false")
{
IsComplaint.InnerText = "否";
}
}
}
}
/// <summary>
/// 按照查询条件查询
/// </summary>
/// <param name="query">查询条件</param>
/// <param name="order">排序</param>
/// <param name="currentPage">页号,-1不分页</param>
/// <param name="pageSize">每页记录数</param>
/// <param name="totalCount">总行数</param>
/// <returns>集合</returns>
public DataTable GetCustHistoryInfo(QueryCustHistoryInfo query, string order, int currentPage, int pageSize, string fields, out int totalCount)
{
string where = GetWhereStr(query);
DataSet ds;
SqlParameter[] parameters =
{
new SqlParameter("@where", SqlDbType.VarChar, 40000),
new SqlParameter("@order", SqlDbType.VarChar, 200),
new SqlParameter("@pagesize", SqlDbType.Int, 4),
new SqlParameter("@indexpage", SqlDbType.Int, 4),
new SqlParameter("@fields", SqlDbType.VarChar, 8000),
new SqlParameter("@totalRecorder", SqlDbType.Int, 10)
};
parameters[0].Value = where;
parameters[1].Value = order;
parameters[2].Value = pageSize;
parameters[3].Value = currentPage;
parameters[4].Value = fields;
parameters[5].Direction = ParameterDirection.Output;
ds = SqlHelper.ExecuteDataset(CONNECTIONSTRINGS, CommandType.StoredProcedure, P_CUSTHISTORYINFO_SELECT, parameters);
totalCount = (int)(parameters[5].Value);
return(ds.Tables[0]);
}
/// IM根据客户号获取业务记录
/// <summary>
/// IM根据客户号获取业务记录
/// </summary>
/// <param name="userid"></param>
/// <param name="query"></param>
/// <param name="order"></param>
/// <param name="currentPage"></param>
/// <param name="pageSize"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public DataTable GetCustBaseInfo_ServiceRecord_IM(int userid, QueryCustHistoryInfo query, int currentPage, int pageSize, out int totalCount)
{
DataTable dt = new DataTable();
dt.TableName = "CustHistoryInfo";
totalCount = 0;
try
{
string phones = "SELECT Tel FROM dbo.CustTel WHERE CustID='" + query.CustID + "'";
DataTable mydt = Dal.CallRecord_ORIG.Instance.GetCustBaseInfo_ServiceRecord(phones, "ModifyTime desc", currentPage, pageSize, out totalCount);
dt.Columns.Add("AssignUserID", typeof(string)); //提交人
dt.Columns.Add("Status", typeof(string)); //状态
dt.Columns.Add("TaskID", typeof(string)); //任务ID
dt.Columns.Add("TaskUrl", typeof(string)); //查看页地址
dt.Columns.Add("Content", typeof(string)); //联系记录
dt.Columns.Add("RecordType", typeof(string)); //记录类型
dt.Columns.Add("LastOperTime", typeof(DateTime)); //提交时间
string url_head = ConfigurationUtil.GetAppSettingValue("IMTaskMiddleDomain") + "/IMTaskMiddle.aspx?" + "UserID=" + System.Web.HttpUtility.UrlEncode(userid.ToString());
foreach (DataRow row in mydt.Rows)
{
string url = "";
//任务状态
string taskstatus = CommonFunction.ObjectToString(row["TaskStatus"]);
string stopstatus = CommonFunction.ObjectToString(row["StopStatus"]);
//类型
string bussinessType = CommonFunction.ObjectToString(row["BusinessType"]);
//客户核实类型
string applytype = CommonFunction.ObjectToString(row["ApplyType"]);
//任务ID
string taskid = CommonFunction.ObjectToString(row["TaskID"]);
//来源
string tasksource = CommonFunction.ObjectToString(row["TaskSource"]);
//创建人
string createuserid = CommonFunction.ObjectToString(row["CreateUserID"]);
DataRow dr = dt.NewRow();
dr["AssignUserID"] = createuserid;
dr["Status"] = GetStatusText(CommonFunction.ObjectToInteger(bussinessType), taskstatus, stopstatus, applytype);
dr["TaskID"] = taskid;
dr["RecordType"] = tasksource;
url += "&BussinessType=" + System.Web.HttpUtility.UrlEncode(bussinessType);
url += "&TaskID=" + System.Web.HttpUtility.UrlEncode(taskid);
url += "&BGID=" + System.Web.HttpUtility.UrlEncode(Convert.ToString(row["BGID"]));
url += "&SCID=" + System.Web.HttpUtility.UrlEncode(Convert.ToString(row["SCID"]));
dr["TaskUrl"] = url_head + url;
dr["Content"] = Convert.ToString(row["Content"]);
dr["LastOperTime"] = CommonFunction.ObjectToString(row["LastOperTime"]);
dt.Rows.Add(dr);
}
}
catch (Exception ex)
{
BLL.Loger.Log4Net.Info("[CustHistoryInf_BLL]GetCustHistoryInfoForWork_IM操作出错!errorMessage:" + ex.Message);
BLL.Loger.Log4Net.Info("[CustHistoryInf_BLL]GetCustHistoryInfoForWork_IM操作出错!errorStackTrace:" + ex.StackTrace);
}
return(dt);
}
//绑定数据
private void BindData()
{
if (RequestTaskID != "")
{
DataTable dt = new DataTable();
QueryCustHistoryInfo query = new QueryCustHistoryInfo();
query.TaskID = RequestTaskID;
int count;
dt = BLL.CustHistoryInfo.Instance.GetCustHistoryInfo(query, "", 1, 10000, Entities.CustHistoryInfo.SelectFieldStr, out count);
if (dt.Rows.Count > 0)
{
switch (int.Parse(dt.Rows[0]["QuestionQuality"].ToString()))
{
case (int)Entities.QuestionNature.NatureCommon:
QuestionQuality.InnerText = "普通";
break;
case (int)Entities.QuestionNature.NatureUrgent:
QuestionQuality.InnerText = "紧急";
break;
}
LastTreatmentTime.InnerText = CommonFunction.GetDateTimeStrForPage(dt.Rows[0]["LastTreatmentTime"].ToString());
if (dt.Rows[0]["IsComplaint"].ToString().ToLower() == "true")
{
chkIsComplaint.Checked = true;
}
//如果该任务已结束，则隐藏按钮
if (int.Parse(dt.Rows[0]["ProcessStatus"].ToString()) == (int)Entities.EnumTaskStatus.TaskStatusOver)
{
ulProcess.Visible = false;
divDeal.Visible = false;
spanBtnSubmit.Visible = false;
//spanHighOper.Visible = false;
//chkIsComplaint.Attributes.Add("disabled", "disabled");
}
else
{
bool right_AgreeTurnOut = BLL.Util.CheckRight(BLL.Util.GetLoginUserID(), "SYS024BUT1101");
bool right_TaskTurnOver = BLL.Util.CheckRight(BLL.Util.GetLoginUserID(), "SYS024BUT1102");
//如果有高级操作权限再判断
if (right_AgreeTurnOut && right_TaskTurnOver)
{
//判断是否是第一次转出，如果不是，则隐藏同意转出按钮
QueryCustHistoryLog query_Log = new QueryCustHistoryLog();
query_Log.TaskID = RequestTaskID;
query_Log.Action = (int)Entities.Action.ActionAgreeApplyTurn;
DataTable dt_Log = BLL.CustHistoryLog.Instance.GetCustHistoryLog(query_Log, "", 1, 10000, out count);
if (dt_Log.Rows.Count > 0)
{
btnTurnOut.Visible = false;
}
}
}
}
}
}
public QueryCustHistoryInfo GetQueryModel(string RequestTaskID, string RequestCustName, string RequestBeginTime, string RequestEndTime,
string RequestConsultID, string RequestQuestionType, string RequestQuestionQuality, string RequestIsComplaint,
string RequestProcessStatus, string RequestStatus, string RequestIsForwarding)
{
QueryCustHistoryInfo query = new QueryCustHistoryInfo();
if (RequestTaskID != "")
{
query.TaskID = RequestTaskID;
}
if (RequestCustName != "")
{
query.CustName = System.Web.HttpUtility.UrlDecode(RequestCustName);
}
if (RequestBeginTime != "")
{
query.BeginTime = RequestBeginTime;
}
if (RequestEndTime != "")
{
query.EndTime = RequestEndTime;
}
int consultID;
if (int.TryParse(RequestConsultID, out consultID))
{
query.ConsultID = consultID;
}
if (RequestQuestionType != "")
{
query.QuestionType = RequestQuestionType;
}
if (RequestQuestionQuality != "")
{
query.QuestionQualityStr = RequestQuestionQuality;
}
if (RequestIsComplaint != "")
{
query.IsCompaintStr = RequestIsComplaint;
}
query.ProcessStatusStr = RequestProcessStatus;
if (RequestStatus != "")
{
query.Status = RequestStatus;
}
if (RequestIsForwarding != "")
{
query.IsForwardingStr = RequestIsForwarding;
}
return(query);
}
//根据任务ID获取客户历史信息
private Entities.CustHistoryInfo getModelCustHistoryInfoByTaskID(string taskID)
{
Entities.CustHistoryInfo Model_CustHistoryInfo = new CustHistoryInfo();
if (taskID != "")
{
QueryCustHistoryInfo query_info = new QueryCustHistoryInfo();
query_info.TaskID = taskID;
int count;
DataTable dt_info = BLL.CustHistoryInfo.Instance.GetCustHistoryInfo(query_info, "", 1, 10000, Entities.CustHistoryInfo.SelectFieldStr, out count);
if (dt_info.Rows.Count == 1)
{
Model_CustHistoryInfo = BLL.CustHistoryInfo.Instance.GetCustHistoryInfo(long.Parse(dt_info.Rows[0]["RecID"].ToString()));
}
}
return(Model_CustHistoryInfo);
}
/// <summary>
/// 导出用
/// </summary>
/// <param name="query"></param>
/// <param name="fields"></param>
/// <returns></returns>
public DataTable GetCustHistoryInfoForExport(QueryCustHistoryInfo query, string fields)
{
string where = GetWhereStr(query);
DataSet ds;
SqlParameter[] parameters =
{
new SqlParameter("@where", SqlDbType.VarChar, 40000),
new SqlParameter("@fields", SqlDbType.VarChar, 8000)
};
parameters[0].Value = where;
parameters[1].Value = fields;
ds = SqlHelper.ExecuteDataset(CONNECTIONSTRINGS, CommandType.StoredProcedure, P_CUSTHISTORYINFO_EXPORT, parameters);
return(ds.Tables[0]);
}
//绑定数据
private void BindData()
{
if (RequestTaskID != "")
{
DataTable dt = new DataTable();
QueryCustHistoryInfo query = new QueryCustHistoryInfo();
query.TaskID = RequestTaskID;
int count;
dt = BLL.CustHistoryInfo.Instance.GetCustHistoryInfo(query, "", 1, 10000, Entities.CustHistoryInfo.SelectFieldStr, out count);
if (dt.Rows.Count > 0)
{
switch (int.Parse(dt.Rows[0]["QuestionQuality"].ToString()))
{
case (int)Entities.QuestionNature.NatureCommon:
QuestionQuality.InnerText = "普通";
break;
case (int)Entities.QuestionNature.NatureUrgent:
QuestionQuality.InnerText = "紧急";
break;
}
LastTreatmentTime.InnerText = dt.Rows[0]["LastTreatmentTime"].ToString() == "1900-1-1 0:00:00" ? "" : dt.Rows[0]["LastTreatmentTime"].ToString();
if (dt.Rows[0]["IsComplaint"].ToString().ToLower() == "true")
{
chkIsComplaint.Checked = true;
}
//如果该任务已结束，则隐藏按钮
if (int.Parse(dt.Rows[0]["ProcessStatus"].ToString()) == (int)Entities.EnumTaskStatus.TaskStatusOver)
{
liNextSolveUser.Visible = false;
divDeal.Visible = false;
spanBtnSubmit.Visible = false;
}
}
else
{
System.Web.Security.FormsAuthentication.SignOut();
Response.Write(@"<script language='javascript'>javascript:alert('无权限访问该页面！');
window.opener = null; window.open('', '_self'); window.close();</script>");
}
}
}
/// <summary>
/// 根据录音ID，查询客户历史记录
/// </summary>
public Entities.CustHistoryInfo GetCustHistoryInfoByCallRecordID(Int64 callRecordID)
{
QueryCustHistoryInfo query = new QueryCustHistoryInfo();
query.CallRecordID = callRecordID;
DataTable dt = new DataTable();
int count = 0;
dt = GetCustHistoryInfo(query, string.Empty, 1, 1, Entities.CustHistoryInfo.SelectFieldStr, out count);
if (count > 0)
{
return(LoadSingleCustHistoryInfo(dt.Rows[0]));
}
else
{
return(null);
}
}
public TaskDetail()
{
//根据任务ID获取主键ID 通过用户控件获取用户基本信息
QueryCustHistoryInfo query = new QueryCustHistoryInfo();
query.TaskID = RequestTaskID;
int count;
DataTable dt = BLL.CustHistoryInfo.Instance.GetCustHistoryInfo(query, "", 1, 10000, Entities.CustHistoryInfo.SelectFieldStr, out count);
if (dt.Rows.Count > 0)
{
Entities.CustHistoryInfo Model_CustHistoryInfo = BLL.CustHistoryInfo.Instance.GetCustHistoryInfo(long.Parse(dt.Rows[0]["RecID"].ToString()));
RequestConsultDataID = Model_CustHistoryInfo.ConsultDataID.ToString();
RequestRecordType = Model_CustHistoryInfo.RecordType.ToString();
RequestConsultID = Model_CustHistoryInfo.ConsultID.ToString();
RequestCustID = Model_CustHistoryInfo.CustID;
}
}
public ExternalTaskProcess()
{
if (RequestTaskID != "")
{
//根据任务ID获取主键ID 通过用户控件获取用户基本信息
QueryCustHistoryInfo query_info = new QueryCustHistoryInfo();
query_info.TaskID = RequestTaskID;
int count;
DataTable dt_info = BLL.CustHistoryInfo.Instance.GetCustHistoryInfo(query_info, "", 1, 10000, Entities.CustHistoryInfo.SelectFieldStr, out count);
if (dt_info.Rows.Count == 1)
{
Entities.CustHistoryInfo Model_CustHistoryInfo = BLL.CustHistoryInfo.Instance.GetCustHistoryInfo(long.Parse(dt_info.Rows[0]["RecID"].ToString()));
RequestConsultDataID = Model_CustHistoryInfo.ConsultDataID.ToString();
RequestRecordType = Model_CustHistoryInfo.RecordType.ToString();
RequestConsultID = Model_CustHistoryInfo.ConsultID.ToString();
RequestCustID = Model_CustHistoryInfo.CustID;
}
}
}
private void BindData()
{
string fields = Entities.CustHistoryInfo.SelectFieldStr;
string _consult = "-2";
if (RequestConsultID == "")
{
_consult = "0";
}
else
{
_consult = RequestConsultID;
}
QueryCustHistoryInfo query = BLL.CustHistoryInfo.Instance.GetQueryModel(RequestTaskID, RequestCustName, RequestBeginTime, RequestEndTime,
_consult, RequestQuestionType, RequestQuestionQuality, RequestIsComplaint, RequestProcessStatus, RequestStatus, RequestIsForwarding);
DataTable dt = BLL.CustHistoryInfo.Instance.GetCustHistoryInfo(query, "chi.CreateTime desc", BLL.PageCommon.Instance.PageIndex, PageSize, fields, out RecordCount);
repeaterTableList.DataSource = dt;
repeaterTableList.DataBind();
litPagerDown.Text = BLL.PageCommon.Instance.LinkStringByPost(BLL.Util.GetUrl(), GroupLength, RecordCount, PageSize, BLL.PageCommon.Instance.PageIndex, 1);
}
//结束任务
private void TaskEnd(out string msg)
{
msg = string.Empty;
if (RequestCHITaskID == "")
{
msg = "'result':'no','msg','没有任务ID，操作失败！'}";
return;
}
//1 当前受理人表中记录：为当前任务ID的受理人状态全置为0无效
UpdateTaskCurrentSolveUserByTaskID();
//2 客户历史记录信息表：是否确定投诉 可修改，任务状态为150003已处理 必须修改
QueryCustHistoryInfo query = new QueryCustHistoryInfo();
query.TaskID = RequestCHITaskID;
int count;
DataTable dt = BLL.CustHistoryInfo.Instance.GetCustHistoryInfo(query, "", 1, 10000, Entities.CustHistoryInfo.SelectFieldStr, out count);
for (int i = 0; i < dt.Rows.Count; i++)
{
CustHistoryInfo model = BLL.CustHistoryInfo.Instance.GetCustHistoryInfo(long.Parse(dt.Rows[i]["RecID"].ToString()));
if (RequestIsComplaint != "")
{
model.IsComplaint = bool.Parse(RequestIsComplaint);
}
model.ProcessStatus = (int)Entities.EnumTaskStatus.TaskStatusOver;
UpdateCustHistoryInfo(model);
}
//3 插入 客户历史信息处理日志（动作记录）
InsertCustHistoryLogByAction(RequestCHITaskID, RequestComment, (int)Entities.Action.ActionTurnOver); //动作：结束
msg = "{'result':'yes','msg':'操作成功！'}";
}
public DataTable GetCustHistoryInfoForExport(QueryCustHistoryInfo query, string fields)
{
return(Dal.CustHistoryInfo.Instance.GetCustHistoryInfoForExport(query, fields));
}
protected void Page_Load(object sender, EventArgs e)
{
BitAuto.YanFa.SysRightManager.Common.UserInfo.Check();
string fields = Fields;
#region 替换字段
fields = fields.Replace("dii.BrandID", "dbo.GetBrandListByCustID( chi.CustID) as BrandID");
fields = fields.Replace("cbi.Phone", "dbo.GetTelByCustID(cbi.CustID) as Phone ");
fields = fields.Replace("cbi.Email", "dbo.GetEmailByCustID(cbi.CustID) AS Email");
fields = fields.Replace("dii.Remark1", "dii.Remark AS Remark1");
fields = fields.Replace("cnc.CarBrandId1", "case cnc.CarBrandId when 0 Then '' else CONVERT(VARCHAR(50), cnc.CarBrandId) end CarBrandId1,case cnc.CarSerialId when 0 Then '' else CONVERT(VARCHAR(50), cnc.CarSerialId) end CarSerialId1");
fields = fields.Replace("csc.CarBrandId2", "case csc.CarBrandId when 0 Then '' else CONVERT(VARCHAR(50), csc.CarBrandId) end CarBrandId2,case csc.CarSerialId when 0 Then '' else CONVERT(VARCHAR(50), csc.CarSerialId) end CarSerialId2");
fields = fields.Replace("csc.CallRecord1", "csc.CallRecord AS CallRecord1");
//替换
fields = fields.Replace("cbi.Sex", "CASE cbi.Sex WHEN 1 THEN '男' WHEN 2 THEN '女' ELSE '未知' END Sex");
fields = fields.Replace("cbi.ProvinceID", "(SELECT AreaName FROM CRM2009.dbo.AreaInfo WHERE AreaID=cbi.ProvinceID AND Level=1) AS ProvinceID");
fields = fields.Replace("cbi.CityID", "(SELECT AreaName FROM CRM2009.dbo.AreaInfo WHERE AreaID=cbi.CityID AND Level=2) AS CityID");
fields = fields.Replace("cbi.CountyID", "(SELECT AreaName FROM CRM2009.dbo.AreaInfo WHERE AreaID=cbi.CountyID AND Level=3) AS CountyID");
//fields = fields.Replace("cbi.AreaID", "CASE cbi.AreaID WHEN 170001 THEN '北京大区' WHEN 170002 THEN '北方大区' WHEN 170003 THEN '南方大区' WHEN 170004 THEN '华东大区' WHEN 170005 THEN '西部大区' ELSE '' END AreaID");
//fields = fields.Replace("cbi.DataSource", "CASE cbi.DataSource WHEN 180001 THEN '呼叫中心' WHEN 180002 THEN '在线' WHEN 180003 THEN '汽车通' WHEN 180004 THEN '车易通' ELSE '未知' END DataSource");
fields = fields.Replace("cbi.AreaID", "case cbi.AreaID when -2 Then '' else CONVERT(VARCHAR(40),cbi.AreaID) end AreaID");
fields = fields.Replace("cbi.DataSource", " case cbi.DataSource when -2 Then '' else CONVERT(VARCHAR(50),cbi.DataSource) end DataSource");
fields = fields.Replace("cbi.CustCategoryID", "CASE cbi.CustCategoryID WHEN 1 THEN '已购车' WHEN 2 THEN '未购车' WHEN 3 THEN '经销商' ELSE '未知' END CustCategoryID");
//
fields = fields.Replace("bci.Age", "CASE bci.Age WHEN -2 THEN '' Else CONVERT(VARCHAR(10), bci.Age) END Age");
fields = fields.Replace("bci.DriveAge", "CASE bci.DriveAge WHEN -2 THEN '' Else CONVERT(VARCHAR(10),bci.DriveAge) END DriveAge");
// fields = fields.Replace("bci.Vocation", "CASE bci.Vocation WHEN 130001 THEN '一般职业' WHEN 130002 THEN '农牧业' WHEN 130003 THEN '渔业' WHEN 130004 THEN '木材森林业' WHEN 130005 THEN '矿业采掘业' WHEN 130006 THEN '交通运输业' WHEN 130007 THEN '餐饮旅游业' WHEN 130008 THEN '建筑工程' WHEN 130009 THEN '制造加工维修业' WHEN 1300010 THEN '出版广告业' WHEN 1300011 THEN '医药卫生保健' WHEN 1300012 THEN '娱乐业' WHEN 1300013 THEN '文教机构' WHEN 1300014 THEN '宗教机构' WHEN 1300015 THEN '邮政通信电力自来水' WHEN 1300016 THEN '零售批发业' WHEN 1300014 THEN '金融保险证券' WHEN 1300018 THEN '家庭管理' WHEN 1300019 THEN '公检法等执法检查机关' WHEN 1300020 THEN '军人' WHEN 1300021 THEN 'IT业（软硬件开发制作）' WHEN 1300022 THEN '职业运动' WHEN 1300023 THEN '无业人员' WHEN 1300024 THEN '其他' Else '' END Vocation");
fields = fields.Replace("bci.Vocation", "case bci.Vocation when -2 Then '' else CONVERT(VARCHAR(50),bci.Vocation) end Vocation");
fields = fields.Replace("bci.Marriage", "CASE bci.Marriage WHEN 0 THEN '未婚' WHEN 1 THEN '已婚' Else '' END Marriage");
//fields = fields.Replace("bci.Income", "CASE bci.Income WHEN 140001 THEN '1000元/月以下' WHEN 140002 THEN '1000-2000元/月' WHEN 140003 THEN '2001-4000元/月' WHEN 140004 THEN '4001-6000元/月' WHEN 140005 THEN '6001-8000元/月' WHEN 140006 THEN '8001-10000元/月' WHEN 140007 THEN '10001-15000元/月' WHEN 140008 THEN '15001-25000元/月' WHEN 140009 THEN '25000元/月以上' WHEN 140009 THEN '保密' ELSE '' END Income");
fields = fields.Replace("bci.Income", "case bci.Income when -2 Then '' else CONVERT(VARCHAR(50),bci.Income) end Income");
fields = fields.Replace("bci.CarBrandId", "case bci.CarBrandId when 0 Then '' else CONVERT(VARCHAR(50), bci.CarBrandId) end CarBrandId");
fields = fields.Replace("bci.CarSerialId", "case bci.CarSerialId when 0 Then '' else CONVERT(VARCHAR(50), bci.CarSerialId) end CarSerialId");
fields = fields.Replace("bci.IsAttestation", "CASE bci.IsAttestation WHEN 0 THEN '否' WHEN 1 THEN '是' END IsAttestation");
fields = fields.Replace("dii.CityScope", "CASE dii.CityScope WHEN 10001 THEN '111城区' WHEN 10002 THEN '111郊区' WHEN 10003 THEN '6全城' WHEN 10004 THEN '224全城' END CityScope");
fields = fields.Replace("dii.MemberType", "CASE dii.MemberType WHEN 20004 THEN '4s' WHEN 20005 THEN '特许经销商' WHEN 20006 THEN '综合店' END MemberType");
fields = fields.Replace("dii.CarType", "CASE dii.CarType WHEN 30001 THEN '新车' WHEN 30002 THEN '二手车' WHEN 30003 THEN '新车/二手车' END CarType");
fields = fields.Replace("dii.MemberStatus", "CASE dii.MemberStatus WHEN 40001 THEN '会员页' WHEN 40002 THEN '旺店页' WHEN 40003 THEN '待创建' END MemberStatus");
//1 fields = fields.Replace("cnc.CarBrandId AS CarBrandId1", "(SELECT top 1 Name FROM CRM2009.dbo.car_brand WHERE Brandid=cnc.CarBrandId) AS CarBrandId1");
fields = fields.Replace("cnc.BuyCarTime", "CASE cnc.BuyCarTime WHEN 50001 THEN '一周内' WHEN 50002 THEN '一月内' WHEN 50003 THEN '半年内' WHEN 50004 THEN '无计划' END BuyCarTime");
fields = fields.Replace("cnc.BuyOrDisplace", "CASE cnc.BuyOrDisplace WHEN 1 THEN '新购' WHEN 2 THEN '置换' END BuyOrDisplace");
fields = fields.Replace("cnc.AcceptTel", "CASE cnc.AcceptTel WHEN 1 THEN '接受' WHEN 0 THEN '不接受' END AcceptTel");
fields = fields.Replace("csc.QuestionType", "CASE csc.QuestionType WHEN 70001 THEN '买车' WHEN 70002 THEN '卖车' WHEN 70003 THEN '删除' END QuestionType");
//1 fields = fields.Replace("csc.CarBrandId AS CarBrandId2", "(SELECT top 1 Name FROM CRM2009.dbo.car_brand WHERE Brandid=csc.CarBrandId) AS CarBrandId2");
//1 fields = fields.Replace("csc.SaleCarBrandId", "(SELECT top 1 Name FROM CRM2009.dbo.car_brand WHERE Brandid=csc.SaleCarBrandId) AS SaleCarBrandId");
fields = fields.Replace("csc.SaleCarBrandId", "case csc.SaleCarBrandId when 0 Then '' else CONVERT(VARCHAR(50), csc.SaleCarBrandId) end SaleCarBrandId,case csc.SaleCarSerialId when 0 Then '' else CONVERT(VARCHAR(50), csc.SaleCarSerialId) end SaleCarSerialId");
fields = fields.Replace("chi.CreateUserID", "(SELECT trueName FROM SysRightsManager.dbo.UserInfo WHERE UserID=chi.CreateUserID) AS CreateUserID");
fields = fields.Replace("chi.CallTime", "case cbi.CallTime when -2 Then 0 else CONVERT(VARCHAR(10),cbi.CallTime) end CallTime ");
#endregion
int RecordCount = 0;
QueryCustHistoryInfo query = BLL.CustHistoryInfo.Instance.GetQueryModel(RequestTaskID, RequestCustName, RequestBeginTime, RequestEndTime,
RequestConsultID, RequestQuestionType, RequestQuestionQuality, RequestIsComplaint, RequestProcessStatus, RequestStatus, "");
DataTable dt = BLL.CustHistoryInfo.Instance.GetCustHistoryInfoForExport(query, fields);
DataSet ds = dt.DataSet;
//foreach (DataColumn col in ds.Tables[0].Columns)
//{
// // ds.Tables[0].Columns[0].DataType = System.Type.GetType("System.String");
// col.DataType = System.Type.GetType("System.String");
//}
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
//string carMaster = BLL.CarTypeAPI.Instance.GetMasterBrandNameByMasterBrandID(int.Parse(model.CarMasterID.ToString()));
//string carSerial = BLL.CarTypeAPI.Instance.GetSerialNameBySerialID(int.Parse(model.CarSerialID.ToString()));
//string carType = BLL.CarTypeAPI.Instance.GetCarTypeNameByCarTypeID(int.Parse(model.CarTypeID.ToString()));
#region 替换成汉字
int intVal = 0;
foreach (DataRow dr in ds.Tables[0].Rows)
{
if (dt.Columns.Contains("AreaID") && dr["AreaID"].ToString() != (-2).ToString() && int.TryParse(dr["AreaID"].ToString(), out intVal))
{
dr["AreaID"] = BLL.Util.GetEnumOptText(typeof(EnumArea), int.Parse(dr["AreaID"].ToString()));
}
if (dt.Columns.Contains("DataSource") && dr["DataSource"].ToString() != (-2).ToString() && int.TryParse(dr["DataSource"].ToString(), out intVal))
{
dr["DataSource"] = BLL.Util.GetEnumOptText(typeof(EnumDataSource), int.Parse(dr["DataSource"].ToString()));
}
if (dt.Columns.Contains("Vocation") && dr["Vocation"].ToString() != (-2).ToString() && int.TryParse(dr["Vocation"].ToString(), out intVal))
{
dr["Vocation"] = BLL.Util.GetEnumOptText(typeof(CustVocation), int.Parse(dr["Vocation"].ToString()));
}
if (dt.Columns.Contains("Income") && dr["Income"].ToString() != (-2).ToString() && int.TryParse(dr["Income"].ToString(), out intVal))
{
dr["Income"] = BLL.Util.GetEnumOptText(typeof(CustInCome), int.Parse(dr["Income"].ToString()));
}
#region 车型车款转成汉字
if (dt.Columns.Contains("CarBrandId1") && dr["CarBrandId1"].ToString() != "0" && int.TryParse(dr["CarBrandId1"].ToString(), out intVal))
{
dr["CarBrandId1"] = BLL.CarTypeAPI.Instance.GetMasterBrandNameByMasterBrandID(int.Parse(dr["CarBrandId1"].ToString()));
if (dt.Columns.Contains("CarSerialId1") && dr["CarSerialId1"].ToString() != "0" && int.TryParse(dr["CarSerialId1"].ToString(), out intVal))
{
dr["CarBrandId1"] += "-" + BLL.CarTypeAPI.Instance.GetSerialNameBySerialID(int.Parse(dr["CarSerialId1"].ToString()));
}
}
if (dt.Columns.Contains("CarBrandId2") && dr["CarBrandId2"].ToString() != "0" && int.TryParse(dr["CarBrandId2"].ToString(), out intVal))
{
dr["CarBrandId2"] = BLL.CarTypeAPI.Instance.GetMasterBrandNameByMasterBrandID(int.Parse(dr["CarBrandId2"].ToString()));
if (dt.Columns.Contains("CarSerialId2") && dr["CarSerialId2"].ToString() != "0" && int.TryParse(dr["CarSerialId2"].ToString(), out intVal))
{
dr["CarBrandId2"] += "-" + BLL.CarTypeAPI.Instance.GetSerialNameBySerialID(int.Parse(dr["CarSerialId2"].ToString()));
}
}
if (dt.Columns.Contains("CarBrandId") && dr["CarBrandId"].ToString() != "0" && int.TryParse(dr["CarBrandId"].ToString(), out intVal))
{
dr["CarBrandId"] = BLL.CarTypeAPI.Instance.GetMasterBrandNameByMasterBrandID(int.Parse(dr["CarBrandId"].ToString()));
}
if (dt.Columns.Contains("CarSerialId") && dr["CarSerialId"].ToString() != "0" && int.TryParse(dr["CarSerialId"].ToString(), out intVal))
{
dr["CarSerialId"] = BLL.CarTypeAPI.Instance.GetSerialNameBySerialID(int.Parse(dr["CarSerialId"].ToString()));
}
if (dt.Columns.Contains("SaleCarBrandId") && dr["SaleCarBrandId"].ToString() != "0" && int.TryParse(dr["SaleCarBrandId"].ToString(), out intVal))
{
dr["SaleCarBrandId"] = BLL.CarTypeAPI.Instance.GetMasterBrandNameByMasterBrandID(int.Parse(dr["SaleCarBrandId"].ToString()));
//SaleCarSerialId
if (dt.Columns.Contains("SaleCarSerialId") && dr["SaleCarSerialId"].ToString() != "0" && int.TryParse(dr["SaleCarSerialId"].ToString(), out intVal))
{
dr["SaleCarBrandId"] += "-" + BLL.CarTypeAPI.Instance.GetSerialNameBySerialID(int.Parse(dr["SaleCarSerialId"].ToString()));
}
}
#endregion
}
#endregion
#region 替换字段名
DataSet configDs = new DataSet();
configDs.ReadXml(Server.MapPath("~/TaskManager/TaskFields.xml"));
if (configDs.Tables["item"] != null)
{
foreach (DataRow dr in configDs.Tables["item"].Rows)
{
foreach (DataColumn col in ds.Tables[0].Columns)
{
if (col.ColumnName == dr["name"].ToString())
{
col.ColumnName = dr["des"].ToString();
}
}
}
}
#endregion
#region 除不需要的列
if (ds.Tables[0].Columns.Contains("RowNumber"))
{
ds.Tables[0].Columns.Remove("RowNumber");
}
if (ds.Tables[0].Columns.Contains("CarSerialId1"))
{
ds.Tables[0].Columns.Remove("CarSerialId1");
}
if (ds.Tables[0].Columns.Contains("CarSerialId2"))
{
ds.Tables[0].Columns.Remove("CarSerialId2");
}
if (ds.Tables[0].Columns.Contains("SaleCarSerialId"))
{
ds.Tables[0].Columns.Remove("SaleCarSerialId");
}
#endregion
BLL.Util.InsertUserLog("导出了任务信息(共计：" + ds.Tables[0].Rows.Count.ToString() + "行)");
// ExcelInOut.CreateEXCEL(ds, "Task" + DateTime.Now.ToString("yyyy-MM-dd-hh-mm-ss"));
BLL.Util.ExportToCSV("个人客户任务" + DateTime.Now.ToString("yyyy-MM-dd-hh-mm-ss"), ds.Tables[0]);
}
}
/// <summary>
/// 获取查询条件语句
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public static string GetWhereStr(QueryCustHistoryInfo query)
{
string where = string.Empty;
if (query.RecID != Constant.INT_INVALID_VALUE)
{
where += " AND chi.RecID=" + query.RecID;
}
if (query.TaskID != Constant.STRING_INVALID_VALUE)
{
where += " AND chi.TaskID='" + StringHelper.SqlFilter(query.TaskID) + "'";
}
if (query.CallRecordID != Constant.INT_INVALID_VALUE)
{
where += " And chi.CallRecordID=" + query.CallRecordID.ToString();
}
if (query.CustID != Constant.STRING_INVALID_VALUE)
{
where += " AND chi.CustID='" + StringHelper.SqlFilter(query.CustID) + "'";
}
if (query.BeginTime != Constant.STRING_INVALID_VALUE)
{
where += " AND chi.CreateTime>='" + StringHelper.SqlFilter(query.BeginTime) + " 0:00:00'";
}
if (query.EndTime != Constant.STRING_INVALID_VALUE)
{
where += " AND chi.CreateTime<='" + StringHelper.SqlFilter(query.EndTime) + " 23:59:59'";
}
//客户姓名
if (query.CustName != Constant.STRING_INVALID_VALUE)
{
where += " AND cbi.CustName like '%" + StringHelper.SqlFilter(query.CustName) + "%'";
}
//问题性质 （枚举）
if (query.QuestionQualityStr != Constant.STRING_INVALID_VALUE)
{
where += " AND chi.QuestionQuality in (" + Dal.Util.SqlFilterByInCondition(query.QuestionQualityStr.ToString()) + ")";
}
if (query.IsCompaintStr != Constant.STRING_INVALID_VALUE && query.IsCompaintStr != "")
{
where += " AND chi.IsComplaint in (" + Dal.Util.SqlFilterByInCondition(query.IsCompaintStr.ToString()) + ")";
}
//是否转发 刘学文 10.24
if (query.IsForwardingStr != Constant.STRING_INVALID_VALUE && query.IsForwardingStr != "")
{
if (query.IsForwardingStr.Contains("0") && !query.IsForwardingStr.Contains("1"))
{
where += " AND chi.TaskID not in (Select top 1 TaskID from CustHistoryTemplateMapping AS ctm Where ctm.TaskID=chi.TaskID)";
}
else if (!query.IsForwardingStr.Contains("0") && query.IsForwardingStr.Contains("1"))
{
where += " AND chi.TaskID in (Select top 1 TaskID from CustHistoryTemplateMapping AS ctm Where ctm.TaskID=chi.TaskID)";
}
}
//任务状态 （枚举）
if (query.ProcessStatusStr != null && query.ProcessStatusStr != "")
{
if (query.ProcessStatusStr.Contains(((int)EnumTaskStatus.TaskStatusNow).ToString()))
{
//是否包含“处理中”
if (query.Status != Constant.STRING_INVALID_VALUE)
{
string[] array_Status = Util.SqlFilterByInCondition(query.Status).Split(',');
string strStatus = string.Empty;
where += " AND chi.TaskID in (SELECT a.TaskID FROM (SELECT TOP 1 CustHistoryLog.TaskID,Status FROM CustHistoryLog WHERE CustHistoryLog.TaskID=chi.TaskID ORDER BY SolveTime DESC) a WHERE 1=1 ";
for (int i = 0; i < array_Status.Length; i++)
{
if (i == 0)
{
strStatus += " AND (";
}
else
{
strStatus += " OR ";
}
strStatus += " a.Status =" + array_Status[i];
if (i == array_Status.Length - 1)
{
strStatus += ")";
}
}
where += strStatus;
where += ")";
}
}
where += " AND chi.ProcessStatus in (" + Dal.Util.SqlFilterByInCondition(query.ProcessStatusStr.ToString()) + ")";
}
int judgeType = 0; //判断咨询类型表的问题类别类型，1-int整型；2-string 字符型
//咨询类型 （枚举）
if (query.ConsultID != Constant.INT_INVALID_VALUE && query.ConsultID != 0 && query.ConsultID != null)
{
string ConsultTableName = string.Empty;
switch (query.ConsultID)
{
case (int)ConsultType.NewCar: ConsultTableName = "ConsultNewCar";
break;
case (int)ConsultType.SecondCar: ConsultTableName = "ConsultSecondCar";
judgeType = 1;
break;
case (int)ConsultType.PFeedback: ConsultTableName = "ConsultPFeedback";
judgeType = 2;
break;
//case (int)ConsultType.Activity: ConsultTableName = "ConsultActivity";
// judgeType = 1;
// break;
case (int)ConsultType.POther: ConsultTableName = "ConsultPOther";
break;
case (int)ConsultType.PUseCar: ConsultTableName = "ConsultPUseCar";
judgeType = 2;
break;
case (int)ConsultType.DCoop: ConsultTableName = "ConsultDCoop";
judgeType = 1;
//modify by qizq 2012-12-20
//query.ConsultDCoopType = 1;
break;
case (int)ConsultType.DCoopFeedback: ConsultTableName = "ConsultDFeedback";
judgeType = 1;
//modify by qizq 2012-12-20
//query.ConsultDCoopType = 2;
break;
case (int)ConsultType.DCoopOther: ConsultTableName = "ConsultDOther";
judgeType = 1;
//modify by qizq 2012-12-20
//query.ConsultDCoopType = 3;
break;
}
where += " AND chi.ConsultID=" + query.ConsultID.ToString() + " AND chi.ConsultDataID in (SELECT con.RecID FROM " + ConsultTableName + " AS con WHERE con.RecID=chi.ConsultDataID";
//*modify by qizq 2012-12-20
//如果是经销商合作表，则还要根据其Type类型字段作为条件查询
//if (ConsultTableName == "ConsultDCoop" && query.ConsultDCoopType != Constant.INT_INVALID_VALUE)
//{
// where += " AND con.Type in (" + Dal.Util.SqlFilterByInCondition(query.ConsultDCoopType.ToString()) + ")";
//}
//*
//如果存在问题类别，加上此条件
if (query.QuestionType != Constant.STRING_INVALID_VALUE && query.QuestionType != "")
{
//1-int整型；2-string 字符型
//if (judgeType == 1)
//{
// string[] array_type = query.QuestionType.Split(',');
// string strType = string.Empty;
// where += " AND con.QuestionType in (" + Dal.Util.SqlFilterByInCondition(query.QuestionType) + ")";
//}
//else if (judgeType == 2)
//{
string[] array_type = Util.SqlFilterByInCondition(query.QuestionType).Split(',');
string strType = string.Empty;
for (int i = 0; i < array_type.Length; i++)
{
if (i == 0)
{
strType += " AND (";
}
else
{
strType += " OR ";
}
strType += " con.QuestionType LIKE '%" + array_type[i] + "%'";
if (i == array_type.Length - 1)
{
strType += ")";
}
}
where += strType;
}
where += ")";
}
else
{
if (query.ConsultID == 0)
{
where += " AND ConsultID!=60010 AND ConsultID!=60011 AND ConsultID!=-2";
}
}
//当登陆者为本人权限时，加上条件，查询的结果是本人处理的记录
if (query.RightStr != Constant.STRING_INVALID_VALUE)
{
where += " AND chi.TaskID in (SELECT tcs.TaskID FROM TaskCurrentSolveUser AS tcs WHERE tcs.TaskID=chi.TaskID AND tcs.CurrentSolveUserEID=" + query.RightStr + ")";
}
return(where);
}
public DataTable CCDataInterface_GetCustHistoryInfo(string Verifycode, int userid, QueryCustHistoryInfo query, int currentPage, out int totalCount, ref string msg)
{
BLL.Loger.Log4Net.Info("[CCDataInterfaceService.asmx]GetCustHistoryInfo ...BEGIN...客户号：" + query.CustID);
totalCount = 0;
int pageSize = 9999;
//验证授权码
string sVerify = "";
sVerify = ConfigurationUtil.GetAppSettingValue("CallRecordAuthorizeCode");
if (sVerify == Verifycode)
{
}
else
{
msg = "授权码错误!!";
return(null);
}
DataTable mydt = null;
try
{
mydt = BLL.CallRecord_ORIG.Instance.GetCustBaseInfo_ServiceRecord_IM(userid, query, currentPage, pageSize, out totalCount);
}
catch (Exception ex)
{
BLL.Loger.Log4Net.Info("[CCDataInterfaceService.asmx]GetCustHistoryInfo ...获取数据操作出错!errorStackTrace:" + ex.StackTrace);
msg = "操作出错!errorStackTrace:" + ex.StackTrace;
}
BLL.Loger.Log4Net.Info("[CCDataInterfaceService.asmx]GetCustHistoryInfo获取数据操作结束,客户号：" + query.CustID);
return(mydt);
}
/// <summary>
/// 按照查询条件查询
/// </summary>
/// <param name="query">查询条件</param>
/// <param name="order">排序</param>
/// <param name="currentPage">页号,-1不分页</param>
/// <param name="pageSize">每页记录数</param>
/// <param name="totalCount">总行数</param>
/// <returns>集合</returns>
public DataTable GetCustHistoryInfo(QueryCustHistoryInfo query, string order, int currentPage, int pageSize, string fields, out int totalCount)
{
return(Dal.CustHistoryInfo.Instance.GetCustHistoryInfo(query, order, currentPage, pageSize, fields, out totalCount));
}
