private void Lbox2_SelectedIndexChanged(object sender, EventArgs e)
{
string tt = lbox2.Items[lbox2.SelectedIndex].ToString();
var r = c.DMClients.OrderBy(t => t.Name).AsEnumerable().Where(t => t.Name.Equals(tt)).FirstOrDefault();
dmc = new DMClient(r.id, c);
dmc.token = BbQuery.getToken(dmc.origUrl);
// authUrl_2.Replace("auth_2", "auth_sha");
var stem = dmc.origUrl.Substring(0, dmc.origUrl.LastIndexOf('/'));
authUrl_sha = stem + "/auth_sha.jsp";
dbtype = "oracle";
db = DMClient.getSqldt("SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'course_main' and COLUMN_NAME='pk1'", dmc);
string dbt = db.Rows[0][0].ToString();
/*pgsql
* oracle
* mssql
*/
if (dbt.Equals("bigint"))
{
dbtype = "pgsql";
}
else if (dbt.Equals("int"))
{
dbtype = "mssql";
}
}
private DataSet getSurveData(DMClient dmc, DateTime fromDate, DateTime toDate)
{
DataSet retValue = new DataSet();
DataTable dt = new DataTable();
StringBuilder sb = new StringBuilder();
sb.Append("SELECT distinct sv.pk1 as gmpk1, sv.name as rtitle ");
// ,ques.pk1 as qpk1 , ques.question_type
sb.Append(", c.pk1 as cpk1 , c.course_name as CourseName , c.course_id as CourseID , (select count(*) from deployment_response dr where dr.deployment_pk1=d.pk1 and dr.crsmain_pk1=c.pk1) as sent ");
sb.Append(", (select count(*) from deployment_response dr where dr.deployment_pk1=d.pk1 and dr.status='RE' and dr.crsmain_pk1=c.pk1) as scored ");
sb.Append("--@S,CONVERT(char(10), d.start_date, 126) as gmdate\n ");
sb.Append(" --@O,to_char( d.start_date, 'yyyy-mm-dd') as gmdate\n ");
sb.Append("FROM clp_sv_survey sv inner JOIN deployment d ON sv.instrument_key = d.collection_key JOIN deployment_response resp on d.pk1 = resp.deployment_pk1 JOIN clp_sv_question ques ON ques.clp_sv_survey_pk1 = sv.pk1 join course_main c on c.pk1 = resp.crsmain_pk1 where (select count(*) from deployment_response where deployment_pk1=d.pk1 and status='RE') >= 0 ");
sb.Append("and ( resp.received_date >= @from_date and resp.received_date <= @to_date) ");
sb.Append(" order by gmpk1,c.pk1");
string sql = sb.ToString();
sql = sql.Replace("@from_date", getSqlDate(fromDate));
sql = sql.Replace("@to_date", getSqlDate(toDate));
if (dbtype.Equals("oracle") || dbtype.Equals("pgsql"))
{
sql = sql.Replace("--@O", "");
}
else
{
sql = sql.Replace("--@S", "");
}
dt = DMClient.getSqldt(sql, dmc);
dt.TableName = "Surveys";
DataTable db = dt.Copy();
retValue.Tables.Add(db);
return(retValue);
}
private string getUser(string username)
{
Debug.WriteLine("AT getUser check with " + username);
string retvalue = "";
string sql = "select u.passwd from users u where u.user_id = '" + username + "'";
DataTable user = DMClient.getSqldt(sql, dmc);
if (user != null && user.Rows.Count > 0 && !user.TableName.Equals("error"))
{
retvalue = user.Rows[0]["passwd"].ToString();
}
Debug.WriteLine("AT getUser check with password " + retvalue);
return(retvalue); // Bb stored password
}
// _______________________ Make Access _______________________________
public static DataSet getDataByDocSetPk1(int svpk1, DMClient dmc)
{
DataSet retValue = new DataSet();
/*
* clp_sv_survey
* clp_sv_survey_deployment
* deployment
* deployment_response
* clp_sv_answer_choice
* clp_sv_question
* clp_sv_question_response
* clp_sv_question_sog
* clp_sv_subquestion
*
*/
/*
* clp_sv_survey
* pk1
* name
* description
* header_body
* dtcreated
* dtmodified
*/
string sql = "select pk1,name, description,header_body,dtcreated, dtmodified from clp_sv_survey where pk1 = " + svpk1.ToString();
DataTable dt = DMClient.getSqldt(sql, dmc);
for (int i = 0; i < dt.Rows.Count; i++)
{
dt.Rows[i]["header_body"] = StripTags(dt.Rows[i]["header_body"].ToString());
}
DataTable clp_sv_survey = dt.Copy();
clp_sv_survey.TableName = "clp_sv_survey";
retValue.Tables.Add(clp_sv_survey);
var survey_pk1 = clp_sv_survey.Select().ToList().Select(t => t.Field <string>("pk1")).ToArray <string>().First();
/*
* clp_sv_survey_deployment
* pk1
* clp_sv_survey_pk1
* deployment_pk1
*/
sql = "select pk1,clp_sv_survey_pk1,deployment_pk1 from clp_sv_survey_deployment where clp_sv_survey_pk1 = " + survey_pk1.ToString();
dt = DMClient.getSqldt(sql, dmc);
DataTable clp_sv_survey_deployment = dt.Copy();
clp_sv_survey_deployment.TableName = "clp_sv_survey_deployment";
retValue.Tables.Add(clp_sv_survey_deployment);
string[] deployment_pk1 = clp_sv_survey_deployment.Select().ToList().Select(t => t.Field <string>("deployment_pk1")).ToArray <string>();
string deps = String.Join(",", deployment_pk1);
/*
* deployment
* pk1
* name
* description
* status
* start_date
* end_date
* scheduled_start_date
* scheduled_end_date
* is_anonymous
* dtmodified
*/
sql = "select pk1,name, description, status, start_date, end_date, scheduled_start_date, scheduled_end_date,is_anonymous, dtmodified from deployment where pk1 in (" + deps + ")";
dt = DMClient.getSqldt(sql, dmc);
DataTable deployment = dt.Copy();
deployment.TableName = "deployment";
retValue.Tables.Add(deployment);
string[] pk1s = deployment.Select().ToList().Select(t => t.Field <string>("pk1")).ToArray <string>();
string dpk1s = String.Join(",", pk1s);
/*
* deployment_response
* pk1
* deployment_pk1
* email
* status
* received_date
* user_pk1
* crsmain_pk1
* crsmain_batch_uid
*/
/*select dr.pk1, dr.deployment_pk1, dr.email, dr.status, received_date, dr.user_pk1, dr.crsmain_pk1,c.course_name,c.course_id
* ,dr.crsmain_batch_uid ,u.lastname,u.firstname
* from deployment_response dr
* join course_main c on c.pk1 =dr.crsmain_pk1
* join course_users cu on cu.crsmain_pk1 = c.pk1 join users u on u.pk1 = cu.users_pk1 where cu.role ='P' and dr.deployment_pk1 in (544)*/
sql = "select dr.pk1, dr.deployment_pk1, dr.email, dr.status, received_date, dr.user_pk1 " +
", dr.crsmain_pk1,c.course_name,c.course_id " +
" ,dr.crsmain_batch_uid ,u.lastname,u.firstname " +
", (select count(*) from deployment_response dr1 where dr1.deployment_pk1 = dr.deployment_pk1 and dr1.crsmain_pk1 = c.pk1) + (select count(*) from clp_deploy_resp_unused du where du.deployment_pk1 = dr.deployment_pk1 and du.crsmain_pk1 = c.pk1) as sent " +
", (select count(*) from deployment_response dr1 where dr1.deployment_pk1 = dr.deployment_pk1 and dr1.status = 'RE' and dr1.crsmain_pk1 = c.pk1) as scored " +
"from deployment_response dr " +
" join course_main c on c.pk1 = dr.crsmain_pk1 " +
"join course_users cu on cu.crsmain_pk1 = c.pk1 " +
" join users u on u.pk1 = cu.users_pk1 " +
" where cu.role ='P' and dr.deployment_pk1 in (" + dpk1s + ")";
dt = DMClient.getSqldt(sql, dmc);
DataTable deployment_response = dt.Copy();
deployment_response.TableName = "deployment_response";
retValue.Tables.Add(deployment_response);
List <string> thedrpk1s = deployment_response.Select().ToList().Select(t => t.Field <string>("pk1")).ToList <string>();
sql = "select pk1, clp_sv_answer_choice_pk1, deployment_response_pk1, answer_text from clp_sv_question_response where deployment_response_pk1 in (@drpk1s)";
int block = 50;
DataTable clp_sv_question_response = new DataTable();
clp_sv_question_response.TableName = "clp_sv_question_response";
while (thedrpk1s.Count > 0)
{
block = (block > thedrpk1s.Count) ? thedrpk1s.Count : block;
string drs = String.Join(",", thedrpk1s.Take <string>(block).ToArray <string>());
var msql = sql;
msql = msql.Replace("@drpk1s", drs);
dt = DMClient.getSqldt(msql, dmc);
DataTable tmp = dt.Copy();
clp_sv_question_response.Merge(tmp);
thedrpk1s.RemoveRange(0, block);
}
retValue.Tables.Add(clp_sv_question_response);
/* clp_sv_question_response
* pk1
* clp_sv_answer_choice_pk1
* deployment_response_pk1
* answer_text
*/
/* clp_sv_question
* pk1
* clp_sv_survey_pk1
* qtext_body
* qtext_type
* display_order
* question_type
* required_ind
* multi_line_ind*/
sql = " select pk1, clp_sv_survey_pk1, qtext_body, qtext_type, display_order, question_type, required_ind, multi_line_ind from clp_sv_question where clp_sv_survey_pk1 = " + survey_pk1;
dt = DMClient.getSqldt(sql, dmc);
for (int i = 0; i < dt.Rows.Count; i++)
{
dt.Rows[i]["qtext_body"] = StripTags(dt.Rows[i]["qtext_body"].ToString());
}
DataTable clp_sv_question = dt.Copy();
clp_sv_question.TableName = "clp_sv_question";
retValue.Tables.Add(clp_sv_question);
// var clp_sv_question_pk1 = clp_sv_question.Select().ToList().Select(t => t.Field<string>("pk1")).ToArray<string>().First();
string[] theqdpk1s = clp_sv_question.Select().ToList().Select(t => t.Field <string>("pk1")).ToArray <string>();
string qdpk1s = String.Join(",", theqdpk1s);
/*
* clp_sv_subquestion
* pk1
* clp_sv_question_pk1
* display_order
* text
* label_type
* select_multiple_ind
* family_uid
*/
sql = "select pk1,clp_sv_question_pk1, display_order, text, label_type, select_multiple_ind, family_uid from clp_sv_subquestion where clp_sv_question_pk1 in (" + qdpk1s + ")";
dt = DMClient.getSqldt(sql, dmc);
DataTable clp_sv_subquestion = dt.Copy();
clp_sv_subquestion.TableName = "clp_sv_subquestion";
retValue.Tables.Add(clp_sv_subquestion);
string[] subs = clp_sv_subquestion.Select().ToList().Select(t => t.Field <string>("pk1")).ToArray <string>();
string subqpk1s = String.Join(",", subs);
/*
*
* clp_sv_answer_choice
* pk1
* clp_sv_subquestion
* display_order
* name
* points
* open_ended_ind
* open_ended_text
* not_applicable_ind
*/
sql = "select pk1, clp_sv_subquestion_pk1, display_order, name, points, open_ended_ind, open_ended_text, not_applicable_ind from clp_sv_answer_choice where clp_sv_subquestion_pk1 in (" + subqpk1s + ")";
dt = DMClient.getSqldt(sql, dmc);
DataTable clp_sv_answer_choice = dt.Copy();
clp_sv_answer_choice.TableName = "clp_sv_answer_choice";
retValue.Tables.Add(clp_sv_answer_choice);
/*
* clp_sv_question_sog pk1, clp_sv_question_pk1,clp_sog_pk1
*/
sql = "select pk1, clp_sv_question_pk1, clp_sog_pk1 from clp_sv_question_sog where clp_sv_question_pk1 in (" + qdpk1s + ")";
dt = DMClient.getSqldt(sql, dmc);
DataTable clp_sv_question_sog = dt.Copy();
clp_sv_question_sog.TableName = "clp_sv_question_sog";
retValue.Tables.Add(clp_sv_question_sog);
/*select cu.deployment_pk1 as deploy_pk1,cu.crsmain_pk1,cu.crsmain_batch_uid,cp.user_pk1,cp.given_name,cp.family_name,cp.email,cp.gender,cp.birthdate
* from clp_deploy_resp_unused cu
* join clp_person cp on cp.pk1 = cu.clp_person_pk1
* where cu.deployment_pk1 = 544 -- deployment_pk1*/
sql = "select cu.deployment_pk1 as deploy_pk1,cu.crsmain_pk1 as cpk1,cu.crsmain_batch_uid as crs_batch_uid,cp.user_pk1,cp.given_name as firstname " +
",cp.family_name as lastname,cp.email,cp.gender,cp.birthdate " +
" from clp_deploy_resp_unused cu " +
" join clp_person cp on cp.pk1 = cu.clp_person_pk1 " +
"where cu.deployment_pk1 in (" + dpk1s + ")";
dt = DMClient.getSqldt(sql, dmc);
DataTable clp_deploy_resp_unused = dt.Copy();
clp_deploy_resp_unused.TableName = "clp_deploy_resp_unused";
retValue.Tables.Add(clp_deploy_resp_unused);
return(retValue);
}
