internal List <Goal> GetGoals()
{
if (goals != null)
{
return(goals);
}
goals = new List <Goal>();
DBConnection connection = null;
try
{
connection = new DBConnection(mdtDb);
if ((dimensions == null || dimensions.Count == 0) && mdtDb.dimensionsTable != null)
{
dimensions = new List <Dimension>();
DbDataReader reader = connection.ExecuteReader("SELECT NAME, LABEL, LABEL_ENG, TABLE_NAME, CODE, DESCRIPTION, DESCRIPTION_ENG, IS_TIME, INT_CODE FROM DIMENSION ORDER BY SEQUENCE");
while (reader.Read())
{
Dimension d = new Dimension
{
name = reader.GetString(0).Trim(),
label = reader.GetString(1).Trim(),
labelEn = reader.GetString(2).Trim(),
isTime = reader.GetByte(7) == 1
};
if (!reader.IsDBNull(3))
{
Table t = new Table
{
name = reader.GetString(3).Trim(),
code = reader.IsDBNull(4) ? null : reader.GetString(4).Trim(),
desc = reader.IsDBNull(5) ? null : reader.GetString(5).Trim(),
descEn = reader.IsDBNull(6) ? null : reader.GetString(6).Trim(),
integerCode = reader.GetByte(8) == 1
};
d.table = t;
}
dimensions.Add(d);
}
reader.Close();
}
foreach (Dimension dimension in dimensions)
{
if (dimension.table == null)
{
continue;
}
DataSet dimensionReader = connection.ExecuteDataSet(dimension.table.GetSqlSelect());
foreach (DataRow dr in dimensionReader.Tables[0].Rows)
{
string key = dr[0].ToString().Trim();
string value = dr[1].ToString().Trim();
dimension.values.Add(key, value);
if (!dr.IsNull(2))
{
string valueEn = dr[2].ToString().Trim();
dimension.valuesEn.Add(key, valueEn);
if (string.IsNullOrWhiteSpace(value))
{
dimension.values[key] = valueEn;
}
}
else
{
dimension.valuesEn.Add(key, value);
}
}
}
DataSet goalReader = connection.ExecuteDataSet(mdtDb.goalTable.GetSqlSelect());
foreach (DataRow dr in goalReader.Tables[0].Rows)
{
Goal goal = new Goal
{
code = dr[0].ToString().Trim(),
desc = dr[1].ToString().Trim(),
descEn = dr[2].ToString().Trim()
};
DataSet targetReader = connection.ExecuteDataSet(mdtDb.targetTable.GetSqlSelect(), new Dictionary <string, object> {
{ "@REFERENCE", goal.code }
});
foreach (DataRow dr1 in targetReader.Tables[0].Rows)
{
Target target = new Target
{
code = dr1[0].ToString().Trim(),
desc = dr1[1].ToString().Trim(),
descEn = dr1[2].ToString().Trim()
};
DataSet indicatorReader = connection.ExecuteDataSet(mdtDb.indicatorTable.GetSqlSelect(), new Dictionary <string, object> {
{ "@REFERENCE", target.code }
});
foreach (DataRow dr2 in indicatorReader.Tables[0].Rows)
{
Indicator indicator = new Indicator
{
code = dr2[0].ToString().Trim(),
desc = dr2[1].ToString().Trim(),
descEn = dr2[2].ToString().Trim()
};
DataSet subIndicatorReader = connection.ExecuteDataSet(mdtDb.subIndicatorTable.GetSqlSelect(), new Dictionary <string, object> {
{ "@REFERENCE", indicator.code }
});
foreach (DataRow dr3 in subIndicatorReader.Tables[0].Rows)
{
SubIndicator subIndicator = new SubIndicator
{
code = dr3[0].ToString().Trim(),
desc = dr3[1].ToString().Trim().Length == 0 ? dr3[0].ToString().Trim() : dr3[1].ToString().Trim(),
descEn = dr3[2].ToString().Trim().Length == 0 ? dr3[0].ToString().Trim() : dr3[2].ToString().Trim()
};
string dims = dr3[3].ToString().Trim();
for (int i = 0; i < dims.Length && i < dimensions.Count; i++)
{
if (dims[i] == '1')
{
subIndicator.dimensions.Add(dimensions[i]);
}
}
indicator.subIndicators.Add(subIndicator);
}
target.indicators.Add(indicator);
}
goal.targets.Add(target);
}
goals.Add(goal);
}
}
finally
{
if (connection != null)
{
connection.CloseConnection();
}
}
return(goals);
}
public string GetValueSet(SubIndicator subIndicator)
{
return("VS" + name + "_" + subIndicator.code);
}
public List <Query> GetDropCreateQueries(Database database, string mainTable, SubIndicator subIndicator)
{
Query query;
List <Query> queries = new List <Query>();
switch (database.type)
{
case "ORACLE":
query = new Query("CREATE OR REPLACE VIEW " + mainTable + " AS SELECT * FROM " + this.name + " WHERE " + this.code + " = '" + subIndicator.code + "'");
queries.Add(query);
break;
default:
query = new Query("IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME = '" + mainTable + "' AND TYPE = 'v') DROP VIEW " + mainTable);
queries.Add(query);
query = new Query("GO");
queries.Add(query);
query = new Query("CREATE VIEW " + mainTable + " AS SELECT * FROM " + this.name + " WHERE " + this.code + " = '" + subIndicator.code + "'");
queries.Add(query);
query = new Query("GO");
queries.Add(query);
break;
}
return(queries);
}
