private void Form4_Load(object sender, EventArgs e)
{
using (System.Data.Odbc.OdbcConnection conn = new System.Data.Odbc.OdbcConnection(gbCn))
{
try
{
conn.Open();
System.Data.Odbc.OdbcCommand command = new System.Data.Odbc.OdbcCommand("SELECT name, database_id, create_date FROM sys.databases", conn);
System.Data.Odbc.OdbcDataReader reader = command.ExecuteReader();
//agrega nombre de base datos
comboBox3.Items.Clear();
while (reader.Read())
{
comboBox3.Items.Add(reader[0].ToString());
}
}
catch (Exception ex)
{
MessageBox.Show("Ocurrio un error al obtener las bases de datos");
Console.WriteLine("Ocurrio un error al obtener las bases de datos");
Console.WriteLine(ex.Message);
conn.Close();
this.Hide();
}
//MessageBox.Show("ODBC Conexión Satisfatoria");
//onsole.WriteLine("ODBC Connection test PASSED!");
conn.Close();
}
}
public bool DesmaterializaPessoa(cPessoa objPessoa)
{
string sql;
try
{
sql = "INSERT INTO pessoa(nome, sexo, dt_nascimento) 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.VarChar, 255).Value = objPessoa.sNome;
sqlCmd.Parameters.Add("", System.Data.Odbc.OdbcType.VarChar, 1).Value = objPessoa.sSexo;
sqlCmd.Parameters.Add("", System.Data.Odbc.OdbcType.Date).Value = objPessoa.dtDataNasc;
if (sqlCmd.ExecuteNonQuery() > 0)
{
sqlConn.Close();
return(true);
}
else
{
sqlConn.Close();
return(false);
}
}
catch (Exception Ex)
{
return(false);
}
}
public bool DesmaterializaDado(cDados dado)
{
string sql;
try
{
sql = "INSERT INTO dados(id_avaliacao, data_hora, freq_cardiaca) 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 = dado.id_avaliacao;
sqlCmd.Parameters.Add("", System.Data.Odbc.OdbcType.DateTime).Value = dado.data;
sqlCmd.Parameters.Add("", System.Data.Odbc.OdbcType.Int).Value = dado.fc;
if (sqlCmd.ExecuteNonQuery() > 0)
{
sqlConn.Close();
return(true);
}
else
{
sqlConn.Close();
return(false);
}
}
catch (Exception Ex)
{
return(false);
}
}
/// <summary>
/// 取消门诊发药状态
/// </summary>
/// <param name="dset"></param>
/// <param name="db"></param>
/// <returns></returns>
public static string SaveMzQxFyzt(DataSet dset, RelationalDatabase db)
{
try
{
DataTable tb = dset.Tables[0];
string ssql = "";
DataRow row = tb.Rows[0];
string BIZID = row["BIZID"].ToString();
string DH = row["DH"].ToString();
#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;
cmd.CommandText = "update MZ_CFD_ZB set sfty='N',qrty='',tyr='', tyrq=null where dh='" + DH + "' and sftf='N'";
cmd.ExecuteNonQuery();
cmd.CommandText = "update MZ_CFD_CB set sfty='N',qrty='',tyr='' where dh='" + DH + "'";
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='MZQXFYZT' AND BIZID='" + BIZID + "'";
db.DoCommand(ssql);
}
#endregion
System.String[] str = { "0", "保存成功" };
return(HisFunctions.GetResponseString("SaveKcph", str));
}
catch (Exception err)
{
throw err;
}
}
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);
}
}
private void button1_Click(object sender, EventArgs e)
{
if (comboBox1.SelectedItem != null)
{
if (textBox1.Text != "")
{
if (textBox2.Text != "")
{
connDsn = comboBox1.SelectedItem.ToString();
connUser = textBox1.Text;
connPassword = textBox2.Text;
connDsnName = "DSN=" + comboBox1.SelectedItem.ToString() + "; Uid=" + textBox1.Text + "; Pwd=" + textBox2.Text + ";";
using (System.Data.Odbc.OdbcConnection conn = new System.Data.Odbc.OdbcConnection(connDsnName))
{
try
{
conn.Open();
System.Data.Odbc.OdbcCommand command = new System.Data.Odbc.OdbcCommand("select getDate()", conn);
command.ExecuteNonQuery();
}
catch (Exception ex) {
MessageBox.Show("ODBC Conexión Fallida revise los datos");
Console.WriteLine("ODBC Connection test failed!!!!");
Console.WriteLine(ex.Message);
conn.Close();
return;
}
MessageBox.Show("ODBC Conexión Satisfatoria");
Console.WriteLine("ODBC Connection test PASSED!");
conn.Close();
button3.Enabled = true;
}
}
else
{
MessageBox.Show("Es necesario rellenar el campo de Password");
}
}
else
{
MessageBox.Show("Es necesario rellenar el campo de Usuario");
}
}
else
{
MessageBox.Show("Seleccione un OBDC");
}
}
public List <FederateRecord> GetAllFederateRecords()
{
List <FederateRecord> results = new List <FederateRecord>();
if (CheckConnection())
{
using (var command = mConnection.CreateCommand())
{
command.CommandText = "{CALL GetAllFederateRecords()}";
command.CommandType = System.Data.CommandType.StoredProcedure;
using (var resultSet = command.ExecuteReader())
{
while (resultSet.Read())
{
FederateRecord fd = new FederateRecord();
fd.RESTAPI = resultSet["RESTAPI"].ToString();
fd.SOAPAPI = resultSet["SOAPAPI"].ToString();
fd.namespacePrefix = resultSet["prefix"].ToString();
fd.OrginizationName = resultSet["OrganizationName"].ToString();
fd.OrganizationURL = resultSet["OrganizationURL"].ToString();
fd.OrganizationPOC = resultSet["OrganizationPOC"].ToString();
fd.OrganizationPOCEmail = resultSet["OrganizationPOCEmail"].ToString();
fd.OrganizationPOCPassword = resultSet["OrganizationPOCPassword"].ToString();
fd.ActivationState = (FederateState)(System.Convert.ToInt16(resultSet["ActivationState"].ToString()));
if (resultSet["AllowFederatedSearch"].ToString() == "1")
{
fd.AllowFederatedSearch = true;
}
if (resultSet["AllowFederatedDownload"].ToString() == "1")
{
fd.AllowFederatedDownload = true;
}
results.Add(fd);
}
}
}
}
else
{
throw (new SystemException("Could not connect to Federate Register Database"));
}
mConnection.Close();
return(results);
}
/* Construct a connection string. If a user specifies their own connection
* string, the contents of the 'server', 'database', 'username', and 'password'
* fields are disregarded. */
private void DSNAccept_Click(object sender, EventArgs e)
{
dsnSource = "DSN=" + DSNOp.Text.ToString() + ";";
/*Try connection String, catch if string is improperly
* formatted*/
try
{
System.Data.Odbc.OdbcConnection connection = new System.Data.Odbc.OdbcConnection(dsnSource);
/*Try to connect with the provided MySqlConnection. If successful, launch
* The main application. If the connection fails to reach a source, catch
* and produce an error message. */
try
{
connection.Open();
connection.Close();
main = new MajorEvaluator.MainWindow(this);
main.ShowDialog(this);
this.Close();
}
catch (Exception s)
{
MessageBox.Show("Could not connect to server. Please try again.");
//MessageBox.Show(s.ToString());
DSNOp.Clear();
}
} catch (Exception s) {
MessageBox.Show("Improper connection string.");
//MessageBox.Show(s.ToString());
}
}
// UPLOAD BUTTON HANDLER -------------------------------------------------------------------------------------------------------
protected void uploadbutton_Click(object sender, EventArgs e)
{
// Get button ID
Button getbuttonID = (Button)sender;
string id = getbuttonID.ID.Replace("_button", "");
// Use button ID to find similarly named upload control ID
FileUpload uploadcontrol = (FileUpload)Form.FindControl(id);
// Only upload if control has file selected
if (uploadcontrol.HasFile)
{
// Add upload path
String savePath = @"c:\temp\";
// Retrieve filename from upload control
String fileName = uploadcontrol.FileName;
// Save data to web server
uploadcontrol.SaveAs(savePath + fileName);
// Fill GridView
// Establish text driver connection
System.Data.Odbc.OdbcConnection csv_connection;
System.Data.Odbc.OdbcDataAdapter csv_adapter;
// Create temporary data table to store CSV data
DataTable csv_data = new DataTable();
// Create connection string and execute connection to CSV
string csv_connectionString = @"Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + savePath + ";";
csv_connection = new System.Data.Odbc.OdbcConnection(csv_connectionString);
// Fill adapter with SELECT * query from CSV
csv_adapter = new System.Data.Odbc.OdbcDataAdapter("select * from [" + fileName + "]", csv_connection);
csv_adapter.Fill(csv_data);
// Close CSV connection
csv_connection.Close();
// Find GridView and fill
GridView filedata = (GridView)Form.FindControl(id + "_table");
filedata.DataSource = csv_data;
filedata.DataBind();
// SESSION MODIFICATION //
DataSet session_datanew = new DataSet();
session_datanew.Tables.Add(csv_data);
session_datanew.DataSetName = "PCADATA";
Registry.Registry registry = Registry.Registry.getRegistry(Session);
registry.registerDataset(session_datanew);
Analysis.ParameterStream stream = Analysis.ParameterStream.getStream(Session);
stream.set("dataSetName", "PCADATA");
//----------------------//
}
}
public HostInfo[] getHostInfos()
{
System.Data.Odbc.OdbcConnection cn = new System.Data.Odbc.OdbcConnection("dsn=TCS;uid=db2inst1;pwd=db2inst1");
System.Data.Odbc.OdbcCommand cmd = new System.Data.Odbc.OdbcCommand("select HostID,HostIP from tblHostConfig where hosttype='1'");
System.Data.Odbc.OdbcDataReader rd;
try
{
cmd.Connection=cn;
cn.Open();
rd = cmd.ExecuteReader();
System.Collections.Generic.List<HostInfo> lst = new List<HostInfo>();
while (rd.Read())
{
HostInfo hi = new HostInfo();
hi.HostName = rd["HostID"].ToString();
hi.IP = rd["HostIP"].ToString();
lst.Add(hi);
}
return lst.ToArray();
}
finally
{
cn.Close();
}
}
public override List <DDD_MSJ.DOM.Mensaje> getMensajes()
{
var sqlConexion = new System.Data.Odbc.OdbcConnection("Dsn=prova");
var sqlComando = new System.Data.Odbc.OdbcCommand();
System.Data.Odbc.OdbcDataReader sqlReader;
sqlComando.CommandText = "select * from mensajeria.Mensaje;";
sqlComando.CommandType = System.Data.CommandType.Text;
sqlComando.Connection = sqlConexion;
try
{
sqlConexion.Open();
sqlReader = sqlComando.ExecuteReader();
string IdMensaje = "";
string Descripcion = "";
DDD_MSJ.DOM.Mensaje Mensaje;
while (sqlReader.Read())
{
IdMensaje = sqlReader.GetString(0);
Descripcion = sqlReader.GetString(1);
Mensaje = new DDD_MSJ.DOM.Mensaje(IdMensaje, Descripcion);
this.m_lstMensajes.Add(Mensaje);
}
}
finally
{
sqlConexion.Close();
}
return(this.m_lstMensajes);
}
public void loadValidCheckRule()
{
ConsoleServer.WriteLine("Loaad Range Table");
System.Data.Odbc.OdbcConnection cn = new System.Data.Odbc.OdbcConnection(Comm.DB2.Db2.db2ConnectionStr);
System.Data.Odbc.OdbcCommand cmd = new System.Data.Odbc.OdbcCommand("select minvalue,maxvalue from tblRDvalidRange");
try
{
cn.Open();
cmd.Connection = cn;
System.Data.Odbc.OdbcDataReader rd = cmd.ExecuteReader();
if (rd.Read())
{
minvalue = System.Convert.ToInt32(rd[0]);
maxvalue = System.Convert.ToInt32(rd[1]);
}
}
catch (Exception ex)
{
ConsoleServer.WriteLine(ex.Message + "," + ex.StackTrace);
}
finally
{
cn.Close();
}
}
public CMSTC(Protocol protocol, string devicename, string ip, int port, int deviceid, byte[] hw_status, byte opmode, byte opstatus, byte comm_state)
: base(protocol, devicename, ip, port, deviceid, hw_status, opmode, opstatus,comm_state)
{
this.OnTCReport += new OnTCReportHandler(CMSTC_OnTCReport);
this.OnTCReceiveText += new OnTCReportHandler(CMSTC_OnTCReceiveText);
// this.m_device.OnReceiveText += new OnTextPackageEventHandler(m_device_OnReceiveText);
System.Data.Odbc.OdbcConnection cn = new System.Data.Odbc.OdbcConnection(Comm.DB2.Db2.db2ConnectionStr);
System.Data.Odbc.OdbcCommand cmd = new System.Data.Odbc.OdbcCommand("select show_icon,CRmode from tblCmsConfig where DeviceName='" + this.DeviceName + "'");
cmd.Connection = cn;
try
{
cn.Open();
System.Data.Odbc.OdbcDataReader rd = cmd.ExecuteReader();
if (rd.Read())
{
isIcon = (System.Convert.ToInt32(rd[0])==0)?false:true;
CrMode = System.Convert.ToInt32(rd[1]);
}
rd.Close();
}
catch (Exception ex)
{
ConsoleServer.WriteLine(ex.Message + ex.StackTrace);
}
finally
{
cn.Close();
}
}
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;
}
}
private void button4_Click(object sender, EventArgs e)
{
String connDsnName = "DSN=" + gbInstance + "; DataBase=" + gbDB + ";Uid=" + gbUser + "; Pwd=" + gbPass + ";";
String SqlQuery = richTextBox1.Text;
using (System.Data.Odbc.OdbcConnection conn = new System.Data.Odbc.OdbcConnection(connDsnName))
{
try
{
conn.Open();
System.Data.Odbc.OdbcCommand command = new System.Data.Odbc.OdbcCommand(SqlQuery, conn);
System.Data.Odbc.OdbcDataReader reader = command.ExecuteReader();
DataTable dataTable = new DataTable();
dataTable.Load(reader);
dataGridView1.DataSource = dataTable;
//DataTable schemaTable = reader.GetSchemaTable();
/*foreach (DataRow row in schemaTable.Rows)
* {
* foreach (DataColumn column in schemaTable.Columns)
* {
* Console.WriteLine(String.Format("{0} = {1}",
* column.ColumnName, row[column]));
* }
* }*/
//dataGridView1.Update();
//while (reader.Read())
//{
//dataGridView1.Rows.Add("five", "six", "seven", "eight");
//MessageBox.Show(reader[0].ToString());
// MessageBox.Show(reader[1].ToString());
//}
dataGridView1.Update();
}
catch (Exception ex)
{
MessageBox.Show("Ocurrio un error al realalizar la consulta");
Console.WriteLine("Ocurrio un error al realalizar la consulta");
Console.WriteLine(ex.Message);
conn.Close();
this.Hide();
}
conn.Close();
button3.Enabled = true;
}
}
protected override void SavePersonalizationBlob
(WebPartManager webPartManager, string path, string userName,
byte[] dataBlob)
{
System.Data.Odbc.OdbcCommand updateCommand = null;
System.Data.Odbc.OdbcConnection updateConnection = null;
string sSQL = null;
try
{
sSQL = "SELECT COUNT(`username`) FROM `personalization` WHERE `username` = '" + userName + "' AND `path` = '" + path + "' and `applicationname` = '" + m_ApplicationName + "';";
updateConnection = new System.Data.Odbc.OdbcConnection(System.Configuration.ConfigurationManager.ConnectionStrings[m_ConnectionStringName].ToString());
if (int.Parse(RawDBQuery.ExecuteScalarOnDB(sSQL, System.Configuration.ConfigurationManager.ConnectionStrings[m_ConnectionStringName].ToString()).ToString()) > 0)
{
sSQL = "UPDATE `personalization` SET `personalizationblob` = ? WHERE `username` = ? AND `applicationname` = ? AND `path` = ?;";
updateCommand = new System.Data.Odbc.OdbcCommand(sSQL, updateConnection);
updateCommand.Parameters.Clear();
updateCommand.Parameters.Add(new System.Data.Odbc.OdbcParameter("personalizationblob", dataBlob));
updateCommand.Parameters.Add(new System.Data.Odbc.OdbcParameter("username", userName));
updateCommand.Parameters.Add(new System.Data.Odbc.OdbcParameter("applicationname", m_ApplicationName));
updateCommand.Parameters.Add(new System.Data.Odbc.OdbcParameter("path", path));
}
else
{
sSQL = "INSERT INTO `personalization` (`username`,`path`,`applicationname`,`personalizationblob`) VALUES (?, ?, ?, ?);";
updateCommand = new System.Data.Odbc.OdbcCommand(sSQL, updateConnection);
updateCommand.Parameters.Clear();
updateCommand.Parameters.Add(new System.Data.Odbc.OdbcParameter("username", userName));
updateCommand.Parameters.Add(new System.Data.Odbc.OdbcParameter("path", path));
updateCommand.Parameters.Add(new System.Data.Odbc.OdbcParameter("applicationname", m_ApplicationName));
updateCommand.Parameters.Add(new System.Data.Odbc.OdbcParameter("personalizationblob", dataBlob));
}
updateConnection.Open();
updateCommand.ExecuteNonQuery();
}
finally
{
if (updateConnection != null)
{
if (updateConnection.State != System.Data.ConnectionState.Closed)
{
updateConnection.Close();
}
else
{
updateConnection.Dispose();
}
}
updateConnection = null;
if (updateCommand != null)
{
updateCommand.Dispose();
}
updateCommand = null;
sSQL = null;
}
}
public bool MaterializaAvaliacao(cAvaliacao obj)
{
string sql;
try
{
sql = "SELECT a.id as id, ";
sql += " a.data as data, ";
sql += " a.idade as idade, ";
sql += " a.peso as peso, ";
sql += " a.altura as altura, ";
sql += " a.nivel_atividade as nivel_atividade, ";
sql += " a.gordura as gordura, ";
sql += " a.vo2 as vo2, ";
sql += " a.desc_atividade as desc_atividade, ";
sql += " a.observacoes as observacoes, ";
sql += " a.fc_max_obtida as fc_max_obtida, ";
sql += " a.fc_min_obtida as fc_min_obtida, ";
sql += " a.fc_media_obtida as fc_media_obtida, ";
sql += " a.tmb as tmb, ";
sql += " a.fc_basal as fc_basal, ";
sql += " p.nome as x_nome ";
sql += " FROM avaliacao AS a LEFT JOIN pessoa AS p ON p.id = a.id_pessoa WHERE a.id = " + obj.id.ToString();
sqlConn = new System.Data.Odbc.OdbcConnection(connString);
sqlConn.Open();
sqlCmd = new System.Data.Odbc.OdbcCommand(sql, sqlConn);
sqlAdapter = new System.Data.Odbc.OdbcDataAdapter();
sqlAdapter.SelectCommand = sqlCmd;
sqlDataSet = new System.Data.DataSet();
sqlAdapter.Fill(sqlDataSet);
System.Data.DataTable dt = sqlDataSet.Tables[0];
if (dt.Rows.Count > 0)
{
System.Data.DataRow linha = dt.Rows[0];
obj.id = int.Parse(linha["id"].ToString());
obj.idade = int.Parse(linha["idade"].ToString());
obj.peso = float.Parse(linha["peso"].ToString());
obj.data_hora = DateTime.Parse(linha["data"].ToString());
obj.x_nome = linha["x_nome"].ToString();
obj.altura = float.Parse(linha["altura"].ToString());
obj.nivel_atividade = int.Parse(linha["altura"].ToString());
}
sqlConn.Close();
return(true);
}
catch (Exception Ex)
{
return(false);
}
}
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( );
}
protected void Button4_Click(object sender, EventArgs e)
{
string SqlStr = "";
System.Data.Odbc.OdbcConnection Cn;
System.Data.Odbc.OdbcCommand Cmd;
System.Data.Odbc.OdbcDataReader Res;
Cn = new System.Data.Odbc.OdbcConnection(@"Driver={ODBC Driver 13 for SQL Server};Server=tcp:monkdb.database.windows.net,1433;Database=monkdb;Uid=monk;Pwd=!@#$qwer19;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;");
//SqlStr = @"CREATE TABLE lineID (SN INT IDENTITY PRIMARY KEY ,lineid nvarchar(50),Name nvarchar(50),type nvarchar(50),CreateTime nvarchar(50),UPDATETime nvarchar(50));";
//SqlStr = @"ALTER TABLE lineID ALTER COLUMN Name nvarchar(50)";
//drop table
SqlStr = @"DROP TABLE Receivemsg;";
Cmd = new System.Data.Odbc.OdbcCommand(SqlStr, Cn);
try
{
Cn.Open( );
Cmd.ExecuteNonQuery( );
Cn.Close( );
lbCreateMSG.Text = "刪除 LineID成功";
}
catch (Exception ex)
{
lbCreateMSG.Text = ex.ToString( );
}
//create table
//SqlStr = @"CREATE TABLE lineID (SN INT IDENTITY PRIMARY KEY ,lineid nvarchar(50),Name nvarchar(50),type nvarchar(50),ENABLE int,CreateTime nvarchar(50),UPDATETime nvarchar(50));";
//SqlStr = @"CREATE TABLE Replymsg (SN INT IDENTITY PRIMARY KEY ,keyValue nvarchar(50),returnMSG nvarchar(200),ENABLE int,CreateTime nvarchar(50),UPDATETime nvarchar(50));";
SqlStr = @"CREATE TABLE Receivemsg (SN INT IDENTITY PRIMARY KEY ,addTime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,msg nvarchar(200),fromUser nvarchar(50) ,fromRoom nvarchar(50));";
Cmd = new System.Data.Odbc.OdbcCommand(SqlStr, Cn);
try
{
Cn.Open( );
Cmd.ExecuteNonQuery( );
Cn.Close( );
lbCreateMSG.Text = "建立table成功";
}
catch (Exception ex)
{
lbCreateMSG.Text = ex.ToString( );
}
}
public void SendCmdImmediately(string sqlCmd)
{
System.Data.Odbc.OdbcConnection cn = new System.Data.Odbc.OdbcConnection(DbCmdServer.getDbConnectStr());
System.Data.Odbc.OdbcCommand cmd = new System.Data.Odbc.OdbcCommand(sqlCmd);
cmd.Connection = cn;
cn.Open();
cmd.ExecuteNonQuery();
cn.Close();
}
/// <summary>
/// 获取医保结算结果
/// </summary>
/// <returns></returns>
public static int GetSiResult(string path, string tablename, ref Neusoft.HISFC.Models.RADT.PatientInfo p, ref string errTxt)
{
if (tablename.Substring(0, 1).ToUpper() != "S")
{
tablename = "S" + tablename;
}
string connect = @"Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277; Dbq=" + path;
System.Data.Odbc.OdbcConnection myconn = new System.Data.Odbc.OdbcConnection(connect);
//个人身份号码 住院号 在院总金额 社保支付金额 个人自费金额 纯自费金额 起伏金额 按比例自负 统筹记账金额 大额记账金额 公务员记账金额 帐户支付金额 现金支付金额 医保记账总额 医保帐户余额 就诊记录号
//GMSFHM ZYH ZYZJE SBZFJE GRZFJE ZFYY CZFJE BFZFJE QFJE ABLZF TCJZJE DEJZJE GWYJZJE ZHZFJE XJZFJE YBJZZE YBZHYE JZJLH
string select = "select * from " + tablename;
System.Data.Odbc.OdbcCommand cmSelect = new System.Data.Odbc.OdbcCommand(select, myconn);
System.Data.Odbc.OdbcDataReader cmReader;
try
{
myconn.Open();
cmReader = cmSelect.ExecuteReader();
}
catch (Exception ex)
{
errTxt = "导出医保信息失败！" + ex.Message;
return(-1);
}
if (!cmReader.Read())
{
errTxt = "医保结算数据不存在！";
return(-2);
}
try
{
p.IDCard = cmReader["GMSFHM"].ToString(); //公民身份号码
p.SIMainInfo.RegNo = cmReader["ZYH"].ToString(); //住院号
p.SIMainInfo.TotCost = Neusoft.FrameWork.Function.NConvert.ToDecimal(cmReader["ZYZJE"]); //住院总金额
p.SIMainInfo.OwnCost = Neusoft.FrameWork.Function.NConvert.ToDecimal(cmReader["XJZFJE"]); //现金支付
p.SIMainInfo.PayCost = Neusoft.FrameWork.Function.NConvert.ToDecimal(cmReader["ZHZFJE"]); //账户支付
p.SIMainInfo.PubCost = p.SIMainInfo.TotCost - p.SIMainInfo.OwnCost - p.SIMainInfo.PayCost; //统筹支付
p.SIMainInfo.OverCost = Neusoft.FrameWork.Function.NConvert.ToDecimal(cmReader["DEJZJE"]); //大额记账金额
p.SIMainInfo.BaseCost = Neusoft.FrameWork.Function.NConvert.ToDecimal(cmReader["QFJE"]); //起付金额
p.SIMainInfo.IndividualBalance = Neusoft.FrameWork.Function.NConvert.ToDecimal(cmReader["YBZHYE"]); //医保账户余额
cmReader.Close();
cmSelect.Dispose();
myconn.Close();
}
catch (Exception e)
{
errTxt = e.ToString();
return(-1);
}
return(1);
}
/// <summary>
/// Clean up any resources being used.
/// </summary>
/// <param name="disposing">true if managed resources should be disposed; otherwise, false.</param>
protected override void Dispose(bool disposing)
{
if (conn.State != System.Data.ConnectionState.Closed)
{
conn.Close();
}
if (disposing && (components != null))
{
components.Dispose();
}
base.Dispose(disposing);
}
public int ListarAvaliacoes(cAvaliacao obj, ref string[][] array)
{
string sql;
try
{
sql = "SELECT a.id as id, a.data as data, p.nome as nome FROM avaliacao AS a LEFT JOIN pessoa AS p ON p.id = a.id_pessoa WHERE 1 = 1 ";
if (obj.x_nome != String.Empty)
{
sql += " AND p.nome LIKE '%" + obj.x_nome + "%'";
}
sqlConn = new System.Data.Odbc.OdbcConnection(connString);
sqlConn.Open();
sqlCmd = new System.Data.Odbc.OdbcCommand(sql, sqlConn);
sqlAdapter = new System.Data.Odbc.OdbcDataAdapter();
sqlAdapter.SelectCommand = sqlCmd;
sqlDataSet = new System.Data.DataSet();
sqlAdapter.Fill(sqlDataSet);
System.Data.DataTable dt = sqlDataSet.Tables[0];
if (dt.Rows.Count > 0)
{
array = new string[dt.Rows.Count][];
int i = 0;
foreach (System.Data.DataRow row in dt.Rows)
{
array[i] = new string[3];
array[i][0] = row[0].ToString();
array[i][1] = row[1].ToString();
array[i][2] = row[2].ToString();
i++;
}
}
sqlConn.Close();
return(dt.Rows.Count);
}
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;
}
public int ListarPessoas(cPessoa obj, ref string [][] array)
{
string sql;
try{
if (obj.sNome == String.Empty)
{
sql = "SELECT ID, nome FROM pessoa";
}
else
{
sql = "SELECT ID, nome FROM pessoa WHERE nome like '%" + obj.sNome + "%'";
}
sqlConn = new System.Data.Odbc.OdbcConnection(connString);
sqlConn.Open();
sqlCmd = new System.Data.Odbc.OdbcCommand(sql, sqlConn);
sqlAdapter = new System.Data.Odbc.OdbcDataAdapter();
sqlAdapter.SelectCommand = sqlCmd;
sqlDataSet = new System.Data.DataSet();
sqlAdapter.Fill(sqlDataSet, "pessoa");
System.Data.DataTable dt = sqlDataSet.Tables["pessoa"];
if (dt.Rows.Count > 0)
{
array = new string[dt.Rows.Count][];
int i = 0;
foreach (System.Data.DataRow row in dt.Rows)
{
array[i] = new string[2];
array[i][0] = row[0].ToString();
array[i][1] = row[1].ToString();
i++;
}
}
sqlConn.Close();
return(dt.Rows.Count);
}catch (Exception Ex) {
return(-1);
}
}
/// <summary>
///
/// </summary>
/// <param name="pid"></param>
public void DisableTempDatastreams(string pid)
{
using (System.Data.Odbc.OdbcConnection conn = new System.Data.Odbc.OdbcConnection(connectionString))
{
conn.Open();
using (var command = conn.CreateCommand())
{
command.CommandText = "CALL RemoveFromCurrentUploads(?)";
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.AddWithValue("targetpid", pid);
//if (command.ExecuteNonQuery() != 1) { throw new Exception("Stored procedure call RemoveFromCurrentUploads failed."); }
command.ExecuteNonQuery();
}
conn.Close();
}
}
/// <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>
///
/// </summary>
/// <param name="pid"></param>
public void DisableTempDatastreams(string pid)
{
using (System.Data.Odbc.OdbcConnection conn = new System.Data.Odbc.OdbcConnection(connectionString))
{
conn.Open();
using (var command = conn.CreateCommand())
{
command.CommandText = "CALL RemoveFromCurrentUploads(?)";
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.AddWithValue("targetpid", pid);
//if (command.ExecuteNonQuery() != 1) { throw new Exception("Stored procedure call RemoveFromCurrentUploads failed."); }
command.ExecuteNonQuery();
}
conn.Close();
}
}
void AddMsg(String UserID, String RoomID, string msg)
{
string SqlStr = "";
System.Data.Odbc.OdbcConnection Cn;
System.Data.Odbc.OdbcCommand Cmd;
System.Data.Odbc.OdbcDataReader Res;
string Time = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss.fff");
Cn = new System.Data.Odbc.OdbcConnection(@"Driver={ODBC Driver 13 for SQL Server};Server=tcp:monkdb.database.windows.net,1433;Database=monkdb;Uid=monk;Pwd=!@#$qwer19;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;");
Cn.Open( );
SqlStr = @"insert into Receivemsg ( MSG, fromUser, fromRoom ) values ( N'" + msg + "', '" + UserID + "', '" + RoomID + "' ); ";
Cmd = new System.Data.Odbc.OdbcCommand(SqlStr, Cn);
Cmd.ExecuteNonQuery( );
Cn.Close( );
}
public MovingConstructManager()
{
System.Data.Odbc.OdbcConnection cn = new System.Data.Odbc.OdbcConnection(Global.Db2ConnectionString);
// System.Data.Odbc.OdbcCommand cmd = new System.Data.Odbc.OdbcCommand("select id, notifier, timeStamp, lineID, directionID, startMileage, endMileage, blockTypeId, blocklane, description,Execution,originalEventid from TBLIIPMCNSLOG");
System.Data.Odbc.OdbcCommand cmd = new System.Data.Odbc.OdbcCommand("select id, notifier, t1.timeStamp, t1.lineID, directionID, startMileage, endMileage, blockTypeId, blocklane, description,t1.Execution,t1.originalEventid,t2.eventid,t2.status"+
" from TBLIIPMCNSLOG t1 inner join tblsysAlarmlog t2 on t2.originaleventid=t1.originaleventid where t2.ifclose=0");
System.Data.Odbc.OdbcDataReader rd;
cmd.Connection = cn;
int id,originalEventid,evtid, status;
string notifier;
DateTime timeStamp;
string lineID, directionID;
int startMileage, endMileage, blockTypeId;
string blocklane, description,IsExecution;
try
{
cn.Open();
rd = cmd.ExecuteReader();
while (rd.Read())
{
id = System.Convert.ToInt32( rd[0]);
notifier= rd[1].ToString();
timeStamp=System.Convert.ToDateTime(rd[2]);
lineID=rd[3].ToString();
directionID=rd[4].ToString();
startMileage=System.Convert.ToInt32(rd[5]);
endMileage= System.Convert.ToInt32(rd[6]);
blockTypeId=System.Convert.ToInt32(rd[7]);
blocklane=rd[8].ToString();
description=rd[9].ToString();
IsExecution = rd[10].ToString();
originalEventid = System.Convert.ToInt32(rd[11]);
evtid = System.Convert.ToInt32(rd[12]);
status=System.Convert.ToInt32(rd[13]);
this.setEvent(id, notifier, timeStamp, lineID, directionID, startMileage, endMileage, blockTypeId, blocklane, description, IsExecution, originalEventid,evtid,status);
}
}
catch(Exception ex)
{
RemoteInterface.ConsoleServer.WriteLine(ex.Message + "," + ex.StackTrace);
}
finally
{
cn.Close();
}
}
public int GetFeeItemList(string path, string tablename, ref Neusoft.HISFC.Models.RADT.PatientInfo p, ref ArrayList al)
{
//string path = filePath.Substring(0, filePath.LastIndexOf(@"\") + 1);
//string tablename = filePath.Substring(filePath.LastIndexOf(@"\") + 1, filePath.Length - filePath.LastIndexOf(@"\") - 1);
string connect = @"Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277; Dbq=" + path;
System.Data.Odbc.OdbcConnection myconn = new System.Data.Odbc.OdbcConnection(connect);
string sql = "select GRSHBZH,ZYH,XMXH,XMBH,XMMC,FLDM,YPGG,YPJX,JG,MCYL,JE,ZFBL,ZFJE,CZFBZ,BZ1,BZ2,BZ3 from " + tablename;
System.Data.Odbc.OdbcCommand cmd = new System.Data.Odbc.OdbcCommand(sql, myconn);
System.Data.Odbc.OdbcDataReader cmReader;
try
{
myconn.Open();
cmReader = cmd.ExecuteReader();
}
catch
{
return(-1);
}
Neusoft.HISFC.Models.Fee.Inpatient.FeeItemList f = null;
while (cmReader.Read())
{
f = new Neusoft.HISFC.Models.Fee.Inpatient.FeeItemList();
p.IDCard = cmReader[0].ToString();
p.PID.PatientNO = cmReader[1].ToString();
f = new Neusoft.HISFC.Models.Fee.Inpatient.FeeItemList();
f.Item.User02 = cmReader[2].ToString();
f.Item.UserCode = cmReader[3].ToString();
f.Item.Name = cmReader[4].ToString();
f.Item.SysClass.Name = cmReader[5].ToString();
f.Item.Specs = cmReader[6].ToString();
f.Item.User01 = cmReader[7].ToString();
f.Item.Price = Neusoft.FrameWork.Function.NConvert.ToDecimal(cmReader[8]);
f.NoBackQty = Neusoft.FrameWork.Function.NConvert.ToDecimal(cmReader[9]);
f.FT.OwnCost = Neusoft.FrameWork.Function.NConvert.ToDecimal(cmReader[10]);
al.Add(f);
}
cmReader.Close();
cmd.Dispose();
myconn.Close();
return(1);
}
public DataSet ConnectCSV(string filetable, bool bFill)
{
DataSet ds = new DataSet();
try
{
// You can get connected to driver either by using DSN or connection string
// Create a connection string as below, if you want to use DSN less connection. The DBQ attribute sets the path of directory which contains CSV files
string strConnString = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + txtCSVFolderPath.Text.Trim() + ";Extensions=asc,csv,tab,txt;Persist Security Info=False";
string sql_select;
System.Data.Odbc.OdbcConnection conn;
//Create connection to CSV file
conn = new System.Data.Odbc.OdbcConnection(strConnString.Trim());
// For creating a connection using DSN, use following line
//conn = new System.Data.Odbc.OdbcConnection(DSN="MyDSN");
//Open the connection
conn.Open();
//Fetch records from CSV
sql_select = "select * from [" + filetable + "]";
obj_oledb_da = new System.Data.Odbc.OdbcDataAdapter(sql_select, conn);
//Fill dataset with the records from CSV file
obj_oledb_da.Fill(ds, "Customers");
if (bFill)
{
//Set the datagrid properties
dGridCSVdata.DataSource = ds;
dGridCSVdata.DataMember = "Customers";
//Close Connection to CSV file
}
conn.Close();
}
catch (Exception exe) //Error
{
MMSUtility.LogFile(exe.Message, "", "ConnectCSV", exe.LineNumber(), "PrintLetterDialog");
MessageBox.Show(exe.Message);
}
return ds;
}
public override void guardarMensajes(List <DOM.Mensaje> Mensajes)
{
var sqlConexion = new System.Data.Odbc.OdbcConnection("Dsn=prova");
var sqlComando = new System.Data.Odbc.OdbcCommand();
sqlComando.CommandText = "INSERT INTO mensajeria.Mensaje (IDMensaje, Mensaje) VALUES ('@IdMensaje','@Descripcion');";
sqlComando.CommandType = System.Data.CommandType.Text;
sqlComando.Connection = sqlConexion;
System.Data.Odbc.OdbcTransaction Transaccion = null;
try
{
sqlConexion.Open();
Transaccion = sqlConexion.BeginTransaction();
sqlComando.Transaction = Transaccion;
foreach (DDD_MSJ.DOM.Mensaje MensajeAInserir in Mensajes)
{
sqlComando.Parameters.AddWithValue("@IdMensaje", MensajeAInserir.IdMensaje);
sqlComando.Parameters.AddWithValue("@Descripcion", MensajeAInserir.Descripcion);
sqlComando.ExecuteNonQuery();
}
}
catch
{
if (Transaccion != null)
{
Transaccion.Rollback();
}
}
finally
{
if (Transaccion != null)
{
Transaccion.Commit();
}
sqlConexion.Close();
}
}
public void Close()
{
if (_odbcConnection == null)
{
return;
}
try
{
_odbcConnection.Close();
_odbcConnection.Dispose();
_odbcConnection = null;
}
catch (System.Exception ex)
{
_odbcConnection = null;
throw ex;
}
}
/// <summary>
///
/// </summary>
/// <param name="pid"></param>
/// <returns></returns>
public string GetTempLocation(string pid)
{
string location = "";
using (System.Data.Odbc.OdbcConnection conn = new System.Data.Odbc.OdbcConnection(connectionString))
{
conn.Open();
using (var command = conn.CreateCommand())
{
command.CommandText = "SELECT hash FROM test.current_uploads WHERE pid = ?";
command.CommandType = System.Data.CommandType.Text;
command.Parameters.AddWithValue("@PID", pid);
try { location = command.ExecuteScalar().ToString(); }
catch { }
}
conn.Close();
}
return(location);
}
public static DataSet ImportCSVFile(string path)
{
// TODO: method assumes path exists.
string directory = Path.GetDirectoryName(path);
string filename = Path.GetFileName(path);
CreateIniFile(path);
System.Data.Odbc.OdbcConnection odbcconn = null;
DataSet taxds = new DataSet();
System.Data.Odbc.OdbcDataAdapter odbcda;
try
{
// string connectionString = "Driver={Microsoft Text Driver (*.txt;*.csv)};Dbq="
// + directory.Trim() + ";Extensions=asc,csv,tab,txt;Persist Security Info=False";
string connectionString = "Driver={Microsoft Text Driver (*.txt;*.csv)};DefaultDir="
+ directory.Trim() + "Extended properties=\"ColNameHeader=True;Format=CSVDelimited;\"";
odbcconn = new System.Data.Odbc.OdbcConnection(connectionString.Trim());
odbcconn.Open();
//Fetch records from CSV
string odbcsql = "select * from [" + filename + "]";
odbcda = new System.Data.Odbc.OdbcDataAdapter(odbcsql, odbcconn);
//Fill dataset with the records from CSV file
odbcda.Fill(taxds, "Taxes");
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
finally
{
odbcconn.Close();
DestroyIniFile(path);
}
return(taxds);
}
/// <summary>
///
/// </summary>
/// <param name="pid"></param>
/// <param name="hash"></param>
public void EnableTempDatastreams(string pid, string hash)
{
//Remove existing references that may occur from the user choosing a new file
//before the upload to Fedora is finished
DisableTempDatastreams(pid);
using (System.Data.Odbc.OdbcConnection conn = new System.Data.Odbc.OdbcConnection(connectionString))
{
conn.Open();
using (var command = conn.CreateCommand())
{
command.CommandText = "CALL AddToCurrentUploads(?,?)";
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.AddWithValue("targetpid", pid);
command.Parameters.AddWithValue("targethash", hash);
//if (command.ExecuteNonQuery() != 1) { throw new Exception("Stored procedure call AddToCurrentUploads failed."); }
command.ExecuteNonQuery();
}
conn.Close();
}
}
public int ListarDados(cDados obj, ref cDados[] array)
{
string sql;
try
{
sql = "SELECT ID, id_avaliacao, data_hora, freq_cardiaca FROM dados WHERE id_avaliacao = " + obj.id_avaliacao.ToString();
sqlConn = new System.Data.Odbc.OdbcConnection(connString);
sqlConn.Open();
sqlCmd = new System.Data.Odbc.OdbcCommand(sql, sqlConn);
sqlAdapter = new System.Data.Odbc.OdbcDataAdapter();
sqlAdapter.SelectCommand = sqlCmd;
sqlDataSet = new System.Data.DataSet();
sqlAdapter.Fill(sqlDataSet);
System.Data.DataTable dt = sqlDataSet.Tables[0];
foreach (System.Data.DataRow row in dt.Rows)
{
Array.Resize(ref array, array.Length + 1);
System.Data.DataRow linha = row;
array[array.Length - 1] = new cDados();
array[array.Length - 1].id = int.Parse(linha["id"].ToString());
array[array.Length - 1].fc = int.Parse(linha["freq_cardiaca"].ToString());
array[array.Length - 1].data = DateTime.Parse(linha["data_hora"].ToString());
}
sqlConn.Close();
return(dt.Rows.Count);
}
catch (Exception Ex)
{
return(-1);
}
}
public void load_IID_Cam_Data()
{
hsCams.Clear();
System.Data.Odbc.OdbcConnection cn = new System.Data.Odbc.OdbcConnection(Global.Db2ConnectionString);
System.Data.Odbc.OdbcCommand cmd = new System.Data.Odbc.OdbcCommand();
System.Data.Odbc.OdbcDataReader rd;
cmd.Connection = cn;
cmd.CommandText = "select devicename,cam_id,direction,mileage,lane_id from tblIIDCamConfig";
try
{
cn.Open();
rd = cmd.ExecuteReader();
while (rd.Read())
{
string devname, direction;
int camid,laneid, mileage;
devname = rd[0].ToString();
camid = System.Convert.ToInt32(rd[1]);
direction = rd[2].ToString();
mileage = System.Convert.ToInt32(rd[3]);
laneid = System.Convert.ToInt32(rd[4]);
IID_CAM_Data camdata=new IID_CAM_Data("N6", direction, devname, camid, laneid, mileage);
camdata.OnEvent += new EventHandler(camdata_OnEvent);
hsCams.Add(camdata.Key,camdata);
}
rd.Close();
}
catch (Exception ex)
{
ConsoleServer.WriteLine(ex.Message + "," + ex.StackTrace);
}
finally
{
cn.Close();
}
// string sql="select camName,
}
string REPLAYMSG(string RECEIVE)
{
int step = 0;
string msg = "";
try
{
string csvStr = "";
string SqlStr = "";
System.Data.Odbc.OdbcConnection Cn;
System.Data.Odbc.OdbcCommand Cmd;
System.Data.Odbc.OdbcDataReader Res;
System.Data.Odbc.OdbcDataAdapter da;
Cn = new System.Data.Odbc.OdbcConnection(@"Driver={ODBC Driver 13 for SQL Server};Server=tcp:monkdb.database.windows.net,1433;Database=monkdb;Uid=monk;Pwd=!@#$qwer19;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;");
Cmd = new System.Data.Odbc.OdbcCommand(SqlStr, Cn);
step = 1;
Cn.Open( );
step = 2;
SqlStr = @"SELECT keyValue ,returnMSG FROM Replymsg ";
Cmd = new System.Data.Odbc.OdbcCommand(SqlStr, Cn);
Res = Cmd.ExecuteReader( );
step = 3;
while (Res.Read( ))
{
if (RECEIVE.ToLower( ).IndexOf(Res[0].ToString( ).ToLower( ), 0) > -1)
{
msg = Res[1].ToString( );
}
}
Cn.Close( );
return(msg);
}
catch (Exception ex)
{
return("");
//return "REPLAYMSG ERR:" + ex.ToString ( );
}
}
public static System.Data.Odbc.OdbcDataReader ExecuteReaderQueryOnDB(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.ExecuteReader();
}
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 void loadRangeTable()
{
//type=A avg wnd spd ,I immediate wns speed
ConsoleServer.WriteLine("Loaad Range Table");
System.Data.Odbc.OdbcConnection cn = new System.Data.Odbc.OdbcConnection(Comm.DB2.Db2.db2ConnectionStr);
System.Data.Odbc.OdbcCommand cmd = new System.Data.Odbc.OdbcCommand("select type,minvalue,maxvalue from tblWDvalidRange");
try
{
cn.Open();
cmd.Connection = cn;
System.Data.Odbc.OdbcDataReader rd = cmd.ExecuteReader();
while (rd.Read())
{
if (rd[0].ToString().Trim() == "A")
{
RangeTable[0, 0] = System.Convert.ToInt32(rd[1]);
RangeTable[0, 1] = System.Convert.ToInt32(rd[2]);
}
else
{
RangeTable[1, 0] = System.Convert.ToInt32(rd[1]);
RangeTable[1, 1] = System.Convert.ToInt32(rd[2]);
}
}
}
catch (Exception ex)
{
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 void NapuniListu()
{
System.Data.Odbc.OdbcCommand odbcCommand;
System.Data.Odbc.OdbcConnection odbcConn = new System.Data.Odbc.OdbcConnection();
string connectionString = Program.GetConnString();
odbcConn = new System.Data.Odbc.OdbcConnection(connectionString);
System.Data.Odbc.OdbcDataReader dr;
odbcCommand = new System.Data.Odbc.OdbcCommand("SELECT ime, id FROM mobiteli", odbcConn);
int id;
lista.Clear();
try
{
if (odbcConn.State != System.Data.ConnectionState.Open)
odbcConn.Open();
dr = odbcCommand.ExecuteReader();
while (dr.Read())
{
int.TryParse(dr["id"].ToString(), out id);
lista.Add(new MobiteliBaza(dr["ime"].ToString(), id));
}
dr.Close();
}
catch { MessageBox.Show("Mobilni ureðaji nisu uèitani", "Greška"); }
finally
{
if (odbcConn.State == System.Data.ConnectionState.Open)
odbcConn.Close();
}
}
//void MFCC_RD_OnTCReport(object tcc, Comm.TextPackage txt)
//{
// //throw new Exception("The method or operation is not implemented.");
// Comm.TC.RDTC tc = (Comm.TC.RDTC)tcc;
// string sql = "update tblRdData5Min set DataValidity='V',DataType=1,current_pluviometric={0},acc_pluviometric={1},rd_degree={2} where devicename='{3}' and timestamp='{4}' ";
// if (txt.Text[0] == 0x48) // cycle data
// {
// if (txt.Text.Length != 13)
// {
// ConsoleServer.WriteLine(tc.DeviceName + "," + txt.ToString() + ",長度不符");
// return;
// }
// else
// ConsoleServer.WriteLine(tc.DeviceName + "," + txt.ToString());
// System.DateTime dt = new DateTime(System.DateTime.Now.Year, System.DateTime.Now.Month, txt.Text[6], txt.Text[7], txt.Text[8], 0);
// this.dbServer.SendSqlCmd(string.Format(sql, txt.Text[9], txt.Text[10] * 256 + txt.Text[11], txt.Text[12], tc.DeviceName, Comm.DB2.Db2.getTimeStampString(dt)));
// }
//}
void DataRepairTask()
{
System.Data.Odbc.OdbcConnection cn = new System.Data.Odbc.OdbcConnection(Comm.DB2.Db2.db2ConnectionStr);
System.Data.Odbc.OdbcCommand cmd = new System.Data.Odbc.OdbcCommand();
cmd.Connection = cn;
string devName = "";
DateTime dt = new DateTime();
while (true)
{
Comm.TC.RDTC tc;
if (!IsLoadTcCompleted)
{
System.Threading.Thread.Sleep(1000);
continue;
}
try
{
cn.Open();
string sqlGetRepair = "select * from (select t1.DEVICENAME, TIMESTAMP ,trycnt,datavalidity,comm_state from TBLRDDATA5MIN t1 inner join tblDeviceConfig t2 on t1.devicename=t2.devicename where mfccid='{0}' and TIMESTAMP between '{1}' and '{2}' and trycnt <1 and datavalidity='N' and comm_state<>3 and enable='Y' fetch first 300 row only) order by trycnt,timestamp desc ";
cmd.CommandText = string.Format(sqlGetRepair, mfccid, Comm.DB2.Db2.getTimeStampString(System.DateTime.Now.AddDays(-7)), Comm.DB2.Db2.getTimeStampString(System.DateTime.Now.AddMinutes(-10)));
System.Data.Odbc.OdbcDataReader rd = cmd.ExecuteReader();
while (rd.Read())
{
try
{
devName = rd[0] as string;
dt = System.Convert.ToDateTime(rd[1]);
if (Program.mfcc_rd.manager.IsContains(devName))
tc = (Comm.TC.RDTC)Program.mfcc_rd.manager[devName];
else
continue;
if (!tc.IsConnected)
{
dbServer.SendSqlCmd(string.Format("update tbldeviceconfig set comm_state=3 where devicename='{0}' ", devName));
continue;
}
System.Data.DataSet ds = this.protocol.GetSendDataSet("get_a_temp_data");
ds.Tables[0].Rows[0]["day"]=dt.Day;
ds.Tables[0].Rows[0]["hour"] = dt.Hour;
ds.Tables[0].Rows[0]["minute"] = dt.Minute;
Comm.SendPackage pkg=this.protocol.GetSendPackage(ds, 0xffff);
tc.Send(pkg);
if (pkg.ReturnTextPackage != null && pkg.ReturnTextPackage.Text.Length != 12)
throw new Exception("回補資料長度錯誤");
else if (pkg.ReturnTextPackage == null && pkg.result == Comm.CmdResult.ACK)
throw new Exception(tc.DeviceName + "," + dt + ",資料回補失敗!");
ds=protocol.GetReturnDsByTextPackage(pkg.ReturnTextPackage);
int amount=System.Convert.ToInt32( ds.Tables[0].Rows[0]["current_pluviometric"]);
string sql =string.Format( "update tblRdData5Min set DataValidity='{5}',DataType=1,current_pluviometric={0},acc_pluviometric={1},rd_degree={2} where devicename='{3}' and timestamp='{4}' " ,
ds.Tables[0].Rows[0]["current_pluviometric"], ds.Tables[0].Rows[0]["acc_pluviometic"], ds.Tables[0].Rows[0]["rd_degree"], tc.DeviceName, Comm.DB2.Db2.getTimeStampString(dt),IsValid(amount)?"V":"I");
// Comm.TC.VD_MinAvgData data = tc.getOneMinAvgData(tc.Tc_GetVDData(dt), dt.Year, dt.Month);
dbServer.SendSqlCmd(sql);
ConsoleServer.WriteLine("==>repair:" + devName + "," + dt.ToString());
}
catch (Exception ex)
{
ConsoleServer.WriteLine(devName + "," + ex.Message + ex.StackTrace);
try
{
dbServer.SendSqlCmd(string.Format("update TBLRDDATA5MIN set trycnt=trycnt+1 where devicename='{0}' and timestamp='{1}'", devName, Comm.DB2.Db2.getTimeStampString(dt)));
}
catch (Exception ee)
{
ConsoleServer.WriteLine(ee.Message + ee.StackTrace);
}
}
}
rd.Close();
System.Threading.Thread.Sleep(1000 * 60);
}
catch (Exception x)
{
ConsoleServer.WriteLine(x.Message + x.StackTrace);
}
finally
{
cn.Close();
}
} //while
}
/*Queries the database for all the classes the currently selected student has taken.
* Then, it constructs a List containing 'Course' objects which represent each course
* the student has completed*/
private void studentSelectionButton_Click(object sender, EventArgs e)
{
searchWindow.ShowDialog(this);
String studentName = searchWindow.getName();
String studentID = searchWindow.getId();
if (studentID != null)
{
setStudent(studentID, studentName);
//Overwrite the previous student course list
this.studentCourseList = new List<Course>();
System.Data.Odbc.OdbcConnection connection = new System.Data.Odbc.OdbcConnection(dsnSource);
System.Data.Odbc.OdbcCommand command = connection.CreateCommand();
System.Data.Odbc.OdbcDataReader Reader;
command.CommandText = "select * from S_HIST_DETAIL where ID = '" + studentID + "'";
try
{
connection.Open();
Reader = command.ExecuteReader();
while (Reader.Read())
{
String courseSubject = Reader["SUBJECT"].ToString();
String courseNumber = Reader["COURSE_NUMBER"].ToString();
String CRN = Reader["CRN"].ToString();
Double gpa = Convert.ToDouble(Reader["DECIMAL_GRADE"]);
String term = Reader["TERM"].ToString();
Course currCourse = new Course(CRN, courseNumber, gpa, courseSubject, term);
studentCourseList.Add(currCourse);
}
connection.Close();
}
catch (Exception s)
{
MessageBox.Show("There was a connection problem. Please try again later");
MessageBox.Show(s.ToString());
}
if (majEval.size() > 0)
{
buildMajorEvaluation();
}
}
}
void DataRepairTask()
{
System.Collections.ArrayList aryThread = new System.Collections.ArrayList();
System.Data.Odbc.OdbcConnection cn = new System.Data.Odbc.OdbcConnection(Comm.DB2.Db2.db2ConnectionStr);
System.Data.Odbc.OdbcCommand cmd = new System.Data.Odbc.OdbcCommand();
cmd.CommandTimeout = 120;
StRepairData rpd=null;//=new StRepairData();
cmd.Connection = cn;
System.Collections.Queue queue = new System.Collections.Queue();
int dayinx = 1;
while (true)
{
Comm.TC.VDTC tc;
if (!IsLoadTcCompleted )
{
System.Threading.Thread.Sleep(1000);
continue;
}
try
{
// cn= new System.Data.Odbc.OdbcConnection(Comm.DB2.Db2.db2ConnectionStr);
while (this.dbServer.getCurrentQueueCnt() > 50)
System.Threading.Thread.Sleep(1000 * 10);
cn.Open();
ConsoleServer.WriteLine("Repair task begin!");
cmd.Connection = cn;
// string sqlGetRepair = "select * from (SELECT t1.DEVICENAME, t1.TIMESTAMP ,trycnt,datavalidity FROM TBLVDDATA1MIN t1 inner join tbldeviceconfig t2 on t1.devicename=t2.devicename WHERE mfccid='{0}' and comm_state <> 3 and t1.TIMESTAMP between '{1}' and '{2}' and trycnt<3 fetch first 300 row only ) where DATAVALIDITY = 'N' order by trycnt,timestamp desc ";
string sqlGetRepair = "select t1.DEVICENAME, TIMESTAMP ,trycnt,datavalidity,comm_state from TBLVDDATA1MIN t1 inner join tblDeviceConfig t2 on t1.devicename=t2.devicename where mfccid='{0}' and TIMESTAMP between '{1}' and '{2}' and trycnt <1 and datavalidity='N' and comm_state=1 and enable='Y' order by timestamp desc fetch first 300 row only ";
cmd.CommandText = string.Format(sqlGetRepair,mfccid, Comm.DB2.Db2.getTimeStampString(System.DateTime.Now.AddDays(-dayinx)), Comm.DB2.Db2.getTimeStampString(System.DateTime.Now.AddDays(-dayinx+1).AddMinutes(-10)));
System.Data.Odbc.OdbcDataReader rd = cmd.ExecuteReader();
while (rd.Read())
{
string devName="" ;
DateTime dt ;
devName = rd[0] as string;
dt = System.Convert.ToDateTime(rd[1]);
queue.Enqueue(new StRepairData(dt,devName));
}
rd.Close();
ConsoleServer.WriteLine("total:" + queue.Count + " to repair!");
if (queue.Count < 300)
{
dayinx++;
if (dayinx ==4)
dayinx = 1;
}
if(queue.Count<10)
System.Threading.Thread.Sleep(1000 * 60);
aryThread.Clear();
while (queue.Count!=0)
{
try
{
rpd =(StRepairData)queue.Dequeue() ;
if (Program.mfcc_vd.manager.IsContains(rpd.devName))
tc = (Comm.TC.VDTC)Program.mfcc_vd.manager[rpd.devName];
else
continue;
if (!tc.IsConnected)
{
dbServer.SendSqlCmd(string.Format("update tbldeviceconfig set comm_state=3 where devicename='{0}' ", rpd.devName));
continue;
}
System.Threading.Thread th= new System.Threading.Thread(Repair_job);
aryThread.Add(th);
th.Start(rpd);
if (aryThread.Count >= 5)
{
for (int i = 0; i < aryThread.Count; i++)
((System.Threading.Thread)aryThread[i]).Join();
aryThread.Clear();
}
// ConsoleServer.WriteLine("==>repair:" + rpd.devName + "," + rpd.dt.ToString());
}
catch (Exception ex)
{
ConsoleServer.WriteLine( ex.Message + ex.StackTrace);
}
}
for (int i = 0; i < aryThread.Count; i++)
((System.Threading.Thread)aryThread[i]).Join();
aryThread.Clear();
}
catch (Exception x)
{
ConsoleServer.WriteLine(x.Message+ x.StackTrace);
}
finally
{
try
{
cn.Close();
}
catch { ;}
}
}
}
//dBase functions
//Use Intersolv ODBC Driver v3.10
private static void SaveDBF(DataTable DT, byte saveType)
{
StringBuilder shortPath = new StringBuilder(300);
Com_WinApi.GetShortPathName(driver.Config.ConfigManager.Instance.CommonConfiguration.Path_Cheques, shortPath, shortPath.Capacity);
//Check
if (File.Exists(driver.Config.ConfigManager.Instance.CommonConfiguration.Path_Cheques + "\\" + DT.TableName + ".DBF"))
File.Delete(driver.Config.ConfigManager.Instance.CommonConfiguration.Path_Cheques + "\\" + DT.TableName + ".DBF");
//Creating connection
System.Data.Odbc.OdbcConnectionStringBuilder c_string = new System.Data.Odbc.OdbcConnectionStringBuilder();
c_string.Driver = "VODBFODBC";
c_string.Dsn = "VODBF";
c_string.Add("DB", shortPath.ToString());
c_string.Add("ULN", 1);
c_string.Add("MS", 1);
System.Data.Odbc.OdbcConnection odbc_connection = new System.Data.Odbc.OdbcConnection(c_string.ConnectionString);
try
{
odbc_connection.Open();
}
catch { }
System.Data.Odbc.OdbcCommand odbc_command = odbc_connection.CreateCommand();
//Creating Table
odbc_command.CommandText = "CREATE TABLE " + DT.TableName + " (ID char(10), AP numeric(11, 5), VQ numeric(11, 3), DR numeric(8, 2))";
try
{
odbc_command.ExecuteNonQuery();
}
catch { }
//Adding data
string[] cols = new string[] { "PRICE", "PACK" };
if (saveType != 0)
Array.Reverse(cols);
for (int i = 0; i < DT.Rows.Count; i++)
{
odbc_command.CommandText = "INSERT INTO " + DT.TableName + " (ID,AP,VQ,DR) values (";
odbc_command.CommandText += "\'" + DT.Rows[i]["ID"] + "\'";
odbc_command.CommandText += ",";
odbc_command.CommandText += MathLib.ReplaceNDS(DT.Rows[i][cols[0]].ToString(), ".");
odbc_command.CommandText += ",";
odbc_command.CommandText += MathLib.ReplaceNDS(DT.Rows[i]["TOT"].ToString(), ".");
odbc_command.CommandText += ",";
odbc_command.CommandText += MathLib.ReplaceNDS(DT.Rows[i][cols[1]].ToString(), ".");
odbc_command.CommandText += ");";
try
{
odbc_command.ExecuteNonQuery();
}
catch { }
}
odbc_connection.Close();
//string shortPath = "C:\\" + DT.TableName + ".dbf";
//mktChequeRow[] rows = new mktChequeRow[DT.Rows.Count + 5];
//for (int i = 0; i < DT.Rows.Count + 5; i++)
//{
// rows[i].sz = DT.Rows[1]["ID"].ToString().ToCharArray();
// rows[i].r1 = (double)DT.Rows[1]["PRICE"];
// rows[i].r2 = (double)DT.Rows[1]["TOT"];
// rows[i].r3 = (double)DT.Rows[1]["PACK"];
//}
//UInt32 r = MakeCheque((UInt32)DT.Rows.Count + 5, rows, shortPath);
}
public void loadUnitRoad()
{
ConsoleServer.WriteLine("loading " + this.lineid);
lock (this)
{
System.Data.Odbc.OdbcConnection cn = new System.Data.Odbc.OdbcConnection(Global.Db2ConnectionString);
try
{
hsUnitRoads[0].Clear();
hsUnitRoads[1].Clear();
System.Data.Odbc.OdbcCommand cmd = new System.Data.Odbc.OdbcCommand("select direction,startmileage,endmileage,unitid from tblGroupUnitRoad where lineid='" + this.lineid + "'");
cmd.Connection = cn;
cn.Open();
System.Data.Odbc.OdbcDataReader rd = cmd.ExecuteReader();
while (rd.Read())
{
string dir, unitid;
int startmil, endmil;
dir = System.Convert.ToString(rd[0]);
startmil = System.Convert.ToInt32(rd[1]);
endmil = System.Convert.ToInt32(rd[2]);
unitid = System.Convert.ToString(rd[3]);
hsUnitRoads[direction.IndexOf(dir)].Add(startmil, new UnitRoad(this,lineid,dir,unitid, startmil, endmil,(direction.IndexOf(dir)==0)?true:false));
}
}
catch (Exception ex)
{
ConsoleServer.WriteLine(ex.Message + ex.StackTrace);
}
finally
{
cn.Close();
}
}
}
public void loadSection()
{
System.Data.Odbc.OdbcConnection cn = new System.Data.Odbc.OdbcConnection(Global.Db2ConnectionString);
try
{
hsSections[0].Clear();
hsSections[1].Clear();
System.Data.Odbc.OdbcCommand cmd = new System.Data.Odbc.OdbcCommand("select lineid,sectionid,sectionName,direction,startmileage,endmileage,maxspeed,minspeed,unit,Threshold from vwSection where lineid='" + this.lineid + "' ");
cmd.Connection = cn;
cn.Open();
System.Data.Odbc.OdbcDataReader rd = cmd.ExecuteReader();
while (rd.Read())
{
string secname, secid, dir;
int unit, threshold;
int startmile, endmile, maxspd, minspd;
dir = System.Convert.ToString(rd[3]);
secid = System.Convert.ToString(rd[1]);
secname = System.Convert.ToString(rd[2]);
startmile = System.Convert.ToInt32(rd[4]);
endmile = System.Convert.ToInt32(rd[5]);
maxspd = System.Convert.ToInt32(rd[6]);
minspd = System.Convert.ToInt32(rd[7]);
unit = System.Convert.ToInt32(rd[8]);
threshold = System.Convert.ToInt32(rd[9]);
try
{
Section sec=new Section(this, lineid, secid, secname, dir, startmile, endmile, maxspd, minspd,unit,threshold);
hsSections[this.direction.IndexOf(dir)].Add(secid,sec );
aryAllSections.Add(sec);
}
catch (Exception ex)
{
;
}
}
}
catch (Exception ex)
{
ConsoleServer.WriteLine(ex.Message + ex.StackTrace);
}
finally
{
cn.Close();
}
}
public void ConnectToData()
{
System.Data.Odbc.OdbcConnection conn =
new System.Data.Odbc.OdbcConnection();
// TODO: Modify the connection string and include any
// additional required properties for your database.
conn.ConnectionString = "Dsn=C4A;dbq=C:/USERS/WBEINHAU/DROPBOX/AUTOMATEN/C4A/C4A_DEMO_2/C4A_DEMO_2/APP_DATA/C4A_Ressources_3.accdb;driverid=25;fil=MS Access;";
try
{
conn.Open();
HyperLink4.Text = conn.GetSchema().ToString();
// Process data here.
}
catch (Exception ex)
{
HyperLink4.Text = "Schnubbeldibums";
}
finally
{
conn.Close();
}
}
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();
}
}
void DataRepairTask()
{
System.Data.Odbc.OdbcConnection cn = new System.Data.Odbc.OdbcConnection(Comm.DB2.Db2.db2ConnectionStr);
System.Data.Odbc.OdbcCommand cmd = new System.Data.Odbc.OdbcCommand();
cmd.Connection = cn;
string devName = "";
DateTime dt = new DateTime();
while (true)
{
Comm.TC.WDTC tc;
if (!IsLoadTcCompleted)
{
System.Threading.Thread.Sleep(1000);
continue;
}
try
{
cn.Open();
string sqlGetRepair = "select * from (select t1.DEVICENAME, TIMESTAMP ,trycnt,datavalidity,comm_state from TBLWDDATA10MIN t1 inner join tblDeviceConfig t2 on t1.devicename=t2.devicename where mfccid='{0}' and TIMESTAMP between '{1}' and '{2}' and trycnt <1 and datavalidity='N' and comm_state=1 and enable='Y' fetch first 300 row only) order by trycnt,timestamp desc ";
cmd.CommandText = string.Format(sqlGetRepair, mfccid, Comm.DB2.Db2.getTimeStampString(System.DateTime.Now.AddDays(-7)), Comm.DB2.Db2.getTimeStampString(System.DateTime.Now.AddMinutes(-10)));
ConsoleServer.WriteLine("Repair sql check!");
System.Data.Odbc.OdbcDataReader rd = cmd.ExecuteReader();
ConsoleServer.WriteLine("Repair task beg!");
while (rd.Read())
{
try
{
devName = rd[0] as string;
dt = System.Convert.ToDateTime(rd[1]);
if (Program.mfcc_wd.manager.IsContains(devName))
tc = (Comm.TC.WDTC)Program.mfcc_wd.manager[devName];
else
continue;
if (!tc.IsConnected) continue;
System.Data.DataSet ds = this.protocol.GetSendDataSet("get_unread_data");
ds.Tables[0].Rows[0]["day"] = dt.Day;
ds.Tables[0].Rows[0]["hour"] = dt.Hour;
ds.Tables[0].Rows[0]["minute"] = dt.Minute;
Comm.SendPackage pkg = this.protocol.GetSendPackage(ds, 0xffff);
tc.Send(pkg);
if (pkg.ReturnTextPackage == null && pkg.result== Comm.CmdResult.ACK)
throw new Exception(tc.DeviceName + "," + dt + ",回補資料失敗!");
if (pkg.ReturnTextPackage.Text.Length != 13)
throw new Exception("回補資料長度錯誤");
ds = protocol.GetReturnDsByTextPackage(pkg.ReturnTextPackage);
string sql = string.Format("update tblWDData10Min set DataValidity='V',DataType=1,average_wind_speed={0},average_wind_direction={1},max_wind_speed={2},max_wind_direction={3},am_degree={4} where devicename='{5}' and timestamp='{6}' ",
ds.Tables[0].Rows[0]["average_wind_speed"], ds.Tables[0].Rows[0]["average_wind_direction"], ds.Tables[0].Rows[0]["max_wind_speed"], ds.Tables[0].Rows[0]["max_wind_direction"], ds.Tables[0].Rows[0]["am_degree"], tc.DeviceName, Comm.DB2.Db2.getTimeStampString(dt));
dbServer.SendSqlCmd(sql);
ConsoleServer.WriteLine("==>repair:" + devName + "," + dt.ToString());
}
catch (Exception ex)
{
ConsoleServer.WriteLine(devName + "," + ex.Message + ex.StackTrace);
try
{
dbServer.SendSqlCmd(string.Format("update TBLWDDATA10MIN set trycnt=trycnt+1 where devicename='{0}' and timestamp='{1}'", devName, Comm.DB2.Db2.getTimeStampString(dt)));
}
catch (Exception ee)
{
ConsoleServer.WriteLine(ee.Message + ee.StackTrace);
}
}
}
rd.Close();
System.Threading.Thread.Sleep(1000 * 60);
}
catch (Exception x)
{
ConsoleServer.WriteLine(x.Message + x.StackTrace);
}
finally
{
cn.Close();
}
} //while
}
private void loadMainLineOutDevice(TC.DevcieManager devMgr)
{
lines.Clear();
foreach (DeviceBaseWrapper dev in devMgr.getOutputDeviceEnum())
{
if (!lines.Contains(dev.lineid + "-" + dev.direction))
{
lines.Add(dev.lineid + "-" + dev.direction, ArrayList.Synchronized(new System.Collections.ArrayList()));
// lineJamRanges.Add(dev.lineid + "-" + dev.direction, ArrayList.Synchronized(new System.Collections.ArrayList()));
}
//if (dev.location == "F" || dev.location == "H" || dev.location == "T")
//{
((ArrayList)lines[dev.lineid + "-" + dev.direction]).Add(dev);
//}
}
System.Collections.Hashtable hsInters = new Hashtable();
System.Data.Odbc.OdbcConnection cn = new System.Data.Odbc.OdbcConnection(Global.Db2ConnectionString);
System.Data.Odbc.OdbcCommand cmd = new System.Data.Odbc.OdbcCommand("Select divisiontype,lineid1,direction1,mileage1,lineid2,direction2,mileage2 from vwcloverleaf");
cmd.Connection = cn;
try
{
cn.Open();
System.Data.Odbc.OdbcDataReader rd = cmd.ExecuteReader();
while (rd.Read())
{
try
{
string divisionType, lineid1, direction1, lineid2, direction2;
int mileage1, mileage2;
divisionType = rd[0].ToString();
lineid1 = rd[1].ToString();
direction1 = rd[2].ToString();
mileage1 = System.Convert.ToInt32(rd[3]);
if (divisionType == "C") //系統交流道
{
lineid2 = rd[4].ToString();
direction2 = rd[5].ToString();
mileage2 = System.Convert.ToInt32(rd[6]);
}
else //匝道
{
lineid2 = lineid1;
direction2 = direction1;
mileage2 = mileage1;
}
InterSection intersec = new InterSection(divisionType, lineid1, direction1, mileage1, lineid2, direction2, mileage2);
if (!hsInters.Contains(intersec.getDevName()))
{
hsInters.Add(intersec.getDevName(), intersec);
try
{
((ArrayList)lines[intersec.getLineID() + "-" + intersec.getDirection()]).Add(intersec);
}
catch (Exception ex2)
{
ConsoleServer.WriteLine(ex2.Message + "," + ex2.StackTrace);
}
}
else if (intersec.getDevType() == "C")
{
((InterSection)hsInters[intersec.getDevName()]).BranchName2 = intersec.BranchName1;
}
//if (lines.Contains(lineid1 + "-" + direction1))
//{
//}
}
catch (Exception ex1)
{
ConsoleServer.WriteLine(ex1.Message + "," + ex1.StackTrace);
}
}
}
catch (Exception ex)
{
ConsoleServer.WriteLine(ex.Message + "," + ex.StackTrace);
}
finally
{
cn.Close();
}
IDictionaryEnumerator iesec = hsInters.GetEnumerator();
while (iesec.MoveNext())
{
try
{
InterSection sec = (InterSection)iesec.Value;
if (sec.type == "C")
{
sec.branch1 = (InterSection)hsInters[sec.BranchName1];
sec.branch2 = (InterSection)hsInters[sec.BranchName2];
}
}
catch (Exception ex)
{
ConsoleServer.WriteLine(ex.Message + "," + ex.StackTrace);
}
}
IDictionaryEnumerator ie = lines.GetEnumerator(); //取得所有的路線
while (ie.MoveNext())
{
((ArrayList)ie.Value).Sort(); //排序所有的車輛偵測器
ArrayList list = (ArrayList)ie.Value;
//if (list.Count == 1)
// (vdlist[0] as DeviceBaseWrapper).AryInx = 0;
// 填上前後車輛偵測器鏈結
// if (list.Count > 1)
for (int i = 0; i < list.Count; i++)
{
// ((DeviceBaseWrapper)vdlist[i]).AryInx = i;
if (i == 0)
((I_Positionable)list[i]).setNextDev( list[i + 1] as I_Positionable);
else if (i == list.Count - 1)
((I_Positionable)list[i]).setPreDev( list[i - 1] as I_Positionable);
else
{
((I_Positionable)list[i]).setPreDev( list[i - 1] as I_Positionable);
((I_Positionable)list[i]).setNextDev( list[i + 1] as I_Positionable);
}
}
}
// oneMinTmr.Elapsed += new System.Timers.ElapsedEventHandler(oneMinTmr_Elapsed);
// oneMinTmr.Start();
//oneMinTmr = new System.Threading.Timer(new System.Threading.TimerCallback(oneMinTmr_Elapsed));
//oneMinTmr.Change(0, 60 * 1000);
ConsoleServer.WriteLine("設備收尋管理啟動完成!");
}
void loadLine()
{
lock (this)
{
System.Data.Odbc.OdbcConnection cn = new System.Data.Odbc.OdbcConnection(Global.Db2ConnectionString);
try
{
hsLines.Clear();
#if !DEBUG
System.Data.Odbc.OdbcCommand cmd = new System.Data.Odbc.OdbcCommand("select lineid,linename,direction,startmileage,endmileage from tblGroupLine where enable='Y' ");
#else
System.Data.Odbc.OdbcCommand cmd = new System.Data.Odbc.OdbcCommand("select lineid,linename,direction,startmileage,endmileage from tblGroupLine where enable='Y' ");
// System.Data.Odbc.OdbcCommand cmd = new System.Data.Odbc.OdbcCommand("select lineid,linename,direction,startmileage,endmileage from tblGroupLine where enable='Y' ");
#endif
cmd.Connection = cn;
cn.Open();
System.Data.Odbc.OdbcDataReader rd = cmd.ExecuteReader();
while (rd.Read())
{
string linename, lineid, direction;
int startmileage, endmileage;
lineid = System.Convert.ToString(rd[0]);
// if (lineid != "N99") continue;
linename = System.Convert.ToString(rd[1]);
direction = System.Convert.ToString(rd[2]);
startmileage = System.Convert.ToInt32(rd[3]);
endmileage = System.Convert.ToInt32(rd[4]);
hsLines.Add(lineid, new Line(lineid, linename, direction, startmileage, endmileage));
}
}
catch (Exception ex)
{
ConsoleServer.WriteLine(ex.Message + ex.StackTrace);
}
finally
{
cn.Close();
}
}
}
public void LogMfccStart(string ProcessName, bool bPlay)
{
System.Data.Odbc.OdbcConnection cn = new System.Data.Odbc.OdbcConnection("dsn=TCS;uid=db2inst1;pwd=db2inst1");
string sql= "insert into tblMFCCStateLog (TIMESTAMP,MFCCId,CONN_State) values('{0}','{1}',{2})" ;
System.Data.Odbc.OdbcCommand cmd=new System.Data.Odbc.OdbcCommand(string.Format(sql, getTimeStampString(DateTime.Now), ProcessName, bPlay ? 1 : 3));
try
{
cn.Open();
cmd.Connection = cn;
cmd.ExecuteNonQuery();
}
finally
{
cn.Close();
}
}
public static void LoadTrafficCSLSRule()
{
System.Data.Odbc.OdbcConnection cn = new System.Data.Odbc.OdbcConnection(DbCmdServer.getDbConnectStr());
System.Data.Odbc.OdbcCommand cmd = new System.Data.Odbc.OdbcCommand()
{
CommandText = "select ls,rs,level,lo,ro from tblvddegree where location='I' order by level",
Connection = cn
};
lock (cslsRules)
{
cslsRules.Clear();
try
{
cn.Open();
System.Data.Odbc.OdbcDataReader rd = cmd.ExecuteReader();
while (rd.Read())
{
int ls, rs, level,lo,ro;
ls = System.Convert.ToInt32(rd["ls"]);
rs = System.Convert.ToInt32(rd["rs"]);
lo = System.Convert.ToInt32(rd["lo"]);
ro = System.Convert.ToInt32(rd["ro"]);
level = System.Convert.ToInt32(rd["level"]);
cslsRules.Add(new TrafficCSLSCOntrolRule()
{
level = level,
rs = rs,
ls = ls,
lo=lo,
ro=ro
});
}
}
catch (Exception ex)
{
ConsoleServer.WriteLine(ex.Message + "," + ex.StackTrace);
}
finally
{
cn.Close();
}
} //lock
}
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 { ;}
}
}
private void btnGuardar_Click(object sender, RoutedEventArgs e)
{
if(txtHost.Text == "")
{
ITCMessageBox.Show(this, "Debes ingresar un Host", "Error!");
txtHost.Focus();
}
else if (txtPort.Text == "")
{
ITCMessageBox.Show(this, "Debes ingresar un Puerto", "Error!");
txtPort.Focus();
}
else if (txtUID.Text == "")
{
ITCMessageBox.Show(this, "Debes ingresar un Usuario", "Error!");
txtUID.Focus();
}
else
{
string sCon;
sCon = "DRIVER={Adaptive Server Enterprise};";
//sCon = "DRIVER={SQL ANYWHERE 11};";
sCon += "server=" + txtHost.Text + ";";
sCon += "port=" + txtPort.Text + ";";
sCon += "uid=" + txtUID.Text + ";";
if(txtPSW.Text != "")
{
sCon += "pwd=" + txtPSW.Text + ";";
}
if (txtDB.Text != "")
{
sCon += "db=" + txtDB.Text + ";";
}
//sCon += "DSN=\"\"";
System.Data.Odbc.OdbcConnection myConn = new System.Data.Odbc.OdbcConnection();
myConn.ConnectionString = sCon;
try
{
myConn.Open();
((Menu)this.Owner).sConexion = sCon;
((Menu)this.Owner).bConexion = true;
myConn.Close();
this.Close();
}
catch(Exception ex)
{
ITCMessageBox.Show(this, "Error en los datos de conexión, no se pude realizar la conexion con los parametros seleccionados", "Error!");
txtHost.Focus();
}
}
}
void DbSQLExecute_Task(object args)
{
// bool isInLock = false;
#if DEBUG
return;
#endif
int inx = System.Convert.ToInt32(args);
System.Data.Odbc.OdbcConnection cn;
System.DateTime dt = System.DateTime.Now; ;
System.Data.Odbc.OdbcCommand cmd = new System.Data.Odbc.OdbcCommand();
cmd.CommandTimeout = 120;
cn = new System.Data.Odbc.OdbcConnection(DbCmdServer.getDbConnectStr());
cmd.Connection = cn;
cn.Open();
ConsoleServer.WriteLine("Db task started!");
state[inx] = 0;
while (true)
{
try
{
state[inx] = 1;
// lock (sqlLockObj)
// {
state[inx] = 2;
if (sqlQueue.Count == 0)
{
if(isPrint)
ConsoleServer.WriteLine("process cnt:" + processcnt);
cn.Close();
lock(this.sqlLockObj)
{
state[inx]=10;
System.Threading.Monitor.Wait(sqlLockObj);
state[inx] = 11;
}
cn.Open();
processcnt = 0;
}
state[inx] = 3;
//}
while( sqlQueue.Count > 0)
{
try
{
dt = System.DateTime.Now;
//lock (sqlLockObj)
//{
state[inx] = 4;
try
{
cmd.CommandText = System.Convert.ToString(sqlQueue.Dequeue());
}
catch { ;}
//}
lastcmd[inx] = cmd.CommandText;
state[inx] = 9;
cmd.ExecuteNonQuery();
ProcessCntPerMin++;
processcnt++;
state[inx] = 5;
if (System.DateTime.Now - dt > new TimeSpan(0, 0, 30))
ConsoleServer.WriteLine("db Executeion time longer than 30 sec:" + cmd.CommandText);
state[inx] = 6;
errcnt = 0;
if (dbqmode == DBQueueMode.Slow) //2013/2/25
System.Threading.Thread.Sleep(1000);
// ConsoleServer.WriteLine("finish!");
}
//catch (System.Data.Odbc.OdbcException odbcex)
//{
// if(odbcex.ErrorCode!=-2147467259) // repeat unixodbc32
// {
// ConsoleServer.WriteLine(odbcex.ErrorCode+","+odbcex.Message+cmd.CommandText);
// }
// else
// Console.WriteLine(odbcex.ErrorCode + "," + odbcex.Message);
// try { cn.Close(); }
// catch { ;}
// try
// {
// cn = new System.Data.Odbc.OdbcConnection(Comm.DB2.Db2.db2ConnectionStr);
// cmd.Connection = cn;
// cn.Open();
// }
// catch { ;}
//}
catch (Exception ex1)
{
errcnt++;
//if(ex1 is System.Data.Odbc.OdbcException)
// ConsoleServer.WriteLine("db exception:"+(ex1 as System.Data.Odbc.OdbcException).Message + cmd.CommandText);
//else
ConsoleServer.WriteLine("db exception:"+ex1.Message + cmd.CommandText);
RemoteInterface.Util.SysLog("dberr.log",System.DateTime.Now+","+ex1.Message+","+cmd.CommandText);
try {
// if ( cn.State == System.Data.ConnectionState.Broken || cn.State == System.Data.ConnectionState.Closed )
// {
try
{
cn.Close();
}
catch { ;}
try
{
cn.Open();
cmd.Connection = cn;
}
catch { ;}
// }
}
catch { ;}
//try
//{
// // state[inx] = 7;
// //// cn = new System.Data.Odbc.OdbcConnection(Comm.DB2.Db2.db2ConnectionStr);
// // cmd.Connection = cn;
// // cn.Open();
// // state[inx] = 8;
//}
//catch (Exception ex){
// ConsoleServer.WriteLine(" In DbSQLExecute_Task " + "," + ex.Message + "," + ex.StackTrace);
//}
}
}
}
catch (Exception ex)
{
ConsoleServer.WriteLine(ex.Message + ex.StackTrace);
}
}
}