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"));
}
