//returns false if EID exists in timeentry, timebatchdetail and unbilledtime table as well as the taskcode existing in taskcode, otherwise returns true //which means at least one of these tests were failed and they need to be fixed private bool anyErrorsInEidOrTaskCode(Row currRow) { DataSet ds1; //timeentry string SQL = "Select * from TimeEntry where entryid=" + currRow.ID.Trim(); ds1 = _jurisUtility.ExecuteSqlCommand(0, SQL); if (ds1.Tables[0].Rows.Count == 0) { currRow.error = "The EID is not in the TimeEntry Table"; return(true); } ds1.Clear(); //timebatchdetail SQL = "Select * from Timebatchdetail where tbdid=(select tbdid from timeentrylink where entryid=" + currRow.ID.Trim() + ")"; ds1 = _jurisUtility.ExecuteSqlCommand(0, SQL); if (ds1.Tables[0].Rows.Count == 0) { currRow.error = "The EID is not in the TimeBatchDetail Table"; return(true); } ds1.Clear(); //unbilledtime SQL = "Select * from UnbilledTime where utid=(select tbdid from timeentrylink where entryid=" + currRow.ID.Trim() + ")"; ds1 = _jurisUtility.ExecuteSqlCommand(0, SQL); if (ds1.Tables[0].Rows.Count == 0) { currRow.error = "The EID is not in the UnbilledTime Table"; return(true); } ds1.Clear(); //taskcode SQL = "Select * from taskcode where TaskCdCode = '" + currRow.newTask.Trim() + "'"; ds1 = _jurisUtility.ExecuteSqlCommand(0, SQL); if (ds1.Tables[0].Rows.Count == 0) { currRow.error = "The new task code is not valid"; return(true); } ds1.Clear(); //only reachable if all of these sql queries return at least one row (they should all only return 1 row btw) :) return(false); }
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); if (!string.IsNullOrEmpty(venKeep) && !string.IsNullOrEmpty(venDelete)) { DialogResult rs = MessageBox.Show("This will merge all transactions and data from vendor: " + venDelete + " into" + "\r\n" + "vendor: " + venKeep + ". Vendor: " + venDelete + " will be deleted. Continue?", "Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Question); if (rs == System.Windows.Forms.DialogResult.Yes) { DataSet ds1 = new DataSet(); String SQL = ""; //we need ot find those ids in the list based on the formatted code var id = venList.First(a => a.code == venKeep); var id1 = venList.First(a => a.code == venDelete); string venKeepID = id.ID; string venDeleteID = id1.ID; if (!radioButtonKeep.Checked) //if they chose to force the separate check setting { if (!string.IsNullOrEmpty(sepChecks)) { SQL = "update vendor set " + sepChecks + " where vensysnbr = " + venKeepID; _jurisUtility.ExecuteNonQueryCommand(0, SQL); } } SQL = "update voucher set vchvendor=" + venKeepID + " where vchvendor=" + venDeleteID; _jurisUtility.ExecuteNonQueryCommand(0, SQL); UpdateStatus("Updated Vouchers.", 1, 9); SQL = "update checkregister set ckregvend=" + venKeepID + " where ckregvend=" + venDeleteID; _jurisUtility.ExecuteNonQueryCommand(0, SQL); UpdateStatus("Updated Check Register.", 2, 9); SQL = "update voucherbatchdetail set vbdvendor=" + venKeepID + " where vbdvendor=" + venDeleteID; _jurisUtility.ExecuteNonQueryCommand(0, SQL); UpdateStatus("Updated Voucher Batch Detail.", 3, 9); SQL = "update vennote set vnvendor=" + venKeepID + " where vnvendor=" + venDeleteID; _jurisUtility.ExecuteNonQueryCommand(0, SQL); UpdateStatus("Updated Vendor Note.", 4, 9); SQL = "update matdisbhistory set mdhvendor=" + venKeepID + "where mdhvendor=" + venDeleteID; _jurisUtility.ExecuteNonQueryCommand(0, SQL); UpdateStatus("Updated Mat Disb History.", 5, 9); //ven1099 SQL = "SELECT [V99Year] ,cast(sum([V99AmountPaid]) as decimal(20,2)) as paid ,cast(sum(V99SupplementAmount) as decimal(20,2)) as supp FROM [Ven1099] where V99Vendor = " + venDeleteID + " or V99Vendor = " + venKeepID + " group by V99Year"; ds1 = _jurisUtility.ExecuteSqlCommand(0, SQL); foreach (DataRow r in ds1.Tables[0].Rows) { SQL = "update Ven1099 set V99AmountPaid = " + r[1].ToString() + ", V99SupplementAmount = " + r[2].ToString() + " where V99Year = " + r[0].ToString() + " and V99Vendor = " + venKeepID; _jurisUtility.ExecuteNonQueryCommand(0, SQL); } //Update ven1099 settings based of selection if (radioButton4.Checked == true) { SQL = "update vendor set ven1099Box=del1099 from (select ven1099Box as del1099, vensysnbr as OldVen from vendor where vensysnbr=" + venDeleteID + ")DV where vensysnbr= " + venKeepID; _jurisUtility.ExecuteNonQueryCommand(0, SQL); } //vensumbyprd SQL = "SELECT [VSPPrdYear],[VSPPrdNbr],cast(sum([VSPVouchers]) as decimal(20,2)) as vouch,cast(sum([VSPPayments]) as decimal(20,2)) as pymt,cast(sum([VSPDiscountsTaken]) as decimal(20,2)) as disc FROM [VenSumByPrd] where VSPVendor = " + venDeleteID + " or vspvendor=" + venKeepID + " group by VSPPrdYear, VSPPrdNbr order by VSPPrdYear, VSPPrdNbr"; ds1.Clear(); ds1 = _jurisUtility.ExecuteSqlCommand(0, SQL); foreach (DataRow r in ds1.Tables[0].Rows) { SQL = "SELECT vspprdyear, vspprdnbr, vspvendor from VenSumByPrd where VSPPrdYear = " + r["VSPPrdYear"].ToString() + " and VSPVendor = " + venKeepID + " and VSPPrdNbr = " + r["VSPPrdNbr"].ToString(); DataSet d2 = _jurisUtility.ExecuteSqlCommand(0, SQL); if (d2.Tables[0].Rows.Count == 0) { SQL = "Insert into VenSumbyPrd(vspvendor, vspprdyear, vspprdnbr, vspvouchers, vsppayments, vspdiscountstaken) values(" + venKeepID + "," + r["VSPPrdYear"].ToString() + "," + r["VSPPrdNbr"].ToString() + "," + r["vouch"].ToString() + "," + r["pymt"].ToString() + "," + r["disc"].ToString() + ")"; _jurisUtility.ExecuteNonQueryCommand(0, SQL); } else { SQL = "update VenSumByPrd set VSPVouchers = " + r["vouch"].ToString() + ", VSPPayments = " + r["pymt"].ToString() + ", VSPDiscountsTaken = " + r["disc"].ToString() + " where VSPPrdYear = " + r["VSPPrdYear"].ToString() + " and VSPVendor = " + venKeepID + " and VSPPrdNbr = " + r["VSPPrdNbr"].ToString(); _jurisUtility.ExecuteNonQueryCommand(0, SQL); } } UpdateStatus("Updated 1099 and Vendor Sum By Period.", 6, 9); ds1.Clear(); //delete records SQL = "delete from ven1099 where V99Vendor = " + venDeleteID; _jurisUtility.ExecuteNonQueryCommand(0, SQL); SQL = "delete from VenSumbyprd where VSPVendor = " + venDeleteID; _jurisUtility.ExecuteNonQueryCommand(0, SQL); SQL = "delete from vendor where VenSysNbr = " + venDeleteID; _jurisUtility.ExecuteNonQueryCommand(0, SQL); UpdateStatus("Removing old records.", 7, 9); //updte doctree SQL = "Delete from documenttree where dtdocclass=7000 and dtkeyl=" + venDeleteID; _jurisUtility.ExecuteNonQueryCommand(0, SQL); UpdateStatus("Updated DocTree.", 8, 9); UpdateStatus("Process Complete.", 9, 9); venDelete = ""; venKeep = ""; venList.Clear(); cbKeep.SelectedIndex = -1; cbDelete.SelectedIndex = -1; MessageBox.Show("The process is complete", "Confirmation", MessageBoxButtons.OK, MessageBoxIcon.None); try { System.Environment.Exit(1); } catch (Exception ex11) { this.Close(); } } } else { MessageBox.Show("Please select a vendor from both drop downs", "Selection error", MessageBoxButtons.OK, MessageBoxIcon.Error); } }
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); }
private void GetFieldLengths() { try { var ds = _jurisUtility.ExecuteSqlCommand(0, Resources.GetFieldLengths); var row = ds.Tables["Table"].Rows[0]; var regex = new Regex(@"\d+"); var fldClient = row["FldClient"] as string; var fldMatter = row["FldMatter"] as string; FldClient = int.Parse(regex.Match(fldClient ?? "0").Value); FldMatter = int.Parse(regex.Match(fldMatter ?? "0").Value); } catch (Exception exception) { MessageBox.Show(this, exception.Message, @"Error getting field lengths", MessageBoxButtons.OK, MessageBoxIcon.Error); } }