makeshop の売上を毎日自動集計してメールで送る

EC の ASP「makeshop」の売上を毎日自動集計して携帯に送るスクリプトを書いた。

<pre class="prettyprint">#!/bin/sh

#
# 設定
#
# MySQL のユーザには FILE permission が必要。
# grant file on . to makeshop@localhost;
#

MAKESHOP_CREDENTIAL=”-d id=shopid -d passwd=shoppw -d submanager=ok -d
etclogin=ok”
MYSQL_CREDENTIAL=”-umakeshop -pmakeshop”

RECIPIENTS=”shop1@fooshop.com, shop2@fooshop.com”


#
# Makeshop からデータをダウンロードする
#
function download_from_makeshop {

local year=$1
local month=$2
local day=$3
local file=$4

local COOKIE=cookie.txt
local NULL=”-o /dev/null”

local LOGIN_PAGE=http://www.fooshop.jp/login/login.html
local INDEX_PAGE=http://www.fooshop.jp/makeshop/newmanager/index2.html
local ORDER_CHECK_PAGE=http://www.fooshop.jp/makeshop/newmanager/order_check_excel.html

# ダウンロードする
curl -c $COOKIE $NULL $MAKESHOP_CREDENTIAL -e $LOGIN_PAGE $INDEX_PAGE
curl -b $COOKIE -o order_sjis.csv -d csv_type=check -d year1=$YEAR
-d month1=$MONTH -d day1=$DAY -d year2=$YEAR -d month2=$MONTH -d
day2=$DAY -d paymethod=ALL -d delivery1=A -d excel1=check
$ORDER_CHECK_PAGE

# Makeshop の CSV は SJIS なので UTF8 に変換する
iconv -f MS932 -t UTF8 order_sjis.csv > order_utf8.csv

# 売上を知るのに必要なカラムのみ取り出す
awk ‘BEGIN { FS=”,”; OFS=”,”; } /^[0-9]/ {print $1, $2, $31, $33,
$34, $36; sales += $33 * $34; }’ order_utf8.csv | sed
-e’s/=”(.)”/\1/g’ > $file

# お掃除
rm order_sjis.csv order_utf8.csv
}

function import_sales_data {
local csv=$1
mysql $MYSQL_CREDENTIAL makeshop «EOF
load data local infile “$csv”
into table sales
fields terminated by ‘,’
(date, order_id, item_name, amount, price, item_id)
;
EOF
}

#
# 日付ディメンションテーブルを更新する
#
function update_dates_table {
mysql $MYSQL_CREDENTIAL makeshop «EOF
call update_dates_table;
EOF
}

#
# 売上を集計する
#
function summarize {

local output=$1

local TIME=date +%Y%m%d_%H%M%S
local DAILY_SALES=/tmp/makeshop_daily_sales_$TIME.csv
local WEEKLY_SALES=/tmp/makeshop_weekly_sales_$TIME.csv
local MONTHLY_SALES=/tmp/makeshop_monthly_sales_$TIME.csv

mysql $MYSQL_CREDENTIAL makeshop «EOF
– 今月の日時売上の集計
select
date,
sum(amount * price) sales
from
sales
where
date >= date_sub(curdate(), interval day(curdate()) - 1 day)
group by
date
order by
date desc
into outfile “$DAILY_SALES” fields terminated by ‘, ‘
;

– 週次売上
select
min(d.date) start_date,
ifnull(sum(amount * price), 0) sales
from
dates d left outer join sales s
on s.date = d.date
group by
d.yearweek
order by
d.yearweek desc
into outfile “$WEEKLY_SALES” fields terminated by ‘, ‘
;

– 月次売上の集計
select
d.year,
d.month,
sum(amount * price) sales
from
sales s join dates d
on s.date = d.date
group by
d.year,
d.month
order by
d.year desc,
d.month desc
into outfile “$MONTHLY_SALES” fields terminated by ‘, ‘
;
EOF

cat «EOF > $output
Daily Sales
——————–
cat $DAILY_SALES

Weekly Sales
——————–
head -10 $WEEKLY_SALES
http://www.fooshop.com/weekly_sales.gif

Monthly Sales
——————–
cat $MONTHLY_SALES
EOF

generate_graph $WEEKLY_SALES /var/www/html/sales/weekly.gif
}

#
# グラフを作成する
#
function generate_graph {

local csv=$1
local image=$2

gnuplot «EOF
set xdata time
set timefmt “%Y-%m-%d”
set terminal gif size 240,160
set output “$image”
unset key
set style line 2 lt 2 lw 1
plot “$csv” using 1:2 with line title “weekly sales”,<br /> 20000 with lines ls 2,<br /> 40000 with lines ls 2,<br /> 60000 with lines ls 2,<br /> 80000 with lines ls 2,<br /> 100000 with lines ls 2,<br /> 120000 with lines ls 2
EOF
}

#
# 結果をメールで送る
#
function send_mail {
local file=$1
mail -s “sales report” -b “$RECIPIENTS” shop@fooshop.com < $file
}

# ダウンロード対象日
YEAR=date -d yesterday +%Y
MONTH=date -d yesterday +%m
DAY=date -d yesterday +%d

download_from_makeshop $YEAR $MONTH $DAY order.csv
import_sales_data order.csv
update_dates_table
summarize mailbody.txt
send_mail mailbody.txt

rm /tmp/makeshop_.csv
</pre>

<pre class="prettyprint">–
– 売上テーブル
–
drop table if exists sales;

create table sales (
id integer primary key auto_increment,
date date,
order_id varchar(64),
item_name varchar(255),
amount integer,
price integer,
item_id varchar(255),
index date(date),
index item_name(item_name),
index item_id(item_id),
unique index (order_id, item_id)
);

–
– 日付ディメンションテーブル
–
drop table if exists dates;

create table dates (
id integer primary key auto_increment,
date date,
year integer,
month integer,
day integer,
week integer,
yearweek integer,
index date(date),
index year(year),
index month(month),
index day(day),
index week(day),
index yearwk(yearweek)
);

–
– 日付ディメンションテーブルの更新
–
drop procedure if exists update_dates_table;

delimiter //
create procedure update_dates_table()
begin
declare currentdate date default ‘2008-12-01’;
truncate dates;
while currentdate <= curdate() do
insert into dates(date, year, month, day, week, yearweek)
values(
currentdate,
year(currentdate),
month(currentdate),
day(currentdate),
week(currentdate, 2),
yearweek(currentdate, 0)
);
set currentdate = date_add(currentdate, interval 1 day);
end while;
end
//
delimiter ;
</pre>