using HRM.BO;
using Ease.Core.DataAccess;
using Ease.Core.Model;
using Ease.Core.Utility;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using Microsoft.Data.SqlClient;
using Ease.Core.DataAccess.SQL;

namespace HRM.DA
{
    #region DailyAttendanceAllowance Service

    public class DailyAttendanceAllowanceService : ServiceTemplate, IDailyAttendanceAllowanceService
    {
        #region Private functions and declaration

        public DailyAttendanceAllowanceService()
        {
        }

        private void MapObject(DailyAttendanceAllowance oDailyAttendanceAllowance, DataReader oReader)
        {
            base.SetObjectID(oDailyAttendanceAllowance, oReader.GetInt32("DailyAttendanceAllowanceID").Value);
            oDailyAttendanceAllowance.DailyAttendanceProcessID = oReader.GetInt32("DailyAttendanceProcessID");
            oDailyAttendanceAllowance.AttnDate = oReader.GetDateTime("AttnDate", DateTime.MinValue);
            oDailyAttendanceAllowance.EmployeeID = oReader.GetInt32("EmployeeID").Value;
            oDailyAttendanceAllowance.ShiftID = oReader.GetInt32("ShiftID").Value;
            oDailyAttendanceAllowance.PayrollTypeID = oReader.GetInt32("PayrollTypeID").Value;
            oDailyAttendanceAllowance.CreatedBy = oReader.GetInt32("CreatedBy").Value;
            oDailyAttendanceAllowance.CreatedDate = oReader.GetDateTime("CreatedDate", DateTime.MinValue);
            oDailyAttendanceAllowance.ModifiedBy = oReader.GetInt32("ModifiedBy");
            oDailyAttendanceAllowance.ModifiedDate = oReader.GetDateTime("ModifiedDate");

            oDailyAttendanceAllowance.BenifitsType = (EnumattnBeniftsType) oReader.GetInt32("BenifitsType").Value;
            oDailyAttendanceAllowance.OtherBenifitItemID = oReader.GetInt32("OtherBenifitItemID");
            oDailyAttendanceAllowance.OtherBenifitValue = oReader.GetDouble("OtherBenifitValue").Value;
            oDailyAttendanceAllowance.SalaryMonth = oReader.GetDateTime("SalaryMonth").Value;
            oDailyAttendanceAllowance.IsArrear = oReader.GetBoolean("IsArrear").Value;



            this.SetObjectState(oDailyAttendanceAllowance, Ease.Core.ObjectState.Saved);
        }

        protected override T CreateObject<T>(DataReader oReader)
        {
            DailyAttendanceAllowance oDailyAttendanceAllowance = new DailyAttendanceAllowance();
            MapObject(oDailyAttendanceAllowance, oReader);
            return oDailyAttendanceAllowance as T;
        }

        private DailyAttendanceAllowance CreateObject(DataReader oReader)
        {
            DailyAttendanceAllowance oDailyAttendanceAllowance = new DailyAttendanceAllowance();
            MapObject(oDailyAttendanceAllowance, oReader);
            return oDailyAttendanceAllowance;
        }

        #endregion

        #region Service implementation
        public List<DailyAttendanceAllowance> GetByPayrollType(int payrollTypeID)
        {
            List<DailyAttendanceAllowance> oDailyAttendanceAllowance = new List<DailyAttendanceAllowance>();

            TransactionContext tc = null;
            try
            {
                tc = TransactionContext.Begin();
                DataReader dr = new DataReader(DailyAttendanceAllowanceDA.GetByPayrollType(tc, payrollTypeID));
                oDailyAttendanceAllowance = this.CreateObjects<DailyAttendanceAllowance>(dr);
                dr.Close();
                tc.End();
            }
            catch (Exception e)
            {
                #region Handle Exception
                if (tc != null)
                    tc.HandleError();
                ExceptionLog.Write(e);
                throw new ServiceException(e.Message, e);
                #endregion
            }

            return oDailyAttendanceAllowance;
        }

        public DailyAttendanceAllowance GetById(int id)
        {
            DailyAttendanceAllowance oDailyAttendanceAllowance = new DailyAttendanceAllowance();

            TransactionContext tc = null;
            try
            {
                tc = TransactionContext.Begin();
                DataReader oreader = new DataReader(DailyAttendanceAllowanceDA.GetById(tc, id));
                if (oreader.Read())
                {
                    oDailyAttendanceAllowance = this.CreateObject<DailyAttendanceAllowance>(oreader);
                }
                oreader.Close();
                tc.End();
            }
            catch (Exception e)
            {
                #region Handle Exception

                if (tc != null)
                    tc.HandleError();
                ExceptionLog.Write(e);
                throw new ServiceException("Failed to Get DailyAttendanceAllowance", e);

                #endregion
            }

            return oDailyAttendanceAllowance;
        }


        public List<DailyAttendanceAllowance> Get(TransactionContext tc, string empids, DateTime attnDate)
        {
            List<DailyAttendanceAllowance> oDailyAttendanceAllowance = new List<DailyAttendanceAllowance>();

            try
            {
                DataReader dr = new DataReader(DailyAttendanceAllowanceDA.Get(tc, empids, attnDate));
                oDailyAttendanceAllowance = this.CreateObjects<DailyAttendanceAllowance>(dr);
                dr.Close();
            }
            catch (Exception e)
            {
                #region Handle Exception
                if (tc != null)
                    tc.HandleError();
                ExceptionLog.Write(e);
                throw new ServiceException(e.Message, e);
                #endregion
            }

            return oDailyAttendanceAllowance;
        }

        public List<DailyAttendanceAllowance> Get(string empids, DateTime attnDate)
        {
            List<DailyAttendanceAllowance> oDailyAttendanceAllowance = new List<DailyAttendanceAllowance>();

            TransactionContext tc = null;
            try
            {
                tc = TransactionContext.Begin();
                oDailyAttendanceAllowance = this.Get(tc, empids, attnDate);
                tc.End();
            }
            catch (Exception e)
            {
                #region Handle Exception
                if (tc != null)
                    tc.HandleError();
                ExceptionLog.Write(e);
                throw new ServiceException(e.Message, e);
                #endregion
            }

            return oDailyAttendanceAllowance;
        }


        public List<DailyAttendanceAllowance> GetByAttnDate(TransactionContext tc, DateTime attnDate)
        {
            List<DailyAttendanceAllowance> oDailyAttendanceAllowance = new List<DailyAttendanceAllowance>();

            try
            {
                DataReader dr = new DataReader(DailyAttendanceAllowanceDA.GetByAttnDate(tc, attnDate));
                oDailyAttendanceAllowance = this.CreateObjects<DailyAttendanceAllowance>(dr);
                dr.Close();
            }
            catch (Exception e)
            {
                #region Handle Exception
                if (tc != null)
                    tc.HandleError();
                ExceptionLog.Write(e);
                throw new ServiceException(e.Message, e);
                #endregion
            }

            return oDailyAttendanceAllowance;
        }
        public List<DailyAttendanceAllowance> GetByAttnDate(DateTime attnDate)
        {
            List<DailyAttendanceAllowance> oDailyAttendanceAllowance = new List<DailyAttendanceAllowance>();
            TransactionContext tc = null;
            try
            {
                tc = TransactionContext.Begin();
                oDailyAttendanceAllowance =  this.GetByAttnDate(tc, attnDate);
                tc.End();
            }
            catch (Exception e)
            {
                #region Handle Exception
                if (tc != null)
                    tc.HandleError();
                ExceptionLog.Write(e);
                throw new ServiceException(e.Message, e);
                #endregion
            }

            return oDailyAttendanceAllowance;
        }
        public List<DailyAttendanceAllowance> GetEditedItem(TransactionContext tc, DateTime attnDate)
        {
            List<DailyAttendanceAllowance> oDailyAttendanceAllowance = new List<DailyAttendanceAllowance>();

            try
            {
                DataReader dr = new DataReader(DailyAttendanceAllowanceDA.GetEditedItems(tc, attnDate));
                oDailyAttendanceAllowance = this.CreateObjects<DailyAttendanceAllowance>(dr);
                dr.Close();
            }
            catch (Exception e)
            {
                #region Handle Exception
                if (tc != null)
                    tc.HandleError();
                ExceptionLog.Write(e);
                throw new ServiceException(e.Message, e);
                #endregion
            }

            return oDailyAttendanceAllowance;
        }
        public List<DailyAttendanceAllowance> GetByAttnDateRange(DateTime attnFromDate, DateTime attnToDate)
        {
            List<DailyAttendanceAllowance> oDailyAttendanceAllowance = new List<DailyAttendanceAllowance>();

            TransactionContext tc = null;
            try
            {
                tc = TransactionContext.Begin();
                DataReader dr = new DataReader(DailyAttendanceAllowanceDA.GetByAttnDateRange(tc, attnFromDate, attnToDate));
                oDailyAttendanceAllowance = this.CreateObjects<DailyAttendanceAllowance>(dr);
                dr.Close();
                tc.End();
            }
            catch (Exception e)
            {
                #region Handle Exception
                if (tc != null)
                    tc.HandleError();
                ExceptionLog.Write(e);
                throw new ServiceException(e.Message, e);
                #endregion
            }

            return oDailyAttendanceAllowance;
        }

        public List<DailyAttendanceAllowance> GetByAttnDateRangePayrollType(DateTime attnFromDate, DateTime attnToDate, int payrollTypeid)
        {
            List<DailyAttendanceAllowance> oDailyAttendanceAllowance = new List<DailyAttendanceAllowance>();

            TransactionContext tc = null;
            try
            {
                tc = TransactionContext.Begin();
                DataReader dr = new DataReader(DailyAttendanceAllowanceDA.GetByAttnDateRangePayrollType(tc, attnFromDate, attnToDate, payrollTypeid));
                oDailyAttendanceAllowance = this.CreateObjects<DailyAttendanceAllowance>(dr);
                dr.Close();
                tc.End();
            }
            catch (Exception e)
            {
                #region Handle Exception
                if (tc != null)
                    tc.HandleError();
                ExceptionLog.Write(e);
                throw new ServiceException(e.Message, e);
                #endregion
            }

            return oDailyAttendanceAllowance;
        }
        
        public DataTable GetByAttnbySalaryMonth(DateTime salaryMonth, int payrollTypeid)
        {
            DataTable oDailyAttendanceAllowance = new DataTable();

            TransactionContext tc = null;
            try
            {
                tc = TransactionContext.Begin();
                oDailyAttendanceAllowance = DailyAttendanceAllowanceDA.GetByAttnbySalaryMonth(tc, salaryMonth, payrollTypeid);
                tc.End();
            }
            catch (Exception e)
            {
                #region Handle Exception
                if (tc != null)
                    tc.HandleError();
                ExceptionLog.Write(e);
                throw new ServiceException(e.Message, e);
                #endregion
            }

            return oDailyAttendanceAllowance;
        }

        public int Save(DailyAttendanceAllowance oDailyAttendanceAllowance)
        {
            TransactionContext tc = null;
            try
            {
                tc = TransactionContext.Begin(true);
                if (oDailyAttendanceAllowance.IsNew)
                {
                    DailyAttendanceAllowanceDA.Insert(tc, oDailyAttendanceAllowance);
                }
                else
                {
                    DailyAttendanceAllowanceDA.Update(tc, oDailyAttendanceAllowance);
                }

                tc.End();
                return oDailyAttendanceAllowance.ID;
            }
            catch (Exception e)
            {
                #region Handle Exception

                if (tc != null)
                    tc.HandleError();
                ExceptionLog.Write(e);
                throw new Exception("Failed to Insert DailyAttendanceAllowance. Because " + e.Message, e);

                #endregion
            }
        }
        /// <summary>
        /// this function can save only single date data
        /// </summary>
        /// <param name="tc"></param>
        /// <param name="pDailyAttendanceAllowance"></param>
        public void Save(TransactionContext tc, List<DailyAttendanceAllowance> pDailyAttendanceAllowance)
        {
            return;
            try
            {
                var Groupdate =
                            from dateGroup in pDailyAttendanceAllowance
                            group dateGroup by dateGroup.AttnDate;

                //   if (Groupdate.Count() > 1) throw new Exception("Mutilple date is not allowed in AttnBenifits collection");

                foreach (var dDate in Groupdate)
                {
                    List<DailyAttendanceAllowance> editedItems = null;
                    if (pDailyAttendanceAllowance.Count > 200)
                    {
                        editedItems = this.GetByAttnDate(tc,dDate.Key);
                    }
                    else
                    {
                        string commaSeparatedEmployeeIDs = string.Join(",", pDailyAttendanceAllowance.Select(item => item.EmployeeID.ToString()));
                        editedItems = this.Get(tc, commaSeparatedEmployeeIDs, dDate.Key);
                    }


                    List<DailyAttendanceAllowance> newList = new List<DailyAttendanceAllowance>();
                    if (editedItems == null)
                    {
                        newList = pDailyAttendanceAllowance;
                    }
                    else
                    {
                        foreach (DailyAttendanceAllowance item in pDailyAttendanceAllowance)
                        {
                            var edItem = editedItems.FirstOrDefault(x => x.AttnDate == item.AttnDate
                            && x.BenifitsType == item.BenifitsType && x.EmployeeID == item.EmployeeID
                            && item.OtherBenifitItemID == x.OtherBenifitItemID);
                            if (edItem == null) newList.Add(item);
                            else
                            {
                                if (edItem.ManualEdited == false)
                                {
                                    item.ManualEdited = false;
                                    newList.Add(item);
                                }
                            }
                        }
                        // previously it was not current collection doesn't have
                        foreach (DailyAttendanceAllowance item in editedItems)
                        {
                            var edItem = newList.FirstOrDefault(x => x.AttnDate == item.AttnDate
                            && x.BenifitsType == item.BenifitsType && x.EmployeeID == item.EmployeeID
                            && item.OtherBenifitItemID == x.OtherBenifitItemID);
                            if (edItem == null)
                            {
                                if (item.ManualEdited == false)
                                {
                                    item.ManualEdited = false;
                                    item.OtherBenifitValue = 0;
                                    newList.Add(item);
                                }
                            }
                        }

                    }

                    if (newList.Count == 1)
                    {
                        foreach (DailyAttendanceAllowance item in newList)
                        {
                            SaveWithSP(tc, item);
                        }
                    }
                    else
                    {
                        SaveBulk(tc, newList);
                    }
                    #region without bulk insert
                    //foreach (DailyAttendanceAllowance item in pDailyAttendanceAllowance)
                    //{
                    //    if (item.IsNew)
                    //    {
                    //        DailyAttendanceAllowanceDA.Insert(tc, item);
                    //    }
                    //    else
                    //    {
                    //        DailyAttendanceAllowanceDA.Update(tc, item);
                    //    }
                    //} 
                    #endregion

                }
            }
            catch (Exception e)
            {
                #region Handle Exception

                if (tc != null)
                    tc.HandleError();
                ExceptionLog.Write(e);
                throw new Exception("Failed to Insert DailyAttendanceAllowance. Because " + e.Message, e);

                #endregion
            }
        }
        private static void SaveWithSP(TransactionContext tc, DailyAttendanceAllowance item)
        {
            List<SqlParameter> p = new List<SqlParameter>
            {
                SqlHelperExtension.CreateInParam("@DailyAttendanceProcessID", SqlDbType.Int, item.DailyAttendanceProcessID),
                SqlHelperExtension.CreateInParam("@AttnDate", SqlDbType.DateTime, item.AttnDate),
                SqlHelperExtension.CreateInParam("@EmployeeID", SqlDbType.Int, item.EmployeeID),
                SqlHelperExtension.CreateInParam("@ShiftID", SqlDbType.Int, item.ShiftID),
                SqlHelperExtension.CreateInParam("@PayrollTypeID", SqlDbType.Int, item.PayrollTypeID),
                SqlHelperExtension.CreateInParam("@BenifitsType", SqlDbType.Int, item.BenifitsType),
                SqlHelperExtension.CreateInParam("@OtherBenifitItemID", SqlDbType.Int, item.OtherBenifitItemID),
                SqlHelperExtension.CreateInParam("@OtherBenifitValue", SqlDbType.Money, item.OtherBenifitValue),
                SqlHelperExtension.CreateInParam("@SalaryMonth", SqlDbType.DateTime, item.SalaryMonth),
                SqlHelperExtension.CreateInParam("@IsArrear", SqlDbType.Int, item.IsArrear),
                SqlHelperExtension.CreateInParam("@AutoCreated", SqlDbType.Int, item.AutoCreated),
                SqlHelperExtension.CreateInParam("@ManualEdited", SqlDbType.Int, item.ManualEdited),
                SqlHelperExtension.CreateInParam("@CreatedBy", SqlDbType.Int, item.CreatedBy),
                SqlHelperExtension.CreateInParam("@CreatedDate", SqlDbType.DateTime, item.CreatedDate),
                

            };

            if (item.ModifiedBy != null)
            {
                p.Add(SqlHelperExtension.CreateInParam("@ModifiedBy", SqlDbType.Int, item.ModifiedBy));
            }
            else
            {
                p.Add(SqlHelperExtension.CreateInParam("@ModifiedBy", SqlDbType.Int, DBNull.Value));
            }
            if (item.ModifiedDate != null)
            {
                p.Add(SqlHelperExtension.CreateInParam("@ModifiedDate", SqlDbType.DateTime, item.ModifiedDate));
            }
            else
            {
                p.Add(SqlHelperExtension.CreateInParam("@ModifiedDate", SqlDbType.DateTime, DBNull.Value));
            }

            tc.ExecuteNonQuery(CommandType.StoredProcedure, "sp_Insert_DailyAttendanceAllowance", p.ToArray());
        }
        public void UpdateDailyAttendanceProcessIDs(TransactionContext tc)
        {
            string sql = SQLParser.MakeSQL(@"
            UPDATE DailyAttendanceAllowance
            SET DailyAttendanceProcessID = dap.DAILYATTNPROCESSID
            FROM
            DAILYATTNPROCESS dap
            INNER JOIN DailyAttendanceAllowance daa
            ON dap.EMPLOYEEID = daa.EmployeeID AND dap.AttnDate = daa.AttnDate");

            tc.ExecuteNonQuery(sql);
        }
        public void SaveBulk(TransactionContext tc, List<DailyAttendanceAllowance> pDailyAttendanceAllowance)
        {
            try
            {
                if (pDailyAttendanceAllowance.Count > 0)
                {
                    string commaSeparatedEmployeeIDs = string.Join(",", pDailyAttendanceAllowance.Select(item => item.EmployeeID.ToString()));

                    DailyAttendanceAllowanceDA.Delete(tc, commaSeparatedEmployeeIDs, pDailyAttendanceAllowance[0].AttnDate, false);

                    int id = tc.GenerateID("DailyAttendanceAllowance", "DailyAttendanceAllowanceID");

                    // make sure the serial of the column name is aligned with the serial of the column name in the table in database
                    DataTable DailyAttendanceAllowanceTable = new DataTable("DailyAttendanceAllowance");
                    DailyAttendanceAllowanceTable.Columns.Add(new DataColumn("DailyAttendanceAllowanceID", typeof(int)));
                    DailyAttendanceAllowanceTable.Columns.Add(new DataColumn("DailyAttendanceProcessID", typeof(int)));
                    DailyAttendanceAllowanceTable.Columns.Add(new DataColumn("AttnDate", typeof(DateTime)));
                    DailyAttendanceAllowanceTable.Columns.Add(new DataColumn("EmployeeID", typeof(int)));
                    DailyAttendanceAllowanceTable.Columns.Add(new DataColumn("ShiftID", typeof(int)));
                    DailyAttendanceAllowanceTable.Columns.Add(new DataColumn("PayrollTypeID", typeof(int)));

                    DailyAttendanceAllowanceTable.Columns.Add(new DataColumn("BenifitsType", typeof(int)));
                    DailyAttendanceAllowanceTable.Columns.Add(new DataColumn("OtherBenifitItemID", typeof(int)));
                    DailyAttendanceAllowanceTable.Columns.Add(new DataColumn("OtherBenifitValue", typeof(double)));
                    DailyAttendanceAllowanceTable.Columns.Add(new DataColumn("SalaryMonth", typeof(DateTime)));
                    DailyAttendanceAllowanceTable.Columns.Add(new DataColumn("IsArrear", typeof(int)));
                    DailyAttendanceAllowanceTable.Columns.Add(new DataColumn("AutoCreated", typeof(int)));
                    DailyAttendanceAllowanceTable.Columns.Add(new DataColumn("ManualEdited", typeof(int)));
                    DailyAttendanceAllowanceTable.Columns.Add(new DataColumn("CREATEDBY", typeof(int)));
                    DailyAttendanceAllowanceTable.Columns.Add(new DataColumn("CREATEDDATE", typeof(DateTime)));
                    DailyAttendanceAllowanceTable.Columns.Add(new DataColumn("MODIFIEDBY", typeof(int)));
                    DailyAttendanceAllowanceTable.Columns.Add(new DataColumn("MODIFIEDDATE", typeof(DateTime)));

                    foreach (DailyAttendanceAllowance item in pDailyAttendanceAllowance)
                    {
                        DailyAttendanceAllowanceTable.Rows.Add(
                            id++,
                            item.DailyAttendanceProcessID,
                            item.AttnDate,
                            item.EmployeeID,
                            item.ShiftID,
                            item.PayrollTypeID,
                            item.BenifitsType,
                            item.OtherBenifitItemID,
                            item.OtherBenifitValue,
                            item.SalaryMonth,
                            item.IsArrear,
                            item.AutoCreated,
                            item.ManualEdited,
                            item.CreatedBy,
                            item.CreatedDate,
                            item.ModifiedBy,
                            item.ModifiedDate
                        );

                    }

                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy((SqlConnection)tc.Connection, SqlBulkCopyOptions.Default, (SqlTransaction)tc.Transaction))
                    {
                        bulkCopy.BulkCopyTimeout = 10000; // in seconds

                        bulkCopy.DestinationTableName = "DailyAttendanceAllowance";
                        bulkCopy.WriteToServer(DailyAttendanceAllowanceTable);
                    }
                }
            }
            catch (Exception e)
            {
                #region Handle Exception
                if (tc != null)
                    tc.HandleError();
                ExceptionLog.Write(e);
                throw new ServiceException(e.Message, e);
                #endregion
            }
        }
        public void Delete(int id)
        {
            TransactionContext tc = null;
            try
            {
                tc = TransactionContext.Begin(true);
                DailyAttendanceAllowanceDA.Delete(tc, id);
                tc.End();
            }
            catch (Exception e)
            {
                #region Handle Exception

                if (tc != null)
                    tc.HandleError();
                ExceptionLog.Write(e);
                throw new ServiceException(e.Message, e);

                #endregion
            }
        }
        #endregion
    }

    #endregion
}