kpt_xdmy/sap.sql

ALTER TABLE `pasture`
    ADD COLUMN `empId` int NULL COMMENT '供应负责人';


ALTER TABLE `diesel`
    ADD COLUMN `sort` int(11) DEFAULT NULL;

ALTER TABLE `diesel`
    ADD COLUMN `oilType` tinyint(1) unsigned NOT NULL DEFAULT 0 COMMENT '柴油类型 0: 空 1: 0# 2: -10# 3: -20# 4: -30# 5: -35#';
ALTER TABLE `diesel`
    ADD COLUMN `oilName` varchar(255) NOT NULL DEFAULT '' COMMENT '柴油类型名称';
ALTER TABLE `diesel`
    ADD COLUMN`dieselCode` varchar(50) DEFAULT NULL;


ALTER TABLE `diesel`
    ADD COLUMN
        `ProofCode` varchar(50) DEFAULT NULL;


CREATE TABLE `diesel_off` (
                              `id` int(11) NOT NULL,
                              `pastureId` int(11) NOT NULL COMMENT '牧场ID',
                              `deptId` int(11) DEFAULT NULL,
                              `eqId` int(11) DEFAULT NULL COMMENT '备件编号',
                              `eqCode` varchar(100) DEFAULT NULL,
                              `oilcardId` int(11) DEFAULT NULL,
                              `cardNumber` varchar(200) NOT NULL COMMENT '卡号',
                              `oilClass` varchar(100) DEFAULT NULL COMMENT '加油工班',
                              `oilAmount` decimal(20,2) DEFAULT NULL COMMENT '油量',
                              `nowPrice` decimal(10,2) DEFAULT NULL COMMENT '本次金额',
                              `price` decimal(10,2) DEFAULT NULL COMMENT '单价',
                              `empId` varchar(100) DEFAULT NULL COMMENT '上班加油工',
                              `inputId` int(11) DEFAULT NULL COMMENT '录入人',
                              `selTime` varchar(100) DEFAULT NULL COMMENT '交易时间',
                              `note` varchar(255) DEFAULT NULL COMMENT '备注',
                              `ProofCode` varchar(50) DEFAULT NULL,
                              `oilType` tinyint(1) unsigned NOT NULL DEFAULT 0 COMMENT '柴油类型 0: 空 1: 0# 2: -10# 3: -20# 4: -30# 5: -35#',
                              `oilName` varchar(255) NOT NULL DEFAULT '' COMMENT '柴油类型名称',
                              `dieselCode` varchar(50) DEFAULT NULL,
                              `sort` int(11) DEFAULT NULL,
                              `createTime` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
                              PRIMARY KEY (`id`) USING BTREE,
                              KEY `selTime` (`selTime`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;







-- 水费
insert into feequery(pastureId,RBUKRS,WERKS,FYLX,GJAHR,POPER,HSL,Date)
select pastureId,p.company_code,p.factory_code,"水费", date_format(date ,'%Y'),date_format(date ,'%m'),    sum(sumPrice) sumPrice, date_format(date ,'%Y-%m') from water  join pasture p on p.id = pastureId  where date_format(date ,'%Y') = '2022'
group by pastureId,date_format(date ,'%Y-%m')




-- 电费
    insert into feequery(pastureId,RBUKRS,WERKS,FYLX,GJAHR,POPER,HSL,Date)
select pastureId,p.company_code,p.factory_code,"电费", date_format(date ,'%Y'),date_format(date ,'%m'),    sum(sumPrice) sumPrice, date_format(date ,'%Y-%m') from electricity  join pasture p on p.id = pastureId  where date_format(date ,'%Y') = '2022'
group by pastureId,date_format(date ,'%Y-%m')



-- 燃动费
    insert into feequery(pastureId,RBUKRS,WERKS,FYLX,GJAHR,POPER,HSL,Date)

select pastureId,p.company_code,p.factory_code,"燃动费", date_format(selTime ,'%Y'),date_format(selTime ,'%m'),    sum(nowPrice) sumPrice, date_format(selTime ,'%Y-%m') from diesel  join pasture p on p.id = pastureId  where date_format(selTime ,'%Y') = '2022'
group by pastureId,date_format(selTime ,'%Y-%m')


ALTER TABLE `eq`.`equipment`
    ADD COLUMN `deptId` varchar(30) NULL COMMENT '成本部门' AFTER `levelthreeTime`;


ALTER TABLE `eq`.`maintain`
    ADD COLUMN `remark` varchar(255) NULL COMMENT '备注' AFTER `outsourcing`;



ALTER TABLE `equipment`
    ADD COLUMN `duration` decimal(15, 2) NULL COMMENT '标准时长' AFTER `license`,
ADD COLUMN `pushStatus` tinyint(1) NOT NULL DEFAULT 0 COMMENT ' 0 报废  1 新增 2 更新' AFTER `duration`

-----------------------------------

ALTER TABLE `partpurchase`
    ADD COLUMN `reject` tinyint(1) NOT NULL DEFAULT 0 COMMENT ' 1 驳回' AFTER `ContractVarianceItem`,
ADD COLUMN `departmentId` int NULL COMMENT '部门' AFTER `reject`



ALTER TABLE `partpurchase`
    ADD COLUMN `date` datetime NULL COMMENT '申购时间' AFTER `explain`

ALTER TABLE `partpurchase`
    ADD COLUMN `employeId` int NULL COMMENT '申购人' AFTER `date`





ALTER TABLE `bigpartpurchase`
    ADD COLUMN `positionId` int NULL COMMENT '职能中心' AFTER `financedate`,
ADD COLUMN `positiondate` datetime NULL AFTER `positionId`


ALTER TABLE `bigacceptance`
    ADD COLUMN `remark` varchar(100) NULL COMMENT '备注' AFTER `isAcceptance`


ALTER TABLE `bigpartpurchase`
    ADD COLUMN `isMilk` tinyint(1) NOT NULL DEFAULT 0 COMMENT '1 奶厅' AFTER `positiondate`;

---------------------------
ALTER TABLE `eq`.`equipment`
    ADD COLUMN `work` varchar(30) NULL COMMENT '工作类别' AFTER `pushStatus`;


ALTER TABLE buydetail
    ADD COLUMN `reportery` decimal(12, 2) NOT NULL DEFAULT 0 COMMENT '库存' AFTER `rowNumber`;