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);
}
