Home

USER MANUAL Data Management

1. ES lt lt lt lt 8 lt lt b 2 kz S SEC02 04 Security gt Access Control gt User Group gt Access Right Maintain Function Access Rights Group ID DM_USER Group Description Data Management User Chinese Group Description HHA Module Function Data Management 2 ss 24 Execute Eee E A K Execution A S SEC02 04 Security gt Access Control gt User Group gt Access Right Maintain Function Access Rights Group ID TIMETABLING_ADMIN Group Description Timetabling Administrator Chinese Group Description WESS Module Function Data Management oo Ig Extract for TESS Extract for TESS Error Log Vv Reset Save Close Alternatively user defined groups can also be created with access rights of Data Management module Copyright 2007 Education Bureau The Government of the HKSAR Page 8 All Rights Reserved WebSAMS User Manual Version 1 3 2 Operation Procedures 2 1 Query Maintenance Create query by wizard LL Function Description Users can create queries by wizard and execute them by this function LL Pre requisites Users have to possess Query Maintenance Function access right 8 User Procedures Click Data Management Query Maintenance on the left menu Click the Add Wizard button Enter Query Name and Query Description i
2. 28 pedet ele tees eost om e eat ee ese leet a dens 28 Modify AMICI ge 30 Delete Task nt eR e b Re a C ePi Codes 31 2 5 MAINTAIN TABLE ACCESS eL ke Haee HE eet 33 DO EXECUTE OVERY TASK pn dett RM Me GEN ds 35 2 6 1 EXC CULE QUCTY PT EHE 35 2 6 2 Execute TASK sexes tacos ioe 36 1 IBXPORTOUERY 0 a dre e ee ten tence 41 2 7 1 Export QOUeEy uii il a A oe ot 41 2 7 2 EXPO T SK to Rete alee eet e RU E dace Rees 43 248 IMPORT a neenon E IH ERR o UR dee E 44 2 6 1 Import Task and Import 44 29 REPORT ees petet te eng tete de HERE ced dedo 46 2 9 1 Entity Relationship Diagram 46 2 9 2 LADO te RAE entrer ERR 47 2 9 3 Data Manual REESE de RE RS re Ee EN 46 2 9 4 Query Lit dai t HIR 49 QUERY ultio testes ORE ia 49 En 5 sop 50 2 10 EXTRACT FOR LESS eiie sie e TR a e E a tee d ete 51 Iq cum 51 Error Log ehe E et hate Ae Ge t Ur Ee edt ee 52 2 11 EXTRACTFOR SOP niaro e ert eed ere ERE HER
3. Select Table Field and Expression Click Add to left expression to add the selected Field as the left part of the expression Click the Add to right expression to add the selected Field as the right part of the expression If the Value radio box is selected as the right expression users can enter some specific values in the right expression If the Prompt radio box is selected as the right expression a will be added to the right expression automatically Users will have to enter the value when the query is executed Click the Add button to add the Having statement s Users can click the AND OR or button to add the operator in the statement Users can click the Remove button to remove the Having statement s Copyright 2007 Education Bureau The Government of the HKSAR Page 13 All Rights Reserved WebSAMS User Manual Version 1 3 10 Click the View tab Student S DAT02 08 Data Management gt Query Maintenance gt View Attendance Student Activities Award amp Punishme Query Name Query 1 m Query Description Query 1 Description SELECT SCH ANNOUNCE ANNOUNCEID FROM TB 5 ANNOUNCE 5 ASSESSTERM WHERE TB_SCH_ANNOUNCE ANNOUNCED Assessment Special Assessme Staff Deployment CDS HKAT HKEAA SPA Report Managemen Data Management Mainte Query Sharing Task Maintenar Table Access
4. gt SPA DD MM Y Y Y Y DD MM Y Y Y Y Report Managemen Task Task Name Data Management Task Descriptio Query Maintena Query Sharing Task Maintenai Table Access C Export 3 Click the Search button to search for queries Copyright 2007 Education Bureau The Government of the HKSAR Page 35 All Rights Reserved WebSAMS User Manual Version 1 3 n sUnUUI ical coc cu may cuu 11 c0 n ds si Table in Query Owner 1 gt Studeni Last Modified m Last Modified Eg Attendance Date After E Date Before gt Student Activities DD MM Y Y Y Y DDMMA YYY C Task Task Name Task Description Staff Deployment CDS Bottom Ton Last gt Query Description odifie a SS IESE STQ0010 Staff Duty Profile Class Teacher r sTo Duty Profile Subject Teacher Report Managemen sTQ0012 Staff Duty Profile Subject Group Teacher for Subject Group Subject 760015 Staff Duty Profile Committee Duty Assignment Query Sharing 5700019 Staff Duty Profile Post Duty Assignment Task Maintenar STQOO0IB Staff Duty Profile Staff Personal Details ees 57000 0 Staff Conespondence List eee TEACHSTF Teaching Staff Eeport TEACHSTFD Dummy Teaching Staff Import Report Go To Execute 5 Top Check the boxes besi
5. Asset Quantity on Hand Nationality Stream Teaching Staff Dummy Teaching Staff 3 Users can check the box beside the Query Name and click the Delete button to delete queries Only the owner of the query can delete the query Post effects N A Notes Built i n standard queries cannot be deleted The checkboxes of the standard queries are disabled Copyright 2007 Education Bureau The Government of the HKSAR All Rights Reserved Page 23 WebSAMS User Manual Version 1 3 Modify Query LL Function Description Users can modify the query information LL Pre requisites Users have to possess Query Maintenance Function access right 8 User Procedures 1 Click Data Management Query Maintenance on the left menu 2 Fillin the search criteria Click the Search button to search for queries D a Student S DAT03 05 Data Management gt Query Maintenance Attendance Student Activities Query Name te 1 Query Description 00000 Table in Query D y Owner D 3 Assessment Last Modified Date Last Modified Date After Before gt Special Assessmel Staff Deployment gt CDS DD MMV Y Y Y Add Advance Add wizard search 0 DD MM Y Y Y Y
6. Home S DAT01 02 Data Management Task Maintenance Student Info Enq School Managemen Add Task Student ask Name Attendance Task Description Student Activities Award amp Punishme Assessment 3 Enter Task and Task Description 4 Click the Save button S DAT01 04 Data Management Task Maintenance gt Attach Detach Query Task info OSGeo Detach Query Task Name aaa Task Description aaa n Query Description Owner Last Modified No record Student Info Eng School Managemen Student Activities Award amp Punishme Assessment Special Assessment Staff Deployment 5 Click the Add button 6 A new window will pop up Fill in the search criteria Click the Search button to search for queries Copyright 2007 Education Bureau The Government of the HKSAR Page 28 All Rights Reserved WebSAMS User Manual Version 1 3 http ncs prtsvr106 Maintain Task Windows Internet Explorer S DAT01 06 Data Management gt Task Maintenance gt Attach Detach Query Query Name Query Description Table in Query Owner helpdesk Last Modified Date After Last Modified Date g OD MMIYYYY Before DD MM YYYY Query Description Last Modified helpdesk 03 2004 40 18 helpdesk 10 2004 43 58 helpdesk 09 2004 40 53 helpdesk 12 10 2004 14 25 11 helpdesk 14 09 2004 16
7. Returns a number of characters from the beginning of a string Returns the number of characters in the specified string Returns the lesser of two parameter values Returns the position of one string within another Returns the natural logarithm of a number Returns the base 10 logarithm of a number Converts all characters in a string to lower case Copyright 2007 Education Bureau The Government of the HKSAR Page 19 All Rights Reserved WebSAMS User Manual Version 1 3 LTRIM string expression MAX expression MIN expression MINUTE datetime expression MOD dividend divisor MONTH date expression MONTHNAME date expression NOW NULLIF expression 1 expression 2 NUMBER PI POWER numeric expression 1 numeric expression 2 QUARTER date expression RADIANS numeric expression RAND integer expression REPEAT string expression integer expression REPLACE original string search string replace string RIGHT string expression integer expression ROUND numeric expression integer expression RTRIM string expression SECOND datetime expression SIGN numeric expression SIN numeric expression SOUNDEX string expression SPACE integer expression Trims leading blanks from a string The maximum value of the expression over the returned rows The minimum value of the expression over the returned rows Returns a number from 0
8. a The query statement will be displayed Click the Save button to save the query b After saving the query successfully users can click the Go To Execute button to execute the query A new window will pop up Select the Output Format d Click the View Result button to view the execution result in a new window e Click the Save Result button to save the execution result Z Execute Query and Task Microsoft Internet Explorer xl E S DAT04 03 Data Management gt Executing o Query Name Query 1 Query Description Query 1 Description SQL Statement SELECT TB_SCH_ANNOUNCE ANNOUNCEID TB_SCH_ANNOUNCE SUID FROM TB_SCH_ANNOUNCE TB_SCH_ASSESSTERM WHERE TB_SCH_ANNOUNCE ENDDATE gt TB_LSCH_ANNOUNCE STARTDATE ORDER BY TB_SCH_ANNOUNCE STARTDATE ASC Output Format HTML Text File C Excel C Word C csv Delimiter Post effects N A Copyright 2007 Education Bureau The Government of the HKSAR Page 14 All Rights Reserved WebSAMS User Manual Version 1 3 Notes Only the owner of the query can delete the query 2 Only users of the SYSTEM ADMIN and SCHOOL HEAD user groups and the owner of the query can modify the query 3 If aquery is modified the owner of the query will be changed to the user who makes the modification 4 The name of the user created queries cannot start with since the character designates built in standard
9. Bottom quen FETTE i23 sysadmin 15 04 2003 124046 copy gt Report Managemei sysadmin 2005 2003 121944 Data Management abode bode sysadmin 05 2003 12 19 24 sysadmin 1903 2003 15 45 57 copy m ERSCHSCHCLASS RETRIEVE DATA FROM TABLE TB_SCH_SCHCLASS 11042008 1734 57 BB EC eua fTest 1A sysadmin 0904 2003 11 11 00 copy Table Access E EAREACD Code E B Execution EEACMPRSLT Curent Public Examnination Results for Subject Components Eco ek LEASBIBSLT Current Public Examination Results for HKEA Subjects IB mph Asset Details Ew Extract for TES Quantity on Hand copy Timetabling gNATON Nationality B coy Code Management al Steam gt Security m ETEACHSIE Teaching Staff gt E Mail m Dummy Teaching Bco Customization 3 Click the Query Name from the search results to view the query 4 Users can modify the query in the same way that they create a query by wizard or through advanced query and click the Save to save the query 5 Users can click the Go To Execute button to execute the query a Anew window will pop up Select the Output Format b Click the View Result button to view the execution res
10. Capital Asset Register Staff Cost Calan and Cael gt Timetabling gt Code Management Security E Mail Customization m ROCLAN 4 Click on the Task Name link to view the SQL statements of all the queries attached to the selected task Post effects N A Notes N A Copyright 2007 Education Bureau The Government of the HKSAR Page 50 All Rights Reserved WebSAMS User Manual Version 1 3 2 10 Extract for TESS Extract LL Description This function allows users to extract timetabling information for the existing timetabling system TESS LL Pre requisites 1 The system has completed Plan New School Year as described in Inter Year Processing 2 Users have to possess Extract for TESS Function access right User Procedures 1 Click Data Management Extract for TESS on the left menu 2 Select the School Level School Session and Extract Parameter 3 Click on the Extract button to extract the timetabling information Award amp Punishme S DAT09 01 Data Management gt Extract for TESS gt Extract Extract Extract Data for TESS 2 2003 Assessment Special Assessme Staff Deployment CDS Last Extracted Date 2 gt HKAT Last Extracted File PAMoode zip Extraction gt HKEAA School Level Primary gt SPA School Session AM 7 Report Manageme Extract Parameter
11. FMP ACCT CLERK FMP PETTYCASH CLERK FMP ADMIN FMP USER1 FMP USER2 and FMP USERS can execute query with the FMP module tables DATO09 Extract Timetabling e School SCH Retrieve the Current School Year Retrieve School Basic Information Week Cycle Based Session Level Number of School Day Retrieve School Facility with Capacity gt 0 Room Retrieve Class Basic Information Class Teacher Home Room Floating Class Retrieve Compulsory Class Subject Information Subject Teacher Number of Period Per Cycle Retrieve Elective Class Subject Information Subject Teacher Number of Period Per Cycle Retrieve Cross Class Subject Group Information Subject Teacher Number of Period Per Cycle Retrieve Subject Group Subjects Information Subject Teacher Number of Period Per Cycle e Staff STF Retrieve Current Staff with teaching duty e Code Management HSE Retrieve codes e Security SEC Get user s SUID DAT11 Extract for SOP e School SCH Copyright 2007 Education Bureau The Government of the HKSAR Page 5 All Rights Reserved WebSAMS User Manual Version 1 3 e Retrieve the available School Year School Level School Session and Class Level Student STU e Retrieve active Student Particulars Assessment ASR e Retrieve academic result of the students Staff STF e Retrieve Current Staff with teaching duty Timetabling TTL e Retrieve the final result set of the selecte
12. button X Post effects N A Notes When a query is detached from a task the query itselfwill not be deleted Delete Task LE Function Description Users can delete the tasks LL Pre requisites 1 Task should exist 2 Users have to possess Task Maintenance Function access right User Procedures 1 Click Data Management Task Maintenance on the left menu 2 Check the box beside the Task Name to be deleted Copyright 2007 Education Bureau The Government of the HKSAR Page 31 All Rights Reserved WebSAMS User Manual Version 1 3 c WebSAMS System ersion 0 9 0 BUILD 07112002 Microsoft Internet Explorer _ x File Edit View Favorites Tools Help Links gt Queen s College 28 05 2003 09 46 Logout Help PZE Current School Year 2002 28 May 2003 10 23 Assessment Special Assessme Staff Deployment CDS Task Description gt HKAT Task 1 Description HKEAA Test 1A Task SPA Data Query Execution Log Report Managemen Curent Public Examination Results Data Management Public Examination Results After Consolidation Query Maintena Budgeting Query Sharing Book cana Capital Asset Register Staff Cost Table Access C Execution Sales and Stock Staff Duty Profile Conespondence List Class List Student Info And Guardian Info Student Profile List Student Mailing Labels Export I
13. 2007 Education Bureau The Government of the HKSAR Page 53 All Rights Reserved WebSAMS User Manual Version 1 3 5 If the extraction is successful The zip file extract data for sop zip will be popped for user to save or open File Download Do you want to open or save this file Name extract_data_for_sop zip Type WinRAR ZIP archive From 10 15 35 102 ge See While files from the Intemet can be useful some files can potentially harm your computer If you do not trust the source do not open or save this file What s the risk e Post effects After downloading the file users can import it in Student Option Programme SOP Notes In the zip file extract_data_for_sop zip it contains the different data files depends on user selection in Extract Option in Excel format Here are five options for data extraction in the following For the Student Particulars 1 All active student particulars will be selected by school year school level school session and class level entered by users The repeaters will be deleted or created manually in SOP The class name of the selected students in the next school year if available The rank is in class level 5 The name of the data file is DataSetStudentParticulars xls For the Student Academic Result 1 All active Student Academic Result will be selected by school year school level school session and class level 2 All ass
14. Add to left expression Add to right expressio Report Managemen Field Operator Field C Data Management me Where Execution Export Import Report Y Note Please go to view page to save the query a Select Table and Field Click the Add to left expression to add the selected Field as the left part of the expression b Click the Add to right expression to add the selected Field as the right part of the expression c If the Value radio box is selected as the right expression users can enter some specific values in the right expression d If the Prompt radio box is selected as the right expression a will be added to the right expression automatically Users will have to manually enter the value when the query is executed e Click the Add button to add the where statement f Users can click the AND OR or button to add the operator in the statement g Users can click the Remove button to remove the Where statement s Copyright 2007 Education Bureau The Government of the HKSAR Page 11 All Rights Reserved WebSAMS User Manual Version 1 3 7 Click the Order By tab optional Student Attendance Student Activities Award amp Punishme QueryName Query 1 Assessment Query Query 1 Description gt Special Assessme Description gt Staff Deployment Table TB SCH ANNOUNCE gt CDS Field ANNOUNCEID 4 Ascendi
15. list table will be shown in the Field list box below b To include fields in the query result select one or more table fields in the Field list box Click the Add to add the selected fields Copyright 2007 Education Bureau The Government of the HKSAR All Rights Reserved Version 1 3 The fields of the selected WebSAMS User Manual Version 1 3 Alternatively to include aggregate functions e g COUNT MAX in the query result users have to apply the operator to the fields They can select an operator from the Operator drop down list and also one or more table fields and then click the Add Operator will then be applied to the selected field s Users can select any of the selected fields in the Field list box and click the Remove button to remove the field s They can also click the Remove All button to remove all the fields in the Field list box Users can select one of the selected fields in the Field list box and click the Move Up or Move Down button to change the sequence of the selected field For the query result to return distinct records users have to mark the Distinct Records radio button value to Yes 6 Click the Where tab optional Student Attendance Student Activities gt Award amp Punishme Query Name esl Assessment Query Description Query 1 Description Special Ass i ANNOUNCEID 4 DESCCHI DESCENG ENDDATE STARTDATE
16. the extraction e g no teacher assigned to class 2 11 Extract for SOP LL X Function Description This function allow user to extract data from WebSAMS into a zip file that contains Excel files for Student Option Programme SOP to import The data includes the student particulars student academic result school defined subjects whole school teacher information whole school and master timetable E Pre requisites 1 This function should be for secondary school only 2 Users have to possess Extract for SOP Function access right VU User Procedures Click Data Management gt Extract for SOP on the left menu Select the School Year School Level School Session and Class Level Select the Extract Option Xe que ce Click on the Extract button to extract the data selected in Extract Option HKAT S DAT11 01 Data Management Extract for SOP Applied Learning HKEAA Extract for SOP gt SPA Please input the extract criteria Report Manageme School Year 2007 School Level Secondary dee edi School Session Whole Day Query Maintena Class Level Secondary 3 Query Sharing tract Option Task Maintena Student Particulars C Student Academic Result L School Defined Subjects Whole School C Teacher Information Whole School Expat C Master Timetable 2008 Secondary 4 Import Data Set with Final Result Set Extract for TES Table Access C Execution Copyright
17. 51 48 helpdesk 09 06 2004 15 59 22 7 Check the box beside the Query Name to be added 8 Click the Add button Post effects N A Notes The same query can be attached to one or more tasks 2 A task can have zero or more than one query attached to it A query can be attached to zero or more than one task 3 The new task name cannot start with since the characters designate the built in standard tasks 4 Task names cannot duplicate Copyright 2007 Education Bureau The Government of the HKSAR Page 29 All Rights Reserved WebSAMS User Manual Version 1 3 Modify Task LL Function Description Users can modify the task information LJ Pre requisites Users have to possess Task Maintenance Function access right 8 User Procedures 1 Click Data Management Task Maintenance on the left menu Assessment S DAT01 01 Data Management gt Task Maintenance Special Assessmel Staff Deployment Bottom CDS g Task Description HKAT Task 1 Description HKEAA Test LA Task SPA Data Query Execution Log Report Managemer Current Public Examination Results Data Management Public Examination Results After Consolidation Query Budgeting Query Sharing Booking Capital Asset Register Table Access C Kaff Cost Execution ES Export
18. After Date Before DDAMMPY Y Y Y DDAMMPY Y Y Y Staff Dep CDS Task Task Name HKAT Task Description gt SPA Report Manageme Data Management Query Maintena Query Sharing 3 Click the Search button Click on the Task Name link to view the queries attached gt Student S DAT04 02 Data Management gt Execution Attendance Student Activities Task Name Test 1 Task Task Description Test 1A Task Query Attached Query Name Query Description Last Modified Special Assessmei ODIO 11 00 Test L est Staff Deployment Ea E CDS HKEAA SPA gt Report Managemen gt Award amp B Assessme Data Management 4 Check the boxes beside Query Name to be executed By default all queries are checked Click the Go To Execute button A new window will pop up to display the query information Select the Output Format Copyright 2007 Education Bureau The Government of the HKSAR Page 38 All Rights Reserved WebSAMS User Manual Version 1 3 2 http ncs prtsyr106 index O amp queryID 453 427 Execute Query and Task Windows Internet S DAT04 03 Data Management gt Execution Query Name helpdesk Query Description school case SQL Statement SELECT date dob FROM TB STU student Output Format Click th
19. CT_YEAR_YYYY 2002 for accounting year 2002 2003 SCHLEVEL 2 please refer to Code Management 3 School Session SCHSESSION 1 Mudo e Alor oCo Managemen FBG102 1 Accounting Year ACCT YEAR YYYY 2002 for accounting year 2002 2003 5 SCHLEVEL 2 for Primary please refer to Code Management module School Session SCHSESSION 1 Coge Managemen FBG103 1 Accounting Year ACCT YEAR YYYY 2002 for accounting year 2002 2003 2 SCHLEVEL 2 2 please refer to Code Management 3 School Session SCHSESSION 1 feierte Code Managemen FBG104 1 Accounting Year ACCT YEAR YYYY 2002 for accounting year 2002 2003 2 ISchool Level SCHLEVEL 2 please refer to Code Management 3 School Session SCHSESSION 1 for AM please refer to Code Management Copyright 2007 Education Bureau The Government of the HKSAR All Rights Reserved Page 57 WebSAMS User Manual Version 1 2 Query Name No Parameter Description Parameter Name Example Remark module FBK101 1 Accounting Month ACCTMTH YYYYMM 200303 YYYYMM FBK102 1 Accounting Year ACCT YEAR YYYY 2002 for accounting year 2002 2003 FBK103 1 Accounting Year ACCT YEAR YYYY 2002 for accounting year 2002 2003 FCA101 1 Registration Date From REG_DATE_FROM_YYYYMMDD 20030301 YYYYMMDD 2 Registrat
20. ES RERO 53 3 APPENDIX qu eais raisses iisas eaen iiini areenan r nsa Soia 56 3 1 APPENDIX I IMPORT EXPORT XML FILE 56 3 2 APPENDIX II PARAMETER LIST a a E a E AEEA a 57 Copyright 2007 Education Bureau The Government of the HKSAR All Rights Reserved WebSAMS User Manual Version 1 3 1 Module Overview 1 1 Introduction 1 1 1 Objective The Data Management module provides a mean for users to extract existing data from the WebSAMS system For data security the WebSAMS system allows WebSAMS administrator to assign different access rights to different users Copyright 2007 Education Bureau The Government of the HKSAR Page 1 All Rights Reserved WebSAMS User Manual Version 1 3 1 2 Function Chart Data Management DAT Query Maintenance Query Sharing Task Maintenance Table Access Control Execution Query amp Task Export Query amp Task Import Query amp Task Entity Relationship Diagram Table Structure Data Manual Query List Report Extract for TESS Extract for TESS xtract for Error Log Extract for SOP Copyright 2007 Education Bureau The Government of the HKSAR Page 2 All Rights Reserved WebSAMS User Manual Version 1 3 1 3 Flow Diagram Table Access Control Query Maintenance Export Query amp Task Query Sharing Report Execution Query amp Task Extract for TESS Extrac
21. Management module lSchool Session A SCHSESS 1 for AM please refer to Code Management module 4 Level A CLASSLVL 2 for Primary 1 please refer to Code Management module 5 Class Name B CLASSNAME 1A for Class 1A please refer to basic information of class details 5000011 1__ VW STU LATESTSTUDENT SCHYEAR 2003 for school year 2003 2 School Level VW STU LATESTSTUDENT SCHLVL l2 iDicase Teter 10 Code Management School Session VW STU LATESTSTUDENT SCHSESS 1 4 iplento Code Management 4 Class Level VW STU LATESTSTUDENT CLASSLVL P1 for emaryst please reteritoxc ode Management module Class TB SCH SCHCLASS CLASSNAME 1A for Class 1A please refer to basic information of class details SUQO0012 1 School Level A SCHLVL 2 1 please refer to Code Management echootSEsslon A SCHSESS 1 for AM please refer to Code Management module Level A CLASSLVL 2 for Primary 1 please refer to Code Management module 4 Name D CLASSNAME 1A for Class 1A please refer to basic information of class details SUQ0030 1 School Year VW STU LATESTSTUDENT SCHYEAHR 2003 for school year 2003 SUQ0031 1 School Year VW STU LATESTSTUDENT SCHYEAHR 2003 for school year 2003 2 School Level VW STU LATESTSTUDENT SCHLVL EE dd Dicase relenio Code Manageme School Session VW STU LATESTSTUDENT SCHSESS 1 1 Code Management 4 Class Level VW STU LATESTSTUDENT CLASSLVL P1 T
22. PETE code Management module All Rights Reserved Copyright 2007 Education Bureau The Government of the HKSAR Page 59 on ebSAMS User Manual Version 1 2 Query Name No Parameter Description Parameter Name Example X Remark Name TB SCH SCHCLASS CLASSNAME 1A for Class 1A please refer to basic information of class details TEACHSTF s E TEACHSTFD TVLDOC Lr Copyright 2007 Education Bureau The Government of the HKSAR All Rights Reserved Page 60
23. Report Table Structure Function access right User Procedures 1 Click Data Management Report on the left menu 2 Click the Table Structure tab Fill in the search criteria 3 Click the Search button to search for a particular table Staff Deployment S DAT08 02 Data Management gt Report gt Table Structure CDS gt HKEAA Table Name Lo o o Y gt SPA Table Description pue Report Manageme Held Name Data Management EL ed Module All Query Maintena Query Sharing Task Maintenan Bottom por Table Description Execution TE_STU_STUCTRL Control table for system use Document dategory Export D y indicator Import Enrolment suvery temporary table gt Timetabling H porary table Code Management Security Sibling group counter E Mail Student default value gt Customization 7 Student departure information Student document TR CTTITNEOYEDPTMID Cham ie rene irm Gee deme thle 4 Click on the Table Name link to view the database table structure in a new window Ln Post effects N A Notes N A Copyright 2007 Education Bureau The Government of the HKSAR Page 47 All Rights Reserved WebSAMS User Manual Version 1 3 2 9 3 Data Manual LL Function Description Users can view the data manual LL Pre requisites Users have to possess Report Data Manual Function access right 8 User Procedures 1 Click Data Management Repor
24. Staff Code 7 Data Management Query Maintenal 4 If the extraction is successful successful message together with the extracted ZIP file will be shown S DAT09 02 Data Management gt Extract for TESS gt Extract Extract Extract Data for TESS Extraction Suocessfully Done Please Click to download PAMoode zip gt Report Managemen w Data Management 5 Click the link of the ZIP file to download it for TESS Post effects After downloading the file users can import it into the stand alone timetabling system TESS Copyright 2007 Education Bureau The Government of the HKSAR Page 51 All Rights Reserved WebSAMS User Manual Version 1 3 Notes The system will always extract information of the new school year 2 The last extraction date of the timetabling extraction file is displayed together with a hyperlink to download the last extracted file The users can download the last extracted file or re extract the timetabling information 3 Ifthere is no error during extraction the timetabling information will be saved in a ZIP file consisting of two files as follows S to ttxt staffcd dat 4 f there is an error during extraction users will be instructed to go to the Error Log page to view the error details 5 The extracted files will overwrite the previous copy in the server Error Log LL Function Description This function will display all the errors that occu
25. Staff Duty Profile Import Report Staff Correspondence List Extract for TES Class List Student Info And Guardian Info gt Timetabling Student Profile List Code Management Student Mailing Labels Security Top zl 2 Click on the Task Name link to view the task information 3 Click the Task Info tab S DAT01 03 Data Task Maintenance gt Task Information Special Assessme Staff Deployment CDS Task Name Task 1 gt HKAT Task Description Task 1 Description gt HKEAA SPA Report Managemer Data Management Query Maintena Query Sharing Modify the Task Description Click the Save button to save the Task Info changes To change the attached queries click the Attach Detach Query tab Copyright 2007 Education Bureau The Government of the HKSAR Page 30 All Rights Reserved WebSAMS User Manual Version 1 3 gt Staff CDS gt HKAT Attach Detach Query gt HKEAA Task Name Task 1 Task Description Task 1 Description SPA S DAT01 04 Data Management gt Task Maintenang i Detach Query r pe vin ntkwok 23 06 2008 17 58 10 dm admin 2600000308921 vin ntkwok 23 06 2003 18 42 44 Data Management Query Query Sharing 7 To attach other queries click the Add button Follow the same steps as in Add Task to attach new queries To detach queries click the box beside the Query Name to be detached Click the Delete
26. Version 1 3 3 Appendix 3 1 Appendix Import Export XML File Format lt xml versionz 1 0 encoding UTF 8 gt lt task gt lt task name gt lt task name gt lt task desc gt lt task desc gt lt queries gt lt query gt lt query name gt lt query name gt lt query desc gt lt query desc gt lt sql code gt lt sql code gt lt distinct gt N lt distinct gt lt modify date gt lt modify date gt lt query gt lt queries gt lt task gt Note 1 The lt task gt lt task name gt and lt task desc gt XML tags are not required if only queries are imported 2 The lt distinct gt XML tag N can either be 0 or 1 where 1 means distinct records Copyright 2007 Education Bureau The Government of the HKSAR Page 56 All Rights Reserved WebSAMS User Manual Version 1 2 3 2 Appendix Il Parameter List Query Name No Parameter Description Parameter Name Example Remark AREA l BIRCTY DQEXLOG o EACMPRSLT 1 Exam Year Lotes Ie ee YYYY 2 Exam Code HK A LE EASBJRSLT 1 Exam Year TB HKE ENTRY EXAMYEAR 2003 YYYY 2 Code TB HKE ENTRY EXAMCODE A HK A LE EXMRSLCON 1 Exam Year A EXAMYEAR 2003 YYYY 2 Code A EXAMCODE A HK A LE 3 jSchool Unique ID A SUID 9996 4 digit s number FBG101 1 Accounting Year AC
27. Web USER MANUAL Data Management Document 27e Copyright 2007 Education Bureau The Government of the HKSAR All Rights Reserved WebSAMS User Manual Version 1 3 Table of Contents L MODULE OVERVIEW wissssctscicsccsatscssnonsstestssnusnssusviseschasusstsuesscecessdatnssaenisucsassts CN SEE QUK UE 1 1 1 INTRODUCTION ee e re eet bee i uua 1 1 1 1 Objective utin eub ae 1 1 2 2 1 3 FLOW DIAGRAM A ees eee Mem Eod e rH ede ee Mete EE dede Pee mada 3 1 4 INTERACTIONS WITH OTHER MODULES 4 1 5 USER GROUP AND ACCESS 7 2 OPERATION PROCEDURES csssssssscssscscesccsscsscsscssscesesssessensessssssesscssesscsssssneesesssseseesoes 9 2d QUERY MAINTENANCE ete Casert rbd eet eb 9 Create query by Wizatd eee seed ae emite e dee ge ERE 9 Advance Query vs dees pede B a dete e ee 15 22 SEARCH DELETE AND MODIFY 0000000 2000 23 Search and Delete Query edente 23 Modify QUERY cT 24 Doce OD 26 2 4 TASK MAINTENANCE
28. a Management r 50000 Duty Profile Class Teacher E Marten r 57900 Staff Duty Profile Subject Teacher An ied r 5700012 Staff Duty Profile Subject Group Teacher for Subject Group Subject lt 00 _ Duty Profile Committee Duty Assignment Everton 00019 Staff Duty Profile Post Duty Assignment FSTQO0IB Staff Duty Profile Staff Personal Details Tr Too Staff Correspondence List a EN Report FTEACHSTF Teaching Staff Extract for TESS TEACHSTFD Dummy Teaching Staff Timetabling Code Management Top 4 Check the boxes beside Query Name to be exported 5 Click the Export button LU The extracted queries can be used outside WebSAMS Copyright 2007 Education Bureau The Government of the HKSAR Page 41 All Rights Reserved WebSAMS User Manual Version 1 3 Notes 1 Users can input Chinese character in the search criteria 2 When exporting there will be a default filename 3 All queries will be exported as Advance Query 4 Only the SQL statement of the data query will be extracted but not the query data 5 The exported queries will be in XML format Appendix 1 Copyright 2007 Education Bureau The Government of the HKSAR Page 42 All Rights Reserved WebSAMS User Manual Version 1 3 2 7 2 Export Task LL Function Description Users can export tasks LL Pre requisites Users have to p
29. at containing school start and end date School class structure Classroom designation IClass based subject SCH CLSSUBICOMP IClass based subject component SCH CLSSUBJCOMPTCHR IClass based subject component teacher B_SCH_CLSSUBIGRP Class subject group i e association between class and subject group and group for cross class subject B_SCH_CLSSUBJTCHR IClass based subject teacher SCH CLSTCHR Class teacher 5 School disabledenabled cycle B_SCH_DISPLAYORDER Staff rank display order SCH DIVSUBJGRP Group for cross class subject TB 5 DSGSUBI Cross class subject c Check the box beside the Table Name d Click the Add button to add the table s to the query 5 Click the Select tab Assessment gt Special Assessme Staff Deployment CDS gt HKAT gt HKEAA al SPA Query EE Field Note multi Nata Management selection is supported leew SA Task Maintenar Table Access C TB_SCH_ANNOUNCE ANNOUNCEID QueryName Query 1 Query 1 Description Operator Distinct Records C Yes 4 No Execution TB SCH ANNOUNCE SUID Export Import Report Extract for TES Timetabling Code Management Security a Select one table from the Table drop down
30. ause For the WHERE clause user can specify a field equals to a prompt e g SELECT tableA name FROM tableA WHERE tableA id If a SQL statement is saved with prompt user will be required to enter the specific value when executing the SQL statement GROUP BY clause Uses can group by columns alias names or functions The result of the query contains one row for each distinct set of values in the named columns aliases or functions All NULL containing rows are treated as a single set The resulting rows are often referred to as groups since there is one row in the result for each group of rows from the table list Aggregate functions can then be applied to these groups to get meaningful results When GROUP BY is used the select list HAVING clause and ORDER BY clause must not reference any identifier that is not named in the GROUP BY clause The exception is that the select list and HAVING clause may contain aggregate functions HAVING clause This clause selects rows based on the group values and not on the individual row values The HAVING clause can only be used if either the statement has a GROUP BY clause or the select list consists solely of aggregate functions Any column names referenced in the HAVING clause must either be in the GROUP BY clause or be used as a parameter to an aggregate function in the HAVING clause Copyright 2007 Education Bureau The Government of the HKSAR Page 17 All Rights Reserved W
31. bleA id tableB id The condition for joining the values in two columns does not need to be equality Users can use any of the other comparison operators not equal lt gt greater than gt less than lt greater than or equal to gt and less than or equal to lt Inner joins and outer joins differ in their treatment of rows that have no match in the other table rows appear in an inner join only if both tables contain at least one row that satisfies the join condition By contrast an outer join contains rows whether or not a row exists in the opposite table to satisfy the join condition Use the keywords LEFT or RIGHT to identify the table that is to appear in its entirety ALEFT OUTER JOIN contains every row in the left hand table SELECT tableA id tableB name FROM tableA LEFT OUTER JOIN tableB ON tableA id tableB id ARIGHT OUTER JOIN contains every row in the right hand table SELECT tableA id tableB name Copyright 2007 Education Bureau The Government of the HKSAR Page 21 All Rights Reserved WebSAMS User Manual Version 1 3 FROM tableA RIGHT OUTER JOIN tableB ON tableA id tableB id Copyright 2007 Education Bureau The Government of the HKSAR Page 22 All Rights Reserved WebSAMS User Manual Version 1 3 2 2 E Search Delete and Modify Query Search and Delete Query Funct ion Description Users can search and delete queries by this function Pr
32. d data set Code Management HSE e Retrieve codes Copyright 2007 Education Bureau The Government of the HKSAR Page 6 All Rights Reserved WebSAMS User Manual Version 1 3 1 5 User Group and Access Right Users have to possess certain access rights to use the corresponding functions in Data Management module Users assigned to different user groups will have different access rights Among the built in user groups only SYSTEM_ADMIN SCHOOL_HEAD DM_ADMIN DM_USER and TIMETABLING_ADMIN can use functions of Data Management module The pre assigned access rights of these five built in groups are depicted below S SEC02 04 Security gt Access Control gt User Group gt Access Right Maintain Function Access Rights Group ID SYSTEM ADMIN Group Description WebSAMS System Administrator Chinese Group Description PLE ARES Module Function Data Management Bottom A v Query Maintenance iv v Query Sharing 7 v v Task Maintenance v v Table Access Control v v v Execution v v Export v v Import v v Report Entity Relationship Diagram v v Report Table Structure v v Report Data Manual v Report Query List v v Extract for TESS v v Extract for TESS Error Log y Extract for SOP M Reset save close Top S SEC02 04 Security gt Access Control gt User Group gt Access Right Maintain Funct
33. de Query Name to be executed Click the Go To Execute button A new window will pop up to display the query information Select the Output Format http ncs prtsvr106 index O amp queryID 453 427 Execute Query and Task Windows Internet Gx lox S DAT04 03 Data Management gt Execution Query Name helpdesk Query Description school case SQL Statement SELECT date dob FROM TB STU student Click the View Result button to view the execution result in a new window Click the Save Result button to save the execution result to a local hard disk 9 If users have selected more than one query click the Next button to view the information of the next query and repeat Step 6 to Step 8 Copyright 2007 Education Bureau The Government of the HKSAR Page 36 All Rights Reserved WebSAMS User Manual Version 1 3 Post effects After the query is executed the data will be extracted in the format specified by the users Audit trail will be updated with the following information i The person who executes the query ii Query Name iii SQL statement iv Date of execution E Notes Users can input Chinese character in the search criteria 2 The system will perform a syntax check and prompt an error message if the query is invalid 3 lf users try to execute a query with restricted tables that they cannot access errors will be prompt 4 Only the following built in user groups ca
34. e View Result button to view the execution result in a new window Click the Save Result button to save the execution result to a local hard disk 9 If users have selected more than one query click the Next button to view the information of the next query and repeat Step 6 to Step 8 Post effects After the query is executed the data will be extracted in the format specified by the users Audit trail will be updated with the following information i The person who executes the query ii Query Name iii SQL statement iv Date of execution E Notes Users can input Chinese character in the search criteria 2 The system will perform a syntax check and prompt an error message if the query is invalid 3 If users try to execute a query with restricted tables that they cannot access errors will be prompt 4 Only the following built in user groups can execute queries with the STF module tables i SCHOOL HEAD ii STAFF MANAGEMENT 1 STAFF MANAGEMENT 2 iv STAFF MANAGEMENT 3 Copyright O 2007 Education Bureau The Government of the HKSAR Page 39 All Rights Reserved WebSAMS User Manual Version 1 3 5 Only the following built in user groups can execute queries with the FMP module tables i SCHOOL_HEAD i FMP ACCT CLERK ii FMP PETTYCASH CLERK iv FMP ADMIN v FMP USER1 vi FMP USER2 USERS 6 For schools having two WebSAMS FMP queries are available in the first WebSAMS onl
35. e requisites Users have to posses Query Maintenance Function access right User Procedures 1 Click Data Management Query Maintenance on the left menu 2 Fi Student gt Attendanc Student Activities gt Award amp Assessment Special Assessmei Staff Deployment CDS gt HKAT HKEAA gt SPA gt Report Table Access Execution Export Import Report Extract for TESY Timetabling Code Management Security gt E Mail Customization ll in the search criteria Click the Search button to search for queries S DAT03 05 Data Management gt Query Maintenance e Query Name la Query Description Table in Query Owner Last Modified Date Last Modified Date After Before DDMM Y Y Y Y DDAMM Y Y Y Y Add Advance Add wizard ER search Reset Bottom sysadmin 25 04 2003 124046 sysadmin 2005 2003 121944 188 sysadmin 20 05 2003 12 19 24 IB Query Name nagemen sysadmin 19 03 2003 15 45 57 IBI RETRIEVE DATA FROM TABLE TB_SCH_SCHCLASS snfong 11 04 2003 17 34 57 180 feta 7 B sysadmin 0904 2003 11 11 00 Current Public Examination Results for HKEA Subject Components Public Examination Results for HKEA Subjects Asset Details
36. ebSAMS User Manual Version 1 3 ORDER BY clause This clause sorts the results of a query Each item in the ORDER BY list can be labeled as ASC for ascending order the default or DESC for descending order If the expression is an integer n then the query results will be sorted by the n th item in the select list UNION The results of several SELECT statements can be combined into a larger result using UNION The component SELECT statements must each have the same number of items in the select list and cannot contain an ORDER BY clause SELECT statement UNION ALL SELECT statement ORDER BY expression integer The results of UNION ALL are the combined results of the component SELECT statements The results of UNION are the same as UNION ALL except that duplicate rows are eliminated Eliminating duplicates requires extra processing so UNION ALL should be used instead of UNION where possible If ORDER BY is used only integers are allowed in the order by list These integers specify the position of the columns to be sorted The column names displayed are the same column names that are displayed for the first SELECT statement SQL Functions ABS numeric expression Returns the absolute value of a numeric expression ACOS numeric expression Returns the arc cosine in radians of a numeric expression ASCII string expression Returns the integer ASCII value of the first byte in a string expression ASIN nu
37. emus Customization i Data Management Timetabling E Mail Code Management FMP HKAT HKEAA E Mail Report Management Customization School Management Security Ln Post effects 1 Users of the DM USER Data User and DM ADMIN Data Admin user groups cannot execute a query with the restricted tables in the query Data Admin cannot create a query with the restricted tables in the query Data Admin users of the SYSTEM ADMIN user group cannot share a query to other users who cannot access the tables in the query LL Notes The assignment of restricted tables will not affect other modules Copyright 2007 Education Bureau The Government of the HKSAR Page 34 All Rights Reserved WebSAMS User Manual Version 1 3 2 6 Execute Query Task 2 6 1 Execute Query LL Function Description Users can execute queries They can specify the output format of the data extracted from the system LL Pre requisites 1 Queries should be prepared 2 Users have to possess Execution Function access right User Procedures 1 Click Data Management Execution on the left menu 2 Select a Query Fill in the search criteria Assessment S DAT04 01 Data Management gt Execution Special gt Staff De ME nt Query Name Query Description CDS mera Table in Query Owner L2 Last Modified Last Modified m HKEAA Date After E Date Before
38. eserved WebSAMS User Manual Version 1 3 2 8 Import 2 8 1 Import Task and Import Query LL Function Description For Import Task This function provides a mean to import tasks from files For Import Query This function provides a mean to import queries from files Therefore users can create modify query statements in other environments that allow editing of the Structural Query Language SQL E Pre requisites 1 Queries Tasks must be prepared in XML format Appendix 1 2 Users have to possess Import Function access right User Procedures 1 Click Data Management gt Import on the left menu Staff Deployment CDS HKEAA S DAT05 01 Data Management gt Import SPA Report Managemer Data Management Query Click the Browse button this window to find the file 2 After the file has been selected the text box will be filled in Click the Import button gt Assessment S DAT05 02 Data Management gt Import Special Assessmei Staff Deployment New CExisting gt CDS Task Name flestiATsk 00 HKAT Task Description Test 1A Task HKEAg Sw 7 7 Query Query Description Duplicate Report Managemen Data Management E 1 4 Query Maintena Refresh save Query Sharing Task Maintenar Table Access 6 Change to No after Execution Refresh i
39. essage will be prompted The import query s name cannot start with The import task s name cannot start with All queries will be imported as Advance Query For Import Task only Copyright 2007 Education Bureau The Government of the HKSAR Page 45 All Rights Reserved WebSAMS User Manual Version 1 3 2 9 Report 2 9 1 Entity Relationship Diagram LL Function Description Users can view the entity relationship diagram of the WebSAMS database LL Pre requisites Users have to possess Report Entity Relationship Diagram Function access right User Procedures 1 Click Data Management Report on the left menu 2 By default the Table Structure tab is displayed 3 Click the ER Diagram tab Staff Deployment CDS HKAT HKEAA SPA gt Report Managemen S DAT08 01 Data Management gt Report gt Entity Relationship Diagram ER Dagrany Data Management Query Maintena Query Sharing Select the Module 5 Click the Preview button to view the entity relationship diagram in a new window 6 Click the Download button to save the file Post effects N A Notes N A Copyright 2007 Education Bureau The Government of the HKSAR Page 46 All Rights Reserved WebSAMS User Manual Version 1 3 2 9 2 Table structure Lj Function Description Users can view the database table structure LL Pre requisites Users have to possess
40. essments and terms include annual result will be retrieved for the specified students 3 Two data files will be generated and called DataSetAcademicResult xls for the academic result and DataSetAcademicResultSubject xls for the subject involved For the School Defined Subjects Copyright O 2007 Education Bureau The Government of the HKSAR Page 54 All Rights Reserved WebSAMS User Manual Version 1 3 1 Retrieve all active school defined subjects 2 The name of the data file is DataSetSchoolDefinedSubject xls For the Teacher Information 1 Retrieve all active teachers who have teaching duty 2 The name of the data file is DataSetTeacher xls For the Master Timetable 1 Retrieve the final result set of the selected data set 2 SOP subject group code SOP subject code i e Combined Science Code and SOP time block will be retrieved for the time slot if user has been imported the Group for Cross Class Subject for the corresponding class levels 3 The timetabling of next school year and next class level will be retrieved of the searching criteria The label will be updated when searching criteria are changed E g If user input School Year is 2008 and Class Level is Secondary 3 the timetable of 2009 Secondary 4 will be extracted 4 The name of the data file is DataSetMasterTimetable xls Copyright 2007 Education Bureau The Government of the HKSAR Page 55 All Rights Reserved WebSAMS User Manual
41. expression expression LCASE string expression LEFT string expression integer expression LENGTH string expression LESSER expression1 expression2 LOCATE string expression 1 string expression 2 integer expression LOG numeric expression LOG10 numeric expression LOWER expression Converts the expression into a date and removes any hours minutes or seconds Returns a string representing a date expression in the specified format Returns a number from 1 to 31 corresponding to the day of the month of the given date Returns the name of the day of the week from a date Converts a number from radian to degree Returns the difference in the SOUNDEX values between the two string expressions Returns the exponential function e to the power of a number Returns the floor of largest integer not greater than a number Returns the greater of two parameter values Returns a number from 0 to 23 corresponding to the hour component of a datetime Generates integer values starting at 1 for each successive row in a query If the first expression is the NULL value then the value of the second expression is returned If the first expression is not NULL the value of the third expression is returned If the first expression is not NULL and there is no third expression NULL is returned Returns the first non NULL expression in the parameter list Converts all characters in a string to lower case
42. f the query name Export is modified and no more duplication with other queries in the system Report Extract for TESS Copyright 2007 Education Bureau The Government of the HKSAR Page 44 All Rights Reserved WebSAMS User Manual Version 1 3 S DAT05 04 Data Management gt Import Task 1 Task 1 Description Test LA Task Test LA Task 7 Bottom Task Task Description Enter a Task Name and Task Description a Users can also click on the Existing radio box A new window will pop up b Users can then click on one of the task name and the system will automatically fill in the Task Name and Task Description fields in the original window Users can click on the View Query link to view the query information in a new window Users can edit the name of the query and click the Refresh button The Duplicate column will be updated to No Check the boxes beside Query Name to be imported Click the Save button Post effects The imported queries tasks can be used in WebSAMS Notes If the imported query s name duplicates with an existing query in the system the system will display an error If the imported task s name duplicates with an existing task in the system the system will append the queries attached to the imported task after the existing queries of that task If the users import a query with table that they do not have the right to access an error m
43. fies rows which aggregate data is to be collected By default rows are returned from relational databases in an order that has no meaning Users can use the ORDER BY clause to sort the rows in the result set Most of the clauses are optional but if they are included then they must appear in the correct order Copyright 2007 Education Bureau The Government of the HKSAR Page 16 All Rights Reserved WebSAMS User Manual Version 1 3 select list column name expression AS alias name The select list is a list of expressions separated by commas specifying what will be retrieved from the database An asterisk means to select all columns of all tables in the FROM clause Aggregate functions are allowed in the select list see SQL Functions Subqueries are also allowed in the select list Each subquery must be within parentheses Alias names can be used throughout the query to represent the aliased expression If the optional alias name is not specified after an expression the expression itself will be shown in the query result WHERE clause This clause specifies which rows will be selected from the tables named in the FROM clause It is also used to do joins between multiple tables This is accomplished by putting a condition in the WHERE clause that relates a column or group of columns from one table with a column or group of columns from another table Both tables must be listed in the FROM cl
44. ion Access Rights Group ID SCHOOL_HEAD Group Description School Head Chinese Group Description Module Function Tata Management Bottom Query Maintenance Query Sharing K 11 K Task Maintenance lt S S S S S S S K Table Access Control lt K Execution Export import Report Entity Relationship Diagram Report Table Structure Report Data Manual Report Query List Extract for TESS Extract for TESS Error Log B Extract for SOP Ii Reset save tese Top 9 S S S S S S S Kl Copyright 2007 Education Bureau The Government of the HKSAR Page 7 All Rights Reserved WebSAMS User Manual Version 1 3 S SEC02 04 Security gt Access Control gt User Group gt Access Right Group ID DM ADMIN Group Description Tata Management Administrator Chinese Group Description Tee Module Function Data Management Bottom CQ 0 Delete 02 Execute Query Maintenance Sharing Maintenance Table Access Control Execution Export Import Report Entity Relationship Diagram Report Table Structure Report Data Manual Report Query List Extract for SOP
45. ion Date REG DATE TO YYYYMMDD 20030301 YYYYMMDD 3 Write off Date From WRITE OFF DATE FROM YYYYMMDD 120030301 YYYYMMDD 4 Wrftite off Date To WRITE OFF DATE TO YYYYMMDD 20030301 YYYYMMDD FCA102 FSC101 1 Payroll Month From PAYROLL MONTH FROM YYYYMM 200303 YYYYMM 2 Payroll Month To PAYROLL MONTH TO YYYYMM 200303 YYYYMM FSS101 1 ibas Voucher Date IBURCHASE_DATE_FROM_YYYYMMDD 20030301 YYYYMMDD 2 Purchase Voucher Date To PURCHASE DATE TO YYYYMMDD 20030301 YYYYMMDD 3 Sales Voucher Date From SALES DATE FROM YYYYMMDD 20030301 YYYYMMDD 4 Sales Voucher Date SALES DATE TO YYYYMMDD 20030301 YYYYMMDD 5 Write off Date From WRITEOFF DATE FROM YYYYMMDD _ 120030301 6 Wrtite off Date To WRITEOFF DATE TO YYYYMMDD 20030301 YYYYMMDD FSS102 NATION RELIG SBJCMP l i SCHHUS STQ0010 STQ0011 l STQ0012 l STQ0015 STQ0019 5 0001 STQ0030 ace STREAM zo E Copyright 2007 Education Bureau The Government of the HKSAR All Rights Reserved Page 58 WebSAMS User Manual Version 1 2 Query Name No Parameter Description Parameter Name Example Remark 5000010 1 School Year A SCHYEAR 2003 for school year 2003 5 School Level A SCHLVL 2 for Primary please refer to Code
46. ir queries LL Pre requisites 1 Queries should be prepared 2 Users have to possess Query Sharing Function access right VU User Procedures Click Data Management gt Query Sharing on the left menu 2 Fill in the search criteria Click the Search button to search for a particular query Only the queries owned by the user will be listed Click on the Query Name from the search results to view the query The existing users that can be assigned the rights to copy execute or view the shared query will be shown Award amp Punishme Assessment Special Assessme Query Name Staff Deployment HKAT HKEAA Report Management Data Management Query Task Maintenan Table Access Executing Export Import Report Extracting for gt Timetabling 5 To add users to the shared user list select the users from the list of Available User and adds to the list of User Selected 6 To remove users from the shared user list select the users from the list of User Selected and put them back to the list of Available User 7 Click the Save button Post effects Copyright 2007 Education Bureau The Government of the HKSAR Page 26 All Rights Reserved WebSAMS User Manual Version 1 3 Notes 1 There are three user groups with the access rights of Data Management SYSTEM_ADMIN Users who have the access right to add de
47. lete update copy the SQL queries in the Data Management module DM_ADMIN Users who have the access rights to add delete update copy the SQL queries in the Data Management module DM_USER Users who can execute the queries if given the access right to execute the query 2 There are three types of access right for each query Share Access If the owner of the query shares the query to a DM_ADMIN user the user will have the Share Access Right In other words that user can copy and execute the query Execute Access If the owner of the query shares the query to a DM_USER user the user will have Execute Access to the query In other words that user can execute the query to extract data View Access All users will have the View access right These users can view the following Query name Query description Query owner Last modified Copyright 2007 Education Bureau The Government of the HKSAR Page 27 All Rights Reserved WebSAMS User Manual Version 1 3 2 4 Task Maintenance Add Task LL X Function Description For convenience several queries can be combined together to form a task This function is used to add a new task and assign queries to the task LL Pre requisites 1 Queries should be prepared 2 Users have to possess Query Sharing Function access right User Procedures 1 Click Data Management Task Maintenance on the left menu 2 Click the Add button
48. meric expression Returns the arc sine in radians of a number ATAN numeric expression Returns the arc tangent in radians of a number 2 numeric expression1 Returns the arc tangent in radians of the numeric expression2 ratio of two numbers AVG expression The mean of the supplied expression over the returned rows CEILING numeric expression Returns the ceiling smallest integer not less than of a number CHAR integer expression Returns the character with the ASCII value of a number CONVERT data type Returns an expression converted to a expression format style supplied data type COS numeric expression Returns the cosine of a number COT numeric expression Returns the cotangent of a number COUNT expression The number of rows in the supplied group Copyright 2007 Education Bureau The Government of the HKSAR Page 18 All Rights Reserved WebSAMS User Manual Version 1 3 where the expression is not NULL COUNT The number of rows in each group DATE expression DATEFORMAT datetime expression string expression DAY date expression DAYNAME date expression DEGREES numeric expression DIFFERENCE string expression 1 string expression 2 EXP numeric expression FLOOR numeric expression GREATER expression1 expression2 HOUR datetime expression IDENTITY expression IFNULL expression 1 expression 2 expression 3 ISNULL
49. mport Report Extract for TESS gt Timetabling Code Management 3 Click the Delete button Post effects N A Notes 1 When a task is deleted the queries attached to the task will not be deleted 2 Standard tasks cannot be deleted The checkboxes of the standard tasks are disabled Copyright 2007 Education Bureau The Government of the HKSAR Page 32 All Rights Reserved WebSAMS User Manual Version 1 3 2 5 E Maintain Table Access Function Description Users can restrict access to tables by users of DM ADMIN and DM USER user groups Pre requisites Users have to possess Table Access Control Function access right User Procedures 1 Click Data Management Table Access Control on the left menu 2 Select a User Name Staff Deployment gt HKAT gt HKEAA Report Manageme Data Management Query Query Sharing Task Maintenan Executing Export Import Report Extracting for Timetabling Code Mgt Security E Mail Customization User Name Student Activities Assessment Attendance Award amp Punishment CDS Code Management Customization Data Management E Mail EMP HKAT Report Management School Management Security Special Assessment Staff Staff Deployment Student Timetabling namne Restrict Tables 3 To set the restricted tables in a module click
50. n execute queries with the STF module tables i SCHOOL HEAD ii STAFF MANAGEMENT 1 STAFF MANAGEMENT 2 iv STAFF MANAGEMENT 3 5 Only the following built in user groups can execute queries with the FMP module tables i SCHOOL HEAD FMP ACCT CLERK FMP PETTYCASH CLERK iv FMP ADMIN v FMP USER1 vi FMP USER2 USERS 6 For schools having two WebSAMS FMP queries are available in the first WebSAMS only For example a school with two WebSAMS Primary AM and PM FMP queries are available in Primary AM WebSAMS but not Primary PM WebSAMS Users have to login the Primary AM WebSAMS to run FMP queries under Data Management Copyright 2007 Education Bureau The Government of the HKSAR Page 37 All Rights Reserved WebSAMS User Manual Version 1 3 2 6 2 Execute Task LL Function Description Users can execute tasks They can specify the output format of the data extracted from the system LL Pre requisites 1 Tasks should be prepared 2 Users have to possess Execution Function access right User Procedures 1 Click Data Management gt Execution on the left menu 2 Select a Task Fill in the search criteria Student S DAT04 01 Data Management gt Execution Attendance Student Activities Query Query Name Query Award amp Table in Query 1 9 1 1 Last Modified Last Modified Special A Date
51. n the Basic Information page School Managemer gt Student Basic information Basic Information Student Activities Query Name Attendance Award amp Punishme Query Description gt Assessment Special Assessmel Note Please go to view page to save the query 4 Click Table tab gt Special Assessme S DATO2 02 Data Managemeny gt Query Maintenance gt Table Staff Deployment CDS Query Name Query 1 gt HKEAA Query Query 1 Description gt SPA Description Report Managemen Selected List of Table Data Management m Table Name Table Description No Record Found LB add Delete Note Pledge go to view page to save the query Task Maintenar Table Access Execution Export Import Report Extract for TESS a Click the Add button b Anew window will pop up Enter the search criteria and click the Search button The tables that the users have access rights will be shown Copyright 2007 Education Bureau The Government of the HKSAR Page 9 All Rights Reserved WebSAMS User Manual E Query Wizard Search Table Microsoft Internet Explorer S DATO2 09 Data gt Query Maintenance gt Wizard Mode Search Criteria Table Name Table Descrip Table Description School announcement School assessment evaluation period Teraporary table of storing calendar report School calendar form
52. ng Note multi D gt HKAT selection is Descending supported HKEAA gt SPA Report Managemen Data Management Order By Query Sharing STARTDATE Task Maintenar Table Access C Execution Export Import Report a Select Table and Field b Select the order as Ascending or Descending order Click the Add to add the Order By statement s C Users can select any of the selected fields in the Order By list box and click the Remove button to remove the field s Users can also click the Remove All button to remove all the fields in the Order By list box d Users can select one of the selected fields in the Order By list box and click the Move Up or Move Down butoon to change the sequence of the selected field 8 Click the Group by tab optional o v eee Assessment Special Assessme Staff Deployment CDS QueryName Query 1 HKAT Query Query 1 Description HKEAA Description gt SPA Table TB SCH ANNOUNCE v Field Note multi ment selection is END E om Suppor Query Sharing Task Maintenar Table Access C Execution Export Import Report Extract for TES gt Timetabling Remove Remove All Mave Up Move Down Note Pease go toffe w page to save the query Code Management Copyright 2007 Education Bureau The Government of the HKSAR Page 12 All Rights Reserved WebSAMS User Ma
53. nt of the HKSAR Page 20 All Rights Reserved WebSAMS User Manual Version 1 3 SQRT numeric expression SUM expression STRING string expression 5543 SUBSTRING string expression start length TAN numeric expression TRIM string expression TRUNCATE numeric expression integer expression UCASE string expression UPPER expression YEAR date expression Returns the square root of a number The sum of the expression over the returned rows Concatenates one or more strings into one large string Returns a substring of a string Returns the tangent of a number Removes leading and trailing blanks from a string Truncates a number at a specified number of places after the decimal point Converts all characters in a string to upper case Converts all characters in a string to upper case Returns a number corresponding to the year of the given date Users can use the optional keyword DISTINCT with AVG SUM LIST and COUNT to eliminate duplicate values before the aggregate function is applied The expression to which the syntax statement refers is usually a column name It can also be a more general expression Join types There are several types of join conditions The most common condition the equal join is based on equality The following query lists each order number and the name of the customer who places the order SELECT tableA id tableB name FROM tableA JOIN tableB ON ta
54. nual Version 1 3 9 Click the Having tab optional Student gt Attendance gt Student Activities Award amp Punishme Select Table and Field Click the Add to add the Group By statement s If the list is not empty then all non operator fields in the selected list must be included For example select suid schid max schyear from tb sch school group by suid is not supported while select suid schid max schyear from tb sch school group by suid schid is supported Users can select any of the selected fields in the Group By list box and click the Remove button to remove the field s Users can also click the Remove All button to remove all the fields in the Group By list box Users can select one of the selected fields in the Group By list box and click the Move Up or Move Down button to change the sequence of the selected field eee S DAT02 07 Data Management gt Query Maintenance gt Having Query Name Query 1 Query Query 1 Description gt Assessment Description Table TB SCH ANNOUNCE Field ANNOUNCEID 4 DESCCHI DESCENG ENDDATE STARTDATE x gt Selected Field Report Manageme Add to left expression Add to right expression k Field Expression Operator Field Exns amp sion C Value Query d Task Maintena rome d Table Access C Execution Export
55. on the link under the module name in the Module Name Restrict Tables list 4 A new window will pop up A list of Tables belonging to the module will be shown Check the boxes beside the name of the tables to be added to the restricted table list Copyright 2007 Education Bureau The Government of the HKSAR Page 33 All Rights Reserved WebSAMS User Manual Version 1 3 samsuser Bottom m m TB MISC PREFERENCE Storing User preference TB MISC SCHLOGO Table storing school logo image TB MISC USER INFO Misc User information Click the Save button to return to the main window If there is a small triangle besides a module click the small triangle to expand and show the user s restricted tables 7 Click the small triangle again to collapse and hide the restricted tables Staff Deployment S DAT10 01 Data Management gt Table Access Control gt CDS gt HKAT User Name alberwong HKEAA 77 777777 7 DE Module Name Restrict Tables Report Managemer Data Management Table Description This table stores the grade conversion table component and its parameters Query Maintena Query Sharing S This Table Stores The Data Type And Data Values Task Maintenar Of The Import File Table Access C This view retreieves the student other Execution assessment information Export Attendance i t Award amp Punishment CDS Report Code Management
56. ossess Export Function access right User Procedures 1 Click Data Management Export on the left menu 2 Select a Task Fill in the search criteria 3 Click the Search button to search for a particular task Student S DAT05 06 Data Management gt Export Attendance Student Activities Query Query Name Query Award amp Punishme Table in Query 5 m dieu piis Last Modified Last Modified Special Assessme Date After Date Before DDAMM Y Y Y Y DDAMM Y Y Y Y Staff Deployment CDS HKAT HKEAA Task Task Name Task Description sff SPA Report Managemer Task Description Data Management FSC100 Staff Cost Query Maintena STTOOLO Staff Duty Profile Query Sharing STTO030 Staff Comespondence List Task Maintenar Table Access C 4 Check the boxes beside the Task Name to be exported 5 Click the Export button Post effects The extracted queries tasks can be used outside WebSAMS Notes Users can input Chinese character in the search criteria When exporting there will be a default filename If users select more than one task to export all the queries attached to the tasks will be exported However the task information task name and task description will not be exported Copyright 2007 Education Bureau The Government of the HKSAR Page 43 All Rights R
57. queries Query names cannot duplicate If the query is saved with the restricted tables that the user cannot access an error message will be shown 7 When query is being saved the system will check the syntax correctness and whether the syntax is supported by WebSAMS If the syntax is incorrect or not supported an error message will be shown Advance Query LL Function Description Users can create advance queries and execute them in this function LL Pre requisites Users have to possess Query Maintenance Function access right User Procedures 1 Click Data Management gt Query Maintenance on the left menu 2 Click the Add Advance button Assessment S DAT03 02 Data Management gt Query Maintenance gt Add Advance Query Special Assessmer Staff Deployment Add Advance Query CDS Query Name HKAT Query Description P s SQL Statement x gt SPA Report Managemen Data Management Query Sharing Task Maintenan Table Access C a Execution Tel save Export Import Report Extract for TES 3 Enter Query Name Query Description and SQL Statement Click the Save button Copyright 2007 Education Bureau The Government of the HKSAR Page 15 All Rights Reserved WebSAMS User Manual Version 1 3 Post effects N A Notes 1 Only the owner of the query can delete the query 2 Only users of the SYSTEM ADMIN and SCHOOL HEAD user g
58. r during the extraction for TESS LL Pre requisites 1 Errors occur when extracting files for TESS 2 Users have to possess Extract for TESS Error Log Function access right VU User Procedures Click Data Management gt Extract for TESS on the left menu Click on the Error Log tab page The errors occur during the extraction will be shown Click on the hyperlink of the Error Log File to view the details of the error qe To c To remove the error log files check the boxes beside the S N column and click the Delete button Attendance Student Activities Award amp Punishme S DAT09 03 Data Management gt Extract for TESS gt Error Log Assessment Special Assessme 3 r Exsadmin SC 1051503967781 log 3 04 28 12 26 11 0 pysadmin Staff Deployment gt CDS re festuser SC 1050995009234 log 53 04 22 15 03 35 0 gt HKAT re admin SC 1050733333359 og 3 04 19 14 22 20 0 pysadmin gt HKEAA re pysadmin SC 1050130760078 log 2003 04 12 14 59 22 0 pysadmin SPA rb Exsadmin SC 1050130751828 log 2003 04 12 14 59 14 0 bysadmin Report Manageme Data Management Copyright 2007 Education Bureau The Government of the HKSAR All Rights Reserved Page 52 WebSAMS User Manual Version 1 3 Post effects N A Notes The error log file contains the extraction parameters and every error encountered during
59. roups and the owner of the query can modify the query 3 lfaquery is modified the owner of the query will be changed to the user who makes the modification 4 The name of the user created queries cannot start with since the character designates the built in standard queries Query names cannot duplicate If the query is saved with the restricted tables that the user cannot access an error message will be shown 7 When query is being saved the system will check the syntax correctness and whether the syntax is supported by WebSAMS If the syntax is incorrect or not supported an error message will be shown 8 The length of a query cannot exceed 8 000 characters Basic Syntax SELECT select list FROM table expression ON search condition WHERE search condition GROUP BY column name HAVING search condition ORDER BY expression integer The clauses in the SELECT statement are as follows The SELECT clause specifies the columns users want to retrieve The FROM clause specifies the tables from which columns are pulled It is required in all queries that retrieve data from tables The ON clause specifies how tables in the FROM clause are to be joined It is used only for multi table queries and is not discussed here The WHERE clause specifies the rows in the tables users want to see The GROUP BY clause allows users to collect aggregate data The HAVING clause speci
60. t on the left menu 2 Click the Data Manual tab Report Managemer Data Management Query Maintena Query Sharing Select the Module Click the Preview button to view the softcopy of the data manual in a new window 5 Click the Download button to save the file Post effects N A Li Notes N A Copyright 2007 Education Bureau The Government of the HKSAR Page 48 All Rights Reserved WebSAMS User Manual Version 1 3 2 9 4 Query List Query LL Function Description Users can view the SQL statements LL Pre requisites Users have to possess Report Query List Function access right 8 User Procedures 1 Click Data Management Report on the left menu 2 Select a Query list Select a Query Fill in the search criteria 3 Click the Search button to search for a particular query Staff Deployment S DAT08 05 Data Management gt Report gt Query List gt CDS gt HKAT _ER Diagram Table Structure Data Manual 168 gt HKEAA Query Query Name Query Description SPA Table in Owner gt Report Managemer Query Last Modified Es Last Modified Data Management Date After Date Before Query Maintena DD MM Y YYY DDAMM Y Y Y Y Shari pc bis Task TaskNam Task Maintenan Task Table Access Description Execution Export Bottom Query Description EP ed j sysadmin 25 04 2003 Ex
61. t for SOP Copyright 2007 Education Bureau The Government of the HKSAR Page 3 All Rights Reserved WebSAMS User Manual Version 1 3 1 4 Interactions with other modules DAT Extract for TESS Extract for SOP Extract for SOP Extract for SOP Extract for TESS Extract for SOP Extract for SOP Extract for TESS Extract for SOP Maintain Task Information Maintain Query Maintain Advance Query Execute Query Task Import Export Query or Task Search Task Query Query Sharing Report Extract for TESS Table Access Control Extract for SOP Copyright 2007 Education Bureau The Government of the HKSAR All Rights Reserved Page 4 WebSAMS User Manual Version 1 3 All DAT Functions e Security SEC Get access right from all user groups that the user belongs to DATO2 Maintain Query amp Maintain Advance Query e Security SEC Additional access right checking Check whether the user belongs to the user groups SCHOOL HEAD or SYSTEM ADMIN since only members of these 2 built in groups can modify the queries owned by other users LL DATO4 Execute Query Task e Security SEC Additional access right checking Check the user s group memberships Only users of the built in user groups SCHOOL HEAD STAFF MANAGEMENT 1 STAFF MANAGEMENT 2 and STAFF MANAGEMENT 3 can execute query with the STF module tables And only users of the built in user groups SCHOOL HEAD
62. to 59 corresponding to the minute component of a datetime value Returns the remainder when one whole number is divided by another Returns a number from 1 to 12 corresponding to the month of the given date Returns the name of the month from a date Returns the current date and time This is the historical syntax for CURRENT TIMESTAMP To provide an abbreviated CASE expression by comparing expressions Generates numbers starting at 1 for each successive row in the results of the query NUMBER is primarily intended for use in select lists Returns the numeric value PI Calculates one number raised to the power of another Returns a number indicating the quarter of the year from the supplied date expression Converts a number from degree to radian Returns a random number in the interval 0 to 1 with an optional seed Concatenates a string a specified number of times Replaces all occurrences of a substring with another substring Returns the rightmost characters of a string Rounds the numeric expression to the specified integer expression amount of places after the decimal point Trims trailing blanks from a string Returns a number from 0 to 59 corresponding to the second component of the given datetime value Returns the sign of a number Returns the sine of a number Returns a number representing the sound of a string Returns a specified number of spaces Copyright 2007 Education Bureau The Governme
63. tract for TES 12 40 46 gt Timetabling sysadmin 20 05 2003 12 19 44 Code Management sysadmin 20 05 2008 Security 12 19 24 gt E Mail De 14357 Customization RIEVE DATA FROM TABLE TB_SCH_SCHCLASS snfong 110422003 ci 17 34 57 4 Click the Query Name link to view the SQL statements 1 Post effects N A LI Notes N A Copyright 2007 Education Bureau The Government of the HKSAR Page 49 All Rights Reserved WebSAMS User Manual Version 1 3 Task LL Function Description Users can view the SQL statements LJ Pre requisites Users have to possess Report Function Query List access right User Procedures 1 Click Data Management Report on the left menu 2 Select a Query list Select a Task Fill in the search criteria 3 Click the Search button to search for a particular task Staff Deployment S DAT08 06 Data Management gt Report gt Query List CDS gt HKEAA Query Query Name Query Description SPA Table in l Owner PEE Report Managemer Query Data Management af red prea Query Maintena DD MM Y Y Y Y DDAMM Y Y Y Y Query Sharing Task Task Name Task Maintenar Task Table Access C Description Execution Export Bottom Task Description Import Repot Extract for TESS fata Query Execution Log urent Public Examination Results Public Examination Results After Consolidation Budgeting Bookkeeping
64. ult in a new window c Click the Save Result button to save the execution result Copyright 2007 Education Bureau The Government of the HKSAR All Rights Reserved Page 24 WebSAMS User Manual Version 1 3 Student S DAT03 03 Data Management gt Query Maintenance Attendance Student Activities Modify Advance Query Query Name 213 Award amp Punishme Query Description Pa Assessment 7 Special Assessme SQL Statement select from tb sch school Staff Deployment cDS HKAT SPA Report Managemen Data Management Query Sharing Task Maintenai Table Access C Execution Export 3 Execute Query and Task Microsoft Internet Explorer S DAT04 03 Data Management gt Executing Query Name Query 1 Query Description Query 1Description SQL Statement SELECT TB_SCH_ANNOUNCE ANNOUNCEID TB_SCH_ANNOUNCE SUID FROM TB_SCH_ANNOUNCE TB_SCH_ASSESSTERM WHERE TB_SCH_ANNOUNCE ENDDATE gt TB_SCH_ANNOUNCE STARTDATE ORDER BY TB_SCH_ANNOUNCE STARTDATE ASC Output Format HTML Text File C Excel C Word C csv 71 X Post effects N A LL Notes N A Copyright 2007 Education Bureau The Government of the HKSAR Page 25 All Rights Reserved WebSAMS User Manual Version 1 3 2 3 Query Sharing LL Function Description The query owners should use this facility to share the access right of the
65. y For example a school with two WebSAMS Primary AM and PM FMP queries are available in Primary AM WebSAMS but not Primary PM WebSAMS Users have to login the Primary AM WebSAMS to run FMP queries under Data Management Copyright 2007 Education Bureau The Government of the HKSAR Page 40 All Rights Reserved WebSAMS User Manual Version 1 3 27 Export Query Task 2 7 1 Export Query LL Function Description Users can use this function to export the query statements in WebSAMS to an external file Afterwards they can modify the query statements or import the queries to another school LL Pre requisites 1 Queries should be prepared 2 Users have to possess Execution Function access right VU User Procedures 1 Click Data Management gt Export on the left menu 2 Select a Query Fill in the search criteria 3 Click the Search button to search for a particular query Student S DAT05 07 Data Management gt Export Attendance Student Activities Query Query Name Query Description m gt Award amp Punishme Table in Query KX Owner gt Assessment Last Modified Date Last Modified Date ta gt Special Assessmel after Before ODIMMIYYYY DDIMMIVYYY Staff Deployment gt CDS Task Task Name 1 Task Description gt E ER Search SPA Bottom Dat

USER MANUAL Data Management

Contents

Download Pdf Manuals

Related Search

Related Contents