private void FillTimeTracking(int year, int month, string workersIds)
{
try
{
_timeTrackingDataTable.DefaultView.RowFilter = "";
_timeTrackingDataTable.Clear();
string commandText = "SELECT WorkersTimeTrackingID, TimeSpentAtWorkID, WorkDayTimeStart, " +
"WorkerID, TimeStart, TimeEnd, ThirdStageVerification, " +
"DeleteRecord, WorkStatusID, WorkOperationID " +
"FROM FAIITimeTracking.WorkersTimeTracking " +
"WHERE YEAR(WorkDayTimeStart) = @Year AND MONTH(WorkDayTimeStart) = @Month AND WorkerID IN (" +
workersIds + ") AND DeleteRecord <> True AND ThirdStageVerification = True";
_timeTrackingConnection = new MySqlConnection(_connectionString);
var command = new MySqlCommand(commandText, _timeTrackingConnection);
command.Parameters.Add("@Year", MySqlDbType.Int64).Value = year;
command.Parameters.Add("@Month", MySqlDbType.Int64).Value = month;
_timeTrackingDataAdapter = new MySqlDataAdapter(command);
_timeTrackingDataAdapter.Fill(_timeTrackingDataTable);
}
catch (Exception exp)
{
MessageBox.Show(
exp.Message +
"\n\n [TSC0002]Попробуйте перезапустить приложение. В случае повторения ошибки обратитесь к администратору.",
"Ошибка", MessageBoxButton.OK, MessageBoxImage.Error);
}
}
private void FillProdCalendar(int year)
{
try
{
if (_prodCalendarDataTable != null)
{
_prodCalendarDataTable.DefaultView.RowFilter = "";
_prodCalendarDataTable.Clear();
}
string commandText =
"SELECT ProductionCalendarID, Date, Standart40Time FROM FAIIProdCalendar.ProductionCalendar WHERE YEAR(Date) = '" +
year + "'";
_prodCalendarConnection = new MySqlConnection(_connectionString);
var command = new MySqlCommand(commandText, _prodCalendarConnection);
_prodCalendarDataAdapter = new MySqlDataAdapter(command);
// ReSharper disable ObjectCreationAsStatement
new MySqlCommandBuilder(_prodCalendarDataAdapter);
// ReSharper restore ObjectCreationAsStatement
_prodCalendarDataAdapter.Fill(_prodCalendarDataTable);
}
catch (Exception exp)
{
MessageBox.Show(exp.Message +
"\n\n[TSC0004] Попробуйте перезапустить приложение. В случае повторения ошибки обратитесь к администратору.",
"Ошибка", MessageBoxButton.OK, MessageBoxImage.Error);
}
}
private void FillInsalubrityMachinesOperations()
{
try
{
if (_insalubrityMachinesOperationsDataTable != null)
{
_insalubrityMachinesOperationsDataTable.DefaultView.RowFilter = "";
_insalubrityMachinesOperationsDataTable.Clear();
}
const string commandText = @"SELECT WorkOperationID, Insalubrity, InsalubrityRate
FROM FAIICatalog.MachinesOperations WHERE Insalubrity = True";
_insalubrityMachinesOperationsConnection = new MySqlConnection(_connectionString);
var command = new MySqlCommand(commandText, _insalubrityMachinesOperationsConnection);
_insalubrityMachinesOperationsDataAdapter = new MySqlDataAdapter(command);
_insalubrityMachinesOperationsDataAdapter.Fill(_insalubrityMachinesOperationsDataTable);
}
catch (Exception exp)
{
MessageBox.Show(
exp.Message +
"\n\n[TSC0005] Попробуйте перезапустить приложение. В случае повторения ошибки обратитесь к администратору.",
"Ошибка", MessageBoxButton.OK, MessageBoxImage.Error);
}
}
private void FillAbsencesTypes()
{
try
{
if (AbsencesTypesDataTable != null)
{
AbsencesTypesDataTable.DefaultView.RowFilter = "";
AbsencesTypesDataTable.Clear();
}
const string commandText = @"SELECT AbsencesTypeID, AbsencesName, AbsencesSymbol, AbsencesDescription,
SetInWeekend, SetInHoliday, ConsiderInResult, ConsiderNorm, Color, Locked
FROM FAIIVacations.AbsencesTypes";
_absencesTypesConnection = new MySqlConnection(_connectionString);
var command = new MySqlCommand(commandText, _absencesTypesConnection);
_absencesTypesDataAdapter = new MySqlDataAdapter(command);
// ReSharper disable ObjectCreationAsStatement
new MySqlCommandBuilder(_absencesTypesDataAdapter);
// ReSharper restore ObjectCreationAsStatement
_absencesTypesDataAdapter.Fill(AbsencesTypesDataTable);
}
catch (Exception exp)
{
MessageBox.Show(
exp.Message +
"\n\n[TSC0003] Попробуйте перезапустить приложение. В случае повторения ошибки обратитесь к администратору.",
"Ошибка", MessageBoxButton.OK, MessageBoxImage.Error);
}
}
public void FillPlannedScheduleForWorker(DateTime ScheduleDate, int workerID)
{
try
{
WorkerPlannedScheduleDataTable.Clear();
WorkerPlannedScheduleDataTable.DefaultView.RowFilter = "";
const string commandText = @"SELECT * FROM FAIIProdCalendar.PlannedSchedule
WHERE YEAR(ScheduleDate) = YEAR(@ScheduleDate)
AND MONTH(ScheduleDate) = MONTH(@ScheduleDate) AND WorkerID = @workerID";
WorkerPlannedScheduleConnection = new MySqlConnection(_connectionString);
var command = new MySqlCommand(commandText, WorkerPlannedScheduleConnection);
command.Parameters.Add("@ScheduleDate", MySqlDbType.DateTime).Value = ScheduleDate;
command.Parameters.Add("@workerID", MySqlDbType.Int64).Value = workerID;
WorkerPlannedScheduleDataAdapter = new MySqlDataAdapter(command);
WorkerPlannedScheduleDataAdapter.Fill(WorkerPlannedScheduleDataTable);
}
catch (Exception exp)
{
MessageBox.Show(
exp.Message +
"\n\n [PS0002]Попробуйте перезапустить приложение. В случае повторения ошибки обратитесь к администратору.",
"Ошибка", MessageBoxButton.OK, MessageBoxImage.Error);
}
}
private void FillPlannedSchedule(DateTime ScheduleDate)
{
try
{
PlannedScheduleDataTable.DefaultView.RowFilter = "";
if (PlannedScheduleViewSource != null)
{
PlannedScheduleViewSource.CustomFilter = "";
}
const string commandText = @"SELECT * FROM FAIIProdCalendar.PlannedSchedule
WHERE YEAR(ScheduleDate) = YEAR(@ScheduleDate)
AND MONTH(ScheduleDate) = MONTH(@ScheduleDate)";
PlannedScheduleConnection = new MySqlConnection(_connectionString);
var command = new MySqlCommand(commandText, PlannedScheduleConnection);
command.Parameters.Add("@ScheduleDate", MySqlDbType.DateTime).Value = ScheduleDate;
PlannedScheduleDataAdapter = new MySqlDataAdapter(command);
new MySqlCommandBuilder(PlannedScheduleDataAdapter);
PlannedScheduleDataAdapter.Fill(PlannedScheduleDataTable);
tempPlannedScheduleDataTable = PlannedScheduleDataTable.Clone();
}
catch (Exception exp)
{
MessageBox.Show(
exp.Message +
"\n\n [PS0001]Попробуйте перезапустить приложение. В случае повторения ошибки обратитесь к администратору.",
"Ошибка", MessageBoxButton.OK, MessageBoxImage.Error);
}
}
private void RefillAbsences()
{
try
{
_absencesTypesDataAdapter.Update(AbsencesTypesDataTable);
AbsencesTypesDataTable.Clear();
_absencesTypesDataAdapter.Fill(AbsencesTypesDataTable);
}
catch (Exception exp)
{
MessageBox.Show(
exp.Message +
"\n\n[TSC0004] Попробуйте перезапустить приложение. В случае повторения ошибки обратитесь к администратору.",
"Ошибка", MessageBoxButton.OK, MessageBoxImage.Error);
}
}
public void DeleteAbsences(int absencesTypeId)
{
var deletingRow = AbsencesTypesDataTable.Select("AbsencesTypeID = " + absencesTypeId)[0];
deletingRow.Delete();
try
{
_absencesTypesDataAdapter.Update(AbsencesTypesDataTable);
}
catch (Exception exp)
{
MessageBox.Show(
exp.Message +
"\n\n[TSC0006] Попробуйте перезапустить приложение. В случае повторения ошибки обратитесь к администратору.",
"Ошибка", MessageBoxButton.OK, MessageBoxImage.Error);
}
}
public bool SaveAbsences(int absencesTypeId, string absencesName, string absencesSymbol, string absencesDescription,
bool setInWeekend, bool setInHoliday, bool considerInResult, object color, bool considerNorm)
{
var dataRows =
AbsencesTypesDataTable.Select("AbsencesSymbol = '" + absencesSymbol +
"' AND AbsencesTypeID NOT = " + absencesTypeId);
if (dataRows.Length > 0)
{
return(false);
}
var dataRow = AbsencesTypesDataTable.Select("AbsencesTypeID = " + absencesTypeId)[0];
dataRow["AbsencesName"] = absencesName;
dataRow["AbsencesSymbol"] = absencesSymbol;
dataRow["AbsencesDescription"] = absencesDescription;
dataRow["SetInWeekend"] = setInWeekend;
dataRow["SetInHoliday"] = setInHoliday;
dataRow["ConsiderInResult"] = considerInResult;
dataRow["ConsiderNorm"] = considerNorm;
dataRow["Color"] = color;
try
{
_absencesTypesDataAdapter.Update(AbsencesTypesDataTable);
}
catch (Exception exp)
{
MessageBox.Show(
exp.Message +
"\n\n[TSC0005] Попробуйте перезапустить приложение. В случае повторения ошибки обратитесь к администратору.",
"Ошибка", MessageBoxButton.OK, MessageBoxImage.Error);
}
return(true);
}
private void FillTimeSpentAtWork(int year, int month, string workersIds)
{
try
{
if (_timeSpentAtWorkDataTable != null)
{
_timeSpentAtWorkDataTable.DefaultView.RowFilter = "";
_timeSpentAtWorkDataTable.Clear();
}
string commandText = "SELECT TimeSpentAtWorkID, WorkerID, Date, ShiftNumber, " +
"VCLP, DayEnd FROM FAIITimeTracking.TimeSpentAtWork " +
"WHERE DayEnd = TRUE AND YEAR(WorkDayTimeStart) = '" + year + "' " +
"AND MONTH(WorkDayTimeStart) = '" + month + "' " +
"AND WorkerID IN (" + workersIds + ")";
_timeSpentAtWorkConnection = new MySqlConnection(_connectionString);
var command = new MySqlCommand(commandText, _timeSpentAtWorkConnection);
#pragma warning disable 618
command.Parameters.Add("@WorkersIds", typeof(string)).Value = workersIds;
#pragma warning restore 618
_timeSpentAtWorkDataAdapter = new MySqlDataAdapter(command);
_timeSpentAtWorkDataAdapter.Fill(_timeSpentAtWorkDataTable);
}
catch (Exception exp)
{
MessageBox.Show(
exp.Message +
"\n\n[TSC0001] Попробуйте перезапустить приложение. В случае повторения ошибки обратитесь к администратору.",
"Ошибка", MessageBoxButton.OK, MessageBoxImage.Error);
}
}
private void FillTimesheet(int year, int month, string workersIds)
{
try
{
if (TimesheetDataTable != null)
{
TimesheetDataTable.DefaultView.RowFilter = "";
TimesheetDataTable.Clear();
}
string commandText =
"SELECT * FROM FAIITimeTracking.Timesheet WHERE YEAR(TimesheetDate) = '" + year +
"' AND MONTH(TimesheetDate) = '" + month + "' AND WorkerID IN (" + workersIds + ")";//AND WorkerID = '205' ";//
_timesheetConnection = new MySqlConnection(_connectionString);
var command = new MySqlCommand(commandText, _timesheetConnection);
_timesheetDataAdapter = new MySqlDataAdapter(command);
// ReSharper disable ObjectCreationAsStatement
new MySqlCommandBuilder(_timesheetDataAdapter);
// ReSharper restore ObjectCreationAsStatement
_timesheetDataAdapter.Fill(TimesheetDataTable);
if (TimesheetDataTable != null)
{
if (TimesheetDataTable.Columns.IndexOf("Deviation") == -1)
{
TimesheetDataTable.Columns.Add("Deviation", typeof(Boolean));
}
}
if (_timesheetWorkerIdsDataTable != null)
{
_timesheetWorkerIdsDataTable.DefaultView.RowFilter = "";
_timesheetWorkerIdsDataTable.Clear();
}
string commandTextIDs =
"SELECT WorkerID FROM FAIITimeTracking.Timesheet " +
"WHERE YEAR(TimesheetDate) = '" + year +
"' AND MONTH(TimesheetDate) = '" + month + "' AND WorkerID IN (" + workersIds + ")";
_timesheetWorkerIdsConnection = new MySqlConnection(_connectionString);
var commandIDs = new MySqlCommand(commandTextIDs, _timesheetWorkerIdsConnection);
_timesheetWorkerIdsDataAdapter = new MySqlDataAdapter(commandIDs);
_timesheetWorkerIdsDataAdapter.Fill(_timesheetWorkerIdsDataTable);
if (_timesheetStatETDataTable != null)
{
_timesheetStatETDataTable.DefaultView.RowFilter = "";
_timesheetStatETDataTable.Clear();
}
string commandText_ET =
"SELECT TimesheetDate, WorkerID, ExceedingTime, ExceedingAllTime " +
"FROM FAIITimeTracking.Timesheet " +
"WHERE YEAR(TimesheetDate) = '" + year +
"' AND WorkerID IN (" + workersIds + ")";
_timesheetStatETConnection = new MySqlConnection(_connectionString);
var command_ET = new MySqlCommand(commandText_ET, _timesheetStatETConnection);
_timesheetStatETDataAdapter = new MySqlDataAdapter(command_ET);
_timesheetStatETDataAdapter.Fill(_timesheetStatETDataTable);
}
catch (Exception exp)
{
MessageBox.Show(exp.Message +
"\n\n[TSC0004] Попробуйте перезапустить приложение. В случае повторения ошибки обратитесь к администратору.",
"Ошибка", MessageBoxButton.OK, MessageBoxImage.Error);
}
}