ࡶʹͭ͘Δ
 KPIπʔϧͷεεϝ 2015/03/28 Minami.rb ࠷ॳͰ࠷ޙͷLTେձ

ࢲ͸…. • @1syo • Rails Programmer • Yokohama.rb • 2012 Ruby kaja award winner • MinatoRubyKaig01 organizer • TDDBC Yokohama TA

MinatoRubyKaigi02

ͨͿΜ9݄, 10݄

KPIπʔϧͱ͸ʁ • Ϣʔβʔ਺.. • ച্… • ೔࣍ɺ݄࣍ɺྦྷܭ.. • Έ͍ͨͳΞϨ..

։ൃͷ࣮৘… • ϦϦʔε௚ޙ͔Βඞཁ • ཁ͕݅;ΜΘΓ͍ͯ͠Δ • ޻ظͱ͔ͳ͍

Ͳ͔͜ͰखΛൈ͔ͳ͍ͱਏ͍

਺ࣈͷਖ਼֬͞ > ࢖͍উख

άϥϑ = ࢮ

ͪΐͬͱલͷ΍ͭ

#! /bin/sh! host=localhost! database=db! user=dbuser! password=password! ! from_date=`date --date '1 days ago' +\%Y-\%m-\%d`! to_date=`date +\%Y-\%m-\%d`! ! [email protected]! [email protected]! subject=counter! bound=`date +"%Y%m%d%H%M%S"`! filename="filename_$from_date.csv"! ! sql="SELECT * FROM users WHERE created_at BETWEEN $from_date AND $to_date"! result=`mysql -u$user -p$password -h$host $database - e"$sql" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\r\n//g'`!

/usr/sbin/sendmail -t -i <<__MAIL__! From: "$from" ! To: "$to"! Subject: "$subject"! MIME-Version: 1.0! Content-Type: multipart/mixed; boundary=\"---------"$bound"\"! ! -----------"$bound"! Content-Type: Text/Plain; charset=ISO-2022-JP! Content-Transfer-Encoding: 7bit! ! Now: "$from_date"! ! --! `hostname`! "$from"! ! -----------"$bound"! Content-Type: application/octet-stream! name="$filename"! Content-Transfer-Encoding: base64! Content-Disposition: attachment;! filename="$filename"! ! `echo -e "$result" | base64`! ! -----------"$bound"--! __MAIL__

֓ཁ • mysqlίϚϯυͰSQLΛୟ͍ͯ • ݁ՌΛawkͰCSVʹͯ͠ • mailͷఴ෇ϑΝΠϧʹͯ͠ૹ৴

͋ͱ͸ΤΫηϧઌੜཔΉʂ

ૂ͍ • SQLͰਖ਼֬ͳ਺ࣈΛग़͢ͷʹઐ೦͍ͨ͠ • ΈΜͳେ޷͖ΤΫηϧઌੜͷݞʹ৐Γ͍ͨ • ؒҧ͍Λࢦఠ͞ΕͨΒ௚͙ʹमਖ਼͍ͨ͠

Α͍఺ • SQLͰΞΫηεͰ͖Ε͹େ఍ूܭͰ͖Δ • ৔߹ʹΑͬͯ͸shellΛۦ࢖ͯ͠ϑΝΠϧ΋ू ܭͰ͖Δ • େྔੜ࢈͠΍͍͢

ѱ͍఺ • sendmail͕࢖͑ͳ͍ͱͳ͍ͱख٧·Γ • ʮʓʓ͞Μ΋ݟ͍ͨʯ͕݁ߏ໘౗ • ूܭ݁Ռ͕෼ࢄ

࠷ۙͷ΍ͭ

module KPI! module Summary! class SalesReport! def client! @client ||= Mysql2::Client.new(! host: Settings.kpi.database.host,! database: Settings.kpi.database.xxxxxxx,! username: config['username'],! password: config['password']! )! end! ! ! ### தུ ####! def collect! sql = <<-__SQL__! select *! from (#লུ...) salse_reports! where placed_at >= '#{start_at}'! and placed_at < '#{end_at}'! order by placed_at! __SQL__! client.query(sql)! end! ! def rows! @rows ||= collect.map { |row| row.values }! end! ! def save! spreadsheet = KPI::GoogleDrive::Collection.new.spreadsheet(@start_at)! sheet = spreadsheet.worksheet(:sales_report, rows)! sheet.clear! sheet.save! end! end! end! end

֓ཁ • msql2 ͰSQLୟ͍ͯ • ݁ՌΛRubyͰΰχϣΰχϣͯ͠ • GoogleDriveʹอଘ͢Δ

͋ͱ͸GoogleઌੜཔΉʂ

ྑ͍఺ • sendmail͕͍Βͳ͍ • ݖݶ෇༩͕ΤϯυϢʔβʔͰ΋Ͱ͖Δ • ूܭ݁Ռͷू໿͕Ͱ͖Δ • APIܦ༝Ͱ࠶ར༻΋Ͱ͖Δ • GoogleDocsͷ͍͢͝ػೳͷԸܙΛड͚ΕΔ

ѱ͍఺ • OAuthͷ։ൃ໘౗ • refresh_tokenʹ༗ޮظݶ͕͋Δ • ηϧͷܗࣜࢦఆʹบ͕͋Δ

GoogleDocsͷ͍͢͝ػೳ

QUERYؔ਺ =QUERY(A2:E6,"select count(A) where B > 5”) ूܭʹඞཁͳதؒσʔλΛGoogleDocsʹొ࿥ ͢Ε͹͋ͱ͸ˢͰରԠͰ͖ΔΑ͏ʹͳͬͨ

DEMO

ࡶʹߦ͜͏!