def exportDivSchedulesRefFormat(self, startgameday, prefix=""):
        headers = ['Game#', 'Game#', 'Tourn Match#','Date', 'Day', 'Time', 'Division', 'Round', 'Home', 'Visitor', 'Field', 'cr_trust', 'ar_trust', 'm_trust']
        datasheet = Dataset(title=prefix)
        datasheet.headers = list(headers)

        schedule_list = self.dbinterface.findDivisionSchedulePHMSARefFormat(startgameday)
        tabformat_list = [(_offset+x[match_id_CONST], x[match_id_CONST], tournMapGamedayIdToCalendar(x[gameday_id_CONST]), tournMapGamedayIdToDate(x[gameday_id_CONST]),
            datetime.strptime(x[start_time_CONST],"%H:%M").strftime("%I:%M %p"),
            x[age_CONST]+x[gen_CONST], x[round_CONST],
            x[home_CONST], x[away_CONST],
            self.fieldinfo[self.findexerGet(x[venue_CONST])]['name'],
            _reftrust_level[_rindexerGet(getTournDivID(x[age_CONST], x[gen_CONST]))]['cr'],
            _reftrust_level[_rindexerGet(getTournDivID(x[age_CONST], x[gen_CONST]))]['ar'],
            _reftrust_level[_rindexerGet(getTournDivID(x[age_CONST], x[gen_CONST]))]['ment'])
            for x in schedule_list] if prefix else [(mapGamedayIdToCalendar(x[gameday_id_CONST],format=1), 'Saturday',
                datetime.strptime(x[start_time_CONST],"%H:%M").strftime("%I:%M %p"),
                x[age_CONST]+x[gen_CONST],
                x[home_CONST], x[away_CONST],
                self.fieldinfo[self.findexerGet(x[venue_CONST])]['name'])
                for x in schedule_list]
        if prefix:
            atabformat_list = [(_offset+i, j[0], j[1], j[2], j[3], j[4], j[5], j[6], j[7], j[8], j[9], j[10], j[11], j[12]) for i,j in enumerate(tabformat_list)]
        else:
            atabformat_list = tabformat_list
        for tabformat in atabformat_list:
            datasheet.append(tabformat)
        sheet_xls_relpath = prefix+'_RefFormat.xls'
        sheet_xls_abspath = os.path.join('/home/henry/workspace/datagraph/bottle_baseball/download/xls',
                                         sheet_xls_relpath)
        with open(sheet_xls_abspath,'wb') as f:
            f.write(datasheet.xls)
        f.close()

 def generate_elimdivxls(self, genxls_id):
     headers = ['Match ID', 'Game Date', 'Day', 'Time', 'Division', 'Home',
         'Visitor', 'Venue', 'Round', 'Comment']
     datasheet_list = list()
     for divinfo in self.divinfo_list:
         div_id = divinfo[genxls_id]
         div_age = divinfo['div_age']
         div_gen = divinfo['div_gen']
         div_str = div_age + div_gen
         datasheet = Dataset(title=div_str)
         datasheet.headers = list(headers)
         match_list = self.sdbinterface.get_schedule(genxls_id, div_age=div_age,
             div_gen=div_gen, elim_flag=True)
         # note conversions for time from 24-hour to am/pm format
         tabformat_list = [(y['match_id'], x['game_date'],
             parser.parse(x['game_date']).strftime("%a"),
             datetime.strptime(x['start_time'], "%H:%M").strftime("%I:%M%p"),
             div_str, self.team_map(div_id, y['home']), self.team_map(div_id, y['away']),
             self.fieldinfo_list[self.findexerGet(y['venue'])]['field_name'],
             y['around'], y['comment']) for x in match_list for y in x['gameday_data']]
         for tabformat in tabformat_list:
             datasheet.append(tabformat)
         datasheet_list.append(datasheet)
     book = Databook(datasheet_list)
     bookname_xls_relpath = self.schedcol_name + "_byDivision.xls"
     bookname_xls_fullpath = os.path.join(self.dir_path, bookname_xls_relpath)
     with open(bookname_xls_fullpath,'wb') as f:
         f.write(book.xls)
     f.close()
     return [{'path':bookname_xls_relpath}]

 def exportDivTeamSchedules(self, div_id, age, gen, numteams, prefix=""):
     headers = ['Gameday#', 'Game Date', 'Day', 'Start Time', 'Venue', 'Home Team', 'Away Team']
     datasheet_list = []
     for team_id in range(1, numteams+1):
         team_str = age+gen+str(team_id)
         datasheet = Dataset(title=team_str)
         datasheet.headers = list(headers)
         teamdata_list = self.dbinterface.findTeamSchedule(age, gen, team_id)
         tabformat_list = [(x[gameday_id_CONST], tournMapGamedayIdToCalendar(x[gameday_id_CONST]), tournMapGamedayIdToDate(x[gameday_id_CONST]),
                            datetime.strptime(x[start_time_CONST],"%H:%M").strftime("%I:%M %p"),
                            self.fieldinfo[self.findexerGet(x[venue_CONST])]['name'],
                            x[home_CONST], x[away_CONST]) for x in teamdata_list]
         for tabformat in tabformat_list:
             datasheet.append(tabformat)
         datasheet_list.append(datasheet)
     book = Databook(datasheet_list)
     cdir = os.path.dirname(__file__)
     bookname_xls = prefix+age + gen +'_schedule.xls'
     bookname_html = prefix+age + gen +'_schedule.html'
     booknamefull_xls = os.path.join('/home/henry/workspace/datagraph/bottle_baseball/download/xls', bookname_xls)
     booknamefull_html = os.path.join('~/workspace/datagraph/bottle_baseball/download/html', bookname_html)
     with open(booknamefull_xls,'wb') as f:
         f.write(book.xls)
     f.close()
     '''

    def render_to_response(self, context, **response_kwargs):
        """If exporting, generate a csv."""
        if 'export' in self.request.GET:
            data = Dataset()
            data.headers = (
                'Name', 'Messages', 'Threads', 'Replies', 'Posters',
                'Category', 'Tags', 'State', 'Members', 'Admins', 'Private',
                'Published', 'Moderated', 'Featured', 'Member list published',
                'Created', 'Created By'
            )

            for group in self.get_queryset():
                data.append((
                    group.group.name, group.message_count, group.thread_count,
                    group.reply_count, group.posters,
                    group.category.name, groups_tags_string([group]),
                    group.state, group.member_count, group.owner_count,
                    group.private, group.published, group.moderated,
                    group.featured, group.member_list_published,
                    group.created_at, group.created_by
                ))

            response = HttpResponse(
                data.csv,
                content_type='text/csv'
            )
            response['Content-Disposition'] = 'attachment; filename=groups.csv'
            return response
        else:
            return super(GroupReportListView, self).render_to_response(
                context, **response_kwargs)

 def generate_fieldxls(self):
     headers = ['Game Date', 'Day', 'Time', 'Division', 'Home',
     'Visitor', 'Venue']
     datasheet_list = list()
     for fieldinfo in self.fieldinfo_list:
         field_name = fieldinfo['field_name']
         field_id = fieldinfo['field_id']
         datasheet = Dataset(title=field_name)
         datasheet.headers = list(headers)
         match_list = self.sdbinterface.get_schedule('field_id',
             field_id=field_id)
         tabformat_list = [(x['game_date'],
             parser.parse(x['game_date']).strftime("%a"),
             datetime.strptime(x['start_time'], "%H:%M").strftime("%I:%M%p"),
             x['div_age']+x['div_gen'], x['home'], x['away'], field_name)
             for x in match_list]
         for tabformat in tabformat_list:
             datasheet.append(tabformat)
         datasheet_list.append(datasheet)
     book = Databook(datasheet_list)
     bookname_xls_relpath = self.schedcol_name + "_byField.xls"
     bookname_xls_fullpath = os.path.join(self.dir_path, bookname_xls_relpath)
     with open(bookname_xls_fullpath,'wb') as f:
         f.write(book.xls)
     f.close()
     return [{'path':bookname_xls_relpath}]

def member_query(db):
    node_id = request.params.get('node_id')
    realname = request.params.get('realname')
    idcard = request.params.get('idcard')
    mobile = request.params.get('mobile')
    _query = db.query(
        models.SlcMember,
        models.SlcNode.node_name
    ).filter(
        models.SlcNode.id == models.SlcMember.node_id
    )
    if idcard:
        _query = _query.filter(models.SlcMember.idcard==idcard)
    if mobile:
        _query = _query.filter(models.SlcMember.mobile==mobile)
    if node_id:
        _query = _query.filter(models.SlcMember.node_id == node_id)
    if realname:
        _query = _query.filter(models.SlcMember.realname.like('%'+realname+'%'))

    if request.path == '/member':
        return render("bus_member_list", page_data = get_page_data(_query),
                       node_list=db.query(models.SlcNode),**request.params)
    elif request.path == "/member/export":
        data = Dataset()
        data.append((u'区域',u'姓名',u'用户名',u'证件号',u'邮箱', u'联系电话', u'地址', u'创建时间'))
        for i,_node_name in _query:
            data.append((
                _node_name, i.realname, i.member_name,i.idcard,
                i.email,i.mobile, i.address,i.create_time
            ))
        name = u"RADIUS-MEMBER-" + datetime.datetime.now().strftime("%Y%m%d-%H%M%S") + ".xls"
        with open(u'./static/xls/%s' % name, 'wb') as f:
            f.write(data.xls)
        return static_file(name, root='./static/xls',download=True)

    def export(self, queryset=None, task_meta=None):
        if queryset is None:
            queryset = self.get_queryset()
        headers = self.get_export_headers()
        data = Dataset(headers=headers)

        if isinstance(queryset, QuerySet):
            # Iterate without the queryset cache, to avoid wasting memory when
            # exporting large datasets.
            iterable = queryset.iterator()
        else:
            iterable = queryset

        if task_meta is not None:  # initialize the total amount accross multiple resources
            self.num_done = task_meta['done']

        for obj in iterable:
            data.append(self.export_resource(obj))

            if task_meta is not None:
                self._update_task_state(task_meta)

            logger.debug('Num done: %d' % self.num_done)

        return data

def csv_output_computers():
    """Utility function to generate a CSV output of computers information from
    Incredibus data, for audit/cleansing purposes.
    """
    computers = Computer.objects.all()
    d = Dataset()
    d.headers = [
        'ID',
        'HOSTNAME',
        'CHASSIS',
        'PROBABLE OWNER EMAIL',
        'PROBABLE OWNER CC',
        'MANAGED BY EMAIL',
        'ASSET NO.',
        'SERIAL NO.'
    ]

    for i in computers:
        row = [i.pk, i.sam_account_name[:-1], i.chassis]
        if i.probable_owner:
            row += [i.probable_owner.email.lower(), i.probable_owner.cost_centre]
        else:
            row += ['', '']
        if i.managed_by:
            row += [i.managed_by.email.lower()]
        else:
            row += ['']
        row += ['', i.serial_number]
        d.append(row)

    f = open('/tmp/computers.csv', 'w')
    f.write(d.csv)
    f.close()

def dump_program_reports():
    print('Running program report dump...')
    dataset = Dataset()
    dataset.headers = ['Site ID', 'Mobile', 'Timestamp', 'Group', 'Program', 'Period code', 'Period number', 'Atot', 'Arel', 'Tin', 'Tout', 'Dead', 'DefT', 'Dcur', 'Dmed']

    for report in ProgramReport.objects.select_related('group', 'program').order_by('created'):
        if not report.reporter.mobile.startswith('+'):
            continue

        dataset.append([
            report.site.hcid,
            report.reporter.mobile,
            timegm(report.created.utctimetuple()),
            report.group.code,
            report.program.code,
            report.period_code,
            report.period_number,
            report.new_marasmic_patients,
            report.readmitted_patients,
            report.patients_transferred_in,
            report.patients_transferred_out,
            report.patient_deaths,
            report.unconfirmed_patient_defaults,
            report.patients_cured,
            report.unresponsive_patients
        ])

    with open('program_reports.csv', 'w') as f:
        f.write(dataset.csv)

    print('Done')

def acceptlog_query(db,render):
    node_id = request.params.get('node_id')
    accept_type = request.params.get('accept_type')
    account_number = request.params.get('account_number')
    operator_name = request.params.get('operator_name')
    query_begin_time = request.params.get('query_begin_time')
    query_end_time = request.params.get('query_end_time')
    opr_nodes = get_opr_nodes(db)
    _query = db.query(
        models.SlcRadAcceptLog.id,
        models.SlcRadAcceptLog.accept_type,
        models.SlcRadAcceptLog.accept_time,
        models.SlcRadAcceptLog.accept_desc,
        models.SlcRadAcceptLog.operator_name,
        models.SlcRadAcceptLog.accept_source,
        models.SlcRadAcceptLog.account_number,
        models.SlcMember.node_id,
        models.SlcNode.node_name
    ).filter(
        models.SlcRadAcceptLog.account_number == models.SlcRadAccount.account_number,
        models.SlcMember.member_id == models.SlcRadAccount.member_id,
        models.SlcNode.id == models.SlcMember.node_id
    )
    if operator_name:
        _query = _query.filter(models.SlcRadAcceptLog.operator_name == operator_name)
    if node_id:
        _query = _query.filter(models.SlcMember.node_id == node_id)
    else:
        _query = _query.filter(models.SlcMember.node_id.in_([i.id for i in opr_nodes]))
    if account_number:
        _query = _query.filter(models.SlcRadAcceptLog.account_number.like('%' + account_number + '%'))
    if accept_type:
        _query = _query.filter(models.SlcRadAcceptLog.accept_type == accept_type)
    if query_begin_time:
        _query = _query.filter(models.SlcRadAcceptLog.accept_time >= query_begin_time + ' 00:00:00')
    if query_end_time:
        _query = _query.filter(models.SlcRadAcceptLog.accept_time <= query_end_time + ' 23:59:59')
    _query = _query.order_by(models.SlcRadAcceptLog.accept_time.desc())
    type_map = ACCEPT_TYPES

    if request.path == '/':
        return render(
            "bus_acceptlog_list",
            page_data=get_page_data(_query),
            node_list=opr_nodes,
            type_map=type_map,
            get_orderid=lambda aid: db.query(models.SlcMemberOrder.order_id).filter_by(accept_id=aid).scalar(),
            **request.params
        )
    elif request.path == '/export':
        data = Dataset()
        data.append((u'区域', u'上网账号', u'受理类型', u'受理时间', u'受理渠道', u'操作员', u'受理描述'))
        for i in _query:
            data.append((
                i.node_name, i.account_number, type_map.get(i.accept_type),
                i.accept_time, i.accept_source, i.operator_name, i.accept_desc
            ))
        name = u"RADIUS-ACCEPTLOG-" + datetime.datetime.now().strftime("%Y%m%d-%H%M%S") + ".xls"
        return export_file(name, data)

    def post(self):
        node_id = self.get_argument('node_id',None)
        accept_type = self.get_argument('accept_type',None)
        account_number = self.get_argument('account_number',None)
        operator_name = self.get_argument('operator_name',None)
        query_begin_time = self.get_argument('query_begin_time',None)
        query_end_time = self.get_argument('query_end_time',None)
        opr_nodes = self.get_opr_nodes()
        _query = self.db.query(
            models.TrAcceptLog.id,
            models.TrAcceptLog.accept_type,
            models.TrAcceptLog.accept_time,
            models.TrAcceptLog.accept_desc,
            models.TrAcceptLog.operator_name,
            models.TrAcceptLog.accept_source,
            models.TrAcceptLog.account_number,
            models.TrCustomer.node_id,
            models.TrNode.node_name
        ).filter(
            models.TrAcceptLog.account_number == models.TrAccount.account_number,
            models.TrCustomer.customer_id == models.TrAccount.customer_id,
            models.TrNode.id == models.TrCustomer.node_id
        )
        if operator_name:
            _query = _query.filter(models.TrAcceptLog.operator_name == operator_name)
        if node_id:
            _query = _query.filter(models.TrCustomer.node_id == node_id)
        else:
            _query = _query.filter(models.TrCustomer.node_id.in_([i.id for i in opr_nodes]))
        if account_number:
            _query = _query.filter(models.TrAcceptLog.account_number.like('%' + account_number + '%'))
        if accept_type:
            _query = _query.filter(models.TrAcceptLog.accept_type == accept_type)
        if query_begin_time:
            _query = _query.filter(models.TrAcceptLog.accept_time >= query_begin_time + ' 00:00:00')
        if query_end_time:
            _query = _query.filter(models.TrAcceptLog.accept_time <= query_end_time + ' 23:59:59')
        _query = _query.order_by(models.TrAcceptLog.accept_time.desc())
        type_map = ACCEPT_TYPES

        if self.request.path == '/admin/customer/acceptlog':
            return self.render(
                "acceptlog_list.html",
                page_data=self.get_page_data(_query),
                node_list=opr_nodes,
                type_map=type_map,
                get_orderid=lambda aid: self.db.query(models.TrCustomerOrder.order_id).filter_by(accept_id=aid).scalar(),
                **self.get_params()
            )
        elif self.request.path == '/admin/customer/acceptlog/export':
            data = Dataset()
            data.append((u'区域', u'上网账号', u'受理类型', u'受理时间', u'受理渠道', u'操作员', u'受理描述'))
            for i in _query:
                data.append((
                    i.node_name, i.account_number, type_map.get(i.accept_type),
                    i.accept_time, i.accept_source, i.operator_name, i.accept_desc
                ))
            name = u"RADIUS-ACCEPTLOG-" + datetime.datetime.now().strftime("%Y%m%d-%H%M%S") + ".xls"
            return self.export_file(name, data)

def dataset(data, headers=None):
    """ `data` is a list of dicts.
    """
    dataset = Dataset()
    dataset.dict = data
    if headers:
        dataset.headers = headers
    return dataset

def order_query(db, render):
    node_id = request.params.get('node_id')
    product_id = request.params.get('product_id')
    pay_status = request.params.get('pay_status')
    account_number = request.params.get('account_number')
    query_begin_time = request.params.get('query_begin_time')
    query_end_time = request.params.get('query_end_time')
    opr_nodes = get_opr_nodes(db)
    _query = db.query(
        models.SlcMemberOrder,
        models.SlcMember.node_id,
        models.SlcMember.realname,
        models.SlcRadProduct.product_name,
        models.SlcNode.node_name
    ).filter(
        models.SlcMemberOrder.product_id == models.SlcRadProduct.id,
        models.SlcMemberOrder.member_id == models.SlcMember.member_id,
        models.SlcNode.id == models.SlcMember.node_id
    )
    if node_id:
        _query = _query.filter(models.SlcMember.node_id == node_id)
    else:
        _query = _query.filter(models.SlcMember.node_id.in_([i.id for i in opr_nodes]))
    if account_number:
        _query = _query.filter(models.SlcMemberOrder.account_number.like('%' + account_number + '%'))
    if product_id:
        _query = _query.filter(models.SlcMemberOrder.product_id == product_id)
    if pay_status:
        _query = _query.filter(models.SlcMemberOrder.pay_status == pay_status)
    if query_begin_time:
        _query = _query.filter(models.SlcMemberOrder.create_time >= query_begin_time + ' 00:00:00')
    if query_end_time:
        _query = _query.filter(models.SlcMemberOrder.create_time <= query_end_time + ' 23:59:59')
    _query = _query.order_by(models.SlcMemberOrder.create_time.desc())

    if request.path == '/':
        return render("bus_order_list",
                      node_list=opr_nodes,
                      products=db.query(models.SlcRadProduct).filter_by(product_status=0),
                      page_data=get_page_data(_query), **request.params)
    elif request.path == '/export':
        data = Dataset()
        data.append((
            u'区域', u"用户姓名", u'上网账号', u'资费', u"订购时间",
            u'订单费用', u'实缴费用', u'支付状态', u'订购渠道', u'订单描述'
        ))
        _f2y = utils.fen2yuan
        _fms = utils.fmt_second
        _pst = {0: u'未支付', 1: u'已支付', 2: u'已取消'}
        for i, _, _realname, _product_name, _node_name in _query:
            data.append((
                _node_name, _realname, i.account_number, _product_name,
                i.create_time, _f2y(i.order_fee), _f2y(i.actual_fee),
                _pst.get(i.pay_status), i.order_source, i.order_desc
            ))
        name = u"RADIUS-ORDERS-" + datetime.datetime.now().strftime("%Y%m%d-%H%M%S") + ".xls"
        return export_file(name, data)

    def post(self):
        node_id = self.get_argument('node_id',None)
        product_id = self.get_argument('product_id',None)
        pay_status = self.get_argument('pay_status',None)
        account_number = self.get_argument('account_number',None)
        query_begin_time = self.get_argument('query_begin_time',None)
        query_end_time = self.get_argument('query_end_time',None)
        opr_nodes = self.get_opr_nodes()
        _query = self.db.query(
            models.TrCustomerOrder,
            models.TrCustomer.node_id,
            models.TrCustomer.realname,
            models.TrProduct.product_name,
            models.TrNode.node_name
        ).filter(
            models.TrCustomerOrder.product_id == models.TrProduct.id,
            models.TrCustomerOrder.customer_id == models.TrCustomer.customer_id,
            models.TrNode.id == models.TrCustomer.node_id
        )
        if node_id:
            _query = _query.filter(models.TrCustomer.node_id == node_id)
        else:
            _query = _query.filter(models.TrCustomer.node_id.in_([i.id for i in opr_nodes]))
        if account_number:
            _query = _query.filter(models.TrCustomerOrder.account_number.like('%' + account_number + '%'))
        if product_id:
            _query = _query.filter(models.TrCustomerOrder.product_id == product_id)
        if pay_status:
            _query = _query.filter(models.TrCustomerOrder.pay_status == pay_status)
        if query_begin_time:
            _query = _query.filter(models.TrCustomerOrder.create_time >= query_begin_time + ' 00:00:00')
        if query_end_time:
            _query = _query.filter(models.TrCustomerOrder.create_time <= query_end_time + ' 23:59:59')
        _query = _query.order_by(models.TrCustomerOrder.create_time.desc())

        if self.request.path == '/admin/customer/order':
            return self.render("order_list.html",
                          node_list=opr_nodes,
                          products=self.db.query(models.TrProduct).filter_by(product_status=0),
                          page_data=self.get_page_data(_query), **self.get_params())
        elif self.request.path == '/admin/customer/order/export':
            data = Dataset()
            data.append((
                u'区域', u"用户姓名", u'上网账号', u'资费', u"订购时间",
                u'订单费用', u'实缴费用', u'支付状态', u'订购渠道', u'订单描述'
            ))
            _f2y = utils.fen2yuan
            _fms = utils.fmt_second
            _pst = {0: u'未支付', 1: u'已支付', 2: u'已取消'}
            for i, _, _realname, _product_name, _node_name in _query:
                data.append((
                    _node_name, _realname, i.account_number, _product_name,
                    i.create_time, _f2y(i.order_fee), _f2y(i.actual_fee),
                    _pst.get(i.pay_status), i.order_source, i.order_desc
                ))
            name = u"RADIUS-ORDERS-" + datetime.datetime.now().strftime("%Y%m%d-%H%M%S") + ".xls"
            return self.export_file(name, data)

def read_database():
    with open(COMO_BATTERY_FILE, 'r') as como:
        data = Dataset(headers=['time', 'capacity', 'cycles'])
        # http://stackoverflow.com/questions/10206905/
        # how-to-convert-json-string-to-dictionary-and-save-order-in-keys
        data.dict = json.loads(
            zlib.decompress(como.read()),
            object_pairs_hook=collections.OrderedDict)
        return data

def acceptlog_query(db):
    node_id = request.params.get('node_id')
    accept_type = request.params.get('accept_type')
    account_number = request.params.get('account_number')
    operator_name = request.params.get('operator_name')
    query_begin_time = request.params.get('query_begin_time')
    query_end_time = request.params.get('query_end_time')
    _query = db.query(
        models.SlcRadAcceptLog.id,
        models.SlcRadAcceptLog.accept_type,
        models.SlcRadAcceptLog.accept_time,
        models.SlcRadAcceptLog.accept_desc,
        models.SlcRadAcceptLog.operator_name,
        models.SlcRadAcceptLog.accept_source,
        models.SlcRadAcceptLog.account_number,
        models.SlcMember.node_id,
        models.SlcNode.node_name
    ).filter(
            models.SlcRadAcceptLog.account_number == models.SlcRadAccount.account_number,
            models.SlcMember.member_id == models.SlcRadAccount.member_id,
            models.SlcNode.id == models.SlcMember.node_id
    )
    if operator_name:
        _query = _query.filter(models.SlcRadAcceptLog.operator_name == operator_name)
    if node_id:
        _query = _query.filter(models.SlcMember.node_id == node_id)
    if account_number:
        _query = _query.filter(models.SlcRadAcceptLog.account_number.like('%'+account_number+'%'))
    if accept_type:
        _query = _query.filter(models.SlcRadAcceptLog.accept_type == accept_type)
    if query_begin_time:
        _query = _query.filter(models.SlcRadAcceptLog.accept_time >= query_begin_time+' 00:00:00')
    if query_end_time:
        _query = _query.filter(models.SlcRadAcceptLog.accept_time <= query_end_time+' 23:59:59')
    _query = _query.order_by(models.SlcRadAcceptLog.accept_time.desc())
    type_map = {'open':u'开户','pause':u'停机','resume':u'复机','cancel':u'销户','next':u'续费','charge':u'充值'}
    if request.path == '/acceptlog':
        return render(
            "bus_acceptlog_list",
            page_data = get_page_data(_query),
            node_list=db.query(models.SlcNode),
            type_map = type_map,
            get_orderid = lambda aid:db.query(models.SlcMemberOrder.order_id).filter_by(accept_id=aid).scalar(),
            **request.params
        )
    elif request.path == '/acceptlog/export':
        data = Dataset()
        data.append((u'区域',u'上网账号',u'受理类型',u'受理时间',u'受理渠道',u'操作员',u'受理描述'))
        for i in _query:
            data.append((
                i.node_name, i.account_number, type_map.get(i.accept_type),
                i.accept_time,i.accept_source,i.operator_name,i.accept_desc
            ))
        name = u"RADIUS-ACCEPTLOG-" + datetime.datetime.now().strftime("%Y%m%d-%H%M%S") + ".xls"
        with open(u'./static/xls/%s' % name, 'wb') as f:
            f.write(data.xls)
        return static_file(name, root='./static/xls',download=True)

    def test_in_the_news(self):
        from tablib import Dataset, Databook
        data = Dataset()

        mention = Mention("http://www.lvbusinesspress.com/articles/2011/11/28/news/iq_49033368.txt")
        mention.append(data)

        mention = Mention("http://www.lasvegasgleaner.com/las_vegas_gleaner/2011/12/unwitting-local-tools-of-corporate-overlords-hire-a-lawyer.html - NPRI in the News")
        mention.append(data)

        self.assertEqual(len(data), 2)
        self.assertEqual(len(data.filter(['in-the-news'])), 1)

def fetch_timetable(browser, link):
    # Utility method to return a nice Dataset from a timetable url
    if debug:
        puts('Fetching timetable from %s' % link)
    response = browser.follow_link(link)
    soup = BeautifulSoup(response.read())
    table = soup.find('table')
    timetable = []
    for row in table.findAll('tr'):
        title = None
        title_test = row.find('td')
        if title_test.find('span'):
            title = title_test.getText()
            values = []
            for col in row.findAll('td')[1:]:
                value = col.getText()
                if value == ' ':
                    value = None
                if isinstance(value, basestring) and ':' in value:
                    try:
                        time = value.strip().split(':')
                        time = datetime.time(*[int(s) for s in time])
                    except:
                        pass
                    else:
                        value = time
                values.append(value)
            timetable.append((title, values))

    while len(timetable):
        if 'TRAIN NO.' not in timetable[0][0]:
            del timetable[0]
        else:
            break

    train_nums = timetable[0]
    data = Dataset()
    data.headers = train_nums[1]
    if debug:
        puts(repr(data.headers))
    for place, times in timetable[1:]:
        if debug:
            puts(repr((place, times)))
        data.rpush(times, tags=[place.title().replace('`S', "'s")])

    #Strip out TRAIN NO. columns
    while 1:
        try:
            del data['TRAIN NO.']
        except:
            break

    return data

def card_list(db):   
    product_id = request.params.get('product_id')
    card_type = request.params.get('card_type') 
    card_status = request.params.get('card_status')
    batch_no = request.params.get('batch_no')
    query_begin_time = request.params.get('query_begin_time')
    query_end_time = request.params.get('query_end_time')
    
    _query = db.query(models.SlcRechargerCard)
    if product_id and card_type == '0':
        _query = _query.filter(models.SlcRechargerCard.product_id==product_id)
    if card_type:
        _query = _query.filter(models.SlcRechargerCard.card_type==card_type)
    if batch_no:
        _query = _query.filter(models.SlcRechargerCard.batch_no==batch_no)
    if card_status:
        _query = _query.filter(models.SlcRechargerCard.card_status==card_status)
    if query_begin_time:
        _query = _query.filter(models.SlcRechargerCard.create_time >= query_begin_time+' 00:00:00')
    if query_end_time:
        _query = _query.filter(models.SlcRechargerCard.create_time <= query_end_time+' 23:59:59')
    
    products = db.query(models.SlcRadProduct).filter(
        models.SlcRadProduct.product_status == 0,
        models.SlcRadProduct.product_policy.in_([0,2])
    )
    if request.path == '/list':
        print "total:",_query.count()
        return render("card_list", 
            page_data = get_page_data(_query),
            card_types = forms.card_types,
            card_states = forms.card_states,
            products = products,
            colors = {0:'',1:'class="success"',2:'class="warning"',3:'class="danger"'},
            **request.params
        )
    elif request.path == '/export':
        data = Dataset()
        data.append((
            u'批次号',u'充值卡号',u'充值卡密码',u'充值卡类型',u'状态',
            u'资费id', u'面值/售价',u"授权月数",u"过期时间",u'创建时间'
         ))
        print "total:",_query.count()
        for i in _query:
            data.append((
                i.batch_no, i.card_number, utils.decrypt(i.card_passwd),forms.card_types[i.card_type],
                forms.card_states[i.card_status],get_product_name(db,i.product_id),utils.fen2yuan(i.fee_value),
                i.months,i.expire_date,i.create_time
            ))
        name = u"RADIUS-CARD-" + datetime.datetime.now().strftime("%Y%m%d-%H%M%S") + ".xls"
        with open(u'./static/xls/%s' % name, 'wb') as f:
            f.write(data.xls)
        return static_file(name, root='./static/xls',download=True)

    def post(self):
        node_id = self.get_argument('node_id',None)
        account_number = self.get_argument('account_number',None)
        query_begin_time = self.get_argument('query_begin_time',None)
        query_end_time = self.get_argument('query_end_time',None)
        opr_nodes = self.get_opr_nodes()
        _query = self.db.query(
            models.TrBilling,
            models.TrCustomer.node_id,
            models.TrNode.node_name
        ).filter(
            models.TrBilling.account_number == models.TrAccount.account_number,
            models.TrCustomer.customer_id == models.TrAccount.customer_id,
            models.TrNode.id == models.TrCustomer.node_id
        )
        if node_id:
            _query = _query.filter(models.TrCustomer.node_id == node_id)
        else:
            _query = _query.filter(models.TrCustomer.node_id.in_(i.id for i in opr_nodes))
        if account_number:
            _query = _query.filter(models.TrBilling.account_number.like('%' + account_number + '%'))
        if query_begin_time:
            _query = _query.filter(models.TrBilling.create_time >= query_begin_time + ' 00:00:00')
        if query_end_time:
            _query = _query.filter(models.TrBilling.create_time <= query_end_time + ' 23:59:59')
        _query = _query.order_by(models.TrBilling.create_time.desc())

        if self.request.path == '/admin/customer/billing':
            return self.render("billing_list.html",
                          node_list=opr_nodes,
                          page_data=self.get_page_data(_query), **self.get_params())

        elif request.path == '/admin/customer/billing/export':
            data = Dataset()
            data.append((
                u'区域', u'上网账号', u'BAS地址', u'会话编号', u'记账开始时间', u'会话时长',
                u'已扣时长', u"已扣流量", u'应扣费用', u'实扣费用', u'剩余余额',
                u'剩余时长', u'剩余流量', u'是否扣费', u'扣费时间'
            ))
            _f2y = utils.fen2yuan
            _fms = utils.fmt_second
            _k2m = utils.kb2mb
            _s2h = utils.sec2hour
            for i, _, _node_name in _query:
                data.append((
                    _node_name, i.account_number, i.nas_addr, i.acct_session_id,
                    i.acct_start_time, _fms(i.acct_session_time), _fms(i.acct_times), _k2m(i.acct_flows),
                    _f2y(i.acct_fee), _f2y(i.actual_fee), _f2y(i.balance),
                    _s2h(i.time_length), _k2m(i.flow_length),
                    (i.is_deduct == 0 and u'否' or u'是'), i.create_time
                ))
            name = u"RADIUS-BILLING-" + datetime.datetime.now().strftime("%Y%m%d-%H%M%S") + ".xls"
            return self.export_file(name, data)