static List <GlData> CalculateRollupData(int accountRollupId, int companyId, int reportingId, int yearId, List <GlData> leafDataMembers, List <SegmentMember> segmentMembers)
{
double aggrValue;
double currMemValue;
int signatLevel;
List <int> leafCompanyids = new List <int>();
List <int> leafMonthids = new List <int>();
List <int> leafRepportingids = new List <int>();
List <GlData> glDataMembers = new List <GlData>();
GlData currGlDaaMember = new GlData();
// Get Current segment member of rollup
SegmentMember currSegMember = segmentMembers.Find(t => t.Id == accountRollupId);
//Get account Rollup Level val
int accountRollupLevelVal = currSegMember.LevelVal;
//Get all leaf members of account under the rollup
int accountRollupLeftVal = currSegMember.LeftVal;
int accountRollupRightVal = currSegMember.RightVal;
List <SegmentMember> leafMembers = segmentMembers.FindAll(t => t.LeftVal >= accountRollupLeftVal && t.RightVal <= accountRollupRightVal && t.MemberType == 4);
Console.WriteLine("Populating Company Leaf members Start Time : " + DateTime.Now);
// Get all leaf members of company
leafCompanyids = GetLeafMembers(companyId, "Company");
Console.WriteLine("Populating Company Leaf members End Time : " + DateTime.Now);
// Get all month ids based on year
Console.WriteLine("Populating Time Leaf members Start Time : " + DateTime.Now);
leafMonthids = GetLeafMembers(yearId, "time");
Console.WriteLine("Populating Time Leaf members End Time : " + DateTime.Now);
// Get all reporting ids based on rollup
Console.WriteLine("Populating Time Reporting members Start Time : " + DateTime.Now);
leafRepportingids = GetLeafMembers(reportingId, "reporting");
Console.WriteLine("Populating Time Reporting members End Time : " + DateTime.Now);
foreach (int reportingid in leafRepportingids)
{
Console.WriteLine("Aggregating data at reporting id: " + reportingid + " Start Time : " + DateTime.Now);
foreach (int compId in leafCompanyids)
{
foreach (int monthId in leafMonthids)
{
aggrValue = 0.0;
signatLevel = 1;
// loop through all leaf account members and aggregate values
foreach (SegmentMember segMemb in leafMembers)
{
currMemValue = 0.0;
currGlDaaMember = new GlData();
currGlDaaMember = leafDataMembers.Find(t => t.ReportingId == reportingid && t.CompanyId == compId && t.MonthId == monthId && t.AccountId == segMemb.Id);
if (currGlDaaMember != null)
{
signatLevel = segMemb.SignValues.Find(t => t.IntLevel == accountRollupLevelVal).Sign;
currMemValue = currGlDaaMember.YTDAmount;
aggrValue = aggrValue + (currMemValue * signatLevel);
}
}
if (aggrValue != 0.0)
{
var glDataMember = new GlData()
{
AccountId = Convert.ToInt32(accountRollupId),
CompanyId = Convert.ToInt32(compId),
MonthId = Convert.ToInt32(monthId),
ReportingId = Convert.ToInt32(reportingid),
YTDAmount = Convert.ToDouble(aggrValue)
};
glDataMembers.Add(glDataMember);
}
}
}
Console.WriteLine("Aggregating data at reporting id: " + reportingid + " End Time : " + DateTime.Now);
Console.WriteLine("Aggregating data count : " + glDataMembers.Count());
}
return(glDataMembers);
}
static List <GlData> ReadData(int accountRollupId, int companyId, int reportingId, int yearId)
{
List <GlData> glDataMembers = new List <GlData>();
var _params = new List <SqlParameter>();
_params.Add(new SqlParameter("@accountRollupId", accountRollupId));
_params.Add(new SqlParameter("@companyId", companyId));
_params.Add(new SqlParameter("@reportingId", reportingId));
_params.Add(new SqlParameter("@yearId", yearId));
SqlParameter[] paramList = _params != null?_params.ToArray() : null;
string strQuery = @"SELECT
SEG1_ID, SEG2_ID , TIME_ID , REPORTING_ID , SUM(YTD) YTD
FROM
FACT_GL FGL,
(
SELECT
DS.REF_IDX
FROM DIM_SEG2 DS,
( SELECT LEFT_VAL, RIGHT_VAL
FROM DIM_SEG2
WHERE IDX = @companyId
) DS_LVL
WHERE
DS.LEFT_VAL >= DS_LVL.LEFT_VAL AND DS.RIGHT_VAL <= DS_LVL.RIGHT_VAL AND
DS.TYPE = 4 AND ROLLUP_ID = 1
)SEG2_IDS ,
(
SELECT LEVEL2_ID FROM DIM_REPORTING WHERE LEVEL1_ID IN (@reportingId) OR LEVEL2_ID IN (@reportingId)
) REP_IDS ,
(
SELECT
DS.REF_IDX
FROM DIM_SEG1 DS,
( SELECT LEFT_VAL, RIGHT_VAL
FROM DIM_SEG1
WHERE IDX = @accountRollupId
) DS_LVL
WHERE
DS.LEFT_VAL >= DS_LVL.LEFT_VAL AND DS.RIGHT_VAL <= DS_LVL.RIGHT_VAL AND
DS.TYPE = 4 AND ROLLUP_ID = 1
)SEG1_IDS ,
(
SELECT MONTH_ID FROM DIM_TIME WHERE YEAR_ID IN (@yearId) OR QTR_ID IN (@yearId) OR MONTH_ID IN (@yearId)
) TIME_IDS
WHERE
FGL.SCENARIO_ID = 1
AND FGL.SEG2_ID = SEG2_IDS.REF_IDX
AND FGL.REPORTING_ID = REP_IDS.LEVEL2_ID
AND FGL.SEG1_ID = SEG1_IDS.REF_IDX
AND FGL.TIME_ID = TIME_IDS.MONTH_ID
GROUP BY
SEG1_ID, SEG2_ID , TIME_ID , REPORTING_ID " ;
using (SqlConnection con = new SqlConnection(SQLConnectionString))
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.CommandText = strQuery;
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.Text;
cmd.Parameters.AddRange(paramList);
con.Open();
using (var irdr = cmd.ExecuteReader())
{
if (irdr.HasRows)
{
while (irdr.Read())
{
var glDataMember = new GlData()
{
AccountId = Convert.ToInt32(irdr["SEG1_ID"]),
CompanyId = Convert.ToInt32(irdr["SEG2_ID"]),
MonthId = Convert.ToInt32(irdr["TIME_ID"]),
ReportingId = Convert.ToInt32(irdr["REPORTING_ID"]),
YTDAmount = Convert.ToDouble(irdr["YTD"])
};
glDataMembers.Add(glDataMember);
}
}
}
}
}
return(glDataMembers);
}