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