/// <summary>
/// 新增相簿
/// </summary>
/// <param name="vt"></param>
/// <returns></returns>
public ActionResult Save(VtTags vt)
{
string[] rtn = new string[] { "", "" };
if (vt.Error)
{
return(PageSettion.VarTagsError(vt.ErrorMsg));
}
var tags = vt.Tags;
PageTableViewModel pv = new PageTableViewModel();
var name = Key.Dict(ref tags, "albumName");
var desc = Key.Dict(ref tags, "albumDesc");
var passwd = Key.Dict(ref tags, "passwd");
Album a = new Album();
if (name != "")
{
a.AlbumName = name;
a.AlbumDesc = desc;
a.PassWord = passwd;
a.ImgNo = "";
a.CreateDateTime = Key.Now;
a.ModifyDateTime = Key.Now;
db.Insert(a);
}
else
{
rtn[0] = AppConfig.ParamError;
}
return(new JsonNetResult(rtn));
}
/// <summary>
/// 每次查詢50筆
/// </summary>
/// <param name="vt"></param>
/// <returns></returns>
public ActionResult AudioList(VtTags vt)
{
string[] rtn = new string[] { "", "", "" };
if (vt.Error) {
return PageSettion.VarTagsError(vt.ErrorMsg);
}
var tags = vt.Tags;
var pv = new PageTableViewModel();
var sPic = Key.Dict(ref tags, "SPic").FixInt();
Sql = " SELECT ";
Sql += " * ";
Sql += " FROM ";
Sql += " ( ";
Sql += " SELECT *, ROW_NUMBER() OVER(ORDER BY ModifyDateTime Desc) as row FROM Audio ";
Sql += " WHERE 1= 1 ";
Sql += " ) a ";
Sql += " WHERE ";
Sql += $" a.row > {sPic} ";
Sql += $" and a.row <= {sPic + AudioLimit} ";
Sql += " Order By a.ModifyDateTime Desc ";
pv.AudioList = db.Query<Audio>(Sql).ToList();
pv.IsData = pv.AudioList.Count > 0;
ViewBag.IsEnd = sPic > 0 && pv.AudioList.Count < AudioLimit;
pv.ViewBag = ViewBag;
rtn[1] = page.View("Audio", pv);
rtn[2] = ViewBag.IsEnd ? "Y" : "";
return new JsonNetResult(rtn);
}
public ActionResult Index()
{
PageTableViewModel pv = new PageTableViewModel();
Sql = "SELECT * FROM Album ORDER BY ModifyDateTime Desc ";
pv.AlbumList = db.Query <Album>(Sql).ToList();
return(View(pv));
}
/// <summary>
/// 相簿明細
/// </summary>
/// <param name="vt"></param>
/// <returns></returns>
public ActionResult Detail(VtTags vt)
{
PageTableViewModel pv = new PageTableViewModel();
var albumNo = Key.Decrypt(Request.QueryString["k"].FixReq());
Sql = $" SELECT * FROM Album WHERE AlbumNo = '{albumNo}' ";
var albumInfo = db.Query <Album>(Sql).FirstOrDefault();
pv.Album = albumInfo;
return(View("Detail", pv));
}
/// <summary>
/// 批次讀取相片
/// </summary>
/// <param name="vt"></param>
/// <returns></returns>
public ActionResult Photos(VtTags vt)
{
string[] rtn = new string[] { "", "", "" };
if (vt.Error)
{
return(PageSettion.VarTagsError(vt.ErrorMsg));
}
var tags = vt.Tags;
var sPic = Key.Dict(ref tags, "SPic").FixInt();
var soFar = Key.Dict(ref tags, "SoFar").FixInt();
var fmDate = Key.Dict(ref tags, "FmDate");
var toDate = Key.Dict(ref tags, "ToDate");
var keyWord = Key.Dict(ref tags, "KeyWord");
string cond = "";
cond += fmDate == "" ? "" : $"AND OrgCreateDateTime >= '{fmDate.Replace("-", "")}' ";
cond += toDate == "" ? "" : $"AND OrgCreateDateTime <= '{toDate.Replace("-", "")}' ";
cond += keyWord == "" ? "" : $"AND FileName like'%{keyWord}%' ";
PageTableViewModel pv = new PageTableViewModel();
Sql = " SELECT ";
Sql += " * ";
Sql += " FROM ";
Sql += " ( ";
Sql += " SELECT *, ROW_NUMBER() OVER(ORDER BY ModifyDateTime Desc) as row FROM Photo ";
Sql += " WHERE 1=1 ";
Sql += cond == "" ? "" : cond;
Sql += " ) a ";
Sql += " WHERE ";
Sql += $" a.row > {sPic} ";
if (soFar != 0)
{
Sql += $" and a.row <= {soFar + PhotoLimit} ";
}
else
{
Sql += $" and a.row <= {sPic + PhotoLimit} ";
}
Sql += " Order By a.ModifyDateTime Desc ";
pv.PhotoList = db.Query <Photo>(Sql).ToList();
ViewBag.IsData = pv.PhotoList.Count() > 0;
ViewBag.TargetID = Key.Dict(ref tags, "TargetID");
ViewBag.IsEnd = sPic > 0 && pv.PhotoList.Count < PhotoLimit;
pv.ViewBag = ViewBag;
rtn[1] = page.View("Photo", pv);
rtn[2] = ViewBag.IsEnd ? "Y" : "";
return(new JsonNetResult(rtn));
}
/// <summary>
/// 加入相簿, 編修相簿名稱
/// </summary>
/// <param name="vt"></param>
/// <returns></returns>
public ActionResult AddImg(VtTags vt)
{
string[] rtn = new string[] { "", "" };
if (vt.Error)
{
return(PageSettion.VarTagsError(vt.ErrorMsg));
}
var tags = vt.Tags;
PageTableViewModel pv = new PageTableViewModel();
var imgNoList = Key.Dict(ref tags, "ImgNo");
var imgNo = imgNoList.Split(',').Select(s => Key.Decrypt(s)).ToArray().Join(",");
var albumNo = Key.Decrypt(Key.Dict(ref tags, "AlbumNo"));
var albumDesc = Key.Dict(ref tags, "AlbumDesc");
var albumName = Key.Dict(ref tags, "AlbumName");
Album a = db.Query <Album>($" SELECT * FROM Album WHERE AlbumNo={albumNo} ").FirstOrDefault();
if (a != null)
{
if (imgNo == "")
{
rtn[0] = "相片加入失敗 !";
}
else
{
a.ImgNo = $"{a.ImgNo},{imgNo}".Trim(',');
if (string.IsNullOrEmpty(a.BgImg))
{
var bgImgNo = imgNo.Split(',')[0].Replace("'", "").Trim(',');
Sql = $"SELECT CAST(ImgNo AS varchar) + FileExt FileName FROM Photo WHERE ImgNo = {bgImgNo} ";
a.BgImg = db.Query <string>(Sql).FirstOrDefault();
}
a.AlbumDesc = albumDesc;
a.AlbumName = albumName;
a.ModifyDateTime = Key.Now;
db.Update(a);
DelExportZip(a.AlbumNo, a.AlbumName);
rtn[1] = "相片加入成功 !";
}
}
else
{
rtn[0] = AppConfig.NoData;
}
return(new JsonNetResult(rtn));
}
/// <summary>
/// 儲存編輯相片
/// </summary>
/// <param name="vt"></param>
/// <returns></returns>
public ActionResult Save(VtTags vt)
{
string[] rtn = new string[] { "", "" };
if (vt.Error)
{
return(PageSettion.VarTagsError(vt.ErrorMsg));
}
var tags = vt.Tags;
PageTableViewModel pv = new PageTableViewModel();
var name = Key.Dict(ref tags, "Name");
var desc = Key.Dict(ref tags, "Desc");
var location = Key.Dict(ref tags, "Location");
var person = Key.Dict(ref tags, "Person");
var imgNo = Key.Decrypt(Key.Dict(ref tags, "ImgNo")).ToInt();
var albumNo = Key.Decrypt(Key.Dict(ref tags, "AlbumNo")).ToInt();
Sql = $"SELECT * FROM Photo WHERE ImgNo = '{imgNo}' ";
var imgInfo = db.Query <Photo>(Sql).FirstOrDefault();
if (imgInfo != null)
{
imgInfo.FileName = name;
imgInfo.FileDesc = desc;
imgInfo.Location = location;
imgInfo.Person = person;
imgInfo.ModifyDateTime = Key.Now;
db.Update(imgInfo);
if (albumNo != 0)
{
// 變更相片底圖
Sql = $"SELECT * FROM Album WHERE AlbumNo = '{albumNo}' ";
var albumInfo = db.Query <Album>(Sql).FirstOrDefault();
if (albumInfo != null)
{
albumInfo.BgImg = $"{imgNo}{imgInfo.FileExt}";
albumInfo.ModifyDateTime = Key.Now;
db.Update(albumInfo);
}
}
}
else
{
rtn[0] = AppConfig.NoData;
}
return(this.ToJsonNet(rtn));
}
/// <summary>
/// 編輯相片
/// </summary>
/// <param name="vt"></param>
/// <returns></returns>
public ActionResult Edit(VtTags vt)
{
string[] rtn = new string[] { "", "" };
if (vt.Error)
{
return(PageSettion.VarTagsError(vt.ErrorMsg));
}
var tags = vt.Tags;
PageTableViewModel pv = new PageTableViewModel();
//var k = Key.Decrypt(Request.QueryString["k"].FixReq());
//var dict = JsonConvert.DeserializeObject<Dictionary<string, string>>(k);
//var videoNo = Key.Dict(ref dict, "VideoNo");
//var backUrl = Key.Dict(ref dict, "BackUrl");
var videoNo = Key.Decrypt(Key.Dict(ref tags, "VideoNo"));
var backUrl = Key.Dict(ref tags, "BackUrl");
ViewBag.IsData = false;
if (videoNo != "")
{
Sql = " SELECT ";
Sql += " * ";
Sql += " FROM ";
Sql += " Video i ";
Sql += " WHERE ";
Sql += $" VideoNo = {videoNo} ";
Sql += " Order by i.ModifyDateTime Desc";
pv.VideoList = db.Query <VideoInfo>(Sql).ToList();
ViewBag.IsData = pv.VideoList.Count() > 0;
if (ViewBag.IsData)
{
Sql = " SELECT Distinct Person FROM Video WHERE Person != '' ";
Sql += " UNION ";
Sql += " SELECT Distinct Person FROM Photo WHERE Person != '' ";
ViewBag.PersonList = db.Query <string>(Sql).ToArray().Join(",");
}
}
pv.ViewBag = ViewBag;
rtn[1] = page.View("Edit", pv);
return(new JsonNetResult(rtn));
}
/// <summary>
/// 查詢
/// </summary>
/// <param name="vt"></param>
/// <returns></returns>
public ActionResult FuncInfo(VtTags vt)
{
PageTableViewModel pv = new PageTableViewModel();
string[] rtn = new string[] { "", "", "" };
if (vt.Error)
{
return(PageSettion.VarTagsError(vt.ErrorMsg));
}
var tags = vt.Tags;
var func = Key.Dict(ref tags, "Func");
var action = Key.Dict(ref tags, "Action");
var controller = Key.Dict(ref tags, "Controller");
var errMsg = Key.Dict(ref tags, "ErrMsg");
var fmDate = Key.Dict(ref tags, "SearchFmDate").Replace("/", "");
var toDate = Key.Dict(ref tags, "SearchToDate").Replace("/", "");
switch (func)
{
case "Query":
Sql = " SELECT ";
Sql += " * ";
Sql += " FROM ";
Sql += " ErrorLog ";
Sql += " WHERE 1=1";
Sql += fmDate == "" ? "" : $" LogDate >= '{fmDate}' ";
Sql += toDate == "" ? "" : $" AND LogDate <= '{toDate}' ";
Sql += errMsg == "" ? "" : $" AND Msg Liek '%{errMsg}%' ";
Sql += action == "" ? "" : $" AND Action = '{action}' ";
Sql += controller == "" ? "" : $" AND Controller like '%{controller}%' ";
Sql += " ORDER BY ";
Sql += " LogDate DESC ";
var errLog = db.Query <ErrorLog>(Sql).ToList();
pv.ErrLogList = errLog;
ViewBag.IsData = errLog.Count() > 0;
break;
}
ViewBag.TargetID = Key.Dict(ref tags, "TargetID");
pv.ViewBag = ViewBag;
rtn[1] = page.View(func, pv);
return(new JsonNetResult(rtn));
}
/// <summary>
/// 每次查詢50筆
/// </summary>
/// <param name="vt"></param>
/// <returns></returns>
public ActionResult VideoList(VtTags vt)
{
string[] rtn = new string[] { "", "", "" };
if (vt.Error)
{
return(PageSettion.VarTagsError(vt.ErrorMsg));
}
var tags = vt.Tags;
var pv = new PageTableViewModel();
var sPic = Key.Dict(ref tags, "SPic").FixInt();
var soFar = Key.Dict(ref tags, "SoFar").FixInt();
var fmDate = Key.Dict(ref tags, "FmDate");
var toDate = Key.Dict(ref tags, "ToDate");
var keyWord = Key.Dict(ref tags, "KeyWord");
var timeLineDate = Key.Dict(ref tags, "Date");
string cond = "";
cond += fmDate == "" ? "" : $"AND OrgCreateDateTime >= '{fmDate.Replace("-", "")}' ";
cond += toDate == "" ? "" : $"AND OrgCreateDateTime <= '{toDate.Replace("-", "")}' ";
cond += keyWord == "" ? "" : $"AND FileName like'%{keyWord}%' ";
Sql = " SELECT ";
Sql += " * ";
Sql += " FROM ";
Sql += " ( ";
Sql += " SELECT *, ROW_NUMBER() OVER(ORDER BY ModifyDateTime Desc) as row FROM Video ";
Sql += " WHERE 1= 1 ";
Sql += cond == "" ? "" : cond;
if (timeLineDate != "")
{
Sql += " AND CONVERT( VARCHAR(7), ";
Sql += " CASE OrgCreateDateTime ";
Sql += " WHEN '9999-12-31 00:00:00.000' ";
Sql += " THEN CreateDateTime ";
Sql += " ELSE OrgCreateDateTime END, 126 ) ";
Sql += $" = '{timeLineDate}' ";
}
Sql += " ) a ";
Sql += " WHERE ";
Sql += $" a.row > {sPic} ";
if (soFar != 0)
{
Sql += $" and a.row <= {soFar + VideoLimit} ";
}
else
{
Sql += $" and a.row <= {sPic + VideoLimit} ";
}
Sql += " Order By a.ModifyDateTime Desc ";
pv.VideoList = db.Query <VideoInfo>(Sql).ToList();
pv.IsData = pv.VideoList.Count > 0;
ViewBag.IsEnd = sPic > 0 && pv.VideoList.Count < VideoLimit;
pv.ViewBag = ViewBag;
rtn[1] = page.View("Video", pv);
rtn[2] = ViewBag.IsEnd ? "Y" : "";
return(new JsonNetResult(rtn));
}
/// <summary>
/// 批次讀取相片
/// </summary>
/// <param name="vt"></param>
/// <returns></returns>
public ActionResult Photo(VtTags vt)
{
string[] rtn = new string[] { "", "", "" };
if (vt.Error)
{
return(PageSettion.VarTagsError(vt.ErrorMsg));
}
var tags = vt.Tags;
var type = Key.Dict(ref tags, "Type");
var albumNo = Key.Decrypt(Key.Dict(ref tags, "AlbumNo"));
var sPic = Key.Dict(ref tags, "SPic").FixInt();
var soFar = Key.Dict(ref tags, "SoFar").FixInt();
var fmDate = Key.Dict(ref tags, "FmDate");
var toDate = Key.Dict(ref tags, "ToDate");
var keyWord = Key.Dict(ref tags, "KeyWord");
int cnt = 0;
string cond = "";
if (albumNo == "")
{
rtn[0] = "相簿名稱錯誤 !";
return(new JsonNetResult(rtn));
}
PageTableViewModel pv = new PageTableViewModel();
List <Photo> img = new List <Photo>();
Sql = $" SELECT TOP {PhotoLimit} * FROM Photo ORDER BY ModifyDateTime Desc";
ViewBag.IsData = db.Query <Photo>(Sql).ToList().Count() > 0;
Sql = $" SELECT AlbumNo, AlbumName, AlbumDesc, ImgNo FROM Album WHERE AlbumNo = '{albumNo}' ";
var albumInfo = db.Query <Album>(Sql).FirstOrDefault();
if (albumInfo != null)
{
switch (type)
{
case "tab_1":
cond = "";
break;
case "tab_2": // 相簿照片
cond = albumInfo.ImgNo == "" ? " AND 1=2" : $" And imgNo IN ({albumInfo.ImgNo}) ";
break;
case "tab_3": // 非相簿照片
cond = albumInfo.ImgNo == "" ? "" : $" And imgNo NOT IN ({albumInfo.ImgNo}) ";
break;
case "tab_4": // 未分類的相片
var imgNoList = db.Query <string>("SELECT ImgNo FROM Album WHERE ImgNo != '' ").ToArray().Join(",").Trim(',');
if (imgNoList != "")
{
cond = $" and imgNo NOT IN ({Regex.Replace(imgNoList, ",,", ",")}) ";
}
break;
default:
cond = "AND 1=2 ";
break;
}
cond += fmDate == "" ? "" : $"AND OrgCreateDateTime >= '{fmDate.Replace("-", "")}' ";
cond += toDate == "" ? "" : $"AND OrgCreateDateTime <= '{toDate.Replace("-", "")}' ";
cond += keyWord == "" ? "" : $"AND FileName like'%{keyWord}%' ";
Sql = " SELECT ";
Sql += " * ";
Sql += " FROM ";
Sql += " ( ";
Sql += " SELECT *, ROW_NUMBER() OVER(ORDER BY ModifyDateTime Desc) as row FROM Photo ";
Sql += " WHERE 1=1 ";
Sql += cond == "" ? "" : cond;
Sql += " ) a ";
Sql += " WHERE ";
Sql += $" a.row > {sPic} ";
if (soFar != 0)
{
Sql += $" and a.row <= {soFar + PhotoLimit} ";
}
else
{
Sql += $" and a.row <= {sPic + PhotoLimit} ";
}
Sql += " Order By a.ModifyDateTime Desc ";
pv.PhotoList = db.Query <Photo>(Sql).ToList();
if (sPic == 0)
{
Sql = " SELECT COUNT(1) FROM Photo ";
Sql += " WHERE 1=1 ";
Sql += cond == "" ? "" : cond;
cnt = db.Query <int>(Sql).FirstOrDefault().FixInt();
}
}
ViewBag.Type = type;
ViewBag.TargetID = Key.Dict(ref tags, "TargetID");
ViewBag.IsEnd = sPic > 0 && pv.PhotoList.Count < PhotoLimit;
pv.Album = albumInfo;
pv.ViewBag = ViewBag;
rtn[0] = ViewBag.IsData ? "" : "請先上傳圖片 !";
rtn[1] = page.View("Photo", pv);
var obj = new { End = ViewBag.IsEnd ? "Y" : "", Cond = Key.Encrypt(cond), Cnt = cnt };
rtn[2] = obj.ToJson();
return(new JsonNetResult(rtn));
}
