public ActionResult ReceivedPurchaseOrderbyVendor(int? page, string id)
{
int pageIndex = 0;
int pageSize = PageSize;
string szMsg = "";
string szVendorId = "";
DateTime dDate = DateTime.Now;
Vendors vendor = db.Vendors.Where(vd => vd.VendorNo == id).FirstOrDefault<Vendors>();
if (vendor != null)
{
szVendorId = vendor.Id.ToString();
}
string szUrl = string.Format("~/Vendors/Edit/{0}", szVendorId);
szUrl = Url.Content(szUrl);
ViewBag.Quit = string.Format("{0}://{1}{2}", Request.Url.Scheme, Request.Url.Authority, szUrl);
PurchaseOrdersbyVendor purchaseorderbyvendor = null;
List<PurchaseOrdersbyVendor> purchaseorderList = new List<PurchaseOrdersbyVendor>();
//qryPOs = db.PurchaseOrders.Where(vd => vd.VendorId == id ).OrderByDescending(vd => vd.PurchaseOrderNo);
var qryPOs = db.PurchasOrderDetails.Join(db.PurchaseOrders, podet => podet.PurchaseOrderId, po => po.PurchaseOrderId, (podet, po)
=> new { podet, po }).Where(prod => prod.po.VendorId == id && prod.podet.Sub_ItemID != null && prod.po.ReceiveStatus != null).OrderByDescending(prod => prod.po.PurchaseOrderId);
if (qryPOs.Count() > 0)
{
foreach (var item in qryPOs)
{
purchaseorderbyvendor = new PurchaseOrdersbyVendor();
if (!string.IsNullOrEmpty(item.po.ReceiveStatus))
{
szMsg = item.po.ReceiveStatus;
szMsg = szMsg.Replace("Received on ", "");
szMsg = szMsg.Replace(".", "");
dDate = Convert.ToDateTime(szMsg);
purchaseorderbyvendor.SODate = dDate;
}
else
{
szMsg = string.Empty;
purchaseorderbyvendor.SODate = null;
}
purchaseorderbyvendor.PurchaseOrderId = item.po.PurchaseOrderId;
purchaseorderbyvendor.PurchaseOrderNo = item.po.PurchaseOrderNo;
purchaseorderbyvendor.Sub_ItemID = item.podet.Sub_ItemID;
purchaseorderbyvendor.Description = item.podet.Description;
purchaseorderbyvendor.Quantity = item.podet.Quantity;
purchaseorderbyvendor.UnitPrice = item.podet.UnitPrice;
purchaseorderList.Add(purchaseorderbyvendor);
}
}
ViewBag.VendorNo = id;
//Set the page
if (page == null)
{
pageIndex = 1;
}
else
{
pageIndex = Convert.ToInt32(page);
}
ViewBag.ReceivedPO = true;
var onePageOfData = purchaseorderList.ToPagedList(pageIndex, pageSize);
ViewBag.OnePageOfData = onePageOfData;
return View("OpenPurchaseOrderbyVendor", purchaseorderList.ToPagedList(pageIndex, pageSize));
}
private DataTable GetReceivedPurchaseOrderTable(string szVendorNo)
{
long telHlp = 0;
long faxHlp = 0;
string telfmt = "000-000-0000";
string szTel = "";
string szMsg = "";
DateTime dDate = DateTime.Now;
DataTable table = null;
DataRow row = null;
PurchaseOrdersbyVendor purchaseorderbyvendor = null;
List<PurchaseOrdersbyVendor> purchaseorderList = new List<PurchaseOrdersbyVendor>();
//qryPOs = db.PurchaseOrders.Where(vd => vd.VendorId == id ).OrderByDescending(vd => vd.PurchaseOrderNo);
var qryPOs = db.PurchasOrderDetails.Join(db.PurchaseOrders, podet => podet.PurchaseOrderId, po => po.PurchaseOrderId, (podet, po)
=> new { podet, po }).Where(prod => prod.po.VendorId == szVendorNo && prod.podet.Sub_ItemID != null && prod.po.ReceiveStatus != null).OrderByDescending(prod => prod.po.PurchaseOrderId);
if (qryPOs.Count() > 0)
{
foreach (var item in qryPOs)
{
purchaseorderbyvendor = new PurchaseOrdersbyVendor();
if (!string.IsNullOrEmpty(item.po.ReceiveStatus))
{
szMsg = item.po.ReceiveStatus;
szMsg = szMsg.Replace("Received on ", "");
szMsg = szMsg.Replace(".", "");
dDate = Convert.ToDateTime(szMsg);
purchaseorderbyvendor.SODate = dDate;
}
else
{
szMsg = string.Empty;
purchaseorderbyvendor.SODate = null;
}
purchaseorderbyvendor.PurchaseOrderId = item.po.PurchaseOrderId;
purchaseorderbyvendor.PurchaseOrderNo = item.po.PurchaseOrderNo;
//purchaseorderbyvendor.SODate = item.po.PODate;
purchaseorderbyvendor.Sub_ItemID = item.podet.Sub_ItemID;
purchaseorderbyvendor.Description = item.podet.Description;
purchaseorderbyvendor.Quantity = item.podet.Quantity;
purchaseorderbyvendor.UnitPrice = item.podet.UnitPrice;
purchaseorderList.Add(purchaseorderbyvendor);
}
}
table = new DataTable("OReceivedPurchaseOrder");
// Set the header
DataColumn col01 = new DataColumn("PurchaseOrderNo", System.Type.GetType("System.String"));
DataColumn col02 = new DataColumn("Date", System.Type.GetType("System.String"));
DataColumn col03 = new DataColumn("ItemID", System.Type.GetType("System.String"));
DataColumn col04 = new DataColumn("Description", System.Type.GetType("System.String"));
DataColumn col05 = new DataColumn("Quantity", System.Type.GetType("System.String"));
DataColumn col06 = new DataColumn("UnitPrice", 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);
//Set the data row
foreach (var item in purchaseorderList)
{
row = table.NewRow();
row["PurchaseOrderNo"] = item.PurchaseOrderNo;
row["Date"] = item.SODate;
row["ItemID"] = item.Sub_ItemID;
row["Description"] = item.Description;
row["Quantity"] = item.Quantity;
row["UnitPrice"] = item.UnitPrice;
table.Rows.Add(row);
}
return table;
}
public PartialViewResult SalesHistorybyItem(int? page, string id)
{
int pageIndex = 0;
int pageSize = PageSize;
string szMsg = "";
PurchaseOrdersbyVendor purchaseorderbyvendor = null;
List<PurchaseOrdersbyVendor> purchaseorderList = new List<PurchaseOrdersbyVendor>();
TimelyDepotContext db01 = new TimelyDepotContext();
//qryPOs = db.PurchaseOrders.Where(vd => vd.VendorId == id ).OrderByDescending(vd => vd.PurchaseOrderNo);
var qryPOs = db.InvoiceDetails.Join(db.Invoices, podet => podet.InvoiceId, po => po.InvoiceId, (podet, po)
=> new { podet, po }).Where(prod => prod.podet.ItemID == id && prod.podet.Sub_ItemID != null).OrderByDescending(prod => prod.po.SalesOrderId);
if (qryPOs.Count() > 0)
{
foreach (var item in qryPOs)
{
szMsg = item.po.PurchaseOrderNo;
purchaseorderbyvendor = new PurchaseOrdersbyVendor();
purchaseorderbyvendor.PurchaseOrderId = item.po.InvoiceId;
purchaseorderbyvendor.PurchaseOrderNo = item.po.InvoiceNo;
purchaseorderbyvendor.SODate = item.po.InvoiceDate;
purchaseorderbyvendor.VendorNo = GetCustomerNo(db01, item.po.CustomerId);
purchaseorderbyvendor.Sub_ItemID = item.podet.Sub_ItemID;
purchaseorderbyvendor.Description = item.podet.Description;
purchaseorderbyvendor.Quantity = item.podet.Quantity;
purchaseorderbyvendor.UnitPrice = item.podet.UnitPrice;
purchaseorderList.Add(purchaseorderbyvendor);
}
}
ViewBag.ItemId = id;
//Set the page
if (page == null)
{
pageIndex = 1;
}
else
{
pageIndex = Convert.ToInt32(page);
}
var onePageOfData = purchaseorderList.ToPagedList(pageIndex, pageSize);
ViewBag.OnePageOfData = onePageOfData;
return PartialView(purchaseorderList.ToPagedList(pageIndex, pageSize));
}
private DataTable GetSalesHistoryrTable(string szItemId)
{
long telHlp = 0;
long faxHlp = 0;
string telfmt = "000-000-0000";
string szTel = "";
string szMsg = "";
TimelyDepotContext db01 = new TimelyDepotContext();
DataTable table = null;
DataRow row = null;
PurchaseOrdersbyVendor purchaseorderbyvendor = null;
List<PurchaseOrdersbyVendor> purchaseorderList = new List<PurchaseOrdersbyVendor>();
//qryPOs = db.PurchaseOrders.Where(vd => vd.VendorId == id ).OrderByDescending(vd => vd.PurchaseOrderNo);
var qryPOs = db.InvoiceDetails.Join(db.Invoices, podet => podet.InvoiceId, po => po.InvoiceId, (podet, po)
=> new { podet, po }).Where(prod => prod.podet.ItemID == szItemId && prod.podet.Sub_ItemID != null).OrderByDescending(prod => prod.po.SalesOrderId);
if (qryPOs.Count() > 0)
{
foreach (var item in qryPOs)
{
szMsg = item.po.PurchaseOrderNo;
purchaseorderbyvendor = new PurchaseOrdersbyVendor();
purchaseorderbyvendor.PurchaseOrderId = item.po.InvoiceId;
purchaseorderbyvendor.PurchaseOrderNo = item.po.InvoiceNo;
purchaseorderbyvendor.SODate = item.po.InvoiceDate;
purchaseorderbyvendor.VendorNo = GetCustomerNo(db01, item.po.CustomerId);
purchaseorderbyvendor.Sub_ItemID = item.podet.Sub_ItemID;
purchaseorderbyvendor.Description = item.podet.Description;
purchaseorderbyvendor.Quantity = item.podet.Quantity;
purchaseorderbyvendor.UnitPrice = item.podet.UnitPrice;
purchaseorderList.Add(purchaseorderbyvendor);
}
}
table = new DataTable("SalesHistory");
// Set the header
DataColumn col01 = new DataColumn("InvoiceNo", System.Type.GetType("System.String"));
DataColumn col02 = new DataColumn("Date", System.Type.GetType("System.String"));
DataColumn col03 = new DataColumn("CustomerNo", System.Type.GetType("System.String"));
DataColumn col04 = new DataColumn("ItemID", System.Type.GetType("System.String"));
DataColumn col05 = new DataColumn("Description", System.Type.GetType("System.String"));
DataColumn col06 = new DataColumn("Quantity", System.Type.GetType("System.String"));
DataColumn col07 = new DataColumn("UnitPrice", 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 purchaseorderList)
{
row = table.NewRow();
row["InvoiceNo"] = item.PurchaseOrderNo;
row["Date"] = item.SODate;
row["CustomerNo"] = item.VendorNo;
row["ItemID"] = item.Sub_ItemID;
row["Description"] = item.Description;
row["Quantity"] = item.Quantity;
row["UnitPrice"] = item.UnitPrice;
table.Rows.Add(row);
}
return table;
}
private DataTable GetOutstandingSalesOrderTable(string customerid)
{
long telHlp = 0;
long faxHlp = 0;
int nCustomerId = 0;
string telfmt = "000-000-0000";
string szTel = string.Empty;
string szMsg = string.Empty;
TimelyDepotContext db01 = new TimelyDepotContext();
DataTable table = null;
DataRow row = null;
PurchaseOrdersbyVendor purchaseorderbyvendor = null;
List<PurchaseOrdersbyVendor> purchaseorderList = new List<PurchaseOrdersbyVendor>();
//qryPOs = db.PurchaseOrders.Where(vd => vd.VendorId == id ).OrderByDescending(vd => vd.PurchaseOrderNo);
if (!string.IsNullOrEmpty(customerid))
{
nCustomerId = Convert.ToInt32(customerid);
}
var qryPOs = db.SalesOrderDetails.Join(db.SalesOrders, podet => podet.SalesOrderId, po => po.SalesOrderId, (podet, po)
=> new { podet, po }).Where(prod => prod.po.CustomerId == nCustomerId && prod.podet.Quantity != prod.podet.ShipQuantity).OrderByDescending(prod => prod.po.SalesOrderId).ThenBy(prod => prod.podet.ItemPosition).ThenBy(prod => prod.podet.ItemOrder);
if (qryPOs.Count() > 0)
{
foreach (var item in qryPOs)
{
szMsg = item.po.PurchaseOrderNo;
purchaseorderbyvendor = new PurchaseOrdersbyVendor();
purchaseorderbyvendor.PurchaseOrderId = item.po.SalesOrderId;
purchaseorderbyvendor.PurchaseOrderNo = item.po.SalesOrderNo;
purchaseorderbyvendor.SODate = item.po.SODate;
//purchaseorderbyvendor.VendorNo = GetCustomerNo(db01, item.po.CustomerId);
purchaseorderbyvendor.Sub_ItemID = item.podet.Sub_ItemID;
purchaseorderbyvendor.Description = item.podet.Description;
purchaseorderbyvendor.Quantity = item.podet.Quantity;
purchaseorderbyvendor.UnitPrice = item.podet.UnitPrice;
purchaseorderList.Add(purchaseorderbyvendor);
}
}
table = new DataTable("OpenPurchaseOrder");
// Set the header
DataColumn col01 = new DataColumn("SalesOrderNo", System.Type.GetType("System.String"));
DataColumn col02 = new DataColumn("Date", System.Type.GetType("System.String"));
//DataColumn col03 = new DataColumn("CustomerNo", System.Type.GetType("System.String"));
DataColumn col04 = new DataColumn("ItemID", System.Type.GetType("System.String"));
DataColumn col05 = new DataColumn("Description", System.Type.GetType("System.String"));
DataColumn col06 = new DataColumn("Quantity", System.Type.GetType("System.String"));
DataColumn col07 = new DataColumn("UnitPrice", 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 purchaseorderList)
{
row = table.NewRow();
row["SalesOrderNo"] = item.PurchaseOrderNo;
row["Date"] = item.SODate;
//row["CustomerNo"] = item.VendorNo;
row["ItemID"] = item.Sub_ItemID;
row["Description"] = item.Description;
row["Quantity"] = item.Quantity;
row["UnitPrice"] = item.UnitPrice;
table.Rows.Add(row);
}
return table;
}
private DataTable GetPaymentListTable(string customerid)
{
bool bPaidinFull = false;
int nInvoiceIdHlp = 0;
double dAmount = 0;
double dSalesAmount = 0;
double dTotalTax = 0;
double dTax = 0;
double dTotalAmount = 0;
double dBalanceDue = 0;
long telHlp = 0;
long faxHlp = 0;
int nCustomerId = 0;
string telfmt = "000-000-0000";
string szTel = string.Empty;
string szMsg = string.Empty;
string szCustomerNo = string.Empty;
string szInvoiceNo = string.Empty;
int nHas = 0;
int nPos = -1;
string szClass = string.Empty;
string szError = string.Empty;
string szDecriptedData = string.Empty;
string szDecriptedCode = string.Empty;
TimelyDepotContext db01 = new TimelyDepotContext();
DataTable table = null;
DataRow row = null;
Invoice invoice = null;
Customers customer = null;
PurchaseOrdersbyVendor purchaseorderbyvendor = null;
List<PurchaseOrdersbyVendor> purchaseorderList = new List<PurchaseOrdersbyVendor>();
//qryPOs = db.PurchaseOrders.Where(vd => vd.VendorId == id ).OrderByDescending(vd => vd.PurchaseOrderNo);
if (!string.IsNullOrEmpty(customerid))
{
nCustomerId = Convert.ToInt32(customerid);
}
if (!string.IsNullOrEmpty(customerid))
{
nCustomerId = Convert.ToInt32(customerid);
customer = db.Customers.Find(nCustomerId);
if (customer != null)
{
szCustomerNo = customer.CustomerNo;
}
}
var qryPOs = db.Payments.Where(pymt => pymt.CustomerNo == szCustomerNo).OrderByDescending(pymt => pymt.PaymentDate);
if (qryPOs.Count() > 0)
{
foreach (var item in qryPOs)
{
szError = string.Empty;
szDecriptedData = TimelyDepotMVC.Controllers.PaymentController.DecodeInfo02(item.PaymentType, ref szError);
if (!string.IsNullOrEmpty(szError))
{
nPos = szError.IndexOf("data to decode");
if (nPos != -1)
{
szDecriptedData = string.Empty;
}
else
{
szDecriptedData = string.Format("******");
}
}
else
{
//Mask the card number
nHas = szDecriptedData.Length;
if (nHas > 4)
{
szMsg = szDecriptedData.Substring(nHas - 4, 4);
szDecriptedData = string.Format("******{0}", szMsg);
}
else
{
szDecriptedData = string.Format("******");
}
}
if (!string.IsNullOrEmpty(item.SalesOrderNo))
{
invoice = db.Invoices.Where(invc => invc.SalesOrderNo == item.SalesOrderNo).FirstOrDefault<Invoice>();
if (invoice != null)
{
szInvoiceNo = invoice.InvoiceNo;
}
}
else
{
szInvoiceNo = string.Empty;
}
purchaseorderbyvendor = new PurchaseOrdersbyVendor();
purchaseorderbyvendor.PurchaseOrderId = item.Id;
purchaseorderbyvendor.PurchaseOrderNo = item.PaymentNo;
purchaseorderbyvendor.VendorNo = szInvoiceNo;
purchaseorderbyvendor.SODate = item.PaymentDate;
purchaseorderbyvendor.Description = item.PaymentType;
purchaseorderbyvendor.UnitPrice = item.Amount;
purchaseorderList.Add(purchaseorderbyvendor);
}
}
table = new DataTable("OpenPurchaseOrder");
// Set the header
DataColumn col01 = new DataColumn("PaymentNo", System.Type.GetType("System.String"));
DataColumn col02 = new DataColumn("Date", System.Type.GetType("System.String"));
//DataColumn col03 = new DataColumn("CustomerNo", System.Type.GetType("System.String"));
DataColumn col04 = new DataColumn("InvoiceNo", System.Type.GetType("System.String"));
DataColumn col05 = new DataColumn("Description", 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 purchaseorderList)
{
row = table.NewRow();
row["PaymentNo"] = item.PurchaseOrderNo;
row["Date"] = item.SODate;
//row["CustomerNo"] = item.VendorNo;
row["InvoiceNo"] = item.VendorNo;
row["Description"] = item.Description;
//row["Quantity"] = item.Quantity;
row["Amount"] = item.UnitPrice;
table.Rows.Add(row);
}
return table;
}
private DataTable GetOutstandingInvoiceTable(string customerid)
{
bool bPaidinFull = false;
int nInvoiceIdHlp = 0;
double dAmount = 0;
double dSalesAmount = 0;
double dTotalTax = 0;
double dTax = 0;
double dTotalAmount = 0;
double dBalanceDue = 0;
long telHlp = 0;
long faxHlp = 0;
int nCustomerId = 0;
string telfmt = "000-000-0000";
string szTel = string.Empty;
string szMsg = string.Empty;
TimelyDepotContext db01 = new TimelyDepotContext();
DataTable table = null;
DataRow row = null;
PurchaseOrdersbyVendor purchaseorderbyvendor = null;
List<PurchaseOrdersbyVendor> purchaseorderList = new List<PurchaseOrdersbyVendor>();
//qryPOs = db.PurchaseOrders.Where(vd => vd.VendorId == id ).OrderByDescending(vd => vd.PurchaseOrderNo);
if (!string.IsNullOrEmpty(customerid))
{
nCustomerId = Convert.ToInt32(customerid);
}
var qryPOs = db.Invoices.Where(invc => invc.CustomerId == nCustomerId).OrderByDescending(invc => invc.InvoiceId);
if (qryPOs.Count() > 0)
{
foreach (var item in qryPOs)
{
//Display here all the invoice for this customer
if (item.InvoiceId != nInvoiceIdHlp)
{
//Get the totals
GetInvoiceTotals01(db01, item.InvoiceId, ref dSalesAmount, ref dTotalTax, ref dTax, ref dTotalAmount, ref dBalanceDue);
nInvoiceIdHlp = item.InvoiceId;
if (item.PaymentAmount == null)
{
dAmount = 0;
}
else
{
dAmount = Convert.ToDouble(item.PaymentAmount);
}
if (dBalanceDue == 0)
{
bPaidinFull = true;
}
else
{
bPaidinFull = false;
}
}
if (bPaidinFull == false)
{
purchaseorderbyvendor = new PurchaseOrdersbyVendor();
purchaseorderbyvendor.PurchaseOrderId = item.InvoiceId;
purchaseorderbyvendor.PurchaseOrderNo = item.InvoiceNo;
purchaseorderbyvendor.SODate = item.InvoiceDate;
purchaseorderbyvendor.VendorNo = dTotalAmount.ToString();
purchaseorderbyvendor.Sub_ItemID = dAmount.ToString(); ;
purchaseorderbyvendor.Description = dBalanceDue.ToString();
//purchaseorderbyvendor.Quantity = item.podet.Quantity;
//purchaseorderbyvendor.UnitPrice = item.podet.UnitPrice;
purchaseorderList.Add(purchaseorderbyvendor);
}
}
}
table = new DataTable("OpenPurchaseOrder");
// Set the header
DataColumn col01 = new DataColumn("InvoiceNo", System.Type.GetType("System.String"));
DataColumn col02 = new DataColumn("Date", System.Type.GetType("System.String"));
//DataColumn col03 = new DataColumn("CustomerNo", System.Type.GetType("System.String"));
DataColumn col04 = new DataColumn("Invoice Amount", System.Type.GetType("System.String"));
DataColumn col05 = new DataColumn("Paid Amount", System.Type.GetType("System.String"));
DataColumn col06 = new DataColumn("Outstanding Amount", System.Type.GetType("System.String"));
//DataColumn col07 = new DataColumn("UnitPrice", 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 purchaseorderList)
{
row = table.NewRow();
row["InvoiceNo"] = item.PurchaseOrderNo;
row["Date"] = item.SODate;
//row["CustomerNo"] = item.VendorNo;
row["Invoice Amount"] = item.VendorNo;
row["Paid Amount"] = item.Sub_ItemID;
row["Outstanding Amount"] = item.Description;
//row["UnitPrice"] = item.UnitPrice;
table.Rows.Add(row);
}
return table;
}
private DataTable GetInvoiceListTable(string customerid)
{
bool bPaidinFull = false;
int nInvoiceIdHlp = 0;
double dAmount = 0;
double dSalesAmount = 0;
double dTotalTax = 0;
double dTax = 0;
double dTotalAmount = 0;
double dBalanceDue = 0;
long telHlp = 0;
long faxHlp = 0;
int nCustomerId = 0;
string telfmt = "000-000-0000";
string szTel = string.Empty;
string szMsg = string.Empty;
TimelyDepotContext db01 = new TimelyDepotContext();
DataTable table = null;
DataRow row = null;
PurchaseOrdersbyVendor purchaseorderbyvendor = null;
List<PurchaseOrdersbyVendor> purchaseorderList = new List<PurchaseOrdersbyVendor>();
//qryPOs = db.PurchaseOrders.Where(vd => vd.VendorId == id ).OrderByDescending(vd => vd.PurchaseOrderNo);
if (!string.IsNullOrEmpty(customerid))
{
nCustomerId = Convert.ToInt32(customerid);
}
var qryPOs = db.InvoiceDetails.Join(db.Invoices, podet => podet.InvoiceId, po => po.InvoiceId, (podet, po)
=> new { podet, po }).Where(prod => prod.po.CustomerId == nCustomerId).OrderByDescending(prod => prod.po.InvoiceId).ThenBy(prod => prod.podet.ItemPosition).ThenBy(prod => prod.podet.ItemOrder);
if (qryPOs.Count() > 0)
{
foreach (var item in qryPOs)
{
szMsg = item.po.PurchaseOrderNo;
if (item.po.InvoiceId != nInvoiceIdHlp)
{
//Get the totals
GetInvoiceTotals01(db01, item.po.InvoiceId, ref dSalesAmount, ref dTotalTax, ref dTax, ref dTotalAmount, ref dBalanceDue);
dAmount = dTotalAmount;
nInvoiceIdHlp = item.po.InvoiceId;
if (dBalanceDue == 0)
{
bPaidinFull = true;
}
else
{
bPaidinFull = false;
}
}
if (bPaidinFull == false)
{
purchaseorderbyvendor = new PurchaseOrdersbyVendor();
purchaseorderbyvendor.PurchaseOrderId = item.po.InvoiceId;
purchaseorderbyvendor.PurchaseOrderNo = item.po.InvoiceNo;
purchaseorderbyvendor.SODate = item.po.InvoiceDate;
//purchaseorderbyvendor.VendorNo = GetCustomerNo(db01, item.po.CustomerId);
purchaseorderbyvendor.Sub_ItemID = item.podet.Sub_ItemID;
purchaseorderbyvendor.Description = item.podet.Description;
purchaseorderbyvendor.Quantity = item.podet.Quantity;
purchaseorderbyvendor.UnitPrice = item.podet.UnitPrice;
purchaseorderList.Add(purchaseorderbyvendor);
}
}
}
table = new DataTable("OpenPurchaseOrder");
// Set the header
DataColumn col01 = new DataColumn("InvoiceNo", System.Type.GetType("System.String"));
DataColumn col02 = new DataColumn("Date", System.Type.GetType("System.String"));
//DataColumn col03 = new DataColumn("CustomerNo", System.Type.GetType("System.String"));
DataColumn col04 = new DataColumn("ItemID", System.Type.GetType("System.String"));
DataColumn col05 = new DataColumn("Description", System.Type.GetType("System.String"));
DataColumn col06 = new DataColumn("Quantity", System.Type.GetType("System.String"));
DataColumn col07 = new DataColumn("UnitPrice", 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 purchaseorderList)
{
row = table.NewRow();
row["InvoiceNo"] = item.PurchaseOrderNo;
row["Date"] = item.SODate;
//row["CustomerNo"] = item.VendorNo;
row["ItemID"] = item.Sub_ItemID;
row["Description"] = item.Description;
row["Quantity"] = item.Quantity;
row["UnitPrice"] = item.UnitPrice;
table.Rows.Add(row);
}
return table;
}
public PartialViewResult PaymentList(int? page, string customerid)
{
bool bPaidinFull = false;
int pageIndex = 0;
int pageSize = PageSize;
int nCustomerId = 0;
int nInvoiceIdHlp = 0;
double dAmount = 0;
double dSalesAmount = 0;
double dTotalTax = 0;
double dTax = 0;
double dTotalAmount = 0;
double dBalanceDue = 0;
string szMsg = string.Empty;
string szCustomerNo = string.Empty;
string szInvoiceNo = string.Empty;
Invoice invoice = null;
Customers customer = null;
PurchaseOrdersbyVendor purchaseorderbyvendor = null;
List<PurchaseOrdersbyVendor> purchaseorderList = new List<PurchaseOrdersbyVendor>();
TimelyDepotContext db01 = new TimelyDepotContext();
//qryPOs = db.PurchaseOrders.Where(vd => vd.VendorId == id ).OrderByDescending(vd => vd.PurchaseOrderNo);
if (!string.IsNullOrEmpty(customerid))
{
nCustomerId = Convert.ToInt32(customerid);
customer = db.Customers.Find(nCustomerId);
if (customer != null)
{
szCustomerNo = customer.CustomerNo;
}
}
//var qryPOs = db.InvoiceDetails.Join(db.Invoices, podet => podet.InvoiceId, po => po.InvoiceId, (podet, po)
// => new { podet, po }).Where(prod => prod.po.CustomerId == nCustomerId).OrderByDescending(prod => prod.po.InvoiceId).ThenBy(prod => prod.podet.ItemPosition).ThenBy(prod => prod.podet.ItemOrder);
var qryPOs = db.Payments.Where(pymt => pymt.CustomerNo == szCustomerNo).OrderByDescending(pymt => pymt.PaymentDate);
if (qryPOs.Count() > 0)
{
foreach (var item in qryPOs)
{
if (!string.IsNullOrEmpty(item.SalesOrderNo))
{
invoice = db01.Invoices.Where(invc => invc.SalesOrderNo == item.SalesOrderNo).FirstOrDefault<Invoice>();
if (invoice != null)
{
szInvoiceNo = invoice.InvoiceNo;
}
}
else
{
szInvoiceNo = string.Empty;
}
purchaseorderbyvendor = new PurchaseOrdersbyVendor();
purchaseorderbyvendor.PurchaseOrderId = item.Id;
purchaseorderbyvendor.PurchaseOrderNo = item.PaymentNo;
purchaseorderbyvendor.VendorNo = szInvoiceNo;
purchaseorderbyvendor.SODate = item.PaymentDate;
purchaseorderbyvendor.Description = item.PaymentType;
purchaseorderbyvendor.UnitPrice = item.Amount;
purchaseorderList.Add(purchaseorderbyvendor);
}
}
ViewBag.CustomerId = customerid;
//Set the page
if (page == null)
{
pageIndex = 1;
}
else
{
pageIndex = Convert.ToInt32(page);
}
var onePageOfData = purchaseorderList.ToPagedList(pageIndex, pageSize);
ViewBag.OnePageOfData = onePageOfData;
return PartialView(purchaseorderList.ToPagedList(pageIndex, pageSize));
}
public PartialViewResult Outstandinginvoice(int? page, string customerid)
{
bool bPaidinFull = false;
int pageIndex = 0;
int pageSize = PageSize;
int nCustomerId = 0;
int nInvoiceIdHlp = 0;
double dAmount = 0;
double dSalesAmount = 0;
double dTotalTax = 0;
double dTax = 0;
double dTotalAmount = 0;
double dBalanceDue = 0;
string szMsg = string.Empty;
PurchaseOrdersbyVendor purchaseorderbyvendor = null;
List<PurchaseOrdersbyVendor> purchaseorderList = new List<PurchaseOrdersbyVendor>();
TimelyDepotContext db01 = new TimelyDepotContext();
//qryPOs = db.PurchaseOrders.Where(vd => vd.VendorId == id ).OrderByDescending(vd => vd.PurchaseOrderNo);
if (!string.IsNullOrEmpty(customerid))
{
nCustomerId = Convert.ToInt32(customerid);
}
//var qryPOs = db.InvoiceDetails.Join(db.Invoices, podet => podet.InvoiceId, po => po.InvoiceId, (podet, po)
// => new { podet, po }).Where(prod => prod.po.CustomerId == nCustomerId).OrderByDescending(prod => prod.po.InvoiceId).ThenBy(prod => prod.podet.ItemPosition).ThenBy(prod => prod.podet.ItemOrder);
var qryPOs = db.Invoices.Where(invc => invc.CustomerId == nCustomerId).OrderByDescending(invc => invc.InvoiceId);
if (qryPOs.Count() > 0)
{
foreach (var item in qryPOs)
{
//Display here all the invoice for this customer
if (item.InvoiceId != nInvoiceIdHlp)
{
//Get the totals
GetInvoiceTotals01(db01, item.InvoiceId, ref dSalesAmount, ref dTotalTax, ref dTax, ref dTotalAmount, ref dBalanceDue);
nInvoiceIdHlp = item.InvoiceId;
if (item.PaymentAmount == null)
{
dAmount = 0;
}
else
{
dAmount = Convert.ToDouble(item.PaymentAmount);
}
if (dBalanceDue == 0)
{
bPaidinFull = true;
}
else
{
bPaidinFull = false;
}
}
if (bPaidinFull == false)
{
purchaseorderbyvendor = new PurchaseOrdersbyVendor();
purchaseorderbyvendor.PurchaseOrderId = item.InvoiceId;
purchaseorderbyvendor.PurchaseOrderNo = item.InvoiceNo;
purchaseorderbyvendor.SODate = item.InvoiceDate;
purchaseorderbyvendor.VendorNo = dTotalAmount.ToString();
purchaseorderbyvendor.Sub_ItemID = dAmount.ToString(); ;
purchaseorderbyvendor.Description = dBalanceDue.ToString();
//purchaseorderbyvendor.Quantity = item.podet.Quantity;
//purchaseorderbyvendor.UnitPrice = item.podet.UnitPrice;
purchaseorderList.Add(purchaseorderbyvendor);
}
}
}
ViewBag.CustomerId = customerid;
//Set the page
if (page == null)
{
pageIndex = 1;
}
else
{
pageIndex = Convert.ToInt32(page);
}
var onePageOfData = purchaseorderList.ToPagedList(pageIndex, pageSize);
ViewBag.OnePageOfData = onePageOfData;
return PartialView(purchaseorderList.ToPagedList(pageIndex, pageSize));
}
public PartialViewResult OutsandingSalesOrder(int? page, string customerid)
{
int pageIndex = 0;
int pageSize = PageSize;
int nCustomerId = 0;
string szMsg = string.Empty;
PurchaseOrdersbyVendor purchaseorderbyvendor = null;
List<PurchaseOrdersbyVendor> purchaseorderList = new List<PurchaseOrdersbyVendor>();
TimelyDepotContext db01 = new TimelyDepotContext();
//qryPOs = db.PurchaseOrders.Where(vd => vd.VendorId == id ).OrderByDescending(vd => vd.PurchaseOrderNo);
if (!string.IsNullOrEmpty(customerid))
{
nCustomerId = Convert.ToInt32(customerid);
}
var qryPOs = db.SalesOrderDetails.Join(db.SalesOrders, podet => podet.SalesOrderId, po => po.SalesOrderId, (podet, po)
=> new { podet, po }).Where(prod => prod.po.CustomerId == nCustomerId && prod.podet.Quantity != prod.podet.ShipQuantity).OrderByDescending(prod => prod.po.SalesOrderId).ThenBy(prod => prod.podet.ItemPosition).ThenBy(prod => prod.podet.ItemOrder);
if (qryPOs.Count() > 0)
{
foreach (var item in qryPOs)
{
szMsg = item.po.PurchaseOrderNo;
purchaseorderbyvendor = new PurchaseOrdersbyVendor();
purchaseorderbyvendor.PurchaseOrderId = item.po.SalesOrderId;
purchaseorderbyvendor.PurchaseOrderNo = item.po.SalesOrderNo;
purchaseorderbyvendor.SODate = item.po.SODate;
//purchaseorderbyvendor.VendorNo = GetCustomerNo(db01, item.po.CustomerId);
purchaseorderbyvendor.Sub_ItemID = item.podet.Sub_ItemID;
purchaseorderbyvendor.Description = item.podet.Description;
purchaseorderbyvendor.Quantity = item.podet.Quantity;
purchaseorderbyvendor.UnitPrice = item.podet.UnitPrice;
purchaseorderList.Add(purchaseorderbyvendor);
}
}
ViewBag.CustomerId = customerid;
//Set the page
if (page == null)
{
pageIndex = 1;
}
else
{
pageIndex = Convert.ToInt32(page);
}
var onePageOfData = purchaseorderList.ToPagedList(pageIndex, pageSize);
ViewBag.OnePageOfData = onePageOfData;
return PartialView(purchaseorderList.ToPagedList(pageIndex, pageSize));
}