//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);
}
}
