-
Create new web application on Web2py
-
replace below files
- models/db.py
- controllers/default.py
- views/default/grid.html
# -*- 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) |
# -*- 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 |
{{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.