internal List<Right> GetBexisRights(string dataBase, Dictionary<int, int> dataSetsMapping)
{
List<Right> bexisRights = new List<Right>();
string datasetQuery = "";
foreach (var dataSetMapping in dataSetsMapping)
{
datasetQuery += "DATASETID = "+ dataSetMapping.Key;
if (dataSetsMapping.Last().Key != dataSetMapping.Key)
datasetQuery += " or ";
}
if (dataSetsMapping.Any())
{
datasetQuery = "where " + datasetQuery + "";
}
// DB query
string mySelectQuery = "SELECT ROLENAME, DATASETID, FOREDIT, APPLICATIONNAME FROM \"PROVIDER\".\"RIGHTS\" "+ datasetQuery;
DB2Connection connect = new DB2Connection(dataBase);
DB2Command myCommand = new DB2Command(mySelectQuery, connect);
connect.Open();
DB2DataReader myReader = myCommand.ExecuteReader();
while (myReader.Read())
{
bexisRights.Add(new Right()
{
RoleName = myReader.GetString(0),
DataSetId = (int)(myReader.GetValue(1)),
CanEdit = myReader.GetString(2)=="N"?false:true
});
}
myReader.Close();
connect.Close();
return bexisRights;
}
public List<MTableDesc> GetTableList(IDbConnection conn, string creater)
{
string strSql = @"select NAME, CREATOR,CTIME,REMARKS from sysibm.systables where type='T' ";
if (!string.IsNullOrEmpty(creater))
{
strSql += string.Format("and creator='{0}'", creater);
}
DB2Command cmd = new DB2Command(strSql, (DB2Connection)conn);
List<MTableDesc> ret = new List<MTableDesc>();
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
MTableDesc model = new MTableDesc();
model.TableName = reader["NAME"] == DBNull.Value ? string.Empty : reader["NAME"].ToString();
model.Creator = reader["CREATOR"] == DBNull.Value ? string.Empty : reader["CREATOR"].ToString();
model.CreateDateTime = reader["CTIME"] == DBNull.Value ? DateTime.Parse("1990-01-01") : Convert.ToDateTime(reader["CTIME"].ToString());
model.Remarks = reader["REMARKS"] == DBNull.Value ? string.Empty : reader["REMARKS"].ToString();
ret.Add(model);
}
}
return ret;
}
public List<MColumn> GetColumnList(IDbConnection conn, string tableName)
{
string sql = string.Format(@"SELECT NAME,TBNAME,TBCREATOR,REMARKS,COLTYPE,LENGTH, NULLS,DEFAULT,KEYSEQ
FROM sysibm.syscolumns where tbname ='{0}'", tableName);
DB2Command cmd = new DB2Command(sql, (DB2Connection)conn);
List<MColumn> ret = new List<MColumn>();
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
MColumn model = new MColumn();
model.ColumnName = DBUtil.GetDBValueStr(reader, "NAME");
model.Creator = DBUtil.GetDBValueStr(reader, "TBCREATOR");
model.Remarks = DBUtil.GetDBValueStr(reader, "REMARKS");
model.ColType = DBUtil.GetDBValueStr(reader, "COLTYPE");
model.Length = DBUtil.GetDBValueInt(reader, "LENGTH");
model.IsNullable = DBUtil.GetDBValueBool(reader, "NULLS");
model.DefaultValue = DBUtil.GetDBValueStr(reader, "DEFAULT");
model.KeySeq = DBUtil.GetDBValueInt(reader, "KEYSEQ");
ret.Add(model);
}
}
return ret;
}
//View Data
public DB2DataReader ExecDataView(String sql)
{
if (this.ConnectDatabase() == true)
{
Cmd = new DB2Command(sql, Conn);
Resultset = Cmd.ExecuteReader();
}
return Resultset;
}
public void IBMBuldCopy()
{
string sql_bruv_1 = "Server=10.24.1.202:446;Database=BRUVDB4V;UID=ATWO;PWD=24rete31;Max Pool Size=100;Min Pool Size=10;";
string sql_frymek = "Server=10.27.5.197:50000;Database=BRUV;UID=db2admin;PWD=db2pass1234!@#$;Max Pool Size=100;Min Pool Size=10;";
try
{
using (DB2Connection myConn = new DB2Connection(sql_bruv_1))
{
using (DB2Connection conn = new DB2Connection(sql_frymek))
{
myConn.Open();
conn.Open();
log.InfoFormat("{0}: Time elapsed: {1}", Table, DateTime.Now);
string myInsertQuery = String.Format("SELECT * FROM RREV.{0}", Table);
using (DB2Command myDB2Command = new DB2Command(myInsertQuery, myConn))
{
using (DB2DataReader reader = myDB2Command.ExecuteReader())
{
using (DB2BulkCopy salesCopy = new DB2BulkCopy(conn))
{
salesCopy.DestinationTableName = String.Format("ATWO.{0}", Table);
salesCopy.WriteToServer(reader);
var errors = salesCopy.Errors;
if (errors.Count > 0)
{
log.ErrorFormat("table:{0}, errors:{1}", Table, errors.Count);
foreach (var er in errors)
{
log.ErrorFormat("table:{0}, msg:{1}", Table, er.ToString());
}
}
salesCopy.Close();
myConn.Close();
conn.Close();
}
}
}
}
}
log.InfoFormat("{0}: Time elapsed: {1}", Table, DateTime.Now);
}
catch (Exception ex)
{
log.Error(ex.ToString());
Console.WriteLine(String.Format("error: {0} with exception: {1}", Table, ex.Message));
}
}
// query bexis1 user from provider.users and generate a random password
public List<UserProperties> GetFromBExIS(string DataBase)
{
List<UserProperties> transferUsers = new List<UserProperties>();
// DB query
string mySelectQuery = "select username, email, firstname, lastname, " +
"organization, projectname, projectleader, " +
"url, phone, mobile, fax, original, street, zipcode, city";
mySelectQuery += " from provider.users;";
DB2Connection connect = new DB2Connection(DataBase);
DB2Command myCommand = new DB2Command(mySelectQuery, connect);
connect.Open();
DB2DataReader myReader = myCommand.ExecuteReader();
// random password
Random gen = new Random();
while (myReader.Read())
{
UserProperties transferUser = new UserProperties();
// bexis1 DB user data
transferUser.username = myReader.GetValue(0).ToString();
transferUser.email = myReader.GetValue(1).ToString();
transferUser.firstname = myReader.GetValue(2).ToString();
transferUser.lastname = myReader.GetValue(3).ToString();
transferUser.organization = myReader.GetValue(4).ToString();
transferUser.projectname = myReader.GetValue(5).ToString();
transferUser.projectleader = myReader.GetValue(6).ToString();
transferUser.url = myReader.GetValue(7).ToString();
transferUser.phone = myReader.GetValue(8).ToString();
transferUser.mobile = myReader.GetValue(9).ToString();
transferUser.fax = myReader.GetValue(10).ToString();
transferUser.original = myReader.GetValue(11).ToString();
transferUser.street = myReader.GetValue(12).ToString();
transferUser.zipcode = myReader.GetValue(13).ToString();
transferUser.city = myReader.GetValue(14).ToString();
// bexis2 required security data
transferUser.password = randomPassword(ref gen); // random password
transferUser.securityQuestionId = 1;
transferUser.securityAnswer = "1";
transferUser.authenticatorId = 1;
// add to list; username required
if (transferUser.username != "")
transferUsers.Add(transferUser);
}
myReader.Close();
connect.Close();
return transferUsers;
}
internal List<string> GetBexisUsersInRole(string dataBase, string roleName)
{
List<string> bexisUsersInRole = new List<string>();
// DB query
string mySelectQuery = "SELECT USERNAME FROM \"PROVIDER\".\"USERSINROLES\" where ROLENAME='" + roleName + "'";
DB2Connection connect = new DB2Connection(dataBase);
DB2Command myCommand = new DB2Command(mySelectQuery, connect);
connect.Open();
DB2DataReader myReader = myCommand.ExecuteReader();
while (myReader.Read())
{
bexisUsersInRole.Add(myReader.GetValue(0).ToString());
}
myReader.Close();
connect.Close();
return bexisUsersInRole;
}
internal List<string> GetBexisRoles(string DataBase)
{
List<string> bexisRoles = new List<string>();
// DB query
string mySelectQuery = "SELECT ROLENAME, APPLICATIONNAME FROM \"PROVIDER\".\"ROLES\"";
DB2Connection connect = new DB2Connection(DataBase);
DB2Command myCommand = new DB2Command(mySelectQuery, connect);
connect.Open();
DB2DataReader myReader = myCommand.ExecuteReader();
while (myReader.Read())
{
bexisRoles.Add(myReader.GetValue(0).ToString());
}
myReader.Close();
connect.Close();
return bexisRoles;
}
/// <summary>
/// This is a sample specific finder method for Entity1.
/// If you want to delete or rename the method think about changing the xml in the BDC model file as well.
/// </summary>
/// <param name="id"></param>
/// <returns>Entity1</returns>
public static GreetingEvent ReadItem(string id)
{
var greetingEvent = new GreetingEvent();
var db2Connection = GetSqlConnection();
db2Connection.Open();
var sqlCommand = new DB2Command
{
CommandText = string.Format("SELECT * FROM GREETING WHERE deptno = '{0}'", id),
Connection = db2Connection
};
var sqlDataReader = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection);
if (sqlDataReader.Read())
{
greetingEvent.DeptNo = sqlDataReader[0].ToString();
greetingEvent.Greeting = sqlDataReader[1].ToString();
}
db2Connection.Dispose();
return greetingEvent;
}
public XmlDocument getMetadataXml(string dataSetID, string DataBase)
{
XmlDocument doc = new XmlDocument();
string mySelectQuery = "select datasetid, metadata";
mySelectQuery += " from explorer.datasets where datasetid = " + dataSetID + ";";
DB2Connection connect = new DB2Connection(DataBase);
DB2Command myCommand = new DB2Command(mySelectQuery, connect);
connect.Open();
DB2DataReader myReader = myCommand.ExecuteReader();
while (myReader.Read())
{
doc.LoadXml(myReader.GetString(1));
}
myReader.Close();
connect.Close();
return doc;
}
/// <summary>
/// This is a sample finder method for Entity1.
/// If you want to delete or rename the method think about changing the xml in the BDC model file as well.
/// </summary>
/// <returns>IEnumerable of Entities</returns>
public static IEnumerable<GreetingEvent> ReadList()
{
var db2Connection = GetSqlConnection();
var trainingEvents = new List<GreetingEvent>();
db2Connection.Open();
var db2Command = new DB2Command
{
Connection = db2Connection,
CommandText = "SELECT * FROM GREETING",
};
var sqlDataReader = db2Command.ExecuteReader(CommandBehavior.CloseConnection);
while (sqlDataReader.Read())
{
var trainingEvent = new GreetingEvent
{
DeptNo = sqlDataReader[0].ToString(),
Greeting = sqlDataReader[1].ToString()
};
trainingEvents.Add(trainingEvent);
}
db2Connection.Dispose();
return trainingEvents;
}
public void queryAuthorAndVariables(ref User user, ref string variableNames, string dataSetID, string DataBase)
{
string mySelectQuery = "select X.* from explorer.datasets, XMLTABLE ('$METADATA/*:metaProfile' Columns " +
"Author varchar(256) Path '*:general/*:metadataCreator'," +
"VarNames varchar(1028) Path 'string-join(*:data/*:dataStructure/*:variables/*:variable/*:name,\",\")'" +
") as X where datasetid = " + dataSetID + ";";
DB2Connection connect = new DB2Connection(DataBase);
DB2Command myCommand = new DB2Command(mySelectQuery, connect);
connect.Open();
DB2DataReader myReader = myCommand.ExecuteReader();
string author = "";
while (myReader.Read())
{
author = myReader.GetString(0);
variableNames = myReader.GetString(1);
}
myReader.Close();
connect.Close();
SubjectManager subjectManager = new SubjectManager();
user = subjectManager.UsersRepo.Get(u => author.Equals(u.FullName)).FirstOrDefault();
}
public BexisPublication[] getPublicationsMetadataXml(string DataBase)
{
var bexisPublications = new List<BexisPublication>();
var docs = new List<XmlDocument>();
var doc = new XmlDocument();
string mySelectQuery = "SELECT ID,DATA FROM \"EXPLORER\".\"PUBLICATIONLIST\"";
DB2Connection connect = new DB2Connection(DataBase);
DB2Command myCommand = new DB2Command(mySelectQuery, connect);
connect.Open();
DB2DataReader myReader = myCommand.ExecuteReader();
while (myReader.Read())
{
doc.LoadXml(myReader.GetString(1));
var bp = new BexisPublication();
bp.Id = (int)(myReader.GetValue(0));
bp.MetaDataXml = doc;
var fileCommand = new DB2Command("SELECT FILENAME, MIMETYPE ,FILE FROM \"EXPLORER\".\"PUBLICATIONFILES\" where PUBID=" + bp.Id, connect);
var fileReader = fileCommand.ExecuteReader();
string dataPath = AppConfiguration.DataPath;
string storepath = Path.Combine(dataPath, "Temp", "Administrator");
// if folder not exist
if (!Directory.Exists(storepath))
Directory.CreateDirectory(storepath);
var index = 1;
if (fileReader.Read())
{
PublicationContentDescriptor pubContent = new PublicationContentDescriptor()
{
OrderNo = index++,
Name = fileReader.GetString(0),
MimeType = fileReader.GetString(1)
};
var filePath = Path.Combine(storepath, Guid.NewGuid().ToString() + Path.GetExtension(pubContent.Name));
File.WriteAllBytes(filePath, ((Byte[])fileReader.GetValue(2)));
pubContent.URI = filePath;
bp.PublicationContentDescriptors.Add(pubContent);
}
fileReader.Close();
bexisPublications.Add(bp);
}
myReader.Close();
connect.Close();
return bexisPublications.ToArray();
}
public DataTable Get_RPT_tblDeviceStatus(string sDevList)
{
try
{
string selectCmd = "";
selectCmd += " SELECT l.Devicename as Devicename,n.LineName as LineName, ";
selectCmd += " (case when l.connectstatus='Y' THEN '連線' when l.connectstatus='N' then '離線' ELSE '-' END) as connectstatus, ";
selectCmd += " (case when connectstatus='Y' then db2inst1.OP_MODE(l.op_mode) when connectstatus='N' THEN '-' ELSE '-' end ) as op_mode, ";
selectCmd += " (case when connectstatus='Y' then db2inst1.OP_STATUS(l.op_status) when connectstatus='N' THEN '-' ELSE '-' end) AS op_status, ";
selectCmd += " l.display as display ";
selectCmd += " FROM " + "db2inst1" + "." + sTableName_DeviceStatus + " l ";
selectCmd += " LEFT JOIN " + "db2inst1" + "." + sTableName_DeviceConfig + " d ON l.Devicename = d.Devicename ";
selectCmd += " LEFT JOIN " + "db2inst1" + "." + sTableName_GroupLine + " n ON d.lineid =n.lineid ";
selectCmd += " where d.Device_Type='CMS' AND l.Devicename IN " + sDevList + " ";
selectCmd += " order by l.Devicename,l.TimeStamp ";
InitDB();
//da = new DB2DataAdapter(selectCmd, conn);
//DataTable DT = new DataTable();
//da.Fill(DT);
//新的做法
DB2Command da = new DB2Command(selectCmd, conn);
DataTable DT = new DataTable();
DT.Load(da.ExecuteReader());
da.Dispose();
conn.Close();
return DT;
}
catch (Exception ex)
{
sErrMsg = ex.Message;
CloseDB();
return null;
}
}
public DataTable Get_RPT_tblDeviceStatusLog(string sDevList, DateTime sStartDate, DateTime sEndDate)
{
try
{
string selectCmd = "";
selectCmd += "Select l.Devicename as Devicename, ";
selectCmd += " n.LineName as LineName, ";
selectCmd += " (case when d.Location = 'R' and Location_R = 'I' then concat(p.VariableValue,'入口') ";
selectCmd += " when d.Location = 'R' and Location_R = 'O' then concat(p.VariableValue,'出口') ";
selectCmd += " else p.VariableValue end) as Location, ";
selectCmd += " p2.VariableValue as Direction, ";
selectCmd += " cast(round(cast(d.Mile_M as double)/1000,2) as decimal(8,2)) as Mileage, ";
selectCmd += " to_char(l.TimeStamp,'YYYY-MM-DD HH24:MI:SS') as TimeStamp, l.display as display,l.DEVICE_DISPLAY as DEVICE_DISPLAY,'不符' as comparison ";
selectCmd += " From " + "db2inst1" + "." + sTableName_tblDeviceComparisonLog + " l LEFT JOIN " + "db2inst1" + "." + sTableName_DeviceConfig + " d LEFT JOIN " + "db2inst1" + "." + sTableName_GroupLine + " n ON n.lineid = d.lineid ";
selectCmd += " LEFT JOIN (select VariableName, VariableValue from " + "db2inst1" + "." + sTableName_Parameter + " where GroupName = 'Location') p ON p.VariableName = d.Location ";
selectCmd += " LEFT JOIN (select VariableName, VariableValue from " + "db2inst1" + "." + sTableName_Parameter + " where GroupName = 'DeviceDirection') p2 ON p2.VariableName = d.Direction ";
selectCmd += " ON d.Devicename = l.Devicename ";
selectCmd += " Where d.Device_Type in " + sDevList + " ";
selectCmd += " And l.Timestamp between timestamp('" + sStartDate.ToString("yyyy-MM-dd HH:mm:ss") + "') ";
selectCmd += " and timestamp('" + sEndDate.ToString("yyyy-MM-dd HH:mm:ss") + "') ";
//selectCmd += " AND comparison in('Y','N') ";
selectCmd += " Order By l.Devicename,l.Timestamp ";
InitDB();
//da = new DB2DataAdapter(selectCmd, conn);
//DataTable DT = new DataTable();
//da.Fill(DT);
//新的做法
DB2Command da = new DB2Command(selectCmd, conn);
DataTable DT = new DataTable();
DT.Load(da.ExecuteReader());
da.Dispose();
conn.Close();
return DT;
}
catch (Exception ex)
{
sErrMsg = ex.Message;
CloseDB();
return null;
}
}
private DataTable GetSchema()
{
dataAdapter.SelectCommand.Connection.Open();
DB2Command cmd = new DB2Command(dataAdapter.SelectCommand.CommandText, dataAdapter.SelectCommand.Connection);
DB2DataReader fake = cmd.ExecuteReader(CommandBehavior.KeyInfo);
DataTable dt = fake.GetSchemaTable();
fake.Close();
dataAdapter.SelectCommand.Connection.Close();
return dt;
}
private List<Observation> queryObservation(string dataSetID, string DataBase, string insertDateStrg)
{
List<Observation> observations = new List<Observation>();
string mySelectQuery = "select obsid, data, deleted, newest";
mySelectQuery += " from explorer.observation";
mySelectQuery += " where datasetid = " + dataSetID;
mySelectQuery += " and insertdate = '" + insertDateStrg + "';";
DB2Connection connect = new DB2Connection(DataBase);
DB2Command myCommand = new DB2Command(mySelectQuery, connect);
connect.Open();
DB2DataReader myReader = myCommand.ExecuteReader();
while (myReader.Read())
{
Observation observation = new Observation();
observation.obsid = myReader.GetInt64(0);
observation.data = new XmlDocument();
observation.data.LoadXml(myReader.GetString(1));
observation.deleted = myReader.GetChar(2);
observation.newest = myReader.GetChar(3);
observations.Add(observation);
}
myReader.Close();
connect.Close();
return observations;
}
private List<DB2TimeStamp> queryDistInsertDates(string dataSetID, string DataBase)
{
List<DB2TimeStamp> distInsertDates = new List<DB2TimeStamp>();
string mySelectQuery = "select distinct insertdate";
mySelectQuery += " from explorer.observation where datasetid = " + dataSetID + " order by insertdate asc;";
DB2Connection connect = new DB2Connection(DataBase);
DB2Command myCommand = new DB2Command(mySelectQuery, connect);
connect.Open();
DB2DataReader myReader = myCommand.ExecuteReader();
while (myReader.Read())
{
distInsertDates.Add(myReader.GetDB2TimeStamp(0));
}
myReader.Close();
connect.Close();
return distInsertDates;
}
public DataTable Get_RPT_VDSPOTSPEED(string sDevList, DateTime sStartDate, DateTime sEndDate)
{
try
{
string selectCmd = "";
int ilane = 0;
string sAvgO = "";
string subSql = "";
string lane_volume_sql = "";
selectCmd += " select (case when x.priority = 3 then '' when x.priority = 2 then TO_char(x.timestamp,'HH24:MI:SS') else TO_char(x.timestamp,'YYYY/MM/DD') end) AS timestamp, ";
selectCmd += " x.DeviceName,x.class,x.total,DB2INST1.ZERO2DASH_CHAR(x.connect_lane1),DB2INST1.ZERO2DASH_CHAR(x.big_lane1),x.small_lane1,x.connect_lane2,x.big_lane2, ";
selectCmd += " x.small_lane2,x.connect_lane3,x.big_lane3,x.small_lane3,x.connect_lane4,x.big_lane4,x.small_lane4, ";
selectCmd += " x.connect_lane5,x.big_lane5,x.small_lane5,x.connect_lane6,x.big_lane6,x.small_lane6 ";
selectCmd += " from ";
selectCmd += " ( ";
selectCmd += "Select timestamp, ";
selectCmd += " s.DeviceName, ";
selectCmd += " '流量' as class, ";
selectCmd += " SUM(s.car_volume) as total, ";
selectCmd += " ";
selectCmd += " (case when c.lane_count >= 1 then char(SUM(case when s.car_class <> 3 then 0 when s.car_class = 3 and s.lane_id <> 1 then 0 else s.car_volume end)) else '-' end) as connect_lane1, ";
selectCmd += " (case when c.lane_count >= 1 then char(SUM(case when s.car_class <> 2 then 0 when s.car_class = 2 and s.lane_id <> 1 then 0 else s.car_volume end)) else '-' end) as big_lane1, ";
selectCmd += " (case when c.lane_count >= 1 then char(SUM(case when s.car_class <> 1 then 0 when s.car_class = 1 and s.lane_id <> 1 then 0 else s.car_volume end)) else '-' end) as small_lane1, ";
selectCmd += " ";
selectCmd += " (case when c.lane_count >= 2 then char(SUM(case when s.car_class <> 3 then 0 when s.car_class = 3 and s.lane_id <> 2 then 0 else s.car_volume end)) else '-' end) as connect_lane2, ";
selectCmd += " (case when c.lane_count >= 2 then char(SUM(case when s.car_class <> 2 then 0 when s.car_class = 2 and s.lane_id <> 2 then 0 else s.car_volume end)) else '-' end) as big_lane2, ";
selectCmd += " (case when c.lane_count >= 2 then char(SUM(case when s.car_class <> 1 then 0 when s.car_class = 1 and s.lane_id <> 2 then 0 else s.car_volume end)) else '-' end) as small_lane2, ";
selectCmd += " ";
selectCmd += " (case when c.lane_count >= 3 then char(SUM(case when s.car_class <> 3 then 0 when s.car_class = 3 and s.lane_id <> 3 then 0 else s.car_volume end)) else '-' end) as connect_lane3, ";
selectCmd += " (case when c.lane_count >= 3 then char(SUM(case when s.car_class <> 2 then 0 when s.car_class = 2 and s.lane_id <> 3 then 0 else s.car_volume end)) else '-' end) as big_lane3, ";
selectCmd += " (case when c.lane_count >= 3 then char(SUM(case when s.car_class <> 1 then 0 when s.car_class = 1 and s.lane_id <> 3 then 0 else s.car_volume end)) else '-' end) as small_lane3, ";
selectCmd += " ";
selectCmd += " (case when c.lane_count >= 4 then char(SUM(case when s.car_class <> 3 then 0 when s.car_class = 3 and s.lane_id <> 4 then 0 else s.car_volume end)) else '-' end) as connect_lane4, ";
selectCmd += " (case when c.lane_count >= 4 then char(SUM(case when s.car_class <> 2 then 0 when s.car_class = 2 and s.lane_id <> 4 then 0 else s.car_volume end)) else '-' end) as big_lane4, ";
selectCmd += " (case when c.lane_count >= 4 then char(SUM(case when s.car_class <> 1 then 0 when s.car_class = 1 and s.lane_id <> 4 then 0 else s.car_volume end)) else '-' end) as small_lane4, ";
selectCmd += " ";
selectCmd += " (case when c.lane_count >= 5 then char(SUM(case when s.car_class <> 3 then 0 when s.car_class = 3 and s.lane_id <> 5 then 0 else s.car_volume end)) else '-' end) as connect_lane5, ";
selectCmd += " (case when c.lane_count >= 5 then char(SUM(case when s.car_class <> 2 then 0 when s.car_class = 2 and s.lane_id <> 5 then 0 else s.car_volume end)) else '-' end) as big_lane5, ";
selectCmd += " (case when c.lane_count >= 5 then char(SUM(case when s.car_class <> 1 then 0 when s.car_class = 1 and s.lane_id <> 5 then 0 else s.car_volume end)) else '-' end) as small_lane5, ";
selectCmd += " ";
selectCmd += " (case when c.lane_count >= 6 then char(SUM(case when s.car_class <> 3 then 0 when s.car_class = 3 and s.lane_id <> 6 then 0 else s.car_volume end)) else '-' end) as connect_lane6, ";
selectCmd += " (case when c.lane_count >= 6 then char(SUM(case when s.car_class <> 2 then 0 when s.car_class = 2 and s.lane_id <> 6 then 0 else s.car_volume end)) else '-' end) as big_lane6, ";
selectCmd += " (case when c.lane_count >= 6 then char(SUM(case when s.car_class <> 1 then 0 when s.car_class = 1 and s.lane_id <> 6 then 0 else s.car_volume end)) else '-' end) as small_lane6, ";
selectCmd += " ";
selectCmd += " 1 as priority ";
selectCmd += " ";
selectCmd += " From " + "db2inst1" + "." + sTableName_SpotSpeed + " s, ";
selectCmd += " " + "db2inst1" + "." + sTableName_VdConfig + " c ";
selectCmd += " Where s.timestamp between timestamp('" + sStartDate.ToString("yyyy-MM-dd HH:mm:ss") + "') ";
selectCmd += " and timestamp('" + sEndDate.ToString("yyyy-MM-dd HH:mm:ss") + "') ";
selectCmd += " And s.devicename = c.devicename ";
selectCmd += " And s.devicename in " + sDevList + " ";
selectCmd += " Group by s.DeviceName, c.lane_count,timestamp ";
selectCmd += " ";
selectCmd += "UNION ";
selectCmd += " ";
selectCmd += "Select timestamp, ";
selectCmd += " s.DeviceName, ";
selectCmd += " '速度' as class, ";
selectCmd += " (case when SUM(s.car_volume) = 0 then 0 else SUM(s.car_speed) / SUM(s.car_volume) end) as total, ";
selectCmd += " ";
ilane = 1;
selectCmd += " (case when c.lane_count >= " + ilane + " then char(case when SUM(case when s.car_class <> 3 then 0 when s.car_class = 3 and s.lane_id <> " + ilane + " then 0 else s.car_volume end) = 0 then 0 else SUM((case when s.car_class <> 3 then 0 when s.car_class = 3 and s.lane_id <> " + ilane + " then 0 else s.car_speed end) * (case when s.car_class <> 3 then 0 else s.car_volume end)) / SUM(case when s.car_class <> 3 then 0 when s.car_class = 3 and s.lane_id <> " + ilane + " then 0 else s.car_volume end) end) else '-' end) as connect_lane1, ";
selectCmd += " (case when c.lane_count >= " + ilane + " then char(case when SUM(case when s.car_class <> 2 then 0 when s.car_class = 2 and s.lane_id <> " + ilane + " then 0 else s.car_volume end) = 0 then 0 else SUM((case when s.car_class <> 2 then 0 when s.car_class = 2 and s.lane_id <> " + ilane + " then 0 else s.car_speed end) * (case when s.car_class <> 2 then 0 else s.car_volume end)) / SUM(case when s.car_class <> 2 then 0 when s.car_class = 2 and s.lane_id <> " + ilane + " then 0 else s.car_volume end) end) else '-' end) as big_lane1, ";
selectCmd += " (case when c.lane_count >= " + ilane + " then char(case when SUM(case when s.car_class <> 1 then 0 when s.car_class = 1 and s.lane_id <> " + ilane + " then 0 else s.car_volume end) = 0 then 0 else SUM((case when s.car_class <> 1 then 0 when s.car_class = 1 and s.lane_id <> " + ilane + " then 0 else s.car_speed end) * (case when s.car_class <> 1 then 0 else s.car_volume end)) / SUM(case when s.car_class <> 1 then 0 when s.car_class = 1 and s.lane_id <> " + ilane + " then 0 else s.car_volume end) end) else '-' end) as small_lane1, ";
ilane = 2;
selectCmd += " (case when c.lane_count >= " + ilane + " then char(case when SUM(case when s.car_class <> 3 then 0 when s.car_class = 3 and s.lane_id <> " + ilane + " then 0 else s.car_volume end) = 0 then 0 else SUM((case when s.car_class <> 3 then 0 when s.car_class = 3 and s.lane_id <> " + ilane + " then 0 else s.car_speed end) * (case when s.car_class <> 3 then 0 else s.car_volume end)) / SUM(case when s.car_class <> 3 then 0 when s.car_class = 3 and s.lane_id <> " + ilane + " then 0 else s.car_volume end) end) else '-' end) as connect_lane2, ";
selectCmd += " (case when c.lane_count >= " + ilane + " then char(case when SUM(case when s.car_class <> 2 then 0 when s.car_class = 2 and s.lane_id <> " + ilane + " then 0 else s.car_volume end) = 0 then 0 else SUM((case when s.car_class <> 2 then 0 when s.car_class = 2 and s.lane_id <> " + ilane + " then 0 else s.car_speed end) * (case when s.car_class <> 2 then 0 else s.car_volume end)) / SUM(case when s.car_class <> 2 then 0 when s.car_class = 2 and s.lane_id <> " + ilane + " then 0 else s.car_volume end) end) else '-' end) as big_lane2, ";
selectCmd += " (case when c.lane_count >= " + ilane + " then char(case when SUM(case when s.car_class <> 1 then 0 when s.car_class = 1 and s.lane_id <> " + ilane + " then 0 else s.car_volume end) = 0 then 0 else SUM((case when s.car_class <> 1 then 0 when s.car_class = 1 and s.lane_id <> " + ilane + " then 0 else s.car_speed end) * (case when s.car_class <> 1 then 0 else s.car_volume end)) / SUM(case when s.car_class <> 1 then 0 when s.car_class = 1 and s.lane_id <> " + ilane + " then 0 else s.car_volume end) end) else '-' end) as small_lane2, ";
ilane = 3;
selectCmd += " (case when c.lane_count >= " + ilane + " then char(case when SUM(case when s.car_class <> 3 then 0 when s.car_class = 3 and s.lane_id <> " + ilane + " then 0 else s.car_volume end) = 0 then 0 else SUM((case when s.car_class <> 3 then 0 when s.car_class = 3 and s.lane_id <> " + ilane + " then 0 else s.car_speed end) * (case when s.car_class <> 3 then 0 else s.car_volume end)) / SUM(case when s.car_class <> 3 then 0 when s.car_class = 3 and s.lane_id <> " + ilane + " then 0 else s.car_volume end) end) else '-' end) as connect_lane3, ";
selectCmd += " (case when c.lane_count >= " + ilane + " then char(case when SUM(case when s.car_class <> 2 then 0 when s.car_class = 2 and s.lane_id <> " + ilane + " then 0 else s.car_volume end) = 0 then 0 else SUM((case when s.car_class <> 2 then 0 when s.car_class = 2 and s.lane_id <> " + ilane + " then 0 else s.car_speed end) * (case when s.car_class <> 2 then 0 else s.car_volume end)) / SUM(case when s.car_class <> 2 then 0 when s.car_class = 2 and s.lane_id <> " + ilane + " then 0 else s.car_volume end) end) else '-' end) as big_lane3, ";
selectCmd += " (case when c.lane_count >= " + ilane + " then char(case when SUM(case when s.car_class <> 1 then 0 when s.car_class = 1 and s.lane_id <> " + ilane + " then 0 else s.car_volume end) = 0 then 0 else SUM((case when s.car_class <> 1 then 0 when s.car_class = 1 and s.lane_id <> " + ilane + " then 0 else s.car_speed end) * (case when s.car_class <> 1 then 0 else s.car_volume end)) / SUM(case when s.car_class <> 1 then 0 when s.car_class = 1 and s.lane_id <> " + ilane + " then 0 else s.car_volume end) end) else '-' end) as small_lane3, ";
ilane = 4;
selectCmd += " (case when c.lane_count >= " + ilane + " then char(case when SUM(case when s.car_class <> 3 then 0 when s.car_class = 3 and s.lane_id <> " + ilane + " then 0 else s.car_volume end) = 0 then 0 else SUM((case when s.car_class <> 3 then 0 when s.car_class = 3 and s.lane_id <> " + ilane + " then 0 else s.car_speed end) * (case when s.car_class <> 3 then 0 else s.car_volume end)) / SUM(case when s.car_class <> 3 then 0 when s.car_class = 3 and s.lane_id <> " + ilane + " then 0 else s.car_volume end) end) else '-' end) as connect_lane4, ";
selectCmd += " (case when c.lane_count >= " + ilane + " then char(case when SUM(case when s.car_class <> 2 then 0 when s.car_class = 2 and s.lane_id <> " + ilane + " then 0 else s.car_volume end) = 0 then 0 else SUM((case when s.car_class <> 2 then 0 when s.car_class = 2 and s.lane_id <> " + ilane + " then 0 else s.car_speed end) * (case when s.car_class <> 2 then 0 else s.car_volume end)) / SUM(case when s.car_class <> 2 then 0 when s.car_class = 2 and s.lane_id <> " + ilane + " then 0 else s.car_volume end) end) else '-' end) as big_lane4, ";
selectCmd += " (case when c.lane_count >= " + ilane + " then char(case when SUM(case when s.car_class <> 1 then 0 when s.car_class = 1 and s.lane_id <> " + ilane + " then 0 else s.car_volume end) = 0 then 0 else SUM((case when s.car_class <> 1 then 0 when s.car_class = 1 and s.lane_id <> " + ilane + " then 0 else s.car_speed end) * (case when s.car_class <> 1 then 0 else s.car_volume end)) / SUM(case when s.car_class <> 1 then 0 when s.car_class = 1 and s.lane_id <> " + ilane + " then 0 else s.car_volume end) end) else '-' end) as small_lane4, ";
ilane = 5;
selectCmd += " (case when c.lane_count >= " + ilane + " then char(case when SUM(case when s.car_class <> 3 then 0 when s.car_class = 3 and s.lane_id <> " + ilane + " then 0 else s.car_volume end) = 0 then 0 else SUM((case when s.car_class <> 3 then 0 when s.car_class = 3 and s.lane_id <> " + ilane + " then 0 else s.car_speed end) * (case when s.car_class <> 3 then 0 else s.car_volume end)) / SUM(case when s.car_class <> 3 then 0 when s.car_class = 3 and s.lane_id <> " + ilane + " then 0 else s.car_volume end) end) else '-' end) as connect_lane5, ";
selectCmd += " (case when c.lane_count >= " + ilane + " then char(case when SUM(case when s.car_class <> 2 then 0 when s.car_class = 2 and s.lane_id <> " + ilane + " then 0 else s.car_volume end) = 0 then 0 else SUM((case when s.car_class <> 2 then 0 when s.car_class = 2 and s.lane_id <> " + ilane + " then 0 else s.car_speed end) * (case when s.car_class <> 2 then 0 else s.car_volume end)) / SUM(case when s.car_class <> 2 then 0 when s.car_class = 2 and s.lane_id <> " + ilane + " then 0 else s.car_volume end) end) else '-' end) as big_lane5, ";
selectCmd += " (case when c.lane_count >= " + ilane + " then char(case when SUM(case when s.car_class <> 1 then 0 when s.car_class = 1 and s.lane_id <> " + ilane + " then 0 else s.car_volume end) = 0 then 0 else SUM((case when s.car_class <> 1 then 0 when s.car_class = 1 and s.lane_id <> " + ilane + " then 0 else s.car_speed end) * (case when s.car_class <> 1 then 0 else s.car_volume end)) / SUM(case when s.car_class <> 1 then 0 when s.car_class = 1 and s.lane_id <> " + ilane + " then 0 else s.car_volume end) end) else '-' end) as small_lane5, ";
ilane = 6;
selectCmd += " (case when c.lane_count >= " + ilane + " then char(case when SUM(case when s.car_class <> 3 then 0 when s.car_class = 3 and s.lane_id <> " + ilane + " then 0 else s.car_volume end) = 0 then 0 else SUM((case when s.car_class <> 3 then 0 when s.car_class = 3 and s.lane_id <> " + ilane + " then 0 else s.car_speed end) * (case when s.car_class <> 3 then 0 else s.car_volume end)) / SUM(case when s.car_class <> 3 then 0 when s.car_class = 3 and s.lane_id <> " + ilane + " then 0 else s.car_volume end) end) else '-' end) as connect_lane6, ";
selectCmd += " (case when c.lane_count >= " + ilane + " then char(case when SUM(case when s.car_class <> 2 then 0 when s.car_class = 2 and s.lane_id <> " + ilane + " then 0 else s.car_volume end) = 0 then 0 else SUM((case when s.car_class <> 2 then 0 when s.car_class = 2 and s.lane_id <> " + ilane + " then 0 else s.car_speed end) * (case when s.car_class <> 2 then 0 else s.car_volume end)) / SUM(case when s.car_class <> 2 then 0 when s.car_class = 2 and s.lane_id <> " + ilane + " then 0 else s.car_volume end) end) else '-' end) as big_lane6, ";
selectCmd += " (case when c.lane_count >= " + ilane + " then char(case when SUM(case when s.car_class <> 1 then 0 when s.car_class = 1 and s.lane_id <> " + ilane + " then 0 else s.car_volume end) = 0 then 0 else SUM((case when s.car_class <> 1 then 0 when s.car_class = 1 and s.lane_id <> " + ilane + " then 0 else s.car_speed end) * (case when s.car_class <> 1 then 0 else s.car_volume end)) / SUM(case when s.car_class <> 1 then 0 when s.car_class = 1 and s.lane_id <> " + ilane + " then 0 else s.car_volume end) end) else '-' end) as small_lane6, ";
selectCmd += " ";
selectCmd += " 2 as priority ";
selectCmd += " ";
selectCmd += " From " + "db2inst1" + "." + sTableName_SpotSpeed + " s, ";
selectCmd += " " + "db2inst1" + "." + sTableName_VdConfig + " c ";
selectCmd += " Where s.timestamp between timestamp('" + sStartDate.ToString("yyyy-MM-dd HH:mm:ss") + "') ";
selectCmd += " and timestamp('" + sEndDate.ToString("yyyy-MM-dd HH:mm:ss") + "') ";
selectCmd += " And s.devicename = c.devicename ";
selectCmd += " And s.devicename in " + sDevList + " ";
selectCmd += " Group by s.DeviceName, c.lane_count,timestamp ";
selectCmd += " ";
selectCmd += "UNION ";
selectCmd += " ";
selectCmd += "Select timestamp, ";
selectCmd += " s.DeviceName, ";
selectCmd += " '占量' as class, ";
selectCmd += " (case when count(*) = 0 then 0 else (select SUM(average_occupancy)/count(*) from " + "db2inst1" + ".tblvddata5min where devicename = s.devicename and timestamp between timestamp('" + sStartDate.ToString("yyyy-MM-dd HH:mm:ss") + "') and timestamp('" + sEndDate.ToString("yyyy-MM-dd HH:mm:ss") + "') group by devicename) end) as total, ";
selectCmd += " ";
ilane = 1;
sAvgO = "average_occupancy_lane1";
subSql = "(select SUM(" + sAvgO + ")/count(*) from " + "db2inst1" + "." + sTableName_VD5Min + " where devicename = s.devicename and timestamp between timestamp('" + sStartDate.ToString("yyyy-MM-dd HH:mm:ss") + "') and timestamp('" + sEndDate.ToString("yyyy-MM-dd HH:mm:ss") + "') group by devicename)";
lane_volume_sql = "SUM(case when s.lane_id <> " + ilane + " then 0 else s.car_volume end)";
selectCmd += " (case when c.lane_count >= " + ilane + " then char(case when ( " + subSql + " * " + lane_volume_sql + ") = 0 then 0 else (" + subSql + " * SUM(case when s.car_class <> 3 then 0 when s.car_class = 3 and s.lane_id <> " + ilane + " then 0 else s.car_volume end)) / (" + subSql + " * " + lane_volume_sql + ") end) else '-' end) as connect_lane1, ";
selectCmd += " (case when c.lane_count >= " + ilane + " then char(case when ( " + subSql + " * " + lane_volume_sql + ") = 0 then 0 else (" + subSql + " * SUM(case when s.car_class <> 2 then 0 when s.car_class = 2 and s.lane_id <> " + ilane + " then 0 else s.car_volume end)) / (" + subSql + " * " + lane_volume_sql + ") end) else '-' end) as big_lane1, ";
selectCmd += " (case when c.lane_count >= " + ilane + " then char(case when ( " + subSql + " * " + lane_volume_sql + ") = 0 then 0 else (" + subSql + " * SUM(case when s.car_class <> 1 then 0 when s.car_class = 1 and s.lane_id <> " + ilane + " then 0 else s.car_volume end)) / (" + subSql + " * " + lane_volume_sql + ") end) else '-' end) as small_lane1, ";
selectCmd += " ";
ilane = 2;
sAvgO = "average_occupancy_lane2";
subSql = "(select SUM(" + sAvgO + ")/count(*) from " + "db2inst1" + "." + sTableName_VD5Min + " where devicename = s.devicename and timestamp between timestamp('" + sStartDate.ToString("yyyy-MM-dd HH:mm:ss") + "') and timestamp('" + sEndDate.ToString("yyyy-MM-dd HH:mm:ss") + "') group by devicename)";
lane_volume_sql = "SUM(case when s.lane_id <> " + ilane + " then 0 else s.car_volume end)";
selectCmd += " (case when c.lane_count >= " + ilane + " then char(case when ( " + subSql + " * " + lane_volume_sql + ") = 0 then 0 else (" + subSql + " * SUM(case when s.car_class <> 3 then 0 when s.car_class = 3 and s.lane_id <> " + ilane + " then 0 else s.car_volume end)) / (" + subSql + " * " + lane_volume_sql + ") end) else '-' end) as connect_lane2, ";
selectCmd += " (case when c.lane_count >= " + ilane + " then char(case when ( " + subSql + " * " + lane_volume_sql + ") = 0 then 0 else (" + subSql + " * SUM(case when s.car_class <> 2 then 0 when s.car_class = 2 and s.lane_id <> " + ilane + " then 0 else s.car_volume end)) / (" + subSql + " * " + lane_volume_sql + ") end) else '-' end) as big_lane2, ";
selectCmd += " (case when c.lane_count >= " + ilane + " then char(case when ( " + subSql + " * " + lane_volume_sql + ") = 0 then 0 else (" + subSql + " * SUM(case when s.car_class <> 1 then 0 when s.car_class = 1 and s.lane_id <> " + ilane + " then 0 else s.car_volume end)) / (" + subSql + " * " + lane_volume_sql + ") end) else '-' end) as small_lane2, ";
selectCmd += " ";
ilane = 3;
sAvgO = "average_occupancy_lane3";
subSql = "(select SUM(" + sAvgO + ")/count(*) from " + "db2inst1" + "." + sTableName_VD5Min + " where devicename = s.devicename and timestamp between timestamp('" + sStartDate.ToString("yyyy-MM-dd HH:mm:ss") + "') and timestamp('" + sEndDate.ToString("yyyy-MM-dd HH:mm:ss") + "') group by devicename)";
lane_volume_sql = "SUM(case when s.lane_id <> " + ilane + " then 0 else s.car_volume end)";
selectCmd += " (case when c.lane_count >= " + ilane + " then char(case when ( " + subSql + " * " + lane_volume_sql + ") = 0 then 0 else (" + subSql + " * SUM(case when s.car_class <> 3 then 0 when s.car_class = 3 and s.lane_id <> " + ilane + " then 0 else s.car_volume end)) / (" + subSql + " * " + lane_volume_sql + ") end) else '-' end) as connect_lane3, ";
selectCmd += " (case when c.lane_count >= " + ilane + " then char(case when ( " + subSql + " * " + lane_volume_sql + ") = 0 then 0 else (" + subSql + " * SUM(case when s.car_class <> 2 then 0 when s.car_class = 2 and s.lane_id <> " + ilane + " then 0 else s.car_volume end)) / (" + subSql + " * " + lane_volume_sql + ") end) else '-' end) as big_lane3, ";
selectCmd += " (case when c.lane_count >= " + ilane + " then char(case when ( " + subSql + " * " + lane_volume_sql + ") = 0 then 0 else (" + subSql + " * SUM(case when s.car_class <> 1 then 0 when s.car_class = 1 and s.lane_id <> " + ilane + " then 0 else s.car_volume end)) / (" + subSql + " * " + lane_volume_sql + ") end) else '-' end) as small_lane3, ";
selectCmd += " ";
ilane = 4;
sAvgO = "average_occupancy_lane4";
subSql = "(select SUM(" + sAvgO + ")/count(*) from " + "db2inst1" + "." + sTableName_VD5Min + " where devicename = s.devicename and timestamp between timestamp('" + sStartDate.ToString("yyyy-MM-dd HH:mm:ss") + "') and timestamp('" + sEndDate.ToString("yyyy-MM-dd HH:mm:ss") + "') group by devicename)";
lane_volume_sql = "SUM(case when s.lane_id <> " + ilane + " then 0 else s.car_volume end)";
selectCmd += " (case when c.lane_count >= " + ilane + " then char(case when ( " + subSql + " * " + lane_volume_sql + ") = 0 then 0 else (" + subSql + " * SUM(case when s.car_class <> 3 then 0 when s.car_class = 3 and s.lane_id <> " + ilane + " then 0 else s.car_volume end)) / (" + subSql + " * " + lane_volume_sql + ") end) else '-' end) as connect_lane4, ";
selectCmd += " (case when c.lane_count >= " + ilane + " then char(case when ( " + subSql + " * " + lane_volume_sql + ") = 0 then 0 else (" + subSql + " * SUM(case when s.car_class <> 2 then 0 when s.car_class = 2 and s.lane_id <> " + ilane + " then 0 else s.car_volume end)) / (" + subSql + " * " + lane_volume_sql + ") end) else '-' end) as big_lane4, ";
selectCmd += " (case when c.lane_count >= " + ilane + " then char(case when ( " + subSql + " * " + lane_volume_sql + ") = 0 then 0 else (" + subSql + " * SUM(case when s.car_class <> 1 then 0 when s.car_class = 1 and s.lane_id <> " + ilane + " then 0 else s.car_volume end)) / (" + subSql + " * " + lane_volume_sql + ") end) else '-' end) as small_lane4, ";
selectCmd += " ";
ilane = 5;
sAvgO = "average_occupancy_lane5";
subSql = "(select SUM(" + sAvgO + ")/count(*) from " + "db2inst1" + "." + sTableName_VD5Min + " where devicename = s.devicename and timestamp between timestamp('" + sStartDate.ToString("yyyy-MM-dd HH:mm:ss") + "') and timestamp('" + sEndDate.ToString("yyyy-MM-dd HH:mm:ss") + "') group by devicename)";
lane_volume_sql = "SUM(case when s.lane_id <> " + ilane + " then 0 else s.car_volume end)";
selectCmd += " (case when c.lane_count >= " + ilane + " then char(case when ( " + subSql + " * " + lane_volume_sql + ") = 0 then 0 else (" + subSql + " * SUM(case when s.car_class <> 3 then 0 when s.car_class = 3 and s.lane_id <> " + ilane + " then 0 else s.car_volume end)) / (" + subSql + " * " + lane_volume_sql + ") end) else '-' end) as connect_lane5, ";
selectCmd += " (case when c.lane_count >= " + ilane + " then char(case when ( " + subSql + " * " + lane_volume_sql + ") = 0 then 0 else (" + subSql + " * SUM(case when s.car_class <> 2 then 0 when s.car_class = 2 and s.lane_id <> " + ilane + " then 0 else s.car_volume end)) / (" + subSql + " * " + lane_volume_sql + ") end) else '-' end) as big_lane5, ";
selectCmd += " (case when c.lane_count >= " + ilane + " then char(case when ( " + subSql + " * " + lane_volume_sql + ") = 0 then 0 else (" + subSql + " * SUM(case when s.car_class <> 1 then 0 when s.car_class = 1 and s.lane_id <> " + ilane + " then 0 else s.car_volume end)) / (" + subSql + " * " + lane_volume_sql + ") end) else '-' end) as small_lane5, ";
selectCmd += " ";
ilane = 6;
sAvgO = "average_occupancy_lane6";
subSql = "(select SUM(" + sAvgO + ")/count(*) from " + "db2inst1" + "." + sTableName_VD5Min + " where devicename = s.devicename and timestamp between timestamp('" + sStartDate.ToString("yyyy-MM-dd HH:mm:ss") + "') and timestamp('" + sEndDate.ToString("yyyy-MM-dd HH:mm:ss") + "') group by devicename)";
lane_volume_sql = "SUM(case when s.lane_id <> " + ilane + " then 0 else s.car_volume end)";
selectCmd += " (case when c.lane_count >= " + ilane + " then char(case when ( " + subSql + " * " + lane_volume_sql + ") = 0 then 0 else (" + subSql + " * SUM(case when s.car_class <> 3 then 0 when s.car_class = 3 and s.lane_id <> " + ilane + " then 0 else s.car_volume end)) / (" + subSql + " * " + lane_volume_sql + ") end) else '-' end) as connect_lane6, ";
selectCmd += " (case when c.lane_count >= " + ilane + " then char(case when ( " + subSql + " * " + lane_volume_sql + ") = 0 then 0 else (" + subSql + " * SUM(case when s.car_class <> 2 then 0 when s.car_class = 2 and s.lane_id <> " + ilane + " then 0 else s.car_volume end)) / (" + subSql + " * " + lane_volume_sql + ") end) else '-' end) as big_lane6, ";
selectCmd += " (case when c.lane_count >= " + ilane + " then char(case when ( " + subSql + " * " + lane_volume_sql + ") = 0 then 0 else (" + subSql + " * SUM(case when s.car_class <> 1 then 0 when s.car_class = 1 and s.lane_id <> " + ilane + " then 0 else s.car_volume end)) / (" + subSql + " * " + lane_volume_sql + ") end) else '-' end) as small_lane6, ";
selectCmd += " ";
selectCmd += " 3 as priority ";
selectCmd += " ";
selectCmd += " From " + "db2inst1" + "." + sTableName_SpotSpeed + " s, ";
selectCmd += " " + "db2inst1" + "." + sTableName_VdConfig + " c ";
selectCmd += " Where s.timestamp between timestamp('" + sStartDate.ToString("yyyy-MM-dd HH:mm:ss") + "') ";
selectCmd += " and timestamp('" + sEndDate.ToString("yyyy-MM-dd HH:mm:ss") + "') ";
selectCmd += " And s.devicename = c.devicename ";
selectCmd += " And s.devicename in " + sDevList + " ";
selectCmd += " Group by s.DeviceName, c.lane_count,timestamp ";
selectCmd += " ) x ";
selectCmd += " order by x.devicename, x.timestamp, x.priority ";
InitDB();
//舊的做法
//da = new DB2DataAdapter(selectCmd, conn);
//DataTable DT = new DataTable();
//da.Fill(DT);
//新的做法
DB2Command da = new DB2Command(selectCmd, conn);
DataTable DT = new DataTable();
DT.Load(da.ExecuteReader());
da.Dispose();
conn.Close();
return DT;
}
catch (Exception ex)
{
sErrMsg = ex.Message;
CloseDB();
return null;
}
}
/// <summary>
/// Uses a SQL Data Reader to load a data table with data from the query execution
/// </summary>
/// <param name="command">Command (Text command or Stored Procedure)</param>
/// <param name="type">Type of command (text, stored procedure or table-direct)</param>
/// <returns>Data Table with the execution results</returns>
public void LoadTable(string command, CommandType type)
{
DataTable resultSet = new DataTable();
try
{
using (DB2Connection connection = new DB2Connection(connectionString.ConnectionString))
{
using (DB2Command cmd = new DB2Command(command))
{
cmd.Connection = connection;
foreach (DB2Parameter parameter in Parameters)
{
cmd.Parameters.Add(parameter);
}
cmd.CommandType = type;
cmd.Connection.Open();
DB2DataReader reader = null;
reader = cmd.ExecuteReader(CommandBehavior.KeyInfo);
resultSet.Load(reader);
reader.Close();
cmd.Connection.Close();
}
}
}
catch (Exception ex)
{
Error aError = new Error(ex.Source, ex.Message, GetCurrentMethod());
ErrorList.Add(aError);
}
Table = resultSet;
}
/// <summary>
/// 执行指定数据库连接对象的数据阅读器.
/// </summary>
/// <remarks>
/// 如果是DB2Helper打开连接,当连接关闭DataReader也将关闭.
/// 如果是调用都打开连接,DataReader由调用都管理.
/// </remarks>
/// <param name="connection">一个有效的数据库连接对象</param>
/// <param name="transaction">一个有效的事务,或者为 'null'</param>
/// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param>
/// <param name="commandText">存储过程名或T-SQL语句</param>
/// <param name="commandParameters">DB2Parameters参数数组,如果没有参数则为'null'</param>
/// <param name="connectionOwnership">标识数据库连接对象是由调用者提供还是由DB2Helper提供</param>
/// <returns>返回包含结果集的DB2DataReader</returns>
private static DB2DataReader ExecuteReader(DB2Connection connection, DB2Transaction transaction, CommandType commandType, string commandText, DB2Parameter[] commandParameters, DB2ConnectionOwnership connectionOwnership)
{
if (connection == null) throw new ArgumentNullException("connection");
bool mustCloseConnection = false;
// 创建命令
DB2Command cmd = new DB2Command();
try
{
PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
// 创建数据阅读器
DB2DataReader dataReader;
if (connectionOwnership == DB2ConnectionOwnership.External)
{
dataReader = cmd.ExecuteReader();
}
else
{
dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
// 清除参数,以便再次使用..
// HACK: There is a problem here, the output parameter values are fletched
// when the reader is closed, so if the parameters are detached from the command
// then the DB2Reader can磘 set its values.
// When this happen, the parameters can磘 be used again in other command.
bool canClear = true;
foreach (DB2Parameter commandParameter in cmd.Parameters)
{
if (commandParameter.Direction != ParameterDirection.Input)
canClear = false;
}
if (canClear)
{
cmd.Parameters.Clear();
}
return dataReader;
}
catch
{
if (mustCloseConnection)
connection.Close();
throw;
}
}
protected void DB2Connection()
{
string Text1 = TextBox1.Text;
string inputPattern = @"^[\s\;]*\w+[^\;]*[\;\s]*$";
Regex input = new Regex(inputPattern);
Match inputmatch = input.Match(TextBox1.Text);
if (inputmatch.Success)
{
string s = @"Server=localhost:50000;Database=" + DropDownList2.SelectedItem.Text + ";UID=db2admin;PWD=***;CurrentSchema=db2admin";
DB2Connection myConnection = new DB2Connection(s);
DB2Command cmd = new DB2Command();
DB2DataReader r = null;
DB2DataAdapter adapter = new DB2DataAdapter(Text1, myConnection);
//string usertype = User.Identity.Name.Substring(4);
string usertype = Session["Role"].ToString().ToLower();
string pattern = "";
switch (usertype)
{
case "level1":
pattern = @"((^|\;)\s*(?i)(\binsert\b|\bupdate\b|\bdelete\b|\bmerge\b|\bcreate\b|\balter\b|\bdrop\b)(?i))|(?i)\binto\b(?i)";
break;
case "level2":
pattern = @"(^|;|\*\/)\s*(?i)(\bcreate\b|\balter\b|\bdrop\b)(?i)";
break;
default:
pattern = @"((^|;|\*\/)\s*(?i)(\bdrop\b\s*database)(?i))";
break;
}
Regex rgx = new Regex(pattern);
Match match = rgx.Match(TextBox1.Text);
if (match.Success)
{
msg.Text = "Permission was denied on database " + DropDownList2.SelectedItem.Text;
}
else
{
try
{
cmd.CommandText = TextBox1.Text;
cmd.Connection = myConnection;
myConnection.Open();
string firstWord = Regex.Match(TextBox1.Text, @"\w+\b").ToString().ToLower();
if (firstWord == "select")
{
DataTable dt = new DataTable();
adapter.Fill(dt);
GridView1.DataSource = dt;
r = cmd.ExecuteReader();
int rowCount = 0;
if (r.HasRows) //results>0
{
while (r.Read())
{
rowCount++;
}
msg.Text = "Result: " + rowCount + " row(s) found";
}
else
{
msg.Text = "Result: 0 row(s) found.<br /><span style='background-color:#339933; color:black; font-size: 15pt'>";
for (int i = 0; i < r.FieldCount; i++)
{
msg.Text += r.GetName(i) + " |";
}
msg.Text += "</span><br />";
}
r.Close();
}
else
{
int numberOfRecords = cmd.ExecuteNonQuery();
msg.Text = "Result: " + numberOfRecords + " row(s) affected.";
}
}
catch (Exception ex)
{
msg.Text = ex.Message;
}
finally
{
myConnection.Close();
GridView1.DataBind();
FormatView();
}
}
}
else
{
//msg.Text = "Only one statement allowed";
Header.Controls.Add(new LiteralControl("<script type=\"text/javascript\">alert('Only one statement allowed');</script>"));
}
}
