[bse.git] / site / data / db / sql_statements.data

--
# VERSION=1.006
name: bse_siteuserSeminarBookingsDetail
sql_statement: <<SQL
select ar.*, pr.*, se.*, ss.*, sb.*,
      lo.description as loc_description,
      lo.room as loc_room,
      lo.street1 as loc_street1,
      lo.street2 as loc_street2,
      lo.suburb as loc_suburb,
      lo.state as loc_state,
      lo.country as loc_country,
      lo.postcode as loc_postcode,
      lo.public_notes as loc_public_notes,
      lo.bookings_name as loc_bookings_name,
      lo.bookings_phone as loc_bookings_phone,
      lo.bookings_fax as loc_bookings_fax,
      lo.bookings_url as loc_bookings_url,
      sb.id as booking_id
  from article ar, product pr, bse_seminars se, bse_seminar_sessions ss,
       bse_locations lo, bse_seminar_bookings sb
  where sb.siteuser_id = ?
    and sb.session_id = ss.id
    and ss.seminar_id = ar.id
    and ss.seminar_id = pr.articleId
    and ss.seminar_id = se.seminar_id
    and ss.location_id = lo.id
SQL

name: bse_locationSessionDetail
sql_statement: <<SQL
select ar.*, pr.*, se.*, ss.*,
   ss.id as session_id
  from article ar, product pr, bse_seminars se, bse_seminar_sessions ss
  where ss.location_id = ?
    and ss.seminar_id = ar.id
    and ss.seminar_id = pr.articleId
    and ss.seminar_id = se.seminar_id
  order by ss.when_at desc
SQL

name: getSeminarBookingBySession_idAndSiteuser_id
sql_statement: <<SQL
select * from bse_seminar_bookings
  where session_id = ? and siteuser_id = ?
SQL

name: bse_cancelSessionBookingForUser
sql_statement: <<SQL
delete from bse_seminar_bookings
  where session_id = ? and siteuser_id = ?
SQL

name: bse_updateSessionBookingForUser
sql_statement: <<SQL
update bse_seminar_bookings
  set roll_present = ?, options = ?, customer_instructions = ?, 
    support_notes = ?
  where session_id = ? and siteuser_id = ?
SQL

name: getSeminarBookingByPkey
sql_statement: <<SQL
select * from bse_seminar_bookings where id = ?
SQL

name: replaceSeminarBooking
sql_statement: <<SQL
replace bse_seminar_bookings values(?,?,?,?,?,?,?)
SQL

name: SeminarSessions.sessionsUnbookedByUser
sql_statement: <<SQL
select ss.* from bse_seminar_sessions ss left join bse_seminar_bookings sb
  on ss.id = sb.session_id and sb.siteuser_id = ?
where ss.seminar_id = ? and sb.siteuser_id is null
SQL

name: seminarSessionInfoUnbooked
sql_statement: <<SQL
select ss.*, lo.*, ss.id as session_id 
  from bse_locations lo 
  right join bse_seminar_sessions ss on lo.id = ss.location_id
  left join bse_seminar_bookings sb
  on ss.id = sb.session_id and sb.siteuser_id = ?
where ss.seminar_id = ? and sb.siteuser_id is null and lo.id = ss.location_id
SQL

name: AdminGroups.forUser
sql_statement: <<SQL
select ab.*, ag.*
  from admin_base ab, admin_groups ag, admin_membership am
where am.user_id = ? and am.group_id = ab.id and ab.id = ag.base_id
SQL

name: siteuserAddFileCategory
sql_statement: <<SQL
insert into bse_file_subscriptions(siteuser_id, category)
 values(?, ?)
SQL

name: siteuserRemoveFileCategory
sql_statement: <<SQL
delete from bse_file_subscriptions where siteuser_id = ? and category = ?
SQL

name: siteuserSubscribedFileCategories
sql_statement: <<SQL
select category
from bse_file_subscriptions
where siteuser_id = ?
SQL

name: OwnedFiles.userVisibleGroupFiles
sql_statement: <<SQL
select of.*
from bse_owned_files of, bse_siteuser_membership sm
where sm.siteuser_id = ?
  and sm.group_id = of.owner_id
  and of.owner_type = 'G'
SQL

name: bseDownloadLogAge
sql_statement: <<SQL
delete from bse_file_access_log
 where when_at < date_sub(now(), interval ? day)
SQL

name: bseClearOldFileNotifications
sql_statement: <<SQL
delete from bse_file_notifies
  where when_at < date_sub(now(), interval ? day)
SQL

name: bseAddOwnedFileNotification
sql_statement: <<SQL
insert into bse_file_notifies(file_id, owner_type, owner_id, when_at)
  values(?, ?, ?, now())
SQL

name: bseAddOwnedFileNotificationTime
sql_statement: <<SQL
insert into bse_file_notifies(file_id, owner_type, owner_id, when_at)
  values(?, ?, ?, ?)
SQL

name: baseClearOldFileNotifications
sql_statement: <<SQL
delete from bse_file_notifies
 where when_at < date_sub(now(), interval ? day)
SQL

name: bseDeleteFileNotification
sql_statement: <<SQL
delete from bse_file_notifies where id = ?
SQL

name: bseExpandGroupFileNotification
sql_statement: <<SQL
insert into bse_file_notifies(file_id, owner_type, owner_id, when_at)
select fn.file_id, 'U', sm.siteuser_id, fn.when_at
from bse_file_notifies fn,
     bse_siteuser_membership sm,
     bse_owned_files fi,
     bse_file_subscriptions fs
where fn.id = ?
  and fi.id = fn.file_id
  and fn.owner_id = sm.group_id
  and fs.siteuser_id = fs.siteuser_id
  and fs.category = fi.category;
SQL

name: bseNotifyFileGroupEntries
sql_statement: <<SQL
select * from bse_file_notifies
where owner_type = 'G'
order by when_at
limit 100
SQL

name: bseFileNotifyUsers
sql_statement: <<SQL
select distinct owner_id as id
from bse_file_notifies
where owner_type = 'U'
SQL

name: bseFileNotifyUserEntries
sql_statement: <<SQL
select *
from bse_file_notifies
where owner_type = 'U'
  and owner_id = ?
SQL

name: BackgroundTasks
sql_statement: select * from bse_background_tasks

name: bseClearArticleFileMetadata
sql_statement: <<SQL
delete from bse_article_file_meta
where file_id = ?
SQL

name: bseClearArticleFileAppMetadata
sql_statement: <<SQL
delete from bse_article_file_meta
where file_id = ? and appdata <> 0
SQL

name: bseClearArticleFileSysMetadata
sql_statement: <<SQL
delete from bse_article_file_meta
where file_id = ? and appdata = 0
SQL

name: bseDeleteArticleFileMetaByName
sql_statement: <<SQL
delete from bse_article_file_meta
where file_id = ? and name = ?
SQL

name: bseArticleKidSummary
sql_statement: <<SQL
select id, title, level, listed, lastModified
from article where parentid = ?
order by displayOrder desc
SQL

name: bseChildOrder
sql_statement: <<SQL
select id, displayOrder
from article
where parentid = ?
SQL

name: bseStepchildOrder
sql_statement: <<SQL
select childId as id, parentDisplayOrder as displayOrder, id as step_id
from other_parents
where parentId = ?
SQL

name: bseSetStepOrder
sql_statement: <<SQL
update other_parents
set parentDisplayOrder = ?
where id = ?
SQL

name: bseSetArticleOrder
sql_statement: <<SQL
update article
set displayOrder = ?
where id = ?
SQL

name: bseMarkOwnedOrdersAnon
sql_statement: <<SQL
update orders
set siteuser_id = -1 where siteuser_id = ?
SQL

name: bseRemoveUserSubs
sql_statement: <<SQL
delete from subscribed_users
where userId = ?
SQL

name: bseRemoveUserWishlist
sql_statement: <<SQL
delete from bse_wishlist
where user_id = ?
SQL

name: bseRemoveUserMemberships
sql_statement: <<SQL
delete from bse_siteuser_membership
where siteuser_id = ?
SQL

name: bseRemoveUserFileSubs
sql_statement: <<SQL
delete from bse_file_subscriptions
where siteuser_id = ?
SQL

name: bseRemoveUserFileNotifies
sql_statement: <<SQL
delete from bse_file_notifies
where owner_id = ? and owner_type = 'U'
SQL

name: bseMarkUserFileAccessesAnon
sql_statement: <<SQL
update bse_file_access_log
set siteuser_id = -1 where siteuser_id = ?
SQL

name: bseRemoveUserProdSubs
sql_statement: <<SQL
delete from bse_user_subscribed
where siteuser_id = ?
SQL

name: bseRemoveUserBookings
sql_statement: <<SQL
delete from bse_seminar_bookings
where siteuser_id = ?
SQL

name: bseMarkProductOrderItemsAnon
sql_statement: <<SQL
update order_item
set productId = -1
where productId = ?
SQL

name: bseRemoveProductFromWishlists
sql_statement: <<SQL
delete from bse_wishlist
where product_id = ?
SQL

name: bseGetMsgManaged
sql_statement: <<SQL
select m.message, m.language_code, b.formatting, b.params
from bse_msg_managed m, bse_msg_base b
where m.id = ?
  and m.language_code = ?
  and m.id = b.id
SQL

name: bseGetMsgDefault
sql_statement: <<SQL
select d.message, d.language_code, b.formatting, b.params
from bse_msg_defaults d, bse_msg_base b
where d.id = ?
  and d.language_code = ?
  and d.id = b.id
order by d.priority desc
SQL

name: bseAllMsgs
sql_statement: <<SQL
select * from bse_msg_base order by id
SQL

name: bseMessageDetail
sql_statement: select * from bse_msg_base where id = ?

name: bseMessageDefaults
sql_statement: <<SQL
select * from bse_msg_defaults
where id = ?
SQL

name: bseMessageDefinitions
sql_statement: <<SQL
select * from bse_msg_managed
where id = ?
SQL

name: bseMessageFetch
sql_statement: <<SQL
select * from bse_msg_managed
where id = ? and language_code = ?
SQL

name: bseMessageCreate
sql_statement: <<SQL
insert into bse_msg_managed(id, language_code, message) values(?,?,?)
SQL

name: bseMessageUpdate
sql_statement: <<SQL
update bse_msg_managed
   set message = ?
 where id = ? and language_code = ?
SQL

name: bseMessageDelete
sql_statement: <<SQL
delete from bse_msg_managed where id = ? and language_code = ?
SQL

name: AdminUIStates.matchingState
sql_statement: <<SQL
select *
from bse_admin_ui_state
where user_id = ?
  and name like ?
SQL

name: bseProductHasSaleFiles
sql_statement: <<SQL
select exists(
  select id
  from article_files
  where articleId = ?
    and forSale <> 0
  ) as "have_sale_files"
SQL

name: bseAuditLogClean
sql_statement: <<SQL
delete from bse_audit_log
where when_at < date_sub(now(), interval ? day)
SQL

name: ArticleFiles.userPaidFor
sql_statement: <<SQL
select distinct af.*
from article_files af, orders od, order_item oi
where af.forSale <> 0
  and af.articleId = oi.productId
  and oi.orderId = od.id
  and od.paidFor <> 0
  and od.siteuser_id = ?
order by af.displayName
SQL

name: ArticleFiles.orderPaidFor
sql_statement: <<SQL
select distinct af.*
from article_files af, order_item oi
where af.forSale <> 0
  and af.articleId = oi.productId
  and oi.orderId = ?
order by af.displayName
SQL

name: Orders.fileOrdersByUser
sql_statement: <<SQL
select distinct od.*
from article_files af, order_item oi, orders od
where af.id = ?
  and af.articleId = oi.productId
  and oi.orderId = od.id
  and od.siteuser_id = ?
SQL

name: Files.selected_files
sql_statement: <<SQL
select bf.*
from bse_files bf, bse_selected_files sf
where sf.owner_type = ?
  and sf.owner_id = ?
  and sf.file_id = bf.id
SQL

name: SelectedFiles.remove_owner
sql_statement: <<SQL
delete from bse_selected_files
 where owner_type = ?
   and owner_id = ?
SQL

name: bseRemoveProductPrices
sql_statement: <<SQL
delete from bse_price_tier_prices
where product_id = ?
SQL

name: TagMembers.removeByTag
sql_statement: <<SQL
delete from bse_tag_members
where owner_type = ?
  and owner_id = ?
  and tag_id = ?
SQL

name: TagMembers.remove_owned_by
sql_statement: <<SQL
delete from bse_tag_members
where owner_type = ?
  and owner_id = ?
SQL

name: Tags.object_tags
sql_statement: <<SQL
select t.*
from bse_tags t, bse_tag_members tm
where t.id = tm.tag_id
  and tm.owner_type = ?
  and tm.owner_id = ?
order by t.cat, t.val
SQL

name: Tag_ids.by_owner
sql_statement: <<SQL
select tag_id as "id"
from bse_tag_members
where owner_type = ?
  and owner_id = ?
SQL

name: TagMembers.deleteTag
sql_statement: <<SQL
delete from bse_tag_members
where tag_id = ?
SQL

name: Articles.byTag
sql_statement: <<SQL
select a.*
from article a, bse_tag_members m
where a.id = m.owner_id
  and m.tag_id = ?
SQL

name: bseTagsCleanup
sql_statement: <<SQL
delete from bse_tags
where id not in (select tag_id from bse_tag_members);
SQL

name: TagMembers.allprods
sql_statement: <<SQL
select *
from bse_tag_members
where owner_type = 'BA'
  and owner_id in
  (select id
     from article
     where parentid = ? 
       and listed != 0
       and generator = 'BSE::Generate::Product'
  union
   select op.childId
     from other_parents op, article a
      where op.parentId = ?
        and op.childId = a.id
        and a.generator = 'BSE::Generate::Product'
   )
SQL

name: Tags.allprods
sql_statement: <<SQL
select distinct t.*
from bse_tags t, bse_tag_members m
where t.id = m.tag_id
 and m.owner_type = 'BA'
 and m.owner_id in
  (select id
     from article
     where parentid = ? 
       and listed <> 0
       and generator = 'BSE::Generate::Product'
  union
   select op.childId
     from other_parents op, article a
      where op.parentId = ?
        and op.childId = a.id
        and a.listed <> 0
        and a.generator = 'BSE::Generate::Product')
SQL

name: TagMembers.allkids
sql_statement: <<SQL
select *
from bse_tag_members
where owner_type = 'BA'
  and owner_id in
  (select id
     from article
     where parentid = ? 
       and listed <> 0
  union
   select op.childId
     from other_parents op, article a
      where op.parentId = ?
        and op.childId = a.id
        and a.listed <> 0
   )
SQL

name: Tags.allkids
sql_statement: <<SQL
select distinct t.*
from bse_tags t, bse_tag_members m
where t.id = m.tag_id
 and m.owner_type = 'BA'
 and m.owner_id in
  (select id
     from article
     where parentid = ? 
       and listed <> 0

  union
   select op.childId
     from other_parents op, article a
      where op.parentId = ?
        and op.childId = a.id
        and a.listed <> 0
  )
SQL

name: Tags.images
sql_statement: <<SQL
select distinct t.*
from bse_tags t, bse_tag_members m, image i
where t.id = m.tag_id
  and m.owner_type = 'BI'
  and m.owner_id = i.id
  and i.articleId = ?
SQL

name: TagMembers.images
sql_statement: <<SQL
select distinct m.*
from bse_tag_members m, image i
where m.owner_type = 'BI'
  and m.owner_id = i.id
  and i.articleId = ?
SQL

name: TagCategoryDeps.deleteCat
sql_statement: <<SQL
delete from bse_tag_category_deps
where cat_id = ?
SQL

name: TagOwners.allCats
sql_statement: <<SQL
select distinct concat(t.cat, ':') as cat
from bse_tags t
where owner_type = ?
  and t.cat <> ''
order by cat
SQL

name: bseRemoveArticleSiteUserGroups
sql_statement: <<SQL
delete from bse_article_groups
where article_id = ?
SQL

name: bseRemoveArticleAdminAccess
sql_statement: <<SQL
delete from admin_perms
where object_id = ?
SQL

name: AuditLog.logonRecords
sql_statement: <<SQL
select * from bse_audit_log
where object_id = ?
  and object_type = ?
  and facility = 'bse'
  and component = ?
  and module = ?
  and
  (
    `function` = 'success'
   or
    `function` = 'invalid'
   or
    `function` = 'unlock'
   or
    `function` = 'recover'
  )
  and when_at > date_sub(now(), interval ? minute)
order by when_at asc
SQL

name: AuditLog.ipLogonRecords
sql_statement: <<SQL
select * from bse_audit_log
where ip_address = ?
  and facility = 'bse'
  and component = ?
  and module = ?
  and
  (
    `function` = 'success'
   or
    `function` = 'invalid'
   or
    `function` = 'unlock'
  )
  and when_at > date_sub(now(), interval ? minute)
order by when_at asc
SQL

name: bse_lockout_ip
sql_statement: call bse_ip_lockout(?,?,?)

name: getAdminUserByPassword_type
sql_statement: <<SQL
select bs.*, us.* from admin_base bs, admin_users us
  where bs.id = us.base_id and us.password_type = ?
SQL

name: PriceTiers.forCoupon
sql_statement: <<SQL
select t.*
from bse_price_tiers t, bse_coupon_tiers c
where c.coupon_id = ?
  and c.tier_id = t.id
SQL