using SuValue.DAL;
using SuValue.Entity;
using SuValue.Entity.Enum;
using SuValue.Entity.Request;
using SuValue.Entity.Responese;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace SuValue.BLL
{
public class ReportBLL
{
/// <summary>
/// 通过传入的条件拼接sql语句
/// </summary>
/// <param name="search"></param>
/// <returns></returns>
public string SqlWhere(EntitySearch mol)
{
string sql = " ";
if (!string.IsNullOrWhiteSpace(mol.Province))
{
sql = " and Province in (" + ToInStr(mol.Province) + ")";
}
if (!string.IsNullOrWhiteSpace(mol.CommonName))
{
string CommonName = string.Empty;
string DrugName = string.Empty;
foreach (var item in mol.CommonName.Split(','))
{
var item1 = item.Split('&');
if (item1.Length > 1)
{
DrugName += " '" + item1[1] + "',";
}
CommonName += " '" + item1[0] + "',";
}
if (!string.IsNullOrEmpty(CommonName))
sql += " and CommonName in (" + (CommonName.Length > 1 ? CommonName.Substring(0, CommonName.Length - 1) : "") + ")";
if (!string.IsNullOrEmpty(DrugName))
sql += " and DrugName in (" + (DrugName.Length > 1 ? DrugName.Substring(0, DrugName.Length - 1) : "") + ")";
}
if (!string.IsNullOrWhiteSpace(mol.DrugName))
{
sql += " and DrugName in (" + ToInStr(mol.DrugName) + ")";
}
if (!string.IsNullOrWhiteSpace(mol.Disease))
{
sql += " and Disease in (" + ToInStr(mol.Disease) + ")";
}
if (!string.IsNullOrWhiteSpace(mol.HosLevel))
{
sql += " and HosLevel in (" + ToInStr(mol.HosLevel) + ")";
}
if (!string.IsNullOrWhiteSpace(mol.HosType))
{
sql += " and HosType in (" + ToInStr(mol.HosType) + ")";
}
if (!string.IsNullOrWhiteSpace(mol.Year))
{
sql += " and Year in (" + ToInStr(mol.Year) + ")";
}
if (!string.IsNullOrWhiteSpace(mol.Month))
{
sql += " and Month in (" + ToInStr(mol.Month) + ")";
}
if (!string.IsNullOrWhiteSpace(mol.Source))
{
sql += " and Source in (" + ToInStr(mol.Source) + ")";
}
return sql;
}
/// <summary>
/// 转换sql In 条件
/// </summary>
/// <param name="str">需要转换sql语句</param>
/// <returns></returns>
private string ToInStr(string str)
{
string insql = string.Empty;
foreach (var item in str.Split(','))
{
insql += " '" + item + "',";
}
return insql.Length > 1 ? insql.Substring(0, insql.Length - 1) : "";
}
/// <summary>
/// 根据页面条件获取具体报表内容
/// </summary>
/// <param name="search"></param>
/// <returns></returns>
public List<EntityReport> GetReportData(EntitySearch search)
{
//根据菜单编码ID获取具体报表信息
if (search == null || search.MenuID.GetValueOrDefault() == 0) return new List<EntityReport>();
string wheresql = SqlWhere(search);
return GetRepots(search, search.MenuID.GetValueOrDefault(), wheresql, search.token);
}
/// <summary>
/// 根据菜单条件获取缓存数据
/// </summary>
/// <param name="MenuID"></param>
/// <param name="sqlwhere"></param>
/// <returns></returns>
public List<EntityReport> GetRepots(EntitySearch search, int MenuID, string wheresql, string project_open_key)
{
List<EntityReport> reportList = new List<EntityReport>();
using (da_reportDAL dal = new da_reportDAL())
{
List<da_report> repots = dal.GetMenuIDReportList(MenuID);
sys_projectDAL projectdal = new sys_projectDAL();
sys_project project = projectdal.GetOpenKey(project_open_key);
Sys_dataDAL datadal = new Sys_dataDAL(project);
foreach (var item in repots.OrderBy(o => o.sort))//组装具体报表
{
string sql = item.ExecSql.Replace("{wh}", " " + wheresql).Replace("{w}", " where 1=1 " + wheresql);
if (!string.IsNullOrEmpty(search.Year) && search.Year.IndexOf(',') == -1) { sql = sql.Replace("{year}", "" + search.Year).Replace("{yesteryear}", "" + (Convert.ToInt32(search.Year) - 1)); }
EntityReport report = new EntityReport
{
title = item.title,
vTitle = item.VTitle,
vUnit = item.VUnit,
xTitle = item.XTitle,
xUnit = item.XUnit,
yTitle = item.YTitle,
yUnit = item.YUnit,
chartType = item.charttype,
chartData = datadal.GetChartData(sql),
formula = item.formula,
specialType = item.SpecialType,
dataType = item.datatype
};
if (item.charttype == 98) //转换树型结构数据 树型结构 name 表示 树名称、X 自身ID Y 父级ID VALUE 具体内容
{
if (report.chartData == null) break;
if (report.treeData == null) report.treeData = new List<EntityTreeData>();
foreach (var chartdata in report.chartData.Where(o => o.y == "0").ToList())
{
report.treeData.Add(LoadTreeData(chartdata, report.chartData));
}
}
reportList.Add(report);
}
}
return reportList;
}
/// <summary>
/// 重新加载树型数据
/// </summary>
/// <param name="report"></param>
public EntityTreeData LoadTreeData(EntityChartData chartdata, List<EntityChartData> chartdatalist)
{
EntityTreeData treedata = new EntityTreeData
{
name = chartdata.name,
value = chartdata.value.ToString(),
children = new List<EntityTreeData>()
};
foreach (var item in chartdatalist.Where(o => o.y == chartdata.x).ToList())
{
treedata.children.Add(LoadTreeData(item, chartdatalist));
}
return treedata;
}
/// <summary>
/// 根据条件再次柱状SQL语句
/// </summary>
/// <param name="search"></param>
/// <param name="sql">需要执行的SQL语句</param>
/// <returns></returns>
public List<EntityChartData> GetChartData(EntitySearch search, string sql)
{
string year = "";
if (search.Year != null)
{
year = search.Year;
search.Year = null;
}
string wheresql = SqlWhere(search);
sys_projectDAL projectdal = new sys_projectDAL();
sys_project project = projectdal.GetOpenKey(search.token);
Sys_dataDAL datadal = new Sys_dataDAL(project);
return datadal.GetChartData(sql.Replace("{wh}", " " + wheresql).Replace("{w}", " where 1=1 " + wheresql).Replace("{year}", "" + year), false, false);
}
/// <summary>
/// 根据条件获取表单
/// </summary>
/// <param name="search"></param>
/// <param name="sql">需要执行的SQL语句</param>
/// <returns></returns>
public List<T> GetTableData<T>(EntitySearch search, string sql) where T : new()
{
string year = "";
if (search.Year != null)
{
year = search.Year;
search.Year = null;
}
string wheresql = SqlWhere(search);
sys_projectDAL projectdal = new sys_projectDAL();
sys_project project = projectdal.GetOpenKey(search.token);
Sys_dataDAL datadal = new Sys_dataDAL(project);
return datadal.GetTableData<T>(sql.Replace("{wh}", " " + wheresql).Replace("{w}", " where 1=1 " + wheresql).Replace("{year}", "" + year).Replace("{yesteryear}", "" + (Convert.ToInt32(year) - 1)));
}
}
}