private void buttonReport_Click(object sender, EventArgs e)
{
// if (string.IsNullOrEmpty(toAtty) || string.IsNullOrEmpty(fromAtty))
// MessageBox.Show("Please select from both Timekeeper drop downs", "Selection Error");
// else
// {
//generates output of the report for before and after the change will be made to client
if (!rbAll.Checked && !rbOne.Checked)
{
MessageBox.Show("Please select at least one checkbox", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else if (rbOne.Checked && String.IsNullOrEmpty(txtPrebill.Text))
{
MessageBox.Show("Please type in the prebill number", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
string SQLTkpr = getReportSQL();
DataSet myRSTkpr = _jurisUtility.RecordsetFromSQL(SQLTkpr);
ReportDisplay rpds = new ReportDisplay(myRSTkpr);
rpds.Show();
}
}
private void showReport()
{
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);
ReportDisplay rpd = new ReportDisplay(ds);
rpd.Show();
}
private void buttonReport_Click(object sender, EventArgs e)
{
// if (string.IsNullOrEmpty(toAtty) || string.IsNullOrEmpty(fromAtty))
// MessageBox.Show("Please select from both Timekeeper drop downs", "Selection Error");
// else
// {
//generates output of the report for before and after the change will be made to client
string SQLTkpr = getReportSQL();
DataSet myRSTkpr = _jurisUtility.RecordsetFromSQL(SQLTkpr);
ReportDisplay rpds = new ReportDisplay(myRSTkpr);
rpds.Show();
// }
}
private void buttonReport_Click(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(toFeeSched) || string.IsNullOrEmpty(fromFeeSched))
{
MessageBox.Show("Please select from both Fee Schedule drop downs", "Selection Error");
}
else
{
//generates output of the report for before and after the change will be made to client
string SQLTkpr = getReportSQL();
DataSet ds = _jurisUtility.RecordsetFromSQL(SQLTkpr);
ReportDisplay rpds = new ReportDisplay(ds);
rpds.Show();
}
}
private void showFinish()
{
UpdateStatus("Client(s)/Matter(s) updated.", 1, 1);
if (errorList.Count == 0)
{
MessageBox.Show("The process is complete and there were no errors.", "Confirmation", MessageBoxButtons.OK, MessageBoxIcon.None);
}
else
{
DialogResult ff = MessageBox.Show("The process is complete but there were errors." + "\r\n" + "Would you like to see the Error Log?", "Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (ff == DialogResult.Yes)
{
ReportDisplay rd = new ReportDisplay(errorList);
rd.showErrors();
rd.Show();
}
}
}
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);
DialogResult dt = MessageBox.Show("Splits for selected client/matters will be removed. Do you wish to continue?",
"Split Removal Confirmation", MessageBoxButtons.YesNo);
if (dt == DialogResult.Yes)
{
string singleClient = this.cbClient.GetItemText(this.cbClient.SelectedItem).Split(' ')[0];
string singleMatter = this.cbMatter.GetItemText(this.cbMatter.SelectedItem).Split(' ')[0];
string s2 = "select dbo.jfn_formatclientcode(clicode) as Client, clireportingname as ClientName, dbo.jfn_formatmattercode(matcode) as Matter, Matreportingname as MatterName, pbmprebill as Prebill from prebillmatter inner join matter on pbmmatter=matsysnbr inner join client on matclinbr=clisysnbr where (clicode like '%' + '" + singleClient + "' or '" + singleClient + "'='*') and " +
" (matcode like '%' + '" + singleMatter + "' or '" + singleMatter + "'='*') ";
DataSet d2 = _jurisUtility.RecordsetFromSQL(s2);
if (d2.Tables[0].Rows.Count == 0)
{
DataTable dvgSource = (DataTable)dataGridView1.DataSource;
int dvgRow = dvgSource.Rows.Count;
int i = 1;
foreach (DataRow dg in dvgSource.Rows)
{
string frommat = dg["SplitFromMat"].ToString();
string tomat = dg["SplitToMat"].ToString();
string CCode = dg["Client"].ToString();
string MCode = dg["Matter"].ToString();
Cursor.Current = Cursors.WaitCursor;
string dsql = "Delete from splitbill where splitfrommat=cast(" + frommat.ToString() + " as int)";
_jurisUtility.ExecuteNonQuery(0, dsql);
string ssql = "update matter Set matsplitmethod=0 where matsysnbr=cast(" + frommat.ToString() + " as int)";
_jurisUtility.ExecuteNonQuery(0, ssql);
toolStripStatusLabel.Text = "Splits for Client/Matter(s) " + CCode + "/" + MCode + " removed.";
statusStrip.Refresh();
UpdateStatus("Split Removal Complete", i, dvgRow);
i = i + 1;
}
Cursor.Current = Cursors.Default;
toolStripStatusLabel.Text = "Splits for Client/Matter(s) " + singleClient + "/" + singleMatter + " removed.";
statusStrip.Refresh();
UpdateStatus("Split Removal Complete", 1, 1);
WriteLog("Split Removal Client/Matter(s) " + singleClient + "/" + singleMatter + " " + DateTime.Now.ToShortDateString());
Application.DoEvents();
MessageBox.Show("Splits removed for Client/Matter(s) " + singleClient + "/" + singleMatter);
cbClient.SelectedIndex = -1;
cbMatter.SelectedIndex = -1;
dataGridView1.DataSource = null;
dataGridView1.Rows.Clear();
cbClient.SelectedIndex = -1;
string CliIndex;
cbClient.ClearItems();
string SQLCli = "select Client from (select '* All' as Client union all select dbo.jfn_formatclientcode(clicode) + ' ' + clireportingname as Client from Client where clisysnbr in (select matclinbr from matter where matsysnbr in (select splitfrommat from splitbill))) CLI order by Client";
DataSet myRSCli = _jurisUtility.RecordsetFromSQL(SQLCli);
if (myRSCli.Tables[0].Rows.Count == 0)
{
cbClient.SelectedIndex = 0;
}
else
{
foreach (DataTable table in myRSCli.Tables)
{
foreach (DataRow dr in table.Rows)
{
CliIndex = dr["Client"].ToString();
cbClient.Items.Add(CliIndex);
}
}
}
}
else
{
DialogResult dt3 = MessageBox.Show("Open prebills exist for one or more selected client/matters. These must be deleted before proceeding. Click yes to view a list of open prebills.",
"Open Prebill Alert", MessageBoxButtons.YesNo);
if (dt3 == DialogResult.Yes)
{ //generates output of the report for before and after the change will be made to client
ReportDisplay rpds = new ReportDisplay(d2);
rpds.Show();
}
else
{
Cursor.Current = Cursors.Default;
toolStripStatusLabel.Text = "Process Cancelled";
statusStrip.Refresh();
UpdateStatus("Process Cancelled", 0, 0);
Application.DoEvents();
}
}
}
else
{
Cursor.Current = Cursors.Default;
toolStripStatusLabel.Text = "Process Cancelled";
statusStrip.Refresh();
UpdateStatus("Process Cancelled", 0, 0);
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);
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 btExceptionRpt_Click(object sender, EventArgs e)
{
string SQLExc = @"Select 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
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 ";
SQLExc = SQLExc + @" where (matstatusflag='O' or matstatusflag='F') and (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) ";
if (rbDays.Checked == true)
{
string DaySince = cbDays.Text;
SQLExc = SQLExc + @" 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;
SQLExc = SQLExc + @" 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) ";
}
SQLExc = SQLExc + getTimeKeepers() + getClients() + getPCs() + getdtException() + getbtException() + getCliException();
SQLExc = SQLExc + @" order by clicode, matcode";
//TextWriter write = new StreamWriter(@"c:\intel\sql1.txt");
// write.WriteLine(SQLExc);
// write.Flush();
// write.Close();
DataSet exceptions = _jurisUtility.RecordsetFromSQL(SQLExc);
ReportDisplay rpds = new ReportDisplay(exceptions);
rpds.Show();
}
private void runPostReport()
{
ReportDisplay rpds = new ReportDisplay(time, expense);
rpds.Show();
}
