C# AriClinicContext.Add方法代码示例

 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 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 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();
            }
        }

 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);
         }
     }
 }

        /// <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 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 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 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 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 ProcessRefractometry(int id, Refractometry rf, OleDbConnection con, AriClinicContext ctx)
        {
            // WithoutGlasses
            string sql = String.Format("SELECT * FROM SNGRefracto WHERE IdRef={0}", id);
            cmd = new OleDbCommand(sql, con);
            da = new OleDbDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds, "ConSNGRefracto");
            if (ds.Tables["ConSNGRefracto"].Rows.Count > 0)
            {
                DataRow dr = ds.Tables["ConSNGRefracto"].Rows[0];
                WithoutGlassesTest wt = new WithoutGlassesTest();
                wt.Refractometry = rf;
                if (dr["SNGLejAVisOD"] != DBNull.Value) wt.FarVisualAcuityRightEye = (string)dr["SNGLejAVisOD"];
                if (dr["SNGLejAVisOI"] != DBNull.Value) wt.FarVisualAcuityLeftEye = (string)dr["SNGLejAVisOI"];
                if (dr["SNGLejAVisAO"] != DBNull.Value) wt.FarVisualAcuityBothEyes = (string)dr["SNGLejAVisAO"];
                if (dr["SNGCerAVisOD"] != DBNull.Value) wt.CloseVisualAcuityRightEye = (string)dr["SNGCerAVisOD"];
                if (dr["SNGCerAVisOI"] != DBNull.Value) wt.CloseVisualAcuityLeftEye = (string)dr["SNGCerAVisOI"];
                if (dr["SNGCerAVisAO"] != DBNull.Value) wt.CloseVisualAcuityBothEyes = (string)dr["SNGCerAVisAO"];
                if (dr["SNGObs"] != DBNull.Value)
                    wt.Comments = (string)dr["SNGObs"];
                ctx.Add(wt);
                ctx.SaveChanges();
            }

            //Glasses Test
            sql = String.Format("SELECT * FROM SGRefracto WHERE IdRef={0}", id);
            cmd = new OleDbCommand(sql, con);
            da = new OleDbDataAdapter(cmd);
            ds = new DataSet();
            da.Fill(ds, "ConSGRefracto");
            if (ds.Tables["ConSGRefracto"].Rows.Count > 0)
            {
                DataRow dr = ds.Tables["ConSGRefracto"].Rows[0];
                GlassesTest gt = new GlassesTest();
                gt.Refractometry = rf;
                if (dr["SGLejAVisOD"] != DBNull.Value) gt.FarVisualAcuityRightEye = (string)dr["SGLejAVisOD"];
                if (dr["SGTuAVisOD"] != DBNull.Value) gt.BothAcuityRightEye = (string)dr["SGTuAVisOD"];
                if (dr["SGCerAVisOD"] != DBNull.Value) gt.CloseAcuityRightEye = (string)dr["SGCerAVisOD"];

                if (dr["SGLejAVisOI"] != DBNull.Value) gt.FarVisualAcuityLeftEye = (string)dr["SGLejAVisOI"];
                if (dr["SGTuAVisOI"] != DBNull.Value) gt.BothAcuityLeftEye = (string)dr["SGTuAVisOI"];
                if (dr["SGCerAVisOI"] != DBNull.Value) gt.CloseAcuityLeftEye = (string)dr["SGCerAVisOI"];

                if (dr["SGObs"] != DBNull.Value) gt.Comments = (string)dr["SGObs"];

                if (dr["SGLeEsfOD"] != DBNull.Value) gt.FarSphericityRightEye = (string)dr["SGLeEsfOD"];
                if (dr["SGLeCilOD"] != DBNull.Value) gt.FarCylinderRightEye = (string)dr["SGLeCilOD"];
                if (dr["SGLeEjeOD"] != DBNull.Value) gt.FarAxisRightEye = (string)dr["SGLeEjeOD"];
                if (dr["SGLePrisOD"] != DBNull.Value) gt.FarPrimsRightEye = (string)dr["SGLePrisOD"];

                if (dr["SGLeEsfOI"] != DBNull.Value) gt.FarSphericityLeftEye = (string)dr["SGLeEsfOI"];
                if (dr["SGLeCilOI"] != DBNull.Value) gt.FarCylinderLeftEye = (string)dr["SGLeCilOI"];
                if (dr["SGLeEjeOI"] != DBNull.Value) gt.FarAxisLeftEye = (string)dr["SGLeEjeOI"];
                if (dr["SGLePrisOI"] != DBNull.Value) gt.FarPrismLeftEye = (string)dr["SGLePrisOI"];

                if (dr["SGLeCent"] != DBNull.Value) gt.FarCenters = (string)dr["SGLeCent"];
                if (dr["SGLeAVis"] != DBNull.Value) gt.FarAcuity = (string)dr["SGLeAVis"];

                if (dr["SGTuEsfOD"] != DBNull.Value) gt.BothSphericityRightEye = (string)dr["SGTuEsfOD"];
                if (dr["SGTuCilOD"] != DBNull.Value) gt.BothCylinderRightEye = (string)dr["SGTuCilOD"];
                if (dr["SGTuEjeOD"] != DBNull.Value) gt.BothAxisRightEye = (string)dr["SGTuEjeOD"];
                if (dr["SGTuPrisOD"] != DBNull.Value) gt.BothPrismRightEye = (string)dr["SGTuPrisOD"];
                //
                if (dr["SGTuEsfOI"] != DBNull.Value) gt.BothSphericityLeftEye = (string)dr["SGTuEsfOI"];
                if (dr["SGTuCilOI"] != DBNull.Value) gt.BothCylinderLeftEye = (string)dr["SGTuCilOI"];
                if (dr["SGTuEjeOI"] != DBNull.Value) gt.BothAxisLeftEye = (string)dr["SGTuEjeOI"];
                if (dr["SGTuPrisOI"] != DBNull.Value) gt.BothPrismLeftEye = (string)dr["SGTuPrisOI"];

                if (dr["SGTuCent"] != DBNull.Value) gt.BothCenters = (string)dr["SGTuCent"];
                if (dr["SGTuAVis"] != DBNull.Value) gt.BothAcuity = (string)dr["SGTuAVis"];

                if (dr["SGCerEsfOD"] != DBNull.Value) gt.CloseSphericityRightEye = (string)dr["SGCerEsfOD"];
                if (dr["SGCerCilOD"] != DBNull.Value) gt.CloseCylinderRightEye = (string)dr["SGCerCilOD"];
                if (dr["SGCerEjeOD"] != DBNull.Value) gt.CloseAxisRightEye = (string)dr["SGCerEjeOD"];
                if (dr["SGCerPrisOD"] != DBNull.Value) gt.ClosePrismRightEye = (string)dr["SGCerEjeOD"];

                if (dr["SGCerEsfOI"] != DBNull.Value) gt.CloseSphericityLeftEye = (string)dr["SGCerEsfOI"];
                if (dr["SGCerCilOI"] != DBNull.Value) gt.CloseCylinderLeftEye = (string)dr["SGCerCilOI"];
                if (dr["SGCerEjeOI"] != DBNull.Value) gt.CloseAxisLeftEye = (string)dr["SGCerEjeOI"];
                if (dr["SGCerPrisOI"] != DBNull.Value) gt.ClosePrismLeftEye = (string)dr["SGCerEjeOI"];

                if (dr["SGCerCent"] != DBNull.Value) gt.CloseCenters = (string)dr["SGCerCent"];
                if (dr["SGCerAVis"] != DBNull.Value) gt.CloseAcuity = (string)dr["SGCerAVis"];

                ctx.Add(gt);
                ctx.SaveChanges();
            }

            // Contact Lenses test
            sql = String.Format("SELECT * FROM LCRefracto WHERE IdRef={0}", id);
            cmd = new OleDbCommand(sql, con);
            da = new OleDbDataAdapter(cmd);
            ds = new DataSet();
            da.Fill(ds, "ConSNGRefracto");
            if (ds.Tables["ConSNGRefracto"].Rows.Count > 0)
            {
                DataRow dr = ds.Tables["ConSNGRefracto"].Rows[0];
                ContactLensesTest lt = new ContactLensesTest();
                lt.Refractometry = rf;
//.........这里部分代码省略.........

        public static void ImportExaminationsAssigned(OleDbConnection con, AriClinicContext ctx)
        {
            // (0) Borra tipos previos
            //ctx.Delete(ctx.WithoutGlassesTests);
            //ctx.Delete(ctx.GlassesTests);
            //ctx.Delete(ctx.ContactLensesTests);
            //ctx.Delete(ctx.OpticalObjectiveExaminations);
            //ctx.Delete(ctx.SubjectiveOpticalExaminations);
            //ctx.Delete(ctx.Cycloplegias);
            //ctx.Delete(ctx.PrescriptionGlasses);

            //ctx.Delete(ctx.Refractometries);
            //ctx.Delete(ctx.Biometries);
            //ctx.Delete(ctx.Paquimetries);
            //ctx.Delete(ctx.Topographies);

            //ctx.Delete(ctx.ExaminationAssigneds);
            //ctx.SaveChanges();

            // (1) Dar de alta los diferentes diagnósticos
            string sql = "SELECT * FROM HistExplor";
            cmd = new OleDbCommand(sql, con);
            da = new OleDbDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds, "ConExploraciones");
            int nreg = ds.Tables["ConExploraciones"].Rows.Count;
            int reg = 0;
            foreach (DataRow dr in ds.Tables["ConExploraciones"].Rows)
            {
                reg++;
                Boolean newEx = false;
                Console.WriteLine("Exploraciones asignadas {0:#####0} de {1:#####0} {2}", reg, nreg, "EXPASG");
                int id = (int)dr["NumHis"];
                Patient patient = (from p in ctx.Patients
                                   where p.OftId == id
                                   select p).FirstOrDefault<Patient>();
                id = (int)dr["IdExEs"];
                Examination examination = (from ex in ctx.Examinations
                                           where ex.OftId == id
                                           select ex).FirstOrDefault<Examination>();
                DateTime examinationDate = (DateTime)dr["Fecha"];
                ExaminationAssigned examas = (from e in ctx.ExaminationAssigneds
                                              where e.Patient.PersonId == patient.PersonId
                                              && e.Examination.ExaminationId == examination.ExaminationId
                                              && e.ExaminationDate == examinationDate
                                              select e).FirstOrDefault<ExaminationAssigned>();
                if (examas == null)
                {
                    examas = new ExaminationAssigned();
                    newEx = true;
                }

                examas.Patient = patient;
                examas.Examination = examination;
                examas.ExaminationDate = examinationDate;
                examas.Comments = (string)dr["Hallazgos"];
                if ((int)dr["TipoProc"] == 1)
                {
                    id = (int)dr["ExtProc"];
                    examas.BaseVisit = (from bs in ctx.BaseVisits
                                        where bs.OftRefVisita == id
                                        select bs).FirstOrDefault<BaseVisit>();
                }
                switch (examas.Examination.ExaminationType.Code)
                {
                    case "general":
                        if (newEx) ctx.Add(examas);
                        ctx.SaveChanges();
                        break;
                    case "refractometry":
                        Refractometry refra;    
                        if (newEx)
                            refra = new Refractometry();
                        else
                            refra = (Refractometry)examas;
                        refra.Patient = examas.Patient;
                        refra.Examination = examas.Examination;
                        refra.ExaminationDate = examas.ExaminationDate;
                        refra.BaseVisit = examas.BaseVisit;
                        refra.Comments = examas.Comments;
                        id = (int)dr["ExtExEs"];
                        ProcessRefractometry(id, refra, con, ctx);
                        if (newEx) ctx.Add(refra);
                        ctx.SaveChanges();
                        break;
                    case "paquimetry":
                        Paquimetry paq;
                        if (newEx)
                            paq = new Paquimetry();
                        else
                            paq = (Paquimetry)examas;
                        paq.Patient = examas.Patient;
                        paq.Examination = examas.Examination;
                        paq.ExaminationDate = examas.ExaminationDate;
                        paq.BaseVisit = examas.BaseVisit;
                        paq.Comments = examas.Comments;
                        id = (int)dr["ExtExEs"];
                        ProcessPaquimetry(id, paq, con, ctx);
                        if (newEx) ctx.Add(paq);
                        ctx.SaveChanges();
//.........这里部分代码省略.........

        public static void ImportExaminations(OleDbConnection con, AriClinicContext ctx)
        {
            // (0) Borra tipos previos
            //ctx.Delete(ctx.WithoutGlassesTests);
            //ctx.Delete(ctx.GlassesTests);
            //ctx.Delete(ctx.ContactLensesTests);
            //ctx.Delete(ctx.OpticalObjectiveExaminations);
            //ctx.Delete(ctx.SubjectiveOpticalExaminations);
            //ctx.Delete(ctx.Cycloplegias);
            //ctx.Delete(ctx.PrescriptionGlasses);

            //ctx.Delete(ctx.Refractometries);
            //ctx.Delete(ctx.Biometries);
            //ctx.Delete(ctx.Paquimetries);
            //ctx.Delete(ctx.Topographies);

            //ctx.Delete(ctx.ExaminationAssigneds);
            //ctx.Delete(ctx.Examinations);
            //ctx.SaveChanges();

            // (1) Dar de alta los diferentes diagnósticos
            string sql = "SELECT * FROM Exploraciones";
            cmd = new OleDbCommand(sql, con);
            da = new OleDbDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds, "ConExploraciones");
            int nreg = ds.Tables["ConExploraciones"].Rows.Count;
            int reg = 0;
            foreach (DataRow dr in ds.Tables["ConExploraciones"].Rows)
            {
                DataRow localDr = dr;
                reg++;
                Console.WriteLine("Exploraciones {0:#####0} de {1:#####0} {2}", reg, nreg, (string)localDr["NomExEs"]);
                Examination exam = (from e in ctx.Examinations
                                    where e.OftId == (int)localDr["IdExEs"]
                                    select e).FirstOrDefault<Examination>();
                if (exam == null)
                {
                    exam = new Examination();
                    ctx.Add(exam);
                }
                exam.OftId = (int)localDr["IdExEs"];
                exam.Name = (string)localDr["NomExEs"];
                int tp = (int)localDr["Tipo"];
                exam.ExaminationType = CntAriCli.GetExaminationType("general", ctx);
                switch (tp)
                {
                    case 1:
                        exam.ExaminationType = CntAriCli.GetExaminationType("refractometry", ctx);
                        break;
                    case 2:
                        exam.ExaminationType = CntAriCli.GetExaminationType("biometry", ctx);
                        break;
                    case 3:
                        exam.ExaminationType = CntAriCli.GetExaminationType("paquimetry", ctx);
                        break;
                    case 4:
                        exam.ExaminationType = CntAriCli.GetExaminationType("topography", ctx);
                        break;
                }
                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.
            // 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)
//.........这里部分代码省略.........

        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();
            }
        }