public DataTable GetCC_CrmCustInfo(QueryCrmCustInfo query, string order, int currentPage, int pageSize, out int totalCount)
{
string joinwhere = string.Empty;
string where = GenerateWhereStr(query, out joinwhere);
DataSet ds;
SqlParameter[] parameters =
{
new SqlParameter("@where", SqlDbType.NVarChar, 40000),
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.NVarChar, 4000)
};
parameters[0].Value = where;
parameters[1].Value = order;
parameters[2].Value = pageSize;
parameters[3].Value = currentPage;
parameters[4].Direction = ParameterDirection.Output;
parameters[5].Value = joinwhere;
ds = SqlHelper.ExecuteDataset(ConnectionStrings_CRM, CommandType.StoredProcedure, P_CC_Crm_SELECT_Manage, parameters);
totalCount = int.Parse(parameters[4].Value.ToString());
return(ds.Tables[0]);
}
public DataTable GetCC_CrmCustInfoByAloneForExportCustID(QueryCrmCustInfo query)
{
string joinwhere = string.Empty;
string where = GenerateWhereStr(query, out joinwhere);
DataSet ds;
SqlParameter[] parameters =
{
new SqlParameter("@where", SqlDbType.NVarChar, 4000)
};
parameters[0].Value = where;
ds = SqlHelper.ExecuteDataset(CONNECTIONSTRINGS, CommandType.StoredProcedure, P_CC_CRM_SELECT_BY_ALONE_MANAGE_FOREXPORT, parameters);
return(ds.Tables[0]);
}
/// <summary>
/// 统计 坐席要核实的“CRM客户”数据
/// </summary>
internal void StatCrmCustCheckRecordsByUserID(bool isManager, out int totalCount, out int noProcessCount, out int processingCount, out int finishedCount)
{
Entities.QueryCrmCustInfo query = new QueryCrmCustInfo();
if (CustID != string.Empty)
{
query.CustID = CustID;
}
if (CustName != string.Empty)
{
query.CustName = CustName;
}
if (AbbrName != string.Empty)
{
query.AbbrName = AbbrName;
}
if (BrandIDs != string.Empty)
{
query.Brandids = this.BrandIDs;
}
if (this.ProvinceID != string.Empty && this.ProvinceID != "-1")
{
query.ProvinceID = this.ProvinceID;
}
if (CityID != string.Empty && this.CityID != "-1")
{
query.CityID = CityID;
}
if (CountyID != string.Empty && CountyID != "-1")
{
query.CountyID = CountyID;
}
DateTime dtime;
if (string.IsNullOrEmpty(this.StartTime) == false && DateTime.TryParse(this.StartTime, out dtime))
{
query.LastUpdateTime_StartTime = dtime;
}
if (string.IsNullOrEmpty(this.EndTime) == false && DateTime.TryParse(this.EndTime, out dtime))
{
query.LastUpdateTime_EndTime = dtime;
}
query.StatusNoManage = this.StatusNoManage == "1" ? true : false;
query.StatusManaging = this.StatusManaging == "1" ? true : false;
query.StatusManageFinsh = this.StatusManageFinsh == "1" ? true : false;
if (query.StatusManaging == true)
{
query.AdditionalStatus = this.AdditionalStatus;
}
if (!query.StatusNoManage && !query.StatusManaging && !query.StatusManageFinsh)
{
query.StatusNoManage = true;
query.StatusManaging = true;
query.StatusManageFinsh = true;
}
if (!isManager)
{
query.UserIDAssigned = BLL.Util.GetLoginUserID();
}
else
{
query.UserIDAssigned = Constant.INT_INVALID_VALUE;
}
query.TaskType = 2;
if (!string.IsNullOrEmpty(CallRecordsCount))
{
query.CallRecordsCount = StringHelper.SqlFilter(CallRecordsCount);
}
if (!string.IsNullOrEmpty(CarType))
{
query.CarType = StringHelper.SqlFilter(CarType);
}
int tid = -1;
if (!string.IsNullOrEmpty(TID))
{
query.TID = int.TryParse(TID, out tid) ? tid : -1;
}
if (TaskBatch > 0)
{
query.Batch = TaskBatch;
}
//add by qizq 2012-6-8客户类别
if (!string.IsNullOrEmpty(CustType))
{
query.TypeID = CustType;
}
BLL.ProjectTaskInfo.Instance.StatProjectTaskInfo(2, query, out totalCount, out noProcessCount, out processingCount, out finishedCount);
}
/// <summary>
/// 得到坐席要核实的“CRM客户”数据
/// </summary>
public DataTable GetCrmCustCheckRecordsByUserID(bool isManager, out int totalCount)
{
Entities.QueryCrmCustInfo query = new QueryCrmCustInfo();
if (CustID != string.Empty)
{
query.CustID = CustID;
}
if (CustName != string.Empty)
{
query.CustName = CustName;
}
if (BrandIDs != string.Empty)
{
query.Brandids = this.BrandIDs;
}
if (this.ProvinceID != string.Empty && this.ProvinceID != "-1")
{
query.ProvinceID = this.ProvinceID;
}
if (CityID != string.Empty && this.CityID != "-1")
{
query.CityID = CityID;
}
if (CountyID != string.Empty && CountyID != "-1")
{
query.CountyID = CountyID;
}
if (AbbrName != string.Empty)
{
query.AbbrName = AbbrName;
}
DateTime dtime;
if (string.IsNullOrEmpty(this.StartTime) == false && DateTime.TryParse(this.StartTime, out dtime))
{
query.LastUpdateTime_StartTime = dtime;
}
if (string.IsNullOrEmpty(this.EndTime) == false && DateTime.TryParse(this.EndTime, out dtime))
{
query.LastUpdateTime_EndTime = dtime;
}
query.StatusNoManage = this.StatusNoManage == "1" ? true : false;
query.StatusManaging = this.StatusManaging == "1" ? true : false;
query.StatusManageFinsh = this.StatusManageFinsh == "1" ? true : false;
if (query.StatusManaging == true)
{
query.AdditionalStatus = this.AdditionalStatus;
}
if (!query.StatusNoManage && !query.StatusManaging && !query.StatusManageFinsh)
{
query.StatusNoManage = true;
query.StatusManaging = true;
query.StatusManageFinsh = true;
}
if (!isManager)
{
query.UserIDAssigned = BLL.Util.GetLoginUserID();
}
else
{
query.UserIDAssigned = Constant.INT_INVALID_VALUE;
}
query.TaskType = 2;
if (!string.IsNullOrEmpty(CallRecordsCount))
{
query.CallRecordsCount = StringHelper.SqlFilter(CallRecordsCount);
}
int tid = -1;
if (!string.IsNullOrEmpty(TID))
{
query.TID = int.TryParse(TID, out tid) ? tid : -1;
}
if (!string.IsNullOrEmpty(CarType))
{
query.CarType = CarType;
}
if (TaskBatch > 0)
{
query.Batch = TaskBatch;
}
query.TaskSource = 2;//任务数据来源，1-Excel，2-CRM
//add by qizq 2012-6-8客户类别
if (!string.IsNullOrEmpty(CustType))
{
query.TypeID = CustType;
}
return(BLL.CrmCustInfo.Instance.GetCC_CrmCustInfo(query, "tk.PTID desc", this.CurrentPage, this.PageSize, out totalCount));
}
private string GetList()
{
QueryCrmCustInfo queryCustInfo = new QueryCrmCustInfo();
#region 条件
if (CustID != string.Empty)
{
queryCustInfo.CustID = CustID;
}
if (CustName != string.Empty)
{
queryCustInfo.CustName = CustName;
}
if (CustAbbr != string.Empty)
{
queryCustInfo.AbbrName = CustAbbr;
}
if (BrandIDs != string.Empty)
{
queryCustInfo.BrandID = BrandIDs;
}
if (CarType != string.Empty)
{
queryCustInfo.CarType = CarType;
}
if (ProvinceID > 0)
{
queryCustInfo.ProvinceID = ProvinceID.ToString();
}
if (CityID > 0)
{
queryCustInfo.CityID = CityID.ToString();
}
if (CountyID > 0)
{
queryCustInfo.CountyID = CountyID.ToString();
}
//if (IsHavMember)
//{
// queryCustInfo.IsHaveMember = true;
//}
//if (IsNoHavMember)
//{
// queryCustInfo.IsHaveNoMember = true;
//}
if (!string.IsNullOrEmpty(CustType))
{
queryCustInfo.TypeID = CustType;
}
if (!string.IsNullOrEmpty(Address))
{
queryCustInfo.Address = Address;
}
if (!string.IsNullOrEmpty(TradeMarketID))
{
queryCustInfo.TradeMarketID = TradeMarketID;
}
if (CustStatus != string.Empty && CustStatus != "0,1")
{
queryCustInfo.StatusIDs = CustStatus;
}
if (CooperateStatusIDs != string.Empty)
{
queryCustInfo.CooperateStatusIDs = CooperateStatusIDs;
}
if (CooperatedStatusIDs != string.Empty)
{
queryCustInfo.CooperatedStatusIDs = CooperatedStatusIDs;
}
if (CustLockStatus != string.Empty && CustLockStatus != "0,1")
{
queryCustInfo.Lock = int.Parse(CustLockStatus);
}
if (DataSource != string.Empty && DataSource != "1,0")
{
queryCustInfo.TaskType = int.Parse(DataSource);
}
if (!string.IsNullOrEmpty(RequestBeginMemberCooperatedTime))
{
queryCustInfo.BeginMemberCooperatedTime = RequestBeginMemberCooperatedTime;
}
if (!string.IsNullOrEmpty(RequestEndMemberCooperatedTime))
{
queryCustInfo.EndMemberCooperatedTime = RequestEndMemberCooperatedTime;
}
if (!string.IsNullOrEmpty(RequestBeginNoMemberCooperatedTime))
{
queryCustInfo.BeginNoMemberCooperatedTime = RequestBeginNoMemberCooperatedTime;
}
if (!string.IsNullOrEmpty(RequestEndNoMemberCooperatedTime))
{
queryCustInfo.EndNoMemberCooperatedTime = RequestEndNoMemberCooperatedTime;
}
if (!string.IsNullOrEmpty(RequestMemberCooperateStatus))
{
queryCustInfo.MemberCooperateStatus = RequestMemberCooperateStatus;
}
if (!string.IsNullOrEmpty(RequestCreateCustStartDate))
{
queryCustInfo.CreateTimeStart = RequestCreateCustStartDate;
}
if (!string.IsNullOrEmpty(RequestCreateCustEndDate))
{
queryCustInfo.CreateTimeEnd = RequestCreateCustEndDate;
}
if (!string.IsNullOrEmpty(RequestAreaTypeIDs))
{
queryCustInfo.AreaTypeIDs = RequestAreaTypeIDs;
}
if (!string.IsNullOrEmpty(RequestProjectName))
{
queryCustInfo.ProjectName = RequestProjectName.Trim();
}
if (!string.IsNullOrEmpty(RequestStartMemberCooperatedBeginTime) ||
!string.IsNullOrEmpty(RequestEndMemberCooperatedBeginTime))
{
queryCustInfo.CooperatedStatusIDs = "1";
if (!string.IsNullOrEmpty(RequestStartMemberCooperatedBeginTime))
{
queryCustInfo.StartMemberCooperatedBeginTime = RequestStartMemberCooperatedBeginTime.Trim();
}
if (!string.IsNullOrEmpty(RequestEndMemberCooperatedBeginTime))
{
queryCustInfo.EndMemberCooperatedBeginTime = RequestEndMemberCooperatedBeginTime.Trim();
}
}
#endregion
DataTable dt = BLL.CrmCustInfo.Instance.GetCC_CrmCustIDsByAlone(queryCustInfo);
List <string> colList = new List <string>();
colList.Add("CustID");
string jsonStr = Converter.DataTable2Json(dt, colList);
return(jsonStr);
}
private DataTable GetData()
{
QueryCrmCustInfo queryCustInfo = new QueryCrmCustInfo();
if (CustID != string.Empty)
{
queryCustInfo.CustID = CustID;
}
if (CustName != string.Empty)
{
queryCustInfo.CustName = CustName;
}
if (CustAbbr != string.Empty)
{
queryCustInfo.AbbrName = CustAbbr;
}
if (BrandIDs != string.Empty)
{
queryCustInfo.BrandID = BrandIDs;
}
if (CarType != string.Empty)
{
queryCustInfo.CarType = CarType;
}
if (ProvinceID > 0)
{
queryCustInfo.ProvinceID = ProvinceID.ToString();
}
if (CityID > 0)
{
queryCustInfo.CityID = CityID.ToString();
}
if (CountyID > 0)
{
queryCustInfo.CountyID = CountyID.ToString();
}
if (IsHavMember)
{
queryCustInfo.IsHaveMember = true;
}
if (IsNoHavMember)
{
queryCustInfo.IsHaveNoMember = true;
}
if (!string.IsNullOrEmpty(CustType))
{
queryCustInfo.TypeID = CustType;
}
if (!string.IsNullOrEmpty(Address))
{
queryCustInfo.Address = Address;
}
if (!string.IsNullOrEmpty(TradeMarketID))
{
queryCustInfo.TradeMarketID = TradeMarketID;
}
if (CustStatus != string.Empty && CustStatus != "0,1")
{
queryCustInfo.StatusIDs = CustStatus;
}
if (CooperateStatusIDs != string.Empty)
{
queryCustInfo.CooperateStatusIDs = CooperateStatusIDs;
}
if (CooperatedStatusIDs != string.Empty)
{
queryCustInfo.CooperatedStatusIDs = CooperatedStatusIDs;
}
if (CustLockStatus != string.Empty && CustLockStatus != "0,1")
{
queryCustInfo.Lock = int.Parse(CustLockStatus);
}
if (DataSource != string.Empty && DataSource != "1,0")
{
queryCustInfo.TaskType = int.Parse(DataSource);
}
if (!string.IsNullOrEmpty(RequestBeginMemberCooperatedTime))
{
queryCustInfo.BeginMemberCooperatedTime = RequestBeginMemberCooperatedTime;
}
if (!string.IsNullOrEmpty(RequestEndMemberCooperatedTime))
{
queryCustInfo.EndMemberCooperatedTime = RequestEndMemberCooperatedTime;
}
if (!string.IsNullOrEmpty(RequestBeginNoMemberCooperatedTime))
{
queryCustInfo.BeginNoMemberCooperatedTime = RequestBeginNoMemberCooperatedTime;
}
if (!string.IsNullOrEmpty(RequestEndNoMemberCooperatedTime))
{
queryCustInfo.EndNoMemberCooperatedTime = RequestEndNoMemberCooperatedTime;
}
if (!string.IsNullOrEmpty(RequestMemberCooperateStatus))
{
queryCustInfo.MemberCooperateStatus = RequestMemberCooperateStatus;
}
if (!string.IsNullOrEmpty(RequestCreateCustStartDate))
{
queryCustInfo.CreateTimeStart = RequestCreateCustStartDate;
}
if (!string.IsNullOrEmpty(RequestCreateCustEndDate))
{
queryCustInfo.CreateTimeEnd = RequestCreateCustEndDate;
}
if (!string.IsNullOrEmpty(RequestDistrictName))
{
queryCustInfo.DistrictName = RequestDistrictName;
}
DataTable dt = BLL.CrmCustInfo.Instance.GetCC_CrmCustInfoByAloneForExportCustID(queryCustInfo);
return(dt);
}
private void rptDataBind()
{
QueryCrmCustInfo queryCustInfo = new QueryCrmCustInfo();
if (CustID != string.Empty)
{
queryCustInfo.CustID = CustID;
}
if (CustName != string.Empty)
{
queryCustInfo.CustName = CustName;
}
if (CustAbbr != string.Empty)
{
queryCustInfo.AbbrName = CustAbbr;
}
if (BrandIDs != string.Empty)
{
queryCustInfo.BrandID = BrandIDs;
}
if (CarType != string.Empty)
{
queryCustInfo.CarType = CarType;
}
if (ProvinceID > 0)
{
queryCustInfo.ProvinceID = ProvinceID.ToString();
}
if (CityID > 0)
{
queryCustInfo.CityID = CityID.ToString();
}
if (CountyID > 0)
{
queryCustInfo.CountyID = CountyID.ToString();
}
if (IsHavMember)
{
queryCustInfo.IsHaveMember = true;
}
if (IsNoHavMember)
{
queryCustInfo.IsHaveNoMember = true;
}
if (!string.IsNullOrEmpty(CustType))
{
queryCustInfo.TypeID = CustType;
}
if (!string.IsNullOrEmpty(Address))
{
queryCustInfo.Address = Address;
}
if (!string.IsNullOrEmpty(TradeMarketID))
{
queryCustInfo.TradeMarketID = TradeMarketID;
}
if (CustStatus != string.Empty && CustStatus != "0,1")
{
queryCustInfo.StatusIDs = CustStatus;
}
if (CooperateStatusIDs != string.Empty)
{
queryCustInfo.CooperateStatusIDs = CooperateStatusIDs;
}
if (CooperatedStatusIDs != string.Empty)
{
queryCustInfo.CooperatedStatusIDs = CooperatedStatusIDs;
}
if (CustLockStatus != string.Empty && CustLockStatus != "0,1")
{
queryCustInfo.Lock = int.Parse(CustLockStatus);
}
if (DataSource != string.Empty && DataSource != "1,0")
{
queryCustInfo.TaskType = int.Parse(DataSource);
}
if (!string.IsNullOrEmpty(RequestBeginMemberCooperatedTime))
{
queryCustInfo.BeginMemberCooperatedTime = RequestBeginMemberCooperatedTime;
}
if (!string.IsNullOrEmpty(RequestEndMemberCooperatedTime))
{
queryCustInfo.EndMemberCooperatedTime = RequestEndMemberCooperatedTime;
}
if (!string.IsNullOrEmpty(RequestBeginNoMemberCooperatedTime))
{
queryCustInfo.BeginNoMemberCooperatedTime = RequestBeginNoMemberCooperatedTime;
}
if (!string.IsNullOrEmpty(RequestEndNoMemberCooperatedTime))
{
queryCustInfo.EndNoMemberCooperatedTime = RequestEndNoMemberCooperatedTime;
}
if (!string.IsNullOrEmpty(RequestMemberCooperateStatus))
{
queryCustInfo.MemberCooperateStatus = RequestMemberCooperateStatus;
}
if (!string.IsNullOrEmpty(RequestCreateCustStartDate))
{
queryCustInfo.CreateTimeStart = RequestCreateCustStartDate;
}
if (!string.IsNullOrEmpty(RequestCreateCustEndDate))
{
queryCustInfo.CreateTimeEnd = RequestCreateCustEndDate;
}
if (!string.IsNullOrEmpty(RequestDistrictName))
{
queryCustInfo.DistrictName = RequestDistrictName;
}
DataTable dt = BLL.CrmCustInfo.Instance.GetCC_CrmCustInfoByAlone(queryCustInfo, "", PageCommon.Instance.PageIndex, PageCommon.Instance.PageSize, out RecordCount);
Repeater_Custs.DataSource = dt;
Repeater_Custs.DataBind();
this.AjaxPager_Custs.InitPager(RecordCount);
}
private string GenerateWhereStr(QueryCrmCustInfo query, out string joinWhere)
{
string where = string.Empty;
joinWhere = string.Empty;
if (query.Officetel != Constant.STRING_INVALID_VALUE)
{
where += " AND ci.Officetel='" + StringHelper.SqlFilter(query.Officetel) + "' ";
}
//易湃会员主营品牌 add lxw 2013-1-8
if (query.DMSMemberBrandID != Constant.STRING_INVALID_VALUE)
{
where += " And ci.CustID in ( select CustID from CRM2009.dbo.DMSMember dms where dms.CustID=ci.CustID AND dms.ID IN ( select MemberID From CRM2009.dbo.DMSMember_Brand Where BrandID IN (" + Dal.Util.SqlFilterByInCondition(query.DMSMemberBrandID) + ") AND dms.SyncStatus = 170002))";
}
if (query.BrandID != Constant.STRING_INVALID_VALUE)
{
where += " And ci.CustID in ( select CustID from CRM2009.dbo.Cust_Brand where BrandID in (" + Dal.Util.SqlFilterByInCondition(query.BrandID) + "))";
}
if (query.CityID != Constant.STRING_INVALID_VALUE && query.CityID != "null")
{
where += " And ci.CityID=" + StringHelper.SqlFilter(query.CityID);
}
if (query.contactName != Constant.STRING_INVALID_VALUE)
{
where += " And ci.ContactName like '%" + StringHelper.SqlFilter(query.contactName) + "%'";
}
if (query.CountyID != Constant.STRING_INVALID_VALUE && query.CountyID != "null")
{
where += " And ci.CountyID=" + StringHelper.SqlFilter(query.CountyID);
}
if (query.Address != Constant.STRING_EMPTY_VALUE && query.Address != Constant.STRING_INVALID_VALUE)
{
where += " And ci.Address like '%" + StringHelper.SqlFilter(query.Address) + "%'";
}
if (query.TradeMarketID != Constant.STRING_EMPTY_VALUE && query.TradeMarketID != Constant.STRING_INVALID_VALUE)
{
where += " And ci.TradeMarketID = " + StringHelper.SqlFilter(query.TradeMarketID);
}
if (query.CustName != Constant.STRING_INVALID_VALUE)
{
where += " And ci.CustName like '%" + StringHelper.SqlFilter(query.CustName) + "%'";
}
if (query.AbbrName != Constant.STRING_INVALID_VALUE)
{
where += " And ci.AbbrName like '%" + StringHelper.SqlFilter(query.AbbrName) + "%'";
}
if (query.CustID != Constant.STRING_INVALID_VALUE)
{
where += " And ci.CustID='" + StringHelper.SqlFilter(query.CustID) + "'";
}
if (query.ProvinceID != Constant.STRING_INVALID_VALUE && query.ProvinceID != "null")
{
where += " And ci.ProvinceID=" + StringHelper.SqlFilter(query.ProvinceID);
}
if (query.TrueName != Constant.STRING_INVALID_VALUE)
{
where += " And ui.TrueName like '%" + StringHelper.SqlFilter(query.TrueName) + "%'";
}
if (query.Brandids != Constant.STRING_INVALID_VALUE)
{
string ids = query.Brandids.Trim(',');
if (ids.Length > 0)
{
where += string.Format(" and ci.custID in (select custid from CRM2009.dbo.cust_brand where brandid in ({0}))", Dal.Util.SqlFilterByInCondition(ids));
}
}
if (query.LastUpdateTime_StartTime != Constant.DATE_INVALID_VALUE)
{
where += " And ct.lastUpdateTime>='" + query.LastUpdateTime_StartTime.ToString("yyyy-MM-dd") + "'";
}
if (query.LastUpdateTime_EndTime != Constant.DATE_INVALID_VALUE)
{
where += " And ct.lastUpdateTime<'" + query.LastUpdateTime_EndTime.AddDays(1).ToString("yyyy-MM-dd") + "'";
}
if (query.Lock != Constant.INT_INVALID_VALUE)
{
where += " And ci.lock=" + query.Lock;
}
if (query.CarType != Constant.STRING_EMPTY_VALUE && query.CarType != null)
{
where += " And ci.CarType in (" + Dal.Util.SqlFilterByInCondition(query.CarType) + ")";
}
if (query.StatusNoManage || query.StatusManaging || query.StatusManageFinsh || query.StatusNoAssign)
{
where += " And ( 1=-1";
if (query.StatusNoManage)
{
if (query.LastUpdateTime_StartTime == Constant.DATE_INVALID_VALUE && query.LastUpdateTime_EndTime == Constant.DATE_INVALID_VALUE)
{
where += " or tk.TaskStatus=180000";
}
}
if (query.StatusManaging)
{
string sqlT = " or ((tk.TaskStatus=180001 or tk.TaskStatus=180002 or tk.TaskStatus=180009)";
StringBuilder sbT = new StringBuilder();
if (string.IsNullOrEmpty(query.AdditionalStatus) == false)
{
sqlT = sqlT + "and ({0}))";
foreach (string s in query.AdditionalStatus.Split(','))
{
string ss = s.Trim();
if (string.IsNullOrEmpty(ss) == false)
{
if (sbT.Length > 0)
{
sbT.Append(" or ");
}
//if (ss.ToLower() == "as_a")
//{
// sbT.Append(string.Format(" tas.AdditionalStatus='{0}' or tas.AdditionalStatus is null ", ss));
//}
//else
//{
sbT.Append(string.Format(" tas.AdditionalStatus='{0}' ", ss));
//}
}
}
}
else
{
sqlT = sqlT + "or ({0}))";
sbT.Append(" 1=-1 ");
}
where += string.Format(sqlT, sbT.ToString());
}
if (query.StatusManageFinsh)
{
where += " or (tk.TaskStatus between 180003 and 180008) or tk.TaskStatus=180010 or tk.TaskStatus=180011";
}
if (query.StatusNoAssign)
{
where += " or (tk.taskStatus is null)";
}
where += ")";
}
else
{
if (query.LastUpdateTime_StartTime != Constant.DATE_INVALID_VALUE || query.LastUpdateTime_EndTime != Constant.DATE_INVALID_VALUE)
{
where += "and tk.taskStatus>180000";
}
}
//分配员工
if (query.UserIDAssigned != Constant.INT_INVALID_VALUE)
{
if (query.UserIDAssigned == 0)
{
where += " And ccte.UserID IS NOT NULL";
}
else
{
where += " And ccte.UserID IS NOT NULL and ccte.UserID=" + query.UserIDAssigned;
}
}
if (query.TaskType != Constant.INT_INVALID_VALUE)
{
if (query.TaskType == 0)
{
where += string.Format(" AND ci.CreateSource=0");
}
else if (query.TaskType == 1)
{
where += string.Format(" And ci.CreateSource=1 ");
}
}
if (!string.IsNullOrEmpty(query.StatusIDs))
{
where += " and ci.Status IN (" + Dal.Util.SqlFilterByInCondition(query.StatusIDs) + ")";
}
else
{
where += " and (ci.Status=0 or ci.status=1)";
}
if ((query.IsHaveMember && query.IsHaveNoMember) || (!query.IsHaveMember && !query.IsHaveNoMember))
{
}
else
{
if (query.IsHaveMember)
{
where += " and EXISTS (select CustID from CRM2009.dbo.DMSMember Where DMSMember.CustID = ci.CustID and status>=0) ";
}
if (query.IsHaveNoMember)
{
where += " and NOT EXISTS (select CustID from CRM2009.dbo.DMSMember Where DMSMember.CustID = ci.CustID and status>=0) ";
}
}
if (!string.IsNullOrEmpty(query.CooperatedStatusIDs))
{
if (query.CooperatedStatusIDs == "1" || query.CooperatedStatusIDs.Contains("1"))//有排期
{
where += " and (ci.CustID IN (select CustID from CRM2009.dbo.DMSMember Where Status=0 And SyncStatus=170002 And Cooperated=1 ";
where += " And 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<='" + StringHelper.SqlFilter(query.EndMemberCooperatedTime) + " 23:59:59' And endtime >='" + StringHelper.SqlFilter(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>'" + StringHelper.SqlFilter(query.EndNoMemberCooperatedTime) + @" 23:59:59' OR endtime <'" + StringHelper.SqlFilter(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 ) ";
}
where += ")) ";
}
if (!string.IsNullOrEmpty(query.StartMemberCooperatedBeginTime) ||
!string.IsNullOrEmpty(query.EndMemberCooperatedBeginTime))
{
where += " and (ci.CustID IN (select CustID from CRM2009.dbo.DMSMember Where Status=0 And SyncStatus=170002 And Cooperated=1 ";
where += " And 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 (ci.CustID IN (select CustID from CRM2009.dbo.DMSMember Where Status=0 And (Cooperated=0 OR Cooperated IS NULL) AND SyncStatus!=170008)) ";
}
}
#region 之前“有排期”、“无排期”逻辑
//if ((!string.IsNullOrEmpty(query.CooperatedStatusIDs) || !string.IsNullOrEmpty(query.CooperateStatusIDs)) &&
// !(query.CooperatedStatusIDs == "1,0" && query.CooperateStatusIDs == "1"))
//{
// ArrayList alWhereOR = new ArrayList();
// if (!string.IsNullOrEmpty(query.CooperateStatusIDs))
// {
// alWhereOR.Add("ci.CooperateStatus IN (" + query.CooperateStatusIDs + ")");
// }
// if (query.CooperatedStatusIDs == "1")
// {
// alWhereOR.Add("(ci.CustID IN (select CustID from DMSMember Where Status=0 And Cooperated IN (" + query.CooperatedStatusIDs + ") And CooperateStatus=0))");
// }
// if (query.CooperatedStatusIDs == "0")
// {
// alWhereOR.Add("(ci.CustID IN (select CustID from DMSMember Where Status=0 And (Cooperated IN (" + query.CooperatedStatusIDs + ") OR Cooperated IS NULL)))");
// }
// string temp = string.Join(" OR ", (string[])alWhereOR.ToArray(typeof(string)));
// if (temp != string.Empty)
// {
// where += " And (" + temp + ")";
// }
//}
#endregion
if (!string.IsNullOrEmpty(query.TypeID))
{
where += " and ci.TypeID in (" + Dal.Util.SqlFilterByInCondition(query.TypeID) + ")";
}
if (query.Batch != Constant.INT_INVALID_VALUE)
{
if (query.Batch == -1)
{
where += " and (tk.tid in (a.TID) or tk.Batch is null)";
}
else if (query.Batch == 0)
{
where += " and tk.Batch is null";
}
else
{
where += " and tk.Batch=" + query.Batch;
}
}
if (query.TID != Constant.INT_INVALID_VALUE)
{
where += " and tk.TID=" + query.TID;
}
if (query.TaskSource != Constant.INT_INVALID_VALUE)
{
where += " and tk.Source=" + query.TaskSource;
}
if (!string.IsNullOrEmpty(query.CreateTimeStart))
{
where += " and ci.CreateTime >= '" + StringHelper.SqlFilter(query.CreateTimeStart) + " 0:0:0' ";
}
if (!string.IsNullOrEmpty(query.CreateTimeEnd))
{
where += " and ci.CreateTime <= '" + StringHelper.SqlFilter(query.CreateTimeEnd) + " 23:59:59' ";
}
if (!string.IsNullOrEmpty(query.DistrictName))
{
where += " AND ISNULL(area3.DistinctName,ISNULL(area2.DistinctName,'')) = '" + StringHelper.SqlFilter(query.DistrictName.Trim()) + "'";
}
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 + "ci.CountyID IN (SELECT AreaID FROM AreaType WHERE Type=2 And Value=1) ";
where += temp + @"(ci.CountyID IN (SELECT AreaID FROM CRM2009.dbo.AreaType WHERE Type=2 And Value=1) OR (
(ci.CountyID=-1 or ci.CountyID is null or ci.CountyID='') And ci.CityID IN (SELECT AreaID FROM CRM2009.dbo.AreaType WHERE Type=1 And Value=1)))";
break;
case "2": //163郊区
where += temp + @"ci.CountyID IN (SELECT AreaID FROM CRM2009.dbo.AreaType WHERE Type=2 And Value=2)
And ci.CityID IN (SELECT AreaID FROM CRM2009.dbo.AreaType WHERE Type=1 And Value=1)";
break;
case "3": //178无人城城区
where += temp + "ci.CityID IN (SELECT AreaID FROM CRM2009.dbo.AreaType WHERE Type=1 And Value=2) ";
break;
//case "4"://178无人城郊区
// where += temp + "ci.CountyID IN (SELECT AreaID FROM AreaType WHERE Type=2 And Value=2) ";
// break;
default:
break;
}
if (i == typeids.Length - 1)
{
where += " ) ";
}
}
}
if (query.IsMagazineReturn != Constant.INT_INVALID_VALUE)
{
if (query.IsMagazineReturn == 0)
{
joinWhere += "LEFT JOIN (select distinct custID from CC_MagazineReturn) AS cmr ON ci.CustID=cmr.CustID";
where += " AND cmr.CustID is null";
}
else if (query.IsMagazineReturn == 1)
{
joinWhere += "LEFT JOIN (select distinct custID from CC_MagazineReturn where Title='" + Utils.StringHelper.SqlFilter(query.ExecCycle) + "') AS cmr ON ci.CustID=cmr.CustID";
where += " AND cmr.custId is not null";
}
}
if (query.ProjectName != Constant.STRING_INVALID_VALUE)
{
where += " and ci.CustID in (select custid from MJ2009.dbo.OrderInfo where projectName='" + SqlFilter(query.ProjectName) + "' and status>=0) ";
}
return(where);
}
/// <summary>
///
/// </summary>
/// <param name="queryCustInfo"></param>
/// <returns></returns>
public DataTable GetCC_CrmCustInfoByAloneForExportCustID(QueryCrmCustInfo queryCustInfo)
{
return(Dal.CrmCustInfo.Instance.GetCC_CrmCustInfoByAloneForExportCustID(queryCustInfo));
}
public DataTable GetCC_CrmCustIDsByAlone(QueryCrmCustInfo queryCustInfo)
{
return(Dal.CrmCustInfo.Instance.GetCC_CrmCustIDsByAlone(queryCustInfo));
}
/// <summary>
/// 查询crm客户(同一客户只显示最后一个批次，最后处理状态)
/// </summary>
/// <param name="queryCustInfo"></param>
/// <param name="order"></param>
/// <param name="currentPage"></param>
/// <param name="pageSize"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public DataTable GetCC_CrmCustInfoByAlone(QueryCrmCustInfo queryCustInfo, string order, int currentPage, int pageSize, out int totalCount)
{
return(Dal.CrmCustInfo.Instance.GetCC_CrmCustInfoByAlone(queryCustInfo, order, currentPage, pageSize, out totalCount));
}
