Skip to content

json data handling

KS LIM edited this page Aug 21, 2022 · 2 revisions
private static async Task<bool> CheckDoubleAlertData(dynamic telegram)
{
  CommonUtility util = new CommonUtility();
  List<KeyValuePair<string, string>> cosmosTlegramData = util.GetTelegramDataList(telegram.TelegramData.ToSring());
  string TelegramText = JsonConvert.SerializeObject(cosmosTelegramData.OrderBy(e=>e.Key));
  //Read the data from database
  List<AlertEntity>preTelegramDataList = DBAccessor.GetAlertData(
   (DataTime)telegram.timeOfOccurrence, (int)telegram.EquipmentID
  );
  foreach(dynamic pretelegram in preTelegramDataList)
  {
      // convert json text to json object
      JObject json = JObject.Parse(pretelegram.telegram.ToString());
      // convert the json text of data json["TeelegramData"] into dictionary so that we can do OrderBy(e=>e.Key)
      List<KeyValuePair<string,string>> preTelegramData = util.GetTelegramDataList(json["TelegramData"].ToString());
      // convert the Json Dictionary into text
      string preTelegramText = JsonConvert.SerializeObject(preTelegramData.OrderBy(e=>e.Key)); 
      if(preTelegramText != null)
      {
         // convert to jason array so that we can get its count() number
         JArray preTelegramJson = JArray.Parse(preTelegramText);
         JArray TelegramJson = JArray.Parse(TelegramText);
         if(TelegramJson.Count()==preTelegramJson.Count())
         { 
            if(preTelegramText.Equals(TelegramText))
            {
                string a = telegram.ToString();
                logger.LogWarning("重複警報電文 :"+A);
                return false;
            } 
         }
      }
  }
  DBAccessor.AddDataToCheckAlertTable(
      (DateTime)telegram.timeOfOccurrence, 
      (int)telegram.EquipmentID,
      (int)telegram.ModelID,
      telegram.ToString()
  );
  return true; 
}
  • DBAccessor.cs SQL code for IDbConnection
public static List<AlertEntity> GetAlertData(DateTime time_of_occurrence, int equipment_id)
{
   var builder = new SqlBuilder();
   builder.Select("CHK.telegram");
   builder.Where("CHK.time_of_occurrence = @time_of_occurrence");
   builder.AddParameters(new {@time_of_occurrence = time_of_occurrence.ToUniversalTime()}); // to avoid time zone issue add the .ToUniversalTime()
   builder.Where("CHK.equipment_id = @equipment_id");
   builder.AddParameters(new {@equipment_id = equipment_id});
   var builderTemplate = builder.AddTemplate("Select /**select**/ from t_check_alert CHK /**where**/");
   using(IDbConnection dbConnection = Connection)
   {
      dbConnection.Open();
      return dbConnection.Query<AlertEntity>(builderTemplate.RawSql, builderTemplate.Parameters).ToString();
   }
} 

public static string AddDataToCheckAlertTable((DataTime time_of_occurrence, int equipment_id, int model_id, string telegram))
{
   using(IDbConnetion dbConnection = Connection)
   {

   }
}

private static IDbConnection Connection
{
   get{
      DefaultTypeMap.MatchNamesWithUnderscores = true;
      return InitializeConnection();
   }
}

private static NpgsqlConnection InitializeConnnection()
{
   string connString = 
     "Server =" + Environment.GetEnvironmentVariable("PSQL_HOST_NAME") + ";" +
     "Username =" + Environment.GetEnvironmentVariable("PSQL_USER_NAME") + ";" +
     "Database =" + Environment.GetEnvironmentVariable("PSQL_DB_NAME") + ";" +
     "Port =" + Environment.GetEnvironmentVariable("PSQL_PORT_NAME") + ";" +
     "Password =" + Environment.GetEnvironmentVariable("PSQL_PASSWORD") + ";" +
     "SSLMode =" + Environment.GetEnvironmentVariable("PSQL_SSL_MODE") + ";" +
     "Pooling =" + Environment.GetEnvironmentVariable("POOLING_FLAG") + ";" +
     "Timeout =" + Environment.GetEnvironmentVariable("POOLING_TIMEOUT") + ";" 
  return new NpgsqlConnection(connString);
}
  • CommonUtility.JObject .GetTelegramDataList
class CommonUtility
{
   // convert the data into object
   public JObject CreateLogJsonObject(dynamic telegramData)
   {
      var jsonArray = new List<JObject>();
      foreach (var item in telegramData)
      {
         jsonArray.Add(new JObject(item));
      }
      Dictionary<string, string> dataMap = new Dictionary<string, string>();
      foreach(var jsonObj in jsonArray)
      {
         foreach(var item in jsonObj)
         {
            string key = (string)item.Key;
            dataMap.Add(key, item.Value.ToString());
         }
      }
      return JObject.Parse(JsonConvert.SerializeObject(dataMap));
   }

   public List<KeyValuePair<string, string>> GetTelegramDataList(string telegramString)
   {
       JArray jArr = JArray.Parse(telegramString);
       List<JProperty> jPs = jArr.Select(o=>o).Cast<JObject>().Properties().ToList();
       return jPs.Select(p=>new KeyValuePair<string,string>(p.Name, p.Value.ToString())).ToList();
   }
}
Clone this wiki locally