dbaccessunit.cpp

指纹门禁机管理系统

//---------------------------------------------------------------------------
#pragma hdrstop
#include "DBAccessUnit.h"
#include "DMDataAccess.h"
#include <encddecd.hpp>
//---------------------------------------------------------------------------
TDBAccess::TDBAccess()
{
   aRV = OK;

}
//---------------------------------------------------------------------------
TDBAccess::~TDBAccess()
{


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

int TDBAccess::ExecuteSQL(AnsiString iSQL)
{
  dmDatabase->adsExecute->Close();
  dmDatabase->adsExecute->SQL->Text = iSQL;
  try
  {
    dmDatabase->adsExecute->ExecSQL();
  }
  catch(...)
  {
    return DBAccess_E;
  }
  return OK;
}
//---------------------------------------------------------------------------

bool TDBAccess::UpdateDeviceState(int iDeviceID,int iState)//修改设备状态
{
  TADOQuery* pExeQuery = dmDatabase->adsExecute;
  pExeQuery->Close();
  pExeQuery->SQL->Text = "Update Device set Flag=:Flag where DeviceID=:DeviceID";
  pExeQuery->Parameters->ParamByName("Flag")->Value = iState;
  pExeQuery->Parameters->ParamByName("DeviceID")->Value = iDeviceID;
  try
  {
    pExeQuery->ExecSQL();
  }
  catch(...)
  {
    return DBAccess_E;
  }
  return OK;
}
//---------------------------------------------------------------------------

int  TDBAccess::GetDeviceInfo(int iDeviceID,DeviceInfo_T& oDeviceInfo)
{
   //得到设备信息
   TADOQuery* pQuery = dmDatabase->adsOp;

   pQuery->Close();
   pQuery->SQL->Text = "Select * from Device where DeviceID=" + AnsiString(iDeviceID);
   try
   {
     pQuery->Open();
   }
   catch(...)
   {
     return DBAccess_E;
   }
   if (pQuery->IsEmpty())
     return DBIsEmpty_E;

   AnsiString lStrName;

   memset(&oDeviceInfo,0,sizeof(oDeviceInfo));
   oDeviceInfo.DeviceID = pQuery->FieldByName("DeviceID")->AsInteger;
   lStrName = pQuery->FieldByName("DeviceName")->AsString;
   strcpy(oDeviceInfo.DeviceName,lStrName.c_str());

   oDeviceInfo.DeviceType = (DeviceType_T)pQuery->FieldByName("DeviceType")->AsInteger;

   AnsiString lStr;
   lStr = pQuery->FieldByName("DeviceParam")->AsString;   //设备参数s

   //对设备参数进行Base64解码
   int lParamSize;
   DecodeData(lStr,(BYTE*)&oDeviceInfo.DeviceParams,&lParamSize);

   oDeviceInfo.AutoLockSec = pQuery->FieldByName("AutoLockSec")->AsInteger;
   oDeviceInfo.DeviceMode = (DeviceMode_T)pQuery->FieldByName("DeviceMode")->AsInteger;
   oDeviceInfo.DeviceStatus = (DeviceStatus_T)pQuery->FieldByName("Flag")->AsInteger;
   lStrName = pQuery->FieldByName("DeviceCaption")->AsString;
   strcpy(oDeviceInfo.DeviceCaption,lStrName.c_str());

   lStrName = pQuery->FieldByName("DeviceDesc")->AsString;
   strcpy(oDeviceInfo.DeviceDesc,lStrName.c_str());
   return OK;

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

int TDBAccess::GetDeviceList(DeviceInfoArray_T& oDeviceInfoList)
{
   //得到设备链接列表
   TADOQuery* pQuery = dmDatabase->adsOp;

   pQuery->Close();
   pQuery->SQL->Text = "Select * from Device";
   try
   {
     pQuery->Open();
   }
   catch(...)
   {
     return DBAccess_E;
   }
   DeviceInfo_T aDeviceInfo;
   AnsiString lStrName;
   oDeviceInfoList.clear();
   for(pQuery->First();!pQuery->Eof;pQuery->Next())
   {
     memset(&aDeviceInfo,0,sizeof(aDeviceInfo));

     aDeviceInfo.DeviceID = pQuery->FieldByName("DeviceID")->AsInteger;
     lStrName = pQuery->FieldByName("DeviceName")->AsString;
     strcpy(aDeviceInfo.DeviceName,lStrName.c_str());
     aDeviceInfo.DeviceType = (DeviceType_T)pQuery->FieldByName("DeviceType")->AsInteger;

     AnsiString lStr;
     lStr = pQuery->FieldByName("DeviceParam")->AsString;   //设备参数s

     //对设备参数进行Base64解码
     int lParamSize;
     DecodeData(lStr,(BYTE*)&aDeviceInfo.DeviceParams,&lParamSize);

     aDeviceInfo.AutoLockSec = pQuery->FieldByName("AutoLockSec")->AsInteger;
     aDeviceInfo.DeviceMode = (DeviceMode_T)pQuery->FieldByName("DeviceMode")->AsInteger;
     aDeviceInfo.DeviceStatus = (DeviceStatus_T)pQuery->FieldByName("Flag")->AsInteger;
     lStrName = pQuery->FieldByName("DeviceCaption")->AsString;
     strcpy(aDeviceInfo.DeviceCaption,lStrName.c_str());

     lStrName = pQuery->FieldByName("DeviceDesc")->AsString;
     strcpy(aDeviceInfo.DeviceDesc,lStrName.c_str());
     oDeviceInfoList.push_back(aDeviceInfo);
   }
   return OK;
}
//---------------------------------------------------------------------------
int TDBAccess::InsertDeviceInfo(DeviceInfo_T iDeviceInfo)
{
  TADOQuery* pExeQuery = dmDatabase->adsExecute;
  pExeQuery->Close();
  pExeQuery->SQL->Text = "insert into Device(DeviceID,DeviceName,DeviceType,DeviceParam,DeviceCaption,AutoLockSec,DeviceDesc,DeviceMode,Flag) Values(:DeviceID,:DeviceName,:DeviceType,:DeviceParam,:DeviceCaption,:AutoLockSec,:DeviceDesc,:DeviceMode,:Flag)";
  pExeQuery->Parameters->ParamByName("DeviceID")->Value = iDeviceInfo.DeviceID;
  pExeQuery->Parameters->ParamByName("DeviceName")->Value = iDeviceInfo.DeviceName;
  pExeQuery->Parameters->ParamByName("DeviceType")->Value = (int)iDeviceInfo.DeviceType;

  //对设备参数进行Base64编码
  AnsiString lParam;
  EncodeData((BYTE*)&iDeviceInfo.DeviceParams,sizeof(iDeviceInfo.DeviceParams),lParam);
  pExeQuery->Parameters->ParamByName("DeviceParam")->Value = lParam;

  pExeQuery->Parameters->ParamByName("DeviceCaption")->Value = iDeviceInfo.DeviceCaption;
  pExeQuery->Parameters->ParamByName("AutoLockSec")->Value = (int)iDeviceInfo.AutoLockSec;
  pExeQuery->Parameters->ParamByName("DeviceDesc")->Value = iDeviceInfo.DeviceDesc;
  pExeQuery->Parameters->ParamByName("DeviceMode")->Value = iDeviceInfo.DeviceMode;
  pExeQuery->Parameters->ParamByName("Flag")->Value = iDeviceInfo.DeviceStatus;
  try
  {
    pExeQuery->ExecSQL();
  }
  catch(...)
  {
    return DBAccess_E;
  }
  return OK;

}
//---------------------------------------------------------------------------
int TDBAccess::UpdateDeviceInfo(int iDeviceID,DeviceInfo_T iDeviceInfo)
{
  TADOQuery* pExeQuery = dmDatabase->adsExecute;
  pExeQuery->Close();
  pExeQuery->SQL->Text = "Update Device set DeviceName=:DeviceName,DeviceType=:DeviceType,DeviceParam=:DeviceParam,DeviceCaption=:DeviceCaption,AutoLockSec=:AutoLockSec,DeviceDesc=:DeviceDesc,DeviceMode=:DeviceMode,Flag=:Flag where DeviceID=:DeviceID";
  pExeQuery->Parameters->ParamByName("DeviceName")->Value = iDeviceInfo.DeviceName;
  pExeQuery->Parameters->ParamByName("DeviceType")->Value = (int)iDeviceInfo.DeviceType;

  //对设备参数进行Base64编码
  AnsiString lParam;
  EncodeData((BYTE*)&iDeviceInfo.DeviceParams,sizeof(iDeviceInfo.DeviceParams),lParam);
  pExeQuery->Parameters->ParamByName("DeviceParam")->Value = lParam;

  pExeQuery->Parameters->ParamByName("DeviceCaption")->Value = iDeviceInfo.DeviceCaption;
  pExeQuery->Parameters->ParamByName("AutoLockSec")->Value = (int)iDeviceInfo.AutoLockSec;
  pExeQuery->Parameters->ParamByName("DeviceDesc")->Value = iDeviceInfo.DeviceDesc;
  pExeQuery->Parameters->ParamByName("DeviceMode")->Value = iDeviceInfo.DeviceMode;
  pExeQuery->Parameters->ParamByName("Flag")->Value = iDeviceInfo.DeviceStatus;
  pExeQuery->Parameters->ParamByName("DeviceID")->Value = iDeviceInfo.DeviceID;
  try
  {
    pExeQuery->ExecSQL();
  }
  catch(...)
  {
    return DBAccess_E;
  }
  return OK;
}
//---------------------------------------------------------------------------
int TDBAccess::DeleteDeviceInfo(int iDeviceID)
{
  TADOQuery* pExeQuery = dmDatabase->adsExecute;
  pExeQuery->Close();
  pExeQuery->SQL->Text = "delete from Device where DeviceID=:DeviceID";
  try
  {
    pExeQuery->Parameters->ParamByName("DeviceID")->Value = iDeviceID;
    pExeQuery->ExecSQL();
  }
  catch(...)
  {
    return DBAccess_E;
  }
  return OK;

}
//---------------------------------------------------------------------------
bool TDBAccess::IsExistsDeviceInfo(int iDeviceID)
{
  TADOQuery* pQuery = dmDatabase->adsOp;
  pQuery->Close();
  pQuery->SQL->Text = "select * from Device where DeviceID=:DeviceID";
  try
  {
    pQuery->Parameters->ParamByName("DeviceID")->Value = iDeviceID;
    pQuery->Open();
  }
  catch(...)
  {
    return DBAccess_E;
  }
  return pQuery->RecordCount != 0;
}
//---------------------------------------------------------------------------

void TDBAccess::AddChildNodes(TTreeView* iTreeView,TTreeNode* iParsentNode,int iParsetID)
{
   TADOQuery* pQuery = dmDatabase->adsOp;
   pQuery->Close();
   pQuery->SQL->Text = "Select * from dept where ParsentDept="+AnsiString(iParsetID);
   pQuery->Open();
   AnsiString lDeptName;
   int lDeptID;
   for(pQuery->First();!pQuery->Eof;pQuery->Next())
   {
     lDeptName = pQuery->FieldByName("DeptName")->AsString;
     lDeptID = pQuery->FieldByName("DeptID")->AsInteger;

     TTreeNode* lNewNode = iTreeView->Items->AddChild(iParsentNode,lDeptName);

     lNewNode->ImageIndex = DeptNode_ICON;
     lNewNode->SelectedIndex = SelectedDeptNode_ICON;
     lNewNode->StateIndex = lDeptID;//当前选中的部门ID号
   }
   TTreeNode* pChildNode;
   for(int i=0;i<iParsentNode->Count;i++)
   {
     pChildNode = iParsentNode->Item[i];
     int lParentID = pChildNode->StateIndex;
     AddChildNodes(iTreeView,pChildNode,lParentID);
   }

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

int TDBAccess::GetDeptTree(TTreeView* oDeptTree)
{
   AnsiString lDeptName;
   aRV = OK;
   TTreeNode* pRootNode = oDeptTree->TopItem;

   pRootNode->ImageIndex = RootNode_ICON;
   pRootNode->SelectedIndex = SelectedRootNode_ICON;

   AddChildNodes(oDeptTree,pRootNode,0);

   return aRV;
}
//---------------------------------------------------------------------------
int TDBAccess::InsertDeptInfo(DeptInfo_T iDeptInfo)
{
  TADOQuery* pExeQuery = dmDatabase->adsExecute;
  pExeQuery->Close();
  pExeQuery->SQL->Text = "insert into Dept(DeptName,ParsentDept,DeptDesc,Flag) Values(:DeptName,:ParsentDept,:DeptDesc,:Flag)";
  try
  {
    pExeQuery->Parameters->ParamByName("DeptName")->Value = AnsiString(iDeptInfo.DeptName);
    pExeQuery->Parameters->ParamByName("ParsentDept")->Value = iDeptInfo.ParsentDept;
    pExeQuery->Parameters->ParamByName("DeptDesc")->Value = AnsiString(iDeptInfo.DeptDesc);
    pExeQuery->Parameters->ParamByName("Flag")->Value = iDeptInfo.Flag;
    pExeQuery->ExecSQL();
  }
  catch(...)
  {
    return DBAccess_E;
  }
  return OK;

}
//---------------------------------------------------------------------------
int TDBAccess::UpdateDeptInfo(int iDeptID,DeptInfo_T iDeptInfo)
{
  TADOQuery* pExeQuery = dmDatabase->adsExecute;
  pExeQuery->Close();
  pExeQuery->SQL->Text = "Update Dept set DeptName=:DeptName,ParsentDept=:ParsentDept,DeptDesc=:DeptDesc,Flag=:Flag where DeptID=:DeptID";
  try
  {
    pExeQuery->Parameters->ParamByName("DeptName")->Value = AnsiString(iDeptInfo.DeptName);
    pExeQuery->Parameters->ParamByName("ParsentDept")->Value = iDeptInfo.ParsentDept;
    pExeQuery->Parameters->ParamByName("DeptDesc")->Value = AnsiString(iDeptInfo.DeptDesc);
    pExeQuery->Parameters->ParamByName("Flag")->Value = iDeptInfo.Flag;
    pExeQuery->Parameters->ParamByName("DeptID")->Value = iDeptID;
    pExeQuery->ExecSQL();
  }
  catch(...)
  {
    return DBAccess_E;
  }
  return OK;
}
//---------------------------------------------------------------------------
int TDBAccess::DeleteDeptInfo(int iDeptID)
{

  if(GetDeptUserCount(iDeptID) > 0)
    return DBIsEmployee_E;

  TADOQuery* pExeQuery = dmDatabase->adsExecute;
  pExeQuery->Close();
  pExeQuery->SQL->Text = "delete from Dept where DeptID=:DeptID";
  try
  {
    pExeQuery->Parameters->ParamByName("DeptID")->Value = iDeptID;
    pExeQuery->ExecSQL();
  }
  catch(...)
  {
    return DBAccess_E;
  }
  return OK;
}
//---------------------------------------------------------------------------
AnsiString TDBAccess::GetDeptName(int iDeptID)
{
   //得到部门名称
   TADOQuery* pQuery = dmDatabase->adsOp;
   pQuery->Close();
   pQuery->SQL->Text = "Select DeptName from Dept where DeptID="+AnsiString(iDeptID);
   try
   {
     pQuery->Open();
   }
   catch(...)
   {
     return NULL;
   }
   if (pQuery->IsEmpty())
   {
     return NULL;
   }
   AnsiString lDeptName = pQuery->FieldByName("DeptName")->AsString;
   return lDeptName;
}
//---------------------------------------------------------------------------
int TDBAccess::GetDeptInfo(int iDeptID,DeptInfo_T* oDeptInfo)
{
   TADOQuery* pQuery = dmDatabase->adsOp;
   pQuery->Close();
   pQuery->SQL->Text = "Select * from dept where DeptID="+AnsiString(iDeptID);
   pQuery->Open();
   if (!pQuery->IsEmpty())
   {
     oDeptInfo->DeptID = pQuery->FieldByName("DeptID")->AsInteger;
     oDeptInfo->ParsentDept = pQuery->FieldByName("ParsentDept")->AsInteger;
     AnsiString lName = pQuery->FieldByName("DeptName")->AsString;
     strcpy(oDeptInfo->DeptName,lName.c_str());

     lName = pQuery->FieldByName("DeptDesc")->AsString;
     strcpy(oDeptInfo->DeptDesc,lName.c_str());
     oDeptInfo->Flag = pQuery->FieldByName("Flag")->AsInteger;
     return OK;
   }
   return DBIsEmpty_E;
}
//---------------------------------------------------------------------------
int TDBAccess::GetFixedDeptID(DeptInfo_T* oFixedDeptInfo)
{
   //得到固定部门信息
   TADOQuery* pQuery = dmDatabase->adsOp;
   pQuery->Close();
   pQuery->SQL->Text = "Select * from dept where Flag = 1";
   pQuery->Open();
   if (!pQuery->IsEmpty())
   {
     oFixedDeptInfo->DeptID = pQuery->FieldByName("DeptID")->AsInteger;
     oFixedDeptInfo->ParsentDept = pQuery->FieldByName("ParsentDept")->AsInteger;
     AnsiString lName = pQuery->FieldByName("DeptName")->AsString;
     strcpy(oFixedDeptInfo->DeptName,lName.c_str());

     lName = pQuery->FieldByName("DeptDesc")->AsString;
     strcpy(oFixedDeptInfo->DeptDesc,lName.c_str());
     oFixedDeptInfo->Flag = pQuery->FieldByName("Flag")->AsInteger;
     return OK;
   }
   return DBIsEmpty_E;

}
//---------------------------------------------------------------------------
int TDBAccess::InitFixedDept()
{
  //初始化固定部门编号
  TADOQuery* pExeQuery = dmDatabase->adsExecute;
  pExeQuery->Close();
  pExeQuery->SQL->Text = "delete from Dept where Flag = 1";
  try
  {
    pExeQuery->ExecSQL();
  }
  catch(...)
  {
    return DBAccess_E;
  }
  DeptInfo_T aDeptInfo;

  aDeptInfo.ParsentDept = 0;
  strcpy(aDeptInfo.DeptName,"未分配部门");
  strcpy(aDeptInfo.DeptDesc,"从设备上传的未分配部门的员工默认部门");
  aDeptInfo.Flag = 1;
  return InsertDeptInfo(aDeptInfo);

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

bool TDBAccess::IsDeptIDExist(int iDeptID)//判断部门ID号是否存在
{