public override List <string> GetListArchPrev(string pathParse, string regionPath) { var arch = new List <string>(); var archtemp = new List <string>(); /*FtpClient ftp = ClientFtp44();*/ try { var ftp = ClientFtp44_old(); ftp.ChangeWorkingDirectory(pathParse); archtemp = ftp.ListDirectory(); } catch (Exception) { Log.Logger("Не могу найти директорию", pathParse); } var serachd = $"{Program.LocalDate:yyyyMMdd}"; //var serachd = $"{Program.LocalDate:yyyy}"; //TODO change it foreach (var a in archtemp.Where(a => a.IndexOf(serachd, StringComparison.Ordinal) != -1)) { var prevA = $"prev_{a}"; using (var connect = ConnectToDb.GetDbConnection()) { connect.Open(); var selectArch = $"SELECT id FROM {Program.Prefix}archiv_plan_graphs WHERE arhiv = @archive"; var cmd = new MySqlCommand(selectArch, connect); cmd.Prepare(); cmd.Parameters.AddWithValue("@archive", prevA); var reader = cmd.ExecuteReader(); var resRead = reader.HasRows; reader.Close(); if (resRead) { continue; } var addArch = $"INSERT INTO {Program.Prefix}archiv_plan_graphs SET arhiv = @archive"; var cmd1 = new MySqlCommand(addArch, connect); cmd1.Prepare(); cmd1.Parameters.AddWithValue("@archive", prevA); cmd1.ExecuteNonQuery(); arch.Add(a); } } return(arch); }
public DataTable GetRegions() { var reg = "SELECT * FROM region"; DataTable dt; using (var connect = ConnectToDb.GetDbConnection()) { connect.Open(); var adapter = new MySqlDataAdapter(reg, connect); var ds = new DataSet(); adapter.Fill(ds); dt = ds.Tables[0]; } return(dt); }
public override List <string> GetListArchCurr(string pathParse, string regionPath) { var arch = new List <string>(); var archtemp = new List <string>(); /*FtpClient ftp = ClientFtp44();*/ try { var ftp = ClientFtp44_old(); ftp.ChangeWorkingDirectory(pathParse); archtemp = ftp.ListDirectory(); } catch (Exception) { Log.Logger("Не могу найти директорию", pathParse); } foreach (var a in archtemp) { using (var connect = ConnectToDb.GetDbConnection()) { connect.Open(); var selectArch = $"SELECT id FROM {Program.Prefix}archiv_plan_graphs WHERE arhiv = @archive"; var cmd = new MySqlCommand(selectArch, connect); cmd.Prepare(); cmd.Parameters.AddWithValue("@archive", a); var reader = cmd.ExecuteReader(); var resRead = reader.HasRows; reader.Close(); if (resRead) { continue; } var addArch = $"INSERT INTO {Program.Prefix}archiv_plan_graphs SET arhiv = @archive"; var cmd1 = new MySqlCommand(addArch, connect); cmd1.Prepare(); cmd1.Parameters.AddWithValue("@archive", a); cmd1.ExecuteNonQuery(); arch.Add(a); } } return(arch); }
public override void Parsing() { var update = false; var xml = GetXml(File.ToString()); var root = (JObject)P.SelectToken("export"); var firstOrDefault = root.Properties().FirstOrDefault(pr => pr.Name.Contains("tender")); if (firstOrDefault != null) { var plan = firstOrDefault.Value; var idXml = ((string)plan.SelectToken("id") ?? "").Trim(); if (string.IsNullOrEmpty(idXml)) { Log.Logger("У плана нет id", FilePath); return; } var planNumber = ((string)plan.SelectToken("planNumber") ?? "").Trim(); var versionNumber = (int?)plan.SelectToken("versionNumber") ?? 0; if (string.IsNullOrEmpty(planNumber)) { Log.Logger("У плана нет planNumber", FilePath); return; } using (var connect = ConnectToDb.GetDbConnection()) { connect.Open(); var selectPlan = $"SELECT id FROM {Program.Prefix}tender_plan WHERE id_xml = @id_xml AND id_region = @id_region AND plan_number = @plan_number AND num_version = @num_version"; var cmd = new MySqlCommand(selectPlan, connect); cmd.Prepare(); cmd.Parameters.AddWithValue("@id_xml", idXml); cmd.Parameters.AddWithValue("@id_region", RegionId); cmd.Parameters.AddWithValue("@plan_number", planNumber); cmd.Parameters.AddWithValue("@num_version", versionNumber); var reader = cmd.ExecuteReader(); if (reader.HasRows) { reader.Close(); //Log.Logger("Такой план уже есть в базе", FilePath, idXml, planNumber); return; } reader.Close(); var maxVerNumber = $"SELECT IFNULL(MAX(num_version), 0) FROM {Program.Prefix}tender_plan WHERE id_region = @id_region AND plan_number = @plan_number"; var cmd0 = new MySqlCommand(maxVerNumber, connect); cmd0.Prepare(); cmd0.Parameters.AddWithValue("@id_region", RegionId); cmd0.Parameters.AddWithValue("@plan_number", planNumber); int max; switch (cmd0.ExecuteScalar()) { case int i: max = i; break; case long l: max = (int)l; break; default: throw new ArgumentException("type object not int and not long", nameof(max)); } if (versionNumber >= max) { if (max != 0) { update = true; } var delAll = $"DELETE tp, ta, tpos, tpr, t_prod, ts, tsp FROM {Program.Prefix}tender_plan AS tp LEFT JOIN {Program.Prefix}tender_plan_attach AS ta ON tp.id = ta.id_plan LEFT JOIN {Program.Prefix}tender_plan_position AS tpos ON tp.id = tpos.id_plan LEFT JOIN {Program.Prefix}tender_plan_pref_rec AS tpr ON tpos.id = tpr.id_plan_prod LEFT JOIN {Program.Prefix}tender_plan_products AS t_prod ON t_prod.id_tender_plan_position = tpos.id LEFT JOIN {Program.Prefix}tender_plan_special_purchases AS tsp ON tsp.id_plan = tp.id LEFT JOIN {Program.Prefix}tender_plan_special_purchase AS ts ON ts.id_plan_special_purchases = tsp.id WHERE tp.id_region = @id_region AND tp.plan_number = @plan_number"; var cmd00 = new MySqlCommand(delAll, connect); cmd00.Prepare(); cmd00.Parameters.AddWithValue("@id_region", RegionId); cmd00.Parameters.AddWithValue("@plan_number", planNumber); cmd00.ExecuteNonQuery(); } else { return; } var purchasePlanNumber = ((string)plan.SelectToken("commonInfo.purchasePlanNumber") ?? "").Trim(); var year = ((string)plan.SelectToken("commonInfo.year") ?? "").Trim(); var createDate = (JsonConvert.SerializeObject(plan.SelectToken("commonInfo.createDate") ?? "") ?? "").Trim('"'); var confirmDate = (JsonConvert.SerializeObject(plan.SelectToken("commonInfo.confirmDate") ?? "") ?? "").Trim('"'); var publishDate = (JsonConvert.SerializeObject(plan.SelectToken("commonInfo.publishDate") ?? "") ?? "").Trim('"'); var printform = ((string)plan.SelectToken("printForm.url") ?? "").Trim(); var cancelStatus = 0; if (!string.IsNullOrEmpty(publishDate)) { var selectDateP = $"SELECT id, create_date FROM {Program.Prefix}tender_plan WHERE id_region = @id_region AND plan_number = @plan_number"; var cmd2 = new MySqlCommand(selectDateP, connect); cmd2.Prepare(); cmd2.Parameters.AddWithValue("@id_region", RegionId); cmd2.Parameters.AddWithValue("@plan_number", planNumber); var dt = new DataTable(); var adapter = new MySqlDataAdapter { SelectCommand = cmd2 }; adapter.Fill(dt); if (dt.Rows.Count > 0) { foreach (DataRow row in dt.Rows) { var dateNew = DateTime.Parse(createDate); var dateOld = (DateTime)row["create_date"]; if (dateNew > dateOld) { var updatePlanCancel = $"UPDATE {Program.Prefix}tender_plan SET cancel = 1 WHERE id = @id"; var cmd3 = new MySqlCommand(updatePlanCancel, connect); cmd3.Prepare(); cmd3.Parameters.AddWithValue("id", (int)row["id"]); cmd3.ExecuteNonQuery(); } else { cancelStatus = 1; } } } } var idCustomer = 0; var idOwner = 0; var customerRegNum = ((string)plan.SelectToken("commonInfo.customerInfo.regNum") ?? "").Trim(); if (!string.IsNullOrEmpty(customerRegNum)) { var selectCust = "SELECT id FROM od_customer WHERE regNumber = @regNumber"; var cmd4 = new MySqlCommand(selectCust, connect); cmd4.Prepare(); cmd4.Parameters.AddWithValue("@regNumber", customerRegNum); var reader2 = cmd4.ExecuteReader(); if (reader2.HasRows) { reader2.Read(); idCustomer = reader2.GetInt32("id"); reader2.Close(); } else { reader2.Close(); var cusFullName = ((string)plan.SelectToken("commonInfo.customerInfo.fullName") ?? "") .Trim(); var cusInn = ((string)plan.SelectToken("commonInfo.customerInfo.INN") ?? "").Trim(); var cusKpp = ((string)plan.SelectToken("commonInfo.customerInfo.KPP") ?? "").Trim(); var cusPhone = ((string)plan.SelectToken("commonInfo.customerInfo.phone") ?? "").Trim(); var cusEmail = ((string)plan.SelectToken("commonInfo.customerInfo.email") ?? "").Trim(); var cusLastName = ((string)plan.SelectToken("commonInfo.responsibleContactInfo.lastName") ?? "").Trim(); var cusFirstName = ((string)plan.SelectToken("commonInfo.responsibleContactInfo.firstName") ?? "").Trim(); var cusMiddleName = ((string)plan.SelectToken("commonInfo.responsibleContactInfo.middleName") ?? "") .Trim(); var cusContactName = $"{cusLastName} {cusFirstName} {cusMiddleName}".Trim(); var insertCustomer = "INSERT INTO od_customer SET regNumber = @regNumber, inn = @inn, kpp = @kpp, full_name = @full_name, phone = @phone, email = @email, contact_name = @contact_name"; var cmd5 = new MySqlCommand(insertCustomer, connect); cmd5.Prepare(); cmd5.Parameters.AddWithValue("@regNumber", customerRegNum); cmd5.Parameters.AddWithValue("@inn", cusInn); cmd5.Parameters.AddWithValue("@kpp", cusKpp); cmd5.Parameters.AddWithValue("@full_name", cusFullName); cmd5.Parameters.AddWithValue("@phone", cusPhone); cmd5.Parameters.AddWithValue("@email", cusEmail); cmd5.Parameters.AddWithValue("@contact_name", cusContactName); cmd5.ExecuteNonQuery(); idCustomer = (int)cmd5.LastInsertedId; } } var ownerRegNum = ((string)plan.SelectToken("commonInfo.ownerInfo.regNum") ?? "").Trim(); if (!string.IsNullOrEmpty(ownerRegNum)) { var selectOwner = "SELECT id FROM od_customer WHERE regNumber = @regNumber"; var cmd6 = new MySqlCommand(selectOwner, connect); cmd6.Prepare(); cmd6.Parameters.AddWithValue("@regNumber", ownerRegNum); var reader3 = cmd6.ExecuteReader(); if (reader3.HasRows) { reader3.Read(); idOwner = reader3.GetInt32("id"); reader3.Close(); } else { reader3.Close(); var ownerFullName = ((string)plan.SelectToken("commonInfo.ownerInfo.fullName") ?? "") .Trim(); var ownerInn = ((string)plan.SelectToken("commonInfo.ownerInfo.INN") ?? "") .Trim(); var ownerKpp = ((string)plan.SelectToken("commonInfo.ownerInfo.KPP") ?? "") .Trim(); var ownerPhone = ((string)plan.SelectToken("commonInfo.ownerInfo.phone") ?? "") .Trim(); var ownerEmail = ((string)plan.SelectToken("commonInfo.ownerInfo.email") ?? "") .Trim(); var ownerFirstName = ((string)plan.SelectToken("commonInfo.confirmContactInfo.firstName") ?? "") .Trim(); var ownerLastName = ((string)plan.SelectToken("commonInfo.confirmContactInfo.lastName") ?? "") .Trim(); var ownerMiddleName = ((string)plan.SelectToken("commonInfo.confirmContactInfo.middleName") ?? "").Trim(); var ownerContactName = $"{ownerLastName} {ownerFirstName} {ownerMiddleName}".Trim(); var insertOwner = "INSERT INTO od_customer SET regNumber = @regNumber, inn = @inn, kpp = @kpp, full_name = @full_name, phone = @phone, email = @email, contact_name = @contact_name"; var cmd7 = new MySqlCommand(insertOwner, connect); cmd7.Prepare(); cmd7.Parameters.AddWithValue("@regNumber", customerRegNum); cmd7.Parameters.AddWithValue("@inn", ownerInn); cmd7.Parameters.AddWithValue("@kpp", ownerKpp); cmd7.Parameters.AddWithValue("@full_name", ownerFullName); cmd7.Parameters.AddWithValue("@phone", ownerPhone); cmd7.Parameters.AddWithValue("@email", ownerEmail); cmd7.Parameters.AddWithValue("@contact_name", ownerContactName); cmd7.ExecuteNonQuery(); idOwner = (int)cmd7.LastInsertedId; } } var sumPushasesSmallBusinessTotal = ((string)plan.SelectToken("totals.outcomeIndicators.sumPushasesSmallBusiness.total") ?? "") .Trim(); var sumPushasesSmallBusinessCurrentYear = ((string)plan.SelectToken("totals.outcomeIndicators.sumPushasesSmallBusiness.currentYear") ?? "") .Trim(); var sumPushasesRequestTotal = ((string)plan.SelectToken("totals.outcomeIndicators.sumPushasesRequest.total") ?? "") .Trim(); var sumPushasesRequestCurrentYear = ((string)plan.SelectToken("totals.outcomeIndicators.sumPushasesRequest.currentYear") ?? "") .Trim(); var financeSupportTotal = ((string)plan.SelectToken("totals.financeSupport.financeSupportTotal.total") ?? "") .Trim(); var financeSupportCurrentYear = ((string)plan.SelectToken("totals.financeSupport.financeSupportTotal.currentYear") ?? "") .Trim(); var insertPlan = $"INSERT INTO {Program.Prefix}tender_plan SET id_xml = @id_xml, plan_number = @plan_number, num_version = @num_version, id_region = @id_region, purchase_plan_number = @purchase_plan_number, year = @year, create_date = @create_date, confirm_date = @confirm_date, publish_date = @publish_date, id_customer = @id_customer, id_owner = @id_owner, print_form = @print_form, cancel = @cancel, sum_pushases_small_business_total = @sum_pushases_small_business_total, sum_pushases_small_business_current_year = @sum_pushases_small_business_current_year, sum_pushases_request_total = @sum_pushases_request_total, sum_pushases_request_current_year = @sum_pushases_request_current_year, finance_support_total = @finance_support_total, finance_support_current_year = @finance_support_current_year, xml = @xml, plan_specification = @plan_specification"; var cmd8 = new MySqlCommand(insertPlan, connect); cmd8.Prepare(); cmd8.Parameters.AddWithValue("@id_xml", idXml); cmd8.Parameters.AddWithValue("@plan_number", planNumber); cmd8.Parameters.AddWithValue("@num_version", versionNumber); cmd8.Parameters.AddWithValue("@id_region", RegionId); cmd8.Parameters.AddWithValue("@purchase_plan_number", purchasePlanNumber); cmd8.Parameters.AddWithValue("@year", year); cmd8.Parameters.AddWithValue("@create_date", createDate); cmd8.Parameters.AddWithValue("@confirm_date", confirmDate); cmd8.Parameters.AddWithValue("@publish_date", publishDate); cmd8.Parameters.AddWithValue("@id_customer", idCustomer); cmd8.Parameters.AddWithValue("@id_owner", idOwner); cmd8.Parameters.AddWithValue("@print_form", printform); cmd8.Parameters.AddWithValue("@cancel", cancelStatus); cmd8.Parameters.AddWithValue("@sum_pushases_small_business_total", sumPushasesSmallBusinessTotal); cmd8.Parameters.AddWithValue("@sum_pushases_small_business_current_year", sumPushasesSmallBusinessCurrentYear); cmd8.Parameters.AddWithValue("@sum_pushases_request_total", sumPushasesRequestTotal); cmd8.Parameters.AddWithValue("@sum_pushases_request_current_year", sumPushasesRequestCurrentYear); cmd8.Parameters.AddWithValue("@finance_support_total", financeSupportTotal); cmd8.Parameters.AddWithValue("@finance_support_current_year", financeSupportCurrentYear); cmd8.Parameters.AddWithValue("@xml", xml); cmd8.Parameters.AddWithValue("@plan_specification", 2017); var resPlan = cmd8.ExecuteNonQuery(); var idPlan = (int)cmd8.LastInsertedId; if (update) { UpdatePlan44?.Invoke(resPlan); } else { AddPlan44?.Invoke(resPlan); } var positions = GetElements(plan, "positions.position"); foreach (var pos in positions) { var positionNumber = ((string)pos.SelectToken("commonInfo.positionNumber") ?? "") .Trim(); var purchasePlanPositionNumber = ((string)pos.SelectToken("commonInfo.purchasePlanPositionInfo.positionNumber") ?? "") .Trim(); var purchaseObjectName = ((string)pos.SelectToken("commonInfo.positionInfo.purchaseObjectName") ?? "") .Trim(); var startMonth = ((string)pos.SelectToken("commonInfo.positionInfo.placingNotificationTerm.month") ?? "") .Trim(); var endMonth = ((string)pos.SelectToken("commonInfo.positionInfo.endContratProcedureTerm.month") ?? "") .Trim(); var idPlacingWay = 0; var placingWayCode = ((string)pos.SelectToken("commonInfo.placingWayInfo.placingWay.code") ?? "").Trim(); var placingWayName = ((string)pos.SelectToken("commonInfo.placingWayInfo.placingWay.name") ?? "").Trim(); if (!string.IsNullOrEmpty(placingWayCode)) { var selectPlacingWay = $"SELECT id_placing_way FROM {Program.Prefix}tender_plan_placing_way WHERE code = @code"; var cmd9 = new MySqlCommand(selectPlacingWay, connect); cmd9.Prepare(); cmd9.Parameters.AddWithValue("@code", placingWayCode); var reader4 = cmd9.ExecuteReader(); if (reader4.HasRows) { reader4.Read(); idPlacingWay = reader4.GetInt32("id_placing_way"); reader4.Close(); } else { reader4.Close(); var insertPlacingWay = $"INSERT INTO {Program.Prefix}tender_plan_placing_way SET code= @code, name= @name"; var cmd10 = new MySqlCommand(insertPlacingWay, connect); cmd10.Prepare(); cmd10.Parameters.AddWithValue("@code", placingWayCode); cmd10.Parameters.AddWithValue("@name", placingWayName); cmd10.ExecuteNonQuery(); idPlacingWay = (int)cmd10.LastInsertedId; //Log.Logger("Добавлен новый placing_way", file_path, id_placing_way); } } var financeTotal = ((string)pos.SelectToken("commonInfo.financeInfo.planPayments.total") ?? "").Trim(); var financeTotalCurrentYear = ((string)pos.SelectToken("commonInfo.financeInfo.planPayments.currentYear") ?? "").Trim(); var maxPrice = ((string)pos.SelectToken("commonInfo.financeInfo.maxPrice") ?? "").Trim(); var purchaseFinCondition = ((string)pos.SelectToken("purchaseConditions.purchaseFinCondition.amount") ?? "").Trim(); var contractFinCondition = ((string)pos.SelectToken("purchaseConditions.contractFinCondition.amount") ?? "").Trim(); var advanceFinCondition = ((string)pos.SelectToken("purchaseConditions.advanceFinCondition.amount") ?? "").Trim(); var purchaseGraph = ((string)pos.SelectToken("purchaseConditions.purchaseGraph.plannedPeriod") ?? "").Trim(); if (string.IsNullOrEmpty(purchaseGraph)) { purchaseGraph = ((string)pos.SelectToken( "purchaseConditions.purchaseGraph.periodicity.periodicityType") ?? "").Trim(); } if (string.IsNullOrEmpty(purchaseGraph)) { purchaseGraph = ((string)pos.SelectToken( "purchaseConditions.purchaseGraph.periodicity.otherPeriodicityText") ?? "").Trim(); } var bankSupportInfo = ((string)pos.SelectToken("purchaseConditions.bankSupportInfo.bankSupportText") ?? "") .Trim(); var insertPosition = $"INSERT INTO {Program.Prefix}tender_plan_position SET id_plan = @id_plan, position_number = @position_number, purchase_plan_position_number = @purchase_plan_position_number, purchase_object_name = @purchase_object_name, start_month = @start_month, end_month = @end_month, id_placing_way = @id_placing_way, finance_total = @finance_total, finance_total_current_year = @finance_total_current_year, max_price = @max_price, purchase_fin_condition = @purchase_fin_condition, contract_fin_condition = @contract_fin_condition, advance_fin_condition = @advance_fin_condition, purchase_graph = @purchase_graph, bank_support_info = @bank_support_info"; var cmd11 = new MySqlCommand(insertPosition, connect); cmd11.Prepare(); cmd11.Parameters.AddWithValue("@id_plan", idPlan); cmd11.Parameters.AddWithValue("@position_number", positionNumber); cmd11.Parameters.AddWithValue("@purchase_plan_position_number", purchasePlanPositionNumber); cmd11.Parameters.AddWithValue("@purchase_object_name", purchaseObjectName); cmd11.Parameters.AddWithValue("@start_month", startMonth); cmd11.Parameters.AddWithValue("@end_month", endMonth); cmd11.Parameters.AddWithValue("@id_placing_way", idPlacingWay); cmd11.Parameters.AddWithValue("@finance_total", financeTotal); cmd11.Parameters.AddWithValue("@finance_total_current_year", financeTotalCurrentYear); cmd11.Parameters.AddWithValue("@max_price", maxPrice); cmd11.Parameters.AddWithValue("@purchase_fin_condition", purchaseFinCondition); cmd11.Parameters.AddWithValue("@contract_fin_condition", contractFinCondition); cmd11.Parameters.AddWithValue("@advance_fin_condition", advanceFinCondition); cmd11.Parameters.AddWithValue("@purchase_graph", purchaseGraph); cmd11.Parameters.AddWithValue("@bank_support_info", bankSupportInfo); cmd11.ExecuteNonQuery(); var idProd = (int)cmd11.LastInsertedId; var products = GetElements(pos, "KTRUInfo.productsSpecification.product"); var pills = GetElements(pos, "KTRUInfo.drugPurchaseObjectsInfo.drugPurchaseObjectInfo"); var insertProduct = $"INSERT INTO {Program.Prefix}tender_plan_products SET id_tender_plan_position = @id_tender_plan_position, OKPD2_code = @OKPD2_code, OKPD2_name = @OKPD2_name, OKEI_code = @OKEI_code, OKEI_name = @OKEI_name, prod_description = @prod_description, products_quantity_total = @products_quantity_total, products_quantity_current_year = @products_quantity_current_year, product_sum_total = @product_sum_total, product_sum_current_year = @product_sum_current_year"; if (products.Count == 0 && pills.Count == 0) { var okpd2Code = ((string)pos.SelectToken("purchaseObjectInfo.OKPD2Info.OKPD2.code") ?? "").Trim(); var okpd2Name = ((string)pos.SelectToken("purchaseObjectInfo.OKPD2Info.OKPD2.name") ?? "").Trim(); var okeiCode = ((string)pos.SelectToken("purchaseObjectInfo.OKEI.code") ?? "").Trim(); var okeiName = ((string)pos.SelectToken("purchaseObjectInfo.OKEI.name") ?? "").Trim(); var posDescription = ((string)pos.SelectToken("purchaseObjectInfo.objectDescription") ?? "") .Trim(); var productsQuantityTotal = ((string)pos.SelectToken("purchaseObjectInfo.productsQuantityInfo.total") ?? "") .Trim(); var productsQuantityCurrentYear = ((string)pos.SelectToken("purchaseObjectInfo.productsQuantityInfo.currentYear") ?? "") .Trim(); var cmd14 = new MySqlCommand(insertProduct, connect); cmd14.Prepare(); cmd14.Parameters.AddWithValue("@id_tender_plan_position", idProd); cmd14.Parameters.AddWithValue("@OKPD2_code", okpd2Code); cmd14.Parameters.AddWithValue("@OKPD2_name", okpd2Name); cmd14.Parameters.AddWithValue("@OKEI_code", okeiCode); cmd14.Parameters.AddWithValue("@OKEI_name", okeiName); cmd14.Parameters.AddWithValue("@prod_description", posDescription); cmd14.Parameters.AddWithValue("@products_quantity_total", productsQuantityTotal); cmd14.Parameters.AddWithValue("@products_quantity_current_year", productsQuantityCurrentYear); cmd14.Parameters.AddWithValue("@product_sum_total", 0.0m); cmd14.Parameters.AddWithValue("@product_sum_current_year", 0.0m); cmd14.ExecuteNonQuery(); } else if (products.Count != 0) { foreach (var p in products) { var okpd2Code = ((string)p.SelectToken("OKPD2.code") ?? "").Trim(); var okpd2Name = ((string)p.SelectToken("OKPD2.name") ?? "").Trim(); var okeiCode = ((string)p.SelectToken("productOKEI.code") ?? "").Trim(); var okeiName = ((string)p.SelectToken("productOKEI.name") ?? "").Trim(); var posDescription = ((string)p.SelectToken("productName") ?? "") .Trim(); var productQuantityTotal = (decimal?)p.SelectToken("productsQuantityInfo.total") ?? 0.0m; var productQuantityCurrentYear = (decimal?)p.SelectToken("productsQuantityInfo.currentYear") ?? 0.0m; var productSumTotal = (decimal?)p.SelectToken("productsQuantityInfo.total") ?? 0.0m; var productSumCurrentYear = (decimal?)p.SelectToken("productsQuantityInfo.currentYear") ?? 0.0m; var cmd14 = new MySqlCommand(insertProduct, connect); cmd14.Prepare(); cmd14.Parameters.AddWithValue("@id_tender_plan_position", idProd); cmd14.Parameters.AddWithValue("@OKPD2_code", okpd2Code); cmd14.Parameters.AddWithValue("@OKPD2_name", okpd2Name); cmd14.Parameters.AddWithValue("@OKEI_code", okeiCode); cmd14.Parameters.AddWithValue("@OKEI_name", okeiName); cmd14.Parameters.AddWithValue("@prod_description", posDescription); cmd14.Parameters.AddWithValue("@products_quantity_total", productQuantityTotal); cmd14.Parameters.AddWithValue("@products_quantity_current_year", productQuantityCurrentYear); cmd14.Parameters.AddWithValue("@product_sum_total", productSumTotal); cmd14.Parameters.AddWithValue("@product_sum_current_year", productSumCurrentYear); cmd14.ExecuteNonQuery(); } } foreach (var pl in pills) { var drugInfo = pl.SelectToken("objectInfoUsingReferenceInfo"); if (drugInfo is null || drugInfo.Type == JTokenType.Null) { drugInfo = pl.SelectToken("objectInfoUsingTextForm"); } if (drugInfo is null || drugInfo.Type == JTokenType.Null) { continue; } var dInf = GetElements(drugInfo, "drugsInfo.drugInfo"); foreach (var di in dInf) { var posDescription1 = ((string)di.SelectToken("MNNInfo.MNNName") ?? "") .Trim(); var posDescription2 = ((string)di.SelectToken("tradeInfo.positionTradeNam") ?? "") .Trim(); var posDescription3 = ((string)di.SelectToken("medicamentalFormInfo.medicamentalFormName") ?? "") .Trim(); var posDescription4 = ((string)di.SelectToken("dosageInfo.dosageGRLSValue") ?? "") .Trim(); var posDescription5 = ((string)di.SelectToken("packagingInfo.packaging1Quantity") ?? "") .Trim(); var posDescription = $"{posDescription1} {posDescription2} {posDescription3} {posDescription4} {posDescription5}" .Trim(); var productQuantityTotal = (decimal?)di.SelectToken("drugQuantityInfo.total") ?? 0.0m; var productQuantityCurrentYear = (decimal?)di.SelectToken("drugQuantityInfo.currentYear") ?? 0.0m; var productSumTotal = (decimal?)pl.SelectToken("drugSumPaymentsInfo.total") ?? 0.0m; var productSumCurrentYear = (decimal?)pl.SelectToken("drugSumPaymentsInfo.currentYear") ?? 0.0m; var cmd14 = new MySqlCommand(insertProduct, connect); cmd14.Prepare(); cmd14.Parameters.AddWithValue("@id_tender_plan_position", idProd); cmd14.Parameters.AddWithValue("@OKPD2_code", ""); cmd14.Parameters.AddWithValue("@OKPD2_name", ""); cmd14.Parameters.AddWithValue("@OKEI_code", ""); cmd14.Parameters.AddWithValue("@OKEI_name", ""); cmd14.Parameters.AddWithValue("@prod_description", posDescription); cmd14.Parameters.AddWithValue("@products_quantity_total", productQuantityTotal); cmd14.Parameters.AddWithValue("@products_quantity_current_year", productQuantityCurrentYear); cmd14.Parameters.AddWithValue("@product_sum_total", productSumTotal); cmd14.Parameters.AddWithValue("@product_sum_current_year", productSumCurrentYear); cmd14.ExecuteNonQuery(); } } var recPref = GetElements(pos, "purchaseConditions.preferensesRequirements.preferenseRequirement"); foreach (var pr in recPref) { var groupCode = ((string)pr.SelectToken("prefsReqsGroup.code") ?? "").Trim(); var groupName = ((string)pr.SelectToken("prefsReqsGroup.name") ?? "").Trim(); var name = ((string)pr.SelectToken("name") ?? "").Trim(); var addInfo = ((string)pr.SelectToken("addInfo") ?? "").Trim(); var insertPrefRec = $"INSERT INTO {Program.Prefix}tender_plan_pref_rec SET id_plan_prod = @id_plan_prod, group_code = @group_code, group_name = @group_name, name = @name, add_info = @add_info"; var cmd12 = new MySqlCommand(insertPrefRec, connect); cmd12.Prepare(); cmd12.Parameters.AddWithValue("@id_plan_prod", idProd); cmd12.Parameters.AddWithValue("@group_code", groupCode); cmd12.Parameters.AddWithValue("@group_name", groupName); cmd12.Parameters.AddWithValue("@name", name); cmd12.Parameters.AddWithValue("@add_info", addInfo); cmd12.ExecuteNonQuery(); } } var specPositions = GetElements(plan, "specialPurchases.specialPurchase"); foreach (var spec in specPositions) { var typeCode = ((string)spec.SelectToken("type.code") ?? "") .Trim(); var typeName = ((string)spec.SelectToken("type.name") ?? "") .Trim(); var financeTotal = ((string)spec.SelectToken("yearFinanceInfo.total") ?? "").Trim(); var financeTotalCurrentYear = ((string)spec.SelectToken("yearFinanceInfo.currentYear") ?? "").Trim(); var insertSpecPositions = $"INSERT INTO {Program.Prefix}tender_plan_special_purchases SET id_plan = @id_plan, type_code = @type_code, type_name = @type_name, finance_total = @finance_total, finance_total_current_year = @finance_total_current_year"; var cmd12 = new MySqlCommand(insertSpecPositions, connect); cmd12.Prepare(); cmd12.Parameters.AddWithValue("@id_plan", idPlan); cmd12.Parameters.AddWithValue("@type_code", typeCode); cmd12.Parameters.AddWithValue("@type_name", typeName); cmd12.Parameters.AddWithValue("@finance_total", financeTotal); cmd12.Parameters.AddWithValue("@finance_total_current_year", financeTotalCurrentYear); cmd12.ExecuteNonQuery(); var idSpecPos = (int)cmd12.LastInsertedId; var specPos = GetElements(spec, "purchases.purchase"); foreach (var spp in specPos) { var posNum = ((string)spp.SelectToken("positionNumber") ?? "") .Trim(); var ikz = ((string)spp.SelectToken("IKZ") ?? "") .Trim(); var pubYear = ((string)spp.SelectToken("publishYear") ?? "") .Trim(); var purNum = ((string)spp.SelectToken("purchaseNumber") ?? "") .Trim(); var kvrName = ((string)spp.SelectToken("KVRInfo.KVR.name") ?? "") .Trim(); var financeTotalP = ((string)spp.SelectToken("yearFinanceInfo.total") ?? "").Trim(); var financeTotalCurrentYearP = ((string)spp.SelectToken("yearFinanceInfo.currentYear") ?? "").Trim(); var insertSpecPosition = $"INSERT INTO {Program.Prefix}tender_plan_special_purchase SET id_plan_special_purchases = @id_plan_special_purchases, position_number = @position_number, ikz = @ikz, publish_year = @publish_year, purchase_number = @purchase_number, kvr_name = @kvr_name , finance_total_current_year = @finance_total_current_year, finance_total = @finance_total"; var cmd13 = new MySqlCommand(insertSpecPosition, connect); cmd13.Prepare(); cmd13.Parameters.AddWithValue("@id_plan_special_purchases", idSpecPos); cmd13.Parameters.AddWithValue("@position_number", posNum); cmd13.Parameters.AddWithValue("@ikz", ikz); cmd13.Parameters.AddWithValue("@publish_year", pubYear); cmd13.Parameters.AddWithValue("@purchase_number", purNum); cmd13.Parameters.AddWithValue("@kvr_name", kvrName); cmd13.Parameters.AddWithValue("@finance_total_current_year", financeTotalCurrentYearP); cmd13.Parameters.AddWithValue("@finance_total", financeTotalP); cmd13.ExecuteNonQuery(); } } var attach = GetElements(plan, "attachments.attachment"); foreach (var att in attach) { var fileName = ((string)att.SelectToken("fileName") ?? "").Trim(); var desc = ((string)att.SelectToken("docDescription") ?? "").Trim(); var url = ((string)att.SelectToken("url") ?? "").Trim(); var insertAttach = $"INSERT INTO {Program.Prefix}tender_plan_attach SET id_plan = @id_plan, file_name = @file_name, description = @description, url = @url"; var cmd13 = new MySqlCommand(insertAttach, connect); cmd13.Prepare(); cmd13.Parameters.AddWithValue("@id_plan", idPlan); cmd13.Parameters.AddWithValue("@file_name", fileName); cmd13.Parameters.AddWithValue("@description", desc); cmd13.Parameters.AddWithValue("@url", url); cmd13.ExecuteNonQuery(); } } } else { Log.Logger("Не могу найти тег Plan44", FilePath); } }