using Casic.Birmm.RbFreqStandMeasure.R_DataBase.Dto;
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 DeviceServiceImpl : DeviceService
{
public int add(string devName, string devCode, string devTypeId, string devModel,
string custometDev, string customerName, string channel)
{
int iRetval = -1;
//DetectionItemService detectionItemService = new DetectionItemServiceImpl();
try
{
if (DbConnectService.mySqlConnect.State == ConnectionState.Closed)
{
LogHelper.WriteErrorLog(MethodBase.GetCurrentMethod().DeclaringType, "addDeviced : 数据库链接断开");
return iRetval;
}
string sQry = "INSERT INTO r_device (DEV_NAME,DEV_CODE,DEV_TYPEID,DEV_MODEL,REG_TIME,CUSTOMER_NAME,CUSTOMER_DEV,STATUSID,CHANNEL,ACTIVE)" +
"values(@DEV_NAME,@DEV_CODE,@DEV_TYPEID,@DEV_MODEL,@REG_TIME,@CUSTOMER_NAME,@CUSTOMER_DEV,@STATUSID,@CHANNEL,@ACTIVE)";
MySqlCommand cmd = new MySqlCommand(sQry, DbConnectService.mySqlConnect);
cmd.Parameters.Add("@DEV_NAME", MySqlDbType.String, 30).Value = devName;
cmd.Parameters.Add("@DEV_CODE", MySqlDbType.String, 30).Value = devCode;
cmd.Parameters.Add("@DEV_TYPEID", MySqlDbType.String, 30).Value = devTypeId;
cmd.Parameters.Add("@DEV_MODEL", MySqlDbType.String, 30).Value = devModel;
cmd.Parameters.Add("@REG_TIME", MySqlDbType.Date, 0).Value = DateTime.Now;
cmd.Parameters.Add("@CUSTOMER_NAME", MySqlDbType.String, 30).Value = customerName;
cmd.Parameters.Add("@CUSTOMER_DEV", MySqlDbType.String, 30).Value = custometDev;
cmd.Parameters.Add("@STATUSID", MySqlDbType.String, 20).Value = 1;
cmd.Parameters.Add("@CHANNEL", MySqlDbType.String, 20).Value = channel;
cmd.Parameters.Add("@ACTIVE", MySqlDbType.Int64, 0).Value = 0;
cmd.ExecuteNonQuery();
cmd.Dispose();
iRetval = 0;
}
catch (MySqlException ex)
{
LogHelper.WriteErrorLog(MethodBase.GetCurrentMethod().DeclaringType, "addDeviced : " + ex.Message);
}
return iRetval;
}
public int update(int id, string devName, string devCode, string devTypeId, string devModel,
string custometDev, string customerName, string channel)
{
int iRetval = -1;
try
{
if (DbConnectService.mySqlConnect.State == ConnectionState.Closed)
{
LogHelper.WriteErrorLog(MethodBase.GetCurrentMethod().DeclaringType, "updateDeviced : 数据库链接断开");
return iRetval;
}
string sQry = "UPDATE r_device SET (DEV_NAME,DEV_CODE,DEV_TYPEID,DEV_MODEL,CUSTOMER_NAME,CUSTOMER_DEV,CHANNEL)" +
"values(@DEV_NAME,@DEV_CODE,@DEV_TYPEID,@DEV_MODEL,@CUSTOMER_NAME,@CUSTOMER_DEV,@CHANNEL) WHERE ID = " + id;
MySqlCommand cmd = new MySqlCommand(sQry, DbConnectService.mySqlConnect);
cmd.Parameters.Add("@DEV_NAME", MySqlDbType.String, 30).Value = devName;
cmd.Parameters.Add("@DEV_CODE", MySqlDbType.String, 30).Value = devCode;
cmd.Parameters.Add("@DEV_TYPEID", MySqlDbType.String, 30).Value = devTypeId;
cmd.Parameters.Add("@DEV_MODEL", MySqlDbType.String, 30).Value = devModel;
cmd.Parameters.Add("@CUSTOMER_NAME", MySqlDbType.String, 30).Value = customerName;
cmd.Parameters.Add("@CUSTOMER_DEV", MySqlDbType.String, 30).Value = custometDev;
cmd.Parameters.Add("@CHANNEL", MySqlDbType.String, 20).Value = channel;
cmd.ExecuteNonQuery();
cmd.Dispose();
iRetval = 0;
}
catch (MySqlException ex)
{
LogHelper.WriteErrorLog(MethodBase.GetCurrentMethod().DeclaringType, "updateDeviced : " + ex.Message);
}
return iRetval;
}
public int delete(int id)
{
int iRetval = -1;
try
{
if (DbConnectService.mySqlConnect.State == ConnectionState.Closed)
{
LogHelper.WriteErrorLog(MethodBase.GetCurrentMethod().DeclaringType, "deleteDeviced : 数据库链接断开");
return iRetval;
}
string sQry = "UPDATE r_device SET ACTIVE = 1 WHERE ID = " + id;
MySqlCommand cmd = new MySqlCommand(sQry, DbConnectService.mySqlConnect);
cmd.ExecuteNonQuery();
cmd.Dispose();
iRetval = 0;
}
catch (MySqlException ex)
{
LogHelper.WriteErrorLog(MethodBase.GetCurrentMethod().DeclaringType, "deleteDeviced : " + ex.Message);
}
return iRetval;
}
public List<DeviceDto> search(string devName, string devCode, string devModel,
string suctomerDev, string customerName, string statusId)
{
List<DeviceDto> deviceDtoList = new List<DeviceDto>();
DictService dictService = new DictServiceImpl();
try
{
if (DbConnectService.mySqlConnect.State == ConnectionState.Closed)
{
LogHelper.WriteErrorLog(MethodBase.GetCurrentMethod().DeclaringType, "searchDevice : 数据库链接断开");
return null;
}
string sQry = "SELECT * FROM r_device where active = " + 0;
if (devName != "" && devName != null)
sQry += " and DEV_NAME like '%" + devName + "%'";
if (devCode != "" && devCode != null)
sQry += " and DEV_CODE like '%" + devCode + "%'";
if (devModel != "" && devModel != null)
sQry += " and DEV_MODEL like '%" + devModel + "%'";
if (suctomerDev != "" && suctomerDev != null)
sQry += " and CUSTOMER_DEV like '%" + suctomerDev + "%'";
if (customerName != "" && customerName != null)
sQry += " and CUSTOMER_NAME like '%" + customerName + "%'";
if (statusId != "" && statusId != null)
sQry += " and STATUSID = " + statusId;
MySqlCommand aCommand = new MySqlCommand(sQry, DbConnectService.mySqlConnect);
using (MySqlDataReader aReader = aCommand.ExecuteReader())
{
while (aReader.Read())
{
DeviceDto deviceDto = new DeviceDto();
if (!aReader.IsDBNull(0)) deviceDto.Id = Convert.ToInt32(aReader.GetString(0));
if (!aReader.IsDBNull(1)) deviceDto.DevName = aReader.GetString(1);
if (!aReader.IsDBNull(2)) deviceDto.DevCode = aReader.GetString(2);
if (!aReader.IsDBNull(3))
{
deviceDto.DevTypeId = aReader.GetString(3);
deviceDto.DevTypeName = dictService.getNameByCode("devType", aReader.GetString(3));
}
if (!aReader.IsDBNull(4)) deviceDto.DevModel = aReader.GetString(4);
if (!aReader.IsDBNull(5)) deviceDto.CustomerName = aReader.GetString(5);
if (!aReader.IsDBNull(6)) deviceDto.CustomerDev = aReader.GetString(6);
if (!aReader.IsDBNull(7))
{
deviceDto.StatusId = aReader.GetString(7);
deviceDto.StatusName = dictService.getNameByCode("devStatus", aReader.GetString(7));
}
if (!aReader.IsDBNull(8)) deviceDto.Channel = aReader.GetString(8);
deviceDtoList.Add(deviceDto);
}
aCommand.Dispose();
}
}
catch (MySqlException ex)
{
LogHelper.WriteErrorLog(MethodBase.GetCurrentMethod().DeclaringType, "searchDevice: " + ex.Message);
deviceDtoList = null;
}
return deviceDtoList;
}
public List<DeviceDto> searchPage(string devName, string devCode, string devModel,
string customDev, string customerName, string statusId,
int page, int limit)
{
List<DeviceDto> deviceDtoList = new List<DeviceDto>();
DictService dictService = new DictServiceImpl();
MySqlConnection conn = null;
try
{
conn = DbConnectService.getConnection();
string sQry = "SELECT * FROM r_device where active = " + 0;
if (devName != "" && devName != null)
sQry += " and DEV_NAME like '%" + devName + "%'";
if (devCode != "" && devCode != null)
sQry += " and DEV_CODE like '%" + devCode + "%'";
if (devModel != "" && devModel != null)
sQry += " and DEV_MODEL like '%" + devModel + "%'";
if (customDev != "" && customDev != null)
sQry += " and CUSTOMER_DEV like '%" + customDev + "%'";
if (customerName != "" && customerName != null)
sQry += " and CUSTOMER_NAME like '%" + customerName + "%'";
if (statusId != "" && statusId != null)
sQry += " and STATUSID = " + statusId;
sQry += " limit " + limit + " offset " + (page - 1) * limit;
MySqlCommand aCommand = new MySqlCommand(sQry, DbConnectService.mySqlConnect);
using (MySqlDataReader aReader = aCommand.ExecuteReader())
{
while (aReader.Read())
{
DeviceDto deviceDto = new DeviceDto();
if (!aReader.IsDBNull(0)) deviceDto.Id = Convert.ToInt32(aReader.GetString(0)); // id
if (!aReader.IsDBNull(1)) deviceDto.DevName = aReader.GetString(1); // devName
if (!aReader.IsDBNull(2)) deviceDto.DevCode = aReader.GetString(2); // devCode
if (!aReader.IsDBNull(3))
{
deviceDto.DevTypeId = aReader.GetString(3); // devType
deviceDto.DevTypeName = dictService.getNameByCode("devType", aReader.GetString(3));
}
if (!aReader.IsDBNull(4)) deviceDto.DevModel = aReader.GetString(4); // devModel
if (!aReader.IsDBNull(5))
{
DateTime reg = aReader.GetDateTime(5); // regTime
deviceDto.RegTime = reg.ToString("yyyy-MM-dd");
}
if (!aReader.IsDBNull(6)) deviceDto.CustomerName = aReader.GetString(6); // customName
if (!aReader.IsDBNull(7)) deviceDto.CustomerDev = aReader.GetString(7); // customDev
if (!aReader.IsDBNull(8))
{
deviceDto.StatusId = aReader.GetString(8); // devStatus
deviceDto.StatusName = dictService.getNameByCode("devStatus", deviceDto.StatusId);
}
if (!aReader.IsDBNull(9)) deviceDto.Channel = aReader.GetString(9);
deviceDtoList.Add(deviceDto);
}
aCommand.Dispose();
}
}
catch (MySqlException ex)
{
LogHelper.WriteErrorLog(MethodBase.GetCurrentMethod().DeclaringType, "searchDevice: " + ex.Message);
deviceDtoList = null;
}
finally
{
if (null != conn)
{
conn.Close();
conn.Dispose(); //用完即关闭连接
}
}
return deviceDtoList;
}
public int getTotalCount(string devName, string devCode, string devModel,
string customDev, string customerName, string statusId)
{
int totalCount = 0;
MySqlConnection conn = null;
try
{
conn = DbConnectService.getConnection();
string sQry = "SELECT count(id) FROM r_device where active = " + 0;
if (devName != "" && devName != null)
sQry += " and DEV_NAME like '%" + devName + "%'";
if (devCode != "" && devCode != null)
sQry += " and DEV_CODE like '%" + devCode + "%'";
if (devModel != "" && devModel != null)
sQry += " and DEV_MODEL like '%" + devModel + "%'";
if (customDev != "" && customDev != null)
sQry += " and CUSTOMER_DEV like '%" + customDev + "%'";
if (customerName != "" && customerName != null)
sQry += " and CUSTOMER_NAME like '%" + customerName + "%'";
if (statusId != "" && statusId != null)
sQry += " and STATUSID = " + statusId;
MySqlCommand aCommand = new MySqlCommand(sQry, DbConnectService.mySqlConnect);
using (MySqlDataReader aReader = aCommand.ExecuteReader())
{
while (aReader.Read())
{
totalCount = aReader.GetInt32(0);
}
aCommand.Dispose();
}
}
catch (MySqlException ex)
{
LogHelper.WriteErrorLog(MethodBase.GetCurrentMethod().DeclaringType, "getTotalCount: " + ex.Message);
totalCount = 0;
}
finally
{
if (null != conn)
{
conn.Close();
conn.Dispose(); //用完即关闭连接
}
}
return totalCount;
}
public List<DeviceView> getDeviceByChannel()
{
List<DeviceView> deviceViewList = new List<DeviceView>();
DictService dictService = new DictServiceImpl();
MySqlConnection conn = null;
try
{
conn = DbConnectService.getConnection();
string sQry = "SELECT * FROM r_device where active = 0 and STATUSID = 2 ";
MySqlCommand aCommand = new MySqlCommand(sQry, DbConnectService.mySqlConnect);
using (MySqlDataReader aReader = aCommand.ExecuteReader())
{
while (aReader.Read())
{
DeviceView deviceView = new DeviceView();
if (!aReader.IsDBNull(0)) deviceView.Id = Convert.ToInt32(aReader.GetString(0)); // id
if (!aReader.IsDBNull(1)) deviceView.DevName = aReader.GetString(1); // devName
if (!aReader.IsDBNull(2)) deviceView.DevCode = aReader.GetString(2); // devCode
if (!aReader.IsDBNull(3))
{
deviceView.DevTypeId = aReader.GetString(3); // devType
deviceView.DevTypeName = dictService.getNameByCode("devType", aReader.GetString(3));
}
if (!aReader.IsDBNull(4)) deviceView.DevModel = aReader.GetString(4); // devModel
if (!aReader.IsDBNull(5))
{
DateTime reg = aReader.GetDateTime(5); // regTime
deviceView.RegTime = reg.ToString("yyyy-MM-dd");
}
if (!aReader.IsDBNull(6)) deviceView.CustomerName = aReader.GetString(6); // customName
if (!aReader.IsDBNull(7)) deviceView.CustomerDev = aReader.GetString(7); // customDev
if (!aReader.IsDBNull(8)) deviceView.Channel = aReader.GetString(8);
if (!aReader.IsDBNull(9)) deviceView.Active = Convert.ToInt32(aReader.GetString(9));
if (!aReader.IsDBNull(10))
{
DateTime reg = aReader.GetDateTime(10); // regTime
deviceView.StartTime = reg.ToString("yyyy-MM-dd");
}
if (!aReader.IsDBNull(11))
{
DateTime reg = aReader.GetDateTime(11); // regTime
deviceView.EndTime = reg.ToString("yyyy-MM-dd");
}
deviceViewList.Add(deviceView);
aCommand.Dispose();
}
}
}
catch (Exception ex)
{
LogHelper.WriteErrorLog(MethodBase.GetCurrentMethod().DeclaringType, "getDeviceByChannel: " + ex.Message);
deviceViewList = null;
}
finally
{
if (null != conn)
{
conn.Close();
conn.Dispose(); //用完即关闭连接
}
}
return deviceViewList;
}
}
}
