# Put the ApacheConAsia讲师信息收集表.xlsx、ApacheConSessions.xlsx file in the project root directory
# will be generated in the root directory zh_name_title.csv and en_name_title.csv file

from pypinyin import lazy_pinyin
import openpyxl
import csv
from translate import Translator
 
# First, define a CSV file
zh_head =["zh_name","zh_title","track","zh_position"]
en_head =["en_name","en_title","track","en_position"]
with open("./zh_name_title.csv","a+",encoding="utf-8",newline="") as zh_f:
   csvf=csv.writer(zh_f)
   csvf.writerow(zh_head)
with open("./en_name_title.csv","a+",encoding="utf-8",newline="") as en_f:
   csvf=csv.writer(en_f)
   csvf.writerow(en_head)

# Open the EXECL file
session=openpyxl.load_workbook("./ApacheConSessions.xlsx")
collect=openpyxl.load_workbook("./ApacheConAsia讲师信息收集表.xlsx")

# Take the string between two fixed symbols
def get_str_btw(str,begin,end):
   par=str.partition(begin)
   return (par[2].partition(end))[0][:]

# Translate Chinese name into English name
def translateName_ch_en(zh_name):
   name_list=lazy_pinyin(zh_name)
   xin=name_list[0]
   ming_list=name_list[1:]
   ming=""
   for i in ming_list:
      ming=ming+i
      en_name=ming.capitalize()+" "+xin.capitalize()
   return (en_name)

# Gets the active table object
session_active=session.active
collect_active=collect.active
flag=False
for cell1 in session_active['J']:
   str=cell1.value
   row=cell1.row
   mail_list=str.split(",")
   for i in mail_list:
      if row>1 :
         session_mail=get_str_btw(i,'<','>')  
         names=i.split(" <")
         zh_title=session_active.cell(row,7).value
         en_title=session_active.cell(row,6).value
         track=session_active.cell(row,1).value
         zh_name=names[0].strip()
         # For names with brackets, remove the brackets and the contents in the brackets
         zh_name=zh_name.split('(',1)[0]
         zh_name=zh_name.split('（',1)[0]
         if zh_title!=None and en_title!=None :
            # Judge whether it is a Chinese name
            if '\u4e00' <=zh_name<='\u9fff':
               # Translate Chinese name into English name
               en_name=translateName_ch_en(zh_name)
            else:
               en_name=zh_name
               for mail in collect_active['J'] :
                  if mail.value==session_mail :
                     zh_name=collect_active.cell(mail.row,3).value
                     break
            
            # According to the topic of Chinese speech, get the position information
            flag=False
            for position in collect_active['D']:
               if position.value==zh_title or position.value==en_title:
                  zh_position=collect_active.cell(position.row,6).value

                  # Translation from Chinese to English
                  translator=Translator(from_lang="chinese",to_lang="english")
                  en_position=translator.translate(zh_position)
                  flag=True
                  break
            if flag==False :
               zh_position=0
               en_position=0
            
            # Write data to a CSV file
            zh_data=[(zh_name,zh_title,track,zh_position)]
            en_data=[(en_name,en_title,track,en_position)]
            with open("./zh_name_title.csv","a+",encoding="utf-8",newline="") as zh_f:
               csvf=csv.writer(zh_f)
               csvf.writerows(zh_data)

            with open("./en_name_title.csv","a+",encoding="utf-8",newline="") as en_f:
               csvf=csv.writer(en_f)
               csvf.writerows(en_data)