private void LoadJailRoomAndBoard() { DataTable dt = new DataTable(); const string sql = "Report_JailRoomBoard"; using (SqlConnection con = DBSettings.NewSqlConnectionClosed) using (SqlCommand cmd = new SqlCommand(sql, con)) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@input_DateTime", SqlDbType.DateTime).Value = this.ucCriteriaAccountStatus.InputDate; dt = DBSettings.ExecuteDataAdapter(this.GetType().ToString(), cmd); } dt.TableName = "JailRoomAndBoardTable"; _ds = new DataSet(); _ds.Tables.Add(dt); _ds.AcceptChanges(); BindingSource binding = new BindingSource(_ds, "JailRoomAndBoardTable"); ReportParameter[] parameters = new ReportParameter[] { new ReportParameter("SnapShotDate", ucCriteriaAccountStatus.InputDate.ToShortDateString()) }; this.reportViewer.LocalReport.ReportEmbeddedResource = "county.feecollections.reports.rptJailReport.rdlc"; this.reportViewer.LocalReport.DataSources.Add(new ReportDataSource("DataSet_sp_prison_room_and_board_data", binding)); this.reportViewer.LocalReport.SetParameters(parameters); }
private void LoadAccountStatusReport() { DataTable dt = new DataTable(); LocalUser user = new LocalUser(); string sql = (user.LenientBilling) ? "Report_AccountStatusLenientBilling" : "Report_AccountStatus"; using (SqlConnection con = DBSettings.NewSqlConnectionClosed) using (SqlCommand cmd = new SqlCommand(sql, con)) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@input_DateTime", SqlDbType.DateTime).Value = this.ucCriteriaAccountStatus.InputDate; dt = DBSettings.ExecuteDataAdapter(this.GetType().ToString(), cmd); } dt.TableName = "AccountStatusTable"; _ds = new DataSet(); _ds.Tables.Add(dt); _ds.AcceptChanges(); BindingSource binding = new BindingSource(_ds, "AccountStatusTable"); ReportParameter[] parameters = new ReportParameter[] { new ReportParameter("SnapShotDate", ucCriteriaAccountStatus.InputDate.ToShortDateString()) }; this.reportViewer.LocalReport.ReportEmbeddedResource = "county.feecollections.reports.rptAccountStatus.rdlc"; this.reportViewer.LocalReport.DataSources.Add(new ReportDataSource("DataSet_sp_account_status_data", binding)); this.reportViewer.LocalReport.SetParameters(parameters); }
private DataSet getData(DateTime date) { DataSet ds = new DataSet(); System.Data.DataTable dt = new System.Data.DataTable(); string sql = "Print_DelinquentNotices"; using (SqlConnection con = DBSettings.NewSqlConnectionClosed) using (SqlCommand cmd = new SqlCommand(sql, con)) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@input_DateTime", SqlDbType.DateTime).Value = date; dt = DBSettings.ExecuteDataAdapter(this.GetType().ToString(), cmd); dt.TableName = "AccountStatusTable"; ds.Tables.Add(dt); } return(ds); }
private void LoadBalanceLess100Report() { DataTable dt = new DataTable(); const string sql = "Report_BalanceLess100"; using (SqlConnection con = DBSettings.NewSqlConnectionClosed) using (SqlCommand cmd = new SqlCommand(sql, con)) { cmd.CommandType = CommandType.StoredProcedure; dt = DBSettings.ExecuteDataAdapter(this.GetType().ToString(), cmd); } dt.TableName = "CaseTable"; _ds = new DataSet(); _ds.Tables.Add(dt); _ds.AcceptChanges(); BindingSource binding = new BindingSource(_ds, "CaseTable"); this.reportViewer.LocalReport.ReportEmbeddedResource = "county.feecollections.reports.rptBalanceLess100.rdlc"; this.reportViewer.LocalReport.DataSources.Add(new ReportDataSource("DataSet_vw_AccntStatus", binding)); }
private void LoadCollectionsBreakdownReport() { // check report parameters if (DateTime.Parse(ucCriteriaCollectionsBreakdown.FromDate.ToShortDateString()) > DateTime.Parse(ucCriteriaCollectionsBreakdown.ToDate.ToShortDateString())) { MessageBox.Show(this, "From Date can not be greater than the To Date.", "Probation Report", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } DataTable dt = new DataTable(); const string sql = "Report_CollectionsBreakdown"; using (SqlConnection con = DBSettings.NewSqlConnectionClosed) using (SqlCommand cmd = new SqlCommand(sql, con)) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@fromdate", SqlDbType.DateTime).Value = ucCriteriaCollectionsBreakdown.FromDate; cmd.Parameters.Add("@todate", SqlDbType.DateTime).Value = ucCriteriaCollectionsBreakdown.ToDate; dt = DBSettings.ExecuteDataAdapter(this.GetType().ToString(), cmd); } dt.TableName = "PaymentsTable"; _ds = new DataSet(); _ds.Tables.Add(dt); _ds.AcceptChanges(); BindingSource binding = new BindingSource(_ds, "PaymentsTable"); ReportParameter[] parameters = new ReportParameter[] { new ReportParameter("FromDate", ucCriteriaCollectionsBreakdown.FromDate.ToString("d")), new ReportParameter("ToDate", ucCriteriaCollectionsBreakdown.ToDate.ToString("d")) }; this.reportViewer.LocalReport.ReportEmbeddedResource = "county.feecollections.reports.rptCollectionsBreakdown.rdlc"; this.reportViewer.LocalReport.DataSources.Add(new ReportDataSource("DataSet_AllPayments", binding)); this.reportViewer.LocalReport.SetParameters(parameters); }
private static void FillDataSource(Table wrdTable, int defendantId, int planId) { string sql0 = " SELECT TOP 1 firstname, middlename, lastname, aka, ssn, birthdate, driverslicense, " + " Defendant.street1, Defendant.street2, Defendant.city, a.abbreviation AS [state], Defendant.zip, phonehome, phonemobile, probationofficer, " + " Employer.employername, Employer.Street1 AS employer_street1, Employer.Street2 AS employer_street2, Employer.City AS employer_city, " + " b.abbreviation AS employer_state, Employer.Zip AS employer_zip, Employer.Phone AS employer_phone, " + " planname, " + " ( " + " SELECT SUM(total) AS balance " + " FROM " + " ( " + " SELECT SUM(amount) AS total " + " FROM PlanFee " + " LEFT OUTER JOIN FeeTypes ON PlanFee.feetypeid = FeeTypes.feetypeid " + " WHERE FeeTypes.billable = 1 " + " AND PlanFee.defendantid = @defendantId " + " AND PlanFee.planid = @planId " + " UNION " + " SELECT -SUM(amount) AS total " + " FROM FeePayment " + " LEFT OUTER JOIN FeeTypes ON FeePayment.feetypeid = FeeTypes.feetypeid " + " WHERE FeeTypes.billable = 1 " + " AND FeePayment.defendantid = @defendantId " + " AND FeePayment.planid = @planId " + " ) AS TransactionTotals " + " ) AS plan_remaining_balance, " + " payperiodtype, paymentarrangementtype, PlanPaymentArrangement.amount as payment_arrangement_amount, " + " startdate, enddate " + " FROM Defendant " + "LEFT OUTER JOIN States a ON Defendant.stateid = a.stateid " + "LEFT OUTER JOIN DefendantEmployers ON Defendant.defendantid = DefendantEmployers.defendantid AND SeparationDate IS NULL " + "LEFT OUTER JOIN Employer ON DefendantEmployers.employerid = Employer.employerid " + "LEFT OUTER JOIN States b ON Employer.stateid = b.stateid " + "LEFT OUTER JOIN DefendantPlans ON Defendant.defendantid = DefendantPlans.defendantid " + "LEFT OUTER JOIN PlanPaymentArrangement ON Defendant.defendantid = PlanPaymentArrangement.defendantid AND DefendantPlans.planid = PlanPaymentArrangement.planid " + "LEFT OUTER JOIN PayPeriodTypes ON PlanPaymentArrangement.payperiodtypeid = PayPeriodTypes.payperiodtypeid " + "LEFT OUTER JOIN PaymentArrangementTypes ON PlanPaymentArrangement.paymentarrangementtypeid = PaymentArrangementTypes.paymentarrangementtypeid " + " WHERE Defendant.defendantid = @defendantId " + " AND DefendantPlans.planid = @planId " + " ORDER BY enddate DESC;"; string sql1 = "SELECT casename, county, " + "CASE capp " + "WHEN 1 THEN 'yes' ELSE 'no' " + "END as capp, " + "CASE [Committed] " + "WHEN 1 THEN 'yes' ELSE 'no' " + "END as [committed] " + "FROM PlanCase " + "LEFT OUTER JOIN IowaCounty ON PlanCase.countyid = IowaCounty.countyid " + "WHERE defendantid = @defendantId " + "AND planid = @planId"; string sql2 = "SELECT feetype, amount AS fee_type_amount " + "FROM PlanFee " + "LEFT OUTER JOIN FeeTypes ON PlanFee.feetypeid = FeeTypes.feetypeid " + "WHERE defendantid = @defendantId " + "AND planid = @planId; "; string sql3 = "SELECT SUM(CourtFines) AS CourtFines, SUM(Restitution) AS Restitution, " + "SUM(CivilPenalties) AS CivilPenalties, SUM(JailRoomAndBoard) AS JailRoomAndBoard FROM " + "(SELECT " + "CASE feetypeid " + "WHEN 19 THEN amount " + "ELSE 0.00 " + "END AS CourtFines, " + "Case feetypeid " + "WHEN 20 THEN amount " + "ELSE 0.00 " + "END AS Restitution, " + "Case feetypeid " + "WHEN 21 THEN amount " + "ELSE 0.00 " + "END AS CivilPenalties, " + "Case feetypeid " + "WHEN 22 THEN amount " + "ELSE 0.00 " + "END AS JailRoomAndBoard " + "FROM PlanFee WHERE defendantid = @defendantId AND planid = @planId) AS FeeBreakDown"; DataSet ds = new DataSet(); DateTime tempDate; string tempStr; using (SqlConnection con = DBSettings.NewSqlConnectionClosed) using (SqlCommand cmd = new SqlCommand(sql0, con)) { System.Data.DataTable dt = new System.Data.DataTable(); cmd.Parameters.Add("@defendantId", SqlDbType.Int).Value = defendantId; cmd.Parameters.Add("@planId", SqlDbType.Int).Value = planId; // general defendant dt = DBSettings.ExecuteDataAdapter("MailMerge", cmd); dt.TableName = "General"; ds.Tables.Add(dt.Copy()); // cases cmd.CommandText = sql1; dt = DBSettings.ExecuteDataAdapter("MailMerge", cmd); dt.TableName = "Cases"; ds.Tables.Add(dt.Copy()); // fees cmd.CommandText = sql2; dt = DBSettings.ExecuteDataAdapter("MailMerge", cmd); dt.TableName = "Fees"; ds.Tables.Add(dt.Copy()); // test cmd.CommandText = sql3; dt = DBSettings.ExecuteDataAdapter("MailMerge", cmd); dt.TableName = "FeeBreakDown"; ds.Tables.Add(dt.Copy()); dt.Dispose(); } wrdTable.Cell(2, 1).Range.InsertAfter(ds.Tables["General"].Rows[0]["firstname"].ToString()); wrdTable.Cell(2, 2).Range.InsertAfter(ds.Tables["General"].Rows[0]["middlename"].ToString()); wrdTable.Cell(2, 3).Range.InsertAfter(ds.Tables["General"].Rows[0]["lastname"].ToString()); wrdTable.Cell(2, 4).Range.InsertAfter(ds.Tables["General"].Rows[0]["aka"].ToString()); wrdTable.Cell(2, 5).Range.InsertAfter(ds.Tables["General"].Rows[0]["ssn"].ToString()); if (DateTime.TryParse(ds.Tables["General"].Rows[0]["birthdate"].ToString(), out tempDate)) { wrdTable.Cell(2, 6).Range.InsertAfter(tempDate.ToString("d")); } wrdTable.Cell(2, 7).Range.InsertAfter(ds.Tables["General"].Rows[0]["driverslicense"].ToString()); wrdTable.Cell(2, 8).Range.InsertAfter(ds.Tables["General"].Rows[0]["street1"].ToString()); wrdTable.Cell(2, 9).Range.InsertAfter(ds.Tables["General"].Rows[0]["street2"].ToString()); wrdTable.Cell(2, 10).Range.InsertAfter(ds.Tables["General"].Rows[0]["city"].ToString()); wrdTable.Cell(2, 11).Range.InsertAfter(ds.Tables["General"].Rows[0]["state"].ToString()); wrdTable.Cell(2, 12).Range.InsertAfter(ds.Tables["General"].Rows[0]["zip"].ToString()); wrdTable.Cell(2, 13).Range.InsertAfter(ds.Tables["General"].Rows[0]["phonehome"].ToString()); wrdTable.Cell(2, 14).Range.InsertAfter(ds.Tables["General"].Rows[0]["phonemobile"].ToString()); wrdTable.Cell(2, 15).Range.InsertAfter(ds.Tables["General"].Rows[0]["probationofficer"].ToString()); // creating employer full address field tempStr = ""; if (!string.IsNullOrEmpty(ds.Tables["General"].Rows[0]["employername"].ToString())) { tempStr += ds.Tables["General"].Rows[0]["employername"].ToString() + "\n"; } if (!string.IsNullOrEmpty(ds.Tables["General"].Rows[0]["employer_street1"].ToString())) { tempStr += ds.Tables["General"].Rows[0]["employer_street1"].ToString() + "\n"; } if (!string.IsNullOrEmpty(ds.Tables["General"].Rows[0]["employer_street2"].ToString())) { tempStr += ds.Tables["General"].Rows[0]["employer_street2"].ToString() + "\n"; } if (!string.IsNullOrEmpty(ds.Tables["General"].Rows[0]["employer_city"].ToString())) { tempStr += ds.Tables["General"].Rows[0]["employer_city"].ToString(); } if (!string.IsNullOrEmpty(ds.Tables["General"].Rows[0]["employer_city"].ToString()) && !string.IsNullOrEmpty(ds.Tables["General"].Rows[0]["employer_state"].ToString())) { tempStr += ","; } if (!string.IsNullOrEmpty(ds.Tables["General"].Rows[0]["employer_state"].ToString())) { tempStr += ds.Tables["General"].Rows[0]["employer_state"].ToString(); } if (!string.IsNullOrEmpty(ds.Tables["General"].Rows[0]["employer_zip"].ToString())) { tempStr += ds.Tables["General"].Rows[0]["employer_zip"].ToString(); } if (!string.IsNullOrEmpty(ds.Tables["General"].Rows[0]["employer_phone"].ToString())) { tempStr += "\n" + ds.Tables["General"].Rows[0]["employer_phone"].ToString(); } wrdTable.Cell(2, 16).Range.InsertAfter(tempStr); wrdTable.Cell(2, 17).Range.InsertAfter(ds.Tables["General"].Rows[0]["employername"].ToString()); wrdTable.Cell(2, 18).Range.InsertAfter(ds.Tables["General"].Rows[0]["employer_street1"].ToString()); wrdTable.Cell(2, 19).Range.InsertAfter(ds.Tables["General"].Rows[0]["employer_street2"].ToString()); wrdTable.Cell(2, 20).Range.InsertAfter(ds.Tables["General"].Rows[0]["employer_city"].ToString()); wrdTable.Cell(2, 21).Range.InsertAfter(ds.Tables["General"].Rows[0]["employer_state"].ToString()); wrdTable.Cell(2, 22).Range.InsertAfter(ds.Tables["General"].Rows[0]["employer_zip"].ToString()); wrdTable.Cell(2, 23).Range.InsertAfter(ds.Tables["General"].Rows[0]["employer_phone"].ToString()); tempStr = ""; tempStr += ds.Tables["General"].Rows[0]["planname"].ToString(); if (!string.IsNullOrEmpty(ds.Tables["General"].Rows[0]["plan_remaining_balance"].ToString())) { tempStr += "\t\t" + Convert.ToDouble(ds.Tables["General"].Rows[0]["plan_remaining_balance"]).ToString("c2"); wrdTable.Cell(2, 26).Range.InsertAfter(Convert.ToDouble(ds.Tables["General"].Rows[0]["plan_remaining_balance"]).ToString("c2")); } wrdTable.Cell(2, 24).Range.InsertAfter(tempStr); wrdTable.Cell(2, 25).Range.InsertAfter(ds.Tables["General"].Rows[0]["planname"].ToString()); if (!string.IsNullOrEmpty(ds.Tables["General"].Rows[0]["plan_remaining_balance"].ToString())) { tempStr += "\t\t" + Convert.ToDouble(ds.Tables["General"].Rows[0]["plan_remaining_balance"]).ToString("c2"); } // enter pay periods if (!string.IsNullOrEmpty(ds.Tables["General"].Rows[0]["payperiodtype"].ToString()) && !string.IsNullOrEmpty(ds.Tables["General"].Rows[0]["paymentarrangementtype"].ToString()) && !string.IsNullOrEmpty(ds.Tables["General"].Rows[0]["payment_arrangement_amount"].ToString())) { tempStr = ds.Tables["General"].Rows[0]["payperiodtype"].ToString() + "\t" + ds.Tables["General"].Rows[0]["paymentarrangementtype"].ToString() + "\t" + Convert.ToDouble(ds.Tables["General"].Rows[0]["payment_arrangement_amount"]).ToString("c2"); wrdTable.Cell(2, 28).Range.InsertAfter(ds.Tables["General"].Rows[0]["payperiodtype"].ToString()); wrdTable.Cell(2, 29).Range.InsertAfter(ds.Tables["General"].Rows[0]["paymentarrangementtype"].ToString()); wrdTable.Cell(2, 30).Range.InsertAfter(Convert.ToDouble(ds.Tables["General"].Rows[0]["payment_arrangement_amount"]).ToString("c2")); } if (DateTime.TryParse(ds.Tables["General"].Rows[0]["startdate"].ToString(), out tempDate)) { wrdTable.Cell(2, 31).Range.InsertAfter(tempDate.ToString("d")); tempStr += "\t" + tempDate.ToString("d"); } if (DateTime.TryParse(ds.Tables["General"].Rows[0]["enddate"].ToString(), out tempDate)) { wrdTable.Cell(2, 32).Range.InsertAfter(tempDate.ToString("d")); tempStr += "\t" + tempDate.ToString("d"); } wrdTable.Cell(2, 27).Range.InsertAfter(tempStr); // building string for all defendant cases; also buidling strings separated by CAPP and NonCAPP case types StringBuilder strPlanCasesDetail = new StringBuilder(); StringBuilder strPlanCases = new StringBuilder(); StringBuilder strCaseCounties = new StringBuilder(); StringBuilder strCaseCAPP = new StringBuilder(); StringBuilder strCaseCommitted = new StringBuilder(); StringBuilder strCaseNotCommitted = new StringBuilder(); //StringBuilder strCaseAll = new StringBuilder(); StringBuilder strCAPP_PlanCasesDetail = new StringBuilder(); StringBuilder strCAPP_PlanCases = new StringBuilder(); StringBuilder strCAPP_CaseCounties = new StringBuilder(); StringBuilder strCAPP_CaseCAPP = new StringBuilder(); StringBuilder strCAPP_CaseCommitted = new StringBuilder(); StringBuilder strCAPP_CaseNotCommitted = new StringBuilder(); StringBuilder strNonCAPP_PlanCasesDetail = new StringBuilder(); StringBuilder strNonCAPP_PlanCases = new StringBuilder(); StringBuilder strNonCAPP_CaseCounties = new StringBuilder(); StringBuilder strNonCAPP_CaseCAPP = new StringBuilder(); StringBuilder strNonCAPP_CaseCommitted = new StringBuilder(); StringBuilder strNonCAPP_CaseNotCommitted = new StringBuilder(); foreach (DataRow dr in ds.Tables["Cases"].Rows) { strPlanCases.Append(dr["casename"].ToString() + "\n"); strCaseCounties.Append(dr["county"].ToString() + "\n"); strCaseCAPP.Append(dr["capp"].ToString() + "\n"); strCaseCommitted.Append(dr["committed"].ToString() + "\n"); strPlanCasesDetail.Append(dr["casename"].ToString() + "\t"); strPlanCasesDetail.Append(dr["county"].ToString() + "\t"); strPlanCasesDetail.Append(dr["capp"].ToString() + "\t"); strPlanCasesDetail.Append(dr["committed"].ToString() + "\n"); if (dr["capp"].ToString().ToLower() == "yes") { strCAPP_PlanCases.Append(dr["casename"].ToString() + "\n"); strCAPP_CaseCounties.Append(dr["county"].ToString() + "\n"); strCAPP_CaseCAPP.Append(dr["capp"].ToString() + "\n"); strCAPP_CaseCommitted.Append(dr["committed"].ToString() + "\n"); strCAPP_PlanCasesDetail.Append(dr["casename"].ToString() + "\t"); strCAPP_PlanCasesDetail.Append(dr["county"].ToString() + "\t"); strCAPP_PlanCasesDetail.Append(dr["capp"].ToString() + "\t"); strCAPP_PlanCasesDetail.Append(dr["committed"].ToString() + "\n"); } else if (dr["capp"].ToString().ToLower() == "no") { strNonCAPP_PlanCases.Append(dr["casename"].ToString() + "\n"); strNonCAPP_CaseCounties.Append(dr["county"].ToString() + "\n"); strNonCAPP_CaseCAPP.Append(dr["capp"].ToString() + "\n"); strNonCAPP_CaseCommitted.Append(dr["committed"].ToString() + "\n"); strNonCAPP_PlanCasesDetail.Append(dr["casename"].ToString() + "\t"); strNonCAPP_PlanCasesDetail.Append(dr["county"].ToString() + "\t"); strNonCAPP_PlanCasesDetail.Append(dr["capp"].ToString() + "\t"); strNonCAPP_PlanCasesDetail.Append(dr["committed"].ToString() + "\n"); } } wrdTable.Cell(2, 33).Range.InsertAfter(strPlanCasesDetail.ToString()); wrdTable.Cell(2, 34).Range.InsertAfter(strPlanCases.ToString()); wrdTable.Cell(2, 35).Range.InsertAfter(strCaseCounties.ToString()); wrdTable.Cell(2, 36).Range.InsertAfter(strCaseCAPP.ToString()); wrdTable.Cell(2, 37).Range.InsertAfter(strCaseCommitted.ToString()); wrdTable.Cell(2, 44).Range.InsertAfter(strCAPP_PlanCasesDetail.ToString()); wrdTable.Cell(2, 45).Range.InsertAfter(strCAPP_PlanCases.ToString()); wrdTable.Cell(2, 46).Range.InsertAfter(strCAPP_CaseCounties.ToString()); wrdTable.Cell(2, 47).Range.InsertAfter(strCAPP_CaseCAPP.ToString()); wrdTable.Cell(2, 48).Range.InsertAfter(strCAPP_CaseCommitted.ToString()); wrdTable.Cell(2, 49).Range.InsertAfter(strNonCAPP_PlanCasesDetail.ToString()); wrdTable.Cell(2, 50).Range.InsertAfter(strNonCAPP_PlanCases.ToString()); wrdTable.Cell(2, 51).Range.InsertAfter(strNonCAPP_CaseCounties.ToString()); wrdTable.Cell(2, 52).Range.InsertAfter(strNonCAPP_CaseCAPP.ToString()); wrdTable.Cell(2, 53).Range.InsertAfter(strNonCAPP_CaseCommitted.ToString()); wrdTable.Cell(2, 54).Range.InsertAfter("$" + ds.Tables["FeeBreakDown"].Rows[0]["CourtFines"].ToString()); wrdTable.Cell(2, 55).Range.InsertAfter("$" + ds.Tables["FeeBreakDown"].Rows[0]["Restitution"].ToString()); wrdTable.Cell(2, 56).Range.InsertAfter("$" + ds.Tables["FeeBreakDown"].Rows[0]["CivilPenalties"].ToString()); wrdTable.Cell(2, 57).Range.InsertAfter("$" + ds.Tables["FeeBreakDown"].Rows[0]["JailRoomAndBoard"].ToString()); // entering fee types tempStr = ""; string strFeeType = ""; string strFeeTypeAmount = ""; double dblTemp = 0; double dblFeesTotal = 0; foreach (DataRow dr in ds.Tables["Fees"].Rows) { strFeeType += dr["feetype"].ToString() + "\n"; if (Double.TryParse(dr["fee_type_amount"].ToString(), out dblTemp)) { strFeeTypeAmount += dblTemp.ToString("C2") + "\n"; dblFeesTotal += dblTemp; } tempStr += dr["feetype"].ToString() + "\t" + dblTemp.ToString("C2") + "\n"; } tempStr += "Total Fees\t" + dblFeesTotal.ToString("C2"); wrdTable.Cell(2, 38).Range.InsertAfter(tempStr); wrdTable.Cell(2, 39).Range.InsertAfter(dblFeesTotal.ToString("C2")); wrdTable.Cell(2, 40).Range.InsertAfter(strFeeType); wrdTable.Cell(2, 41).Range.InsertAfter(strFeeTypeAmount); // creating field for firstname lastname wrdTable.Cell(2, 42).Range.InsertAfter(ds.Tables["General"].Rows[0]["firstname"].ToString() + " " + ds.Tables["General"].Rows[0]["lastname"].ToString()); // creating full address field tempStr = ""; tempStr += ds.Tables["General"].Rows[0]["street1"].ToString() + "\n"; if (!string.IsNullOrEmpty(ds.Tables["General"].Rows[0]["street2"].ToString())) { tempStr += ds.Tables["General"].Rows[0]["street2"].ToString() + "\n"; } if (!string.IsNullOrEmpty(ds.Tables["General"].Rows[0]["city"].ToString())) { tempStr += ds.Tables["General"].Rows[0]["city"].ToString(); } if (!string.IsNullOrEmpty(ds.Tables["General"].Rows[0]["city"].ToString()) && !string.IsNullOrEmpty(ds.Tables["General"].Rows[0]["state"].ToString())) { tempStr += ","; } tempStr += " " + ds.Tables["General"].Rows[0]["state"].ToString() + " " + ds.Tables["General"].Rows[0]["zip"].ToString(); wrdTable.Cell(2, 43).Range.InsertAfter(tempStr); }
private DataSet GetProbationReportData() { DataSet dsDefendantsCases = new DataSet(); dsDefendantsCases.DataSetName = "ProbationReport"; DataTable dt = new DataTable(); using (SqlConnection con = DBSettings.NewSqlConnectionClosed) using (SqlCommand cmd = new SqlCommand("", con)) { cmd.Parameters.Add("@capp", SqlDbType.Bit).Value = ucCriteriaSubReportCases.IncludeCAPPCases; cmd.Parameters.Add("@noncapp", SqlDbType.Bit).Value = ucCriteriaSubReportCases.IncludeNonCAPPCases; cmd.CommandText = "SELECT DISTINCT a.defendantid as defendantid, a.firstname + ' ' + a.middlename as firstname, a.lastname as lastname, " + "a.probationofficer as probationofficer, b.receiveddate as receiveddate, dp.noncompliancenotice, " + "CASE " + "WHEN DateDiff(Day, b.receiveddate, getdate()) > 30 THEN 'Yes' " + "WHEN DateDiff(Day, b.receiveddate, getdate()) <= 30 THEN 'No' " + "WHEN DateDiff(Day, c.startdate, getdate()) > 30 THEN 'Yes' " + "WHEN DateDiff(Day, c.startdate, getdate()) <= 30 THEN 'No' " + "END as deliquentstatus, ISNULL(z.amount ,0) AS amount " + "FROM Defendant as a " + "LEFT OUTER JOIN (select defendantid, MAX(receiveddate) as receiveddate from FeePayment group by defendantid) as b on a.defendantid = b.defendantid " + "LEFT OUTER JOIN (select defendantid, amount, receiveddate from FeePayment) as z on a.defendantid = z.defendantid AND b.receiveddate = z.receiveddate " + "INNER JOIN PlanPaymentArrangement AS c ON a.defendantid = c.defendantid " + "INNER JOIN DefendantPlans dp on a.defendantid = dp.defendantid " + "WHERE active = 1 AND hasprobationofficer = 1 " + "ORDER BY probationofficer, lastname, firstname "; dt = DBSettings.ExecuteDataAdapter(this.GetType().ToString(), cmd); dt.TableName = "DefendantTable"; dsDefendantsCases.Tables.Add(dt.Copy()); cmd.CommandText = "SELECT Defendant.defendantid, ISNULL(casename, 'null_casename') as casename, ISNULL(PlanCase.capp, '' ) as capp FROM Defendant " + "LEFT OUTER JOIN PlanCase ON PlanCase.defendantid = Defendant.defendantid " + "WHERE active = 1 AND hasprobationofficer = 1 " + "AND (PlanCase.capp = @capp OR PlanCase.capp = @noncapp ) " + "AND ((@capp = 1 AND @noncapp <> 1) OR (@capp <> 1 AND @noncapp = 1) OR (@capp = 1 AND @noncapp = 1)) " + "ORDER BY Defendant.defendantid; "; dt = DBSettings.ExecuteDataAdapter(this.GetType().ToString(), cmd); dt.TableName = "CaseTable"; dsDefendantsCases.Tables.Add(dt.Copy()); foreach (DataRow dr in dsDefendantsCases.Tables["DefendantTable"].Rows) { // does the defendant table have a record. DataRow[] drs = dsDefendantsCases.Tables["CaseTable"].Select("defendantid = " + dr["defendantid"]); if (drs.Length == 1) { // add a couple of records to even out the rdlc columns DataRow newDr; newDr = dsDefendantsCases.Tables["CaseTable"].NewRow(); newDr["defendantid"] = dr["defendantid"]; newDr["casename"] = ""; dsDefendantsCases.Tables["CaseTable"].Rows.Add(newDr); } if (drs.Length <= 0) { // add a couple of records to even out the rdlc columns DataRow newDr; newDr = dsDefendantsCases.Tables["CaseTable"].NewRow(); newDr["defendantid"] = dr["defendantid"]; newDr["casename"] = ""; dsDefendantsCases.Tables["CaseTable"].Rows.Add(newDr); newDr = dsDefendantsCases.Tables["CaseTable"].NewRow(); newDr["defendantid"] = dr["defendantid"]; newDr["casename"] = ""; dsDefendantsCases.Tables["CaseTable"].Rows.Add(newDr); } } } return(dsDefendantsCases); }