using HRM.BO;
using Ease.Core.DataAccess;
using System;
using System.Data;
using HRM.BO.Fund;
using Microsoft.Data.SqlClient;

namespace HRM.DA
{
    public class GlobalFunctionDA
    {
        internal static DateTime ServerDate(TransactionContext tc)
        {
            object serverDate = null;
            serverDate = tc.ExecuteScalar("select getdate() as serverdate");
            return (serverDate == DBNull.Value ? DateTime.Now : Convert.ToDateTime(serverDate));
        }

        internal static DateTime GetOperationDate(TransactionContext tc)
        {
            DateTime opDate = DateTime.MinValue;
            SqlParameter[] p = new SqlParameter[1];
            p[0] = new SqlParameter("@OperationDate", SqlDbType.DateTime);
            p[0].Direction = ParameterDirection.Output;
            p[0].Value = DateTime.MinValue;

            tc.ExecuteNonQuery(CommandType.StoredProcedure, "[dbo].[GetOperationDate]", p);

            if (p[0].Value != null && p[0].Value != DBNull.Value)
                opDate = Convert.ToDateTime(p[0].Value);

            return opDate;
        }

        internal static IDataReader AvailableUserObject(TransactionContext tc)
        {
            return tc.ExecuteReader("select * from vwUserObject order by UserObjectID");
        }

        internal static object CurrentDate(TransactionContext tc)
        {
            object currentDate = null;
            string sSQL =
                SQLParser.MakeSQL(
                    "select LastMonthEndDate from ProjectDetails where ProjectID=%n"); //, FM.BO.Common.User.CurrentUser.ProjectID.Integer
            currentDate = tc.ExecuteScalar(sSQL);
            return (currentDate == DBNull.Value ? DateTime.Now : Convert.ToDateTime(currentDate));
        }

        internal static object GetYearEnd(TransactionContext tc)
        {
            object currentDate = null;
            string sSQL =
                SQLParser.MakeSQL(
                    "select LastYearEndDate from ProjectDetails where ProjectID=%n"); //, FM.BO.Common.User.CurrentUser.ProjectID.Integer
            currentDate = tc.ExecuteScalar(sSQL);
            return (currentDate == DBNull.Value ? DateTime.Now : Convert.ToDateTime(currentDate));
        }

        internal static string GetMaxCode(TransactionContext tc, string sTableName, string ColumnName)
        {
            object sCode = tc.ExecuteScalar("SELECT MAX(%q) FROM  %q", ColumnName, sTableName);
            if (sCode == DBNull.Value)
                return "";
            return sCode.ToString();
        }

        internal static string GetMaxCode(TransactionContext tc, string sTableName, string ColumnName, int tier)
        {
            object sCode = tc.ExecuteScalar("SELECT MAX(%q) FROM %q where tire=%n ", ColumnName, sTableName, tier);
            if (sCode == DBNull.Value)
                return "";
            return sCode.ToString();
        }

        internal static string GetMaxCode(TransactionContext tc, string sTableName, string ColumnName,
            string IDColumnName, string parentIDColumnName, string parentCode, int tier)
        {
            string sql = string.Empty;
            if (string.IsNullOrWhiteSpace(parentCode))
            {
                sql = string.Format(@"SELECT MAX({0}) FROM {1} where tire={2} ", ColumnName, sTableName, tier);
            }
            else
            {
                sql = string.Format(@"SELECT MAX({0}) FROM {1} where {2} in(Select {3} FROM {1} where {0}='{4}')",
                    ColumnName, sTableName, parentIDColumnName, IDColumnName, parentCode);
            }

            object sCode = tc.ExecuteScalar(sql);
            if (sCode == DBNull.Value)
                return "";
            return sCode.ToString();
        }
    }
}