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