CEL_Payroll/Payroll.Service/Assets/DA/AssetInventoryDA.cs

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

using Ease.CoreV35.DataAccess.SQL;

namespace Payroll.Service
{
   public class AssetInventoryDA
    {
        #region  Constructor
        private AssetInventoryDA() { }
        #endregion
        
        #region Get
        internal static IDataReader Get(TransactionContext tc,ID id)
        {
            return tc.ExecuteReader("SELECT * FROM AssetInventory WHERE AssetInventoryID=%n", id.Integer);
        }

        internal static IDataReader Get(TransactionContext tc)
        {
            return tc.ExecuteReader("SELECT * FROM AssetInventory");
        }

        internal static IDataReader GetAllAsset(TransactionContext tc,int assignedUserID, int assignUserID)
        {
            return tc.ExecuteReader("SELECT * FROM AssetInventory WHERE AssignedUserID=%n And AssignUserID=%n", assignedUserID, assignUserID);
        }

        internal static IDataReader GetAllAsset(TransactionContext tc, int assignedUserID)
        {
            return tc.ExecuteReader("SELECT * FROM AssetInventory WHERE AssignedUserID=%n ", assignedUserID);
        }

        internal static IDataReader GetReceivedItems(TransactionContext tc, int receiveEmpoyeeID)
        {
            return tc.ExecuteReader("SELECT * FROM AssetInventory WHERE AssignedUserID=%n And IsEmpRetained=0 And IsEmpConfirmed=1", receiveEmpoyeeID);
        }

        internal static IDataReader GetAssets(TransactionContext tc, int assignedUserID, bool isEmpConfirmed, int assignUserID)
        {
            string sSQL = SQLParser.MakeSQL("SELECT * FROM AssetInventory WHERE AssignedUserID=%n AND IsEmpConfirmed=%b And AssignUserID=%n", assignedUserID, isEmpConfirmed, assignUserID);
            return tc.ExecuteReader(sSQL);
        }

        internal static IDataReader GetReturnedItems(TransactionContext tc, int receiveEmpid)
        {
            return tc.ExecuteReader("SELECT * FROM AssetInventory WHERE AssignedUserID=%n And IsEmpRetained=1", receiveEmpid);
        }

        internal static IDataReader GetEmpAssignedNotReceiveConfirm(TransactionContext tc, int employeeID)
        {
            return tc.ExecuteReader("SELECT * FROM AssetInventory WHERE AssignedUserID=%n And IsEmpConfirmed=0", employeeID);
        }

        #endregion

        #region Insert
        internal static void Insert(TransactionContext tc, AssetInventory oAssetInventory)
        {
            string sSQL = SQLParser.MakeSQL("INSERT INTO AssetInventory(AssetInventoryID, AssetID, AssignedUserID, AssignedDate," +
                              " AssignedRemarks, AssignUserID,IsEmpConfirmed,IsEmpRetained,RetainRemarks,RetainUserID,RetainDate,CreatedBy, CreationDate)" +
                              " VALUES(%n, %n, %n, %d, %s, %n,%b,%b,%s,%n,%d, %n,%d)",
                              oAssetInventory.ID.Integer, oAssetInventory.AssetID.Integer, DataReader.GetNullValue(oAssetInventory.ReceiveEmployeeID,IDType.Integer),
                              oAssetInventory.AssignedDate, oAssetInventory.AssignedRemarks,
                             DataReader.GetNullValue( oAssetInventory.AssignUserID,IDType.Integer),
                              oAssetInventory.IsEmpConfirmed, oAssetInventory.IsEmpRetained, oAssetInventory.RetainRemarks, DataReader.GetNullValue(oAssetInventory.RetainUserID, IDType.Integer), oAssetInventory.RetainDate,
                              DataReader.GetNullValue(oAssetInventory.CreatedBy.Integer),
                              DataReader.GetNullValue(oAssetInventory.CreatedDate));
            tc.ExecuteNonQuery(sSQL);
        }
        #endregion

        #region Update
        internal static void Update(TransactionContext tc, AssetInventory oAssetInventory)
        {
            string sSQL = SQLParser.MakeSQL("UPDATE AssetInventory SET [AssetID]=%n,[AssignedUserID]=%n,[AssignedDate]=%d," +
                 " [AssignedRemarks]=%s,[AssignUserID]=%n,[IsEmpConfirmed]=%b,[EmpConfirmDate]=%d," +
                 " [EmpConfirmRemarks]=%s,[RetainUserID]=%n,[RetainDate]=%d,[RetainRemarks]=%s,[IsEmpRetained]=%b," +
                 " [ModifiedBy]=%n,[ModifiedDate]=%d WHERE [AssetInventoryID]=%n",
                 oAssetInventory.AssetID.Integer, oAssetInventory.ReceiveEmployeeID.Integer,
                 oAssetInventory.AssignedDate,oAssetInventory.AssignedRemarks,
                 oAssetInventory.AssignUserID.Integer,
                 DataReader.GetNullValue(oAssetInventory.IsEmpConfirmed),
                 DataReader.GetNullValue(oAssetInventory.empConfirmDate),
                 DataReader.GetNullValue(oAssetInventory.EmpConfirmRemarks),
                 DataReader.GetNullValue(oAssetInventory.RetainUserID.Integer),
                 DataReader.GetNullValue(oAssetInventory.RetainDate),
                 DataReader.GetNullValue(oAssetInventory.RetainRemarks), DataReader.GetNullValue(oAssetInventory.IsEmpRetained),
                 DataReader.GetNullValue(oAssetInventory.ModifiedBy.Integer),
                 DataReader.GetNullValue(oAssetInventory.ModifiedDate),
                 oAssetInventory.ID.Integer);
            tc.ExecuteNonQuery(sSQL);
        }

        internal static void UpdateEmpConfirm(TransactionContext tc, AssetInventory oAssetInventory)
        {
            string sSQL = SQLParser.MakeSQL(@"UPDATE AssetInventory SET IsEmpConfirmed=%b,
                                            EmpConfirmDate=%d,EmpConfirmRemarks=%s,
                                            [ModifiedBy]=%n,[ModifiedDate]=%d 
                                            where AssetInventoryID=%n",
                oAssetInventory.IsEmpConfirmed,oAssetInventory.empConfirmDate,
                oAssetInventory.EmpConfirmRemarks,
                DataReader.GetNullValue(oAssetInventory.ModifiedBy.Integer),
                DataReader.GetNullValue(oAssetInventory.ModifiedDate),
                oAssetInventory.ID.Integer);
            tc.ExecuteNonQuery(sSQL);
        }

        internal static void UpdateRetainAsset(TransactionContext tc, AssetInventory oAssetInventory)
        {
            string sSQL = SQLParser.MakeSQL(@"UPDATE AssetInventory SET [RetainUserID]=%n,
                 [RetainDate]=%d,[RetainRemarks]=%s,[IsEmpRetained]=%b,[ModifiedBy]=%n,[ModifiedDate]=%d
                  where [AssetInventoryID]=%n",
                oAssetInventory.RetainUserID.Integer,
                oAssetInventory.RetainDate,
                oAssetInventory.RetainRemarks,oAssetInventory.IsEmpRetained,
                DataReader.GetNullValue(oAssetInventory.ModifiedBy.Integer),
                DataReader.GetNullValue(oAssetInventory.ModifiedDate),
                oAssetInventory.ID.Integer);
            tc.ExecuteNonQuery(sSQL);
        }
        #endregion

        #region Delete
        internal static void Delete(TransactionContext tc, int id)
        {
            tc.ExecuteNonQuery("DELETE FROM [AssetInventory] WHERE AssetInventoryID=%n", id);
        }

        internal static void Delete(TransactionContext tc, int AssignedUserID,int AssignUserID)
        {
            tc.ExecuteNonQuery("DELETE FROM [AssetInventory] WHERE  AssignedUserID=%n And AssignUserID=%n ", AssignedUserID, AssignUserID);
        }
        #endregion

    }
}