// Проверяет, что поставщик может работать и работает с данным клиентом.
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 void CheckPriceActual(uint priceId)
{
var supplierName = GetSupplierName(priceId);
var actualPrice = Convert.ToInt32(
MySqlHelper.ExecuteScalar(
Connection,
@"
select
pc.PriceCode
from
usersettings.pricescosts pc,
usersettings.priceitems pim,
farm.formrules fr
where
pc.PriceCode = ?SourcePC
and exists(select * from userSettings.pricesregionaldata prd where prd.PriceCode = pc.PriceCode and prd.BaseCost=pc.CostCode limit 1)
and pim.Id = pc.PriceItemId
and fr.Id = pim.FormRuleId
and (to_days(now())-to_days(pim.PriceDate)) < fr.MaxOld",
new MySqlParameter("?SourcePC", priceId)));
#if !DEBUG
if (actualPrice == 0)
{
throw new ReportException(String.Format("Прайс-лист {0} ({1}) не является актуальным.", supplierName, priceId));
}
#endif
}
protected string GetSupplierName(uint priceId)
{
string customerFirmName;
var drPrice = MySqlHelper.ExecuteDataset(
Connection,
@"
select
concat(suppliers.Name, '(', pricesdata.PriceName, ') - ', regions.Region) as FirmName,
pricesdata.PriceCode,
suppliers.HomeRegion
from
usersettings.pricesdata,
Customers.suppliers,
farm.regions
where
pricesdata.PriceCode = ?PriceCode
and suppliers.Id = pricesdata.FirmCode
and regions.RegionCode = suppliers.HomeRegion
limit 1", new MySqlParameter("?PriceCode", priceId))
.Tables[0].AsEnumerable().FirstOrDefault();
if (drPrice != null)
{
customerFirmName = drPrice["FirmName"].ToString();
}
else
{
throw new ReportException($"Не найден прайс-лист с кодом {priceId}.");
}
return(customerFirmName);
}
public void CopyToInbound(string extrFileName, MySqlConnection connection, MySqlTransaction transaction)
{
var command = new MySqlCommand(@"
update usersettings.PriceItems
set LastDownload = ?FileTime, LocalLastDownload=?LocalLastDownload
where Id = ?Id",
connection, transaction);
command.Parameters.AddWithValue("?Id", PriceItemId);
command.Parameters.AddWithValue("?FileTime", FileTime);
command.Parameters.AddWithValue("?LocalLastDownload", LocalLastDownload);
command.ExecuteNonQuery();
var ignore = Convert.ToBoolean(MySqlHelper.ExecuteScalar(connection, @"
select SilentSkipProcessing from Usersettings.PricesData pd
join Usersettings.PricesCosts pc on pc.PriceCode = pd.PriceCode
where pc.PriceItemId = ?priceItemId", new MySqlParameter("priceItemId", PriceItemId)));
if (ignore)
{
log.WarnFormat("Обработка прайса отключена, игнорирую файл {0}", FilePath);
return;
}
if (File.Exists(FilePath))
{
File.Delete(FilePath);
}
File.Copy(extrFileName, FilePath);
PriceItemList.AddItem(this);
}
private void ApplyNonOptimal(MySqlTransaction trans)
{
MySqlCommand UpdCmd = new MySqlCommand(@"
UPDATE
reports.report_properties
SET
PropertyValue = ?PPropertyValue
WHERE ID = ?PID", MyCn, trans);
UpdCmd.Parameters.Clear();
UpdCmd.Parameters.Add(new MySqlParameter("PID", MySqlDbType.Int64));
UpdCmd.Parameters["PID"].Direction = ParameterDirection.Input;
UpdCmd.Parameters["PID"].SourceColumn = PID.ColumnName;
UpdCmd.Parameters["PID"].SourceVersion = DataRowVersion.Current;
UpdCmd.Parameters.Add(new MySqlParameter("PPropertyValue", MySqlDbType.VarString));
UpdCmd.Parameters["PPropertyValue"].Direction = ParameterDirection.Input;
UpdCmd.Parameters["PPropertyValue"].SourceColumn = PPropertyValue.ColumnName;
UpdCmd.Parameters["PPropertyValue"].SourceVersion = DataRowVersion.Current;
MyDA.UpdateCommand = UpdCmd;
string strHost = HttpContext.Current.Request.UserHostAddress;
string strUser = HttpContext.Current.User.Identity.Name;
if (strUser.StartsWith("ANALIT\\"))
{
strUser = strUser.Substring(7);
}
MySqlHelper.ExecuteNonQuery(trans.Connection, "set @INHost = ?Host; set @INUser = ?User", new MySqlParameter[] { new MySqlParameter("Host", strHost), new MySqlParameter("User", strUser) });
MyDA.Update(DS, DS.Tables[dtNonOptionalParams.TableName].TableName);
}
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); });
}
}
/// <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 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 static PriceProcessItem TryToLoadPriceProcessItem(string filename)
{
if (String.IsNullOrEmpty(filename))
{
return(null);
}
uint priceItemId = ParseId(filename);
var isDownloaded = IsDownloadedPrice(filename);
if (priceItemId == 0)
{
return(null);
}
var drPriceItem = MySqlHelper.ExecuteDataRow(
ConnectionHelper.GetConnectionString(),
@"select distinct
pc.PriceCode as PriceCode,
if(pd.CostType = 1, pc.CostCode, null) CostCode,
pc.PriceItemId,
pd.ParentSynonym,
pi.LastDownload
from (usersettings.pricescosts pc, usersettings.pricesdata pd)
join usersettings.priceitems pi on pc.PriceItemId = pi.Id
where pc.PriceItemId = ?PriceItemId
and ((pd.CostType = 1) or (exists(select * from userSettings.pricesregionaldata prd where prd.PriceCode = pd.PriceCode and prd.BaseCost=pc.CostCode)))
and pd.PriceCode = pc.PriceCode
group by pi.Id",
new MySqlParameter("?PriceItemId", priceItemId));
if (drPriceItem == null)
{
return(null);
}
var priceCode = Convert.ToUInt64(drPriceItem["PriceCode"]);
var costCode = (drPriceItem["CostCode"] is DBNull) ? null : (ulong?)Convert.ToUInt64(drPriceItem["CostCode"]);
var parentSynonym = (drPriceItem["ParentSynonym"] is DBNull) ? null : (ulong?)Convert.ToUInt64(drPriceItem["ParentSynonym"]);
var lastDownload = (drPriceItem["LastDownload"] is DBNull) ? DateTime.MinValue : Convert.ToDateTime(drPriceItem["LastDownload"]);
var item = new PriceProcessItem(isDownloaded, priceCode, costCode, priceItemId, filename, parentSynonym);
if (isDownloaded)
{
item.FileTime = lastDownload;
}
return(item);
}
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 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());
}
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);
}
public override List <ulong> ParseAddressIds(MySqlConnection Connection, ulong FirmCode, string ArchFileName, string CurrentFileName)
{
var list = new List <ulong>();
var SQL = AddressIdQuery.SqlGetClientAddressId(true, true);
string FirmClientCode, DeliveryCode;
try {
string[] parts = Path.GetFileNameWithoutExtension(CurrentFileName).Split('_');
FirmClientCode = parts[0];
DeliveryCode = parts[1];
}
catch (Exception ex) {
throw new Exception("Не получилось сформировать SupplierClientId(FirmClientCode) и SupplierDeliveryId(FirmClientCode2) из документа.", ex);
}
var ds = MySqlHelper.ExecuteDataset(
Connection,
SQL,
new MySqlParameter("?SupplierId", FirmCode),
new MySqlParameter("?SupplierClientId", FirmClientCode),
new MySqlParameter("?SupplierDeliveryId", DeliveryCode));
foreach (DataRow drApteka in ds.Tables[0].Rows)
{
list.Add(Convert.ToUInt64(drApteka["AddressId"]));
}
if (list.Count == 0)
{
throw new Exception("Не удалось найти клиентов с SupplierClientId(FirmClientCode) = " + FirmClientCode +
" и SupplierDeliveryId(FirmClientCode2) = " + DeliveryCode + ".");
}
return(list);
}
protected void btnApply_Click(object sender, EventArgs e)
{
Validate();
if (!IsValid)
{
return;
}
CopyChangesToTable();
MySqlTransaction trans;
MyCn.Open();
trans = MyCn.BeginTransaction(IsolationLevel.ReadCommitted);
try {
MySqlCommand UpdCmd = new MySqlCommand(@"
UPDATE
reports.reports
SET
ReportCaption = ?RReportCaption,
ReportTypeCode = ?RReportTypeCode,
GeneralReportCode = ?RGeneralReportCode,
Enabled = ?REnabled
WHERE ReportCode = ?RReportCode", MyCn, trans);
UpdCmd.Parameters.Clear();
UpdCmd.Parameters.Add(new MySqlParameter("RReportCaption", MySqlDbType.VarString));
UpdCmd.Parameters["RReportCaption"].Direction = ParameterDirection.Input;
UpdCmd.Parameters["RReportCaption"].SourceColumn = RReportCaption.ColumnName;
UpdCmd.Parameters["RReportCaption"].SourceVersion = DataRowVersion.Current;
UpdCmd.Parameters.Add(new MySqlParameter("RReportTypeCode", MySqlDbType.Int64));
UpdCmd.Parameters["RReportTypeCode"].Direction = ParameterDirection.Input;
UpdCmd.Parameters["RReportTypeCode"].SourceColumn = RReportTypeCode.ColumnName;
UpdCmd.Parameters["RReportTypeCode"].SourceVersion = DataRowVersion.Current;
UpdCmd.Parameters.Add(new MySqlParameter("RReportCode", MySqlDbType.Int64));
UpdCmd.Parameters["RReportCode"].Direction = ParameterDirection.Input;
UpdCmd.Parameters["RReportCode"].SourceColumn = RReportCode.ColumnName;
UpdCmd.Parameters["RReportCode"].SourceVersion = DataRowVersion.Current;
UpdCmd.Parameters.Add(new MySqlParameter("REnabled", MySqlDbType.Byte));
UpdCmd.Parameters["REnabled"].Direction = ParameterDirection.Input;
UpdCmd.Parameters["REnabled"].SourceColumn = REnabled.ColumnName;
UpdCmd.Parameters["REnabled"].SourceVersion = DataRowVersion.Current;
UpdCmd.Parameters.Add(new MySqlParameter("RGeneralReportCode", Request["r"]));
MySqlCommand DelCmd = new MySqlCommand(@"
DELETE from reports.reports
WHERE ReportCode = ?RDelReportCode", MyCn, trans);
DelCmd.Parameters.Clear();
DelCmd.Parameters.Add(new MySqlParameter("RDelReportCode", MySqlDbType.Int64));
DelCmd.Parameters["RDelReportCode"].Direction = ParameterDirection.Input;
DelCmd.Parameters["RDelReportCode"].SourceColumn = RReportCode.ColumnName;
DelCmd.Parameters["RDelReportCode"].SourceVersion = DataRowVersion.Original;
MySqlCommand InsCmd = new MySqlCommand(@"
INSERT INTO
reports.reports
SET
ReportCaption = ?RReportCaption,
ReportTypeCode = ?RReportTypeCode,
GeneralReportCode = ?RGeneralReportCode,
Enabled = ?REnabled
", MyCn, trans);
InsCmd.Parameters.Clear();
InsCmd.Parameters.Add(new MySqlParameter("RReportCaption", MySqlDbType.VarString));
InsCmd.Parameters["RReportCaption"].Direction = ParameterDirection.Input;
InsCmd.Parameters["RReportCaption"].SourceColumn = RReportCaption.ColumnName;
InsCmd.Parameters["RReportCaption"].SourceVersion = DataRowVersion.Current;
InsCmd.Parameters.Add(new MySqlParameter("RReportTypeCode", MySqlDbType.Int64));
InsCmd.Parameters["RReportTypeCode"].Direction = ParameterDirection.Input;
InsCmd.Parameters["RReportTypeCode"].SourceColumn = RReportTypeCode.ColumnName;
InsCmd.Parameters["RReportTypeCode"].SourceVersion = DataRowVersion.Current;
InsCmd.Parameters.Add(new MySqlParameter("REnabled", MySqlDbType.Byte));
InsCmd.Parameters["REnabled"].Direction = ParameterDirection.Input;
InsCmd.Parameters["REnabled"].SourceColumn = REnabled.ColumnName;
InsCmd.Parameters["REnabled"].SourceVersion = DataRowVersion.Current;
InsCmd.Parameters.Add(new MySqlParameter("RGeneralReportCode", Request["r"]));
MyDA.UpdateCommand = UpdCmd;
MyDA.DeleteCommand = DelCmd;
MyDA.InsertCommand = InsCmd;
var strHost = HttpContext.Current.Request.UserHostAddress;
var strUser = HttpContext.Current.User.Identity.Name;
if (strUser.StartsWith("ANALIT\\"))
{
strUser = strUser.Substring(7);
}
MySqlHelper.ExecuteNonQuery(trans.Connection, "set @INHost = ?Host; set @INUser = ?User", new MySqlParameter[] { new MySqlParameter("Host", strHost), new MySqlParameter("User", strUser) });
MyDA.Update(DS, DS.Tables[dtReports.TableName].TableName);
trans.Commit();
}
catch {
trans.Rollback();
throw;
}
finally {
MyCn.Close();
}
var report = DbSession.Load <GeneralReport>(Convert.ToUInt64(Request["r"]));
report.EMailSubject = tbEMailSubject.Text;
report.ReportFileName = tbReportFileName.Text;
report.ReportArchName = tbReportArchName.Text;
report.NoArchive = NoArchive.Checked;
report.MailPerFile = MailPerFile.Checked;
report.SendDescriptionFile = SendDescriptionFile.Checked;
report.Format = ReportFormatDD.Text;
DbSession.Save(report);
foreach (GridViewRow dr in fileGridView.Rows)
{
var idField = ((HiddenField)dr.FindControl("Id")).Value;
var property = DbSession.Get <FileSendWithReport>(Convert.ToUInt32(idField));
var file = ((FileUpload)dr.FindControl("UploadFile"));
if (file.HasFile)
{
property.FileName = file.FileName;
File.WriteAllBytes(property.FileNameForSave, file.FileBytes);
DbSession.Save(property);
}
}
PostData();
}
/// <summary>
/// Дублирует все значения свойств из одного отчета в другой (клонирование)
/// </summary>
/// <param name="sourceReportId">код исходного отчета из таблицы reports.reports</param>
/// <param name="destinationReportId">код отчета-приемника из таблицы reports.reports</param>
public static void CopyReportProperties(ulong sourceReportId, ulong destinationReportId)
{
Report _sourceReport = Report.Find(sourceReportId);
Report _destinationReport = Report.Find(destinationReportId);
if (_sourceReport.ReportType != _destinationReport.ReportType)
{
throw new ReportTunerException(
String.Format(
"Тип клонируемого отчета отличается от конечного отчета. Тип исходного отчета: {0}. Тип отчета-приемника: {1}",
_sourceReport.ReportType.ReportTypeName,
_destinationReport.ReportType.ReportTypeName));
}
DataSet dsReportProperties = MySqlHelper.ExecuteDataset(
ConnectionHelper.GetConnectionString(),
@"
SELECT
rp.*,
rtp.PropertyType
FROM
reports.report_properties rp,
reports.report_type_properties rtp
where
rp.ReportCode = ?SourceReportId
and rtp.Id = rp.PropertyId
order by rp.PropertyID;
SELECT
rpv.*
FROM
reports.report_properties rp,
reports.report_property_values rpv
where
rp.ReportCode = ?SourceReportId
and rpv.ReportPropertyId = rp.Id;
SELECT
rp.*,
rtp.PropertyType
FROM
reports.report_properties rp,
reports.report_type_properties rtp
where
rp.ReportCode = ?DestinationReportId
and rtp.Id = rp.PropertyId
order by rp.PropertyID;
",
new MySqlParameter("?SourceReportId", sourceReportId),
new MySqlParameter("?DestinationReportId", destinationReportId));
DataTable dtSourceProperties = dsReportProperties.Tables[0];
DataTable dtSourcePropertiesValues = dsReportProperties.Tables[1];
DataTable dtDestinationProperties = dsReportProperties.Tables[2];
StringBuilder sbCommand = new StringBuilder();
foreach (DataRow drSourceProperty in dtSourceProperties.Rows)
{
DataRow[] drDestinationProperties = dtDestinationProperties.Select("PropertyId = " + drSourceProperty["PropertyId"]);
if (drDestinationProperties.Length == 0)
{
//Свойство не существует, поэтому просто вставляем новое
sbCommand.AppendFormat("insert into reports.report_properties (ReportCode, PropertyId, PropertyValue) values ({0}, {1}, '{2}');\r\n",
destinationReportId, drSourceProperty["PropertyId"], drSourceProperty["PropertyValue"]);
if (drSourceProperty["PropertyType"].ToString().Equals("LIST", StringComparison.OrdinalIgnoreCase))
{
sbCommand.AppendLine("set @LastReportPropertyId = last_insert_id();");
foreach (DataRow drSourcePropertiesValue in dtSourcePropertiesValues.Select("ReportPropertyId = " + drSourceProperty["Id"]))
{
sbCommand.AppendFormat("insert into reports.report_property_values (ReportPropertyId, Value) values (@LastReportPropertyId, '{0}');\r\n",
drSourcePropertiesValue["Value"]);
}
}
}
else
{
//Свойство существует, поэтому обновляем запись
sbCommand.AppendFormat("update reports.report_properties set PropertyValue = '{0}' where Id = {1};\r\n",
drSourceProperty["PropertyValue"], drDestinationProperties[0]["Id"]);
if (drSourceProperty["PropertyType"].ToString().Equals("LIST", StringComparison.OrdinalIgnoreCase))
{
sbCommand.AppendFormat("delete from reports.report_property_values where ReportPropertyId = {0};\r\n", drDestinationProperties[0]["Id"]);
foreach (DataRow drSourcePropertiesValue in dtSourcePropertiesValues.Select("ReportPropertyId = " + drSourceProperty["Id"]))
{
sbCommand.AppendFormat("insert into reports.report_property_values (ReportPropertyId, Value) values ({0}, '{1}');\r\n",
drDestinationProperties[0]["Id"], drSourcePropertiesValue["Value"]);
}
}
}
}
MySqlConnection connection = new MySqlConnection(ConnectionHelper.GetConnectionString());
connection.Open();
try {
MySqlTransaction transaction = connection.BeginTransaction();
MySqlHelper.ExecuteNonQuery(connection, sbCommand.ToString());
transaction.Commit();
}
finally {
connection.Close();
}
}
public static DataSet ExecuteDataset(MySqlConnection connection, string commandText)
{
return(MySqlHelper.ExecuteDataset(connection, commandText, null));
}
public static object ExecuteScalar(MySqlConnection connection, string commandText)
{
return(MySqlHelper.ExecuteScalar(connection, commandText, null));
}
public static MySqlDataReader ExecuteReader(string connectionString, string commandText)
{
return(MySqlHelper.ExecuteReader(connectionString, commandText, null));
}
protected void GetSourceCodes()
{
var enabledPrice = Convert.ToInt32(
MySqlHelper.ExecuteScalar(
DataAdapter.SelectCommand.Connection,
"select PriceCode from ActivePrices where PriceCode = ?SourcePC and RegionCode = ?SourceRegionCode",
new MySqlParameter("?SourcePC", _sourcePriceCode),
new MySqlParameter("?SourceRegionCode", _sourceRegionCode)));
if (enabledPrice == 0 && _byBaseCosts)
{
enabledPrice = Convert.ToInt32(
MySqlHelper.ExecuteScalar(
DataAdapter.SelectCommand.Connection,
"select PriceCode from ActivePrices where PriceCode = ?SourcePC limit 1;",
new MySqlParameter("?SourcePC", _sourcePriceCode)));
if (enabledPrice != 0)
{
_sourceRegionCode = Convert.ToUInt64(
MySqlHelper.ExecuteScalar(
DataAdapter.SelectCommand.Connection,
"select RegionCode from ActivePrices where PriceCode = ?SourcePC limit 1;",
new MySqlParameter("?SourcePC", _sourcePriceCode)));
}
}
//Добавляем к таблице Core поле CatalogCode и заполняем его
DataAdapter.SelectCommand.CommandText = "alter table Core add column CatalogCode int unsigned, add key CatalogCode(CatalogCode);";
DataAdapter.SelectCommand.Parameters.Clear();
DataAdapter.SelectCommand.ExecuteNonQuery();
if (_calculateByCatalog)
{
DataAdapter.SelectCommand.CommandText = "update Core, catalogs.products set Core.CatalogCode = products.CatalogId where products.Id = Core.ProductId;";
}
else
{
DataAdapter.SelectCommand.CommandText = "update Core set CatalogCode = ProductId;";
}
DataAdapter.SelectCommand.ExecuteNonQuery();
DataAdapter.SelectCommand.CommandText = @"
drop temporary table IF EXISTS TmpSourceCodes;
CREATE temporary table TmpSourceCodes(
ID int(32) unsigned,
PriceCode int(32) unsigned,
RegionCode int(32) unsigned,
Code char(20),
BaseCost decimal(8,2) unsigned,
CatalogCode int(32) unsigned,
CodeFirmCr int(32) unsigned,
SynonymCode int(32) unsigned,
SynonymFirmCrCode int(32) unsigned,
key ID(ID),
key CatalogCode(CatalogCode),
key CodeFirmCr(CodeFirmCr),
key SynonymFirmCrCode(SynonymFirmCrCode),
key SynonymCode(SynonymCode))engine=MEMORY PACK_KEYS = 0;";
if (enabledPrice == 0)
{
//Если прайс-лист не включен клиентом или прайс-лист ассортиментный, то добавляем его в таблицу источников TmpSourceCodes, но с ценами NULL
DataAdapter.SelectCommand.CommandText += @"
INSERT INTO TmpSourceCodes
Select
FarmCore.ID,
FarmCore.PriceCode,
?SourceRegionCode as RegionCode,
FarmCore.Code,
NULL,";
if (_calculateByCatalog)
{
DataAdapter.SelectCommand.CommandText += "Products.CatalogId, ";
}
else
{
DataAdapter.SelectCommand.CommandText += "Products.Id, ";
}
DataAdapter.SelectCommand.CommandText += @"
FarmCore.CodeFirmCr,
FarmCore.SynonymCode,
FarmCore.SynonymFirmCrCode
FROM
(
farm.core0 FarmCore,
catalogs.products
)
left join farm.corecosts cc on cc.Core_Id = FarmCore.id and cc.PC_CostCode = FarmCore.PriceCode
WHERE
FarmCore.PriceCode = ?SourcePC
and products.id = FarmCore.ProductId;";
}
else
{
DataAdapter.SelectCommand.CommandText += @"
INSERT INTO TmpSourceCodes
Select
Core.ID,
Core.PriceCode,
Core.RegionCode,
FarmCore.Code,
Core.Cost,";
if (_calculateByCatalog)
{
DataAdapter.SelectCommand.CommandText += "Products.CatalogId, ";
}
else
{
DataAdapter.SelectCommand.CommandText += "Products.Id, ";
}
DataAdapter.SelectCommand.CommandText += @"
FarmCore.CodeFirmCr,
FarmCore.SynonymCode,
FarmCore.SynonymFirmCrCode
FROM
Core,
farm.core0 FarmCore,
catalogs.products
WHERE
Core.PriceCode = ?SourcePC
and FarmCore.id = Core.Id
and products.id = Core.ProductId
and Core.RegionCode = ?SourceRegionCode;";
}
DataAdapter.SelectCommand.Parameters.Clear();
DataAdapter.SelectCommand.Parameters.AddWithValue("?SourcePC", _sourcePriceCode);
DataAdapter.SelectCommand.Parameters.AddWithValue("?SourceRegionCode", _sourceRegionCode);
DataAdapter.SelectCommand.ExecuteNonQuery();
DataAdapter.SelectCommand.CommandText = @"
select
Core.Id,
Core.CatalogCode,
FarmCore.CodeFirmCr,
Core.Cost,
Core.PriceCode,
Core.RegionCode,
FarmCore.Quantity
from
Core,
farm.core0 FarmCore
where
FarmCore.Id = core.id";
//todo: изменить заполнение в другую таблицу
DataAdapter.Fill(_dsReport, "AllCoreT");
DataAdapter.SelectCommand.CommandText = @"
select
ActivePrices.PriceCode, ActivePrices.RegionCode, ActivePrices.PriceDate, ActivePrices.FirmName
from
ActivePrices
where
(ActivePrices.PriceCode <> ?SourcePC or ActivePrices.RegionCode <> ?SourceRegionCode)
order by ActivePrices.PositionCount DESC";
DataAdapter.SelectCommand.Parameters.Clear();
DataAdapter.SelectCommand.Parameters.AddWithValue("?SourcePC", _sourcePriceCode);
DataAdapter.SelectCommand.Parameters.AddWithValue("?SourceRegionCode", _sourceRegionCode);
DataAdapter.Fill(_dsReport, "Prices");
}
protected void btnApply_Click(object sender, EventArgs e)
{
CopyChangesToTable(dgvNonOptional, dtNonOptionalParams, PPropertyValue.ColumnName);
CopyChangesToTable(dgvOptional, dtOptionalParams, OPPropertyValue.ColumnName);
MyCn.Open();
var drows = DS.Tables[dtOptionalParams.TableName].Rows.Cast <DataRow>().Where(dr => (dr.RowState == DataRowState.Added) && (dr[OPrtpID.ColumnName] is DBNull)).ToArray();
for (var i = 0; i < drows.Count(); i++)
{
DS.Tables[dtOptionalParams.TableName].Rows.Remove(drows[i]);
}
foreach (DataRow dr in DS.Tables[dtOptionalParams.TableName].Rows)
{
if (dr.RowState == DataRowState.Added)
{
dr[OPPropertyValue.ColumnName] = MySqlHelper.ExecuteScalar(MyCn, "SELECT DefaultValue FROM reports.report_type_properties WHERE ID=" + dr[OPrtpID.ColumnName]);
}
}
MyCn.Close();
MyCn.Open();
var trans = MyCn.BeginTransaction(IsolationLevel.ReadCommitted);
try {
var requiredParameters = DS.Tables[dtNonOptionalParams.TableName];
var baseCostRows = requiredParameters.Select("PPropertyName = 'ByBaseCosts'");
var weightCostRows = requiredParameters.Select("PPropertyName = 'ByWeightCosts'");
var clientRows = requiredParameters.Select("PPropertyName = 'ClientCode'");
if (baseCostRows.Length > 0 && weightCostRows.Length > 0 && clientRows.Length > 0)
{
if (baseCostRows[0]["PPropertyValue"].ToString() == "1" ||
weightCostRows[0]["PPropertyValue"].ToString() == "1")
{
clientRows[0]["PPropertyValue"] = 1;
}
}
ApplyNonOptimal(trans);
ApplyOptimal(trans);
trans.Commit();
PostData();
}
catch {
trans.Rollback();
throw;
}
finally {
MyCn.Close();
}
if (dgvNonOptional.Rows.Count > 0)
{
btnApply.Visible = true;
}
else
{
btnApply.Visible = false;
}
}
protected void btnApply_Click(object sender, EventArgs e)
{
CopyChangesToTable();
MySqlTransaction trans;
MyCn.Open();
trans = MyCn.BeginTransaction(IsolationLevel.ReadCommitted);
try {
MySqlCommand UpdCmd = new MySqlCommand(@"
UPDATE
reports.enum_values
SET
Value = ?evValue,
DisplayValue = ?evDisplayValue
WHERE ID = ?evID", MyCn, trans);
UpdCmd.Parameters.Clear();
UpdCmd.Parameters.Add(new MySqlParameter("evValue", MySqlDbType.VarString));
UpdCmd.Parameters["evValue"].Direction = ParameterDirection.Input;
UpdCmd.Parameters["evValue"].SourceColumn = evValue.ColumnName;
UpdCmd.Parameters["evValue"].SourceVersion = DataRowVersion.Current;
UpdCmd.Parameters.Add(new MySqlParameter("evDisplayValue", MySqlDbType.VarString));
UpdCmd.Parameters["evDisplayValue"].Direction = ParameterDirection.Input;
UpdCmd.Parameters["evDisplayValue"].SourceColumn = evDisplayValue.ColumnName;
UpdCmd.Parameters["evDisplayValue"].SourceVersion = DataRowVersion.Current;
UpdCmd.Parameters.Add(new MySqlParameter("evID", MySqlDbType.Int64));
UpdCmd.Parameters["evID"].Direction = ParameterDirection.Input;
UpdCmd.Parameters["evID"].SourceColumn = evID.ColumnName;
UpdCmd.Parameters["evID"].SourceVersion = DataRowVersion.Current;
MySqlCommand DelCmd = new MySqlCommand(@"
DELETE from reports.enum_values
WHERE ID = ?evDelID", MyCn, trans);
DelCmd.Parameters.Clear();
DelCmd.Parameters.Add(new MySqlParameter("evDelID", MySqlDbType.Int64));
DelCmd.Parameters["evDelID"].Direction = ParameterDirection.Input;
DelCmd.Parameters["evDelID"].SourceColumn = evID.ColumnName;
DelCmd.Parameters["evDelID"].SourceVersion = DataRowVersion.Original;
MySqlCommand InsCmd = new MySqlCommand(@"
INSERT INTO
reports.enum_values
SET
Value = ?evValue,
DisplayValue = ?evDisplayValue,
PropertyEnumID = ?evPEID", MyCn, trans);
InsCmd.Parameters.Clear();
InsCmd.Parameters.Add(new MySqlParameter("evValue", MySqlDbType.VarString));
InsCmd.Parameters["evValue"].Direction = ParameterDirection.Input;
InsCmd.Parameters["evValue"].SourceColumn = evValue.ColumnName;
InsCmd.Parameters["evValue"].SourceVersion = DataRowVersion.Current;
InsCmd.Parameters.Add(new MySqlParameter("evDisplayValue", MySqlDbType.VarString));
InsCmd.Parameters["evDisplayValue"].Direction = ParameterDirection.Input;
InsCmd.Parameters["evDisplayValue"].SourceColumn = evDisplayValue.ColumnName;
InsCmd.Parameters["evDisplayValue"].SourceVersion = DataRowVersion.Current;
InsCmd.Parameters.Add(new MySqlParameter("evPEID", Request["e"]));
MyDA.UpdateCommand = UpdCmd;
MyDA.DeleteCommand = DelCmd;
MyDA.InsertCommand = InsCmd;
string strHost = HttpContext.Current.Request.UserHostAddress;
string strUser = HttpContext.Current.User.Identity.Name;
if (strUser.StartsWith("ANALIT\\"))
{
strUser = strUser.Substring(7);
}
MySqlHelper.ExecuteNonQuery(trans.Connection, "set @INHost = ?Host; set @INUser = ?User", new MySqlParameter[] { new MySqlParameter("Host", strHost), new MySqlParameter("User", strUser) });
MyDA.Update(DS, DS.Tables[dtEnumValues.TableName].TableName);
trans.Commit();
PostData();
}
catch {
trans.Rollback();
throw;
}
finally {
MyCn.Close();
}
if (dgvEnumValues.Rows.Count > 0)
{
btnApply.Visible = true;
}
else
{
btnApply.Visible = false;
}
}
public void ResendPrice(WcfCallParameter paramDownlogId)
{
var downlogId = Convert.ToUInt64(paramDownlogId.Value);
var drFocused = MySqlHelper.ExecuteDataRow(ConnectionHelper.GetConnectionString(),
@"
SELECT distinct
logs.RowID as DRowID,
logs.LogTime as DLogTime,
logs.Addition as DAddition,
logs.ArchFileName as DArchFileName,
logs.ExtrFileName as DExtrFileName,
sp.Name as DFirmName,
r.Region as DRegion,
if(pd.CostType = 1, concat('[Колонка] ', pc.CostName), pd.PriceName) as DPriceName,
pim.Id as DPriceItemId,
pd.PriceCode as DPriceCode,
pd.ParentSynonym,
if(pd.CostType = 1, pc.CostCode, null) DCostCode,
st.Type as DSourceType,
s.PricePath as DPricePath,
s.EmailTo as DEmailTo,
s.EmailFrom as DEmailFrom,
s.ArchivePassword,
pricefmts.FileExtention as DFileExtention
FROM
logs.downlogs as logs,
Customers.Suppliers sp,
usersettings.pricesdata pd,
usersettings.pricescosts pc,
usersettings.PriceItems pim,
farm.regions r,
farm.sources s,
farm.Sourcetypes st,
farm.formrules fr,
farm.pricefmts
WHERE
pim.Id = logs.PriceItemId
and pc.PriceItemId = pim.Id
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 sp.Id = pd.firmcode
and r.regioncode = sp.HomeRegion
and s.Id = pim.SourceId
and st.Id = s.SourceTypeId
and logs.ResultCode in (2, 3)
and fr.Id = pim.FormRuleId
and pricefmts.id = fr.PriceFormatId
and logs.Rowid = ?DownLogId",
new MySqlParameter("?DownLogId", downlogId));
var filename = GetFileFromArhive(downlogId);
var sourceArchiveFileName = filename;
var archFileName = drFocused["DArchFileName"].ToString();
var externalFileName = drFocused["DExtrFileName"].ToString();
var extractedFile = string.Empty;
try {
if (drFocused["DSourceType"].ToString().Equals("EMAIL", StringComparison.OrdinalIgnoreCase))
{
// Если файл пришел по e-mail, то это должен быть файл *.eml, открываем его на чтение
filename = ExtractFileFromAttachment(filename, archFileName, externalFileName);
extractedFile = filename;
}
var tempDirectory = Path.Combine(Path.GetTempPath(), Path.GetFileNameWithoutExtension(archFileName));
if (ArchiveHelper.IsArchive(filename))
{
if (File.Exists(tempDirectory))
{
File.Delete(tempDirectory);
}
if (Directory.Exists(tempDirectory))
{
Directory.Delete(tempDirectory, true);
}
Directory.CreateDirectory(tempDirectory);
ArchiveHelper.Extract(filename, externalFileName, tempDirectory, drFocused["ArchivePassword"].ToString());
filename = FileHelper.FindFromArhive(tempDirectory, externalFileName);
if (String.IsNullOrEmpty(filename))
{
var errorMessage = String.Format(
"Невозможно найти файл {0} в распакованном архиве!", externalFileName);
throw new FaultException <string>(errorMessage, new FaultReason(errorMessage));
}
}
if (String.IsNullOrEmpty(filename))
{
return;
}
var priceExtention = drFocused["DFileExtention"].ToString();
var destinationFile = Path.Combine(Settings.Default.InboundPath,
"d" + drFocused["DPriceItemId"] + "_" + downlogId + priceExtention);
if (File.Exists(destinationFile))
{
throw new FaultException <string>(MessagePriceInQueue,
new FaultReason(MessagePriceInQueue));
}
File.Copy(filename, destinationFile);
var item = new PriceProcessItem(true,
Convert.ToUInt64(drFocused["DPriceCode"].ToString()),
(drFocused["DCostCode"] is DBNull) ? null : (ulong?)Convert.ToUInt64(drFocused["DCostCode"].ToString()),
Convert.ToUInt64(drFocused["DPriceItemId"].ToString()),
destinationFile,
(drFocused["ParentSynonym"] is DBNull) ? null : (ulong?)Convert.ToUInt64(drFocused["ParentSynonym"].ToString()));
PriceItemList.AddItem(item);
var priceItemId = Convert.ToUInt64(drFocused["DPriceItemId"]);
downlogId = LogResendPriceAsDownload(priceItemId, archFileName, externalFileName, paramDownlogId.LogInformation);
if (downlogId > 0)
{
destinationFile = Path.Combine(Settings.Default.HistoryPath,
downlogId + Path.GetExtension(sourceArchiveFileName));
File.Copy(sourceArchiveFileName, destinationFile);
}
if (Directory.Exists(tempDirectory))
{
FileHelper.Safe(() => Directory.Delete(tempDirectory, true));
}
}
finally {
if (File.Exists(extractedFile))
{
File.Delete(extractedFile);
}
}
}
protected void btnApply_Click(object sender, EventArgs e)
{
CopyChangesToTable();
MySqlTransaction trans;
MyCn.Open();
trans = MyCn.BeginTransaction(IsolationLevel.ReadCommitted);
try {
MySqlCommand UpdCmd = new MySqlCommand(@"
UPDATE
reports.reports
SET
ReportCaption = ?RReportCaption,
ReportTypeCode = ?RReportTypeCode,
GeneralReportCode = ?RGeneralReportCode,
Enabled = ?REnabled
WHERE ReportCode = ?RReportCode", MyCn, trans);
UpdCmd.Parameters.Clear();
UpdCmd.Parameters.Add(new MySqlParameter("RReportCaption", MySqlDbType.VarString));
UpdCmd.Parameters["RReportCaption"].Direction = ParameterDirection.Input;
UpdCmd.Parameters["RReportCaption"].SourceColumn = RReportCaption.ColumnName;
UpdCmd.Parameters["RReportCaption"].SourceVersion = DataRowVersion.Current;
UpdCmd.Parameters.Add(new MySqlParameter("RReportTypeCode", MySqlDbType.Int64));
UpdCmd.Parameters["RReportTypeCode"].Direction = ParameterDirection.Input;
UpdCmd.Parameters["RReportTypeCode"].SourceColumn = RReportTypeCode.ColumnName;
UpdCmd.Parameters["RReportTypeCode"].SourceVersion = DataRowVersion.Current;
UpdCmd.Parameters.Add(new MySqlParameter("RReportCode", MySqlDbType.Int64));
UpdCmd.Parameters["RReportCode"].Direction = ParameterDirection.Input;
UpdCmd.Parameters["RReportCode"].SourceColumn = RReportCode.ColumnName;
UpdCmd.Parameters["RReportCode"].SourceVersion = DataRowVersion.Current;
UpdCmd.Parameters.Add(new MySqlParameter("REnabled", MySqlDbType.Byte));
UpdCmd.Parameters["REnabled"].Direction = ParameterDirection.Input;
UpdCmd.Parameters["REnabled"].SourceColumn = REnabled.ColumnName;
UpdCmd.Parameters["REnabled"].SourceVersion = DataRowVersion.Current;
UpdCmd.Parameters.Add(new MySqlParameter("RGeneralReportCode", _templateReportId));
MySqlCommand DelCmd = new MySqlCommand(@"
DELETE from reports.reports
WHERE ReportCode = ?RDelReportCode", MyCn, trans);
DelCmd.Parameters.Clear();
DelCmd.Parameters.Add(new MySqlParameter("RDelReportCode", MySqlDbType.Int64));
DelCmd.Parameters["RDelReportCode"].Direction = ParameterDirection.Input;
DelCmd.Parameters["RDelReportCode"].SourceColumn = RReportCode.ColumnName;
DelCmd.Parameters["RDelReportCode"].SourceVersion = DataRowVersion.Original;
MySqlCommand InsCmd = new MySqlCommand(@"
INSERT INTO
reports.reports
SET
ReportCaption = ?RReportCaption,
ReportTypeCode = ?RReportTypeCode,
GeneralReportCode = ?RGeneralReportCode,
Enabled = ?REnabled
", MyCn, trans);
InsCmd.Parameters.Clear();
InsCmd.Parameters.Add(new MySqlParameter("RReportCaption", MySqlDbType.VarString));
InsCmd.Parameters["RReportCaption"].Direction = ParameterDirection.Input;
InsCmd.Parameters["RReportCaption"].SourceColumn = RReportCaption.ColumnName;
InsCmd.Parameters["RReportCaption"].SourceVersion = DataRowVersion.Current;
InsCmd.Parameters.Add(new MySqlParameter("RReportTypeCode", MySqlDbType.Int64));
InsCmd.Parameters["RReportTypeCode"].Direction = ParameterDirection.Input;
InsCmd.Parameters["RReportTypeCode"].SourceColumn = RReportTypeCode.ColumnName;
InsCmd.Parameters["RReportTypeCode"].SourceVersion = DataRowVersion.Current;
InsCmd.Parameters.Add(new MySqlParameter("REnabled", MySqlDbType.Byte));
InsCmd.Parameters["REnabled"].Direction = ParameterDirection.Input;
InsCmd.Parameters["REnabled"].SourceColumn = REnabled.ColumnName;
InsCmd.Parameters["REnabled"].SourceVersion = DataRowVersion.Current;
InsCmd.Parameters.Add(new MySqlParameter("RGeneralReportCode", _templateReportId));
MyDA.UpdateCommand = UpdCmd;
MyDA.DeleteCommand = DelCmd;
MyDA.InsertCommand = InsCmd;
string strHost = HttpContext.Current.Request.UserHostAddress;
string strUser = HttpContext.Current.User.Identity.Name;
if (strUser.StartsWith("ANALIT\\"))
{
strUser = strUser.Substring(7);
}
MySqlHelper.ExecuteNonQuery(trans.Connection, "set @INHost = ?Host; set @INUser = ?User", new MySqlParameter[] { new MySqlParameter("Host", strHost), new MySqlParameter("User", strUser) });
MyDA.Update(DS, DS.Tables[dtReports.TableName].TableName);
trans.Commit();
PostData();
}
catch {
trans.Rollback();
throw;
}
finally {
MyCn.Close();
}
if (dgvReports.Rows.Count > 0)
{
btnApply.Visible = true;
}
else
{
btnApply.Visible = false;
}
}
protected void btnApply_Click(object sender, EventArgs e)
{
CopyChangesToTable();
var _deletedReports = new List <ulong>();
var _updatedReports = new List <ulong>();
DataTable dtInserted;
MyCn.Open();
var trans = MyCn.BeginTransaction(IsolationLevel.ReadCommitted);
try {
var UpdCmd = new MySqlCommand(@"
UPDATE
reports.general_reports
SET
Allow = ?Allow,
Public = ?Public,
Comment = ?Comment,
FirmCode = if(PayerID = ?payerID, FirmCode,
(select min(Id)
from
(select s.Id
from Customers.Suppliers s
where s.Payer = ?payerID) tbl)),
PayerID = ?payerID
WHERE GeneralReportCode = ?GeneralReportCode", MyCn, trans);
UpdCmd.Parameters.Clear();
UpdCmd.Parameters.Add(new MySqlParameter("Allow", MySqlDbType.Byte));
UpdCmd.Parameters["Allow"].Direction = ParameterDirection.Input;
UpdCmd.Parameters["Allow"].SourceColumn = Allow.ColumnName;
UpdCmd.Parameters["Allow"].SourceVersion = DataRowVersion.Current;
UpdCmd.Parameters.Add(new MySqlParameter("Public", MySqlDbType.Byte));
UpdCmd.Parameters["Public"].Direction = ParameterDirection.Input;
UpdCmd.Parameters["Public"].SourceColumn = Public.ColumnName;
UpdCmd.Parameters["Public"].SourceVersion = DataRowVersion.Current;
UpdCmd.Parameters.Add(new MySqlParameter("Comment", MySqlDbType.VarString));
UpdCmd.Parameters["Comment"].Direction = ParameterDirection.Input;
UpdCmd.Parameters["Comment"].SourceColumn = Comment.ColumnName;
UpdCmd.Parameters["Comment"].SourceVersion = DataRowVersion.Current;
UpdCmd.Parameters.Add(new MySqlParameter("GeneralReportCode", MySqlDbType.Int64));
UpdCmd.Parameters["GeneralReportCode"].Direction = ParameterDirection.Input;
UpdCmd.Parameters["GeneralReportCode"].SourceColumn = GeneralReportCode.ColumnName;
UpdCmd.Parameters["GeneralReportCode"].SourceVersion = DataRowVersion.Current;
UpdCmd.Parameters.Add("?payerID", MySqlDbType.Int64).SourceColumn = GRPayerID.ColumnName;
MySqlCommand DelCmd = new MySqlCommand(@"
DELETE from reports.general_reports
WHERE GeneralReportCode = ?GRDelCode", MyCn, trans);
DelCmd.Parameters.Clear();
DelCmd.Parameters.Add(new MySqlParameter("GRDelCode", MySqlDbType.Int64));
DelCmd.Parameters["GRDelCode"].Direction = ParameterDirection.Input;
DelCmd.Parameters["GRDelCode"].SourceColumn = GeneralReportCode.ColumnName;
DelCmd.Parameters["GRDelCode"].SourceVersion = DataRowVersion.Original;
MySqlCommand InsCmd = new MySqlCommand(@"
INSERT INTO
reports.general_reports
(PayerId, Allow, Public, Comment, FirmCode)
select
?PayerId,
?Allow,
?Public,
?Comment,
min(Id)
from
(
select s.Id
from Customers.Suppliers s
where s.Payer = ?payerID
) tbl;
select last_insert_id() as GRLastInsertID;
", MyCn, trans);
InsCmd.Parameters.Clear();
InsCmd.Parameters.Add(new MySqlParameter("Allow", MySqlDbType.Byte));
InsCmd.Parameters["Allow"].Direction = ParameterDirection.Input;
InsCmd.Parameters["Allow"].SourceColumn = Allow.ColumnName;
InsCmd.Parameters["Allow"].SourceVersion = DataRowVersion.Current;
InsCmd.Parameters.Add(new MySqlParameter("Public", MySqlDbType.Byte));
InsCmd.Parameters["Public"].Direction = ParameterDirection.Input;
InsCmd.Parameters["Public"].SourceColumn = Public.ColumnName;
InsCmd.Parameters["Public"].SourceVersion = DataRowVersion.Current;
InsCmd.Parameters.Add(new MySqlParameter("PayerId", MySqlDbType.Int64));
InsCmd.Parameters["PayerId"].Direction = ParameterDirection.Input;
InsCmd.Parameters["PayerId"].SourceColumn = GRPayerID.ColumnName;
InsCmd.Parameters["PayerId"].SourceVersion = DataRowVersion.Current;
InsCmd.Parameters.Add(new MySqlParameter("Comment", MySqlDbType.VarString));
InsCmd.Parameters["Comment"].Direction = ParameterDirection.Input;
InsCmd.Parameters["Comment"].SourceColumn = Comment.ColumnName;
InsCmd.Parameters["Comment"].SourceVersion = DataRowVersion.Current;
MyDA.UpdateCommand = UpdCmd;
MyDA.DeleteCommand = DelCmd;
MyDA.InsertCommand = InsCmd;
string strHost = HttpContext.Current.Request.UserHostAddress;
string strUser = HttpContext.Current.User.Identity.Name;
if (strUser.StartsWith("ANALIT\\"))
{
strUser = strUser.Substring(7);
}
MySqlHelper.ExecuteNonQuery(trans.Connection, "set @INHost = ?Host; set @INUser = ?User", new MySqlParameter[] { new MySqlParameter("Host", strHost), new MySqlParameter("User", strUser) });
DataTable dtDeleted = DS.Tables[dtGeneralReports.TableName].GetChanges(DataRowState.Deleted);
if (dtDeleted != null)
{
foreach (DataRow drDeleted in dtDeleted.Rows)
{
var code = Convert.ToUInt64(drDeleted[GeneralReportCode.ColumnName, DataRowVersion.Original]);
var comment = drDeleted[Comment.ColumnName, DataRowVersion.Original].ToString();
SendDeleteAlert(code, comment, strUser, strHost);
_deletedReports.Add(code);
}
MyDA.Update(dtDeleted);
}
dtInserted = DS.Tables[dtGeneralReports.TableName].GetChanges(DataRowState.Added);
if (dtInserted != null)
{
foreach (DataRow drInsert in dtInserted.Rows)
{
if (!Convert.IsDBNull(drInsert[GRPayerID.ColumnName]) && (drInsert[GRPayerID.ColumnName] is long))
{
MyDA.Update(new DataRow[] { drInsert });
_updatedReports.Add(Convert.ToUInt64(drInsert["GRLastInsertID"]));
}
}
}
DataTable dtUpdated = DS.Tables[dtGeneralReports.TableName].GetChanges(DataRowState.Modified);
if (dtUpdated != null)
{
foreach (DataRow drUpdate in dtUpdated.Rows)
{
if (drUpdate["Comment", DataRowVersion.Original] != drUpdate["Comment", DataRowVersion.Current] ||
drUpdate["Public", DataRowVersion.Original] != drUpdate["Public", DataRowVersion.Current] ||
drUpdate["Allow", DataRowVersion.Original] != drUpdate["Allow", DataRowVersion.Current])
{
_updatedReports.Add(Convert.ToUInt64(drUpdate["GeneralReportCode"]));
}
}
MyDA.Update(dtUpdated);
}
trans.Commit();
}
catch
{
trans.Rollback();
throw;
}
finally {
MyCn.Close();
}
//Удаляем задания для отчетов и обновляем комментарии в заданиях (или создаем эти задания)
// А также включаем/выключаем задание при изменении галки "Включен"
UpdateTasksForGeneralReports(_deletedReports, _updatedReports);
PostData();
if (dgvReports.Rows.Count > 0)
{
btnApply.Visible = true;
}
else
{
btnApply.Visible = false;
}
if (dtInserted != null)
{
if (!Request.Url.OriginalString.Contains("#"))
{
Response.Redirect(Request.Url.OriginalString + "#addedPage");
}
}
}
public virtual List <Offer> GetOffers(int clientId, uint sourcePriceCode, uint?noiseSupplierId, bool allAssortment, bool byCatalog, bool withProducers)
{
ClientCode = clientId;
InvokeGetActivePrices();
var assortmentSupplierId = Convert.ToUInt32(
MySqlHelper.ExecuteScalar(Connection,
@"
select FirmCode
from usersettings.pricesdata
where pricesdata.PriceCode = ?PriceCode
",
new MySqlParameter("?PriceCode", sourcePriceCode)));
//Заполняем код региона прайс-листа как домашний код региона клиента, относительно которого строится отчет
var SourceRegionCode = Convert.ToUInt64(
MySqlHelper.ExecuteScalar(Connection,
@"
select RegionCode
from Customers.Clients
where Id = ?ClientCode",
new MySqlParameter("?ClientCode", ClientCode)));
var enabledCost = MySqlHelper.ExecuteScalar(
Connection,
"select CostCode from ActivePrices where PriceCode = ?SourcePC and RegionCode = ?SourceRegionCode",
new MySqlParameter("?SourcePC", sourcePriceCode),
new MySqlParameter("?SourceRegionCode", SourceRegionCode));
if (enabledCost != null)
{
MySqlHelper.ExecuteNonQuery(
Connection,
@"
drop temporary table IF EXISTS Usersettings.SourcePrice;
create temporary table Usersettings.SourcePrice engine=MEMORY
select * from ActivePrices where PriceCode = ?SourcePC and RegionCode = ?SourceRegionCode;",
new MySqlParameter("?SourcePC", sourcePriceCode),
new MySqlParameter("?SourceRegionCode", SourceRegionCode));
}
var joinText = allAssortment || sourcePriceCode == 0 ? " Left JOIN " : " JOIN ";
string withWithoutPropertiesText;
if (byCatalog)
{
withWithoutPropertiesText = String.Format(@" if(C0.SynonymCode is not null, S.Synonym, {0}) ", GetCatalogProductNameSubquery("p.id"));
}
else
{
withWithoutPropertiesText = String.Format(@" if(C0.SynonymCode is not null, S.Synonym, {0}) ", QueryParts.GetFullFormSubquery("p.id", true));
}
var firmcr = withProducers ? " and ifnull(C0.CodeFirmCr,0) = ifnull(c00.CodeFirmCr,0) " : string.Empty;
var producerId = withProducers ? " ifnull(c00.CodeFirmCr, 0) " : " 0 ";
var producerName = withProducers ? " if(c0.SynonymFirmCrCode is not null, Sfc.Synonym , Prod.Name) " : " '-' ";
var result = new List <Offer>();
DataAdapter.SelectCommand.CommandText =
string.Format(
@"
select
p.CatalogId,
c00.ProductId,
{0} as ProducerId,
{1} as ProductName,
{2} as ProducerName,
c00.Id as CoreId,
c00.Code,
Prices.FirmCode as SupplierId,
c00.PriceCode as PriceId,
Prices.RegionCode as RegionId,
c00.Quantity,
if(if(round(cc.Cost * Prices.Upcost, 2) < c00.MinBoundCost, c00.MinBoundCost, round(cc.Cost * Prices.Upcost, 2)) > c00.MaxBoundCost,
c00.MaxBoundCost, if(round(cc.Cost*Prices.UpCost,2) < c00.MinBoundCost, c00.MinBoundCost, round(cc.Cost * Prices.Upcost, 2))) as Cost,
c0.Id as AssortmentCoreId,
c0.Code as AssortmentCode,
c0.CodeCr as AssortmentCodeCr,
{9} as AssortmentSupplierId,
c0.PriceCode as AssortmentPriceId,
{10} as AssortmentRegionId,
c0.Quantity as AssortmentQuantity,
{7} as AssortmentCost
from
Usersettings.ActivePrices Prices
join farm.core0 c00 on c00.PriceCode = Prices.PriceCode
join farm.CoreCosts cc on cc.Core_Id = c00.Id and cc.PC_CostCode = Prices.CostCode
join catalogs.Products as p on p.id = c00.productid
join Catalogs.Catalog as cg on p.catalogid = cg.id
{3} farm.Core0 c0 on c0.productid = c00.productid {4} and C0.PriceCode = {5}
{6}
left join Catalogs.Producers Prod on c00.CodeFirmCr = Prod.Id
left join farm.Synonym S on C0.SynonymCode = S.SynonymCode
left join farm.SynonymFirmCr Sfc on C0.SynonymFirmCrCode = Sfc.SynonymFirmCrCode
{8}
WHERE
{11}
",
producerId,
withWithoutPropertiesText,
producerName,
joinText,
firmcr,
sourcePriceCode,
(enabledCost != null)
? @"
left join farm.CoreCosts cc0 on cc0.Core_Id = c0.Id and cc0.PC_CostCode = " + enabledCost + @"
left join Usersettings.SourcePrice c0Prices on c0Prices.CostCode = " + enabledCost
: "",
(enabledCost != null)
? @"
if(cc0.Cost is null, 0,
if(if(round(cc0.Cost * c0Prices.Upcost, 2) < c0.MinBoundCost, c0.MinBoundCost, round(cc0.Cost * c0Prices.Upcost, 2)) > c0.MaxBoundCost,
c0.MaxBoundCost, if(round(cc0.Cost*c0Prices.UpCost,2) < c0.MinBoundCost, c0.MinBoundCost, round(cc0.Cost * c0Prices.Upcost, 2)))
)"
: " null ",
@"",
assortmentSupplierId,
SourceRegionCode,
sourcePriceCode == 0
? " c00.Junk = 0 "
: @"
({1} (c0.PriceCode <> c00.PriceCode) or (Prices.RegionCode <> {0}) or (c0.Id = c00.Id))
and (c00.Junk = 0 or c0.Id = c00.Id)".Format(SourceRegionCode, allAssortment || sourcePriceCode == 0 ? "(c0.PriceCode is null) or" : string.Empty));
Random random = null;
if (noiseSupplierId.HasValue)
{
random = new Random();
}
#if DEBUG
Debug.WriteLine(DataAdapter.SelectCommand.CommandText);
#endif
using (var reader = DataAdapter.SelectCommand.ExecuteReader()) {
foreach (var row in reader.Cast <IDataRecord>())
{
var offer = new Offer(row, noiseSupplierId, random);
result.Add(offer);
}
}
return(result);
}
protected override void GenerateReport()
{
ProfileHelper.Next("PreGetOffers");
if (_priceCode == 0)
{
throw new ReportException("В отчете не установлен параметр \"Прайс-лист\".");
}
var customerFirmName = GetSupplierName(_priceCode);
CheckPriceActual(_priceCode);
if (_byWeightCosts)
{
ProcessWeigth();
return;
}
GetOffers(_SupplierNoise);
var enabledPrice = Convert.ToInt32(
MySqlHelper.ExecuteScalar(
DataAdapter.SelectCommand.Connection,
"select PriceCode from ActivePrices where PriceCode = ?PriceCode",
new MySqlParameter("?PriceCode", _priceCode)));
if (enabledPrice == 0 && !_byBaseCosts)
{
var clientShortName = Convert.ToString(
MySqlHelper.ExecuteScalar(
DataAdapter.SelectCommand.Connection,
@"select Name from Customers.Clients where Id = ?FirmCode",
new MySqlParameter("?FirmCode", ClientCode)));
throw new ReportException(String.Format("Для клиента {0} ({1}) не доступен прайс-лист {2} ({3}).", clientShortName, ClientCode, customerFirmName, _priceCode));
}
DataAdapter.SelectCommand.Parameters.Clear();
var selectCommandText = String.Empty;
switch (_reportType)
{
case DefReportType.ByName: {
selectCommandText = @"
drop temporary table IF EXISTS SummaryByPrices;
CREATE temporary table SummaryByPrices (
NameId int Unsigned,
key NameId(NameId))engine=MEMORY PACK_KEYS = 0;
INSERT INTO SummaryByPrices
select distinct Catalog.NameId
from
ActivePrices apt,
Core c,
Catalogs.Products,
Catalogs.Catalog
where
apt.PriceCode <> ?SourcePC
and apt.PriceCode=c.PriceCode
and Products.Id = c.ProductId
and Catalog.Id = products.CatalogId;
drop temporary table IF EXISTS OtherByPrice;
CREATE temporary table OtherByPrice (
NameId int Unsigned,
Code VARCHAR(20) not NULL,
key NameId(NameId))engine=MEMORY PACK_KEYS = 0;
INSERT INTO OtherByPrice
select distinct Catalog.NameId, FarmCore.Code
from
(
Core c
inner join Catalogs.Products on c.ProductId = Products.Id
inner join Catalogs.Catalog on Products.CatalogId = Catalog.Id
)
inner join farm.Core0 FarmCore on FarmCore.Id = c.Id
left join SummaryByPrices st on st.NameId = Catalog.NameId
where
c.PriceCode=?SourcePC
and st.NameId is NULL
and Catalog.Pharmacie = 1;
select distinct OtherByPrice.Code, CatalogNames.Name
from
OtherByPrice
inner join catalogs.CatalogNames on OtherByPrice.NameId = CatalogNames.Id
order by CatalogNames.Name;";
break;
}
case DefReportType.ByNameAndForm: {
selectCommandText = @"
drop temporary table IF EXISTS SummaryByPrices;
CREATE temporary table SummaryByPrices (
CatalogId int Unsigned,
key CatalogId(CatalogId)) engine=MEMORY PACK_KEYS = 0;
INSERT INTO SummaryByPrices
select distinct Products.CatalogId
from
ActivePrices apt,
Core c,
Catalogs.Products
where
apt.PriceCode <> ?SourcePC
and apt.PriceCode=c.PriceCode
and Products.Id = c.ProductId;
drop temporary table IF EXISTS OtherByPrice;
CREATE temporary table OtherByPrice (
CatalogId int Unsigned,
Code VARCHAR(20) not NULL,
key CatalogId(CatalogId) ) engine=MEMORY PACK_KEYS = 0;
INSERT INTO OtherByPrice
select distinct Products.CatalogId, FarmCore.Code
from
(
Core c
inner join Catalogs.Products on c.ProductId = Products.Id
)
inner join farm.Core0 FarmCore on FarmCore.Id = c.Id
left join SummaryByPrices st on st.CatalogId = Products.CatalogId
where
c.PriceCode=?SourcePC
and st.CatalogId is NULL;
select distinct OtherByPrice.Code, CatalogNames.Name, CatalogForms.Form
from
OtherByPrice
inner join catalogs.catalog on OtherByPrice.CatalogId = catalog.Id
inner join catalogs.CatalogNames on catalog.NameId = CatalogNames.Id
inner join catalogs.CatalogForms on catalog.FormId = CatalogForms.Id
where catalog.Pharmacie = 1
order by CatalogNames.Name, CatalogForms.Form;";
break;
}
case DefReportType.ByNameAndFormAndFirmCr: {
selectCommandText = @"
drop temporary table IF EXISTS SummaryByPrices;
CREATE temporary table SummaryByPrices (
CatalogId int Unsigned,
CodeFirmCr int Unsigned,
key CatalogId(CatalogId),
key CodeFirmCr(CodeFirmCr)) engine=MEMORY PACK_KEYS = 0;
INSERT INTO SummaryByPrices
select distinct Products.CatalogId, FarmCore.CodeFirmCr
from
ActivePrices apt,
Core c,
farm.Core0 FarmCore,
Catalogs.Products
where
apt.PriceCode <> ?SourcePC
and apt.PriceCode = c.PriceCode
and Products.Id = c.ProductId
and FarmCore.Id = c.Id
and FarmCore.CodeFirmCr is not null;
drop temporary table IF EXISTS OtherByPrice;
CREATE temporary table OtherByPrice (
CatalogId int Unsigned,
CodeFirmCr int Unsigned,
Code VARCHAR(20) not NULL,
key CatalogId(CatalogId),
key CodeFirmCr(CodeFirmCr) ) engine=MEMORY PACK_KEYS = 0;
INSERT INTO OtherByPrice
select distinct Products.CatalogId, FarmCore.CodeFirmCr, FarmCore.Code
from
Core c
inner join farm.Core0 FarmCore on c.Id = FarmCore.Id
inner join Catalogs.Products on c.ProductId = Products.Id
left join SummaryByPrices st on st.CatalogId = Products.CatalogId and st.CodeFirmCr = FarmCore.CodeFirmCr
where
c.PriceCode=?SourcePC
and FarmCore.CodeFirmCr is not null
and st.CatalogId is NULL;
select distinct OtherByPrice.Code, CatalogNames.Name, CatalogForms.Form, Producers.Name as FirmCr
from
OtherByPrice
join catalogs.catalog on OtherByPrice.CatalogId = catalog.Id
join catalogs.CatalogNames on catalog.NameId = CatalogNames.Id
join catalogs.CatalogForms on catalog.FormId = CatalogForms.Id
join Catalogs.Producers on Producers.Id = OtherByPrice.CodeFirmCr
where catalog.Pharmacie = 1
order by CatalogNames.Name, CatalogForms.Form, Producers.Name;";
break;
}
case DefReportType.ByProduct: {
selectCommandText = String.Format(@"
drop temporary table IF EXISTS SummaryByPrices;
CREATE temporary table SummaryByPrices (
ProductId int Unsigned,
key ProductId(ProductId)) engine=MEMORY PACK_KEYS = 0;
INSERT INTO SummaryByPrices
select distinct c.ProductId
from
ActivePrices apt,
Core c
where
apt.PriceCode <> ?SourcePC
and apt.PriceCode=c.PriceCode;
drop temporary table IF EXISTS OtherByPrice;
CREATE temporary table OtherByPrice (
ProductId int Unsigned,
Code VARCHAR(20) not NULL,
key ProductId(ProductId)) engine=MEMORY PACK_KEYS = 0;
INSERT INTO OtherByPrice
select distinct c.ProductId, FarmCore.Code
from
Core c
inner join farm.Core0 FarmCore on FarmCore.Id = c.Id
left join SummaryByPrices st on st.ProductId = c.ProductId
where
c.PriceCode=?SourcePC
and st.ProductId is NULL;
select
distinct
OtherByPrice.Code,
CatalogNames.Name,
{0} as FullForm
from
(
OtherByPrice
inner join catalogs.products on OtherByPrice.ProductId = products.Id
inner join catalogs.catalog on products.CatalogId = catalog.Id
inner join catalogs.CatalogNames on catalog.NameId = CatalogNames.Id
)
where catalog.Pharmacie = 1
order by CatalogNames.Name, FullForm;
", QueryParts.GetFullFormSubquery("OtherByPrice.ProductId", false));
break;
}
case DefReportType.ByProductAndFirmCr: {
selectCommandText = String.Format(@"
drop temporary table IF EXISTS SummaryByPrices;
CREATE temporary table SummaryByPrices (
ProductId int Unsigned,
CodeFirmCr int Unsigned,
key ProductId(ProductId),
key CodeFirmCr(CodeFirmCr)) engine=MEMORY PACK_KEYS = 0;
INSERT INTO SummaryByPrices
select distinct c.ProductId, FarmCore.CodeFirmCr
from
ActivePrices apt,
Core c,
farm.Core0 FarmCore
where
apt.PriceCode <> ?SourcePC
and apt.PriceCode=c.PriceCode
and FarmCore.Id = c.Id
and FarmCore.CodeFirmCr is not null;
drop temporary table IF EXISTS OtherByPrice;
CREATE temporary table OtherByPrice (
ProductId int Unsigned,
CodeFirmCr int Unsigned,
Code VARCHAR(20) not NULL,
key ProductId(ProductId),
key CodeFirmCr(CodeFirmCr) ) engine=MEMORY PACK_KEYS = 0;
INSERT INTO OtherByPrice
select distinct c.ProductId, FarmCore.CodeFirmCr, FarmCore.Code
from
Core c
inner join farm.Core0 FarmCore on FarmCore.Id = c.Id
left join SummaryByPrices st on st.ProductId = c.ProductId and st.CodeFirmCr = FarmCore.CodeFirmCr
where
c.PriceCode=?SourcePC
FarmCore.CodeFirmCr is not null
and st.ProductId is NULL;
select
distinct
OtherByPrice.Code,
CatalogNames.Name,
{0} as FullForm,
Producers.Name as FirmCr
from
OtherByPrice
join catalogs.products on OtherByPrice.ProductId = products.Id
join catalogs.catalog on products.CatalogId = catalog.Id
join catalogs.CatalogNames on catalog.NameId = CatalogNames.Id
join Catalogs.Producers on Producers.Id = OtherByPrice.CodeFirmCr
where catalog.Pharmacie = 1
order by CatalogNames.Name, FullForm, Producers.Name;
", QueryParts.GetFullFormSubquery("OtherByPrice.ProductId", false));
break;
}
}
DataAdapter.SelectCommand.CommandText = selectCommandText;
DataAdapter.SelectCommand.Parameters.AddWithValue("?SourcePC", _priceCode);
DataAdapter.Fill(_dsReport, "Results");
}
private void ProcessWeigth()
{
GetWeightCostOffers();
DataAdapter.SelectCommand.CommandType = CommandType.Text;
DataAdapter.SelectCommand.Parameters.Clear();
var selectCommandText = String.Empty;
switch (_reportType)
{
case DefReportType.ByName: {
selectCommandText = @"
drop temporary table IF EXISTS SummaryByPrices;
CREATE temporary table SummaryByPrices (
NameId int Unsigned,
key NameId(NameId))engine=MEMORY PACK_KEYS = 0;
INSERT INTO SummaryByPrices
select distinct Catalog.NameId
from
Core c,
Catalogs.Products pr,
Catalogs.Assortment,
Catalogs.Catalog
where
c.PriceCode <> ?SourcePC
and pr.Id=c.ProductId
and Catalog.Id = pr.CatalogId
and Assortment.catalogId = Catalog.Id;
drop temporary table IF EXISTS OtherByPrice;
CREATE temporary table OtherByPrice (
NameId int Unsigned,
Code VARCHAR(20) not NULL,
key NameId(NameId))engine=MEMORY PACK_KEYS = 0;
INSERT INTO OtherByPrice
select distinct Catalog.NameId, '' Code
from
(
Core c
inner join Catalogs.Products pr on pr.Id=c.ProductId
inner join Catalogs.Catalog on pr.CatalogId = Catalog.Id
)
left join SummaryByPrices st on st.NameId = Catalog.NameId
where
c.PriceCode=?SourcePC
and st.NameId is NULL
and Catalog.Pharmacie = 1;
select distinct OtherByPrice.Code, CatalogNames.Name
from
OtherByPrice
inner join catalogs.CatalogNames on OtherByPrice.NameId = CatalogNames.Id
order by CatalogNames.Name;";
break;
}
case DefReportType.ByNameAndForm: {
selectCommandText = @"
drop temporary table IF EXISTS SummaryByPrices;
CREATE temporary table SummaryByPrices (
CatalogId int Unsigned,
key CatalogId(CatalogId)) engine=MEMORY PACK_KEYS = 0;
INSERT INTO SummaryByPrices
select distinct pr.CatalogId
from
Core c,
Catalogs.Products pr
where
c.PriceCode <> ?SourcePC
and pr.Id = c.ProductId;
drop temporary table IF EXISTS OtherByPrice;
CREATE temporary table OtherByPrice (
CatalogId int Unsigned,
Code VARCHAR(20) not NULL,
key CatalogId(CatalogId) ) engine=MEMORY PACK_KEYS = 0;
INSERT INTO OtherByPrice
select distinct pr.CatalogId, '' Code
from
(
Core c
inner join Catalogs.Products pr on c.ProductId = pr.Id
)
left join SummaryByPrices st on st.CatalogId = pr.CatalogId
where
c.PriceCode=?SourcePC
and st.CatalogId is NULL;
select distinct OtherByPrice.Code, CatalogNames.Name, CatalogForms.Form
from
OtherByPrice
inner join catalogs.catalog on OtherByPrice.CatalogId = catalog.Id
inner join catalogs.CatalogNames on catalog.NameId = CatalogNames.Id
inner join catalogs.CatalogForms on catalog.FormId = CatalogForms.Id
where catalog.Pharmacie = 1
order by CatalogNames.Name, CatalogForms.Form;";
break;
}
case DefReportType.ByNameAndFormAndFirmCr: {
selectCommandText = @"
drop temporary table IF EXISTS SummaryByPrices;
CREATE temporary table SummaryByPrices (
CatalogId int Unsigned,
CodeFirmCr int Unsigned,
key CatalogId(CatalogId),
key CodeFirmCr(CodeFirmCr)) engine=MEMORY PACK_KEYS = 0;
INSERT INTO SummaryByPrices
select distinct Products.CatalogId, c.ProducerId as CodeFirmCr
from
Core c,
Catalogs.Products
where
c.PriceCode <> ?SourcePC
and Products.Id = c.ProductId
and c.ProducerId is not null;
drop temporary table IF EXISTS OtherByPrice;
CREATE temporary table OtherByPrice (
CatalogId int Unsigned,
CodeFirmCr int Unsigned,
Code VARCHAR(20) not NULL,
key CatalogId(CatalogId),
key CodeFirmCr(CodeFirmCr) ) engine=MEMORY PACK_KEYS = 0;
INSERT INTO OtherByPrice
select distinct Products.CatalogId, c.ProducerId as CodeFirmCr, '' as Code
from
Core c
inner join Catalogs.Products on c.ProductId = Products.Id
left join SummaryByPrices st on st.CatalogId = Products.CatalogId and st.CodeFirmCr = c.ProducerId
where
c.PriceCode=?SourcePC
and c.ProducerId is not null
and st.CatalogId is NULL;
select distinct OtherByPrice.Code, CatalogNames.Name, CatalogForms.Form, Producers.Name as FirmCr
from OtherByPrice
join catalogs.catalog on OtherByPrice.CatalogId = catalog.Id
join catalogs.CatalogNames on catalog.NameId = CatalogNames.Id
join catalogs.CatalogForms on catalog.FormId = CatalogForms.Id
join Catalogs.Producers on Producers.Id = OtherByPrice.CodeFirmCr
where catalog.Pharmacie = 1
order by CatalogNames.Name, CatalogForms.Form, Producers.Name;";
break;
}
case DefReportType.ByProduct: {
selectCommandText = String.Format(@"
drop temporary table IF EXISTS SummaryByPrices;
CREATE temporary table SummaryByPrices (
ProductId int Unsigned,
key ProductId(ProductId)) engine=MEMORY PACK_KEYS = 0;
INSERT INTO SummaryByPrices
select distinct c.ProductId
from
Core c
where
c.PriceCode <> ?SourcePC;
drop temporary table IF EXISTS OtherByPrice;
CREATE temporary table OtherByPrice (
ProductId int Unsigned,
Code VARCHAR(20) not NULL,
key ProductId(ProductId)) engine=MEMORY PACK_KEYS = 0;
INSERT INTO OtherByPrice
select distinct c.ProductId, '' as Code
from
Core c
left join SummaryByPrices st on st.ProductId = c.ProductId
where
c.PriceCode=?SourcePC
and st.ProductId is NULL;
select
distinct
OtherByPrice.Code,
CatalogNames.Name,
CatalogForms.Form as FullForm
from
(
OtherByPrice
inner join catalogs.products on OtherByPrice.ProductId = products.Id
inner join catalogs.catalog on products.CatalogId = catalog.Id
inner join catalogs.CatalogNames on catalog.NameId = CatalogNames.Id
inner join catalogs.CatalogForms on CatalogForms.Id = catalog.FormId
)
where catalog.Pharmacie = 1
order by CatalogNames.Name, FullForm;
");
break;
}
case DefReportType.ByProductAndFirmCr: {
selectCommandText = String.Format(@"
drop temporary table IF EXISTS SummaryByPrices;
CREATE temporary table SummaryByPrices (
ProductId int Unsigned,
CodeFirmCr int Unsigned,
key ProductId(ProductId),
key CodeFirmCr(CodeFirmCr)) engine=MEMORY PACK_KEYS = 0;
INSERT INTO SummaryByPrices
select distinct c.ProductId, c.ProducerId as CodeFirmCr
from Core c
where c.PriceCode <> ?SourcePC
c.ProducerId is not null;
drop temporary table IF EXISTS OtherByPrice;
CREATE temporary table OtherByPrice (
ProductId int Unsigned,
CodeFirmCr int Unsigned,
Code VARCHAR(20) not NULL,
key ProductId(ProductId),
key CodeFirmCr(CodeFirmCr) ) engine=MEMORY PACK_KEYS = 0;
INSERT INTO OtherByPrice
select distinct c.ProductId, c.ProducerId as CodeFirmCr, '' as Code
from
Core c
join catalogs.products on products.id = c.ProductId
left join SummaryByPrices st on st.ProductId = c.ProductId and st.CodeFirmCr = c.ProducerId
where
c.PriceCode = ?SourcePC
and c.ProducerId is not null
and st.ProductId is NULL;
select
distinct
OtherByPrice.Code,
CatalogNames.Name,
CatalogForms.Form as FullForm,
Producers.Name as FirmCr
from
OtherByPrice
join catalogs.products on OtherByPrice.ProductId = products.Id
join catalogs.catalog on products.CatalogId = catalog.Id
join catalogs.CatalogNames on catalog.NameId = CatalogNames.Id
join catalogs.CatalogForms on CatalogForms.Id = catalog.FormId
join Catalogs.Producers on Producers.Id = OtherByPrice.CodeFirmCr
where catalog.Pharmacie = 1
order by CatalogNames.Name, FullForm, Producers.Name;
");
break;
}
}
var sourcePc = Convert.ToInt32(
MySqlHelper.ExecuteScalar(DataAdapter.SelectCommand.Connection,
@"
select
pricesdata.FirmCode
from
usersettings.pricesdata
where
pricesdata.PriceCode = ?PriceCode;" ,
new MySqlParameter("?PriceCode", _priceCode)));
DataAdapter.SelectCommand.CommandText = selectCommandText;
DataAdapter.SelectCommand.Parameters.AddWithValue("?SourcePC", sourcePc);
DataAdapter.Fill(_dsReport, "Results");
ProfileHelper.End();
}
public void ApplyChanges(MySqlConnection masterConnection, IProgressNotifier formProgress, List <DataRow> rows)
{
operatorName = Environment.UserName.ToLower();
CalculateChanges(masterConnection, formProgress, rows);
var updateSynonymProducerEtalonSQL = daSynonymFirmCr.UpdateCommand.CommandText;
var insertSynonymProducerEtalonSQL = daSynonymFirmCr.InsertCommand.CommandText;
var changes = dtSynonymFirmCr.GetChanges(DataRowState.Modified);
if (changes != null)
{
stat.SynonymFirmCrCount += changes.Rows.Count;
}
formProgress.Status = "Применение изменений в базу данных...";
DataRow lastUpdateSynonym = null;
try {
With.DeadlockWraper(c => {
var humanName = GetHumanName(c, operatorName);
var helper = new Common.MySql.MySqlHelper(c, null);
var commandHelper = helper.Command("set @inHost = ?Host; set @inUser = ?UserName;");
commandHelper.AddParameter("?Host", Environment.MachineName);
commandHelper.AddParameter("?UserName", operatorName);
commandHelper.Execute();
//Заполнили таблицу логов для синонимов наименований
daSynonym.SelectCommand.Connection = c;
daSynonym.Update(dtSynonym);
formProgress.ApplyProgress += 10;
var insertExclude = new MySqlCommand(@"
insert into Farm.Excludes(CatalogId, PriceCode, ProducerSynonym, DoNotShow, Operator, OriginalSynonymId)
value (?CatalogId, ?PriceCode, ?ProducerSynonym, ?DoNotShow, ?Operator, ?OriginalSynonymId);", c);
insertExclude.Parameters.AddWithValue("?PriceCode", priceId);
insertExclude.Parameters.AddWithValue("?Operator", humanName);
insertExclude.Parameters.Add("?ProducerSynonym", MySqlDbType.VarChar);
insertExclude.Parameters.Add("?DoNotShow", MySqlDbType.Byte);
insertExclude.Parameters.Add("?CatalogId", MySqlDbType.UInt32);
insertExclude.Parameters.Add("?OriginalSynonymId", MySqlDbType.UInt32);
foreach (var exclude in excludes.Where(e => e.Id == 0))
{
if (!IsExcludeCorrect(c, exclude))
{
continue;
}
insertExclude.Parameters["?ProducerSynonym"].Value = exclude.ProducerSynonym;
insertExclude.Parameters["?DoNotShow"].Value = exclude.DoNotShow;
insertExclude.Parameters["?CatalogId"].Value = exclude.CatalogId;
insertExclude.Parameters["?OriginalSynonymId"].Value = exclude.GetOriginalSynonymId();
insertExclude.ExecuteScalar();
}
//Заполнили таблицу логов для синонимов производителей
daSynonymFirmCr.SelectCommand.Connection = c;
daSynonymFirmCr.UpdateCommand.Connection = c;
daSynonymFirmCr.InsertCommand.Connection = c;
var dtSynonymFirmCrCopy = dtSynonymFirmCr.Copy();
foreach (DataRow drInsertProducerSynonym in dtSynonymFirmCrCopy.Rows)
{
lastUpdateSynonym = drInsertProducerSynonym;
daSynonymFirmCr.InsertCommand.CommandText = insertSynonymProducerEtalonSQL;
daSynonymFirmCr.UpdateCommand.CommandText = updateSynonymProducerEtalonSQL;
//обновляем по одному синониму производителя, т.к. может быть добавление в исключение
daSynonymFirmCr.Update(new[] { drInsertProducerSynonym });
}
MySqlHelper.ExecuteNonQuery(c,
@"
update
usersettings.pricescosts,
usersettings.priceitems
set
priceitems.LastSynonymsCreation = now()
where
pricescosts.PriceCode = ?PriceCode
and priceitems.Id = pricescosts.PriceItemId",
new MySqlParameter("?PriceCode", priceId));
formProgress.ApplyProgress += 10;
//Заполнили таблицу логов для запрещённых выражений
daForbidden.SelectCommand.Connection = c;
var dtForbiddenCopy = dtForbidden.Copy();
daForbidden.Update(dtForbiddenCopy);
formProgress.ApplyProgress += 10;
//Обновление таблицы нераспознанных выражений
daUnrecUpdate.SelectCommand.Connection = c;
var dtUnrecUpdateCopy = dtUnrecUpdate.Copy();
daUnrecUpdate.Update(dtUnrecUpdateCopy);
formProgress.ApplyProgress += 10;
// Сохраняем запрещенные имена производителей
var deleteUnrec = new MySqlCommand("delete from farm.UnrecExp where LOWER(FirmCr) = ?FirmName and Status = 1", c);
var insertForbiddenProducer = new MySqlCommand(@"
insert into Farm.Forbiddenproducers(Name)
value (?Name);", c);
insertForbiddenProducer.Parameters.Add("?Name", MySqlDbType.VarChar);
foreach (var producer in ForbiddenProducers.Where(e => e.Id == 0))
{
insertForbiddenProducer.Parameters["?Name"].Value = producer.Name;
insertForbiddenProducer.ExecuteScalar();
// удаляем нераспознанные выражения с таким же наименованием производителя
deleteUnrec.Parameters.Clear();
deleteUnrec.Parameters.AddWithValue("?FirmName", producer.Name.ToLower());
deleteUnrec.ExecuteNonQuery();
}
});
}
catch (Exception e) {
if (e.Message.Contains("Duplicate entry"))
{
Mailer.SendDebugLog(dtSynonymFirmCr, e, lastUpdateSynonym);
}
throw;
}
formProgress.ApplyProgress = 80;
formProgress.Status = String.Empty;
formProgress.Error = String.Empty;
formProgress.Status = "Перепроведение пpайса...";
formProgress.ApplyProgress = 80;
try {
#if !DEBUG
_remotePriceProcessor.RetransPriceSmartMsMq(priceId);
#endif
}
catch (Exception e) {
formProgress.Error = "При перепроведении файлов возникла ошибка, которая отправлена разработчику.";
_logger.Error(String.Format("Ошибка при перепроведении прайс листа {0}", priceId), e);
}
_logger.DebugFormat("Перепроведение пpайса завершено.");
formProgress.ApplyProgress = 100;
}
protected void btnApply_Click(object sender, EventArgs e)
{
if (Page.IsValid)
{
CopyChangesToTable();
MySqlTransaction trans;
MyCn.Open();
trans = MyCn.BeginTransaction(IsolationLevel.ReadCommitted);
try {
MySqlCommand UpdCmd = new MySqlCommand(@"
UPDATE
reports.report_type_properties
SET
PropertyName = ?PName,
DisplayName = ?PDisplayName,
DefaultValue = ?PDefaultValue,
PropertyType = ?PType,
Optional = ?POptional,
PropertyEnumID = ?PEnumID,
SelectStoredProcedure = ?PStoredProc
WHERE ID = ?PID", MyCn, trans);
UpdCmd.Parameters.Clear();
UpdCmd.Parameters.Add(new MySqlParameter("PName", MySqlDbType.VarString));
UpdCmd.Parameters["PName"].Direction = ParameterDirection.Input;
UpdCmd.Parameters["PName"].SourceColumn = PName.ColumnName;
UpdCmd.Parameters["PName"].SourceVersion = DataRowVersion.Current;
UpdCmd.Parameters.Add(new MySqlParameter("PDisplayName", MySqlDbType.VarString));
UpdCmd.Parameters["PDisplayName"].Direction = ParameterDirection.Input;
UpdCmd.Parameters["PDisplayName"].SourceColumn = PDisplayName.ColumnName;
UpdCmd.Parameters["PDisplayName"].SourceVersion = DataRowVersion.Current;
UpdCmd.Parameters.Add(new MySqlParameter("PDefaultValue", MySqlDbType.VarString));
UpdCmd.Parameters["PDefaultValue"].Direction = ParameterDirection.Input;
UpdCmd.Parameters["PDefaultValue"].SourceColumn = PDefaultValue.ColumnName;
UpdCmd.Parameters["PDefaultValue"].SourceVersion = DataRowVersion.Current;
UpdCmd.Parameters.Add(new MySqlParameter("PType", MySqlDbType.VarString));
UpdCmd.Parameters["PType"].Direction = ParameterDirection.Input;
UpdCmd.Parameters["PType"].SourceColumn = PType.ColumnName;
UpdCmd.Parameters["PType"].SourceVersion = DataRowVersion.Current;
UpdCmd.Parameters.Add(new MySqlParameter("POptional", MySqlDbType.Byte));
UpdCmd.Parameters["POptional"].Direction = ParameterDirection.Input;
UpdCmd.Parameters["POptional"].SourceColumn = POptional.ColumnName;
UpdCmd.Parameters["POptional"].SourceVersion = DataRowVersion.Current;
UpdCmd.Parameters.Add(new MySqlParameter("PEnumID", MySqlDbType.Int64));
UpdCmd.Parameters["PEnumID"].Direction = ParameterDirection.Input;
UpdCmd.Parameters["PEnumID"].SourceColumn = PEnumID.ColumnName;
UpdCmd.Parameters["PEnumID"].SourceVersion = DataRowVersion.Current;
UpdCmd.Parameters.Add(new MySqlParameter("PStoredProc", MySqlDbType.VarString));
UpdCmd.Parameters["PStoredProc"].Direction = ParameterDirection.Input;
UpdCmd.Parameters["PStoredProc"].SourceColumn = PStoredProc.ColumnName;
UpdCmd.Parameters["PStoredProc"].SourceVersion = DataRowVersion.Current;
UpdCmd.Parameters.Add(new MySqlParameter("PID", MySqlDbType.Int64));
UpdCmd.Parameters["PID"].Direction = ParameterDirection.Input;
UpdCmd.Parameters["PID"].SourceColumn = PID.ColumnName;
UpdCmd.Parameters["PID"].SourceVersion = DataRowVersion.Current;
MySqlCommand DelCmd = new MySqlCommand(@"
DELETE from reports.report_type_properties
WHERE ID = ?PDelID", MyCn, trans);
DelCmd.Parameters.Clear();
DelCmd.Parameters.Add(new MySqlParameter("PDelID", MySqlDbType.Int64));
DelCmd.Parameters["PDelID"].Direction = ParameterDirection.Input;
DelCmd.Parameters["PDelID"].SourceColumn = PID.ColumnName;
DelCmd.Parameters["PDelID"].SourceVersion = DataRowVersion.Original;
MySqlCommand InsCmd = new MySqlCommand(@"
INSERT INTO
reports.report_type_properties
SET
PropertyName = ?PName,
DisplayName = ?PDisplayName,
DefaultValue = ?PDefaultValue,
PropertyType = ?PType,
PropertyEnumID = ?PEnumID,
Optional = ?POptional,
SelectStoredProcedure = ?PStoredProc,
ReportTypeCode = ?rtc
", MyCn, trans);
InsCmd.Parameters.Clear();
InsCmd.Parameters.Add(new MySqlParameter("PName", MySqlDbType.VarString));
InsCmd.Parameters["PName"].Direction = ParameterDirection.Input;
InsCmd.Parameters["PName"].SourceColumn = PName.ColumnName;
InsCmd.Parameters["PName"].SourceVersion = DataRowVersion.Current;
InsCmd.Parameters.Add(new MySqlParameter("PDisplayName", MySqlDbType.VarString));
InsCmd.Parameters["PDisplayName"].Direction = ParameterDirection.Input;
InsCmd.Parameters["PDisplayName"].SourceColumn = PDisplayName.ColumnName;
InsCmd.Parameters["PDisplayName"].SourceVersion = DataRowVersion.Current;
InsCmd.Parameters.Add(new MySqlParameter("PDefaultValue", MySqlDbType.VarString));
InsCmd.Parameters["PDefaultValue"].Direction = ParameterDirection.Input;
InsCmd.Parameters["PDefaultValue"].SourceColumn = PDefaultValue.ColumnName;
InsCmd.Parameters["PDefaultValue"].SourceVersion = DataRowVersion.Current;
InsCmd.Parameters.Add(new MySqlParameter("PType", MySqlDbType.VarString));
InsCmd.Parameters["PType"].Direction = ParameterDirection.Input;
InsCmd.Parameters["PType"].SourceColumn = PType.ColumnName;
InsCmd.Parameters["PType"].SourceVersion = DataRowVersion.Current;
InsCmd.Parameters.Add(new MySqlParameter("POptional", MySqlDbType.Byte));
InsCmd.Parameters["POptional"].Direction = ParameterDirection.Input;
InsCmd.Parameters["POptional"].SourceColumn = POptional.ColumnName;
InsCmd.Parameters["POptional"].SourceVersion = DataRowVersion.Current;
InsCmd.Parameters.Add(new MySqlParameter("PEnumID", MySqlDbType.Int64));
InsCmd.Parameters["PEnumID"].Direction = ParameterDirection.Input;
InsCmd.Parameters["PEnumID"].SourceColumn = PEnumID.ColumnName;
InsCmd.Parameters["PEnumID"].SourceVersion = DataRowVersion.Current;
InsCmd.Parameters.Add(new MySqlParameter("PStoredProc", MySqlDbType.VarString));
InsCmd.Parameters["PStoredProc"].Direction = ParameterDirection.Input;
InsCmd.Parameters["PStoredProc"].SourceColumn = PStoredProc.ColumnName;
InsCmd.Parameters["PStoredProc"].SourceVersion = DataRowVersion.Current;
InsCmd.Parameters.Add(new MySqlParameter("rtc", Request["rtc"]));
MyDA.UpdateCommand = UpdCmd;
MyDA.DeleteCommand = DelCmd;
MyDA.InsertCommand = InsCmd;
string strHost = HttpContext.Current.Request.UserHostAddress;
string strUser = HttpContext.Current.User.Identity.Name;
if (strUser.StartsWith("ANALIT\\"))
{
strUser = strUser.Substring(7);
}
MySqlHelper.ExecuteNonQuery(trans.Connection, "set @INHost = ?Host; set @INUser = ?User", new MySqlParameter[] { new MySqlParameter("Host", strHost), new MySqlParameter("User", strUser) });
MyDA.Update(DS, DS.Tables[dtProperties.TableName].TableName);
trans.Commit();
PostData();
}
catch {
trans.Rollback();
throw;
}
finally {
MyCn.Close();
}
btnApply.Visible = dgvProperties.Rows.Count > 0;
}
}