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 ReserveCRUD : Aram_CRM_Project.DAL.DA
    {
        public string InsertCustomer(Reserve ReserveVar)
        {
            try
            {
                base.SQL = "SP_InsertCustomer";
                base.InitializeCommand();
                base.AddParameter("@Name", ReserveVar.Name);
                base.AddParameter("@Tel", ReserveVar.Tel);
                base.AddParameter("@Cell", ReserveVar.Cell);
                base.AddParameter("@BirthDate", ReserveVar.BirthDate);
                base.AddParameter("@MarriageDate", ReserveVar.MarriageDate);
                base.AddParameter("@CustomerGroupId", ReserveVar.CustomerGroupId);
                base.AddParameter("@RelationshipWayId", ReserveVar.RelationshipWayId);
                base.AddParameter("@CityStateId", ReserveVar.CityStateId);
                base.AddParameter("@Address", ReserveVar.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(Reserve ReserveVar)
        {
            try
            {
                base.SQL = "SP_UpdateCustomer";
                base.InitializeCommand();
                base.AddParameter("@Id", ReserveVar.CustomerId);
                base.AddParameter("@Name", ReserveVar.Name);
                base.AddParameter("@Tel", ReserveVar.Tel);
                base.AddParameter("@Cell", ReserveVar.Cell);
                base.AddParameter("@BirthDate", ReserveVar.BirthDate);
                base.AddParameter("@MarriageDate", ReserveVar.MarriageDate);
                base.AddParameter("@CustomerGroupId", ReserveVar.CustomerGroupId);
                base.AddParameter("@RelationshipWayId", ReserveVar.RelationshipWayId);
                base.AddParameter("@CityStateId", ReserveVar.CityStateId);
                base.AddParameter("@Address", ReserveVar.Address);
                base.ExecuteStoredProcedure();
            }
            catch (Exception exp)
            {
                MessageBox.Show(exp.Message);
            }
        }

        public string InsertReserve(Reserve ReserveVar)
        {
            try
            {
                base.SQL = "SP_InsertReserve";
                base.InitializeCommand();
                base.AddParameter("@Date", ReserveVar.Date);
                base.AddParameter("@PayTypeId", ReserveVar.PayTypeId);
                base.AddParameter("@PayerName", ReserveVar.PayerName);
                base.AddParameter("@ReceptionName", ReserveVar.ReceptionName);
                base.AddParameter("@Price", ReserveVar.Price);
                base.AddParameter("@CustomerId", ReserveVar.CustomerId);
                base.AddParameter("@ResultReserveId", ReserveVar.ResultReserveId);
                base.AddParameter("@Marketer", ReserveVar.Marketer);
                return base.ExecuteScalar().ToString();
            }
            catch (Exception exp)
            {
                if (exp.Message.Contains("UNIQUE KEY") && exp.Message.Contains("duplicate"))
                    return "-1";
                else
                    return "0";
            }
        }

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

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

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

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

        public DataTable SearchReserve(Reserve ReserveVar)
        {
            try
            {
                DataTable dtTemp = new DataTable();
                base.SQL = "SP_SearchReserve";
                base.InitializeCommand();
                base.AddParameter("@Name", ReserveVar.Name);
                base.AddParameter("@CustomerGroupName", ReserveVar.CustomerGroupName);
                base.AddParameter("@CityStateName", ReserveVar.CityStateName);
                base.AddParameter("@ResultReserveName", ReserveVar.ResultReserveName);
                base.AddParameter("@PayTypeName", ReserveVar.PayTypeName);
                base.AddParameter("@StartDate", ReserveVar.StartDate);
                base.AddParameter("@EndDate", ReserveVar.EndDate);
                base.AddParameter("@Marketer", ReserveVar.Marketer);
                base.AddParameter("@Tel", ReserveVar.Tel);
                base.AddParameter("@Cell", ReserveVar.Cell);
                base.FillDataTable(dtTemp);
                return dtTemp;
            }
            catch (Exception exp)
            {
                return null;
            }
        }

        public DataTable SearchReserveWithStuff(Reserve ReserveVar)
        {
            try
            {
                DataTable dtTemp = new DataTable();
                base.SQL = "SP_SearchReserveWithStuff";
                base.InitializeCommand();
                base.AddParameter("@Name", ReserveVar.Name);
                base.AddParameter("@CustomerGroupName", ReserveVar.CustomerGroupName);
                base.AddParameter("@CityStateName", ReserveVar.CityStateName);
                base.AddParameter("@ResultReserveName", ReserveVar.ResultReserveName);
                base.AddParameter("@PayTypeName", ReserveVar.PayTypeName);
                base.AddParameter("@StartDate", ReserveVar.StartDate);
                base.AddParameter("@EndDate", ReserveVar.EndDate);
                base.AddParameter("@Stuff", ReserveVar.Stuff);
                base.AddParameter("@Marketer", ReserveVar.Marketer);
                base.AddParameter("@Tel", ReserveVar.Tel);
                base.AddParameter("@Cell", ReserveVar.Cell);
                base.FillDataTable(dtTemp);
                return dtTemp;
            }
            catch (Exception exp)
            {
                return null;
            }
        }

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

        public Reserve ReadReserve(string Id)
        {
            try
            {
                DataTable dtTemp = new DataTable();
                Reserve ReserveVar = new Reserve();

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

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

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

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

        public int UpdateReserve(Reserve ReserveVar)
        {
            try
            {
                base.SQL = "SP_UpdateReserve";
                base.InitializeCommand();
                //Customer
                base.AddParameter("@CustomerId", ReserveVar.CustomerId);
                base.AddParameter("@Name", ReserveVar.Name);
                base.AddParameter("@Tel", ReserveVar.Tel);
                base.AddParameter("@Cell", ReserveVar.Cell);
                base.AddParameter("@BirthDate", ReserveVar.BirthDate);
                base.AddParameter("@MarriageDate", ReserveVar.MarriageDate);
                base.AddParameter("@CustomerGroupId", ReserveVar.CustomerGroupId);
                base.AddParameter("@RelationshipWayId", ReserveVar.RelationshipWayId);
                base.AddParameter("@CityStateId", ReserveVar.CityStateId);
                base.AddParameter("@Address", ReserveVar.Address);
                //Reserve
                base.AddParameter("@ReserveId", ReserveVar.ReserveId);
                base.AddParameter("@Date", ReserveVar.Date);
                base.AddParameter("@PayTypeId", ReserveVar.PayTypeId);
                base.AddParameter("@PayerName", ReserveVar.PayerName);
                base.AddParameter("@ReceptionName", ReserveVar.ReceptionName);
                base.AddParameter("@Price", ReserveVar.Price);
                base.AddParameter("@ResultReserveId", ReserveVar.ResultReserveId);
                base.AddParameter("@Marketer", ReserveVar.Marketer);
                return base.ExecuteStoredProcedure();
            }
            catch (Exception exp)
            {
                if (exp.Message.Contains("UNIQUE KEY") && exp.Message.Contains("duplicate"))
                    return -1;
                else
                    return 0;
            }
        }

        public int DeleteReserve(string Id)
        {
            try
            {
                base.SQL = "SP_DeleteReserve";
                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 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 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 GetRelationshipWayForComboBox()
        {
            try
            {
                DataTable dtTemp = new DataTable();
                base.SQL = "SP_GetRelationshipWayForComboBox";
                base.InitializeCommand();
                base.FillDataTable(dtTemp);
                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 GetCityStateForComboBox()
        {
            try
            {
                DataTable dtTemp = new DataTable();
                base.SQL = "SP_GetCityStateForComboBox";
                base.InitializeCommand();
                base.FillDataTable(dtTemp);
                return dtTemp;
            }
            catch (Exception exp)
            {
                return null;
            }
        }
    }
}
