private void btnExec_Click(object sender, EventArgs e) { dmc = (DMClient)this.Tag; string token = dmc.token; if (!tbSql.Text.Equals(String.Empty)) { if (token == null || token.Equals("")) { dmc.token = dmc.getToken(); } } tbResult.AppendText(DMClient.getSql(tbSql.Text, dmc)); // MessageBox.Show(dmc.name); }
private void btnGo_Click(object sender, EventArgs e) { dmc = (DMClient)this.Tag; string token = dmc.token; string tbSql = null; // sql goes here StringBuilder sb = new StringBuilder(); sb.Append("select c.pk1 as cpk1,c.course_name,c.course_id,c.dtmodified as cmod,cnt.pk1 as cntpk1, cnt.title,cnt.start_date,cnt.end_date, ca.pk1 as capk1 "); sb.Append(" ,(case when cnt.end_date is null then {d '" + ft + "'} else cnt.end_date end) as sort_date"); // sb.Append(" ,(select pk1 from course_main where pk1 = replace(c.course_id,'" + BbShadowCoursePrefix + "','')) as underlyingPk1 "); sb.Append(" ,(case when (select count(pk1) from course_main "); sb.Append(" where pk1 = replace(c.course_id,'" + BbShadowCoursePrefix + "','')) > 0 then "); sb.Append(" (select pk1 from course_main where pk1 = replace(c.course_id,'" + BbShadowCoursePrefix + "','')) "); sb.Append(" else c.pk1 end) as underlyingpk1 "); sb.Append(" ,ca.multiple_attempts_ind as m_ind "); sb.Append(" ,(select count(a.pk1) from attempt a inner join gradebook_grade gb on a.gradebook_grade_pk1 = gb.pk1 where gb.gradebook_main_pk1 = gm.pk1 and gb.course_users_pk1 = cu.pk1) as acount "); sb.Append(" ,(select min(a.status) from attempt a inner join gradebook_grade gb on a.gradebook_grade_pk1 = gb.pk1 where gb.gradebook_main_pk1 = gm.pk1 and gb.course_users_pk1 = cu.pk1 ) as status "); sb.Append(" ,cu.available_ind,cu.row_status "); sb.Append(" from course_main c inner join course_contents cnt on cnt.crsmain_pk1 = c.pk1 "); sb.Append(" inner join course_assessment ca on ca.pk1 = (select link_source_pk1 from link where course_contents_pk1 = cnt.pk1) "); sb.Append(" inner join course_users cu on cu.crsmain_pk1 = c.pk1 "); sb.Append(" inner join users u on u.pk1 = cu.users_pk1 "); sb.Append(" inner join gradebook_main gm on gm.qti_asi_data_pk1 = ca.qti_asi_data_pk1 and gm.crsmain_pk1 = c.pk1"); sb.Append(" where u.user_id = '" + tbUsername.Text.Trim() + "' and cnt.CNTHNDLR_HANDLE = 'resource/x-bb-asmt-survey-link' "); sb.Append(" and cnt.AVAILABLE_IND = 'Y' and cu.role = 'S' "); // sb.Append(" and cu.available_ind = 'Y' and cu.row_status = 0 "); sb.Append("and (c.course_id like '" + BbShadowCourseId + "%' escape '!') "); // for only shadow courses // sb.Append(" and ((cnt.end_date is null) or (not cnt.end_date is null and cnt.end_date >= {ts '" + mt + "'})) "); // sb.Append(" and ((cnt.start_date is null) or (not cnt.start_date is null and cnt.start_date <= {ts '" + mt + "'})) "); sb.Append(" order by sort_date,cnt.title,c.course_name,c.course_id"); tbSql = sb.ToString(); tbMemo.AppendText(tbSql + Environment.NewLine); if (!String.IsNullOrEmpty(tbSql)) { if (token == null || token.Equals("")) { dmc.token = dmc.getToken(); } } // QTIUtility.Logger.__SpecialLogger("SQL: " + dmc.name + Environment.NewLine + tbSql.Text + Environment.NewLine + "-- end --" + Environment.NewLine); tbMemo.AppendText(DMClient.getSql(tbSql, dmc)); }