public static object ExecuteScalarOnDB(string sSQL, string s_ConnectionString)
{
System.Data.Odbc.OdbcConnection QConnection = null;
System.Data.Odbc.OdbcCommand QCommand = null;
try
{
QConnection = new System.Data.Odbc.OdbcConnection(s_ConnectionString);
QCommand = new System.Data.Odbc.OdbcCommand(sSQL, QConnection);
QConnection.Open();
return(QCommand.ExecuteScalar());
}
finally
{
if (QCommand != null)
{
QCommand.Dispose();
}
QCommand = null;
if (QConnection != null && QConnection.State != System.Data.ConnectionState.Closed)
{
QConnection.Close();
}
if (QConnection != null)
{
QConnection.Dispose();
}
QConnection = null;
}
}
void AddID(String UserID, String UserName, string IDtype)
{
string strPath = HttpContext.Current.Request.PhysicalApplicationPath + "LineID.csv";// "C:\\Test.csv";
System.IO.FileInfo file;
FileStream fs;
StreamWriter sw;
//CHECK LineID.csv exist & create lineID.csv
if (File.Exists(strPath) == false)
{
file = new FileInfo(strPath);
fs = new FileStream(strPath, FileMode.Append, FileAccess.Write);
sw = new StreamWriter(fs, Encoding.Default);
sw.Write("LineID,Name,type,CreateTime", true);
sw.Write(Environment.NewLine, true);
sw.Close( );
}
string csvStr = "";
System.Data.Odbc.OdbcConnection csvCn;
System.Data.Odbc.OdbcCommand csvCmd;
System.Data.Odbc.OdbcDataReader csvRes;
csvCn = new System.Data.Odbc.OdbcConnection(@"Driver={Microsoft Text Driver (*.txt; *.csv)};extensions=csv,txt;DBQ=" + HttpContext.Current.Request.PhysicalApplicationPath + ";");
csvCn.Open( );
csvStr = @"SELECT Name FROM LineID.csv WHERE LineID = '" + UserID + "'";
csvCmd = new System.Data.Odbc.OdbcCommand(csvStr, csvCn);
var tmp = csvCmd.ExecuteScalar( );
if (tmp == null)
{
//add id
file = new FileInfo(strPath);
fs = new FileStream(strPath, FileMode.Append, FileAccess.Write);
sw = new StreamWriter(fs, Encoding.Default);
sw.Write(UserID + "," + UserName + "," + IDtype + "," + DateTime.Now.ToString("yyyyMMdd_HHmmssfff"), true);
sw.Write(Environment.NewLine, true);
sw.Close( );
}
else if (tmp.ToString( ) != UserName.ToString( ) && IDtype == "UserID")
{
//add id
file = new FileInfo(strPath);
fs = new FileStream(strPath, FileMode.Append, FileAccess.Write);
sw = new StreamWriter(fs, Encoding.Default);
sw.Write(UserID + "," + UserName + "," + IDtype + "," + DateTime.Now.ToString("yyyyMMdd_HHmmssfff"), true);
sw.Write(Environment.NewLine, true);
sw.Close( );
}
else
{
}
csvCn.Close( );
}
public int DesmaterializaAvaliacao(cAvaliacao obj)
{
string sql;
try
{
sql = "INSERT INTO avaliacao(id_pessoa, data, idade, peso, altura, nivel_atividade, gordura, vo2, ";
sql += " desc_atividade, observacoes, fc_max_obtida, fc_min_obtida, fc_media_obtida, fc_basal, tmb) ";
sql += " values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
sqlConn = new System.Data.Odbc.OdbcConnection(connString);
sqlConn.Open();
sqlCmd = new System.Data.Odbc.OdbcCommand(sql, sqlConn);
sqlCmd.Parameters.Add("", System.Data.Odbc.OdbcType.Int).Value = obj.id_pessoa;
sqlCmd.Parameters.Add("", System.Data.Odbc.OdbcType.DateTime).Value = obj.data_hora;
sqlCmd.Parameters.Add("", System.Data.Odbc.OdbcType.Int).Value = obj.idade;
sqlCmd.Parameters.Add("", System.Data.Odbc.OdbcType.Double).Value = obj.peso;
sqlCmd.Parameters.Add("", System.Data.Odbc.OdbcType.Double).Value = obj.altura;
sqlCmd.Parameters.Add("", System.Data.Odbc.OdbcType.Int).Value = obj.nivel_atividade;
sqlCmd.Parameters.Add("", System.Data.Odbc.OdbcType.Double).Value = obj.gordura;
sqlCmd.Parameters.Add("", System.Data.Odbc.OdbcType.Double).Value = obj.vo2;
sqlCmd.Parameters.Add("", System.Data.Odbc.OdbcType.VarChar, 255).Value = obj.atividade;
sqlCmd.Parameters.Add("", System.Data.Odbc.OdbcType.Text).Value = obj.observacoes;
sqlCmd.Parameters.Add("", System.Data.Odbc.OdbcType.Int).Value = obj.fc_max;
sqlCmd.Parameters.Add("", System.Data.Odbc.OdbcType.Int).Value = obj.fc_min;
sqlCmd.Parameters.Add("", System.Data.Odbc.OdbcType.Int).Value = obj.fc_med;
sqlCmd.Parameters.Add("", System.Data.Odbc.OdbcType.Int).Value = obj.fc_basal;
sqlCmd.Parameters.Add("", System.Data.Odbc.OdbcType.Int).Value = obj.tmb;
if (sqlCmd.ExecuteNonQuery() > 0)
{
sql = "select @@identity";
sqlCmd = new System.Data.Odbc.OdbcCommand(sql, sqlConn);
int id = (int)sqlCmd.ExecuteScalar();
sqlConn.Close();
return(id);
}
else
{
sqlConn.Close();
return(-1);
}
}
catch (Exception Ex)
{
return(-1);
}
}
/// <summary>
/// 使用离线数据库连接器，无需使用Open方法，获取数据库当前时间
/// </summary>
public override DateTime GetDateTimeNow()
{
DateTime ret = DateTime.Now;
using (System.Data.Odbc.OdbcConnection con = new System.Data.Odbc.OdbcConnection(this.ConnectionText))
{
using (System.Data.Odbc.OdbcCommand cmd = new System.Data.Odbc.OdbcCommand("select now()", con))
{
con.Open();
ret = Convert.ToDateTime(cmd.ExecuteScalar());
con.Close();
}
}
return ret;
}
/// <summary>
/// 使用离线数据库连接器，无需使用Open方法，获取数据库当前时间
/// </summary>
public override DateTime GetDateTimeNow()
{
DateTime ret = DateTime.Now;
using (System.Data.Odbc.OdbcConnection con = new System.Data.Odbc.OdbcConnection(this.ConnectionText))
{
using (System.Data.Odbc.OdbcCommand cmd = new System.Data.Odbc.OdbcCommand("select now()", con))
{
con.Open();
ret = Convert.ToDateTime(cmd.ExecuteScalar());
con.Close();
}
}
return(ret);
}
private static void loadRangeRule()
{
System.Data.Odbc.OdbcConnection cn = new System.Data.Odbc.OdbcConnection(Comm.DB2.Db2.db2ConnectionStr);
System.Data.Odbc.OdbcCommand cmd = new System.Data.Odbc.OdbcCommand();
System.Data.Odbc.OdbcDataReader rd;
cmd.Connection = cn;
int num = 0;
try
{
cn.Open();
cmd.CommandText = "select count(*) from tblVDValidRange";
num = System.Convert.ToInt32(cmd.ExecuteScalar());
rangeDatas = new RangeData[num];
cmd.CommandText = "select type,maxvalue,minvalue from tblVDValidRange";
rd = cmd.ExecuteReader();
int i = 0;
while (rd.Read())
{
try
{
rangeDatas[i++] = new RangeData(System.Convert.ToString(rd[0]).Trim(), System.Convert.ToInt32(rd[1]), System.Convert.ToChar(rd[2]));
}
catch (Exception ex1)
{
RemoteInterface.ConsoleServer.WriteLine(ex1.Message + ex1.StackTrace);
}
//Value1[i] = System.Convert.ToInt32(rd[1]);
//type2[i] = System.Convert.ToChar(rd[2]);
//Value2[i] = System.Convert.ToInt32(rd[3]);
}
rd.Close();
}
catch (Exception ex)
{
RemoteInterface.ConsoleServer.WriteLine(ex.Message + ex.StackTrace);
}
finally
{
cn.Close();
}
}
public static int getAlarmId()
{
System.Data.Odbc.OdbcConnection cn = new System.Data.Odbc.OdbcConnection(Global.Db2ConnectionString);
System.Data.Odbc.OdbcCommand cmd = new System.Data.Odbc.OdbcCommand("SELECT NEXTVAL FOR DB2INST1.SEQ_ALARM FROM SYSIBM.SYSDUMMY1 ");
cmd.Connection = cn;
try
{
cn.Open();
return System.Convert.ToInt32( cmd.ExecuteScalar());
}
catch (Exception ex)
{
ConsoleServer.WriteLine(ex.Message+","+ex.StackTrace);
throw ex;
}
finally
{
try
{
cn.Close();
}
catch { ;}
}
}
public static Host.Event.EventMode getEventMode(int alarmClass)
{
System.Data.Odbc.OdbcConnection cn = new System.Data.Odbc.OdbcConnection(Global.Db2ConnectionString);
System.Data.Odbc.OdbcCommand cmd = new System.Data.Odbc.OdbcCommand("SELECT login_mode From tblSysAlarmConfig where alarmClass="+alarmClass);
cmd.Connection = cn;
try
{
cn.Open();
return ( Host.Event.EventMode)System.Convert.ToInt32(cmd.ExecuteScalar());
}
catch (Exception ex)
{
ConsoleServer.WriteLine(ex.Message + "," + ex.StackTrace);
throw ex;
}
finally
{
try
{
cn.Close();
}
catch { ;}
}
}
/// <summary>
/// 同步批号
/// </summary>
/// <param name="dset"></param>
/// <param name="db"></param>
/// <returns></returns>
public static string SaveKcph(DataSet dset, RelationalDatabase db)
{
try
{
DataTable tb = dset.Tables[0];
string ssql = "";
List <System.String[]> listUpdate = new List <System.String[]>();
List <System.String[]> listInsert = new List <System.String[]>();
for (int nrow = 0; nrow <= tb.Rows.Count - 1; nrow++)
{
#region 变量
DataRow row = tb.Rows[nrow];
ParameterEx[] parameters = new ParameterEx[13];
string _sdeptid = HisFunctions.ConvertNewhisidToOldHisid(HisFunctions.DataMapType.JC_DEPT_PROPERTY, Convertor.IsNull(row["deptid"], ""), db);
if (_sdeptid == "")
{
throw new Exception("没有找到科室匹配信息");
}
parameters[0].Text = "yfh";
parameters[0].DataType = System.Data.DbType.String;
parameters[0].Value = _sdeptid;
string yppc = _sdeptid.ToString() + row["cjid"].ToString();
// yppm=left('0000000000',
string temp = "0000000000";
int N = yppc.Length;
if (N < 10)
{
yppc = temp.Substring(0, 10 - N) + yppc;
}
yppc = "10" + yppc;
parameters[1].Text = "yppc";
parameters[1].DataType = System.Data.DbType.String;
parameters[1].Value = yppc;
string cjid = row["cjid"].ToString();
parameters[2].Text = "ypbm";
parameters[2].Value = row["cjid"];
parameters[3].Text = "ypmc";
parameters[3].DataType = System.Data.DbType.String;
parameters[3].Value = row["s_yppm"].ToString().Replace("'", "");
parameters[4].Text = "czgg";
parameters[4].DataType = System.Data.DbType.String;
parameters[4].Value = row["s_ypgg"].ToString().Replace("'", "");
parameters[5].Text = "dw";
parameters[5].DataType = System.Data.DbType.String;
parameters[5].Value = Convertor.IsNull(row["s_zxdw"], "");
parameters[6].Text = "cjbm";
parameters[6].Value = row["sccj"].ToString();
parameters[7].Text = "grdj";
parameters[7].Value = row["grdj"].ToString();
parameters[8].Text = "pfj";
parameters[8].Value = row["pfj"].ToString();
parameters[9].Text = "lsj";
parameters[9].Value = row["lsj"].ToString();
parameters[10].Text = "ccrq";
parameters[10].DataType = System.Data.DbType.String;
parameters[10].Value = Convert.ToDateTime(row["djsj"]).ToString("yyyy-MM-dd HH:mm:ss");
parameters[11].Text = "sxrq";
parameters[11].DataType = System.Data.DbType.String;
parameters[11].Value = Convert.ToDateTime(row["ypxq"]).ToString("yyyy-MM-dd HH:mm:ss");
parameters[12].Text = "sl";
parameters[12].Value = Convertor.IsNull(row["kcl"], "");
#endregion
#region 插入语句
ssql = "insert into yk_kcb(";
for (int i = 0; i <= parameters.Length - 1; i++)
{
ssql = ssql + parameters[i].Text + ",";
}
ssql = ssql.Substring(0, ssql.Length - 1) + ")";
ssql = ssql + "values(";
for (int i = 0; i <= parameters.Length - 1; i++)
{
if (parameters[i].DataType != null)
{
ssql = ssql + "'" + TrasenHIS.DAL.BaseDal.GetEncodingStringToInforMix(parameters[i].Value.ToString()) + "',";//TrasenHIS.DAL.BaseDal.GetEncodingStringToInforMix(parameters[i].Value.ToString())
}
else
{
ssql = ssql + parameters[i].Value + ",";
}
}
ssql = ssql.Substring(0, ssql.Length - 1) + ")";
//用于记录生成的SQL语句，医嘱主键，操作类型。。在一面的事务中一次性处理
System.String[] str_insert = { row["id"].ToString(), _sdeptid, yppc, cjid, ssql, "add" };
listInsert.Add(str_insert);
#endregion
#region 更新语句
ssql = "update yk_kcb set ";
for (int i = 0; i <= parameters.Length - 1; i++)
{
if (parameters[i].DataType != null)
{
ssql = ssql + " " + parameters[i].Text + "= '" + TrasenHIS.DAL.BaseDal.GetEncodingStringToInforMix(parameters[i].Value.ToString()) + "',";//TrasenHIS.DAL.BaseDal.GetEncodingStringToInforMix(parameters[i].Value.ToString())
}
else
{
ssql = ssql + " " + parameters[i].Text + "= " + parameters[i].Value + ",";
}
}
ssql = ssql.Substring(0, ssql.Length - 1) + "";
ssql = ssql + " where yfh='" + _sdeptid + "' and yppc='" + yppc + "' and ypbm=" + cjid + "";
//用于记录生成的SQL语句，医嘱主键，操作类型。。在一面的事务中一次性处理
System.String[] str_update = { row["id"].ToString(), _sdeptid, yppc, cjid, ssql, "update" };
listUpdate.Add(str_update);
#endregion
}
#region 老HIS事务处理
System.Data.Odbc.OdbcConnection connection = new System.Data.Odbc.OdbcConnection(DAL.BaseDal.oldhis_constr);
System.Data.Odbc.OdbcTransaction tx = null;
System.Data.Odbc.OdbcCommand cmd = new System.Data.Odbc.OdbcCommand();
try
{
connection.Open();
cmd.Connection = connection;
tx = connection.BeginTransaction();
cmd.Transaction = tx;
for (int i = 0; i <= listInsert.Count - 1; i++)
{
//插入和更新老HIS
ssql = "select * from yk_kcb where yfh='" + listInsert[i][1].ToString() + "' and yppc='" + listInsert[i][2].ToString() + "' and ypbm=" + listInsert[i][3].ToString() + "";
cmd.CommandText = ssql;
object o = cmd.ExecuteScalar();
if (o != null)
{
cmd.CommandText = listUpdate[i][4];
}
else
{
cmd.CommandText = listInsert[i][4];
}
cmd.ExecuteNonQuery();
}
tx.Commit();
cmd.Dispose();
connection.Close();
}
catch (Exception ex)
{
//事务回滚
tx.Rollback();
cmd.Dispose();
connection.Close();
tx.Dispose();
throw new Exception(ex.Message);
}
#endregion
#region 回填新HIS记录
for (int nrow = 0; nrow <= tb.Rows.Count - 1; nrow++)
{
ssql = "update EVENTLOG set FINISH=1 ,FINISH_DATE='" + DateTime.Now.ToString() + "' where EVENT='KCBH' AND BIZID='" + tb.Rows[nrow]["ID"].ToString() + "'";
db.DoCommand(ssql);
}
#endregion
System.String[] str = { "0", "保存成功" };
return(HisFunctions.GetResponseString("SaveKcph", str));
}
catch (Exception err)
{
throw err;
}
}
public void load_vd_travel_mapping_table()
{
string[][] newTable;
System.Data.Odbc.OdbcConnection cn = new System.Data.Odbc.OdbcConnection(Global.Db2ConnectionString);
try
{
System.Data.Odbc.OdbcCommand cmd = new System.Data.Odbc.OdbcCommand(string.Format("select count(*) from tblGroupUnitVD where unitID='{0}' ", unitid));
cmd.Connection = cn;
cn.Open();
newTable = new string[System.Convert.ToInt32(cmd.ExecuteScalar())][];
cmd.CommandText = string.Format("select devicename1,DeviceName2,DeviceName3,DeviceName4 from tblGroupUnitVD where unitid='{0}' order by priority", unitid);
System.Data.Odbc.OdbcDataReader rd = cmd.ExecuteReader();
int inx = 0;
while (rd.Read())
{
System.Collections.ArrayList ary = new System.Collections.ArrayList();
string devName1, devName2, devName3, devName4;
devName1 = System.Convert.ToString(rd[0]);
if (devName1 != "")
ary.Add(devName1);
devName2 = System.Convert.ToString(rd[1]);
if (devName2 != "")
ary.Add(devName2);
devName3 = System.Convert.ToString(rd[2]);
if (devName3 != "")
ary.Add(devName3);
devName4 = System.Convert.ToString(rd[3]);
if (devName4 != "")
ary.Add(devName4);
newTable[inx] = new string[ary.Count];
for (int i = 0; i < ary.Count; i++)
newTable[inx][i] = ary[i].ToString();
inx++;
}
vd_travel_mapping_table = newTable;
}
catch (Exception ex)
{
RemoteInterface.ConsoleServer.WriteLine(ex.Message + ex.StackTrace);
}
finally
{
cn.Close();
}
}
void getGID()
{
System.DateTime dt = System.DateTime.Today;
string sql = "select gid from tblavitimegroup where month={0}";
System.Data.Odbc.OdbcConnection cn = new System.Data.Odbc.OdbcConnection(Global.Db2ConnectionString);
System.Data.Odbc.OdbcCommand cmd = new System.Data.Odbc.OdbcCommand(string.Format(sql,DateTime.Now.Month));
try
{
cn.Open();
cmd.Connection = cn;
_gid = System.Convert.ToInt32(cmd.ExecuteScalar());
}
catch (Exception ex)
{
ConsoleServer.WriteLine(ex.Message + "," + ex.StackTrace);
}
finally
{
cn.Close();
}
}
void getClassId()
{
System.DateTime dt = System.DateTime.Today;
string sql="select classid from tblAnnualDate where datecode='{0}'";
System.Data.Odbc.OdbcConnection cn = new System.Data.Odbc.OdbcConnection(Global.Db2ConnectionString);
System.Data.Odbc.OdbcCommand cmd=new System.Data.Odbc.OdbcCommand(string.Format(sql,DbCmdServer.getTimeStampString(dt)));
try
{
cn.Open();
cmd.Connection = cn;
_classid = System.Convert.ToInt32(cmd.ExecuteScalar());
}
catch (Exception ex)
{
ConsoleServer.WriteLine(ex.Message + "," + ex.StackTrace);
}
finally
{
cn.Close();
}
// System.Data.Odbc.OdbcDataReader
}
public void ReloadDeviceLocation()
{
System.Data.Odbc.OdbcConnection cn = new System.Data.Odbc.OdbcConnection(Global.Db2ConnectionString);
System.Data.Odbc.OdbcCommand cmd = new System.Data.Odbc.OdbcCommand("select location from tbldeviceconfig where devicename='"+this.deviceName+"'");
cmd.Connection = cn;
try
{
cn.Open();
this.location= cmd.ExecuteScalar().ToString();
}
catch (Exception ex)
{
throw ex;
}
finally
{
cn.Close();
}
}
public int AddPerformanceCubeResult(PerformanceCubeResult result)
{
if (result.FramesPerSecond > 65)
return -1;
int databaseId = -1;
System.Data.Odbc.OdbcConnection conn = createSQLConnection();
System.Data.Odbc.OdbcTransaction transaction = null;
try
{
string mySelectQuery =
string.Format(@"SELECT `id` , `FramesPerSecond` FROM `PerformanceGLCubeResults`
WHERE `DBDeviceId` ={0} AND `NumberTriangles` ={1} AND `isMonoTouch` ={2}",
result.DeviceDatabaseId, result.NumberOfTriangles, result.IsMonoTouch ? 1 : 0);
conn.Open();
System.Data.Odbc.OdbcCommand mySelectCommand = new System.Data.Odbc.OdbcCommand(mySelectQuery, conn);
System.Data.Odbc.OdbcDataReader reader = mySelectCommand.ExecuteReader();
if (reader.Read())
{
System.Data.Odbc.OdbcCommand myUpdateCommand = new System.Data.Odbc.OdbcCommand(mySelectQuery, conn);
databaseId = Convert.ToInt32(reader["id"]);
string myUpdateQuery = string.Format(@"UPDATE `billholmes54`.`PerformanceGLCubeResults` SET `FramesPerSecond` = '{1}'
WHERE `PerformanceGLCubeResults`.`id` ={0}
AND `PerformanceGLCubeResults`.`FramesPerSecond` <{1} LIMIT 1 ;",
databaseId, result.FramesPerSecond);
myUpdateCommand.CommandText = myUpdateQuery;
myUpdateCommand.ExecuteNonQuery();
}
else
{
transaction = conn.BeginTransaction();
System.Data.Odbc.OdbcCommand myInsertCommand = new System.Data.Odbc.OdbcCommand(mySelectQuery, conn, transaction);
string myInsertQuery =
string.Format(@"INSERT INTO `billholmes54`.`PerformanceGLCubeResults`
(`id`, `DBDeviceId`, `NumberTriangles`, `FramesPerSecond`, `isMonoTouch`)
VALUES (NULL, '{0}', '{1}', '{2}', '{3}');",
result.DeviceDatabaseId, result.NumberOfTriangles, result.FramesPerSecond, result.IsMonoTouch ? "1" : "0");
myInsertCommand.CommandText = myInsertQuery;
myInsertCommand.ExecuteNonQuery();
myInsertCommand.CommandText = "select last_insert_id();";
databaseId = Convert.ToInt32(myInsertCommand.ExecuteScalar());
transaction.Commit();
}
}
catch (Exception)
{
if (transaction != null)
transaction.Rollback();
throw;
}
finally
{
conn.Close();
}
return databaseId;
}
public int AddDevice(FullDeviceInfo deviceInfo)
{
int databaseId = -1;
System.Data.Odbc.OdbcConnection conn = createSQLConnection();
System.Data.Odbc.OdbcTransaction transaction = null;
try
{
string selectQuery = string.Format("SELECT `DatabaseId` FROM `PerformanceAppDeviceInfo` WHERE `UniqueId` = '{0}'", deviceInfo.UniqueId);
conn.Open();
System.Data.Odbc.OdbcCommand selectCommand = new System.Data.Odbc.OdbcCommand(selectQuery, conn);
try
{
databaseId = (int)selectCommand.ExecuteScalar();
}
catch (Exception)
{
}
if (databaseId == -1)
{
string myInsertQuery =
string.Format(@"INSERT INTO `billholmes54`.`PerformanceAppDeviceInfo`
(`DatabaseId`, `UniqueId`, `SystemName`, `ModelName`, `UIIdion`, `SpecificHWVersion`, `OSName`, `OSVersion`, `OwnerName`)
VALUES (NULL, '{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}');",
deviceInfo.UniqueId, deviceInfo.SystemName, deviceInfo.ModelName, deviceInfo.UIIdion, deviceInfo.SpecificHWVersion,
deviceInfo.OSName, deviceInfo.OSVersion, deviceInfo.OwnerName);
transaction = conn.BeginTransaction();
System.Data.Odbc.OdbcCommand insertCommand = new System.Data.Odbc.OdbcCommand(myInsertQuery, conn, transaction);
insertCommand.ExecuteNonQuery();
insertCommand.CommandText = "select last_insert_id();";
databaseId = Convert.ToInt32(insertCommand.ExecuteScalar());
transaction.Commit();
}
else
{
string myInsertQuery =
string.Format(@"UPDATE `billholmes54`.`PerformanceAppDeviceInfo`
SET `SystemName` = '{0}', `ModelName` = '{1}',
`UIIdion` = '{2}', `SpecificHWVersion` = '{3}',
`OSName` = '{4}', `OSVersion` = '{5}', `OwnerName` = '{6}'
WHERE `PerformanceAppDeviceInfo`.`DatabaseId` = {7} LIMIT 1;",
deviceInfo.SystemName, deviceInfo.ModelName, deviceInfo.UIIdion, deviceInfo.SpecificHWVersion,
deviceInfo.OSName, deviceInfo.OSVersion, deviceInfo.OwnerName, databaseId);
System.Data.Odbc.OdbcCommand updateCommand = new System.Data.Odbc.OdbcCommand(myInsertQuery, conn);
updateCommand.ExecuteNonQuery();
}
}
catch (Exception)
{
if (transaction != null)
transaction.Rollback();
throw;
}
finally
{
conn.Close();
}
return databaseId;
}
public int AddPerformanceMatrixTestResult(MatrixTestResult result)
{
int databaseId = -1;
System.Data.Odbc.OdbcConnection conn = createSQLConnection();
System.Data.Odbc.OdbcTransaction transaction = null;
try
{
string mySelectQuery =
string.Format("SELECT `id` FROM `PerformanceMatrixTestResults` WHERE `DBDeviceId` ={0} AND `isMonoTouch` ={1}",
result.DeviceDatabaseId, result.IsMonoTouch ? 1 : 0);
conn.Open();
System.Data.Odbc.OdbcCommand mySelectCommand = new System.Data.Odbc.OdbcCommand(mySelectQuery, conn);
System.Data.Odbc.OdbcDataReader reader = mySelectCommand.ExecuteReader();
if (reader.Read())
{
System.Data.Odbc.OdbcCommand myUpdateCommand = new System.Data.Odbc.OdbcCommand(mySelectQuery, conn);
databaseId = Convert.ToInt32(reader["id"]);
string myUpdateQuery = string.Format(@" update `billholmes54`.`PerformanceMatrixTestResults`
set `CSTestResult` = case
when `CSTestResult` < {1} then '{1}'
else CSTestResult
end,
`CTestResult` = case
when `CTestResult` < {2} then '{2}'
else CTestResult
end,
`BLASTestResult` = case
when `BLASTestResult` < {3} then '{3}'
else BLASTestResult
end
where
id = {0}",
databaseId, result.CSTestResult, result.CTestResult, result.BLASTestResult);
myUpdateCommand.CommandText = myUpdateQuery;
myUpdateCommand.ExecuteNonQuery();
}
else
{
transaction = conn.BeginTransaction();
System.Data.Odbc.OdbcCommand myInsertCommand = new System.Data.Odbc.OdbcCommand(mySelectQuery, conn, transaction);
string myInsertQuery =
string.Format(@"INSERT INTO `billholmes54`.`PerformanceMatrixTestResults`
(`id`, `DBDeviceId`, `CSTestResult`, `CTestResult`, `BLASTestResult`, `isMonoTouch`)
VALUES (NULL, '{0}', '{1}', '{2}', '{3}', '{4}');",
result.DeviceDatabaseId, result.CSTestResult, result.CTestResult, result.BLASTestResult, result.IsMonoTouch ? "1" : "0");
myInsertCommand.CommandText = myInsertQuery;
myInsertCommand.ExecuteNonQuery();
myInsertCommand.CommandText = "select last_insert_id();";
databaseId = Convert.ToInt32(myInsertCommand.ExecuteScalar());
transaction.Commit();
}
}
catch (Exception)
{
if (transaction != null)
transaction.Rollback();
throw;
}
finally
{
conn.Close();
}
return databaseId;
}
public static object ExecuteScalarOnDB(string sSQL, string s_ConnectionString)
{
System.Data.Odbc.OdbcConnection QConnection = null;
System.Data.Odbc.OdbcCommand QCommand = null;
try
{
QConnection = new System.Data.Odbc.OdbcConnection(s_ConnectionString);
QCommand = new System.Data.Odbc.OdbcCommand(sSQL, QConnection);
QConnection.Open();
return QCommand.ExecuteScalar();
}
finally
{
if (QCommand != null) QCommand.Dispose();
QCommand = null;
if (QConnection != null && QConnection.State != System.Data.ConnectionState.Closed) QConnection.Close();
if (QConnection != null) QConnection.Dispose();
QConnection = null;
}
}
public static int getProcessTimeByAlarmClass(int alarmclass)
{
System.Data.Odbc.OdbcConnection cn = new System.Data.Odbc.OdbcConnection(Global.Db2ConnectionString);
System.Data.Odbc.OdbcCommand cmd = new System.Data.Odbc.OdbcCommand("select avgtime from TBLSYSALARMCONFIG where alarmclass="+alarmclass);
cmd.Connection = cn;
try
{
cn.Open();
return System.Convert.ToInt32(cmd.ExecuteScalar());
}
catch (Exception ex)
{
ConsoleServer.WriteLine(ex.Message + "," + ex.StackTrace);
throw ex;
}
finally
{
try
{
cn.Close();
}
catch { ;}
}
}
private bool m_Commit(object oDeviceData, string strCustomState)
{
bool result = false;
GLPBase report = oDeviceData as GLPBase;
string outgoingMessage = string.Empty;
//to nie odpowiedz wiec, sprawdzamy połaczenie
if (report == null)
{
byte[] arrToSend = UTF8Encoding.UTF8.GetBytes(".ttc\r\n");
try
{
SendToDevice(arrToSend, "tcp", true);
UpdateProgress(2, 3, "Connection OK", "");
}
catch
{
UpdateProgress(1, 3, "Waiting for connection...", "");
}
}
else if (report.TerminalStatus != 3) //poleczenie ok brak garmina
{
UpdateProgress(2, 3, "Connection OK but terminal not connected", "");
}
else if (report.TerminalStatus == 3) //polaczenie i garmin ok
{
outgoingMessage = ".tt " + ChatTextID + ",\"" + ChatText + "\"";
if (CannedResponseList.Count > 0)
{
int newFlag = 1;
ChatReader reader = new ChatReader();
foreach (ChatResponseBag response in reader.GetAllResponses())
{
uint iResponseID = (uint)response.ID;
if (CannedResponseList.Contains(iResponseID))
{
outgoingMessage += ",\"" + response.ResponseText + "\"";
}
newFlag = 0;
}
if (newFlag == 1)
{
uint[] CannedResponseID = new uint[CannedResponseList.Count];
for (int i = 0; i < CannedResponseList.Count; i++)
{
CannedResponseID[i] = CannedResponseList[i];
}
string ConnStr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
System.Data.Odbc.OdbcConnection conn = new System.Data.Odbc.OdbcConnection();
conn.ConnectionString = ConnStr;
try
{
conn.Open();
for (int i = 0; i < CannedResponseList.Count; i++)
{
System.Data.Odbc.OdbcCommand cmd = new System.Data.Odbc.OdbcCommand(
@"SELECT body FROM chat_profile_message WHERE chat_profile_message_id = " + CannedResponseID[i] + ";"
, conn);
outgoingMessage += ",\"" + cmd.ExecuteScalar().ToString() + "\"";
}
}
catch (Exception ex)
{
}
finally
{
conn.Close();
}
}
}
outgoingMessage += "\r\n";
byte[] arrToSend = UTF8Encoding.UTF8.GetBytes(outgoingMessage);
try
{
SendToDevice(arrToSend, "tcp", true);
UpdateProgress(3, 3, "Completed", "");
Thread.Sleep(2000);
result = true;
}
catch
{
UpdateProgress(1, 3, "Waiting for connection...", "");
}
}
return(result);
}
void initArray()
{
System.Data.Odbc.OdbcConnection cn = new System.Data.Odbc.OdbcConnection(RemoteInterface.DbCmdServer.getDbConnectStr());
System.Data.Odbc.OdbcCommand cmd = new System.Data.Odbc.OdbcCommand("select count(*) from tbldeviceConfig where location='T' and lineid='N6' and device_type='VD' ");
try
{
cmd.Connection = cn;
cn.Open();
int count = System.Convert.ToInt32(cmd.ExecuteScalar());
vd_devNames = new string[count];
vd_dirs = new int[count];
vd_mile_ks = new int[count];
vd_mile_ms = new int[count];
}
catch (Exception ex)
{
Console.WriteLine(ex.Message + ex.StackTrace);
}
finally
{
cn.Close();
}
}
public void LoadSectionTravelTimeWeight()
{
System.Data.Odbc.OdbcConnection cn = new System.Data.Odbc.OdbcConnection(Global.Db2ConnectionString);
string sql = "";
System.Data.Odbc.OdbcCommand cmd =new System.Data.Odbc.OdbcCommand();
System.Data.Odbc.OdbcDataReader rd;
cmd.Connection=cn;
try
{
cn.Open();
lock (aryNormalDayWeight)
{
int cnt = 0;
aryNormalDayWeight.Clear();
System.DateTime dt=DateTime.Now;
dt=dt.AddHours(-dt.Hour).AddMinutes(-dt.Minute).AddSeconds(-dt.Second);
cmd.CommandText = string.Format("select count(*) from tblSectionTravelTimeWeightHoliday where sectionid='{0}' and date='{1}'",this.sectionId, DbCmdServer.getTimeStampString(dt));
cnt =System.Convert.ToInt32( cmd.ExecuteScalar());
if (cnt == 0)
{
sql = "select SectionId,Start_Timestamp,End_Timestamp,WeightedId,Vd_Percentage,Avi_Percentage,Etc_Percentage,History_Percentage from tblSectionTravelTimeWeight where sectionid='{0}' and holiday={1} order by Start_Timestamp,WeightedId ";
cmd.CommandText = string.Format(sql, this.sectionId, 0); // normalday
}
else
{
sql = "select SectionId,Start_Timestamp,End_Timestamp,WeightedId,Vd_Percentage,Avi_Percentage,Etc_Percentage,History_Percentage from tblSectionTravelTimeWeightHoliday where sectionid='{0}' and date='{1}' order by Start_Timestamp,WeightedId ";
cmd.CommandText = string.Format(sql, this.sectionId, DbCmdServer.getTimeStampString(dt)); // normalday
}
rd = cmd.ExecuteReader();
dt=new DateTime();
SectionTravelTimeWeightSegnment seg = null;
while (rd.Read())
{
DateTime beg, end;
int weightid,vd_percent, avi_percent, etc_percent, his_percent;
beg = System.Convert.ToDateTime(rd[1]);
end = System.Convert.ToDateTime(rd[2]);
weightid = System.Convert.ToInt32(rd[3]);
vd_percent = System.Convert.ToInt32(rd[4]);
avi_percent = System.Convert.ToInt32(rd[5]);
etc_percent = System.Convert.ToInt32(rd[6]);
his_percent = System.Convert.ToInt32(rd[7]);
if (beg != dt)
{
dt = beg;
seg = new SectionTravelTimeWeightSegnment(beg, end);
aryNormalDayWeight.Add(seg);
}
if (weightid < 16)
seg.AddWeight(weightid, vd_percent, avi_percent, etc_percent, his_percent);
}
rd.Close();
aryNormalDayWeight.Sort();
}
/* lock (aryHoliDayWeight)
{
aryHoliDayWeight.Clear();
sql = "select SectionId,Start_Timestamp,End_Timestamp,WeightedId,Vd_Percentage,Avi_Percentage,Etc_Percentage,History_Percentage,Holiday from tblSectionTravelTime where sectionid='{0}' and holiday={1} order by Start_Timestamp,WeightedId ";
cmd.CommandText = string.Format(sql, this.sectionId, 1); // holiday
rd = cmd.ExecuteReader();
DateTime dt = new DateTime();
SectionTravelTimeWeightSegnment seg = null;
while (rd.Read())
{
DateTime beg, end;
int weightid, vd_percent, avi_percent, etc_percent, his_percent;
beg = System.Convert.ToDateTime(rd[1]);
end = System.Convert.ToDateTime(rd[2]);
weightid = System.Convert.ToInt32(rd[3]);
vd_percent = System.Convert.ToInt32(rd[4]);
avi_percent = System.Convert.ToInt32(rd[5]);
etc_percent = System.Convert.ToInt32(rd[6]);
his_percent = System.Convert.ToInt32(rd[7]);
if (beg != dt)
{
dt = beg;
seg = new SectionTravelTimeWeightSegnment(beg, end);
aryHoliDayWeight.Add(seg);
}
if(weightid<16)
seg.AddWeight(weightid, vd_percent, avi_percent, etc_percent, his_percent);
}
rd.Close();
aryHoliDayWeight.Sort();
}
*/
}
catch (Exception ex)
{
ConsoleServer.WriteLine(ex.Message + "," + ex.StackTrace);
}
finally
{
cn.Close();
}
}
private static void loadSpeedOccupancyRule()
{
System.Data.Odbc.OdbcConnection cn = new System.Data.Odbc.OdbcConnection(Comm.DB2.Db2.db2ConnectionStr);
System.Data.Odbc.OdbcCommand cmd = new System.Data.Odbc.OdbcCommand();
System.Data.Odbc.OdbcDataReader rd;
cmd.Connection = cn;
int num = 0;
try
{
cn.Open();
cmd.CommandText = "select count(*) from tblVDInvalidPrinciple";
num = System.Convert.ToInt32(cmd.ExecuteScalar());
Value1 = new int[num];
Value2 = new int[num];
type1 = new char[num];
type2 = new char[num];
cmd.CommandText = "select type1,value1,type2,value2 from tblVDInvalidPrinciple";
rd = cmd.ExecuteReader();
int i = 0;
while (rd.Read())
{
type1[i] = System.Convert.ToChar(rd[0]);
Value1[i] = System.Convert.ToInt32(rd[1]);
type2[i] = System.Convert.ToChar(rd[2]);
Value2[i] = System.Convert.ToInt32(rd[3]);
i++;
}
rd.Close();
}
catch (Exception ex)
{
type1 = new char[1];
type2 = new char[1];
Value1 = new int[1];
Value2 = new int[1];
type1[0] = 'O';
type2[0] = 'S';
Value1[0] = 40;
Value2[0] = 40;
RemoteInterface.ConsoleServer.WriteLine(ex.Message + ex.StackTrace);
}
finally
{
cn.Close();
}
}