dbaccessunit.cpp

指纹门禁机管理系统

//---------------------------------------------------------------------------

int TDBAccess::ClearTemplateInfo()
{
  TADOQuery* pExeQuery = dmDatabase->adsExecute;
  pExeQuery->Close();
  pExeQuery->SQL->Text = "delete from EmptTemplate";
  try
  {
    pExeQuery->ExecSQL();
  }
  catch(...)
  {
    return DBAccess_E;
  }
  return OK;
}
//---------------------------------------------------------------------------

int TDBAccess::GetTemplateInfoList(int iUserID,TemplateInfoArray_T &oTemplateInfoList)//得到指纹数据列表
{
   TADOQuery* pQuery = dmDatabase->adsOp;
   pQuery->SQL->Text = "Select * from EmptTemplate where EmpID="+AnsiString(iUserID);
   pQuery->Close();
   try
   {
     pQuery->Open();
   }
   catch(...)
   {
     return  DBAccess_E;
   }

   TemplateInfo_T aTemplateInfo;
   AnsiString lStrName;
   oTemplateInfoList.clear();
   for(pQuery->First();!pQuery->Eof;pQuery->Next())
   {
     memset(&aTemplateInfo,0,sizeof(aTemplateInfo));

     aTemplateInfo.EmpID = pQuery->FieldByName("EmpID")->AsInteger;
     aTemplateInfo.FingerType = pQuery->FieldByName("FingerType")->AsInteger;
     lStrName = pQuery->FieldByName("FingerTemplate")->AsString;
     int lTemplateSize;
     DecodeData(lStrName,aTemplateInfo.FingerTemplate,&lTemplateSize);

     oTemplateInfoList.push_back(aTemplateInfo);
   }
   return OK;
}
//---------------------------------------------------------------------------
int  TDBAccess::GetUserInfo_T(int iUserID,UserInfo_T& oUserInfo)
{
   TADOQuery* pQuery = dmDatabase->adsOp;
   AnsiString lSQL = "Select * from Employee_T where EmpID="+AnsiString(iUserID);
   pQuery->Close();
   pQuery->SQL->Text = lSQL;
   try
   {
     pQuery->Open();
   }
   catch(...)
   {
     return  DBAccess_E;
   }
   if (!pQuery->IsEmpty())
   {
     oUserInfo.UserID = pQuery->FieldByName("EmpID")->AsInteger;

     AnsiString lStrName = pQuery->FieldByName("EmpName")->AsString;
     strcpy(oUserInfo.EmpName,lStrName.c_str());

     oUserInfo.ManageClass = pQuery->FieldByName("ManageClass")->AsInteger;
     oUserInfo.DeptID = pQuery->FieldByName("DeptID")->AsInteger;
     oUserInfo.AttendPlan = pQuery->FieldByName("AttendPlan")->AsInteger;
     oUserInfo.VerifyMode = pQuery->FieldByName("VerifyMode")->AsInteger;
     oUserInfo.FingerCount = pQuery->FieldByName("FingerCount")->AsInteger;
     oUserInfo.IsUsePwd = pQuery->FieldByName("IsUsePsw")->AsInteger;

     lStrName = pQuery->FieldByName("EmpPwd")->AsString;
     strcpy(oUserInfo.EmpPwd,lStrName.c_str());

     oUserInfo.Flag = pQuery->FieldByName("Flag")->AsInteger;

     lStrName = pQuery->FieldByName("EmpDesc")->AsString;
     strcpy(oUserInfo.EmpDesc,lStrName.c_str());

     return OK;
   }
   return DBIsEmpty_E;
}
//---------------------------------------------------------------------------

int TDBAccess::InsertLog(LogInfoBase_T iLogData)
{
   //新增日志记录
   TADOQuery* pExeQuery = dmDatabase->adsExecute;
   pExeQuery->Close();
   pExeQuery->SQL->Text = "insert into EventLog(DeviceLogID,DeviceID,MainEmpID,ObjEmpID,EventTime,EventType,Flag) Values(:DeviceLogID,:DeviceID,:MainEmpID,:ObjEmpID,:EventTime,:EventType,:Flag)";
   pExeQuery->Parameters->ParamByName("DeviceLogID")->Value = iLogData.DeviceLogID;
   pExeQuery->Parameters->ParamByName("DeviceID")->Value = iLogData.DeviceID;
   pExeQuery->Parameters->ParamByName("MainEmpID")->Value = iLogData.MainEmpID;
   pExeQuery->Parameters->ParamByName("ObjEmpID")->Value = iLogData.ObjEmpID;
   pExeQuery->Parameters->ParamByName("EventTime")->Value = SystemTimeToDateTime(iLogData.EventTime);
   pExeQuery->Parameters->ParamByName("EventType")->Value = iLogData.OpType;
   pExeQuery->Parameters->ParamByName("Flag")->Value = iLogData.Flag;

   try
   {
     pExeQuery->ExecSQL();
   }
   catch(...)
   {
     return DBAccess_E;
   }
   return OK;
}
//---------------------------------------------------------------------------
int TDBAccess::ClearEventLog()
{
   int lLogType = etNormalEvent;
   AnsiString lSQL = "delete from EventLog where EventType="+AnsiString(lLogType);
   return ExecuteSQL(lSQL);
}
//---------------------------------------------------------------------------
int TDBAccess::ClearEventLog(int iDeviceID)
{
   int lLogType = etNormalEvent;
   AnsiString lSQL = "delete from EventLog where DeviceID=" + AnsiString(iDeviceID)+ " and EventType="+AnsiString(lLogType);
   return ExecuteSQL(lSQL);
}
//---------------------------------------------------------------------------

int TDBAccess::ClearOperateLog()
{
   int lLogType = etNormalEvent;
   AnsiString lSQL = "delete from EventLog where EventType <> "+AnsiString(lLogType);
   return ExecuteSQL(lSQL);
}
//---------------------------------------------------------------------------
int TDBAccess::ClearOperateLog(int iDeviceID)
{
   //清除所有该设备的日志
   int lLogType = etNormalEvent;
   AnsiString lSQL = "delete from OperateLog where DeviceID=" + AnsiString(iDeviceID) + " and EventType <> "+AnsiString(lLogType);
   return ExecuteSQL(lSQL);
}
//---------------------------------------------------------------------------
int TDBAccess::GetMaxLogID(int iDeviceID,int& iLogID)
{
   return OK;
}
//---------------------------------------------------------------------------
bool TDBAccess::IsExistLog(int iDeviceID,int iLogID)//判断是否存在这条日志
{
   TADOQuery* pQuery = dmDatabase->adsOp;
   pQuery->Close();
   pQuery->SQL->Text = "Select * from EventLog where DeviceID="+AnsiString(iDeviceID)+" and DeviceLogID="+AnsiString(iLogID);
   pQuery->Open();
   if(pQuery->RecordCount > 0)
     return true;
   return false;
}
//---------------------------------------------------------------------------

int TDBAccess::InsertRoleData(EnguardRole_T iRole)
{
   //
  AnsiString lRoleString;
  EncodeData((BYTE*)&iRole,sizeof(EnguardRole_T),lRoleString);

  TADOQuery* pExeQuery = dmDatabase->adsExecute;
  pExeQuery->Close();
  pExeQuery->SQL->Text = "insert into GuardRole(RoleID,RoleData,Flag)Values(:RoleID,:RoleData,:Flag)";
  pExeQuery->Parameters->ParamByName("RoleID")->Value = 1;
  pExeQuery->Parameters->ParamByName("RoleData")->Value = lRoleString;
  pExeQuery->Parameters->ParamByName("Flag")->Value = 1;
  try
  {
    pExeQuery->ExecSQL();
  }
  catch(...)
  {
    return DBAccess_E;
  }
  return OK;
}
//---------------------------------------------------------------------------
int TDBAccess::ReadRoleData(EnguardRole_T* oRole)
{
  TADOQuery* pQuery = dmDatabase->adsOp;
  pQuery->Close();
  pQuery->SQL->Text = "Select * from GuardRole where RoleID=1";
  try
  {
    pQuery->Open();
  }
  catch(...)
  {
    return DBAccess_E;
  }
  if (!pQuery->IsEmpty())
  {
     AnsiString lRoleString = pQuery->FieldByName("RoleData")->AsString;
     int lRoleSize;
     DecodeData(lRoleString,(BYTE*)oRole,&lRoleSize);
     return OK;
  }
  return DBIsEmpty_E;
}
//---------------------------------------------------------------------------
int TDBAccess::UpdateRoleData(EnguardRole_T iRole)
{
  AnsiString lRoleString;
  EncodeData((BYTE*)&iRole,sizeof(EnguardRole_T),lRoleString);

  TADOQuery* pExeQuery = dmDatabase->adsExecute;
  pExeQuery->Close();
  pExeQuery->SQL->Text = "update GuardRole set RoleData=:RoleData,Flag=:Flag where RoleID=1";
  pExeQuery->Parameters->ParamByName("RoleData")->Value = lRoleString;
  pExeQuery->Parameters->ParamByName("Flag")->Value = 1;
  try
  {
    pExeQuery->ExecSQL();
  }
  catch(...)
  {
    return DBAccess_E;
  }
  return OK;
}
//---------------------------------------------------------------------------
bool TDBAccess::IsExistRoleData()
{
  TADOQuery* pQuery = dmDatabase->adsOp;
  pQuery->Close();
  pQuery->SQL->Text = "Select * from GuardRole where RoleID=1";
  try
  {
    pQuery->Open();
  }
  catch(...)
  {
    return false;
  }
  return !pQuery->IsEmpty();

}
//---------------------------------------------------------------------------
bool TDBAccess::EncodeData(BYTE* iInData,int iInDataSize,AnsiString& oString)
{
   //对数据进行编码
   TMemoryStream* msToEncode = new TMemoryStream();
   msToEncode->Write(iInData,iInDataSize);
   msToEncode->Position = 0;
   TStringStream *msEncoded = new TStringStream("");
   EncodeStream(msToEncode,msEncoded);

   oString = msEncoded->DataString;
   delete msToEncode;
   delete msEncoded;
   return true;
}
//---------------------------------------------------------------------------
bool TDBAccess::DecodeData(AnsiString iToString,BYTE* oOutData,int* oOutDataSize)
{
   TStringStream *msDecoded = new TStringStream("");
   msDecoded->Write(iToString.c_str(),iToString.Length());
   msDecoded->Position = 0;

   TMemoryStream* msToDecode = new TMemoryStream();

   DecodeStream(msDecoded,msToDecode);

   *oOutDataSize = msToDecode->Size;
   msToDecode->Position = 0;
   msToDecode->Read(oOutData,msToDecode->Size);
   delete msToDecode;
   delete msDecoded;
   return true;

}
//---------------------------------------------------------------------------
int TDBAccess::ClearUserInfo_T()
{
  AnsiString lSQL = "delete from Employee_T";
  return ExecuteSQL(lSQL);
}
//---------------------------------------------------------------------------
int TDBAccess::InsertUserInfo_T(UserInfo_T iUserInfo)
{
  TADOQuery* pExeQuery = dmDatabase->adsExecute;
  pExeQuery->Close();
  pExeQuery->SQL->Text = "insert into Employee_T(EmpID,EmpName,DeptID,ManageClass,AttendPlan,VerifyMode,FingerCount,IsUsePsw,EmpPwd,EmpDesc,Flag) Values(:EmpID,:EmpName,:DeptID,:ManageClass,:AttendPlan,:VerifyMode,:FingerCount,:IsUsePsw,:EmpPwd,:EmpDesc,:Flag)";
  try
  {
    pExeQuery->Parameters->ParamByName("EmpID")->Value = iUserInfo.UserID;
    AnsiString lEmpName = iUserInfo.EmpName;
    lEmpName = lEmpName.SubString(1,14);//有时会出现15个字节，c++ builder的bug
    pExeQuery->Parameters->ParamByName("EmpName")->Value = lEmpName.Trim();
    if(!IsDeptIDExist(iUserInfo.DeptID))
    {
       DeptInfo_T lFixedDeptInfo;
       GetFixedDeptID(&lFixedDeptInfo);
       iUserInfo.DeptID = lFixedDeptInfo.DeptID; 
    }
    pExeQuery->Parameters->ParamByName("DeptID")->Value = iUserInfo.DeptID;
    pExeQuery->Parameters->ParamByName("ManageClass")->Value = (int)iUserInfo.ManageClass;
    pExeQuery->Parameters->ParamByName("AttendPlan")->Value = (int)iUserInfo.AttendPlan;
    pExeQuery->Parameters->ParamByName("VerifyMode")->Value = (int)iUserInfo.VerifyMode;
    pExeQuery->Parameters->ParamByName("FingerCount")->Value = (int)iUserInfo.FingerCount;
    pExeQuery->Parameters->ParamByName("IsUsePsw")->Value = (int)iUserInfo.IsUsePwd;
    pExeQuery->Parameters->ParamByName("EmpPwd")->Value = AnsiString(iUserInfo.EmpPwd).Trim();
    pExeQuery->Parameters->ParamByName("EmpDesc")->Value = "";
    pExeQuery->Parameters->ParamByName("Flag")->Value = wsInService;
    pExeQuery->ExecSQL();
  }
  catch(...)
  {
    return DBAccess_E;
  }
  return OK;
}
//---------------------------------------------------------------------------

int TDBAccess::InsertTemplateInfo_T(TemplateInfo_T iTemplateInfo)
{
  //如果上传上来的模板的最后四位是0，0，0，1则是防胁迫指纹
  char lCmp[4]={0x00,0x00,0x00,0x01};
  if(memcmp(iTemplateInfo.FingerTemplate+252,lCmp,4) == 0)
     iTemplateInfo.FingerType = 1;
  else
     iTemplateInfo.FingerType = 0;

  TADOQuery* pExeQuery = dmDatabase->adsExecute;
  AnsiString lTemplateStr;
  EncodeData(iTemplateInfo.FingerTemplate,256,lTemplateStr);

  pExeQuery->Close();
  pExeQuery->SQL->Text = "insert into EmptTemplate_T(EmpID,FingerIndex,FingerType,FingerTemplate,Flag) Values(:EmpID,:FingerIndex,:FingerType,:FingerTemplate,:Flag)";
  pExeQuery->Parameters->ParamByName("EmpID")->Value = iTemplateInfo.EmpID;
  pExeQuery->Parameters->ParamByName("FingerIndex")->Value = 0;//GetTemplateCount(iTemplateInfo.EmpID)+1;
  pExeQuery->Parameters->ParamByName("FingerType")->Value = iTemplateInfo.FingerType;
  pExeQuery->Parameters->ParamByName("FingerTemplate")->Value = lTemplateStr;
  pExeQuery->Parameters->ParamByName("Flag")->Value = 0;//iTemplateInfo.Flag;
  try
  {
    pExeQuery->ExecSQL();
  }
  catch(...)
  {
    return DBAccess_E;
  }
  return OK;
}
//---------------------------------------------------------------------------
int TDBAccess::ClearTemplateInfo_T()
{
  TADOQuery* pExeQuery = dmDatabase->adsExecute;
  pExeQuery->Close();
  pExeQuery->SQL->Text = "delete from EmptTemplate_T";
  try
  {
    pExeQuery->ExecSQL();
  }
  catch(...)
  {
    return DBAccess_E;
  }
  return OK;
}
//---------------------------------------------------------------------------

int TDBAccess::GetTemplateInfoList_T(int iUserID,TemplateInfoArray_T &oTemplateInfoList)//得到指纹数据列表
{
   TADOQuery* pQuery = dmDatabase->adsOp;
   pQuery->SQL->Text = "Select * from EmptTemplate_T where EmpID="+AnsiString(iUserID);
   pQuery->Close();
   try
   {
     pQuery->Open();
   }
   catch(...)
   {
     return  DBAccess_E;
   }

   TemplateInfo_T aTemplateInfo;
   AnsiString lStrName;
   oTemplateInfoList.clear();
   for(pQuery->First();!pQuery->Eof;pQuery->Next())
   {
     memset(&aTemplateInfo,0,sizeof(aTemplateInfo));

     aTemplateInfo.EmpID = pQuery->FieldByName("EmpID")->AsInteger;
     aTemplateInfo.FingerType = pQuery->FieldByName("FingerType")->AsInteger;
     lStrName = pQuery->FieldByName("FingerTemplate")->AsString;
     int lTemplateSize;
     DecodeData(lStrName,aTemplateInfo.FingerTemplate,&lTemplateSize);

     oTemplateInfoList.push_back(aTemplateInfo);
   }
   return OK;
}
//---------------------------------------------------------------------------

int TDBAccess::GetTemplateCount_T(int iUserID)//获取员工指纹数
{
  TADOQuery* pQuery = dmDatabase->adsOp;
  pQuery->Close();
  pQuery->SQL->Text = "Select * from EmptTemplate_T where EmpID=:EmpID";
  pQuery->Parameters->ParamByName("EmpID")->Value = iUserID;
  try
  {
    pQuery->Open();
  }
  catch(...)
  {
    return -1;
  }
  return pQuery->RecordCount;
}
//---------------------------------------------------------------------------

int TDBAccess::UpdateTemplateCount_T(int iUserID,int iFingerCount)//修改员工指纹数
{
  TADOQuery* pExeQuery = dmDatabase->adsExecute;
  pExeQuery->Close();
  pExeQuery->SQL->Text = "Update Employee_T set FingerCount=:FingerCount where EmpID=:EmpID";
  try
  {
    pExeQuery->Parameters->ParamByName("FingerCount")->Value = iFingerCount;
    pExeQuery->Parameters->ParamByName("EmpID")->Value = iUserID;
    pExeQuery->ExecSQL();
  }
  catch(...)
  {
    return DBAccess_E;
  }
  return OK;
}
//---------------------------------------------------------------------------

#pragma package(smart_init)