public static User Get(string username)
{
string sql = "select Id,UserName,Password,RealName,RoleType,Status,bugzillaid from Users where UserName=@UserName";
SqlParam p = new SqlParam();
p.AddParam("@UserName", username, SqlDbType.VarChar, 100);
DataTable dt = ProjectDB.GetDt(sql, p);
if (St.CheckDt(dt))
{
User o = new User();
o.Id = St.ToInt32(dt.Rows[0]["Id"], 0);
o.UserName = dt.Rows[0]["UserName"].ToString();
o.Password = dt.Rows[0]["Password"].ToString();
o.RealName = dt.Rows[0]["RealName"].ToString();
o.RoleType = St.ToInt32(dt.Rows[0]["RoleType"], 0);
o.Status = St.ToInt32(dt.Rows[0]["Status"], 0);
o.BugzillaId = St.ToInt32(dt.Rows[0]["bugzillaid"], 0);
return(o);
}
else
{
return(new User());
}
}
public static List <WeekReport> Get()
{
string sql = "SELECT [Id],[ProjectName],[WorkContent],[WorkType],[PlanDateStart],[PlanDateEnd],[PlanDay],[RealDay],[RealWorkContent],[Status],[UserId],[Remark],[AddTime] FROM [dbo].[WeekReports]";
DataTable dt = ProjectDB.GetDt(sql);
return(GetList(dt));
}
public static List <Dic> Get()
{
List <Dic> list = UnicornCache.Get <List <Dic> >(CacheKey.Dic);
if (list == null)
{
list = new List <Dic>();
string sql = "select Id,Code,Name,Type,Remark,AddTime from Dics";
DataTable dt = ProjectDB.GetDt(sql);
foreach (DataRow row in dt.Rows)
{
Dic o = new Dic();
o.Id = St.ToInt32(row["Id"], 0);
o.Code = row["Code"].ToString();
o.Name = row["Name"].ToString();
o.Type = St.ToInt32(row["Type"].ToString(), 0);
o.Remark = row["Remark"].ToString();
o.AddTime = St.ToDateTime(row["AddTime"].ToString());
list.Add(o);
}
UnicornCache.Add(CacheKey.Dic, list);
}
return(list);
}
public static List <Server> Get()
{
List <Server> list = new List <Server>();
string sql = "SELECT [Id] ,[ServerName],[UserName] ,[Password],[IISVersion],[SqlVersion],[ProjectId],[AddTime],[OSName],[ServerType] FROM [dbo].[Servers]";
DataTable dt = ProjectDB.GetDt(sql);
List <Project> lp = ProjectRule.Get();
foreach (DataRow row in dt.Rows)
{
Server o = new Server();
o.Id = St.ToInt32(row["Id"], 0);
o.ServerName = row["ServerName"].ToString();
o.UserName = row["UserName"].ToString();
o.Password = row["Password"].ToString();
o.IISVersion = row["IISVersion"].ToString();
o.SqlVersion = row["SqlVersion"].ToString();
o.ProjectId = row["ProjectId"].ToString();
o.ProjectName = o.ProjectId == ""?"":string.Join(",", lp.Where(a => Array.IndexOf(o.ProjectId.Split(','), a.Id.ToString()) >= 0).Select(a => a.Name).ToArray());
o.AddTime = St.ToDateTime(row["AddTime"].ToString());
o.OSName = row["OSName"].ToString();
o.ServerType = row["ServerType"].ToString();
list.Add(o);
}
return(list);
}
public static List <User> Get()
{
List <User> list = UnicornCache.Get <List <User> >(CacheKey.User);
if (list == null)
{
list = new List <User>();
string sql = "select Id,UserName,Password,RealName,RoleType,Status,LeaveTime,bugzillaid from Users";
DataTable dt = ProjectDB.GetDt(sql);
foreach (DataRow row in dt.Rows)
{
User o = new User();
o.Id = St.ToInt32(row["Id"], 0);
o.UserName = row["UserName"].ToString();
o.Password = row["Password"].ToString();
o.RealName = row["RealName"].ToString();
o.RoleType = St.ToInt32(row["RoleType"], 0);
o.Status = St.ToInt32(row["Status"], 0);
o.LeaveTime = St.ToDateTime(row["LeaveTime"].ToString());
o.BugzillaId = St.ToInt32(row["bugzillaid"], 0);
list.Add(o);
}
UnicornCache.Add(CacheKey.User, list);
}
return(list);
}
public static void Delete(int id)
{
string sql = @"Delete from Attentions where Id=@id";
SqlParam p = new SqlParam();
p.AddParam("@id", id, SqlDbType.Int, 0);
ProjectDB.SqlExecute(sql, p);
}
public static void Update(int id)
{
string sql = @"update Users set [Status]=[Status]%2+1 where Id=@id";
SqlParam p = new SqlParam();
p.AddParam("@id", id, SqlDbType.Int, 0);
ProjectDB.SqlExecute(sql, p);
UnicornCache.Remove(CacheKey.User);
}
public static void Delete(int id)
{
string sql = @"Delete from Projects where Id=@id";
SqlParam p = new SqlParam();
p.AddParam("@id", id, SqlDbType.Int, 0);
ProjectDB.SqlExecute(sql, p);
UnicornCache.Remove(CacheKey.Project);
}
public static void Add(string Remark, string ProjectId)
{
string sql = @"INSERT INTO [dbo].[Attentions] ([Remark] ,[ProjectId]) VALUES (@Remark ,@ProjectId)";
SqlParam p = new SqlParam();
p.AddParam("@Remark", Remark, SqlDbType.VarChar, 2000);
p.AddParam("@ProjectId", ProjectId, SqlDbType.VarChar, 1000);
ProjectDB.SqlExecute(sql, p);
}
public static void Update(int id, string username, string password)
{
string sql = @"update Users set Password=@Password where Id=@id";
SqlParam p = new SqlParam();
p.AddParam("@id", id, SqlDbType.Int, 0);
p.AddParam("@Password", St.GetMd5(username + password), SqlDbType.VarChar, 100);
ProjectDB.SqlExecute(sql, p);
UnicornCache.Remove(CacheKey.User);
}
public static void Add(int WorkPlanId, string FileName, string FilePath)
{
string sql = @"INSERT INTO [dbo].[WorkPlanFiles] ([WorkPlanId] ,[FileName], [FilePath], [AddTime]) VALUES (@WorkPlanId ,@FileName, @FilePath, GetDate())";
SqlParam p = new SqlParam();
p.AddParam("@WorkPlanId", WorkPlanId, SqlDbType.Int, 0);
p.AddParam("@FileName", FileName, SqlDbType.VarChar, 200);
p.AddParam("@FilePath", FilePath, SqlDbType.VarChar, 200);
ProjectDB.SqlExecute(sql, p);
}
public static void Update(int id, string Remark, string ProjectId)
{
string sql = @"UPDATE [dbo].[Attentions] SET [Remark] = @Remark,[ProjectId] = @ProjectId WHERE id=@id";
SqlParam p = new SqlParam();
p.AddParam("@Remark", Remark, SqlDbType.VarChar, 2000);
p.AddParam("@ProjectId", ProjectId, SqlDbType.VarChar, 1000);
p.AddParam("@id", id, SqlDbType.Int, 0);
ProjectDB.SqlExecute(sql, p);
}
/// <summary>
/// 根据上线时间查询所有已完成计划
/// </summary>
/// <param name="start"></param>
/// <param name="end"></param>
/// <returns></returns>
public static List <WorkPlan> Get(DateTime start, DateTime end)
{
string sql = "select Id,SheepNo,ProjectId,WorkRemark,PlanType,StartTime,EndTime,RealStartTime,RealEndTime,PublishTime,State,NeederId,Remark,AddTime,Tester,Dever,FilePath,SecretScanTime,SecretScanCount from WorkPlans where State=2 and PublishTime between @dtstart and @dtend order by PublishTime";
SqlParam p = new SqlParam();
p.AddParam("@dtstart", start, SqlDbType.DateTime, 0);
p.AddParam("@dtend", end, SqlDbType.DateTime, 0);
DataTable dt = ProjectDB.GetDt(sql, p);
return(GetList(dt));
}
public static void Add(string code, string name, int type, string remark)
{
string sql = @"insert into Dics(Code,Name,Type,Remark) values(@Code,@Name,@Type,@Remark)";
SqlParam p = new SqlParam();
p.AddParam("@Code", code, SqlDbType.VarChar, 50);
p.AddParam("@Name", name, SqlDbType.VarChar, 50);
p.AddParam("@Type", type, SqlDbType.Int, 0);
p.AddParam("@Remark", remark, SqlDbType.VarChar, 50);
ProjectDB.SqlExecute(sql, p);
UnicornCache.Remove(CacheKey.Dic);
}
public static void Update(int id, string code, string name, int type, string remark)
{
string sql = @"update Dics set Code=@Code,Name=@Name,Type=@Type,Remark=@Remark where Id=@id";
SqlParam p = new SqlParam();
p.AddParam("@id", id, SqlDbType.Int, 0);
p.AddParam("@Code", code, SqlDbType.VarChar, 50);
p.AddParam("@Name", name, SqlDbType.VarChar, 50);
p.AddParam("@Type", type, SqlDbType.Int, 0);
p.AddParam("@Remark", remark, SqlDbType.VarChar, 50);
ProjectDB.SqlExecute(sql, p);
UnicornCache.Remove(CacheKey.Dic);
}
public static void Add(string ServerName, string UserName, string Password, string IISVersion, string SqlVersion, string ProjectId, string OSName, string ServerType)
{
string sql = @"INSERT INTO [dbo].[Servers] ([ServerName] ,[UserName] ,[Password] ,[IISVersion] ,[SqlVersion] ,[ProjectId] ,[AddTime],[OSName],[ServerType]) VALUES (@ServerName ,@UserName ,@Password ,@IISVersion ,@SqlVersion ,@ProjectId,GETDATE(),@OSName,@ServerType)";
SqlParam p = new SqlParam();
p.AddParam("@ServerName", ServerName, SqlDbType.VarChar, 200);
p.AddParam("@UserName", UserName, SqlDbType.VarChar, 200);
p.AddParam("@Password", Password, SqlDbType.VarChar, 200);
p.AddParam("@IISVersion", IISVersion, SqlDbType.VarChar, 50);
p.AddParam("@SqlVersion", SqlVersion, SqlDbType.VarChar, 50);
p.AddParam("@ProjectId", ProjectId, SqlDbType.VarChar, 1000);
p.AddParam("@OSName", OSName, SqlDbType.VarChar, 100);
p.AddParam("@ServerType", ServerType, SqlDbType.VarChar, 100);
ProjectDB.SqlExecute(sql, p);
}
public static void Update(int id, string ServerName, string UserName, string Password, string IISVersion, string SqlVersion, string ProjectId, string OSName, string ServerType)
{
string sql = @"UPDATE [dbo].[Servers] SET [ServerName] = @ServerName,[UserName] = @UserName,[Password] = @Password,[IISVersion] = @IISVersion,[SqlVersion] = @SqlVersion,[ProjectId] = @ProjectId,[OSName]=@OSName,[ServerType]=@ServerType WHERE id=@id";
SqlParam p = new SqlParam();
p.AddParam("@ServerName", ServerName, SqlDbType.VarChar, 200);
p.AddParam("@UserName", UserName, SqlDbType.VarChar, 200);
p.AddParam("@Password", Password, SqlDbType.VarChar, 200);
p.AddParam("@IISVersion", IISVersion, SqlDbType.VarChar, 50);
p.AddParam("@SqlVersion", SqlVersion, SqlDbType.VarChar, 50);
p.AddParam("@ProjectId", ProjectId, SqlDbType.VarChar, 1000);
p.AddParam("@OSName", OSName, SqlDbType.VarChar, 100);
p.AddParam("@ServerType", ServerType, SqlDbType.VarChar, 100);
p.AddParam("@id", id, SqlDbType.Int, 0);
ProjectDB.SqlExecute(sql, p);
}
public static void Add(string username, string realname, int userrole, DateTime leavetime, int bugzillaid)
{
string sql = @" if exists(select Id from Users where UserName=@UserName)
update Users set RealName=@RealName,RoleType=@RoleType,LeaveTime=@LeaveTime,bugzillaid=@bugzillaid where UserName=@UserName
else
insert into Users(UserName,Password,RealName,RoleType,Status,LeaveTime,bugzillaid) values(@UserName,@Password,@RealName,@RoleType,1,@LeaveTime,@bugzillaid)" ;
SqlParam p = new SqlParam();
p.AddParam("@UserName", username, SqlDbType.VarChar, 100);
p.AddParam("@RealName", realname, SqlDbType.VarChar, 500);
p.AddParam("@RoleType", userrole, SqlDbType.Int, 0);
p.AddParam("@Password", St.GetMd5(username + "123456"), SqlDbType.VarChar, 100);
p.AddParam("@LeaveTime", leavetime, SqlDbType.DateTime, 0);
p.AddParam("@bugzillaid", bugzillaid, SqlDbType.Int, 0);
ProjectDB.SqlExecute(sql, p);
UnicornCache.Remove(CacheKey.User);
}
public static List <Attentions> Get()
{
List <Attentions> list = new List <Attentions>();
string sql = "SELECT [Id] ,[Remark],[ProjectId],[AddTime] FROM [dbo].[Attentions] order by Id desc";
DataTable dt = ProjectDB.GetDt(sql);
List <Project> lp = ProjectRule.Get();
foreach (DataRow row in dt.Rows)
{
Attentions o = new Attentions();
o.Id = St.ToInt32(row["Id"], 0);
o.Remark = row["Remark"].ToString();
o.ProjectId = row["ProjectId"].ToString();
o.ProjectName = o.ProjectId == "" ? "" : string.Join(",", lp.Where(a => Array.IndexOf(o.ProjectId.Split(','), a.Id.ToString()) >= 0).Select(a => a.Name).ToArray());
o.AddTime = St.ToDateTime(row["AddTime"].ToString());
list.Add(o);
}
return(list);
}
public static List <WorkPlanFile> GetList(int workplanid)
{
string sql = "select Id,WorkPlanId,FileName,FilePath from WorkPlanFiles where WorkPlanId = @workplanid order by Id desc";
SqlParam p = new SqlParam();
p.AddParam("@workplanid", workplanid, SqlDbType.Int, 0);
DataTable dt = ProjectDB.GetDt(sql, p);
List <WorkPlanFile> list = new List <WorkPlanFile>();
foreach (DataRow row in dt.Rows)
{
WorkPlanFile o = new WorkPlanFile();
o.Id = St.ToInt32(row["Id"], 0);
o.WorkPlanId = St.ToInt32(row["WorkPlanId"], 0);
o.FileName = row["FileName"].ToString();
o.FilePath = row["FilePath"].ToString();
list.Add(o);
}
return(list);
}
public static WorkPlanFile Get(int id)
{
string sql = "select Id,WorkPlanId,FileName,FilePath from WorkPlanFiles where Id = @Id";
SqlParam p = new SqlParam();
p.AddParam("@Id", id, SqlDbType.Int, 0);
DataTable dt = ProjectDB.GetDt(sql, p);
WorkPlanFile o = new WorkPlanFile();
if (dt.Rows.Count > 0)
{
var row = dt.Rows[0];
o.Id = St.ToInt32(row["Id"], 0);
o.WorkPlanId = St.ToInt32(row["WorkPlanId"], 0);
o.FileName = row["FileName"].ToString();
o.FilePath = row["FilePath"].ToString();
}
return(o);
}
public static void Add(int id, string name, string url, int parentid, string TestUrl, string SiteFileName, string DatabaseName, string TestUserName, string TestPassword, string Remark, int IsShow)
{
string sql = @" if exists(select Id from Projects where Id=@Id)
update Projects set Name=@Name,ParentId=@ParentId,Url=@Url,TestUrl=@TestUrl,SiteFileName=@SiteFileName,DatabaseName=@DatabaseName,TestUserName=@TestUserName,TestPassword=@TestPassword,Remark=@Remark,IsShow=@IsShow where Id=@Id
else
insert into Projects(Name,ParentId,Url,TestUrl,SiteFileName,DatabaseName,TestUserName,TestPassword,Remark,IsShow) values(@Name,@ParentId,@Url,@TestUrl,@SiteFileName,@DatabaseName,@TestUserName,@TestPassword,@Remark,@IsShow)" ;
SqlParam p = new SqlParam();
p.AddParam("@Id", id, SqlDbType.Int, 0);
p.AddParam("@Name", name, SqlDbType.VarChar, 500);
p.AddParam("@ParentId", parentid, SqlDbType.Int, 0);
p.AddParam("@Url", url, SqlDbType.VarChar, 500);
p.AddParam("@TestUrl", TestUrl, SqlDbType.VarChar, 500);
p.AddParam("@SiteFileName", SiteFileName, SqlDbType.VarChar, 500);
p.AddParam("@DatabaseName", DatabaseName, SqlDbType.VarChar, 500);
p.AddParam("@TestUserName", TestUserName, SqlDbType.VarChar, 200);
p.AddParam("@TestPassword", TestPassword, SqlDbType.VarChar, 200);
p.AddParam("@Remark", Remark, SqlDbType.VarChar, 500);
p.AddParam("@IsShow", IsShow, SqlDbType.Int, 0);
ProjectDB.SqlExecute(sql, p);
UnicornCache.Remove(CacheKey.Project);
}