public static void StoreUserTag(string username, string tag)
{
var existtag = RetrieveUserTag(username);
var tags = tag.Split(new string[] { ";", "," }, StringSplitOptions.RemoveEmptyEntries);
var filtertag = string.Empty;
foreach (var tg in tags)
{
if (!existtag.ToUpper().Contains(tg.Trim().ToUpper()))
{
filtertag = filtertag + tg.Trim().ToUpper() + ";";
}
}
if (!string.IsNullOrEmpty(filtertag))
{
filtertag = existtag + ";" + filtertag;
}
else
{
filtertag = existtag;
}
var sql = "delete from UserBlog where UserName='******' and APVal1 = '<APVal1>'";
sql = sql.Replace("<UserName>", username).Replace("<APVal1>", UserBlogContentType.TAG);
DBUtility.ExeLocalSqlNoRes(sql);
sql = "insert into UserBlog(UserName,APVal1,APVal2) "
+ " values('<UserName>','<APVal1>','<APVal2>')";
sql = sql.Replace("<UserName>", username).Replace("<APVal1>", UserBlogContentType.TAG).Replace("<APVal2>", filtertag);
DBUtility.ExeLocalSqlNoRes(sql);
}
public static void AddGroup(string grouptag, string groupmember)
{
var sql = "insert into UserGroupVM(GroupID,GroupTag,GroupMember,TimeStamp) values('<GroupID>','<GroupTag>','<GroupMember>','<TimeStamp>')";
sql = sql.Replace("<GroupID>", IssueViewModels.GetUniqKey()).Replace("<GroupTag>", grouptag).Replace("<GroupMember>", groupmember.ToUpper()).Replace("<TimeStamp>", DateTime.Now.ToString());
DBUtility.ExeLocalSqlNoRes(sql);
}
public void UpdateBlogDoc()
{
var sql = "update UserBlog set APVal4 = '<APVal4>' where APVal2 = '<APVal2>'";
sql = sql.Replace("<APVal2>", DocKey).Replace("<APVal4>", dbContent);
DBUtility.ExeLocalSqlNoRes(sql);
}
public static void RemoveBlogDoc(string DOCKey)
{
var sql = "delete from UserBlog where APVal2='<APVal2>'";
sql = sql.Replace("<APVal2>", DOCKey);
DBUtility.ExeLocalSqlNoRes(sql);
}
public static void AddUserDailyRank(string rankkey, string username, string ranktype, string summary, string backlink, int rank)
{
if (string.Compare(ranktype, UserRankType.BASE, true) == 0)
{
if (RetrieveRankItems(rankkey, UserRankType.BASE).Count > 0)
{
return;
}
}
if (string.Compare(ranktype, UserRankType.SPECIAL, true) == 0)
{
if (RetrieveRankItems(rankkey, UserRankType.SPECIAL).Count > 0)
{
return;
}
}
var sql = "insert into UserKPIVM(RankKey,UserName,RankType,Summary,BackLink,Rank,ADMIRERank,UpdateTime) "
+ " values('<RankKey>',N'<UserName>','<RankType>',N'<Summary>',N'<BackLink>',<Rank>,<ADMIRERank>,'<UpdateTime>')";
sql = sql.Replace("<RankKey>", rankkey).Replace("<UserName>", username).Replace("<RankType>", ranktype).Replace("<Summary>", summary)
.Replace("<BackLink>", backlink).Replace("<Rank>", rank.ToString()).Replace("<ADMIRERank>", "0")
.Replace("<UpdateTime>", DateTime.Now.ToString());
DBUtility.ExeLocalSqlNoRes(sql);
UpdateUserTotalRank(username, rank);
}
public static void DeleteGroup(string groupid)
{
var sql = "delete from UserGroupVM where GroupID = '<GroupID>'";
sql = sql.Replace("<GroupID>", groupid);
DBUtility.ExeLocalSqlNoRes(sql);
}
public static void UpdateSPComment(string ErrorKey, string CommentType, string Date, string dbcomment)
{
var csql = "update ErrorComments set Comment = '<Comment>' where ErrorKey='<ErrorKey>' and CommentType='<CommentType>' and CommentDate='<CommentDate>'";
csql = csql.Replace("<ErrorKey>", ErrorKey).Replace("<CommentType>", CommentType).Replace("<CommentDate>", Date).Replace("<Comment>", dbcomment);
DBUtility.ExeLocalSqlNoRes(csql);
}
public static void UpdateJO(string sn, string jo)
{
var sql = "update ProjectTestData set APPV1 = '<JO>' where ModuleSerialNum = '<ModuleSerialNum>'";
sql = sql.Replace("<ModuleSerialNum>", sn).Replace("<JO>", jo);
DBUtility.ExeLocalSqlNoRes(sql);
}
public static void RemoveMyLearn(string dockey, string username)
{
var sql = "delete from UserLearn where DOCKey=N'<DOCKey>' and UserName='******'";
sql = sql.Replace("<DOCKey>", dockey).Replace("<UserName>", username);
DBUtility.ExeLocalSqlNoRes(sql);
}
public void StoreEvent()
{
var sql = "insert into ProjectEvent(ProjectKey,EventKey,Event,UpdateDate) values('<ProjectKey>','<EventKey>','<Event>','<UpdateDate>')";
sql = sql.Replace("<ProjectKey>", ProjectKey).Replace("<EventKey>", EventKey).Replace("<Event>", dbEvent).Replace("<UpdateDate>", UpdateDate.ToString());
DBUtility.ExeLocalSqlNoRes(sql);
}
public static void CloseSPResultComment(string ErrorKey, string CommentType, string Date)
{
var csql = "update ErrorComments set APVal2 = 'CLOSED' where ErrorKey='<ErrorKey>' and CommentType='<CommentType>' and CommentDate='<CommentDate>'";
csql = csql.Replace("<ErrorKey>", ErrorKey).Replace("<CommentType>", CommentType).Replace("<CommentDate>", Date);
DBUtility.ExeLocalSqlNoRes(csql);
}
public static void ValidateUserWithDate(string username, DateTime date)
{
var sql = "update UserTable set Validated = 1 where UserName = N'<UserName>' and UpdateDate = '<UpdateDate>'";
sql = sql.Replace("<UpdateDate>", date.ToString()).Replace("<UserName>", username.ToUpper());
DBUtility.ExeLocalSqlNoRes(sql);
}
public static void ActiveUser(string username)
{
var sql = "update UserTable set Validated = 1 where UserName = N'<UserName>'";
sql = sql.Replace("<UserName>", username.ToUpper());
DBUtility.ExeLocalSqlNoRes(sql);
}
public static void UpdateAdmireRank(string rankkey, int admirerank)
{
var baserankitem = RetrieveRankItems(rankkey, UserRankType.BASE);
var specialitem = RetrieveRankItems(rankkey, UserRankType.SPECIAL);
var admirebaseitem = new List <UserKPIVM>();
admirebaseitem.AddRange(baserankitem);
admirebaseitem.AddRange(specialitem);
if (admirebaseitem.Count == 0)
{
return;
}
var admirerankitem = RetrieveRankItems(rankkey, UserRankType.ADMIRE);
if (admirerankitem.Count > 0)
{
return;
}
AddUserDailyRank(admirebaseitem[0].RankKey, admirebaseitem[0].UserName, UserRankType.ADMIRE, admirebaseitem[0].Summary, admirebaseitem[0].BackLink, admirerank);
var sql = "update UserKPIVM set ADMIRERank = <ADMIRERank> where RankKey='<RankKey>'";
sql = sql.Replace("<RankKey>", rankkey).Replace("<ADMIRERank>", admirerank.ToString());
DBUtility.ExeLocalSqlNoRes(sql);
UpdateUserTotalRank(admirebaseitem[0].UserName, admirerank);
}
public static void UpdateUserProject(string username, string projectkey)
{
var us = RetrieveUserProjectKeyStr(username);
if (string.IsNullOrEmpty(us))
{
var sql = "update UserTable set APVal1 = '<APVal1>' where UserName = N'<UserName>'";
sql = sql.Replace("<UserName>", username.ToUpper()).Replace("<APVal1>", projectkey);
DBUtility.ExeLocalSqlNoRes(sql);
}
else
{
var dict = new Dictionary <string, bool>();
var pjs = us.Split(new string[] { ";" }, StringSplitOptions.RemoveEmptyEntries);
foreach (var pj in pjs)
{
if (!dict.ContainsKey(pj))
{
dict.Add(pj, true);
}
}
if (!dict.ContainsKey(projectkey))
{
var sql = "update UserTable set APVal1 = '<APVal1>' where UserName = N'<UserName>'";
sql = sql.Replace("<UserName>", username.ToUpper()).Replace("<APVal1>", us + ";" + projectkey);
DBUtility.ExeLocalSqlNoRes(sql);
}//end if
}
}
public void RegistUser()
{
var sql = "insert into UserTable(UserName,PassWD,UpdateDate) values(N'<UserName>','<PassWD>','<UpdateDate>')";
sql = sql.Replace("<UserName>", Email.ToUpper()).Replace("<PassWD>", Password).Replace("<UpdateDate>", UpdateDate.ToString());
DBUtility.ExeLocalSqlNoRes(sql);
}
private static void UpdateEvent(string ekey, string Event)
{
var sql = "update ProjectEvent set Event = '<Event>',UpdateDate='<UpdateDate>' where EventKey = '<EventKey>'";
sql = sql.Replace("<EventKey>", ekey).Replace("<Event>", Event).Replace("<UpdateDate>", DateTime.Now.ToString());
DBUtility.ExeLocalSqlNoRes(sql);
}
public static void RemoveICare(string me, string other)
{
var sql = "delete from UserNet where ME='<ME>' and OTHER='<OTHER>'";
sql = sql.Replace("<ME>", me.ToUpper()).Replace("<OTHER>", other.ToUpper());
DBUtility.ExeLocalSqlNoRes(sql);
}
public static void StoreErrorAttachment(string errorkey, string attachmenturl)
{
var sql = "insert into PJErrorAttachment(ErrorKey,Attachment,UpdateTime) values('<ErrorKey>',N'<Attachment>','<UpdateTime>')";
sql = sql.Replace("<ErrorKey>", errorkey).Replace("<Attachment>", attachmenturl).Replace("<UpdateTime>", DateTime.Now.ToString());
DBUtility.ExeLocalSqlNoRes(sql);
}
public static void RestPwd(string username, string pwd)
{
var sql = "update UserTable set PassWD = '<PassWD>' where UserName = N'<UserName>'";
sql = sql.Replace("<UserName>", username.ToUpper()).Replace("<PassWD>", pwd);
DBUtility.ExeLocalSqlNoRes(sql);
}
public static string ShareDoc(string DOCPJK, string DOCType, string DOCKey, string DOCTag, string DOCCreator, string DOCDate, string BackLink)
{
var sql = "select APVal1 from ShareDoc where DOCPJK='<DOCPJK>' and DOCKey=N'<DOCKey>'";
sql = sql.Replace("<DOCPJK>", DOCPJK).Replace("<DOCKey>", DOCKey);
var dbret = DBUtility.ExeLocalSqlWithRes(sql, null);
if (dbret.Count > 0)
{
var docid1 = Convert.ToString(dbret[0][0]);
if (string.IsNullOrEmpty(docid1))
{
docid1 = UpdateDocID(DOCPJK, DOCKey);
}
return(docid1);
}
var docid = IssueViewModels.GetUniqKey();
sql = "insert into ShareDoc(DOCPJK,DOCType,DOCKey,DOCTag,DOCCreator,DOCDate,APVal1,BackLink) values('<DOCPJK>','<DOCType>',N'<DOCKey>','<DOCTag>','<DOCCreator>','<DOCDate>','<DOCID>','<BackLink>')";
sql = sql.Replace("<DOCPJK>", DOCPJK).Replace("<DOCType>", DOCType)
.Replace("<DOCKey>", DOCKey).Replace("<DOCTag>", DOCTag)
.Replace("<DOCCreator>", DOCCreator).Replace("<DOCDate>", DOCDate)
.Replace("<DOCID>", docid).Replace("<BackLink>", BackLink);
DBUtility.ExeLocalSqlNoRes(sql);
return(docid);
}
public static void SetUserBookTag(string UserName, string DocTag)
{
var sql = "Update UserTable set DocTag='<DocTag>' where UserName = '******'";
sql = sql.Replace("<UserName>", UserName).Replace("<DocTag>", DocTag);
DBUtility.ExeLocalSqlNoRes(sql);
}
public static void DeleteErrorComment(string ErrorKey, string CommentType, string Date)
{
var sql = "update ErrorComments set APVal1 = 'delete' where ErrorKey='<ErrorKey>' and CommentType='<CommentType>' and CommentDate='<CommentDate>'";
sql = sql.Replace("<ErrorKey>", ErrorKey).Replace("<CommentType>", CommentType).Replace("<CommentDate>", Date);
DBUtility.ExeLocalSqlNoRes(sql);
}
public static void StoreUserReviewed(string UserName, string pjkey, string Summary, string Link)
{
var sql = "insert into UserReviewedItems(UserName,PJKey,Summary,Link,UpdateTime) values('<UserName>','<PJKey>',N'<Summary>','<Link>','<UpdateTime>')";
sql = sql.Replace("<UserName>", UserName).Replace("<PJKey>", pjkey).Replace("<Summary>", Summary)
.Replace("<Link>", Link).Replace("<UpdateTime>", DateTime.Now.ToString());
DBUtility.ExeLocalSqlNoRes(sql);
}
//private void StoreErrorComment(string CommentDate)
//{
// if (!string.IsNullOrEmpty(Description))
// {
// var sql = "insert into ErrorComments(ErrorKey,Comment,Reporter,CommentDate) values('<ErrorKey>','<Comment>','<Reporter>','<CommentDate>')";
// sql = sql.Replace("<ErrorKey>", ErrorKey).Replace("<Comment>", dbDescription)
// .Replace("<Reporter>", Reporter).Replace("<CommentDate>", CommentDate);
// DBUtility.ExeLocalSqlNoRes(sql);
// }
//}
public static void StoreErrorComment(string ErrorKey, string dbComment, string CommentType, string Reporter, string CommentDate)
{
var sql = "insert into ErrorComments(ErrorKey,Comment,Reporter,CommentDate,CommentType) values('<ErrorKey>','<Comment>','<Reporter>','<CommentDate>','<CommentType>')";
sql = sql.Replace("<ErrorKey>", ErrorKey).Replace("<Comment>", dbComment)
.Replace("<Reporter>", Reporter).Replace("<CommentDate>", CommentDate).Replace("<CommentType>", CommentType);
DBUtility.ExeLocalSqlNoRes(sql);
}
public static void AddICare(string me, string other)
{
RemoveICare(me, other);
var sql = "insert into UserNet(ME,OTHER) values('<ME>','<OTHER>')";
sql = sql.Replace("<ME>", me.ToUpper()).Replace("<OTHER>", other.ToUpper());
DBUtility.ExeLocalSqlNoRes(sql);
}
public static void StoreLinkDBTIssue(string errorkey, string linktime, string originalissuekey, string sameasissuekey, string sameassn)
{
var sql = "insert into SameAsDBTVM(ErrorKey,LinkTime,OriginalIssueKey,SameAsIssueKey,SameAsSN,SameAsTime) values('<ErrorKey>','<LinkTime>','<OriginalIssueKey>','<SameAsIssueKey>','<SameAsSN>','<SameAsTime>')";
sql = sql.Replace("<ErrorKey>", errorkey).Replace("<LinkTime>", linktime).Replace("<OriginalIssueKey>", originalissuekey)
.Replace("<SameAsIssueKey>", sameasissuekey).Replace("<SameAsSN>", sameassn).Replace("<SameAsTime>", DateTime.Now.ToString());
DBUtility.ExeLocalSqlNoRes(sql);
}
public void UpdateShortDesc()
{
var sql = "update ProjectError set ShortDesc = '<ShortDesc>' where ErrorKey = '<ErrorKey>'";
sql = sql.Replace("<ErrorKey>", ErrorKey).Replace("<ShortDesc>", ShortDesc);
DBUtility.ExeLocalSqlNoRes(sql);
//StoreErrorComment(DateTime.Now.ToString());
}
public void StoreProjectTestData()
{
var isql = "insert into ProjectTestData(ProjectKey,DataID,ModuleSerialNum,WhichTest,ModuleType,ErrAbbr,TestTimeStamp,TestStation,PN,UpdateTime,APPV1) values('<ProjectKey>','<DataID>','<ModuleSerialNum>','<WhichTest>','<ModuleType>','<ErrAbbr>','<TestTimeStamp>','<TestStation>','<PN>','<UpdateTime>','<JO>')";
isql = isql.Replace("<ProjectKey>", ProjectKey).Replace("<DataID>", DataID).Replace("<ModuleSerialNum>", ModuleSerialNum)
.Replace("<WhichTest>", WhichTest).Replace("<ModuleType>", ModuleType).Replace("<ErrAbbr>", ErrAbbr)
.Replace("<TestTimeStamp>", TestTimeStamp.ToString()).Replace("<TestStation>", TestStation).Replace("<PN>", PN).Replace("<UpdateTime>", DateTime.Now.ToString()).Replace("<JO>", JO);
DBUtility.ExeLocalSqlNoRes(isql);
}
public static void DeleteAnalyse(string ErrorKey, string Date)
{
var sql = "update ErrorComments set APVal1 = 'delete' where ErrorKey='<ErrorKey>' and CommentDate > '<CommentDate1>' and CommentDate < '<CommentDate2>'";
sql = sql.Replace("<ErrorKey>", ErrorKey).Replace("<CommentDate1>", DateTime.Parse(Date).AddSeconds(-3).ToString())
.Replace("<CommentDate2>", DateTime.Parse(Date).AddSeconds(3).ToString());
DBUtility.ExeLocalSqlNoRes(sql);
}