private IEnumerable <ProducerSynonym> GetSynonyms(string synonym) { return(With.Connection(c => { var command = new MySqlCommand(@" select sfc.SynonymFirmCrCode, sfc.CodeFirmCr as ProducerId, sfc.Synonym, null as CatalogId from Farm.SynonymFirmCr sfc where sfc.Synonym = ?Synonym and sfc.PriceCode = ?PriceCode and sfc.CodeFirmCr is not null", c); command.Parameters.AddWithValue("?PriceCode", priceId); command.Parameters.AddWithValue("?Synonym", synonym); List <ProducerSynonym> synonyms; using (var reader = command.ExecuteReader()) synonyms = reader.Cast <DbDataRecord>().Select(ProducerSynonym.CreateSynonym).ToList(); command = new MySqlCommand(@" select sfc.SynonymFirmCrCode, sfc.CodeFirmCr as ProducerId, sfc.Synonym, e.CatalogId from Farm.Excludes e join Farm.SynonymFirmCr sfc on sfc.Synonym = e.ProducerSynonym and e.PriceCode = sfc.PriceCode where sfc.Synonym = ?Synonym and sfc.PriceCode = ?PriceCode and sfc.CodeFirmCr is null group by e.CatalogId, sfc.Synonym", c); command.Parameters.AddWithValue("?PriceCode", priceId); command.Parameters.AddWithValue("?Synonym", synonym); using (var reader = command.ExecuteReader()) synonyms = synonyms.Concat(reader.Cast <DbDataRecord>().Select(ProducerSynonym.CreateSynonym)).ToList(); return synonyms; })); }
public void Parse_if_one_waydill_exclude() { var files = new[] { @"..\..\Data\Waybills\h1016416.DBF", @"..\..\Data\Waybills\bi055540.DBF", }; SetUp(files); var sql = string.Format("insert into usersettings.WaybillExcludeFile (Mask, Supplier) value ('*40.DBF', {0});", supplier.Id); session.CreateSQLQuery(sql) .ExecuteUpdate(); Process(); CheckClientDirectory(2, DocType.Waybill); var logs = CheckDocumentLogEntry(2); CheckDocumentEntry(1); Assert.IsTrue(logs.Any(l => l.Addition.Contains($"Разбор документа не производился, применена маска исключения '{"*40.DBF"}'."))); With.Connection(c => { var helper = new MySqlHelper(c); helper.Command("delete from usersettings.WaybillExcludeFile;").Execute(); }); }
public void GetHumanName() { With.Connection(c => { var name = Updater.GetHumanName(c, "test1231423"); Assert.That(name, Is.Empty); }); }
private DataTable FillPrices() { var table = new DataTable(); With.Connection(c => { DbHelper.PricesFill(c, table, "", false, 0); }); return(table); }
// Проверяет, что поставщик может работать и работает с данным клиентом. private bool SupplierAvaliableForClient(ulong supplierId, ulong addressId) { var supplier = new MySqlParameter("?SupplierId", MySqlDbType.Int32); supplier.Value = supplierId; var address = new MySqlParameter("?AddressId", MySqlDbType.Int32); address.Value = addressId; return(With.Connection(c => Convert.ToInt32(MySqlHelper.ExecuteScalar(c, @" SELECT count(i.Id) FROM Customers.Addresses a JOIN Customers.Suppliers as s ON s.Id = ?SupplierId JOIN usersettings.pricesdata as prices ON prices.FirmCode = s.Id JOIN Customers.intersection as i ON i.ClientId = a.ClientId and i.LegalEntityId = a.LegalEntityId and i.PriceId = prices.PriceCode join Customers.AddressIntersection ai on ai.IntersectionId = i.Id and ai.AddressId = a.Id WHERE a.Id = ?AddressId AND i.AgencyEnabled = 1 AND i.AvailableForClient = 1 AND prices.enabled = 1 AND prices.AgencyEnabled = 1 ", supplier, address)) > 0)); }
protected bool MoveWaybill(string archFileName, string fileName, DataRow drCurrent, BaseDocumentReader documentReader) { using (var cleaner = new FileCleaner()) { var supplierId = Convert.ToUInt32(drCurrent[WaybillSourcesTable.colFirmCode]); try { var addresses = With.Connection(c => documentReader.ParseAddressIds(c, supplierId, archFileName, fileName)); var formatFile = documentReader.FormatOutputFile(fileName, drCurrent); cleaner.Watch(fileName); cleaner.Watch(formatFile); foreach (var addressId in addresses) { var log = DocumentReceiveLog.LogNoCommit(supplierId, (uint)addressId, formatFile, _currentDocumentType.DocType, "Получен с нашего FTP"); _logger.InfoFormat("WaybillLanSourceHandler: обработка файла {0}", fileName); documentReader.ImportDocument(log, fileName); new WaybillService().Process(new[] { log }.ToList()); } } catch (Exception e) { var message = "Не удалось отформатировать документ.\nОшибка: " + e; _logger.ErrorFormat("WaybillLanSourceHandler: {0}, archfilename {1}, fileName {2}, error {3}", message, archFileName, fileName, e); DocumentReceiveLog.Log(supplierId, null, fileName, _currentDocumentType.DocType, message); return(false); } } return(true); }
public static void Execute(string commandText) { With.Connection(c => { var command = c.CreateCommand(); command.CommandText = commandText; command.ExecuteNonQuery(); }); }
protected void CheckErrorMessage(TestPriceItem priceItem, string etalonMessage) { var query = String.Format(@"select ShortErrorMessage from `logs`.downlogs where PriceItemId = {0}", priceItem.Id); var message = String.Empty; With.Connection(connection => { message = MySqlHelper.ExecuteScalar(connection, query).ToString(); }); Assert.That(message.Contains(etalonMessage), Is.True); }
public static void Execute(string commandText, params object[] parameters) { With.Connection(c => { var command = c.CreateCommand(); command.CommandText = String.Format(commandText, parameters); command.ExecuteNonQuery(); }); }
protected void CheckDownloadedFile() { var querySelectDownlogId = String.Format(@"select count(RowId) from `logs`.downlogs where PriceItemId = {0}", priceItem.Id); var countDownlogId = 0; With.Connection(connection => { countDownlogId = Convert.ToInt32(MySqlHelper.ExecuteScalar(connection, querySelectDownlogId)); }); Assert.That(countDownlogId, Is.EqualTo(1)); }
public void If_exclude_not_for_pharmacie_ignore_it() { var product = TestCatalogProduct.Queryable.First(c => !c.Pharmacie); var exclude = new DbExclude { CatalogId = product.Id }; With.Connection(c => { Assert.That(Updater.IsExcludeCorrect(c, exclude), Is.False); }); }
public static DataTable Fill(string sql) { return(With.Connection(c => { var adapter = new MySqlDataAdapter(sql, c); var table = new DataTable(); adapter.Fill(table); return table; })); }
public void Get_sources() { With.Connection(c => { var adapter = new MySqlDataAdapter( BaseSourceHandler.GetSourcesCommand("HTTP"), c); var table = new DataTable(); adapter.Fill(table); }); }
private DataTable MatrixItems() { return(With.Connection(c => { var adapter = new MySqlDataAdapter("select * from farm.BuyingMatrix where PriceId = ?Priceid", c); adapter.SelectCommand.Parameters.AddWithValue("?PriceId", price.Id); var table = new DataTable(); adapter.Fill(table); return table; })); }
public static void InsertOrUpdateTable(string queryInsert, string queryUpdate, params MySqlParameter[] parameters) { // Пробуем вставить строку в таблицу try { With.Connection(connection => { MySqlHelper.ExecuteNonQuery(connection, queryInsert, parameters); }); } catch (Exception) { // Если не получилось вставить строку, пробуем обновить ее With.Connection(connection => { MySqlHelper.ExecuteNonQuery(connection, queryUpdate, parameters); }); } }
public static IPriceFormalizer CreateFormalizer(string fileName, DataRow dataRow, Type parserClass) { PriceFormalizationInfo priceInfo = null; SessionHelper.StartSession(s => { var price = s.Load <Price>(Convert.ToUInt32(dataRow[FormRules.colPriceCode])); NHibernateUtil.Initialize(price); priceInfo = new PriceFormalizationInfo(dataRow, price); }); return(With.Connection(c => (IPriceFormalizer)Activator.CreateInstance(parserClass, new object[] { fileName, priceInfo }))); }
public static DataTable LoadFormRules(uint priceItemId) { var query = String.Format(@" select distinct pi.Id as PriceItemId, pi.RowCount, pd.PriceCode, PD.PriceName as SelfPriceName, PD.PriceType, pd.CostType, if(pd.CostType = 1, pc.CostCode, null) CostCode, pc.CostName, s.Id as FirmCode, s.Name as FirmShortName, r.Region, not s.Disabled as FirmStatus, FR.JunkPos as SelfJunkPos, FR.AwaitPos as SelfAwaitPos, FR.VitallyImportantMask as SelfVitallyImportantMask, ifnull(pd.ParentSynonym, pd.PriceCode) as ParentSynonym, PFR.*, pricefmts.FileExtention, pricefmts.ParserClassName, pd.BuyingMatrix, pricefmts.Id as PriceFormat, FR.PriceEncode from (usersettings.PriceItems pi, usersettings.pricescosts pc, UserSettings.PricesData pd, Customers.Suppliers s, Farm.formrules FR, Farm.FormRules PFR, farm.pricefmts) join Farm.Regions r on r.RegionCode = s.HomeRegion where pi.Id = {0} and pc.PriceItemId = pi.Id and pd.PriceCode = pc.PriceCode and ((pd.CostType = 1) or (exists(select * from userSettings.pricesregionaldata prd where prd.PriceCode = pd.PriceCode and prd.BaseCost=pc.CostCode))) and s.Id = pd.FirmCode and FR.Id = pi.FormRuleId and PFR.Id= if(FR.ParentFormRules, FR.ParentFormRules, FR.Id) and pricefmts.ID = PFR.PriceFormatId", priceItemId); var dtFormRules = new DataTable("FromRules"); With.Connection(c => { var daFormRules = new MySqlDataAdapter(query, c); daFormRules.Fill(dtFormRules); }); return(dtFormRules); }
/// <summary> /// Проверяет, существует ли клиент с указанным кодом. /// Также ищет указанный код среди адресов в таблице Customers.Addresses, /// поэтому можно сказать, что также проверяет адрес клиента на существование /// </summary> private bool ClientExists(uint checkClientCode) { var queryGetClientCode = String.Format(@" SELECT Addr.Id FROM Customers.Addresses Addr WHERE Addr.Id = {0}", checkClientCode); return(With.Connection(c => { var clientCode = MySqlHelper.ExecuteScalar(c, queryGetClientCode); return (clientCode != null); })); }
public void UpdateLastCheck() { LastSuccessfulCheck = DateTime.Now; With.Connection(c => { MySqlHelper.ExecuteNonQuery(c, @" update farm.Sources src join usersettings.PriceItems pim on src.Id = pim.SourceId set src.LastSuccessfulCheck = ?LastSuccessfulCheck where pim.Id = ?PriceItemId", new MySqlParameter("?PriceItemId", PriceItemId), new MySqlParameter("?LastSuccessfulCheck", LastSuccessfulCheck)); }); }
public void RetransPriceSmart(uint priceId) { With.Connection(c => { var price = Session.Load <Price>(priceId); if (price.ParentSynonym != null) { priceId = price.ParentSynonym.Value; } var adapter = new MySqlDataAdapter(@" select distinct pc.PriceItemId, pf.FileExtention from usersettings.pricesdata pd, Customers.Suppliers s, usersettings.pricescosts pc, usersettings.priceitems pim, farm.formrules fr, farm.pricefmts pf where (pd.PriceCode = ?priceId or pd.ParentSynonym = ?priceId) and pd.AgencyEnabled = 1 and s.Id = pd.FirmCode and s.Disabled = 0 and pc.PriceCode = pd.PriceCode and (pd.CostType = 1 or exists(select * from userSettings.pricesregionaldata prd where prd.PriceCode = pd.PriceCode and prd.BaseCost=pc.CostCode)) and pim.Id = pc.PriceItemId and exists( select * from Farm.UnrecExp ue where ue.PriceItemId = pim.Id ) and fr.Id = pim.FormRuleId and pf.Id = fr.PriceFormatId", c); adapter.SelectCommand.Parameters.AddWithValue("?priceId", priceId); var data = new DataTable(); adapter.Fill(data); foreach (var row in data.Rows.Cast <DataRow>()) { try { RetransPrice(Convert.ToUInt32(row["PriceItemId"]), Settings.Default.BasePath, true); } catch (FaultException e) { log.Info(String.Format("Ошибка при перепроведении прайс листа, priceItemId = {0}", row["PriceItemId"]), e); } catch (Exception e) { log.Warn(String.Format("Ошибка при перепроведении прайс листа, priceItemId = {0}", row["PriceItemId"]), e); } } }); }
public static List <ulong> GetAddressIds(ulong supplierId, string supplierDeliveryId) { var parametrs = new[] { new MySqlParameter("?SupplierId", supplierId), new MySqlParameter("?SupplierDeliveryId", supplierDeliveryId) }; var sql = SqlGetClientAddressId(false, true); var ds = With.Connection(c => MySqlHelper.ExecuteDataset( c, sql, parametrs)); return(ds.Tables[0].AsEnumerable().Select(r => Convert.ToUInt64(r["AddressId"])).ToList()); }
public DataTable LoadAssortmentByProducer(uint producerId) { return(With.Connection(c => { var command = new MySqlCommand(@" select a.CatalogId, a.ProducerId from Catalogs.Assortment a where a.ProducerId = ?ProducerId", c); command.Parameters.AddWithValue("?producerId", producerId); var adapter = new MySqlDataAdapter(command); var table = new DataTable(); adapter.Fill(table); return table; })); }
public static MailsText GetMailsInfo() { var ds = new DataSet(); With.Connection(c => { var commandHelper = new CommandHelper(new MySqlCommand(@" SELECT ProcessingAboutFirmBody, ProcessingAboutFirmSubject, ProcessingAboutNamesSubject, ProcessingAboutNamesBody FROM usersettings.defaults; ", c)); commandHelper.Fill(ds, "defaults"); }); var data = ds.Tables["defaults"].Rows[0]; return(new MailsText(data["ProcessingAboutFirmBody"].ToString(), data["ProcessingAboutFirmSubject"].ToString(), data["ProcessingAboutNamesBody"].ToString(), data["ProcessingAboutNamesSubject"].ToString())); }
private uint?GetFirmCodeByFromList(AddressList FromList) { foreach (MailboxAddress address in FromList) { var firmCode = With.Connection(c => MySqlHelper.ExecuteScalar( c, String.Format(@" SELECT w.FirmCode FROM documents.waybill_sources w WHERE w.EMailFrom LIKE '%{0}%' AND w.SourceID = 1", address.EmailAddress))); if (firmCode != null) { return(Convert.ToUInt32(firmCode)); } } return(null); }
public void Load(DataTable table) { table.Clear(); var sql = Producers.ToSql() + " union " + Equivalents.ToSql() + " order by CName"; With.Connection(c => { var adapter = new MySqlDataAdapter(sql, c); Producers.BindParameters(adapter.SelectCommand); adapter.Fill(table); }); var drUnknown = table.NewRow(); drUnknown["CCode"] = 0; drUnknown["CName"] = "производитель не известен"; table.Rows.InsertAt(drUnknown, 0); }
public void Email_info_test() { With.Connection(c => { var command = new CommandHelper(new MySqlCommand(string.Format(@" delete from usersettings.defaults; insert into usersettings.defaults (ProcessingAboutFirmBody, ProcessingAboutFirmSubject, ProcessingAboutNamesSubject, ProcessingAboutNamesBody, senderId, formaterId, EmailFooter) value ('testFirmBody','testFirmSubject','testNameSubject','testNameBody', 1, 12, {0}); ", "'С уважением,\r\nАналитическая компания \"Инфорум\" г.Воронеж\r\nМосква +7 499 7097350\r\nС.-Петербург +7 812 3090521\r\nВоронеж +7 473 2606000\r\nЧелябинск +7 351 7501892\r\nОрел +7 4862 632334\r\nСмоленск +7 4812 330364\r\nБрянск +7 4832 300631\r\nКурск +7 4712 745447\r\nКазань +7 843 2495786\r\nE-mail: [email protected]\r\nhttp://www.analit.net'"), c)); command.Execute(); }); var mailParams = MailsText.GetMailsInfo(); Assert.AreEqual(mailParams.ProcessingAboutFirmBody, "testFirmBody"); Assert.AreEqual(mailParams.ProcessingAboutFirmSubject, "testFirmSubject"); Assert.AreEqual(mailParams.ProcessingAboutNamesBody, "testNameBody"); Assert.AreEqual(mailParams.ProcessingAboutNamesSubject, "testNameSubject"); }
public List <uint> Query() { var parametrs = new List <MySqlParameter> { new MySqlParameter("?SupplierId", supplierId), new MySqlParameter("?SupplierDeliveryId", SupplierDeliveryId) }; if (includeClientId) { parametrs.Add(new MySqlParameter("?SupplierClientId", SupplierClientId)); } var sql = SqlGetClientAddressId(includeClientId, true); var ds = With.DeadlockWraper(() => With.Connection(c => MySqlHelper.ExecuteDataset( c, sql, parametrs.ToArray()))); return(ds.Tables[0].AsEnumerable().Select(r => Convert.ToUInt32(r["AddressId"])).ToList()); }
public void FtpChangePassiveModeTest() { source.PricePath = "217.173.73.200"; source.PriceMask = "price.rar"; source.ExtrMask = "price*.dbf"; source.FtpLogin = "******"; source.FtpPassword = "******"; source.FtpDir = "price"; source.Save(); Process(); var sql = String.Format(@"select count(*) from `logs`.downlogs where PriceItemId = {0}", priceItem); var count = 0; With.Connection(connection => count = Convert.ToInt32(MySqlHelper.ExecuteScalar(connection, sql))); var files = Directory.GetFiles(Settings.Default.InboundPath); Assert.That(count, Is.EqualTo(1)); }
public static bool IsAssortmentExists(long productId, long producerId) { object assortmentExists = null; With.Connection((slaveConnection) => { assortmentExists = MySql.Data.MySqlClient.MySqlHelper.ExecuteScalar(slaveConnection, @" select assortment.CatalogId from catalogs.products, catalogs.assortment where (products.Id = ?ProductId) and (assortment.CatalogId = products.CatalogId) and (assortment.ProducerId = ?ProducerId)", new MySqlParameter("?ProductId", productId), new MySqlParameter("?ProducerId", producerId)); }); return(assortmentExists != null); }
private List <DocumentReceiveLog> ProcessWaybill(DocType documentType, WaybillSource source, DownloadedFile downloadedFile) { var documentLogs = new List <DocumentReceiveLog>(); var reader = new SupplierFtpReader(); var addressIds = With.Connection(c => reader.ParseAddressIds(c, source.Id, downloadedFile.FileName, downloadedFile.FileName)); foreach (var addressId in addressIds) { // Если накладная - это архив, разархивируем логируем каждый файл и копируем в папку клиенту var waybillFiles = new[] { downloadedFile.FileName }; try { waybillFiles = FileHelper.TryExtractArchive(downloadedFile.FileName, downloadedFile.FileName + BaseSourceHandler.ExtrDirSuffix) ?? waybillFiles; } catch (ArchiveHelper.ArchiveException e) { _logger.Warn($"Ошибка при извлечении файлов из архива {downloadedFile.FileName}", e); WaybillService.SaveWaybill(downloadedFile.FileName); continue; } foreach (var file in waybillFiles) { var isNew = IsNewWaybill(source, (uint)addressId, Path.GetFileName(file), new FileInfo(file).Length); if (!isNew) { _logger.DebugFormat("Файл {0} не является новой накладной, не обрабатываем его", file); continue; } var log = DocumentReceiveLog.LogNoCommit(source.Id, (uint)addressId, file, documentType, "Получен с клиентского FTP"); _logger.InfoFormat("WaybillFtpSourceHandler: обработка файла {0}", file); documentLogs.Add(log); } } return(documentLogs); }