using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.Odbc;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.Services;

namespace CRMPortal
{
    public partial class CheckInventory : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        [WebMethod]
        public static string GetInventoryItems(string size)
        {
            size = size.ToUpper();

            DataTable results = new DataTable("Inventory");
            
            OdbcConnection odbc = new OdbcConnection();
            odbc.ConnectionString = Logic.odbcString;
            odbc.Open();

            string query = "SELECT TOP 100 ITEM, ISIZE, IDESC, PRIC1, COSTC FROM INVMAS WHERE ISIZE LIKE '%"+(size.Equals("")?"":size+"%")+"'";
            OdbcCommand cmd = new OdbcCommand(query, odbc);
            OdbcDataReader r = cmd.ExecuteReader();

            results.Load(r);
            cmd.Dispose();
            r.Dispose();
            
            System.Web.Script.Serialization.JavaScriptSerializer serializer = new System.Web.Script.Serialization.JavaScriptSerializer();
            /*
            List<List<string>> rows = new List<List<string>>();
            List<string> row;
            foreach (DataRow dr in results.Rows)
            {
                row = new List<string>();
                foreach (DataColumn col in results.Columns)
                {
                    row.Add(dr[col].ToString());
                }

                

                rows.Add(row);
            }
            */
            
            List<Dictionary<string, object>> rows = new List<Dictionary<string, object>>();
            Dictionary<string, object> row;
            foreach (DataRow dr in results.Rows)
            {
                row = new Dictionary<string, object>();
                foreach (DataColumn col in results.Columns)
                {
                    row.Add(col.ColumnName, dr[col]);
                }
                
                query = "SELECT SUM(CAST(SLSQTY AS INT)) AS QUANTITY FROM INVLOC WHERE SITEM = '" + row["ITEM"] + "'";
                cmd = new OdbcCommand(query, odbc);
                r = cmd.ExecuteReader();
                r.Read();
                row.Add("QTY", r.GetValue(0).ToString());
                r.Dispose();
                cmd.Dispose();

                List<List<string>> stores = new List<List<string>>();
                query = "SELECT SSTOR, SLSQTY FROM INVLOC WHERE SITEM = '"+row["ITEM"]+"' AND SLSQTY > 0";
                cmd = new OdbcCommand(query, odbc);
                r = cmd.ExecuteReader();
                while (r.Read())
                {
                    List<string> store = new List<string>();
                    store.Add(r.GetValue(0).ToString());
                    store.Add(r.GetValue(1).ToString());
                    stores.Add(store);
                }
                row.Add("stores", stores);

                rows.Add(row);
            }
            
            odbc.Close();
            return serializer.Serialize(rows);
        }
    }
}