version 2 of script for syncing data every 10 minutes

syncScript.py

'''
    from remote desktop connection
'''


from datetime import date, datetime ,timedelta
import sys
from time import sleep
from time import ctime

import logging
from requests import request
import mysql.connector
from mysql.connector import Error
import json
import re
from authentication import generateAccessTokens
from authentication import refreshTokens

from apiCall import pushInto
#
# pushInto(moduleName , payload):
# from apiCall import pushInto
#
global clientCounter, convCounter , leadsCounter ,invoiceInfoCounter , invoiceItemCounter
#global aDate , secondDate , counter


'''logging'''
now = datetime.now()

fileName = now.strftime('%Y_%m_%d_%H_%M_%S')
logging.basicConfig(filename="logs\\version_6_ALL_Modules_2015-07-02"+"_LOGS.log", level=logging.INFO, datefmt='%Y-%m-%d %H:%M:%S')
#aDate = datetime(2013,7,26,10,00)

'''

        operations start from here -  first oauth then read data - while time passses check if condition to refresh token is acheived
'''
logging.info("-- operations started at {} (test time = 2015-07-02 10:00) --".format(ctime()))

logging.info("-- not calling generateAccessToken because already called  (test time = 2015-07-02 10:00) --")
#oauthResponse = generateAccessTokens()
#logging.info("-- response recieved : \n {} \n\n -- at {}  --".format(oauthResponse.text,time.ctime()))
# /* testing with 2015 07 25*/



'''
    queries
'''
# queryClients = "select id,company_name,company_grade,phone_no,email,user_id,added_on FROM clients"
queryClients = "select * from clients"
queryConversation = "select s_n,with_email,user_id,added_on,msg,followup_on FROM conversation"
queryLeads = "select * FROM leads"
queryInvoiceInfo = "select * FROM invoice_info"
queryInvoiceItems = "select * FROM invoice_items"

# info has Id not id but it is item[index=0]
# queryInvoiceItems = "select * FROM invoice_items"
alpha = re.compile(r"[a-z|A-Z]|[!@#$%^&*/]")
counter = 0
# after every 30 minutes i.e. counter%3==0 run refreshToken.py to refresh the accessToken
clientCounter = 0
convCounter = 0
leadsCounter = 0
invoiceInfoCounter = 0
invoiceItemCounter = 0

''' date variables '''

# aDate = datetime(2015,1,3,11,00)
#aDate = datetime(2015,1,3,10,00)
#aDate = datetime(2013,7,26,10,00)
#"2014-09-23"
#aDate = datetime(2014,9,23,10,00)
#2015-06-23

#/* testing with 2015-07-02*/
aDate = datetime(2015,7,2,10,00)


#
# 2013-07-26
#
secondDate = aDate

# 480 minutes - till 19:00 so loop breaks at counter = 49

def updateTime():
    ''' timer '''
    global aDate , secondDate , counter

    counter+=1
    # d1 = aDate.strftime('%Y-%m-%d %H:%M:%S')
    # d2 = secondDate.strftime('%Y-%m-%d %H:%M:%S')
    # print("before update aDate = {} , secondDate = {}".format(d1,d2))
    print('\n not waiting for 10 seconds\n')
#    time.sleep(60) # 60 seconds now -  for 10 minutes replace 60 with 60*10
#    await for 5 seconds - test
    for i in range(10):
        print('remaining seconds :  {}'.format(10-i))
        sleep(1)
    diff = secondDate + timedelta(minutes=10) 
    aDate = secondDate
    secondDate = diff
    # print("now after update : aDate = {} , secondDate = {}".format( aDate.strftime('%Y-%m-%d %H:%M:%S') , secondDate.strftime('%Y-%m-%d %H:%M:%S')))
    return aDate , secondDate

''' refresh token every 40 minutes '''

'''
        2015-07-02

        script runs from 12:00 hrs to 20:00 hrs
        12 pm to 8pm
'''
while aDate < datetime(2015,7,2,20,00):
    # counter is incremented in updateTime function
    print('--in the loop--')
    print('waiting for 10 minutes')
    logging.info('--------------------- 10 minute wait ----------------------------')
#    time.sleep(2) # 2 seconds now
#     waiting for 1minute -------- test -------------
    timestamp1 , timestamp2 = updateTime()
    timeCond = " WHERE added_on between '{}' and '{}'".format(timestamp1, timestamp2)
    '''
        wait complete timestamps updated now check for refresh
    '''
    if counter%4==0:
        print("minutes passed = ",10*counter,'(counter = ',counter,')')
        logging.info('minutes passed = {} , counter = {} '.format(10*counter , counter))
        
        logging.info(" - - - - refreshing tokens at {} - - - - - ".format(ctime()))
#        refreshResponse = refreshTokens()
#        logging.info("-- response recieved : \n {}\n\n -- at {}  --".format(refreshResponse.text,ctime()))

    print('condition no {} : {} '.format(counter,timeCond))
    logging.info('-> ->condition no {} : {} '.format(counter,timeCond))
    # everytime a new query is generated connect to db and perform operations
    # since keeping a connection alive is not good enough
    # not possible to keep alive the mysql connection
    with mysql.connector.connect() as connectPointer:
        print('-> connection established at timestamp = {}',timestamp2)
        logging.info(' --connection established at \'{}\' timestamp  : {} --'.format(ctime(),timestamp2))
        # clients
        with connectPointer.cursor() as aCursor:
            print('1. fetching clients')
            logging.info('1. fetching clients')
            aCursor.execute(queryClients+timeCond)
            result = aCursor.fetchall()
            if not result:
                print('--> no clients created in the last 10 minutes <--')
                logging.info('__________  no clients created in the last 10 minutes  __________')

            else:
                clientCounter+=1
                # create clients payload ------------------  id,company_name,company_grade,phone_no,email,user_id,added_on
                client_collection_list = []
                for rows in result:
                    client_collection = {}
                    # convert it to string otherwise it raises error
                    local_id = rows[0]
                    cp1 = rows[1]
                    cp2 = rows[2]
                    name = rows[3] # company name - Name - since not null guaranteed entry
                    company_grade = rows[4]
                    phoneNo = str(rows[5]) # error in db remove if not a valid number
                    mn_1 = rows[6]
                    mn_2 = rows[7]
                    email = rows[8]
                    designation = rows[9]
                    address = str(rows[10]) # remove special chars from it - results in unvalidated json
                    website = rows[11]
                    user_id = rows[12]
                    added_on = str(rows[13])
                    last_conv = rows[14]
                    followup_on = str(rows[15])
                    last_mail_no = rows[16]
                    last_mail_on =str(rows[17])
                    last_sms_no = rows[18]
                    last_sms_on = str(rows[19])
                    country = rows[21]
                    if not bool(re.search(alpha,phoneNo)):
                        client_collection['phone_no'] = phoneNo
                        # print("{ 'phone_no' : ",phoneNo,"}")
                    else:
                        client_collection['phone_no'] = None
                        # print("{ 'phone_no' : ","'null'","}")
                    client_collection['Name'] = name
                    client_collection['company_name'] = name
                    client_collection['user_id'] = user_id
                    client_collection['local_id'] = local_id
                    client_collection['added_on'] = added_on.replace(' ','T') + '+05:30'
                    if len(followup_on)<5:
                        client_collection['followup_on'] = None
                    else:
                        client_collection['followup_on'] = followup_on.replace(' ','T') + '+05:30'
                    if len(last_mail_on)<5:
                        client_collection['last_mail_on'] = None
                    else:
                        client_collection['last_mail_on'] = last_mail_on.replace(' ','T') + '+05:30'

                    if len(last_sms_on)<5:
                        client_collection['last_sms_on'] = None
                    else:
                        client_collection['last_sms_on'] = last_sms_on.replace(' ' ,'T') + '+05:30'

                    client_collection['company_grade'] = company_grade
                    client_collection['website'] = website
                    client_collection['last_conversation'] = last_conv
                    client_collection['country'] = country
                    client_collection['address'] =  re.sub('[^A-Za-z0-9]' , ' ',address)
                    client_collection['mobile_no1'] = mn_1
                    client_collection['mobile_no2'] = mn_2
                    client_collection['contact_person1'] = cp1
                    client_collection['contact_person2'] = cp2
                    

                    
                    del name
                    del user_id
                    del local_id
                    del added_on
                    del company_grade
                    del phoneNo
                    del mn_1
                    del mn_2
                    del address
                    del website
                    del followup_on
                    del last_mail_no
                    del last_mail_on
                    del last_sms_on
                    del last_sms_no
                    del cp1
                    del cp2
                    
                    # print(json.dumps(client_collection))
                    client_collection_list.append(client_collection)
                    
                fileName = 'payloadJsons\\clients\\clients_'+str(clientCounter)+'_temp.json'
                # payloadJsons\\clients_1_temp.json - read this payload to push
                with open(fileName,'w',encoding='utf-8') as clientJson:
                    clientJson.write(json.dumps(
                        {"data":client_collection_list},indent=4,default=str,sort_keys=True
                        ))
                
                logging.info("\n\n{} clients crated \n\n".format(len(client_collection_list)))
#                responseClient = pushInto('clients',json.dumps(
#                    {"data":client_collection_list},indent=4,default=str,sort_keys=True
#                    ))
#                print('response to  clients api call : \n\n ',responseClient.text)
#                responseText = ''
#                responseText = "\n -- reponse recieved at {} -- \n {} ".format(ctime(),responseClient.text)
#                logging.info(responseText)
#                logging.info('____________________________________________')

    print('connection closed now')
    logging.info("------------------ connection closed at {} -----------------------".format(ctime()))
    print('\n\n\n ||| is the connection open : {} |||\n'.format(connectPointer.is_connected()))

    # ----------------------------- conversation ------------------------------------------------------------------
    
    with mysql.connector.connect() as connectPointer:
        print('-> connection established at timestamp = {}',timestamp2)
        logging.info(' --connection established at \'{}\' timestamp  : {} --'.format(ctime(),timestamp2))
        # conversations
        with connectPointer.cursor() as aCursor:
            print('2. fetching conversations')
            logging.info('2. fetching conversations')
            aCursor.execute(queryConversation+timeCond)
            result = aCursor.fetchall()
            if not result:
                print('--> no conversations created in the last 10 minutes <--')
                logging.info('__________  no conversations created in the last 10 minutes  __________')

            else:
                convCounter+=1
                conv_Collection_List = []
                # create payloads for conversations -------------------- s_n,with_email,user_id,added_on,msg,followup_on
                for rows in result:
                    convCollection = {}
                    name = str(rows[0])
                    s_n = rows[0]
                    with_email = rows[1]
                    user_id = rows[2]
                    added_on = str(rows[3])
                    msg = rows[4]
                    followup_on = str(rows[-1])
                    
                    convCollection['Name'] = name
                    convCollection['added_on'] = added_on.replace(' ','T') + '+05:30'
                    convCollection['s_n'] = s_n
                    convCollection['msg'] = msg
                    convCollection['user_id'] = user_id
                    convCollection['with_email'] = with_email
                    convCollection['followup_on'] = followup_on.replace(' ','T') + '+05:30'
                    conv_Collection_List.append(convCollection)
                    
                    del name
                    del s_n
                    del with_email
                    del user_id
                    del added_on
                    del msg
                    del followup_on
                    
                fileName = 'payloadJsons\\conversation\\conversation_'+str(convCounter)+'_temp.json'

                with open(fileName,'w',encoding='utf-8') as clientJson:
                    clientJson.write(json.dumps(
                        {"data":conv_Collection_List},indent=4,default=str,sort_keys=True
                        ))

                logging.info('____________________________________________')
                logging.info("| caling api to push conversations |")
                responseConversation = pushInto('conversation',json.dumps(
                        {"data":conv_Collection_List},indent=4,default=str,sort_keys=True
                        ))
                    # print('-'*50)
                # logging.info("\n\n\n>>>calling generic module api {}\n\n\n".format(time.ctime()))
                # responseReturned = pushIntoModule(module_name=modulesList[index],payload=leadsJson)
#                responseText = ''
#                responseText = "\n -- reponse recieved at {} -- \n {} ".format(ctime(),responseConversation.text)
#                logging.info(responseText)
#                logging.info('____________________________________________')
                

                logging.info("\n\n{} conversations inserted \n\n".format(len(conv_Collection_List)))
                print('inserted conversations in ',fileName)
            

    print('connection closed now')
    logging.info("------------------ connection closed at {} -----------------------".format(ctime()))
    print('\n\n\n ||| is the connection open : {} |||\n'.format(connectPointer.is_connected()))

    # ------------------------------------------------- leads --------------------------------------------------------
    
    with mysql.connector.connect() as connectPointer:
        print('-> connection established at timestamp = {}',timestamp2)
        logging.info(' --connection established at \'{}\' timestamp  : {} --'.format(ctime(),timestamp2))
        # leads
        with connectPointer.cursor() as aCursor:
            print('3. fetching leads')
            logging.info('3. fetching leads')
            aCursor.execute(queryLeads+timeCond)
            result = aCursor.fetchall()
            if not result:
                print('--> no leads created in the last 10 minutes <--')
                logging.info('__________  no leads created in the last 10 minutes  __________')

            else:
                leadsCounter+=1
                # payloads for leads ---------------------------------
                # id,client_id,lead_source,importance,added_on,user_id,requirement,status,invoice_id
                leadsCollList = []
                for rows in result:
                    leadsColl = {}
                    local_id = rows[0]
                    name = str(local_id)
                    client_id = rows[1]
                    lead_source = rows[2]
                    importance = rows[3]
                    added_on = str(rows[4])
                    user_id = rows[5]
                    requirement = rows[6]
                    report_type = rows[7]
                    time_from = rows[8] # check for null
                    time_to = rows[9] # check for null
                    open_price = rows[10]
                    close_price = rows[11]
                    status = rows[12]
                    causes = rows[13]
                    invoice_id = rows[-2]
                    temp = rows[-1]
                    
                    leadsColl['Name'] = name + ' - ' +requirement
                    leadsColl['local_id'] = local_id
                    leadsColl['client_id'] = client_id
                    leadsColl['Lead_Source'] = lead_source
                    leadsColl['source'] = lead_source

                    leadsColl['importance'] = importance
                    leadsColl['added_on'] = added_on.replace(' ','T') + '+05:30'
                    leadsColl['requirement'] = requirement
                    leadsColl['status'] = status
                    leadsColl['invoice_id'] = invoice_id
                    leadsColl['Last_Name'] = "(lead)" +  ' - ' +requirement
                    leadsColl['user_id'] = user_id
                    
                    if time_from == None:
                        leadsColl['time_from'] = None
                    else:
                        leadsColl['time_from'] = str(time_from)
                    if time_to == None:
                        leadsColl['time_to'] = None
                    else:
                        leadsColl['time_to'] = str(time_from)      
                                       
                    leadsColl['opening_price'] = open_price
                    leadsColl['closing_price'] = close_price                    
                    leadsColl['report_type'] = report_type
                    leadsColl['temp'] = temp 
                    

                    leadsCollList.append(leadsColl)
                    
                    del name
                    del local_id
                    del client_id
                    del lead_source
                    del importance
                    del added_on
                    del user_id
                    del requirement
                    del status
                    del time_from
                    del time_to
                    del open_price
                    del close_price
                    del temp
                    del report_type
                    del invoice_id
                    
                print("{} leads created".format(len(leadsCollList)))


                fileName = 'payloadJsons\\leads\\leads'+str(leadsCounter)+'_temp.json'

                with open(fileName,'w',encoding='utf-8') as clientJson:
                    clientJson.write(json.dumps(
                        {"data":leadsCollList},indent=4,default=str,sort_keys=True
                        ))
            
#                logging.info('____________________________________________')
#                logging.info("| caling api to push leads |")
#                responseLeads = pushInto('Leads',json.dumps(
#                        {"data":leadsCollList},indent=4,default=str,sort_keys=True
#                        ))
#                 print('-'*50)
#                responseText = ''
#                responseText = "\n -- reponse recieved at {} -- \n {} ".format(ctime(),responseLeads.text)
#                logging.info(responseText)
#                logging.info('____________________________________________')
#            
#                
                logging.info("\n\n{} leads inserted \n\n".format(len(leadsCollList)))
                print('inserted leads in ',fileName)

    print('connection closed now')
    logging.info("------------------ connection closed at {} -----------------------".format(ctime()))
    print('\n\n\n ||| is the connection open : {} |||\n',connectPointer.is_connected())

    # ------------------------------------------------------ invoice info ------------------------------------------------------------------
    
    with mysql.connector.connect() as connectPointer:
        print('-> connection established at timestamp = {}',timestamp2)
        logging.info(' --connection established at \'{}\' timestamp  : {} --'.format(ctime(),timestamp2))        
        # invoice info
        with connectPointer.cursor() as aCursor:
            print('4. fetching invoice info')
            logging.info('4. fetching invoice info')

            aCursor.execute(queryInvoiceInfo+timeCond)
            result = aCursor.fetchall()
            if not result:
                print('--> no invoice info created in the last 10 minutes <--')
                logging.info('__________  no invoice info created in the last 10 minutes  __________')
            else:
                invoiceInfoCounter+=1
                # payloads for invoice info ---------------------------------
                # Id,invoice_no,invoice_of,user_id,added_on,Email,payment_in,sale_rule,sale_amount 
                invoiceInfoCollList = []
                for rows in result:
                    invInfoColl = {}
                    invInfoColl['local_id'] = rows[0]
                    invInfoColl['Name'] = str(rows[0])
                    invInfoColl['invoice_no'] = rows[1]
                    invInfoColl['invoice_of'] = rows[2]
                    invInfoColl['Customer_Name'] = rows[3]
                    invInfoColl['Company_Name'] = rows[4]
                    invInfoColl['Customer_Designation'] = rows[5]
                    added_on = str(rows[14])
                    invInfoColl['added_on'] = added_on.replace(' ','T') + '+05:30'
                    invInfoColl['Email'] = rows[12]
                    invInfoColl['payment_in'] = rows[17]
                    invInfoColl['sale_rule'] = rows[23]
                    invInfoColl['sale_amount'] = rows[24]
                    invInfoColl['user_id'] = rows[13]
                    payment_on = rows[16]
                    if payment_on == None:
                        invInfoColl['payment_on'] = None
                    else:
                        invInfoColl['payment_on'] = (str(payment_on) + 'T00:00:00') + '+05:30'
                    invInfoColl['payment_detail'] = rows[15]
                    invInfoColl['Address'] = rows[6]
                    invInfoColl['payment_in'] = rows[17]
                    invInfoColl['data_detail'] = rows[18]
                    invInfoColl['tds_percentage'] = rows[22]
                    invInfoColl['sale_rule'] = rows[23]
                    invInfoColl['sale_amount'] = rows[24]
                    invInfoColl['amount_recieved'] = rows[19]
                    data_sent_on = str(rows[20])
                    if len (data_sent_on) < 5:
                        invInfoColl['data_sent_on'] = None
                    else:
                        invInfoColl['data_sent_on'] = data_sent_on.replace(' ','T') + '+05:30'
                    invoiceInfoCollList.append(invInfoColl)
                    
                    del added_on
                print("{} invoice info created".format(len(invoiceInfoCollList)))
                # ----------------------------------------------------------------  

                fileName = 'payloadJsons\\invoiceInfo\\invoiceInfo'+str(invoiceInfoCounter)+'_temp.json'

                with open(fileName,'w',encoding='utf-8') as clientJson:
                    clientJson.write(json.dumps(
                        {"data":invoiceInfoCollList},indent=4,default=str,sort_keys=True
                        ))

#                logging.info('____________________________________________')
#                logging.info("| caling api to push invoice info |")
#                responseInvoiceInfo = pushInto('invoice_info',json.dumps(
#                        {"data":invoiceInfoCollList},indent=4,default=str,sort_keys=True
#                        ))
#                responseText = ''
#                responseText = "\n -- reponse recieved at {} -- \n {} ".format(ctime(),responseInvoiceInfo.text)
#                logging.info(responseText)
#                logging.info('____________________________________________')                

                logging.info("\n\n{} invoice info inserted \n\n".format(len(invoiceInfoCollList)))
                print('inserted invoice info in ',fileName)
    print('connection closed now')
    print('\n\n\n ||| is the connection open : {} |||\n'.format(connectPointer.is_connected()))
    logging.info("------------------ connection closed at {} -----------------------".format(ctime()))
  
    # ------------------------------------------------------ invoice items ------------------------------------------------------------------
    
    with mysql.connector.connect() as connectPointer:
        print('-> connection established at timestamp = {}',timestamp2)
        logging.info(' --connection established at \'{}\' timestamp  : {} --'.format(ctime(),timestamp2))        
        # invoice info
        with connectPointer.cursor() as aCursor:
            print('4. fetching invoice items')
            logging.info('4. fetching invoice items')

            aCursor.execute(queryInvoiceItems+timeCond)
            result = aCursor.fetchall()
            if not result:
                print('--> no invoice items created in the last 10 minutes <--')
                logging.info('__________  no invoice items created in the last 10 minutes  __________')
            else:
                invoiceItemCounter+=1
                # payloads for invoice info ---------------------------------
                # Id,invoice_no,invoice_of,user_id,added_on,Email,payment_in,sale_rule,sale_amount 
                invoiceItemCollList = []
                for rows in result:
                    invItemColl = {}
                    invItemColl['local_id'] = rows[0]
                    invItemColl['Name'] = str(rows[0])
                    invItemColl['invoice_id'] = rows[1]
                    invItemColl['Data_Type'] = rows[2]
                    invItemColl['Query'] = rows[3]
                    added_on = str(rows[11])
                    invItemColl['added_on'] = added_on.replace(' ','T') + '+05:30'
                    invItemColl['Total_Months'] = rows[6]
                    invItemColl['Amount'] = rows[7]
                    invItemColl['Mode_Of_Delivery'] = rows[8]
                    invItemColl['status'] = rows[10]
                    invItemColl['Date_From'] = rows[4]
                    invItemColl['Date_To'] = rows[5]
                    invItemColl['user_id'] = rows[12]
                    invItemColl['Country'] = rows[16]
                    invItemColl['payment_status'] = rows[13]
                    invItemColl['data_status'] = rows[14]
                    invoiceItemCollList.append(invItemColl)
                    
                    del added_on
                print("{} invoice item created".format(len(invoiceItemCollList)))
                # ----------------------------------------------------------------  

                fileName = 'payloadJsons\\invoiceItems\\invoiceItems'+str(invoiceItemCounter)+'_temp.json'

                with open(fileName,'w',encoding='utf-8') as clientJson:
                    clientJson.write(json.dumps(
                        {"data":invoiceItemCollList},indent=4,default=str,sort_keys=True
                        ))

#                logging.info('____________________________________________')
#                logging.info("| caling api to push invoice info |")
#                responseInvoiceInfo = pushInto('invoice_info',json.dumps(
#                        {"data":invoiceItemCollList},indent=4,default=str,sort_keys=True
#                        ))
#                responseText = ''
#                responseText = "\n -- reponse recieved at {} -- \n {} ".format(ctime(),responseInvoiceInfo.text)
#                logging.info(responseText)
                logging.info('____________________________________________')                
                logging.info("\n\n{} invoice items inserted \n\n".format(len(invoiceItemCollList)))
                print('inserted invoice item in ',fileName)
    print('connection closed now')
    print('\n\n\n ||| is the connection open : {} |||\n'.format(connectPointer.is_connected()))
    logging.info("------------------ connection closed at {} -----------------------".format(ctime()))

        

print('total\n clients {} ,\n conversations {} ,\n leads {},\n invoice info {}'.format(clientCounter,convCounter,leadsCounter,invoiceInfoCounter))
logging.info("-- operations closed at {} (test time = 2015 Jan 3 10:00) --".format(ctime()))
logging.info('\ntotal:\n clients {} ,\n conversations {} ,\n leads {},\n invoice info {}'.format(clientCounter,convCounter,leadsCounter,invoiceInfoCounter))