/// 工单记录导出数据查询
/// <summary>
/// 工单记录导出数据查询
/// </summary>
/// <param name="query"></param>
/// <param name="order"></param>
/// <returns></returns>
public DataTable GetExportWorkOrderList(QueryWOrderV2DataInfo query, string order)
{
string where = GetWorkOrderListWhere(query);
SqlParameter[] parmas = new SqlParameter[] {
new SqlParameter("@where", SqlDbType.NVarChar, 2000)
{
Value = where
}
};
return(SqlHelper.ExecuteDataset(CONNECTIONSTRINGS, CommandType.StoredProcedure, "P_WOrderInfo_Export", parmas).Tables[0]);
}
private void WorkOrderBind()
{
QueryWOrderV2DataInfo query = BLL.Util.BindQuery <QueryWOrderV2DataInfo>(this.Context);
query.CC_LoginID = userId;
string orderstring = "a.WorkOrderStatus ASC,a.CreateTime DESC";
DataTable dt = BLL.WOrderInfo.Instance.GetWorkOrderInfoForList(query, orderstring, PageCommon.Instance.PageIndex, PageSize, out totalCount);
rptWorkOrderList.DataSource = dt;
rptWorkOrderList.DataBind();
litPagerDown.Text = BLL.PageCommon.Instance.LinkStringByPost(BLL.Util.GetUrl(), GroupLength, totalCount, PageSize, BLL.PageCommon.Instance.PageIndex, 1);
}
/// 按照查询条件查询(包含数据权限)
/// <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 GetWorkOrderInfoForList(QueryWOrderV2DataInfo query, string order, int currentPage, int pageSize, out int totalCount)
{
string where = GetWorkOrderListWhere(query);
string sqlstr = @"
SELECT
a.*,
ISNULL(STUFF((SELECT ',' + RTRIM(UserID) + ':'+UserName FROM dbo.WOrderToAndCC
WHERE ReceiverID =a.LastReceiverID and PersonType=1 FOR XML PATH('')), 1, 1, ''), '') LastReceivers,
isnull(b.BusiTypeName,'--') BusiTypeName,
CONVERT(NVARCHAR(50),isnull(e.TagName+'/','-')+isnull(c.TagName,'-')) TagName,
d.CustCategoryID,
vuser.TrueName,
ISNULL(crmcust.CustName,'--') AS CRMCustName
YanFaFROM WOrderInfo a
LEFT JOIN dbo.WOrderBusiType b ON a.BusinessType=b.RecID
LEFT JOIN dbo.WOrderTag c ON a.BusinessTag=c.RecID
LEFT JOIN dbo.WOrderTag e ON c.PID=e.RecID
LEFT JOIN dbo.CustBasicInfo d ON a.CBID=d.CustID
LEFT JOIN CRM2009.dbo.CustInfo crmcust ON a.CRMCustID=crmcust.CustID
INNER JOIN v_userinfo vuser ON a.CreateUserID=vuser.UserID
WHERE a.Status=0 " + where;
DataSet ds;
SqlParameter[] parameters =
{
new SqlParameter("@SQL", SqlDbType.NVarChar, 4000),
new SqlParameter("@Order", SqlDbType.NVarChar, 200),
new SqlParameter("@CurPage", SqlDbType.Int, 4),
new SqlParameter("@PageRows", SqlDbType.Int, 4),
new SqlParameter("@TotalRecorder", SqlDbType.Int, 4)
};
parameters[0].Value = sqlstr;
parameters[1].Value = order;
parameters[2].Value = currentPage;
parameters[3].Value = pageSize;
parameters[4].Direction = ParameterDirection.Output;
ds = SqlHelper.ExecuteDataset(CONNECTIONSTRINGS, CommandType.StoredProcedure, "P_Page", parameters);
totalCount = (int)(parameters[4].Value);
return(ds.Tables[0]);
}
private void WorkOrderExport()
{
Stopwatch sw = new Stopwatch();
sw.Start();
QueryWOrderV2DataInfo query = BLL.Util.BindQuery <QueryWOrderV2DataInfo>(this.Context);
query.CC_LoginID = BLL.Util.GetLoginUserID();
string orderstring = " a.WorkOrderStatus ASC,a.CreateTime Desc ";
DataTable dt = BLL.WOrderInfo.Instance.GetExportWorkOrderList(query, orderstring);
BLL.WOrderInfo.LogToLog4("[工单导出] 查询工单累计耗时：" + sw.Elapsed.ToString());
//获取CRM的访问分类数据
Dictionary <int, string> dictCrmVistType = BLL.WOrderInfo.Instance.GetCrmVistType();
BLL.WOrderInfo.LogToLog4("[工单导出] 查询CRM的访问分类数据累计耗时：" + sw.Elapsed.ToString());
//循环处理
foreach (DataRow dr in dt.Rows)
{
dr["用户地区"] = GetAddress(CommonFunction.ObjectToString(dr["ProvinceID"]), CommonFunction.ObjectToString(dr["CityID"]), CommonFunction.ObjectToString(dr["CountyID"]));
dr["工单状态"] = BitAuto.ISDC.CC2012.BLL.Util.GetEnumOptText(typeof(BitAuto.ISDC.CC2012.Entities.WorkOrderStatus), CommonFunction.ObjectToInteger(dr["WorkOrderStatus"], -1));
dr["工单来源"] = BitAuto.ISDC.CC2012.BLL.Util.GetEnumOptText(typeof(BitAuto.ISDC.CC2012.Entities.WorkOrderDataSource), CommonFunction.ObjectToInteger(dr["DataSource"], -1));
dr["工单类型"] = BitAuto.ISDC.CC2012.BLL.Util.GetEnumOptText(typeof(BitAuto.ISDC.CC2012.Entities.WOrderCategoryEnum), CommonFunction.ObjectToInteger(dr["CategoryID"], -1));
dr["投诉级别"] = BitAuto.ISDC.CC2012.BLL.Util.GetEnumOptText(typeof(BitAuto.ISDC.CC2012.Entities.ComplaintLevelEnum), CommonFunction.ObjectToInteger(dr["ComplaintLevel"], -1));
int visitType = CommonFunction.ObjectToInteger(dr["VisitType"]);
if (dictCrmVistType.ContainsKey(visitType))
{
dr["访问分类"] = dictCrmVistType[visitType];
}
else
{
dr["访问分类"] = "--";
}
int isEstablish = CommonFunction.ObjectToInteger(dr["IsEstablish"], -1);
if (isEstablish == 1)
{
dr["是否接通"] = "是";
}
else if (isEstablish == 0)
{
dr["是否接通"] = "否";
}
else
{
dr["是否接通"] = "--";
}
dr["未接通原因"] = BitAuto.ISDC.CC2012.BLL.Util.GetEnumOptText(typeof(BitAuto.ISDC.CC2012.Entities.NotEstablishReason), CommonFunction.ObjectToInteger(dr["NotEstablishReason"], -1));
if (CommonFunction.ObjectToInteger(dr["P2RecID"]) == CommonFunction.ObjectToInteger(dr["P3RecID"]))
{
//当只有一条处理记录时
dr["最后处理人"] = "";
dr["最后处理记录"] = "";
dr["最后处理时间"] = DBNull.Value;
}
int isReturnVisit = CommonFunction.ObjectToInteger(dr["IsReturnVisit"], -1);
if (isReturnVisit == 0)
{
dr["是否回访"] = "否";
}
else if (isReturnVisit == 1)
{
dr["是否回访"] = "是";
}
else
{
dr["是否回访"] = "--";
}
}
//删除id列
dt.Columns.Remove("ProvinceID");
dt.Columns.Remove("CityID");
dt.Columns.Remove("CountyID");
dt.Columns.Remove("WorkOrderStatus");
dt.Columns.Remove("DataSource");
dt.Columns.Remove("CategoryID");
dt.Columns.Remove("ComplaintLevel");
dt.Columns.Remove("VisitType");
dt.Columns.Remove("IsEstablish");
dt.Columns.Remove("NotEstablishReason");
dt.Columns.Remove("P3RecID");
dt.Columns.Remove("P2RecID");
dt.Columns.Remove("IsReturnVisit");
BLL.WOrderInfo.LogToLog4("[工单导出] 循环处理累计耗时：" + sw.Elapsed.ToString());
//导出
BLL.Util.ExportToCSV("工单记录_" + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss"), dt);
sw.Stop();
BLL.WOrderInfo.LogToLog4("[工单导出] 导出完成累计耗时：" + sw.Elapsed.ToString());
}
/// 获取查询条件
/// <summary>
/// 获取查询条件
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
private string GetWorkOrderListWhere(QueryWOrderV2DataInfo query)
{
string where = string.Empty;
if (query.BGID > 0)
{
where += " AND a.BGID=" + query.BGID;
}
if (!string.IsNullOrEmpty(query.BeginCreateTime))
{
where += " AND a.CreateTime>='" + SqlFilter(query.BeginCreateTime) + " 0:00:00'";
}
if (!string.IsNullOrEmpty(query.EndCreateTime))
{
where += "AND a.CreateTime<='" + SqlFilter(query.EndCreateTime) + " 23:59:59'";
}
if (query.BusiType > 0)
{
where += " AND a.BusinessType=" + query.BusiType;
}
if (!string.IsNullOrEmpty(query.ComplaintLevel))
{
where += " AND a.ComplaintLevel in (" + SqlFilter(query.ComplaintLevel.Replace('-', ',')) + ") ";
}
if (!string.IsNullOrEmpty(query.Phone))
{
where += " AND a.Phone = '" + SqlFilter(query.Phone) + "'";
}
if (query.CreateUserID > 0)
{
where += " AND a.CreateUserID = " + query.CreateUserID;
}
if (!string.IsNullOrEmpty(query.CreateUserName) && query.CreateUserName.Trim() != "")
{
where += " AND vuser.TrueName LIKE '%" + query.CreateUserName.Trim() + "%'";
}
if (!string.IsNullOrEmpty(query.OrderID))
{
where += " AND a.OrderID = '" + SqlFilter(query.OrderID) + "'";
}
if (query.CategoryID > 0)
{
where += " AND a.CategoryID=" + query.CategoryID;
}
if (query.WorkOrderStatus > 0)
{
where += " AND a.WorkOrderStatus=" + query.WorkOrderStatus;
}
if (!string.IsNullOrEmpty(query.WorkOrderStatusList))
{
where += " AND a.WorkOrderStatus IN (" + Dal.Util.SqlFilterByInCondition(query.WorkOrderStatusList) + ")";
}
if (!string.IsNullOrEmpty(query.ReVisitStr))
{
where += " AND a.OrderID in ( SELECT OrderID FROM WOrderProcess WHERE ProcessType=" + (int)WOrderOperTypeEnum.L05_回访 + " AND IsReturnVisit IN (" + SqlFilter(query.ReVisitStr.Replace('-', ',')) + " ))";
}
if (!string.IsNullOrEmpty(query.MemberName) && query.MemberName.Trim() != "")
{
where += " AND d.CustCategoryID=" + (int)CustTypeEnum.T02_经销商 + " AND d.CustID IN (SELECT CustID FROM dbo.DealerInfo WHERE Name LIKE '%" + SqlFilter(query.MemberName.Trim()) + "%' AND Status=0)";
}
if (query.BigTagID > 0)
{
where += " AND c.PID=" + query.BigTagID;
}
if (query.TagID > 0)
{
where += " AND c.RecID=" + query.TagID;
}
//crm接口使用
if (!string.IsNullOrEmpty(query.CRMCustID))
{
where += " AND a.CRMCustID='" + SqlFilter(query.CRMCustID) + "'";
}
if (!string.IsNullOrEmpty(query.CRMCustName) && query.CRMCustName.Trim() != "")
{
where += " AND crmcust.CustName LIKE '%" + SqlFilter(query.CRMCustName.Trim()) + "%'";
}
if (query.CRMCountyID > 0)
{
where += " AND crmcust.CountyID=" + query.CRMCountyID;
}
else if (query.CRMCityID > 0)
{
where += " AND crmcust.CityID=" + query.CRMCityID;
}
else if (query.CRMProvinceID > 0)
{
where += " AND crmcust.ProvinceID=" + query.CRMProvinceID;
}
if (!string.IsNullOrEmpty(query.ProcessUserName) && query.ProcessUserName.Trim() != "")
{
where += @" AND EXISTS (SELECT 1 FROM dbo.WOrderToAndCC
WHERE ReceiverID=a.LastReceiverID AND PersonType=" + (int)WOrderPersonTypeEnum.P01_接收人 + @"
AND UserID IN (SELECT UserID FROM dbo.v_userinfo WHERE Status=0 AND TrueName LIKE '%" + SqlFilter(query.ProcessUserName.Trim()) + "%'))";
}
if (query.RightZN != null && !string.IsNullOrEmpty(query.RightZN.CRM_LoginDepartID))
{
where += " AND a.OrderID IN (SELECT OrderID FROM dbo.WOrderToAndCC WHERE PersonType=" + (int)WOrderPersonTypeEnum.P01_接收人
+ " AND UserID IN ("
+ "SELECT UserID FROM dbo.v_userinfo WHERE DepartID IN (SELECT id FROM Crm2009.[dbo].[f_GetChildDepartid]('" + SqlFilter(query.RightZN.CRM_LoginDepartID) + "'))"
+ ")) ";
}
if (query.RightGR != null && query.RightGR.CRM_LoginID > 0 && !string.IsNullOrEmpty(query.RightGR.CRM_LoginDepartID))
{
where += GetRightGR(query.RightGR.CRM_LoginID, query.RightGR.CRM_LoginDepartID);
}
#region 数据权限判断
if (query.CC_LoginID > 0)
{
string whereDataRight = "";
whereDataRight = Dal.UserGroupDataRigth.Instance.GetSqlRightstrByOrderWhere("a", "BGID", "CreateUserID", query.CC_LoginID,
" a.OrderID IN (SELECT OrderID FROM dbo.WOrderToAndCC WHERE UserID ='" + query.CC_LoginID + "') ");
where += whereDataRight;
}
#endregion
return(where);
}
/// 工单记录导出数据查询
/// <summary>
/// 工单记录导出数据查询
/// </summary>
/// <param name="query"></param>
/// <param name="order"></param>
/// <returns></returns>
public DataTable GetExportWorkOrderList(QueryWOrderV2DataInfo query, string order)
{
return(Dal.WOrderInfo.Instance.GetExportWorkOrderList(query, order));
}
/// 按照查询条件查询(包含数据权限)
/// <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 GetWorkOrderInfoForList(QueryWOrderV2DataInfo query, string order, int currentPage, int pageSize, out int totalCount)
{
return(Dal.WOrderInfo.Instance.GetWorkOrderInfoForList(query, order, currentPage, pageSize, out totalCount));
}
