private void acceptButton_Click(object sender, RoutedEventArgs e)
{
if (CheckIfGiveDataIsCorrect())
{
if (DataBase.TryConnectToSql(nameServer.Text, nameUser.Text, password.Password))
{
if (DataBase.ConnectToDataBase())
{
DataBase.CloseConnection();
using (System.IO.StreamWriter file = new System.IO.StreamWriter("Config.file", true))
{
file.WriteLine(nameServer.Text);
file.WriteLine(nameUser.Text);
file.WriteLine(password.Password);
}
CanRunApplication = true;
this.Close();
}
else
{
CanRunApplication = false;
BuildAndDisplayMessageErrorConnectToDataBase();
}
}
else
{
CanRunApplication = false;
BuildAndDisplayMessageErrorConnectToSql();
}
}
}
public RunApplication()
{
Directory.SetCurrentDirectory(AppDomain.CurrentDomain.BaseDirectory);
if (!System.IO.File.Exists("Config.file"))
{
GetDataBaseWindow getDataBaseWindow = new GetDataBaseWindow();
getDataBaseWindow.ShowDialog();
runApllication = getDataBaseWindow.CanRunApplication;
}
else
{
if (LoadConfigFileAndTryConnectSql())
{
if (DataBase.ConnectToDataBase())
{
DataBase.CloseConnection();
runApllication = true;
}
else
{
ErrorWindow errorWindow = new ErrorWindow();
errorWindow.DisplayErrorConnectToDataBase();
errorWindow.ShowDialog();
}
}
else
{
ErrorWindow errorWindow = new ErrorWindow();
errorWindow.DisplayErrorConnectToSql();
errorWindow.ShowDialog();
}
}
}
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 List <string> GetListStringFromExecuteReader(string contentCommand, string nameReturnColumn)
{
List <string> returnList = new List <string>();
SqlCommand command = new SqlCommand();
if (DataBase.ConnectToDataBase())
{
command.Connection = DataBase.Connection;
command.CommandText = contentCommand;
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
try
{
returnList.Add(reader[nameReturnColumn].ToString());
}
catch (SqlException message)
{
ApplicationLog.LogService.AddRaportCatchException("Error !!!\tZapytanie nie zwróciło żadnej wartości.", message);
}
}
DataBase.CloseConnection();
reader.Dispose();
}
return(returnList);
}
public string[,] GetBiggestResults()
{
string[,] biggestResults = new string[4, 2];
int i = 0;
string contentCommand =
"SELECT TOP 4 * FROM ( SELECT membership.Title AS Title ,sum(dailyuseofapplication.ActivityTime) AS ActivityTime " +
"FROM dailyuseofapplication INNER JOIN activeapplications ON activeapplications.Id = dailyuseofapplication.IdTitle " +
"INNER JOIN membership ON membership.Id = activeapplications.IdMembership GROUP BY membership.Title UNION " +
"SELECT activeapplications.Title AS Title, dailyuseofapplication.ActivityTime AS ActivityTime FROM dailyuseofapplication " +
"INNER JOIN activeapplications ON dailyuseofapplication.IdTitle = activeapplications.Id " +
"LEFT JOIN membership ON membership.Id = activeapplications.IdMembership WHERE activeapplications.Id > 2 " +
"AND ( activeapplications.IdMembership IS NULL OR membership.AsOneApplication = 0 )) d ORDER BY ActivityTime DESC";
SqlDataReader reader = GetExecuteReader(contentCommand);
while (reader.Read())
{
biggestResults[i, 0] = reader["Title"].ToString();
biggestResults[i, 1] = reader["ActivityTime"].ToString();
i++;
}
reader.Dispose();
DataBase.CloseConnection();
return(biggestResults);
}
public static Dictionary <string, string> GetDictionaryFromExecuteReader(string contentCommand, string nameKey, string nameValue)
{
Dictionary <string, string> dictionary = new Dictionary <string, string>();
SqlCommand command = new SqlCommand();
if (DataBase.ConnectToDataBase())
{
command.Connection = DataBase.Connection;
command.CommandText = contentCommand;
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
try
{
if (!dictionary.ContainsKey(reader[nameKey].ToString()))
{
dictionary.Add(reader[nameKey].ToString(), reader[nameValue].ToString());
}
}
catch (SqlException message)
{
ApplicationLog.LogService.AddRaportCatchException("Error !!!\tPobranie słownika nie powiodło się.", message);
}
}
DataBase.CloseConnection();
reader.Dispose();
}
return(dictionary);
}
public int GetTimeForAllTitle()
{
int time = 0;
string contentCommand = "SELECT ActivityTime FROM dailyuseofapplication " +
"WHERE dailyuseofapplication.IdTitle > 2";
SqlDataReader reader = GetExecuteReader(contentCommand);
while (reader.Read())
{
time += Convert.ToInt32(reader["ActivityTime"]);
}
DataBase.CloseConnection();
return(time);
}
private int GetAllTimeActivity()
{
int timeActivity = 0;
string contentCommand = "SELECT ActivityTime from dailyuseofapplication";
SqlDataReader reader = GetExecuteReader(contentCommand);
while (reader.Read())
{
timeActivity += Convert.ToInt32(reader["ActivityTime"]);
}
reader.Dispose();
DataBase.CloseConnection();
return(timeActivity);
}
public void Update()
{
nameTitle = "'" + activeWindow.GetNameActiveWindow().Replace("'", "") + "'";
DataBase.ConnectToDataBase();
command.Connection = DataBase.Connection;
if (GetTimeForTitle(nameTitle) > 0)
{
UpDateTimeThisTitle();
}
else
{
AddNameTitleToTableDailyUse();
}
DataBase.CloseConnection();
}
/// <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);
}
/// <summary>
/// Pobiera czas aktywności dla tytułu aplikacji.
/// </summary>
/// <param name="title">Tytuł aplikacji/onaczenie czynności.</param>
/// <returns></returns>
public int GetTimeForTitle(string title)
{
int time = 0;
nameTitle = "'" + title.Replace("'", "") + "'";
string contentCommand = "SELECT ActivityTime from dailyuseofapplication INNER JOIN " +
"activeapplications on dailyuseofapplication.IdTitle = activeapplications.Id " +
"WHERE activeapplications.Title = " + nameTitle;
SqlDataReader reader = GetExecuteReader(contentCommand);
while (reader.Read())
{
time = Convert.ToInt32(reader["ActivityTime"]);
}
reader.Dispose();
DataBase.CloseConnection();
return(time);
}
/// <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);
}
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 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);
}
static public bool ExecuteNonQuery(string contentCommand)
{
bool executeNonQuery = true;
SqlCommand command = new SqlCommand();
if (DataBase.ConnectToDataBase())
{
command.Connection = DataBase.Connection;
command.CommandText = contentCommand;
try
{
command.ExecuteNonQuery();
}
catch (Exception exception)
{
ApplicationLog.LogService.AddRaportCatchException("Error !!!\tNie udało się wykonać zapytania (ExecuteNonQuery).", exception);
executeNonQuery = false;
}
DataBase.CloseConnection();
}
return(executeNonQuery);
}
public int GetTimeForNumberActivity(List <int> numbers, bool ifExcept = false)
{
int time = 0;
string contentCommand = "SELECT ActivityTime FROM dailyuseofapplication INNER JOIN " +
"activeapplications ON dailyuseofapplication.IdTitle = activeapplications.Id " +
"WHERE 1 = 1";
for (int i = 0; i < numbers.Count; i++)
{
contentCommand += " AND activeapplications.IdNameActivity " + (ifExcept ? "!" : "") + "= " + numbers[i];
}
SqlDataReader reader = GetExecuteReader(contentCommand);
while (reader.Read())
{
time += Convert.ToInt32(reader["ActivityTime"]);
}
reader.Dispose();
DataBase.CloseConnection();
return(time);
}
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);
}
