#!/bin/sh################################################ ## author:lishujun ## date:2013-4-11 ## use:$orderdetail 2013-4-11 ## ################################################### load Library ###. /www/log/stat/db_alias. /www/log/stat/public_funcinit_date $1init_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 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 functionmain