EchoTex_Payroll/HRM.DA/DA/Assets/AssetItDA.cs

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


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

        private AssetItDA()
        {
        }

        #endregion

        #region Get
        internal static IDataReader Get(TransactionContext tc, int id)
        {
            return tc.ExecuteReader("SELECT * FROM AssetIt WHERE AssetItID=%n", id);
        }

        internal static IDataReader GetSerial(TransactionContext tc, int id)
        {
            return tc.ExecuteReader("SELECT * FROM AssetIt WHERE AssetItID=%n", id);
        }
        internal static IDataReader Get(TransactionContext tc)
        {
            return tc.ExecuteReader("SELECT asr.*, ast.* FROM AssetIt ast inner join AssetSerial asr on ast.AssetSerialID = asr.AssetSerialId order by ast.AssetItId asc");
        }

        internal static IDataReader GetAssetIt(TransactionContext tc, int assetserialid, int assetitid, string ipaddress, string macaddress, string serialno, string specification)
        {
            string sql = string.Empty;

            if (assetserialid > 0)
            {
                sql = SQLParser.TagSQL(sql) + SQLParser.MakeSQL("asr.assetserialid = %n", assetserialid);
            }

            if (assetitid > 0)
            {
                sql = SQLParser.TagSQL(sql) + SQLParser.MakeSQL("ast.assetitid = %n", assetitid);
            }

            if (ipaddress != null)
            {
                sql = SQLParser.TagSQL(sql) + SQLParser.MakeSQL("ast.ipaddress = %n", ipaddress);
            }

            if (macaddress != null)
            {
                sql = SQLParser.TagSQL(sql) + SQLParser.MakeSQL("ast.macaddress = %n", macaddress);
            }

            if (serialno != null)
            {
                sql = SQLParser.TagSQL(sql) + SQLParser.MakeSQL("asr.serialno = %n", serialno);
            }

            if (specification != null)
            {
                sql = SQLParser.TagSQL(sql) + SQLParser.MakeSQL("asr.specification = %n", specification);
            }

            string sqlQuery = SQLParser.MakeSQL(@"select asr.*, ast.* from AssetSerial asr
                                                INNER JOIN AssetIt ast ON ast.AssetSerialID = asr.AssetSerialID %q", sql);
            return tc.ExecuteReader(sqlQuery);
        }

        internal static IDataReader GetAssetItSerialPicker(TransactionContext tc, int assetid, int categoryid, int assetitid, int assetserialid)
        {
            DataSet dSet = new DataSet();
            string sql = string.Empty;

            if (assetid > 0)
            {
                sql = SQLParser.TagSQL(sql) + SQLParser.MakeSQL("a.assetid = %n", assetid);
            }
            if (categoryid > 0)
            {
                sql = SQLParser.TagSQL(sql) + SQLParser.MakeSQL("a.AssetCategoryId = %n", categoryid);
            }
            if (assetserialid > 0)
            {
                sql = SQLParser.TagSQL(sql) + SQLParser.MakeSQL("asr.AssetSerialID = %n", assetserialid);
            }
            if (assetitid > 0)
            {
                sql = SQLParser.TagSQL(sql) + SQLParser.MakeSQL("ast.AssetItID = %n", assetitid);
            }
            
            string sqlQuery = SQLParser.MakeSQL(@"select ast.* from AssetSerial asr
                                                 inner join asset a on a.AssetID=asr.AssetID     
                                                 inner join AssetIt ast on ast.AssetSerialID= asr.AssetSerialID %q", sql);
            Console.WriteLine(sqlQuery);
            return tc.ExecuteReader(sqlQuery);
        }

        #endregion Get

        #region Insert
        internal static void Insert(TransactionContext tc, AssetIt oAsset)
        {
            if (oAsset.SerialNo != null || oAsset.SerialNo != "")
            {
                string sql = SQLParser.MakeSQL("INSERT INTO AssetIt(AssetItID, AssetSerialID, IpAddress, MacAddress, SerialNo, Specification)" +
                                           "VALUES(%n, %n, %s, %s, %s, %s)",
                oAsset.ID, oAsset.AssetSerialID, oAsset.IpAddress, oAsset.MacAddress, oAsset.SerialNo, oAsset.Specification);
                tc.ExecuteNonQuery(sql);
            }
            else
                return;
        }
        #endregion Insert


        #region Update

        internal static void Update(TransactionContext tc, AssetIt oAsset)
        {
            string sSQL = SQLParser.MakeSQL("UPDATE AssetIt SET IpAddress=%s, MacAddress=%s, SerialNo=%s where AssetItID=%n",oAsset.IpAddress, oAsset.MacAddress, oAsset.SerialNo, oAsset.ID);
            tc.ExecuteNonQuery(sSQL);
        }

        #endregion Update


        #region Delete

        internal static void Delete(TransactionContext tc, int id)
        {
            tc.ExecuteNonQuery("DELETE FROM AssetIt WHERE AssetItID=%n", id);
        }

        #endregion
    }
}