EchoTex_Payroll/HRM.DA/DA/SearchTools/ReportColumnDefinitionDA.cs

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

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

        private ReportColumnDefinitionDA()
        {
        }

        #endregion

        #region Action Query

        internal static void Insert(TransactionContext tc, ReportColumnDefinition rc)
        {
            string sSQL = SQLParser.MakeSQL("INSERT INTO ReportColumnDefinition(ColumnID, TableName, ColumnName,"
                                            + " DataType, SUBGroupID, Caption, Width, Alignment, ValueFormat, UsedAsSearch,"
                                            + " IsThisPicker, PickerType, PickerTablesName, PickerColumnsName, ValueMemberColumnName, UserData, ColumnCode, AssemblyName, UDControlName, IsUDControl, GROUPID)"
                                            + " VALUES(%n, %s, %s, %n, %n, %s, %n, %n, %s, %b, %b, %n, %s, %s, %s, %s, %s, %s, %s, %b, %n)",
                rc.ID, rc.TableName, rc.ColumnName, rc.DataType, rc.ObjectSUBGroupID,
                rc.Caption, rc.Width, rc.Alignment, rc.ValueFormat, rc.UsedAsSearch,
                rc.IsThisPicker, NullHandler.GetNullValue((int)rc.PickerType),
                NullHandler.GetNullValue(rc.PickerTablesName), NullHandler.GetNullValue(rc.PickerColumnsName),
                NullHandler.GetNullValue(rc.ValueMemberColumnName), NullHandler.GetNullValue(rc.UserData),
                NullHandler.GetNullValue(rc.ColumnCode), NullHandler.GetNullValue(rc.AssemblyName),
                NullHandler.GetNullValue(rc.UDControlName), rc.IsUDControl, rc.ObjectGroupID);
            tc.ExecuteNonQuery(sSQL);
        }

        internal static void Update(TransactionContext tc, ReportColumnDefinition rc)
        {
            tc.ExecuteNonQuery(@"UPDATE ReportColumnDefinition SET TableName=%s, ColumnName=%s, DataType=%n,
                               SUBGroupID=%n, Caption=%s, Width=%n, Alignment=%n, ValueFormat=%s, UsedAsSearch=%b,
                               IsThisPicker=%b, PickerType=%n, PickerTablesName=%s, PickerColumnsName=%s,
                               ValueMemberColumnName=%s, UserData=%s, ColumnCode=%s,AssemblyName=%s,UDControlName=%s,IsUDControl=%b,GROUPID=%n WHERE ColumnID=%n",
                rc.TableName, rc.ColumnName,
                rc.DataType, rc.ObjectSUBGroupID, rc.Caption, rc.Width, rc.Alignment, rc.ValueFormat,
                rc.UsedAsSearch, rc.IsThisPicker,
                (int)rc.PickerType, NullHandler.GetNullValue(rc.PickerTablesName),
                NullHandler.GetNullValue(rc.PickerColumnsName), NullHandler.GetNullValue(rc.ValueMemberColumnName),
                NullHandler.GetNullValue(rc.UserData), NullHandler.GetNullValue(rc.ColumnCode),
                NullHandler.GetNullValue(rc.AssemblyName), NullHandler.GetNullValue(rc.UDControlName), rc.IsUDControl,
                rc.ObjectGroupID, rc.ID);
        }

        internal static void Delete(TransactionContext tc, int columnID)
        {
            tc.ExecuteNonQuery("DELETE FROM ReportColumnDefinition WHERE ColumnID=%n", columnID);
        }

        #endregion

        #region Picker Dependancy

        internal static void InsertChild(TransactionContext tc, PickerDependancy pd)
        {
            tc.ExecuteNonQuery(
                "INSERT INTO PickerDependancy(PickerDependancyID, MasterPickerID, MasterPickerColumn, ChildPickerID,"
                + " ChildPickerColumn)"
                + " VALUES(%n, %n, %s, %n, %s)",
                pd.ID, pd.MasterPickerID, pd.MasterPickerColumn, pd.ParentID, pd.ChildPickerColumn);
        }

        internal static void DeleteByReportColumnID(TransactionContext tc, int columnID)
        {
            tc.ExecuteNonQuery("DELETE FROM PickerDependancy WHERE ChildPickerID=%n", columnID);
        }

        internal static int GenChildID(TransactionContext tc)
        {
            return tc.GenerateID("PickerDependancy", "PickerDependancyID");
        }

        internal static IDataReader GetChild(TransactionContext tc, int columnID)
        {
            return tc.ExecuteReader("SELECT * FROM PickerDependancy WHERE ChildPickerID=%n", columnID);
        }

        #endregion

        #region Select Query

        internal static int GenID(TransactionContext tc)
        {
            return tc.GenerateID("ReportColumnDefinition", "ColumnID");
        }

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

        internal static IDataReader Get(TransactionContext tc)
        {
            string sSQL = SQLParser.MakeSQL("SELECT * FROM ReportColumnDefinition e ORDER BY e.Caption");
            return tc.ExecuteReader(sSQL);
        }

        internal static IDataReader GetBySubGroup(TransactionContext tc, int subGroupID)
        {
            return tc.ExecuteReader("SELECT * FROM ReportColumnDefinition WHERE groupID =%n ORDER BY Caption",
                subGroupID);
        }

        internal static IDataReader GetByReport(TransactionContext tc, int reportID)
        {
            return tc.ExecuteReader(
                "SELECT A.*, B.Aggregate, B.ColumnOrderBy, B.ColumnVisible FROM ReportColumnDefinition A,"
                + " (SELECT ColumnID, Aggregate, ColumnPosition, ColumnOrderBy, ColumnVisible"
                + " FROM ReportColumn WHERE ReportID=%n AND ColumnID IS NOT NULL"
                + " UNION"
                + " SELECT ColumnID, Aggregate, 0, 0, 1"
                + " FROM ReportJoinedColumn WHERE ReportID=%n) B"
                + " WHERE A.ColumnID = B.ColumnID ORDER BY B.ColumnPosition", reportID, reportID);
        }

        internal static IDataReader SearchRCD(TransactionContext tc, string searchString)
        {
            return tc.ExecuteReader("SELECT * FROM ReportColumnDefinition %q", searchString);
        }

        #endregion
    }
}