//一次获取所有板的最新数据
public DataTable GetAllRealTimeData()
{
List <string> BoardNumberList;
CData_PointTable dtp = new CData_PointTable();
BoardNumberList = dtp.GetAllBoardNumber();
DataTable AllRealTimeData = new DataTable();
//if (BoardNumberList == null)
// return AllRealTimeData;//返回错误信息
string sql;
//需改为从实时表中读取数据
/*
* foreach (string bn in BoardNumberList)
* {
* //sql = "select top 1 * from td_datasmallcon where Code='" + bn + "' order by RecvDt DESC";
* //DataTable dt = DataBus_DAStaticFunctions.querySql(sql, DataBus_DAStaticFunctions.DATABASECON);
* //mysql
* sql = "select * from td_datasmallcon where Code='" + bn + "' order by RecvDt DESC limit 1";//mysql不能用top 1*
* DataTable dt = DataBus_MySqlHelper.QuerySql(sql);
* AllRealTimeData.Merge(dt);
* }
* */
sql = "select * from daq_read ";
DataTable dt = DataBus_MySqlHelper.QuerySql(sql);
return(dt);
}
//根据表名，id获取write pointid
public string GetWritePointID(string type, string id)
{
string PointID = "";
string sql = "select * from " + type + " where id = " + id;
DataTable dt = new DataTable();
try
{
dt = DataBus_MySqlHelper.QuerySql(sql);
if (type == "Actuator")
{
PointID = dt.Rows[0]["SendPTID"].ToString();
}
else if (type == "Fan" || type == "Pump")
{
PointID = dt.Rows[0]["FrequencySetPTID"].ToString();
}
else
{
PointID = dt.Rows[0]["PointID"].ToString();
}
}
catch
{
}
return(PointID);
}
public DataTableCollection GetAllTypeName()
{
string sql = "select * from IDE_TypeName";
DataTableCollection tpn = DataBus_MySqlHelper.QueryForDataTableCollection(sql);
return(tpn);
}
public DataTable MySqlQuery(string sql)
{
DataTable dt = new DataTable();
dt = DataBus_MySqlHelper.QuerySql(sql);
return(dt);
}
//下发ONOFF指令
public void SendOnOffData(string DeviceID, string state)
{
if (state != "" && state != null)
{
string savesql = "update td_datasmallwrite set ONOFF = '" + state + "', Handle = '1' where Address ='" + DeviceID + "' ";
//DataBus_DAStaticFunctions.exeSql(savesql, "connSys");
DataBus_MySqlHelper.ExecuteSql(savesql); // mysql
}
}
//修改数据库 buttoncheck指令 1开 0关
public bool SendButtonCheckData(string RelatedPort, string PortBind, string DeviceID, string state)
{
if (RelatedPort == null || PortBind == null || DeviceID == null)
{
return(false);
}
string savesql = "update daq_control set " + RelatedPort + " = '" + state + "', " + PortBind + " = '1' where Address ='" + DeviceID + "' ";
int i = DataBus_MySqlHelper.ExecuteSql(savesql);
return(true);
}
//根据板号获取最新一条记录
public DataTable GetNewRecordByBroadNumber(string BroadNumber)
{
DataTable dt = new DataTable();
//string sql = "select top 1 * from td_datasmallcon where Code='" + BroadNumber + "' order by RecvDt DESC";
//dt = DataBus_DAStaticFunctions.querySql(sql, DataBus_DAStaticFunctions.DATABASECON);
//mysql
string sql = "select * from daq_read where Address='" + BroadNumber + "' ";
dt = DataBus_MySqlHelper.QuerySql(sql);
return(dt);
}
//根据ID获取project表
public DataTable GetProjectByID(int id, int GETALLDATA)
{
string sql = "select name, id from IDE_Project";
if (id != GETALLDATA)
{
// sql += " where id = '" + id + "'";
sql += " where id = " + id;
}
DataTable dt = new DataTable();
dt = DataBus_MySqlHelper.QuerySql(sql);
return(dt);
}
public DataTable GetAllRealTimeData()
{
string sql;
sql = "select * from daq_control ";
DataTable dt = new DataTable();
try
{
dt = DataBus_MySqlHelper.QuerySql(sql);
}
catch
{
}
return(dt);
}
//BroadNumber 板号 maxNum 能显示的最大条数
public int GetHistoryDataCountByTimeZone(string BroadNumber, DateTime timeBegin, DateTime timeEnd, string tableName)
{
int num = 0;
string tb = timeBegin.ToString("yyyy-MM-dd HH:mm:ss");
string te = timeEnd.ToString("yyyy-MM-dd HH:mm:ss");
DataTable dt = new DataTable();
try
{
string countsql = " select count(*) from " + tableName + " where Address='" + BroadNumber + "' and TimeStamp > '" + tb + "' and TimeStamp < '" + te + "'";
dt = DataBus_MySqlHelper.QuerySql(countsql);
num = Convert.ToInt32(dt.Rows[0][0].ToString());
}
catch { }
return(num);
}
//获取所有设备名字
public DataTable GetAllName(string type)
{
DataTable PointTable = new DataTable();
//string sql = "select id,name from " + type + " order by id asc";
string sql = "select id,name from ide_point " + " where type = " + type + " order by id asc";
try
{
PointTable = DataBus_MySqlHelper.QuerySql(sql);
}
catch (System.Exception e)
{
string errText = e.Message.ToString();
return(null);
}
return(PointTable);
}
public DataTable GetHistoryDataByTimeZone(string BroadNumber, DateTime timeBegin, DateTime timeEnd, string tableName)
{
DataTable dt = new DataTable();
try
{
string tb = timeBegin.ToString("yyyy-MM-dd HH:mm:ss");
string te = timeEnd.ToString("yyyy-MM-dd HH:mm:ss");
//最多显示1000条数据，如果超过，改为查询另一张表
string sql = " select * from " + tableName + " where Address='" + BroadNumber + "' and TimeStamp > '" + tb + "' and TimeStamp < '" + te + "' order by TimeStamp DESC";
//string countsql = " select count(*) from daq_read_history where Address='" + BroadNumber + "' and TimeStamp > '" + timeBegin.ToString() + "' and TimeStamp < '" + timeEnd.ToString() + "'";
//string sql = " select * from daq_read_history order by TimeStamp DESC limit 1000 ";
dt = DataBus_MySqlHelper.QuerySql(sql);
}
catch { }
return(dt);
}
//根据表名，id获取pointid
public string GetPointID(string type, string id)
{
string PointID = "";
string sql = "select * from " + type + " where id = " + id;
DataTable dt = new DataTable();
try
{
//dt = DataBus_DAStaticFunctions.querySql(sql, DataBus_DAStaticFunctions.DATABASECON);
//mysql
dt = DataBus_MySqlHelper.QuerySql(sql);
PointID = dt.Rows[0]["PointID"].ToString();
}
catch
{
}
return(PointID);
}
//根据ID type获取名字
public string GetOneName(string id, string type)
{
DataTable PointTable = new DataTable();
string sql = "select name from " + "ide_" + type + " where id = " + id + "order by id desc";
try
{
PointTable = DataBus_MySqlHelper.QuerySql(sql);
}
catch (System.Exception e)
{
string errText = e.Message.ToString();
return(null);
}
string name = PointTable.Rows[0]["name"].ToString();
return(name);
}
//获取整个点表
public DataTable GetPointTable()
{
DataTable PointTable = new DataTable();
try
{
string sql = "select * from IDE_Point";
//获取point表
//PointTable = DataBus_DAStaticFunctions.querySql("select * from BSTAR.dbo.Point", DataBus_DAStaticFunctions.DATABASECON);
//mysql
PointTable = DataBus_MySqlHelper.QuerySql(sql);
}
catch (System.Exception e)
{
string errText = e.Message.ToString();
return(null);
}
return(PointTable);
}
//修改数据库下发控制指令
public bool SendControlData(string Data, string RelatedPort, string PortBind, string DeviceID)
{
if (RelatedPort == null || PortBind == null || DeviceID == null)
{
return(false);
}
//增加对于PID的特殊处理 AO_AUTOMANUAL
if (RelatedPort.Substring(0, 2) == "AO")
{
string automaunal_str = RelatedPort + "_AUTOMANUAL";
string querysql = "select * from daq_control where Address ='" + DeviceID + "' ";
DataTable dt = DataBus_MySqlHelper.QuerySql(querysql);
if (dt.Rows[0][automaunal_str].ToString() == "1")
{
return(false);
}
}
//string savesql = "update td_datasmallwrite set " + RelatedPort + " = '" + Data + "', " + PortBind + " = '1', Handle = '1' where code ='" + DeviceID + "' ";
string savesql = "update daq_control set " + RelatedPort + " = '" + Data + "', " + PortBind + " = '1' where Address ='" + DeviceID + "' ";
int i = DataBus_MySqlHelper.ExecuteSql(savesql);
return(true);
//DataBus_MySqlHelper.ExecuteSql(savesql); mysql
}
public int MySqlExe(string sql)
{
int rows = DataBus_MySqlHelper.ExecuteSql(sql);
return(rows);
}
public static MySqlConnection GetConn()
{
MySqlConnection conn = DataBus_MySqlHelper.getConn();
return(conn);
}
