EchoTex_Payroll/HRM.DA/DA/Assets/AssetCategoryDA.cs

using HRM.BO;
using Ease.Core.DataAccess;
using System;
using System.Data;

namespace HRM.DA
{
    internal class AssetCategoryDA
    {
        #region Constructor

        private AssetCategoryDA()
        {
        }

        #endregion

        #region Insert function

        internal static void Insert(TransactionContext tc, AssetCategory item)
        {
            tc.ExecuteNonQuery(
                "INSERT INTO ASSETCATEGORY(ASSETCATEGORYID, code, description, parentID, CreatedBy, CreationDate, Status,TIRE, payrolltypeid)" +
                " VALUES(%n, %s, %s, %n,%n, %d, %n,%n,%n)", item.ID, item.Code, item.Name, item.ParentID,
                item.CreatedBy, item.CreatedDate,item.Status, item.Tier, item.PayrollTypeID);
        }

        #endregion

        #region Update function

        internal static void Update(TransactionContext tc, AssetCategory item)
        {
            tc.ExecuteNonQuery(
                "UPDATE AssetCategory SET code=%s, description=%s, parentID=%n,ModifiedBy=%n, ModifiedDate=%d, Status=%n" +
                " WHERE ASSETCATEGORYID=%n", item.Code, item.Name, item.ParentID, item.ModifiedBy, item.ModifiedDate,
                item.Status, item.ID);
        }

        #endregion

        #region Get Function

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

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

        internal static IDataReader Get(TransactionContext tc, string sCode)
        {
            return tc.ExecuteReader("SELECT * FROM AssetCategory WHERE Code=%s", sCode);
        }

        internal static IDataReader GetChilds(TransactionContext tc, int ID)
        {
            return tc.ExecuteReader("SELECT * FROM AssetCategory WHERE ParentID=%n Order By DESCRIPTION", ID);
        }

        internal static IDataReader Get(TransactionContext tc, EnumStatus status, int payrollTypeID)
        {
            if (EnumStatus.Active == status || EnumStatus.Inactive == status)
            {
                string sql =
                    SQLParser.MakeSQL(
                        "SELECT * FROM AssetCategory Where Status=%n and payrollTypeid=%n  Order By DESCRIPTION", status,
                        payrollTypeID);
                return tc.ExecuteReader(sql);
            }
            else
            {
                return tc.ExecuteReader("SELECT * FROM AssetCategory Where  payrollTypeid=%n Order By DESCRIPTION",
                    payrollTypeID);
            }
        }


        internal static IDataReader GetParent(TransactionContext tc, EnumStatus status, int payrollTypeID)
        {
            if (EnumStatus.Active == status || EnumStatus.Inactive == status)
            {
                string sql =
                    SQLParser.MakeSQL(
                        "SELECT * FROM AssetCategory parentid is null Where and Status=%n  Order By DESCRIPTION", status,
                        payrollTypeID);
                return tc.ExecuteReader(sql);
            }
            else
            {
                return tc.ExecuteReader("SELECT * FROM AssetCategory Where parentid is null Order By DESCRIPTION",
                    payrollTypeID);
            }
        }

        internal static IDataReader GetByTier(TransactionContext tc, int tier)
        {
            return tc.ExecuteReader("SELECT * FROM AssetCategory Where Tire=%n Order by Description", tier);
        }

        #endregion

        #region Delete function

        internal static void Delete(TransactionContext tc, int ID, int UserID)
        {
            tc.ExecuteNonQuery("UPDATE AssetCategory SET CreatedBy=%n,ModifiedBy=%n  Where LocationID=%n", UserID, UserID,
                ID);
            tc.ExecuteNonQuery("DELETE FROM AssetCategory WHERE LocationID=%n", ID);
        }

        internal static void Delete(TransactionContext tc, int ID)
        {
            tc.ExecuteNonQuery("DELETE FROM AssetCategory WHERE ASSETCATEGORYID=%n", ID);
        }

        #endregion

        internal static IDataReader Get(TransactionContext tc, EnumStatus sts)
        {
            string sql = SQLParser.MakeSQL("Select * From AssetCategory where Status = %n", sts);
            return tc.ExecuteReader(sql);
        }

     

        internal static IDataReader GetAllAssetCategories(TransactionContext tc, object payrollTypeID, EnumStatus status,
            string code, string name)
        {
            string sqlClause = string.Empty;
            sqlClause = SQLParser.TagSQL(sqlClause) + SQLParser.MakeSQL("PayrollTypeID = %n", payrollTypeID);
            if (EnumStatus.Active == status || EnumStatus.Inactive == status)
            {
                sqlClause = SQLParser.TagSQL(sqlClause) + SQLParser.MakeSQL("status = %n", status);
            }

            if (!string.IsNullOrWhiteSpace(code))
            {
                sqlClause = SQLParser.TagSQL(sqlClause) + SQLParser.MakeSQL("Code = %s", code);
            }

            if (!string.IsNullOrWhiteSpace(name))
            {
                sqlClause = SQLParser.TagSQL(sqlClause) + SQLParser.MakeSQL("Name LIKE %s", ("%" + name + "%"));
            }

            return tc.ExecuteReader("SELECT * FROM AssetCategory %q", sqlClause);
        }
        internal static IDataReader GetAllAssetCategory(TransactionContext tc, string code, string name)
        {
            string sqlClause = string.Empty;
            if (!string.IsNullOrWhiteSpace(code))
            {
                sqlClause = SQLParser.TagSQL(sqlClause) + SQLParser.MakeSQL("Code = %s", code);
            }
            if (!string.IsNullOrWhiteSpace(name))
            {
                sqlClause = SQLParser.TagSQL(sqlClause) + SQLParser.MakeSQL("Name LIKE %s", ("%" + name + "%"));
            }

            return tc.ExecuteReader("SELECT * FROM AssetCategory %q ", sqlClause);
        }
       
    }
}