public void EvalQueryCusts(QueryProjectTask_Cust query, string where)
{
if (query.CustName != Constant.STRING_INVALID_VALUE)
{
where += " and ProjectTask_Cust.CustName like '%" + SqlFilter(query.CustName) + "%'";
}
if (query.AbbrName != Constant.STRING_INVALID_VALUE)
{
where += " and ProjectTask_Cust.AbbrName like '%" + SqlFilter(query.AbbrName) + "%'";
}
if (query.UserName != Constant.STRING_INVALID_VALUE)
{
where += " and ProjectTask_Cust.lastUpdateUserID =(select userid from Crm2009.dbo.v_userinfo where truename='" + SqlFilter(query.UserName) + "')";
}
if (query.Brandids != Constant.STRING_INVALID_VALUE)
{
where += " and ProjectTask_Cust.PTID in ( select PTID from ProjectTask_Cust_Brand where BrandID in (" + Dal.Util.SqlFilterByInCondition(query.Brandids) + ") ) ";
}
if (query.ProvinceID != Constant.STRING_INVALID_VALUE)
{
where += " and ProjectTask_Cust.ProvinceID='" + SqlFilter(query.ProvinceID) + "'";
}
if (query.CityID != Constant.STRING_INVALID_VALUE)
{
where += " and ProjectTask_Cust.CityID='" + SqlFilter(query.CityID) + "'";
}
if (query.CountyID != Constant.STRING_INVALID_VALUE)
{
where += " and ProjectTask_Cust.CountyID='" + SqlFilter(query.CountyID) + "'";
}
if (query.SubmitTime != Constant.STRING_INVALID_VALUE)
{
where += " and dbo.fn_DateToString(ProjectTask_Cust.lastUpdateTime,'yyyy-MM-dd')>= '" + SqlFilter(query.SubmitTime) + "'";
}
if (query.BeginSubmitTime != Constant.STRING_INVALID_VALUE)
{
where += " and dbo.fn_DateToString(ProjectTask_Cust.lastUpdateTime,'yyyy-MM-dd')>= '" + SqlFilter(query.BeginSubmitTime) + "'";
}
if (query.EndSubmitTime != Constant.STRING_INVALID_VALUE)
{
where += " and dbo.fn_DateToString(ProjectTask_Cust.lastUpdateTime,'yyyy-MM-dd')<= '" + SqlFilter(query.EndSubmitTime) + "'";
}
if (query.TaskStatus.Count > 0)
{
StringBuilder sb = new StringBuilder();
foreach (Entities.EnumTaskStatus s in query.TaskStatus)
{
sb.Append(((int)s) + ",");
}
where += string.Format(" and ProjectTaskInfo.TaskStatus in ({0})", sb.ToString().Trim(','));
}
//状态为有效
where += " and ProjectTaskInfo.Status= 0";
}
/// <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 GetProjectTask_Cust(QueryProjectTask_Cust query, string order, int currentPage, int pageSize, out int totalCount)
{
string where = "";
if (query.CustName != Constant.STRING_INVALID_VALUE)
{
where += " and ProjectTask_Cust.CustName like '%" + SqlFilter(query.CustName) + "%'";
}
if (query.AbbrName != Constant.STRING_INVALID_VALUE)
{
where += " and ProjectTask_Cust.AbbrName like '%" + SqlFilter(query.AbbrName) + "%'";
}
if (query.UserName != Constant.STRING_INVALID_VALUE)
{
where += " and ProjectTask_Cust.lastUpdateUserID =(select userid from Crm2009.dbo.v_userinfo where truename='" + SqlFilter(query.UserName) + "')";
}
if (query.SubmitUserName != Constant.STRING_INVALID_VALUE)
{
where += " and (SELECT TOP 1 truename FROM Crm2009.dbo.v_userinfo WHERE USERID =(SELECT TOP 1 UserID FROM dbo.ProjectTaskInfo WHERE PTID=ProjectTask_Cust.PTID Order by CreateTime Desc))='" + SqlFilter(query.SubmitUserName) + "'";
}
if (query.Brandids != Constant.STRING_INVALID_VALUE)
{
where += " and ProjectTask_Cust.PTID in ( select PTID from ProjectTask_Cust_Brand where BrandID in (" + Dal.Util.SqlFilterByInCondition(query.Brandids) + ") ) ";
}
if (query.ProvinceID != Constant.STRING_INVALID_VALUE)
{
where += " and ProjectTask_Cust.ProvinceID='" + SqlFilter(query.ProvinceID) + "'";
}
if (query.CityID != Constant.STRING_INVALID_VALUE)
{
where += " and ProjectTask_Cust.CityID='" + SqlFilter(query.CityID) + "'";
}
if (query.CountyID != Constant.STRING_INVALID_VALUE)
{
where += " and ProjectTask_Cust.CountyID='" + SqlFilter(query.CountyID) + "'";
}
//if (queryCC_Custs.SubmitTime != Constant.STRING_INVALID_VALUE)
//{
// where += " and dbo.fn_DateToString(CC_Custs.lastUpdateTime,'yyyy-MM-dd')>= '" + SqlFilter(queryCC_Custs.SubmitTime) + "'";
//}
if (query.BeginSubmitTime != Constant.STRING_INVALID_VALUE)
{
where += " and dbo.fn_DateToString((SELECT TOP 1 CreateTime FROM dbo.ProjectTaskLog WHERE PTID=ProjectTask_Cust.PTID Order by CreateTime Desc),'yyyy-MM-dd')>= '" + SqlFilter(query.BeginSubmitTime) + "'";
}
if (query.EndSubmitTime != Constant.STRING_INVALID_VALUE)
{
where += " and dbo.fn_DateToString((SELECT TOP 1 CreateTime FROM dbo.ProjectTaskLog WHERE PTID=ProjectTask_Cust.PTID Order by CreateTime Desc),'yyyy-MM-dd')<= '" + SqlFilter(query.EndSubmitTime) + "'";
}
if (query.TaskStatus == null)
{
where += " and 1!=1 ";
}
else if (query.TaskStatus != null && query.TaskStatus.Count > 0)
{
StringBuilder sb = new StringBuilder();
foreach (Entities.EnumTaskStatus s in query.TaskStatus)
{
sb.Append(((int)s) + ",");
}
where += string.Format(" and ProjectTaskInfo.TaskStatus in ({0})", sb.ToString().Trim(','));
}
if (query.TopOneTaskStatus.Count > 0)
{
StringBuilder sb = new StringBuilder();
foreach (Entities.EnumTaskStatus s in query.TopOneTaskStatus)
{
sb.Append(((int)s) + ",");
}
where += string.Format(" and ctl.TaskStatus in ({0})", sb.ToString().Trim(','));
}
if (query.FailureReason != Constant.STRING_INVALID_VALUE)
{
if (query.FailureReason.Equals("其它异常"))
{
where += " and (SELECT TOP 1 Description FROM dbo.ProjectTaskLog WHERE PTID=ProjectTask_Cust.PTID ORDER BY CreateTime DESC) Not IN('提交审核失败，与CRM系统中的客户名称重复','提交审核失败，与CRM系统中的会员简称重复') ";
}
else
{
where += " and (SELECT TOP 1 Description FROM dbo.ProjectTaskLog WHERE PTID=ProjectTask_Cust.PTID ORDER BY CreateTime DESC) = '" + SqlFilter(query.FailureReason) + "' ";
}
}
if (query.CarType != Constant.STRING_EMPTY_VALUE && query.CarType != null)
{
where += " and ProjectTask_Cust.CarType in (" + Dal.Util.SqlFilterByInCondition(query.CarType) + ")";
}
if (query.TaskSource != Constant.INT_INVALID_VALUE)
{
where += " and ProjectTaskInfo.Source=" + query.TaskSource;
}
//状态为有效
where += " and ProjectTaskInfo.Status= 0";
DataSet ds;
SqlParameter[] parameters =
{
new SqlParameter("@where", SqlDbType.VarChar, 8000),
new SqlParameter("@order", SqlDbType.NVarChar, 100),
new SqlParameter("@pagesize", SqlDbType.Int, 4),
new SqlParameter("@page", SqlDbType.Int, 4),
new SqlParameter("@totalRecorder", SqlDbType.Int, 4)
};
parameters[0].Value = where;
parameters[1].Value = order;
parameters[2].Value = pageSize;
parameters[3].Value = currentPage;
parameters[4].Direction = ParameterDirection.Output;
ds = SqlHelper.ExecuteDataset(CONNECTIONSTRINGS, CommandType.StoredProcedure, P_PROJECTTASK_CUST_SELECT, parameters);
totalCount = int.Parse(parameters[4].Value.ToString());
return(ds.Tables[0]);
}
/// <summary>
/// 按照查询条件查询
/// </summary>
public DataTable GetProjectTask_Cust(QueryProjectTask_Cust queryProjectTask_Cust, string order, int currentPage, int pageSize, out int totalCount)
{
return(Dal.ProjectTask_Cust.Instance.GetProjectTask_Cust(queryProjectTask_Cust, order, currentPage, pageSize, out totalCount));
}
