cls_datapub.pas

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

//****   返回表说明                   *********
//*********************************************
function TDataPub.sGetRelTableMemo(sTableName:string):string;
var
  sqlString:string;
  adoQue:TADOQuery;
begin
  sqlString:='select * from sys_table where TableName=''' + sTableName + '''';
  adoQue:=mdataOperate.adoGetAdoQuery(sqlString);
  //sqlString:= adoQue.fieldbyname('Note').AsString;
  Result:=adoQue.fieldbyname('Note').AsString;
  adoQue.Free;
end;
//*********************************************
//****  删除记录时检查关联关系        *********
//****  0:存在关联 1:不存在           *********
//*********************************************
function TDataPub.iCheckRelation:integer;
var
  iLoop,jLoop:integer;
  sqlString:string;
  adoQue:TADOQuery;
begin
  sGetRelTable;
  sGetRelColumn;
  for iLoop:=0 to high(msArrRelTableNames) do
  begin
    sqlString:='select * from ' + msArrRelTableNames[iLoop] + ' where ';
    for jloop:= 0 to high(msArrRelColNames[iLoop]) do
    begin
      if msArrRelColDataType[iLoop][jLoop]='VARCHAR2' then
        sqlString:=sqlString + msArrRelColNames[iLoop][jloop] + '=''' + msArrRelColData[iLoop][jloop] + '''' + ' and   '
      else if (msArrRelColDataType[iLoop][jLoop]='NUMBER') and (msArrRelColData[iLoop][jloop]<>'') then
        sqlString:=sqlString + msArrRelColNames[iLoop][jloop] + '=' + msArrRelColData[iLoop][jloop] + ' and   '
      else if (msArrRelColDataType[iLoop][jLoop]='NUMBER') and (msArrRelColData[iLoop][jloop]='') then
        sqlString:=sqlString + msArrRelColNames[iLoop][jloop] + '=0' + ' and   '
      else if msArrRelColDataType[iLoop][jLoop]='DATE' then
        sqlString:=sqlString + msArrRelColNames[iLoop][jloop] + '=''' + msArrRelColData[iLoop][jloop] + ''' and   ';
    end;
    sqlString:=copy(sqlString,1,length(sqlString)-6);
    adoQue:=mdataOperate.adoGetAdoQuery(sqlString);
    if adoQue.RecordCount<>0 then
      begin
        Result:= 0;
        msRelTableName:=msArrRelTableNames[iLoop];
        msRelTableMemo:=sGetRelTableMemo(msRelTableName);
        //sqlString:=msRelTableMemo;
        adoQue.Free;
        break;
      end
    else
      begin
        Result:=1;
        adoQue.Free;
      end;
  end;
end;
//*********************************************
//         取得表信息                         *
//   返回值  True:成功   False:失败           *
//                                            *
//*********************************************
function TDataPub.blnGetTableInfor;
var
  adoQue:TADOQuery;
  sqlText:string;
begin
  sqlText:='select * from sys_table where tablename=''' + msTableName + '''';
  adoQue:=mdataOperate.adoGetAdoQuery(sqlText);
  if adoQue.RecordCount<>0 then
  begin
    msTableNote:=adoQue.FieldValues['Note'];
    if (miState=0) or (miState=4) then
    begin
      miWidth:=adoQue.FieldValues['Width'];
      miHeight:=adoQue.FieldValues['Height'];
    end
    else if (miState=1) or (miState=2) then
    begin
      miWidth:=adoQue.FieldValues['SelectWidth'];
      miHeight:=adoQue.FieldValues['SelectHeight'];
    end
    else if miState=3 then
    begin
      miWidth:=adoQue.FieldValues['ConditionWidth'];
      miHeight:=adoQue.FieldValues['ConditionHeight'];
    end;
    adoQue.Free;
    Result:=true;
  end
  else
  begin
    Result:=false;
    adoQue.Free;
    Application.MessageBox('库中不存在此表！','提示',MB_OK+MB_ICONINFORMATION);
  end;
end;
//*********************************************
//         取得列信息                         *
//   返回值  True:成功   False:失败           *
//                                            *
//*********************************************
function TDataPub.blnGetColumnInfor;
var
  adoQue:TADOQuery;
  sqlText:string;
  iLoop:integer;
  //iNum:integer;
begin
  if miState=3 then
    sqlText:='select * from sys_column where tablename=''' + msTableName + ''' and ISELECTVISIBLE=1 order by columnid'
  else if miState=10 then
    sqlText:='select * from sys_column where tablename=''' + msTableName + ''' and ISVISIBLE=1 order by columnid'
  else
    sqlText:='select * from sys_column where tablename=''' + msTableName + ''' and ISVISIBLE=1 order by columnid';
  adoQue:=mdataOperate.adoGetAdoQuery(sqlText);
  if adoQue.RecordCount<>0 then
  begin
    miColumnCount:=adoQue.RecordCount;
    SetLength(msArrColumnName,miColumnCount);
    SetLength(msArrColumnType,miColumnCount);
    SetLength(msArrColumnComments,miColumnCount);
    SetLength(miArrColumnLength,miColumnCount);
    SetLength(miArrColumnScale,miColumnCount);
    SetLength(miArrColumnNull,miColumnCount);
    SetLength(miArrColumnPri,miColumnCount);
    SetLength(miArrColumnFor,miColumnCount);
    SetLength(msArrFTableName,miColumnCount);
    SetLength(msArrFCodeColumn,miColumnCount);
    SetLength(msArrFNameColumn,miColumnCount);
    SetLength(msArrFNameColumnMemo,miColumnCount);
    SetLength(miArrLabelWidth,miColumnCount);
    SetLength(miArrEditWidth,miColumnCount);
    SetLength(msArrDataValue,miColumnCount);
    SetLength(msArrShowValue,miColumnCount);
    SetLength(miArrAuto,miColumnCount);
    //iNum:=0;
    for iloop:=0 to miColumnCount - 1 do
    begin
      msArrColumnName[iLoop]:=adoQue.FieldValues['ColumnName'];
      msArrColumnType[iLoop]:=adoQue.FieldValues['ColumnType'];
      msArrColumnComments[iLoop]:=adoQue.Fieldbyname('ColumnComment').AsString;
      miArrColumnLength[iLoop]:=adoQue.Fieldbyname('COLUMNLENGTH').AsInteger;
      miArrColumnScale[iLoop]:=adoQue.Fieldbyname('COLUMNSCALE').AsInteger;
      miArrColumnNull[iLoop]:=adoQue.Fieldbyname('ISNULL').AsInteger;
      miArrColumnPri[iLoop]:=adoQue.Fieldbyname('PRIMARYKEY').AsInteger;
      miArrColumnFor[iLoop]:=adoQue.Fieldbyname('FOREIGNKEY').AsInteger;
      msArrFTableName[iLoop]:=adoQue.fieldbyname('FTable').AsString;
      msArrFCodeColumn[iLoop]:=adoQue.fieldbyname('FCodeColumn').AsString;
      msArrFNameColumn[iLoop]:=adoQue.fieldbyname('FNameColumn').AsString;
      msArrFNameColumnMemo[iLoop]:= adoQue.fieldbyname('FNameColumnMemo').AsString;
      miArrLabelWidth[iLoop]:=adoQue.fieldbyname('LabelWidth').AsInteger;
      miArrEditWidth[iLoop]:=adoQue.fieldbyname('EditWidth').AsInteger;
      miArrAuto[iLoop]:=adoQue.fieldbyname('ISAUTO').AsInteger;
      {if adoQue.Fieldbyname('PRIMARYKEY').AsInteger=1 then
      begin
        SetLength(msArrPriColName,length(msArrPriColName) +1);
        SetLength(msArrPriColType,length(msArrPriColType) +1);
        msArrPriColName[iNum]:=adoQue.FieldValues['ColumnName'];
        msArrPriColType[iNum]:= adoQue.FieldValues['ColumnType'];
        iNum:=iNum + 1;
      end;}
      adoQue.Next;
    end;
    adoQue.Free;
    Result:=true;
  end
  else
  begin
    Result:=false;
    adoQue.Free;
    Application.MessageBox('库中不存在此表！','提示',MB_OK+MB_ICONINFORMATION);
  end;
end;
//*********************************************
//            取得特殊关联表的信息            *
//                                            *
//                                            *
//*********************************************
function TDataPub.blnGetSpeInfor:boolean;  // 取得特殊关联表的信息
var
  iLoop,jLoop:integer;
  adoQue,adoFieldQue:TADOQuery;
  sqlText:string;
begin
  sqlText:='select distinct RelationTable from sys_Relation where OriginalTable=''' + msTableName + '''';
  adoQue:=mdataOperate.adoGetAdoQuery(sqlText);
  try
  begin
    SetLength(msSpecialRelTableNames,adoQue.RecordCount);
    SetLength(msOriginalField,adoQue.RecordCount);
    SetLength(msRelField,adoQue.RecordCount);
    SetLength(msRelType,adoQue.RecordCount);
    for iLoop:=0 to adoQue.RecordCount -1 do
    begin
      msSpecialRelTableNames[iLoop]:=adoQue.FieldValues['RelationTable'];
      adoQue.Next;
    end;
    adoQue.Free;
    for iLoop:=0 to high(msSpecialRelTableNames) do
    begin
      sqlText:='select * from sys_Relation where OriginalTable=''' + msTableName + ''' and RelationTable=''' +
        msSpecialRelTableNames[iLoop] + '''';
      adoFieldQue:=mdataOperate.adoGetAdoQuery(sqlText);
      SetLength(msOriginalField[iLoop],adoFieldQue.RecordCount);
      SetLength(msRelField[iLoop],adoFieldQue.RecordCount);
      SetLength(msRelType[iLoop],adoFieldQue.RecordCount);
      for jLoop:=0 to adoFieldQue.RecordCount -1 do
      begin
        msOriginalField[iLoop][jLoop]:=adoFieldQue.FieldValues['OriginalField'];
        msRelField[iLoop][jLoop]:=adoFieldQue.FieldValues['RelationField'];
        msRelType[iLoop][jLoop]:=adoFieldQue.FieldValues['RelationType'];
        adoFieldQue.Next;
      end;
      adoFieldQue.Free;
    end;
  end
  except on e:Exception do
  begin
    Result:=false;
    adoQue.Free;
    adoFieldQue.Free;
    ShowMessage(e.Message);
  end;
  end;
end;
//*********************************************
//            将特殊关联表加入下啦列表        *
//                                            *
//                                            *
//*********************************************
procedure TDataPub.SpecialRelResource(cb_Relate:TBase_ComboBox);
var
  iLoop:integer;
begin
  for iLoop:=0 to High(msSpecialRelTableNames) do
  begin
    cb_Relate.itemsvalue.Add(msSpecialRelTableNames[iLoop]);
    cb_Relate.items.Add(sGetRelTableMemo(msSpecialRelTableNames[iLoop]));
    cb_Relate.ItemIndex:=0;
  end;
end;
//*********************************************
//          更改外键显示列标题                *
//                                            *
//                                            *
//*********************************************
procedure TDataPub.sChangeListCaption(lv_Grid:TBase_ListView);
var
  iLoop:integer;
begin
  for iloop:=0 to miColumnCount-1 do
  begin
    if miArrColumnFor[iLoop]=1 then
    begin
      lv_Grid.Columns[iLoop].Caption:= msArrFNameColumnMemo[iLoop];
    end;
  end;
end;
//*********************************************
//          更改外键值                        *
//                                            *
//                                            *
//*********************************************
procedure TDataPub.sChangeListData(lv_Grid:TBase_ListView);
var
  iLoop,jLoop,kLoop:integer;
  adoQue:TADOQuery;
  sqlString:string;
begin
  for iLoop:=0 to lv_Grid.Columns.Count -1 do
  begin
    if miArrColumnFor[iLoop]= 1 then
    begin
      sqlString:='select * from ' + msArrFTableName[iLoop];
      adoQue:=mdataOperate.adoGetAdoQuery(sqlString);
      for jLoop:= 0 to lv_Grid.Items.Count -1 do
      begin
        adoQue.First;
        if iLoop=0 then
        begin
          for kLoop:=0 to adoQue.RecordCount -1 do
          begin
            if (lv_Grid.Items[jLoop].Caption=adoQue.fieldbyname(msArrFCodeColumn[iLoop]).AsString) then
            begin
              lv_Grid.Items[jLoop].Caption:=adoQue.fieldbyname(msArrFNameColumn[iLoop]).AsString;
              break;
            end;
            adoQue.Next;
          end;
        end
        else
        begin
          for kLoop:=0 to adoQue.RecordCount -1 do
          begin
            if (lv_Grid.Items[jLoop].SubItems[iLoop-1]=adoQue.fieldbyname(msArrFCodeColumn[iLoop]).AsString) then
            begin
              lv_Grid.Items[jLoop].SubItems[iLoop-1]:=adoQue.fieldbyname(msArrFNameColumn[iLoop]).AsString;
              break;
            end;
            adoQue.Next;
          end;
        end;
      end;
      adoQue.Free;
    end;
  end;
end;
//*********************************************
//        ListView选择事件                    *
//                                            *
//                                            *
//*********************************************
procedure TDataPub.lvSelectItem(Sender: TObject; Item: TListItem; Selected: Boolean);
var
  iLoop,jLoop:integer;
  PtableTemp:pTable;
  iNum:integer;
begin
  if Selected=False then
    exit;
  PtableTemp:=Item.Data;
  iNum:=0;
  for iLoop:=0 to mContainer.ControlCount -1 do
  begin
    if (mContainer.Controls[iLoop] is TBase_LabeledEdit) then
    begin
      TBase_LabeledEdit(mContainer.Controls[iLoop]).Text:=PtableTemp^.DataValue[iNum];
      iNum:=iNum + 1;
    end
    else if (mContainer.Controls[iLoop] is TMaskEdit) then
    begin
      TMaskEdit(mContainer.Controls[iLoop]).Text:=PtableTemp^.DataValue[iNum];
      iNum:=iNum + 1;
    end
    else if (mContainer.Controls[iLoop] is TBase_ComboBox) then
    begin
      for jLoop:=0 to TBase_ComboBox(mContainer.Controls[iLoop]).Items.Count -1 do
      begin
        if TBase_ComboBox(mContainer.Controls[iLoop]).itemsvalue[jloop]=PtableTemp^.DataValue[iNum] then
          begin
            TBase_ComboBox(mContainer.Controls[iLoop]).ItemIndex :=jLoop;
            break;
          end;