#!/bin/sh
###############################################
# #
# author:lishujun #
# date:2013-4-11 #
# use:$orderdetail 2013-4-11 #
# #
###############################################
### load Library ###
. /www/log/stat/db_alias
. /www/log/stat/public_func
init_date $1
init_db
### define global variable ###
bookStatTable='book_order'
bookNameFile='./tmp/book_name_2013'
orderDetailFile='./tmp/order_detail_2013'
cpBookFile='./tmp/cp_book'
clickedNumberFile='./tmp/click_number'
tempDataFile='./tmp/data'
scriptFile='./tmp/sql_script'
### functions ###
cleanTemporaryFiles()
{
echo clean temporary files...
rm $bookNameFile
rm $orderDetailFile
rm $cpBookFile
rm $clickedNumberFile
rm $tempDataFile
rm $scriptFile
}
executeSql()
{
echo writing to database...
cat $scriptFile | statdb -f
}
loadDataToFiles()
{
echo load data...
#load basic info : bookId, bookName, author, class, chapCount
echo "select id,bookname,author,class,num_chapter from bc_bookinfos" |bookdb -s|piconv -f utf8 -t gb2312 >$bookNameFile
#load cp books: cpId, bookId
echo "select cpid,bid from bc_book_charge_cp_bid" |bookdb -s|piconv -f utf8 -t gb2312 >$cpBookFile
#load book click number
echo "select id,num_today_click from bc_bookinfos_ext_stat" |bookdb -s|piconv -f utf8 -t gb2312 >$clickedNumberFile
#load order info : userId, bookId, chapId, orderAmount, size, date, time, chargeable
#for i in `echo "show tables like 'bc_user_charged_list_wap%';"|booknewdb -s`
#do
# sql="select userid,bid,chapterid,price,size,from_unixtime(charged_time),use_curr from $i"
# sql=$sql" where from_unixtime(charged_time) like '$stat_date%' "
#
# echo $sql | booknewdb -s
# #echo $sql
#done >$orderDetailFile
#load order info : userId, bookId,chapId, orderAmount, size, date, time, chargeable (from merge!!!)
sql="select userid,bid,chapterid,price,size,from_unixtime(charged_time),use_curr from bc_user_charged_list_wap_merge"
sql=$sql" where from_unixtime(charged_time) like '$stat_date%' "
echo $sql | booknewdb -s |piconv -f utf8 -t gb2312 > $orderDetailFile
}
stat()
{
echo stat...
awk -v statDate=$stat_date \
-v bookStatTable=$bookStatTable \
-v bookNameFile=$bookNameFile \
-v cpBookFile=$cpBookFile \
-v clickedNumberFile=$clickedNumberFile \
-v tempDataFile=$tempDataFile \
-v scriptFile=$scriptFile \
'
### util functions ###
function isVaild(value)
{
if(value > 0)
return 1
else
return 0
}
function computeARPU(bookId,books)
{
if(isVaild(books[bookId,"orderUserCount"]) == 0)
{
return 0
}
return books[bookId,"orderAmount"] / books[bookId,"orderUserCount"]
}
function readerConversionRate(bookId,books)
{
if(isVaild(books[bookId,"clickedUserCount"]) == 0)
{
return 0
}
return books[bookId,"readerCount"] / books[bookId,"clickedUserCount"]
}
function deepReaderConversionRate(bookId,books)
{
if(isVaild(books[bookId,"readerCount"]) == 0)
{
return 0
split(names,nameArray,",")split(names,nameArray,",")}
return books[bookId,"deepReaderCount"] / books[bookId,"readerCount"]
}
function readerOrderRate(bookId,books)
{
if(isVaild(books[bookId,"readerCount"]) == 0)
{
return 0
}
retunrn books[bookId,"orderUserCount"] / books[bookId,"readerCount"]
}
function browserOrderRate(bookId,books)
{
if(isVaild(books[bookId,"cickedUserCount"]) == 0)
{
return 0
}
retunrn books[bookId,"orderUserCount"] / books[bookId,"clickedUserCount"]
}
function lowerStyle(oldStr)
{
newStr = ""
for(j=1;j <= length(oldStr);j++)
{
oneChar = substr(oldStr,j,1)
if(match(oneChar,/[A-Z]/) == 0)
{
newStr = newStr "" oneChar
}
else
{
if(substr(oldStr,j)=="Id")
{
newStr = newStr "" tolower(oneChar)
}
else
{
newStr = newStr "_" tolower(oneChar)
}
}
}
return newStr
}
function getSubArrayNames()
{
#for print
#names = "authorId,classId,cpId,chapCount,statDate,orderAmount,orderCount,orderUserCount,orderChapCount,"
#names = names "readerCount,deepReaderCount,clickedUserCount,"
#names = names "ARPU,readerConversionRate,deepReaderConversionRate,readerOrderRate,browserOrderRate"
#for insert
names = "authorId,classId,cpId,statDate,orderAmount,orderCount,orderUserCount,"
names = names "readerCount,deepReaderCount,clickedUserCount,"
names = names "ARPU,readerConversionRate,deepReaderConversionRate,readerOrderRate,browserOrderRate"
return names
}
function makeDeleteSql(bookId)
{
sql = "delete from "bookStatTable" where bookid=" bookId " and stat_date=\"" statDate "\";"
print sql >> scriptFile
}
function makeInsertSql(bookId,books)
{
names = getSubArrayNames()
split(names,nameArray,",")
filedNames = ""
filedValues = ""
for(i in nameArray)
{
if(nameArray[i] == "ARPU")
{
nameArray[i] = "arpu"
}
filedNames = filedNames "" lowerStyle(nameArray[i]) ","
filedValues = filedValues "\"" books[bookId,nameArray[i]] "\","
}
filedNames = filedNames"bookid"
filedValues = filedValues""bookId
sql = "insert into " bookStatTable "(" filedNames ") values(" filedValues ");"
print sql >> scriptFile
}
function printBookStatInfo(bookId,books)
{
names = getSubArrayNames()
split(names,nameArray,",")
printf("\n\nbookId=%s,detail:\n",bookId) >> tempDataFile
for(i in nameArray)
{
printf("%s is %s\n",nameArray[i],books[bookId,nameArray[i]]) >> tempDataFile
}
}
### make books ###
BEGIN{
#load cpId
while(getline<cpBookFile == 1)
{
cpId=$1
bookId=$2
cpBook[bookId]=cpId
}
#load clicked stat
while(getline<clickedNumberFile == 1)
{
bookId=$1
clickedNumber=$2
clickedStatList[bookId]=clickedNumber
}
#define books
while(getline<bookNameFile == 1)
{
bookId = $1
bookName = $2
authorId = $3
classId = $4
chapCount = $5
#initialize basic info of this book
indexSet[bookId] = bookId
books[bookId,"authorId"] = authorId
books[bookId,"classId"] = classId
books[bookId,"chapCount"] = chapCount
books[bookId,"cpId"] = cpBook[bookId]
books[bookId,"statDate"] = statDate
#initialize browse info of this book
books[bookId,"readChapCount"] = 0
books[bookId,"readerCount"] = 0
books[bookId,"deepReaderCount"] = 0
books[bookId,"clickedUserCount"] = clickedStatList[bookId]
#initialize order info of this book
books[bookId,"orderAmount"] = 0.0
books[bookId,"orderUserCount"] = 0
books[bookId,"orderCount"] = 0
books[bookId,"orderChapCount"] = 0
#initialize ARPU and Rates
books[bookId,"ARPU"] = 0
books[bookId,"readerConversionRate"] = 0
books[bookId,"deepReaderConversionRate"] = 0
books[bookId,"readerOrderRate"] = 0
books[bookId,"browserOrderRate"] = 0
}
}
### compute OrderCost ###
{
userId = $1
bookId = $2
chapId = $3
price = $4
size = $5
recordDate = $6
recordTime = $7
chargeable = (int($8) == 0)
bookReadUser[bookId,userId]++
#element of array defalut is 0.
#so , the value is 1 bebind first increment
if(bookId in indexSet)
{
if(chargeable)
{
books[bookId,"orderAmount"] += price
}
if(bookReadUser[bookId,userId] == 1)
{
books[bookId,"orderUserCount"] += 1
}
if(chapId == 1)
{
books[bookId,"orderChapCount"] += book[bookId,"chapCount"]
}
else
{
books[bookId,"orderChapCount"] += 1
}
books[bookId,"orderCount"] += 1
}
}
### make SQL statement ###
END{
print "END..."
for (bookId in indexSet)
{
if (books[bookId,"orderCount"]>0)
{
#compute ARPU and Rates
books[bookId,"ARPU"] = computeARPU(bookId,books)
books[bookId,"readerConversionRate"] = readerConversionRate(bookId,books)
books[bookId,"deepReaderConversionRate"] = deepReaderConversionRate(bookId,books)
books[bookId,"readerOrderRate"] = readerOrderRate(bookId,books)
books[bookId,"browserOrderRate"] = browserOrderRate(bookId,books)
#make insert SQL statement
#printBookStatInfo(bookId,books)
makeDeleteSql(bookId)
makeInsertSql(bookId,books)
}
}
}
' $orderDetailFile
}
main()
{
cleanTemporaryFiles
loadDataToFiles
stat
executeSql
echo done
}
#call main function
main
浙公网安备 33010602011771号