public QueryConstructorWindow()
{
InitializeComponent();
SQLiteConnection connection = DatabaseConnectionHandler.Invoke();
TextBlockQueryHeader.Text = this.Title;
//_allTables = connection.Query<Table>("SELECT name FROM sqlite_master WHERE type='table';").ToList();
_allTablesString = connection.Query <string>("SELECT name FROM sqlite_master WHERE type='table';").ToList();
_allTablesString.Insert(0, "-");
JoinTableElement jte = new JoinTableElement();
JoinTableElementStorage js = new JoinTableElementStorage();
js.FirstTable = _allTablesString;
js.SecondTable = _allTablesString;
jte.OwnStorage = js;
JoinTableElementPack jp = new JoinTableElementPack();
jp.Element = jte;
jp.Storage = js;
var t = new List <JoinTableElementPack>();
jp.Element.PropertyChanged += ElementChangeHandler;
t.Add(jp);
ListBoxJoins.ItemsSource = t;
}
private void ButtonAddSource_OnClick(object sender, RoutedEventArgs e)
{
NewsSource ns = new NewsSource();
ns.s_rss_link = TextBoxAddSource.Text;
// проверка на наличия в БД
SQLiteConnection connection = DatabaseConnectionHandler.Invoke();
string sql = $"select count(*) from {TableNames.NetworkAddress} where s_url='{ns.s_rss_link}';";
if (connection.QueryFirst <int>(sql) != 0)
{
MessageBox.Show("Данный источник уже содержится в таблице");
return;
}
try
{
TryLoadLink(ns);
}
catch (Exception ex)
{
MessageBox.Show("Ошибка добавления " + ex.Message);
return;
}
DataBase.SaveNewsSource(ns);
MessageBox.Show($"Источник {ns.s_name} успешно добавлен.");
ListBoxSources.ItemsSource = DataBase.LoadNewsSources();
}
private void SaveExcelReport_Click(object sender, RoutedEventArgs e)
{
SQLiteConnection connection = DatabaseConnectionHandler.Invoke();
string sql = $"select count(*) from {TableNames.Newspapers}";
int count = connection.QueryFirst <int>(sql);
if (count < 1)
{
MessageBox.Show("В базе нет новостей.");
return;
}
String path = "";
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Title = "Выберите место для сохранения отчёта";
saveFileDialog.FileName = "report.xlsx";
saveFileDialog.Filter = "Excel table (*.xlsx)|*.xlsx";
saveFileDialog.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
if (saveFileDialog.ShowDialog() == true)
{
path = saveFileDialog.FileName;
}
else
{
return;
}
CreateReport(path);
MessageBox.Show($"Отчёт сохранён в: {path}");
}
private void ButtonSaveFullDB_Click(object sender, RoutedEventArgs e)
{
SQLiteConnection connection = DatabaseConnectionHandler.Invoke();
string sql = $"select count(*) from {TableNames.Newspapers}";
int count = connection.QueryFirst <int>(sql);
if (count < 1)
{
MessageBox.Show("В базе нет новостей.");
return;
}
String path = "";
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Title = "Выберите место для сохранения резервной копии";
saveFileDialog.FileName = $"{DateTime.Now.ToShortDateString()}.ofrebak";
saveFileDialog.Filter = "Резервная копия базы данных (*.ofrebak)|*.ofrebak";
saveFileDialog.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
if (saveFileDialog.ShowDialog() == true)
{
path = saveFileDialog.FileName;
}
else
{
return;
}
CreateAndSaveArchive(path);
MessageBox.Show($"Данные сохранены в: {saveFileDialog.FileName}");
}
public static void SetReadNewsSources(List <NewsSource> sources)
{
SQLiteConnection connection = DatabaseConnectionHandler.Invoke();
foreach (NewsSource source in sources)
{
String sql = $"update {TableNames.NewsSource} set " +
$"i_is_chosen={source.i_is_chosen} where id={source.id}";
connection.Execute(sql);
}
}
public static Boolean SaveNewspaper(Newspaper np)
{
SQLiteConnection connection = DatabaseConnectionHandler.Invoke();
string sql = $"select count(*) from {TableNames.Newspapers} where s_link='{np.s_link}'";
int t = connection.QueryFirst <int>(sql);
if (t != 0) // проверка наличия новости в БД
{
return(true);
}
np.id = MainWindow.LastDBIdent;
MainWindow.LastDBIdent++;
try
{
sql = $"insert into {TableNames.NewspaperHeader} (s_text) " +
$"values ('{np.s_header}');";
connection.Execute(sql);
int headerId =
connection.QueryFirst <int>($"select id from " +
$"{TableNames.NewspaperHeader} " +
$"where s_text='{np.s_header}'");
sql = $"insert into {TableNames.NewspaperDescription} " +
$"(s_text) values ('{np.s_description}');";
connection.Execute(sql);
int descriptionId =
connection.QueryFirst <int>($"select id from " +
$"{TableNames.NewspaperDescription} " +
$"where s_text='{np.s_description}'");
sql = $"insert into {TableNames.NewspaperFullText} " +
$"(id, s_text, i_is_parsed) values ({np.id}, " +
$"'{np.s_full_text}', {np.i_is_parsed});";
connection.Execute(sql);
sql = $"insert into {TableNames.Newspapers} (i_thematic_id, " +
$"i_header_id, i_source_id, s_date, i_is_read, i_description_id, s_link) " +
$"values ({np.i_thematic_id}, {headerId}, {np.i_source_id}, " +
$"'{np.s_date}', {0}, {descriptionId}, '{np.s_link}');";
connection.Execute(sql);
}
catch (Exception ex)
{
Console.WriteLine("error in newspaper save: " + ex.Message);
return(false); // если ломается запрос в БД, то потом могут воникнуть проблемы с обработкой полной новости.
}
return(true);
}
public MainWindow()
{
if (!File.Exists(second_course.Names.Database)) // Запись таблиц в бд при первом запуске
{
SQLiteConnection connection = DatabaseConnectionHandler.Invoke();
connection.Execute(Queries.FullDBCreationQuery);
DataBase.SaveDefaultNewsSources();
}
InitializeComponent();
TextBlockHeader.Text = this.Title;
LastDBIdent = GetLastDBIdent();
TextBoxSearch.GotFocus += (source, e) =>
{
if (this._searchFieldWaterMarkActive)
{
this._searchFieldWaterMarkActive = false;
this.TextBoxSearch.Text = "";
this.TextBoxSearch.Foreground = (SolidColorBrush)(new BrushConverter().ConvertFrom("#f5f5f5"));
}
};
TextBoxSearch.LostFocus += (source, e) =>
{
if (!this._searchFieldWaterMarkActive && string.IsNullOrEmpty(TextBoxSearch.Text))
{
this._searchFieldWaterMarkActive = true;
this.TextBoxSearch.Text = "Введите искомые слова";
this.TextBoxSearch.Foreground = (SolidColorBrush)(new BrushConverter().ConvertFrom("#A1A1A1"));
}
};
// ==========================================================================
_updateRSSWorker.DoWork += Worker_RSSUpdateDoWork;
_updateRSSWorker.RunWorkerCompleted += Worker_RSSUpdateFinished;
_updateRSSWorker.WorkerSupportsCancellation = true;
_loadFullNewsWorker.DoWork += Worker_ParseFullNewsDoWork;
_loadFullNewsWorker.RunWorkerCompleted += Worker_ParseFullNewsFinished;
_loadFullNewsWorker.WorkerSupportsCancellation = true;
_loadFullNewsWorker.WorkerReportsProgress = true;
_loadFullNewsWorker.ProgressChanged += Worker_ParseFullNewsProgressChanged;
_newspapers = DataBase.LoadSelectedNewspapers();
SetHeadersSourceList(_newspapers);
//QueryConstructorWindow qw = new QueryConstructorWindow();
//qw.ShowInTaskbar = true;
//qw.ShowDialog();
//Application.Current.Shutdown();
}
private void ListBoxNewHeaders_OnSelectionChanged(object sender, SelectionChangedEventArgs e)
{
if (e.AddedItems.Count > 0)
{
Newspaper selectedItem = (Newspaper)e.AddedItems[0];
SQLiteConnection connection = DatabaseConnectionHandler.Invoke();
BuildFullTextView(selectedItem);
selectedItem.i_is_read = true;
string sql = $"update {TableNames.Newspapers} set " +
$"i_is_read=1 where id={selectedItem.id}";
connection.Execute(sql);
}
}
public static List <NewsSource> LoadNewsSources(SQLiteConnection connection = null)
{
if (connection == null)
{
connection = DatabaseConnectionHandler.Invoke();
}
String sql = $"select src.id id, names.s_name s_name, address.s_url " +
$"s_rss_link, i_is_chosen from ({TableNames.NewsSource} " +
$"src INNER JOIN {TableNames.SourceSiteName} names ON " +
$"src.i_site_name_id = names.id) INNER JOIN " +
$"{TableNames.NetworkAddress} address ON src.i_rss_url_id = address.id";
return(new List <NewsSource>(connection.Query <NewsSource>(sql)));
}
private List <string> GetListOfColumnNames(string tableName)
{
if (tableName != "-")
{
SQLiteConnection connection = DatabaseConnectionHandler.Invoke();
string sql = $"PRAGMA table_info({tableName});";
List <TableColumn> columns = connection.Query <TableColumn>(sql).ToList();
List <string> res = new List <string>();
res.Add("-");
columns.ForEach(c => res.Add(c.name));
return(res);
}
return(null);
}
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 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);
}
/// <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;
}
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);
}
}
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);
}