//public static string StaticTicketDate1;
// public MainViewModel bugForm = new MainViewModel();
public static void opens(string ticketNumber, string ticketDate, string ticketReference, string ticketCategory, string ticketStatus, string ticketOriginator, string ticketDescription, string ticketAttachment)
{
try
{
OleDbConnection con = new OleDbConnection(string.Format("Provider = Microsoft.ACE.OLEDB.12.0; Data Source = {0}; Extended Properties =\"Excel 8.0; HDR=Yes; IMEX=1;\"", MainViewModel.staticFilePath));
//MessageBox.Show("staticFilePath" + MainViewModel.staticFilePath);
//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 dataTable = new DataTable();
OleDbDataAdapter adapter = new OleDbDataAdapter(oconn);
oconn.Connection = con;
int i;
int j;
//MessageBox.Show(dataTable.Rows[0][0].ToString());
adapter.Fill(dataTable);
//MessageBox.Show("data Table [1][1]" +dataTable.Rows[1][1].ToString());
//ds.Tables.Add(dataTable);
ObservableCollection<Ticket> 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" + ticketNumber + "r.ticketno" +r.ticketNo);
if (ticketNumber.Equals(r.ticketNo))
{
MainViewModel.StaticTicketNo = r.ticketNo;
MainViewModel.StaticTicketDate = r.ticketDate;
MainViewModel.StaticTicketReference = r.ticketReference;
MainViewModel.StaticTicketCategory = r.ticketCategory;
MainViewModel.StaticTicketStatus = r.ticketStatus;
MainViewModel.StaticTicketOriginator = r.tickectReporter;
MainViewModel.StaticTicketDescription = r.ticketDescription;
MainViewModel.StaticTicketAttachment = 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(string ticketNumber, string ticketDate, string ticketReference, string ticketCategory, string ticketStatus, string ticketOriginator, string ticketDescription, string ticketAttachment)
{
try
{
MessageBox.Show("Ticket Saved");
OleDbConnection con1 = new OleDbConnection(string.Format("Provider = Microsoft.ACE.OLEDB.12.0; Data Source = {0}; Extended Properties =\"Excel 8.0; HDR=Yes; typeGuessRows=0; ImportMixedType=test; IMEX=1;\"", MainViewModel.staticFilePath));
connStr2 = string.Format(@"Provider = Microsoft.ACE.OLEDB.12.0; Data Source = {0}; Extended Properties =""Excel 12.0 Macro; HDR=Yes; typeGuessRows=1; ImportMixedType=text; IMEX=3; """, MainViewModel.staticFilePath);
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++)
{
Ticket 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 (ticketNumber.Equals(r.ticketNo))
{
switchOld = false;
switchFind = true;
r.ticketNo = ticketNumber.ToString();
r.ticketDate = ticketDate.ToString();
r.ticketReference = ticketReference.ToString();
r.ticketCategory = ticketCategory.ToString();
r.ticketStatus = ticketStatus.ToString();
r.tickectReporter = ticketOriginator.ToString();
r.ticketDescription = ticketDescription.ToString();
r.ticketAttachment = ticketAttachment.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");
Ticket r = new Ticket();
r.ticketNo = ticketNumber.ToString();
r.ticketDate = ticketDate.ToString();
r.ticketReference = ticketReference.ToString();
r.ticketCategory = ticketCategory.ToString();
r.ticketStatus = ticketStatus.ToString();
r.tickectReporter = ticketOriginator.ToString();
r.ticketDescription = ticketDescription.ToString();
r.ticketAttachment = ticketAttachment.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");
}
}