private DataTable GetInvoiceTable() { long telHlp = 0; long faxHlp = 0; string telfmt = "000-000-0000"; string szTel = ""; TimelyDepotContext db01 = new TimelyDepotContext(); DataTable table = null; DataRow row = null; InvoiceList theinvoicelist = null; List<InvoiceList> invoiceList = new List<InvoiceList>(); var qryInvoice = db.CustomersContactAddresses.Join(db.Invoices, ctad => ctad.CustomerId, slod => slod.CustomerId, (ctad, slod) => new { ctad, slod }).OrderBy(cact => cact.slod.InvoiceId); if (qryInvoice.Count() > 0) { foreach (var item in qryInvoice) { if (string.IsNullOrEmpty(item.ctad.Tel)) { szTel = "0"; } else { szTel = item.ctad.Tel; } telHlp = Convert.ToInt64(szTel); szTel = string.Format("{0}", telHlp.ToString(telfmt)); theinvoicelist = new InvoiceList(); theinvoicelist.InvoiceId = item.slod.InvoiceId; theinvoicelist.InvoiceNo = item.slod.InvoiceNo; theinvoicelist.SODate = item.slod.ShipDate; theinvoicelist.CustomerNo = GetCustomerDataInvoice(db01, item.ctad.CustomerId.ToString()); theinvoicelist.CompanyName = item.ctad.CompanyName; theinvoicelist.PurchaseOrderNo = item.slod.PurchaseOrderNo; theinvoicelist.SalesOrderNo = item.slod.SalesOrderNo; theinvoicelist.PaymentAmount = GetInvoiceAmount(db01, item.slod.InvoiceId); invoiceList.Add(theinvoicelist); } } table = new DataTable("InvoiceList"); // Set the header DataColumn col01 = new DataColumn("InvoiceNo", System.Type.GetType("System.String")); DataColumn col02 = new DataColumn("ShipDate", System.Type.GetType("System.String")); DataColumn col03 = new DataColumn("CustomerNo", System.Type.GetType("System.String")); DataColumn col04 = new DataColumn("CompanyName", System.Type.GetType("System.String")); DataColumn col05 = new DataColumn("SalesOrderNo", System.Type.GetType("System.String")); DataColumn col06 = new DataColumn("PurchaseOrderNo", System.Type.GetType("System.String")); DataColumn col07 = new DataColumn("Amount", System.Type.GetType("System.String")); table.Columns.Add(col01); table.Columns.Add(col02); table.Columns.Add(col03); table.Columns.Add(col04); table.Columns.Add(col05); table.Columns.Add(col06); table.Columns.Add(col07); //Set the data row foreach (var item in invoiceList) { row = table.NewRow(); row["InvoiceNo"] = item.InvoiceNo; row["ShipDate"] = item.SODate; row["CustomerNo"] = item.CustomerNo; row["CompanyName"] = item.CompanyName; row["SalesOrderNo"] = item.SalesOrderNo; row["PurchaseOrderNo"] = item.PurchaseOrderNo; row["Amount"] = item.PaymentAmount; table.Rows.Add(row); } return table; }
public PartialViewResult InvoiceList(int? page) { int pageIndex = 0; int pageSize = PageSize; TimelyDepotContext db01 = new TimelyDepotContext(); InvoiceList theinvoicelist = null; List<InvoiceList> invoiceList = new List<InvoiceList>(); var qrySalesOrder = db.CustomersContactAddresses.Join(db.Invoices, ctad => ctad.CustomerId, slod => slod.CustomerId, (ctad, slod) => new { ctad, slod }).OrderBy(cact => cact.slod.SalesOrderId); if (qrySalesOrder.Count() > 0) { foreach (var item in qrySalesOrder) { theinvoicelist = new InvoiceList(); theinvoicelist.InvoiceId = item.slod.InvoiceId; theinvoicelist.InvoiceNo = item.slod.InvoiceNo; theinvoicelist.SODate = item.slod.ShipDate; theinvoicelist.CustomerNo = GetCustomerDataInvoice(db01, item.ctad.CustomerId.ToString()); theinvoicelist.CompanyName = item.ctad.CompanyName; theinvoicelist.PurchaseOrderNo = item.slod.PurchaseOrderNo; theinvoicelist.SalesOrderNo = item.slod.SalesOrderNo; theinvoicelist.PaymentAmount = GetInvoiceAmount(db01, item.slod.InvoiceId); invoiceList.Add(theinvoicelist); } } //Set the page if (page == null) { pageIndex = 1; } else { pageIndex = Convert.ToInt32(page); } var onePageOfData = invoiceList.ToPagedList(pageIndex, pageSize); ViewBag.OnePageOfData = onePageOfData; return PartialView(invoiceList.ToPagedList(pageIndex, pageSize)); }