//
// GetCustomerData
public static void GetCustomerData(TimelyDepotContext db01, ref string szASI, ref string szSAGE, ref string szWebSite, ref string szEmail, int CustomerId = 0)
{
Customers customer = null;
CustomersContactAddress contactaddress = null;
IQueryable<CustomersContactAddress> qryAddress = null;
customer = db01.Customers.Find(CustomerId);
if (customer != null)
{
szASI = customer.ASINo;
szSAGE = customer.SageNo;
qryAddress = db01.CustomersContactAddresses.Where(ctad => ctad.CustomerId == customer.Id);
if (qryAddress.Count() > 0)
{
contactaddress = qryAddress.FirstOrDefault<CustomersContactAddress>();
if (contactaddress != null)
{
szWebSite = contactaddress.Website;
szEmail = contactaddress.Email;
}
}
}
}
//
// GET: /PutchaseOrder/GetVendorData
public static void GetVendorData(TimelyDepotContext db01, string szVendorNo, ref string CompanyName, ref string FirstName, ref string LastName,
ref string Title, ref string Address1, ref string Address2, ref string Address3, ref string City, ref string State, ref string Zip, ref string Country, ref string Tel, ref string Fax,
ref string Email, ref string Website, ref string Tel1, ref string Tel2)
{
var qryVendor = db01.VendorsContactAddresses.Join(db01.Vendors, vdca => vdca.VendorId, vd => vd.Id, (vdca, vd)
=> new { vdca, vd }).Where(Nvcda => Nvcda.vd.VendorNo == szVendorNo);
if (qryVendor.Count() > 0)
{
foreach (var item in qryVendor)
{
CompanyName = item.vdca.CompanyName;
FirstName = item.vdca.FirstName;
LastName = item.vdca.LastName;
Title = item.vdca.Title;
Address1 = item.vdca.Address;
Address2 = item.vdca.Note;
Address3 = item.vdca.Address3;
City = item.vdca.City;
State = item.vdca.State;
Zip = item.vdca.Zip;
Country = item.vdca.Country;
Fax = item.vdca.Fax;
Email = item.vdca.Email;
Website = item.vdca.Website;
Tel1 = item.vdca.Tel1;
Tel2 = item.vdca.Tel2;
Tel = item.vdca.Tel;
break;
}
}
}
public static SelectList GetListVendorPartNo(TimelyDepotContext db01, string szItemId, ref string szVendorPartNo02)
{
IQueryable<SUB_ITEM> qrySubitem = db01.SUB_ITEM.Where(sbit => sbit.ItemID == szItemId).OrderBy(sbit => sbit.PartNo);
List<KeyValuePair<string, string>> listSelector = new List<KeyValuePair<string, string>>();
if (qrySubitem.Count() > 0)
{
foreach (var item in qrySubitem)
{
if (!string.IsNullOrEmpty(item.PartNo))
{
if (string.IsNullOrEmpty(szVendorPartNo02))
{
szVendorPartNo02 = item.PartNo;
}
else
{
szVendorPartNo02 = string.Format("{0} {1}", szVendorPartNo02, item.PartNo);
}
//listSelector.Add(new KeyValuePair<string, string>(item.PartNo, item.PartNo));
}
}
}
SelectList listVendorPartNo = new SelectList(listSelector, "Key", "Value");
return listVendorPartNo;
}
//
// GET: /PurchaseOrder/GetTradeName
public static string GetTradeName(TimelyDepotContext db01, int tradeid = 0)
{
string szTradeName = "";
Trade trade = db01.Trades.Find(tradeid);
if (trade != null)
{
szTradeName = trade.TradeName;
}
return szTradeName;
}
//
// /Vendors/GetCompanyName
public static string GetCompanyName(TimelyDepotContext db01, string szVendorID)
{
int nVendorID = Convert.ToInt32(szVendorID);
string szCompanyname = "";
VendorsContactAddress vendoraddres = db01.VendorsContactAddresses.Where(vdad => vdad.VendorId == nVendorID).FirstOrDefault<VendorsContactAddress>();
if (vendoraddres != null)
{
szCompanyname = vendoraddres.CompanyName;
}
return szCompanyname;
}
//
// GET: /Inventory/GetDiscount
public static double GetDiscount(TimelyDepotContext db01, string szDiscount)
{
double dDiscount = 0;
DiscountManage discountmanage = db01.DiscountManages.Where(dm => dm.DiscountName == szDiscount).FirstOrDefault<DiscountManage>();
if (discountmanage != null)
{
dDiscount = Convert.ToDouble(discountmanage.DiscountPercentage) / 100;
}
return dDiscount;
}
//
// /Invoice/GetOrderContact
public static string GetOrderContact(int CustomerID)
{
string szOrderContact = "";
TimelyDepotContext db01 = new TimelyDepotContext();
CustomersSalesContact salescontact = db01.CustomersSalesContacts.Where(slct => slct.CustomerId == CustomerID).FirstOrDefault<CustomersSalesContact>();
if (salescontact != null)
{
szOrderContact = string.Format("{0} {1}", salescontact.FirstName, salescontact.LastName);
}
return szOrderContact;
}
//
// CountDetails
public static string CountDetails(TimelyDepotContext db, int DetailId, string szItemId, string szDescription)
{
bool bStatus = false;
int nIdSubItem = 0;
string szHas = "0";
UserQuotationDetail userquotationdetail = null;
SUB_ITEM subitem = null;
TimelyDepotContext db02 = new TimelyDepotContext();
IQueryable<SUB_ITEM> qrySubItem = null;
IQueryable<UserQuotationDetail> qryUsrDet = db.UserQuotationDetails.Where(usdt => usdt.DetailId == DetailId);
if (qryUsrDet.Count() > 0)
{
szHas = qryUsrDet.Count().ToString();
foreach (var item in qryUsrDet)
{
userquotationdetail = db.UserQuotationDetails.Find(item.Id);
if (userquotationdetail != null)
{
qrySubItem = db02.SUB_ITEM.Where(sbit => sbit.ItemID == szItemId && sbit.Description == userquotationdetail.ProductType);
if (qrySubItem.Count() > 0)
{
subitem = qrySubItem.FirstOrDefault<SUB_ITEM>();
if (subitem != null)
{
nIdSubItem = subitem.Id;
}
}
else
{
nIdSubItem = 0;
}
if (string.IsNullOrEmpty(userquotationdetail.ItemID))
{
userquotationdetail.ItemID = nIdSubItem.ToString();
bStatus = true;
}
}
}
if (bStatus)
{
db.SaveChanges();
}
}
return szHas;
}
public static void GetCustomerEmail(TimelyDepotContext db01, ref string szName, ref string szEmail, int nCustomerId = 0)
{
CustomersContactAddress contactaddress = db01.CustomersContactAddresses.Where(ctad => ctad.CustomerId == nCustomerId).FirstOrDefault<CustomersContactAddress>();
if (contactaddress != null)
{
szName = contactaddress.CompanyName;
szEmail = contactaddress.Email;
}
else
{
szName = "";
szEmail = "";
}
}
public static string GetFirstVendorPartNo(TimelyDepotContext db01, string szItemId)
{
string szVendorPartNo = "";
VendorItem vendoritem = db01.VendorItems.Where(vdit => vdit.ItemId == szItemId).FirstOrDefault<VendorItem>();
if (vendoritem != null)
{
if (!string.IsNullOrEmpty(vendoritem.VendorPartNo))
{
szVendorPartNo = vendoritem.VendorPartNo;
}
}
return szVendorPartNo;
}
//
// Get customer email
public static string GetCustomerEmail(TimelyDepotContext db01, int CustomerId)
{
string szEmail = string.Empty;
IQueryable<CustomersContactAddress> qryAddress = null;
CustomersContactAddress customeraddress = null;
qryAddress = db01.CustomersContactAddresses.Where(ctad => ctad.CustomerId == CustomerId);
if (qryAddress.Count() > 0)
{
customeraddress = qryAddress.FirstOrDefault<CustomersContactAddress>();
if (customeraddress != null)
{
szEmail = customeraddress.Email;
}
}
return szEmail;
}
//
// /SalesOrder/GetCompanyName
public static string GetCompanyName(TimelyDepotContext db01, string VendorId)
{
int nVendorId = Convert.ToInt32(VendorId);
string szCompanyName = "";
IQueryable<VendorsContactAddress> qryAddress = null;
VendorsContactAddress vendoraddress = null;
qryAddress = db01.VendorsContactAddresses.Where(vad => vad.VendorId == nVendorId);
if (qryAddress.Count() > 0)
{
vendoraddress = qryAddress.FirstOrDefault<VendorsContactAddress>();
if (vendoraddress != null)
{
szCompanyName = vendoraddress.CompanyName;
}
}
return szCompanyName;
}
public static string GetBussinesType(TimelyDepotContext db01, int nCustomerId, string CustomerNo)
{
string szBussinesType = "Domestic";
Customers customer;
CustomersContactAddress maincontact = null;
if (nCustomerId != null)
{
customer = db01.Customers.Find(nCustomerId);
if (customer != null)
{
szBussinesType = customer.BussinesType;
}
}
else
{
}
return szBussinesType;
}
public static string GetBussinesType(TimelyDepotContext db01, int nVendorid, string szVendorNo)
{
string szBussinesType = "Domestic";
Vendors vendor = null;
if (nVendorid > 0)
{
vendor = db01.Vendors.Find(nVendorid);
if (vendor != null)
{
szBussinesType = vendor.BussinesType;
}
}
else
{
vendor = db01.Vendors.Where(vd => vd.VendorNo == szVendorNo).FirstOrDefault<Vendors>();
if (vendor != null)
{
szBussinesType = vendor.BussinesType;
}
}
return szBussinesType;
}
public PartialViewResult GetSalesDetailsSRC(int? page, string salesorderid)
{
TimelyDepotContext db01 = new TimelyDepotContext();
int pageIndex = 0;
int pageSize = PageSize;
pageSize = 10;
int nSalesOrderid = Convert.ToInt32(salesorderid);
string szSalesOredidHlp = "";
IQueryable<SalesOrderDetail> qrysalesdetails = null;
IQueryable<ImprintMethods> qryImprint = null;
SalesOrderDetail setupcharge = null;
SalesOrderDetail runcharge = null;
SalesOrderDetailSRC salesorderdetailSRC = null;
List<SalesOrderDetailSRC> salesdetailsList = new List<SalesOrderDetailSRC>();
List<KeyValuePair<string, string>> listSelector = new List<KeyValuePair<string, string>>();
//qrysalesdetails = db.SalesOrderDetails.Where(sldt => sldt.SalesOrderId == nSalesOrderid).OrderBy(sldt => sldt.Sub_ItemID).ThenBy(sldt => sldt.ItemOrder);
qrysalesdetails = db.SalesOrderDetails.Where(sldt => sldt.SalesOrderId == nSalesOrderid && sldt.ItemID != "").OrderBy(sldt => sldt.ItemPosition).ThenBy(sldt => sldt.ItemOrder);
if (qrysalesdetails.Count() > 0)
{
foreach (var item in qrysalesdetails)
{
salesorderdetailSRC = new SalesOrderDetailSRC();
salesorderdetailSRC.BackOrderQuantity = item.BackOrderQuantity;
salesorderdetailSRC.Description = item.Description;
salesorderdetailSRC.Id = item.Id;
salesorderdetailSRC.ImprintMethod = item.ImprintMethod;
salesorderdetailSRC.ItemID = item.ItemID;
salesorderdetailSRC.ItemOrder = item.ItemOrder;
salesorderdetailSRC.ItemPosition = item.ItemPosition;
salesorderdetailSRC.Logo = item.Logo;
salesorderdetailSRC.Quantity = item.Quantity;
salesorderdetailSRC.SalesOrderId = item.SalesOrderId;
salesorderdetailSRC.ShipQuantity = item.ShipQuantity;
salesorderdetailSRC.Sub_ItemID = item.Sub_ItemID;
salesorderdetailSRC.Tax = item.Tax;
salesorderdetailSRC.UnitPrice = item.UnitPrice;
salesorderdetailSRC.QuantitySC = 0;
salesorderdetailSRC.UnitPricSRC = 0;
salesorderdetailSRC.QuantityRC = 0;
salesorderdetailSRC.UnitPriceRC = 0;
//Set Up Charge
szSalesOredidHlp = string.Format("Set up Charge {0} {1}", salesorderdetailSRC.SalesOrderId.ToString(), salesorderdetailSRC.ItemID);
setupcharge = db01.SalesOrderDetails.Where(stup => stup.SalesOrderId == item.SalesOrderId && stup.Description == szSalesOredidHlp).FirstOrDefault<SalesOrderDetail>();
if (setupcharge != null)
{
salesorderdetailSRC.QuantitySC = setupcharge.Quantity;
salesorderdetailSRC.UnitPricSRC = setupcharge.UnitPrice;
}
//Run Charge
szSalesOredidHlp = string.Format("Run Charge {0} {1}", salesorderdetailSRC.SalesOrderId.ToString(), salesorderdetailSRC.ItemID);
runcharge = db01.SalesOrderDetails.Where(stup => stup.SalesOrderId == item.SalesOrderId && stup.Description == szSalesOredidHlp).FirstOrDefault<SalesOrderDetail>();
if (runcharge != null)
{
salesorderdetailSRC.QuantityRC = runcharge.Quantity;
salesorderdetailSRC.UnitPriceRC = runcharge.UnitPrice;
}
salesdetailsList.Add(salesorderdetailSRC);
}
}
ViewBag.SalesOrderId = salesorderid;
//Get the imprint methods
qryImprint = db.ImprintMethods.OrderBy(trd => trd.Description);
if (qryImprint.Count() > 0)
{
foreach (var item in qryImprint)
{
listSelector.Add(new KeyValuePair<string, string>(item.Description, item.Description));
}
}
SelectList imprintlist = new SelectList(listSelector, "Key", "Value");
ViewBag.ImprintList = imprintlist;
//Set the page
if (page == null)
{
pageIndex = 1;
}
else
{
pageIndex = Convert.ToInt32(page);
}
var onePageOfData = salesdetailsList.ToPagedList(pageIndex, pageSize);
ViewBag.OnePageOfData = onePageOfData;
return PartialView(salesdetailsList.ToPagedList(pageIndex, pageSize));
}
public ActionResult AddSalesOrderDetails(string itemOrder, int id = 0, int salesorderId = 0, int itemPos = 0)
{
int nitemPosNext = 0;
int nItemPos = 0;
int nCurrentItemPos = 0;
int nNextItemPos = 0;
int nSalesOrderId = 0;
double nItemOrder = 0;
double dItemOrder = 0;
double dCurrentItemOrder = 0;
double dNextItemOrder = 0;
double dQty = 0;
decimal dPrice = 0;
string szCurentItemId = "";
string szNextItemId = "";
string szSalesOrderIdHlp = "";
string szItemId = "";
SalesOrderDetail salesdetail = null;
SalesOrderDetail salesdetailcurrent = null;
SalesOrderDetail salesdetailnext = null;
SalesOrderDetail salesorderdetail = null;
SetupChargeDetail setupcharge = null;
IQueryable<PRICE> qryPrice = null;
TimelyDepotContext db01 = new TimelyDepotContext();
SUB_ITEM subitem = db.SUB_ITEM.Find(id);
if (subitem != null)
{
if (!string.IsNullOrEmpty(itemOrder))
{
//itemOrder = itemOrder.Replace(".", ",");
nItemOrder = Convert.ToDouble(itemOrder);
}
//Get the current salesorderdetail
salesdetailcurrent = db.SalesOrderDetails.Where(sldt => sldt.SalesOrderId == salesorderId && sldt.ItemPosition == itemPos && sldt.ItemOrder == nItemOrder).FirstOrDefault<SalesOrderDetail>();
if (salesdetailcurrent != null)
{
nCurrentItemPos = Convert.ToInt32(salesdetailcurrent.ItemPosition);
dCurrentItemOrder = Convert.ToDouble(salesdetailcurrent.ItemOrder);
szCurentItemId = salesdetailcurrent.ItemID;
}
//Get the next salesorderdetail
salesdetailnext = db.SalesOrderDetails.Where(sldt => sldt.SalesOrderId == salesorderId && sldt.ItemPosition == nCurrentItemPos && sldt.ItemOrder > dCurrentItemOrder).OrderBy(sldt => sldt.ItemOrder).FirstOrDefault<SalesOrderDetail>();
if (salesdetailnext != null)
{
nNextItemPos = Convert.ToInt32(salesdetailnext.ItemPosition);
szNextItemId = salesdetailnext.ItemID;
dNextItemOrder = Convert.ToDouble(salesdetailnext.ItemOrder);
}
if (subitem.ItemID == szNextItemId)
{
nItemPos = nCurrentItemPos;
dItemOrder = (dCurrentItemOrder + dNextItemOrder) / 2;
}
else
{
nItemPos = nCurrentItemPos;
dItemOrder = dCurrentItemOrder + 1;
}
//
// Set the price and qty search to the lowest price for this item
double dDiscountPrc = 0;
qryPrice = db.PRICEs.Where(prc => prc.Item == subitem.ItemID).OrderBy(prc => prc.Qty);
if (qryPrice.Count() > 0)
{
foreach (var item in qryPrice)
{
dQty = item.Qty;
dPrice = item.thePrice;
dDiscountPrc = TimelyDepotMVC.Controllers.InventoryController.GetDiscount(db01, item.Discount_Code);
break;
}
}
salesdetail = new SalesOrderDetail();
salesdetail.SalesOrderId = salesorderId;
salesdetail.ItemID = subitem.ItemID;
salesdetail.Sub_ItemID = subitem.Sub_ItemID;
salesdetail.BackOrderQuantity = 0;
salesdetail.Description = subitem.Description;
salesdetail.Quantity = dQty;
salesdetail.ShipQuantity = 0;
salesdetail.Tax = 0;
salesdetail.UnitPrice = dPrice * (1 - Convert.ToDecimal(dDiscountPrc));
salesdetail.ItemPosition = nItemPos;
salesdetail.ItemOrder = dItemOrder;
db.SalesOrderDetails.Add(salesdetail);
db.SaveChanges();
szSalesOrderIdHlp = salesdetail.SalesOrderId.ToString();
nSalesOrderId = Convert.ToInt32(szSalesOrderIdHlp);
szItemId = salesdetail.ItemID;
//Create setup charge
setupcharge = db.SetupChargeDetails.Where(stup => stup.itemid == subitem.ItemID).FirstOrDefault<SetupChargeDetail>();
if (setupcharge != null)
{
// Setup charge
double dDiscount = TimelyDepotMVC.Controllers.InventoryController.GetDiscount(db, setupcharge.SetupChargeDiscountCode);
salesorderdetail = new SalesOrderDetail();
salesorderdetail.SalesOrderId = nSalesOrderId;
salesorderdetail.ItemID = string.Empty;
salesorderdetail.Sub_ItemID = string.Empty;
salesorderdetail.Description = string.Format("Set up Charge {0} {1}", szSalesOrderIdHlp, szItemId);
salesorderdetail.Quantity = 0;
salesorderdetail.ShipQuantity = 0;
salesorderdetail.BackOrderQuantity = 0;
salesorderdetail.Tax = 0;
salesorderdetail.UnitPrice = setupcharge.SetUpCharge * (1 - Convert.ToDecimal(dDiscount));
salesorderdetail.ItemPosition = 0;
salesorderdetail.ItemOrder = 0;
salesorderdetail.Tax = 0;
db.SalesOrderDetails.Add(salesorderdetail);
//Create run charge
dDiscount = TimelyDepotMVC.Controllers.InventoryController.GetDiscount(db, setupcharge.RunChargeDiscountCode);
salesorderdetail = new SalesOrderDetail();
salesorderdetail.SalesOrderId = nSalesOrderId;
salesorderdetail.ItemID = string.Empty;
salesorderdetail.Sub_ItemID = string.Empty;
salesorderdetail.Description = string.Format("Run Charge {0} {1}", szSalesOrderIdHlp, szItemId);
salesorderdetail.Quantity = 0;
salesorderdetail.ShipQuantity = 0;
salesorderdetail.BackOrderQuantity = 0;
salesorderdetail.Tax = 0;
salesorderdetail.UnitPrice = setupcharge.RunCharge * (1 - Convert.ToDecimal(dDiscount));
salesorderdetail.ItemPosition = 0;
salesorderdetail.ItemOrder = 0;
salesorderdetail.Tax = 0;
db.SalesOrderDetails.Add(salesorderdetail);
db.SaveChanges();
}
}
return RedirectToAction("Edit", new { id = salesorderId });
}
private void GetSalesOrderTotals01(TimelyDepotContext db01, int nSalesOrderId, ref double dSalesAmount, ref double dTotalTax, ref double dTax, ref double dTotalAmount, ref double dBalanceDue)
{
double dShipping = 0;
double dPayment = 0;
double dSOTax = 0;
IQueryable<SalesOrderDetail> qryDetails = null;
InitialInfo initialinfo = null;
dSalesAmount = 0;
dTax = 0;
dTotalAmount = 0;
dBalanceDue = 0;
dTotalTax = 0;
initialinfo = db01.InitialInfoes.FirstOrDefault<InitialInfo>();
if (initialinfo == null)
{
initialinfo = new InitialInfo();
initialinfo.InvoiceNo = 0;
initialinfo.PaymentNo = 0;
initialinfo.PurchaseOrderNo = 0;
initialinfo.SalesOrderNo = 1;
initialinfo.TaxRate = 0;
db.InitialInfoes.Add(initialinfo);
dTax = initialinfo.TaxRate;
}
else
{
dTax = initialinfo.TaxRate;
}
//Each sales order should save it own tax information. Also the tax should be on product only, no tax on service.
SalesOrder salesorder = db01.SalesOrders.Find(nSalesOrderId);
if (salesorder != null)
{
dShipping = Convert.ToDouble(salesorder.ShippingHandling);
dPayment = Convert.ToDouble(salesorder.PaymentAmount);
//Use the sales order tax information
if (salesorder.Tax_rate != null)
{
if (Convert.ToDecimal(salesorder.Tax_rate) >= 0)
{
dTax = Convert.ToDouble(salesorder.Tax_rate);
}
}
dSOTax = dTax;
qryDetails = db01.SalesOrderDetails.Where(sldt => sldt.SalesOrderId == salesorder.SalesOrderId);
if (qryDetails.Count() > 0)
{
foreach (var item in qryDetails)
{
////use the tax on product
//if (item.Tax != null)
//{
// if (Convert.ToDecimal(item.Tax) >= 0)
// {
// dTax = Convert.ToDouble(item.Tax);
// }
//}
dSalesAmount = dSalesAmount + (Convert.ToDouble(item.Quantity) * Convert.ToDouble(item.UnitPrice));
//use the tax on product
if (!string.IsNullOrEmpty(item.Sub_ItemID))
{
dTotalTax = dTotalTax + (Convert.ToDouble(item.Quantity) * Convert.ToDouble(item.UnitPrice) * (dTax / 100));
}
}
}
dTotalAmount = dSalesAmount + dTotalTax + dShipping;
dBalanceDue = dTotalAmount - dPayment;
//Set the sales order tax again
dTax = dSOTax;
}
}
private DataTable GetSalesOrderTable()
{
long telHlp = 0;
long faxHlp = 0;
string telfmt = "000-000-0000";
string szTel = "";
TimelyDepotContext db01 = new TimelyDepotContext();
DataTable table = null;
DataRow row = null;
SalesOrderList thesalesorderlist = null;
List<SalesOrderList> vendorList = new List<SalesOrderList>();
var qrySalesOrder = db.CustomersContactAddresses.Join(db.SalesOrders, 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)
{
if (string.IsNullOrEmpty(item.ctad.Tel))
{
szTel = "0";
}
else
{
szTel = item.ctad.Tel;
}
telHlp = Convert.ToInt64(szTel);
szTel = string.Format("{0}", telHlp.ToString(telfmt));
thesalesorderlist = new SalesOrderList();
thesalesorderlist.SalesOrderId = item.slod.SalesOrderId;
thesalesorderlist.SalesOrderNo = item.slod.SalesOrderNo;
thesalesorderlist.SODate = item.slod.SODate;
thesalesorderlist.CustomerNo = GetCustomerDataSO(db01, item.ctad.CustomerId.ToString());
thesalesorderlist.CompanyName = item.ctad.CompanyName;
thesalesorderlist.PurchaseOrderNo = item.slod.PurchaseOrderNo;
thesalesorderlist.ShipDate = item.slod.ShipDate;
thesalesorderlist.PaymentAmount = GetSalesOrderAmount(db01, item.slod.SalesOrderId);
vendorList.Add(thesalesorderlist);
}
}
table = new DataTable("SalesOrderList");
// Set the header
DataColumn col01 = new DataColumn("SalesOrderNo", System.Type.GetType("System.String"));
DataColumn col02 = new DataColumn("SODate", 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("PurchaseOrderNo", System.Type.GetType("System.String"));
DataColumn col06 = new DataColumn("ShipDate", 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 vendorList)
{
row = table.NewRow();
row["SalesOrderNo"] = item.SalesOrderNo;
row["SODate"] = item.SODate;
row["CustomerNo"] = item.CustomerNo;
row["CompanyName"] = item.CompanyName;
row["PurchaseOrderNo"] = item.PurchaseOrderNo;
row["ShipDate"] = item.ShipDate;
row["Amount"] = item.PaymentAmount;
table.Rows.Add(row);
}
return table;
}
private string GetCustomerDataSO(TimelyDepotContext db01, string szCustomerId)
{
int nCustomerid = Convert.ToInt32(szCustomerId);
string szCustomerNo = "";
Customers customeraddress = db01.Customers.Where(ctad => ctad.Id == nCustomerid).FirstOrDefault<Customers>();
if (customeraddress != null)
{
szCustomerNo = customeraddress.CustomerNo;
}
return szCustomerNo;
}
private void DeleteSalesOrderDetails(int id)
{
int nCustomerID = 0;
int nUserID = 0;
int nQuoteId = 0;
string szItemID = "";
string szEmail = "";
//IQueryable<UserQuotation> qryQuoteUsr = null;
IQueryable<UserQuotationDetail> qryQuoteDetails = null;
UserQuotation userquotation = null;
CustomersContactAddress customercontactaddress = null;
TimelyDepotContext db01 = new TimelyDepotContext();
//SalesOrderDetail salesorderdetails = null;
//Get customer email
SalesOrder salesorder = db.SalesOrders.Find(id);
if (salesorder != null)
{
customercontactaddress = db01.CustomersContactAddresses.Where(cuad => cuad.CustomerId == salesorder.CustomerId).FirstOrDefault<CustomersContactAddress>();
if (customercontactaddress != null)
{
szEmail = customercontactaddress.Email;
}
}
IQueryable<SalesOrderDetail> qryalesorderdetails = db.SalesOrderDetails.Where(sodt => sodt.SalesOrderId == id);
if (qryalesorderdetails.Count() > 0)
{
foreach (var item in qryalesorderdetails)
{
if (string.IsNullOrEmpty(szItemID))
{
szItemID = item.ItemID;
}
db.SalesOrderDetails.Remove(item);
}
//Restore Quotation Details
//qryQuoteUsr = db.UserQuotations.Where(qtus => qtus.ProductId == szItemID);
var qryQuoteUsr = db01.UserQuotations.Join(db01.UserRegistrations, usqt => usqt.UserId, usrg => usrg.RId, (usqt, usrg)
=> new { usqt, usrg }).Where(uqus => uqus.usqt.ProductId == szItemID && uqus.usrg.UserName == szEmail);
if (qryQuoteUsr.Count() > 0)
{
//userquotation = qryQuoteUsr.FirstOrDefault<UserQuotation>();
//if (userquotation != null)
//{
// nQuoteId = userquotation.Id;
//}
foreach (var item in qryQuoteUsr)
{
//Get the quoteid
if (nQuoteId == 0)
{
nQuoteId = item.usqt.Id;
}
}
}
if (nQuoteId > 0)
{
qryQuoteDetails = db.UserQuotationDetails.Where(qtdt => qtdt.DetailId == nQuoteId);
if (qryQuoteDetails.Count() > 0)
{
foreach (var item in qryQuoteDetails)
{
item.ShippedQuantity = null;
item.BOQuantity = null;
item.Status = 0;
db.Entry(item).State = EntityState.Modified;
}
}
}
db01.SaveChanges();
}
}
//
// GetTradeData
public static void GetTradeData(TimelyDepotContext db01, ref string szAddress, ref string szCity, ref string szState, ref string szZip,
ref string szCountry, ref string szTel, ref string szFax, ref string szWebSite, ref string szEmail, ref string szTradeName,
ref string szAsiTrade, ref string szSageTrade, ref string szPpaiTrade, int TradeId = 0)
{
Trade trade = db01.Trades.Find(TradeId);
if (trade != null)
{
szTradeName = trade.TradeName;
szAddress = trade.Address;
szCity = trade.City;
szState = trade.State;
szZip = trade.PostCode;
szCountry = trade.Country;
szTel = trade.Tel;
szFax = trade.Fax;
szWebSite = trade.WebSite;
szEmail = trade.Email;
szAsiTrade = trade.ASINo;
szSageTrade = trade.SageNo;
szPpaiTrade = trade.PPAINo;
}
}
public PartialViewResult SalesOrderList(int? page)
{
int pageIndex = 0;
int pageSize = PageSize;
TimelyDepotContext db01 = new TimelyDepotContext();
SalesOrderList thesalesorderlist = null;
List<SalesOrderList> customerList = new List<SalesOrderList>();
var qrySalesOrder = db.CustomersContactAddresses.Join(db.SalesOrders, 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)
{
thesalesorderlist = new SalesOrderList();
thesalesorderlist.SalesOrderId = item.slod.SalesOrderId;
thesalesorderlist.SalesOrderNo = item.slod.SalesOrderNo;
thesalesorderlist.SODate = item.slod.SODate;
thesalesorderlist.CustomerNo = GetCustomerDataSO(db01, item.ctad.CustomerId.ToString());
thesalesorderlist.CompanyName = item.ctad.CompanyName;
thesalesorderlist.PurchaseOrderNo = item.slod.PurchaseOrderNo;
thesalesorderlist.ShipDate = item.slod.ShipDate;
thesalesorderlist.PaymentAmount = GetSalesOrderAmount(db01, item.slod.SalesOrderId);
customerList.Add(thesalesorderlist);
}
}
//Set the page
if (page == null)
{
pageIndex = 1;
}
else
{
pageIndex = Convert.ToInt32(page);
}
var onePageOfData = customerList.ToPagedList(pageIndex, pageSize);
ViewBag.OnePageOfData = onePageOfData;
return PartialView(customerList.ToPagedList(pageIndex, pageSize));
}
public static string GetCompanyName02(TimelyDepotContext db01, string szVendorID, ref string szCity, ref string szZip, ref string szPhone)
{
long telHlp = 0;
long faxHlp = 0;
string telfmt = "000-000-0000";
string szBussinesType = "";
int nVendorID = Convert.ToInt32(szVendorID);
string szCompanyname = "";
VendorsContactAddress vendoraddres = db01.VendorsContactAddresses.Where(vdad => vdad.VendorId == nVendorID).FirstOrDefault<VendorsContactAddress>();
if (vendoraddres != null)
{
telHlp = Convert.ToInt64(vendoraddres.Tel1);
faxHlp = Convert.ToInt64(vendoraddres.Fax);
szCompanyname = vendoraddres.CompanyName;
szCity = vendoraddres.City;
szZip = vendoraddres.Zip;
//szPhone = vendoraddres.Tel1;
szPhone = telHlp.ToString(telfmt);
szBussinesType = GetBussinesType(db01, Convert.ToInt32(vendoraddres.VendorId), "");
if (!string.IsNullOrEmpty(szBussinesType))
{
if (szBussinesType.ToUpper() != "DOMESTIC")
{
int nLen = 13;
if (!string.IsNullOrEmpty(vendoraddres.Tel1))
{
nLen = vendoraddres.Tel1.Length;
}
switch (nLen)
{
case 8:
telfmt = "000-00000";
break;
case 9:
telfmt = "000-000000";
break;
case 10:
telfmt = "000-0000000";
break;
case 11:
telfmt = "000-00000000";
break;
case 12:
telfmt = "000-000000000";
break;
case 13:
telfmt = "000-0000000000";
break;
default:
break;
}
telHlp = Convert.ToInt64(vendoraddres.Tel1);
szPhone = telHlp.ToString(telfmt);
}
}
}
return szCompanyname;
}
// Get the ItemID value for the selected itemId Detail
// Get subitem id
public static string GetSubItemId01(TimelyDepotContext db01, int ItemId = 0)
{
int nItemId = Convert.ToInt32(ItemId);
string szItemId = "";
UserQuotation quote = null;
UserQuotationDetail quotedetail = db01.UserQuotationDetails.Find(nItemId);
if (quotedetail != null)
{
quote = db01.UserQuotations.Find(quotedetail.DetailId);
if (quote != null)
{
szItemId = quote.ProductId;
}
}
return szItemId;
}
// Get the ItemID value for the selected itemId Detail
// Get subitem id
public static string GetSubItemId(TimelyDepotContext db01, string ItemId)
{
int nItemId = Convert.ToInt32(ItemId);
string szItemId = "";
IQueryable<SUB_ITEM> qrysubitem = null;
SUB_ITEM subitem = null;
qrysubitem = db01.SUB_ITEM.Where(sbit => sbit.Id == nItemId);
if (qrysubitem.Count() > 0)
{
subitem = qrysubitem.FirstOrDefault<SUB_ITEM>();
if (subitem != null)
{
szItemId = subitem.ItemID;
}
}
return szItemId;
}
public decimal GetSalesOrderAmount(TimelyDepotContext db01, int nSalesOrderId)
{
double dAmount = 0;
double dSalesAmount = 0;
double dTotalTax = 0;
double dTax = 0;
double dTotalAmount = 0;
double dBalanceDue = 0;
//Get the totals
GetSalesOrderTotals01(db01, nSalesOrderId, ref dSalesAmount, ref dTotalTax, ref dTax, ref dTotalAmount, ref dBalanceDue);
dAmount = dTotalAmount;
return Convert.ToDecimal(dAmount);
}
//
// Get user name
public static string GetUserRegistrationName(TimelyDepotContext db, int Id)
{
string szName = "";
UserRegistration userregistration = db.UserRegistrations.Find(Id);
if (userregistration != null)
{
szName = userregistration.UserName;
}
return szName;
}
//
//
public static void GetCustomerEmail(TimelyDepotContext db01, ref string szName, ref string szEmail, int SalesOrderId = 0)
{
IQueryable<Customers> qrycustomer = null;
IQueryable<CustomersContactAddress> qryAddress = null;
Customers customer = null;
CustomersContactAddress contactaddres = null;
SalesOrder salesorder = db01.SalesOrders.Find(SalesOrderId);
if (salesorder != null)
{
qrycustomer = db01.Customers.Where(cst => cst.Id == salesorder.CustomerId);
if (qrycustomer.Count() > 0)
{
customer = qrycustomer.FirstOrDefault<Customers>();
if (customer != null)
{
qryAddress = db01.CustomersContactAddresses.Where(ctad => ctad.CustomerId == customer.Id);
if (qryAddress.Count() > 0)
{
contactaddres = qryAddress.FirstOrDefault<CustomersContactAddress>();
if (contactaddres != null)
{
szName = contactaddres.CompanyName;
szEmail = contactaddres.Email;
}
}
}
}
}
}
public static string GetSalesOrderNo(TimelyDepotContext db01, int qutoeId)
{
string szSalesOrderNo = "";
SalesOrder salesorder = db01.SalesOrders.Where(slod => slod.QuoteId == qutoeId).FirstOrDefault<SalesOrder>();
if (salesorder != null)
{
szSalesOrderNo = salesorder.SalesOrderNo;
}
return szSalesOrderNo;
}
public static void GetInvoiceData(TimelyDepotContext db01, string SalesOrderNo, ref string szShippedDate01, ref string szTrackingNo, ref string szInvoice)
{
DateTime dDate = DateTime.Now;
szShippedDate01 = "";
szTrackingNo = "";
szInvoice = "";
var qryInvoice = db01.Invoices.Join(db01.SalesOrders, invc => invc.SalesOrderNo, slor => slor.SalesOrderNo, (invc, slor)
=> new { invc, slor }).Where(Ndata => Ndata.slor.SalesOrderNo == SalesOrderNo);
if (qryInvoice.Count() > 0)
{
foreach (var item in qryInvoice)
{
szInvoice = item.invc.InvoiceNo;
szTrackingNo = item.invc.TrackingNo;
if (item.invc.ShipDate != null)
{
dDate = Convert.ToDateTime(item.invc.ShipDate);
szShippedDate01 = dDate.ToShortDateString();
}
break;
}
}
}