public static void Parse2085(TextBox t, string tableData, int fieldid, int tagid)
{
db db = new db(System.Configuration.ConfigurationSettings.AppSettings["db"]);
db.ParameterAdd("@FieldID", fieldid);
db.ParameterAdd("@TagID", tagid);
db.ParameterAdd("@IsEstimate", "");
var parsedData = Regex.Matches(tableData.Trim(), @"(\w*): (\d{1,3}(\,\d{3})*)\s*km\s*(\((\d{4})\))?\s*");
int year = 0;
double total = 0d;
string notes = "";
int c = 0;
db.SetSqlStoredProcedure();
for (var i = 0; i < parsedData.Count; i++)
{
if (parsedData[i].Groups[1].Value == "total")
{
Double.TryParse(parsedData[i].Groups[2].Value, out total);
}
else if (parsedData[i].Groups[1].Value == "note")
{
notes = parsedData[i].Groups[2].Value;
}
if (parsedData[i].Groups[5].Value != "")
{
int.TryParse(parsedData[i].Groups[5].Value, out year);
}
}
if (year == 0)
{
return;
}
db.ParameterAdd("@Value", total);
db.ParameterAdd("@Year", year);
db.ParameterAdd("@Note", notes);
try
{
db.ParameterEdit("@IsEstimate", false);
var ex = db.ExecuteSql("CIA_Insert_Data");
if (ex != null)
{
t.Text += ex.Message + "--->" + Environment.NewLine;
}
}
catch (Exception convEx)
{
t.Text += convEx.Message + "-->" + fieldid + Environment.NewLine;
}
db.CloseConnection();
}
static void Main(string[] args)
{
// Creates two database connections
db db = new db("DB");
db dbUpdate = new db("DB");
// Gets the default Parameters so I can just update instead.
dbUpdate.ParameterAdd("@Latitude", "");
dbUpdate.ParameterAdd("@Longitude", "");
dbUpdate.ParameterAdd("@ZipCode", "");
// Queries the DB
var sqlStr = "SELECT ZipCode FROM ZipCodes WHERE ZipCode > 48101";
var ex = db.ExecuteSqlReader(sqlStr);
while (db.Reader.Read())
{
Console.WriteLine("Updating " + db.Reader[0]);
// Makes the call to bing. Only could get US to work for now as a zip code was giving me heck.
HttpWebRequest req = (HttpWebRequest)WebRequest.Create("http://dev.virtualearth.net/REST/v1/Locations/US/-/" + db.Reader[0] + "/?key=" + System.Configuration.ConfigurationManager.AppSettings["BingKey"]);
var resp = req.GetResponse();
var stream = resp.GetResponseStream();
var strReader = new StreamReader(stream);
var str = (strReader.ReadToEnd());
var data = JsonConvert.DeserializeObject<BingMaps>(str);
// Update the datbase
sqlStr = "UPDATE ZipCodes SET Latitude=@Latitude, Longitude=@Longitude WHERE ZipCode=@ZipCode";
dbUpdate.ParameterEdit("@Latitude", data.resourceSets[0].resources[0].point.coordinates[0]);
dbUpdate.ParameterEdit("@Longitude", data.resourceSets[0].resources[0].point.coordinates[1]);
dbUpdate.ParameterEdit("@ZipCode", db.Reader[0]);
ex = dbUpdate.ExecuteSql(sqlStr);
if (ex != null)
{
Console.WriteLine("Zip Code: " + db.Reader[0] + " had an error in updating. Error -->" + ex.Message);
}
Thread.Sleep(1000);
}
dbUpdate.CloseConnection();
db.CloseConnection();
}
public static string ParseTableData(string tableData, int fieldid, int tagid)
{
var sb = new StringBuilder();
var db = new db(System.Configuration.ConfigurationSettings.AppSettings["db"]);
db.ParameterAdd("@FieldID", fieldid);
db.ParameterAdd("@TagID", tagid);
db.ParameterAdd("@Value", "");
db.ParameterAdd("@Year", "");
db.ParameterAdd("@IsEstimate", "");
var parsedData = Regex.Split(tableData.Trim(), @"[\r\n]+").ToList();
int c = 0;
var note = parsedData.FirstOrDefault(a => a.Trim().StartsWith("note:"));
var noteRemove = note;
if (note == null)
{
note = "";
}
else
{
note = note.Replace("note:", "").Trim();
}
db.ParameterAdd("@Note", note);
db.SetSqlStoredProcedure();
var noteDateMatch = Regex.Matches(note, @".*\(\b(\d{0,2}\s*\w*\s\d{2,4}|\d{4}|FY\d{0,4}\/?\d{0,4})\s*(est.?)?\b.*$", RegexOptions.Singleline);
string noteDate = "";
if (noteDateMatch.Count > 0)
{
noteDate = parseDate(noteDateMatch[0].Groups[1].Value);
}
parsedData.Remove(noteRemove);
foreach (var i in parsedData)
{
var regExString = @"^\$?(-?\d{1,3}(\,\d{3})*\.?\d*)%?\s*(\w*)?.*\(\b(\w*\s\d{1,2},\s?\d{2,4}|\d{0,2}\s*\w*\s\d{2,4}|\d{4}|FY\d{0,4}\/?\d{0,4})\s*(est.?)?\b.*$";
var matches = Regex.Match(i.Trim(), regExString);
if (matches.Groups.Count < 2 && noteDate == "")
{
sb.AppendLine("DID NOT MATCH-->" + i.Trim());
continue;
}
else if(matches.Groups.Count < 2 && noteDate != "")
{
matches = Regex.Match(i.Trim() + " " + note, regExString);
if (matches.Groups.Count < 2 && noteDate != "")
{
sb.AppendLine("2nd Chance DID NOT MATCH-->" + i.Trim());
continue;
}
}
try
{
var value = Convert.ToDecimal(matches.Groups[1].Value);
switch (matches.Groups[3].Value.Trim())
{
case "thousand":
value *= 1000m;
break;
case "million":
value *= 1000000m;
break;
case "billion":
value *= 1000000000m;
break;
case "trillion":
value *= 1000000000000m;
break;
}
db.ParameterEdit("@Value", value);
var date = parseDate(matches.Groups[4].Value) ?? noteDate;
if (date == "")
{
continue;
}
db.ParameterEdit("@Year", date);
db.ParameterEdit("@IsEstimate", string.IsNullOrEmpty(matches.Groups[5].Value) ? false : true);
var ex = db.ExecuteSql("CIA_Insert_Data");
if (ex != null)
{
sb.AppendLine(ex.Message + "--->");
}
}
catch (Exception convEx)
{
sb.AppendLine(convEx.Message + "-->" + tagid + "--->" + fieldid);
}
}
db.CloseConnection();
return sb.ToString();
}