#encoding=utf8

#!/usr/bin/env python

import os

import sys

import urllib2

import MySQLdb

import simplejson

import subprocess

import sys

sys.path.append("/home/hadoop/hivelib")

from common import *

from util import Utils

from datetime import datetime,timedelta

from dbutil import DBUtils

import csv

class RptTracksCps(object):

    def __init__(self,today):

        self.today = today

        self.dayStr = today.strftime("%Y-%m-%d")

        self.paramDict = {"dayStr":self.dayStr}

        self.testFlag =False

    def run(self):

        self.deleteData4DB()

        self.DataDeal_source()

        self.DataDeal_base()

        self.selectSum4DB()

    def DataDeal_source(self):

        tmpFileName = "rpttrackscps_source_%s.log" % self.dayStr

        conn = DBUtils.getConnection(dbName="ktepdb")

        selectSql = """select detail_external,sum(passenger_number),sum(order_pay),date,external from source_conversion where date ='%(dayStr)s' and external='marketing' group by detail_external,date""" % self.paramDict

        rowList = DBUtils.selectSql(selectSql,conn)

        self.insertData2DB(rowList)

    def selectSum4DB(self):

        conn = DBUtils.getConnection(dbName="uniondb")

       

        selectSql = """select detail_external,sum(passenger_number),sum(order_pay),date,external,sum(pay_order) from rpttracks_cps where date ='%(dayStr)s' and external='marketing' group by detail_external,date""" % self.paramDict

        rowLists = DBUtils.selectSql(selectSql,conn)

        #self.insertData2DB(rowLists)

        insertSql ='insert into rpttracks_cps(detail_external,passenger_number,order_pay,date,external,pay_order) VALUES (%s,%s,%s,%s,%s,%s)'

        DBUtils.insertList(insertSql,rowLists,conn)

        sum=0

        for item in rowLists:

            sum+=1

            continue

        #print sum

        selectSql = """select detail_external,count(*) from rpttracks_cps where date ='%(dayStr)s'""" % self.paramDict

        rowList = DBUtils.selectSql(selectSql,conn)

        for line in rowList:

            deleteSql = "delete from `rpttracks_cps` where date='%s' and external='marketing' ORDER BY id LIMIT %s" % (self.paramDict["dayStr"],line[1]-sum)

            #print line[1]

            DBUtils.deleteSql(deleteSql,conn)

            break

        conn.close()

    def DataDeal_base(self):

        url = ""

        site_no={}

        sites=""

        urls = urllib2.urlopen(url)

        data = urls.read()

        datas = simplejson.loads(data)

        conn = DBUtils.getConnection(dbName="ktepdb")

        conns = DBUtils.getConnection(dbName="uniondb")

        #print datas

        for line in datas.keys():

            try:

                if line == "xxx":

                    continue

                tmpFileName = "rpttrackscps_base_%s.log" % self.dayStr

                sql = '''

                    select detail_external,sum(pay_order),order_sn,date,external from base_%s where date ='%s' and external='marketing' group by detail_external

                ''' % (line,self.paramDict["dayStr"])

                rowList = DBUtils.selectSql(sql,conn)

                insertSql ='insert into rpttracks_cps(detail_external,pay_order,order_sn,date,external) VALUES (%s,%s,%s,%s,%s)'

                DBUtils.insertList(insertSql,rowList,conns)

            except:

                pass

        conn.close()

        conns.close()

    def deleteData4DB(self):

        conn = DBUtils.getConnection(dbName="uniondb")

        deleteSql = "delete from `rpttracks_cps` where date='%(dayStr)s' and external='marketing'" % self.paramDict

        DBUtils.deleteSql(deleteSql,conn)

        conn.close()

    def insertData2DB(self,rowList):

        insertSql ='insert into rpttracks_cps(detail_external,passenger_number,order_pay,date,external) VALUES (%s,%s,%s,%s,%s)'

        conn = DBUtils.getConnection(dbName="uniondb")

        DBUtils.insertList(insertSql,rowList,conn)

        conn.close()

if __name__=='__main__':

    today = datetime.now() - timedelta(days=1)

    ompdata2db = RptTracksCps(today)

    ompdata2db.run()

    #homedir = os.getcwd()

    #print homedir