public void LoadReport(int lang, ref ReportVM result, ref DataTable dtOtherData, string reportType, ref DataTable rptds, ref ReportViewer Migration_Rport, ref Label NotificationLbl) { string[,] FixedVariables = new string[10, 2]; int ReportID = 0; if (HttpContext.Current.Request.QueryString["repId"] != null) { ReportID = Convert.ToInt16(HttpContext.Current.Request.QueryString["repId"]); } List <ConfigCenterVm> yearWindow = MgrConfigCenter.GetValue("", 1, -1, 8); result = MgrReports.GetReportsByID(ReportID, lang); if (result != null) { ChangingVaribleID = result.changingVariableID; RunningVariable = result.RunningVariableID; YearFrom = result.YearID; YearTo = result.YearTo; ThemeID = result.ThemeID; ThemeName = result.ThemeName; UnitScale = result.UnitScale; SubCodeVm sub; foreach (PropertyInfo col in result.GetType().GetProperties()) { if (("YearID" == col.Name && RunningVariable == "004")) { continue; } if (col.GetValue(result, null) != null) { if (!string.IsNullOrWhiteSpace(col.GetValue(result, null).ToString())) { if ( !"ReportID,ReportEnName,ReportArName,ReportFrName,ThemeID,ThemeType,UnitScale,UnitScaleAr,UnitScaleFr,ThemeName,ThemeNameAr,ThemeNameFr,RunningVariableID,YearTo,changingVariableID,RunningVariableName,changingVariableName" .Contains(col.Name)) { sub = MgrSubCode.GetSubCodeModel(col.GetValue(result, null).ToString(), lang); if (sub == null) { switch (lang) { case 1: lang = 3; break; case 2: lang = 1; break; case 3: lang = 1; break; } sub = MgrSubCode.GetSubCodeModel(col.GetValue(result, null).ToString(), lang); if (sub == null) { switch (lang) { case 1: lang = 2; break; case 2: lang = 3; break; case 3: lang = 2; break; } sub = MgrSubCode.GetSubCodeModel(col.GetValue(result, null).ToString(), lang); } } if (sub != null) { if (!dtOtherData.Columns.Contains("KeyName")) { dtOtherData.Columns.Add("KeyName"); dtOtherData.Columns.Add("KeyValue"); } DataRow dtRow = dtOtherData.NewRow(); if ((result.changingVariableID == "004" || result.RunningVariableID == "004") && col.Name.ToLower() == "yearid") { } else { dtRow[0] = sub.GeneralName + " : "; dtRow[1] = sub.Name; dtOtherData.Rows.Add(dtRow); FixedVariables[z, 0] = col.Name; FixedVariables[z, 1] = col.GetValue(result, null).ToString(); z++; } } // string Y = asd.Tables[0].Rows[0][i].ToString(); // string x = asd.Tables[0].Columns[i].ColumnName; } } } } z = 0; } HttpContext.Current.Session["dtOtherData"] = dtOtherData; SqlConnection con = new SqlConnection(ConnectionString12); SqlCommand cmd = new SqlCommand(); SqlCommand cmd1 = new SqlCommand(); cmd.Connection = con; cmd1.Connection = con; if (ChangingVaribleID != "") { string Query = "select Queryfrom,VariableID,VariableColumnName from VariablesMapping where VariableID= '" + ChangingVaribleID + "' or VariableID= '" + RunningVariable + "'"; string sqlstrg; //= "select * FROM dbo.FactStatisticalData INNER JOIN dbo.Reports ON dbo.FactStatisticalData.ReportID = dbo.Reports.ReportID left outer JOIN dbo.SubCode AS subEducation ON dbo.FactStatisticalData.EducationLevelID = subEducation.SubID and subEducation.GeneralID = '" + ChangingVaribleID + "' left outer JOIN dbo.SubCode AS subGender ON dbo.FactStatisticalData.GenderID = subGender.SubID and subGender.GeneralID = '002' WHERE (dbo.FactStatisticalData.ReportID = " + ReportID + ") order by subEducation.SubID Asc, subGender.SubID Asc"; cmd.CommandType = CommandType.Text; cmd.CommandText = Query; DataSet ds = new DataSet(); con.Open(); cmd.ExecuteNonQuery(); SqlDataAdapter adpt = new SqlDataAdapter(cmd); adpt.Fill(ds); StringBuilder fixedColumn = new StringBuilder( "YearID, GenderID, GovID, MaritalStatusID, EducationLevelID, AgeID, SectorID, CountryID, MonthID, NationailtyID, SchoolTypeID, " + "EconomicActivityID, CostID, GeographicalDistributionID, GovernoratesGroupID, GenderRatioID, EstablishmentID, UniversityID, FacultyID, InistitueID, DropOutID, " + "TeahcingPositionsID, WaterID, WaterProducerID, RoadID, VehicleID, TransportedItemID, CargoStatusTravelingID, TravelStatusID, CommodityID, CommodityGroupID, CaseTypeID, " + "CaseStatusID, AssociationsActivityID, CulturalServiceAssociationsID, SocialServiceAssociationsID, IssuedCapitalID, WaterPollutionIndicatorID, TransportingFacilityID, " + "PortNameID, AirPollutionID, AreaDateID, CropsSeasonID, liveStockID, OriginPlaceID, BorrowingPurposeID, AuthoritySurveillanceID, ServiceID, FisheryRegionID, FoodProductsID, " + "ChemicalProductID, PaperPrintingProductID, RubberPlasticProductID, TextileProductID, MetalicEngineeringElectricProductID, ConstrcutionsMaterialRefractoryProductID, " + "PetroluemNaturalGasProductID, TradeID, CountriesGroupID, ArrivalMethodID, HotelTypeID, RadioStationID, ProgramTypeID, SubjectTypeID, MuseumTypeID, IndustrySectionsID, " + "EducationalTypeID"); string queryYearWindow = !string.IsNullOrEmpty(yearWindow[0].Value) ? " convert(int, subcode.name)>=" + yearWindow[0].Value : " "; queryYearWindow += !string.IsNullOrEmpty(queryYearWindow) ? " and " : ""; queryYearWindow += !string.IsNullOrEmpty(yearWindow[1].Value) ? " convert(int, subcode.name )<=" + yearWindow[1].Value + " and " : " "; if (ds.Tables[0].Rows.Count == 2) { fixedColumn.Replace(ds.Tables[0].Rows[0][2] + ",", ""); fixedColumn.Replace(ds.Tables[0].Rows[1][2] + ",", ""); sqlstrg = " select distinct * " + "FROM dbo.FactStatisticalData " + "INNER JOIN dbo.Reports ON dbo.Reports.ReportID=" + ReportID + " and dbo.FactStatisticalData.ThemeID = dbo.Reports.ThemeID " + ds.Tables[0].Select("VariableID=" + ChangingVaribleID)[0][0] + lang + " " + ds.Tables[0].Select("VariableID=" + RunningVariable)[0][0] + lang + " left outer join dbo.subcode on FactStatisticalData.yearid=subcode.subid and subcode.generalid='004' and subcode.LanguageID=" + lang + "WHERE " + queryYearWindow + " (dbo.FactStatisticalData.ThemeID = " + ThemeID + ") and FactStatisticalData." + ds.Tables[0].Rows[0][2] + " is not null and FactStatisticalData." + ds.Tables[0].Rows[1][2] + " is not null "; } else { fixedColumn.Replace(ds.Tables[0].Rows[0][2] + ",", ""); sqlstrg = " select distinct * FROM dbo.FactStatisticalData " + "INNER JOIN dbo.Reports ON dbo.Reports.ReportID=" + ReportID + " and dbo.FactStatisticalData.ThemeID = dbo.Reports.ThemeID " + ds.Tables[0].Rows[0][0].ToString() + lang + " left outer join dbo.subcode on FactStatisticalData.yearid=subcode.subid and subcode.generalid='004' and subcode.LanguageID= " + lang + " WHERE " + queryYearWindow + " (dbo.FactStatisticalData.ThemeID = " + ThemeID + ") and FactStatisticalData." + ds.Tables[0].Rows[0][2] + " is not null "; } con.Close(); cmd.Dispose(); ds.Dispose(); adpt.Dispose(); for (int x = 0; x < 10; x++) { if (FixedVariables[x, 1] != null) { if (!FixedVariables[x, 1].Contains("00001")) { if (!string.IsNullOrEmpty(YearTo) && FixedVariables[x, 1].Substring(0, 3) == "004") { } else { sqlstrg = sqlstrg + "and FactStatisticalData." + FixedVariables[x, 0] + " = '" + FixedVariables[x, 1] + "'"; fixedColumn.Replace(FixedVariables[x, 0] + ",", ""); } } } } fixedColumn.Replace(",", " is null AND FactStatisticalData."); sqlstrg += "AND FactStatisticalData." + fixedColumn + " IS null"; cmd1.CommandType = CommandType.Text; cmd1.CommandText = sqlstrg; DataSet ds1 = new DataSet(); con.Open(); cmd1.ExecuteNonQuery(); SqlDataAdapter adpt1 = new SqlDataAdapter(cmd1); adpt1.Fill(ds1); if (ds1.Tables[0].Rows.Count != 0) { if (ds.Tables[0].Rows.Count != 2) { System.Data.DataColumn newColumn = new System.Data.DataColumn("Name1", typeof(System.String)); newColumn.DefaultValue = HttpContext.GetGlobalResourceObject("CommonControls", "F22_Value"); if (!ds1.Tables[0].Columns.Contains("Name1")) { ds1.Tables[0].Columns.Add(newColumn); } ; } HttpContext.Current.Session["result"] = result; HttpContext.Current.Session["ds1"] = ds1.Tables[0]; HttpContext.Current.Session["ThemeName"] = ThemeName; HttpContext.Current.Session["UnitScale"] = UnitScale; rptds = ds1.Tables[0]; } else { NotificationLbl.Text = "Sorry, there's no available data"; } } else { NotificationLbl.Text = "Sorry, there's no available data"; } }
public int Insert(ReportVM fixedVariables, int themeId, string changingVariable, string runningVariable, string changingValue, string runningValue, decimal value) { string changingVariableColumn = ""; string runningVariableColumn = ""; changingVariableColumn = Utils.VariableName(changingVariable.Substring(0, 3)); if (!string.IsNullOrEmpty(runningVariable)) { runningVariableColumn = Utils.VariableName(runningVariable.Substring(0, 3)); } FactStatisticalData item = new FactStatisticalData(); if (fixedVariables != null) { item.YearID = fixedVariables.YearID; foreach (var column in fixedVariables.GetType().GetProperties()) { if ( !"ReportID,ThemeNameAr,ThemeNameFr,UnitScaleAr,UnitScaleFr,ReportEnName,ReportArName,ReportFrName,ThemeID,ThemeName,ThemeType,UnitScale,RunningVariableID,changingVariableID,RunningVariableName,changingVariableName,FixedVariable,YearTo,Source,SourceAr,SourceFr,PublishYear" .Contains(column.Name)) { item.GetType().GetProperty(column.Name).SetValue(item, column.GetValue(fixedVariables, null)); } } } // item.SectorID = fixedVariables.SectorID; // item.AgeID = fixedVariables.AgeID; item.GetType().GetProperty(changingVariableColumn).SetValue(item, changingValue.Substring(0, 8)); if (!string.IsNullOrEmpty(runningVariable)) { item.GetType().GetProperty(runningVariableColumn).SetValue(item, runningValue.Substring(0, 8)); } item.ThemeID = themeId; item.Value = value; if (_context.FactStatisticalDatas.Where(r => (item.AgeID == null || r.AgeID == item.AgeID) && (item.EducationLevelID == null || r.EducationLevelID == item.EducationLevelID) && (item.AgeID == null || r.AgeID == item.AgeID) && (item.GenderID == null || r.GenderID == item.GenderID) && (item.GovID == null || r.GovID == item.GovID) && (item.MaritalStatusID == null || r.MaritalStatusID == item.MaritalStatusID) && (item.YearID == null || r.YearID == item.YearID) && (item.FactDataID == null || r.FactDataID == item.FactDataID) && (item.ThemeID == null || r.ThemeID == item.ThemeID) ).Any() == false) { _context.FactStatisticalDatas.Add(item); } else { _context.FactStatisticalDatas.Attach(item); _context.Entry(item).State = EntityState.Modified; } return(_context.SaveChanges()); }