Skip to content

Instantly share code, notes, and snippets.

@tonosaman

tonosaman/README.md

Last active July 31, 2019 12:47
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save tonosaman/19bd598b9fcf6d292cdb5acdd226d1e8 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save tonosaman/19bd598b9fcf6d292cdb5acdd226d1e8 to your computer and use it in GitHub Desktop.
Download ZIP
Web2py sample application - SalesLog
Raw
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

SalesLog-companies

Contacts in grid view

SalesLog-contacts

Edit/New contact

SalesLog-contacts-edit

Raw
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)
Raw
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
Raw
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>
@tonosaman
Copy link
Author

tonosaman commented Jul 31, 2019 •
edited
Loading

Fig.

SalesLog-companies
SalesLog-contacts
SalesLog-contacts-edit

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment