v022_to_v023.sql

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

insert into QX_TIMEZONE values (99, 'Indian/Christmas', '(GMT+07:00) Christmas Island Time',420);
insert into QX_TIMEZONE values (100, 'Indian/Cocos', '(GMT+06:30) Cocos Islands Time',390);
insert into QX_TIMEZONE values (101, 'Indian/Kerguelen', '(GMT+05:00) French Southern & Antarctic Lands Time',300);
insert into QX_TIMEZONE values (102, 'Indian/Mahe', '(GMT+04:00) Seychelles Time',240);
insert into QX_TIMEZONE values (103, 'Indian/Maldives', '(GMT+05:00) Maldives Time',300);
insert into QX_TIMEZONE values (104, 'Indian/Mauritius', '(GMT+04:00) Mauritius Time',240);
insert into QX_TIMEZONE values (105, 'Indian/Reunion', '(GMT+04:00) Reunion Time',240);
insert into QX_TIMEZONE values (106, 'MIT', '(GMT-11:00) West Samoa Time',-660);
insert into QX_TIMEZONE values (107, 'Pacific/Chatham', '(GMT+12:45) Chatham Standard Time',765);
insert into QX_TIMEZONE values (108, 'Pacific/Easter', '(GMT-06:00) Easter Is. Time',-360);
insert into QX_TIMEZONE values (109, 'Pacific/Efate', '(GMT+11:00) Vanuatu Time',660);
insert into QX_TIMEZONE values (110, 'Pacific/Enderbury', '(GMT+13:00) Phoenix Is. Time',780);
insert into QX_TIMEZONE values (111, 'Pacific/Fakaofo', '(GMT-10:00) Tokelau Time',-600);
insert into QX_TIMEZONE values (112, 'Pacific/Fiji', '(GMT+12:00) Fiji Time',720);
insert into QX_TIMEZONE values (113, 'Pacific/Funafuti', '(GMT+12:00) Tuvalu Time',720);
insert into QX_TIMEZONE values (114, 'Pacific/Galapagos', '(GMT-06:00) Galapagos Time',-360);
insert into QX_TIMEZONE values (115, 'Pacific/Gambier', '(GMT-09:00) Gambier Time',-540);
insert into QX_TIMEZONE values (116, 'Pacific/Guadalcanal', '(GMT+11:00) Solomon Is. Time',660);
insert into QX_TIMEZONE values (117, 'Pacific/Guam', '(GMT+10:00) Chamorro Standard Time',600);
insert into QX_TIMEZONE values (118, 'Pacific/Kiritimati', '(GMT+14:00) Line Is. Time',840);
insert into QX_TIMEZONE values (119, 'Pacific/Kosrae', '(GMT+11:00) Kosrae Time',660);
insert into QX_TIMEZONE values (120, 'Pacific/Majuro', '(GMT+12:00) Marshall Islands Time',720);
insert into QX_TIMEZONE values (121, 'Pacific/Marquesas', '(GMT-09:30) Marquesas Time',-570);
insert into QX_TIMEZONE values (122, 'Pacific/Nauru', '(GMT+12:00) Nauru Time',720);
insert into QX_TIMEZONE values (123, 'Pacific/Niue', '(GMT-11:00) Niue Time',-660);
insert into QX_TIMEZONE values (124, 'Pacific/Norfolk', '(GMT+11:30) Norfolk Time',690);
insert into QX_TIMEZONE values (125, 'Pacific/Noumea', '(GMT+11:00) New Caledonia Time',660);
insert into QX_TIMEZONE values (126, 'Pacific/Pago_Pago', '(GMT-11:00) Samoa Standard Time',-660);
insert into QX_TIMEZONE values (127, 'Pacific/Palau', '(GMT+09:00) Palau Time',540);
insert into QX_TIMEZONE values (128, 'Pacific/Pitcairn', '(GMT-08:00) Pitcairn Standard Time',-480);
insert into QX_TIMEZONE values (129, 'Pacific/Ponape', '(GMT+11:00) Ponape Time',660);
insert into QX_TIMEZONE values (130, 'Pacific/Port_Moresby', '(GMT+10:00) Papua New Guinea Time',600);
insert into QX_TIMEZONE values (131, 'Pacific/Rarotonga', '(GMT-10:00) Cook Is. Time',-600);
insert into QX_TIMEZONE values (132, 'Pacific/Tahiti', '(GMT-10:00) Tahiti Time',-600);
insert into QX_TIMEZONE values (133, 'Pacific/Tarawa', '(GMT+12:00) Gilbert Is. Time',720);
insert into QX_TIMEZONE values (134, 'Pacific/Tongatapu', '(GMT+13:00) Tonga Time',780);
insert into QX_TIMEZONE values (135, 'Pacific/Truk', '(GMT+10:00) Truk Time',600);
insert into QX_TIMEZONE values (136, 'Pacific/Wake', '(GMT+12:00) Wake Time',720);
insert into QX_TIMEZONE values (137, 'Pacific/Wallis', '(GMT+12:00) Wallis & Futuna Time',720);
insert into QX_TIMEZONE values (138, 'UTC', '(GMT-00:00) Coordinated Universal Time',0);
go

create table dbo.QX_COUNTRY (
  PKEY                 INT          NOT NULL,
  COUNTRY_ID           VARCHAR(3)   NOT NULL,
  NAME                 VARCHAR(255) NOT NULL,
  DEF_LANG_ID          CHAR(2),
  DEF_TIMEZONE_ID      VARCHAR(255),
constraint PK_QX_COUNTRY primary key (PKEY),
--constraint FK_QX_COUNTRY_QX_LANGUAGE    foreign key (DEF_LANG_ID)    references QX_LANGUAGE (LANGUAGE_ID),
--constraint FK_QX_COUNTTRY_QX_TIMEZONE    foreign key (DEF_TIMEZONE_ID)    references QX_TIMEZONE (TIMEZONE_ID)
)
go

create index IDX_QX_COUNTRY_LANGUAGE on dbo.QX_COUNTRY (DEF_LANG_ID)
create index IDX_QX_COUNTRY_TIMEZONE on dbo.QX_COUNTRY (DEF_TIMEZONE_ID)
go

insert into QX_COUNTRY values (0, 'US', 'United States', 'en', 'America/Belize');
insert into QX_COUNTRY values (1, 'DE', 'Germany', 'de', 'Africa/Algiers');
insert into QX_COUNTRY values (2, 'FR', 'France', 'de', 'Africa/Algiers');
insert into QX_COUNTRY values (3, 'IT', 'Italy', 'it', 'Africa/Algiers');
insert into QX_COUNTRY values (4, 'ES', 'Spain', 'es', 'UTC');
insert into QX_COUNTRY values (5, 'PT', 'Portugal', 'pt', 'UTC');
insert into QX_COUNTRY values (6, 'GB', 'United Kingdom', 'en', 'UTC');
insert into QX_COUNTRY values (7, 'IE', 'Ireland', 'en', 'UTC');
go

CREATE TABLE dbo.QX_TIME_PATTERN (
  PKEY             INT          NOT NULL,
  TIME_PATTERN_ID  VARCHAR(255) NOT NULL
constraint PK_QX_TIME_PATTERN primary key (PKEY),
)
GO

INSERT INTO dbo.QX_TIME_PATTERN VALUES (0, 'H:mm');
INSERT INTO dbo.QX_TIME_PATTERN VALUES (1, 'h:mm a');
INSERT INTO dbo.QX_TIME_PATTERN VALUES (2, 'HH:mm:ss');
GO

CREATE TABLE dbo.QX_DATE_PATTERN (
  PKEY             INT          NOT NULL,
  DATE_PATTERN_ID  VARCHAR(255) NOT NULL
constraint PK_QX_DATE_PATTERN primary key (PKEY),
)
GO

INSERT INTO dbo.QX_DATE_PATTERN VALUES (0, 'd MMM y');
INSERT INTO dbo.QX_DATE_PATTERN VALUES (1, 'd MMM yyyy');
INSERT INTO dbo.QX_DATE_PATTERN VALUES (2, 'dd MMM yy');
INSERT INTO dbo.QX_DATE_PATTERN VALUES (3, 'dd MMM yyyy');
INSERT INTO dbo.QX_DATE_PATTERN VALUES (4, 'dd/MM/yy');
INSERT INTO dbo.QX_DATE_PATTERN VALUES (5, 'ddMMMyy');
INSERT INTO dbo.QX_DATE_PATTERN VALUES (6, 'ddMMMyyyy');
INSERT INTO dbo.QX_DATE_PATTERN VALUES (7, 'dMMMyy');
INSERT INTO dbo.QX_DATE_PATTERN VALUES (8, 'M/d/yy');
INSERT INTO dbo.QX_DATE_PATTERN VALUES (9, 'M/d/yyyy');
INSERT INTO dbo.QX_DATE_PATTERN VALUES (10, 'MM/dd/yy');
INSERT INTO dbo.QX_DATE_PATTERN VALUES (11, 'MM/dd/yyyy');
INSERT INTO dbo.QX_DATE_PATTERN VALUES (12, 'MMddyy');
INSERT INTO dbo.QX_DATE_PATTERN VALUES (13, 'MMddyyyy');
INSERT INTO dbo.QX_DATE_PATTERN VALUES (14, 'MMM d, yyyy');
INSERT INTO dbo.QX_DATE_PATTERN VALUES (15, 'MMMM d yy');
INSERT INTO dbo.QX_DATE_PATTERN VALUES (16, 'MMMM d yyyy');
INSERT INTO dbo.QX_DATE_PATTERN VALUES (17, 'MMMM dd yy');
INSERT INTO dbo.QX_DATE_PATTERN VALUES (18, 'MMMM dd yyyy');
GO

drop view dbo.QX_DD_DATEPOS
go
create view dbo.QX_DD_DATEPOS as
    select
        ENUM_VALUE as PKEY,
        ENUM_LABEL as NAME
    from QX_FIELDINFO
    where (TABLE_NAME = 'qx_user_settings') and (COLUMN_NAME = 'date_position')
go

delete from QX_FIELDINFO where TABLE_NAME = 'qx_user_prop'
go
insert into QX_FIELDINFO values ('qx_user_settings', 'date_position', 14, 0, 'First');
insert into QX_FIELDINFO values ('qx_user_settings', 'date_position', 14, 1, 'Second');
go

drop table dbo.QX_USER_SETTINGS
go

create table dbo.QX_USER_SETTINGS (
  PKEY        INT  NOT NULL,
  USER_ID     INT  NOT NULL,
  LANG        INT  NULL,
  COUNTRY     INT  NULL,
  TIMEZONE    INT  NULL,
  DPATTERN    INT  NULL,
  TPATTERN    INT  NULL,
  DPOS        INT  NULL,
constraint PK_USER_SETTINGS primary key (PKEY)
)
go

create unique index IDX_USER_SETTINGS_USER_ID on dbo.QX_USER_SETTINGS (USER_ID)
go


UPDATE QX_SYS_PROP SET PROP_VAL = '023' WHERE (PROP_ID = 'DB_CORE_VERSION');
go