public int DeleteDataConfig(DC_DATA_CONFIG config)
{
DeleteRealData(config.ID);
string sql = $@"DELETE FROM DC.DC_DATA_CONFIG WHERE ID={GetValueStr(config.ID)}";
return(OracleDataHelper.ExecuteNonQuery(sql));
}
public int InsertDCDevice(DC_DEVICE device)
{
string sql = $@"INSERT INTO DC.DC_DEVICE(
SERVER_ID,
NUM,
NAME,
IP_ADDRESS,
MAC_ADDRESS,
CHANNEL,
S7CONNECTION,
DESCRIPTION,
ENABLE,
CRA_ID)VALUES(
{GetValueStr(device.SERVER_ID)},
{GetValueStr(device.NUM)},
{GetValueStr(device.NAME)},
{GetValueStr(device.IP_ADDRESS)},
{GetValueStr(device.MAC_ADDRESS)},
{GetValueStr(device.CHANNEL)},
{GetValueStr(device.S7CONNECTION)},
{GetValueStr(device.DESCRIPTION)},
{GetValueStr(device.ENABLE)},
{GetValueStr(device.CRA_ID)}
) RETURNING ID INTO :ID";
OracleParameter para = new OracleParameter("ID", OracleDbType.Int32);
int res = OracleDataHelper.ExecuteNonQuery(sql, new OracleParameter[] { para });
device.ID = ((OracleDecimal)para.Value).ToInt32();
return(res);
}
public int InsertRealData(int configID)
{
string sql = $@"INSERT INTO DC.DC_REAL_DATA(
DATA_CONFIG_ID,
UPDATED)VALUES(
'{configID}',0)";
return(OracleDataHelper.ExecuteNonQuery(sql));
}
/// <summary>
/// 更新实时数据
/// </summary>
/// <param name="dataPointID"></param>
/// <param name="value"></param>
/// <param name="quality"></param>
/// <param name="stamp"></param>
/// <param name="updateTime"></param>
/// <param name="updated"></param>
/// <returns></returns>
public int UpdateRealData(int dataID, object value, string quality, DateTime stamp, DateTime updateTime, int updated)
{
string sql = $@"UPDATE DC.DC_REAL_DATA SET VALUE='{value}',QUALITY='{quality}',
TIME_STAMP=TO_DATE('{stamp.ToString("yyyy/MM/dd HH:mm:ss")}','yyyy/mm/dd hh24:mi:ss'),
UPDATE_TIME=TO_DATE('{updateTime.ToString("yyyy/MM/dd HH:mm:ss")}','yyyy/mm/dd hh24:mi:ss'),
UPDATED={updated} WHERE DATA_CONFIG_ID={dataID}";
return(OracleDataHelper.ExecuteNonQuery(sql));
}
public int ModifyDCServer(DC_SERVER server)
{
string sql = $@"UPDATE DC.DC_SERVER SET
NUM={GetValueStr(server.NUM)},
NAME={GetValueStr(server.NAME)},
IP_ADDRESS={GetValueStr(server.IP_ADDRESS)},
DESCRIPTION={GetValueStr(server.DESCRIPTION)},
ENABLE={GetValueStr(server.ENABLE)}
WHERE ID={GetValueStr(server.ID)}";
return(OracleDataHelper.ExecuteNonQuery(sql));
}
/// <summary>
/// 插入动作
/// </summary>
/// <param name="craID"></param>
/// <param name="x"></param>
/// <param name="y"></param>
/// <param name="l_y"></param>
/// <param name="z"></param>
/// <param name="l_z"></param>
/// <param name="wgt"></param>
/// <param name="l_wgt"></param>
/// <param name="symbol"></param>
/// <param name="count"></param>
/// <param name="cNum"></param>
/// <returns></returns>
public int InsertAction(int craID, object time, object x, object y, object l_y, object z, object l_z, object wgt, object l_wgt, object symbol, object count, object cNum)
{
if (CraneActionColumns == null)
{
CraneActionColumns = GetColumns("CRANE_ACTION_RECORD").Rows.Cast <DataRow>().ToList();
}
DataRow timerow = CraneActionColumns.First(s => s["COLUMN_NAME"].ToString() == "OPERATION_TIME");
DataRow xrow = CraneActionColumns.First(s => s["COLUMN_NAME"].ToString() == "COORD_X");
DataRow yrow = CraneActionColumns.First(s => s["COLUMN_NAME"].ToString() == "COORD_Y");
DataRow l_yrow = CraneActionColumns.First(s => s["COLUMN_NAME"].ToString() == "LIITLT_HOOK_Y");
DataRow zrow = CraneActionColumns.First(s => s["COLUMN_NAME"].ToString() == "COORD_Z");
DataRow l_zrow = CraneActionColumns.First(s => s["COLUMN_NAME"].ToString() == "LITTLE_HOOK_Z");
DataRow wgtrow = CraneActionColumns.First(s => s["COLUMN_NAME"].ToString() == "WEIGHT");
DataRow symbolrow = CraneActionColumns.First(s => s["COLUMN_NAME"].ToString() == "ACTION_SYMBOL");
DataRow countrow = CraneActionColumns.First(s => s["COLUMN_NAME"].ToString() == "HANGE_QUAN");
DataRow cNumrow = CraneActionColumns.First(s => s["COLUMN_NAME"].ToString() == "CACHE_NUM");
time = ParseValue(time, timerow);
x = ParseValue(x, xrow);
y = ParseValue(y, yrow);
l_y = ParseValue(l_y, l_yrow);
z = ParseValue(z, zrow);
l_z = ParseValue(l_z, l_zrow);
wgt = ParseValue(wgt, wgtrow);
symbol = ParseValue(symbol, symbolrow);
count = ParseValue(count, countrow);
cNum = ParseValue(cNum, cNumrow);
time = ValidateValueRange(timerow, time.ToString())? time : null;
x = ValidateValueRange(xrow, x.ToString())?x: null;
y = ValidateValueRange(yrow, y.ToString())?y: null;
l_y = ValidateValueRange(l_yrow, l_y.ToString())? l_y : null;
z = ValidateValueRange(zrow, z.ToString())? z : null;
l_z = ValidateValueRange(l_zrow, l_z.ToString())? l_z : null;
wgt = ValidateValueRange(wgtrow, wgt.ToString())? wgt : null;
symbol = ValidateValueRange(symbolrow, symbol.ToString())? symbol : null;
count = ValidateValueRange(countrow, count.ToString())? count : null;
cNum = ValidateValueRange(cNumrow, cNum.ToString())? cNum : null;
string guid = Guid.NewGuid().ToString("N");
string sql = $@"INSERT INTO PUB.CRANE_ACTION_RECORD(ID,CRA_ID,ACCEPT_TIME,OPERATION_TIME,COORD_X,COORD_Y,LIITLT_HOOK_Y,COORD_Z,LITTLE_HOOK_Z,
WEIGHT,LITTLE_HOOK_WEIGHT,ACTION_SYMBOL,HANGE_QUAN,CACHE_NUM)
VALUES('{guid}','{craID}',{GetDateTimeStr(DateTime.Now)},{GetDateTimeStr(time)},{GetValueStr(x)},{GetValueStr(y)},{GetValueStr(l_y)},{GetValueStr(z)},{GetValueStr(l_z)},
{GetValueStr(wgt)},{GetValueStr(l_wgt)},{GetValueStr(symbol)},{GetValueStr(count)},{GetValueStr(cNum)})";
return(OracleDataHelper.ExecuteNonQuery(sql));
}
public int InsertDataConfig(DC_DATA_CONFIG config)
{
string sql = $@"INSERT INTO DC.DC_DATA_CONFIG(
DEVICE_ID,
NUM,
NAME,
MEMORY_ADDRESS,
SUBSCRIPTION,
DESCRIPTION,
ENABLE,
TABLE_USER,
TABLE_NAME,
IDENTITY_COLUMN,
IDENTITY_VALUE,
FIELD_NAME,
FIELD_DATA_TYPE,
FIELD_DATA_LENGTH,
FIELD_DATA_PRECISION,
FIELD_DATA_SCALE,
CONVERTER
)VALUES(
{GetValueStr(config.DEVICE_ID)},
{GetValueStr(config.NUM)},
{GetValueStr(config.NAME)},
{GetValueStr(config.MEMORY_ADDRESS)},
{GetValueStr(config.SUBSCRIPTION)},
{GetValueStr(config.DESCRIPTION)},
{GetValueStr(config.ENABLE)},
{GetValueStr(config.TABLE_USER)},
{GetValueStr(config.TABLE_NAME)},
{GetValueStr(config.IDENTITY_COLUMN)},
{GetValueStr(config.IDENTITY_VALUE)},
{GetValueStr(config.FIELD_NAME)},
{GetValueStr(config.FIELD_DATA_TYPE)},
{GetValueStr(config.FIELD_DATA_LENGTH)},
{GetValueStr(config.FIELD_DATA_PRECISION)},
{GetValueStr(config.FIELD_DATA_SCALE)},
{GetValueStr(config.CONVERTER)}
) RETURNING ID INTO :ID";
OracleParameter para = new OracleParameter("ID", OracleDbType.Int32);
int res = OracleDataHelper.ExecuteNonQuery(sql, new OracleParameter[] { para });
config.ID = ((OracleDecimal)para.Value).ToInt32();
InsertRealData(config.ID);
return(res);
}
public int ModifyDevice(DC_DEVICE device)
{
string sql = $@"UPDATE DC.DC_DEVICE SET
SERVER_ID={GetValueStr(device.SERVER_ID)},
NUM={GetValueStr(device.NUM)},
NAME={GetValueStr(device.NAME)},
IP_ADDRESS={GetValueStr(device.IP_ADDRESS)},
MAC_ADDRESS={GetValueStr(device.MAC_ADDRESS)},
CHANNEL={GetValueStr(device.CHANNEL)},
S7CONNECTION={GetValueStr(device.S7CONNECTION)},
DESCRIPTION={GetValueStr(device.DESCRIPTION)},
ENABLE={GetValueStr(device.ENABLE)},
CRA_ID={GetValueStr(device.CRA_ID)}
WHERE ID={GetValueStr(device.ID)}";
return(OracleDataHelper.ExecuteNonQuery(sql));
}
public bool ChangeLastOnline(string customerId, DateTime time)
{
string query = "CHAT.CUSTOMER_LASTONLINE";
try
{
OracleDataHelper helper = OracleHelper;
helper.BeginTransaction();
helper.ExecuteNonQuery(query, customerId, time);
helper.Commit();
}
catch (Exception ex)
{
OracleHelper.Rollback();
throw ex;
}
return(true);
}
public bool UpdateCustomerInfo(CustomerInfo cus)
{
string query = "CHAT.CUSTOMER_UPDATE";
try
{
OracleDataHelper helper = OracleHelper;
helper.BeginTransaction();
helper.ExecuteNonQuery(query, cus.CustomerId, cus.CustomerName);
helper.Commit();
}
catch (Exception ex)
{
OracleHelper.Rollback();
throw ex;
}
return(true);
}
public bool UnFriend(string customerId, string requestId)
{
string query = "CHAT.FRIEND_DELETE";
try
{
OracleDataHelper helper = OracleHelper;
helper.BeginTransaction();
helper.ExecuteNonQuery(query, customerId, requestId);
helper.Commit();
}
catch (Exception ex)
{
OracleHelper.Rollback();
throw ex;
}
return(true);
}
public bool AddFriendRequest(AddFriendRequest request)
{
string query = "CHAT.FRIENDREQUEST_CREATE";
try
{
OracleDataHelper helper = OracleHelper;
helper.BeginTransaction();
helper.ExecuteNonQuery(query, request.Id, request.Sender, request.Receiver, request.Datetime);
helper.Commit();
}
catch (Exception ex)
{
OracleHelper.Rollback();
throw ex;
}
return(true);
}
public bool DeleteFriendRequest(string requestId)
{
string query = "CHAT.FRIENDREQUEST_DELETE";
try
{
OracleDataHelper helper = OracleHelper;
helper.BeginTransaction();
helper.ExecuteNonQuery(query, requestId);
helper.Commit();
}
catch (Exception ex)
{
OracleHelper.Rollback();
throw ex;
}
return(true);
}
public bool LeaveGroup(string customerId, string groupId)
{
string query = "CHAT.LEAVEGROUP";
try
{
OracleDataHelper helper = OracleHelper;
helper.BeginTransaction();
helper.ExecuteNonQuery(query, customerId, groupId);
helper.Commit();
}
catch (Exception ex)
{
OracleHelper.Rollback();
throw ex;
}
return(true);
}
public bool UpdateGroupInfo(GroupInfo group)
{
string query = "CHAT.GROUP_UPDATE";
try
{
OracleDataHelper helper = OracleHelper;
helper.BeginTransaction();
helper.ExecuteNonQuery(query, group.GroupId, group.GroupName, group.Description, group.IsPrivate);
helper.Commit();
}
catch (Exception ex)
{
OracleHelper.Rollback();
throw ex;
}
return(true);
}
/// <summary>
/// 更新实时数据至业务表
/// </summary>
/// <param name="obj"></param>
/// <param name="dic"></param>
/// <returns></returns>
public int UpdateRealData2BusiTable(string user, string table, string identityValue, Dictionary <DC_DATA_CONFIG, DC_REAL_DATA> dic)
{
var datas = dic.Where(s => ValidateValueQuality(s.Value)); //校验质量
datas = ConvertValue(datas); //转换值
datas = datas.Where(s => ValidateValueRange(s.Key.FIELD_DATA_TYPE, s.Key.FIELD_DATA_PRECISION ?? 0, s.Key.FIELD_DATA_SCALE ?? 0, s.Key.FIELD_DATA_LENGTH ?? 0, s.Value.VALUE)); //校验范围
if (datas.Count() != 0)
{
string str = string.Join(",", datas.Select(s => $"{s.Key.FIELD_NAME}={GetValueStr(s.Key, s.Value)}")); //字段赋值字符串
string sql = $@"UPDATE {user}.{table} SET {str} WHERE ID='{identityValue}'"; //完整sql
return(OracleDataHelper.ExecuteNonQuery(sql)); //执行
}
return(0);
}
public bool AddMessage(MessageInfo msg)
{
string query = "CHAT.MESSAGE_CREATE";
try
{
OracleDataHelper helper = OracleHelper;
helper.BeginTransaction();
helper.ExecuteNonQuery(query, msg.Id, msg.SenderId, msg.ReceiverId, msg.Content, msg.Datetime, msg.MessageType);
helper.Commit();
}
catch (Exception ex)
{
OracleHelper.Rollback();
throw ex;
}
return(true);
}
public bool DeleteGroupInfo(string groupId)
{
string query = "CHAT.GROUP_DISABLE";
try
{
OracleDataHelper helper = OracleHelper;
helper.BeginTransaction();
helper.ExecuteNonQuery(query, groupId);
helper.Commit();
}
catch (Exception ex)
{
OracleHelper.Rollback();
throw ex;
}
return(true);
}
public int InsertDCServer(DC_SERVER server)
{
string sql = $@"INSERT INTO DC.DC_SERVER(
NUM,
NAME,
IP_ADDRESS,
DESCRIPTION,
ENABLE
)VALUES(
{GetValueStr(server.NUM)},
{GetValueStr(server.NAME)},
{GetValueStr(server.IP_ADDRESS)},
{GetValueStr(server.DESCRIPTION)},
{GetValueStr(server.ENABLE)}
) RETURNING ID INTO :ID";
OracleParameter para = new OracleParameter("ID", OracleDbType.Int32);
int res = OracleDataHelper.ExecuteNonQuery(sql, new OracleParameter[] { para });
server.ID = ((OracleDecimal)para.Value).ToInt32();
return(res);
}
public bool SetAdminGroup(string customerId, string groupId, bool isAdmin)
{
string query = "CHAT.SETADMINGROUP";
try
{
OracleDataHelper helper = OracleHelper;
helper.BeginTransaction();
int admin = 0;
if (isAdmin)
{
admin = 1;
}
helper.ExecuteNonQuery(query, customerId, groupId, admin);
helper.Commit();
}
catch (Exception ex)
{
OracleHelper.Rollback();
throw ex;
}
return(true);
}
public int ModifyDataConfig(DC_DATA_CONFIG config)
{
string sql = $@"UPDATE DC.DC_DATA_CONFIG SET
DEVICE_ID={GetValueStr(config.DEVICE_ID)},
NUM={GetValueStr(config.NUM)},
NAME={GetValueStr(config.NAME)},
MEMORY_ADDRESS={GetValueStr(config.MEMORY_ADDRESS)},
SUBSCRIPTION={GetValueStr(config.SUBSCRIPTION)},
DESCRIPTION={GetValueStr(config.DESCRIPTION)},
ENABLE={GetValueStr(config.ENABLE)},
TABLE_USER={GetValueStr(config.TABLE_USER)},
TABLE_NAME={GetValueStr(config.TABLE_NAME)},
IDENTITY_COLUMN={GetValueStr(config.IDENTITY_COLUMN)},
IDENTITY_VALUE={GetValueStr(config.IDENTITY_VALUE)},
FIELD_NAME={GetValueStr(config.FIELD_NAME)},
FIELD_DATA_TYPE={GetValueStr(config.FIELD_DATA_TYPE)},
FIELD_DATA_LENGTH={GetValueStr(config.FIELD_DATA_LENGTH)},
FIELD_DATA_PRECISION={GetValueStr(config.FIELD_DATA_PRECISION)},
FIELD_DATA_SCALE={GetValueStr(config.FIELD_DATA_SCALE)},
CONVERTER={GetValueStr(config.CONVERTER)} WHERE ID='{config.ID}'
";
return(OracleDataHelper.ExecuteNonQuery(sql));
}
public int DeleteDevice(DC_DEVICE device)
{
string sql = $@"DELETE FROM DC.DC_DEVICE WHERE ID={GetValueStr(device.ID)}";
return(OracleDataHelper.ExecuteNonQuery(sql));
}
public int DeleteDCServer(DC_SERVER server)
{
string sql = $@"DELETE FROM DC.DC_SERVER WHERE ID={GetValueStr(server.ID)}";
return(OracleDataHelper.ExecuteNonQuery(sql));
}
public int DeleteRealData(int configID)
{
string sql = $"DELETE FROM DC.DC_REAL_DATA WHERE DATA_CONFIG_ID='{configID}'";
return(OracleDataHelper.ExecuteNonQuery(sql));
}
/// <summary>
/// 更新 DC_WRITE_DATA 标志
/// </summary>
/// <param name="ids"></param>
/// <returns></returns>
public int UpdateWriteDataFlag(int id)
{
string sql = $@"UPDATE DC.DC_WRITE_DATA SET UPDATED=0, WRITE_TIME={GetDateTimeStr(DateTime.Now)} WHERE ID = '{id}'";
return(OracleDataHelper.ExecuteNonQuery(sql));
}
/// <summary>
/// 更新 DC_REAL_DATA 标志
/// </summary>
/// <param name="ids"></param>
/// <returns></returns>
public int UpdateRealDataFlag(List <int> ids)
{
string sql = $@"UPDATE DC.DC_REAL_DATA SET UPDATED=0, READ_TIME={GetDateTimeStr(DateTime.Now)} WHERE ID IN ({string.Join(",",ids)})";
return(OracleDataHelper.ExecuteNonQuery(sql));
}