public Escorted GetEscortById(int id)
{
string query = "exec spEscorted_GetEscortById @id=" + id;
try
{
dbs = new DbService();
SqlDataReader sdr = dbs.GetDataReader(query);
Escorted escort = new Escorted();
if (sdr.Read())
{
escort.Id = int.Parse(sdr["Id"].ToString());
escort.DisplayName = sdr["DisplayName"].ToString();
escort.FirstNameH = sdr["FirstNameH"].ToString();
escort.LastNameH = sdr["LastNameH"].ToString();
escort.CellPhone = sdr["CellPhone"].ToString();
escort.IsAnonymous = String.IsNullOrEmpty(sdr["IsAnonymous"].ToString());
escort.IsActive = String.IsNullOrEmpty(sdr["IsActive"].ToString());
}
return(escort);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
dbs.CloseConnection();
}
}
public void deactivateEscorted(string displayName, string active)
{
Escorted c = new Escorted();
c.DisplayName = displayName;
c.deactivateEscorted(active);
}
public void setEscorted(Escorted escorted, string func)
{
Escorted p = new Escorted();
p = escorted;
p.setEscorted(func);
}
public List <Escorted> getescortedsList(string displayName)
{
#region DB functions
string query = "select * from Escorted e where patient='" + displayName + "' or patient1='" + displayName + "'";
query += " order by firstNameH";
List <Escorted> list = new List <Escorted>();
DbService db = new DbService();
DataSet ds = db.GetDataSetByQuery(query);
foreach (DataRow dr in ds.Tables[0].Rows)
{
Escorted e = new Escorted();
e.Pat = new Patient(dr["patient"].ToString());
e.DisplayName = dr["displayName"].ToString();
e.FirstNameA = dr["firstNameA"].ToString();
e.FirstNameH = dr["firstNameH"].ToString();
e.LastNameH = dr["lastNameH"].ToString();
e.LastNameA = dr["lastNameA"].ToString();
e.CellPhone = dr["cellPhone"].ToString();
e.CellPhone2 = dr["cellPhone2"].ToString();
e.HomePhone = dr["homePhone"].ToString();
e.Status = dr["statusEscorted"].ToString();
e.ContactType = dr["contactType"].ToString();
e.Gender = dr["gender"].ToString();
e.Addrees = dr["city"].ToString();
list.Add(e);
}
#endregion
return(list);
}
public Escorted getEscorted()
{
#region DB functions
string query = "select id,patient,displayName, firstNameH,firstNameA, lastNameH,lastNameA, cellPhone,cellPhone2,homePhone,city,statusEscorted, contactType,gender from Escorted where displayName ='" + displayName + "'";
Escorted p = new Escorted();
DbService db = new DbService();
DataSet ds = db.GetDataSetByQuery(query);
foreach (DataRow dr in ds.Tables[0].Rows)
{
p.Id = int.Parse(dr["id"].ToString());
p.Pat = new Patient(dr["patient"].ToString());
p.DisplayName = dr["displayName"].ToString();
p.FirstNameA = dr["firstNameA"].ToString();
p.FirstNameH = dr["firstNameH"].ToString();
p.LastNameH = dr["lastNameH"].ToString();
p.LastNameA = dr["lastNameA"].ToString();
p.CellPhone = dr["cellPhone"].ToString();
p.CellPhone2 = dr["cellPhone2"].ToString();
p.HomePhone = dr["homePhone"].ToString();
p.Addrees = dr["city"].ToString();
p.Status = dr["statusEscorted"].ToString();
p.ContactType = dr["contactType"].ToString();
p.Gender = dr["gender"].ToString();
}
#endregion
return(p);
}
public string getEscorted(string displayName)
{
JavaScriptSerializer j = new JavaScriptSerializer();
Escorted p = new Escorted();
p.DisplayName = displayName;
Escorted escorted = p.getEscorted();
return(j.Serialize(escorted));
}
public Escorted getEscorted(string patientName)
{
#region DB functions
SqlCommand cmd;
SqlParameter[] cmdParams;
// displayName = displayName.Replace("'", "''");
string query = "select * from Escorted where DisplayName =@displayName"; //id,patient,displayName, firstNameH,firstNameA, lastNameH,lastNameA, cellPhone,cellPhone2,homePhone,city,statusEscorted, contactType,gender
Escorted e = new Escorted();
DbService db = new DbService();
cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmdParams = new SqlParameter[1];
cmdParams[0] = cmd.Parameters.AddWithValue("@displayName", displayName);
DataSet ds = db.GetDataSetByQuery(query, true, cmd.CommandType, cmdParams);
foreach (DataRow dr in ds.Tables[0].Rows)
{
e.Id = int.Parse(dr["Id"].ToString());
//p.Pat = new Patient(dr["patient"].ToString());
e.DisplayName = dr["DisplayName"].ToString();
e.FirstNameA = dr["FirstNameA"].ToString();
e.FirstNameH = dr["FirstNameH"].ToString();
e.LastNameH = dr["LastNameH"].ToString();
e.LastNameA = dr["LastNameA"].ToString();
e.CellPhone = dr["CellPhone"].ToString();
e.CellPhone2 = dr["CellPhone2"].ToString();
e.HomePhone = dr["HomePhone"].ToString();
e.City = dr["City"].ToString();
//p.Status = dr["statusEscorted"].ToString();
e.IsActive = Convert.ToBoolean(dr["IsActive"].ToString());
// p.ContactType = dr["contactType"].ToString();
e.Gender = dr["Gender"].ToString();
e.EnglishName = dr["EnglishName"].ToString();
}
db = new DbService();
cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmdParams = new SqlParameter[2];
cmdParams[0] = cmd.Parameters.AddWithValue("@patient", patientName);
cmdParams[1] = cmd.Parameters.AddWithValue("@escort", e.DisplayName);
query = "select Relationship from RelationshipView where Patient=@patient and Escort=@escort";
DataRow dr2 = db.GetDataSetByQuery(query, true, cmd.CommandType, cmdParams).Tables[0].Rows[0];
e.ContactType = dr2["Relationship"].ToString();
#endregion
return(e);
}
public List <Escorted> getContactType()
{
List <Escorted> cl = new List <Escorted>();
string query = "select Name,EnglishName from ContactType";
DbService db = new DbService();
DataSet ds = db.GetDataSetByQuery(query);
foreach (DataRow dr in ds.Tables[0].Rows)
{
Escorted e = new Escorted();
e.DisplayName = dr["Name"].ToString();
e.EnglishName = dr["EnglishName"].ToString();
cl.Add(e);
}
return(cl);
}
public RidePat(Patient _pat, Escorted _escorted1, Destination _startPlace, Destination _target,
string _day, string _date, string _leavingHour, int _quantity, string _addition, string _rideType,
Volunteer _coordinator, string _remark, string _status)
{
Pat = _pat;
Escorted1 = _escorted1;
StartPlace = _startPlace;
Target = _target;
Day = _day;
Date = _date;
LeavingHour = _leavingHour;
Quantity = _quantity;
Addition = _addition;
RideType = _rideType;
Coordinator = _coordinator;
Remark = _remark;
Status = _status;
}
//By Sufa & Matan --Get a list of all Escorts for Patient.
public List <Escorted> getescortedsList(string displayName, string caller)
{
#region DB functions
displayName = displayName.Replace("'", "''");
string query = "select * from PatientEscortView where PatientName= N'" + displayName + "'";
if (caller == "ridePatForm")
{
query += " and IsActive = 'True'";
}
//query += " order by EscortName";
List <Escorted> list = new List <Escorted>();
DbService db = new DbService();
DataSet ds = db.GetDataSetByQuery(query);
foreach (DataRow dr in ds.Tables[0].Rows)
{
Escorted e = new Escorted();
// e.Pat = new Patient(dr["PatientName"].ToString()); //new Patient(dr["patient"].ToString());
e.Id = int.Parse(dr["EscortId"].ToString());
e.DisplayName = dr["EscortName"].ToString();
e.FirstNameA = dr["FirstNameA"].ToString();
e.FirstNameH = dr["FirstNameH"].ToString();
e.LastNameH = dr["LastNameH"].ToString();
e.LastNameA = dr["LastNameA"].ToString();
e.CellPhone = dr["CellPhone"].ToString();
e.CellPhone2 = dr["CellPhone2"].ToString();
e.HomePhone = dr["HomePhone"].ToString();
e.IsActive = Convert.ToBoolean(dr["IsActive"].ToString());
// e.ContactType = dr["ContactType"].ToString();
e.Gender = dr["Gender"].ToString();
e.City = dr["City"].ToString();
e.ContactType = dr["Relationship"].ToString();
e.EnglishName = dr["EnglishName"].ToString();
list.Add(e);
}
#endregion
return(list);
}
//getAllEquipment() taken from patient, don't need it here.
//new function to set an anonymous patient
public void setAnonymousPatient(string func)
{
int res = 0;
DbService db;
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
SqlParameter[] cmdParams = new SqlParameter[20];
cmdParams[0] = cmd.Parameters.AddWithValue("@firstNameH", FirstNameH);
cmdParams[1] = cmd.Parameters.AddWithValue("@lastNameH", LastNameH);
cmdParams[2] = cmd.Parameters.AddWithValue("@firstNameA", FirstNameA);
cmdParams[3] = cmd.Parameters.AddWithValue("@lastNameA", LastNameA);
cmdParams[4] = cmd.Parameters.AddWithValue("@cellPhone", CellPhone);
cmdParams[5] = cmd.Parameters.AddWithValue("@cellPhone2", CellPhone1);
cmdParams[6] = cmd.Parameters.AddWithValue("@homePhone", HomePhone);
cmdParams[7] = cmd.Parameters.AddWithValue("@city", City);
cmdParams[8] = cmd.Parameters.AddWithValue("@IsActive", IsActive);
cmdParams[9] = cmd.Parameters.AddWithValue("@birthDate", BirthDate);
cmdParams[10] = cmd.Parameters.AddWithValue("@history", History);
cmdParams[11] = cmd.Parameters.AddWithValue("@department", Department);
cmdParams[12] = cmd.Parameters.AddWithValue("@barrier", Barrier.Name);
cmdParams[13] = cmd.Parameters.AddWithValue("@hospital", Hospital.Name);
cmdParams[14] = cmd.Parameters.AddWithValue("@gender", Gender);
cmdParams[15] = cmd.Parameters.AddWithValue("@remarks", Remarks);
cmdParams[16] = cmd.Parameters.AddWithValue("@isAnonymous", IsAnonymous);
cmdParams[17] = cmd.Parameters.AddWithValue("@numberOfEscort", NumberOfEscort);
cmdParams[18] = cmd.Parameters.AddWithValue("@englishName", EnglishName);
string displayName = FirstNameH + " " + LastNameH;
cmdParams[19] = cmd.Parameters.AddWithValue("@displayName", displayName.Trim());
string query = "";
if (func == "edit")
{
query = "UPDATE Patient SET FirstNameH=@firstNameH,FirstNameA=@firstNameA,LastNameH=@lastNameH,";
query += "IsActive=@IsActive,";
query += "Barrier=@barrier,Hospital=@hospital,IsAnonymous=@isAnonymous,NumberOfEscort=@numberOfEscort,EnglishName=@englishName,DisplayName=@displayName Where Id=" + Id;
db = new DbService();
res = db.ExecuteQuery(query, cmd.CommandType, cmdParams);
if (res > 0)
{
query = "delete from Equipment_Patient where PatientId=" + Id;
db = new DbService();
db.ExecuteQuery(query);
}
if (Equipment.Count > 0)
{
foreach (string e in Equipment)
{
SqlParameter[] cmdParams2 = new SqlParameter[2];
cmdParams2[0] = cmd.Parameters.AddWithValue("@equipment", e);
cmdParams2[1] = cmd.Parameters.AddWithValue("@id", Id);
query = "insert into Equipment_Patient (EquipmentId,PatientId) values (@equipment,@id)";
db = new DbService();
db.ExecuteQuery(query, cmd.CommandType, cmdParams2);
}
}
}
else if (func == "new")
{
query = "insert into Patient (FirstNameH,FirstNameA,LastNameH,LastNameA,CellPhone,CellPhone2,";
query += "HomePhone,CityCityName,IsActive,BirthDate,History,Department,Barrier,Hospital,Gender,Remarks,IsAnonymous,NumberOfEscort,EnglishName)";
query += " values (@firstNameH,@firstNameA,@lastNameH,@lastNameA,";
query += "@cellPhone,@cellPhone2,@homePhone,@city,@IsActive,@birthDate,";
query += "@history,@department,@barrier,@hospital,@gender,@remarks,@isAnonymous,@numberOfEscort,@englishName); select SCOPE_IDENTITY()";
db = new DbService();
Id = int.Parse(db.GetObjectScalarByQuery(query, cmd.CommandType, cmdParams).ToString());
if (Equipment.Count > 0 && Id != 0)
{
foreach (string e in Equipment)
{
SqlParameter[] cmdParams2 = new SqlParameter[2];
cmdParams2[0] = cmd.Parameters.AddWithValue("@equipment", e);
cmdParams2[1] = cmd.Parameters.AddWithValue("@id", Id);
query = "insert into Equipment_Patient (EquipmentId,PatientId) values (@equipment,@id)";
db = new DbService();
db.ExecuteQuery(query, cmd.CommandType, cmdParams2);
}
}
Escorted escort = new Escorted();
try
{
escort.setAnonymousEscorted(func, Id, NumberOfEscort);
}
catch (Exception ex)
{
throw new Exception("Problem with setAnonymousEscorted: " + ex.Message);
}
}
}
public List <Ride> GetMyPastRides(int volunteerId)
{
string query = "EXEC GetDriverPastRides @driverId=" + volunteerId;
DbService db = new DbService();
DataSet ds = db.GetDataSetByQuery(query);
DataSet EscortDS = new DataSet();;
Ride r = new Ride();
List <Ride> rl = new List <Ride>();
r.RidePats = new List <RidePat>();
RidePat rp2 = new RidePat();
//List<RidePat> rpl = r.RidePats;
bool RidePatexists;
bool RideExists;
try
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
RideExists = false;
RidePatexists = false;
foreach (Ride ride in rl)
{
if (ride.Id == int.Parse(dr["RideNum"].ToString()))
{
RideExists = true;
RidePatexists = false;
foreach (RidePat ridePat in ride.RidePats)
{
if (ridePat.RidePatNum == int.Parse(dr["RidePatNum"].ToString()))
{
RidePatexists = true;
}
//if (RidePatexists && dr["Escort"].ToString() != "")
//{
// if (EscortDS.Tables[0].Rows.Count > 0)
// {
// foreach (DataRow row in EscortDS.Tables[0].Rows)
// {
// Escorted e = new Escorted();
// e.DisplayName = row["DisplayName"].ToString();
// rp2.Pat.EscortedList.Add(e);
// }
// }
// break;
//}
}
}
}
if (RidePatexists)
{
continue;
}
// rp2 = new RidePat();
// //ride.RidePats = new List<RidePat>();
// rp2.RidePatNum = int.Parse(dr["RidePatNum"].ToString());
// rp2.Pat = new Patient();
// rp2.Pat.DisplayName = dr["DisplayName"].ToString();
// rp2.Pat.CellPhone = dr["CellPhone"].ToString();
// rp2.Pat.Equipment = rp2.Pat.getEquipmentForPatient(rp2.Pat.DisplayName);
// db = new DbService();
// query = "select DisplayName from RidePatEscortView where RidePatNum=" + rp2.RidePatNum;
// EscortDS = db.GetDataSetByQuery(query);
// if (EscortDS.Tables[0].Rows.Count > 0)
// {
// rp2.Pat.EscortedList = new List<Escorted>();
// foreach (DataRow row in EscortDS.Tables[0].Rows)
// {
// Escorted e = new Escorted();
// e.DisplayName = row["DisplayName"].ToString();
// rp2.Pat.EscortedList.Add(e);
// }
// }
// Location origin = new Location();
// origin.Name = dr["Origin"].ToString();
// rp2.Origin = origin;
// Location dest = new Location();
// dest.Name = dr["Destination"].ToString();
// rp2.Destination = dest;
// rp2.Area = dr["Area"].ToString();
// rp2.Shift = dr["Shift"].ToString();
// rp2.Date = Convert.ToDateTime(dr["PickupTime"].ToString());
// //adding anonymous
// rp2.Pat.IsAnonymous = dr["IsAnonymous"].ToString();
// ride.RidePats.Add(rp2);
// }
//}
//if (RideExists) continue;
Ride r2 = new Ride();
if (dr["Maindriver"].ToString() == volunteerId.ToString())
{
r2.DriverType = "Primary";
}
else
{
r2.DriverType = "Secondary";
}
r2.Id = int.Parse(dr["RideNum"].ToString());
query = "select statusStatusName from status_Ride where RideRideNum=" + r2.Id + " order by Timestamp desc";
db = new DbService();
r2.Statuses = new List <string>();
foreach (DataRow status in db.GetDataSetByQuery(query).Tables[0].Rows)
{
r2.Statuses.Add(status.ItemArray[0].ToString());
}
r2.Status = r2.Statuses[0];
db = new DbService();
r2.Status = db.GetObjectScalarByQuery(query).ToString();
RidePat rp = new RidePat();
r2.RidePats = new List <RidePat>();
rp.RidePatNum = int.Parse(dr["RidePatNum"].ToString());
rp.Pat = new Patient();
rp.Pat.DisplayName = dr["DisplayName"].ToString();
rp.Pat.CellPhone = dr["CellPhone"].ToString();
rp.Pat.Equipment = rp.Pat.getEquipmentForPatient(rp.Pat.DisplayName);
rp.Pat.EscortedList = new List <Escorted>();
db = new DbService();
query = "select DisplayName from RidePatEscortView where RidePatNum=" + rp.RidePatNum;
EscortDS = db.GetDataSetByQuery(query);
if (EscortDS.Tables[0].Rows.Count > 0)
{
rp.Pat.EscortedList = new List <Escorted>();
foreach (DataRow row in EscortDS.Tables[0].Rows)
{
Escorted e = new Escorted();
e.DisplayName = row["DisplayName"].ToString();
rp.Pat.EscortedList.Add(e);
}
}
Location origin2 = new Location();
origin2.Name = dr["Origin"].ToString();
rp.Origin = origin2;
Location dest2 = new Location();
dest2.Name = dr["Destination"].ToString();
rp.Destination = dest2;
rp.Area = dr["Area"].ToString();
rp.Shift = dr["Shift"].ToString();
rp.Pat.IsAnonymous = dr["IsAnonymous"].ToString();
rp.Date = Convert.ToDateTime(dr["PickupTime"].ToString());
r2.RidePats.Add(rp);
rl.Add(r2);
}
return(rl);
}
catch (Exception e)
{
throw e;
}
}
