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); }