public async Task<object> UpdateMinutesWithSelect(Minutes_detail mdata)
{
DBConnector d = new DBConnector();
if (!d.SQLConnect())
return WebApiApplication.CONNECTDBERRSTRING;
List<Minutes_detail> result = new List<Minutes_detail>();
Minutes_detail dummyfordeleteminutes = new Minutes_detail();
string ifexistscond = string.Format("if exists (select * from {0} where {1} = {2}) ", FieldName.TABLE_NAME,
FieldName.MINUTES_ID, mdata.minutes_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_1;
//TOEDIT --UPDATE MINUTES TABLE (2 CASE WITH FILE_NAME UPDATES)
if (mdata.file_name != "")
{
insertintotemp1_1 = string.Format("INSERT INTO {0} " +
"select * from " +
"(update {1} set {2} = {3}, {4} = '{5}', {6} = '{7}',{8} = '{9}' " +
"OUTPUT deleted.{8} where {10} = {11}) as outputupdate ",
temp1tablename, FieldName.TABLE_NAME, FieldName.TEACHER_ID, mdata.teacher_id,
FieldName.DATE, mdata.date, FieldName.TOPIC_NAME, mdata.topic_name,
FieldName.FILE_NAME, mdata.file_name, FieldName.MINUTES_ID, mdata.minutes_id);
}
else
{
insertintotemp1_1 = string.Format("update {0} set {1} = {2}, {3} = '{4}', {5} = '{6}' " +
"where {7} = {8} ",
FieldName.TABLE_NAME, FieldName.TEACHER_ID, mdata.teacher_id,
FieldName.DATE, mdata.date, FieldName.TOPIC_NAME, mdata.topic_name,
FieldName.MINUTES_ID, mdata.minutes_id);
}
//OK UPDATE ATTENDEE
string deletefromminutesattendee = string.Format("DELETE FROM {0} where {1} = {2} ",
Minutes_attendee.FieldName.TABLE_NAME, Minutes_attendee.FieldName.MINUTES_ID, mdata.minutes_id);
string insertintominutesattendee = string.Format("INSERT INTO {0} values ",
Minutes_attendee.FieldName.TABLE_NAME);
foreach (Teacher_with_t_name t in mdata.attendee)
{
insertintominutesattendee += string.Format("({0},{1})", mdata.minutes_id, t.teacher_id);
if (t != mdata.attendee.Last())
insertintominutesattendee += ",";
}
string insertintominutespiccmd = string.Format("insert into {0} values ", Minutes_pic.FieldName.TABLE_NAME);
string deletefromminutespicturecmd = string.Format("delete from {0} output deleted.{1} where {2} = {3} ",
Minutes_pic.FieldName.TABLE_NAME, Minutes_pic.FieldName.FILE_NAME, Minutes_pic.FieldName.MINUTES_ID,
mdata.minutes_id);
string excludecond = "1=1 ";
int len = insertintominutespiccmd.Length;
foreach (Minutes_pic m in mdata.pictures)
{
if (m.minutes_id != 0)
{
//Gen delete cond
excludecond += string.Format("and {0} != '{1}' ", Minutes_pic.FieldName.FILE_NAME, m.file_name);
}
else
{
if (insertintominutespiccmd.Length <= len)
insertintominutespiccmd += string.Format("({0},'{1}')", mdata.minutes_id, m.file_name);
else
insertintominutespiccmd += string.Format(",({0},'{1}')", mdata.minutes_id, m.file_name);
}
}
if (insertintominutespiccmd.Length <= len)
insertintominutespiccmd = "";
deletefromminutespicturecmd += string.Format("and ({0}) ", excludecond);
string insertintotemp1_2 = string.Format("INSERT INTO {0} " +
"select * from " +
"({1}) " +
"as outputdelete ",
temp1tablename, deletefromminutespicturecmd);
curri_id = mdata.curri_id;
aca_year = mdata.aca_year;
string selectcmd = getSelectByCurriculumAcademicCommand(true);
d.iCommand.CommandText = string.Format("{0} BEGIN {1} {2} {3} {4} {5} {6} {7} END",ifexistscond, createtabletemp1,
insertintotemp1_1, deletefromminutesattendee, insertintominutesattendee,
insertintotemp1_2, insertintominutespiccmd, 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)
{
minutes_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.MINUTES_ID].Ordinal]);
date = item.ItemArray[data.Columns[FieldName.DATE].Ordinal].ToString();
//If date is not null (means result is row with attendee data)
if (date != "")
{
//Is it exists?
if (result.FirstOrDefault(m => m.minutes_id == minutes_id) == null)
{
result.Add(new Minutes_detail
{
aca_year = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.ACA_YEAR].Ordinal]),
teacher_id = item.ItemArray[data.Columns["t1_id"].Ordinal].ToString() != "" ? Convert.ToInt32(item.ItemArray[data.Columns["t1_id"].Ordinal]) : 0,
t_name = NameManager.GatherPreName(item.ItemArray[data.Columns["t1_prename"].Ordinal].ToString()) + item.ItemArray[data.Columns["t1_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(),
topic_name = item.ItemArray[data.Columns[FieldName.TOPIC_NAME].Ordinal].ToString(),
minutes_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.MINUTES_ID].Ordinal]),
date = Convert.ToDateTime(item.ItemArray[data.Columns[Self_evaluation.FieldName.DATE].Ordinal].ToString(), System.Globalization.CultureInfo.CurrentCulture).GetDateTimeFormats(new System.Globalization.CultureInfo("fr-FR"))[0]
});
result.FirstOrDefault(m => m.minutes_id == minutes_id).attendee.Add(new Teacher_with_t_name
{
teacher_id = Convert.ToInt32(item.ItemArray[data.Columns["t2_id"].Ordinal]),
t_name = NameManager.GatherPreName(item.ItemArray[data.Columns["t2_prename"].Ordinal].ToString()) + item.ItemArray[data.Columns["t2_name"].Ordinal].ToString()
});
}
else
{
result.FirstOrDefault(m => m.minutes_id == minutes_id).attendee.Add(new Teacher_with_t_name
{
teacher_id = Convert.ToInt32(item.ItemArray[data.Columns["t2_id"].Ordinal]),
t_name = NameManager.GatherPreName(item.ItemArray[data.Columns["t2_prename"].Ordinal].ToString()) + item.ItemArray[data.Columns["t2_name"].Ordinal].ToString()
});
}
}
else if(minutes_id != -1 && minutes_id != -2)
{
result.First(m => m.minutes_id == minutes_id).pictures.Add(new Minutes_pic
{
file_name = item.ItemArray[data.Columns[Minutes_pic.FieldName.FILE_NAME].Ordinal].ToString(),
minutes_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.MINUTES_ID].Ordinal])
});
}
//Add file_name to delete into dummy obj : minute_id == 1 || minutes_id == -2
else
{
dummyfordeleteminutes.pictures.Add(new Minutes_pic
{
file_name = item.ItemArray[data.Columns[Minutes_pic.FieldName.FILE_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();
}
result.Add(dummyfordeleteminutes);
return result;
}
public async Task<IHttpActionResult> PostForAddNewMinutes()
{
if (!Request.Content.IsMimeMultipartContent())
{
return new System.Web.Http.Results.StatusCodeResult(HttpStatusCode.UnsupportedMediaType, Request);
}
string savepathmain = WebApiApplication.SERVERPATH + "download/minutes";
string savepathsub = WebApiApplication.SERVERPATH + "myImages/minutes";
string temppath = WebApiApplication.SERVERPATH + "temp";
var result = new MultipartFormDataStreamProvider(temppath);
Minutes_detail data = new Minutes_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.aca_year = Convert.ToInt32(datareceive["aca_year"]);
data.topic_name = datareceive["topic_name"].ToString();
data.teacher_id = Convert.ToInt32(datareceive["teacher_id"]);
data.date = Convert.ToDateTime(datareceive["date"].ToString(), new System.Globalization.CultureInfo("fr-FR")).GetDateTimeFormats(new System.Globalization.CultureInfo("en-US"))[5];
JArray tlist = (JArray)datareceive["attendee"];
foreach (JObject item in tlist)
{
data.attendee.Add(new Teacher_with_t_name
{
teacher_id = Convert.ToInt32(item["teacher_id"])
});
}
//main minutes file
MultipartFileData file = result.FileData.Last();
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/minutes/" + newfilename;
File.Move(string.Format("{0}/{1}", temppath, fileInfo.Name), string.Format("{0}/{1}", savepathmain, newfilename));
////-----------------
tlist = (JArray)datareceive["pictures"];
if (result.FileData.Count > 1) {
int fileind = 0;
foreach (JObject item in tlist)
{
MultipartFileData file1 = result.FileData[fileind++];
FileInfo fileInfo1 = new FileInfo(file1.LocalFileName);
string newfilename1 = string.Format("{0}.{1}", fileInfo1.Name.Substring(9), file1.Headers.ContentDisposition.FileName.Split('.').LastOrDefault().Split('\"').FirstOrDefault());
data.pictures.Add(new Minutes_pic { file_name = "myImages/minutes/" + newfilename1 });
File.Move(string.Format("{0}/{1}", temppath, fileInfo1.Name), string.Format("{0}/{1}", savepathsub, newfilename1));
}
}
object resultfromdb = await datacontext.InsertNewMinutesWithSelect(data);
if (resultfromdb.GetType().ToString() != "System.String")
return Ok(resultfromdb);
else
return InternalServerError(new Exception(resultfromdb.ToString()));
}
catch (System.Exception e)
{
return InternalServerError(e);
}
}
public async Task<object> InsertNewMinutesWithSelect(Minutes_detail mdata)
{
DBConnector d = new DBConnector();
if (!d.SQLConnect())
return WebApiApplication.CONNECTDBERRSTRING;
List<Minutes_detail> result = new List<Minutes_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.MINUTES_ID);
string createtabletemp2 = string.Format("create table {0} (" +
"[row_num] INT IDENTITY(1, 1) NOT NULL," +
"[{1}] INT NULL," +
"PRIMARY KEY ([row_num])) "
, temp2tablename, Minutes_attendee.FieldName.TEACHER_ID);
string createtabletemp3 = 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 "
, temp3tablename, Minutes_pic.FieldName.FILE_NAME,
DBFieldDataType.FILE_NAME_TYPE);
string insertintotemp1 = string.Format("INSERT INTO {0} " +
"select * from " +
"(insert into {1}({2},{3},{4},{5},{6},{7}) output inserted.{8} values " +
"('{9}','{10}',{11},'{12}','{13}','{14}')) " +
"as outputinsert ",
temp1tablename, FieldName.TABLE_NAME, FieldName.TEACHER_ID, FieldName.CURRI_ID, FieldName.ACA_YEAR,
FieldName.DATE, FieldName.TOPIC_NAME, FieldName.FILE_NAME, FieldName.MINUTES_ID,
mdata.teacher_id, mdata.curri_id, mdata.aca_year, mdata.date, mdata.topic_name,
mdata.file_name);
string insertintotemp2 = string.Format("INSERT INTO {0} VALUES (null)", temp2tablename);
foreach (Teacher_with_t_name t in mdata.attendee)
{
insertintotemp2 += string.Format(",({0})", t.teacher_id);
}
string insertintotemp3 = string.Format("INSERT INTO {0} VALUES (null)", temp3tablename);
foreach (Minutes_pic m in mdata.pictures)
{
insertintotemp3 += string.Format(",('{0}')", m.file_name);
}
string insertintominutesattendee = string.Format(" INSERT INTO {0} " +
"select {1},{2} from {3},{4} where {2} is not null ",
Minutes_attendee.FieldName.TABLE_NAME, FieldName.MINUTES_ID, Minutes_attendee.FieldName.TEACHER_ID,
temp1tablename, temp2tablename);
string insertintominutespic = string.Format(" INSERT INTO {0} " +
"select {1},{2} from {3},{4} where {2} is not null ",
Minutes_pic.FieldName.TABLE_NAME, FieldName.MINUTES_ID, Minutes_pic.FieldName.FILE_NAME,
temp1tablename, temp3tablename);
curri_id = mdata.curri_id;
aca_year = mdata.aca_year;
string selectcmd = getSelectByCurriculumAcademicCommand(false);
d.iCommand.CommandText = string.Format("BEGIN {0} {1} {2} {3} {4} {5} {6} {7} {8} END", createtabletemp1, createtabletemp2, createtabletemp3,
insertintotemp1, insertintotemp2, insertintotemp3, insertintominutesattendee,
insertintominutespic, 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)
{
minutes_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.MINUTES_ID].Ordinal]);
date = item.ItemArray[data.Columns[FieldName.DATE].Ordinal].ToString();
//If date is not null (means result is row with attendee data)
if (date != "")
{
//Is it exists?
if (result.FirstOrDefault(m => m.minutes_id == minutes_id) == null)
{
result.Add(new Minutes_detail
{
aca_year = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.ACA_YEAR].Ordinal]),
teacher_id = item.ItemArray[data.Columns["t1_id"].Ordinal].ToString() != "" ? Convert.ToInt32(item.ItemArray[data.Columns["t1_id"].Ordinal]) : 0,
t_name = NameManager.GatherPreName(item.ItemArray[data.Columns["t1_prename"].Ordinal].ToString()) + item.ItemArray[data.Columns["t1_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(),
topic_name = item.ItemArray[data.Columns[FieldName.TOPIC_NAME].Ordinal].ToString(),
minutes_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.MINUTES_ID].Ordinal]),
date = Convert.ToDateTime(item.ItemArray[data.Columns[Self_evaluation.FieldName.DATE].Ordinal].ToString(), System.Globalization.CultureInfo.CurrentCulture).GetDateTimeFormats(new System.Globalization.CultureInfo("fr-FR"))[0]
});
result.FirstOrDefault(m => m.minutes_id == minutes_id).attendee.Add(new Teacher_with_t_name
{
teacher_id = Convert.ToInt32(item.ItemArray[data.Columns["t2_id"].Ordinal]),
t_name = NameManager.GatherPreName(item.ItemArray[data.Columns["t2_prename"].Ordinal].ToString()) + item.ItemArray[data.Columns["t2_name"].Ordinal].ToString()
});
}
else
{
result.FirstOrDefault(m => m.minutes_id == minutes_id).attendee.Add(new Teacher_with_t_name
{
teacher_id = Convert.ToInt32(item.ItemArray[data.Columns["t2_id"].Ordinal]),
t_name = NameManager.GatherPreName(item.ItemArray[data.Columns["t2_prename"].Ordinal].ToString()) + item.ItemArray[data.Columns["t2_name"].Ordinal].ToString()
});
}
}
else
{
result.First(m => m.minutes_id == minutes_id).pictures.Add( new Minutes_pic
{
file_name = item.ItemArray[data.Columns[Minutes_pic.FieldName.FILE_NAME].Ordinal].ToString(),
minutes_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.MINUTES_ID].Ordinal])
});
}
}
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;
}
public async Task<IHttpActionResult> PutForEditMinutes()
{
if (!Request.Content.IsMimeMultipartContent())
{
return new System.Web.Http.Results.StatusCodeResult(HttpStatusCode.UnsupportedMediaType, Request);
}
string savepathmain = WebApiApplication.SERVERPATH + "download/minutes";
string savepathsub = WebApiApplication.SERVERPATH + "myImages/minutes";
string temppath = WebApiApplication.SERVERPATH + "temp";
var result = new MultipartFormDataStreamProvider(temppath);
Minutes_detail data = new Minutes_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.aca_year = Convert.ToInt32(datareceive["aca_year"]);
data.topic_name = datareceive["topic_name"].ToString();
data.teacher_id = Convert.ToInt32(datareceive["teacher_id"]);
data.date = Convert.ToDateTime(datareceive["date"].ToString(), new System.Globalization.CultureInfo("fr-FR")).GetDateTimeFormats(new System.Globalization.CultureInfo("en-US"))[5];
data.minutes_id = Convert.ToInt32(datareceive["minutes_id"]);
JArray tlist = (JArray)datareceive["attendee"];
foreach (JObject item in tlist)
{
data.attendee.Add(new Teacher_with_t_name
{
teacher_id = Convert.ToInt32(item["teacher_id"])
});
}
int fileind;
if (datareceive["file_name"].ToString() != "")
{
//main minutes file (if exists)
MultipartFileData file = result.FileData.Last();
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/minutes/" + newfilename;
File.Move(string.Format("{0}/{1}", temppath, fileInfo.Name), string.Format("{0}/{1}", savepathmain, newfilename));
}
else {
data.file_name = "";
}
////---------------
fileind = 0;
tlist = (JArray)datareceive["pictures"];
foreach (JObject item in tlist)
{
if (Convert.ToInt32(item["minutes_id"]) == 0)
{
MultipartFileData file1 = result.FileData[fileind++];
FileInfo fileInfo1 = new FileInfo(file1.LocalFileName);
string newfilename1 = string.Format("{0}.{1}", fileInfo1.Name.Substring(9), file1.Headers.ContentDisposition.FileName.Split('.').LastOrDefault().Split('\"').FirstOrDefault());
data.pictures.Add(new Minutes_pic { minutes_id = 0, file_name = "myImages/minutes/" + newfilename1 });
File.Move(string.Format("{0}/{1}", temppath, fileInfo1.Name), string.Format("{0}/{1}", savepathsub, newfilename1));
}
else
{
data.pictures.Add(new Minutes_pic {
minutes_id = Convert.ToInt32(item["minutes_id"]),
file_name = item["file_name"].ToString()
});
}
}
object resultfromdb = await datacontext.UpdateMinutesWithSelect(data);
if (resultfromdb.GetType().ToString() != "System.String")
{
//string delpath = HttpContext.Current.Server.MapPath("~/");
string delpath = WebApiApplication.SERVERPATH;
List<Minutes_pic> picture_delete_list = ((List<Minutes_detail>)resultfromdb).Last().pictures;
//try catch foreach delete every file that targeted in strlist
foreach (Minutes_pic picture_to_delete in picture_delete_list)
{
if (File.Exists(string.Format("{0}{1}", delpath, picture_to_delete.file_name)))
File.Delete(string.Format("{0}{1}", delpath, picture_to_delete.file_name));
}
((List<Minutes_detail>)resultfromdb).Remove(((List<Minutes_detail>)resultfromdb).Last());
return Ok(resultfromdb);
}
else
return BadRequest(resultfromdb.ToString());
}
catch (Exception e)
{
return InternalServerError(e);
}
}
