/// <summary>
/// Pobiera ilość dni pracy aplikacji. Zwraca ilość dni z minusem jako string.
/// </summary>
/// <returns>Zwraca ilość dni jako string.</returns>
public string GetDayWorkingApplication()
{
string contentCommand = "SELECT DATEDIFF(DAY, GETDATE(), " + SqlValidator.Validate(GetDataRunApplication()) + ") as dateDifference";
string dateDifference = DataBase.GetListStringFromExecuteReader(contentCommand, "dateDifference")[0];
return(dateDifference);
}
public static string GetAllAutoGroupingApplication(int idGroup)
{
string contentCommand = "SELECT COUNT(*) as AutoGroupingApplication from activeapplications WHERE IdMembership = "
+ idGroup + " AND AutoGrouping = 1";
return(DataBase.GetListStringFromExecuteReader(contentCommand, "AutoGroupingApplication")[0]);
}
public static string GetAllNotJoinedApplication()
{
string contentCommand = "SELECT COUNT(*) as noJoinedApplication from activeapplications " +
"WHERE IdMembership IS NULL AND Id > 2";
return(DataBase.GetListStringFromExecuteReader(contentCommand, "noJoinedApplication")[0]);
}
public static string GetNameActivityByIdTitle(int idTitle)
{
string contentCommand = "SELECT nameactivity.NameActivity as nameActivity from activeapplications " +
" INNER JOIN nameactivity ON nameactivity.Id = activeapplications.IdNameActivity " +
" WHERE activeapplications.Id = " + idTitle;
return(DataBase.GetListStringFromExecuteReader(contentCommand, "nameActivity")[0]);
}
public static string GetTimeNonActiveWindow(string idNonActiveWindow)
{
string contentCommand = "SELECT alldate.ActivityTime AS ActivityTime FROM noactivewindow ";
contentCommand += "INNER JOIN alldate ON alldate.Id = noactivewindow.IdNoActiveWindow ";
contentCommand += "WHERE noactivewindow.IdNoActiveWindow = " + idNonActiveWindow;
return(DataBase.GetListStringFromExecuteReader(contentCommand, "ActivityTime")[0]);
}
public static string GetAllNotAssignedApplication()
{
string contentCommand = "SELECT ((SELECT COUNT(*) as noAssigmentApplication from activeapplications "
+ " LEFT JOIN membership ON membership.Id = activeapplications.IdMembership "
+ " WHERE idNameActivity = 1 AND (membership.AsOneApplication = 0 OR activeapplications.IdMembership IS NULL)) + "
+ " (SELECT COUNT(DISTINCT activeapplications.IdMembership) as noAssigmentApplication from activeapplications "
+ " INNER JOIN membership ON membership.Id = activeapplications.IdMembership "
+ " WHERE idNameActivity = 1 AND membership.AsOneApplication = 1)) as noAssigmentApplication ";
return(DataBase.GetListStringFromExecuteReader(contentCommand, "noAssigmentApplication")[0]);
}
public static bool CheckIfExistTitle(string title)
{
string contentCommand = "SELECT COUNT(*) as TitleExist from activeapplications WHERE Title = " + title;
if (DataBase.GetListStringFromExecuteReader(contentCommand, "TitleExist")[0] != "0")
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// Sprawdzanie czy obecna data jest kolejnym dniem, którego jeszcze nie ma w tabeli.
/// </summary>
/// <returns></returns>
public bool CheckIfIsNextDay()
{
string contentCommand = "SELECT Id from alldate WHERE Date = CONVERT(VARCHAR(10), DATEADD(day, -1, GETDATE()), 23) OR Date = GETDATE()";
if (!DataBase.GetListStringFromExecuteReader(contentCommand, "Id").Any())
{
return(true);
}
else
{
return(false);
}
}
public static bool CheckIfExistApplication(int idApplication)
{
string contentCommand = "SELECT COUNT(*) AS ifExistName FROM exceptionapplication WHERE IdExceptionApplication = " + idApplication;
if (Convert.ToInt32(DataBase.GetListStringFromExecuteReader(contentCommand, "ifExistName")[0]) > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// Sprawdza czy date istnieje w bazie.
/// </summary>
/// <param name="numberDayBack">Ilość dni na minusie wstecz od dzieśejszej daty.</param>
/// <returns></returns>
private bool CheckIfDateExistInBase(string numberDayBack = "0")
{
string contentCommand = "SELECT TOP 1 Date FROM alldate WHERE Date = CONVERT(VARCHAR(10), DATEADD(day, " + numberDayBack + ", GETDATE()), 23)";
if (!DataBase.GetListStringFromExecuteReader(contentCommand, "Date").Any())
{
return(false);
}
else
{
return(true);
}
}
public static bool CheckIfExistName(string name)
{
string contentCommand = "SELECT COUNT(*) AS ifExistName FROM membership WHERE Title = " + SqlValidator.Validate(name);
if (Convert.ToInt32(DataBase.GetListStringFromExecuteReader(contentCommand, "ifExistName")[0]) > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// Pobiera date uruchomienia aplikacji.
/// </summary>
/// <returns>Zwraca date jako string.</returns>
private string GetDataRunApplication()
{
string dataRunApplication = string.Empty;
string contentCommand = "SELECT TOP 1 Date FROM alldate ORDER BY Date ASC";
if (DataBase.GetListStringFromExecuteReader(contentCommand, "Date").Any())
{
dataRunApplication = DataBase.GetListStringFromExecuteReader(contentCommand, "Date")[0];
}
else
{
dataRunApplication = "0";
}
return(dataRunApplication);
}
public static string GetNameGroupByIdTitle(int idTitle)
{
string contentCommand = "SELECT membership.Title as nameGroup from activeapplications " +
" INNER JOIN membership ON membership.Id = activeapplications.IdMembership " +
" WHERE activeapplications.Id = " + idTitle;
if (DataBase.GetListStringFromExecuteReader(contentCommand, "nameGroup").Any())
{
return(DataBase.GetListStringFromExecuteReader(contentCommand, "nameGroup")[0]);
}
else
{
return("Brak");
}
}
public string GetTimeActivityForDateAndIdActivity(string date, int idTitle)
{
string contentCommand = "SELECT ActivityTime from alldate WHERE Date = " + SqlValidator.Validate(date)
+ " AND IdTitle = " + idTitle;
string returnValue = string.Empty;
if (DataBase.GetListStringFromExecuteReader(contentCommand, "ActivityTime").Any())
{
return(DataBase.GetListStringFromExecuteReader(contentCommand, "ActivityTime")[0]);
}
else
{
return("0");
}
}
public static bool DeleteGroup(int idGroup)
{
string contentCommand = "SELECT Title from membership WHERE Id = " + idGroup;
string nameGroup = DataBase.GetListStringFromExecuteReader(contentCommand, "Title")[0];
contentCommand = "DELETE FROM membership WHERE Id = " + idGroup;
if (DataBase.ExecuteNonQuery(contentCommand))
{
ApplicationLog.LogService.AddRaportInformation("Została usunięta grupa '" + nameGroup + "'");
return(true);
}
else
{
ApplicationLog.LogService.AddRaportWarning("Nie udało się usunąć grupa '" + nameGroup + "'");
return(false);
}
}
public string CountApplicationInInterwalTime(string dateFrom, string dateTo)
{
string contentCommand = "SELECT COUNT(DISTINCT IdTitle) as countIdTitle FROM alldate "
+ " INNER JOIN activeapplications ON activeapplications.Id = alldate.IdTitle "
+ " LEFT JOIN membership ON membership.Id = activeapplications.IdMembership "
+ " WHERE IdTitle > 2 AND (activeapplications.IdMembership IS NULL OR membership.AsOneApplication = 0) "
+ " AND " + SqlValidator.Validate_BETWEEN(ColumnNames.Date, dateFrom, dateTo).Replace("Date", "alldate.Date");
int returnCount = Convert.ToInt32(DataBase.GetListStringFromExecuteReader(contentCommand, "countIdTitle")[0]);
contentCommand = "SELECT COUNT(DISTINCT activeapplications.IdMembership) as countIdTitle FROM alldate "
+ " INNER JOIN activeapplications ON activeapplications.Id = alldate.IdTitle "
+ " INNER JOIN membership ON membership.Id = activeapplications.IdMembership "
+ " WHERE IdTitle > 2 AND membership.AsOneApplication = 1 "
+ " AND " + SqlValidator.Validate_BETWEEN(ColumnNames.Date, dateFrom, dateTo).Replace("Date", "alldate.Date");
returnCount += Convert.ToInt32(DataBase.GetListStringFromExecuteReader(contentCommand, "countIdTitle")[0]);
return(returnCount.ToString());
}
public int GetTimeForNumberActivity(List <int> numbers, string startDate = "", string endDate = "", bool ifExcept = false)
{
string contentCommand = "SELECT SUM(ActivityTime) as sumTimeActivity FROM alldate INNER JOIN " +
"activeapplications ON alldate.IdTitle = activeapplications.Id " +
"WHERE 1 = 1";
for (int i = 0; i < numbers.Count; i++)
{
contentCommand += " AND activeapplications.IdNameActivity " + (ifExcept ? "!" : "") + "= " + numbers[i];
}
if (!string.IsNullOrEmpty(startDate) && !string.IsNullOrEmpty(endDate))
{
contentCommand += " AND " + SqlValidator.Validate(startDate) + " < alldate.Date AND alldate.Date < " + SqlValidator.Validate(endDate);
}
else if (!string.IsNullOrEmpty(startDate))
{
contentCommand += " AND alldate.Date = " + SqlValidator.Validate(startDate);
}
int returnValue = 0;
Int32.TryParse(DataBase.GetListStringFromExecuteReader(contentCommand, "sumTimeActivity")[0], out returnValue);
return(returnValue);
}
public static string GetAllNameActivity()
{
string contentCommand = "SELECT COUNT(*) AS numberNameActivity FROM nameactivity WHERE ID != 1";
return(DataBase.GetListStringFromExecuteReader(contentCommand, "numberNameActivity")[0]);
}
/// <summary>
/// Metoda tylko dla szukania id aktywności, brak w metodzie walidacji SQL.
/// </summary>
/// <param name="name">Tytuł szukaniej aktywności.</param>
/// <returns></returns>
public static string GetIdActivityByName(string name)
{
string contentCommand = "SELECT Id from activeapplications WHERE Title = " + name;
return(DataBase.GetListStringFromExecuteReader(contentCommand, "Id")[0]);
}
public static string GetNumberApplicationInGroupWithFilter(int idGroup)
{
string contentCommand = "SELECT COUNT(*) AS Num FROM activeapplications WHERE IdMembership = " + idGroup + " AND AutoGrouping = 1";
return(DataBase.GetListStringFromExecuteReader(contentCommand, "Num")[0]);
}
public static int GetIDForNameActivity(string nameActivity)
{
string contentCommand = "SELECT Id FROM nameactivity WHERE NameActivity = " + SqlValidator.Validate(nameActivity);
return(Convert.ToInt32(DataBase.GetListStringFromExecuteReader(contentCommand, "Id")[0]));
}
public static string GetNumberAllConfiguredGroups()
{
string contentCommand = "SELECT COUNT(*) AS numberGroups FROM membership WHERE Configuration = 1";
return(DataBase.GetListStringFromExecuteReader(contentCommand, "numberGroups")[0]);
}
public static string GetNameActivityForID(int idActivity)
{
string contentCommand = "SELECT NameActivity FROM nameactivity WHERE Id = " + idActivity;
return(DataBase.GetListStringFromExecuteReader(contentCommand, "NameActivity")[0]);
}
