#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;
|
}
|
}
|
}
|
