private void DoDaFix()
{
OpenFileDialog dlg = new OpenFileDialog();
if (dlg.ShowDialog() == DialogResult.OK)
{
updateClients(dlg.FileName.ToString());
updateMatters(dlg.FileName.ToString());
}
DialogResult dr;
UpdateStatus("All Formats Updated.", 1, 1);
if (cliErrors.Tables[0].Rows.Count == 0 && matErrors.Tables[0].Rows.Count == 0)
{
MessageBox.Show("The process is complete and there werre no errors", "Confirmation", MessageBoxButtons.OK, MessageBoxIcon.None);
}
else
{
dr = MessageBox.Show("The process is complete but there were errors" + "\r\n" + "Would you like to see the error log?", "Error Log Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.None);
if (dr == DialogResult.Yes)
{
ReportDisplay rpt = new ReportDisplay(cliErrors, matErrors);
rpt.ShowDialog();
}
}
}
private void button2_Click(object sender, EventArgs e) //add number of time entries on each bill/matter
{
string SQL = "select distinct dbo.jfn_FormatClientCode(clicode) as ClientCode, dbo.jfn_FormatMatterCode(MatCode) as MatterCode, " +
" arbillnbr as BillNo, convert(varchar, arbilldate, 101) as BillDate, count(bb.EntryID) as TotalEntries from billedtime " +
"inner join timeentrylink aa on btid = aa.tbdid " +
"inner join timeentry bb on aa.entryid = bb.entryid " +
"inner join ARBill on arbillnbr = btbillnbr " +
"Inner join matter on matsysnbr = btmatter " +
"inner join Client on clisysnbr = matclinbr " +
"where MatterSysNbr in (SELECT MatSysNbr FROM Matter where MatBillAgreeCode in ('N', 'B')) and entrysource = 'JurisGo' " +
" and (billedtime.btbillnbr not in (select btbillnbr from ##TempGo where ##TempGo.btbillnbr in (select btbillnbr from ##TempGo2) and ##TempGo.BillableFlag = 'N')) " +
" group by clicode, matcode, arbillnbr, arbilldate";
DataSet ff = _jurisUtility.RecordsetFromSQL(SQL);
ReportDisplay rp = new ReportDisplay(ff);
rp.ShowDialog();
}
private void 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()
{
// 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()
{
// 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 (dataGridViewA.Rows.Count == 0 && dataGridViewB.Rows.Count == 0)
{
MessageBox.Show("At least one group must have at least one matter", "Selection Error", MessageBoxButtons.OK, MessageBoxIcon.None);
}
else
{
if ((!String.IsNullOrEmpty(textBoxA.Text) && dataGridViewA.Rows.Count == 0) || (!String.IsNullOrEmpty(textBoxB.Text) && dataGridViewB.Rows.Count == 0))
{
MessageBox.Show("If an increment is specified, at least one matter must be in the group", "Selection Error", MessageBoxButtons.OK, MessageBoxIcon.None);
}
else
{
if ((String.IsNullOrEmpty(textBoxA.Text) && dataGridViewA.Rows.Count != 0) || (String.IsNullOrEmpty(textBoxB.Text) && dataGridViewB.Rows.Count != 0))
{
MessageBox.Show("If a group is populated, an increment must be specified", "Selection Error", MessageBoxButtons.OK, MessageBoxIcon.None);
}
else
{
List <CliMat> GroupAList = null;
List <CliMat> GroupBList = null;
if (dataGridViewA.Rows.Count > 0)
{
GroupAList = getCliMatList(dataGridViewA).ToList();
}
if (dataGridViewB.Rows.Count > 0)
{
GroupBList = getCliMatList(dataGridViewB).ToList();
}
if (GroupAList != null)
{
string matIDs = "";
foreach (CliMat cl in GroupAList)
{
matIDs = matIDs + cl.matsysnbr + ",";
}
matIDs = matIDs.TrimEnd(',');
//MessageBox.Show(matIDs);
string SQL = "SELECT dbo.jfn_FormatClientCode(CliCode) as ClientCode ,dbo.jfn_FormatMatterCode(MatCode) as MatterCode,convert(varchar, EntryDate, 101) as EntryDate " +
" ,empname ,case when FeeScheduleCode is null then '' else FeeScheduleCode end as FeeScheduleCode " +
" ,case when TaskCode is null then '' else TaskCode end as TaskCode " +
" ,case when ActivityCode is null then '' else ActivityCode end as ActivityCode " +
" ,ActualHoursWork,Amount,Narrative " +
" ,case when EntryStatus in (0,1,2,3,4,5) then 'Draft' when EntryStatus = 6 then 'Recorded' else 'Posted' end as EntryStatus " +
" FROM TimeEntry " +
" inner join employee on empsysnbr = TimekeeperSysNbr " +
" inner join matter on MatterSysNbr = matsysnbr " +
" inner join client on ClientSysNbr = clisysnbr " +
" where EntryStatus < 8 and MatterSysNbr in (" + matIDs + ") " + getRule(0);
DataSet myRSTkpr = _jurisUtility.RecordsetFromSQL(SQL);
if (myRSTkpr.Tables[0].Rows.Count > 0)
{
ReportDisplay rpds = new ReportDisplay(myRSTkpr, "Group A Issues");
rpds.ShowDialog();
}
else
{
MessageBox.Show("There were no issues in Group A", "Finished", MessageBoxButtons.OK, MessageBoxIcon.None);
}
}
if (GroupBList != null)
{
string matIDs = "";
foreach (CliMat cl in GroupBList)
{
matIDs = matIDs + cl.matsysnbr + ",";
}
matIDs = matIDs.TrimEnd(',');
string SQL = "SELECT dbo.jfn_FormatClientCode(CliCode) as ClientCode,dbo.jfn_FormatMatterCode(MatCode) as MatterCode,convert(varchar, EntryDate, 101) as EntryDate " +
" ,empname ,case when FeeScheduleCode is null then '' else FeeScheduleCode end as FeeScheduleCode " +
" ,case when TaskCode is null then '' else TaskCode end as TaskCode " +
" ,case when ActivityCode is null then '' else ActivityCode end as ActivityCode " +
" ,ActualHoursWork,Amount,Narrative " +
" ,case when EntryStatus in (0,1,2,3,4,5) then 'Draft' when EntryStatus = 6 then 'Recorded' else 'Posted' end as EntryStatus " +
" FROM TimeEntry " +
" inner join employee on empsysnbr = TimekeeperSysNbr " +
" inner join matter on MatterSysNbr = matsysnbr " +
" inner join client on ClientSysNbr = clisysnbr " +
" where EntryStatus < 8 and MatterSysNbr in (" + matIDs + ") " + getRule(1);
DataSet myRSTkpr = _jurisUtility.RecordsetFromSQL(SQL);
if (myRSTkpr.Tables[0].Rows.Count > 0)
{
ReportDisplay rpds = new ReportDisplay(myRSTkpr, "Group B Issues");
rpds.ShowDialog();
}
else
{
MessageBox.Show("There were no issues in Group B", "Finished", MessageBoxButtons.OK, MessageBoxIcon.None);
}
}
UpdateStatus("Reports displayed properly!", 1, 1);
MessageBox.Show("Reports displayed properly!", "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);
DataTable errors;
if (!string.IsNullOrEmpty(expSchCode))
{
string sb = "delete from ExpSchRate where ESRSch = '" + expSchCode + "'";
_jurisUtility.ExecuteNonQueryCommand(0, sb);
DataTable dvgSource = (DataTable)dataGridView1.DataSource;
errors = dvgSource.Clone();
errors.Columns.Add("Problem");
errors.Clear();
errors.Columns[0].DataType = typeof(string);
errors.Columns[1].DataType = typeof(string);
errors.Columns[2].DataType = typeof(string);
errors.Columns[3].DataType = typeof(string);
errors.Columns[4].DataType = typeof(string);
errors.Columns[5].DataType = typeof(string);
DataTable temp = new DataTable();
temp = errors.Clone();
int numRows = dataGridView1.RowCount;
int j = 1;
foreach (DataRow dg in dvgSource.Rows)
{
temp = checkRowsForInvalidData(dg, temp);
if (temp.Rows.Count == 0)
{
string ESch = dg["ExpenseSchedule"].ToString();
string ECode = dg["ExpenseCode"].ToString();
string Summ = dg["Summarize"].ToString();
string Units = dg["ShowUnits"].ToString();
string Multi = dg["Multiplier"].ToString();
string sqli = "Insert into ExpSchRate (ESRSch ,ESRCode,ESRSumry,ESRShowUnits,ESRMult) values ('" + ESch + "', '" + ECode + "', '" + Summ + "', '" + Units + "', " + Multi + ")";
_jurisUtility.ExecuteNonQueryCommand(0, sqli);
j = j + 1;
Application.DoEvents();
toolStripStatusLabel.Text = "Exp Sch Rate Import Processing...";
statusStrip.Refresh();
UpdateStatus("Exp Sch Rate Import Processing...", j, numRows);
}
else
{
errors.Rows.Add(temp.Rows[0].ItemArray);
temp.Clear();
}
}
//expSchCode = dataGridView1.Rows[0].Cells[0].Value.ToString();
Cursor.Current = Cursors.Default;
Application.DoEvents();
toolStripStatusLabel.Text = "Exp Sch Rate Completed";
statusStrip.Refresh();
UpdateStatus("Exp Sch Rate Completed", 0, 0);
WriteLog("Exp Sch Rate updated for " + expSchCode);
if (errors.Rows.Count > 0)
{
DialogResult drr = MessageBox.Show("There were rows that could not be procesed. Would you like to see the list?", "Error Log", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (drr == System.Windows.Forms.DialogResult.Yes)
{
ReportDisplay rpd = new ReportDisplay(errors);
rpd.ShowDialog();
}
}
else
{
MessageBox.Show("The process finished without issue", "Complete", MessageBoxButtons.OK, MessageBoxIcon.None);
}
expSchCode = "";
}
else
{
MessageBox.Show("Please select a spreadsheet first");
}
}
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);
}
}
private void DoDaFix()
{
if (string.IsNullOrEmpty(fileName))
{
MessageBox.Show("Please select an Excel file before proceeding", "Selection Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(fileName);
Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Excel.Range xlRange = xlWorksheet.UsedRange;
CliObj cli = null;
List <CliObj> clients = new List <CliObj>();
int lastUsedRow = xlWorksheet.Cells.Find("*", System.Reflection.Missing.Value,
System.Reflection.Missing.Value, System.Reflection.Missing.Value,
Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlPrevious,
false, System.Reflection.Missing.Value, System.Reflection.Missing.Value).Row;
int count = 1;
string clientCode = "";
string rateSched = "";
string matterCode = "";
bool goAhead = true;
Error err = new Error();
if (xlRange.Cells[1, 3] != null && xlRange.Cells[1, 3].Value2 != null && xlRange.Cells[1, 2] != null && xlRange.Cells[1, 2].Value2 != null)
{
matterCode = xlRange.Cells[1, 3].Value2.ToString();
clientCode = xlRange.Cells[1, 2].Value2.ToString();
if (err.doesMatExist(clientCode, _jurisUtility, matterCode))
{
if (err.doesCliExist(clientCode, _jurisUtility))
{
String ssql = "select MatFeeSch from matter inner join client on clisysnbr = matclinbr where dbo.jfn_FormatMatterCode(matcode) = '" + matterCode + "' and dbo.jfn_FormatClientCode(clicode) = '" + clientCode + "'";
DataSet dd = _jurisUtility.RecordsetFromSQL(ssql);
rateSched = dd.Tables[0].Rows[0][0].ToString();
}
else
{
goAhead = false;
MessageBox.Show("That Client Code is not valid. Please update the spreadsheet", "Excel Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
goAhead = false; // there was an issue with client or matter code
MessageBox.Show("That Matter Code is not valid. Please update the spreadsheet", "Excel Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else if (xlRange.Cells[1, 2] != null && xlRange.Cells[1, 2].Value2 != null)
{
clientCode = xlRange.Cells[1, 2].Value2.ToString();
if (err.doesCliExist(clientCode, _jurisUtility))
{
String ssql = "select CliFeeSch from client where dbo.jfn_FormatClientCode(clicode) = '" + clientCode + "'";
DataSet dd = _jurisUtility.RecordsetFromSQL(ssql);
rateSched = dd.Tables[0].Rows[0][0].ToString();
}
else
{
goAhead = false;
MessageBox.Show("That Client Code is not valid. Please update the spreadsheet", "Excel Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
MessageBox.Show("There is no Client Code or the spreadsheet is not in the correct format. Please update the spreadsheet", "Excel Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
if (!goAhead)
{
//close and release
xlWorkbook.Close();
//quit and release
xlApp.Quit();
}
else
{
for (int i = 2; i <= lastUsedRow; i++)
{
cli = new CliObj();
cli.error = false;
cli.errorMess = "";
for (int j = 1; j <= 2; j++)
{
if (j == 1)
{
if (xlRange.Cells[i, j] != null && xlRange.Cells[i, j].Value2 != null)
{
cli.PT = xlRange.Cells[i, j].Value2.ToString();
}
}
else if (j == 2)
{
if (xlRange.Cells[i, j] != null && xlRange.Cells[i, j].Value2 != null)
{
if (err.isRateNumeric(xlRange.Cells[i, j].Value2.ToString(), _jurisUtility))
{
cli.rate = Convert.ToDouble(xlRange.Cells[i, j].Value2);
}
else
{
cli.error = true;
cli.errorMess = "The Rate specified: " + xlRange.Cells[i, j].Value2.ToString() + " is not numeric. Please update the spreadsheet";
}
}
}
}
clients.Add(cli);
UpdateStatus("Accessing Spreadsheet", count, lastUsedRow * 2);
count++;
}
//close and release
xlWorkbook.Close();
//quit and release
xlApp.Quit();
UpdateStatus("Updating Database", count, lastUsedRow * 2);
foreach (CliObj cl in clients)
{
if (err.doesPTExist(cl.PT, _jurisUtility))
{
string sql = "select count(*) as CT, PTRFeeSch, PTRPrsTyp from PersTypRate " +
" where PTRFeeSch = '" + rateSched + "' and PTRPrsTyp = '" + cl.PT + "' " +
" group by PTRFeeSch, PTRPrsTyp " +
" having count(*) > 0";
DataSet fd = _jurisUtility.RecordsetFromSQL(sql);
if (fd != null && fd.Tables.Count > 0 && fd.Tables[0].Rows.Count > 0)
{
string innersql = "update PersTypRate set PTRRate = cast(" + cl.rate + " as money) where PTRFeeSch = '" + rateSched + "' and PTRPrsTyp = '" + cl.PT + "' ";
_jurisUtility.ExecuteNonQuery(0, innersql);
}
else
{
string innersql = "insert into PersTypRate (PTRFeeSch, PTRPrsTyp, PTRRate) values ('" + rateSched + "', '" + cl.PT + "', cast(" + cl.rate + " as money))";
_jurisUtility.ExecuteNonQuery(0, innersql);
}
}
else
{
cl.error = true;
cl.errorMess = "Personnel Type " + cl.PT + " is not valid. Please update the PT for that record";
}
count++;
UpdateStatus("Updating Database", count, lastUsedRow * 2);
}
UpdateStatus("Updating Database", lastUsedRow * 2, lastUsedRow * 2);
count++;
UpdateStatus("All items updated.", 1, 1);
List <CliObj> errors = clients.Where(p => p.error == true).ToList();
if (errors.Count == 0)
{
MessageBox.Show("The process is complete", "Confirmation", MessageBoxButtons.OK, MessageBoxIcon.None);
}
else
{
DialogResult dr = MessageBox.Show("The process is complete but there were errors." + "\r\n" + "Would you like to see the error list?", "Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.None);
if (dr == DialogResult.Yes)
{
DataSet df = new DataSet();
df.Tables.Add(err.ToDataTable(errors));
ReportDisplay rd = new ReportDisplay(df);
rd.ShowDialog();
}
}
clients.Clear();
fileName = "";
}
}
fileName = "";
}
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 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 phase = "";
if (rb1.Checked == true)
{
phase = "0";
}
if (rb2.Checked == true)
{
phase = "2";
}
if (rb3.Checked == true)
{
phase = "3";
}
if (rb4.Checked == true)
{
phase = "4";
}
DataTable errors;
string sqlBud = " select mfbmatter from matterfeebudget inner join matter on matsysnbr=mfbmatter inner join client on matclinbr=clisysnbr where dbo.jfn_formatclientcode(clicode) in ('" + client + "') and matsysnbr in (" + matters + ") " +
" union all " +
" select mebmatter from matterexpbudget inner join matter on matsysnbr=mebmatter inner join client on matclinbr=clisysnbr where dbo.jfn_formatclientcode(clicode) in ('" + client + "') and matsysnbr in (" + matters + ") ";
DataSet myRSBud = _jurisUtility.RecordsetFromSQL(sqlBud);
if (myRSBud.Tables[0].Rows.Count == 0) // new budget
{
DialogResult dialRes = MessageBox.Show("The fee and expense budget for " + client + "/" + matterCodes + " will be updated. Do you wish to continue?", "Budget Update", MessageBoxButtons.YesNo);
if (dialRes == DialogResult.Yes)
{
string sb = "Create Table #MFBTemp(mat int, ttype varchar(10), taskexp varchar(5), hours decimal(12,2), amt money, phase int)";
_jurisUtility.ExecuteNonQueryCommand(0, sb);
DataTable dvgSource = (DataTable)dataGridView1.DataSource;
errors = dvgSource.Clone();
errors.Columns.Add("Problem");
errors.Clear();
errors.Columns[0].DataType = typeof(string);
errors.Columns[1].DataType = typeof(string);
errors.Columns[2].DataType = typeof(string);
errors.Columns[3].DataType = typeof(string);
errors.Columns[4].DataType = typeof(string);
errors.Columns[5].DataType = typeof(string);
errors.Columns[6].DataType = typeof(string);
errors.Columns[7].DataType = typeof(string);
errors.Columns[8].DataType = typeof(string);
errors.Columns[9].DataType = typeof(string);
DataTable temp = new DataTable();
temp = errors.Clone();
int numRows = dataGridView1.RowCount;
int j = 1;
foreach (DataRow dg in dvgSource.Rows)
{
temp = checkRowsForInvalidData(dg, temp);
if (temp.Rows.Count == 0)
{
string TType = dg["TaskExp"].ToString();
string TaskExp = dg["Task_Code"].ToString();
string Hours = dg["Hours"].ToString();
string Amount = dg["Amount"].ToString();
string sqli = "Insert into #MFBTemp Select matsysnbr,'" + TType + "','" + TaskExp + "', cast('" + Hours + "' as decimal(12,2)),cast('" + Amount + "' as money)," + phase + " from client inner join matter on matclinbr=clisysnbr where dbo.jfn_formatclientcode(clicode) in ('" + client + "') and matsysnbr in (" + matters + ")";
_jurisUtility.ExecuteNonQueryCommand(0, sqli);
j = j + 1;
Application.DoEvents();
toolStripStatusLabel.Text = "Budget Import Processing...";
statusStrip.Refresh();
UpdateStatus("Budget Import Processing...", j, numRows);
}
else
{
errors.Rows.Add(temp.Rows[0].ItemArray);
temp.Clear();
}
}
string s6 = "update client set clibudgetoption=1 from #MFBTemp inner join matter on matsysnbr=mat where matclinbr=clisysnbr";
_jurisUtility.ExecuteNonQueryCommand(0, s6);
string s5 = "update matter set matbudgetoption=1, matbudgetphase=" + phase + " from #MFBTemp where matsysnbr=mat";
_jurisUtility.ExecuteNonQueryCommand(0, s5);
string s2 = @"Insert into matterfeebudget(mfbmatter, mfbline, mfbphase, mfbtaskcode, mfbbudhrswrk, mfbbudfeewrk, mfbactualhrswrk, mfbactualfeewrk, mfbactualhrsbld, mfbactualfeebld )
select mat, rank() over (partition by mat order by taskexp) as RowNbr, phase, taskexp, hours, amt,0,0,0,0 from #MFBTemp where ttype='Task' and (hours <> 0 and amt <> 0)";
_jurisUtility.ExecuteNonQueryCommand(0, s2);
string s3 = @"Insert into matterexpbudget(mebmatter, mebline, mebphase, mebexpcode, mebbudexp, mebexpincur, mebexpbld)
select mat, rank() over (partition by mat order by taskexp) as RowNbr, phase, taskexp, amt,0,0 from #MFBTemp where ttype='Expense' and taskexp in (select expcdcode from expensecode) and (hours <> 0 and amt <> 0)";
_jurisUtility.ExecuteNonQueryCommand(0, s3);
string s4 = "drop table #MFBTemp";
_jurisUtility.ExecuteNonQueryCommand(0, s4);
Cursor.Current = Cursors.Default;
Application.DoEvents();
toolStripStatusLabel.Text = "Budget Import Completed";
statusStrip.Refresh();
UpdateStatus("Budget Import Completed", 0, 0);
WriteLog("Core Matter Budget updated for " + client + "/" + matterCodes);
if (errors.Rows.Count > 0)
{
DialogResult drr = MessageBox.Show("There were rows that could not be procesed. Would you like to see the list?", "Error Log", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (drr == System.Windows.Forms.DialogResult.Yes)
{
ReportDisplay rpd = new ReportDisplay(errors);
rpd.ShowDialog();
}
}
}
else
{
Cursor.Current = Cursors.Default;
Application.DoEvents();
toolStripStatusLabel.Text = "Budget Import Cancelled";
statusStrip.Refresh();
UpdateStatus("Budget Import ", 1, 1);
}
}
else
{
DialogResult dialRes = MessageBox.Show("A budget already exists for " + client + "/" + matterCodes + ". Continuing will overwrite the existing budget. Do you wish to continue?", "Budget Update", MessageBoxButtons.YesNo);
if (dialRes == DialogResult.Yes)
{
string d2 = "delete from matterfeebudget where mfbmatter in (" + matters + ") ";
_jurisUtility.ExecuteNonQueryCommand(0, d2);
string d3 = "delete from matterexpbudget where mebmatter in (" + matters + ") ";
_jurisUtility.ExecuteNonQueryCommand(0, d3);
string sb = "Create Table #MFBTemp(mat int, ttype varchar(10), taskexp varchar(5), hours decimal(12,2), amt money, phase int)";
_jurisUtility.ExecuteNonQueryCommand(0, sb);
DataTable dvgSource = (DataTable)dataGridView1.DataSource;
errors = dvgSource.Clone();
errors.Columns.Add("Problem");
errors.Clear();
errors.Columns[0].DataType = typeof(string);
errors.Columns[1].DataType = typeof(string);
errors.Columns[2].DataType = typeof(string);
errors.Columns[3].DataType = typeof(string);
errors.Columns[4].DataType = typeof(string);
errors.Columns[5].DataType = typeof(string);
errors.Columns[6].DataType = typeof(string);
errors.Columns[7].DataType = typeof(string);
errors.Columns[8].DataType = typeof(string);
errors.Columns[9].DataType = typeof(string);
DataTable temp = new DataTable();
temp = errors.Clone();
int numRows = dataGridView1.RowCount;
int j = 1;
foreach (DataRow dg in dvgSource.Rows)
{
temp = checkRowsForInvalidData(dg, temp);
if (temp.Rows.Count == 0)
{
string TType = dg["TaskExp"].ToString();
string TaskExp = dg["Task_Code"].ToString();
string Hours = dg["Hours"].ToString();
string Amount = dg["Amount"].ToString();
string sqli = "Insert into #MFBTemp Select matsysnbr,'" + TType + "','" + TaskExp + "', cast('" + Hours + "' as decimal(12,2)),cast('" + Amount + "' as money)," + phase + " from client inner join matter on matclinbr=clisysnbr where dbo.jfn_formatclientcode(clicode) in ('" + client + "') and matsysnbr in (" + matters + ")";
_jurisUtility.ExecuteNonQueryCommand(0, sqli);
j = j + 1;
Application.DoEvents();
toolStripStatusLabel.Text = "Budget Import Processing...";
statusStrip.Refresh();
UpdateStatus("Budget Import Processing...", j, numRows);
}
else
{
errors.Rows.Add(temp.Rows[0].ItemArray);
temp.Clear();
}
}
string s6 = "update client set clibudgetoption=1 from #MFBTemp inner join matter on matsysnbr=mat where matclinbr=clisysnbr";
_jurisUtility.ExecuteNonQueryCommand(0, s6);
string s5 = "update matter set matbudgetoption=1, matbudgetphase=" + phase + " from #MFBTemp where matsysnbr=mat";
_jurisUtility.ExecuteNonQueryCommand(0, s5);
string s2 = @"Insert into matterfeebudget(mfbmatter, mfbline, mfbphase, mfbtaskcode, mfbbudhrswrk, mfbbudfeewrk, mfbactualhrswrk, mfbactualfeewrk, mfbactualhrsbld, mfbactualfeebld )
select mat, rank() over (partition by mat order by taskexp) as RowNbr, phase, taskexp, hours, amt,0,0,0,0 from #MFBTemp where ttype='Task' and (hours<>0 and amt<>0)";
_jurisUtility.ExecuteNonQueryCommand(0, s2);
string s3 = @"Insert into matterexpbudget(mebmatter, mebline, mebphase, mebexpcode, mebbudexp, mebexpincur, mebexpbld)
select mat, rank() over (partition by mat order by taskexp) as RowNbr, phase, taskexp, amt,0,0 from #MFBTemp where taskexp in (select expcdcode from expensecode) and ttype='Expense' and (hours<>0 and amt<>0)";
_jurisUtility.ExecuteNonQueryCommand(0, s3);
string s4 = "drop table #MFBTemp";
_jurisUtility.ExecuteNonQueryCommand(0, s4);
Cursor.Current = Cursors.Default;
Application.DoEvents();
toolStripStatusLabel.Text = "Budget Import Completed";
statusStrip.Refresh();
UpdateStatus("Budget Import Completed", 1, 1);
WriteLog("Core Matter Budget updated for " + client + "/" + matterCodes);
if (errors.Rows.Count > 0)
{
DialogResult drr = MessageBox.Show("There were rows that could not be procesed. Would you like to see the list?", "Error Log", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (drr == System.Windows.Forms.DialogResult.Yes)
{
ReportDisplay rpd = new ReportDisplay(errors);
rpd.ShowDialog();
}
}
}
else
{
Cursor.Current = Cursors.Default;
Application.DoEvents();
toolStripStatusLabel.Text = "Budget Import Cancelled";
statusStrip.Refresh();
UpdateStatus("Budget Import Cancelled", 0, 0);
}
}
}
private void backgroundWorkerAll_DoWork(object sender, DoWorkEventArgs e)
{
current = 0;
bproc = e.Argument as BothProcessor;
BackgroundWorker worker = sender as BackgroundWorker;
try
{
for (int a = 6; a < 10; a++)
{
bproc.processExpenseEntries(a.ToString());
backgroundWorkerAll.ReportProgress(current);
var tlist = bproc.allExp.ToList();
foreach (ExpenseEntry tt in tlist)
{
current++;
bproc.compareExpenseEntries(tt);
backgroundWorkerAll.ReportProgress(current);
}
}
}
catch (Exception ex1)
{
e.Result = ex1;
}
try
{
for (int a = 6; a < 10; a++)
{
bproc.processTimeEntries(a.ToString());
backgroundWorkerAll.ReportProgress(current);
var tlist = bproc.allTimes.ToList();
foreach (TimeEntry tt in tlist)
{
current++;
bproc.compareTimeEntries(tt);
backgroundWorkerAll.ReportProgress(current);
}
}
}
catch (Exception ex1)
{
e.Result = ex1;
}
DataTable dt = ConvertTo(bproc.correctedTimes);
DataTable dx = ConvertTo(bproc.correctedExpenses);
Errors = new DataSet();
Errors1 = new DataSet();
Errors.Tables.Add(dt);
Errors1.Tables.Add(dx);
if (bproc.correctedExpenses.Count == 0 && bproc.correctedTimes.Count == 0)
{
MessageBox.Show("There were no issues found in your expense or time entries," + "\r\n" + "so no changes are needed. The tool will now close");
Environment.ExitCode = 1;
Application.Exit();
}
else
{
DialogResult tpr = seePreReport();
if (tpr == DialogResult.Yes)
{
ReportDisplay rpds = new ReportDisplay(Errors, Errors1, 2);
rpds.ShowDialog();
for (int d = 0; d < 10; d++)
{
var typeList = bproc.correctedExpenses.Where(c => c.newEntryStatus == d).ToList();
bproc.updateExpenseEntries(typeList, d);
backgroundWorkerAll.ReportProgress(current);
current++;
var typeList1 = bproc.correctedTimes.Where(c => c.newEntryStatus == d).ToList();
bproc.updateTimeEntries(typeList1, d);
backgroundWorkerAll.ReportProgress(current);
current++;
}
}
else if (tpr == DialogResult.No)
{
for (int d = 0; d < 10; d++)
{
var typeList = bproc.correctedExpenses.Where(c => c.newEntryStatus == d).ToList();
bproc.updateExpenseEntries(typeList, d);
backgroundWorkerAll.ReportProgress(current);
current++;
var typeList1 = bproc.correctedTimes.Where(c => c.newEntryStatus == d).ToList();
bproc.updateTimeEntries(typeList1, d);
backgroundWorkerAll.ReportProgress(current);
current++;
}
}
else if (tpr == DialogResult.Cancel)
{
Environment.ExitCode = 1;
Application.Exit();
}
}
}
