def getamzProd(a,i,search_1):#取得連線後的DataFrame資料,a是index,i是DataFrame總長度
    while(a<=i):
        ProdId=search_1.iloc[a]['pindex']
        pname=search_1.iloc[a]['pname']
#         totalRev=search_1.iloc[a]['totalRev']
        totalRev=1#快速測試用
        return ProdId,pname,totalRev

def Authors(soup,ProdId):#(1)評論者 &產品ID
    list_author=[]
    list_prodid=[]
#     rAuthors=soup.select('span[class="a-size-base a-color-secondary review-byline"] > a[data-hook="review-author"]')
    rAuthors=soup.select('div[data-hook="review"]')

    for author in rAuthors:
        list_author.append(author.div.next_sibling.span.get_text("|",strip=True).split("|")[1])
        list_prodid.append(ProdId)
#         print(author.div.prettify())
#         print(list_author)
    return list_author,list_prodid                              

def Stars(soup):#(2)星星數
    list_star=[]
    Rstars=soup.select("i[data-hook='review-star-rating']")
    for star in Rstars:
        star=re.sub('[a-zA-Z].+s','',star.text).split(' ')[0][0]
        fStar=int(star)
        list_star.append(fStar)
#         print(repr(fStar))
    return list_star

def Dates(soup):#(3)日期
    list_date=[]
    rDates=soup.select("span[data-hook='review-date']")
    for date in rDates:
        date2=(date.text)[3:]
        date3=datetime.datetime.strptime(date2, '%B %d, %Y').strftime('%Y-%m-%d')
        list_date.append(date3)
#         print(repr(date2),end='\n')
    return list_date    

def Title(soup):#(4)評論主旨
    list_title=[]
    rtitle=soup.select("a[data-hook='review-title']")
    for title in rtitle:
        list_title.append(title.text)
#         print((title.text),end='\n')
    return list_title

def Reviews(soup):#(5)評論內容
    list_review=[]
    reviews=soup.select("span[class='a-size-base review-text']")
    for review in reviews:
#         list_review.append(review.text)
        list_review.append(review.get_text(separator="\n\n",strip=True))
#         print(repr(review.text)) 
    return list_review

def Verifieds(soup):#(6)購買驗證
    list_verified=[]
    rVerifieds=soup.select('div[class="a-row a-spacing-mini review-data review-format-strip"]')
    a=0
    for verified in rVerifieds :
        if 'Verified' in verified.text:
            ver=1
            a+=1
        else:
            ver=0
            a+=1
        list_verified.append(ver)
#         print(a,(ver),verified,end='\n')
    return list_verified  

def Comments(soup):#(7)評論回覆數
    list_comment=[]
    rcomments = soup.select('span[class="review-comment-total aok-hidden"]')
    for comment in rcomments:
        list_comment.append(comment.text)
#         print(repr(comment.text))
    return list_comment     

def Helps(soup):#(8)覺得有幫助的人數
    list_helps=[]
    tagsHelps=soup.select('span[class="cr-vote-buttons"] > span[class="a-color-secondary"]')
    idx=0
    for helps in tagsHelps:
        if "One" in helps.text:
            NumPeopleFindHelpful=1
        elif (helps.span==None):
            NumPeopleFindHelpful=0    
        else:
            NumPeopleFindHelpful=int(re.sub('[^0-9]', '',(helps.text)))
        idx+=1
#         print(idx,repr(NumPeopleFindHelpful))                
        list_helps.append(NumPeopleFindHelpful)
    return list_helps

def Crawler(ProdId,ProdName,totalNumReviews,maxretrytime=60): 
    '''
    此函數輸入ProdId,ProdName,reviews
    輸出為含有Amazon評論等資訊的Dataframe
    '''
    url_base ="https://www.amazon.com/"
    url_01 = "/product-reviews/"
    url_02="/ref=cm_cr_arp_d_paging_btm_1?pageNumber="
    url_03="&reviewerType=all_reviews&pageSize=50"
    #分辨要換多少頁 除以50有餘數就加一頁
    #if (totalNumReviews %50) != 0:
    #    totalNumPages=int(totalNumReviews/50) +1
    #else:
    #    totalNumPages=int(totalNumReviews/50)
    totalNumPages=int(totalNumReviews/50)+1
    #table_final=pd.DataFrame()
    list_prodid=[]
    list_author=[]
    list_star=[]
    list_date=[]
    list_title=[]
    list_review=[]
    list_verified=[]
    list_comments=[]
    list_helps=[]
    for currentPageNum in range(1,totalNumPages+1):
        print("ProdId= %s. Total number of pages= %s. Current page= %s."%(ProdId,totalNumPages,currentPageNum) )
        passed=False
        cnt=0
        while(passed==False):
            cnt+=1
            if(cnt>maxretrytime):
                raise Exception("Error! Tried too many times but we are still blocked by Amazon.")
                print("ProdId="+ProdId+","+"CurrentPage="+currentPageNum)
            try:
                # 建立連線
                with requests.Session() as session:
                    #session.headers = {'User-Agent': "Mozilla/5.0 (Windows NT 6.1; WOW64; rv:50.0) Gecko/20100101 Firefox/50.0"}
                    uri=url_base+ProdName+url_01+ProdId+url_02+str(currentPageNum)+url_03
                    r=session.get(uri)

                    if(r.status_code!=200):
                        print("Connection failed(status/=200). Reconnecting...")
                        sleep(0.3)
                    else:
                        # 回應200則獲取湯
                        soup = BeautifulSoup(r.content,"lxml")
                        #print(soup.prettify())
                        # 若發現湯是假的，就小睡數秒，之後再重複獲取一次湯。若重複獲取湯的次數超過maxretrytime，程式將終止
                        if("Robot Check" in soup.text):
                            print("we are identified as a robot! Reconnecting...")
                            sleep(1+0.5*random.randint(0,1)) # 睡 1 或 1.5 秒
                            if(cnt>25):
                                sleep(1)                 # 要是一直不行(重試超過25次)，不如就再多睡一秒吧
                        else:
                            print("We've obtained the correct soup!")
                            passed=True

                            lst_author,lst_prodid=Authors(soup,ProdId) #(1)評論者與ProdId 分別放到2個列表
                            lst_star=Stars(soup)                        #(2)星星數
                            lst_date=Dates(soup)                        #(3)日期
                            lst_title=Title(soup)                       #(4)評論主旨
                            lst_review=Reviews(soup)                    #(5)評論內容
                            lst_verified=Verifieds(soup)                #(6)購買驗證
                            lst_comments=Comments(soup)                 #(7)評論回覆數
                            lst_helps=Helps(soup)                       #(8)覺得有幫助的人數
                            
                            print(lst_author)
    #                         print(list_star)
    #                         print(list_date)
    #                         print(list_title)
    #                         print(list_review)
    #                         print(list_verified)
    #                         print(list_comments)
    #                         print(list_helps)
                            print("URL=",uri)
                            lengths=[len(lst_prodid),len(lst_author),len(lst_star),len(lst_date),len(lst_title),len(lst_review),len(lst_verified),len(lst_comments),len(lst_helps)]
                            if(len(set(lengths))!=1):
                                print(lengths)
                                warnings.warn('Beware. Lists obtained have no equal length.')
                                print("length of lst_prodid=",len(lst_prodid))       
                                print("length of lst_author=",len(lst_author))
                                print("length of lst_star=",len(lst_star))
                                print("length of lst_date=",len(lst_date))
                                print("length of lst_title=",len(lst_title))
                                print("length of lst_review=",len(lst_review))
                                print("length of lst_verified=",len(lst_verified))
                                print("length of lst_comments=",len(lst_comments))
                                print("length of lst_helps=",len(lst_helps))
                            
                            #data={"pindex":list_prodid,"author":list_author,"star":list_star,"date":list_date,"title":list_title,"review":list_review,"verified":list_verified,"comment":list_comments,"help":list_helps}

    #                         try:
                            #以下兩行效率不一定佳。但就算不佳，因為我們資料量不是很大，所以暫無更改必要。
                            #table=pd.DataFrame(data)
                            #table_final=table_final.append(table,ignore_index=True)
    #                         except ValueError:
    #                             print("ValueError!")
    #                             print(soup.prettify())
    #                             table.to_csv("crawler.csv", sep=',', encoding='utf-8')            
            except:
                print("Error encounted! Probably due to connection time out. Reconnecting...")
                print("ProdId= "+ProdId+". "+"Current Page= "+str(currentPageNum))
                sleep(1)

        list_prodid+=lst_prodid
        list_author+=lst_author
        list_star+=lst_star
        list_date+=lst_date
        list_title+=lst_title
        list_review+=lst_review
        list_verified+=lst_verified
        list_comments+=lst_comments
        list_helps+=lst_helps
        
    df=pd.DataFrame.from_items([("pindex",list_prodid),("author",list_author),("star",list_star),\
                                 ("date",list_date),("title",list_title),("review",list_review), \
                                 ("verified",list_verified),("comment",list_comments),("help",list_helps)])\
         .drop_duplicates("review").reset_index(drop=True)
    return df

def prodInfoFetcherForCrawler(thisCrawlerID,prodType):
    prodTypes=["central","canister","handheld","robotic","stick","upright","wetdry"]

    engine=create_engine("mysql+pymysql://semantic:GbwSq1RzFb@104.199.201.206:13606/Tests?charset=utf8mb4",echo=False, encoding='utf-8')
    conn = engine.connect()

    sql_command = "SELECT pindex,pname,totalRev,cID,cStatus FROM Tests.amzProd where type='"+ prodType +"' \
    and cStatus!=1 and cID="+str(thisCrawlerID)+" ORDER BY totalRev desc"
    resultSet = pd.read_sql_query(sql=sql_command, con=conn, coerce_float=False)

    conn.close()

    return resultSet

def prodRevstoSQL(ProdId,resultTable):
    prodTypes=["central","canister","handheld","robotic","stick","upright","wetdry"]

    # prepare the connection and connect to the DB
    engine=create_engine("mysql+pymysql://semantic:GbwSq1RzFb@104.199.201.206:13606/Tests?charset=utf8mb4",convert_unicode=True,echo=False)
    conn = engine.connect()
    
    resultTable.to_sql(name='amzRev', con=conn, if_exists = 'append', index=False)

    sql_command = "UPDATE Tests.amzProd SET cStatus=1 where pindex='"+ ProdId +"'"
    result = conn.execute(sql_command)
    # close the connection
    conn.close()

prodTypes=["central","canister","handheld","robotic","stick","upright","wetdry"]
thisCrawlerID=1
prodType=prodTypes[5]
resultSet=prodInfoFetcherForCrawler(thisCrawlerID,prodType)

ProdId,ProdName,NumReviews=resultSet.loc[5,["pindex","pname","totalRev"]]
print(ProdId,ProdName,NumReviews)

ProdId,ProdName,NumReviews=resultSet.loc[5,["pindex","pname","totalRev"]]
NumReviews=200
resultTable=Crawler(ProdId,ProdName,NumReviews)

pd.set_option('max_colwidth', 2000)
print(resultTable.loc[resultTable["author"] == "Kellie",["review","title"]])

"""
Explanation: This notebook is written by Tsao, Suei and later finalized by Weng.
End of explanation
"""


resultSet

"""
Explanation: short summary

We had problems obtaining the correct amount of author names. Now it is fixed 
( by doing: author.div.next_sibling.span.get_text("|").split("|")[1]) )
the paragraphs are separated now 
( by doing: soupObj.get_text("\n\n") )
A single review is possible to be fetched more than once for some unknown reason. We'll just remove the duplicates.
I removed the unnecessary header: {'User-Agent': "Mozilla/5.0 (Windows NT 6.1; WOW64; rv:50.0) Gecko/20100101 Firefox/50.0"}, as Amazon will consider us as a robot if this header is used.


Pandas: the use of loc and iloc:
End of explanation
"""


print(resultSet.loc[0,"rurl"])
print(resultSet.iloc[0,-2])

"""
Explanation: the following results are the same:
End of explanation
"""


print(prodTypes[-2])

print([*range(5)])
print([*range(0,5,2)])
print([*range(1,5,2)])

"""
Explanation: 我們先前確立了我們的爬蟲程式完全可以抓到我們想要抓的東西，且抓了幾個有數千評論的商品，測試結果均正常。接下來，我們要指派特定吸塵器種類底下的商品給各爬蟲程式去爬。
以下假設爬蟲程式ID為3，且吸塵器種類為upright。
我們的設計是，ID=3的爬蟲程式會去查詢amzProd這個資料表，以此確定吸塵器種類為upright的商品評論中，有哪些是屬於他的任務。也就是說，若資料表內cID=3且吸塵器種類為upright，且cStatus=0(0代表該商品還沒有被爬過，反之，1代表有)，則爬蟲程式會認為該商品是它的任務。
End of explanation
"""


print(prodTypes)
thisCrawlerID=1
prodType=prodTypes[0]
resultSet=prodInfoFetcherForCrawler(thisCrawlerID,prodType)
display(resultSet)

print(*range(0,2,2))
print(*range(1,2,2))

print(*range(0,2,4))

for j in range(2,2,2):
    print("this is item %i of %i items"%(j,nRows))
    x=resultSet.loc[j,["pindex","pname","totalRev"]]
    pri

"""
Explanation: 查詢資料表，確定自己有多少任務：
End of explanation
"""


for j in [0,]:
    ProdId,ProdName,NumReviews=resultSet.loc[j,["pindex","pname","totalRev"]]
    print(ProdId,ProdName,NumReviews,"\n")
    resultTable=Crawler(ProdId,ProdName,NumReviews)
    display(resultTable)
    prodRevstoSQL(ProdId,resultTable)

"""
Explanation: 爬蟲開始，以下我們只爬待爬商品中，最多篇評論的那一個商品來做測試
End of explanation
"""


