private void DoDaFix()
{
string sql = "";
sql = "update ledgerhistory set lhcashamt = 1862.49, lhcshexp = 0.00 where lhsysnbr = 67394";
_jurisUtility.ExecuteNonQuery(0, sql);
sql = "update CRARAlloc set CRACshExpAmt = 0.00, CRAPrePostCshExp = 0.00 where crabatch = CRABatch and CRARecNbr = 14 and CRABillNbr = 43422";
_jurisUtility.ExecuteNonQuery(0, sql);
sql = "update CRExpAlloc set CREPrePost = 0.00, CREAmount = 0.00 where CREBatch = 23361 and crerecnbr = 14 and CREBillNbr = 43422 and CREExpType = 'C'";
_jurisUtility.ExecuteNonQuery(0, sql);
sql = "update ExpSumByPrd set ESPReceived = 0.00 where ESPPrdYear = 2018 and ESPPrdNbr = 4 and espexpcd = 'E119' and espmatter = 134";
_jurisUtility.ExecuteNonQuery(0, sql);
sql = "update ExpSumITD set ESiReceived = (ESiReceived - 0.09) where Esiexpcd = 'E119' and esimatter = 134";
_jurisUtility.ExecuteNonQuery(0, sql);
UpdateStatus("Ledger History and associated tables updated.", 1, 1);
MessageBox.Show("The process is complete.", "Confirmation", MessageBoxButtons.OK, MessageBoxIcon.None);
}
private void DoDaFix()
{
string sql = "update ledgerhistory set lhcshexp = 0 where lhsysnbr in (1124759, 1021030, 1021031, 1128417, 1128418)";
_jurisUtility.ExecuteNonQuery(0, sql);
sql = "update ledgerhistory set lhcshexp=lhcashamt where lhsysnbr in (1124343,1124346)";
_jurisUtility.ExecuteNonQuery(0, sql);
string S2 = @"update armatalloc
set armfeercvd = armfeebld + armfeeadj, armcshexprcvd = armcshexpbld + armcshexpadj
, armncshexprcvd = armncshexpbld + armncshexpadj
, armsurchgrcvd = armsurchgbld + armsurchgadj
, armintrcvd = armintbld + armintadj
, armtax1rcvd = armtax1bld + armtax1adj
, armtax3rcvd = armtax3bld + armtax3adj
, armtax2rcvd = armtax2bld + armtax2adj
where armbaldue = 0 and(armfeebld - armfeercvd + armfeeadj <> 0 OR armcshexpbld - armcshexprcvd + armcshexpadj <> 0 OR
armncshexpbld - armncshexprcvd + armncshexpadj <> 0 OR armsurchgbld - armsurchgrcvd + armsurchgadj <> 0 OR
armintbld - armintrcvd + armintadj <> 0 OR armtax1bld - armtax1rcvd + armtax1adj <> 0 OR
armtax3bld - armtax3rcvd + armtax3adj <> 0 OR armtax2bld - armtax2rcvd + armtax2adj <> 0)";
_jurisUtility.ExecuteNonQuery(0, S2);
UpdateStatus("All Ledger Entries updated", 1, 1);
MessageBox.Show("The process is complete", "Confirmation", MessageBoxButtons.OK, MessageBoxIcon.None);
}
private void DoDaFix()
{
string sql = "update Vendor set VenDefaultAPAcct = 'AP'";
_jurisUtility.ExecuteNonQuery(0, sql);
sql = "update Vendor set VenDefaultDistAcct = (SELECT ChtSysNbr FROM ChartOfAccounts where dbo.jfn_FormatChartOfAccount(ChtSysNbr) = '9000-000')";
_jurisUtility.ExecuteNonQuery(0, sql);
UpdateStatus("All vendors updated.", 1, 1);
MessageBox.Show("The process is complete", "Confirmation", MessageBoxButtons.OK, MessageBoxIcon.None);
}
private void button2_Click(object sender, EventArgs e)
{
string s2 = "update prebill set pbstatus=2, pbaction=0 where pbsysnbr=" + tbPrebill.Text.ToString();
_jurisUtility.ExecuteNonQuery(0, s2);
MessageBox.Show(tbPrebill.ToString() + " has been reset to ready to edit status.", "Prebill Status", MessageBoxButtons.OK);
string sql = "SELECT distinct empname, BillToBillingAtty FROM PreBill " +
"inner join billto on billtosysnbr = pbbillto " +
"inner join employee on empsysnbr = BillToBillingAtty " +
" inner join prebillmatter on pbmprebill = pbsysnbr " +
" inner join matter on matsysnbr = pbmmatter " +
" where pbstatus <= 2 and matbillagreecode = 'R' and matfltfeeorretainer<>0 and matstatusflag='O' ";
DataSet emp = _jurisUtility.RecordsetFromSQL(sql);
if (emp == null || emp.Tables[0].Rows.Count == 0)
{
MessageBox.Show("There are no prebills to process", "No processing", MessageBoxButtons.OK, MessageBoxIcon.Hand);
}
else
{
comboBox1.ValueMember = "BillToBillingAtty";
comboBox1.DisplayMember = "empname";
comboBox1.DataSource = emp.Tables[0];
}
}
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.ExecuteNonQuery(0, SQL);
}
}
private void DoDaFix()
{
string sql = "";
sql = " update CheckRegister " +
" set CkRegCleared = 'N', CkRegReconDate = '01/01/1900' " +
" where CkRegSysNbr in (44457, 44521, 44522, 44533, 44534, 44538, 44581, 44597, 44799, 44800, 44801, 44802, 44803) ";
_jurisUtility.ExecuteNonQuery(0, sql);
sql = " delete from BankReconHistory " +
" where BRHBank = '08' and BRHStmtDate > '2020-11-30' ";
_jurisUtility.ExecuteNonQuery(0, sql);
UpdateStatus("Recon and Check Register updated.", 1, 1);
MessageBox.Show("The process is complete.", "Confirmation", MessageBoxButtons.OK, MessageBoxIcon.None);
}
private void processSingleClient(CliMat cc)
{
string sql = "";
cc.clisys = getCliSysNbr(cc.clicode);
if (cc.clisys == 0)
{
ErrorLog er = new ErrorLog();
er.client = cc.clicode;
er.message = "Client " + cc.clicode + " does not appear to be a valid client. Check that the entered code matches what is displayed in Core exactly." + "\r\n" + "\r\n"; //still close client even with no matters
errorList.Add(er);
}
else
{
//make changes to matters first
sql = " select matsysnbr from matter where matclinbr = " + cc.clisys.ToString() + " and MatStatusFlag <> 'C'";
DataSet ds = _jurisUtility.RecordsetFromSQL(sql);
if (ds == null || ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
{
ErrorLog er = new ErrorLog();
er.client = cc.clicode;
er.message = "Client " + cc.clicode + " does not have any open matters so no matters were changed. Client was still closed." + "\r\n" + "\r\n"; //still close client even with no matters
errorList.Add(er);
}
else
{
foreach (DataRow dr in ds.Tables[0].Rows) // Lieke wants clients closed regardless of matter status or errors
{
processSingleMatter(Convert.ToInt32(dr[0].ToString()), "");
}
}
//close client regardless of matter status
sql = "";
sql = "update client set CliReportingName = left('" + cc.name + "', 30), CliNickName = left('" + cc.name + "', 30), branch = '" + cc.branch + "' where clisysnbr = " + cc.clisys.ToString();
_jurisUtility.ExecuteNonQuery(0, sql);
sql = "update client set AccountRep = '' where clisysnbr = " + cc.clisys.ToString();
_jurisUtility.ExecuteNonQuery(0, sql);
sql = " select CNNoteText from ClientNote where cnclient = " + cc.clisys + " and CNNoteIndex = 'Remarks'";
DataSet ds1 = _jurisUtility.RecordsetFromSQL(sql);
if (ds1 == null || ds1.Tables.Count == 0 || ds1.Tables[0].Rows.Count == 0)
{ //if no remarks notecard exists, create a new one
sql = "insert into ClientNote ([CNClient] ,[CNNoteIndex],[CNObject],[CNNoteText] ,[CNNoteObject]) " +
" values (" + cc.clisys.ToString() + ", 'Remarks', ' ', cast('" + cc.remarks + "' as nvarchar(max)), null)";
_jurisUtility.ExecuteNonQuery(0, sql);
}
else // if it does exist, put the new text at the top
{
sql = "update ClientNote set CNNoteText = cast('" + cc.remarks + "' + char(13) + char(10) + char(13) + char(10) + cast(CNNoteText as varchar(1000)) as nvarchar(max)) where CNClient = " + cc.clisys.ToString() + " and CNNoteIndex = 'Remarks'";
_jurisUtility.ExecuteNonQuery(0, sql);
}
}
}
private void DoDaFix()
{
string sql = "";
if (!String.IsNullOrEmpty(textBoxCode1.Text) || !String.IsNullOrEmpty(textBoxCode2.Text))
{
if (!String.IsNullOrEmpty(textBoxID.Text))
{
if (IsNumeric(textBoxID.Text))
{ //unbilled time
if (String.IsNullOrEmpty(textBoxCode1.Text))
{
sql = "update billedtime set BTCode2 = '" + textBoxCode2.Text + "' where btid = " + textBoxID.Text;
_jurisUtility.ExecuteNonQuery(0, sql);
sql = "update unbilledtime set uTCode2 = '" + textBoxCode2.Text + "' where utid = " + textBoxID.Text;
_jurisUtility.ExecuteNonQuery(0, sql);
}
else if (String.IsNullOrEmpty(textBoxCode2.Text))
{
sql = "update billedtime set BTCode1 = '" + textBoxCode1.Text + "' where btid = " + textBoxID.Text;
_jurisUtility.ExecuteNonQuery(0, sql);
sql = "update unbilledtime set uTCode1 = '" + textBoxCode1.Text + "' where utid = " + textBoxID.Text;
_jurisUtility.ExecuteNonQuery(0, sql);
}
else if (!String.IsNullOrEmpty(textBoxCode2.Text) && !String.IsNullOrEmpty(textBoxCode1.Text))
{
sql = "update billedtime set BTCode1 = '" + textBoxCode1.Text + "', btcode2 = '" + textBoxCode2.Text + "' where btid = " + textBoxID.Text;
_jurisUtility.ExecuteNonQuery(0, sql);
sql = "update unbilledtime set uTCode1 = '" + textBoxCode1.Text + "', utcode2 = '" + textBoxCode2.Text + "' where utid = " + textBoxID.Text;
_jurisUtility.ExecuteNonQuery(0, sql);
}
UpdateStatus("Entry Updated.", 1, 1);
}
}
else
{
MessageBox.Show("EntryID is Required", "Entry Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
MessageBox.Show("At least one code is required", "Entry 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);
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);
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()
{
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 = "";
}
