public static List<Case> GetCases()
{
List<Case> results = new List<Case>();
string sql = "SELECT * FROM RJL_Case";
using (SqlConnection conn = new SqlConnection(Constants.DSN))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.CommandType = CommandType.Text;
SqlDataReader read = cmd.ExecuteReader();
while (read.Read())
{
Case newCase = new Case()
{
CaseID = Convert.ToInt32(read["Case_ID"]),
CourtID = default(int),
ReferralDate = read["Referral_Date"] as DateTime? ?? default(DateTime),
ReferralNumber = Convert.ToInt32(read["Referral_Number"]),
CourtDate = read["Court_Date"] as DateTime? ?? default(DateTime),
DateOfFinalConference = read["Final_Conference_Date"] as DateTime? ?? default(DateTime),
DateOfCompletion = read["Closure_Date"] as DateTime? ?? default(DateTime),
Status = read["Status"].ToString(),
District = Convert.ToInt32(read["District"])
};
newCase.GetOffenders();
newCase.GetVictims();
newCase.GetAffiliates();
newCase.GetNotes();
newCase.GetCharges();
newCase.GetDocuments();
newCase.GetCaseManagers();
results.Add(newCase);
}
}
return results;
}
public static List<Case> GetCases(bool getBasics, int personID, string status)
{
List<Case> results = new List<Case>();
string sql = @"
SELECT *
FROM RJL_Case c
INNER JOIN Case_Manager cm ON c.Case_ID = cm.Case_ID
WHERE Person_ID = @PersonID
AND Status LIKE @Status";
using (SqlConnection conn = new SqlConnection(Constants.DSN))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("Status", "%" + status + "%");
cmd.Parameters.AddWithValue("PersonID", personID);
SqlDataReader read = cmd.ExecuteReader();
while (read.Read())
{
Case newCase = new Case()
{
CaseID = Convert.ToInt32(read["Case_ID"]),
CourtID = default(int),
ReferralDate = read["Referral_Date"] as DateTime? ?? default(DateTime),
ReferralNumber = Convert.ToInt32(read["Referral_Number"]),
CourtDate = read["Court_Date"] as DateTime? ?? default(DateTime),
DateOfFinalConference = read["Final_Conference_Date"] as DateTime? ?? default(DateTime),
DateOfCompletion = read["Closure_Date"] as DateTime? ?? default(DateTime),
Status = read["Status"].ToString()
};
newCase.GetOffenders();
newCase.GetVictims();
newCase.GetAffiliates();
newCase.GetNotes();
newCase.GetCharges();
newCase.GetDocuments();
newCase.GetCaseManagers();
results.Add(newCase);
}
}
return results;
}
public static Case Get(int caseID)
{
string sql = "SELECT * FROM RJL_Case WHERE Case_ID = @CaseID";
using (SqlConnection conn = new SqlConnection(Constants.DSN))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("CaseID", caseID);
SqlDataReader read = cmd.ExecuteReader();
if (read.Read())
{
Case result = new Case()
{
CaseID = Convert.ToInt32(read["Case_ID"]),
CourtID = Convert.ToInt32(read["Court_ID"]),
ReferralDate = read["Referral_Date"] as DateTime? ?? default(DateTime),
ReferralNumber = Convert.ToInt32(read["Referral_Number"]),
CourtDate = read["Court_Date"] as DateTime? ?? default(DateTime),
DateOfFinalConference = read["Final_Conference_Date"] as DateTime? ?? default(DateTime),
DateOfCompletion = read["Closure_Date"] as DateTime? ?? default(DateTime),
Status = read["Status"].ToString(),
District = Convert.ToInt32(read["District"])
};
result.GetOffenders();
result.GetVictims();
result.GetAffiliates();
result.GetNotes();
result.GetCharges();
result.GetDocuments();
result.GetCaseManagers();
return result;
}
}
return null;
}