#region

using System;
using System.Collections.Generic;
using System.Data;
using CSFrameworkV5.Common;
using CSFrameworkV5.Core;
using CSFrameworkV5.Interfaces;
using CSFrameworkV5.Interfaces.InterfaceModels;
using CSFrameworkV5.Models;

#endregion

namespace CSFrameworkV5.DataAccess
{
    public class dalCommon : dalBase, IBridge_CommonData
    {
        /// <summary>
        ///     构造器
        /// </summary>
        /// <param name="user">当前用户登录信息</param>
        public dalCommon(Loginer user) : base(user)
        {
        }

        public bool ApprovalBusiness(QueryApproval P)
        {
            if (string.IsNullOrWhiteSpace(P.DBID))
                throw new Exception("ApprovalBusiness方法缺少DBID的值。");

            try
            {
                var db = DatabaseProvider.GetDatabase(P.DBID);
                var sp = db.CreateSqlProc("usp_ApprovalBusiness");
                sp.AddParam("TableName", DbType.String, 50, P.TableName);
                sp.AddParam("KeyFieldName", DbType.String, 50, P.KeyFieldName);
                sp.AddParam("KeyValue", DbType.String, 50, P.KeyValue);
                sp.AddParam("FlagApp", DbType.String, 250, P.FlagApp);
                sp.AddParam("AppUser", DbType.String, 250, _Loginer.Account);

                db.ExecuteCommand(sp.Command);

                //没报异常，审核成功！
                return true;
            }
            catch
            {
                return false;
            }
        }

        /// <summary>
        ///     获取业务单据名称定义
        /// </summary>
        /// <returns></returns>
        public DataTable GetBusinessTables()
        {
            var SQL =
                "SELECT * FROM sys_BusinessTables ORDER BY ModuleID,SortID;";
            return DatabaseProvider.SystemDatabase.GetTable(SQL,
                "sys_BusinessTables");
        }

        /// <summary>
        ///     获取自动序号，如：XX0000001
        /// </summary>
        /// <param name="dataCode">XX</param>
        /// <param name="asHeader">作为前缀返回</param>
        /// <returns></returns>
        public string GetDataSN(string dataCode, bool asHeader)
        {
            var no = DocNoTool.GetDataSN(_Database, dataCode, asHeader);
            return no;
        }

        public List<DbTableModel> GetDbTableFields(string DBName,
            string TableName)
        {
            var db = DatabaseProvider.SystemDatabase;
            var sql =
                $"SELECT DISTINCT DBName,TableName,FieldName,DisplayName FROM sys_FieldNameDefs WHERE DBName={db.ParamSymboName}DBName AND TableName={db.ParamSymboName}TableName";
            var sp = db.CreateCommand(sql);
            sp.AddParam("DBName", DBName);
            sp.AddParam("TableName", TableName);
            return db.ExecuteReaderList<DbTableModel>(sp.Command);
        }

        public List<DbTableModel> GetDbTables()
        {
            var sql = "SELECT DISTINCT DBName,TableName FROM sys_FieldNameDefs";
            var sp = DatabaseProvider.SystemDatabase.CreateCommand(sql);
            return DatabaseProvider.SystemDatabase
                .ExecuteReaderList<DbTableModel>(sp.Command);
        }

        /// <summary>
        ///     获取单据号码（单据流水号码）
        /// </summary>
        /// <param name="DocNoName"></param>
        /// <returns></returns>
        public string GetDocNo(string DocNoName)
        {
            var no = DocNoTool.GetDocNo(_Database, DocNoName);
            return no;
        }

        /// <summary>
        ///     获取空表
        /// </summary>
        /// <param name="DBID">账套编号</param>
        /// <param name="tableName">表名</param>
        /// <returns></returns>
        public DataTable GetEmptyTable(string DBID, string tableName)
        {
            if (string.IsNullOrEmpty(tableName)) throw new Exception("表名不能为空！");

            var sql = $"SELECT * FROM {tableName} WHERE 1=0;";
            return DatabaseProvider.GetDatabase(DBID).GetTable(sql, tableName);
        }

        /// <summary>
        ///     获取模块名称定义
        /// </summary>
        /// <returns></returns>
        public DataTable GetModules()
        {
            return _Database.GetTable("SELECT * FROM sys_ModuleFileList;",
                "sys_ModuleFileList");
        }

        public DateTime GetServerTime()
        {
            return _Database.GetServerTime();
        }

        /// <summary>
        ///     获取系统帐套清单
        /// </summary>
        /// <returns></returns>
        public DataTable GetSystemDataSet()
        {
            //系统数据库
            var db = DatabaseProvider.SystemDatabase;
            var sql =
                $"SELECT * FROM tb_DataSet WHERE IsUse={db.ParamSymboName}IsUse;";
            var cmd = db.CreateCommand(sql);
            cmd.AddParam("IsUse", "Y");
            return db.GetTable(cmd.Command, "tb_DataSet");
        }

        public DataTable GetSystemDataSet(string user, string password)
        {
            var sp =
                DatabaseProvider.SystemDatabase.CreateSqlProc(
                    "usp_GetDataSetByUser");
            sp.AddParam("User", DbType.String, user);
            sp.AddParam("Password", DbType.String, password);
            //取系统数据库里的资料表
            return DatabaseProvider.SystemDatabase.GetTable(sp.Command,
                "tb_DataSet");
        }

        public DataTable GetSystemSettings4Program(string DataSetID,
            string UserID)
        {
            var sp =
                DatabaseProvider.SystemDatabase.CreateSqlProc(
                    "usp_sys_GetSystemSettings4Program");
            sp.AddParam("DataSetID", DbType.String, 50, DataSetID);
            sp.AddParam("UserID", DbType.String, 50, UserID);
            return DatabaseProvider.SystemDatabase.GetTable(sp.Command,
                sys_SystemSettingsByUser.__TableName);
        }

        public DataTable GetSystemSettingsByUser(string DataSetID,
            string UserID)
        {
            var sp =
                DatabaseProvider.SystemDatabase.CreateSqlProc(
                    "usp_sys_GetSystemSettingsByUser");
            sp.AddParam("DataSetID", DbType.String, 50, DataSetID);
            sp.AddParam("UserID", DbType.String, 50, UserID);
            return DatabaseProvider.SystemDatabase.GetTable(sp.Command,
                sys_SystemSettingsByUser.__TableName);
        }

        public DataTable GetSystemSettingsEx(string ParamName, string ParamType)
        {
            var sp =
                DatabaseProvider.SystemDatabase.CreateSqlProc(
                    "usp_sys_GetSystemSettings");
            sp.AddParam("Content", DbType.String, 50, ParamName);
            sp.AddParam("ParamType", DbType.String, 50, ParamType);
            return DatabaseProvider.SystemDatabase.GetTable(sp.Command,
                sys_SystemSettings.__TableName);
        }

        /// <summary>
        ///     获取物理表的字段
        /// </summary>
        /// <param name="tableName">物理表名</param>
        /// <returns></returns>
        public DataTable GetTableFields(string tableName)
        {
            var sp = _Database.CreateSqlProc("sp_sys_GetTableFieldType");
            sp.AddParam("TableName", DbType.String, 50, tableName);
            return _Database.GetTable(sp.Command, "tb_TableFieldTypeData");
        }

        /// <summary>
        ///     获取表的所有字段
        /// </summary>
        /// <param name="tableName">表名</param>
        public DataTable GetTableFieldsDef(string tableName,
            bool onlyDisplayField)
        {
            var sp =
                DatabaseProvider.SystemDatabase.CreateSqlProc(
                    "sp_sys_GetTableFieldsForPicker");
            sp.AddParam("TableName", tableName);
            sp.AddParam("ReturnAll", onlyDisplayField ? "N" : "Y");
            return DatabaseProvider.SystemDatabase.GetTable(sp.Command,
                "Fields");
        }

        public byte[] GetUpgrader(string upgraderName, string serverVer)
        {
            //获取当前DLL版本字节
            return new dalFileUpload().GetUpgrader(upgraderName, serverVer);
        }

        public string GetUpgraderVersion(string upgraderName)
        {
            //获取最新版本号
            return new dalFileUpload().GetUpgraderVersion(upgraderName);
        }


        /// <summary>
        ///     获取版本更新表数据
        /// </summary>
        /// <returns></returns>
        public DataTable GetUpgraderVersionData()
        {
            return DatabaseProvider.SystemDatabase.GetTable(
                "SELECT distinct upgraderName  FROM sys_UpgraderVersion;",
                "sys_UpgraderVersion");
        }

        /// <summary>
        ///     获取当前模块报表样式
        /// </summary>
        /// <returns></returns>
        public DataTable GetReports(string ReportsName)
        {
            return DatabaseProvider.SystemDatabase.GetTable(
                string.Format(
                    "SELECT *  FROM SYS_Reports WHERE ReportTitle='{0}';",
                    ReportsName), "SYS_Reports");
        }


        public void PostUserSettings(Loginer currentUser, string paramKey,
            string paramValue)
        {
            var sp =
                DatabaseProvider.SystemDatabase.CreateSqlProc(
                    "usp_sys_PostSystemSettings4User");
            sp.AddParam("DatasetID", DbType.String, 50, currentUser.DBID);
            sp.AddParam("UserID", DbType.String, 50, currentUser.Account);
            sp.AddParam("ParamCode", DbType.String, 50, paramKey);
            sp.AddParam("ParamValue", DbType.String, 250, paramValue);
            DatabaseProvider.SystemDatabase.ExecuteCommand(sp.Command);
        }

        public DataTable SearchOutstanding(string invoiceNo, string customer,
            DateTime dateFrom, DateTime dateTo, DateTime dateEnd,
            string outstandingType)
        {
            //outstandingType:AR/AP/ALL

            if (outstandingType == "AR")
            {
                var sp = _Database.CreateSqlProc("sp_QueryOutstandingAR");
                sp.AddParam("InvoiceNo", DbType.String, 20, invoiceNo);
                sp.AddParam("CustomerCode", DbType.String, 20, customer);
                sp.AddParam("FromDate", DbType.String, 8,
                    ConvertEx.ToCharYYYYMMDD(dateFrom));
                sp.AddParam("ToDate", DbType.String, 8,
                    ConvertEx.ToCharYYYYMMDD(dateTo));
                sp.AddParam("EndDate", DbType.String, 8,
                    ConvertEx.ToCharYYYYMMDD(dateEnd));
                return _Database.GetTable(sp.Command,
                    "Outstanding" + outstandingType);
            }

            if (outstandingType == "AP")
            {
                var sp = _Database.CreateSqlProc("sp_QueryOutstandingAP");
                sp.AddParam("InvoiceNo", DbType.String, 20, invoiceNo);
                sp.AddParam("SupplierCode", DbType.String, 20, customer);
                sp.AddParam("FromDate", DbType.String, 8,
                    ConvertEx.ToCharYYYYMMDD(dateFrom));
                sp.AddParam("ToDate", DbType.String, 8,
                    ConvertEx.ToCharYYYYMMDD(dateTo));
                sp.AddParam("EndDate", DbType.String, 8,
                    ConvertEx.ToCharYYYYMMDD(dateEnd));
                return _Database.GetTable(sp.Command,
                    "Outstanding" + outstandingType);
            }

            return null;
        }

        public DataSet SearchOutstandingByPage(string invoiceNo,
            string customer,
            DateTime dateFrom, DateTime dateTo, DateTime dateEnd,
            string outstandingType, int pageNo, int pageSize)
        {
            //outstandingType:AR/AP/ALL

            if (outstandingType == "AR")
            {
                var sp =
                    _Database.CreateSqlProc("sp_QueryOutstandingAR_ByPage");
                sp.AddParam("InvoiceNo", DbType.String, 20, invoiceNo);
                sp.AddParam("CustomerCode", DbType.String, 20, customer);
                sp.AddParam("FromDate", DbType.String, 8,
                    ConvertEx.ToCharYYYYMMDD(dateFrom));
                sp.AddParam("ToDate", DbType.String, 8,
                    ConvertEx.ToCharYYYYMMDD(dateTo));
                sp.AddParam("EndDate", DbType.String, 8,
                    ConvertEx.ToCharYYYYMMDD(dateEnd));
                sp.AddParam("PageNo", DbType.Int32, 8, pageNo);
                sp.AddParam("PageSize", DbType.Int32, 8, pageSize);
                var ds = _Database.GetDataSet(sp.Command);
                ds.Tables[0].TableName = "Outstanding" + outstandingType;
                ds.Tables[1].TableName = "TOTAL_PAGES";
                return ds;
            }

            if (outstandingType == "AP")
            {
                var sp =
                    _Database.CreateSqlProc("sp_QueryOutstandingAP_ByPage");
                sp.AddParam("InvoiceNo", DbType.String, 20, invoiceNo);
                sp.AddParam("SupplierCode", DbType.String, 20, customer);
                sp.AddParam("FromDate", DbType.String, 8,
                    ConvertEx.ToCharYYYYMMDD(dateFrom));
                sp.AddParam("ToDate", DbType.String, 8,
                    ConvertEx.ToCharYYYYMMDD(dateTo));
                sp.AddParam("EndDate", DbType.String, 8,
                    ConvertEx.ToCharYYYYMMDD(dateEnd));
                sp.AddParam("PageNo", DbType.Int32, 8, pageNo);
                sp.AddParam("PageSize", DbType.Int32, 8, pageSize);
                var ds = _Database.GetDataSet(sp.Command);
                ds.Tables[0].TableName = "Outstanding" + outstandingType;
                ds.Tables[1].TableName = "TOTAL_PAGES";
                return ds;
            }

            return null;
        }

        public bool UploadUpgrader(string upgraderName, byte[] body,
            string version, DateTime uploadTime, string user)
        {
            new dalFileUpload().UploadUpgrader(upgraderName, body, version,
                uploadTime, user);
            return true;
        }

        public DataTable GetSysBusinessTables()
        {
            var sql = "SELECT * FROM sys_BusinessTables;";

            //取系统数据库里的资料表
            return DatabaseProvider.SystemDatabase.GetTable(sql,
                "sys_BusinessTables");
        }

        /// <summary>
        ///     获取系统帐套清单
        /// </summary>
        /// <param name="systemDB">系统数据库名</param>
        /// <returns></returns>
        public DataTable GetSystemDataSet(string user, string password,
            string systemDB)
        {
            var sp =
                DatabaseProvider.SystemDatabase.CreateSqlProc(
                    "usp_GetDataSetByUser");
            sp.AddParam("User", DbType.String, user);
            sp.AddParam("Password", DbType.String, password);
            return DatabaseProvider.SystemDatabase.GetTable(sp.Command,
                "tb_DataSet");
        }

        public int GetUpgraderPackages(string currentVersionID)
        {
            return new dalFileUpload().GetUpgraderPackages(currentVersionID);
        }

        public bool TestConnection()
        {
            var sql = "SELECT COUNT(*) FROM tb_DataSet;";

            //取系统数据库的帐套数据表
            var o = DatabaseProvider.SystemDatabase.ExecuteScalar(sql);
            return true;
        }
    }
}