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