Ticket #190: list2.groovy

/** µŒ³ö°æÖ÷¹€×÷Á¿Í³ŒÆ */
import groovy.sql.*
import java.io.*

def dburl = "jdbc:mysql://192.168.74.5:3310/bbs7_lady_app?useUnicode=true&characterEncoding=GBK"
def username = "bbs7_lady_app"
def password = "bbs7_lady_app"
def db_driver = "com.mysql.jdbc.Driver"

//def dburl = "jdbc:mysql://192.168.237.215:3308/bbs7_lady_app?useUnicode=true&characterEncoding=GBK"
//def username = "bbs7_lady_app"
//def password = "lady7bbsmysql"
//def db_driver = "com.mysql.jdbc.Driver"

def pg_dburl = 'jdbc:postgresql://192.168.74.10:5432/bbs7_lady_app'
def pg_username = "bbs7_lady_app"
def pg_password = "bbs7_lady_app"
def pg_db_driver = "org.postgresql.Driver"

//def pg_dburl = 'jdbc:postgresql://192.168.239.230:5432/bbs7_lady_app'
//def pg_username = "bbs7_lady_app"
//def pg_password = "LadYbbs#X38v#"
//def pg_db_driver = "org.postgresql.Driver"

def db = Sql.newInstance(dburl, username, password, db_driver)

def pg_db = Sql.newInstance(pg_dburl, pg_username, pg_password, pg_db_driver)

def uid_username = [:]

//²é³öËùÓаæÖ÷
def uids = []

//°æÖ÷¶ÔÓŠµÄ°æ¿é
def uid_fid = [:]

db.eachRow("""
        SELECT a.uid uid, a.fid fid, b.name name, a.roleid roleid, c.name rolename
                 FROM bbs7_user_forum_role a, bbs7_forum b, bbs7_role c 
                WHERE (a.roleid=7 or a.roleid=8 or a.roleid=9) AND a.fid=b.fid AND a.roleid=c.id
 """) { row ->
                if (uid_fid[row.uid+""] == null){
                        uid_fid[row.uid+""]     = [row.fid]
                }else{
                        uid_fid[row.uid+""]     << row.fid
                }
                
                if (!uids.contains(row.uid)){
                        uids << row.uid 
                }
}


//²é³öËùÓз¢Ìû
def uid_countpost = [:]

def count_topic= 10
        
count_topic.times { seq ->
        def suffix = ""
        if(seq != 0){
                suffix = "_" + seq      
        }
        def sql = """ SELECT a.operatorid uid , c.fid , COUNT(1) as total 
                                  FROM bbs7_log_topic_201311 a, 
                                bbs7_topic${suffix} b,
                                (SELECT a.uid uid, a.fid fid, b.name NAME, a.roleid roleid, c.name rolename 
                                 FROM bbs7_user_forum_role a, bbs7_forum b, bbs7_role c 
                        WHERE (a.roleid=7 OR a.roleid=8 OR a.roleid=9) AND a.fid=b.fid AND a.roleid=c.id ) AS  c 
                        WHERE (ACTION= 'replyTopic' OR ACTION = 'postTopic' ) 
                        AND a.tid = b.tid 
                        AND b.fid = c.fid 
                        AND a.operatorid = c.uid 
                        GROUP BY a.operatorid 
                        """ as String

        db.eachRow(sql){ row ->

                if(uid_countpost[row.uid+""] == null){
                        uid_countpost[row.uid+""] = row.total
                }else{
                        uid_countpost[row.uid+""] += row.total
                }
        }
}
                

def opeartor = [
        "topTopic":"Ö÷ÌâÖÃÏî",
        "setPick":"ÉèÖ÷Ì⟫»ª",
        "deleteTopic":"ÉŸ³ýÖ÷Ìâ",
        "deletePost":"ÉŸ³ý»ØÌû",
        "move":"ÒÆÌû",
        "upTopic":"ÌáÉýÖ÷Ìâ",
        "downTopic":"ϳÁÖ÷Ìâ",
        "highlight":"ÉèÖÃÖ÷ÌâžßÁÁ",
        "unLockTopic":"œâËøÓû§",
        "copyTopic":"¿œ±ŽÖ÷Ìâ",
        "scoreGrade":"ÆÀ·Ö",
        "handleReport":"ŽŠÀíŸÙ±š",
        "lockUser":"ËøÓû§"
]

def start = Date.parse("yyyy-MM-dd","2013-11-01")
def end = Date.parse("yyyy-MM-dd HH:mm:ss","2013-11-30 23:59:59")

//def start = "2013-11-01"
//def end = "2013-12-01"

def uid_action = [:]

def query(def db, def sql, def uid, def uid_action){
        db.eachRow(sql) { row ->
                if (uid_action[uid] != null){
                        if (uid_action[uid][row.action] != null){
                                uid_action[uid][row.action] = uid_action[uid][row.action] + 1
                        }else{
                                uid_action[uid][row.action] = 1
                        }
                }else{
                        uid_action[uid] = [:]
                }
        }               
}

uid_onlinetime = [:]
//ͳŒÆÔÚÏßʱ³€
uids.each {uid ->
        db.eachRow("SELECT uid,SUM(onlineminutes) onlineminutes FROM bbs7_online_time_201311 WHERE uid = ${uid}"){ row ->
                uid_onlinetime[row.uid+""] = row.onlineminutes == null ? 0 : row.onlineminutes
        }
}

//ͳŒÆÌû×Ó²Ù×÷ÈÕÖŸ
uids.each { uid ->

        def sql = """
                select topiclogid, logid, operatorid, operatorip, tid, pid, action, 
                        reason, createat, summary1, summary2, summary3 from bbs7_log_topic_201311 where 1=1
                        and operatorid = ${uid} and createat >= ${start} and createat <= ${end} 
                        order by createat desc
        """ 
        query(db, sql, uid, uid_action)

}

//ͳŒÆÓû§Ïà¹Ø²Ù×÷ÈÕÖŸ
uids.each { uid ->
        def sql = """
                select userlogid, logid, operatorid, operatorip, uid, action, 
                        reason, createat, summary1, summary2, summary3 from bbs7_log_user_201311 where 1=1
                        and operatorid = ${uid} and createat >= ${start} and createat <= ${end} 
                        order by createat desc
        """ 
        query(db, sql, uid, uid_action)

}

//ͳŒÆ»ý·ÖÏà¹Ø²Ù×÷ÈÕÖŸ
uids.each { uid ->
        def sql = """
                select scorelogid, logid, operatorid, operatorip, uid, action, 
                        reason, createat, summary1, summary2, summary3 from bbs7_log_score_201311 where 1=1
                        and operatorid = ${uid} and createat >= ${start} and createat <= ${end} 
                        order by createat desc
        """ 
        query(db, sql, uid, uid_action)

}

//»ñÈ¡Óû§Ãû
uids.each {uid ->
        pg_db.eachRow("select uid, username from bbs7_user where uid = ${uid}"){ row ->
                uid_username[row.uid +""] = row.username
        }
}

def f = new File("working2.csv")

if (f.exists()){
        f.delete()
}

f.append("uid")
f.append(",")

f.append("̞")
f.append(",")

f.append("ÔÚÏßʱŒä")
f.append(",")

f.append("°æÄÚ·¢ÌûÊý")
f.append(",")

opeartor.each { k, v ->
        f.append(v)
        f.append(",")
}
f.append("\n")

class Report{
        def uid
        def username
        int onlineminutes
        int countpost
        int topTopic
        int setPick
        int deleteTopic
        int deletePost
        int move
        int upTopic
        int downTopic
        int highlight
        int unLockTopic
        int copyTopic
        int scoreGrade
        int handleReport
        int lockUser
}

def reports = []

uid_action.each{ uid, action ->

        def report = new Report()
        report.uid = uid
        report.username = uid_username[uid+""]
        report.onlineminutes = uid_onlinetime[uid+""] == null ? 0 :  uid_onlinetime[uid+""]
        report.countpost = uid_countpost[uid+""] == null ? 0 : uid_countpost[uid+""]
        report.setPick = action["setPick"] == null ? 0 : action["setPick"]
        report.deleteTopic = action["deleteTopic"] == null ? 0 : action["deleteTopic"]
        report.deletePost = action["deletePost"] == null ? 0 : action["deletePost"]
        report.move = action["move"] == null ? 0 : action["move"]
        report.upTopic = action["upTopic"] == null ? 0 : action["upTopic"]
        report.downTopic = action["downTopic"] == null ? 0 : action["downTopic"]
        report.highlight = action["highlight"] == null ? 0 : action["highlight"]
        report.unLockTopic = action["unLockTopic"] == null ? 0 : action["unLockTopic"]
        report.copyTopic = action["copyTopic"] == null ? 0 : action["copyTopic"]
        report.scoreGrade = action["scoreGrade"] == null ? 0 : action["scoreGrade"]
        report.handleReport = action["handleReport"] == null ? 0 : action["handleReport"]
        report.lockUser = action["lockUser"] == null ? 0 : action["lockUser"]
        reports << report
}

reports.each { 
        f.append(it.uid)        
        f.append(",")
        f.append(it.username)   
        f.append(",")
        f.append(it.onlineminutes)      
        f.append(",")
        f.append(it.countpost)  
        f.append(",")
        f.append(it.topTopic)
        f.append(",")
        f.append(it.setPick)
        f.append(",")
        f.append(it.deleteTopic)
        f.append(",")
        f.append(it.deletePost)
        f.append(",")
        f.append(it.move)
        f.append(",")
        f.append(it.upTopic)
        f.append(",")
        f.append(it.downTopic)
        f.append(",")
        f.append(it.highlight)
        f.append(",")
        f.append(it.unLockTopic)
        f.append(",")
        f.append(it.copyTopic)
        f.append(",")
        f.append(it.scoreGrade)
        f.append(",")
        f.append(it.handleReport)
        f.append(",")
        f.append(it.lockUser)
        f.append("\n")
}