ccdb.cpp

wince下对sqlserver数据库的操作,增删改操作,单进程单线程

							if(nRet && i <num && bSyn)
*/							if(nRet  && bSyn)
							{
#ifndef _USEBYLHL
								DLLSynFuntInterfaceBug(pstrReqParam->nBusID,ACTION_MODIFY,(char*)pstrReqParam->pBuf,sizeof(int));
#endif
							}
							if(nRet )
								return ERR_BUS_SUCCESS;
							else
								return ERR_BUS_SQLEXE;
					}else if(ACTION_DEV_UPDATE_STATUS==pstrReqParam->nMethod )
					{
						if(pstrReqParam->nSize != sizeof(short)*3)
						{
							return ERR_BUS_PARAMSIZE;
						}
						swprintf(cwSql,L"Update DevTable  set Value=%d \
							where (SysAddr=%d and DevAddr=%d) or \
							DevID in (select virtual.DevId from DevTable as real,DevTable as virtual where real.SysAddr=%d and real.DevAddr=%d and real.DevID = virtual.RealDevID)",
							*(((short*)pstrReqParam->pBuf)+2),*(short*)pstrReqParam->pBuf,*(((short*)pstrReqParam->pBuf)+1),
							*(short*)pstrReqParam->pBuf,*(((short*)pstrReqParam->pBuf)+1));
//						wprintf(_T("----------------------sql=%s\n"),cwSql);	
	/*					if(!g_DataBase.OpenData())
							{
								return ERR_PROG_NOTOPEN;
							}
							nRet=g_DataBase.ExecuteSql(cwSql);
							g_DataBase.CloseData();
	*/
						//修改设备值列表
					//	if(BUSINESS_DEV==pstrReqParam->nBusID)
						{
							g_DevValueMap.SetAt(*((int*)pstrReqParam->pBuf ),*(unsigned short *)((char *)pstrReqParam->pBuf +4));
						}


							if(bSyn)
							{
#ifndef _USEBYLHL
								struct devce_state_info DevInfo;
								memset(&DevInfo,0,sizeof(struct devce_state_info));
								DevInfo.shSysAddr = *(short*)pstrReqParam->pBuf;
								DevInfo.shDevAddr =*(((short*)pstrReqParam->pBuf)+1);
								DevInfo.stateType=1;
								DevInfo.nCurrentState=*(((short*)pstrReqParam->pBuf)+2);
								DLLSynFuntInterfaceBug(pstrReqParam->nBusID,SYS_ACTION_DEV_STATUS,(char*)pstrReqParam->pBuf,sizeof(short)*2,
									(char*)&DevInfo,sizeof(struct devce_state_info));
#endif
							}
								return ERR_BUS_SUCCESS;
			//				else
			//					return ERR_BUS_SQLEXE;
					}else if(ACTION_DEV_QUERBYTYPEANDHOUSE==pstrReqParam->nMethod)
					{//根据类型和房间查询设备
						if(pstrReqParam->nSize != sizeof(short) *3)
						{
							return ERR_BUS_PARAMSIZE;
						}
						swprintf(cwSql,L"select * from %s where houseid =%d and DevType=%d",
							BusList[busindex].Name,
							*(int*)pstrReqParam->pBuf,*(((short*)pstrReqParam->pBuf)+2));

					}else if(ACTION_DEV_UPDATEOPERSOURCE==pstrReqParam->nMethod)
					{//根据设备ID更改操作源
						if(pstrReqParam->nSize != sizeof(int)*2)
						{
							return ERR_BUS_PARAMSIZE;
						}
						swprintf(cwSql,L"Update DevTable  set OperSource=%d \
							where DevID=%d",
							*(((int*)pstrReqParam->pBuf)+1),*(int*)pstrReqParam->pBuf);
						if(!g_DataBase.OpenData())
							{
								return ERR_PROG_NOTOPEN;
							}
							nRet=g_DataBase.ExecuteSql(cwSql);
							g_DataBase.CloseData();
							if(bSyn)
							{//通知说设备记录修改了
#ifndef _USEBYLHL
								DLLSynFuntInterfaceBug(pstrReqParam->nBusID,ACTION_MODIFY,(char*)pstrReqParam->pBuf,sizeof(int));
#endif
							}
								return ERR_BUS_SUCCESS;
					}else
					{
						ASSERT(0);
						return ERR_BUS_MOTHEDNONE;
					}
				}
				break;
			case	BUSINESS_SCENE:	          //	场景表
				{
					if(ACTION_SCENE_QUERBYTYPEANDHOUSE==pstrReqParam->nMethod)
					{
						if(pstrReqParam->nSize != sizeof(int) +1)
						{
							return ERR_BUS_PARAMSIZE;
						}
						swprintf(cwSql,L"select * from %s where houseid =%d and Type=%d",
							BusList[busindex].Name,
							*(int*)pstrReqParam->pBuf,*(((char*)pstrReqParam->pBuf)+4));
					}else
					{
						ASSERT(0);
						return ERR_BUS_MOTHEDNONE;
					}
				}break;
			case BUSINESS_PASSRECORD:
				{//根据门禁ID查询进出记录的最后一条
					if(ACTION_PASSRECORD_LASTTIME==pstrReqParam->nMethod)
					{
						if(pstrReqParam->nSize != sizeof(int))
						{
							return ERR_BUS_PARAMSIZE;
						}
			//			swprintf(cwSql,L"select * from passrecord where Idx in (select Max(Idx) from PassRecord where GuardID=%d)",*(int*)pstrReqParam->pBuf);
						swprintf(cwSql,L"select top 1 * from passrecord where GuardID in (select GuardID from Devtable,GuardLocktable \
						where SysAddr=%d and DevAddr=%d and DevID=LockID) order by time Desc",
						*(short*)pstrReqParam->pBuf,*(((short*)pstrReqParam->pBuf)+1));
					}else if(ACTION_PASSRECORD_QUERYBYTIME ==pstrReqParam->nMethod)
					{//根据起始时间和结束时间查询进出记录，time_t
						if(pstrReqParam->nSize != sizeof(time_t)*2 + CC_MEMB_NAME_LEN)
						{
							return ERR_BUS_PARAMSIZE;
						}
						char *pUser=(char*)pstrReqParam->pBuf +sizeof(time_t)*2;
						if(strlen(pUser)<1)
						swprintf(cwSql,L"select * from passrecord where time >= %d and time <= %d order by time Desc",
							*((int*)pstrReqParam->pBuf),*((int*)pstrReqParam->pBuf +1));
						else
						{
						WCHAR cwTemp[CC_MEMB_NAME_LEN+1]=L"";
						MultiByteToWideChar(CP_ACP,MB_PRECOMPOSED,(char*)pUser,CC_MEMB_NAME_LEN,cwTemp,CC_MEMB_NAME_LEN);
						ReplaceYinhao(cwTemp);
						swprintf(cwSql,L"select * from passrecord where time >= %d and time <= %d and UserName ='%s' \
							order by time Desc",
							*((int*)pstrReqParam->pBuf),*((int*)pstrReqParam->pBuf +1),cwTemp);
						}
					}else
					{
						ASSERT(0);
						return ERR_BUS_MOTHEDNONE;
					}		
				}
				break;
			case BUSINESS_INFRAREFUNBIND:
				{
					if( ACTION_INFRAREFUNBIND_QUERYBYKEYVAL==pstrReqParam->nMethod)
					{//根据遥控器按键查询绑定记录
						if(pstrReqParam->nSize != sizeof(int))
						{
							return ERR_BUS_PARAMSIZE;
						}
						swprintf(cwSql,L"select * from InfrareFunBindTable where KeyVal=%d",*(int*)pstrReqParam->pBuf);
					}else
					{
						ASSERT(0);
						return ERR_BUS_MOTHEDNONE;
					}
				}
				break;
			case BUSINESS_CARDTIMER:
				{
					if(ACTION_CARTTIME_QUERYBYCARDNUM==pstrReqParam->nMethod)
					{//根据卡号查询卡的有效时间
						if(pstrReqParam->nSize != CC_CARD_NUM_LEN)
						{
							return ERR_BUS_PARAMSIZE;
						}
						WCHAR cwTemp[CC_CARD_NUM_LEN+1]=L"";
						MultiByteToWideChar(CP_ACP,MB_PRECOMPOSED,(char*)pstrReqParam->pBuf,CC_CARD_NUM_LEN,cwTemp,CC_CARD_NUM_LEN);
						ReplaceYinhao(cwTemp);
						swprintf(cwSql,L"select * from cardtimer where cardid in (select cardid from cardtable where Number='%s');",cwTemp);
					}else
					{
						ASSERT(0);
						return ERR_BUS_MOTHEDNONE;
					}

				}
				break;
			case BUSINESS_ALARMSPRING:
				{
					if(ACTION_ALARMSPRING_QUERYBYTIME==pstrReqParam->nMethod)
					{//根据起始时间、结束时间查询报警、报警类型查询报警记录				
						if(pstrReqParam->nSize != sizeof(int)*2 + sizeof(BYTE))
						{
							return ERR_BUS_PARAMSIZE;
						}
						WCHAR cwTemp[100]=L"";
						if(*((BYTE*)pstrReqParam->pBuf+sizeof(int)*2) == ALARMSPRING_ALL)
							;
						else
							swprintf(cwTemp,L"and IsCurAlarm = %d",*((BYTE*)pstrReqParam->pBuf+sizeof(int)*2));
						swprintf(cwSql,L"select * from AlarmSpringTable where time >=%d and time <= %d %s",
						*((int*)pstrReqParam->pBuf),*((int*)pstrReqParam->pBuf +1),cwTemp);
					}else if(ACTION_ALARMSPRING_BEHISTORY==pstrReqParam->nMethod)
					{
						swprintf(cwSql,L"Update AlarmSpringTable set IsCurAlarm =%d ",
							ALARMSPRING_HIS);
							if(!g_DataBase.OpenData())
							{
								return ERR_PROG_NOTOPEN;
							}
							nRet=g_DataBase.ExecuteSql(cwSql);
							g_DataBase.CloseData();
							if(nRet)
								return ERR_BUS_SUCCESS;
							else
								return ERR_BUS_SQLEXE;
					}else
					{
						ASSERT(0);
						return ERR_BUS_MOTHEDNONE;
					}
				}
				break;
			case BUSINESS_OUTGOING:
				{
					if(ACTION_OUTGOING_QUERYBYTIME==pstrReqParam->nMethod)
					{//根据起始时间，结束时间，开支类型、开支项目（个数不定）查询开支记录,				
						if(pstrReqParam->nSize < sizeof(int)*2+sizeof(BYTE))
						{
							return ERR_BUS_PARAMSIZE;
						}
						if(*((unsigned char*)pstrReqParam->pBuf +sizeof(int)*2) ==255)
						{
							swprintf(cwSql,L"select * from OutgoingRecordTable where DoTime >=%d and DoTime <= %d ",
							*((int*)pstrReqParam->pBuf),
							*((int*)pstrReqParam->pBuf +1));
						}else
						swprintf(cwSql,L"select * from OutgoingRecordTable where DoTime >=%d and DoTime <= %d and InOut =%d",
						*((int*)pstrReqParam->pBuf),
						*((int*)pstrReqParam->pBuf +1),
						*((char*)pstrReqParam->pBuf +sizeof(int)*2));
						//开支项
						WCHAR temp[10];
						wcscpy(cwTemp,L"");
						for(int i=sizeof(int)*2 + sizeof(BYTE);i<pstrReqParam->nSize;i++)
						{
						//	if(*((char*)pstrReqParam->pBuf+i) ==0)
						//		continue;
							if(wcslen(cwTemp) ==0)
							{
								wcscpy(cwTemp,L" and Type in(");
								swprintf(temp,L"%d",*((char*)pstrReqParam->pBuf+i));
							}else
							{
								swprintf(temp,L",%d",*((char*)pstrReqParam->pBuf+i));
							}
							wcscat(cwTemp,temp);
						}
						if(wcslen(cwTemp)>0)
						{
							wcscat(cwSql,cwTemp);
							wcscat(cwSql,L")");
						}
					}else
					{
						ASSERT(0);
						return ERR_BUS_MOTHEDNONE;
					}
				}
				break;
			case BUSINESS_CARD:
				{
					if(ACTION_CARD_QUERYBYNUM==pstrReqParam->nMethod)
					{//根据卡号查询卡的记录
						if(pstrReqParam->nSize != CC_CARD_NUM_LEN)
						{
							return ERR_BUS_PARAMSIZE;
						}
						WCHAR cwTemp[CC_CARD_NUM_LEN+1]=L"";
						MultiByteToWideChar(CP_ACP,MB_PRECOMPOSED,(char*)pstrReqParam->pBuf,CC_CARD_NUM_LEN,cwTemp,CC_DOOR_MAGCODE_LEN);
						ReplaceYinhao(cwTemp);
						swprintf(cwSql,L"select * from cardtable where Number='%s'",cwTemp);
					}else
					{
						ASSERT(0);
						return ERR_BUS_MOTHEDNONE;
					}
				}
				break;
			case BUSINESS_TIMER:
				{
					if(ACTION_TIMER_DELBYBUSID==pstrReqParam->nMethod)
					{//根据业务类型及业务ID删除定时器
						if(pstrReqParam->nSize != sizeof(DelParamTimer))
						{
							return ERR_BUS_PARAMSIZE;
						}
						DelParamTimer *pParam=(DelParamTimer*)pstrReqParam->pBuf;
						swprintf(cwSql,L"delete from Timertable where BusType=%d and busid=%d",
							pParam->cBusiType,pParam->nBusiId);
							if(!g_DataBase.OpenData())
							{
								return ERR_PROG_NOTOPEN;
							}
							nRet=g_DataBase.ExecuteSql(cwSql);
							g_DataBase.CloseData();
							if(nRet)
								return ERR_BUS_SUCCESS;
							else
								return ERR_BUS_SQLEXE;

					}else if(ACTION_TIMER_CURMINUTE==pstrReqParam->nMethod)
					{//查询当前分钟内的所有定时器
						if(pstrReqParam->nSize >0)
						{
							return ERR_BUS_PARAMSIZE;
						}
						CTime tm1;
						tm1=CTime::GetCurrentTime();
						CTime tm2(tm1.GetYear(),tm1.GetMonth(),tm1.GetDay(),0,0,0);
						CTime tm3(tm1.GetYear(),tm1.GetMonth(),tm1.GetDay(),23,59,0);
						CTime tm4(tm1.GetYear(),1,1,0,0,0);
						CTime tm5(tm1.GetYear(),tm1.GetMonth(),tm1.GetDay(),tm1.GetHour(),tm1.GetMinute(),0);
						CTime tm6=tm5+CTimeSpan(0,0,1,0);
						CTimeSpan tmspan1=tm1-tm4;
						CTimeSpan tmspan2=tm3-tm4;
						int nMin=tm1.GetHour()*60+tm1.GetMinute();
						int nYearMin=(tm1.GetMonth()-1)*31 *1440+ (tm1.GetDay()-1) *1440 + tm1.GetHour()*60 +tm1.GetMinute();
						swprintf(cwSql,L"select * from TimerTable \
						where (TimerTyper=1 and Time>= %d and Time <%d) \
						or (TimerTyper =2 and Time=%d) \
						or (TimerTyper =3 and Time= %d ) \
						or (timerTyper =4 and Time=%d ) \
						or (timerTyper=5 and Time =%d )",
						tm5.GetTime(),tm6.GetTime(), //
						nMin, //每天
						//数据库定义周日为0，而Ctime定义周一为1
						(tm1.GetDayOfWeek()  - 1)*1440 + nMin, //每周
						(tm1.GetDay()-1)*1440+ nMin, //每月
						nYearMin//每年
						);
					}else
					{
						ASSERT(0);
						return ERR_BUS_MOTHEDNONE;
					}
				}
				break;
			case BUSINESS_VOICEMSG:
				{
					if(ACTION_VOICEMSG_QUERY==pstrReqParam->nMethod)
					{//根据时间范围、人、标记组合查询
						if(pstrReqParam->nSize != sizeof(struct voicemsg_query_param))
						{
							return ERR_BUS_PARAMSIZE;
						}
						//设置查询条件
						struct voicemsg_query_param *pParam=(struct voicemsg_query_param *)pstrReqParam->pBuf;
						WCHAR wcWhere[512],wcTemp[100];
						swprintf(wcWhere,L"where time > %d and time <%d",pParam->tFrom,pParam->fTo);
						if(pParam->cFlag<2)
						{
							swprintf(wcTemp,L" and ReadFlag=%d",pParam->cFlag);
							wcscat(wcWhere,wcTemp);
						}
						if(strlen(pParam->szUserName) > 0)
						{
							MultiByteToWideChar(CP_ACP,MB_PRECOMPOSED,pParam->szUserName,VOICEUSER_NAME_LEN,wcTemp,100);
							ReplaceYinhao(wcTemp);
							wcscat(wcWhere,L" and FromName=\'");
							wcscat(wcWhere,wcTemp);
							wcscat(wcWhere,L"\'");
						}
						if(pParam->nSendUserID >0)
						{
							swprintf(wcTemp,L" and FromID =%d",pParam->nSendUserID);
							wcscat(wcWhere,wcTemp);
						}
						if(pParam->nRecvUserID >0)
						{
							swprintf(wcTemp,L"and ToID = %d",pParam->nRecvUserID);
							wcscat(wcWhere,wcTemp);
						}
						wcscat(wcWhere,L" order by time desc");
						
						if(!g_DataBase.OpenData())
						{
							return ERR_PROG_NOTOPEN;
						}
						int nID=g_DataBase.GetRows(BusList+busindex,wcWhere,pOut);
						g_DataBase.CloseData();
						return nID;
					}else if(ACTION_VOICEMSG_UPDATEFALAG==pstrReqParam->nMethod)
					{
						if(pstrReqParam->nSize !=  5)
						{
							return ERR_BUS_PARAMSIZE;
						}
						swprintf(cwSql,L"Update VoiceMsgTable set ReadFlag= %d where Idx=%d",
							*((char*)pstrReqParam->pBuf+4),*(int*)(pstrReqParam->pBuf));
							if(!g_DataBas