/// <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; } }