public static string GetInvoiceSumm_Template(string template, string pathToDatabase, string userID, DateTime printDate1, DateTime printDate2) { string text = ""; try { ModelSumm model = GetInvoiceSummModel(pathToDatabase, userID, printDate1, printDate2); model.ItemsSumm = SummHelper.GetItemsSummary(pathToDatabase, printDate1); VelocityEngine fileEngine = new VelocityEngine(); fileEngine.Init(); string content = GetTemplateFile(template, pathToDatabase); VelocityContext context = new VelocityContext(); StreamWriter ws = new StreamWriter(new MemoryStream()); context.Put("util", new CustomTool()); context.Put("model", model); fileEngine.Evaluate(context, ws, null, content); ws.Flush(); byte[] data = new byte[ws.BaseStream.Length - 2]; ws.BaseStream.Position = 2; int nread = ws.BaseStream.Read(data, 0, data.Length); text = Encoding.UTF8.GetString(data, 0, nread); ws.Close(); } catch (Exception ex) { } return(text); }
private static double GetSumTotal(Invoice[] invs,string trxtype,List<InvoiceDtls> itemlist,Item[] itemCodes,ModelSumm model) { double total = 0; var sum1 = from p in invs where p.trxtype == trxtype group p by 1 into g select new { amt = g.Sum (x => x.amount), tax = g.Sum (x => x.taxamt), grplist = g.ToList() }; if (sum1.Count() >0) total = sum1.FirstOrDefault().amt + sum1.FirstOrDefault().tax; List<string> invnosTmp = new List<string>(); foreach (var grptmp in sum1) { foreach (var lst in grptmp.grplist){ invnosTmp.Add(lst.invno); } } var itemlistCS = from p in itemlist where invnosTmp.Contains(p.invno) select p; List<TaxSumm> taxsummCS = GetSubTaxSumm(itemCodes, itemlistCS.ToList()); if (trxtype=="CASH") model.CSTaxSumm = taxsummCS; else model.InvTaxSumm = taxsummCS; return total; }
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); }
private static double GetCNSumTotal(CNNote[] invs, string trxtype, List <CNNoteDtls> itemlist, Item[] itemCodes, ModelSumm model) { double total = 0; var sum1 = from p in invs where p.trxtype == trxtype group p by 1 into g select new { amt = g.Sum(x => x.amount), tax = g.Sum(x => x.taxamt), grplist = g.ToList() }; if (sum1.Count() > 0) { total = sum1.FirstOrDefault().amt + sum1.FirstOrDefault().tax; } List <string> invnosTmp = new List <string>(); foreach (var grptmp in sum1) { foreach (var lst in grptmp.grplist) { invnosTmp.Add(lst.invno); } } var itemlistCS = from p in itemlist where invnosTmp.Contains(p.cnno) select p; List <TaxSumm> taxsummCS = GetSubCNTaxSumm(itemCodes, itemlistCS.ToList()); if (trxtype == "CASH") { model.CNCSTaxSumm = taxsummCS; } else { model.CNInvTaxSumm = taxsummCS; } return(total); }