/// <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);
}
public static List <Membership> GetAllGroups(CommandParameters parameters)
{
List <Membership> allGroups = new List <Membership>();
string query = "SELECT membership.Id AS " + ColumnNames.ID +
", membership.Title AS " + ColumnNames.Title +
", membership.Date AS " + ColumnNames.Date +
", membership.Active AS " + ColumnNames.IfActive +
", membership.Configuration AS " + ColumnNames.IfConfiguration +
", membership.Filter AS " + ColumnNames.Filter +
", membership.ActiveConfiguration AS " + ColumnNames.IfActiveConfiguration +
", membership.AsOneApplication AS " + ColumnNames.IfAsOneApplication +
" FROM membership WHERE 1 = 1 ";
query += CommandParameters.CheckParameters(parameters);
if (DataBase.ConnectToDataBase())
{
SqlCommand command = new SqlCommand();
command.Connection = DataBase.Connection;
command.CommandText = query;
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
try
{
allGroups.Add(Membership.GetMembershipFromReader(reader));
}
catch (SqlException message)
{
ApplicationLog.LogService.AddRaportCatchException("Error!!!\tZapytanie nie zwróciło żadnej wartości.", message);
}
}
DataBase.CloseConnection();
reader.Dispose();
}
return(allGroups);
}
/// <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);
}
/// <summary>
/// Pobierz dzienne aktywności jako obiekt 'Activity'
/// </summary>
/// <returns>Zwraca listę dziennych aktywności</returns>
public List <Activity> GetDailyActivity(CommandParameters parameters)
{
List <Activity> dailyActivity = new List <Activity>();
string query = "SELECT nameactivity.Id AS " + ColumnNames.ID +
", nameactivity.NameActivity AS " + ColumnNames.Name +
", SUM(alldate.ActivityTime) AS " + ColumnNames.ActivityTime +
", alldate.Date AS " + ColumnNames.Date +
" FROM nameactivity " +
"INNER JOIN activeapplications ON nameactivity.Id = activeapplications.IdNameActivity " +
"INNER JOIN alldate ON alldate.IdTitle = activeapplications.Id " +
"WHERE " + ColumnNames.ActivityTime + " > 0 ";
query += CommandParameters.CheckParameters(parameters);
query += " GROUP BY nameactivity.Id, nameactivity.NameActivity, alldate.Date ";
if (DataBase.ConnectToDataBase())
{
command.Connection = DataBase.Connection;
command.CommandText = query;
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
try
{
dailyActivity.Add(Activity.GetActivityFromReader(reader));
}
catch (SqlException message)
{
ApplicationLog.LogService.AddRaportCatchException("Error!!!\tZapytanie nie zwróciło żadnej wartości.", message);
}
}
DataBase.CloseConnection();
reader.Dispose();
}
return(dailyActivity);
}