再次更新

#!/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