public static Int32 GetThematicID(String thematicName)
{
SQLiteConnection connection = DatabaseConnectionHandler.Invoke();
String sql = $"select count(*) from {TableNames.Thematic} where s_name = '{thematicName}'";
int thematicId = -1;
if (connection.QueryFirst <int>(sql) == 0)
{
String sqlInsert = $"insert into {TableNames.Thematic} (s_name) values ('{thematicName}')";
connection.Execute(sqlInsert);
}
sql = $"select id from {TableNames.Thematic} where s_name = '{thematicName}'";
thematicId = connection.QueryFirst <Int32>(sql);
return(thematicId);
}
private void ButtonRemoveAllData_OnClick(object sender, RoutedEventArgs e)
{
File.Delete(Names.Database);
if (Directory.Exists("temp"))
{
Directory.Delete("temp", true);
}
SQLiteConnection connection = DatabaseConnectionHandler.Invoke();
connection.Execute(Queries.FullDBCreationQuery);
DataBase.SaveDefaultNewsSources();
MainWindow.LastDBIdent = 0;
this.Close();
}
public static void FillSourceNames(List <Newspaper> newspapers)
{
SQLiteConnection connection = DatabaseConnectionHandler.Invoke();
List <NewsSource> sources = connection.Query <NewsSource>($"select * from {TableNames.NewsSource} INNER JOIN {TableNames.SourceSiteName} names ON i_site_name_id = names.id").ToList();
foreach (Newspaper newspaper in newspapers)
{
foreach (NewsSource source in sources)
{
if (source.id == newspaper.i_source_id)
{
newspaper.SourceName = source.s_name;
break;
}
}
}
}
/// <summary>
/// Получение максимального индекса в БД чтобы локально их присваивать
/// </summary>
/// <returns></returns>
private static Int32 GetLastDBIdent()
{
SQLiteConnection connection = DatabaseConnectionHandler.Invoke();
int maxIdent;
try
{
maxIdent = connection.QueryFirst <Int32>($"select max(id) from {TableNames.Newspapers}");
}
catch (Exception)
{
Console.WriteLine("set max id = 0");
maxIdent = 0;
}
maxIdent++; // Чтобы следующий элемент имел отличный от найденного id
return(maxIdent);
}
public void Worker_ParseFullNewsFinished(object sender, RunWorkerCompletedEventArgs e)
{
List <Newspaper> items = e.Result as List <Newspaper>;
//SetHeadersSourceList(items);
SQLiteConnection connection = DatabaseConnectionHandler.Invoke();
String sql = $"select count(*) from {TableNames.NewspaperFullText}" +
$" where i_is_parsed=1;";
Int32 parsed = connection.QueryFirst <Int32>(sql);
sql = $"select count(*) from {TableNames.NewspaperFullText};";
Int32 allCount = connection.QueryFirst <Int32>(sql);
MessageBox.Show($"Обновление завершено. Загружены полные версии для {parsed} из {allCount}.");
ProgressBarParsingNews.Value = 0;
_isFullNewsUpdatingNow = false;
ButtonUpdate.IsEnabled = true;
ButtonUpdate.Content = "Обновить";
ButtonSettings.IsEnabled = true;
}
public static void SaveNewsSource(NewsSource source)
{
SQLiteConnection connection = DatabaseConnectionHandler.Invoke();
string sql = $"insert into {TableNames.SourceSiteName} (s_name) values ('{source.s_name}');";
connection.Execute(sql);
//sql = $"select id from {TableNames.SourceSiteName} where rowid=last_insert_rowid();";
sql = $"select max(id) from {TableNames.SourceSiteName};";
int siteNameId = connection.QueryFirst <int>(sql);
sql = $"insert into {TableNames.NetworkAddress} (s_url) values ('{source.s_rss_link}');";
connection.Execute(sql);
//sql = $"select id from {TableNames.NetworkAddress} where rowid=last_insert_rowid();";
sql = $"select max(id) from {TableNames.NetworkAddress};";
int networkAddressId = connection.QueryFirst <int>(sql);
sql = $"insert into {TableNames.NewsSource} (i_site_name_id, i_rss_url_id, i_is_chosen) " +
$"values ({siteNameId}, {networkAddressId}, 1);";
connection.Execute(sql);
}
public static void SaveImage(ImageDB image)
{
SQLiteConnection connection = DatabaseConnectionHandler.InvokeMultiple();
string sql = $"insert into {TableNames.NetworkAddress} (s_url) values ('{image.s_url}')";
connection.Execute(sql);
int networkId = connection.QueryFirst <int>(
$"select id from {TableNames.NetworkAddress} where s_url='{image.s_url}'");
connection.Execute($"insert into {TableNames.LocalFileAddress} " +
$"(s_path) values ('{image.s_path}')");
int localId = connection.QueryFirst <int>(
$"select id from {TableNames.LocalFileAddress} where s_path='{image.s_path}'");
connection.Execute($"insert into {TableNames.Images} (i_newspaper_id, " +
$"i_local_address_id, i_network_address_id) values " +
$"({image.i_newspaper_id}, {localId}, {networkId})");
}
public static List <Newspaper> LoadNewspapers(SQLiteConnection connection = null)
{
if (connection == null)
{
connection = DatabaseConnectionHandler.Invoke();
}
String sql = $"select news.id id, news.s_date s_date, descs.s_text " +
$"s_description, heads.s_text s_header, i_thematic_id," +
$" i_source_id, i_is_read, s_link from (({TableNames.Newspapers} " +
$"news INNER JOIN {TableNames.NewspaperHeader} heads " +
$"ON news.i_header_id = heads.id) INNER JOIN {TableNames.NewspaperDescription} " +
$"descs ON news.i_description_id = descs.id)";
List <Newspaper> newspapers = connection.Query <Newspaper>(sql).ToList();
FillSourceNames(newspapers);
newspapers.Sort((np1, np2) => np2.s_date.CompareTo(np1.s_date));
return(newspapers);
}
private void CreateReport(string filePath)
{
SQLiteConnection connection = DatabaseConnectionHandler.Invoke();
var wb = new XLWorkbook();
var ws = wb.Worksheets.Add("Новости");
ws.Cell("A1").Value = "Дата";
ws.Cell("B1").Value = "Название";
ws.Cell("C1").Value = "Краткое описание";
ws.Cell("D1").Value = "Полный текст (формат HTML)";
ws.Cell("E1").Value = "Название источника";
List <Newspaper> news = DataBase.LoadNewspapers();
for (int i = 0; i < news.Count; i++) // сдвиг на 2 вверх для работы с ячейками экселя
{
ws.Cell($"A{i + 2}").Value = news[i].s_date;
ws.Cell($"B{i + 2}").Value = news[i].s_header;
ws.Cell($"C{i + 2}").Value = news[i].s_description;
ws.Cell($"D{i + 2}").Value = connection.QueryFirst <String>($"select s_text from {TableNames.NewspaperFullText} where id={news[i].id}");
ws.Cell($"E{i + 2}").Value = news[i].SourceName;
}
var rngTable = ws.Range("A1:E1");
rngTable.Style.Font.Bold = true;
var content = ws.Range($"A1:E{news.Count + 1}");
content.Style.Border.InsideBorder = XLBorderStyleValues.Thin;
content.Style.Border.OutsideBorder = XLBorderStyleValues.Thick;
ws.Columns(1, 5).AdjustToContents();
File.Delete(filePath);
wb.SaveAs(filePath);
}
private void ButtonLoadFullDB_Click(object sender, RoutedEventArgs e)
{
OpenFileDialog openFileDialog = new OpenFileDialog();
openFileDialog.Title = "Выберите файл резервной копии";
openFileDialog.Filter = "Резервная копия базы данных (*.ofrebak)|*.ofrebak";
openFileDialog.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
if (openFileDialog.ShowDialog() == false)
{
return;
}
using (Ionic.Zip.ZipFile zip1 = Ionic.Zip.ZipFile.Read(openFileDialog.FileName))
{
foreach (Ionic.Zip.ZipEntry entry in zip1)
{
entry.Extract(Directory.GetCurrentDirectory(), ExtractExistingFileAction.DoNotOverwrite);
}
}
SQLiteConnection externalConnection =
DatabaseConnectionHandler.InvokeWithPath($"{Directory.GetCurrentDirectory()}\\{Names.TempBDFile}");
SQLiteConnection localConnection = DatabaseConnectionHandler.Invoke();
List <Newspaper> externalNews = DataBase.LoadNewspapers(externalConnection);
string sql;
foreach (Newspaper np in externalNews) // заполнение новостей данными
{
sql = $"select i_is_parsed from {TableNames.NewspaperFullText} where id={np.id}";
np.i_is_parsed = externalConnection.QueryFirst <bool>(sql);
sql = $"select s_text from {TableNames.NewspaperFullText} where id={np.id}";
np.s_full_text = externalConnection.QueryFirst <string>(sql);
sql = $"select s_name from {TableNames.Thematic} where id={np.i_thematic_id}";
string thematic = externalConnection.QueryFirst <string>(sql);
np.i_thematic_id = DataBase.GetThematicID(thematic);
}
List <NewsSource> sources = DataBase.LoadNewsSources(externalConnection);
sql = $"select image.id id, i_newspaper_id, s_path," +
$" s_url from {TableNames.Images} " +
$"INNER JOIN {TableNames.LocalFileAddress} " +
$"ON image.i_local_address_id = local_address.id " +
$"INNER JOIN {TableNames.NetworkAddress} ON " +
$"network_address.id = image.i_network_address_id;";
List <ImageDB> images = externalConnection.Query <ImageDB>(sql).ToList();
foreach (NewsSource source in sources)
{
sql = $"select count(*) from {TableNames.NetworkAddress} " +
$"where s_url='{source.s_rss_link}';";
int cnt = localConnection.QueryFirst <int>(sql);
if (cnt == 0)
{
DataBase.SaveNewsSource(source);
}
}
foreach (Newspaper np in externalNews) // статьи получают актуальные локальные айди и сохраняются в базе
{
List <ImageDB> newsImages = GetNewspaperImages(np.id, images);
DataBase.SaveNewspaper(np);
foreach (ImageDB image in newsImages)
{
sql = $"select count(*) from {TableNames.LocalFileAddress} " +
$"where s_path = '{image.s_path}';";
int count = localConnection.QuerySingle <int>(sql);
if (count != 0)
{
continue;
}
DataBase.SaveImage(image);
}
}
File.Delete(Names.TempBDFile);
MessageBox.Show("Данные успешно загружены");
}
private void ButtonBase_OnClick(object sender, RoutedEventArgs e)
{
List <JoinTableElementPack> lst = ListBoxJoins.ItemsSource as List <JoinTableElementPack>;
bool execauteSql = false;
List <string> tablesInUse = new List <string>();
string sql = $"SELECT # FROM";
foreach (var pack in lst)
{
if (pack.Element.FirstTable != null && pack.Element.SecondTable != null &&
pack.Element.FirstField != null && pack.Element.SecondField != null &&
pack.Element.FirstTable != "-" && pack.Element.SecondTable != "-" &&
pack.Element.FirstField != "-" && pack.Element.SecondField != "-") // проверка на завершённость строки джойнов
{
if (execauteSql)
{
if (!tablesInUse.Contains(pack.Element.FirstTable) &&
!tablesInUse.Contains(pack.Element.SecondTable))
{
MessageBox.Show("Попытка соединить две пары разных таблиц в одном запросе");
return;
}
}
if (!execauteSql)
{
sql += $" {pack.Element.FirstTable}";
execauteSql = true;
}
if (pack.Element.FirstTable == pack.Element.SecondTable)
{
MessageBox.Show("Попытка объединить таблицу с ней же.");
return;
}
if (tablesInUse.Contains(pack.Element.SecondTable))
{
sql += $" INNER JOIN {pack.Element.FirstTable} ON " +
$"{pack.Element.FirstTable}.{pack.Element.FirstField} = " +
$"{pack.Element.SecondTable}.{pack.Element.SecondField}";
}
else
{
sql += $" INNER JOIN {pack.Element.SecondTable} ON " +
$"{pack.Element.FirstTable}.{pack.Element.FirstField} = " +
$"{pack.Element.SecondTable}.{pack.Element.SecondField}";
}
tablesInUse.Add(pack.Element.FirstTable);
tablesInUse.Add(pack.Element.SecondTable);
}
}
if (!execauteSql)
{
foreach (var pack in lst)
{
if (pack.Element.FirstTable != null && pack.Element.FirstTable != "-")
{
sql = $"SELECT # FROM {pack.Element.FirstTable}";
execauteSql = true;
tablesInUse.Add(pack.Element.FirstTable);
break;
}
if (pack.Element.SecondTable != null && pack.Element.SecondTable != "-")
{
sql = $"SELECT # FROM {pack.Element.SecondTable}";
execauteSql = true;
tablesInUse.Add(pack.Element.SecondTable);
break;
}
}
}
List <string> selectedColumns = GetSelectedColumns();
if (selectedColumns.Count < 1)
{
MessageBox.Show("Выберите хотя бы один столбец для вывода.");
return;
}
sql = PerformSelectedColumns(sql, selectedColumns);
sql = PerformConditions(sql);
if (execauteSql)
{
try
{
System.Data.DataTable table = new System.Data.DataTable();
SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter(sql, DatabaseConnectionHandler.Invoke());
dataAdapter.Fill(table);
DataGridResult.ItemsSource = table.DefaultView;
}
catch (Exception exception)
{
MessageBox.Show(sql, "Ошибка: " + exception.Message);
}
}
else
{
MessageBox.Show(
"Для выполнения запроса выберите таблицу, либо установите значения для объединения таблиц.");
}
//MessageBox.Show(sql);
}
private void BuildFullTextView(Newspaper np)
{
GridScrollView.Children.Clear();
SQLiteConnection connection = DatabaseConnectionHandler.Invoke();
String sql = $"select s_text from {TableNames.NewspaperFullText} " +
$"where id={np.id}";
string fullText = "";
try
{
fullText = connection.QueryFirst <String>(sql);
}
catch (Exception ex)
{
Console.WriteLine("full text loading error, np id=" + np.id);
}
String thematic = connection.QueryFirst <String>(
$"select s_name from {TableNames.Thematic} where id={np.i_thematic_id}");
GridScrollView.RowDefinitions.Add(new RowDefinition());
GridScrollView.VerticalAlignment = VerticalAlignment.Top;
var tb = new TextBlock();
tb.Inlines.Clear();
Hyperlink hyperLink = new Hyperlink()
{
NavigateUri = new Uri(np.s_link)
};
hyperLink.RequestNavigate += Hyperlink_RequestNavigate;
hyperLink.Foreground = (SolidColorBrush)(new BrushConverter().ConvertFrom("#d9d9d9"));
hyperLink.Inlines.Add(new Run("Открыть в браузере")
{
FontWeight = FontWeights.Normal
});
tb.Inlines.Add(hyperLink);
tb.FontSize = 20;
tb.Foreground = (SolidColorBrush)(new BrushConverter().ConvertFrom("#d9d9d9"));
GridScrollView.Children.Add(tb);
Grid.SetRow(tb, GridScrollView.RowDefinitions.Count - 1);
Grid.SetColumnSpan(tb, 2);
if (thematic != "-")
{
var tb6 = new TextBlock();
tb6.Text = thematic;
tb6.TextAlignment = TextAlignment.Right;
tb6.FontSize = 20;
tb6.Foreground = (SolidColorBrush)(new BrushConverter().ConvertFrom("#d9d9d9"));
GridScrollView.Children.Add(tb6);
Grid.SetRow(tb6, GridScrollView.RowDefinitions.Count - 1);
Grid.SetColumn(tb6, 1);
}
GridScrollView.RowDefinitions.Add(new RowDefinition());
var rect = new System.Windows.Shapes.Rectangle();
rect.Height = 1;
rect.Fill = Brushes.Gray;
GridScrollView.Children.Add(rect);
Grid.SetRow(rect, GridScrollView.RowDefinitions.Count - 1);
Grid.SetColumnSpan(rect, 2);
GridScrollView.RowDefinitions.Add(new RowDefinition());
var tb3 = new TextBlock();
tb3.Text = "\n" + np.s_header + "\n";
tb3.Foreground = (SolidColorBrush)(new BrushConverter().ConvertFrom("#f5f5f5"));
tb3.TextWrapping = TextWrapping.Wrap;
tb3.FontWeight = FontWeights.Bold;
tb3.FontSize = 28;
GridScrollView.Children.Add(tb3);
Grid.SetRow(tb3, GridScrollView.RowDefinitions.Count - 1);
Grid.SetColumnSpan(tb3, 2);
GridScrollView.RowDefinitions.Add(new RowDefinition());
var tb2 = new TextBlock();
tb2.Text = fullText.Length == 0 ? np.s_description : fullText;
tb2.Foreground = (SolidColorBrush)(new BrushConverter().ConvertFrom("#f5f5f5"));
tb2.TextWrapping = TextWrapping.Wrap;
tb2.FontSize = 20;
GridScrollView.Children.Add(tb2);
Grid.SetRow(tb2, GridScrollView.RowDefinitions.Count - 1);
Grid.SetColumnSpan(tb2, 2);
SQLiteConnection conn = DatabaseConnectionHandler.Invoke();
sql = $"select i_local_address_id from " +
$"{TableNames.Images} where i_newspaper_id={np.id}";
List <int> localAddressIds = conn.Query <int>(sql).ToList();
foreach (int localAddressId in localAddressIds) // загрузка всех картинок
{
sql = $"select s_path from {TableNames.LocalFileAddress} where id={localAddressId};";
string localPath = conn.QueryFirst <string>(sql);
var img = new System.Windows.Controls.Image();
try
{
img.Source = new BitmapImage(new Uri(Directory.GetCurrentDirectory() + "\\" + localPath));
}
catch (Exception ex)
{
Console.WriteLine($"error in image loading: {localPath} // {ex.Message}");
continue;
}
GridScrollView.RowDefinitions.Add(new RowDefinition());
GridScrollView.Children.Add(img);
Grid.SetRow(img, GridScrollView.RowDefinitions.Count - 1);
Grid.SetColumnSpan(img, 2);
}
}
/// <summary>
/// Загружает полную версию новости для каждого элемента. Часть backgroundWorker
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
public void Worker_ParseFullNewsDoWork(object sender, DoWorkEventArgs e)
{
SQLiteConnection connection = DatabaseConnectionHandler.Invoke();
string sql = $"select np.id id, np.s_link s_link from " +
$"{TableNames.NewspaperFullText} as ft " +
$"INNER JOIN {TableNames.Newspapers} as np on ft.id = np.id " +
$"where ft.i_is_parsed = 0";
List <Newspaper> news = connection.Query <Newspaper>(sql).ToList();
int threads = 16;
List <List <Newspaper> > parallelWorks = new List <List <Newspaper> >();
int step = news.Count / (threads - 1);
if (step < 1)
{
step = 1;
}
for (int i = 0; i < news.Count; i += step) // деление на части для параллельного парсинга
{
List <Newspaper> works = new List <Newspaper>();
for (int j = i; j < i + step && j < news.Count; j++)
{
works.Add(news[j]);
}
parallelWorks.Add(works);
}
bool isServerDown = false;
int parsed = 0;
Parallel.For(0, threads, (i, state) =>
{
if (i < parallelWorks.Count)
{
foreach (Newspaper np in parallelWorks[i])
{
if (_loadFullNewsWorker.CancellationPending)
{
break;
}
string fullText;
try
{
fullText = ParseFullNewspaper.GetFullNewspaperText(np.s_link, np.id);
}
catch (Exception ex)
{
Console.WriteLine($"error in parsing newspaper id={np.id}, {ex.Message}, s_link={np.s_link}");
if (ex.Message == "Невозможно соединиться с удаленным сервером")
{
isServerDown = true;
break;
}
continue;
}
DataBase.UpdateNewspaperFullText(np.id, fullText);
parsed++;
if (_loadFullNewsWorker.WorkerReportsProgress)
{
_loadFullNewsWorker.ReportProgress((int)(((double)parsed / (double)news.Count) * 100));
}
//Console.WriteLine(parsed + " / " + news.Count + " np id: " + np.id);
}
}
});
if (isServerDown)
{
MessageBox.Show("Невозможно соединиться с удаленным сервером, возможно сервер выключен или отсутствует подключение к сети интернет.");
return;
}
e.Result = news;
}
