Beispiel #1
0
        public async Task<IHttpActionResult> PostForNewResearch()
        {
            if (!Request.Content.IsMimeMultipartContent())
            {
                return new System.Web.Http.Results.StatusCodeResult(HttpStatusCode.UnsupportedMediaType, Request);
            }

            string savepath = WebApiApplication.SERVERPATH + "download/research";
            var result = new MultipartFormDataStreamProvider(savepath);
            Research_detail data = new Research_detail();
            try
            {
                await Request.Content.ReadAsMultipartAsync(result);
                //READ JSON DATA PART
                JObject datareceive = JObject.Parse(result.FormData.GetValues(result.FormData.AllKeys[0])[0]);
                data.curri_id  = datareceive["curri_id"].ToString();
                data.name = datareceive["name"].ToString();
                data.year_publish = Convert.ToInt32(datareceive["year_publish"]);

                JArray tlist = (JArray)datareceive["researcher"];

                foreach (JObject item in tlist)
                {
                    Teacher_with_t_name t = new Teacher_with_t_name
                    {
                        teacher_id = Convert.ToInt32(item["teacher_id"])
                    };
                    data.researcher.Add(t);

                }

                //evidence_real_code evidence_name secret teacher_id
                //GET FILENAME WITH CHANGE FILENAME TO HAVE ITS EXTENSION
                MultipartFileData file = result.FileData[0];
                FileInfo fileInfo = new FileInfo(file.LocalFileName);
                string newfilename = string.Format("{0}.{1}", fileInfo.Name.Substring(9), file.Headers.ContentDisposition.FileName.Split('.').LastOrDefault().Split('\"').FirstOrDefault());
                data.file_name = "download/research/" + newfilename;
                File.Move(string.Format("{0}/{1}", savepath, fileInfo.Name), string.Format("{0}/{1}", savepath, newfilename));

                object resultfromdb = await datacontext.InsertNewResearchWithSelect(data);

                if (resultfromdb.GetType().ToString() != "System.String")
                    return Ok(resultfromdb);
                else
                {
                    if (File.Exists(string.Format("{0}/{1}", savepath, newfilename)))
                        File.Delete(string.Format("{0}/{1}", savepath, newfilename));
                    return InternalServerError(new Exception(resultfromdb.ToString()));
                }

            }
            catch (System.Exception e)
            {
                return InternalServerError(e);
            }
        }
Beispiel #2
0
        public async Task<object> UpdateResearchWithSelect(Research_detail rdata)
        {
            DBConnector d = new DBConnector();
            if (!d.SQLConnect())
                return WebApiApplication.CONNECTDBERRSTRING;
            List<Research_detail> result = new List<Research_detail>();
            string ifexistscond = string.Format("if exists (select * from {0} where {1} = {2}) ", FieldName.TABLE_NAME,
    FieldName.RESEARCH_ID, rdata.research_id);
            string temp1tablename = "#temp1";

            string createtabletemp1 = string.Format("create table {0} (" +
                                     "[row_num] INT IDENTITY(1, 1) NOT NULL," +
                                     "[{1}] {2} NOT NULL," +
                                     "PRIMARY KEY([row_num])) " +
                                     "ALTER TABLE {0} " +
                                     "ALTER COLUMN {1} {2} COLLATE DATABASE_DEFAULT ",
                                     temp1tablename, FieldName.FILE_NAME,DBFieldDataType.FILE_NAME_TYPE);

            string insertintotemp1 = string.Format("INSERT INTO {0} " +
                                     "select * from " +
                                     "(update {1} set {2} = '{3}', {4} = '{5}', {6} = {7} " +
                                     "OUTPUT deleted.{4} where {8} = {9}) as outputupdate ",
                                     temp1tablename, FieldName.TABLE_NAME, FieldName.NAME, rdata.name,
                                     FieldName.FILE_NAME, rdata.file_name, FieldName.YEAR_PUBLISH, rdata.year_publish,
                                     FieldName.RESEARCH_ID, rdata.research_id);

            string deletefromresearchowner = string.Format("DELETE FROM {0} where {1} = {2} ",
                Research_owner.FieldName.TABLE_NAME, Research_owner.FieldName.RESEARCH_ID, rdata.research_id);

            string insertintoresearchowner = string.Format("INSERT INTO {0} values ",
                Research_owner.FieldName.TABLE_NAME);

            foreach (Teacher_with_t_name t in rdata.researcher)
            {
                insertintoresearchowner += string.Format("({0},{1})", rdata.research_id, t.teacher_id);
                if (t != rdata.researcher.Last())
                    insertintoresearchowner += ",";
            }

            string selectcmd = string.Format("select temp1out.{6},r.*,{15}.{1},{15}.{2} from " +
                "(select {3}.{4},{3}.{5},{3}.{6}," +
                "{3}.{7}, {3}.{8},{9} from {3}, {10} where " +
                "{5} = '{11}' and {3}.{4} = {10}.{12}) as r,(select {6} from {14}) as temp1out,({0}) as {15} where r.{9} = {15}.{13} order by {7} ",
                oTeacher.getSelectTeacherByJoinCommand(), Teacher.FieldName.T_PRENAME, Teacher.FieldName.T_NAME,
                FieldName.TABLE_NAME, FieldName.RESEARCH_ID, FieldName.CURRI_ID, FieldName.FILE_NAME,
                FieldName.NAME, FieldName.YEAR_PUBLISH, Research_owner.FieldName.TEACHER_ID,
                Research_owner.FieldName.TABLE_NAME, rdata.curri_id, Research_owner.FieldName.RESEARCH_ID,
                Teacher.FieldName.TEACHER_ID,temp1tablename,Teacher.FieldName.ALIAS_NAME);
            
            d.iCommand.CommandText = string.Format("{0} BEGIN {1} {2} {3} {4} {5} END",ifexistscond, createtabletemp1,
                insertintotemp1, deletefromresearchowner, insertintoresearchowner, selectcmd);
            try
            {
                System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync();
                if (res.HasRows)
                {
                    DataTable data = new DataTable();
                    data.Load(res);

                    //get to-be delete file_name set in file_name property of main object for future use
                    file_name = data.Rows[0].ItemArray[0].ToString();

                    foreach (DataRow item in data.Rows)
                    {
                        int rid = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.RESEARCH_ID].Ordinal]);
                        if (result.FirstOrDefault(r => r.research_id == rid) == null)
                            result.Add(new Research_detail
                            {
                                name = item.ItemArray[data.Columns[FieldName.NAME].Ordinal].ToString(),
                                curri_id = item.ItemArray[data.Columns[FieldName.CURRI_ID].Ordinal].ToString(),
                                file_name = item.ItemArray[3].ToString(),
                                research_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.RESEARCH_ID].Ordinal]),
                                year_publish = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.YEAR_PUBLISH].Ordinal])
                            });

                        result.First(r => r.research_id == rid).researcher.Add(new Teacher_with_t_name
                        {
                            teacher_id = Convert.ToInt32(item.ItemArray[data.Columns[Teacher.FieldName.TEACHER_ID].Ordinal]),
                            t_name = NameManager.GatherPreName(item.ItemArray[data.Columns[Teacher.FieldName.T_PRENAME].Ordinal].ToString()) + item.ItemArray[data.Columns[Teacher.FieldName.T_NAME].Ordinal].ToString()
                        });
                    }
                    data.Dispose();
                }
                else
                {
                    //Reserved for return error string
                    res.Close();
                    return "ไม่พบข้อมูลงานวิจัยที่ต้องการแก้ไขในระบบ";
                }
                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;
        }
Beispiel #3
0
        public async Task<object> InsertNewResearchWithSelect(Research_detail rdata)
        {
            DBConnector d = new DBConnector();
            if (!d.SQLConnect())
                return WebApiApplication.CONNECTDBERRSTRING;
            List<Research_detail> result = new List<Research_detail>();
            string temp1tablename = "#temp1";
            string temp2tablename = "#temp2";
            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.RESEARCH_ID);
            string createtabletemp2 = string.Format("create table {0} (" +
                                      "[row_num] INT IDENTITY(1, 1) NOT NULL," +
                                      "[{1}] INT NULL," +
                                      "PRIMARY KEY ([row_num])) "
                                      , temp2tablename, Research_owner.FieldName.TEACHER_ID);

            string insertintotemp1 = string.Format("INSERT INTO {0} " +
                                     "select * from " +
                                     "(insert into {1} output inserted.{2} values " +
                                     "('{3}', '{4}', {5}, '{6}')) as outputinsert ",
                                     temp1tablename,FieldName.TABLE_NAME,FieldName.RESEARCH_ID,
                                     rdata.curri_id,rdata.name,rdata.year_publish,rdata.file_name);

            string insertintotemp2 = string.Format("INSERT INTO {0} VALUES (null)", temp2tablename);
                
                foreach(Teacher_with_t_name item in rdata.researcher)
                insertintotemp2 += string.Format(",({0})", item.teacher_id);

            string insertintoresowner = string.Format(" INSERT INTO {0} " +
                                        "select {1},{2} from {3},{4} where {2} is not null ",
                                        Research_owner.FieldName.TABLE_NAME, FieldName.RESEARCH_ID, Research_owner.FieldName.TEACHER_ID,
                                        temp1tablename, temp2tablename);

            curri_id = rdata.curri_id;
            string selectcmd = getSelectByCurriculumCommand();

            d.iCommand.CommandText = string.Format("BEGIN {0} {1} {2} {3} {4} {5} END", createtabletemp1, createtabletemp2,
                insertintotemp1, insertintotemp2, insertintoresowner, 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 rid = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.RESEARCH_ID].Ordinal]);
                        if (result.FirstOrDefault(r => r.research_id == rid) == null)
                            result.Add(new Research_detail
                            {
                                name = item.ItemArray[data.Columns[FieldName.NAME].Ordinal].ToString(),
                                curri_id = item.ItemArray[data.Columns[FieldName.CURRI_ID].Ordinal].ToString(),
                                file_name = item.ItemArray[data.Columns[FieldName.FILE_NAME].Ordinal].ToString(),
                                research_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.RESEARCH_ID].Ordinal]),
                                year_publish = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.YEAR_PUBLISH].Ordinal])
                            });

                        result.First(r => r.research_id == rid).researcher.Add(new Teacher_with_t_name
                        {
                            teacher_id = Convert.ToInt32(item.ItemArray[data.Columns[Teacher.FieldName.TEACHER_ID].Ordinal]),
                            t_name = NameManager.GatherPreName(item.ItemArray[data.Columns[Teacher.FieldName.T_PRENAME].Ordinal].ToString()) + item.ItemArray[data.Columns[Teacher.FieldName.T_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;
        }