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;
}
}
