//public static string StaticTicketDate1;
// public MainViewModel bugForm = new MainViewModel();
public void opens()
{
try
{
//Brows b = new Brows();
//MessageBox.Show("Button works"+ sheet);
OleDbConnection con = new OleDbConnection("Provider = Microsoft.ACE.OLEDB.12.0; Data Source = " + MainViewModel.staticFilePath + "; Extended Properties =\"Excel 8.0; HDR=Yes; IMEX=1;\"");
//string from = textBox_from.Text.ToString();
//string to = textBox_to.Text.ToString();
OleDbCommand oconn = new OleDbCommand
(
"SELECT * From [" + "sheet1" + "$]", con
);
//DataSet ds = new DataSet();
dataTable = new DataTable();
OleDbDataAdapter adapter = new OleDbDataAdapter(oconn);
oconn.Connection = con;
int i;
int j;
adapter.Fill(dataTable);
//MessageBox.Show(dataTable.Rows[0][0].ToString());
//ds.Tables.Add(dataTable);
ef = new ObservableCollection<Ticket>();
for (i = 0; i <= dataTable.Rows.Count - 1; i++)
{
Ticket r = new Ticket();
r.ticketNo = dataTable.Rows[i][0].ToString();
r.ticketDate = dataTable.Rows[i][1].ToString();
r.ticketReference = dataTable.Rows[i][2].ToString();
r.ticketCategory = dataTable.Rows[i][3].ToString();
r.ticketStatus = dataTable.Rows[i][4].ToString();
r.tickectReporter = dataTable.Rows[i][5].ToString();
r.ticketDescription = dataTable.Rows[i][6].ToString();
r.ticketAttachment = dataTable.Rows[i][7].ToString();
//MessageBox.Show("Number" + MainWindow.bugForm.Number);
if (MainWindow.bugForm.Number.Equals(r.ticketNo))
{
MainWindow.bugForm.Number = r.ticketNo;
MainWindow.bugForm.Date = r.ticketDate;
MainWindow.bugForm.Reference = r.ticketReference;
MainWindow.bugForm.Category = r.ticketCategory;
MainWindow.bugForm.Status = r.ticketStatus;
MainWindow.bugForm.Originator = r.tickectReporter;
MainWindow.bugForm.Description = r.ticketDescription;
MainWindow.bugForm.Attachment = r.ticketAttachment;
}
ef.Add(r);
}
con.Close();
}
catch (Exception ButtonException)
{
MessageBox.Show(" an Exception happend, " + ButtonException);
throw new MyException("Open Bug file Exception");
}
}
public void AddReport()
{
try {
MessageBox.Show("Ticket Saved??");
OleDbConnection con1 = new OleDbConnection("Provider = Microsoft.ACE.OLEDB.12.0; Data Source = " + MainViewModel.staticFilePath + "; Extended Properties =\"Excel 8.0; HDR=Yes; typeGuessRows=0; ImportMixedType=test; IMEX=1;\"");
connStr2 = @"Provider = Microsoft.ACE.OLEDB.12.0; Data Source = " + MainViewModel.staticFilePath + "; Extended Properties =\"Excel 12.0 Macro; HDR=Yes; typeGuessRows=1; ImportMixedType=text; IMEX=3; \"";
OleDbCommand oconn1 = new OleDbCommand
(
"SELECT * From [" + "sheet1" + "$]", con1
);
dataTable1 = new DataTable();
OleDbDataAdapter adapter = new OleDbDataAdapter(oconn1);
OleDbConnection conn2 = new OleDbConnection(connStr2);
oconn1.Connection = con1;
int i;
int j;
adapter.Fill(dataTable1);
//MessageBox.Show(dataTable.Rows[0][0].ToString());
//ds.Tables.Add(dataTable);
ef1 = new ObservableCollection<Ticket>();
for (i = 0; i <= dataTable1.Rows.Count - 1; i++)
{
r = new Ticket();
r.ticketNo = dataTable1.Rows[i][0].ToString();
r.ticketDate = dataTable1.Rows[i][1].ToString();
r.ticketReference = dataTable1.Rows[i][2].ToString();
r.ticketCategory = dataTable1.Rows[i][3].ToString();
r.ticketStatus = dataTable1.Rows[i][4].ToString();
r.tickectReporter = dataTable1.Rows[i][5].ToString();
r.ticketDescription = dataTable1.Rows[i][6].ToString();
r.ticketAttachment = dataTable1.Rows[i][7].ToString();
//MessageBox.Show("Number" + MainWindow.bugForm.Number);
if (MainWindow.bugForm.Number.Equals(r.ticketNo))
{
switchOld = false;
switchFind = true;
r.ticketNo = MainWindow.bugForm.Number.ToString();
r.ticketDate = MainWindow.bugForm.Date.ToString();
r.ticketReference = MainWindow.bugForm.Reference.ToString();
r.ticketCategory = MainWindow.bugForm.Category.ToString();
r.ticketStatus = MainWindow.bugForm.Status.ToString();
r.tickectReporter = MainWindow.bugForm.Originator.ToString();
r.ticketDescription = MainWindow.bugForm.Description.ToString();
r.ticketAttachment = MainWindow.bugForm.Attachment.ToString();
one = r.ticketNo;
sql = @"Update [Sheet1$] set [Date Created] = '"+r.ticketDate+"', [ApplicationName] = '"+r.ticketReference+"', [Category]='"+r.ticketCategory+"', [Status]='"+r.ticketStatus+"', [Originator]='"+r.tickectReporter+"', [Description]='"+r.ticketDescription+"', [AttachedFile]='"+r.ticketAttachment+"' WHERE [ReportNo] = "+r.ticketNo+";";
OleDbCommand cmd2 = new OleDbCommand(sql, conn2);
conn2.Open();
//OleDbCommand cmd2 = new OleDbCommand(@"Update [Sheet1$] set [Date Created] = "+r.ticketDate+" where [ReportNo] = "+r.ticketNo +", conn2");
cmd2.ExecuteNonQuery();
//oconn1.CommandText = sql;
//oconn1.ExecuteNonQuery();
conn2.Close();
}
ef1.Add(r);
}
if (switchFind != true)
{
MessageBox.Show("Check point new");
r = new Ticket();
r.ticketNo = MainWindow.bugForm.Number.ToString();
r.ticketDate = MainWindow.bugForm.Date.ToString();
r.ticketReference = MainWindow.bugForm.Reference.ToString();
r.ticketCategory = MainWindow.bugForm.Category.ToString();
r.ticketStatus = MainWindow.bugForm.Status.ToString();
r.tickectReporter = MainWindow.bugForm.Originator.ToString();
r.ticketDescription = MainWindow.bugForm.Description.ToString();
r.ticketAttachment = MainWindow.bugForm.Attachment.ToString();
ef1.Add(r);
//switchFind = false;
//con1.Open();
//sql = "INSERT INTO [Sheet1$](ReportNo,Date,ApplicationName,Category,Status,Originator,Description,Attachedfile)VALUES(r.ticketNo,r.ticketDate,r.ticketReference,r.ticketCategory,r.ticketStatus,r.tickectReporter,r.ticketDescription,r.ticketAttachment)";
//sql = "INSERT INTO [Sheet2$](1,2)VALUES('2','3');";
one = r.ticketNo;
sql = "INSERT INTO [Sheet1$]([ReportNo],[Date Created],[ApplicationName],[Category],[Status],[Originator],[Description],[Attachedfile])VALUES('"+r.ticketNo+"','"+ r.ticketDate+"','"+ r.ticketReference+"','"+ r.ticketCategory+"','"+r.ticketStatus+"','"+ r.tickectReporter+"','"+ r.ticketDescription+"','"+ r.ticketAttachment+"'); ";
//sql = "INSERT INTO TABLE[Sheet1$](ReportNo)VALUES('3e3e33e')";
OleDbCommand cmd2 = new OleDbCommand(sql, conn2);
conn2.Open();
cmd2.ExecuteNonQuery();
//oconn1.CommandText = sql;
//oconn1.ExecuteNonQuery();
conn2.Close();
switchFind = true;
}
switchFind = false;
}
catch (Exception ButtonException2)
{
MessageBox.Show(" an Exception happend. AddTicket Class Data did not add to file, " + ButtonException2);
throw new MyException("Add Ticket Exception");
}
}