public void PrintTaxSumm(ref string test,InvoiceDtls[] list ) { List<Item> list2 = new List<Item> (); using (var db = new SQLite.SQLiteConnection (pathToDatabase)) { list2 = db.Table<Item> ().ToList<Item> (); } var grp = from p in list group p by p.taxgrp into g select new {taxgrp = g.Key, ttltax = g.Sum (x => x.tax),ttlAmt = g.Sum (v => v.netamount)}; test += "------------------------------------------------\r"; test += "SUMMARY TAX GROUP AMOUNT TAX AMT \r"; test += "------------------------------------------------\r"; // 12345678 123456789012345 123456789012 1234567890 string pline=""; foreach (var g in grp) { var list3 =list2.Where (x => x.taxgrp == g.taxgrp).ToList (); if (list3.Count > 0) { string stax = g.taxgrp.Trim () + " @ " + list3 [0].tax.ToString () + "%"; pline = pline + stax.PadRight (15,' '); } else pline = pline + g.taxgrp.Trim().PadRight (15, ' '); pline = pline + g.ttlAmt.ToString("n2").PadLeft(12, ' ')+" "; pline = pline + g.ttltax.ToString("n2").PadLeft(10, ' '); test += "".PadRight(9,' ')+pline + "\r"; pline = ""; } }
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 bool PrintCNInvoice(CNNote cn,ref string test,ref double ttlAmt,ref double ttltax) { bool IsfoundInvoice =false; InvoiceDtls[] list =null; Invoice inv=null; using (var db = new SQLite.SQLiteConnection (pathToDatabase)){ var lsinv= db.Table<Invoice> ().Where (x => x.invno==cn.invno).ToList<Invoice>(); if (lsinv.Count > 0) { IsfoundInvoice =true; inv = lsinv [0]; var ls = db.Table<InvoiceDtls> ().Where (x => x.invno == cn.invno).ToList<InvoiceDtls> (); list = new InvoiceDtls[ls.Count]; ls.CopyTo (list); } } if (inv != null) { PrintInvoice (inv, list, ref test,needFooter:false); foreach(InvoiceDtls itm in list) { ttlAmt = ttlAmt+ itm.netamount; ttltax = ttltax+itm.tax; } } return IsfoundInvoice; }
Invoice[] GetInvoices(DateTime printdate1, DateTime printdate2) { Invoice[] invs = { }; using (var db = new SQLite.SQLiteConnection (pathToDatabase)) { var list = db.Table<Invoice> ().Where (x => x.invdate >= printdate1 && x.invdate <= printdate2).OrderBy (x => x.invdate).ToList<Invoice> (); invs = new Invoice[list.Count]; list.CopyTo (invs); } return invs; }
internal void GetInvTaxInfo(InvoiceDtls[] list, string pathToDatabase, MyModel model) { List<Item> list2 = new List<Item> (); using (var db = new SQLite.SQLiteConnection (pathToDatabase)) { list2 = db.Table<Item> ().ToList<Item> (); } var grp = from p in list group p by p.taxgrp into g select new { taxgrp = g.Key, ttltax = g.Sum (x => x.tax), ttlAmt = g.Sum (v => v.netamount) }; foreach (var g in grp) { TaxInfo taxinfo = new TaxInfo(); taxinfo.Tax = g.taxgrp.Trim(); taxinfo.TaxPer =0; var list3 = list2.Where(x => x.taxgrp == g.taxgrp).ToList(); if (list3.Count > 0) { taxinfo.TaxPer = list3[0].tax; } taxinfo.TaxAmt = g.ttltax; taxinfo.Amount = g.ttlAmt; model.TaxSumm.Add(taxinfo); } }