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