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 = "update matter set matbillingfield06=matdescription";
_jurisUtility.ExecuteNonQueryCommand(0, SQL);
UpdateStatus("Copying description to MatBillingField06.", 1, 3);
SQL = "update matter set matdescription=''";
_jurisUtility.ExecuteNonQueryCommand(0, SQL);
UpdateStatus("Clearing description.", 2, 3);
SQL = "update matter set matdescription=Conflictnote from (select mnmatter, " +
"case when charindex(char(13),cast(mnnotetext as varchar(8000)))=0 then cast(mnnotetext as varchar(8000)) else " +
"left(cast(mnnotetext as varchar(8000)), charindex(char(13),cast(mnnotetext as varchar(8000))) -1) end as ConflictNote from matternote where mnnoteindex='Conflict' ) MN " +
"where matsysnbr=mnmatter";
_jurisUtility.ExecuteNonQueryCommand(0, SQL);
UpdateStatus("Moving first line of conflict note.", 3, 3);
MessageBox.Show("Finished", "Status Update");
}
private void WriteLog(string comment)
{
var sql =
string.Format("Insert Into UtilityLog(ULTimeStamp,ULWkStaUser,ULComment) Values('{0}','{1}', '{2}')",
DateTime.Now, GetComputerAndUser(), comment);
_jurisUtility.ExecuteNonQueryCommand(0, sql);
}
private void DoDaFix()
{
MessageBox.Show(OrigDB);
string sql = "EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'";
_jurisUtility.ExecuteNonQueryCommand(0, sql);
/*
* DataTable d2 = dataGridView1.DataSource as DataTable;
* int i = 1;
*
* if (d2.Rows.Count == 0)
* { MessageBox.Show("No vouchers to import. Select a different file or close the application."); }
* else
* {
* foreach (DataRow dr in d2.Rows)
* {
* string vtype = dr["Type"].ToString();
* string VoucherDate = dr["VoucherDate"].ToString();
* string VendorCode = dr["VendorCode"].ToString();
* string PONbr = dr["PONbr"].ToString();
* string InvoiceNbr = dr["InvoiceNbr"].ToString();
* string DueDate = dr["DueDate"].ToString();
* string InvoiceDate = dr["InvoiceDate"].ToString();
* string DiscountDate = dr["DiscountDate"].ToString();
* string InvoiceAmt = dr["InvoiceAmt"].ToString();
* string NonDiscAmt = dr["NonDiscAmt"].ToString();
* string VchReference = dr["VchReference"].ToString();
* string SeparateCheck = dr["SeparateCheck"].ToString();
* string APAcct = dr["APAcct"].ToString();
* string GLDistAcct = dr["GLDistAcct"].ToString();
* string GLAmt = dr["GLAmt"].ToString();
* string TrustBank = dr["TrustBank"].ToString();
* string ExpClient = dr["ExpClient"].ToString();
* string ExpMatter = dr["ExpMatter"].ToString();
* string ExpCode = dr["ExpCode"].ToString();
* string ExpTaskCode = dr["ExpTaskCode"].ToString();
* string ExpUnits = dr["ExpUnits"].ToString();
* string ExpAmount = dr["ExpAmount"].ToString();
* string ExpNarrative = dr["ExpNarrative"].ToString();
* string ExpBillNote = dr["ExpBillNote"].ToString();
*
* string s2 = "Insert into #Vch " +
* "Values(" + i + ",'" + vtype + "',convert(datetime,'" + VoucherDate + "',101),'" + VendorCode + "','" + PONbr + "','" + InvoiceNbr + "',convert(datetime,'" + InvoiceDate + "',101),convert(datetime,'" + DueDate + "',101),convert(datetime,'" + DiscountDate + "',101), " +
* "cast(isnull('" + InvoiceAmt + "','0') as decimal(12,2)), cast(isnull('" + NonDiscAmt + "','0') as decimal(12,2)), '" + VchReference + "','" + SeparateCheck + "','" + APAcct + "','" + GLDistAcct + "', cast(isnull('" + GLAmt + "','0') as money), " +
* "'" + TrustBank + "','" + ExpClient + "','" + ExpMatter + "','" + ExpCode + "','" + ExpTaskCode + "',cast(isnull('" + ExpUnits + "','0') as decimal(12,2)),cast(isnull('" + ExpAmount + "','0') as decimal(12,2)),'" + ExpNarrative + "','" + ExpBillNote + "')";
* _jurisUtility.ExecuteNonQueryCommand(0, s2);
*
* i = i + 1;
* }
* }
*/
UpdateStatus("All MBF07 fields updated.", 1, 1);
MessageBox.Show("The process is complete", "Confirmation", MessageBoxButtons.OK, MessageBoxIcon.None);
}
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);
DateTime dt = dateTimePicker1.Value.Date;
if (checkBox2.Checked)
{
string venList = "";
foreach (DataGridViewRow r in dataGridView2.SelectedRows)
{
venList = venList + "'" + r.Cells[3].Value.ToString() + "',";
}
venList = venList.TrimEnd(',');
if (!string.IsNullOrEmpty(venList))
{
DialogResult d = MessageBox.Show("Are you sure you want to update these Distributions?", "Exception report", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (d == System.Windows.Forms.DialogResult.Yes)
{
string SQL = "UPDATE ScheduleTasks SET LastRuntime = '" + dt.ToString("yyyy-MM-dd HH:mm:ss") + "' where id in (" + venList + ")";
_jurisUtility.ExecuteNonQueryCommand(0, SQL);
UpdateStatus("Distributions reset successfuly.", 1, 1);
MessageBox.Show("Distributions updated successfully", "Success!", MessageBoxButtons.OK, MessageBoxIcon.None);
}
}
}
else
{
DialogResult d = MessageBox.Show("Are you sure you want to update these Distributions?", "Exception report", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (d == System.Windows.Forms.DialogResult.Yes)
{
string SQL = "UPDATE ScheduleTasks SET LastRuntime = '" + dt.ToString("yyyy-MM-dd HH:mm:ss") + "'";
if (checkBox1.Checked)
{
SQL = SQL + " WHERE (CAST(Active AS BIT) = 1)";
}
_jurisUtility.ExecuteNonQueryCommand(0, SQL);
UpdateStatus("Distributions reset successfuly.", 1, 1);
MessageBox.Show("Distributions updated successfully", "Success!", MessageBoxButtons.OK, MessageBoxIcon.None);
}
}
}
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 DoDaFix()
{
if (rbOne.Checked && String.IsNullOrEmpty(txtPrebill.Text))
{
MessageBox.Show("Please type in the prebill number", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
UpdateStatus("Processing Tax 1 Update.", 0, 1);
string RunSQL = "";
// 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 (rbAll.Checked == true)
{
RunSQL = "update prebillmatter " +
"set pbmtax1bld = ivutax " +
" from (select Clicode, Clireportingname, Matcode, Matreportingname,matsysnbr, pbfprebill, cast(sum(utamount) as decimal(12,2)) as IVUEntries, cast(sum(utamount * .04) as decimal(12,2)) as IVUTax" +
" from matter" +
" inner join client on matclinbr=clisysnbr" +
" inner join prebillfeeitem on pbfmatter=matsysnbr" +
" inner join unbilledtime on pbfutrecnbr=utrecnbr and pbfutbatch=utbatch" +
" where utcode1='IVU' " +
"group by clicode, clireportingname, matcode, matreportingname, pbfprebill, matsysnbr) PBM " +
"where pbmprebill=pbfprebill and pbmmatter=matsysnbr ";
}
else
{
RunSQL = "update prebillmatter " +
"set pbmtax1bld = ivutax " +
" from (select Clicode, Clireportingname, Matcode, Matreportingname,matsysnbr, pbfprebill, cast(sum(utamount) as decimal(12,2)) as IVUEntries, cast(sum(utamount * .04) as decimal(12,2)) as IVUTax" +
" from matter" +
" inner join client on matclinbr=clisysnbr" +
" inner join prebillfeeitem on pbfmatter=matsysnbr" +
" inner join unbilledtime on pbfutrecnbr=utrecnbr and pbfutbatch=utbatch" +
" where utcode1='IVU' and pbfprebill=" + txtPrebill.Text.ToString() +
"group by clicode, clireportingname, matcode, matreportingname, pbfprebill, matsysnbr) PBM " +
"where pbmprebill=pbfprebill and pbmmatter=matsysnbr ";
}
_jurisUtility.ExecuteNonQueryCommand(0, RunSQL);
UpdateStatus("Tax 1 Updated.", 1, 1);
}
}
private void DoDaFix()
{
string sql = "";
if (!checkBoxAll.Checked)
{
sql = "update cashreceipt set CRPayor = '" + textBoxWillBe.Text + "' where CRBatch = " + comboBox1.SelectedValue.ToString() + " and CRRecNbr = " + comboBox2.SelectedValue.ToString();
}
else
{
sql = "update cashreceipt set CRPayor = '" + textBoxWillBe.Text + "' where CRBatch = " + comboBox1.SelectedValue.ToString();
}
_jurisUtility.ExecuteNonQueryCommand(0, sql);
UpdateStatus("Payor field(s) updated.", 1, 1);
MessageBox.Show("The process is complete", "Confirmation", MessageBoxButtons.OK, MessageBoxIcon.None);
}
private void updateClients(string fileName)
{
cliErrors = new DataSet();
DataTable et = cliErrors.Tables.Add("Errors");
et.Columns.Add("MatterCode");
et.Columns.Add("BillToBill");
et.Columns.Add("BillToEdit");
et.Columns.Add("ErrorMessage");
System.Data.OleDb.OleDbConnection MyConnection;
System.Data.DataSet DtSet;
System.Data.OleDb.OleDbDataAdapter MyCommand;
MyConnection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=Excel 12.0;");
MyCommand = new System.Data.OleDb.OleDbDataAdapter("select distinct * from [Client$]", MyConnection);
DtSet = new System.Data.DataSet();
MyCommand.Fill(DtSet);
MyConnection.Close();
DataTable dt = DtSet.Tables[0];
for (int i = 0; i < dt.Rows.Count; i++)
{
string clicode = dt.Rows[i]["CliCode"].ToString();
string BillToBill = dt.Rows[i]["BillToBillFormat"].ToString();
string sqlIns = "update client set CliBillFormat = '" + BillToBill + "' where clicode like '%000" + clicode + "'";
_jurisUtility.ExecuteNonQueryCommand(0, sqlIns);
if (_jurisUtility.error)
{
et.Rows.Add(clicode, BillToBill, "", _jurisUtility.errorMessage.Replace("\r", "").Replace("\n", "").Trim());
_jurisUtility.error = false;
_jurisUtility.errorMessage = "";
}
}
}
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 (checkRequisitesAreGood())
{
string SQL = "update bc set " + getBillCopySQL() +
" from billcopy as bc inner join billto as bt on bt.BillToSysNbr = bc.BilCpyBillTo " +
" inner join client as c on c.clisysnbr = bt.BillToCliNbr " +
" where (" + getWhereClause() + ") ";
_jurisUtility.ExecuteNonQueryCommand(0, SQL);
UpdateStatus("Finished Update.", 1, 1);
MessageBox.Show("The process is complete", "Confirmation", MessageBoxButtons.OK, MessageBoxIcon.None);
UpdateStatus("Finished Update.", 0, 1);
}
else
{
MessageBox.Show(message, "Selection Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
message = "";
}
}
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);
Cursor.Current = Cursors.WaitCursor;
Application.DoEvents();
toolStripStatusLabel.Text = "Updating Client Matters to Require Activity Codes";
statusStrip.Refresh();
UpdateStatus("Updating Client Matters to Require Activity Codes ", 0, 1);
Application.DoEvents();
string allUsedClients = "";
allUsedClients = getNumericCliCodesBetweenOneAnd9999();
allUsedClients.TrimEnd(',');
string sql = "update matter set matreqactycdontime = 'Y' where matclinbr in (" + allUsedClients + ")";
_jurisUtility.ExecuteNonQueryCommand(0, sql);
sql = "update client set clireqactycdontime = 'Y' where clisysnbr in (" + allUsedClients + ")";
_jurisUtility.ExecuteNonQueryCommand(0, sql);
Cursor.Current = Cursors.Default;
toolStripStatusLabel.Text = "Update Client Matters (Clients 0005 - 9999) to Require Activity Codes Complete";
statusStrip.Refresh();
UpdateStatus("Update Client Matters to Require Activity Codes Complete", 1, 1);
WriteLog("Update Client Matters to Require Activity Codes");
Application.DoEvents();
allUsedClients = "";
}
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);
DialogResult result = MessageBox.Show("This will change all vendor discount GL account references from " + fromGLAccount + "\r\n" + "to " + toGLAccount + ". Are you sure?", "Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (result == System.Windows.Forms.DialogResult.Yes)
{
UpdateStatus("Updating Vendors...", 1, 1);
string SQL = "update Vendor Set vendiscacct = (select ChtSysNbr from ChartOfAccounts where " + formattingString + " = " + toGLAccount + ") where vendiscacct = (select ChtSysNbr from ChartOfAccounts where " + formattingString + " = " + fromGLAccount + ")";
_jurisUtility.ExecuteNonQueryCommand(0, SQL);
MessageBox.Show("The process is complete", "Finished", MessageBoxButtons.OK, MessageBoxIcon.None);
toGLAccount = "";
fromGLAccount = "";
button1.Enabled = 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);
UpdateStatus("Removing TkprChng - Matter Duplicates.", 1, 4);
string SQL = "delete From matternote where mnnoteindex<>'TkprChg' and mnnoteindex like 'TkprChg%'";
_jurisUtility.ExecuteNonQueryCommand(0, SQL);
SQL = "delete From ClientNote where CNNoteIndex<>'TkprChg' and CNNoteIndex like 'TkprChg%'";
_jurisUtility.ExecuteNonQueryCommand(0, SQL);
UpdateStatus("Removing TkprChng - Client Duplicates.", 2, 4);
SQL = "delete matternote from matternote inner join " +
"(select mnmatter as matsys, mnnoteindex as noteindex,mnnotetext as notetext, " +
"rank() over (partition by mnmatter order by mntimestamp) RMat " +
"from matternote where mnnoteindex like 'TkprUpdate%' ) Mn " +
"on mnmatter=matsys and mnnoteindex=noteindex " +
"where rmat<>1 and mnnoteindex like 'TkprUpdate%'";
_jurisUtility.ExecuteNonQueryCommand(0, SQL);
UpdateStatus("Removing TkprUpdate - Matter Duplicates.", 3, 4);
SQL = "delete ClientNote from ClientNote inner join " +
"(select CNClient as matsys, CNNoteIndex as noteindex,CNClient as notetext, " +
"rank() over (partition by CNClient order by CNTimeStamp) RMat " +
"from ClientNote where CNNoteIndex like 'TkprUpdate%' ) Mn " +
"on CNClient=matsys and CNNoteIndex=noteindex " +
"where rmat<>1 and CNNoteIndex like 'TkprUpdate%'";
_jurisUtility.ExecuteNonQueryCommand(0, SQL);
UpdateStatus("All incorrect notecards removed.", 4, 4);
MessageBox.Show("The process is complete", "Confirmation", MessageBoxButtons.OK, MessageBoxIcon.None);
}
private void DoDaFix()
{
string SQL = "";
UpdateStatus("Creating Notecards...", 0, 0);
toolStripStatusLabel.Text = "Creating Notecards...";
Cursor.Current = Cursors.WaitCursor;
statusStrip.Refresh();
Application.DoEvents();
//Billing Guides
SQL = "insert into matternote (mnmatter, mnnoteindex, mnobject, mnnotetext, mnnoteobject) select matsysnbr, 'Billing Guides', '', '', null from matter where matsysnbr not in (select mnmatter from matternote where MNNoteIndex = 'Billing Guides')";
_jurisUtility.ExecuteNonQueryCommand(0, SQL);
UpdateStatus("Billing Guides Added.", 1, 5);
//Closing Ltr
SQL = "insert into matternote (mnmatter, mnnoteindex, mnobject, mnnotetext, mnnoteobject) select matsysnbr, 'Closing Ltr', '', '', null from matter where matsysnbr not in (select mnmatter from matternote where MNNoteIndex = 'Closing Ltr')";
_jurisUtility.ExecuteNonQueryCommand(0, SQL);
UpdateStatus("Closing Ltr Added.", 2, 5);
//Engagement Ltr
SQL = "insert into matternote (mnmatter, mnnoteindex, mnobject, mnnotetext, mnnoteobject) select matsysnbr, 'Engagement Ltr', '', '', null from matter where matsysnbr not in (select mnmatter from matternote where MNNoteIndex = 'Engagement Ltr')";
_jurisUtility.ExecuteNonQueryCommand(0, SQL);
UpdateStatus("Engagement Ltr Added.", 3, 5);
//Invoice Sent To
SQL = "insert into matternote (mnmatter, mnnoteindex, mnobject, mnnotetext, mnnoteobject) select matsysnbr, 'Invoice Sent To', '', '', null from matter where matsysnbr not in (select mnmatter from matternote where MNNoteIndex = 'Invoice Sent To')";
_jurisUtility.ExecuteNonQueryCommand(0, SQL);
UpdateStatus("Invoice Sent To Added.", 4, 5);
//Write Off Instr
SQL = "insert into matternote (mnmatter, mnnoteindex, mnobject, mnnotetext, mnnoteobject) select matsysnbr, 'Write Off Instr', '', '', null from matter where matsysnbr not in (select mnmatter from matternote where MNNoteIndex = 'Write Off Instr')";
_jurisUtility.ExecuteNonQueryCommand(0, SQL);
UpdateStatus("Write Off Instr Added.", 5, 5);
toolStripStatusLabel.Text = "Notecards Updated.";
Cursor.Current = Cursors.Default;
statusStrip.Refresh();
Application.DoEvents();
MessageBox.Show("Process Complete!", "Confirmation", MessageBoxButtons.OK, MessageBoxIcon.None);
}
public void updateTimeEntries(List <TimeEntry> tList, int EntryStatus)
{
string IDs = "";
if (tList.Count > 0)
{
foreach (TimeEntry tt in tList)
{
IDs = IDs + tt.ID + ",";
}
IDs = IDs.TrimEnd(',');
String SQL = "update timeentry set EntryStatus = " + EntryStatus + " where EntryID in (" + IDs + ")";
_jurisUtility.ExecuteNonQueryCommand(0, SQL);
}
}
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 (validRec) //do nothing if a valid rec wasnt found on the bank account
{
string SQL = "SELECT BRHStmtDate, BRHBookLastStmtDate FROM BankReconHistory WHERE (BRHBank = '" + BnkCode + "') AND (BRHRecorded = 'N')";
DataSet rectest1 = _jurisUtility.RecordsetFromSQL(SQL);
if (rectest1.Tables[0].Rows.Count > 0) //there ARE unrecorded recs
{
DialogResult inProgress = MessageBox.Show("There is a bank reconciliation in progress for Bank " + BnkCode + ". That reconciliation will be deleted." + "\r\n" + "The last recorded reconciliation (statement date: " + textBoxStateDate.Text + ") will be set to 'unrecorded'. Do you wish to continue?", "Reconciliation in progress", MessageBoxButtons.YesNo, MessageBoxIcon.Warning);
if (inProgress == System.Windows.Forms.DialogResult.Yes)
{
/////////delete unrecorded items
SQL = "DELETE FROM BankReconHistory WHERE (BRHBank = '" + BnkCode + "') AND (BRHRecorded = 'N')";
_jurisUtility.ExecuteNonQueryCommand(0, SQL);
SQL = "UPDATE CheckRegister SET CkRegCleared = 'N' WHERE (CkRegBank = '" + BnkCode + "') AND (CkRegCleared = 'P')";
_jurisUtility.ExecuteNonQueryCommand(0, SQL);
/////////set last rec to unrecorded after unrecorded rec was removed
setBankRecToUnrecorded();
updateBankAccount();
UpdateStatus("Bank Rec undone.", 1, 1);
MessageBox.Show("The process is complete", "Confirmation", MessageBoxButtons.OK, MessageBoxIcon.None);
}
else
{
MessageBox.Show("No changes were made");
}
}
else //there are NO unrecorded recs
{
setBankRecToUnrecorded();
updateBankAccount();
UpdateStatus("Bank Rec undone.", 1, 1);
MessageBox.Show("The process is complete", "Confirmation", MessageBoxButtons.OK, MessageBoxIcon.None);
}
}
validRec = 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);
string SQL = "ALTER TABLE dbo.Employee ALTER COLUMN CommissionPercent varchar(5)";
_jurisUtility.ExecuteNonQueryCommand(0, SQL);
SQL = "ALTER TABLE dbo.Employee ALTER COLUMN WorkingPercent varchar(5)";
_jurisUtility.ExecuteNonQueryCommand(0, SQL);
SQL = "ALTER TABLE dbo.Employee ALTER COLUMN DrawAmount varchar(8)";
_jurisUtility.ExecuteNonQueryCommand(0, SQL);
// SQL = "ALTER TABLE dbo.Employee_Log ALTER COLUMN CommissionPercent varchar(5)";
// _jurisUtility.ExecuteNonQueryCommand(0, SQL);
// SQL = "ALTER TABLE dbo.Employee_Log ALTER COLUMN WorkingPercent varchar(5)";
// _jurisUtility.ExecuteNonQueryCommand(0, SQL);
// SQL = "ALTER TABLE dbo.Employee_Log ALTER COLUMN DrawAmount varchar(8)";
// _jurisUtility.ExecuteNonQueryCommand(0, SQL);
SQL = "update sysparam set sptxtvalue = 'CommissionPercent,C,5,N' where sptxtvalue = 'CommissionPercent,N,3,N' and spname = 'FldTkprUDF#2'";
_jurisUtility.ExecuteNonQueryCommand(0, SQL);
SQL = "update sysparam set sptxtvalue = 'WorkingPercent,C,5,N' where sptxtvalue = 'WorkingPercent,N,3,N' and spname = 'FldTkprUDF#3'";
_jurisUtility.ExecuteNonQueryCommand(0, SQL);
SQL = "update sysparam set sptxtvalue = 'DrawAmount,C,8,N' where sptxtvalue = 'DrawAmount,N,7,N' and spname = 'FldTkprUDF#4'";
_jurisUtility.ExecuteNonQueryCommand(0, SQL);
UpdateStatus("Both UDF Fields Updated.", 1, 1);
MessageBox.Show("The process is complete", "Confirmation", MessageBoxButtons.OK, MessageBoxIcon.None);
}
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);
Cursor.Current = Cursors.WaitCursor;
Application.DoEvents();
toolStripStatusLabel.Text = "Processing Retainer Prebill Allocations....";
statusStrip.Refresh();
string SQLSel = @"select cast(Prebill as varchar(20)) as Prebill, cast(matsysnbr as varchar(10)) as MatterSys,cast(billtobillingatty as varchar(20)) as BTkpr, cast(morig1 as varchar(20)) as OTkpr1,
cast(case when morig2 is null then '' else morig2 end as varchar(20)) as OTkpr2,
cast(case when morig3 is null then '' else morig3 end as varchar(20)) OTkpr3
,convert(varchar(30),cast(case when retainertoallocate>0 then (retainertoallocate * OTPct1 * .01) else 0 end as money),1) as OT1Alloc1
,case when convert(varchar(30),cast(case when retainertoallocate>0 then (retainertoallocate * OTPct2 * .01) else 0 end as money),1) is null then '0.00' else convert(varchar(30),cast(case when retainertoallocate>0 then (retainertoallocate * OTPct2 * .01) else 0 end as money),1) end as OT2Alloc2
,case when convert(varchar(30),cast(case when retainertoallocate>0 then (retainertoallocate * OTPct3 * .01) else 0 end as money),1) is null then '0.00' else convert(varchar(30),cast(case when retainertoallocate>0 then (retainertoallocate * OTPct3 * .01) else 0 end as money),1) end as OT3Alloc3,
convert(varchar(30),case when retainertoallocate>0 then (
cast((retainertoallocate * case when otpct1 is null then '0' when OTPct1='' then '0' else otPct1 end * .01) as decimal(12,2)) +
cast((retainertoallocate * case when otpct2 is null then '0' when OTPct2='' then '0' else otpct2 end * .01) as decimal(12,2)) +
cast((retainertoallocate * case when otpct3 is null then '0' when OTPct3='' then '0' else otpct3 end * .01) as decimal(12,2))) - retainertoallocate else 0 end,1) as Rmd
from (select prebill as Prebill,billtobillingatty, matsysnbr, matfltfeeorretainer as retainer, sum(totalfees) as TotalFees, morig1, morig2, morig3, morig4,
sum(cast(case when totalfees is null then 0 else totalfees end as money)) as Total,
sum(cast(otherfees as money)) as Other, sum(cast(otkpr1Fees as money)) as O1Fees, ot1, case when cast(otpct1 as int)=0 then '' else cast(otpct1 as varchar(10)) end as OTPct1,
sum(cast(otkpr2Fees as money)) as O2Fees, ot2, case when cast(otpct2 as int)=0 then '' else cast(otpct2 as varchar(10)) end as OTPct2,
sum(cast(otkpr3Fees as money)) as O3Fees, ot3, case when cast(otpct3 as int)=0 then '' else cast(otpct3 as varchar(10)) end as OTPct3,
sum(cast(otkpr4Fees as money)) as O4Fees, ot4, case when cast(otpct4 as int)=0 then '' else cast(otpct4 as varchar(10)) end as OTPct4,
case when matfltfeeorretainer is null then 0 else matfltfeeorretainer end - sum(cast(case when totalfees is null then 0 else totalfees end as money)) as RetainerToAllocate
from (select pbmprebill as prebill, utmatter, utamount as TotalFees, case when uttkpr not in (select morigatty from matorigatty where morigmat=matsysnbr) then utamount else null end as OtherFees
,case when uttkpr=morig1 then utamount else null end as Otkpr1Fees, case when uttkpr=morig2 then utamount else null end as Otkpr2Fees
,case when uttkpr=morig3 then utamount else null end as Otkpr3Fees, case when uttkpr=morig4 then utamount else null end as Otkpr4Fees,morig1, morig2, morig3, morig4, ot1, ot2, ot3, ot4, otpct1, otpct2, otpct3, otpct4, null as Expenses
from unbilledtime
inner join prebillmatter on pbmmatter=utmatter
inner join prebillfeeitem on pbfutbatch=utbatch and pbfutrecnbr=utrecnbr
inner join matter on pbmmatter=matsysnbr
inner join (select morigmat, max(case when rnk=1 then morigatty else '' end) as morig1, max(case when rnk=2 then morigatty else null end) as morig2, max(case when rnk=3 then morigatty else null end) as morig3
, max(case when rnk=4 then morigatty else null end) as morig4, max(case when rnk=1 then empinitials else null end) as OT1, max(case when rnk=2 then empinitials else null end) as OT2
, max(case when rnk=3 then empinitials else null end) as OT3, max(case when rnk=4 then empinitials else null end) as OT4, max(case when rnk=1 then cast(morigpcnt as int) else null end) as OTpct1
, max(case when rnk=2 then cast(morigpcnt as int) else null end) as OTpct2, max(case when rnk=3 then cast(morigpcnt as int) else null end) as OTpct3
, max(case when rnk=4 then cast(morigpcnt as int) else null end) as OTpct4
from (select morigmat, morigatty, morigpcnt, empinitials, rank() over (partition by morigmat order by case when billtobillingatty=morigatty then 0 else morigatty end) as rnk
from matorigatty
inner join employee on empsysnbr=morigatty
inner join matter on morigmat=matsysnbr
inner join billto on matbillto=billtosysnbr)MO
group by morigmat)MOrig on morigmat=matsysnbr
where matbillagreecode='R' and matfltfeeorretainer<>0 and matstatusflag='O'
) UT
inner join matter on matsysnbr=utmatter
inner join client on matclinbr=clisysnbr
inner join billto on matbillto=billtosysnbr
inner join prebill on prebill=pbsysnbr
where pbstatus<=2 and matbillagreecode='R' and pbbillto in (select billtosysnbr from billto where billtobillingatty = " + billToAttyEmpSys + ") " +
" and (cast(billtobillingatty as varchar(20))<>cast(morig1 as varchar(20)) or " +
" cast(case when morig2 is null then '' else morig2 end as varchar(20))>'') " +
" group by prebill, billtobillingatty, matsysnbr, matfltfeeorretainer, morig1, morig2, morig3, morig4, ot1, ot2, ot3, ot4, otpct1, otpct2, otpct3, otpct4 " +
" ) AllocTbl order by prebill";
DataSet PBRS = _jurisUtility.RecordsetFromSQL(SQLSel);
int counter;
int rowCount = PBRS.Tables[0].Rows.Count;
string Prebill = "";
string MatSys = "";
string Btkpr = "";
string Otkpr1 = "";
string Otkpr2 = "";
string Otkpr3 = "";
string Alloc1 = "";
string Alloc2 = "";
string Alloc3 = "";
string Rd = "";
string SQLCT = "EXEC sp_MSforeachtable @command1='ALTER TABLE ? NOCHECK CONSTRAINT ALL'";
_jurisUtility.ExecuteNonQueryCommand(0, SQLCT);
if (rowCount != 0)
{
counter = 0;
foreach (DataRow row in PBRS.Tables[0].Rows)
{
counter = counter + 1;
Prebill = row["Prebill"].ToString();
toolStripStatusLabel.Text = "Processing Retainer Prebill Allocations for " + Prebill.ToString() + "....";
statusStrip.Refresh();
MatSys = row["MatterSys"].ToString();
Btkpr = row["BTkpr"].ToString();
Otkpr1 = row["OTkpr1"].ToString();
Otkpr2 = row["OTkpr2"].ToString();
Otkpr3 = row["OTkpr3"].ToString();
Alloc1 = row["OT1Alloc1"].ToString();
Alloc2 = row["OT2Alloc2"].ToString();
Alloc3 = row["OT3Alloc3"].ToString();
Rd = row["Rmd"].ToString();
string SQL99 = @"update prebillfeeitem set pbfamtonbill=utamount from unbilledtime where utbatch=pbfutbatch and utrecnbr=pbfutrecnbr and pbfstatus='0' and pbfprebill= cast('" + Prebill.ToString() + "' as int) and cast('" + Alloc1.ToString() + "' as money)>0";
_jurisUtility.ExecuteNonQueryCommand(0, SQL99);
string SQL10 = @"update prebillfeeitem set pbfamtonbill=cast('" + Alloc1.ToString() + "' as money), pbftkpronbill=cast('" + Otkpr1.ToString() + "' as int) where pbftkpronbill = cast('" + Btkpr.ToString() + "' as int) and pbfseqonbill=0 and pbfhrsonbill = 0 and pbfprebill = cast('" + Prebill.ToString() + "' as int) and pbfstatus = 'I' and cast('" + Alloc1.ToString() + "' as money)>0";
_jurisUtility.ExecuteNonQueryCommand(0, SQL10);
string SQL436 = @"insert into timebatchdetail(tbdbatch,tbdrecnbr, tbdrectype, tbdposted, tbddate, tbdprdyear, tbdprdnbr, tbdtkpr, tbdmatter, tbdbudgphase, tbdfeesched, tbdtaskcd, tbdactivitycd,tbdbillableflg,tbdactualhrswrk, tbdhourssource, tbdhourstobill, tbdratesource, tbdrate, tbdamountsource, tbdamount, tbdcode1, tbdcode2, tbdcode3, tbdbillnote, tbdstopwatch, tbdnarrative, tbdid)
Select pbbatch, maxrec +1 as Recnbr, 2, 'Y',pbfd, (select prdyear from actngperiod where prdstartdate<=pbfd and prdenddate>=pbfd and prdnbr<>0),
(select prdnbr from actngperiod where prdstartdate<=pbfd and prdenddate>=pbfd and prdnbr<>0),cast('" + Otkpr1.ToString() + @"' as int),cast('" + MatSys.ToString() + @"' as int), 0, matfeesch, null,null, 'Y', 0,'W',0,3, 0, 3, 0, '','','','',0,'', (select max(tbdid) + 1 from timebatchdetail)
from prebillmatter
inner join matter on pbmmatter=matsysnbr
left outer join tkprrate on tkrfeesch=matfeesch and tkremp=cast('" + Otkpr1.ToString() + @"' as int)
left outer join perstyprate on ptrfeesch=matfeesch and ptrprstyp=(select empprstyp from employee where empsysnbr=cast('" + Otkpr1.ToString() + @"' as int)) ,
(select tbdbatch as pbbatch, max(tbdrecnbr) as maxrec, (select max(pbfdateonbill) from prebillfeeitem) as pbfd
from timebatchdetail where tbdbatch=(select max(pbfutbatch) from prebillfeeitem ) group by tbdbatch) PBatch
where cast('" + Otkpr1.ToString() + @"' as int) is not null and
cast('" + Otkpr1.ToString() + "' as int) > 0 and cast('" + Alloc1.ToString() + @"' as money) <> 0
and cast('" + Alloc1.ToString() + "' as money) is not null and pbmmatter = cast('" + MatSys.ToString() + @"' as int)
and pbmprebill = cast('" + Prebill.ToString() + "' as int) and cast('" + Prebill.ToString() + "' as int) not in (select pbfprebill from prebillfeeitem where pbfstatus='I' and pbftkpronbill=cast('" + Otkpr1.ToString() + @"' as int)) ";
_jurisUtility.ExecuteNonQueryCommand(0, SQL436);
string SQL46 = @"insert into timebatchdetail(tbdbatch,tbdrecnbr, tbdrectype, tbdposted, tbddate, tbdprdyear, tbdprdnbr, tbdtkpr, tbdmatter, tbdbudgphase, tbdfeesched, tbdtaskcd, tbdactivitycd,tbdbillableflg,tbdactualhrswrk, tbdhourssource, tbdhourstobill, tbdratesource, tbdrate, tbdamountsource, tbdamount, tbdcode1, tbdcode2, tbdcode3, tbdbillnote, tbdstopwatch, tbdnarrative, tbdid)
Select pbbatch, maxrec +1 as Recnbr, 2, 'Y',pbfd, (select prdyear from actngperiod where prdstartdate<=pbfd and prdenddate>=pbfd and prdnbr<>0),
(select prdnbr from actngperiod where prdstartdate<=pbfd and prdenddate>=pbfd and prdnbr<>0),cast('" + Otkpr2.ToString() + @"' as int),cast('" + MatSys.ToString() + @"' as int), 0, matfeesch, null,null, 'Y', 0,'W',0,3, 0, 3, 0, '','','','',0,'', (select max(tbdid) + 1 from timebatchdetail)
from prebillmatter
inner join matter on pbmmatter=matsysnbr
left outer join tkprrate on tkrfeesch=matfeesch and tkremp=cast('" + Otkpr2.ToString() + @"' as int)
left outer join perstyprate on ptrfeesch=matfeesch and ptrprstyp=(select empprstyp from employee where empsysnbr=cast('" + Otkpr2.ToString() + @"' as int)) ,
(select tbdbatch as pbbatch, max(tbdrecnbr) as maxrec, (select max(pbfdateonbill) from prebillfeeitem ) as pbfd
from timebatchdetail where tbdbatch=(select max(pbfutbatch) from prebillfeeitem ) group by tbdbatch) PBatch
where cast('" + Otkpr2.ToString() + @"' as int) is not null and
cast('" + Otkpr2.ToString() + "' as int) > 0 and cast('" + Alloc2.ToString() + @"' as money) <> 0
and cast('" + Alloc2.ToString() + "' as money) is not null and pbmmatter = cast('" + MatSys.ToString() + @"' as int)
and pbmprebill = cast('" + Prebill.ToString() + "' as int) ";
_jurisUtility.ExecuteNonQueryCommand(0, SQL46);
string SQL47 = @"insert into timebatchdetail(tbdbatch,tbdrecnbr, tbdrectype, tbdposted, tbddate, tbdprdyear, tbdprdnbr, tbdtkpr, tbdmatter, tbdbudgphase, tbdfeesched, tbdtaskcd, tbdactivitycd,tbdbillableflg,tbdactualhrswrk, tbdhourssource, tbdhourstobill, tbdratesource, tbdrate, tbdamountsource, tbdamount, tbdcode1, tbdcode2, tbdcode3, tbdbillnote, tbdstopwatch, tbdnarrative, tbdid)
Select pbbatch, maxrec +1 as Recnbr, 2, 'Y',pbfd, (select prdyear from actngperiod where prdstartdate<=pbfd and prdenddate>=pbfd and prdnbr<>0),
(select prdnbr from actngperiod where prdstartdate<=pbfd and prdenddate>=pbfd and prdnbr<>0),cast('" + Otkpr3.ToString() + @"' as int),cast('" + MatSys.ToString() + @"' as int), 0, matfeesch, null,null, 'Y', 0,'W',0,3, 0, 3, 0, '','','','',0,'', (select max(tbdid) + 1 from timebatchdetail)
from prebillmatter
inner join matter on pbmmatter=matsysnbr
left outer join tkprrate on tkrfeesch=matfeesch and tkremp=cast('" + Otkpr3.ToString() + @"' as int)
left outer join perstyprate on ptrfeesch=matfeesch and ptrprstyp=(select empprstyp from employee where empsysnbr=cast('" + Otkpr3.ToString() + @"' as int)) ,
(select tbdbatch as pbbatch, max(tbdrecnbr) as maxrec, (select max(pbfdateonbill) from prebillfeeitem ) as pbfd
from timebatchdetail where tbdbatch=(select max(pbfutbatch) from prebillfeeitem ) group by tbdbatch) PBatch
where cast('" + Otkpr3.ToString() + @"' as int) is not null and
cast('" + Otkpr3.ToString() + "' as int) > 0 and cast('" + Alloc3.ToString() + @"' as money) <> 0
and cast ('" + Alloc3.ToString() + "' as money) is not null and pbmmatter = cast('" + MatSys.ToString() + @"' as int)
and pbmprebill = cast('" + Prebill.ToString() + "' as int) ";
_jurisUtility.ExecuteNonQueryCommand(0, SQL47);
string SQL44 = @"Insert into unbilledtime(utbatch, utrecnbr, utmatter, utbudgphase,utdate, utprdyear, utprdnbr, uttkpr, utfeesched, uttaskcd, utactivitycd, utbillableflg,utactualhrswrk,uthourssource, uthourstobill, utratesource, utrate, utamountsource, utamount, utstdrate, utamtatstdrate, utnarrative,utid, utpostdate, utpostin, utcode1, utcode2, utcode3, utbillnote)
select tbdbatch, tbdrecnbr, tbdmatter, tbdbudgphase, tbddate, tbdprdyear, tbdprdnbr, tbdtkpr, tbdfeesched, tbdtaskcd, tbdactivitycd,tbdbillableflg,tbdactualhrswrk, tbdhourssource, tbdhourstobill, tbdratesource, tbdrate,tbdamountsource, tbdamount, tbdrate, tbdamount, tbdnarrative, tbdid, tbddate, -1, tbdcode1, tbdcode2, tbdcode3,tbdbillnote
from timebatchdetail,(select pbfutbatch as pbbatch, max(pbfutrecnbr) as maxrec, max(pbfdateonbill) as pbfd
from prebillfeeitem where pbfutbatch=(select max(pbfutbatch) from prebillfeeitem) group by pbfutbatch) PBatch
where tbdmatter=cast('" + MatSys.ToString() + @"' as int) and tbdbatch=pbbatch and tbdrecnbr>maxrec";
_jurisUtility.ExecuteNonQueryCommand(0, SQL44);
string SQL121 = @"Insert into prebillfeeitem(pbfprebill, pbfmatter, pbftkpronbill, pbfutbatch, pbfutrecnbr, pbfstatus, pbfdateonbill, pbfhrsonbill, pbfrateonbill, pbfamtonbill, pbfseqonbill)
Select cast('" + Prebill.ToString() + "' as int), cast('" + MatSys.ToString() + "' as int), uttkpr, utbatch, utrecnbr, 'I',utdate, utactualhrswrk, 0, 0, case when (select max(pbfseqonbill) from prebillfeeitem where pbfprebill=cast('" + Prebill.ToString() + "' as int)) is null then Rank() over (order by utrecnbr) -1 else (select max(pbfseqonbill) from prebillfeeitem where pbfprebill=cast('" + Prebill.ToString() + @"' as int)) + Rank() over (order by utrecnbr) end
from unbilledtime,(select pbfutbatch as pbbatch, max(pbfutrecnbr) as maxrec, max(pbfdateonbill) as pbfd
from prebillfeeitem where pbfutbatch=(select max(pbfutbatch) from prebillfeeitem) group by pbfutbatch) PBatch
where utmatter=cast('" + MatSys.ToString() + "' as int) and utbatch=pbbatch and utrecnbr>maxrec";
_jurisUtility.ExecuteNonQueryCommand(0, SQL121);
string SQL33 = @"Update prebillfeeitem
set pbfamtonbill=case when pbftkpronbill=cast('" + Otkpr1.ToString() + "' as int) then cast('" + Alloc1.ToString() + @"' as money)
when pbftkpronbill=cast('" + Otkpr2.ToString() + "' as int) then cast('" + Alloc2.ToString() + @"' as money)
when pbftkpronbill=cast('" + Otkpr3.ToString() + "' as int) then cast('" + Alloc3.ToString() + @"' as money) else pbfamtonbill end
where pbfprebill=cast('" + Prebill.ToString() + "' as int) and pbfmatter=cast('" + MatSys.ToString() + "' as int) and pbfstatus='I' and pbfhrsonbill=0";
_jurisUtility.ExecuteNonQueryCommand(0, SQL33);
string SQL34 = @"update prebillfeeitem set pbfamtonbill=(pbfamtonbill - cast('" + Rd.ToString() + @"' as money)) from matter where pbfprebill=cast('" + Prebill.ToString() + "' as int) and cast('" + Rd.ToString() + @"' as money)>0 and cast('" + Rd.ToString() + @"' as money) is not null and pbftkpronbill=cast('" + Otkpr2.ToString() + "' as int) and pbfstatus='I'";
_jurisUtility.ExecuteNonQueryCommand(0, SQL34);
string SQL13 = @"insert into prebillfeerecap(pbfrprebill, pbfrmatter, pbfrtkpronbill, pbfrhrsonbill, pbframtonbill, pbfrppdapplied,pbfrtrustapplied)
select cast('" + Prebill.ToString() + "' as int),cast('" + MatSys.ToString() + "' as int), cast('" + Otkpr1.ToString() + "' as int),0,0,0,0 from employee where cast('" + Alloc1.ToString() + "' as money)<>0 and cast('" + Alloc1.ToString() + "' as money) is not null and empsysnbr=cast('" + Otkpr1.ToString() + "' as int) and empsysnbr not in (select pbfrtkpronbill from prebillfeerecap where pbfrprebill=cast('" + Prebill.ToString() + "' as int))";
_jurisUtility.ExecuteNonQueryCommand(0, SQL13);
string SQL3 = @"insert into prebillfeerecap(pbfrprebill, pbfrmatter, pbfrtkpronbill, pbfrhrsonbill, pbframtonbill, pbfrppdapplied,pbfrtrustapplied)
select cast('" + Prebill.ToString() + "' as int),cast('" + MatSys.ToString() + "' as int), cast('" + Otkpr2.ToString() + "' as int),0,0,0,0 from employee where cast('" + Alloc2.ToString() + "' as money)<>0 and cast('" + Alloc2.ToString() + "' as money) is not null and empsysnbr=cast('" + Otkpr2.ToString() + "' as int) and empsysnbr not in (select pbfrtkpronbill from prebillfeerecap where pbfrprebill=cast('" + Prebill.ToString() + "' as int))";
_jurisUtility.ExecuteNonQueryCommand(0, SQL3);
string SQL4 = @"insert into prebillfeerecap(pbfrprebill, pbfrmatter, pbfrtkpronbill, pbfrhrsonbill, pbframtonbill, pbfrppdapplied,pbfrtrustapplied)
select cast('" + Prebill.ToString() + "' as int),cast('" + MatSys.ToString() + "' as int), cast('" + Otkpr3.ToString() + "' as int),0,0,0,0 from employee where cast('" + Alloc3.ToString() + "' as money)<>0 and cast('" + Alloc3.ToString() + "' as money) is not null and empsysnbr=cast('" + Otkpr3.ToString() + "' as int) and empsysnbr not in (select pbfrtkpronbill from prebillfeerecap where pbfrprebill=cast('" + Prebill.ToString() + "' as int))";
_jurisUtility.ExecuteNonQueryCommand(0, SQL4);
string SQL12 = @"update prebillfeerecap set pbframtonbill=amt
from (select pbfmatter, pbfprebill, pbftkpronbill, sum(pbfamtonbill) as amt from prebillfeeitem inner join matter on pbfmatter=matsysnbr where matbillagreecode='R' group by pbfmatter, pbfprebill, pbftkpronbill)PB
where pbfrmatter=pbfmatter and pbfprebill=pbfrprebill and pbfrtkpronbill=pbftkpronbill and pbfrprebill=cast('" + Prebill.ToString() + "' as int) and pbfrmatter=cast('" + MatSys.ToString() + "' as int)";
_jurisUtility.ExecuteNonQueryCommand(0, SQL12);
string SQL17 = @"update prebillmatter set pbmfeebld=amt
from (select pbfmatter, pbfprebill, sum(pbfamtonbill) as amt from prebillfeeitem inner join matter on pbfmatter=matsysnbr where matbillagreecode='R' and pbfprebill=cast('" + Prebill.ToString() + "' as int) and pbfmatter=cast('" + MatSys.ToString() + @"' as int) group by pbfmatter, pbfprebill)PB where pbmmatter=pbfmatter and pbmprebill=pbfprebill ";
_jurisUtility.ExecuteNonQueryCommand(0, SQL17);
string SQL20 = "update prebill set pbstatus=4, pbaction=1 where pbsysnbr=cast('" + Prebill.ToString() + "' as int)";
_jurisUtility.ExecuteNonQueryCommand(0, SQL20);
}
string SQLDT = "EXEC sp_MSforeachtable @command1='ALTER TABLE ? CHECK CONSTRAINT ALL'";
_jurisUtility.ExecuteNonQueryCommand(0, SQLDT);
string SQLPB = @"select cast(Prebill as varchar(20)) as Prebill, cast(matsysnbr as varchar(10)) as MatterSys,cast(billtobillingatty as varchar(20)) as BTkpr, cast(morig1 as varchar(20)) as OTkpr1,
cast(case when morig2 is null then '' else morig2 end as varchar(20)) as OTkpr2,
cast(case when morig3 is null then '' else morig3 end as varchar(20)) OTkpr3
,convert(varchar(30),cast(isnull(case when (retainertoallocate * OTPct1 * .01)>o1fees then (retainertoallocate * OTPct1 * .01) - o1fees else 0 end,0) as money),1) as OT1Alloc
,convert(varchar(30),cast(isnull(case when (retainertoallocate * OTPct2 * .01) >o2fees then (retainertoallocate * OTPct2 * .01) - o2fees else 0 end,0) as money),1) as OT2Alloc
,convert(varchar(30),cast(isnull(case when (retainertoallocate * OTPct3 * .01)>o3fees then (retainertoallocate * OTPct3 * .01) - o3fees else 0 end,0) as money),1) as OT3Alloc
from (select prebill as Prebill,billtobillingatty, matsysnbr, matfltfeeorretainer as retainer, sum(totalfees) as TotalFees, morig1, morig2, morig3, morig4,
sum(cast(case when totalfees is null then 0 else totalfees end as money)) as Total,
sum(cast(otherfees as money)) as Other, sum(cast(otkpr1Fees as money)) as O1Fees, ot1, case when cast(otpct1 as int)=0 then '' else cast(otpct1 as varchar(10)) end as OTPct1,
sum(cast(otkpr2Fees as money)) as O2Fees, ot2, case when cast(otpct2 as int)=0 then '' else cast(otpct2 as varchar(10)) end as OTPct2,
sum(cast(otkpr3Fees as money)) as O3Fees, ot3, case when cast(otpct3 as int)=0 then '' else cast(otpct3 as varchar(10)) end as OTPct3,
sum(cast(otkpr4Fees as money)) as O4Fees, ot4, case when cast(otpct4 as int)=0 then '' else cast(otpct4 as varchar(10)) end as OTPct4,
matfltfeeorretainer - sum(cast(case when totalfees is null then 0 else totalfees end as money)) as RetainerToAllocate
from (select pbmprebill as prebill, utmatter, utamount as TotalFees, case when uttkpr not in (select morigatty from matorigatty where morigmat=matsysnbr) then utamount else null end as OtherFees
,case when uttkpr=morig1 then utamount else null end as Otkpr1Fees, case when uttkpr=morig2 then utamount else null end as Otkpr2Fees
,case when uttkpr=morig3 then utamount else null end as Otkpr3Fees, case when uttkpr=morig4 then utamount else null end as Otkpr4Fees,morig1, morig2, morig3, morig4, ot1, ot2, ot3, ot4, otpct1, otpct2, otpct3, otpct4, null as Expenses
from unbilledtime
inner join prebillmatter on pbmmatter=utmatter
inner join prebillfeeitem on pbfutbatch=utbatch and pbfutrecnbr=utrecnbr
inner join matter on pbmmatter=matsysnbr
inner join billto on billtosysnbr = matbillto
inner join (select morigmat, max(case when rnk=1 then morigatty else '' end) as morig1, max(case when rnk=2 then morigatty else null end) as morig2, max(case when rnk=3 then morigatty else null end) as morig3
, max(case when rnk=4 then morigatty else null end) as morig4, max(case when rnk=1 then empinitials else null end) as OT1, max(case when rnk=2 then empinitials else null end) as OT2
, max(case when rnk=3 then empinitials else null end) as OT3, max(case when rnk=4 then empinitials else null end) as OT4, max(case when rnk=1 then cast(morigpcnt as int) else null end) as OTpct1
, max(case when rnk=2 then cast(morigpcnt as int) else null end) as OTpct2, max(case when rnk=3 then cast(morigpcnt as int) else null end) as OTpct3
, max(case when rnk=4 then cast(morigpcnt as int) else null end) as OTpct4
from (select morigmat, morigatty, morigpcnt, empinitials, rank() over (partition by morigmat order by case when billtobillingatty=morigatty then 0 else morigatty end) as rnk
from matorigatty
inner join employee on empsysnbr=morigatty
inner join matter on morigmat=matsysnbr
inner join billto on matbillto=billtosysnbr)MO
group by morigmat)MOrig on morigmat=matsysnbr
where matbillagreecode='R' and matfltfeeorretainer<>0 and matstatusflag='O' and billtobillingatty = " + billToAttyEmpSys
+ @" ) UT
inner join matter on matsysnbr=utmatter
inner join client on matclinbr=clisysnbr
inner join billto on matbillto=billtosysnbr
inner join prebill on prebill=pbsysnbr
where pbstatus<=2 and matbillagreecode='R' " +
" and (cast(billtobillingatty as varchar(20))<>cast(morig1 as varchar(20)) or " +
" cast(case when morig2 is null then '' else morig2 end as varchar(20))>'') " +
" group by matsysnbr, matfltfeeorretainer, ot1, ot2, ot3, ot4, otpct1, otpct2, otpct3, otpct4, prebill, clicode, matcode, matreportingname, clireportingname,morig1, morig2, morig3, morig4, billtobillingatty " +
" having matfltfeeorretainer - sum(cast(case when totalfees is null then 0 else totalfees end as money))>0 ) AllocTbl order by prebill";
DataSet myRS = _jurisUtility.RecordsetFromSQL(SQLPB);
dataGridView1.AutoGenerateColumns = true;
dataGridView1.DataSource = myRS.Tables[0];
toolStripStatusLabel.Text = "Processing Completed.";
statusStrip.Refresh();
}
else
{
string SQLDT = "EXEC sp_MSforeachtable @command1='ALTER TABLE ? CHECK CONSTRAINT ALL'";
_jurisUtility.ExecuteNonQueryCommand(0, SQLDT);
toolStripStatusLabel.Text = "No Records to Process.";
statusStrip.Refresh();
}
Cursor.Current = Cursors.Default;
Application.DoEvents();
}
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);
chosenDate = dateTimePicker1.Value.ToString(@"MM/dd/yyyy");
string SQLTkpr = "select * from employee where empid like '% %'";
DataSet myRSTkpr = _jurisUtility.RecordsetFromSQL(SQLTkpr);
if (myRSTkpr.Tables[0].Rows.Count > 0)
{
MessageBox.Show("This data has Employee IDS that contain spaces. This is considered invalid data" + "\r\n" + "and this tool will only work for validated data. Please contact Professional Services.", "Data Integrity Issue", MessageBoxButtons.OK, MessageBoxIcon.Stop);
}
else
{
Cursor.Current = Cursors.WaitCursor;
Application.DoEvents();
toolStripStatusLabel.Text = "Running";
statusStrip.Refresh();
SQLTkpr = getReportSQL();
DataSet report = _jurisUtility.RecordsetFromSQL(SQLTkpr);
ReportDisplay rpds = new ReportDisplay(report);
rpds.ShowDialog();
DialogResult result = MessageBox.Show("Would you like to process the data shown in the report?", "Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (result == System.Windows.Forms.DialogResult.Yes)
{
UpdateStatus("Beginning removal.", 1, 3);
Thread.Sleep(50);
string SQL = "delete from distributedreports" +
" where (convert(datetime,reportgeneratedtime,101) <= convert(datetime,'" + chosenDate + "', 101) and targetorganizationalunitid iN (select organizationalunit.id" +
" from organizationalunit inner join employee on code=empid where EmpId in (" + employees + "))) or targetorganizationalunitid = 1";
_jurisUtility.ExecuteNonQueryCommand(0, SQL);
UpdateStatus("Removing system Generated Reports.", 2, 3);
Thread.Sleep(50);
//delete items not associated with employees (system generated)
SQL = "delete from distributedreports" +
" where (convert(datetime,reportgeneratedtime,101) <= convert(datetime,'" + chosenDate + "', 101) and targetorganizationalunitid iN (select organizationalunit.id" +
" from organizationalunit where code not in (select empid from employee)))";
_jurisUtility.ExecuteNonQueryCommand(0, SQL);
UpdateStatus("Database Updated.", 3, 3);
MessageBox.Show("The process is complete", "Confirmation", MessageBoxButtons.OK, MessageBoxIcon.None);
Cursor.Current = Cursors.WaitCursor;
Application.DoEvents();
toolStripStatusLabel.Text = "Ready to Execute";
statusStrip.Refresh();
}
}
}
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 int CreateLedgerHistory(CreditMemo cm)
{
Cursor.Current = Cursors.WaitCursor;
toolStripStatusLabel.Text = "Creating Ledger History Record...";
statusStrip.Refresh();
Application.DoEvents();
int lastLH = 0;
string sqlB = "select SpNbrValue from sysparam where spname = 'LastSysNbrLH'";
DataSet spBatch = _jurisUtility.RecordsetFromSQL(sqlB);
DataTable dtB = spBatch.Tables[0];
if (dtB.Rows.Count == 0)
{
MessageBox.Show("Invalid sysparam data - LastSysNbrLH");
}
else
{
foreach (DataRow dr in dtB.Rows)
{
lastLH = Convert.ToInt32(dr["SpNbrValue"].ToString());
}
}
string SQL = "Insert into ledgerhistory( [LHSysNbr] ,[LHMatter] ,[LHBillNbr] ,[LHType] ,[LHDate] ,[LHPrdYear] ,[LHPrdNbr] ,[LHCashAmt] ,[LHFees] ,[LHCshExp] ,[LHNCshExp] ,[LHSurcharge] ,[LHTaxes1] ,[LHTaxes2] ,[LHTaxes3] ,[LHInterest] ,[LHComment]) VALUES (" +
(lastLH + 1).ToString() + "," + cm.mat + "," + cm.inv + ",8,'" + DateTime.Today + "', " + PYear + ", " + PNbr + ", 0.00, " + cm.fees * -1 + "," + cm.cashexp * -1 + ", " + cm.noncashexp * -1 + ", 0.00, 0.00, 0.00,0.00,0.00, 'Credit Memo Tool Write Off')";
_jurisUtility.ExecuteNonQueryCommand(0, SQL);
SQL = "Update ARMatalloc set ARMLHLink = " + (lastLH + 1).ToString() + ",ARMFeeAdj = (([ARMFeeBld] - [ARMFeeRcvd] + [ARMFeeAdj]) * -1) + ARMFeeAdj ,ARMCshExpAdj = (([ARMCshExpBld] - [ARMCshExpRcvd] + [ARMCshExpAdj]) * -1) + ARMCshExpAdj " +
" ,ARMNCshExpAdj = (([ARMNCshExpBld] - [ARMNCshExpRcvd] + [ARMNCshExpAdj]) * -1) + ARMNCshExpAdj ,[ARMBalDue] = 0 where ARMBillNbr = " + cm.inv + " and ARMMatter = " + cm.mat;
_jurisUtility.ExecuteNonQueryCommand(0, SQL);
SQL = "Update matter set MatAdjSinceLastBill = (MatAdjSinceLastBill + (" + cm.fees * -1 + "+" + cm.cashexp * -1 + "+ " + cm.noncashexp * -1 + ")) where matsysnbr = " + cm.mat;
_jurisUtility.ExecuteNonQueryCommand(0, SQL);
//MatAdjSinceLastBill = MatAdjSinceLastBill + adjustment
SQL = "Update sysparam set spnbrvalue=spnbrvalue + 1 where spname='LastSysNbrLH'";
_jurisUtility.ExecuteNonQueryCommand(0, SQL);
int lastBatchNo = 0;
sqlB = "select SpNbrValue from sysparam where spname = 'LastBatchCM'";
DataSet spBatch1 = _jurisUtility.RecordsetFromSQL(sqlB);
DataTable dtB1 = spBatch1.Tables[0];
if (dtB1.Rows.Count == 0)
{
MessageBox.Show("Invalid sysparam data - LastBatchCM");
}
else
{
foreach (DataRow dr in dtB1.Rows)
{
lastBatchNo = Convert.ToInt32(dr["SpNbrValue"].ToString());
}
}
string MYFolder = PYear + "-" + PNbr;
SQL = "Insert into creditmemobatch( [CMBBatchNbr] ,[CMBComment] ,[CMBStatus] ,[CMBRecCount] ,[CMBEnteredBy] ,[CMBDateEntered] ,[CMBLastOpenedBy] ,[CMBLastOpenedDate] ,[CMBJEBatchNbr]) VALUES (" +
lastBatchNo + 1 + ",'Write Off by JPS - Credit Memo Utility','U',1, (select empsysnbr from employee where EmpID = 'SMGR'), getdate(), " +
" (select empsysnbr from employee where EmpID = 'SMGR'), getdate(), null)";
_jurisUtility.ExecuteNonQueryCommand(0, SQL);
SQL = "Insert into creditmemo ( [CMBatchNbr] ,[CMRecNbr] ,[CMLHLink] ,[CMBillNbr] ,[CMMatter] ,[CMComment] ,[CMDate] ,[CMPrdYear] ,[CMPrdNbr] ,[CMPreAdjFee] ,[CMFeeAdj] ,[CMPreAdjCshExp] ,[CMCshExpAdj] ,[CMPreAdjNCshExp] ,[CMNCshExpAdj],[CMPreAdjSurchg] ,[CMSurchgAdj] ,[CMPreAdjTax1] ,[CMTax1Adj] ,[CMPreAdjTax2] ,[CMTax2Adj] ,[CMPreAdjTax3] ,[CMTax3Adj] ,[CMPreAdjInterest] ,[CMInterestAdj],[CMPrintOption] ,[CMNarrative]) VALUES (" +
lastBatchNo + 1 + ",1," + (lastLH + 1).ToString() + "," + cm.inv + ", " + cm.mat + ", 'Write off by JPS - Credit Memo Tool', getdate(), " + PYear + ", " + PNbr + "," + cm.fees + "," + cm.fees * -1 + "," + cm.cashexp + "," + cm.cashexp * -1 + ", " + cm.noncashexp + "," + cm.noncashexp * -1 + ", 0.00, 0.00, 0.00,0.00,0.00,0.00, 0.00, 0.00, 0.00, 0.00, 'A', 'Credit Memo Tool Write Off')";
_jurisUtility.ExecuteNonQueryCommand(0, SQL);
SQL = "Update arftaskalloc set ARFTAdj= ((arftactualamtbld - arftrcvd + arftadj ) * -1) + ARFTAdj" +
" where arftmatter=" + cm.mat + " and arftbillnbr=" + cm.inv;
_jurisUtility.ExecuteNonQueryCommand(0, SQL);
SQL = "select distinct ARFTTkpr from ARFTaskAlloc where ARFTBillNbr = " + cm.inv + " and ARFTMatter = " + cm.mat;
DataSet dds = _jurisUtility.RecordsetFromSQL(SQL);
foreach (DataRow rr in dds.Tables[0].Rows)
{
SQL = "Insert into CMFeeAlloc([CMFBatch] ,[CMFRecNbr] ,[CMFTkpr] ,[CMFBillNbr] ,[CMFMatter] ,[CMFPreAdj] ,[CMFAdj]) " +
" values (" + lastBatchNo + 1 + ", 1, " + rr[0].ToString() + ", " + cm.inv + ", " + cm.mat + ", (select sum(ARFTAdj) * -1 from ARFTaskAlloc where arftmatter=" + cm.mat + " and arftbillnbr=" + cm.inv + " and ARFTTkpr = " + rr[0].ToString() + ") , (select sum(ARFTAdj) from ARFTaskAlloc where arftmatter=" + cm.mat + " and arftbillnbr=" + cm.inv + " and ARFTTkpr = " + rr[0].ToString() + " ))";
_jurisUtility.ExecuteNonQueryCommand(0, SQL);
}
SQL = "Update arexpalloc set arepend= ((AREBldAmount - ARERcvd + AREAdj ) * -1) + arepend" +
" where AREMatter=" + cm.mat + " and AREBillNbr=" + cm.inv;
_jurisUtility.ExecuteNonQueryCommand(0, SQL);
SQL = "SELECT [AREBillNbr] ,[AREMatter],[AREExpCd] ,[AREExpType] ,([AREBldAmount] - [ARERcvd] + [AREAdj]) as total FROM [ARExpAlloc] where AREBillNbr = " + cm.inv + " and AREMatter = " + cm.mat;
dds.Clear();
dds = _jurisUtility.RecordsetFromSQL(SQL);
foreach (DataRow rr in dds.Tables[0].Rows)
{
SQL = "Insert into CMExpAlloc ([CMEBatch] ,[CMERecNbr] ,[CMEExpCd] ,[CMEExpType] ,[CMEBillNbr] ,[CMEMatter] ,[CMEPreAdj],[CMEAdj]) " +
" values (" + lastBatchNo + 1 + ", 1, '" + rr[2].ToString() + "', '" + rr[3].ToString() + "', " + rr[0].ToString() + ", " + rr[1].ToString() + ", " + rr[4].ToString() + ", " + (Convert.ToDouble(rr[4].ToString()) * -1).ToString()
+ " )";
_jurisUtility.ExecuteNonQueryCommand(0, SQL);
}
SQL = "Update sysparam set spnbrvalue=spnbrvalue + 1 where spname='LastBatchCM'";
_jurisUtility.ExecuteNonQueryCommand(0, SQL);
SQL = "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='LastSysNbrDocTree' then spnbrvalue else 0 end) as DTree,max(case when spname='CfgMiscOpts' then substring(sptxtvalue,14,1) else 0 end) as DOrder from sysparam";
DataSet myRSSysParm = _jurisUtility.RecordsetFromSQL(SQL);
DataTable dtSP = myRSSysParm.Tables[0];
if (dtSP.Rows.Count == 0)
{
MessageBox.Show("Incorrect SysParams");
}
else
{
foreach (DataRow dr in dtSP.Rows)
{
string LastSys = dr["DTree"].ToString();
DOrder = dr["DOrder"].ToString();
if (DOrder == "2")
{
string SPSql = "Select dtdocid from documenttree where dtparentid=37 and dtdocclass=5200 and dttitle='" + MYFolder + "'";
DataSet spMY = _jurisUtility.RecordsetFromSQL(SPSql);
DataTable dtMY = spMY.Tables[0];
if (dtMY.Rows.Count == 0)
{
string s2 = "Insert into documenttree(dtdocid, dtsystemcreated, dtdocclass, dtdoctype, dtparentid, dttitle) " +
"values((select max(dtdocid) + 1 from documenttree), 'Y', 5200,'F', 37,'" + MYFolder + "') ";
_jurisUtility.ExecuteNonQueryCommand(0, s2);
s2 = "Update sysparam set spnbrvalue=(select max(dtdocid) from documenttree) where spname='LastSysNbrDocTree'";
_jurisUtility.ExecuteNonQueryCommand(0, s2);
s2 = "Insert into documenttree(dtdocid, dtsystemcreated, dtdocclass, dtdoctype, dtparentid, dttitle) " +
"select (select max(dtdocid) from documenttree) + 1, 'Y', 5200,'F', dtdocid,'SMGR'" +
" from documenttree where dtparentid=37 and dttitle='" + MYFolder + "'";
_jurisUtility.ExecuteNonQueryCommand(0, s2);
s2 = "Update sysparam set spnbrvalue=(select max(dtdocid) from documenttree) where spname='LastSysNbrDocTree'";
_jurisUtility.ExecuteNonQueryCommand(0, s2);
s2 = "Insert into documenttree(dtdocid, dtsystemcreated, dtdocclass, dtdoctype, dtparentid, dttitle, dtkeyL) " +
"select (select max(dtdocid) from documenttree) + 1, 'Y', 5200,'R', " +
" (Select dtdocid from documenttree where dtparentid=(Select dtdocid from documenttree where dtparentid=37 and dttitle='" + MYFolder + "') and dttitle='SMGR')," +
"'JPS-Credit Memo Tool', " + lastBatchNo + 1;
_jurisUtility.ExecuteNonQueryCommand(0, s2);
s2 = "Update sysparam set spnbrvalue=(select max(dtdocid) from documenttree) where spname='LastSysNbrDocTree'";
_jurisUtility.ExecuteNonQueryCommand(0, s2);
}
else
{
string SMGRSql = "Select dtdocid from documenttree where dtparentid=(Select dtdocid from documenttree where dtparentid=37 and dttitle='" + MYFolder + "') and dttitle='SMGR'";
DataSet spSMGR = _jurisUtility.RecordsetFromSQL(SMGRSql);
DataTable dtSMGR = spSMGR.Tables[0];
if (dtSMGR.Rows.Count == 0)
{
string s2 = "Insert into documenttree(dtdocid, dtsystemcreated, dtdocclass, dtdoctype, dtparentid, dttitle) " +
"select (select max(dtdocid) from documenttree) + 1, 'Y', 5200,'F', dtdocid,'SMGR'" +
" from documenttree where dtparentid=37 and dttitle='" + MYFolder + "'";
_jurisUtility.ExecuteNonQueryCommand(0, s2);
s2 = "Update sysparam set spnbrvalue=(select max(dtdocid) from documenttree) where spname='LastSysNbrDocTree'";
_jurisUtility.ExecuteNonQueryCommand(0, s2);
s2 = "Insert into documenttree(dtdocid, dtsystemcreated, dtdocclass, dtdoctype, dtparentid, dttitle, dtkeyL) " +
"select (select max(dtdocid) from documenttree) + 1, 'Y', 5200,'R', " +
" (Select dtdocid from documenttree where dtparentid=(Select dtdocid from documenttree where dtparentid=37 and dttitle='" + MYFolder + "') and dttitle='SMGR')," +
"'JPS-Credit Memo Tool', " + lastBatchNo + 1;
_jurisUtility.ExecuteNonQueryCommand(0, s2);
s2 = "Update sysparam set spnbrvalue=(select max(dtdocid) from documenttree) where spname='LastSysNbrDocTree'";
_jurisUtility.ExecuteNonQueryCommand(0, s2);
}
else
{
string s2 = "Insert into documenttree(dtdocid, dtsystemcreated, dtdocclass, dtdoctype, dtparentid, dttitle, dtkeyL) " +
"select (select max(dtdocid) from documenttree) + 1, 'Y', 5200,'R', " +
" (Select dtdocid from documenttree where dtparentid=(Select dtdocid from documenttree where dtparentid=37 and dttitle='" + MYFolder + "') and dttitle='SMGR')," +
"'JPS-Credit Memo Tool', " + lastBatchNo + 1;
_jurisUtility.ExecuteNonQueryCommand(0, s2);
s2 = "Update sysparam set spnbrvalue=(select max(dtdocid) from documenttree) where spname='LastSysNbrDocTree'";
_jurisUtility.ExecuteNonQueryCommand(0, s2);
}
}
}
else
{
string SPSql = "Select dtdocid from documenttree where dtparentid=37 and dtdocclass=5200 and dttitle='SMGR'";
DataSet spMY = _jurisUtility.RecordsetFromSQL(SPSql);
DataTable dtMY = spMY.Tables[0];
if (dtMY.Rows.Count == 0)
{
string s2 = "Insert into documenttree(dtdocid, dtsystemcreated, dtdocclass, dtdoctype, dtparentid, dttitle) " +
"values ((select max(dtdocid) + 1 from documenttree), 'Y', 5200,'F', 37,'SMGR') ";
_jurisUtility.ExecuteNonQueryCommand(0, s2);
s2 = "Update sysparam set spnbrvalue=(select max(dtdocid) from documenttree) where spname='LastSysNbrDocTree'";
_jurisUtility.ExecuteNonQueryCommand(0, s2);
s2 = "Insert into documenttree(dtdocid, dtsystemcreated, dtdocclass, dtdoctype, dtparentid, dttitle) " +
"select (select max(dtdocid) from documenttree) + 1, 'Y', 5200,'F', dtdocid,'" + MYFolder + "'" +
" from documenttree where dtparentid=37 and dttitle='SMGR'";
_jurisUtility.ExecuteNonQueryCommand(0, s2);
s2 = "Update sysparam set spnbrvalue=(select max(dtdocid) from documenttree) where spname='LastSysNbrDocTree'";
_jurisUtility.ExecuteNonQueryCommand(0, s2);
s2 = "Insert into documenttree(dtdocid, dtsystemcreated, dtdocclass, dtdoctype, dtparentid, dttitle, dtkeyL) " +
"select (select max(dtdocid) from documenttree) + 1, 'Y', 5200,'R', " +
" (Select dtdocid from documenttree where dtparentid=(Select dtdocid from documenttree where dtparentid=37 and dttitle='SMGR') and dttitle='" + MYFolder + "')," +
"'JPS-Credit Memo Tool', " + lastBatchNo + 1;
_jurisUtility.ExecuteNonQueryCommand(0, s2);
s2 = "Update sysparam set spnbrvalue=(select max(dtdocid) from documenttree) where spname='LastSysNbrDocTree'";
_jurisUtility.ExecuteNonQueryCommand(0, s2);
}
else
{
string SMGRSql = "Select dtdocid from documenttree where dtparentid=(Select dtdocid from documenttree where dtparentid=37 and dttitle='SMGR') and dttitle='" + MYFolder + "'";
DataSet spSMGR = _jurisUtility.RecordsetFromSQL(SMGRSql);
DataTable dtSMGR = spSMGR.Tables[0];
if (dtSMGR.Rows.Count == 0)
{
string s2 = "Insert into documenttree(dtdocid, dtsystemcreated, dtdocclass, dtdoctype, dtparentid, dttitle) " +
"select (select max(dtdocid) from documenttree) + 1, 'Y', 5200,'F', dtdocid,'" + MYFolder + "'" +
" from documenttree where dtparentid=37 and dttitle='SMGR'";
_jurisUtility.ExecuteNonQueryCommand(0, s2);
s2 = "Update sysparam set spnbrvalue=(select max(dtdocid) from documenttree) where spname='LastSysNbrDocTree'";
_jurisUtility.ExecuteNonQueryCommand(0, s2);
s2 = "Insert into documenttree(dtdocid, dtsystemcreated, dtdocclass, dtdoctype, dtparentid, dttitle, dtkeyL) " +
"select (select max(dtdocid) from documenttree) + 1, 'Y', 5200,'R', " +
" (Select dtdocid from documenttree where dtparentid=(Select dtdocid from documenttree where dtparentid=37 and dttitle='SMGR')and dttitle='" + MYFolder + "')," +
"'JPS-Credit Memo Tool', " + lastBatchNo + 1;
_jurisUtility.ExecuteNonQueryCommand(0, s2);
s2 = "Update sysparam set spnbrvalue=(select max(dtdocid) from documenttree) where spname='LastSysNbrDocTree'";
_jurisUtility.ExecuteNonQueryCommand(0, s2);
}
else
{
string s2 = "Insert into documenttree(dtdocid, dtsystemcreated, dtdocclass, dtdoctype, dtparentid, dttitle, dtkeyL) " +
"select (select max(dtdocid) from documenttree) + 1, 'Y', 5200,'R', " +
" (Select dtdocid from documenttree where dtparentid=(Select dtdocid from documenttree where dtparentid=37 and dttitle='SMGR') and dttitle='" + MYFolder + "') ," +
"'JPS-Credit Memo Tool', " + lastBatchNo + 1;
_jurisUtility.ExecuteNonQueryCommand(0, s2);
s2 = "Update sysparam set spnbrvalue=(select max(dtdocid) from documenttree) where spname='LastSysNbrDocTree'";
_jurisUtility.ExecuteNonQueryCommand(0, s2);
}
}
}
}
}
return(lastBatchNo + 1);
}
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 DoDaFix()
{
string sql = "";
UpdateStatus("Updating TimeEntry...", 1, 7);
sql = "update TimeEntry set BillableFlag = 'N' " +
"where MatterSysNbr in (SELECT MatSysNbr FROM Matter where MatBillAgreeCode in ('N', 'B')) and entrysource = 'JurisGo'";
_jurisUtility.ExecuteNonQueryCommand(0, sql);
UpdateStatus("Updating TimeBatch...", 2, 7);
//timebatchdetail
sql = " update timebatchdetail set TBDBillableFlg = 'N' " +
" where timebatchdetail.tbdid in (" +
" select timebatchdetail.tbdid FROM [TimeEntry] " +
" inner join TimeEntryLink on timeentrylink.entryid = timeentry.entryid " +
" inner join timebatchdetail on timeentrylink.tbdid = timebatchdetail.tbdid " +
" inner join matter on matsysnbr = mattersysnbr " +
" where MatBillAgreeCode in ('N', 'B') and entrysource = 'JurisGo' and tbdrectype in (1,2)) ";
_jurisUtility.ExecuteNonQueryCommand(0, sql);
UpdateStatus("Updating UnbilledTime...", 3, 7);
//unbilledtime
sql = "update unbilledtime set UTBillableFlg = 'N' "
+ "from unbilledtime "
+ "inner join timeentrylink aa on utid = aa.tbdid "
+ "inner join timeentry bb on aa.entryid = bb.entryid "
+ "where MatterSysNbr in (SELECT MatSysNbr FROM Matter where MatBillAgreeCode in ('N', 'B')) and entrysource = 'JurisGo'";
_jurisUtility.ExecuteNonQueryCommand(0, sql);
UpdateStatus("Updating FeeSumByPrd...", 5, 7);
//feesumbyprd
sql = " update FeeSumByPrd set [FSPNonBilHrsEntered] = hhb.FSPBilHrsEntered,[FSPBilHrsEntered] = hhb.FSPBilHrsEntered " +
" ,[FSPFeeEnteredActualValue] = hhb.FSPFeeEnteredActualValue from " +
" ( " +
" select matter, yr, prd, tkpr, task, act, sum(FSPBilHrsEntered) as FSPBilHrsEntered, sum(FSPNonBilHrsEntered) as FSPNonBilHrsEntered, " +
" sum(FSPFeeEnteredActualValue) as FSPFeeEnteredActualValue " +
" from ( " +
" SELECT [UTMatter] as matter,[UTPrdYear] as yr,[UTPrdNbr] as prd,[UTTkpr] as tkpr,[UTTaskCd] as task ,[UTActivityCd] as act " +
" ,sum(case when UTBillableFlg = 'Y' then [UTHoursToBill] else 0 end) as FSPBilHrsEntered "+
" ,sum(case when UTBillableFlg = 'N' then [UTActualHrsWrk] else 0 end) as FSPNonBilHrsEntered "+
" ,sum([UTAmount]) as FSPFeeEnteredActualValue " +
" FROM [UnbilledTime] " +
" group by [UTMatter],[UTPrdYear],[UTPrdNbr] ,[UTTkpr] ,[UTTaskCd] ,[UTActivityCd] " +
" union all " +
" SELECT [BTMatter] as matter,[BTPrdYear] as yr,[BTPrdNbr] as prd,[BTWrkTkpr] as tkpr ,[BTTaskCd] as task ,[BTActivityCd] as act " +
" ,sum(case when BTBillableFlg = 'Y' then [BTHoursToBill] else 0 end) as FSPBilHrsEntered " +
" ,sum(case when BTBillableFlg = 'N' then [BTActualHrsWrk] else 0 end) as FSPNonBilHrsEntered "+
" ,sum([BTAmount]) as FSPFeeEnteredActualValue " +
" FROM [BilledTime] " +
" group by [BTMatter] ,[BTPrdYear],[BTPrdNbr] ,[BTWrkTkpr] ,[BTTaskCd] ,[BTActivityCd] ) llk " +
" where yr >= 2019 and matter in (SELECT MatSysNbr FROM Matter where MatBillAgreeCode in ('N', 'B')) " +
" group by matter, yr, prd, tkpr, task, act) hhb " +
" where [FSPMatter]= hhb.matter and [FSPPrdYear] = hhb.yr and [FSPPrdNbr] = hhb.prd and [FSPTkpr] = hhb.tkpr " +
" and [FSPTaskCd] = hhb.task and [FSPActivityCd] = hhb.act ";
_jurisUtility.ExecuteNonQueryCommand(0, sql);
UpdateStatus("Updating FeeSumITD...", 6, 7);
//feesumitd
sql = " update FeeSumITD set [FSINonBilHrsEntered] = hhb.FSPBilHrsEntered,[FSIBilHrsEntered] = hhb.FSPBilHrsEntered " +
" ,[FSIFeeEnteredActualValue] = hhb.FSPFeeEnteredActualValue from " +
" ( " +
" select matter, tkpr, sum(FSPBilHrsEntered) as FSPBilHrsEntered, sum(FSPNonBilHrsEntered) as FSPNonBilHrsEntered, " +
" sum(FSPFeeEnteredActualValue) as FSPFeeEnteredActualValue " +
" from ( " +
" SELECT [UTMatter] as matter,[UTTkpr] as tkpr " +
" ,sum(case when UTBillableFlg = 'Y' then [UTHoursToBill] else 0 end) as FSPBilHrsEntered "+
" ,sum(case when UTBillableFlg = 'N' then [UTActualHrsWrk] else 0 end) as FSPNonBilHrsEntered "+
" ,sum([UTAmount]) as FSPFeeEnteredActualValue " +
" , sum([UTHoursToBill]) as WIPHrs " +
" , sum([UTAmount]) as WIPBal " +
" FROM [UnbilledTime] " +
" group by [UTMatter],[UTTkpr] " +
" union all " +
" SELECT [BTMatter] as matter, [BTWrkTkpr] as tkpr " +
" ,sum(case when BTBillableFlg = 'Y' then [BTHoursToBill] else 0 end) as FSPBilHrsEntered " +
" ,sum(case when BTBillableFlg = 'N' then [BTActualHrsWrk] else 0 end) as FSPNonBilHrsEntered "+
" ,sum([BTAmount]) as FSPFeeEnteredActualValue " +
" , 0 as WIPHrs, 0 as WIPBal " +
" FROM [BilledTime] " +
" group by [BTMatter] ,[BTPrdYear],[BTPrdNbr] ,[BTWrkTkpr] ,[BTTaskCd] ,[BTActivityCd] ) llk " +
" where matter in (SELECT MatSysNbr FROM Matter where MatBillAgreeCode in ('N', 'B')) " +
" group by matter, tkpr) hhb " +
" where [FSIMatter]= hhb.matter and [FSITkpr] = hhb.tkpr ";
_jurisUtility.ExecuteNonQueryCommand(0, sql);
UpdateStatus("All tables updated.", 7, 7);
MessageBox.Show("The process is complete", "Confirmation", MessageBoxButtons.OK, MessageBoxIcon.None);
}
private void ApplyFirmName()
{
try
{
var sql = string.Format(Resources.SqlSetFirmNameSysParam, textBoxFirmName.Text);
if (_jurisUtility.ExecuteNonQueryCommand(0, sql) == 1)
{
sql = Resources.SqlApplyFirmName;
_jurisUtility.ExecuteNonQueryCommand(0, sql);
}
}
catch (Exception exception)
{
MessageBox.Show(this, exception.Message, @"Error setting firm name", MessageBoxButtons.OK,
MessageBoxIcon.Error);
}
}
private void DoDaFix()
{
if (attySelected && dateSelected)
{
DialogResult result = MessageBox.Show("You are about to remove all unbilled time and expense entries" + "\r\n" +
"for Originating Timekeeper: " + origAtty + " that occurred on or before" + "\r\n" +
"the following date: " + date + ". Are you sure?", "Confirmation Dialog", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (result == System.Windows.Forms.DialogResult.Yes)
{
string SQL = @"Select cast(count(*) as Varchar(10)) as PB from prebillmatter where pbmmatter in (select uematter from unbilledexpense where uedate<='" + date + "' and uematter in (select morigmat from matorigatty inner join employee on empsysnbr=morigatty where empinitials='" + origAtty + "'))" +
" or pbmmatter in (select utmatter from unbilledtime where utdate<='" + date + "' and utmatter in (select morigmat from matorigatty inner join employee on empsysnbr=morigatty where empinitials='" + origAtty + "'))";
DataSet myRSPB = _jurisUtility.RecordsetFromSQL(SQL);
getPostReportDataSet();
foreach (DataTable table in myRSPB.Tables)
{
foreach (DataRow dr in table.Rows)
{
string PBIndex = dr["PB"].ToString();
if (PBIndex == "0")
{ // 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);
UpdateStatus("Updating Time Detail Dist.", 0, 15);
SQL = "Delete from timedetaildist where tddid in (select utid from unbilledtime where utdate<='" + date + "' and utmatter in (select morigmat from matorigatty inner join employee on empsysnbr=morigatty where empinitials='" + origAtty + "'))";
_jurisUtility.ExecuteNonQueryCommand(0, SQL);
UpdateStatus("Updating Timekeeper Diary.", 1, 15);
SQL = "Delete from tkprdiary from timebatchdetail where tkprdiary.tdbatch=tbdbatch and tbdrecnbr=tdrecnbr and tbdid in (select utid from unbilledtime where utdate<='" + date + "' and utmatter in (select morigmat from matorigatty inner join employee on empsysnbr=morigatty where empinitials='" + origAtty + "'))";
_jurisUtility.ExecuteNonQueryCommand(0, SQL);
UpdateStatus("Updating Time Batch Detail.", 2, 15);
SQL = "Delete from timebatchdetail where tbdid in (select utid from unbilledtime where utdate<='" + date + "' and utmatter in (select morigmat from matorigatty inner join employee on empsysnbr=morigatty where empinitials='" + origAtty + "'))";
_jurisUtility.ExecuteNonQueryCommand(0, SQL);
UpdateStatus("Updating Fee Sum by Period.", 3, 15);
SQL = @"update feesumbyprd set fspworkedhrsentered=fspworkedhrsentered + hrs,
fspbilhrsentered=fspbilhrsentered + Bhrs, fspnonbilhrsentered=fspnonbilhrsentered + uhrs,
fspfeeenteredactualvalue=fspfeeenteredactualvalue + amt,
fspfeeenteredstdvalue=fspfeeenteredstdvalue+stdamt
from (select utmatter, uttkpr, isnull(uttaskcd,'') as taskcd, isnull(utactivitycd,'') as activitycd,
month(utdate) as utprdnbr,year(utdate) as utprdyear, sum(utactualhrswrk) * -1 as Hrs, sum(case when utbillableflg='Y' then utactualhrswrk else 0 end) * -1 as Bhrs,
sum(case when utbillableflg='N' then utactualhrswrk else 0 end) * -1 as UHrs, sum(utamount) * -1 as Amt, sum(utamtatstdrate) * -1 as StdAmt
from unbilledtime
where utdate<='" + date + "' and utmatter in (select morigmat from matorigatty inner join employee on empsysnbr=morigatty where empinitials='" + origAtty + "') group by utmatter, uttkpr, isnull(uttaskcd,''), isnull(utactivitycd,'') ,month(utdate), year(utdate)) UT where utmatter=fspmatter and uttkpr=fsptkpr and utprdnbr=fspprdnbr and utprdyear=fspprdyear and isnull(fsptaskcd,'')=taskcd and isnull(fspactivitycd,'')=activitycd and fspworkedhrsentered<>hrs";
_jurisUtility.ExecuteNonQueryCommand(0, SQL);
UpdateStatus("Updating Unbilled Time.", 4, 15);
SQL = "Delete from unbilledtime where utdate<='" + date + "' and utmatter in (select morigmat from matorigatty inner join employee on empsysnbr=morigatty where empinitials='" + origAtty + "')";
_jurisUtility.ExecuteNonQueryCommand(0, SQL);
UpdateStatus("Updating Expense Detail Dist.", 5, 15);
SQL = "Delete from expdetaildist where eddid in (select ueid from unbilledexpense where uedate<='" + date + "' and uematter in (select morigmat from matorigatty inner join employee on empsysnbr=morigatty where empinitials='" + origAtty + "'))";
_jurisUtility.ExecuteNonQueryCommand(0, SQL);
UpdateStatus("Updating Expense Batch Detail.", 5, 15);
SQL = "Delete from expbatchdetail where ebdid in (select ueid from unbilledexpense where uedate<='" + date + "' and uematter in (select morigmat from matorigatty inner join employee on empsysnbr=morigatty where empinitials='" + origAtty + "'))";
_jurisUtility.ExecuteNonQueryCommand(0, SQL);
UpdateStatus("Updating Unbilled Expense.", 6, 15);
SQL = "Delete from unbilledexpense where uedate<='" + date + "' and uematter in (select morigmat from matorigatty inner join employee on empsysnbr=morigatty where empinitials='" + origAtty + "')";
_jurisUtility.ExecuteNonQueryCommand(0, SQL);
UpdateStatus("Updating Time Batch Detail.", 7, 15);
SQL = "Delete from timebatchdetail where tbdid in (select tbdid from timeentrylink where entryid in (select entryid from timeentry where entrydate<='" + date + "' and mattersysnbr in (select morigmat from matorigatty inner join employee on empsysnbr=morigatty where empinitials='" + origAtty + "') and entrystatus<=7))";
_jurisUtility.ExecuteNonQueryCommand(0, SQL);
UpdateStatus("Updating Time Entry Table.", 8, 15);
SQL = "Delete from timeentry where entrydate<='" + date + "' and mattersysnbr in (select morigmat from matorigatty inner join employee on empsysnbr=morigatty where empinitials='" + origAtty + "') and entrystatus<=7";
_jurisUtility.ExecuteNonQueryCommand(0, SQL);
SQL = "Delete from timeentrylink where entryid not in (select entryid from timeentry)";
_jurisUtility.ExecuteNonQueryCommand(0, SQL);
UpdateStatus("Updating Expense Batch Detail.", 9, 15);
SQL = "Delete from expbatchdetail where ebdid in (select ebdid from expenseentrylink where entryid in (select entryid from expenseentry where entrydate<='" + date + "' and mattersysnbr in (select morigmat from matorigatty inner join employee on empsysnbr=morigatty where empinitials='" + origAtty + "') and entrystatus<=7))";
_jurisUtility.ExecuteNonQueryCommand(0, SQL);
UpdateStatus("Updating Expense Entry Table.", 10, 15);
SQL = "Delete from expenseentry where entrydate<='" + date + "' and mattersysnbr in (select morigmat from matorigatty inner join employee on empsysnbr=morigatty where empinitials='" + origAtty + "') and entrystatus<=7";
_jurisUtility.ExecuteNonQueryCommand(0, SQL);
SQL = "Delete from expenseentrylink where entryid not in (select entryid from expenseentry)";
_jurisUtility.ExecuteNonQueryCommand(0, SQL);
UpdateStatus("Updating Exp Sum by Period.", 11, 15);
SQL = @"update expsumbyprd set espentered=espentered + amt
from (select uematter, ueexpcd,ueprdnbr, ueprdyear, sum(ueamount) * -1 as Amt
from unbilledexpense
where uedate<='" + date + "' and uematter in (select morigmat from matorigatty inner join employee on empsysnbr=morigatty where empinitials='" + origAtty + @"')
group by uematter, ueexpcd,ueprdnbr, ueprdyear) UT
where uematter=espmatter and ueexpcd=espexpcd and ueprdnbr=espprdnbr and ueprdyear=espprdyear ";
_jurisUtility.ExecuteNonQueryCommand(0, SQL);
UpdateStatus("Updating Fee Sum ITD Table.", 12, 15);
SQL = "update feesumitd set fsicurunbilhrs=ut, fsicurunbilbal=ua from (select utmatter, sum(case when utbillableflg='T' then utactualhrswrk else 0 end) as UT, sum(utamount) as UA from unbilledtime group by utmatter) UT where fsimatter=utmatter ";
_jurisUtility.ExecuteNonQueryCommand(0, SQL);
UpdateStatus("Updating Fee Sum ITD Table.", 14, 15);
SQL = "update expsumitd set esicurunbilbal=ua from (select uematter, sum(ueamount) as ua from unbilledexpense group by uematter) UE where uematter=esimatter";
_jurisUtility.ExecuteNonQueryCommand(0, SQL);
UpdateStatus("Time and Expense Entries dated on and before " + date + " for Originating Timekeeper: " + origAtty + " have been deleted.", 5, 5);
DialogResult r = MessageBox.Show("The process is complete! Would you like to view the deleted items in a report?", "Process Complete!", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (r == DialogResult.Yes)
{
runPostReport();
}
}
else
{
MessageBox.Show("There are open prebills for matters in selection. Delete prebills before proceeding", "Selection Error", MessageBoxButtons.OK, MessageBoxIcon.Warning);
UpdateStatus("Application Cancelled. Open Prebills Exist.", 0, 4);
}
}
}
}
}
else
{
MessageBox.Show("Please select both a date and an originting attorney", "Selection Error", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
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(pathToExcelFile))
{
toolStripStatusLabel.Text = "Running. Please Wait...";
xlApp.Visible = false;
Workbook xlWorkbook = xlApp.Workbooks.Open(pathToExcelFile, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
_Worksheet xlWorksheet = (_Worksheet)xlWorkbook.Sheets[1];
Microsoft.Office.Interop.Excel.Range xlRange = xlWorksheet.UsedRange;
int rowCount = xlRange.Rows.Count;
Row currentRow = null;
for (int a = 2; a < rowCount; a++)
{
Microsoft.Office.Interop.Excel.Range range1 = xlWorksheet.Rows[a]; //For all columns in rows
//Range range1 = worksheet.Columns[1]; //for all rows in column 1
int col = 1;
currentRow = new Row(); //custom row class. I only use a few of the attributes but they are all programmed in the class if needed
foreach (Range r in range1.Cells) //range1.Cells represents all the columns/rows
{
currentRow.rowNumber = a;
if (col == 1)
{
currentRow.ID = Convert.ToString(r.Value);
}
else if (col == 2)
{
currentRow.matter = Convert.ToString(r.Value);
}
else if (col == 3)
{
currentRow.emp = Convert.ToString(r.Value);
}
else if (col == 4)
{
currentRow.tkpr = Convert.ToString(r.Value);
}
else if (col == 5)
{
currentRow.date = Convert.ToDateTime(r.Value);
}
else if (col == 6)
{
currentRow.hours = Convert.ToString(r.Value);
}
else if (col == 7)
{
currentRow.desc = Convert.ToString(r.Value);
}
else if (col == 8)
{
currentRow.oldTask = Convert.ToString(r.Value);
}
else if (col == 9)
{
currentRow.newTask = Convert.ToString(r.Value);
}
else if (col == 10)
{
currentRow.entryStatus = Convert.ToInt32(r.Value);
}
else if (col > 10)
{
break;
}
col++;
}
//if there IS an error (returns true)
if (anyErrorsInEidOrTaskCode(currentRow))
{
badRows.Add(currentRow);
}
else // no error so continue
{
//do sql stuff here
string SQL = "Update TimeEntry Set taskcode='" + currentRow.newTask.Trim() + "' where entryid=" + currentRow.ID.Trim();
_jurisUtility.ExecuteNonQueryCommand(0, SQL);
SQL = "Update Timebatchdetail Set tbdtaskcd='" + currentRow.newTask.Trim() + "' where tbdid=(select tbdid from timeentrylink where entryid=" + currentRow.ID.Trim() + ")";
_jurisUtility.ExecuteNonQueryCommand(0, SQL);
SQL = "Update UnbilledTime Set uttaskcd='" + currentRow.newTask.Trim() + "' where utid=(select tbdid from timeentrylink where entryid=" + currentRow.ID.Trim() + ")";
_jurisUtility.ExecuteNonQueryCommand(0, SQL);
}
}
UpdateStatus("All Task codes updated.", 1, 1);
toolStripStatusLabel.Text = "Status: Ready to Execute";
if (badRows.Count == 0)
{
MessageBox.Show("The process is complete without error", "Confirmation", MessageBoxButtons.OK, MessageBoxIcon.None);
}
else
{
DialogResult rr = MessageBox.Show("The process is complete but there were" + "\r\n" + "errors. Would you like to see them?", "Errors", MessageBoxButtons.YesNo, MessageBoxIcon.None);
if (rr == DialogResult.Yes)
{
DataSet ds = displayErrors();
ReportDisplay rpds = new ReportDisplay(ds);
rpds.Show();
}
}
xlWorkbook.Close(false, Type.Missing, Type.Missing);
xlApp.Quit();
}
else
{
MessageBox.Show("Please browse to your Excel file first", "Selection Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}
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 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()
{
// 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()
{
Cursor.Current = Cursors.WaitCursor;
Application.DoEvents();
toolStripStatusLabel.Text = "Executing Matter Closer....";
statusStrip.Refresh();
UpdateStatus("Executing Matter Closer", 0, 5);
string TBSql = @"Select case when wip <> 0.00 or UnbilledTime <> 0 or UnbilledExp <> 0 or UnpostedTime <> 0 or UnpostedExp <> 0 or UnpostedVouchers <> 0 or OpenVouchers <> 0 or openbills <> 0 or AR <> 0.00 or PPD <> 0.00 or trust <> 0.00 then 'Unresolved - Review Exception Report' else 'Ready to Close' end as MatterStatus, dbo.jfn_formatclientcode(clicode) as Client, Clireportingname, dbo.jfn_formatmattercode(matcode) as Matter,MatPracticeClass as PC,
Matreportingname, empinitials as BillTkpr, OrigTkpr, matsysnbr,convert(varchar(10),matdateopened,101) as OpenDate,
case when matstatusflag='O' then 'Open' when matstatusflag='F' then 'Final Bill Sent' else matstatusflag end as Status,
case when convert(varchar(10), matdatelastwork,101)='01/01/1900' then 'Never' else convert(varchar(10), matdatelastwork,101) end as LastWorked,
case when convert(varchar(10), matdatelastbill,101)='01/01/1900' then 'Never' else convert(varchar(10), matdatelastbill,101) end as LastBIlled,
case when convert(varchar(10), matdatelastexp,101)='01/01/1900' then 'Never' else convert(varchar(10), matdatelastexp,101) end as LastExpense,
case when convert(varchar(10), matdatelastpaymt,101)='01/01/1900' then 'Never' else convert(varchar(10), matdatelastpaymt,101) end as LastPayment, unbilledtime, unbilledexp, unpostedtime, unpostedexp, unpostedvouchers, openvouchers, openbills, wip, ar, ppd, trust,
case when wip <> 0.00 or UnbilledTime <> 0 or UnbilledExp <> 0 or UnpostedTime <> 0 or UnpostedExp <> 0 or UnpostedVouchers <> 0 or OpenVouchers <> 0 or openbills <> 0 or AR <> 0.00 or PPD <> 0.00 or trust <> 0.00 then 1 else 0 end as Unresolved,
case when wip <> 0.00 or UnbilledTime <> 0 or UnbilledExp <> 0 or UnpostedTime <> 0 or UnpostedExp <> 0 or UnpostedVouchers <> 0 or OpenVouchers <> 0 or openbills <> 0 or AR <> 0.00 or PPD <> 0.00 or trust <> 0.00 then 0 else 1 end as ReadytoClose
from matter
inner join client on matclinbr=clisysnbr
inner join billto on matbillto=billtosysnbr
inner join employee on empsysnbr=billtobillingatty
inner join (select morigmat, max(case when ot=1 then empinitials else '' end) + max(case when ot=2 then ' ' + empinitials else '' end) +
max(case when ot=3 then ' ' + empinitials else '' end) + max(case when ot=4 then ' ' + empinitials else '' end) + max(case when ot=5 then ' ' + empinitials else '' end) as OrigTkpr
from (select morigmat, empinitials, rank() over (Partition by morigmat order by empinitials) as OT
from matorigatty inner join employee on morigatty=empsysnbr)MO
group by morigmat)MO on matsysnbr=morigmat
inner join (select matsysnbr as matsys, sum(unbilledtime) as UnbilledTime, sum(UnbilledExp) as UnbilledExp, sum(unpostedtime) as UnpostedTime, sum(unpostedexp) as UnpostedExp,
sum(unpostedvouchers) as UnpostedVouchers, sum(openVouchers) as OpenVouchers, sum(openBills) as OpenBills, sum(wipbalance) as WIp, sum(arbalance) as AR, sum(ppdbalance) as PPD, sum(trustbalance) as trust
from (select matsysnbr, 0 as UnbilledTime, 0 as UnbilledExp, 0 as UnpostedTime, 0 as UnpostedExp, 0 as UnpostedVouchers, 0 as OpenVouchers,0 as OpenBills, 0 as WIPBalance,0 as ARBalance, matppdbalance as PPDBalance, 0 as TrustBalance
from matter
union all select armmatter, 0, 0, 0, 0, 0, 0, count(armbillnbr) as OpenBills, 0 as WIp, sum(armbaldue) as ARBalance, 0 as PPD, 0 as Trust from armatalloc where armbaldue<>0 group by armmatter
union all select utmatter, count(utid), 0, 0, 0, 0, 0, 0, sum(utamount), 0, 0, 0 from unbilledtime group by utmatter union all
select uematter, 0, count(ueid), 0,0,0,0,0,sum(ueamount), 0, 0, 0 from unbilledexpense group by uematter
union all select tbdmatter, 0,0, count(tbdid),0,0,0,0,0,0,0,0 from timebatchdetail where tbdposted='N' and tbdid not in (select tbdid from timeentrylink) group by tbdmatter
union all select mattersysnbr, 0,0, count(entryid),0,0,0,0,0,0,0,0 from timeentry where entrystatus<=6 group by mattersysnbr
union all select ebdmatter, 0,0, 0,count(ebdid),0,0,0,0,0,0,0 from expbatchdetail where ebdposted='N' and ebdid not in (select ebdid from ExpenseEntryLink) group by ebdmatter
union all select mattersysnbr, 0,0, 0,count(entryid),0,0,0,0,0,0,0 from expenseentry where entrystatus<=6 group by mattersysnbr
union all select vbmmatter, 0,0, 0,0,count(vbdid) as VchCount,0,0,0,0,0,0 from voucherbatchmatdist inner join voucherbatchdetail on vbdbatch=vbmbatch and vbdrecnbr=vbmrecnbr where vbdposted='N' group by vbmmatter
union all select vmmatter,0,0, 0,0,0, count(vmvoucher) as Vch,0,0,0,0,0 from voucher inner join vouchermatdist on vmvoucher=vchvouchernbr where vchstatus='O' and vmamount-vmamountpaid<>0 group by vmmatter
union all select tamatter,0,0,0,0,0,0,0,0,0,0, sum(tabalance) from trustaccount group by tamatter) Mat group by matsysnbr)MatList on matsysnbr=matsys";
TBSql = TBSql + @" where (matstatusflag='O' or matstatusflag='F') ";
if (rbDays.Checked == true)
{
string DaySince = cbDays.Text;
TBSql = TBSql + @" and DATEDIFF(d, Matter.MatDateLastWork, GETDATE())>" + DaySince.ToString() +
" and DATEDIFF(d, Matter.MatDateLastExp, GETDATE())>" + DaySince.ToString() +
" and DATEDIFF(d, Matter.MatDateLastBill, GETDATE())>" + DaySince.ToString() +
" and DATEDIFF(d, Matter.MatDateLastPaymt, GETDATE())>" + DaySince.ToString();
}
else
{
string dt = dtSince.Text;
TBSql = TBSql + @" and (Matter.MatDateLastWork)<convert(datetime,'" + dt.ToString() + "',101) " +
" and (Matter.MatDateLastExp)<convert(datetime,'" + dt.ToString() + "',101) " +
" and (Matter.MatDateLastBill)<convert(datetime,'" + dt.ToString() + "',101) " +
" and (Matter.MatDateLastPaymt)<convert(datetime,'" + dt.ToString() + "',101) ";
}
TBSql = TBSql + getTimeKeepers() + getClients() + getPCs() + getdtException() + getbtException() + getCliException();
TBSql = TBSql + @" order by case when wip <> 0.00 or UnbilledTime <> 0 or UnbilledExp <> 0 or UnpostedTime <> 0 or UnpostedExp <> 0 or UnpostedVouchers <> 0 or OpenVouchers <> 0 or openbills <> 0 or AR <> 0.00 or PPD <> 0.00 or trust <> 0.00 then 'Unresolved - Review Exception Report' else 'Ready to Close' end, Clicode, Matcode";
DataSet exceptions = _jurisUtility.RecordsetFromSQL(TBSql);
ReportDisplay rpds = new ReportDisplay(exceptions);
rpds.ShowDialog();
object sumExcept;
object sumToClose;
sumExcept = exceptions.Tables[0].Compute("sum(unresolved)", string.Empty);
sumToClose = exceptions.Tables[0].Compute("sum(ReadytoClose)", string.Empty);
string message = sumExcept.ToString() + " matters cannot be closed due to open transactions. These can be reviewed on the exceptions report. " + sumToClose.ToString() + " matters will be closed. Do you wish to continue?";
string caption = "Matters to Close";
MessageBoxButtons buttons = MessageBoxButtons.YesNo;
DialogResult result;
// Displays the MessageBox.
result = MessageBox.Show(this, message, caption, buttons,
MessageBoxIcon.Question, MessageBoxDefaultButton.Button1);
if (result == DialogResult.Yes)
{
Cursor.Current = Cursors.WaitCursor;
Application.DoEvents();
toolStripStatusLabel.Text = "Executing Matter Closer....";
statusStrip.Refresh();
UpdateStatus("Executing Matter Closer", 1, 5);
string MatterSql = @"update matter set matstatusflag='C',matlockflag=3, matdateclosed=cast(getdate() as date) where matsysnbr in (select matsysnbr from matter
inner join client on matclinbr=clisysnbr
inner join billto on matbillto=billtosysnbr
inner join employee on empsysnbr=billtobillingatty
inner join (select morigmat, max(case when ot=1 then empinitials else '' end) + max(case when ot=2 then ' ' + empinitials else '' end) +
max(case when ot=3 then ' ' + empinitials else '' end) + max(case when ot=4 then ' ' + empinitials else '' end) + max(case when ot=5 then ' ' + empinitials else '' end) as OrigTkpr
from (select morigmat, empinitials, rank() over (Partition by morigmat order by empinitials) as OT
from matorigatty inner join employee on morigatty=empsysnbr)MO
group by morigmat)MO on matsysnbr=morigmat
inner join (select matsysnbr as matsys, sum(unbilledtime) as UnbilledTime, sum(UnbilledExp) as UnbilledExp, sum(unpostedtime) as UnpostedTime, sum(unpostedexp) as UnpostedExp,
sum(unpostedvouchers) as UnpostedVouchers, sum(openVouchers) as OpenVouchers, sum(openBills) as OpenBills, sum(wipbalance) as WIp, sum(arbalance) as AR, sum(ppdbalance) as PPD, sum(trustbalance) as trust
from (select matsysnbr, 0 as UnbilledTime, 0 as UnbilledExp, 0 as UnpostedTime, 0 as UnpostedExp, 0 as UnpostedVouchers, 0 as OpenVouchers,0 as OpenBills, 0 as WIPBalance,0 as ARBalance, matppdbalance as PPDBalance, 0 as TrustBalance
from matter
union all select armmatter, 0, 0, 0, 0, 0, 0, count(armbillnbr) as OpenBills, 0 as WIp, sum(armbaldue) as ARBalance, 0 as PPD, 0 as Trust from armatalloc where armbaldue<>0 group by armmatter
union all select utmatter, count(utid), 0, 0, 0, 0, 0, 0, sum(utamount), 0, 0, 0 from unbilledtime group by utmatter union all
select uematter, 0, count(ueid), 0,0,0,0,0,sum(ueamount), 0, 0, 0 from unbilledexpense group by uematter
union all select tbdmatter, 0,0, count(tbdid),0,0,0,0,0,0,0,0 from timebatchdetail where tbdposted='N' and tbdid not in (select tbdid from timeentrylink) group by tbdmatter
union all select mattersysnbr, 0,0, count(entryid),0,0,0,0,0,0,0,0 from timeentry where entrystatus<=6 group by mattersysnbr
union all select ebdmatter, 0,0, 0,count(ebdid),0,0,0,0,0,0,0 from expbatchdetail where ebdposted='N' and ebdid not in (select ebdid from ExpenseEntryLink) group by ebdmatter
union all select mattersysnbr, 0,0, 0,count(entryid),0,0,0,0,0,0,0 from expenseentry where entrystatus<=6 group by mattersysnbr
union all select vbmmatter, 0,0, 0,0,count(vbdid) as VchCount,0,0,0,0,0,0 from voucherbatchmatdist inner join voucherbatchdetail on vbdbatch=vbmbatch and vbdrecnbr=vbmrecnbr where vbdposted='N' group by vbmmatter
union all select vmmatter,0,0, 0,0,0, count(vmvoucher) as Vch,0,0,0,0,0 from voucher inner join vouchermatdist on vmvoucher=vchvouchernbr where vchstatus='O' and vmamount-vmamountpaid<>0 group by vmmatter
union all select tamatter,0,0,0,0,0,0,0,0,0,0, sum(tabalance) from trustaccount group by tamatter) Mat group by matsysnbr)MatList on matsysnbr=matsys ";
;
MatterSql = MatterSql + @" where (matstatusflag='O' or matstatusflag='F') and wip= 0.00 and UnbilledTime= 0 and UnbilledExp= 0 and UnpostedTime= 0 and UnpostedExp= 0 and UnpostedVouchers= 0 and OpenVouchers= 0 and openbills= 0 and AR= 0.00 and PPD= 0.00 and trust = 0.00";
if (rbDays.Checked == true)
{
string DaySince = cbDays.Text;
MatterSql = MatterSql + @" and DATEDIFF(d, Matter.MatDateLastWork, GETDATE())>" + DaySince.ToString() +
" and DATEDIFF(d, Matter.MatDateLastExp, GETDATE())>" + DaySince.ToString() +
" and DATEDIFF(d, Matter.MatDateLastBill, GETDATE())>" + DaySince.ToString() +
" and DATEDIFF(d, Matter.MatDateLastPaymt, GETDATE())>" + DaySince.ToString();
}
else
{
string dt = dtSince.Text;
MatterSql = MatterSql + @" and (Matter.MatDateLastWork)<convert(datetime,'" + dt.ToString() + "',101) " +
" and (Matter.MatDateLastExp)<convert(datetime,'" + dt.ToString() + "',101) " +
" and (Matter.MatDateLastBill)<convert(datetime,'" + dt.ToString() + "',101) " +
" and (Matter.MatDateLastPaymt)<convert(datetime,'" + dt.ToString() + "',101) ";
}
MatterSql = MatterSql + getTimeKeepers() + getClients() + getPCs() + getdtException() + getbtException() + getCliException() + ") ";
_jurisUtility.ExecuteNonQueryCommand(0, MatterSql);
Cursor.Current = Cursors.WaitCursor;
Application.DoEvents();
toolStripStatusLabel.Text = "Executing Matter Closer....";
statusStrip.Refresh();
UpdateStatus("Executing Matter Closer", 4, 5);
DialogResult r3 = MessageBox.Show(sumToClose.ToString() + " matters closed.",
"Matters Closed", MessageBoxButtons.OK,
MessageBoxIcon.Information,
MessageBoxDefaultButton.Button1);
Cursor.Current = Cursors.Default;
Application.DoEvents();
toolStripStatusLabel.Text = "Matter Closer Completed...." + sumToClose.ToString() + " matters closed. " + sumExcept.ToString() + " matters could not be closed due to exceptions.";
statusStrip.Refresh();
UpdateStatus("Matter Closer Completed", 5, 5);
}
}
private void runUpdateSQL(List <ThousandMatters> matters, bool runAll)//uses Matterlist
{
DataSet finalDS = new DataSet();
if ((matters == null && runAll) || matters.Count != 0)
{
string reportSQL = "";
//if matter and billing timekeeper
if (runAll)
{
reportSQL = "select Clicode, Clireportingname, Matcode, Matreportingname" +
" from matter" +
" inner join client on matclinbr=clisysnbr where MatStatusFlag <> 'C'";
}
else
{
foreach (ThousandMatters tt in matters)
{
reportSQL = reportSQL = "select Clicode, Clireportingname, Matcode, Matreportingname" +
" from matter" +
" inner join client on matclinbr=clisysnbr where matsysnbr in (" + tt.matters + ")" +
"union all";
}
reportSQL = reportSQL.Substring(0, reportSQL.Length - "union all".Length);
}
DataSet report = _jurisUtility.RecordsetFromSQL(reportSQL);
finalDS.Merge(report);
finalDS.Tables[0].DefaultView.Sort = "Clicode";
ReportDisplay rpds = new ReportDisplay(finalDS);
rpds.ShowDialog();
if (areAnyCheckBoxesChecked())
{
string options = returnSQLUpdateString();
DialogResult dialog = MessageBox.Show("This tool will update all BillCopy Settings for all matters associated" + "\r\n" + "with the selections. This cannot be undone. Are you sure?", "Confirmation Dialog", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (dialog == System.Windows.Forms.DialogResult.Yes)
{
string SQL = "";
if (runAll)
{
SQL = "update C set " + options + " from billcopy C inner join billto T on T.BillToSysNbr = C.BilCpyBillTo inner join matter M on M.matbillto=T.billtosysnbr where MatStatusFlag <> 'C'";
_jurisUtility.ExecuteNonQueryCommand(0, SQL);
}
else
{
int count = 0;
foreach (ThousandMatters gg in matters)
{
SQL = "update C set " + options + " from billcopy C inner join billto T on T.BillToSysNbr = C.BilCpyBillTo inner join matter M on M.matbillto=T.billtosysnbr where matsysnbr in (" + gg.matters + ")";
_jurisUtility.ExecuteNonQueryCommand(0, SQL);
count++;
UpdateStatus("Updating", count, matters.Count);
}
}
UpdateStatus("Finished", 1, 1);
MessageBox.Show("The process is complete", "Confirmation", MessageBoxButtons.OK, MessageBoxIcon.None);
}
}
else
{
MessageBox.Show("There are no checkboxes checked. Please update your selection.", "No selections made", MessageBoxButtons.OK, MessageBoxIcon.None);
}
}
else
{
MessageBox.Show("This timekeeper or the selected client(s) have no matters associated" + "\r\n" + "with them so there is nothing to process. Please update your selection.", "No Matters to Process", MessageBoxButtons.OK, MessageBoxIcon.None);
}
}
