public static void ImportInvoices(OleDbConnection con, AriClinicContext ctx)
{
//(0) Delete previous invoices
ctx.Delete(ctx.InvoiceLines);
ctx.Delete(ctx.Invoices);
//
//(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");
foreach (DataRow dr in ds.Tables["ConFacturas"].Rows)
{
Invoice inv = new Invoice();
inv.InvoiceDate = (DateTime)dr["Fecha"];
inv.Year = (int)dr["Ano"];
inv.InvoiceNumber = (int)dr["NumFactura"];
inv.Serial = "F"; // we must to set serial parameter to "F"
int id = (int)dr["NumHis"];
inv.Customer = (from c in ctx.Customers
where c.OftId == id
select c).FirstOrDefault<Customer>();
inv.Total = (decimal)dr["Total"];
ctx.Add(inv);
}
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");
foreach (DataRow dr in ds.Tables["ConLineasFactura"].Rows)
{
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
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 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 int InvoiceAnesthesicServiceNote(AnestheticServiceNote asn, AriClinicContext ctx)
{
// it there's an invoice related to this service
// we do nothing and return 0
if (asn.Invoice != null)
return 0;
// invoice
Invoice i = new Invoice();
Decimal total = 0;
i.Customer = asn.Customer;
asn.Invoice = i; // this make the relationship
i.InvoiceDate = asn.ServiceNoteDate;
i.Serial = CntAriCli.GetHealthCompany(ctx).InvoiceSerial;
i.Year = i.InvoiceDate.Year;
i.InvoiceNumber = CntAriCli.GetNextInvoiceNumber(i.Serial, i.Year, ctx);
ctx.Add(i);
// invoice lines
foreach (Ticket t in asn.AnestheticTickets)
{
InvoiceLine il = new InvoiceLine();
il.Invoice = i;
il.Ticket = t;
il.Description = t.Description;
il.Amount = t.Amount;
il.TaxType = t.InsuranceService.Service.TaxType;
il.TaxPercentage = il.TaxType.Percentage;
total += il.Amount;
ctx.Add(il);
}
i.Total = total;
// save the work and return id
ctx.SaveChanges();
return i.InvoiceId;
}
