/// <summary>
/// Get customers Orders
/// </summary>
/// <param name="pagenumber"></param>
/// <param name="pageSize"></param>
/// <param name="pCustomerID"></param>
/// <returns></returns>
public IPagedList <CustomersWithOrders> getPremierCustomersOrders(int pagenumber, int pageSize, int pCustomerID)
{
IPagedList <CustomersWithOrders> _customers = null;
using (var db = new AdventureWorks2016CTP3Entities())
{
var query = from c in db.Customers
join cd in db.CustomerPIIs on c.CustomerID equals cd.CustomerID
join sh in db.SalesOrderHeaders on c.CustomerID equals sh.CustomerID
//join be in db.BusinessEntityAddresses on c.CustomerID equals be.BusinessEntityID
join a in db.Addresses on sh.BillToAddressID equals a.AddressID
where c.CustomerID == pCustomerID
select new CustomersWithOrders()
{
CustomerID = c.CustomerID,
FirstName = cd.FirstName,
LastName = cd.LastName,
EmailAddress = cd.EmailAddress,
PhoneNumber = cd.PhoneNumber,
SalesOrderID = sh.SalesOrderID,
OrderDate = sh.OrderDate,
ShipDate = sh.ShipDate,
AccountNumber = sh.AccountNumber,
SubTotal = sh.SubTotal,
TaxAmt = sh.TaxAmt,
Freight = sh.Freight,
TotalDue = sh.TotalDue,
FullAddress = a.AddressLine1 + " " + a.AddressLine2 + " " + a.City + " " + a.PostalCode,
AddressID = a.AddressID
};
query = query.OrderBy(CustomersWithOrders => CustomersWithOrders.CustomerID);
_customers = query.ToPagedList(pagenumber, pageSize);
}
return(_customers);
}
public SalesOrderDetail GetItemDetil(int pSalesOrderDetailID)
{
using (var db = new AdventureWorks2016CTP3Entities())
{
var _Products = db.SalesOrderDetails.Where(sd => sd.SalesOrderDetailID == pSalesOrderDetailID);
return(_Products.FirstOrDefault());
}
}
public Product getProducts(int pProductId)
{
using (var db = new AdventureWorks2016CTP3Entities())
{
var _Products = db.Products.Where(Product => Product.ProductID == pProductId).OrderBy(Product => Product.ProductID).Take(1);
return(_Products.FirstOrDefault());
}
}
public bool DeleteItemInDB(int pSalesOrderDetailID)
{
using (var db = new AdventureWorks2016CTP3Entities())
{
SalesOrderDetail _SalesOrderDetail = db.SalesOrderDetails.Where(sd => sd.SalesOrderDetailID == pSalesOrderDetailID).SingleOrDefault();
db.SalesOrderDetails.Remove(_SalesOrderDetail);
db.SaveChanges();
return(true);
}
}
public bool UpdateItemInDB(SalesOrderDetail pSalesOrderDetail)
{
using (var db = new AdventureWorks2016CTP3Entities())
{
SalesOrderDetail _SalesOrderDetail = db.SalesOrderDetails.Where(sd => sd.SalesOrderDetailID == pSalesOrderDetail.SalesOrderDetailID).SingleOrDefault();
_SalesOrderDetail.CarrierTrackingNumber = pSalesOrderDetail.CarrierTrackingNumber;
db.SaveChanges();
return(true);
}
}
public IPagedList <CustomersWithOrders> getCustomersDetail(int pagenumber, int pageSize)
{
IPagedList <CustomersWithOrders> _customers = null;
using (var db = new AdventureWorks2016CTP3Entities())
{
var query = from c in db.Customers
join cd in db.CustomerPIIs on c.CustomerID equals cd.CustomerID
join sh in db.SalesOrderHeaders on c.CustomerID equals sh.CustomerID
join sd in db.SalesOrderDetails on sh.SalesOrderID equals sd.SalesOrderID
group new { c, cd, sh } by new
{
c.CustomerID,
cd.FirstName,
cd.LastName,
cd.EmailAddress,
cd.PhoneNumber,
sh.SalesOrderID,
sh.OrderDate,
sh.ShipDate,
sh.AccountNumber,
sh.SubTotal,
sh.TaxAmt,
sh.Freight,
sh.TotalDue
} into grp
where grp.Count() > 19
select new CustomersWithOrders()
{
Count = grp.Count(),
CustomerID = grp.Key.CustomerID,
FirstName = grp.Key.FirstName,
LastName = grp.Key.LastName,
EmailAddress = grp.Key.EmailAddress,
PhoneNumber = grp.Key.PhoneNumber,
SalesOrderID = grp.Key.SalesOrderID,
OrderDate = grp.Key.OrderDate,
ShipDate = grp.Key.ShipDate,
AccountNumber = grp.Key.AccountNumber,
SubTotal = grp.Key.SubTotal,
TaxAmt = grp.Key.TaxAmt,
Freight = grp.Key.Freight,
TotalDue = grp.Key.TotalDue,
};
query = query.OrderBy(CustomersWithOrders => CustomersWithOrders.CustomerID);
_customers = query.ToPagedList(pagenumber, pageSize);
//.Skip(pagenumber * pageSize).Take(pageSize);
// query.ToList<CustomersWithOrders>();
}
return(_customers);
}
public bool ChangeShipingAddress(int pCustomerID, int pSalesOrderID, int pAddressID)
{
var _result = false;
using (var db = new AdventureWorks2016CTP3Entities())
{
SalesOrderHeader _SalesOrderHeaders = db.SalesOrderHeaders.FirstOrDefault(s => s.SalesOrderID == pSalesOrderID);
_SalesOrderHeaders.BillToAddressID = pAddressID;
db.SaveChanges();
_result = true;
}
return(_result);
}
public bool SaveNewItemToOrder(int pCustomerID, SalesOrderDetail pSalesOrderDetail)
{
using (var db = new AdventureWorks2016CTP3Entities())
{
pSalesOrderDetail.rowguid = Guid.NewGuid();
db.SalesOrderDetails.Add(pSalesOrderDetail);
SalesOrderHeader _SalesOrderHeader = db.SalesOrderHeaders.Where(t => t.SalesOrderID == pSalesOrderDetail.SalesOrderID).FirstOrDefault();
_SalesOrderHeader.SubTotal = _SalesOrderHeader.SubTotal + pSalesOrderDetail.LineTotal;
_SalesOrderHeader.TotalDue = _SalesOrderHeader.TotalDue + pSalesOrderDetail.LineTotal;
db.SaveChanges();
return(true);
}
}
public int getSpecialOfferID(int pProductId)
{
using (var db = new AdventureWorks2016CTP3Entities())
{
var _Products = db.SpecialOfferProducts.Where(Product => Product.ProductID == pProductId).OrderBy(Product => Product.ModifiedDate).Take(1);
var output = _Products.FirstOrDefault();
if (output != null)
{
return(output.SpecialOfferID);
}
else
{
return(1);
}
}
}
public List <Product> getProducts()
{
using (var db = new AdventureWorks2016CTP3Entities())
{
List <Product> _Products;
var query = from p in db.Products
join sp in db.SpecialOfferProducts on p.ProductID equals sp.ProductID
orderby p.ProductID
select p
;
query = query.Take(50);
_Products = query.ToList <Product>();
return(_Products);
}
}
/// <summary>
/// List of customer having more than 19 sales order
/// </summary>
/// <returns></returns>
public IPagedList <CustomersWithOrders> getPremierCustomers(int pagenumber, int pageSize)
{
IPagedList <CustomersWithOrders> _customers = null;
using (var db = new AdventureWorks2016CTP3Entities())
{
var query = from c in db.Customers
join cd in db.CustomerPIIs on c.CustomerID equals cd.CustomerID
join sh in db.SalesOrderHeaders on c.CustomerID equals sh.CustomerID
//join be in db.BusinessEntityAddresses on c.CustomerID equals be.BusinessEntityID
join a in db.Addresses on sh.BillToAddressID equals a.AddressID
group new { c, cd, sh, a } by new
{
c.CustomerID,
cd.FirstName,
cd.LastName,
cd.EmailAddress,
cd.PhoneNumber,
a.AddressLine1,
a.AddressLine2,
a.City,
a.PostalCode,
a.AddressID
} into grp
where grp.Count() > 19
select new CustomersWithOrders()
{
Count = grp.Count(),
CustomerID = grp.Key.CustomerID,
FirstName = grp.Key.FirstName,
LastName = grp.Key.LastName,
EmailAddress = grp.Key.EmailAddress,
PhoneNumber = grp.Key.PhoneNumber,
FullAddress = grp.Key.AddressLine1 + " " + grp.Key.AddressLine2 + " " + grp.Key.City + " " + grp.Key.PostalCode,
AddressID = grp.Key.AddressID
};
query = query.OrderBy(CustomersWithOrders => CustomersWithOrders.CustomerID);
_customers = query.ToPagedList(pagenumber, pageSize);
}
return(_customers);
}
public List <CustomerAddress> getCustomersAddress(int pCustomerID)
{
List <CustomerAddress> _customersAddress = null;
using (var db = new AdventureWorks2016CTP3Entities())
{
var query = from c in db.Customers
join be in db.BusinessEntityAddresses on c.CustomerID equals be.BusinessEntityID
join a in db.Addresses on be.AddressID equals a.AddressID
where c.CustomerID == pCustomerID
select new CustomerAddress()
{
AddressLine1 = a.AddressLine1,
AddressLine2 = a.AddressLine2,
City = a.City,
PostalCode = a.PostalCode,
AddressID = a.AddressID,
StateProvinceID = a.StateProvinceID
};
query = query.OrderBy(CustomerAddress => CustomerAddress.AddressID);
_customersAddress = query.ToList <CustomerAddress>();
}
return(_customersAddress);
}
