busdata.h

关于数据库应用方面的例子

	{_T("GuardID"),DATA_INT,4},
	{_T("roomname"),DATA_NVARCHAR,CC_ROOM_NAME_LEN},
	{_T("LockCode"),DATA_NVARCHAR,CC_DOOR_MAGCODE_LEN},
};
#define GUARDLOCK_DEVADDR_POS	8+CC_DEVIE_NAME_LEN+CC_DEVICE_NUM_LEN
#define GUARDLOCK_DEVADDR_VALUE	14+CC_DEVIE_NAME_LEN+CC_DEVICE_NUM_LEN
BusFieldStruct  FieldsDoorKeyGuardLock[]=
{
	{_T("DevID"),DATA_INT,4},
	{_T("Name"),DATA_NVARCHAR,CC_DEVIE_NAME_LEN},
	{_T("SeriaL"),DATA_NVARCHAR,CC_DEVICE_NUM_LEN},
	{_T("HouseID"),DATA_INT,4},
	{_T("SysAddr"),DATA_SMALLINT,2},
	{_T("DevAddr"),DATA_SMALLINT,2},
	{_T("DevType"),DATA_SMALLINT,2},
	{_T("Value"),DATA_SMALLINT,2},
	{_T("IsOnLine"),DATA_TINYINT,1},
	{_T("SysStatus"),DATA_TINYINT,1},
	{_T("RealDevID"),DATA_INT,4},
	{_T("OperSource"),DATA_INT,4},
	{_T("Reserve"),DATA_NVARCHAR,CC_KEEP_CODE_LEN},

	{_T("nDoorMagCode"),DATA_NVARCHAR,CC_DOOR_MAGCODE_LEN}
};
//组合业务表数据
CombBusStruct CombBusList[]=
{
//取short类型数据
	{
		{
			BUS_VALUE_SHORT,
			0,
			sizeof(FieldsValueShort)/sizeof(FieldsValueShort[0]),
			_T("CCInfoTable"),FieldsValueShort,
			-1,
			-1,
			-1
		},
			_T("select 1 as value")
	},
//取int类型数据
	{
		{
			BUS_VALUE_INT,
			0,
			sizeof(FieldsValueInt)/sizeof(FieldsValueInt[0]),
			_T("CCInfoTable"),FieldsValueInt,
			-1,
			-1,
			-1
		},
			_T("select 1 as value")
	},
//中控用户信息
	{
		{
			MULTIBUS_CCINFO,
			0,
			sizeof(FieldsCCInfoDetail)/sizeof(FieldsCCInfoDetail[0]),
			_T("CCInfoTable"),FieldsCCInfoDetail,
			0,
			-1,
			-1
		},
			_T("select CCID,Name,Password,SuperPsw,TelPsw,UserName,Sex,Birthday,CertificateType,\
			Certificate,MobileNum,ZipCode,a.Section,Addr,HomePhone,Serial,GardenID,a.Reserve,\
			SectionName from CCInfoTable as a left join SectionTable as b on a.Section= b.Section")
	},
	//房间及其设备数量信息
	{
		{
			MULTIBUS_HOUSE_DEVNUM,
			0,
			sizeof(FieldsHouseDevNumInfo)/sizeof(FieldsHouseDevNumInfo[0]),
			_T("CCInfoTable"),FieldsHouseDevNumInfo,
			0,
			-1,
			-1
		},
			_T("select a.HouseID,a.Name,a.TypeID,a.Description,a.Reserve,TypeName,count(DevID) as DevCount from HouseTable as a inner join \
			HouseTypeTable as b on a.TypeID = b.HouseTypeID left join DevTable as c on a.houseid=c.houseid \
			group by a.HouseID,a.Name,a.TypeID,a.Description,a.Reserve,b.TypeName")
	},
//设备房间信息	5002
	{
		{
			MULTIBUS_DEV_HOUSENAME,
			0,
			sizeof(FieldsDevHouseName)/sizeof(FieldsDevHouseName[0]),
			_T("CCInfoTable"),FieldsDevHouseName,
			0,
			-1,
			-1
		},
			_T("select DevID,DevTable.Name,Serial,DevTable.HouseID,SysAddr,DevAddr,DevType,Value,IsOnLine,SysStatus,RealDevID,OperSource,DevTable.Reserve,HouseTable.Name \
				from HouseTable,DevTable where DevTable.HouseID = HouseTable.HouseID")

	},
//查询场景及其房间信息
	{
		{
			MULTIBUS_SENCE_HOUSENAME,
			0,
			sizeof(FieldsSceneHouseName)/sizeof(FieldsSceneHouseName[0]),
			_T("CCInfoTable"),FieldsSceneHouseName,
			0,
			-1,
			-1
		},
	//		_T("select SceneID,SceneTable.Name,SceneTable.HouseID,Attrib,Type,Description,SceneTable.Reserve,HouseTable.Name as HouseName \
	//			from SceneTable,HouseTable where SceneTable.HouseID= HouseTable.HouseID"
				_T("select SceneID,SceneTable.Name,SceneTable.HouseID,Attrib,Type,SceneTable.Description,SceneTable.Reserve,HouseTable.Name as HouseName \
				from SceneTable,HouseTable where SceneTable.HouseID= HouseTable.HouseID")
	},
//场景及其房间设备信息
	{
		{
			MULTIBUS_SENCE_HOUSENAME_DEVNUM,
			0,
			sizeof(FieldsSceneHouseNameDevNum)/sizeof(FieldsSceneHouseNameDevNum[0]),
			_T("CCInfoTable"),FieldsSceneHouseNameDevNum,
			0,
			-1,
			-1
		},
	//		_T("select SceneTable.SceneID,SceneTable.Name,SceneTable.HouseID,Attrib,Type,Description,SceneTable.Reserve,HouseTable.Name as HouseName count(DevID) as DevNum\
	//			from SceneTable,HouseTable,SceneDevTable where SceneTable.HouseID= HouseTable.HouseID and SceneDevTable.SceneID= SceneTable.SceneID \
	//			group by SceneDevTable.SceneID"
			_T("select a.SceneID,a.Name,a.HouseID,Attrib,Type,a.Description,a.Reserve,b.Name,count(DevID) as DevNum \
				from SceneTable as a \
				LEFT JOIN SceneDevTable as c ON c.SceneID= a.SceneID \
				LEFT JOIN HouseTable as b ON a.HouseID= b.HouseID \
				group by a.SceneID,a.Name,a.HouseID,Attrib,Type,a.Description,a.Reserve,b.Name")
	},
//场景、房间、设备
	{
		{
			MULTIBUS_SENCE_HOUSENAME_DEVNAME,
			0,
			sizeof(FieldsSceneHouseNameDevName)/sizeof(FieldsSceneHouseNameDevName[0]),
			_T("CCInfoTable"),FieldsSceneHouseNameDevName,
			0,
			-1,
			-1
		},
		_T("select a.SceneID,a.DevID,Oper,a.Value,a.reserve,DevType,b.Name,c.Name \
			from SceneDevTable as a,DevTable as b,HouseTable as c \
			where a.DevID=b.DevID and b.HouseID = c.HouseID")

	},
//门卡及其有效时间
	{
		{
			MULTIBUS_CARD_TIME,
			0,
			sizeof(FieldsCardTime)/sizeof(FieldsCardTime[0]),
			_T("CCInfoTable"),FieldsCardTime,
			6,
			-1,
			-1
		},
		_T("select a.CardID,Number,UserName,RegTime,CardType,a.Reserve,Idx,Type,StartTime,EndTime \
			from CardTable as a LEFT JOIN CardTimer as b ON \
			a.CardID = b.CardID")
	},
//日程详细信息	5007
	{
		{
			MULTIBUS_SCHEDULEDETAIL,
			0,
			sizeof(FieldsScheduleDetail)/sizeof(FieldsScheduleDetail[0]),
			_T("CCInfoTable"),FieldsScheduleDetail,
			0,
			-1,
			-1
		},//时钟业务类型bustype为2
		_T("")

	},
//闹钟详细信息	5008
	{
		{
			MULTIBUS_CLOCKDETAIL,
			0,
			sizeof(FieldsClockDetail)/sizeof(FieldsClockDetail[0]),
			_T("CCInfoTable"),FieldsClockDetail,
			0,
			-1,
			-1
		},
		_T("")
	},
//备忘录详细信息	5009
	{
		{
			MULTIBUS_TICKLERDETAIL,
			0,
			sizeof(FieldsTicklerDetail)/sizeof(FieldsTicklerDetail[0]),
			_T("CCInfoTable"),FieldsTicklerDetail,
			0,
			-1,
			-1
		},
		_T("")
	},
//局部设防信息
	{
		{
			MULTIBUS_PARTSAFTY_HOUSENAME,
			0,
			sizeof(FieldsPartSaftyHouseName)/sizeof(FieldsPartSaftyHouseName[0]),
			_T("CCInfoTable"),FieldsPartSaftyHouseName,
			0,
			-1,
			-1
		},
		_T("select ID,a.HouseID,Type,StartTime,EndTime,b.Name \
			from  PartDefenceTable as a left join HouseTable as b \
			on a.HouseID = b.HouseID")
	},
//门磁门禁信息
	{
		{
			MULTIBUS_LOCK_GUARD,
			0,
			sizeof(FieldsLockGuard)/sizeof(FieldsLockGuard[0]),
			_T("CCInfoTable"),FieldsLockGuard,
			0,
			-1,
			-1
		},
		_T("")
	},
//未绑定的门禁信息
	{
		{
			MULTIBUS_GUARD_NOLOCK,
			0,
			sizeof(FieldsGuardNoLock)/sizeof(FieldsGuardNoLock[0]),
			_T("CCInfoTable"),FieldsGuardNoLock,
			0,
			-1,
			-1
		},
			_T("select DevID,devtable.Name,Housetable.Name \
			from DevTable,HouseTable \
			where DevType=4 and DevTable.HouseID = HouseTable.HouseID and DevID NOT IN (select GuardID from GuardLockTable)")
	},



	//#define	MULTIBUS_PASSRECORD_DETAIL	5013	//进出记录的详细信息
	{
		{
			MULTIBUS_PASSRECORD_DETAIL,
			0,
			sizeof(FieldsPassRecordDetail)/sizeof(FieldsPassRecordDetail[0]),
			_T("CCInfoTable"),FieldsPassRecordDetail,
			0,
			-1,
			-1
		},
			_T("select Idx,a.CardID,a.UserName,Time,a.GuardID,a.Reserve,b.Name,Number\
			from passrecord as a,devtable as b,CardTable as c\
			where a.GuardID=DevID and a.CardID = c.CardID order by time desc")
	},
//#define	MULTIBUS_ALARMCONFIG_DETAIL	5015	//报警设置的详细信息（根据ID查询一条记录）
	{
		{
			MULTIBUS_ALARMCONFIG_DETAIL,
			0,
			sizeof(FieldsAlarmConfigDetail)/sizeof(FieldsAlarmConfigDetail[0]),
			_T("CCInfoTable"),FieldsAlarmConfigDetail,
			0,
			-1,
			-1
		},
			_T("select a.ID,SendType,MobileNum,TelNum,MailAddr,Content,a.RingID,a.Reserve,b.Name \
			from AlarmConfigTable as a left join RingTable as b \
			on a.RingID=b.RingID")
	},
//#define	MULTIBUS_ALARMSPRING_DETAIL	5014	//查询所有的报警触发详细记录(或者当前报警)
/*	{
		{
			MULTIBUS_ALARMSPRING_DETAIL,
			0,
			sizeof(FieldsAlarmSpringDetail)/sizeof(FieldsAlarmSpringDetail[0]),
			_T("CCInfoTable"),FieldsAlarmSpringDetail,
			0,
			-1,
			-1
		},
			_T(""
	},
*/
//#define	MULTIBUS_ALARMNOTIFY_DETAIL	5016	//报警通知的详细信息
	{
		{
			MULTIBUS_ALARMNOTIFY_DETAIL,
			0,
			sizeof(FieldsAlarmNotifyDetail)/sizeof(FieldsAlarmNotifyDetail[0]),
			_T("CCInfoTable"),FieldsAlarmNotifyDetail,
			0,
			-1,
			-1
		},
			_T("select a.Idx,AlarmSpingID,SendType,NotifyObject,RingID,Result,a.Reserve,b.AlarmTypeID,b.Time \
			from AlarmNotifyHisTable as a,AlarmSpringTable as b \
			where a.AlarmSpingID = b.Idx")
	},
//MULTIBUS_REMOTE_BIND		5017	//遥控器绑定信息表
	{
		{
			MULTIBUS_REMOTE_BIND,
			0,
			sizeof(FieldsRemoteFunBind)/sizeof(FieldsRemoteFunBind[0]),
			_T("CCInfoTable"),FieldsRemoteFunBind,
			0,
			-1,
			-1
		},
			_T("select Idx,KeyVal,ObjectType,ObjectID,a.Reserve,Name\
			from InfrareFunBindTable as a,devTable as b \
			where a.ObjectType=%d and ObjectID = b.DevID \
			UNION \
			select Idx,KeyVal,ObjectType,ObjectID,a.Reserve,Name\
			from InfrareFunBindTable as a,sceneTable as b \
			where a.ObjectType=%d and ObjectID = b.SceneID")
	},
//#define MULTIBUS_SCENEDEVALL		5021	//场景下设备＋虚设备＋加同一房间的红外收发节点
	{
		{
			MULTIBUS_SCENEDEVALL,
			0,
			sizeof(FieldsSceneDevAll)/sizeof(FieldsSceneDevAll[0]),
			_T("CCInfoTable"),FieldsSceneDevAll,
			-1,
			-1,
			-1
		},
			_T("select a.DevID,a.SysAddr,a.DevAddr,a.DevType,a.Value,a.IsOnLine,a.SysStatus, \
			c.DevID,c.SysAddr,c.DevAddr,c.DevType,c.Value,c.IsOnLine,c.SysStatus,\
			d.DevID,d.SysAddr,d.DevAddr,d.DevType,d.Value,d.IsOnLine,d.SysStatus,b.oper,b.value \
			from Devtable as a inner join scenedevtable as b on b.sceneid=1 and b.DevID =a.devid \
			left join Devtable as c on a.DevType>999 and a.realdevid=c.devid \
			left join Devtable as d on a.DevType>1000  and  c.Houseid=d.HouseID and d.DevType=14")
	},
//ACTION_ROOMIR_QUERYBYADDR			21//根据系统地址、设备地址查找同一房间下的房间及红外设备MULTIBUS_ROOM_IR		5022	//房间及红外设备，结构Dev_Room_IR
	{
		{
			ACTION_ROOMIR_QUERYBYADDR,
			0,
			sizeof(FieldsRoomIr)/sizeof(FieldsRoomIr[0]),
			_T("CCInfoTable"),FieldsRoomIr,
			-1,
			-1,
			-1
		},
		_T("")
	},
//ACTION_ROOM_GUARDLOCK_QUERYBYADDR	21//根据系统地址设备地址查找 MULTIBUS_ROOM_GUARDLOCK		5023	//门禁房间及门磁码 Dev_Room_GuardLock
	{
		{
			MULTIBUS_ROOM_GUARDLOCK,
			0,
			sizeof(FieldsRoomGuardLock)/sizeof(FieldsRoomGuardLock[0]),
			_T("CCInfoTable"),FieldsRoomGuardLock,
			-1,
			-1,
			-1
		},
		_T("")
	},
//ACTION_QUERYBYLOCKADDR 21//根据门磁设备系统、设备地址查询绑定的门禁信息
	{
		{
			MULTIBUS_DOORKEY_GUARDLOCK,
			0,
			sizeof(FieldsDoorKeyGuardLock)/sizeof(FieldsDoorKeyGuardLock[0]),
			_T("CCInfoTable"),FieldsDoorKeyGuardLock,
			-1,
			-1,
			-1
		},
		_T("")
	},
};
#endif