#! /usr/bin env python
#coding:utf-8
import requests
import YDM
import re
from lxml import etree
import logging
import sys
import  MySQLdb
import  csv
reload(sys)
sys.setdefaultencoding('utf8')
class Oppo_spider:
    def __init__(self):
        self.username= 'myself'
        self.password = '1178874437a'
        self.appid = 4359
        self.appkey = '0715011dad66b60956de51723d5509c2'
        self.codetype = 1004
        self.timeout = 60
        self.s = requests.session()
        self.headers = {'User-Agent': 'Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/63.0.3239.132 Safari/537.36'}
        self.db =  MySQLdb .connect(host="101.200.33.202 ",port=3306,user="root", passwd="root", db="ladon", charset='utf8')
        self.cursor = self.db.cursor()
    def log_in(self):

        log_url = 'https://e.oppomobile.com/login'
        pic_url = 'https://e.oppomobile.com/loginCaptcha'
        identifying_code=self.s.get(pic_url,headers=self.headers)
        with open('code.jpg','wb')as f:
            f.write(identifying_code.content)
        filename = 'code.jpg'
        if (self.username == 'username'):
           print('請設(shè)置好相關(guān)參數(shù)再測試')
        else:
            # 初始化
            yundama = YDM.YDMHttp(self.username, self.password, self.appid, self.appkey)
            balance = yundama.balance()
            print('你剩余的賬戶積分為: %s' % balance)
            # 開始識別，圖片路徑，驗證碼類型ID，超時時間（秒），識別結(jié)果
            cid, result = yundama.decode(filename, self.codetype, self.timeout)
            code = result
            print("識別的驗證碼為:%s")%(code.encode("utf-8"))

            Form_data = {'name':'1000002573','passwd':'pairuioppo8','captcha':code}
            manage_page_response=self.s.post(log_url,data=Form_data,headers=self.headers).text
            if(manage_page_response.encode("utf-8").find("廣告主ID")==-1):
                print("sign in is failing , now restart sign in ,please hold on a few minutes")
                self.log_in()

            else:
                pattern = re.compile(r'<div class="account_area"><a href="/agency/passwd">.*?(\d+).*?(\d+).*?')
                ad_owner_id = pattern.search(manage_page_response).group(1).encode("utf-8")
                user_id = pattern.search(manage_page_response).group(2).encode("utf-8")
                if (ad_owner_id == "1000002573" and user_id == "800002573"):
                    print("sign in is sucessful 你的廣告主ID為:%s 用戶ID為:%s") % (ad_owner_id, user_id)
                    self.Promotion_manage(manage_page_response)
                    # self.Data_excel()
                    # self.Financial_manage()
                    # self.Coustomer_manage()




    def Promotion_manage(self,manage_page):

        manage_page_data=etree.HTML(manage_page).xpath('//tr[contains(@class,"tolist")]//td/text()')
        manage_page_ifrma=etree.HTML(manage_page).xpath('//tr[contains(@class,"tolist")]//td/a[contains(@href,"javascript:;")]/@onclick')


        '''
        csvfile = open('data.csv', 'wb')  # 打開方式還可以使用file對象
        writer = csv.writer(csvfile)
        writer.writerow(['客戶名稱', '客戶ID'])
        '''


        print("推廣管理數(shù)據(jù)輸出中:\r\n")
        for i in range(len(manage_page_data)):

            if (i%10 ==2):
               manage_page_data[i]= manage_page_data[i].strip()
               if(manage_page_data[i]=="審核通過"):
                   # print manage_page_data[i-1]
                   # sql1="insert into account(usable_status)values(%d) where NOT EXISTS (select media=4 , account_id_from_media=manage_page_data[i-1] from account) ;"%(2)
                   sql1 = "update account set(usable_status)values(%d);"%(2)

                   try:


                       self.cursor.execute(sql1)
                       # 提交到數(shù)據(jù)庫執(zhí)行


                       self.db.commit()
                   except:
                       # print (manage_page_data[i])
                       # 發(fā)生錯誤時回滾
                       self.db.rollback()
               else:

                   # sql2 = "insert into account(usable_status)values(%d) where  (select media=4 ,account_id_from_media=manage_page_data[i-1] from account ;"%(3)

                   sql2 = "update set account(usable_status)values(%d);"%(3)

                   try:
                       # 執(zhí)行sql語句
                       self.cursor.execute(sql2)
                       # 提交到數(shù)據(jù)庫執(zhí)行

                       self.db.commit()
                   except:
                       # 發(fā)生錯誤時回滾
                       self.db.rollback()



            if(i%10==3):
                manage_page_data[i] = manage_page_data[i].strip()
            # print (manage_page_data[i])
            if(i%10==9 ):
                print("\r\n")

                # writer.writerow([manage_page_data[i-9],manage_page_data[i-9+1]])
              
                sql3 = "update  account set (account_fullname, user,account_id_from_media, funds,agency_id,media_id)values(%s,%s,%s,%s,%d,%d)  where media_id=4 and account_id_from_media=manage_page_data[i - 9 + 1] ;" % (
                 manage_page_data[i - 9], manage_page_data[i - 9], manage_page_data[i - 9 + 1] ,
                manage_page_data[i - 9 + 4], 8, 4)
                print sql3

                try:
                # 執(zhí)行sql語句
                  self.cursor.execute(sql3)
                # 提交到數(shù)據(jù)庫執(zhí)行
                  self.db.commit()
                except:
                # 發(fā)生錯誤時回滾
                  self.db.rollback()

            # 關(guān)閉數(shù)據(jù)庫連接
        self.db.close()


            #    manage_page_data.insert(i+1,(manage_page_ifrma[i%9].split("('", 1)[1].split("')", 1)[0]))

        # for i in manage_page_data:
        #         print i
                # if (i % 11 == 10):
                #    print("\r\n")

            # if(i%10==4 and manage_page_data[i]>0):
            #     onclick_res_page=self.s.get('"https://e.oppomobile.com/"+manage_page_data[i+6]',headers=self.headers).text
            # for i in range(len(onclick_res_page)):
            #
            #     print onclick_res_page[i]
            #
            #     pass






            # sql = "insert into account (account_fullname, user,account_id_from_media,usable_status, funds,agency_id,media_id )values(manage_page_data[i],manage_page_data[i],manage_page_data[i+1],)"
            # try:
            #
            #
            #
            #     self.db.commit()
            # except:
            #     self.db.rollback()


    # def Data_excel(self):
    #
    #     data_excel_page=self.s.get("https://e.oppomobile.com/agency/rpt",headers=self.headers).text
    #     data_excel_data=etree.HTML(data_excel_page).xpath('//table[contains(@class,"mod_table")]/tbody/tr//td/text()')
    #     print("數(shù)據(jù)報表數(shù)據(jù)輸出中:\r\n")
    #     for i in range(len(data_excel_data)):
    #         print data_excel_data[i]
    #         sql="insert into account(user_name,password,comment)values()"
    #         try:
    #             self.cursor.execute(sql)
    #             self.db.commit()
    #         except:
    #             self.db.rollback()




    # def Financial_manage(self):
    #     financial_manage_page=self.s.get("https://e.oppomobile.com/agency/finance",headers=self.headers).text
    #     financial_manage_data=etree.HTML(financial_manage_page).xpath('//tr[contains(@class,"tolist")]//td/text()')
    #     print("財務(wù)管理數(shù)據(jù)輸出中:\r\n")
    #     for i in range(len(financial_manage_data)):
    #         print (financial_manage_data[i])
            # sql = "insert into account(optimizer_duty_name)values()"
            # try:
            #     self.cursor.execute(sql)
            #     self.db.commit()
            # except:
            #     self.db.rollback()



    # def Coustomer_manage(self):
    #     coustomer_manage_page=self.s.get("https://e.oppomobile.com/agency/customer/list",headers=self.headers).text
    #     coustomer_manage_data=etree.HTML(coustomer_manage_page).xpath('//tr[contains(@class,"tolist")]//td/text()')
    #     print("客戶管理數(shù)據(jù)輸出中:\r\n")
    #     for i in range(len(coustomer_manage_data)):
    #         print (coustomer_manage_data[i])
        #     sql="insert into account(media_duty_name)values()"
        #     try:
        #         self.cursor.execute(sql)
        #         self.db.commit()
        #     except:
        #         self.db.rollback()
        # self.db.close()

    # def create_table(self):
    #      try:
    #          create_tb_cmd = """ create table if not exists account \
    #                         (id int not null auto_increment,duty_name text not null,\
    #                         duty_phone int not null,duty_email varchar(20),not null, \
    #                         user_name varchar(10) not null,password varchar(100) not null,comment varchar(100) not null,\
    #                         optimizer_duty_name varchar(10) not null,
    #                         media_duty_name varchar(10) not null);"""
    #      except:
    #          print("create table have a error")

if __name__ =="__main__":
    oppo=Oppo_spider()
    oppo.log_in()