public DataSet GetDataSet(IQueryServerContext context)
{
var sql = $"select {resQueryFileds2} from ResContract ";
var wherePart = GetResFilter(context);
if (!string.IsNullOrEmpty(wherePart))
{
sql = String.Concat(sql, " WHERE ", wherePart);
}
var result = Utility.CurDatabase.ExecuteDataSet(sql);
return(result);
}
public DataSet GetDataSet(IQueryServerContext context)
{
var sql = $"select {resQueryFileds1},{resQueryFileds2} from fqres inner join ResContract on fqres.ResNm = ResContract.ResNm where fqres.resState<>1";
//where fqres.resState=11去掉了这个条件，只要有合同就查询，就是能查询之前的合同
var wherePart = GetResFilter(context);
if (!string.IsNullOrEmpty(wherePart))
{
sql = String.Concat(sql, " and ", wherePart);
}
var result = Utility.CurDatabase.ExecuteDataSet(sql);
return(result);
}
public DataSet GetDataSet(IQueryServerContext context)
{
//根据前台选择获取所有满足条件的合同信息
var needResList = GetResInfo(context);
//筛选不到资源直接返回空
if (needResList == null || needResList.Count == 0)
{
return(GetEmptySet());
}
//并行获取变更记录
var taskList = new Task <DataTable> [needResList.Count];
for (int i = 0; i < needResList.Count; ++i)
{
taskList[i] = Task <DataTable> .Factory.StartNew(GetEachChangeTable, needResList[i]);
}
//汇总dt
var dtResult = new DataTable();
foreach (var item in taskList)
{
//批量合并到汇总dt，获取result的时候会去等待当前任务执行完毕
var dt = item.Result;
if (dt == null || dt.Rows.Count == 0)
{
continue;
}
if (dtResult.Rows.Count == 0)
{
dtResult = dt.Copy();
}
else
{
foreach (DataRow dr in dt.Rows)
{
dtResult.ImportRow(dr);
}
}
}
//没有变更
if (dtResult.Rows.Count == 0)
{
return(GetEmptySet());
}
var ds = new DataSet();
ds.Tables.Add(dtResult);
return(ds);
}
/// <summary>
/// 返回数据结果
/// </summary>
/// <param name="context"></param>
/// <returns></returns>
public DataSet GetDataSet(IQueryServerContext context)
{
//核算单位,好像不管用
var hsdwmc = context[resdwmc].ToString();
context.AddMacroVal("hsdwmc", hsdwmc);
//资源类别和资源表联查
var resCountSql = @"SELECT
lsbzdw.lsbzdw_dwmc hsdwmc,
Zylb.path,
Zylb.Zylbbh,
Zylb.Zylbmc,
Zylb.Layer,
fqres.resslormj,
fqres.resnm,
fqres.reskpbh,
fqres.reslbbh,
fqres.reslbmc,
fqres.resjyfs,
fqres.reszybh,
fqres.reszymc,
fqres.ResSyzk,
fqres.resbmmc,
fqres.ressjnms,
fqres.restzrq,
fqres.resstatename
from
Zylb
left join fqres on fqres.ResTypeId = Zylb.Zylbnm";
var joinPart = GetResFilter(context);
if (!string.IsNullOrEmpty(joinPart))
{
resCountSql = String.Concat(resCountSql, " and ", joinPart);
}
resCountSql = String.Concat(resCountSql, " left join lsbzdw on fqres.resssdwid = lsbzdw.lsbzdw_dwbh ");
var orderByPart = " order by Zylb.Path";
//根据资源编号排序汇总，前台默认是类别必须必填
resCountSql = string.Concat(resCountSql, orderByPart);
var result = Utility.CurDatabase.ExecuteDataSet(resCountSql);
//统计结果
//获取查询级数
int resLbQueryLayer = Convert.ToInt32(context["reslblayer"]);
//int resLbQueryLayer = 4;
GetNotDetailCount(result, resLbQueryLayer);
return(result);
}
private string GetResFilter(IQueryServerContext context)
{
var list = new List <string>();
//如果资源类别编号有筛选条件，查询所有类别的合同,查询条件变化后是不是需要调整
//if (!string.IsNullOrEmpty(context[reslbbh]))
//{
// //先找到分级码
// var getPathSql = "select path from zylb where zylbbh={0}";
// var path = Utility.CurDatabase.ExecuteScalar(getPathSql, context[reslbbh]);
// //查询资源类别编号是所有下级的信息
// list.Add($"reslbbh in (select zylbbh from zylb where path like '{path}%') ");
//}
if (!string.IsNullOrEmpty(context[resbh]))
{
list.Add($"fqres.reszybh = '{context[resbh]}'");
}
if (!string.IsNullOrEmpty(context[ressyzk]))
{
list.Add($"fqres.ResSyzkId = '{context[ressyzk]}'");
}
if (!string.IsNullOrEmpty(context[resdwbh]))
{
var selectDwbh = context[resdwbh].ToString();
var selectDwnmSql = $"select lsbzdw_dwnm from lsbzdw where lsbzdw_dwbh='{selectDwbh}'";
var selectDwnm = Utility.CurDatabase.ExecuteScalar(selectDwnmSql).ToString();
//找到核算单位的所有下级
var hsdwSql = $"select lsbzdw_dwbh from LSBZDW where lsbzdw_dwnm like '{selectDwnm}%'";
list.Add($"fqres.ResSsdwId in ({hsdwSql})");
}
//注意日期的处理
if (!string.IsNullOrEmpty(context[tzrq]))
{
var tzrqTime = Convert.ToDateTime(context[tzrq]).ToString("yyyy-MM-dd");
//前台是空的时候传过来的是"0001-01-01"
if (tzrqTime.StartsWith("0001") == false)
{
list.Add($"convert(varchar(10),fqres.restzrq,121)<='{tzrqTime}%'");
}
}
//没有是全部
if (list.Count == 0)
{
return(string.Empty);
}
return(string.Join(" And ", list));
}
/// <summary>
/// 查询资源合同变更日志记录
/// </summary>
/// <param name="context"></param>
/// <returns></returns>
public DataSet GetDataSet(IQueryServerContext context)
{
var dataid = context["htnm"];
var reshtCode = context["htbh"];
var reshtName = context["htmc"];
var dt = GetChangeDataTable(resHtModelId, dataid, reshtCode, reshtName);
if (dt == null || dt.Rows.Count == 0)
{
return(GetEmptySet());
}
var ds = new DataSet();
ds.Tables.Add(dt);
return(ds);
}
/// <summary>
/// 返回数据结果
/// </summary>
/// <param name="context"></param>
/// <returns></returns>
public DataSet GetDataSet(IQueryServerContext context)
{
//把选中的核算单位名称加入到副标题
var hsdwmc = context[resdwmc].ToString();
context.AddMacroVal("hsdwmc", hsdwmc);
var sql = $"select {resQueryFileds} from fqres";
var wherePart = GetResFilter(context);
if (!string.IsNullOrEmpty(wherePart))
{
sql = String.Concat(sql, " where ", wherePart);
}
var result = Utility.CurDatabase.ExecuteDataSet(sql);
return(result);
}
private string GetResFilter(IQueryServerContext context)
{
var list = new List <string>();
if (!string.IsNullOrEmpty(context[reslbbn]))
{
list.Add($"reslbbh='{context[reslbbn]}'");
}
if (!string.IsNullOrEmpty(context[resbh]))
{
list.Add($"reszybh = '{context[resbh]}'");
}
if (!string.IsNullOrEmpty(context[ressyzk]))
{
list.Add($"ResSyzkId = '{context[ressyzk]}'");
}
//是包含下级还是只看本级？
if (!string.IsNullOrEmpty(context[resdwbh]))
{
list.Add($"ResSsdwId = '{context[resdwbh]}'");
}
//注意日期的处理
if (!string.IsNullOrEmpty(context[tzrq]))
{
var tzrqTime = Convert.ToDateTime(context[tzrq]).ToString("yyyy-MM-dd");
//前台是空的时候传过来的是"0001-01-01"
if (tzrqTime.StartsWith("0001") == false)
{
list.Add($"convert(varchar(10),restzrq,121)<='{tzrqTime}%'");
}
}
//没有是全部
if (list.Count == 0)
{
return(string.Empty);
}
return(string.Join(" and ", list));
}
private string GetResFilter(IQueryServerContext context)
{
var resFilterList = new List <string>();
#region 关于资源的,单独查资源的表获取ID
//资源类别编号
if (!string.IsNullOrEmpty(context[reslbbn]))
{
resFilterList.Add($"reslbbh='{context[reslbbn]}'");
}
//资源编号
if (!string.IsNullOrEmpty(context[resbh]))
{
resFilterList.Add($"reszybh = '{context[resbh]}'");
}
//资源使用状况
if (!string.IsNullOrEmpty(context[ressyzk]))
{
resFilterList.Add($"ResSyzkId = '{context[ressyzk]}'");
}
var resFilter = string.Empty;
if (resFilterList.Count != 0)
{
var resPart = string.Join(" and ", resFilterList);
resFilter = $"select resnm from fqres where {resPart}";
}
#endregion
var list = new List <string>();
//资源的过滤
if (!string.IsNullOrEmpty(resFilter))
{
list.Add($"resnm in({resFilter}) ");
}
//合同类别编号
if (!string.IsNullOrEmpty(context[HTLBBH]))
{
list.Add($"HtLb = '{context[HTLBBH]}'");
}
//合同编号
if (!string.IsNullOrEmpty(context[HTBH]))
{
list.Add($"HtBh = '{context[HTBH]}'");
}
//合同填制日期，注意日期的处理
if (!string.IsNullOrEmpty(context[tzrq]))
{
var tzrqTime = Convert.ToDateTime(context[tzrq]).ToString("yyyy-MM-dd");
//前台是空的时候传过来的是"0001-01-01"
if (tzrqTime.StartsWith("0001") == false)
{
list.Add($"convert(varchar(10),CreatedDate,121)<='{tzrqTime}'");
}
}
//合同开始时间
if (!string.IsNullOrEmpty(context[HTKSSJ]))
{
var HTKSSJTime = Convert.ToDateTime(context[HTKSSJ]).ToString("yyyy-MM-dd");
//前台是空的时候传过来的是"0001-01-01"
if (HTKSSJTime.StartsWith("0001") == false)
{
list.Add($"convert(varchar(10),HtKsrq,121) like '{HTKSSJTime}%'");
}
}
//合同结束时间
if (!string.IsNullOrEmpty(context[HTJSSJ]))
{
var HTJSSJTime = Convert.ToDateTime(context[HTJSSJ]).ToString("yyyy-MM-dd");
//前台是空的时候传过来的是"0001-01-01"
if (HTJSSJTime.StartsWith("0001") == false)
{
list.Add($"convert(varchar(10),HtJsrq,121) like '{HTJSSJTime}%'");
}
}
//合同所属核算单位
if (!string.IsNullOrEmpty(context[resdwbh]))
{
list.Add($"hthsdwbh='{context[resdwbh]}'");
}
//没有是全部
if (list.Count == 0)
{
return(string.Empty);
}
return(string.Join(" And ", list));
}
/// <summary>
/// 分页情况
/// </summary>
/// <param name="context"></param>
/// <param name="startRecNum"></param>
/// <param name="endRecNum"></param>
/// <returns></returns>
public DataSet GetDataSetByPage(IQueryServerContext context, int startRecNum, int endRecNum)
{
throw new System.NotImplementedException();
}
private List <ResInfo> GetResInfo(IQueryServerContext context)
{
var wherePart = string.Empty;
var list = new List <string>();
//资源类别名称
if (!string.IsNullOrEmpty(context["zylbmc"]))
{
list.Add($"ResLbmc = '{context["zylbmc"]}'");
}
//资源编号
if (!string.IsNullOrEmpty(context["zybh"]))
{
list.Add($"ResZybh = '{context["zybh"]}'");
}
//资源名称名称
if (!string.IsNullOrEmpty(context["zymc"]))
{
list.Add($"ResZymc = '{context["zymc"]}'");
}
//资源核算单位
if (!string.IsNullOrEmpty(context["hsdwbh"]))
{
list.Add($"ResSsdwId = '{context["hsdwbh"]}'");
}
//资源签订日期
if (!string.IsNullOrEmpty(context["zytzrq"]))
{
var tzrqTime = Convert.ToDateTime(context["zytzrq"]).ToString("yyyy-MM-dd");
//前台是空的时候传过来的是"0001-01-01"
if (tzrqTime.StartsWith("0001") == false)
{
//2018-01-01:10位数只计算到天数
list.Add($"convert(varchar(10),ResTzrq,121) <= '{tzrqTime}'");
}
}
//没有是全部
if (list.Count != 0)
{
wherePart = string.Join(" and ", list);
}
wherePart = string.Join(" And ", list);
var sql = "select resnm,ResZybh,ResZymc,reslbmc from fqres";
if (!string.IsNullOrEmpty(wherePart))
{
sql = string.Concat(sql, " where ", wherePart);
}
var resList = new List <ResInfo>();
var ds = Utility.CurDatabase.ExecuteDataSet(sql);
if (!DataSetValidator.IsDatasetValid(ds))
{
return(resList);
}
foreach (DataRow item in ds.Tables[0].Rows)
{
var resInfo = new ResInfo();
resInfo.Resnm = item["resnm"].ToString();
resInfo.Reszybh = item["ResZybh"].ToString();
resInfo.Reszymc = item["ResZymc"].ToString();
resList.Add(resInfo);
}
return(resList);
}
private List <HtInfo> GetHtInfo(IQueryServerContext context)
{
var wherePart = string.Empty;
var list = new List <string>();
//合同类别名称
if (!string.IsNullOrEmpty(context["htlbmc"]))
{
list.Add($"htlb = '{context["htlbmc"]}'");
}
//合同编号
if (!string.IsNullOrEmpty(context["htbh"]))
{
list.Add($"htbh = '{context["htbh"]}'");
}
//合同名称
if (!string.IsNullOrEmpty(context["htmc"]))
{
list.Add($"htmc = '{context["htmc"]}'");
}
//合同核算单位
if (!string.IsNullOrEmpty(context["hsdwbh"]))
{
list.Add($"hthsdwbh = '{context["hsdwbh"]}'");
}
//合同签订日期
if (!string.IsNullOrEmpty(context["tzrq"]))
{
var tzrqTime = Convert.ToDateTime(context["tzrq"]).ToString("yyyy-MM-dd");
//前台是空的时候传过来的是"0001-01-01"
if (tzrqTime.StartsWith("0001") == false)
{
list.Add($"convert(varchar(10),htqdrq,121) <= '{tzrqTime}'");
}
}
//没有是全部
if (list.Count != 0)
{
wherePart = string.Join(" and ", list);
}
wherePart = string.Join(" And ", list);
var sql = "select htnm,htbh,htmc,htlb from rescontract";
if (!string.IsNullOrEmpty(wherePart))
{
sql = string.Concat(sql, " where ", wherePart);
}
var htList = new List <HtInfo>();
var ds = Utility.CurDatabase.ExecuteDataSet(sql);
if (!DataSetValidator.IsDatasetValid(ds))
{
return(htList);
}
foreach (DataRow item in ds.Tables[0].Rows)
{
var htInfo = new HtInfo();
htInfo.Htnm = item["htnm"].ToString();
htInfo.Htbh = item["htbh"].ToString();
htInfo.Htmc = item["htmc"].ToString();
htList.Add(htInfo);
}
return(htList);
}
