public List <InventoryReportModel> GetInventoryReport(InventoryReportParamModel inventoryReportParamModel)
{
List <InventoryReportModel> inventoryReportModel = new List <InventoryReportModel>();
using (SqlConnection con = new SqlConnection(_ConnectionString.Value.ConnectionString))
{
var query = "";
query = " SELECT S.StoreName,INV.Id,F.FoodMenuName as FoodMenuName,FMC.FoodMenuCategoryName ,INV.StockQty, INV.OpeningQty as OpeningQty, " +
" S.StoreName,INV.Id,F.FoodMenuCode,F.FoodMenuName as FoodMenuName,FMC.FoodMenuCategoryName ,INV.StockQty, " +
" F.PurchasePrice, (INV.StockQty * F.PurchasePrice) as Amount , U.Unitname," +
" case when INV.StockQty < 0 THEN 0 else 1 end as StockQtyText,F.AlterQty" +
" FROM inventory INV INNER JOIN FoodMenu F ON INV.FoodMenuId = F.Id" +
" INNER JOIN FoodMenuCategory FMC on FMC.Id = F.FoodCategoryId" +
" inner join Store S on S.Id = INV.StoreId inner join Units U on U.Id = F.UnitsId " +
" where INV.StockQty <> 0 or ISNULL(OpeningQty,0) <>0";
//query = "SELECT S.StoreName,INV.Id,I.IngredientName as FoodMenuName,IC.IngredientCategoryName as FoodMenuCategoryName ,INV.StockQty, INV.OpeningQty as OpeningQty, " +
// " S.StoreName,INV.Id,I.Code as FoodMenuCode ,INV.StockQty, " +
// " I.PurchasePrice, (INV.StockQty * I.PurchasePrice) as Amount , U.Unitname, " +
// " case when INV.StockQty < 0 THEN 0 else 1 end as StockQtyText,I.AlterQty" +
// " FROM inventory INV INNER JOIN Ingredient I ON INV.IngredientId = I.Id " +
// " INNER JOIN IngredientCategory IC on IC.Id = I.IngredientCategoryId " +
// " inner join Store S on S.Id = INV.StoreId inner join Units U on U.Id = I.IngredientUnitId " +
// " where INV.StockQty <> 0 or ISNULL(OpeningQty,0) <> 0;";
inventoryReportModel = con.Query <InventoryReportModel>(query).ToList();
}
return(inventoryReportModel);
}
public List <InventoryDetailReportModel> GetInventoryDetailReport(InventoryReportParamModel inventoryReportParamModel, int id)
{
List <InventoryDetailReportModel> inventoryReportModel = new List <InventoryDetailReportModel>();
using (SqlConnection con = new SqlConnection(_ConnectionString.Value.ConnectionString))
{
var query = " SELECT Convert(varchar(10), ID.DocDate, 103)as DocDate,ID.StoreId,DocType,DocTable,DocNumber,DocNumberId,DocNumberDetailId,SupplierId, " +
" StoreName,SupplierName,ID.FoodMenuId,ID.IngredientId,Reamrks,StockInQty,StockOutQty,BalanceQty " +
" FROM InventoryDetail ID inner join Inventory I ON I.StoreId = ID.Storeid and (I.FoodmenuId = ID.FoodMenuid or I.IngredientID = ID.IngredientID) " +
" inner join Store S ON S.ID = ID.StoreID " +
" left join SUPPLIER SP on SP.Id = ID.SupplierId " +
" WHERE I.ID=" + id + "Order by ID.DocDate asc";
// " where Convert(varchar(10), ID.DocDate, 103) between '01/01/2021' and '01/01/2022'"
//// " WHERE I.AlterQty < INV.StockQty ";
//if (inventoryReportParamModel.StoreId.ToString().Length != 0)
// query = query + " AND INV.StoreId = " + inventoryReportParamModel.StoreId.ToString();
//if (inventoryReportParamModel.FoodMenuId.ToString().Length != 0)
// query = query + " AND FM.Id =" + inventoryReportParamModel.FoodMenuId.ToString();
//if (inventoryReportParamModel.IngredientCategoryId.ToString().Length != 0)
// query = query + " AND I.IngredientCategoryId =" + inventoryReportParamModel.IngredientCategoryId.ToString();
//if (inventoryReportParamModel.IngredientId.ToString().Length != 0)
// query = query + " AND INV.IngredientId = " + inventoryReportParamModel.IngredientId.ToString();
inventoryReportModel = con.Query <InventoryDetailReportModel>(query).ToList();
}
return(inventoryReportModel);
}
public ViewResult Inventory(InventoryReportParamModel inventoryReportParamModel)
{
List <InventoryReportModel> inventoryReportModel = new List <InventoryReportModel>();
inventoryReportModel = _iReportService.GetInventoryReport(inventoryReportParamModel);
return(View(inventoryReportModel));
}
public JsonResult GetInventoryStockList(int supplierId, int storeId, int itemType, int active, string reportDate)
{
List <InventoryReportModel> inventoryReportModel = new List <InventoryReportModel>();
InventoryReportParamModel inventoryReportParamModel = new InventoryReportParamModel();
inventoryReportModel = _iReportService.GetInventoryStockList(supplierId, storeId, itemType, active, reportDate);
return(Json(new { InventoryStockList = inventoryReportModel }));
}
public ActionResult InventoryDetail(int id, string name, string code, string stock, string opening)
{
if (code == "null")
{
code = "";
}
ViewData["FoodMenuName"] = name;
ViewData["FoodMenuCode"] = code;
ViewData["StockQty"] = stock;
ViewData["OpeningQty"] = opening;
List <InventoryDetailReportModel> inventoryReportModel = new List <InventoryDetailReportModel>();
InventoryReportParamModel inventoryReportParamModel = new InventoryReportParamModel();
inventoryReportModel = _iReportService.GetInventoryDetailReport(inventoryReportParamModel, id);
if (inventoryReportModel.Count > 0)
{
ViewData["StoreName"] = inventoryReportModel[0].StoreName;
}
return(View(inventoryReportModel));
}
public List <InventoryDetailReportModel> GetInventoryDetailReport(InventoryReportParamModel inventoryReportParamModel, int id)
{
return(_iReportRepository.GetInventoryDetailReport(inventoryReportParamModel, id));
}
public List <InventoryReportModel> GetInventoryReport(InventoryReportParamModel inventoryReportParamModel)
{
return(_iReportRepository.GetInventoryReport(inventoryReportParamModel));
}