__init__.py

import pytz    
import transaction
import colander
from datetime import datetime, date
from email.utils import parseaddr
from pyramid.view import view_config
from pyramid.httpexceptions import (HTTPFound, HTTPForbidden, HTTPNotFound)
from pyramid.security import (remember, forget, authenticated_userid,)
from deform import (Form, ValidationFailure, widget,)
from ..tools import dmy, date_from_str, int_to_roman
from sqlalchemy import func, and_

from ..models import (DBSession, User,)
from ..models.isipkd import(
      ARSspd, ARInvoice, Unit, Rekening
      )
from pyramid.view import notfound_view_config



###############################################################################
# Not Found
###############################################################################

#@view_config(context=HTTPNotFound, renderer='templates/login.pt')
#def not_found(self, request):
#    request.response.status = 404
#    print request
#    return dict(request=request)


###############################################################################
# Not Found
###############################################################################

#@view_config(route_name='forbidden', renderer='templates/forbidden.pt')
#def not_found(self, request):
#    #request.response.status = 404
#    return dict(request=request)
    
########
# Home #
########
@view_config(route_name='home', renderer='templates/home.pt')
def view_home(request):
    statusadmin = False
    if hasattr(request.user,'groups'):
        for g in request.user.groups:
            if g.group_name=='admin':
                statusadmin = True
                break
    if statusadmin == True:
        if 'date' in request.params and request.params['date']:
            now = date_from_str(request.params['date'].strip())
        else:
            now = date.today()
        dates = dict(
                    year = now.year,
                    month = now.month,
                    week = int(now.strftime('%W')),
                    day = now,
                )
        data_dashboard = dict(
                        tabular = dict(
                            tahun = dict(q=0,n=0),
                            bulan = dict(q=0,n=0),
                            minggu = dict(q=0,n=0),
                            hari = dict(q=0,n=0),
                        ), 
                        chart = dict(
                            q = [0,0,0,0,0,0,0,0,0,0,0,0],
                            n = [0,0,0,0,0,0,0,0,0,0,0,0],
                        ),
                        rek = [], ## Rekening Header
                        rekdet = [], ## Rekening Detail
                        sopd = [], ## Ranking Penerimaan
                        sopd10 = [], ## 10 Ranking Penerimaan
                        sopd4 = []
                    )
        #List Untuk Header
        opd_ls = Unit.query().filter(Unit.level_id==3).order_by(Unit.kode).all()
        
        #List Rekening Lv 3
        reks41 = Rekening.query().filter(Rekening.level_id==3, Rekening.kode.ilike('4.1.%')).order_by(Rekening.kode).all()
        no3=0
        for r in reks41:
            no3+=1
            no_roman = int_to_roman(no3)
            data_dashboard['rek'].append(dict(no=no_roman,kode=r.kode.strip(), nama=r.nama.strip(), target=0, realisasi=0, persen=0))
            
        #List Rekening Lv 4
        reks41det = Rekening.query().filter(Rekening.level_id.between(3,4), Rekening.kode.ilike('4.1.%')).order_by(Rekening.kode).all()
        for r in reks41det:
            if r.level_id==3:
                no4=0
            elif r.level_id==4:
                no4+=1
                data_dashboard['rekdet'].append(dict(no=no4, kode=r.kode.strip(), level=r.level_id, nama=r.nama.strip(), target=0, realisasi=0, persen=0))
                
        #QUERY PAYMENT
        payments = DBSession.query(
                        ARSspd.tgl_bayar.label('tanggal'),
                        ARSspd.bayar.label('jumlah'),
                        ARInvoice.rek_kode.label('rek_kode'),
                        Unit.kode.label('kode'),
                        Unit.nama.label('nama'),
                ).join(ARInvoice, ARInvoice.id == ARSspd.arinvoice_id).\
                  join(Unit, func.trim(Unit.kode) == func.trim(ARInvoice.unit_kode)).\
                filter(ARSspd.tahun_id==dates['year']).order_by(ARInvoice.unit_kode)
        payments = payments.all()
        for p in payments:
            ## JIKA ADA FILTER DEPARTEMEN DI HEADER
            if 'unit' in request.params and request.params['unit']:
                if p.kode.strip().startswith(request.params['unit'].strip()):
                    data_dashboard['tabular']['tahun']['q'] += 1
                    data_dashboard['tabular']['tahun']['n'] += int(p.jumlah)
                    if p.tanggal.month == dates['month']:
                        data_dashboard['tabular']['bulan']['q'] += 1
                        data_dashboard['tabular']['bulan']['n'] += int(p.jumlah)
                    if int(p.tanggal.strftime('%W')) == dates['week']:
                        data_dashboard['tabular']['minggu']['q'] += 1
                        data_dashboard['tabular']['minggu']['n'] += int(p.jumlah)
                    if p.tanggal.date() == dates['day']:
                        data_dashboard['tabular']['hari']['q'] += 1
                        data_dashboard['tabular']['hari']['n'] += int(p.jumlah)
                    for x in range(12):
                        if p.tanggal.month == x+1:
                            data_dashboard['chart']['q'][x] += int(1)
                            data_dashboard['chart']['n'][x] += int(p.jumlah)
                    ## Rekening
                    for r in data_dashboard['rek']:
                        if p.rek_kode.strip().startswith(r['kode']):
                            r['realisasi'] += int(p.jumlah)
                    for r in data_dashboard['rekdet']:
                        if p.rek_kode.strip().startswith(r['kode']):
                            r['realisasi'] += int(p.jumlah)
                            
            ## JIKA TIDAK ADA FILTER DEPARTEMEN HEADER
            elif not 'unit' in request.params or not request.params['unit']:
                data_dashboard['tabular']['tahun']['q'] += 1
                data_dashboard['tabular']['tahun']['n'] += int(p.jumlah)
                if p.tanggal.month == dates['month']:
                    data_dashboard['tabular']['bulan']['q'] += 1
                    data_dashboard['tabular']['bulan']['n'] += int(p.jumlah)
                if int(p.tanggal.strftime('%W')) == dates['week']:
                    data_dashboard['tabular']['minggu']['q'] += 1
                    data_dashboard['tabular']['minggu']['n'] += int(p.jumlah)
                if p.tanggal.date() == dates['day']:
                    data_dashboard['tabular']['hari']['q'] += 1
                    data_dashboard['tabular']['hari']['n'] += int(p.jumlah)
                for x in range(12):
                    if p.tanggal.month == x+1:
                        data_dashboard['chart']['q'][x] += int(1)
                        data_dashboard['chart']['n'][x] += int(p.jumlah)
                ## Rekening
                for r in data_dashboard['rek']:
                    if p.rek_kode.strip().startswith(r['kode']):
                        r['realisasi'] += int(p.jumlah)
                for r in data_dashboard['rekdet']:
                    if p.rek_kode.strip().startswith(r['kode']):
                        r['realisasi'] += int(p.jumlah)
                    
        #QUERY INVOICE
        invoices = DBSession.query(
                        ARInvoice.tgl_tetap.label('tanggal'),
                        ARInvoice.jumlah.label('jumlah'),
                        ARInvoice.rek_kode.label('rek_kode'),
                        Unit.kode.label('kode'),
                        Unit.nama.label('nama'),
                ).join(Unit, func.trim(Unit.kode) == func.trim(ARInvoice.unit_kode)).\
                filter(ARInvoice.tahun_id==dates['year']).order_by(ARInvoice.unit_kode)
        invoices = invoices.all()
        for i in invoices:
            ## JIKA ADA FILTER DEPARTEMEN DI HEADER
            if 'unit' in request.params and request.params['unit']:
                if i.kode.strip().startswith(request.params['unit'].strip()):
                    ## REKENING
                    for r in data_dashboard['rek']:
                        if i.rek_kode.strip().startswith(r['kode']):
                            r['target'] += int(i.jumlah)
                    for r in data_dashboard['rekdet']:
                        if i.rek_kode.strip().startswith(r['kode']):
                            r['target'] += int(i.jumlah)
            ## JIKA TIDAK ADA FILTER DEPARTEMEN HEADER
            elif not 'unit' in request.params or not request.params['unit']:
                for r in data_dashboard['rek']:
                    if i.rek_kode.strip().startswith(r['kode']):
                        r['target'] += int(i.jumlah)
                for r in data_dashboard['rekdet']:
                    if i.rek_kode.strip().startswith(r['kode']):
                        r['target'] += int(i.jumlah)
        
        ##REKENING##
        for r in data_dashboard['rek']:
            r['persen'] = r['realisasi'] and str(round((float(r['realisasi'])/float(r['target']))*100,2)).replace('.0','') or '0'
        for r in data_dashboard['rekdet']:
            r['persen'] = r['realisasi'] and str(round((float(r['realisasi'])/float(r['target']))*100,2)).replace('.0','') or '0'

        #Unit
        opds = Unit.query().filter(Unit.level_id.between(3,4)).order_by(Unit.kode).all()
        for op in opds:
            if op.level_id==3:
                data_dashboard['sopd'].append(dict(peringkat=0,kode=op.kode.strip(), level=op.level_id, unit=op.nama.strip(), nominal=0))
            if op.level_id==4:
                data_dashboard['sopd4'].append(dict(kode=op.kode.strip(), level=op.level_id, unit=op.nama.strip(), nominal=0))
            
        ##RANKING##
        tanggal_berjalan = dates['day']
        tahun_berjalan = tanggal_berjalan.year
        opd = DBSession.query(
                        func.sum(ARSspd.bayar).label('jumlah'),
                        func.max(func.trim(Unit.kode)).label('kode'),
                        func.max(func.trim(Unit.nama)).label('nama'),
                        func.max(Unit.level_id).label('level_id'),
                ).join(ARInvoice, ARInvoice.id == ARSspd.arinvoice_id).\
                  join(Unit, func.trim(Unit.kode) == func.trim(ARInvoice.unit_kode)).\
                filter(ARSspd.tahun_id==tahun_berjalan,
                       Unit.level_id>=3).\
                group_by(func.trim(Unit.kode)).all()
        for sopd in opd:
            if sopd.level_id>=3:
                for unit3 in data_dashboard['sopd']:
                    if sopd.kode.startswith(unit3['kode']):
                        unit3['nominal']+=sopd.jumlah
            if sopd.level_id>=4:
                for unit4 in data_dashboard['sopd4']:
                    if sopd.kode.startswith(unit4['kode']):
                        unit4['nominal']+=sopd.jumlah
                        
        ##SORT
        data_dashboard['sopd'] = sorted(data_dashboard['sopd'], key = lambda i: (i['nominal']), reverse=True)
        i=-1
        for opd in data_dashboard['sopd']:
            i+=1
            if i < 10:
                data_dashboard['sopd10'].append(dict(peringkat=i+1, kode=opd['kode'], unit=opd['unit'], nominal=opd['nominal']))
            opd['peringkat'] = i+1
            

        return dict(project='esipkd', dates=dates, data = data_dashboard, opds=opd_ls, statusadmin=statusadmin)
    else:
        return dict(project='esipkd', statusadmin=statusadmin)


#########
# Login #
#########
class Login(colander.Schema):
    username = colander.SchemaNode(colander.String(),
                                   title="Nama Pengguna")
    password = colander.SchemaNode(colander.String(),
                                   title="Kata Sandi",
                                   widget=widget.PasswordWidget())


# http://deformdemo.repoze.org/interfield/
def login_validator(form, value):
    user = form.user
    if not user:
        raise colander.Invalid(form, 'Login gagal')
    if not user.user_password:
        raise colander.Invalid(form, 'Login gagal')        
    if not user.check_password(value['password']):
        raise colander.Invalid(form, 'Login gagal')

def get_login_headers(request, user):
    headers = remember(request, user.email)
    #headers = remember(request, user.id)
    #user.last_login_date = datetime.now()
    DBSession.add(user)
    DBSession.flush()
    transaction.commit()
    return headers

@view_config(context=HTTPForbidden, renderer='templates/login.pt')
@view_config(route_name='login', renderer='templates/login.pt')
def view_login(request):
    if authenticated_userid(request):
        return HTTPFound(location=request.route_url('forbidden'))
    
    login_url = request.resource_url(request.context,'login')
    referrer = request.url
    if referrer == login_url:
        referrer = '/'
            
    came_from = request.params.get('came_from', referrer)
    schema = Login(validator=login_validator)
    form = Form(schema, buttons=('login',))
    if 'login' in request.POST: 
        controls = request.POST.items()
        identity = request.POST.get('username')
        user = schema.user = User.get_by_identity(identity)
        try:
            c = form.validate(controls)
        except ValidationFailure, e:
            request.session['login failed'] = e.render()
            return HTTPFound(location=came_from) #location.request.route_url('login')
                    
            
        headers = get_login_headers(request, user)
        return HTTPFound(location=came_from, # request.route_url(came_from),
                          headers=headers)
    elif 'login failed' in request.session:
        r = dict(form=request.session['login failed'])
        del request.session['login failed']
        return r
        
    return dict(form=form.render())

@view_config(route_name='logout')
def view_logout(request):
    headers = forget(request)
    return HTTPFound(location = request.route_url('home'),
                      headers = headers)    


###################
# Change password #
###################
class Password(colander.Schema):
    old_password = colander.SchemaNode(colander.String(),
                                       title="Kata Sandi Lama",
                                       widget=widget.PasswordWidget())
    new_password = colander.SchemaNode(colander.String(),
                                       title="Kata Sandi Baru",
                                       widget=widget.PasswordWidget())
    retype_password = colander.SchemaNode(colander.String(),
                                       title="Ketik Ulang Kata Sandi",
                                       widget=widget.PasswordWidget())

                                          
def password_validator(form, value):
    if not form.request.user.check_password(value['old_password']):
        raise colander.Invalid(form, 'Invalid old password.')
    if value['new_password'] != value['retype_password']:
        raise colander.Invalid(form, 'Retype mismatch.')
                                          

@view_config(route_name='password', renderer='templates/password.pt',
             permission='edit')
def view_password(request):
    schema = Password(validator=password_validator)
    form = Form(schema, buttons=('simpan','cancel'))
    if request.POST:
        if 'simpan' in request.POST:
            schema.request = request
            controls = request.POST.items()
            try:
                c = form.validate(controls)
            except ValidationFailure, e:
                request.session['invalid password'] = e.render()
                return HTTPFound(location=request.route_url('password'))
            user = request.user
            user.password = c['new_password']
            DBSession.add(user)
            DBSession.flush()
            transaction.commit()
            request.session.flash('Your password has been changed.')
        return HTTPFound(location=request.route_url('home'))
    elif 'invalid password' in request.session:
        r = dict(form=request.session['invalid password'])
        del request.session['invalid password']
        return r
    return dict(form=form.render())