//上面的则是执行的时候需要用到的一些内容,但它不是公用的方法,所以只是看看里面的参数的传值问题,不用考虑太多的内容问题
/// <summary>
/// 获取需要加载的 拟 一级项目信息
/// </summary>
/// <returns></returns>
private DataSet GetDataTable(string ProjectId)
{
DataSet ds = new DataSet();
string conditionField = string.Empty;
string conditionSql = string.Empty;
//string ProjectId = this.hiProjectID.Value;
//如果显示本级信息
if (IsShowSelf)
{
conditionField = "BudgetProjectName";
}
else
{
conditionField = "ParentProjectName";
}
//为了避免存在相同名称的预算项目名称,以父级区分一下(但还存在问题 如果父级名称也一样,会查出多条数据)
if (!string.IsNullOrEmpty(ParentBudgetProjectName))
{
conditionSql += string.Format(" AND ParentProjectName='{0}'", ParentBudgetProjectName);
}
string sqlSelect = string.Format(@"DECLARE @FirstBudgetProjectEntityID nvarchar(36);
DECLARE @Count INT
SELECT @Count=COUNT(*) FROM BMIS_ProjectBudgetYearForm WHERE ProjectEntityId='{0}'
IF @Count>0
BEGIN
---查询项目对应的一级预算项目应该对应的执行ID(数据表中没有此数据)
SET @FirstBudgetProjectEntityID=(
SELECT TOP 1(FirstParentBudgetProjectExecuteEntityID) FROM table1 WHERE ProjectEntityId='{0}')
SELECT BudgetProjectExecuteEntityID FROM USV_ProjectBudgetExe WHERE {3} IN ({1}) AND ProjectLevel='{2}' AND FirstParentBudgetProjectExecuteEntityID=@FirstBudgetProjectEntityID {4}
SELECT @FirstBudgetProjectEntityID
SELECT *,ParentExeID AS ParentID,NULL AS MonthExeValue,NULL YExeValue,NULL YAllExeValue FROM USV_ProjectBudgetExe WHEREFirstParentBudgetProjectExecuteEntityID=@FirstBudgetProjectEntityID
----查询执行的月度信息
END
ELSE
BEGIN
SELECT TOP 0(BudgetProjectExecuteEntityID) FROM USV_ProjectBudgetExe
SELECT @FirstBudgetProjectEntityID
SELECT TOP 0*,NULL as ParentID,NULL AS MonthExeValue,NULL YExeValue,NULL YAllExeValue FROM USV_ProjectBudgetExe
END
", ProjectId, BudgetProjectName, ProjectLevel, conditionField, conditionSql);
try
{
PortalDB.LoadDataSet(CommandType.Text, sqlSelect, ds, new string[] { "ShowBudgetInfo", "FirstBudgetProjectEntity", "ExeData" });
}
catch (Exception ex)
{
PortalLogging.HandleException(ex);
ShowAlertMessage(ex.Message.ToString());
}
return ds;
}
//下面为公用的一些代码的内容
/// <summary>
/// 获取需要绑定的datatable dt1 dt2
/// </summary>
/// <param name="projectId"></param>
/// <returns></returns>
public DataTable GetBindDatatable(string ProjectId)
{
DataRow rowYear = InitYearInfo();
DataTable dt = new DataTable();
DataSet dsProjectInfo = GetDataTable(ProjectId);
string budgetExeID = string.Empty;
DataTable dtShowProjectInfo = dsProjectInfo.Tables["ShowBudgetInfo"];
DataTable dtFirst = dsProjectInfo.Tables["FirstBudgetProjectEntity"];
DataTable dtExeData = dsProjectInfo.Tables["ExeData"];
string parentExeID = string.Empty;
foreach (DataRow row in dtShowProjectInfo.Rows)
{
parentExeID += string.Format("'{0}',", row["BudgetProjectExecuteEntityID"].ToString());
budgetExeID += GetLowerBudgetInfoID(row["BudgetProjectExecuteEntityID"].ToString(), "'" + row["BudgetProjectExecuteEntityID"].ToString() + "',", dtExeData);
}
if (!string.IsNullOrEmpty(budgetExeID))
{
budgetExeID = budgetExeID.TrimEnd(',');
parentExeID = parentExeID.TrimEnd(',');
hiParentExeID.Value = parentExeID;
//由于查询的预算科目不一定是一级预算科目,即ParentExeID 有不为NULL的情况
//树 的一级的ParenExeID必须为NULL,手动将ParentExeID不为NULL的数据置为NULL
string sqlSelect = string.Format(@"
----项目预算信息
SELECT Temp.*,NULL AS Project2ExeValue,NULL AS Rate,
----本年执行数
ISNULL((SELECT SUM(ISNULL(MExeValue,0)) FROM table2 D WHERE D.BudgetProjectExecuteEntityID=Temp.BudgetProjectExecuteEntityID AND [Year]='{2}'),0) AS YExeValue
FROM (
SELECT *,NULL AS ParentID,ISNULL(Y,1) AS TotalValue FROM table3 WHERE BudgetProjectExecuteEntityID IN ({1})
UNION
SELECT *,ParentExeID AS ParentID,ISNULL(Y,1) AS TotalValue FROM table4 WHERE BudgetProjectExecuteEntityID IN ({0}) AND BudgetProjectExecuteEntityID NOT IN ({1})) Temp ORDER BY SortIndex
", budgetExeID, parentExeID, ddlYear.SelectedValue);
dt = PortalDB.ExecuteDataSet(CommandType.Text, sqlSelect).Tables[0];
hiFirstExeID.Value = dtFirst.Rows[0][0].ToString();
}
return dt;
}
//以下为绑定表1 dt1和表2 dt2
/// <summary>
/// 绑定项目费用科目信息
/// </summary>
public void ProjectDataBind()
{
//暂未用到
DataRow rowYear = InitYearInfo();
DataTable dt1 = GetBindDatatable(hiProjectID.Value);
DataTable dt2 = GetBindDatatable(hiProjectID1.Value);
//尤其是这里,里面的遍历的代码不太懂,所以应该仔细的看看
foreach (DataRow dr in dt1.Rows)
{
object budgetProjectName = dr["BudgetProjectName"];
DataRow[] selRows = dt2.Select(string.Format("BudgetProjectName='{0}'", budgetProjectName));
if (selRows.Length > 0)
{
dr["Project2ExeValue"] = selRows[0]["YExeValue"];
}
Decimal Project2Rate = Convert.ToDecimal(dr["YExeValue"]);
Decimal rate1 = Convert.ToDecimal(dr["Project2ExeValue"]);
object rate = dr["Rate"];
if (Project2Rate != 0)
{
rate = (rate1 - Project2Rate) / Project2Rate;
Convert.ToDecimal(rate);
}
else
{
}
dr["Rate"] = rate;
}
tgvProjectBudgetProject.UseDefaultDataSource = true;
tgvProjectBudgetProject.DataSource = dt1;
tgvProjectBudgetProject.PagingData();
}