using System;
using Payroll.BO;
using System.Data;
using System.Linq;
using Ease.CoreV35.Model;
using System.Data.SqlClient;
using Ease.CoreV35.DataAccess;
using System.Collections.Generic;
using Ease.CoreV35.DataAccess.SQL;

namespace Payroll.Service
{
    #region  LeaveParameter DA
    public class LeaveParameterDA
    {
        #region  Constructor
        public LeaveParameterDA() { }
        #endregion

        #region  Insert function
        public static void Insert(TransactionContext tc, LeaveParameter oItem)
        {
            tc.ExecuteNonQuery("INSERT INTO LEAVEPARAMETER(LEAVEPARAMID, LEAVEID, ACCUMULATEDLEAVE, ISACTIVE, FortifiedMonth, AllowAdvance, IgnoreHolidays, MonthlyBalance, IsForfited, ApplicableFor,CreatedBy,CreationDate,PayrollTypeID)" +
                " VALUES(%n, %n, %n, %n, %n, %n, %n, %n, %n, %n,%n,%d,%n)", oItem.ID.Integer, oItem.LeaveId, 
                oItem.MaxAccumulatedDays, oItem.IsActive, oItem.ForfitedMonth, oItem.AllowAdvance, 
                oItem.IgnoreHoliday,(int) oItem.CalculationType, oItem.IsForfited, (int)oItem.ApplicableFor,
                oItem.CreatedBy.Integer,oItem.CreatedDate,oItem.PayrollTypeID);
        }
        public static void InsertChildren(TransactionContext tc, LeaveParameterDetail oItem)
        {
            tc.ExecuteNonQuery("INSERT INTO LEAVEPARAMDETAIL(LEAVEPARAMID, MAXDAYS, MAXCF, MAXENCASH, ID, YEAR)" +
                    " VALUES(%n, %n, %n, %n, %n, %n)", oItem.LeaveParamId, oItem.MaxDays, oItem.MaxCF, 
                    oItem.MaxEncash, oItem.ID.Integer, oItem.Year);
        }
        #endregion

        #region  Update function
        public static void Update(TransactionContext tc, LeaveParameter oItem)
        {
            tc.ExecuteNonQuery("UPDATE LEAVEPARAMETER SET LEAVEID=%n, ACCUMULATEDLEAVE=%n, ISACTIVE=%n,"+
                    " FortifiedMonth=%n, AllowAdvance=%n, IgnoreHolidays=%n, MonthlyBalance=%n, IsForfited=%n,"+
                    " ApplicableFor=%n,ModifiedBy=%n,ModifiedDate=%d WHERE LEAVEPARAMID=%n",
                    oItem.LeaveId, oItem.MaxAccumulatedDays, oItem.IsActive, oItem.ForfitedMonth,
                    oItem.AllowAdvance, oItem.IgnoreHoliday,(int) oItem.CalculationType, oItem.IsForfited,
                    (int)oItem.ApplicableFor,oItem.ModifiedBy.Integer,oItem.ModifiedDate,
                    oItem.ID.Integer);

        }
        #endregion

        #region ID Generation function
        public static int GetNewID(TransactionContext tc)
        {
            return tc.GenerateID("LeaveParameter", "LeaveParamId");
        }
        public static int GetNewDetailID(TransactionContext tc)
        {
            return tc.GenerateID("LEAVEPARAMDETAIL", "ID");
        }
        #endregion

        #region Get Function
        public static IDataReader Get(TransactionContext tc)
        {
            return tc.ExecuteReader("SELECT * FROM LeaveParameter");
        }
        public static IDataReader GetByParamId(TransactionContext tc, int paramId)
        {
            return tc.ExecuteReader("SELECT * FROM LeaveParameter where LEAVEPARAMID=%n", paramId);
        }
        public static IDataReader GetLeaveDetail(TransactionContext tc, int leaveID)
        {
            return tc.ExecuteReader("SELECT * FROM LeaveParamDetail where LEAVEID=%n", leaveID);
        }


        public static IDataReader GetByLeaveID(TransactionContext tc, int leaveID)
        {
            return tc.ExecuteReader("SELECT * FROM LeaveParameter where LEAVEID=%n", leaveID);
        }

        public static IDataReader Get(TransactionContext tc, bool isActive, ID PayrolltypeID)
        {
            return tc.ExecuteReader("SELECT * FROM LeaveParameter where IsActive=%b AND Payrolltypeid=%n", isActive,  PayrolltypeID.Integer);
        }
        public static IDataReader Get(TransactionContext tc, int nLeaveParamId)
        {
            return tc.ExecuteReader("SELECT * FROM LeaveParameter WHERE LeaveParamId=%n", nLeaveParamId);
        }
        public static IDataReader Get(TransactionContext tc, int nLeaveId, int nGradeId)
        {
            return tc.ExecuteReader("SELECT * FROM LeaveParameter WHERE LeaveParamId in(select LeaveParamId from LeaveGrade where GradeId=%n) and LeaveId=%n", nGradeId, nLeaveId);
        }

        public static IDataReader Get(TransactionContext tc, int sbuId, int locationId, int functionId, bool Isactive)
        {
            return tc.ExecuteReader("select * from LeaveParameter P where P.IsActive=%b and P.LeaveParamId in (select LeaveParamId from LeaveFunction where functionId =%n " +
                " and LeaveParamId in(select LeaveParamId from LeaveLocation where LocationId =%n " +
                " and LeaveParamId in (select LeaveParamId from LeaveSBU where SBUId =%n)))", Isactive, functionId, locationId, sbuId);
        }
        public static IDataReader Get(TransactionContext tc, int sbuId, int locationId, int functionId)
        {
            return tc.ExecuteReader("select * from LeaveParameter P where P.LeaveParamId in (select LeaveParamId from LeaveFunction where functionId =%n " +
                " and LeaveParamId in(select LeaveParamId from LeaveLocation where LocationId =%n " +
                " and LeaveParamId in (select LeaveParamId from LeaveSBU where SBUId =%n)))", functionId, locationId, sbuId);
        }
        public static IDataReader Get(TransactionContext tc, int sbuId, int locationId, int functionId, int nLeaveID)
        {
            return tc.ExecuteReader("select * from LeaveParameter P where P.LeaveParamId in (select LeaveParamId from LeaveFunction where FunctionId =%n AND LeaveID=%n " +
                " and LeaveParamId in(select LeaveParamId from LeaveLocation where LocationId =%n " +
                " and LeaveParamId in (select LeaveParamId from LeaveSBU where SBUId =%n)))", functionId, nLeaveID, locationId, sbuId);
        }

        public static IDataReader GetParameter(TransactionContext tc, int sbuId, int locationId, int functionId, int nLeaveID)
        {
            return tc.ExecuteReader("select P.* from LeaveParameter P, LeaveFunction LF, LeaveLocation LL, LeaveSBU LB "
            + " WHERE P.LeaveParamId = LF.LeaveParamId AND P.LeaveParamId = LL.LeaveParamId AND P.LeaveParamId = LB.LeaveParamId "
            + " AND P.LeaveID=%n  AND LF.FunctionId =%n AND  LL.LocationId=%n AND LB.SBUId=%n "
                , nLeaveID, functionId, locationId, sbuId);
        }

        public static IDataReader GetByLeaveId(TransactionContext tc, int leaveId)
        {
            return tc.ExecuteReader("SELECT * FROM LeaveParameter where leaveId=%n", leaveId);
        }
        public static IDataReader GetLeaveSBUs(TransactionContext tc, int nParamId)
        {
            return tc.ExecuteReader("SELECT * FROM LeaveSBU WHERE LeaveParamId=%n", nParamId);
        }
        public static IDataReader GetLeaveLocations(TransactionContext tc, int nParamId)
        {
            return tc.ExecuteReader("SELECT * FROM LeaveLocation WHERE LeaveParamId=%n", nParamId);
        }
        public static IDataReader GetLeaveFunctions(TransactionContext tc, int nParamId)
        {
            return tc.ExecuteReader("SELECT * FROM LeaveFunction WHERE LeaveParamId=%n", nParamId);
        }
        public static IDataReader GetDetails(TransactionContext tc, int nLeaveParamId)
        {
            return tc.ExecuteReader("SELECT * FROM LeaveParamDetail WHERE LEAVEPARAMID=%n", nLeaveParamId);
        }
        public static IDataReader GetDetails(TransactionContext tc)
        {
            return tc.ExecuteReader("SELECT * FROM LeaveParamDetail");
        }
        public static IDataReader GetDetail(TransactionContext tc, int paramID)
        {
            return tc.ExecuteReader("SELECT * FROM LeaveParamDetail WHERE LeaveParamId=%n", paramID);
        }
        
        #endregion

        #region  Delete function
        public static void Delete(TransactionContext tc, int nLeaveParamId)
        {
            tc.ExecuteNonQuery("DELETE FROM LeaveParameter WHERE LeaveParamId=%n", nLeaveParamId);
        }
        public static void DeleteLeaveSBU(TransactionContext tc, int nParamId)
        {
            tc.ExecuteNonQuery("DELETE FROM LeaveSBU WHERE LeaveParamId=%n", nParamId);
        }
        public static void DeleteLeaveLocation(TransactionContext tc, int nParamId)
        {
            tc.ExecuteNonQuery("DELETE FROM LeaveLocation WHERE LeaveParamId=%n", nParamId);
        }
        public static void DeleteLeaveFunction(TransactionContext tc, int nParamId)
        {
            tc.ExecuteNonQuery("DELETE FROM LeaveFunction WHERE LeaveParamId=%n", nParamId);
        }
        public static void DeleteDetails(TransactionContext tc, int nLeaveParamId)
        {
            tc.ExecuteNonQuery("DELETE FROM LeaveParamDetail WHERE LeaveParamId=%n", nLeaveParamId);
        }
        #endregion
    }
    #endregion
}