private void UpdateTzToDB()
{
bool successToLoadData = false;
System.Collections.Generic.List<string> Parameters = new List<string>();
System.Collections.Generic.List<string> DataFromTextBox = new List<string>();
string cmdName = "UPDATE USP_TZ_DATA SET ";
try
{
for (int i = 0; i < this.tabPage1.Controls.Count; i++)
{
if (this.tabPage1.Controls[i].Controls.Count == 0)
{
if ((this.tabPage1.Controls[i].GetType() == typeof(ComboBox)))
{
cmdName += " " + this.tabPage1.Controls[i].Name + " = :" + this.tabPage1.Controls[i].Name + ",";
Parameters.Add(this.tabPage1.Controls[i].Name);
DataFromTextBox.Add(PriemSpisanie.IsNullParametr((ComboBox)this.tabPage1.Controls[i]));
}
else if ((this.tabPage1.Controls[i].GetType() == typeof(TextBox)))
{
cmdName += " " + this.tabPage1.Controls[i].Name + " = :" + this.tabPage1.Controls[i].Name + ",";
Parameters.Add(this.tabPage1.Controls[i].Name);
DataFromTextBox.Add(PriemSpisanie.IsNullParametr((TextBox)this.tabPage1.Controls[i]));
}
else if (this.tabPage1.Controls[i].GetType() == typeof(DateTimePicker))
{
cmdName += " " + this.tabPage1.Controls[i].Name + " = to_date(:" + this.tabPage1.Controls[i].Name + ",'DD.MM.YYYY hh24:mi:ss'),";
Parameters.Add(this.tabPage1.Controls[i].Name);
DataFromTextBox.Add(((DateTimePicker)(this.tabPage1.Controls[i])).Value.ToString());
}
else
{
continue;
}
}
else if (this.tabPage1.Controls[i].Controls.Count > 0)
{
UpdateDBCycle(this.tabPage1.Controls[i], ref cmdName, ref Parameters, ref DataFromTextBox);
}
}
for (int i = 0; i < this.tabPage2.Controls.Count; i++)
{
if (this.tabPage2.Controls[i].Controls.Count == 0)
{
if ((this.tabPage2.Controls[i].GetType() == typeof(ComboBox)))
{
cmdName += " " + this.tabPage2.Controls[i].Name + " = :" + this.tabPage2.Controls[i].Name + ",";
Parameters.Add(this.tabPage2.Controls[i].Name);
DataFromTextBox.Add(PriemSpisanie.IsNullParametr((ComboBox)this.tabPage2.Controls[i]));
}
else if ((this.tabPage2.Controls[i].GetType() == typeof(TextBox)))
{
cmdName += " " + this.tabPage2.Controls[i].Name + " = :" + this.tabPage2.Controls[i].Name + ",";
Parameters.Add(this.tabPage2.Controls[i].Name);
DataFromTextBox.Add(PriemSpisanie.IsNullParametr((TextBox)this.tabPage2.Controls[i]));
}
else if (this.tabPage2.Controls[i].GetType() == typeof(DateTimePicker))
{
cmdName += " " + this.tabPage2.Controls[i].Name + " = to_date(:" + this.tabPage2.Controls[i].Name + ",'DD.MM.YYYY hh24:mi:ss'),";
Parameters.Add(this.tabPage2.Controls[i].Name);
DataFromTextBox.Add(((DateTimePicker)(this.tabPage2.Controls[i])).Value.ToString());
}
else
{
continue;
}
}
else if ((this.tabPage2.Controls[i].Controls.Count > 0) && ((String.Compare(this.tabPage2.Controls[i].Name, "panel45")) != 0))
{
UpdateDBCycle(this.tabPage2.Controls[i], ref cmdName, ref Parameters, ref DataFromTextBox);
}
}
cmdName = cmdName.Remove(cmdName.Length - 1);
cmdName += " WHERE ID_DOC = '" + number + "'";
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error");
}
successToLoadData = SQLOracle.UpdateQuery(cmdName, Parameters, DataFromTextBox);
Parameters.Clear();
DataFromTextBox.Clear();
for (int i = 1; i < 10; i++)
{
if (successToLoadData == true)
{
cmdName = "UPDATE USP_TZ_DATA_TP SET NAIM_KOMP = :NAIM_KOMP,OBOZN_KOMP = :OBOZN_KOMP, KOL = :KOL WHERE NUM_PANEL = '" + i.ToString() + "' AND ID_DOC = '" + number +"'";
Parameters.Add("NAIM_KOMP"); DataFromTextBox.Add(PriemSpisanie.IsNullParametr(((TextBox)(this.panel33.Controls.Find(("NAIM_KOMP0" + i.ToString()), true)[0]))));
Parameters.Add("OBOZN_KOMP"); DataFromTextBox.Add(PriemSpisanie.IsNullParametr(((TextBox)(this.panel32.Controls.Find(("OBOZN_KOMP0" + i.ToString()), true)[0]))));
Parameters.Add("KOL"); DataFromTextBox.Add(PriemSpisanie.IsNullParametr(((TextBox)(this.panel34.Controls.Find(("KOL0" + i.ToString()), true)[0]))));
successToLoadData = SQLOracle.UpdateQuery(cmdName, Parameters, DataFromTextBox);
Parameters.Clear();
DataFromTextBox.Clear();
}
}
for (int i = 10; i < 21; i++)
{
if (successToLoadData == true)
{
cmdName = "UPDATE USP_TZ_DATA_TP SET NAIM_KOMP = :NAIM_KOMP,OBOZN_KOMP = :OBOZN_KOMP, KOL = :KOL WHERE NUM_PANEL = '" + i.ToString() + "' AND ID_DOC = '" + number + "'";
Parameters.Add("NAIM_KOMP"); DataFromTextBox.Add(PriemSpisanie.IsNullParametr(((TextBox)(this.panel33.Controls.Find(("NAIM_KOMP" + i.ToString()), true)[0]))));
Parameters.Add("OBOZN_KOMP"); DataFromTextBox.Add(PriemSpisanie.IsNullParametr(((TextBox)(this.panel32.Controls.Find(("OBOZN_KOMP" + i.ToString()), true)[0]))));
Parameters.Add("KOL"); DataFromTextBox.Add(PriemSpisanie.IsNullParametr(((TextBox)(this.panel34.Controls.Find(("KOL" + i.ToString()), true)[0]))));
successToLoadData = SQLOracle.UpdateQuery(cmdName, Parameters, DataFromTextBox);
Parameters.Clear();
DataFromTextBox.Clear();
}
}
if ((BMPIsLoad == 1)||(BMPIsLoad == 3))
if ((this.SCETCH1.Image != null) && (successToLoadData == true))
using (TemporaryFile tempFile = new TemporaryFile())
{
this.SCETCH1.Image.Save(tempFile.FilePath.ToString());
System.IO.FileStream FileStreamSCETCH1 = new System.IO.FileStream(tempFile.FilePath.ToString(), System.IO.FileMode.Open, System.IO.FileAccess.Read);
byte[] SCETCH1InByte = new Byte[FileStreamSCETCH1.Length];
FileStreamSCETCH1.Read(SCETCH1InByte, 0, SCETCH1InByte.Length);
FileStreamSCETCH1.Close();
IDisposable disp = (IDisposable)FileStreamSCETCH1;
disp.Dispose();
successToLoadData = SQLOracle.BLOBUpdateQuery(("UPDATE USP_TZ_DATA SET SCETCH1 = :SCETCH1 WHERE ID_DOC = '" + number + "'"), "SCETCH1", SCETCH1InByte);
FileStreamSCETCH1.Dispose();
}
if ((BMPIsLoad == 2) || (BMPIsLoad == 3))
if ((this.SCETCH2.Image != null) && (successToLoadData == true))
using (TemporaryFile tempFile = new TemporaryFile())
{
this.SCETCH2.Image.Save(tempFile.FilePath.ToString());
System.IO.FileStream FileStreamBMP = new System.IO.FileStream(tempFile.FilePath.ToString(), System.IO.FileMode.Open, System.IO.FileAccess.Read);
byte[] BMPInByte = new Byte[FileStreamBMP.Length];
FileStreamBMP.Read(BMPInByte, 0, BMPInByte.Length);
FileStreamBMP.Close();
IDisposable disp = (IDisposable)FileStreamBMP;
disp.Dispose();
successToLoadData = SQLOracle.BLOBUpdateQuery(("UPDATE USP_TZ_DATA SET SCETCH2 = :SCETCH2 WHERE ID_DOC = '" + number + "'"), "SCETCH2", BMPInByte);
FileStreamBMP.Dispose();
}
if (successToLoadData == true)
{
MessageBox.Show("Загрузка прошла успешно!");
}
else
{
MessageBox.Show("Загрузка прошла неудачно!");
}
}
/// <summary>
/// Выгрузка данных в Excel;
/// </summary>
/// <returns></returns>
public void createXLS()
{
ExcelClass TZExcel = new ExcelClass();
HashCode.HashCode.CheckFileByHash("техническое задание.xlt");
if (System.IO.File.Exists(Program.PathString + "\\техническое задание.xlt"))
{
try
{
TZExcel.OpenDocument(Program.PathString + "\\техническое задание.xlt");
TZExcel.Visible = false;
if ((BMPIsLoad == 1) || (BMPIsLoad == 3))
{
if (this.SCETCH1.Image != null)
{
using (TemporaryFile tempFile = new TemporaryFile())
{
this.SCETCH1.Image.Save(tempFile.FilePath.ToString(), System.Drawing.Imaging.ImageFormat.Bmp);
TZExcel.SelectCells(this.SCETCH1.Name, Type.Missing);
TZExcel.WritePictureToCell(tempFile.FilePath.ToString());
}
}
}
else if ((BMPIsLoad == 0) || (BMPIsLoad == 2))
{
if (this.SCETCH1.Image != null)
{
TZExcel.SelectCells(this.SCETCH1.Name, Type.Missing);
TZExcel.WritePictureToCell(SQLOracle.LoadImageToTemp("Select SCETCH1 FROM USP_TZ_DATA WHERE ID_DOC = '" + number + "'"));
}
}
if ((BMPIsLoad == 2) || (BMPIsLoad == 3))
{
if (this.SCETCH2.Image != null)
{
using (TemporaryFile tempFile = new TemporaryFile())
{
this.SCETCH2.Image.Save(tempFile.FilePath.ToString(), System.Drawing.Imaging.ImageFormat.Bmp);
TZExcel.SelectCells(this.SCETCH2.Name, Type.Missing);
TZExcel.WritePictureToCell(tempFile.FilePath.ToString());
}
}
}
else if ((BMPIsLoad == 0) || (BMPIsLoad == 1))
{
if (this.SCETCH2.Image != null)
{
TZExcel.SelectCells(this.SCETCH2.Name, Type.Missing);
TZExcel.WritePictureToCell(SQLOracle.LoadImageToTemp("Select SCETCH2 FROM USP_TZ_DATA WHERE ID_DOC = '" + number + "'"));
}
}
for (int i = 0; i < this.tabPage1.Controls.Count; i++)
{
if (this.tabPage1.Controls[i].Controls.Count == 0)
{
if ((this.tabPage1.Controls[i].GetType() == typeof(DateTimePicker)) || (this.tabPage1.Controls[i].GetType() == typeof(TextBox)) || (this.tabPage1.Controls[i].GetType() == typeof(ComboBox)))
{
TZExcel.SelectCells(this.tabPage1.Controls[i].Name, Type.Missing);
TZExcel.WriteDataToCell(this.tabPage1.Controls[i].Text.ToString());
}
}
else if (this.tabPage1.Controls[i].Controls.Count > 0)
{
ExcelExport(this.tabPage1.Controls[i], ref TZExcel);
}
}
for (int i = 0; i < this.tabPage2.Controls.Count; i++)
{
if (this.tabPage2.Controls[i].Controls.Count == 0)
{
if ((this.tabPage2.Controls[i].GetType() == typeof(DateTimePicker)) || (this.tabPage2.Controls[i].GetType() == typeof(TextBox)) || (this.tabPage2.Controls[i].GetType() == typeof(ComboBox)))
{
TZExcel.SelectCells(this.tabPage2.Controls[i].Name, Type.Missing);
TZExcel.WriteDataToCell(this.tabPage2.Controls[i].Text.ToString());
}
}
else if (this.tabPage2.Controls[i].Controls.Count > 0)
{
ExcelExport(this.tabPage2.Controls[i], ref TZExcel);
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error");
}
finally
{
TZExcel.Visible = true;
TZExcel.Dispose();
}
}
}