protected override bool OnStartup(StartupEventArgs eventArgs)
{
AppWPF = new SCADA.AppWPF();
AppWPF.InitializeComponent();
AppWPF.Run();
return(false);
}
public DialogWindowSettingProgram()
{
AppWPF application = ((AppWPF)Application.Current);
InitializeComponent();
OptionsGeneralProgramm = new GridOptionsGeneralProgramm();
OptionsDatabaseProgramm = new GridOptionsDatabaseProgramm();
OldCreateFolder = application.ConfigProgramBin.CreateFolder;
OldPathCreateProject = application.ConfigProgramBin.PathBrowseProject;
OldSQLSecuritySSPI = application.ConfigProgramBin.SQLSecuritySSPI;
OldUseDatabase = application.ConfigProgramBin.UseDatabase;
OldSQLServerName = application.ConfigProgramBin.SQLServerName;
OldSQLDatabaseName = application.ConfigProgramBin.SQLDatabaseName;
OldSQLPassword = application.ConfigProgramBin.SQLPassword;
OldSQLUserName = application.ConfigProgramBin.SQLUserName;
}
private void Apply(object sender, RoutedEventArgs e)
{
e.Handled = true;
AppWPF application = ((AppWPF)Application.Current);
if (OptionsDatabaseProgramm.TBDatabaseName.Text.Length > 40)
{
if (TreeViewProperties.SelectedItem != TreeViewItemDatabase)
{
TreeViewItemDatabase.IsSelected = true;
}
OptionsDatabaseProgramm.LPopupMessage.Content = "Имя базы данных не может быть длинее 40 символов.";
OptionsDatabaseProgramm.PopupMessage.PlacementTarget = OptionsDatabaseProgramm.TBDatabaseName;
OptionsDatabaseProgramm.PopupMessage.IsOpen = true;
return;
}
else if (OptionsDatabaseProgramm.TBServerName.Text.Length > 40)
{
if (TreeViewProperties.SelectedItem != TreeViewItemDatabase)
{
TreeViewItemDatabase.IsSelected = true;
}
OptionsDatabaseProgramm.LPopupMessage.Content = "Имя сервера не может быть длинее 40 символов.";
OptionsDatabaseProgramm.PopupMessage.PlacementTarget = OptionsDatabaseProgramm.TBServerName;
OptionsDatabaseProgramm.PopupMessage.IsOpen = true;
return;
}
if (!(bool)OptionsDatabaseProgramm.RBSecuritySSPI.IsChecked)
{
if (OptionsDatabaseProgramm.TBUserName.Text.Length > 40)
{
if (TreeViewProperties.SelectedItem != TreeViewItemDatabase)
{
TreeViewItemDatabase.IsSelected = true;
}
OptionsDatabaseProgramm.LPopupMessage.Content = "Имя пользователя не может быть длинее 40 символов.";
OptionsDatabaseProgramm.PopupMessage.PlacementTarget = OptionsDatabaseProgramm.TBUserName;
OptionsDatabaseProgramm.PopupMessage.IsOpen = true;
return;
}
else if (OptionsDatabaseProgramm.TBUserPassword.Text.Length > 40)
{
if (TreeViewProperties.SelectedItem != TreeViewItemDatabase)
{
TreeViewItemDatabase.IsSelected = true;
}
OptionsDatabaseProgramm.LPopupMessage.Content = "Пароль не может быть длинее 40 символов.";
OptionsDatabaseProgramm.PopupMessage.PlacementTarget = OptionsDatabaseProgramm.TBUserPassword;
OptionsDatabaseProgramm.PopupMessage.IsOpen = true;
return;
}
}
if (OldUseDatabase != (bool)OptionsDatabaseProgramm.chbUseDatabase.IsChecked)
{
application.ConfigProgramBin.UseDatabase = (bool)OptionsDatabaseProgramm.chbUseDatabase.IsChecked;
}
if (OldCreateFolder != OptionsGeneralProgramm.CheckBoxCreateFolder.IsChecked || OldPathCreateProject != OptionsGeneralProgramm.TextBoxBrowseProject.Text)
{
application.ConfigProgramBin.PathBrowseProject = OptionsGeneralProgramm.TextBoxBrowseProject.Text;
application.ConfigProgramBin.CreateFolder = (bool)OptionsGeneralProgramm.CheckBoxCreateFolder.IsChecked;
}
if (OldSQLServerName != OptionsDatabaseProgramm.TBServerName.Text || OldSQLDatabaseName != OptionsDatabaseProgramm.TBDatabaseName.Text)
{
application.ConfigProgramBin.SQLServerName = OptionsDatabaseProgramm.TBServerName.Text;
application.ConfigProgramBin.SQLDatabaseName = OptionsDatabaseProgramm.TBDatabaseName.Text;
}
if (OldSQLSecuritySSPI != OptionsDatabaseProgramm.RBSecuritySSPI.IsChecked)
{
application.ConfigProgramBin.SQLSecuritySSPI = (bool)OptionsDatabaseProgramm.RBSecuritySSPI.IsChecked;
}
if (OptionsDatabaseProgramm.RBSecuritySSPI.IsChecked == false)
{
if (OldSQLUserName != OptionsDatabaseProgramm.TBUserName.Text || OldSQLPassword != OptionsDatabaseProgramm.TBUserPassword.Text)
{
application.ConfigProgramBin.SQLUserName = OptionsDatabaseProgramm.TBUserName.Text;
application.ConfigProgramBin.SQLPassword = OptionsDatabaseProgramm.TBUserPassword.Text;
}
}
this.Close();
}
private void BLoadTable_Click(object sender, RoutedEventArgs e)
{
AppWPF app = (AppWPF)Application.Current;
MainWindow window = (MainWindow)Application.Current.MainWindow;
try
{
if (app.ConfigProgramBin.UseDatabase)
{
DateTime form = DataPickerForm.SelectedDate.Value;
form = form.AddHours(TimePickerForm.Value.Value.Hour);
form = form.AddMinutes(TimePickerForm.Value.Value.Minute);
form = form.AddSeconds(TimePickerForm.Value.Value.Second);
DateTime to = DataPickerTo.SelectedDate.Value;
to = to.AddHours(TimePickerTo.Value.Value.Hour);
to = to.AddMinutes(TimePickerTo.Value.Value.Minute);
to = to.AddSeconds(TimePickerTo.Value.Value.Second);
string format = "yyyy/MM/dd HH:mm:ss";
string colSelect = null;
List <string> colTableName = new List <string>();
if (LBTables.SelectedItems.Count >= 2)
{
int count = 0;
while (true)
{
colSelect = " WHERE " + "\"Time\"" + " BETWEEN " + "'" + form.ToString(format) + "'" + " AND " + "'" + to.ToString(format) + "'";
colTableName.Add(LBTables.SelectedItems[count].ToString());
count++;
if (count == LBTables.SelectedItems.Count)
{
WindowArchiveTable wArchiveTable = new WindowArchiveTable(colSelect, colTableName);
wArchiveTable.Owner = Application.Current.MainWindow;
wArchiveTable.Show();
break;
}
}
}
else
{
colSelect = "select * from " + "\"" + LBTables.SelectedItem.ToString().Replace(' ', '_') + "\"" + " WHERE " + "\"Time\"" + " BETWEEN " + "'" + form.ToString(format) + "'" + " AND " + "'" + to.ToString(format) + "'";
colTableName.Add(LBTables.SelectedItem.ToString());
WindowArchiveTable wArchiveChart = new WindowArchiveTable(colSelect, colTableName);
wArchiveChart.Owner = Application.Current.MainWindow;
wArchiveChart.Show();
}
}
}
catch (SystemException ex)
{
if (window.CollectionMessage.Count > 300)
{
window.CollectionMessage.RemoveAt(0);
window.CollectionMessage.Insert(298, "Сообщение " + " : " + "Ошибка в окне Архива " + ex.Message + " " + DateTime.Now);
}
else
{
window.CollectionMessage.Add("Сообщение " + " : " + "Ошибка в окне Архива " + ex.Message + " " + DateTime.Now);
}
//if (ex is SqlException)
//{
// SqlException sqlex = ex as SqlException;
// foreach (SqlError er in sqlex.Errors)
// {
// if (window.WindowErrorMessages.LBMessageError.Text.Length > 0)
// {
// window.CountLineTextMessage++;
// window.WindowErrorMessages.LBMessageError.Text += "\n" + "Сообщение " + window.CountLineTextMessage.ToString() + " : " + "Ошибка в окне Архива " + er.Message + " " + DateTime.Now;
// }
// else
// {
// window.CountLineTextMessage++;
// window.WindowErrorMessages.LBMessageError.Text = "Сообщение " + window.CountLineTextMessage.ToString() + " : " + "Ошибка в окне Архива " + er.Message + " " + DateTime.Now;
// }
// }
//}
}
//SqlConnectionStringBuilder Sqlbuilder = new SqlConnectionStringBuilder();
//Sqlbuilder.DataSource = app.ConfigProgramBin.SQLServerName;
//Sqlbuilder.InitialCatalog = app.ConfigProgramBin.SQLDatabaseName;
//if (((AppWPF)Application.Current).ConfigProgramBin.SQLSecuritySSPI)
//{
// Sqlbuilder.IntegratedSecurity = true;
//}
//else
//{
// Sqlbuilder.UserID = app.ConfigProgramBin.SQLUserName;
// Sqlbuilder.Password = app.ConfigProgramBin.SQLPassword;
//}
e.Handled = true;
}
private void BGetOptionalData_Click(object sender, RoutedEventArgs e)
{
AppWPF app = (AppWPF)Application.Current;
MainWindow window = (MainWindow)Application.Current.MainWindow;
try
{
if (app.ConfigProgramBin.UseDatabase)
{
DateTime form = DataPickerForm.SelectedDate.Value;
form = form.AddHours(TimePickerForm.Value.Value.Hour);
form = form.AddMinutes(TimePickerForm.Value.Value.Minute);
form = form.AddSeconds(TimePickerForm.Value.Value.Second);
DateTime to = DataPickerTo.SelectedDate.Value;
to = to.AddHours(TimePickerTo.Value.Value.Hour);
to = to.AddMinutes(TimePickerTo.Value.Value.Minute);
to = to.AddSeconds(TimePickerTo.Value.Value.Second);
string format = "yyyy/MM/dd HH:mm:ss";
List <string> colSelect = new List <string>();
List <string> colTableName = new List <string>();
string[] sOptional = new string[5];
if ((bool)CHBAverage.IsChecked)
{
sOptional[0] = "avg";
}
if ((bool)CHBMax.IsChecked)
{
sOptional[1] = "max";
}
if ((bool)CHBMin.IsChecked)
{
sOptional[2] = "min";
}
if ((bool)CHBSum.IsChecked)
{
sOptional[3] = "sum";
}
if ((bool)CHBIntegralSum.IsChecked)
{
sOptional[4] = "integralsum";
}
if (LBTables.SelectedItems.Count >= 2)
{
int count = 0;
while (true)
{
colSelect.Add("select * from " + "\"" + LBTables.SelectedItems[count].ToString().Replace(' ', '_') + "\"" + " WHERE " + "\"Time\"" + " BETWEEN " + "'" + form.ToString(format) + "'" + " AND " + "'" + to.ToString(format) + "'");
colTableName.Add(LBTables.SelectedItems[count].ToString());
count++;
if (count == LBTables.SelectedItems.Count)
{
WindowGetOptionalData wArchiveOptional = new WindowGetOptionalData(colSelect, colTableName, sOptional, form.ToString(format), to.ToString(format));
wArchiveOptional.Owner = Application.Current.MainWindow;
wArchiveOptional.Show();
break;
}
}
}
else
{
colSelect.Add("select * from " + "\"" + LBTables.SelectedItem.ToString().Replace(' ', '_') + "\"" + " Where \"Value\" = (select (\"Value\") from " + "\"" + LBTables.SelectedItem.ToString().Replace(' ', '_') + "\"" + " WHERE " + "\"Time\"" + " BETWEEN " + "'" + form.ToString(format) + "'" + " AND " + "'" + to.ToString(format) + "')");
colTableName.Add(LBTables.SelectedItem.ToString());
WindowGetOptionalData wArchiveOptional = new WindowGetOptionalData(colSelect, colTableName, sOptional, form.ToString(format), to.ToString(format));
wArchiveOptional.Owner = Application.Current.MainWindow;
wArchiveOptional.Show();
}
}
}
catch (SystemException ex)
{
if (window.CollectionMessage.Count > 300)
{
window.CollectionMessage.RemoveAt(0);
window.CollectionMessage.Insert(298, "Сообщение " + " : " + "Ошибка в окне Архива " + ex.Message + " " + DateTime.Now);
}
else
{
window.CollectionMessage.Add("Сообщение " + " : " + "Ошибка в окне Архива " + ex.Message + " " + DateTime.Now);
}
}
e.Handled = true;
}
public WindowArchive()
{
InitializeComponent();
AppWPF app = (AppWPF)Application.Current;
MainWindow window = (MainWindow)Application.Current.MainWindow;
if (app.ConfigProgramBin.UseDatabase)
{
//SqlConnectionStringBuilder Sqlbuilder = new SqlConnectionStringBuilder();
//Sqlbuilder.DataSource = app.ConfigProgramBin.SQLServerName;
//Sqlbuilder.InitialCatalog = app.ConfigProgramBin.SQLDatabaseName;
//if (((AppWPF)Application.Current).ConfigProgramBin.SQLSecuritySSPI)
//{
// Sqlbuilder.IntegratedSecurity = true;
//}
//else
//{
// Sqlbuilder.UserID = app.ConfigProgramBin.SQLUserName;
// Sqlbuilder.Password = app.ConfigProgramBin.SQLPassword;
//}
string connstring = String.Format("Server={0};Port={1};" +
"User Id={2};Password={3};Database={4};",
app.ConfigProgramBin.SQLServerName, 5432, app.ConfigProgramBin.SQLUserName,
app.ConfigProgramBin.SQLPassword, app.ConfigProgramBin.SQLDatabaseName);
Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(connstring);
string sql = "Select table_name FROM information_schema.tables WHERE table_schema = 'public'";
Npgsql.NpgsqlCommand command = null;
try
{
conn.Open();
command = new Npgsql.NpgsqlCommand(sql, conn);
Npgsql.NpgsqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
string table = reader.GetString(0);
CollectionTables.Add(table.Replace('_', ' '));
}
reader.Close();
LBTables.ItemsSource = CollectionTables;
LBTables.SelectionMode = SelectionMode.Multiple;
Binding bindingLoadTable = new Binding();
bindingLoadTable.Source = LBTables;
bindingLoadTable.Path = new PropertyPath("SelectedItem");
bindingLoadTable.Converter = new RemoveButtonConverter();
Binding bindingLoadTableChart = new Binding();
bindingLoadTableChart.Source = LBTables;
bindingLoadTableChart.Path = new PropertyPath("SelectedItem");
bindingLoadTableChart.Converter = new RemoveButtonConverter();
Binding bindingGetOptionalData = new Binding();
bindingGetOptionalData.Source = LBTables;
bindingGetOptionalData.Path = new PropertyPath("SelectedItem");
bindingGetOptionalData.Converter = new RemoveButtonConverter();
Binding bindingGetOptionalData2 = new Binding();
bindingGetOptionalData2.Source = CHBAverage;
bindingGetOptionalData2.Path = new PropertyPath("IsChecked");
Binding bindingGetOptionalData3 = new Binding();
bindingGetOptionalData3.Source = CHBSum;
bindingGetOptionalData3.Path = new PropertyPath("IsChecked");
Binding bindingGetOptionalData4 = new Binding();
bindingGetOptionalData4.Source = CHBMax;
bindingGetOptionalData4.Path = new PropertyPath("IsChecked");
Binding bindingGetOptionalData5 = new Binding();
bindingGetOptionalData5.Source = CHBMin;
bindingGetOptionalData5.Path = new PropertyPath("IsChecked");
Binding bindingGetOptionalData6 = new Binding();
bindingGetOptionalData6.Source = CHBIntegralSum;
bindingGetOptionalData6.Path = new PropertyPath("IsChecked");
MultiBinding mBindingGetOptionalData = new MultiBinding();
mBindingGetOptionalData.Converter = new GetOptionalData();
mBindingGetOptionalData.Bindings.Add(bindingGetOptionalData);
mBindingGetOptionalData.Bindings.Add(bindingGetOptionalData2);
mBindingGetOptionalData.Bindings.Add(bindingGetOptionalData3);
mBindingGetOptionalData.Bindings.Add(bindingGetOptionalData4);
mBindingGetOptionalData.Bindings.Add(bindingGetOptionalData5);
mBindingGetOptionalData.Bindings.Add(bindingGetOptionalData6);
BLoadTable.SetBinding(Button.IsEnabledProperty, bindingLoadTable);
BLoadTableChart.SetBinding(Button.IsEnabledProperty, bindingLoadTableChart);
BGetOptionalData.SetBinding(Button.IsEnabledProperty, mBindingGetOptionalData);
DataPickerForm.DisplayDateEnd = DateTime.Now;
DataPickerForm.ToolTip = "Диапазон даты от";
DataPickerForm.SelectedDateChanged += DataPickerForm_SelectedDateChanged;
DataPickerForm.SelectedDate = DateTime.Now;
TimePickerForm.ValueChanged += TimePickerForm_ValueChanged;
TimePickerForm.ToolTip = "Диапазон времени от";
TimePickerForm.Format = TimeFormat.Custom;
TimePickerForm.FormatString = "HH:mm:ss";
if (DateTime.Now.Hour == 0)
{
TimePickerForm.Value = DateTime.Now;
}
else
{
TimePickerForm.Value = DateTime.Now.AddHours(-1);
}
DataPickerTo.DisplayDateEnd = DateTime.Now;
DataPickerTo.ToolTip = "Диапазон даты до";
DataPickerTo.SelectedDateChanged += DataPickerTo_SelectedDateChanged;
DataPickerTo.SelectedDate = DateTime.Now;
TimePickerTo.ValueChanged += TimePickerTo_ValueChanged;
TimePickerTo.ToolTip = "Диапазон времени до";
TimePickerTo.Format = TimeFormat.Custom;
TimePickerTo.FormatString = "HH:mm:ss";
TimePickerTo.Value = DateTime.Now;
}
catch (SystemException ex)
{
if (window.CollectionMessage.Count > 300)
{
window.CollectionMessage.RemoveAt(0);
window.CollectionMessage.Insert(298, "Сообщение " + " : " + "Ошибка в окне Архива " + ex.Message + " " + DateTime.Now);
}
else
{
window.CollectionMessage.Add("Сообщение " + " : " + "Ошибка в окне Архива " + ex.Message + " " + DateTime.Now);
}
//if (ex is SqlException)
//{
// SqlException sqlex = ex as SqlException;
// foreach (SqlError er in sqlex.Errors)
// {
// if (window.WindowErrorMessages.LBMessageError.Text.Length > 0)
// {
// window.CountLineTextMessage++;
// window.WindowErrorMessages.LBMessageError.Text += "\n" + "Сообщение " + window.CountLineTextMessage.ToString() + " : " + "Ошибка в окне Архива " + er.Message + " " + DateTime.Now;
// }
// else
// {
// window.CountLineTextMessage++;
// window.WindowErrorMessages.LBMessageError.Text = "Сообщение " + window.CountLineTextMessage.ToString() + " : " + "Ошибка в окне Архива " + er.Message + " " + DateTime.Now;
// }
// }
//}
}
finally
{
conn.Close();
conn.Dispose();
if (command != null)
{
command.Dispose();
}
}
}
}
public WindowArchiveChart(List <string> colSelect, List <string> colTableName)
{
InitializeComponent();
AppWPF app = (AppWPF)Application.Current;
MainWindow window = (MainWindow)Application.Current.MainWindow;
if (app.ConfigProgramBin.UseDatabase)
{
//SqlConnectionStringBuilder Sqlbuilder = new SqlConnectionStringBuilder();
//Sqlbuilder.DataSource = app.ConfigProgramBin.SQLServerName;
//Sqlbuilder.InitialCatalog = app.ConfigProgramBin.SQLDatabaseName;
//if (((AppWPF)Application.Current).ConfigProgramBin.SQLSecuritySSPI)
//{
// Sqlbuilder.IntegratedSecurity = true;
//}
//else
//{
// Sqlbuilder.UserID = app.ConfigProgramBin.SQLUserName;
// Sqlbuilder.Password = app.ConfigProgramBin.SQLPassword;
//}
string connstring = String.Format("Server={0};Port={1};" +
"User Id={2};Password={3};Database={4};",
app.ConfigProgramBin.SQLServerName, 5432, app.ConfigProgramBin.SQLUserName,
app.ConfigProgramBin.SQLPassword, app.ConfigProgramBin.SQLDatabaseName);
Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(connstring);
try
{
conn.Open();
PlotModel plotModel = new PlotModel();
if (colSelect.Count >= 2)
{
int count = 0;
while (true)
{
DataTable dataTable = new DataTable();
Npgsql.NpgsqlDataAdapter adapter = new Npgsql.NpgsqlDataAdapter(colSelect[count], conn);
adapter.Fill(dataTable);
adapter.Dispose();
OxyPlot.Series.LineSeries line = new OxyPlot.Series.LineSeries()
{
CanTrackerInterpolatePoints = false,
Title = string.Format(colTableName[count]),
Smooth = false,
TrackerFormatString = "{0}" + Environment.NewLine + "{3} {4}" + Environment.NewLine + "{1} {2:dd/MM/yyyy HH:mm:ss}"
};
DataView data = dataTable.DefaultView;
foreach (DataRowView rowView in data)
{
DataRow row = rowView.Row;
DateTime dt = (DateTime)row.ItemArray[1];
double d = Convert.ToDouble(row.ItemArray[0]);
line.Points.Add(new DataPoint(OxyPlot.Axes.DateTimeAxis.ToDouble(dt), d));
}
plotModel.Series.Add(line);
count++;
if (count == colSelect.Count)
{
break;
}
}
}
else
{
DataTable dataTable = new DataTable();
Npgsql.NpgsqlDataAdapter adapter = new Npgsql.NpgsqlDataAdapter(colSelect[0], conn);
adapter.Fill(dataTable);
adapter.Dispose();
OxyPlot.Series.LineSeries line = new OxyPlot.Series.LineSeries()
{
CanTrackerInterpolatePoints = false,
Title = string.Format(colTableName[0]),
Smooth = false,
TrackerFormatString = "{0}" + Environment.NewLine + "{3} {4}" + Environment.NewLine + "{1} {2:dd/MM/yyyy HH:mm:ss}"
};
DataView data = dataTable.DefaultView;
foreach (DataRowView rowView in data)
{
DataRow row = rowView.Row;
DateTime dt = (DateTime)row.ItemArray[1];
double d = Convert.ToDouble(row.ItemArray[0]);
line.Points.Add(new DataPoint(OxyPlot.Axes.DateTimeAxis.ToDouble(dt), d));
}
plotModel.Series.Add(line);
}
plotModel.LegendTitle = "Легенда";
plotModel.LegendOrientation = LegendOrientation.Horizontal;
plotModel.LegendPlacement = LegendPlacement.Outside;
plotModel.LegendPosition = LegendPosition.TopRight;
plotModel.LegendBackground = OxyColor.FromAColor(200, OxyColors.White);
plotModel.LegendBorder = OxyColors.Black;
var dateAxis = new OxyPlot.Axes.DateTimeAxis(OxyPlot.Axes.AxisPosition.Bottom, "Дата", "dd/MM HH:mm")
{
MajorGridlineStyle = LineStyle.Solid, MinorGridlineStyle = LineStyle.Solid, IntervalLength = 65
};
plotModel.Axes.Add(dateAxis);
var valueAxis = new OxyPlot.Axes.LinearAxis(AxisPosition.Left)
{
MajorGridlineStyle = LineStyle.Solid, MinorGridlineStyle = LineStyle.Solid, Title = "Значение"
};
valueAxis.MaximumPadding = 0.3;
valueAxis.MinimumPadding = 0.3;
plotModel.Axes.Add(valueAxis);
Plot = new Plot();
Plot.SetValue(Grid.RowProperty, 1);
Plot.Model = plotModel;
Plot.MinHeight = 100;
Plot.MinWidth = 100;
GridMain.Children.Add(Plot);
}
catch (SystemException ex)
{
if (window.CollectionMessage.Count > 300)
{
window.CollectionMessage.RemoveAt(0);
window.CollectionMessage.Insert(298, "Сообщение " + " : " + "Ошибка в окне Архива " + ex.Message + " " + DateTime.Now);
}
else
{
window.CollectionMessage.Add("Сообщение " + " : " + "Ошибка в окне Архива " + ex.Message + " " + DateTime.Now);
}
//if (ex is SqlException)
//{
// SqlException sqlex = ex as SqlException;
// foreach (SqlError er in sqlex.Errors)
// {
// if (window.WindowErrorMessages.LBMessageError.Text.Length > 0)
// {
// window.CountLineTextMessage++;
// window.WindowErrorMessages.LBMessageError.Text += "\n" + "Сообщение " + window.CountLineTextMessage.ToString() + " : " + "Ошибка в окне Архива " + er.Message + " " + DateTime.Now;
// }
// else
// {
// window.CountLineTextMessage++;
// window.WindowErrorMessages.LBMessageError.Text = "Сообщение " + window.CountLineTextMessage.ToString() + " : " + "Ошибка в окне Архива " + er.Message + " " + DateTime.Now;
// }
// }
//}
}
finally
{
conn.Close();
conn.Dispose();
}
}
}
private void BNewDatabase_Click(object sender, RoutedEventArgs e)
{
e.Handled = true;
AppWPF app = (AppWPF)Application.Current;
if (TBNameDatabase.Text.Length > 40)
{
LPopupMessage.Content = "Имя базы данных не может быть длинее 40 символов.";
PopupMessage.PlacementTarget = TBNameDatabase;
PopupMessage.IsOpen = true;
return;
}
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
builder.DataSource = ServerName;
if (SSPI)
{
builder.IntegratedSecurity = true;
}
else
{
builder.UserID = app.ConfigProgramBin.SQLUserName;
builder.Password = app.ConfigProgramBin.SQLPassword;
}
string str = null;
string newSQLDatabaseName = TBNameDatabase.Text;
if (TBNameDatabase.Text == null || TBNameDatabase.Text.Length == 0)
{
LPopupMessage.Content = "Имя базы данных не может быть пустой.";
PopupMessage.PlacementTarget = TBNameDatabase;
PopupMessage.IsOpen = true;
return;
}
else
{
str = "CREATE DATABASE [" + TBNameDatabase.Text + "]";
}
using (SqlConnection cn = new SqlConnection())
{
cn.ConnectionString = builder.ConnectionString;
try
{
SqlCommand myCommand = new SqlCommand(str, cn);
cn.Open();
myCommand.ExecuteNonQuery();
CBDatabases.Items.Add(TBNameDatabase.Text);
CBDatabases.SelectedItem = TBNameDatabase.Text;
}
catch (SystemException ex)
{
LPopupMessage.Content = ex.Message;
PopupMessage.PlacementTarget = TBNameDatabase;
PopupMessage.IsOpen = true;
return;
}
}
this.Close();
}
public WindowArchiveTable(string colSelect, List <string> colTableName)
{
InitializeComponent();
AppWPF app = (AppWPF)Application.Current;
MainWindow window = (MainWindow)Application.Current.MainWindow;
if (app.ConfigProgramBin.UseDatabase)
{
//SqlConnectionStringBuilder Sqlbuilder = new SqlConnectionStringBuilder();
//Sqlbuilder.DataSource = app.ConfigProgramBin.SQLServerName;
//Sqlbuilder.InitialCatalog = app.ConfigProgramBin.SQLDatabaseName;
//if (((AppWPF)Application.Current).ConfigProgramBin.SQLSecuritySSPI)
//{
// Sqlbuilder.IntegratedSecurity = true;
//}
//else
//{
// Sqlbuilder.UserID = app.ConfigProgramBin.SQLUserName;
// Sqlbuilder.Password = app.ConfigProgramBin.SQLPassword;
//}
string connstring = String.Format("Server={0};Port={1};" +
"User Id={2};Password={3};Database={4};",
app.ConfigProgramBin.SQLServerName, 5432, app.ConfigProgramBin.SQLUserName,
app.ConfigProgramBin.SQLPassword, app.ConfigProgramBin.SQLDatabaseName);
Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(connstring);
int count = 0;
while (true)
{
colTableName[count] = colTableName[count].Replace(' ', '_');
count++;
if (count == colTableName.Count)
{
break;
}
}
try
{
conn.Open();
if (colTableName.Count >= 2)
{
count = 0;
string tables = "SELECT ";
while (true)
{
tables += colTableName[count] + @".""Value"", ";
count++;
if (count == colTableName.Count)
{
break;
}
}
count = 0;
tables += "COALESCE(";
while (true)
{
if (count + 1 == colTableName.Count)
{
tables += colTableName[count] + @".""Time"") AS Время FROM " + colTableName[0];
}
else
{
tables += colTableName[count] + @".""Time"", ";
}
count++;
if (count == colTableName.Count)
{
break;
}
}
count = 1;
while (true)
{
if (colTableName.Count == 2)
{
tables += " FULL OUTER JOIN " + colTableName[count] + @" USING(""Time"")";
break;
}
else
{
tables += " FULL OUTER JOIN " + colTableName[count] + @" USING(""Time"")";
}
count++;
if (count == colTableName.Count)
{
break;
}
}
tables += colSelect;
Npgsql.NpgsqlDataAdapter adapter = new Npgsql.NpgsqlDataAdapter(tables, conn);
adapter.Fill(DataTable);
adapter.Dispose();
int countR = 0;
//List<int> colTimes = new List<int>();
//while (true)
//{
// if (DataTable.Columns[countR].ColumnName.LastIndexOf("Time") > 0)
// {
// DataTable.Columns.RemoveAt(countR);
// }
// countR++;
// if (countR == DataTable.Columns.Count)
// {
// break;
// }
//}
countR = 0;
int countInRow = 0;
DataRow rowNext;
DataRow row;
DataTable.DefaultView.Sort = "Время asc";
DataTable = DataTable.DefaultView.ToTable();
if (DataTable.Rows.Count > 0)
{
while (true)
{
row = DataTable.Rows[countR];
if (countR + 1 == DataTable.Rows.Count)
{
break;
}
rowNext = DataTable.Rows[countR + 1];
DateTime dt1 = (DateTime)row[row.ItemArray.Length - 1];
DateTime dt2 = (DateTime)rowNext[row.ItemArray.Length - 1];
if (dt1.Year == dt2.Year && dt1.Month == dt2.Month && dt1.Day == dt2.Day && dt1.Hour == dt2.Hour && dt1.Minute == dt2.Minute && dt1.Second == dt2.Second)
{
while (true)
{
if (row[countInRow] is DBNull && !(rowNext[countInRow] is DBNull))
{
row[countInRow] = rowNext[countInRow];
}
countInRow++;
if (countInRow == row.ItemArray.Length)
{
countInRow = 0;
DataTable.Rows.RemoveAt(countR + 1);
break;
}
}
}
countR++;
if (countR == DataTable.Rows.Count)
{
break;
}
}
}
count = 0;
while (true)
{
if (count == DataTable.Columns.Count - 1)
{
break;
}
colTableName[count] = colTableName[count].Replace('_', ' ');
DataTable.Columns[count].ColumnName = colTableName[count];
count++;
if (count == DataTable.Rows.Count)
{
break;
}
}
DGTable.IsReadOnly = true;
DGTable.ItemsSource = DataTable.DefaultView;
DGTable.AutoGenerateColumns = true;
DGTable.MinHeight = 400;
DGTable.MinWidth = 300;
}
else
{
Npgsql.NpgsqlDataAdapter adapter = new Npgsql.NpgsqlDataAdapter(colSelect, conn);
adapter.Fill(DataTable);
DataTable.Columns["Value"].ColumnName = colTableName[0].Replace('_', ' ');
DataTable.Columns["Time"].ColumnName = "Время";
adapter.Dispose();
DGTable.IsReadOnly = true;
DGTable.ItemsSource = DataTable.DefaultView;
DGTable.AutoGenerateColumns = true;
DGTable.MinHeight = 400;
DGTable.MinWidth = 300;
}
}
catch (SystemException ex)
{
if (window.CollectionMessage.Count > 300)
{
window.CollectionMessage.RemoveAt(0);
window.CollectionMessage.Insert(298, "Сообщение " + " : " + "Ошибка в окне Архива " + ex.Message + " " + DateTime.Now);
}
else
{
window.CollectionMessage.Add("Сообщение " + " : " + "Ошибка в окне Архива " + ex.Message + " " + DateTime.Now);
}
//if (ex is SqlException)
//{
// SqlException sqlex = ex as SqlException;
// foreach (SqlError er in sqlex.Errors)
// {
// if (window.WindowErrorMessages.LBMessageError.Text.Length > 0)
// {
// window.CountLineTextMessage++;
// window.WindowErrorMessages.LBMessageError.Text += "\n" + "Сообщение " + window.CountLineTextMessage.ToString() + " : " + "Ошибка в окне Архива " + er.Message + " " + DateTime.Now;
// }
// else
// {
// window.CountLineTextMessage++;
// window.WindowErrorMessages.LBMessageError.Text = "Сообщение " + window.CountLineTextMessage.ToString() + " : " + "Ошибка в окне Архива " + er.Message + " " + DateTime.Now;
// }
// }
//}
}
finally
{
conn.Close();
conn.Dispose();
}
}
}
public WindowGetOptionalData(List <string> colSelect, List <string> colTableName, string[] sOptionals, string formTime, string toTime)
{
InitializeComponent();
DataTable DataTable = new DataTable();
AppWPF app = (AppWPF)Application.Current;
MainWindow window = (MainWindow)Application.Current.MainWindow;
if (app.ConfigProgramBin.UseDatabase)
{
string connstring = String.Format("Server={0};Port={1};" +
"User Id={2};Password={3};Database={4};",
app.ConfigProgramBin.SQLServerName, 5432, app.ConfigProgramBin.SQLUserName,
app.ConfigProgramBin.SQLPassword, app.ConfigProgramBin.SQLDatabaseName);
Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(connstring);
int count = 0;
while (true)
{
colTableName[count] = colTableName[count].Replace(' ', '_');
count++;
if (count == colTableName.Count)
{
break;
}
}
try
{
conn.Open();
DataTable dt = new System.Data.DataTable();
dt.Columns.Add("Value", typeof(decimal));
dt.Columns.Add("Time", typeof(DateTime));
dt.Columns.Add("Опция", typeof(string));
DataTable.Columns.Add("Value", typeof(decimal));
DataTable.Columns.Add("Time", typeof(DateTime));
DataTable.Columns.Add("Опция", typeof(string));
object[] obj = new object[3];
int countOptional = 0;
DateTime dtForm = DateTime.Parse(formTime);
DateTime dtTo = DateTime.Parse(toTime);
int intCount = 1;
foreach (string str in sOptionals)
{
if (countOptional == 0)
{
if (str == "max")
{
int i = colSelect[0].IndexOf("(select ");
string s = colSelect[0].Insert(i + 8, "max");
Npgsql.NpgsqlDataAdapter adapter = new Npgsql.NpgsqlDataAdapter(s, conn);
adapter.FillSchema(DataTable, SchemaType.Source);
adapter.Fill(DataTable);
adapter.Dispose();
foreach (DataRow dr in DataTable.Rows)
{
obj[0] = dr.ItemArray[0];
obj[1] = dr.ItemArray[1];
obj[2] = "Максимальное значение";
dr.ItemArray = obj;
}
int index = 0;
while (true)
{
DataRow dr = DataTable.Rows[index];
if ((DateTime)dr.ItemArray[1] < dtForm || (DateTime)dr.ItemArray[1] > dtTo)
{
DataTable.Rows.Remove(dr);
index--;
}
index++;
if (DataTable.Rows.Count == index)
{
break;
}
}
countOptional++;
}
else if (str == "min")
{
int i = colSelect[0].IndexOf("(select ");
string s = colSelect[0].Insert(i + 8, "min");
Npgsql.NpgsqlDataAdapter adapter = new Npgsql.NpgsqlDataAdapter(s, conn);
adapter.FillSchema(DataTable, SchemaType.Source);
adapter.Fill(DataTable);
adapter.Dispose();
foreach (DataRow dr in DataTable.Rows)
{
obj[0] = dr.ItemArray[0];
obj[1] = dr.ItemArray[1];
obj[2] = "Минимальное значение";
dr.ItemArray = obj;
}
int index = 0;
while (true)
{
DataRow dr = DataTable.Rows[index];
if ((DateTime)dr.ItemArray[1] < dtForm || (DateTime)dr.ItemArray[1] > dtTo)
{
DataTable.Rows.Remove(dr);
index--;
}
index++;
if (DataTable.Rows.Count == index)
{
break;
}
}
countOptional++;
}
else if (str == "avg")
{
int i = colSelect[0].IndexOf("*");
string s = colSelect[0].Remove(i, 1);
s = s.Insert(7, "avg(\"Value\") ");
i = s.IndexOf("Where");
s = s.Remove(i + 6);
s = s + " \"Time\"" + " BETWEEN " + "'" + formTime + "'" + " AND " + "'" + toTime + "'";
Npgsql.NpgsqlDataAdapter adapter = new Npgsql.NpgsqlDataAdapter(s, conn);
adapter.FillSchema(DataTable, SchemaType.Source);
adapter.Fill(DataTable);
adapter.Dispose();
if (DataTable.Rows.Count != 0)
{
foreach (DataRow dr in DataTable.Rows)
{
obj[0] = dr.ItemArray[3];
obj[1] = DateTime.Now;
obj[2] = "Среднее значение";
dr.ItemArray = obj;
}
DataTable.Columns.RemoveAt(3);
}
else
{
}
countOptional++;
}
else if (str == "sum")
{
int i = colSelect[0].IndexOf("*");
string s = colSelect[0].Remove(i, 1);
s = s.Insert(7, "sum(\"Value\") ");
i = s.IndexOf("Where");
s = s.Remove(i + 6);
s = s + " \"Time\"" + " BETWEEN " + "'" + formTime + "'" + " AND " + "'" + toTime + "'";
Npgsql.NpgsqlDataAdapter adapter = new Npgsql.NpgsqlDataAdapter(s, conn);
adapter.Fill(DataTable);
adapter.Dispose();
if (DataTable.Rows.Count != 0)
{
foreach (DataRow dr in DataTable.Rows)
{
obj[0] = dr.ItemArray[3];
obj[1] = DateTime.Now;
obj[2] = "Арифметическая сумма";
dr.ItemArray = obj;
}
DataTable.Columns.RemoveAt(3);
}
else
{
DataRow datarow = DataTable.NewRow();
DataTable.Clear();
obj[0] = 0M;
obj[1] = DateTime.Now;
obj[2] = "Арифметическая сумма. Пустая таблица.";
datarow.ItemArray = obj;
DataTable.Rows.Add(datarow);
}
countOptional++;
}
else if (str == "integralsum")
{
int i = colSelect[0].IndexOf("Where");
string s = colSelect[0].Remove(i + 6);
s = s + " \"Time\"" + " BETWEEN " + "'" + formTime + "'" + " AND " + "'" + toTime + "'";
Npgsql.NpgsqlDataAdapter adapter = new Npgsql.NpgsqlDataAdapter(s, conn);
adapter.Fill(DataTable);
adapter.Dispose();
DateTime form = new DateTime();
DateTime to = new DateTime();
decimal d = 0;
int init = 0;
decimal prevItem = 0;
TimeSpan ts;
if (DataTable.Rows.Count != 0)
{
foreach (DataRow dr in DataTable.Rows)
{
if (intCount == 1)
{
form = (DateTime)dr.ItemArray[1];
prevItem = (decimal)dr.ItemArray[0];
intCount++;
}
else if (intCount == 2)
{
if (init == 0)
{
to = (DateTime)dr.ItemArray[1];
ts = to.Subtract(form);
form = to;
d = ((decimal)ts.TotalSeconds / 3600) * prevItem;
prevItem = (decimal)dr.ItemArray[0];
init = 1;
}
else
{
to = (DateTime)dr.ItemArray[1];
ts = to.Subtract(form);
form = to;
d += ((decimal)ts.TotalSeconds / 3600) * prevItem;
prevItem = (decimal)dr.ItemArray[0];
}
}
}
DataRow datarow = DataTable.NewRow();
DataTable.Clear();
obj[0] = d;
obj[1] = DateTime.Now;
obj[2] = "Интегральная сумма";
datarow.ItemArray = obj;
DataTable.Rows.Add(datarow);
}
else
{
DataRow datarow = DataTable.NewRow();
DataTable.Clear();
obj[1] = DateTime.Now;
obj[2] = "Интегральная сумма. Пустая таблица.";
datarow.ItemArray = obj;
DataTable.Rows.Add(datarow);
}
countOptional++;
}
}
else
{
if (str == "max")
{
int i = colSelect[0].IndexOf("(select ");
string s = colSelect[0].Insert(i + 8, "max");
Npgsql.NpgsqlDataAdapter adapter = new Npgsql.NpgsqlDataAdapter(s, conn);
adapter.FillSchema(dt, SchemaType.Source);
dt.Rows.Clear();
adapter.Fill(dt);
adapter.Dispose();
foreach (DataRow dr in dt.Rows)
{
obj[0] = dr.ItemArray[0];
obj[1] = dr.ItemArray[1];
obj[2] = "Максимальное значение";
dr.ItemArray = obj;
}
int index = 0;
while (true)
{
DataRow dr = dt.Rows[index];
if ((DateTime)dr.ItemArray[1] < dtForm || (DateTime)dr.ItemArray[1] > dtTo)
{
dt.Rows.Remove(dr);
index--;
}
index++;
if (dt.Rows.Count == index)
{
break;
}
}
DataTable.Merge(dt);
countOptional++;
}
else if (str == "min")
{
int i = colSelect[0].IndexOf("(select ");
string s = colSelect[0].Insert(i + 8, "min");
Npgsql.NpgsqlDataAdapter adapter = new Npgsql.NpgsqlDataAdapter(s, conn);
adapter.FillSchema(dt, SchemaType.Source);
dt.Rows.Clear();
adapter.Fill(dt);
adapter.Dispose();
foreach (DataRow dr in dt.Rows)
{
obj[0] = dr.ItemArray[0];
obj[1] = dr.ItemArray[1];
obj[2] = "Минимальное значение";
dr.ItemArray = obj;
}
int index = 0;
while (true)
{
DataRow dr = dt.Rows[index];
if ((DateTime)dr.ItemArray[1] < dtForm || (DateTime)dr.ItemArray[1] > dtTo)
{
dt.Rows.Remove(dr);
index--;
}
index++;
if (dt.Rows.Count == index)
{
break;
}
}
DataTable.Merge(dt);
countOptional++;
}
else if (str == "avg")
{
int i = colSelect[0].IndexOf("*");
string s = colSelect[0].Remove(i, 1);
s = s.Insert(7, "avg(\"Value\") ");
i = s.IndexOf("Where");
s = s.Remove(i + 6);
s = s + " \"Time\"" + " BETWEEN " + "'" + formTime + "'" + " AND " + "'" + toTime + "'";
Npgsql.NpgsqlDataAdapter adapter = new Npgsql.NpgsqlDataAdapter(s, conn);
adapter.FillSchema(dt, SchemaType.Source);
dt.Rows.Clear();
adapter.Fill(dt);
adapter.Dispose();
dt.Columns[0].ColumnName = "Value";
foreach (DataRow dr in dt.Rows)
{
obj[0] = dr.ItemArray[0];
obj[1] = DateTime.Now;
obj[2] = "Среднее значение";
dr.ItemArray = obj;
}
dt.Columns.RemoveAt(3);
DataTable.Merge(dt);
countOptional++;
}
else if (str == "sum")
{
int i = colSelect[0].IndexOf("*");
string s = colSelect[0].Remove(i, 1);
s = s.Insert(7, "sum(\"Value\") ");
i = s.IndexOf("Where");
s = s.Remove(i + 6);
s = s + " \"Time\"" + " BETWEEN " + "'" + formTime + "'" + " AND " + "'" + toTime + "'";
Npgsql.NpgsqlDataAdapter adapter = new Npgsql.NpgsqlDataAdapter(s, conn);
dt.Rows.Clear();
adapter.Fill(dt);
adapter.Dispose();
foreach (DataRow dr in dt.Rows)
{
obj[0] = dr.ItemArray[3];
obj[1] = DateTime.Now;
obj[2] = "Сумма";
dr.ItemArray = obj;
}
dt.Columns.RemoveAt(3);
DataTable.Merge(dt);
countOptional++;
}
}
}
DataTable.Columns["Value"].ColumnName = colTableName[0].Replace('_', ' ');
DataTable.Columns["Time"].ColumnName = "Время";
DGTable.IsReadOnly = true;
DGTable.ItemsSource = DataTable.DefaultView;
DGTable.AutoGenerateColumns = true;
DGTable.MinHeight = 400;
DGTable.MinWidth = 300;
}
catch (SystemException ex)
{
conn.Close();
if (window.CollectionMessage.Count > 300)
{
window.CollectionMessage.RemoveAt(0);
window.CollectionMessage.Insert(298, "Сообщение " + " : " + "Ошибка в окне Архива " + ex.Message + " " + DateTime.Now);
}
else
{
window.CollectionMessage.Add("Сообщение " + " : " + "Ошибка в окне Архива " + ex.Message + " " + DateTime.Now);
}
}
finally
{
conn.Close();
}
}
}