using HRM.BO; using Ease.Core.DataAccess; using System; using System.Collections.Generic; using System.Data; using NPOI.SS.Formula.Functions; using static System.Runtime.InteropServices.JavaScript.JSType; namespace HRM.DA { class TrainingScheduleDA { //DataReader.GetNullValue(item.DepartmentID, 0) #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, int TSEmpId) { return tc.ExecuteReader("SELECT * from TrainingScheduleEmployee where TrainingScheduleEmployeeID=%n", TSEmpId); } public static IDataReader GetTrainingScheduleDates(TransactionContext tc) { return tc.ExecuteReader("SELECT * from TrainingScheduleDate"); } public static IDataReader GetTrainingScheduleEmployees(TransactionContext tc, int trainingScheduleId) { return tc.ExecuteReader("SELECT * from TrainingScheduleEmployee where TrainingScheduleID=%n", trainingScheduleId); } public static IDataReader GetTrainingScheduleDates(TransactionContext tc, int trainingScheduleID) { return tc.ExecuteReader("SELECT * from TrainingScheduleDate where TrainingScheduleID=%n", trainingScheduleID); } public static IDataReader GetTrainingSchedule(TransactionContext tc, int trainingId, int trainingTypeId, DateTime? fromDate, DateTime? toDate) { string sqlClause = string.Empty; if (trainingId > 0) { sqlClause = SQLParser.TagSQL(sqlClause) + SQLParser.MakeSQL("ts.TRAININGID = %n", trainingId); } if (trainingTypeId > 0) { sqlClause = SQLParser.TagSQL(sqlClause) + SQLParser.MakeSQL("tr.TRAININGTYPEID = %n", trainingTypeId); } if (fromDate != null && toDate != null) { // sqlClause = SQLParser.TagSQL(sqlClause) + SQLParser.MakeSQL("StartDate BETWEEN %d AND %d", fromDate, toDate); sqlClause = SQLParser.TagSQL(sqlClause) + SQLParser.MakeSQL("CAST(STARTDATE as Date)>= cast(%d as Date) AND CAST(STARTDATE as Date)<= cast(%d as Date) AND CAST(ENDDATE as Date)>= cast( %d as Date) AND CAST(ENDDATE as Date)<= cast(%d as Date)", fromDate, toDate,fromDate,toDate); } return tc.ExecuteReader(@"SELECT ts.*,tr.NAME TrainingName,tt.Name TrainingTypeName from TrainingSchedule ts Inner join TRAINING tr on ts.TRAININGID=tr.TRAININGID Inner join TRAININGTYPE tt on tt.TRAININGTYPEID=tr.TRAININGTYPEID %q", sqlClause); } #endregion #endregion #region Insert Function public static void Insert(TransactionContext tc, TrainingSchedule oItem) { string sql = SQLParser.MakeSQL( "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, ManHour, ManDays, DocumentName)" + " VALUES(%n,%n,%n,%n,%s,%d,%d,%b,%n,%d,%d,%n,%n,%n,%s,%n,%n,%n,%n,%n,%s,%n,%n,%n,%s)", oItem.ID, DataReader.GetNullValue(oItem.TrainingID, 0), DataReader.GetNullValue(oItem.TrainingNatureID, 0), DataReader.GetNullValue(oItem.InstituteID, 0), oItem.TrainerDescription, oItem.EnrolledStartDate, oItem.EnrolledEndDate, oItem.IsShowInWeb, DataReader.GetNullValue(oItem.TNAnalysisID, 0), 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, oItem.ManHour, oItem.ManDays, oItem.DocumentName); tc.ExecuteNonQuery(sql); } public static void InsertTrainingScheduleAttn(TransactionContext tc, TrainingScheduleAttn oItem) { tc.ExecuteNonQuery("INSERT INTO TrainingScheduleAttn(TSAttnID, TrainingScheduleID,TSDateID, EmployeeID)" + " VALUES(%n,%n,%n,%n)", oItem.ID, oItem.TrainingScheduleID, oItem.TSDateID, oItem.EmployeeID); } #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, oItem.TrainingScheduleID, oItem.ScheduleEmpStatus, oItem.EmployeeID, oItem.ProposedByID, 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, DataReader.GetNullValue(oItem.TrainingScheduleID, 0), 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, ManHour=%n, ManDays=%n, DocumentName=%s" + " WHERE TrainingScheduleID=%n", DataReader.GetNullValue(oItem.TrainingID, 0), DataReader.GetNullValue(oItem.TrainingNatureID, 0), DataReader.GetNullValue(oItem.InstituteID, 0), oItem.TrainerDescription, oItem.EnrolledStartDate, oItem.EnrolledEndDate, oItem.IsShowInWeb, DataReader.GetNullValue(oItem.TNAnalysisID, 0), oItem.StartDate, oItem.EndDate, DataReader.GetNullValue(oItem.Cost), DataReader.GetNullValue(oItem.MaxParticipants), oItem.EnrolledParticipants, DataReader.GetNullValue(oItem.CourseOutline), oItem.ScheduleStatus, oItem.PayrollTypeID, oItem.VenueCost, oItem.RefreshmentCost, oItem.TrainerFees, oItem.OthesCost, oItem.Venue, oItem.ManHour, oItem.ManDays, oItem.DocumentName, oItem.ID); } 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, oItem.ScheduleEmpStatus, oItem.EmployeeID, oItem.ProposedByID, oItem.ProposedDate, oItem.Remarks, oItem.ID); } 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); } } }