def set_CSB(puser_id, ENVIRO):
q_str =""" insert into csb values ( %(session_id)s,
now() + interval '30 minutes' )"""
CSB = uuid.uuid1().hex
m.run_sql_command(ENVIRO.get('CONN'), q_str,{'session_id':str(puser_id)+ CSB})
return CSB
def list_category(POST,
GET,
ENVIRO,
COOKIES,
CLIENT_STATE,
CONTEXT,
TEMPLATE,
TEMPLATE_ENGINE,
CSB='',
TEMPLATE_STACK={}):
_key = -1
if 'cat_id' in GET:
_key = int(''.join(GET.get('cat_id', '-1')))
_prev_id = int(''.join(GET.get('prev_id', '9999999999')))
_count = int(''.join(GET.get('count', '-1')))
elif 'cat_id' in POST:
_key = int(''.join(POST.get('cat_id', '-1')))
_prev_id = int(''.join(POST.get('prev_id', '9999999999')))
_count = int(''.join(POST.get('count', '-1')))
q_str = """ select '?blog_id=' || blog_id::text as url, blog_title as Name,
Blog_title as LinkText
from blog, blog_cats
where bc_blog_id = blog_id
and bc_cat_id = %(cat_id)s
and blog_id <= %(prev_id)s
ordered by blog_id desc
limit by %(count)s)
"""
_rec = m.run_sql_command(ENVIRO.get('CONN'), q_str, {
'cat_id': _key,
'prev_id': _prev_id,
'count': _count
})
_list_arts = m.build_url_links(_rec,
p_url_path='',
p_app_command='view_category',
ENVIRO=ENVIRO)
CONTEXT.update({'list_articles': _list_arts})
_rec = m.run_sql_command(
ENVIRO.get('CONN'),
"select cat_long from catergory where cat_id = %(cat_id)", {
'cat_id': _key,
})
CONTEXT.update({'PAGE_NAME': "List of Blogs in " + _rec[0]['cat_long']})
_ouput = TEMPLATE_ENGINE(pfile=TEMPLATE,
ptype='string',
pcontext=CONTEXT,
preturn_type='string',
pcache_path=ENVIRO.get(
'TEMPLATE_CACHE_PATH_PRE_RENDER', ''))
return True, _ouput, ENVIRO, CLIENT_STATE, COOKIES, CSB
def save_blog(POST,
GET,
ENVIRO,
CLIENT_STATE,
COOKIES,
CONTEXT,
TEMPLATE,
TEMPLATE_ENGINE,
CSB='',
TEMPLATE_STACK={}):
if 'blog_id' in GET:
_key = int(''.join(GET.get('blog_id')), '-1')
_search_tags = ''.join(GET.get('search_tags', '')).split(',')
_text = ''.join(GET.get('content', ''))
_title = ''.join(GET.get('title', ''))
elif 'blog_id' in POST:
_key = int(''.join(POST.get('blog_id', '-1')))
_search_tags = ''.join(POST.get('search_tags', '')).split(',')
_text = ''.join(POST.get('content', ''))
_title = ''.join(POST.get('title', ''))
else:
return False, '', ENVIRO, CLIENT_STATE, COOKIES, CSB
CONTEXT.update({'PAGE_NAME': "New Blog Was Saved"})
_sql = """ insert into blog values (
%(blog_id)s ,
%(blog_user_id)s ,
now(),
%(blog_htmltext)s ,
%(search_tags)s ,
to_tsvector(%(tvs)s),
%(blog_title)s )
on Conflict ( blog_id ) do Update set
blog_htmltext = %(blog_htmltext)s ,
search_tags = %(search_tags)s,
tvs = to_tsvector(%(tvs)s),
blog_title =%(blog_title)s ;"""
_topadd = {
'blog_id': _key,
'blog_user_id': int(ENVIRO.get('SEC', {}).get('USER_ID', -1)),
'blog_htmltext': _text,
'search_tags': _search_tags,
'tvs': _title + extext(_text),
'blog_title': _title
}
m.run_sql_command(ENVIRO.get('CONN'), _sql, _topadd)
#GET = {'blogkey':_key}
#_is_in_cache, TEMPLATE, _template_name = m.build_template('view', TEMPLATE_STACK['view'], ENVIRO=ENVIRO)
#return view({}, GET, ENVIRO, CLIENT_STATE, COOKIES, CONTEXT,TEMPLATE, TEMPLATE_ENGINE, '', TEMPLATE_STACK )
_r, url_path = m.furl_get_to_app('view', ENVIRO, {'blog_id': _key})
if _r:
ENVIRO = m.client_redirect(url_path, ENVIRO)
return True, '', ENVIRO, CLIENT_STATE, COOKIES, CSB
def get_child_comments(POST,
GET,
ENVIRO,
CLIENT_STATE,
COOKIES,
CONTEXT,
TEMPLATE,
TEMPLATE_ENGINE,
CSB='',
TEMPLATE_STACK={}):
if 'bc_id' in GET and 'limit' in GET and 'offset' in GET:
q_str = """ select bc_id , bc_blog_id , bc_parent_bc_id,
bc_user_id, bc_date, bc_comment, bc_title,
(select count(bc_parent_bc_id) from blog_comments dd where dd.bc_id = bc_id) as bc_child_count
from blog_comments where
bc_id = %(bc_id)s
limit %(limit)s offset %(offset)s """
_rec = m.run_sql_command(
ENVIRO.get('CONN'), q_str, {
'bc_id': g.GET.get(bc_id, 0),
'p_limit': g.GET.get('limit', 0),
'p_offset': p_offset.get('offset', 50)
})
_rec = cur.fetchall()
if len(_rec) > 0:
CONTEXT.update({'child_comments': _rec})
_ouput = TEMPLATE_ENGINE(pfile=TEMPLATE,
ptype='string',
pcontext=CONTEXT,
preturn_type='string')
return True, _output, ENVIRO, CLIENT_STATE, COOKIES, CSB
return False, '', ENVIRO, CLIENT_STATE, COOKIES, CSB
def get_cert_detail(ENVIRO={}, pcert_id=-1):
_where = ''
_sql = """ select caldetail_id,
caldetail_calhead_id,
caldetail_calprorules_id ,
caldetails_seqence ,
caldetail_std_asrcv ,
caldetail_mut_asrcv ,
caldetail_std_afterrcv ,
caldetail_mut_afterrcv ,
caldetail_timestamp ,
caldetail_datatype ,
caldetail_descrip_text_datacollect ,
caldetail_default_text ,
caldetail_checkoff_descrip ,
caldetail_checkoff_values ,
caldetail_dev_asrcv_low ,
caldetail_checkoff_flag ,
caldetail_dev_afterrcv_low ,
caldetail_dev_asrcv_high ,
caldetail_dev_afterrcv_high ,
caldetail_devtext ,
caldetail_standard_altvalue,
caldetail_checkoff_na
from mcal.caldetail """
if cert_id > -1:
_where = " where caldetail_calhead_id = %(cert_id) "
_sql = _sql + _where
_sql = _sql + " order by caldetails_seqence "
return m.run_sql_command(ENVIRO.get('CONN'), _sql, {'cert_id': pcert_id})
def submit_cal_save(POST={},
GET={},
ENVIRO={},
CLIENT_STATE={},
COOKIES={},
CONTEXT={},
TEMPLATE='',
TEMPLATE_ENGINE=None,
CSB='',
TEMPLATE_STACK={}):
_sql = ''
for _k, _v in POST.items():
_parts = _k.split('-')
if _parts[0] in (
'caldetail_mut_afterrcv',
'caldetail_mut_asrcv',
'caldetail_std_afterrcv',
'caldetail_std_asrcv',
):
_sql = _sql + "Update mcal.caldetail set %s = %s where caldetail_id = %s ; \r\n " % (
_parts[0], _v[0], _parts[1])
elif _parts[0] == 'caldetail_default_text':
_sql = _sql + "Update mcal.caldetail set %s = $$%s$$ where caldetail_id = %s ; \r\n " % (
_parts[0], _v[0], _parts[1])
elif _parts[0] in ('caldetail_checkoff_flag', 'caldetail_checkoff_na'):
_sql = _sql + "Update mcal.caldetail set %s = %s where caldetail_id = %s ; \r\n " % (
_parts[0], _v[0], _parts[1])
_sql = 'begin; ' + _sql + ' rollback;'
_r = m.run_sql_command(ENVIRO.get('CONN'), _sql)
return get_cert_edit(POST, GET, ENVIRO, CLIENT_STATE, COOKIES, CONTEXT,
TEMPLATE, TEMPLATE_ENGINE, CSB, TEMPLATE_STACK)
def link_to_sales_order(POST={},
GET={},
ENVIRO={},
CLIENT_STATE={},
COOKIES={},
CONTEXT={},
TEMPLATE='',
TEMPLATE_ENGINE=None,
CSB='',
TEMPLATE_STACK={}):
_calprohd = POST.get('calhead_id', [''])
_sales_order = POST.get('sales_order', [''])
_so_line = POST.get('so_line', [''])
if _calprohd[0] > '' or _sales_order[0] > '':
_sql = 'select mcal.set_cert_header( %(calprohd)s, %(sales_order)s, %(_so_line)s);'
_r = m.run_sql_command(
ENVIRO.get('CONN'), _sql, {
'calprohd': _calprohd[0],
'sales_order': _sales_order[0],
'_so_line': _so_line[0]
})
headers = {
'Content-Type': 'text/html;',
'charset': 'UTF-8',
'Location': '/cert_edit?calhead_id=' + _calprohd[0]
}
ENVIRO.update({'STATUS': '303'})
ENVIRO.update({'HEADERS': headers})
return True, '', ENVIRO, CLIENT_STATE, COOKIES, CSB
def new_cal(POST={},
GET={},
ENVIRO={},
CLIENT_STATE={},
COOKIES={},
CONTEXT={},
TEMPLATE='',
TEMPLATE_ENGINE=None,
CSB='',
TEMPLATE_STACK={}):
_calprohd = GET.get('calprohd_id', '')
if _calprohd == None:
_is_in_cache, TEMPLATE, _template_name = m.build_template(
'list_procedures',
get_template_stack('list_procedures'),
True,
p_template_extension='html',
ENVIRO=ENVIRO)
return list_procedures(POST, GET, ENVIRO, CLIENT_STATE, COOKIES,
CONTEXT, TEMPLATE, TEMPLATE_ENGINE, CSB,
TEMPLATE_STACK)
_sql = "select mcal.create_cert( %(calprohd)s) as id "
_r = m.run_sql_command(ENVIRO.get('CONN'), _sql,
{'calprohd': _calprohd[0]})
#GET.update({'calhead_id': [str(_r[0]['id'])]})
headers = {
'Content-Type': 'text/html;',
'charset': 'UTF-8',
'Location': '/cert_edit?calhead_id=' + str(_r[0]['id'])
}
ENVIRO.update({'STATUS': '303'})
ENVIRO.update({'HEADERS': headers})
return True, '', ENVIRO, CLIENT_STATE, COOKIES, CSB
def list_procedures(POST={},
GET={},
ENVIRO={},
CLIENT_STATE={},
COOKIES={},
CONTEXT={},
TEMPLATE='',
TEMPLATE_ENGINE=None,
CSB='',
TEMPLATE_STACK={}):
CONTEXT.update({
'PAGE_NAME':
"Pick Calibration Procedure to Create a New Certificate"
})
CONTEXT.update({'PAGE_DESCRIPTION': "List of Cert Procedures"})
_sql = """select *,
'<a href=new_cal?calprohd_id=' || calprohd_id::text || '> New Cal </a>'
as url_new
from mcal.calprohd
where calprohd_active
and calprohd_expire > now()::date
order by calprohd_descrip """
CONTEXT.update({'calprohd': m.run_sql_command(ENVIRO.get('CONN'), _sql)})
_ouput = TEMPLATE_ENGINE(pfile=TEMPLATE,
ptype='string',
pcontext=CONTEXT,
preturn_type='string',
pcache_path=ENVIRO.get(
'TEMPLATE_CACHE_PATH_PRE_RENDER', ''))
return True, _ouput, ENVIRO, CLIENT_STATE, COOKIES, CSB
def link_item_cert(POST={},
GET={},
ENVIRO={},
CLIENT_STATE={},
COOKIES={},
CONTEXT={},
TEMPLATE='',
TEMPLATE_ENGINE=None,
CSB='',
TEMPLATE_STACK={}):
_calhead = POST.get('calhead_id', [''])
_item_id = POST.get('item_id', [''])
if _calhead[0] > '' or _item_id[0] > '':
_sql = 'update mcal.calhead set calhead_coitem_id = %(item_id)s where calhead_id = %(_calhead)s'
_r = m.run_sql_command(ENVIRO.get('CONN'), _sql, {
'_calhead': _calhead[0],
'item_id': _item_id[0]
})
headers = {
'Content-Type': 'text/html;',
'charset': 'UTF-8',
'Location': '/cert_edit?calhead_id=' + _calhead[0]
}
ENVIRO.update({'STATUS': '303'})
ENVIRO.update({HEADERS: headers})
return True, '', ENVIRO, CLIENT_STATE, COOKIES, CSB
def get_blog_counts(p_id, CONTEXT={}, ENVIRO={}):
q_str = """ select bc_views
from blog_counter where bc_blog_id = %(id)s """
_rec = m.run_sql_command(ENVIRO.get('CONN'), q_str, {'id': p_id})
CONTEXT.update({'web_urls': m.build_url_links(_rec, ENVIRO=ENVIRO)})
return True, CONTEXT
def add_line_to_quote(POST={},
GET={},
ENVIRO={},
CLIENT_STATE={},
COOKIES={},
CONTEXT={},
TEMPLATE='',
TEMPLATE_ENGINE=None,
CSB='',
TEMPLATE_STACK={}):
_quhead = int(POST.get('quhead', ['-1'])[0])
_qunumber = int(POST.get('qunumber', ['-1'])[0])
_itemsite_id = int(POST.get('itemsite_id', ['-1'])[0])
_qty = int(POST.get('qty', ['0'])[0])
_warehouse = int(POST.get('warehouse', ['0'])[0])
_message = 'Failed to Add Item the Quote'
q_str = """select xmag.additem_to_quote(
%(quhead)s,
%(itemsite_id)s,
%(qty)s
) as idkey;
"""
_r = m.run_sql_command(ENVIRO.get('CONN'), q_str, {
'itemsite_id': _itemsite_id,
'quhead': _quhead,
'qty': _qty
})
if _r[0]['idkey'] > 0:
_message = 'Added the Item to the Quote'
_redirect = """ /list_van_inventory?message=%s;warehouse=%s;qunumber=%s;quhead=%s""" % (
_message, _warehouse, _qunumber, _quhead)
return m.client_redirect(ENVIRO, _redirect, '303', CLIENT_STATE, COOKIES,
CSB)
def save_session(CLIENT_STATE={},
POST={},
GET={},
ENVIRO={},
COOKIES={},
CONTEXT={},
TEMPLATE=''
):
_sec = ENVIRO.get('SEC')
if CLIENT_STATE.get('session_id', '') =='':
CLIENT_STATE.update({'session_id':str(m.get_db_next_id(ENVIRO.get('CONN'),'client_state_cs_id_seq'))})
##post and get should always hold the last set of commands.
# when it reloads it places the previous POST and GET's in prev_state
CLIENT_STATE.update({'TIMEOUT' : str(dt.utcnow() + td(seconds=_sec.get('USER_TIMER',6000)))})
CLIENT_STATE.update({'POST': POST})
CLIENT_STATE.update({'GET': GET})
CLIENT_STATE.update({'USER_ID':_sec.get('USER_ID',-1)})
q_sql = """ insert into client_state ( cs_id, cs_data, cs_ip ) values (
%(session_id)s, %(pdict)s, %(remote_ip)s )
on conflict (cs_id) do Update
set cs_data = %(pdict)s,
cs_ip = %(remote_ip)s,
cs_user_id = %(user_id)s
"""
_rec = m.run_sql_command(ENVIRO.get('CONN'), q_sql,
{'session_id':CLIENT_STATE.get('session_id'),
'pdict': json.dumps(CLIENT_STATE),
'remote_ip':ENVIRO.get('REMOTE_ADDR','0.0.0.0'),
'user_id': _sec.get('USER_ID',-1),
}
)
return _rec.get('state', False)
def show_access(POST={}, GET={}, ENVIRO={}, CLIENT_STATE={},
COOKIES={}, CONTEXT={}, TEMPLATE='',
TEMPLATE_ENGINE=None, CSB='', TEMPLATE_STACK={}):
if 'key_id' in GET :
_key = int(''.join(GET.get('blog_id')),'-1')
_search_tags = ''.join(GET.get('search_tags', '')).split(',')
_text = ''.join(GET.get('content', ''))
_title = ''.join(GET.get('title', ''))
elif 'key_type' in POST:
_key = int(''.join(POST.get('blog_id','-1')))
_search_tags = ''.join(POST.get('search_tags', '')).split(',')
_text = ''.join(POST.get('content', ''))
_title = ''.join(POST.get('title', ''))
else :
return False, '', ENVIRO, CLIENT_STATE, COOKIES, CSB
q_str = """select sa_id, sa_allowed sa_target_id, sa_app_name, sa_app_function
from sec_access order by"""
CONTEXT.update({'groups': m.run_sql_command(ENVIRO.get('CONN'), q_str)})
_ouput = TEMPLATE_ENGINE(pfile = TEMPLATE,
ptype = 'string',
pcontext = CONTEXT,
preturn_type ='string',
pcache_path = ENVIRO.get('TEMPLATE_CACHE_PATH_PRE_RENDER', ''))
return True, _ouput, ENVIRO, CLIENT_STATE, COOKIES, CSB
def create_quote(POST={},
GET={},
ENVIRO={},
CLIENT_STATE={},
COOKIES={},
CONTEXT={},
TEMPLATE='',
TEMPLATE_ENGINE=None,
CSB='',
TEMPLATE_STACK={}):
_key = int(GET.get('incdt_id', ['-1'])[0])
if _key < 0:
return m.client_redirect(ENVIRO, '/list_services', '303', CLIENT_STATE,
COOKIES, CSB)
q_str = """select xmag.create_quote_from_incident(%(key)s) as quhead_id """
_r = m.run_sql_command(ENVIRO.get('CONN'), q_str, {'key': _key})
if _r[0]['quhead_id'] > 1:
return m.client_redirect(ENVIRO,
'/edit_quote?quhead_id=' + _r[0]['quhead_id'],
'303', CLIENT_STATE, COOKIES, CSB)
return m.client_redirect(ENVIRO, '/list_services?message=',
+qp('Failed to Create Quote Template'), '303',
CLIENT_STATE, COOKIES, CSB)
def write_file_to_db(p_con=None,
p_image_id=-1,
p_user_id=-1,
p_fname='',
p_type='',
p_fstream=''):
if p_image_id == -1:
_holder = 'default'
else:
_holder = str(p_image_id)
_sql = "Insert into files values( " + _holder + ", %(p_fname)s, %(p_user_id)s, now(), %(p_type)s, %(p_fstream)s )"
_sql = _sql + """on conflict (file_id) do Update set
file_name = %(p_fname)s,
file_type = %(p_type)s ,
file_stream= %(p_fstream)s returning file_id"""
_r = m.run_sql_command(
p_con, _sql, {
'p_fname': p_fname,
'p_type': p_type,
'p_fstream': p_fstream,
'p_user_id': p_user_id,
})
return _r
def get_cats(POST,
GET,
ENVIRO,
COOKIES,
CLIENT_STATE,
CONTEXT,
TEMPLATE,
TEMPLATE_ENGINE,
CSB='',
TEMPLATE_STACK={}):
q_str = """ select '?cat_id=' || cat_id::text as url, cat_short as Name,
cat_long || ' ' || coalesce(bc_count, 0)::text as LinkText
from category
left join ( select count(*) as bc_count,
bc_cat_id from blog_cats
group by bc_cat_id ) bl
on bc_cat_id = cat_id
"""
_rec = m.run_sql_command(ENVIRO.get('CONN'), q_str)
_cat = m.build_url_links(_rec,
p_url_path='',
p_app_command='view_category',
ENVIRO=ENVIRO)
CONTEXT.update({'category': _cat})
return True, CONTEXT
def list_cal(POST={},
GET={},
ENVIRO={},
CLIENT_STATE={},
COOKIES={},
CONTEXT={},
TEMPLATE='',
TEMPLATE_ENGINE=None,
CSB='',
TEMPLATE_STACK={}):
q_str = """
select calhead_id as id, calhead_id, calhead_coitem_id, cust_name,
calprohd_descrip, calprohd_report_id,
calhead_caldate, calhead_expire, calhead_status,
(select caldetail_default_text
from mcal.caldetail
where caldetail_descrip_text_datacollect = 'Serial Number'
and caldetail_calhead_id = calhead_id
) as serial,
'<a href=cert_edit?calhead_id=' || calhead_id::text || '>Edit Cert</a>'as url_edit,
'<a href=cert_report?calhead_id=' || calhead_id::text || '>Report</a>'as url_report,
'<a href=cert_replace?calhead_id=' || calhead_id::text || '>Replace</a>' as url_replace,
'<a href=email_cert?calhead_id=' || calhead_id::text || '>Email</a>' as url_email
from mcal.calhead
left join mcal.calprohd on calhead_calprohd_id = calprohd_id
left join custinfo on calhead_cust_id = cust_id
order by calhead_caldate desc"""
message = GET.get('message', ['-1'])[0]
CONTEXT.update({'cal_head': m.run_sql_command(ENVIRO.get('CONN'), q_str)})
CONTEXT.update({'cert_types': get_cert_types(ENVIRO)})
CONTEXT.update({'PAGE_NAME': "List Certificates"})
CONTEXT.update({
'PAGE_DESCRIPTION':
"Page listing certificates to edit, print and replace"
})
CONTEXT.update({'app_messages': message})
_rec = m.run_sql_command(ENVIRO.get('CONN'), q_str)
_ouput = TEMPLATE_ENGINE(pfile=TEMPLATE,
ptype='string',
pcontext=CONTEXT,
preturn_type='string',
pcache_path=ENVIRO.get(
'TEMPLATE_CACHE_PATH_PRE_RENDER', ''))
return True, _ouput, ENVIRO, CLIENT_STATE, COOKIES, CSB
def dump_files_to_disk(p_con=None, p_path=''):
""" Dumps all files saved in the DB to p_path or current working directory"""
_sql = "select file_id ,file_name, file_type, file_stream from files "
_r = m.run_sql_command(p_con, _sql)
for _e in _r:
_w = m.run_sql_command(
p_con, "select file_stream from files where file_id= %(id)s ",
{'id': _r['file_id']})
_temp = open(p_path + str(_e['file_id']) + '_' + _e['file_name'], 'rb')
_temp.write(_w['file_stream'])
_temp.flush()
_temp.close
return True
def save_cert_header(POST={},
GET={},
ENVIRO={},
CLIENT_STATE={},
COOKIES={},
CONTEXT={},
TEMPLATE='',
TEMPLATE_ENGINE=None,
CSB='',
TEMPLATE_STACK={}):
_columns = [
'calhead_id', 'calhead_caldate', 'calhead_expire', 'calhead_temp',
'calhead_humdity', 'calhead_operator_notes', 'calhead_coitem_id',
'calhead_failcert', 'calhead_status', 'calhead_addr_line1',
'calhead_addr_line2', 'calhead_addr_line3', 'calhead_addr_city',
'calhead_addr_state', 'calhead_addr_postalcode',
'calhead_addr_country', 'calhead_addr_number', 'calhead_cntct_name ',
'calhead_cntct_phone', 'calhead_cntct_fax', 'calhead_cntct_email',
'calhead_cntct_title', 'calhead_notesprinted ', 'calhead_operator',
'calhead_supersedes', 'calhead_cust_po'
]
_sql = """begin;
Update mcal.calhead set
calhead_caldate = %(calhead_caldate)s,
calhead_expire = %(calhead_expire)s,
calhead_temp = %(calhead_temp)s,
calhead_humdity = %(calhead_humdity)s,
calhead_operator_notes = %(calhead_operator_notes)s,
calhead_coitem_id = %(calhead_coitem_id)s,
calhead_failcert = %(calhead_failcert)s,
calhead_status = %(calhead_status)s,
calhead_addr_line1 = %(calhead_addr_line1)s,
calhead_addr_line2 = %(calhead_addr_line2)s,
calhead_addr_line3 = %(calhead_addr_line3)s,
calhead_addr_city = %(calhead_addr_city)s,
calhead_addr_state = %(calhead_addr_state)s,
calhead_addr_postalcode = %(calhead_addr_postalcode)s,
calhead_addr_country = %(calhead_addr_country)s,
calhead_cntct_name = %(calhead_cntct_name)s,
calhead_cntct_phone = %(calhead_cntct_phone)s,
calhead_cntct_fax = %(calhead_cntct_fax)s,
calhead_cntct_email = %(calhead_cntct_email)s,
calhead_cntct_title = %(calhead_cntct_title)s,
calhead_notesprinted = %(calhead_notesprinted)s,
calhead_operator = %(calhead_operator)s ,
calhead_cust_po = %(calhead_cust_po)s
where calhead_id = %(calhead_id)s ;
rollback;
"""
_data = {}
for _k, _v in POST.items():
if _k in _columns:
_data.update({_key: _v[0]})
_r = m.run_sql_command(ENVIRO.get('CONN'), _sql, _data)
return get_cert_edit(POST, GET, ENVIRO, CLIENT_STATE, COOKIES, CONTEXT,
TEMPLATE, TEMPLATE_ENGINE, CSB, TEMPLATE_STACK)
def load_credentials_user_pwd(puser='', pwd='' , ENVIRO={}):
#client_state = ENVIRO.
q_str ="""select user_id, user_name, user_last , user_email ,
user_type, user_pwd, user_displayname from users
where crypt( %(pwd)s, user_pwd) = user_pwd
and (user_displayname = %(puser)s or user_email = %(puser)s)
"""
_rec = m.run_sql_command(ENVIRO.get('CONN'), q_str, {'pwd':pwd, 'puser':puser} )
return create_SEC(_rec, ENVIRO)
def check_CSB(pcsb, ENVIRO):
""" antime check_CSB called the database
copy is purged from database regardless
if it is valid or expired
"""
q_str =""" select true from csb
where csb_id = %(pcsb)s
and csb_expires > now()"""
_r = m.run_sql_command(ENVIRO.get('CONN'), q_str,{'pcsb':pcsb})
if len(_r) > 0:
_return = True
CSB_STATUS=True
else:
_return = False
CSB_STATUS=False
q_str = """ delete from csb where csb_id = %(pcsb)s """
m.run_sql_command(ENVIRO.get('CONN'), q_str,{'pcsb':pcsb})
return _return
def get_incdt_link_docs(ENVIRO={}, pkey=-1):
q_str = """select file_id, file_title,
'<a href=get_file_from_xdb?file_id=' || file_id::text || '>'|| file_title ||'</a>'as url_dowload_file
from file, docass
where docass_source_id = %(key)s
and docass_source_type = 'INCDT'
and docass_target_id = file_id
and docass_target_type = 'FILE'; """
return m.run_sql_command(ENVIRO.get('CONN'), q_str, {'key': pkey})
def list_todoitem(ENVIRO={}, p_key=-1):
q_str = """ Select todoitem_id,
todoitem_name, todoitem_description,
todoitem_status, todoitem_active,
'<a href=view_todo?todo_id=' || todoitem_id::text || '>View </a>'as url_view_todo,
'<a href=close_todo?todo_id=' || todoitem_id::text || '>Close </a>'as url_close_todo,
'<a href=del_todo?todo_id=' || todoitem_id::text || '>Delete</a>'as url_del_todo
from todoitem where todoitem_incdt_id = %(key)s """
return m.run_sql_command(ENVIRO.get('CONN'), q_str, {'key': p_key})
def add_comment(
p_id=-1,
p_user_id=-1,
p_text='',
p_bc_parent=None,
):
q_str = """ insert into blog_comments values ( default, %(blog_id),
%(bc_user_id)s, now(), %(bc_comment)s,
%(bc_comments)s::tsvector ) """
_topass = {'blog_id': p_id, 'bc_user_id': p_user_id, 'bc_comment': p_text}
return m.run_sql_command(ENVIRO.get('CONN'), q_str, _topass)
def search_blog(POST={},
GET={},
ENVIRO={},
CLIENT_STATE={},
COOKIES={},
CONTEXT={},
TEMPLATE='',
TEMPLATE_ENGINE=None,
CSB='',
TEMPLATE_STACK={}):
_text = ''
CONTEXT.update({'PAGE_NAME': "Searching Through the Blog"})
if 'search_value' in POST:
_text = POST['search_value']
elif 'search_value' in GET:
_text = GET['search_value']
else:
CONTEXT.update({'search_count': '0'})
return True
q_str = """select blog_id, blog_title, blog_date,
blog_htmltext,
ts_rank_cd(blog_tsv, query) AS rank
from blog, to_tsquery('english', %(search_value)s) query
where
blog_tsv @@ query
order by rank desc
"""
_rec = m.run_sql_command(ENVIRO.get('CONN'), q_str,
{'search_value': _text})
if len(_rec) > 0:
_cat = []
for _r in _rec:
_cat.append({
'blog_url':
build_get_url('view', {'blogkey': _r.get('blog_id')}),
'blog_title':
_r.get('blog_title', ''),
'blog_date':
_r.get('blog_date', ''),
'blog_text_250':
html2text(_r.get('blog_htmltext', '')[0:250]),
'rank':
_r.get('rank', '0')
})
CONTEXT.update({'search_results': _rec[0]})
_ouput = TEMPLATE_ENGINE(pfile=TEMPLATE,
ptype='string',
pcontext=CONTEXT,
preturn_type='string',
pcache_path=ENVIRO.get('TEMPLATE_TMP_PATH'))
return True, _ouput, ENVIRO, CLIENT_STATE, COOKIES, CSB
return False
def get_blog_view_counts(p_id, CONTEXT={}, ENVIRO={}):
q_str = """ select blog_title as Name,
blog_title || coalesce(bc_views, 0)::text as LinkText,
'?id='||blog_id::text as url
from blog
left join blog_counter on blog_id = bc_blog_id
order by blog_counter desc limit 5 """
_rec = m.run_sql_command(ENVIRO.get('CONN'), q_str)
CONTEXT.update({'web_urls': m.build_url_links(_rec, ENVIRO=ENVIRO)})
return True, CONTEXT
def show_users_groups(POST={}, GET={}, ENVIRO={}, CLIENT_STATE={},
COOKIES={}, CONTEXT={}, TEMPLATE='',
TEMPLATE_ENGINE=None, CSB='', TEMPLATE_STACK={}):
q_str = """Select user_id, user_name, user_last,
user_email, user_pwd, user_displayname
from users where user_type = 'user' """
CONTEXT.update({'users': m.run_sql_command(ENVIRO.get('CONN'), q_str)})
q_str = """Select user_id, user_name, user_last,
user_email, user_pwd, user_displayname
from users where user_type = 'groups' """
CONTEXT.update({'groups': m.run_sql_command(ENVIRO.get('CONN'), q_str)})
_ouput = TEMPLATE_ENGINE(pfile = TEMPLATE,
ptype = 'string',
pcontext = CONTEXT,
preturn_type ='string',
pcache_path = ENVIRO.get('TEMPLATE_CACHE_PATH_PRE_RENDER', ''))
return True, _ouput, ENVIRO, CLIENT_STATE, COOKIES, CSB
def load_session(p_session_id = None, APPSTACK={}, ENVIRO={}, CLIENT_STATE={}):
""" Returns true if stored session loads and retry command is set
else returns false load enviroment should finish as normal
"""
_sec = ENVIRO.get('SEC')
_session_cookie = {}
CLIENT_STATE.update({'APPSTACK':APPSTACK})
if p_session_id is None or not hasattr(p_session_id, 'value') :
_r, CLIENT_STATE, _session_cookie = create_session(CLIENT_STATE=CLIENT_STATE, ENVIRO=ENVIRO)
return False, CLIENT_STATE, _sec, _session_cookie
else:
if p_session_id.value.isdigit() == False:
return False, CLIENT_STATE, _sec, _session_cookie
q_str =""" select cs_data from client_state
where cs_id = %(session_id)s """
_r=m.run_sql_command(ENVIRO.get('CONN'),q_str,{'session_id':p_session_id.value})
if len(_r) ==0: ##the database does not have the session data create a new one
_r, CLIENT_STATE, _session_cookie = create_session(CLIENT_STATE=CLIENT_STATE, ENVIRO=ENVIRO)
return False, CLIENT_STATE, _sec, _session_cookie
CLIENT_STATE.update({'PREV_STATE':_r[0]['cs_data']})
##set the global client_state = to the one stored in the database
_timeout = dt.strptime(CLIENT_STATE.get('PREV_STATE',{}).get('TIMEOUT','' ), '%Y-%m-%d %H:%M:%S.%f')
if _timeout is None :
_timeout = dt.utcnow() - td(seconds=SEC['USER_TIMER'])
CLIENT_STATE.update({'TIMEOUT':_timeout})
_ctime = dt.utcnow()
if _timeout< _ctime \
and 'security' in APPSTACK \
and not _sec['USER_AUTOLOGIN']:
## the session has timeout and the app to run requiries security and
# the user auto login is turned off go to log in
m.error('Seesion Id %s timeout, redirect to login script ')
save_session(CLIENT_STATE, 'retry',)
return False, CLIENT_STATE, _session_cookie
elif CLIENT_STATE.get('TIMEOUT')< _ctime and _sec.get('USER_AUTOLOGIN',False):
## session has timeout but the autologin is turned on so log the user in and continue
_results, _sec = load_credentials(int(p_session_id.value), ENVIRO)
if _results:
if CLIENT_STATE['last_command'] == 'retry':
CLIENT_STATE.update({'last_command':'retry'})
CLIENT_STATE.update({'prev_state':{}})
#CLIENT_STATE.update({'session_id':int(_session_cookie.value)})
return True, CLIENT_STATE, _sec, _session_cookie
elif _timeout > _ctime:
_results, _sec = load_credentials(psession_id =int(p_session_id.value), ENVIRO=ENVIRO )
if len(CLIENT_STATE.get('last_command','')) > 0:
return True, CLIENT_STATE, _sec, _session_cookie
CLIENT_STATE.update({'session_id':int(p_session_id.value)})
return False, CLIENT_STATE, _sec, _session_cookie
def load_credentials(psession_id=-1, ENVIRO={}):
q_str ="""select user_id, user_name, user_last , user_email ,
user_type, user_pwd, user_displayname from users
where user_id in (select cs_user_id from client_state where
cs_id = %(psession_id)s and cs_ip = %(ip)s)
"""
_rec = m.run_sql_command(ENVIRO.get('CONN'), q_str,
{'psession_id':psession_id,
'ip':ENVIRO.get('REMOTE_ADDR', '0.0.0.0')
}
)
return create_SEC(_rec, ENVIRO)
