static public List <TestUnit> QueryTestUnit(string moduleid, string managerid, string level, string state, string begintime, string endtime,
int startnum, int pagesize)
{
string sql = "select Unitno,Packagename,Buglevel,Testtitle,Testtime,Adminname,State,Id 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)";
}
sql += " order by cast(testtime as datetime) desc";
DataSet data = SqlDBUtil.ExecuteQuery(sql, startnum, pagesize);
List <TestUnit> ls = new List <TestUnit>();
foreach (DataRow row in data.Tables["ds"].Rows)
{
ls.Add(Row2Tu(row));
}
return(ls);
}
static public List <PackageInfo> queryPackageInfo(string moduleid, string managerid, string state, string begintime, string endtime)
{
string sql = "select * 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)";
}
sql += " order by cast(packtime as datetime) desc";
DataSet data = SqlDBUtil.ExecuteQuery(sql, null);
List <PackageInfo> ls = new List <PackageInfo>();
foreach (DataRow row in data.Tables["ds"].Rows)
{
ls.Add(Row2PackageInfo(row));
}
return(ls);
}
/// <summary>
/// 所有没有测试的更新包除了已发布，已废止的。
/// </summary>
/// <returns></returns>
static public DataTable getAllUnTestPack()
{
string sql = "SELECT packageInfo.*,moduleInfo.id as realmoduleid, moduleInfo.code FROM moduleInfo right JOIN packageInfo ON moduleInfo.ID = packageInfo.moduleid order by cast(packageInfo.packtime as datetime) desc";
DataSet data = SqlDBUtil.ExecuteQuery(sql);
return(data.Tables["ds"]);
}
static public List <UIcheckinfo> QueryUIcheckinfo(string moduleid, string managerid, string state, string begintime, string endtime,
int startnum, int pagesize)
{
string sql = "select * 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)";
}
sql += " order by cast(createtime as datetime) desc";
DataSet data = SqlDBUtil.ExecuteQuery(sql, startnum, pagesize);
List <UIcheckinfo> ls = new List <UIcheckinfo>();
foreach (DataRow row in data.Tables["ds"].Rows)
{
ls.Add(Row2UIcheckinfo(row));
}
return(ls);
}
/// <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 getRePortTest(string begintime, string endtime)
{
string sql = "SELECT unitno,buglevel,packagename,testtitle FROM testunit " +
"where cast(testtime as datetime)>=cast('" + begintime + "' as datetime) and cast(testtime as datetime)<=cast('" + endtime + "' as datetime) order by unitno asc";
DataSet data = SqlDBUtil.ExecuteQuery(sql);
return(data.Tables["ds"]);
}
static public DataTable getRePortPack(string begintime, string endtime)
{
string sql = "SELECT packageInfo.packagename, packageInfo.packtime,personinfo.fullname FROM personinfo right JOIN packageInfo ON personinfo.ID = packageInfo.managerid " +
"where cast(packageInfo.packtime as datetime) >=cast('" + begintime + "' as datetime)" +
" and cast(packageInfo.packtime as datetime) <=cast('" + endtime + "' as datetime)" + " order by cast(packageInfo.packtime as datetime) asc";
DataSet data = SqlDBUtil.ExecuteQuery(sql);
return(data.Tables["ds"]);
}
static public List <UICheckView> getUICheckViewByNO(string checkno)
{
DataSet data = SqlDBUtil.ExecuteQuery("select * from UICheckView where checkno='" + checkno + "'");
List <UICheckView> ls = new List <UICheckView>();
foreach (DataRow row in data.Tables["ds"].Rows)
{
ls.Add(Row2UICheckview(row));
}
return(ls);
}
static public DataTable getPersonTable()
{
string sql = "select * from PersonInfo where role like '%1;%' or role like '%1'";
DataSet data = SqlDBUtil.ExecuteQuery(sql);
DataRow dr = data.Tables["ds"].NewRow();
dr["fullname"] = "全部责任人";
dr["id"] = 0;
data.Tables["ds"].Rows.InsertAt(dr, 0);
return(data.Tables["ds"]);
}
static public DataTable getAllProjectTable()
{
string sql = "select * from projectinfo order by projectname";
DataSet data = SqlDBUtil.ExecuteQuery(sql, null);
DataRow dr = data.Tables["ds"].NewRow();
dr["projectname"] = "全部项目";
dr["id"] = 0;
data.Tables["ds"].Rows.InsertAt(dr, 0);
return(data.Tables["ds"]);
}
static public DataTable getAllModuleTable()
{
string sql = "select * from ModuleInfo order by fullname";
DataSet data = SqlDBUtil.ExecuteQuery(sql, null);
DataRow dr = data.Tables["ds"].NewRow();
dr["fullname"] = "全部平台";
dr["id"] = 0;
data.Tables["ds"].Rows.InsertAt(dr, 0);
return(data.Tables["ds"]);
}
static public List <TestUnit> getAlltestUnit()
{
DataSet data = SqlDBUtil.ExecuteQuery("select Unitno,Packagename,Buglevel,Testtitle,Testtime,Adminname,State,Id from testunit order by Unitno desc");
List <TestUnit> ls = new List <TestUnit>();
foreach (DataRow row in data.Tables["ds"].Rows)
{
ls.Add(Row2Tu(row));
}
return(ls);
}
static public PersonInfo getPersonInfoByModuleid(int id)
{
string sql = "select PersonInfo.* from PersonInfo,moduleInfo where PersonInfo.id = moduleInfo.managerid and moduleInfo.id=" + id.ToString();
DataSet data = SqlDBUtil.ExecuteQuery(sql);
PersonInfo ls = new PersonInfo();
if (data.Tables["ds"].Rows.Count > 0)
{
ls = Row2PersonInfo(data.Tables["ds"].Rows[0]);
}
return(ls);
}
////////
static public List <PackageInfo> getPackageInfoBypath(string path)
{
string sql = "select * from packageinfo where packagepath='" + path + "'";
DataSet data = SqlDBUtil.ExecuteQuery(sql, null);
List <PackageInfo> ls = new List <PackageInfo>();
foreach (DataRow row in data.Tables["ds"].Rows)
{
ls.Add(Row2PackageInfo(row));
}
return(ls);
}
static public List <PersonInfo> getAllPersonInfo(string name, string password)
{
string sql = "select * from PersonInfo where fullname='" + name + "' and password='******'";
DataSet data = SqlDBUtil.ExecuteQuery(sql);
List <PersonInfo> ls = new List <PersonInfo>();
foreach (DataRow row in data.Tables["ds"].Rows)
{
ls.Add(Row2PersonInfo(row));
}
return(ls);
}
/// <summary>
/// 根据role值获取人员列表，0普通用户1主管2美工3配置4经理5测试
/// </summary>
/// <param name="role"></param>
/// <returns></returns>
static public List <PersonInfo> getPersonByRole(int role)
{
string sql = "select * from PersonInfo where role like '%" + role.ToString() + "%'";
DataSet data = SqlDBUtil.ExecuteQuery(sql);
List <PersonInfo> ls = new List <PersonInfo>();
foreach (DataRow row in data.Tables["ds"].Rows)
{
ls.Add(Row2PersonInfo(row));
}
return(ls);
}
/// <summary>
/// 查询被主题关联的缺陷列表
/// </summary>
/// <param name="themeid"></param>
/// <returns></returns>
public static List <TestUnit> getGuanLianUnitList(string themeid)
{
string sql = "select * from testunit where id in (select unitid from testunittheme where themeid =" + themeid + ")";
DataSet data = SqlDBUtil.ExecuteQuery(sql);
List <TestUnit> ls = new List <TestUnit>();
foreach (DataRow row in data.Tables["ds"].Rows)
{
ls.Add(Row2Tu(row));
}
return(ls);
}
static public List <PersonInfo> getAllPersonInfo()
{
string sql = "select * from PersonInfo";
DataSet data = SqlDBUtil.ExecuteQuery(sql);
List <PersonInfo> ls = new List <PersonInfo>();
foreach (DataRow row in data.Tables["ds"].Rows)
{
ls.Add(Row2PersonInfo(row));
}
return(ls);
}
static public List <ModuleInfo> getAllModuleInfoByProjectID(string id)
{
string sql = "SELECT * from moduleInfo where id in (select moduleid from moduleproject where projectid=" + id + ")";
DataSet data = SqlDBUtil.ExecuteQuery(sql);
List <ModuleInfo> ls = new List <ModuleInfo>();
foreach (DataRow row in data.Tables["ds"].Rows)
{
ls.Add(Row2ModuleInfo(row));
}
return(ls);
}
static public List <ModuleProject> getAllMPByPrjIDAndMdlID(string pid, string mid)
{
string sql = "SELECT * from ModuleProject where moduleid=" + mid + " and projectid=" + pid;
DataSet data = SqlDBUtil.ExecuteQuery(sql);
List <ModuleProject> ls = new List <ModuleProject>();
foreach (DataRow row in data.Tables["ds"].Rows)
{
ls.Add(Row2ModuleProject(row));
}
return(ls);
}
static public List <ProjectInfo> getAllProjectInfo()
{
string sql = "select * from projectinfo order by projectname";
DataSet data = SqlDBUtil.ExecuteQuery(sql, null);
List <ProjectInfo> ls = new List <ProjectInfo>();
foreach (DataRow row in data.Tables["ds"].Rows)
{
ls.Add(Row2ProjectInfo(row));
}
return(ls);
}
static public List <ProjectInfo> getAllProjectInfoByModuleid(int id)
{
string sql = "select * from ProjectInfo where id in (SELECT moduleproject.projectid FROM moduleproject " +
"where moduleproject.moduleid = " + id + ")";
DataSet data = SqlDBUtil.ExecuteQuery(sql);
List <ProjectInfo> ls = new List <ProjectInfo>();
foreach (DataRow row in data.Tables["ds"].Rows)
{
ls.Add(Row2ProjectInfo(row));
}
return(ls);
}
static public List <ProjectInfo> getAllProjectInfoByModulename(string mname, string mcode)
{
string sql = "select * from ProjectInfo where id in (SELECT moduleproject.projectid FROM moduleInfo , moduleproject " +
"where moduleInfo.ID = moduleproject.moduleid and moduleInfo.fullname like '" + mname + "%' and code = '" + mcode + "')";
DataSet data = SqlDBUtil.ExecuteQuery(sql);
List <ProjectInfo> ls = new List <ProjectInfo>();
foreach (DataRow row in data.Tables["ds"].Rows)
{
ls.Add(Row2ProjectInfo(row));
}
return(ls);
}
static public List <ModuleInfo> getAllModuleInfoLikename(string mname, string mcode)
{
string sql = "SELECT * FROM moduleInfo " +
"where moduleInfo.fullname like '%" + mname + "%' and code = '" + mcode + "'";
DataSet data = SqlDBUtil.ExecuteQuery(sql);
List <ModuleInfo> ls = new List <ModuleInfo>();
foreach (DataRow row in data.Tables["ds"].Rows)
{
ls.Add(Row2ModuleInfo(row));
}
return(ls);
}
/// <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);
}
static public TestUnit gettestUnitById(int id)
{
DataSet data = SqlDBUtil.ExecuteQuery("select top 1 * from testunit where id=" + id.ToString());
if (data.Tables["ds"].Rows.Count > 0)
{
TestUnit ls = Row2TestUnit(data.Tables["ds"].Rows[0]);
return(ls);
}
else
{
return(new TestUnit());
}
}
// ///
static public PackageInfo getPackageInfoByID(string id)
{
string sql = "select * from packageinfo where id='" + id + "'";
DataSet data = SqlDBUtil.ExecuteQuery(sql, null);
if (data.Tables["ds"].Rows.Count > 0)
{
return(Row2PackageInfo(data.Tables["ds"].Rows[0]));
}
else
{
return(null);
}
}
static public UIcheckinfo getUIcheckInfoByPackId(int id)
{
DataSet data = SqlDBUtil.ExecuteQuery("select top 1 * from UIcheckinfo where packageid=" + id.ToString());
if (data.Tables["ds"].Rows.Count > 0)
{
UIcheckinfo ls = Row2UIcheckinfo(data.Tables["ds"].Rows[0]);
return(ls);
}
else
{
return(new UIcheckinfo());
}
}
/// <summary>
/// 统计BUG概率
/// </summary>
/// <param name="begintime"></param>
/// <param name="endtime"></param>
/// <returns></returns>
static public DataTable getRePortBugRate(string begintime, string endtime)
{
DataTable numtable = new DataTable("numdt");
numtable.Columns.Add("");
//------------------查姓名--------------------------------------------
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 sum(packageInfo.testrate/100) FROM testunit INNER JOIN packageInfo ON testunit.packageid = packageInfo.ID " +
"where cast(testunit.testtime as datetime)>=cast('" + begintime + "' as datetime) and cast(testunit.testtime as datetime)<=cast('" + endtime + "' as datetime) and testunit.adminname='{0}'";
string sqlnum = "select count(*) from (select distinct testunit.packageid FROM testunit INNER JOIN packageInfo ON testunit.packageid = packageInfo.ID " +
"where cast(testunit.testtime as datetime)>=cast('" + begintime + "' as datetime) and cast(testunit.testtime as datetime)<=cast('" + endtime + "' as datetime) and testunit.adminname='{0}') a";
string sqlv = "";
DataRow dr = numtable.NewRow();
for (int i = 0; i < adminname.Count; i++)
{
sqlv = string.Format(sql, adminname[i]);
double num1 = SqlDBUtil.ExecuteSUM(sqlv, null);
sqlv = string.Format(sqlnum, adminname[i]);
double num2 = SqlDBUtil.ExecuteSUM(sqlv, null);
if (num2 == 0.00)
{
dr[i + 1] = 0;
}
else
{
dr[i + 1] = num1 / num2;
}
}
numtable.Rows.Add(dr);
return(numtable);
}
static public List <TestTheme> getAllTestThemeByPersonname(string personname)
{
string sql = "SELECT * FROM TestTheme ";
if (string.IsNullOrEmpty(personname))
{
sql += "where personname is null";
}
else
{
sql += "where personname='" + personname + "'";
}
DataSet data = SqlDBUtil.ExecuteQuery(sql);
List <TestTheme> ls = new List <TestTheme>();
foreach (DataRow row in data.Tables["ds"].Rows)
{
ls.Add(Row2TestTheme(row));
}
return(ls);
}