📄 dbaccessunit.cpp

📁 指纹门禁机管理系统
💻 CPP
📖 第 1 页 / 共 3 页
字号:
   TADOQuery* pQuery = dmDatabase->adsOp;
   pQuery->Close();
   pQuery->SQL->Text = "Select * from Dept where DeptID="+AnsiString(iDeptID);
   pQuery->Open();
   if(pQuery->RecordCount > 0)
     return true;
   return false;
}
//---------------------------------------------------------------------------

int TDBAccess::GetDeptUserCount(int iDeptID)
{
   TADOQuery* pQuery = dmDatabase->adsOp;
   pQuery->Close();
   pQuery->SQL->Text = "Select * from Employee where DeptID="+AnsiString(iDeptID);
   pQuery->Open();
   return pQuery->RecordCount;
}
//---------------------------------------------------------------------------

int TDBAccess::GetUserMaxID()//获取员工最大ID号
{
   TADOQuery* pQuery = dmDatabase->adsOp;
   pQuery->Close();
   pQuery->SQL->Text = "Select max(EmpID) from Employee";
   pQuery->Open();
   int lMaxID = pQuery->Fields->Fields[0]->AsInteger;
   return lMaxID;
}
//---------------------------------------------------------------------------

bool TDBAccess::IsUserIDExist(int iUserID)//判断员工ID号是否存在
{
   TADOQuery* pQuery = dmDatabase->adsOp;
   pQuery->Close();
   pQuery->SQL->Text = "Select * from Employee where EmpID="+AnsiString(iUserID);
   pQuery->Open();
   if(pQuery->RecordCount > 0)
     return true;
   return false;
}
//---------------------------------------------------------------------------

int TDBAccess::InsertUserInfo(UserInfo_T iUserInfo)
{
  TADOQuery* pExeQuery = dmDatabase->adsExecute;
  pExeQuery->Close();
  pExeQuery->SQL->Text = "insert into Employee(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);
    pExeQuery->Parameters->ParamByName("EmpName")->Value = lEmpName.Trim();
    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 = AnsiString(iUserInfo.EmpDesc);
    pExeQuery->Parameters->ParamByName("Flag")->Value = iUserInfo.Flag;
    pExeQuery->ExecSQL();
  }
  catch(...)
  {
    return DBAccess_E;
  }
  return OK;
}
//---------------------------------------------------------------------------
int TDBAccess::UpdateUserInfo(int UserID,UserInfo_T iUserInfo)
{
  TADOQuery* pExeQuery = dmDatabase->adsExecute;
  pExeQuery->Close();
  pExeQuery->SQL->Text = "Update Employee set EmpName=:EmpName,DeptID=:DeptID,ManageClass=:ManageClass,AttendPlan=:AttendPlan,VerifyMode=:VerifyMode,FingerCount=:FingerCount,IsUsePsw=:IsUsePsw,EmpPwd=:EmpPwd,EmpDesc=:EmpDesc,Flag=:Flag where EmpID=:EmpID";
  try
  {
    AnsiString lEmpName = iUserInfo.EmpName;
    lEmpName = lEmpName.SubString(1,14);
    pExeQuery->Parameters->ParamByName("EmpName")->Value = lEmpName.Trim();
    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 = iUserInfo.IsUsePwd;
    pExeQuery->Parameters->ParamByName("EmpPwd")->Value = AnsiString(iUserInfo.EmpPwd);
    pExeQuery->Parameters->ParamByName("EmpDesc")->Value = AnsiString(iUserInfo.EmpDesc);
    pExeQuery->Parameters->ParamByName("Flag")->Value = AnsiString(iUserInfo.Flag);
    pExeQuery->Parameters->ParamByName("EmpID")->Value = UserID;
    pExeQuery->ExecSQL();
  }
  catch(...)
  {
    return DBAccess_E;
  }
  return OK;
}
//---------------------------------------------------------------------------
int TDBAccess::DeleteUserInfo(int iUserID)
{
  AnsiString lSQL = "delete from Employee where EmpID="+AnsiString(iUserID);
  ExecuteSQL(lSQL);

  //同时删除指纹数据
  return DeleteTemplateOneUser(iUserID);//删除一个员工的所有指纹
}
//---------------------------------------------------------------------------
int TDBAccess::ClearUserInfo()
{
  AnsiString lSQL = "delete from Employee";
  return ExecuteSQL(lSQL);
}
//---------------------------------------------------------------------------
int TDBAccess::GetUserList(int iDeptID,UserInfoExArray_T& oUserList)
{
   TADOQuery* pQuery = dmDatabase->adsOp;
   AnsiString lSQL = "Select * from Employee e,Dept d where e.DeptID=d.DeptID";
   if (iDeptID != 0)  //0为全体员工
   {
      lSQL = lSQL + " and e.DeptID= "+AnsiString(iDeptID);
   }

   pQuery->Close();
   pQuery->SQL->Text = lSQL;
   try
   {
     pQuery->Open();
   }
   catch(...)
   {
     return  DBAccess_E;
   }
   AnsiString lStrName;
   oUserList.clear();
   for(pQuery->First();!pQuery->Eof;pQuery->Next())
   {
     UserInfoEx_T aUserInfo;
     aUserInfo.BaseUserInfo.UserID = pQuery->FieldByName("EmpID")->AsInteger;

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

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

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

     aUserInfo.BaseUserInfo.Flag = pQuery->FieldByName("e.Flag")->AsInteger;

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

     lStrName = pQuery->FieldByName("DeptName")->AsString;
     strcpy(aUserInfo.DeptName,lStrName.c_str());

     oUserList.push_back(aUserInfo);
   }
   return OK;

}
//---------------------------------------------------------------------------
int  TDBAccess::GetUserInfo(int iUserID,UserInfoEx_T& oUserInfo)
{
   TADOQuery* pQuery = dmDatabase->adsOp;
   AnsiString lSQL = "Select * from Employee e,Dept d where e.DeptID=d.DeptID and EmpID="+AnsiString(iUserID);
   pQuery->Close();
   pQuery->SQL->Text = lSQL;
   try
   {
     pQuery->Open();
   }
   catch(...)
   {
     return  DBAccess_E;
   }
   if (!pQuery->IsEmpty())
   {
     oUserInfo.BaseUserInfo.UserID = pQuery->FieldByName("EmpID")->AsInteger;

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

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

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

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

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

     lStrName = pQuery->FieldByName("DeptName")->AsString;
     strcpy(oUserInfo.DeptName,lStrName.c_str());
     return OK;
   }
   return DBIsEmpty_E;
}
//---------------------------------------------------------------------------
int  TDBAccess::GetUserSimpleInfo(int iUserID,UserSimpleInfo_T& oSimpleInfo)
{

   TADOQuery* pQuery = dmDatabase->adqFastEmp;
   AnsiString lSQL = "Select * from Employee e,Dept d where e.DeptID=d.DeptID and EmpID="+AnsiString(iUserID);
   pQuery->SQL->Text = lSQL;
   try
   {
     if (!pQuery->Active)
     {
        pQuery->Close();
        pQuery->Open();
     }
   }
   catch(...)
   {
     return  DBAccess_E;
   }
   if (!pQuery->IsEmpty())
   {
     oSimpleInfo.UserID = pQuery->FieldByName("EmpID")->AsInteger;
     oSimpleInfo.EmpName = pQuery->FieldByName("EmpName")->AsString;
     oSimpleInfo.DeptID = pQuery->FieldByName("e.DeptID")->AsInteger;
     oSimpleInfo.DeptName = pQuery->FieldByName("DeptName")->AsString;
     return OK;
   }
   return LogQueueEmpty_E;
}
//---------------------------------------------------------------------------
bool TDBAccess::GetUserPassword(int iUserID,AnsiString& oPassword)
{
   TADOQuery* pQuery = dmDatabase->adqFastEmp;
   AnsiString lSQL = "Select * from Employee where EmpID="+AnsiString(iUserID);
   pQuery->SQL->Text = lSQL;
   try
   {
      pQuery->Close();
      pQuery->Open();
   }
   catch(...)
   {
     return  DBAccess_E;
   }
   if (!pQuery->IsEmpty())
   {
     oPassword = pQuery->FieldByName("EmpPwd")->AsString;
     return true;
   }
   return false;

}
//---------------------------------------------------------------------------
int  TDBAccess::UpdateUserPassword(int iUserID,AnsiString iPassword)
{
  TADOQuery* pExeQuery = dmDatabase->adsExecute;
  pExeQuery->Close();
  pExeQuery->SQL->Text = "Update Employee set EmpPwd=:EmpPwd where EmpID=:EmpID";
  try
  {
    pExeQuery->Parameters->ParamByName("EmpPwd")->Value = iPassword;
    pExeQuery->Parameters->ParamByName("EmpID")->Value = iUserID;
    pExeQuery->ExecSQL();
  }
  catch(...)
  {
    return DBAccess_E;
  }
  return OK;

}
//---------------------------------------------------------------------------
int  TDBAccess::SetUserRole(int iUserID,char iRoleVal)
{
  TADOQuery* pExeQuery = dmDatabase->adsExecute;
  pExeQuery->Close();
  pExeQuery->SQL->Text = "Update Employee set AttendPlan=:AttendPlan where EmpID=:EmpID";
  try
  {
    pExeQuery->Parameters->ParamByName("AttendPlan")->Value = (int)iRoleVal;
    pExeQuery->Parameters->ParamByName("EmpID")->Value = iUserID;
    pExeQuery->ExecSQL();
  }
  catch(...)
  {
    return DBAccess_E;
  }
  return OK;
}
//---------------------------------------------------------------------------
int  TDBAccess::SetUserDept(int iUserID,int iDeptID)
{
  TADOQuery* pExeQuery = dmDatabase->adsExecute;
  pExeQuery->Close();
  pExeQuery->SQL->Text = "Update Employee set DeptID=:DeptID where EmpID=:EmpID";
  try
  {
    pExeQuery->Parameters->ParamByName("DeptID")->Value = iDeptID;
    pExeQuery->Parameters->ParamByName("EmpID")->Value = iUserID;
    pExeQuery->ExecSQL();
  }
  catch(...)
  {
    return DBAccess_E;
  }
  return OK;
}
//---------------------------------------------------------------------------

int TDBAccess::UpdateUserFlag(int iUserID,int iFlag)
{
  //修改员工状态
  TADOQuery* pExeQuery = dmDatabase->adsExecute;
  pExeQuery->Close();
  pExeQuery->SQL->Text = "Update Employee set Flag=:Flag where EmpID=:EmpID";
  try
  {
    pExeQuery->Parameters->ParamByName("Flag")->Value = iFlag;
    pExeQuery->Parameters->ParamByName("EmpID")->Value = iUserID;
    pExeQuery->ExecSQL();
  }
  catch(...)
  {
    return DBAccess_E;
  }
  return OK;

}
//---------------------------------------------------------------------------

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

int TDBAccess::UpdateTemplateCount(int iUserID,int iFingerCount)//修改员工指纹数
{
  TADOQuery* pExeQuery = dmDatabase->adsExecute;
  pExeQuery->Close();
  pExeQuery->SQL->Text = "Update Employee 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;
}
//---------------------------------------------------------------------------

int TDBAccess::InsertTemplateInfo(TemplateInfo_T iTemplateInfo)
{
  TADOQuery* pExeQuery = dmDatabase->adsExecute;
  AnsiString lTemplateStr;
  EncodeData(iTemplateInfo.FingerTemplate,256,lTemplateStr);

  pExeQuery->Close();
  pExeQuery->SQL->Text = "insert into EmptTemplate(EmpID,FingerIndex,FingerType,FingerTemplate,Flag) Values(:EmpID,:FingerIndex,:FingerType,:FingerTemplate,:Flag)";
  pExeQuery->Parameters->ParamByName("EmpID")->Value = iTemplateInfo.EmpID;
  pExeQuery->Parameters->ParamByName("FingerIndex")->Value = 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::DeleteTemplateInfo(int iUserID,int iFingerIndex)
{
   AnsiString lSQL = "delete from EmptTemplate where EmpID="+AnsiString(iUserID)+" and FingerIndex="+AnsiString(iFingerIndex);
   ExecuteSQL(lSQL);

   //把指纹编号排顺序
   TADOQuery* pQuery = dmDatabase->adsOp;
   pQuery->SQL->Text = "Select * from EmptTemplate where EmpID="+AnsiString(iUserID);
   pQuery->Close();
   try
   {
     pQuery->Open();
   }
   catch(...)
   {
     return DBAccess_E;
   }

   int lIndex;
   for(pQuery->First();!pQuery->Eof;pQuery->Next())
   {
     lIndex = pQuery->FieldByName("FingerIndex")->AsInteger;
     if(lIndex > iFingerIndex)
     {
       lSQL = "Update EmptTemplate set FingerIndex="+AnsiString(lIndex-1)+" where TemplateID="+AnsiString(pQuery->FieldByName("TemplateID")->AsInteger);
       ExecuteSQL(lSQL);
     }
   }

   //修改员工指纹数
   int lCount = GetTemplateCount(iUserID);
   UpdateTemplateCount(iUserID,lCount);
   return OK;
}
//---------------------------------------------------------------------------

int TDBAccess::DeleteTemplateOneUser(int iUserID)//删除一个员工的所有指纹
{
   AnsiString lSQL = "delete from EmptTemplate where EmpID="+AnsiString(iUserID);
   ExecuteSQL(lSQL);
   return OK;
}
⌨️ 快捷键说明

复制代码 Ctrl + C
搜索代码 Ctrl + F
全屏模式 F11
切换主题 Ctrl + Shift + D
显示快捷键 ?
增大字号 Ctrl + =
减小字号 Ctrl + -