public static ModelSumm GetInvoiceSummModel(string pathToDatabase,string userID, DateTime printDate1,DateTime printDate2)
{
ModelSumm model = new ModelSumm ();
model.DailyTrx = new List<ModelSummDate> ();
model.GrpPrices = new List<ModelGrpPrice> ();
var itemCodes = DataHelper.GetItems ();
var invs = DataHelper.GetInvoices (printDate1, printDate2);
var cns = DataHelper.GetCNNote (printDate1, printDate2);
List<string> invnos = new List<string> ();
foreach (var itm in invs) {
invnos.Add (itm.invno);
}
List<string> cnnosTmp = new List<string> ();
foreach (var cnitm in cns) {
cnnosTmp.Add (cnitm.cnno);
}
using (var db = new SQLite.SQLiteConnection (pathToDatabase, true)) {
var itemlist = from p in db.Table<InvoiceDtls> ()
where invnos.Contains (p.invno)
select p;
var cnitemlist = from p in db.Table<CNNoteDtls> ()
where cnnosTmp.Contains (p.cnno)
select p;
model.PrintDate = DateTime.Now;
model.UserID = "MOK";
model.Company = db.Table<CompanyInfo> ().Take (1).FirstOrDefault ();
model.TotalCash = GetSumTotal (invs, "CASH", itemlist.ToList (), itemCodes, model);
model.TotalInv = GetSumTotal (invs, "INVOICE", itemlist.ToList (), itemCodes, model);
model.TotalCNInv = GetCNSumTotal (cns, "INVOICE", cnitemlist.ToList (), itemCodes, model);
model.TotalCNCash = GetCNSumTotal (cns, "CASH", cnitemlist.ToList (), itemCodes, model);
var grpitm = from code in itemlist
group code by code.icode
into g
select new { key = g.Key, idesc=g.Max(x=>x.description),results = g };
foreach (var grpicode in grpitm)
{
var grpprice =from icode in grpicode.results
group icode by icode.price into g
select new { key = g.Key,
tax=g.Sum(x=>x.tax),
amount=g.Sum(x=>x.netamount),
qty = g.Sum(x => x.qty),
results = g };
ModelGrpPrice mprice = new ModelGrpPrice();
mprice.ICode = grpicode.key;
mprice.IDesc = grpicode.idesc;
mprice.PriceList = new List<GrpPriceList>();
foreach (var itm in grpprice) {
GrpPriceList gprice = new GrpPriceList();
gprice.Amount = itm.amount;
gprice.Price = itm.key;
gprice.Qty = itm.qty;
gprice.TaxAmt = itm.tax;
mprice.PriceList.Add(gprice);
}
model.GrpPrices.Add(mprice);
}
// var grp = from inv in invs
// group inv by inv.invdate into g
// select new { key = g.Key, results = g };
ModelSummDate summ = new ModelSummDate ();
summ.Date = printDate1;
summ.CashList = new List<Invoice> ();
summ.InvList = new List<Invoice> ();
var typgrp = from ty in invs
group ty by ty.trxtype into tg
select new { key = tg.Key, results = tg };
foreach (var g1 in typgrp) {
if (g1.key == "CASH")
summ.CashList = g1.results.OrderBy (x => x.invno).ToList ();
else
summ.InvList = g1.results.OrderBy (x => x.invno).ToList ();
}
var typgrp2 = from ty in cns
group ty by ty.trxtype into tg
select new { key = tg.Key, results = tg };
foreach (var g1 in typgrp2) {
if (g1.key == "CASH")
summ.CashCNList = g1.results.OrderBy (x => x.cnno).ToList ();
else
summ.InvCNList = g1.results.OrderBy (x => x.cnno).ToList ();
}
model.DailyTrx.Add (summ);
model.TtlTaxSumm = new List<TaxSumm> ();
model.TtlTaxSumm = GeTotalTaxSumm(model.TtlTaxSumm, model.InvTaxSumm);
model.TtlTaxSumm = GeTotalTaxSumm(model.TtlTaxSumm, model.CSTaxSumm);
model.TtlTaxSumm = GeTotalTaxSumm(model.TtlTaxSumm, model.CNInvTaxSumm);
model.TtlTaxSumm = GeTotalTaxSumm(model.TtlTaxSumm, model.CNCSTaxSumm);
}
return model;
}
public static ModelSumm GetInvoiceSummModel(string pathToDatabase, string userID, DateTime printDate1, DateTime printDate2)
{
ModelSumm model = new ModelSumm();
model.DailyTrx = new List <ModelSummDate> ();
model.GrpPrices = new List <ModelGrpPrice> ();
var itemCodes = DataHelper.GetItems();
var invs = DataHelper.GetInvoices(printDate1, printDate2);
var cns = DataHelper.GetCNNote(printDate1, printDate2);
List <string> invnos = new List <string> ();
foreach (var itm in invs)
{
invnos.Add(itm.invno);
}
List <string> cnnosTmp = new List <string> ();
foreach (var cnitm in cns)
{
cnnosTmp.Add(cnitm.cnno);
}
using (var db = new SQLite.SQLiteConnection(pathToDatabase, true)) {
var itemlist = from p in db.Table <InvoiceDtls> ()
where invnos.Contains(p.invno)
select p;
var cnitemlist = from p in db.Table <CNNoteDtls> ()
where cnnosTmp.Contains(p.cnno)
select p;
model.PrintDate = DateTime.Now;
model.UserID = "MOK";
model.Company = db.Table <CompanyInfo> ().Take(1).FirstOrDefault();
model.TotalCash = GetSumTotal(invs, "CASH", itemlist.ToList(), itemCodes, model);
model.TotalInv = GetSumTotal(invs, "INVOICE", itemlist.ToList(), itemCodes, model);
model.TotalCNInv = GetCNSumTotal(cns, "INVOICE", cnitemlist.ToList(), itemCodes, model);
model.TotalCNCash = GetCNSumTotal(cns, "CASH", cnitemlist.ToList(), itemCodes, model);
var grpitm = from code in itemlist
group code by code.icode
into g
select new { key = g.Key, idesc = g.Max(x => x.description), results = g };
foreach (var grpicode in grpitm)
{
var grpprice = from icode in grpicode.results
group icode by icode.price into g
select new { key = g.Key,
tax = g.Sum(x => x.tax),
amount = g.Sum(x => x.netamount),
qty = g.Sum(x => x.qty),
results = g };
ModelGrpPrice mprice = new ModelGrpPrice();
mprice.ICode = grpicode.key;
mprice.IDesc = grpicode.idesc;
mprice.PriceList = new List <GrpPriceList>();
foreach (var itm in grpprice)
{
GrpPriceList gprice = new GrpPriceList();
gprice.Amount = itm.amount;
gprice.Price = itm.key;
gprice.Qty = itm.qty;
gprice.TaxAmt = itm.tax;
mprice.PriceList.Add(gprice);
}
model.GrpPrices.Add(mprice);
}
// var grp = from inv in invs
// group inv by inv.invdate into g
// select new { key = g.Key, results = g };
ModelSummDate summ = new ModelSummDate();
summ.Date = printDate1;
summ.CashList = new List <Invoice> ();
summ.InvList = new List <Invoice> ();
var typgrp = from ty in invs
group ty by ty.trxtype into tg
select new { key = tg.Key, results = tg };
foreach (var g1 in typgrp)
{
if (g1.key == "CASH")
{
summ.CashList = g1.results.OrderBy(x => x.invno).ToList();
}
else
{
summ.InvList = g1.results.OrderBy(x => x.invno).ToList();
}
}
var typgrp2 = from ty in cns
group ty by ty.trxtype into tg
select new { key = tg.Key, results = tg };
foreach (var g1 in typgrp2)
{
if (g1.key == "CASH")
{
summ.CashCNList = g1.results.OrderBy(x => x.cnno).ToList();
}
else
{
summ.InvCNList = g1.results.OrderBy(x => x.cnno).ToList();
}
}
model.DailyTrx.Add(summ);
model.TtlTaxSumm = new List <TaxSumm> ();
model.TtlTaxSumm = GeTotalTaxSumm(model.TtlTaxSumm, model.InvTaxSumm);
model.TtlTaxSumm = GeTotalTaxSumm(model.TtlTaxSumm, model.CSTaxSumm);
model.TtlTaxSumm = GeTotalTaxSumm(model.TtlTaxSumm, model.CNInvTaxSumm);
model.TtlTaxSumm = GeTotalTaxSumm(model.TtlTaxSumm, model.CNCSTaxSumm);
}
return(model);
}