gas.sql

数据库源代码,用来创建一个数据库,是做一个燃气系统的.

-- ----------------------------
-- Table structure for f_repaircompany
-- ----------------------------
CREATE TABLE `f_repaircompany` (
  `companyRepairId` int(11) NOT NULL auto_increment,
  `fk_f_callcenter` int(11) default NULL,
  `repairNo` varchar(255) default NULL COMMENT '报修单编号',
  `clientName` varchar(255) default NULL COMMENT '客户姓名',
  `clientAddress` varchar(255) default NULL COMMENT '客户地址',
  `telephone` varchar(255) default NULL COMMENT '联系电话',
  `repairDate` varchar(255) default NULL COMMENT '维修日期',
  `state` varchar(255) default NULL COMMENT '状态',
  `repairContent` varchar(255) default NULL COMMENT '报修内容',
  `fk_y_disposalstate` int(11) default NULL COMMENT '处理情况',
  `clientSignature` varchar(255) default NULL COMMENT '客户签名',
  `signDate` date default NULL COMMENT '日期',
  `judge` varchar(255) default NULL COMMENT '对维修人员的服务',
  `advice` int(11) default NULL COMMENT '意见或建议',
  `ifBlankOut` int(11) default NULL COMMENT '是否作废',
  `blankOutReason` text COMMENT '作废理由',
  `clientAdvice` text COMMENT '客户意见',
  `operateMan` int(11) default NULL,
  PRIMARY KEY  (`companyRepairId`),
  KEY `fk_f_callcenter` (`fk_f_callcenter`),
  KEY `fk_y_disposalstate` (`fk_y_disposalstate`),
  KEY `operateMan` (`operateMan`),
  CONSTRAINT `f_repaircompany_ibfk_5` FOREIGN KEY (`operateMan`) REFERENCES `tbl_emplogin` (`loginId`),
  CONSTRAINT `f_repaircompany_ibfk_1` FOREIGN KEY (`fk_f_callcenter`) REFERENCES `f_callcenter` (`centerId`),
  CONSTRAINT `f_repaircompany_ibfk_2` FOREIGN KEY (`fk_y_disposalstate`) REFERENCES `y_disposalstate` (`disposal_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for f_repairresident
-- ----------------------------
CREATE TABLE `f_repairresident` (
  `residentRepairId` int(11) NOT NULL auto_increment,
  `fk_f_callcenter` int(11) default NULL COMMENT '报修单编号',
  `repairNo` varchar(255) default NULL COMMENT '报修单编号',
  `clientNameFromCenter` varchar(255) default NULL COMMENT '客户姓名',
  `clientAddressFromCenter` varchar(255) default NULL COMMENT '客户地址',
  `telephoneFromCenter` varchar(255) default NULL COMMENT '联系电话',
  `bookingDateFromCenter` date default NULL COMMENT '预约日期',
  `fk_y_bookplan` int(11) default NULL,
  `gasTypeFromCenter` varchar(255) default NULL COMMENT '表类型',
  `gasClassFromCenter` varchar(255) default NULL COMMENT '表种类',
  `gasNoFromCenter` varchar(255) default NULL COMMENT '进气方向',
  `insertCardFromCenter` varchar(255) default NULL COMMENT '插卡方式',
  `gasType` int(11) default NULL COMMENT '表类型',
  `gasClass` int(11) default NULL COMMENT '表种类',
  `gasInDerection` int(11) default NULL COMMENT '进气方向',
  `insertCard` varchar(255) default NULL COMMENT '插卡方式',
  `storeAdd` varchar(255) default NULL COMMENT '库累计量',
  `engineReading` int(8) default NULL COMMENT '机械读数',
  `repairContent` varchar(255) default NULL COMMENT '报修内容',
  `fk_y_disposalstate` int(11) default NULL COMMENT '处理情况',
  `fk_emp` int(11) default NULL COMMENT '维修人员',
  `serviceFee` int(8) default NULL COMMENT '上门服务费',
  `manualFee` int(8) default NULL COMMENT '人工费',
  `datumFee` int(11) default NULL COMMENT '材料费',
  `addUp` float default NULL COMMENT '合计',
  `maintainDate` date default NULL COMMENT '维修日期',
  `state` varchar(255) default NULL COMMENT '状态',
  `maintainerSignature` varchar(255) default NULL COMMENT '维修人员签名',
  `clientSignature` varchar(255) default NULL COMMENT '客户签名',
  `signDate` date default NULL COMMENT '日期',
  `judge` varchar(255) default NULL COMMENT '对维修人员的服务',
  `advice` int(11) default NULL COMMENT '意见或建议',
  `ifBlankOut` varchar(255) default NULL COMMENT '是否作废',
  `blankOutReason` text COMMENT '作废理由',
  `clientAdvice` text COMMENT '客户意见',
  `operateMan` int(11) default NULL,
  PRIMARY KEY  (`residentRepairId`),
  KEY `fk_y_disposalstate` (`fk_y_disposalstate`),
  KEY `fk_emp` (`fk_emp`),
  KEY `fk_y_bookplan` (`fk_y_bookplan`),
  KEY `fk_f_callcenter` (`fk_f_callcenter`),
  KEY `operateMan` (`operateMan`),
  CONSTRAINT `f_repairresident_ibfk_8` FOREIGN KEY (`operateMan`) REFERENCES `tbl_emplogin` (`loginId`),
  CONSTRAINT `f_repairresident_ibfk_2` FOREIGN KEY (`fk_y_disposalstate`) REFERENCES `y_disposalstate` (`disposal_id`),
  CONSTRAINT `f_repairresident_ibfk_3` FOREIGN KEY (`fk_emp`) REFERENCES `tbl_emp` (`emp_no`),
  CONSTRAINT `f_repairresident_ibfk_5` FOREIGN KEY (`fk_y_bookplan`) REFERENCES `y_bookplan` (`bookplan_id`),
  CONSTRAINT `f_repairresident_ibfk_7` FOREIGN KEY (`fk_f_callcenter`) REFERENCES `f_callcenter` (`centerId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for f_residentchange
-- ----------------------------
CREATE TABLE `f_residentchange` (
  `changeId` int(11) default NULL,
  `changeNo` varchar(255) default NULL COMMENT '换表单编号(HB＋【报修单编号】的4位年＋2位月＋2位日＋3位流水号)',
  `fk_repairresident` int(11) default NULL COMMENT '居民客户报修单',
  `gasType` varchar(255) default NULL COMMENT '表类型(居民客户报修单带出)',
  `changeAddress` varchar(255) default NULL COMMENT '换表地址(居民客户报修单带出)',
  `changeDate` date default NULL COMMENT '换表日期',
  `oldTrouble` varchar(200) default NULL COMMENT '原表故障',
  `oldGasNo` varchar(255) default NULL COMMENT '原表编号(居民客户报修单带出)',
  `oldEngineReading` varchar(255) default NULL COMMENT '原表机械读数(居民客户报修单带出)',
  `newGasNo` varchar(50) default NULL COMMENT '新表编号',
  `oldLastReading` int(8) default NULL COMMENT '旧表上期读数',
  `newEngineReading` int(8) default NULL COMMENT '新表机械读数',
  `obligate` int(8) default NULL COMMENT '预留量',
  `clientSignature` varchar(255) default NULL,
  `operateMan` varchar(11) default NULL COMMENT '操作人员(居民客户报修单带出)',
  KEY `fk_repairresident` (`fk_repairresident`),
  KEY `operator` (`operateMan`),
  CONSTRAINT `f_residentchange_ibfk_1` FOREIGN KEY (`fk_repairresident`) REFERENCES `f_repairresident` (`residentRepairId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for f_residentcost
-- ----------------------------
CREATE TABLE `f_residentcost` (
  `datumId` int(11) NOT NULL auto_increment,
  `datumNo` varchar(255) default NULL COMMENT '材料编号',
  `fk_y_residentopendatum` int(11) default NULL COMMENT '居民开户材料维护',
  `datumName` varchar(255) default NULL COMMENT '材料名称',
  `datumSpec` varchar(255) default NULL COMMENT '材料规格',
  `datumUnit` varchar(255) default NULL COMMENT '材料单位',
  `datumPrice` float default NULL COMMENT '材料单价',
  `quanlity` int(8) default NULL COMMENT '数量',
  `addUp` float default NULL COMMENT '合计',
  PRIMARY KEY  (`datumId`),
  KEY `fk_y_residentopendatum` (`fk_y_residentopendatum`),
  CONSTRAINT `f_residentcost_ibfk_1` FOREIGN KEY (`fk_y_residentopendatum`) REFERENCES `y_residentopendatum` (`datum_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for f_spdossier
-- ----------------------------
CREATE TABLE `f_spdossier` (
  `dossierId` int(11) NOT NULL auto_increment,
  `dossierNo` varchar(11) default NULL COMMENT '编号(SP＋9位流水号)',
  `fk_y_residenttype` int(11) default NULL COMMENT '客户类型',
  `clientName` varchar(255) default NULL COMMENT '客户名称',
  `fk_useGasApply` int(11) default NULL COMMENT '有相关 “供气通知书”但未形成“输配档案”的用气申请单',
  `linkman` varchar(200) default NULL COMMENT '联系人',
  `telephone` varchar(200) default NULL COMMENT '联系电话',
  `address` varchar(200) default NULL COMMENT '地址',
  `ifTrusteeship` int(11) default NULL COMMENT '是否托管',
  `selTrusteeship` varchar(255) default NULL COMMENT '托管选项',
  `operateDate` datetime default NULL COMMENT '操作日期',
  `operateMan` int(11) default NULL COMMENT '操作人',
  PRIMARY KEY  (`dossierId`),
  KEY `fk_y_residenttype` (`fk_y_residenttype`),
  KEY `fk_useGasApply` (`fk_useGasApply`),
  KEY `operateMan` (`operateMan`),
  CONSTRAINT `f_spdossier_ibfk_3` FOREIGN KEY (`operateMan`) REFERENCES `tbl_emplogin` (`loginId`),
  CONSTRAINT `f_spdossier_ibfk_1` FOREIGN KEY (`fk_y_residenttype`) REFERENCES `y_residenttype` (`resident_id`),
  CONSTRAINT `f_spdossier_ibfk_2` FOREIGN KEY (`fk_useGasApply`) REFERENCES `project01_usegasapply` (`useGasId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for f_spequipment
-- ----------------------------
CREATE TABLE `f_spequipment` (
  `equipId` int(11) NOT NULL auto_increment,
  `equipNo` varchar(255) default NULL COMMENT '编号(5位流水号)',
  `fk_y_establishment` int(11) default NULL COMMENT '设施名称',
  `manufacturer` varchar(200) default NULL COMMENT '生产厂家',
  `caliber` int(8) default NULL COMMENT '口径',
  `exportPress` int(8) default NULL COMMENT '出口压力',
  `maintainKind` varchar(200) default NULL COMMENT '维修性质',
  `interfaceType` varchar(200) default NULL COMMENT '接口形式',
  `quantity` int(8) default NULL COMMENT '数量',
  `datum` varchar(200) default NULL COMMENT '材料',
  `pressGrade` varchar(200) default NULL COMMENT '压力等级',
  `incept` text COMMENT '起始点',
  `build` date default NULL COMMENT '建成日期(从“工程质量验收单”的【验收时间】系统自动带出)',
  `fk_projectMassCheck` int(11) default NULL,
  `maturity` date default NULL COMMENT '到期日期',
  `remark` text COMMENT '备注',
  `operateDate` datetime default NULL COMMENT '操作日期',
  `operateMan` int(11) default NULL COMMENT '操作人',
  PRIMARY KEY  (`equipId`),
  KEY `fk_y_establishment` (`fk_y_establishment`),
  KEY `fk_projectMassCheck` (`fk_projectMassCheck`),
  KEY `operateMan` (`operateMan`),
  CONSTRAINT `f_spequipment_ibfk_5` FOREIGN KEY (`operateMan`) REFERENCES `tbl_emplogin` (`loginId`),
  CONSTRAINT `f_spequipment_ibfk_1` FOREIGN KEY (`fk_y_establishment`) REFERENCES `y_establishment` (`establishment_id`),
  CONSTRAINT `f_spequipment_ibfk_4` FOREIGN KEY (`fk_projectMassCheck`) REFERENCES `project02_qualitycheck_list` (`Pro2Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for p_input
-- ----------------------------
CREATE TABLE `p_input` (
  `inputId` int(10) NOT NULL auto_increment,
  `inputNo` varchar(255) default NULL,
  `fk_ys` int(10) default NULL,
  `input_date` date default NULL,
  `fk_area` int(255) default NULL,
  `fk_transport` int(255) default NULL,
  `car_number` varchar(255) default NULL,
  `dock_zl` float default NULL,
  `input_zl` float default NULL,
  `chae` float default NULL,
  `pot` varchar(255) default NULL,
  `remark` varchar(255) default NULL,
  `operator_man` int(11) default NULL,
  `operation_date` datetime default NULL,
  PRIMARY KEY  (`inputId`),
  KEY `fk_ys` (`fk_ys`),
  KEY `fk_area` (`fk_area`),
  KEY `fk_transport` (`fk_transport`),
  KEY `operator_man` (`operator_man`),
  CONSTRAINT `p_input_ibfk_6` FOREIGN KEY (`operator_man`) REFERENCES `tbl_emplogin` (`loginId`),
  CONSTRAINT `p_input_ibfk_1` FOREIGN KEY (`fk_ys`) REFERENCES `p_produce` (`produceId`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `p_input_ibfk_2` FOREIGN KEY (`fk_area`) REFERENCES `y_gasproducearea` (`gasproducearea_id`),
  CONSTRAINT `p_input_ibfk_3` FOREIGN KEY (`fk_transport`) REFERENCES `y_transportunit` (`transport_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for p_output
-- ----------------------------
CREATE TABLE `p_output` (
  `outputId` int(11) NOT NULL auto_increment,
  `outputNo` varchar(255) default NULL,
  `date` date default NULL,
  `type` varchar(255) default NULL,
  `client` varchar(255) default NULL,
  `cqd_num` varchar(255) default NULL,
  `car_num` varchar(255) default NULL,
  `jzfs` varchar(255) default NULL,
  `gplx` varchar(255) default NULL,
  `number_2` int(11) default NULL,
  `number_5` int(11) default NULL,
  `number_10` int(11) default NULL,
  `nubmer_15` int(11) default NULL,
  `number_50` int(11) default NULL,
  `heft` int(11) default NULL,
  `total_heft` int(11) default NULL,
  `remark` varchar(255) default NULL,
  `operator_man` int(255) default NULL,
  `operateion_date` date default NULL,
  PRIMARY KEY  (`outputId`),
  KEY `operator_man` (`operator_man`),
  CONSTRAINT `p_output_ibfk_1` FOREIGN KEY (`operator_man`) REFERENCES `tbl_emplogin` (`loginId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for p_produce
-- ----------------------------
CREATE TABLE `p_produce` (
  `produceId` int(11) NOT NULL auto_increment,
  `produceNo` varchar(255) default NULL,
  `contract_id` varchar(50) default NULL,
  `type` varchar(50) default NULL,
  `fk_transport` int(50) default NULL,
  `number` float default NULL,
  `fk_area` int(11) default NULL,
  `specs` varchar(50) default NULL,
  `date_start` date default NULL,
  `date_end` date default NULL,
  `pot` varchar(50) default NULL,
  `remark` text,
  `operator_man` int(255) default NULL,
  `operation_date` date default NULL,
  PRIMARY KEY  (`produceId`),
  KEY `fk_transport` (`fk_transport`),
  KEY `fk_area` (`fk_area`),
  KEY `operator_man` (`operator_man`),
  CONSTRAINT `p_produce_ibfk_4` FOREIGN KEY (`operator_man`) REFERENCES `tbl_emplogin` (`loginId`),
  CONSTRAINT `p_produce_ibfk_1` FOREIGN KEY (`fk_transport`) REFERENCES `y_transportunit` (`transport_id`),
  CONSTRAINT `p_produce_ibfk_2` FOREIGN KEY (`fk_area`) REFERENCES `y_gasproducearea` (`gasproducearea_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for p_scrbb
-- ----------------------------
CREATE TABLE `p_scrbb` (