private CompositKeyRecord getFeeSumByPrdDataSet(DataRow dr) { CompositKeyRecord rec = new CompositKeyRecord(); rec.Year = dr["FSPPrdYear"].ToString().Trim(); rec.Matter = dr["FSPMatter"].ToString().Trim(); rec.Tkpr = dr["FSPTkpr"].ToString().Trim(); rec.TaskCode = dr["FSPTaskCd"].ToString().Trim(); if (!string.IsNullOrEmpty(dr["FSPActivityCd"].ToString().Trim())) { rec.ActCode = " = '" + dr["FSPActivityCd"].ToString().Trim() + "' "; } else { rec.ActCode = " is null "; } rec.Period = dr["FSPPrdNbr"].ToString().Trim(); rec.WorkHrsBld = Double.Parse(dr["FSPWorkedHrsBld"].ToString().Trim()); rec.HrsBld = Double.Parse(dr["FSPHrsBilled"].ToString().Trim()); rec.StdValueBld = Double.Parse(dr["FSPFeeBldStdValue"].ToString().Trim()); rec.ActualValueBld = Double.Parse(dr["FSPFeeBldActualValue"].ToString().Trim()); rec.ActualAmtBld = Double.Parse(dr["FSPFeeBldActualAmt"].ToString().Trim()); rec.Rcvd = Double.Parse(dr["FSPFeeReceived"].ToString().Trim()); rec.Adj = Double.Parse(dr["FSPFeeAdjusted"].ToString().Trim()); rec.WorkedHrsEntered = Double.Parse(dr["FSPWorkedHrsEntered"].ToString().Trim()); rec.NonBilHrsEntered = Double.Parse(dr["FSPNonBilHrsEntered"].ToString().Trim()); rec.BilHrsEntered = Double.Parse(dr["FSPBilHrsEntered"].ToString().Trim()); rec.FeeEnteredStdValue = Double.Parse(dr["FSPFeeEnteredStdValue"].ToString().Trim()); rec.FeeEnteredActualValue = Double.Parse(dr["FSPFeeEnteredActualValue"].ToString().Trim()); return(rec); }
private CompositKeyRecord getARFTaskAllocDataSet(DataRow dr) { CompositKeyRecord rec = new CompositKeyRecord(); rec.BillNo = dr["ARFTBillNbr"].ToString().Trim(); rec.Matter = dr["ARFTMatter"].ToString().Trim(); rec.Tkpr = dr["ARFTTkpr"].ToString().Trim(); rec.TaskCode = dr["ARFTTaskCd"].ToString().Trim(); if (!string.IsNullOrEmpty(dr["ARFTActivityCd"].ToString().Trim())) { rec.ActCode = " = '" + dr["ARFTActivityCd"].ToString().Trim() + "' "; } else { rec.ActCode = " is null "; } rec.WorkHrsBld = Double.Parse(dr["ARFTWorkedHrsBld"].ToString().Trim()); rec.HrsBld = Double.Parse(dr["ARFTHrsBld"].ToString().Trim()); rec.StdValueBld = Double.Parse(dr["ARFTStdValueBld"].ToString().Trim()); rec.ActualValueBld = Double.Parse(dr["ARFTActualValueBld"].ToString().Trim()); rec.ActualAmtBld = Double.Parse(dr["ARFTActualAmtBld"].ToString().Trim()); rec.Rcvd = Double.Parse(dr["ARFTRcvd"].ToString().Trim()); rec.Adj = Double.Parse(dr["ARFTAdj"].ToString().Trim()); rec.Pend = Double.Parse(dr["ARFTPend"].ToString().Trim()); return(rec); }
private CompositKeyRecord getCRFeeAllocDataSet(DataRow dr) { CompositKeyRecord rec = new CompositKeyRecord(); rec.BillNo = dr["CRFBillNbr"].ToString().Trim(); rec.Matter = dr["CRFMatter"].ToString().Trim(); rec.Tkpr = dr["CRFTkpr"].ToString().Trim(); rec.TaskCode = dr["CRFTaskCd"].ToString().Trim(); if (!string.IsNullOrEmpty(dr["CRFActivityCd"].ToString().Trim())) { rec.ActCode = " = '" + dr["CRFActivityCd"].ToString().Trim() + "' "; } else { rec.ActCode = " is null "; } rec.Batch = dr["CRFBatch"].ToString().Trim(); rec.Record = dr["CRFRecNbr"].ToString().Trim(); rec.PrePost = Double.Parse(dr["CRFPrePost"].ToString().Trim()); rec.Amount = Double.Parse(dr["CRFAmount"].ToString().Trim()); return(rec); }
private void DoDaFix() { // Enter your SQL code here // To run a T-SQL statement with no results, int RecordsAffected = _jurisUtility.ExecuteNonQueryCommand(0, SQL); // To get an ADODB.Recordset, ADODB.Recordset myRS = _jurisUtility.RecordsetFromSQL(SQL); DialogResult result = MessageBox.Show("This will change all Task code references from " + fromTaskCode + "\r\n" + "to " + toTaskCode + ". Are you sure?", "Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Question); if (result == System.Windows.Forms.DialogResult.Yes) { UpdateStatus("Updating AR Fee Task and Billed Time...", 1, 8); string SQL = "Select * from [ARFTaskAlloc] where ARFTTaskCd = '" + fromTaskCode + "'"; //one record at a time. If exists with same data but new act code, update and conbine. If not, update DataSet fromArf = _jurisUtility.RecordsetFromSQL(SQL); // no matches so who cares if (fromArf.Tables[0].Rows.Count > 0) { foreach (DataRow dr in fromArf.Tables[0].Rows) { CompositKeyRecord record = new CompositKeyRecord(); record = getARFTaskAllocDataSet(dr); SQL = "Select * from [ARFTaskAlloc] where ARFTTaskCd = '" + toTaskCode + "' and [ARFTBillNbr] = " + record.BillNo + " and [ARFTMatter] =" + record.Matter + " and [ARFTTkpr] = " + record.Tkpr + " and [ARFTActivityCd] " + record.ActCode; DataSet matches = _jurisUtility.RecordsetFromSQL(SQL); if (matches.Tables[0].Rows.Count > 0) //we need to combine because key already exists { double WorkHrsBld = Double.Parse(matches.Tables[0].Rows[0]["ARFTWorkedHrsBld"].ToString().Trim()); double HrsBld = Double.Parse(matches.Tables[0].Rows[0]["ARFTHrsBld"].ToString().Trim()); double StdValueBld = Double.Parse(matches.Tables[0].Rows[0]["ARFTStdValueBld"].ToString().Trim()); double ActualValueBld = Double.Parse(matches.Tables[0].Rows[0]["ARFTActualValueBld"].ToString().Trim()); double ActualAmtBld = Double.Parse(matches.Tables[0].Rows[0]["ARFTActualAmtBld"].ToString().Trim()); double Rcvd = Double.Parse(matches.Tables[0].Rows[0]["ARFTRcvd"].ToString().Trim()); double Adj = Double.Parse(matches.Tables[0].Rows[0]["ARFTAdj"].ToString().Trim()); double Pend = Double.Parse(matches.Tables[0].Rows[0]["ARFTPend"].ToString().Trim()); //update record with correct task code SQL = "update ARFTaskAlloc set [ARFTWorkedHrsBld] = Cast(" + WorkHrsBld + " + " + record.WorkHrsBld + " as decimal(12,2)),[ARFTHrsBld] = Cast(" + HrsBld + " + " + record.HrsBld + " as decimal(12,2)),[ARFTStdValueBld] = Cast(" + StdValueBld + " + " + record.StdValueBld + " as decimal(12,2)),[ARFTActualValueBld] = Cast(" + ActualValueBld + " + " + record.ActualValueBld + " as decimal(12,2)),[ARFTActualAmtBld] = Cast(" + ActualAmtBld + " + " + record.ActualAmtBld + " as decimal(12,2)),[ARFTRcvd] = Cast(" + Rcvd + " + " + record.Rcvd + " as decimal(12,2)),[ARFTAdj] = Cast(" + Adj + " + " + record.Adj + " as decimal(12,2)),[ARFTPend] = Cast(" + Pend + " + " + record.Pend + " as decimal(12,2)) where ARFTTaskCd = '" + toTaskCode + "' and [ARFTBillNbr] = " + record.BillNo + " and [ARFTMatter] =" + record.Matter + " and [ARFTTkpr] = " + record.Tkpr + " and [ARFTActivityCd] " + record.ActCode; _jurisUtility.ExecuteNonQueryCommand(0, SQL); //delete record with old task code SQL = "delete from ARFTaskAlloc where ARFTTaskCd = '" + fromTaskCode + "' and [ARFTBillNbr] = " + record.BillNo + " and [ARFTMatter] =" + record.Matter + " and [ARFTTkpr] = " + record.Tkpr + " and [ARFTActivityCd] " + record.ActCode; _jurisUtility.ExecuteNonQueryCommand(0, SQL); } else //no match so we can just update { SQL = "update ARFTaskAlloc set ARFTTaskCd ='" + toTaskCode + "' where ARFTTaskCd = '" + fromTaskCode + "' and [ARFTBillNbr] = " + record.BillNo + " and [ARFTMatter] =" + record.Matter + " and [ARFTTkpr] = " + record.Tkpr + " and [ARFTActivityCd] " + record.ActCode; _jurisUtility.ExecuteNonQueryCommand(0, SQL); } } } fromArf.Clear(); SQL = "update BilledTime set BTTaskCd ='" + toTaskCode + "' where BTTaskCd = '" + fromTaskCode + "'"; _jurisUtility.ExecuteNonQueryCommand(0, SQL); SQL = "update ExpenseEntry set TaskCode ='" + toTaskCode + "' where TaskCode = '" + fromTaskCode + "'"; _jurisUtility.ExecuteNonQueryCommand(0, SQL); UpdateStatus("Updating Cash Rec Fee Alloc...", 2, 8); //fave and most recent SQL = "delete from TaskCodeMostRecent where Code ='" + fromTaskCode + "'"; _jurisUtility.ExecuteNonQueryCommand(0, SQL); SQL = "delete from TaskCodeFavorite where Code ='" + fromTaskCode + "'"; _jurisUtility.ExecuteNonQueryCommand(0, SQL); SQL = "Select * from [CRFeeAlloc] where CRFTaskCd = '" + fromTaskCode + "'"; //one record at a time. If exists with same data but new act code, update and conbine. If not, update fromArf = _jurisUtility.RecordsetFromSQL(SQL); // no matches so who cares if (fromArf.Tables[0].Rows.Count > 0) { foreach (DataRow dr in fromArf.Tables[0].Rows) { CompositKeyRecord record = new CompositKeyRecord(); record = getCRFeeAllocDataSet(dr); SQL = "Select * from [CRFeeAlloc] where CRFTaskCd = '" + toTaskCode + "' and [CRFBillNbr] = " + record.BillNo + " and [CRFMatter] =" + record.Matter + " and [CRFTkpr] = " + record.Tkpr + " and [CRFActivityCd] " + record.ActCode + " and CRFBatch = " + record.Batch + " and CRFRecNbr = " + record.Record; DataSet matches = _jurisUtility.RecordsetFromSQL(SQL); if (matches.Tables[0].Rows.Count > 0) //we need to combine because key already exists { double PrePost = Double.Parse(matches.Tables[0].Rows[0]["CRFPrePost"].ToString().Trim()); double Amount = Double.Parse(matches.Tables[0].Rows[0]["CRFAmount"].ToString().Trim()); //update record with correct task code SQL = "update CRFeeAlloc set [CRFPrePost] = Cast(" + PrePost + " + " + record.PrePost + " as decimal(12,2)),[CRFAmount] = Cast(" + Amount + " + " + record.Amount + " as decimal(12,2)) where CRFTaskCd = '" + fromTaskCode + "' and [CRFBillNbr] = " + record.BillNo + " and [CRFMatter] =" + record.Matter + " and [CRFTkpr] = " + record.Tkpr + " and [CRFActivityCd] " + record.ActCode + " and CRFBatch = " + record.Batch + " and CRFRecNbr = " + record.Record; _jurisUtility.ExecuteNonQueryCommand(0, SQL); //delete record with old task code SQL = "delete from CRFeeAlloc where CRFTaskCd = '" + fromTaskCode + "' and [CRFBillNbr] = " + record.BillNo + " and [CRFMatter] =" + record.Matter + " and [CRFTkpr] = " + record.Tkpr + " and [CRFActivityCd] " + record.ActCode + " and CRFBatch = " + record.Batch + " and CRFRecNbr = " + record.Record; _jurisUtility.ExecuteNonQueryCommand(0, SQL); } else //no match so we can just update { SQL = "update CRFeeAlloc set CRFTaskCd ='" + toTaskCode + "' where CRFTaskCd = '" + fromTaskCode + "' and [CRFBillNbr] = " + record.BillNo + " and [CRFMatter] =" + record.Matter + " and [CRFTkpr] = " + record.Tkpr + " and [CRFActivityCd] " + record.ActCode + " and CRFBatch = " + record.Batch + " and CRFRecNbr = " + record.Record; _jurisUtility.ExecuteNonQueryCommand(0, SQL); } } } fromArf.Clear(); UpdateStatus("Updating Fee Sum By Period...", 3, 8); SQL = "Select * from [FeeSumByPrd] where FSPTaskCd = '" + fromTaskCode + "'"; //one record at a time. If exists with same data but new act code, update and conbine. If not, update fromArf = _jurisUtility.RecordsetFromSQL(SQL); // no matches so who cares if (fromArf.Tables[0].Rows.Count > 0) { foreach (DataRow dr in fromArf.Tables[0].Rows) { CompositKeyRecord record = new CompositKeyRecord(); record = getFeeSumByPrdDataSet(dr); SQL = "Select * from [FeeSumByPrd] where FSPTaskCd = '" + toTaskCode + "' and [FSPMatter] =" + record.Matter + " and [FSPTkpr] = " + record.Tkpr + " and [FSPActivityCd] " + record.ActCode + " and FSPPrdYear = " + record.Year + " and FSPPrdNbr = " + record.Period; DataSet matches = _jurisUtility.RecordsetFromSQL(SQL); if (matches.Tables[0].Rows.Count > 0) //we need to combine because key already exists { double WorkedHrsBld = Double.Parse(matches.Tables[0].Rows[0]["FSPWorkedHrsBld"].ToString().Trim()); double HrsBilled = Double.Parse(matches.Tables[0].Rows[0]["FSPHrsBilled"].ToString().Trim()); double FeeBldStdValue = Double.Parse(matches.Tables[0].Rows[0]["FSPFeeBldStdValue"].ToString().Trim()); double FeeBldActualValue = Double.Parse(matches.Tables[0].Rows[0]["FSPFeeBldActualValue"].ToString().Trim()); double FeeBldActualAmt = Double.Parse(matches.Tables[0].Rows[0]["FSPFeeBldActualAmt"].ToString().Trim()); double FeeReceived = Double.Parse(matches.Tables[0].Rows[0]["FSPFeeReceived"].ToString().Trim()); double FeeAdjusted = Double.Parse(matches.Tables[0].Rows[0]["FSPFeeAdjusted"].ToString().Trim()); double WorkedHrsEntered = Double.Parse(matches.Tables[0].Rows[0]["FSPWorkedHrsEntered"].ToString().Trim()); double NonBilHrsEntered = Double.Parse(matches.Tables[0].Rows[0]["FSPNonBilHrsEntered"].ToString().Trim()); double BilHrsEntered = Double.Parse(matches.Tables[0].Rows[0]["FSPBilHrsEntered"].ToString().Trim()); double FeeEnteredStdValue = Double.Parse(matches.Tables[0].Rows[0]["FSPFeeEnteredStdValue"].ToString().Trim()); double FeeEnteredActualValue = Double.Parse(matches.Tables[0].Rows[0]["FSPFeeEnteredActualValue"].ToString().Trim()); //update record with correct task code SQL = "update FeeSumByPrd set [FSPWorkedHrsEntered] = Cast(" + WorkedHrsEntered + " + " + record.WorkedHrsEntered + " as decimal(12,2)) ,[FSPNonBilHrsEntered] = Cast(" + NonBilHrsEntered + " + " + record.NonBilHrsEntered + " as decimal(12,2)) ,[FSPBilHrsEntered] = Cast(" + BilHrsEntered + " + " + record.BilHrsEntered + " as decimal(12,2)) ,[FSPFeeEnteredStdValue] = Cast(" + FeeEnteredStdValue + " + " + record.FeeEnteredStdValue + " as decimal(12,2)) ,[FSPFeeEnteredActualValue] = Cast(" + FeeEnteredActualValue + " + " + record.FeeEnteredActualValue + " as decimal(12,2)),[FSPWorkedHrsBld] = Cast(" + WorkedHrsBld + " + " + record.WorkHrsBld + " as decimal(12,2)) ,[FSPHrsBilled] = Cast(" + HrsBilled + " + " + record.HrsBld + " as decimal(12,2)) ,[FSPFeeBldStdValue] = Cast(" + FeeBldStdValue + " + " + record.StdValueBld + " as decimal(12,2)) ,[FSPFeeBldActualValue] = Cast(" + FeeBldActualValue + " + " + record.ActualValueBld + " as decimal(12,2)) ,[FSPFeeBldActualAmt] = Cast(" + FeeBldActualAmt + " + " + record.ActualAmtBld + " as decimal(12,2)) ,[FSPFeeReceived] = Cast(" + FeeReceived + " + " + record.Rcvd + " as decimal(12,2)) ,[FSPFeeAdjusted] = Cast(" + FeeAdjusted + " + " + record.Adj + " as decimal(12,2)) where FSPTaskCd = '" + fromTaskCode + "' and [FSPMatter] =" + record.Matter + " and [FSPTkpr] = " + record.Tkpr + " and [FSPActivityCd] " + record.ActCode + " and FSPPrdYear = " + record.Year + " and FSPPrdNbr = " + record.Period; _jurisUtility.ExecuteNonQueryCommand(0, SQL); //delete record with old task code SQL = "delete from FeeSumByPrd where FSPTaskCd = '" + fromTaskCode + "' and [FSPMatter] =" + record.Matter + " and [FSPTkpr] = " + record.Tkpr + " and [FSPActivityCd] " + record.ActCode + " and FSPPrdYear = " + record.Year + " and FSPPrdNbr = " + record.Period; _jurisUtility.ExecuteNonQueryCommand(0, SQL); } else //no match so we can just update { SQL = "update FeeSumByPrd set FSPTaskCd ='" + toTaskCode + "' where FSPTaskCd = '" + fromTaskCode + "' and [FSPMatter] =" + record.Matter + " and [FSPTkpr] = " + record.Tkpr + " and [FSPActivityCd] " + record.ActCode + " and FSPPrdYear = " + record.Year + " and FSPPrdNbr = " + record.Period; _jurisUtility.ExecuteNonQueryCommand(0, SQL); } } } fromArf.Clear(); UpdateStatus("Updating Matter Fee Budget...", 4, 8); SQL = "update MatterFeeBudget set MFBTaskCode ='" + toTaskCode + "' where MFBTaskCode = '" + fromTaskCode + "'"; _jurisUtility.ExecuteNonQueryCommand(0, SQL); SQL = "update MatterExpBudget set MEBTaskCode ='" + toTaskCode + "' where MEBTaskCode = '" + fromTaskCode + "'"; _jurisUtility.ExecuteNonQueryCommand(0, SQL); SQL = "update ExpBatchDetail set EBDBudgTaskCd ='" + toTaskCode + "' where EBDBudgTaskCd = '" + fromTaskCode + "'"; _jurisUtility.ExecuteNonQueryCommand(0, SQL); UpdateStatus("Updating Time Batch Detail...", 5, 8); SQL = "update TimeBatchDetail set TBDTaskCd ='" + toTaskCode + "' where TBDTaskCd = '" + fromTaskCode + "'"; _jurisUtility.ExecuteNonQueryCommand(0, SQL); UpdateStatus("Updating Time Entries...", 6, 8); SQL = "update TimeEntry set TaskCode ='" + toTaskCode + "' where TaskCode = '" + fromTaskCode + "'"; _jurisUtility.ExecuteNonQueryCommand(0, SQL); SQL = "update VoucherBatchMatDist set VBMBudgTaskCd ='" + toTaskCode + "' where VBMBudgTaskCd = '" + fromTaskCode + "'"; _jurisUtility.ExecuteNonQueryCommand(0, SQL); SQL = "update VoucherMatDist set VMBudgTaskCd ='" + toTaskCode + "' where VMBudgTaskCd = '" + fromTaskCode + "'"; _jurisUtility.ExecuteNonQueryCommand(0, SQL); UpdateStatus("Updating Unbilled Time...", 7, 8); //xref SQL = "Select * from [TaskCodeXref] where TCXFirmsCode = '" + fromTaskCode + "'"; fromArf = _jurisUtility.RecordsetFromSQL(SQL); //if old code exists, we only need to check if new one does as well. if (fromArf.Tables[0].Rows.Count > 0) // we have records from the old code so now we check if the new one exists { foreach (DataRow dr in fromArf.Tables[0].Rows) { //see if the new key exists as well with the same TCXList ID string TCXList = dr["TCXList"].ToString().Trim(); SQL = "select * from TaskCodeXref where TCXFirmsCode = '" + toTaskCode + "'"; DataSet temp = _jurisUtility.RecordsetFromSQL(SQL); if (fromArf.Tables[0].Rows.Count > 0) //if new code also exists, delete the record with the old code { SQL = "delete from TaskCodeXref where TCXFirmsCode = '" + fromTaskCode + "' and TCXList = '" + TCXList + "'"; _jurisUtility.ExecuteNonQueryCommand(0, SQL); } //if the old code exists but the new one does not, update the old key to be the new key else { SQL = "update TaskCodeXref set TCXFirmsCode = '" + toTaskCode + "' where TCXList = '" + TCXList + "' and TCXFirmsCode = '" + fromTaskCode + "'"; _jurisUtility.ExecuteNonQueryCommand(0, SQL); } } }//if the old one does not exist, we dont care SQL = "update UnbilledTime set UTTaskCd ='" + toTaskCode + "' where UTTaskCd = '" + fromTaskCode + "'"; _jurisUtility.ExecuteNonQueryCommand(0, SQL); UpdateStatus("All tables updated.", 8, 8); DialogResult dr1 = MessageBox.Show("Would you like to delete the Task Code?", "Deletion prompt", MessageBoxButtons.YesNo, MessageBoxIcon.Question); if (dr1 == System.Windows.Forms.DialogResult.Yes) { SQL = "delete from TaskCode where TaskCdCode = '" + fromTaskCode + "'"; _jurisUtility.ExecuteNonQueryCommand(0, SQL); } MessageBox.Show("The process is complete", "Finished", MessageBoxButtons.OK, MessageBoxIcon.None); toTaskCode = ""; fromTaskCode = ""; button1.Enabled = false; } }