locals
  nID         : integer;
  sUserName   : string;
  cur : integer;
  bUseProjects: boolean;
  projid      : integer;
  nMaxProjId  : integer;
  title       : string; 
  nCount      : integer;
  nVaultId    : integer;
  sVaultName  : string;
  szStem      : string;
  szStartDate : string; 
  szEndDate   : string;
  szTmpTable  : string;
  ncur1       : integer;
  ncur2       : integer;
  ncur3       : integer;
  nuid        : integer

begin
--  SQL diagnostics on
  let szTmpTable = "USER_ACTIVITY_REPORT"
  let szStartDate = '2001-12-17'
  let szEndDate = '2009-12-17'
  let szStartDate = '31-JAN-01'
  let szEndDate = '2009-12-17'

  let bUseProjects = true

  -- Create the report table
  SQL diagnostics on
  SQL execute "DROP TABLE :szTmpTable:" EndSQL
  SQL execute "CREATE TABLE :szTmpTable:( ID INTEGER, TOTAL INTEGER, UNAME VARCHAR(255) )" EndSQL
--  SQL execute "INSERT INTO :szTmpTable:(ID, TOTAL) SELECT id, 0 FROM users WHERE status = 9001" EndSQL
  SQL diagnostics off
--  goto EXIT

  if bUseProjects then
    let projid = 762 -- should be 1
--    let projid = 1

    -- Get The Max Project ID
    SQL execute cur "SELECT MAX(ID) FROM EDMPROJECTS" EndSQL  
    SQL fetch cur nMaxProjId EndSQL
    SQL break cur

    let nMaxProjId = 765 -- *******************

    -- Loop Around All Projects
    while projid <= nMaxProjId loop
      SQL execute cur "SELECT PROJECT, PASSWD FROM EDMPROJECTS WHERE ID=:projid AND STATUS=1" EndSQL  
      SQL fetch cur PROJ_hidname PROJ_hidpass EndSQL
      SQL break cur

      if Qerrorno = 0 then
        let PROJ_login   = true    -- Log on to a project
        let PROJ_hidden  = true    -- Use PROJ_name rather than inquire from the user

        run SQLlogin.hcl@@EDMutils -- connect to DB
        if not Qdblogged then  -- logged on to RDB account
          lprint @(76187,PROJ_hidname)
          run SQLlogin.hcl@@EDMutils -- connect to DB
        else
          gosub UpdateTotals
        endif
      endif
      let projid = projid + 1
      if rem(projid ,50) = 0 then
         mprint "Processed(" projid ")" nl endprint
      endif
    endloop
  else
    gosub UpdateTotals
  endif

  gosub DisplayReport

  -- Remove Report Table
  SQL execute "DROP TABLE :szTmpTable:" EndSQL

  EDM commit
  goto EXIT

-------------------------------------------------------------------------------
label UpdateTotals

  lprint "Building Report..."

  -- Get all the vaults which have been modified after the start date
  SQL execute ncur1
    "SELECT va.id, va.stem, va.name FROM vaults va, vault_locs locs "
    "WHERE va.id = locs.vault AND locs.status = 1 AND locs.timelastmod > :szStartDate"
  EndSQL
  SQL fetch ncur1 nVaultId szStem sVaultName EndSQL
  
  while Qerrorno = 0 loop -- Loop Vaults
--    SQL execute ncur2
--      "select userref, count(*) from :szStem:_HS" 
--      " where done > :szStartDate" 
--      " AND KIND IN (101, 103, 108)"
--      " Group by userref order by userref"
--    EndSQL
--    SQL fetch ncur2 nuid nCount EndSQL


    SQL execute ncur2 
      "select u.uname, count(*) from :szStem:_HS h, users u"
      " where done > :szStartDate" 
      " AND KIND IN (101, 103, 108)"
      " AND h.userref = u.id"
      " Group by h.userref, u.uname order by h.userref"
    EndSQL
    SQL fetch ncur2 sUserName nCount EndSQL


    while Qerrorno = 0 loop -- Loop Users
--      mprint szStem " " sVaultName " " sUserName " " nCount nl endprint

      SQL execute ncur3 "select id from :szTmpTable: where uname = :sUserName" EndSQL
      SQL fetch ncur3 nID EndSQL

      if Qerrorno /= 0 then
        SQL execute ncur3 "select max(id) from :szTmpTable:" EndSQL
        SQL fetch ncur3 nID EndSQL
        let nID = nID + 1
        SQL execute "INSERT INTO :szTmpTable:(ID, TOTAL, UNAME) VALUES (:nID:, :nCount:, :sUserName)" EndSQL
      else      
        SQL execute "UPDATE USER_ACTIVITY_REPORT set total = total + :nCount: where id=:nID" EndSQL
      endif
      SQL break ncur3

      SQL fetch ncur2 sUserName nCount EndSQL
    endloop -- End Loop Users

    SQL break ncur2
    SQL fetch ncur1 nVaultId szStem sVaultName EndSQL
  endloop -- End Loop Vaults
  SQL break ncur1

  EDM commit
return

-------------------------------------------------------------------------------
label DisplayReport
  System delete QEDMreport EndSystem
  -- Write report header
  openout QEDMreport
  let title = "User Activity Report (" + szStartDate + " to " + szEndDate +")"
  runa header.rep@@EDMreports ( title )
  lprint " "
  lprint " "
  --                          1         2         3         4         5         6         7         8         9
  --                 123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
  lprint            "| User Name                         | Number Of Registrations     |"
  let QEDMrepform = "| ^S                               ^| ^S                         ^|" 
  lprint            "|-----------------------------------|-----------------------------|"
  closeout
  SQL execute ncur1
    "select uname, total"
    "from :szTmpTable:"
    " where total <> 0 order by total desc"
  EndSQL

  let QEDMreplayout = true
  EDM report ncur1
  openout QEDMreport
  lprint " " lprint " "
  closeout
  if QEDMrepcount > 0 then
    runa report.hcl@@GENutils ( QEDMreport "User Activity Report" false )
  else
    Alert  @(51277)    -- 'Nothing selected'
  endif
 
  return

-------------------------------------------------------------------------------
label EXIT
  lprint "Report Complete"
--  SQL diagnostics off
end