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