dm.pas

批量修改约束关系, 只对sql server

UNIT DM;

INTERFACE

USES SysUtils, Classes, ADODB, DB, Forms, Windows, RzStatus, RzCommon, IniFiles,
  Variants, Controls, OleServer, yjkType, frxDesgn, frxClass, Dialogs;

TYPE
  TAccInfo=RECORD
    ID:STRING;
    Name:STRING;
    master:STRING;
    funit:STRING;
    year:STRING;
    month:STRING;
    LoginInUser:STRING;
    LoginInDate:TDate;
    LoginIn:Boolean;
  END;

  TToolsInfo=RECORD
    WareID:STRING;                      //工具仓库编号
    ClassID:STRING;                     //工具存货分类编号
  END;
  {
    TOneTools=record
      ID: integer;
      sl: integer;
      InvID: string;
      InvName: string;
      InvGG: string;
      InvCode: string;
      InvUnit: string;
      InvKCL: Integer;
      InvKYL: Integer;
      DJBH: string;
      LJBH: string;
      func: string;
      fdate: TDateTime;
      memo: string;
    end;  }
  TCodeInfo=RECORD
    icode:ARRAY OF Integer;             //各级长度
    scode:STRING;
    length:Integer;
    chsname:STRING;
    tablename:STRING;
    AllLen:Integer;
  END;

  TModiInvTables=RECORD
    chsname:STRING;
    realName:STRING;
    keyField:STRING;
  END;

  TInvFK_Info=RECORD
    fkname:STRING;
    status:Integer;
  END;
  { TCodeType= set of('科目编码级次', '存货分类编码级次', '部门编码级次', '供应商分类编码级次',
     '客户分类编码级次', '地区分类编码级次') ;
    }
  PAccinfo=^TAccInfo;

  TFDM=CLASS(TDataModule)
    aqTemp:TADOQuery;
    aqExec:TADOQuery;
    ADOCommand:TADOCommand;
    aqTmp:TADOQuery;
    aqShare:TADOQuery;
    DBConn:TADOConnection;
    dlgSave:TSaveDialog;
    dlgOpen:TOpenDialog;
    aqOper:TADOQuery;
    aqTools:TADOQuery;
    aqReport:TADOQuery;
    PROCEDURE DataModuleCreate(Sender:TObject);
    PROCEDURE DataModuleDestroy(Sender:TObject);
  PRIVATE
  PUBLIC
    FUNCTION InitDbsEnvir:Integer;
    FUNCTION CreateTables:Integer;
    FUNCTION OpenTables:Integer;
    FUNCTION GetJDStr(aid:STRING):STRING;
    FUNCTION GetUserAccess:Boolean;
    FUNCTION CheckUserAccOne(Acc:STRING):Boolean;
    FUNCTION GetChangRowCount:Integer;
    FUNCTION GetRate(ADate:STRING):Currency;
    FUNCTION OpenWorker:Boolean;
    PROCEDURE CastPowerStr(AStr:STRING);
    FUNCTION GetAccInfo(AStation, AModul:STRING):TAccInfo;
    FUNCTION GetCustTableEName(ATableCName:STRING):STRING;
    FUNCTION GetCustTableFieldsTitle(ATableCName:STRING):STRING;
    FUNCTION GetMaxDJBH:Integer;
    FUNCTION GetToolsWareList(AList:TStrings):Integer;
    FUNCTION GetAccIDList(AList:TStrings):Integer;
    FUNCTION GetAccInfoEx(AAccID, aYear:STRING):TAccInfo;
    FUNCTION GetOneCodeInfo(AName:STRING):TCodeInfo;
    FUNCTION InitModiInvTables:Integer;
    FUNCTION ModiInvTo2306:Integer;     //去掉相关强制关系
    FUNCTION ModiInvTo6:Integer;        //加上相关强制关系
    FUNCTION GetOnePrimaryKey(aTable:STRING):STRING; //取一表的主键名称
    FUNCTION BackupInvFKObject:Integer;
    FUNCTION GetInvFKs:Integer;
    FUNCTION DeleteInvFks:Integer;
    FUNCTION CreateInvFKs:Integer;
    FUNCTION DisableInvFKs(AName:STRING):Integer;
    FUNCTION EnableInvFKs(AName:STRING; bFlag:Boolean=True):Integer;
    PROCEDURE DelInvPrimaryKey;
    PROCEDURE AddInvPrimaryKey;
  END;
  {
  PROCEDURE CreateSubTree(FNodeName:STRING; Node:TTreeNode=NIL); overload;
  PROCEDURE CreateSubTree(ALevel: integer; Node:TTreeNode=NIL); overload;
  }
FUNCTION InitApplication:Integer;
PROCEDURE InitAdminPower;
FUNCTION ReadLJCodeField:STRING;
FUNCTION ReadLJClassID:STRING;
FUNCTION ReadReportSQL(AStr:STRING):STRING;
FUNCTION ReadReportSQLEx(AStr:STRING):STRING;

VAR
  FDM:TFDM;
  VerInfo:TRzVersionInfo;
  InitOKConn:Boolean;
  DBINI:TRzRegIniFile;
  WorkUser:TOper;
  bLinked:Boolean=false;
  bHasLogin:Boolean;                    //是否登录标志
  AccInfo:TAccInfo;
  ToolsInfo:TToolsInfo;
  CurrAccDBName:STRING;
  sIniFile:STRING;
  InvCode:TCodeInfo;
  InvTables:ARRAY OF TModiInvTables;
  InvFK:ARRAY OF TInvFK_Info;

CONST
  sRemoteConn='SELECT a.* FROM OPENROWSET(''SQLOLEDB'',''%s'';''%s'';''%s'',''%s'')as a';
  sDataNameBase='UFDATA_%s_%s';
  sTableNameM='工具短期借还';
  sTableNameD='量具编号维护';
  bZBCorp:Boolean=True;                 //装备公司标志
  sInvFKObjectTempTable='##InvFK_bakcup';

IMPLEMENTATION

{$R *.dfm}

{ TFDM }

USES ADOFuncs, PublicFunction, XFunc, xfuncs,
  SetDbsLink;

//数据模块创建

FUNCTION InitApplication:Integer;
VAR
  sSQL:STRING;
BEGIN
  Result:=0;
  FDM:=TFDM.Create(Application);
  frmDbsLink:=TfrmDbsLink.Create(Application);
  frmDbsLink.PropSave.LoadProperties;
  YDB.ServerName:=frmDbsLink.EDServer.Text;
  YDB.User:=frmDbsLink.EDSA.Text;
  YDB.Pass:=frmDbsLink.EDPass.Text;

  TRY
    Result:=FDM.InitDbsEnvir;
    IF Result<1 THEN
    BEGIN
      MyInformation('请认真检查''环境设置''是否正确...'#13#10'然后退出程序,重新运行...');
      Application.ProcessMessages;
      exit;
    END;
  EXCEPT
    Mywarning('数据环境初始化失败...');
  END;
END;

PROCEDURE InitAdminPower;
BEGIN

END;
{
PROCEDURE CreateSubTree(ALevel: integer; Node:TTreeNode=NIL);
VAR
  mLocalName, S:STRING;
  TreeNode:TTreeNode;
  Ads_Tmp:TADOQuery;
BEGIN
  Ads_Tmp:=TADOQuery.Create(NIL);
  Ads_Tmp.Connection:=FDM.DBConn;
  S:='SELECT [KeyID], [ParentID], [名称] FROM [House2Cash].[dbo].[区栋结构] '
    +' Where [ParentID] ='+FNodeName;
  TRY
    OpenSQL(Ads_Tmp, S);
    WITH Ads_Tmp DO
    BEGIN
      WHILE NOT Eof DO
      BEGIN
        mLocalName:=FieldByName('KeyID').AsString;
        TreeNode:=TTreeView(Node.TreeView).Items.AddChild(Node,
          mLocalName+'='+FieldByName('名称').AsString);
        CreateSubTree(mLocalName, TreeNode);
        Next;
      END;
    END;
  FINALLY
    Ads_Tmp.Close;
    Ads_Tmp.Free;
  END;
END;
       }

PROCEDURE TFDM.CastPowerStr(AStr:STRING);
VAR MList:TStrings;                     //模块列表
  PList:TStrings;                       //权限列表
  M, P:Integer;
  S:STRING;
BEGIN
  MList:=TStringList.Create;
  PList:=TStringList.Create;

  TRY
    StrToStrs('|', AStr, MList);
    FOR M:=0 TO MList.Count-1 DO
    BEGIN
      S:=MList.Strings[M];
      StrToStrs('&', S, PList);
      FOR P:=0 TO PList.Count-1 DO
      BEGIN
        CASE P OF
          0:WorkUser.Power[M].Power.input:=IntToBoolExt(PList.Strings[P]);
          1:WorkUser.Power[M].Power.audit:=IntToBoolExt(PList.Strings[P]);
          2:WorkUser.Power[M].Power.query:=IntToBoolExt(PList.Strings[P]);
          3:WorkUser.Power[M].Power.print:=IntToBoolExt(PList.Strings[P]);
        END;
      END;
    END;
  FINALLY
    PList.Free;
    MList.Free;
  END;
END;

//创建数据库及数据表
//返回值：0: 失败；
//        1: 成功；

FUNCTION TFDM.InitDbsEnvir:Integer;
VAR
  sConnStr:STRING;

  FUNCTION DBConnect:Integer;
  BEGIN
    sConnStr:=GetConnStr('master', YDB.ServerName, YDB.Pass, YDB.User);
    Result:=ConnectADO(DBConn, sConnStr);
  END;

BEGIN
  TRY
    //测试连接SQL Server, 失败则连接参数错
    Result:=DBConnect;
    IF Result=0 THEN
    BEGIN
      Result:=DBConnect;
    END;
  EXCEPT
    Result:=0;
  END;
END;

FUNCTION TFDM.CreateTables:Integer;
VAR
  sTableName:STRING;
  iErrCount:Integer;

  FUNCTION CreateTable(S, sDrop, W:STRING):Boolean;
  VAR
    sErr:STRING;
  BEGIN
    Result:=(ExecSQL(ADOCommand, S)>0);
    IF NOT Result THEN
    BEGIN
      ExecSQL(ADOCommand, sDrop);
      sErr:='创建'+W+'失败!'#13#10'请检查数据库...';
      TRY
        Logs.Add(S+#13#10+sErr);
      EXCEPT
      END;
    END;
  END;

BEGIN
  Result:=0;
  TRY
    TRY
      sTableName:='Acc_Rate';
      //if Tables.IndexOf(sTableName) < 0 then
      //begin
      //  if not CreateTable(aqCreateAccRate.SQL.Text, '', '计息临时主表') then Exit;
      //end;
    EXCEPT
    END;
    Result:=1;
  FINALLY
    Logs.SaveToFile(AppPath.Temp+'CreateTables.log');
    Logs.Clear;
  END;
END;

PROCEDURE TFDM.DataModuleCreate(Sender:TObject);
BEGIN
  VerInfo:=TRzVersionInfo.Create(Application);
  VerInfo.FilePath:=Application.ExeName;

  DBINI:=TRzRegIniFile.Create(Application);
  Logs:=TStringList.Create;
  Tables:=TStringList.Create;

  AppPath.Self:=ExtractFilePath(ParamStr(0));
  AppPath.Backup:=AppPath.Self+'Backup\';
  AppPath.Report:=AppPath.Self+'Reports\';
  AppPath.Temp:=AppPath.Self+'Temp\';
  AppPath.BackImage:=AppPath.Self+'backpic\';

  XFunc.WriteRegStr('AppPath', AppPath.Self);

  IF NOT DirectoryExists(AppPath.Backup) THEN
    CreateDirectory(PChar(AppPath.Backup), NIL);
  IF NOT DirectoryExists(AppPath.Temp) THEN
    CreateDirectory(PChar(AppPath.Temp), NIL);
  IF NOT DirectoryExists(AppPath.Report) THEN
    CreateDirectory(PChar(AppPath.Report), NIL);
  IF NOT DirectoryExists(AppPath.BackImage) THEN
    CreateDirectory(PChar(AppPath.BackImage), NIL);
END;

FUNCTION TFDM.OpenTables:Integer;
BEGIN
END;

FUNCTION TFDM.GetJDStr(aid:STRING):STRING;
BEGIN
  IF aid='1' THEN
    Result:='贷'
  ELSE
    Result:='借';
END;

FUNCTION TFDM.GetUserAccess:Boolean;
BEGIN
  Result:=aqOper.Active;
  TRY
    IF Result THEN
    BEGIN
      Result:=FDM.aqOper.Locate('登录名', WorkUser.login, []);
      IF Result THEN
        WorkUser.sPower:=Trim(aqOper.FieldByName('权限').AsString)
      ELSE
        WorkUser.sPower:='';
    END;
  EXCEPT
    Result:=false;
  END;
END;

FUNCTION TFDM.CheckUserAccOne(Acc:STRING):Boolean;
BEGIN
  Result:=false;
  IF WorkUser.sPower='' THEN exit;
  IF Trim(Acc)='' THEN exit;

  Acc:=Acc+'=1';

  Result:=(pos(Acc, WorkUser.sPower)>0);