public void GetRowForRollup(ref DSForecastRprt.EngrInfoRow eRow) { decimal tmpftcHrs, tmpftcAmnt; if (eRow.FTCHrs < -1000) { tmpftcHrs = eRow.BudgetHrs; } else { tmpftcHrs = eRow.FTCHrs; } if (eRow.FTCAmnt < -1000) { tmpftcAmnt = eRow.BudgetDlrs; } else { tmpftcAmnt = eRow.FTCAmnt; } if (tmpftcHrs < eRow.ActualTime) { tmpftcHrs = eRow.ActualTime; } if (tmpftcAmnt < eRow.ActualAmnt) { tmpftcAmnt = eRow.ActualAmnt; } eRow.FTCHrs = tmpftcHrs; eRow.FTCAmnt = tmpftcAmnt; }
public DSForecastRprt LoadReportForProjectRollup(string project, int rprtCase) { DataSet ds; RevSol.RSConnection cnn; SqlCommand cmd; SqlParameter prm; SqlDataReader dr; DSForecastRprt dsFor; cnn = new RevSol.RSConnection("CR"); cmd = new SqlCommand("spProject_GetListByMaster", cnn.GetConnection()); cmd.CommandType = CommandType.StoredProcedure; prm = cmd.Parameters.Add("@MasterNum", SqlDbType.VarChar, 50); prm.Value = project; dr = cmd.ExecuteReader(); dsFor = new DSForecastRprt(); string sProject, sDescription, sCustomer, sLocation, sManager, sBillType; var project_info = GetProjectInfo(project); ds = GetCRForProject(project, rprtCase); sProject = ""; sDescription = ""; sCustomer = ""; sLocation = ""; sManager = ""; sBillType = ""; foreach (DataRow d in project_info.Rows) { sProject = d["Project"].ToString(); sDescription = d["Description"].ToString(); sCustomer = d["Customer"].ToString(); sLocation = d["Location"].ToString(); sManager = d["Manager"].ToString(); sBillType = d["BillType"].ToString(); } while (dr.Read()) { ds = GetCRForProject(dr["Number"].ToString(), rprtCase); foreach (DataRow d in ds.Tables[0].Rows) { DSForecastRprt.EngrInfoRow eir = dsFor.EngrInfo.NewEngrInfoRow(); eir.Project = sProject; eir.Description = sDescription; eir.Customer = sCustomer; eir.Location = sLocation; eir.BudgetGroup = d["BudgetGroup"].ToString(); eir.AcctGroup = d["AcctGroup"].ToString(); eir.Manager = sManager; eir.BillType = sBillType; try { eir.BudgetDlrs = Convert.ToDecimal(d["BudgetDlrs"]); eir.BudgetHrs = Convert.ToDecimal(d["BudgetHrs"]); eir.ActualTime = Convert.ToDecimal(d["ActualTime"]); eir.ActualAmnt = Convert.ToDecimal(d["ActualAmnt"]); eir.JSBudgetHrs = Convert.ToDecimal(d["JSBudgetHrs"]); eir.RemainingHrs = Convert.ToDecimal(d["RemainingHrs"]); eir.JSLastUpdated = Convert.ToDateTime(d["JSLastUpdated"]); eir.EarnedHrs = Convert.ToDecimal(d["EarnedHrs"]); eir.ProjectedHrs = Convert.ToDecimal(d["ProjectedHrs"]); eir.ForecastHrs = Convert.ToDecimal(d["ForecastHrs"]); eir.FTCHrs = Convert.ToDecimal(d["FTCHrs"]); eir.FTCAmnt = Convert.ToDecimal(d["FTCAmnt"]); eir.FTCUpdate = Convert.ToDateTime(d["FTCUpdate"]); } catch { } dsFor.EngrInfo.AddEngrInfoRow(eir); } foreach (DataRow d in ds.Tables[1].Rows) { DSForecastRprt.Table1Row t1r = dsFor.Table1.NewTable1Row(); t1r.Project = sProject; t1r.AcctNumber = d["AcctNumber"].ToString(); t1r.AcctGroup = d["AcctGroup"].ToString(); t1r.AcctNumberGroup = d["AcctNumberGroup"].ToString(); t1r.budget = Convert.ToDecimal(d["budget"]); t1r.costs = Convert.ToDecimal(d["costs"]); t1r.ForecastHrs = Convert.ToDecimal(d["ForecastHrs"]); t1r.ForecastAmnt = Convert.ToDecimal(d["ForecastAmnt"]); t1r.ftc = Convert.ToDecimal(d["ftc"]); dsFor.Table1.AddTable1Row(t1r); } } dr.Close(); DSForecastRprt rprtDs = new DSForecastRprt(); var groups = new List <string>(); foreach (DataRow row in dsFor.EngrInfo.Rows) { groups.Add(row["BudgetGroup"].ToString()); } groups = groups.Distinct().OrderBy(x => x).ToList(); foreach (var group in groups) { DSForecastRprt.EngrInfoRow eRow = rprtDs.EngrInfo.NewEngrInfoRow(); DataRow[] dInfos = dsFor.EngrInfo.Select("BudgetGroup = '" + group + "'"); this.GroupRows(dInfos, eRow); rprtDs.EngrInfo.AddEngrInfoRow(eRow); } var exp_groups = new List <string>(); foreach (DataRow row in dsFor.Table1.Rows) { exp_groups.Add(row["AcctNumber"].ToString()); } exp_groups = exp_groups.Distinct().OrderBy(x => x).ToList(); foreach (var exp_group in exp_groups) { DSForecastRprt.Table1Row tblRow = rprtDs.Table1.NewTable1Row(); DataRow[] dInfos = dsFor.Table1.Select("AcctNumber = '" + exp_group + "'"); this.GroupExpRows(sProject, dInfos, tblRow); rprtDs.Table1.AddTable1Row(tblRow); } return(rprtDs); }
private void GroupRows(DataRow[] dInfos, DSForecastRprt.EngrInfoRow eRow) { decimal dBudgetDlrs, dBudgetHrs, dActualTime, dActualAmnt, dJSBudgetHrs, dRemainingHrs; DateTime dtJSLastUpdated; decimal dEarnedHrs, dProjectedHrs, dForecastHrs, dFTCHrs, dFTCAmnt; DateTime dtFTCUpdate; dBudgetDlrs = 0; dBudgetHrs = 0; dActualTime = 0; dActualAmnt = 0; dJSBudgetHrs = 0; dRemainingHrs = 0; dtJSLastUpdated = new DateTime(1901, 1, 1); dEarnedHrs = 0; dProjectedHrs = 0; dForecastHrs = 0; dFTCHrs = 0; dFTCAmnt = 0; dtFTCUpdate = new DateTime(1901, 1, 1); eRow.Manager = ""; eRow.BillType = ""; eRow.BudgetDlrs = 0; eRow.BudgetHrs = 0; eRow.ActualTime = 0; eRow.ActualAmnt = 0; eRow.JSBudgetHrs = 0; eRow.RemainingHrs = 0; eRow.JSLastUpdated = new DateTime(1901, 1, 1); eRow.EarnedHrs = 0; eRow.ProjectedHrs = 0; eRow.ForecastHrs = 0; eRow.FTCHrs = 0; eRow.FTCAmnt = 0; eRow.FTCUpdate = new DateTime(1901, 1, 1); foreach (DataRow dRow in dInfos) { var sProject = dRow["Project"].ToString(); var sDescription = dRow["Description"].ToString(); var sCustomer = dRow["Customer"].ToString(); var sLocation = dRow["Location"].ToString(); var sBudgetGroup = dRow["BudgetGroup"].ToString(); var sAcctGroup = dRow["AcctGroup"].ToString(); var sManager = dRow["Manager"].ToString(); var sBillType = dRow["BillType"].ToString(); if (dRow["BudgetDlrs"] == DBNull.Value) { dBudgetDlrs = 0; } else { dBudgetDlrs = Convert.ToDecimal(dRow["BudgetDlrs"]); } if (dRow["BudgetHrs"] == DBNull.Value) { dBudgetHrs = 0; } else { dBudgetHrs = Math.Round(Convert.ToDecimal(dRow["BudgetHrs"]), 0, MidpointRounding.AwayFromZero); } if (dRow["ActualTime"] == DBNull.Value) { dActualTime = 0; } else { dActualTime = Math.Round(Convert.ToDecimal(dRow["ActualTime"]), 0, MidpointRounding.AwayFromZero); } if (dRow["ActualAmnt"] == DBNull.Value) { dActualAmnt = 0; } else { dActualAmnt = Convert.ToDecimal(dRow["ActualAmnt"]); } if (dRow["JSBudgetHrs"] == DBNull.Value) { dJSBudgetHrs = 0; } else { dJSBudgetHrs = Convert.ToDecimal(dRow["JSBudgetHrs"]); } if (dRow["RemainingHrs"] == DBNull.Value) { dRemainingHrs = 0; } else { dRemainingHrs = Convert.ToDecimal(dRow["RemainingHrs"]); } if (dRow["JSLastUpdated"] == DBNull.Value) { dtJSLastUpdated = new DateTime(1901, 1, 1); } else { dtJSLastUpdated = Convert.ToDateTime(dRow["JSLastUpdated"]); } if (dRow["EarnedHrs"] == DBNull.Value) { dEarnedHrs = 0; } else { dEarnedHrs = Convert.ToDecimal(dRow["EarnedHrs"]); } if (dRow["ProjectedHrs"] == DBNull.Value) { dProjectedHrs = 0; } else { dProjectedHrs = Convert.ToDecimal(dRow["ProjectedHrs"]); } if (dRow["ForecastHrs"] == DBNull.Value) { dForecastHrs = 0; } else { dForecastHrs = Convert.ToDecimal(dRow["ForecastHrs"]); } if (dRow["FTCHrs"] == DBNull.Value) { dFTCHrs = 0; } else { dFTCHrs = Math.Round(Convert.ToDecimal(dRow["FTCHrs"]), 0, MidpointRounding.AwayFromZero); } if (dRow["FTCAmnt"] == DBNull.Value) { dFTCAmnt = 0; } else { dFTCAmnt = Convert.ToDecimal(dRow["FTCAmnt"]); } if (dRow["FTCUpdate"] == DBNull.Value) { dtFTCUpdate = new DateTime(1901, 1, 1); } else { dtFTCUpdate = Convert.ToDateTime(dRow["FTCUpdate"]); } eRow.Project = sProject; eRow.Description = sDescription; eRow.Customer = sCustomer; eRow.Location = sLocation; eRow.BudgetGroup = sBudgetGroup; eRow.AcctGroup = sAcctGroup; eRow.Manager = sManager; eRow.BillType = sBillType; eRow.BudgetDlrs += dBudgetDlrs; eRow.BudgetHrs += dBudgetHrs; eRow.ActualTime += dActualTime; eRow.ActualAmnt += dActualAmnt; eRow.JSBudgetHrs += dJSBudgetHrs; eRow.RemainingHrs += dRemainingHrs; eRow.JSLastUpdated = dtJSLastUpdated; eRow.EarnedHrs += dEarnedHrs; eRow.ProjectedHrs += dProjectedHrs; eRow.ForecastHrs += dForecastHrs; GetRowForRollup(dBudgetHrs, dBudgetDlrs, dActualTime, dActualAmnt, dForecastHrs, ref dFTCHrs, ref dFTCAmnt); eRow.FTCHrs += dFTCHrs; eRow.FTCAmnt += dFTCAmnt; eRow.FTCUpdate = dtFTCUpdate; } }