def check_passwd_for_cms(user_id, password):
package_name = 'pkg_user_auth'
if StrUtil.get_safe_config(current_app,
'PROJECT_STAGE') == Const.DEVELOPMENT:
package_name = 'pkg_user_auth_debug'
current_sqlalchemy_echo = StrUtil.get_safe_config(
current_app, 'SQLALCHEMY_ECHO')
# 認証SQLのログを出力しないようにする
db.session.bind.echo = False
returnVal = db.session.execute(
'select ' + package_name +
'.check_passwd_for_cms(:user_id, :password) as val from dual', {
'user_id': user_id,
'password': password
}).fetchone().val
# 現状設定に戻す
db.session.bind.echo = current_sqlalchemy_echo
if returnVal == 0:
return True
return False
def admin_main_init(db_id, request):
db_name = ""
information_message = ""
# ナビゲーションリンク
navi_arr_ref = []
navi_arr_ref.append('Main Menu')
navi_arr_ref.append(url_for('db_adm_index', db_id=db_id))
if app.lib.cms_lib.session.current_db:
db_name = app.lib.cms_lib.session.current_db.db_name
information_message = app.lib.cms_lib.session.current_db.information_message
StrUtil.print_debug(
'main_db_admin_init. db_name:{0} information_message:{1}'.format(
db_name, information_message))
cmsObjectType = CmsObjectType()
objTypeList = cmsObjectType.getObjectTypeList(db_id)
return render_template('cms_db_admin/main.html',
title='ログインメイン',
navi_bar_html=HtmlUtil.print_navi_bar(navi_arr_ref),
db_id=db_id,
db_name=db_name,
current_user=current_user,
objectTypeList=objTypeList,
appVer=current_app.config['APP_VER'])
def createCsvFile(self, writer):
self._init_xml()
try:
# ヘッダを書き込み
headerFilePath = os.path.join(current_app.root_path, self.csvDir,
self.headerFileName)
with open(headerFilePath, 'r', encoding='utf_8_sig') as f:
l_strip = [s.strip() for s in f.readlines()]
writer.writerow(l_strip)
# データリストを書き込み
if self.dataList is not None:
for data in self.dataList:
rowData = []
for col in self.xmlReader.getColumnList():
if hasattr(data, col):
rowData.append(getattr(data, col))
else:
rowData.append('')
writer.writerow(rowData)
except Exception as e:
tb = sys.exc_info()[2]
StrUtil.print_error("createCsvFile. error_msg:{}".format(
str(e.with_traceback(tb))))
def redirectDbAdmin(db_id):
if len(db_id) == 0:
flash('[db_id]パラメータを入れてください')
return render_template('error/404.html')
# データベースオブジェクトを取得する
current_db = app.lib.cms_lib.session.get_current_db(db_id)
# グローバル変数に設定する
app.lib.cms_lib.session.current_db = current_db
if current_db is None:
flash('[db_id:{}]情報を取得できません'.format(db_id))
return render_template('error/404.html')
StrUtil.print_debug("db_adm_login_required. cur_db.db_id=[{}]".format(
str(current_db.db_id)))
if current_user.is_active:
session['last_login_user_id'] = current_user.get_id()
return set_cookie(
StrUtil.get_safe_config(current_app, 'CMS_DB_SYS_COOKIE'),
current_user.tuid, url_for('db_adm_index', db_id=db_id))
form = LoginForm()
# ログイン情報を保持する
last_login_user_id = StrUtil.get_safe_edit_mode('last_login_user_id',
session)
user_id = request.args.get('user_id') or last_login_user_id
if user_id:
form.user_id.data = user_id
return render_template('cms_admin/login.html',
form=form,
db_id=db_id,
db_name=current_db.db_name,
systemVersion="Developer Version 1.00")
def view_pdf(file_id):
params = {}
params['file_id'] = file_id
params = file_service.decompress_file(params)
if params['df'] != '':
downloadDirPath = current_app.config['DOWNLOAD_DIR_PATH']
pdf_file_path = params['df'].replace(downloadDirPath, '/view_pdf')
StrUtil.print_debug("file_path:{}".format(pdf_file_path))
return render_template('view_pdf.html', pdf_full_path=pdf_file_path)
def _check_date(year, month, day):
try:
newDataStr = "%04d/%02d/%02d" % (int(year), int(month), int(day))
newDate = datetime.datetime.strptime(newDataStr, "%Y/%m/%d")
return True
except Exception as e:
tb = sys.exc_info()[2]
StrUtil.print_error("_check_date error_msg:{}".format(str(e.with_traceback(tb))))
return False
def doDbAdminLogin(db_id, form):
if len(db_id) == 0:
flash('[db_id]パラメータを入れてください')
return render_template('error/404.html')
# データベースオブジェクトを取得する
current_db = app.lib.cms_lib.session.get_current_db(db_id)
# グローバル変数に設定する
app.lib.cms_lib.session.current_db = current_db
if current_db is None:
flash('[db_id:{}]情報を取得できません'.format(db_id))
return render_template('error/404.html')
StrUtil.print_debug("db_adm_login_required. cur_db.db_id=[{}]".format(
str(current_db.db_id)))
# リダイレクトURLを取得する
next_url = _get_next_url()
if form.validate_on_submit():
user = User.query.filter_by(tuid=form.user_id.data).first()
if user is None or not PkgUserAuth.check_passwd_for_cms(
form.user_id.data, form.password.data):
# エラーログを記録する
pkgCmsErrLog = PkgCmsErrLog()
pkgCmsErrLog.saveErrLog('LOGIN_ERROR', str(form.user_id.data),
str(current_db.db_id), '')
db.session.commit()
flash('invalid user_id or password')
return redirect(
url_for('db_adm_login',
db_id=db_id,
user_id=form.user_id.data,
next_url=next_url))
login_user(user, False)
session['last_login_user_id'] = form.user_id.data
return set_cookie(
StrUtil.get_safe_config(current_app, 'CMS_DB_SYS_COOKIE'),
current_user.tuid, url_for('db_adm_index', db_id=db_id))
# ログイン情報を保持する
last_login_user_id = StrUtil.get_safe_edit_mode('last_login_user_id',
session)
user_id = request.args.get('user_id') or last_login_user_id
if user_id:
form.user_id.data = user_id
return render_template('cms_db_admin/login.html',
form=form,
db_id=db_id,
db_name=current_db.db_name,
next_url=next_url,
systemVersion="Developer Version 1.00")
def file_pdf(file_id):
params = {}
params['file_id'] = file_id
params['disp_mode'] = request.args.get('disp_mode')
params = file_service.decompress_file(params)
if params['df'] is not None and params['df'] != '':
downloadDirPath = current_app.config['DOWNLOAD_DIR_PATH']
pdf_file_path = params['df'].replace(downloadDirPath, '/file_pdf')
StrUtil.print_debug("file_path:{}".format(pdf_file_path))
return render_template('view_pdf.html', pdf_full_path=pdf_file_path)
else:
return render_template('error/fileNotFound.html')
def saveErrLog(self, error_cd, user_id, db_id='', note=''):
ip_addr = StrUtil.get_ip_addr()
url = StrUtil.get_current_url(error_cd)
db.session.execute(
'begin ' + self.package_name + '.save_error_log' +
'(:error_cd, :user_id, :db_id, :ip_addr, :url, :note); ' + 'end;',
{
'error_cd': error_cd,
'user_id': user_id,
'db_id': db_id,
'ip_addr': ip_addr,
'url': url,
'note': note
})
def wrapper(*args, **kwargs):
logout_user()
StrUtil.print_debug('db_adm_login_required. func=[{}]'.format(
func.__name__))
db_id = app.lib.cms_lib.session.get_db_id()
if not db_id:
flash('[db_id]パラメータが必要です')
return redirect(url_for('login'))
# データベースオブジェクトを取得する
current_db = app.lib.cms_lib.session.get_current_db(db_id)
# グローバル変数に設定する
app.lib.cms_lib.session.current_db = current_db
# db情報チェック
if not current_db:
flash('[db_id:{}]情報を取得できません'.format(db_id))
return redirect(url_for('db_adm_login', db_id=db_id))
StrUtil.print_debug(
'db_adm_login_required. cur_db.db_id=[{}]'.format(
str(current_db.db_id)))
session_id = app.lib.cms_lib.session.get_session_id(
StrUtil.get_safe_config(current_app, 'CMS_DB_SYS_COOKIE'))
if session_id:
StrUtil.print_debug(
'db_adm_login_required. session_cookie_name:{0} session_id:{1}'
.format('DB_ADMIN_SESSION_COOKIE', session_id))
cst = CmsSessionTable.get_db_adm_session_info(session_id)
if cst is None:
flash('invalid user_id or password')
return redirect(url_for('db_adm_login'))
# 取得したユーザIDでユーザ情報を取得する
user = User.query.filter_by(tuid=cst.user_id).first()
if user is None:
flash('invalid user_id or password')
return redirect(url_for('db_adm_login'))
# DB管理者権限チェック
pkgCmsSecurity = PkgCmsSecurity()
if not pkgCmsSecurity.isDbAdminUser(db_id, user.tuid):
flash('利用権限がありません')
return redirect(
UserAuth._get_redirect_url(
url_for('db_adm_login', db_id=current_db.db_id)))
login_user(user, False)
else:
StrUtil.print_debug('login_required. no session id got.')
return redirect(
UserAuth._get_redirect_url(
url_for('db_adm_login', db_id=current_db.db_id)))
return func(*args, **kwargs)
def unzip_file(uf, unzipDirPath, file_name):
df = os.path.join(unzipDirPath, str(file_name))
try:
decompressedFile = gzip.open(uf, 'rb')
if not os.path.isdir(unzipDirPath):
os.makedirs(unzipDirPath)
openDf = open(df, 'wb')
openDf.write(decompressedFile.read())
decompressedFile.close()
openDf.close()
return df
except Exception as e:
StrUtil.print_error('unzip_file file_path:{}'.format(str(df)))
return None
def get_db_adm_session_info(session_id):
current_time = datetime.now()
return CmsSessionTable.query.filter_by(cookie_name=StrUtil.get_safe_config(current_app, 'CMS_DB_SYS_COOKIE'),
session_id=session_id) \
.filter(CmsSessionTable.login_date >= current_time - timedelta(days=7)) \
.filter(CmsSessionTable.login_date <= current_time).first()
def _get_ymd(date_str, fmt, date_hash):
if fmt == 'YYYY-MM-DD' or fmt == 'YYYY/MM/DD':
match = re.search('^(\d+)[\-\/](\d+)[\-\/](\d+)$', date_str)
if not match:
return 1
date_hash['yyyy'] = match.group(1)
date_hash['mm'] = match.group(2)
date_hash['dd'] = match.group(3)
elif fmt == 'YY/MM/DD':
match = re.search('^(\d{1,2})[\-\/](\d{1,2})[\-\/](\d{1,2})$', date_str)
if not match:
return 1
if int(match.group(3)) > 50:
date_hash['yyyy'] = 1900 + int(match.group(1))
else:
date_hash['yyyy'] = 2000 + int(match.group(1))
date_hash['mm'] = match.group(2)
date_hash['dd'] = match.group(3)
elif fmt == 'DD/Mon/YY' or fmt == 'DD-Mon-YY':
match = re.search('^(\d+)[\-\/](\w+)[\-\/](\d+)$', date_str)
if not match:
return 1
if int(match.group(3)) > 50:
date_hash['yyyy'] = 1900 + int(match.group(3))
else:
date_hash['yyyy'] = 2000 + int(match.group(3))
date_hash['mm'] = ArrUtil.search_array(DateUtil.MoYs, match.group(2)) + 1
date_hash['dd'] = match.group(1)
if int(date_hash['mm']) <= 0:
return 1
elif fmt == 'DD/Mon/YYYY' or fmt == 'DD-Mon-YYYY':
match = re.search('^(\d+)[\-\/](\w+)[\-\/](\d+)$', date_str)
if not match:
return 1
date_hash['yyyy'] = int(match.group(3))
date_hash['mm'] = ArrUtil.search_array(DateUtil.MoYs, match.group(2)) + 1
date_hash['dd'] = match.group(1)
if int(date_hash['mm']) <= 0:
return 1
else:
StrUtil.print_debug("Invalid date format({})".format(fmt))
sys.exit(1)
return 0
def showCtxSearchList(db_id, request):
if len(db_id) == 0:
return render_template('error/404.html')
db_name = ""
result_cnt = 0
if app.lib.cms_lib.session.current_db:
db_name = app.lib.cms_lib.session.current_db.db_name
# 全文検索テキストを取得する
if request.method == 'GET':
ctx_search_text = request.args.get('ctx_search_text')
if request.method == 'POST':
ctx_search_text = request.form['ctx_search_text']
if not ctx_search_text:
if request.method == 'POST':
flash('検索条件を入れてください')
ctx_search_list = None
ctx_search_text = ''
elif StrUtil.lenb(ctx_search_text) > 256 and request.method == 'POST':
flash('検索条件が長すぎます')
ctx_search_list = None
else:
# 全文検索リストを取得
cmsCtxData = CmsCtxData()
ctx_cond = CtxUtil.process_ctx_search_text(ctx_search_text)
result_cnt = cmsCtxData.getCtxSearchListCnt(db_id, ctx_cond)
ctx_search_list = cmsCtxData.getCtxSearchList(db_id, ctx_cond)
note = ctx_search_text
if len(note) > 100:
note = ctx_search_text[0:100]
# 全文検索を記録する
pkgCmsLog = PkgCmsLog()
pkgCmsLog.saveOperationLog(current_user.tuid,
db_id,
operation_cd=Const.OPERATION_CD_CTX_SEARCH,
note='SearchCond: {}, ResultCnt: {}'.format(
note, result_cnt))
db.session.commit()
user_name = ''
if current_user.is_active:
user_name = current_user.get_user_name()
return render_template('ctx_search.html',
db_id=db_id,
db_name=db_name,
result_cnt=result_cnt,
user_name=user_name,
ctx_search_text=ctx_search_text,
ctx_search_list=ctx_search_list,
appVer=current_app.config['APP_VER'])
def privs_corp_select(db_id, request):
if len(db_id) == 0:
return render_template('error/404.html')
db_name = ""
result_cnt = 0
if app.lib.cms_lib.session.current_db:
db_name = app.lib.cms_lib.session.current_db.db_name
# 組織コードを取得する
if request.method == 'GET':
corp_txt = request.args.get('corp_txt')
if request.method == 'POST':
corp_txt = request.form['corp_txt']
if StrUtil.lenb(corp_txt) > 256 and request.method == 'POST':
flash('検索条件が長すぎます')
corp_list = None
else:
# 組織検索リストを取得
cmsDbCodeMaster = CmsDbCodeMaster()
result_cnt = cmsDbCodeMaster.getCorpListCnt(corp_txt)
corp_list = cmsDbCodeMaster.getCorpList(corp_txt)
note = corp_txt
if len(note) > 100:
note = corp_txt[0:100]
# 組織検索を記録する
pkgCmsLog = PkgCmsLog()
pkgCmsLog.saveOperationLog(current_user.tuid,
db_id,
operation_cd=Const.OPERATION_CD_CORP_SEARCH,
note='SearchCond: {}, ResultCnt: {}'.format(
note, result_cnt))
db.session.commit()
form = {
"db_id": db_id,
"db_name": db_name,
"corp_txt": corp_txt,
"wait_msg": Const.WAIT_MSG,
"select_corp_msg": Const.SELECT_CORP_MSG,
"zero_list_msg": Const.ZERO_LIST_MSG,
}
return render_template('cms_db_admin/privs_corp_select.html',
title='Corp Select',
form=form,
result_cnt=result_cnt,
corp_list=corp_list,
appVer=current_app.config['APP_VER'])
def addOperationLog(self,
cmsOperationLog,
operation_cd,
object_id='',
object_type='',
note=''):
cmsOperationLog.operation_date = datetime.now()
cmsOperationLog.operation_cd = operation_cd
cmsOperationLog.object_id = object_id
cmsOperationLog.object_type = object_type
cmsOperationLog.ip_addr = StrUtil.get_ip_addr()
cmsOperationLog.note = note
return db.session.add(cmsOperationLog)
def set_cookie(session_cookie_name, tuid, redirectUrl):
random_str = '{0}{1}'.format(StrUtil.make_random_str(25),
str(CreateSeq.getSessionIdSeq()).zfill(9))
StrUtil.print_debug('random_str:{}'.format(str(random_str)))
cst = CmsSessionTable(session_cookie_name, random_str, tuid)
db.session.add(cst)
db.session.commit()
if request.method == 'GET':
next_url = request.args.get('next_url')
else:
next_url = request.form['next_url']
if not next_url:
next_url = redirectUrl
else:
next_url = urllib.parse.unquote(next_url)
StrUtil.print_debug('next_url:{}'.format(str(next_url)))
response = make_response(redirect(next_url))
response.set_cookie(session_cookie_name, random_str)
return response
def doAdminLogin(form):
# リダイレクトURLを取得する
next_url = _get_next_url()
if form.validate_on_submit():
user = User.query.filter_by(tuid=form.user_id.data).first()
if user is None or not PkgUserAuth.check_passwd_for_cms(
form.user_id.data, form.password.data):
# エラーログを記録する
pkgCmsErrLog = PkgCmsErrLog()
pkgCmsErrLog.saveErrLog('LOGIN_ERROR', str(form.user_id.data), '',
'')
db.session.commit()
flash('invalid user_id or password')
return redirect(
url_for('adm_login',
user_id=form.user_id.data,
next_url=next_url))
login_user(user, False)
session['last_login_user_id'] = form.user_id.data
return set_cookie(
StrUtil.get_safe_config(current_app, 'CMS_SYS_COOKIE'),
current_user.tuid, url_for('adm_index'))
# ログイン情報を保持する
last_login_user_id = StrUtil.get_safe_edit_mode('last_login_user_id',
session)
user_id = request.args.get('user_id') or last_login_user_id
if user_id:
form.user_id.data = user_id
return render_template('cms_admin/login.html',
form=form,
next_url=next_url,
systemVersion="Developer Version 1.00")
def swhEditMode():
db_id = app.lib.cms_lib.session.get_db_id()
if db_id + '_is_edit_mode' in session:
if StrUtil.get_safe_edit_mode(str(db_id) + '_is_edit_mode', session):
session[str(db_id) + '_is_edit_mode'] = False
else:
cmsSecurity = PkgCmsSecurity()
if cmsSecurity.isDbEditable(db_id, current_user.get_id()) == False:
return render_template('error/noPrivs.html',
errorMsg='編集権限がありません。')
session[str(db_id) + '_is_edit_mode'] = True
else:
session[str(db_id) + '_is_edit_mode'] = False
return redirect(url_for('index', db_id=db_id, jtree_store='keep'))
def saveOperationLog(self,
user_id,
db_id,
operation_cd,
object_id='',
object_type='',
note=''):
ip_addr = StrUtil.get_ip_addr()
db.session.execute(
'begin ' + self.package_name + '.save_operation_log' +
'(:user_id, :operation_cd, :object_id, :object_type, :db_id, :note, :ip_addr); '
+ 'end;', {
'user_id': user_id,
'operation_cd': operation_cd,
'object_id': object_id,
'object_type': object_type,
'db_id': db_id,
'note': note,
'ip_addr': ip_addr
})
def wrapper(*args, **kwargs):
logout_user()
StrUtil.print_debug('adm_login_required. func=[{}]'.format(
str(func.__name__)))
session_id = app.lib.cms_lib.session.get_session_id(
StrUtil.get_safe_config(current_app, 'CMS_SYS_COOKIE'))
if session_id:
StrUtil.print_debug(
'login_required. session_cookie_name:{0} session_id:{1}'.
format('ADMIN_SESSION_COOKIE', session_id))
cst = CmsSessionTable.get_adm_session_info(session_id)
if cst is None:
flash('invalid user_id or password')
return redirect(url_for('adm_login'))
# 取得したユーザIDでユーザ情報を取得する
user = User.query.filter_by(tuid=cst.user_id).first()
if user is None:
flash('invalid user_id or password')
return redirect(url_for('adm_login'))
# 管理者権限チェック
pkgCmsSecurity = PkgCmsSecurity()
if not pkgCmsSecurity.isAdminUser(user.tuid):
flash('利用権限がありません')
return redirect(
UserAuth._get_redirect_url(url_for('adm_login')))
login_user(user, False)
else:
StrUtil.print_debug('login_required. no session id got.')
return redirect(
UserAuth._get_redirect_url(url_for('adm_login')))
return func(*args, **kwargs)
def check_input_form_data_by_db(param_prop):
err_msgs = []
try:
if 'table_name' not in param_prop \
or 'col_prop' not in param_prop:
err_msgs.append(Const.INVALID_PARAM_ERR_MSG)
param_prop['err_msgs'].extend(err_msgs)
return
col_prop = param_prop['col_prop']
if 'cname' not in col_prop \
or 'input_value' not in col_prop \
or 'db_field' not in col_prop:
err_msgs.append(Const.INVALID_PARAM_ERR_MSG)
param_prop['err_msgs'].extend(err_msgs)
return
user_tab_columns = DbUtil.get_user_tab_columns_hash(
param_prop['table_name'])
for idx in range(0, len(col_prop['cname'])):
value = col_prop['input_value'][idx]
db_field = col_prop['db_field'][idx]
# 必須チェック
if 'nullable' in user_tab_columns[db_field]:
if user_tab_columns[db_field][
'nullable'] == 'N' and not value:
err_msgs.append(
Const.REQUIRED_MSG.format(col_prop['cname'][idx]))
continue
if not value:
continue
if 'data_type' in user_tab_columns[
db_field] and 'data_length' in user_tab_columns[
db_field]:
data_type = user_tab_columns[db_field]['data_type']
data_length = user_tab_columns[db_field]['data_length']
# 文字列チェック
if data_type == 'VARCHAR2' or data_type == 'CHAR':
# 桁数チェック
if StrUtil.lenb(value) > int(data_length):
err_msgs.append(
Const.LENGTH_OVER_MSG.format(
col_prop['cname'][idx], str(data_length)))
# 数字チェック
elif data_type == 'NUMBER':
"""
if re.search(',', str(data_length)):
t = value
t = re.sub(r'[^\.]', r'', t)
if len(t) > 1 or re.search('[^0-9^\.]', value):
err_msgs.append(
Const.NUMERICAL_VALUE_REQUIRED_MSG.format(col_prop['cname'][idx]))
else:
if re.search('[^0-9]', value):
err_msgs.append(
Const.INTEGER_VALUE_REQUIRED_MSG.format(col_prop['cname'][idx]))
"""
if NumUtil.is_number_data(str(value)) != 1:
err_msgs.append(
Const.NUMERICAL_VALUE_REQUIRED_MSG.format(
col_prop['cname'][idx]))
else:
num_prop = {
'sign_ref': '',
'i_ref': '',
'f_ref': ''
}
NumUtil.split_number(str(value), num_prop)
if 'data_precision' in user_tab_columns[db_field] \
and user_tab_columns[db_field]['data_precision'] is not None:
if len(num_prop['i_ref']) > int(
user_tab_columns[db_field]
['data_precision']):
err_msgs.append(
Const.INTEGRAL_PART_OUT_OF_RANGE_MSG.
format(
col_prop['cname'][idx],
str(user_tab_columns[db_field]
['data_precision'])))
if 'data_scale' in user_tab_columns[db_field] \
and user_tab_columns[db_field]['data_scale'] is not None:
if len(num_prop['f_ref']) > int(
user_tab_columns[db_field]
['data_scale']):
err_msgs.append(
Const.FRACTIONAL_PART_OUT_OF_RANGE_MSG.
format(
col_prop['cname'][idx],
str(user_tab_columns[db_field]
['data_scale'])))
# 日付チェック
elif data_type == 'DATE':
if DateUtil.check_date_format(value,
Const.DATE_FORMAT) != 0:
err_msgs.append(
Const.AVAILABLE_DATE_REQUIRED_MSG.format(
col_prop['cname'][idx], value))
# 文字列「CLOB」チェック
elif data_type == 'CLOB':
if len(value) > 10 * 1024:
err_msgs.append(
Const.LENGTH_OVER_MSG.format(
col_prop['cname'][idx], '10,000'))
param_prop['err_msgs'].extend(err_msgs)
except Exception as e:
tb = sys.exc_info()[2]
param_prop['err_msgs'].extend(str(e.with_traceback(tb)))
StrUtil.print_error(
'check_input_form_data_by_db error_msg:{}'.format(
str(e.with_traceback(tb))))
def wrapper(*args, **kwargs):
logout_user()
StrUtil.print_debug('login_required. func=[{}]'.format(
str(func.__name__)))
db_id = app.lib.cms_lib.session.get_db_id()
if not db_id:
flash('[db_id]パラメータが必要です')
return redirect(url_for('login'))
# データベースオブジェクトを取得する
current_db = app.lib.cms_lib.session.get_current_db(db_id)
# グローバル変数に設定する
app.lib.cms_lib.session.current_db = current_db
if not current_db:
flash('[db_id:{}]情報を取得できません'.format(db_id))
return redirect(url_for('login', db_id=db_id))
StrUtil.print_debug('login_required. cur_db.db_id=[{}]'.format(
str(current_db.db_id)))
session_id = app.lib.cms_lib.session.get_session_id(
current_db.session_cookie_name)
if session_id:
StrUtil.print_debug(
'login_required. session_cookie_name:{0} session_id:{1}'.
format(current_db.session_cookie_name, session_id))
# セッションテーブルからユーザIDを取得する（有効期限：一週間）
cst = CmsSessionTable.get_session_info(
current_db.session_cookie_name, session_id)
if cst is None:
flash('invalid user_id or password')
return redirect(url_for('login', db_id=db_id))
# 取得したユーザIDでユーザ情報を取得する
user = User.query.filter_by(tuid=cst.user_id).first()
if user is None:
flash('invalid user_id or password')
return redirect(url_for('login', db_id=db_id))
# アクセス権限チェック
pkgIpAddrUtil = PkgIpAddrUtil()
id_addr = StrUtil.get_ip_addr()
if not id_addr or not pkgIpAddrUtil.isDbIpAddrVisible(
db_id, id_addr):
# ログ出力 DBの参照権限なし
PkgCmsErrLog().saveErrLog(Const.IP_ADDRESS_ERROR,
user.tuid, str(current_db.db_id))
db.session.commit()
flash('利用権限がありません')
return redirect(url_for('login', db_id=db_id))
# 参照権限チェック
pkgCmsSecurity = PkgCmsSecurity()
if not pkgCmsSecurity.isDbVisible(db_id, user.tuid):
# ログ出力 DBの参照権限なし
PkgCmsErrLog().saveErrLog(Const.DB_PRIVS_ERROR, user.tuid,
str(current_db.db_id))
db.session.commit()
flash('このDBを参照する権限がありません')
return redirect(url_for('login', db_id=db_id))
StrUtil.print_debug('login_required. user_id=[{}]'.format(
str(current_db.db_id)))
login_user(user, False)
session['db_id'] = db_id
else:
StrUtil.print_debug('login_required. no session id got.')
return redirect(
UserAuth._get_redirect_url(
url_for('login', db_id=current_db.db_id)))
return func(*args, **kwargs)
'created_by',
'created_at',
]
input_value = [
'1',
'ああああああああああああああああああああああああああああああああああああああああ',
'test body',
'10.24',
'1',
'z02039n0',
'2020-02-18',
]
db_field = [
'ID',
'TITLE',
'BODY',
'AMOUNT',
'ORDER_BY',
'CREATED_BY',
'CREATED_AT',
]
col_prop = {'cname': cname, 'input_value': input_value, 'db_field': db_field}
param_prop = {
'err_msgs': [],
'table_name': 'PYTHON_TEST001',
'col_prop': col_prop
}
DbUtil.check_input_form_data_by_db(param_prop)
StrUtil.print_debug(list(param_prop['err_msgs']))
def save_and_get_filename(self):
upload_temp_dir = StrUtil.get_safe_config(current_app,
'UPLOAD_TMP_DIR_PATH')
if upload_temp_dir and not os.path.exists(upload_temp_dir):
os.makedirs(upload_temp_dir, exist_ok=True)
return self._save_file_temporarily(upload_temp_dir)
def check_input_form_data_by_prop(param_prop):
err_msgs = []
try:
if 'form' not in param_prop:
err_msgs.append(Const.INVALID_PARAM_ERR_MSG)
param_prop['err_msgs'].extend(err_msgs)
return
form = param_prop['form']
for pro in param_prop['pro_list']:
property_type = pro.get("property_type")
if "KEYWORD" == property_type:
continue
col_name = pro.get("db_column_name").lower()
value = form.__dict__[col_name].data
if col_name.startswith("num_"):
if len(value) > 0:
value = float(value)
else:
value = ''
# 必須チェック
if pro.get("nullable") == 'FALSE' and not value:
err_msgs.append(
Const.REQUIRED_MSG.format(pro.get("property_name")))
continue
if not value:
continue
# 数字チェック
if "NUMBER" == property_type:
if NumUtil.is_number_data(str(value)) != 1:
err_msgs.append(
Const.NUMERICAL_VALUE_REQUIRED_MSG.format(
pro.get("property_name")))
else:
num_prop = {'sign_ref': '', 'i_ref': '', 'f_ref': ''}
NumUtil.split_number(str(value), num_prop)
if (len(num_prop['i_ref']) +
len(num_prop['f_ref'])) > int(
pro.get("i_len")):
err_msgs.append(
Const.INTEGRAL_PART_OUT_OF_RANGE_MSG.format(
pro.get("property_name"),
str(pro.get("i_len") - pro.get("f_len"))))
if len(num_prop['f_ref']) > int(pro.get("f_len")):
err_msgs.append(
Const.FRACTIONAL_PART_OUT_OF_RANGE_MSG.format(
pro.get("property_name"),
str(pro.get("f_len"))))
# 日付チェック
elif 'DATE' == property_type:
if DateUtil.check_date_format(value,
Const.DATE_FORMAT) != 0:
err_msgs.append(
Const.AVAILABLE_DATE_REQUIRED_MSG.format(
pro.get("property_name"), value))
# 文字列チェック
elif 'TEXT' == property_type or 'TEXT_MULTILINE' == property_type:
# 桁数チェック
if pro.get("data_size"):
if StrUtil.lenb(value) > int(pro.get("data_size")):
err_msgs.append(
Const.LENGTH_OVER_MSG.format(
pro.get("property_name"),
str(pro.get("data_size"))))
# バリデータチェック（正式表現）
re_cond = pro.get('validate_rule')
if re_cond and len(value) > 0:
try:
if not re.search(re_cond, value):
err_msgs.append(
pro.get('validate_err_msg').replace(
'<#DATA#>', value))
except Exception as e:
tb = sys.exc_info()[2]
StrUtil.print_error(
'check_input_form_data_by_prop validate_rule:{} error_msg:{}'
.format(re_cond, str(e.with_traceback(tb))))
param_prop['err_msgs'].extend(err_msgs)
except Exception as e:
tb = sys.exc_info()[2]
param_prop['err_msgs'].extend(str(e.with_traceback(tb)))
StrUtil.print_error(
'check_input_form_data_by_prop error_msg:{}'.format(
str(e.with_traceback(tb))))
def save_privs_user(func, request):
if len(func) == 0:
return render_template('error/404.html')
res, msg = {}, "OK"
form = PrivsUserForm()
err_msgs = []
isSaveError = False
db_id = request.form["db_id"]
if func == Const.UPDATE_PRIVS_USER:
old_corp_cd = request.form["old_corp_cd"]
old_dept_cd = request.form["old_dept_cd"]
old_privs_type = request.form["old_privs_type"]
corp_cd = request.form["corp_cd"]
dept_cd = request.form["dept_cd"]
tuid = request.form["user_id"]
privs_type = request.form["privs_type"]
cmsDbPrivsUser = CmsDbPrivsUser()
# 保存処理(新規、編集)
if func == Const.ADD_PRIVS_USER or func == Const.UPDATE_PRIVS_USER:
if func == Const.ADD_PRIVS_USER:
privsUser = cmsDbPrivsUser.getPrivsUser(db_id, corp_cd, dept_cd,
tuid, privs_type)
if privsUser and privsUser.tuid == tuid:
err_msgs.append(Const.DATA_EXIST_ERR_MSG)
isSaveError = True
user_info = User.getUserInfo(tuid)
if not user_info:
err_msgs.append(Const.USER_ID_NOT_EXIST_ERR_MSG)
isSaveError = True
else:
privsUser = cmsDbPrivsUser.getPrivsUser(db_id, corp_cd, dept_cd,
tuid, privs_type)
# 登録しようとするデータが存在すれば（自分自身以外）、更新できないよう
if privsUser and \
(privsUser.corp_cd != old_corp_cd
or privsUser.dept_cd != old_dept_cd):
err_msgs.append(Const.DATA_EXIST_ERR_MSG)
isSaveError = True
if not isSaveError:
# 入力チェックする
cname = [
"Corp Cd",
"Department",
"User Id",
"Privs Type",
]
input_value = [
corp_cd,
dept_cd,
tuid,
privs_type,
]
db_field = [
"MANAGEMENT_CORP_CD",
"DEPT_CD",
"TUID",
"PRIVS_TYPE",
]
col_prop = {
'cname': cname,
'input_value': input_value,
'db_field': db_field
}
param_prop = {
'err_msgs': [],
'table_name': 'CMS_DB_PRIVS_USER',
'form': form,
'col_prop': col_prop
}
DbUtil.check_input_form_data_by_db(param_prop)
if len(param_prop['err_msgs']) > 0:
err_msgs = param_prop['err_msgs']
isSaveError = True
if request.method == 'POST' and not isSaveError:
# form = DatabaseForm(request.form)
if form.validate_on_submit() == False:
StrUtil.print_debug("validate error.")
else:
try:
if func == Const.ADD_PRIVS_USER:
addPrivsUser = CmsDbPrivsUser(db_id, corp_cd, dept_cd,
tuid, privs_type)
cmsDbPrivsUser.addPrivsUser(addPrivsUser, tuid)
# Privs User登録を記録する
pkgCmsLog = PkgCmsLog()
pkgCmsLog.saveOperationLog(
current_user.tuid,
db_id,
operation_cd=Const.OPERATION_CD_ADD_PRIVS_USER,
object_id=None,
object_type=None,
note=tuid)
db.session.commit()
else:
uptPrivsUser = cmsDbPrivsUser.uptPrivsUser(
db_id, old_corp_cd, old_dept_cd, tuid,
old_privs_type, corp_cd, dept_cd, privs_type,
current_user.get_id())
# Privs User変更を記録する
pkgCmsLog = PkgCmsLog()
pkgCmsLog.saveOperationLog(
current_user.tuid,
db_id,
operation_cd=Const.OPERATION_CD_UPDATE_PRIVS_USER,
object_id=None,
object_type=None,
note=tuid)
db.session.commit()
except Exception as e:
db.session.rollback()
tb = sys.exc_info()[2]
StrUtil.print_error(
"Database save failed. error_msg:{}".format(
str(e.with_traceback(tb))))
err_msgs.append('Database save failed.')
# 削除処理
elif func == Const.DELETE_PRIVS_USER:
try:
cmsDbPrivsUser.delPrivsUser(db_id, corp_cd, dept_cd, tuid,
privs_type, current_user.get_id())
# Privs User削除を記録する
pkgCmsLog = PkgCmsLog()
pkgCmsLog.saveOperationLog(
current_user.tuid,
db_id,
operation_cd=Const.OPERATION_CD_DELETE_PRIVS_USER,
object_id=None,
object_type=None,
note=tuid)
db.session.commit()
except Exception as e:
db.session.rollback()
tb = sys.exc_info()[2]
StrUtil.print_error("Database save failed. error_msg:{}".format(
str(e.with_traceback(tb))))
err_msgs.append('Database delete failed.')
res = {**res, **{"err_msgs": err_msgs}}
return Response(json.dumps(res))
def json_serial(obj):
# 日付型の場合には、文字列に変換します
if isinstance(obj, (datetime, date)):
return obj.strftime(StrUtil.get_safe_config(current_app, 'STRFTIME_TIME_FORMAT'))
raise TypeError("Type %s not serializable" % type(obj))
def check_input_form_data_by_db2(param_prop):
err_msgs = []
try:
if 'form' not in param_prop \
or 'table_name' not in param_prop \
or 'col_prop' not in param_prop:
err_msgs.append(Const.INVALID_PARAM_ERR_MSG)
param_prop['err_msgs'] = err_msgs
return
col_prop = param_prop['col_prop']
if 'cname' not in col_prop \
or 'input_field' not in col_prop \
or 'db_field' not in col_prop:
err_msgs.append(Const.INVALID_PARAM_ERR_MSG)
param_prop['err_msgs'] = err_msgs
return
form = param_prop['form']
user_tab_columns = DbUtil.get_user_tab_columns_hash(
param_prop['table_name'])
for idx in range(0, len(col_prop['cname'])):
input_field = col_prop['input_field'][idx]
value = str(form.__dict__[input_field].data)
db_field = col_prop['db_field'][idx]
# 必須チェック
if 'nullable' in user_tab_columns[db_field]:
if user_tab_columns[db_field][
'nullable'] == 'N' and not value:
err_msgs.append(
Const.REQUIRED_MSG.format(col_prop['cname'][idx]))
# 桁数チェック
if 'data_type' in user_tab_columns[
db_field] and 'data_length' in user_tab_columns[
db_field]:
data_type = user_tab_columns[db_field]['data_type']
data_length = user_tab_columns[db_field]['data_length']
if data_type == 'VARCHAR2' or data_type == 'CHAR':
if StrUtil.lenb(value) > int(data_length):
err_msgs.append(
Const.LENGTH_OVER_MSG.format(
col_prop['cname'][idx], str(data_length)))
elif data_type == 'NUMBER':
if re.search(',', str(data_length)):
t = value
t = re.sub(r'[^\.]', r'', t)
if len(t) > 1 or re.search('[^0-9^\.]', value):
err_msgs.append(
Const.NUMERICAL_VALUE_REQUIRED_MSG.format(
col_prop['cname'][idx]))
else:
if re.search('[^0-9]', value):
err_msgs.append(
Const.INTEGER_VALUE_REQUIRED_MSG.format(
col_prop['cname'][idx]))
elif data_type == 'DATE':
if DateUtil.check_date_format(value,
Const.DATE_FORMAT) != 0:
err_msgs.append(
Const.AVAILABLE_DATE_REQUIRED_MSG.format(
col_prop['cname'][idx], value))
param_prop['err_msgs'] = err_msgs
except Exception as e:
tb = sys.exc_info()[2]
param_prop['err_msgs'] = str(e.with_traceback(tb))
StrUtil.print_error(
'check_input_form_data_by_db error_msg:{}'.format(
str(e.with_traceback(tb))))
def save_privs_dept(func, request):
if len(func) == 0:
return render_template('error/404.html')
res, msg = {}, "OK"
form = PrivsDeptForm()
err_msgs = []
isSaveError = False
db_id = request.form["db_id"]
if func == Const.UPDATE_PRIVS_DEPT:
old_div_cd = request.form["old_div_cd"]
old_dept_cd = request.form["old_dept_cd"]
old_emp_type_cd = request.form["old_emp_type_cd"]
old_working_type_cd = request.form["old_working_type_cd"]
old_privs_type = request.form["old_privs_type"]
corp_cd = request.form["corp_cd"]
div_cd = request.form["div_cd"]
dept_cd = request.form["dept_cd"]
emp_type_cd = request.form["emp_type_cd"]
working_type_cd = request.form["working_type_cd"]
privs_type = request.form["privs_type"]
cmsDbPrivsDept = CmsDbPrivsDept()
cmsDbCodeMaster = CmsDbCodeMaster()
OPERATION_NOTE = "CORP_CD={}, DIV_CD={}, DEPT_CD={}, EMP_TYPE_CD={}, WORKING_TYPE_ID={}, PRIVS_TYPE={}"
# 保存処理(新規、編集)
if func == Const.ADD_PRIVS_DEPT or func == Const.UPDATE_PRIVS_DEPT:
if func == Const.ADD_PRIVS_DEPT:
isCorpCdExist = cmsDbCodeMaster.checkCorpCdExist(corp_cd)
if not isCorpCdExist:
err_msgs.append(
Const.DATA_NOT_EXIST_ERR_MSG.replace("%s", "Corp Cd"))
isSaveError = True
privsDept = cmsDbPrivsDept.getPrivsDept(db_id, corp_cd, div_cd,
dept_cd, emp_type_cd,
working_type_cd,
privs_type)
if privsDept and privsDept.corp_cd == corp_cd:
err_msgs.append(Const.DATA_EXIST_ERR_MSG)
isSaveError = True
else:
privsDept = cmsDbPrivsDept.getPrivsDept(db_id, corp_cd, div_cd,
dept_cd, emp_type_cd,
working_type_cd,
privs_type)
# 登録しようとするデータが存在すれば（自分自身以外）、更新できないよう
if privsDept and \
(privsDept.div_cd != old_div_cd
or privsDept.dept_cd != old_dept_cd
or privsDept.emp_type_cd != old_emp_type_cd
or privsDept.working_type_cd != old_working_type_cd):
err_msgs.append(Const.DATA_EXIST_ERR_MSG)
isSaveError = True
if not isSaveError:
# 入力チェックする
cname = [
"Corp Cd",
"Div Cd",
"Dept Cd",
"Emp Type",
"Working Type",
"Privs Type",
]
input_value = [
corp_cd,
div_cd,
dept_cd,
emp_type_cd,
working_type_cd,
privs_type,
]
db_field = [
"MANAGEMENT_CORP_CD",
"DIV_CD",
"DEPT_CD",
"EMP_TYPE_CD",
"WORKING_TYPE_CD",
"PRIVS_TYPE",
]
col_prop = {
'cname': cname,
'input_value': input_value,
'db_field': db_field
}
param_prop = {
'err_msgs': [],
'table_name': 'CMS_DB_PRIVS_DEPT',
'form': form,
'col_prop': col_prop
}
DbUtil.check_input_form_data_by_db(param_prop)
if len(param_prop['err_msgs']) > 0:
err_msgs = param_prop['err_msgs']
isSaveError = True
if request.method == 'POST' and not isSaveError:
if form.validate_on_submit() == False:
StrUtil.print_debug("validate error.")
else:
try:
if func == Const.ADD_PRIVS_DEPT:
addPrivsDept = CmsDbPrivsDept(db_id, corp_cd, div_cd,
dept_cd, emp_type_cd,
working_type_cd,
privs_type)
cmsDbPrivsDept.addPrivsDept(addPrivsDept,
current_user.get_id())
# Privs Dept登録を記録する
pkgCmsLog = PkgCmsLog()
pkgCmsLog.saveOperationLog(
current_user.get_id(),
db_id,
operation_cd=Const.OPERATION_CD_ADD_PRIVS_DEPT,
object_id=None,
object_type=None,
note=OPERATION_NOTE.format(corp_cd, div_cd,
dept_cd, emp_type_cd,
working_type_cd,
privs_type))
db.session.commit()
else:
cmsDbPrivsDept.uptPrivsDept(
db_id, corp_cd, div_cd, dept_cd, emp_type_cd,
working_type_cd, old_div_cd, old_dept_cd,
old_emp_type_cd, old_working_type_cd,
old_privs_type, current_user.get_id())
# Privs Dept変更を記録する
pkgCmsLog = PkgCmsLog()
pkgCmsLog.saveOperationLog(
current_user.tuid,
db_id,
operation_cd=Const.OPERATION_CD_UPDATE_PRIVS_DEPT,
object_id=None,
object_type=None,
note=OPERATION_NOTE.format(corp_cd, div_cd,
dept_cd, emp_type_cd,
working_type_cd,
privs_type))
db.session.commit()
except Exception as e:
db.session.rollback()
tb = sys.exc_info()[2]
StrUtil.print_error(
"Database save failed. error_msg:{}".format(
str(e.with_traceback(tb))))
err_msgs.append('Database save failed.')
# 削除処理
elif func == Const.DELETE_PRIVS_DEPT:
try:
cmsDbPrivsDept.delPrivsDept(db_id, corp_cd, div_cd, dept_cd,
emp_type_cd, working_type_cd,
privs_type, current_user.get_id())
# Privs Dept削除を記録する
pkgCmsLog = PkgCmsLog()
pkgCmsLog.saveOperationLog(
current_user.tuid,
db_id,
operation_cd=Const.OPERATION_CD_DELETE_PRIVS_DEPT,
object_id=None,
object_type=None,
note=OPERATION_NOTE.format(corp_cd, div_cd, dept_cd,
emp_type_cd, working_type_cd,
privs_type))
db.session.commit()
except Exception as e:
db.session.rollback()
tb = sys.exc_info()[2]
StrUtil.print_error("Database save failed. error_msg:{}".format(
str(e.with_traceback(tb))))
err_msgs.append('Database delete failed.')
res = {**res, **{"err_msgs": err_msgs}}
return Response(json.dumps(res))
