0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for scientists, academics, teachers, and students in the field of chemistry

/** First, we create tables like moodle tables, but simpler.
    I am not dealing with course here, suppose the table #users already
    has only the students of a specific course.
**/

CREATE table #users
([id] INT, [idnumber] VARCHAR(100), [firstname] VARCHAR(100),
 [lastname] VARCHAR(100), [email] VARCHAR(100), [institution] VARCHAR(255));
 
/** Got all modules used in courses **/
CREATE table #course_modules
([id] INT, [course] INT, [module] INT, [instance] INT, [section] INT,
[completion] INT, [completionview] INT);

CREATE table #modules
([id] INT, [name] VARCHAR(50));

CREATE TABLE #course_sections
([id] INT, [course] INT, [section] INT, [name] VARCHAR(50), [sequence] VARCHAR(100));

CREATE TABLE #forum
([id] INT, [course] INT, [name] VARCHAR(50));

CREATE TABLE #checklist
([id] INT, [course] INT, [name] VARCHAR(50));

CREATE TABLE #assign
([id] INT, [course] INT, [name] VARCHAR(50));

CREATE TABLE #quiz
([id] INT, [course] INT, [name] VARCHAR(50));

CREATE TABLE #resource
([id] INT, [course] INT, [name] VARCHAR(50));

CREATE TABLE #questionnaire
([id] INT, [course] INT, [name] VARCHAR(50));

CREATE TABLE #page
([id] INT, [course] INT, [name] VARCHAR(50));

/** completion data **/
CREATE table #course_modules_completion
([id] INT , [coursemoduleid] INT, [userid] INT, [completionstate] INT, [viewed] INT)




/** Now let's populate these tables with some data **/
INSERT INTO #users
([id], [idnumber], [firstname], [lastname], [email], [institution])
VALUES
    (102345, 'idMaria', 'Maria', 'da Silva', '[email protected]','EscoladeMaria'),
    (103735, 'idJoao', 'João', 'de Deus','[email protected]','EscoladeJoao');    
    
INSERT INTO #modules /** Puttin those used in EducaSP courses **/
([id], [name])
VALUES
    (10, 'forum'),            /** Fórum **/
    (18, 'page'),             /** Vídeos **/
    (19, 'questionnaire'),    /** Pesquisa **/
    (20, 'quiz'),             /** Questionário **/
    (21, 'resource'),         /** Arquivos **/
    (24, 'url'),              /** URL **/
    (28, 'assign'),           /** Tarefas **/
    (38, 'checklist');        /** Presença **/

INSERT INTO #course_modules
([id], [course], [module], [instance], [section], [completion], [completionview])
VALUES
    (90752,1465,10,6849,21485,0,0),
    (93151,1465,38,5,21485,2,0),
    (93259,1465,28,15781,21486,1,0),
    (93308,1465,20,3925,21487,2,1),
    (93416,1465,18,4151,21486,2,1),
    (93417,1465,21,23560,21488,2,1),
    (93440,1465,19,1351,21489,2,0);

INSERT INTO #course_sections
([id], [course], [section], [name], [sequence])
VALUES
  (21485,1465,0,'','90752,93151'),
  (21486,1465,1,'Teste de tópico','93259,93416'),
  (21487,1465,2,'','93308'),
  (21488,1465,3,'','93417'),
  (21489,1465,4,'','93440');


/** Now let's put the modules **/
INSERT INTO #forum ([id], [course], [name])
VALUES
(6849,1465,'Avisos');

INSERT INTO #checklist
([id], [course], [name])
VALUES
(5,1465,'TestePresença');

INSERT INTO #assign
([id], [course], [name])
VALUES
(15781,1465,'Teste Tarefa');

INSERT INTO #quiz
([id], [course], [name])
VALUES
(3925,1465,'Tchubirabirom quiz');

INSERT INTO #page
([id], [course], [name])
VALUES
(4151,1465,'Teste de página');

INSERT INTO #resource
([id], [course], [name])
VALUES
(23560,1465,'Teste Recurso');

INSERT INTO #questionnaire
([id], [course], [name])
VALUES
(1351,1465,'Teste de Enquete');

INSERT INTO #course_modules_completion
([id] , [coursemoduleid] , [userid] , [completionstate], [viewed])
VALUES
(920307,93151,102345,0,0),
(920327,93151,103735,1,0),
(972378,93259,102345,1,0),
(980037,93416,102345,1,1),
(980253,93259,103735,1,0),
(982442,93417,102345,1,1),
(982488,93308,102345,3,1),
(982498,93308,103735,2,1);



/************************* NOW WE HAVE TABLES, LET'S ROLL!!! ***********************/
/* The first thing I need is a list of activities names and IDs. This is tricky
   because the data is totally decentralized in many tables.
*/
DECLARE @courseid AS INT = 1465;

SELECT sections.* FROM #course_sections AS sections WHERE course=@courseid ORDER BY section ASC

/* Get one row for each module */
SELECT id, course, section, name, value INTO #separated_modules
FROM #course_sections CROSS APPLY STRING_SPLIT(sequence, ',') WHERE course=@courseid


SELECT row_number() OVER (ORDER BY sm.section) ordem, sm.section, sm.value, cm.*
FROM #separated_modules AS sm
JOIN #course_modules AS cm ON cm.id=sm.value
WHERE cm.course=@courseid AND cm.completion != 0

/* Get the total number of modules in course */
DECLARE @num_modules AS INT;
SELECT @num_modules = COUNT(id) FROM #course_modules;

PRINT @num_modules

/* Now let's get an order, the moduleID and the name of each one */
DECLARE @loop_cnt INT = 0;

WHILE @loop_cnt < @num_modules
BEGIN

   PRINT 'Inside simulated FOR LOOP on TechOnTheNet.com';

END;

PRINT 'Done simulated FOR LOOP on TechOnTheNet.com';

view current version

edited oct 17 19

created oct 17 19 Mariana.Rodrigues