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

namespace Payroll.Service
{
    internal class ClaimRuleDA
    {
        #region Constructor
        private ClaimRuleDA()
        {}
        #endregion

        #region ClaimRule

        #region  Insert function

        internal static void Insert(TransactionContext tc, ClaimRule item)
        {
            tc.ExecuteNonQuery(@"INSERT INTO ClaimRule(ClaimRuleID, IsAnnualBalance, IsAtActualPayment, 
            NotMaximum, MaxServiceLength, MonthDurationFromLastClaim, NoOfDependent,WFStatus,
            ClaimBasicID, FixedAmount, IsGradeWiseDifferent, AllowAdvance,
            CreatedBy, CreatedDate,OnceMonthly,OnceDaily)" +
            " VALUES(%n,%b,%b,%n,%n,%n,%n,%n,%n,%n,%b,%b,%n,%d,%n,%n)",
            DataReader.GetNullValue(item.ID), item.IsAnnualBalance, item.IsAtActualPayment, 
            item.NotMaximum, 
            item.MaxServiceLength,
            item.MonthDurationFromLastClaim, item.NoOfDependent, 
            item.WFStatus, DataReader.GetNullValue(item.ClaimBasicID), item.FixedAmount, item.IsDesignationWiseDifferent, item.AllowAdvance,
            DataReader.GetNullValue(item.CreatedBy), DataReader.GetNullValue(item.CreatedDate), item.OnceMonthly,item.OnceDaily);
        }

        #endregion

        #region  Update function

        internal static void Update(TransactionContext tc, ClaimRule item)
        {
            tc.ExecuteNonQuery(@"UPDATE ClaimRule SET IsAnnualBalance=%b, IsAtActualPayment=%b, 
            NotMaximum=%n, MaxServiceLength=%n, MonthDurationFromLastClaim=%n, NoOfDependent=%n,WFStatus=%n,
            ClaimBasicID=%n, FixedAmount=%n, IsGradeWiseDifferent=%b, AllowAdvance=%b,
            ModifiedBy=%n, ModifiedDate=%d, OnceMonthly=%n,OnceDaily=%n" +
            " WHERE ClaimRuleID=%n", item.IsAnnualBalance, item.IsAtActualPayment, item.NotMaximum,
            item.MaxServiceLength, item.MonthDurationFromLastClaim, item.NoOfDependent, item.WFStatus,
            DataReader.GetNullValue(item.ClaimBasicID), item.FixedAmount, item.IsDesignationWiseDifferent, item.WFStatus,
            DataReader.GetNullValue(item.ModifiedBy), DataReader.GetNullValue(item.ModifiedDate), 
            item.OnceMonthly,item.OnceDaily, DataReader.GetNullValue(item.ID));
        }

        #endregion

        #region Get Function

        internal static IDataReader Get(TransactionContext tc)
        {
            return tc.ExecuteReader(@"SELECT cr.*,cs.ItemName ClaimBasicName FROM ClaimRule AS cr 
                                     INNER JOIN ClaimBasic cs ON cr.ClaimBasicID = cs.ClaimBasicID");
        }

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

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

        internal static IDataReader Get(TransactionContext tc, int claimID, int designationID)
        {
            return tc.ExecuteReader("SELECT * FROM ClaimRule cr INNER JOIN ClaimRuleGrades crgl ON cr.ClaimRuleID = crgl.ClaimRuleID WHERE crgl.GradeID=%n And cr.ClaimBasicID=%n",
                                        designationID, claimID);
        }
        #endregion

        #region  Delete function

        internal static void Delete(TransactionContext tc, int nID)
        {
            tc.ExecuteNonQuery("DELETE FROM ClaimRule WHERE ClaimRuleID=%n", nID);
        }

        internal static void DeleteByClaimBasicID(TransactionContext tc, int nID)
        {
            tc.ExecuteNonQuery("DELETE FROM ClaimRule WHERE ClaimRuleID=%n", nID);
        }

        #endregion

        #endregion

        #region ClaimRuleGrades

        #region  Insert function

        internal static void InsertClaimRuleGrades(TransactionContext tc, ClaimRuleDesignations item)
        {
            tc.ExecuteNonQuery(@"INSERT INTO ClaimRuleGrades(ClaimRuleGradesID, ClaimPaymentGradesID, ClaimRuleID, GradeID, Amount,ClaimBasicItemId)" +
            " VALUES(%n,%n,%n,%n,%n,%n)",
            DataReader.GetNullValue(item.ID), DataReader.GetNullValue(item.ClaimPaymentGradesID), DataReader.GetNullValue(item.ClaimRuleID),
            DataReader.GetNullValue(item.DesignationID), item.Amount, DataReader.GetNullValue(item.ClaimBasicItemId));
        }

        #endregion

        #region  Update function

        internal static void UpdateClaimRuleGrades(TransactionContext tc, ClaimRuleDesignations item)
        {
            tc.ExecuteNonQuery(@"UPDATE ClaimRuleGrades SET ClaimPaymentGradesID=%n, ClaimRuleID=%n, GradeID=%n, Amount=%n, ClaimBasicItemId=%n " +
            " WHERE ClaimRuleGradesID=%n", DataReader.GetNullValue(item.ClaimPaymentGradesID), DataReader.GetNullValue(item.ClaimRuleID),
            DataReader.GetNullValue(item.DesignationID), item.Amount, DataReader.GetNullValue(item.ClaimBasicItemId), DataReader.GetNullValue(item.ID));
        }

        #endregion

        #region Get Function

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

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

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

        #endregion

        #region  Delete function

        internal static void DeleteClaimRuleGrades(TransactionContext tc, int nID)
        {
            tc.ExecuteNonQuery("DELETE FROM ClaimRuleGrades WHERE ClaimRuleGradesID=%n", nID);
        }

        internal static void DeleteClaimRuleGradesByClaimRuleID(TransactionContext tc, int nID)
        {
            tc.ExecuteNonQuery("DELETE FROM ClaimRuleGrades WHERE ClaimRuleID=%n", nID);
        }

        #endregion

        #endregion

       
    }
}