DALService.DALServiceClient dal = new TMS.WinUI.DALService.DALServiceClient();
DataSet ds = new DataSet();
DataTable dt = dal.GetReportTrace(itemID, stateID, departID, dtpBegin.Value.AddHours(-24), dtpEnd.Value.AddSeconds(1), proposedFlag, dtpPlanBegin.Value, dtpPlanEnd.Value, planFlag, dtpRealBegin.Value, dtpRealEnd.Value, tFlag).Tables[0];
dt.TableName = "ReportTrace";
DataTable dtProject = dal.GetReportProject().Tables[0];
dtProject.TableName = "ProjectTable";
DataTable dtUseCase = dal.GetReportTestUseCase().Tables[0];
dtUseCase.TableName = "UseCaseTable";
ds.Tables.Add(dt.Copy());
ds.Tables.Add(dtProject.Copy());
ds.Tables.Add(dtUseCase.Copy());
ds.Relations.Add("ReportKey",ds.Tables["ReportTrace"].Columns["RequirementID"], ds.Tables["ProjectTable"].Columns["RequirementID"],false);
ds.Relations.Add("UseCaseKey", ds.Tables["ProjectTable"].Columns["ProjectID"], ds.Tables["UseCaseTable"].Columns["ProjectID"], false);
this.tmsUltraGridList.DataSource = ds;
dal.Close();
#region 对UltraGrid的调整,必须放在bind()下面
//第一层表数据
tmsUltraGridList.DisplayLayout.Bands[0].Columns[0].Hidden = true;//0隐藏RequirementID
tmsUltraGridList.DisplayLayout.Bands[0].Columns[1].Hidden = true;//1隐藏ItemID
tmsUltraGridList.DisplayLayout.Bands[0].Columns[2].Header.Caption = "系统";//2ItemName
tmsUltraGridList.DisplayLayout.Bands[0].Columns[3].Header.Caption = "需求编码";//3需求编码
tmsUltraGridList.DisplayLayout.Bands[0].Columns[4].Hidden = true;//4需求状态ID
tmsUltraGridList.DisplayLayout.Bands[0].Columns[5].Header.Caption = "需求状态";//5状态名称
tmsUltraGridList.DisplayLayout.Bands[0].Columns[6].Header.Caption = "需求描述";//6需求描述
tmsUltraGridList.DisplayLayout.Bands[0].Columns[7].Hidden = true;//7部门ID
tmsUltraGridList.DisplayLayout.Bands[0].Columns[8].Header.Caption = "提出部门";//8部门名称
tmsUltraGridList.DisplayLayout.Bands[0].Columns[9].Hidden = true;//9提出ID
tmsUltraGridList.DisplayLayout.Bands[0].Columns[10].Header.Caption = "提出人";//10提出人姓名
tmsUltraGridList.DisplayLayout.Bands[0].Columns[11].Header.Caption = "提出时间";//11提出时间
tmsUltraGridList.DisplayLayout.Bands[0].Columns[12].Header.Caption = "计划开始时间";//12计划开始时间
tmsUltraGridList.DisplayLayout.Bands[0].Columns[13].Header.Caption = "计划结束时间";//13计划结束时间
tmsUltraGridList.DisplayLayout.Bands[0].Columns[14].Hidden = true;//14责任人ID
tmsUltraGridList.DisplayLayout.Bands[0].Columns[15].Header.Caption = "责任人";//15责任人
tmsUltraGridList.DisplayLayout.Bands[0].Columns[16].Header.Caption = "分析计划开始时间";//16分析计划开始时间
tmsUltraGridList.DisplayLayout.Bands[0].Columns[17].Header.Caption = "分析计划完成时间";//17分析计划开始时间
tmsUltraGridList.DisplayLayout.Bands[0].Columns[18].Header.Caption = "分析实际开始时间";//18分析计划开始时间
tmsUltraGridList.DisplayLayout.Bands[0].Columns[19].Header.Caption = "分析实际完成时间";//19分析计划开始时间
//第二层表数据
tmsUltraGridList.DisplayLayout.Bands[1].Columns[0].Hidden = true;//0隐藏RequirementID
tmsUltraGridList.DisplayLayout.Bands[1].Columns[1].Hidden = true;//0隐藏ProjectID
tmsUltraGridList.DisplayLayout.Bands[1].Columns[2].Header.Caption = "功能点编号";
tmsUltraGridList.DisplayLayout.Bands[1].Columns[3].Header.Caption = "功能点描述";
tmsUltraGridList.DisplayLayout.Bands[1].Columns[4].Header.Caption = "开发责任人";
tmsUltraGridList.DisplayLayout.Bands[1].Columns[5].Header.Caption = "开发状态";
tmsUltraGridList.DisplayLayout.Bands[1].Columns[6].Header.Caption = "计划开始时间";
tmsUltraGridList.DisplayLayout.Bands[1].Columns[7].Header.Caption = "计划结束时间";
tmsUltraGridList.DisplayLayout.Bands[1].Columns[8].Header.Caption = "实际开始时间";
tmsUltraGridList.DisplayLayout.Bands[1].Columns[9].Header.Caption = "实际结束时间";
tmsUltraGridList.DisplayLayout.Bands[1].Columns[10].Header.Caption = "用例责任人";
tmsUltraGridList.DisplayLayout.Bands[1].Columns[11].Header.Caption = "用例状态";
tmsUltraGridList.DisplayLayout.Bands[1].Columns[12].Header.Caption = "计划开始时间";
tmsUltraGridList.DisplayLayout.Bands[1].Columns[13].Header.Caption = "计划结束时间";
tmsUltraGridList.DisplayLayout.Bands[1].Columns[14].Header.Caption = "实际开始时间";
tmsUltraGridList.DisplayLayout.Bands[1].Columns[15].Header.Caption = "实际结束时间";
//第三层表数据
tmsUltraGridList.DisplayLayout.Bands[2].Columns[0].Hidden = true;//0隐藏RequirementID
tmsUltraGridList.DisplayLayout.Bands[2].Columns[1].Header.Caption = "用例编号";
tmsUltraGridList.DisplayLayout.Bands[2].Columns[2].Header.Caption = "测试任务";
tmsUltraGridList.DisplayLayout.Bands[2].Columns[3].Header.Caption = "测试责任人";
tmsUltraGridList.DisplayLayout.Bands[2].Columns[4].Header.Caption = "测试状态";
tmsUltraGridList.DisplayLayout.Bands[2].Columns[5].Header.Caption = "测试结果";
tmsUltraGridList.DisplayLayout.Bands[2].Columns[6].Header.Caption = "计划开始时间";
tmsUltraGridList.DisplayLayout.Bands[2].Columns[7].Header.Caption = "计划结束时间";
tmsUltraGridList.DisplayLayout.Bands[2].Columns[8].Hidden = true;
tmsUltraGridList.DisplayLayout.Bands[2].Columns[9].Header.Caption = "实际结束时间";
#endregion
#region 需求追溯表
/// <summary>
/// 需求追溯表第一层
/// </summary>
/// <param name="itemID">系统ID</param>
/// <param name="state">状态ID</param>
/// <param name="departmentID">部门ID</param>
/// <param name="proposedBeginTime">提出完成时间开头</param>
/// <param name="proposedEndTime">提出完成时间结束</param>
/// <param name="proposedFlag">是否选中提出完成时间查询</param>
/// <param name="planBeginTime">计划完成时间开头</param>
/// <param name="planEndTime">计划完成时间结束</param>
/// <param name="planPlanFlag">是否选中计划完成时间查询</param>
/// <param name="beginTime">实际完成时间开头</param>
/// <param name="endTime">实际完成时间结束</param>
/// <param name="tFlag">是否选中实际完成时间查询</param>
/// <returns></returns>
public DataSet GetReportTrace(int itemID, int state, int departmentID, DateTime proposedBeginTime, DateTime proposedEndTime, bool proposedFlag, DateTime planBeginTime, DateTime planEndTime, bool planPlanFlag, DateTime beginTime, DateTime endTime, bool tFlag)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(" select R.RequirementID,TT.ItemID,TT.ItemCode,R.RequirementCode,R.State,TS.StateName,R.RequirementDescription,");
strSql.Append(" R.DepartmentID,TD.DepartmentName,R.ProposedByID,TU.UserName,R.ProposedTime,R.PlanBeginTime,R.PlanEndTime,");
strSql.Append(" R.ResponsibilityPersonID,TUP.UserName as TUPUserName,TRA.PlanBeginTime as TRAPlanBeginTime,");
strSql.Append(" TRA.PlanEndTime as TRAPlanEndTime,TRA.ActualBeginTime as TRAActualBeginTime,");
strSql.Append(" R.ActualEndTime as TRAActualEndTime");
strSql.Append(" from TM_Requirement R");
strSql.Append(" left join TM_RequirementAnalysis TRA on R.RequirementID=TRA.RequirementID ");
strSql.Append(" left join (select itemID,ItemCode from TM_Item) TT on R.ItemID=TT.ItemID");
strSql.Append(" left join (select StateID,StateName from TM_State where StatetypeID=1) TS on R.State=TS.StateID");
strSql.Append(" left join (select DepartmentID,DepartmentName from TM_Department) TD on R.DepartmentID=TD.DepartmentID");
strSql.Append(" left join (select LoginID,UserName from TM_User) TU on R.ProposedByID=TU.LoginID");
strSql.Append(" left join (select LoginID,UserName from TM_User) TUP on R.ResponsibilityPersonID=TUP.LoginID");
strSql.Append(" Where 1=1");
if (itemID != -1)
{
strSql.Append(" and TT.ItemID=" + itemID);
}
if (state != -1)
{
strSql.Append(" and R.State=" + state);
}
if (departmentID != -1)
{
strSql.Append(" and R.DepartmentID=" + departmentID);
}
if (proposedFlag)
{
strSql.Append(" and R.ProposedTime Between '" + proposedBeginTime + "' And '" + proposedEndTime + "'");
}
if (planPlanFlag)
{
strSql.Append(" and R.PlanEndTime Between '" + planBeginTime + "' And '" + planEndTime + "'");
}
if (tFlag)
{
strSql.Append(" and R.ActualEndTime Between '" + beginTime + "' And '" + endTime + "'");
}
List<Model.ReportTraceModel> modelList = new List<TMS.Model.ReportTraceModel>();
DataSet ds = DbHelperSQL.Query(strSql.ToString());
return ds;
}
/// <summary>
/// 获取第二层得到开发任务的实体集
/// </summary>
/// <param name="ProjectID">流水号 -1为所有</param>
/// <param name="D_State">开发的状态 -1 为所有</param>
/// <param name="D_Person">开发责任人 为''所有</param>
/// <param name="D_DistributionByID">开发分配责任人为''所有</param>
/// <param name="T_State">测试状态 -1为所有</param>
/// <param name="T_Person">测试用例责任人为''所有</param>
/// <param name="T_DistributionByID">测试用例分配责任人为''所有</param>
/// <param name="InsertByID">操作责任人为''所有</param>
/// <param name="ITemID">系统(项目)ID -1为所有</param>
/// <returns></returns>
public DataSet GetReportProject()
{
List<SqlParameter> parameterList = new List<SqlParameter>();
StringBuilder strSql = new StringBuilder();
strSql.Append("select RequirementID,ProjectID,ProjectCode,ProgramDescription,D_PersonName,D_StateName,D_PlanBeginTime,D_PlanEndTime,D_ActualBeginTime,D_ActualEndTime,T_PersonName,T_StateName,T_PlanBeginTime,T_PlanEndTime,T_ActualBeginTime,T_ActualEndTime from vwProject ");
strSql.Append(" where 1=1 ");
DataSet ds = DbHelperSQL.Query(strSql.ToString());
return ds;
}
/// <summary>
/// 获取第三层表结果中的测试任务
/// </summary>
/// <returns></returns>
public DataSet GetReportTestUseCase()
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select ProjectID,TestUseCaseCode,CaseTitle,ResponsibleByName,StateName,TestResult,PlanBeginTime,PlanEndTime,InsertTime,EndTime from vwTestTask ");
strSql.Append(" where 1=1 ");
DataSet ds = DbHelperSQL.Query(strSql.ToString());
return ds;
}
#endregion
private void tspBtnExportExcel_Click(object sender, EventArgs e)
{
SaveFileDialog sf = new SaveFileDialog();
sf.InitialDirectory = "C:";
sf.Filter = "Excel2007(*.xlsx)|*.xlsx|Excel2003(*.xls)|*.xls";
sf.FilterIndex = 2;
if (sf.ShowDialog() == DialogResult.OK)
{
// DO YOUR THINGS
string saveName = sf.FileName;
ultraGridExcelExporter1.Export(tmsUltraGrid1, saveName);
}
}
来源：（http://www.szemba.cn 深圳MBA）
浙公网安备 33010602011771号