public DataTable GetStatisticsData(Dictionary <string, object> dict) { string strSql = ""; //加载省份数据 if (string.IsNullOrEmpty("" + dict["ProvinceID"])) { strSql = string.Format(@"select name,count(name) as totalCount from( select D.Name,D.OrderIndex from (select B.Parentitemid from( select area from Crm_Customer where deleted=0 and createtime between to_date('{0}','yyyy-mm-dd') and to_date('{1}','yyyy-mm-dd') and company = {2})A left join crm_categoryitem B on A.Area=B.ID)C left join crm_categoryitem D on C.Parentitemid = D.ID) group by Name,OrderIndex order by OrderIndex", dict["startDate"], dict["endDate"], dict["CompanyId"]); } else //加载市级数据 { strSql = string.Format(@"select name,count(name) as totalCount from( select B.ID,B.Name,B.Parentitemid,B.Orderindex from( select area from Crm_Customer where deleted=0 and createtime between to_date('{0}','yyyy-mm-dd') and to_date('{1}','yyyy-mm-dd') and company = {3} )A left join crm_categoryitem B on A.Area=B.ID where parentitemid = '{2}') group by Name,OrderIndex order by OrderIndex", dict["startDate"], dict["endDate"], dict["ProvinceID"], dict["CompanyId"]); } return(NHelper.ExecuteDataSet(strSql).Tables[0]); }
/// <summary> /// 获取客户消费行为走势数据 /// </summary> /// <param name="dict"></param> /// <returns></returns> public DataTable GetExpenseTrendData(Dictionary <string, object> dict) { DateTime dateBegin = Convert.ToDateTime(dict["startDate"]); DateTime dateEnd = Convert.ToDateTime(dict["endDate"]); string filterSql = " and Deleted=0 "; if (dict.ContainsKey("CustomerID") && !string.IsNullOrEmpty("" + dict["CustomerID"])) { filterSql += string.Format(" and CustomerID='{0}'", dict["CustomerID"]); } filterSql += " And COMPANY = " + dict["CompanyId"]; return(NHelper.GetStatisticsData(NHibernateDatabaseFactory.TableName(typeof(CustormerConsumRecord)), "ConsumptionDate", dateBegin, dateEnd, "AFTERDISCOUNTFREE", "", filterSql)); }
public DataTable GetStatisticsData(Dictionary <string, object> dict) { string strSql = string.Format( @"select ID,Name,count(ID) as totalCount from( select B.ID,B.Name from (select Evaluate from crm_CompanyAction where deleted =0 and company = {2} and ActionDate between to_date('{0}','yyyy-mm-dd') and to_date('{1}','yyyy-mm-dd'))A join crm_categoryitem B on A.Evaluate = B.ID) group by ID,NAME ", dict["startDate"], dict["endDate"], dict["CompanyId"]); return(NHelper.ExecuteDataSet(strSql).Tables[0]); }
/// <summary> /// 条件查询不分页 /// </summary> /// <param name="dict"></param> /// <param name="selectFields"></param> /// <returns></returns> public DataTable GetByCondition(Dictionary <string, object> dict, string selectFields = "*") { StringBuilder stringBuilder = new StringBuilder("select " + selectFields + " From Sys_Operator Where Deleted != 1 "); foreach (var condition in dict) { if (condition.Key == "OperatorName") { stringBuilder.AppendFormat(" And {0} LIKE '%{1}%'", condition.Key, condition.Value).AppendLine(); } else { stringBuilder.AppendFormat(" And {0} = '{1}'", condition.Key, condition.Value).AppendLine(); } } return(NHelper.ExecuteDataSet(stringBuilder.ToString()).Tables[0]); }