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);
}
