import os
import uuid
from osipkd.tools import row2dict, xls_reader
from datetime import datetime
from sqlalchemy import not_, func
from sqlalchemy.sql.expression import and_
from ziggurat_foundations.models import groupfinder
from pyramid.view import (
    view_config,
    )
from pyramid.httpexceptions import (
    HTTPFound,
    )
import colander
from deform import (
    Form,
    widget,
    ValidationFailure,
    )
from osipkd.models import (
    DBSession,
    Group
    )
from osipkd.models.pemda import Unit as UnitModel, Urusan as UrusanModel, UserUnit as UserUnitModel
from datatables import ColumnDT, DataTables
from osipkd.views.base_view import BaseViews

SESS_ADD_FAILED = 'Tambah unit gagal'
SESS_EDIT_FAILED = 'Edit unit gagal'

class AddSchema(colander.Schema):
    choices = DBSession.query(UrusanModel.id,
                  UrusanModel.nama).order_by(UrusanModel.nama).all()
    kode = colander.SchemaNode(
                    colander.String(),
                    validator=colander.Length(max=18))
    urusan_id = colander.SchemaNode(
                    colander.Integer(),
                    widget = widget.SelectWidget(values=choices),
                    missing=colander.drop,)
    nama = colander.SchemaNode(
                    colander.String())
    kategori = colander.SchemaNode(
                    colander.String(),
                    missing=colander.drop,)
    singkat = colander.SchemaNode(
                    colander.String(),
                    missing=colander.drop,)
    disabled = colander.SchemaNode(
                    colander.Boolean())
class EditSchema(AddSchema):
    id = colander.SchemaNode(colander.String(),
            missing=colander.drop,
            widget=widget.HiddenWidget(readonly=True))
class view_unit(BaseViews):
    ########                    
    # List #
    ########    
    @view_config(route_name='unit', renderer='templates/unit/list.pt',
                 permission='read')
    def view_list(self):
        return dict(a={})
    ##########                    
    # Action #
    ##########    
    @view_config(route_name='unit-act', renderer='json',
                 permission='view')
    def gaji_unit_act(self):
        ses = self.request.session
        req = self.request
        params = req.params
        url_dict = req.matchdict
        if url_dict['act']=='grid':
            columns = []
            columns.append(ColumnDT('id'))
            columns.append(ColumnDT('kode'))
            columns.append(ColumnDT('nama'))
            columns.append(ColumnDT('disabled'))
            
            groups = groupfinder(req.user, req)
            ids = []
            if req.user.id==1 or 'group:admin' in groups:
                query = UnitModel.query() #DBSession.query(UnitModel)
            else:
                units = DBSession.query(UserUnitModel.unit_id, 
                             UserUnitModel.sub_unit, UnitModel.kode
                             ).join(UnitModel).filter(UnitModel.id==UserUnitModel.unit_id,
                                    UserUnitModel.user_id==req.user.id).all() 

                for unit in units:
                    if unit.sub_unit:
                        rows = DBSession.query(UnitModel.id).filter(UnitModel.kode.ilike('%s%%' % unit.kode)).all()
                    else:
                        rows = DBSession.query(UnitModel.id).filter(UnitModel.kode==unit.kode).all()
                    for i in range(len(rows)):
                        ids.append(rows[i])
                query = DBSession.query(UnitModel).filter((UnitModel.id).in_(ids))
            rowTable = DataTables(req, UnitModel, query, columns)
            return rowTable.output_result()
            
        elif url_dict['act']=='changeid':
            ids  = UserUnitModel.unit_granted(req.user.id, params['unit_id'])
            if req.user.id>1 and 'g:admin' not in groupfinder(req.user, req)\
                    and not ids:
                return {'success':False, 'msg':'Anda tidak boleh mengubah ke unit yang bukan hak akses anda'}

            row = UnitModel.get_by_id('unit_id' in params and params['unit_id'] or 0)
            if row:
                ses['unit_id']=row.id
                ses['unit_kd']=row.kode
                ses['unit_nm']=row.nama
                return {'success':True, 'msg':'Sukses ubah SKPD'}
                
        elif url_dict['act']=='headofnama':
            term = 'term' in params and params['term'] or '' 
            rows = DBSession.query(UnitModel.id, UnitModel.kode, UnitModel.nama
                      ).filter(
                      UnitModel.nama.ilike('%%%s%%' % term) ).all()
            r = []
            for k in rows:
                d={}
                d['id']          = k[0]
                d['value']       = k[2]
                d['kode']        = k[1]
                d['nama']        = k[2]
                r.append(d)
            return r
            
        elif url_dict['act']=='headofkode':
            term = 'term' in params and params['term'] or '' 
            rows = DBSession.query(UnitModel.id, UnitModel.kode, UnitModel.nama
                      ).filter(
                      UnitModel.kode.ilike('%%%s%%' % term) ).all()
            r = []
            for k in rows:
                d={}
                d['id']          = k[0]
                d['value']       = k[1]
                d['kode']        = k[1]
                d['nama']        = k[2]
                r.append(d)
            return r
            
        elif url_dict['act']=='import':
            rows = DBSession.execute("""SELECT a.kode, a.nama, a.passwd, b.unit_id 
                                        FROM admin.users2 a
                                        INNER JOIN admin.user_units2 b
                                        ON a.id = b.id""").all()
            for kode,nama,passwd, unit_id in rows:
                user = Users()
                user.user_name = nama
                user.user_password = passwd
                user.email = ''.join([nama,'@tangerangkab.org'])
                user.status = 1
                DBSession.add(user)
                DBSession.flush()
                if user.id:
                    user_unit=UserUnitModel()
                    user_unit.user_id = user.id
                    user_unit.unit_id = unit_id
                    user_unit.status  = 1
                    DBSession.add(user_unit)
                    DBSession.flush()
                
                  
    #######    
    # Add #
    #######
    def form_validator(self, form, value):
        if 'id' in form.request.matchdict:
            uid = form.request.matchdict['id']
            q = DBSession.query(UnitModel).filter_by(id=uid)
            unit = q.first()
        else:
            unit = None
    def get_form(self, class_form, row=None):
        schema = class_form(validator=self.form_validator)
        schema = schema.bind()
        schema.request = self.request
        if row:
          schema.deserialize(row)
        return Form(schema, buttons=('simpan','batal'))
    def save(self, values, user, row=None):
        if not row:
            row = UnitModel()
            row.created = datetime.now()
            row.create_uid = user.id
        row.from_dict(values)
        row.updated = datetime.now()
        row.update_uid = user.id
        row.disabled = 'disabled' in values and values['disabled'] and 1 or 0
        DBSession.add(row)
        DBSession.flush()
        return row
    def save_request(self, values, row=None):
        if 'id' in self.request.matchdict:
            values['id'] = self.request.matchdict['id']
        row = self.save(values, self.request.user, row)
        self.request.session.flash('unit sudah disimpan.')
    def route_list(self):
        return HTTPFound(location=self.request.route_url('unit'))
    def session_failed(self, session_name):
        r = dict(form=self.session[session_name])
        del self.session[session_name]
        return r
    @view_config(route_name='unit-add', renderer='templates/unit/add.pt',