private void Form4_Load(object sender, EventArgs e) { try { this.passwordTableAdapter.Fill(this.skladZavDBDataSet1.Password); } catch (Exception) { MessageBox.Show("База данных не доступна! Нажмите OK и введите действительные настройки подключения.", "Заявка по складу", MessageBoxButtons.OK, MessageBoxIcon.Error); this.Visible = false; Form10 f10 = new Form10(); f10.ShowDialog(); return; } }
private void button1_Click(object sender, EventArgs e) { if ((textBox1.Text == "") || (textBox2.Text == "") || (textBox3.Text == "")) { MessageBox.Show("Заполните пустые поля!", "Заявка по складу", MessageBoxButtons.OK, MessageBoxIcon.Warning); } else { if (textBox2.Text != textBox3.Text) { textBox1.Text = null; textBox2.Text = null; textBox3.Text = null; MessageBox.Show("Пароли не совпадают!", "Заявка по складу", MessageBoxButtons.OK, MessageBoxIcon.Warning); } else { int? prov = 0; string path = ""; try { XmlDocument doc = new XmlDocument(); doc.Load(AppDomain.CurrentDomain.BaseDirectory + "SkladZav.xml"); XmlElement xRoot = doc.DocumentElement; foreach (XmlNode xnode in xRoot) { if (xnode.Attributes.Count > 0) { XmlNode attr = xnode.Attributes.GetNamedItem("name"); if (attr != null) { if (attr.Value.ToString() == "ConnectionString") { foreach (XmlNode childnode in xnode.ChildNodes) { if (childnode.Name == "Path") { if (childnode.InnerText.ToString() != "") { path = childnode.InnerText; } } } } } } } } catch (Exception) { MessageBox.Show("Не удалось найти XML!", "Заявка по складу", MessageBoxButtons.OK, MessageBoxIcon.Error); Application.Exit(); } try { var sqlConn = new SqlConnection(path); { var sqlCmd = new SqlCommand("UpdatePassword2", sqlConn); sqlCmd.CommandType = CommandType.StoredProcedure; sqlCmd.Parameters.AddWithValue("@old", textBox1.Text); sqlCmd.Parameters.AddWithValue("@new", textBox2.Text); sqlCmd.Parameters.Add("@prov", SqlDbType.Int, 4); sqlCmd.Parameters["@prov"].Direction = ParameterDirection.Output; sqlConn.Open(); sqlCmd.ExecuteScalar(); prov = Convert.ToInt32(sqlCmd.Parameters["@prov"].Value.ToString()); sqlConn.Close(); } } catch (Exception) { MessageBox.Show("База данных не доступна! Нажмите OK и введите действительные настройки подключения.", "Заявка по складу", MessageBoxButtons.OK, MessageBoxIcon.Error); this.Visible = false; Form10 f10 = new Form10(); f10.ShowDialog(); return; } if (prov == 1) { textBox1.Text = null; textBox2.Text = null; textBox3.Text = null; MessageBox.Show("Пароль изменен!", "Заявка по складу", MessageBoxButtons.OK, MessageBoxIcon.Information); this.Visible = false; } else { if (prov == 2) { textBox1.Text = null; textBox2.Text = null; textBox3.Text = null; MessageBox.Show("Неверный пароль!", "Заявка по складу", MessageBoxButtons.OK, MessageBoxIcon.Warning); } else { textBox1.Text = null; textBox2.Text = null; textBox3.Text = null; MessageBox.Show("Пароль не был изменен!", "Заявка по складу", MessageBoxButtons.OK, MessageBoxIcon.Error); } } } } }
private void Form3_Load(object sender, EventArgs e) { string path = AppDomain.CurrentDomain.BaseDirectory; try { XmlDocument doc = new XmlDocument(); doc.Load(AppDomain.CurrentDomain.BaseDirectory + "SkladZav.xml"); XmlElement xRoot = doc.DocumentElement; foreach (XmlNode xnode in xRoot) { if (xnode.Attributes.Count > 0) { XmlNode attr = xnode.Attributes.GetNamedItem("name"); if (attr != null) { if (attr.Value.ToString() == "ExcelSaving") { foreach (XmlNode childnode in xnode.ChildNodes) { if (childnode.Name == "Path") { if (childnode.InnerText.ToString() != "") { path = childnode.InnerText; } } } } } } } } catch (Exception) { MessageBox.Show("Не удалось найти XML!", "Заявка по складу", MessageBoxButtons.OK, MessageBoxIcon.Error); Application.Exit(); } try { this.halfRecordTableAdapter.Fill(this.skladZavDBDataSet4.HalfRecord); this.lastMonthTableAdapter.Fill(this.skladZavDBDataSet4.LastMonth); } catch (Exception) { this.Visible = false; MessageBox.Show("База данных не доступна! Нажмите OK и введите действительные настройки подключения.", "Заявка по складу", MessageBoxButtons.OK, MessageBoxIcon.Error); Form10 f10 = new Form10(); f10.ShowDialog(); return; } if (DateTime.Now.Month > Convert.ToDateTime(skladZavDBDataSet4.LastMonth.Rows[0]["Number"].ToString()).Month || ((Convert.ToDateTime(skladZavDBDataSet4.LastMonth.Rows[0]["Number"].ToString()).Month == 12) && (DateTime.Now.Month != Convert.ToDateTime(skladZavDBDataSet4.LastMonth.Rows[0]["Number"].ToString()).Month))) { int x = 2, i = 0, SumK = 2; decimal Sum = 0; this.Visible = false; try { excelapp = new Excel.Application(); excelapp.Visible = false; excelapp.SheetsInNewWorkbook = 1; excelapp.Workbooks.Add(Type.Missing); excelapp.DisplayAlerts = false; excelappworkbooks = excelapp.Workbooks; excelappworkbook = excelappworkbooks[1]; excelsheets = excelappworkbook.Worksheets; excelworksheet = (Excel.Worksheet)excelsheets.get_Item(1); excelcells = (Excel.Range)excelworksheet.Columns["A:D", Type.Missing]; excelcells.ColumnWidth = 40; excelcells.RowHeight = 23; excelcells.Cells.Borders.LineStyle = Excel.XlLineStyle.xlContinuous; excelcells.Cells.Font.Name = "Times New Roman"; excelcells.Cells.Font.Size = 12; excelcells.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; excelcells.Cells.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter; excelcells = excelworksheet.get_Range("A1", "D1"); excelcells.RowHeight = 50; excelcells.Cells.Font.Size = 14; excelcells.Cells.Font.Bold = 1; excelcells.Cells.Interior.ColorIndex = 6; excelcells = excelworksheet.get_Range("A1", Type.Missing); excelcells.Cells.Value = "Наименование"; excelcells = excelworksheet.get_Range("B1", Type.Missing); excelcells.Cells.Value = "Количество по заявке"; excelcells = excelworksheet.get_Range("C1", Type.Missing); excelcells.Cells.Value = "Количество по приходу"; excelcells = excelworksheet.get_Range("D1", Type.Missing); excelcells.Cells.Value = "Сумма"; excelcells = (Excel.Range)excelworksheet.Columns["D:D", Type.Missing]; excelcells.Cells.NumberFormat = "#,##0.00$"; excelworksheet.get_Range("A" + x, "D" + x).Cells.Interior.ColorIndex = 4; excelworksheet.get_Range("A" + x, Type.Missing).Cells.Value = "Заказы"; x = x + 1; DataRow[] SubunitRow = this.skladZavDBDataSet4.Tables["halfrecord"].Select("Subunit = 'Заказ'"); while (i + 1 <= SubunitRow.Length) { excelworksheet.get_Range("A" + x, Type.Missing).Cells.Value = SubunitRow[i]["Name"].ToString(); excelworksheet.get_Range("B" + x, Type.Missing).Cells.Value = Convert.ToInt32(SubunitRow[i]["CountR"].ToString()); excelworksheet.get_Range("C" + x, Type.Missing).Cells.Value = Convert.ToInt32(SubunitRow[i]["CountIN"].ToString()); excelworksheet.get_Range("D" + x, Type.Missing).Cells.Value = Convert.ToDecimal(SubunitRow[i]["Sum"].ToString()); Sum = Sum + Convert.ToDecimal(SubunitRow[i]["Sum"].ToString()); x = x + 1; i++; } if (Sum != 0) { excelworksheet.get_Range("D" + SumK, Type.Missing).Cells.Value = Sum; } i = 0; excelworksheet.get_Range("A" + x, "D" + x).Cells.Interior.ColorIndex = 4; excelworksheet.get_Range("A" + x, Type.Missing).Cells.Value = "Цех"; SumK = x; Sum = 0; x = x + 1; SubunitRow = this.skladZavDBDataSet4.Tables["halfrecord"].Select("Subunit = 'Цех'"); while (i + 1 <= SubunitRow.Length) { excelworksheet.get_Range("A" + x, Type.Missing).Cells.Value = SubunitRow[i]["Name"].ToString(); excelworksheet.get_Range("B" + x, Type.Missing).Cells.Value = Convert.ToInt32(SubunitRow[i]["CountR"].ToString()); excelworksheet.get_Range("C" + x, Type.Missing).Cells.Value = Convert.ToInt32(SubunitRow[i]["CountIN"].ToString()); excelworksheet.get_Range("D" + x, Type.Missing).Cells.Value = Convert.ToDecimal(SubunitRow[i]["Sum"].ToString()); Sum = Sum + Convert.ToDecimal(SubunitRow[i]["Sum"].ToString()); x = x + 1; i++; } if (Sum != 0) { excelworksheet.get_Range("D" + SumK, Type.Missing).Cells.Value = Sum; } i = 0; excelworksheet.get_Range("A" + x, "D" + x).Cells.Interior.ColorIndex = 4; excelworksheet.get_Range("A" + x, Type.Missing).Cells.Value = "ОГМ"; SumK = x; Sum = 0; x = x + 1; SubunitRow = this.skladZavDBDataSet4.Tables["halfrecord"].Select("Subunit = 'ОГМ'"); while (i + 1 <= SubunitRow.Length) { excelworksheet.get_Range("A" + x, Type.Missing).Cells.Value = SubunitRow[i]["Name"].ToString(); excelworksheet.get_Range("B" + x, Type.Missing).Cells.Value = Convert.ToInt32(SubunitRow[i]["CountR"].ToString()); excelworksheet.get_Range("C" + x, Type.Missing).Cells.Value = Convert.ToInt32(SubunitRow[i]["CountIN"].ToString()); excelworksheet.get_Range("D" + x, Type.Missing).Cells.Value = Convert.ToDecimal(SubunitRow[i]["Sum"].ToString()); Sum = Sum + Convert.ToDecimal(SubunitRow[i]["Sum"].ToString()); x = x + 1; i++; } if (Sum != 0) { excelworksheet.get_Range("D" + SumK, Type.Missing).Cells.Value = Sum; } i = 0; excelworksheet.get_Range("A" + x, "D" + x).Cells.Interior.ColorIndex = 4; excelworksheet.get_Range("A" + x, Type.Missing).Cells.Value = "Белоусов"; SumK = x; Sum = 0; x = x + 1; SubunitRow = this.skladZavDBDataSet4.Tables["halfrecord"].Select("Subunit = 'Белоусов'"); while (i + 1 <= SubunitRow.Length) { excelworksheet.get_Range("A" + x, Type.Missing).Cells.Value = SubunitRow[i]["Name"].ToString(); excelworksheet.get_Range("B" + x, Type.Missing).Cells.Value = Convert.ToInt32(SubunitRow[i]["CountR"].ToString()); excelworksheet.get_Range("C" + x, Type.Missing).Cells.Value = Convert.ToInt32(SubunitRow[i]["CountIN"].ToString()); excelworksheet.get_Range("D" + x, Type.Missing).Cells.Value = Convert.ToDecimal(SubunitRow[i]["Sum"].ToString()); Sum = Sum + Convert.ToDecimal(SubunitRow[i]["Sum"].ToString()); x = x + 1; i++; } if (Sum != 0) { excelworksheet.get_Range("D" + SumK, Type.Missing).Cells.Value = Sum; } i = 0; excelworksheet.get_Range("A" + x, "D" + x).Cells.Interior.ColorIndex = 4; excelworksheet.get_Range("A" + x, Type.Missing).Cells.Value = "Стройка"; SumK = x; Sum = 0; x = x + 1; SubunitRow = this.skladZavDBDataSet4.Tables["halfrecord"].Select("Subunit = 'Стройка'"); while (i + 1 <= SubunitRow.Length) { excelworksheet.get_Range("A" + x, Type.Missing).Cells.Value = SubunitRow[i]["Name"].ToString(); excelworksheet.get_Range("B" + x, Type.Missing).Cells.Value = Convert.ToInt32(SubunitRow[i]["CountR"].ToString()); excelworksheet.get_Range("C" + x, Type.Missing).Cells.Value = Convert.ToInt32(SubunitRow[i]["CountIN"].ToString()); excelworksheet.get_Range("D" + x, Type.Missing).Cells.Value = Convert.ToDecimal(SubunitRow[i]["Sum"].ToString()); Sum = Sum + Convert.ToDecimal(SubunitRow[i]["Sum"].ToString()); x = x + 1; i++; } if (Sum != 0) { excelworksheet.get_Range("D" + SumK, Type.Missing).Cells.Value = Sum; } i = 0; excelworksheet.get_Range("A" + x, "D" + x).Cells.Interior.ColorIndex = 4; excelworksheet.get_Range("A" + x, Type.Missing).Cells.Value = "Объекты"; SumK = x; Sum = 0; x = x + 1; SubunitRow = this.skladZavDBDataSet4.Tables["halfrecord"].Select("Subunit = 'Объекты'"); while (i + 1 <= SubunitRow.Length) { excelworksheet.get_Range("A" + x, Type.Missing).Cells.Value = SubunitRow[i]["Name"].ToString(); excelworksheet.get_Range("B" + x, Type.Missing).Cells.Value = Convert.ToInt32(SubunitRow[i]["CountR"].ToString()); excelworksheet.get_Range("C" + x, Type.Missing).Cells.Value = Convert.ToInt32(SubunitRow[i]["CountIN"].ToString()); excelworksheet.get_Range("D" + x, Type.Missing).Cells.Value = Convert.ToDecimal(SubunitRow[i]["Sum"].ToString()); Sum = Sum + Convert.ToDecimal(SubunitRow[i]["Sum"].ToString()); x = x + 1; i++; } if (Sum != 0) { excelworksheet.get_Range("D" + SumK, Type.Missing).Cells.Value = Sum; } i = 0; excelworksheet.get_Range("A" + x, "D" + x).Cells.Interior.ColorIndex = 4; excelworksheet.get_Range("A" + x, Type.Missing).Cells.Value = "Прочие"; SumK = x; Sum = 0; x = x + 1; SubunitRow = this.skladZavDBDataSet4.Tables["halfrecord"].Select("Subunit = 'Прочие'"); while (i + 1 <= SubunitRow.Length) { excelworksheet.get_Range("A" + x, Type.Missing).Cells.Value = SubunitRow[i]["Name"].ToString(); excelworksheet.get_Range("B" + x, Type.Missing).Cells.Value = Convert.ToInt32(SubunitRow[i]["CountR"].ToString()); excelworksheet.get_Range("C" + x, Type.Missing).Cells.Value = Convert.ToInt32(SubunitRow[i]["CountIN"].ToString()); excelworksheet.get_Range("D" + x, Type.Missing).Cells.Value = Convert.ToDecimal(SubunitRow[i]["Sum"].ToString()); Sum = Sum + Convert.ToDecimal(SubunitRow[i]["Sum"].ToString()); x = x + 1; i++; } if (Sum != 0) { excelworksheet.get_Range("D" + SumK, Type.Missing).Cells.Value = Sum; } i = 0; } catch (Exception) { MessageBox.Show("Ошибка открытия Excel!", "Заявка по складу", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } try { string get = path + @"\" + "Отчет с " + Convert.ToDateTime(skladZavDBDataSet4.LastMonth.Rows[0]["Number"].ToString()).ToShortDateString() + " по " + DateTime.Now.ToShortDateString() + ".xlsx"; excelappworkbook = excelappworkbooks[1]; excelappworkbook.SaveAs(get, Excel.XlFileFormat.xlOpenXMLWorkbook, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); excelapp.Workbooks.Close(); excelapp.Quit(); for (i = 0; i + 1 <= this.skladZavDBDataSet4.Tables["halfrecord"].Rows.Count; i++) { this.skladZavDBDataSet4.Tables["halfrecord"].Rows[i].Delete(); } this.halfRecordTableAdapter.Update(this.skladZavDBDataSet4.HalfRecord); skladZavDBDataSet4.LastMonth.Rows[0]["Number"] = DateTime.Now; this.lastMonthTableAdapter.Update(this.skladZavDBDataSet4.LastMonth); } catch (Exception) { excelapp.Workbooks.Close(); excelapp.Quit(); MessageBox.Show("Не удалось сохранить отчет!", "Заявка по складу", MessageBoxButtons.OK, MessageBoxIcon.Error); } this.Visible = true; } }