Web2py sample application - SalesLog

README.md

Setup

1. Create new web application on Web2py

2. replace below files

• models/db.py
• controllers/default.py
• views/default/grid.html

Usage

Summary of contacts

Contacts in grid view

Edit/New contact

db.py

# -*- coding: utf-8 -*-

# -------------------------------------------------------------------------
# This scaffolding model makes your app work on Google App Engine too
# File is released under public domain and you can use without limitations
# -------------------------------------------------------------------------

if request.global_settings.web2py_version < "2.14.1":
    raise HTTP(500, "Requires web2py 2.13.3 or newer")

# -------------------------------------------------------------------------
# if SSL/HTTPS is properly configured and you want all HTTP requests to
# be redirected to HTTPS, uncomment the line below:
# -------------------------------------------------------------------------
# request.requires_https()

# -------------------------------------------------------------------------
# app configuration made easy. Look inside private/appconfig.ini
# -------------------------------------------------------------------------
from gluon.contrib.appconfig import AppConfig

# -------------------------------------------------------------------------
# once in production, remove reload=True to gain full speed
# -------------------------------------------------------------------------
myconf = AppConfig(reload=True)

if not request.env.web2py_runtime_gae:
    # ---------------------------------------------------------------------
    # if NOT running on Google App Engine use SQLite or other DB
    # ---------------------------------------------------------------------
    db = DAL(myconf.get('db.uri'),
             pool_size=myconf.get('db.pool_size'),
             migrate_enabled=myconf.get('db.migrate'),
             check_reserved=['all'])
else:
    # ---------------------------------------------------------------------
    # connect to Google BigTable (optional 'google:datastore://namespace')
    # ---------------------------------------------------------------------
    db = DAL('google:datastore+ndb')
    # ---------------------------------------------------------------------
    # store sessions and tickets there
    # ---------------------------------------------------------------------
    session.connect(request, response, db=db)
    # ---------------------------------------------------------------------
    # or store session in Memcache, Redis, etc.
    # from gluon.contrib.memdb import MEMDB
    # from google.appengine.api.memcache import Client
    # session.connect(request, response, db = MEMDB(Client()))
    # ---------------------------------------------------------------------

# -------------------------------------------------------------------------
# by default give a view/generic.extension to all actions from localhost
# none otherwise. a pattern can be 'controller/function.extension'
# -------------------------------------------------------------------------
response.generic_patterns = ['*'] if request.is_local else []
# -------------------------------------------------------------------------
# choose a style for forms
# -------------------------------------------------------------------------
response.formstyle = myconf.get('forms.formstyle')  # or 'bootstrap3_stacked' or 'bootstrap2' or other
response.form_label_separator = myconf.get('forms.separator') or ''

# -------------------------------------------------------------------------
# (optional) optimize handling of static files
# -------------------------------------------------------------------------
# response.optimize_css = 'concat,minify,inline'
# response.optimize_js = 'concat,minify,inline'

# -------------------------------------------------------------------------
# (optional) static assets folder versioning
# -------------------------------------------------------------------------
# response.static_version = '0.0.0'

# -------------------------------------------------------------------------
# Here is sample code if you need for
# - email capabilities
# - authentication (registration, login, logout, ... )
# - authorization (role based authorization)
# - services (xml, csv, json, xmlrpc, jsonrpc, amf, rss)
# - old style crud actions
# (more options discussed in gluon/tools.py)
# -------------------------------------------------------------------------

from gluon.tools import Auth, Service, PluginManager

# host names must be a list of allowed host names (glob syntax allowed)
auth = Auth(db, host_names=myconf.get('host.names'))
service = Service()
plugins = PluginManager()

# -------------------------------------------------------------------------
# create all tables needed by auth if not custom tables
# -------------------------------------------------------------------------
auth.define_tables(username=True, signature=False)

# -------------------------------------------------------------------------
# configure email
# -------------------------------------------------------------------------
mail = auth.settings.mailer
mail.settings.server = 'logging' if request.is_local else myconf.get('smtp.server')
mail.settings.sender = myconf.get('smtp.sender')
mail.settings.login = myconf.get('smtp.login')
mail.settings.tls = myconf.get('smtp.tls') or False
mail.settings.ssl = myconf.get('smtp.ssl') or False

# -------------------------------------------------------------------------
# configure auth policy
# -------------------------------------------------------------------------
auth.settings.registration_requires_verification = False
auth.settings.registration_requires_approval = False
auth.settings.reset_password_requires_verification = True

# -------------------------------------------------------------------------
# Define your tables below (or better in another model file) for example
#
# >>> db.define_table('mytable', Field('myfield', 'string'))
#
# Fields can be 'string','text','password','integer','double','boolean'
#       'date','time','datetime','blob','upload', 'reference TABLENAME'
# There is an implicit 'id integer autoincrement' field
# Consult manual for more options, validators, etc.
#
# More API examples for controllers:
#
# >>> db.mytable.insert(myfield='value')
# >>> rows = db(db.mytable.myfield == 'value').select(db.mytable.ALL)
# >>> for row in rows: print row.id, row.myfield
# -------------------------------------------------------------------------

# -------------------------------------------------------------------------
# after defining tables, uncomment below to enable auditing
# -------------------------------------------------------------------------
# auth.enable_record_versioning(db)

# migrate=False

# -------------------------------------------------------------------------
# 営業活動記録
# -------------------------------------------------------------------------

db.define_table('company', Field('name', notnull = True, unique = True, label='社名'), format = '%(name)s')
db.define_table('contact',
                Field('name', notnull=True, label='訪問相手'),
                Field('company', 'reference company', label='訪問先社名',
                      widget = SQLFORM.widgets.autocomplete(request, db.company.name, id_field=db.company.id, limitby=(0,20), min_length=1)),
                Field('priority','integer', default=2, label='見込み',
                      requires=IS_IN_SET([1,2,3], labels=['薄い', '未知数', '有望'], zero=None),
                      represent=lambda x, row: dict([(1,'薄い'),(2,'未知数'),(3,'有望')])[x]
                ),
                Field('meet_at', type='date', label='訪問日', notnull = True, represent=lambda x, row: x.strftime("%Y-%m-%d")),
                Field('follow', 'boolean', label='フォローアップ済', default=False),
                Field('picture', 'upload', label='画像'),
                Field('email', requires = IS_EMAIL(), label='メールアドレス'),
                Field('phone_number', requires = IS_MATCH('[\d\-\(\) ]+'), label='電話番号'),
                Field('address', label='所在地'),
                Field('created_on', 'datetime',
                      default=request.now, update=request.now, writable=False),
                Field('created_by', 'reference auth_user',
                      default=auth.user_id, update=auth.user_id, writable=False),
                format='%(name)s',
                )

db.executesql("""
CREATE VIEW IF NOT EXISTS contact_summary AS
WITH last_contact AS (
SELECT contact.company AS company_id, contact.id AS id, COUNT(contact.id) AS num, MAX(meet_at) AS meet_at
 FROM contact GROUP BY contact.company HAVING meet_at = MAX(meet_at)
)
SELECT company.id AS id, last_contact.id AS last_contact_id, company.name AS name,
       last_contact.num AS num, last_contact.meet_at AS meet_at
 FROM company LEFT OUTER JOIN last_contact ON last_contact.company_id = company.id
 ORDER BY last_contact.meet_at DESC
""")

db.define_table('contact_summary',
                Field('id', 'integer'),
                Field('last_contact_id', 'integer'),
                Field('name', 'string', label='訪問先'),
                Field('num', 'integer', label='累計訪問回数'),
                Field('meet_at', 'date', label='最終訪問日'),
                migrate=False)

default.py

# -*- coding: utf-8 -*-
# this file is released under public domain and you can use without limitations

# user is required for authentication and authorization
def user():
    """
    exposes:
    http://..../[app]/default/user/login
    http://..../[app]/default/user/logout
    http://..../[app]/default/user/register
    http://..../[app]/default/user/profile
    http://..../[app]/default/user/retrieve_password
    http://..../[app]/default/user/change_password
    http://..../[app]/default/user/bulk_register
    use @auth.requires_login()
        @auth.requires_membership('group name')
        @auth.requires_permission('read','table name',record_id)
    to decorate functions that need access control
    also notice there is http://..../[app]/appadmin/manage/auth to allow administrator to manage users
    """
    return dict(form=auth())

# download is for downloading files uploaded in the db (does streaming)
@cache.action()
def download():
    """
    allows downloading of uploaded files
    http://..../[app]/default/download/[filename]
    """
    return response.download(request, db)

def call():
    """
    exposes services. for example:
    http://..../[app]/default/call/jsonrpc
    decorate with @services.jsonrpc the functions to expose
    supports xml, json, xmlrpc, jsonrpc, amfrpc, rss, csv
    """
    return service()

#--------------------
# https://www.tutorialspoint.com/web2py/web2py_quick_guide.htm
# Building an Application > Creation of Controller

response.menu = [
        [u'訪問先一覧', False, URL('index')],
        [u'訪問履歴', False, URL('contacts')],
        [u'訪問実績入力', False, URL('contacts', args=['new', 'contact'])]]
response.title= u"営業活動記録"

@auth.requires_login()
def index():
    db.contact_summary.num.represent = \
        lambda value, row: A(SPAN(_class='glyphicon glyphicon-search'),
                 u'コンタクト (%d件)' % (0 if value is None else value),
                 _disabled=(True if value is None else False),
                 _class='button btn btn-default',
                 _href='' if value is None else URL("contacts", vars=dict(keywords = 'contact.company="%d"' % row.id))
        )
    db.contact_summary.meet_at.represent = \
        lambda value, row: SPAN() if value is None else \
            A(SPAN(_class='glyphicon glyphicon-search'),
              u'最終訪問日(%s)' % row.meet_at,
              _class='button btn btn-default',
              _href=URL('default/contacts', 'view', args=['contact', row.last_contact_id]))
    db.contact_summary.last_contact_id.represent = lambda value, row: DIV(' ', _style='display:None')
    grid = SQLFORM.grid(db.contact_summary,
                        orderby=[~db.contact_summary.meet_at],
                        fields=[db.contact_summary.name, db.contact_summary.num, db.contact_summary.meet_at, db.contact_summary.last_contact_id],
                        headers = {'contact_summary.last_contact_id': DIV(' ', _style='display:None')},
                        paginate=5,
                        csv=False,
                        details=False,
                        create=False,
                        editable=False,
                        deletable=False,
                        user_signature=False,
                        represent_none=""
    )
    response.subtitle = u'訪問先サマリ'
    response.view = 'default/grid.html'
    return dict(grid=grid)

@auth.requires_login()
def contacts():
    grid = SQLFORM.grid(db.contact,
                        fields=[ db.contact.name
                               , db.contact.company
                               , db.contact.priority
                               , db.contact.follow
                               , db.contact.meet_at],
                        orderby=[~db.contact.meet_at],
                        paginate=20,
                        details=True,
                        create=True,
                        editable=True,
                        deletable=True,
                        user_signature=False,
                        exportclasses=dict(csv=(ExporterCSV, 'CSV', u'CSVファイルを出力します'),
                                           pdf=(ExporterPDF, 'PDF', u'PDFファイルを出力します'),
                                           json=False, html=False, tsv=False, xml=False, csv_with_hidden_cols=False, tsv_with_hidden_cols=False),
    )

    def onvalidation_contact(form):
        if form.vars['company'] is None:
            form.vars['company'] = db.company.insert(name=form.vars['_autocomplete_company_name_aux'])

    form = grid.element('.web2py_form')
    if form and form.process(dbio=True, onvalidation=onvalidation_contact).accepted:
        redirect(URL(c='default', f='contacts'))
    elif form and form.errors:
        response.flash = u'エラー:不正な入力があります'

    response.subtitle = u'訪問履歴'
    response.view = 'default/grid.html'
    return dict(grid=grid)

#--------------------
from gluon.sqlhtml import ExportClass

class ExporterCSV(ExportClass):
    label = 'CSV'
    file_ext = "csv"
    content_type = "text/csv"

    def __init__(self, rows):
        ExportClass.__init__(self, rows)

    def labels(self):
        return [db[t][f].label for t, f in
                (col.replace('"', '').split('.') for col in self.rows.colnames)]

    def export(self):
        if self.rows:
            # field names (i.e ID, NAME, EMAIL, COMPANY)
            # labels = [c.split('.')[-1] for c in self.rows.colnames]
            from cStringIO import StringIO
            import csv
            out = StringIO()
            csv.writer(out).writerow(self.labels())
            self.rows.export_to_csv_file(out, represent=True, write_colnames=False)
            return out.getvalue()
        else:
            return ''

class ExporterPDF(ExportClass):
    label = 'PDF'
    file_ext = "pdf"
    content_type = "application/pdf"

    def __init__(self, rows):
        ExportClass.__init__(self, rows)

    def export(self):
        inputs = [x.values() for x in self.rows.as_list()]
        return out_pdf(inputs)

#--------------------
# https://qiita.com/ekzemplaro/items/a3e3d4419a560f3185e3

from reportlab.lib import colors
from reportlab.lib.pagesizes import A4
from reportlab.platypus import SimpleDocTemplate, Table, TableStyle
from reportlab.platypus import Spacer
from reportlab.platypus import Paragraph
from reportlab.pdfbase.pdfmetrics import registerFont
from reportlab.pdfbase.cidfonts import UnicodeCIDFont
from reportlab.lib.styles import ParagraphStyle
from reportlab.lib.styles import getSampleStyleSheet
from reportlab.pdfbase import pdfmetrics
from reportlab.pdfbase.ttfonts import TTFont
from reportlab.lib.units import inch, mm
from uuid import uuid4
from cgi import escape
import os

def pdf():
    rows= [['テスト', 'Good\nAfternoon', 'CC', 'DD', '春'],
           ['おはよう\nございます。', '11', '12', '13', '夏'],
           ['今日は\n晴れています。', '21', '22', '23', '秋']]
    rows = [['こんにちは', 'Good\nMorning', 'CC', 'DD', '春'],
            ['おはよう\nございます。', '11', '12', '13', '夏'],
            ['今晩は', '21', '22', '23', '秋'],
            ['さようなら', '31', '32', '33', '冬']]
    rows = [x.values() for x in db().select(db.contact.name, db.contact.company, db.contact.priority, db.contact.meet_at, db.contact.email).as_list()]
    return out_pdf(rows)

def out_pdf(rows):
    def table_first(elements, styles, rows):
        elements.append(Paragraph('16pt フォント', ParagraphStyle(name='Normal', fontName='TakaoMincho', fontSize=16)))
        elements.append(Spacer(1, 10*mm))
        tt = Table(rows, colWidths=(10*mm, 40*mm, 10*mm, 40*mm, 80*mm), rowHeights=9*mm)
        tt.setStyle(TableStyle([('BACKGROUND', (1, 1), (-2,-2), colors.cyan),
                                ('TEXTCOLOR', (0, 0), (1,-1), colors.red),
                                ('FONT', (0, 0), (-1, -1), "TakaoMincho", 16),
                                ('GRID', (0, 0), (4, 4), 0.25, colors.black)]))
        elements.append(tt)

    def table_second(elements, styles, rows):
        elements.append(Paragraph('20pt フォント', ParagraphStyle(name='Normal', fontName='TakaoMincho', fontSize=20)))
        elements.append(Spacer(1, 10*mm))
        tt = Table(rows, colWidths=(10*mm, 40*mm, 10*mm, 40*mm, 90*mm), rowHeights=12*mm)
        tt.setStyle(TableStyle([('BACKGROUND', (1,1), (-2,-2), colors.magenta),
                                ('TEXTCOLOR', (0,0), (1,-1), colors.blue),
                                ('FONT', (0, 0), (-1, -1), "TakaoMincho", 20),
                                ('GRID', (0, 0), (4, 4), 0.25, colors.black)]))
        elements.append(tt)

    pdfmetrics.registerFont(TTFont('TakaoMincho', '/usr/share/fonts/truetype/takao-mincho/TakaoMincho.ttf'))
    elements = []
    styles = getSampleStyleSheet()
    table_first(elements, styles, rows)
    elements.append(Spacer(1, 10*mm))
    table_second(elements, styles, rows)

    tmpfilename = os.path.join(request.folder, 'private', str(uuid4()))
    doc = SimpleDocTemplate(tmpfilename, pagesize=A4)
    doc.build(elements)
    data = open(tmpfilename, "rb").read()
    os.unlink(tmpfilename)
    response.headers['Content-Type'] = 'application/pdf'
    return data

grid.html

{{extend 'layout.html'}}
{{
if not request.args:
  w2p_grid_tbl = grid.element('table')
  original_export_menu = grid.element('div.w2p_export_menu')
  if w2p_grid_tbl and original_export_menu:
    export_menu_items = []
    for a in original_export_menu.elements('a'):
      a['_class'] = ''
      export_menu_items.append(LI(a))
    pass
    export_menu = grid.element('div.w2p_export_menu',replace=
      DIV(A('ダウンロード', SPAN(_class='caret'), _href='#',
            _class='dropdown-toggle', **{'_data-toggle':'dropdown'}),
          UL(*export_menu_items, _class='dropdown-menu'),
          _class='w2p_export_menu btn-group')
)
  pass
pass
}}
{{=grid}}

<details>
<summary>このページの使い方</summary>
このように説明文を記述できます。

{{=MARKMIN('''
----
# [[Markmin markup language http://www.web2py.com/init/static/markmin.html]]

[[alt-string for the image [the image title] http://www.web2py.com/examples/static/web2py_logo.png left 200px]]
[[alt-string for the image [the image title] http://www.web2py.com/examples/static/web2py_logo.png center 200px]]

## How to start interactive web2py

``
$ ./web2py.py -S IntranetSample -M
``:code[sh]

## [[Self-Reference and aliases http://www.web2py.com/books/default/chapter/29/06/the-database-abstraction-layer#Self-Reference-and-aliases]]

``
>>> fid, mid = db.person.bulk_insert([dict(name='Massimo'), dict(name='Claudia')])
>>> db.person.insert(name='Marco', father_id=fid, mother_id=mid)
3
>>> Father = db.person.with_alias('father')
>>> Mother = db.person.with_alias('mother')
>>> type(Father)
<class 'pydal.objects.Table'>
>>> str(Father)
'person AS father'
>>> rows = db().select(db.person.name, Father.name, Mother.name,
...                    left=(Father.on(Father.id == db.person.father_id),
...                          Mother.on(Mother.id == db.person.mother_id)))
>>> for row in rows:
...     print row.person.name, row.father.name, row.mother.name
...
Massimo None None
Claudia None None
Marco Massimo Claudia
``:code[sh]

## [[DAL modeling http://www.web2py.com/books/default/chapter/33/06//#-SQL]]

### How to represent sub-query

DAL can not construct a single select query that contains complex subqueries.
So we execute separate select queries and bind them from the python code.

[[find exclude sort about Rows http://www.web2py.com/books/default/chapter/33/06//#find-exclude-sort]]

### How to use VIEW in database

If you need complicated aggregation as sub-query, then you should CREATE VIEW and bypass DAL.
[[Stack Overflow https://stackoverflow.com/a/33676595]]

In models/db.py:
- ``db.executesql("""CREATE VIEW IF NOT EXISTS view_name AS...""")``
- ``db.define_table('view_name', ...``

## [[SQLite3 support WINDOW function version 3.25 or higher https://mag.osdn.jp/18/09/19/164500]]

- Ubuntu 16.04 LTS support version 3.11.0
- backport is upper 3.22.0
  -- ``sudo add-apt-repository ppa:jonathonf/backports``
  -- ``sudo apt-get update && sudo apt-get install sqlite3``
- **TODO**: Build env in docker contains web2py and sqlite3

''', extra={'code':lambda text,lang='python': CODE(text,language=lang).xml()})
}}
</details>

Fig.