dm.pas

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

  InvTables[34].chsname:='GSP质量管理的药品入库质量验收单子表';
  InvTables[34].keyField:='cInvCode';
  InvTables[35].realName:='GSP_VouchUQAudit';
  InvTables[35].chsname:='GSP质量管理的不合格药品报损审批表';
  InvTables[35].keyField:='cInvCode';
  InvTables[36].realName:='GSP_VouchZYYHFile';
  InvTables[36].chsname:='GSP质量管理的重点药品养护档案主表';
  InvTables[36].keyField:='cInvCode';

  InvTables[37].realName:='ProductStructure';
  InvTables[37].chsname:='基础档案的产品结构父项';
  InvTables[37].keyField:='CPSPCode';
  InvTables[38].realName:='ProductStructures';
  InvTables[38].chsname:='基础档案的产品结构子项';
  InvTables[38].keyField:='cPSCode';
  InvTables[39].realName:='CA_EnMMC';
  InvTables[39].chsname:='成本核算的月末部门共用材料盘点表';
  InvTables[39].keyField:='cMatID';
  InvTables[40].realName:='CA_EnMOM';
  InvTables[40].chsname:='成本核算的月末在产品原材料盘点表';
  InvTables[40].keyField:='cMatID';
  InvTables[41].realName:='CA_MaBSW';
  InvTables[41].chsname:='成本核算的材料及外购半成品耗用统计表';
  InvTables[41].keyField:='cMatID';
  InvTables[42].realName:='CA_MatDf';
  InvTables[42].chsname:='成本核算的材料表';
  InvTables[42].keyField:='cMatID';
  InvTables[43].realName:='CostJustVouch';
  InvTables[43].chsname:='存货核算的计划价或售价调整单主表';
  InvTables[43].keyField:='cInvCode';
  InvTables[44].realName:='CostJustVouchs';
  InvTables[44].chsname:='存货核算的计划价或售价调整单子表';
  InvTables[44].keyField:='cInvCode';
  InvTables[45].realName:='fa_ZWVouchers';
  InvTables[45].chsname:='固定资产的固定资产折旧分配凭证临时表';
  InvTables[45].keyField:='citem_id';
  InvTables[46].realName:='FD_AccSet';
  InvTables[46].chsname:='资金管理的账户科目定义表';
  InvTables[46].keyField:='citem_id';
  InvTables[47].realName:='GL_accass';
  InvTables[47].chsname:='总账的辅助总账';
  InvTables[47].keyField:='citem_id';
  InvTables[48].realName:='GL_accvouch';
  InvTables[48].chsname:='总账的凭证及明细账';
  InvTables[48].keyField:='citem_id';
  InvTables[49].realName:='GL_bautotran';
  InvTables[49].chsname:='总账的总账自动转账定义表';
  InvTables[49].keyField:='citem_id';
  InvTables[50].realName:='GL_bfreq';
  InvTables[50].chsname:='总账的总账常用凭证表';
  InvTables[50].keyField:='citem_id';
  InvTables[51].realName:='GL_mitemused';
  InvTables[51].chsname:='总账的总账常用项目表';
  InvTables[51].keyField:='citem_id';
  InvTables[52].realName:='IA_DecReadyHead';
  InvTables[52].chsname:='存货核算的跌价准备科目表';
  InvTables[52].keyField:='cInvCode';
  InvTables[53].realName:='IA_DecReadys';
  InvTables[53].chsname:='存货核算的跌价准备单子表';
  InvTables[53].keyField:='cInvCode';
  InvTables[54].realName:='IA_InvTData';
  InvTables[54].chsname:='存货核算的存货临时表';
  InvTables[54].keyField:='InvCode';
  InvTables[55].realName:='IA_MaxMinCostDif';
  InvTables[55].chsname:='存货核算的存货单价差异率设置表';
  InvTables[55].keyField:='cinvcode';
  InvTables[56].realName:='IA_MoneyPlan';
  InvTables[56].chsname:='存货核算的存货资金占用规划表';
  InvTables[56].keyField:='cInvCode';
  InvTables[57].realName:='IA_OppHead';
  InvTables[57].chsname:='存货核算的存货对方科目设置表';
  InvTables[57].keyField:='cInvCode';
  InvTables[58].realName:='JustInVouchs';
  InvTables[58].chsname:='存货核算的出入库调整单子表';
  InvTables[58].keyField:='cInvCode';
  InvTables[59].realName:='PP_ForecastDetails';
  InvTables[59].chsname:='采购计划的采购计划市场预测子表';
  InvTables[59].keyField:='cInvCode';
  InvTables[60].realName:='PP_PODetails';
  InvTables[60].chsname:='采购计划的生产订单子表';
  InvTables[60].keyField:='cInvCode';
  InvTables[61].realName:='PP_POMain';
  InvTables[61].chsname:='采购计划的生产订单主表';
  InvTables[61].keyField:='cInvCode';
  InvTables[62].realName:='PP_PPCDetails';
  InvTables[62].chsname:='采购计划的采购计划子表';
  InvTables[62].keyField:='cInvCode';
  InvTables[63].realName:='PP_PPCQuota';
  InvTables[63].chsname:='采购计划的采购计划配额表';
  InvTables[63].keyField:='cInvCode';
  InvTables[64].realName:='PP_RMRPdetails';
  InvTables[64].chsname:='采购计划的相关需求子计划表';
  InvTables[64].keyField:='cInvCode';
  InvTables[65].realName:='PP_RMRPdetails';
  InvTables[65].chsname:='采购计划的相关需求子计划表';
  InvTables[65].keyField:='cInvCode';
  InvTables[66].realName:='Ap_Detail';
  InvTables[66].chsname:='应收应付的应收应付明细账';
  InvTables[66].keyField:='cInvCode';
  InvTables[67].realName:='Ap_InvCode';
  InvTables[67].chsname:='应收应付的应收应付购销科目定义表';
  InvTables[67].keyField:='cKeyCode';
  InvTables[68].realName:='PM_ItemQCsub';
  InvTables[68].chsname:='项目管理的项目期初子表';
  InvTables[68].keyField:='cCodeMx';
  InvTables[69].realName:='PM_ActQuaInputSub';
  InvTables[69].chsname:='项目管理的专属项目要素汇总表子表';
  InvTables[69].keyField:='cCodeMx';
  InvTables[70].realName:='PM_BudgetSub';
  InvTables[70].chsname:='项目管理的项目编制子表';
  InvTables[70].keyField:='cCodeMx';
  InvTables[71].realName:='Pm_RecordIns';
  InvTables[71].chsname:='项目管理的专属项目结算单';
  InvTables[71].keyField:='cInvCode';
  InvTables[72].realName:='PP_ROPDetails';
  InvTables[72].chsname:='物料需求计划的ROP采购计划子表';
  InvTables[72].keyField:='cInvCode';
  InvTables[73].realName:='Ven_Inv_Price_Rule';
  InvTables[73].chsname:='采购管理的供应商存货价格表主表';
  InvTables[73].keyField:='cInvCode';
  InvTables[74].realName:='qmInspectVouchers';
  InvTables[74].chsname:='质量管理报检单子表';
  InvTables[74].keyField:='cInvcode';
  InvTables[75].realName:='qmCheckVoucher';
  InvTables[75].chsname:='质量管理检验单主表';
  InvTables[75].keyField:='cInvCode';
  InvTables[76].realName:='qmRejectVoucher';
  InvTables[76].chsname:='质量管理不良品处理单主表';
  InvTables[76].keyField:='cInvCode';
  InvTables[77].realName:='qmRejectVouchers';
  InvTables[77].chsname:='质量管理不良品处理单子表';
  InvTables[77].keyField:='cDimInvCode';
  InvTables[78].realName:='CM_Contract_Item_A';
  InvTables[78].chsname:='合同管理的自由态合同标的表';
  InvTables[78].keyField:='strInvoiceID';
  InvTables[79].realName:='CM_Contract_Item_B';
  InvTables[79].chsname:='合同管理的生效态合同标的表';
  InvTables[79].keyField:='strInvoiceID';
  InvTables[80].realName:='CM_Contract_Item_C';
  InvTables[80].chsname:='合同管理的结案态合同标的表';
  InvTables[80].keyField:='strInvoiceID';
  InvTables[81].realName:='CM_Balances';
  InvTables[81].chsname:='合同管理的合同结算单子表';
  InvTables[81].keyField:='cItemID';
END;

FUNCTION TFDM.ModiInvTo2306:Integer;
VAR sSQL:STRING;
BEGIN
  sSQL:='UPDATE ['+CurrAccDBName+'].[dbo].[sysobjects] '
    +' SET [status]=3330 '
    +' FROM ['+CurrAccDBName+'].[dbo].[sysobjects]'
    +' WHERE [id] in(SELECT [constid] FROM ['+CurrAccDBName+'].[dbo].[sysforeignkeys] '
    +' WHERE [rkeyid]=(SELECT [id] FROM ['+CurrAccDBName+'].[dbo].sysobjects WHERE [name]=''Inventory''))';

  TRY
    Result:=ExecSQL(FDM.aqExec, sSQL);
  EXCEPT

  END;
END;

FUNCTION TFDM.ModiInvTo6:Integer;
VAR sSQL:STRING;
BEGIN
  sSQL:='UPDATE ['+CurrAccDBName+'].[dbo].[sysobjects] '
    +' SET [status]=6 '
    +' FROM ['+CurrAccDBName+'].[dbo].[sysobjects]'
    +' WHERE [id] in (SELECT [constid] FROM ['+CurrAccDBName+'].[dbo].[sysforeignkeys] '
    +' WHERE [rkeyid]=(SELECT [id] FROM ['+CurrAccDBName+'].[dbo].sysobjects WHERE [name]=''Inventory''))';

  TRY
    Result:=ExecSQL(FDM.aqExec, sSQL);
  EXCEPT

  END;
END;

FUNCTION TFDM.GetOnePrimaryKey(aTable:STRING):STRING;
VAR sSQL:STRING;
BEGIN
  sSQL:='SELECT * FROM ['+CurrAccDBName+'].[dbo].[sysobjects] '
    +' WHERE [name] in (SELECT [name] '
    +' FROM ['+CurrAccDBName+'].[dbo].[sysindexes] '
    +' WHERE [id]=(SELECT [id] FROM ['+CurrAccDBName+'].[dbo].[sysobjects]'
    +' WHERE [name]=''Inventory''))-- and xtype=''PK'' ';
END;

FUNCTION TFDM.BackupInvFKObject:Integer;
VAR sSQL:STRING;
BEGIN
  sSQL:='SELECT * FROM [UFDATA_888_2006].[dbo].[sysobjects] '
    +' WHERE [id] in(SELECT [constid] '
    +' INTO '+sInvFKObjectTempTable
    +' FROM ['+CurrAccDBName+'].[dbo].[sysforeignkeys] '
    +' WHERE rkeyid=(SELECT [id] FROM ['+CurrAccDBName+'].[dbo].sysobjects WHERE [name]=''Inventory'')) '
    +' ORDER BY [name] ';

  TRY
    Result:=ExecSQL(FDM.aqExec, sSQL);
  EXCEPT
  END;
END;

FUNCTION TFDM.GetInvFKs:Integer;
VAR sSQL:STRING;
  k:Integer;
BEGIN
  sSQL:='SELECT * FROM [UFDATA_888_2006].[dbo].[sysobjects] '
    +' WHERE [id] in(SELECT [constid] '
    +' INTO '+sInvFKObjectTempTable
    +' FROM ['+CurrAccDBName+'].[dbo].[sysforeignkeys] '
    +' WHERE rkeyid=(SELECT [id] FROM ['+CurrAccDBName+'].[dbo].sysobjects WHERE [name]=''Inventory'')) '
    +' ORDER BY [name] ';

  TRY
    Result:=ExecSQL(FDM.aqTemp, sSQL);
    SetLength(InvFK, Result);

    k:=0;
    WHILE NOT FDM.aqTemp.Eof DO
    BEGIN
      InvFK[k].fkname:=Trim(FDM.aqTemp.FieldByName('name').AsString);
      InvFK[k].status:=FDM.aqTemp.FieldByName('status').AsInteger;

      Inc(k);
      FDM.aqTemp.Next;
    END;
  FINALLY
    FDM.aqTemp.Close;
  END;
END;

FUNCTION TFDM.DeleteInvFks:Integer;
VAR sSQL:STRING;
  k:Integer;
BEGIN
  FDM.GetInvFKs;

  Result:=High(InvFK);
  FOR k:=0 TO Result DO
  BEGIN
    sSQL:='ALTER TABLE ['+CurrAccDBName+'].[dbo].[Inventory] '
      +' DROP CONSTRAINT '+InvFK[k].fkname;
    TRY
      ExecSQL(FDM.aqExec, sSQL);
    EXCEPT

    END;
  END;
END;

FUNCTION TFDM.CreateInvFKs:Integer;
VAR sSQL:STRING;
  k:Integer;
BEGIN
  FDM.GetInvFKs;

  Result:=High(InvFK);
  FOR k:=0 TO Result DO
  BEGIN
    sSQL:='ALTER TABLE ['+CurrAccDBName+'].[dbo].[Inventory] '
      +' DROP CONSTRAINT '+InvFK[k].fkname;
    TRY
      ExecSQL(FDM.aqExec, sSQL);
    EXCEPT

    END;
  END;
END;

FUNCTION TFDM.DisableInvFKs(AName:STRING):Integer;
VAR sSQL:STRING;
  k:Integer;
BEGIN
  sSQL:='select H1.[name] as fkname, H2.[name] as rtblName '
    +' from (SELECT * FROM ['+CurrAccDBName+'].[dbo].[sysobjects] A, '
    +'          (SELECT * FROM ['+CurrAccDBName+'].[dbo].[sysforeignkeys] '
    +'           where [rkeyid]=(SELECT [id] FROM ['+CurrAccDBName+'].[dbo].sysobjects WHERE [name]=''Inventory'')) B '
    +'       where A.[id]=B.[constid])H1, '
    +'      (select * from ['+CurrAccDBName+'].dbo.sysobjects C, '
    +'          (SELECT * FROM ['+CurrAccDBName+'].[dbo].[sysreferences] '
    +'           where rkeyid=(SELECT [id] FROM ['+CurrAccDBName+'].[dbo].sysobjects WHERE [name]=''Inventory'')) D '
    +'       where C.[id]=D.[fkeyid])H2  '
    +' where H1.[constid]=H2.[constid] ';
  sSQL:='select H1.[name] as fkname, H2.[name] as rtblName '
    +' from (SELECT * FROM [dbo].[sysobjects] A, '
    +'          (SELECT * FROM [dbo].[sysforeignkeys] '
    +'           where [rkeyid]=(SELECT [id] FROM [dbo].sysobjects WHERE [name]='+Quotedstr(AName)+')) B '
    +'       where A.[id]=B.[constid])H1, '
    +'      (select * from dbo.sysobjects C, '
    +'          (SELECT * FROM [dbo].[sysreferences] '
    +'           where rkeyid=(SELECT [id] FROM [dbo].sysobjects WHERE [name]='+Quotedstr(AName)+')) D '
    +'       where C.[id]=D.[fkeyid])H2  '
    +' where H1.[constid]=H2.[constid] ';

  TRY
    Result:=OpenQuery(FDM.aqTemp, sSQL);

    FOR k:=1 TO Result DO
    BEGIN
      sSQL:='ALTER TABLE dbo.'
        +FDM.aqTemp.FieldByName('rtblName').AsString+' NOCHECK CONSTRAINT '
        +FDM.aqTemp.FieldByName('fkname').AsString;

      ExecSQL(FDM.aqExec, sSQL);

      FDM.aqTemp.Next;
    END;
  FINALLY
    FDM.aqTemp.Close;
  END;
END;

FUNCTION TFDM.EnableInvFKs(AName:STRING; bFlag:Boolean=True):Integer;
VAR sSQL:STRING;
  k:Integer;
BEGIN
  sSQL:='select H1.[name] as fkname, H2.[name] as rtblName '
    +' from (SELECT * FROM [dbo].[sysobjects] A, '
    +'          (SELECT * FROM [dbo].[sysforeignkeys] '
    +'           where [rkeyid]=(SELECT [id] FROM [dbo].sysobjects WHERE [name]='+Quotedstr(AName)+')) B '
    +'       where A.[id]=B.[constid])H1, '
    +'      (select * from dbo.sysobjects C, '
    +'          (SELECT * FROM [dbo].[sysreferences] '
    +'           where rkeyid=(SELECT [id] FROM [dbo].sysobjects WHERE [name]='+Quotedstr(AName)+')) D '
    +'       where C.[id]=D.[fkeyid])H2  '
    +' where H1.[constid]=H2.[constid] ';

  TRY
    Result:=OpenQuery(FDM.aqTemp, sSQL);

    FOR k:=1 TO Result DO
    BEGIN
      IF bFlag THEN
        sSQL:='ALTER TABLE dbo.'
          +FDM.aqTemp.FieldByName('rtblName').AsString+' CHECK CONSTRAINT '
          +FDM.aqTemp.FieldByName('fkname').AsString
      ELSE
        sSQL:='ALTER TABLE dbo.'
          +FDM.aqTemp.FieldByName('rtblName').AsString+' NOCHECK CONSTRAINT '
          +FDM.aqTemp.FieldByName('fkname').AsString;

      ExecSQL(FDM.aqExec, sSQL);

      FDM.aqTemp.Next;
    END;
  FINALLY
    FDM.aqTemp.Close;
  END;
END;

PROCEDURE TFDM.DelInvPrimaryKey;
VAR sSQL:STRING;
BEGIN
  sSQL:='ALTER TABLE ['+CurrAccDBName+'].[dbo].[Inventory] '
    +' DROP CONSTRAINT [aaaaaInventory_PK]';

  TRY
    ExecSQL(FDM.aqExec, sSQL);
  EXCEPT
  END;
END;

PROCEDURE TFDM.AddInvPrimaryKey;
VAR sSQL:STRING;
BEGIN
  sSQL:='ALTER TABLE ['+CurrAccDBName+'].[dbo].[Inventory] '
    +' ADD CONSTRAINT [aaaaaInventory_PK] PRIMARY KEY  NONCLUSTERED '
    +' ([cInvCode])  ON [PRIMARY] ';

  TRY
    ExecSQL(FDM.aqExec, sSQL);
  EXCEPT
  END;
END;

END.