ポイント
- FileMaker 独自のやり方というよりも、SQL で一般的に使える方法
- タイムスタンプのフィールドを、日付タイプのフィールドに変換する
- コード例:
WHERE CAST("作成日時" AS DATE) = '2016/04/29'
FileMaker のタイムスタンプタイプと日付タイプについて
- タイムスタンプ: yyyy/mm/dd hh:mm:dd 形式
- 日付: yyyy/mm/dd 形式
タグ: SQL
Visual Studio から SQL Server の DB に接続出来ている、という前提です。
の内容にプラスαしたものとなります。
ポイント
- SQL Server Object Explorer、またはサーバーエクスプローラーを使用する。
- 右クリック > 新しいクエリ、で開いたエディタで、データベースを選択して SQL を書いて実行する。
- 実行は、エディタエリア左上部の「▷」アイコン、Execute (Ctrl+Shift+E) で行う。
- 実行結果がウインドウの下に表示され、結果を確認できる。
- テーブルやカラム名の補完が効く (Ctrl + スペース) ので積極的に使っていきたい。
ポイント
- テーブルをエンティティとして配置するところまで自動で作成できる
- リレーションは設定されない
具体的な手順♪
出力したいものの仕様はこうします♪
- 「商品名」「規格1」「規格2」,「商品名」「規格1」「規格2」…、という形で 1 回で買った受注の商品をひとつのセルに出力させたい。
- 北海道,東京都,沖縄県…、という形で 1 回で買った受注のお届け先都道府県をひとつのセルに出力させたい。
- 商品は小計（dtb_order.subtotal）の前に、お届け先都道府県は配送情報 ID の次に出力させる。
SQL ポイント
受注の各商品も CSV 出力する SQL 文
受注の CSV 出力には、商品名は出力されません。
なぜなら、EC-CUBE の受注 CSV ダウンロードは 1 受注を1 行に出力する仕様でございますもの。
もし、1 回の買物で、複数種類の商品を買った場合、これらの商品をどうやって CSV に出力すればよいかしら？商品すべてを 1 つのセルに書きだすようにすればよいかしら？
できそうですわね！では、それぞれの商品の値段、購入した個数も知りたくなったらどうかしら？ひとつのセルに押し込めるかしら？ムギュウムギュウ♪
素直にはできませんわね＞＜。大変難しいですの！
考え方はいろいろございますけれども、1 行 に 1 商品で出力します。そして、各行にその受注の合計金額など他の情報も掲載します。そのような方法も採れますわ、悪く無いと思いますの。
複雑さが増してしまいますけれども、CSV でしたら エクセルを念頭においていると存じます。まだ現実的な範囲内ではないかしら？
そのようなわけで、これを実現する SQL 文を書いてみましたわ♪
コンテンツ管理＞CSV出力項目設定＞高度な設定に設定する SQL 文
実際にフォームに入力するときは、1 行目の SELECT は入力しません。また、最後の 1 文の ; も入力しません。
SELECT
dtb_order.order_id,
dtb_order.customer_id,
dtb_order.message,
dtb_order.order_name01,
dtb_order.order_name02,
dtb_order.order_kana01,
dtb_order.order_kana02,
dtb_order.order_email,
dtb_order.order_tel01,
dtb_order.order_tel02,
dtb_order.order_tel03,
dtb_order.order_fax01,
dtb_order.order_fax02,
dtb_order.order_fax03,
dtb_order.order_zip01,
dtb_order.order_zip02,
(SELECT mtb_pref.name FROM mtb_pref WHERE mtb_pref.id = dtb_order.order_pref),
dtb_order.order_addr01,
dtb_order.order_addr02,
dtb_order.order_sex,
dtb_order.order_birth,
dtb_order.order_job,
dtb_order.subtotal,
dtb_order.discount,
dtb_order.deliv_fee,
dtb_order.charge,
dtb_order.use_point,
dtb_order.add_point,
dtb_order.tax,
dtb_order.total,
dtb_order.payment_total,
dtb_order.deliv_id,
dtb_order.payment_method,
dtb_order.note,
dtb_order.status,
dtb_order.create_date,
dtb_order.update_date,
dtb_order.commit_date,
dtb_order.device_type_id,
(SELECT count(*) FROM dtb_shipping WHERE dtb_shipping.order_id = dtb_order.order_id),
(SELECT ARRAY_TO_STRING(ARRAY(SELECT dtb_shipping.shipping_id FROM dtb_shipping WHERE dtb_shipping.order_id = dtb_order.order_id), ','))
FROM
dtb_order
WHERE
del_flg = 0
;
サンプルソース
<?php
// table の id に対して次の値が検索条件として渡されてくると仮定
$data = array(2, 7, 14, 23);
/*
* 入力値の数だけ ,? を繰り返し、２文字目以降を取得することで動的に SQL の IN 句のバインドを生成
*
* １．count(array(2, 7, 14, 23)) で渡されてきた入力パラメーターの数をかぞえる。結果は 4 となる。
* ２．str_repeat(',?', 4) で ',?' を ４ 回繰り返す。結果は ,?,?,?,? となる。
* ３．substr(',?,?,?,?', 1) で ,?,?,?,? 文字列の ２ 番目から最後までを返す。結果は ?,?,?,? となる。
*/
$sql = 'SELECT * FROM testtable WHERE id IN(' . substr(str_repeat(',?', count($data)), 1) . ')';
// SELECT * FROM testtable WHERE id IN(?,?,?,?) と表示されれば成功！
echo $sql;
?>