GitBucket
Newer
using Casic.Birmm.RbFreqStandMeasure.R_DataBase.Model;
using Casic.Birmm.RbFreqStandMeasure.Tools;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Text;
namespace Casic.Birmm.RbFreqStandMeasure.R_DataBase.Service.Impl
{
class CounterDataServiceImpl:CounterDataService
{
public long add(long deviceId, string outValue, string value, string sensitivity, string logTime, string detecType)
{
int iRetval = -1;
long dataId = -1;
try
{
if (DbConnectService.mySqlConnect.State == ConnectionState.Closed)
{
LogHelper.WriteErrorLog(MethodBase.GetCurrentMethod().DeclaringType, "addCounterParam : 数据库链接断开");
iRetval = DbConnectService.openDb();
if (iRetval != 0)
{
LogHelper.WriteErrorLog(MethodBase.GetCurrentMethod().DeclaringType, "重连失败！");
return iRetval;
}
}
//先查询是否有历史数据
string sQry = "select ID from r_counter_data where DEVICE_ID=" + deviceId + " and OUT_VALUE = '" + outValue + "'";
MySqlCommand cmd = new MySqlCommand(sQry, DbConnectService.mySqlConnect);
bool isUpdated = false;
using (MySqlDataReader aReader = cmd.ExecuteReader())
{
if (aReader.Read())
{
dataId = Convert.ToInt64(aReader.GetString(0));
aReader.Close();
sQry = "update r_counter_data set VALUE=@VALUE, SENSITIVITY=@SENSITIVITY, LOG_TIME=@LOG_TIME, DETEC_TYPE=@DETEC_TYPE " +
" where ID=" + dataId;
cmd = new MySqlCommand(sQry, DbConnectService.mySqlConnect);
cmd.Parameters.Add("@VALUE", MySqlDbType.String, 255).Value = value;
cmd.Parameters.Add("@SENSITIVITY", MySqlDbType.String, 255).Value = sensitivity;
cmd.Parameters.Add("@LOG_TIME", MySqlDbType.String, 20).Value = logTime;
cmd.Parameters.Add("@DETEC_TYPE", MySqlDbType.String, 5).Value = detecType;
cmd.ExecuteNonQuery();
isUpdated = true;
}
aReader.Close();
}
if (!isUpdated)
{
sQry = "INSERT INTO r_counter_data (DEVICE_ID, OUT_VALUE, VALUE, SENSITIVITY, LOG_TIME, DETEC_TYPE)" +
"values(@DEVICE_ID,@OUT_VALUE,@VALUE,@SENSITIVITY,@LOG_TIME,@DETEC_TYPE)";
cmd = new MySqlCommand(sQry, DbConnectService.mySqlConnect);
cmd.Parameters.Add("@DEVICE_ID", MySqlDbType.Int64, 20).Value = deviceId;
cmd.Parameters.Add("@OUT_VALUE", MySqlDbType.String, 255).Value = outValue;
cmd.Parameters.Add("@VALUE", MySqlDbType.String, 255).Value = value;
cmd.Parameters.Add("@SENSITIVITY", MySqlDbType.String, 255).Value = sensitivity;
cmd.Parameters.Add("@LOG_TIME", MySqlDbType.String, 20).Value = logTime;
cmd.Parameters.Add("@DETEC_TYPE", MySqlDbType.String, 5).Value = detecType;
cmd.ExecuteNonQuery();
sQry = "SELECT max(ID) from r_counter_data";
cmd = new MySqlCommand(sQry, DbConnectService.mySqlConnect);
using (MySqlDataReader aReader = cmd.ExecuteReader())
{
if (aReader.Read()) dataId = Convert.ToInt64(aReader.GetString(0));
aReader.Close();
}
}
cmd.Dispose();
iRetval = 0;
}
catch (MySqlException ex)
{
LogHelper.WriteErrorLog(MethodBase.GetCurrentMethod().DeclaringType, "addCounter : " + ex.Message);
return -1;
}
return dataId;
}
public int update(long deviceId, string outValue, string value, string sensitivity, DateTime logTime, string detecType)
{
int iRetval = -1;
try
{
if (DbConnectService.mySqlConnect.State == ConnectionState.Closed)
{
LogHelper.WriteErrorLog(MethodBase.GetCurrentMethod().DeclaringType, "addCounterParam : 数据库链接断开");
iRetval = DbConnectService.openDb();
if (iRetval != 0)
{
LogHelper.WriteErrorLog(MethodBase.GetCurrentMethod().DeclaringType, "重连失败！");
return iRetval;
}
}
string sQry = "SELECT * FROM r_counter_data where DEVICE_ID=@DEVICE_ID AND OUT_VALUE=@OUT_VALUE";
MySqlCommand cmd = new MySqlCommand(sQry, DbConnectService.mySqlConnect);
cmd.Parameters.Add("@DEVICE_ID", MySqlDbType.Int64, 20).Value = deviceId;
cmd.Parameters.Add("@OUT_VALUE", MySqlDbType.String, 255).Value = outValue;
cmd.ExecuteNonQuery();
using (MySqlDataReader aReader = cmd.ExecuteReader())
{
if (aReader.Read())
{
sQry = "update r_counter_data set VALUE=@VALUE, SENSITIVITY=@SENSITIVITY, LOG_TIME@LOG_TIME, DETEC_TYPE=@DETEC_TYPE " +
" where DEVICE_ID=@DEVICE_ID AND OUT_VALUE=@OUT_VALUE";
cmd = new MySqlCommand(sQry, DbConnectService.mySqlConnect);
cmd.Parameters.Add("@DEVICE_ID", MySqlDbType.Int64, 20).Value = deviceId;
cmd.Parameters.Add("@OUT_VALUE", MySqlDbType.String, 255).Value = outValue;
cmd.Parameters.Add("@VALUE", MySqlDbType.String, 0).Value = value;
cmd.Parameters.Add("@SENSITIVITY", MySqlDbType.String, 0).Value = sensitivity;
cmd.Parameters.Add("@LOG_TIME", MySqlDbType.DateTime, 0).Value = logTime;
cmd.Parameters.Add("@DETEC_TYPE", MySqlDbType.DateTime, 0).Value = detecType;
cmd.ExecuteNonQuery();
}
else
{
sQry = "INSERT INTO r_counter_data (DEVICE_ID, OUT_VALUE, VALUE, SENSITIVITY, LOG_TIME, DETEC_TYPE)" +
"values(@DEVICE_ID,@OUT_VALUE,@VALUE,@SENSITIVITY,@LOG_TIME,@DETEC_TYPE)";
cmd = new MySqlCommand(sQry, DbConnectService.mySqlConnect);
cmd.Parameters.Add("@DEVICE_ID", MySqlDbType.Int64, 20).Value = deviceId;
cmd.Parameters.Add("@OUT_VALUE", MySqlDbType.String, 255).Value = outValue;
cmd.Parameters.Add("@VALUE", MySqlDbType.String, 0).Value = value;
cmd.Parameters.Add("@SENSITIVITY", MySqlDbType.String, 0).Value = sensitivity;
cmd.Parameters.Add("@LOG_TIME", MySqlDbType.DateTime, 0).Value = logTime;
cmd.Parameters.Add("@DETEC_TYPE", MySqlDbType.DateTime, 0).Value = detecType;
cmd.ExecuteNonQuery();
}
aReader.Close();
}
cmd.Dispose();
iRetval = 0;
}
catch (MySqlException ex)
{
LogHelper.WriteErrorLog(MethodBase.GetCurrentMethod().DeclaringType, "addCounterParam : " + ex.Message);
iRetval = -1;
}
return iRetval;
}
public int updateById(long id, string outValue, string value, string sensitivity, DateTime logTime, string detecType)
{
int iRetval = -1;
try
{
if (DbConnectService.mySqlConnect.State == ConnectionState.Closed)
{
LogHelper.WriteErrorLog(MethodBase.GetCurrentMethod().DeclaringType, "addCounterParam : 数据库链接断开");
iRetval = DbConnectService.openDb();
if (iRetval != 0)
{
LogHelper.WriteErrorLog(MethodBase.GetCurrentMethod().DeclaringType, "重连失败！");
return iRetval;
}
}
string sQry = "update r_counter_data set OUT_VALUE=@OUT_VALUE, VALUE=@VALUE, SENSITIVITY=@SENSITIVITY, LOG_TIME@LOG_TIME, DETEC_TYPE=@DETEC_TYPE " +
" where ID=@ID";
MySqlCommand cmd = new MySqlCommand(sQry, DbConnectService.mySqlConnect);
cmd.Parameters.Add("@ID", MySqlDbType.Int64, 20).Value = id;
cmd.Parameters.Add("@OUT_VALUE", MySqlDbType.String, 255).Value = outValue;
cmd.Parameters.Add("@VALUE", MySqlDbType.String, 0).Value = value;
cmd.Parameters.Add("@SENSITIVITY", MySqlDbType.String, 0).Value = sensitivity;
cmd.Parameters.Add("@LOG_TIME", MySqlDbType.DateTime, 0).Value = logTime;
cmd.Parameters.Add("@DETEC_TYPE", MySqlDbType.DateTime, 0).Value = detecType;
cmd.ExecuteNonQuery();
cmd.Dispose();
iRetval = 0;
}
catch (MySqlException ex)
{
LogHelper.WriteErrorLog(MethodBase.GetCurrentMethod().DeclaringType, "addCounterParam : " + ex.Message);
iRetval = -1;
}
return iRetval;
}
public int delete(long deviceId, string outValue,string logTime)
{
int iRetval = -1;
try
{
if (DbConnectService.mySqlConnect.State == ConnectionState.Closed)
{
LogHelper.WriteErrorLog(MethodBase.GetCurrentMethod().DeclaringType, "deleteCounter : 数据库链接断开");
iRetval = DbConnectService.openDb();
if (iRetval != 0)
{
LogHelper.WriteErrorLog(MethodBase.GetCurrentMethod().DeclaringType, "重连失败！");
return iRetval;
}
}
string sQry = "delete from r_counter_data where DEVICE_ID=@DEVICE_ID and OUT_VALUE=@OUT_VALUE and LOG_TIME=@LOG_TIME";
MySqlCommand cmd = new MySqlCommand(sQry, DbConnectService.mySqlConnect);
cmd.Parameters.Add("@DEVICE_ID", MySqlDbType.Int64, 20).Value = deviceId;
cmd.Parameters.Add("@OUT_VALUE", MySqlDbType.String, 255).Value = outValue;
cmd.Parameters.Add("@LOG_TIME", MySqlDbType.String, 20).Value = logTime;
cmd.ExecuteNonQuery();
cmd.Dispose();
iRetval = 0;
}
catch (MySqlException ex)
{
LogHelper.WriteErrorLog(MethodBase.GetCurrentMethod().DeclaringType, "deleteCounter : " + ex.Message);
iRetval = -1;
}
return iRetval;
}
public List<CounterData> getHistory(long deviceId, string outValue, string type)
{
List<CounterData> counterDataList = new List<CounterData>();
try
{
if (DbConnectService.mySqlConnect.State == ConnectionState.Closed)
{
LogHelper.WriteErrorLog(MethodBase.GetCurrentMethod().DeclaringType, "searchDetectionItem : 数据库链接断开");
int iRetval = DbConnectService.openDb();
if (iRetval != 0)
{
LogHelper.WriteErrorLog(MethodBase.GetCurrentMethod().DeclaringType, "重连失败！");
return null;
}
}
string sQry = "SELECT * FROM r_counter_data where DEVICE_ID = " + deviceId;
if(!outValue.Equals("")) sQry = sQry + " and OUT_VALUE = " + outValue;
if (!type.Equals("")) sQry = sQry + " and DETEC_TYPE = " + type;
MySqlCommand aCommand = new MySqlCommand(sQry, DbConnectService.mySqlConnect);
using (MySqlDataReader aReader = aCommand.ExecuteReader())
{
while (aReader.Read())
{
CounterData counterData = new CounterData();
//姓名
if (!aReader.IsDBNull(0)) counterData.Id = Convert.ToInt32(aReader.GetString(0));
if (!aReader.IsDBNull(1)) counterData.DeviceId = Convert.ToInt64(aReader.GetString(1));
if (!aReader.IsDBNull(2)) counterData.OutValue = aReader.GetString(2);
if (!aReader.IsDBNull(3)) counterData.Value = aReader.GetString(3);
if (!aReader.IsDBNull(4)) counterData.Sensitivity = aReader.GetString(4);
if (!aReader.IsDBNull(5)) counterData.LogTime = aReader.GetString(5);
if (!aReader.IsDBNull(6)) counterData.DetecType = aReader.GetString(6);
counterDataList.Add(counterData);
}
aReader.Close();
}
aCommand.Dispose();
}
catch (MySqlException ex)
{
LogHelper.WriteErrorLog(MethodBase.GetCurrentMethod().DeclaringType, "getHistory: " + ex.Message);
}
return counterDataList;
}
}
}