scraper

from bs4 import BeautifulSoup
from selenium import webdriver
import lxml.html
import time
import mysql.connector

mydb = mysql.connector.connect(
    host = "localhost",
    user = "root",
    password = "",
    database = "lowesdb"
)

mycursor = mydb.cursor()

def make_soup():
    response = driver.execute_script('return document.documentElement.outerHTML')
    soup = BeautifulSoup(response, 'html.parser')
    return soup


USERNAME = 'Perisicdoors'
PASSWORD = 'Lowesperisic2015'

driver = webdriver.Firefox()
driver.get('https://scs-lowes.microsoftcrmportals.com/Payments/')

usr_field = driver.find_element_by_id('Username')
usr_field.send_keys(USERNAME)

pwd_field = driver.find_element_by_id('Password')
pwd_field.send_keys(PASSWORD)

singin_btn = driver.find_element_by_id('submit-signin-local')
singin_btn.submit()

ok = 0
next_page = 2
while (1<2):
    time.sleep(12)
    soup = make_soup()
    for link in soup.findAll('a', {'class': 'details-link has-tooltip'}):
        href = 'https://scs-lowes.microsoftcrmportals.com' + link.get('href')
        driver.get(href)
        query = "SELECT ID FROM paymentinfo WHERE link=%s"
        mycursor.execute(query, (href, ))
        res = str(mycursor.fetchone())
        if (res != 'None'):
            ok = 1
            break
        parsed_page = lxml.html.fromstring(driver.page_source)
        doc = parsed_page.xpath('//input[contains(@id, "scs_paymentinvoicenumber")]') #payment num
        payment_num = doc[0].get('value')
        doc = parsed_page.xpath('//input[contains(@id, "scs_vendor_name")]') # instaler
        instaler = doc[0].get('value')
        doc = parsed_page.xpath('//input[contains(@id, "scs_paymentinitiator_name")]') # initiator
        initiator = doc[0].get('value')
        doc = parsed_page.xpath('//input[contains(@id, "scs_invoicenumber")]')  # invoice num
        invoice_num = doc[0].get('value')
        doc = parsed_page.xpath('//input[contains(@id, "scs_paymentamount")]')  # req_amount
        req_amount = doc[0].get('value')
        doc = parsed_page.xpath('//input[contains(@id, "scs_purchaseorder_name")]')  # pur_order
        pur_order = doc[0].get('value')
        doc = parsed_page.xpath('//input[contains(@id, "scs_paymentreason")]')  # description
        description = doc[0].get('value')
        doc = parsed_page.xpath('//input[contains(@id, "scs_paymentinitiateddate")]') #date
        date = doc[0].get('value')
        doc = parsed_page.xpath('//input[contains(@id, "scs_laborcategory_name")]')  # labor_ctg
        labor_ctg = doc[0].get('value')
        doc = parsed_page.xpath('//input[contains(@id, "scs_storeid_name")]')  # store
        store = doc[0].get('value')
        doc = parsed_page.xpath('//input[contains(@id, "scs_paymenttype")]')  # type
        type = doc[0].get('value')
        doc = parsed_page.xpath('//input[contains(@id, "scs_customer_name")]') #customer
        customer = doc[0].get('value')
        paid = ""
        doc = parsed_page.xpath('//input[contains(@id, "scs_checknumber")]')  # check_num
        check_num = doc[0].get('value')
        doc = parsed_page.xpath('//input[contains(@id, "scs_checkdate")]')  # check_date
        check_date = doc[0].get('value')
        doc = parsed_page.xpath('//input[contains(@id, "scs_checkamount")]')  # check_amount
        check_amount = doc[0].get('value')
        if check_amount == req_amount:
            paid = "yes"
        else:
            paid = "no"
        info = (href, payment_num, instaler, initiator, invoice_num, req_amount, pur_order, description, date, labor_ctg, store, type, customer, paid, check_num, check_date, check_amount)


        sql_formula = "INSERT INTO paymentinfo (ID, link, payment_num, instaler, initiator, invoice_num, " \
                      "req_amount, pur_order, description, date, labor_ctg, store, type, customer, " \
                      "paid, check_num, check_date, check_amount) " \
                      "VALUES (NULL, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);"

        mycursor.execute(sql_formula, info)
        mydb.commit()
    if ok == 1:
        break
    driver.get('https://scs-lowes.microsoftcrmportals.com/Payments/')
    time.sleep(12)
    btn_next = driver.find_element_by_link_text(str(next_page))
    btn_next.click()
    next_page = next_page + 1

check_query = "SELECT link FROM paymentinfo WHERE paid='no'"
mycursor.execute(check_query)
links_nt_pd = mycursor.fetchall()
for link in links_nt_pd:
    href = str(link)
    href = href[2:-3]
    driver.get(href)
    parsed_page = lxml.html.fromstring(driver.page_source)
    doc = parsed_page.xpath('//input[contains(@id, "scs_paymentamount")]')  # req_amount
    req_amount = doc[0].get('value')
    doc = parsed_page.xpath('//input[contains(@id, "scs_checkamount")]')  # check_amount
    check_amount = doc[0].get('value')
    if (req_amount == check_amount):
        sql_update = "UPDATE paymentinfo SET paid='yes' WHERE link=%s"
        mycursor.execute(sql_update, (href, ))
        mydb.commit()
        doc = parsed_page.xpath('//input[contains(@id, "scs_checknumber")]')  # check_num
        check_num = doc[0].get('value')
        sql_update = "UPDATE paymentinfo SET check_num=%s WHERE link=%s"
        mycursor.execute(sql_update, (check_num, href))
        mydb.commit()
        doc = parsed_page.xpath('//input[contains(@id, "scs_checkdate")]')  # check_date
        check_date = doc[0].get('value')
        sql_update = "UPDATE paymentinfo SET check_date=%s WHERE link=%s"
        mycursor.execute(sql_update, (check_date, href))
        mydb.commit()
        sql_update = "UPDATE paymentinfo SET check_amount=%s WHERE link=%s"
        mycursor.execute(sql_update, (check_amount, href))
        mydb.commit()

driver.quit()