CEL_Payroll/Payroll.Service/TrainingS/DA/TrainingScheduleDA.cs

using System;
using System.Data;
using Ease.CoreV35.DataAccess;
using Ease.CoreV35.Model;
using Payroll.BO;


namespace Payroll.Service
{
    class TrainingScheduleDA
    {
        //DataReader.GetNullValue(item.DepartmentID, IDType.Integer)
        #region Get Function
        public static IDataReader Get(TransactionContext tc, int payrollTypeID)
        {
            return tc.ExecuteReader("SELECT * from TrainingSchedule WHERE PAYROLLTYPEID=%n", payrollTypeID);
        }

        public static IDataReader GetTS(TransactionContext tc, int trainingScheduleID)
        {
            return tc.ExecuteReader("SELECT * from TrainingSchedule WHERE TrainingScheduleID=%n", trainingScheduleID);
        }
        public static IDataReader GetbyStatus(TransactionContext tc, int status, int payrollTypeID)
        {
            return tc.ExecuteReader("SELECT * from TrainingSchedule WHERE ScheduleStatus=%n AND PAYROLLTYPEID=%n", status, payrollTypeID);
        }
        public static IDataReader Get(TransactionContext tc, DateTime today, int EmpID, int status, int payrollTypeID)
        {
            return tc.ExecuteReader("SELECT ts.* FROM TrainingSchedule ts,TrainingScheduleEmployee tse WHERE ts.TrainingScheduleID=tse.TrainingScheduleID AND tse.EmployeeID=%n AND %d BETWEEN ts.EnrolledStartDate AND ts.EnrolledEndDate AND ts.ScheduleStatus=%n AND PAYROLLTYPEID=%n", EmpID, today, status, payrollTypeID);
        }
        public static IDataReader Get(TransactionContext tc, bool active, int payrollTypeID)
        {
            return tc.ExecuteReader("SELECT * from TrainingSchedule WHERE ScheduleStatus=%n AND PAYROLLTYPEID=%n", active, payrollTypeID);
        }

        public static IDataReader Get(TransactionContext tc, DateTime today, int payrollTypeID)
        {
            return tc.ExecuteReader("SELECT * from TrainingSchedule WHERE %d BETWEEN EnrolledStartDate AND EnrolledEndDate AND PAYROLLTYPEID=%n", today, payrollTypeID);
        }
        public static IDataReader Get(TransactionContext tc, DateTime fromDate, DateTime toDate, int training, int payrollTypeID)
        {
            return tc.ExecuteReader("SELECT * from TrainingSchedule WHERE StartDate BETWEEN %d AND %d AND TrainingID=%n AND PAYROLLTYPEID=%n", fromDate, toDate, training, payrollTypeID);
         }
        public static IDataReader Get(TransactionContext tc, int training, int TNAID, bool active, int payrollTypeID)
        {
            return tc.ExecuteReader("SELECT * from TrainingSchedule WHERE TrainingID=%n  AND TNAnalysisID=%n  AND ScheduleStatus=%n AND PAYROLLTYPEID=%n", training, TNAID, active, payrollTypeID);
        }
        public static IDataReader Get(TransactionContext tc, int training, int TNAID, int payrollTypeID)
        {
            return tc.ExecuteReader("SELECT * from TrainingSchedule WHERE TrainingID=%n  AND TNAnalysisID=%n AND PAYROLLTYPEID=%n", training, TNAID, payrollTypeID);
        }
        public static IDataReader Get(TransactionContext tc, DateTime fromDate, DateTime toDate, int training, bool active, int payrollTypeID)
         {
             return tc.ExecuteReader("SELECT * from TrainingSchedule WHERE StartDate BETWEEN %d AND %d AND TrainingID=%n AND ScheduleStatus=%n AND PAYROLLTYPEID=%n", fromDate, toDate, training, active, payrollTypeID);
         }
        public static IDataReader Get(TransactionContext tc, DateTime fromDate, DateTime toDate, bool active, int payrollTypeID)
         {
             return tc.ExecuteReader("SELECT * from TrainingSchedule WHERE StartDate BETWEEN %d AND %d AND ScheduleStatus=%n AND PAYROLLTYPEID=%n", fromDate, toDate, active, payrollTypeID);
         }
        public static IDataReader Get(TransactionContext tc, DateTime fromDate, DateTime toDate, int payrollTypeID)
         {
             return tc.ExecuteReader("SELECT * from TrainingSchedule WHERE StartDate BETWEEN %d AND %d AND PAYROLLTYPEID=%n ", fromDate, toDate, payrollTypeID);
         }
        internal static DataSet Get(TransactionContext tc, string query)
        {
            return tc.ExecuteDataSet(query);
        }
        #region Child Get

        public static IDataReader getScheduleEmp(TransactionContext tc, ID TSEmpId)
        {
            return tc.ExecuteReader("SELECT * from TrainingScheduleEmployee where TrainingScheduleEmployeeID=%n", TSEmpId.Integer);
        }
        public static IDataReader GetTrainingScheduleDates(TransactionContext tc)
        {
            return tc.ExecuteReader("SELECT * from TrainingScheduleDate");
        }

        public static IDataReader GetTrainingScheduleEmployees(TransactionContext tc, ID trainingScheduleId)
        {
            return tc.ExecuteReader("SELECT * from TrainingScheduleEmployee where TrainingScheduleID=%n",trainingScheduleId.Integer);
        }

        public static IDataReader GetTrainingScheduleDates(TransactionContext tc, int trainingScheduleID)
        {
            return tc.ExecuteReader("SELECT * from TrainingScheduleDate where TrainingScheduleID=%n",trainingScheduleID);
        }
        #endregion

        #endregion

        #region Insert Function

        public static void Insert(TransactionContext tc, TrainingSchedule oItem)
        {
            tc.ExecuteNonQuery("INSERT INTO TrainingSchedule(TrainingScheduleID, TRAININGID,TrainingNatureID, InstituteID, TrainerDescription, EnrolledStartDate, EnrolledEndDate,IsShowInWeb,TNAnalysisID, StartDate,EndDate,Cost,MaxParticipants,EnrolledParticipants,CourseOutline,ScheduleStatus,VenueCost, RefreshmentCost, TrainerFees, OthesCost,Venue, PAYROLLTYPEID)" +
          " VALUES(%n,%n,%n,%n,%s,%d,%d,%b,%n,%d,%d,%n,%n,%n,%s,%n,%n,%n,%n,%n,%s,%n)", oItem.ID.Integer, DataReader.GetNullValue(oItem.TrainingID, IDType.Integer), DataReader.GetNullValue(oItem.TrainingNatureID, IDType.Integer), DataReader.GetNullValue(oItem.InstituteID, IDType.Integer), oItem.TrainerDescription, oItem.EnrolledStartDate, oItem.EnrolledEndDate, oItem.IsShowInWeb, DataReader.GetNullValue(oItem.TNAnalysisID, IDType.Integer), oItem.StartDate, oItem.EndDate, DataReader.GetNullValue(oItem.Cost), DataReader.GetNullValue(oItem.MaxParticipants), 
          DataReader.GetNullValue(oItem.EnrolledParticipants), DataReader.GetNullValue(oItem.CourseOutline), DataReader.GetNullValue(oItem.ScheduleStatus),oItem.VenueCost,oItem.RefreshmentCost,oItem.TrainerFees,oItem.OthesCost,oItem.Venue, oItem.PayrollTypeID.Integer);

        }
        public static void InsertTrainingScheduleAttn(TransactionContext tc, TrainingScheduleAttn oItem)
        {
            tc.ExecuteNonQuery("INSERT INTO TrainingScheduleAttn(TSAttnID, TrainingScheduleID,TSDateID, EmployeeID)" +
            " VALUES(%n,%n,%n,%n)", oItem.ID.Integer, oItem.TrainingScheduleID.Integer, oItem.TSDateID.Integer, oItem.EmployeeID.Integer);

        }
        #region Child Insert
        
        public static void InsertTrainingEmployee(TransactionContext tc, TrainingScheduleEmployee oItem)
        {
            string sql = SQLParser.MakeSQL("INSERT INTO TrainingScheduleEmployee(TrainingScheduleEmployeeID, TrainingScheduleID, ScheduleEmpStatus, EmployeeID, ProposedByID, ProPosedDate,Remarks)" +
            " VALUES(%n,%n,%n,%n,%n,%d,%s)", oItem.ID.Integer, oItem.TrainingScheduleID.Integer, oItem.ScheduleEmpStatus, oItem.EmployeeID.Integer, oItem.ProposedByID.Integer, oItem.ProposedDate, oItem.Remarks);
            tc.ExecuteNonQuery(sql);

        }

        public static void InsertTrainingScheduleDate(TransactionContext tc, TrainingScheduleDate oItem)
        {
            tc.ExecuteNonQuery("INSERT INTO TrainingScheduleDate(TrainingScheduleDateID, TrainingScheduleID, ScheduleDate,StartTime,EndTime,Remarks)" +
             " VALUES(%n,%n,%d,%D,%D,%s)", oItem.ID.Integer,DataReader.GetNullValue(oItem.TrainingScheduleID,IDType.Integer), oItem.ScheduleDate,oItem.StartTime,oItem.EndTime, oItem.Remarks);

        }
        #endregion

        #endregion

        #region Update Function

        public static void Update(TransactionContext tc, TrainingSchedule oItem)
        {
            tc.ExecuteNonQuery("UPDATE TrainingSchedule SET TRAININGID=%n,TrainingNatureID=%n, InstituteID=%n,TrainerDescription=%s,EnrolledStartDate=%d,EnrolledEndDate=%d,IsShowInWeb=%b,TNAnalysisID=%n,StartDate=%d,EndDate=%d,Cost=%n,MaxParticipants=%n,EnrolledParticipants=%n,CourseOutline=%s,ScheduleStatus=%n, PAYROLLTYPEID=%n, VenueCost=%n, RefreshmentCost=%n, TrainerFees=%n, OthesCost=%n, Venue=%s" +
        " WHERE TrainingScheduleID=%n", DataReader.GetNullValue(oItem.TrainingID, IDType.Integer), DataReader.GetNullValue(oItem.TrainingNatureID, IDType.Integer), DataReader.GetNullValue(oItem.InstituteID, IDType.Integer),
        oItem.TrainerDescription, oItem.EnrolledStartDate, oItem.EnrolledEndDate,oItem.IsShowInWeb,
        DataReader.GetNullValue(oItem.TNAnalysisID, IDType.Integer), oItem.StartDate, oItem.EndDate, 
        DataReader.GetNullValue(oItem.Cost), DataReader.GetNullValue(oItem.MaxParticipants), 
        oItem.EnrolledParticipants, DataReader.GetNullValue(oItem.CourseOutline),
        oItem.ScheduleStatus, oItem.PayrollTypeID.Integer, oItem.VenueCost, oItem.RefreshmentCost, oItem.TrainerFees, oItem.OthesCost,oItem.Venue, oItem.ID.Integer);

        }
        public static void updateScheduleEmp(TransactionContext tc, TrainingScheduleEmployee oItem)
        {
            tc.ExecuteNonQuery("UPDATE TrainingScheduleEmployee SET TrainingScheduleID=%n, ScheduleEmpStatus=%n, EmployeeID=%n, ProposedByID=%n, ProPosedDate=%d,Remarks=%s WHERE TrainingScheduleEmployeeID=%n", oItem.TrainingScheduleID.Integer, oItem.ScheduleEmpStatus, oItem.EmployeeID.Integer, oItem.ProposedByID.Integer, oItem.ProposedDate, oItem.Remarks,oItem.ID.Integer);
        }
        public static void updateScheduleEmp(TransactionContext tc, int SEmpID, int status)
        {
            tc.ExecuteNonQuery("UPDATE TrainingScheduleEmployee SET ScheduleEmpStatus=%n WHERE TrainingScheduleEmployeeID=%n", status, SEmpID);
        }
        #endregion

        #region Delete Function
        public static void Delete(TransactionContext tc, int id)
        {
            tc.ExecuteNonQuery("Delete from TrainingSchedule where TrainingScheduleID=%n", id);
        }

        public static void DeleteScheduleDetail(TransactionContext tc, int trainingScheduleID)
        {
            tc.ExecuteNonQuery("Delete from TrainingSchEmpCost where TrainSchEmpID In (select  TrainingScheduleEmployeeID from TrainingScheduleEmployee where TrainingScheduleID=%n)", trainingScheduleID);
            tc.ExecuteNonQuery("Delete from TrainingScheduleDate where TrainingScheduleID=%n", trainingScheduleID);
            tc.ExecuteNonQuery("Delete from TrainingScheduleEmployee where TrainingScheduleID=%n", trainingScheduleID);
        }


        public static void DeleteTrainingEmployees(TransactionContext tc, int trainingScheduleID)
        {
            tc.ExecuteNonQuery("Delete from TrainingScheduleEmployee where TrainingScheduleID=%n", trainingScheduleID);
        }

        public static void DeleteTrainingScheduleDate(TransactionContext tc, int id)
        {
            tc.ExecuteNonQuery("Delete from TrainingScheduleDate where TrainingScheduleDateID=%n", id);
        }

        public static void DeleteTrainingScheduleDates(TransactionContext tc, int trainingScheduleID)
        {
            tc.ExecuteNonQuery("Delete from TrainingScheduleDate where TrainingScheduleID=%n", trainingScheduleID);
        }

        public static void DeleteTrainingScheduleAttn(TransactionContext tc, int trainingScheduleID)
        {
            tc.ExecuteNonQuery("Delete from TrainingScheduleAttn where TrainingScheduleID=%n", trainingScheduleID);

        }
        public static void DeleteNominatedEmp(TransactionContext tc, int SEmpID)
        {
            tc.ExecuteNonQuery("Delete from TrainingScheduleEmployee where TrainingScheduleEmployeeID=%n", SEmpID);
        }

        #endregion

        public static bool IsExists(string TableName, string ColName, string sCode, TransactionContext tc)
        {
            object ob = tc.ExecuteScalar("SELECT COUNT(*) FROM " + TableName + " WHERE " + ColName + "=%s", sCode);
            return (Convert.ToInt32(ob) > 0);
        }

    }
}