/// <summary> /// 根据关联的unitid获取主题 /// </summary> /// <param name="unitid"></param> /// <returns></returns> static public List <TestTheme> getTestThemeByUnitid(string unitid) { List <TestTheme> ls = new List <TestTheme>(); string sql1 = "select count(*) from testunittheme where themeid = 99999 and unitid=" + unitid; int count = SqlDBUtil.ExecuteScalar(sql1); if (count > 0) { string personid = System.Configuration.ConfigurationManager.AppSettings["UserId"]; //string personname = System.Configuration.ConfigurationManager.AppSettings["Username"]; TestTheme default_tt = new TestTheme(); default_tt.Id = 99999; default_tt.Personid = ((personid == null)?0:Int32.Parse(personid)); default_tt.Personname = System.Configuration.ConfigurationManager.AppSettings["Username"]; ls.Add(default_tt); } string sql = "SELECT * FROM TestTheme " + "where id in (select themeid from testunittheme where unitid=" + unitid + ")"; DataSet data = SqlDBUtil.ExecuteQuery(sql); foreach (DataRow row in data.Tables["ds"].Rows) { ls.Add(Row2TestTheme(row)); } return(ls); }
static public DataTable getRePortBugLevelAll(string begintime, string endtime) { DataTable numtable = new DataTable("numdt"); numtable.Columns.Add("", Type.GetType("System.String")); numtable.Columns.Add("轻微", Type.GetType("System.Int32")); numtable.Columns.Add("一般", Type.GetType("System.Int32")); numtable.Columns.Add("紧急", Type.GetType("System.Int32")); numtable.Columns.Add("严重", Type.GetType("System.Int32")); List <PersonInfo> personlist = PersonDao.getAllPersonInfo(); string sql = "SELECT count(*) from testunit where " + "cast(testtime as datetime)>=cast('" + begintime + "' as datetime) " + "and cast(testtime as datetime)<=cast('" + endtime + "' as datetime) " + "and buglevel='{0}'"; string sqltemp = string.Format(sql, CommonConst.BUGLEVEL_QinWei); int qinwei = SqlDBUtil.ExecuteScalar(sqltemp); sqltemp = string.Format(sql, CommonConst.BUGLEVEL_YiBan); int yiban = SqlDBUtil.ExecuteScalar(sqltemp); sqltemp = string.Format(sql, CommonConst.BUGLEVEL_YanZhong); int yanzhong = SqlDBUtil.ExecuteScalar(sqltemp); sqltemp = string.Format(sql, CommonConst.BUGLEVEL_JinJI); int jinji = SqlDBUtil.ExecuteScalar(sqltemp); numtable.Rows.Add(null, qinwei, yiban, jinji, yanzhong); return(numtable); }
/// <summary> /// 统计BUG趋势图 /// </summary> /// <param name="begintime"></param> /// <param name="endtime"></param> /// <returns></returns> static public DataTable getRePortBugNum(string begintime, string endtime) { DataTable numtable = new DataTable("numdt"); numtable.Columns.Add("name", Type.GetType("System.String")); //------------------查时间点-------------------------------- string sqlcol = "SELECT distinct(CONVERT(varchar(10), cast(testtime as datetime), 23) ) FROM testunit " + "where cast(testtime as datetime)>=cast('" + begintime + "' as datetime) and cast(testtime as datetime)<=cast('" + endtime + "' as datetime) " + "order by CONVERT(varchar(10), cast(testtime as datetime), 23) asc"; DataSet datacol = SqlDBUtil.ExecuteQuery(sqlcol); DataRowCollection drs = datacol.Tables["ds"].Rows; List <string> timecol = new List <string>(); for (int i = 0; i < drs.Count; i++) { timecol.Add(drs[i][0].ToString()); numtable.Columns.Add(drs[i][0].ToString(), Type.GetType("System.Int32")); } //------------------查姓名-------------------------------------------- string sqlname = "SELECT distinct(adminname) FROM testunit " + "where cast(testtime as datetime)>=cast('" + begintime + "' as datetime) and cast(testtime as datetime)<=cast('" + endtime + "' as datetime) "; DataSet dataname = SqlDBUtil.ExecuteQuery(sqlname); DataRowCollection drsname = dataname.Tables["ds"].Rows; List <string> adminname = new List <string>(); for (int j = 0; j < drsname.Count; j++) { adminname.Add(drsname[j][0].ToString()); } string sql = "SELECT count(*) FROM testunit where cast(testtime as datetime)>=" + "cast('" + begintime + "' as datetime) and " + "cast(testtime as datetime)<=cast('" + endtime + "' as datetime) and " + "adminname='{0}' and CONVERT(varchar(10), cast(testtime as datetime), 23) =CONVERT(varchar(10), cast('{1}' as datetime), 23)"; string sqlv = ""; for (int a = 0; a < adminname.Count; a++) { DataRow dr = numtable.NewRow(); dr[0] = adminname[a]; for (int b = 0; b < timecol.Count; b++) { sqlv = string.Format(sql, adminname[a], timecol[b]); int num = SqlDBUtil.ExecuteScalar(sqlv); dr[b + 1] = num; } numtable.Rows.Add(dr); } return(numtable); }
/// <summary> /// 检查模块代码是否存在 /// </summary> /// <param name="code">模块代码</param> /// <returns>true 存在,false 不存在</returns> static public bool getModuleByCode(string code) { string sql = "select count(*) from ModuleInfo where code = '" + code + "'"; int data = SqlDBUtil.ExecuteScalar(sql); if (data > 0) { return(true); } else { return(false); } }
static public int QueryUIcheckCount(string moduleid, string managerid, string state, string begintime, string endtime) { string sql = "select count(*) from uicheckinfo where " + "(0=" + moduleid + " or moduleid=" + moduleid + ")" + " and (0=" + managerid + " or adminid=" + managerid + ")" + " and ('全部状态'='" + state + "' or state='" + state + "')"; if (begintime != null) { sql += " and cast(createtime as datetime)>=cast('" + begintime + "' as datetime)"; } if (endtime != null) { sql += " and cast(createtime as datetime)<=cast('" + endtime + "' as datetime)"; } return(SqlDBUtil.ExecuteScalar(sql)); }
static public DataTable getRePortPackNUM(string begintime, string endtime) { DataTable numtable = new DataTable("numdt"); numtable.Columns.Add("personname", Type.GetType("System.String")); numtable.Columns.Add("pubnum", Type.GetType("System.Int32")); numtable.Columns.Add("fznum", Type.GetType("System.Int32")); numtable.Columns.Add("totalnum", Type.GetType("System.Int32")); numtable.Columns.Add("rate", Type.GetType("System.String")); List <PersonInfo> personlist = PersonDao.getAllPersonInfo(); string sql = "SELECT count(*) from packageinfo a where " + "a.managerid ={0} " + "and cast(packtime as datetime)>=cast('" + begintime + "' as datetime) " + "and cast(packtime as datetime)<=cast('" + endtime + "' as datetime) " + "and state='{1}'"; string totalsql = "SELECT count(*) from packageinfo a where " + "a.managerid ={0} " + "and cast(packtime as datetime)>=cast('" + begintime + "' as datetime) " + "and cast(packtime as datetime)<=cast('" + endtime + "' as datetime) "; System.Globalization.NumberFormatInfo provider = new System.Globalization.NumberFormatInfo(); provider.PercentDecimalDigits = 2; //小数点保留几位数. provider.PercentPositivePattern = 1; //百分号出现在何处. int pub = 0; int fz = 0; int total = 0; foreach (PersonInfo ps in personlist) { string sqltemp = string.Format(sql, ps.Id, CommonConst.PACKSTATE_YiFaBu); int pubnum = SqlDBUtil.ExecuteScalar(sqltemp); pub += pubnum; //合计数 sqltemp = string.Format(sql, ps.Id, CommonConst.PACKSTATE_YiFeiZhi); int fznum = SqlDBUtil.ExecuteScalar(sqltemp); fz += fznum; //合计数 sqltemp = string.Format(totalsql, ps.Id); int totalnum = SqlDBUtil.ExecuteScalar(sqltemp); total += totalnum; //合计数 double rate = (double)pubnum / (fznum + pubnum); //一定要用double类型. numtable.Rows.Add(ps.Fullname, pubnum, fznum, totalnum, rate.ToString("P", provider)); } double totalrate = (double)pub / (fz + pub); //一定要用double类型. numtable.Rows.Add("合计", pub, fz, total, totalrate.ToString("P", provider)); return(numtable); }
static public int queryPackageInfoCount(string moduleid, string managerid, string state, string begintime, string endtime) { string sql = "select count(*) from packageinfo where " + "(0=" + moduleid + " or moduleid=" + moduleid + ")" + " and (0=" + managerid + " or managerid=" + managerid + ")" + " and ('全部'='" + state + "' or state='" + state + "')"; if (begintime != null) { sql += " and cast(packtime as datetime)>=cast('" + begintime + "' as datetime)"; } if (endtime != null) { sql += " and cast(packtime as datetime)<=cast('" + endtime + "' as datetime)"; } return(SqlDBUtil.ExecuteScalar(sql)); }
static public DataTable getRePortBugLevel(string begintime, string endtime) { DataTable numtable = new DataTable("numdt"); numtable.Columns.Add("personname", Type.GetType("System.String")); numtable.Columns.Add("轻微", Type.GetType("System.Int32")); numtable.Columns.Add("一般", Type.GetType("System.Int32")); numtable.Columns.Add("紧急", Type.GetType("System.Int32")); numtable.Columns.Add("严重", Type.GetType("System.Int32")); numtable.Columns.Add("总计", Type.GetType("System.Int32")); List <PersonInfo> personlist = PersonDao.getAllPersonInfo(); string sql = "SELECT count(*) from testunit where adminid={0} " + "and cast(testtime as datetime)>=cast('" + begintime + "' as datetime) " + "and cast(testtime as datetime)<=cast('" + endtime + "' as datetime) " + "and buglevel='{1}'"; int 轻微 = 0; int 一般 = 0; int 紧急 = 0; int 严重 = 0; foreach (PersonInfo ps in personlist) { string sqltemp = string.Format(sql, ps.Id, CommonConst.BUGLEVEL_QinWei); int qinwei = SqlDBUtil.ExecuteScalar(sqltemp); 轻微 += qinwei; //合计数 sqltemp = string.Format(sql, ps.Id, CommonConst.BUGLEVEL_YiBan); int yiban = SqlDBUtil.ExecuteScalar(sqltemp); 一般 += yiban; //合计数 sqltemp = string.Format(sql, ps.Id, CommonConst.BUGLEVEL_YanZhong); int yanzhong = SqlDBUtil.ExecuteScalar(sqltemp); 严重 += yanzhong; //合计数 sqltemp = string.Format(sql, ps.Id, CommonConst.BUGLEVEL_JinJI); int jinji = SqlDBUtil.ExecuteScalar(sqltemp); 紧急 += jinji; //合计数 int zongji = jinji + yanzhong + yiban + qinwei; numtable.Rows.Add(ps.Fullname, qinwei, yiban, jinji, yanzhong, zongji); } int 总计 = 紧急 + 严重 + 一般 + 轻微; numtable.Rows.Add("合计", 轻微, 一般, 紧急, 严重, 总计); return(numtable); }
static public int QueryTestUnitCount(string moduleid, string managerid, string level, string state, string begintime, string endtime) { string sql = "select count(*) from testunit where " + "(0=" + moduleid + " or moduleid=" + moduleid + ")" + " and (0=" + managerid + " or adminid=" + managerid + ")" + " and ('全部等级'='" + level + "' or buglevel='" + level + "')" + " and ('全部'='" + state + "' or state='" + state + "')"; if (begintime != null) { sql += " and cast(testtime as datetime)>=cast('" + begintime + "' as datetime)"; } if (endtime != null) { sql += " and cast(testtime as datetime)<=cast('" + endtime + "' as datetime)"; } return(SqlDBUtil.ExecuteScalar(sql)); }
/// <summary> /// 统计BUG类型 /// </summary> /// <param name="begintime"></param> /// <param name="endtime"></param> /// <returns></returns> static public DataTable getRePortBugType(string begintime, string endtime) { DataTable numtable = new DataTable("numdt"); numtable.Columns.Add("", Type.GetType("System.String")); numtable.Columns.Add(CommonConst.BUGTYPE_JieMian, Type.GetType("System.Int32")); numtable.Columns.Add(CommonConst.BUGTYPE_GongNeng, Type.GetType("System.Int32")); numtable.Columns.Add(CommonConst.BUGTYPE_JianYi, Type.GetType("System.Int32")); numtable.Columns.Add(CommonConst.BUGTYPE_BianMa, Type.GetType("System.Int32")); numtable.Columns.Add(CommonConst.BUGTYPE_BianYi, Type.GetType("System.Int32")); //------------------查姓名-------------------------------------------- string sqlname = "SELECT distinct adminname FROM testunit " + "where cast(testtime as datetime)>=cast('" + begintime + "' as datetime) and cast(testtime as datetime)<=cast('" + endtime + "' as datetime) "; DataSet dataname = SqlDBUtil.ExecuteQuery(sqlname); DataRowCollection drsname = dataname.Tables["ds"].Rows; List <string> adminname = new List <string>(); for (int j = 0; j < drsname.Count; j++) { adminname.Add(drsname[j][0].ToString()); numtable.Columns.Add(drsname[j][0].ToString(), Type.GetType("System.Double")); } string sql = "select count(*) from testunit where bugtype = '{0}' and adminname='{1}'" + "and cast(testtime as datetime)>=cast('" + begintime + "' as datetime) and " + " cast(testtime as datetime)<=cast('" + endtime + "' as datetime)"; for (int i = 0; i < adminname.Count; i++) { DataRow dr = numtable.NewRow(); dr[0] = adminname[i]; for (int p = 0; p < CommonConst.BUGTYPE.Length; p++) { dr[p + 1] = SqlDBUtil.ExecuteScalar(string.Format(sql, CommonConst.BUGTYPE[p], adminname[i])); } numtable.Rows.Add(dr); } return(numtable); }
/// <summary> /// 获取每个模块,关联的项目数 /// </summary> /// <param name="packid">更新包ID</param> /// <returns></returns> static public int getPrjNumByPackid(string packid) { string sql = "select count(*) from dbo.moduleproject where moduleid = (select moduleid from packageinfo where id=" + packid + ")"; return(SqlDBUtil.ExecuteScalar(sql)); }
//发布更新包查询 static public int queryPubPackageInfoCount(string moduleid, string managerid, string pubpath, string keyword, string begintime, string endtime) { string sql = "SELECT count(DISTINCT a.id) FROM packageInfo AS a INNER JOIN " + "moduleInfo AS b ON a.moduleid = b.ID INNER JOIN " + "moduleproject AS c ON b.ID = c.moduleid INNER JOIN " + "projectinfo AS d ON c.projectid = d.ID " + "WHERE (0 = " + moduleid + " or a.moduleid = " + moduleid + ") " + "and (0 = " + managerid + " or a.managerid = " + managerid + ") "; if (null == pubpath) { pubpath = ""; sql += " and a.state ='已发布'"; if (begintime != null) { sql += " and cast(a.publishtime as datetime)>=cast('" + begintime + "' as datetime)"; } if (endtime != null) { sql += " and cast(a.publishtime as datetime)<=cast('" + endtime + "' as datetime)"; } } else if (pubpath.Equals("未发布")) { sql += " and a.state ='已测试'"; if (begintime != null) { sql += " and cast(a.testtime as datetime)>=cast('" + begintime + "' as datetime)"; } if (endtime != null) { sql += " and cast(a.testtime as datetime)<=cast('" + endtime + "' as datetime)"; } } else if (pubpath.Equals("今日发布")) { begintime = System.DateTime.Now.ToString("yyyy-MM-dd") + " 00:00:00"; endtime = System.DateTime.Now.ToString("yyyy-MM-dd") + " 23:59:59"; sql += " and cast(a.publishtime as datetime)>=cast('" + begintime + "' as datetime)"; sql += " and cast(a.publishtime as datetime)<=cast('" + endtime + "' as datetime)"; } else { sql += " and a.state ='已发布'"; sql += " and (d.ftppath + a.pubpath LIKE '" + pubpath + "%')"; if (begintime != null) { sql += " and cast(a.publishtime as datetime)>=cast('" + begintime + "' as datetime)"; } if (endtime != null) { sql += " and cast(a.publishtime as datetime)<=cast('" + endtime + "' as datetime)"; } } if (null != keyword && "".Equals(keyword)) { sql += " and a.packagename like '%" + keyword + "%'"; } return(SqlDBUtil.ExecuteScalar(sql)); }