예제 #1
0
        //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");
            }


        }
예제 #2
0
        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");
            }

        }