using ActionTireCo.Crm.Model.View;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using System.Web;
using System.Web.Mvc;

namespace ActionTireCo.Crm.Controllers
{
    public class InventoryController : Controller
    {
        public ActionResult Index()
        {
            return View();
        }

        public async Task<ActionResult> Search(string criteria)
        {
            string query =
                String.Format(
                    "SELECT [INVMAS].[ITEM] AS [Item], CAST([INVLOC].[SSTOR] AS INT) AS [StoreNumber], UPPER(RTRIM(LTRIM([STE].[STNAM]))) AS [StoreName], [INVMAS].[ISIZE] AS [Size], [INVMAS].[IDESC] AS [Description], CAST([INVMAS].[PRIC1] AS MONEY) AS [Price], CAST([INVMAS].[COSTC] AS INT) AS [Cost], CAST(SUM(ISNULL([INVLOC].[SLSQTY],0)) AS BIGINT) AS [Quantity] FROM [XFODBC]..[PUBLIC].[INVMAS] AS [INVMAS] INNER JOIN [XFODBC]..[PUBLIC].[INVLOC] AS [INVLOC] ON [INVMAS].[ITEM] = INVLOC.[SITEM] INNER JOIN [XFODBC]..[PUBLIC].[STORE] AS [STE] ON [INVLOC].[SSTOR] = [STE].[STNUM] WHERE [INVMAS].[ISIZE] LIKE '%{0}%' GROUP BY [INVMAS].[ITEM], CAST([INVLOC].[SSTOR] AS INT), UPPER(RTRIM(LTRIM([STE].[STNAM]))), [INVMAS].[ISIZE], [INVMAS].[IDESC], CAST([INVMAS].[PRIC1] AS MONEY), CAST([INVMAS].[COSTC] AS INT) HAVING CAST(SUM(ISNULL([INVLOC].[SLSQTY],0)) AS BIGINT) > 0 ORDER BY UPPER(RTRIM(LTRIM([STE].[STNAM]))), CAST(SUM(ISNULL([INVLOC].[SLSQTY],0)) AS BIGINT) DESC;",
                    new object[]
                    {
                        criteria
                    }
                );
            ActionTireCoCrmContext context = new ActionTireCoCrmContext();
            return View(
                await context.Database.SqlQuery<InventoryModel>(query).ToListAsync()
            );
        }
    }
}