private void BuildPlanCases()
{
const string sql = "SELECT casename, countyid, committed, commitdate, commitbasedate, commitdaystill, capp, "
+ "updatedby, updateddate "
+ "FROM PlanCase "
+ "WHERE planid = @planid "
+ "AND defendantid = @defendantid "
+ "ORDER BY updateddate; ";
using (SqlCommand cmd = new SqlCommand(sql))
{
cmd.Parameters.Add("@planid", SqlDbType.Int).Value = ParentId;
cmd.Parameters.Add("@defendantid", SqlDbType.Int).Value = _intDefendantId;
using (SqlDataReader dr = DBSettings.ExecuteReader(this.GetType().Name, cmd))
{
while (dr.Read())
{
PlanCase plancase = PlanCase.CreateCase(dr, ParentId, _intDefendantId);
this.Add(plancase);
}
dr.Close();
}
}
}
private void BuildPlanFees()
{
const string sql = "SELECT FeeTypes.feetypeid, PlanFee.amount, "
+ "PlanFee.updatedby, PlanFee.updateddate "
+ "FROM PlanFee "
+ "LEFT OUTER JOIN FeeTypes ON PlanFee.feetypeid = FeeTypes.feetypeid "
+ "WHERE planid = @planid "
+ "AND defendantid = @defendantid "
+ "ORDER BY paymentorder ASC; ";
using (SqlCommand cmd = new SqlCommand(sql))
{
cmd.Parameters.Add("@planid", SqlDbType.Int).Value = ParentId;
cmd.Parameters.Add("@defendantid", SqlDbType.Int).Value = _intDefendantId;
using (SqlDataReader dr = DBSettings.ExecuteReader(this.GetType().Name, cmd))
{
while (dr.Read())
{
PlanFee planfee = PlanFee.CreateFee(dr, ParentId, _intDefendantId);
this.Add(planfee);
}
dr.Close();
}
}
}
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 void Select()
{
if (base.ID > 0)
{
const string sql = "SELECT feetype, paymentorder, billable, updatedby, updateddate "
+ "FROM FeeTypes "
+ "WHERE feetypeid = @id; ";
using (SqlCommand cmd = new SqlCommand(sql))
{
cmd.Parameters.Add("@id", SqlDbType.Int).Value = base.ID;
using (SqlDataReader dr = DBSettings.ExecuteReader(this.GetType().Name, cmd))
{
if (dr.Read())
{
InitializeProperties();
_strFeeTypeName = dr["feetype"].ToString();
if (!dr.IsDBNull(dr.GetOrdinal("paymentorder")))
{
_intPaymentOrder = Convert.ToInt32(dr["paymentorder"].ToString());
}
_Billable = Convert.ToBoolean(dr["billable"].ToString());
base.SetNewUpdateProperties(dr["updatedby"].ToString(), Convert.ToDateTime(dr["updateddate"]));
}
else
{
dr.Close();
throw new MyException(this.GetType().Name, MyErrorType.ConcurrencyObjectNotExists, new Exception());
}
dr.Close();
}
}
}
}
private void BuildFeePayments()
{
const string sql = "SELECT receiveddate, amount, "
+ "updatedby, updateddate "
+ "FROM FeePayment "
+ "WHERE defendantid = @id "
+ "AND planid = @planid "
+ "AND feetypeid = @feetypeid "
+ "ORDER BY receiveddate DESC; ";
using (SqlCommand cmd = new SqlCommand(sql))
{
cmd.Parameters.Add("@id", SqlDbType.Int).Value = ParentId;
cmd.Parameters.Add("@planid", SqlDbType.Int).Value = _intPlanId;
cmd.Parameters.Add("@feetypeid", SqlDbType.Int).Value = _intFeeTypeId;
using (SqlDataReader dr = DBSettings.ExecuteReader(this.GetType().Name, cmd))
{
while (dr.Read())
{
FeePayment payment = FeePayment.CreateFeePayment(dr, _intPlanId, ParentId, _intFeeTypeId);
this.Add(payment);
}
dr.Close();
}
}
}
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 Select()
{
if (base.ID > 0)
{
const string sql = "SELECT payperiodtype, updatedby, updateddate "
+ "FROM PayPeriodTypes "
+ "WHERE payperiodtypeid = @id; ";
using (SqlCommand cmd = new SqlCommand(sql))
{
cmd.Parameters.Add("@id", SqlDbType.Int).Value = base.ID;
using (SqlDataReader dr = DBSettings.ExecuteReader(this.GetType().Name, cmd))
{
if (dr.Read())
{
InitializeProperties();
_strPayPeriodType = dr["payperiodtype"].ToString();
base.SetNewUpdateProperties(dr["updatedby"].ToString(), Convert.ToDateTime(dr["updateddate"]));
}
else
{
dr.Close();
throw new MyException(this.GetType().Name, MyErrorType.ConcurrencyObjectNotExists, new Exception());
}
dr.Close();
}
}
}
}
private void BuildEmployerDefendantsList()
{
const string sql = "SELECT defendant.defendantid, defendant.firstname, defendant.middlename, defendant.lastname, "
+ "defendant.aka, defendant.ssn, defendant.birthdate, defendant.driverslicense, "
+ "defendant.street1, defendant.street2, defendant.city, defendant.stateid, defendant.zip, "
+ "defendant.phonehome, defendant.phonemobile, "
+ "defendant.hasprobationofficer, defendant.probationofficer, "
+ "defendant.barreduntil, defendant.notes, defendant.active, "
+ "defendantemployers.updatedby, defendantemployers.updateddate "
+ "FROM Employer "
+ "LEFT OUTER JOIN DefendantEmployers ON employer.employerid = defendantemployers.employerid "
+ "INNER JOIN Defendant ON defendantemployers.defendantid = defendant.defendantid "
+ "WHERE employer.employerid = @employerid "
+ "ORDER BY lastname, firstname DESC; ";
using (SqlCommand cmd = new SqlCommand(sql))
{
cmd.Parameters.Add("@employerid", SqlDbType.Int).Value = ParentId;
using (SqlDataReader dr = DBSettings.ExecuteReader(this.GetType().Name, cmd))
{
while (dr.Read())
{
Defendant defendant = Defendant.CreateCurrentDefendant(dr);
this.Add(defendant);
}
dr.Close();
}
}
}
protected void Delete(SqlCommand cmd)
{
cmd.Parameters.Add("@id", SqlDbType.Int).Value = _intID;
cmd.Parameters.Add("@updatedby", SqlDbType.VarChar).Value = _strUpdatedBy;
cmd.Parameters.Add("@updateddate", SqlDbType.DateTime).Value = _updatedDate;
if (!(DBSettings.ExecuteNonQuery(this.GetType().Name, cmd) > 0))
{
throw new MyException(this.GetType().Name, MyErrorType.ConcurrencyObjectUpdate, new Exception());
}
}
/// <summary>
/// This function executes the cmd paramenter by using ExecuteNonQuery. In addition,
/// this fuction sets the following SqlParameters:
///
/// @id - base object id
/// @updatedby - Current windows username
/// @updateddate - the update parameter
///
/// throws a MyErrorType.ConCurrentcyObjectUpdate exception if the update
/// doesn't return a value > 0.
/// </summary>
/// <param name="cmd">A SqlCommand object with update statement. (function will update @id, @updatedby, and @updateddate paramenters.)</param>
/// <param name="updateDate">The date the @updateddate parameter should be updated to in the database</param>
protected void Update(SqlCommand cmd, DateTime updateDate)
{
cmd.Parameters.Add("@id", SqlDbType.Int).Value = _intID;
cmd.Parameters.Add("@updatedby", SqlDbType.VarChar).Value = LocalUser.WindowsUserName;
cmd.Parameters.Add("@updateddate", SqlDbType.DateTime).Value = updateDate;
if (DBSettings.ExecuteNonQuery(this.GetType().Name, cmd) <= 0)
{
throw new MyException(this.GetType().Name, MyErrorType.ConcurrencyObjectUpdate, new Exception());
}
}
private void Select()
{
if (base.ID > 0)
{
const string sql = "SELECT casename, countyid, committed, commitdate, commitdaystill, commitbasedate, capp, updatedby, updateddate "
+ "FROM PlanCase "
+ "WHERE planid = @id "
+ "AND defendantid = @defendantid "
+ "AND casename = @casename; ";
using (SqlCommand cmd = new SqlCommand(sql))
{
cmd.Parameters.Add("@id", SqlDbType.Int).Value = base.ID;
cmd.Parameters.Add("@defendantid", SqlDbType.Int).Value = _intDefendantId;
cmd.Parameters.Add("@casename", SqlDbType.VarChar).Value = _strCaseName_orig;
using (SqlDataReader dr = DBSettings.ExecuteReader(this.GetType().Name, cmd))
{
if (dr.Read())
{
InitializeProperties();
_strCaseName = dr["casename"].ToString();
if (!dr.IsDBNull(dr.GetOrdinal("countyid")))
{
_intCountyId = Convert.ToInt32(dr["countyid"]);
}
_Committed = Convert.ToBoolean(dr["committed"].ToString());
if (!dr.IsDBNull(dr.GetOrdinal("commitdate")))
{
_CommitDate = (DateTime)dr["commitdate"];
}
if (!dr.IsDBNull(dr.GetOrdinal("commitdaystill")))
{
_dblCommitDaysTill = Convert.ToDouble(dr["commitdaystill"]);
}
if (!dr.IsDBNull(dr.GetOrdinal("commitbasedate")))
{
_CommitBaseDate = (DateTime)dr["commitbasedate"];
}
_CAPP = Convert.ToBoolean(dr["capp"].ToString());
base.SetNewUpdateProperties(dr["updatedby"].ToString(), (DateTime)dr["updateddate"]);
}
else
{
dr.Close();
throw new MyException(this.GetType().Name, MyErrorType.ConcurrencyObjectNotExists, new Exception());
}
dr.Close();
}
}
}
}
private void Select()
{
if (base.ID > 0)
{
const string sql = "SELECT payperiodtypeid, paymentarrangementtypeid, amount, startdate, enddate, updatedby, updateddate "
+ "FROM PlanPaymentArrangement "
+ "WHERE paymentarrangementid = @id "
+ "AND planid = @planid "
+ "AND defendantid = @defendantid; ";
using (SqlCommand cmd = new SqlCommand(sql))
{
cmd.Parameters.Add("@id", SqlDbType.Int).Value = base.ID;
cmd.Parameters.Add("@planid", SqlDbType.Int).Value = _intPlanId;
cmd.Parameters.Add("@defendantid", SqlDbType.Int).Value = _intDefendantId;
using (SqlDataReader dr = DBSettings.ExecuteReader(this.GetType().Name, cmd))
{
if (dr.Read())
{
InitializeProperties();
if (!dr.IsDBNull(dr.GetOrdinal("payperiodtypeid")))
{
_intPayPeriodTypeId = Convert.ToInt32(dr["payperiodtypeid"]);
}
if (!dr.IsDBNull(dr.GetOrdinal("paymentarrangementtypeid")))
{
_intPaymentArrangementTypeId = Convert.ToInt32(dr["paymentarrangementtypeid"]);
}
if (!dr.IsDBNull(dr.GetOrdinal("amount")))
{
_dblAmount = Convert.ToDouble(dr["amount"]);
}
if (!dr.IsDBNull(dr.GetOrdinal("startdate")))
{
_StartDate = (DateTime)dr["startdate"];
}
if (!dr.IsDBNull(dr.GetOrdinal("enddate")))
{
_EndDate = (DateTime)dr["enddate"];
}
base.SetNewUpdateProperties(dr["updatedby"].ToString(), (DateTime)dr["updateddate"]);
}
else
{
dr.Close();
throw new MyException(this.GetType().Name, MyErrorType.ConcurrencyObjectNotExists, new Exception());
}
dr.Close();
}
}
}
}
private void btnDBTest_Click(object sender, EventArgs e)
{
bool success = DBSettings.TestConnection(txtDBServer.Text.Trim(), txtDBName.Text.Trim(), txtDBUser.Text.Trim(), txtDBPassword.Text.Trim(), chkIntegratedSecurity.Checked);
if (success)
{
MyMessageBox.Show(this, "Database Settings", MyDisplayMessage.TestSuccess);
InitializePreferencesTab();
}
else
{
MessageBox.Show("A connection could not be made to the database. Please check your connection information.", "Database Error", MessageBoxButtons.OK);
}
}
private bool TestConnection()
{
while (true)
{
//try
//{
if (DBSettings.TestConnection())
{
return(true);
}
else
//{
//}
//catch (MyException myex)
{
try
{
if (DialogResult.No == MessageBox.Show("A connection could not be made to the database. Please check your connection information. Click \"Yes\" to try again or \"No\" to exit.", "Database Error", MessageBoxButtons.YesNo))
{
Environment.Exit(0);
}
frmOptions frm = new frmOptions();
frm.ShowInTaskbar = true;
frm.StartPosition = FormStartPosition.CenterScreen;
frm.Text = Application.CompanyName + " " + Application.ProductName + " Database Connection";
frm.ShowDialog();
if (frm.IsEmpty())
{
return(false);
}
}
catch (ArgumentException)
{
frmOptions frm = new frmOptions();
frm.ShowInTaskbar = true;
frm.StartPosition = FormStartPosition.CenterScreen;
frm.Text = Application.CompanyName + " " + Application.ProductName + " Database Connection";
frm.ShowDialog();
if (frm.IsEmpty())
{
return(false);
}
}
}
}
}
private void Select()
{
if (base.ID > 0)
{
const string sql = "SELECT employername, street1, street2, city, stateid, zip, phone, "
+ "separationdate, defendantemployers.updatedby, defendantemployers.updateddate "
+ "FROM DefendantEmployers JOIN Employer ON defendantemployers.employerid = employer.employerid "
+ "WHERE defendantid = @defendantid "
+ "AND employerid = @id; ";
using (SqlCommand cmd = new SqlCommand(sql))
{
cmd.Parameters.Add("@defendantid", SqlDbType.Int).Value = _intDefendantId;
cmd.Parameters.Add("@id", SqlDbType.Int).Value = base.ID;
using (SqlDataReader dr = DBSettings.ExecuteReader(this.GetType().Name, cmd))
{
if (dr.Read())
{
InitializeProperties();
this.RaiseChangedEvents = false;
EmployerName = dr["employername"].ToString();
Street1 = dr["street1"].ToString();
Street2 = dr["street2"].ToString();
City = dr["city"].ToString();
StateID = (dr.IsDBNull(dr.GetOrdinal("stateid"))) ? -1 : Convert.ToInt32(dr["stateid"].ToString());
Zip = dr["zip"].ToString();
Phone = dr["phone"].ToString();
if (!dr.IsDBNull(dr.GetOrdinal("separationdate")))
{
_SeparationDate = (DateTime)dr["separationdate"];
}
base.SetNewUpdateProperties(dr["updatedby"].ToString(), (DateTime)dr["updateddate"]);
this.RaiseChangedEvents = true;
}
else
{
dr.Close();
throw new MyException(this.GetType().Name, MyErrorType.ConcurrencyObjectNotExists, new Exception());
}
dr.Close();
}
}
}
}
private void Select()
{
if (base.ID > 0)
{
const string sql = "SELECT feetypeid, receiveddate, amount, updatedby, updateddate "
+ "FROM FeePayment "
+ "WHERE defendantid = @id "
+ "AND planid = @planid "
+ "AND feetypeid = @feetypeid "
+ "AND receiveddate = @receiveddate; ";
using (SqlCommand cmd = new SqlCommand(sql))
{
cmd.Parameters.Add("@id", SqlDbType.Int).Value = base.ID;
cmd.Parameters.Add("@planid", SqlDbType.Int).Value = _intPlanId_orig;
cmd.Parameters.Add("@feetypeid", SqlDbType.Int).Value = _intFeeTypeId_orig;
cmd.Parameters.Add("@receiveddate", SqlDbType.DateTime).Value = _ReceivedDate_orig;
using (SqlDataReader dr = DBSettings.ExecuteReader(this.GetType().Name, cmd))
{
if (dr.Read())
{
InitializeProperties();
if (!dr.IsDBNull(dr.GetOrdinal("feetypeid")))
{
_intFeeTypeId = Convert.ToInt32(dr["feetypeid"]);
}
if (!dr.IsDBNull(dr.GetOrdinal("receiveddate")))
{
_ReceivedDate = Convert.ToDateTime(dr["receiveddate"]);
}
if (!dr.IsDBNull(dr.GetOrdinal("amount")))
{
_dblAmount = Convert.ToDouble(dr["amount"]);
}
base.SetNewUpdateProperties(dr["updatedby"].ToString(), (DateTime)dr["updateddate"]);
}
else
{
dr.Close();
throw new MyException(this.GetType().Name, MyErrorType.ConcurrencyObjectNotExists, new Exception());
}
dr.Close();
}
}
}
}
private void BuildFeeTypeList()
{
const string sql = "SELECT feetypeid, feetype, paymentorder, billable, updatedby, updateddate "
+ "FROM FeeTypes "
+ "ORDER BY paymentorder ASC; ";
using (SqlCommand cmd = new SqlCommand(sql))
using (SqlDataReader dr = DBSettings.ExecuteReader(this.GetType().Name, cmd))
{
while (dr.Read())
{
FeeType feetype = FeeType.CreateFeeType(dr);
this.Add(feetype);
}
dr.Close();
}
}
private void InitializePreferencesTab()
{
try
{
DBSettings.TestConnection();
cbbCounties.DataSource = Helper.IowaCountyList;
cbbCounties.DisplayMember = "county";
cbbCounties.ValueMember = "countyid";
cbbCounties.SelectedValue = _user.HomeCountyId;
cbxLenientBilling.Checked = _user.LenientBilling;
}
catch
{
}
}
private void BuildUnrelatedEmployerList(string employerIdsCsv)
{
string sql = string.Format("SELECT employerid, employername, street1, street2, city, stateid, zip, phone, updatedby, updateddate "
+ "FROM Employer WHERE employerid NOT IN ( {0} ); ", employerIdsCsv);
using (SqlCommand cmd = new SqlCommand(sql))
{
using (SqlDataReader dr = DBSettings.ExecuteReader(this.GetType().Name, cmd))
{
while (dr.Read())
{
Employer employer = Employer.CreateEmployer(dr);
this.Add(employer);
}
dr.Close();
}
}
}
private void Select()
{
if (base.ID > 0)
{
const string sql = "SELECT planname, capp, noncapp, isfiled, hasinsurance, "
+ "incontempt, noncompliancenotice, fileddate, updatedby, updateddate "
+ "FROM DefendantPlans "
+ "WHERE planid = @id AND defendantid = @defendantid; ";
using (SqlCommand cmd = new SqlCommand(sql))
{
cmd.Parameters.Add("@id", SqlDbType.Int).Value = base.ID;
cmd.Parameters.Add("@defendantid", SqlDbType.Int).Value = _intDefendantId;
using (SqlDataReader dr = DBSettings.ExecuteReader(this.GetType().Name, cmd))
{
if (dr.Read())
{
InitializeProperties();
_strPlanName = dr["planname"].ToString();
_bolCAPP = (dr.IsDBNull(dr.GetOrdinal("capp"))) ? false : Convert.ToBoolean(dr["capp"].ToString());
_bolNonCAPP = (dr.IsDBNull(dr.GetOrdinal("noncapp"))) ? false : Convert.ToBoolean(dr["noncapp"].ToString());
_bolIsFiled = (dr.IsDBNull(dr.GetOrdinal("isfiled"))) ? false : Convert.ToBoolean(dr["isfiled"].ToString());
_bolNonComplianceNotice = (dr.IsDBNull(dr.GetOrdinal("noncompliancenotice"))) ? false : Convert.ToBoolean(dr["noncompliancenotice"].ToString());
_bolHasInsurance = (dr.IsDBNull(dr.GetOrdinal("hasinsurance"))) ? false : Convert.ToBoolean(dr["hasinsurance"].ToString());
_bolInContempt = (dr.IsDBNull(dr.GetOrdinal("incontempt"))) ? false : Convert.ToBoolean(dr["incontempt"].ToString());
if (!dr.IsDBNull(dr.GetOrdinal("fileddate")))
{
_FiledDate = dr.GetDateTime(dr.GetOrdinal("fileddate"));
}
base.SetNewUpdateProperties(dr["updatedby"].ToString(), (DateTime)dr["updateddate"]);
}
else
{
dr.Close();
throw new MyException(this.GetType().Name, MyErrorType.ConcurrencyObjectNotExists, new Exception());
}
dr.Close();
}
}
}
}
private void BuildRestrictedCasePrefixList()
{
const string sql = "SELECT prefixid, prefix, updatedby, updateddate "
+ "FROM RestrictedCasePrefixes "
+ "ORDER BY prefix DESC; ";
using (SqlCommand cmd = new SqlCommand(sql))
{
using (SqlDataReader dr = DBSettings.ExecuteReader(this.GetType().Name, cmd))
{
while (dr.Read())
{
RestrictedCasePrefix prefix = RestrictedCasePrefix.CreateCasePrefix(dr);
this.Add(prefix);
}
dr.Close();
}
}
}
private void BuildEmployerList()
{
const string sql = "SELECT employerid, employername, street1, street2, city, stateid, zip, phone, updatedby, updateddate "
+ "FROM Employer "
+ "ORDER BY employername DESC; ";
using (SqlCommand cmd = new SqlCommand(sql))
{
using (SqlDataReader dr = DBSettings.ExecuteReader(this.GetType().Name, cmd))
{
while (dr.Read())
{
Employer employer = Employer.CreateEmployer(dr);
this.Add(employer);
}
dr.Close();
}
}
}
private void BuildPaymentArrangementTypeList()
{
const string sql = "SELECT paymentarrangementtypeid, paymentarrangementtype, updatedby, updateddate "
+ "FROM PaymentArrangementTypes "
+ "ORDER BY paymentarrangementtype DESC; ";
using (SqlCommand cmd = new SqlCommand(sql))
{
using (SqlDataReader dr = DBSettings.ExecuteReader(this.GetType().Name, cmd))
{
while (dr.Read())
{
PaymentArrangementType paymentarrangementtype = PaymentArrangementType.CreatePaymentArrangementType(dr);
this.Add(paymentarrangementtype);
}
dr.Close();
}
}
}
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 BuildDefendantPlansList()
{
const string sql = "SELECT planid, planname, capp, noncapp, isfiled, hasinsurance, "
+ "incontempt, noncompliancenotice, fileddate, updatedby, updateddate "
+ "FROM DefendantPlans "
+ "WHERE defendantid = @defendantid "
+ "ORDER BY planname ASC; ";
using (SqlCommand cmd = new SqlCommand(sql))
{
cmd.Parameters.Add("@defendantid", SqlDbType.Int).Value = ParentId;
using (SqlDataReader dr = DBSettings.ExecuteReader(this.GetType().Name, cmd))
{
while (dr.Read())
{
Plan plan = Plan.CreateCurrentPlan(dr, ParentId);
this.Add(plan);
}
dr.Close();
}
}
}
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 BuildDefendantList()
{
const string sql = "SELECT defendantid, firstname, middlename, lastname, aka, ssn, birthdate, driverslicense, "
+ "street1, street2, city, stateid, zip, phonehome, phonemobile, "
+ "hasprobationofficer, probationofficer, "
+ "barreduntil, notes, active, updatedby, updateddate "
+ "FROM Defendant "
+ "WHERE active = 1 "
+ "ORDER BY lastname, firstname; ";
using (SqlCommand cmd = new SqlCommand(sql))
{
using (SqlDataReader dr = DBSettings.ExecuteReader(this.GetType().Name, cmd))
{
while (dr.Read())
{
Defendant defendant = Defendant.CreateCurrentDefendant(dr);
this.Add(defendant);
}
dr.Close();
}
}
}
private void BuildDefendantEmployersList()
{
const string sql = "SELECT employer.employerid, employername, street1, street2, city, stateid, zip, phone, "
+ "separationdate, defendantemployers.updatedby, defendantemployers.updateddate "
+ "FROM DefendantEmployers JOIN Employer ON defendantemployers.employerid = employer.employerid "
+ "WHERE defendantid = @defendantid "
+ "ORDER BY employername DESC; ";
using (SqlCommand cmd = new SqlCommand(sql))
{
cmd.Parameters.Add("@defendantid", SqlDbType.VarChar).Value = ParentId;
using (SqlDataReader dr = DBSettings.ExecuteReader(this.GetType().Name, cmd))
{
while (dr.Read())
{
EmployerDefendant defendant = EmployerDefendant.CreateEmployerDefendant(dr, ParentId);
this.Add(defendant);
}
dr.Close();
}
}
}
private void Select()
{
if (base.ID > 0)
{
const string sql = "SELECT employername, street1, street2, city, stateid, zip, phone, updatedby, updateddate "
+ "FROM Employer "
+ "WHERE employerid = @id; ";
using (SqlCommand cmd = new SqlCommand(sql))
{
cmd.Parameters.Add("@id", SqlDbType.Int).Value = base.ID;
using (SqlDataReader dr = DBSettings.ExecuteReader(this.GetType().Name, cmd))
{
if (dr.Read())
{
InitializeProperties();
_strName = dr["employername"].ToString();
_strStreet1 = dr["street1"].ToString();
_strStreet2 = dr["street2"].ToString();
_strCity = dr["city"].ToString();
_intStateId = (dr.IsDBNull(dr.GetOrdinal("stateid"))) ? -1 : Convert.ToInt32(dr["stateid"].ToString());
_strZip = dr["zip"].ToString();
_strPhone = dr["phone"].ToString();
base.SetNewUpdateProperties(dr["updatedby"].ToString(), (DateTime)dr["updateddate"]);
}
else
{
dr.Close();
throw new MyException(this.GetType().Name, MyErrorType.ConcurrencyObjectNotExists, new Exception());
}
dr.Close();
}
}
}
}
protected void Insert(SqlCommand cmd, DateTime updateDate)
{
cmd.CommandText += "SET @ident = SCOPE_IDENTITY(); ";
cmd.Parameters.Add("@updatedby", SqlDbType.VarChar).Value = LocalUser.WindowsUserName;
cmd.Parameters.Add("@updateddate", SqlDbType.DateTime).Value = updateDate;
SqlParameter paramId = new SqlParameter("@ident", SqlDbType.Int);
paramId.Direction = ParameterDirection.Output;
cmd.Parameters.Add(paramId);
if (DBSettings.ExecuteNonQuery(this.GetType().Name, cmd) > 0)
{
if (!DBNull.Value.Equals(paramId.Value))
{
_intID = (int)paramId.Value;
}
}
else
{
throw new ArgumentNullException(this.GetType().Name + "Id", "The insert failed to return a unique identifier for the " + this.GetType().Name);
}
}
