/// <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));
}
