private void CreateExclude(Exclude exclude, List <DbExclude> excludes, Unrecexp expression)
{
if (excludes.Any(e => e.CatalogId == exclude.CatalogId && e.ProducerSynonym.Equals(exclude.Name, StringComparison.CurrentCultureIgnoreCase)))
{
return;
}
excludes.Add(new DbExclude {
CatalogId = exclude.CatalogId,
DoNotShow = exclude.State == ProducerSynonymState.Unknown,
ProducerSynonym = exclude.Name,
OriginalSynonym = expression.CreatedProductSynonym,
OriginalSynonymId = expression.ProductSynonymId
});
}
public void CalculateChanges(MySqlConnection masterConnection, IProgressNotifier formProgress, List <DataRow> rows)
{
formProgress.Status = "Подготовка таблиц...";
stat.Reset();
//Кол-во удаленных позиций - если оно равно кол-во нераспознанных позиций, то прайс автоматически проводится
int DelCount = 0;
formProgress.ApplyProgress = 1;
//Заполнение таблиц перед вставкой
//Заполнили таблицу нераспознанных наименований для обновления
daUnrecUpdate = new MySqlDataAdapter("select * from farm.UnrecExp where PriceItemId = ?PriceItemId", masterConnection);
var cbUnrecUpdate = new MySqlCommandBuilder(daUnrecUpdate);
daUnrecUpdate.SelectCommand.Parameters.AddWithValue("?PriceItemId", priceItemId);
dtUnrecUpdate = new DataTable();
daUnrecUpdate.Fill(dtUnrecUpdate);
dtUnrecUpdate.Constraints.Add("UnicNameCode", dtUnrecUpdate.Columns["RowID"], true);
//Заполнили таблицу синонимов наименований
daSynonym = new MySqlDataAdapter("select * from farm.Synonym where PriceCode = ?PriceCode limit 0", masterConnection);
daSynonym.SelectCommand.Parameters.AddWithValue("?PriceCode", priceId);
dtSynonym = new DataTable();
daSynonym.Fill(dtSynonym);
dtSynonym.Constraints.Add("UnicNameCode", dtSynonym.Columns["Synonym"], false);
dtSynonym.Columns.Add("ChildPriceCode", typeof(long));
daSynonym.InsertCommand = new MySqlCommand(
@"
replace into farm.synonym (PriceCode, Synonym, Junk, ProductId, SupplierCode) values (?PriceCode, ?Synonym, ?Junk, ?ProductId, ?SupplierCode);
set @LastSynonymID = last_insert_id();
insert into farm.UsedSynonymLogs (SynonymCode, OperatorName) values (@LastSynonymID, ?OperatorName);
insert into logs.synonymlogs (LogTime, OperatorName, OperatorHost, Operation, SynonymCode, PriceCode, Synonym, Junk, ProductId, ChildPriceCode)
values (now(), ?OperatorName, ?OperatorHost, 0, @LastSynonymID, ?PriceCode, ?Synonym, ?Junk, ?ProductId, ?ChildPriceCode);
select @LastSynonymID as SynonymCode;",
masterConnection);
daSynonym.InsertCommand.Parameters.AddWithValue("?OperatorName", operatorName);
daSynonym.InsertCommand.Parameters.AddWithValue("?OperatorHost", Environment.MachineName);
daSynonym.InsertCommand.Parameters.Add("?PriceCode", MySqlDbType.UInt64, 0, "PriceCode");
daSynonym.InsertCommand.Parameters.Add("?Synonym", MySqlDbType.VarString, 0, "Synonym");
daSynonym.InsertCommand.Parameters.Add("?Junk", MySqlDbType.Byte, 0, "Junk");
daSynonym.InsertCommand.Parameters.Add("?ProductId", MySqlDbType.UInt64, 0, "ProductId");
daSynonym.InsertCommand.Parameters.Add("?SupplierCode", MySqlDbType.VarString, 0, "SupplierCode");
daSynonym.InsertCommand.Parameters.Add("?ChildPriceCode", MySqlDbType.Int64, 0, "ChildPriceCode");
formProgress.ApplyProgress += 1;
//Заполнили таблицу синонимов производителей
daSynonymFirmCr =
new MySqlDataAdapter(
"select sfc.* from farm.SynonymFirmCr sfc, farm.AutomaticProducerSynonyms aps where sfc.PriceCode = ?PriceCode and aps.ProducerSynonymId = sfc.SynonymFirmCrCode",
masterConnection);
daSynonymFirmCr.SelectCommand.Parameters.AddWithValue("?PriceCode", priceId);
dtSynonymFirmCr = new DataTable();
daSynonymFirmCr.Fill(dtSynonymFirmCr);
dtSynonymFirmCr.Constraints.Add("UnicNameCode",
new[] { dtSynonymFirmCr.Columns["Synonym"], dtSynonymFirmCr.Columns["CodeFirmCr"] }, false);
dtSynonymFirmCr.Columns.Add("ChildPriceCode", typeof(long));
dtSynonymFirmCr.Columns.Add("Processed", typeof(bool));
daSynonymFirmCr.InsertCommand = new MySqlCommand(
@"
select farm.CreateProducerSynonym(?PriceCode, ?CodeFirmCr, ?Synonym, false);
",
masterConnection);
daSynonymFirmCr.InsertCommand.Parameters.AddWithValue("?OperatorName", operatorName);
daSynonymFirmCr.InsertCommand.Parameters.AddWithValue("?OperatorHost", Environment.MachineName);
daSynonymFirmCr.InsertCommand.Parameters.Add("?PriceCode", MySqlDbType.UInt64, 0, "PriceCode");
daSynonymFirmCr.InsertCommand.Parameters.Add("?Synonym", MySqlDbType.VarString, 0, "Synonym");
daSynonymFirmCr.InsertCommand.Parameters.Add("?CodeFirmCr", MySqlDbType.UInt64, 0, "CodeFirmCr");
daSynonymFirmCr.InsertCommand.Parameters.Add("?SupplierCode", MySqlDbType.VarString, 0, "SupplierCode");
daSynonymFirmCr.InsertCommand.Parameters.Add("?ChildPriceCode", MySqlDbType.Int64, 0, "ChildPriceCode");
daSynonymFirmCr.UpdateCommand = new MySqlCommand(
@"
update farm.synonymFirmCr set CodeFirmCr = ?CodeFirmCr where SynonymFirmCrCode = ?SynonymFirmCrCode;
delete from farm.AutomaticProducerSynonyms where ProducerSynonymId = ?SynonymFirmCrCode;
",
masterConnection);
daSynonymFirmCr.UpdateCommand.Parameters.AddWithValue("?OperatorName", operatorName);
daSynonymFirmCr.UpdateCommand.Parameters.AddWithValue("?OperatorHost", Environment.MachineName);
daSynonymFirmCr.UpdateCommand.Parameters.Add("?PriceCode", MySqlDbType.UInt64, 0, "PriceCode");
daSynonymFirmCr.UpdateCommand.Parameters.Add("?Synonym", MySqlDbType.VarString, 0, "Synonym");
daSynonymFirmCr.UpdateCommand.Parameters.Add("?CodeFirmCr", MySqlDbType.UInt64, 0, "CodeFirmCr");
daSynonymFirmCr.UpdateCommand.Parameters.Add("?ChildPriceCode", MySqlDbType.Int64, 0, "ChildPriceCode");
daSynonymFirmCr.UpdateCommand.Parameters.Add("?SynonymFirmCrCode", MySqlDbType.Int64, 0, "SynonymFirmCrCode");
formProgress.ApplyProgress += 1;
formProgress.ApplyProgress += 1;
//Заполнили таблицу запрещённых выражений
daForbidden = new MySqlDataAdapter("select * from farm.Forbidden limit 0", masterConnection);
dtForbidden = new DataTable();
daForbidden.Fill(dtForbidden);
dtForbidden.Constraints.Add("UnicNameCode", new[] { dtForbidden.Columns["Forbidden"] }, false);
daForbidden.InsertCommand = new MySqlCommand(
@"
insert into farm.Forbidden (PriceCode, Forbidden) values (?PriceCode, ?Forbidden);
insert into logs.ForbiddenLogs (LogTime, OperatorName, OperatorHost, Operation, ForbiddenRowID, PriceCode, Forbidden)
values (now(), ?OperatorName, ?OperatorHost, 0, last_insert_id(), ?PriceCode, ?Forbidden);",
masterConnection);
daForbidden.InsertCommand.Parameters.AddWithValue("?OperatorName", operatorName);
daForbidden.InsertCommand.Parameters.AddWithValue("?OperatorHost", Environment.MachineName);
daForbidden.InsertCommand.Parameters.Add("?PriceCode", MySqlDbType.UInt64, 0, "PriceCode");
daForbidden.InsertCommand.Parameters.Add("?Forbidden", MySqlDbType.VarString, 0, "Forbidden");
formProgress.ApplyProgress = 10;
var forProducerSynonyms = new List <Unrecexp>();
foreach (var dr in rows)
{
var expression = new Unrecexp(dr);
DelCount += UpDateUnrecExp(dtUnrecUpdate, dr, masterConnection, expression);
//Вставили новую запись в таблицу запрещённых выражений
var name = dr["UEName1"].ToString().Trim();
if (!MarkForbidden(dr, "UEAlready") && MarkForbidden(dr, "UEStatus"))
{
var newDR = dtForbidden.NewRow();
newDR["PriceCode"] = childPriceId;
newDR["Forbidden"] = name;
try {
dtForbidden.Rows.Add(newDR);
stat.ForbiddenCount++;
}
catch (ConstraintException) {
}
}
else if (NotNameForm(dr, "UEAlready") && !NotNameForm(dr, "UEStatus"))
{
//Вставили новую запись в таблицу синонимов наименований
var newDR = dtSynonym.NewRow();
newDR["PriceCode"] = priceId;
newDR["Synonym"] = name;
newDR["ProductId"] = dr["UEPriorProductId"];
newDR["Junk"] = dr["UEJunk"];
newDR["SupplierCode"] = dr["UECode"];
if (priceId != childPriceId)
{
newDR["ChildPriceCode"] = childPriceId;
}
var synonym = dtSynonym.Rows
.Cast <DataRow>()
.FirstOrDefault(r => r["Synonym"].ToString().Equals(name, StringComparison.CurrentCultureIgnoreCase));
if (synonym == null)
{
synonym = newDR;
dtSynonym.Rows.Add(newDR);
stat.SynonymCount++;
}
expression.CreatedProductSynonym = synonym;
}
//если сопоставили по производителю
if (NotFirmForm(dr, "UEAlready") && !NotFirmForm(dr, "UEStatus"))
{
forProducerSynonyms.Add(expression);
}
}
var selectExcludes =
new MySqlCommand(
@"select Id, CatalogId, ProducerSynonym, DoNotShow
from farm.Excludes
where pricecode = ?PriceCode",
masterConnection);
selectExcludes.Parameters.AddWithValue("?PriceCode", priceId);
using (var reader = selectExcludes.ExecuteReader()) {
excludes = reader.Cast <DbDataRecord>().Select(r => new DbExclude {
Id = Convert.ToUInt32(r["Id"]),
CatalogId = Convert.ToUInt32(r["CatalogId"]),
ProducerSynonym = r["ProducerSynonym"].ToString(),
DoNotShow = Convert.ToBoolean(r["DoNotShow"]),
}).ToList();
}
var selectForbiddenProducers = new MySqlCommand(
"select * from farm.forbiddenproducers",
masterConnection);
using (var reader = selectForbiddenProducers.ExecuteReader()) {
ForbiddenProducers = reader.Cast <DbDataRecord>().Select(r => new ForbiddenProducerSynonym {
Id = Convert.ToUInt32(r["Id"]),
Name = r["Name"].ToString()
}).ToList();
}
UpdateProducerSynonym(forProducerSynonyms, excludes, dtSynonymFirmCr, ForbiddenProducers);
}
private int UpDateUnrecExp(DataTable dtUnrecExpUpdate, DataRow drUpdated, MySqlConnection masterConnection, Unrecexp expression)
{
int DelCount = 0;
if (!Convert.IsDBNull(drUpdated["UEPriorProductId"]) &&
CatalogHelper.IsHiddenProduct(masterConnection, Convert.ToInt64(drUpdated["UEPriorProductId"])))
{
//Производим проверку того, что синоним может быть сопоставлен со скрытым каталожным наименованием
//Если в процессе распознования каталожное наименование скрыли, то сбрасываем распознавание
resolver.UnresolveProduct(drUpdated);
stat.HideSynonymCount++;
}
if (Convert.IsDBNull(drUpdated["UEProductSynonymId"]) &&
CatalogHelper.IsSynonymExists(masterConnection, priceId, drUpdated["UEName1"].ToString()))
{
//Производим проверку того, что синоним может быть уже вставлен в таблицу синонимов
//Если в процессе распознования синоним уже кто-то добавил, то сбрасываем распознавание
drUpdated["UEPriorProductId"] = DBNull.Value;
drUpdated["UEStatus"] = (int)((FormMask)Convert.ToByte(drUpdated["UEStatus"]) & (~FormMask.NameForm));
var synonym = dtSynonym.NewRow();
synonym["SynonymCode"] = MySqlHelper.ExecuteScalar(
masterConnection,
"select SynonymCode from farm.synonym where synonym = ?SynonymName and PriceCode = ?LockedSynonymPriceCode",
new MySqlParameter("?LockedSynonymPriceCode", priceId),
new MySqlParameter("?SynonymName", String.Format("{0} ", drUpdated["UEName1"].ToString())));
expression.CreatedProductSynonym = synonym;
stat.DuplicateSynonymCount++;
}
var drNew = dtUnrecExpUpdate.Rows.Find(Convert.ToUInt32(drUpdated["UERowID"]));
if (drNew != null)
{
dtUnrecExpUpdate.Rows.Remove(drNew);
DelCount++;
}
return(DelCount);
}
