public void UpdateTimeNonActiv()
{
string contentCommand = "UPDATE dailyuseofapplication SET ActivityTime = ActivityTime + "
+ 1 + " WHERE IdTitle = 2";
DataBase.ExecuteNonQuery(contentCommand);
}
private void AddTimeToDisabledComputer(int time)
{
string contentCommand = "UPDATE dailyuseofapplication SET ActivityTime = ActivityTime + "
+ time + " WHERE IdTitle = 1";
DataBase.ExecuteNonQuery(contentCommand);
}
public static bool AddActivityToApplicationWithGroup(string idMembership, string idActivity)
{
string contentCommand = "UPDATE activeapplications SET IdNameActivity = "
+ " (select top 1 IdNameActivity from activeapplications where IdMembership = " + idMembership + ") "
+ " WHERE Id = " + idActivity;
return(DataBase.ExecuteNonQuery(contentCommand));
}
private void UpDateTimeThisTitle()
{
string contentCommand = "UPDATE dailyuseofapplication SET ActivityTime = ActivityTime + 1 FROM dailyuseofapplication " +
"INNER JOIN activeapplications ON dailyuseofapplication.IdTitle = activeapplications.Id " +
"WHERE activeapplications.Title = " + nameTitle;
DataBase.ExecuteNonQuery(contentCommand);
}
private void RestartContentTable()
{
string contentCommand = "TRUNCATE TABLE dailyuseofapplication";
DataBase.ExecuteNonQuery(contentCommand);
contentCommand = "INSERT INTO dailyuseofapplication (IdTitle, ActivityTime) VALUES (1, 0), (2, 0)";
DataBase.ExecuteNonQuery(contentCommand);
}
public static void ChangeNameActivity(string oldNameActivity, string newNameActivity)
{
string contentCommand = "UPDATE nameactivity SET NameActivity = " + SqlValidator.Validate(newNameActivity)
+ " WHERE NameActivity = " + SqlValidator.Validate(oldNameActivity);
if (DataBase.ExecuteNonQuery(contentCommand))
{
ApplicationLog.LogService.AddRaportInformation("Nazwa aktywności " + SqlValidator.Validate(oldNameActivity) + " została zmieniona na " + SqlValidator.Validate(newNameActivity));
}
else
{
ApplicationLog.LogService.AddRaportWarning("Nie udało się zamienić nazwy aktywności " + SqlValidator.Validate(oldNameActivity) + " na " + SqlValidator.Validate(newNameActivity));
}
}
public static bool DeleteOneApplicationWithGroup(int idApplication)
{
string contentCommand = "UPDATE activeapplications SET IdMembership = NULL , AutoGrouping = NULL WHERE ID = " + idApplication;
if (!DataBase.ExecuteNonQuery(contentCommand))
{
ApplicationLog.LogService.AddRaportWarning("Nie udało się usunąć aplikacji z aktywności");
return(false);
}
else
{
return(true);
}
}
public static bool Add(string name)
{
string contentCommand = "INSERT INTO membership (Title) VALUES (" + SqlValidator.Validate(name) + ")";
if (!DataBase.ExecuteNonQuery(contentCommand))
{
ApplicationLog.LogService.AddRaportWarning("Nie udało się dodać nowej grupy");
return(false);
}
else
{
return(true);
}
}
public static bool AddNewActivity(string nameActivity)
{
string contentCommand = "INSERT INTO nameactivity (NameActivity) VALUES (" + SqlValidator.Validate(nameActivity) + ")";
if (DataBase.ExecuteNonQuery(contentCommand))
{
ApplicationLog.LogService.AddRaportInformation("Została dodana nowa aktywność " + SqlValidator.Validate(nameActivity));
return(true);
}
else
{
ApplicationLog.LogService.AddRaportWarning("Nie udało się dodać nowej aktywności " + SqlValidator.Validate(nameActivity));
return(false);
}
}
public static bool DeleteActivity(string nameActivity)
{
string contentCommand = "DELETE FROM nameactivity WHERE NameActivity = " + SqlValidator.Validate(nameActivity);
if (DataBase.ExecuteNonQuery(contentCommand))
{
ApplicationLog.LogService.AddRaportInformation("Została usunięta aktywność " + SqlValidator.Validate(nameActivity));
return(true);
}
else
{
ApplicationLog.LogService.AddRaportWarning("Nie udało się usunąć aktywności " + SqlValidator.Validate(nameActivity));
return(false);
}
}
public static bool DeleteFilterToConfiguration(int idGroup)
{
string contentCommand = "UPDATE membership SET Filter = NULL WHERE Id = " + idGroup;
if (!DataBase.ExecuteNonQuery(contentCommand))
{
ApplicationLog.LogService.AddRaportError("Nie udało się usunąć filtru ",
ApplicationLog.LogService.GetNameCurrentMethod() + "()",
System.IO.Directory.GetParent(System.IO.Directory.GetCurrentDirectory()).Parent.FullName + @"\Membership_db.cs");
return(false);
}
else
{
return(true);
}
}
public static bool DeleteOneApplicationWithActivity(int idActivity, int idApplication)
{
string contentCommand = "UPDATE activeapplications SET IdNameActivity = "
+ ((int)IdNameActivityEnum.Lack).ToString()
+ " WHERE IdNameActivity = " + idActivity + " AND ID = " + idApplication;
if (!DataBase.ExecuteNonQuery(contentCommand))
{
ApplicationLog.LogService.AddRaportWarning("Nie udało się usunąć aplikacji z aktywności");
return(false);
}
else
{
return(true);
}
}
public static bool DeleteAllApplicationsWithActivity(int idActivity)
{
string contentCommand = "UPDATE activeapplications SET IdNameActivity = "
+ ((int)IdNameActivityEnum.Lack).ToString()
+ " WHERE IdNameActivity = " + idActivity;
if (!DataBase.ExecuteNonQuery(contentCommand))
{
ApplicationLog.LogService.AddRaportError("Nie udało się usunąć wszystkich aplikacji z aktywności",
ApplicationLog.LogService.GetNameCurrentMethod() + "()",
System.IO.Directory.GetParent(System.IO.Directory.GetCurrentDirectory()).Parent.FullName + @"\ActiveApplication_db.cs");
return(false);
}
else
{
return(true);
}
}
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);
}
}
/// <summary>
/// Dodaje rekord do tabeli 'alldate', obługuje również dodatkowe połączenia.
/// </summary>
/// <param name="title">Nazwa aktywności.</param>
/// <param name="activityTime">Czas aktywności.</param>
/// <param name="idNameActivity">Id aktywności do którego jest przypisana aplikacja.</param>
/// <param name="daysDifferenceFromToday">Dni różnicy od dzisiejszego dnia.</param>
/// <param name="openningDataBase">Czy połącznie do bazy danych w metodzie ma być otwierane.</param>
/// <param name="closingConnectionDataBase">Czy połącznie do bazy danych w metodzie ma być zamykane.</param>
/// <param name="additionalConnection">Czy używać dodatkowego połącznia z bazy danych.</param>
public bool Add(int idTitle, int activityTime, int daysDifferenceFromToday = 1,
bool openninConnectiongDataBase = false, bool closingConnectionDataBase = false,
bool additionalConnection = false)
{
bool addRecord = false;
if (additionalConnection == false)
{
if (openninConnectiongDataBase)
{
DataBase.ConnectToDataBase();
}
if (DataBase.CheckIsOpenConnection())
{
command.Connection = DataBase.Connection;
command.CommandText = "INSERT INTO alldate (Date, IdTitle, ActivityTime) VALUES (DATEADD(day, " + (daysDifferenceFromToday * (-1)) + ", GETDATE()) , "
+ idTitle + " , " + activityTime + ")";
if (DataBase.ExecuteNonQuery(command))
{
addRecord = true;
}
}
if (closingConnectionDataBase)
{
DataBase.CloseConnection();
}
}
else
{
DataBase.AdditionalConnectToDataBase();
command.Connection = DataBase.AdditionalConnection;
command.CommandText = "INSERT INTO alldate (Date, IdTitle, ActivityTime) VALUES (DATEADD(day, " + (daysDifferenceFromToday * (-1)) + ", GETDATE()) , "
+ idTitle + " , " + activityTime + ")";
if (DataBase.ExecuteNonQuery(command))
{
addRecord = true;
}
DataBase.AdditionalConnection.Close();
}
return(addRecord);
}
public static void ChangeNameNonActiveWindow(string idNonActiveWindow, string newName)
{
if (!ActiveApplication_db.CheckIfExistTitle(SqlValidator.Validate(newName)))
{
string contentCommand = "INSERT INTO activeapplications (Title, IdNameActivity) VALUES ( " + SqlValidator.Validate(newName) + " , 1 )";
DataBase.ExecuteNonQuery(contentCommand);
string newIDApplication = ActiveApplication_db.GetIdActivityByName(SqlValidator.Validate(newName));
new AllData_db().UpdateIDApplication(newIDApplication, idNonActiveWindow);
DeleteNonActiveWindow(idNonActiveWindow);
}
else
{
string newIDApplication = ActiveApplication_db.GetIdActivityByName(SqlValidator.Validate(newName));
new AllData_db().UpdateIDApplication(newIDApplication, idNonActiveWindow);
DeleteNonActiveWindow(idNonActiveWindow);
}
}
public void TransferDataToAllDataAndClearTable()
{
string contentCommand = "SELECT IdTitle, ActivityTime from dailyuseofapplication";
SqlDataReader reader = GetExecuteReader(contentCommand);
while (reader.Read())
{
var idTitle = reader["IdTitle"];
var ActivityTime = reader["ActivityTime"];
allData_db.Add(Convert.ToInt32(idTitle), Convert.ToInt32(ActivityTime), additionalConnection: true);
}
reader.Dispose();
RestartContentTable();
DataBase.CloseConnection();
contentCommand = "INSERT INTO noactivewindow (IdNoActiveWindow) SELECT ID FROM alldate WHERE IdTitle = ";
contentCommand += "(SELECT ID FROM activeapplications ";
contentCommand += "LEFT JOIN noactivewindow ON noactivewindow.IdNoActiveWindow = alldate.Id ";
contentCommand += "WHERE Title = 'Brak aktywnego okna' AND noactivewindow.IdNoActiveWindow IS NULL) ";
contentCommand += "AND Date > CONVERT(VARCHAR(10), DATEADD(day, -20, GETDATE()), 23)";
DataBase.ExecuteNonQuery(contentCommand);
}
private void AddNameTitleToTableDailyUse()
{
string contentCommand = string.Empty;
if (!ActiveApplication_db.CheckIfExistTitle(nameTitle))
{
contentCommand = "INSERT INTO activeapplications (Title, IdNameActivity) VALUES ( "
+ nameTitle + " , " + 1 + " )";
DataBase.ExecuteNonQuery(contentCommand);
}
contentCommand = "INSERT INTO dailyuseofapplication (IdTitle, ActivityTime) " +
"SELECT activeapplications.ID, 1 " +
"FROM activeapplications WHERE activeapplications.Title = " + nameTitle;
DataBase.ExecuteNonQuery(contentCommand);
Dictionary <string, string> filters = Membership_db.GetFilterDictionaryIfIsFullConfiguration();
if (filters.Any())
{
Regex regex;
int idNameTitle = 0;
foreach (KeyValuePair <string, string> filter in filters)
{
regex = new Regex(filter.Value, RegexOptions.IgnoreCase);
if (regex.Matches(nameTitle).Count > 0)
{
idNameTitle = (Convert.ToInt32(ActiveApplication_db.GetIdActivityByName(nameTitle)));
ActiveApplication_db.AddGroupToApplications(new List <int> {
idNameTitle
}, (filter.Key).ToString());
ActiveApplication_db.AddActivityToApplicationWithGroup((filter.Key).ToString(), idNameTitle.ToString());
ExceptionApplication_db.AddExceptionApplication(idNameTitle);
}
}
}
}
public static bool DeleteAllApplicationsWithGroup(int idGroup, bool ifAutoGrouping = false, bool ifAddAutoGrouping = false)
{
string autoGrouping = string.Empty;
if (ifAddAutoGrouping)
{
autoGrouping = " AND AutoGrouping = " + Convert.ToInt32(ifAutoGrouping);
}
string contentCommand = "UPDATE activeapplications SET IdMembership = NULL , AutoGrouping = NULL"
+ " WHERE IdMembership = " + idGroup + autoGrouping;
if (!DataBase.ExecuteNonQuery(contentCommand))
{
ApplicationLog.LogService.AddRaportError("Nie udało się usunąć wszystkich aplikacji z grupy",
ApplicationLog.LogService.GetNameCurrentMethod() + "()",
System.IO.Directory.GetParent(System.IO.Directory.GetCurrentDirectory()).Parent.FullName + @"\ActiveApplication_db.cs");
return(false);
}
else
{
return(true);
}
}
public static bool SetAsOneApplication(int idGroup, bool ifAsOneApplication)
{
string contentCommand = "UPDATE membership SET AsOneApplication = " + Convert.ToInt32(ifAsOneApplication) + " WHERE Id = " + idGroup;
return(DataBase.ExecuteNonQuery(contentCommand));
}
public static bool SetActivityConfiguration(int idGroup, bool ifActivity)
{
string contentCommand = "UPDATE membership SET ActiveConfiguration = " + Convert.ToInt32(ifActivity) + " WHERE Id = " + idGroup;
return(DataBase.ExecuteNonQuery(contentCommand));
}
public static bool AddActivityToApplicationGroup(int idMembership, string idActivity)
{
string contentCommand = "UPDATE activeapplications SET IdNameActivity = " + idActivity + " WHERE IdMembership = " + idMembership;
return(DataBase.ExecuteNonQuery(contentCommand));
}
public void UpdateIDApplication(string newIDTitle, string oldIDTitle)
{
string contentCommand = "UPDATE alldate SET IdTitle = " + newIDTitle + " WHERE Id = " + oldIDTitle;
DataBase.ExecuteNonQuery(contentCommand);
}
public static bool DeleteNameActivityForIdMembership(int idMembership)
{
string contentCommand = "UPDATE activeapplications SET IdNameActivity = 1 WHERE IdMembership = " + idMembership;
return(DataBase.ExecuteNonQuery(contentCommand));
}
public static bool AddGroupToApplication(string idApplication, string idGroup)
{
string contentCommand = "UPDATE activeapplications SET IdMembership = " + idGroup + ", AutoGrouping = 0 WHERE Id = " + idApplication;
return(DataBase.ExecuteNonQuery(contentCommand));
}
public static bool AddGroupToApplications(List <int> idApplications, string idGroup)
{
string contentCommand = "UPDATE activeapplications SET IdMembership = " + idGroup + ", AutoGrouping = 1 WHERE Id " + SqlValidator.Validate_IN(idApplications);
return(DataBase.ExecuteNonQuery(contentCommand));
}
public static void DeleteNonActiveWindow(string idNonActiveWindow)
{
string contentCommand = "DELETE FROM noactivewindow WHERE IdNoActiveWindow = " + idNonActiveWindow;
DataBase.ExecuteNonQuery(contentCommand);
}
