def production_export( self, **kw ):
        ws = [Item.active == 0, Item.status == STATUS_APPROVE, ]
        if kw.get( "jobNo", False ) : ws.append( Item.jobNo.op( "ilike" )( "%%%s%%" % kw["jobNo"] ) )
        if kw.get( "systemNo", False ) : ws.append( Item.systemNo.op( "ilike" )( "%%%s%%" % kw["systemNo"] ) )
        if kw.get( "desc", False ) : ws.append( Item.desc.op( "ilike" )( "%%%s%%" % kw["desc"] ) )
        if kw.get( "approve_time_from", False ) : ws.append( Item.approveTime >= kw["approve_time_from"] )
        if kw.get( "approve_time_to", False ) : ws.append( Item.approveTime <= kw["approve_time_to"] )
        result = qry( Item ).filter( and_( *ws ) ).order_by( desc( Item.createTime ) ).all()

        data = []
        for h in  qry( Item ).filter( and_( *ws ) ).order_by( desc( Item.createTime ) ):
            data.append( map( unicode, [ h.systemNo, h.jobNo, h.desc, h.createTime.strftime( "%Y/%m/%d %H:%M" ),
                                      h.showStatus(),
                                      h.approveTime.strftime( "%Y/%m/%d %H:%M" ) if h.approveTime else '',
                                     ] ) )

        try:
            v = getExcelVersion()
            if not v : raise ReportGenerationException()
            if v <= "2003" :  # version below 2003
                templatePath = os.path.join( config.get( "public_dir" ), "TEMPLATE", "CAB_PRODUCTION_REPORT_TEMPLATE.xls" )
            else :  # version above 2003
                templatePath = os.path.join( config.get( "public_dir" ), "TEMPLATE", "CAB_PRODUCTION_REPORT_TEMPLATE.xlsx" )

            tempFileName, realFileName = self._getReportFilePath( templatePath )
            sdexcel = CABItemReport( templatePath = tempFileName, destinationPath = realFileName )
            sdexcel.inputData( data )
            sdexcel.outputData()
        except:
            traceback.print_exc()
            logError()
            if sdexcel:sdexcel.clearData()
            raise ReportGenerationException()
        else:
            return serveFile( realFileName )

def shipment():
	if request.method=='POST':
		for v in request.form:
			'''v : 'shipment-743-0'
			'''
			splited=v.split('-')
			order_id=int(splited[1])
			product_id=int(splited[2])
			order=Order.query.filter_by(id=order_id).one()
			#order.products[key].status_id=int(request.form[v])
			for p in order.products:
				if p.product.id==product_id:
					if p.status_id is not int(request.form[v]):
						p.status_id=int(request.form[v])
						db_session.add(order)
						db_session.commit()

		q_str=request.args.get('status','')
		if q_str:
			url='/shipment/?status=' + q_str
		else:
			url='/shipment/'
		return redirect(url)
	elif request.args.get('status')=='accepted':
		orders=Order.query.options(joinedload(Order.products)).filter(Order.products.any(status_id=1)) \
			.order_by(desc(Order.created_at)).all()
	elif request.args.get('status')=='prepared':
		orders=Order.query.options(joinedload(Order.products)).filter(Order.products.any(status_id=2)) \
			.order_by(desc(Order.created_at)).all()
	else:
		#orders=Order.query.order_by(desc(Order.created_at)).all()
		orders=Order.query.options(joinedload(Order.products)).order_by(desc(Order.created_at)).all()

	statuses=Status.query.order_by(Status.id).all()
	return render_template('admin_shipment.html',orders=orders,statuses=statuses)

    def get_entries(self, category_id=None, category_name=None, date_from=None, date_to=None, limit=None):
        if not self.entries:
            self.entries = IncomeTable.query\
            .filter(IncomeTable.user == self.user_id)\
            .join(IncomeCategoriesTable)\
            .add_columns(IncomeCategoriesTable.name, IncomeCategoriesTable.slug)\
            .order_by(desc(IncomeTable.date)).order_by(desc(IncomeTable.id))

        # provided category id
        if category_id:
            self.entries = self.entries.filter(IncomeTable.category == category_id)

        # provided category name
        if category_name:
            self.entries = self.entries.filter(IncomeCategoriesTable.name == category_name)

        # provided date range
        if date_from and date_to:
            self.entries = self.entries.filter(IncomeTable.date >= date_from).filter(IncomeTable.date <= date_to)

        # provided count
        if limit:
            self.entries = self.entries.limit(limit)

        return self.entries

 def manage(self,type,id):
     if type == 'article':
         c.articles = Session.query(Article).order_by(desc(Article.pub_date)).all()
         return render('/admin/manage_news.html')
     elif type == 'comments':
         c.article = Session.query(Article).filter_by(id=id).order_by(desc(Article.pub_date)).one()
         return render('/admin/manage_comments.html')

    def __get_result(model, flight_field, **kw):
        subq = (
            DBSession.query(
                getattr(Flight, flight_field),
                func.count("*").label("count"),
                func.sum(Flight.index_score).label("total"),
            )
            .group_by(getattr(Flight, flight_field))
            .outerjoin(Flight.model)
        )

        if "year" in kw:
            try:
                year = int(kw["year"])
            except:
                raise HTTPBadRequest

            year_start = date(year, 1, 1)
            year_end = date(year, 12, 31)
            subq = subq.filter(Flight.date_local >= year_start).filter(Flight.date_local <= year_end)

        subq = subq.subquery()

        result = DBSession.query(
            model, subq.c.count, subq.c.total, over(func.rank(), order_by=desc("total")).label("rank")
        ).join((subq, getattr(subq.c, flight_field) == model.id))

        result = result.order_by(desc("total"))
        return result

    def get_top_scorers(self):
        """Top players by score. Shared by all renderers."""
        cutoff = self.now - timedelta(days=self.lifetime)
        cutoff = self.now - timedelta(days=120)

        top_scorers_q = DBSession.query(
            fg.row_number().over(order_by=expr.desc(func.sum(PlayerGameStat.score))).label("rank"),
            Player.player_id, Player.nick, func.sum(PlayerGameStat.score).label("total_score"))\
            .filter(Player.player_id == PlayerGameStat.player_id)\
            .filter(Game.game_id == PlayerGameStat.game_id)\
            .filter(Game.map_id == self.map_id)\
            .filter(Player.player_id > 2)\
            .filter(PlayerGameStat.create_dt > cutoff)\
            .order_by(expr.desc(func.sum(PlayerGameStat.score)))\
            .group_by(Player.nick)\
            .group_by(Player.player_id)

        if self.last:
            top_scorers_q = top_scorers_q.offset(self.last)

        if self.limit:
            top_scorers_q = top_scorers_q.limit(self.limit)

        top_scorers = top_scorers_q.all()

        return top_scorers

    def get_notifications(count, to_user_id, get_older=False, than_id=None):
        notification_query = g.db().query(Notification).filter(Notification.to_user_id == to_user_id)
        if than_id:
            if get_older:
                notifications = notification_query.filter(Notification.id < than_id).order_by(
                    expression.desc(Notification.cr_tm)).limit(count + 1).all()
                there_is_more = ['there_is_older', len(notifications) > count]
                notifications = notifications[0:count]
                # notifications.reverse()
            else:
                notifications = notification_query.filter(Notification.id > than_id).order_by(
                    expression.asc(Notification.cr_tm)).limit(count + 1).all()
                there_is_more = ['there_is_newer', len(notifications) > count]
                notifications = notifications[0:count]

        else:
            notifications = notification_query.order_by(expression.desc(Notification.cr_tm)).limit(
                count + 1).all()
            there_is_more = ['there_is_older', len(notifications) > count]
            notifications = notifications[0:count]
            # notifications.reverse()

        return {
            there_is_more[0]: there_is_more[1],
            'items': notifications
        }

    def get_messages(self, count, get_older=False, than_id=None):
        messages_filter = (Message.contact_id == self.id)
        messages_query = g.db().query(Message)
        if than_id:
            if get_older:
                messages = messages_query.filter(and_(messages_filter, Message.id < than_id)).order_by(
                    expression.desc(Message.cr_tm)).limit(count + 1).all()
                there_is_more = ['there_is_older', len(messages) > count]
                messages = messages[0:count]
                # messages.reverse()

            else:
                messages = messages_query.filter(and_(messages_filter, Message.id > than_id)).order_by(
                    expression.asc(Message.cr_tm)).limit(count + 1).all()
                there_is_more = ['there_is_newer', len(messages) > count]
                messages = messages[0:count]

        else:
            messages = messages_query.filter(messages_filter).order_by(expression.desc(Message.cr_tm)).limit(
                count + 1).all()
            there_is_more = ['there_is_older', len(messages) > count]
            messages = messages[0:count]
            # messages.reverse()

        return {
            there_is_more[0]: there_is_more[1],
            'items': messages
        }

 def get_all_datatypes_in_burst(self, burst_id):
     """
     Get all dataTypes in burst, order by their creation, desc.
     
     :param burst_id BurstConfiguration Identifier.
     :returns: list dataType GIDs or empty list.
     """
     try:
         groups = (
             self.session.query(model.DataTypeGroup)
             .join(model.Operation, model.DataTypeGroup.fk_from_operation == model.Operation.id)
             .join(model.WorkflowStep, model.Operation.id == model.WorkflowStep.fk_operation)
             .join(model.Workflow)
             .filter(model.Workflow.fk_burst == burst_id)
             .order_by(desc(model.DataTypeGroup.id))
             .all()
         )
         result = (
             self.session.query(model.DataType)
             .filter(model.DataType.fk_parent_burst == burst_id)
             .filter(model.DataType.fk_datatype_group == None)
             .filter(model.DataType.type != self.EXCEPTION_DATATYPE_GROUP)
             .order_by(desc(model.DataType.id))
             .all()
         )
         result.extend(groups)
     except SQLAlchemyError, exc:
         self.logger.exception(exc)
         result = []

def parse_custom_query(session, args, group, order):
    table = Ticket.__table__
    cols = table.columns
    if group not in cols:
        group = 'status'
    if order not in cols:
        order = 'priority'
    groupcol = cols[group]
    ordercol = cols[order]
    preds = [cols[key].in_(values)
             for (key, values) in args.iterlists()
             if key in cols]
    q = session.query(Ticket.id.label('ticket'),
                      Ticket.summary,
                      Ticket.owner,
                      Ticket.type,
                      Ticket.priority,
                      Ticket.component,
                      Ticket.time.label('created'),
                      Enum.value.label('__color__'),
                      groupcol.label('__group__')
                      ).filter(and_(Enum.type == 'priority',
                                    Ticket.priority == Enum.name,
                                    *preds))
    return q.order_by([
        desc(groupcol) if args.get('groupdesc') == '1' else groupcol,
        desc(ordercol) if args.get('desc') == '1' else ordercol,
        ])

 def execute(self):
     try:
         limit = int(self.get_argument('limit', default=15))
         page = int(self.get_argument('page', default=0))
         """ for Exception test """
         #limit = self.get_argument('limit')
         #page = self.get_argument('page')
     except Exception as e:
         return self.handle_request_exception(e)
     else:
         data = self.emc.get(self.emc.get_key_format() % (page, limit))
         if not data:
             titles = session.query(EVJPTitle).\
                         order_by(desc(EVJPTitle.released_date)).\
                         order_by(desc(EVJPTitle.ev_title_id)).\
                         slice(page * limit, page * limit + limit).\
                         all()
             
             try:            
                 data = self.to_json(titles, limit, page)
             except RuntimeError:
                 data = self.to_json([], limit, page)
             else:
                 self.emc.set(self.emc.get_key_format() % (page, limit), data)
             
         self.start_response(self.get_status(), self.get_response_headers())
         return iter([data])

    def get_entries(self, category_id=None, category_name=None, date_from=None, date_to=None, limit=None):
        if not self.entries:
            self.entries = ExpensesTable.query\
            .filter(ExpensesTable.user == self.user_id)\
            .join(ExpenseCategoriesTable)\
            .add_columns(ExpenseCategoriesTable.name, ExpenseCategoriesTable.slug)\
            .order_by(desc(ExpensesTable.date)).order_by(desc(ExpensesTable.id))\
            .outerjoin(ExpensesToLoansTable)\
            .outerjoin((UsersTable, (UsersTable.id == ExpensesToLoansTable.shared_with)))\
            .add_columns(UsersTable.name, UsersTable.slug)

        # provided category id
        if category_id:
            self.entries = self.entries.filter(ExpensesTable.category == category_id)

        # provided category name
        if category_name:
            self.entries = self.entries.filter(ExpenseCategoriesTable.name == category_name)

        # provided date range
        if date_from and date_to:
            self.entries = self.entries.filter(ExpensesTable.date >= date_from).filter(ExpensesTable.date <= date_to)

        # provided count
        if limit:
            self.entries = self.entries.limit(limit)

        return self.entries

def dashboard(request):
    player = request.player
    db = request.db

    wins, losses, ratio = player.wins_losses()

    # Upcoming games
    upcoming_games = []
    results = db.query(Game).filter(Game.time > datetime.now()).order_by(desc(Game.time)).limit(4)
    for game in results:
        game.hour, game.min, game.m = game.get_printed_time()
        upcoming_games.append(game)

    # Previous Games
    previous_games = []
    results = db.query(Game).filter(Game.time < datetime.now()).order_by(desc(Game.time)).limit(4)
    for game in results:
        game.hour, game.min, game.m = game.get_printed_time()
        previous_games.append(game)

    leaders = get_leaderboard(request)

    return {
        'wins': wins,
        'losses': losses,
        'ratio': ratio,
        'upcoming_games': upcoming_games,
        'previous_games': previous_games,
        'leaders': leaders
    }

    def top_maps(self):
        """Returns the raw data shared by all renderers."""
        try:
            top_maps_q = DBSession.query(
                fg.row_number().over(order_by=expr.desc(func.count())).label("rank"),
                Game.map_id, Map.name, func.count().label("times_played"))\
                .filter(Map.map_id == Game.map_id)\
                .filter(Game.server_id == self.server_id)\
                .filter(Game.create_dt > (self.now - timedelta(days=self.lifetime)))\
                .group_by(Game.map_id)\
                .group_by(Map.name) \
                .order_by(expr.desc(func.count()))

            if self.last:
                top_maps_q = top_maps_q.offset(self.last)

            if self.limit:
                top_maps_q = top_maps_q.limit(self.limit)

            top_maps = top_maps_q.all()
        except Exception as e:
            log.debug(e)
            raise HTTPNotFound

        return top_maps

    def get(self):

        # TODO:
        TOTAL_INSTANCE = self.db.query(Instance.id).count()
        TOTAL_APPLIANCE = self.db.query(Appliance.id).count()
        TOTAL_USER = self.db.query(User.id).count()
        TOTAL_JOB = self.db.query(Job.id).count()
        TOTAL_NODE = self.db.query(Node.id).count()

        new_users = self.db.query(User).order_by(
            desc(User.id) ).limit(10)
        new_jobs = self.db.query(Job).order_by(
            desc(Job.id) ).limit(10)

        ud = self._get_data()

        d = { 'title': self.trans(_('Admin Console')),
              'human_size': human_size,
              'TOTAL_APPLIANCE': TOTAL_APPLIANCE,
              'TOTAL_INSTANCE': TOTAL_INSTANCE,
              'TOTAL_USER': TOTAL_USER,
              'TOTAL_JOB': TOTAL_JOB,
              'TOTAL_NODE': TOTAL_NODE,
              'NEW_USER_LIST': new_users,
              'NEW_JOB_LIST': new_jobs,

              'TOTAL_MEMORY': ud['TOTAL_MEMORY'],
              'USED_MEMORY': ud['USED_MEMORY'],
              'TOTAL_CPU': ud['TOTAL_CPU'],
              'USED_CPU': ud['USED_CPU'] }


        d.update( self._get_data() )

        self.render('admin/index.html', **d)

def top_last_month(cls, key, ids=None, num=None):
    """Aggregate a listing of the top items (by pageviews) last month.

    We use the last month because it's guaranteed to be fully computed and
    therefore will be more meaningful.

    """

    cur_month = datetime.date.today().replace(day=1)
    last_month = decrement_month(cur_month)

    q = (
        Session.query(cls)
        .filter(cls.date == last_month)
        .filter(cls.interval == "month")
        .order_by(desc(cls.date), desc(cls.pageview_count))
    )

    if ids:
        q = q.filter(getattr(cls, key).in_(ids))
    else:
        num = num or 55
        q = q.limit(num)

    return [(getattr(r, key), (r.unique_count, r.pageview_count)) for r in q.all()]

    def top_players(self):
        """Top players on this server by total playing time."""
        try:
            top_players_q = DBSession.query(
                fg.row_number().over(
                    order_by=expr.desc(func.sum(PlayerGameStat.alivetime))).label("rank"),
                Player.player_id, Player.nick,
                func.sum(PlayerGameStat.alivetime).label("alivetime"))\
                .filter(Player.player_id == PlayerGameStat.player_id)\
                .filter(Game.game_id == PlayerGameStat.game_id)\
                .filter(Game.server_id == self.server_id)\
                .filter(Player.player_id > 2)\
                .filter(PlayerGameStat.create_dt > (self.now - timedelta(days=self.lifetime)))\
                .order_by(expr.desc(func.sum(PlayerGameStat.alivetime)))\
                .group_by(Player.nick)\
                .group_by(Player.player_id)

            if self.last:
                top_players_q = top_players_q.offset(self.last)

            if self.limit:
                top_players_q = top_players_q.limit(self.limit)

            top_players = top_players_q.all()

        except Exception as e:
            log.debug(e)
            raise HTTPNotFound

        return top_players

def _get_result(model, flight_field, year=None):
    subq = db.session \
        .query(getattr(Flight, flight_field),
               func.count('*').label('count'),
               func.sum(Flight.index_score).label('total')) \
        .group_by(getattr(Flight, flight_field)) \
        .outerjoin(Flight.model)

    if isinstance(year, int):
        year_start = date(year, 1, 1)
        year_end = date(year, 12, 31)
        subq = subq.filter(Flight.date_local >= year_start) \
                   .filter(Flight.date_local <= year_end)

    subq = subq.subquery()

    result = db.session \
        .query(model, subq.c.count, subq.c.total,
               over(func.rank(), order_by=desc('total')).label('rank')) \
        .join((subq, getattr(subq.c, flight_field) == model.id))

    if model == User:
        result = result.options(subqueryload(model.club))

    result = result.order_by(desc('total'))
    return result

    def __get_result(model, flight_field, **kw):
        subq = DBSession \
            .query(getattr(Flight, flight_field),
                   func.count('*').label('count'),
                   func.sum(Flight.index_score).label('total')) \
            .group_by(getattr(Flight, flight_field)) \
            .outerjoin(Flight.model)

        if 'year' in kw:
            try:
                year = int(kw['year'])
            except:
                raise HTTPBadRequest

            year_start = date(year, 1, 1)
            year_end = date(year, 12, 31)
            subq = subq.filter(Flight.date_local >= year_start) \
                       .filter(Flight.date_local <= year_end)

        subq = subq.subquery()

        result = DBSession \
            .query(model, subq.c.count, subq.c.total,
                   over(func.rank(), order_by=desc('total')).label('rank')) \
            .join((subq, getattr(subq.c, flight_field) == model.id))

        result = result.order_by(desc('total'))
        return result

    def mostFrequentVariableAndValue(self, variableNameList):
        """
    :type variableNameList: list(str)
"""

        subQueryList = []
        
        if len(variableNameList) == 0:
            raise EmptyVariableNameListError()

        with closing(self._sessionMaker()) as session:
            # For each variable, retrieve all possible values and their occurrence count.
            for variableName in variableNameList:
                variableNameColumn = literal(variableName).label(self._VARIABLE_NAME_KEY)
                variableValueColumn = getattr(SQLModsecurityAuditEntryMessage, variableName).label(self._VARIABLE_VALUE_KEY)
                variableValueCountColumn = count().label(self._VARIABLE_VALUE_COUNT_KEY)
                
                # Subquery of each variable.
                subQuery = self._makeQuery(session, [variableNameColumn, variableValueColumn, variableValueCountColumn])
                subQuery = subQuery.group_by(self._VARIABLE_NAME_KEY, self._VARIABLE_VALUE_KEY) 
                subQueryList.append(subQuery)
    
            # Merging all subqueries and sorting by reverse count...
            query = union(*subQueryList).order_by(desc(self._VARIABLE_VALUE_COUNT_KEY)).limit(1)
            query = query.order_by(desc(self._VARIABLE_VALUE_COUNT_KEY)).limit(1)
            
            # ... then picking the first one.
            item = session.execute(query).fetchone()
            
            if item is not None:
                return {str(item.variableName): item.variableValue}
            else:
                return None