def originate(self, exten, cust):
      ''' Originate call
      '''
      uphones = DBSession.query(User).get(request.identity['user'].user_id).phone
      if len(uphones)<1:
         return dict(status=2)

      o = Outcall()
      o.user_id = request.identity['user'].user_id
      o.cust_id = cust
      o.cookie = randint(-2147483648, 2147483647)
      DBSession.add(o)
      DBSession.flush() # needed to get the out_id

      chan = uphones[0].sip_id
      exten = sub(r'\D', '', exten)
      log.debug('originate: outcall %d from extension %s to %s' % (
         o.out_id, chan, exten))
      res = Globals.manager.originate(
            'SIP/' + chan.encode('iso-8859-1'), # Channel
            exten.encode('iso-8859-1'), # Extension
            context=chan.encode('iso-8859-1'),
            priority='1',
            caller_id=default_cid,
            variables= {'OUTCALL': o.out_id,
               'COOKIE': o.cookie},
            async = True   # Seems to be needed else DB is not commited and
                           # call to uniqueid below fails with "inexistant call"
            )
      status = o.out_id if res.headers['Response']==u'Success' else -1
      log.debug('originate: res=%s, outcall=%d, status=%s, cookie=%d' % (
         res, o.out_id, status, o.cookie))
      return dict(status=status)

   def listen(self, id, **kw):
      ''' Listen record
      '''
      r = DBSession.query(Record).get(id)
      fn = '%s/rec-%s.wav' % (dir_monitor, r.uniqueid)
      try:
         st = stat(fn)
      except:
         flash(u'Enregistrement introuvable: %s' % fn, 'error')
         redirect('/records/')

      phones = DBSession.query(User).filter(User.user_name==request.identity['repoze.who.userid']).one().phone
      if len(phones)<1:
         log.debug('Playback from user %s : no extension' % (
            request.identity['user']))
         flash(u'Poste de l\'utilisateur %s introuvable' % \
               request.identity['user'], 'error')
         redirect('/records/')

      sip = phones[0].sip_id
      res = Globals.manager.originate(
            'SIP/' + sip, # Channel
            sip, # Extension
            application = 'Playback',
            data = fn[:-4],
            )
      log.debug('Playback %s from user %s (%s) returns %s' % (
         fn[:-4], request.identity['user'], sip, res))

      redirect('/records/')

def check_access():
   '''Check access rights / group: admin=full access, boss=users from same department, user.
   Returns SA Query object for selected CDRs
   '''

   if in_any_group('admin', 'APPELS'):
      cdrs = DBSession.query(CDR)

   elif in_group('CDS'):
      # Find list of phones from the user's list of phones
      # user_phones -> departments -> phones
      phones = []
      for p in request.identity['user'].phone:
         log.info('CDS phone %s -> department %s' % (p, p.department))
      for d in [d.department for d in request.identity['user'].phone]:
         log.info('CDS department <%s>' % (d))
         for p in d.phones:
            phones.append(p)
      src = [prefix_src + p.exten for p in phones]
      dst = [p.exten for p in phones]
      cdrs = DBSession.query(CDR).filter( (CDR.src.in_(src)) | (CDR.dst.in_(dst)) )
      log.info('CDS phone <%s> -> source <%s>, destination <%s>' % (
         request.identity['user'].phone, src, dst))


   elif in_group('utilisateurs'):
      src = [prefix_src + p.exten for p in request.identity['user'].phone]
      dst = [p.exten for p in request.identity['user'].phone]
      cdrs = DBSession.query(CDR).filter( (CDR.src.in_(src)) | (CDR.dst.in_(dst)) )

   else:
      flash(u'Accès interdit')
      redirect('/')

   return cdrs

   def save(self, name, comment, type, begin, end, file, active=None):

      log.debug('Save "%s"!' % file)

      c = Campaign()
      c.name = name
      c.comment = comment
      c.type = type
      c.active = active
      c.begin = begin
      c.end = end
      DBSession.add(c)
      DBSession.flush()
      log.debug(u'nouvelle campagne %s créée' % c.cmp_id)

      msg = u'Campagne "%s" créée' % name
      if file is not None:
         l, e, m = process_file(file, c.cmp_id)
         if l==0:
            msg += m
         else:
            msg += u', %d lignes intégrées' % l
            if e!=0:
               msg += u', %d erreurs' % e

      flash(msg)
      redirect('/cc_campaign/')

 def delete(self, id, **kw):
    ''' Delete shortcut from DB
    '''
    log.info('delete ' + kw['_id'])
    DBSession.delete(DBSession.query(Shortcut).get(kw['_id']))
    flash(u'Raccourci supprimé', 'notice')
    redirect('/shortcuts/')

   def create(self, pwd1, pwd2, **kw):
      ''' Add new user to DB
      '''
      if DBSession.query(User).filter(User.user_name==kw['user_name']).all():
         flash(u'Ce compte existe déjà, utilisateur pas créé', 'error')
         redirect('/users/')

      log.info('new ' + kw['user_name'])
      u = User()
      u.user_name = kw['user_name']
      u.firstname = kw['firstname']
      u.lastname = kw['lastname']
      u.email_address = kw['email_address']
      u.fax = kw['fax']
      u.voicemail = kw['voicemail']
      u.email_voicemail = kw['email_voicemail']
      u.password = pwd1
      u.display_name = u.lastname + ' ' + u.firstname
      u.ascii_name = asterisk_string(u.display_name)
      over = kw.get('over')
      if over is not None:
         Globals.manager.send_action({'Action': 'DBput',
            'Family': 'over', 'Key': over, 'Val': 'context'})

      if 'groups' in kw:
         u.groups = DBSession.query(Group).\
               filter(Group.group_id.in_(kw['groups'])).all()

      DBSession.add(u)
      flash(u'Nouvel utilisateur "%s" créé' % (kw['user_name']))

      redirect('/users/')

   def create(self, **kw):
      ''' Add new pickup to DB
      '''

      if DBSession.query(Pickup).filter(Pickup.name==kw['name']).all():
         flash(u'Ce groupe existe déjà, pas créé', 'error')
         redirect('/pickups/')

      # Find new pickup group (0-63)
      i = 0
      for p in DBSession.query(Pickup).order_by(Pickup.pickup_id):
         if p.pickup_id!=i: break
	 i += 1

      if i>63:
         flash(u'Nombre maximum de groupe d\'interceptions atteint, création impossible',
            'error')

      else:
         p = Pickup()
         p.pickup_id = i
         p.name = kw['name']
         p.comment = kw['comment']
         DBSession.add(p)
         flash(u'Nouveau groupe d\'interception "%s" créé' % (kw['name']))

      redirect('/pickups/')

 def delete(self, id, **kw):
    ''' Delete department from DB
    '''
    log.info('delete ' + kw['_id'])
    DBSession.delete(DBSession.query(Department).get(kw['_id']))
    flash(u'Service supprimé', 'notice')
    redirect('/departments/')

   def create_admin(self, cf_types, exten, to_intern, to_extern):
      ''' Add call forward to Asterisk database
      '''
      try:
         p = DBSession.query(Phone).filter(Phone.exten==exten).one()
      except:
         log.warning('No phone for extension %s' % exten)
         flash(u'Poste %s inexistant, renvoi non créé' % exten, 'error')
         redirect('/forwards/')

      dest = to_extern if to_extern else to_intern
      man = Globals.manager.command('database put %s %s %s' % (
         cf_types, p.sip_id, dest))
      log.debug('database put %s %s %s returns %s' % (
         cf_types, p.sip_id, dest, man))

      # Notify user's phone
      man = Globals.manager.command('sip notify grandstream-idle-screen-refresh %s' % (
         p.sip_id))
      log.debug('sip notify grandstream-idle-screen-refresh %s returns %s' % (
         p.sip_id, man))

      if len(dest) < 6:
         # Notify dest phone if not external
         try:
            p = DBSession.query(Phone).filter(Phone.exten==dest).one()
            man = Globals.manager.command('sip notify grandstream-idle-screen-refresh %s' % (
               p.sip_id))
            log.debug('sip notify grandstream-idle-screen-refresh %s returns %s' % (
               p.sip_id, man))
         except:
            log.warning('Phone not found for dest "%s"')

#      flash(u'Une erreur est survenue', 'error')
      redirect('/forwards/')

 def delete(self, id, **kw):
    ''' Delete group from DB
    '''
    log.info('delete ' + kw['_id'])
    DBSession.delete(DBSession.query(Group).get(kw['_id']))
    flash(u'Groupe supprimé', 'notice')
    redirect('/groups/')

   def delete(self, id, **kw):
      ''' Delete call forward
      '''
      exten, cf, to = kw['_id'].split(':')
      try:
         p = DBSession.query(Phone).filter(Phone.exten==exten).one()
      except:
         log.warning('No phone for extension %s' % exten)
         flash(u'Poste %s inexistant, renvoi non supprimé' % exten, 'error')
         redirect('/forwards/')

      log.info('delete %s %s %s' % (p.sip_id, cf, to))
      if in_any_group('admin', 'CDS'):
         man = Globals.manager.command('database del %s %s' % (
            cf, p.sip_id))
         log.debug('admin: database delete %s %s returns %s' % (
            cf_types, p.sip_id, man))

      else:
         u = DBSession.query(User).get(request.identity['user'].user_id)
         for p in u.phone:
            man = Globals.manager.command('database del %s %s' % (
               cf, p.sip_id))
            log.debug('database delete %s %s returns %s' % (
               cf, p.sip_id, man))
#      flash(u'Une erreur est survenue', 'error')

      redirect('/forwards/')

def play_or_tts(typ, val, brk=None):
    """ Choose Playback / Background or RealSpeak 
   Return app, param
   """
    if typ == "s":
        # XXX      if val not in application.sounds:
        # XXX         application.sounds.append(DBSession.query(Sound).get(val))
        s = DBSession.query(Sound).get(val)
        name = "astportal/%s" % s.name if s is not None else "beep"
        if brk is not None:
            app = u"Background"
            param = u"%s" % (name)
        else:
            app = u"Playback"
            param = u"%s" % (name)

    elif typ == "t":
        # XXX      if val not in application.texts:
        # XXX         application.texts.append(DBSession.query(Text).get(val))
        app = u"RealSpeak"
        txt = DBSession.query(Text).get(val)
        param = u"%s" % (txt.text.replace(",", "\,"))
        if brk is not None:
            param += u",%s" % (brk)

    return (app, param)

   def create(self, **kw):
      ''' Add new shortcut to DB
      '''

      if DBSession.query(Shortcut).filter(Shortcut.exten==kw['exten']).all() or \
         DBSession.query(Shortcut).filter(Shortcut.number==kw['number']).all():
         flash(u'Erreur : raccourci %s ou numéro %s existe déjà' % (
            kw['exten'], kw['number']), 'error')
         redirect('/shortcuts/')

      s = Shortcut()
      s.exten = kw['exten']
      s.number = kw['number']
      s.comment = kw['comment']
      s.phone = kw['phone']
      DBSession.add(s)
      flash(u'Nouveau raccourci "%s -> %s" créé' % (s.exten, s.number))

      if p.exten is not None:
         # Create new hint (extensions.conf)
         res = Globals.manager.update_config(
            directory_asterisk  + 'extensions.conf', 
            None, [('Append', 'shortcuts', 'exten', 
               '>%s,1,%s/%s' % \
                  (p.exten, 'SIP' if sip_type=='sip' else 'PJSIP', p.sip_id))])
         log.debug('Update hints extensions.conf returns %s' % res)

      redirect('/shortcuts/')

   def create_forward(self, cf_types, to_intern, to_extern=None, **kw):
      ''' Add call forward to Asterisk database
      '''
      log.debug('create_forward: %s %s %s' % (cf_types, to_intern, to_extern))
      dest = to_extern if to_extern else to_intern
      u = DBSession.query(User). \
            filter(User.user_name==request.identity['repoze.who.userid']). \
            one()
      for phone in u.phone:
         man = Globals.manager.command('database put %s %s %s' % (
            cf_types, phone.sip_id, dest))
         log.debug('database put %s %s %s returns %s' % (
            cf_types, phone.sip_id, dest, man))

         # Notify user's phone
         man = Globals.manager.command('sip notify grandstream-idle-screen-refresh %s' % (
            phone.sip_id))
         log.debug('sip notify grandstream-idle-screen-refresh %s returns %s' % (
            phone.sip_id, man))

      if len(dest) < 6:
         # Notify dest phone if not external
         try:
            phone = DBSession.query(Phone).filter(Phone.exten==dest).one()
            man = Globals.manager.command('sip notify grandstream-idle-screen-refresh %s' % (
               phone.sip_id))
            log.debug('sip notify grandstream-idle-screen-refresh %s returns %s' % (
               phone.sip_id, man))
         except:
            log.warning('Phone not found for dest "%s"')

#      flash(u'Une erreur est survenue', 'error')

      redirect('/forwards/')

 def delete(self, id, **kw):
    ''' Delete contact from DB
    '''
    log.info('delete ' + kw['_id'])
    DBSession.delete(DBSession.query(Phonebook).get(kw['_id']))
    flash(u'Contact supprimé', 'notice')
    redirect('/phonebook/')

   def put(self, **kw):
      ''' Update sound in DB
      '''
      id = int(kw['id'])
      s = DBSession.query(Sound).get(id)
      if kw.has_key('owner_id'):
         s.owner_id = kw['owner_id']
      s.comment = kw['comment']

      wav = kw['file']
      filetype = wav.type
      filedata = wav.file
      filename = '%s/%d_%s' % (dir_tmp, s.sound_id, wav.filename)
      # Temporarily save uploaded audio file
      out = open(filename, 'w')
      out.write(filedata.read())
      out.close()

      ret = process_file(filename, filetype, s.sound_id, filetype, s.name, s.language)

      if ret:
         flash(ret,'error')
         DBSession.delete(s)
         redirect('/moh/')

      flash(u'Son modifié')
      redirect('/moh/%d/edit' % id)

 def delete(self, id, **kw):
    ''' Delete holiday from DB
    '''
    log.info('delete ' + kw['_id'])
    DBSession.delete(DBSession.query(Holiday).get(kw['_id']))
    update_extensions()
    flash(u'Jour férié supprimé', 'notice')
    redirect('/holidays/')

def create_phones(base):
   for i in range(0,100):
      p = Phone()
      p.number = '%s%02d' % (base, i)
      p.department_id = -1
      DBSession.add(p)
   DBSession.flush()
   transaction.commit()

    def fetch_scenario(self, id, page=1, rp=25, sortname="name", sortorder="asc", qtype=None, query=None):
        """ Function called on AJAX request made by FlexGrid
      Fetch data from DB, return the list of rows + total + current page
      """

        actions = [
            {"action_id": x.action_id, "action_name": x.name, "action_comment": x.comment}
            for x in DBSession.query(Action).order_by(Action.name)
        ]

        owner = ""  # DBSession.query(Application.owner_id).get(id)[0]

        sounds = [
            {"sound_id": x.sound_id, "sound_name": x.name, "sound_comment": x.comment}
            for x in DBSession.query(Sound).filter(Sound.type == 1).order_by(Sound.name)
        ]

        texts = []

        queues = [
            {"queue_id": x.queue_id, "queue_name": x.name, "queue_comment": x.comment}
            for x in DBSession.query(Queue).order_by(Queue.name)
        ]

        qevents = [
            {"qe_id": x.qe_id, "event": x.event} for x in DBSession.query(Queue_event).order_by(Queue_event.event)
        ]

        scenario = []
        positions = {}
        for x in (
            DBSession.query(Scenario).filter(Scenario.app_id == id).order_by(Scenario.context).order_by(Scenario.step)
        ):
            scenario.append(
                {
                    "sce_id": x.sce_id,
                    "context": x.context,
                    "extension": x.extension,
                    "priority": x.step,
                    "application": x.action,
                    "parameters": x.parameters,
                    "comments": x.comments,
                    "target": 0,
                }
            )
            if x.top and x.left:
                context = "context_" + x.context
                positions[context] = {"top": x.top, "left": x.left}

        return dict(
            scenario=scenario,
            sounds=sounds,
            texts=texts,
            actions=actions,
            queues=queues,
            qevents=qevents,
            positions=positions,
        )

   def record(self, name, channel, queue, custom1=None, custom2=None):
      '''Record a queue member
         Called from call center monitor web page

Action: Monitor
Mix: 1
File: test
Channel: SIP/pEpSNlcv-000001b9
   '''
      log.debug('Record request "%s" (%s) on "%s"' % (name, channel, queue))

      # Check channel exists, else abort
      for cha in Globals.asterisk.channels.keys():
         if cha.startswith(channel):
            unique_id = Globals.asterisk.channels[cha]['Uniqueid']
            break
      else:
         log.warning('No active channel for %s ?' % channel)
         return dict(status=0)

      # XXX Authentification

      # Gather data from database
      user_id = DBSession.query(User).filter(
         User.user_name==request.identity['repoze.who.userid']).one().user_id
      member_id = DBSession.query(Phone).filter(
         Phone.sip_id==channel[-8:]).one().user_id
      queue_id = DBSession.query(Queue).filter(
            Queue.name==queue).one().queue_id

      # Create filename and send record action to Asterisk via manager
      f = 'rec-%s' % unique_id
      res = Globals.manager.send_action(
            {  'Action': 'Monitor',
               'Mix': 1,
               'Channel': cha,
               'File': f})
      log.info('Record request from userid "%s" to channel %s returns "%s"' % ( 
         user_id, cha, res))

      if res.get_header('Response')=='Success':
         status = 0
         # Set "recorded" flag on member
         Globals.asterisk.members[name]['Recorded'] = True
         # Insert record info into database
         r = Record()
         r.uniqueid = unique_id
         r.queue_id = queue_id
         r.member_id = member_id
         r.user_id = user_id
         r.custom1 = custom1
         r.custom2 = custom2
         DBSession.add(r)

      else:
         status = 1

      return dict(status=status)