/// <summary>
/// 运行自定义函数
/// 引用下列的命名空间
/// 1. using System.Data.SqlClient;
/// 2. using Ass;
/// </summary>
/// <param name="funcName">函数名</param>
/// <param name="sqlParms">参数</param>
/// <returns></returns>
public static object MySqlFunction(this CHIS.DbContext.CHISEntitiesSqlServer db, string funcName,
params System.Data.SqlClient.SqlParameter[] sqlParms)
{
string connectionString = new Code.Utility.DataBaseHelper().ConnectionString;
using (var conn = new System.Data.SqlClient.SqlConnection(connectionString))
{
string strSql = funcName; //自定SQL函数
var cmd = new System.Data.SqlClient.SqlCommand(strSql, conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
if (sqlParms != null)
{
foreach (var p in sqlParms)
{
cmd.Parameters.Add(p);
}
}
System.Data.SqlClient.SqlParameter rtnp = new System.Data.SqlClient.SqlParameter();
rtnp.ParameterName = "@Return";
rtnp.Direction = System.Data.ParameterDirection.ReturnValue;
cmd.Parameters.Add(rtnp);
conn.Open();
object o = cmd.ExecuteScalar();
return(rtnp.Value);
}
}
public static async Task _Initial_FuncAccess()
{
var db = new Code.Utility.DataBaseHelper().GetMainDbContext();
_db_FuncAccess = null;
_db_FuncAccess = await db.CHIS_Sys_FuncAccess.AsNoTracking().ToListAsync();
}
public static async Task _Initial_ChinaArea()
{
var db = new Code.Utility.DataBaseHelper().GetMainDbContext();
_db_ChinaArea = null;
_db_ChinaArea = await db.SYS_ChinaArea.AsNoTracking().ToListAsync();
}
public static async Task _Initial_Dict()
{
var db = new Code.Utility.DataBaseHelper().GetMainDbContext();
_db_dictDetail = null;
_db_dictDetail = await db.vwCHIS_Code_DictDetail.AsNoTracking().ToListAsync();
}
public void OnAuthorization(AuthorizationFilterContext context)
{
bool bPass = false;
var model = Global.db_FuncAccess.FirstOrDefault(m => m.AKeyId == FuncAccessKey);
if (model != null)
{
var doctorId = int.Parse(context.HttpContext.User.FindFirst("DoctorId").Value);
var stationId = int.Parse(context.HttpContext.User.FindFirst("StationId").Value);
string sql = string.Format("exec sp_Sys_GetFuncDetailAccess '{0}','{1}',{2},{3}", model.ActionKey, model.FuncName, doctorId, stationId);
var db = new Code.Utility.DataBaseHelper().GetMainDbContext();
CHIS.Models.DataModel.typevalue rtn = db.SqlQuery <CHIS.Models.DataModel.typevalue>(sql).First();
if (model.ResultType == "bool")
{
bPass = new Ass.ObjReturn(rtn.value, rtn.type).ToBool();
//通过则不予理睬
}
}
//未通过返回页面
if (!bPass)
{
context.Result = new RedirectResult("/Home/Unaccess"); //转到未被授权的页面
}
}
/// <summary>
/// 获取工作站的当日接诊量
/// </summary>
public static Models.StatisticsModels.TreatBasicSummary StationTodayTreatSummary(this CHIS.Models.vwCHIS_Code_WorkStation model, int doctorId)
{
var db = new Code.Utility.DataBaseHelper().GetMainDbContext();
string sql = string.Format(@"
select tt.name,sum(tt.waiting) waiting,sum(tt.treating) treating,sum(tt.treated) treated from (
select 'ThisToday' name,0 waiting,0 treating,0 treated
union
select a as name,waiting,treating,treated from (
select 'ThisToday' a,TreatStatus b ,count(1) n from vwCHIS_Register
where StationId={0} and EmployeeID={1} and RegisterDate >= '{2}' and RegisterDate< '{3}'
group by TreatStatus) t pivot(max(n) for b in(waiting,treating,treated)) m
) tt group by tt.name
", model.StationID, doctorId, DateTime.Today.ToDateString(), DateTime.Today.AddDays(1).ToDateString());
var rlt = db.SqlQuery <Models.StatisticsModels.TreatBasicSummary>(sql).FirstOrDefault();
return(rlt);
}
/// <summary>
/// 执行存储过程返回表单
/// </summary>
public static IList <T> SqlQuery <T>(this CHIS.DbContext.CHISEntitiesSqlServer db, string sql, params object[] parameters)
where T : new()
{
//注意：不要对GetDbConnection获取到的conn进行using或者调用Dispose，否则DbContext后续不能再进行使用了，会抛异常
string connectionString = new Code.Utility.DataBaseHelper().ConnectionString;
using (var conn = new System.Data.SqlClient.SqlConnection(connectionString))
{
conn.Open();
using (var command = conn.CreateCommand())
{
command.CommandText = sql;
command.Parameters.AddRange(parameters);
var propts = typeof(T).GetProperties();
var rtnList = new List <T>();
T model; object val;
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
model = new T();
foreach (var l in propts)
{
try
{
val = reader[l.Name];
if (val == DBNull.Value)
{
l.SetValue(model, null);
}
else
{
l.SetValue(model, val);
}
}
catch { }
}
rtnList.Add(model);
}
}
return(rtnList);
}
}
}
