function get_blog_post($blog_post)
{
$get_blog_post_query = <<<SQL
select bp.blog_post,
bp.title,
bp.body,
to_char( bp.created, 'Day, Month DD, YYYY HH:MI:SS AM' ) as created,
m.first_name || ' ' || m.last_name as author,
r.name as role,
me.first_name || ' ' || me.last_name as editor,
to_char( bp.edited, 'Day, Month DD, YYYY HH:MI:SS AM' ) as edited,
re.name as editor_role
from tb_blog_post bp
join tb_member m
on bp.creator = m.member
join tb_member_role mr
on m.member = mr.member
join tb_role r
on mr.role = r.role
left join tb_member me
on bp.editor = me.member
left join tb_member_role mre
on me.member = mre.member
left join tb_role re
on mre.role = re.role
where bp.blog_post = ?blog_post?
SQL;
$params = ['blog_post' => $blog_post];
$result = query_execute($get_blog_post_query, $params);
return query_success($result) ? query_fetch_one($result) : false;
}
function get_webpage_access_allowed($page_name)
{
$session_member = SessionLib::get('user_member.member');
$params = ['page_name' => $page_name];
if ($session_member == -1) {
$get_access_query = <<<SQL
select access_allowed_by_default as access_allowed
from tb_webpage
where ?page_name? ilike base_uri_glob
SQL;
} else {
$get_access_query = <<<SQL
select tt.access_allowed
from tb_member_role rm,
fn_get_page_permissions_for_role( rm.role ) tt
join tb_webpage w
using ( webpage )
where rm.member = ?member?
and ?page_name? ilike w.base_uri_glob
SQL;
$params['member'] = $session_member;
}
$result = query_execute($get_access_query, $params);
if (query_success($result)) {
$row = query_fetch_one($result);
return $row['access_allowed'] == 't';
}
return false;
}
function get_max_and_min_blog_post()
{
$get_max_min_query = <<<SQL
select max( blog_post ) as max,
min( blog_post ) as min
from tb_blog_post
SQL;
$result = query_execute($get_max_min_query);
return query_success($result) ? query_fetch_one($result) : false;
}
function get_role_by_abbreviation($abbreviation)
{
$description_query = <<<SQL
select *
from tb_role
where abbreviation = ?abbreviation?
SQL;
$params = ['abbreviation' => $abbreviation];
$result = query_execute($description_query, $params);
return query_success($result) ? query_fetch_one($result) : false;
}
function get_member_session_by_key($key)
{
$get_session_query = <<<SQL
select member_session,
member,
accessed,
value,
extract( epoch from now() - accessed ) as age_seconds
from tb_member_session
where key = ?key?
SQL;
$params = ['key' => $key];
$result = query_execute($get_session_query, $params);
return query_success($result) ? query_fetch_one($result) : false;
}
function get_member_by_gatech_email($gatech_email)
{
$get_member_query = <<<SQL
select m.*,
r.is_admin
from tb_member m
join tb_member_role mr
on m.member = mr.member
join tb_role r
on mr.role = r.role
where m.gatech_email_address = ?gatech_email?
SQL;
$params = ['gatech_email' => $gatech_email];
$result = query_execute($get_member_query, $params);
return query_success($result) ? query_fetch_one($result) : false;
}
function create_member($gatech_email, $first_name, $last_name, $password)
{
$insert_member = <<<SQL
insert into tb_member
(
first_name,
last_name,
gatech_email_address,
display_email_address,
password_hash
)
values (
?first_name?,
?last_name?,
?gatech_email?,
?gatech_email?,
crypt( ?password?, gen_salt( 'bf' ) )
)
returning member
SQL;
$params = ['first_name' => $first_name, 'last_name' => $last_name, 'gatech_email' => $gatech_email, 'password' => $password];
begin_transaction();
$insert = query_execute($insert_member, $params);
if (query_success($insert)) {
$member_created = query_fetch_one($insert);
$member_pk = $member_created['member'];
$insert_role = <<<SQL
insert into tb_member_role
(
member,
role
)
values (
?member?,
?role?
)
SQL;
$params = ['member' => $member_pk, 'role' => ROLE_MEMBER];
$result = query_execute($insert_role, $params);
if (query_success($result)) {
commit_transaction();
return $member_pk;
}
}
rollback_transaction();
return false;
}
function get_member($member)
{
$get_member_query = <<<SQL
select m.*,
m.first_name || ' ' || m.last_name as name,
r.is_admin
from tb_member m
join tb_member_role mr
on m.member = mr.member
join tb_role r
on mr.role = r.role
where m.member = ?member?
SQL;
$params = ['member' => $member];
$result = query_execute($get_member_query, $params);
return query_success($result) ? query_fetch_one($result) : false;
}
function get_equipment_manager_email()
{
$email_query = <<<SQL
select m.display_email_address
from tb_member m
join tb_member_role mr
on m.member = mr.member
join tb_role r
on mr.role = r.role
where r.role = ?role?
SQL;
$params = ['role' => ROLE_EQUIPMENT_MANAGER];
$result = query_execute($email_query, $params);
if (query_success($result)) {
$row = query_fetch_one($result);
return $row['display_email_address'];
} else {
return false;
}
}
function create_or_update_member_session_by_key($key, $param_map)
{
$query = <<<SQL
select fn_insert_or_update_row
(
'tb_member_session',
?param_json?::json,
array[ 'key' ]
) as member_session
SQL;
$param_map['key'] = $key;
$param_json = json_encode($param_map);
$params = ['param_json' => $param_json];
$upsert = query_execute($query, $params);
if (query_success($upsert)) {
$retval = query_fetch_one($upsert);
return $retval['member_session'];
} else {
return false;
}
}