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