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