private static int Get_SIDToFilts(int idset, QueryPart query)
{
DataWrapper dtw = new DataWrapper(DataWrapper.ECONNECTIONTYPE.SQL, DataAccess.SQLConnString_DB.ConnectionString);
if (dtw.TestConnection())
{
dtw.DBConnection.Open();
try
{
int SID = -1;
////////////////////////
System.Data.IDbCommand cmd = dtw.DBConnection.CreateCommand();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "dbo.proc_GET_SID_VIEW_FILTS";
System.Data.IDbDataParameter _param_idset = cmd.CreateParameter();
_param_idset.DbType = System.Data.DbType.Int32;
_param_idset.ParameterName = "IDSet";
_param_idset.Value = idset;
cmd.Parameters.Add(_param_idset);
System.Data.IDbDataParameter _param_where = cmd.CreateParameter();
_param_where.DbType = System.Data.DbType.String;
_param_where.ParameterName = "Sql_Where";
_param_where.Value = query.WHERE_PART_CODE;
cmd.Parameters.Add(_param_where);
System.Data.IDbDataParameter _param_sid = cmd.CreateParameter();
_param_sid.DbType = System.Data.DbType.Int32;
_param_sid.ParameterName = "SID";
_param_sid.Direction = ParameterDirection.Output;
cmd.Parameters.Add(_param_sid);
cmd.ExecuteNonQuery();
SID = (_param_sid.Value != null) ? (int)_param_sid.Value : -1;
////////////////////////
dtw.DBConnection.Close();
return SID;
}
catch
{
dtw.DBConnection.Close();
return -1;
}
} return -1;
}
/// <summary>
/// Insert_Data
/// </summary>
/// <param name="idset">ID of Structure </param>
/// <param name="view">Matrix like view of CUBE</param>
/// <returns></returns>
///
public static QueryReport Insert_Data(int idset, Dictionary<string, List<string>> view)
{
QueryReport _report = new QueryReport();
_report.StartReport();
string min_time = string.Empty;
string max_time = string.Empty;
try
{
#region INIT VARIABLE
QueryPart fi_dim_qp = new QueryPart();
QueryPart fi_att_qp = new QueryPart();
QueryPart fa_att_qp = new QueryPart();
List<ConceptDimensionDB> lstDim = DataAccess.Get_DimensionsPartial(idset);
List<ConceptAttributeDB> lstAtt = DataAccess.Get_AttributesPartial(idset);
int SID = -1;
int itemCount = ((List<string>)view[view.Keys.First<string>()]).Count;
_report.RecordTargetCount = itemCount;
#endregion
#region LOAD TABLE TIME PERIOD
// Chache of TIME_PERIOD db table
List<TimePeriod> time_period = DataAccess.Get_TimePeriod();
#endregion
for (int i = 0; i < itemCount; i++)
{
bool hasError = false;
QueryPart cur_fi_dim_qp = new QueryPart();
QueryPart cur_fi_att_qp = new QueryPart();
QueryPart cur_fa_att_qp = new QueryPart();
#region GET DIMENSIONS PART
foreach (ConceptDimensionDB conc in lstDim)
{
// se la dimensione non e' una timedimension
if (!conc.IsTimeSeriesDim)
{
cur_fi_dim_qp[conc.Code] = new QueryCouple(conc.MemberTable, view[conc.Code][i]);
}
}
#endregion
#region GET ATTRIBUTES PART
foreach (ConceptAttributeDB conc in lstAtt)
{
if (view.ContainsKey(conc.Code))
{
if (conc.IsObservationValue)
{
cur_fa_att_qp[conc.Code] = new QueryCouple(conc.MemberTable, view[conc.Code][i]);
}
else
{
cur_fi_att_qp[conc.Code] = new QueryCouple(conc.MemberTable, view[conc.Code][i]);
}
}
}
#endregion
if (!hasError)
{
#region GET FILTS SID KEY
if (cur_fi_dim_qp.KEY_RECORD != fi_dim_qp.KEY_RECORD)
{
// Select SID from view
SID = DataAccess.Get_SIDToFilts(idset, cur_fi_dim_qp);
_report.QueryExecCount++;
// if no Serie found
if (SID < 0)
{
#region RESOLVE FILTS KEY
foreach (string dimStr in cur_fi_dim_qp.FIELDS.Keys)
{
// risolvo il codice della dimensione ed ottengo l'id nel database
cur_fi_dim_qp[dimStr].VALUE = DataAccess.Get_IDCode(cur_fi_dim_qp[dimStr].MEMBER, cur_fi_dim_qp[dimStr].CODE);
_report.QueryExecCount++;
if ((int)cur_fi_dim_qp[dimStr].VALUE < 0)
{
// codice non rivolvibile
_report.Errors.Add(string.Format("Unable to resolve {0} in row [{1}]", dimStr, i));
hasError = true;
break;
}
fi_dim_qp = cur_fi_dim_qp;
}
#endregion
if (!hasError)
{
#region Attribute FILTS Query Part
if (cur_fi_att_qp.KEY_RECORD != fi_att_qp.KEY_RECORD)
{
foreach (string attStr in cur_fi_att_qp.FIELDS.Keys)
{
cur_fi_att_qp[attStr].VALUE = DataAccess.Get_IDCode(cur_fi_att_qp[attStr].MEMBER, cur_fi_att_qp[attStr].CODE);
_report.QueryExecCount++;
if ((int)cur_fi_att_qp[attStr].VALUE < 0)
{
var isCodeRappresentation = (from c in lstAtt where c.MemberTable == cur_fi_att_qp[attStr].MEMBER select c.IsCodelist).OfType<bool>().First();
if (!isCodeRappresentation)
cur_fi_att_qp[attStr].VALUE = DataAccess.Insert_Code("Att", cur_fi_att_qp[attStr].MEMBER, cur_fi_att_qp[attStr].CODE, string.Empty);
if ((int)cur_fi_att_qp[attStr].VALUE < 0) cur_fi_att_qp[attStr].VALUE = null;
/*
if ((int)cur_fi_att_qp[attStr].VALUE < 0)
{
// codice non rivolvibile
hasError = true;
_report.Errors.Add(string.Format("Unable to resolve {0} in row [{1}]", attStr, i));
break;
}
*/
}
}
fi_att_qp = cur_fi_att_qp;
}
#endregion
//Insert new series in FILTS
SID = DataAccess.Insert_DataFilts(idset,
fi_dim_qp.SELECT_PART + ((fi_att_qp.SELECT_PART != string.Empty) ? " , " + fi_att_qp.SELECT_PART : string.Empty),
fi_dim_qp.INSERT_PART_VALUE + ((fi_att_qp.INSERT_PART_VALUE != string.Empty) ? "," + fi_att_qp.INSERT_PART_VALUE : string.Empty),
fi_dim_qp.WHERE_PART_VALUE);
_report.QueryExecCount++;
}
else
{
// Serie non trovata
}
}
fi_dim_qp = cur_fi_dim_qp;
}
#endregion
if (!hasError)
{
#region Attribute FACTS Query Part
if (cur_fa_att_qp.KEY_RECORD != fa_att_qp.KEY_RECORD)
{
foreach (string attStr in cur_fa_att_qp.FIELDS.Keys)
{
cur_fa_att_qp[attStr].VALUE = DataAccess.Get_IDCode(cur_fa_att_qp[attStr].MEMBER, cur_fa_att_qp[attStr].CODE);
_report.QueryExecCount++;
if ((int)cur_fa_att_qp[attStr].VALUE < 0)
{
var isCodeRappresentation = (from c in lstAtt where c.MemberTable == cur_fa_att_qp[attStr].MEMBER select c.IsCodelist).OfType<bool>().First();
if (!isCodeRappresentation)
cur_fa_att_qp[attStr].VALUE = DataAccess.Insert_Code("Att", cur_fa_att_qp[attStr].MEMBER, cur_fa_att_qp[attStr].CODE, string.Empty);
if ((int)cur_fa_att_qp[attStr].VALUE < 0) cur_fa_att_qp[attStr].VALUE = null;
// codice non rivolvibile
/*
if ((int)cur_fa_att_qp[attStr].VALUE < 0)
{
_report.Errors.Add(string.Format("Unable to resolve {0} in row [{1}]", attStr, i));
hasError = true;
break;
}
*/
}
}
fa_att_qp = cur_fa_att_qp;
}
#endregion
}
if (!hasError)
{
if (SID > -1)
{
#region INSERT INTO FactS
int idtime = -1;
string tPeriod = TranscodeTimePeriod(view["TIME_PERIOD"][i], view["FREQ"][i]);
try
{
idtime = (from period in time_period where period.CODE == tPeriod select period.ID).First();
}
catch
{
idtime = -1;
}
if (idtime >= 0)
{
// Converto value per inserimento in db Sql
object value = (view["OBS_VALUE"][i] == string.Empty || view["OBS_VALUE"][i] == "NaN") ? null : view["OBS_VALUE"][i].Replace('.', ',');
//DataAccess.Delete_DataFacts(idset, SID, idtime);
int _res = DataAccess.Insert_DataFacts(idset, SID, idtime, value, fa_att_qp.SELECT_PART, fa_att_qp.INSERT_PART_VALUE);
_report.QueryExecCount++;
// Get Min - Max Time
min_time = TranscodeTime.CompareMin(min_time, tPeriod);
max_time = TranscodeTime.CompareMax(max_time, tPeriod);
switch (_res)
{
case 0: _report.RecordCount++; break;
case 2: _report.RecordOverrideCount++; break;
case 1: _report.RecordIgnoreCount++; break;
case -1: _report.Errors.Add("Insert fail on Record [" + i + "]"); break;
}
}
else
{
_report.Errors.Add(string.Format("Unable to resolve TIME_PERIOD : {0} in row [{1}]", tPeriod, i));
hasError = true;
}
#endregion
}
}
}
}
}
catch (Exception ex)
{
Console.Write(ex.Message);
}
finally
{
_report.StopReport();
_report.MaxTime = max_time;
_report.MinTime = min_time;
}
return _report;
}
