php - 从表格数量输出发票

Question

我有以下表格

customers

cust_id     cust_name
1           a company
2           a company 2
3           a company 3

tariffs

tariff_id   cost_1     cost_2     cost_3
1           2          0          3
2           1          1          1
3           4          0          0


terminals

term_id     cust_id      term_number     tariff_id
1           1            12345           1
2           1            67890           2
3           2            14324           1
4           3            78788           3

usage

term_ident  usage_type   usage_amount    date
12345       1            20              11/12/2010
67890       2            10              31/12/2010
14324       1            1               01/01/2011
14324       2            5               01/01/2011
78788       1            0               14/01/2011

在现实生活中，这些表非常大 - 有 5000 个客户、250 个关税、500000 个终端和 500 万条使用记录。

在终端表中 - term_id、cust_id 和 tariff_id 都是外键。使用表中没有外键 - 这只是从 csv 文件导入的原始数据。

使用表中可能存在终端表中不存在的终端 - 这些可以忽略。

我需要做的是为每位客户开具使用发票。我只想包括 15/12/2010 和 15/01/2011 之间的使用情况 - 这是计费期。我需要根据其关税计算使用发票的行项目......例如:获取使用表中的第一条记录 - usage_1(对于 term_id 1)的成本将为 90x2 = 180，这是因为term_ident 使用 tariff_id 编号 1。

输出应该如下所示

Customer 2

date         terminal  usage_cost_1  usage_cost_2  usage_cost_3  total cost
01/01/2011   14324     18            0             6             24

我是一名称职的 PHP 开发人员 - 但只是 SQL 的初学者。我需要一些关于生成发票的最有效过程的建议——也许在 PHP 中的处理开始计算成本之前有一个 SQL 查询可以帮助我——或者 SQL 语句也可能产生成本？欢迎任何建议....

编辑:

1) 当前有一些东西正在运行这个进程 - 它是用 C++ 编写的，需要大约 24 小时来处理这个......我无法访问源代码。

2) 我在 Symfony 中使用 Doctrine - 我不确定 Doctrine 对检索数据有多大帮助，因为 Objects 只会减慢过程 - 我不确定在这里使用 Objects 是否会有太大帮助？

编辑@13:54 ->

如果使用表指定不正确......抱歉!

我必须将 usage_type 映射到每个终端的特定关税成本，即 usage_type 1 = cost_1 在适当的关税中......我想这会使它稍微复杂一些？

Answer 1

好了，应该不到 24 小时 ;)

SELECT u.date, u.term_ident terminal,
    (ta.cost_1 * u.usage_1) usage_cost_1,
    (ta.cost_2 * u.usage_2) usage_cost_2,
    (ta.cost_3 * u.usage_3) usage_cost_3,
    (usage_cost_1 + usage_cost_2 + usage_cost_3) total_cost
FROM usage u
INNER JOIN terminals te ON te.term_number = u.term_ident
INNER JOIN tariffs ta ON ta.tariff_id = te.tariff_id
INNER JOIN customers c ON c.cust_id = te.cust_id
WHERE u.date BETWEEN '2010-12-15' AND '2011-01-15'
    AND c.cust_id = 2

此查询仅适用于 cust_id = 2 的客户。如果您想要整个数据集的结果，只需删除条件即可。

更新

这对您的新要求来说并不是那么微不足道。您可以将使用表转换为您之前发布的新表。

要在 SELECT 查询中做出决定，您可以这样做。但这不是您期望的结果。它可用于创建转换后的新使用表。

SELECT u.date, u.term_ident terminal,
    CASE u.usage_type
        WHEN 1 then ta.cost_1 * u.usage_1
        WHEN 2 then ta.cost_2 * u.usage_2
        WHEN 3 THEN ta.cost_3 * u.usage_3
    AS usage_cost
FROM usage u
INNER JOIN terminals te ON te.term_number = u.term_ident
INNER JOIN tariffs ta ON ta.tariff_id = te.tariff_id
INNER JOIN customers c ON c.cust_id = te.cust_id
WHERE u.date BETWEEN '2010-12-15' AND '2011-01-15'
    AND c.cust_id = 2