private void ParsingPage(string url) { var s = DownloadString.DownL(url); if (string.IsNullOrEmpty(s)) { Log.Logger("Empty string in ParserPage()", url); return; } var htmlDoc = new HtmlDocument(); htmlDoc.LoadHtml(s); var tens = htmlDoc.DocumentNode.SelectNodes("//div[contains(@class, 'b-table__container')]") ?? new HtmlNodeCollection(null); foreach (var a in tens) { try { ParserTender(a); } catch (Exception e) { Log.Logger(e); } } }
private void ParsingPage(string url, bool tektkp = false) { var s = DownloadString.DownL(url); if (string.IsNullOrEmpty(s)) { Log.Logger($"Empty string in {GetType().Name}.{System.Reflection.MethodBase.GetCurrentMethod().Name}", url); } var parser = new HtmlParser(); var document = parser.Parse(s); var tens = document.All.Where(m => m.ClassList.Contains("section-procurement__item") && m.TagName == "DIV"); foreach (var t in tens) { try { ParsingTender(t, url, tektkp); } catch (Exception e) { Log.Logger(e); } } }
private void GetDocuments(int idTender, MySqlConnection connect) { var s = DownloadString.DownL(_tn.Href); if (string.IsNullOrEmpty(s)) { Log.Logger("Empty string in GetDocuments()", _tn.Href); return; } var htmlDoc = new HtmlDocument(); htmlDoc.LoadHtml(s); var docs = htmlDoc.DocumentNode.SelectNodes("//ul[contains(@class, 'tenders-d__list')]/li") ?? new HtmlNodeCollection(null); foreach (var doc in docs) { var urlAttT = (doc.SelectSingleNode("./a")?.Attributes["href"]?.Value ?? "") .Trim(); var fName = (doc.SelectSingleNode("./p").InnerText ?? "Закупочная документация").Trim(); var urlAtt = $"https://kzgroup.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(); } } }
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() { 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() { 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() { 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() { 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 end_date = @end_date AND date_version = @date_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("@end_date", _tn.DateEnd); cmd.Parameters.AddWithValue("@date_version", _tn.DateUpd); var dt = new DataTable(); var adapter = new MySqlDataAdapter { SelectCommand = cmd }; adapter.Fill(dt); if (dt.Rows.Count > 0) { return; } var s = DownloadString.DownL(_tn.Href, tryCount: 5); if (string.IsNullOrEmpty(s)) { Log.Logger("Empty string in ParsingTender()", _tn.Href); return; } Thread.Sleep(5000); var htmlDoc = new HtmlDocument(); htmlDoc.LoadHtml(s); var navigator = (HtmlNodeNavigator)htmlDoc.CreateNavigator(); var datePubT = htmlDoc.DocumentNode .SelectSingleNode( "//td[. = 'Дата публикации']/following-sibling::td") ?.InnerText.Replace("МСК", "").Trim() ?? ""; var datePub = datePubT.ParseDateUnRus(); if (datePub != DateTime.MinValue) { _tn.DatePub = datePub; } var dateUpd = _tn.DateUpd; 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); GetEtp(connect, out var idEtp); GetPlacingWay(connect, out var idPlacingWay); var status = htmlDoc.DocumentNode .SelectSingleNode( "//td[. = 'Статус']/following-sibling::td") ?.InnerText.Trim() ?? ""; 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", 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); AddAttachments(htmlDoc, connect, idTender); var cusName = htmlDoc.DocumentNode .SelectSingleNode( "//td[. = 'Заказчик']/following-sibling::td/a[starts-with(@href, '/Organization')]") ?.InnerText.Trim() ?? ""; var cusInn = htmlDoc.DocumentNode .SelectSingleNode( "//td[. = 'ИНН/КПП']/following-sibling::td") ?.InnerText.Trim().GetDataFromRegex(@"^(\d+)/") ?? ""; 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, 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", cusName); cmd14.Parameters.AddWithValue("@inn", cusInn); cmd14.ExecuteNonQuery(); customerId = (int)cmd14.LastInsertedId; } } var nmck = htmlDoc.DocumentNode .SelectSingleNode( "//td[contains(., 'Сумма договора')]/following-sibling::td/span") ?.InnerText.Trim().ReplaceHtmlEntyty().ExtractPriceNew() ?? ""; var currency = "руб."; 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", currency); cmd18.Parameters.AddWithValue("@finance_source", ""); cmd18.ExecuteNonQuery(); var idLot = (int)cmd18.LastInsertedId; var poList = htmlDoc.DocumentNode.SelectNodes( "//div[@id = 'ProductRequestGrid']//tbody/tr") ?? new HtmlNodeCollection(null); if (poList.Count != 0) { poList.RemoveAt(0); foreach (var pp in poList) { var namePo = (pp.SelectSingleNode(".//td[1]/a") ?.InnerText ?? "").Trim(); var okeiP = ""; var quantityP = (pp.SelectSingleNode(".//td[4]") ?.InnerText ?? "").Trim(); quantityP = quantityP.Replace(" ", "").Replace(",", "."); quantityP = Regex.Replace(quantityP, @"\s+", ""); var okpd2 = (pp.SelectSingleNode(".//td[2]") ?.InnerText ?? "").Trim(); var price = (pp.SelectSingleNode(".//td[3]") ?.InnerText ?? "").Trim().ReplaceHtmlEntyty().ExtractPriceNew(); var sum = (pp.SelectSingleNode(".//td[5]") ?.InnerText ?? "").Trim().ReplaceHtmlEntyty().ExtractPriceNew(); 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, okpd2_code = @okpd2_code, sum = @sum, 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", namePo); cmd19.Parameters.AddWithValue("@quantity_value", quantityP); cmd19.Parameters.AddWithValue("@okei", okeiP); cmd19.Parameters.AddWithValue("@customer_quantity_value", quantityP); cmd19.Parameters.AddWithValue("@sum", sum); cmd19.Parameters.AddWithValue("@okpd2_code", okpd2); cmd19.Parameters.AddWithValue("@price", price); cmd19.ExecuteNonQuery(); } } else { 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", nmck); cmd20.ExecuteNonQuery(); } var id = _tn.Href.GetDataFromRegex(@"Index/(\d+)"); var delivPlace = DownloadString.DownL( $"https://zakupki.lenreg.ru/ProductRequestGroup/GetDeliveryAddress?idGroup={id}", tryCount: 5) .Trim('"'); var delivTerm1 = navigator.SelectSingleNode( "//td[. = 'Плановая дата заключения договора']/following-sibling::td") ?.Value?.Trim().DelDoubleWhitespace() ?? ""; var delivTerm2 = navigator.SelectSingleNode( "//td[. = 'Срок выполнения работ, оказания услуг, поставки товаров']/following-sibling::td") ?.Value?.Trim().DelDoubleWhitespace() ?? ""; var delivTerm = ""; if (!string.IsNullOrEmpty(delivTerm1)) { delivTerm = $"{delivTerm}Плановая дата заключения договора: {delivTerm1}"; } if (!string.IsNullOrEmpty(delivTerm2)) { delivTerm = $"{delivTerm}\nСрок выполнения работ, оказания услуг, поставки товаров: {delivTerm2}"; } if (!string.IsNullOrEmpty(delivPlace) || !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", delivPlace.Trim()); cmd16.Parameters.AddWithValue("@max_price", nmck); cmd16.Parameters.AddWithValue("@delivery_term", delivTerm.ReplaceHtmlEntyty().Trim()); 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 type_fz = @type_fz 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("@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; } Thread.Sleep(3000); var s = DownloadString.DownL(_tn.Href, tryCount: 5); 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; organiserId = AddOrganizer(orgName, connect, organiserId); GetEtp(connect, out var idEtp); 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); AddAttachments(htmlDoc, connect, idTender); 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", _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, 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(); var delivPlace = htmlDoc.DocumentNode .SelectSingleNode( "//p[contains(., 'Место поставки товара/выполнения работ/оказания услуг:')]") ?.InnerText.Replace("Место поставки товара/выполнения работ/оказания услуг:", "").Trim(); if (!string.IsNullOrEmpty(delivPlace) || !string.IsNullOrEmpty(_tn.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", delivPlace); cmd16.Parameters.AddWithValue("@max_price", _tn.Nmck); cmd16.Parameters.AddWithValue("@delivery_term", _tn.DelivTerm); 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 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; } Thread.Sleep(4000); 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 dateUpd = DateTime.Now; var(updated, cancelStatus) = UpdateTenderVersion(connect, _tn.PurNum, dateUpd); var printForm = _tn.Href; var customerId = 0; var 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(); if (purObjInfo == "") { purObjInfo = (document.QuerySelector("span:contains('Наименование продукции:') + span")?.TextContent ?? "") .Trim(); } if (purObjInfo == "") { purObjInfo = _tn.PurName; } 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", 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", _tn.DateEnd); cmd9.Parameters.AddWithValue("@scoring_date", _tn.Scoring); 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 docs = document.QuerySelectorAll( "a[href^='/document.php?']"); 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, purObjInfo); TenderKwords(connect, idTender); AddVerNumber(connect, _tn.PurNum, TypeFz); } }