internal static List <ActiveApplication> GetNonAssignedApplicationWithGroup()
{
List <ActiveApplication> activeApplications = new List <ActiveApplication>();
string query = "SELECT Distinct membership.Id AS " + ColumnNames.ID +
" ,membership.Title AS " + ColumnNames.Title +
" ,membership.Date AS " + ColumnNames.Date +
" ,membership.Id AS " + ColumnNames.IdMembership +
" FROM membership INNER JOIN activeapplications ON activeapplications.IdMembership = membership.Id " +
" WHERE AsOneApplication = 1 and activeapplications.IdNameActivity = 1";
if (DataBase.ConnectToDataBase())
{
command.Connection = DataBase.Connection;
command.CommandText = query;
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
try
{
activeApplications.Add(ActiveApplication.GetActiveApplicationFromReader(reader));
}
catch (SqlException message)
{
ApplicationLog.LogService.AddRaportCatchException("Error!!!\tZapytanie nie zwróciło żadnej wartości.", message);
}
}
DataBase.CloseConnection();
reader.Dispose();
}
return(activeApplications);
}
public static ActiveApplication GetActiveApplicationFromReader(SqlDataReader reader)
{
List <string> namesField = new List <string>();
for (int i = 0; i < reader.FieldCount; i++)
{
namesField.Add(reader.GetName(i));
}
ActiveApplication activeApplication = new ActiveApplication();
if (namesField.Contains(ColumnNames.ID))
{
activeApplication.ID = Int32.Parse((reader[ColumnNames.ID]).ToString());
}
if (namesField.Contains(ColumnNames.Title))
{
activeApplication.Title = reader[ColumnNames.Title].ToString();
}
if (namesField.Contains(ColumnNames.ActivityTime))
{
activeApplication.ActivityTime = Int32.Parse(reader[ColumnNames.ActivityTime].ToString());
}
if (namesField.Contains(ColumnNames.Date))
{
activeApplication.Date = reader[ColumnNames.Date].ToString();
}
if (namesField.Contains(ColumnNames.NameActivity))
{
activeApplication.NameActivity = reader[ColumnNames.NameActivity].ToString();
}
if (namesField.Contains(ColumnNames.IdNameActivity))
{
activeApplication.IdNameActivity = (Int32.Parse((reader[ColumnNames.IdNameActivity]).ToString()));
}
if (namesField.Contains(ColumnNames.IdMembership))
{
int IdMembership = 0;
if (Int32.TryParse((reader[ColumnNames.IdMembership]).ToString(), out IdMembership))
{
activeApplication.IdMembership = IdMembership;
}
}
if (namesField.Contains(ColumnNames.IfAutoGrouping))
{
activeApplication.IfAutoGrouping = reader[ColumnNames.IfAutoGrouping].ToString().Equals("1") ? true : false;
}
return(activeApplication);
}
/// <summary>
/// Pobiera każdą pozycje tytułu aktywności.
/// </summary>
/// <param name="parameters"></param>
/// <returns></returns>
internal List <ActiveApplication> GetActiveApplication(CommandParameters parameters, bool ifGetOnlyOtherActivity = false)
{
List <ActiveApplication> activeApplication = new List <ActiveApplication>();
string query = "SELECT activeapplications.Id AS " + ColumnNames.ID +
", activeapplications.Title AS " + ColumnNames.Title +
", alldate.ActivityTime AS " + ColumnNames.ActivityTime +
", activeapplications.IdNameActivity AS " + ColumnNames.IdNameActivity +
", alldate.Date AS " + ColumnNames.Date +
" FROM alldate " +
" INNER JOIN activeapplications ON activeapplications.Id = alldate.IdTitle ";
if (!ifGetOnlyOtherActivity)
{
query += "WHERE activeapplications.Id > 2 ";
}
else
{
query += "WHERE activeapplications.Id in (1, 2) ";
}
query += CommandParameters.CheckParameters(parameters).Replace("ID", "activeapplications.ID");
if (ifGetOnlyOtherActivity)
{
query += " ORDER BY Date , ActivityTime DESC";
}
if (DataBase.ConnectToDataBase())
{
command.Connection = DataBase.Connection;
command.CommandText = query;
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
try
{
activeApplication.Add(ActiveApplication.GetActiveApplicationFromReader(reader));
}
catch (SqlException message)
{
ApplicationLog.LogService.AddRaportCatchException("Error!!!\tNie udało się pobrać ActiveApplication.", message);
}
}
DataBase.CloseConnection();
reader.Dispose();
}
return(activeApplication);
}
internal static List <ActiveApplication> GetActiveApplication(CommandParameters parameters)
{
List <ActiveApplication> activeApplications = new List <ActiveApplication>();
string query = "SELECT activeapplications.Id AS " + ColumnNames.ID +
", activeapplications.Title AS " + ColumnNames.Title +
", nameactivity.NameActivity AS " + ColumnNames.NameActivity +
", nameactivity.Id AS " + ColumnNames.IdNameActivity +
", activeapplications.IdMembership AS " + ColumnNames.IdMembership +
", activeapplications.AutoGrouping AS " + ColumnNames.IfAutoGrouping +
", membership.AsOneApplication AS " + ColumnNames.IfAsOneApplication +
" FROM activeapplications " +
" LEFT JOIN nameactivity ON activeapplications.IdNameActivity = nameactivity.Id " +
" LEFT JOIN membership ON membership.Id = activeapplications.IdMembership " +
" WHERE activeapplications.Id > 2 " + ((parameters.IfAsOneApplication == (Convert.ToInt32(false)).ToString()) ?
" AND (membership.AsOneApplication = 0 OR activeapplications.IdMembership IS NULL) " : "");
query += CommandParameters.CheckParameters(parameters);
if (DataBase.ConnectToDataBase())
{
command.Connection = DataBase.Connection;
command.CommandText = query;
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
try
{
activeApplications.Add(ActiveApplication.GetActiveApplicationFromReader(reader));
}
catch (SqlException message)
{
ApplicationLog.LogService.AddRaportCatchException("Error!!!\tZapytanie nie zwróciło żadnej wartości.", message);
}
}
DataBase.CloseConnection();
reader.Dispose();
}
return(activeApplications);
}
/// <summary>
/// Pobiera pozycji połączone w grupy.
/// </summary>
/// <param name="parameters"></param>
/// <returns></returns>
internal List <ActiveApplication> GetActiveApplicationGrouping(CommandParameters parameters)
{
List <ActiveApplication> activeApplication = new List <ActiveApplication>();
string query = "SELECT membership.Title AS " + ColumnNames.Title +
", sum(alldate.ActivityTime) AS " + ColumnNames.ActivityTime +
", alldate.Date AS " + ColumnNames.Date +
", activeapplications.Id AS " + ColumnNames.ID +
", activeapplications.IdNameActivity AS " + ColumnNames.IdNameActivity +
" FROM alldate " +
" INNER JOIN activeapplications ON activeapplications.Id = alldate.IdTitle " +
" INNER JOIN membership ON membership.Id = activeapplications.IdMembership " +
"WHERE activeapplications.Id > 2 AND AsOneApplication = 1 ";
query += CommandParameters.CheckParameters(parameters).Replace("Date", "alldate.Date").Replace("ID", "activeapplications.ID");
query += " GROUP BY membership.Title, alldate.Date, activeapplications.Id, activeapplications.IdNameActivity";
if (DataBase.ConnectToDataBase())
{
command.Connection = DataBase.Connection;
command.CommandText = query;
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
try
{
activeApplication.Add(ActiveApplication.GetActiveApplicationFromReader(reader));
}
catch (SqlException message)
{
ApplicationLog.LogService.AddRaportCatchException("Error!!!\tNie udało się pobrać zgrupowanych ActiveApplication.)", message);
}
}
DataBase.CloseConnection();
reader.Dispose();
}
return(activeApplication);
}
