private string getMaxGrupa() { string result = "Grupa"; try { using (ISession session = NHibernateHelper.Instance.OpenSession()) using (session.BeginTransaction()) { CurrentSessionContext.Bind(session); GrupaDAO grupaDAO = DAOFactoryFactory.DAOFactory.GetGrupaDAO(); IList<Grupa> grupe = grupaDAO.FindAll(); foreach (Grupa g in grupe) { if (g.Naziv.Length > result.Length) { result = g.Naziv; } } } } catch (Exception) { } finally { CurrentSessionContext.Unbind(NHibernateHelper.Instance.SessionFactory); } return result; }
protected override List <object> loadEntities() { GrupaDAO grupaDAO = DAOFactoryFactory.DAOFactory.GetGrupaDAO(); return(new List <Grupa>(grupaDAO.FindAll()).ConvertAll <object>( delegate(Grupa g) { return g; })); }
public virtual List <object[]> getNeplacenDolazakNaTrening(DateTime from, DateTime to) { try { string dolasciQuery = @" SELECT DISTINCT d.datum_vreme_dolaska, c.clan_id, c.ne_placa_clanarinu, g.grupa_id FROM clanovi c INNER JOIN (dolazak_na_trening d LEFT OUTER JOIN grupe g ON d.grupa_id = g.grupa_id) ON c.clan_id = d.clan_id WHERE (d.datum_vreme_dolaska BETWEEN '{0}' AND '{1}')"; dolasciQuery = String.Format(dolasciQuery, from.ToString("yyyy-MM-dd HH:mm:ss"), to.ToString("yyyy-MM-dd HH:mm:ss")); IList <object[]> dolasci = Session.CreateSQLQuery(dolasciQuery).List <object[]>(); string uplateQuery = @" SELECT DISTINCT datepart(year, u.vazi_od) god, datepart(month, u.vazi_od) mes, u.clan_id FROM uplate u WHERE (u.vazi_od BETWEEN '{0}' AND '{1}')"; uplateQuery = String.Format(uplateQuery, from.ToString("yyyy-MM-dd HH:mm:ss"), to.ToString("yyyy-MM-dd HH:mm:ss")); IList <object[]> uplate = Session.CreateSQLQuery(uplateQuery).List <object[]>(); ISet uplateSet = new HashedSet(); foreach (object[] row in uplate) { int god = (int)row[0]; int mes = (int)row[1]; int id = (int)row[2]; uplateSet.Add(new ClanGodinaMesec(id, god, mes)); } DateTime firstDateTimeInYear = new DateTime(from.Year, 1, 1, 0, 0, 0); DateTime lastDateTimeInYear = new DateTime(to.AddYears(1).Year, 1, 1, 0, 0, 0).AddSeconds(-1); List <Grupa> godisnjaClanarinaGrupe = new List <Grupa>(); GrupaDAO grupaDAO = DAOFactoryFactory.DAOFactory.GetGrupaDAO(); IList <Grupa> grupe = grupaDAO.FindAll(); foreach (Grupa g in grupe) { if (g.ImaGodisnjuClanarinu) { godisnjaClanarinaGrupe.Add(g); } } if (godisnjaClanarinaGrupe.Count == 0) { // TODO3: Da li je ovo potrebno? MessageDialogs.showMessage("Ne mogu da pronadjem grupu za godisnju clanarinu", "Greska"); } string uplateGodisnjaClanarinaQuery = @" SELECT DISTINCT datepart(year, u.vazi_od) god, u.clan_id FROM uplate u WHERE (u.vazi_od BETWEEN '{0}' AND '{1}') {2}"; string filter = String.Empty; if (godisnjaClanarinaGrupe.Count > 0) { filter = " AND " + Util.getGrupeFilter(godisnjaClanarinaGrupe, "u", "grupa_id"); } uplateGodisnjaClanarinaQuery = String.Format(uplateGodisnjaClanarinaQuery, firstDateTimeInYear.ToString("yyyy-MM-dd HH:mm:ss"), lastDateTimeInYear.ToString("yyyy-MM-dd HH:mm:ss"), filter); IList <object[]> uplateGodisnjaClanarina = Session.CreateSQLQuery(uplateGodisnjaClanarinaQuery).List <object[]>(); ISet godisnjeUplateSet = new HashedSet(); foreach (object[] row in uplateGodisnjaClanarina) { int god = (int)row[0]; int id = (int)row[1]; godisnjeUplateSet.Add(new ClanGodinaMesec(id, god, 1)); } List <object[]> result = new List <object[]>(); foreach (object[] row in dolasci) { DateTime datum_vreme_dolaska = (DateTime)row[0]; int clan_id = (int)row[1]; bool neplacaClanarinu = (bool)row[2]; object grupa_id = row[3]; bool imaUplatu = uplateSet.Contains( new ClanGodinaMesec(clan_id, datum_vreme_dolaska.Year, datum_vreme_dolaska.Month)); if (!imaUplatu) { imaUplatu = godisnjeUplateSet.Contains(new ClanGodinaMesec(clan_id, datum_vreme_dolaska.Year, 1)); } if (!imaUplatu) { imaUplatu = neplacaClanarinu; } if (imaUplatu) { continue; } object[] item = new object[] { datum_vreme_dolaska, clan_id, grupa_id }; result.Add(item); } return(result); } catch (HibernateException ex) { string message = String.Format( "{0} \n\n{1}", Strings.DatabaseAccessExceptionMessage, ex.Message); throw new InfrastructureException(message, ex); } }
public virtual List <object[]> getDolazakNaTreningMesecniReportItems(DateTime from, DateTime to, bool samoNedostajuceUplate) { try { string dolasciQuery = @" SELECT DISTINCT datepart(year, d.datum_vreme_dolaska) god, datepart(month, d.datum_vreme_dolaska) mes, datepart(day, d.datum_vreme_dolaska) dan, c.clan_id, c.broj, c.ime, c.prezime, c.datum_rodjenja, c.ne_placa_clanarinu, g.naziv FROM clanovi c INNER JOIN (dolazak_na_trening d LEFT OUTER JOIN grupe g ON d.grupa_id = g.grupa_id) ON c.clan_id = d.clan_id WHERE (d.datum_vreme_dolaska BETWEEN '{0}' AND '{1}') ORDER BY god, mes, g.naziv, c.prezime, c.ime, c.datum_rodjenja, dan"; dolasciQuery = String.Format(dolasciQuery, from.ToString("yyyy-MM-dd HH:mm:ss"), to.ToString("yyyy-MM-dd HH:mm:ss")); IList <object[]> dolasci = Session.CreateSQLQuery(dolasciQuery).List <object[]>(); string uplateQuery = @" SELECT DISTINCT datepart(year, u.vazi_od) god, datepart(month, u.vazi_od) mes, u.clan_id FROM uplate u WHERE (u.vazi_od BETWEEN '{0}' AND '{1}')"; uplateQuery = String.Format(uplateQuery, from.ToString("yyyy-MM-dd HH:mm:ss"), to.ToString("yyyy-MM-dd HH:mm:ss")); IList <object[]> uplate = Session.CreateSQLQuery(uplateQuery).List <object[]>(); ISet uplateSet = new HashedSet(); foreach (object[] row in uplate) { int god = (int)row[0]; int mes = (int)row[1]; int id = (int)row[2]; uplateSet.Add(new ClanGodinaMesec(id, god, mes)); } DateTime firstDateTimeInYear = new DateTime(from.Year, 1, 1, 0, 0, 0); DateTime lastDateTimeInYear = new DateTime(to.AddYears(1).Year, 1, 1, 0, 0, 0).AddSeconds(-1); List <Grupa> godisnjaClanarinaGrupe = new List <Grupa>(); GrupaDAO grupaDAO = DAOFactoryFactory.DAOFactory.GetGrupaDAO(); IList <Grupa> grupe = grupaDAO.FindAll(); foreach (Grupa g in grupe) { if (g.ImaGodisnjuClanarinu) { godisnjaClanarinaGrupe.Add(g); } } if (godisnjaClanarinaGrupe.Count == 0) { // TODO3: Da li je ovo potrebno? MessageDialogs.showMessage("Ne mogu da pronadjem grupu za godisnju clanarinu", "Greska"); } string uplateGodisnjaClanarinaQuery = @" SELECT DISTINCT datepart(year, u.vazi_od) god, u.clan_id FROM uplate u WHERE (u.vazi_od BETWEEN '{0}' AND '{1}') {2}"; string filter = String.Empty; if (godisnjaClanarinaGrupe.Count > 0) { filter = " AND " + Util.getGrupeFilter(godisnjaClanarinaGrupe, "u", "grupa_id"); } uplateGodisnjaClanarinaQuery = String.Format(uplateGodisnjaClanarinaQuery, firstDateTimeInYear.ToString("yyyy-MM-dd HH:mm:ss"), lastDateTimeInYear.ToString("yyyy-MM-dd HH:mm:ss"), filter); IList <object[]> uplateGodisnjaClanarina = Session.CreateSQLQuery(uplateGodisnjaClanarinaQuery).List <object[]>(); ISet godisnjeUplateSet = new HashedSet(); foreach (object[] row in uplateGodisnjaClanarina) { int god = (int)row[0]; int id = (int)row[1]; godisnjeUplateSet.Add(new ClanGodinaMesec(id, god, 1)); } List <object[]> result = new List <object[]>(); int prev_id = -1; object[] prevItem = null; int brojDana = 0; foreach (object[] row in dolasci) { int god = (int)row[0]; int mes = (int)row[1]; int id = (int)row[3]; bool neplacaClanarinu = (bool)row[8]; bool imaUplatu = uplateSet.Contains(new ClanGodinaMesec(id, god, mes)); if (!imaUplatu) { imaUplatu = godisnjeUplateSet.Contains(new ClanGodinaMesec(id, god, 1)); } if (!imaUplatu) { imaUplatu = neplacaClanarinu; } if (samoNedostajuceUplate && imaUplatu) { continue; } int broj = (int)row[4]; string ime = (string)row[5]; string prezime = (string)row[6]; Nullable <DateTime> datumRodjenja = null; if (row[7] != null) { datumRodjenja = (DateTime)row[7]; } string nazivGrupe = String.Empty; if (row[9] != null) { nazivGrupe = (string)row[9]; } string imaUplatuStr = "NE"; if (imaUplatu) { imaUplatuStr = ""; } string clan = Clan.formatPrezimeImeBrojDatumRodjAdresaMesto( prezime, ime, broj, datumRodjenja, String.Empty, String.Empty); object[] item = new object[] { clan, nazivGrupe, imaUplatuStr, god, mes }; if (prevItem != null && god == (int)prevItem[3] && mes == (int)prevItem[4] && id == prev_id && nazivGrupe == (string)prevItem[1]) { ++brojDana; } else { if (prevItem != null) { if ((string)prevItem[2] == "NE") { prevItem[2] = formatBrojTreninga(brojDana, samoNedostajuceUplate); } result.Add(prevItem); } brojDana = 1; prev_id = id; prevItem = item; } } // Add last item if (prevItem != null) { if ((string)prevItem[2] == "NE") { prevItem[2] = formatBrojTreninga(brojDana, samoNedostajuceUplate); } result.Add(prevItem); } return(result); } catch (HibernateException ex) { string message = String.Format( "{0} \n\n{1}", Strings.DatabaseAccessExceptionMessage, ex.Message); throw new InfrastructureException(message, ex); } }