def get_future(dbo, user = ""): """ Gets a list of future diary notes for the user supplied (or all users if no user passed) LINKID, LINKTYPE, DIARYDATETIME, DIARYFORNAME, SUBJECT, NOTE, LINKINFO """ return db.query(dbo, "SELECT *, cast(DiaryDateTime AS time) AS DiaryTime " \ "FROM diary WHERE %s " \ "AND DiaryDateTime > %s" \ "ORDER BY DiaryDateTime" % (user_role_where_clause(dbo, user), db.ddt(i18n.now(dbo.timezone))))
def get_future(dbo, user=""): """ Gets a list of future diary notes for the user supplied (or all users if no user passed) LINKID, LINKTYPE, DIARYDATETIME, DIARYFORNAME, SUBJECT, NOTE, LINKINFO """ return db.query(dbo, "SELECT *, cast(DiaryDateTime AS time) AS DiaryTime " \ "FROM diary WHERE %s " \ "AND DiaryDateTime > %s" \ "ORDER BY DiaryDateTime" % (user_role_where_clause(dbo, user), db.ddt(i18n.now(dbo.timezone))))
def insert_onlineformincoming_from_form(dbo, post, remoteip): """ Create onlineformincoming records from posted data. We create a row for every key/value pair in the posted data with a unique collation ID. """ IGNORE_FIELDS = [ "formname", "flags", "redirect", "account", "filechooser", "method" ] collationid = db.query_int(dbo, "SELECT MAX(CollationID) FROM onlineformincoming") + 1 formname = post["formname"] posteddate = i18n.now(dbo.timezone) flags = post["flags"] for k, v in post.data.iteritems(): if k not in IGNORE_FIELDS: label = "" displayindex = 0 fieldname = k # Form fields should have a _ONLINEFORMFIELD.ID suffix we can use to get the # original label and display position if k.find("_") != -1: fid = utils.cint(k[k.rfind("_")+1:]) fieldname = k[0:k.rfind("_")] if fid != 0: fld = db.query(dbo, "SELECT Label, DisplayIndex FROM onlineformfield WHERE ID = %d" % fid) if len(fld) > 0: label = fld[0]["LABEL"] displayindex = fld[0]["DISPLAYINDEX"] sql = db.make_insert_sql("onlineformincoming", ( ( "CollationID", db.di(collationid)), ( "FormName", db.ds(formname)), ( "PostedDate", db.ddt(posteddate)), ( "Flags", db.ds(flags)), ( "FieldName", db.ds(fieldname)), ( "Label", db.ds(label)), ( "DisplayIndex", db.di(displayindex)), ( "Host", db.ds(remoteip)), ( "Value", post.db_string(k)) )) db.execute(dbo, sql) # Sort out the preview of the first few fields fieldssofar = 0 preview = [] for fld in get_onlineformincoming_detail(dbo, collationid): if fieldssofar < 3: fieldssofar += 1 preview.append( fld["LABEL"] + ": " + fld["VALUE"] ) db.execute(dbo, "UPDATE onlineformincoming SET Preview = %s WHERE CollationID = %s" % ( db.ds(", ".join(preview)), db.di(collationid) )) # Did the original form specify some email addresses to send # incoming submissions to? email = db.query_string(dbo, "SELECT o.EmailAddress FROM onlineform o " \ "INNER JOIN onlineformincoming oi ON oi.FormName = o.Name " \ "WHERE oi.CollationID = %d" % int(collationid)) if email is not None and email.strip() != "": utils.send_email(dbo, configuration.email(dbo), email, "", "%s - %s" % (formname, ", ".join(preview)), get_onlineformincoming_plain(dbo, collationid)) return collationid
def get_completed_upto_today(dbo, user = ""): """ Gets a list of completed diary notes upto and including today for the user supplied (or all users if no user passed) LINKID, LINKTYPE, DIARYDATETIME, DIARYFORNAME, SUBJECT, NOTE, LINKINFO """ sixmonths = i18n.subtract_days(i18n.now(dbo.timezone), 182) return db.query(dbo, "SELECT *, cast(DiaryDateTime AS time) AS DiaryTime " \ "FROM diary WHERE %s " \ "AND DateCompleted Is Not Null AND DiaryDateTime <= %s AND DiaryDateTime >= %s" \ "ORDER BY DiaryDateTime DESC" % (user_role_where_clause(dbo, user), db.ddt(i18n.now(dbo.timezone)), db.ddt(sixmonths)))
def get_all_upto_today(dbo, user=""): """f Gets a list of all diary notes upto and including today for the user supplied (or all users if no user passed) LINKID, LINKTYPE, DIARYDATETIME, DIARYFORNAME, SUBJECT, NOTE, LINKINFO """ sixmonths = i18n.subtract_days(i18n.now(dbo.timezone), 182) return db.query(dbo, "SELECT *, cast(DiaryDateTime AS time) AS DiaryTime " \ "FROM diary WHERE %s " \ "AND DiaryDateTime <= %s AND DiaryDateTime >= %s" \ "ORDER BY DiaryDateTime DESC" % (user_role_where_clause(dbo, user), db.ddt(i18n.now(dbo.timezone)), db.ddt(sixmonths)))
def insert_onlineformincoming_from_form(dbo, data, remoteip): """ Create onlineformincoming records from posted data. We create a row for every key/value pair in the posted data with a unique collation ID. """ IGNORE_FIELDS = [ "formname", "flags", "redirect", "account", "filechooser", "method" ] collationid = db.query_int( dbo, "SELECT MAX(CollationID) FROM onlineformincoming") + 1 formname = utils.df_ks(data, "formname") posteddate = i18n.now(dbo.timezone) flags = utils.df_ks(data, "flags") for k, v in data.iteritems(): if k not in IGNORE_FIELDS: label = "" displayindex = 0 fieldname = k # Form fields should have a _ONLINEFORMFIELD.ID suffix we can use to get the # original label and display position if k.find("_") != -1: fid = utils.cint(k[k.rfind("_") + 1:]) fieldname = k[0:k.rfind("_")] if fid != 0: fld = db.query( dbo, "SELECT Label, DisplayIndex FROM onlineformfield WHERE ID = %d" % fid) if len(fld) > 0: label = fld[0]["LABEL"] displayindex = fld[0]["DISPLAYINDEX"] sql = db.make_insert_sql( "onlineformincoming", (("CollationID", db.di(collationid)), ("FormName", db.ds(formname)), ("PostedDate", db.ddt(posteddate)), ("Flags", db.ds(flags)), ("FieldName", db.ds(fieldname)), ("Label", db.ds(label)), ("DisplayIndex", db.di(displayindex)), ("Host", db.ds(remoteip)), ("Value", db.ds(v)))) db.execute(dbo, sql) # Sort out the preview of the first few fields fieldssofar = 0 preview = [] for fld in get_onlineformincoming_detail(dbo, collationid): if fieldssofar < 3: fieldssofar += 1 preview.append(fld["LABEL"] + ": " + fld["VALUE"]) db.execute( dbo, "UPDATE onlineformincoming SET Preview = %s WHERE CollationID = %s" % (db.ds(", ".join(preview)), db.di(collationid))) return collationid
def login(dbo, username): """ Marks the given user as logged in """ logout(dbo, username) db.execute(dbo, "DELETE FROM activeuser WHERE UPPER(UserName) LIKE '%s'" % str(username.upper())) db.execute(dbo, db.make_insert_sql("activeuser", ( ( "UserName", db.ds(username)), ( "Since", db.ddt(i18n.now())), ( "Messages", db.ds("asm3")) ))) al.info("%s logged in" % username, "users.login", dbo)
def login(dbo, username): """ Marks the given user as logged in """ logout(dbo, username) db.execute( dbo, "DELETE FROM activeuser WHERE UPPER(UserName) LIKE '%s'" % str(username.upper())) db.execute( dbo, db.make_insert_sql("activeuser", (("UserName", db.ds(username)), ("Since", db.ddt(i18n.now())), ("Messages", db.ds("asm3"))))) al.info("%s logged in" % username, "users.login", dbo)
def get_uncompleted_upto_today(dbo, user=""): """ Gets a list of uncompleted diary notes upto and including today for the user supplied (or all users if no user passed) LINKID, LINKTYPE, DIARYDATETIME, DIARYFORNAME, SUBJECT, NOTE, LINKINFO """ sixmonths = i18n.subtract_days(i18n.now(dbo.timezone), 182) current = i18n.now(dbo.timezone) alltoday = datetime.datetime(current.year, current.month, current.day, 23, 59, 59) return db.query(dbo, "SELECT *, cast(DiaryDateTime AS time) AS DiaryTime " \ "FROM diary WHERE %s " \ "AND DateCompleted Is Null AND DiaryDateTime <= %s AND DiaryDateTime >= %s" \ "ORDER BY DiaryDateTime DESC" % (user_role_where_clause(dbo, user), db.ddt(alltoday), db.ddt(sixmonths)))
def insert_onlineformincoming_from_form(dbo, data, remoteip): """ Create onlineformincoming records from posted data. We create a row for every key/value pair in the posted data with a unique collation ID. """ IGNORE_FIELDS = [ "formname", "flags", "redirect", "account", "filechooser", "method" ] collationid = db.query_int(dbo, "SELECT MAX(CollationID) FROM onlineformincoming") + 1 formname = utils.df_ks(data, "formname") posteddate = i18n.now(dbo.timezone) flags = utils.df_ks(data, "flags") for k, v in data.iteritems(): if k not in IGNORE_FIELDS: label = "" displayindex = 0 fieldname = k # Form fields should have a _ONLINEFORMFIELD.ID suffix we can use to get the # original label and display position if k.find("_") != -1: fid = utils.cint(k[k.rfind("_")+1:]) fieldname = k[0:k.rfind("_")] if fid != 0: fld = db.query(dbo, "SELECT Label, DisplayIndex FROM onlineformfield WHERE ID = %d" % fid) if len(fld) > 0: label = fld[0]["LABEL"] displayindex = fld[0]["DISPLAYINDEX"] sql = db.make_insert_sql("onlineformincoming", ( ( "CollationID", db.di(collationid)), ( "FormName", db.ds(formname)), ( "PostedDate", db.ddt(posteddate)), ( "Flags", db.ds(flags)), ( "FieldName", db.ds(fieldname)), ( "Label", db.ds(label)), ( "DisplayIndex", db.di(displayindex)), ( "Host", db.ds(remoteip)), ( "Value", db.ds(v)) )) db.execute(dbo, sql) # Sort out the preview of the first few fields fieldssofar = 0 preview = [] for fld in get_onlineformincoming_detail(dbo, collationid): if fieldssofar < 3: fieldssofar += 1 preview.append( fld["LABEL"] + ": " + fld["VALUE"] ) db.execute(dbo, "UPDATE onlineformincoming SET Preview = %s WHERE CollationID = %s" % ( db.ds(", ".join(preview)), db.di(collationid) )) return collationid
def action(dbo, action, username, tablename, description): """ Adds an audit record """ # Truncate description field to 16k if it's very long if len(description) > 16384: description = description[0:16384] sql = db.make_insert_sql("audittrail", ( ( "Action", db.ds(action) ), ( "AuditDate", db.ddt(i18n.now(dbo.timezone)) ), ( "UserName", db.ds(username) ), ( "TableName", db.ds(tablename) ), ( "Description", db.ds(description) ) )) db.execute(dbo, sql)
def df_dt(data, datefield, timefield, l): """ Returns a datetime field for the database """ if data.has_key(datefield): d = display2python(l, data[datefield]) if data.has_key(timefield): tbits = data[timefield].split(":") hour = 0 minute = 0 second = 0 if len(tbits) > 0: hour = cint(tbits[0]) if len(tbits) > 1: minute = cint(tbits[1]) if len(tbits) > 2: second = cint(tbits[2]) t = datetime.time(hour, minute, second) d = d.combine(d, t) return db.ddt(d) else: return "Null"
def update_animalcontrol_dispatchnow(dbo, acid, username): """ Updates an animal control incident record, marking it dispatched now with the current user as ACO. """ db.execute(dbo, "UPDATE animalcontrol SET DispatchedACO=%s, DispatchDateTime=%s WHERE ID=%d" % (db.ds(username), db.ddt(now(dbo.timezone)), acid)) audit.edit(dbo, username, "animalcontrol", acid, "aco=%s, dispatch=%s" % (username, now(dbo.timezone)))
def get_uncompleted_upto_today(dbo, user = "", includecreatedby = True): """ Gets a list of uncompleted diary notes upto and including today for the user supplied (or all users if no user passed) LINKID, LINKTYPE, DIARYDATETIME, DIARYFORNAME, SUBJECT, NOTE, LINKINFO """ sixmonths = i18n.subtract_days(i18n.now(dbo.timezone), 182) current = i18n.now(dbo.timezone) alltoday = datetime.datetime(current.year, current.month, current.day, 23, 59, 59) return db.query(dbo, "SELECT *, cast(DiaryDateTime AS time) AS DiaryTime " \ "FROM diary WHERE %s " \ "AND DateCompleted Is Null AND DiaryDateTime <= %s AND DiaryDateTime >= %s" \ "ORDER BY DiaryDateTime DESC" % (user_role_where_clause(dbo, user, includecreatedby), db.ddt(alltoday), db.ddt(sixmonths)))
def df_dt(data, datefield, timefield, l): """ Returns a datetime field for the database """ return db.ddt(df_kdt(data, datefield, timefield, l))
def update_animalcontrol_respondnow(dbo, acid, username): """ Updates an animal control incident record, marking it responded to now """ db.execute(dbo, "UPDATE animalcontrol SET RespondedDateTime=%s WHERE ID=%d" % (db.ddt(now(dbo.timezone)), acid)) audit.edit(dbo, username, "animalcontrol", acid, "responded=%s" % now(dbo.timezone))