cls_datapub.pas

用Delhpi和mapx开发的警务管理系统

//****************************************************************************
//数据操作类                                                                 ×
//创建日期：2003-07-18                 作者：王元斌                          ×
//版本：1.0                                                                  ×
//                                                                           ×
//                                                                           ×
//                                                                           ×
//****************************************************************************
unit cls_DataPub;

interface

uses
    Windows, Messages, SysUtils, Classes, Graphics,Controls, Forms, Dialogs,StdCtrls,ComCtrls,
    frmSystemDM,adodb,Base_Label,Base_Edit,Base_Panel,Base_DateTimePicker,Base_Combobox,
    Base_StaticText,Base_LabeledEdit,Base_ListView,Base_BitBtn,frm_Ole,cls_BaseClass,
    Base_Button,Base_MaskEditLat,Base_MaskEditLon,Mask,Base_Time;

type
  LVTable=record
    //绑定的表的数据
    DataValue:array of string;
  end;
  pTable=^LVTable;
  TDataPub=class
    private
      mdataOperate:TDataOperate;
      miState:integer;  //状态   0:添加 1:查询 2:嵌套表添加
      msFieldName:TStringList;//嵌套表添加时，传进的主键字段名
      //miSelect:integer;  //0: 不需生成 ListView 1需要
      msSqlString:string;//查询时的SQL语句

      mContainer:TBase_Panel;   //容器
      mAttribute:TListView;
      mcb_Relate:TBase_ComboBox; //关联资源容器
      msTableName:string;      //表名
      msTableNote:string;      //表说明
      miWidth:integer;         //窗体宽度
      miHeight:integer;        //窗体高度
      msForm:TForm;

      miColumnCount:integer;  // 列树
      miArrColumnLength:array of integer;  //列长度
      miArrColumnScale:array of integer;   //小数位数
      miArrColumnNull:array of integer;    //是否为空
      miArrColumnFor:array of integer;    //是否是外键
      msArrFTableName:array of string;    //外键所引自的表名
      msArrFCodeColumn:array of string;   //外键表的代码及列
      msArrFNameColumn:array of string;   //外键表显示列
      msArrFNameColumnMemo:array of string;   //外键表显示列列名
      miArrLabelWidth,miArrEditWidth:array of integer;  //控件宽度
      miArrAuto:array of integer;                       //是否是自动增加列 

      msArrPriColName:array of string;     //主键名
      msArrPriColType:array of string;      //主键类型

      msArrRelTableNames: array of string;//delete操作中，相关表的数组
      //msArrRelColId:array of array  of integer;
      msRelTableName:string;                      //关联表名
      msRelTableMemo:string;                      //关联表说明


      msArrDataValue:array of string;      //保存数据
      msArrShowValue:array of string;      //显示的数据
      miCurrentIndex:integer;              //当前控件对应的序号

      procedure allIntOnKeypress(Sender: TObject;var Key: Char);
      procedure allFloatOnKeypress(Sender: TObject;var Key: Char);
      function blnGetTableInfor:boolean;     //取得表信息
      function blnGetColumnInfor:boolean;    //取得列信息
      function blnGetSpeInfor:boolean;  // 取得特殊关联表的信息
      function blnGenerateControl:boolean;   //生成窗体控件
      function sGetConValue(sControl: TControl): String;   //取得单个控件的值
      procedure sGetDataValue;               //取得所有数据
      function sGetConShowValue(sControl: TControl): String;
      function iCheckNullAble():integer;//非空验证
      function iCheckRelation:integer;  //判断关联关系
      function iCheckUnique:integer;    //主键唯一性验证
      procedure sGetRelTable;     //取得关联表的表名
      procedure sGetRelColumn;    //取得关联列的列名和列值
      function sGetRelTableMemo(sTableName:string):string;  //取得关联表的表说明
      procedure lvSelectItem(Sender: TObject; Item: TListItem; Selected: Boolean);
      procedure lvChangeData;//      修改数据后,修改ListView的显示
      procedure lvDeleteData; //      删除记录时, 修改ListView的显示
      procedure OpenClick(Sender: TObject);   //打开编辑窗体
      procedure EditClick(Sender:TObject);    //关联表编辑
      procedure lvDataShow();
    public
      msArrColumnName:array of string; //列名
      msArrColumnComments:array of string; //列说明
      msArrColumnType:array of string;   //列类型
      miArrColumnPri:array of integer;    //是否是主键
      
      msArrRelColNames:array of array of string;//delete操作中，相关表的关联列
      msArrRelColData:array of array of string;//delete操作中，相关表的关联列值
      msArrRelColDataType:array of array of string;//delete操作中，相关表的关联列类型

      msSpecialRelTableNames:array of string;//特殊关联表的表名
      msOriginalField:array of array of string;  //原表字段名
      msRelField:array of array of string;       // 关联字段名
      msRelType:array of array of integer;      //关联类型

      lv_Temp:TBase_ListView;

      constructor create(dataOperate:TDataOperate;sTableName:string;sForm:TForm;sContainer:TBase_Panel;cb_Relate:TBase_ComboBox;iState:integer;sSql:string;sFieldName:TStringList);overload;
      constructor create(dataOperate:TDataOperate;sTableName:string);overload;
      constructor create(dataOperate:TDataOperate;sTableName:string;sForm:TForm;sContainer:TBase_Panel;iState:integer;lv_Arrtribute:TListView);overload;
      constructor create(dataOperate:TDataOperate;sTableName:string;iState:integer);overload;
      constructor create(dataOperate:TDataOperate;sTableName:string;sContainer:TBase_Panel;iState:integer;lv_Arrtribute:TListView);overload;

      procedure sChangeListCaption(lv_Grid:TBase_ListView);//             更改外键列的标题
      procedure sChangeListData(lv_Grid:TBase_ListView);  //               更改外键列的列值
      procedure SpecialRelResource(cb_Relate:TBase_ComboBox);          //增加特殊关联资源名
      procedure blnAddRecord;
      procedure blnUpdateRecord;
      procedure blnDeleteRecord;
      function  sFormWhereSql:string;   //形成查询语句
      function  sFormSelectSql:string;
      function  iGetColumnIndex(sColumnName:string):integer;    //根据字段名得到字段的序号
      procedure ShowData;
  end;
const
  miDistance=5;
  miLabelHeight=18;
implementation

uses DB, StrUtils, ExtCtrls, cls_GridListView, Buttons, frm_Basic;

constructor TDataPub.create(dataOperate:TDataOperate;sTableName:string);
begin
  mdataOperate:=dataOperate;
  msTableName:=UpperCase(sTableName);
  blnGetTableInfor;
  blnGetColumnInfor;
  blnGetSpeInfor;
end;

constructor TDataPub.create(dataOperate:TDataOperate;sTableName:string;iState:integer);
begin
  mdataOperate:=dataOperate;
  msTableName:=UpperCase(sTableName);
  miState:=iState;
  blnGetTableInfor;
  blnGetColumnInfor;
  blnGetSpeInfor;
end;


constructor TDataPub.create(dataOperate:TDataOperate;sTableName:string;sForm:TForm;sContainer:TBase_Panel;cb_Relate:TBase_ComboBox;iState:integer;sSql:string;sFieldName:TStringList);
begin
  mdataOperate:=dataOperate;
  miState:=iState;
  msFieldName:=sFieldName;
  //miSelect:=iSelect;
  msSqlString:=sSql;
  msTableName:=UpperCase(sTableName);
  msForm:=sForm;
  mContainer:=sContainer;
  mcb_Relate:=cb_Relate;
  blnGetTableInfor;
  blnGetColumnInfor;
  blnGetSpeInfor;
  blnGenerateControl;
  sForm.Caption:=msTableNote;
  sForm.Refresh;
  msForm.Width:=miWidth;
  msForm.Height:=miHeight;
end;


constructor TDataPub.create(dataOperate:TDataOperate;sTableName:string;sForm:TForm;sContainer:TBase_Panel;iState:integer;lv_Arrtribute:TListView);
begin
  mdataOperate:=dataOperate;
  miState:=iState;
  msTableName:=UpperCase(sTableName);
  msForm:=sForm;
  mContainer:=sContainer;
  mAttribute:=lv_Arrtribute;
  blnGetTableInfor;
  blnGetColumnInfor;
  blnGetSpeInfor;
  blnGenerateControl;
  sForm.Caption:=msTableNote;
  sForm.Refresh;
  msForm.Width:=miWidth;
  msForm.Height:=miHeight;
end;


constructor TDataPub.create(dataOperate:TDataOperate;sTableName:string;sContainer:TBase_Panel;iState:integer;lv_Arrtribute:TListView);
begin
  mdataOperate:=dataOperate;
  miState:=iState;
  msTableName:=UpperCase(sTableName);
  mContainer:=sContainer;
  mAttribute:=lv_Arrtribute;
  miWidth:=sContainer.Width;
  miHeight:=sContainer.Height;
  blnGetTableInfor;
  blnGetColumnInfor;
  blnGenerateControl;
  ShowData;
end;


//*********************************************
//****   整数录入框的OnKeypress事件   *********
//****                                *********
//*********************************************
procedure TDataPub.allIntOnKeypress(Sender: TObject;
  var Key: Char);
begin
  case key of
   '0','1','2','3','4','5','6','7','8','9',#8:key:=key;
   else
   key:=#0;
   end;                      
end;
//*********************************************
//****   浮点数录入框的OnKeypress事件   *********
//****                                *********
//*********************************************
procedure TDataPub.allFloatOnKeypress(Sender: TObject;
  var Key: Char);
begin
   case key of
   '0','1','2','3','4','5','6','7','8','9':
    begin
      if (pos('.',TCustomEdit(Sender).Text)=0) and (length(TCustomEdit(Sender).Text)<(miArrColumnLength[miCurrentIndex] - miArrColumnScale[miCurrentIndex]-1)) then
        key:=key
      else if (pos('.',TCustomEdit(Sender).Text)<>0) and ((length(TCustomEdit(Sender).Text) - pos('.',TCustomEdit(Sender).Text))<miArrColumnScale[miCurrentIndex]) then
        key:=key
      else
        key:=#0;
    end;
    #8: key:=key;
   '.':begin
        if (pos('.',TCustomEdit(Sender).Text)<>0) or (length(TCustomEdit(Sender).Text)=0) then
          key:=#0
        else
          key:=key;
       end;
   else
    key:=#0;
   end;
end;
//***********************************************
//****  非空验证                        *********
//**** -1:验证通过 其它:为通过验证的序号*********
//***********************************************
function TDataPub.iCheckNullAble():integer;
var
  iLoop:integer;
begin
  result:=-1;
  for iLoop:=0 to miColumnCount -1 do
    if ((miArrColumnNull[iLoop]=0) and (msArrDataValue[iLoop]='') and(miArrAuto[iLoop]=0)) then
      begin
        result:=iLoop;
        break;
      end;
end;
//*********************************************
//****  主键唯一性验证                *********
//****  1:唯一  -1:不唯一             *********
//*********************************************
function TDataPub.iCheckUnique:integer;
var
  sqlString:string;
  iLoop:integer;
  adoQue:TADOQuery;
begin
  sqlString:='select count(*) from ' + msTableName + ' where ';
  for iLoop:=0 to miColumnCount -1 do
  begin
    if miArrColumnPri[iLoop] = 1 then
    begin
      sqlString:=sqlString + msArrColumnName[iLoop] + '=''' + msArrDataValue[iLoop] + '''' + ' and   ';
    end;
  end;
  sqlString:=copy(sqlString,1,length(sqlstring)-6);
  adoQue:=mdataOperate.adoGetAdoQuery(sqlString);
  if adoQue.Fields[0].AsInteger <>0 then
    result:=-1
  else
    result:=1;
  adoQue.Free;
end;
//*********************************************
//****  取得关联表信息                *********
//****                                *********
//*********************************************
procedure TdataPub.sGetRelTable;
var
  iLoop:integer;
  sqlString:string;
  adoQue:TADOQuery;
begin
  sqlString:='select distinct TableName from sys_column where FDirectTable=''' + msTableName + '''';
  adoQue:=mdataOperate.adoGetAdoQuery(sqlString);
  SetLength(msArrRelTableNames,adoQue.RecordCount);
  SetLength(msArrRelColNames,adoQue.RecordCount);
  //SetLength(msArrRelColId,adoQue.RecordCount);
  SetLength(msArrRelColData,adoQue.RecordCount);
  SetLength(msArrRelColDataType,adoQue.RecordCount);
  for iLoop:=0 to adoQue.RecordCount -1 do
  begin
    msArrRelTableNames[iLoop]:=adoQue.FieldValues['TableName'];
    adoQue.Next;
  end;
  adoQue.Free;
end;
//*********************************************
//****  取得关联表列信息              *********
//****                                *********
//*********************************************
procedure TdataPub.sGetRelColumn;
var
  iLoop,jLoop:integer;
  sqlString:string;
  adoQue:TADOQuery;
begin
  for iLoop:=0 to high(msArrRelTableNames) do
  begin
    sqlString:='select ColumnId,ColumnName,ColumnType from sys_column where tablename=''' + msArrRelTableNames[iLoop] + '''' +
    ' and FDirectTable=''' + msTableName + '''' + ' order by ColumnId';
    adoQue:=mdataOperate.adoGetAdoQuery(sqlString);
    SetLength(msArrRelColNames[iLoop],adoQue.RecordCount);
    //SetLength(msArrRelColId[iLoop],adoQue.RecordCount);
    SetLength(msArrRelColData[iLoop],adoQue.RecordCount);
    SetLength(msArrRelColDataType[iLoop],adoQue.RecordCount);
    for jLoop:=0 to adoQue.RecordCount -1 do
    begin
      msArrRelColNames[iLoop][jloop]:=adoQue.FieldValues['ColumnName'];
      //msArrRelColId[iLoop][jLoop]:= adoQue.FieldValues['ColumnId'];
      msArrRelColData[iLoop][jloop]:=msArrDataValue[jLoop];
      msArrRelColDataType[iLoop][jLoop]:=adoQue.FieldValues['ColumnType'];
      adoQue.Next;
    end;
    adoQue.Free;
  end;
end;
//*********************************************
//****  根据表明查找表说明            *********