//public ActionResult DailySell()
//{
// Product_Sell sell = new Product_Sell();
// if (sell.sellEndDate == DateTime.Today)
// {
// list = db.Product_Sells.OrderBy(x => x.sellEndDate).ToList();
// }
// return View(list);
//}
public ActionResult DailySell(int?page)
{
Product_Sell sell = new Product_Sell();
var list = db.Product_Sells.OrderByDescending(x => x.sellEndDate).ToList().ToPagedList(page ?? 1, 50);
return(View(list));
}
public async Task <ActionResult <Product_Sell> > GetProduct_Sell(int id)
{
var data = await _db.Product_Sell.Where(ps => ps.Product_SellId == id).Select(s => new { s.CategoryId, s.CustomerId, s.DateOfSell, s.Discount_Percentage, s.EmployeeId, s.Product_SellId, s.Quantity, s.SubTotalCost, s.UnitPrice }).FirstOrDefaultAsync();
Product_Sell editProductSellObject = new Product_Sell()
{
Product_SellId = data.Product_SellId,
CategoryId = data.CustomerId,
CustomerId = data.CustomerId,
EmployeeId = data.EmployeeId,
Quantity = data.Quantity,
UnitPrice = data.UnitPrice,
Discount_Percentage = data.Discount_Percentage,
SubTotalCost = data.SubTotalCost,
DateOfSell = data.DateOfSell
};
if (editProductSellObject == null)
{
return(NotFound());
}
return(editProductSellObject);
}
public async Task <ActionResult <string> > PostProduct_Sell(Product_Sell pSell)
{
double subTotal = 0;
subTotal = Convert.ToDouble((pSell.Quantity * pSell.UnitPrice) - pSell.Discount_Percentage);
string msg = null;
if (pSell.Discount_Percentage <= 0)
{
pSell.Discount_Percentage = 0;
}
Product_Sell SellProduct = new Product_Sell()
{
Product_SellId = 0,
Quantity = pSell.Quantity,
UnitPrice = pSell.UnitPrice,
Discount_Percentage = pSell.Discount_Percentage,
SubTotalCost = subTotal,
DateOfSell = Convert.ToDateTime(DateTime.Now),
CategoryId = pSell.CategoryId,
EmployeeId = pSell.EmployeeId,
CustomerId = pSell.CustomerId,
};
_db.Product_Sell.Add(SellProduct);
int response = await _db.SaveChangesAsync();
if (response > 0)
{
msg = "Inserted Record Successfully!!";
}
else
{
msg = "Invalid data";
}
return(msg);
}
//------IN EVERY MONTH HOW MANY TIME A SPECIFIC PRODUCT HAS BEEN SEEL--------
public ActionResult BuyProduct(int?id)
{
product q = null;
Product_Sell r = new Product_Sell();
q = db.products.Find(id);
if (id != null)
{
if (q == null)
{
return(HttpNotFound());
}
else
{
if (q.Buy > 0) // buy use korchi koita product ase er jonno
{
var Buy_status = db.Database.ExecuteSqlCommand("UPDATE [product] SET Buy = {0}, count = {1},sellEndDate={2} WHERE productId={3}", q.Buy - 1, q.count + 1, q.sellEndDate = DateTime.Now, q.productId);
r.productId = q.productId;
r.productName = q.productName;
r.image = q.image;
r.sellEndDate = DateTime.Now;
r.price = q.price;
var pr = ((q.count * 100) / q.click); //ekhane count ta buy
//if(pr>100)
//{
// pr = 100;
//}
var predect = db.Database.ExecuteSqlCommand("UPDATE [product] SET predection = {0} WHERE productId={1}", q.predection = pr, q.productId); //predection ta daynamic
db.Product_Sells.Add(r); // ja sell hocche seta table e add hocche
var date = DateTime.Now.Date.Day;
var notnow = DateTime.Now;
var monthName1 = notnow.ToString("MMMM");
if (monthName1 == "January")
{
var status = db.Database.ExecuteSqlCommand("UPDATE [product] SET January = {0} WHERE productId={1}", q.January + 1, q.productId);
var pred = (((q.January * 31) / date) + q.December + q.October + q.November) / 4;
var mod = (((q.January * 31) / date) + q.December + q.October + q.November) % 4;
pred = pred + mod;
var st = db.Database.ExecuteSqlCommand("UPDATE [product] SET PredectionResult = {0} WHERE productId={1}", q.PredectionResult = pred, q.productId);
db.SaveChanges();
if (q.PredectionResult > q.Buy)
{
var pre = q.PredectionResult;
var b = q.Buy;
var result = pre - b;
var status1 = db.Database.ExecuteSqlCommand("UPDATE [product] SET Result = {0} WHERE productId={1}", q.Result = result, q.productId);
}
//db.products.Add(q);
db.SaveChanges();
}
else if (monthName1 == "February")
{
var status1 = db.Database.ExecuteSqlCommand("UPDATE [product] SET February = {0} WHERE productId={1}", q.February + 1, q.productId);
var pred = (((q.February * 28) / date) + q.January + q.December + q.November) / 4;
var mod = (((q.February * 28) / date) + q.January + q.December + q.November) % 4;
pred = pred + mod;
var st = db.Database.ExecuteSqlCommand("UPDATE [product] SET PredectionResult = {0} WHERE productId={1}", q.PredectionResult = pred, q.productId);
db.SaveChanges();
if (q.PredectionResult > q.Buy)
{
var pre = q.PredectionResult;
var b = q.Buy;
var result = pre - b;
var status
= db.Database.ExecuteSqlCommand("UPDATE [product] SET Result = {0} WHERE productId={1}", q.Result = result, q.productId);
}
//db.products.Add(q);
db.SaveChanges();
}
else if (monthName1 == "March")
{
var status1 = db.Database.ExecuteSqlCommand("UPDATE [product] SET March = {0} WHERE productId={1}", q.March + 1, q.productId);
var pred = (q.February + q.January + q.December + ((q.March * 31) / date)) / 4;
var mod = (q.February + q.January + q.December + ((q.March * 31) / date)) % 4;
pred = pred + mod;
var st = db.Database.ExecuteSqlCommand("UPDATE [product] SET PredectionResult = {0} WHERE productId={1}", q.PredectionResult = pred, q.productId);
db.SaveChanges();
if (q.PredectionResult > q.Buy)
{
var pre = q.PredectionResult;
var b = q.Buy;
var result = pre - b;
var status = db.Database.ExecuteSqlCommand("UPDATE [product] SET Result = {0} WHERE productId={1}", q.Result = result, q.productId);
}
//db.products.Add(q);
db.SaveChanges();
}
else if (monthName1 == "April")
{
var status1 = db.Database.ExecuteSqlCommand("UPDATE [product] SET April = {0} WHERE productId={1}", q.April + 1, q.productId);
var pred = (q.February + q.January + ((q.April * 30) / date) + q.March) / 4;
var mod = (q.February + q.January + ((q.April * 30) / date) + q.March) % 4;
pred = pred + mod;
var st = db.Database.ExecuteSqlCommand("UPDATE [product] SET PredectionResult = {0} WHERE productId={1}", q.PredectionResult = pred, q.productId);
db.SaveChanges();
if (q.PredectionResult > q.Buy)
{
var pre = q.PredectionResult;
var b = q.Buy;
var result = pre - b;
var status = db.Database.ExecuteSqlCommand("UPDATE [product] SET Result = {0} WHERE productId={1}", q.Result = result, q.productId);
}
//db.products.Add(q);
db.SaveChanges();
}
else if (monthName1 == "May")
{
var status1 = db.Database.ExecuteSqlCommand("UPDATE [product] SET May = {0} WHERE productId={1}", q.May + 1, q.productId);
var pred = (q.February + ((q.May * 31) / date) + q.April + q.March) / 4;
var mod = (q.February + ((q.May * 31) / date) + q.April + q.March) % 4;
pred = pred + mod;
var st = db.Database.ExecuteSqlCommand("UPDATE [product] SET PredectionResult = {0} WHERE productId={1}", q.PredectionResult = pred, q.productId);
db.SaveChanges();
if (q.PredectionResult > q.Buy)
{
var pre = q.PredectionResult;
var b = q.Buy;
var result = pre - b;
var status = db.Database.ExecuteSqlCommand("UPDATE [product] SET Result = {0} WHERE productId={1}", q.Result = result, q.productId);
}
//db.products.Add(q);
db.SaveChanges();
}
else if (monthName1 == "June")
{
var status1 = db.Database.ExecuteSqlCommand("UPDATE [product] SET June = {0} WHERE productId={1}", q.June + 1, q.productId);
var pred = (((q.June * 30) / date) + q.May + q.April + q.March) / 4;
var mod = (((q.June * 30) / date) + q.May + q.April + q.March) % 4;
pred = pred + mod;
var st = db.Database.ExecuteSqlCommand("UPDATE [product] SET PredectionResult = {0} WHERE productId={1}", q.PredectionResult = pred, q.productId);
db.SaveChanges();
if (q.PredectionResult > q.Buy)
{
var pre = q.PredectionResult;
var b = q.Buy;
var result = pre - b;
var status = db.Database.ExecuteSqlCommand("UPDATE [product] SET Result = {0} WHERE productId={1}", q.Result = result, q.productId);
}
//db.products.Add(q);
db.SaveChanges();
}
else if (monthName1 == "July")
{
var status1 = db.Database.ExecuteSqlCommand("UPDATE [product] SET November = {0} WHERE productId={1}", q.July + 1, q.productId);
var pred = (((q.July * 31) / date) + q.May + q.April + q.July) / 4;
var mod = (((q.July * 31) / date) + q.May + q.April + q.July) % 4;
pred = pred + mod;
var st = db.Database.ExecuteSqlCommand("UPDATE [product] SET PredectionResult = {0} WHERE productId={1}", q.PredectionResult = pred, q.productId);
db.SaveChanges();
if (q.PredectionResult > q.Buy)
{
var pre = q.PredectionResult;
var b = q.Buy;
var result = pre - b;
var status = db.Database.ExecuteSqlCommand("UPDATE [product] SET Result = {0} WHERE productId={1}", q.Result = result, q.productId);
}
//db.products.Add(q);
db.SaveChanges();
}
else if (monthName1 == "August")
{
var status1 = db.Database.ExecuteSqlCommand("UPDATE [product] SET August = {0} WHERE productId={1}", q.August + 1, q.productId);
var pred = (q.June + q.May + ((q.August * 31) / date) + q.July) / 4;
var mod = (q.June + q.May + ((q.August * 31) / date) + q.July) % 4;
pred = pred + mod;
var st = db.Database.ExecuteSqlCommand("UPDATE [product] SET PredectionResult = {0} WHERE productId={1}", q.PredectionResult = pred, q.productId);
db.SaveChanges();
if (q.PredectionResult > q.Buy)
{
var pre = q.PredectionResult;
var b = q.Buy;
var result = pre - b;
var status = db.Database.ExecuteSqlCommand("UPDATE [product] SET Result = {0} WHERE productId={1}", q.Result = result, q.productId);
}
//db.products.Add(q);
db.SaveChanges();
}
else if (monthName1 == "September")
{
var status1 = db.Database.ExecuteSqlCommand("UPDATE [product] SET September = {0} WHERE productId={1}", q.September + 1, q.productId);
var pred = (q.June + ((q.September * 30) / date) + q.August + q.July) / 4;
var mod = (q.June + ((q.September * 30) / date) + q.August + q.July) % 4;
pred = pred + mod;
var st = db.Database.ExecuteSqlCommand("UPDATE [product] SET PredectionResult = {0} WHERE productId={1}", q.PredectionResult = pred, q.productId);
db.SaveChanges();
if (q.PredectionResult > q.Buy)
{
var pre = q.PredectionResult;
var b = q.Buy;
var result = pre - b;
var status = db.Database.ExecuteSqlCommand("UPDATE [product] SET Result = {0} WHERE productId={1}", q.Result = result, q.productId);
}
//db.products.Add(q);
db.SaveChanges();
}
else if (monthName1 == "October")
{
var status1 = db.Database.ExecuteSqlCommand("UPDATE [product] SET October = {0} WHERE productId={1}", q.October + 1, q.productId);
var pred = (((q.October * 31) / date) + q.September + q.August + q.July) / 4;
var mod = (((q.October * 31) / date) + q.September + q.August + q.July) % 4;
pred = pred + mod;
var st = db.Database.ExecuteSqlCommand("UPDATE [product] SET PredectionResult = {0} WHERE productId={1}", q.PredectionResult = pred, q.productId);
db.SaveChanges();
if (q.PredectionResult > q.Buy)
{
var pre = q.PredectionResult;
var b = q.Buy;
var result = pre - b;
var status = db.Database.ExecuteSqlCommand("UPDATE [product] SET Result = {0} WHERE productId={1}", q.Result = result, q.productId);
}
//db.products.Add(q);
db.SaveChanges();
}
else if (monthName1 == "November")
{
var status1 = db.Database.ExecuteSqlCommand("UPDATE [product] SET November = {0} WHERE productId={1}", q.November + 1, q.productId);
db.SaveChanges();
var pred = (q.August + q.September + q.October + ((q.November * 30) / date)) / 4;
var mod = (q.August + q.September + q.October + ((q.November * 30) / date)) % 4;
pred = pred + mod;
var st = db.Database.ExecuteSqlCommand("UPDATE [product] SET PredectionResult = {0} WHERE productId={1}", q.PredectionResult = pred, q.productId);
if (q.PredectionResult > q.Buy)
{
var pre = q.PredectionResult;
var b = q.Buy;
var result = pre - (b);
var status = db.Database.ExecuteSqlCommand("UPDATE [product] SET Result = {0} WHERE productId={1}", q.Result = result, q.productId);
}
//db.products.Add(q);
db.SaveChanges();
}
else if (monthName1 == "December")
{
var status1 = db.Database.ExecuteSqlCommand("UPDATE [product] SET December = {0} WHERE productId={1}", q.December + 1, q.productId);
var pred = (((q.December * 31) / date) + q.September + q.October + q.November) / 4;
var mod = (((q.December * 31) / date) + q.September + q.October + q.November) % 4;
pred = pred + mod;
var st = db.Database.ExecuteSqlCommand("UPDATE [product] SET PredectionResult = {0} WHERE productId={1}", q.PredectionResult = pred, q.productId);
db.SaveChanges();
if (q.PredectionResult > q.Buy)
{
var pre = q.PredectionResult;
var b = q.Buy;
var result = pre - b;
var status = db.Database.ExecuteSqlCommand("UPDATE [product] SET Result = {0} WHERE productId={1}", q.Result = result, q.productId);
}
//db.products.Add(q);
db.SaveChanges();
}
db.SaveChanges();
}
else
{
return(RedirectToAction("Index", "Product"));
}
}
}
return(View(q));
}
public async Task <ActionResult <string> > PutProduct_Sell(int id, Product_Sell editSell)
{
if (id != editSell.Product_SellId)
{
return(BadRequest());
}
double subTotal = 0;
subTotal = Convert.ToDouble((editSell.Quantity * editSell.UnitPrice) - editSell.Discount_Percentage);
string msg = null;
if (editSell.Discount_Percentage <= 0)
{
editSell.Discount_Percentage = 0;
}
Product_Sell EditSellProduct = new Product_Sell()
{
Product_SellId = editSell.Product_SellId,
Quantity = editSell.Quantity,
UnitPrice = editSell.UnitPrice,
Discount_Percentage = editSell.Discount_Percentage,
SubTotalCost = subTotal,
DateOfSell = Convert.ToDateTime(DateTime.Now),
CategoryId = editSell.CategoryId,
EmployeeId = editSell.EmployeeId,
CustomerId = editSell.CustomerId,
};
_db.Entry(EditSellProduct).State = EntityState.Modified;
try
{
int editResponse = await _db.SaveChangesAsync();
if (editResponse > 0)
{
msg = "Updated Record Successfully!!";
}
else
{
msg = "update operation Unsuccessfull";
}
}
catch (DbUpdateConcurrencyException)
{
if (!Product_SellExists(id))
{
return(NotFound());
}
else
{
throw;
}
}
return(msg);
}
public async Task <ActionResult <IEnumerable <VmProductSellDetails> > > Monthly_Product_SellList(VmSearchParams searchParams)
{
List <VmProductSellDetails> vmList = new List <VmProductSellDetails>();
List <Product_Sell> productSellList = new List <Product_Sell>();
var dataList = await _db.Product_Sell.Where(cp => cp.DateOfSell.Year == searchParams.DateOfSell.Year && cp.DateOfSell.Month == searchParams.DateOfSell.Month).ToListAsync();
foreach (var data in dataList)
{
Product_Sell psel = new Product_Sell()
{
Product_SellId = data.Product_SellId,
Quantity = data.Quantity,
UnitPrice = data.UnitPrice,
Discount_Percentage = data.Discount_Percentage,
SubTotalCost = data.SubTotalCost,
DateOfSell = data.DateOfSell,
CategoryId = data.CategoryId,
EmployeeId = data.EmployeeId,
CustomerId = data.CustomerId
};
productSellList.Add(psel);
}
foreach (var data in productSellList)
{
var emp = await _db.Employee.Where(e => e.EmployeeId == data.EmployeeId).Select(e => new { e.EmpAddress, e.EmployeeId, e.EmpMobileNo, e.EmpName, e.GenderId, e.ReligionId, e.ThanaId }).FirstOrDefaultAsync();
var category = await _db.Category.Where(c => c.CategoryId == data.CategoryId).Select(c => new { c.CategoryId, c.CategoryName, c.BrandId }).FirstOrDefaultAsync();
var brand = await _db.Brand.Where(b => b.BrandId == category.BrandId).Select(b => new { b.BrandId, b.BrandName, b.ProductId }).FirstOrDefaultAsync();
var product = await _db.Product.Where(p => p.ProductId == brand.ProductId).Select(p => new { p.ProductId, p.ProductName }).FirstOrDefaultAsync();
var customer = await _db.Customer.Where(cs => cs.CustomerId == data.CustomerId).Select(c => new { c.CustomerId, c.Address, c.CustomerMobileNo, c.CustomerName }).FirstOrDefaultAsync();
VmProductSellDetails vm = new VmProductSellDetails()
{
Product_SellId = data.Product_SellId,
Quantity = data.Quantity,
UnitPrice = data.UnitPrice,
Discount_Percentage = data.Discount_Percentage,
SubTotalCost = data.SubTotalCost,
DateOfSell = data.DateOfSell,
CategoryId = category.CategoryId,
CategoryName = category.CategoryName,
BrandId = brand.BrandId,
BrandName = brand.BrandName,
ProductId = product.ProductId,
ProductName = product.ProductName,
EmployeeId = emp.EmployeeId,
EmpName = emp.EmpName,
CustomerId = customer.CustomerId,
CustomerMobileNo = customer.CustomerMobileNo,
CustomerName = customer.CustomerName
};
vmList.Add(vm);
}
return(vmList);
}
