static List <SegmentMember> PopulateLevelSigns(List <SegmentMember> segMembers)
{
var lineageIndexVal = new Dictionary <int, int>();
int lineIndex;
string lineage = "";
int segLevelVal;
List <string> lineageVal;
int signVal = 1;
List <SignValue> signValues;
SegmentMember tempSegmember = new SegmentMember();
foreach (SegmentMember segmember in segMembers.Where(x => x.MemberType == 4))
{
signValues = new List <SignValue>();
lineage = segmember.Lineage;
if (lineageSignValues.ContainsKey(lineage))
{
signValues = lineageSignValues[lineage];
}
else
{
lineageIndexVal = new Dictionary <int, int>();
lineageVal = lineage.Split('/').ToList();
lineIndex = 1;
// Populate Lineage Index
foreach (string linVal in lineageVal)
{
if (linVal.Trim() != "")
{
lineageIndexVal.Add(Convert.ToInt32(lineIndex), Convert.ToInt32(linVal));
lineIndex++;
}
}
segLevelVal = segmember.LevelVal - 1;
signVal = 1;
// populate sign at each level
while (segLevelVal >= 1)
{
signVal = signVal * Convert.ToInt32(ReturnSingValueByRollupOperator(idRollup[lineageIndexVal[segLevelVal]]));
var signValue = new SignValue()
{
IntLevel = Convert.ToInt32(segLevelVal),
Sign = Convert.ToInt32(signVal)
};
signValues.Add(signValue);
segLevelVal--;
}
lineageSignValues.Add(lineage, signValues);
}
segmember.SignValues = signValues;
}
return(segMembers);
}
static List <SegmentMember> PopulateSegmentMember()
{
List <SegmentMember> segmentMembers = new List <SegmentMember>();
string strQuery = "SELECT IDX,LABEL,PARENT_IDX,LEVEL_VAL,TYPE,LEFT_VAL,RIGHT_VAL,ROLLUP_OPERATOR,LINEAGE FROM DIM_SEG1 WHERE ROLLUP_ID = 1 ";
using (SqlConnection con = new SqlConnection(SQLConnectionString))
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.CommandText = strQuery;
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.Text;
con.Open();
using (var irdr = cmd.ExecuteReader())
{
if (irdr.HasRows)
{
while (irdr.Read())
{
var segmentMember = new SegmentMember()
{
Id = Convert.ToInt32(irdr["IDX"]),
Label = Convert.ToString(irdr["LABEL"]),
ParentId = Convert.ToInt32(irdr["PARENT_IDX"]),
LeftVal = Convert.ToInt32(irdr["LEFT_VAL"]),
RightVal = Convert.ToInt32(irdr["RIGHT_VAL"]),
LevelVal = Convert.ToInt32(irdr["LEVEL_VAL"]),
MemberType = Convert.ToInt32(irdr["TYPE"]),
RollupOperator = Convert.ToString(irdr["ROLLUP_OPERATOR"]),
Lineage = Convert.ToString(irdr["LINEAGE"])
};
if (Convert.ToInt32(irdr["TYPE"]) == 3)
{
idRollup.Add(Convert.ToInt32(irdr["IDX"]), Convert.ToString(irdr["ROLLUP_OPERATOR"]));
}
segmentMembers.Add(segmentMember);
}
}
}
}
}
return(segmentMembers);
}
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);
}