forblind 內部管理系統協作計劃

@copyright 2020 台北市視障者家長協會

@ver 0.0.1 @date 2020-01-27 11:25:34 (星期1)

系統協作計劃開始

資料庫初始設定 (5.7 d1_forblind)

Laravel 安裝

Laravel 初始設定

省略...

捐贈收據清單

DDD 資料集導向開發 (Dataset Driven Development)

捐贈收據清單

需求 : 含捐款項目, 捐物項目, 依收據編號排序, 共有 收據編號, 收據日期, 項目, 收據抬頭, 備註 五個欄位

可能會有不同的條件來過濾, 再逐一討論

需求分析

檔案 : forblind/fb.sys/200121.捐贈表單/捐贈收據清單.xlsx

收據編號	收據日期	項目	收據抬頭	備註
10800001	2019-01-02	1000	善___
10800002	2019-01-02	150000	財___
10800003	2019-01-03	靠___	舒___
10800004	2019-01-03	星___	心___
10800005	2019-01-03	馬___	研___
10800006	2019-01-03	3600	卡___
...
...

這五個欄位, 分別位於 4 個不同的表, 全部用 donation_ID 串起來

  • donation : 捐贈主檔
  • donation_receipt : 捐贈收據
  • donation_amount : 捐款項目記錄
  • donation_goods : 捐物項目記錄

我們用 LEFT JOIN 一次把它全抓出來, 做一些小小的判斷, 寫到 xlsx 檔案.

where 條件之一為 donation.status 狀態 (有三種 0 : 刪除, 1 : 正常, 2 : 作廢 ), 清單只要 1, 2 這兩種.

where 條件之二暫時先用 donation_receipt.receiptDate 限制收據日期. 實際上線時, 可再調整使用其他不同欄位之條件來過濾.

sql statement 實測

請直接在 mysql 命令列執行下列 sql statement

SELECT
d.donation_ID,
d.status '狀態',
d.donationSN 'DSN捐贈單號',
r.receiptSN '收據編號',
r.receiptDate '收據日期',
a.amount '捐款項目',
g.goodsName '捐物項目',
r.receiptTitle '收據抬頭',
d.discardInfo '作廢原因',
d.discardDateTime '作廢時間',
d.remarks '備註'
FROM donation d
LEFT JOIN donation_receipt r ON d.donation_ID = r.donation_ID
LEFT JOIN donation_amount a ON d.donation_ID = a.donation_ID
LEFT JOIN donation_goods g ON d.donation_ID = g.donation_ID
WHERE d.status in (1, 2)
AND r.receiptDate >= '2019-01-01'
AND r.receiptDate <= '2019-01-07'
ORDER BY r.receiptSN ASC
LIMIT 99;

sql statement 結果

donation_ID DSN捐贈單號 收據編號 收據日期 捐款項目 捐物項目 收據抬頭 作廢原因 作廢時間 備註
7042 A201900001 10800001 2019-01-02 1000 NULL 林___ NULL NULL
7043 A201900002 10800002 2019-01-02 150000 NULL 財___ NULL NULL 台___
7044 C201900001 10800003 2019-01-03 NULL 靠___ 舒___ NULL NULL
7045 C201900002 10800004 2019-01-03 NULL 星___ 心___ NULL NULL
TL;DR 結果太長了, 不列出來
17 rows in set (0.04 sec)

已很接近我們想要的答案, 現在使用 Eloquent 方式做出第一種版本, 這個版本先不做關聯, 只用 join(...).

建立 Model

懶人法則 : 使用 bash 的 alias , 可以少打一些字

$ vi ~/.bashrc

加入 alias art='php artisan'

$ source ~/.bashrc

使用 Eloquent 必須要有 Model. 只要有 Donation 主檔的 Model 類別及正確對應的資料庫表格名稱即可.

注意 : 所有 Eloquent 取得的是一個 Collection (Illuminate\Database\Eloquent\Collection) 物件

它有很多方法可以變化, 經常用到的是 $result->toArray(); 轉成陣列給程式操作

Collection (Illuminate\Database\Eloquent\Collection) 物件 參考

執行

art make:model Donation

# 查看加入了那些檔案
git clean -n

# 檔案在這裡
ll app/Donation.php

回應

[ak4567@forblind]$ art make:model Donation
Model created successfully.
[ak4567@forblind]$ git clean -n
Would remove app/Console/Commands/ReportDonation1.php
Would remove app/Donation.php
[ak4567@forblind]$ ll app/Donation.php
-rw-r--r--  1 alexkuo  staff  104  1 28 16:46 app/Donation.php
[ak4567@forblind]$

vi app/Donation.php

實際資料表名稱, 一定要改, 以免 Laravel 自動化而誤判

修改程式

{
    /**
     * 實際資料表名稱
     */
    protected $table = 'donation';
}

完成以上工作後, 用 tinker 工具來測試 Donation Model. 如果資訊量很大, 還是老老實實的寫到程式裡, 用 Log::debug() 記錄, 會比較好除錯 ^_^

tinker 實測

[ak4567@forblind]$ art tinker
Psy Shell v0.9.12 (PHP 7.4.1 — cli) by Justin Hileman
>>> Donation::take(1)->get();
>>>
>>> Donation::get('donation_ID')->take(1);
PHP Fatal error:  Class 'Donation' not found in Psy Shell code on line 1
>>>
>>>
>>> \App\Donation::get()->take(1);
=> Illuminate\Database\Eloquent\Collection {#3055
     all: [
       App\Donation {#15558
         donation_ID: 1,
         donors_ID: 2,
         donationSN: "A201200643",
         donationDate: "2012-07-20",
         updateDateTime: "2018-08-01 15:28:08",
         remarks: "",
         status: 1,
         imputCode: null,
         originalSN: null,
         discardDateTime: null,
         discardInfo: null,
         discardUser: null,
       },
     ],
   }
>>>
>>>
>>> \App\Donation::get('donation_ID')->take(1);
=> Illuminate\Database\Eloquent\Collection {#3073
     all: [
       App\Donation {#15567
         donation_ID: 700,
       },
     ],
   }
>>>
>>>
>>> \App\Donation::take(1);
=> Illuminate\Database\Eloquent\Builder {#3081}
>>>
>>>
>>> \App\Donation::take(1)->get();
=> Illuminate\Database\Eloquent\Collection {#28075
     all: [
       App\Donation {#28073
         donation_ID: 1,
         donors_ID: 2,
         donationSN: "A201200643",
         donationDate: "2012-07-20",
         updateDateTime: "2018-08-01 15:28:08",
         remarks: "",
         status: 1,
         imputCode: null,
         originalSN: null,
         discardDateTime: null,
         discardInfo: null,
         discardUser: null,
       },
     ],
   }
>>>
>>>
>>> \App\Donation::get(['donation_ID', 'donationDate', 'status'])->take(3);
=> Illuminate\Database\Eloquent\Collection {#40534
     all: [
       App\Donation {#15555
         donation_ID: 1,
         donationDate: "2012-07-20",
         status: 1,
       },
       App\Donation {#15554
         donation_ID: 2,
         donationDate: "2012-07-20",
         status: 1,
       },
       App\Donation {#15553
         donation_ID: 3,
         donationDate: "2012-07-20",
         status: 1,
       },
     ],
   }
>>>
>>>
>>> use App\Donation
>>> Donation::get(['donation_ID', 'donationDate', 'status'])->take(3)
=> Illuminate\Database\Eloquent\Collection {#3066
     all: [
       App\Donation {#28047
         donation_ID: 1,
         donationDate: "2012-07-20",
         status: 1,
       },
       App\Donation {#40541
         donation_ID: 2,
         donationDate: "2012-07-20",
         status: 1,
       },
       App\Donation {#28058
         donation_ID: 3,
         donationDate: "2012-07-20",
         status: 1,
       },
     ],
   }
>>>
>>>
>>> Donation::get(['donation_ID', 'donationDate', 'status'])->take(3)->orderBy('donation_ID', 'DESC');
BadMethodCallException with message 'Method Illuminate/Database/Eloquent/Collection::orderBy does not exist.'
>>>
>>>
>>> Donation::take(3)->orderBy('donation_ID', 'DESC')->get(['donation_ID', 'donationDate', 'status']);
=> Illuminate\Database\Eloquent\Collection {#40538
     all: [
       App\Donation {#3060
         donation_ID: 18931,
         donationDate: "2020-01-20",
         status: 1,
       },
       App\Donation {#28071
         donation_ID: 18930,
         donationDate: "2020-01-20",
         status: 1,
       },
       App\Donation {#3071
         donation_ID: 18929,
         donationDate: "2019-12-31",
         status: 1,
       },
     ],
   }
>>>
>>>
>>> use App\Donation as d
>>>
>>> d::take(1)->get();
>>>
>>> d::take(3)->orderBy('donation_ID', 'DESC')->get(['donation_ID', 'donationDate', 'status']);
>>>
>>> d::take(3)->where('status', 2)->orderBy('donation_ID', 'DESC')->get(['donation_ID', 'donationDate', 'status']);
>>>
>>> d::take(3)->whereIn('status', [0,2])->orderBy('donation_ID', 'DESC')->get(['donation_ID', 'donationDate', 'status']);
>>>
>>> CTRL+C

建立命令列程式

後續的工作, 先用命令列手動方式來執行, 未來再搭配自定義的程式庫, 方便靈活的在 Console 及 Web, API 之間無遠弗屆的使用.

簡單的作業前說明 :

  • ReportDonation1 是完整類別名稱, 儘量完整表達此類別功能, 大寫開頭
  • rpt 是命令集合的大分類, 先把跟報表有關的都歸類到 rpt. 就像預設的 art make:????, 或者也可訂一個 test 專門用來測試一些想法或概念, 全部小寫
  • d1 是此命令集合下的其中一個子命令. 之後可以用 d2, d3 ..., 全部小寫
  • 以上都可隨意自由訂定. 除了類別名稱要清楚明確外, 另兩個我都會用短縮寫, 在執行命令時, 省時省力 (懶人法則)

請先完成下列作業, 產生所需要的程式框架.

執行

art make:command ReportDonation1 --command=rpt:d1

# 查看加入了那些檔案
git clean -n

# 檔案在這裡
ll app/Console/Commands/ReportDonation1.php

# 執行看看
art rpt

# 執行看看
art rpt:d1

# 執行看看
art rpt:d1 --help

回應

[ak4567@forblind]$ art make:command ReportDonation1 --command=rpt:d1
Console command created successfully.
[ak4567@forblind]$ git clean -n
Would remove app/Console/Commands/ReportDonation1.php
[ak4567@forblind]$ ll app/Console/Commands/ReportDonation1.php
-rw-r--r--  1 alexkuo  staff  675  1 28 16:19 app/Console/Commands/ReportDonation1.php

[ak4567@forblind]$ art rpt

 Command "rpt" is not defined.

 Do you want to run "rpt:d1" instead?  (yes/no) [no]:
 > yes

[ak4567@forblind]$ art rpt:d1
[ak4567@forblind]$ art rpt:d1 --help
Description:
  Command description

Usage:
  rpt:d1

Options:
  -h, --help            Display this help message
  -q, --quiet           Do not output any message
  -V, --version         Display this application version
      --ansi            Force ANSI output
      --no-ansi         Disable ANSI output
  -n, --no-interaction  Do not ask any interactive question
      --env[=ENV]       The environment the command should run under
  -v|vv|vvv, --verbose  Increase the verbosity of messages: 1 for normal output, 2 for more verbose output and 3 for debug
[ak4567@forblind]$

最後直接執行時, 都沒有任何回應, 只有 --help 會顯示預設的 Description, Usage, Options. 沒關係, 這是正常的.

git a .
git cm '1. 命令列程式 rpt:d1 初始化
2. Donation Model 初始化'

程式實作

vi app/Console/Commands/ReportDonation1.php

use 必要的 Class

use Illuminate\Console\Command;
use DB;
use Log;
use Storage;
use App\Donation;

主要入口 handle(), 直接呼叫

public function handle()
{
    $this->xlsx_v01();
}

加入第一個版本 xlsx_v01() 的程式

/**
 * 第一個版本, 初步完成, 但還要判斷 "捐款" 跟 "捐物" 的項目
 * @date 2020-01-29 13:23:36 (星期3)
 *
 * @return int
 */
public function xlsx_v01()
{
    // 顯示 "開始" 訊息到終端螢幕, command 的內建方法. 有 info() 一般訊息, line() 單列訊息,
    // comment() 註解訊息, question() 問題訊息, 和 error() 錯誤訊息等方法,
    // 每一種方法將會依據它所代表的目的, 分別對應一種適當的 ANSI 顏色.
    $this->info('info 一般訊息, 第一個版本開始...');
    $this->line('line 單列訊息, 第一個版本開始...');
    $this->comment('comment 註解訊息, 第一個版本開始...');
    $this->question('question 問題訊息, 第一個版本開始...');
    $this->error('error 錯誤訊息, 第一個版本開始...');

    // 啟動 DB Query Log, 顯示 PDO 產生的 sql statement
    DB::enableQueryLog();

    // Eloquent 開始
    // 對照 sql statement
    // table 的 alias 一定要用 as
    // from 主資料表
    $res = \App\Donation::from('donation as d')
        // left join 其他三個資料表
        ->leftJoin('donation_receipt as r', 'd.donation_ID', '=', 'r.donation_ID')
        ->leftJoin('donation_amount as a', 'd.donation_ID', '=', 'a.donation_ID')
        ->leftJoin('donation_goods as g', 'd.donation_ID', '=', 'g.donation_ID')
        // 條件之一, 狀態為 1:正常, 2:作癈. In 的參數要用 [] 陣列
        ->whereIn('d.status', [1, 2])
        // 條件之二, 收據日期, 多個組合, 要用 [[]] 二維陣列
        ->where([
            ['r.receiptDate', '>=', '2019-01-01'],
            ['r.receiptDate', '<=', '2019-01-07'],
        ])
        // 排序, 使用 收據編號
        ->orderBy('r.receiptSN', 'ASC')
        // 筆數, 99 筆
        ->take(99)
        // 最後, 取出 get(), 並給它要取出的欄位陣列
        ->get([
            'd.donation_ID', // ID
            'd.status', // 狀態
            'd.donationSN', // DSN捐贈單號
            'r.receiptSN', // 收據編號
            'r.receiptDate', // 收據日期
            'a.amount', // 捐款項目
            'g.goodsName', // 捐物項目
            'r.receiptTitle', // 收據抬頭
            'd.discardInfo', // 作廢原因
            'd.discardDateTime', // 作廢時間
            'd.remarks', // 備註
        ]);
    // Log 資料庫的 sql statement, 以方便對照是否符合正確的需求
    Log::debug(DB::getQueryLog());
    dump(DB::getQueryLog());
    printf("\nsql statement = %s", var_export(DB::getQueryLog(), true));

    // 取得筆數
    $cnt = $res->count();
    printf("\n取得筆數 = %d", $cnt);

    // data 二維陣列用來寫入 xlsx
    $data = []; // 先清空
    $i = 0;

    // 結果逐一顯示
    // 此版本, 僅先演示產生所有資料, 但仍不符合需求, 下一個版本再修正
    foreach ($res as $r) {
        $i++;
        printf("\n#%d-%d : ID=%d, title=%s", $cnt, $i, $r->donation_ID, $r->receiptTitle);
        $data[] = [
            $r->receiptSN, // 收據編號
            $r->receiptDate, // 收據日期
            $r->amount, // 項目
            $r->receiptTitle, // 收據抬頭
            $r->remarks, // 備註
        ];
    }

    // Log data[[]], 看看是否為我們要的欄位
    dump($data);
    Log::debug('data 二維陣列 = ' . var_export($data, true));
    // printf("\ndata 二維陣列 = " . var_export($data, true));

    // 使用 \Vtiful\Kernel\Excel 寫入 xlsx, 存檔到 storage/app/public 子目錄下
    $config = ['path' => Storage::disk('public')->path('/')];
    $excel = new \Vtiful\Kernel\Excel($config);
    $xlsxFilename = sprintf('捐款收據清單-%s.xlsx', date('Ymd.His'));
    $fileObject = $excel->fileName($xlsxFilename);

    // 寫入完成後, 回傳完整 xlsx 檔名路徑
    $xlsxFilenamePathFull = $fileObject->header([
        '收據編號', '收據日期', '項目', '收據抬頭', '備註'
    ])
        ->data($data)
        ->output();
    //printf("\n寫入 %d 筆 xlsx 完成, 完整 xlsx 檔名路徑 = %s", $cnt, $xlsxFilenamePathFull);
    $this->comment('寫入 ' . $cnt . ' 筆 xlsx 完成, 完整 xlsx 檔名路徑 = ' . $xlsxFilenamePathFull);

    // 最後完成, 顯示結束
    $this->output->success('結束');
    return 0;
}

執行

art rpt:d1

程式開發時, 我會加上一些 printf(...), dump(...) 在程式裡, 便於了解整個過程是否符合預期

執行時, 可以自行選擇性的把 printf(...), dump(...) 註解起來, 不要顯示

正式上線時, 使用 Log::debug(...) 存到 storage/logs/ 裡的 log 檔, 避免顯示太多訊息, 也可以在事後查詢過程中是否有其他未測試過而發生的邊際效應問題

回應

[ak4567@forblind]$ art rpt:d1
info 一般訊息, 第一個版本開始...
line 單列訊息, 第一個版本開始...
comment 註解訊息, 第一個版本開始...
question 問題訊息, 第一個版本開始...
error 錯誤訊息, 第一個版本開始...
...(中略)
取得筆數 = 17
 #17-1 : ID=7042, title=林......
 #17-2 : ID=7043, title=財......
 #17-3 : ID=7044, title=舒......
 #17-4 : ID=7045, title=心......
 #17-5 : ID=7046, title=研......
 #17-6 : ID=7047, title=卡......
 #17-7 : ID=7048, title=曾......
 #17-8 : ID=7049, title=蔡......
 #17-9 : ID=7050, title=李......
#17-10 : ID=7051, title=吳......
#17-11 : ID=7052, title=范......
#17-12 : ID=7053, title=景......
#17-13 : ID=7054, title=沈......
#17-14 : ID=7055, title=朱......
#17-15 : ID=7056, title=財......
#17-16 : ID=7057, title=財......
#17-17 : ID=7058, title=財......
...(中略)

寫入 17 筆 xlsx 完成, 完整 xlsx 檔名路徑 = /home/ak4567/sys20/storage/app/public/捐款收據清單-20200129.133428.xlsx

 [OK] 結束

查看 xlsx

把 /home/ak4567/sys20/storage/app/public/捐款收據清單-20200129.133428.xlsx 下載回來, 用 excel 打開查看
收據編號 收據日期 項目 收據抬頭 備註
10800001 2019-01-02 1000 林___
10800002 2019-01-02 150000 財___ 台___
10800003 2019-01-03 舒___
10800004 2019-01-03 心___
10800005 2019-01-03 研___
10800006 2019-01-03 3600 卡___
10800007 2019-01-03 300 曾___
10800008 2019-01-04 500 蔡___ 2___
10800009 2019-01-04 300 李___
10800010 2019-01-04 500 吳___
10800011 2019-01-04 2000 范___
10800012 2019-01-04 300 景___
10800013 2019-01-07 1000 沈___
10800014 2019-01-04 20000 朱___ 0___
10800015 2019-01-07 802157 財___ 台___
10800016 2019-01-07 661926 財___
10800017 2019-01-07 695841 財___

文件參考

繁體中文的文件, 都很舊, 也很零散, 以下是簡體中文版, 翻譯較完整

Eloquent 模型檢索

命令列 option 定義參數 參考

Laravel 6.x 中文文件