daniels/fuellogger

0.3.1 2022-05-02 09:03 UTC

This package is auto-updated.

Last update: 2024-08-30 01:06:18 UTC


README

根据 https://creativecommons.tankerkoenig.de/ 的汽油价格信息进行仪表板和价格提醒

安装

./vendor/bin/doctrine-migrations migrations:migrate --configuration ./src/Migrations/migrations.php --db-configuration ./src/Migrations/migrations_db.php

导入现有数据

Tankerkönig 在 https://creativecommons.tankerkoenig.de/ 的历史记录中提供现有数据,这些数据可以导入。为此,创建一个导入表格。

CREATE TABLE `import` (
  `datetime` datetime NOT NULL,
  `stationid` char(36) NOT NULL,
  `diesel` float(4,3) NOT NULL,
  `e5` float(4,3) NOT NULL,
  `e10` float(4,3) NOT NULL,
  `dieselchange` int(1) NOT NULL,
  `e5change` int(1) NOT NULL,
  `e10change` int(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `import`
  ADD KEY `stationid` (`stationid`);

将 CSV 数据导入此表格。

以下查询将价格数据传输到本包的结构

-- E5

INSERT INTO prices (
SELECT UUID(), st.ID, 'e5', im.e5, im.datetime, im.datetime FROM `import` im 
LEFT JOIN stations st ON im.stationid = st.TKID
WHERE e5change = 1 AND st.TKID IS NOT NULL
);

-- E10

INSERT INTO prices (
SELECT UUID(), st.ID, 'e10', im.e10, im.datetime, im.datetime FROM `import` im 
LEFT JOIN stations st ON im.stationid = st.TKID
WHERE e10change = 1 AND st.TKID IS NOT NULL
);

-- Diesel

INSERT INTO prices (
SELECT UUID(), st.ID, 'diesel', im.diesel, im.datetime, im.datetime FROM `import` im 
LEFT JOIN stations st ON im.stationid = st.TKID
WHERE dieselchange = 1 AND st.TKID IS NOT NULL
);

之后可以删除导入的表格。