upgrade3.04-3.05.sql

java开源项目源代码

CREATE TABLE `reports` (
  `id` int(5) NOT NULL auto_increment,
  `reportname` varchar(30) NOT NULL default '',
  `reporttype` char(3) NOT NULL default 'rpt',
  `groupname` varchar(9) NOT NULL default 'misc',
  `defaultreport` enum('1','0') NOT NULL default '0',
  `papersize` varchar(15) NOT NULL default 'A4,210,297',
  `paperorientation` enum('P','L') NOT NULL default 'P',
  `margintop` int(3) NOT NULL default '10',
  `marginbottom` int(3) NOT NULL default '10',
  `marginleft` int(3) NOT NULL default '10',
  `marginright` int(3) NOT NULL default '10',
  `coynamefont` varchar(20) NOT NULL default 'Helvetica',
  `coynamefontsize` int(3) NOT NULL default '12',
  `coynamefontcolor` varchar(11) NOT NULL default '0,0,0',
  `coynamealign` enum('L','C','R') NOT NULL default 'C',
  `coynameshow` enum('1','0') NOT NULL default '1',
  `title1desc` varchar(50) NOT NULL default '%reportname%',
  `title1font` varchar(20) NOT NULL default 'Helvetica',
  `title1fontsize` int(3) NOT NULL default '10',
  `title1fontcolor` varchar(11) NOT NULL default '0,0,0',
  `title1fontalign` enum('L','C','R') NOT NULL default 'C',
  `title1show` enum('1','0') NOT NULL default '1',
  `title2desc` varchar(50) NOT NULL default 'Report Generated %date%',
  `title2font` varchar(20) NOT NULL default 'Helvetica',
  `title2fontsize` int(3) NOT NULL default '10',
  `title2fontcolor` varchar(11) NOT NULL default '0,0,0',
  `title2fontalign` enum('L','C','R') NOT NULL default 'C',
  `title2show` enum('1','0') NOT NULL default '1',
  `filterfont` varchar(10) NOT NULL default 'Helvetica',
  `filterfontsize` int(3) NOT NULL default '8',
  `filterfontcolor` varchar(11) NOT NULL default '0,0,0',
  `filterfontalign` enum('L','C','R') NOT NULL default 'L',
  `datafont` varchar(10) NOT NULL default 'Helvetica',
  `datafontsize` int(3) NOT NULL default '10',
  `datafontcolor` varchar(10) NOT NULL default 'black',
  `datafontalign` enum('L','C','R') NOT NULL default 'L',
  `totalsfont` varchar(10) NOT NULL default 'Helvetica',
  `totalsfontsize` int(3) NOT NULL default '10',
  `totalsfontcolor` varchar(11) NOT NULL default '0,0,0',
  `totalsfontalign` enum('L','C','R') NOT NULL default 'L',
  `col1width` int(3) NOT NULL default '25',
  `col2width` int(3) NOT NULL default '25',
  `col3width` int(3) NOT NULL default '25',
  `col4width` int(3) NOT NULL default '25',
  `col5width` int(3) NOT NULL default '25',
  `col6width` int(3) NOT NULL default '25',
  `col7width` int(3) NOT NULL default '25',
  `col8width` int(3) NOT NULL default '25',
  `table1` varchar(25) NOT NULL default '',
  `table2` varchar(25) default NULL,
  `table2criteria` varchar(75) default NULL,
  `table3` varchar(25) default NULL,
  `table3criteria` varchar(75) default NULL,
  `table4` varchar(25) default NULL,
  `table4criteria` varchar(75) default NULL,
  `table5` VARCHAR(25) ,
  `table5criteria` VARCHAR(75) ,
  `table6` VARCHAR(25),
  `table6criteria` VARCHAR(75), 
  PRIMARY KEY  (`id`),
  KEY `name` (`reportname`,`groupname`)
) ENGINE=InnoDB ;

CREATE TABLE `reportfields` (
  `id` int(8) NOT NULL auto_increment,
  `reportid` int(5) NOT NULL default '0',
  `entrytype` varchar(15) NOT NULL default '',
  `seqnum` int(3) NOT NULL default '0',
  `fieldname` varchar(35) NOT NULL default '',
  `displaydesc` varchar(25) NOT NULL default '',
  `visible` enum('1','0') NOT NULL default '1',
  `columnbreak` enum('1','0') NOT NULL default '1',
  `params` text,
  PRIMARY KEY  (`id`),
  KEY `reportid` (`reportid`)
) ENGINE=Innodb;


DROP TABLE IF EXISTS `reportlinks`;
CREATE TABLE IF NOT EXISTS `reportlinks` (
  `table1` varchar(25) NOT NULL default '',
  `table2` varchar(25) NOT NULL default '',
  `equation` varchar(75) NOT NULL default ''
) ENGINE=InnoDB;

-- 
-- Dumping data for table `reportlinks`
-- 

INSERT INTO `reportlinks` VALUES ('accountgroups', 'accountsection', 'accountgroups.sectioninaccounts=accountsection.sectionid');
INSERT INTO `reportlinks` VALUES ('accountsection', 'accountgroups', 'accountsection.sectionid=accountgroups.sectioninaccounts');
INSERT INTO `reportlinks` VALUES ('bankaccounts', 'chartmaster', 'bankaccounts.accountcode=chartmaster.accountcode');
INSERT INTO `reportlinks` VALUES ('chartmaster', 'bankaccounts', 'chartmaster.accountcode=bankaccounts.accountcode');
INSERT INTO `reportlinks` VALUES ('banktrans', 'systypes', 'banktrans.type=systypes.typeid');
INSERT INTO `reportlinks` VALUES ('systypes', 'banktrans', 'systypes.typeid=banktrans.type');
INSERT INTO `reportlinks` VALUES ('banktrans', 'bankaccounts', 'banktrans.bankact=bankaccounts.accountcode');
INSERT INTO `reportlinks` VALUES ('bankaccounts', 'banktrans', 'bankaccounts.accountcode=banktrans.bankact');
INSERT INTO `reportlinks` VALUES ('bom', 'stockmaster', 'bom.parent=stockmaster.stockid');
INSERT INTO `reportlinks` VALUES ('stockmaster', 'bom', 'stockmaster.stockid=bom.parent');
INSERT INTO `reportlinks` VALUES ('bom', 'stockmaster', 'bom.component=stockmaster.stockid');
INSERT INTO `reportlinks` VALUES ('stockmaster', 'bom', 'stockmaster.stockid=bom.component');
INSERT INTO `reportlinks` VALUES ('bom', 'workcentres', 'bom.workcentreadded=workcentres.code');
INSERT INTO `reportlinks` VALUES ('workcentres', 'bom', 'workcentres.code=bom.workcentreadded');
INSERT INTO `reportlinks` VALUES ('bom', 'locations', 'bom.loccode=locations.loccode');
INSERT INTO `reportlinks` VALUES ('locations', 'bom', 'locations.loccode=bom.loccode');
INSERT INTO `reportlinks` VALUES ('buckets', 'workcentres', 'buckets.workcentre=workcentres.code');
INSERT INTO `reportlinks` VALUES ('workcentres', 'buckets', 'workcentres.code=buckets.workcentre');
INSERT INTO `reportlinks` VALUES ('chartdetails', 'chartmaster', 'chartdetails.accountcode=chartmaster.accountcode');
INSERT INTO `reportlinks` VALUES ('chartmaster', 'chartdetails', 'chartmaster.accountcode=chartdetails.accountcode');
INSERT INTO `reportlinks` VALUES ('chartdetails', 'periods', 'chartdetails.period=periods.periodno');
INSERT INTO `reportlinks` VALUES ('periods', 'chartdetails', 'periods.periodno=chartdetails.period');
INSERT INTO `reportlinks` VALUES ('chartmaster', 'accountgroups', 'chartmaster.group_=accountgroups.groupname');
INSERT INTO `reportlinks` VALUES ('accountgroups', 'chartmaster', 'accountgroups.groupname=chartmaster.group_');
INSERT INTO `reportlinks` VALUES ('contractbom', 'workcentres', 'contractbom.workcentreadded=workcentres.code');
INSERT INTO `reportlinks` VALUES ('workcentres', 'contractbom', 'workcentres.code=contractbom.workcentreadded');
INSERT INTO `reportlinks` VALUES ('contractbom', 'locations', 'contractbom.loccode=locations.loccode');
INSERT INTO `reportlinks` VALUES ('locations', 'contractbom', 'locations.loccode=contractbom.loccode');
INSERT INTO `reportlinks` VALUES ('contractbom', 'stockmaster', 'contractbom.component=stockmaster.stockid');
INSERT INTO `reportlinks` VALUES ('stockmaster', 'contractbom', 'stockmaster.stockid=contractbom.component');
INSERT INTO `reportlinks` VALUES ('contractreqts', 'contracts', 'contractreqts.contract=contracts.contractref');
INSERT INTO `reportlinks` VALUES ('contracts', 'contractreqts', 'contracts.contractref=contractreqts.contract');
INSERT INTO `reportlinks` VALUES ('contracts', 'custbranch', 'contracts.debtorno=custbranch.debtorno');
INSERT INTO `reportlinks` VALUES ('custbranch', 'contracts', 'custbranch.debtorno=contracts.debtorno');
INSERT INTO `reportlinks` VALUES ('contracts', 'stockcategory', 'contracts.branchcode=stockcategory.categoryid');
INSERT INTO `reportlinks` VALUES ('stockcategory', 'contracts', 'stockcategory.categoryid=contracts.branchcode');
INSERT INTO `reportlinks` VALUES ('contracts', 'salestypes', 'contracts.typeabbrev=salestypes.typeabbrev');
INSERT INTO `reportlinks` VALUES ('salestypes', 'contracts', 'salestypes.typeabbrev=contracts.typeabbrev');
INSERT INTO `reportlinks` VALUES ('custallocns', 'debtortrans', 'custallocns.transid_allocfrom=debtortrans.id');
INSERT INTO `reportlinks` VALUES ('debtortrans', 'custallocns', 'debtortrans.id=custallocns.transid_allocfrom');
INSERT INTO `reportlinks` VALUES ('custallocns', 'debtortrans', 'custallocns.transid_allocto=debtortrans.id');
INSERT INTO `reportlinks` VALUES ('debtortrans', 'custallocns', 'debtortrans.id=custallocns.transid_allocto');
INSERT INTO `reportlinks` VALUES ('custbranch', 'debtorsmaster', 'custbranch.debtorno=debtorsmaster.debtorno');
INSERT INTO `reportlinks` VALUES ('debtorsmaster', 'custbranch', 'debtorsmaster.debtorno=custbranch.debtorno');
INSERT INTO `reportlinks` VALUES ('custbranch', 'areas', 'custbranch.area=areas.areacode');
INSERT INTO `reportlinks` VALUES ('areas', 'custbranch', 'areas.areacode=custbranch.area');
INSERT INTO `reportlinks` VALUES ('custbranch', 'salesman', 'custbranch.salesman=salesman.salesmancode');
INSERT INTO `reportlinks` VALUES ('salesman', 'custbranch', 'salesman.salesmancode=custbranch.salesman');
INSERT INTO `reportlinks` VALUES ('custbranch', 'locations', 'custbranch.defaultlocation=locations.loccode');
INSERT INTO `reportlinks` VALUES ('locations', 'custbranch', 'locations.loccode=custbranch.defaultlocation');
INSERT INTO `reportlinks` VALUES ('custbranch', 'shippers', 'custbranch.defaultshipvia=shippers.shipper_id');
INSERT INTO `reportlinks` VALUES ('shippers', 'custbranch', 'shippers.shipper_id=custbranch.defaultshipvia');
INSERT INTO `reportlinks` VALUES ('debtorsmaster', 'holdreasons', 'debtorsmaster.holdreason=holdreasons.reasoncode');
INSERT INTO `reportlinks` VALUES ('holdreasons', 'debtorsmaster', 'holdreasons.reasoncode=debtorsmaster.holdreason');
INSERT INTO `reportlinks` VALUES ('debtorsmaster', 'currencies', 'debtorsmaster.currcode=currencies.currabrev');
INSERT INTO `reportlinks` VALUES ('currencies', 'debtorsmaster', 'currencies.currabrev=debtorsmaster.currcode');
INSERT INTO `reportlinks` VALUES ('debtorsmaster', 'paymentterms', 'debtorsmaster.paymentterms=paymentterms.termsindicator');
INSERT INTO `reportlinks` VALUES ('paymentterms', 'debtorsmaster', 'paymentterms.termsindicator=debtorsmaster.paymentterms');
INSERT INTO `reportlinks` VALUES ('debtorsmaster', 'salestypes', 'debtorsmaster.salestype=salestypes.typeabbrev');
INSERT INTO `reportlinks` VALUES ('salestypes', 'debtorsmaster', 'salestypes.typeabbrev=debtorsmaster.salestype');
INSERT INTO `reportlinks` VALUES ('debtortrans', 'custbranch', 'debtortrans.debtorno=custbranch.debtorno');
INSERT INTO `reportlinks` VALUES ('custbranch', 'debtortrans', 'custbranch.debtorno=debtortrans.debtorno');
INSERT INTO `reportlinks` VALUES ('debtortrans', 'systypes', 'debtortrans.type=systypes.typeid');
INSERT INTO `reportlinks` VALUES ('systypes', 'debtortrans', 'systypes.typeid=debtortrans.type');
INSERT INTO `reportlinks` VALUES ('debtortrans', 'periods', 'debtortrans.prd=periods.periodno');
INSERT INTO `reportlinks` VALUES ('periods', 'debtortrans', 'periods.periodno=debtortrans.prd');
INSERT INTO `reportlinks` VALUES ('debtortranstaxes', 'taxauthorities', 'debtortranstaxes.taxauthid=taxauthorities.taxid');
INSERT INTO `reportlinks` VALUES ('taxauthorities', 'debtortranstaxes', 'taxauthorities.taxid=debtortranstaxes.taxauthid');
INSERT INTO `reportlinks` VALUES ('debtortranstaxes', 'debtortrans', 'debtortranstaxes.debtortransid=debtortrans.id');
INSERT INTO `reportlinks` VALUES ('debtortrans', 'debtortranstaxes', 'debtortrans.id=debtortranstaxes.debtortransid');
INSERT INTO `reportlinks` VALUES ('discountmatrix', 'salestypes', 'discountmatrix.salestype=salestypes.typeabbrev');
INSERT INTO `reportlinks` VALUES ('salestypes', 'discountmatrix', 'salestypes.typeabbrev=discountmatrix.salestype');
INSERT INTO `reportlinks` VALUES ('freightcosts', 'locations', 'freightcosts.locationfrom=locations.loccode');
INSERT INTO `reportlinks` VALUES ('locations', 'freightcosts', 'locations.loccode=freightcosts.locationfrom');
INSERT INTO `reportlinks` VALUES ('freightcosts', 'shippers', 'freightcosts.shipperid=shippers.shipper_id');