using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Windows.Forms;
using Aram_CRM_Project.BLL.Entity;

namespace Aram_CRM_Project.BLL
{
    class OrderCRUD : Aram_CRM_Project.DAL.DA
    {
        public string InsertCustomer(Order OrderVar)
        {
            try
            {
                base.SQL = "SP_InsertCustomer";
                base.InitializeCommand();
                base.AddParameter("@Name", OrderVar.Name);
                base.AddParameter("@Tel", OrderVar.Tel);
                base.AddParameter("@Cell", OrderVar.Cell);
                base.AddParameter("@BirthDate", OrderVar.BirthDate);
                base.AddParameter("@MarriageDate", OrderVar.MarriageDate);
                base.AddParameter("@CustomerGroupId", OrderVar.CustomerGroupId);
                base.AddParameter("@RelationshipWayId", OrderVar.RelationshipWayId);
                base.AddParameter("@CityStateId", OrderVar.CityStateId);
                base.AddParameter("@Address", OrderVar.Address);
                return base.ExecuteScalar().ToString();
            }
            catch (Exception exp)
            {
                if (exp.Message.Contains("UNIQUE KEY") && exp.Message.Contains("duplicate"))
                    return "-1";
                else
                    return "0";
            }
        }

        public void UpdateCustomer(Order OrderVar)
        {
            try
            {
                base.SQL = "SP_UpdateCustomer";
                base.InitializeCommand();
                base.AddParameter("@Id", OrderVar.CustomerId);
                base.AddParameter("@Name", OrderVar.Name);
                base.AddParameter("@Tel", OrderVar.Tel);
                base.AddParameter("@Cell", OrderVar.Cell);
                base.AddParameter("@BirthDate", OrderVar.BirthDate);
                base.AddParameter("@MarriageDate", OrderVar.MarriageDate);
                base.AddParameter("@CustomerGroupId", OrderVar.CustomerGroupId);
                base.AddParameter("@RelationshipWayId", OrderVar.RelationshipWayId);
                base.AddParameter("@CityStateId", OrderVar.CityStateId);
                base.AddParameter("@Address", OrderVar.Address);
                base.ExecuteStoredProcedure();
            }
            catch (Exception exp)
            {
                MessageBox.Show(exp.Message);
            }
        }

        public string InsertOrderFromReserve(Order OrderVar)
        {
            try
            {
                base.SQL = "SP_InsertOrderFromReserve";
                base.InitializeCommand();
                base.AddParameter("@Date", OrderVar.Date);
                base.AddParameter("@PayTypeId", OrderVar.PayTypeId);
                base.AddParameter("@Price", OrderVar.Price);
                base.AddParameter("@CustomerId", OrderVar.CustomerId);
                base.AddParameter("@ReserveId", OrderVar.ReserveId);
                base.AddParameter("@ReservePrice", OrderVar.ReservePrice);
                base.AddParameter("@Marketer", OrderVar.Marketer);
                return base.ExecuteScalar().ToString();
            }
            catch (Exception exp)
            {
                if (exp.Message.Contains("UNIQUE KEY") && exp.Message.Contains("duplicate"))
                    return "-1";
                else
                    return "0";
            }
        }

        public string InsertOrder(Order OrderVar)
        {
            try
            {
                base.SQL = "SP_InsertOrder";
                base.InitializeCommand();
                base.AddParameter("@Date", OrderVar.Date);
                base.AddParameter("@PayTypeId", OrderVar.PayTypeId);
                base.AddParameter("@Price", OrderVar.Price);
                base.AddParameter("@CustomerId", OrderVar.CustomerId);
                base.AddParameter("@Marketer", OrderVar.Marketer);
                return base.ExecuteScalar().ToString();
            }
            catch (Exception exp)
            {
                if (exp.Message.Contains("UNIQUE KEY") && exp.Message.Contains("duplicate"))
                    return "-1";
                else
                    return "0";
            }
        }



        public DataTable ReportOrderBill(string Id)
        {
            try
            {
                DataTable dtTemp = new DataTable();
                base.SQL = "SP_ReportOrderBill";
                base.InitializeCommand();
                base.AddParameter("@Id", Id);
                base.FillDataTable(dtTemp);
                return dtTemp;
            }
            catch (Exception exp)
            {
                return null;
            }
        }

        public DataTable ReportOrderStuffBill(string Id)
        {
            try
            {
                DataTable dtTemp = new DataTable();
                base.SQL = "SP_ReportOrderStuffBill";
                base.InitializeCommand();
                base.AddParameter("@Id", Id);
                base.FillDataTable(dtTemp);
                return dtTemp;
            }
            catch (Exception exp)
            {
                return null;
            }
        }

        public DataTable ReportOrderCheckBill(string OrderId)
        {
            try
            {
                DataTable dtTemp = new DataTable();
                base.SQL = "SP_ReportOrderCheckBill";
                base.InitializeCommand();
                base.AddParameter("@OrderId", OrderId);
                base.FillDataTable(dtTemp);
                return dtTemp;
            }
            catch (Exception exp)
            {
                return null;
            }
        }

        public DataTable ReadOrderCheck(string Id)
        {
            try
            {
                DataTable dtTemp = new DataTable();
                base.SQL = "SP_ReadOrderCheck";
                base.InitializeCommand();
                base.AddParameter("@OrderId", Id);
                base.FillDataTable(dtTemp);
                return dtTemp;
            }
            catch (Exception exp)
            {
                return null;
            }
        }

        public void InsertReserveStuffAndCheck(string Query)
        {
            try
            {
                base.SQL = Query;
                base.InitializeCommand();
                base.ExecuteStoredProcedure();
            }
            catch (Exception exp)
            {
                MessageBox.Show(exp.Message);
            }
        }


        public string GetOrderIdInserted(Order OrderVar)
        {
            try
            {
                base.SQL = "SP_GetOrderIdInserted";
                base.InitializeCommand();
                base.AddParameter("@Name", OrderVar.Name);
                base.AddParameter("@Tel", OrderVar.Tel);
                base.AddParameter("@Cell", OrderVar.Cell);
                base.AddParameter("@BirthDate", OrderVar.BirthDate);
                base.AddParameter("@MarriageDate", OrderVar.MarriageDate);
                base.AddParameter("@Address", OrderVar.Address);
                base.AddParameter("@CityStateId", OrderVar.CityStateId);
                base.AddParameter("@CustomerGroupId", OrderVar.CustomerGroupId);
                base.AddParameter("@RelationshipWayId", OrderVar.RelationshipWayId);
                object Result = base.ExecuteScalar();
                return Result.ToString();

            }
            catch (Exception exp)
            {
                return null;
            }
        }

        public void InsertOrderStuff(string OrderId, string StuffId, string Quantity)
        {
            try
            {
                base.SQL = "SP_InsertOrderStuff";
                base.InitializeCommand();
                base.AddParameter("@OrderId", OrderId);
                base.AddParameter("@StuffId", StuffId);
                base.AddParameter("@Quantity", Quantity);
                base.ExecuteStoredProcedure();
            }
            catch (Exception exp)
            {
                MessageBox.Show(exp.Message);
            }
        }

        public int UpdateOrder(Order OrderVar)
        {
            try
            {
                base.SQL = "SP_UpdateOrder";
                base.InitializeCommand();
                //Customer
                base.AddParameter("@CustomerId", OrderVar.CustomerId);
                base.AddParameter("@Name", OrderVar.Name);
                base.AddParameter("@Tel", OrderVar.Tel);
                base.AddParameter("@Cell", OrderVar.Cell);
                base.AddParameter("@BirthDate", OrderVar.BirthDate);
                base.AddParameter("@MarriageDate", OrderVar.MarriageDate);
                base.AddParameter("@CustomerGroupId", OrderVar.CustomerGroupId);
                base.AddParameter("@RelationshipWayId", OrderVar.RelationshipWayId);
                base.AddParameter("@CityStateId", OrderVar.CityStateId);
                base.AddParameter("@Address", OrderVar.Address);
                //Order
                base.AddParameter("@OrderId", OrderVar.OrderId);
                base.AddParameter("@Date", OrderVar.Date);
                base.AddParameter("@PayTypeId", OrderVar.PayTypeId);
                base.AddParameter("@Price", OrderVar.Price);
                base.AddParameter("@Marketer", OrderVar.Marketer);
                return base.ExecuteStoredProcedure();
            }
            catch (Exception exp)
            {
                if (exp.Message.Contains("UNIQUE KEY") && exp.Message.Contains("duplicate"))
                    return -1;
                else
                    return 0;
            }
        }

        public void UpdateOrderStuffAndCheck(string Query)
        {
            try
            {
                base.SQL = Query;
                base.InitializeCommand();
                base.ExecuteStoredProcedure();
            }
            catch (Exception exp)
            {
                MessageBox.Show(exp.Message);
            }
        }

        public void UpdateOrderStuff(string Id, string StuffId, string Quantity)
        {
            try
            {
                base.SQL = "SP_UpdateOrderStuff";
                base.InitializeCommand();
                base.AddParameter("@Id", Id);
                base.AddParameter("@StuffId", StuffId);
                base.AddParameter("@Quantity", Quantity);
                base.ExecuteStoredProcedure();
            }
            catch(Exception exp)
            {
                MessageBox.Show(exp.Message);
            }
        }

        public void DeleteOrderStuff(string Id)
        {
            try
            {
                base.SQL = "SP_DeleteOrderStuff";
                base.InitializeCommand();
                base.AddParameter("@Id", Id);
                base.ExecuteStoredProcedure();
            }
            catch (Exception exp)
            {
                MessageBox.Show(exp.Message);
            }
        }

        public int DeleteOrder(string Id)
        {
            try
            {
                base.SQL = "SP_DeleteOrder";
                base.InitializeCommand();
                base.AddParameter("@Id", Id);
                return base.ExecuteStoredProcedure();
            }
            catch (Exception exp)
            {
                //The DELETE statement conflicted with the REFERENCE
                if (exp.Message.Contains("DELETE") && exp.Message.Contains("REFERENCE"))
                    return 1;
                else if (exp.Message.Contains("REFERENCE") || exp.Message.Contains("conflicted"))
                    return -1;
                else
                    return 0;
            }
        }

        public Order ReadReserveForOrder(string Id)
        {
            try
            {
                DataTable dtTemp = new DataTable();
                Order OrderVar = new Order();

                base.SQL = "SP_ReadReserve";
                base.InitializeCommand();
                base.AddParameter("@Id", Id);
                base.FillDataTable(dtTemp);
                //dtTemp.Rows[0][""].ToString();
                OrderVar.CustomerId = dtTemp.Rows[0]["CustomerId"].ToString();
                OrderVar.Name = dtTemp.Rows[0]["Name"].ToString();
                OrderVar.Tel = dtTemp.Rows[0]["Tel"].ToString();
                OrderVar.Cell = dtTemp.Rows[0]["Cell"].ToString();
                OrderVar.BirthDate = dtTemp.Rows[0]["BirthDate"].ToString();
                OrderVar.MarriageDate = dtTemp.Rows[0]["MarriageDate"].ToString();
                OrderVar.Address = dtTemp.Rows[0]["Address"].ToString();
                OrderVar.CityStateId = dtTemp.Rows[0]["CityStateId"].ToString();
                OrderVar.CustomerGroupId = dtTemp.Rows[0]["CustomerGroupId"].ToString();
                OrderVar.RelationshipWayId = dtTemp.Rows[0]["RelationshipWayId"].ToString();
                OrderVar.ReserveId = dtTemp.Rows[0]["ReserveId"].ToString();
                OrderVar.Date = dtTemp.Rows[0]["Date"].ToString();
                OrderVar.PayTypeId = dtTemp.Rows[0]["PayTypeId"].ToString();
                OrderVar.Price = dtTemp.Rows[0]["Price"].ToString();
                OrderVar.Marketer = dtTemp.Rows[0]["Marketer"].ToString();
                dtTemp.Dispose();
                return OrderVar;
            }
            catch (Exception exp)
            {
                return null;
            }
        }

        public Order ReadOrder(string Id)
        {
            try
            {
                DataTable dtTemp = new DataTable();
                Order OrderVar = new Order();

                base.SQL = "SP_ReadOrder";
                base.InitializeCommand();
                base.AddParameter("@Id", Id);
                base.FillDataTable(dtTemp);
                //dtTemp.Rows[0][""].ToString();
                OrderVar.CustomerId = dtTemp.Rows[0]["CustomerId"].ToString();
                OrderVar.Name = dtTemp.Rows[0]["Name"].ToString();
                OrderVar.Tel = dtTemp.Rows[0]["Tel"].ToString();
                OrderVar.Cell = dtTemp.Rows[0]["Cell"].ToString();
                OrderVar.BirthDate = dtTemp.Rows[0]["BirthDate"].ToString();
                OrderVar.MarriageDate = dtTemp.Rows[0]["MarriageDate"].ToString();
                OrderVar.Address = dtTemp.Rows[0]["Address"].ToString();
                OrderVar.CityStateId = dtTemp.Rows[0]["CityStateId"].ToString();
                OrderVar.CustomerGroupId = dtTemp.Rows[0]["CustomerGroupId"].ToString();
                OrderVar.RelationshipWayId = dtTemp.Rows[0]["RelationshipWayId"].ToString();
                OrderVar.OrderId = dtTemp.Rows[0]["OrderId"].ToString();
                OrderVar.Date = dtTemp.Rows[0]["Date"].ToString();
                OrderVar.PayTypeId = dtTemp.Rows[0]["PayTypeId"].ToString();
                OrderVar.Price = dtTemp.Rows[0]["Price"].ToString();
                OrderVar.ReservePrice = dtTemp.Rows[0]["ReservePrice"].ToString();
                OrderVar.ReserveId = dtTemp.Rows[0]["ReserveId"].ToString();
                OrderVar.Marketer = dtTemp.Rows[0]["Marketer"].ToString();
                dtTemp.Dispose();
                return OrderVar;
            }
            catch(Exception exp)
            {
                return null;
            }
        }

        public DataTable ReadOrderStuff(string Id)
        {
            try
            {
                DataTable dtTemp = new DataTable();
                base.SQL = "SP_ReadOrderStuff";
                base.InitializeCommand();
                base.AddParameter("@OrderId", Id);
                base.FillDataTable(dtTemp);
                return dtTemp;
            }
            catch(Exception exp)
            {
                return null;
            }
        }

        

        public DataTable SearchOrder(Order OrderVar)
        {
            try
            {
                DataTable dtTemp = new DataTable();
                base.SQL = "SP_SearchOrder";
                base.InitializeCommand();
                base.AddParameter("@Name", OrderVar.Name);
                base.AddParameter("@CustomerGroupName", OrderVar.CustomerGroupName);
                base.AddParameter("@CityStateName", OrderVar.CityStateName);
                base.AddParameter("@PayTypeName", OrderVar.PayTypeName);
                base.AddParameter("@StartDate", OrderVar.StartDate);
                base.AddParameter("@EndDate", OrderVar.EndDate);
                base.AddParameter("@Marketer", OrderVar.Marketer);
                base.AddParameter("@Tel", OrderVar.Tel);
                base.AddParameter("@Cell", OrderVar.Cell);
                base.FillDataTable(dtTemp);
                return dtTemp;
            }
            catch (Exception exp)
            {
                return null;
            }
        }

        public DataTable SearchOrderWithStuff(Order OrderVar)
        {
            try
            {
                DataTable dtTemp = new DataTable();
                base.SQL = "SP_SearchOrderWithStuff";
                base.InitializeCommand();
                base.AddParameter("@Name", OrderVar.Name);
                base.AddParameter("@CustomerGroupName", OrderVar.CustomerGroupName);
                base.AddParameter("@CityStateName", OrderVar.CityStateName);
                base.AddParameter("@PayTypeName", OrderVar.PayTypeName);
                base.AddParameter("@StartDate", OrderVar.StartDate);
                base.AddParameter("@EndDate", OrderVar.EndDate);
                base.AddParameter("@Stuff", OrderVar.Stuff);
                base.AddParameter("@Marketer", OrderVar.Marketer);
                base.AddParameter("@Tel", OrderVar.Tel);
                base.AddParameter("@Cell", OrderVar.Cell);
                base.FillDataTable(dtTemp);
                return dtTemp;
            }
            catch (Exception exp)
            {
                return null;
            }
        }

        public DataTable SearchOrderById(string Id)
        {
            try
            {
                DataTable dtTemp = new DataTable();
                base.SQL = "SP_SearchOrderById";
                base.InitializeCommand();
                base.AddParameter("@Id", Id);
                base.FillDataTable(dtTemp);
                return dtTemp;
            }
            catch (Exception exp)
            {
                return null;
            }
        }

        public DataTable ReportOrder(Order OrderVar)
        {
            try
            {
                DataTable dtTemp = new DataTable();
                base.SQL = "SP_ReportOrder";
                base.InitializeCommand();
                base.AddParameter("@Name", OrderVar.Name);
                base.AddParameter("@CustomerGroupName", OrderVar.CustomerGroupName);
                base.AddParameter("@CityStateName", OrderVar.CityStateName);
                base.AddParameter("@ResultDeliveryName", OrderVar.ResultDeliveryName);
                base.AddParameter("@PayTypeName", OrderVar.PayTypeName);
                base.AddParameter("@StartDate", OrderVar.StartDate);
                base.AddParameter("@EndDate", OrderVar.EndDate);
                base.FillDataTable(dtTemp);
                return dtTemp;
            }
            catch (Exception exp)
            {
                return null;
            }
        }

        public DataTable SearchOrderId(string Id)
        {
            try
            {
                DataTable dtTemp = new DataTable();
                base.SQL = "SP_SearchOrderId";
                base.InitializeCommand();
                base.AddParameter("@Id", Id);
                base.FillDataTable(dtTemp);
                return dtTemp;
            }
            catch (Exception exp)
            {
                return null;
            }
        }

        public DataTable GetCityStateForComboBox()
        {
            try
            {
                DataTable dtTemp = new DataTable();
                base.SQL = "SP_GetCityStateForComboBox";
                base.InitializeCommand();
                base.FillDataTable(dtTemp);
                return dtTemp;
            }
            catch (Exception exp)
            {
                return null;
            }
        }

        public DataTable ShowCityStateForSearchComboBox()
        {
            try
            {
                DataTable dtTemp = new DataTable();
                DataRow dr = dtTemp.NewRow();

                base.SQL = "SP_GetCityStateForComboBox";
                base.InitializeCommand();
                base.FillDataTable(dtTemp);
                dr["Id"] = 0;
                dr["Name"] = "همه مناطق";
                dtTemp.Rows.Add(dr);
                return dtTemp;
            }
            catch (Exception exp)
            {
                return null;
            }
        }

        public DataTable GetCustomerGroupForComboBox()
        {
            try
            {
                DataTable dtTemp = new DataTable();
                base.SQL = "SP_GetCustomerGroupForComboBox";
                base.InitializeCommand();
                base.FillDataTable(dtTemp);
                return dtTemp;
            }
            catch (Exception exp)
            {
                return null;
            }
        }

        public DataTable ShowCustomerGroupForSearchComboBox()
        {
            try
            {
                DataTable dtTemp = new DataTable();
                DataRow dr = dtTemp.NewRow();

                base.SQL = "SP_GetCustomerGroupForComboBox";
                base.InitializeCommand();
                base.FillDataTable(dtTemp);
                dr["Id"] = 0;
                dr["Name"] = "همه گروه ها";
                dtTemp.Rows.Add(dr);
                return dtTemp;
            }
            catch (Exception exp)
            {
                return null;
            }
        }

        public DataTable GetRelationshipWayForComboBox()
        {
            try
            {
                DataTable dtTemp = new DataTable();
                base.SQL = "SP_GetRelationshipWayForComboBox";
                base.InitializeCommand();
                base.FillDataTable(dtTemp);
                return dtTemp;
            }
            catch (Exception exp)
            {
                return null;
            }
        }

        public DataTable ShowRelationshipWayForSearchComboBox()
        {
            try
            {
                DataTable dtTemp = new DataTable();
                DataRow dr = dtTemp.NewRow();

                base.SQL = "SP_GetRelationshipWayForComboBox";
                base.InitializeCommand();
                base.FillDataTable(dtTemp);
                dr["Id"] = 0;
                dr["Name"] = "همه موارد";
                dtTemp.Rows.Add(dr);
                return dtTemp;
            }
            catch (Exception exp)
            {
                return null;
            }
        }

        public Order OrderBill(string Id)
        {
            try
            {
                DataTable dtTemp = new DataTable();
                Order OrderVar = new Order();

                base.SQL = "SP_OrderBill";
                base.InitializeCommand();
                base.AddParameter("@Id", Id);
                base.FillDataTable(dtTemp);
                OrderVar.Id = dtTemp.Rows[0]["Id"].ToString();
                OrderVar.Name = dtTemp.Rows[0]["Name"].ToString();
                OrderVar.Address = dtTemp.Rows[0]["Address"].ToString();
                OrderVar.OrderDate = dtTemp.Rows[0]["OrderDate"].ToString();
                OrderVar.Price = dtTemp.Rows[0]["Price"].ToString();
                OrderVar.PayTypeName = dtTemp.Rows[0]["PayTypeName"].ToString();
                OrderVar.BankComment = dtTemp.Rows[0]["BankComment"].ToString();
                OrderVar.PayerName = dtTemp.Rows[0]["PayerName"].ToString();
                OrderVar.PayDate = dtTemp.Rows[0]["PayDate"].ToString();
                OrderVar.DelivererName = dtTemp.Rows[0]["DelivererName"].ToString();
                OrderVar.ResultDeliveryName = dtTemp.Rows[0]["ResultDeliveryName"].ToString();
                dtTemp.Dispose();
                return OrderVar;
            }
            catch (Exception exp)
            {
                return null;
            }
        }

        public DataTable OrderStuffBill(string OrderId)
        {
            try
            {
                DataTable dtTemp = new DataTable();
                base.SQL = "SP_OrderStuffBill";
                base.InitializeCommand();
                base.AddParameter("@OrderId", OrderId);
                base.FillDataTable(dtTemp);
                return dtTemp;
            }
            catch (Exception exp)
            {
                return null;
            }
        }

        

    }
}
