protected void LoadTicketData(Ticket tck)
{
taxt = tck.InsuranceService.Service.TaxType;
LoadTaxTypeCombo(taxt);
txtDescription.Text = tck.Description;
txtTaxPercentage.Text = taxt.Percentage.ToString();
txtAmount.Text = tck.Amount.ToString();
}
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 bool PaymentControl(Ticket tck, Payment pay, decimal amount)
{
decimal thisAmount = 0;
if (pay != null)
thisAmount = pay.Amount;
if (((tck.Paid - thisAmount) + amount) > tck.Amount)
return false;
else
return true;
}
public static void UpdateTicketPayments(Ticket tck, AriClinicContext ctx)
{
decimal total = 0;
foreach (Payment pay in tck.Payments)
{
total += pay.Amount;
}
tck.Paid = total;
ctx.SaveChanges();
}
public static void CheckConversionRequest(Ticket tk, AriClinicContext ctx)
{
if (tk.Policy.Customer.Patients.Count > 0)
{
// obtain related patient
Patient p = tk.Policy.Customer.Patients[0];
// look for a request with the same customer and service
Request req = (from r in p.Requests
where r.Status != "CONVERTIDA"
&& r.Service.ServiceId == tk.InsuranceService.Service.ServiceId
select r).FirstOrDefault<Request>();
if (req != null)
{
req.Status = "CONVERTIDA";
ctx.SaveChanges();
}
}
}
public static void CreatePayment(Ticket t, PaymentMethod pm, Decimal amount, DateTime dt, string des, ServiceNote note, Clinic cl, GeneralPayment gp, AriClinicContext ctx)
{
// Now we need verify if there's a payment yet with the same values
Payment p = new Payment();
p.Amount = amount;
p.Clinic = cl;
p.PaymentDate = dt;
p.PaymentMethod = pm;
p.GeneralPayment = gp;
p.Description = des;
p.Ticket = t;
t.Paid = t.Paid + amount;
ctx.Add(p);
}
