public static List <CoTopicVM> RetrieveTopic(string topicid)
{
var ret = new List <CoTopicVM>();
var sql = "select topicid,subject,creator,status,createdate,topiccontent from CoTopicVM where topicid = '<topicid>'";
sql = sql.Replace("<topicid>", topicid);
var dbret = DBUtility.ExeLocalSqlWithRes(sql);
foreach (var line in dbret)
{
var imgresize = SeverHtmlDecode.ResizeImageFromHtml(Convert.ToString(line[5]));
var tempvm = new CoTopicVM(Convert.ToString(line[0]), Convert.ToString(line[1]), imgresize, Convert.ToString(line[2])
, Convert.ToString(line[3]), Convert.ToDateTime(line[4]).ToString("yyyy-MM-dd HH:mm:ss"));
tempvm.eventtimelist = RetrieveTopicDueDate(tempvm.topicid);
tempvm.ProjectWorkingList = TopicProject.RetrieveTopicPJ(tempvm.topicid, TopicPJStatus.Working);
tempvm.ProjectDoneList = TopicProject.RetrieveTopicPJ(tempvm.topicid, TopicPJStatus.Done);
tempvm.CommentList = TopicCommentVM.RetrieveComment(tempvm.topicid);
ret.Add(tempvm);
}
return(ret);
}
public static void UpdateTopicPJ(string topicid, List <string> pjlist, Controller ctrl)
{
var sql = "delete from auroratopicpj where topicid = '<topicid>'";
sql = sql.Replace("<topicid>", topicid);
DBUtility.ExeLocalSqlNoRes(sql);
foreach (var pj in pjlist)
{
sql = "insert into auroratopicpj(topicid,eventid,project,status,updatetime) values(@topicid,@eventid,@project,@status,@updatetime)";
var param = new Dictionary <string, string>();
param.Add("@topicid", topicid);
param.Add("@eventid", CoTopicVM.GetUniqKey());
param.Add("@project", pj);
param.Add("@status", TopicPJStatus.Working);
param.Add("@updatetime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
DBUtility.ExeLocalSqlNoRes(sql, param);
}
//var pjdict = CfgUtility.GetPJDict(ctrl);
//foreach (var pj in pjlist)
//{
// if (!pjdict.ContainsKey(pj))
// {
// sql = "delete from auroranewpj where project = '<project>'";
// sql = sql.Replace("<project>", pj);
// DBUtility.ExeLocalSqlNoRes(sql);
// sql = "insert into auroranewpj(project) values('<project>')";
// sql = sql.Replace("<project>", pj);
// DBUtility.ExeLocalSqlNoRes(sql);
// }
//}
}
public static List <CoTopicVM> RetrieveTopic4List(string username, string topicbelongtype, string status, string searchkey = null)
{
var topicreaddict = RetrieveTopicIsRead(username);
var ret = new List <CoTopicVM>();
var sql = "";
if (string.Compare(topicbelongtype, TopicBelongType.IAssign) == 0)
{
if (string.IsNullOrEmpty(searchkey))
{
sql = "select topicid,subject,creator,status,createdate from CoTopicVM where creator = '<creator>' and status = '<status>' and Removed <> 'TRUE' order by createdate desc";
}
else
{
sql = "select topicid,subject,creator,status,createdate from CoTopicVM where creator = '<creator>' and status = '<status>' and Removed <> 'TRUE' and subject like @subject order by createdate desc";
}
sql = sql.Replace("<creator>", username).Replace("<status>", status);
}
else
{
if (string.IsNullOrEmpty(searchkey))
{
sql = "select topicid,subject,creator,status,createdate from CoTopicVM where topicid in (select distinct topicid from auroratopicpeople where people = '<people>' and isowner <> 'TRUE') and status = '<status>' and Removed <> 'TRUE' order by createdate desc";
}
else
{
sql = "select topicid,subject,creator,status,createdate from CoTopicVM where topicid in (select distinct topicid from auroratopicpeople where people = '<people>' and isowner <> 'TRUE') and status = '<status>' and Removed <> 'TRUE' and subject like @subject order by createdate desc";
}
sql = sql.Replace("<people>", username).Replace("<status>", status);
}
var dbret = new List <List <object> >();
if (string.IsNullOrEmpty(searchkey))
{
dbret = DBUtility.ExeLocalSqlWithRes(sql);
}
else
{
var param = new Dictionary <string, string>();
param.Add("@subject", "%" + searchkey + "%");
dbret = DBUtility.ExeLocalSqlWithRes(sql, param);
}
foreach (var line in dbret)
{
var tempvm = new CoTopicVM(Convert.ToString(line[0]), Convert.ToString(line[1]), "", Convert.ToString(line[2])
, Convert.ToString(line[3]), Convert.ToDateTime(line[4]).ToString("yyyy-MM-dd HH:mm:ss"));
tempvm.eventtimelist = RetrieveTopicDueDate(tempvm.topicid);
tempvm.ProjectWorkingList = TopicProject.RetrieveTopicPJ(tempvm.topicid, TopicPJStatus.Working);
tempvm.ProjectDoneList = TopicProject.RetrieveTopicPJ(tempvm.topicid, TopicPJStatus.Done);
if (topicreaddict.ContainsKey(tempvm.topicid))
{
tempvm.isread = topicreaddict[tempvm.topicid];
}
ret.Add(tempvm);
}
return(ret);
}