public bool DeleteByRoom(string roomGuid)
{
bool result = false;
List<Parameter> parameters = new List<Parameter>();
DatabaseHibernate hibernate = new DatabaseHibernate();
string sql = string.Format("select {0} from e_machine as t where [t].[room_id] = '{1}'", this.Asterisk("[t]."), roomGuid);
List<object[]> values = hibernate.Read(Variable.Link, sql, parameters);
List<Machine> results = this.Parse(values);
if ((results != null) && (results.Count > 0))
{
for (int i = 0; i < results.Count; i++)
{
result = this.Delete(results[i]);
if (!result)
{
break;
}
}
}
else
{
result = true;
}
return result;
}
/// <summary>
/// 总数
/// </summary>
/// <returns>总数</returns>
public int Count()
{
int result = -1;
string sql = "select count([t].[guid]) from m_user as t";
List<Parameter> parameters = new List<Parameter>();
DatabaseHibernate hibernate = new DatabaseHibernate();
List<object[]> values = hibernate.Read(Variable.Link, sql, parameters);
if ((values != null) && (values.Count == 1))
{
object[] moduleValues = values[0];
result = DatabaseHibernate.ParseInt(moduleValues[0]);
}
return result;
}
public List<User> QueryByInsertUser(string insertUserId)
{
List<User> results = null;
string sql = string.Format("select {0} from m_user as t where[t].[insert_user_id] = '{1}'", this.Asterisk("[t]."), insertUserId);
List<Parameter> parameters = new List<Parameter>();
parameters.Clear();
DatabaseHibernate hibernate = new DatabaseHibernate();
List<object[]> values = hibernate.Read(Variable.Link, sql, parameters);
results = this.Parse(values);
return results;
}
/// <summary>
/// 通过主键查询
/// </summary>
/// <param name="guid">主键</param>
/// <returns>用户</returns>
public User QueryByGuid(string guid)
{
User result = null;
string sql = string.Format("select {0} from m_user as t where [t].[guid] = :guid", this.Asterisk("[t]."));
List<Parameter> parameters = new List<Parameter>();
parameters.Add(new Parameter("guid", DatabaseHibernate.Parameter(guid)));
DatabaseHibernate hibernate = new DatabaseHibernate();
List<object[]> values = hibernate.Read(Variable.Link, sql, parameters);
if ((values != null) && (values.Count == 1))
{
result = new User();
object[] moduleValues = values[0];
result = this.Parse(moduleValues);
}
return result;
}
/// <summary>
/// 分页查询
/// </summary>
/// <param name="page">页码</param>
/// <param name="rows">每页行数</param>
/// <param name="total">总数</param>
/// <returns>用户集合</returns>
public List<User> Query(int page, int rows, ref int total)
{
List<User> results = new List<User>();
string sql = string.Format("select {0} from m_user as t order by [t].[name]", this.Asterisk("[t]."));
List<Parameter> parameters = new List<Parameter>();
DatabaseHibernate hibernate = new DatabaseHibernate();
string countSql = string.Format("select count(*) from ({0})", sql);
parameters.Clear();
List<object[]> values = hibernate.Read(Variable.Link, countSql, parameters);
if ((values != null) && (values.Count == 1))
{
object[] moduleValues = values[0];
total = DatabaseHibernate.ParseInt(moduleValues[0]);
}
string querySql = sql;
parameters.Clear();
values = hibernate.Read(Variable.Link, querySql, parameters, page, rows);
results = this.Parse(values);
return results;
}
/// <summary>
/// 分页查询
/// </summary>
/// <param name="page">页码</param>
/// <param name="rows">每页行数</param>
/// <param name="total">总数</param>
/// <param name="userGuid"></param>
/// <returns>用户和房间关系集合</returns>
public List<UserRoom> QueryByUser(int page, int rows, ref int total, string userGuid)
{
List<UserRoom> results = new List<UserRoom>();
string sql = string.Format("select {0} from e_user_room as t, e_room as u where [t].[room_id] = [u].[guid] and [t].[user_id] = '{1}' order by [u].[name]", this.Asterisk("[t]."), userGuid);
List<Parameter> parameters = new List<Parameter>();
DatabaseHibernate hibernate = new DatabaseHibernate();
string countSql = string.Format("select count(*) from ({0})", sql);
parameters.Clear();
List<object[]> values = hibernate.Read(Variable.Link, countSql, parameters);
if ((values != null) && (values.Count == 1))
{
object[] moduleValues = values[0];
total = DatabaseHibernate.ParseInt(moduleValues[0]);
}
string querySql = sql;
parameters.Clear();
values = hibernate.Read(Variable.Link, querySql, parameters, page, rows);
results = this.Parse(values);
return results;
}
public List<Detector> QueryNormalDataCacheByMachine(string machineId, DateTime beginDate, DateTime endDate)
{
List<Detector> results = new List<Detector>();
List<Parameter> parameters = new List<Parameter>();
DatabaseHibernate hibernate = new DatabaseHibernate();
string sql = string.Format("select {0}, [v].[value], [v].[refresh_time] from (select t_a.* from e_detector as t_a left join e_machine as u_a on [t_a].[machine_id] = [u_a].[guid]) as t, e_data_cache as v where [t].[guid] = [v].[detector_id] and ([v].[refresh_time] >= :beginDate and [v].[refresh_time] <= :endDate) and [v].[update_user_id] >= :type and [t].[machine_id] = '{1}' order by [v].[refresh_time] desc, [t].[serial]", this.Asterisk("[t]."), machineId);
parameters.Add(new Parameter("beginDate", DatabaseHibernate.Parameter(beginDate)));
parameters.Add(new Parameter("endDate", DatabaseHibernate.Parameter(endDate)));
parameters.Add(new Parameter("update_user_id", DatabaseHibernate.Parameter("UploadInstruction")));
List<object[]> values = hibernate.Read(Variable.Link, sql, parameters);
results = this.ParseDataCache(values);
return results;
}
public List<Detector> QueryDataCacheByMachine(string machineId)
{
List<Detector> results = new List<Detector>();
List<Parameter> parameters = new List<Parameter>();
DatabaseHibernate hibernate = new DatabaseHibernate();
string sql = string.Format("select {0}, [v].[value], [v].[refresh_time] from (select t_a.* from e_detector as t_a left join e_machine as u_a on [t_a].[machine_id] = [u_a].[guid]) as t, e_data_cache as v where [t].[guid] = [v].[detector_id] and [v].[newest] = 1 and [t].[machine_id] = '{1}' order by [t].[serial], [v].[refresh_time] desc", this.Asterisk("[t]."), machineId);
List<object[]> values = hibernate.Read(Variable.Link, sql, parameters);
results = this.ParseDataCache(values);
return results;
}
public Machine QueryByNameOrIp(string name, string roomId, string ip)
{
Machine result = null;
List<Parameter> parameters = new List<Parameter>();
DatabaseHibernate hibernate = new DatabaseHibernate();
string sql = string.Format("select {0} from e_machine as t where ([t].[name] = :name and [t].[room_id] = :roomId) or ([t].[ip] = :ip)", this.Asterisk("[t]."));
parameters.Add(new Parameter("name", name));
parameters.Add(new Parameter("roomId", roomId));
parameters.Add(new Parameter("ip", ip));
List<object[]> values = hibernate.Read(Variable.Link, sql, parameters);
List<Machine> results = this.Parse(values);
if ((results != null) && (results.Count > 0))
{
result = results[0];
}
return result;
}
public Detector QueryByGuid(string guid)
{
Detector result = null;
List<Parameter> parameters = new List<Parameter>();
DatabaseHibernate hibernate = new DatabaseHibernate();
string sql = string.Format("select {0} from e_detector as t where [t].[guid] = '{1}'", this.Asterisk("[t]."), guid);
List<object[]> values = hibernate.Read(Variable.Link, sql, parameters);
List<Detector> detectors = this.Parse(values);
if ((detectors != null) && (detectors.Count > 0))
{
result = detectors[0];
}
return result;
}
public List<MessageCache> QueryByMachine(string machineId, DateTime beginDate, DateTime endDate)
{
List<MessageCache> results = new List<MessageCache>();
List<Parameter> parameters = new List<Parameter>();
DatabaseHibernate hibernate = new DatabaseHibernate();
string sql = string.Format("select {0} from e_message_cache as t where ([t].[send_time] >= :beginDate and [t].[send_time] <= :endDate) and [t].[machine_id] = '{1}' order by [t].[send_time] desc", this.Asterisk("[t]."), machineId);
parameters.Add(new Parameter("beginDate", DatabaseHibernate.Parameter(beginDate)));
parameters.Add(new Parameter("endDate", DatabaseHibernate.Parameter(endDate)));
List<object[]> values = hibernate.Read(Variable.Link, sql, parameters);
results = this.Parse(values);
return results;
}
public DetectorType QueryByNameOrCode(string name, byte code)
{
DetectorType result = null;
List<Parameter> parameters = new List<Parameter>();
DatabaseHibernate hibernate = new DatabaseHibernate();
string sql = string.Format("select {0} from e_detector_type as t where (([t].[name] = :name) or ([t].[code] = :code))", this.Asterisk("[t]."));
parameters.Add(new Parameter("name", DatabaseHibernate.Parameter(name)));
parameters.Add(new Parameter("code", DatabaseHibernate.Parameter(code)));
List<object[]> values = hibernate.Read(Variable.Link, sql, parameters);
List<DetectorType> results = this.Parse(values);
if ((results != null) && (results.Count > 0))
{
result = results[0];
}
return result;
}
public Room QueryByName(string name)
{
Room result = null;
string sql = string.Format("select {0} from e_room as t where ucase([t].[name]) = ucase(:name)", this.Asterisk("[t]."));
List<Parameter> parameters = new List<Parameter>();
parameters.Add(new Parameter("name", DatabaseHibernate.Parameter(name)));
DatabaseHibernate hibernate = new DatabaseHibernate();
List<object[]> values = hibernate.Read(Variable.Link, sql, parameters);
if ((values != null) && (values.Count == 1))
{
result = new Room();
object[] moduleValues = values[0];
result = this.Parse(moduleValues);
}
return result;
}
public Room QueryByGuid(string guid)
{
Room result = null;
string sql = string.Format("select {0} from e_room as t where [t].[guid] = '{1}'", this.Asterisk("[t]."), guid);
List<Parameter> parameters = new List<Parameter>();
DatabaseHibernate hibernate = new DatabaseHibernate();
List<object[]> values = hibernate.Read(Variable.Link, sql, parameters);
if ((values != null) && (values.Count == 1))
{
result = new Room();
object[] moduleValues = values[0];
result = this.Parse(moduleValues);
}
return result;
}
public List<Machine> QueryByRoom(string roomId)
{
List<Machine> results = new List<Machine>();
List<Parameter> parameters = new List<Parameter>();
DatabaseHibernate hibernate = new DatabaseHibernate();
string sql = string.Format("select {0} from e_machine as t where [t].[room_id] = :room_id", this.Asterisk("[t]."));
parameters.Add(new Parameter("room_id", roomId));
List<object[]> values = hibernate.Read(Variable.Link, sql, parameters);
results = this.Parse(values);
return results;
}
/// <summary>
/// 通过帐户查询
/// </summary>
/// <param name="account">帐号</param>
/// <returns>用户</returns>
public User SelectByAccount(string account)
{
User result = null;
string sql = string.Format("select {0} from m_user as t where ucase([t].[account]) = ucase(:account)", this.Asterisk("[t]."));
List<Parameter> parameters = new List<Parameter>();
parameters.Add(new Parameter("account", DatabaseHibernate.Parameter(account)));
DatabaseHibernate hibernate = new DatabaseHibernate();
List<object[]> values = hibernate.Read(Variable.Link, sql, parameters);
if ((values != null) && (values.Count == 1))
{
result = new User();
object[] moduleValues = values[0];
result = this.Parse(moduleValues);
}
return result;
}
public List<Module.Environmental.Detector> QueryByMachine(string machineId)
{
List<Detector> results = new List<Detector>();
List<Parameter> parameters = new List<Parameter>();
DatabaseHibernate hibernate = new DatabaseHibernate();
string sql = string.Format("select {0} from e_detector as t left join e_machine as u on [t].[machine_id] = [u].[guid] where [t].[machine_id] = '{1}' order by [t].[serial]", this.Asterisk("[t]."), machineId);
List<object[]> values = hibernate.Read(Variable.Link, sql, parameters);
results = this.Parse(values);
return results;
}
public Machine QueryByIp(string ip)
{
Machine result = null;
List<Parameter> parameters = new List<Parameter>();
DatabaseHibernate hibernate = new DatabaseHibernate();
string sql = string.Format("select {0} from e_machine as t, e_room as u where [t].[room_id] = [u].[guid] and [t].[ip] = '{1}'", this.Asterisk("[t]."), ip);
List<object[]> values = hibernate.Read(Variable.Link, sql, parameters);
List<Machine> results = this.Parse(values);
if ((results != null) && (results.Count > 0))
{
result = results[0];
}
return result;
}