public List <EmployeeContract> GetEmployeeContracts(long[] ids, DateTime dateBegin, DateTime dateEnd)
{
return(GetTypedListFromIList((IList)HibernateTemplate.Execute(delegate(ISession session)
{
return
session.CreateCriteria(typeof(EmployeeContract))
.Add(Expression.In("EmployeeID", ids))
.Add(Expression.Not(
Expression.Or(
Expression.Gt("ContractBegin", dateEnd),
Expression.Lt("ContractEnd", dateBegin))))
.AddOrder(Order.Asc("EmployeeID"))
.AddOrder(Order.Asc("ContractBegin"))
.List();
}
)));
}
/// <summary>
/// Execute schema update script, determined by the Configuration object
/// used for creating the SessionFactory. A replacement for NHibernate's
/// SchemaUpdate class, for automatically executing schema update scripts
/// on application startup. Can also be invoked manually.
/// </summary>
/// <remarks>
/// Fetch the LocalSessionFactoryObject itself rather than the exposed
/// SessionFactory to be able to invoke this method, e.g. via
/// <code>LocalSessionFactoryObject lsfo = (LocalSessionFactoryObject) ctx.GetObject("mySessionFactory");</code>.
/// <p>
/// Uses the SessionFactory that this bean generates for accessing a ADO.NET
/// connection to perform the script.
/// </p>
/// </remarks>
public virtual void UpdateDatabaseSchema()
{
log.Info("Updating database schema for Hibernate SessionFactory");
HibernateTemplate hibernateTemplate = new HibernateTemplate(sessionFactory);
hibernateTemplate.TemplateFlushMode = TemplateFlushMode.Never;
hibernateTemplate.Execute(
new HibernateDelegate(delegate(ISession session)
{
IDbConnection con = session.Connection;
Dialect dialect = Dialect.GetDialect(Configuration.Properties);
DatabaseMetadata metadata = new DatabaseMetadata((DbConnection)con, dialect);
string[] sql = Configuration.GenerateSchemaUpdateScript(dialect, metadata);
ExecuteSchemaScript(con, sql);
return(null);
}));
}
public User GetByLogin(string login)
{
object usr = HibernateTemplate.Execute(delegate(ISession session)
{
ICriteria crit = session.CreateCriteria(typeof(User))
.SetFetchMode("UserCountryList", FetchMode.Join)
.SetFetchMode("UserRegionList", FetchMode.Join)
.SetFetchMode("UserStoreList", FetchMode.Join)
.Add(Expression.Eq("LoginName", login));
HibernateTemplate.PrepareCriteria(crit);
return(crit.UniqueResult());
}
);
return(usr == null ? null : (User)usr);
}
public List <EmployeeAllIn> GetEntitiesByCountry(long countryid, DateTime?aBegin, DateTime?aEnd)
{
IList resultList = (IList)HibernateTemplate.Execute(delegate(ISession session)
{
string sQuery = @"SELECT _allin from EmployeeAllIn _allin, Employee empl, Store st where
st.CountryID=:cid AND
empl.MainStoreID=st.id AND _allin.EmployeeID=empl.id
AND NOT(_allin.BeginTime>:etime OR _allin.EndTime<:stime) ORDER BY _allin.EmployeeID, _allin.BeginTime
";
if (aBegin.HasValue && aEnd.HasValue)
{
return(session.CreateQuery(sQuery)
//.AddEntity("_allin", typeof(EmployeeAllIn))
.SetInt64("cid", countryid)
.SetDateTime("etime", aEnd.Value)
.SetDateTime("stime", aBegin.Value)
.List());
}
else if (aBegin.HasValue)
{
sQuery = @"SELECT _allin from EmployeeAllIn _allin, Employee empl, Store st where
st.CountryID=:cid AND
empl.MainStoreID=st.id AND _allin.EmployeeID=empl.id
AND _allin.BeginTime <= :stime AND _allin.EndTime>=:stime1 ORDER BY _allin.EmployeeID, _allin.BeginTime
";
return(session.CreateQuery(sQuery)
//.AddEntity("_allin", typeof(EmployeeAllIn))
.SetInt64("cid", countryid)
.SetDateTime("stime", aBegin.Value)
.SetDateTime("stime1", aBegin.Value)
.List());
}
sQuery = @"SELECT _allin from EmployeeAllIn _allin, Employee empl, Store st where
st.CountryID=:cid AND
empl.MainStoreID=st.id AND _allin.EmployeeID=empl.id ORDER BY _allin.EmployeeID, _allin.BeginTime
";
return(session.CreateQuery(sQuery)
//.AddEntity("_allin", typeof(EmployeeAllIn))
.SetInt64("cid", countryid)
.List());
}
);
return(GetTypedListFromIList(resultList));
}
public List <EmployeeDayStatePlanning> GetEmployeeStates(long emplid, DateTime beginDate, DateTime endDate)
{
List <EmployeeDayStatePlanning> states = new List <EmployeeDayStatePlanning>();
HibernateTemplate.Execute(delegate(ISession session)
{
session.CreateCriteria(typeof(EmployeeDayStatePlanning))
.Add(Expression.Eq("EmployeeID", emplid))
.Add(Expression.Between("Date", beginDate, endDate))
.AddOrder(Order.Asc("Date"))
.List(states);
return(null);
}
);
return(states);
}
public IList GetStoresWithEmployeeWeekTimeRecordingDelay()
{
List <StoreDelayInfo> result = new List <StoreDelayInfo>();
string query = "exec spStore_LastEmployeeWTRWarning";
HibernateTemplate.Execute(
delegate(ISession session)
{
session.CreateSQLQuery(query)
.AddEntity(typeof(StoreDelayInfo))
.List(result);
return(null);
}
);
return(result);
}
public UserGroupMembership FindMembershipsByUserAndGroup(User user, UserGroup userGroup)
{
Require.NotNull(userGroup, "userGroup");
Require.NotNull(user, "user");
HibernateDelegate <UserGroupMembership> finder = delegate(ISession session) {
IQueryOver <UserGroupMembership, UserGroupMembership> queryOver = session.QueryOver <UserGroupMembership>();
queryOver
/*Mitgliedschaft des Nutzers*/
.Where(membership => membership.User == user)
/*In der Gruppe*/
.And(membership => membership.UserGroup == userGroup);
return(queryOver.SingleOrDefault());
};
return(HibernateTemplate.Execute(finder));
}
public double GetAvgWorkingDaysInWeek(long countryID, int year)
{
double result = 0;
AvgWorkingDaysInWeek entity = (AvgWorkingDaysInWeek)HibernateTemplate.Execute(delegate(ISession session)
{
ICriteria criteria = session.CreateCriteria(typeof(AvgWorkingDaysInWeek));
return(criteria.Add(Expression.Eq("CountryID", countryID))
.Add(Expression.Eq("Year", (short)year))
.UniqueResult <AvgWorkingDaysInWeek>());
}
);
if (entity != null)
{
result = Convert.ToDouble(entity.DaysCount);
}
return(result);
}
public List <BufferHours> GetBufferHours(long storeworldID)
{
//string query = "SELECT {_buffer.*} FROM BufferHours {_buffer} where _buffer.Store_WorldID=:sw_id ORDER BY _buffer.Year";
//return (List<BufferHours>)HibernateTemplate.Execute(delegate(ISession session)
// {
// return GetTypedListFromIList(session.CreateSQLQuery(query).AddEntity("_buffer", typeof(BufferHours)).SetInt64("sw_id", storeworldID).List());
// }
//);
return((List <BufferHours>) HibernateTemplate.Execute(delegate(ISession session)
{
return GetTypedListFromIList(session.CreateCriteria(typeof(BufferHours))
.Add(Expression.Eq("StoreWorldID", storeworldID))
.AddOrder(Order.Asc("Year"))
.List());
}
));
}
public BufferHours GetBufferHours(long storeworldID, short year)
{
IList result = (IList)HibernateTemplate.Execute(delegate(ISession session)
{
return(session.CreateCriteria(typeof(BufferHours))
.Add(Expression.Eq("StoreWorldID", storeworldID))
.Add(Expression.Eq("Year", year))
.List());
}
);
if (result == null || result.Count == 0)
{
return(null);
}
return((BufferHours)result[0]);
}
public EmployeeDayStatePlanning GetEmployeesState(long emplid, DateTime date)
{
EmployeeDayStatePlanning result =
(EmployeeDayStatePlanning)HibernateTemplate.
Execute(delegate(ISession session)
{
return
(session.
CreateCriteria(
typeof(EmployeeDayStatePlanning))
.Add(Expression.Eq("EmployeeID", emplid))
.Add(Expression.Eq("Date", date))
.UniqueResult <EmployeeDayStatePlanning>());
}
);
return(result);
}
public List <EmployeeWeekTimeRecording> GetEmployeesWeekState(long[] emplids, DateTime beginDate, DateTime endDate)
{
List <EmployeeWeekTimeRecording> res = new List <EmployeeWeekTimeRecording>();
HibernateTemplate.Execute(
delegate(ISession session)
{
session.CreateCriteria(typeof(EmployeeWeekTimeRecording))
.Add(Expression.In("EmployeeID", emplids))
.Add(Expression.Eq("WeekBegin", beginDate))
.Add(Expression.Eq("WeekEnd", endDate))
.List(res);
return(null);
}
);
return(res);
}
public List <WorkingTimePlanning> GetWorkingTimeByEmployeeId(long employeeId, DateTime beginDate, DateTime endDate)
{
Debug.Assert(beginDate <= endDate);
IList result = (IList)HibernateTemplate.Execute(
delegate(ISession session)
{
return(session.CreateCriteria(typeof(WorkingTimePlanning))
.Add(Expression.Eq("EmployeeID", employeeId))
.Add(Expression.Ge("Date", beginDate))
.Add(Expression.Le("Date", endDate))
.List());
}
);
return(GetTypedListFromIList(result));;
}
public List <EmployeeWeekTimePlanning> GetEmployeeWeekStatesInFuture(long emplid, DateTime fromDate)
{
List <EmployeeWeekTimePlanning> entities = new List <EmployeeWeekTimePlanning>();
HibernateTemplate.Execute(
delegate(ISession session)
{
session.CreateCriteria(typeof(EmployeeWeekTimePlanning))
.Add(Expression.Eq("EmployeeID", emplid))
.Add(Expression.Ge("WeekBegin", fromDate))
.AddOrder(Order.Asc("WeekBegin"))
.List(entities);
return(null);
}
);
return(entities);
}
public List <EmployeeWeekTimeRecording> GetEmployeeWeekStateByDateRange(long emplid, DateTime beginDate, DateTime endDate)
{
List <EmployeeWeekTimeRecording> res = new List <EmployeeWeekTimeRecording>();
HibernateTemplate.Execute(
delegate(ISession session)
{
session.CreateCriteria(typeof(EmployeeWeekTimeRecording))
.Add(Expression.Eq("EmployeeID", emplid))
.Add(Expression.Ge("WeekBegin", beginDate))
.Add(Expression.Lt("WeekBegin", endDate))
.AddOrder(Order.Asc("WeekBegin"))
.List(res);
return(null);
}
);
return(res);
}
public EmployeeWeekTimeRecording GetLastWeek(long emplid, DateTime date)
{
EmployeeWeekTimeRecording res = null;
HibernateTemplate.Execute(
delegate(ISession session)
{
res = session.CreateCriteria(typeof(EmployeeWeekTimeRecording))
.Add(Expression.Eq("EmployeeID", emplid))
.Add(Expression.Lt("WeekBegin", date))
.AddOrder(Order.Desc("WeekBegin"))
.SetMaxResults(1)
.UniqueResult <EmployeeWeekTimeRecording>();
return(res);
}
);
return(res);
}
public List <EmployeeRecordingWorkingModel> GetWorkingModelByEmployeeId(long employeeId, DateTime beginDate, DateTime endDate)
{
Debug.Assert(beginDate <= endDate);
List <EmployeeRecordingWorkingModel> res = new List <EmployeeRecordingWorkingModel>();
HibernateTemplate.Execute(delegate(ISession session)
{
session.CreateCriteria(typeof(EmployeeRecordingWorkingModel))
.Add(Expression.Eq("EmployeeID", employeeId))
.Add(Expression.Between("Date", beginDate, endDate))
.AddOrder(Order.Asc("Date"))
.AddOrder(Order.Asc("WorkingModelID"))
.List(res);
return(null);
}
);
return(res);
}
public List <EmployeeWeekTimePlanning> LoadAllFromDateSorted(DateTime monday)
{
Debug.Assert(monday.DayOfWeek == DayOfWeek.Monday);
List <EmployeeWeekTimePlanning> entities = new List <EmployeeWeekTimePlanning>();
HibernateTemplate.Execute(
delegate(ISession session)
{
session.CreateCriteria(typeof(EmployeeWeekTimePlanning))
.Add(Expression.Ge("WeekBegin", monday))
.AddOrder(Order.Asc("EmployeeID"))
.AddOrder(Order.Asc("WeekBegin"))
.List(entities);
return(null);
}
);
return(entities);
}
public long[] GetStoreEmptyOpenCloseTimeList(long userID)
{
string query =
@"exec spStoreIDListEmptyOpenCloseTime :userID";
List <long> result = new List <long>();
HibernateTemplate.Execute(
delegate(ISession session)
{
session.CreateSQLQuery(query)
.AddScalar("StoreID", NHibernateUtil.Int64)
.SetParameter("userID", userID)
.List(result);
return(null);
}
);
return(result.ToArray());
}
public IList GetStoreStructure(long storeID)
{
List <StoreStructure> storeStructureList = new List <StoreStructure>();
string query = "exec spStoreStructureGet :storeId, :date";
HibernateTemplate.Execute(
delegate(ISession session)
{
session.CreateSQLQuery(query)
.AddEntity(typeof(StoreStructure))
.SetParameter("storeId", storeID)
.SetParameter("date", DateTime.Now.ToString("yyyMMdd"))
.List(storeStructureList);
return(null);
}
);
return(storeStructureList);
}
public IPage <Peanut> FindAttendablePeanutsForUser(IPageable pageRequest, User user, DateTime from, DateTime to)
{
Require.NotNull(user, "user");
Require.NotNull(pageRequest, "pageRequest");
HibernateDelegate <IPage <Peanut> > finder = delegate(ISession session) {
IQueryOver <Peanut, Peanut> queryOver = session.QueryOver <Peanut>();
/*Die aktiven Gruppen des Nutzers*/
IList <UserGroup> userGroups =
session.QueryOver <UserGroupMembership>()
.Where(mem => mem.User == user)
.And(
mem =>
mem.MembershipType == UserGroupMembershipType.Administrator ||
mem.MembershipType == UserGroupMembershipType.Member).Select(mem => mem.UserGroup)
.List <UserGroup>();
IList <Peanut> attendedPeanuts =
session.QueryOver <PeanutParticipation>()
.Where(part => part.ParticipationState == PeanutParticipationState.Confirmed)
.JoinQueryOver(part => part.UserGroupMembership).Where(mem => mem.User == user)
.Select(part => part.Peanut)
.List <Peanut>();
queryOver
/*An bereits fixierten Tickets, kann sich kein neuer Teilnehmer anmelden.*/
.Where(peanut => peanut.PeanutState == PeanutState.Scheduling)
/*Am oder nach dem ab Datum*/
.And(peanut => peanut.Day >= from)
/*Nicht nach dem To Datum*/
.AndNot(peanut => peanut.Day > to)
/*Nur Peanuts aus den Gruppen des Nutzers*/
.WhereRestrictionOn(peanut => peanut.UserGroup).IsIn(userGroups.ToList())
/*Keine Peanuts an denen der Nutzer teilnimmt*/
.WhereRestrictionOn(peanut => peanut.Id).Not.IsIn(attendedPeanuts.Select(p => p.Id).ToList());
return(FindPage(queryOver, pageRequest));
};
return(HibernateTemplate.Execute(finder));
}
public int ExecuteUpdate(string sql, IDictionary <string, object> parameters)
{
return(HibernateTemplate.Execute(session =>
{
// Create SQL Query
var query = session.CreateSQLQuery(sql);
// Parameters
if (parameters != null && parameters.Count != 0)
{
foreach (var parameter in parameters)
{
query.SetParameter(parameter.Key, parameter.Value);
}
}
HibernateTemplate.PrepareQuery(query);
return query.ExecuteUpdate();
}));
}
public IPage <UserGroupMembership> FindMembershipsByUser(IPageable pageRequest, User user,
IList <UserGroupMembershipType> membershipTypes = null)
{
Require.NotNull(pageRequest, "pageRequest");
Require.NotNull(user, "user");
HibernateDelegate <IPage <UserGroupMembership> > finder = delegate(ISession session) {
IQueryOver <UserGroupMembership, UserGroupMembership> queryOver = session.QueryOver <UserGroupMembership>();
queryOver.Where(membership => membership.User == user);
if (membershipTypes != null && membershipTypes.Any())
{
queryOver.WhereRestrictionOn(memberShip => memberShip.MembershipType).IsIn(membershipTypes.ToArray());
}
return(FindPage(queryOver, pageRequest));
};
return(HibernateTemplate.Execute(finder));
}
/// <summary>
/// Liefert eine Liste mit Entities entsprechend der Pageinformationen
/// aus der Menge aller Entitäten.
/// </summary>
/// <param name="pageable">Beschreibung welche Menge der Entitäten zurückgeliefert werden.</param>
/// <returns>Liste mit Entitäten.</returns>
public virtual IPage <T> GetAll(IPageable pageable)
{
Require.NotNull(pageable, "pageable");
HibernateDelegate <IPage <T> > finder = delegate(ISession session) {
ICriteria elementsCriteria = session.CreateCriteria(typeof(T));
elementsCriteria.AddOrder(Order.Asc("Id"));
ApplyPaging(pageable, elementsCriteria);
ICriteria countCriteria = session.CreateCriteria(typeof(T));
countCriteria.SetProjection(Projections.RowCountInt64());
IFutureValue <long> futureTotalCount = countCriteria.FutureValue <long>();
IEnumerable <T> futureElements = elementsCriteria.Future <T>();
Page <T> page = new Page <T>(futureElements.ToList(), pageable, futureTotalCount.Value);
return(page);
};
return(HibernateTemplate.Execute(finder));
}
public List <EmployeeDayStateRecording> GetEmployeesStates(long[] emplids, DateTime beginDate, DateTime endDate)
{
List <EmployeeDayStateRecording> states = new List <EmployeeDayStateRecording>();
if (emplids != null && emplids.Length > 0)
{
HibernateTemplate.Execute(delegate(ISession session)
{
session.CreateCriteria(typeof(EmployeeDayStateRecording))
.Add(Expression.In("EmployeeID", emplids))
.Add(Expression.Between("Date", beginDate, endDate))
.AddOrder(Order.Asc("EmployeeID"))
.List(states);
return(null);
}
);
}
return(states);
}
public EmployeeWeekTimeRecording GetRecordingWeek(long employeeid, DateTime weekDate)
{
return((EmployeeWeekTimeRecording)HibernateTemplate.Execute(
delegate(ISession session)
{
return session.CreateCriteria(typeof(EmployeeWeekTimeRecording))
.Add(Expression.Eq("EmployeeID", employeeid))
.Add(Expression.Eq("WeekBegin", weekDate))
.UniqueResult <EmployeeWeekTimeRecording>();
}
));
//return res;
//return Session.CreateCriteria(typeof(EmployeeWeekTimeRecording))
//.Add(Expression.Eq("EmployeeID", employeeid))
//.Add(Expression.Eq("WeekBegin", weekDate))
//.UniqueResult<EmployeeWeekTimeRecording>();
}
public List <AbsenceTimeRecording> GetAbsenceTimeByEmployeeId(long employeeId, DateTime beginDate, DateTime endDate)
{
Debug.Assert(beginDate <= endDate);
IList result = (IList)HibernateTemplate.Execute(
delegate(ISession session)
{
return
(session.CreateCriteria(typeof(AbsenceTimeRecording))
.Add(Expression.Eq("EmployeeID", employeeId))
.Add(Expression.Ge("Date", beginDate))
.Add(Expression.Le("Date", endDate))
.List());
}
);
if ((result == null) || (result.Count == 0))
{
return(null);
}
return(GetTypedListFromIList(result));
}
public List <EmployeeRelation> GetEmployeeRelationsByStoreAndWorld(long storeid, long worldid, DateTime?aBegin, DateTime?aEnd)
{
IList result =
(IList)HibernateTemplate
.Execute(delegate(ISession session)
{
ICriteria criteria =
session.CreateCriteria(typeof(EmployeeRelation));
criteria.Add(Expression.Eq("StoreID", storeid));
if (worldid > 0)
{
criteria.Add(Expression.Eq("WorldID", worldid));
}
if (aEnd.HasValue && aBegin.HasValue)
{
criteria.Add(Expression.Not(
Expression.Or(
Expression.Gt("BeginTime", aEnd),
Expression.Lt("EndTime", aBegin))));
}
else
{
if (aBegin.HasValue)
{
criteria
.Add(Expression.Le("BeginTime", aBegin))
.Add(Expression.Ge("EndTime", aBegin));
}
}
criteria.AddOrder(Order.Asc("EmployeeID")).AddOrder(Order.Asc("BeginTime"));
return(criteria.List());
}
);
return(GetTypedListFromIList(result));
}
public List <EmployeeRelation> GetEmployeeRelations(long storeid, DateTime aBegin, DateTime aEnd)
{
IList result =
(IList)HibernateTemplate
.Execute(delegate(ISession session)
{
ICriteria criteria =
session.CreateCriteria(typeof(EmployeeRelation));
criteria.Add(Expression.Eq("StoreID", storeid));
criteria.Add(Expression.Not(
Expression.Or(
Expression.Gt("BeginTime", aEnd),
Expression.Lt("EndTime", aBegin))));
criteria.AddOrder(Order.Asc("EmployeeID")).AddOrder(Order.Asc("BeginTime"));
return(criteria.List());
}
);
return(GetTypedListFromIList(result));
}
public long[] GetCountryNoWorkingFeastDaysIDList()
{
List <long> result = new List <long>();
HibernateTemplate.Execute(
delegate(ISession session)
{
string query =
@"select CountryID from Country c
where
not exists (select * from dbo.YearlyWorkingDays w where c.CountryID = w.CountryID)
and not exists (select * from dbo.Feasts f where c.CountryID = f.CountryID)" ;
session.CreateSQLQuery(query)
.AddScalar("CountryID", NHibernateUtil.Int64)
.List(result);
return(null);
}
);
return(result.ToArray());
}
