import dataset
import codecs
import sys
import codecs
import traceback
import requests
import re
import pandas as pd
import random
import urllib
import json
import gspread
import datetime
from gspread_pandas import Spread, Client
from oauth2client.service_account import ServiceAccountCredentials
import os

def save_sheet(df,filename,tabname,startpos='A1'):

    scope = ['https://spreadsheets.google.com/feeds',
            'https://www.googleapis.com/auth/drive']

#    credentials = ServiceAccountCredentials.from_json_keyfile_name('c:\\keys\\service\\gspread.json', scope)
    credentials = ServiceAccountCredentials.from_json_keyfile_name('c:\\keys\\spread2.json', scope)

    gc = gspread.authorize(credentials)
    spread = Spread(filename,creds=credentials)

    spread.df_to_sheet(df, index=False, sheet=tabname, start=startpos, replace=False)



db = dataset.connect('mysql://hhh7796hhh:lYmWsu^ujcA1@127.0.0.1:13306/crm?charset=utf8mb4')

contracts=[]

def any_contract(comp):
    global contracts
    for c in contracts:
        if comp in c:
            return True
    return False

#cursor=db.query("SELECT company,contract_time FROM xoops.execute_form where contract_time>'2020-01-01' and is_delete <> 'Y'")
cursor=db.query("SELECT distinct f.company,f.contract_time FROM xoops.execute_form f, xoops.execute_detail i  where f.exf_id=i.exf_id and f.contract_time>'2020-01-01' and f.is_delete <> 'Y'")

for c in cursor:
    contracts.append(c['company'])


df = pd.DataFrame(columns=('company','contact','title','telephone','worktype','sales','dt'))
idx=0
#cursor=db.query("SELECT company_s_temp, company_f_temp,sales,create_time FROM crm.customer_contact where create_time >='2020-01-01' and create_time <='2020-21-31'")

cursor=db.query("SELECT c.company_s_temp,c.company_f_temp,c.name,c.title,c.telete,m.work_type, m.sales, m.set_date FROM crm.customer_maintenance m, customer_contact c where ( (m.work_type='M') or (m.work_type='MTWR') ) and  c.cus_id=m.cus_id and m.set_date >='2020-01-01' and m.set_date <='2020-21-31'")

for c in cursor:
    if c['company_s_temp'] is None:
        continue
    if not any_contract(c['company_s_temp']):
        df.loc[idx]=[c['company_f_temp'],c['name'],c['title'],c['telete'],c['work_type'],c['sales'],c['set_date']]
        idx+=1

save_sheet(df,'sales_report','report')