public DataTable GetCC_CRMDMSMemberInfo(QueryCRMDMSMember query, string order, int currentPage, int pageSize, string strSelectDpid, out int totalCount) { string where = string.Empty; where = GetWhere(query, where); 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), new SqlParameter("@DeptID", SqlDbType.NVarChar, 200) }; parameters[0].Value = where; parameters[1].Value = order; parameters[2].Value = pageSize; parameters[3].Value = currentPage; parameters[4].Direction = ParameterDirection.Output; parameters[5].Value = strSelectDpid; ds = SqlHelper.ExecuteDataset(ConnectionStrings_CRM, CommandType.StoredProcedure, P_CC_DMSMEMBER_ALONE_SELECT, parameters); totalCount = int.Parse(parameters[4].Value.ToString()); return(ds.Tables[0]); }
private static string GetWhere(QueryCRMDMSMember query, string where) { if (query.Status != Constant.INT_INVALID_VALUE) { where += " and DMSMember.Status = " + query.Status; } if (query.DMSMemberCode != Constant.STRING_INVALID_VALUE) { where += " and DMSMember.MemberCode = '" + SqlFilter(query.DMSMemberCode) + "'"; } if (query.DMSMemberName != Constant.STRING_INVALID_VALUE) { where += " and DMSMember.Name Like '%" + SqlFilter(query.DMSMemberName) + "%'"; } if (query.ProvinceID != Constant.STRING_INVALID_VALUE && query.ProvinceID != "-1") { where += " and DMSMember.ProvinceID='" + SqlFilter(query.ProvinceID) + "'"; } if (query.CityID != Constant.STRING_INVALID_VALUE && query.CityID != "-1") { where += " and DMSMember.CityID='" + SqlFilter(query.CityID) + "'"; } if (query.CountyID != Constant.STRING_INVALID_VALUE && query.CountyID != "-1") { where += " and DMSMember.CountyID='" + SqlFilter(query.CountyID) + "'"; } if (query.AreaTypeWhereStr != Constant.STRING_INVALID_VALUE) { where += query.AreaTypeWhereStr; } if (query.IsCCCreate != null) { //where += " and DMSMember.CreateUserID IN (SELECT UserID FROM v_userinfo WHERE DepartID" + (query.IsCCCreate.Value ? "" : "!") + "='" + CCDepartID + "')"; where += " and DMSMember.CreateSource=" + (query.IsCCCreate.Value ? "1" : "0"); } if (query.IsCCUserMapping != null) { where += " and DMSMember.CustID IN (SELECT CustID FROM CustUserMapping WHERE UserID IN " + "(SELECT UserID FROM v_userinfo WHERE DepartID" + (query.IsCCUserMapping.Value ? "" : "!") + "='" + CCDepartID + "'))"; } if (query.IsCCReturnVisit != null) { where += " and DMSMember.CustID IN (SELECT CustID FROM ReturnVisit WHERE Status=0 And CreateuserDepart " + (query.IsCCReturnVisit.Value ? "" : "!") + "='" + CCDepartID + "')"; } //if (!string.IsNullOrEmpty(query.MemberCooperateStatus)) //{ // where += " and DMSMember.CooperateStatus IN (" + query.MemberCooperateStatus + ") "; //} if (query.MemberCreateTimeStart != Constant.STRING_INVALID_VALUE) { where += " and DMSMember.CreateTime >= '" + StringHelper.SqlFilter(query.MemberCreateTimeStart) + " 0:0:0'"; } if (query.MemberCreateTimeEnd != Constant.STRING_INVALID_VALUE) { where += " and DMSMember.CreateTime <= '" + StringHelper.SqlFilter(query.MemberCreateTimeEnd) + " 23:59:59'"; } if (query.ReturnVisitTimeStart != Constant.STRING_INVALID_VALUE) { where += " and DMSMember.CustID IN (SELECT CustID FROM ReturnVisit WHERE Status=0 And CreateTime >='" + StringHelper.SqlFilter(query.ReturnVisitTimeStart) + " 0:0:0') "; } if (query.ReturnVisitTimeEnd != Constant.STRING_INVALID_VALUE) { where += " and DMSMember.CustID IN (SELECT CustID FROM ReturnVisit WHERE Status=0 And CreateTime <='" + StringHelper.SqlFilter(query.ReturnVisitTimeEnd) + " 23:59:59') "; } if (query.StrDeptS == "1") { var TelSaleDeptIDs = ConfigurationManager.AppSettings["TelSaleDeptIDs"]; var tIds = TelSaleDeptIDs.Split(','); TelSaleDeptIDs = ""; for (int i = 0; i < tIds.Length; i++) { TelSaleDeptIDs += ",'" + tIds[i] + "'"; } if (TelSaleDeptIDs.Length > 1) { TelSaleDeptIDs = TelSaleDeptIDs.Substring(1); } where += " and EXISTS(SELECT 1 FROM CustDepartMapping cm WHERE DMSMember.CustID=cm.CustID AND cm.DepartID in( " + TelSaleDeptIDs + "))"; } else if (query.StrDeptS == "0") { var TelSaleDeptIDs = ConfigurationManager.AppSettings["TelSaleDeptIDs"]; var tIds = TelSaleDeptIDs.Split(','); TelSaleDeptIDs = ""; for (int i = 0; i < tIds.Length; i++) { TelSaleDeptIDs += ",'" + tIds[i] + "'"; } if (TelSaleDeptIDs.Length > 1) { TelSaleDeptIDs = TelSaleDeptIDs.Substring(1); } where += " and not EXISTS(SELECT 1 FROM CustDepartMapping cm WHERE DMSMember.CustID=cm.CustID AND cm.DepartID in( " + TelSaleDeptIDs + "))"; } /* * if (!string.IsNullOrEmpty(query.AreaTypeIDs)) * { * string[] typeids = query.AreaTypeIDs.Split(','); * for (int i = 0; i < typeids.Length; i++) * { * string temp = " or "; * if (i == 0) * { * temp = " and ("; * } * switch (typeids[i]) * { * case "1"://163城区 * where += temp + @"(DMSMember.CountyID IN (SELECT AreaID FROM AreaType WHERE Type=2 And Value=1) OR ( * (DMSMember.CountyID=-1 or DMSMember.CountyID is null OR DMSMember.CountyID='') And DMSMember.CityID IN (SELECT AreaID FROM AreaType WHERE Type=1 And Value=1)))"; * break; * case "2"://163郊区 * where += temp + @"DMSMember.CountyID IN (SELECT AreaID FROM AreaType WHERE Type=2 And Value=2) * And DMSMember.CityID IN (SELECT AreaID FROM AreaType WHERE Type=1 And Value=1)"; * break; * case "3"://178无人城 * where += temp + "DMSMember.CityID IN (SELECT AreaID FROM AreaType WHERE Type=1 And Value=2) "; * break; * //case "4"://178无人城郊区 * // where += temp + "DMSMember.CountyID IN (SELECT AreaID FROM AreaType WHERE Type=2 And Value=2) "; * // break; * default: * break; * } * if (i == typeids.Length - 1) * { * where += " ) "; * } * } * } */ if (!string.IsNullOrEmpty(query.BrandIDs)) { where += " and DMSMember.ID IN (SELECT MemberID FROM DMSMember_Brand WHERE BrandID IN (" + Dal.Util.SqlFilterByInCondition(query.BrandIDs) + "))"; } if (!string.IsNullOrEmpty(query.CooperatedStatusIDs)) { if (query.CooperatedStatusIDs == "1" || query.CooperatedStatusIDs.Contains("1"))//有排期 { where += " And DMSMember.Status=0 And DMSMember.Cooperated=1 "; where += " And DMSMember.MemberCode IN (select distinct membercode from mj2009.dbo.CYTMember Where Status in (1003,1007)"; if (!string.IsNullOrEmpty(query.MemberCooperateStatus)) { where += " And MemberType IN (" + Dal.Util.SqlFilterByInCondition(query.MemberCooperateStatus) + ") "; } if (!string.IsNullOrEmpty(query.BeginMemberCooperatedTime) && !string.IsNullOrEmpty(query.EndMemberCooperatedTime))//有排期时间范围 { where += " And begintime<='" + query.EndMemberCooperatedTime + " 23:59:59' And endtime >='" + query.BeginMemberCooperatedTime + " 0:0:0' "; } where += " AND CYTMember.membercode=DMSMember.MemberCode) "; if (!string.IsNullOrEmpty(query.BeginNoMemberCooperatedTime) && !string.IsNullOrEmpty(query.EndNoMemberCooperatedTime))//无排期时间范围 { where += @" And DMSMember.MemberCode IN (SELECT DISTINCT temp2.memberCode FROM ( SELECT temp.memberCode,SUM(temp.NotCooperated) AS SUMNotCooperated,COUNT(*) CountRecord FROM ( select membercode, (CASE WHEN begintime>'" + query.EndNoMemberCooperatedTime + @" 23:59:59' OR endtime <'" + query.BeginNoMemberCooperatedTime + @" 0:0:0' THEN 1 ELSE 0 END) AS NotCooperated from mj2009.dbo.CYTMember Where Status in (1003,1007) ) AS temp GROUP BY temp.memberCode ) AS temp2 WHERE temp2.SUMNotCooperated=temp2.CountRecord ) "; } } //有排期开始时间范围 add=masj 2013-07-04 if (!string.IsNullOrEmpty(query.StartMemberCooperatedBeginTime) || !string.IsNullOrEmpty(query.EndMemberCooperatedBeginTime)) { where += " And DMSMember.Status=0 And DMSMember.Cooperated=1 "; where += " And DMSMember.MemberCode IN (select distinct membercode from mj2009.dbo.CYTMember Where Status in (1003,1007)"; if (!string.IsNullOrEmpty(query.StartMemberCooperatedBeginTime)) { where += " and begintime >= '" + StringHelper.SqlFilter(query.StartMemberCooperatedBeginTime) + "'"; } if (!string.IsNullOrEmpty(query.EndMemberCooperatedBeginTime)) { where += " and begintime <= '" + StringHelper.SqlFilter(query.EndMemberCooperatedBeginTime) + "'"; } where += " AND CYTMember.membercode=DMSMember.MemberCode) "; } if (query.CooperatedStatusIDs == "0" || query.CooperatedStatusIDs.Contains("0"))//无排期 { where += " and DMSMember.Status=0 And (DMSMember.Cooperated=0 OR DMSMember.Cooperated IS NULL) AND DMSMember.SyncStatus!=170008 "; } } if (!string.IsNullOrEmpty(query.MemberType)) { where += " and DMSMember.MemberType IN (" + Dal.Util.SqlFilterByInCondition(query.MemberType) + ")"; } //add by qizq 2012-8-2 排期确认时间 if (!string.IsNullOrEmpty(query.ConfirmDateEnd) && !string.IsNullOrEmpty(query.ConfirmDateStart)) { where += " and DMSMember.MemberCode in (select distinct membercode from mj2009.dbo.CYTMember where confirmtime is not null and confirmtime between '" + query.ConfirmDateStart + " 0:00:00' and '" + query.ConfirmDateEnd + " 23:59:59' and membercode!='' and status in ('1003','1007') and begintime<=endtime)"; } //add by masj 2013-06-28 if (query.IsMagazineReturn != Constant.INT_INVALID_VALUE) { if (query.IsMagazineReturn == 0) { where += " and DMSMember.ID not in (select distinct DMSMemberID from CRM2009.dbo.CC_MagazineReturn)"; } else if (query.IsMagazineReturn == 1) { where += " and DMSMember.ID in (select distinct DMSMemberID from CRM2009.dbo.CC_MagazineReturn where Title='" + Utils.StringHelper.SqlFilter(query.ExecCycle) + "')"; } } if (query.CustContactOfficeTypeCode > 0) { where += @" and DMSMember.CustID in (SELECT CustID FROM ( SELECT CustID,MAX(ID) AS ID FROM CRM2009.dbo.ContactInfo WHERE Status=0 AND OfficeTypeCode=" + query.CustContactOfficeTypeCode + @" GROUP BY CustID ) AS a)"; } if (query.MemberSyncStatus != Constant.STRING_INVALID_VALUE) { where += " And SyncStatus in (" + Dal.Util.SqlFilterByInCondition(query.MemberSyncStatus) + ")"; } return(where); }
public DataTable GetCRMDMSMemberInfo(QueryCRMDMSMember query, string order, int currentPage, int pageSize, out int totalCount) { string where = string.Empty; string innerwhere = string.Empty;//客户联系人条件 string joinwhere = string.Empty; where = GetWhere(query, where); if (query.CustContactOfficeTypeCode > 0) { innerwhere = @" LEFT JOIN (SELECT ci.CustID,CName,Title,OfficeTypeCode FROM ContactInfo AS ci INNER JOIN ( SELECT CustID,MAX(ID) AS ID FROM ContactInfo WHERE Status=0 AND OfficeTypeCode=" + query.CustContactOfficeTypeCode + @" GROUP BY CustID ) AS a ON a.ID=ci.ID ) AS m ON m.CustID=DMSMember.CustID " ; where += " AND m.OfficeTypeCode=" + query.CustContactOfficeTypeCode; } else { //联系人导出:如果未选择客户联系人职级,需要过滤联系人为空 add lxw 13.7.29 where += " AND m.CName != '' "; } if (query.IsMagazineReturn != Constant.INT_INVALID_VALUE) { if (query.IsMagazineReturn == 0) { joinwhere += "LEFT JOIN (select distinct DMSMemberID from CC_MagazineReturn) AS cmr ON DMSMember.ID=cmr.DMSMemberID"; where += " AND cmr.DMSMemberID is null"; } else if (query.IsMagazineReturn == 1) { joinwhere += "LEFT JOIN (select distinct DMSMemberID from CC_MagazineReturn where Title='" + Utils.StringHelper.SqlFilter(query.ExecCycle) + "') AS cmr ON DMSMember.ID=cmr.DMSMemberID"; where += " AND cmr.DMSMemberID is not null"; } } DataSet ds; SqlParameter[] parameters = { new SqlParameter("@where", SqlDbType.VarChar, 8000), new SqlParameter("@innerwhere", 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), new SqlParameter("@joinwhere", SqlDbType.VarChar, 8000), new SqlParameter("@DeptID", SqlDbType.NVarChar, 200) }; parameters[0].Value = where; parameters[1].Value = innerwhere; parameters[2].Value = order; parameters[3].Value = pageSize; parameters[4].Value = currentPage; parameters[5].Direction = ParameterDirection.Output; parameters[6].Value = joinwhere; parameters[7].Value = query.SelectDeptID; ds = SqlHelper.ExecuteDataset(ConnectionStrings_CRM, CommandType.StoredProcedure, P_CRM_DMSMEMBER_ALONE_SELECT, parameters); totalCount = int.Parse(parameters[5].Value.ToString()); return(ds.Tables[0]); }
public DataTable GetCRMDMSMemberInfo(QueryCRMDMSMember query, string order, int currentPage, int pageSize, out int totalCount) { return(Dal.CRMDMSMember.Instance.GetCRMDMSMemberInfo(query, order, currentPage, pageSize, out totalCount)); }