EchoTex_Payroll/HRM.DA/DA/Bonus/PRBCalculationDA.cs

using System;
using Payroll.BO;
using System.Data;
using System.Linq;
using System.Data.SqlClient;
using Ease.CoreV35.DataAccess;
using System.Collections.Generic;
using Ease.Core.DataAccess;
using HRM.BO;
using static iTextSharp.text.pdf.AcroFields;
using HRM.DA;
using NPOI.SS.Formula.Functions;

namespace Payroll.Service
{
    #region  PRBCalculationDA

    internal class PRBCalculationDA
    {
        #region  Constructor

        private PRBCalculationDA() { }

        #endregion

        #region  Insert function

        internal static void Insert(TransactionContext tc, PRBCalculation item)
        {
            tc.ExecuteNonQuery("INSERT INTO PRBCalculation(PRBCalculationID,EmployeeID, NoOFDays, BonusAmount, TargetT1,AcheiveT1,AmountT1, TargetT2,AcheiveT2,AmountT2,TargetT3,AcheiveT3,AmountT3,TargetT4,AcheiveT4,AmountT4," +
                                     " TargetT5,AcheiveT5,AmountT5,TargetT6,AcheiveT6,AmountT6,TargetT7,AcheiveT7,AmountT7,TotalAcheive,TotalPRB,TAX,NetAmount,CreatedBy, CreatedDate,AnnualBaseSalary,PRBMonth,TargetT8,AcheiveT8,AmountT8,TargetT9,AcheiveT9,AmountT9,TargetT10,AcheiveT10,AmountT10,PrbYear,BatchId)" +
                                     " VALUES(%n,%n,%n,%n,%n,%n,%n, %n, %n, %n, %n, %n, %n, %n,%n,%n,%n,%n,%n,%n,%n,%n,%n,%n,%n,%n,%n,%n,%n,%n,%d,%n,%d,%n,%n,%n,%n,%n,%n,%n,%n,%n,%d,%n)",
            item.ID,item.EmployeeID, item.NoOFDays, item.BonusAmount, item.TargetT1, item.AcheiveT1, item.AmountT1, item.TargetT2, item.AcheiveT2, item.AmountT2, item.TargetT3, item.AcheiveT3, item.AmountT3, 
            item.TargetT4,item.AcheiveT4,item.AmountT4, item.TargetT5, item.AcheiveT5, item.AmountT5, item.TargetT6, item.AcheiveT6, item.AmountT6, item.TargetT7, item.AcheiveT7, item.AmountT7,item.TotalAcheive, item.TotalPRB, item.Tax, item.NetAmount, item.CreatedBy, item.CreatedDate,item.AnnualBasicSalary,item.EndDate,
            item.TargetT8, item.AcheiveT8, item.AmountT8,  item.TargetT9, item.AcheiveT9, item.AmountT9, item.TargetT10, item.AcheiveT10, item.AmountT10, item.Year,item.BatchID);
        }

        #endregion

        #region  Update function

        internal static void Update(TransactionContext tc, PRBCalculation item)
        {
            tc.ExecuteNonQuery("UPDATE PRBCalculation SET NoOFDays=%n, BonusAmount=%n,TargetT1=%n,AcheiveT1=%n, AmountT1=%n, TargetT2=%n, AcheiveT2=%n, AmountT2=%n, TargetT3 = %n, AcheiveT3 = %n, AmountT3 = %n, TargetT4=%n," +
                 " AcheiveT4=%n,AmountT4=%n,TargetT5=%n,AcheiveT5=%n,AmountT5=%n,TargetT6=%n,AcheiveT6=%n,AmountT6=%n,TargetT7=%n,AcheiveT7=%n,AmountT7=%n,TotalAcheive=%n,TotalPRB=%n,TAX=%n,NetAmount=%n,ModifiedDate=%d,ModifiedBy=%n,AnnualBaseSalary=%n,PRBMonth=%d,TargetT8=%n,AcheiveT8=%n,AmountT8=%n,TargetT9=%n,AcheiveT9=%n,AmountT9=%n,TargetT10=%n,AcheiveT10=%n,AmountT10=%n,PrbYear=%d " +
                " WHERE EmployeeID=%n", item.NoOFDays, item.BonusAmount, item.TargetT1, item.AcheiveT1, item.AmountT1, item.TargetT2, item.AcheiveT2, item.AmountT2, item.TargetT3, item.AcheiveT3, item.AmountT3,
               item.TargetT4, item.AcheiveT4, item.AmountT4, item.TargetT5, item.AcheiveT5, item.AmountT5, item.AmountT6, item.TargetT6, item.AcheiveT6, item.TargetT7, item.AcheiveT7, item.AmountT7, item.TotalAcheive, item.TotalPRB, item.Tax, item.NetAmount, item.ModifiedDate, item.ModifiedBy,item.AnnualBasicSalary,item.EndDate,
               item.TargetT8, item.AcheiveT8, item.AmountT8, item.TargetT9, item.AcheiveT9, item.AmountT9, item.TargetT10, item.AcheiveT10, item.AmountT10, item.Year,
               item.EmployeeID);
        }

        #endregion

        #region Get Function

        internal static IDataReader GetByEmpIDs(TransactionContext tc, string sEmpIDs, DateTime prbYear, DateTime prbMonth)
        {
            string sqlClause = string.Empty;
           
            if (!string.IsNullOrWhiteSpace(sEmpIDs))
            {
                sqlClause = SQLParser.TagSQL(sqlClause) + SQLParser.MakeSQL("EMPLOYEEID IN (%q)", sEmpIDs);
            }
            sqlClause = SQLParser.TagSQL(sqlClause) + SQLParser.MakeSQL("MONTH(PRBMonth)=MONTH(%d)", prbMonth);
            sqlClause = SQLParser.TagSQL(sqlClause) + SQLParser.MakeSQL("YEAR(prbYear)=YEAR(%d)", prbYear);

            string sql = SQLParser.MakeSQL(@"SELECT * FROM PRBCalculation %q", sqlClause);
            return tc.ExecuteReader(sql);
        }

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



        internal static IDataReader GetByBatchId(TransactionContext tc, int nID)
        {
            return tc.ExecuteReader("SELECT * FROM PRBCalculation WHERE batchID=%n", nID);
        }
        internal static IDataReader GetByEmployeeID(TransactionContext tc, int nID)
        {
            return tc.ExecuteReader("SELECT * FROM PRBCalculation WHERE EmployeeID=%n", nID);
        }

        internal static IDataReader GetByEmployeeIDDecData(TransactionContext tc, int nID, DateTime prbYear)
        {
            return tc.ExecuteReader("SELECT * FROM PRBCalculation WHERE EmployeeID=%n and MONTH(PRBMonth)=MONTH(%d) and YEAR(prbYear)=YEAR(%d)", nID, prbYear, prbYear);
        }


        internal static DataTable getPrbAmountOfCurrentMonth(TransactionContext tc, int nID)
        {
            DataTable oSMonthlys = new DataTable();
            try
            {

                string sql = SQLParser.MakeSQL(@"SELECT PrbMonth,COUNT(employeeID) Totalemployee,SUM(TotalPRB) TotalAmount  FROM PRBCalculation WHERE batchID=%n GROUP BY PRBMonth ",
                                                nID);
                oSMonthlys = tc.ExecuteDataTable(sql);

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return oSMonthlys;
        }



        internal static void UpdatePrbStatus(TransactionContext tc, int pkid, EnumPRBStatus status)
        {
            string ssql = "";

            ssql = SQLParser.MakeSQL(
              "UPDATE PRBCalculation SET PRBStatus=%n WHERE BatchID=%n",
              (int)status,
              pkid);

            tc.ExecuteNonQuery(ssql);
        }

        internal static void ApproveOrRejectPrbStatus(TransactionContext tc, int batchId, EnumPRBStatus status,int appovedId)
        {
            string ssql = "";

            ssql = SQLParser.MakeSQL(
              "UPDATE PRBCalculation SET PRBStatus=%n,StatusUpdatedBy=%n,StatusUpdateDate=%d WHERE BatchId=%n",
              (int)status, appovedId,DateTime.Now,
              batchId);

            tc.ExecuteNonQuery(ssql);
        }


        #endregion

        #region  Delete function

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

        internal static void Delete(TransactionContext tc, int nID, DateTime prbMonth)
        {
            tc.ExecuteNonQuery("DELETE FROM PRBCalculation WHERE EmployeeID=%n and MONTH(PRBMonth)=MONTH(%d)", nID, prbMonth);
        }

        internal static void DeleteExistingData(TransactionContext tc, string empids, DateTime prbMonth,DateTime prbYear)
        {
            tc.ExecuteNonQuery("DELETE FROM PRBCalculation WHERE EmployeeID in (%q) and MONTH(PRBMonth)=MONTH(%d)", empids, prbMonth, prbYear);
        }


        #endregion


    }

    #endregion
}