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();
}
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 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 ApplyRisk(AnestheticServiceNote asn, AriClinicContext ctx)
{
if (asn.Chk3)
{
foreach (AnestheticTicket atck in asn.AnestheticTickets)
{
atck.Amount = atck.Amount * 1.5M;
}
ctx.SaveChanges();
}
}
public static void ApplyPCA(AnestheticServiceNote asn, AriClinicContext ctx)
{
// Read parameters
AriCliModel.Parameter parameter = GetParameter(ctx);
Service painPump = parameter.PainPump;
// Do we need check pain pump?
if (painPump != null && asn.Chk1)
{
// Is there a pain pump assigned?
var rs = from t in asn.AnestheticTickets
where t.InsuranceService.Service.ServiceId == painPump.ServiceId
select t;
if (rs.Count() == 0)
{
// 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");
}
// Does this policy (insurance) includes a pain pump service?
InsuranceService ins = PolicyIncludesService(pol, painPump);
if (ins == null)
{
throw new AriCliException(2, "The insurance company have not the pain pump service assigned");
}
// More expensive procedure
AnestheticTicket aatck = asn.AnestheticTickets.OrderByDescending(x => x.Amount).FirstOrDefault <AnestheticTicket>();
if (aatck != null)
{
Procedure proc = aatck.Procedure;
// Everything seems ok, we can add the 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 ApplyMultiTicket(AnestheticServiceNote asn, AriClinicContext ctx)
{
bool first = true;
if (asn.AnestheticTickets.Count > 1)
{
foreach (AnestheticTicket atck in asn.AnestheticTickets.OrderByDescending(x => x.Amount))
{
if (!first)
{
atck.Amount = atck.Amount / 2;
atck.Comments = "-50%";
ctx.SaveChanges();
}
first = false;
}
}
}
public static GeneralPayment GeneralPaymentNew(Clinic clinic, ServiceNote sn, decimal amount, PaymentMethod payMethod, DateTime payDate, string description, AriClinicContext ctx)
{
var rs = from t in sn.Tickets
where t.Amount > t.Paid
select t;
GeneralPayment gp = new GeneralPayment();
gp.ServiceNote = sn;
gp.PaymentDate = payDate;
gp.Description = description;
gp.PaymentMethod = payMethod;
gp.Amount = amount;
gp.Clinic = clinic;
ctx.Add(gp);
foreach (Ticket t in rs.OrderByDescending(tk => tk.Amount - tk.Paid))
{
Payment pay = new Payment();
pay.PaymentMethod = payMethod;
pay.PaymentDate = payDate;
pay.Ticket = t;
pay.GeneralPayment = gp;
pay.Description = description;
pay.Clinic = clinic;
decimal dif = t.Amount - t.Paid;
if (dif <= amount)
{
pay.Amount = dif;
amount = amount - dif;
t.Paid = t.Paid + dif;
}
else
{
pay.Amount = amount;
t.Paid = t.Paid + amount;
amount = 0;
}
ctx.Add(pay);
if (amount == 0)
{
break;
}
}
ctx.SaveChanges();
return(gp);
}
public static void ApplyMultiTicket(AnestheticServiceNote asn, AriClinicContext ctx)
{
bool first = true;
if (asn.AnestheticTickets.Count > 1)
{
foreach (AnestheticTicket atck in asn.AnestheticTickets.OrderByDescending(x => x.Amount))
{
if (!first)
{
atck.Amount = atck.Amount / 2;
atck.Comments = "-50%";
ctx.SaveChanges();
}
first = false;
}
}
}
public static GeneralPayment GeneralPaymentNew(Clinic clinic, ServiceNote sn, decimal amount, PaymentMethod payMethod, DateTime payDate, string description, AriClinicContext ctx)
{
var rs = from t in sn.Tickets
where t.Amount > t.Paid
select t;
GeneralPayment gp = new GeneralPayment();
gp.ServiceNote = sn;
gp.PaymentDate = payDate;
gp.Description = description;
gp.PaymentMethod = payMethod;
gp.Amount = amount;
gp.Clinic = clinic;
ctx.Add(gp);
foreach (Ticket t in rs.OrderByDescending(tk => tk.Amount - tk.Paid))
{
Payment pay = new Payment();
pay.PaymentMethod = payMethod;
pay.PaymentDate = payDate;
pay.Ticket = t;
pay.GeneralPayment = gp;
pay.Description = description;
pay.Clinic = clinic;
decimal dif = t.Amount - t.Paid;
if (dif <= amount)
{
pay.Amount = dif;
amount = amount - dif;
t.Paid = t.Paid + dif;
}
else
{
pay.Amount = amount;
t.Paid = t.Paid + amount;
amount = 0;
}
ctx.Add(pay);
if (amount == 0) break;
}
ctx.SaveChanges();
return gp;
}
public static void CheckTickets(AnestheticServiceNote asn, bool[] lschk, AriClinicContext ctx)
{
int i = 0;
int lenght = lschk.Count();
foreach (AnestheticTicket actk in asn.AnestheticTickets)
{
if (asn.Chk2)
{
actk.Checked = true;
}
else
{
if (i < lenght)
{
actk.Checked = lschk[i];
}
}
i++;
}
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();
}
}
/// <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();
}
/// <summary>
/// Importa losprofesionales dados de alta en OFT
/// </summary>
/// <param name="con">Conector con OFT</param>
/// <param name="ctx">Contexto para AriClinic</param>
public static void ImportProfessionals(OleDbConnection con, AriClinicContext ctx)
{
//(0) Borrar los professionales que existieran previamente
//ctx.Delete(ctx.Professionals);
//(1) Leer los Médicos de OFT
string sql = "SELECT * FROM Medicos";
cmd = new OleDbCommand(sql, con);
da = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds, "ConMedicos");
int nreg = ds.Tables["ConMedicos"].Rows.Count;
int reg = 0;
Professional p = null;
foreach (DataRow dr in ds.Tables["ConMedicos"].Rows)
{
DataRow localDr = dr;
reg++;
Console.WriteLine("Profesionales {0:#####0} de {1:#####0} {2}", reg, nreg, (string)localDr["NomMed"]);
p = (from pf in ctx.Professionals
where pf.OftId == (int)localDr["IdMed"]
select pf).FirstOrDefault<Professional>();
if (p == null)
{
p = new Professional();
ctx.Add(p);
}
p.ComercialName = (string)localDr["NomMed"];
p.FullName = p.ComercialName;
p.OftId = (int)localDr["IdMed"];
}
ctx.SaveChanges();
}
public static void ImportAppointmentInfo(OleDbConnection con, AriClinicContext ctx)
{
//(1) Borramos las citas anteriores.
//ctx.Delete(ctx.AppointmentInfos);
//(2) Leer las agendas OFT y darlas de alta en AriClinic
string sql = "SELECT * FROM Citas";
cmd = new OleDbCommand(sql, con);
da = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds, "ConCitas");
int nreg = ds.Tables["ConCitas"].Rows.Count;
int reg = 0;
int i2 = 0;
int i3 = 0;
int i4 = 0;
foreach (DataRow dr in ds.Tables["ConCitas"].Rows)
{
i3++;
reg++;
Console.WriteLine("Citas {0:#####0} de {1:#####0} {2}", reg, nreg, "CITAS");
int id = (int)dr["NumHis"];
Patient patient = (from p in ctx.Patients
where p.OftId == id
select p).FirstOrDefault<Patient>();
id = (int)dr["IdLibCit"];
Diary diary = (from d in ctx.Diaries
where d.OftId == id
select d).FirstOrDefault<Diary>();
DateTime dt = (DateTime)dr["Fecha"];
DateTime ht = (DateTime)dr["Hora"];
DateTime dd = new DateTime(dt.Year, dt.Month, dt.Day, ht.Hour, ht.Minute, ht.Second);
AppointmentInfo app = (from a in ctx.AppointmentInfos
where a.Diary.DiaryId == diary.DiaryId
&& a.Patient.PersonId == patient.PersonId
&& a.BeginDateTime == dd
select a).FirstOrDefault<AppointmentInfo>();
if (app == null)
{
app = new AppointmentInfo();
ctx.Add(app);
}
id = (int)dr["IdTipCit"];
app.AppointmentType = (from at in ctx.AppointmentTypes
where at.OftId == id
select at).FirstOrDefault<AppointmentType>();
app.Diary = diary;
app.Patient = patient;
id = (int)dr["IdMed"];
app.Professional = (from pr in ctx.Professionals
where pr.OftId == id
select pr).FirstOrDefault<Professional>();
i4++;
app.BeginDateTime = dd;
ht = (DateTime)dr["HrFin"];
dd = new DateTime(dt.Year, dt.Month, dt.Day, ht.Hour, ht.Minute, ht.Second);
app.EndDateTime = dd;
ht = (DateTime)dr["Durac"];
app.Duration = ht.Minute;
if (app.Patient != null)
{
i2++;
app.Subject = CntAriCli.GetAppointmentSubject(app);
}
else
{
app.Subject = "SIN PACIENTE";
}
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 CheckTickets(AnestheticServiceNote asn, bool[] lschk, AriClinicContext ctx)
{
int i = 0;
int lenght = lschk.Count();
foreach (AnestheticTicket actk in asn.AnestheticTickets)
{
if (asn.Chk2)
{
actk.Checked = true;
}
else
{
if (i < lenght) actk.Checked = lschk[i];
}
i++;
}
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 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)
{
}
}
}
public static void ApplyPCA(AnestheticServiceNote asn, AriClinicContext ctx)
{
// Read parameters
AriCliModel.Parameter parameter = GetParameter(ctx);
Service painPump = parameter.PainPump;
// Do we need check pain pump?
if (painPump != null && asn.Chk1)
{
// Is there a pain pump assigned?
var rs = from t in asn.AnestheticTickets
where t.InsuranceService.Service.ServiceId == painPump.ServiceId
select t;
if (rs.Count() == 0)
{
// 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");
}
// Does this policy (insurance) includes a pain pump service?
InsuranceService ins = PolicyIncludesService(pol, painPump);
if (ins == null)
{
throw new AriCliException(2, "The insurance company have not the pain pump service assigned");
}
// More expensive procedure
AnestheticTicket aatck = asn.AnestheticTickets.OrderByDescending(x => x.Amount).FirstOrDefault<AnestheticTicket>();
if (aatck != null)
{
Procedure proc = aatck.Procedure;
// Everything seems ok, we can add the 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 ActualizaTiposCitaVisitas(OleDbConnection con, AriClinicContext ctx)
{
//
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++;
Console.WriteLine("Act visitas - {0:#####0} de {1:#####0}", reg, nreg);
int oft_ref_visita = (int)dr["RefVisita"];
int oft_id = (int)dr["IdTipCit"];
if (oft_id != 0)
{
// buscamos la nota de servicio
BaseVisit visit = (from v in ctx.BaseVisits
where v.OftRefVisita == oft_ref_visita
select v).FirstOrDefault<BaseVisit>();
if (visit != null)
{
Console.WriteLine("---------------------->Tipo visita");
// ahora el tiipo relacionado relacionada
AppointmentType atype = (from a in ctx.AppointmentTypes
where a.OftId == oft_id
select a).FirstOrDefault<AppointmentType>();
if (atype != null)
{
visit.AppointmentType = atype;
ctx.SaveChanges();
}
}
}
}
}
public static void DeleteLabTest(AriClinicContext ctx)
{
ctx.Delete(ctx.LabTestAssigneds);
ctx.Delete(ctx.LabTests);
ctx.SaveChanges();
}
private static void CreateServicesToTest()
{
using (AriClinicContext ctx = new AriClinicContext("AriClinicContext"))
{
for (int i = 0; i < 10000; i++)
{
Service ser = new Service();
ser.Name = String.Format("Servicio {0}", i);
int i2 = i % 2;
switch (i2)
{
case 0:
ser.TaxType = CntAriCli.GetTaxType(3, ctx);
break;
case 1:
ser.TaxType = CntAriCli.GetTaxType(4, ctx);
break;
}
int i3 = i % 3;
switch (i3)
{
case 0:
ser.ServiceCategory = CntAriCli.GetServiceCategory(1, ctx);
break;
case 1:
ser.ServiceCategory = CntAriCli.GetServiceCategory(2, ctx);
break;
}
ctx.Add(ser);
ctx.SaveChanges();
Console.WriteLine("Creando registro {0}", i);
}
}
}
public static void ImportServiceNote(OleDbConnection con, AriClinicContext ctx)
{
//(0) Borrar las notas de servicio y tickets previos
//ctx.Delete(ctx.Payments);
//ctx.Delete(ctx.GeneralPayments);
//ctx.Delete(ctx.Tickets);
//ctx.Delete(ctx.ServiceNotes);
//ctx.SaveChanges();
// Nos hace falta una clínica, la creamos ahora
Clinic cl = (from c in ctx.Clinics
where c.Name == "Clinica Valencia"
select c).FirstOrDefault<Clinic>();
if (cl == null)
{
cl = new Clinic();
cl.Name = "Clinica Valencia";
ctx.Add(cl);
ctx.SaveChanges();
}
//(1) Leer las notas de servicio OFT
string sql = "SELECT * FROM NotaServicio";
cmd = new OleDbCommand(sql, con);
da = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds, "ConNotaServicio");
int nreg = ds.Tables["ConNotaServicio"].Rows.Count;
int reg = 0;
foreach (DataRow dr in ds.Tables["ConNotaServicio"].Rows)
{
reg++;
Console.WriteLine("Notas de servicio {0:#####0} de {1:#####0} A:{2} N:{3}", reg, nreg,(int)dr["Ano"], (int)dr["NumNota"]);
ServiceNote sn = (from s in ctx.ServiceNotes
where s.Oft_Ano == (int)dr["Ano"]
&& s.Oft_NumNota == (int)dr["NumNota"]
select s).FirstOrDefault<ServiceNote>();
if (sn == null)
{
sn = new ServiceNote();
ctx.Add(sn);
}
int id = (int)dr["NumHis"];
sn.Customer = (from cus in ctx.Customers
where cus.OftId == id
select cus).FirstOrDefault<Customer>();
sn.ServiceNoteDate = (DateTime)dr["Fecha"];
decimal total = (decimal)dr["Total"];
sn.Total = total;
sn.Clinic = cl;
Professional prf = (from p in ctx.Professionals
where p.OftId == 0
select p).FirstOrDefault<Professional>();
sn.Professional = prf;
sn.Oft_Ano = (int)dr["Ano"];
sn.Oft_NumNota = (int)dr["NumNota"];
ctx.SaveChanges();
}
//(2) Importar la líneas de las notas de servicio
sql = "SELECT * FROM LinNotaServicio";
cmd = new OleDbCommand(sql, con);
da = new OleDbDataAdapter(cmd);
ds = new DataSet();
da.Fill(ds, "ConLineasServicio");
nreg = ds.Tables["ConLineasServicio"].Rows.Count;
reg = 0;
foreach (DataRow dr in ds.Tables["ConLineasServicio"].Rows)
{
reg++;
Console.WriteLine("Lineas de servicio {0:#####0} de {1:#####0} A:{2} N:{3}", reg, nreg, (int)dr["Ano"], (int)dr["NumNota"]);
int idSer = (int)dr["IdServMed"];
int idAno = (int)dr["Ano"];
int idNumNota = (int)dr["NumNota"];
int idProfessional = (int)dr["IdMed"];
InsuranceService insuranceService = (from ins in ctx.InsuranceServices
where ins.Service.OftId == idSer
select ins).FirstOrDefault<InsuranceService>();
ServiceNote serviceNote = (from sn in ctx.ServiceNotes
where sn.Oft_Ano == idAno && sn.Oft_NumNota == idNumNota
select sn).FirstOrDefault<ServiceNote>();
Decimal amount = (decimal)dr["Importe"];
Professional professional = (from p in ctx.Professionals
where p.OftId == idProfessional
select p).FirstOrDefault<Professional>();
Ticket tk = (from t in ctx.Tickets
where t.ServiceNote.ServiceNoteId == serviceNote.ServiceNoteId
&& t.InsuranceService.InsuranceServiceId == insuranceService.InsuranceServiceId
&& t.Professional.PersonId == professional.PersonId
&& t.Amount == amount
select t).FirstOrDefault<Ticket>();
if (tk == null)
{
tk = new Ticket();
ctx.Add(tk);
}
tk.InsuranceService = insuranceService;
tk.ServiceNote = serviceNote;
tk.Amount = amount;
tk.Professional = professional;
tk.ServiceNote.Professional = tk.Professional;
if (tk.ServiceNote.Professional == null)
tk.ServiceNote.Professional = tk.Professional;
tk.Description = (string)dr["Descripcion"];
tk.Clinic = cl;
tk.TicketDate = tk.ServiceNote.ServiceNoteDate;
// hay notas sin cliente, no deberia pero las hay
if (tk.ServiceNote.Customer != null)
tk.Policy = tk.ServiceNote.Customer.Policies.FirstOrDefault<Policy>();
ctx.SaveChanges();
}
}
public static void DeleteProcedures(AriClinicContext ctx)
{
ctx.Delete(ctx.ProcedureAssigneds);
ctx.Delete(ctx.Procedures);
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);
}
}
/// <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 ApplyRisk(AnestheticServiceNote asn, AriClinicContext ctx)
{
if (asn.Chk3)
{
foreach (AnestheticTicket atck in asn.AnestheticTickets)
{
atck.Amount = atck.Amount * 1.5M;
}
ctx.SaveChanges();
}
}
public static void ImportDiary(OleDbConnection con, AriClinicContext ctx)
{
//(1) Borramos las agendas anteriores
//ctx.Delete(ctx.Diaries);
//(2) Leer las agendas OFT y darlas de alta en AriClinic
string sql = "SELECT * FROM LibrosCita";
cmd = new OleDbCommand(sql, con);
da = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds, "ConLibrosCita");
int nreg = ds.Tables["ConLibrosCita"].Rows.Count;
int reg = 0;
foreach (DataRow dr in ds.Tables["ConLibrosCita"].Rows)
{
reg++;
Console.WriteLine("Formas de pago {0:#####0} de {1:#####0} {2}", reg, nreg, (string)dr["NomLibCit"]);
int id = (int)dr["IdLibCit"];
Diary dia = (from d in ctx.Diaries
where d.OftId == id
select d).FirstOrDefault<Diary>();
if (dia == null)
{
dia = new Diary();
ctx.Add(dia);
}
dia.BeginHour = (DateTime)dr["HrIni"];
dia.EndHour = (DateTime)dr["HrFin"];
dia.Name = (string)dr["NomLibCit"];
dia.TimeStep = 10;
dia.OftId = id;
}
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 DeleteDiagnostics(AriClinicContext ctx)
{
ctx.Delete(ctx.DiagnosticAssigneds);
ctx.Delete(ctx.Diagnostics);
ctx.SaveChanges();
}
public static void ImportAppointmentType(OleDbConnection con, AriClinicContext ctx)
{
//(1) Primero borrar citas y los tipos de cita anteriores
//ctx.Delete(ctx.AppointmentInfos);
//ctx.Delete(ctx.AppointmentTypes);
//(2) Leer los tipos de OFT y darlos de alta en AriClinic
string sql = "SELECT * FROM TiposCita";
cmd = new OleDbCommand(sql, con);
da = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds, "ConTiposCita");
int nreg = ds.Tables["ConTiposCita"].Rows.Count;
int reg = 0;
foreach (DataRow dr in ds.Tables["ConTiposCita"].Rows)
{
reg++;
Console.WriteLine("Formas de pago {0:#####0} de {1:#####0} {2}", reg, nreg, (string)dr["NomTipCit"]);
int id = (int)dr["IdTipCit"];
DateTime durac = (DateTime)dr["Durac"];
AppointmentType apptype = (from apt in ctx.AppointmentTypes
where apt.OftId == id
select apt).FirstOrDefault<AppointmentType>();
if (apptype == null)
{
apptype = new AppointmentType();
ctx.Add(apptype);
}
apptype.Name = (string)dr["NomTipCit"];
apptype.Duration = durac.Minute;
apptype.OftId = id;
}
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();
}
}
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 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();
}
