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);
}
}
