/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList">ArrayList</param>
public static void ExecuteSqlTran(ArrayList sqlList)
{
bool mustCloseConnection = false;
string ConString = System.Configuration.ConfigurationSettings.AppSettings["conInsertDB2"].ToString();
using (DB2Connection conn = new DB2Connection(ConString))
{
conn.Open();
using (DB2Transaction trans = conn.BeginTransaction())
{
DB2Command cmd = new DB2Command();
try
{
for (int i = 0; i < sqlList.Count; i++)
{
string cmdText = sqlList[i].ToString();
PrepareCommand(cmd, conn, trans, CommandType.Text, cmdText, null, out mustCloseConnection);
int val = cmd.ExecuteNonQuery();
}
trans.Commit();
}
catch
{
trans.Rollback();
throw;
}
finally
{
conn.Close();
cmd.Dispose();
}
}
}
}
protected override void Dispose(bool disposing)
{
if (!disposed)
{
if (disposing)
{
if (insertCommand != null)
{
insertCommand.Dispose();
}
if (updateCommand != null)
{
updateCommand.Dispose();
}
if (deleteCommand != null)
{
deleteCommand.Dispose();
}
}
}
}
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;
}
}
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;
}
}
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;
}
}
