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]);
}
