upgrade2.9b-3.0.sql

来自「java开源项目源代码」· SQL 代码 · 共 904 行 · 第 1/5 页

INSERT INTO accountsection (sectionid, sectionname) VALUES (30, 'Amounts Payable');
INSERT INTO accountsection (sectionid, sectionname) VALUES (50, 'Financed By');

ALTER TABLE `accountgroups` CHANGE `sectioninaccounts` `sectioninaccounts` INT( 11 ) DEFAULT '0' NOT NULL;
ALTER TABLE `accountgroups` ADD INDEX ( `sectioninaccounts` );

ALTER TABLE `accountgroups` ADD CONSTRAINT `accountgroups_ibfk_1` FOREIGN KEY ( `sectioninaccounts` ) REFERENCES `accountsection` ( `sectionid` );


CREATE TABLE securityroles (
    secroleid int NOT NULL auto_increment,
    secrolename text NOT NULL,
    PRIMARY KEY(secroleid)
)TYPE=INNODB;


INSERT INTO securityroles VALUES (1, 'Inquiries/Order Entry');
INSERT INTO securityroles VALUES (2, 'Manufac/Stock Admin');
INSERT INTO securityroles VALUES (3, 'Purchasing Officer');
INSERT INTO securityroles VALUES (4, 'AP Clerk');
INSERT INTO securityroles VALUES (5, 'AR Clerk');
INSERT INTO securityroles VALUES (6, 'Accountant');
INSERT INTO securityroles VALUES (7, 'Customer Log On Only');
INSERT INTO securityroles VALUES (8, 'System Administrator');


CREATE TABLE securitytokens (
    tokenid integer NOT NULL,
    tokenname text NOT NULL,
    PRIMARY KEY (tokenid)
)TYPE=INNODB;

INSERT INTO securitytokens VALUES (1, 'Order Entry/Inquiries customer access only');
INSERT INTO securitytokens VALUES (2, 'Basic Reports and Inquiries with selection options');
INSERT INTO securitytokens VALUES (3, 'Credit notes and AR management');
INSERT INTO securitytokens VALUES (4, 'Purchasing data/PO Entry/Reorder Levels');
INSERT INTO securitytokens VALUES (5, 'Accounts Payable');
INSERT INTO securitytokens VALUES (6, 'Not Used');
INSERT INTO securitytokens VALUES (7, 'Bank Reconciliations');
INSERT INTO securitytokens VALUES (8, 'General ledger reports/inquiries');
INSERT INTO securitytokens VALUES (9, 'Not Used');
INSERT INTO securitytokens VALUES (10, 'General Ledger Maintenance, stock valuation & Configuration');
INSERT INTO securitytokens VALUES (11, 'Inventory Management and Pricing');
INSERT INTO securitytokens VALUES (12, 'Unknown');
INSERT INTO securitytokens VALUES (13, 'Unknown');
INSERT INTO securitytokens VALUES (14, 'Unknown');
INSERT INTO securitytokens VALUES (15, 'User Management and System Administration');


CREATE TABLE securitygroups (
    secroleid integer NOT NULL,
    tokenid integer NOT NULL,
    PRIMARY KEY (secroleid, tokenid),
    KEY (secroleid),
    KEY (tokenid)
) ENGINE=INNODB;


INSERT INTO securitygroups VALUES (1, 1);
INSERT INTO securitygroups VALUES (1, 2);
INSERT INTO securitygroups VALUES (2, 1);
INSERT INTO securitygroups VALUES (2, 2);
INSERT INTO securitygroups VALUES (2, 11);
INSERT INTO securitygroups VALUES (3, 1);
INSERT INTO securitygroups VALUES (3, 2);
INSERT INTO securitygroups VALUES (3, 3);
INSERT INTO securitygroups VALUES (3, 4);
INSERT INTO securitygroups VALUES (3, 5);
INSERT INTO securitygroups VALUES (3, 11);
INSERT INTO securitygroups VALUES (4, 1);
INSERT INTO securitygroups VALUES (4, 2);
INSERT INTO securitygroups VALUES (4, 5);
INSERT INTO securitygroups VALUES (5, 1);
INSERT INTO securitygroups VALUES (5, 2);
INSERT INTO securitygroups VALUES (5, 3);
INSERT INTO securitygroups VALUES (5, 11);
INSERT INTO securitygroups VALUES (6, 1);
INSERT INTO securitygroups VALUES (6, 2);
INSERT INTO securitygroups VALUES (6, 3);
INSERT INTO securitygroups VALUES (6, 4);
INSERT INTO securitygroups VALUES (6, 5);
INSERT INTO securitygroups VALUES (6, 6);
INSERT INTO securitygroups VALUES (6, 7);
INSERT INTO securitygroups VALUES (6, 8);
INSERT INTO securitygroups VALUES (6, 9);
INSERT INTO securitygroups VALUES (6, 10);
INSERT INTO securitygroups VALUES (6, 11);
INSERT INTO securitygroups VALUES (7, 1);
INSERT INTO securitygroups VALUES (8, 1);
INSERT INTO securitygroups VALUES (8, 2);
INSERT INTO securitygroups VALUES (8, 3);
INSERT INTO securitygroups VALUES (8, 4);
INSERT INTO securitygroups VALUES (8, 5);
INSERT INTO securitygroups VALUES (8, 6);
INSERT INTO securitygroups VALUES (8, 7);
INSERT INTO securitygroups VALUES (8, 8);
INSERT INTO securitygroups VALUES (8, 9);
INSERT INTO securitygroups VALUES (8, 10);
INSERT INTO securitygroups VALUES (8, 11);
INSERT INTO securitygroups VALUES (8, 12);
INSERT INTO securitygroups VALUES (8, 13);
INSERT INTO securitygroups VALUES (8, 14);
INSERT INTO securitygroups VALUES (8, 15);


ALTER TABLE securitygroups ADD CONSTRAINT securitygroups_secroleid_fk FOREIGN KEY (secroleid) REFERENCES securityroles(secroleid);

ALTER TABLE securitygroups ADD CONSTRAINT securitygroups_tokenid_fk FOREIGN KEY (tokenid) REFERENCES securitytokens(tokenid);

UPDATE www_users SET fullaccess = fullaccess + 1;

CREATE TABLE paymentmethods (
    paymentid tinyint NOT NULL auto_increment,
    paymentname varchar(15) NOT NULL,
    paymenttype integer DEFAULT 1 NOT NULL,
    receipttype integer DEFAULT 1 NOT NULL,
    PRIMARY KEY (paymentid)
)ENGINE=Innodb;

INSERT INTO paymentmethods VALUES (1, 'Cheque', 1, 1);
INSERT INTO paymentmethods VALUES (2, 'Cash', 1, 1);
INSERT INTO paymentmethods VALUES (3, 'Direct Credit', 1, 1);

CREATE TABLE unitsofmeasure (
	unitid tinyint NOT NULL auto_increment,
	unitname varchar(15) NOT NULL,
	PRIMARY KEY (unitid)
)ENGINE=Innodb;

INSERT INTO unitsofmeasure VALUES (2, 'metres');
INSERT INTO unitsofmeasure VALUES (3, 'kgs');
INSERT INTO unitsofmeasure VALUES (4, 'litres');
INSERT INTO unitsofmeasure VALUES (5, 'length');
INSERT INTO unitsofmeasure VALUES (6, 'pack');
INSERT INTO unitsofmeasure VALUES (1, 'each');

CREATE TABLE config(
confname varchar( 35 ) NOT NULL ,
confvalue text NOT NULL ,
PRIMARY KEY ( confname )
)ENGINE=Innodb;

INSERT INTO config VALUES('DefaultDateFormat','d/m/Y');
INSERT INTO config VALUES('DefaultTheme','fresh');
INSERT INTO config VALUES('PastDueDays1','30');
INSERT INTO config VALUES('PastDueDays2','60');
INSERT INTO config VALUES('DefaultCreditLimit','1000');
INSERT INTO config VALUES('Show_Settled_LastMonth','1');
INSERT INTO config VALUES('RomalpaClause','Ownership will not pass to the buyer until the goods have been paid for in full.');
INSERT INTO config VALUES('QuickEntries','10');
INSERT INTO config VALUES('DispatchCutOffTime','14');
INSERT INTO config VALUES('AllowSalesOfZeroCostItems','0');
INSERT INTO config VALUES('CreditingControlledItems_MustExist','0');
INSERT INTO config VALUES('DefaultPriceList','WS');
INSERT INTO config VALUES('Default_Shipper','1');
INSERT INTO config VALUES('DoFreightCalc','0');
INSERT INTO config VALUES('FreightChargeAppliesIfLessThan','1000');
INSERT INTO config VALUES('DefaultTaxLevel','1');
INSERT INTO config VALUES('TaxAuthorityReferenceName','Tax Ref');
INSERT INTO config VALUES('CountryOfOperation','USD');
INSERT INTO config VALUES('NumberOfPeriodsOfStockUsage','12');
INSERT INTO config VALUES('Check_Qty_Charged_vs_Del_Qty','1');
INSERT INTO config VALUES('Check_Price_Charged_vs_Order_Price','1');
INSERT INTO config VALUES('OverChargeProportion','30');
INSERT INTO config VALUES('OverReceiveProportion','20');
INSERT INTO config VALUES('PO_AllowSameItemMultipleTimes','1');
INSERT INTO config VALUES('YearEnd','3');
INSERT INTO config VALUES('PageLength','48');
INSERT INTO config VALUES('part_pics_dir','part_pics');
INSERT INTO config VALUES('MaxImageSize','300');
INSERT INTO config VALUES('reports_dir','reports');
INSERT INTO config VALUES('EDIHeaderMsgId','D:01B:UN:EAN010');
INSERT INTO config VALUES('EDIReference','WEBERP');
INSERT INTO config VALUES('EDI_MsgPending','EDI_Pending');
INSERT INTO config VALUES('EDI_MsgSent','EDI_Sent');
INSERT INTO config VALUES('EDI_Incoming_Orders', 'EDI_Incoming_Orders');
INSERT INTO config VALUES('DefaultDisplayRecordsMax','50');
INSERT INTO config VALUES('RadioBeaconStockLocation','BL');
INSERT INTO config VALUES('RadioBeaconHomeDir', '/home/RadioBeacon');
INSERT INTO config VALUES('RadioBeaconFileCounter', '/home