/// <summary>
/// 根据医疗机构获取审核用户列表
/// </summary>
/// <param name="querystr"></param>
/// <param name="page"></param>
/// <param name="limit"></param>
/// <returns></returns>
public IActionResult GetCheckUserListByYLJG(string querystr, int page, int limit)
{
YYKKGL result = new YYKKGL();
if (!string.IsNullOrEmpty(querystr))
{
result = JsonConvert.DeserializeObject <YYKKGL>(querystr);
}
var resultCountModel = new RespResultCountViewModel();
int totalcount = 0;
try
{
bool isadmin = false;
string curryydm = User.GetCurrentUserOrganizeId();
if (User.GetCurrentUserName() == "admin" || _afterCheckService.IsCityYBJ(curryydm)) //如果为管理员或者市医保局 可查看所有
{
isadmin = true;
}
var data = _complaintStatisticalService.GetCheckUserListsByYLJG(result, isadmin, curryydm, page, limit, ref totalcount);
if (data != null)
{
resultCountModel.code = 0;
resultCountModel.msg = "获取数据成功";
resultCountModel.data = data;
resultCountModel.count = totalcount;
}
else
{
resultCountModel.code = -1;
resultCountModel.msg = "没有检索到数据";
}
return(Ok(resultCountModel));
}
catch (Exception ex)
{
resultCountModel.code = -1;
resultCountModel.msg = "操作失败:" + ex.ToString();
return(Ok(resultCountModel));
}
}
public List <YYKKGL> GetYYKKList(YYKKGL queryCodition, bool isAdmin, string curryydm, int page, int limit, ref int count)
{
var list = new List <YYKKGL>();
string bt1 = ((page - 1) * limit + 1).ToString();
string bt2 = (page * limit).ToString();
StringBuilder query = new StringBuilder();
if (string.IsNullOrEmpty(queryCodition.StartTime))
{
queryCodition.StartTime = new DateTime(DateTime.Now.Year, 1, 1).ToString("yyyy-MM-dd");
}
if (string.IsNullOrEmpty(queryCodition.EndTime))
{
queryCodition.EndTime = new DateTime(DateTime.Now.Year, 12, 1).AddMonths(1).AddDays(-1).ToString("yyyy-MM-dd");
}
if (string.IsNullOrEmpty(queryCodition.StartConclusionTime))
{
queryCodition.StartConclusionTime = new DateTime(DateTime.Now.Year, 1, 1).ToString("yyyy-MM-dd");
}
if (string.IsNullOrEmpty(queryCodition.EndConclusionTime))
{
queryCodition.EndConclusionTime = new DateTime(DateTime.Now.Year, 12, 1).AddMonths(1).AddDays(-1).ToString("yyyy-MM-dd");
}
if (!string.IsNullOrEmpty(queryCodition.YYMC))
{
query.Append(" AND ccm.InstitutionName like '%" + queryCodition.YYMC + "%'");
}
if (!string.IsNullOrEmpty(queryCodition.JGDJ))
{
query.Append(" AND yhi.InstitutiongGradeCode='" + queryCodition.JGDJ + "' ");
}
if (!string.IsNullOrEmpty(queryCodition.ICDCode))
{
query.Append(" AND ccm.ICDCode='" + queryCodition.ICDCode + "'");
}
if (!string.IsNullOrEmpty(queryCodition.WGDJ))
{
query.Append(" AND ccm.YDLevel='" + queryCodition.WGDJ + "'");
}
if (!string.IsNullOrEmpty(queryCodition.StartConclusionTime))
{
query.Append(" AND ccm.DoubtfulConclusionTime >= '" + queryCodition.StartConclusionTime + "' ");
}
if (!string.IsNullOrEmpty(queryCodition.EndConclusionTime))
{
query.Append(" AND ccm.DoubtfulConclusionTime <= '" + queryCodition.EndConclusionTime + "' ");
}
using (var db = _dbContext.GetIntance())
{
if (isAdmin)
{
if (!string.IsNullOrEmpty(queryCodition.YYBM))
{
query.Append(" AND ccm.InstitutionCode = '" + queryCodition.YYBM + "'");
}
list = db.Ado.SqlQuery <YYKKGL>("SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY ccm.InstitutionCode) AS RowId," +
"ccm.InstitutionCode AS YYBM,ccm.InstitutionName AS YYMC,'居民基本医疗保险' AS XZLB,'住院' AS FYLY,SUM(ALLMoney) AS SJKKJE," +
"CONVERT(varchar(100), convert(datetime,'" + queryCodition.StartTime + "', 20), 23) + ' 至 ' + CONVERT(varchar(100), convert(datetime,'" + queryCodition.EndTime + "',20), 23) AS SettlementTime," +
"CONVERT(varchar(100), convert(datetime,'" + queryCodition.StartConclusionTime + "', 20), 23) + ' 至 ' + CONVERT(varchar(100), convert(datetime,'" + queryCodition.EndConclusionTime + "',20), 23) AS ConclusionTime, SUM(yhi.zfy) AS JSJE,SUM(yhi.YBBXFY) AS TCJE,SUM(yhi.DBBXBXFY) AS DBJE," +
"COUNT(yhi.IdNumber) AS CKRC FROM Check_Complain_MZL AS ccm LEFT JOIN YB_HosInfo AS yhi ON ccm.RegisterCode=yhi.HosRegisterCode " +
"WHERE ccm.ComplaintStatus='10' AND yhi.SettlementDate>='" + queryCodition.StartTime + "' AND yhi.SettlementDate<='" + queryCodition.EndTime + "' " + query +
"GROUP BY ccm.InstitutionCode,ccm.InstitutionName) AS tj WHERE tj.RowId BETWEEN " + bt1 + " AND " + bt2);
count = db.Ado.GetInt("SELECT COUNT(1) FROM (SELECT ROW_NUMBER() OVER(ORDER BY ccm.InstitutionCode) AS RowId," +
"ccm.InstitutionCode,ccm.InstitutionName AS YYMC,'居民基本医疗保险' AS XZLB,'住院' AS FYLY,SUM(ALLMoney) AS SJKKJE," +
"CONVERT(varchar(100), convert(datetime,'" + queryCodition.StartTime + "', 20), 23) + ' 至 ' + CONVERT(varchar(100), convert(datetime,'" + queryCodition.EndTime + "',20), 23) AS SettlementTime," +
"CONVERT(varchar(100), convert(datetime,'" + queryCodition.StartConclusionTime + "', 20), 23) + ' 至 ' + CONVERT(varchar(100), convert(datetime,'" + queryCodition.EndConclusionTime + "',20), 23) AS ConclusionTime, SUM(yhi.zfy) AS JSJE,SUM(yhi.YBBXFY) AS TCJE,SUM(yhi.DBBXBXFY) AS DBJE," +
"COUNT(yhi.IdNumber) AS CKRC FROM Check_Complain_MZL AS ccm LEFT JOIN YB_HosInfo AS yhi ON ccm.RegisterCode=yhi.HosRegisterCode " +
"WHERE ccm.ComplaintStatus='10' AND yhi.SettlementDate>='" + queryCodition.StartTime + "' AND yhi.SettlementDate<='" + queryCodition.EndTime + "' " + query +
"GROUP BY ccm.InstitutionCode,ccm.InstitutionName) AS tj");
}
else if (curryydm.Substring(0, 2) == "15") //是医院
{
query.Append(" AND ccm.InstitutionCode = '" + curryydm + "'");
list = db.Ado.SqlQuery <YYKKGL>("SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY ccm.InstitutionCode) AS RowId," +
"ccm.InstitutionCode AS YYBM,ccm.InstitutionName AS YYMC,'居民基本医疗保险' AS XZLB,'住院' AS FYLY,SUM(ALLMoney) AS SJKKJE," +
"CONVERT(varchar(100), convert(datetime,'" + queryCodition.StartTime + "', 20), 23) + ' 至 ' + CONVERT(varchar(100), convert(datetime,'" + queryCodition.EndTime + "',20), 23) AS SettlementTime," +
"CONVERT(varchar(100), convert(datetime,'" + queryCodition.StartConclusionTime + "', 20), 23) + ' 至 ' + CONVERT(varchar(100), convert(datetime,'" + queryCodition.EndConclusionTime + "',20), 23) AS ConclusionTime, SUM(yhi.zfy) AS JSJE,SUM(yhi.YBBXFY) AS TCJE,SUM(yhi.DBBXBXFY) AS DBJE," +
"COUNT(yhi.IdNumber) AS CKRC FROM Check_Complain_MZL AS ccm LEFT JOIN YB_HosInfo AS yhi ON ccm.RegisterCode=yhi.HosRegisterCode " +
"WHERE ccm.ComplaintStatus='10' AND yhi.SettlementDate>='" + queryCodition.StartTime + "' AND yhi.SettlementDate<='" + queryCodition.EndTime + "' " + query +
"GROUP BY ccm.InstitutionCode,ccm.InstitutionName) AS tj WHERE tj.RowId BETWEEN " + bt1 + " AND " + bt2);
count = db.Ado.GetInt("SELECT COUNT(1) FROM (SELECT ROW_NUMBER() OVER(ORDER BY ccm.InstitutionCode) AS RowId," +
"ccm.InstitutionCode,ccm.InstitutionName AS YYMC,'居民基本医疗保险' AS XZLB,'住院' AS FYLY,SUM(ALLMoney) AS SJKKJE," +
"CONVERT(varchar(100), convert(datetime,'" + queryCodition.StartTime + "', 20), 23) + ' 至 ' + CONVERT(varchar(100), convert(datetime,'" + queryCodition.EndTime + "',20), 23) AS SettlementTime," +
"CONVERT(varchar(100), convert(datetime,'" + queryCodition.StartConclusionTime + "', 20), 23) + ' 至 ' + CONVERT(varchar(100), convert(datetime,'" + queryCodition.EndConclusionTime + "',20), 23) AS ConclusionTime, SUM(yhi.zfy) AS JSJE,SUM(yhi.YBBXFY) AS TCJE,SUM(yhi.DBBXBXFY) AS DBJE," +
"COUNT(yhi.IdNumber) AS CKRC FROM Check_Complain_MZL AS ccm LEFT JOIN YB_HosInfo AS yhi ON ccm.RegisterCode=yhi.HosRegisterCode " +
"WHERE ccm.ComplaintStatus='10' AND yhi.SettlementDate>='" + queryCodition.StartTime + "' AND yhi.SettlementDate<='" + queryCodition.EndTime + "' " + query +
"GROUP BY ccm.InstitutionCode,ccm.InstitutionName) AS tj");
}
else //旗县医保局
{
var XAreaCode = returnXAreaCode(curryydm).Substring(0, 6);
query.Append(" AND SUBSTRING(ccm.FamilyCode,0,7) = '" + XAreaCode + "'");
if (!string.IsNullOrEmpty(queryCodition.YYBM))
{
query.Append(" AND ccm.InstitutionCode = '" + queryCodition.YYBM + "'");
}
list = db.Ado.SqlQuery <YYKKGL>("SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY ccm.InstitutionCode) AS RowId," +
"ccm.InstitutionCode AS YYBM,ccm.InstitutionName AS YYMC,'居民基本医疗保险' AS XZLB,'住院' AS FYLY,SUM(ALLMoney) AS SJKKJE," +
"CONVERT(varchar(100), convert(datetime,'" + queryCodition.StartTime + "', 20), 23) + ' 至 ' + CONVERT(varchar(100), convert(datetime,'" + queryCodition.EndTime + "',20), 23) AS SettlementTime," +
"CONVERT(varchar(100), convert(datetime,'" + queryCodition.StartConclusionTime + "', 20), 23) + ' 至 ' + CONVERT(varchar(100), convert(datetime,'" + queryCodition.EndConclusionTime + "',20), 23) AS ConclusionTime, SUM(yhi.zfy) AS JSJE,SUM(yhi.YBBXFY) AS TCJE,SUM(yhi.DBBXBXFY) AS DBJE," +
"COUNT(yhi.IdNumber) AS CKRC FROM Check_Complain_MZL AS ccm LEFT JOIN YB_HosInfo AS yhi ON ccm.RegisterCode=yhi.HosRegisterCode " +
"WHERE ccm.ComplaintStatus='10' AND yhi.SettlementDate>='" + queryCodition.StartTime + "' AND yhi.SettlementDate<='" + queryCodition.EndTime + "' " + query +
"GROUP BY ccm.InstitutionCode,ccm.InstitutionName) AS tj WHERE tj.RowId BETWEEN " + bt1 + " AND " + bt2);
count = db.Ado.GetInt("SELECT COUNT(1) FROM (SELECT ROW_NUMBER() OVER(ORDER BY ccm.InstitutionCode) AS RowId," +
"ccm.InstitutionCode,ccm.InstitutionName AS YYMC,'居民基本医疗保险' AS XZLB,'住院' AS FYLY,SUM(ALLMoney) AS SJKKJE," +
"CONVERT(varchar(100), convert(datetime,'" + queryCodition.StartTime + "', 20), 23) + ' 至 ' + CONVERT(varchar(100), convert(datetime,'" + queryCodition.EndTime + "',20), 23) AS SettlementTime," +
"CONVERT(varchar(100), convert(datetime,'" + queryCodition.StartConclusionTime + "', 20), 23) + ' 至 ' + CONVERT(varchar(100), convert(datetime,'" + queryCodition.EndConclusionTime + "',20), 23) AS ConclusionTime, SUM(yhi.zfy) AS JSJE,SUM(yhi.YBBXFY) AS TCJE,SUM(yhi.DBBXBXFY) AS DBJE," +
"COUNT(yhi.IdNumber) AS CKRC FROM Check_Complain_MZL AS ccm LEFT JOIN YB_HosInfo AS yhi ON ccm.RegisterCode=yhi.HosRegisterCode " +
"WHERE ccm.ComplaintStatus='10' AND yhi.SettlementDate>='" + queryCodition.StartTime + "' AND yhi.SettlementDate<='" + queryCodition.EndTime + "' " + query +
"GROUP BY ccm.InstitutionCode,ccm.InstitutionName) AS tj");
}
}
return(list);
}
public List <CheckUserList> GetCheckUserListsByYLJG(YYKKGL queryCodition, bool isAdmin, string curryydm, int page, int limit, ref int count)
{
var list = new List <CheckUserList>();
string bt1 = ((page - 1) * limit + 1).ToString();
string bt2 = (page * limit).ToString();
StringBuilder query = new StringBuilder();
if (!string.IsNullOrEmpty(queryCodition.StartTime))
{
query.Append(" AND ccm.SettlementDate>= '" + queryCodition.StartTime + "'");
}
if (!string.IsNullOrEmpty(queryCodition.EndTime))
{
query.Append(" AND ccm.SettlementDate<= '" + queryCodition.EndTime + "'");
}
//if (!string.IsNullOrEmpty(queryCodition.YYBM))
//{
// query.Append(" AND ccm.InstitutionCode = '" + queryCodition.YYBM + "'");
//}
if (!string.IsNullOrEmpty(queryCodition.YYMC))
{
query.Append(" AND ccm.InstitutionName like '%" + queryCodition.YYMC + "%'");
}
if (!string.IsNullOrEmpty(queryCodition.JGDJ))
{
query.Append(" and a.InstitutionGradeCode='" + queryCodition.JGDJ + "'");
}
if (!string.IsNullOrEmpty(queryCodition.WGDJ))
{
query.Append(" and ccm.YDLevel='" + queryCodition.WGDJ + "'");
}
if (!string.IsNullOrEmpty(queryCodition.ICDCode))
{
query.Append(" and ccm.ICDCode='" + queryCodition.ICDCode + "'");
}
if (!string.IsNullOrEmpty(queryCodition.StartConclusionTime))
{
query.Append(" AND ccm.DoubtfulConclusionTime >= '" + queryCodition.StartConclusionTime + "' ");
}
if (!string.IsNullOrEmpty(queryCodition.EndConclusionTime))
{
query.Append(" AND ccm.DoubtfulConclusionTime <= '" + queryCodition.EndConclusionTime + "' ");
}
using (var db = _dbContext.GetIntance())
{
if (isAdmin)
{
if (!string.IsNullOrEmpty(queryCodition.YYBM))
{
query.Append(" AND ccm.InstitutionCode = '" + queryCodition.YYBM + "'");
}
list = db.Ado.SqlQuery <CheckUserList>(@"
SELECT * FROM(
SELECT ROW_NUMBER() OVER(ORDER BY ccm.SettlementDate DESC) AS RowId, ccm.RegisterCode, PersonalCode, IdNumber, Personname NAME,
a.Gender AS Gender, a.Age AS Age,
ccm.InstitutionCode, ccm.InstitutionName, ICDCode, DiseaseName, ALLMoney KKJE, SettlementDate, InHosDate, OutHosDate,
a.InstitutionGradeCode as InstitutionGradeCode, a.InstitutionGradeName as InstitutionGradeName
FROM Check_Complain_MZL AS ccm LEFT JOIN(SELECT RegisterCode, MAX(cri.InstitutionGradeCode) InstitutionGradeCode,
MAX(cri.InstitutionGradeName) InstitutionGradeName, MAX(Gender) Gender, MAX(Age) Age
FROM Check_ResultInfo AS cri GROUP BY cri.RegisterCode) a ON ccm.RegisterCode = a.RegisterCode WHERE ComplaintStatus = '10'" + query + @"
) tj where tj.RowId BETWEEN " + bt1 + " AND " + bt2);
count = db.Ado.GetInt(@"SELECT COUNT(1) FROM (SELECT ROW_NUMBER() OVER(ORDER BY ccm.SettlementDate DESC) AS RowId, ccm.RegisterCode, PersonalCode, IdNumber, Personname NAME,
a.Gender AS Gender, a.Age AS Age,
ccm.InstitutionCode, ccm.InstitutionName, ICDCode, DiseaseName, ALLMoney KKJE, SettlementDate, InHosDate, OutHosDate,
a.InstitutionGradeCode as InstitutionGradeCode, a.InstitutionGradeName as InstitutionGradeName
FROM Check_Complain_MZL AS ccm LEFT JOIN(SELECT RegisterCode, MAX(cri.InstitutionGradeCode) InstitutionGradeCode,
MAX(cri.InstitutionGradeName) InstitutionGradeName, MAX(Gender) Gender, MAX(Age) Age
FROM Check_ResultInfo AS cri GROUP BY cri.RegisterCode) a ON ccm.RegisterCode = a.RegisterCode WHERE ComplaintStatus = '10'" + query + ") AS tj");
}
else if (curryydm.Substring(0, 2) == "15") //是医院
{
query.Append(" AND ccm.InstitutionCode = '" + curryydm + "'");
list = db.Ado.SqlQuery <CheckUserList>(@"
SELECT * FROM(
SELECT ROW_NUMBER() OVER(ORDER BY ccm.SettlementDate DESC) AS RowId, ccm.RegisterCode, PersonalCode, IdNumber, Personname NAME,
a.Gender AS Gender, a.Age AS Age,
ccm.InstitutionCode, ccm.InstitutionName, ICDCode, DiseaseName, ALLMoney KKJE, SettlementDate, InHosDate, OutHosDate,
a.InstitutionGradeCode as InstitutionGradeCode, a.InstitutionGradeName as InstitutionGradeName
FROM Check_Complain_MZL AS ccm LEFT JOIN(SELECT RegisterCode, MAX(cri.InstitutionGradeCode) InstitutionGradeCode,
MAX(cri.InstitutionGradeName) InstitutionGradeName, MAX(Gender) Gender, MAX(Age) Age
FROM Check_ResultInfo AS cri GROUP BY cri.RegisterCode) a ON ccm.RegisterCode = a.RegisterCode WHERE ComplaintStatus = '10'" + query + @"
) tj where tj.RowId BETWEEN " + bt1 + " AND " + bt2);
count = db.Ado.GetInt(@"SELECT COUNT(1) FROM (SELECT ROW_NUMBER() OVER(ORDER BY ccm.SettlementDate DESC) AS RowId, ccm.RegisterCode, PersonalCode, IdNumber, Personname NAME,
a.Gender AS Gender, a.Age AS Age,
ccm.InstitutionCode, ccm.InstitutionName, ICDCode, DiseaseName, ALLMoney KKJE, SettlementDate, InHosDate, OutHosDate,
a.InstitutionGradeCode as InstitutionGradeCode, a.InstitutionGradeName as InstitutionGradeName
FROM Check_Complain_MZL AS ccm LEFT JOIN(SELECT RegisterCode, MAX(cri.InstitutionGradeCode) InstitutionGradeCode,
MAX(cri.InstitutionGradeName) InstitutionGradeName, MAX(Gender) Gender, MAX(Age) Age
FROM Check_ResultInfo AS cri GROUP BY cri.RegisterCode) a ON ccm.RegisterCode = a.RegisterCode WHERE ComplaintStatus = '10'" + query + ") AS tj");
}
else //旗县医保局
{
var XAreaCode = returnXAreaCode(curryydm).Substring(0, 6);
query.Append(" AND SUBSTRING(ccm.FamilyCode,0,7) = '" + XAreaCode + "'");
if (!string.IsNullOrEmpty(queryCodition.YYBM))
{
query.Append(" AND ccm.InstitutionCode = '" + queryCodition.YYBM + "'");
}
list = db.Ado.SqlQuery <CheckUserList>(@"
SELECT * FROM(
SELECT ROW_NUMBER() OVER(ORDER BY ccm.SettlementDate DESC) AS RowId, ccm.RegisterCode, PersonalCode, IdNumber, Personname NAME,
a.Gender AS Gender, a.Age AS Age,
ccm.InstitutionCode, ccm.InstitutionName, ICDCode, DiseaseName, ALLMoney KKJE, SettlementDate, InHosDate, OutHosDate,
a.InstitutionGradeCode as InstitutionGradeCode, a.InstitutionGradeName as InstitutionGradeName
FROM Check_Complain_MZL AS ccm LEFT JOIN(SELECT RegisterCode, MAX(cri.InstitutionGradeCode) InstitutionGradeCode,
MAX(cri.InstitutionGradeName) InstitutionGradeName, MAX(Gender) Gender, MAX(Age) Age
FROM Check_ResultInfo AS cri GROUP BY cri.RegisterCode) a ON ccm.RegisterCode = a.RegisterCode WHERE ComplaintStatus = '10'" + query + @"
) tj where tj.RowId BETWEEN " + bt1 + " AND " + bt2);
count = db.Ado.GetInt(@"SELECT COUNT(1) FROM (SELECT ROW_NUMBER() OVER(ORDER BY ccm.SettlementDate DESC) AS RowId, ccm.RegisterCode, PersonalCode, IdNumber, Personname NAME,
a.Gender AS Gender, a.Age AS Age,
ccm.InstitutionCode, ccm.InstitutionName, ICDCode, DiseaseName, ALLMoney KKJE, SettlementDate, InHosDate, OutHosDate,
a.InstitutionGradeCode as InstitutionGradeCode, a.InstitutionGradeName as InstitutionGradeName
FROM Check_Complain_MZL AS ccm LEFT JOIN(SELECT RegisterCode, MAX(cri.InstitutionGradeCode) InstitutionGradeCode,
MAX(cri.InstitutionGradeName) InstitutionGradeName, MAX(Gender) Gender, MAX(Age) Age
FROM Check_ResultInfo AS cri GROUP BY cri.RegisterCode) a ON ccm.RegisterCode = a.RegisterCode WHERE ComplaintStatus = '10'" + query + ") AS tj");
}
}
return(list);
}