public static ICommand getCMSCategory(int ruleid,int alarmclass,string devtype,int distance,string devName,string MegType,string lineid,string location,string devLine)
{
if (location == "S")
{
distance = 3;
}
SelectCommand com = new SelectCommand();
com.FiledNames += string.Format(" Mess.* , '{0}',cms.show_Icon " ,location);
com.TblNames += string.Format(" {0}.{1} Mess , {0}.{2} cms ", schema, DB2TableName.tblRSPDefaultMess,DB2TableName.tblCMSConfig);
com.WhereCon += string.Format(" Mess.ruleid={0} ", ruleid);
com.WhereCon += string.Format(" and Mess.alarmclass={0} ", alarmclass);
com.WhereCon += string.Format(" and Mess.devicetype='{0}' ", devtype)
+ string.Format(" and Mess.distance = {0}", distance);
//com.WhereCon += string.Format("and distance = case when (select sec.lineid ", 0);
//com.WhereCon += string.Format(" from {0}.{1} dev, ", schema,DB2TableName.tblDeviceConfig);
//com.WhereCon += string.Format(" {0}.{1} sec ", schema,DB2TableName.tblGroupSection);
//com.WhereCon += string.Format(" where devicename='{0}' ", devName);
//com.WhereCon += string.Format(" and sec.sectionid=dev.sectionid ) = '{0}' then {1} else 3 end ", lineid, distance);
com.WhereCon += string.Format(" and Mess.cmstype = cms.category and cms.Devicename='{0}' ",devName);
com.WhereCon += string.Format(" and Mess.level = '{0}' ", MegType);
if (devLine != lineid || location == "S") //�D�D�uŪ����u
{
com.WhereCon += " and Mess.Locate = 'T'";
}
else
{
com.WhereCon += " and Mess.Locate = 'N'";
}
return com;
}
public static ICommand getBlockMeg1(string typeList)
{
SelectCommand com = new SelectCommand();
com.FiledNames += string.Format(" * ");
com.TblNames += string.Format(" {0}.{1} ", schema, DB2TableName.tblRSPBlock);
com.WhereCon += string.Format(" typeid in ({0}) ", typeList);
return com;
}
public static ICommand getBlockMeg2(int typeId,int laneCount,string composition)
{
SelectCommand com = new SelectCommand();
com.FiledNames += string.Format(" * ");
com.TblNames += string.Format(" {0}.{1} ", schema, DB2TableName.tblRSPBlockDetail);
com.WhereCon += string.Format(" typeid = {0} ", typeId);
com.WhereCon += string.Format(" and lanecount = {0} ", laneCount);
com.WhereCon += string.Format(" and composition = '{0}' ", composition.Trim());
return com;
}
public static ICommand getDisplyDevTypes(Degree degree, int alarmClass, string sectionid)
{
SelectCommand com = new SelectCommand();
if (degree == Degree.L)
com.FiledNames += string.Format(" Ruleid,devicetype ,lowdevicestart as devStart,lownormal as normal,lowsystem as system,lowisextend as isextend ");
else if (degree == Degree.M)
com.FiledNames += string.Format(" Ruleid,devicetype ,middevicestart as devStart,midnormal as normal,midsystem as system,midisextend as isextend ");
else if (degree == Degree.H)
com.FiledNames += string.Format(" Ruleid,devicetype ,highdevicestart as devStart,highnormal as normal,highsystem as system,highisextend as isextend ");
else if (degree == Degree.S)
com.FiledNames += string.Format(" Ruleid,devicetype ,superdevicestart as devStart,supernormal as normal,supersystem as system,superisextend as isextend ");
else
com.FiledNames += string.Format(" Ruleid,devicetype ");
com.TblNames += string.Format(" {0}.{1} ", schema, DB2TableName.tblRSPDeviceRange);
com.WhereCon += string.Format(" Ruleid in (select Ruleid from {0}.{1} where RUNING='Y') ", schema, DB2TableName.tblRSPRule);
com.WhereCon += string.Format(" and ALARMCLASS={0} ", alarmClass);
com.WhereCon += string.Format(" and SECTIONID='{0}' and devicetype = 'CMS' ", sectionid);
return com;
}
public static ICommand getCMSCategory(int ruleid,int alarmclass,string devtype,int distance,string devName,string MegType,string lineid)
{
SelectCommand com = new SelectCommand();
com.FiledNames += string.Format(" * ");
com.TblNames += string.Format(" {0}.{1} ", schema, DB2TableName.tblRSPDefaultMess);
com.WhereCon += string.Format(" ruleid={0} ", ruleid);
com.WhereCon += string.Format(" and alarmclass={0} ", alarmclass);
com.WhereCon += string.Format(" and devicetype='{0}' ", devtype);
com.WhereCon += string.Format("and distance = case when (select sec.lineid ", 0);
com.WhereCon += string.Format(" from {0}.{1} dev, ", schema,DB2TableName.tblDeviceConfig);
com.WhereCon += string.Format(" {0}.{1} sec ", schema,DB2TableName.tblGroupSection);
com.WhereCon += string.Format(" where devicename='{0}' ", devName);
com.WhereCon += string.Format(" and sec.sectionid=dev.sectionid ) = '{0}' then {1} else 3 end ", lineid, distance);
com.WhereCon += string.Format(" and cmstype in (select category from {0}.{1} where Devicename='{2}') ",schema,DB2TableName.tblCMSConfig, devName);
com.WhereCon += string.Format(" and level = '{0}' ", MegType);
return com;
}
public static ICommand getRspExecution(string rspId)
{
SelectCommand com = new SelectCommand();
com.FiledNames += string.Format(" * ", 0);
com.TblNames += string.Format(" {0}.{1} exe ", schema,DB2TableName.tblRspExecution);
com.WhereCon += string.Format(" exe.exe_id='{0}' ", rspId);
return com;
}
public static ICommand getSectionSpeed(string sectionid)
{
SelectCommand com = new SelectCommand();
com.FiledNames += string.Format(" Rsp_CSLS_Decrease,Rsp_CSLS_Range,Rsp_MaxSpeed_Low,Rsp_MinSpeed_Low, ");
com.FiledNames += string.Format(" Rsp_MaxSpeed_Mid,Rsp_MinSpeed_Mid,Rsp_MaxSpeed_High,Rsp_MinSpeed_High,Rsp_MaxSpeed_Super,Rsp_MinSpeed_Super ");
com.TblNames += string.Format(" {0}.{1} ", schema, DB2TableName.vwGroupSection);
com.WhereCon += string.Format(" sectionid = '{0}' ", sectionid.Trim());
return com;
}
private static ICommand getTunnelData(string direction)
{
SelectCommand com = new SelectCommand();
com.FiledNames += string.Format(" row_number() over() as row,tun.tunnelid,tun.originalkey, ");
com.FiledNames += string.Format(" tun.tunnelname,line.lineid,line.linename,sec.SECTIONID,sec.DIRECTION,line.startmileage as line_start, ");
com.FiledNames += string.Format(" tun.startmileage as tun_Start,tun.endmileage as tun_end,line.endmileage as line_end ");
com.TblNames += string.Format(" {0}.{1} tun left join (select lineid,sectionid,direction from {0}.{2}) sec on sec.sectionid=tun.sectionid, ", schema, DB2TableName.tblGroupTunnel,DB2TableName.tblGroupSection);
com.TblNames += string.Format(" {0}.{1} line ", schema, DB2TableName.tblGroupLine);
com.WhereCon += string.Format(" line.lineid=sec.lineid ");
com.WhereCon += string.Format(" and sec.direction='{0}' ", direction.Trim());
com.OrderBy += string.Format(" sec.direction,tun.startmileage ");
return com;
}
public static ICommand getIIPEventCmd(int eventid)
{
SelectCommand com = new SelectCommand();
com.FiledNames += " * ";
com.TblNames += string.Format(" {0}.{1} ", schema, DB2TableName.tblIIPEvent);
com.WhereCon += string.Format(" eventid = {0} ", eventid);
return com;
}
public static ICommand getTunnelData()
{
SelectCommand com = new SelectCommand();
com.FiledNames += string.Format(" * ");
com.TblNames += string.Format(" ( ");
com.TblNames += string.Format(" ({0}) ", getTunnelData("S").getCommand());
com.TblNames += string.Format(" union ");
com.TblNames += string.Format(" ({0} {1}) ", getTunnelData("N").getCommand(), "desc");
com.TblNames += string.Format(" union ");
com.TblNames += string.Format(" ({0}) ", getTunnelData("E").getCommand());
com.TblNames += string.Format(" union ");
com.TblNames += string.Format(" ({0} {1}) ", getTunnelData("W").getCommand(), "desc");
com.TblNames += string.Format(" ) a ");
com.OrderBy += string.Format(" a.direction ");
return com;
}
/// <summary>
/// ���o�s������ƪ�
/// </summary>
/// <param name="alarmclass">�����O�s��</param>
/// <param name="sectionid">��q�s��</param>
/// <returns></returns>
public static ICommand getUnitData(byte alarmclass, int start_mile, int end_mile, string LineID)
{
SelectCommand com = new SelectCommand();
com.FiledNames += string.Format(" ser.serviceid,type.subserviceid,servicename, ", 0);
com.FiledNames += string.Format(" event.alarmclass,event.ifalarm,type.phone,type.fax,type.start_mileage,type.END_MILEAGE ", 0);
com.TblNames += string.Format(" {0}.{1} ser, ", schema,DB2TableName.tblIIPCommService);
com.TblNames += string.Format(" {0}.{1} event, ", schema, DB2TableName.tblIIPCommServiceEvent);
com.TblNames += string.Format(" {0}.{1} type ", schema, DB2TableName.tblIIPServiceType);
com.WhereCon += string.Format(" ser.SERVICEID=event.SERVICEID ");
com.WhereCon += string.Format(" and type.SERVICEID=event.SERVICEID ");
com.WhereCon += string.Format(" and event.alarmclass = {0} and event.ifAlarm = 1", alarmclass);
com.WhereCon += string.Format(" and (type.start_mileage<={0} or type.start_mileage<={1}) ", start_mile, end_mile);
com.WhereCon += string.Format(" and (type.END_MILEAGE>{0} or type.END_MILEAGE>{1}) and type.LineID = '{2}'", start_mile, end_mile,LineID);
return com;
}
public static ICommand getOutputData(string rspId,string devName)
{
SelectCommand com = new SelectCommand();
com.FiledNames += string.Format(" device_type,outputdata1,devicename,priority ");
com.TblNames += string.Format(" {0}.{1} ", schema, DB2TableName.tblRSPExecutionOutputData);
com.WhereCon += string.Format(" exe_id='{0}' and deviceName='{1}' ", rspId, devName);
return com;
}
public static ICommand gettblRSPExecutionOutputdataFlowID(string exe_id, string devicename, string device_type)
{
SelectCommand com = new SelectCommand();
com.FiledNames += string.Format(" flowid ");
com.TblNames += string.Format(" {0}.{1} ", schema, DB2TableName.tblRSPExecutionOutputData);
com.WhereCon += string.Format(" exe_id='{0}' ", exe_id);
com.WhereCon += string.Format(" and devicename='{0}' ", devicename);
com.WhereCon += string.Format(" and device_type='{0}' ", device_type);
return com;
}
public static ICommand getMoveEventCmd(int alarmClass)
{
SelectCommand com = new SelectCommand();
com.FiledNames += " * ";
com.TblNames += string.Format(" {0}.{1} ", schema, DB2TableName.tblIIPEvent);
com.WhereCon += string.Format(" INC_NAME={0} and INC_STATUS in ({1},{2},{3}) ", alarmClass, (int)EventStatus.Enter, (int)EventStatus.Execute, (int)EventStatus.Wait);
return com;
}
public static ICommand getOutputData(string rspId)
{
SelectCommand com = new SelectCommand();
com.FiledNames += string.Format(" device_type,case when outputdata2 is null then outputData1 when outputdata2 = '' then outputdata1 else outputdata2 end,devicename,priority ");
com.TblNames += string.Format(" {0}.{1} ", schema, DB2TableName.tblRSPExecutionOutputData);
com.WhereCon += string.Format(" exe_id='{0}' ", rspId);
return com;
}
public static ICommand getMessRule(string serverity,int type)
{
SelectCommand com = new SelectCommand();
com.FiledNames += string.Format(" * ");
com.TblNames += string.Format(" {0}.{1} ", schema, DB2TableName.tblRSPMessRule);
com.WhereCon += string.Format(" serverity = '{0}' ", serverity);
com.WhereCon += string.Format(" and Ruleid in (select Ruleid from {0}.{1} where RUNING='Y') ", schema,DB2TableName.tblRSPRule);
com.WhereCon += string.Format(" and type = {0}", type);
return com;
}
public static ICommand getMessColor()
{
SelectCommand com = new SelectCommand();
com.FiledNames += string.Format(" * ");
com.TblNames += string.Format(" {0}.{1} ", schema, DB2TableName.tblRSPMessColor);
return com;
}
/// <summary>
/// ���o�۰ʡB�b�۰ʸ�ƪ�
/// </summary>
/// <param name="eventID">�ƥ�s��</param>
/// <returns>SQL�R�O</returns>
public static ICommand getLogCmd(int eventID)
{
SelectCommand selectCmd = new SelectCommand();
string str = "";
str += " list.FLOWID,cty.alarmtype,list.alarmclass,list.DEGREE, ";
str += " list.TIMESTAMP as startTime,list.endTime, (case list.alarmclass when 60 then "
+ " (Select fire.Start_mileage from TBLGROUPTUNNELFIREDIVISION fire,TBLGROUPTUNNELPARENT tun "
+ " where tun.Tunid = fire.tunID and char(tun.tuncode) = log.tunid and log.placeid = fire.placeid and log.divid = fire.divid ) else list.STARTMILEAGE end) as STARTMILEAGE,";
str += " (case list.alarmclass when 60 then "
+ " (Select fire.End_mileage from TBLGROUPTUNNELFIREDIVISION fire,TBLGROUPTUNNELPARENT tun "
+ " where tun.Tunid = fire.tunID and char(tun.tuncode) = log.tunid and log.placeid = fire.placeid and log.divid = fire.divid ) else list.ENDMILEAGE end) as ENDMILEAGE, list.DEVICENAME,list.LINEID,list.DIRECTION,list.SectionId, ";
str += " list.IFHANDLE,list.IFCLOSE,list.IFERROR, ";
str += " log.Lane_id,log.Cam_id,log.DivisionID,log.eventid,log.memo, ";
str += " cty.login_mode,log.originaleventid, ";
str += " log.MC_ID,log.MC_Notifier,log.MC_blocktypeid,log.MC_blocklane,mc_memo,Execution,log.Login_Mode ";
selectCmd.FiledNames = str;
str = "";
str += string.Format(" {0}.{1} list left join {0}.{2} cty on cty.alarmclass = list.alarmclass ", schema, DB2TableName.tblSysAlarmList, DB2TableName.tblSysAlarmConfig);
str += string.Format(" left join {0}.{1} log on list.ORIGINALKEY = log.eventid ", schema, DB2TableName.tblSysAlarmLog);
selectCmd.TblNames = str;
str = "";
str += string.Format(" type='S' and ORIGINALKEY={0} ", eventID);
selectCmd.WhereCon = str;
return selectCmd;
}
public static ICommand getSerious(int laneCount)
{
SelectCommand com = new SelectCommand();
com.FiledNames += string.Format(" * ");
com.TblNames += string.Format(" {0}.{1} ", schema, DB2TableName.tblRSPSerious);
com.WhereCon += string.Format(" lanecount = {0} ", laneCount);
com.WhereCon += string.Format(" and Ruleid in (select Ruleid from {0}.{1} where RUNING='Y') ", schema,DB2TableName.tblRSPRule);
return com;
}
public static ICommand getPriority(byte alarmclass,MegType type)
{
SelectCommand com = new SelectCommand();
com.FiledNames += string.Format(" * ");
com.TblNames += string.Format(" {0}.{1} ", schema, DB2TableName.tblRspPriority);
com.WhereCon += string.Format(" alarmclass={0} and level='{1}' ",alarmclass,type.ToString());
return com;
}
public static ICommand getSeriousByAlarmClass(int alarmClass)
{
SelectCommand com = new SelectCommand();
com.FiledNames += string.Format(" * ");
com.TblNames += string.Format(" {0}.{1} ", schema, DB2TableName.tblRSPSeriousByAlarmClass);
com.WhereCon += string.Format(" alarmclass = {0} ", alarmClass);
com.WhereCon += string.Format(" and Ruleid in (select Ruleid from {0}.{1} where RUNING='Y') ", schema, DB2TableName.tblRSPRule);
return com;
}
public static ICommand getHostIP()
{
SelectCommand com = new SelectCommand();
com.FiledNames += string.Format(" hostip ");
com.TblNames += string.Format(" {0}.{1} ", schema, DB2TableName.tblHostConfig);
com.WhereCon += " HOSTID='HOST' ";
return com;
}
public static ICommand getTunnelData(string id,string direction)
{
SelectCommand com = new SelectCommand();
com.FiledNames += string.Format(" * ");
com.TblNames += string.Format(" {0}.{1} ", schema, DB2TableName.tblGroupTunnel);
com.WhereCon += string.Format(" Tunnelid = '{0}' ", id.Trim());
return com;
}
/// <summary>
/// ���o�ƥ�s��
/// </summary>
/// <returns></returns>
public static ICommand getEventID()
{
SelectCommand com = new SelectCommand();
//select NEXTVAL FOR DB2INST1.SEQ_EVENT FROM SYSIBM.SYSDUMMY1
com.FiledNames += string.Format(" NEXTVAL FOR {0}.SEQ_EVENT ", schema);
com.TblNames += "SYSIBM.SYSDUMMY1";
return com;
}
public static ICommand getExeUnitData(string rspId, int alarmClass)
{
SelectCommand com = new SelectCommand();
com.FiledNames += string.Format(" iip.INC_ID,iip.COMM_TIME,eve.SERVICEID,iip.SUBSERVICEID,type.SUBSERVICENAME,type.PHONE,type.FAX,ser.SERVICENAME,iip.MEMO,eve.IFALARM ");
com.TblNames += string.Format(" {0}.{1} iip left join(select * from {0}.{2}) type on type.subserviceid=iip.subserviceid ", schema, DB2TableName.tblIIPService, DB2TableName.tblIIPServiceType);
com.TblNames += string.Format(" left join(select * from {0}.{1}) ser on ser.serviceid =type.serviceid ", schema, DB2TableName.tblIIPCommService);
com.TblNames += string.Format(" left join(select * from {0}.{1}) eve on ser.serviceid =eve.serviceid ", schema, DB2TableName.tblIIPCommServiceEvent);
com.WhereCon += string.Format(" iip.INC_ID='{0}' ", rspId);
com.WhereCon += string.Format(" and eve.alarmclass={0} ", alarmClass);
return com;
}
public static ICommand getRGSMode()
{
SelectCommand com = new SelectCommand();
com.FiledNames += string.Format(" dev.devicename,dev.mile_m,dev.lineid,dev.sectionid,dev.direction,rgs.cityroad_g_id,rgs.SECONDLINE_G_ID ");
com.TblNames += string.Format(" {0}.{1} dev left join(select * from {0}.{2}) sec on sec.sectionid=dev.sectionid, ", schema, DB2TableName.tblDeviceConfig,DB2TableName.tblGroupSection);
com.TblNames += string.Format(" {0}.{1} rgs ", schema, DB2TableName.tblRgsConfig);
com.WhereCon += string.Format(" dev.devicename=rgs.devicename ");
return com;
}
private static ICommand getLineData()
{
SelectCommand com = new SelectCommand();
com.FiledNames += string.Format(" * ");
com.TblNames += string.Format(" {0}.{1} ", schema, DB2TableName.tblGroupLine);
return com;
}
public static ICommand getRMSMode()
{
SelectCommand com = new SelectCommand();
com.FiledNames += string.Format(" dev.devicename,dev.mile_m,dev.lineid,dev.sectionid,dev.direction, ");
com.FiledNames += string.Format(" rsp_controlmode_low,rsp_planno_low, ");
com.FiledNames += string.Format(" rsp_controlmode_mid,rsp_planno_mid, ");
com.FiledNames += string.Format(" rsp_controlmode_high,rsp_planno_high, ");
com.FiledNames += string.Format(" rsp_controlmode_super,rsp_planno_super ");
com.TblNames += string.Format(" {0}.{1} dev, ", schema, DB2TableName.tblDeviceConfig);
com.TblNames += string.Format(" {0}.{1} rms ", schema, DB2TableName.tblRmsConfig);
com.WhereCon += string.Format(" dev.devicename=rms.devicename ");
return com;
}
public static ICommand getRGSCategory(int ruleid, int alarmclass, string devtype, int distance, string devName, string MegType, string lineid,string location,string devLine)
{
SelectCommand com = new SelectCommand();
com.FiledNames += string.Format(" * ");
com.TblNames += string.Format(" {0}.{1} ", schema, DB2TableName.tblRSPDefaultMess);
com.WhereCon += string.Format(" ruleid={0} ", ruleid);
com.WhereCon += string.Format(" and alarmclass={0} ", alarmclass);
com.WhereCon += string.Format(" and devicetype='{0}' ", devtype)
+ string.Format(" and distance = {0}", distance);
//com.WhereCon += string.Format("and distance = case when (select sec.lineid ", 0);
//com.WhereCon += string.Format(" from {0}.{1} dev, ", schema, DB2TableName.tblDeviceConfig);
//com.WhereCon += string.Format(" {0}.{1} sec ", schema, DB2TableName.tblGroupSection);
//com.WhereCon += string.Format(" where devicename='{0}' ", devName);
//com.WhereCon += string.Format(" and sec.sectionid=dev.sectionid ) = '{0}' then {1} else 3 end ", lineid, distance);
com.WhereCon += string.Format(" and level = '{0}' ", MegType);
if (devLine != lineid)//�����D��ΫD�D�uŪ����u
{
com.WhereCon += " and Locate = 'T'";
}
else
{
com.WhereCon += " and Locate = 'N'";
}
return com;
}
/// <summary>
/// ���o�����p���ƥ��ƪ�
/// </summary>
/// <param name="devName">�]�ƦW��</param>
/// <returns>�R�O�@�P����</returns>
public static ICommand getIIPEventCmd(string id,bool isEventId)
{
SelectCommand com = new SelectCommand();
//com.FiledNames = " * ";
com.FiledNames += " eve.INC_ID,eve.inc_type_name,eve.inc_name,eve.incidentid, ";
com.FiledNames += " eve.inc_congestion,inc_serverity,inc_lineid,inc_direction, ";
com.FiledNames += " eve.inc_location,eve.inc_interchange,eve.from_milepost1, ";
com.FiledNames += " eve.to_milepost1,inc_blockage,eve.inc_login_mode,eve.inc_time, ";
com.FiledNames += " eve.inc_step_times,eve.inc_stepno,eve.inc_status,eve.inc_spreadnews, ";
com.FiledNames += " eve.control_volumn_level,eve.roadnet_turnto,eve.delay_time, ";
com.FiledNames += " eve.entrance,eve.eventid,sec.SECTIONNAME,line.LINENAME,type.DESCRIPTION as alarmTypename, ";
com.FiledNames += " con.DESCRIPTION as alarmclassName,con.shortname,line.g_code_id as LINE_ICON, ";
com.FiledNames += " con.G_CODE_ID as ACC_ICON,sec.G_CODE_ID_CITY,sec.G_CODE_ID_PATH,sec.Lane_count,eve.BlockTypeId, ";
com.FiledNames += " line.DIRECTION as line_Direction,line.STARTMILEAGE as line_s_mile,line.ENDMILEAGE as line_e_mile , eve.inc_notify_plant,eve.Execute";
com.TblNames += string.Format(" {0}.{1} eve left join(select * from {0}.{2}) sec on sec.sectionid=eve.inc_location ", schema, DB2TableName.tblIIPEvent, DB2TableName.vwGroupSection);
com.TblNames += string.Format(" left join(select * from {0}.{1}) line on line.lineid=eve.inc_lineid ", schema, DB2TableName.tblGroupLine);
com.TblNames += string.Format(" left join(select * from {0}.{1}) type on type.alarmtype=eve.Inc_type_name ", schema, DB2TableName.tblSysAlarmType);
com.TblNames += string.Format(" left join(select * from {0}.{1}) con on con.alarmclass=eve.Inc_name ", schema, DB2TableName.tblSysAlarmConfig);
com.WhereCon += string.Format(" eve.INC_STATUS = {0} ", (int)EventStatus.Enter);
if (!string.IsNullOrEmpty(id))
{
if (!isEventId)
com.WhereCon += string.Format(" and eve.INC_ID = '{0}'", id);
else
com.WhereCon += string.Format(" and eve.EventID = {0}", id);
}
return com;
}
