v001_to_v002.sql

CRM源码This file describes some issues that should be implemented in future and how it should be imple

if exists (select * from dbo.sysobjects where id = object_id(N'[QX_TAB]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [QX_TAB]
GO

create table dbo.QX_SUBFOCUS (
  SUBFOCUS_ID          VARCHAR(255)	NOT NULL,
  FOCUS_ID             VARCHAR(255),
  SUBFOCUS_ORDER       INT,
constraint PK_QX_SUBFOCUS primary key (SUBFOCUS_ID)
)
go

create index IDX_QX_SUBFOCUS_FOCUS on dbo.QX_SUBFOCUS (FOCUS_ID)
create index IDX_QX_SUBFOCUS_ORDER on dbo.QX_SUBFOCUS (SUBFOCUS_ORDER)
go


create table dbo.QX_TAB (
  TAB_ID               VARCHAR(255)	NOT NULL,
  SUBFOCUS_ID          VARCHAR(255),
  TAB_ORDER            INT,
constraint PK_QX_TAB primary key (TAB_ID)
)
go

create index IDX_QX_TAB_SUBFOCUS on dbo.QX_TAB (SUBFOCUS_ID)
create index IDX_QX_TAB_ORDER on dbo.QX_TAB (TAB_ORDER)
go

insert into QX_CAP_TYPE values (7, 'Sub Focus');
go

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[QX_RIGHT]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[QX_RIGHT]
GO
create view dbo.QX_RIGHT as
    select distinct
        cast(PR.SECURITY_ID as VARCHAR) + '_' + FR.FORM_ID as PKEY,
        PR.SECURITY_ID,
        PR.PERMISSION_ID,
        FC.FOCUS_ID,
        TB.TAB_ID,
        FR.FORM_ID,
        FS.ACCESS_LEVEL,
        SC.NAME AS ROLE
    from
        QX_FOCUS FC
        JOIN    QX_SUBFOCUS SF  on (FC.FOCUS_ID = SF.FOCUS_ID)
        JOIN    QX_TAB TB  on (SF.SUBFOCUS_ID = TB.SUBFOCUS_ID)
        JOIN    QX_FORM FR on (TB.TAB_ID = FR.TAB_ID)
        LEFT OUTER JOIN	   QX_FORMSET FS    on (FR.FORM_ID = FS.FORM_ID)
        LEFT OUTER JOIN    QX_PERMISSION PR on (FS.PERMISSION_ID = PR.PERMISSION_ID)
        LEFT OUTER JOIN    QX_SECURITY SC   on (PR.SECURITY_ID = SC.SECURITY_ID)
go