private void barButtonItemConnectToDb_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
{
ConnectToDb form = new ConnectToDb();
form.Owner = this;
form.ShowDialog();
}
public void ParsingTender()
{
using (var connect = ConnectToDb.GetDbConnection())
{
connect.Open();
var selectTend =
$"SELECT id_tender FROM {AppBuilder.Prefix}tender WHERE purchase_number = @purchase_number AND doc_publish_date = @doc_publish_date AND type_fz = @type_fz AND notice_version = @notice_version AND end_date = @end_date";
var cmd = new MySqlCommand(selectTend, connect);
cmd.Prepare();
cmd.Parameters.AddWithValue("@purchase_number", _tn.PurNum);
cmd.Parameters.AddWithValue("@doc_publish_date", _tn.DatePub);
cmd.Parameters.AddWithValue("@type_fz", TypeFz);
cmd.Parameters.AddWithValue("@notice_version", _tn.Status);
cmd.Parameters.AddWithValue("@end_date", _tn.DateEnd);
var dt = new DataTable();
var adapter = new MySqlDataAdapter {
SelectCommand = cmd
};
adapter.Fill(dt);
if (dt.Rows.Count > 0)
{
return;
}
var dateUpd = DateTime.Now;
var url =
$"https://api-zakaz.bashkortostan.ru/apifront/purchases/{_tn.Id}";
var result = DownloadString.DownLUserAgent(url, false, headers);
if (string.IsNullOrEmpty(result))
{
Log.Logger(
$"Empty string in {GetType().Name}.{System.Reflection.MethodBase.GetCurrentMethod().Name}",
url);
return;
}
var jobj = JObject.Parse(result);
var t = jobj.SelectToken("data") ?? throw new ApplicationException($"data was not found {_tn.Href}");
var(updated, cancelStatus) = UpdateTenderVersion(connect, _tn.PurNum, dateUpd);
CreaateOrganizer(connect, out var organiserId);
GetEtp(connect, out var idEtp);
var idRegion = GetRegionFromString("башкор", connect);
PlacingWay = ((string)(t.SelectToken(
"$..purchase_method")) ??
"").Trim();
GetPlacingWay(connect, out var idPlacingWay);
var idTender = CreateTender(connect, 0, organiserId, idPlacingWay, idEtp, cancelStatus, dateUpd,
updated);
var attacments = GetElements(t, "documents");
WriteAttachments(connect, attacments, idTender);
CreateCustomer(connect, out var customerId, t);
CreateLot(connect, idTender, customerId, t);
TenderKwords(connect, idTender);
AddVerNumber(connect, _tn.PurNum, TypeFz);
}
}
public void ParsingTender()
{
using (var connect = ConnectToDb.GetDbConnection())
{
connect.Open();
var selectTend =
$"SELECT id_tender FROM {AppBuilder.Prefix}tender WHERE purchase_number = @purchase_number AND doc_publish_date = @doc_publish_date AND type_fz = @type_fz AND notice_version = @notice_version AND end_date = @end_date";
var cmd = new MySqlCommand(selectTend, connect);
cmd.Prepare();
cmd.Parameters.AddWithValue("@purchase_number", _tn.PurNum);
cmd.Parameters.AddWithValue("@doc_publish_date", _tn.DatePub);
cmd.Parameters.AddWithValue("@type_fz", TypeFz);
cmd.Parameters.AddWithValue("@notice_version", _tn.Status);
cmd.Parameters.AddWithValue("@end_date", _tn.DateEnd);
var dt = new DataTable();
var adapter = new MySqlDataAdapter {
SelectCommand = cmd
};
adapter.Fill(dt);
if (dt.Rows.Count > 0)
{
return;
}
var s = DownloadString.DownLHttpPost(_tn.Href);
if (string.IsNullOrEmpty(s))
{
s = DownloadString.DownLHttpPost(_tn.Href.Replace(
"https://market.lot-online.ru/app/SmallPurchaseCard/page?SmallPurchaseCard.smallPurchaseEntity=",
"https://market.lot-online.ru/app/section/pochta/SmallPurchaseCard/page?SmallPurchaseCard.smallPurchaseEntity="));
}
if (string.IsNullOrEmpty(s))
{
Log.Logger("Empty string in ParsingTender()", _tn.Href);
return;
}
var htmlDoc = new HtmlDocument();
htmlDoc.LoadHtml(s);
var navigator = (HtmlNodeNavigator)htmlDoc.CreateNavigator();
var dateUpd = DateTime.Now;
var(updated, cancelStatus) = UpdateTenderVersion(connect, _tn.PurNum, dateUpd);
CreaateOrganizer(connect, out var organiserId);
var idPlacingWay = 0;
GetEtp(connect, out var idEtp);
var idRegion = GetRegionFromString(_tn.RegionName, connect);
var idTender = CreateTender(connect, idRegion, organiserId, idPlacingWay, idEtp, cancelStatus, dateUpd,
updated);
CreateCustomer(connect, out var customerId);
AddAttachments(htmlDoc, connect, idTender);
CreateLot(connect, idTender, customerId, htmlDoc);
TenderKwords(connect, idTender);
AddVerNumber(connect, _tn.PurNum, TypeFz);
}
}
private void GetListConformity()
{
using (var connect = ConnectToDb.GetDbConnection())
{
connect.Open();
var selectPw =
$"SELECT id_placing_way, name FROM {AppBuilder.Prefix}placing_way WHERE conformity = 0";
var cmd1 = new MySqlCommand(selectPw, connect);
cmd1.Prepare();
var adapter1 = new MySqlDataAdapter {
SelectCommand = cmd1
};
adapter1.Fill(_dt);
}
}
internal WeatherData FindCityId(string city)
{
CityForSearch result;
city = translate.GetTranslit(city);
using (ConnectToDb db = new ConnectToDb())
{
result = db.Cities.SingleOrDefault(x => x.name == city);
if (result == null)
{
return(GetResponseFromWeather(db.Cities.SingleOrDefault(x => x.id == 696050)));
}
}
return(GetResponseFromWeather(result));
}
public void ParsingTender()
{
using (var connect = ConnectToDb.GetDbConnection())
{
connect.Open();
var selectTend =
$"SELECT id_tender FROM {AppBuilder.Prefix}tender WHERE purchase_number = @purchase_number AND type_fz = @type_fz AND doc_publish_date = @doc_publish_date AND end_date = @end_date";
var cmd = new MySqlCommand(selectTend, connect);
cmd.Prepare();
cmd.Parameters.AddWithValue("@purchase_number", _tn.PurNum);
cmd.Parameters.AddWithValue("@type_fz", TypeFz);
cmd.Parameters.AddWithValue("@doc_publish_date", _tn.DatePub);
cmd.Parameters.AddWithValue("@end_date", _tn.DateEnd);
var dt = new DataTable();
var adapter = new MySqlDataAdapter {
SelectCommand = cmd
};
adapter.Fill(dt);
if (dt.Rows.Count > 0)
{
return;
}
var dateUpd = DateTime.Now;
var cancelStatus = 0;
var updated = false;
updated = UpdateVersion(connect, updated, dateUpd, ref cancelStatus);
var printForm = _tn.Href;
var customerId = 0;
var organiserId = 0;
var orgName = EtpName;
organiserId = AddOrganizer(orgName, connect, organiserId);
var idPlacingWay = 0;
GetEtp(connect, out var idEtp);
var idTender = AddTender(connect, organiserId, idPlacingWay, idEtp, cancelStatus, dateUpd, printForm,
updated);
customerId = AddCustomer(orgName, connect, customerId);
var idLot = AddLot(connect, idTender);
AddPurObjects(connect, idLot, customerId);
TenderKwords(connect, idTender);
AddVerNumber(connect, _tn.PurNum, TypeFz);
}
}
private void Parser()
{
GetListConformity();
if (_dt.Rows.Count == 0)
{
Log.Logger("cannot empty conformity in table");
return;
}
var ml = new ConformityLearner();
using (var connect = ConnectToDb.GetDbConnection())
{
connect.Open();
ml.PredictConformity(_dt.Rows, connect);
}
}
public void ParsingTender()
{
using (var connect = ConnectToDb.GetDbConnection())
{
var dateUpd = DateTime.Now;
connect.Open();
if (TenderExist(connect))
{
return;
}
var s = DownloadString.DownLHttpPostWithCookiesB2b(_tn.Href, ParserB2BWeb.CookieCollection,
useProxy: AppBuilder.UserProxy);
if (string.IsNullOrEmpty(s))
{
Log.Logger("Empty string in ParsingTender()", _tn.Href);
return;
}
if (s.Contains("не допускает использование ботов"))
{
Log.Logger("Google Captcha");
return;
}
var htmlDoc = new HtmlDocument();
htmlDoc.LoadHtml(s);
var navigator = (HtmlNodeNavigator)htmlDoc.CreateNavigator();
UpdateCancelStatus(connect, dateUpd, out var updated, out var cancelStatus);
var printForm = _tn.Href;
AddOrganizer(connect, navigator, out var organiserId);
AddCustomer(connect, out var customerId);
GetEtp(connect, out var idEtp);
GetPlacingWay(connect, out var idPlacingWay);
FillPurName(navigator);
FillBidAndScorDates(navigator, out var scoringDate, out var biddingDate);
FillNoticeVer(navigator, out var noticeVer);
AddTender(connect, organiserId, idPlacingWay, idEtp, scoringDate, biddingDate, cancelStatus, dateUpd,
noticeVer, printForm, updated, out var idTender);
AddAttachments(htmlDoc, connect, idTender);
AddLots(htmlDoc, navigator, connect, idTender, customerId);
TenderKwords(connect, idTender);
AddVerNumber(connect, _tn.PurNum, TypeFz);
}
}
public void LetsParse()
{
using (StreamReader file = File.OpenText(@"D:\Projects\OpenWeather\OpenWeather\Content\city.list.min.json"))
{
JsonSerializer serializer = new JsonSerializer();
var cities = (CityForSearch[])serializer.Deserialize(file, typeof(CityForSearch[]));
using (ConnectToDb db = new ConnectToDb())
{
var cityUa = cities.Where(x => x.country == "UA");
foreach (var item in cityUa)
{
{
db.Cities.Add(item);
db.SaveChanges();
}
}
db.SaveChanges();
}
}
} //create database with ua loc
public void ParsingTender()
{
using (var connect = ConnectToDb.GetDbConnection())
{
connect.Open();
var selectTend =
$"SELECT id_tender FROM {AppBuilder.Prefix}tender WHERE purchase_number = @purchase_number AND doc_publish_date = @doc_publish_date AND type_fz = @type_fz AND notice_version = @notice_version AND end_date = @end_date";
var cmd = new MySqlCommand(selectTend, connect);
cmd.Prepare();
cmd.Parameters.AddWithValue("@purchase_number", _tn.PurNum);
cmd.Parameters.AddWithValue("@doc_publish_date", _tn.DatePub);
cmd.Parameters.AddWithValue("@type_fz", TypeFz);
cmd.Parameters.AddWithValue("@notice_version", _tn.Status);
cmd.Parameters.AddWithValue("@end_date", _tn.DateEnd);
var dt = new DataTable();
var adapter = new MySqlDataAdapter {
SelectCommand = cmd
};
adapter.Fill(dt);
if (dt.Rows.Count > 0)
{
return;
}
if (_tn.NeedId != 0)
{
ParserNeed(connect);
}
else if (_tn.TenderId != 0)
{
ParserTender(connect);
}
else if (_tn.AuctionId != 0)
{
ParserAuction(connect);
}
}
}
public void ParsingTender()
{
using (var connect = ConnectToDb.GetDbConnection())
{
connect.Open();
var selectTend =
$"SELECT id_tender FROM {AppBuilder.Prefix}tender WHERE purchase_number = @purchase_number AND doc_publish_date = @doc_publish_date AND type_fz = @type_fz AND notice_version = @notice_version AND end_date = @end_date";
var cmd = new MySqlCommand(selectTend, connect);
cmd.Prepare();
cmd.Parameters.AddWithValue("@purchase_number", _tn.PurNum);
cmd.Parameters.AddWithValue("@doc_publish_date", _tn.DatePub);
cmd.Parameters.AddWithValue("@type_fz", TypeFz);
cmd.Parameters.AddWithValue("@notice_version", _tn.Status);
cmd.Parameters.AddWithValue("@end_date", _tn.DateEnd);
var dt = new DataTable();
var adapter = new MySqlDataAdapter {
SelectCommand = cmd
};
adapter.Fill(dt);
if (dt.Rows.Count > 0)
{
return;
}
var dateUpd = DateTime.Now;
var(updated, cancelStatus) = UpdateTenderVersion(connect, _tn.PurNum, dateUpd);
CreaateOrganizer(connect, out var organiserId);
GetPlacingWay(connect, out var idPlacingWay);
GetEtp(connect, out var idEtp);
var idRegion = GetRegionFromString(_tn.Region, connect);
var idTender = CreateTender(connect, idRegion, organiserId, idPlacingWay, idEtp, cancelStatus, dateUpd,
updated);
CreateCustomer(connect, out var customerId);
CreateLot(connect, idTender, customerId);
TenderKwords(connect, idTender);
AddVerNumber(connect, _tn.PurNum, TypeFz);
}
}
public ActionResult Create(OrderViewModel orderViewModel)
{
if (ModelState.IsValid)
{
int customerId = ConnectToDb.GetLastGeneratedValue(orderViewModel.FirstName, orderViewModel.LastName);
if (customerId != 0)
{
bookDbContext.CustomerAdresses.Add(new CustomerAdress
{
Country = orderViewModel.Country,
CityOrVillage = orderViewModel.CityOrVillage,
Street = orderViewModel.Street,
House = orderViewModel.House,
Appartment = orderViewModel.Appartment,
CustomerId = customerId
});
MyCart cart = MyCart.GetCart();
for (int i = 0; i < cart.bookCartCount; i++)
{
bookDbContext.CustomerOrders.Add(new CustomerOrder
{
CustomerId = customerId,
BookId = cart[i].book.BookId,
Price = cart[i].book.Price,
Quantity = cart[i].amount,
OrderDateTime = DateTime.Now,
OrderStatus = OrderStatus.Open.ToString()
});
}
bookDbContext.SaveChanges();
return(View("Success", cart));
}
}
return(View("Fall"));
}
public void ParsingTender()
{
var dateUpd = DateTime.Now;
using (var connect = ConnectToDb.GetDbConnection())
{
connect.Open();
var selectTend =
$"SELECT id_tender FROM {AppBuilder.Prefix}tender WHERE purchase_number = @purchase_number AND end_date = @end_date AND type_fz = @type_fz AND doc_publish_date = @doc_publish_date AND notice_version = @notice_version";
var cmd = new MySqlCommand(selectTend, connect);
cmd.Prepare();
cmd.Parameters.AddWithValue("@purchase_number", _tn.PurNum);
cmd.Parameters.AddWithValue("@end_date", _tn.DateEnd);
cmd.Parameters.AddWithValue("@type_fz", TypeFz);
cmd.Parameters.AddWithValue("@doc_publish_date", _tn.DatePub);
cmd.Parameters.AddWithValue("@notice_version", _tn.Status);
var dt = new DataTable();
var adapter = new MySqlDataAdapter {
SelectCommand = cmd
};
adapter.Fill(dt);
if (dt.Rows.Count > 0)
{
//Log.Logger("This tender is exist in base", PurNum);
return;
}
Thread.Sleep(5000);
var s = DownloadString.DownL(_tn.Href);
if (string.IsNullOrEmpty(s))
{
Log.Logger("Empty string in ParsingTender()", _tn.Href);
return;
}
var htmlDoc = new HtmlDocument();
htmlDoc.LoadHtml(s);
var navigator = (HtmlNodeNavigator)htmlDoc.CreateNavigator();
var cancelStatus = 0;
var updated = false;
var selectDateT =
$"SELECT id_tender, date_version, cancel FROM {AppBuilder.Prefix}tender WHERE purchase_number = @purchase_number AND type_fz = @type_fz";
var cmd2 = new MySqlCommand(selectDateT, connect);
cmd2.Prepare();
cmd2.Parameters.AddWithValue("@purchase_number", _tn.PurNum);
cmd2.Parameters.AddWithValue("@type_fz", TypeFz);
var adapter2 = new MySqlDataAdapter {
SelectCommand = cmd2
};
var dt2 = new DataTable();
adapter2.Fill(dt2);
foreach (DataRow row in dt2.Rows)
{
//DateTime dateNew = DateTime.Parse(pr.DatePublished);
updated = true;
if (dateUpd >= (DateTime)row["date_version"])
{
row["cancel"] = 1;
//row.AcceptChanges();
//row.SetModified();
}
else
{
cancelStatus = 1;
}
}
var commandBuilder =
new MySqlCommandBuilder(adapter2)
{
ConflictOption = ConflictOption.OverwriteChanges
};
adapter2.Update(dt2);
var printForm = _tn.Href;
var customerId = 0;
var organiserId = 0;
if (!string.IsNullOrEmpty(_tn.CusName))
{
var selectOrg =
$"SELECT id_organizer FROM {AppBuilder.Prefix}organizer WHERE full_name = @full_name";
var cmd3 = new MySqlCommand(selectOrg, connect);
cmd3.Prepare();
cmd3.Parameters.AddWithValue("@full_name", _tn.CusName);
var dt3 = new DataTable();
var adapter3 = new MySqlDataAdapter {
SelectCommand = cmd3
};
adapter3.Fill(dt3);
if (dt3.Rows.Count > 0)
{
organiserId = (int)dt3.Rows[0].ItemArray[0];
}
else
{
var phone = (navigator
.SelectSingleNode(
"//td[. = 'Телефон']/following-sibling::td")
?.Value ?? "").Trim();
var email = (navigator
.SelectSingleNode(
"//td[. = 'Электронная почта']/following-sibling::td")
?.Value ?? "").Trim();
var contactPerson = (navigator
.SelectSingleNode(
"//td[. = 'Контактное лицо']/following-sibling::td")
?.Value ?? "").Trim();
var addOrganizer =
$"INSERT INTO {AppBuilder.Prefix}organizer SET full_name = @full_name, contact_phone = @contact_phone, contact_person = @contact_person, contact_email = @contact_email, inn = @inn";
var cmd4 = new MySqlCommand(addOrganizer, connect);
cmd4.Prepare();
cmd4.Parameters.AddWithValue("@full_name", _tn.CusName);
cmd4.Parameters.AddWithValue("@contact_phone", phone);
cmd4.Parameters.AddWithValue("@contact_person", contactPerson);
cmd4.Parameters.AddWithValue("@contact_email", email);
cmd4.Parameters.AddWithValue("@inn", _tn.CusInn);
cmd4.ExecuteNonQuery();
organiserId = (int)cmd4.LastInsertedId;
}
}
GetEtp(connect, out var idEtp);
PlacingWay = (navigator
.SelectSingleNode(
"//td[. = 'Способ закупки']/following-sibling::td")
?.Value ?? "").Trim();
GetPlacingWay(connect, out var idPlacingWay);
var idRegion = GetRegionFromString("мурман", connect);
var insertTender =
$"INSERT INTO {AppBuilder.Prefix}tender SET id_region = @id_region, id_xml = @id_xml, purchase_number = @purchase_number, doc_publish_date = @doc_publish_date, href = @href, purchase_object_info = @purchase_object_info, type_fz = @type_fz, id_organizer = @id_organizer, id_placing_way = @id_placing_way, id_etp = @id_etp, end_date = @end_date, scoring_date = @scoring_date, bidding_date = @bidding_date, cancel = @cancel, date_version = @date_version, num_version = @num_version, notice_version = @notice_version, xml = @xml, print_form = @print_form";
var cmd9 = new MySqlCommand(insertTender, connect);
cmd9.Prepare();
cmd9.Parameters.AddWithValue("@id_region", idRegion);
cmd9.Parameters.AddWithValue("@id_xml", _tn.PurNum);
cmd9.Parameters.AddWithValue("@purchase_number", _tn.PurNum);
cmd9.Parameters.AddWithValue("@doc_publish_date", _tn.DatePub);
cmd9.Parameters.AddWithValue("@href", _tn.Href);
cmd9.Parameters.AddWithValue("@purchase_object_info", _tn.PurName);
cmd9.Parameters.AddWithValue("@type_fz", TypeFz);
cmd9.Parameters.AddWithValue("@id_organizer", organiserId);
cmd9.Parameters.AddWithValue("@id_placing_way", idPlacingWay);
cmd9.Parameters.AddWithValue("@id_etp", idEtp);
cmd9.Parameters.AddWithValue("@end_date", _tn.DateEnd);
cmd9.Parameters.AddWithValue("@scoring_date", DateTime.MinValue);
cmd9.Parameters.AddWithValue("@bidding_date", DateTime.MinValue);
cmd9.Parameters.AddWithValue("@cancel", cancelStatus);
cmd9.Parameters.AddWithValue("@date_version", dateUpd);
cmd9.Parameters.AddWithValue("@num_version", 1);
cmd9.Parameters.AddWithValue("@notice_version", _tn.Status);
cmd9.Parameters.AddWithValue("@xml", _tn.Href);
cmd9.Parameters.AddWithValue("@print_form", printForm);
var resInsertTender = cmd9.ExecuteNonQuery();
var idTender = (int)cmd9.LastInsertedId;
Counter(resInsertTender, updated);
if (!string.IsNullOrEmpty(_tn.CusName))
{
var selectCustomer =
$"SELECT id_customer FROM {AppBuilder.Prefix}customer WHERE full_name = @full_name";
var cmd13 = new MySqlCommand(selectCustomer, connect);
cmd13.Prepare();
cmd13.Parameters.AddWithValue("@full_name", _tn.CusName);
var reader7 = cmd13.ExecuteReader();
if (reader7.HasRows)
{
reader7.Read();
customerId = (int)reader7["id_customer"];
reader7.Close();
}
else
{
reader7.Close();
var insertCustomer =
$"INSERT INTO {AppBuilder.Prefix}customer SET reg_num = @reg_num, full_name = @full_name, is223=1, inn = @inn";
var cmd14 = new MySqlCommand(insertCustomer, connect);
cmd14.Prepare();
var customerRegNumber = Guid.NewGuid().ToString();
cmd14.Parameters.AddWithValue("@reg_num", customerRegNumber);
cmd14.Parameters.AddWithValue("@full_name", _tn.CusName);
cmd14.Parameters.AddWithValue("@inn", _tn.CusInn);
cmd14.ExecuteNonQuery();
customerId = (int)cmd14.LastInsertedId;
}
}
var docs = htmlDoc.DocumentNode.SelectNodes(
"//table[thead[tr[th[. = 'Прикрепленные документы']]]]/tbody//td/a") ??
new HtmlNodeCollection(null);
foreach (var dd in docs)
{
var urlAttT = (dd?.Attributes["href"]?.Value ?? "").Trim();
var fName = (dd?.InnerText ?? "").Trim();
var urlAtt = $"https://goszakupki.govvrn.ru{urlAttT}";
if (!string.IsNullOrEmpty(fName))
{
var insertAttach =
$"INSERT INTO {AppBuilder.Prefix}attachment SET id_tender = @id_tender, file_name = @file_name, url = @url";
var cmd10 = new MySqlCommand(insertAttach, connect);
cmd10.Prepare();
cmd10.Parameters.AddWithValue("@id_tender", idTender);
cmd10.Parameters.AddWithValue("@file_name", fName);
cmd10.Parameters.AddWithValue("@url", urlAtt);
cmd10.ExecuteNonQuery();
}
}
var lotNum = 1;
var insertLot =
$"INSERT INTO {AppBuilder.Prefix}lot SET id_tender = @id_tender, lot_number = @lot_number, max_price = @max_price, currency = @currency, finance_source = @finance_source";
var cmd18 = new MySqlCommand(insertLot, connect);
cmd18.Prepare();
cmd18.Parameters.AddWithValue("@id_tender", idTender);
cmd18.Parameters.AddWithValue("@lot_number", lotNum);
cmd18.Parameters.AddWithValue("@max_price", _tn.Nmck);
cmd18.Parameters.AddWithValue("@currency", "");
cmd18.Parameters.AddWithValue("@finance_source", "");
cmd18.ExecuteNonQuery();
var idLot = (int)cmd18.LastInsertedId;
var delivPlace = (navigator
.SelectSingleNode(
"//td[contains(., 'Место доставки')]/following-sibling::td")
?.Value ?? "").Trim();
var delivTerm1 = (navigator
.SelectSingleNode(
"//td[contains(., 'Срок и условия')]/following-sibling::td")
?.Value ?? "").Trim();
var delivTerm2 = (navigator
.SelectSingleNode(
"//td[contains(., 'Сроки поставки товаров')]/following-sibling::td")
?.Value ?? "").Trim();
var delivTerm3 = (navigator
.SelectSingleNode(
"//td[contains(., 'Сведения о включенных')]/following-sibling::td")
?.Value ?? "").Trim();
var delivTerm = $"{delivTerm1}\n{delivTerm2}\n{delivTerm3}".Trim();
if (!string.IsNullOrEmpty(delivTerm) || !string.IsNullOrEmpty(delivPlace))
{
var insertCustomerRequirement =
$"INSERT INTO {AppBuilder.Prefix}customer_requirement SET id_lot = @id_lot, id_customer = @id_customer, delivery_place = @delivery_place, max_price = @max_price, delivery_term = @delivery_term";
var cmd16 = new MySqlCommand(insertCustomerRequirement, connect);
cmd16.Prepare();
cmd16.Parameters.AddWithValue("@id_lot", idLot);
cmd16.Parameters.AddWithValue("@id_customer", customerId);
cmd16.Parameters.AddWithValue("@delivery_place", delivPlace);
cmd16.Parameters.AddWithValue("@max_price", _tn.Nmck);
cmd16.Parameters.AddWithValue("@delivery_term", delivTerm);
cmd16.ExecuteNonQuery();
}
var poList =
htmlDoc.DocumentNode.SelectNodes("//table[thead[tr[th[. = 'Количество']]]]/tbody/tr") ??
new HtmlNodeCollection(null);
if (poList.Count != 0)
{
poList.RemoveAt(poList.Count - 1);
foreach (var pp in poList)
{
var namePo = (pp.SelectSingleNode(".//td[2]")
?.InnerText ?? "").Trim();
var okeiP = (pp.SelectSingleNode(".//td[3]")
?.InnerText ?? "").Trim();
var priceP = (pp.SelectSingleNode(".//td[4]")
?.InnerText.Replace(",", ".").DelAllWhitespace() ?? "").Trim();
var sumP = (pp.SelectSingleNode(".//td[6]")
?.InnerText.Replace(",", ".").DelAllWhitespace() ?? "").Trim();
var quantityP = (pp.SelectSingleNode(".//td[5]")
?.InnerText.Replace(",", ".").DelAllWhitespace() ?? "").Trim();
var insertLotitem =
$"INSERT INTO {AppBuilder.Prefix}purchase_object SET id_lot = @id_lot, id_customer = @id_customer, name = @name, quantity_value = @quantity_value, okei = @okei, customer_quantity_value = @customer_quantity_value, price = @price, sum = @sum";
var cmd19 = new MySqlCommand(insertLotitem, connect);
cmd19.Prepare();
cmd19.Parameters.AddWithValue("@id_lot", idLot);
cmd19.Parameters.AddWithValue("@id_customer", customerId);
cmd19.Parameters.AddWithValue("@name", namePo);
cmd19.Parameters.AddWithValue("@quantity_value", quantityP);
cmd19.Parameters.AddWithValue("@okei", okeiP);
cmd19.Parameters.AddWithValue("@customer_quantity_value", quantityP);
cmd19.Parameters.AddWithValue("@price", priceP);
cmd19.Parameters.AddWithValue("@sum", sumP);
cmd19.ExecuteNonQuery();
}
}
TenderKwords(connect, idTender);
AddVerNumber(connect, _tn.PurNum, TypeFz);
}
}
public void ParsingTender()
{
using (var connect = ConnectToDb.GetDbConnection())
{
connect.Open();
var selectTend =
$"SELECT id_tender FROM {AppBuilder.Prefix}tender WHERE purchase_number = @purchase_number AND type_fz = @type_fz AND doc_publish_date = @doc_publish_date AND notice_version = @notice_version";
var cmd = new MySqlCommand(selectTend, connect);
cmd.Prepare();
cmd.Parameters.AddWithValue("@purchase_number", _tn.PurNum);
cmd.Parameters.AddWithValue("@type_fz", TypeFz);
cmd.Parameters.AddWithValue("@doc_publish_date", _tn.DatePub);
cmd.Parameters.AddWithValue("@notice_version", _tn.Status);
var dt = new DataTable();
var adapter = new MySqlDataAdapter {
SelectCommand = cmd
};
adapter.Fill(dt);
if (dt.Rows.Count > 0)
{
return;
}
var s = DownloadString.DownLUserAgent(_tn.Href);
if (string.IsNullOrEmpty(s))
{
Log.Logger("Empty string in ParsingTender()", _tn.Href);
return;
}
var htmlDoc = new HtmlDocument();
htmlDoc.LoadHtml(s);
var dateUpd = DateTime.Now;
var cancelStatus = 0;
var updated = false;
updated = UpdateDate(connect, updated, dateUpd, ref cancelStatus);
var printForm = _tn.Href;
var customerId = 0;
var organiserId = 0;
organiserId = AddOrganizer(connect, organiserId);
GetEtp(connect, out var idEtp);
var resInsertTender = AddTender(connect, organiserId, idEtp, cancelStatus, dateUpd, printForm,
out var idTender);
Counter(resInsertTender, updated);
customerId = AddCustomer(htmlDoc, connect, customerId);
var idLot = AddLot(htmlDoc, connect, idTender);
AddDelivTerms(htmlDoc, connect, idLot, customerId);
var sPo = DownloadString.DownLUserAgent($"http://is-mt.pro/Purchase/ListCgc?id={_tn.PurNum}");
if (string.IsNullOrEmpty(sPo))
{
Log.Logger("Empty string in ParsingTender()", _tn.Href);
}
AddPurObjects(sPo, htmlDoc, connect, idLot, customerId);
TenderKwords(connect, idTender);
AddVerNumber(connect, _tn.PurNum, TypeFz);
}
}
public void ParsingTender()
{
using (var connect = ConnectToDb.GetDbConnection())
{
connect.Open();
var selectTend =
$"SELECT id_tender FROM {AppBuilder.Prefix}tender WHERE purchase_number = @purchase_number AND end_date = @end_date AND type_fz = @type_fz AND doc_publish_date = @doc_publish_date AND notice_version = @notice_version";
var cmd = new MySqlCommand(selectTend, connect);
cmd.Prepare();
cmd.Parameters.AddWithValue("@purchase_number", _tn.PurNum);
cmd.Parameters.AddWithValue("@end_date", _tn.DateEnd);
cmd.Parameters.AddWithValue("@type_fz", TypeFz);
cmd.Parameters.AddWithValue("@doc_publish_date", _tn.DatePub);
cmd.Parameters.AddWithValue("@notice_version", _tn.Status);
var dt = new DataTable();
var adapter = new MySqlDataAdapter {
SelectCommand = cmd
};
adapter.Fill(dt);
if (dt.Rows.Count > 0)
{
//Log.Logger("This tender is exist in base", PurNum);
return;
}
var headers = new Dictionary <string, string>
{
["sec-fetch-site"] = "none",
["sec-fetch-mode"] = "navigate",
["sec-fetch-user"] = "******",
["sec-fetch-dest"] = "document",
["accept-language"] = "ru-RU,ru;q=0.9,en-US;q=0.8,en;q=0.7",
};
var s = DownloadString.DownLUserAgent(_tn.Href, false, headers);
if (string.IsNullOrEmpty(s))
{
Log.Logger("Empty string in ParsingTender()", _tn.Href);
return;
}
var htmlDoc = new HtmlDocument();
htmlDoc.LoadHtml(s);
var navigator = (HtmlNodeNavigator)htmlDoc.CreateNavigator();
var dateUpd = DateTime.Now;
var cancelStatus = 0;
var updated = false;
var selectDateT =
$"SELECT id_tender, date_version, cancel FROM {AppBuilder.Prefix}tender WHERE purchase_number = @purchase_number AND type_fz = @type_fz";
var cmd2 = new MySqlCommand(selectDateT, connect);
cmd2.Prepare();
cmd2.Parameters.AddWithValue("@purchase_number", _tn.PurNum);
cmd2.Parameters.AddWithValue("@type_fz", TypeFz);
var adapter2 = new MySqlDataAdapter {
SelectCommand = cmd2
};
var dt2 = new DataTable();
adapter2.Fill(dt2);
foreach (DataRow row in dt2.Rows)
{
//DateTime dateNew = DateTime.Parse(pr.DatePublished);
updated = true;
if (dateUpd >= (DateTime)row["date_version"])
{
row["cancel"] = 1;
//row.AcceptChanges();
//row.SetModified();
}
else
{
cancelStatus = 1;
}
}
var commandBuilder =
new MySqlCommandBuilder(adapter2)
{
ConflictOption = ConflictOption.OverwriteChanges
};
adapter2.Update(dt2);
var printForm = _tn.Href;
var customerId = 0;
var organiserId = 0;
var orgName = (navigator
.SelectSingleNode(
"//div[contains(., 'Организатор')]/following-sibling::div")
?.Value ?? "").Trim();
if (orgName != "")
{
_tn.OrgName = orgName;
}
if (!string.IsNullOrEmpty(_tn.OrgName))
{
var selectOrg =
$"SELECT id_organizer FROM {AppBuilder.Prefix}organizer WHERE full_name = @full_name";
var cmd3 = new MySqlCommand(selectOrg, connect);
cmd3.Prepare();
cmd3.Parameters.AddWithValue("@full_name", _tn.OrgName);
var dt3 = new DataTable();
var adapter3 = new MySqlDataAdapter {
SelectCommand = cmd3
};
adapter3.Fill(dt3);
if (dt3.Rows.Count > 0)
{
organiserId = (int)dt3.Rows[0].ItemArray[0];
}
else
{
var phone = (navigator
.SelectSingleNode(
"//div[contains(., 'Телефон')]/following-sibling::div")
?.Value ?? "").Trim();
var email = (navigator
.SelectSingleNode(
"//div[contains(., 'Электронная почта')]/following-sibling::div/a")
?.Value ?? "").Trim();
var inn = "";
var kpp = "";
var contactPerson = (navigator
.SelectSingleNode(
"//div[contains(., 'Контактное лицо')]/following-sibling::div")
?.Value ?? "").Trim();
var addOrganizer =
$"INSERT INTO {AppBuilder.Prefix}organizer SET full_name = @full_name, contact_phone = @contact_phone, contact_person = @contact_person, contact_email = @contact_email, inn = @inn, kpp = @kpp";
var cmd4 = new MySqlCommand(addOrganizer, connect);
cmd4.Prepare();
cmd4.Parameters.AddWithValue("@full_name", _tn.OrgName);
cmd4.Parameters.AddWithValue("@contact_phone", phone);
cmd4.Parameters.AddWithValue("@contact_person", contactPerson);
cmd4.Parameters.AddWithValue("@contact_email", email);
cmd4.Parameters.AddWithValue("@inn", inn);
cmd4.Parameters.AddWithValue("@kpp", kpp);
cmd4.ExecuteNonQuery();
organiserId = (int)cmd4.LastInsertedId;
}
}
PlacingWay = (navigator
.SelectSingleNode(
"//div[contains(., 'Способ закупки')]/following-sibling::div")
?.Value ?? "").Trim();
GetPlacingWay(connect, out var idPlacingWay);
GetEtp(connect, out var idEtp);
var idRegion = 0;
var regionName = (navigator
.SelectSingleNode(
"//div[contains(., 'Регион')]/following-sibling::div")
?.Value ?? "").Trim();
if (!string.IsNullOrEmpty(regionName))
{
idRegion = GetRegionFromString(regionName, connect);
}
var insertTender =
$"INSERT INTO {AppBuilder.Prefix}tender SET id_region = @id_region, id_xml = @id_xml, purchase_number = @purchase_number, doc_publish_date = @doc_publish_date, href = @href, purchase_object_info = @purchase_object_info, type_fz = @type_fz, id_organizer = @id_organizer, id_placing_way = @id_placing_way, id_etp = @id_etp, end_date = @end_date, scoring_date = @scoring_date, bidding_date = @bidding_date, cancel = @cancel, date_version = @date_version, num_version = @num_version, notice_version = @notice_version, xml = @xml, print_form = @print_form";
var cmd9 = new MySqlCommand(insertTender, connect);
cmd9.Prepare();
cmd9.Parameters.AddWithValue("@id_region", idRegion);
cmd9.Parameters.AddWithValue("@id_xml", _tn.PurNum);
cmd9.Parameters.AddWithValue("@purchase_number", _tn.PurNum);
cmd9.Parameters.AddWithValue("@doc_publish_date", _tn.DatePub);
cmd9.Parameters.AddWithValue("@href", _tn.Href);
cmd9.Parameters.AddWithValue("@purchase_object_info", _tn.PurName);
cmd9.Parameters.AddWithValue("@type_fz", TypeFz);
cmd9.Parameters.AddWithValue("@id_organizer", organiserId);
cmd9.Parameters.AddWithValue("@id_placing_way", idPlacingWay);
cmd9.Parameters.AddWithValue("@id_etp", idEtp);
cmd9.Parameters.AddWithValue("@end_date", _tn.DateEnd);
cmd9.Parameters.AddWithValue("@scoring_date", DateTime.MinValue);
cmd9.Parameters.AddWithValue("@bidding_date", DateTime.MinValue);
cmd9.Parameters.AddWithValue("@cancel", cancelStatus);
cmd9.Parameters.AddWithValue("@date_version", dateUpd);
cmd9.Parameters.AddWithValue("@num_version", 1);
cmd9.Parameters.AddWithValue("@notice_version", _tn.Status);
cmd9.Parameters.AddWithValue("@xml", _tn.Href);
cmd9.Parameters.AddWithValue("@print_form", printForm);
var resInsertTender = cmd9.ExecuteNonQuery();
var idTender = (int)cmd9.LastInsertedId;
Counter(resInsertTender, updated);
if (!string.IsNullOrEmpty(_tn.OrgName))
{
var selectCustomer =
$"SELECT id_customer FROM {AppBuilder.Prefix}customer WHERE full_name = @full_name";
var cmd13 = new MySqlCommand(selectCustomer, connect);
cmd13.Prepare();
cmd13.Parameters.AddWithValue("@full_name", _tn.OrgName);
var reader7 = cmd13.ExecuteReader();
if (reader7.HasRows)
{
reader7.Read();
customerId = (int)reader7["id_customer"];
reader7.Close();
}
else
{
reader7.Close();
var insertCustomer =
$"INSERT INTO {AppBuilder.Prefix}customer SET reg_num = @reg_num, full_name = @full_name, is223=1";
var cmd14 = new MySqlCommand(insertCustomer, connect);
cmd14.Prepare();
var customerRegNumber = Guid.NewGuid().ToString();
cmd14.Parameters.AddWithValue("@reg_num", customerRegNumber);
cmd14.Parameters.AddWithValue("@full_name", _tn.OrgName);
cmd14.ExecuteNonQuery();
customerId = (int)cmd14.LastInsertedId;
}
}
var docs = htmlDoc.DocumentNode.SelectNodes(
"//li[@class = 'documents-list__item']/a") ??
new HtmlNodeCollection(null);
foreach (var doc in docs)
{
var urlAttT = (doc?.Attributes["href"]?.Value ?? "").Trim();
var fName = doc.InnerText.Trim().DelDoubleWhitespace();
var urlAtt = $"https://segezha-group.com{urlAttT}";
if (!string.IsNullOrEmpty(fName))
{
var insertAttach =
$"INSERT INTO {AppBuilder.Prefix}attachment SET id_tender = @id_tender, file_name = @file_name, url = @url";
var cmd10 = new MySqlCommand(insertAttach, connect);
cmd10.Prepare();
cmd10.Parameters.AddWithValue("@id_tender", idTender);
cmd10.Parameters.AddWithValue("@file_name", fName);
cmd10.Parameters.AddWithValue("@url", urlAtt);
cmd10.ExecuteNonQuery();
}
}
var lotNum = 1;
var insertLot =
$"INSERT INTO {AppBuilder.Prefix}lot SET id_tender = @id_tender, lot_number = @lot_number, max_price = @max_price, currency = @currency, finance_source = @finance_source";
var cmd18 = new MySqlCommand(insertLot, connect);
cmd18.Prepare();
cmd18.Parameters.AddWithValue("@id_tender", idTender);
cmd18.Parameters.AddWithValue("@lot_number", lotNum);
cmd18.Parameters.AddWithValue("@max_price", "");
cmd18.Parameters.AddWithValue("@currency", "");
cmd18.Parameters.AddWithValue("@finance_source", "");
cmd18.ExecuteNonQuery();
var idLot = (int)cmd18.LastInsertedId;
var insertLotitem =
$"INSERT INTO {AppBuilder.Prefix}purchase_object SET id_lot = @id_lot, id_customer = @id_customer, name = @name, sum = @sum";
var cmd19 = new MySqlCommand(insertLotitem, connect);
cmd19.Prepare();
cmd19.Parameters.AddWithValue("@id_lot", idLot);
cmd19.Parameters.AddWithValue("@id_customer", customerId);
cmd19.Parameters.AddWithValue("@name", _tn.PurName);
cmd19.Parameters.AddWithValue("@sum", "");
cmd19.ExecuteNonQuery();
var delivTerm = (navigator
.SelectSingleNode(
"//h3[contains(., 'ПРЕДМЕТ ЗАКУПКИ')]/following-sibling::div[@class = 'page-text']/text()")
?.Value ?? "").Trim();
if (!string.IsNullOrEmpty(delivTerm))
{
var insertCustomerRequirement =
$"INSERT INTO {AppBuilder.Prefix}customer_requirement SET id_lot = @id_lot, id_customer = @id_customer, delivery_place = @delivery_place, max_price = @max_price, delivery_term = @delivery_term";
var cmd16 = new MySqlCommand(insertCustomerRequirement, connect);
cmd16.Prepare();
cmd16.Parameters.AddWithValue("@id_lot", idLot);
cmd16.Parameters.AddWithValue("@id_customer", customerId);
cmd16.Parameters.AddWithValue("@delivery_place", "");
cmd16.Parameters.AddWithValue("@max_price", "");
cmd16.Parameters.AddWithValue("@delivery_term",
System.Net.WebUtility.HtmlDecode(delivTerm).DelDoubleWhitespace());
cmd16.ExecuteNonQuery();
}
TenderKwords(connect, idTender);
AddVerNumber(connect, _tn.PurNum, TypeFz);
}
}
public void ParsingTender()
{
using (var connect = ConnectToDb.GetDbConnection())
{
var dateUpd = DateTime.Now;
connect.Open();
var selectTend =
$"SELECT id_tender FROM {AppBuilder.Prefix}tender WHERE purchase_number = @purchase_number AND end_date = @end_date AND type_fz = @type_fz AND notice_version = @notice_version";
var cmd = new MySqlCommand(selectTend, connect);
cmd.Prepare();
cmd.Parameters.AddWithValue("@purchase_number", _tn.PurNum);
cmd.Parameters.AddWithValue("@end_date", _tn.DateEnd);
cmd.Parameters.AddWithValue("@type_fz", TypeFz);
cmd.Parameters.AddWithValue("@notice_version", _tn.Status);
var dt = new DataTable();
var adapter = new MySqlDataAdapter {
SelectCommand = cmd
};
adapter.Fill(dt);
if (dt.Rows.Count > 0)
{
return;
}
var s = DownloadString.DownLHttpPostWithCookies(_tn.Href, ParserFederal.HttpsT2Federal1Ru,
ParserFederal.Cookie);
if (string.IsNullOrEmpty(s))
{
Log.Logger("Empty string in ParsingTender()", _tn.Href);
return;
}
var htmlDoc = new HtmlDocument();
htmlDoc.LoadHtml(s);
var navigator = (HtmlNodeNavigator)htmlDoc.CreateNavigator();
var cancelStatus = 0;
var updated = false;
var selectDateT =
$"SELECT id_tender, date_version, cancel FROM {AppBuilder.Prefix}tender WHERE purchase_number = @purchase_number AND type_fz = @type_fz";
var cmd2 = new MySqlCommand(selectDateT, connect);
cmd2.Prepare();
cmd2.Parameters.AddWithValue("@purchase_number", _tn.PurNum);
cmd2.Parameters.AddWithValue("@type_fz", TypeFz);
var adapter2 = new MySqlDataAdapter {
SelectCommand = cmd2
};
var dt2 = new DataTable();
adapter2.Fill(dt2);
foreach (DataRow row in dt2.Rows)
{
updated = true;
if (dateUpd >= (DateTime)row["date_version"])
{
row["cancel"] = 1;
//row.AcceptChanges();
//row.SetModified();
}
else
{
cancelStatus = 1;
}
}
var commandBuilder =
new MySqlCommandBuilder(adapter2)
{
ConflictOption = ConflictOption.OverwriteChanges
};
adapter2.Update(dt2);
var printForm = _tn.Href;
var customerId = 0;
var organiserId = 0;
if (!string.IsNullOrEmpty(EtpName))
{
var selectOrg =
$"SELECT id_organizer FROM {AppBuilder.Prefix}organizer WHERE full_name = @full_name";
var cmd3 = new MySqlCommand(selectOrg, connect);
cmd3.Prepare();
cmd3.Parameters.AddWithValue("@full_name", EtpName);
var dt3 = new DataTable();
var adapter3 = new MySqlDataAdapter {
SelectCommand = cmd3
};
adapter3.Fill(dt3);
if (dt3.Rows.Count > 0)
{
organiserId = (int)dt3.Rows[0].ItemArray[0];
}
else
{
var phone = "";
var email = "";
var contactPerson = "";
var addOrganizer =
$"INSERT INTO {AppBuilder.Prefix}organizer SET full_name = @full_name, contact_phone = @contact_phone, contact_person = @contact_person, contact_email = @contact_email";
var cmd4 = new MySqlCommand(addOrganizer, connect);
cmd4.Prepare();
cmd4.Parameters.AddWithValue("@full_name", EtpName);
cmd4.Parameters.AddWithValue("@contact_phone", phone);
cmd4.Parameters.AddWithValue("@contact_person", contactPerson);
cmd4.Parameters.AddWithValue("@contact_email", email);
cmd4.ExecuteNonQuery();
organiserId = (int)cmd4.LastInsertedId;
}
}
GetEtp(connect, out var idEtp);
GetPlacingWay(connect, out var idPlacingWay);
var biddingDateT =
navigator.SelectSingleNode(
"//td[contains(., 'проведения аукциона')]/following-sibling::td")?.Value?.Trim() ??
"";
var dateBiddingT = biddingDateT.GetDataFromRegex(@"(\d{2}\.\d{2}\.\d{4}\s+\d{2}:\d{2})");
var biddingDate = dateBiddingT.ParseDateUn("dd.MM.yyyy HH:mm");
var scoringDateT =
navigator.SelectSingleNode(
"//td[contains(., 'рассмотрения заявок')]/following-sibling::td")?.Value?.Trim() ??
"";
var dateScoringT = scoringDateT.GetDataFromRegex(@"(\d{2}\.\d{2}\.\d{4}\s+\d{2}:\d{2})");
var scoringDate = dateScoringT.ParseDateUn("dd.MM.yyyy HH:mm");
var insertTender =
$"INSERT INTO {AppBuilder.Prefix}tender SET id_region = @id_region, id_xml = @id_xml, purchase_number = @purchase_number, doc_publish_date = @doc_publish_date, href = @href, purchase_object_info = @purchase_object_info, type_fz = @type_fz, id_organizer = @id_organizer, id_placing_way = @id_placing_way, id_etp = @id_etp, end_date = @end_date, scoring_date = @scoring_date, bidding_date = @bidding_date, cancel = @cancel, date_version = @date_version, num_version = @num_version, notice_version = @notice_version, xml = @xml, print_form = @print_form";
var cmd9 = new MySqlCommand(insertTender, connect);
cmd9.Prepare();
cmd9.Parameters.AddWithValue("@id_region", 0);
cmd9.Parameters.AddWithValue("@id_xml", _tn.PurNum);
cmd9.Parameters.AddWithValue("@purchase_number", _tn.PurNum);
cmd9.Parameters.AddWithValue("@doc_publish_date", _tn.DatePub);
cmd9.Parameters.AddWithValue("@href", _tn.Href);
cmd9.Parameters.AddWithValue("@purchase_object_info", _tn.PurName);
cmd9.Parameters.AddWithValue("@type_fz", TypeFz);
cmd9.Parameters.AddWithValue("@id_organizer", organiserId);
cmd9.Parameters.AddWithValue("@id_placing_way", idPlacingWay);
cmd9.Parameters.AddWithValue("@id_etp", idEtp);
cmd9.Parameters.AddWithValue("@end_date", _tn.DateEnd);
cmd9.Parameters.AddWithValue("@scoring_date", scoringDate);
cmd9.Parameters.AddWithValue("@bidding_date", biddingDate);
cmd9.Parameters.AddWithValue("@cancel", cancelStatus);
cmd9.Parameters.AddWithValue("@date_version", dateUpd);
cmd9.Parameters.AddWithValue("@num_version", 1);
cmd9.Parameters.AddWithValue("@notice_version", _tn.Status);
cmd9.Parameters.AddWithValue("@xml", _tn.Href);
cmd9.Parameters.AddWithValue("@print_form", printForm);
var resInsertTender = cmd9.ExecuteNonQuery();
var idTender = (int)cmd9.LastInsertedId;
Counter(resInsertTender, updated);
if (!string.IsNullOrEmpty(_tn.CusName))
{
var selectCustomer =
$"SELECT id_customer FROM {AppBuilder.Prefix}customer WHERE full_name = @full_name";
var cmd13 = new MySqlCommand(selectCustomer, connect);
cmd13.Prepare();
cmd13.Parameters.AddWithValue("@full_name", _tn.CusName);
var reader7 = cmd13.ExecuteReader();
if (reader7.HasRows)
{
reader7.Read();
customerId = (int)reader7["id_customer"];
reader7.Close();
}
else
{
reader7.Close();
var insertCustomer =
$"INSERT INTO {AppBuilder.Prefix}customer SET reg_num = @reg_num, full_name = @full_name, is223=1";
var cmd14 = new MySqlCommand(insertCustomer, connect);
cmd14.Prepare();
var customerRegNumber = Guid.NewGuid().ToString();
cmd14.Parameters.AddWithValue("@reg_num", customerRegNumber);
cmd14.Parameters.AddWithValue("@full_name", _tn.CusName);
cmd14.ExecuteNonQuery();
customerId = (int)cmd14.LastInsertedId;
}
}
AddAttachments(connect, idTender);
AddLots(htmlDoc, connect, idTender, customerId);
TenderKwords(connect, idTender);
AddVerNumber(connect, _tn.PurNum, TypeFz);
}
}
public void ParsingTender()
{
using (var connect = ConnectToDb.GetDbConnection())
{
connect.Open();
var selectTend =
$"SELECT id_tender FROM {AppBuilder.Prefix}tender WHERE purchase_number = @purchase_number AND type_fz = @type_fz AND notice_version = @notice_version";
var cmd = new MySqlCommand(selectTend, connect);
cmd.Prepare();
cmd.Parameters.AddWithValue("@purchase_number", _tn.Id);
cmd.Parameters.AddWithValue("@type_fz", TypeFz);
cmd.Parameters.AddWithValue("@notice_version", _tn.StateString);
var dt = new DataTable();
var adapter = new MySqlDataAdapter {
SelectCommand = cmd
};
adapter.Fill(dt);
if (dt.Rows.Count > 0)
{
return;
}
var url = $"https://zmo-new-webapi.rts-tender.ru/market/api/v1/trades/{_tn.Id}";
var s = DownloadString.DownLRtsZmo(url, null, _section);
if (string.IsNullOrEmpty(s))
{
Log.Logger(
$"Empty string in {GetType().Name}.{System.Reflection.MethodBase.GetCurrentMethod().Name}",
url);
return;
}
var tender = JObject.Parse(s);
var dateUpd = DateTime.Now;
var(updated, cancelStatus) = UpdateTenderVersion(connect, _tn.Id, dateUpd);
var organiserId = 0;
var orgName = _tn.CusName;
if (orgName != "")
{
var selectOrg =
$"SELECT id_organizer FROM {AppBuilder.Prefix}organizer WHERE full_name = @full_name";
var cmd3 = new MySqlCommand(selectOrg, connect);
cmd3.Prepare();
cmd3.Parameters.AddWithValue("@full_name", orgName);
var dt3 = new DataTable();
var adapter3 = new MySqlDataAdapter {
SelectCommand = cmd3
};
adapter3.Fill(dt3);
if (dt3.Rows.Count > 0)
{
organiserId = (int)dt3.Rows[0].ItemArray[0];
}
else
{
var phone = ((string)tender.SelectToken("data.ContactPerson.Phone") ?? "").Trim();
var email = ((string)tender.SelectToken("data.ContactPerson.ContactEmail") ?? "").Trim();
var contactPerson = ((string)tender.SelectToken("data.ContactPerson.Name") ?? "").Trim();
var inn = "";
var kpp = "";
var postAddr = "";
var addOrganizer =
$"INSERT INTO {AppBuilder.Prefix}organizer SET full_name = @full_name, contact_phone = @contact_phone, contact_person = @contact_person, contact_email = @contact_email, inn = @inn, kpp = @kpp, post_address = @post_address";
var cmd4 = new MySqlCommand(addOrganizer, connect);
cmd4.Prepare();
cmd4.Parameters.AddWithValue("@full_name", orgName);
cmd4.Parameters.AddWithValue("@contact_phone", phone);
cmd4.Parameters.AddWithValue("@contact_person", contactPerson);
cmd4.Parameters.AddWithValue("@contact_email", email);
cmd4.Parameters.AddWithValue("@inn", inn);
cmd4.Parameters.AddWithValue("@kpp", kpp);
cmd4.Parameters.AddWithValue("@post_address", postAddr);
cmd4.ExecuteNonQuery();
organiserId = (int)cmd4.LastInsertedId;
}
}
var idPlacingWay = 0;
GetEtp(connect, out var idEtp);
var regionName = ((string)tender.SelectToken("data.DeliveryKladrRegionName") ?? "").Trim();
var idRegion = GetRegionFromString(regionName, connect);
var href = "";
switch (_section)
{
case 132:
href = $"https://market.rts-tender.ru/zapros/{_tn.Id}/request";
break;
default:
Log.Logger("Bad url", url);
return;
}
var insertTender =
$"INSERT INTO {AppBuilder.Prefix}tender SET id_region = @id_region, id_xml = @id_xml, purchase_number = @purchase_number, doc_publish_date = @doc_publish_date, href = @href, purchase_object_info = @purchase_object_info, type_fz = @type_fz, id_organizer = @id_organizer, id_placing_way = @id_placing_way, id_etp = @id_etp, end_date = @end_date, scoring_date = @scoring_date, bidding_date = @bidding_date, cancel = @cancel, date_version = @date_version, num_version = @num_version, notice_version = @notice_version, xml = @xml, print_form = @print_form";
var cmd9 = new MySqlCommand(insertTender, connect);
cmd9.Prepare();
cmd9.Parameters.AddWithValue("@id_region", idRegion);
cmd9.Parameters.AddWithValue("@id_xml", _tn.Id);
cmd9.Parameters.AddWithValue("@purchase_number", _tn.Id);
cmd9.Parameters.AddWithValue("@doc_publish_date", _tn.PublicationDate);
cmd9.Parameters.AddWithValue("@href", href);
cmd9.Parameters.AddWithValue("@purchase_object_info", _tn.PurName);
cmd9.Parameters.AddWithValue("@type_fz", TypeFz);
cmd9.Parameters.AddWithValue("@id_organizer", organiserId);
cmd9.Parameters.AddWithValue("@id_placing_way", idPlacingWay);
cmd9.Parameters.AddWithValue("@id_etp", idEtp);
cmd9.Parameters.AddWithValue("@end_date", _tn.EndDate);
cmd9.Parameters.AddWithValue("@scoring_date", DateTime.MinValue);
cmd9.Parameters.AddWithValue("@bidding_date", DateTime.MinValue);
cmd9.Parameters.AddWithValue("@cancel", cancelStatus);
cmd9.Parameters.AddWithValue("@date_version", dateUpd);
cmd9.Parameters.AddWithValue("@num_version", 1);
cmd9.Parameters.AddWithValue("@notice_version", _tn.StateString);
cmd9.Parameters.AddWithValue("@xml", href);
cmd9.Parameters.AddWithValue("@print_form", href);
var resInsertTender = cmd9.ExecuteNonQuery();
var idTender = (int)cmd9.LastInsertedId;
Counter(resInsertTender, updated);
var attacments = GetElements(tender, "data.OtherFiles");
WriteAttachments(connect, attacments, idTender);
var lotNum = 1;
var insertLot =
$"INSERT INTO {AppBuilder.Prefix}lot SET id_tender = @id_tender, lot_number = @lot_number, max_price = @max_price, currency = @currency";
var cmd18 = new MySqlCommand(insertLot, connect);
cmd18.Prepare();
cmd18.Parameters.AddWithValue("@id_tender", idTender);
cmd18.Parameters.AddWithValue("@lot_number", lotNum);
cmd18.Parameters.AddWithValue("@max_price", _tn.Nmck);
cmd18.Parameters.AddWithValue("@currency", "");
cmd18.ExecuteNonQuery();
var idLot = (int)cmd18.LastInsertedId;
var customerId = 0;
if (!string.IsNullOrEmpty(_tn.CusName))
{
var selectCustomer =
$"SELECT id_customer FROM {AppBuilder.Prefix}customer WHERE full_name = @full_name";
var cmd13 = new MySqlCommand(selectCustomer, connect);
cmd13.Prepare();
cmd13.Parameters.AddWithValue("@full_name", _tn.CusName);
var reader7 = cmd13.ExecuteReader();
if (reader7.HasRows)
{
reader7.Read();
customerId = (int)reader7["id_customer"];
reader7.Close();
}
else
{
reader7.Close();
var insertCustomer =
$"INSERT INTO {AppBuilder.Prefix}customer SET reg_num = @reg_num, full_name = @full_name, inn = @inn, is223=1";
var cmd14 = new MySqlCommand(insertCustomer, connect);
cmd14.Prepare();
var customerRegNumber = Guid.NewGuid().ToString();
cmd14.Parameters.AddWithValue("@reg_num", customerRegNumber);
cmd14.Parameters.AddWithValue("@full_name", _tn.CusName);
cmd14.Parameters.AddWithValue("@inn", "");
cmd14.ExecuteNonQuery();
customerId = (int)cmd14.LastInsertedId;
}
}
var delivTerm = ((string)tender.SelectToken("data.DeliveryTerms") ?? "").Trim();
foreach (var dp in _tn.DeliveryKladrRegionName)
{
if (delivTerm != "" || dp != "")
{
var insertCustomerRequirement =
$"INSERT INTO {AppBuilder.Prefix}customer_requirement SET id_lot = @id_lot, id_customer = @id_customer, max_price = @max_price, delivery_place = @delivery_place, delivery_term = @delivery_term";
var cmd16 = new MySqlCommand(insertCustomerRequirement, connect);
cmd16.Prepare();
cmd16.Parameters.AddWithValue("@id_lot", idLot);
cmd16.Parameters.AddWithValue("@id_customer", customerId);
cmd16.Parameters.AddWithValue("@delivery_place", dp);
cmd16.Parameters.AddWithValue("@delivery_term", delivTerm);
cmd16.Parameters.AddWithValue("@max_price", _tn.Nmck);
cmd16.ExecuteNonQuery();
}
}
var purObjects = GetElements(tender, "data.Products");
purObjects.ForEach(po =>
{
var okpdName = "";
var okpdCode = ((string)po.SelectToken(
"ClassificatorCode") ?? "").Trim();
var okei = ((string)po.SelectToken(
"OkeiName") ?? "").Trim();
var quantity = (decimal?)po.SelectToken(
"Quantity") ?? 0.0m;
var price = (decimal?)po.SelectToken(
"Price") ?? 0.0m;
var sum = (decimal?)po.SelectToken(
"Sum") ?? 0.0m;
var poName = ((string)po.SelectToken(
"Name") ?? "").Trim();
var insertLotitem =
$"INSERT INTO {AppBuilder.Prefix}purchase_object SET id_lot = @id_lot, id_customer = @id_customer, name = @name, sum = @sum, okpd2_code = @okpd2_code, okpd2_group_code = @okpd2_group_code, okpd2_group_level1_code = @okpd2_group_level1_code, okpd_name = @okpd_name, quantity_value = @quantity_value, customer_quantity_value = @customer_quantity_value, okei = @okei, price = @price";
var cmd19 = new MySqlCommand(insertLotitem, connect);
cmd19.Prepare();
cmd19.Parameters.AddWithValue("@id_lot", idLot);
cmd19.Parameters.AddWithValue("@id_customer", customerId);
cmd19.Parameters.AddWithValue("@name", poName);
cmd19.Parameters.AddWithValue("@sum", sum);
cmd19.Parameters.AddWithValue("@okpd2_code", okpdCode);
cmd19.Parameters.AddWithValue("@okpd2_group_code", "");
cmd19.Parameters.AddWithValue("@okpd2_group_level1_code", "");
cmd19.Parameters.AddWithValue("@okpd_name", okpdName);
cmd19.Parameters.AddWithValue("@quantity_value", quantity);
cmd19.Parameters.AddWithValue("@customer_quantity_value", quantity);
cmd19.Parameters.AddWithValue("@okei", okei);
cmd19.Parameters.AddWithValue("@price", price);
cmd19.ExecuteNonQuery();
});
TenderKwords(connect, idTender);
AddVerNumber(connect, _tn.Id, TypeFz);
}
}
public void ParsingTender()
{
var s = DownloadString.DownL(_tn.Href);
if (string.IsNullOrEmpty(s))
{
Log.Logger("Empty string in ParsingTender()", _tn.Href);
return;
}
var htmlDoc = new HtmlDocument();
htmlDoc.LoadHtml(s);
var navigator = (HtmlNodeNavigator)htmlDoc.CreateNavigator();
var dateUpd = DateTime.Now;
_tn.PurNum = (navigator
.SelectSingleNode(
"//td[contains(., 'Номер закупки') or contains(., 'Номер запроса') or contains(., 'Номер процедуры')]/following-sibling::td/a")
?.Value ?? "").Trim();
if (string.IsNullOrEmpty(_tn.PurNum))
{
_tn.PurNum =
(navigator.SelectSingleNode(
"//td[contains(., 'Номер закупки') or contains(., 'Номер запроса') or contains(., 'Номер процедуры') or contains(., 'Номер закупки')]/following-sibling::td")
?.Value ?? "").Replace("ЕИС", "").Trim();
}
if (string.IsNullOrEmpty(_tn.PurNum))
{
Log.Logger("cannot find purNum in ", _tn.Href);
return;
}
var eis = (navigator
.SelectSingleNode(
"//td[contains(., 'Номер закупки') or contains(., 'Номер запроса') or contains(., 'Номер процедуры')]/following-sibling::td/span/a/acronym")
?.Value ?? "").Trim();
if (eis.Contains("ЕИС"))
{
//Log.Logger("EIS tender", _tn.PurNum);
return;
}
var noticeVersion = (navigator
.SelectSingleNode(
"//td[contains(., 'Состояние лота')]/following-sibling::td")
?.Value ?? "").Trim();
using (var connect = ConnectToDb.GetDbConnection())
{
connect.Open();
var selectTend =
$"SELECT id_tender FROM {AppBuilder.Prefix}tender WHERE purchase_number = @purchase_number AND end_date = @end_date AND type_fz = @type_fz AND doc_publish_date = @doc_publish_date AND notice_version = @notice_version";
var cmd = new MySqlCommand(selectTend, connect);
cmd.Prepare();
cmd.Parameters.AddWithValue("@purchase_number", _tn.PurNum);
cmd.Parameters.AddWithValue("@end_date", _tn.DateEnd);
cmd.Parameters.AddWithValue("@type_fz", TypeFz);
cmd.Parameters.AddWithValue("@doc_publish_date", _tn.DatePub);
cmd.Parameters.AddWithValue("@notice_version", noticeVersion);
var dt = new DataTable();
var adapter = new MySqlDataAdapter {
SelectCommand = cmd
};
adapter.Fill(dt);
if (dt.Rows.Count > 0)
{
//Log.Logger("This tender is exist in base", PurNum);
return;
}
var cancelStatus = 0;
var updated = false;
var selectDateT =
$"SELECT id_tender, date_version, cancel FROM {AppBuilder.Prefix}tender WHERE purchase_number = @purchase_number AND type_fz = @type_fz";
var cmd2 = new MySqlCommand(selectDateT, connect);
cmd2.Prepare();
cmd2.Parameters.AddWithValue("@purchase_number", _tn.PurNum);
cmd2.Parameters.AddWithValue("@type_fz", TypeFz);
var adapter2 = new MySqlDataAdapter {
SelectCommand = cmd2
};
var dt2 = new DataTable();
adapter2.Fill(dt2);
foreach (DataRow row in dt2.Rows)
{
//DateTime dateNew = DateTime.Parse(pr.DatePublished);
updated = true;
if (dateUpd >= (DateTime)row["date_version"])
{
row["cancel"] = 1;
//row.AcceptChanges();
//row.SetModified();
}
else
{
cancelStatus = 1;
}
}
var commandBuilder =
new MySqlCommandBuilder(adapter2)
{
ConflictOption = ConflictOption.OverwriteChanges
};
adapter2.Update(dt2);
var printForm = _tn.Href;
var customerId = 0;
var organiserId = 0;
if (!string.IsNullOrEmpty(_tn.OrgName))
{
var selectOrg =
$"SELECT id_organizer FROM {AppBuilder.Prefix}organizer WHERE full_name = @full_name";
var cmd3 = new MySqlCommand(selectOrg, connect);
cmd3.Prepare();
cmd3.Parameters.AddWithValue("@full_name", _tn.OrgName);
var dt3 = new DataTable();
var adapter3 = new MySqlDataAdapter {
SelectCommand = cmd3
};
adapter3.Fill(dt3);
if (dt3.Rows.Count > 0)
{
organiserId = (int)dt3.Rows[0].ItemArray[0];
}
else
{
var phone = "";
var email = "";
var contactPerson = "";
var addOrganizer =
$"INSERT INTO {AppBuilder.Prefix}organizer SET full_name = @full_name, contact_phone = @contact_phone, contact_person = @contact_person, contact_email = @contact_email";
var cmd4 = new MySqlCommand(addOrganizer, connect);
cmd4.Prepare();
cmd4.Parameters.AddWithValue("@full_name", _tn.OrgName);
cmd4.Parameters.AddWithValue("@contact_phone", phone);
cmd4.Parameters.AddWithValue("@contact_person", contactPerson);
cmd4.Parameters.AddWithValue("@contact_email", email);
cmd4.ExecuteNonQuery();
organiserId = (int)cmd4.LastInsertedId;
}
}
GetEtp(connect, out var idEtp);
GetPlacingWay(connect, out var idPlacingWay);
var biddingDateT = (navigator
.SelectSingleNode(
"//td[span[contains(., 'Начало') and contains(., 'проведения')]]/following-sibling::td/text()[1]")
?.Value ?? "").Replace(" ", " ").Trim();
biddingDateT = Regex.Replace(biddingDateT, @"\s+", " ");
biddingDateT = biddingDateT.GetDateWithMonth();
var biddingDate = biddingDateT.ParseDateUn("dd MM yyyy HH:mm");
var insertTender =
$"INSERT INTO {AppBuilder.Prefix}tender SET id_region = @id_region, id_xml = @id_xml, purchase_number = @purchase_number, doc_publish_date = @doc_publish_date, href = @href, purchase_object_info = @purchase_object_info, type_fz = @type_fz, id_organizer = @id_organizer, id_placing_way = @id_placing_way, id_etp = @id_etp, end_date = @end_date, scoring_date = @scoring_date, bidding_date = @bidding_date, cancel = @cancel, date_version = @date_version, num_version = @num_version, notice_version = @notice_version, xml = @xml, print_form = @print_form";
var cmd9 = new MySqlCommand(insertTender, connect);
cmd9.Prepare();
cmd9.Parameters.AddWithValue("@id_region", 0);
cmd9.Parameters.AddWithValue("@id_xml", _tn.PurNum);
cmd9.Parameters.AddWithValue("@purchase_number", _tn.PurNum);
cmd9.Parameters.AddWithValue("@doc_publish_date", _tn.DatePub);
cmd9.Parameters.AddWithValue("@href", _tn.Href);
cmd9.Parameters.AddWithValue("@purchase_object_info", _tn.PurName);
cmd9.Parameters.AddWithValue("@type_fz", TypeFz);
cmd9.Parameters.AddWithValue("@id_organizer", organiserId);
cmd9.Parameters.AddWithValue("@id_placing_way", idPlacingWay);
cmd9.Parameters.AddWithValue("@id_etp", idEtp);
cmd9.Parameters.AddWithValue("@end_date", _tn.DateEnd);
cmd9.Parameters.AddWithValue("@scoring_date", DateTime.MinValue);
cmd9.Parameters.AddWithValue("@bidding_date", biddingDate);
cmd9.Parameters.AddWithValue("@cancel", cancelStatus);
cmd9.Parameters.AddWithValue("@date_version", dateUpd);
cmd9.Parameters.AddWithValue("@num_version", 1);
cmd9.Parameters.AddWithValue("@notice_version", noticeVersion);
cmd9.Parameters.AddWithValue("@xml", _tn.Href);
cmd9.Parameters.AddWithValue("@print_form", printForm);
var resInsertTender = cmd9.ExecuteNonQuery();
var idTender = (int)cmd9.LastInsertedId;
Counter(resInsertTender, updated);
var cusName = (navigator
.SelectSingleNode(
"//td[contains(., 'Наименование') and contains(., 'заказчика')]/following-sibling::td")
?.Value ?? "").Replace(" ", " ").Trim();
if (!string.IsNullOrEmpty(cusName))
{
var selectCustomer =
$"SELECT id_customer FROM {AppBuilder.Prefix}customer WHERE full_name = @full_name";
var cmd13 = new MySqlCommand(selectCustomer, connect);
cmd13.Prepare();
cmd13.Parameters.AddWithValue("@full_name", cusName);
var reader7 = cmd13.ExecuteReader();
if (reader7.HasRows)
{
reader7.Read();
customerId = (int)reader7["id_customer"];
reader7.Close();
}
else
{
reader7.Close();
var insertCustomer =
$"INSERT INTO {AppBuilder.Prefix}customer SET reg_num = @reg_num, full_name = @full_name, is223=1";
var cmd14 = new MySqlCommand(insertCustomer, connect);
cmd14.Prepare();
var customerRegNumber = Guid.NewGuid().ToString();
cmd14.Parameters.AddWithValue("@reg_num", customerRegNumber);
cmd14.Parameters.AddWithValue("@full_name", cusName);
cmd14.ExecuteNonQuery();
customerId = (int)cmd14.LastInsertedId;
}
}
var docs = htmlDoc.DocumentNode.SelectNodes(
"//h3/following-sibling::div//table/tbody/tr") ??
new HtmlNodeCollection(null);
foreach (var doc in docs)
{
var nameDd = (doc.SelectSingleNode("./td[1]/a")?.InnerText ?? "").Trim();
if (!nameDd.Contains("Извещение"))
{
continue;
}
var exp = (doc.SelectSingleNode("./td[2]")?.InnerText ?? "").Trim();
if (!exp.Contains("(действующая)"))
{
continue;
}
var documents = doc.SelectNodes("./td[4]/div/a") ??
new HtmlNodeCollection(null);
foreach (var dd in documents)
{
var urlAttT = (dd?.Attributes["href"]?.Value ?? "").Trim();
var fName = (dd?.InnerText ?? "").Trim();
var urlAtt = $"https://etpzakupki.tatar/{urlAttT}";
if (!string.IsNullOrEmpty(fName))
{
var insertAttach =
$"INSERT INTO {AppBuilder.Prefix}attachment SET id_tender = @id_tender, file_name = @file_name, url = @url";
var cmd10 = new MySqlCommand(insertAttach, connect);
cmd10.Prepare();
cmd10.Parameters.AddWithValue("@id_tender", idTender);
cmd10.Parameters.AddWithValue("@file_name", fName);
cmd10.Parameters.AddWithValue("@url", urlAtt);
cmd10.ExecuteNonQuery();
}
}
}
var lotNum = 1;
var insertLot =
$"INSERT INTO {AppBuilder.Prefix}lot SET id_tender = @id_tender, lot_number = @lot_number, max_price = @max_price, currency = @currency, finance_source = @finance_source";
var cmd18 = new MySqlCommand(insertLot, connect);
cmd18.Prepare();
cmd18.Parameters.AddWithValue("@id_tender", idTender);
cmd18.Parameters.AddWithValue("@lot_number", lotNum);
cmd18.Parameters.AddWithValue("@max_price", _tn.Nmck);
cmd18.Parameters.AddWithValue("@currency", _tn.Currency);
cmd18.Parameters.AddWithValue("@finance_source", "");
cmd18.ExecuteNonQuery();
var idLot = (int)cmd18.LastInsertedId;
var insertLotitem =
$"INSERT INTO {AppBuilder.Prefix}purchase_object SET id_lot = @id_lot, id_customer = @id_customer, name = @name, sum = @sum";
var cmd19 = new MySqlCommand(insertLotitem, connect);
cmd19.Prepare();
cmd19.Parameters.AddWithValue("@id_lot", idLot);
cmd19.Parameters.AddWithValue("@id_customer", customerId);
cmd19.Parameters.AddWithValue("@name", _tn.PurName);
cmd19.Parameters.AddWithValue("@sum", _tn.Nmck);
cmd19.ExecuteNonQuery();
TenderKwords(connect, idTender);
AddVerNumber(connect, _tn.PurNum, TypeFz);
}
}
public void ParsingTender()
{
var s = DownloadString.DownL(_tn.Href);
if (String.IsNullOrEmpty(s))
{
Log.Logger($"Empty string in {GetType().Name}.{System.Reflection.MethodBase.GetCurrentMethod().Name}",
_tn.Href);
}
var parser = new HtmlParser();
var document = parser.Parse(s);
var datePubT = (document.QuerySelector("td:contains('Дата публикации:') + td")?.TextContent ?? "").Trim();
var datePub = datePubT.ParseDateUn("dd.MM.yyyy HH:mm 'GMT'z");
var dateEndT = (document.QuerySelector("td:contains('Дата окончания приема заявок') + td")?.TextContent ??
"").Trim();
if (dateEndT == "")
{
dateEndT =
(document.QuerySelector("td:contains('Подведение итогов не позднее') + td")?.TextContent ??
"").Trim();
}
var dateEnd = dateEndT.ParseDateUn("dd.MM.yyyy HH:mm 'GMT'z");
if (datePub == DateTime.MinValue || dateEnd == DateTime.MinValue)
{
Log.Logger($"Empty dates in {GetType().Name}.{System.Reflection.MethodBase.GetCurrentMethod().Name}",
_tn.Href, datePubT, dateEndT);
return;
}
var purNumT = (document.QuerySelector("h1.section-procurement__title")?.TextContent ?? "").Trim();
var purNum = purNumT.GetDataFromRegex("Извещение о процедуре (.+)");
if (string.IsNullOrEmpty(purNum))
{
Log.Logger($"Empty purNum in {GetType().Name}.{System.Reflection.MethodBase.GetCurrentMethod().Name}",
_tn.Href, purNumT);
return;
}
var noticeVersion = _tn.Status;
using (var connect = ConnectToDb.GetDbConnection())
{
connect.Open();
var selectTend =
$"SELECT id_tender FROM {AppBuilder.Prefix}tender WHERE purchase_number = @purchase_number AND end_date = @end_date AND type_fz = @type_fz AND notice_version = @notice_version";
var cmd = new MySqlCommand(selectTend, connect);
cmd.Prepare();
cmd.Parameters.AddWithValue("@purchase_number", purNum);
cmd.Parameters.AddWithValue("@end_date", dateEnd);
cmd.Parameters.AddWithValue("@type_fz", TypeFz);
cmd.Parameters.AddWithValue("@notice_version", noticeVersion);
var dt = new DataTable();
var adapter = new MySqlDataAdapter {
SelectCommand = cmd
};
adapter.Fill(dt);
if (dt.Rows.Count > 0)
{
//Log.Logger("This tender is exist in base", PurNum);
return;
}
var dateUpd = DateTime.Now;
var(updated, cancelStatus) = UpdateTenderVersion(connect, purNum, dateUpd);
var printForm = _tn.Href;
var customerId = 0;
var organiserId = 0;
organiserId = GetOrganizer(document, connect);
PlacingWay = (document.QuerySelector("td:contains('Способ закупки:') + td")?.TextContent ?? "")
.Trim();
GetPlacingWay(connect, out var idPlacingWay);
GetEtp(connect, out var idEtp);
var purObjInfo =
(document.QuerySelector("span:contains('Наименование закупки:') + span")?.TextContent ?? "")
.Trim();
var insertTender =
$"INSERT INTO {AppBuilder.Prefix}tender SET id_region = @id_region, id_xml = @id_xml, purchase_number = @purchase_number, doc_publish_date = @doc_publish_date, href = @href, purchase_object_info = @purchase_object_info, type_fz = @type_fz, id_organizer = @id_organizer, id_placing_way = @id_placing_way, id_etp = @id_etp, end_date = @end_date, scoring_date = @scoring_date, bidding_date = @bidding_date, cancel = @cancel, date_version = @date_version, num_version = @num_version, notice_version = @notice_version, xml = @xml, print_form = @print_form";
var cmd9 = new MySqlCommand(insertTender, connect);
cmd9.Prepare();
cmd9.Parameters.AddWithValue("@id_region", 0);
cmd9.Parameters.AddWithValue("@id_xml", purNum);
cmd9.Parameters.AddWithValue("@purchase_number", purNum);
cmd9.Parameters.AddWithValue("@doc_publish_date", datePub);
cmd9.Parameters.AddWithValue("@href", _tn.Href);
cmd9.Parameters.AddWithValue("@purchase_object_info", purObjInfo);
cmd9.Parameters.AddWithValue("@type_fz", TypeFz);
cmd9.Parameters.AddWithValue("@id_organizer", organiserId);
cmd9.Parameters.AddWithValue("@id_placing_way", idPlacingWay);
cmd9.Parameters.AddWithValue("@id_etp", idEtp);
cmd9.Parameters.AddWithValue("@end_date", dateEnd);
cmd9.Parameters.AddWithValue("@scoring_date", DateTime.MinValue);
cmd9.Parameters.AddWithValue("@bidding_date", DateTime.MinValue);
cmd9.Parameters.AddWithValue("@cancel", cancelStatus);
cmd9.Parameters.AddWithValue("@date_version", dateUpd);
cmd9.Parameters.AddWithValue("@num_version", 1);
cmd9.Parameters.AddWithValue("@notice_version", noticeVersion);
cmd9.Parameters.AddWithValue("@xml", _tn.Href);
cmd9.Parameters.AddWithValue("@print_form", printForm);
var resInsertTender = cmd9.ExecuteNonQuery();
var idTender = (int)cmd9.LastInsertedId;
Counter(resInsertTender, updated);
var docs = document.QuerySelectorAll(
"#documentation > a");
if (docs.Length == 0)
{
docs = document.QuerySelectorAll(
"div.procedure__item--documents a");
}
GetDocs(docs, connect, idTender);
var lots = document.QuerySelectorAll(
"div.procedure__lots > div.procedure__lot");
GetLots(lots, connect, idTender, customerId);
TenderKwords(connect, idTender);
AddVerNumber(connect, purNum, TypeFz);
}
}
public void ParsingTender()
{
using (var connect = ConnectToDb.GetDbConnection())
{
connect.Open();
var selectTend =
$"SELECT id_tender FROM {AppBuilder.Prefix}tender WHERE purchase_number = @purchase_number AND type_fz = @type_fz AND doc_publish_date = @doc_publish_date";
var cmd = new MySqlCommand(selectTend, connect);
cmd.Prepare();
cmd.Parameters.AddWithValue("@purchase_number", _tn.PurNum);
cmd.Parameters.AddWithValue("@type_fz", TypeFz);
cmd.Parameters.AddWithValue("@doc_publish_date", _tn.DatePub);
var dt = new DataTable();
var adapter = new MySqlDataAdapter {
SelectCommand = cmd
};
adapter.Fill(dt);
if (dt.Rows.Count > 0)
{
return;
}
var s = DownloadString.DownLUserAgent(_tn.Href);
if (string.IsNullOrEmpty(s))
{
Log.Logger("Empty string in ParsingTender()", _tn.Href);
return;
}
var htmlDoc = new HtmlDocument();
htmlDoc.LoadHtml(s);
var navigator = (HtmlNodeNavigator)htmlDoc.CreateNavigator();
var dateUpd = DateTime.Now;
var cancelStatus = 0;
var updated = false;
var selectDateT =
$"SELECT id_tender, date_version, cancel FROM {AppBuilder.Prefix}tender WHERE purchase_number = @purchase_number AND type_fz = @type_fz";
var cmd2 = new MySqlCommand(selectDateT, connect);
cmd2.Prepare();
cmd2.Parameters.AddWithValue("@purchase_number", _tn.PurNum);
cmd2.Parameters.AddWithValue("@type_fz", TypeFz);
var adapter2 = new MySqlDataAdapter {
SelectCommand = cmd2
};
var dt2 = new DataTable();
adapter2.Fill(dt2);
foreach (DataRow row in dt2.Rows)
{
updated = true;
if (dateUpd >= (DateTime)row["date_version"])
{
row["cancel"] = 1;
}
else
{
cancelStatus = 1;
}
}
var commandBuilder =
new MySqlCommandBuilder(adapter2)
{
ConflictOption = ConflictOption.OverwriteChanges
};
adapter2.Update(dt2);
var printForm = _tn.Href;
var customerId = 0;
var organiserId = 0;
var orgName = "RubEx Group";
if (!string.IsNullOrEmpty(orgName))
{
var selectOrg =
$"SELECT id_organizer FROM {AppBuilder.Prefix}organizer WHERE full_name = @full_name";
var cmd3 = new MySqlCommand(selectOrg, connect);
cmd3.Prepare();
cmd3.Parameters.AddWithValue("@full_name", orgName);
var dt3 = new DataTable();
var adapter3 = new MySqlDataAdapter {
SelectCommand = cmd3
};
adapter3.Fill(dt3);
if (dt3.Rows.Count > 0)
{
organiserId = (int)dt3.Rows[0].ItemArray[0];
}
else
{
var phone = "+7 4712 37-15-03";
var email = "";
var inn = "";
var kpp = "";
var contactPerson = "";
var addOrganizer =
$"INSERT INTO {AppBuilder.Prefix}organizer SET full_name = @full_name, contact_phone = @contact_phone, contact_person = @contact_person, contact_email = @contact_email, inn = @inn, kpp = @kpp";
var cmd4 = new MySqlCommand(addOrganizer, connect);
cmd4.Prepare();
cmd4.Parameters.AddWithValue("@full_name", orgName);
cmd4.Parameters.AddWithValue("@contact_phone", phone);
cmd4.Parameters.AddWithValue("@contact_person", contactPerson);
cmd4.Parameters.AddWithValue("@contact_email", email);
cmd4.Parameters.AddWithValue("@inn", inn);
cmd4.Parameters.AddWithValue("@kpp", kpp);
cmd4.ExecuteNonQuery();
organiserId = (int)cmd4.LastInsertedId;
}
}
var idPlacingWay = 0;
GetEtp(connect, out var idEtp);
var dateEndT = htmlDoc.DocumentNode
.SelectSingleNode(
"//strong[contains(., 'Дата и время окончания приема заявок')]/following-sibling::strong")
?.InnerText.Trim();
var dateEnd = dateEndT.ParseDateUn("dd.MM.yyyy\nгода hh:ss часов 00 минут");
if (dateEnd == DateTime.MinValue)
{
dateEndT = s.GetDataFromRegex(@"(\d{2}\.\d{2}\.\d{4} года \d{2})");
dateEnd = dateEndT.ParseDateUn("dd.MM.yyyy года hh");
}
if (dateEnd != DateTime.MinValue)
{
_tn.DateEnd = dateEnd;
}
var insertTender =
$"INSERT INTO {AppBuilder.Prefix}tender SET id_region = @id_region, id_xml = @id_xml, purchase_number = @purchase_number, doc_publish_date = @doc_publish_date, href = @href, purchase_object_info = @purchase_object_info, type_fz = @type_fz, id_organizer = @id_organizer, id_placing_way = @id_placing_way, id_etp = @id_etp, end_date = @end_date, scoring_date = @scoring_date, bidding_date = @bidding_date, cancel = @cancel, date_version = @date_version, num_version = @num_version, notice_version = @notice_version, xml = @xml, print_form = @print_form";
var cmd9 = new MySqlCommand(insertTender, connect);
cmd9.Prepare();
cmd9.Parameters.AddWithValue("@id_region", 0);
cmd9.Parameters.AddWithValue("@id_xml", _tn.PurNum);
cmd9.Parameters.AddWithValue("@purchase_number", _tn.PurNum);
cmd9.Parameters.AddWithValue("@doc_publish_date", _tn.DatePub);
cmd9.Parameters.AddWithValue("@href", _tn.Href);
cmd9.Parameters.AddWithValue("@purchase_object_info", _tn.PurName);
cmd9.Parameters.AddWithValue("@type_fz", TypeFz);
cmd9.Parameters.AddWithValue("@id_organizer", organiserId);
cmd9.Parameters.AddWithValue("@id_placing_way", idPlacingWay);
cmd9.Parameters.AddWithValue("@id_etp", idEtp);
cmd9.Parameters.AddWithValue("@end_date", _tn.DateEnd);
cmd9.Parameters.AddWithValue("@scoring_date", DateTime.MinValue);
cmd9.Parameters.AddWithValue("@bidding_date", DateTime.MinValue);
cmd9.Parameters.AddWithValue("@cancel", cancelStatus);
cmd9.Parameters.AddWithValue("@date_version", dateUpd);
cmd9.Parameters.AddWithValue("@num_version", 1);
cmd9.Parameters.AddWithValue("@notice_version", "");
cmd9.Parameters.AddWithValue("@xml", _tn.Href);
cmd9.Parameters.AddWithValue("@print_form", printForm);
var resInsertTender = cmd9.ExecuteNonQuery();
var idTender = (int)cmd9.LastInsertedId;
Counter(resInsertTender, updated);
if (!string.IsNullOrEmpty(orgName))
{
var selectCustomer =
$"SELECT id_customer FROM {AppBuilder.Prefix}customer WHERE full_name = @full_name";
var cmd13 = new MySqlCommand(selectCustomer, connect);
cmd13.Prepare();
cmd13.Parameters.AddWithValue("@full_name", orgName);
var reader7 = cmd13.ExecuteReader();
if (reader7.HasRows)
{
reader7.Read();
customerId = (int)reader7["id_customer"];
reader7.Close();
}
else
{
reader7.Close();
var insertCustomer =
$"INSERT INTO {AppBuilder.Prefix}customer SET reg_num = @reg_num, full_name = @full_name, is223=1, inn = @inn";
var cmd14 = new MySqlCommand(insertCustomer, connect);
cmd14.Prepare();
var customerRegNumber = Guid.NewGuid().ToString();
cmd14.Parameters.AddWithValue("@reg_num", customerRegNumber);
cmd14.Parameters.AddWithValue("@full_name", orgName);
cmd14.Parameters.AddWithValue("@inn", "");
cmd14.ExecuteNonQuery();
customerId = (int)cmd14.LastInsertedId;
}
}
var lotNum = 1;
var insertLot =
$"INSERT INTO {AppBuilder.Prefix}lot SET id_tender = @id_tender, lot_number = @lot_number, max_price = @max_price, currency = @currency, finance_source = @finance_source";
var cmd18 = new MySqlCommand(insertLot, connect);
cmd18.Prepare();
cmd18.Parameters.AddWithValue("@id_tender", idTender);
cmd18.Parameters.AddWithValue("@lot_number", lotNum);
cmd18.Parameters.AddWithValue("@max_price", "");
cmd18.Parameters.AddWithValue("@currency", "");
cmd18.Parameters.AddWithValue("@finance_source", "");
cmd18.ExecuteNonQuery();
var idLot = (int)cmd18.LastInsertedId;
var insertLotitem =
$"INSERT INTO {AppBuilder.Prefix}purchase_object SET id_lot = @id_lot, id_customer = @id_customer, name = @name";
var cmd19 = new MySqlCommand(insertLotitem, connect);
cmd19.Prepare();
cmd19.Parameters.AddWithValue("@id_lot", idLot);
cmd19.Parameters.AddWithValue("@id_customer", customerId);
cmd19.Parameters.AddWithValue("@name", _tn.PurName);
cmd19.ExecuteNonQuery();
TenderKwords(connect, idTender);
AddVerNumber(connect, _tn.PurNum, TypeFz);
}
}
public void ParsingTender()
{
var s = DownloadString.DownLUserAgent(_tn.Href);
if (string.IsNullOrEmpty(s))
{
Log.Logger("Empty string in ParsingTender()", _tn.Href);
return;
}
var htmlDoc = new HtmlDocument();
htmlDoc.LoadHtml(s);
var navigator = (HtmlNodeNavigator)htmlDoc.CreateNavigator();
var dateUpd = DateTime.Now;
var dateEndT =
navigator
.SelectSingleNode(
"//td[contains(text(), 'Дата и время окончания приема предложений')]/following-sibling::td")
?.Value?.ReplaceHtmlEntyty()?.DelDoubleWhitespace().Trim() ?? throw new Exception(
$"cannot find dateEndT in {_tn.Href}");
var dateEnd = dateEndT.ParseDateUn("dd.MM.yyyy HH:mm");
if (dateEnd == DateTime.MinValue)
{
Log.Logger("Empty dateEnd", _tn.Href);
return;
}
using (var connect = ConnectToDb.GetDbConnection())
{
connect.Open();
var selectTend =
$"SELECT id_tender FROM {AppBuilder.Prefix}tender WHERE purchase_number = @purchase_number AND end_date = @end_date AND type_fz = @type_fz AND doc_publish_date = @doc_publish_date";
var cmd = new MySqlCommand(selectTend, connect);
cmd.Prepare();
cmd.Parameters.AddWithValue("@purchase_number", _tn.PurNum);
cmd.Parameters.AddWithValue("@end_date", dateEnd);
cmd.Parameters.AddWithValue("@type_fz", TypeFz);
cmd.Parameters.AddWithValue("@doc_publish_date", _tn.DatePub);
var dt = new DataTable();
var adapter = new MySqlDataAdapter {
SelectCommand = cmd
};
adapter.Fill(dt);
if (dt.Rows.Count > 0)
{
//Log.Logger("This tender is exist in base", PurNum);
return;
}
var cancelStatus = 0;
var updated = false;
var selectDateT =
$"SELECT id_tender, date_version, cancel FROM {AppBuilder.Prefix}tender WHERE purchase_number = @purchase_number AND type_fz = @type_fz";
var cmd2 = new MySqlCommand(selectDateT, connect);
cmd2.Prepare();
cmd2.Parameters.AddWithValue("@purchase_number", _tn.PurNum);
cmd2.Parameters.AddWithValue("@type_fz", TypeFz);
var adapter2 = new MySqlDataAdapter {
SelectCommand = cmd2
};
var dt2 = new DataTable();
adapter2.Fill(dt2);
foreach (DataRow row in dt2.Rows)
{
//DateTime dateNew = DateTime.Parse(pr.DatePublished);
updated = true;
if (dateUpd >= (DateTime)row["date_version"])
{
row["cancel"] = 1;
//row.AcceptChanges();
//row.SetModified();
}
else
{
cancelStatus = 1;
}
}
var commandBuilder =
new MySqlCommandBuilder(adapter2)
{
ConflictOption = ConflictOption.OverwriteChanges
};
adapter2.Update(dt2);
var printForm = _tn.Href;
var customerId = 0;
var organiserId = 0;
var inn = "";
var orgName =
navigator.SelectSingleNode(
"//td[contains(text(), 'Полное наименование заказчика, место нахождения организации, УНП')]/following-sibling::td/text()[1]")
?.Value?.ReplaceHtmlEntyty()?.Trim() ?? "";
if (!string.IsNullOrEmpty(orgName) || orgName != "-")
{
var selectOrg =
$"SELECT id_organizer FROM {AppBuilder.Prefix}organizer WHERE full_name = @full_name";
var cmd3 = new MySqlCommand(selectOrg, connect);
cmd3.Prepare();
cmd3.Parameters.AddWithValue("@full_name", orgName);
var dt3 = new DataTable();
var adapter3 = new MySqlDataAdapter {
SelectCommand = cmd3
};
adapter3.Fill(dt3);
if (dt3.Rows.Count > 0)
{
organiserId = (int)dt3.Rows[0].ItemArray[0];
}
else
{
var phone = "";
var email = "";
inn = navigator.SelectSingleNode(
"//td[contains(text(), 'Полное наименование заказчика, место нахождения организации, УНП')]/following-sibling::td/text()[3]")
?.Value?.Trim() ?? "";
inn = inn.GetDataFromRegex(@"(\d{9})");
var kpp = "";
var contactPerson = navigator.SelectSingleNode(
"//td[contains(text(), 'Фамилии, имена и отчества, номера телефонов работников заказчика')]/following-sibling::td")
?.Value?.Trim() ?? "";
var address = navigator.SelectSingleNode(
"//td[contains(text(), 'Полное наименование заказчика, место нахождения организации, УНП')]/following-sibling::td/text()[2]")
?.Value?.Trim() ?? "";
var addOrganizer =
$"INSERT INTO {AppBuilder.Prefix}organizer SET full_name = @full_name, contact_phone = @contact_phone, contact_person = @contact_person, contact_email = @contact_email, inn = @inn, kpp = @kpp, fact_address = @fact_address";
var cmd4 = new MySqlCommand(addOrganizer, connect);
cmd4.Prepare();
cmd4.Parameters.AddWithValue("@full_name", orgName);
cmd4.Parameters.AddWithValue("@contact_phone", phone);
cmd4.Parameters.AddWithValue("@contact_person", contactPerson);
cmd4.Parameters.AddWithValue("@contact_email", email);
cmd4.Parameters.AddWithValue("@inn", inn);
cmd4.Parameters.AddWithValue("@kpp", kpp);
cmd4.Parameters.AddWithValue("@fact_address", address);
cmd4.ExecuteNonQuery();
organiserId = (int)cmd4.LastInsertedId;
}
}
var idPlacingWay = 0;
GetEtp(connect, out var idEtp);
var insertTender =
$"INSERT INTO {AppBuilder.Prefix}tender SET id_region = @id_region, id_xml = @id_xml, purchase_number = @purchase_number, doc_publish_date = @doc_publish_date, href = @href, purchase_object_info = @purchase_object_info, type_fz = @type_fz, id_organizer = @id_organizer, id_placing_way = @id_placing_way, id_etp = @id_etp, end_date = @end_date, scoring_date = @scoring_date, bidding_date = @bidding_date, cancel = @cancel, date_version = @date_version, num_version = @num_version, notice_version = @notice_version, xml = @xml, print_form = @print_form";
var cmd9 = new MySqlCommand(insertTender, connect);
cmd9.Prepare();
cmd9.Parameters.AddWithValue("@id_region", 0);
cmd9.Parameters.AddWithValue("@id_xml", _tn.PurNum);
cmd9.Parameters.AddWithValue("@purchase_number", _tn.PurNum);
cmd9.Parameters.AddWithValue("@doc_publish_date", _tn.DatePub);
cmd9.Parameters.AddWithValue("@href", _tn.Href);
cmd9.Parameters.AddWithValue("@purchase_object_info", _tn.PurName);
cmd9.Parameters.AddWithValue("@type_fz", TypeFz);
cmd9.Parameters.AddWithValue("@id_organizer", organiserId);
cmd9.Parameters.AddWithValue("@id_placing_way", idPlacingWay);
cmd9.Parameters.AddWithValue("@id_etp", idEtp);
cmd9.Parameters.AddWithValue("@end_date", dateEnd);
cmd9.Parameters.AddWithValue("@scoring_date", DateTime.MinValue);
cmd9.Parameters.AddWithValue("@bidding_date", DateTime.MinValue);
cmd9.Parameters.AddWithValue("@cancel", cancelStatus);
cmd9.Parameters.AddWithValue("@date_version", dateUpd);
cmd9.Parameters.AddWithValue("@num_version", 1);
cmd9.Parameters.AddWithValue("@notice_version", "");
cmd9.Parameters.AddWithValue("@xml", _tn.Href);
cmd9.Parameters.AddWithValue("@print_form", printForm);
var resInsertTender = cmd9.ExecuteNonQuery();
var idTender = (int)cmd9.LastInsertedId;
Counter(resInsertTender, updated);
if (!string.IsNullOrEmpty(orgName) || orgName != "-")
{
var selectCustomer =
$"SELECT id_customer FROM {AppBuilder.Prefix}customer WHERE full_name = @full_name";
var cmd13 = new MySqlCommand(selectCustomer, connect);
cmd13.Prepare();
cmd13.Parameters.AddWithValue("@full_name", orgName);
var reader7 = cmd13.ExecuteReader();
if (reader7.HasRows)
{
reader7.Read();
customerId = (int)reader7["id_customer"];
reader7.Close();
}
else
{
reader7.Close();
var insertCustomer =
$"INSERT INTO {AppBuilder.Prefix}customer SET reg_num = @reg_num, full_name = @full_name, is223=1, inn = @inn";
var cmd14 = new MySqlCommand(insertCustomer, connect);
cmd14.Prepare();
var customerRegNumber = Guid.NewGuid().ToString();
cmd14.Parameters.AddWithValue("@reg_num", customerRegNumber);
cmd14.Parameters.AddWithValue("@full_name", orgName);
cmd14.Parameters.AddWithValue("@inn", inn);
cmd14.ExecuteNonQuery();
customerId = (int)cmd14.LastInsertedId;
}
}
GetDocs(htmlDoc, connect, idTender);
var requirements = new List <Req>();
var req1 = navigator.SelectSingleNode(
"//td[contains(text(), 'Требования к составу участников')]/following-sibling::td")
?.Value?.Trim() ?? "";
var req2 = navigator.SelectSingleNode(
"//td[contains(text(), 'Квалификационные требования')]/following-sibling::td")
?.Value?.Trim() ?? "";
if (!string.IsNullOrEmpty(req1))
{
requirements.Add(new Req {
Name = "Требования к составу участников", Content = req1
});
}
if (!string.IsNullOrEmpty(req2))
{
requirements.Add(new Req {
Name = "Квалификационные требования", Content = req2
});
}
GetLots(htmlDoc, connect, idTender, requirements, customerId);
TenderKwords(connect, idTender);
AddVerNumber(connect, _tn.PurNum, TypeFz);
}
}
public void ParsingTender()
{
using (var connect = ConnectToDb.GetDbConnection())
{
connect.Open();
var selectTend =
$"SELECT id_tender FROM {AppBuilder.Prefix}tender WHERE purchase_number = @purchase_number AND type_fz = @type_fz AND doc_publish_date = @doc_publish_date AND end_date = @end_date";
var cmd = new MySqlCommand(selectTend, connect);
cmd.Prepare();
cmd.Parameters.AddWithValue("@purchase_number", _tn.PurNum);
cmd.Parameters.AddWithValue("@type_fz", TypeFz);
cmd.Parameters.AddWithValue("@doc_publish_date", _tn.DatePub);
cmd.Parameters.AddWithValue("@end_date", _tn.DateEnd);
var dt = new DataTable();
var adapter = new MySqlDataAdapter {
SelectCommand = cmd
};
adapter.Fill(dt);
if (dt.Rows.Count > 0)
{
return;
}
var dateUpd = DateTime.Now;
var cancelStatus = 0;
var updated = false;
var s = DownloadString.DownLUserAgent(_tn.Href);
if (string.IsNullOrEmpty(s))
{
Log.Logger("Empty string in ParsingTender()", _tn.Href);
return;
}
var htmlDoc = new HtmlDocument();
htmlDoc.LoadHtml(s);
var navigator = (HtmlNodeNavigator)htmlDoc.CreateNavigator();
updated = UpdateVersion(connect, updated, dateUpd, ref cancelStatus);
var printForm = _tn.Href;
var customerId = 0;
var organiserId = 0;
var orgName = EtpName;
organiserId = AddOrganizer(orgName, connect, organiserId);
var idPlacingWay = 0;
GetEtp(connect, out var idEtp);
var status = navigator.SelectSingleNode(
"//span[contains(@class, 'tender-status')]")
?.Value?.Trim() ?? "";
var insertTender =
$"INSERT INTO {AppBuilder.Prefix}tender SET id_region = @id_region, id_xml = @id_xml, purchase_number = @purchase_number, doc_publish_date = @doc_publish_date, href = @href, purchase_object_info = @purchase_object_info, type_fz = @type_fz, id_organizer = @id_organizer, id_placing_way = @id_placing_way, id_etp = @id_etp, end_date = @end_date, scoring_date = @scoring_date, bidding_date = @bidding_date, cancel = @cancel, date_version = @date_version, num_version = @num_version, notice_version = @notice_version, xml = @xml, print_form = @print_form";
var cmd9 = new MySqlCommand(insertTender, connect);
cmd9.Prepare();
cmd9.Parameters.AddWithValue("@id_region", 0);
cmd9.Parameters.AddWithValue("@id_xml", _tn.PurNum);
cmd9.Parameters.AddWithValue("@purchase_number", _tn.PurNum);
cmd9.Parameters.AddWithValue("@doc_publish_date", _tn.DatePub);
cmd9.Parameters.AddWithValue("@href", _tn.Href);
cmd9.Parameters.AddWithValue("@purchase_object_info", _tn.PurName);
cmd9.Parameters.AddWithValue("@type_fz", TypeFz);
cmd9.Parameters.AddWithValue("@id_organizer", organiserId);
cmd9.Parameters.AddWithValue("@id_placing_way", idPlacingWay);
cmd9.Parameters.AddWithValue("@id_etp", idEtp);
cmd9.Parameters.AddWithValue("@end_date", _tn.DateEnd);
cmd9.Parameters.AddWithValue("@scoring_date", DateTime.MinValue);
cmd9.Parameters.AddWithValue("@bidding_date", DateTime.MinValue);
cmd9.Parameters.AddWithValue("@cancel", cancelStatus);
cmd9.Parameters.AddWithValue("@date_version", dateUpd);
cmd9.Parameters.AddWithValue("@num_version", 1);
cmd9.Parameters.AddWithValue("@notice_version", status);
cmd9.Parameters.AddWithValue("@xml", _tn.Href);
cmd9.Parameters.AddWithValue("@print_form", printForm);
var resInsertTender = cmd9.ExecuteNonQuery();
var idTender = (int)cmd9.LastInsertedId;
Counter(resInsertTender, updated);
if (!string.IsNullOrEmpty(_tn.OrgName))
{
var selectCustomer =
$"SELECT id_customer FROM {AppBuilder.Prefix}customer WHERE full_name = @full_name";
var cmd13 = new MySqlCommand(selectCustomer, connect);
cmd13.Prepare();
cmd13.Parameters.AddWithValue("@full_name", _tn.OrgName);
var reader7 = cmd13.ExecuteReader();
if (reader7.HasRows)
{
reader7.Read();
customerId = (int)reader7["id_customer"];
reader7.Close();
}
else
{
reader7.Close();
var insertCustomer =
$"INSERT INTO {AppBuilder.Prefix}customer SET reg_num = @reg_num, full_name = @full_name, is223=1, inn = @inn";
var cmd14 = new MySqlCommand(insertCustomer, connect);
cmd14.Prepare();
var customerRegNumber = Guid.NewGuid().ToString();
cmd14.Parameters.AddWithValue("@reg_num", customerRegNumber);
cmd14.Parameters.AddWithValue("@full_name", _tn.OrgName);
cmd14.Parameters.AddWithValue("@inn", "");
cmd14.ExecuteNonQuery();
customerId = (int)cmd14.LastInsertedId;
}
}
var price = (navigator.SelectSingleNode("//li[contains(., 'Общий бюджет:')]")
?.Value ?? "").ReplaceHtmlEntyty().ExtractPriceNew().Trim();
var lotNum = 1;
var insertLot =
$"INSERT INTO {AppBuilder.Prefix}lot SET id_tender = @id_tender, lot_number = @lot_number, max_price = @max_price, currency = @currency, finance_source = @finance_source";
var cmd18 = new MySqlCommand(insertLot, connect);
cmd18.Prepare();
cmd18.Parameters.AddWithValue("@id_tender", idTender);
cmd18.Parameters.AddWithValue("@lot_number", lotNum);
cmd18.Parameters.AddWithValue("@max_price", price);
cmd18.Parameters.AddWithValue("@currency", "");
cmd18.Parameters.AddWithValue("@finance_source", "");
cmd18.ExecuteNonQuery();
var idLot = (int)cmd18.LastInsertedId;
var insertLotitem =
$"INSERT INTO {AppBuilder.Prefix}purchase_object SET id_lot = @id_lot, id_customer = @id_customer, name = @name, quantity_value = @quantity_value, okei = @okei, customer_quantity_value = @customer_quantity_value, price = @price, sum = @sum";
var cmd20 = new MySqlCommand(insertLotitem, connect);
cmd20.Prepare();
cmd20.Parameters.AddWithValue("@id_lot", idLot);
cmd20.Parameters.AddWithValue("@id_customer", customerId);
cmd20.Parameters.AddWithValue("@name", _tn.PurName);
cmd20.Parameters.AddWithValue("@quantity_value", "");
cmd20.Parameters.AddWithValue("@okei", "");
cmd20.Parameters.AddWithValue("@customer_quantity_value", "");
cmd20.Parameters.AddWithValue("@price", "");
cmd20.Parameters.AddWithValue("@sum", price);
cmd20.ExecuteNonQuery();
if (!string.IsNullOrEmpty(_tn.City))
{
var insertCustomerRequirement =
$"INSERT INTO {AppBuilder.Prefix}customer_requirement SET id_lot = @id_lot, id_customer = @id_customer, delivery_place = @delivery_place, max_price = @max_price, delivery_term = @delivery_term";
var cmd16 = new MySqlCommand(insertCustomerRequirement, connect);
cmd16.Prepare();
cmd16.Parameters.AddWithValue("@id_lot", idLot);
cmd16.Parameters.AddWithValue("@id_customer", customerId);
cmd16.Parameters.AddWithValue("@delivery_place", _tn.City);
cmd16.Parameters.AddWithValue("@max_price", "");
cmd16.Parameters.AddWithValue("@delivery_term", "");
cmd16.ExecuteNonQuery();
}
TenderKwords(connect, idTender);
AddVerNumber(connect, _tn.PurNum, TypeFz);
}
}
public void ParsingTender()
{
using (var connect = ConnectToDb.GetDbConnection())
{
connect.Open();
var selectTend =
$"SELECT id_tender FROM {AppBuilder.Prefix}tender WHERE purchase_number = @purchase_number AND doc_publish_date = @doc_publish_date AND type_fz = @type_fz AND end_date = @end_date";
var cmd = new MySqlCommand(selectTend, connect);
cmd.Prepare();
cmd.Parameters.AddWithValue("@purchase_number", _tn.PurNum);
cmd.Parameters.AddWithValue("@doc_publish_date", _tn.DatePub);
cmd.Parameters.AddWithValue("@type_fz", TypeFz);
cmd.Parameters.AddWithValue("@end_date", _tn.DateEnd);
var dt = new DataTable();
var adapter = new MySqlDataAdapter {
SelectCommand = cmd
};
adapter.Fill(dt);
if (dt.Rows.Count > 0)
{
return;
}
var s = DownloadString.DownLUserAgent(_tn.Href);
if (string.IsNullOrEmpty(s))
{
Log.Logger(
$"Empty string in {GetType().Name}.{System.Reflection.MethodBase.GetCurrentMethod().Name}",
_tn.Href);
return;
}
var tender = JObject.Parse(s);
var dateUpd = DateTime.Now;
var(updated, cancelStatus) = UpdateTenderVersion(connect, _tn.PurNum, dateUpd);
var printForm = _tn.Href;
var organiserId = 0;
var orgName = EtpName;
if (orgName != "")
{
var selectOrg =
$"SELECT id_organizer FROM {AppBuilder.Prefix}organizer WHERE full_name = @full_name";
var cmd3 = new MySqlCommand(selectOrg, connect);
cmd3.Prepare();
cmd3.Parameters.AddWithValue("@full_name", orgName);
var dt3 = new DataTable();
var adapter3 = new MySqlDataAdapter {
SelectCommand = cmd3
};
adapter3.Fill(dt3);
if (dt3.Rows.Count > 0)
{
organiserId = (int)dt3.Rows[0].ItemArray[0];
}
else
{
var phone = "";
var email = "";
var contactPerson = "";
var inn = "";
var kpp = "";
var postAddr = "";
var addOrganizer =
$"INSERT INTO {AppBuilder.Prefix}organizer SET full_name = @full_name, contact_phone = @contact_phone, contact_person = @contact_person, contact_email = @contact_email, inn = @inn, kpp = @kpp, post_address = @post_address";
var cmd4 = new MySqlCommand(addOrganizer, connect);
cmd4.Prepare();
cmd4.Parameters.AddWithValue("@full_name", orgName);
cmd4.Parameters.AddWithValue("@contact_phone", phone);
cmd4.Parameters.AddWithValue("@contact_person", contactPerson);
cmd4.Parameters.AddWithValue("@contact_email", email);
cmd4.Parameters.AddWithValue("@inn", inn);
cmd4.Parameters.AddWithValue("@kpp", kpp);
cmd4.Parameters.AddWithValue("@post_address", postAddr);
cmd4.ExecuteNonQuery();
organiserId = (int)cmd4.LastInsertedId;
}
}
var idPlacingWay = 0;
GetEtp(connect, out var idEtp);
var idRegion = 0;
var regions = GetElements(tender, "regions");
if (regions.Count == 1)
{
var regName = (string)regions[0].SelectToken("name") ?? "";
idRegion = GetRegionFromString(regName, connect);
}
var noticeVersion = (int?)tender.SelectToken("status") ?? 0;
var insertTender =
$"INSERT INTO {AppBuilder.Prefix}tender SET id_region = @id_region, id_xml = @id_xml, purchase_number = @purchase_number, doc_publish_date = @doc_publish_date, href = @href, purchase_object_info = @purchase_object_info, type_fz = @type_fz, id_organizer = @id_organizer, id_placing_way = @id_placing_way, id_etp = @id_etp, end_date = @end_date, scoring_date = @scoring_date, bidding_date = @bidding_date, cancel = @cancel, date_version = @date_version, num_version = @num_version, notice_version = @notice_version, xml = @xml, print_form = @print_form";
var cmd9 = new MySqlCommand(insertTender, connect);
cmd9.Prepare();
cmd9.Parameters.AddWithValue("@id_region", idRegion);
cmd9.Parameters.AddWithValue("@id_xml", _tn.PurNum);
cmd9.Parameters.AddWithValue("@purchase_number", _tn.PurNum);
cmd9.Parameters.AddWithValue("@doc_publish_date", _tn.DatePub);
cmd9.Parameters.AddWithValue("@href", _tn.Href);
cmd9.Parameters.AddWithValue("@purchase_object_info", _tn.PurName);
cmd9.Parameters.AddWithValue("@type_fz", TypeFz);
cmd9.Parameters.AddWithValue("@id_organizer", organiserId);
cmd9.Parameters.AddWithValue("@id_placing_way", idPlacingWay);
cmd9.Parameters.AddWithValue("@id_etp", idEtp);
cmd9.Parameters.AddWithValue("@end_date", _tn.DateEnd);
cmd9.Parameters.AddWithValue("@scoring_date", DateTime.MinValue);
cmd9.Parameters.AddWithValue("@bidding_date", _tn.DateEnd);
cmd9.Parameters.AddWithValue("@cancel", cancelStatus);
cmd9.Parameters.AddWithValue("@date_version", dateUpd);
cmd9.Parameters.AddWithValue("@num_version", 1);
cmd9.Parameters.AddWithValue("@notice_version", noticeVersion);
cmd9.Parameters.AddWithValue("@xml", _tn.Href);
cmd9.Parameters.AddWithValue("@print_form", printForm);
var resInsertTender = cmd9.ExecuteNonQuery();
var idTender = (int)cmd9.LastInsertedId;
Counter(resInsertTender, updated);
var attacments = GetElements(tender, "customer_documents");
WriteAttachments(connect, attacments, idTender);
var lotNum = 1;
var insertLot =
$"INSERT INTO {AppBuilder.Prefix}lot SET id_tender = @id_tender, lot_number = @lot_number, max_price = @max_price, currency = @currency";
var cmd18 = new MySqlCommand(insertLot, connect);
cmd18.Prepare();
cmd18.Parameters.AddWithValue("@id_tender", idTender);
cmd18.Parameters.AddWithValue("@lot_number", lotNum);
cmd18.Parameters.AddWithValue("@max_price", "");
cmd18.Parameters.AddWithValue("@currency", "");
cmd18.ExecuteNonQuery();
var idLot = (int)cmd18.LastInsertedId;
var customerId = 0;
var cusName = ((string)tender.SelectToken("customer_name") ?? "").Trim();
var cusInn = ((string)tender.SelectToken("customer_inn") ?? "").Trim();
if (!string.IsNullOrEmpty(cusName))
{
var selectCustomer =
$"SELECT id_customer FROM {AppBuilder.Prefix}customer WHERE full_name = @full_name";
var cmd13 = new MySqlCommand(selectCustomer, connect);
cmd13.Prepare();
cmd13.Parameters.AddWithValue("@full_name", cusName);
var reader7 = cmd13.ExecuteReader();
if (reader7.HasRows)
{
reader7.Read();
customerId = (int)reader7["id_customer"];
reader7.Close();
}
else
{
reader7.Close();
var insertCustomer =
$"INSERT INTO {AppBuilder.Prefix}customer SET reg_num = @reg_num, full_name = @full_name, inn = @inn, is223=1";
var cmd14 = new MySqlCommand(insertCustomer, connect);
cmd14.Prepare();
var customerRegNumber = Guid.NewGuid().ToString();
cmd14.Parameters.AddWithValue("@reg_num", customerRegNumber);
cmd14.Parameters.AddWithValue("@full_name", cusName);
cmd14.Parameters.AddWithValue("@inn", cusInn);
cmd14.ExecuteNonQuery();
customerId = (int)cmd14.LastInsertedId;
}
}
var restricts = new List <string>();
var forSmallBusiness = (bool?)tender.SelectToken("for_small_business") ?? false;
if (forSmallBusiness)
{
restricts.Add("Субъект малого и среднего предпринимательства");
}
var forProducer = (bool?)tender.SelectToken("for_producer") ?? false;
if (forProducer)
{
restricts.Add("Производитель");
}
var forAuthorizedDealer = (bool?)tender.SelectToken("for_authorized_dealer") ?? false;
if (forAuthorizedDealer)
{
restricts.Add("Официальный дилер");
}
var russianProduction = (bool?)tender.SelectToken("russian_production") ?? false;
if (russianProduction)
{
restricts.Add("Российское производство");
}
var nationalProject = (bool?)tender.SelectToken("national_project") ?? false;
if (nationalProject)
{
restricts.Add("Национальный проект");
}
var denyAlternative = (bool?)tender.SelectToken("deny_alternative") ?? false;
if (denyAlternative)
{
restricts.Add("Запрет альтернатив");
}
foreach (var restrict in restricts)
{
var insertRestrict =
$"INSERT INTO {AppBuilder.Prefix}restricts SET id_lot = @id_lot, foreign_info = @foreign_info, info = @info";
var cmd19 = new MySqlCommand(insertRestrict, connect);
cmd19.Prepare();
cmd19.Parameters.AddWithValue("@id_lot", idLot);
cmd19.Parameters.AddWithValue("@foreign_info", restrict);
cmd19.Parameters.AddWithValue("@info", "");
cmd19.ExecuteNonQuery();
}
var delivDateS = (string)tender.SelectToken("date_delivery") ?? "";
var dateDeliv = delivDateS.ParseDateUn("yyyy-MM-dd");
var delivTerm = (string)tender.SelectToken("delivery_conditions") ?? "";
if (dateDeliv != DateTime.MinValue)
{
delivTerm =
$"{delivTerm}\nПредполагаемая дата поставки / выполнения работ (услуг): {dateDeliv:yyyy-MM-dd}";
}
foreach (var region in regions)
{
var regName = (string)regions[0].SelectToken("name") ?? "";
if (delivTerm != "" || regName != "")
{
var insertCustomerRequirement =
$"INSERT INTO {AppBuilder.Prefix}customer_requirement SET id_lot = @id_lot, id_customer = @id_customer, max_price = @max_price, delivery_place = @delivery_place, delivery_term = @delivery_term";
var cmd16 = new MySqlCommand(insertCustomerRequirement, connect);
cmd16.Prepare();
cmd16.Parameters.AddWithValue("@id_lot", idLot);
cmd16.Parameters.AddWithValue("@id_customer", customerId);
cmd16.Parameters.AddWithValue("@delivery_place", regName);
cmd16.Parameters.AddWithValue("@delivery_term", delivTerm);
cmd16.Parameters.AddWithValue("@max_price", "");
cmd16.ExecuteNonQuery();
}
}
if (regions.Count == 0)
{
var insertCustomerRequirement =
$"INSERT INTO {AppBuilder.Prefix}customer_requirement SET id_lot = @id_lot, id_customer = @id_customer, max_price = @max_price, delivery_place = @delivery_place, delivery_term = @delivery_term";
var cmd16 = new MySqlCommand(insertCustomerRequirement, connect);
cmd16.Prepare();
cmd16.Parameters.AddWithValue("@id_lot", idLot);
cmd16.Parameters.AddWithValue("@id_customer", customerId);
cmd16.Parameters.AddWithValue("@delivery_place", "");
cmd16.Parameters.AddWithValue("@delivery_term", delivTerm);
cmd16.Parameters.AddWithValue("@max_price", "");
cmd16.ExecuteNonQuery();
}
var purObjects = GetElements(tender, "positions");
purObjects.ForEach(po =>
{
var okpdName = ((string)po.SelectToken(
"okpd2_name") ?? "").Trim();
var okpdCode = ((string)po.SelectToken(
"okpd2_code") ?? "").Trim();
var okei = ((string)po.SelectToken(
"okei_name") ?? "").Trim();
var quantity = (string)po.SelectToken(
"quantity") ?? "";
var price = (string)po.SelectToken(
"max_price") ?? "";
var sum = (string)po.SelectToken(
"max_cost") ?? "";
var poName = ((string)po.SelectToken(
"position_name") ?? "").Trim();
var insertLotitem =
$"INSERT INTO {AppBuilder.Prefix}purchase_object SET id_lot = @id_lot, id_customer = @id_customer, name = @name, sum = @sum, okpd2_code = @okpd2_code, okpd2_group_code = @okpd2_group_code, okpd2_group_level1_code = @okpd2_group_level1_code, okpd_name = @okpd_name, quantity_value = @quantity_value, customer_quantity_value = @customer_quantity_value, okei = @okei, price = @price";
var cmd19 = new MySqlCommand(insertLotitem, connect);
cmd19.Prepare();
cmd19.Parameters.AddWithValue("@id_lot", idLot);
cmd19.Parameters.AddWithValue("@id_customer", customerId);
cmd19.Parameters.AddWithValue("@name", poName);
cmd19.Parameters.AddWithValue("@sum", sum);
cmd19.Parameters.AddWithValue("@okpd2_code", okpdCode);
cmd19.Parameters.AddWithValue("@okpd2_group_code", "");
cmd19.Parameters.AddWithValue("@okpd2_group_level1_code", "");
cmd19.Parameters.AddWithValue("@okpd_name", okpdName);
cmd19.Parameters.AddWithValue("@quantity_value", quantity);
cmd19.Parameters.AddWithValue("@customer_quantity_value", quantity);
cmd19.Parameters.AddWithValue("@okei", okei);
cmd19.Parameters.AddWithValue("@price", price);
cmd19.ExecuteNonQuery();
});
TenderKwords(connect, idTender);
AddVerNumber(connect, _tn.PurNum, TypeFz);
}
}
public void ParsingTender()
{
using (var connect = ConnectToDb.GetDbConnection())
{
connect.Open();
var selectTend =
$"SELECT id_tender FROM {AppBuilder.Prefix}tender WHERE purchase_number = @purchase_number AND doc_publish_date = @doc_publish_date AND type_fz = @type_fz AND notice_version = @notice_version";
var cmd = new MySqlCommand(selectTend, connect);
cmd.Prepare();
cmd.Parameters.AddWithValue("@purchase_number", _tn.PurNum);
cmd.Parameters.AddWithValue("@doc_publish_date", _tn.DatePub);
cmd.Parameters.AddWithValue("@type_fz", TypeFz);
cmd.Parameters.AddWithValue("@notice_version", _tn.Status);
var dt = new DataTable();
var adapter = new MySqlDataAdapter {
SelectCommand = cmd
};
adapter.Fill(dt);
if (dt.Rows.Count > 0)
{
//Log.Logger("This tender is exist in base", PurNum);
return;
}
var s = DownloadString.DownL(_tn.Href);
if (String.IsNullOrEmpty(s))
{
Log.Logger(
$"Empty string in {GetType().Name}.{System.Reflection.MethodBase.GetCurrentMethod().Name}",
_tn.Href);
}
var ss = s.GetDataFromRegex(":request=\"(.+)}\"");
ss += "}";
ss = HttpUtility.HtmlDecode(ss);
var tender = JObject.Parse(ss);
var purNum = _tn.PurNum;
var noticeVersion = _tn.Status;
var dateUpd = DateTime.Now;
var(updated, cancelStatus) = UpdateTenderVersion(connect, purNum, dateUpd);
var printForm = _tn.Href;
var customerId = 0;
var organiserId = 0;
if (_tn.CusName != "")
{
var selectOrg =
$"SELECT id_organizer FROM {AppBuilder.Prefix}organizer WHERE full_name = @full_name";
var cmd3 = new MySqlCommand(selectOrg, connect);
cmd3.Prepare();
cmd3.Parameters.AddWithValue("@full_name", _tn.CusName);
var dt3 = new DataTable();
var adapter3 = new MySqlDataAdapter {
SelectCommand = cmd3
};
adapter3.Fill(dt3);
if (dt3.Rows.Count > 0)
{
organiserId = (int)dt3.Rows[0].ItemArray[0];
}
else
{
var phone = ((string)tender.SelectToken("customer.phone") ?? "").Trim();
var email = "";
var contactPerson = ((string)tender.SelectToken("customer.director_fullname") ?? "").Trim();
var inn = ((string)tender.SelectToken("customer.inn") ?? "").Trim();
var kpp = ((string)tender.SelectToken("customer.kpp") ?? "").Trim();
var addOrganizer =
$"INSERT INTO {AppBuilder.Prefix}organizer SET full_name = @full_name, contact_phone = @contact_phone, contact_person = @contact_person, contact_email = @contact_email, inn = @inn, kpp = @kpp";
var cmd4 = new MySqlCommand(addOrganizer, connect);
cmd4.Prepare();
cmd4.Parameters.AddWithValue("@full_name", _tn.CusName);
cmd4.Parameters.AddWithValue("@contact_phone", phone);
cmd4.Parameters.AddWithValue("@contact_person", contactPerson);
cmd4.Parameters.AddWithValue("@contact_email", email);
cmd4.Parameters.AddWithValue("@inn", inn);
cmd4.Parameters.AddWithValue("@kpp", kpp);
cmd4.ExecuteNonQuery();
organiserId = (int)cmd4.LastInsertedId;
}
}
var idPlacingWay = 0;
GetEtp(connect, out var idEtp);
var insertTender =
$"INSERT INTO {AppBuilder.Prefix}tender SET id_region = @id_region, id_xml = @id_xml, purchase_number = @purchase_number, doc_publish_date = @doc_publish_date, href = @href, purchase_object_info = @purchase_object_info, type_fz = @type_fz, id_organizer = @id_organizer, id_placing_way = @id_placing_way, id_etp = @id_etp, end_date = @end_date, scoring_date = @scoring_date, bidding_date = @bidding_date, cancel = @cancel, date_version = @date_version, num_version = @num_version, notice_version = @notice_version, xml = @xml, print_form = @print_form";
var cmd9 = new MySqlCommand(insertTender, connect);
cmd9.Prepare();
cmd9.Parameters.AddWithValue("@id_region", 0);
cmd9.Parameters.AddWithValue("@id_xml", purNum);
cmd9.Parameters.AddWithValue("@purchase_number", purNum);
cmd9.Parameters.AddWithValue("@doc_publish_date", _tn.DatePub);
cmd9.Parameters.AddWithValue("@href", _tn.Href);
cmd9.Parameters.AddWithValue("@purchase_object_info", _tn.PurName);
cmd9.Parameters.AddWithValue("@type_fz", TypeFz);
cmd9.Parameters.AddWithValue("@id_organizer", organiserId);
cmd9.Parameters.AddWithValue("@id_placing_way", idPlacingWay);
cmd9.Parameters.AddWithValue("@id_etp", idEtp);
cmd9.Parameters.AddWithValue("@end_date", _tn.DateEnd);
cmd9.Parameters.AddWithValue("@scoring_date", DateTime.MinValue);
cmd9.Parameters.AddWithValue("@bidding_date", DateTime.MinValue);
cmd9.Parameters.AddWithValue("@cancel", cancelStatus);
cmd9.Parameters.AddWithValue("@date_version", dateUpd);
cmd9.Parameters.AddWithValue("@num_version", 1);
cmd9.Parameters.AddWithValue("@notice_version", noticeVersion);
cmd9.Parameters.AddWithValue("@xml", _tn.Href);
cmd9.Parameters.AddWithValue("@print_form", printForm);
var resInsertTender = cmd9.ExecuteNonQuery();
var idTender = (int)cmd9.LastInsertedId;
Counter(resInsertTender, updated);
var attachments = GetElements(tender, "current_condition.attachments_files");
attachments.AddRange(GetElements(tender, "current_condition.interaction_files"));
foreach (var att in attachments)
{
var name = ((string)att.SelectToken("name") ?? "").Trim();
var fName = ((string)att.SelectToken("file.original_name") ?? "").Trim();
var webPath = ((string)att.SelectToken("web_path") ?? "").Trim();
var urlAtt = $"https://sberb2b.ru{webPath}{name}";
if (!string.IsNullOrEmpty(fName))
{
var insertAttach =
$"INSERT INTO {AppBuilder.Prefix}attachment SET id_tender = @id_tender, file_name = @file_name, url = @url";
var cmd10 = new MySqlCommand(insertAttach, connect);
cmd10.Prepare();
cmd10.Parameters.AddWithValue("@id_tender", idTender);
cmd10.Parameters.AddWithValue("@file_name", fName);
cmd10.Parameters.AddWithValue("@url", urlAtt);
cmd10.ExecuteNonQuery();
}
}
var lotNum = 1;
var nmck = (decimal?)tender.SelectToken("last_customer_published_condition.total_price") ?? 0.0m;
var insertLot =
$"INSERT INTO {AppBuilder.Prefix}lot SET id_tender = @id_tender, lot_number = @lot_number, max_price = @max_price, currency = @currency";
var cmd18 = new MySqlCommand(insertLot, connect);
cmd18.Prepare();
cmd18.Parameters.AddWithValue("@id_tender", idTender);
cmd18.Parameters.AddWithValue("@lot_number", lotNum);
cmd18.Parameters.AddWithValue("@max_price", nmck);
cmd18.Parameters.AddWithValue("@currency", "");
cmd18.ExecuteNonQuery();
var idLot = (int)cmd18.LastInsertedId;
if (!string.IsNullOrEmpty(_tn.CusName))
{
var selectCustomer =
$"SELECT id_customer FROM {AppBuilder.Prefix}customer WHERE full_name = @full_name";
var cmd13 = new MySqlCommand(selectCustomer, connect);
cmd13.Prepare();
cmd13.Parameters.AddWithValue("@full_name", _tn.CusName);
var reader7 = cmd13.ExecuteReader();
if (reader7.HasRows)
{
reader7.Read();
customerId = (int)reader7["id_customer"];
reader7.Close();
}
else
{
reader7.Close();
var insertCustomer =
$"INSERT INTO {AppBuilder.Prefix}customer SET reg_num = @reg_num, full_name = @full_name, inn = @inn, is223=1";
var cmd14 = new MySqlCommand(insertCustomer, connect);
cmd14.Prepare();
var customerRegNumber = Guid.NewGuid().ToString();
var inn = ((string)tender.SelectToken("customer.inn") ?? "").Trim();
cmd14.Parameters.AddWithValue("@reg_num", customerRegNumber);
cmd14.Parameters.AddWithValue("@full_name", _tn.CusName);
cmd14.Parameters.AddWithValue("@inn", inn);
cmd14.ExecuteNonQuery();
customerId = (int)cmd14.LastInsertedId;
}
}
var insertLotitem =
$"INSERT INTO {AppBuilder.Prefix}purchase_object SET id_lot = @id_lot, id_customer = @id_customer, name = @name, sum = @sum, okpd2_code = @okpd2_code, okpd2_group_code = @okpd2_group_code, okpd2_group_level1_code = @okpd2_group_level1_code, okpd_name = @okpd_name";
var cmd19 = new MySqlCommand(insertLotitem, connect);
cmd19.Prepare();
cmd19.Parameters.AddWithValue("@id_lot", idLot);
cmd19.Parameters.AddWithValue("@id_customer", customerId);
cmd19.Parameters.AddWithValue("@name", _tn.PurName);
cmd19.Parameters.AddWithValue("@sum", nmck);
cmd19.Parameters.AddWithValue("@okpd2_code", "");
cmd19.Parameters.AddWithValue("@okpd2_group_code", "");
cmd19.Parameters.AddWithValue("@okpd2_group_level1_code", "");
cmd19.Parameters.AddWithValue("@okpd_name", "");
cmd19.ExecuteNonQuery();
var delivPl =
((string)tender.SelectToken(
"last_customer_published_condition.condition_deliveries[0].builded_string") ?? "").Trim();
var delivTerm =
((string)tender.SelectToken(
"last_customer_published_condition.condition_payment.string_representation") ?? "").Trim();
if (delivTerm != "" || delivPl != "")
{
var insertCustomerRequirement =
$"INSERT INTO {AppBuilder.Prefix}customer_requirement SET id_lot = @id_lot, id_customer = @id_customer, max_price = @max_price, delivery_place = @delivery_place, delivery_term = @delivery_term";
var cmd16 = new MySqlCommand(insertCustomerRequirement, connect);
cmd16.Prepare();
cmd16.Parameters.AddWithValue("@id_lot", idLot);
cmd16.Parameters.AddWithValue("@id_customer", customerId);
cmd16.Parameters.AddWithValue("@delivery_place", delivPl);
cmd16.Parameters.AddWithValue("@delivery_term", delivTerm);
cmd16.Parameters.AddWithValue("@max_price", nmck);
cmd16.ExecuteNonQuery();
}
TenderKwords(connect, idTender);
AddVerNumber(connect, purNum, TypeFz);
}
}
public void ParsingTender()
{
using (var connect = ConnectToDb.GetDbConnection())
{
connect.Open();
var selectTend =
$"SELECT id_tender FROM {AppBuilder.Prefix}tender WHERE purchase_number = @purchase_number AND end_date = @end_date AND type_fz = @type_fz AND doc_publish_date = @doc_publish_date";
var cmd = new MySqlCommand(selectTend, connect);
cmd.Prepare();
cmd.Parameters.AddWithValue("@purchase_number", _tn.PurNum);
cmd.Parameters.AddWithValue("@end_date", _tn.DateEnd);
cmd.Parameters.AddWithValue("@type_fz", TypeFz);
cmd.Parameters.AddWithValue("@doc_publish_date", _tn.DatePub);
var dt = new DataTable();
var adapter = new MySqlDataAdapter {
SelectCommand = cmd
};
adapter.Fill(dt);
if (dt.Rows.Count > 0)
{
//Log.Logger("This tender is exist in base", PurNum);
return;
}
var wait = new WebDriverWait(_driver, TimeSpan.FromSeconds(30));
_driver.Navigate().GoToUrl(_tn.Href);
Thread.Sleep(3000);
wait.Until(dr =>
dr.FindElement(By.XPath(
"//th[. = 'Номер']")));
var dateUpd = DateTime.Now;
var cancelStatus = 0;
var updated = false;
var selectDateT =
$"SELECT id_tender, date_version, cancel FROM {AppBuilder.Prefix}tender WHERE purchase_number = @purchase_number AND type_fz = @type_fz";
var cmd2 = new MySqlCommand(selectDateT, connect);
cmd2.Prepare();
cmd2.Parameters.AddWithValue("@purchase_number", _tn.PurNum);
cmd2.Parameters.AddWithValue("@type_fz", TypeFz);
var adapter2 = new MySqlDataAdapter {
SelectCommand = cmd2
};
var dt2 = new DataTable();
adapter2.Fill(dt2);
foreach (DataRow row in dt2.Rows)
{
//DateTime dateNew = DateTime.Parse(pr.DatePublished);
updated = true;
if (dateUpd >= (DateTime)row["date_version"])
{
row["cancel"] = 1;
//row.AcceptChanges();
//row.SetModified();
}
else
{
cancelStatus = 1;
}
}
var commandBuilder =
new MySqlCommandBuilder(adapter2)
{
ConflictOption = ConflictOption.OverwriteChanges
};
adapter2.Update(dt2);
var printForm = _tn.Href;
var customerId = 0;
var organiserId = 0;
var orgName = "ОАО «Нафтан»";
if (!string.IsNullOrEmpty(orgName))
{
var selectOrg =
$"SELECT id_organizer FROM {AppBuilder.Prefix}organizer WHERE full_name = @full_name";
var cmd3 = new MySqlCommand(selectOrg, connect);
cmd3.Prepare();
cmd3.Parameters.AddWithValue("@full_name", orgName);
var dt3 = new DataTable();
var adapter3 = new MySqlDataAdapter {
SelectCommand = cmd3
};
adapter3.Fill(dt3);
if (dt3.Rows.Count > 0)
{
organiserId = (int)dt3.Rows[0].ItemArray[0];
}
else
{
var phone = "+375(214)59-82-57";
var email = "*****@*****.**";
var inn = "300042199";
var kpp = "";
var contactPerson = "";
var addOrganizer =
$"INSERT INTO {AppBuilder.Prefix}organizer SET full_name = @full_name, contact_phone = @contact_phone, contact_person = @contact_person, contact_email = @contact_email, inn = @inn, kpp = @kpp";
var cmd4 = new MySqlCommand(addOrganizer, connect);
cmd4.Prepare();
cmd4.Parameters.AddWithValue("@full_name", orgName);
cmd4.Parameters.AddWithValue("@contact_phone", phone);
cmd4.Parameters.AddWithValue("@contact_person", contactPerson);
cmd4.Parameters.AddWithValue("@contact_email", email);
cmd4.Parameters.AddWithValue("@inn", inn);
cmd4.Parameters.AddWithValue("@kpp", kpp);
cmd4.ExecuteNonQuery();
organiserId = (int)cmd4.LastInsertedId;
}
}
PlacingWay = _driver
.FindElement(By.XPath("//td[contains(., 'Процедура закупки')]/following-sibling::td/strong"))
?.Text.Trim();
GetPlacingWay(connect, out var idPlacingWay);
GetEtp(connect, out var idEtp);
var insertTender =
$"INSERT INTO {AppBuilder.Prefix}tender SET id_region = @id_region, id_xml = @id_xml, purchase_number = @purchase_number, doc_publish_date = @doc_publish_date, href = @href, purchase_object_info = @purchase_object_info, type_fz = @type_fz, id_organizer = @id_organizer, id_placing_way = @id_placing_way, id_etp = @id_etp, end_date = @end_date, scoring_date = @scoring_date, bidding_date = @bidding_date, cancel = @cancel, date_version = @date_version, num_version = @num_version, notice_version = @notice_version, xml = @xml, print_form = @print_form";
var cmd9 = new MySqlCommand(insertTender, connect);
cmd9.Prepare();
cmd9.Parameters.AddWithValue("@id_region", 0);
cmd9.Parameters.AddWithValue("@id_xml", _tn.PurNum);
cmd9.Parameters.AddWithValue("@purchase_number", _tn.PurNum);
cmd9.Parameters.AddWithValue("@doc_publish_date", _tn.DatePub);
cmd9.Parameters.AddWithValue("@href", _tn.Href);
cmd9.Parameters.AddWithValue("@purchase_object_info", _tn.PurName);
cmd9.Parameters.AddWithValue("@type_fz", TypeFz);
cmd9.Parameters.AddWithValue("@id_organizer", organiserId);
cmd9.Parameters.AddWithValue("@id_placing_way", idPlacingWay);
cmd9.Parameters.AddWithValue("@id_etp", idEtp);
cmd9.Parameters.AddWithValue("@end_date", _tn.DateEnd);
cmd9.Parameters.AddWithValue("@scoring_date", DateTime.MinValue);
cmd9.Parameters.AddWithValue("@bidding_date", DateTime.MinValue);
cmd9.Parameters.AddWithValue("@cancel", cancelStatus);
cmd9.Parameters.AddWithValue("@date_version", dateUpd);
cmd9.Parameters.AddWithValue("@num_version", 1);
cmd9.Parameters.AddWithValue("@notice_version", "");
cmd9.Parameters.AddWithValue("@xml", _tn.Href);
cmd9.Parameters.AddWithValue("@print_form", printForm);
var resInsertTender = cmd9.ExecuteNonQuery();
var idTender = (int)cmd9.LastInsertedId;
Counter(resInsertTender, updated);
if (!string.IsNullOrEmpty(orgName))
{
var selectCustomer =
$"SELECT id_customer FROM {AppBuilder.Prefix}customer WHERE full_name = @full_name";
var cmd13 = new MySqlCommand(selectCustomer, connect);
cmd13.Prepare();
cmd13.Parameters.AddWithValue("@full_name", orgName);
var reader7 = cmd13.ExecuteReader();
if (reader7.HasRows)
{
reader7.Read();
customerId = (int)reader7["id_customer"];
reader7.Close();
}
else
{
reader7.Close();
var insertCustomer =
$"INSERT INTO {AppBuilder.Prefix}customer SET reg_num = @reg_num, full_name = @full_name, is223=1";
var cmd14 = new MySqlCommand(insertCustomer, connect);
cmd14.Prepare();
var customerRegNumber = Guid.NewGuid().ToString();
cmd14.Parameters.AddWithValue("@reg_num", customerRegNumber);
cmd14.Parameters.AddWithValue("@full_name", orgName);
cmd14.ExecuteNonQuery();
customerId = (int)cmd14.LastInsertedId;
}
}
var docs = _driver.FindElements(
By.XPath(
"//p/a[starts-with(@href, '/ru/Home/Download')]"));
ExtractDocs(docs, connect, idTender);
var lotNum = 1;
var insertLot =
$"INSERT INTO {AppBuilder.Prefix}lot SET id_tender = @id_tender, lot_number = @lot_number, max_price = @max_price, currency = @currency, finance_source = @finance_source";
var cmd18 = new MySqlCommand(insertLot, connect);
cmd18.Prepare();
cmd18.Parameters.AddWithValue("@id_tender", idTender);
cmd18.Parameters.AddWithValue("@lot_number", lotNum);
cmd18.Parameters.AddWithValue("@max_price", "");
cmd18.Parameters.AddWithValue("@currency", "");
cmd18.Parameters.AddWithValue("@finance_source", "");
cmd18.ExecuteNonQuery();
var idLot = (int)cmd18.LastInsertedId;
var insertLotitem =
$"INSERT INTO {AppBuilder.Prefix}purchase_object SET id_lot = @id_lot, id_customer = @id_customer, name = @name, sum = @sum";
var cmd19 = new MySqlCommand(insertLotitem, connect);
cmd19.Prepare();
cmd19.Parameters.AddWithValue("@id_lot", idLot);
cmd19.Parameters.AddWithValue("@id_customer", customerId);
cmd19.Parameters.AddWithValue("@name", _tn.PurName);
cmd19.Parameters.AddWithValue("@sum", "");
cmd19.ExecuteNonQuery();
TenderKwords(connect, idTender);
AddVerNumber(connect, _tn.PurNum, TypeFz);
}
void ExtractDocs(ReadOnlyCollection <IWebElement> docs, MySqlConnection con, int idTender)
{
foreach (var doc in docs)
{
var urlAtt = (doc.GetAttribute("href") ?? "").Trim();
var fName = (doc.Text ?? "").Trim();
if (string.IsNullOrEmpty(fName))
{
continue;
}
var insertAttach =
$"INSERT INTO {AppBuilder.Prefix}attachment SET id_tender = @id_tender, file_name = @file_name, url = @url";
var cmd10 = new MySqlCommand(insertAttach, con);
cmd10.Prepare();
cmd10.Parameters.AddWithValue("@id_tender", idTender);
cmd10.Parameters.AddWithValue("@file_name", fName);
cmd10.Parameters.AddWithValue("@url", urlAtt);
cmd10.ExecuteNonQuery();
}
}
}
public void ParsingTender()
{
using (var connect = ConnectToDb.GetDbConnection())
{
connect.Open();
var selectTend =
$"SELECT id_tender FROM {AppBuilder.Prefix}tender WHERE purchase_number = @purchase_number AND type_fz = @type_fz";
var cmd = new MySqlCommand(selectTend, connect);
cmd.Prepare();
cmd.Parameters.AddWithValue("@purchase_number", _tn.PurNum);
cmd.Parameters.AddWithValue("@type_fz", TypeFz);
var dt = new DataTable();
var adapter = new MySqlDataAdapter {
SelectCommand = cmd
};
adapter.Fill(dt);
if (dt.Rows.Count > 0)
{
return;
}
var dateUpd = DateTime.Now;
var(updated, cancelStatus) = UpdateTenderVersion(connect, _tn.PurNum, dateUpd);
var printForm = _tn.Href;
var customerId = 0;
var organiserId = 0;
var orgName = EtpName;
organiserId = AddOrganizer(orgName, connect, organiserId);
var idPlacingWay = 0;
GetEtp(connect, out var idEtp);
var insertTender =
$"INSERT INTO {AppBuilder.Prefix}tender SET id_region = @id_region, id_xml = @id_xml, purchase_number = @purchase_number, doc_publish_date = @doc_publish_date, href = @href, purchase_object_info = @purchase_object_info, type_fz = @type_fz, id_organizer = @id_organizer, id_placing_way = @id_placing_way, id_etp = @id_etp, end_date = @end_date, scoring_date = @scoring_date, bidding_date = @bidding_date, cancel = @cancel, date_version = @date_version, num_version = @num_version, notice_version = @notice_version, xml = @xml, print_form = @print_form";
var cmd9 = new MySqlCommand(insertTender, connect);
cmd9.Prepare();
cmd9.Parameters.AddWithValue("@id_region", 0);
cmd9.Parameters.AddWithValue("@id_xml", _tn.PurNum);
cmd9.Parameters.AddWithValue("@purchase_number", _tn.PurNum);
cmd9.Parameters.AddWithValue("@doc_publish_date", _tn.DatePub);
cmd9.Parameters.AddWithValue("@href", EtpUrl);
cmd9.Parameters.AddWithValue("@purchase_object_info", _tn.PurName);
cmd9.Parameters.AddWithValue("@type_fz", TypeFz);
cmd9.Parameters.AddWithValue("@id_organizer", organiserId);
cmd9.Parameters.AddWithValue("@id_placing_way", idPlacingWay);
cmd9.Parameters.AddWithValue("@id_etp", idEtp);
cmd9.Parameters.AddWithValue("@end_date", _tn.DateEnd);
cmd9.Parameters.AddWithValue("@scoring_date", DateTime.MinValue);
cmd9.Parameters.AddWithValue("@bidding_date", DateTime.MinValue);
cmd9.Parameters.AddWithValue("@cancel", cancelStatus);
cmd9.Parameters.AddWithValue("@date_version", dateUpd);
cmd9.Parameters.AddWithValue("@num_version", 1);
cmd9.Parameters.AddWithValue("@notice_version", "");
cmd9.Parameters.AddWithValue("@xml", EtpUrl);
cmd9.Parameters.AddWithValue("@print_form", printForm);
var resInsertTender = cmd9.ExecuteNonQuery();
var idTender = (int)cmd9.LastInsertedId;
Counter(resInsertTender, updated);
if (!string.IsNullOrEmpty(orgName))
{
var selectCustomer =
$"SELECT id_customer FROM {AppBuilder.Prefix}customer WHERE full_name = @full_name";
var cmd13 = new MySqlCommand(selectCustomer, connect);
cmd13.Prepare();
cmd13.Parameters.AddWithValue("@full_name", orgName);
var reader7 = cmd13.ExecuteReader();
if (reader7.HasRows)
{
reader7.Read();
customerId = (int)reader7["id_customer"];
reader7.Close();
}
else
{
reader7.Close();
var insertCustomer =
$"INSERT INTO {AppBuilder.Prefix}customer SET reg_num = @reg_num, full_name = @full_name, is223=1, inn = @inn";
var cmd14 = new MySqlCommand(insertCustomer, connect);
cmd14.Prepare();
var customerRegNumber = Guid.NewGuid().ToString();
cmd14.Parameters.AddWithValue("@reg_num", customerRegNumber);
cmd14.Parameters.AddWithValue("@full_name", orgName);
cmd14.Parameters.AddWithValue("@inn", "");
cmd14.ExecuteNonQuery();
customerId = (int)cmd14.LastInsertedId;
}
}
/*var insertAttach =
* $"INSERT INTO {AppBuilder.Prefix}attachment SET id_tender = @id_tender, file_name = @file_name, url = @url";
* var cmd10 = new MySqlCommand(insertAttach, connect);
* cmd10.Prepare();
* cmd10.Parameters.AddWithValue("@id_tender", idTender);
* cmd10.Parameters.AddWithValue("@file_name", "Пакет документов");
* cmd10.Parameters.AddWithValue("@url", _tn.Href);
* cmd10.ExecuteNonQuery();*/
var lotNum = 1;
var insertLot =
$"INSERT INTO {AppBuilder.Prefix}lot SET id_tender = @id_tender, lot_number = @lot_number, max_price = @max_price, currency = @currency, finance_source = @finance_source";
var cmd18 = new MySqlCommand(insertLot, connect);
cmd18.Prepare();
cmd18.Parameters.AddWithValue("@id_tender", idTender);
cmd18.Parameters.AddWithValue("@lot_number", lotNum);
cmd18.Parameters.AddWithValue("@max_price", _tn.Nmck);
cmd18.Parameters.AddWithValue("@currency", "");
cmd18.Parameters.AddWithValue("@finance_source", "");
cmd18.ExecuteNonQuery();
var idLot = (int)cmd18.LastInsertedId;
var insertLotitem =
$"INSERT INTO {AppBuilder.Prefix}purchase_object SET id_lot = @id_lot, id_customer = @id_customer, name = @name, quantity_value = @quantity_value, okei = @okei, customer_quantity_value = @customer_quantity_value, price = @price, sum = @sum";
var cmd20 = new MySqlCommand(insertLotitem, connect);
cmd20.Prepare();
cmd20.Parameters.AddWithValue("@id_lot", idLot);
cmd20.Parameters.AddWithValue("@id_customer", customerId);
cmd20.Parameters.AddWithValue("@name", _tn.PurName);
cmd20.Parameters.AddWithValue("@quantity_value", "");
cmd20.Parameters.AddWithValue("@okei", "");
cmd20.Parameters.AddWithValue("@customer_quantity_value", "");
cmd20.Parameters.AddWithValue("@price", "");
cmd20.Parameters.AddWithValue("@sum", "");
cmd20.ExecuteNonQuery();
TenderKwords(connect, idTender);
AddVerNumber(connect, _tn.PurNum, TypeFz);
}
}
public void ParsingTender()
{
using (var connect = ConnectToDb.GetDbConnection())
{
connect.Open();
var selectTend =
$"SELECT id_tender FROM {AppBuilder.Prefix}tender WHERE purchase_number = @purchase_number AND type_fz = @type_fz AND doc_publish_date = @doc_publish_date AND end_date = @end_date AND notice_version = @notice_version";
var cmd = new MySqlCommand(selectTend, connect);
cmd.Prepare();
cmd.Parameters.AddWithValue("@purchase_number", _tn.PurNum);
cmd.Parameters.AddWithValue("@type_fz", TypeFz);
cmd.Parameters.AddWithValue("@doc_publish_date", _tn.DatePub);
cmd.Parameters.AddWithValue("@end_date", _tn.DateEnd);
cmd.Parameters.AddWithValue("@notice_version", _tn.Status);
var dt = new DataTable();
var adapter = new MySqlDataAdapter {
SelectCommand = cmd
};
adapter.Fill(dt);
if (dt.Rows.Count > 0)
{
return;
}
var s = DownloadString.DownLUserAgent(_tn.Href);
if (string.IsNullOrEmpty(s))
{
Log.Logger("Empty string in ParsingTender()", _tn.Href);
return;
}
var htmlDoc = new HtmlDocument();
htmlDoc.LoadHtml(s);
var navigator = (HtmlNodeNavigator)htmlDoc.CreateNavigator();
var dateUpd = DateTime.Now;
var(updated, cancelStatus) = UpdateTenderVersion(connect, _tn.PurNum, dateUpd);
var printForm = _tn.Href;
var customerId = 0;
var organiserId = 0;
var orgName = EtpName;
GetPlacingWay(connect, out var idPlacingWay);
GetEtp(connect, out var idEtp);
organiserId = AddOrganizer(orgName, connect, organiserId);
var biddingDateT = (navigator
.SelectSingleNode(
"//td[. = 'Дата подведения итогов:']/following-sibling::td/div")
?.Value ?? "").Trim();
var biddingDate = biddingDateT.ParseDateUn("dd.MM.yyyy");
var insertTender =
$"INSERT INTO {AppBuilder.Prefix}tender SET id_region = @id_region, id_xml = @id_xml, purchase_number = @purchase_number, doc_publish_date = @doc_publish_date, href = @href, purchase_object_info = @purchase_object_info, type_fz = @type_fz, id_organizer = @id_organizer, id_placing_way = @id_placing_way, id_etp = @id_etp, end_date = @end_date, scoring_date = @scoring_date, bidding_date = @bidding_date, cancel = @cancel, date_version = @date_version, num_version = @num_version, notice_version = @notice_version, xml = @xml, print_form = @print_form";
var cmd9 = new MySqlCommand(insertTender, connect);
cmd9.Prepare();
cmd9.Parameters.AddWithValue("@id_region", 0);
cmd9.Parameters.AddWithValue("@id_xml", _tn.PurNum);
cmd9.Parameters.AddWithValue("@purchase_number", _tn.PurNum);
cmd9.Parameters.AddWithValue("@doc_publish_date", _tn.DatePub);
cmd9.Parameters.AddWithValue("@href", _tn.Href);
cmd9.Parameters.AddWithValue("@purchase_object_info", _tn.PurName);
cmd9.Parameters.AddWithValue("@type_fz", TypeFz);
cmd9.Parameters.AddWithValue("@id_organizer", organiserId);
cmd9.Parameters.AddWithValue("@id_placing_way", idPlacingWay);
cmd9.Parameters.AddWithValue("@id_etp", idEtp);
cmd9.Parameters.AddWithValue("@end_date", _tn.DateEnd);
cmd9.Parameters.AddWithValue("@scoring_date", DateTime.MinValue);
cmd9.Parameters.AddWithValue("@bidding_date", biddingDate);
cmd9.Parameters.AddWithValue("@cancel", cancelStatus);
cmd9.Parameters.AddWithValue("@date_version", dateUpd);
cmd9.Parameters.AddWithValue("@num_version", 1);
cmd9.Parameters.AddWithValue("@notice_version", _tn.Status);
cmd9.Parameters.AddWithValue("@xml", _tn.Href);
cmd9.Parameters.AddWithValue("@print_form", printForm);
var resInsertTender = cmd9.ExecuteNonQuery();
var idTender = (int)cmd9.LastInsertedId;
Counter(resInsertTender, updated);
if (!string.IsNullOrEmpty(_tn.CusName))
{
var selectCustomer =
$"SELECT id_customer FROM {AppBuilder.Prefix}customer WHERE full_name = @full_name";
var cmd13 = new MySqlCommand(selectCustomer, connect);
cmd13.Prepare();
cmd13.Parameters.AddWithValue("@full_name", _tn.CusName);
var reader7 = cmd13.ExecuteReader();
if (reader7.HasRows)
{
reader7.Read();
customerId = (int)reader7["id_customer"];
reader7.Close();
}
else
{
reader7.Close();
var insertCustomer =
$"INSERT INTO {AppBuilder.Prefix}customer SET reg_num = @reg_num, full_name = @full_name, is223=1, inn = @inn";
var cmd14 = new MySqlCommand(insertCustomer, connect);
cmd14.Prepare();
var customerRegNumber = Guid.NewGuid().ToString();
cmd14.Parameters.AddWithValue("@reg_num", customerRegNumber);
cmd14.Parameters.AddWithValue("@full_name", _tn.CusName);
cmd14.Parameters.AddWithValue("@inn", "");
cmd14.ExecuteNonQuery();
customerId = (int)cmd14.LastInsertedId;
}
}
var docs = htmlDoc.DocumentNode.SelectNodes(
"//div[@class = 'doc-item']") ??
new HtmlNodeCollection(null);
foreach (var doc in docs)
{
var urlAttT = (doc.SelectSingleNode(".//a")?.Attributes["href"]?.Value ?? "").Trim();
var fName = doc.InnerText.Replace("Скачать", "").Trim();
var urlAtt = $"https://pptk-mos.ru{urlAttT}";
if (!string.IsNullOrEmpty(fName))
{
var insertAttach =
$"INSERT INTO {AppBuilder.Prefix}attachment SET id_tender = @id_tender, file_name = @file_name, url = @url";
var cmd10 = new MySqlCommand(insertAttach, connect);
cmd10.Prepare();
cmd10.Parameters.AddWithValue("@id_tender", idTender);
cmd10.Parameters.AddWithValue("@file_name", fName);
cmd10.Parameters.AddWithValue("@url", urlAtt);
cmd10.ExecuteNonQuery();
}
}
var lotNum = 1;
var insertLot =
$"INSERT INTO {AppBuilder.Prefix}lot SET id_tender = @id_tender, lot_number = @lot_number, max_price = @max_price, currency = @currency, finance_source = @finance_source";
var cmd18 = new MySqlCommand(insertLot, connect);
cmd18.Prepare();
cmd18.Parameters.AddWithValue("@id_tender", idTender);
cmd18.Parameters.AddWithValue("@lot_number", lotNum);
cmd18.Parameters.AddWithValue("@max_price", _tn.Nmck);
cmd18.Parameters.AddWithValue("@currency", "");
cmd18.Parameters.AddWithValue("@finance_source", "");
cmd18.ExecuteNonQuery();
var idLot = (int)cmd18.LastInsertedId;
var insertLotitem =
$"INSERT INTO {AppBuilder.Prefix}purchase_object SET id_lot = @id_lot, id_customer = @id_customer, name = @name, quantity_value = @quantity_value, okei = @okei, customer_quantity_value = @customer_quantity_value, price = @price, sum = @sum";
var cmd20 = new MySqlCommand(insertLotitem, connect);
cmd20.Prepare();
cmd20.Parameters.AddWithValue("@id_lot", idLot);
cmd20.Parameters.AddWithValue("@id_customer", customerId);
cmd20.Parameters.AddWithValue("@name", _tn.PurName);
cmd20.Parameters.AddWithValue("@quantity_value", "");
cmd20.Parameters.AddWithValue("@okei", "");
cmd20.Parameters.AddWithValue("@customer_quantity_value", "");
cmd20.Parameters.AddWithValue("@price", "");
cmd20.Parameters.AddWithValue("@sum", _tn.Nmck);
cmd20.ExecuteNonQuery();
TenderKwords(connect, idTender);
AddVerNumber(connect, _tn.PurNum, TypeFz);
}
}
public void ParsingTender()
{
using (var connect = ConnectToDb.GetDbConnection())
{
connect.Open();
var selectTend =
$"SELECT id_tender FROM {AppBuilder.Prefix}tender WHERE purchase_number = @purchase_number AND doc_publish_date = @doc_publish_date AND type_fz = @type_fz AND notice_version = @notice_version AND end_date = @end_date";
var cmd = new MySqlCommand(selectTend, connect);
cmd.Prepare();
cmd.Parameters.AddWithValue("@purchase_number", _tn.PurNum);
cmd.Parameters.AddWithValue("@doc_publish_date", _tn.DatePub);
cmd.Parameters.AddWithValue("@type_fz", TypeFz);
cmd.Parameters.AddWithValue("@notice_version", _tn.Status);
cmd.Parameters.AddWithValue("@end_date", _tn.DateEnd);
var dt = new DataTable();
var adapter = new MySqlDataAdapter {
SelectCommand = cmd
};
adapter.Fill(dt);
if (dt.Rows.Count > 0)
{
//Log.Logger("This tender is exist in base", PurNum);
return;
}
var dateUpd = DateTime.Now;
var(updated, cancelStatus) = UpdateTenderVersion(connect, _tn.PurNum, dateUpd);
var printForm = _tn.Href;
var organiserId = 0;
var orgName = ((string)_tn.JsonT.SelectToken("organizer.@Name") ?? "").Trim();
if (orgName != "")
{
var selectOrg =
$"SELECT id_organizer FROM {AppBuilder.Prefix}organizer WHERE full_name = @full_name";
var cmd3 = new MySqlCommand(selectOrg, connect);
cmd3.Prepare();
cmd3.Parameters.AddWithValue("@full_name", orgName);
var dt3 = new DataTable();
var adapter3 = new MySqlDataAdapter {
SelectCommand = cmd3
};
adapter3.Fill(dt3);
if (dt3.Rows.Count > 0)
{
organiserId = (int)dt3.Rows[0].ItemArray[0];
}
else
{
var phone = ((string)_tn.JsonT.SelectToken("organizer.@Phone") ?? "").Trim();
var email = ((string)_tn.JsonT.SelectToken("organizer.@Email") ?? "").Trim();
var contactPerson = "";
var inn = ((string)_tn.JsonT.SelectToken("organizer.@INN") ?? "").Trim();
var kpp = ((string)_tn.JsonT.SelectToken("organizer.@KPP") ?? "").Trim();
var postAddr = ((string)_tn.JsonT.SelectToken("organizer.@Address") ?? "").Trim();
var addOrganizer =
$"INSERT INTO {AppBuilder.Prefix}organizer SET full_name = @full_name, contact_phone = @contact_phone, contact_person = @contact_person, contact_email = @contact_email, inn = @inn, kpp = @kpp, post_address = @post_address";
var cmd4 = new MySqlCommand(addOrganizer, connect);
cmd4.Prepare();
cmd4.Parameters.AddWithValue("@full_name", orgName);
cmd4.Parameters.AddWithValue("@contact_phone", phone);
cmd4.Parameters.AddWithValue("@contact_person", contactPerson);
cmd4.Parameters.AddWithValue("@contact_email", email);
cmd4.Parameters.AddWithValue("@inn", inn);
cmd4.Parameters.AddWithValue("@kpp", kpp);
cmd4.Parameters.AddWithValue("@post_address", postAddr);
cmd4.ExecuteNonQuery();
organiserId = (int)cmd4.LastInsertedId;
}
}
GetEtp(connect, out var idEtp);
PlacingWay = ((string)_tn.JsonT.SelectToken("@ContractType") ?? "").Trim();
GetPlacingWay(connect, out var idPlacingWay);
var insertTender =
$"INSERT INTO {AppBuilder.Prefix}tender SET id_region = @id_region, id_xml = @id_xml, purchase_number = @purchase_number, doc_publish_date = @doc_publish_date, href = @href, purchase_object_info = @purchase_object_info, type_fz = @type_fz, id_organizer = @id_organizer, id_placing_way = @id_placing_way, id_etp = @id_etp, end_date = @end_date, scoring_date = @scoring_date, bidding_date = @bidding_date, cancel = @cancel, date_version = @date_version, num_version = @num_version, notice_version = @notice_version, xml = @xml, print_form = @print_form";
var cmd9 = new MySqlCommand(insertTender, connect);
cmd9.Prepare();
cmd9.Parameters.AddWithValue("@id_region", 0);
cmd9.Parameters.AddWithValue("@id_xml", _tn.PurNum);
cmd9.Parameters.AddWithValue("@purchase_number", _tn.PurNum);
cmd9.Parameters.AddWithValue("@doc_publish_date", _tn.DatePub);
cmd9.Parameters.AddWithValue("@href", _tn.Href);
cmd9.Parameters.AddWithValue("@purchase_object_info", _tn.PurName);
cmd9.Parameters.AddWithValue("@type_fz", TypeFz);
cmd9.Parameters.AddWithValue("@id_organizer", organiserId);
cmd9.Parameters.AddWithValue("@id_placing_way", idPlacingWay);
cmd9.Parameters.AddWithValue("@id_etp", idEtp);
cmd9.Parameters.AddWithValue("@end_date", _tn.DateEnd);
cmd9.Parameters.AddWithValue("@scoring_date", DateTime.MinValue);
cmd9.Parameters.AddWithValue("@bidding_date", DateTime.MinValue);
cmd9.Parameters.AddWithValue("@cancel", cancelStatus);
cmd9.Parameters.AddWithValue("@date_version", dateUpd);
cmd9.Parameters.AddWithValue("@num_version", 1);
cmd9.Parameters.AddWithValue("@notice_version", _tn.Status);
cmd9.Parameters.AddWithValue("@xml", _tn.Href);
cmd9.Parameters.AddWithValue("@print_form", printForm);
var resInsertTender = cmd9.ExecuteNonQuery();
var idTender = (int)cmd9.LastInsertedId;
Counter(resInsertTender, updated);
var attachments = GetElements(_tn.JsonT, "docs.doc");
foreach (var att in attachments)
{
var fName = ((string)att.SelectToken("@Name") ?? "").Trim();
var urlAtt = ((string)att.SelectToken("@Link") ?? "").Trim();
if (!string.IsNullOrEmpty(fName))
{
var insertAttach =
$"INSERT INTO {AppBuilder.Prefix}attachment SET id_tender = @id_tender, file_name = @file_name, url = @url";
var cmd10 = new MySqlCommand(insertAttach, connect);
cmd10.Prepare();
cmd10.Parameters.AddWithValue("@id_tender", idTender);
cmd10.Parameters.AddWithValue("@file_name", fName);
cmd10.Parameters.AddWithValue("@url", urlAtt);
cmd10.ExecuteNonQuery();
}
}
var lots = GetElements(_tn.JsonT, "lots.lot");
var lotNum = 1;
foreach (var lot in lots)
{
var nmck = ((string)lot.SelectToken("@Price") ?? "").Trim();
var currency = ((string)lot.SelectToken("@Currency") ?? "").Trim();
var lotName = ((string)lot.SelectToken("@Name") ?? "").Trim();
var insertLot =
$"INSERT INTO {AppBuilder.Prefix}lot SET id_tender = @id_tender, lot_number = @lot_number, max_price = @max_price, currency = @currency, lot_name = @lot_name";
var cmd18 = new MySqlCommand(insertLot, connect);
cmd18.Prepare();
cmd18.Parameters.AddWithValue("@id_tender", idTender);
cmd18.Parameters.AddWithValue("@lot_number", lotNum);
cmd18.Parameters.AddWithValue("@max_price", nmck);
cmd18.Parameters.AddWithValue("@currency", currency);
cmd18.Parameters.AddWithValue("@lot_name", lotName);
cmd18.ExecuteNonQuery();
var idLot = (int)cmd18.LastInsertedId;
lotNum++;
var customerId = 0;
var cusName = ((string)lot.SelectToken("customer.@Name") ?? "").Trim();
if (!string.IsNullOrEmpty(cusName))
{
var selectCustomer =
$"SELECT id_customer FROM {AppBuilder.Prefix}customer WHERE full_name = @full_name";
var cmd13 = new MySqlCommand(selectCustomer, connect);
cmd13.Prepare();
cmd13.Parameters.AddWithValue("@full_name", cusName);
var reader7 = cmd13.ExecuteReader();
if (reader7.HasRows)
{
reader7.Read();
customerId = (int)reader7["id_customer"];
reader7.Close();
}
else
{
reader7.Close();
var insertCustomer =
$"INSERT INTO {AppBuilder.Prefix}customer SET reg_num = @reg_num, full_name = @full_name, inn = @inn, is223=1";
var cmd14 = new MySqlCommand(insertCustomer, connect);
cmd14.Prepare();
var customerRegNumber = Guid.NewGuid().ToString();
var inn = ((string)lot.SelectToken("customer.@INN") ?? "").Trim();
cmd14.Parameters.AddWithValue("@reg_num", customerRegNumber);
cmd14.Parameters.AddWithValue("@full_name", cusName);
cmd14.Parameters.AddWithValue("@inn", inn);
cmd14.ExecuteNonQuery();
customerId = (int)cmd14.LastInsertedId;
}
}
var purObs = GetElements(lot, "goods.good");
foreach (var po in purObs)
{
var poName = ((string)po.SelectToken("@Name") ?? "").Trim();
var poPrice = ((string)po.SelectToken("@Price") ?? "").Trim();
var poOkei = ((string)po.SelectToken("@OKEI") ?? "").Trim();
var poQuant = ((string)po.SelectToken("@Quantity") ?? "").Trim();
var poOkpd = ((string)po.SelectToken("@OKDP") ?? "").Trim();
var insertLotitem =
$"INSERT INTO {AppBuilder.Prefix}purchase_object SET id_lot = @id_lot, id_customer = @id_customer, name = @name, sum = @sum, okpd2_code = @okpd2_code, okpd2_group_code = @okpd2_group_code, okpd2_group_level1_code = @okpd2_group_level1_code, okpd_name = @okpd_name, price = @price, quantity_value = @quantity_value, okei = @okei, customer_quantity_value = @customer_quantity_value";
var cmd19 = new MySqlCommand(insertLotitem, connect);
cmd19.Prepare();
cmd19.Parameters.AddWithValue("@id_lot", idLot);
cmd19.Parameters.AddWithValue("@id_customer", customerId);
cmd19.Parameters.AddWithValue("@name", poName);
cmd19.Parameters.AddWithValue("@sum", nmck);
cmd19.Parameters.AddWithValue("@okpd2_code", poOkpd);
cmd19.Parameters.AddWithValue("@okpd2_group_code", "");
cmd19.Parameters.AddWithValue("@okpd2_group_level1_code", "");
cmd19.Parameters.AddWithValue("@okpd_name", "");
cmd19.Parameters.AddWithValue("@price", poPrice);
cmd19.Parameters.AddWithValue("@quantity_value", poQuant);
cmd19.Parameters.AddWithValue("@customer_quantity_value", poQuant);
cmd19.Parameters.AddWithValue("@okei", poOkei);
cmd19.ExecuteNonQuery();
}
if (purObs.Count == 0)
{
var insertLotitem =
$"INSERT INTO {AppBuilder.Prefix}purchase_object SET id_lot = @id_lot, id_customer = @id_customer, name = @name, sum = @sum, okpd2_code = @okpd2_code, okpd2_group_code = @okpd2_group_code, okpd2_group_level1_code = @okpd2_group_level1_code, okpd_name = @okpd_name";
var cmd19 = new MySqlCommand(insertLotitem, connect);
cmd19.Prepare();
cmd19.Parameters.AddWithValue("@id_lot", idLot);
cmd19.Parameters.AddWithValue("@id_customer", customerId);
cmd19.Parameters.AddWithValue("@name", lotName);
cmd19.Parameters.AddWithValue("@sum", nmck);
cmd19.Parameters.AddWithValue("@okpd2_code", "");
cmd19.Parameters.AddWithValue("@okpd2_group_code", "");
cmd19.Parameters.AddWithValue("@okpd2_group_level1_code", "");
cmd19.Parameters.AddWithValue("@okpd_name", "");
cmd19.ExecuteNonQuery();
}
}
TenderKwords(connect, idTender);
AddVerNumber(connect, _tn.PurNum, TypeFz);
}
}
public void ParsingTender()
{
using (var connect = ConnectToDb.GetDbConnection())
{
var dateUpd = DateTime.Now;
connect.Open();
var selectTend =
$"SELECT id_tender FROM {AppBuilder.Prefix}tender WHERE purchase_number = @purchase_number AND end_date = @end_date AND type_fz = @type_fz";
var cmd = new MySqlCommand(selectTend, connect);
cmd.Prepare();
cmd.Parameters.AddWithValue("@purchase_number", _tn.PurNum);
cmd.Parameters.AddWithValue("@end_date", _tn.DateEnd);
cmd.Parameters.AddWithValue("@type_fz", TypeFz);
var dt = new DataTable();
var adapter = new MySqlDataAdapter {
SelectCommand = cmd
};
adapter.Fill(dt);
if (dt.Rows.Count > 0)
{
return;
}
var cancelStatus = 0;
var updated = false;
var selectDateT =
$"SELECT id_tender, date_version, cancel FROM {AppBuilder.Prefix}tender WHERE purchase_number = @purchase_number AND type_fz = @type_fz";
var cmd2 = new MySqlCommand(selectDateT, connect);
cmd2.Prepare();
cmd2.Parameters.AddWithValue("@purchase_number", _tn.PurNum);
cmd2.Parameters.AddWithValue("@type_fz", TypeFz);
var adapter2 = new MySqlDataAdapter {
SelectCommand = cmd2
};
var dt2 = new DataTable();
adapter2.Fill(dt2);
foreach (DataRow row in dt2.Rows)
{
updated = true;
if (dateUpd >= (DateTime)row["date_version"])
{
row["cancel"] = 1;
//row.AcceptChanges();
//row.SetModified();
}
else
{
cancelStatus = 1;
}
}
var commandBuilder =
new MySqlCommandBuilder(adapter2)
{
ConflictOption = ConflictOption.OverwriteChanges
};
adapter2.Update(dt2);
var printForm = _tn.Href;
var customerId = 0;
var organiserId = 0;
if (!string.IsNullOrEmpty(EtpName))
{
var selectOrg =
$"SELECT id_organizer FROM {AppBuilder.Prefix}organizer WHERE full_name = @full_name";
var cmd3 = new MySqlCommand(selectOrg, connect);
cmd3.Prepare();
cmd3.Parameters.AddWithValue("@full_name", EtpName);
var dt3 = new DataTable();
var adapter3 = new MySqlDataAdapter {
SelectCommand = cmd3
};
adapter3.Fill(dt3);
if (dt3.Rows.Count > 0)
{
organiserId = (int)dt3.Rows[0].ItemArray[0];
}
else
{
var phone = "";
var email = "";
var contactPerson = "";
var addOrganizer =
$"INSERT INTO {AppBuilder.Prefix}organizer SET full_name = @full_name, contact_phone = @contact_phone, contact_person = @contact_person, contact_email = @contact_email";
var cmd4 = new MySqlCommand(addOrganizer, connect);
cmd4.Prepare();
cmd4.Parameters.AddWithValue("@full_name", EtpName);
cmd4.Parameters.AddWithValue("@contact_phone", phone);
cmd4.Parameters.AddWithValue("@contact_person", contactPerson);
cmd4.Parameters.AddWithValue("@contact_email", email);
cmd4.ExecuteNonQuery();
organiserId = (int)cmd4.LastInsertedId;
}
}
GetEtp(connect, out var idEtp);
var idPlacingWay = 0;
var insertTender =
$"INSERT INTO {AppBuilder.Prefix}tender SET id_region = @id_region, id_xml = @id_xml, purchase_number = @purchase_number, doc_publish_date = @doc_publish_date, href = @href, purchase_object_info = @purchase_object_info, type_fz = @type_fz, id_organizer = @id_organizer, id_placing_way = @id_placing_way, id_etp = @id_etp, end_date = @end_date, scoring_date = @scoring_date, bidding_date = @bidding_date, cancel = @cancel, date_version = @date_version, num_version = @num_version, notice_version = @notice_version, xml = @xml, print_form = @print_form";
var cmd9 = new MySqlCommand(insertTender, connect);
cmd9.Prepare();
cmd9.Parameters.AddWithValue("@id_region", 0);
cmd9.Parameters.AddWithValue("@id_xml", _tn.PurNum);
cmd9.Parameters.AddWithValue("@purchase_number", _tn.PurNum);
cmd9.Parameters.AddWithValue("@doc_publish_date", _tn.DatePub);
cmd9.Parameters.AddWithValue("@href", _tn.Href);
cmd9.Parameters.AddWithValue("@purchase_object_info", _tn.PurName);
cmd9.Parameters.AddWithValue("@type_fz", TypeFz);
cmd9.Parameters.AddWithValue("@id_organizer", organiserId);
cmd9.Parameters.AddWithValue("@id_placing_way", idPlacingWay);
cmd9.Parameters.AddWithValue("@id_etp", idEtp);
cmd9.Parameters.AddWithValue("@end_date", _tn.DateEnd);
cmd9.Parameters.AddWithValue("@scoring_date", DateTime.MinValue);
cmd9.Parameters.AddWithValue("@bidding_date", DateTime.MinValue);
cmd9.Parameters.AddWithValue("@cancel", cancelStatus);
cmd9.Parameters.AddWithValue("@date_version", dateUpd);
cmd9.Parameters.AddWithValue("@num_version", 1);
cmd9.Parameters.AddWithValue("@notice_version", _tn.Notice);
cmd9.Parameters.AddWithValue("@xml", _tn.Href);
cmd9.Parameters.AddWithValue("@print_form", printForm);
var resInsertTender = cmd9.ExecuteNonQuery();
var idTender = (int)cmd9.LastInsertedId;
Counter(resInsertTender, updated);
if (!string.IsNullOrEmpty(EtpName))
{
var selectCustomer =
$"SELECT id_customer FROM {AppBuilder.Prefix}customer WHERE full_name = @full_name";
var cmd13 = new MySqlCommand(selectCustomer, connect);
cmd13.Prepare();
cmd13.Parameters.AddWithValue("@full_name", EtpName);
var reader7 = cmd13.ExecuteReader();
if (reader7.HasRows)
{
reader7.Read();
customerId = (int)reader7["id_customer"];
reader7.Close();
}
else
{
reader7.Close();
var insertCustomer =
$"INSERT INTO {AppBuilder.Prefix}customer SET reg_num = @reg_num, full_name = @full_name, is223=1";
var cmd14 = new MySqlCommand(insertCustomer, connect);
cmd14.Prepare();
var customerRegNumber = Guid.NewGuid().ToString();
cmd14.Parameters.AddWithValue("@reg_num", customerRegNumber);
cmd14.Parameters.AddWithValue("@full_name", EtpName);
cmd14.ExecuteNonQuery();
customerId = (int)cmd14.LastInsertedId;
}
}
foreach (var dd in _tn.Attachments)
{
if (string.IsNullOrEmpty(dd.Name))
{
continue;
}
var insertAttach =
$"INSERT INTO {AppBuilder.Prefix}attachment SET id_tender = @id_tender, file_name = @file_name, url = @url";
var cmd10 = new MySqlCommand(insertAttach, connect);
cmd10.Prepare();
cmd10.Parameters.AddWithValue("@id_tender", idTender);
cmd10.Parameters.AddWithValue("@file_name", dd.Name);
cmd10.Parameters.AddWithValue("@url", dd.Url);
cmd10.ExecuteNonQuery();
}
var lotNum = 1;
var insertLot =
$"INSERT INTO {AppBuilder.Prefix}lot SET id_tender = @id_tender, lot_number = @lot_number, max_price = @max_price, currency = @currency, finance_source = @finance_source";
var cmd18 = new MySqlCommand(insertLot, connect);
cmd18.Prepare();
cmd18.Parameters.AddWithValue("@id_tender", idTender);
cmd18.Parameters.AddWithValue("@lot_number", lotNum);
cmd18.Parameters.AddWithValue("@max_price", "");
cmd18.Parameters.AddWithValue("@currency", "");
cmd18.Parameters.AddWithValue("@finance_source", "");
cmd18.ExecuteNonQuery();
var idLot = (int)cmd18.LastInsertedId;
var insertLotitem =
$"INSERT INTO {AppBuilder.Prefix}purchase_object SET id_lot = @id_lot, id_customer = @id_customer, name = @name, quantity_value = @quantity_value, okei = @okei, customer_quantity_value = @customer_quantity_value, price = @price, sum = @sum";
var cmd20 = new MySqlCommand(insertLotitem, connect);
cmd20.Prepare();
cmd20.Parameters.AddWithValue("@id_lot", idLot);
cmd20.Parameters.AddWithValue("@id_customer", customerId);
cmd20.Parameters.AddWithValue("@name", _tn.PurName);
cmd20.Parameters.AddWithValue("@quantity_value", "");
cmd20.Parameters.AddWithValue("@okei", "");
cmd20.Parameters.AddWithValue("@customer_quantity_value", "");
cmd20.Parameters.AddWithValue("@price", "");
cmd20.Parameters.AddWithValue("@sum", "");
cmd20.ExecuteNonQuery();
TenderKwords(connect, idTender);
AddVerNumber(connect, _tn.PurNum, TypeFz);
}
}
public void ParsingTender()
{
using (var connect = ConnectToDb.GetDbConnection())
{
connect.Open();
var selectTend =
$"SELECT id_tender FROM {AppBuilder.Prefix}tender WHERE purchase_number = @purchase_number AND end_date = @end_date AND type_fz = @type_fz AND doc_publish_date = @doc_publish_date AND notice_version = @notice_version";
var cmd = new MySqlCommand(selectTend, connect);
cmd.Prepare();
cmd.Parameters.AddWithValue("@purchase_number", _tn.PurNum);
cmd.Parameters.AddWithValue("@end_date", _tn.DateEnd);
cmd.Parameters.AddWithValue("@type_fz", TypeFz);
cmd.Parameters.AddWithValue("@doc_publish_date", _tn.DatePub);
cmd.Parameters.AddWithValue("@notice_version", _tn.Status);
var dt = new DataTable();
var adapter = new MySqlDataAdapter {
SelectCommand = cmd
};
adapter.Fill(dt);
if (dt.Rows.Count > 0)
{
//Log.Logger("This tender is exist in base", PurNum);
return;
}
Thread.Sleep(5000);
var s = DownloadString.DownLUserAgent(_tn.Href);
if (string.IsNullOrEmpty(s))
{
Log.Logger("Empty string in ParsingTender()", _tn.Href);
return;
}
var htmlDoc = new HtmlDocument();
htmlDoc.LoadHtml(s);
var navigator = (HtmlNodeNavigator)htmlDoc.CreateNavigator();
var dateUpd = DateTime.Now;
var cancelStatus = 0;
var updated = false;
var selectDateT =
$"SELECT id_tender, date_version, cancel FROM {AppBuilder.Prefix}tender WHERE purchase_number = @purchase_number AND type_fz = @type_fz";
var cmd2 = new MySqlCommand(selectDateT, connect);
cmd2.Prepare();
cmd2.Parameters.AddWithValue("@purchase_number", _tn.PurNum);
cmd2.Parameters.AddWithValue("@type_fz", TypeFz);
var adapter2 = new MySqlDataAdapter {
SelectCommand = cmd2
};
var dt2 = new DataTable();
adapter2.Fill(dt2);
foreach (DataRow row in dt2.Rows)
{
//DateTime dateNew = DateTime.Parse(pr.DatePublished);
updated = true;
if (dateUpd >= (DateTime)row["date_version"])
{
row["cancel"] = 1;
//row.AcceptChanges();
//row.SetModified();
}
else
{
cancelStatus = 1;
}
}
var commandBuilder =
new MySqlCommandBuilder(adapter2)
{
ConflictOption = ConflictOption.OverwriteChanges
};
adapter2.Update(dt2);
var printForm = _tn.Href;
var customerId = 0;
var organiserId = 0;
var orgName = (navigator
.SelectSingleNode(
"//div[contains(strong, 'Предприятие')]/ul/li[1]/text()[1]")
?.Value ?? "").Trim();
if (!string.IsNullOrEmpty(orgName))
{
var selectOrg =
$"SELECT id_organizer FROM {AppBuilder.Prefix}organizer WHERE full_name = @full_name";
var cmd3 = new MySqlCommand(selectOrg, connect);
cmd3.Prepare();
cmd3.Parameters.AddWithValue("@full_name", orgName);
var dt3 = new DataTable();
var adapter3 = new MySqlDataAdapter {
SelectCommand = cmd3
};
adapter3.Fill(dt3);
if (dt3.Rows.Count > 0)
{
organiserId = (int)dt3.Rows[0].ItemArray[0];
}
else
{
var phone = (navigator
.SelectSingleNode(
"//li[contains(., 'По процедурным и организационным вопросам:')]/following-sibling::ul/li[3]")
?.Value.Replace("Тел.:", "").Replace(" ", "") ?? "").Trim();
var email = (navigator
.SelectSingleNode(
"//li[contains(., 'По процедурным и организационным вопросам:')]/following-sibling::ul/li[2]/a")
?.Value.Replace("Тел.:", "").Replace(" ", "") ?? "").Trim();
var inn = (navigator
.SelectSingleNode(
"//div[contains(strong, 'Предприятие')]/ul/li[1]/text()[2]")
?.Value.Replace("ИНН:", "") ?? "").Trim();
var kpp = (navigator
.SelectSingleNode(
"//div[contains(strong, 'Предприятие')]/ul/li[1]/text()[3]")
?.Value.Replace("КПП:", "") ?? "").Trim();
var contactPerson = (navigator
.SelectSingleNode(
"//li[contains(., 'По процедурным и организационным вопросам:')]/following-sibling::ul/li[1]/i")
?.Value.Replace("Тел.:", "").Replace(" ", "") ?? "").Trim();
var addOrganizer =
$"INSERT INTO {AppBuilder.Prefix}organizer SET full_name = @full_name, contact_phone = @contact_phone, contact_person = @contact_person, contact_email = @contact_email, inn = @inn, kpp = @kpp";
var cmd4 = new MySqlCommand(addOrganizer, connect);
cmd4.Prepare();
cmd4.Parameters.AddWithValue("@full_name", orgName);
cmd4.Parameters.AddWithValue("@contact_phone", phone);
cmd4.Parameters.AddWithValue("@contact_person", contactPerson);
cmd4.Parameters.AddWithValue("@contact_email", email);
cmd4.Parameters.AddWithValue("@inn", inn);
cmd4.Parameters.AddWithValue("@kpp", kpp);
cmd4.ExecuteNonQuery();
organiserId = (int)cmd4.LastInsertedId;
}
}
var idPlacingWay = 0;
GetEtp(connect, out var idEtp);
var insertTender =
$"INSERT INTO {AppBuilder.Prefix}tender SET id_region = @id_region, id_xml = @id_xml, purchase_number = @purchase_number, doc_publish_date = @doc_publish_date, href = @href, purchase_object_info = @purchase_object_info, type_fz = @type_fz, id_organizer = @id_organizer, id_placing_way = @id_placing_way, id_etp = @id_etp, end_date = @end_date, scoring_date = @scoring_date, bidding_date = @bidding_date, cancel = @cancel, date_version = @date_version, num_version = @num_version, notice_version = @notice_version, xml = @xml, print_form = @print_form";
var cmd9 = new MySqlCommand(insertTender, connect);
cmd9.Prepare();
cmd9.Parameters.AddWithValue("@id_region", 0);
cmd9.Parameters.AddWithValue("@id_xml", _tn.PurNum);
cmd9.Parameters.AddWithValue("@purchase_number", _tn.PurNum);
cmd9.Parameters.AddWithValue("@doc_publish_date", _tn.DatePub);
cmd9.Parameters.AddWithValue("@href", _tn.Href);
cmd9.Parameters.AddWithValue("@purchase_object_info", _tn.PurName);
cmd9.Parameters.AddWithValue("@type_fz", TypeFz);
cmd9.Parameters.AddWithValue("@id_organizer", organiserId);
cmd9.Parameters.AddWithValue("@id_placing_way", idPlacingWay);
cmd9.Parameters.AddWithValue("@id_etp", idEtp);
cmd9.Parameters.AddWithValue("@end_date", _tn.DateEnd);
cmd9.Parameters.AddWithValue("@scoring_date", DateTime.MinValue);
cmd9.Parameters.AddWithValue("@bidding_date", DateTime.MinValue);
cmd9.Parameters.AddWithValue("@cancel", cancelStatus);
cmd9.Parameters.AddWithValue("@date_version", dateUpd);
cmd9.Parameters.AddWithValue("@num_version", 1);
cmd9.Parameters.AddWithValue("@notice_version", _tn.Status);
cmd9.Parameters.AddWithValue("@xml", _tn.Href);
cmd9.Parameters.AddWithValue("@print_form", printForm);
var resInsertTender = cmd9.ExecuteNonQuery();
var idTender = (int)cmd9.LastInsertedId;
Counter(resInsertTender, updated);
if (!string.IsNullOrEmpty(orgName))
{
var selectCustomer =
$"SELECT id_customer FROM {AppBuilder.Prefix}customer WHERE full_name = @full_name";
var cmd13 = new MySqlCommand(selectCustomer, connect);
cmd13.Prepare();
cmd13.Parameters.AddWithValue("@full_name", orgName);
var reader7 = cmd13.ExecuteReader();
if (reader7.HasRows)
{
reader7.Read();
customerId = (int)reader7["id_customer"];
reader7.Close();
}
else
{
reader7.Close();
var insertCustomer =
$"INSERT INTO {AppBuilder.Prefix}customer SET reg_num = @reg_num, full_name = @full_name, is223=1";
var cmd14 = new MySqlCommand(insertCustomer, connect);
cmd14.Prepare();
var customerRegNumber = Guid.NewGuid().ToString();
cmd14.Parameters.AddWithValue("@reg_num", customerRegNumber);
cmd14.Parameters.AddWithValue("@full_name", orgName);
cmd14.ExecuteNonQuery();
customerId = (int)cmd14.LastInsertedId;
}
}
var docs = htmlDoc.DocumentNode.SelectNodes(
"//span[contains(., 'Документы:')]/following-sibling::ul/li/a") ??
new HtmlNodeCollection(null);
foreach (var doc in docs)
{
var urlAttT = (doc?.Attributes["href"]?.Value ?? "").Trim();
var fName = (doc?.InnerText ?? "").Trim();
var urlAtt = $"http://sibintek.ru{urlAttT}";
if (!string.IsNullOrEmpty(fName))
{
var insertAttach =
$"INSERT INTO {AppBuilder.Prefix}attachment SET id_tender = @id_tender, file_name = @file_name, url = @url";
var cmd10 = new MySqlCommand(insertAttach, connect);
cmd10.Prepare();
cmd10.Parameters.AddWithValue("@id_tender", idTender);
cmd10.Parameters.AddWithValue("@file_name", fName);
cmd10.Parameters.AddWithValue("@url", urlAtt);
cmd10.ExecuteNonQuery();
}
}
var nmck = (navigator
.SelectSingleNode(
"//li[contains(b, 'Сведения о начальной цене договора (цене лота)')]/following-sibling::ul/li[1]")
?.Value ?? "").Trim();
nmck = nmck.GetDataFromRegex(@"([\d ,]+)\s");
nmck = nmck.Replace(" ", "").Replace(",", ".");
nmck = Regex.Replace(nmck, @"\s+", "");
var lotNum = 1;
var insertLot =
$"INSERT INTO {AppBuilder.Prefix}lot SET id_tender = @id_tender, lot_number = @lot_number, max_price = @max_price, currency = @currency, finance_source = @finance_source";
var cmd18 = new MySqlCommand(insertLot, connect);
cmd18.Prepare();
cmd18.Parameters.AddWithValue("@id_tender", idTender);
cmd18.Parameters.AddWithValue("@lot_number", lotNum);
cmd18.Parameters.AddWithValue("@max_price", nmck);
cmd18.Parameters.AddWithValue("@currency", "");
cmd18.Parameters.AddWithValue("@finance_source", "");
cmd18.ExecuteNonQuery();
var idLot = (int)cmd18.LastInsertedId;
var insertLotitem =
$"INSERT INTO {AppBuilder.Prefix}purchase_object SET id_lot = @id_lot, id_customer = @id_customer, name = @name, sum = @sum";
var cmd19 = new MySqlCommand(insertLotitem, connect);
cmd19.Prepare();
cmd19.Parameters.AddWithValue("@id_lot", idLot);
cmd19.Parameters.AddWithValue("@id_customer", customerId);
cmd19.Parameters.AddWithValue("@name", _tn.PurName);
cmd19.Parameters.AddWithValue("@sum", nmck);
cmd19.ExecuteNonQuery();
TenderKwords(connect, idTender);
AddVerNumber(connect, _tn.PurNum, TypeFz);
}
}
