public List <ItemSellingPrice> GetItemSellingPricesReport(int OrganizationId) { using (IDbConnection connection = OpenConnection(dataConnection)) { ItemSellingPriceList model = new ItemSellingPriceList(); string sql = @" SELECT I.ItemId, I.ItemName, ISNULL(I.PartNo, '-') PartNo, CategoryName,ItemGroupName,ItemSubGroupName,UnitName, ISP.SellingPrice,Round(sum(Amount)/sum(Rate),2)as Average FROM Item I INNER JOIN ItemCategory IC ON IC.itmCatId=I.ItemCategoryId INNER JOIN ItemGroup IG ON IG.ItemGroupId=I.ItemGroupId INNER JOIN ItemSubGroup ISG ON ISG.ItemSubGroupId=I.ItemSubGroupId INNER JOIN Unit U ON U.UnitId=I.ItemUnitId LEFT JOIN GRNItem GR ON I.ItemId=GR.ItemId LEFT JOIN ItemSellingPrice ISP ON I.ItemId = ISP.ItemId WHERE I.isActive=1 GROUP BY I.itemid, I.ItemName,I.PartNo, CategoryName,ItemGroupName,ItemSubGroupName,UnitName, ISP.SellingPrice ORDER BY ItemName; "; return(connection.Query <ItemSellingPrice>(sql, new { OrganizationId = OrganizationId }).ToList()); } }
// GET: ItemSellingPrice public ActionResult Index() { ItemSellingPriceList obj = new ItemSellingPriceList(); ItemSellingPriceRepository repo = new ItemSellingPriceRepository(); obj = repo.GetItemSellingPrices(OrganizationId); return(View(obj)); }
public List <ItemSellingPrice> GetOrganization(int OrganizationId) { using (IDbConnection connection = OpenConnection(dataConnection)) { ItemSellingPriceList model = new ItemSellingPriceList(); string sql = @"select * from organization where OrganizationId=@OrganizationId"; return(connection.Query <ItemSellingPrice>(sql, new { OrganizationId = OrganizationId }).ToList()); } }
public ItemSellingPriceList GetItemSellingPrices(int OrganizationId) { using (IDbConnection connection = OpenConnection(dataConnection)) { ItemSellingPriceList model = new ItemSellingPriceList(); // string sql = @"SELECT // I.ItemId, // I.ItemName, // ISNULL(I.PartNo, '-') PartNo, // CategoryName,ItemGroupName,ItemSubGroupName,UnitName, // ISP.SellingPrice // FROM // Item I // INNER JOIN ItemCategory ON itmCatId=ItemCategoryId // INNER JOIN ItemGroup G ON I.ItemGroupId=G.ItemGroupId // INNER JOIN ItemSubGroup S ON I.ItemSubGroupId=S.ItemSubGroupId // INNER JOIN Unit U ON U.UnitId=I.ItemUnitId // LEFT JOIN ItemSellingPrice ISP ON I.ItemId = ISP.ItemId // WHERE // I.isActive=1 // order by ItemName;"; string sql = @" SELECT I.ItemId, I.ItemName, ISNULL(I.PartNo, '-') PartNo, CategoryName,ItemGroupName,ItemSubGroupName,UnitName, ISP.SellingPrice,Round(sum(Amount)/sum(Rate),2)as Average FROM Item I INNER JOIN ItemCategory IC ON IC.itmCatId=I.ItemCategoryId INNER JOIN ItemGroup IG ON IG.ItemGroupId=I.ItemGroupId INNER JOIN ItemSubGroup ISG ON ISG.ItemSubGroupId=I.ItemSubGroupId INNER JOIN Unit U ON U.UnitId=I.ItemUnitId LEFT JOIN GRNItem GR ON I.ItemId=GR.ItemId LEFT JOIN ItemSellingPrice ISP ON I.ItemId = ISP.ItemId WHERE I.isActive=1 GROUP BY I.itemid, I.ItemName,I.PartNo, CategoryName,ItemGroupName,ItemSubGroupName,UnitName, ISP.SellingPrice ORDER BY ItemName; "; // var objItemSellingPrices = connection.Query<ItemSellingPrice>(sql, new { OrganizationId = OrganizationId }).ToList<ItemSellingPrice>(); model.ItemSellingPriceLists = connection.Query <ItemSellingPrice>(sql, new { OrganizationId = OrganizationId, }).ToList <ItemSellingPrice>(); return(model); } }
public ActionResult Save(ItemSellingPriceList model) { foreach (ItemSellingPrice item in model.ItemSellingPriceLists) { item.CreatedDate = System.DateTime.Now; item.CreatedBy = UserID.ToString(); item.OrganizationId = OrganizationId; } var rtn = new ItemSellingPriceRepository().InsertItemSellingPrice(model.ItemSellingPriceLists); TempData["Success"] = "Added Successfully!"; return(RedirectToAction("Index")); }