public DataTable GetCC_AuditContrastCSTInfo(QueryProjectTask_AuditContrastInfo query, string order, int currentPage, int pageSize, out int totalCount)
{
return(Dal.ProjectTask_AuditContrastInfo.Instance.GetCC_AuditContrastCSTInfo(query, order, currentPage, pageSize, out totalCount));
}
//add by qizhiqiang 2012-4-27
/// <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_AuditContrastInfoForChange(QueryProjectTask_AuditContrastInfo query, string order, int currentPage, int pageSize, out int totalCount)
{
string where = string.Empty;
//modify by qizhiqiang 2012-4-25 把车商通包含进来
if (query.CustIDORMemberID != Constant.STRING_INVALID_VALUE)
{
//where += " And ((ac.CustID='" + StringHelper.SqlFilter(query.CustIDORMemberID) + "' And ac.DMSMemberID IS NULL) OR (ac.DMSMemberID IN (SELECT CONVERT(VARCHAR(50),ID) FROM DMSMember WHERE MemberCode='" + StringHelper.SqlFilter(query.CustIDORMemberID) + "')))";
where += " And ((ac.CustID='" + StringHelper.SqlFilter(query.CustIDORMemberID) + "' And ac.DMSMemberID IS NULL) or (ac.DMSMemberID IN (SELECT CONVERT(VARCHAR(50),ID) FROM Crm2009.dbo.DMSMember WHERE MemberCode='" + StringHelper.SqlFilter(query.CustIDORMemberID) + "')) OR (ac.DMSMemberID IN (SELECT CONVERT(VARCHAR(50),CSTRecID) FROM Crm2009.dbo.cstMember WHERE cstmemberid='" + StringHelper.SqlFilter(query.CustIDORMemberID) + "')))";
}
if (query.CustNameORMemberName != Constant.STRING_INVALID_VALUE)
{
//where += " And ((ac.CustID IN (SELECT CustID FROM CustInfo WHERE CustName='" + StringHelper.SqlFilter(query.CustNameORMemberName) + "') And ac.DMSMemberID IS NULL) OR (ac.DMSMemberID IN (SELECT CONVERT(VARCHAR(50),ID) FROM DMSMember WHERE Name='" + StringHelper.SqlFilter(query.CustNameORMemberName) + "')))";
where += " And ((ac.CustID IN (SELECT CustID FROM Crm2009.dbo.CustInfo WHERE CustName='" + StringHelper.SqlFilter(query.CustNameORMemberName) + "') And ac.DMSMemberID IS NULL) OR (ac.DMSMemberID IN (SELECT CONVERT(VARCHAR(50),ID) FROM Crm2009.dbo.DMSMember WHERE Name='" + StringHelper.SqlFilter(query.CustNameORMemberName) + "')) or (ac.DMSMemberID IN (SELECT CONVERT(VARCHAR(50),CSTRecID) FROM Crm2009.dbo.cstMember WHERE fullName='" + StringHelper.SqlFilter(query.CustNameORMemberName) + "')))";
}
if (query.PTID != Constant.INT_INVALID_VALUE)
{
where += " And ac.PTID='" + query.PTID + "'";
}
if (query.RecID != Constant.INT_INVALID_VALUE)
{
where += " And ac.RecID =" + query.RecID;
}
if (query.CreateStartDate != Constant.STRING_INVALID_VALUE)
{
where += " And ac.CreateTime >='" + StringHelper.SqlFilter(query.CreateStartDate) + " 0:0:0'";
}
if (query.CreateEndDate != Constant.STRING_INVALID_VALUE)
{
where += " And ac.CreateTime <='" + StringHelper.SqlFilter(query.CreateEndDate) + " 23:59:59'";
}
if (query.ExportStatus != Constant.INT_INVALID_VALUE)
{
where += " And ac.ExportStatus=" + query.ExportStatus;
}
if (query.ContrastType != Constant.INT_INVALID_VALUE)
{
where += " And ac.ContrastType=" + query.ContrastType;
}
if (query.DisposeStatus != Constant.INT_INVALID_VALUE)
{
where += " And ac.DisposeStatus=" + query.DisposeStatus;
}
if (query.IsNullDisposeStatus != null)
{
if (query.IsNullDisposeStatus.Value)
{
where += " And ac.DisposeStatus IS NULL";
}
else
{
where += " And ac.DisposeStatus IS Not NULL";
}
where += " And ac.DisposeStatus IS NULL";
}
if (query.SeatTrueName != Constant.STRING_INVALID_VALUE)
{
where += @" And ac.PTID IN (
SELECT PTID FROM dbo.ProjectTask_Employee WHERE Status=0 AND UserID IN (SELECT UserID FROM Crm2009.dbo.v_userinfo WHERE TrueName Like '%" + StringHelper.SqlFilter(query.SeatTrueName) + "%')) ";
}
//modify by qizhiqiang 2012-4-26去掉经营范围
//if (query.CarType != Constant.STRING_EMPTY_VALUE)
//{
// where += " And CustInfo.CarType in (" + query.CarType + ")";
//}
if (query.TaskBatch != -1 && query.TaskBatch != Constant.INT_INVALID_VALUE) //-1代表全部轮次
{
where += " And ProjectTaskInfo.Batch=" + query.TaskBatch;
}
//where += " and (Case when ac.DMSMemberID is not null then (((ac.DMSMemberID IN (SELECT CONVERT(VARCHAR(50),ID) FROM DMSMember where MemberCode<>'' and MemberCode<>'0' and MemberCode<>'-2')) OR (ac.DMSMemberID IN (SELECT CONVERT(VARCHAR(50),CSTRecID) FROM cstMember where CONVERT(VARCHAR(50),cstMemberid)<>'' and CONVERT(VARCHAR(50),cstMemberid)<>'0' and CONVERT(VARCHAR(50),cstMemberid)<>'-2'))))";
where += " and ((ac.DMSMemberID is null) or (exists(select membercode from Crm2009.dbo.DMSMember where CONVERT(VARCHAR(50),ID)=ac.DMSMemberID and membercode<>'' and membercode<>'0' and membercode<>'-2')) or (exists(select CONVERT(VARCHAR(50),cstmemberid) from Crm2009.dbo.cstMember where CSTRecID=ac.DMSMemberID and CONVERT(VARCHAR(50),cstmemberid)<>'' and CONVERT(VARCHAR(50),cstmemberid)<>'0' and CONVERT(VARCHAR(50),cstmemberid)<>'-2')))";
DataSet ds;
SqlParameter[] parameters =
{
new SqlParameter("@where", SqlDbType.VarChar, 8000),
new SqlParameter("@order", SqlDbType.NVarChar, 200),
new SqlParameter("@pagesize", SqlDbType.Int, 4),
new SqlParameter("@indexpage", 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_PROJECTTASL_AUDITCONTRASTINFO_SELECT, parameters);
totalCount = (int)(parameters[4].Value);
return(ds.Tables[0]);
}
/// <summary>
/// 根据查询条件，批量更新导出状态
/// </summary>
/// <param name="query">查询条件</param>
/// <param name="exportStatus">导出状态（0-未导出，1-已导出）</param>
public int BatchUpdateExportStatusByWhere(QueryProjectTask_AuditContrastInfo query, int exportStatus)
{
return(Dal.ProjectTask_AuditContrastInfo.Instance.BatchUpdateExportStatusByWhere(query, exportStatus));
}
