using HRM.BO;
using Ease.Core.DataAccess;
using System;
using System.Data;


namespace HRM.DA
{
    #region OpiItem

    internal class OpiItemDA
    {
        #region Constructor

        public OpiItemDA()
        {
        }

        #endregion

        #region Insert function

        internal static void Insert(TransactionContext tc, OpiItem oItem)
        {
            tc.ExecuteNonQuery(
                "INSERT INTO OpiItem(OpiItemID, Code, Name, OpiType, SequenceNO, Status, CreatedBy, CreationDate, payrolltypeid)" +
                " VALUES(%n, %s, %s, %n, %n, %n, %n, %D, %n)", oItem.ID, oItem.Code, oItem.Name, oItem.OpiType,
                oItem.Sequence, oItem.Status, DataReader.GetNullValue(oItem.CreatedBy),
                DataReader.GetNullValue(oItem.CreatedDate), oItem.PayrolltypeID);
        }

        #endregion

        #region Update function

        internal static void Update(TransactionContext tc, OpiItem oItem)
        {
            tc.ExecuteNonQuery(
                "UPDATE OpiItem SET Code=%s, Name=%s, OpiType=%n, SequenceNO=%n, Status=%n, ModifiedBy=%n, ModifiedDate=%d" +
                " WHERE OpiItemID=%n", oItem.Code, oItem.Name, oItem.OpiType, oItem.Sequence, oItem.Status,
                DataReader.GetNullValue(oItem.ModifiedBy), DataReader.GetNullValue(oItem.ModifiedDate), oItem.ID);
        }

        #endregion

        #region Get Function

        internal static IDataReader Get(TransactionContext tc)
        {
            return tc.ExecuteReader("SELECT * FROM OpiItem WHERE STATUS=%n Order By Code", (int)EnumStatus.Active);
        }

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

        internal static IDataReader Get(TransactionContext tc, EnumStatus status)
        {
            if (EnumStatus.Active == status || EnumStatus.Inactive == status)
            {
                return tc.ExecuteReader("SELECT * FROM OpiItem where Status=%n Order By SequenceNo", status);
            }
            else
            {
                return tc.ExecuteReader("SELECT * FROM OpiItem Order By SequenceNo");
            }
        }

        internal static IDataReader Get(TransactionContext tc, EnumStatus status, int payrollTypeID)
        {
            if (EnumStatus.Active == status || EnumStatus.Inactive == status)
            {
                return tc.ExecuteReader("SELECT * FROM OpiItem where Status=%n and payrollTypeID = %n order by SequenceNO", status, payrollTypeID);
            }
            else
            {
                return tc.ExecuteReader("SELECT * FROM OpiItem where payrolltypeid=%n order by SequenceNO", payrollTypeID);
            }
        }

        internal static IDataReader Get(TransactionContext tc, EnumOpiType opiType, EnumStatus status)
        {
            if (EnumStatus.Active == status || EnumStatus.Inactive == status)
            {
                return tc.ExecuteReader("SELECT * FROM OpiItem WHERE OpiType=%n AND Status=%n", opiType, status);
            }
            else
            {
                return tc.ExecuteReader("SELECT * FROM OpiItem WHERE OpiType=%n", opiType);
            }
        }

        #endregion

        #region Delete function

        internal static void Delete(TransactionContext tc, int nID)
        {
            tc.ExecuteNonQuery("DELETE FROM OpiItem WHERE OpiItemID=%n", nID);
        }
        internal static IDataReader GetMonthyIndividualAndOneOff(TransactionContext tc, int payrolltypeid, EnumStatus status)
        {
            return tc.ExecuteReader("Select DISTINCT ALLWD.* from  OpiItem ALLWD,OPIParameter ADPB"
                                    + " Where ALLWD.OpiItemID=ADPB.OpiItemID"
                                    + " AND ALLWD.Status=%n"
                                   + " AND (( ADPB.OpiPeriodicity =%n ) OR ( ADPB.OpiPeriodicity =%n  " +
                                    " and ADPB.EntitleType=%n ))  AND ALLWD.PayrollTypeID=%n ", status,
                                    EnumPeriodicity.OneOff, EnumPeriodicity.Monthly, EnumEntitleType.Individual, payrolltypeid);
        }
        #endregion
    }

    #endregion
}