xlsxHandler

# coding: utf-8
import xlsxwriter
from weixin.tools.db import queryMany
from weixin.tools.table_fields import TABLE_FIELDs
from openpyxl.writer.excel import ExcelWriter
from openpyxl import load_workbook


class FileHandler(object):
    '''
    文件处理类
    '''

    @staticmethod
    def create_file(file_name):
        workbook = xlsxwriter.Workbook(file_name)
        worksheet = workbook.add_worksheet()

        FileHandler.set_title(worksheet)

        workbook.close()

        return file_name

    @staticmethod
    def wirte_content(wb, data, cell):
        '''
        写数据
        :param wb:
        :param data:
        :param cell:
        :return:
        '''
        ws = wb.get_sheet_by_name(wb.get_sheet_names()[0])
        ws.cell(cell).value = data

    @staticmethod
    def set_title(worksheet):
        header = [u'老师', u'课程数目', u'课程', u'订单量', u'订单号', u'订单状态', u'交易额', u'交易时间', u'用户', u'渠道']

        # worksheet.set_column('B:B', 15)
        for index, title in enumerate(header):
            alphabet = chr(index + ord('A'))
            worksheet.set_column(alphabet + ':' + alphabet, 10)
            worksheet.write(alphabet + str(1), title)


class DateHandler(object):
    @staticmethod
    def get_teacher():
        result = {}

        order_dict = DateHandler.get_pay_order()

        teachers = queryMany('teacher', TABLE_FIELDs.get("teacher")[0])
        for teacher in teachers:
            # 获取该讲师下的活动列表
            activity_list = queryMany('activity', TABLE_FIELDs.get("activity")[0], wheres={'UserID': teacher['id']})

            # 获取该活动下的订单情况
            for activity in activity_list:
                used_list = []
                native_order_list = queryMany('activity_sign_up', TABLE_FIELDs.get("activity_sign_up")[0],
                                              wheres={"ActivityID": activity['ActivityID'], 'PayStatus': 1})
                for native_order in native_order_list:
                    orders_num = native_order['OutTradeNo']
                    if orders_num in order_dict:
                        native_order['sumMoney'] = order_dict[orders_num]['sumMoney']
                        native_order['pyDatetime'] = order_dict[orders_num]['pyDatetime']
                        native_order['pyStatus'] = order_dict[orders_num]['pyStatus']
                        native_order['orderNum'] = order_dict[orders_num]['orderNum']

                        username, channel_name = DateHandler.get_user_channel(order_dict[orders_num]['openID'])

                        native_order['user'] = username
                        native_order['ChannelName'] = channel_name

                        del order_dict[orders_num]
                        used_list.append(native_order)

                activity.update({'orders': used_list})

            result.update({teacher['Surname']: activity_list})

        if order_dict:
            return False
            
        # 无名尸单特殊处理
        mismatch_order = []
        for key in order_dict:
            username, channel_name = DateHandler.get_user_channel(order_dict[key]['openID'])
            order_dict[key].update({'user': username, 'ChannelName': channel_name})
            mismatch_order.append(order_dict[key])
            result.update({u'异常订单': [{'Subject': u'未知活动名称', 'orders': mismatch_order}]})

        return result

    @staticmethod
    def get_user_channel(openId):
        '''
        获取用户跟渠道
        :param openId:
        :return:
        '''
        channel_dict = DateHandler.get_channel()
        username = u'未知用户异常数据'
        channel_name = u'未知渠道'
        users = queryMany('user', TABLE_FIELDs.get("user")[0], wheres={"OpenID": openId})
        if users:
            user = users[0]
            username = users[0]['Surname']
            channel_name = u'公众号'  # 默认是公众号过来的
            if user['Channel'] in channel_dict:
                # 如果是0 ，则表明是公众号过来的, 否则读取对应的渠道明
                channel_name = channel_dict[user['Channel']]

        return username, channel_name

    @staticmethod
    def get_channel():
        '''
        获取渠道列表
        :return:
        '''
        result = {}
        channel_list = queryMany("channel", TABLE_FIELDs.get("channel")[0])
        for channel in channel_list:
            result.update({channel['id']: channel['ChannelName']})

        return result

    @staticmethod
    def get_pay_order():
        result = {}
        weixin_order = queryMany('pay_order_wx', TABLE_FIELDs.get("pay_order_wx")[0])
        # 还是以订单号进行统计
        for order in weixin_order:
            # TODO 可能出现同订单的情况，（购买系列课）
            key = order['orderNum']
            if key in result:
                print u'重复值'
            else:
                result.update({key: order})

        return result

    @staticmethod
    def date_to_excl(filename):
        '''
        读写xlsx,数据统一写完再做保存文件操作
        :param filename: 
        :return: 
        '''
        wb = load_workbook(filename)

        DateHandler.handler_data(wb)

        ew = ExcelWriter(wb)
        ew.save(filename)

    @staticmethod
    def handler_data(wb):
        teachers = DateHandler.get_teacher()
        rows = 2
        for teacher in teachers:

            FileHandler.wirte_content(wb, teacher, 'A' + str(rows))
            activity_list = teachers[teacher]
            FileHandler.wirte_content(wb, len(activity_list), 'B' + str(rows))
            if len(activity_list) == 0:
                rows += 1
                continue

            for activity in activity_list:
                FileHandler.wirte_content(wb, activity['Subject'], 'C' + str(rows))

                orders_list = activity['orders']
                num = len(orders_list)
                print num
                FileHandler.wirte_content(wb, len(orders_list), 'D' + str(rows))

                if len(orders_list) == 0:
                    rows += 1
                    continue
                for order in orders_list:
                    FileHandler.wirte_content(wb, order['orderNum'], 'E' + str(rows))
                    FileHandler.wirte_content(wb, order['pyStatus'], 'F' + str(rows))
                    FileHandler.wirte_content(wb, order['sumMoney'], 'G' + str(rows))
                    FileHandler.wirte_content(wb, order['pyDatetime'], 'H' + str(rows))
                    FileHandler.wirte_content(wb, order['user'], 'I' + str(rows))
                    FileHandler.wirte_content(wb, order['ChannelName'], 'J' + str(rows))
                    rows += 1


if __name__ == "__main__":
    filename = FileHandler.create_file('test' + '.xlsx')
    DateHandler.date_to_excl(filename)