public static void DeleteVisit(AriClinicContext ctx)
{
ctx.Delete(ctx.OphthalmologicVisits);
ctx.Delete(ctx.BaseVisits);
ctx.Delete(ctx.VisitReasons);
ctx.SaveChanges();
}
public static void GeneralPaymentDelete(GeneralPayment gp, AriClinicContext ctx)
{
foreach (Payment p in gp.Payments)
{
PaymentDelete(p, ctx);
}
ctx.Delete(gp);
ctx.SaveChanges();
}
public static void DeleteExaminations(AriClinicContext ctx)
{
ctx.Delete(ctx.WithoutGlassesTests);
ctx.Delete(ctx.GlassesTests);
ctx.Delete(ctx.ContactLensesTests);
ctx.Delete(ctx.OpticalObjectiveExaminations);
ctx.Delete(ctx.PrescriptionGlasses);
ctx.Delete(ctx.Cycloplegias);
//
ctx.Delete(ctx.Biometries);
ctx.Delete(ctx.Topographies);
ctx.Delete(ctx.Paquimetries);
//
ctx.Delete(ctx.ExaminationAssigneds);
ctx.Delete(ctx.Examinations);
//
ctx.SaveChanges();
}
public static void GeneralPaymentDelete(GeneralPayment gp, AriClinicContext ctx)
{
foreach (Payment p in gp.Payments)
{
PaymentDelete(p, ctx);
}
ctx.Delete(gp);
ctx.SaveChanges();
}
public static void PaymentDelete(Payment pay, AriClinicContext ctx)
{
// minus paid in ticket
if (pay.Ticket != null)
{
pay.Ticket.Paid = pay.Ticket.Paid - pay.Amount;
}
ctx.Delete(pay);
ctx.SaveChanges();
}
public static void PaymentDelete(Payment pay, AriClinicContext ctx)
{
// minus paid in ticket
if (pay.Ticket != null)
{
pay.Ticket.Paid = pay.Ticket.Paid - pay.Amount;
}
ctx.Delete(pay);
ctx.SaveChanges();
}
public static void CreateAssociateTickets(AnestheticServiceNote asn, AriClinicContext ctx)
{
// Does this customer have a primary policy with that service?
Policy pol = PrimaryPolicy(asn.Customer);
if (pol == null)
{
throw new AriCliException(1, "There isn't a primary policy for this customer");
}
// Delete all tickets
ctx.Delete(asn.AnestheticTickets);
foreach (Procedure proc in asn.Procedures)
{
// Does this policy includes related (from procedure) services
InsuranceService ins = PolicyIncludesService(pol, proc.Service);
if (ins == null)
{
throw new AriCliException(3, "The insurance company have not the nomenclator service assigned");
}
// Everything seems ok, we can add anesthetic ticket
AnestheticTicket atck = new AnestheticTicket()
{
TicketDate = asn.ServiceNoteDate,
Description = String.Format("{0} ({1})", proc.Name, ins.Service.Name),
Amount = ins.Price,
Policy = pol,
InsuranceService = ins,
User = asn.User,
Clinic = asn.Clinic,
Professional = asn.Professional,
Surgeon = asn.Surgeon,
Procedure = proc,
AnestheticServiceNote = asn
};
ctx.Add(atck);
ctx.SaveChanges();
}
}
public static void CreateAssociateTickets(AnestheticServiceNote asn, AriClinicContext ctx)
{
// Does this customer have a primary policy with that service?
Policy pol = PrimaryPolicy(asn.Customer);
if (pol == null)
{
throw new AriCliException(1, "There isn't a primary policy for this customer");
}
// Delete all tickets
ctx.Delete(asn.AnestheticTickets);
foreach (Procedure proc in asn.Procedures)
{
// Does this policy includes related (from procedure) services
InsuranceService ins = PolicyIncludesService(pol, proc.Service);
if (ins == null)
{
throw new AriCliException(3, "The insurance company have not the nomenclator service assigned");
}
// Everything seems ok, we can add anesthetic ticket
AnestheticTicket atck = new AnestheticTicket()
{
TicketDate = asn.ServiceNoteDate,
Description = String.Format("{0} ({1})", proc.Name, ins.Service.Name),
Amount = ins.Price,
Policy = pol,
InsuranceService = ins,
User = asn.User,
Clinic = asn.Clinic,
Professional = asn.Professional,
Surgeon = asn.Surgeon,
Procedure = proc,
AnestheticServiceNote = asn
};
ctx.Add(atck);
ctx.SaveChanges();
}
}
public static void DeleteProcedures(AriClinicContext ctx)
{
ctx.Delete(ctx.ProcedureAssigneds);
ctx.Delete(ctx.Procedures);
ctx.SaveChanges();
}
public static void ImportAssurancePolicies(OleDbConnection con, AriClinicContext ctx)
{
//(0) Borrar las aseguradoras y pólizas previas.
ctx.Delete(ctx.Policies);
ctx.Delete(ctx.Insurances);
ctx.Delete(ctx.InsuranceServices);
ctx.SaveChanges();
//(1) Por defecto creamos una aseguradora que es la clínica de Valencia.
Insurance insurance = (from i in ctx.Insurances
where i.Name == "MIESTETIC (Valencia)"
select i).FirstOrDefault<Insurance>();
if (insurance == null)
{
insurance = new Insurance();
insurance.Name = "MIESTETIC (Valencia)";
insurance.Internal = true;
ctx.Add(insurance);
}
//(2) Ahora leemos, de nuevo, todos los tipos de servicio porque en OFT
// ellos llevan los importes y en nuestro caso son los Insurance services
string sql = "SELECT * FROM ServMed";
cmd = new OleDbCommand(sql, con);
da = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds, "ConServicios");
int nreg = ds.Tables["ConServicios"].Rows.Count;
int reg = 0;
InsuranceService ins = null;
foreach (DataRow dr in ds.Tables["ConServicios"].Rows)
{
reg++;
Console.WriteLine("Servicions {0:#####0} de {1:#####0} {2}", reg, nreg, "SERVICIO");
int id = (int)dr["IdServMed"];
ins = (from i in ctx.InsuranceServices
where i.OftId == id
select i).FirstOrDefault<InsuranceService>();
if (ins == null)
{
ins = new InsuranceService();
ctx.Add(ins);
}
ins.Insurance = insurance;
ins.Service = (from s in ctx.Services
where s.OftId == id
select s).FirstOrDefault<Service>();
ins.Price = (decimal)dr["Importe"];
}
//(3) por último asignamos una póliza a todos los clientes que tenemos dados de alta.
foreach (Customer cus in ctx.Customers)
{
Customer localCus = cus;
Policy pol = (from p in ctx.Policies
where p.Customer.PersonId == localCus.PersonId &&
p.Insurance.InsuranceId == insurance.InsuranceId
select p).FirstOrDefault<Policy>();
if (pol == null)
{
pol = new Policy();
pol.Customer = localCus;
pol.Insurance = insurance;
ctx.Add(pol);
}
}
ctx.SaveChanges();
}
public static void DeleteDiagnostics(AriClinicContext ctx)
{
ctx.Delete(ctx.DiagnosticAssigneds);
ctx.Delete(ctx.Diagnostics);
ctx.SaveChanges();
}
/// <summary>
/// Traspasa los datos que corresponden a servicios y categorias de servicio
/// </summary>
/// <param name="con">Conector a OFT</param>
/// <param name="ctx">Contexto de AriClinc</param>
public static void ImportCategories(OleDbConnection con, AriClinicContext ctx)
{
int id = 0;
// (0) Borrar los datos previos.
ctx.Delete(ctx.Services);
ctx.Delete(ctx.ServiceCategories);
ctx.SaveChanges();
// (1) Dar de alta las categorias de servicio
string sql = "SELECT * FROM TipServMed";
cmd = new OleDbCommand(sql, con);
da = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds, "ConServicios");
int nreg = ds.Tables["ConServicios"].Rows.Count;
int reg = 0;
ServiceCategory sc = null;
foreach (DataRow dr in ds.Tables["ConServicios"].Rows)
{
DataRow localDr = dr;
reg++;
Console.WriteLine("Categorias {0:#####0} de {1:#####0} {2}", reg, nreg, (string)localDr["NomTipServMed"]);
sc = (from scat in ctx.ServiceCategories
where scat.OftId == (int)localDr["IdTipServMed"]
select scat).FirstOrDefault<ServiceCategory>();
if (sc == null)
{
sc = new ServiceCategory();
ctx.Add(sc);
}
sc.Name = (string)localDr["NomTipServMed"];
sc.OftId = (int)localDr["IdTipservMed"];
ctx.SaveChanges();
}
// (2) Con las categorías dadas de alta damos de alta los
// servicios.
sql = "SELECT * FROM ServMed";
cmd = new OleDbCommand(sql, con);
da = new OleDbDataAdapter(cmd);
ds = new DataSet();
da.Fill(ds, "ConServ2");
nreg = ds.Tables["ConServ2"].Rows.Count;
reg = 0;
Service s = null;
foreach (DataRow dr in ds.Tables["ConServ2"].Rows)
{
reg++;
s = (from sr in ctx.Services
where sr.OftId == (int)dr["IdServMed"]
select sr).FirstOrDefault<Service>();
if (s == null)
{
s = new Service();
ctx.Add(s);
}
s.Name = (string)dr["NomServMed"];
id = (int)dr["IdTipServMed"];
s.ServiceCategory = (from scat in ctx.ServiceCategories
where scat.OftId == id
select scat).FirstOrDefault<ServiceCategory>();
s.OftId = (int)dr["IdServMed"];
}
ctx.SaveChanges();
}
public static void ImportDiagnostics(OleDbConnection con, AriClinicContext ctx)
{
// (0) Borra tipos previos
ctx.Delete(ctx.DiagnosticAssigneds);
ctx.Delete(ctx.Diagnostics);
ctx.SaveChanges();
// (1) Dar de alta los diferentes diagnósticos
string sql = "SELECT * FROM Diagnosticos";
cmd = new OleDbCommand(sql, con);
da = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds, "ConDiagnosticos");
int nreg = ds.Tables["ConDiagnosticos"].Rows.Count;
int reg = 0;
foreach (DataRow dr in ds.Tables["ConDiagnosticos"].Rows)
{
reg++;
Console.WriteLine("Diagnósticos {0:#####0} de {1:#####0} {2}", reg, nreg, (string)dr["NomDiag"]);
Diagnostic diag = (from d in ctx.Diagnostics
where d.OftId == (int)dr["IdDiag"]
select d).FirstOrDefault<Diagnostic>();
if (diag == null)
{
diag = new Diagnostic();
ctx.Add(diag);
}
diag.OftId = (int)dr["IdDiag"];
diag.Name = (string)dr["NomDiag"];
ctx.SaveChanges();
}
}
public static void ImportInvoices(OleDbConnection con, AriClinicContext ctx)
{
//(0) Delete previous invoices
ctx.Delete(ctx.InvoiceLines);
ctx.Delete(ctx.Invoices);
ctx.SaveChanges();
//
//(1) Read OFT invoices and import to Ariclinic
string sql = "SELECT * FROM Factura";
cmd = new OleDbCommand(sql, con);
da = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds, "ConFacturas");
int nreg = ds.Tables["ConFacturas"].Rows.Count;
int reg = 0;
foreach (DataRow dr in ds.Tables["ConFacturas"].Rows)
{
DataRow localDr = dr;
reg++;
Console.WriteLine("Facturas {0:#####0} de {1:#####0} {2}", reg, nreg, "FACTURAS 1");
Invoice inv = (from f in ctx.Invoices
where f.Serial == "F" &&
f.Year == (int)localDr["Ano"] &&
f.InvoiceNumber == (int)localDr["NumFactura"]
select f).FirstOrDefault<Invoice>();
if (inv == null)
{
inv = new Invoice();
ctx.Add(inv);
}
else
{
// if exits all lines will be recreated
ctx.Delete(inv.InvoiceLines);
}
inv.InvoiceDate = (DateTime)localDr["Fecha"];
inv.Year = (int)localDr["Ano"];
inv.InvoiceNumber = (int)localDr["NumFactura"];
inv.Serial = "F"; // we must to set serial parameter to "F"
int id = (int)localDr["NumHis"];
inv.Customer = (from c in ctx.Customers
where c.OftId == id
select c).FirstOrDefault<Customer>();
inv.Total = (decimal)localDr["Total"];
ctx.SaveChanges();
}
//(2) Importe invoice lines;
int idTipoIva = 0;
int idServMed = 0;
int Ano = 0;
int NumFac = 0;
sql = "SELECT * FROM LinFactura";
cmd = new OleDbCommand(sql, con);
da = new OleDbDataAdapter(cmd);
ds = new DataSet();
da.Fill(ds, "ConLineasFactura");
nreg = ds.Tables["ConLineasFactura"].Rows.Count;
reg = 0;
foreach (DataRow dr in ds.Tables["ConLineasFactura"].Rows)
{
reg++;
Console.WriteLine("Facturas 2 {0:#####0} de {1:#####0} {2}", reg, nreg, "FACTURAS 2");
InvoiceLine il = new InvoiceLine();
idTipoIva = (int)dr["IdTipoIva"];
idServMed = (int)dr["IdServMed"];
Ano = (int)dr["Ano"];
NumFac = (int)dr["NumFactura"];
TaxType tx = (from t in ctx.TaxTypes
where t.OftId == idTipoIva
select t).FirstOrDefault<TaxType>();
Service sv = (from s in ctx.Services
where s.OftId == idServMed
select s).FirstOrDefault<Service>();
sv.TaxType = tx;
Invoice inv = (from iv in ctx.Invoices
where iv.Year == Ano && iv.InvoiceNumber == NumFac && iv.Serial == "F"
select iv).FirstOrDefault<Invoice>();
il.Invoice = inv;
il.TaxType = tx;
il.TaxPercentage = tx.Percentage;
il.Amount = (decimal)dr["Importe"];
il.Description = (string)dr["Descripcion"];
ctx.Add(il);
ctx.SaveChanges();
}
}
public static void DeleteLabTest(AriClinicContext ctx)
{
ctx.Delete(ctx.LabTestAssigneds);
ctx.Delete(ctx.LabTests);
ctx.SaveChanges();
}
public static void DeletePrimaryClasses(AriClinicContext ctx)
{
ctx.Delete(ctx.Professionals);
ctx.Delete(ctx.Clinics); // clínicas.
ctx.Delete(ctx.Customers); // eliminar los clientes.
ctx.Delete(ctx.Patients); // por último, los pacientes.
ctx.SaveChanges();
}
public static void ImportDrugs(OleDbConnection con, AriClinicContext ctx)
{
// (0) Borra tipos previos
ctx.Delete(ctx.Treatments);
ctx.SaveChanges();
ctx.Delete(ctx.Drugs);
ctx.SaveChanges();
// (1) Dar de alta los diferentes diagnósticos
string sql = "SELECT * FROM Farmacos";
cmd = new OleDbCommand(sql, con);
da = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds, "ConFarmacos");
int nreg = ds.Tables["ConFarmacos"].Rows.Count;
int reg = 0;
foreach (DataRow dr in ds.Tables["ConFarmacos"].Rows)
{
reg++;
Console.WriteLine("Fármacos {0:#####0} de {1:#####0} {2}", reg, nreg, (string)dr["NomFarm"]);
Drug d = (from drg in ctx.Drugs
where drg.OftId == (int)dr["IdFarm"]
select drg).FirstOrDefault<Drug>();
if (d == null)
{
d = new Drug();
ctx.Add(d);
}
d.OftId = (int)dr["IdFarm"];
d.Name = (string)dr["NomFarm"];
ctx.SaveChanges();
}
}
public static void ImportTreatment(OleDbConnection con, AriClinicContext ctx)
{
// (0) Borra tipos previos
ctx.Delete(ctx.Treatments);
ctx.SaveChanges();
// (1) Dar de alta los diferentes diagnósticos
string sql = "SELECT * FROM HistFarm";
cmd = new OleDbCommand(sql, con);
da = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds, "ConFarmacos");
int nreg = ds.Tables["ConFarmacos"].Rows.Count;
int reg = 0;
foreach (DataRow dr in ds.Tables["ConFarmacos"].Rows)
{
reg++;
Console.WriteLine("Tratamientos {0:#####0} de {1:#####0} {2}", reg, nreg, "TREATMENT");
int id = (int)dr["IdFarm"];
Drug dia = (from d in ctx.Drugs
where d.OftId == id
select d).FirstOrDefault<Drug>();
id = (int)dr["NumHis"];
Patient patient = (from p in ctx.Patients
where p.OftId == id
select p).FirstOrDefault<Patient>();
DateTime treatmentDate = (DateTime)dr["Fecha"];
Treatment t = (from tr in ctx.Treatments
where tr.Patient.PersonId == patient.PersonId
&& tr.Drug.DrugId == dia.DrugId
&& tr.TreatmentDate == treatmentDate
select tr).FirstOrDefault<Treatment>();
if (t == null)
{
t = new Treatment();
ctx.Add(t);
}
t.Patient = patient;
t.Drug = dia;
if ((int)dr["TipoProc"] == 1)
{
id = (int)dr["ExtProc"];
t.BaseVisit = (from v in ctx.BaseVisits
where v.OftRefVisita == id
select v).FirstOrDefault<BaseVisit>();
}
t.TreatmentDate = treatmentDate;
t.Recommend = (string)dr["Posologia"];
t.Quantity = (int)(float)dr["Cantidad"];
ctx.SaveChanges();
}
}
public static void ImportProceduresAssigned(OleDbConnection con, AriClinicContext ctx)
{
// (0) Borra tipos previos
ctx.Delete(ctx.ProcedureAssigneds);
ctx.SaveChanges();
// (1) Dar de alta los diferentes diagnósticos
string sql = "SELECT * FROM HistProc";
cmd = new OleDbCommand(sql, con);
da = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds, "ConProcedimientos");
int nreg = ds.Tables["ConProcedimientos"].Rows.Count;
int reg = 0;
foreach (DataRow dr in ds.Tables["ConProcedimientos"].Rows)
{
reg++;
Console.WriteLine("Procedimientos {0:#####0} de {1:#####0} {2}", reg, nreg, "ASGPROCS");
int id = (int)dr["IdProEs"];
Procedure procedure = (from p in ctx.Procedures
where p.OftId == id
select p).FirstOrDefault<Procedure>();
id = (int)dr["NumHis"];
Patient patient = (from p in ctx.Patients
where p.OftId == id
select p).FirstOrDefault<Patient>();
DateTime procedureDate = (DateTime)dr["Fecha"];
ProcedureAssigned pa = (from pas in ctx.ProcedureAssigneds
where pas.Patient.PersonId == patient.PersonId
&& pas.Procedure.ProcedureId == procedure.ProcedureId
&& pas.ProcedureDate == procedureDate
select pas).FirstOrDefault<ProcedureAssigned>();
if (pa == null)
{
pa = new ProcedureAssigned();
ctx.Add(pa);
}
pa.Patient = patient;
pa.Procedure = procedure;
pa.ProcedureDate = procedureDate;
pa.Comments = (string)dr["Observa"];
ctx.SaveChanges();
}
}
public static void ImportProcedures(OleDbConnection con, AriClinicContext ctx)
{
// (0) Borra tipos previos
ctx.Delete(ctx.ProcedureAssigneds);
ctx.Delete(ctx.Procedures);
ctx.SaveChanges();
// (1) Dar de alta los diferentes diagnósticos
string sql = "SELECT * FROM Procedimientos";
cmd = new OleDbCommand(sql, con);
da = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds, "ConProcedimientos");
int nreg = ds.Tables["ConProcedimientos"].Rows.Count;
int reg = 0;
foreach (DataRow dr in ds.Tables["ConProcedimientos"].Rows)
{
DataRow localDr = dr;
reg++;
Console.WriteLine("Procedimientos {0:#####0} de {1:#####0} {2}", reg, nreg, (string)localDr["NomProEs"]);
Procedure proc = (from p in ctx.Procedures
where p.OftId == (int)localDr["IdProEs"]
select p).FirstOrDefault<Procedure>();
if (proc == null)
{
proc = new Procedure();
ctx.Add(proc);
}
proc.OftId = (int)localDr["IdProEs"];
proc.Name = (string)localDr["NomProEs"];
ctx.SaveChanges();
}
}
public static void ImportDiagnosticsAssigned(OleDbConnection con, AriClinicContext ctx)
{
// (0) Borra tipos previos
ctx.Delete(ctx.DiagnosticAssigneds);
ctx.SaveChanges();
// (1) Dar de alta los diferentes diagnósticos
string sql = "SELECT * FROM HistDiag";
cmd = new OleDbCommand(sql, con);
da = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds, "ConDiagnosticos");
int nreg = ds.Tables["ConDiagnosticos"].Rows.Count;
int reg = 0;
foreach (DataRow dr in ds.Tables["ConDiagnosticos"].Rows)
{
reg++;
Console.WriteLine("Diagnósticos asignados {0:#####0} de {1:#####0} {2}", reg, nreg, "DG ASIGNADOS");
int id = (int)dr["IdDiag"];
Diagnostic diag = (from d in ctx.Diagnostics
where d.OftId == id
select d).FirstOrDefault<Diagnostic>();
id = (int)dr["NumHis"];
Patient patient = (from p in ctx.Patients
where p.OftId == id
select p).FirstOrDefault<Patient>();
DiagnosticAssigned das = (from d in ctx.DiagnosticAssigneds
where d.Patient.PersonId == patient.PersonId
&& d.Diagnostic.DiagnosticId == diag.DiagnosticId
&& d.DiagnosticDate == (DateTime)dr["Fecha"]
select d).FirstOrDefault<DiagnosticAssigned>();
if (das == null)
{
das = new DiagnosticAssigned();
ctx.Add(das);
}
das.Patient = patient;
das.Diagnostic = diag;
if ((int)dr["TipoProc"] == 1)
{
id = (int)dr["ExtProc"];
das.BaseVisit = (from v in ctx.BaseVisits
where v.OftRefVisita == id
select v).FirstOrDefault<BaseVisit>();
}
das.DiagnosticDate = (DateTime)dr["Fecha"];
das.Comments = (string)dr["Observa"];
ctx.SaveChanges();
}
}
public static void ImportVisitReasons(OleDbConnection con, AriClinicContext ctx)
{
// (0) Borra tipos previos
ctx.Delete(ctx.VisitReasons);
ctx.SaveChanges();
// (1) Dar de alta los tipos de IVA importados.
string sql = "SELECT * FROM Motivos";
cmd = new OleDbCommand(sql, con);
da = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds, "ConMotivos");
int nreg = ds.Tables["ConMotivos"].Rows.Count;
int reg = 0;
foreach (DataRow dr in ds.Tables["ConMotivos"].Rows)
{
DataRow localDr = dr;
reg++;
Console.WriteLine("Motivos visita {0:#####0} de {1:#####0} {2}", reg, nreg, (string)localDr["NomMot"]);
VisitReason vr = (from v in ctx.VisitReasons
where v.OftId == (int)localDr["IdMot"]
select v).FirstOrDefault<VisitReason>();
if (vr == null)
{
vr = new VisitReason();
ctx.Add(vr);
}
vr.Name = (string)localDr["NomMot"];
vr.OftId = (int)localDr["IdMot"];
}
ctx.SaveChanges();
}
public static void DeleteDrugTreatments(AriClinicContext ctx)
{
ctx.Delete(ctx.Treatments);
ctx.Delete(ctx.Drugs);
ctx.SaveChanges();
}
public static void ImportPaymentTypes(OleDbConnection con, AriClinicContext ctx)
{
//(1) Borrar antiguas formas de pago
ctx.Delete(ctx.PaymentMethods);
ctx.SaveChanges();
//(2) Lleer todos los pacientes en OFT
string sql = "SELECT * FROM FormaPago";
cmd = new OleDbCommand(sql, con);
da = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds, "ConFPago");
int nreg = ds.Tables["ConFPago"].Rows.Count;
int reg = 0;
foreach (DataRow dr in ds.Tables["ConFPago"].Rows)
{
reg++;
Console.WriteLine("Formas de pago {0:#####0} de {1:#####0} {2}", reg, nreg, (string)dr["NomFormaPago"]);
PaymentMethod pm = (from pme in ctx.PaymentMethods
where pme.OftId == (int)dr["IdFormaPago"]
select pme).FirstOrDefault<PaymentMethod>();
if (pm == null)
{
pm = new PaymentMethod();
ctx.Add(pm);
}
pm.Name = (string)dr["NomFormaPago"];
pm.OftId = (int)dr["IdFormaPago"];
}
ctx.SaveChanges();
}
public static void ImportSources(OleDbConnection con, AriClinicContext ctx)
{
// (0) Borra tipos previos
ctx.Delete(ctx.Sources);
ctx.SaveChanges();
// (1) Dar de alta las procedencias
string sql = "SELECT * FROM ProcMed";
cmd = new OleDbCommand(sql, con);
da = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds, "ConProcMed");
int nreg = ds.Tables["ConProcMed"].Rows.Count;
int reg = 0;
foreach (DataRow dr in ds.Tables["ConProcMed"].Rows)
{
reg++;
Console.WriteLine("Procedencias {0:#####0} de {1:#####0} {2}", reg, nreg, (string)dr["NomProcMed"]);
Source src = CntAriCli.GetSourceByOftId((int)dr["IdProcMed"], ctx);
if (src == null)
{
src = new Source();
src.OftId = (int)dr["IdProcMed"];
ctx.Add(src);
}
src.Name = (string)dr["NomProcMed"];
ctx.SaveChanges();
}
}
public static void ImportPayments(OleDbConnection con, AriClinicContext ctx)
{
//(1) Borrar antiguos pagos
ctx.Delete(ctx.GeneralPayments);
ctx.Delete(ctx.Payments);
ctx.SaveChanges();
foreach (Ticket tt in ctx.Tickets)
{
tt.Paid = 0;
}
ctx.SaveChanges();
//(2) Obtener la clínica por defecto
Clinic cl = ctx.Clinics.FirstOrDefault<Clinic>();
//(3) Leer todos los pagos y darlos de alta.
string sql = "SELECT * FROM LinNotaPago";
cmd = new OleDbCommand(sql, con);
da = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds, "ConPagos");
int nreg = ds.Tables["ConPagos"].Rows.Count;
int reg = 0;
foreach (DataRow dr in ds.Tables["ConPagos"].Rows)
{
reg++;
Console.WriteLine("Formas de pago {0:#####0} de {1:#####0} {2}", reg, nreg, "PAGOS");
int id = (int)dr["IdFormaPago"];
PaymentMethod pm = (from p in ctx.PaymentMethods
where p.OftId == id
select p).FirstOrDefault<PaymentMethod>();
int idAno = (int)dr["Ano"];
int idNumNota = (int)dr["NumNota"];
ServiceNote note = (from n in ctx.ServiceNotes
where n.Oft_Ano == idAno && n.Oft_NumNota == idNumNota
select n).FirstOrDefault<ServiceNote>();
// we create a general payment too
GeneralPayment gp = (from gpp in ctx.GeneralPayments
where gpp.ServiceNote.ServiceNoteId == note.ServiceNoteId
&& gpp.PaymentDate == (DateTime)dr["Fecha"]
&& gpp.PaymentMethod.PaymentMethodId == pm.PaymentMethodId
&& gpp.Amount == (decimal)dr["Importe"]
select gpp).FirstOrDefault<GeneralPayment>();
if (gp == null)
{
gp = new GeneralPayment();
gp.Clinic = cl;
ctx.Add(gp);
}
gp.Amount = (decimal)dr["Importe"];
gp.ServiceNote = note;
gp.PaymentDate = (DateTime)dr["Fecha"];
gp.PaymentMethod = pm;
gp.Description = (string)dr["Descripcion"];
note.Paid = note.Paid + gp.Amount;
ctx.Delete(gp.Payments);
bool res = CntAriCli.PayNote(pm, (decimal)dr["Importe"], (DateTime)dr["Fecha"], (string)dr["Descripcion"], note, gp.Clinic, gp, ctx);
if (!res)
{
}
}
}
/// <summary>
/// Importa los tipos de IVA
/// </summary>
/// <param name="con"></param>
/// <param name="ctx"></param>
public static void ImportTaxTypes(OleDbConnection con, AriClinicContext ctx)
{
// (0) Borra tipos previos
ctx.Delete(ctx.TaxTypes);
ctx.SaveChanges();
// (1) Dar de alta los tipos de IVA importados.
string sql = "SELECT * FROM TiposIva";
cmd = new OleDbCommand(sql, con);
da = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds, "ConTiposIVA");
int nreg = ds.Tables["ConTiposIVA"].Rows.Count;
int reg = 0;
TaxType tt = null;
foreach (DataRow dr in ds.Tables["ConTiposIVA"].Rows)
{
DataRow localDr = dr;
reg++;
Console.WriteLine("Tipos IVA {0:#####0} de {1:#####0} {2}", reg, nreg, (string)localDr["NomTipoIva"]);
tt = (from ti in ctx.TaxTypes
where ti.OftId == (int)localDr["IdTipoIVA"]
select ti).FirstOrDefault<TaxType>();
if (tt == null)
{
tt = new TaxType();
ctx.Add(tt);
}
tt.Name = (string)localDr["NomTipoIva"];
Single p = (Single)localDr["Porcentaje"];
tt.Percentage = decimal.Parse(p.ToString());
tt.OftId = (int)localDr["IdTipoIva"];
}
ctx.SaveChanges();
}
public static void DeletePatientRelated(AriClinicContext ctx)
{
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.Delete(ctx.Addresses); // eliminar direcciones.
ctx.SaveChanges();
}
public static void ImportVisits(OleDbConnection con, AriClinicContext ctx)
{
int id = 0;
// (0) Borra tipos previos
ctx.Delete(ctx.MotAppends);
ctx.Delete(ctx.AntSegments);
ctx.Delete(ctx.Fundus);
ctx.Delete(ctx.OphthalmologicVisits);
ctx.Delete(ctx.BaseVisits);
ctx.SaveChanges();
// (1) Dar de alta las visitas importadas
string sql = "SELECT * FROM HistVisitas";
cmd = new OleDbCommand(sql, con);
da = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds, "ConVisitas");
int nreg = ds.Tables["ConVisitas"].Rows.Count;
int reg = 0;
foreach (DataRow dr in ds.Tables["ConVisitas"].Rows)
{
reg++;
Boolean newVisit = false;
Console.WriteLine("Visitas {0:#####0} de {1:#####0} {2}", reg, nreg, "VISITAS");
BaseVisit visit = (from v in ctx.BaseVisits
where v.OftRefVisita == (int)dr["RefVisita"]
select v).FirstOrDefault<BaseVisit>();
if (visit == null)
{
visit = new BaseVisit();
newVisit = true;
}
visit.OftRefVisita = (int)dr["RefVisita"];
visit.VisitDate = (DateTime)dr["Fecha"];
id = (int)dr["IdTipCit"];
visit.AppointmentType = (from apt in ctx.AppointmentTypes
where apt.OftId == id
select apt).FirstOrDefault<AppointmentType>();
id = (int)dr["IdMot"];
visit.VisitReason = (from vr in ctx.VisitReasons
where vr.OftId == id
select vr).FirstOrDefault<VisitReason>();
id = (int)dr["IdMed"];
visit.Professional = (from p in ctx.Professionals
where p.OftId == id
select p).FirstOrDefault<Professional>();
id = (int)dr["NumHis"];
visit.Patient = (from p in ctx.Patients
where p.OftId == id
select p).FirstOrDefault<Patient>();
if (dr["Observaciones"] != DBNull.Value)
visit.Comments = (string)dr["Observaciones"];
if ((decimal)(float)dr["TOOD"] != 0 || (decimal)(float)dr["TOOI"] != 0)
{
OphthalmologicVisit ophVisit;
if (newVisit)
{
ophVisit = new OphthalmologicVisit();
}
else
{
ophVisit = (OphthalmologicVisit)visit;
}
ophVisit.OftRefVisita = visit.OftRefVisita;
ophVisit.AppointmentType = visit.AppointmentType;
ophVisit.VisitReason = visit.VisitReason;
ophVisit.VisitDate = visit.VisitDate;
ophVisit.Patient = visit.Patient;
ophVisit.Professional = visit.Professional;
ophVisit.Comments = visit.Comments;
ophVisit.VType = "ophvisit";
if (newVisit) ctx.Add(ophVisit);
ctx.SaveChanges();
// Motilidad y anejos
MotAppend mot;
if (newVisit)
{
mot = new MotAppend();
}
else
{
mot = ophVisit.MotAppends[0];
}
if (dr["MotOcular"] != DBNull.Value)
mot.EyeMotility = (string)dr["MotOcular"];
if (dr["cejas"] != DBNull.Value)
mot.Eyebrows = (string)dr["Cejas"];
if (dr["AreaPeriocular"] != DBNull.Value)
mot.PeriocularArea = (string)dr["AreaPeriocular"];
mot.C1RE = (decimal)(float)dr["C1OD"];
mot.C1LE = (decimal)(float)dr["C1OI"];
mot.C2RE = (decimal)(float)dr["C2OD"];
mot.C2LE = (decimal)(float)dr["C2OI"];
mot.C3RE = (decimal)(float)dr["C3OD"];
mot.C3LE = (decimal)(float)dr["C3OI"];
mot.C4RE = (decimal)(float)dr["C4OD"];
mot.C4LE = (decimal)(float)dr["C4OI"];
mot.C5RE = (decimal)(float)dr["C5OD"];
mot.C5LE = (decimal)(float)dr["C5OI"];
mot.C6RE = (decimal)(float)dr["C6OD"];
mot.C6LE = (decimal)(float)dr["C6OI"];
mot.C7RE = (decimal)(float)dr["C7OD"];
mot.C7LE = (decimal)(float)dr["C7OI"];
mot.C8RE = (decimal)(float)dr["C8OD"];
mot.C8LE = (decimal)(float)dr["C8OI"];
mot.C9RE = (decimal)(float)dr["C9OD"];
mot.C9LE = (decimal)(float)dr["C9OI"];
mot.C10RE = (decimal)(float)dr["C10OD"];
mot.C10LE = (decimal)(float)dr["C10OI"];
mot.C11RE = (decimal)(float)dr["C11OD"];
mot.C11LE = (decimal)(float)dr["C11OI"];
mot.C12RE = (decimal)(float)dr["C12OD"];
mot.C12LE = (decimal)(float)dr["C12OI"];
mot.OphthalmologicVisit = ophVisit;
if (newVisit) ctx.Add(mot);
ctx.SaveChanges();
// Segmento anterior
AntSegment ant;
if (newVisit)
{
ant = new AntSegment();
}
else
{
ant = ophVisit.AntSegments[0];
}
if (dr["ObsParpados"] != DBNull.Value)
ant.EyebrowsComments = (string)dr["ObsParpados"];
if (dr["Conjuntiva"].GetType() == typeof(DBNull))
{
}
if (dr["Conjuntiva"] != DBNull.Value)
ant.Conjunctiva = (string)dr["Conjuntiva"];
if (dr["Cornea"] != DBNull.Value)
ant.Cornea = (string)dr["Cornea"];
if (dr["Camara"] != DBNull.Value)
ant.Chamber = (string)dr["Camara"];
if (dr["Tyndall"] != DBNull.Value)
ant.Tyndall = (string)dr["Tyndall"];
if (dr["Pupila"] != DBNull.Value)
ant.Pupil = (string)dr["Pupila"];
if (dr["Cristalino"] != DBNull.Value)
ant.Crystalline = (string)dr["Cristalino"];
ant.EyestrainLE = (decimal)(float)dr["TOOI"];
ant.EyestrainRE = (decimal)(float)dr["TOOD"];
ant.OphthalmologicVisit = ophVisit;
if (newVisit) ctx.Add(ant);
ctx.SaveChanges();
// Fondo de ojo
Fundus fundus;
if (newVisit)
{
fundus = new Fundus();
}
else
{
fundus = ophVisit.Fundus[0];
}
if (dr["NervioOptico"] != DBNull.Value)
fundus.OpticNerve = (string)dr["NervioOptico"];
if (dr["Vasos"] != DBNull.Value)
fundus.Vessels = (string)dr["Vasos"];
if (dr["Macula"] != DBNull.Value)
fundus.Macula = (string)dr["Macula"];
if (dr["Vitreo"] != DBNull.Value)
fundus.Vitreous = (string)dr["Vitreo"];
if (dr["Periferia"] != DBNull.Value)
fundus.Periphery = (string)dr["Periferia"];
fundus.OphthalmologicVisit = ophVisit;
if (newVisit) ctx.Add(fundus);
ctx.SaveChanges();
}
else
{
visit.VType = "general";
if (newVisit) ctx.Add(visit);
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)
{
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 void DeleteEmailsAddressAndTelephones(AriClinicContext ctx)
{
ctx.Delete(ctx.Emails); // eliminar correos electrónicos
ctx.Delete(ctx.Telephones); // eliminar teléfonos.
ctx.Delete(ctx.Addresses); // eliminar las direcciones.
ctx.SaveChanges();
}
private static void CreateThings()
{
// Create a user
using (AriClinicContext ctx = new AriClinicContext("AriClinicContext"))
{
Console.WriteLine("Deleting all records....");
ctx.Delete(ctx.Logs);
ctx.Delete(ctx.Users);
ctx.Delete(ctx.UserGroups);
ctx.Delete(ctx.HealthcareCompanies);
ctx.SaveChanges();
Console.WriteLine("Creating default group..");
UserGroup ug = new UserGroup();
ug.Name = "Reservado";
ctx.Add(ug);
Console.WriteLine("Creating administrator user..");
User user = new User();
user.Name = "Superuser";
user.Login = "******";
user.UserGroup = ug;
user = CntAriCli.EncryptPassword(user, "admin");
ctx.Add(user);
HealthcareCompany hc = new HealthcareCompany();
hc.Name = "Ariadna Salud S.L.";
ctx.Add(hc);
Clinic clinic = new Clinic()
{
Name = "Clinica 1"
};
ctx.Add(clinic);
// parameters
Console.WriteLine("Creating parameters...");
AriCliModel.Parameter parameter = new Parameter()
{
PainPump = null,
UseNomenclator = false
};
ctx.Add(parameter);
// processes
Console.WriteLine("Creating process...");
Process process = new Process()
{
Name = "Administración",
Code = "admin",
};
Process admin = process;
ctx.Add(admin);
process = new Process()
{
Name = "Procesos",
Code = "process",
ParentProcess = admin
};
ctx.Add(process);
process = new Process()
{
Name = "Permisos",
Code = "permision",
ParentProcess = admin
};
ctx.Add(process);
// permissions
Console.WriteLine("Creating permissions...");
Permission permission = new Permission()
{
Process = admin,
UserGroup = ug,
View=true,
Create=true,
Modify=true,
Execute=true,
};
ctx.Add(permission);
permission = new Permission()
{
Process = process, // must be permission process
UserGroup = ug,
View = true,
Create = true,
Modify = true,
Execute = true,
};
ctx.Add(permission);
// import data
ctx.SaveChanges();
Console.WriteLine("All jobs done");
}
}
public static void BigDelete(AriClinicContext ctx)
{
ctx.Delete(ctx.InvoiceLines);
ctx.Delete(ctx.Invoices);
ctx.SaveChanges();
ctx.Delete(ctx.AppointmentInfos);
ctx.Delete(ctx.AppointmentTypes);
ctx.Delete(ctx.Diaries);
ctx.SaveChanges();
ctx.Delete(ctx.Payments);
ctx.Delete(ctx.Tickets);
ctx.Delete(ctx.ServiceNotes);
ctx.Delete(ctx.Policies);
ctx.Delete(ctx.Insurances);
ctx.Delete(ctx.InsuranceServices);
ctx.SaveChanges();
ctx.Delete(ctx.Services);
ctx.Delete(ctx.ServiceCategories);
ctx.Delete(ctx.TaxTypes);
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.PaymentMethods);
ctx.SaveChanges();
}
