public IList GetMaxCollector(DateTime month) { StringBuilder sql = new StringBuilder(); sql.AppendLine(@" select top 1 isnull(a.COLLECTOR_ID,'') COLLECTOR_ID,isnull(a.LOAN_BASIC_INSTALLMENT,0) LOAN_BASIC_INSTALLMENT, isnull(b.INSTALLMENT_BASIC,0) INSTALLMENT_BASIC from ( select loan.COLLECTOR_ID, sum(loan.LOAN_BASIC_INSTALLMENT) LOAN_BASIC_INSTALLMENT from dbo.T_LOAN loan where loan.LOAN_STATUS = 'OK' and loan.COLLECTOR_ID is not null group by loan.COLLECTOR_ID having sum(loan.LOAN_BASIC_INSTALLMENT) > 0 ) a left join ( select ins.EMPLOYEE_ID, sum(ins.INSTALLMENT_BASIC) INSTALLMENT_BASIC from dbo.T_INSTALLMENT ins where ins.INSTALLMENT_MATURITY_DATE >= :startDate and ins.INSTALLMENT_MATURITY_DATE <= :endDate and ins.INSTALLMENT_STATUS = 'Paid' and ins.EMPLOYEE_ID is not null group by ins.EMPLOYEE_ID ) b on a.COLLECTOR_ID = b.EMPLOYEE_ID order by b.INSTALLMENT_BASIC desc, a.LOAN_BASIC_INSTALLMENT desc; "); ISQLQuery q = Session.CreateSQLQuery(sql.ToString()); q.SetDateTime("startDate", month); q.SetDateTime("endDate", month.AddMonths(1).AddDays(-1)); q.AddScalar("COLLECTOR_ID", NHibernateUtil.String); q.AddScalar("LOAN_BASIC_INSTALLMENT", NHibernateUtil.Decimal); q.AddScalar("INSTALLMENT_BASIC", NHibernateUtil.Decimal); IList list = q.List(); return(list); }
public void UpdateLastForumsNotification(IEnumerable <string> notifiedMailAddresses, DateTime notificationTime) { // Users cannot edit their mailadresses so there is no SQL injection possible string joinedMailAddresses = string.Join(",", notifiedMailAddresses.Select(m => "'" + m + "'")); ISQLQuery updateQuery = this.Session.CreateSQLQuery( @"UPDATE MediaCommUsers SET LastForumsNotification = :notificationTime WHERE EMailAddress IN ( " + joinedMailAddresses + " )"); updateQuery.SetDateTime("notificationTime", notificationTime); updateQuery.ExecuteUpdate(); }
private IQuery addDateFilterAndSortingToQuery(IQuery sourceQuery) { string modifiedQueryString = sourceQuery.QueryString; bool dateFilterExists = false; //добавляем фильтр по дате if (StartPeriod != DateTime.MinValue && EndPeriod != DateTime.MinValue) { dateFilterExists = true; modifiedQueryString += " and test_datetime between :startPeriod and :endPeriod"; } //добавляем опции сортировки if (SortOptions != null) { modifiedQueryString += GetSortQueryEnding(SortOptions); } ISQLQuery modifiedQuery = _session.CreateSQLQuery(modifiedQueryString); FieldInfo returnTypesFi = sourceQuery.GetType().GetField("queryReturns", BindingFlags.NonPublic | BindingFlags.Instance); //SQLQueryScalarReturn ArrayList returns = (ArrayList)returnTypesFi.GetValue(sourceQuery); foreach (object value in returns) { modifiedQuery.AddScalar(((SQLQueryScalarReturn)value).ColumnAlias, ((SQLQueryScalarReturn)value).Type); } if (dateFilterExists) { modifiedQuery.SetDateTime("startPeriod", StartPeriod); modifiedQuery.SetDateTime("endPeriod", EndPeriod); } return(modifiedQuery); }
public IList GetMaxSalesman(DateTime month) { StringBuilder sql = new StringBuilder(); sql.AppendLine(@" select top 1 isnull(a.SALESMAN_ID,'') SALESMAN_ID ,isnull(a.LOAN_CREDIT_PRICE,0) LOAN_CREDIT_PRICE ,isnull(b.COMMISSION_VALUE,0) COMMISSION_VALUE from ( select loan.SALESMAN_ID, sum(loan.LOAN_CREDIT_PRICE) LOAN_CREDIT_PRICE from dbo.T_LOAN loan where loan.LOAN_STATUS = 'OK' and loan.SALESMAN_ID is not null and loan.LOAN_SUBMISSION_DATE >= :startDate and loan.LOAN_SUBMISSION_DATE <= :endDate group by loan.SALESMAN_ID having sum(loan.LOAN_CREDIT_PRICE) > 0 ) a left join ( select com.COMMISSION_VALUE from dbo.M_COMMISSION com where com.COMMISSION_STATUS = 'SA' and (com.COMMISSION_START_DATE <= :startDate or com.COMMISSION_END_DATE >= :endDate ) ) b on 1=1 order by a.LOAN_CREDIT_PRICE desc; "); ISQLQuery q = Session.CreateSQLQuery(sql.ToString()); q.SetDateTime("startDate", month); q.SetDateTime("endDate", month.AddMonths(1).AddDays(-1)); q.AddScalar("SALESMAN_ID", NHibernateUtil.String); q.AddScalar("LOAN_CREDIT_PRICE", NHibernateUtil.Decimal); q.AddScalar("COMMISSION_VALUE", NHibernateUtil.Decimal); IList list = q.List(); return(list); }
public bool CheckDay(DateTime checkDay, string type) { StringBuilder sql = new StringBuilder(); sql.Append("SELECT count(*) as Count FROM DbHolidayProfile hp INNER JOIN DbHoliday h ON hp.Id = h.HolidayProfileId where hp.IsApprove = 1 AND hp.Year = :Year AND h.Date = :Date AND hp.Type = :Type"); ISQLQuery query = GetCurrentSession().CreateSQLQuery(sql.ToString()); query.SetInt32("Year", checkDay.Year); query.SetDateTime("Date", checkDay.Date); query.SetString("Type", type); query.AddScalar("Count", NHibernateUtil.Int32); int count = query.UniqueResult <Int32>(); if (count > 0 || (checkDay.DayOfWeek == DayOfWeek.Saturday || checkDay.DayOfWeek == DayOfWeek.Sunday)) { return(true); } return(false); }
public void CalculatePrize(DateTime resultDate) { // StringBuilder sql = new StringBuilder(); // sql.AppendLine(@" // update TSalesDet det // set det.SalesDetStatus = :detStatus // , det.SalesDetPrize = g.GamePrize // from TSalesDet det, // TSales s, // TResult res, // TResultDet res_det, // MGame g // where s.SalesDate = res.ResultDate // and det.SalesId.Id = s.Id // and res.Id = res_det.ResultId.Id // and det.GameId.Id = res_det.GameId.Id // and g.Id = res_det.GameId.Id // and s.SalesDate = :resultDate "); // IQuery q = Session.CreateQuery(sql.ToString()); // q.SetString("detStatus", Enums.EnumSalesDetStatus.Win.ToString()); // q.SetDateTime("resultDate", resultDate); //q.ExecuteUpdate(); StringBuilder sql = new StringBuilder(); //EXECUTE [SP_CALCULATE_PRIZE] // @SalesDetStatus = :detStatus // ,@SalesDate = :resultDate //calculate use query, not SP cause sql compact not support SP //SQL CE not support update table from other table, use subquery in where sql.AppendLine(@" update T_SALES_DET set SALES_DET_STATUS = null , SALES_DET_PRIZE = null , DATA_STATUS = :DATA_STATUS , MODIFIED_BY = :MODIFIED_BY , MODIFIED_DATE = :MODIFIED_DATE where SALES_ID in ( select s.SALES_ID from T_SALES s where s.SALES_DATE = :resultDate ); "); //update det // set SALES_DET_STATUS = null // ,SALES_DET_PRIZE = null // from T_SALES_DET det, T_SALES s // where det.SALES_ID = s.SALES_ID // and s.SALES_DATE = :resultDate; //update det //set SALES_DET_STATUS = :detStatus // ,SALES_DET_PRIZE = prize.PRIZE_VALUE //from T_SALES_DET det, T_SALES s, T_RESULT res, T_RESULT_DET res_det, M_GAME g, M_GAME_PRIZE prize //where s.SALES_DATE = res.RESULT_DATE // and det.SALES_ID = s.SALES_ID // and res.RESULT_ID = res_det.RESULT_ID // and det.GAME_ID = res_det.GAME_ID // and g.GAME_ID = res_det.GAME_ID // and s.SALES_DATE = :resultDate // and res_det.RESULT_DET_NUMBER = det.SALES_DET_NUMBER // and prize.GAME_ID = g.GAME_ID // and res_det.RESULT_DET_ORDER_NO between prize.PRIZE_NO_START and prize.PRIZE_NO_END; ISQLQuery q = Session.CreateSQLQuery(sql.ToString()); //q.SetString("detStatus", Enums.EnumSalesDetStatus.Win.ToString()); q.SetDateTime("resultDate", resultDate); q.SetString("DATA_STATUS", Enums.EnumDataStatus.Updated.ToString()); q.SetString("MODIFIED_BY", ""); q.SetDateTime("MODIFIED_DATE", DateTime.Now); //set all detail sales to default q.ExecuteUpdate(); //search detail that win sql = new StringBuilder(); sql.AppendLine(@" select det.SALES_DET_ID , sum(prize.PRIZE_VALUE) as PRIZE_VALUE from T_SALES_DET det, T_SALES s, T_RESULT res, T_RESULT_DET res_det, M_GAME g, M_GAME_PRIZE prize where s.SALES_DATE = res.RESULT_DATE and det.SALES_ID = s.SALES_ID and res.RESULT_ID = res_det.RESULT_ID and det.GAME_ID = res_det.GAME_ID and g.GAME_ID = res_det.GAME_ID and s.SALES_DATE = :resultDate and res_det.RESULT_DET_NUMBER = det.SALES_DET_NUMBER and prize.GAME_ID = g.GAME_ID and res_det.RESULT_DET_ORDER_NO between prize.PRIZE_NO_START and prize.PRIZE_NO_END group by det.SALES_DET_ID; "); q = Session.CreateSQLQuery(sql.ToString()); q.SetDateTime("resultDate", resultDate); q.AddScalar("SALES_DET_ID", NHibernateUtil.String); q.AddScalar("PRIZE_VALUE", NHibernateUtil.Decimal); IList list = q.List(); object[] obj; string detailId; decimal? prize; TSalesDet det = null; for (int i = 0; i < list.Count; i++) { obj = (object[])list[i]; detailId = obj[0].ToString(); prize = Convert.ToDecimal(obj[1]); det = Session.Get(typeof(TSalesDet), detailId) as TSalesDet; if (det != null) { det.DataStatus = Enums.EnumDataStatus.Updated.ToString(); det.ModifiedBy = ""; det.ModifiedDate = DateTime.Now; det.SalesDetStatus = Enums.EnumSalesDetStatus.Win.ToString(); det.SalesDetPrize = prize; Session.Update(det); } } }