protected void Page_Load(object sender, EventArgs e)
{
// First control the type of parent page
if (Request.QueryString["HcId"] != null)
{
hcId = Int32.Parse(Request.QueryString["HcId"]);
hc = CntAriCli.GetHealthCompany(ctx);
caller = "hccom"; // Called by a Healthcare Company
}
if (Request.QueryString["ClinicId"] != null)
{
clinicId = Int32.Parse(Request.QueryString["ClinicId"]);
cl = CntAriCli.GetClinic(clinicId, ctx);
caller = "clinic"; // Called by a Healthcare Company
}
if (Request.QueryString["PatientId"] != null)
{
patientId = Int32.Parse(Request.QueryString["PatientId"]);
pat = CntAriCli.GetPatient(patientId, ctx);
caller = "patient";
}
if (Request.QueryString["CustomerId"] != null)
{
customerId = Int32.Parse(Request.QueryString["CustomerId"]);
cus = CntAriCli.GetCustomer(customerId, ctx);
caller = "customer";
}
if (Request.QueryString["ProfessionalId"] != null)
{
professionalId = Int32.Parse(Request.QueryString["ProfessionalId"]);
prof = CntAriCli.GetProfessional(professionalId, ctx);
caller = "professional";
}
}
/// <summary>
/// Traspasa los datos de los pacientes desde OFT a AriClinic
/// </summary>
/// <param name="con"> Connector a OFT</param>
/// <param name="ctx"> Contexto de AriClinic</param>
public static void ImportPatientCustomer(OleDbConnection con, AriClinicContext ctx)
{
// (1) Eliminar los datos que pudiera haber previamente y que serán
// sustituidos por los nuevos.
// DeletePatientRelated(ctx);
/* ACL-176
* Ahora solo pretendem,os importar direcciones, correos y telfonos
* suponemos que personas, pacientes y clientes son correctos
* y que los pacientes están corretamente ligados a clientes
*
* */
DeleteEmailsAddressAndTelephones(ctx);
// (1.1) Montar las procedencias
Console.WriteLine("Importing sources...");
ImportSources(con, ctx);
// (2) Lleer todos los pacientes en OFT
string sql = "SELECT * FROM Historiales";
cmd = new OleDbCommand(sql, con);
da = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds, "ConHistoriales");
int nreg = ds.Tables["ConHistoriales"].Rows.Count;
int reg = 0;
// (2.0) Por cada fila lieda de la tabla, damos de alta el
// paciente correspondiente con sus direcciones, teléfonosç
// y correo electrónico.
foreach (DataRow dr in ds.Tables["ConHistoriales"].Rows)
{
++reg; // un registro más (para saber por donde va)
Console.WriteLine("registro {1:#####0} de {2:#####0} / {0}", (string)dr["Nombre"], reg, nreg);
// (2.1) Crear cliente
// ACL-176 nos aseguramos que al menos el cliente existe
Customer customer = CntAriCli.GetCustomerByOftId((int)dr["NumHis"], ctx);
if (customer == null)
{
customer = new Customer();
customer.OftId = (int)dr["NumHis"];
ctx.Add(customer);
}
if (dr["NumDni"] != DBNull.Value)
customer.VATIN = (string)dr["NumDni"];
customer.FullName = (string)dr["Nombre"];
customer.ComercialName = (string)dr["Nombre"];
ctx.SaveChanges();
// (2.2) Crear paciente y asignarle el cliente
Patient patient = CntAriCli.GetPatientByOftId((int)dr["NumHis"], ctx);
if (patient == null)
{
patient = new Patient();
patient.OftId = (int)dr["NumHis"];
ctx.Add(patient);
}
patient.Name = (string)dr["Nom"];
patient.Surname1 = (string)dr["Apell1"];
if (dr["Apell2"] != DBNull.Value)
patient.Surname2 = (string)dr["Apell2"];
patient.FullName = (string)dr["Nombre"];
if (dr["FechaNac"] != DBNull.Value)
patient.BornDate = (DateTime)dr["FechaNac"];
patient.Sex = "M";
if (dr["Sexo"] != DBNull.Value)
if ((byte)dr["Sexo"] == 2)
patient.Sex = "W";
// ACL-176 machacamos la asociación
patient.Customer = customer;
// asignar la procedencia
// ACL-176 la procedencia preexiste
Source src = (from s in ctx.Sources
where s.OftId == (int)dr["IdProcMed"]
select s).FirstOrDefault<Source>();
if (src != null)
patient.Source = src;
// asignar la fecha de apertura
if (dr["FecAper"] != DBNull.Value)
patient.OpenDate = (DateTime)dr["FecAper"];
ctx.SaveChanges();
// ACL-176 innecesario por el borre inicial
//// eliminar los datos asociados
//ctx.Delete(customer.Addresses);
//ctx.Delete(patient.Addresses);
//ctx.SaveChanges();
// (2.3) Crear la dirección y asignársela a paciente y cliente.
Address address = new Address();
address.Street = (string)dr["Direccion"];
address.City = (string)dr["Poblacion"];
if (dr["CodPostal"] != DBNull.Value)
address.PostCode = (string)dr["CodPostal"];
address.Province = (string)dr["Provincia"];
address.Type = "Primary";
// ACL-176 ¿Qué persona es el paciente?
address.Person = CntAriCli.GetPersonByPatient(patient, ctx);
ctx.Add(address);
ctx.SaveChanges();
// ACL-176 pacientes y clientes tienen duplicados sus registros
address = new Address();
address.Street = (string)dr["Direccion"];
address.City = (string)dr["Poblacion"];
if (dr["CodPostal"] != DBNull.Value)
address.PostCode = (string)dr["CodPostal"];
address.Province = (string)dr["Provincia"];
address.Type = "Primary";
// ACL-176 ¿Qué persona es el paciente?
address.Person = CntAriCli.GetPersonByCustomer(customer, ctx);
ctx.Add(address);
ctx.SaveChanges();
// ACL-176 el borre inicial lo vuelve innecesario
//// eliminar los teléfonos asociados
//ctx.Delete(customer.Telephones);
//ctx.Delete(patient.Telephones);
//ctx.SaveChanges();
// (2.4) Lo mismo para los teléfono.
Telephone telephone = new Telephone();
if (dr["Tel1"] != DBNull.Value)
{
telephone.Number = (string)dr["Tel1"];
if (telephone.Number != "")
{
telephone.Type = "Primary";
// ACL-176 turno del paciente
telephone.Person = CntAriCli.GetPersonByPatient(patient, ctx);
ctx.Add(telephone);
ctx.SaveChanges();
}
}
if (dr["Tel2"] != DBNull.Value)
{
telephone = new Telephone();
telephone.Number = (string)dr["Tel2"];
if (telephone.Number != "")
{
telephone.Type = "Primary";
// ACL-176 turno del paciente
telephone.Person = CntAriCli.GetPersonByPatient(patient, ctx);
ctx.Add(telephone);
ctx.SaveChanges();
}
}
if (dr["Movil"] != DBNull.Value)
{
telephone = new Telephone();
telephone.Number = (string)dr["Movil"];
if (telephone.Number != "")
{
telephone.Type = "Secondary";
// ACL-176 turno del paciente
telephone.Person = CntAriCli.GetPersonByPatient(patient, ctx);
ctx.Add(telephone);
ctx.SaveChanges();
}
}
//ACL-176 Es un rollo pero hay que repetir todo el proceso para el cliente
telephone = new Telephone();
if (dr["Tel1"] != DBNull.Value)
{
telephone.Number = (string)dr["Tel1"];
if (telephone.Number != "")
{
telephone.Type = "Primary";
// ACL-176 turno del paciente
telephone.Person = CntAriCli.GetPersonByCustomer(customer, ctx);
ctx.Add(telephone);
ctx.SaveChanges();
}
}
if (dr["Tel2"] != DBNull.Value)
{
telephone = new Telephone();
telephone.Number = (string)dr["Tel2"];
if (telephone.Number != "")
{
telephone.Type = "Primary";
// ACL-176 turno del paciente
telephone.Person = CntAriCli.GetPersonByCustomer(customer, ctx);
ctx.Add(telephone);
ctx.SaveChanges();
}
}
if (dr["Movil"] != DBNull.Value)
{
telephone = new Telephone();
telephone.Number = (string)dr["Movil"];
if (telephone.Number != "")
{
telephone.Type = "Secondary";
// ACL-176 turno del paciente
telephone.Person = CntAriCli.GetPersonByCustomer(customer, ctx);
ctx.Add(telephone);
ctx.SaveChanges();
}
}
// ACL-176 innecasrio por borre total
//// eliminar los correos anteriores
//ctx.Delete(customer.Emails);
//ctx.Delete(patient.Emails);
//ctx.SaveChanges();
// (2.5) Igual pero para correos electrónicos
Email email = new Email();
if (dr["Email"] != DBNull.Value)
email.Url = (string)dr["Email"];
if (email.Url != "0" && email.Url != "")
{
email.Type = "Primary";
//ACL-176 turno de paciente
email.Person = CntAriCli.GetPersonByPatient(patient, ctx);
ctx.Add(email);
ctx.SaveChanges();
}
// ACL-176 ahora el cliente.
email = new Email();
if (dr["Email"] != DBNull.Value)
email.Url = (string)dr["Email"];
if (email.Url != "0" && email.Url != "")
{
email.Type = "Primary";
//ACL-176 turno de paciente
email.Person = CntAriCli.GetPersonByCustomer(customer, ctx);
ctx.Add(email);
ctx.SaveChanges();
}
}
}
/// <summary>
/// Traspasa los datos de los pacientes desde OFT a AriClinic
/// </summary>
/// <param name="con"> Connector a OFT</param>
/// <param name="ctx"> Contexto de AriClinic</param>
public static void ImportPatientCustomer(OleDbConnection con, AriClinicContext ctx)
{
// (1) Eliminar los datos que pudiera haber previamente y que serán
// sustituidos por los nuevos.
ctx.Delete(ctx.Addresses); // eliminar direcciones.
ctx.Delete(ctx.Emails); // eliminar correos electrónicos
ctx.Delete(ctx.Telephones); // eliminar teléfonos.
ctx.Delete(ctx.Policies); // eliminar las pólizas.
ctx.Delete(ctx.Customers); // eliminar los clientes.
ctx.Delete(ctx.Patients); // por último, los pacientes.
ctx.SaveChanges();
// (2) Lleer todos los pacientes en OFT
string sql = "SELECT * FROM Historiales";
cmd = new OleDbCommand(sql, con);
da = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds, "ConHistoriales");
int nreg = ds.Tables["ConHistoriales"].Rows.Count;
int reg = 0;
// (2.0) Por cada fila lieda de la tabla, damos de alta el
// paciente correspondiente con sus direcciones, teléfonosç
// y correo electrónico.
foreach (DataRow dr in ds.Tables["ConHistoriales"].Rows)
{
++reg; // un registro más (para saber por donde va)
// (2.1) Crear cliente
Customer customer = new Customer();
if (dr["NumDni"] != DBNull.Value)
customer.VATIN = (string)dr["NumDni"];
customer.FullName = (string)dr["Nombre"];
customer.ComercialName = (string)dr["Nombre"];
customer.OftId = (int)dr["NumHis"];
ctx.Add(customer);
// (2.2) Crear paciente y asignarle el cliente
Patient patient = new Patient();
patient.Name = (string)dr["Nom"];
patient.Surname1 = (string)dr["Apell1"];
if (dr["Apell2"] != DBNull.Value)
patient.Surname2 = (string)dr["Apell2"];
patient.FullName = (string)dr["Nombre"];
if (dr["FechaNac"] != DBNull.Value)
patient.BornDate = (DateTime)dr["FechaNac"];
patient.Sex = "M";
if (dr["Sexo"] != DBNull.Value)
if ((byte)dr["Sexo"] == 2)
patient.Sex = "W";
patient.Customer = customer;
patient.OftId = (int)dr["NumHis"];
ctx.Add(patient);
// (2.3) Crear la dirección y asignársela a cliente y paciente.
Address address = new Address();
address.Street = (string)dr["Direccion"];
address.City = (string)dr["Poblacion"];
if (dr["CodPostal"] != DBNull.Value)
address.PostCode = (string)dr["CodPostal"];
address.Province = (string)dr["Provincia"];
address.Type = "Primary";
ctx.Add(address);
customer.Addresses.Add(address);
patient.Addresses.Add(address);
// (2.4) Lo mismo para los teléfono.
Telephone telephone = new Telephone();
if (dr["Tel1"] != DBNull.Value)
{
telephone.Number = (string)dr["Tel1"];
telephone.Type = "Primary";
ctx.Add(telephone);
patient.Telephones.Add(telephone);
customer.Telephones.Add(telephone);
}
if (dr["Tel2"] != DBNull.Value)
{
telephone = new Telephone();
telephone.Number = (string)dr["Tel2"];
telephone.Type = "Primary";
ctx.Add(telephone);
patient.Telephones.Add(telephone);
customer.Telephones.Add(telephone);
}
if (dr["Movil"] != DBNull.Value)
{
telephone = new Telephone();
telephone.Number = (string)dr["Movil"];
telephone.Type = "Secondary";
ctx.Add(telephone);
patient.Telephones.Add(telephone);
customer.Telephones.Add(telephone);
}
// (2.5) Igual pero para correos electrónicos
Email email = new Email();
if (dr["Email"] != DBNull.Value)
email.Url = (string)dr["Email"];
email.Type = "Primary";
ctx.Add(email);
patient.Emails.Add(email);
customer.Emails.Add(email);
}
ctx.SaveChanges();
}
public static void UpdateCustomerRelatedData(Patient pat, AriClinicContext ctx)
{
Customer cus = pat.Customer;
// update comercial name
cus.ComercialName = pat.FullName;
cus.FullName = cus.ComercialName;
// update related address
// primary address exists
Address adr = (from a in cus.Addresses
where a.Type == "Primary"
select a).FirstOrDefault<Address>();
if (adr == null)
{
// we assign the address of patient as customer address
Address adrpat = (from a in pat.Addresses
where a.Type == "Primary"
select a).FirstOrDefault<Address>();
if (adrpat != null)
{
adr = new Address();
adr.Street = adrpat.Street;
adr.City = adrpat.City;
adr.PostCode = adrpat.PostCode;
adr.Street2 = adrpat.Street2;
adr.Province = adrpat.Province;
adr.Type = adrpat.Type;
adr.Country = adrpat.Country;
adr.Person = cus;
cus.Addresses.Add(adr);
}
}
}
public static IList<Policy> GetPolicies(Patient patient, AriClinicContext ctx)
{
return patient.Customer.Policies.ToList<Policy>();
}
public static Policy GetPolicyInForce(Patient patient, DateTime dt, AriClinicContext ctx)
{
Policy policy = null;
policy = (from p in patient.Customer.Policies
where p.BeginDate <= dt && p.EndDate >= dt
select p).FirstOrDefault();
return policy;
}
public static void DeletePatient(Patient patient, AriClinicContext ctx)
{
// delete associated customer
ctx.Delete(patient.Customer.Addresses);
ctx.Delete(patient.Customer.Emails);
ctx.Delete(patient.Customer.Telephones);
ctx.Delete(patient.Customer);
// deleting patient
ctx.Delete(patient.Addresses);
ctx.Delete(patient.Emails);
ctx.Delete(patient.Telephones);
ctx.Delete(patient);
// do it!
ctx.SaveChanges();
}
public static void CheckPolicy(Patient patient, AriClinicContext ctx)
{
// Does this customer have a policiy yet?
if (patient.Customer.Policies.Count > 0)
return;
// He hasn't. Is there only one insurance company in db?.
if (ctx.Insurances.Count() != 1)
return;
Insurance insurance = ctx.Insurances.First<Insurance>();
// There's only one insurance company and we create a policy related to it.
Policy policy = new Policy();
policy.Insurance = insurance;
policy.Customer = patient.Customer;
ctx.Add(policy);
ctx.SaveChanges();
}
public static string GetPrimaryPhone(Patient p, AriClinicContext ctx)
{
string telNumber = "";
Telephone tel = (from t in ctx.Telephones
where t.Person.PersonId == p.PersonId
&& t.Type == "Primary"
select t).FirstOrDefault<Telephone>();
if (tel != null) telNumber = tel.Number;
return telNumber;
}
public static void SetRequestAssociation(Patient p, IList<Request> lr, AriClinicContext ctx){
foreach (Request req in lr)
{
Request r = CntAriCli.GetRequest(req.RequestId, ctx);
if (r != null)
{
r.Patient = CntAriCli.GetPatient(p.PersonId, ctx);
r.Surname1 = "";
r.Surname2 = "";
r.Name = "";
r.FullName = "";
r.Sex = "";
ctx.SaveChanges();
if (r.BornDate != null && r.BornDate != new DateTime())
{
p.BornDate = r.BornDate;
}
if (r.Dni != "")
{
p.Customer.VATIN = r.Dni;
r.Dni = "";
}
if (p.Addresses.Count == 0)
{
Address a = new Address();
a.PostCode = r.PostalCode;
p.Addresses.Add(a);
r.PostalCode = "";
}
if (p.Emails.Count == 0)
{
Email e = new Email();
e.Url = r.Email;
p.Emails.Add(e);
r.Email = "";
}
if (p.Telephones.Count == 0)
{
Telephone t = new Telephone();
t.Number = r.Telephone;
p.Telephones.Add(t);
r.Telephone = "";
}
ctx.SaveChanges();
}
}
}
public static IList<Request> GetPossibleAssociateRequest(Patient p, AriClinicContext ctx)
{
IList<Request> lr = new List<Request>();
// dni match before other attempts
var rs = from r in ctx.Requests
where r.Patient == null
&& r.Dni != ""
&& r.Dni == p.Customer.VATIN
select r;
if (rs.Count() > 0)
{
foreach (Request rq in rs)
lr.Add(rq);
return lr;
}
// first attempt
// Name, Surname1 and Surname2 matches
rs = from r in ctx.Requests
where r.Patient == null
&& r.FullName == p.FullName
select r;
if (rs.Count() > 0)
{
foreach (Request rq in rs)
lr.Add(rq);
return lr;
}
// second attempt
// telephone
foreach (Telephone t in p.Telephones)
{
rs = from r in ctx.Requests
where r.Patient == null &&
r.Telephone == t.Number
select r;
if (rs.Count() > 0)
{
foreach (Request rq in rs)
lr.Add(rq);
return lr;
}
}
// third attempt
// email
foreach (Email e in p.Emails)
{
rs = from r in ctx.Requests
where r.Patient == null &&
r.Email == e.Url
select r;
if (rs.Count() > 0)
{
foreach (Request rq in rs)
lr.Add(rq);
return lr;
}
}
return lr;
}
public static IList<Request> GetRequestsByStatus(string status, Patient patient, AriClinicContext ctx)
{
return (from c in ctx.Requests
where c.Patient.PersonId == patient.PersonId
&& c.Status == status
orderby c.RequestDateTime descending
select c).ToList<Request>();
}
public static Person GetPersonByPatient(Patient patient, AriClinicContext ctx)
{
return (from p in ctx.People
where p.PersonId == patient.PersonId
select p).FirstOrDefault<Person>();
}
public static string GetInsuranceInformation(Patient patient, AriClinicContext ctx)
{
string res = "";
Policy pol = GetPolicyInForce(patient, DateTime.Now, ctx);
if (pol == null)
{
// there's not policy in force
pol = (from p in patient.Customer.Policies
orderby p.EndDate descending
select p).FirstOrDefault<Policy>();
}
if (pol != null)
{
res = String.Format("{0} [{1}] {2:dd/MM/yyyy} - {3:dd/MM/yyyy}", pol.Insurance.Name, pol.PolicyNumber, pol.BeginDate, pol.EndDate);
}
return res;
}
/// <summary>
/// Traspasa los datos de los pacientes desde OFT a AriClinic
/// </summary>
/// <param name="con"> Connector a OFT</param>
/// <param name="ctx"> Contexto de AriClinic</param>
public static void ImportPatientCustomer(OleDbConnection con, AriClinicContext ctx)
{
Console.WriteLine("---Importando Historiales---");
// (1) Eliminar los datos que pudiera haber previamente y que serán
// sustituidos por los nuevos.
ctx.Delete(ctx.Addresses); // elimar direcciones.
ctx.Delete(ctx.Emails); // eliminar correos electrónicos
ctx.Delete(ctx.Telephones); // eliminar teléfonos.
ctx.Delete(ctx.Customers); // eliminar los clientes.
ctx.Delete(ctx.Patients); // por último, los pcaientes.
ctx.SaveChanges();
// (2) Lleer todos los pacientes en OFT
string sql = "SELECT * FROM Historiales";
cmd = new OleDbCommand(sql, con);
da = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds, "ConHistoriales");
int nreg = ds.Tables["ConHistoriales"].Rows.Count;
int reg = 0;
foreach (DataRow dr in ds.Tables["ConFacturas"].Rows)
{
Console.WriteLine("[{0} de {1}] {2}", ++reg, nreg, dr["Nombre"]);
// (2.1) Crear cliente
Customer customer = new Customer();
customer.VATIN = (string)dr["NumDni"];
customer.FullName = (string)dr["Nombre"];
customer.ComercialName = (string)dr["Nombre"];
ctx.Add(customer);
// (2.2) Crear paciente y asignarle el cliente
Patient patient = new Patient();
patient.Name = (string)dr["Nom"];
patient.Surname1 = (string)dr["Apell1"];
patient.Surname2 = (string)dr["Apell2"];
patient.FullName = (string)dr["Nombre"];
patient.BornDate = (DateTime)dr["FechaNac"];
patient.Customer = customer;
ctx.Add(patient);
// (2.3) Crear la dirección y asignársela a cliente y paciente.
Address address = new Address();
address.Street = (string)dr["Direccion"];
address.City = (string)dr["Poblacion"];
address.PostCode = (string)dr["CodPostal"];
address.Province = (string)dr["Provincia"];
address.Type = "Primary";
ctx.Add(address);
customer.Addresses.Add(address);
patient.Addresses.Add(address);
// (2.4) Lo mismo para los teléfono.
Telephone telephone = new Telephone();
if ((string)dr["Tel1"] != "")
{
telephone.Number = (string)dr["Tel1"];
telephone.Type = "Primary";
ctx.Add(telephone);
patient.Telephones.Add(telephone);
customer.Telephones.Add(telephone);
}
if ((string)dr["Tel2"] != "")
{
telephone = new Telephone();
telephone.Number = (string)dr["Tel2"];
telephone.Type = "Primary";
ctx.Add(telephone);
patient.Telephones.Add(telephone);
customer.Telephones.Add(telephone);
}
if ((string)dr["Movil"] != "")
{
telephone = new Telephone();
telephone.Number = (string)dr["Movil"];
telephone.Type = "Secondary";
ctx.Add(telephone);
patient.Telephones.Add(telephone);
customer.Telephones.Add(telephone);
}
// (2.5) Igual pero para correos electrónicos
Email email = new Email();
email.Url = (string)dr["Email"];
email.Type = "Primary";
ctx.Add(email);
patient.Emails.Add(email);
customer.Emails.Add(email);
}
}
public static String GetInsuranceData(Patient patient, AriClinicContext ctx)
{
string insuranceData = "";
int policyInForceId = 0;
// policy in force
Policy policy = CntAriCli.GetPolicyInForce(patient, DateTime.Now, ctx);
if (policy == null)
insuranceData = "[*]";
else
{
insuranceData = string.Format("[ IG:{0} PN:{1} ]", policy.Insurance.Name, policy.PolicyNumber);
policyInForceId = policy.PolicyId;
}
// policies but in force
foreach (Policy plcy in CntAriCli.GetPolicies(patient, ctx))
{
if (plcy != null && (plcy.PolicyId != policyInForceId))
{
insuranceData = string.Format("{0} / IG:{1} PN:{2} /",
insuranceData, plcy.Insurance.Name, plcy.PolicyNumber);
}
}
return insuranceData;
}
public static IList<DiagnosticAssigned> GetDiagnosticsAssigned(Patient patient, AriClinicContext ctx)
{
return (from da in ctx.DiagnosticAssigneds
orderby da.DiagnosticDate descending
where da.Patient.PersonId == patient.PersonId
select da).ToList<DiagnosticAssigned>();
}
