public static IEnumerable <Statistics> CountTotalProducts(IMSEntities db)
{
string objs = @"
select sum(QuantityRemaining) as 'TotalProducts' from Stock";
var result = db.Database.SqlQuery <Statistics>(objs);
return(result);
}
public static IEnumerable <Statistics> CountCustomers(IMSEntities db)
{
string objs = @"
select count(UserAccountID) as 'TotalCustomers' from UserAccounts where usertypeid = 3";
var result = db.Database.SqlQuery <Statistics>(objs);
return(result);
}
public static IEnumerable <Statistics> CountSuppliers(IMSEntities db)
{
string objs = @"
select count(supplierid) as 'TotalSuppliers' from Suppliers";
var result = db.Database.SqlQuery <Statistics>(objs);
return(result);
}
public static IEnumerable <ProductList> GetItemsOutOfStock(IMSEntities db)
{
string objs = @"
select p.ProductName, s.QuantityRemaining from stock s
inner join Product p on s.productid=p.ProductID where s.QuantityRemaining = 0";
var result = db.Database.SqlQuery <ProductList>(objs);
return(result);
}
public static IEnumerable <Statistics> Sales(IMSEntities db)
{
string objs = @"
select sum(BillingAmount) as 'TotalSales' from Sales s
where s.DateCreated > (CAST(CAST(GETDATE() AS DATE) AS DATETIME))";
var result = db.Database.SqlQuery <Statistics>(objs);
return(result);
}
public static IEnumerable <ProductCount> Count4(IMSEntities db)
{
string objs = @"
select sum(s.QuantityRemaining) as 'Total' from Product p
inner join stock s on p.productid = s.ProductID
inner join ProductType pt on p.ProductTypeID = pt.ProductTypeID
where pt.ProductTypeID = 4";
var result = db.Database.SqlQuery <ProductCount>(objs);
return(result);
}
public static IEnumerable <PurchaseInfo> InactiveCustomerList(IMSEntities db)
{
string objs = @"
select distinct ua.UserAccountID, ua.Email, ud.FirstName, ud.LastName, ps.DateCreated as 'LastPurchase'
from ProductSales ps
inner join Sales s on ps.SalesID = s.SalesID
inner join UserAccounts ua on s.UserAccountID = ua.UserAccountID
inner join UserDetails ud on ua.UserAccountID = ud.UserAccountID
where ps.DateCreated <= (GETDATE()-31)";
var result = db.Database.SqlQuery <PurchaseInfo>(objs);
return(result);
}
public static IEnumerable <ProductList> GetItemsNotSold31Days(IMSEntities db)
{
string objs = @"
select distinct p.ProductID, p.ProductName, p.Description, pt.ProductTypeName, ps.DateCreated as 'LastSold'
from ProductSales ps
inner join product p on ps.ProductID = p.ProductID
inner join ProductType pt on p.ProductTypeID = pt.ProductTypeID
inner join stock s on ps.ProductID = s.ProductID
where ps.DateCreated <= (GETDATE() - 31)
AND s.QuantityRemaining != 0";
var result = db.Database.SqlQuery <ProductList>(objs);
return(result);
}
public static IEnumerable <ProductDetails> GetProductDetails(IMSEntities db, int?ProductID)
{
SqlParameter proParam = new SqlParameter("ProductID", ProductID);
string objs = @"
select p.ProductID, p.ProductName, pt.ProductTypeName, p.PricePerItem, p.Description, p.DateCreated, s.QuantityRemaining
from product p
inner join stock s on p.ProductID = s.ProductID
inner join producttype pt on p.ProductTypeID = pt.ProductTypeID where p.ProductID = @ProductID";
object[] parameters = new object[] { proParam };
var result = db.Database.SqlQuery <ProductDetails>(objs, parameters);
return(result);
}
public static IEnumerable <ProductList> GetItemsStocked(IMSEntities db, int?ProductTypeID)
{
SqlParameter proTypeParam = new SqlParameter("ProductTypeID", ProductTypeID);
string objs = @"
select s.ProductId, p.ProductName, s.QuantityRemaining from ProductType pt
inner join Product p on pt.ProductTypeID = p.ProductTypeID
inner join stock s on p.ProductID = s.ProductID
where pt.ProductTypeID = @ProductTypeID";
object[] parameters = new object[] { proTypeParam };
var result = db.Database.SqlQuery <ProductList>(objs, parameters);
return(result);
}
public static IEnumerable <Billing> GetBill(IMSEntities db, int?SalesID)
{
SqlParameter proParam = new SqlParameter("SalesID", SalesID);
string objs = @"
select p.ProductName, p.PricePerItem, s.BillingDate, s.BillingAmount, ps.Quantity, s.SalesID
from product p
inner join productsales ps on p.ProductID = ps.ProductID
inner join sales s on ps.SalesID = s.SalesID where ps.SalesID = @SalesID";
object[] parameters = new object[] { proParam };
var result = db.Database.SqlQuery <Billing>(objs, parameters);
return(result);
}
public static IEnumerable <PurchaseInfo> GetPurchaseInfo(IMSEntities db, int?UserAccountID)
{
SqlParameter uaIDParam = new SqlParameter("UserAccountID", UserAccountID);
string objs = @"
select ua.UserAccountID, ud.firstname, ud.LastName, p.ProductName, ps.quantity, s.billingdate from UserDetails ud
inner join UserAccounts ua on ud.UserAccountID=ua.UserAccountID
inner join sales s on ua.UserAccountID=s.UserAccountID
inner join ProductSales ps on ps.SalesID=s.SalesID
inner join Product p on ps.ProductID=p.ProductID
where s.BillingDate>= (GETDATE()-31) and ua.useraccountid = @UserAccountID";
object[] parameters = new object[] { uaIDParam };
var result = db.Database.SqlQuery <PurchaseInfo>(objs, parameters);
return(result);
}