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