EchoTex_Payroll/HRM.DA/DA/Bonus/ProdBonusWorkScheduleDA.cs

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


namespace HRM.DA
{
    internal class ProdBonusWorkScheduleDA
    {
        #region Constructor

        private ProdBonusWorkScheduleDA()
        {
        }

        #endregion

        #region Insert function

        internal static void Insert(TransactionContext tc, ProdBonusWorkSchedule item)
        {
            tc.ExecuteNonQuery(
                "INSERT INTO ProdBonusWorkSchedule(ProdBonusWorkScheduleID, ProdBonusSetupID,ProdBonusLineID,StartDateTime, EndDateTime)" +
                " VALUES(%n, %n,%n, %D,%D)", item.ID, item.ProdBonusSetupID, item.ProdBonusLineID, item.StartDateTime,
                item.EndDateTime);
        }

        #endregion

        #region Update function

        internal static void Update(TransactionContext tc, ProdBonusWorkSchedule item)
        {
            //string sql = SQLParser.MakeSQL(
            //    "UPDATE ProdBonusWorkSchedule SET ProdBonusSetupID=%n,ProdBonusLineID=%n, StartDateTime=%D, EndDateTime=%D " +
            //    "WHERE ProdBonusWorkScheduleID=%n", item.ProdBonusSetupID, item.ProdBonusLineID, item.StartDateTime,
            //    item.EndDateTime, item.ID);
            string sql = SQLParser.MakeSQL(
                "UPDATE ProdBonusWorkSchedule SET ProdBonusSetupID=%n,ProdBonusLineID=%n, StartDateTime=%D" +
                "WHERE ProdBonusWorkScheduleID=%n", item.ProdBonusSetupID, item.ProdBonusLineID, item.StartDateTime, item.ID);
            tc.ExecuteNonQuery(sql);
        }

        #endregion

        #region Get Function

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

        internal static IDataReader GetBySetupId(TransactionContext tc, int nSetupID)
        {
            return tc.ExecuteReader("SELECT * FROM ProdBonusWorkSchedule where ProdBonusSetupID=%n", nSetupID);
        }
        internal static DataTable GetschedulewithTime(TransactionContext tc, int lineID)
        {
            return tc.ExecuteDataTable(@"select a.*,bb.scheduleCount , (select max(ps.InTime) from ProdBonusAttn ps where ps.ProdBonusLineID =%n  
and  DATEADD(dd, 0, DATEDIFF(dd, 0, ps.InTime)) = bb.scheduleDate) as inTime,
(select max(ps.OutTime) from ProdBonusAttn ps where ps.ProdBonusLineID =%n  
and  DATEADD(dd, 0, DATEDIFF(dd, 0, ps.InTime)) = bb.scheduleDate) as outTime from 
(SELECT * FROM ProdBonusWorkSchedule where ProdBonusLineID=%n) a,
(
select DATEADD(dd, 0, DATEDIFF(dd, 0, InTime)) scheduleDate,COUNT(*) scheduleCount  from ProdBonusAttn where 
ProdBonusLineID =%n  group by DATEADD(dd, 0, DATEDIFF(dd, 0, InTime))  
 ) bb
where bb.scheduleDate = DATEADD(dd, 0, DATEDIFF(dd, 0,  a.StartDateTime)) ", lineID, lineID, lineID, lineID);
        }


        internal static IDataReader GetByLineID(TransactionContext tc, int lineID)
        {
            return tc.ExecuteReader("SELECT * FROM ProdBonusWorkSchedule where ProdBonusLineID=%n", lineID);
        }
        internal static IDataReader Get(TransactionContext tc, int nID)
        {
            return tc.ExecuteReader("SELECT * FROM ProdBonusWorkSchedule WHERE ProdBonusWorkScheduleID=%n", nID);
        }

        #endregion

        #region Delete function

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


        #endregion
    }
}