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

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

namespace Payroll.Service
{
    class TrainingSchEmpCostDA
    {
        #region Get Function

        public static IDataReader Get(TransactionContext tc, int id)
        {
            return tc.ExecuteReader("SELECT * from TrainingSchEmpCost WHERE TrainingSchEmpCostID=%n", id);
        }

        public static IDataReader Get(TransactionContext tc)
        {
            return tc.ExecuteReader("SELECT * from TrainingSchEmpCost");
        }

        internal static IDataReader GetByTrainingScheduleEmpID(TransactionContext tc, ID empID, ID trainSchEmpID)
        {

            return tc.ExecuteReader("SELECT * from TrainingSchEmpCost where EmployeeID=%n and TrainSchEmpID=%n", empID.Integer, trainSchEmpID.Integer);
        }

        #endregion

        #region Insert Function
        public static void Insert(TransactionContext tc, TrainingSchEmpCost oItem)
        {
            tc.ExecuteNonQuery("INSERT INTO TrainingSchEmpCost(TrainingSchEmpCostID, CostHdID, Amount,TrunDate,Remarks,EmployeeID,TrainSchEmpID,CREATEDBY,CREATEDDATE)" +
          " VALUES(%n,%n,%n,%d,%s,%n,%n,%n,%d)", oItem.ID.Integer, oItem.CostHdID, oItem.Amount, oItem.TrunDate, oItem.Remarks, oItem.EmployeeID, oItem.TrainSchEmpID, DataReader.GetNullValue(oItem.CreatedBy.Integer), DataReader.GetNullValue(oItem.CreatedDate));

        }
        #endregion

        #region Update Function

        public static void Update(TransactionContext tc, TrainingSchEmpCost oItem)
        {
            tc.ExecuteNonQuery("UPDATE TrainingSchEmpCost SET CostHdID=%n,Amount=%n,TrunDate=%d,Remarks=%s,EmployeeID=%n,TrainSchEmpID=%n,MODIFIEDBY=%n,MODIFIEDDATE=%d" +
        " WHERE TrainingSchEmpCostID=%n", oItem.CostHdID, oItem.Amount, oItem.TrunDate, oItem.Remarks, oItem.EmployeeID, oItem.TrainSchEmpID, DataReader.GetNullValue(oItem.ModifiedBy.Integer), DataReader.GetNullValue(oItem.ModifiedDate), oItem.ID.Integer);

        }

        #endregion

        #region Delete Function
        public static void Delete(TransactionContext tc, int id)
        {
            tc.ExecuteNonQuery("Delete from TrainingSchEmpCost where TrainingSchEmpCostID=%n", id);
        }
        #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);
        }


        internal static void DeleteCommonUpdateUncommon(TransactionContext tc)
        {
            tc.ExecuteNonQuery(@"DELETE from TrainingSchEmpCost WHERE CostHdID IN (1,2,3,4) AND TrainSchEmpID 
                            NOT IN (SELECT tse.TrainingScheduleEmployeeID FROM TrainingScheduleEmployee tse)");

            tc.ExecuteNonQuery(@"Update TrainingSchEmpCost SET TrainSchEmpID=1034 WHERE TrainSchEmpID=1024
                                Update TrainingSchEmpCost SET TrainSchEmpID=1035 WHERE TrainSchEmpID=1025
                                Update TrainingSchEmpCost SET TrainSchEmpID=1673 WHERE TrainSchEmpID=1643
                                Update TrainingSchEmpCost SET TrainSchEmpID=1674 WHERE TrainSchEmpID=1644
                                Update TrainingSchEmpCost SET TrainSchEmpID=1675 WHERE TrainSchEmpID=1645
                                Update TrainingSchEmpCost SET TrainSchEmpID=1676 WHERE TrainSchEmpID=1646
                                Update TrainingSchEmpCost SET TrainSchEmpID=1677 WHERE TrainSchEmpID=1647
                                Update TrainingSchEmpCost SET TrainSchEmpID=1878 WHERE TrainSchEmpID=1849
                                Update TrainingSchEmpCost SET TrainSchEmpID=297 WHERE TrainSchEmpID=293
                                Update TrainingSchEmpCost SET TrainSchEmpID=298 WHERE TrainSchEmpID=294
                                Update TrainingSchEmpCost SET TrainSchEmpID=306 WHERE TrainSchEmpID=302
                                Update TrainingSchEmpCost SET TrainSchEmpID=307 WHERE TrainSchEmpID=303
                                Update TrainingSchEmpCost SET TrainSchEmpID=352 WHERE TrainSchEmpID=346
                                Update TrainingSchEmpCost SET TrainSchEmpID=353 WHERE TrainSchEmpID=347
                                Update TrainingSchEmpCost SET TrainSchEmpID=429 WHERE TrainSchEmpID=409
                                Update TrainingSchEmpCost SET TrainSchEmpID=79 WHERE TrainSchEmpID=43
                                Update TrainingSchEmpCost SET TrainSchEmpID=80 WHERE TrainSchEmpID=44
                                Update TrainingSchEmpCost SET TrainSchEmpID=81 WHERE TrainSchEmpID=45
                                Update TrainingSchEmpCost SET TrainSchEmpID=485 WHERE TrainSchEmpID=480
                                Update TrainingSchEmpCost SET TrainSchEmpID=486 WHERE TrainSchEmpID=481
                                Update TrainingSchEmpCost SET TrainSchEmpID=487 WHERE TrainSchEmpID=482
                                Update TrainingSchEmpCost SET TrainSchEmpID=524 WHERE TrainSchEmpID=521
                                Update TrainingSchEmpCost SET TrainSchEmpID=525 WHERE TrainSchEmpID=522
                                Update TrainingSchEmpCost SET TrainSchEmpID=589 WHERE TrainSchEmpID=561
                                Update TrainingSchEmpCost SET TrainSchEmpID=590 WHERE TrainSchEmpID=562
                                Update TrainingSchEmpCost SET TrainSchEmpID=591 WHERE TrainSchEmpID=563
                                Update TrainingSchEmpCost SET TrainSchEmpID=249 WHERE TrainSchEmpID=58
                                Update TrainingSchEmpCost SET TrainSchEmpID=250 WHERE TrainSchEmpID=59
                                Update TrainingSchEmpCost SET TrainSchEmpID=251 WHERE TrainSchEmpID=60
                                Update TrainingSchEmpCost SET TrainSchEmpID=252 WHERE TrainSchEmpID=61
                                Update TrainingSchEmpCost SET TrainSchEmpID=753 WHERE TrainSchEmpID=749
                                Update TrainingSchEmpCost SET TrainSchEmpID=767 WHERE TrainSchEmpID=759
                                Update TrainingSchEmpCost SET TrainSchEmpID=768 WHERE TrainSchEmpID=760");

            tc.ExecuteNonQuery(@"ALTER TABLE TrainingSchEmpCost
                                ADD CONSTRAINT FK_TrainingSchEmpCost_TrainingSchEmp
                                FOREIGN KEY (TrainSchEmpID)
                                REFERENCES TrainingScheduleEmployee(TrainingScheduleEmployeeID)");
        }

        internal static IDataReader GetCostWithoutParent(TransactionContext tc)
        {
            return tc.ExecuteReader(@"SELECT * from TrainingSchEmpCost WHERE  TrainSchEmpID 
                                    NOT IN (SELECT tse.TrainingScheduleEmployeeID FROM TrainingScheduleEmployee tse)");
        }
    }
}