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 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 bool DeleteInvoice(Invoice inv, AriClinicContext ctx)
{
// Last invoice number?
int n = GetNextInvoiceNumber(inv.Serial, inv.Year, ctx) - 1;
// only the last invoice can be deleted
if (inv.InvoiceNumber != n)
return false;
// erase possible service note relation
if (inv.ServiceNotes != null && inv.ServiceNotes.Count > 0)
{
for (int i = 0; i < inv.ServiceNotes.Count; i++)
{
inv.ServiceNotes.ElementAt(i).Invoice = null;
}
}
else if (inv.AnestheticServiceNotes != null && inv.AnestheticServiceNotes.Count > 0)
{
for (int i = 0; i < inv.AnestheticServiceNotes.Count; i++)
{
inv.AnestheticServiceNotes.ElementAt(i).Invoice = null;
}
}
// delete lines
ctx.Delete(inv.InvoiceLines);
// delete invoice
ctx.Delete(inv);
return true;
}
public static Decimal GetInvoiceTotal(Invoice inv)
{
Decimal total = 0;
foreach (InvoiceLine invl in inv.InvoiceLines)
{
total += invl.Amount;
}
return total;
}
public static AmendmentInvoice CreateAmendmentInvoice(Invoice invoice, DateTime vdate, string reason, AriClinicContext ctx)
{
// first of all check if there's another amendment invoice
// linked to this invoice.
AmendmentInvoice aInv = (from a in ctx.AmendmentInvoices
where a.OriginalInvoice.InvoiceId == invoice.InvoiceId
select a).FirstOrDefault<AmendmentInvoice>();
if (aInv != null)
{
return null;
}
// collecting some stuff we'll need.
HealthcareCompany hc = CntAriCli.GetHealthCompany(ctx);
// now let's go to create it
aInv = new AmendmentInvoice();
aInv.Customer = invoice.Customer;
aInv.InvoiceDate = vdate;
aInv.Serial = hc.AmendmentInvoiceSerial;
aInv.Year = aInv.InvoiceDate.Year;
aInv.InvoiceNumber = CntAriCli.GetNextAmendmentInvoiceNumber(aInv.Serial, aInv.Year, ctx);
aInv.OriginalInvoice = invoice;
aInv.Reason = reason;
ctx.Add(aInv);
ctx.SaveChanges();
// related lines
decimal total = 0;
foreach (InvoiceLine il in invoice.InvoiceLines)
{
AmendmentInvoiceLine ail = new AmendmentInvoiceLine();
ail.AmendmentInvoice = aInv;
ail.TaxType = il.TaxType;
ail.TaxPercentage = il.TaxPercentage;
ail.Description = il.Description;
ail.Amount = -il.Amount;
total += ail.Amount;
ctx.Add(ail);
ctx.SaveChanges();
}
aInv.Total = total;
ctx.SaveChanges();
return aInv;
}
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;
}
