public void Insert_Tabel(MySqlQueries mySqlQueries, DataGridView dataGridView, DateTimePicker dateTimePicker1, string query, string ID) { string output = string.Empty; string Identify = string.Empty; string RabVrem = string.Empty; int index = 0; DateTime Begin = new DateTime(dateTimePicker1.Value.Year, 1, 1); DateTime End = new DateTime(dateTimePicker1.Value.Year, 12, 31); Select_DataGridView(mySqlQueries.Select_Grafik_For_Tabel, dataGridView, ID, Begin.Year.ToString()); Select_Text(mySqlQueries.Exists_Tabel, ref output, ID, Begin.Year.ToString(), Begin.Month.ToString(), Begin.Day.ToString()); if (output == "0") { while (Begin <= End) { Insert_Update(query, ID, dataGridView.Rows[index].Cells[1].Value.ToString(), dataGridView.Rows[index].Cells[2].Value.ToString(), dataGridView.Rows[index].Cells[3].Value.ToString(), dataGridView.Rows[index].Cells[4].Value.ToString(), dataGridView.Rows[index].Cells[5].Value.ToString().Replace(',', '.')); Begin = Begin.AddDays(1); index++; } MessageBox.Show("Операция выполнена успешно.", "Успех"); } else { MessageBox.Show("Записи уже существуют.", "Предупреждение", MessageBoxButtons.OK, MessageBoxIcon.Warning); } }
public Main(string login, string password) { InitializeComponent(); MySqlOperations = new MySqlOperations(MySqlQueries); MySqlQueries = new MySqlQueries(); Login = login; Password = password; }
public Print_Tabel_Dialog(string iD, MySqlOperations mySqlOperations, MySqlQueries mySqlQueries) { InitializeComponent(); ID = iD; MySqlOperations = mySqlOperations; MySqlQueries = mySqlQueries; dateTimePicker1.Value = DateTime.Now; }
public Oklad(MySqlOperations mySqlOperations, MySqlQueries mySqlQueries) { InitializeComponent(); MySqlOperations = mySqlOperations; MySqlQueries = mySqlQueries; dateTimePicker1.Value = DateTime.Now; dateTimePicker1.MaxDate = DateTime.Parse(DateTime.Now.ToShortDateString()); }
public Vyplaty(MySqlOperations mySqlOperations, MySqlQueries mySqlQueries) { InitializeComponent(); MySqlOperations = mySqlOperations; MySqlQueries = mySqlQueries; dateTimePicker1.Value = DateTime.Now; dateTimePicker2.Value = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1); MySqlOperations.Select_ComboBox(MySqlQueries.Select_Sotrudniki_ComboBox, comboBox1); }
public Print_Vedomost_Dialog(MySqlOperations mySqlOperations, MySqlQueries mySqlQueries, string iD = null) { InitializeComponent(); ID = iD; MySqlOperations = mySqlOperations; MySqlQueries = mySqlQueries; dateTimePicker1.Value = DateTime.Now; mySqlOperations.Select_ComboBox(mySqlQueries.Select_Otdely_ComboBox, comboBox1); }
public Sotrudniki(MySqlOperations mySqlOperations, MySqlQueries mySqlQueries) { InitializeComponent(); MySqlOperations = mySqlOperations; MySqlQueries = mySqlQueries; MySqlOperations.Select_ComboBox(MySqlQueries.Select_Otdely_ComboBox, comboBox1); MySqlOperations.Select_ComboBox(MySqlQueries.Select_Doljnosti_ComboBox, comboBox2); MySqlOperations.Select_ComboBox(MySqlQueries.Select_Oklad_ComboBox, comboBox3); MySqlOperations.Select_ComboBox(MySqlQueries.Select_Rasch_Scheta_ComboBox, comboBox4); }
public void Print_Listok(MySqlQueries mySqlQueries, DataGridView dataGridView, SaveFileDialog saveFileDialog, string ID = null) { ExcelApplication ExcelApp = null; Workbooks workbooks = null; Workbook workbook = null; string output = null; string fileName = null; saveFileDialog.Title = "Сохранить расчетный листок как"; saveFileDialog.FileName = "Расчетный листок " + dataGridView.SelectedRows[0].Cells[4].Value.ToString(); saveFileDialog.InitialDirectory = Application.StartupPath + "\\Отчетность\\"; if (saveFileDialog.ShowDialog() == DialogResult.OK) { fileName = saveFileDialog.FileName; try { ExcelApp = new ExcelApplication(); workbooks = ExcelApp.Workbooks; workbook = workbooks.Open(Application.StartupPath + "\\Blanks\\Listok.xlsx"); ExcelApp.Cells[2, 5] = DateTime.Parse(dataGridView.SelectedRows[0].Cells[1].Value.ToString()).ToShortDateString(); ExcelApp.Cells[5, 3] = dataGridView.SelectedRows[0].Cells[4].Value.ToString(); decimal Nachisleno = decimal.Parse(dataGridView.SelectedRows[0].Cells[5].Value.ToString()); ExcelApp.Cells[10, 3] = Nachisleno; DateTime date1 = DateTime.Parse(dataGridView.SelectedRows[0].Cells[2].Value.ToString().Split(' ')[0]); DateTime date2 = DateTime.Parse(dataGridView.SelectedRows[0].Cells[3].Value.ToString().Split(' ')[0]); Select_Text(mySqlQueries.Select_Otrabotano, ref output, dataGridView.SelectedRows[0].Cells[4].Value.ToString(), date1.Year.ToString() + "-" + date1.Month.ToString() + "-" + date1.Day.ToString(), date2.Year.ToString() + "-" + date2.Month.ToString() + "-" + date2.Day.ToString()); ExcelApp.Cells[10, 5] = decimal.Parse(output); Select_Text(mySqlQueries.Select_Kol_Dney_Otrabotano, ref output, dataGridView.SelectedRows[0].Cells[4].Value.ToString(), date1.Year.ToString() + "-" + date1.Month.ToString() + "-" + date1.Day.ToString(), date2.Year.ToString() + "-" + date2.Month.ToString() + "-" + date2.Day.ToString()); ExcelApp.Cells[10, 4] = decimal.Parse(output); ExcelApp.Cells[13, 3] = Nachisleno; ExcelApp.Cells[13, 7] = dataGridView.SelectedRows[0].Cells[6].Value.ToString(); ExcelApp.Cells[14, 4] = dataGridView.SelectedRows[0].Cells[7].Value.ToString(); ExcelApp.Cells[10, 7] = (Nachisleno * 1) / 100; ExcelApp.Cells[11, 7] = (Nachisleno * 1) / 100; ExcelApp.Cells[12, 7] = (Nachisleno * 13) / 100; workbook.SaveAs(fileName); ExcelApp.Visible = true; } catch (Exception ex) { MessageBox.Show(ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error); } finally { Marshal.ReleaseComObject(workbook); Marshal.ReleaseComObject(workbooks); Marshal.ReleaseComObject(ExcelApp); } } else { MessageBox.Show("Отсутствует табель учета рабочего времени" + '\n' + "для данного сотрудника на выбранный вами месяц." + '\n' + "Пожалуйста заполните табель учета рабочего времени для данного сотрудника.", "Предупреждение", MessageBoxButtons.OK, MessageBoxIcon.Warning); } }
public Tabel(string id, MySqlOperations mySqlOperations, MySqlQueries mySqlQueries, string query) { InitializeComponent(); ID = id; MySqlOperations = mySqlOperations; MySqlQueries = mySqlQueries; MySqlOperations.Select_DataGridView(query, dataGridView1, ID); if (int.TryParse(ID, out int result) != true) { MySqlOperations.Select_Text(MySqlQueries.Select_ID_Sotrudnika, ref ID, null, ID); } Cancel_Update(); }
//Методы для таблиц Должности, Отдел //Методы для таблиц График, Табель public void Insert_Grafik(MySqlQueries mySqlQueries, DateTimePicker dateTimePicker1, string query, string ID) { string output = string.Empty; string Identify = string.Empty; string RabVrem = string.Empty; DateTime Begin = new DateTime(dateTimePicker1.Value.Year, 1, 1); DateTime End = new DateTime(dateTimePicker1.Value.Year, 12, 31); Select_Text(mySqlQueries.Exists_Grafik_Raboty, ref output, ID, Begin.Year.ToString(), Begin.Month.ToString(), Begin.Day.ToString()); if (output == "0") { while (Begin <= End) { if (Begin.DayOfWeek.ToString() == "Friday") { Identify = "Р"; RabVrem = "7.2"; } else if (Begin.DayOfWeek.ToString() == "Saturday" || Begin.DayOfWeek.ToString() == "Sunday") { Identify = "В"; RabVrem = "0"; } else { Identify = "Р"; RabVrem = "8.2"; } Insert_Update(query, ID, Begin.Year.ToString(), Begin.Month.ToString(), Begin.Day.ToString(), Identify, RabVrem); Begin = Begin.AddDays(1); } MessageBox.Show("Операция выполнена успешно.", "Успех"); } else { MessageBox.Show("Записи уже существуют.", "Предупреждение", MessageBoxButtons.OK, MessageBoxIcon.Warning); } }
public Doljnosti(MySqlOperations mySqlOperations, MySqlQueries mySqlQueries) { InitializeComponent(); MySqlOperations = mySqlOperations; MySqlQueries = mySqlQueries; }
public LogIn() { InitializeComponent(); MySqlQueries = new MySqlQueries(); MySqlOperations = new MySqlOperations(MySqlQueries); }
public Otdely(MySqlOperations mySqlOperations, MySqlQueries mySqlQueries) { InitializeComponent(); MySqlOperations = mySqlOperations; MySqlQueries = mySqlQueries; }
public void Print_Vedomost(MySqlQueries mySqlQueries, DataGridView dataGridView, DateTimePicker dateTimePicker, SaveFileDialog saveFileDialog, string ID = null) { ExcelApplication ExcelApp = null; Workbooks workbooks = null; Workbook workbook = null; Worksheet worksheet = null; string output = null; string fileName = null; Select_Text(mySqlQueries.Select_Otdel_by_ID, ref output, ID); saveFileDialog.Title = "Сохранить расчетно-платежная ведомость как"; saveFileDialog.FileName = "Расчетно-платежная ведомость за " + dateTimePicker.Text + " (" + output + ")"; saveFileDialog.InitialDirectory = Application.StartupPath + "\\Отчетность\\"; if (saveFileDialog.ShowDialog() == DialogResult.OK) { fileName = saveFileDialog.FileName; try { ExcelApp = new ExcelApplication(); workbooks = ExcelApp.Workbooks; workbook = workbooks.Open(Application.StartupPath + "\\Blanks\\Vedomost.xlsx"); worksheet = workbook.Worksheets.get_Item(1) as Worksheet; ExcelApp.Cells[2, 2] = dateTimePicker.Text; ExcelApp.Cells[4, 8] = output; Select_Text(mySqlQueries.Itog_Vyplat_Po_Otdelu, ref output, ID); ExcelApp.Cells[5, 8] = output; string date1 = dateTimePicker.Value.Year.ToString() + "-" + dateTimePicker.Value.Month.ToString() + "-1"; string date2 = dateTimePicker.Value.Year.ToString() + "-" + dateTimePicker.Value.AddMonths(1).Month.ToString() + "-0"; Select_DataGridView(mySqlQueries.Select_Vyplaty_Otdela, dataGridView, ID, date1, date2); int ExRow = 4; int ExCol = 1; for (int i = 0; i < dataGridView.RowCount - 1; i++) { for (int j = 0; j < dataGridView.ColumnCount; j++) { ExcelApp.Cells[ExRow, ExCol] = dataGridView.Rows[i].Cells[j].Value.ToString(); ExCol++; } ExRow++; ExCol = 1; } var cells = worksheet.get_Range("A4 ", "E" + (ExRow - 1).ToString()); cells.Borders[XlBordersIndex.xlInsideVertical].LineStyle = XlLineStyle.xlContinuous; cells.Borders[XlBordersIndex.xlInsideHorizontal].LineStyle = XlLineStyle.xlContinuous; cells.Borders[XlBordersIndex.xlEdgeTop].LineStyle = XlLineStyle.xlContinuous; cells.Borders[XlBordersIndex.xlEdgeRight].LineStyle = XlLineStyle.xlContinuous; cells.Borders[XlBordersIndex.xlEdgeLeft].LineStyle = XlLineStyle.xlContinuous; cells.Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlContinuous; workbook.SaveAs(fileName); ExcelApp.Visible = true; } catch (Exception ex) { MessageBox.Show(ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error); } finally { Marshal.ReleaseComObject(workbook); Marshal.ReleaseComObject(workbooks); Marshal.ReleaseComObject(ExcelApp); } } else { MessageBox.Show("Отсутствует табель учета рабочего времени" + '\n' + "для данного сотрудника на выбранный вами месяц." + '\n' + "Пожалуйста заполните табель учета рабочего времени для данного сотрудника.", "Предупреждение", MessageBoxButtons.OK, MessageBoxIcon.Warning); } }
public void Print_Tabel(MySqlQueries mySqlQueries, DataGridView dataGridView, DateTimePicker dateTimePicker, SaveFileDialog saveFileDialog, string ID) { ExcelApplication ExcelApp = null; Workbooks workbooks = null; Workbook workbook = null; string output = null; string fileName = null; Select_Text(mySqlQueries.Select_FIO_Sotrudnika_by_ID, ref output, ID); saveFileDialog.Title = "Сохранить табель как"; saveFileDialog.FileName = "Табель учета рабочего времени для " + output; saveFileDialog.InitialDirectory = Application.StartupPath + "\\Отчетность\\"; if (saveFileDialog.ShowDialog() == DialogResult.OK) { fileName = saveFileDialog.FileName; Select_Text(mySqlQueries.Exists_Tabel_Print, ref output, ID, dateTimePicker.Value.Year.ToString() + "-" + dateTimePicker.Value.Month.ToString() + "-1", dateTimePicker.Value.Year.ToString() + "-" + dateTimePicker.Value.AddMonths(1).Month.ToString() + "-0"); if (output == "1") { Select_DataGridView(mySqlQueries.Print_Tabel, dataGridView, ID, dateTimePicker.Value.Year.ToString() + "-" + dateTimePicker.Value.Month.ToString() + "-1", dateTimePicker.Value.Year.ToString() + "-" + dateTimePicker.Value.AddMonths(1).Month.ToString() + "-0"); try { Select_Text(mySqlQueries.Select_FIO_Sotrudnika_by_ID, ref output, ID); ExcelApp = new ExcelApplication(); workbooks = ExcelApp.Workbooks; workbook = workbooks.Open(Application.StartupPath + "\\Blanks\\Tabel.xlsx"); ExcelApp.Cells[3, 16] = dateTimePicker.Text.ToString(); ExcelApp.Cells[11, 3] = output; ExcelApp.Cells[11, 2] = ID; Select_Text(mySqlQueries.Select_Otdel_Sotrudnika_by_ID, ref output, ID); ExcelApp.Cells[4, 3] = output.Split(' ')[0]; ExcelApp.Cells[4, 6] = output.Split(' ')[1]; Select_Text(mySqlQueries.Select_Doljnost_Sotrudnika_by_ID, ref output, ID); ExcelApp.Cells[11, 19] = output; Select_Text(mySqlQueries.Select_Kol_Rab_Dney_Tabel, ref output, ID, dateTimePicker.Value.Year.ToString() + "-" + dateTimePicker.Value.Month.ToString() + "-1", dateTimePicker.Value.Year.ToString() + "-" + dateTimePicker.Value.AddMonths(1).Month.ToString() + "-0"); ExcelApp.Cells[11, 20] = output; Select_Text(mySqlQueries.Select_VP_Tabel, ref output, ID, dateTimePicker.Value.Year.ToString() + "-" + dateTimePicker.Value.Month.ToString() + "-1", dateTimePicker.Value.Year.ToString() + "-" + dateTimePicker.Value.AddMonths(1).Month.ToString() + "-0"); ExcelApp.Cells[11, 21] = output; Select_Text(mySqlQueries.Select_Otrabotano_Tabel, ref output, ID, dateTimePicker.Value.Year.ToString() + "-" + dateTimePicker.Value.Month.ToString() + "-1", dateTimePicker.Value.Year.ToString() + "-" + dateTimePicker.Value.AddMonths(1).Month.ToString() + "-0"); ExcelApp.Cells[11, 22] = decimal.Parse(output); Select_Text(mySqlQueries.Select_Itogo_Rab_Chasov_Grafik, ref output, ID, dateTimePicker.Value.Year.ToString() + "-" + dateTimePicker.Value.Month.ToString() + "-1", dateTimePicker.Value.Year.ToString() + "-" + dateTimePicker.Value.AddMonths(1).Month.ToString() + "-0"); ExcelApp.Cells[28, 5] = output; int ExCol = 3; int ExRow = 12; for (int i = 0; i < dataGridView.Rows.Count - 1; i++) { for (int j = 1; j < dataGridView.Columns.Count; j++) { ExcelApp.Cells[ExRow, ExCol] = dataGridView.Rows[0].Cells[j].Value.ToString(); ExCol++; if (ExCol == 18 && j == 15) { ExRow++; ExCol = 3; } } } workbook.SaveAs(fileName); ExcelApp.Visible = true; } catch (Exception ex) { MessageBox.Show(ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error); } finally { Marshal.ReleaseComObject(workbook); Marshal.ReleaseComObject(workbooks); Marshal.ReleaseComObject(ExcelApp); } } else { MessageBox.Show("Отсутствует табель учета рабочего времени" + '\n' + "для данного сотрудника на выбранный вами месяц." + '\n' + "Пожалуйста заполните табель учета рабочего времени для данного сотрудника.", "Предупреждение", MessageBoxButtons.OK, MessageBoxIcon.Warning); } } }
public MySqlOperations(MySqlQueries sqlQueries) { this.MySqlQueries = sqlQueries; }
public void Print_Grafik(MySqlQueries mySqlQueries, DataGridView dataGridView, DateTimePicker dateTimePicker, SaveFileDialog saveFileDialog, string ID) { ExcelApplication ExcelApp = null; Workbooks workbooks = null; Workbook workbook = null; string output = null; string fileName = null; Select_Text(mySqlQueries.Select_Doljnost_by_ID, ref output, ID); saveFileDialog.Title = "Сохранить график работы как"; saveFileDialog.FileName = "График работы для " + output; saveFileDialog.InitialDirectory = Application.StartupPath + "\\Отчетность\\"; if (saveFileDialog.ShowDialog() == DialogResult.OK) { fileName = saveFileDialog.FileName; Select_Text(mySqlQueries.Exists_Grafik_Raboty_Print, ref output, ID, dateTimePicker.Value.Year.ToString() + "-1-1", dateTimePicker.Value.AddYears(1).Year.ToString() + "-1-0"); if (output == "1") { Select_DataGridView(mySqlQueries.Print_Grafik, dataGridView, ID, dateTimePicker.Value.Year.ToString() + "-1-1", dateTimePicker.Value.AddYears(1).Year.ToString() + "-1-0"); if (dataGridView.Rows.Count >= 13) { try { Select_Text(mySqlQueries.Select_Doljnost_by_ID, ref output, ID); ExcelApp = new ExcelApplication(); workbooks = ExcelApp.Workbooks; workbook = workbooks.Open(Application.StartupPath + "\\Blanks\\Grafik.xlsx"); ExcelApp.Cells[1, 22] = dateTimePicker.Value.Year.ToString(); ExcelApp.Cells[2, 13] = output; ExcelApp.Cells[26, 11] = dateTimePicker.Value.Year.ToString(); Select_Text(mySqlQueries.Select_Kol_Rab_Dney_Grafik, ref output, ID, dateTimePicker.Value.Year.ToString() + "-1-1", dateTimePicker.Value.AddYears(1).Year.ToString() + "-1-0"); ExcelApp.Cells[27, 5] = output; Select_Text(mySqlQueries.Select_Kol_PredPrazdn_Dney_Grafik, ref output, ID, dateTimePicker.Value.Year.ToString() + "-1-1", dateTimePicker.Value.AddYears(1).Year.ToString() + "-1-0"); ExcelApp.Cells[27, 20] = output; Select_Text(mySqlQueries.Select_Kol_Poln_Dney_Grafik, ref output, ID, dateTimePicker.Value.Year.ToString() + "-1-1", dateTimePicker.Value.AddYears(1).Year.ToString() + "-1-0"); ExcelApp.Cells[27, 11] = output; Select_Text(mySqlQueries.Select_Itogo_Rab_Chasov_Grafik, ref output, ID, dateTimePicker.Value.Year.ToString() + "-1-1", dateTimePicker.Value.AddYears(1).Year.ToString() + "-1-0"); ExcelApp.Cells[28, 5] = decimal.Parse(output); int ExCol = 2; int ExRow = 7; for (int i = 0; i < dataGridView.Rows.Count - 1; i++) { if (ExRow == 10 || ExRow == 14 || ExRow == 18) { ExRow++; } ExCol = 2; for (int j = 1; j < dataGridView.Columns.Count; j++) { ExcelApp.Cells[ExRow, ExCol] = dataGridView.Rows[i].Cells[j].Value.ToString(); ExCol++; } ExRow++; } workbook.SaveAs(fileName); ExcelApp.Visible = true; } catch (Exception ex) { MessageBox.Show(ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error); } finally { Marshal.ReleaseComObject(workbook); Marshal.ReleaseComObject(workbooks); Marshal.ReleaseComObject(ExcelApp); } } else { MessageBox.Show("Не хватает записей графика работы" + '\n' + "для данной должности на выбранный вами год." + '\n' + "Пожалуйста дополните график работы для данной должности." + '\n' + "Необходимо заполнить график на 12 месяцев для выбранного вами года.", "Предупреждение", MessageBoxButtons.OK, MessageBoxIcon.Warning); } } else { MessageBox.Show("Отсутствует график работы" + '\n' + "для данной должности на выбранный вами год." + '\n' + "Пожалуйста заполните график работы для данной должности.", "Предупреждение", MessageBoxButtons.OK, MessageBoxIcon.Warning); } } }
public Raschetniki(MySqlOperations mySqlOperations, MySqlQueries mySqlQueries) { InitializeComponent(); MySqlOperations = mySqlOperations; MySqlQueries = mySqlQueries; }