Last active
July 21, 2025 02:18
-
-
Save ckhung/bb3e6c78119f8c0d3b3cf142068f2fe3 to your computer and use it in GitHub Desktop.
共筆維護試算表、地圖自動更新，大尺寸 (較大試算表) 版本
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| // dot -Tsvg flow.dot > flow.svg | |
| digraph "gs2umap 資料流向 (上下游關係)" { | |
| rankdir = TB; | |
| overlap = scale; | |
| key [ label="gsheet-key.txt" ]; | |
| gs [ label="google sheet" ]; | |
| php [ label="gs2umap.php" ]; | |
| python [ label="gs2umap.py" ]; | |
| csv [ label="state-organs.csv" ]; | |
| { key gs } -> php -> python -> csv -> umap; | |
| } |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <?xml version="1.0" encoding="UTF-8" standalone="no"?> | |
| <!DOCTYPE svg PUBLIC "-//W3C//DTD SVG 1.1//EN" | |
| "http://www.w3.org/Graphics/SVG/1.1/DTD/svg11.dtd"> | |
| <!-- Generated by graphviz version 2.42.4 (0) | |
| --> | |
| <!-- Title: gs2umap 資料流向 (上下游關係) Pages: 1 --> | |
| <svg width="316pt" height="332pt" | |
| viewBox="0.00 0.00 315.94 332.00" xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink"> | |
| <g id="graph0" class="graph" transform="scale(1 1) rotate(0) translate(4 328)"> | |
| <title>gs2umap 資料流向 (上下游關係)</title> | |
| <polygon fill="white" stroke="transparent" points="-4,4 -4,-328 311.94,-328 311.94,4 -4,4"/> | |
| <!-- key --> | |
| <g id="node1" class="node"> | |
| <title>key</title> | |
| <ellipse fill="none" stroke="black" cx="75.39" cy="-306" rx="75.29" ry="18"/> | |
| <text text-anchor="middle" x="75.39" y="-302.3" font-family="Times,serif" font-size="14.00">gsheet-key.txt</text> | |
| </g> | |
| <!-- php --> | |
| <g id="node3" class="node"> | |
| <title>php</title> | |
| <ellipse fill="none" stroke="black" cx="156.39" cy="-234" rx="73.39" ry="18"/> | |
| <text text-anchor="middle" x="156.39" y="-230.3" font-family="Times,serif" font-size="14.00">gs2umap.php</text> | |
| </g> | |
| <!-- key->php --> | |
| <g id="edge1" class="edge"> | |
| <title>key->php</title> | |
| <path fill="none" stroke="black" d="M94.59,-288.41C105.08,-279.34 118.24,-267.97 129.71,-258.06"/> | |
| <polygon fill="black" stroke="black" points="132.06,-260.66 137.34,-251.47 127.48,-255.36 132.06,-260.66"/> | |
| </g> | |
| <!-- gs --> | |
| <g id="node2" class="node"> | |
| <title>gs</title> | |
| <ellipse fill="none" stroke="black" cx="238.39" cy="-306" rx="69.59" ry="18"/> | |
| <text text-anchor="middle" x="238.39" y="-302.3" font-family="Times,serif" font-size="14.00">google sheet</text> | |
| </g> | |
| <!-- gs->php --> | |
| <g id="edge2" class="edge"> | |
| <title>gs->php</title> | |
| <path fill="none" stroke="black" d="M218.96,-288.41C208.34,-279.34 195.02,-267.97 183.4,-258.06"/> | |
| <polygon fill="black" stroke="black" points="185.56,-255.3 175.69,-251.47 181.02,-260.62 185.56,-255.3"/> | |
| </g> | |
| <!-- python --> | |
| <g id="node4" class="node"> | |
| <title>python</title> | |
| <ellipse fill="none" stroke="black" cx="156.39" cy="-162" rx="67.69" ry="18"/> | |
| <text text-anchor="middle" x="156.39" y="-158.3" font-family="Times,serif" font-size="14.00">gs2umap.py</text> | |
| </g> | |
| <!-- php->python --> | |
| <g id="edge3" class="edge"> | |
| <title>php->python</title> | |
| <path fill="none" stroke="black" d="M156.39,-215.7C156.39,-207.98 156.39,-198.71 156.39,-190.11"/> | |
| <polygon fill="black" stroke="black" points="159.89,-190.1 156.39,-180.1 152.89,-190.1 159.89,-190.1"/> | |
| </g> | |
| <!-- csv --> | |
| <g id="node5" class="node"> | |
| <title>csv</title> | |
| <ellipse fill="none" stroke="black" cx="156.39" cy="-90" rx="87.18" ry="18"/> | |
| <text text-anchor="middle" x="156.39" y="-86.3" font-family="Times,serif" font-size="14.00">state-organs.csv</text> | |
| </g> | |
| <!-- python->csv --> | |
| <g id="edge4" class="edge"> | |
| <title>python->csv</title> | |
| <path fill="none" stroke="black" d="M156.39,-143.7C156.39,-135.98 156.39,-126.71 156.39,-118.11"/> | |
| <polygon fill="black" stroke="black" points="159.89,-118.1 156.39,-108.1 152.89,-118.1 159.89,-118.1"/> | |
| </g> | |
| <!-- umap --> | |
| <g id="node6" class="node"> | |
| <title>umap</title> | |
| <ellipse fill="none" stroke="black" cx="156.39" cy="-18" rx="36.29" ry="18"/> | |
| <text text-anchor="middle" x="156.39" y="-14.3" font-family="Times,serif" font-size="14.00">umap</text> | |
| </g> | |
| <!-- csv->umap --> | |
| <g id="edge5" class="edge"> | |
| <title>csv->umap</title> | |
| <path fill="none" stroke="black" d="M156.39,-71.7C156.39,-63.98 156.39,-54.71 156.39,-46.11"/> | |
| <polygon fill="black" stroke="black" points="159.89,-46.1 156.39,-36.1 152.89,-46.1 159.89,-46.1"/> | |
| </g> | |
| </g> | |
| </svg> |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <?php | |
| header('Content-type: text/plain'); | |
| $mydir = dirname(__FILE__); | |
| $proj_table = [ | |
| 'so' => '1x26waGdKyGDNJMcmTP4XX6-39B2lvqlL5YcSoau16KE:main:config:state-organs.csv', | |
| ]; | |
| $gskey=file_get_contents('/home/ckhung/secret/gsheet-key.txt'); | |
| parse_str($_SERVER['QUERY_STRING'], $ARGS); | |
| if (! array_key_exists('p', $ARGS)) | |
| exit("QUERY_STRING 內找不到必要參數 'p'"); | |
| $projs = explode(',', $ARGS['p']); | |
| foreach ($projs as $proj_name) { | |
| if (! array_key_exists($proj_name, $proj_table)) { | |
| echo("未定義的專案名稱 '$proj_name'\n"); | |
| continue; | |
| } | |
| $tmp = explode(':', $proj_table[$proj_name]); | |
| [$gs_id, $data_sheet, $config_sheet, $out_file] = $tmp; | |
| assert(preg_match('/^[\w-]+$/', $gs_id)); | |
| assert(preg_match('/^[\w-]+$/', $data_sheet)); | |
| assert(preg_match('/^[\w-]+$/', $config_sheet)); | |
| assert(preg_match('/^[\w\.-]+$/', $out_file)); | |
| $cfg_rows = shell_exec("curl -G 'https://sheets.googleapis.com/v4/spreadsheets/$gs_id/values/$config_sheet' --data-urlencode key='$gskey' | jq -r '.values as \$r | (\$r[0]), (\$r[1:][]) | @csv'"); | |
| $cfg_rows = preg_split("#[\r\n]+#", $cfg_rows); | |
| $opt_str = ''; | |
| foreach ($cfg_rows as $line) { | |
| $line = str_replace('"', '', $line); | |
| $line = explode(',', $line); | |
| if (count($line) != 2) continue; | |
| // input sanitization | |
| if (! preg_match('/^\w+/', $line[0], $matches)) continue; | |
| $key = $matches[0]; | |
| if (! preg_match('/^#?\w+/', $line[1], $matches)) continue; | |
| $value = $matches[0]; | |
| if (strlen($key)>0) | |
| $opt_str .= " --$key='$value'"; | |
| } | |
| print("$proj_name: $opt_str\n"); | |
| $ret = shell_exec("curl -G 'https://sheets.googleapis.com/v4/spreadsheets/$gs_id/values/$data_sheet' --data-urlencode key='$gskey' | jq -r '.values as \$r | (\$r[0]), (\$r[1:][]) | @csv' | $mydir/gs2umap.py $opt_str > $mydir/$out_file"); | |
| } | |
| $date = date('Y/m/d H:i:s'); | |
| echo($date); | |
| ?> | |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| #!/usr/bin/python3 | |
| import argparse, csv, sys | |
| from datetime import datetime | |
| from itertools import zip_longest | |
| parser = argparse.ArgumentParser( | |
| description='google sheet to umap csv', | |
| formatter_class=argparse.ArgumentDefaultsHelpFormatter) | |
| parser.add_argument('-c', '--color', type=str, default='#00f', | |
| help='color of a marker on map') | |
| parser.add_argument('-f', '--future_color', type=str, default='#f00', | |
| help='color of a marker on map') | |
| args = parser.parse_args() | |
| today = datetime.today() | |
| uniq_table = {} | |
| reader = csv.reader(sys.stdin) | |
| header = next(reader) | |
| for row in reader: | |
| item = dict(zip_longest(header, row)) | |
| place = item['縣市'] + ' ' + item['地點'] | |
| item['地點'] = place | |
| del item['縣市'] | |
| del item['放映時間'] | |
| item['日期'] = datetime.strptime(item['日期'], '%y/%m/%d') | |
| if not place in uniq_table: | |
| uniq_table[place] = item | |
| uniq_table[place]['播放場數'] = 0 | |
| uniq_table[place]['播放場數'] += 1 | |
| if 'latitude' in item: | |
| uniq_table[place]['latitude'] = item['latitude'] | |
| uniq_table[place]['longitude'] = item['longitude'] | |
| uniq_table[place]['地址'] = item['地址'] | |
| if item['日期'] >= today: | |
| if uniq_table[place]['日期'] > item['日期']: | |
| # 新的一筆記錄是更近的未來 | |
| uniq_table[place]['日期'] = item['日期'] | |
| print('color,日期,地點,播放場數,latitude,longitude,地址') | |
| for place, item in uniq_table.items(): | |
| if item['日期'] >= today: | |
| item['日期'] = item['日期'].strftime('%Y/%m/%d') | |
| color = args.future_color | |
| else: | |
| item['日期'] = '' | |
| color = args.color | |
| print('{},{},{},{},{:.6f},{:.6f},{}'.format(color, item['日期'], place, item['播放場數'], float(item['latitude']), float(item['longitude']), item['地址'])) | |
| ''' | |
| 日期,放映時間,縣市,地點,latitude,longitude,地址 | |
| 2025/12/31,12:15,彰化,彰基 | |
| 2025/12/30,12:15,彰化,彰基 | |
| 2025/12/13,12:15,彰化,彰基 | |
| 2025/12/13,7:15,彰化,彰基 | |
| 2025/11/03,14:00,高雄,總圖店喜樂時代影城 | |
| 2025/07/05,14:00,雲林,鵝媽媽鵝童樂園(雲林縣勞工育樂中心) | |
| 2025/06/15,,台南,勞工育樂中心 | |
| 2025/06/08,14:30,台南,麻豆戲院 | |
| 2025/06/07,19:00,新竹,新豐鄉公益場-新豐中正堂,24.8619561,120.9900924,新竹縣新豐鄉商學館 | |
| 2025/06/07,14:00,雲林,鵝媽媽鵝童樂園(雲林縣勞工育樂中心),23.6808545,120.5523373,雲林縣鬥六市嘉新路222號 | |
| 2025/06/07,13:30,新竹,新埔公益場-新埔下寮里活動中心 | |
| 2025/06/07,10:00,新竹,新埔公益場-新埔下寮里活動中心,24.8488016,121.0363768,新竹縣新埔鎮下寮裡 | |
| ''' |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment