/// <summary>
/// 通讯异常统计
/// </summary>
//public class Pxmplan
//{
// private int _r;
// public int R
// {
// get { return _r; }
// set { _r = value; }
// }
// private string _id;
// public string Id
// {
// get { return _id; }
// set { _id = value; }
// }
// private string city;
// public string City
// {
// get { return city; }
// set { city = value; }
// }
// private string mineNum;// varchar(50) Checked煤矿编码
// public string MineNum
// {
// get { return mineNum; }
// set { mineNum = value; }
// }
// private string mineName;// varchar(50) Checked煤矿名称
// /// <summary>
// /// 煤矿名称
// /// </summary>
// public string MineName
// {
// get { return mineName; }
// set { mineName = value; }
// }
// private string rq;//填写日期
// /// <summary>
// /// 填写日期
// /// </summary>
// public string Rq
// {
// get { return rq; }
// set { rq = value; }
// }
// private string contents;//计划内容
// /// <summary>
// /// 计划内容
// /// </summary>
// public string Contents
// {
// get { return contents; }
// set { contents = value; }
// }
//}
///// <summary>
///// 通讯异常查询
///// </summary>
///// <param name="sql"></param>
///// <returns></returns>
//public string QueryInfo(string sql)
//{
// sql = "select * from Report_config where "+sql;
// string jsonStr = "";
// DataTable dt = SQLDataServer.ToDataTable(sql, conn);
// if (dt != null && dt.Rows.Count > 0)
// {
// //此数据只有一条（取第一条）
// Pxmplan m = new Pxmplan();
// m.Contents = dt.Rows[0]["Remark"].ToString();
// m.MineName = dt.Rows[0]["ReportName"].ToString();
// m.MineNum = dt.Rows[0]["DispName"].ToString();
// m.Rq = dt.Rows[0]["CreateTime"].ToString();
// jsonStr = JsonConvert.SerializeObject(m);
// }
// return jsonStr;
//}
/// <summary>
/// 获取指定报表类型的报表信息
/// </summary>
/// <param name="reportname">报表类型</param>
/// <returns>数据集</returns>
public DataTable GetReportList(string reportname)
{
//string sql = "select * from Report_config where Dispname='" + reportname + "'";
string sql = "select * from Report_config where ReportName='" + reportname + "'";
return(SQLDataServer.ToDataTable(sql, conn));
}
/// <summary>
/// 日、月上下井报表
/// </summary>
/// <param name="reportable">【必须】报表表名</param>
/// <param name="minecode">煤矿编号，全部则为空</param>
/// <param name="begintime">【必选】开始时间</param>
/// <param name="endtime">【必选】结束时间</param>
/// <param name="VIEW">区分日报表、月报表</param>
/// <returns></returns>
public DataTable GetReportData_RYSXJBB(string minecode, DateTime BegingTime, DateTime EndTime, TransJsonToTreeListModel.EnumDataType VIEW)
{
int ts = 6;
if (VIEW == TransJsonToTreeListModel.EnumDataType.ReportRYSXJBB_R || VIEW == TransJsonToTreeListModel.EnumDataType.ReportRYGBLDXJBB_R)
{
ts = 8;
}
var whereLD = " 1=1 ";
if (VIEW == TransJsonToTreeListModel.EnumDataType.ReportRYGBLDXJBB_R ||
VIEW == TransJsonToTreeListModel.EnumDataType.ReportRYGBLDXJBB_Y)
{
whereLD = "工种或职务 like '%矿领导%'";
}
string where = "SubmitTime>='" + BegingTime.ToString("yyyy-MM-dd HH:mm:ss") + "' and SubmitTime<'" + EndTime.ToString("yyyy-MM-dd HH:mm:ss") + "' And " + whereLD;
if (minecode != "" && minecode != null)
{
where += " and 煤矿编号='" + minecode + "'";
}
var sql = string.Format(
@"Select 煤矿编号,煤矿名称,姓名,标识卡,性别,工种或职务,部门,dbo.FunConvertTime(Sum(累计时间)) 累计时间,Sum(累计次数) 累计次数,Convert(varchar({0}),SubmitTime,112) TheDate From ShineView_His.dbo.Report_UpDown
Where {1}
Group By 煤矿编号,煤矿名称,姓名,标识卡,性别,工种或职务,部门,Convert(varchar({0}),SubmitTime,112)", ts, where);
return(SQLDataServer.ToDataTable(sql, conn));
}
/// <summary>
/// 加载模拟量日、月馈电异常数据
/// </summary>
/// <param name="reportable">【必须】报表表名</param>
/// <param name="minecode">煤矿编号，全部则为空</param>
/// <param name="begintime">【必选】开始时间</param>
/// <param name="endtime">【必选】结束时间</param>
/// <param name="VIEW">区分日报表、月报表</param>
/// <returns></returns>
public DataTable GetReportData_KD(string minecode, string devname, DateTime BegingTime, DateTime EndTime, TransJsonToTreeListModel.EnumDataType VIEW)
{
//string type = "A";
//int ts = 6;
//if (VIEW == TransJsonToTreeListModel.EnumDataType.ReportMNLRKD || VIEW == TransJsonToTreeListModel.EnumDataType.ReportKGLRKD)
// ts = 8;
//if (VIEW == TransJsonToTreeListModel.EnumDataType.ReportKGLRKD || VIEW == TransJsonToTreeListModel.EnumDataType.ReportKGLYKD)
// type = "D";
string where = "SubmitTime>='" + BegingTime.ToString("yyyy-MM-dd HH:mm:ss") + "' and SubmitTime<'" +
EndTime.ToString("yyyy-MM-dd HH:mm:ss") + "'";
if (minecode != "" && minecode != null)
{
where += " and 煤矿编号='" + minecode + "'";
}
if (devname != null && devname != "")
{
where += " and 设备名='" + devname + "'";
}
where += " and 累计次数 IS NOT NULL";
string sql = string.Format(@"SELECT 煤矿编号, 煤矿名称,测点编号,设备名,安装位置,单位,断电区域,count(累计次数) 累计次数,[ShineView_Data].dbo.FunConvertTime(sum(累计时间)) 累计时间 FROM ShineView_His.dbo.Report_FeedBack
WHERE {0}
GROUP BY 煤矿编号, 煤矿名称,测点编号,设备名,安装位置,单位,断电区域", where);
// var sql =
// string.Format(
// @"Select 煤矿编号,煤矿名称 ,测点编号 ,设备名 ,安装位置 ,断电区域,单位,Convert(varchar({0}),SubmitTime,112) TheDate,Sum(累计时间) 累计时间,Sum(累计次数) 累计次数 From
//(Select * From ShineView_His.dbo.Report_FeedBack T1 Left Join [ShineView_Data].[dbo].[DeviceType] T2 On T1.设备名=T2.TypeName ) T100 Where {1}
// Group By 煤矿编号,煤矿名称 ,测点编号 ,设备名 ,安装位置 ,断电区域,单位,Convert(varchar({0}),SubmitTime,112)", ts, where);
return(SQLDataServer.ToDataTable(sql, conn));
}
/// <summary>
/// 加载模拟量开关量日、月断电异常数据
/// </summary>
/// <param name="reportable">【必须】报表表名</param>
/// <param name="minecode">煤矿编号，全部则为空</param>
/// <param name="begintime">【必选】开始时间</param>
/// <param name="endtime">【必选】结束时间</param>
/// <param name="VIEW">区分日报表、月报表</param>
/// <returns></returns>
public DataTable GetReportData_DD(string minecode, string devname, DateTime BegingTime, DateTime EndTime, TransJsonToTreeListModel.EnumDataType VIEW)
{
//if (VIEW == TransJsonToTreeListModel.EnumDataType.ReportMNLRDD || VIEW == TransJsonToTreeListModel.EnumDataType.ReportKGLRDD)
// ts = 8;
//if (VIEW == TransJsonToTreeListModel.EnumDataType.ReportKGLRDD || VIEW == TransJsonToTreeListModel.EnumDataType.ReportKGLYDD)
// type = "D";
string where = "SubmitTime>='" + BegingTime.ToString("yyyy-MM-dd HH:mm:ss") + "' and SubmitTime<'" +
EndTime.ToString("yyyy-MM-dd HH:mm:ss") + "'";
if (minecode != "" && minecode != null)
{
where += " and 煤矿编号='" + minecode + "'";
}
if (devname != null && devname != "")
{
where += " and 设备名='" + devname + "'";
}
where += " and 断电次数 is not null";
string sql = string.Format(@"select 煤矿编号, 煤矿名称,测点编号,设备名,安装位置,单位, 断电值,复电值,max(最大值) 最大值 ,最大值时间,count(*) 断电次数,shineview_data.dbo.FunConvertTime (sum(累计时间)) 断电时间
from ShineView_His.dbo.Report_Power where {0} group by 煤矿编号, 煤矿名称,测点编号,设备名,安装位置,单位, 断电值,复电值,最大值时间", where);
// var sql = string.Format(
// @"Select T.*,TT.本次最大值时间 From (
//Select 煤矿编号 ,煤矿名称 ,测点编号 ,设备名 ,安装位置 ,单位 ,断电范围 ,Sum(IsNull(断电次数, 0)) 断电次数 ,Sum(IsNull(累计时间, 0)) 累计时间 ,Max(IsNull(最大值, 0)) 最大值 ,Avg(IsNull(本次平均值, 0)) 平均值
// ,Convert(varchar({0}),SubmitTime,112) TheDate From (
//Select * From ShineView_His.dbo.Report_Power T1 Left Join [ShineView_Data].[dbo].[DeviceType] T2 On T1.设备名=T2.TypeName ) T100 Where {1}
// Group By 煤矿编号,煤矿名称 ,测点编号 ,设备名 ,安装位置 ,断电范围,单位,Convert(varchar({0}),SubmitTime,112)
// ) T Left Join ShineView_His.dbo.Report_Power TT On T.最大值=TT.本次最大值", ts, where);
return(SQLDataServer.ToDataTable(sql, conn));
}
/// <summary>
/// 超员报表 2017-06-20新增
/// </summary>
/// <param name="MineCode"></param>
/// <param name="BeginTime"></param>
/// <param name="EndTime"></param>
/// <param name="flag"></param>
/// <returns></returns>
public DataTable GetReportRYCYBB(string MineCode, DateTime BeginTime, DateTime EndTime, int flag)
{
string sql1 = "select mc.SimpleName , cy.MineCode,cy.AreaName,cy.AreaCode,max(cy.Number) Number, " +
" max( isnull(cy.SUM,0) -isnull(cy.Number,0)) Counts,MAX(T1.InAreaTime) MaxTime, " +
" cy.StartAlTime,cy.EndAlTime,DATEDIFF(s, cy.StartAlTime, cy.EndAlTime)Continuous_Tmp,CAST(NULL AS VARCHAR(50)) Continuous ,cast(null as varchar(5)) CYL" +
" from ShineView_His.dbo.RYCYXZH cy " +
" left join ShineView_Data.dbo.MineConfig mc " +
" on mc.MineCode = cy.MineCode " +
" left JOIN " +
" ( " +
" select * From ( " +
" select minecode ,startaltime,endaltime,number,sum,InAreaTime,areacode ,row_number() over(partition by minecode ,areacode ,startaltime, endaltime order by minecode desc) rn " +
" From ShineView_His.dbo.RYCYXZH " +
" ) t where t.rn <=1 " +
" ) AS T1 " +
" on t1.minecode =cy.minecode and t1.startaltime=cy.startaltime and t1.endaltime =cy.endaltime and t1.areacode =cy.areacode and t1.number =cy.number where 1=" + flag;
string sql2 = " GROUP BY mc.SimpleName , cy.MineCode,cy.AreaName,cy.AreaCode,cy.Number,cy.StartAlTime,cy.EndAlTime,T1.InAreaTime";
if (!string.IsNullOrEmpty(MineCode))
{
sql1 += " and cy.MineCode ='" + MineCode + "'";
}
sql1 += " and cy.startaltime>='" + BeginTime + "'";
sql1 += " and cy.startaltime<='" + EndTime + "'";
string whereData = sql1 + sql2;
return(SQLDataServer.ToDataTable(whereData, conn));
}
/// <summary>
/// 加载模拟量日、月报警异常数据
/// </summary>
/// <param name="reportable">【必须】报表表名</param>
/// <param name="minecode">煤矿编号，全部则为空</param>
/// <param name="begintime">【必选】开始时间</param>
/// <param name="endtime">【必选】结束时间</param>
/// <param name="VIEW">区分日报表、月报表</param>
/// <returns></returns>
public DataTable GetReportData_BJ(string minecode, string devname, DateTime BegingTime, DateTime EndTime, TransJsonToTreeListModel.EnumDataType VIEW)
{
//int ts = 6;
//if (VIEW == TransJsonToTreeListModel.EnumDataType.ReportMNLRKD)
// ts = 8;
string where = "SubmitTime>='" + BegingTime.ToString("yyyy-MM-dd HH:mm:ss") + "' and SubmitTime<'" + EndTime.ToString("yyyy-MM-dd HH:mm:ss") + "'";
if (minecode != "" && minecode != null)
{
where += " and 煤矿编号='" + minecode + "'";
}
if (devname != null && devname != "")
{
where += " and 设备名='" + devname + "'";
}
where += " and 报警次数 is not null";
// var sql = string.Format(
// @"Select T.*,TT.本次最大值时间 From (
//Select 煤矿编号,煤矿名称 ,测点编号 ,设备名 ,安装位置 ,单位,Convert(varchar({0}),SubmitTime,112) TheDate,dbo.FunConvertTime(Sum(累计时间)) 累计时间,Sum(报警次数) 报警次数,Avg(本次平均值) 平均值,
//Max(本次最大值) 最大值
// From (Select * From ShineView_His.dbo.Report_Alarm T1 Left Join [ShineView_Data].[dbo].[DeviceType] T2 On T1.设备名=T2.TypeName ) T100 Where {1} And T100.Type='A'
//Group By 煤矿编号,煤矿名称 ,测点编号 ,设备名 ,安装位置 ,单位,Convert(varchar({0}),SubmitTime,112)
// ) T Left Join ShineView_His.dbo.Report_Power TT On T.最大值=TT.本次最大值", ts, where);
string sql = string.Format(@"SELECT 煤矿名称,测点编号,设备名,安装位置,单位,报警值,解报值,报警次数,shineview_data.dbo.FunConvertTime(累计时间) 累计时间,最大值,最大值时间,SubmitTime FROM shineview_his.dbo.Report_Alarm
WHERE {0}
GROUP BY 煤矿名称,测点编号,设备名,安装位置,单位,报警值,解报值,报警次数,累计时间,最大值,最大值时间,SubmitTime", where);
return(SQLDataServer.ToDataTable(sql, conn));
}
/// <summary>
/// 获取指定表的列信息
/// </summary>
/// <param name="tabname">表名</param>
/// <returns>列名，数据类型</returns>
public DataTable GetTableColumns(string tabname)
{
//SubmitTime
string sql = "select COLUMN_NAME,(case when data_type in ('char','nchar','varchar','nvarchar','text') then 'string' else DATA_TYPE end) as DATA_TYPE from information_schema.columns where TABLE_NAME='" + tabname + "' and COLUMN_NAME not in ('SubmitTime')";
//string sql = "select * from " + tabname + " where 1=2 ";
return(SQLDataServer.ToDataTable(sql, conn));
}
/// <summary>
/// 执行存储过程，放回数据表集合
/// </summary>
/// <param name="sqlc">查询参数列表</param>
/// <returns>数据表集合</returns>
public DataTableCollection ReturnDTS_ExcutePro(SqlParameter[] sqlc, string DB)
{
if (DB == "His")
{
conn = read.GetSQLConnectionHis();
}
return(SQLDataServer.ProcedureDataSet("AutoPage", sqlc, conn).Tables);
}
/// <summary>
/// 通讯异常报表
/// </summary>
/// <param name="reportable">【必须】报表表名</param>
/// <param name="minecode">煤矿编号，全部则为空</param>
/// <param name="begintime">【必选】开始时间</param>
/// <param name="endtime">【必选】结束时间</param>
/// <param name="VIEW">区分日报表、月报表</param>
/// <returns></returns>
public DataTable GetReportData_RYTXYCBB(string minecode, DateTime BegingTime, DateTime EndTime)
{
var sql = string.Format(
@"Select T1.*,T2.SimpleName,Case When TypeCode=1 Then '安全监控' When TypeCode=2 Then '人员管理' When TypeCode=3 Then '瓦斯抽放' Else '安全监控+瓦斯抽放' End
TypeName,[dbo].[FunConvertTime](Continuous) ContinuousTime From [ShineView_His].dbo.BadLog T1 Left Join [ShineView_Data].[dbo].[MineConfig] T2 On T1.MineCode=T2.MineCode
Where T1.MineCode like '%{0}%' AND LastTime>='{1}' And LastTime<'{2}' ", minecode, BegingTime.ToString("yyyy-MM-dd HH:mm:ss"), EndTime.ToString("yyyy-MM-dd HH:mm:ss"));
return(SQLDataServer.ToDataTable(sql, conn));
}
/// <summary>
/// 获取历史传输异常信息
/// </summary>
/// <param name="where">条件</param>
/// <returns></returns>
public DataTable GetRtBadLog(string where)
{
DataTable dt = new DataTable();
SqlParameter[] sqlparms =
{
new SqlParameter("@where", where)
};
dt = SQLDataServer.ProcedureDataTable("Call_RtBadLog", sqlparms, conn);
return(dt);
}
/// <summary>
/// 煤矿信息-百度地图中使用
/// </summary>
/// <param name="mineID"></param>
/// <returns></returns>
public DataTable GetMap_MineInfo(string mineID)
{
DataTable dt = new DataTable();
SqlParameter[] sqlparms =
{
new SqlParameter("@mineCode", mineID)
};
dt = SQLDataServer.ProcedureDataTable("Map_MineInfo", sqlparms, conn);
return(dt);
}
/// <summary>
/// 删除指定的表
/// </summary>
/// <param name="tablename">表名</param>
/// <returns></returns>
public bool DeleteReportTable(string tablename)
{
try
{
string sql = "drop table " + tablename;
return(SQLDataServer.OperationSQL(sql, conn));
}
catch
{
return(false);
}
}
/// <summary>
/// 增加报表
/// </summary>
/// <param name="reportname">报表名称</param>
/// <param name="tablename">报表对应的表名</param>
/// <param name="systemtype">报表所属系统类型</param>
/// <param name="remark">报表备注</param>
/// <returns></returns>
public bool InsertReport(string reportname, string tablename, int systemtype, string remark)
{
try
{
string sql = "insert into Report_Config(ReportName,DispName,SystemType,Remark) values('" + reportname + "','" + tablename + "'," + systemtype + ",'" + remark + "')";
return(SQLDataServer.OperationSQL(sql, conn));
}
catch
{
return(false);
}
}
/// <summary>
/// 删除指定编号的报表
/// </summary>
/// <param name="ID">编号</param>
/// <returns></returns>
public bool DeleteReport(int ID)
{
try
{
string sql = "delete Report_Config where ID=" + ID;
return(SQLDataServer.OperationSQL(sql, conn));
}
catch
{
return(false);
}
}
/// <summary>
/// 班超时报表
/// </summary>
/// <param name="reportable">【必须】报表表名</param>
/// <param name="minecode">煤矿编号，全部则为空</param>
/// <param name="begintime">【必选】开始时间</param>
/// <param name="endtime">【必选】结束时间</param>
/// <param name="VIEW">区分日报表、月报表</param>
/// <returns></returns>
public DataTable GetReportData_RYCSBB_B(string minecode, DateTime BegingTime, DateTime EndTime)
{
string where = "InTime>='" + BegingTime.ToString("yyyy-MM-dd HH:mm:ss") + "' and InTime<'" + EndTime.ToString("yyyy-MM-dd HH:mm:ss") + "'";
if (minecode != "" && minecode != null)
{
where += " and MineCode='" + minecode + "'";
}
var sql = string.Format(
@"Select MineCode,SimpleName,SystemType,Department,Count(1) OverCount,[ShineView_Data].[dbo].[FunConvertTime](Sum(DateDiff(Second,StartAlTime,EndAlTime))) OverTime
,Convert(varchar(8),InTime,112) TheDate
From ShineView_His.dbo.RYCSH Where {0}
Group BY MineCode,SimpleName,SystemType,Department,Convert(varchar(8),InTime,112)", where);
return(SQLDataServer.ToDataTable(sql, conn));
}
/// <summary>
/// 超时报表 2017-06-20新增
/// </summary>
/// <param name="MineCode"></param>
/// <param name="BeginTime"></param>
/// <param name="EndTime"></param>
/// <param name="flag"></param>
/// <param name="TimeSpan"></param>
/// <returns></returns>
public DataTable GetReportRYCSBB(string MineCode, DateTime BeginTime, DateTime EndTime, int flag, string TimeSpan)
{
string sql1 = "select SimpleName ,MineCode,JobCardCode,Name,Position,Department,sum( Continuous) Continuous_Tmp, " +
" max( MaxTime) MaxTime_Tmp ,min(MinTime ) MinTime_Tmp,sum( Counts ) Counts,cast(null as varchar(50)) Continuous, " +
" cast(null as varchar(50)) MaxTime,cast(null as varchar(50)) MinTime" +
" from " +
" ( " +
" select mc.SimpleName,cs.MineCode ,cs.JobCardCode ,cs.Name,cs.Position , cs.Department, " +
" Sum(DATEDIFF(s, StartAlTime, EndAlTime)) Continuous,COUNT(cs.MineCode) Counts,max(DATEDIFF(s, StartAlTime, EndAlTime)) MaxTime, " +
" min(DATEDIFF(s, StartAlTime, EndAlTime)) MinTime " +
" from ShineView_His.dbo.RYCSH cs " +
" left join ShineView_Data.dbo.MineConfig mc " +
" on cs.MineCode =mc.MineCode where 1=" + flag;
string sql2 = " GROUP BY mc.SimpleName ,cs.MineCode ,JobCardCode,Name, Position,Department " +
" union ALL " +
" select mc.SimpleName,cs.MineCode ,cs.JobCardCode ,xx.Name,xx.Position , xx.Department, " +
" Sum(DATEDIFF(s, StartAlTime, EndAlTime)) Continuous,COUNT(cs.MineCode) Counts,max(DATEDIFF(s, StartAlTime, EndAlTime)) MaxTime, " +
" min(DATEDIFF(s, StartAlTime, EndAlTime)) MinTime " +
" from ShineView_Data.dbo.RYCS cs " +
" left join ShineView_Data.dbo.MineConfig mc " +
" on cs.MineCode =mc.MineCode " +
" left join ShineView_Data.dbo.RYXX xx " +
" on xx.MineCode =cs.MineCode and cs.JobCardCode=xx.JobCardCode where EndAlTime NOT LIKE 'x%' AND EndAlTime NOT LIKE 'X%' and 1=" + flag;
string sql3 = " GROUP BY mc.SimpleName ,cs.MineCode ,cs.JobCardCode,xx.Name, xx.Position,xx.Department " +
" ) as a GROUP BY SimpleName,MineCode,JobCardCode,Name,Position,Department";
if (!string.IsNullOrEmpty(MineCode))
{
sql1 += " and mc.MineCode ='" + MineCode + "'";
sql2 += " and mc.MineCode ='" + MineCode + "'";
}
if (!string.IsNullOrEmpty(TimeSpan))
{
sql1 += " and DATEDIFF(s, StartAlTime, EndAlTime)>=" + TimeSpan;
sql2 += " and DATEDIFF(s, StartAlTime, EndAlTime)>=" + TimeSpan;
}
sql1 += " and StartAlTime>='" + BeginTime + "'";
sql2 += " and StartAlTime>='" + BeginTime + "'";
sql1 += " and StartAlTime<='" + EndTime + "'";
sql2 += " and StartAlTime<='" + EndTime + "'";
string whereData = sql1 + sql2 + sql3;
return(SQLDataServer.ToDataTable(whereData, conn));
}
/// <summary>
/// 获取指定的报表数据
/// </summary>
/// <param name="reportname">报表名称</param>
/// <param name="begintime">参数</param>
/// <param name="endtime">参数</param>
/// <returns></returns>
public bool SetReportData(string reportname, DateTime begintime, DateTime endtime)
{
try
{
string procname = "UP_" + reportname;
SqlParameter[] sqlparms =
{
new SqlParameter("@begintime ", begintime),
new SqlParameter("@endtime", endtime)
};
return(SQLDataServer.OperationProcedure(procname, sqlparms, conn));
}
catch
{
return(false);
}
}
/// <summary>
/// 加载日、月设备故障数据
/// </summary>
/// <param name="reportable">【必须】报表表名</param>
/// <param name="minecode">煤矿编号，全部则为空</param>
/// <param name="begintime">【必选】开始时间</param>
/// <param name="endtime">【必选】结束时间</param>
/// <param name="VIEW">区分日报表、月报表</param>
/// <returns></returns>
public DataTable GetReportData_SBGZ(string minecode, string devname, DateTime BegingTime, DateTime EndTime, TransJsonToTreeListModel.EnumDataType VIEW)
{
// int ts = 6;
// if (VIEW == TransJsonToTreeListModel.EnumDataType.ReportSBGZR)
// ts = 8;
string where = "HitchDatetime>='" + BegingTime.ToString("yyyy-MM-dd HH:mm:ss") + "' and HitchDatetime<'" + EndTime.ToString("yyyy-MM-dd HH:mm:ss") + "'";
if (minecode != "" && minecode != null)
{
where += " and 煤矿编号='" + minecode + "'";
}
if (devname != null && devname != "")
{
where += " and 设备名='" + devname + "'";
}
//where += "";
string sql = string.Format(
@"SELECT gz.mineCode 煤矿编号,MC.SimpleName 煤矿名称, sensorNum 测点编号,DT.TypeName 设备名,place 安装位置,累计次数,shineview_data.dbo.FunConvertTime(累计时间) 累计时间 FROM
(SELECT mineCode,sensorNum,type,place,Count(*) 累计次数,Sum(DateDiff(SS,HitchDatetime,HitchEndDatetime)) 累计时间 FROM ShineView_His.dbo.AQGZ
WHERE {0}
GROUP BY mineCode,sensorNum,type,place
) GZ
LEFT JOIN [ShineView_Data].[dbo].[DeviceType] DT ON GZ.Type=DT.TypeCode
LEFT JOIN [ShineView_Data].[dbo].[MineConfig] MC ON GZ.mineCode=MC.MineCode", where);
// var sql =
// string.Format(
// @"Select 煤矿编号,煤矿名称 ,测点编号 ,设备名 ,安装位置,dbo.FunConvertTime(Sum(累计时间)) 累计时间,Count(*) 累计次数,Convert(varchar({0}),HitchDatetime,112) TheDate From (
//SELECT T1.MineCode 煤矿编号,T2.SimpleName 煤矿名称
//,SensorNum 测点编号
//,Place 安装位置
//,T3.TypeName 设备名,
//DateDiff(SS,HitchDatetime,HitchEndDatetime) 累计时间,HitchDatetime
//FROM ShineView_His.dbo.AQGZ T1 Left Join [ShineView_Data].[dbo].[MineConfig] T2 On T1.MineCode=T2.MineCode
// Left Join [ShineView_Data].[dbo].[DeviceType] T3 On T1.Type=T3.TypeCode
//) T Where {1} Group By 煤矿编号,煤矿名称 ,测点编号 ,设备名 ,安装位置 ,Convert(varchar({0}),HitchDatetime,112)", ts, where);
return(SQLDataServer.ToDataTable(sql, conn));
}
/// <summary>
/// 日、月超时报表
/// </summary>
/// <param name="reportable">【必须】报表表名</param>
/// <param name="minecode">煤矿编号，全部则为空</param>
/// <param name="begintime">【必选】开始时间</param>
/// <param name="endtime">【必选】结束时间</param>
/// <param name="VIEW">区分日报表、月报表</param>
/// <returns></returns>
public DataTable GetReportData_RYCSBB(string minecode, DateTime BegingTime, DateTime EndTime, TransJsonToTreeListModel.EnumDataType VIEW)
{
int ts = 6;
if (VIEW == TransJsonToTreeListModel.EnumDataType.ReportRYCSBB_R)
{
ts = 8;
}
string where = "StartAlTime>='" + BegingTime.ToString("yyyy-MM-dd HH:mm:ss") + "' and StartAlTime<'" + EndTime.ToString("yyyy-MM-dd HH:mm:ss") + "'";
if (minecode != "" && minecode != null)
{
where += " and MineCode='" + minecode + "'";
}
var sql = string.Format(
@" SELECT [MineCode] ,[SimpleName],[Name],[Position],[Department],[InTime],[StartAlTime],[EndAlTime]
,dbo.FunConvertTime(Datediff(SECOND,[StartAlTime],[EndAlTime])) continuoustime
FROM ShineView_His.[dbo].[RYCSH] where 1=1 and {1}", ts, where);
return(SQLDataServer.ToDataTable(sql, conn));
}
/// <summary>
/// 获取设备配置信息
/// </summary>
/// <param name="where">查询条件</param>
/// <param name="devtype">设备类型</param>
/// <returns></returns>
public DataTable GetDeviceInfo(string where, string devtype)
{
DataTable dt = new DataTable();
if (devtype != "" && devtype != null)
{
where = "d.type='" + devtype + "' and " + where;
}
SqlParameter[] sqlparms =
{
new SqlParameter("@where", where)
};
if (devtype == "D")
{
dt = SQLDataServer.ProcedureDataTable("Call_DeviceInfo_D", sqlparms, conn);
}
else
{
dt = SQLDataServer.ProcedureDataTable("Call_DeviceInfo", sqlparms, conn);
}
return(dt);
}
public DataTableCollection ReturnDTS_ExcutePro_MNLDay(SqlParameter[] sqlc)
{
conn = read.GetSQLConnectionHis();
return(SQLDataServer.ProcedureDataSet("GetMNLDay_AutoPage", sqlc, conn).Tables);
}
public DataTableCollection GetMnlMinute_Curve(SqlParameter[] sqlc)
{
conn = read.GetSQLConnectionHis();
return(SQLDataServer.ProcedureDataSet("GetMnlMinute_Curve", sqlc, conn).Tables);
}
/// <summary>
/// 向数据库中存文件
/// </summary>
/// <param name="sql"></param>
/// <param name="FileContent"></param>
/// <returns></returns>
public bool InsertFileSql(string sql, byte[] FileContent)
{
return(SQLDataServer.InsertFile(sql, FileContent, conn));
}
public int ExecSql(string sql)
{
return(SQLDataServer.ExecSql(sql, conn));
}
public bool ExcuteSqls(List <string> sql)
{
return(SQLDataServer.OperationSQLs(sql, conn));
}
public bool ExcuteSql(string sql)
{
return(SQLDataServer.OperationSQL(sql, conn));
}
public DataSet ReturnDs(string sql)
{
return(SQLDataServer.ToDataSet(sql, conn));
}
public DataTable ReturnData(string sql, string con)
{
return(SQLDataServer.ToDataTable(sql, con));
}
/// <summary>
/// 查询煤矿列表
/// </summary>
/// <returns></returns>
public DataTable GetSystemConfigList()
{
string sql = @"Select C.*,sc.TypeName, M.SimpleName,M.MineCode MyMineCode From SystemConfig C Left Join MineConfig M On C.MineCode = M.ID left join SystemTypesInfo sc on C.TypeCode=sc.TypeCode order by M.MineCode ";
return(SQLDataServer.ToDataTable(sql, conn));
}
/// <summary>
/// 获取所有固定格式报表
/// </summary>
/// <returns></returns>
public DataTable GetFixedReport()
{
string sql = "select * from Report_config where SystemType=0";
return(SQLDataServer.ToDataTable(sql, conn));
}
