private void listBoxCompanies_SelectedIndexChanged(object sender, EventArgs e) { if (_jurisUtility.DbOpen) { _jurisUtility.CloseDatabase(); } CompanyCode = "Company" + listBoxCompanies.SelectedValue; _jurisUtility.SetInstance(CompanyCode); JurisDbName = _jurisUtility.Company.DatabaseName; JBillsDbName = "JBills" + _jurisUtility.Company.Code; _jurisUtility.OpenDatabase(); if (_jurisUtility.DbOpen) { ///GetFieldLengths(); } string sql = "SELECT Name,convert(varchar,LastRuntime, 101) as LastRunDate,case when Active = 1 then 'Y' else 'N' end as Active,ID FROM ScheduleTasks"; DataSet Tasks = _jurisUtility.RecordsetFromSQL(sql); dataGridView2.AutoGenerateColumns = true; dataGridView2.DataSource = Tasks.Tables[0]; // dataset dataGridView2.Columns[0].Width = 220; dataGridView2.Columns[1].Width = 100; dataGridView2.Columns[2].Width = 50; }
private void getTotalRecords(int flag) { String SQL = ""; DataSet ff; if (flag == 1) //time only { int counter = 0; SQL = "select entryid from timeentry where entrystatus in (6,7,8,9)"; ff = _jurisUtility.RecordsetFromSQL(SQL); foreach (DataRow r in ff.Tables[0].Rows) { counter++; } totalRecords = counter; ff.Clear(); } else if (flag == 2) //exp only { int counter = 0; SQL = "select entryid from expenseentry where entrystatus in (6,7,8,9)"; ff = _jurisUtility.RecordsetFromSQL(SQL); foreach (DataRow r in ff.Tables[0].Rows) { counter++; } totalRecords = counter; ff.Clear(); } else if (flag == 3) //both { int counter = 0; SQL = "select entryid from timeentry where entrystatus in (6,7,8,9)"; ff = _jurisUtility.RecordsetFromSQL(SQL); foreach (DataRow r in ff.Tables[0].Rows) { counter++; } totalRecords = counter; ff.Clear(); counter = 0; SQL = "select entryid from expenseentry where entrystatus in (6,7,8,9)"; ff = _jurisUtility.RecordsetFromSQL(SQL); foreach (DataRow r in ff.Tables[0].Rows) { counter++; } totalRecords = totalRecords + counter; ff.Clear(); } }
private void processSingleClient(CliMat cc) { string sql = ""; cc.clisys = getCliSysNbr(cc.clicode); if (cc.clisys == 0) { ErrorLog er = new ErrorLog(); er.client = cc.clicode; er.message = "Client " + cc.clicode + " does not appear to be a valid client. Check that the entered code matches what is displayed in Core exactly." + "\r\n" + "\r\n"; //still close client even with no matters errorList.Add(er); } else { //make changes to matters first sql = " select matsysnbr from matter where matclinbr = " + cc.clisys.ToString() + " and MatStatusFlag <> 'C'"; DataSet ds = _jurisUtility.RecordsetFromSQL(sql); if (ds == null || ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0) { ErrorLog er = new ErrorLog(); er.client = cc.clicode; er.message = "Client " + cc.clicode + " does not have any open matters so no matters were changed. Client was still closed." + "\r\n" + "\r\n"; //still close client even with no matters errorList.Add(er); } else { foreach (DataRow dr in ds.Tables[0].Rows) // Lieke wants clients closed regardless of matter status or errors { processSingleMatter(Convert.ToInt32(dr[0].ToString()), ""); } } //close client regardless of matter status sql = ""; sql = "update client set CliReportingName = left('" + cc.name + "', 30), CliNickName = left('" + cc.name + "', 30), branch = '" + cc.branch + "' where clisysnbr = " + cc.clisys.ToString(); _jurisUtility.ExecuteNonQuery(0, sql); sql = "update client set AccountRep = '' where clisysnbr = " + cc.clisys.ToString(); _jurisUtility.ExecuteNonQuery(0, sql); sql = " select CNNoteText from ClientNote where cnclient = " + cc.clisys + " and CNNoteIndex = 'Remarks'"; DataSet ds1 = _jurisUtility.RecordsetFromSQL(sql); if (ds1 == null || ds1.Tables.Count == 0 || ds1.Tables[0].Rows.Count == 0) { //if no remarks notecard exists, create a new one sql = "insert into ClientNote ([CNClient] ,[CNNoteIndex],[CNObject],[CNNoteText] ,[CNNoteObject]) " + " values (" + cc.clisys.ToString() + ", 'Remarks', ' ', cast('" + cc.remarks + "' as nvarchar(max)), null)"; _jurisUtility.ExecuteNonQuery(0, sql); } else // if it does exist, put the new text at the top { sql = "update ClientNote set CNNoteText = cast('" + cc.remarks + "' + char(13) + char(10) + char(13) + char(10) + cast(CNNoteText as varchar(1000)) as nvarchar(max)) where CNClient = " + cc.clisys.ToString() + " and CNNoteIndex = 'Remarks'"; _jurisUtility.ExecuteNonQuery(0, sql); } } }
private void buttonReport_Click(object sender, EventArgs e) { // if (string.IsNullOrEmpty(toAtty) || string.IsNullOrEmpty(fromAtty)) // MessageBox.Show("Please select from both Timekeeper drop downs", "Selection Error"); // else // { //generates output of the report for before and after the change will be made to client if (!rbAll.Checked && !rbOne.Checked) { MessageBox.Show("Please select at least one checkbox", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } else if (rbOne.Checked && String.IsNullOrEmpty(txtPrebill.Text)) { MessageBox.Show("Please type in the prebill number", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } else { string SQLTkpr = getReportSQL(); DataSet myRSTkpr = _jurisUtility.RecordsetFromSQL(SQLTkpr); ReportDisplay rpds = new ReportDisplay(myRSTkpr); rpds.Show(); } }
public ClientBranchSelector(JurisUtility _jurisUtility, string windowText, string tableName) { InitializeComponent(); this.Text = windowText; tName = tableName; dataGridView1.ColumnCount = 1; dataGridView2.ColumnCount = 1; dataGridView1.Columns[0].Name = "Client Codes "; dataGridView2.Columns[0].Name = "Branches "; JU = _jurisUtility; DataSet client = _jurisUtility.RecordsetFromSQL("select client from " + tableName); DataSet branch = _jurisUtility.RecordsetFromSQL("select branch from " + tableName); fillRecordSet(client, dataGridView1); fillRecordSet(branch, dataGridView2); }
private void DoDaFix() { // Enter your SQL code here // To run a T-SQL statement with no results, int RecordsAffected = _jurisUtility.ExecuteNonQueryCommand(0, SQL); // To get an ADODB.Recordset, ADODB.Recordset myRS = _jurisUtility.RecordsetFromSQL(SQL); String SQL = "select tlmatter, tlbank, sum(tlamount) as ledBal, TABalance from trustledger " + " inner join trustaccount matt on matt.tamatter = tlmatter and TABank = tlbank" + " group by tlmatter, tlbank, TABalance " + " having matt.tabalance <> sum(tlamount) " + " order by tlmatter, tlbank"; DataSet ds = _jurisUtility.RecordsetFromSQL(SQL); foreach (DataRow row in ds.Tables[0].Rows) { tBal = new TrustHistory(); tBal.AcctID = row["tlbank"].ToString(); tBal.matterID = row["tlmatter"].ToString(); tBal.ledgerBal = Double.Parse(row["ledBal"].ToString()); tBal.AcctBal = Double.Parse(row["TABalance"].ToString()); tList.Add(tBal); } foreach (TrustHistory hist in tList) { if (hist.AcctBal != hist.ledgerBal) { SQL = "Update TrustAccount set TABalance = " + hist.ledgerBal.ToString("F", CultureInfo.CreateSpecificCulture("en-CA")) + " where TAMatter = " + hist.matterID + " and TABank = '" + hist.AcctID + "'"; _jurisUtility.ExecuteNonQueryCommand(0, SQL); } } _jurisUtility.ExecuteNonQueryCommand(0, SQL); UpdateStatus("All accounts and matters processed.", 1, 1); tList.Clear(); DialogResult dr = MessageBox.Show("The process is complete. Would you like to confirm the data is balanced?" + "\r\n" + "If the data is now balanced, the report will show NO records. Continue?", "Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.None); if (dr == System.Windows.Forms.DialogResult.Yes) { showReport(); } }
private void comboBox1_SelectedIndexChanged(object sender, EventArgs e) { if (comboBox1.SelectedIndex != -1) { batch = Convert.ToInt32(comboBox1.SelectedValue.ToString()); string sql = "SELECT distinct CRRecNbr from CashReceipt where CRBatch = " + batch; DataSet emp = _jurisUtility.RecordsetFromSQL(sql); if (emp == null || emp.Tables[0].Rows.Count == 0) { MessageBox.Show("There are no Records for that batch to process", "No processing", MessageBoxButtons.OK, MessageBoxIcon.Hand); } else { comboBox2.ValueMember = "CRRecNbr"; comboBox2.DisplayMember = "CRRecNbr"; comboBox2.DataSource = emp.Tables[0]; } } }
private void listBoxCompanies_SelectedIndexChanged(object sender, EventArgs e) { if (_jurisUtility.DbOpen) { _jurisUtility.CloseDatabase(); } CompanyCode = "Company" + listBoxCompanies.SelectedValue; _jurisUtility.SetInstance(CompanyCode); JurisDbName = _jurisUtility.Company.DatabaseName; JBillsDbName = "JBills" + _jurisUtility.Company.Code; _jurisUtility.OpenDatabase(); if (_jurisUtility.DbOpen) { ///GetFieldLengths(); } string TkprIndex; cbFrom.ClearItems(); string SQLTkpr = "select empinitials + case when len(empinitials)=1 then ' ' when len(empinitials)=2 then ' ' when len(empinitials)=3 then ' ' else ' ' end + empname as employee from employee where ( empsysnbr in (select morigatty from matorigatty) ) order by empinitials"; DataSet myRSTkpr = _jurisUtility.RecordsetFromSQL(SQLTkpr); if (myRSTkpr.Tables[0].Rows.Count == 0) { cbFrom.SelectedIndex = 0; } else { foreach (DataTable table in myRSTkpr.Tables) { foreach (DataRow dr in table.Rows) { TkprIndex = dr["employee"].ToString(); cbFrom.Items.Add(TkprIndex); } } } DateTime result = DateTime.Today.Subtract(TimeSpan.FromDays(1)); dateTimePicker1.Value = result; }
public bool doesPTExist(string PT, JurisUtility _jurisUtility) { bool ret = false; String sql = "Select * from PersonnelType where PrsTypCode = '" + PT + "'"; DataSet fd = _jurisUtility.RecordsetFromSQL(sql); if (fd != null && fd.Tables.Count > 0 && fd.Tables[0].Rows.Count > 0) { ret = true; } return(ret); }
public bool doesCliExist(string cli, JurisUtility _jurisUtility) { bool ret = false; String sql = "select * from client where dbo.jfn_FormatClientCode(clicode) = '" + cli + "'"; DataSet fd = _jurisUtility.RecordsetFromSQL(sql); if (fd != null && fd.Tables.Count > 0 && fd.Tables[0].Rows.Count > 0) { ret = true; } return(ret); }
public bool doesMatExist(string cli, JurisUtility _jurisUtility, string mat) { bool ret = false; String sql = "select * from matter inner join client on clisysnbr = matclinbr where dbo.jfn_FormatMatterCode(matcode) = '" + mat + "' and dbo.jfn_FormatClientCode(clicode) = '" + cli + "'"; DataSet fd = _jurisUtility.RecordsetFromSQL(sql); if (fd != null && fd.Tables.Count > 0 && fd.Tables[0].Rows.Count > 0) { ret = true; } return(ret); }
private void listBoxCompanies_SelectedIndexChanged(object sender, EventArgs e) { if (_jurisUtility.DbOpen) { _jurisUtility.CloseDatabase(); } CompanyCode = "Company" + listBoxCompanies.SelectedValue; _jurisUtility.SetInstance(CompanyCode); JurisDbName = _jurisUtility.Company.DatabaseName; JBillsDbName = "JBills" + _jurisUtility.Company.Code; _jurisUtility.OpenDatabase(); if (_jurisUtility.DbOpen) { ///GetFieldLengths(); } string bankAcct; comboBox1.ClearItems(); string SQLTkpr = "SELECT BnkCode + ' ' + BnkDesc as bankaccount FROM BankAccount"; DataSet myRSTkpr = _jurisUtility.RecordsetFromSQL(SQLTkpr); if (myRSTkpr.Tables[0].Rows.Count == 0) { comboBox1.SelectedIndex = -1; } else { foreach (DataTable table in myRSTkpr.Tables) { foreach (DataRow dr in table.Rows) { bankAcct = dr["bankaccount"].ToString(); comboBox1.Items.Add(bankAcct); } } } }
private string getWhereClause() { string SQL = ""; string Cli = "'" + textBoxClient.Text.Replace(" ", "").Replace(",", "','") + "'"; string Bra = ""; string temp = ""; if (textBoxBranch.Text.ToLower().Contains("slfe")) { string sql = "select distinct BRANCH from client where BRANCH like 'SLFe%'"; DataSet dd = _jurisUtility.RecordsetFromSQL(sql); if (dd != null && dd.Tables[0].Rows.Count > 0) { foreach (DataRow rw in dd.Tables[0].Rows) { temp = temp + rw[0].ToString() + ","; } temp = temp.TrimEnd(','); } } textBoxBranch.Text = textBoxBranch.Text.ToLower().Replace("slfe", temp); Bra = "'" + textBoxBranch.Text.Replace(" ", "").Replace(",", "','") + "'"; Bra = Bra.TrimEnd(','); if (!string.IsNullOrEmpty(textBoxBranch.Text) && !string.IsNullOrEmpty(textBoxClient.Text)) { SQL = " bt.BillToCliNbr in (select clisysnbr from client where clicode in (" + Cli + ")) or c.BRANCH in (" + Bra + ") "; } else if (!string.IsNullOrEmpty(textBoxClient.Text)) { SQL = " bt.BillToCliNbr in (select clisysnbr from client where clicode in (" + Cli + ")) "; } else if (!string.IsNullOrEmpty(textBoxBranch.Text)) { SQL = " c.BRANCH in (" + Bra + ") "; } return(SQL); }
private void listBoxCompanies_SelectedIndexChanged(object sender, EventArgs e) { if (_jurisUtility.DbOpen) { _jurisUtility.CloseDatabase(); } CompanyCode = "Company" + listBoxCompanies.SelectedValue; _jurisUtility.SetInstance(CompanyCode); JurisDbName = _jurisUtility.Company.DatabaseName; JBillsDbName = "JBills" + _jurisUtility.Company.Code; _jurisUtility.OpenDatabase(); if (_jurisUtility.DbOpen) { ///GetFieldLengths(); } string sql = "SELECT distinct empname, BillToBillingAtty FROM PreBill " + "inner join billto on billtosysnbr = pbbillto " + "inner join employee on empsysnbr = BillToBillingAtty " + " inner join prebillmatter on pbmprebill = pbsysnbr " + " inner join matter on matsysnbr = pbmmatter " + " where pbstatus <= 2 and matbillagreecode = 'R' and matfltfeeorretainer<>0 and matstatusflag='O' "; DataSet emp = _jurisUtility.RecordsetFromSQL(sql); if (emp == null || emp.Tables[0].Rows.Count == 0) { MessageBox.Show("There are no prebills to process", "No processing", MessageBoxButtons.OK, MessageBoxIcon.Hand); } else { comboBox1.ValueMember = "BillToBillingAtty"; comboBox1.DisplayMember = "empname"; comboBox1.DataSource = emp.Tables[0]; } }
private string getNumericCliCodesBetweenOneAnd9999() { List <string> clinbrs = new List <string>(); string usedCliNbrs = ""; string sql = "select dbo.jfn_FormatClientCode(clicode) as clicode from client where clicode NOT LIKE '%[^0-9]%'"; DataSet ds = _jurisUtility.RecordsetFromSQL(sql); if (ds.Tables[0].Rows.Count == 0) { return("None"); } else { foreach (DataRow dr in ds.Tables[0].Rows) { clinbrs.Add(dr[0].ToString()); } } foreach (string cli in clinbrs) { if (Convert.ToInt32(cli) >= 5 && Convert.ToInt32(cli) <= 9999) { usedCliNbrs = usedCliNbrs + "'" + cli.ToString() + "',"; } } usedCliNbrs = usedCliNbrs + "'BTDXUUHZ'"; //usedCliNbrs.Substring(0, usedCliNbrs.Length - 2); ds.Clear(); sql = "select clisysnbr from client where dbo.jfn_FormatClientCode(clicode) in (" + usedCliNbrs + ")"; ds = _jurisUtility.RecordsetFromSQL(sql); usedCliNbrs = ""; foreach (DataRow dr in ds.Tables[0].Rows) { usedCliNbrs = usedCliNbrs + dr[0].ToString() + ","; } usedCliNbrs = usedCliNbrs + "999999999"; return(usedCliNbrs); }
private void listBoxCompanies_SelectedIndexChanged(object sender, EventArgs e) { if (_jurisUtility.DbOpen) { _jurisUtility.CloseDatabase(); } CompanyCode = "Company" + listBoxCompanies.SelectedValue; _jurisUtility.SetInstance(CompanyCode); JurisDbName = _jurisUtility.Company.DatabaseName; JBillsDbName = "JBills" + _jurisUtility.Company.Code; _jurisUtility.OpenDatabase(); if (_jurisUtility.DbOpen) { ///GetFieldLengths(); } string sql = "select dbo.jfn_formatclientcode(client.CliCode) AS Client, client.clireportingname as ClientName, " + " dbo.jfn_formatmattercode(matter.MatCode) AS Matter, matter.matreportingname as MatterName, " + " matsysnbr as matsysnbr, clisysnbr as clisysnbr " + " from matter inner join client on matclinbr = clisysnbr where MatStatusFlag <> 'C' order by clicode, matcode"; DataSet Tasks = _jurisUtility.RecordsetFromSQL(sql); AllItems = new DataSet(); AllItems.Tables.Add(Tasks.Tables[0].Copy()); foreach (DataRow row in Tasks.Tables[0].Rows) { object[] items = new object[6]; items[0] = row[0].ToString(); items[1] = row[1].ToString(); items[2] = row[2].ToString(); items[3] = row[3].ToString(); items[4] = row[4].ToString(); items[5] = row[5].ToString(); dataGridView2.Rows.Add(items); } }
private void listBoxCompanies_SelectedIndexChanged(object sender, EventArgs e) { if (_jurisUtility.DbOpen) { _jurisUtility.CloseDatabase(); } CompanyCode = "Company" + listBoxCompanies.SelectedValue; _jurisUtility.SetInstance(CompanyCode); JurisDbName = _jurisUtility.Company.DatabaseName; JBillsDbName = "JBills" + _jurisUtility.Company.Code; _jurisUtility.OpenDatabase(); if (_jurisUtility.DbOpen) { ///GetFieldLengths(); } comboBox1.ClearItems(); string SQLTkpr = "select cast(EmpId as varchar) + ' ' + EmpName as employee from employee order by EmpInitials"; DataSet myRSTkpr = _jurisUtility.RecordsetFromSQL(SQLTkpr); if (myRSTkpr.Tables[0].Rows.Count == 0) { comboBox1.SelectedIndex = 0; } else { comboBox1.Items.Add("* All Employees"); foreach (DataRow dr in myRSTkpr.Tables[0].Rows) { comboBox1.Items.Add(dr["employee"].ToString()); allEmployees = allEmployees + "" + dr["employee"].ToString().Split(' ')[0] + ","; } allEmployees = allEmployees.TrimEnd(','); } }
private void buttonReport_Click(object sender, EventArgs e) { // if (string.IsNullOrEmpty(toAtty) || string.IsNullOrEmpty(fromAtty)) // MessageBox.Show("Please select from both Timekeeper drop downs", "Selection Error"); // else // { //generates output of the report for before and after the change will be made to client string SQLTkpr = getReportSQL(); DataSet myRSTkpr = _jurisUtility.RecordsetFromSQL(SQLTkpr); ReportDisplay rpds = new ReportDisplay(myRSTkpr); rpds.Show(); // } }
private void button2_Click(object sender, EventArgs e) //add number of time entries on each bill/matter { string SQL = "select distinct dbo.jfn_FormatClientCode(clicode) as ClientCode, dbo.jfn_FormatMatterCode(MatCode) as MatterCode, " + " arbillnbr as BillNo, convert(varchar, arbilldate, 101) as BillDate, count(bb.EntryID) as TotalEntries from billedtime " + "inner join timeentrylink aa on btid = aa.tbdid " + "inner join timeentry bb on aa.entryid = bb.entryid " + "inner join ARBill on arbillnbr = btbillnbr " + "Inner join matter on matsysnbr = btmatter " + "inner join Client on clisysnbr = matclinbr " + "where MatterSysNbr in (SELECT MatSysNbr FROM Matter where MatBillAgreeCode in ('N', 'B')) and entrysource = 'JurisGo' " + " and (billedtime.btbillnbr not in (select btbillnbr from ##TempGo where ##TempGo.btbillnbr in (select btbillnbr from ##TempGo2) and ##TempGo.BillableFlag = 'N')) " + " group by clicode, matcode, arbillnbr, arbilldate"; DataSet ff = _jurisUtility.RecordsetFromSQL(SQL); ReportDisplay rp = new ReportDisplay(ff); rp.ShowDialog(); }
private void UpdateBanks() { cbBank.ClearItems(); cbNew.ClearItems(); string SqlBank = "select left(bnkcode + ' ',10) + bnkdesc as Bank from BankAccount order by bnkcode"; DataSet sb = _jurisUtility.RecordsetFromSQL(SqlBank); string BCode; if (sb.Tables[0].Rows.Count == 0) { cbBank.SelectedIndex = 0; } else { foreach (DataTable table in sb.Tables) { foreach (DataRow dr in table.Rows) { BCode = dr["Bank"].ToString(); cbBank.Items.Add(BCode); } } } string SqlNew = "select Bank from (select left('****' + ' ',10) + 'CREATE NEW' as bank union all select left(bnkcode + ' ',10) + bnkdesc as Bank from BankAccount) BK group by bank order by bank"; DataSet sn = _jurisUtility.RecordsetFromSQL(SqlNew); string NCode; if (sn.Tables[0].Rows.Count == 0) { cbNew.SelectedIndex = 0; } else { foreach (DataTable table2 in sn.Tables) { foreach (DataRow dr2 in table2.Rows) { NCode = dr2["Bank"].ToString(); cbNew.Items.Add(NCode); } } } }
private void listBoxCompanies_SelectedIndexChanged(object sender, EventArgs e) { if (_jurisUtility.DbOpen) { _jurisUtility.CloseDatabase(); } CompanyCode = "Company" + listBoxCompanies.SelectedValue; _jurisUtility.SetInstance(CompanyCode); JurisDbName = _jurisUtility.Company.DatabaseName; JBillsDbName = "JBills" + _jurisUtility.Company.Code; _jurisUtility.OpenDatabase(); if (_jurisUtility.DbOpen) { cbClient.SelectedIndex = -1; string CliIndex; cbClient.ClearItems(); string SQLCli = "select Client from (select '* All' as Client union all select dbo.jfn_formatclientcode(clicode) + ' ' + clireportingname as Client from Client where clisysnbr in (select matclinbr from matter where matsysnbr in (select splitfrommat from splitbill))) CLI order by Client"; DataSet myRSCli = _jurisUtility.RecordsetFromSQL(SQLCli); if (myRSCli.Tables[0].Rows.Count == 0) { cbClient.SelectedIndex = 0; } else { foreach (DataTable table in myRSCli.Tables) { foreach (DataRow dr in table.Rows) { CliIndex = dr["Client"].ToString(); cbClient.Items.Add(CliIndex); } } } } }
private void listBoxCompanies_SelectedIndexChanged(object sender, EventArgs e) { if (_jurisUtility.DbOpen) { _jurisUtility.CloseDatabase(); } CompanyCode = "Company" + listBoxCompanies.SelectedValue; _jurisUtility.SetInstance(CompanyCode); JurisDbName = _jurisUtility.Company.DatabaseName; JBillsDbName = "JBills" + _jurisUtility.Company.Code; _jurisUtility.OpenDatabase(); if (_jurisUtility.DbOpen) { ///GetFieldLengths(); } string sstest = "select distinct ChtSubAcct1 from ChartOfAccounts order by ChtSubAcct1"; DataSet dd = _jurisUtility.RecordsetFromSQL(sstest); if (dd.Tables[0].Rows.Count == 1 && Convert.ToInt32(dd.Tables[0].Rows[0][0].ToString()) == 0) { sstest = getSQLBasedOnAccounts(false); } else { sstest = getSQLBasedOnAccounts(true); } string taskCode; cbFrom.ClearItems(); string SQLTkpr = sstest; DataSet myRSTkpr = _jurisUtility.RecordsetFromSQL(SQLTkpr); if (myRSTkpr.Tables[0].Rows.Count == 0) { cbFrom.SelectedIndex = 0; } else { foreach (DataTable table in myRSTkpr.Tables) { foreach (DataRow dr in table.Rows) { taskCode = dr["COA"].ToString(); cbFrom.Items.Add(taskCode); } } } string TkprIndex2; cbTo.ClearItems(); string SQLTkpr2 = sstest; DataSet myRSTkpr2 = _jurisUtility.RecordsetFromSQL(SQLTkpr2); if (myRSTkpr2.Tables[0].Rows.Count == 0) { cbTo.SelectedIndex = 0; } else { foreach (DataTable table in myRSTkpr2.Tables) { foreach (DataRow dr in table.Rows) { TkprIndex2 = dr["COA"].ToString(); cbTo.Items.Add(TkprIndex2); } } } }
private void DoDaFix() { // Enter your SQL code here // To run a T-SQL statement with no results, int RecordsAffected = _jurisUtility.ExecuteNonQueryCommand(0, SQL); // To get an ADODB.Recordset, ADODB.Recordset myRS = _jurisUtility.RecordsetFromSQL(SQL); OpenFileDialog dlg = new OpenFileDialog(); if (dlg.ShowDialog() == DialogResult.OK) { Cursor.Current = Cursors.WaitCursor; Application.DoEvents(); toolStripStatusLabel.Text = "Client Matter Name Formatting in process...."; statusStrip.Refresh(); UpdateStatus("Client Matter Name Formatting in process....", 0, 0); Application.DoEvents(); string fileName; fileName = dlg.FileName.ToString(); string script = File.ReadAllText(fileName); _jurisUtility.ExecuteNonQueryCommand(0, script); string SQL = "select clisysnbr, clireportingname FROM Client where clireportingname<>replace(replace(cast(clibillingfield02 as varchar(8000)), char(13),''), ' ', ' ')"; badClients = _jurisUtility.RecordsetFromSQL(SQL); List <Client> cliList = new List <Client>(); Client cli = null; if (badClients.Tables[0].Rows.Count > 0) { foreach (DataRow row in badClients.Tables[0].Rows) { cli = new Client(); cli.ID = Convert.ToInt32(row[0].ToString()); cli.name = row[1].ToString(); cli.type = 1; cliList.Add(cli); } SQL = "Update Client set clireportingname=replace(replace(cast(clibillingfield02 as varchar(8000)), char(13),''), ' ', ' ')"; _jurisUtility.ExecuteNonQueryCommand(0, SQL); badClients.Clear(); SQL = "select clisysnbr, dbo.jfn_FormatClientCode(clicode) as ClientCode, clireportingname, clinickname, clibillingfield02 as OldName FROM Client where CliNickName<>replace(replace(cast(clibillingfield02 as varchar(8000)), char(13),''), ' ', ' ')"; badClients = _jurisUtility.RecordsetFromSQL(SQL); if (badClients.Tables[0].Rows.Count > 0) { foreach (DataRow row in badClients.Tables[0].Rows) { if (IsAllUpper(row[2].ToString())) { string asTitleCase = System.Threading.Thread.CurrentThread.CurrentCulture.TextInfo.ToTitleCase(row[2].ToString().ToLower()); SQL = "Update Client set clireportingname = '" + asTitleCase + "', clinickname = '" + asTitleCase + "' where clisysnbr = " + row[0].ToString(); _jurisUtility.ExecuteNonQueryCommand(0, SQL); } else { SQL = "Update Client set clireportingname = replace(replace(cast(clibillingfield02 as varchar(8000)), char(13),''), ' ', ' '), clinickname = replace(replace(cast(clibillingfield02 as varchar(8000)), char(13),''), ' ', ' ') where clisysnbr = " + row[0].ToString(); _jurisUtility.ExecuteNonQueryCommand(0, SQL); } } } } SQL = "Update Client set clireportingname = clinickname"; _jurisUtility.ExecuteNonQueryCommand(0, SQL); //same thing but for matters (I am using the same variables, lists, etc to save time SQL = "select matsysnbr, matreportingname FROM matter where matreportingname<>replace(replace(cast(matbillingfield02 as varchar(8000)), char(13),''), ' ', ' ')"; badMatters = _jurisUtility.RecordsetFromSQL(SQL); if (badMatters.Tables[0].Rows.Count > 0) { foreach (DataRow row in badMatters.Tables[0].Rows) { cli = new Client(); cli.ID = Convert.ToInt32(row[0].ToString()); cli.name = row[1].ToString(); cli.type = 2; cliList.Add(cli); } SQL = "Update matter set matreportingname=replace(replace(cast(matbillingfield02 as varchar(8000)), char(13),''), ' ', ' ')"; _jurisUtility.ExecuteNonQueryCommand(0, SQL); badMatters.Clear(); SQL = "select matsysnbr, dbo.jfn_FormatMatterCode(matcode) as MatterCode, matreportingname, matnickname, matbillingfield02 as OldName FROM matter where matNickName<>replace(replace(cast(matbillingfield02 as varchar(8000)), char(13),''), ' ', ' ')"; badMatters = _jurisUtility.RecordsetFromSQL(SQL); if (badMatters.Tables[0].Rows.Count > 0) { foreach (DataRow row in badMatters.Tables[0].Rows) { if (IsAllUpper(row[2].ToString())) { string asTitleCase = System.Threading.Thread.CurrentThread.CurrentCulture.TextInfo.ToTitleCase(row[2].ToString().ToLower()); SQL = "Update matter set matreportingname = '" + asTitleCase + "', matnickname = '" + asTitleCase + "' where matsysnbr = " + row[0].ToString(); _jurisUtility.ExecuteNonQueryCommand(0, SQL); } else { SQL = "Update matter set matreportingname = replace(replace(cast(matbillingfield02 as varchar(8000)), char(13),''), ' ', ' '), matnickname = replace(replace(cast(matbillingfield02 as varchar(8000)), char(13),''), ' ', ' ') where matsysnbr = " + row[0].ToString(); _jurisUtility.ExecuteNonQueryCommand(0, SQL); } } } } SQL = "Update matter set matreportingname = matnickname"; _jurisUtility.ExecuteNonQueryCommand(0, SQL); string clis = ""; string mats = ""; foreach (Client cc in cliList) { if (cc.type == 1) { clis = clis + "," + cc.ID; } if (cc.type == 2) { mats = mats + "," + cc.ID; } } clis = clis.TrimStart(','); clis = clis.TrimEnd(','); mats = mats.TrimEnd(','); mats = mats.TrimStart(','); badMatters.Clear(); badClients.Clear(); SQL = "select dbo.jfn_FormatClientCode(clicode) as ClientCode, clireportingname, clinickname, clibillingfield02 as OldName FROM Client where clisysnbr in (" + clis + ")"; badClients = _jurisUtility.RecordsetFromSQL(SQL); SQL = "select dbo.jfn_FormatMatterCode(matcode) as MatterCode, matreportingname, matnickname, matbillingfield02 FROM matter where matsysnbr in (" + mats + ")"; badMatters = _jurisUtility.RecordsetFromSQL(SQL); Cursor.Current = Cursors.Default; toolStripStatusLabel.Text = "Client Matter Name Formatting Complete"; statusStrip.Refresh(); UpdateStatus("Client Matter Name Formatting Complete", 1, 1); if (badClients.Tables[0].Rows.Count == 0 && badMatters.Tables[0].Rows.Count == 0) { MessageBox.Show("The process is complete. There were no found anomalies"); } else { DialogResult dd = MessageBox.Show("The process is complete. Would you like to see a list of items that need a closer look?", "Anomaly Log", MessageBoxButtons.YesNo, MessageBoxIcon.Question); if (dd == System.Windows.Forms.DialogResult.Yes) { ReportDisplay rpds = new ReportDisplay(badClients, badMatters); rpds.ShowDialog(); } } WriteLog("Client Matter Name Formatting"); Application.DoEvents(); badMatters.Clear(); badClients.Clear(); cliList.Clear(); } }
private void populateDropDowns() { string TkprIndex; cbKeep.ClearItems(); string SQLTkpr = "select dbo.jfn_FormatVendorCode(vencode) + ' ' + venname as vendor, dbo.jfn_FormatVendorCode(vencode) as vencode, VenSysNbr as ID from vendor"; if (!includeInactive) { SQLTkpr = SQLTkpr + " where VenActive='Y' order by vencode"; } else if (includeInactive) { SQLTkpr = SQLTkpr + " order by vencode"; } DataSet myRSTkpr = _jurisUtility.RecordsetFromSQL(SQLTkpr); if (myRSTkpr.Tables[0].Rows.Count == 0) { cbKeep.SelectedIndex = 0; } else { foreach (DataTable table in myRSTkpr.Tables) { foreach (DataRow dr in table.Rows) { TkprIndex = dr["vendor"].ToString(); cbKeep.Items.Add(TkprIndex); VendorCodeToID v = new VendorCodeToID(); //keep a list of all formatted codes and their associated ids v.code = dr["vencode"].ToString(); v.ID = dr["ID"].ToString(); venList.Add(v); } } } string TkprIndex2; cbDelete.ClearItems(); string SQLTkpr2 = "select dbo.jfn_FormatVendorCode(vencode) + ' ' + venname as vendor, dbo.jfn_FormatVendorCode(vencode) as vencode, VenSysNbr as ID from vendor"; if (!includeInactive) { SQLTkpr2 = SQLTkpr2 + " where VenActive='Y' order by vencode"; } else if (includeInactive) { SQLTkpr2 = SQLTkpr2 + " order by vencode"; } DataSet myRSTkpr2 = _jurisUtility.RecordsetFromSQL(SQLTkpr2); if (myRSTkpr2.Tables[0].Rows.Count == 0) { cbDelete.SelectedIndex = 0; } else { foreach (DataTable table in myRSTkpr2.Tables) { foreach (DataRow dr in table.Rows) { TkprIndex2 = dr["vendor"].ToString(); cbDelete.Items.Add(TkprIndex2); } } } }
private void DoDaFix() { // Enter your SQL code here // To run a T-SQL statement with no results, int RecordsAffected = _jurisUtility.ExecuteNonQueryCommand(0, SQL); // To get an ADODB.Recordset, ADODB.Recordset myRS = _jurisUtility.RecordsetFromSQL(SQL); String SQL = "select utid from unbilledtime where utcode1 not in (select empid from employee) " + "and utcode1 is not null and utcode1 <> ''"; DataSet ds = _jurisUtility.RecordsetFromSQL(SQL); foreach (DataRow dr in ds.Tables[0].Rows) { badUTIDs.Add(dr["utid"].ToString()); } String SQL1 = "update unbilledtime" + " set unbilledtime.uttkpr=case when code1sys is null then ut.uttkpr else code1sys end" + " from (select utid, uttkpr,ut.empid as AsWorkedTkpr, utcode1 as Code1, code1.empsysnbr as Code1Sys" + " from unbilledtime" + " inner join employee UT on uttkpr=UT.empsysnbr" + " left outer join employee Code1 on utcode1=code1.empid" + " inner join PreBillFeeItem on pbfutbatch=utbatch and pbfutrecnbr=utrecnbr" + " where utcode1<>UT.empid and utcode1>' ' and Code1.empvalidastkpr='Y') UT" + " where ut.utid=unbilledtime.utid "; ; _jurisUtility.ExecuteNonQueryCommand(0, SQL1); UpdateStatus("All As Worked Timekeepers updated.", 1, 1); if (badUTIDs.Count() > 0) { string IDs = ""; foreach (string id in badUTIDs) { IDs = IDs + ", " + id; } IDs = IDs.TrimEnd(); IDs = IDs.TrimEnd(','); IDs = IDs.TrimStart(); IDs = IDs.TrimStart(','); string SQL2 = "SELECT [UTBatch] as BatchNo " + ",Matnickname as MatterName " + ",convert(varchar, [UTDate], 120) as [Date] " + ",empname as Timekeeper " + ",[UTAmount] as WorkedAmount " + ",[UTCode1] as Code1 " + ",[UTNarrative] as Explanation " + "FROM [UnbilledTime] " + "inner join matter on matsysnbr = utmatter " + "inner join employee on empsysnbr = UTTkpr " + "where UTID in (" + IDs + ")"; DialogResult result = MessageBox.Show("There are records that could not be updated because Code1" + "\r\n" + "was not a valid timekeeper. Would you like to see them?", "Post Report", MessageBoxButtons.YesNo, MessageBoxIcon.Question); if (result == System.Windows.Forms.DialogResult.Yes) { DataSet error = _jurisUtility.RecordsetFromSQL(SQL2); ReportDisplay rpds = new ReportDisplay(error); rpds.Show(); } } else { MessageBox.Show("The process had completed free of any issues", "Confirmation", MessageBoxButtons.OK, MessageBoxIcon.None); } }
private void listBoxCompanies_SelectedIndexChanged(object sender, EventArgs e) { if (_jurisUtility.DbOpen) { _jurisUtility.CloseDatabase(); } CompanyCode = "Company" + listBoxCompanies.SelectedValue; _jurisUtility.SetInstance(CompanyCode); JurisDbName = _jurisUtility.Company.DatabaseName; JBillsDbName = "JBills" + _jurisUtility.Company.Code; _jurisUtility.OpenDatabase(); if (_jurisUtility.DbOpen) { ///GetFieldLengths(); } listViewClient.Clear(); setColumns(); string SQLTkpr = "SELECT [CliSysNbr] ,right([CliCode], 8) as CliCode,[CliNickName] FROM [Client]"; DataSet myRSTkpr = _jurisUtility.RecordsetFromSQL(SQLTkpr); if (myRSTkpr.Tables[0].Rows.Count == 0) { MessageBox.Show("There are no open clients in this database", "Selection Error", MessageBoxButtons.OK, MessageBoxIcon.Stop); } else { foreach (DataRow dr in myRSTkpr.Tables[0].Rows) { string offsetClient = ""; if (dr["CliCode"].ToString().Trim().Length < 8) //ensure they are all same number of digits { int diff = 8 - dr["CliCode"].ToString().Trim().Length; for (int a = 0; a < diff; a++) { offsetClient = offsetClient + "0"; } } ListViewItem lvi = new ListViewItem(dr["CliSysNbr"].ToString()); lvi.SubItems.Add(offsetClient + dr["CliCode"].ToString().Trim()); lvi.SubItems.Add(dr["CliNickName"].ToString()); listViewClient.Items.Add(lvi); } } comboBoxBill.ClearItems(); myRSTkpr.Clear(); SQLTkpr = "select cast(EmpSysNbr as varchar) + ' ' + EmpName as employee from employee"; myRSTkpr = _jurisUtility.RecordsetFromSQL(SQLTkpr); if (myRSTkpr.Tables[0].Rows.Count == 0) { comboBoxBill.SelectedIndex = 0; } else { comboBoxBill.Items.Add("* All Employees"); foreach (DataRow dr in myRSTkpr.Tables[0].Rows) { comboBoxBill.Items.Add(dr["employee"].ToString()); allEmployeesBill = allEmployeesBill + dr["employee"].ToString().Split(' ')[0] + ","; } allEmployeesBill = allEmployeesBill.TrimEnd(','); } comboBoxOrig.ClearItems(); myRSTkpr.Clear(); SQLTkpr = "select cast(EmpSysNbr as varchar) + ' ' + EmpName as employee from employee"; myRSTkpr = _jurisUtility.RecordsetFromSQL(SQLTkpr); if (myRSTkpr.Tables[0].Rows.Count == 0) { comboBoxOrig.SelectedIndex = 0; } else { comboBoxOrig.Items.Add("* All Employees"); foreach (DataRow dr in myRSTkpr.Tables[0].Rows) { comboBoxOrig.Items.Add(dr["employee"].ToString()); allEmployeesOrig = allEmployeesOrig + dr["employee"].ToString().Split(' ')[0] + ","; } allEmployeesOrig = allEmployeesOrig.TrimEnd(','); } }
private void DoDaFix() { if (memos != null && memos.Count > 0) { Cursor.Current = Cursors.WaitCursor; toolStripStatusLabel.Text = "Creating Credit Memos..."; statusStrip.Refresh(); UpdateStatus("Creating Credit Memos...", 1, memos.Count + 1); Application.DoEvents(); string SQLC = "select max(case when spname='CurAcctPrdYear' then cast(spnbrvalue as varchar(4)) else '' end) as PrdYear, max(Case when spname = 'CurAcctPrdNbr' then case " + " when spnbrvalue<9 then '0' + cast(spnbrvalue as varchar(1)) else cast(spnbrvalue as varchar(2)) end else '' end) as PrdNbr," + "max(case when spname='CfgMiscOpts' then substring(sptxtvalue,14,1) else 0 end) as DOrder from sysparam"; DataSet myRSSysParm = _jurisUtility.RecordsetFromSQL(SQLC); DataTable dtSP = myRSSysParm.Tables[0]; if (dtSP.Rows.Count == 0) { MessageBox.Show("Incorrect SysParams"); } else { foreach (DataRow dr in dtSP.Rows) { PYear = dr["PrdYear"].ToString(); PNbr = dr["PrdNbr"].ToString(); DOrder = dr["DOrder"].ToString(); } } int counter = 1; foreach (CreditMemo cm in memos) { cm.LHID = CreateLedgerHistory(cm); toolStripStatusLabel.Text = "Processing Credit Memos..."; statusStrip.Refresh(); UpdateStatus("Processing Credit Memos...", counter + 1, memos.Count + 1); counter++; } } else { MessageBox.Show("There were no valid transactions to process - Credit Memo List = 0", "Selection Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } //DataTable d1 = (DataTable)dataGridView1.DataSource; Cursor.Current = Cursors.Default; toolStripStatusLabel.Text = "Utility Completed."; statusStrip.Refresh(); UpdateStatus("Utility Completed.", memos.Count + 1, memos.Count + 1); Application.DoEvents(); string cmt = Application.ProductName.ToString(); WriteLog("JPS - Credit Memo Utility"); MessageBox.Show("Credit Memo Creation Completed.", "Success", MessageBoxButtons.OK, MessageBoxIcon.None); }
public void processTimeEntries(string ID) { allTimes.Clear(); try { if (ds != null) { ds.Clear(); } //go through each status individually //due to the size and nature of the data, we will do them by EntryType (6, 7, 8, 9). We ignore all draft time (0-5) String SQL = "SELECT t.EntryID, dbo.jfn_FormatClientCode(CliCode) as clicode,dbo.jfn_FormatMatterCode(MatCode) as matcode " + " ,EntryDate ,e.empname as empName ,BillableFlag ,ActualHoursWork ,Amount, EntryStatus, " + " tbd.tbdid, ut.utid, pb.PBFUTBatch, pb.PBFUTRecNbr, bt.BTID " + " FROM TimeEntry t " + " inner join matter m on m.matsysnbr = t.MatterSysNbr " + " inner join client c on c.clisysnbr = ClientSysNbr " + " inner join employee e on e.empsysnbr = t.TimekeeperSysNbr " + " left outer join timeentrylink tel on tel.entryid = t.entryid " + " left outer join timebatchdetail tbd on tel.tbdid = tbd.tbdid " + " left outer join unbilledtime ut on ut.utid = tbd.tbdid " + " left outer join PreBillFeeItem pb on pb.PBFUTBatch = ut.utbatch and pb.PBFUTRecNbr = ut.UTRecNbr " + " left outer join billedtime bt on bt.btid = tbd.tbdid " + " where t.entrystatus = " + ID; ds = _jurisUtility.RecordsetFromSQL(SQL); foreach (DataRow dr in ds.Tables[0].Rows) { TimeEntry te = new TimeEntry(); te.amount = Convert.ToDecimal(dr["Amount"].ToString().Trim()); te.hours = Convert.ToDecimal(dr["ActualHoursWork"].ToString().Trim()); te.ClientNo = dr["clicode"].ToString().Trim(); te.MatterNo = dr["matcode"].ToString().Trim(); te.ID = Convert.ToInt32(dr["EntryID"].ToString().Trim()); te.Date = DateTime.Parse(dr["EntryDate"].ToString().Trim()).ToString("MM/dd/yyyy"); te.oldEntryStatus = Convert.ToInt32(dr["EntryStatus"].ToString().Trim()); if (!string.IsNullOrEmpty(dr["tbdid"].ToString().Trim())) { te.tbdid = Convert.ToInt32(dr["tbdid"].ToString().Trim()); } if (!string.IsNullOrEmpty(dr["utid"].ToString().Trim())) { te.utid = Convert.ToInt32(dr["utid"].ToString().Trim()); } if (!string.IsNullOrEmpty(dr["PBFUTBatch"].ToString().Trim())) { te.pbbatch = Convert.ToInt32(dr["PBFUTBatch"].ToString().Trim()); } if (!string.IsNullOrEmpty(dr["PBFUTRecNbr"].ToString().Trim())) { te.pbrec = Convert.ToInt32(dr["PBFUTRecNbr"].ToString().Trim()); } if (!string.IsNullOrEmpty(dr["BTID"].ToString().Trim())) { te.btid = Convert.ToInt32(dr["BTID"].ToString().Trim()); } if (dr["BillableFlag"].ToString().Trim() == "Y") { te.isBillable = true; } else { te.isBillable = false; } te.Timekeeper = dr["empName"].ToString().Trim(); te.explanation = ""; te.ExpCode = ""; te.quantity = 1; te.Summarize = false; te.newEntryStatus = -1; allTimes.Add(te); } ds.Clear(); } catch (Exception ex1) { message = ex1.Message + "\r\n" + ex1.InnerException; } }
private void DoDaFix() { // Enter your SQL code here // To run a T-SQL statement with no results, int RecordsAffected = _jurisUtility.ExecuteNonQueryCommand(0, SQL); // To get an ADODB.Recordset, ADODB.Recordset myRS = _jurisUtility.RecordsetFromSQL(SQL); string SQL = ""; DataSet batches; string items = ""; if (checkedListBox1.Items.Count > 0) //did they select at least one checkbox? { int total = checkedListBox1.Items.Count; for (int i = 0; i < (checkedListBox1.Items.Count); i++) { if (checkedListBox1.GetItemChecked(i)) { switch (i) { case 0: //cash receipt items = ""; batches = _jurisUtility.RecordsetFromSQL("select distinct crbbatchnbr from CashReceiptsBatch where crbreccount=0"); if (batches.Tables[0].Rows.Count != 0) { foreach (DataRow dr in batches.Tables[0].Rows) { items = items + dr["crbbatchnbr"].ToString() + ","; } items = items.TrimEnd(','); SQL = "delete from documenttree where dtdocclass=5300 and dtkeyL in (" + items + ") and dtdoctype = 'R'"; _jurisUtility.ExecuteNonQueryCommand(0, SQL); // SQL = "delete from CashReceiptsBatch where crbreccount=0"; // _jurisUtility.ExecuteNonQueryCommand(0, SQL); } UpdateStatus("Updating database...", i, total); batches.Clear(); break; case 1: //Check items = ""; batches = _jurisUtility.RecordsetFromSQL("select distinct cbbatchnbr from CheckBatch where cbreccount=0"); if (batches.Tables[0].Rows.Count != 0) { foreach (DataRow dr in batches.Tables[0].Rows) { items = items + dr["cbbatchnbr"].ToString() + ","; } items = items.TrimEnd(','); SQL = "delete from documenttree where dtdocclass=7300 and dtkeyL in (" + items + ") and dtdoctype = 'R'"; _jurisUtility.ExecuteNonQueryCommand(0, SQL); // SQL = "delete from CheckBatch where cbreccount=0"; // _jurisUtility.ExecuteNonQueryCommand(0, SQL); } UpdateStatus("Updating database...", i, total); batches.Clear(); break; case 2: //Credit Memo items = ""; batches = _jurisUtility.RecordsetFromSQL("select distinct cmbbatchnbr from CreditMemoBatch where cmbreccount=0"); if (batches.Tables[0].Rows.Count != 0) { foreach (DataRow dr in batches.Tables[0].Rows) { items = items + dr["cmbbatchnbr"].ToString() + ","; } items = items.TrimEnd(','); SQL = "delete from documenttree where dtdocclass=5200 and dtkeyL in (" + items + ") and dtdoctype = 'R'"; _jurisUtility.ExecuteNonQueryCommand(0, SQL); // SQL = "delete from CreditMemoBatch where cmbreccount=0"; //_jurisUtility.ExecuteNonQueryCommand(0, SQL); } UpdateStatus("Updating database...", i, total); batches.Clear(); break; case 3: //Expense items = ""; batches = _jurisUtility.RecordsetFromSQL("select distinct ebbatchnbr from ExpenseBatch where ebreccount=0"); if (batches.Tables[0].Rows.Count != 0) { foreach (DataRow dr in batches.Tables[0].Rows) { items = items + dr["ebbatchnbr"].ToString() + ","; } items = items.TrimEnd(','); SQL = "delete from documenttree where dtdocclass=5000 and dtkeyL in (" + items + ") and dtdoctype = 'R'"; _jurisUtility.ExecuteNonQueryCommand(0, SQL); // SQL = "delete from ExpenseBatch where ebreccount=0"; // _jurisUtility.ExecuteNonQueryCommand(0, SQL); } UpdateStatus("Updating database...", i, total); batches.Clear(); break; case 4: //Journal Entry items = ""; _jurisUtility.ExecuteNonQuery(0, " update ARPostBatch set ARPJEBatchNbr = null where arpbatchnbr in (select distinct arpbatchnbr from arpostbatch where arpjebatchnbr in (select jebbatchnbr from jebatch where jebreccount=0) and arpbatchnbr in (select arpdbatch from arpostdetail inner join armatalloc on armbillnbr=arpdbillnbr group by arpdbatch having sum(armfeebld + armcshexpbld + armncshexpbld + ARMSurchgBld + armtax1bld + armtax2bld + armtax3bld + armintbld) = 0))"); batches = _jurisUtility.RecordsetFromSQL("select distinct jebbatchnbr from JEBatch where jebreccount=0 and jebbatchnbr not in (select arpjebatchnbr from arpostbatch)"); //if rec count == 0 then set ARPostBatch in ARPJEBatchNbr to null if (batches.Tables[0].Rows.Count != 0) { foreach (DataRow dr in batches.Tables[0].Rows) { items = items + dr["jebbatchnbr"].ToString() + ","; } items = items.TrimEnd(','); SQL = "delete from documenttree where dtdocclass=4700 and dtkeyL in (" + items + ") and dtdoctype = 'R'"; _jurisUtility.ExecuteNonQueryCommand(0, SQL); // SQL = "delete from JEBatch where jebreccount=0"; // _jurisUtility.ExecuteNonQueryCommand(0, SQL); } UpdateStatus("Updating database...", i, total); batches.Clear(); break; case 5: //Manual Bill items = ""; batches = _jurisUtility.RecordsetFromSQL("select distinct mbbbatchnbr from ManualBillBatch where mbbreccount=0"); if (batches.Tables[0].Rows.Count != 0) { foreach (DataRow dr in batches.Tables[0].Rows) { items = items + dr["mbbbatchnbr"].ToString() + ","; } items = items.TrimEnd(','); SQL = "delete from documenttree where dtdocclass=5100 and dtkeyL in (" + items + ") and dtdoctype = 'R'"; _jurisUtility.ExecuteNonQueryCommand(0, SQL); // SQL = "delete from ManualBillBatch where mbbreccount=0"; //_jurisUtility.ExecuteNonQueryCommand(0, SQL); } UpdateStatus("Updating database...", i, total); batches.Clear(); break; case 6: //Time Batch items = ""; batches = _jurisUtility.RecordsetFromSQL("select distinct tbbatchnbr from TimeBatch where tbreccount=0"); if (batches.Tables[0].Rows.Count != 0) { foreach (DataRow dr in batches.Tables[0].Rows) { items = items + dr["tbbatchnbr"].ToString() + ","; } items = items.TrimEnd(','); // SQL = "delete from TimeBatchImportError where TBIEBatchNbr in (" + items + ")"; // _jurisUtility.ExecuteNonQueryCommand(0, SQL); // SQL = "delete from TimeBatchDetail where TBDBatch in (" + items + ")"; // _jurisUtility.ExecuteNonQueryCommand(0, SQL); SQL = "delete from documenttree where dtdocclass=4900 and dtkeyL in (" + items + ") and dtdoctype = 'R'"; _jurisUtility.ExecuteNonQueryCommand(0, SQL); // SQL = "delete from TimeBatch where tbreccount=0"; // _jurisUtility.ExecuteNonQueryCommand(0, SQL); } UpdateStatus("Updating database...", i, total); batches.Clear(); break; case 7: //Trust Adjustment items = ""; batches = _jurisUtility.RecordsetFromSQL("select distinct tabbatchnbr from TrAdjBatch where tabreccount=0"); if (batches.Tables[0].Rows.Count != 0) { foreach (DataRow dr in batches.Tables[0].Rows) { items = items + dr["tabbatchnbr"].ToString() + ","; } items = items.TrimEnd(','); SQL = "delete from documenttree where dtdocclass=7500 and dtkeyL in (" + items + ") and dtdoctype = 'R'"; _jurisUtility.ExecuteNonQueryCommand(0, SQL); // SQL = "delete from TrAdjBatch where tabreccount=0"; // _jurisUtility.ExecuteNonQueryCommand(0, SQL); } UpdateStatus("Updating database...", i, total); batches.Clear(); break; case 8: //Voucher items = ""; batches = _jurisUtility.RecordsetFromSQL("select distinct vbbatchnbr from VoucherBatch where vbreccount=0"); if (batches.Tables[0].Rows.Count != 0) { foreach (DataRow dr in batches.Tables[0].Rows) { items = items + dr["vbbatchnbr"].ToString() + ","; } items = items.TrimEnd(','); SQL = "delete from documenttree where dtdocclass=7200 and dtkeyL in (" + items + ") and dtdoctype = 'R'"; _jurisUtility.ExecuteNonQueryCommand(0, SQL); // SQL = "delete from VoucherBatch where vbreccount=0"; //_jurisUtility.ExecuteNonQueryCommand(0, SQL); } UpdateStatus("Updating database...", i, total); batches.Clear(); break; } } } UpdateStatus("Update Complete", total, total); MessageBox.Show("THe process is complete!"); } else { MessageBox.Show("At least one checkbox needs to be selected"); } }
private void DoDaFix() { UpdateStatus("Creating New Accounts...", 0, 0); toolStripStatusLabel.Text = "Creating New Accounts..."; Cursor.Current = Cursors.WaitCursor; statusStrip.Refresh(); //Create New Accounts string SQL = @"Insert into ChartofAccounts(chtsysnbr, chtmainacct, chtsubacct, chtdesc, chtsubtotlevel, chtfinstmttype, chtsaftype, chtparencode, chtcomprescode, chtcashflowtype, chtsubacct1, chtsubacct2, chtsubacct3, chtsubacct4, chtsubacct5, chtsubacct6, chtsubacct7, chtsubacct8) select(select spnbrvalue from sysparam where spname = 'LastSysNbrChart') + rank() over(order by newacct) as Chtsysnbr, right('000000000' + cast(newacct as varchar(8)), 8),chtsubacct, left(min(newdesc), 30) as NewDesc, chtsubtotlevel, chtfinstmttype, chtsaftype, min(chtparencode), chtcomprescode, min(chtcashflowtype), (select isnull(min(coas1id),0) from coasubaccount1), 0, 0, 0, 0, 0, 0, 0 from #tblcoa inner join chartofaccounts on oldsysnbr = chtsysnbr where newsysnbr is null group by newacct, chtsubacct, chtsubtotlevel, chtfinstmttype,chtsaftype, chtcomprescode"; _jurisUtility.ExecuteNonQueryCommand(0, SQL); SQL = @"update sysparam set spnbrvalue = (select max(chtsysnbr) from chartofaccounts) where spname = 'LastSysNbrChart'"; _jurisUtility.ExecuteNonQueryCommand(0, SQL); SQL = @"Insert into documenttree(dtdocid, dtsystemcreated, dtdocclass, dtdoctype, dtparentid, dttitle, dtkeyl) select(select spnbrvalue from sysparam where spname = 'LastSysNbrDocTree') + rank() over(order by chtsysnbr) as Chtsysnbr,'Y','2100','R','9', chtdesc, chtsysnbr from chartofaccounts where chtsysnbr not in (select dtkeyl from documenttree where dtdocclass = 2100 and dtdoctype = 'R' and dtparentid = 9)"; _jurisUtility.ExecuteNonQueryCommand(0, SQL); SQL = @"update sysparam set spnbrvalue = (select max(dtdocid) from documenttree) where spname = 'LastSysNbrDocTree'"; _jurisUtility.ExecuteNonQueryCommand(0, SQL); SQL = @"Update #tblcoa set newsysnbr = chtsysnbr from chartofaccounts where chtmainacct = right('000000000' + cast(newacct as varchar(8)), 8) and newsysnbr is null"; _jurisUtility.ExecuteNonQueryCommand(0, SQL); //Update DataGrid With New Accounts string sql2 = "select * from #tblcoa order by newacct"; DataSet coa = _jurisUtility.ExecuteSqlCommand(0, sql2); dataGridView1.DataSource = coa.Tables[0]; UpdateStatus("Updating Chart of Accounts...", 1, 25); toolStripStatusLabel.Text = "Updating Chart of Accounts..."; Cursor.Current = Cursors.WaitCursor; statusStrip.Refresh(); //Update New Accounts and Accounts that will not have completely new data string sql = @"update jebatchdetail set jebdaccount=newsysnbr from #tblcoa where jebdaccount=oldsysnbr and statustype in (1,2,4)"; _jurisUtility.ExecuteNonQueryCommand(0, sql); sql = @"update voucherbatchdetail set VBDDiscAcct=newsysnbr from #tblcoa where VBDDiscAcct=oldsysnbr and statustype in (1,2,4)"; _jurisUtility.ExecuteNonQueryCommand(0, sql); UpdateStatus("Updating Chart of Accounts...", 2, 25); toolStripStatusLabel.Text = "Updating Chart of Accounts..."; Cursor.Current = Cursors.WaitCursor; statusStrip.Refresh(); sql = @"update voucherbatchgldist set VBGGLAcct=newsysnbr from #tblcoa where VBGGLAcct=oldsysnbr and statustype in (1,2,4)"; _jurisUtility.ExecuteNonQueryCommand(0, sql); sql = @"update vouchergldist set VGLGLAcct=newsysnbr from #tblcoa where VGLGLAcct=oldsysnbr and statustype in (1,2,4)"; _jurisUtility.ExecuteNonQueryCommand(0, sql); UpdateStatus("Updating Chart of Accounts...", 3, 25); toolStripStatusLabel.Text = "Updating Chart of Accounts..."; Cursor.Current = Cursors.WaitCursor; statusStrip.Refresh(); sql = @"update journalentry set jeaccount=newsysnbr from #tblcoa where jeaccount=oldsysnbr and statustype in (1,2,4)"; _jurisUtility.ExecuteNonQueryCommand(0, sql); sql = @"update jetemplatedetail set jetdaccount=newsysnbr from #tblcoa where jetdaccount=oldsysnbr and statustype in (1,2,4)"; _jurisUtility.ExecuteNonQueryCommand(0, sql); sql = @"update vendor set VenDiscAcct=newsysnbr from #tblcoa where VenDiscAcct=oldsysnbr and statustype in (1,2,4)"; _jurisUtility.ExecuteNonQueryCommand(0, sql); sql = @"update ChartNote set ChnAccount=newsysnbr from #tblcoa where ChnAccount=oldsysnbr and statustype in (1,2,4)"; _jurisUtility.ExecuteNonQueryCommand(0, sql); sql = @"update VchTemplate set VTVchDiscountAccount=newsysnbr from #tblcoa where VTVchDiscountAccount=oldsysnbr and statustype in (1,2,4)"; _jurisUtility.ExecuteNonQueryCommand(0, sql); sql = @"update VchTemplateGLDist set VTGLDAccount=newsysnbr from #tblcoa where VTGLDAccount=oldsysnbr and statustype in (1,2,4)"; _jurisUtility.ExecuteNonQueryCommand(0, sql); UpdateStatus("Updating Chart of Accounts...", 4, 25); toolStripStatusLabel.Text = "Updating Chart of Accounts..."; Cursor.Current = Cursors.WaitCursor; statusStrip.Refresh(); sql = @"update vendor set VenDefaultDistAcct=newsysnbr from #tblcoa where VenDefaultDistAcct=oldsysnbr and statustype in (1,2,4)"; _jurisUtility.ExecuteNonQueryCommand(0, sql); sql = @"update apaccount set apaglacct=newsysnbr from #tblcoa where apaglacct=oldsysnbr and statustype in (1,2,4)"; _jurisUtility.ExecuteNonQueryCommand(0, sql); sql = @"update practclassglacct set pgaacct=newsysnbr from #tblcoa where pgaacct=oldsysnbr and statustype in (1,2,4)"; _jurisUtility.ExecuteNonQueryCommand(0, sql); sql = @"update OfficeGLAccount set ogaacct=newsysnbr from #tblcoa where ogaacct=oldsysnbr and statustype in (1,2,4)"; _jurisUtility.ExecuteNonQueryCommand(0, sql); sql = @"update voucher set VchDiscAcct=newsysnbr from #tblcoa where VchDiscAcct=oldsysnbr and statustype in (1,2,4)"; _jurisUtility.ExecuteNonQueryCommand(0, sql); UpdateStatus("Updating Chart of Accounts...", 5, 25); toolStripStatusLabel.Text = "Updating Chart of Accounts..."; Cursor.Current = Cursors.WaitCursor; statusStrip.Refresh(); sql = @"update CRNonCliAlloc set CRNCreditAccount=newsysnbr from #tblcoa where CRNCreditAccount=oldsysnbr and statustype in (1,2,4)"; _jurisUtility.ExecuteNonQueryCommand(0, sql); sql = @"update ExpCodeGLAcct set ECGAAcct=newsysnbr from #tblcoa where ECGAAcct=oldsysnbr and statustype in (1,2,4)"; _jurisUtility.ExecuteNonQueryCommand(0, sql); UpdateStatus("Updating Chart of Accounts...", 6, 25); toolStripStatusLabel.Text = "Updating Chart of Accounts..."; Cursor.Current = Cursors.WaitCursor; statusStrip.Refresh(); sql = @"update ExpDetailDist set EDDAccount=newsysnbr from #tblcoa where EDDAccount=oldsysnbr and statustype in (1,2,4)"; _jurisUtility.ExecuteNonQueryCommand(0, sql); sql = @"update BkAcctGLAcct set BGAAcct=newsysnbr from #tblcoa where BGAAcct=oldsysnbr and statustype in (1,2,4)"; _jurisUtility.ExecuteNonQueryCommand(0, sql); UpdateStatus("Updating Chart of Accounts...", 7, 25); toolStripStatusLabel.Text = "Updating Chart of Accounts..."; Cursor.Current = Cursors.WaitCursor; statusStrip.Refresh(); sql = @"update TimeDetailDist set TDDAccount=newsysnbr from #tblcoa where TDDAccount=oldsysnbr and statustype in (1,2,4)"; _jurisUtility.ExecuteNonQueryCommand(0, sql); sql = @"update EmpGLAcct set EGAAcct=newsysnbr from #tblcoa where EGAAcct=oldsysnbr and statustype in (1,2,4)"; _jurisUtility.ExecuteNonQueryCommand(0, sql); UpdateStatus("Updating Chart of Accounts...", 8, 25); toolStripStatusLabel.Text = "Updating Chart of Accounts..."; Cursor.Current = Cursors.WaitCursor; statusStrip.Refresh(); sql = @"update FSLayoutItem set FSLIChtSysNbr=newsysnbr from #tblcoa where FSLIChtSysNbr=oldsysnbr and statustype in (1,2,4)"; _jurisUtility.ExecuteNonQueryCommand(0, sql); //Update Accounts that are moving to an existing account that is also moving sql = @"update jebatchdetail set jebdaccount = newsysnbr from #tblcoa where jebdaccount=oldsysnbr and statustype in (3)"; _jurisUtility.ExecuteNonQueryCommand(0, sql); sql = @"update jetemplatedetail set jetdaccount=newsysnbr from #tblcoa where jetdaccount=oldsysnbr and statustype in (3)"; _jurisUtility.ExecuteNonQueryCommand(0, sql); UpdateStatus("Updating Chart of Accounts...", 9, 25); toolStripStatusLabel.Text = "Updating Chart of Accounts..."; Cursor.Current = Cursors.WaitCursor; statusStrip.Refresh(); sql = @"update voucherbatchdetail set VBDDiscAcct=newsysnbr from #tblcoa where VBDDiscAcct=oldsysnbr and statustype in (3)"; _jurisUtility.ExecuteNonQueryCommand(0, sql); sql = @"update voucherbatchgldist set VBGGLAcct=newsysnbr from #tblcoa where VBGGLAcct=oldsysnbr and statustype in (3)"; _jurisUtility.ExecuteNonQueryCommand(0, sql); sql = @"update ChartNote set ChnAccount=newsysnbr from #tblcoa where ChnAccount=oldsysnbr and statustype in (3)"; _jurisUtility.ExecuteNonQueryCommand(0, sql); sql = @"update VchTemplate set VTVchDiscountAccount=newsysnbr from #tblcoa where VTVchDiscountAccount=oldsysnbr and statustype in (3)"; _jurisUtility.ExecuteNonQueryCommand(0, sql); sql = @"update VchTemplateGLDist set VTGLDAccount=newsysnbr from #tblcoa where VTGLDAccount=oldsysnbr and statustype in (3)"; _jurisUtility.ExecuteNonQueryCommand(0, sql); UpdateStatus("Updating Chart of Accounts...", 10, 25); toolStripStatusLabel.Text = "Updating Chart of Accounts..."; Cursor.Current = Cursors.WaitCursor; statusStrip.Refresh(); sql = @"update apaccount set apaglacct=newsysnbr from #tblcoa where apaglacct=oldsysnbr and statustype in (3)"; _jurisUtility.ExecuteNonQueryCommand(0, sql); sql = @"update vouchergldist set VGLGLAcct=newsysnbr from #tblcoa where VGLGLAcct=oldsysnbr and statustype in (3)"; _jurisUtility.ExecuteNonQueryCommand(0, sql); sql = @"update journalentry set jeaccount=newsysnbr from #tblcoa where jeaccount=oldsysnbr and statustype in (3)"; _jurisUtility.ExecuteNonQueryCommand(0, sql); UpdateStatus("Updating Chart of Accounts...", 11, 25); toolStripStatusLabel.Text = "Updating Chart of Accounts..."; Cursor.Current = Cursors.WaitCursor; statusStrip.Refresh(); sql = @"update vendor set VenDiscAcct=newsysnbr from #tblcoa where VenDiscAcct=oldsysnbr and statustype in (3)"; _jurisUtility.ExecuteNonQueryCommand(0, sql); sql = @"update practclassglacct set pgaacct=newsysnbr from #tblcoa where pgaacct=oldsysnbr and statustype in (3)"; _jurisUtility.ExecuteNonQueryCommand(0, sql); sql = @"update OfficeGLAccount set ogaacct=newsysnbr from #tblcoa where ogaacct=oldsysnbr and statustype in (3)"; _jurisUtility.ExecuteNonQueryCommand(0, sql); sql = @"update vendor set VenDefaultDistAcct=newsysnbr from #tblcoa where VenDefaultDistAcct=oldsysnbr and statustype in (3)"; _jurisUtility.ExecuteNonQueryCommand(0, sql); UpdateStatus("Updating Chart of Accounts...", 12, 25); toolStripStatusLabel.Text = "Updating Chart of Accounts..."; Cursor.Current = Cursors.WaitCursor; statusStrip.Refresh(); sql = @"update voucher set VchDiscAcct=newsysnbr from #tblcoa where VchDiscAcct=oldsysnbr and statustype in (3)"; _jurisUtility.ExecuteNonQueryCommand(0, sql); sql = @"update CRNonCliAlloc set CRNCreditAccount=newsysnbr from #tblcoa where CRNCreditAccount=oldsysnbr and statustype in (3)"; _jurisUtility.ExecuteNonQueryCommand(0, sql); UpdateStatus("Updating Chart of Accounts...", 13, 25); toolStripStatusLabel.Text = "Updating Chart of Accounts..."; Cursor.Current = Cursors.WaitCursor; statusStrip.Refresh(); sql = @"update ExpCodeGLAcct set ECGAAcct=newsysnbr from #tblcoa where ECGAAcct=oldsysnbr and statustype in (3)"; _jurisUtility.ExecuteNonQueryCommand(0, sql); sql = "select * from expdetaildist inner join #tblcoa on EDDAccount=oldsysnbr order by eddbatch, EDDRecNbr"; DataSet dd = _jurisUtility.RecordsetFromSQL(sql); var result = dd.Tables[0] .AsEnumerable() .Where(myRow => myRow.Field <int>("RowNo") == 1); sql = @"update ExpDetailDist set EDDAccount=newsysnbr from #tblcoa where EDDAccount=oldsysnbr and statustype in (3)"; //select min(ogaacct) from officeglaccount //where ogatype=210 _jurisUtility.ExecuteNonQueryCommand(0, sql); UpdateStatus("Updating Chart of Accounts...", 14, 25); toolStripStatusLabel.Text = "Updating Chart of Accounts..."; Cursor.Current = Cursors.WaitCursor; statusStrip.Refresh(); sql = @"update BkAcctGLAcct set BGAAcct=newsysnbr from #tblcoa where BGAAcct=oldsysnbr and statustype in (3)"; _jurisUtility.ExecuteNonQueryCommand(0, sql); sql = @"update TimeDetailDist set TDDAccount=newsysnbr from #tblcoa where TDDAccount=oldsysnbr and statustype in (3)"; _jurisUtility.ExecuteNonQueryCommand(0, sql); UpdateStatus("Updating Chart of Accounts...", 15, 25); toolStripStatusLabel.Text = "Updating Chart of Accounts..."; Cursor.Current = Cursors.WaitCursor; statusStrip.Refresh(); sql = @"update EmpGLAcct set EGAAcct=newsysnbr from #tblcoa where EGAAcct=oldsysnbr and statustype in (3)"; _jurisUtility.ExecuteNonQueryCommand(0, sql); sql = @"update FSLayoutItem set FSLIChtSysNbr=newsysnbr from #tblcoa where FSLIChtSysNbr=oldsysnbr and statustype in (3)"; _jurisUtility.ExecuteNonQueryCommand(0, sql); UpdateStatus("Updating Chart Budgets...", 20, 25); toolStripStatusLabel.Text = "Updating Chart Budgets..."; Cursor.Current = Cursors.WaitCursor; statusStrip.Refresh(); string SqLCB = @"select newsysnbr, chbperiod as prd, chbprdyear as yr, sum(chbnetchange) as NC, sum(chbbudget) as Bud into #tblcb from chartbudget inner join #tblcoa on oldsysnbr=chbaccount group by newsysnbr, chbperiod, chbprdyear"; _jurisUtility.ExecuteNonQueryCommand(0, SqLCB); sql = "Delete from chartbudget"; _jurisUtility.ExecuteNonQueryCommand(0, sql); sql = @"Insert into ChartBudget(chbaccount, chbprdyear, chbperiod, chbnetchange, chbbudget) Select newsysnbr, yr, prd, nc, bud from #tblcb"; _jurisUtility.ExecuteNonQueryCommand(0, sql); sql = "drop table #tblcb"; _jurisUtility.ExecuteNonQueryCommand(0, sql); UpdateStatus("Account Clean Up...", 23, 25); toolStripStatusLabel.Text = "Account Clean Up..."; Cursor.Current = Cursors.WaitCursor; statusStrip.Refresh(); sql = @"update sysparam set sptxtvalue=cast(newsysnbr as varchar(10)) + ',0' from #tblcoa where spname='DefEmpGlAcct' and sptxtvalue=cast(oldsysnbr as varchar(10)) + ',0'"; _jurisUtility.ExecuteNonQueryCommand(0, sql); sql = @"update sysparam set sptxtvalue=cast(newsysnbr as varchar(10)) + ',0' from #tblcoa where spname='DefExpGlAcct' and sptxtvalue=cast(oldsysnbr as varchar(10)) + ',0'"; _jurisUtility.ExecuteNonQueryCommand(0, sql); sql = @"update sysparam set sptxtvalue=cast(newsysnbr as varchar(10)) + ',0' from #tblcoa where spname='DefVenDiscAcct' and sptxtvalue=cast(oldsysnbr as varchar(10)) + ',0'"; _jurisUtility.ExecuteNonQueryCommand(0, sql); sql = @"delete from chartofaccountchartcategory where chtsysnbr not in (Select newsysnbr from #tblcoa)"; _jurisUtility.ExecuteNonQueryCommand(0, sql); sql = @"delete from chartofaccounts where chtsysnbr not in (select newsysnbr from #tblcoa)"; _jurisUtility.ExecuteNonQueryCommand(0, sql); sql = @"update chartofaccounts set chtsubacct1=0,chtdesc=left(newdesc,30) from #tblcoa where newsysnbr=chtsysnbr"; _jurisUtility.ExecuteNonQueryCommand(0, sql); sql = @"delete from documenttree where dtdocclass='2100' and dtdoctype='R' and dtparentid=9 and dtkeyl not in (select chtsysnbr from chartofaccounts)"; _jurisUtility.ExecuteNonQueryCommand(0, sql); sql = "drop table #tblcoa"; _jurisUtility.ExecuteNonQueryCommand(0, sql); toolStripStatusLabel.Text = "Complete."; Cursor.Current = Cursors.Default; statusStrip.Refresh(); UpdateStatus("Complete...", 25, 25); WriteLog("GL CROSSWALK CHART OF ACCOUNTS UPDATE " + DateTime.Now.ToString("MM/dd/yyyy")); MessageBox.Show("The process completed successfully", "Finished", MessageBoxButtons.OK, MessageBoxIcon.None); }