private void ConnectionForm_Load(object sender, EventArgs e)
{
var connection = ConnetionToSqlServer.Default();
tbPassword.Text = connection.Password;
tbServerName.Text = connection.Server;
tbUser.Text = connection.UserName;
tbDataBase.Text = connection.DataBase;
cbSqlAut.Checked = !connection.WindowsAuthentication;
}
private IList <MessageModel> GetAllMessages()
{
IList <MessageModel> list = new List <MessageModel>();
var connection = ConnetionToSqlServer.Default();
var select = $"exec dbo.GetMessageBetweenDates '{dtpStart.Value.ToString("yyyy-MM-dd")}', '{dtpEnd.Value.ToString("yyyy-MM-dd")}'";
//Log.Debug(select);
var reader = connection.ExecuteReader(select);
if (reader != null)
{
while (reader.Read())
{
var desc = new MessageModel();
desc.Id = reader.GetGuid(0);
desc.Number = reader.GetString(1);
desc.DateStart = reader.GetDateTime(2);
desc.DateEnd = reader.GetDateTime(3);
desc.DateStartBargaining = reader.GetDateTime(4);
desc.Url = reader.GetString(5);
desc.Description = reader.GetString(6).Replace(";", ",");
desc.DateMesage = reader.GetDateTime(7);
desc.TypeOfBidding = reader.GetString(8);
list.Add(desc);
}
reader.Close();
}
foreach (var message in list)
{
select = $"exec dbo.GetLotByMessageID '{message.Id}'";
reader = connection.ExecuteReader(select);
if (reader != null)
{
while (reader.Read())
{
var lot = new LotModel()
{
Id = reader.GetGuid(0),
Number = reader.GetInt32(1),
Description = reader.GetString(2).Replace(";", ","),
StartPrice = reader.GetDouble(3),
Step = reader.GetString(4),
Deposit = reader.GetString(5).Replace(";", ","),
PriceReductionInformation = reader.GetString(6).Replace(";", ","),
PropertyClassification = reader.GetString(7).Replace(";", ",")
};
message.Lots.Add(lot);
}
reader.Close();
}
}
;
return(list);
}
private void MainForm_Load(object sender, EventArgs e)
{
ReadConfig();
var conform = new ConnectionForm();
if (conform.ShowDialog() == DialogResult.Cancel)
{
this.Close();
}
else
{
var exportSetting = new ExportConfigDataBase(fileNameConfig, ConnetionToSqlServer.Default());
exportSetting.ExecuteExport();
}
}
public void Import()
{
XmlDocument xDoc = new XmlDocument();
xDoc.Load(fileNameConfig);
XmlElement xRoot = xDoc.DocumentElement;
ConnetionToSqlServer.Default().Server = xRoot.ChildNodes[0].Attributes.GetNamedItem("Server").Value;
ConnetionToSqlServer.Default().DataBase = xRoot.ChildNodes[0].Attributes.GetNamedItem("DataBase").Value;
var sqlAut = xRoot.ChildNodes[0].Attributes.GetNamedItem("SQLAuthentication")?.Value;
if (sqlAut == "True")
{
ConnetionToSqlServer.Default().SQLAuthentication = true;
ConnetionToSqlServer.Default().UserName = xRoot.ChildNodes[0].Attributes.GetNamedItem("Login")?.Value;
ConnetionToSqlServer.Default().Password = xRoot.ChildNodes[0].Attributes.GetNamedItem("Password")?.Value;
}
}
private void button1_Click(object sender, EventArgs e)
{
var connection = ConnetionToSqlServer.Default();
if (!cbSqlAut.Checked)
{
connection.DataBase = tbDataBase.Text;
connection.Server = tbServerName.Text;
connection.WindowsAuthentication = !cbSqlAut.Checked;
}
else
{
connection.DataBase = tbDataBase.Text;
connection.Server = tbServerName.Text;
connection.UserName = tbUser.Text;
connection.Password = tbPassword.Text;
connection.WindowsAuthentication = !cbSqlAut.Checked;
}
connection.Connect();
}
public void Export(string path)
{
var doc = new XmlDocument();
var decl = doc.CreateXmlDeclaration("1.0", "utf-8", "");
doc.InsertBefore(decl, doc.DocumentElement);
XmlNode configuration = doc.CreateElement("configuration");
XmlNode connection = doc.CreateElement("connection");
var server = doc.CreateAttribute("Server");
server.InnerText = ConnetionToSqlServer.Default().Server;
connection.Attributes?.Append(server);
var dataBase = doc.CreateAttribute("DataBase");
dataBase.InnerText = ConnetionToSqlServer.Default().DataBase;
connection.Attributes?.Append(dataBase);
var sqlAut = doc.CreateAttribute("SQLAuthentication");
sqlAut.InnerText = ConnetionToSqlServer.Default().SQLAuthentication.ToString();
connection.Attributes?.Append(sqlAut);
if (ConnetionToSqlServer.Default().SQLAuthentication)
{
var login = doc.CreateAttribute("Login");
login.InnerText = ConnetionToSqlServer.Default().UserName;
connection.Attributes?.Append(login);
var password = doc.CreateAttribute("Password");
password.InnerText = ConnetionToSqlServer.Default().Password;
connection.Attributes?.Append(password);
}
configuration.AppendChild(connection);
doc.AppendChild(configuration);
doc.Save(path);
}
private void SaveInBaseListDescriptionMessage(List <MessageModel> descriptionMessage, DateTime date)
{
var con = ConnetionToSqlServer.Default();
var regex = new Regex(@"\d+:\d+");
string format = "yyyy-MM-dd";
foreach (var message in descriptionMessage)
{
string dateStart = "null";
string dateEnd = "null";
string dateStartBargaining = "null";
if (message.DateStart.HasValue)
{
dateStart = $"'{((DateTime)message.DateStart).ToString(format)}'";
}
if (message.DateEnd.HasValue)
{
dateEnd = $"'{((DateTime)message.DateEnd).ToString(format)}'";
}
if (message.DateStartBargaining.HasValue)
{
dateStartBargaining = $"'{((DateTime)message.DateStartBargaining).ToString(format)}'";
}
string insert = $@"exec dbo.SaveMessage '{message.Id}', '{message.Number}',{dateStart}, {dateEnd},{dateStartBargaining}, '{message.Url}', '{message.Description}', '{message.DateMesage.ToString(format)}', '{message.TypeOfBidding}'";
//Log.Debug(insert);
con.ExecuteNonQuery(insert);
foreach (var lot in message.Lots)
{
insert = $@"exec dbo.SaveLot '{lot.Id}', {lot.Number}, '{lot.Description.Replace("'", "")}', {lot.StartPrice.ToString(System.Globalization.CultureInfo.GetCultureInfo("en-US"))}, '{lot.Step}', '{lot.Deposit}', '{lot.PriceReductionInformation}', '{lot.PropertyClassification}', '{message.Id}'";
con.ExecuteNonQuery(insert);
}
}
string typeMessage = GetTypeMessage(descriptionMessage.Count);
ParserBot.SendMessage($"За {date.ToShortDateString()} спарсено {descriptionMessage.Count} {typeMessage}");
}
static void ImportConnectionConfigForBankpurty()
{
var st = Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData);
var fileCatalog = st + @"\ParseBankuprty";
Directory.CreateDirectory(fileCatalog);
string fileNameConfig = fileCatalog + @"\ParseBankuprty.config";
if (File.Exists(fileNameConfig))
{
ImportConfigDataBase inmportConfigDataBase = new ImportConfigDataBase(ConnetionToSqlServer.Default());
inmportConfigDataBase.ExecuteImport();
}
}
private void ReadConfig()
{
var st = Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData);
var fileCatalog = st + @"\Parse";
Directory.CreateDirectory(fileCatalog);
fileNameConfig = fileCatalog + @"\Parse.config";
if (File.Exists(fileNameConfig))
{
ImportConfigDataBase inmportConfigDataBase = new ImportConfigDataBase(ConnetionToSqlServer.Default());
inmportConfigDataBase.ExecuteImport();
}
}
private void MainForm_Load(object sender, EventArgs e)
{
ReadConfig();
var conform = new ConnectionForm();
if (conform.ShowDialog() == DialogResult.Cancel)
{
this.Close();
}
else
{
var exportSetting = new ExportConfigDataBase();
exportSetting.Export(fileName);
}
cbChooseParse.SelectedIndex = 0;
cbTypeRoom.SelectedIndex = 0;
cbTypeSell.SelectedIndex = 0;
sfdParseFile.Filter = "csv files (*.csv)|*.csv|All files (*.*)|*.*";
sfdParseFile.FilterIndex = 1;
Log.Debug("Get Connection");
var connection = ConnetionToSqlServer.Default();
string select = "SELECT [ID],[Name] FROM [ParseBulding].[dbo].[District]";
var reader = connection.ExecuteReader(select);
if (reader != null)
{
while (reader.Read())
{
listDistricts.Add(new District {
Id = reader.GetGuid(0), Name = reader.GetString(1)
});
}
reader.Close();
foreach (var district in listDistricts)
{
select = $@"SELECT [Id]
,[Name]
,[XCoor]
,[YCoor]
,[IdRegion]
FROM[ParseBulding].[dbo].[Metro]
where IdRegion = '{district.Id}'";
reader = connection.ExecuteReader(select);
while (reader.Read())
{
var metro = new Metro
{
Id = reader.GetGuid(0),
Name = reader.GetString(1),
XCoor = (float)reader.GetDouble(2),
YCoor = (float)reader.GetDouble(3)
};
district.Metros.Add(metro);
listMetros.Add(metro);
}
reader.Close();
}
}
}