public async Task SaveToMysql(MySqlConnection conn, Res res)
{
if (await ExistsOnMysql(conn, res))
return;
byte[] image = null;
if (res.Oekaki != null)
{
var imagePath = cacheDir + ryu_s.MyCommon.Tool.SanitizeForFilename(res.Oekaki);
using (var fs = new FileStream(imagePath, FileMode.Open, FileAccess.Read))
{
using (var br = new BinaryReader(fs))
{
image = br.ReadBytes((int)fs.Length);
}
}
}
var query = $"INSERT INTO {communityId} ({col_resnum},{col_name},{col_trip},{col_id},{col_date},{col_body},{col_oekaki_url},{col_oekaki_image})"
+ $"values(@{col_resnum},@{col_name},@{col_trip},@{col_id},@{col_date},@{col_body},@{col_oekaki_url},@{col_oekaki_image})";
var cmd = new MySqlCommand(query, conn);
cmd.Parameters.Add($"@{col_resnum}", resnum_type);
cmd.Parameters.Add($"@{col_name}", name_type, name_size);
cmd.Parameters.Add($"@{col_trip}", trip_type, trip_size);
cmd.Parameters.Add($"@{col_id}", id_type, id_size);
cmd.Parameters.Add($"@{col_date}", date_type);
cmd.Parameters.Add($"@{col_body}", body_type);
cmd.Parameters.Add($"@{col_oekaki_url}", oekaki_url_type, oekaki_url_size);
cmd.Parameters.Add($"@{col_oekaki_image}", oekaki_image_type);
cmd.Parameters[$"@{col_resnum}"].Value = res.Resnum;
cmd.Parameters[$"@{col_name}"].Value = (res.Name != null) ? MySqlHelper.EscapeString(res.Name) : res.Name;
cmd.Parameters[$"@{col_trip}"].Value = res.Trip;
cmd.Parameters[$"@{col_id}"].Value = res.Id;
cmd.Parameters[$"@{col_date}"].Value = res.Date;
cmd.Parameters[$"@{col_body}"].Value = (res.Body != null) ? MySqlHelper.EscapeString(res.Body) : res.Body;
cmd.Parameters[$"@{col_oekaki_url}"].Value = res.Oekaki;
cmd.Parameters[$"@{col_oekaki_image}"].Value = image;
try
{
var rowsAffected = await cmd.ExecuteNonQueryAsync();
}
catch (MySqlException ex)
{
ryu_s.MyCommon.Logging.LogException(ryu_s.MyCommon.LogLevel.error, ex);
}
return;
}
public List<Res> ParseHtml(string html)
{
var list = new List<Res>();
// var pattern = "<dt class=\"reshead\">\\s+<a name=\"\\d+\" class=\"resnumhead\"></a>(?<resnum>\\d+)\\s+：\\s+<span class=\"name\">(?<name>[^<]*?)</span>\\s+：(?<predate>.+?)\\s+ID: (?<id>[^\\s]+)\\s+</dt>\\s+<dd class=\"resbody\">\\n (?<body>.+?)\\n \\n(?<footer>.+?)</dd>";
var pattern = "<dt class=\"reshead\">\\s+<a name=\"\\d+\" class=\"resnumhead\"></a>(?<resnum>\\d+)\\s+：\\s+(?<nametrip>.+?)\\s+：(?<predate>.+?)\\s+ID: (?<id>[^\\s]+)\\s+</dt>\\s+<dd class=\"resbody\">\\n (?<body>.+?)\\n \\n(?<footer>.+?)</dd>";
//var pattern = "<dt class=\"reshead\">(?<res>.+?)</dd>";
var matches = Regex.Matches(html, pattern, RegexOptions.Singleline | RegexOptions.Compiled);
foreach (Match match1 in matches)
{
var res = new Res();
var resnum = int.Parse(match1.Groups["resnum"].Value);
res.Resnum = resnum;
var nametrip = match1.Groups["nametrip"].Value;
var namePattern = "<span class=\"name\">(?<name>.*?)</span>";
var nameMatch = Regex.Match(nametrip, namePattern, RegexOptions.Compiled);
if (nameMatch.Success)
{
res.Name = nameMatch.Groups["name"].Value;
}
var tripPattern = "<span class=\"trip\">(?<trip>.*?)</span>";
var tripMatch = Regex.Match(nametrip, tripPattern, RegexOptions.Compiled);
if (tripMatch.Success)
{
res.Trip = tripMatch.Groups["trip"].Value;
}
var predate = match1.Groups["predate"].Value;//"削除しました"の場合があるため一旦文字列として抽出し、再度正規表現にかける
var datePattern = "(?<year>\\d+)/(?<month>\\d+)/(?<day>\\d+)\\(.\\) (?<hour>\\d+):(?<minute>\\d+):(?<second>\\d+)";
var match = Regex.Match(predate, datePattern, RegexOptions.Compiled);
if (match.Success)
{
var year = int.Parse(match.Groups["year"].Value);
var month = int.Parse(match.Groups["month"].Value);
var day = int.Parse(match.Groups["day"].Value);
var hour = int.Parse(match.Groups["hour"].Value);
var minute = int.Parse(match.Groups["minute"].Value);
var second = int.Parse(match.Groups["second"].Value);
res.Date = new DateTime(year, month, day, hour, minute, second);
}
var id = match1.Groups["id"].Value;
res.Id = id;
var preBody = match1.Groups["body"].Value;
res.Body = RemoveLinkFromBody(preBody);
var footer = match1.Groups["footer"].Value;//お絵かきの画像のURLが含まれている。
var oekakiPattern = "(http://dic\\.nicovideo\\.jp/.+\\.png)";
var matchOekaki = Regex.Match(footer, oekakiPattern, RegexOptions.Singleline | RegexOptions.Compiled);
if (matchOekaki.Success)
{
res.Oekaki = matchOekaki.Groups[1].Value;
}
list.Add(res);
}
return list;
}
public async Task<bool> ExistsOnMysql(MySqlConnection conn, Res res)
{
var query = $"select * from {communityId} where resnum={res.Resnum}";
var reader = await ryu_s.Db.MySQL.ExecuteReaderAsync(conn, query);
var dt = ryu_s.Db.MySQL.ConvertDataReader(reader);
return (dt.Rows.Count > 0);
}
