WCS Member

-- IBM documentation on WCS Member data model
-- http://pic.dhe.ibm.com/infocenter/wchelp/v7r0m0/topic/com.ibm.commerce.data.doc/refs/rdmmember.htm


-- Get total count of registered and Guest user count
select to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') as "Report as of",
(select count(*) from users where registertype='G') as "Guest User Count",
(select count(*) from users where registertype='R') as "Registered User Count"
from dual;

-- Order or cart count site wider based on user registration type
select u.registertype, o.status, count(*) as "Order/Cart Count"
from orders o, users u where o.member_id = u.users_id
group by u.registertype, o.status
order by registertype, status;

-- Get address details by login id
select * from ADDRESS
where member_id in (select users_id from userreg where logonid like '%<LOGIN_ID>%')
and status='P';

-- Copying password in case of a locked out account
-- This is a simple strategy to regenerate password from a working account
UPDATE userreg SET passwordexpired=0,status=1,passwordcreation=SYSDATE,
   logonpassword=(SELECT logonpassword FROM userreg WHERE logonid='<WORKING_ACCOUNT>'),
   salt=(SELECT salt FROM userreg WHERE logonid='<WORKING_ACCOUNT>')
WHERE logonid='<LOCKED_OUT_USERID>';