/// <summary>
/// 返回指定Sql语句的DbDataReader
/// 方法关闭数据库连接
/// </summary>
/// <param name="sqlstr">传入的Sql语句</param>
/// <returns>DBDataReader对象</returns>
public virtual DbDataReader GetDataReader(string sqlstr)
{
//程序不能返回抽象类，故用分数据访问分工来生成DataReader对象
if (databaseType == "Oracle")
{
System.Data.OracleClient.OracleCommand cmd = new System.Data.OracleClient.OracleCommand();
System.Data.OracleClient.OracleDataReader oraDr = cmd.ExecuteReader();
return(oraDr);
}
else if (databaseType == "SqlServer")
{
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
System.Data.SqlClient.SqlDataReader sqlDr = cmd.ExecuteReader();
return(sqlDr);
}
else if (databaseType == "Sybase")
{
System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand();
System.Data.OleDb.OleDbDataReader oleDr = cmd.ExecuteReader();
return(oleDr);
}
else
{
System.Data.Odbc.OdbcCommand cmd = new System.Data.Odbc.OdbcCommand();
System.Data.Odbc.OdbcDataReader odbcDr = cmd.ExecuteReader();
return(odbcDr);
}
}
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 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 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 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;
}
}
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 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);
}
/// <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);
}
public DataTable GetSchema(string SQLstr)
{
System.Data.Odbc.OdbcCommand cmd = new System.Data.Odbc.OdbcCommand(SQLstr, this.mCon);
System.Data.Odbc.OdbcDataReader dr;
DataTable dt = new DataTable();
this.mCon.Open();
dr = cmd.ExecuteReader(CommandBehavior.KeyInfo);
dt = dr.GetSchemaTable();
dr.Close();
cmd.Dispose();
this.mCon.Close();
return(dt);
}
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);
}
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;
}
}
public override DataTable GetSchemaTable(string sql)
{
try
{
using (var conn = new System.Data.Odbc.OdbcConnection(ProviderString))
{
conn.Open();
var cmd = new System.Data.Odbc.OdbcCommand(sql, conn);
var rdr = cmd.ExecuteReader(CommandBehavior.SchemaOnly);
return(rdr.GetSchemaTable());
}
}
catch
{
return(null);
}
}
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 authenticate()
{
InitializeComponent();
ConStr = "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=localhost; PORT=3306; DATABASE=authenticate; UID=auth; PWD=;OPTION=3";
AuthCon = new System.Data.Odbc.OdbcConnection(ConStr);
try
{
if (AuthCon.State == ConnectionState.Closed)
{
AuthCon.Open();
}
//MessageBox.Show("Connection Opened");
}
catch (System.Data.Odbc.OdbcException Ex)
{
MessageBox.Show("Could not access the database.\r\nPlease make sure you completed the fields with the correct information and try again.\r\n\r\nMore details:\r\n" + Ex.Message, "Database connection error", MessageBoxButtons.OK, MessageBoxIcon.Error);
this.Close();
}
#region if password not set
AuthCom = new System.Data.Odbc.OdbcCommand("SELECT passwd FROM mis_pass", AuthCon);
AuthRead = AuthCom.ExecuteReader();
string chkpass;
string[] name = { "admin", "guest" };
int term = 0;
while (AuthRead.Read())
{
chkpass = (string)AuthRead[0];
if (chkpass == "")
{
MessageBox.Show(" Please set the password for " + name[term]);
txtbxusername.Text = name[term];
txtbxusername.ReadOnly = true;
initialset = true;
break;
}
if (++term > 1)
{
break;
}
}
#endregion
}
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,
}
/// <summary>
/// 获取医保结算结果
/// </summary>
/// <returns></returns>
public int GetSiResult(string path, string tablename, ref Neusoft.HISFC.Models.RADT.PatientInfo p, ref string errTxt)
{
//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 select = "select GRSHBZH ,ZYH ,ZYZJE ,SBZFJE ,GWYBZJE ,GRZFJE ,ZFYY 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(-1);
}
p.IDCard = cmReader[0].ToString();
p.PID.PatientNO = cmReader[1].ToString();
p.SIMainInfo.TotCost = Neusoft.FrameWork.Function.NConvert.ToDecimal(cmReader[2]);
p.SIMainInfo.PubCost = Neusoft.FrameWork.Function.NConvert.ToDecimal(cmReader[3]);
p.SIMainInfo.OfficalCost = Neusoft.FrameWork.Function.NConvert.ToDecimal(cmReader[4]);
p.SIMainInfo.OwnCost = Neusoft.FrameWork.Function.NConvert.ToDecimal(cmReader[5]);
p.SIMainInfo.ID = cmReader[6].ToString();//自费原因
cmReader.Close();
cmSelect.Dispose();
myconn.Dispose();
return(1);
}
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;
}
}
void loadETStatus()
{
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 deviceName,telcode,f1,f2,f3,x1,x2 from tblETConfig");
cmd.Connection = cn;
try
{
cn.Open();
System.Data.Odbc.OdbcDataReader rd = cmd.ExecuteReader();
while (rd.Read())
{
string devicename;
string telcode;
byte[]f=new byte[3];
byte[] x = new byte[2];
devicename = rd[0].ToString();
// telcode = rd[1].ToString();
f[0] = System.Convert.ToByte(rd[2]);
f[1] = System.Convert.ToByte(rd[3]);
f[2] = System.Convert.ToByte(rd[4]);
x[0] = System.Convert.ToByte(rd[5]);
x[1] = System.Convert.ToByte(rd[6]);
try
{
hsEtStatus.Add(devicename, new ETStatus(devicename, f, x));
}
catch (Exception ex1)
{
ConsoleServer.WriteLine(ex1.Message + "," + ex1.StackTrace);
}
}
}
catch (Exception ex)
{
cn.Close();
}
}
public int ListarDados(cDados obj, ref cDados[] array)
{
string sql;
try
{
sql = "SELECT id, id_pessoa, data_hora, freq_cardiaca FROM dados WHERE id_pessoa = " + obj.id_avaliacao.ToString() + " ORDER BY data_hora";
sqlConn = new System.Data.Odbc.OdbcConnection(connString);
sqlConn.Open();
sqlCmd = new System.Data.Odbc.OdbcCommand(sql, sqlConn);
System.Data.Odbc.OdbcDataReader data;
data = sqlCmd.ExecuteReader();
int cont = 0;
while (data.Read())
{
Array.Resize(ref array, array.Length + 1);
array[cont] = new cDados();
array[cont].id = data.GetInt32(0);
array[cont].id_avaliacao = data.GetInt32(1);
array[cont].data = data.GetDateTime(2);
array[cont].fc = data.GetInt32(3);
cont++;
}
sqlConn.Close();
return(cont);
}
catch (Exception Ex)
{
return(-1);
}
}
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 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;
}
// <summary>
/// recompute indexes for selected table
/// to speed up queries
/// </summary>
/// <returns></returns>
public static void UpdateIndexes(string tableType, DbConnect dbConnect)
{
ProtCidSettings.progressInfo.ResetCurrentProgressInfo();
DbQuery dbQuery = new DbQuery();
try
{
System.Data.Odbc.OdbcCommand updateIndexCommand = dbConnect.CreateCommand();
// retrieve user-defined indexes
string showIndexStr = @"select RDB$INDEX_NAME, RDB$RELATION_NAME from RDB$INDICES WHERE RDB$SYSTEM_FLAG = 0;";
//string showIndexStr = @"select RDB$INDEX_NAME from RDB$INDICES;";
updateIndexCommand.CommandText = showIndexStr;
System.Data.Odbc.OdbcDataReader indexReader = updateIndexCommand.ExecuteReader();
// ArrayList indexList = new ArrayList ();
Dictionary <string, List <string> > relationIndexHash = new Dictionary <string, List <string> > ();
string indexName = "";
string relationName = "";
if (indexReader.HasRows)
{
while (indexReader.Read())
{
relationName = indexReader.GetString(1).Trim().ToUpper();
if (relationName.IndexOf(tableType.ToUpper()) > -1)
{
indexName = indexReader.GetString(0).Trim().ToUpper();
if (relationIndexHash.ContainsKey(relationName))
{
relationIndexHash[relationName].Add(indexName);
}
else
{
List <string> relationIndexList = new List <string> ();
relationIndexList.Add(indexName);
relationIndexHash.Add(relationName, relationIndexList);
}
}
}
indexReader.Close();
}
foreach (string relationTable in relationIndexHash.Keys)
{
foreach (string index in relationIndexHash[relationTable])
{
// rebuild this index for cryst and interface
if (indexName.ToString().ToUpper().IndexOf("RDB$PRIMARY") == -1)
{
string inactiveIndexStr = string.Format("ALTER INDEX {0} INACTIVE;", index);
updateIndexCommand.CommandText = inactiveIndexStr;
updateIndexCommand.ExecuteNonQuery();
string activeIndexStr = string.Format("ALTER INDEX {0} ACTIVE;", index);
updateIndexCommand.CommandText = activeIndexStr;
updateIndexCommand.ExecuteNonQuery();
}
// recompute selectivity of this index
string updateSelectivityStr = string.Format("SET STATISTICS INDEX {0};", index);
updateIndexCommand.CommandText = updateSelectivityStr;
updateIndexCommand.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
// Displays the Error Message in the progress label.
ProtCidSettings.progressInfo.progStrQueue.Enqueue("Update Indexes Errors: " + ex.Message);
}
}
private List<MatrixTestResult> GetPerformanceMatrixResults(GetPerformanceResultsFilter filter)
{
List<MatrixTestResult> matrixResults = new List<MatrixTestResult>();
System.Data.Odbc.OdbcConnection conn = createSQLConnection();
try
{
string mySelectQuery;
switch (filter)
{
case GetPerformanceResultsFilter.MonoTouch:
mySelectQuery = "SELECT * FROM `PerformanceMatrixTestResults` WHERE `isMonoTouch` = 1 ORDER BY `DBDeviceId` ASC";
break;
case GetPerformanceResultsFilter.ObjectiveC:
mySelectQuery = "SELECT * FROM `PerformanceMatrixTestResults` WHERE `isMonoTouch` = 0 ORDER BY `DBDeviceId` ASC";
break;
default:
mySelectQuery = "SELECT * FROM `PerformanceMatrixTestResults` ORDER BY `PerformanceMatrixTestResults`.`DBDeviceId` ASC";
break;
}
System.Data.Odbc.OdbcCommand myCommand = new System.Data.Odbc.OdbcCommand(mySelectQuery, conn);
conn.Open();
System.Data.Odbc.OdbcDataReader myReader = myCommand.ExecuteReader();
try
{
while (myReader.Read())
{
matrixResults.Add(new MatrixTestResult()
{
DatabaseId = (int)myReader["id"],
DeviceDatabaseId = (int)myReader["DBDeviceId"],
CSTestResult = (float)myReader["CSTestResult"],
CTestResult = (float)myReader["CTestResult"],
BLASTestResult = (float)myReader["BLASTestResult"],
IsMonoTouch = Convert.ToBoolean(myReader["isMonoTouch"])
});
}
}
finally
{
myReader.Close();
}
}
finally
{
conn.Close();
}
return matrixResults;
}
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("設備收尋管理啟動完成!");
}
private void btnOKnp_Click(object sender, EventArgs e)
{
if (txtBxnewuser.Text == "")
{
MessageBox.Show(" Username not specified ");
}
else if (txtBxnewuser.Text != "")
{
if (AuthCon.State == ConnectionState.Open)
{
AuthCom = new System.Data.Odbc.OdbcCommand("SELECT username FROM mis_pass", AuthCon);
AuthRead = AuthCom.ExecuteReader();
string usrnm = "";
bool usrchek = false;//indicate that user does not exist
while (AuthRead.Read())
{
usrnm = (string)AuthRead[0];
if (usrnm.CompareTo(txtBxnewuser.Text) == 0)
{
usrchek = true; // user exists
break;
}
}
if (usrchek == false)
{
MessageBox.Show(" Username name does not exist. Please re-check ");
}
else if (usrchek == true)
{
AuthCom = new System.Data.Odbc.OdbcCommand("SELECT passwd FROM mis_pass WHERE username='******'", AuthCon);
AuthRead = AuthCom.ExecuteReader();
string passwd = "";
bool passchek = false;//indicate that password is invalid
while (AuthRead.Read())
{
passwd = (string)AuthRead[0];
passwd = DecodePass(passwd, passwd.Length);
if (passwd.CompareTo(txtBxoldpass.Text) == 0)
{
passchek = true; // password valid
break;
}
else if (passwd.CompareTo(txtBxoldpass.Text) != 0)
{
MessageBox.Show(" Invalid Password ");
}
}
if (passchek == true)
{
if (txtBxnewpass.Text.CompareTo(txtBxconfirm.Text) != 0)
{
MessageBox.Show("The Passwords do not match. Please Retype");
txtBxconfirm.Text = txtBxnewpass.Text = "";
}
else if (txtBxnewpass.Text.CompareTo(txtBxconfirm.Text) == 0)
{
string newpass = txtBxconfirm.Text;
newpass = EncodePass(newpass, newpass.Length);
AuthCom = new System.Data.Odbc.OdbcCommand("UPDATE mis_pass SET passwd='" + newpass + "' WHERE username='******'", AuthCon);
AuthCom.ExecuteNonQuery();
MessageBox.Show(" Password changed successfully ");
}
}
else if (passchek == false)
{
MessageBox.Show(" Invalid Password ");
}
}
}
}
else if (AuthCon.State == ConnectionState.Closed)
{
MessageBox.Show(" Connection closed ");
}
}
public void loadVIData()
{
System.Data.Odbc.OdbcConnection cn = new System.Data.Odbc.OdbcConnection(RemoteInterface.DbCmdServer.getDbConnectStr());
System.Data.Odbc.OdbcCommand cmd = new System.Data.Odbc.OdbcCommand("select devicename,direction,mile_m from tbldeviceConfig where devicename in ('VI-N6-W-37.2','VI-N6-E-34.6')");
try
{
cmd.Connection = cn;
cn.Open();
System.Data.Odbc.OdbcDataReader rd = cmd.ExecuteReader();
int inx = 0;
while (rd.Read())
{
vi_devNames[inx] = rd[0].ToString();
if (rd[1].ToString() == "E")
vi_dirs[inx] = 0;
else if (rd[1].ToString() == "W")
vi_dirs[inx] = 1;
else if (rd[1].ToString() == "S")
vi_dirs[inx] = 2;
else if (rd[1].ToString() == "N")
vi_dirs[inx] = 3;
vi_mile_ks[inx] = System.Convert.ToInt32(rd[2]) / 1000;
vi_mile_ms[inx] = System.Convert.ToInt32(rd[2]) % 1000;
inx++;
}
}
catch (Exception ex)
{
ConsoleServer.WriteLine(ex.Message + "," + ex.StackTrace);
}
finally
{
cn.Close();
}
}
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();
}
}
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 int getTravelTime(DateTime dt)
//{
// dt = dt.AddSeconds(-dt.Second).AddMinutes(-dt.Minute % 5);
// if(! hsTravelData.Contains(dt))
// return -1;
// return ((TravelData)hsTravelData[dt]).TravelTime;
//}
//public int getSpeed(DateTime dt)
//{
// dt = dt.AddSeconds(-dt.Second).AddMinutes(-dt.Minute % 5);
// if (!hsTravelData.Contains(dt))
// return -1;
// return ((TravelData)hsTravelData[dt]).Speed;
//}
void loadHisTravelTime()
{
System.DateTime dt = System.DateTime.Today;
string sql = "select timestamp,speed,travelTime from tblTTSDataHistory where sectionid='{0}' and gid={1} and classid='{2}' ";
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, this.secid,this._gid,this._classid));
try
{
cn.Open();
cmd.Connection = cn;
System.Data.Odbc.OdbcDataReader rd = cmd.ExecuteReader();
while (rd.Read())
{
DateTime time = Convert.ToDateTime(rd[0]);
int speed = Convert.ToInt32(rd[1]);
int traveltime = Convert.ToInt32(rd[2]);
hsTravelData.Add(time, new TravelData(time, speed, traveltime));
}
}
catch (Exception ex)
{
ConsoleServer.WriteLine(ex.Message + "," + ex.StackTrace);
}
finally
{
cn.Close();
}
}
public PerformanceCubeResult FindPerformanceCubeResult(int databaseId)
{
System.Data.Odbc.OdbcConnection conn = createSQLConnection();
try
{
string selectQuery = string.Format("SELECT * FROM `PerformanceGLCubeResults` WHERE `id` ={0}",
databaseId);
conn.Open();
System.Data.Odbc.OdbcCommand selectCommand = new System.Data.Odbc.OdbcCommand(selectQuery, conn);
try
{
System.Data.Odbc.OdbcDataReader reader = selectCommand.ExecuteReader();
if (!reader.Read())
return null;
return new PerformanceCubeResult()
{
DatabaseId = databaseId,
DeviceDatabaseId = Convert.ToInt32(reader["DBDeviceId"]),
NumberOfTriangles = Convert.ToInt32(reader["NumberTriangles"]),
FramesPerSecond = Convert.ToDouble(reader["FramesPerSecond"])
};
}
catch (Exception)
{
}
}
finally
{
conn.Close();
}
return null;
}
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();
}
}
static void DbTest()
{
string selectCmd="";
selectCmd += "Select * From tbldeviceconfig";
selectCmd += " where DeviceName ='" + "VD-T78-W-25.1" + "'";
System.Data.Odbc.OdbcConnection cn = new System.Data.Odbc.OdbcConnection(Comm.DB2.Db2.db2ConnectionStr);
System.Data.Odbc.OdbcCommand cmd = new System.Data.Odbc.OdbcCommand(selectCmd);
cmd.Connection = cn;
cn.Open();
System.Data.Odbc.OdbcDataReader rd = cmd.ExecuteReader();
rd.Read();
// Console.WriteLine(name);
//for (int i = 0; i < name.Length; i++)
// Console.Write(((int)name[i] + " ");
Console.WriteLine(rd.GetString(Comm.DB2.Db2.getFiledInx(rd,"ip")));
rd.Close();
cn.Close();
}
public List<DeviceInfo> GetDeviceList()
{
// SELECT `DatabaseId`, `ModelName`, `UIIdion`, `SpecificHWVersion`, `OSName`, `OSVersion` FROM `PerformanceAppDeviceInfo`
List<DeviceInfo> retList = new List<DeviceInfo>();
System.Data.Odbc.OdbcConnection conn = createSQLConnection();
try
{
string selectQuery = @"SELECT `DatabaseId`, `ModelName`, `UIIdion`, `SpecificHWVersion`, `OSName`, `OSVersion`
FROM `PerformanceAppDeviceInfo`";
conn.Open();
System.Data.Odbc.OdbcCommand selectCommand = new System.Data.Odbc.OdbcCommand(selectQuery, conn);
try
{
System.Data.Odbc.OdbcDataReader reader = selectCommand.ExecuteReader();
while (reader.Read())
{
DeviceInfo di = new DeviceInfo()
{
DatabaseId = (int)reader["DatabaseId"],
ModelName = reader["ModelName"].ToString(),
OSName = reader["OSName"].ToString(),
OSVersion = reader["OSVersion"].ToString(),
SpecificHWVersion = reader["SpecificHWVersion"].ToString(),
UIIdion = reader["UIIdion"].ToString(),
};
retList.Add(di);
}
}
catch (Exception)
{
}
}
finally
{
conn.Close();
}
return retList;
}
private string GetColumnImage(string imageID, string column, string fileName)
{
string retImageFullName = "";
if (_odbcConnection == null)
{
return(retImageFullName);
}
//ODBC Access
System.Data.Odbc.OdbcCommand odbcCommand = null;
System.Data.Odbc.OdbcDataReader odbcRead = null;
try
{
odbcCommand = new System.Data.Odbc.OdbcCommand();
odbcCommand.CommandText = "SELECT " + column + " FROM DBSDESX.CHVT34A0_IMAGENS WHERE CREFARQ = '" + imageID + "'";
odbcCommand.Connection = _odbcConnection;
odbcRead = odbcCommand.ExecuteReader(); //System.Data.CommandBehavior.SequentialAccess
//byte[] _buf = (byte[]) odbcCommand.ExecuteScalar();
//if (!odbcRead.Read())
// return retImageFullName; //not found
}
catch (System.Exception ex)
{
throw ex;
}
//Read BLOB
System.Text.StringBuilder sb = new System.Text.StringBuilder();
int bufferSize = 100;
byte[] outByte = new byte[bufferSize];
long retval;
long startIndex = 0;
try
{
// Read bytes into outByte[] and retain the number of bytes returned.
retval = odbcRead.GetBytes(0, startIndex, outByte, 0, bufferSize);
// Continue while there are bytes beyond the size of the buffer.
while (retval == bufferSize)
{
//Write the buffer. (convert byte to char)
for (int i = 0; i < retval; i++)
{
sb.Append(System.Convert.ToChar(outByte[i]));
}
// Reposition start index to end of last buffer and fill buffer.
startIndex += bufferSize;
retval = odbcRead.GetBytes(0, startIndex, outByte, 0, bufferSize);
}
// Write the remaining buffer. (convert byte to char)
for (int i = 0; i < retval; i++)
{
sb.Append(System.Convert.ToChar(outByte[i]));
}
}
catch
{
}
//Close DB2 reader
try
{
odbcRead.Close();
odbcCommand.Dispose();
}
catch (System.Exception ex)
{
throw ex;
}
//Create file
try
{
if (sb.Length > 0)
{
string myDocFolder = System.Environment.GetFolderPath(System.Environment.SpecialFolder.CommonPictures) + "\\";
string text = sb.ToString();
byte[] base64EncodedBytes = System.Convert.FromBase64String(text);
System.IO.File.WriteAllBytes(myDocFolder + fileName, base64EncodedBytes);
retImageFullName = myDocFolder + fileName;
}
}
catch (System.Exception ex)
{
throw ex;
}
return(retImageFullName);
}
private void SummReport_Load(object sender, EventArgs e)
{
ConStr = "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=" + constring.hostname + "; PORT=" + constring.portno + "; DATABASE=" + constring.database + "; UID=" + constring.username + "; PWD=" + constring.password + ";OPTION=3";
OdbcCon = new System.Data.Odbc.OdbcConnection(ConStr);
OdbcCon.Open();
radioButton2.Checked = true;
#region region 01
string CellSelectionStr;
string QueryStr_PartI = "SELECT count(*), sum(CallCost), sum(Duration)";
string QueryStr = "";
string QueryStr_PartII = "";
string QueryStr_PartIII = "";
int No_of_Months;
int MonthStart = forform1.stat_datetimestart.Month;
int MonthEnd = forform1.stat_datetimeend.Month;
string MonthString;
string WhereConditionStr = "";
if ((MonthEnd - MonthStart) < 0)
{
No_of_Months = MonthEnd - MonthStart + 12 + 1;
}
else
{
No_of_Months = MonthEnd - MonthStart + 1;
}
#endregion
#region region 02
for (int ChargeClassNo = 1; ChargeClassNo <= 11; ChargeClassNo++)
{
forChargeClass.TotalRec [ChargeClassNo - 1] = 0;
forChargeClass.sumCallCost[ChargeClassNo - 1] = 0;
forChargeClass.SumTotalDuration[ChargeClassNo - 1] = 0;
WhereConditionStr = string.Concat(" ChargeClass = " + ChargeClassNo.ToString() + " and ");
for (int n = 0; n <= 4; n++) //No of Services 0 for pcc , ..... , 4 for uan
{
if (forform1.stat_flagservices[n] == false)
{
continue;
}
for (int MonthNo = MonthStart; MonthNo <= MonthEnd; MonthNo++)
{
#region for No of Months
if (MonthNo > 9)
{
MonthString = MonthNo.ToString();
}
else
{
MonthString = string.Concat("0", MonthNo.ToString());
}
QueryStr_PartII = " from " + ServiceStr[n] + MonthString + " where " + WhereConditionStr;
if (MonthStart == MonthEnd)
{
QueryStr_PartIII = string.Concat("day(StopDateAndTime)>=" + forform1.stat_datetimestart.Day + " and day(StopDateAndTime)<=" + forform1.stat_datetimeend.Day);
}
else if (MonthNo == MonthStart)
{
QueryStr_PartIII = string.Concat("day(StopDateAndTime)>=" + forform1.stat_datetimestart.Day);
}
else if (MonthNo != MonthEnd && MonthNo != MonthStart)
{
QueryStr_PartIII = string.Concat("month(StopDateAndTime)=" + MonthNo.ToString());
}
else if (MonthNo == MonthEnd)
{
QueryStr_PartIII = string.Concat("day(StopDateAndTime)<=" + forform1.stat_datetimeend.Day);
}
QueryStr = string.Concat(QueryStr_PartI, QueryStr_PartII, QueryStr_PartIII);
OdbcCom = new System.Data.Odbc.OdbcCommand(QueryStr, OdbcCon);
OdbcDR = OdbcCom.ExecuteReader();
while (OdbcDR.Read()) //rows
{
forChargeClass.TotalRec [ChargeClassNo - 1] += Int64.Parse(OdbcDR[0].ToString());
if (forChargeClass.TotalRec [ChargeClassNo - 1] == 0)
{
break;
}
//MessageBox.Show(QueryStr+ " "+ forChargeClass.TotalRec [ChargeClassNo - 1].ToString() +" forChargeClass.sumCallCost["+ChargeClassNo.ToString() +"- 1]"+" "+OdbcDR[1].ToString());
if (Int64.Parse(OdbcDR[0].ToString()) != 0)
{
forChargeClass.sumCallCost[ChargeClassNo - 1] += Int64.Parse(OdbcDR[1].ToString()) / 10000;
}
if (Int64.Parse(OdbcDR[0].ToString()) != 0)
{
forChargeClass.SumTotalDuration[ChargeClassNo - 1] += Int64.Parse(OdbcDR[2].ToString());
}
}
#endregion
}
}
}
forChargeClass.TotalRec [11] = 0; forChargeClass.sumCallCost[11] = 0; forChargeClass.SumTotalDuration[11] = 0;
for (int ChargeClassNo = 1; ChargeClassNo <= 11; ChargeClassNo++)
{
forChargeClass.TotalRec [11] += forChargeClass.TotalRec [ChargeClassNo - 1];
forChargeClass.sumCallCost[11] += forChargeClass.sumCallCost[ChargeClassNo - 1];
forChargeClass.SumTotalDuration[11] += forChargeClass.SumTotalDuration[ChargeClassNo - 1];
}
#endregion
txtBxCC.Text = "1";
}
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();
}
}
static void Main(string[] args)
{
string scs = "";
string dcs = "";
string sq = "";
string dt = "";
Boolean trim = false;
int r = 0;
int t = 0;
string sql = "";
string nr = "";
string nc = "";
string nl = Environment.NewLine;
System.Data.Odbc.OdbcConnection ibmc = new System.Data.Odbc.OdbcConnection();
NpgsqlConnection pgc = new NpgsqlConnection();
string msg = "Help:";
msg = msg + nl + "version 0.25";
msg = msg + nl + "-scs source connection string";
msg = msg + nl + "-dcs destination connection string";
msg = msg + nl + "-sq path to source query";
msg = msg + nl + "-dt fully qualified name of destination table";
msg = msg + nl + "-t trim text";
msg = msg + nl + "--help info";
//---------------------------------------parse args into variables-------------------------------------------------
for (int i = 0; i < args.Length; i = i + 1)
{
switch (args[i])
{
//source connection string
case "-scs":
scs = args[i + 1];
break;
//destination connection string
case "-dcs":
dcs = args[i + 1];
break;
//source query path
case "-sq":
try {
sq = System.IO.File.ReadAllText(args[i + 1]);
}
catch (Exception e) {
Console.Write(nl + "error reasing source sql file: " + e.Message);
return;
}
break;
//destination table name
case "-dt":
dt = "INSERT INTO " + args[i + 1] + " VALUES ";
break;
case "-t":
trim = true;
break;
case "--help":
Console.Write(nl);
Console.Write(msg);
return;
case "-help":
Console.Write(nl);
Console.Write(msg);
return;
case "-h":
Console.Write(nl);
Console.Write(msg);
return;
case @"\?":
Console.Write(nl);
Console.Write(msg);
return;
default:
break;
}
}
Console.Write(nl);
Console.Write(scs);
Console.Write(nl);
Console.Write(dcs);
Console.Write(nl);
Console.Write(sq);
Console.Write(nl);
Console.Write(dt);
//return;
//-------------------------------------------establish connections-------------------------------------------------
try {
ibmc.ConnectionString = scs;
}
catch (Exception e) {
Console.Write(nl + "bad source connection string: " + e.Message);
return;
}
try {
pgc.ConnectionString = dcs;
}
catch (Exception e) {
Console.Write(nl + "bad source connection string: " + e.Message);
return;
}
try {
ibmc.Open();
}
catch (Exception e) {
Console.Write(nl + "issue connection to source: " + e.Message);
return;
}
try {
pgc.Open();
}
catch (Exception e) {
ibmc.Close();
Console.Write(nl + "issue connecting to destination: " + e.Message);
}
//----------------------------------------------setup commands---------------------------------------------------
var ibmcmd = new System.Data.Odbc.OdbcCommand(sq, ibmc);
var pgcom = pgc.CreateCommand();
//---------------------------------------------begin transaction---------------------------------------------------------
Console.Write(nl);
Console.Write("etl start:" + DateTime.Now.ToString());
NpgsqlTransaction pgt = pgc.BeginTransaction();
ibmcmd.CommandTimeout = 6000;
System.Data.Odbc.OdbcDataReader ibmdr;
try {
ibmdr = ibmcmd.ExecuteReader();
}
catch (Exception e) {
Console.Write(nl);
Console.Write("error on source sql:");
Console.Write(nl);
Console.Write(e.Message);
ibmc.Close();
pgc.Close();
return;
}
//setup getv object array dimensioned to number of columns for scenario
//getv hold an array of datareader row
var getv = new object[ibmdr.FieldCount];
int cols = ibmdr.FieldCount;
//dtn holds list of data types per column
var dtn = new string[ibmdr.FieldCount];
while (ibmdr.Read())
{
r = r + 1;
t = t + 1;
nr = "";
//populate all the data type names into a string array instead of calling against ibmdr in every iteration
if (t == 1)
{
for (int i = 0; i < cols; i++)
{
dtn[i] = ibmdr.GetDataTypeName(i);
}
}
for (int i = 0; i < cols; i++)
{
//Console.Write(nl);
//Console.Write(DBNull.Value.Equals(ibmdr.GetValue(i)).ToString());
//Console.Write(ibmdr.GetValue(i).ToString());
Boolean dnull = false;
if (dtn[i] == "BIGINT")
{
dnull = DBNull.Value.Equals(ibmdr.GetInt64(i));
}
else
{
dnull = DBNull.Value.Equals(ibmdr.GetValue(i));
}
if (!dnull)
{
switch (dtn[i])
{
case "VARCHAR":
if (trim)
{
nc = "'" + ibmdr.GetValue(i).ToString().Replace("'", "''").TrimEnd() + "'";
}
else
{
nc = "'" + ibmdr.GetValue(i).ToString().Replace("'", "''") + "'";
}
break;
case "CHAR":
if (trim)
{
nc = "'" + ibmdr.GetValue(i).ToString().Replace("'", "''").TrimEnd() + "'";
}
else
{
nc = "'" + ibmdr.GetValue(i).ToString().Replace("'", "''") + "'";
}
break;
case "DATE":
if (ibmdr.GetValue(i).ToString() == "1/1/0001 12:00:00 AM" || ibmdr.GetValue(i).ToString() == "")
{
nc = "NULL";
}
else
{
nc = "'" + ibmdr.GetValue(i).ToString() + "'";
}
break;
case "TIME":
nc = "'" + ibmdr.GetValue(i).ToString() + "'";
break;
case "TIMESTAMP":
nc = "'" + ibmdr.GetDateTime(i).ToString() + "'";
break;
case "BIGINT":
nc = ibmdr.GetInt64(i).ToString();
break;
default:
if (ibmdr.GetValue(i).ToString() != "")
{
nc = ibmdr.GetValue(i).ToString();
}
else
{
nc = "NULL";
}
break;
}
}
else
{
nc = "NULL";
}
if (i != 0)
{
nr = nr + ",";
}
nr = nr + nc;
}
if (sql != "")
{
sql = sql + ",";
}
sql = sql + "(" + nr + ")";
if (r == 250)
{
r = 0;
sql = dt + sql;
pgcom.CommandText = sql;
try {
pgcom.ExecuteNonQuery();
}
catch (Exception e) {
Console.Write(nl);
Console.Write(e.Message);
System.IO.File.WriteAllText(@"C:\Users\ptrowbridge\Downloads\runner_error.sql", sql);
ibmc.Close();
pgt.Rollback();
pgc.Close();
return;
}
sql = "";
Console.Write(nl + t.ToString());
}
}
if (r != 0)
{
sql = dt + sql;
pgcom.CommandText = sql;
try {
pgcom.ExecuteNonQuery();
}
catch (Exception e) {
Console.Write(nl);
Console.Write(e.Message);
System.IO.File.WriteAllText(@"C:\Users\ptrowbridge\Downloads\runner_error.sql", sql);
//ibmc.Close();
pgt.Rollback();
pgc.Close();
return;
}
sql = "";
Console.Write(nl + t.ToString());
}
pgt.Commit();
ibmc.Close();
pgc.Close();
Console.Write(nl);
Console.Write("etl end:" + DateTime.Now.ToString());
}
private void btnok_Click(object sender, EventArgs e)
{
if (initialset == true)
{
string newpass = txtbxpassword.Text;
newpass = EncodePass(newpass, newpass.Length);
//MessageBox.Show(newpass);
AuthCom = new System.Data.Odbc.OdbcCommand("UPDATE mis_pass SET passwd='" + newpass + "' WHERE username='******'", AuthCon);
AuthCom.ExecuteNonQuery();
MessageBox.Show(" New password updated. please restart application");
validity.cancelled = true;
AuthCon.Close();
this.Close();
}
else if (initialset == false)
{
if (txtbxusername.Text == "")
{
MessageBox.Show(" Please Specify Username ");
}
else if (AuthCon.State == ConnectionState.Open)
{
string passwd = "";
AuthCom = new System.Data.Odbc.OdbcCommand("SELECT passwd FROM mis_pass WHERE username='******'", AuthCon);
AuthRead = AuthCom.ExecuteReader();
while (AuthRead.Read())
{
passwd = (String)AuthRead[0];
}
//MessageBox.Show(passwd);
if (passwd.CompareTo("") == 0)
{
MessageBox.Show(" Username invalid ");
}
else if (passwd.CompareTo("") != 0)
{
passwd = DecodePass(passwd, passwd.Length);
//MessageBox.Show(passwd);
if (passwd.CompareTo(txtbxpassword.Text) == 0)
{
validity.authenticated = true;
if (txtbxusername.Text == "admin")
{
validity.uservalue = true;
}
else
{
validity.uservalue = false;
}
}
else
{
validity.authenticated = false;
}
AuthCon.Close();
this.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
}
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 { ;}
}
}
}
public void loadLCSData()
{
string sql = "select t1.devicename as devicename,tunnel,place,div from tblLCSConfig t2 inner join tbldeviceconfig t1 on t1.devicename=t2.devicename where location='T' and lineid='N6' ";
System.Data.Odbc.OdbcConnection cn = new System.Data.Odbc.OdbcConnection(DbCmdServer.getDbConnectStr());
System.Data.Odbc.OdbcCommand cmd = new System.Data.Odbc.OdbcCommand(sql);
System.Data.Odbc.OdbcDataReader rd;
try
{
cn.Open();
cmd.Connection = cn;
rd = cmd.ExecuteReader();
while (rd.Read())
{
LCSDeviceData devcdata;
string devicename=rd[0].ToString();
int tunnid=System.Convert.ToInt32(rd[1]);
int place=System.Convert.ToInt32(rd[2]);
int div=System.Convert.ToInt32(rd[3]);
devcdata = new LCSDeviceData(devicename, tunnid, place, div);
this.hsLCSDeviceData.Add(devicename, devcdata);
ConsoleServer.WriteLine("loadoing " + devicename);
}
rd.Close();
}
catch (Exception ex)
{
ConsoleServer.WriteLine(ex.Message + "," + ex.StackTrace);
}
finally
{
cn.Close();
}
}
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
}
public override System.Collections.ArrayList getTableList()
{
System.Collections.ArrayList ar = new System.Collections.ArrayList();
System.Data.Odbc.OdbcConnection rawconn = this._dbObj.GetDatabaseConnector().GetRawConnectionObject();
System.Data.Odbc.OdbcCommand ocomm = new System.Data.Odbc.OdbcCommand();
string sqlStr = "select name from sysobjects where type='U';";
//sqlStr = this._dbObj.GetDatabaseConnector().DoTopLevelSqlTranslations(ref sqlStr);
ocomm.CommandText = sqlStr;
ocomm.Connection = rawconn;
try
{
System.Data.Odbc.OdbcDataReader odr = ocomm.ExecuteReader();
while (odr.Read())
{
ar.Add(Convert.ToString(odr[0]));
}
}
catch (Exception e)
{
System.Diagnostics.Debug.Print(e.Message);
return null;
}
return ar;
}
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
}
void SetTemVDData(string devname, int dir, int milek, int milem, System.Data.DataSet ds)
{
System.Text.StringBuilder sql = new StringBuilder();
System.IO.MemoryStream ms = new System.IO.MemoryStream();
ms.WriteByte(0x21);
ms.WriteByte((byte)dir);
ms.WriteByte((byte)(milek/256));
ms.WriteByte((byte)(milek % 256));
ms.WriteByte((byte)((milem ) / 256));
ms.WriteByte((byte)((milem ) % 256));
ms.WriteByte((byte)ds.Tables[1].Rows.Count); //lanecnt
ms.WriteByte(0); //odd
System.DateTime dt= DateTime.Now;
dt=dt.AddSeconds(-dt.Second).AddMinutes(-5);
dt=dt.AddMinutes(-(dt.Minute % 5));
//sql.Append("select ");
//for(int i=0;i<ds.Tables[1].Rows.Count;i++)
//{
//}
// string sql = "select small_car_volume_lane1,big_car_volume,connect_car_volume,small_car_speed,big_car_speed,connect_car_speed,average_occupancy from tblvddata5min where devicename='{0}' and timestamp='{1}' ";
System.Data.Odbc.OdbcConnection cn = new System.Data.Odbc.OdbcConnection(RemoteInterface.DbCmdServer.getDbConnectStr());
System.Data.Odbc.OdbcCommand cmd=new System.Data.Odbc.OdbcCommand();
cmd.Connection = cn;
try
{
cn.Open();
for (int i = 0; i < (byte)ds.Tables[1].Rows.Count; i++)
{
sql = new StringBuilder("select ");
sql.Append("small_car_volume_lane").Append(i + 1).Append(",");
sql.Append("big_car_volume_lane").Append(i + 1).Append(",");
sql.Append("connect_car_volume_lane").Append(i + 1).Append(",");
sql.Append("small_car_speed_lane").Append(i + 1).Append(",");
sql.Append("big_car_speed_lane").Append(i + 1).Append(",");
sql.Append("connect_car_speed_lane").Append(i + 1).Append(",");
sql.Append("average_occupancy_lane").Append(i + 1).Append(",");
sql.Remove(sql.ToString().Length - 1, 1).Append(" from tblvddata5min where devicename='{0}' and timestamp='{1}'");
cmd.CommandText = string.Format(sql.ToString(), devname, RemoteInterface.DbCmdServer.getTimeStampString(dt));
System.Data.Odbc.OdbcDataReader rd = cmd.ExecuteReader();
if (!rd.Read())
{
rd.Close();
continue;
}
// System.Data.DataRow r = ds.Tables[1].Rows[i];
ms.WriteByte((byte)i); //loopid
ms.WriteByte(0); //odd
int car_vol = System.Convert.ToInt32(rd[0]);
ms.WriteByte((byte)(car_vol / 256));
ms.WriteByte((byte)(car_vol % 256));
int big_vol = System.Convert.ToInt32(rd[1]);
ms.WriteByte((byte)(big_vol / 256));
ms.WriteByte((byte)(big_vol % 256));
int cn_vol = System.Convert.ToInt32(rd[2]);
ms.WriteByte((byte)(cn_vol / 256));
ms.WriteByte((byte)(cn_vol % 256));
int carspd = System.Convert.ToByte(rd[3]);
ms.WriteByte((byte)carspd);
carspd = System.Convert.ToByte(rd[4]);
ms.WriteByte((byte)carspd);
carspd = System.Convert.ToByte(rd[5]);
ms.WriteByte((byte)carspd);
ms.WriteByte(System.Convert.ToByte(rd[6]));
rd.Close();
}
}
catch (Exception ex)
{
ConsoleServer.WriteLine(ex.Message + "," + ex.StackTrace);
}
finally
{
cn.Close();
}
Comm.SendPackage pkg=new Comm.SendPackage(Comm.CmdType.CmdSet,Comm.CmdClass.A,0xffff,ms.ToArray());
Program.mfcc_tem.manager[TEM_DEVNAME].Send(pkg);
}
private List<PerformanceCubeResult> GetPerformanceCubeResults(GetPerformanceResultsFilter filter)
{
List<PerformanceCubeResult> cubeResults = new List<PerformanceCubeResult>();
System.Data.Odbc.OdbcConnection conn = createSQLConnection();
try
{
//
string mySelectQuery;
switch (filter)
{
case GetPerformanceResultsFilter.MonoTouch:
mySelectQuery = "SELECT * FROM `PerformanceGLCubeResults` WHERE `isMonoTouch` = 1 ORDER BY `DBDeviceId` ASC";
break;
case GetPerformanceResultsFilter.ObjectiveC:
mySelectQuery = "SELECT * FROM `PerformanceGLCubeResults` WHERE `isMonoTouch` = 0 ORDER BY `DBDeviceId` ASC";
break;
default:
mySelectQuery = "SELECT * FROM `PerformanceGLCubeResults` ORDER BY `PerformanceGLCubeResults`.`DBDeviceId` ASC";
break;
}
System.Data.Odbc.OdbcCommand myCommand = new System.Data.Odbc.OdbcCommand(mySelectQuery, conn);
conn.Open();
System.Data.Odbc.OdbcDataReader myReader = myCommand.ExecuteReader();
try
{
while (myReader.Read())
{
cubeResults.Add(new PerformanceCubeResult()
{
DatabaseId = (int)myReader["id"],
DeviceDatabaseId = (int)myReader["DBDeviceId"],
NumberOfTriangles = (int)myReader["NumberTriangles"],
FramesPerSecond = (double)myReader["FramesPerSecond"],
IsMonoTouch = Convert.ToBoolean(myReader["isMonoTouch"])
});
}
}
finally
{
myReader.Close();
}
}
finally
{
conn.Close();
}
return cubeResults;
}