/// <summary>
/// 获得VCUCodeList
/// </summary>
/// <returns>取得响应地址</returns>
public uint GetVCUCodeList()
{
OdbcConnection conn = PostgresHelper.GetOdbcConnection(this.connectionString);
uint value = 0;
try
{
PostgresHelper.CheckConnection(conn);
//where \"CarType\" = '{0}',car.CarType
string sql = string.Format("SELECT id, baud, sendaddress, responseaddress FROM \"GAC_New_VCU\".\"T_VCUCodeList\";");
DataTable dtTpmsCoderLister = PostgresHelper.ExecuteDataTable(conn, CommandType.Text, sql);
int count = dtTpmsCoderLister.Rows.Count;
if (count != 0)
{
value = uint.Parse(Convert.ToInt64(dtTpmsCoderLister.Rows[0]["ResponseAddress"] + "", 16) + "");
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
PostgresHelper.CheckCloseConneciton(conn);
}
return(value);
}
/// <summary>
/// 上传到大线激活岗
/// </summary>
/// <param name="car"></param>
//public void SaveRemoteResult(Car car)
//{
// OdbcConnection conn = PostgresHelper.GetOdbcConnection(this.mesCnnStr);
// try
// {
// //查询大线激活岗的数据库中是否存在该VIN
// PostgresHelper.CheckConnection(conn);
// string existSql = string.Format("SELECT count(0) FROM \"T_Result\" where \"VIN\" = '{0}';", car.VIN);
// string sql = string.Empty;
// DataTable dtExist = PostgresHelper.ExecuteDataTable(conn, CommandType.Text, existSql);
// //上传到大线激活岗的数据库中
// if (dtExist.Rows[0][0] + "" == "0")
// {
// //不存在则插入
// //sql = string.Format("INSERT INTO \"T_Result\"(\"VIN\", \"ID020\", \"ID022\", \"ID021\", \"ID023\", \"Dev\", \"WriteInTime\", \"IsWriteIn\", \"WriteInResult\", \"ErrorCode\", \"MTOC\", \"CarType\") VALUES (\'{0}\', \'{1}\', \'{2}\', \'{3}\',\'{4}\', \'{5}\', \'{6}\', \'{7}\',\'{8}\', \'{9}\', \'{10}\', \'{11}\');"
// // , car.VIN, car.RFID, car.LFID, car.RRID, car.LRID, 201, DateTime.Now, true, car.WriteInResult, car.ErrorCode, car.MTOC, car.CarType);
// }
// else
// {
// //存在则更新
// //sql = string.Format("UPDATE \"T_Result\" SET \"Dev\"=\'{0}\', \"WriteInTime\"=\'{1}\', \"IsWriteIn\"=\'{2}\', \"WriteInResult\"=\'{3}\', \"ErrorCode\"=\'{4}\', \"MTOC\"=\'{5}\', \"CarType\"=\'{6}\' WHERE \"ID\"=\'{7}\';"
// // ,201, DateTime.Now, true, car.WriteInResult, car.ErrorCode, car.MTOC, car.CarType,car.ID);
// }
// //打开上游数据库
// PostgresHelper.CheckConnection(conn);
// //上传
// PostgresHelper.ExecuteNonQuery(conn, CommandType.Text, sql);
// }
// catch (Exception ex)
// {
// logger.Error(ex.Message + "***" + ex.StackTrace);
// }
// finally
// {
// PostgresHelper.CheckCloseConneciton(conn);
// }
//}
/// <summary>
/// 获得历史数据
/// </summary>
/// <param name="startTime"></param>
/// <param name="endTime"></param>
/// <param name="vin"></param>
/// <returns></returns>
public DataTable GetHistoryResult(DateTime startTime, DateTime endTime, string vin)
{
OdbcConnection conn = PostgresHelper.GetOdbcConnection(this.connectionString);
DataTable dataTable = new DataTable();
try
{
PostgresHelper.CheckConnection(conn);
string sql = string.Empty;
if (vin != "")
{
sql = string.Format("select ID as 序号, vin as VIN码,mtoc as MTOC码, \"flashBin\" as 驱动文件,\"writeBin\" as 写入文件,\"calBin\" as 标定文件,softwareversion as 软件版本,testtime as 刷写时间,case when teststate=2 then '成功' when teststate!='2' then '失败' end as 刷写状态,case when isprint=1 then '是' when isprint!=1 then '否' end as 是否打印,\"tracyCode\" as 追溯码,\"num\" as 刷写端口 FROM \"GAC_New_VCU\".\"T_Result\" where testtime >= '{0}' and testtime <= '{1}' and vin='{2}' ", startTime, endTime, vin);
}
else
{
sql = string.Format("select ID as 序号, vin as VIN码,mtoc as MTOC码, \"flashBin\" as 驱动文件,\"writeBin\" as 写入文件,\"calBin\" as 标定文件,softwareversion as 软件版本,testtime as 刷写时间,case when teststate=2 then '成功' when teststate!='2' then '失败' end as 刷写状态,case when isprint=1 then '是' when isprint!=1 then '否' end as 是否打印,\"tracyCode\" as 追溯码,\"num\" as 刷写端口 FROM \"GAC_New_VCU\".\"T_Result\" where testtime >= '{0}' and testtime <= '{1}' ", startTime, endTime);
}
//string sql = string.Format("SELECT \"ID\" as 序号, \"VIN\", \"ID020\" as 右前ID, \"ID022\" as 左前ID, \"ID021\" as 右后ID, \"ID023\" as 左后ID, \"TestTime\" as 激活时间, \"UploadSign\" as 上传标识, \"WriteInTime\" as 写入时间, \"IsWriteIn\" as 写入标识, \"WriteInResult\" as 写入结果,\"ErrorCode\" as 写入过程, \"MTOC\", \"CarType\" as 车型 FROM \"T_Result\" where \"WriteInTime\" >= '{0}' and \"WriteInTime\" <= '{1}' and \"VIN\" like '%{2}%';", startTime, endTime, vin);CRC1 as CRC1校验码,CRC2 as CRC2校验码 ,
dataTable = PostgresHelper.ExecuteDataTable(conn, CommandType.Text, sql);
}
catch (Exception ex)
{
throw ex;
}
finally
{
PostgresHelper.CheckCloseConneciton(conn);
}
return(dataTable);
}
/// <summary>
/// 获取驱动、写入、cal文件对应的零件号、软件版本以及硬件型号信息
/// </summary>
/// <param name="driverPath"></param>
/// <param name="driverName"></param>
/// <param name="writePath"></param>
/// <param name="writeName"></param>
/// <param name="calPath"></param>
/// <param name="calName"></param>
public string[] GetInfo(string driverPath, string driverName, string writePath, string writeName, string calPath, string calName)
{
string[] info = new string[6];
OdbcConnection conn = PostgresHelper.GetOdbcConnection(this.connectionString);
try
{
PostgresHelper.CheckConnection(conn);
string sql = string.Format("select * from \"GAC_New_VCU\".\"T_VCUConfig\" where \"driverpath\"='" + driverPath + "' and \"drivername\"='" + driverName + "' and \"binpath\"='" + writePath + "' and \"binname\"='" + writeName + "' and \"calpath\"='" + calPath + "' and \"calname\"='" + calName + "'");
DataTable exist = PostgresHelper.ExecuteDataTable(conn, CommandType.Text, sql);
if (exist.Rows.Count != 0)
{
info[0] = exist.Rows[0]["elementNum"].ToString();
info[1] = exist.Rows[0]["softwareversion"].ToString();
info[2] = exist.Rows[0]["hardwarecode"].ToString();
info[3] = exist.Rows[0]["SW"].ToString();
info[4] = exist.Rows[0]["HW"].ToString();
info[5] = exist.Rows[0]["sign"].ToString();
}
else
{
info = null;
}
}
catch (Exception ex)
{
logger.Error(ex.Message);
}
finally
{
PostgresHelper.CheckCloseConneciton(conn);
}
return(info);
}
/// <summary>
/// 读取初始化参数
/// </summary>
/// <param name="tableName"></param>
/// <param name="group"></param>
/// <param name="key"></param>
/// <returns></returns>
public string GetConfigValue(string tableName, string group, string key)
{
OdbcConnection conn = PostgresHelper.GetOdbcConnection(this.connectionString);
string value = string.Empty;
try
{
//打开数据库连接
PostgresHelper.CheckConnection(conn);
string sql = string.Format("select keyvalue from" + " " + "\"" + "GAC_New_VCU" + "\"" + "." + "\"" + tableName + "\"" + " " + "where groups='" + group + "' and keys='" + key + "' ");
//string sql = string.Format("select keyvalue from '"+ tableName +"' " + "where groups='" + group + "' and keys='" + key + "' ");
DataTable dtValue = PostgresHelper.ExecuteDataTable(conn, CommandType.Text, sql);
if (dtValue.Rows.Count == 0)
{
throw new Exception(tableName + "中不存在" + group + "或者" + key);
}
value = dtValue.Rows[0][0] + "";
}
catch (Exception ex)
{
throw ex;
}
finally
{
//关闭数据库连接
PostgresHelper.CheckCloseConneciton(conn);
}
return(value);
}
public override IPluginResponse Execute(IPluginRequest pluginRequest)
{
pluginResponse = CreatePluginResponse();
logsetHash = pluginRequest.LogsetHash;
IMongoCollection <BsonDocument> tabadminCollection = MongoDatabase.GetCollection <BsonDocument>(collectionToQuery);
persistenceHelper = GetConcurrentBatchPersister <TabadminModelBase>(pluginRequest);
using (IDbConnection dbConnection = GetOutputDatabaseConnection())
{
Log.Info("Processing Tableau Server version data from tabadmin logs...");
dbConnection.CreateOrMigrateTable <TSVersion>();
TabadminVersionProcessor.Execute(tabadminCollection, persistenceHelper, pluginResponse, logsetHash);
// TODO: Create one class for processing Action and Error objects, as they are nearly identical.
Log.Info("Processing tabadmin error data...");
dbConnection.CreateOrMigrateTable <TabadminError>();
TabadminErrorProcessor.Execute(tabadminCollection, persistenceHelper, pluginResponse, logsetHash);
Log.Info("Processing tabadmin admin action data...");
dbConnection.CreateOrMigrateTable <TabadminAction>();
TabadminActionProcessor.Execute(tabadminCollection, persistenceHelper, pluginResponse, logsetHash);
// Shutdown the persistenceHelper to force a flush to the database, then re-initialize it for future use.
persistenceHelper.Shutdown();
persistenceHelper = GetConcurrentBatchPersister <TabadminModelBase>(pluginRequest);
IList <TSVersion> allTsVersions = dbConnection.Query <TSVersion>("select * from tabadmin_ts_version");
// TODO: Figure out how to do a lazy query of Error and Action objects, and update them one at a time, rather than loading
// the entire table into memory. I ran into issues updating the objects while holding the SELECT query connection open with Each().
// The driver doesn't seem to be able to handle two connections at once.
Log.Info("Updating version_id foreign keys for TabadminError objects...");
foreach (var tabadminError in dbConnection.Query <TabadminError>("select * from tabadmin_error"))
{
tabadminError.VersionId = TSVersionHelper.GetTSVersionIdByDate(allTsVersions, tabadminError);
dbConnection.Update(tabadminError);
}
Log.Info("Updating version_id foreign keys for TabadminAction objects...");
foreach (var tabadminAction in dbConnection.Query <TabadminAction>("select * from tabadmin_action"))
{
tabadminAction.VersionId = TSVersionHelper.GetTSVersionIdByDate(allTsVersions, tabadminAction);
dbConnection.Update(tabadminAction);
}
}
persistenceHelper.Shutdown();
// Check if we persisted any data.
if (!PostgresHelper.ContainsRecord <TSVersion>(OutputDatabaseConnectionFactory) &&
!PostgresHelper.ContainsRecord <TabadminError>(OutputDatabaseConnectionFactory) &&
!PostgresHelper.ContainsRecord <TabadminAction>(OutputDatabaseConnectionFactory))
{
Log.Info("Failed to persist any data from Tabadmin logs!");
pluginResponse.GeneratedNoData = true;
}
return(pluginResponse);
}
/// <summary>
/// 获取标准数据
/// </summary>
/// <param name="sValues"></param>
/// <returns></returns>
public int GetStandard(VCUPath vcuPath)
{
OdbcConnection conn = PostgresHelper.GetOdbcConnection(this.connectionString);
DataTable dataTable = new DataTable();
try
{
PostgresHelper.CheckConnection(conn);
string sql;
if (string.IsNullOrEmpty(vcuPath.ConditionCode))
{
sql = "select * from t_TCUconfig where CarType = '" + vcuPath.CarType + "'";
}
else
{
sql = "select * from t_TCUconfig where CarType = '" + vcuPath.CarType + "'" + " and ConditionCode = '" + vcuPath.ConditionCode + "'";
}
dataTable = PostgresHelper.ExecuteDataTable(conn, CommandType.Text, sql);
if (dataTable.Rows.Count > 0)
{
DataRow dr = dataTable.Rows[0];
vcuPath.CarType = dr["CarType"].ToString();
vcuPath.ConditionCode = dr["ConditionCode"].ToString();
vcuPath.DriverPath = dr["DriverPath"].ToString();
vcuPath.BinPath = dr["BinPath"].ToString();
vcuPath.BinName = dr["BinName"].ToString();
vcuPath.DriverName = dr["DriverName"].ToString();
vcuPath.CalName = dr["calname"].ToString();
vcuPath.CalPath = dr["calpath"].ToString();
vcuPath.CRC1 = dr["CRC1"].ToString();
vcuPath.CRC2 = dr["CRC2"].ToString();
vcuPath.SoftWareVersion = dr["SoftWareVersion"].ToString();
vcuPath.SoftWareCode = dr["SoftWareCode"].ToString();
return(1);
}
else
{
return(0);
}
}
catch (Exception ex)
{
return(-1);
}
finally
{
PostgresHelper.CheckCloseConneciton(conn);
}
}
/// <summary>
/// 修改VCU相关信息
/// </summary>
/// <param name="item"></param>
public void UpdateVCUconfig(VCUconfig item)
{
try
{
string sql = string.Format("Update \"GAC_New_VCU\".\"T_VCUConfig\" set mtoc = '{0}' ,drivername= '{1}',driverpath= '{2}',binname= '{3}',binpath= '{4}',calname= '{5}',calpath= '{6}',softwareversion= '{7}',\"elementNum\"= '{8}',\"hardwarecode\"= '{9}' ,\"HW\"= '{10}',\"SW\"= '{11}' where id = {12};", item.MTOC, item.DriverName, item.DriverPath, item.BinName, item.BinPath, item.CalName, item.CalPath, item.SoftWareVersion, item.ElementNum, item.HardWareCode, item.HW, item.SW, item.Id);
int result = PostgresHelper.ExecuteNonQuery(this.connectionString, CommandType.Text, sql);
}
catch (Exception ex)
{
logger.Error(ex.Message + "***" + ex.StackTrace);
}
}
/// <summary>
/// 删除VCU配置信息
/// </summary>
/// <param name="item"></param>
public void DeleteVCUconfig(VCUconfig item)
{
try
{
string sql = string.Format("delete from \"GAC_New_VCU\".\"T_VCUConfig\" where id = {0}", item.Id);
int result = PostgresHelper.ExecuteNonQuery(this.connectionString, CommandType.Text, sql);
}
catch (Exception ex)
{
logger.Error(ex.Message + "***" + ex.StackTrace);
}
}
/// <summary>
/// 保存VCU相关信息
/// </summary>
/// <param name="item"></param>
public void SaveVCUconfig(VCUconfig item)
{
try
{
string sql = string.Format("INSERT INTO \"GAC_New_VCU\".\"T_VCUConfig\"(mtoc,drivername,driverpath,binname,binpath,calname,calpath,softwareversion,hardwarecode,\"elementNum\",\"HW\",\"SW\",\"sign\") VALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}');",
item.MTOC, item.DriverName, item.DriverPath, item.BinName, item.BinPath, item.CalName, item.CalPath, item.SoftWareVersion, item.HardWareCode, item.ElementNum, item.HW, item.SW, item.Sign);
int result = PostgresHelper.ExecuteNonQuery(this.connectionString, CommandType.Text, sql);
}
catch (Exception ex)
{
logger.Error(ex.Message + "***" + ex.StackTrace);
}
}
/// <summary>
/// 查找重复VCU信息
/// </summary>
/// <param name="item"></param>
public int RepeatVCUconfig(VCUconfig item)
{
int result = 0;
try
{
string sql = string.Format("select count(*) from \"GAC_New_VCU\".\"T_VCUConfig\" where mtoc = '{0}' ", item.MTOC);
result = Convert.ToInt32(PostgresHelper.ExecuteScalar(this.connectionString, CommandType.Text, sql));
}
catch (Exception ex)
{
logger.Error(ex.Message + "***" + ex.StackTrace);
}
return(result);
}
/// <summary>
/// 数据上传
/// </summary>
/// <param name="obj"></param>
public void Upload_Timer()
{
if (!canUse_Upload)
{
return;
}
else
{
lock (olock_Upload)
{
if (canUse_Upload)
{
canUse_Upload = false;
}
else
{
return;
}
}
}
try
{
string localSql = "select * from T_Result where uploadsign=0";
DataTable dt = PostgresHelper.ExecuteDataTable(this.ConnectionString, CommandType.Text, localSql);
foreach (DataRow item in dt.Rows)
{
//上传数据
string sql = string.Format("INSERT INTO T_Upload_TcuData(cartype, conditioncode, crc1, crc2, softwareversion, softwarecode, testtime) VALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}');", item["CarType"].ToString(), item["ConditionCode"].ToString(), item["CRC1"].ToString(), item["CRC2"].ToString(), item["SoftWareVersion"].ToString(), item["SoftWareCode"].ToString(), item["testtime"].ToString());
int result = SQLServerHelper.ExecuteNonQuery(this.MESCnnStr, CommandType.Text, sql);
//把上传标识uploadsign改为1
if (result > 0)
{
string updateSql = string.Format("update T_Result set UploadSign = 1 where id ={0}", Convert.ToInt32(item["id"]));
PostgresHelper.ExecuteNonQuery(this.connectionString, CommandType.Text, updateSql);
}
}
}
catch (Exception ex)
{
logger.Error(ex.Message + "***" + ex.StackTrace);
}
}
/// <summary>
/// 得到今天已经打印了多少条数据
/// </summary>
/// <returns></returns>
public int GetPrintNum()
{
int num = 0;
try
{
string endTime = DateTime.Now.ToString();
string startTime = DateTime.Now.ToShortDateString() + " 00:00:00";
string sql = string.Format("select count(*) from t_result where testtime > '{0}' and testtime < '{1}'", startTime, endTime);
num = Convert.ToInt32(PostgresHelper.ExecuteScalar(this.connectionString, CommandType.Text, sql)) + 1;
}
catch (Exception ex)
{
logger.Error(ex.Message + "***" + ex.StackTrace);
}
return(num);
}
/// <summary>
///
/// </summary>
/// <returns></returns>
public List <DefineFlower> GetDefineFlower()
{
OdbcConnection conn = PostgresHelper.GetOdbcConnection(this.connectionString);
List <DefineFlower> lstDefineFlower = new List <DefineFlower>();
try
{
PostgresHelper.CheckConnection(conn);
string sql = string.Format("SELECT id, flowname, sendcmd, waittime, receivecmd, enabled, sendaddress,sleeptime, receivenum, canind FROM \"GAC_New_VCU\".\"T_DefineFlow\" where enabled = 1 order by id;");
DataTable dtDefineFlower = PostgresHelper.ExecuteDataTable(conn, CommandType.Text, sql);
if (dtDefineFlower.Rows.Count == 0)
{
throw new Exception("T_DefineFlow中不存在");
}
for (int i = 0; i < dtDefineFlower.Rows.Count; i++)
{
DefineFlower defineFlower = new DefineFlower();
defineFlower.ID = int.Parse(dtDefineFlower.Rows[i]["ID"] + "");
defineFlower.FlowName = dtDefineFlower.Rows[i]["Flowname"] + "";
defineFlower.SendCmd = dtDefineFlower.Rows[i]["SendCmd"] + "";
defineFlower.WaitTime = int.Parse(dtDefineFlower.Rows[i]["WaitTime"] + "");
defineFlower.ReceiveCmd = (dtDefineFlower.Rows[i]["ReceiveCmd"] + "").ToUpper();
defineFlower.Enabled = (dtDefineFlower.Rows[i]["Enabled"] + "" == "1") ? true : false;
// uint.Parse(Convert.ToInt64(dtDefineFlower.Rows[i]["SendAddress"] + "", 16) + "");
defineFlower.SendAddress = uint.Parse(Convert.ToInt64(dtDefineFlower.Rows[i]["SendAddress"] + "", 16) + "");
defineFlower.SleepTime = int.Parse(dtDefineFlower.Rows[i]["SleepTime"] + "");
defineFlower.ReceiveNum = int.Parse(dtDefineFlower.Rows[i]["ReceiveNum"] + "");
lstDefineFlower.Add(defineFlower);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
PostgresHelper.CheckCloseConneciton(conn);
}
return(lstDefineFlower);
}
/// <summary>
/// 删除VIN_MTOC码
/// </summary>
public void DeleteVIN_MTOC()
{
OdbcConnection conn = PostgresHelper.GetOdbcConnection(this.connectionString);
try
{
PostgresHelper.CheckConnection(conn);
string sql = string.Format("DELETE FROM \"GAC_New_VCU\".\"VIN_MTOC\"");
PostgresHelper.ExecuteNonQuery(conn, CommandType.Text, sql);
}
catch (Exception ex)
{
logger.Info(ex.Message + "***" + ex.StackTrace);
}
finally
{
PostgresHelper.CheckCloseConneciton(conn);
}
}
/// <summary>
/// 检查数据库连接状态
/// </summary>
/// <param name="connstr"></param>
/// <returns></returns>
public bool CheckConnection(string connstr)
{
OdbcConnection conn = PostgresHelper.GetOdbcConnection(connstr);
try
{
PostgresHelper.CheckConnection(conn);
return(true);
}
catch
{
return(false);
}
finally
{
PostgresHelper.CheckCloseConneciton(conn);
}
}
/// <summary>
/// 获取下一台车VIN码
/// </summary>
/// <param name="vin"></param>
public string GetNextVIN(string vin)
{
string result = string.Empty;
OdbcConnection conn = PostgresHelper.GetOdbcConnection(this.connectionString);
try
{
PostgresHelper.CheckConnection(conn);
string sql = string.Empty;
sql = string.Format("select \"updateTime\" from \"GAC_New_VCU\".\"T_MTOC\" where \"vin\"= '" + vin + "'");
DataTable dtExist = PostgresHelper.ExecuteDataTable(conn, CommandType.Text, sql);
if (dtExist.Rows.Count != 0)
{
sql = string.Format("select * from \"GAC_New_VCU\".\"T_MTOC\" where \"updateTime\"> '" + dtExist.Rows[0][0].ToString() + "' and \"state\" !='2' order by \"updateTime\" asc limit 1");
DataTable dtExistVIN = PostgresHelper.ExecuteDataTable(conn, CommandType.Text, sql);
if (dtExistVIN.Rows.Count != 0)
{
result = dtExistVIN.Rows[0]["vin"].ToString();
}
else
{
logger.Info("不存在下一台车，请核查!");
}
}
else
{
logger.Info("不存在当前VIN");
}
return(result);
}
catch (Exception ex)
{
logger.Error(ex.Message);
return(null);
}
finally
{
PostgresHelper.CheckCloseConneciton(conn);
}
}
/// <summary>
/// 插入TPMSCode
/// </summary>
/// <returns></returns>
public int InsertTPMSCode(string carType, string canind, string baud, string sendAddress, string responseAddress)
{
OdbcConnection conn = PostgresHelper.GetOdbcConnection(this.connectionString);
int result = -1;
try
{
string sql = string.Format("INSERT INTO \"GAC_New_VCU\".\"T_TPMSCodeList\"(\"CarType\", \"CANIND\", \"Baud\", \"SendAddress\",\"ResponseAddress\") VALUES ('{0}', '{1}', '{2}', '{3}', '{4}');", carType, canind, baud, sendAddress, responseAddress);
result = PostgresHelper.ExecuteNonQuery(conn, CommandType.Text, sql);
}
catch (Exception ex)
{
logger.Error(ex.Message + "***" + ex.StackTrace);
}
finally
{
PostgresHelper.CheckCloseConneciton(conn);
}
return(result);
}
/// <summary>
/// 更新RunParam
/// </summary>
/// <param name="id"></param>
/// <param name="value"></param>
/// <returns></returns>
public int UpdateRunParam(string id, string value)
{
OdbcConnection conn = PostgresHelper.GetOdbcConnection(this.connectionString);
int result = -1;
try
{
string sql = string.Format("UPDATE \"GAC_New_VCU\".\"T_RunParam\" SET \"keyvalue\"='{0}' WHERE ID = '{1}';", value, id);
result = PostgresHelper.ExecuteNonQuery(conn, CommandType.Text, sql);
}
catch (Exception ex)
{
throw ex;
}
finally
{
PostgresHelper.CheckCloseConneciton(conn);
}
return(result);
}
/// <summary>
/// 取出数据
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public DataTable GetDataSource(string sql)
{
DataTable dtTable = new DataTable();
OdbcConnection conn = PostgresHelper.GetOdbcConnection(this.connectionString);
try
{
PostgresHelper.CheckConnection(conn);
dtTable = PostgresHelper.ExecuteDataTable(conn, CommandType.Text, sql);
}
catch (Exception ex)
{
throw ex;
}
finally
{
PostgresHelper.CheckCloseConneciton(conn);
}
return(dtTable);
}
/// <summary>
/// 修改当前检测VCU的状态
/// </summary>
/// <param name="state"></param>
public void ChangeState(bool result, string vin)
{
OdbcConnection conn = PostgresHelper.GetOdbcConnection(this.connectionString);
try
{
string state = (result == true) ? "2" : "1";
PostgresHelper.CheckConnection(conn);
string sql = string.Format("update \"GAC_New_VCU\".\"T_MTOC\" set \"state\"='" + state + "' where \"vin\"='" + vin + "'");
PostgresHelper.ExecuteNonQuery(conn, CommandType.Text, sql);
logger.Info(vin + "更新写入状态" + state + "成功!");
}
catch (Exception ex)
{
logger.Error(ex.Message);
}
finally
{
PostgresHelper.CheckCloseConneciton(conn);
}
}
/// <summary>
/// 更新流程
/// </summary>
/// <param name="flowName"></param>
/// <param name="sendCmd"></param>
/// <param name="receiveCmd"></param>
/// <param name="enable"></param>
/// <param name="carType"></param>
/// <param name="sleepTime"></param>
/// <param name="receiveNum"></param>
/// <param name="id"></param>
/// <returns></returns>
public int UpdateDefineFlow(string flowName, string sendCmd, string receiveCmd, string enable, string sleepTime, string receiveNum, string id)
{
OdbcConnection conn = PostgresHelper.GetOdbcConnection(this.connectionString);
int result = -1;
try
{
PostgresHelper.CheckConnection(conn);
string sql = string.Format("UPDATE \"GAC_New_VCU\".\"T_DefineFlow\" SET \"Flowname\"='{0}', \"SendCmd\"='{1}', \"ReceiveCmd\"='{2}', \"Enabled\"='{3}', \"CarType\"='{4}', \"SleepTime\"='{5}', \"ReceiveNum\"='{6}' WHERE \"ID\"='{7}'", flowName, sendCmd, receiveCmd, enable, sleepTime, receiveNum, id);
result = PostgresHelper.ExecuteNonQuery(conn, CommandType.Text, sql);
}
catch (Exception ex)
{
logger.Error(ex.Message + "***" + ex.StackTrace);
}
finally
{
PostgresHelper.CheckCloseConneciton(conn);
}
return(result);
}
/// <summary>
/// 删除TPMS特制码
/// </summary>
/// <returns></returns>
public int DeleteTPMSCode(string id)
{
OdbcConnection conn = PostgresHelper.GetOdbcConnection(this.connectionString);
int result = -1;
try
{
PostgresHelper.CheckConnection(conn);
string sql = string.Format("DELETE FROM \"GAC_New_VCU\".\"T_TPMSCodeList\" WHERE \"ID\" = '{0}';", id);
result = PostgresHelper.ExecuteNonQuery(conn, CommandType.Text, sql);
}
catch (Exception ex)
{
logger.Error(ex.Message + "***" + ex.StackTrace);
}
finally
{
PostgresHelper.CheckCloseConneciton(conn);
}
return(result);
}
/// <summary>
/// 检查TPMS特制码是否存在
/// </summary>
/// <param name="tpmsCode"></param>
/// <param name="carType"></param>
/// <returns></returns>
public int ExistTPMSCode(string carType)
{
OdbcConnection conn = PostgresHelper.GetOdbcConnection(this.connectionString);
int result = -1;
try
{
PostgresHelper.CheckConnection(conn);
string sql = string.Format("SELECT count(0) FROM \"GAC_New_VCU\".\"T_TPMSCodeList\" where \"CarType\"='{0}';", carType);
DataTable dtExist = PostgresHelper.ExecuteDataTable(conn, CommandType.Text, sql);
result = int.Parse(dtExist.Rows[0][0] + "");
}
catch (Exception ex)
{
logger.Error(ex.Message + "***" + ex.StackTrace);
}
finally
{
PostgresHelper.CheckCloseConneciton(conn);
}
return(result);
}
public FhirbaseFunction WithString(string value)
{
this._parameters.Add(PostgresHelper.TextParam(value));
return(this);
}
public FhirbaseFunction WithStringArray(string[] stringArray)
{
this._parameters.Add(PostgresHelper.StringArray(stringArray));
return(this);
}
public FhirbaseFunction WithInt32(int value)
{
this._parameters.Add(PostgresHelper.Int(value));
return(this);
}
/// <summary>
/// Call FHIRbase function and cast value
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="func"></param>
/// <returns></returns>
public T Cast <T>()
{
return((T)PostgresHelper.Function(this.ConnectionString, this._name, this._parameters.ToArray()));
}
/// <summary>
/// 获取MTOC码和对应写入的bin文件
/// </summary>
/// <param name="element"></param>
public string GetMTOC(string element, string vincode, VCUconfig config)
{
OdbcConnection conn = PostgresHelper.GetOdbcConnection(this.connectionString);
string res = string.Empty;
try
{
this.vin = vincode;
PostgresHelper.CheckConnection(conn);
//先查找第一个写入状态为-1的件，插入零件号并获取MTOC码和id
string mtoc = string.Empty;
int result = -1;
string sql = string.Empty;
sql = string.Format("select * from \"GAC_New_VCU\".\"T_MTOC\" where \"vin\"='" + vincode + "'");
DataTable dtExist = PostgresHelper.ExecuteDataTable(conn, CommandType.Text, sql);
if (dtExist.Rows.Count != 0)
{
mtoc = dtExist.Rows[0]["mtoc"].ToString();
id = int.Parse(dtExist.Rows[0]["id"].ToString());
vin = dtExist.Rows[0]["vin"].ToString();
//修改对应零件号以及写入状态为0（开始写入）
sql = string.Format("update \"GAC_New_VCU\".\"T_MTOC\" set \"element\"='" + element + "',\"state\"='0' where \"vin\"='" + vin + "'");
result = PostgresHelper.ExecuteNonQuery(conn, CommandType.Text, sql);
logger.Info(vin + "更新零件号" + element + "成功,对应mtoc码为:" + mtoc);
//查找MTOC码对应的bin文件以及软、硬件版本号等信息
sql = string.Format("select * from \"GAC_New_VCU\".\"T_VCUConfig\" where \"mtoc\"='" + mtoc + "' ");
DataTable Exist = PostgresHelper.ExecuteDataTable(conn, CommandType.Text, sql);
if (Exist.Rows.Count != 0)
{
config.DriverName = Exist.Rows[0]["drivername"] + "";
config.DriverPath = Exist.Rows[0]["driverpath"] + "";
config.BinName = Exist.Rows[0]["binname"] + "";
config.BinPath = Exist.Rows[0]["binpath"] + "";
config.CalName = Exist.Rows[0]["calname"] + "";
config.CalPath = Exist.Rows[0]["calpath"] + "";
config.SoftWareVersion = Exist.Rows[0]["softwareversion"] + "";
config.HardWareCode = Exist.Rows[0]["hardwarecode"] + "";
config.SW = Exist.Rows[0]["SW"] + "";
config.HW = Exist.Rows[0]["HW"] + "";
config.ElementNum = Exist.Rows[0]["elementNum"] + "";
logger.Info("下载MTOC码为" + mtoc + "对应相关信息成功!");
}
else
{
logger.Info("不存在MTOC码为" + mtoc + "对应的相关信息!");
res = "不存在MTOC码为" + mtoc + "对应的相关信息!";
}
}
else
{
logger.Info("不存在未写入的VCU");
res = "不存在未写入的VCU";
}
}
catch (Exception ex)
{
logger.Error(ex.Message);
res = ex.Message;
}
finally
{
PostgresHelper.CheckCloseConneciton(conn);
}
return(res);
}
public FhirbaseFunction WithJson(string jsonValue)
{
this._parameters.Add(PostgresHelper.Json(jsonValue));
return(this);
}
/// <summary>
/// 保存到本地数据库
/// </summary>
/// <param name="car"></param>
public int SaveLocalResult(string element, bool state, string vin, string driver, string write, string cal)
{
int result = 0;
string sql = string.Empty;
try
{
sql = string.Format("select * from \"GAC_New_VCU\".\"T_VCUConfig\" where \"mtoc\" in (select \"mtoc\" from \"GAC_New_VCU\".\"T_MTOC\" where \"vin\"='" + vin + "')");
DataTable exist = PostgresHelper.ExecuteDataTable(this.connectionString, CommandType.Text, sql);
if (exist.Rows.Count != 0)
{
string mtoc = exist.Rows[0]["mtoc"].ToString();
string flashBin = exist.Rows[0]["drivername"].ToString();
string writeBin = exist.Rows[0]["binname"].ToString();
string calBin = exist.Rows[0]["calname"].ToString();
string elementCode = exist.Rows[0]["elementNum"].ToString();
string softwareCode = exist.Rows[0]["softwareversion"].ToString();
string sign = exist.Rows[0]["sign"].ToString();
int teststate = (state) ? 2 : 1;
int isprint = (state) ? 1 : 0;
sql = string.Format("INSERT INTO \"GAC_New_VCU\".\"T_Result\"(vin, mtoc, \"flashBin\", \"writeBin\", \"calBin\",softwareversion, testtime, teststate,isprint,\"tracyCode\",\"sign\") VALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}');", vin, mtoc, flashBin, writeBin, calBin, softwareCode
, DateTime.Now, teststate, isprint, element, sign);
}
else
{
sql = string.Format("select * from \"GAC_New_VCU\".\"T_MTOC\" where \"vin\"= '" + vin + "'");
DataTable dtVIN = PostgresHelper.ExecuteDataTable(this.connectionString, CommandType.Text, sql);
if (dtVIN.Rows.Count != 0)
{
sql = string.Format("select * from \"GAC_New_VCU\".\"T_VCUConfig\" where \"mtoc\"='" + dtVIN.Rows[0]["mtoc"].ToString() + "'");
DataTable save = PostgresHelper.ExecuteDataTable(this.connectionString, CommandType.Text, sql);
if (save.Rows.Count != 0)
{
string mtoc = save.Rows[0]["mtoc"].ToString();
string flashBin = driver;
string writeBin = write;
string calBin = cal;
string elementCode = save.Rows[0]["elementNum"].ToString();
string softwareCode = save.Rows[0]["softwareversion"].ToString();
string sign = save.Rows[0]["sign"].ToString();
int teststate = (state) ? 2 : 1;
int isprint = (state) ? 1 : 0;
sql = string.Format("INSERT INTO \"GAC_New_VCU\".\"T_Result\"(vin, mtoc, \"flashBin\", \"writeBin\", \"calBin\",softwareversion, testtime, teststate,isprint,\"tracyCode\",\"sign\") VALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}');", vin, mtoc, flashBin, writeBin, calBin, softwareCode
, DateTime.Now, teststate, isprint, element, sign);
}
}
else
{
string flashBin = driver;
string writeBin = write;
string calBin = cal;
int teststate = (state) ? 2 : 1;
int isprint = (state) ? 1 : 0;
sql = string.Format("INSERT INTO \"GAC_New_VCU\".\"T_Result\"(vin,\"flashBin\", \"writeBin\", \"calBin\",testtime, teststate,isprint,\"tracyCode\") VALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}');", vin, flashBin, writeBin, calBin, DateTime.Now, teststate, isprint, element);
}
}
result = PostgresHelper.ExecuteNonQuery(this.connectionString, CommandType.Text, sql);
}
catch (Exception ex)
{
logger.Error(ex.Message + "***" + ex.StackTrace);
}
return(result);
}