using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlServerCe;
using SinosysHardware.DataAccess;
using System.Windows.Forms;
namespace SinosysHardware.Business
{
    class OrderDB
    {


        /// <summary>
        /// Retrieve a Collection of Order objects
        /// </summary>
        /// <param name="tomorrow"></param>
        /// <param name="orderState"></param>
        /// <returns></returns>
        public DataSet  GetOrders(Services.OrderState orderState)
        {
            //Get int value of OrderState enum
            int _orderState = (int)orderState;
            string Sql=null;
            HelperDB hdb = new HelperDB();
            //If you're looking for orders from any date
          
                //If you're looking for all orders
                if (_orderState == 6)
                {
                    //Set the SQL statement
                    Sql = "SELECT Orders.OrderId As OrderID, Customers.Name as CustomerName, Orders.DeliveryDate as DeliveryDate, OrderState.Name as OrderStateName, Orders.CustomerId as CustomerID " +
                                       "FROM Orders, Customers, OrderState " +
                                       "WHERE Orders.CustomerId = Customers.CustomerId " +
                                       "AND Orders.OrderState = OrderState.OrderStateId " +
                                       "AND Orders.OrderState > 0";
                    DataSet Ds = hdb.GetDataSet(Sql, "ThreeTable");
                    return Ds;
                }
                //If you're looking for specific orders
                else
                {
                    //Set the SQL statement
                    Sql = "SELECT Orders.OrderId As OrderID, Customers.Name as CustomerName, Orders.DeliveryDate as DeliveryDate, OrderState.Name as OrderStateName, Orders.CustomerId as CustomerID" +
                                       " FROM Orders, Customers, OrderState " +
                                       "WHERE Orders.CustomerId = Customers.CustomerId " +
                                       "AND Orders.OrderState = OrderState.OrderStateId " +
                                       "AND Orders.OrderState = @OrderState";
                    //Add Paramters plus values
                   SqlCeParameter paras = new SqlCeParameter("@OrderState", _orderState);
                   DataSet Ds = hdb.GetDataSet(Sql, "ThreeTable", paras);
                   return Ds;
               
                }
            
        }


        /// <summary>
        /// Create a new Order and associated OrderDetails
        /// </summary>
        /// <param name="customerId"></param>
        /// <param name="deliveryDate"></param>
        /// <param name="orderState"></param>
        /// <param name="orderItems"></param>
        public void CreateOrder(int customerId, string deliveryDate, int orderState, ListView.ListViewItemCollection orderItems)
        {
            string Sql = "INSERT INTO Orders (CustomerId, DeliveryDate, OrderState) VALUES (@CustomerId, @DeliveryDate, @OrderState);select @@Identity as orderid";

            string[] sqlstring = Sql.Split(';'); ;
            SqlCeParameter[] paras1 = new SqlCeParameter[3];
            //Add Paramters plus values
            paras1[0] = new SqlCeParameter("@CustomerId", customerId);
            paras1[1] = new SqlCeParameter("@DeliveryDate", deliveryDate);
            paras1[2] = new SqlCeParameter("@OrderState", orderState);
            HelperDB hdb = new HelperDB();

            //Retrieve value of Identity column that was just incremented
            int _identity = Convert.ToInt32(hdb.ExcuteScaleID(sqlstring, paras1));
          
            //Iterate through collection
            foreach (ListViewItem item in orderItems)
            {
                //Set the SQL statement
                string sql = "INSERT INTO OrderDetails (OrderId, InventoryId, Quantity) VALUES (@OrderId, @InventoryId, @Quantity)";
                SqlCeParameter[] paras2 = new SqlCeParameter[3];
                //Add Paramters plus values
                paras2[0] = new SqlCeParameter("@OrderId", _identity);
                paras2[1] = new SqlCeParameter("@InventoryId", int.Parse(item.SubItems[2].Text));
                paras2[2] = new SqlCeParameter("@Quantity", int.Parse(item.SubItems[0].Text));

                //Insert OrderDetails data
                hdb.ExcuteSql(sql, paras2);
            }
        }

        /// <summary>
        /// Update the current state of an order 
        /// </summary>
        /// <param name="orderId"></param>
        /// <param name="orderState"></param>
        public  void UpdateOrderState(int orderId, Services.OrderState orderState)
        {
            //Update OrderState value
            int _orderState = (int)orderState;
            string Sql = "UPDATE Orders SET OrderState = @OrderState WHERE OrderId = @OrderId";
            SqlCeParameter[] paras = new SqlCeParameter[2];
            paras[0] = new SqlCeParameter("@OrderState", _orderState);
            paras[1] = new SqlCeParameter("@OrderId", orderId);
            HelperDB hdb = new HelperDB();
            hdb.ExcuteSql(Sql, paras);
        }
    }
}
