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