EchoTex_Payroll/HRM.DA/DA/Fund/Transaction/GLDA.cs

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

namespace HRM.DA
{
    #region GL DA

    public class GLDA
    {
        #region Constructor

        public GLDA()
        {
        }

        #endregion

        #region Insert function

        public static void Insert(TransactionContext tc, GL oItem)
        {
            tc.ExecuteNonQuery(
                "INSERT INTO GL(GLID, GLCode, GLDescription, GLType, GLHeadType, OwnerID, TierNo,PositionNo, LegacyCode, IsActive, CreatedBy, CreateDate, Modifiedby, ModifiedDate)" +
                " VALUES(%n, %s, %s, %n, %n, %n,%n ,%n, %s, %n, %n, %d, %n, %d)", oItem.ID, oItem.GLCode,
                oItem.GLDescription, oItem.GLType, oItem.GLHeadType, oItem.OwnerID, oItem.TierNo, oItem.PositionNo,
                oItem.LegacyCode, oItem.IsActive, oItem.CreatedBy, oItem.CreateDate,
                NullHandler.GetNullValue(oItem.Modifiedby), NullHandler.GetNullValue(oItem.ModifiedDate));
        }

        #endregion

        #region Update function

        public static void Update(TransactionContext tc, GL oItem)
        {
            tc.ExecuteNonQuery(
                "UPDATE GL SET GLCode=%s, GLDescription=%s, GLType=%n, GLHeadType=%n, OwnerID=%n, TierNo=%n,PositionNo=%n ,LegacyCode=%s, IsActive=%n, CreatedBy=%n, CreateDate=%d, Modifiedby=%n, ModifiedDate=%d" +
                " WHERE GLID=%n", oItem.GLCode, oItem.GLDescription, oItem.GLType, oItem.GLHeadType, oItem.OwnerID,
                oItem.TierNo, oItem.PositionNo, NullHandler.GetNullValue(oItem.LegacyCode), oItem.IsActive,
                oItem.CreatedBy, oItem.CreateDate, NullHandler.GetNullValue(oItem.Modifiedby),
                NullHandler.GetNullValue(oItem.ModifiedDate), oItem.ID);
        }

        public static void UpdateHeaType(TransactionContext tc, GL gl)
        {
            tc.ExecuteNonQuery("UPDATE GL SET GLHeadType=%n WHERE GLID=%n", gl.GLHeadType, gl.ID);
        }

        public static void UpdatePositionNo(TransactionContext tc, int glID, int positionNo)
        {
            tc.ExecuteNonQuery("UPDATE GL SET PositionNo=%n WHERE GLID=%n", positionNo, glID);
        }

        #endregion

        #region ID Generation function

        public static int GetNewID(TransactionContext tc)
        {
            return tc.GenerateID("GL", "GLID");
        }

        public static int GenerateMaxPositionNo(TransactionContext tc, int nOwnerID)
        {
            object nMaxID = tc.ExecuteScalar("SELECT MAX(PositionNo) FROM GL Where OwnerID=%n ", nOwnerID);
            if (nMaxID == DBNull.Value)
            {
                nMaxID = 1;
            }
            else
            {
                nMaxID = Convert.ToInt32(nMaxID) + 1;
                if ((int)nMaxID <= 0)
                {
                    nMaxID = 1;
                }
            }

            return (int)nMaxID;
        }

        #endregion

        #region Get Function

        public static bool IsTranExist(TransactionContext tc, string bottomMostChildKeys)
        {
            object count = tc.ExecuteScalar("SELECT COUNT(*) FROM GLTranDetail WHERE GLID in (%q)",
                bottomMostChildKeys);
            if (count == null || count == DBNull.Value || Convert.ToInt32(count) <= 0)
            {
                object countOpen = tc.ExecuteScalar("SELECT COUNT(*) FROM OpenGLTranDetail WHERE GLID in (%q)",
                    bottomMostChildKeys);
                if (countOpen == null || countOpen == DBNull.Value || Convert.ToInt32(countOpen) <= 0)
                {
                    return false;
                }
                else
                {
                    return true;
                }
            }
            else
            {
                return true;
            }
        }

        public static IDataReader Get(TransactionContext tc)
        {
            return tc.ExecuteReader("SELECT * FROM GL ORDER BY dbo.GL.GLCode, dbo.GL.PositionNo");
        }

        public static IDataReader Get(TransactionContext tc, int nGLID)
        {
            return tc.ExecuteReader("SELECT * FROM GL WHERE GLID=%n", nGLID);
        }

        public static IDataReader Get(TransactionContext tc, string code, int tierNo)
        {
            return tc.ExecuteReader("SELECT * FROM GL WHERE GLCode=%s AND TierNo=%n", code, tierNo);
        }

        public static IDataReader Get(TransactionContext tc, string code)
        {
            return tc.ExecuteReader("SELECT * FROM GL WHERE GLCode=%s", code);
        }

        public static IDataReader GetRoot(TransactionContext tc)
        {
            return tc.ExecuteReader("SELECT * FROM GL WHERE OwnerID IS NULL ");
        }

        public static IDataReader GetByOwnerID(TransactionContext tc, int nOwnerID)
        {
            return tc.ExecuteReader("SELECT * FROM GL WHERE OwnerID=%n Order By PositionNo", nOwnerID);
        }

        public static IDataReader GetOwner(TransactionContext tc, int nOwnerID)
        {
            return tc.ExecuteReader("SELECT * FROM GL WHERE OwnerID=%n Order By PositionNo", nOwnerID);
        }

        public static IDataReader GetByTierNo(TransactionContext tc, int tierNo)
        {
            return tc.ExecuteReader("SELECT * FROM GL WHERE TierNo=%n ORDER BY GLCode,PositionNo", tierNo);
        }

        //public static DataSet GetByTierNoDS(TransactionContext tc, int tierNo)
        //{
        //    return tc.ExecuteDataSet("SELECT dbo.GL.GLCode AS [" + SystemConfig.GetGLLevelName(tierNo) + " Code], dbo.GL.GLDescription AS [Description], OwnerGL.GLCode AS [Parent Code] FROM dbo.GL INNER JOIN"
        //             + " dbo.GL OwnerGL ON dbo.GL.OwnerID = OwnerGL.GLID WHERE dbo.GL.TierNo=%n ORDER BY dbo.GL.GLCode, dbo.GL.PositionNo", tierNo);
        //}
        //public static DataSet GetByTierNoDSControl(TransactionContext tc, int tierNo)
        //{
        //    return tc.ExecuteDataSet("SELECT dbo.GL.GLCode AS [" + SystemConfig.GetGLLevelName(tierNo) + " Code], dbo.GL.GLDescription AS [Description] FROM dbo.GL WHERE dbo.GL.TierNo=%n ORDER BY dbo.GL.GLCode, dbo.GL.PositionNo", tierNo);
        //}
        public static DataSet GetByTierNoDS(TransactionContext tc)
        {
            return tc.ExecuteDataSet(
                "SELECT dbo.UserRecord.Field1 AS [Subsidiary Code], dbo.UserRecord.Field2 AS [Subsidiary Name], dbo.GL.GLCode AS [Parent Code] " +
                "FROM dbo.GL RIGHT OUTER JOIN " +
                "dbo.GLUserObject ON dbo.GL.GLID = dbo.GLUserObject.GLID LEFT OUTER JOIN " +
                "dbo.UserRecord LEFT OUTER JOIN " +
                "dbo.UserObject ON dbo.UserRecord.UserObjectID = dbo.UserObject.UserObjectID ON dbo.GLUserObject.UserObjectID = dbo.UserObject.UserObjectID");
        }

        public static IDataReader Get(TransactionContext tc, EnumGLType glType, int tierNo)
        {
            return tc.ExecuteReader("SELECT * FROM GL WHERE GLType=%n AND TierNo=%n ORDER BY GLCode,PositionNo", glType,
                tierNo);
        }

        public static IDataReader Get(TransactionContext tc, EnumGLHeadType gLHeadType, int tierNo)
        {
            return tc.ExecuteReader("SELECT * FROM GL WHERE GLHeadType=%n AND TierNo=%n ORDER BY GLCode,PositionNo",
                gLHeadType, tierNo);
        }

        public static IDataReader GetByGLHeadType(TransactionContext tc, EnumGLHeadType gLHeadType)
        {
            return tc.ExecuteReader("SELECT * FROM GL WHERE GLHeadType=%n ORDER BY GLCode,GLDescription", gLHeadType);
        }

        public static IDataReader GetBySearch(TransactionContext tc, string sSearch)
        {
            return tc.ExecuteReader("SELECT * FROM GL %q ORDER BY GLCode,PositionNo", sSearch);
        }

        public static IDataReader GetByTierNoOrdrByDesc(TransactionContext tc, int tierNo)
        {
            return tc.ExecuteReader("SELECT * FROM GL WHERE TierNo=%n ORDER BY GLCode,dbo.GL.GLDescription", tierNo);
        }

        public static IDataReader GetOrdrByDesc(TransactionContext tc)
        {
            return tc.ExecuteReader("SELECT * FROM GL ORDER BY dbo.GL.GLDescription");
        }

        public static IDataReader GetOrdrByDesc(TransactionContext tc, EnumGLType glType, int tierNo)
        {
            return tc.ExecuteReader("SELECT * FROM GL WHERE GLType=%n AND TierNo=%n ORDER BY dbo.GL.GLDescription",
                glType, tierNo);
        }

        public static IDataReader GetOrdrByDesc(TransactionContext tc, EnumGLHeadType gLHeadType, int tierNo)
        {
            return tc.ExecuteReader("SELECT * FROM GL WHERE GLHeadType=%n AND TierNo=%n ORDER BY dbo.GL.GLDescription",
                gLHeadType, tierNo);
        }

        public static IDataReader GetBySearchOrdrByDesc(TransactionContext tc, string sSearch)
        {
            return tc.ExecuteReader("SELECT * FROM GL %q ORDER BY dbo.GL.GLDescription", sSearch);
        }

        public static IDataReader GetPostingLevelGL(TransactionContext tc, int p)
        {
            throw new Exception("The method or operation is not implemented.");
        }

        #endregion

        #region Delete function

        public static void Delete(TransactionContext tc, int nGLID)
        {
            tc.ExecuteNonQuery("DELETE FROM GL WHERE GLID=%n", nGLID);
        }

        #endregion
    }

    #endregion
}