public async Task<IHttpActionResult> PutForAddQuestionare(Questionare_set_detail_full data)
 {
     data.date = DateTime.Now.GetDateTimeFormats(new System.Globalization.CultureInfo("en-US"))[5];
     object result = await datacontext.InsertNewQuestionareWithSelect(data);
     if (result.GetType().ToString() != "System.String")
         return Ok(result);
     else
         return InternalServerError(new Exception(result.ToString()));
 }
Exemple #2
0
        public async Task<object> InsertNewQuestionareWithSelect(Questionare_set_detail_full qdata)
        {
            DBConnector d = new DBConnector();
            if (!d.SQLConnect())
                return WebApiApplication.CONNECTDBERRSTRING;
            List<Questionare_set_detail> result = new List<Questionare_set_detail>();
            string temp1tablename = "#temp1";
            string temp2tablename = "#temp2";
            string temp3tablename = "#temp3";
            string createtabletemp1 = string.Format("create table {0} (" +
                                      "[row_num] INT IDENTITY(1, 1) NOT NULL," +
                                      "[{1}] INT NOT NULL," +
                                      "PRIMARY KEY ([row_num])) ", temp1tablename, FieldName.QUESTIONARE_SET_ID);

            string createtabletemp2 = string.Format("create table {0} (" +
                                      "[row_num] INT IDENTITY(1, 1) NOT NULL," +
                                      "[{1}] VARCHAR(2000) NOT NULL," +
                                      "PRIMARY KEY ([row_num])) " +
                                      "ALTER TABLE {0} " +
                                      "ALTER COLUMN {1} VARCHAR(2000) COLLATE DATABASE_DEFAULT "
                                      , temp2tablename, Questionare_question_obj.FieldName.DETAIL);

             string createtabletemp3 = string.Format("create table {0} (" +
                                       "[row_num] INT IDENTITY(1, 1) NOT NULL," +
                                       "[{1}] INT NULL," +
                                       "PRIMARY KEY ([row_num])) "
                                       , temp3tablename, Questionare_privilege.FieldName.PRIVILEGE_TYPE_ID);


            string insertintotemp1 = string.Format("INSERT INTO {0} " +
                                     "select * from " +
                                     "(insert into {1} output inserted.{2} values " +
                                     "('{3}','{4}',{5},'{6}','{7}')) " +
                                     "as outputinsert ",
                                     temp1tablename, FieldName.TABLE_NAME, FieldName.QUESTIONARE_SET_ID,
                                     qdata.personnel_id, qdata.curri_id,qdata.aca_year, qdata.name,qdata.date);


            string insertintotemp2 = string.Format("INSERT INTO {0} VALUES (null)", temp2tablename);

            foreach (Questionare_question_obj q in qdata.my_questions)
            {
                    insertintotemp2 += string.Format(",('{0}')", q.detail);
            }

            string insertintotemp3 = string.Format("INSERT INTO {0} VALUES (null)", temp3tablename);

            foreach (User_type u in qdata.my_target)
            {
                insertintotemp3 += string.Format(",('{0}')", u.user_type_id);
            }

            string insertintoquestionareprivilege = string.Format(" INSERT INTO {0} " +
                                        "select {1},{2} from {3},{4} where {2} is not null ",
                                        Questionare_privilege.FieldName.TABLE_NAME, FieldName.QUESTIONARE_SET_ID, Questionare_privilege.FieldName.PRIVILEGE_TYPE_ID,
                                        temp1tablename, temp3tablename);

            string insertintoquestionarequestionobj = string.Format(" INSERT INTO {0} " +
                                        "select {1},{2} from {3},{4} where {2} is not null ",
                                        Questionare_question_obj.FieldName.TABLE_NAME, FieldName.QUESTIONARE_SET_ID, Questionare_question_obj.FieldName.DETAIL,
                                        temp1tablename, temp2tablename);

            curri_id = qdata.curri_id;
            aca_year = qdata.aca_year;

            string selectcmd = getSelectByCurriculumAcademicCommand();

            d.iCommand.CommandText = string.Format("BEGIN {0} {1} {2} {3} {4} {5} {6} {7} {8} END", createtabletemp1, createtabletemp2,createtabletemp3,
                insertintotemp1, insertintotemp2, insertintotemp3, insertintoquestionareprivilege,
                insertintoquestionarequestionobj, selectcmd);

            try
            {
                System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync();
                if (res.HasRows)
                {
                    DataTable data = new DataTable();
                    data.Load(res);
                    foreach (DataRow item in data.Rows)
                    {
                        int qid = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.QUESTIONARE_SET_ID].Ordinal]);
                        if (result.FirstOrDefault(t => t.questionare_set_id == qid) == null)
                        {
                            result.Add(new Questionare_set_detail
                            {
                                aca_year = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.ACA_YEAR].Ordinal]),
                                name = item.ItemArray[data.Columns[FieldName.NAME].Ordinal].ToString(),
                                personnel_id = item.ItemArray[data.Columns[FieldName.PERSONNEL_ID].Ordinal].ToString() != "" ? Convert.ToInt32(item.ItemArray[data.Columns[FieldName.PERSONNEL_ID].Ordinal]) : 0,
                                questionare_set_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.QUESTIONARE_SET_ID].Ordinal]),
                                date = Convert.ToDateTime(item.ItemArray[data.Columns[FieldName.DATE].Ordinal].ToString(), System.Globalization.CultureInfo.CurrentCulture).GetDateTimeFormats()[3],
                                curri_id = item.ItemArray[data.Columns[FieldName.CURRI_ID].Ordinal].ToString(),
                                t_name = item.ItemArray[data.Columns[User_list.FieldName.T_PRENAME].Ordinal].ToString() + item.ItemArray[data.Columns[Teacher.FieldName.T_NAME].Ordinal].ToString()
                            });
                        }
                        result.First(t => t.questionare_set_id == qid).target.Add(new User_type
                        {
                            user_type_id = Convert.ToInt32(item.ItemArray[data.Columns[Questionare_privilege.FieldName.PRIVILEGE_TYPE_ID].Ordinal]),
                            user_type = item.ItemArray[data.Columns[User_type.FieldName.USER_TYPE_NAME].Ordinal].ToString()
                        });
                    }
                    data.Dispose();
                }
                else
                {
                    //Reserved for return error string
                }
                res.Close();
            }
            catch (Exception ex)
            {
                //Handle error from sql execution
                return ex.Message;
            }
            finally
            {
                //Whether it success or not it must close connection in order to end block
                d.SQLDisconnect();
            }
            return result;

        }