#!/usr/bin/env python
# coding: utf-8

# In[77]:


import requests
import urllib.request
from urllib.request import urlopen
import json
import datetime
import time
import re
import sqlite3
import gspread
from oauth2client.service_account import ServiceAccountCredentials
from pydrive.auth import GoogleAuth
from pydrive.drive import GoogleDrive
import csv
import pandas as pd


# In[59]:


# In[101]:
post_ids=[]
titles=[]
urls=[]
authors=[]
#tags=[]
#categories=[]
release_dates=[]
update_dates=[]
status_datas=[]
post_types=[]
contents=[]
parents=[]

afi_link="/web/"
#POST先URL
url = "https://plus-company.jp/epilino/wp-json/wp/v2/pages?page="

#POST送信
response = requests.get(url)
resDatas = response.json()

Err_cnt=0
#for i in range(1,99):
for i in range(1,35):
    #print(i)
    response = requests.get(url+str(i))
    if(response.status_code==200):
        resDatas = response.json()
        for y in range(0,len(resDatas)):
            post_ids.append(resDatas[y]['id'])
            titles.append(resDatas[y]['title']['rendered'])
            urls.append(resDatas[y]['link'])
            authors.append(resDatas[y]['author'])
            #tags.append(resDatas[y]['tags'])
            #categories.append(resDatas[y]['categories'])
            release_dates.append(resDatas[y]['date'])
            update_dates.append(resDatas[y]['modified'])
            status_datas.append(resDatas[y]['status'])
            post_types.append(resDatas[y]['type'])
            contents.append(resDatas[y]['content']['rendered'])
            parents.append(resDatas[y]['parent'])
    else:
        Err_cnt=Err_cnt+1
        if(Err_cnt>10):
            print("Err")
            i=999
            break


# In[99]:


url2 = "https://plus-company.jp/epilino/wp-json/wp/v2/users?page="
user_ids=[]
user_names=[]
Err_cnt=0
for i in range(1,10):
    #print(i)
    response = requests.get(url2+str(i))
    if(response.status_code==200):
        resDatas = response.json()
        for y in range(0,len(resDatas)):
            user_ids.append(resDatas[y]['id'])
            user_names.append(resDatas[y]['name'])
    else:
        Err_cnt=Err_cnt+1
        if(Err_cnt>10):
            print("Err")
            i=999
            break


# In[103]:


user_ids


# In[74]:


datetime.date.today()#今日の日付
datetime.date.today().year#年
datetime.date.today().day#日
datetime.date.today().isoformat()
today=datetime.datetime.today().strftime("%Y-%m-%d")


# In[75]:


#内部発リンク取得
contents_links=[]
cnt=0
for i in range(len(post_ids)):
    cnt=cnt+1
    #html = urlopen(urls[i])
    #check_data = html.read().decode()
    check_data = contents[i]
    get_datas2=((re.findall(r'href=\".*?\"', check_data)))
    contents_link=""
    try:
        for yy in range(len(get_datas2)):
            link_data = get_datas2[yy]
            link_data = link_data.replace('href="', '')
            link_data = link_data.replace('"', '')
            if(link_data[0]=="/"):
                link_data="https://plus-company.jp/epilino"+link_data
                
            if('plus-company.jp' in link_data):
                if(afi_link in link_data):
                    if(contents_link==""):
                        contents_link="["+link_data
                    else:
                        contents_link=contents_link+","+link_data
    except:
        contents_link=""
        
    #contents_link = contents_link.replace('https://plus-company.jp/epilino/wp-json/oembed/1.0/embed?url=', '')
    #contents_link = contents_link.replace('https://plus-company.jp/epilino/wp-json/,', '')
    #contents_link = re.sub('https://plus-company.jp/epilino/wp-json/wp/v2/pages/[0-9]{1,4},', '', contents_link)
        
    if(contents_link==""):
        contents_link=""
    else:
        contents_link=contents_link+"]"
        
    contents_links.append(contents_link)


# In[76]:


#関連記事取得
related_article_links=[]
for i in range(len(post_ids)):
    try:
        html = urlopen(urls[i])
        related_article_link=""
        check_data = html.read().decode()
        get_datas=((re.findall(r'<div class=\"recommend_box_area\">.*</aside>', check_data, flags = re.DOTALL)))
    except:
        html ="404"
        get_datas="404"
    try:
        check_data = get_datas[0]
        get_datas2=((re.findall(r'href="https://plus-company\.jp.*?"', check_data)))

        try:
            for yy in range(len(get_datas2)):
                link_data = get_datas2[yy]
                link_data = link_data.replace('href="', '')
                link_data = link_data.replace('"', '')

                if(related_article_link==""):
                    related_article_link="["+link_data
                else:
                    related_article_link=related_article_link+","+link_data

        except:
            related_article_link=""

        if(related_article_link==""):
            related_article_link=""
        else:
            related_article_link=related_article_link+"]"
        print related_article_link
        related_article_links.append(related_article_link)
    except:
        related_article_link=""
        related_article_links.append(related_article_link)


# In[77]:


#本文被リンク
contents_back_links=[]
for i in range(len(post_ids)):
    try:
        check_url=urls[i]
        back_link_url=""
        for yy in range(len(contents_links)):
            check_contents_link=contents_links[yy]
            if(check_url in check_contents_link):
                if(back_link_url==""):
                    back_link_url="["+urls[yy]
                else:
                    back_link_url=back_link_url+","+urls[yy]

        if(back_link_url==""):
            back_link_url=""
        else:
            back_link_url=back_link_url+"]"
    except:
        back_link_url=""
    contents_back_links.append(back_link_url)


# In[78]:


#関連記事被リンク
related_article_back_links=[]
for i in range(len(post_ids)):
    try:
        check_url=urls[i]
        back_link_url=""
        for yy in range(len(related_article_links)):
            check_contents_link=related_article_links[yy]
            if(check_url in check_contents_link):
                if(back_link_url==""):
                    back_link_url="["+urls[yy]
                else:
                    back_link_url=back_link_url+","+urls[yy]

        if(back_link_url==""):
            back_link_url=""
        else:
            back_link_url=back_link_url+"]"
    except:
        back_link_url=""

    related_article_back_links.append(back_link_url)


# In[84]:


#使用画像取得
use_images=[]
use_image_files=[]
cnt=0
for i in range(len(post_ids)):
    cnt=cnt+1
    check_data = contents[i]
    get_datas2=((re.findall(r'src=\".*?\"', check_data)))
    use_image=""
    use_image_file=""
    try:
        for yy in range(len(get_datas2)):
            link_data = get_datas2[yy]
            link_data = link_data.replace('src="', '')
            link_data = link_data.replace('"', '')
           #if(link_data[0]=="/"):
            link_data=link_data
            link_data2=link_data.replace('https://epilino-img.plus-company.jp/img/', '')
            #if('plus-company.jp' in link_data):
            if(use_image==""):
                use_image="["+link_data
            else:
                use_image=use_image+","+link_data
            
            if(use_image_file==""):
                use_image_file="["+link_data2
            else:
                use_image_file=use_image_file+","+link_data2
                
                
    except:
        use_image=""
        use_image_file=""
        
    #contents_link = contents_link.replace('https://plus-company.jp/epilino/wp-json/oembed/1.0/embed?url=', '')
    #contents_link = contents_link.replace('https://plus-company.jp/epilino/wp-json/,', '')
    #contents_link = re.sub('https://plus-company.jp/epilino/wp-json/wp/v2/pages/[0-9]{1,4},', '', contents_link)
        
    if(use_image==""):
        use_image=""
    else:
        use_image=use_image+"]"
    
    if(use_image_file==""):
        use_image_file=""
    else:
        use_image_file=use_image_file+"]"
        
    use_images.append(use_image)
    use_image_files.append(use_image_file)


# In[86]:


#親ページ取得情報取得
categorys=[]
for i in range(len(parents)):
    if(parents[i]==0):
        category="-"
    else:
        for x in range(len(post_ids)):
            if(post_ids[x]==parents[i]):
                category=titles[x]
    categorys.append(category)


# In[120]:


#ライターの名前変換
author_names=[]
for i in range(len(authors)):
    add_name="-"
    for y in range(len(user_ids)):
        if(int(authors[i])==int(user_ids[y])):
            add_name=user_names[y]
            continue
        else:
            pass
    author_names.append(add_name)
    

try:
    #2つのAPIを記述しないとリフレッシュトークンを3600秒毎に発行し続けなければならない
    scope = ['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive']
    spread_json="decoded-cove-298205-261119a2dc7d.json"
    SPREADSHEET_KEY="1yI2gBY0h6OfZ5UgpCYf4OijONjO9E5zFejfEtgz0aew"
    #認証情報設定
    #ダウンロードしたjsonファイル名をクレデンシャル変数に設定（秘密鍵、Pythonファイルから読み込みしやすい位置に置く）
    credentials = ServiceAccountCredentials.from_json_keyfile_name(spread_json, scope)

    #OAuth2の資格情報を使用してGoogle APIにログインします。
    gc = gspread.authorize(credentials)

    TAB_NAME2="7pocketタグリスト"
    workbook = gc.open_by_key(SPREADSHEET_KEY)
    worksheet = workbook.worksheet(TAB_NAME2)
except:
    pass

#7poketタグリスト取得
_7pocket_datas = worksheet.get_all_records()
_7pocket_tags=[]
for i in range(len(_7pocket_datas)):
    _7pocket_tags.append(_7pocket_datas[i]['HTMLタグ属性名'])


#contentsの7poketタグ取得
contents_7pocket_tags=[]
cnt=0
get_data=""
get_data_tags=""
for i in range(len(post_ids)):
    cnt=cnt+1
    check_data = contents[i]
    get_data_tags=((re.findall(r'class=\".*?\"', check_data)))
    contents_7pocket_tag=""
    try:
        for yy in range(len(get_data_tags)):
            get_data_1 = get_data_tags[yy]
            get_data_2 = get_data_1.replace('class="', '')
            get_data_3 = get_data_2.replace('"', '')
            get_data_3 = get_data_3.replace('　', '')
            get_data_4 = get_data_3.split(' ')
            #print(get_data_4)
            for zz in range(len(get_data_4)):
                for xx in range(len(_7pocket_tags)):
                    if(_7pocket_tags[xx] == get_data_4[zz]):
                        if(contents_7pocket_tag==""):
                            contents_7pocket_tag=_7pocket_tags[xx]
                        else:
                            contents_7pocket_tag=contents_7pocket_tag+","+_7pocket_tags[xx]
    except:
        contents_7pocket_tag=""
        
    if(contents_7pocket_tag==""):
        contents_7pocket_tag=""
    else:
        pass
        #contents_7pocket_tag=contents_7pocket_tag+"]"
        
    contents_7pocket_tag = contents_7pocket_tag.split(',')
    contents_7pocket_tag = set(contents_7pocket_tag)
    contents_7pocket_tag = list(contents_7pocket_tag)
    contents_7pocket_tag_ss =""
    
    for i in range(len(contents_7pocket_tag)):
        if(contents_7pocket_tag_ss==""):
            contents_7pocket_tag_ss="["+contents_7pocket_tag[i]
        else:
            contents_7pocket_tag_ss=contents_7pocket_tag_ss+","+contents_7pocket_tag[i]
    
    if(contents_7pocket_tag_ss==""):
        contents_7pocket_tag_ss=""
    else:
        contents_7pocket_tag_ss=contents_7pocket_tag_ss+"]"
    
    #print(contents_7pocket_tag_ss)
    #print(xx)
    contents_7pocket_tags.append(contents_7pocket_tag_ss)

# In[121]:


#CSV化
df = pd.DataFrame()
df["記事ID"] = post_ids
df["記事タイトル"] = titles
df["URL"] = urls
df["ライターID"] = authors
df["ライター"] = author_names
#df["タグ"] = tag_txts
df["カテゴリー"] = categorys
df["公開日"] = release_dates
df["更新日"] = update_dates
#df["本文"] = contents
df["関連記事リンク（発リンク）"] = related_article_links
df["本文内記事リンク（発リンク）"] =  contents_links
df["関連記事リンク（被リンク）"] = related_article_back_links
df["本文内記事リンク（被リンク）"] = contents_back_links
#df["セーフサーチ対象"] = safe_search_flgs
#df["セーフサーチ対象KW"] = safe_search_kw_s
#df["ID"] = common_content_ids
#df["ID→URL"] =  id_to_urls
df["使用画像"] = use_images
df["使用画像ファイル名"] = use_image_files
df["7poketタグ"] = contents_7pocket_tags

#CSVファイルに出力
csv_file_name="epilino_entry_"+today+".csv"
df.to_csv(csv_file_name,index=False)


# In[122]:


try:
    #2つのAPIを記述しないとリフレッシュトークンを3600秒毎に発行し続けなければならない
    scope = ['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive']
    spread_json="decoded-cove-298205-261119a2dc7d.json"
    SPREADSHEET_KEY="1yI2gBY0h6OfZ5UgpCYf4OijONjO9E5zFejfEtgz0aew"
    #認証情報設定
    #ダウンロードしたjsonファイル名をクレデンシャル変数に設定（秘密鍵、Pythonファイルから読み込みしやすい位置に置く）
    credentials = ServiceAccountCredentials.from_json_keyfile_name(spread_json, scope)

    #OAuth2の資格情報を使用してGoogle APIにログインします。
    gc = gspread.authorize(credentials)

    TAB_NAME=csv_file_name
    workbook = gc.open_by_key(SPREADSHEET_KEY)
    worksheet = workbook.sheet1
    workbook.add_worksheet(title=TAB_NAME, rows=3000, cols=26)
except:
        pass


# In[123]:


try:
    from gspread_dataframe import get_as_dataframe, set_with_dataframe
    worksheet = workbook.worksheet(TAB_NAME)
    #DataFrame貼り付け
    set_with_dataframe(worksheet, df)

    today=datetime.date.today() 
    oneday=datetime.timedelta(days=1) 
    yesterday=today-oneday  
    delete_tab_name="epilino_entry_"+str(yesterday)+".csv"
except:
    pass
try:
    worksheet = workbook.worksheet(delete_tab_name)
    workbook.del_worksheet(worksheet)
except:
    print("削除対象のシートはありませんでした")