/// <summary> /// 按照查询条件查询 分页 /// </summary> /// <param name="queryExcelCustInfo">查询值对象,用来存放查询条件</param> /// <param name="currentPage">页号,-1不分页</param> /// <param name="totalCount">总行数</param> /// <param name="pageSize">每页记录数</param> /// <returns>销售网络集合</returns> public DataTable GetExcelCustInfo_Manage(QueryExcelCustInfo query, int currentPage, int pageSize, out int totalCount) { string where = GenerateWhereStr(query); string order = " ei.CreateTime desc"; 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) }; 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_EXCELCUSTINFO_SELECT, parameters); totalCount = int.Parse(parameters[4].Value.ToString()); return(ds.Tables[0]); }
public void Delete(QueryExcelCustInfo query) { string where = string.Empty; where = GenerateWhereStr(query); SqlParameter[] parameters = { new SqlParameter("@Where", SqlDbType.VarChar, 8000) }; parameters[0].Value = where; SqlHelper.ExecuteNonQuery(CONNECTIONSTRINGS, CommandType.StoredProcedure, P_EXCELCUSTINFO_Delete_By_Contion, parameters); }
public bool HasTaskStatusMoreThanAssign(QueryExcelCustInfo query) { bool result = false; string where = string.Empty; where = GenerateWhereStr(query); SqlParameter[] parameters = { new SqlParameter("@Where", SqlDbType.VarChar, 8000) }; parameters[0].Value = where; using (SqlDataReader reader = SqlHelper.ExecuteReader(CONNECTIONSTRINGS, CommandType.StoredProcedure, P_EXCELCUSTINFO_Select_TaskStatus_By_Contion, parameters)) { if (reader.Read()) { result = true; } } return(result); }
public void StatNewCustCheckRecordsByUserID(QueryExcelCustInfo query, out int totalCount, out int noProcessCount, out int processingCount, out int finishedCount) { string where = GenerateWhereStr(query); SqlParameter[] parameters = { new SqlParameter("@where", SqlDbType.NVarChar, 40000), new SqlParameter("@totalCount", SqlDbType.Int), new SqlParameter("@NoProcessCount", SqlDbType.Int, 4), new SqlParameter("@ProcessingCount", SqlDbType.Int, 4), new SqlParameter("@FinishedCount", SqlDbType.Int, 4) }; parameters[0].Value = where; parameters[1].Direction = ParameterDirection.Output; parameters[2].Direction = ParameterDirection.Output; parameters[3].Direction = ParameterDirection.Output; parameters[4].Direction = ParameterDirection.Output; SqlHelper.ExecuteNonQuery(CONNECTIONSTRINGS, CommandType.StoredProcedure, "p_ExcelCustInfo_StatCountOfRecords", parameters); totalCount = int.Parse(parameters[1].Value.ToString()); noProcessCount = int.Parse(parameters[2].Value.ToString()); processingCount = int.Parse(parameters[3].Value.ToString()); finishedCount = int.Parse(parameters[4].Value.ToString()); }
public bool BatchInsertProjectTaskInfo_Employee(QueryExcelCustInfo info, int userId) { return(Dal.ProjectTaskInfo.Instance.BatchInsertProjectTaskInfo_Employee(info, userId)); }
private string GenerateWhereStr(QueryExcelCustInfo query) { string where = string.Empty; if (query.BrandName != Constant.STRING_EMPTY_VALUE) { where += " And ei.BrandName like '%" + StringHelper.SqlFilter(query.BrandName) + "%'"; } if (query.ProvinceName != Constant.STRING_EMPTY_VALUE) { query.ProvinceName = StringHelper.SqlFilter(query.ProvinceName).Replace("省", ""); where += " And ei.ProvinceName like '%" + query.ProvinceName + "%'"; } if (query.CityName != Constant.STRING_EMPTY_VALUE) { query.CityName = StringHelper.SqlFilter(query.CityName).Replace("市", ""); where += " And ei.CityName like '%" + query.CityName + "%'"; } if (query.CountyName != Constant.STRING_EMPTY_VALUE) { where += " And ei.CountyName like '%" + StringHelper.SqlFilter(query.CountyName) + "%'";; } if (query.TypeNames != Constant.STRING_EMPTY_VALUE) { where += "And ei.TypeName in (select * from dbo.f_split('" + Dal.Util.SqlFilterByInCondition(query.TypeNames) + "',','))"; } if (query.CustName != Constant.STRING_EMPTY_VALUE) { where += " And ei.CustName like '%" + StringHelper.SqlFilter(query.CustName) + "%'"; } if (query.ID != Constant.INT_INVALID_VALUE) { where += " And ei.ID=" + query.ID; } if (query.TrueName != Constant.STRING_EMPTY_VALUE) { where += " And ui.TrueName like '%" + StringHelper.SqlFilter(query.TrueName) + "%'"; } 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.CarType != Constant.STRING_EMPTY_VALUE) { where += " And ei.CarType in (" + Dal.Util.SqlFilterByInCondition(query.CarType) + ")"; } //add by qizq 添加客户类别2012-6-8 if (!string.IsNullOrEmpty(query.TypeID)) { where += " and ct.typeid in (" + Dal.Util.SqlFilterByInCondition(query.TypeID.Trim()) + ")"; } if (query.StatusNoManage || query.StatusManaging || query.StatusManageFinsh || query.StatusNoAssign) { where += "And ( 1=-1 "; if (query.StatusNoManage) { where += " or tk.TaskStatus=180000 "; } if (query.StatusManaging) { string sqlT = " or ((tk.TaskStatus=180001 or tk.TaskStatus=180002 or tk.TaskStatus=180009) and ({0}))"; StringBuilder sbT = new StringBuilder(); if (string.IsNullOrEmpty(query.AdditionalStatus) == false) { 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 { sbT.Append(" 1=1 "); } where += string.Format(sqlT, sbT.ToString()); } if (query.StatusManageFinsh) { where += " or (tk.TaskStatus between 180003 and 180008)"; } if (query.StatusNoAssign) { where += " or ccte.TID is null"; } where += ")"; } 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; } } //where += " and tk.Status=0 "; 暂不需要 if (query.CallRecordsCount != Constant.STRING_INVALID_VALUE) { where += " and (SELECT Count(*) FROM CallRecordInfo WHERE TaskTypeID=1 AND (TaskID=tk.PTID OR CustID=tk.CRMCustID))=" + StringHelper.SqlFilter(query.CallRecordsCount); } if (query.PTID != Constant.STRING_INVALID_VALUE) { where += " and tk.PTID='" + StringHelper.SqlFilter(query.PTID) + "'"; } return(where); }
public bool HasTaskStatusMoreThanAssign(QueryExcelCustInfo query) { return(Dal.ExcelCustInfo.Instance.HasTaskStatusMoreThanAssign(query)); }
public void Delete(QueryExcelCustInfo query) { Dal.ExcelCustInfo.Instance.Delete(query); }
/// <summary> /// 统计 /// </summary> public void StatNewCustCheckRecordsByUserID(QueryExcelCustInfo query, out int totalCount, out int noProcessCount, out int processingCount, out int finishedCount) { Dal.ExcelCustInfo.Instance.StatNewCustCheckRecordsByUserID(query, out totalCount, out noProcessCount, out processingCount, out finishedCount); }
public DataTable GetExcelCustInfo_Manage(QueryExcelCustInfo queryExcelCustInfo, int currentPage, int pageSize, out int totalCount) { return(Dal.ExcelCustInfo.Instance.GetExcelCustInfo_Manage(queryExcelCustInfo, currentPage, pageSize, out totalCount)); }