|
public static ExecuteDataset ( |
||
| connection | ||
| commandText | string | Command to execute |
| return | DataSet | |
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 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 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 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);
}
/// <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));
}