using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Ease.CoreV35.DataAccess;
using System.Data;
using HRM.BO;
using Ease.Core.DataAccess;

namespace HRM.DA
{
    #region ShiftDA

    internal class ShiftDA
    {
        #region Constructor

        private ShiftDA()
        {
        }

        #endregion

        #region Insert function
        //oShift.HasTiffinAllowance = oReader.GetBoolean("HasTiffinAllowance").HasValue? oReader.GetBoolean("HasTiffinAllowance").Value : false;
        //    oShift.HasSpecialAllowance = oReader.GetBoolean("HasSpecialAllowance").HasValue? oReader.GetBoolean("HasSpecialAllowance").Value : false;
        //    oShift.TiffinAllowanceID = oReader.GetInt32("TiffinAllowanceID");
        //    oShift.SpecialAllowanceID = oReader.GetInt32("SpecialAllowanceID");
        internal static void Insert(TransactionContext tc, Shift item)
        {
            string sql = SQLParser.MakeSQL(@"INSERT INTO dbo.Shift (
                                             ShiftID, Code, Name, 
                                             InTime, OutTime, LateCalcualtion, 
                                             EarlyExitBefore, IsOverlapingDay, ShortName,
                                             SequenceNo, Status, CreatedBy, 
                                             CreatedDate,
                                             MinimumOTHour, IsCounterClock, hasAbsentTime,
                                             AbsentTime, DelayCalcualtion, ShiftType,
                                             MinimumInOTHour, LunchHour, FlexibleHour, PayrollTypeID,
                                            HasBenefits, HasExtraHourAllowance, 
                                            HasNightAllowance, HasHolidayAllowance, 
                                            HasOtherAllowance, HasRegularOverTime,HasHolidayOverTime,
                                            ExtraHourAllowanceID, NightAllowanceID,
                                            HolidayAllowanceID, OtherAllowanceID,
                                            RegularOverTimeAllowanceID, HolidayOverTimeAllowanceID,
                                            ExtraHourAllowanceFromTime,
                                            HasTiffinAllowance,HasSpecialAllowance,TiffinAllowanceID,SpecialAllowanceID)   
                                             VALUES (
                                             %n, %s, %s,
                                             %D, %D, %n,
                                             %n, %b, %s,
                                             %n, %n, %n, 
                                             %D,
                                             %n, %b, %b, 
                                             %D, %n, %n, %n,
                                             %n, %n, %n, %n,
                                            %b,%b,%b,
                                            %b,%b,%b,
                                            %n,%n,%n,
                                            %n,%n,%n,%D,
                                            %b, %b, %n, %n )",
                item.ID, item.Code, item.Name,
                item.InTime, item.OutTime, item.LateCalcualtion,
                item.EarlyExitBefore, item.IsOverlapingDay, item.ShortName,
                item.Sequence, item.Status, item.CreatedBy,
                item.CreatedDate,
                item.MinimumOutOTHour, item.IsCounterClock, item.hasAbsentTime,
                DataReader.GetNullValue(item.AbsentTime), item.DelayCalcualtion, item.ShiftType,
                item.MinimumInOTHour, item.LunchHour, item.FlexibleHour, item.payrollTypeID,
                item.HasBenefits, item.HasExtraHourAllowance,
                item.HasNightAllowance, item.HasHolidayAllowance,
                item.HasOtherAllowance, item.HasRegularOverTime, item.HasHolidayOverTime,
                item.ExtraHourAllowanceID, item.NightAllowanceID,
                item.HolidayAllowanceID, item.OtherAllowanceID,
                item.RegularOverTimeAllowanceID, item.HolidayOverTimeAllowanceID,
                DataReader.GetNullValue(item.ExtraHourAllowanceFromTime), 
                item.HasTiffinAllowance, item.HasSpecialAllowance, item.TiffinAllowanceID, item.SpecialAllowanceID);

            tc.ExecuteNonQuery(sql);
        }

        #endregion

        #region Update function

        internal static void Update(TransactionContext tc, Shift item)
        {
            string sql = SQLParser.MakeSQL(@"UPDATE Shift SET 
                                 Code=%s, Name=%s, InTime=%D, 
                                 OutTime=%D, hasAbsentTime=%b, AbsentTime=%D,
                                 LateCalcualtion=%n,DelayCalcualtion=%n,EarlyExitBefore=%n, 
                                 MinimumOTHour=%n, IsOverlapingDay=%b,ShortName=%s,
                                 ModifiedBy=%n, ModifiedDate=%d,SequenceNo=%n, 
                                 Status=%n, ShiftType=%n, MinimumInOTHour=%n,  
                                 LunchHour=%n, FlexibleHour=%n, WorkHour=%n,
                                HasBenefits=%b, HasExtraHourAllowance=%b, 
                                HasNightAllowance=%b, HasHolidayAllowance=%b, 
                                HasOtherAllowance=%b, HasRegularOverTime=%b, HasHolidayOverTime=%b,
                                ExtraHourAllowanceID=%n, NightAllowanceID=%n,
                                HolidayAllowanceID=%n, OtherAllowanceID=%n,
                                RegularOverTimeAllowanceID=%n,HolidayOverTimeAllowanceID=%n,
                                ExtraHourAllowanceFromTime=%D,
                                HasTiffinAllowance=%b,HasSpecialAllowance=%b,TiffinAllowanceID=%n,SpecialAllowanceID=%n
                                WHERE ShiftID=%n",
                item.Code, item.Name, item.InTime,
                item.OutTime, item.hasAbsentTime, DataReader.GetNullValue(item.AbsentTime),
                item.LateCalcualtion, item.DelayCalcualtion, item.EarlyExitBefore,
                item.MinimumOutOTHour, item.IsOverlapingDay, item.ShortName,
                item.ModifiedBy, item.ModifiedDate, item.Sequence,
                item.Status, (int)item.ShiftType, item.MinimumInOTHour,
                item.LunchHour, item.FlexibleHour, item.WorkHour,
                item.HasBenefits, item.HasExtraHourAllowance,
                item.HasNightAllowance, item.HasHolidayAllowance,
                item.HasOtherAllowance, item.HasRegularOverTime, item.HasHolidayOverTime,
                item.ExtraHourAllowanceID, item.NightAllowanceID,
                item.HolidayAllowanceID, item.OtherAllowanceID,
                item.RegularOverTimeAllowanceID, item.HolidayOverTimeAllowanceID,
                DataReader.GetNullValue(item.ExtraHourAllowanceFromTime),
                item.HasTiffinAllowance, item.HasSpecialAllowance, item.TiffinAllowanceID, item.SpecialAllowanceID,
                item.ID);

            tc.ExecuteNonQuery(sql);
        }

        #endregion

        #region Get Function

        internal static IDataReader Get(TransactionContext tc, string code, string name, EnumStatus status, int payrollTypeID)
        {
            string sqlClause = string.Empty;
            sqlClause = SQLParser.TagSQL(sqlClause) + SQLParser.MakeSQL("PayrollTypeID = %n", payrollTypeID);
            if (EnumStatus.Active == status || EnumStatus.Inactive == status)
            {
                sqlClause = SQLParser.TagSQL(sqlClause) + SQLParser.MakeSQL("status = %n", status);
            }

            if (!string.IsNullOrWhiteSpace(code))
            {
                sqlClause = SQLParser.TagSQL(sqlClause) + SQLParser.MakeSQL("Code = %s", code);
            }

            if (!string.IsNullOrWhiteSpace(name))
            {
                sqlClause = SQLParser.TagSQL(sqlClause) + SQLParser.MakeSQL("Name LIKE %s", ("%" + name + "%"));
            }

            return tc.ExecuteReader("SELECT * FROM shift %q ", sqlClause);

        }

        internal static IDataReader Get(TransactionContext tc, bool isCounterClock)
        {
            return tc.ExecuteReader("SELECT * FROM Shift Where IsCounterClock=%b ORDER BY Code", isCounterClock);
        }

        internal static IDataReader Get(TransactionContext tc, int nID)
        {
            return tc.ExecuteReader("SELECT * FROM Shift WHERE ShiftID=%n", nID);
        }

        internal static DataTable GetShiftForMobile(TransactionContext tc, int nID)
        {
            return tc.ExecuteDataTable("SELECT ShiftID, Code, Name, ShiftType, InTime, OutTime, ShortName, Status, PayrollTypeID, WorkHour FROM Shift With(NoLock) WHERE ShiftID=%n", nID);
        }

        internal static IDataReader GetAllShift(TransactionContext tc)
        {
            return tc.ExecuteReader("SELECT * FROM Shift");
        }

        internal static DataTable GetAllShiftWithNoLock(TransactionContext tc)
        {
            return tc.ExecuteDataTable("SELECT ShiftID, Code, Name, ShiftType, InTime, OutTime, ShortName, Status, PayrollTypeID, WorkHour FROM Shift With(NoLock)");
        }

        internal static IDataReader GetAllShiftByPayrollType(TransactionContext tc, int payrollTypeId)
        {
            return tc.ExecuteReader("SELECT * FROM Shift WHERE PayrollTypeID = %n", payrollTypeId);
        }

        internal static IDataReader GetShiftByIds(TransactionContext tc, string shiftIds)
        {
            return tc.ExecuteReader(@"
            SELECT * FROM Shift
            WHERE ShiftID IN (%q)", shiftIds);
        }

        internal static IDataReader Get(TransactionContext tc, string shiftName)
        {
            return tc.ExecuteReader("SELECT * FROM Shift WHERE ShortName=%s", shiftName);
        }

        #endregion

        #region Delete function

        internal static void Delete(TransactionContext tc, int nID)
        {
            tc.ExecuteNonQuery("DELETE FROM [Shift] WHERE ShiftID=%n", nID);
        }

        #endregion

        #region IsExist

        internal static bool IsExist(TransactionContext tc, string shiftCode, string shortName, int shiftid)
        {
            bool isExist = false;
            if (shiftCode != string.Empty && shortName != string.Empty)
            {
                object obj =
                    tc.ExecuteScalar("SELECT Count(*) from Shift Where Code=%s AND ShortName=%s and ShiftID <> %n",
                        shiftCode, shortName, shiftid);
                isExist = Convert.ToInt32(obj) > 0 ? true : false;
            }
            else if (shiftCode != string.Empty)
            {
                object obj = tc.ExecuteScalar("SELECT Count(*) from Shift Where Code=%s and ShiftID <> %n", shiftCode,
                    shiftid);
                isExist = Convert.ToInt32(obj) > 0 ? true : false;
            }
            else if (shortName != string.Empty)
            {
                object obj = tc.ExecuteScalar("SELECT Count(*) from Shift Where ShortName=%s and ShiftID <> %n",
                    shortName, shiftid);
                isExist = Convert.ToInt32(obj) > 0 ? true : false;
            }

            return isExist;
        }

        #endregion
    }

    #endregion
}