import os
import logging
from xpinyin import Pinyin
import time
from moviepy.editor import VideoFileClip
#视频文件路径 这是是 / 最后也以 / 结尾
VIDEOPATH = "G:/resources/计算机/编程语言/Python/python3视频教程集合/Python3入门与进阶/"
#课程ID
COURSEID = 44
#章节ID  没有章节，默认为0
CHAPTERID = 22


SUFFIX = ".mp4"
                    
class video:
    #定义基本属性
    title = ''
    course_id = 0
    #拼音名
    slug = 0
    #播放地址
    url = ''
    #上线时间
    published_at = ''
    #时长，单位：秒
    duration = 0
    #章节
    chapter_id = 0
   
    #直接设置默认值的属性
    #用户id
    user_id = 0
    #价格
    charge = 0
    #观看次数
    view_num = 0
     #试看秒数
    free_seconds = 10
    #简短介绍
    short_description = ''
     #简介原始内容
    original_desc = ''
    #简介渲染后的内容
    render_desc = ''
    #1显示,-1隐藏
    is_show = 1
    created_at = ''
    updated_at = ''
    tencent_video_id = ''
    player_pc = 'xg'
    player_h5 = 'xg'
    
    #定义私有属性,私有属性在类外部无法直接进行访问
    __id = 0

    #定义构造方法
    def __init__(self,title,course_id,slug,url,published_at,duration,chapter_id):
        self.title = title
        self.course_id = course_id
        self.slug = slug
        self.url = url
        self.published_at = published_at
        self.duration = duration
        self.chapter_id = chapter_id
        self.short_description  = title
        self.original_desc = "<p>"+ title + "</p>"
        self.render_desc = "<p>"+ title + "</p>"
        self.created_at = published_at
        self.updated_at = published_at
        
        
logging.basicConfig(filename='getFiles.log',level=logging.INFO,
                    format ='%(asctime)s-%(levelname)s----%(message)s',
                    datefmt = '%y-%m-%d %H:%M',
                    filemode = 'w')
                    
p = Pinyin()

def get_viedo_time(filename):
    u"""
    获取视频时长（s:秒）
    """
    clip = VideoFileClip(filename)
    return clip.duration

# 遍历文件
def walkFile(file):
    L=[]
    for root, dirs, files in os.walk(file):
        # root 表示当前正在访问的文件夹路径
        # dirs 表示该文件夹下的子目录名list
        # files 表示该文件夹下的文件list
        # 遍历文件
        for file in files:
            #print(os.path.join(root, file))
            #根据后缀过滤掉非视频文件，防止程序获取视频时长时报错
            
            fileInfo=os.path.splitext(file)
            fileShortName,fileTypeSuffix=fileInfo
            if fileTypeSuffix.lower()==SUFFIX.lower():
                title = fileShortName
                #file 含有扩展名
                duration = int(get_viedo_time(os.path.join(root, file)));
                #duration = 1320;
                slug = p.get_pinyin(title).replace(' ','').replace('　','')
                published_at = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
                url = os.path.join(root, file).replace("G:/resources/","http://www.chxfresource.com/")
                course_id = COURSEID
                chapter_id = CHAPTERID
                v = video(title,course_id,slug,url,published_at,duration, chapter_id)
                L.append(v)

        # 遍历所有的文件夹
        #for d in dirs:
        #    print(os.path.join(root, d)) 
    for vi in L: 
        #print(file)
        #sql = f'{vi.user_id},{vi.course_id},{vi.title},{vi.slug},{vi.url},{vi.charge},{vi.view_num},{vi.short_description},{vi.original_desc},{vi.render_desc},{vi.published_at},{vi.is_show},{vi.chapter_id},{vi.duration},{vi.free_seconds}'
        sql = f'INSERT INTO videos (user_id, course_id, title, slug, url, charge, view_num, short_description, original_desc, render_desc, published_at, is_show, chapter_id, duration, free_seconds,created_at,updated_at,tencent_video_id,player_pc,player_h5) VALUES(\'{vi.user_id}\',\'{vi.course_id}\',\'{vi.title}\',\'{vi.slug}\',\'{vi.url}\',\'{vi.charge}\',\'{vi.view_num}\',\'{vi.short_description}\',\'{vi.original_desc}\',\'{vi.render_desc}\',\'{vi.published_at}\',\'{vi.is_show}\',\'{vi.chapter_id}\',\'{vi.duration}\',\'{vi.free_seconds}\',\'{vi.created_at}\',\'{vi.updated_at}\',\'{vi.tencent_video_id}\',\'{vi.player_pc}\',\'{vi.player_h5}\');'
        
        logging.info(sql)
    
    
    
def main():
    walkFile(VIDEOPATH)
    print("-------------处理完成！--------------");

if __name__ == '__main__':
    main()