public async Task <object> InsertOrUpdate(Others_evaluation_s_indic_name_list_with_file_name odata) { DBConnector d = new DBConnector(); if (!d.SQLConnect()) { return(WebApiApplication.CONNECTDBERRSTRING); } //Others_evaluation_s_indic_name_list_with_file_name result = new Others_evaluation_s_indic_name_list_with_file_name(); string temp5tablename = "#temp5"; string createtabletemp5 = string.Format("CREATE TABLE {0}(" + "[row_num] INT IDENTITY(1, 1) NOT NULL," + "[{1}] {2} NULL," + "PRIMARY KEY([row_num])) " + "ALTER TABLE {0} " + "ALTER COLUMN [{1}] {2} collate DATABASE_DEFAULT ", temp5tablename, Evidence.FieldName.FILE_NAME, DBFieldDataType.FILE_NAME_TYPE); string updatecmd = ""; if (odata.file_name == "") { foreach (Others_evaluation_sub_indicator_name o in odata.evaluation_detail) { updatecmd += string.Format("if not exists (select * from {0} where {1} = '{2}' and {3} = {4} and {5} = {6} and {7} = {8}) " + "BEGIN " + "INSERT INTO {0} values ({6},{8},'{9}',{10},'{11}','{12}','{13}','{14}',null,'{2}',{4}) " + "END " + "ELSE " + "BEGIN " + "UPDATE {0} set {15} = '{9}',{16} = {10},{17} = '{11}',{18} = '{12}',{19} = '{13}',{20} = '{14}' where {1} = '{2}' and {3} = {4} and {5} = {6} and {7} = {8} " + "END ", FieldName.TABLE_NAME, FieldName.CURRI_ID, o.curri_id, FieldName.ACA_YEAR, o.aca_year, FieldName.INDICATOR_NUM, o.indicator_num, FieldName.SUB_INDICATOR_NUM, o.sub_indicator_num, o.assessor_id, o.evaluation_score > 0 ? "'" + o.evaluation_score.ToString() + "'" : "null", /*11*/ o.strength, /*12*/ o.improve , o.date, o.time, FieldName.ASSESSOR_ID, FieldName.EVALUATION_SCORE, FieldName.STRENGTH, FieldName.IMPROVE, FieldName.DATE, FieldName.TIME); } } else { Others_evaluation_sub_indicator_name minobj = odata.evaluation_detail.Min(); updatecmd += string.Format("if not exists (select * from {0} where {1} = '{2}' and {3} = {4} and {5} = {6} and {7} = {8}) " + "BEGIN " + "INSERT INTO {0} values ({6},{8},'{9}',{10},'{11}','{12}','{13}','{14}','{22}','{2}',{4}) " + "END " + "ELSE " + "BEGIN " + "INSERT INTO #TEMP5 " + "select * from " + "(UPDATE {0} set {15} = '{9}',{16} = {10},{17} = '{11}',{18} = '{12}',{19} = '{13}',{20} = '{14}',{21} = '{22}' " + "output deleted.{21} " + "where {1} = '{2}' and {3} = {4} and {5} = {6} and {7} = {8}) as outputupdate " + "END ", FieldName.TABLE_NAME, FieldName.CURRI_ID, minobj.curri_id, FieldName.ACA_YEAR, minobj.aca_year, FieldName.INDICATOR_NUM, minobj.indicator_num, FieldName.SUB_INDICATOR_NUM, minobj.sub_indicator_num, minobj.assessor_id, minobj.evaluation_score > 0 ? "'" + minobj.evaluation_score.ToString() + "'" : "null", /*11*/ minobj.strength, /*12*/ minobj.improve, minobj.date, minobj.time, FieldName.ASSESSOR_ID, FieldName.EVALUATION_SCORE, FieldName.STRENGTH, FieldName.IMPROVE, FieldName.DATE, FieldName.TIME, Evidence.FieldName.FILE_NAME, odata.file_name); foreach (Others_evaluation_sub_indicator_name o in odata.evaluation_detail) { if (o != minobj) { updatecmd += string.Format("if not exists (select * from {0} where {1} = '{2}' and {3} = {4} and {5} = {6} and {7} = {8}) " + "BEGIN " + "INSERT INTO {0} values ({6},{8},'{9}',{10},'{11}','{12}','{13}','{14}',null,'{2}',{4}) " + "END " + "ELSE " + "BEGIN " + "UPDATE {0} set {15} = '{9}',{16} = {10},{17} = '{11}',{18} = '{12}',{19} = '{13}',{20} = '{14}' where {1} = '{2}' and {3} = {4} and {5} = {6} and {7} = {8} " + "END ", FieldName.TABLE_NAME, FieldName.CURRI_ID, o.curri_id, FieldName.ACA_YEAR, o.aca_year, FieldName.INDICATOR_NUM, o.indicator_num, FieldName.SUB_INDICATOR_NUM, o.sub_indicator_num, o.assessor_id, o.evaluation_score > 0 ? "'" + o.evaluation_score.ToString() + "'" : "null", /*11*/ o.strength, /*12*/ o.improve , o.date, o.time, FieldName.ASSESSOR_ID, FieldName.EVALUATION_SCORE, FieldName.STRENGTH, FieldName.IMPROVE, FieldName.DATE, FieldName.TIME); } } } string selectcmd = string.Format("select {1} from {0} ", temp5tablename, Evidence.FieldName.FILE_NAME); d.iCommand.CommandText = string.Format("BEGIN {0} {1} {2} END", createtabletemp5, updatecmd, selectcmd); try { object result = await d.iCommand.ExecuteScalarAsync(); if (result != null) { //File name to delete from #temp5 table strength = result.ToString(); } else { strength = ""; } } 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(null); }
public async Task <IHttpActionResult> Put() { if (!Request.Content.IsMimeMultipartContent()) { return(new System.Web.Http.Results.StatusCodeResult(HttpStatusCode.UnsupportedMediaType, Request)); } string savepath = WebApiApplication.SERVERPATH + "download/othersevaluation"; var result = new MultipartFormDataStreamProvider(savepath); try { await Request.Content.ReadAsMultipartAsync(result); //READ JSON DATA PART JObject datareceive = JObject.Parse(result.FormData.GetValues(result.FormData.AllKeys[0])[0]); Others_evaluation_s_indic_name_list_with_file_name listdata = new Others_evaluation_s_indic_name_list_with_file_name(); JArray olist = (JArray)datareceive["evaluation_detail"]; DateTime d = DateTime.Now; foreach (JObject obj in olist) { Others_evaluation_sub_indicator_name o = new Others_evaluation_sub_indicator_name(); o.aca_year = Convert.ToInt32(obj["aca_year"]); o.curri_id = obj["curri_id"].ToString(); o.others_evaluation_id = Convert.ToInt32(obj["others_evaluation_id"]); o.assessor_id = Convert.ToInt32(obj["assessor_id"]); o.date = d.GetDateTimeFormats(new System.Globalization.CultureInfo("en-US"))[5]; o.time = d.GetDateTimeFormats()[101]; if (obj["strength"] != null) { o.strength = obj["strength"].ToString(); } else { o.strength = ""; } if (obj["improve"] != null) { o.improve = obj["improve"].ToString(); } else { o.improve = ""; } o.indicator_num = Convert.ToInt32(obj["indicator_num"]); o.sub_indicator_num = Convert.ToInt32(obj["sub_indicator_num"]); o.evaluation_score = Convert.ToInt32(obj["evaluation_score"]); listdata.evaluation_detail.Add(o); } if (result.FileData.Count > 0) { 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()); listdata.file_name = "download/othersevaluation/" + newfilename; File.Move(string.Format("{0}/{1}", savepath, fileInfo.Name), string.Format("{0}/{1}", savepath, newfilename)); } else { listdata.file_name = ""; } object resultfromdb = await datacontext.InsertOrUpdate(listdata); if (resultfromdb == null) { if (result.FileData.Count > 0) { string delpath = WebApiApplication.SERVERPATH; //delete file that targeted (it has set via datacontext's [suggestion] property if (datacontext.strength != "") { if (File.Exists(string.Format("{0}{1}", delpath, datacontext.strength))) { File.Delete(string.Format("{0}{1}", delpath, datacontext.strength)); } } } return(Ok()); } else { return(InternalServerError(new Exception(resultfromdb.ToString()))); } } catch (Exception e) { return(InternalServerError(e)); } }
public async Task <object> SelectByIndicator() { DBConnector d = new DBConnector(); if (!d.SQLConnect()) { return(WebApiApplication.CONNECTDBERRSTRING); } Others_evaluation_s_indic_name_list_with_file_name result = new Others_evaluation_s_indic_name_list_with_file_name(); string temp5tablename = "#temp5"; string createtabletemp5 = string.Format("CREATE TABLE {0}(" + "[row_num] INT IDENTITY(1, 1) NOT NULL," + "[{1}] VARCHAR(2000) NULL," + "[{2}] INT NULL," + "[{3}] INT NULL," + "[{4}] INT NULL," + "[{5}] {16} NULL," + "[{6}] CHAR NULL," + "[{7}] VARCHAR(MAX) NULL," + "[{8}] VARCHAR(MAX) NULL," + "[{9}] DATE NULL," + "[{10}] TIME(0) NULL," + "[{11}] {15} NULL," + "[{12}] {14} NULL," + "[{13}] INT NULL," + "[{17}] VARCHAR(16) NULL," + "[{18}] VARCHAR(60) NULL," + "PRIMARY KEY([row_num])) " + "ALTER TABLE {0} " + "ALTER COLUMN [{1}] VARCHAR(2000) collate DATABASE_DEFAULT " + "ALTER TABLE {0} " + "ALTER COLUMN [{5}] {16} collate DATABASE_DEFAULT " + "ALTER TABLE {0} " + "ALTER COLUMN [{6}] CHAR collate DATABASE_DEFAULT " + "ALTER TABLE {0} " + "ALTER COLUMN [{7}] VARCHAR(MAX) collate DATABASE_DEFAULT " + "ALTER TABLE {0} " + "ALTER COLUMN [{8}] VARCHAR(MAX) collate DATABASE_DEFAULT " + "ALTER TABLE {0} " + "ALTER COLUMN [{11}] {15} collate DATABASE_DEFAULT " + "ALTER TABLE {0} " + "ALTER COLUMN [{12}] {14} collate DATABASE_DEFAULT " + "ALTER TABLE {0} " + "ALTER COLUMN [{17}] VARCHAR(16) collate DATABASE_DEFAULT " + "ALTER TABLE {0} " + "ALTER COLUMN [{18}] VARCHAR(60) collate DATABASE_DEFAULT ", temp5tablename, Sub_indicator.FieldName.SUB_INDICATOR_NAME, FieldName.OTHERS_EVALUATION_ID, FieldName.INDICATOR_NUM, FieldName.SUB_INDICATOR_NUM, FieldName.ASSESSOR_ID, FieldName.EVALUATION_SCORE, FieldName.STRENGTH, FieldName.IMPROVE, FieldName.DATE, FieldName.TIME, Evidence.FieldName.FILE_NAME, FieldName.CURRI_ID, FieldName.ACA_YEAR, DBFieldDataType.CURRI_ID_TYPE, DBFieldDataType.FILE_NAME_TYPE, DBFieldDataType.USERNAME_TYPE, Teacher.FieldName.T_PRENAME, Teacher.FieldName.T_NAME); string insertintotemp5_1 = string.Format("insert into {13} " + "select {2}, {0}.*,{17},{18} " + "from {0}, {1}, {14} " + "where {0}.{3} = {4} and " + "{0}.{5} = {1}.{6} and " + "{0}.{3} = {1}.{7} and " + "{15} = {16} and " + //user_id = assessor_id "{1}.{8} = " + "(select max(s1.{8}) from {1} as s1 where s1.{8} <= {9}) and " + "{0}.{10} = '{11}' and " + "{0}.{12} = {9} ", FieldName.TABLE_NAME, Sub_indicator.FieldName.TABLE_NAME, Sub_indicator.FieldName.SUB_INDICATOR_NAME, FieldName.INDICATOR_NUM, indicator_num, FieldName.SUB_INDICATOR_NUM, Sub_indicator.FieldName.SUB_INDICATOR_NUM, Sub_indicator.FieldName.INDICATOR_NUM, Sub_indicator.FieldName.ACA_YEAR, aca_year, FieldName.CURRI_ID, curri_id, FieldName.ACA_YEAR, temp5tablename, User_list.FieldName.TABLE_NAME, User_list.FieldName.USER_ID, FieldName.ASSESSOR_ID, Teacher.FieldName.T_PRENAME, Teacher.FieldName.T_NAME); string insertintotemp5_2 = string.Format("insert into {12} " + "select {1},0,{2},{3}," + "'','0','','',null,null,'','{4}',{6},null,null " + "from {0} where " + "{2} = {13} and {5} = " + "(select max(s1.{5}) from {0} as s1 where s1.{5} <= {6}) " + "and not exists(select * from {7} " + "where {8} = '{4}' and {9} = {6} and " + "{0}.{2} = {7}.{10} " + "and {0}.{3} = {7}.{11}) ", Sub_indicator.FieldName.TABLE_NAME, Sub_indicator.FieldName.SUB_INDICATOR_NAME, Sub_indicator.FieldName.INDICATOR_NUM, Sub_indicator.FieldName.SUB_INDICATOR_NUM, curri_id, Sub_indicator.FieldName.ACA_YEAR, aca_year, FieldName.TABLE_NAME, FieldName.CURRI_ID, FieldName.ACA_YEAR, FieldName.INDICATOR_NUM, FieldName.SUB_INDICATOR_NUM, temp5tablename, indicator_num); string insertintotemp5_3truecase = string.Format("insert into {0} " + "select {1}," + "{2}.*," + "{3}, {4} from {2}, {5}, {6} " + "where {2}.{7} = {8} " + "and {2}.{7} = {5}.{9} " + "and {2}.{10} = 0 " + "and {11} = {12} " + "and {5}.{13} = " + "(select max(s1.{13}) from {5} as s1 where s1.{13} <= {14}) " + "and {2}.{15} = '{16}' and {2}.{17} = {14} ", temp5tablename, Indicator.FieldName.INDICATOR_NAME_E, /*2 others*/ FieldName.TABLE_NAME, Teacher.FieldName.T_PRENAME, Teacher.FieldName.T_NAME, /*5 ind*/ Indicator.FieldName.TABLE_NAME, /*6 usr*/ User_list.FieldName.TABLE_NAME, FieldName.INDICATOR_NUM, indicator_num, Indicator.FieldName.INDICATOR_NUM, FieldName.SUB_INDICATOR_NUM, User_list.FieldName.USER_ID, FieldName.ASSESSOR_ID, Indicator.FieldName.ACA_YEAR, aca_year, FieldName.CURRI_ID, curri_id, FieldName.ACA_YEAR ); string insertintotemp5_3falsecase = string.Format("insert into {0} select " + "{1}," + "0,{2},0,'','0','','',null,null,'','{3}',{4},null,null " + "from {5} " + "where {6} = {2} " + "and {5}.{7} = (select max({7}) from {5} where {7} <= {4}) ", temp5tablename, Indicator.FieldName.INDICATOR_NAME_E, indicator_num, curri_id, aca_year, Indicator.FieldName.TABLE_NAME, Indicator.FieldName.INDICATOR_NUM, Indicator.FieldName.ACA_YEAR); string insertintotemp5_3 = string.Format("if exists (select * from {0} where {1} = 0 and {2} = {3} and {4} = '{5}' and {6} = {7}) " + " BEGIN " + insertintotemp5_3truecase + " END " + " else " + " BEGIN " + insertintotemp5_3falsecase + " END ", FieldName.TABLE_NAME, FieldName.SUB_INDICATOR_NUM, FieldName.INDICATOR_NUM, indicator_num, FieldName.CURRI_ID, curri_id, FieldName.ACA_YEAR, aca_year); string selectcmd = string.Format("select * from {0} order by {1} ", temp5tablename, FieldName.SUB_INDICATOR_NUM); string selectselfscorecmd = string.Format("select {0},{1} " + "from {2} " + "where {3} = '{4}' and {5} = {6} and {7} = {8} ", Self_evaluation.FieldName.SUB_INDICATOR_NUM, Self_evaluation.FieldName.EVALUATION_SCORE, Self_evaluation.FieldName.TABLE_NAME, Self_evaluation.FieldName.CURRI_ID, curri_id, Self_evaluation.FieldName.ACA_YEAR, aca_year, Self_evaluation.FieldName.INDICATOR_NUM, indicator_num); d.iCommand.CommandText = string.Format("BEGIN {0} {1} {2} {3} {4} {5} END", createtabletemp5, insertintotemp5_1, insertintotemp5_2, insertintotemp5_3, selectcmd, selectselfscorecmd); try { System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync(); do { if (res.HasRows) { DataTable data = new DataTable(); data.Load(res); if (data.Columns.Count > 2) { foreach (DataRow item in data.Rows) { if (Convert.ToInt32(item.ItemArray[data.Columns[FieldName.OTHERS_EVALUATION_ID].Ordinal]) != 0) { string h, m; DateTime timeofday = Convert.ToDateTime(item.ItemArray[data.Columns[FieldName.TIME].Ordinal].ToString(), System.Globalization.CultureInfo.CurrentCulture); h = timeofday.Hour.ToString(); m = timeofday.Minute.ToString(); result.evaluation_detail.Add(new Others_evaluation_sub_indicator_name { curri_id = item.ItemArray[data.Columns[FieldName.CURRI_ID].Ordinal].ToString(), others_evaluation_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.OTHERS_EVALUATION_ID].Ordinal]), aca_year = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.ACA_YEAR].Ordinal]), assessor_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.ASSESSOR_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(), date = Convert.ToDateTime(item.ItemArray[data.Columns[FieldName.DATE].Ordinal].ToString(), System.Globalization.CultureInfo.CurrentCulture).GetDateTimeFormats()[3], time = (timeofday.Hour > 9 ? "" : "0") + h + '.' + (timeofday.Minute > 9 ? "" : "0") + m, strength = item.ItemArray[data.Columns[FieldName.STRENGTH].Ordinal].ToString(), improve = item.ItemArray[data.Columns[FieldName.IMPROVE].Ordinal].ToString(), evaluation_score = item.ItemArray[data.Columns[FieldName.EVALUATION_SCORE].Ordinal].ToString() != "" ? Convert.ToInt32(item.ItemArray[data.Columns[FieldName.EVALUATION_SCORE].Ordinal]) : 0, indicator_num = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.INDICATOR_NUM].Ordinal]), sub_indicator_num = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.SUB_INDICATOR_NUM].Ordinal]), sub_indicator_name = item.ItemArray[data.Columns[Sub_indicator.FieldName.SUB_INDICATOR_NAME].Ordinal].ToString() }); if (item.ItemArray[data.Columns[Evidence.FieldName.FILE_NAME].Ordinal].ToString() != "") { result.file_name = item.ItemArray[data.Columns[Evidence.FieldName.FILE_NAME].Ordinal].ToString(); } } else { result.evaluation_detail.Add(new Others_evaluation_sub_indicator_name { curri_id = this.curri_id, others_evaluation_id = 0, aca_year = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.ACA_YEAR].Ordinal]), assessor_id = 31, date = "", time = "", strength = "", improve = "", evaluation_score = 0, indicator_num = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.INDICATOR_NUM].Ordinal]), sub_indicator_num = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.SUB_INDICATOR_NUM].Ordinal]), sub_indicator_name = item.ItemArray[data.Columns[Sub_indicator.FieldName.SUB_INDICATOR_NAME].Ordinal].ToString() }); } } } else { foreach (DataRow item in data.Rows) { sub_indicator_num = Convert.ToInt32(item.ItemArray[data.Columns[Self_evaluation.FieldName.SUB_INDICATOR_NUM].Ordinal]); result.evaluation_detail.First(t => t.sub_indicator_num == sub_indicator_num).self_score = Convert.ToInt32(item.ItemArray[data.Columns[Self_evaluation.FieldName.EVALUATION_SCORE].Ordinal]); } } data.Dispose(); } else if (!res.IsClosed) { if (!res.NextResult()) { break; } } } while (!res.IsClosed); 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); }