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')