CEL_Payroll/Payroll.Service/SAPLookUp/DA/SAPDataProcessDA.cs

using System;
using Payroll.BO;
using System.Data;
using System.Linq;
using Ease.CoreV35.Model;
using System.Data.SqlClient;
using Ease.CoreV35.DataAccess;
using System.Collections.Generic;
using Ease.CoreV35.DataAccess.SQL;

namespace Payroll.Service
{
    #region  SAPDataProcessDA

    internal class SAPDataProcessDA
    {
        #region  Constructor

        private SAPDataProcessDA() { }

        #endregion

        #region  Insert function

        internal static void Insert(TransactionContext tc, SAPDataProcess item)
        {
            string sql = SQLParser.MakeSQL(@"INSERT INTO SAPDataProcess(SAPDataProcessID, ProcessDate, ProcessStatus, ProcessTime, ProcessBy, MailCompleted,CutOffday,SourceFileName)
            VALUES(%n, %D, %n, %s, %n, %n,%n,%s)", item.ID.Integer, item.ProcessDate, item.ProcessStatus, item.ProcessTime, item.ProcessBy, item.MailCompleted, item.CutOffday, item.SourceFileName);
            tc.ExecuteNonQuery(sql);
        }

        internal static void Insert(TransactionContext tc, SAPDataProcessDetail item)
        {
            string sql = SQLParser.MakeSQL(@"INSERT INTO SAPDataProcessDetail(SAPDataProcessDetailID, EmployeeNo, Name, DataType, Description,SAPDataProcessID)
            VALUES(%n, %s, %s, %s, %s,%n)", item.ID.Integer, item.EmployeeNo, item.Name, item.DataType, item.Description, item.SAPDataProcessID.Integer);
            
            tc.ExecuteNonQuery(sql);
        }

        #endregion

        #region  Update function

        internal static void Update(TransactionContext tc, SAPDataProcess item)
        {
            tc.ExecuteNonQuery("UPDATE SAPDataProcess SET ProcessDate=%s, ProcessStatus=%s, ProcessTime=%n, ProcessBy=%d, MailCompleted=%n,CutOffday=%n" +
            " WHERE SAPDataProcessID=%n", item.ProcessDate, item.ProcessStatus, item.ProcessTime, item.ProcessBy, item.MailCompleted,item.CutOffday,item.ID.Integer);
        }

        #endregion

        #region Get Function

        internal static IDataReader Get(TransactionContext tc)
        {
            return tc.ExecuteReader("SELECT * FROM SAPDataProcess order by ProcessDate DESC,SourceFileName Desc");
        }

        internal static IDataReader Get(TransactionContext tc, ID nID)
        {
            return tc.ExecuteReader("SELECT * FROM SAPDataProcess WHERE SAPDataProcessID=%n", nID.Integer);
        }

        internal static IDataReader Get(TransactionContext tc, DateTime dSAPDate)
        {
            return tc.ExecuteReader("SELECT * FROM SAPDataProcess WHERE ProcessDate=%d", dSAPDate);
        }

        internal static IDataReader GetSAPDetails(TransactionContext tc, ID nSAPID)
        {
            return tc.ExecuteReader("SELECT * FROM SAPDataProcessDetail WHERE SAPDataProcessID=%n", nSAPID.Integer);
        }

        internal static DataSet GetSapDataProcessDetail(TransactionContext tc, ID ID)
        {
            DataSet oGetSapDataProcessDetail = new DataSet();
            oGetSapDataProcessDetail = tc.ExecuteDataSet("Select description, EmployeeNo, Name  from SAPDataProcessDetail where SAPDataProcessID=%n", ID.Integer);
            return oGetSapDataProcessDetail;
        }

        internal static DataSet GetSapProcessDifferentErrors(TransactionContext tc)
        {

            return tc.ExecuteDataSet(@"Select * 
                                        from
                                        (Select emp.EMPLOYEENO,emp.NAME,'Bank Information Not Found' as Problem
                                         from (Select * from Employee
                                               where STATUS = 1 
	                                           Except
	                                           Select * from Employee 
	                                           Where EmployeeID in (Select Distinct(EMPLOYEEID) from BANKACCOUNTHISTORY)
	                                           And STATUS = 1 ) emp
                                         UNION
                                         Select emp.EMPLOYEENO,emp.NAME,'Cost Center Not Found' as Problem
                                         from  (Select * from Employee
	                                            where STATUS = 1 
		                                        Except
		                                        Select * from Employee 
		                                        Where EmployeeID in (Select Distinct(EMPLOYEEID) from EMPCOSTCENTER)
		                                        And STATUS = 1) emp
                                         Union
                                         Select EMPLOYEENO,NAME,'Grade Set to N/A' as Problem 
                                         from Employee e
                                         Where GRADEID IN (Select GRADEID from GRADES Where Code = 'N/A')
                                         And [STATUS] = 1 
                                         Union
                                         Select EMPLOYEENO,NAME,'Basic Salary Set to 1.00' as Problem 
                                         from Employee 
                                         Where BASICSALARY = 1
                                         And STATUS = 1 
                                         Union
                                         Select EMPLOYEENO,NAME,'Location Set to Location_Error' as Problem 
                                         from Employee 
                                         Where LOCATIONID in (Select LOCATIONID from LOCATION Where DESCRIPTION = 'Location_Error')
                                         And STATUS = 1 
                                         Union
                                         Select EMPLOYEENO,NAME,'Vendor Code Not Found' as Problem
                                         from Employee 
                                         where VendorCode is NULL 
                                         And STATUS = 1
                                         Union
                                         Select EMPLOYEENO,NAME,'GDDBID Not Found' as Problem
                                         from Employee 
                                         where GlobalID = '' And STATUS = 1 
                                         Union
                                         Select EMPLOYEENO,NAME,'Designation SET TO EXTERNAL' as Problem 
                                         from Employee 
                                         WHERE DESIGNATIONID in (SELECT DesignationID FROM DESIGNATION WHERE NAME = 'EXTERNAL')
                                         And STATUS = 1
                                         
                                         ) empProblems
                                        Order by empProblems.EMPLOYEENO,empProblems.Problem");
            
        }

        #endregion

        #region  Delete function

        internal static void Delete(TransactionContext tc, ID nID)
        {

            tc.ExecuteNonQuery("DELETE FROM [SAPDataProcess] WHERE SAPDataProcessID=%n", nID.Integer);
        }

        internal static void DeleteSAPDetail(TransactionContext tc, ID nID)
        {
            tc.ExecuteNonQuery("DELETE FROM [SAPDataProcessDetail] WHERE SAPDataProcessID=%n", nID.Integer);
        }

        #endregion

        #region Scalar Functions

        internal static bool GetFileReadStatus(TransactionContext tc, string filename)
        {
            string sql = @"SELECT * FROM SAPDataProcess sp
WHERE sp.SourceFileName LIKE '%"+filename+@"%'
AND sp.ProcessStatus = "+ ((int)EnumSAPProcessStatus.Success);
            int count = Convert.ToInt32(tc.ExecuteScalar(sql));
            if (count > 0)
                return true;
            else
                return false;
        }

        #endregion

        
    }

    #endregion
}