public async Task<IHttpActionResult> PostToAddGalleryWithPicture()
{
if (!Request.Content.IsMimeMultipartContent())
{
return new System.Web.Http.Results.StatusCodeResult(HttpStatusCode.UnsupportedMediaType, Request);
}
string savepath = WebApiApplication.SERVERPATH + "myImages/gallery";
var result = new MultipartFormDataStreamProvider(savepath);
Gallery_detail data = new Gallery_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.date_created = DateTime.Now.GetDateTimeFormats(new System.Globalization.CultureInfo("en-US"))[5];
data.aca_year = Convert.ToInt32(datareceive["aca_year"]);
data.personnel_id = Convert.ToInt32(datareceive["personnel_id"]);
JArray tlist = (JArray)datareceive["pictures"];
int fileind = 0;
foreach (JObject item in tlist)
{
Picture p = new Picture
{
caption = item["caption"].ToString()
};
MultipartFileData file = result.FileData[fileind++];
FileInfo fileInfo = new FileInfo(file.LocalFileName);
string newfilename = string.Format("{0}.{1}", fileInfo.Name.Substring(9), file.Headers.ContentDisposition.FileName.Split('.').LastOrDefault().Split('\"').FirstOrDefault());
p.file_name = "myImages/gallery/" + newfilename;
File.Move(string.Format("{0}/{1}", savepath, fileInfo.Name), string.Format("{0}/{1}", savepath, newfilename));
data.pictures.Add(p);
}
object resultfromdb = await datacontext.InsertNewGalleryWithSelect(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> UpdateGalleryWithSelect(Gallery_detail gdata)
{
DBConnector d = new DBConnector();
if (!d.SQLConnect())
return WebApiApplication.CONNECTDBERRSTRING;
List<Gallery_detail> result = new List<Gallery_detail>();
Gallery_detail dummyfordeletepictures = new Gallery_detail();
string temp1tablename = "#temp1";
string updatepicturecmd = "";
string ifexistscond = string.Format("if exists (select * from {0} where {1} = {2}) ", FieldName.TABLE_NAME,
FieldName.GALLERY_ID, gdata.gallery_id);
string insertintopicturecmd = string.Format("insert into {0} values ", Picture.FieldName.TABLE_NAME);
string deletefrompicturecmd = string.Format("delete from {0} output deleted.{1} where {2} = {3} ",
Picture.FieldName.TABLE_NAME, Picture.FieldName.FILE_NAME, Picture.FieldName.GALLERY_ID,
gdata.gallery_id);
string excludecond = "1=1 ";
int len = insertintopicturecmd.Length;
string createtabletemp1 = 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 "
, temp1tablename, Picture.FieldName.FILE_NAME,
DBFieldDataType.FILE_NAME_TYPE);
string updategallerycmd = string.Format("update {0} set {1} = '{2}', {3} = '{4}' where {5} = {6} ",
FieldName.TABLE_NAME, FieldName.PERSONNEL_ID, gdata.personnel_id, FieldName.NAME, gdata.name,
FieldName.GALLERY_ID, gdata.gallery_id);
foreach (Picture p in gdata.pictures)
{
if (p.gallery_id != 0)
{
updatepicturecmd += string.Format("update {0} set {1} = '{2}' where {3} = {4} and {5} = '{6}' ",
Picture.FieldName.TABLE_NAME, Picture.FieldName.CAPTION, p.caption, Picture.FieldName.GALLERY_ID,
gdata.gallery_id, Picture.FieldName.FILE_NAME, p.file_name);
//Gen delete cond
excludecond += string.Format("and {0} != '{1}' ", Picture.FieldName.FILE_NAME, p.file_name);
}
else
{
if (insertintopicturecmd.Length <= len)
insertintopicturecmd += string.Format("({0},'{1}','{2}')", gdata.gallery_id, p.file_name, p.caption);
else
insertintopicturecmd += string.Format(",({0},'{1}','{2}')", gdata.gallery_id, p.file_name, p.caption);
}
}
if (insertintopicturecmd.Length <= len)
insertintopicturecmd = "";
deletefrompicturecmd += string.Format("and ({0}) ", excludecond);
string insertintotemp1 = string.Format("INSERT INTO {0} " +
"select * from " +
"({1}) " +
"as outputdelete ",
temp1tablename, deletefrompicturecmd);
curri_id = gdata.curri_id;
aca_year = gdata.aca_year;
string selectcmd = getSelectByCurriculumAcademicCommand(true);
d.iCommand.CommandText = string.Format("{0} BEGIN {1} {2} {3} {4} {5} {6} END",ifexistscond, createtabletemp1,updategallerycmd,updatepicturecmd,
insertintotemp1,insertintopicturecmd,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)
{
gallery_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.GALLERY_ID].Ordinal]);
if (gallery_id != 0)
{
//If date is not null (means result is row with picture data)
if (result.FirstOrDefault(g => g.gallery_id == gallery_id) == null)
{
string real_t_prename;
if (item.ItemArray[data.Columns[User_type.FieldName.USER_TYPE_NAME].Ordinal].ToString() == "อาจารย์")
real_t_prename = NameManager.GatherPreName(item.ItemArray[data.Columns[Teacher.FieldName.T_PRENAME].Ordinal].ToString());
else
real_t_prename = item.ItemArray[data.Columns[Teacher.FieldName.T_PRENAME].Ordinal].ToString();
result.Add(new Gallery_detail
{
aca_year = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.ACA_YEAR].Ordinal]),
personnel_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.PERSONNEL_ID].Ordinal]),
t_name = real_t_prename + item.ItemArray[data.Columns[Teacher.FieldName.T_NAME].Ordinal].ToString(),
curri_id = item.ItemArray[data.Columns[FieldName.CURRI_ID].Ordinal].ToString(),
gallery_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.GALLERY_ID].Ordinal]),
date_created = Convert.ToDateTime(item.ItemArray[data.Columns[FieldName.DATE_CREATED].Ordinal].ToString(), System.Globalization.CultureInfo.CurrentCulture).GetDateTimeFormats()[3],
name = item.ItemArray[data.Columns[FieldName.NAME].Ordinal].ToString(),
pictures = new List<Picture>()
});
}
result.First(g => g.gallery_id == gallery_id).pictures.Add(new Picture
{
gallery_id = Convert.ToInt32(item.ItemArray[data.Columns[Picture.FieldName.GALLERY_ID].Ordinal]),
file_name = item.ItemArray[data.Columns[Picture.FieldName.FILE_NAME].Ordinal].ToString(),
caption = item.ItemArray[data.Columns[Picture.FieldName.CAPTION].Ordinal].ToString()
});
}
else
{
dummyfordeletepictures.pictures.Add(new Picture
{
file_name = item.ItemArray[data.Columns[Picture.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(dummyfordeletepictures);
return result;
}
public async Task<object> InsertNewGalleryWithSelect(Gallery_detail gdata)
{
DBConnector d = new DBConnector();
if (!d.SQLConnect())
return WebApiApplication.CONNECTDBERRSTRING;
List<Gallery_detail> result = new List<Gallery_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.GALLERY_ID);
string createtabletemp2 = string.Format("create table {0} (" +
"[row_num] INT IDENTITY(1, 1) NOT NULL," +
"[{1}] {3} NULL," +
"[{2}] VARCHAR(MAX) NULL," +
"PRIMARY KEY ([row_num])) " +
"ALTER TABLE {0} " +
"ALTER COLUMN {1} {3} COLLATE DATABASE_DEFAULT " +
"ALTER TABLE {0} " +
"ALTER COLUMN {2} VARCHAR(MAX) COLLATE DATABASE_DEFAULT "
, temp2tablename, Picture.FieldName.FILE_NAME,Picture.FieldName.CAPTION,
DBFieldDataType.FILE_NAME_TYPE);
string insertintotemp1 = string.Format("INSERT INTO {0} " +
"select * from " +
"(insert into {1}({2},{3},{4},{5},{6}) output inserted.{7} values " +
"('{8}','{9}','{10}','{11}',{12})) " +
"as outputinsert ",
temp1tablename, FieldName.TABLE_NAME, FieldName.PERSONNEL_ID, FieldName.NAME, FieldName.DATE_CREATED,
FieldName.CURRI_ID, FieldName.ACA_YEAR, FieldName.GALLERY_ID,
gdata.personnel_id, gdata.name, gdata.date_created, gdata.curri_id, gdata.aca_year);
string insertintotemp2 = string.Format("INSERT INTO {0} VALUES (null,null)", temp2tablename);
foreach (Picture p in gdata.pictures)
{
insertintotemp2 += string.Format(",('{0}','{1}')", p.file_name,p.caption);
}
string insertintopicture = string.Format(" INSERT INTO {0} " +
"select {1},{2},{3} from {4},{5} where {2} is not null ",
Picture.FieldName.TABLE_NAME, FieldName.GALLERY_ID, Picture.FieldName.FILE_NAME,
Picture.FieldName.CAPTION,
temp1tablename, temp2tablename);
curri_id = gdata.curri_id;
aca_year = gdata.aca_year;
string selectcmd = getSelectByCurriculumAcademicCommand(false);
d.iCommand.CommandText = string.Format("BEGIN {0} {1} {2} {3} {4} {5} END", createtabletemp1, createtabletemp2,
insertintotemp1, insertintotemp2,
insertintopicture, 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)
{
gallery_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.GALLERY_ID].Ordinal]);
if (result.FirstOrDefault(g => g.gallery_id == gallery_id) == null)
{
string real_t_prename;
if (item.ItemArray[data.Columns[User_type.FieldName.USER_TYPE_NAME].Ordinal].ToString() == "อาจารย์")
real_t_prename = NameManager.GatherPreName(item.ItemArray[data.Columns[Teacher.FieldName.T_PRENAME].Ordinal].ToString());
else
real_t_prename = item.ItemArray[data.Columns[Teacher.FieldName.T_PRENAME].Ordinal].ToString();
result.Add(new Gallery_detail
{
aca_year = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.ACA_YEAR].Ordinal]),
personnel_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.PERSONNEL_ID].Ordinal]),
t_name = real_t_prename + item.ItemArray[data.Columns[Teacher.FieldName.T_NAME].Ordinal].ToString(),
curri_id = item.ItemArray[data.Columns[FieldName.CURRI_ID].Ordinal].ToString(),
gallery_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.GALLERY_ID].Ordinal]),
date_created = Convert.ToDateTime(item.ItemArray[data.Columns[FieldName.DATE_CREATED].Ordinal].ToString(), System.Globalization.CultureInfo.CurrentCulture).GetDateTimeFormats()[3],
name = item.ItemArray[data.Columns[FieldName.NAME].Ordinal].ToString(),
pictures = new List<Picture>()
});
}
result.First(g => g.gallery_id == gallery_id).pictures.Add(new Picture
{
gallery_id = Convert.ToInt32(item.ItemArray[data.Columns[Picture.FieldName.GALLERY_ID].Ordinal]),
file_name = item.ItemArray[data.Columns[Picture.FieldName.FILE_NAME].Ordinal].ToString(),
caption = item.ItemArray[data.Columns[Picture.FieldName.CAPTION].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;
}