public DataTable GetCustomerStructure(Dictionary <string, object> dictionary)
{
var companyId = dictionary["Company"];
var caType = dictionary["CaType"].ToString();
var startDate = DateTime.Parse(dictionary["startDate"].ToString());
var endDate = DateTime.Parse(dictionary["endDate"].ToString());
string sql =
@"SELECT COUNT(R.ID) AS TOTAL,C.ID ,C.NAME FROM {2} R
INNER JOIN {4} C ON C.ID = {3}
WHERE R.DELETED <> 1
AND R.CUSTOMERSOURCE IS NOT NULL
AND TO_CHAR( R.CREATETIME ,'yyyy-mm-dd') >='{0}'
AND TO_CHAR( R.CREATETIME ,'yyyy-mm-dd') <='{1}'
AND Company = {5}
GROUP BY C.ID ,C.NAME";
string orderBy = caType == "source" ? "R.CUSTOMERSOURCE" : "R.CUSTOMERBUSINESS";
return
(this.ExecuteDataTable(string.Format(sql,
startDate.ToString("yyyy-MM-dd"),
endDate.ToString("yyyy-MM-dd"),
NHibernateDatabaseFactory.TableName(typeof(Customer)),
orderBy,
NHibernateDatabaseFactory.TableName(typeof(CategoryItem)),
companyId
)));
}
public IList <OprHist> OprHistInfoList <T1>(int requestId)
{
var requestType = NHibernateDatabaseFactory.TableName(typeof(T1));
return
(this.GetSession().CreateQuery(string.Format(
"From OprHist Where RequestType = '{0}' And RequestId = {1} Order By OprDateTime ", requestType,
requestId)).List <OprHist>());
}
public bool NoSendInfo(string mobile, Template template, DateTime holidayDate)
{
return(this.GetSession().CreateSQLQuery(
String.Format(
@"SELECT COUNT(1) FROM {3} M WHERE
M.TEMPLATE = {0}
AND M.TONUMBER = '{1}'
AND to_char(M.MESSAGEDATE,'yyyy-mm-dd') = '{2}'",
template.Id, mobile, holidayDate.ToString("yyyy-MM-dd"),
NHibernateDatabaseFactory.TableName(typeof(MessageContent))
)).UniqueResult().ObjectToInt() <= 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));
}
/// <summary>
/// 统计客户应收
/// </summary>
/// <param name="dict"></param>
/// <returns></returns>
public DataTable GetStatisticsData(Dictionary <string, object> dict)
{
string strSql = string.Format(@"select name,sum(fee) as totalCount from (
select B.NAME,A.fee from
(select customerid,AFTERDISCOUNTFREE,RECEIVEMONEY,(AFTERDISCOUNTFREE-RECEIVEMONEY) as fee from {2}
where Deleted=0 And COMPANY = {3} and AFTERDISCOUNTFREE-RECEIVEMONEY >0
and ConsumptionDate between to_date('{0}','yyyy-mm-dd') and to_date('{1}','yyyy-mm-dd')
) A left join Crm_Customer B on A.customerid = B.ID)
where fee > 0
group by name", dict["startDate"], dict["endDate"], NHibernateDatabaseFactory.TableName(typeof(CustormerConsumRecord)), dict["CompanyId"]);
return(WebCrm.Framework.DependencyResolver.Resolver <IChartRepository>().ExecuteDataTable(strSql));
}
public DataTable GetCustomerAgeStructure(Dictionary <string, object> dictionary)
{
string sql =
@"
SELECT SUM(T.IDCOUNT) as AgeSum,
(CASE
WHEN T.yearDiff < 20 THEN
'20岁以下'
WHEN T.yearDiff >= 20 AND T.yearDiff < 30 THEN
'20-30岁'
WHEN T.yearDiff >= 30 AND T.yearDiff < 40 THEN
'30-40岁'
WHEN T.yearDiff >= 40 AND T.yearDiff < 50 THEN
'40-50岁'
ELSE
'50岁以上'
END) AS AGE
FROM (select count(id) AS IDCOUNT,
EXTRACT(year FROM sysdate) - EXTRACT(year FROM birthday) as yearDiff
from {0} WHERE birthday >= to_date('{1}', 'yyyy-mm-dd') AND birthday <=to_date('{2}', 'yyyy-mm-dd') And company = {3}
AND DELETED <>1
group by EXTRACT(year FROM sysdate) - EXTRACT(year FROM birthday)) T
group by (CASE
WHEN T.yearDiff < 20 THEN
'20岁以下'
WHEN T.yearDiff >= 20 AND T.yearDiff < 30 THEN
'20-30岁'
WHEN T.yearDiff >= 30 AND T.yearDiff < 40 THEN
'30-40岁'
WHEN T.yearDiff >= 40 AND T.yearDiff < 50 THEN
'40-50岁'
ELSE
'50岁以上'
END)
";
var startDate = DateTime.Parse(dictionary["startDate"].ToString());
var endDate = DateTime.Parse(dictionary["endDate"].ToString());
var companyId = dictionary["CompanyId"].ToString();
return
(this.ExecuteDataTable(string.Format(sql,
NHibernateDatabaseFactory.TableName(typeof(CustomerInfo)),
startDate.ToString("yyyy-MM-dd"), endDate.ToString("yyyy-MM-dd"),
companyId)));
}
public void Query(PageQuery <CustomerContactQuery, CustomerContactInfo> pageQuery)
{
StringBuilder stringBuilder =
new StringBuilder(string.Format("FROM {0} T",
NHibernateDatabaseFactory.TableName(typeof(CustomerContactInfo))));
if (pageQuery.Condition != null)
{
var companyId = pageQuery.OperatorUser != null && pageQuery.OperatorUser.Company != null
? pageQuery.OperatorUser.Company.Id
: 0;
stringBuilder.AppendFormat(" INNER JOIN CRM_CUSTOMER C ON C.COMPANY = {0} ", companyId).AppendLine();
if (pageQuery.Condition.BussinessPerson != null)
{
stringBuilder.AppendLine(" AND C.ID = T.CUSTOMERID").AppendLine();
}
stringBuilder.AppendLine(" WHERE 1=1 ");
if (pageQuery.Condition.EndBirthday.HasValue)
{
stringBuilder.AppendFormat(
@" And (to_char(t.BIRTHDAY,'mm-dd') >= '{0}')
And (to_char(t.BIRTHDAY,'mm-dd') <= '{1}')",
pageQuery.Condition.EndBirthday.Value.ToString("MM-dd"), DateTime.Now.ToString("MM-dd")).
AppendLine();
}
if (pageQuery.Condition.BussinessPerson != null)
{
stringBuilder.AppendLine(string.Format(" And C.BELONGPERSON = {0}",
pageQuery.Condition.BussinessPerson.Id));
}
}
pageQuery.Result = this.GetSession()
.CreateSQLQuery("SELECT T.* " + stringBuilder.ToString())
.AddEntity("T", typeof(CustomerContactInfo))
.SetPagerInfo(pageQuery.Pager)
.List <CustomerContactInfo>();
pageQuery.RecordCount =
this.GetSession().CreateSQLQuery("Select Count(*) " + stringBuilder.ToString())
.UniqueResult()
.ObjectToInt();
}
public DataTable GetIncomeTrend(Dictionary <string, object> dict)
{
var startDate = dict.ContainsKey("startDate") ? (dict["startDate"]).ToString() : DateTime.Now.AddMonths(-1).ToShortDateString();
var endDate = dict.ContainsKey("endDate") ? (dict["endDate"]).ToString() : DateTime.Now.ToShortDateString();
string sql =
string.Format(
@"
SELECT R.TOTAL,I.NAME,I.ID FROM (
SELECT T.CONSUMERBUSINESSTYPE,SUM(T.SPENDTOTAL) AS TOTAL FROM {0} T
WHERE T.DELETED <>1 AND TO_CHAR(T.CONSUMPTIONDATE,'yyyy-mm-dd') >= '{2}'
AND TO_CHAR(T.CONSUMPTIONDATE,'yyyy-mm-dd') <='{3}'
AND T.Company = {4}
GROUP BY T.CONSUMERBUSINESSTYPE
) R INNER JOIN {1} I ON R.CONSUMERBUSINESSTYPE = I.ID
",
NHibernateDatabaseFactory.TableName(typeof(CustormerConsumRecord)),
NHibernateDatabaseFactory.TableName(typeof(CategoryItem)),
DateTime.Parse(startDate).ToString("yyyy-MM-dd"),
DateTime.Parse(endDate).ToString("yyyy-MM-dd"),
dict["CompanyId"]);
return(this.ExecuteDataTable(sql));
}
private static ISession GetCurrentSession()
{
ISession session = NHibernateDatabaseFactory.GetSession();
return(session);
}
public void TestTableName()
{
this.Log(NHibernateDatabaseFactory.TableName <WebCrm.Model.CategoryItem>());
}
