## ---------------------------------------------------------------------------
## Licensed to the Apache Software Foundation (ASF) under one or more
## contributor license agreements.  See the NOTICE file distributed with
## this work for additional information regarding copyright ownership.
## The ASF licenses this file to You under the Apache License, Version 2.0
## (the "License"); you may not use this file except in compliance with
## the License.  You may obtain a copy of the License at
##
##      http://www.apache.org/licenses/LICENSE-2.0
##
## Unless required by applicable law or agreed to in writing, software
## distributed under the License is distributed on an "AS IS" BASIS,
## WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
## See the License for the specific language governing permissions and
## limitations under the License.
## ---------------------------------------------------------------------------

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

from xml.dom.minidom import parse
from pypinyin import lazy_pinyin
import xml.dom.minidom
import openpyxl
import csv

 
 # First, define a CSV file
head =["collect_row","pic","zh_name","en_name","position","track","title","mail","sessions_row"]
with open("./mapping.csv","a+",encoding="utf-8",newline="") as f:
   csvf=csv.writer(f)
   csvf.writerow(head)

# Using minidom parser to open XML document
DOMTree = xml.dom.minidom.parse("./drawing1.xml")

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

# Getting data in a collection
collection = DOMTree.documentElement
twoCellAnchors = collection.getElementsByTagName("xdr"+":"+"twoCellAnchor")

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

# Get row and pic
for twoCellAnchor in twoCellAnchors:
 
   # Get row
   from1 = twoCellAnchor.getElementsByTagName('xdr'+':'+'to')[0]
   row1=from1.getElementsByTagName('xdr'+':'+'row')[0]
   row=int(row1.childNodes[0].data)

   # Get pic
   pic = twoCellAnchor.getElementsByTagName('xdr'+':'+'pic')[0]
   blipFill = pic.getElementsByTagName('xdr'+':'+'blipFill')[0]
   blip = blipFill.getElementsByTagName('a'+':'+'blip')[0]
   if blip.hasAttribute("r"+":"+"embed"):
      pic="picture"+(blip.getAttribute("r"+":"+"embed"))[3:]

   # Gets the active table object
   collect_active=collect.active
   session_active=session.active

   # Get the name, position, track and email address of the lecturer
   zh_name=collect_active.cell(row,3).value
   position=collect_active.cell(row,6).value
   track=collect_active.cell(row,9).value
   mail=collect_active.cell(row,10).value

   # Judge whether it is a Chinese name
   if '\u4e00' <=zh_name <='\u9fff':
      # Translate Chinese name into English 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()
   else:
      en_name=zh_name

   # According to the mail from the collection table, find the corresponding row from the session table. 
   # If there is no corresponding row, return 0
   flag=False
   for cell1 in session_active['J']:
      str=cell1.value
      mail_list=str.split(",")
      for i in mail_list:
         session_mail=get_str_btw(i,'<','>')
         if session_mail==mail :
            flag=True
            session_row=cell1.row
            title=session_active.cell(cell1.row,7).value
            break
   if flag==False:
      session_row=0
      title=collect_active.cell(row,4).value       

   # Write data to a CSV file
   data=[
      (row,pic,zh_name,en_name,position,track,title,mail,session_row)
   ]
   with open("./mapping.csv","a+",encoding="utf-8",newline="") as f:
      csvf=csv.writer(f)
      csvf.writerows(data)