// 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 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 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 <EmployeeRelation> GetEmployeeRelationByCountryId(long countryid)
{
string hql = @"SELECt entity FROM EmployeeRelation entity, Employee empl, Store stores
WHERE empl.id=entity.EmployeeID AND empl.MainStoreID=stores.id AND
stores.CountryID={0} order by entity.EmployeeID asc, entity.BeginTime asc";
hql = String.Format(hql, countryid);
return(GetTypedListFromIList(HibernateTemplate.FindByNamedParam(hql, new string[] { }, new object[] { })));
}
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 long GetCountryByStoreId(long storeid)
{
string query =
@"SELECT entityregion.CountryID FROM Region entityregion,Store entitystore WHERE entitystore.ID={0} AND entitystore.RegionID=entityregion.ID ";
query = String.Format(query, storeid);
IList lst = HibernateTemplate.FindByNamedParam(query, new string[0], new object[0]);
//IList lst = Session.CreateSQLQuery(query).//List();
if (lst == null || lst.Count > 1)
{
throw new Exception("Country for store not found or return grater than one");
}
return((long)lst[0]);
}
public Store[] GetStoresByCountryId(long countryid)
{
string query =
@"SELECT entitystore FROM Region entityregion, Store entitystore WHERE entityregion.CountryID={0} AND entityregion.ID=entitystore.RegionID";
query = String.Format(query, countryid);
IList lst = HibernateTemplate.FindByNamedParam(query, new string[0], new object[0]);
//IList lst = Session.CreateSQLQuery(query).//List();
Store[] result = null;
if (lst != null && lst.Count > 0)
{
result = new Store[lst.Count];
lst.CopyTo(result, 0);
}
return(result);
}
/// <summary>
/// Finds the entities by named parameter list.
/// </summary>
/// <param name="fields">Fields to select from entity (by default whole entity is selected).</param>
/// <param name="fromAddition">From clause addition (i.e. is added after "SELECT entity FROM [TypeName] entity, ").</param>
/// <param name="condition">The condition (is added after default WHERE-conditions using AND operation).</param>
/// <param name="afterJoinClauses">The after join clauses (ORDER BY, for example).</param>
/// <param name="paramNames">The parameter names.</param>
/// <param name="paramValues">The parameter values.</param>
/// <param name="bCheckForReading">Check user rights for reading or for writing entity.</param>
/// <param name="user">User to find for.</param>
/// <returns>Entity list</returns>
public IList FindByNamedParam(string[] fields, string fromAddition, string condition, string afterJoinClauses,
string[] paramNames, object[] paramValues, bool bCheckForReading, User user)
{
StringBuilder sb = new StringBuilder(50);
sb.AppendFormat("SELECT ");
if ((fields == null) || (fields.Length == 0))
{
sb.AppendFormat(" entity ");
}
else
{
int i = 0;
// at least one item is present
sb.AppendFormat(" {0}", fields[i++]);
for (; i < fields.Length; i++)
{
sb.AppendFormat(",{0}", fields[i]);
}
}
sb.AppendFormat(" FROM {0} entity ", typeof(T).Name);
if ((fromAddition != null) && (fromAddition.Length > 0))
{
sb.AppendFormat(",{0} ", fromAddition);
}
bool isWhereClauseAdded = false;
if ((condition != null) && (condition.Length > 0))
{
sb.AppendFormat(" WHERE ({0}) ", condition);
isWhereClauseAdded = true;
}
// HibernateTemplate doesn't accept nulls instead of empty arrays
List <string> pNames = new List <string>();
if (paramNames != null)
{
pNames.AddRange(paramNames);
}
List <object> pValues = new List <object>();
if (paramValues != null)
{
pValues.AddRange(paramValues);
}
string permittedIDFilter;
switch (CreatePermittedIDFilter(pNames, pValues, bCheckForReading, out permittedIDFilter, user))
{
case PermittedIDsResult.None:
return(null);
case PermittedIDsResult.All:
break;
case PermittedIDsResult.Restricted:
if ((permittedIDFilter != null) && (permittedIDFilter.Length > 0))
{
if (!isWhereClauseAdded)
{
sb.Append(" WHERE ");
isWhereClauseAdded = true;
}
else
{
sb.Append(" AND ");
}
sb.AppendFormat("(entity.ID IN ({0})) ", permittedIDFilter);
}
break;
default:
Debug.Assert(false, "Unknown PermittedIDsResult");
break;
}
if ((afterJoinClauses != null) && (afterJoinClauses.Length > 0))
{
sb.AppendFormat(" {0} ", afterJoinClauses);
}
Debug.Assert(pNames.Count == pValues.Count);
IList result = HibernateTemplate.FindByNamedParam(sb.ToString(), pNames.ToArray(), pValues.ToArray());
if ((result == null) || (result.Count == 0))
{
return(null);
}
return(result);
}
