売り上げ管理システムとして完成させる為には、画面入力->処理->画面出力という流れが必要です。ここでは、「処理」の部分をsql 文として構築してきました。アプリケーションとして作り上げるには、何らかのプログラミング言語をつかって、コードを書く必要があります。
sql文をアプリケーションから利用する観点から、使用されるであろうものを列挙してみます。関数の引数を$nameという変数として表現しています。
レコードを追加する
参照されるテーブル(buyer branch goods)
---- 購入者テーブルbuyerにデータを入力する
insert into buyer (id, name) values ($id, $name);
.import --csv $buyer_list,csv buyer
---- 店テーブル branch にデータを入力する
insert into branch (id, name) values($id, $name);
.import --csv $branch_list.csv branch
---- 商品テーブル goods にデータを入力する
insert into goods (code, name, price) values($code, $name, $price);
.import --csv $goods_list,csv goods日々追加されるテーブル(orders details)
---- 注文テーブルordersにcsvファイルから一括して格納する
.import --csv $orders_date_list.csv orders
---- 注文テーブルorders にデータを格納する
insert into orders (id, buyer_id, publish, branch_id, amount)
values ($id, $buyer_id, $publish, $branch_id, $amount); -- ①
---- 注文詳細テーブルdetailsにcsvファイルから一括して格納する
.import --csv $details_data_list.csv details
---- 注文詳細テーブルにデータを格納する
-- c_markが無い場合
insert into details (orders_id, goods_id, quantity)
values ($orders_id, $goods_id, $quantity); -- ②
-- c_markが有効の場合
insert into details (orders_id, goods_id, quantity, c_mark)
values ($orders_id, $goods_id, $quantity, true); -- ③
-- 後から追加(iud_mark='i')の場合
insert into details (orders_id, goods_id, quantity, iud_mark)
values ($orders_id, $goods_id, $quantity,'i'); -- ④① orders_before_insert beforeトリガーにより、branch_idおよびbuyer_idの外部キーチェックが行われる。tri_publish_chk afterトリガーにより日時チェックがおこなわれる。
② tri_amount afterトリガーにより、subtotal計算がおこなわれる。
③ details_before_insert betore トリガーにより、goods_idおよびorders_idの外部キーチェックが行われる。tri_amount_chk afterトリガーにより、subtotal計算と該当orders_idのamount値のチェック、訂正が行われる。
④ details_before_insert betore トリガーにより、goods_idおよびorders_idの外部キーチェックが行われる。 tri_details_append afterトリガーにより、当日であるかを確認、subtotal計算、c_markに’i’がセットされる。該当orders_idのiud_markに’u’がセットされ、amountの再計算が行われる。
レコードを更新・削除する
購入者buyerテーブルデータの更新･削除
変更が生じた場合は、新たにidを発行して挿入する。旧レコードには削除マークをたてます。削除の場合は、該当レコードに削除マークを立てます。
---- 更新
update buyer set del_flag=true where id=$id;
insert into buyer (id, name) values ($new_id, $new_name);
---- 削除
update buyer set del_flag=true where id=$id;店branchテーブルデータの更新
変更が生じた場合は、新たにidを発行して挿入する。旧レコードには削除マークをたてます。削除の場合は、該当レコードに削除マークを立てます。
---- 更新
update branch set del_flag=true where id=$id;
insert into branch (id, name) values ($new_id, $new_name);
---- 削除
update branch set del_flag=true where id=$id;商品goodsテーブルデータの更新・削除
商品の価格変更はupdateで行います。その際、価格変更履歴テーブルに旧価格と新価格のログを残します。削除したい場合は、該当レコードに削除マークを立てます。価格変更履歴テーブルの新価格には0をセットします。
---- 更新
update goods set price=$chg_price where code=$code; -- ⑤
---- 削除
update goods set del_flag=true where code=$code; -- ⑥⑤ tri_chg_price afterトリガーにより、価格変更履歴テーブルchglog_priceに旧価格と新価格のログが書き込まれます。
⑥ tri_goods_del afterトリガーにより、価格変更履歴テーブルchglog_priceに新価格を０としてログを書き込みます。
注文ordersテーブルの更新･削除
レコードの更新はdetailsテーブルの追加・削除により行われます。
---- 削除
update orders set iud_mark='d' where id=$id; -- ⑦⑦ tri_orders_del afterトリガーにより、注文当日であるかチェックされ、details テーブルの対象 orders_id の iud_mark列に ‘d+’ をセットする。
注文詳細detailsテーブルの更新･削除
---- 注文した商品の数量を変更する
$rowid = select rowid from details where orders_id=$orders_id and goods_id=$goods_id and iud_mark is $iud_mark; -- 対象rowidを特定する
update details set quantity=$quantity, iud_mark='u' where rowid=$rowid; -- ⑧
---- 注文した商品の一部を削除する
$rowid = select rowid from details where orders_id=$orders_id and goods_id=$goods_id and iud_mark=$iud_mark; -- 対象rowidを特定する
update details set iud_mark='d' where rowid=$rowid; -- ⑨
⑧ details_before_update betore トリガーにより、goods_idおよびorders_idの外部キーチェックが行われる。 tri_quantity_chg afterトリガーにより、当日であるかを確認、subtotal計算される。ordersテーブルの該当orders_idのiud_markに’u’ がセットされ、amountの再計算が行われる。
⑨ details_before_update betore トリガーにより、goods_idおよびorders_idの外部キーチェックが行われる。 tri_detail_del afterトリガーにより、当日であるかを確認される。ordersテーブルの該当orders_idのiud_markに’u’がセットされ、 amountの再計算が行われる。
レコードを検索する
---- 特定日の購買者の商品詳細データリスト
select * from details where orders_id=(select id from orders where buyer_id=$id and publish=$publish);
---- 指定購買者の商品支払い情報
select * from view_orders where buyer_name=$name;
---- 指定店舗の商品支払い情報
select * from view_orders where branch_name=$branch_name;
---- 特定商品の購買情報
select * from details where goods_id= (select code from goods where name=$name);
---- 特定商品のデータ登録情報
select * from goods where name=$goods_name;
---- 特定購買者のデータ登録情報
select * from buyer where name=$name;
---- 特定商品の価格改定情報
select * from chglog_price where goods_id=(select code from goods where name=$name);統計・分析をする
---- 特定月日総売り上げ
select sum(amount) from orders where publish=$publish and iud_mark is not 'd';
---- 特定年月別総売り上げ
/*
特定年$year
$pattern = $year. '-'. '__'. '-'. '__';
特定年月$year $month
$pattern = $year. '-'. $month. '-'. '__';
*/
select sum(amount) from orders where iud_mark is not 'd' and publish like $pattern;
---- 支店別注文データ 総売り上げ （日別、月別、年別）
/*
$pattern = $year. '-'. '__'. '-'. '__';
$pattern = $year. '-'. $month. '-'. '__';
$pattern = $year. '-'. $month. '-'. $day;
*/
select sum(amount) from view_orders where branch_name=$branch_name;
select sum(amount) from view_orders where branch_name=$branch_name and $pattern;
---- 指定商品の売り上げ個数（日別、月別、年別）
/*
$pattern = $year. '-'. '__'. '-'. '__';
$pattern = $year. '-'. $month. '-'. '__';
$pattern = $year. '-'. $month. '-'. $day;
*/
select sum(quantity), sum(subtotal) from details where goods_id=(select code from goods where name=$name) and orders_id=(select id from orders where publish like $pattern) and iud_mark is not 'd' and iud_mark is not 'd+';
---- 支店総数
select count() from branch where del_flag != true;
---- 商品総数（削除データは除く）
select count() from goods where del_flag != true;
