public List <EmployeeAllIn> GetEntitiesByStoreAndRelation(long storeid, DateTime?aBegin, DateTime?aEnd) { IList resultList = (IList)HibernateTemplate.Execute(delegate(ISession session) { string hql = string.Empty; if (aBegin.HasValue && aEnd.HasValue) { string sQuery = @"SELECT _allin from EmployeeAllIn _allin, EmployeeRelation rel where rel.StoreID={0} AND NOT(rel.BeginTime>'{2}' OR rel.EndTime<'{1}') AND _allin.EmployeeID=rel.EmployeeID AND NOT(_allin.BeginTime>'{2}' OR _allin.EndTime<'{1}') ORDER BY _allin.EmployeeID, _allin.BeginTime "; hql = string.Format(sQuery, storeid, DateTimeSql.DateToSqlString(aBegin.Value), DateTimeSql.DateToSqlString(aEnd.Value)); } else if (aBegin.HasValue) { string sQuery = @"SELECT _allin from EmployeeAllIn _allin, EmployeeRelation rel where rel.StoreID={0} AND rel.BeginTime<='{1}' AND '{1}' <= rel.EndTime AND _allin.EmployeeID=rel.EmployeeID AND _allin.BeginTime<='{1}' AND '{1}' <= _allin.EndTime ORDER BY _allin.EmployeeID, _allin.BeginTime "; hql = string.Format(sQuery, storeid, DateTimeSql.DateToSqlString(aBegin.Value)); } else { string sQuery = @"SELECT _allin from EmployeeAllIn _allin, Employee empl where empl.MainStoreID={0} AND empl.id = _allin.EmployeeID ORDER BY _allin.EmployeeID, _allin.BeginTime "; hql = string.Format(sQuery, storeid); } return(session.CreateQuery(hql).List()); } ); return(GetTypedListFromIList(resultList)); }
public BaumaxStoreDaysManager(long countryid, DateTime fromDate, DateTime toDate) { FromDate = DateTimeSql.CheckSmallMinMax(fromDate); ToDate = DateTimeSql.CheckSmallMinMax(toDate); LoadCountry(countryid); }
// return all relation of employees where Employee.MainStoreID=storeid and found into begin and end date-range // need for absence planning public List <EmployeeRelation> GetEmployeeRelationByMainStore(long storeid, DateTime begin, DateTime end) { Debug.Assert(storeid > 0); Debug.Assert(DateTimeHelper.Between(begin, DateTimeSql.SmallDatetimeMin, DateTimeSql.SmallDatetimeMax)); Debug.Assert(DateTimeHelper.Between(end, DateTimeSql.SmallDatetimeMin, DateTimeSql.SmallDatetimeMax)); begin = DateTimeSql.CheckSmallMinMax(begin); end = DateTimeSql.CheckSmallMinMax(end); string template_hql = @"select relation from EmployeeRelation relation, Employee empl where empl.MainStoreID={0} and empl.id=relation.EmployeeID and NOT((relation.BeginTime>'{2}') OR (relation.EndTime<'{1}')) order by relation.EmployeeID asc, relation.BeginTime asc"; string hql = string.Format(template_hql, storeid, DateTimeSql.DateToSqlString(begin), DateTimeSql.DateToSqlString(end)); IList list = HibernateTemplate.FindByNamedParam(hql, new string[] { }, new object[] { }); if (list == null || list.Count == 0) { return(null); } return(GetTypedListFromIList(list)); }
public List <EmployeeContract> GetEmployeeContractsByStore(long storeid, DateTime begin, DateTime end) { List <EmployeeContract> lst = null; HibernateTemplate.Execute(delegate(ISession session) { string sQuery = @"select contracts from EmployeeContract contracts, Employee empl where contracts.EmployeeID=empl.id AND empl.MainStoreID = {0} AND NOT(contracts.ContractBegin > '{2}' OR contracts.ContractEnd < '{1}') order by contracts.EmployeeID asc, contracts.ContractBegin asc"; string hql = string.Format(sQuery, storeid, DateTimeSql.DateToSqlString(begin), DateTimeSql.DateToSqlString(end)); IList list = session.CreateQuery(hql).List(); lst = GetTypedListFromIList(list); return(null); } ); return(lst); }
// like GetWorkingHoursByWorlds but throuh store procedure private int?[,] EmployeeDayStateGetHoursSum(DateTime begin, DateTime end, long storeID, EmployeeDayStateTableType employeeDayStateTableType) { string query = string.Format("exec spEmployeeDayStateGetHoursSum '{0}', '{1}', {2}, {3}" , DateTimeSql.DateToSqlString(begin) , DateTimeSql.DateToSqlString(end) , storeID , (byte)employeeDayStateTableType); using (IDataReader reader = GetDataReader(query)) { const int valuesCount = 2; int?[,] result = new int?[1, valuesCount]; if (reader.Read()) { for (int i = 0; i < valuesCount; i++) { if (reader.IsDBNull(i)) { result[0, i] = null; } else { result[0, i] = reader.GetInt32(i); } } } return(result); } }
public List <EmployeeContract> GetEmployeeContractsByCountry(long countryid, DateTime begin, DateTime end) { List <EmployeeContract> lst = new List <EmployeeContract>(1000); HibernateTemplate.Execute(delegate(ISession session) { string sQuery = @"select contracts from EmployeeContract contracts, Employee empl, Store st where contracts.EmployeeID=empl.id AND empl.MainStoreID = st.id AND st.CountryID={0} AND NOT(contracts.ContractBegin>'{2}' OR contracts.ContractEnd<'{1}') order by contracts.EmployeeID asc, contracts.ContractBegin asc"; string hql = string.Format(sQuery, countryid, DateTimeSql.DateToSqlString(begin), DateTimeSql.DateToSqlString(end)); session.CreateQuery(sQuery).List(lst); //IList<EmployeeContract> list = session.CreateQuery(sQuery) // .SetInt64("countryid", countryid) // .List<EmployeeContract>(); //if (list != null) //{ // lst.AddRange(list); //} return(null); } ); return(lst); }
public bool BusinessVolumeActualDataExists(DateTime date, long storeID, long worldID) { string query; if (worldID == SharedConsts.CalculateAll) { query = string.Format("exec spBV_ActualDataExists '{0}', {1}" , DateTimeSql.DateToSqlString(date), storeID); } else { query = string.Format("exec spBV_ActualDataExists '{0}', {1}, {2}" , DateTimeSql.DateToSqlString(date), storeID, worldID); } bool result = (bool)HibernateTemplate.Execute(delegate(ISession session) { return(session.CreateSQLQuery(query) .AddScalar("result", NHibernateUtil.Boolean) .UniqueResult <bool>()); } ); return(result); }
public List <AbsenceTimeRecording> GetCountryHolidayTimeRecordingsBetweenDate(long countryid, DateTime beginDate, DateTime endDate) { Debug.Assert(beginDate <= endDate); string squery = @"select _absences from AbsenceTimeRecording _absences, Store st, Employee empl, Absence absn WHERE _absences.Date BETWEEN '{1}' AND '{2}' AND _absences.EmployeeID=empl.id AND _absences.AbsenceID=absn.id AND absn.AbsenceTypeID=2 AND empl.MainStoreID=st.id AND st.CountryID={0} ORDER BY _absences.EmployeeID, _absences.Date";// + countyid); string sql = String.Format(squery, countryid, DateTimeSql.DateToSqlString(beginDate), DateTimeSql.DateToSqlString(endDate)); List <AbsenceTimeRecording> result = (List <AbsenceTimeRecording>) HibernateTemplate.Execute( delegate(ISession session) { IQuery query = Session.CreateQuery(sql); IList lst = query.List(); return(GetTypedListFromIList(lst)); } ); return(result); }
public CountryCloseDaysListEx(long countryid, DateTime from, DateTime to) { CountryId = countryid; FromDate = DateTimeSql.CheckSmallMinMax(from); ToDate = DateTimeSql.CheckSmallMinMax(to); DoLoad(CountryId, FromDate, ToDate); }
public List <long> GetStoresWhereWorkedEmployee(long employeeid, DateTime begin, DateTime end) { string hql = @"select distinct st.id from Store st, EmployeeDayStatePlanning days, StoreToWorld sw where sw.StoreID= st.id AND days.StoreWorldId=sw.id and (days.Date BETWEEN '{1}' AND '{2}') and days.EmployeeID={0}"; string hql2 = @"select distinct st.id from Store st, EmployeeDayStateRecording days, StoreToWorld sw where sw.StoreID= st.id AND days.StoreWorldId=sw.id and (days.Date BETWEEN '{1}' AND '{2}') and days.EmployeeID={0}"; string query = String.Format(hql, employeeid, DateTimeSql.DateToSqlString(begin), DateTimeSql.DateToSqlString(end)); IList objects = HibernateTemplate.FindByNamedParam(query, new string[] { }, new object[] { }); string query2 = String.Format(hql2, employeeid, DateTimeSql.DateToSqlString(begin), DateTimeSql.DateToSqlString(end)); IList objects2 = HibernateTemplate.FindByNamedParam(query2, new string[] { }, new object[] { }); Dictionary <long, object> diction = new Dictionary <long, object>(); if (objects != null) { foreach (long id in objects) { diction[id] = null; } } if (objects2 != null) { foreach (long id in objects2) { diction[id] = null; } } if (diction.Count == 0) { return(null); } List <long> ids = new List <long>(diction.Keys); return(ids); }
protected void DoLoad(long countryid, DateTime from, DateTime to) { if (CountryId <= 0) { return; } DateTime from1 = DateTimeSql.CheckSmallMinMax(from); DateTime to1 = DateTimeSql.CheckSmallMinMax(to); BuildDiction(Service.GetYearlyWorkingDaysFiltered(countryid, from1, to1)); }
public List <AbsenceTimePlanning> GetEntitiesByStoreRelation(long storeid, DateTime begin, DateTime end) { string query = @"select entity from AbsenceTimePlanning entity, EmployeeRelation relations where entity.Date between '{1}' and '{2}' and entity.EmployeeID=relations.EmployeeID and relations.StoreID={0} and NOT((relations.BeginTime>'{2}') OR (relations.EndTime<'{1}'))"; string hql = string.Format(query, storeid, DateTimeSql.DateToSqlString(begin), DateTimeSql.DateToSqlString(end)); return(GetTypedListFromIList(HibernateTemplate.FindByNamedParam(hql, new string[] { }, new object[] { }))); }
public List <EmployeeContract> GetEmployeeContractsByRelationStore(long storeid, DateTime begin, DateTime end) { string hql_template = @"select contracts from EmployeeContract contracts, EmployeeRelation relations where contracts.EmployeeID=relations.EmployeeID and relations.StoreID={0} and NOT(contracts.ContractBegin > '{2}' OR contracts.ContractEnd < '{1}') and NOT((relations.BeginTime > '{2}') OR (relations.EndTime < '{1}')) order by contracts.EmployeeID asc, contracts.ContractBegin asc"; string hql = string.Format(hql_template, storeid, DateTimeSql.DateToSqlString(begin), DateTimeSql.DateToSqlString(end)); IList list = HibernateTemplate.FindByNamedParam(hql, new string[] { }, new object[] { }); return(GetTypedListFromIList(list)); }
public BaumaxStoreDaysManager(DateTime fromDate, DateTime toDate) { FromDate = DateTimeSql.CheckSmallMinMax(fromDate); ToDate = DateTimeSql.CheckSmallMinMax(toDate); }
public BaumaxFeasts(DateTime from) { FromDate = DateTimeSql.CheckSmallMinMax(from); }
public BaumaxCloseDays(DateTime from) { FromDate = DateTimeSql.CheckSmallMinMax(from); }
public int[] GetWorldWorkingHoursForYearAsWeeksSum(long storeworldid, int year, bool bPlanned) { DateTime begin = DateTimeHelper.GetBeginYearDate(year); DateTime end = DateTimeHelper.GetEndYearDate(year); int week_count = DateTimeHelper.GetCountWeekInYear(year); string query = @"select entity.Date ,sum(entity.WorkingHours) from {0} entity where entity.StoreWorldId = {1} and entity.Date between '{2}' and '{3}' group by entity.Date"; string sql = null; if (bPlanned) { sql = String.Format(query, new object[] { "EmployeeDayStatePlanning", storeworldid, DateTimeSql.DateToSqlString(begin), DateTimeSql.DateToSqlString(end) }); } else { sql = String.Format(query, new object[] { "EmployeeDayStateRecording", storeworldid, DateTimeSql.DateToSqlString(begin), DateTimeSql.DateToSqlString(end) }); } Dictionary <DateTime, int> resultDiction = new Dictionary <DateTime, int>(400); IList list = HibernateTemplate.FindByNamedParam(sql, new string[] { }, new object[] { }); if (list != null) { DateTime date; int minutes; foreach (object[] objs in list) { date = Convert.ToDateTime(objs[0]); minutes = Convert.ToInt32(objs[1]); resultDiction[date] = minutes; } } int[] sums = new int[week_count]; for (int i = 0; i < week_count; i++) { sums[i] = 0; } int weeknumber = 0; foreach (KeyValuePair <DateTime, int> keypair in resultDiction) { weeknumber = DateTimeHelper.GetWeekNumber(keypair.Key); if (weeknumber > 0 && weeknumber <= week_count) { sums[weeknumber - 1] += keypair.Value; } } return(sums); }
public Dictionary <long, int?> GetWorkingHoursByWorlds(long storeid, DateTime monday, bool bPlanned) { Debug.Assert(monday.DayOfWeek == DayOfWeek.Monday); string query = @"select entity.StoreWorldId ,sum(entity.WorkingHours) from {0} entity, StoreToWorld sw where entity.StoreWorldId = sw.id and sw.StoreID = {1} and entity.Date between '{2}' and '{3}' group by entity.StoreWorldId"; string sql = null; DateTime sunday = DateTimeHelper.GetSunday(monday); if (bPlanned) { sql = String.Format(query, new object[] { "EmployeeDayStatePlanning", storeid, DateTimeSql.DateToSqlString(monday), DateTimeSql.DateToSqlString(sunday) }); } else { sql = String.Format(query, new object[] { "EmployeeDayStateRecording", storeid, DateTimeSql.DateToSqlString(monday), DateTimeSql.DateToSqlString(sunday) }); } Dictionary <long, int?> resultDiction = new Dictionary <long, int?>(); HibernateTemplate.Execute(delegate(ISession session) { IList list = session.CreateQuery(sql).List(); if (list != null) { foreach (object[] objs in list) { long id = Convert.ToInt64(objs[0]); resultDiction[id] = null; if (objs[1] != null) { resultDiction[id] = Convert.ToInt32(objs[1]); } //(reader.IsDBNull(1)) ? null : (int?)reader.GetInt32(1); } } return(null); } ); return(resultDiction); }