public virtual DataSet GetPriceCodeByName(string[] firmNames)
{
var adapter = new MySqlDataAdapter();
adapter.SelectCommand = new MySqlCommand(@"
select p.PriceCode,
p.PriceName,
pd.PriceInfo,
s.Id as FirmCode,
s.Name as FirmName,
rd.ContactInfo,
rd.OperativeInfo,
ifnull(p.MinReq, 0) as MinReq
from usersettings.prices p
join Customers.Suppliers as s ON p.FirmCode = s.Id
join usersettings.pricesdata pd on pd.PriceCode = p.PriceCode
join usersettings.RegionalData rd on p.FirmCode = rd.FirmCode and p.RegionCode = rd.RegionCode
");
if (firmNames != null && firmNames.Length > 0)
{
adapter.SelectCommand.CommandText += " where " + Utils.StringArrayToQuery(firmNames, "s.Name");
}
var data = new DataSet();
With.Connection(c => {
using (InvokeGetPrices(c)) {
adapter.SelectCommand.Connection = c;
adapter.Fill(data, "PriceList");
}
});
return(data);
}
public Stream GetWaybill(uint id)
{
var data = With.Connection(c => c
.Read("select DocumentType, AddressId from Logs.Document_Logs where RowId = ?id", new { id })
.Select(r => new { docType = (DocType)Convert.ToUInt32(r["DocumentType"]), addressId = Convert.ToUInt32(r["addressId"]) })
.FirstOrDefault());
if (data == null)
{
log.WarnFormat("Документ с номером {0} не найден", id);
throw new FaultException(String.Format("Документ с номером {0} не найден", id));
}
var docPath = ConfigurationManager.AppSettings["DocPath"];
var addresses = User.AvaliableAddresses.Where(a => a.Enabled && a.Client == User.Client).Select(a => a.Id);
if (!addresses.Contains(data.addressId))
{
log.WarnFormat("Попытка получить накладную {0} адреса доставки {1} доступа к которому у него нет", id, data.addressId);
throw new FaultException("Доступ запрещен");
}
string path = Path.Combine(docPath, data.addressId.ToString(), data.docType.ToString());
var file = Directory.GetFiles(path, String.Format("{0}_*", id)).FirstOrDefault();
if (String.IsNullOrEmpty(file))
{
log.WarnFormat("На найден файл накладной {0} по пути {1}\\{0}_*", id, path);
throw new FaultException("Файл не найден, файлы накладных хранятся за последнии 3 месяца.");
}
return(File.OpenRead(file));
}
public DataSet GetMinReqSettings()
{
return(With.Connection(c => {
var helper = new MySqlHelper(c);
using (GetPrices(c)) {
return SqlBuilder
.WithCommandText(@"
select a.Address,
p.PriceCode,
p.RegionCode,
ai.ControlMinReq,
if(ai.MinReq > 0, ai.MinReq, p.MinReq) as MinReq
from (Usersettings.Prices p, Customers.Users u)
join Customers.Addresses a on a.ClientId = u.ClientId
join Customers.Intersection i on i.PriceId = p.PriceCode
and i.RegionId = p.RegionCode
and i.ClientId = u.ClientId
and a.LegalEntityId = i.LegalEntityId
join Customers.AddressIntersection ai on ai.IntersectionId = i.Id and a.Id = ai.AddressId")
.AddCriteria("u.Id = ?UserId")
.AddOrder("a.Address")
.ToCommand(helper)
.AddParameter("userId", ServiceContext.User.Id)
.Fill();
}
}));
}
private DataSet GetOrders(Func <MySqlDataAdapter, string> action)
{
var adapter = new MySqlDataAdapter();
adapter.SelectCommand = new MySqlCommand("");
var filter = action(adapter);
adapter.SelectCommand.CommandText = String.Format(@"
SELECT ol.RowId OrderLineId,
ifnull(i.SupplierClientId, '') as ClientCode,
ifnull(ai.SupplierDeliveryId, '') as ClientCode2,
ifnull(i.SupplierPaymentId, '') as ClientCode3,
oh.RowID as OrderID,
cast(oh.PriceDate as char) as PriceDate,
cast(oh.WriteTime as char) as OrderDate,
ifnull(oh.ClientAddition, '') as Comment,
ol.Code as ItemID,
ol.Cost,
ol.Quantity,
if(length(ifnull(ai.SupplierDeliveryId, ''))< 1, concat(cd.Name, '; ', a.Address, '; ',
(select c.contactText
from contacts.contact_groups cg
join contacts.contacts c on cg.Id = c.ContactOwnerId
where cd.ContactGroupOwnerId = cg.ContactGroupOwnerId
and cg.Type = 0
and c.Type = 1
limit 1)), '') as Addition,
if(length(ifnull(i.SupplierClientId, '')) < 1, cd.Name, '') as ClientName,
ifnull(i.PriceMarkup + pd.UpCost + prd.UpCost, 0) PriceMarkup,
c.CategoryId
FROM UserSettings.PricesData pd
JOIN Orders.OrdersHead oh ON pd.PriceCode = oh.PriceCode
JOIN Orders.OrdersList ol ON oh.RowID = ol.OrderID
join Usersettings.pricesregionaldata prd on prd.PriceCode = pd.PriceCode and prd.RegionCode = oh.RegionCode
JOIN Customers.Intersection i ON i.ClientId = oh.ClientCode and i.RegionId = oh.RegionCode and i.PriceId = oh.PriceCode
JOIN Customers.Clients cd ON cd.Id = oh.ClientCode
join Customers.Addresses a on a.Id = oh.AddressId
join Customers.AddressIntersection ai on i.Id = ai.IntersectionId and a.Id = ai.AddressId
JOIN UserSettings.RetClientsSet rcs on rcs.ClientCode = cd.Id
join Catalogs.Products p on p.Id = ol.ProductId
join Catalogs.Catalog c on c.Id = p.CatalogId
WHERE pd.FirmCode in ({1})
and oh.Deleted = 0
and oh.Submited = 1
and rcs.ServiceClient = 0
and rcs.InvisibleOnFirm != 2
{0}
order by OrderDate
", filter, SupplierIds.Implode());
var data = new DataSet();
With.Connection(c => {
adapter.SelectCommand.Connection = c;
adapter.Fill(data);
});
return(data);
}
public virtual DataSet GetCategories()
{
return(With.Connection(c => {
var adapter = new MySqlDataAdapter("select Id, Name from Catalogs.Category", c);
var data = new DataSet();
adapter.Fill(data);
return data;
}));
}
public DataSet GetAddresses()
{
return(With.Connection(c => {
return c.FillDataSet(@"
select a.Id, a.Address as Name
from Customers.Users u
join Customers.UserAddresses ua on u.Id = ua.UserId
join Customers.Addresses a on ua.AddressId = a.Id and a.ClientId = u.ClientId
where a.Enabled = 1 and u.Id = ?userId", new { userId = User.Id });
}));
}
public virtual DataSet GetOrderItems(DateTime begin, DateTime end)
{
if ((DateTime.Now - end) < TimeSpan.FromMinutes(10))
{
throw new Exception("Запрос некорректен: данные за последние 10 минут не предоставляются");
}
var sql = @"
select cast(date(oh.WriteTime) as char) as WriteDate,
cast(time(oh.WriteTime) as char) as WriteTime,
a.Address,
concat(s.Name, ' (', pd.PriceName, ')') as PriceName,
u.Login,
ol.CoreId as OrderId,
ifnull(ifnull(s.Synonym, (select Catalogs.GetProductName(ol.ProductId))), '') as Product,
ifnull(ifnull(sf.Synonym, pr.Name), '') as Producer,
ol.Cost,
ol.Quantity,
ifnull(ol.ProductId, '') as PrepCode,
ifnull(ampc.code, '') as ItemID
from Orders.OrdersHead oh
join Orders.OrdersList ol ON oh.RowID = ol.OrderID
join UserSettings.PricesData pd ON pd.PriceCode = oh.PriceCode
join Customers.Suppliers s on s.Id = pd.FirmCode
join Customers.Clients cd ON cd.Id = oh.ClientCode
join Customers.Addresses a on a.Id = oh.AddressId
join Customers.Users u on u.Id = oh.UserId
left join farm.Synonym s on s.SynonymCode = ol.SynonymCode
left join farm.SynonymFirmCr sf on sf.SynonymFirmCrCode = ol.SynonymFirmCrCode
left join catalogs.Producers pr on pr.Id = ol.CodeFirmCr
left join farm.core0 ampc ON ampc.ProductId = ol.ProductId and ampc.codefirmcr = ol.codefirmcr and ampc.PriceCode = 1864
WHERE oh.Deleted = 0
and oh.ClientCode = ?clientId
and oh.WriteTime >= ?begin
and oh.WriteTime <= ?end
group by ol.RowId
order by oh.WriteTime";
return(With.Connection(c => {
var adapter = new MySqlDataAdapter(sql, c);
var parameters = adapter.SelectCommand.Parameters;
parameters.AddWithValue("clientId", ServiceContext.User.Client.Id);
parameters.AddWithValue("begin", begin);
parameters.AddWithValue("end", end);
parameters.AddWithValue("assortmentPriceId", AssortmentPriceId);
var data = new DataSet();
adapter.Fill(data);
return data;
}));
}
public DataSet GetNamesFromCatalog(string[] name, string[] form, bool offerOnly, int limit, int selStart)
{
DataSet result = null;
With.Connection(c => {
var helper = new MySqlHelper(c);
SqlBuilder builder;
using (GetActivePrices(c)) {
if (offerOnly)
{
builder =
SqlBuilder.WithCommandText(
@"
SELECT distinct c.id as FullCode,
cn.name,
cf.form
FROM Catalogs.Catalog c
JOIN Catalogs.CatalogNames cn on cn.id = c.nameid
JOIN Catalogs.CatalogForms cf on cf.id = c.formid
JOIN Catalogs.Products p on p.CatalogId = c.Id
JOIN Farm.Core0 c0 on c0.ProductId = p.Id
JOIN activeprices ap on ap.PriceCode = c0.PriceCode" );
}
else
{
builder =
SqlBuilder.WithCommandText(
@"
SELECT c.id as FullCode,
cn.name,
cf.form
FROM Catalogs.Catalog c
JOIN Catalogs.CatalogNames cn on cn.id = c.nameid
JOIN Catalogs.CatalogForms cf on cf.id = c.formid" );
}
result = builder
.AddInCriteria("cn.Name", name)
.AddInCriteria("cf.Form", form)
.AddCriteria("c.Hidden = 0")
.Limit(limit, selStart)
.ToCommand(helper)
.Fill();
}
});
return(result);
}
public DataSet GetWaybills(DateTime begin, DateTime end)
{
return(With.Connection(c => {
return c.FillDataSet(@"
select l.RowId as Id,
l.LogTime,
l.FirmCode as SupplierId,
l.AddressId,
l.DocumentType as DocType,
l.DocumentSize as DocSize,
l.FileName
from Logs.Document_Logs l
join Customers.Addresses a on a.Id = l.AddressId
join Customers.UserAddresses ua on a.Id = ua.AddressId
join Customers.Users u on ua.UserId = u.Id and a.ClientId = u.ClientId
where l.LogTime >= ?begin and l.LogTime <= ?end
and l.IsFake = 0
and a.Enabled = 1
and u.Id = ?userId" , new { userId = User.Id, begin, end });
}));
}
public DataSet GetSupplierInfoById(int firmId)
{
return(With.Connection(c => {
var helper = new MySqlHelper(c);
using (GetPrices(c))
{
return SqlBuilder
.WithCommandText(@"
select p.FirmCode SupplierId,
p.PriceCode as PriceCode,
p.PriceName as PriceName,
p.PriceDate as PriceDate,
rd.ContactInfo,
rd.OperativeInfo,
0 as PublicUpCost,
p.DisabledByClient,
s.Name as FirmShortName,
s.FullName as FirmFullName,
rd.SupportPhone as RegionPhone,
(select c.contactText
from contacts.contact_groups cg
join contacts.contacts c on cg.Id = c.ContactOwnerId
where s.ContactGroupOwnerId = cg.ContactGroupOwnerId
and cg.Type = 0
and c.Type = 1
limit 1) as Phone,
s.Address as Address
from prices p
join Customers.Suppliers s on p.firmcode = s.Id
join usersettings.regionaldata rd on rd.firmcode = s.Id and rd.regioncode = p.regioncode" )
.AddInCriteria("p.FirmCode", new int[] { firmId })
.ToCommand(helper)
.Fill();
}
}));
}
public DataSet GetOffers(string[] rangeField,
string[] rangeValue,
bool newEar,
string[] sortField,
string[] sortOrder,
int limit,
int selStart)
{
var settings = Session.Load <OrderRules>(User.Client.Id);
return(With.Connection(c => {
var helper = new MySqlHelper(c);
var columnNameMapping = new Dictionary <string, string>(StringComparer.InvariantCultureIgnoreCase)
{
{ "offerid", "offers.Id" },
{ "pricecode", "offers.PriceCode" },
{ "fullcode", "p.CatalogId" },
{ "name", "s.synonym" },
{ "crname", "sfc.synonym" },
{ "code", "c.Code" },
{ "codecr", "c.CodeCr" },
{ "unit", "c.Unit" },
{ "volume", "c.Volume" },
{ "quantity", "c.Quantity" },
{ "note", "c.Note" },
{ "period", "c.Period" },
{ "doc", "c.Doc" },
{ "junk", "c.Junk" },
{ "cost", "offers.Cost" },
{ "SupplierId", "ap.FirmCode" },
};
ValidateFieldNames(columnNameMapping, rangeField);
ValidateFieldNames(columnNameMapping, sortField);
ValidateSortDirection(sortOrder);
var groupedValues = GroupValues(rangeField, rangeValue);
if (rangeField != null &&
(rangeValue == null ||
rangeField.Length != rangeValue.Length))
{
throw new Exception("Количество полей для фильтрации не совпадает с количеством значение по которым производится фильтрация");
}
using (GetOffers(c)) {
var builder = SqlBuilder
.WithCommandText(@"
SELECT offers.Id as OfferId,
ap.FirmCode SupplierId,
offers.PriceCode,
p.CatalogId as FullCode,
c.Code,
c.CodeCr,
s.synonym as Name,
sfc.synonym as CrName,
c.Unit,
c.Volume,
c.Quantity,
c.Note,
c.Period,
c.Doc,
c.Junk,
c.RequestRatio,
c.OrderCost MinOrderSum,
c.MinOrderCount,
max(cc.Cost) RegistryCost,
c.VitallyImportant,
offers.Cost,
cl.Code as ClientCatalogId
FROM core as offers
JOIN farm.core0 as c on c.id = offers.id
join farm.Synonym s on c.synonymcode = s.synonymcode
join usersettings.ActivePrices ap on ap.PriceCode = c.PriceCode
left join farm.SynonymFirmCr sfc on sfc.SynonymFirmCrCode = c.synonymfirmcrcode
join Catalogs.Products p on p.Id = c.ProductId
left join farm.Core0 rp on rp.PriceCode = 4863 and rp.ProductId = c.ProductId and rp.CodeFirmCr <=> c.CodeFirmCr
left join farm.CoreCosts cc on cc.Core_id = rp.id and cc.PC_CostCode = 8317
left join farm.Core0 cl on cl.ProductId = c.ProductId and cl.CodeFirmCr <=> c.CodeFirmCr and cl.PriceCode = ?mapPriceId" );
foreach (var pair in groupedValues)
{
builder.AddInCriteria(columnNameMapping[pair.Key], pair.Value);
}
return builder
.Append("group by c.Id")
.AddOrderMultiColumn(sortField, sortOrder)
.Limit(limit, selStart)
.ToCommand(helper)
.AddParameter("mapPriceId", settings.CatalogMapPriceId)
.Fill();
}
}));
}
public virtual DataSet GetPrices(bool onlyLeader,
bool newEar,
string[] rangeField,
string[] rangeValue,
string[] sortField,
string[] sortOrder,
uint limit,
uint selStart)
{
//словарь для валидации и трансляции имен полей для клиента в имена полей для использования в запросе
var validRequestFields = new Dictionary <string, string>(StringComparer.CurrentCultureIgnoreCase)
{
{ "OrderID", "c.id" },
{ "SalerCode", "c.Code" },
{ "SalerName", "s.Name" },
{ "ItemID", "ampc.Code" },
{ "OriginalCR", "sfc.Synonym" },
{ "OriginalName", "s.Synonym" },
{ "PriceCode", "ap.PriceCode" },
{ "PrepCode", "c.ProductId" },
{ "MnnId", "cm.MnnId" }
};
var validSortFields = new List <string> {
"OrderID",
"SalerCode",
"CreaterCode",
"ItemID",
"OriginalName",
"OriginalCR",
"Unit",
"Volume",
"Quantity",
"Note",
"Period",
"Doc",
"Junk",
"Upcost",
"Cost",
"PriceCode",
"SalerName",
"PriceDate",
"PrepCode",
"OrderCode1",
"OrderCode2"
};
//проверка входящих параметров
if (rangeValue == null || rangeField == null ||
rangeField.Length != rangeValue.Length)
{
throw new ArgumentException("Входящие параметры не валидны");
}
//TODO: в принципе в этой проверке нет нужды если будет неверное название поля
//то будет Exception на этапе трансляции
//проверка имен полей для фильтрации
foreach (var fieldName in rangeField)
{
if (!validRequestFields.ContainsKey(fieldName))
{
throw new ArgumentException(String.Format("По полю {0} не может производиться фильтрация", fieldName), fieldName);
}
}
//проверка имен полей для сортировки
if (sortField != null)
{
foreach (var fieldName in sortField)
{
if (!validSortFields.Exists(value => String.Compare(fieldName, value, true) == 0))
{
throw new ArgumentException(String.Format("По поляю {0} не может производиться сортировка", fieldName), fieldName);
}
}
}
//проверка направлений сортировки
if (sortOrder != null)
{
foreach (var direction in sortOrder)
{
if (!(String.Compare(direction, "Asc", true) == 0 || String.Compare(direction, "Desc", true) == 0))
{
throw new ArgumentException(
String.Format("Не допустимое значение направления сортровки {0}. Допустимые значение \"Asc\" и \"Desc\"",
direction), direction);
}
}
}
//словарь хранящий имена фильтруемых полей и значения фильтрации
// | |
// имя поля список значений для него
var filtedField = new Dictionary <string, List <string> >();
//разбирается входящие параметры args.RangeField и args.RangeValue и одновременно клиентские имена
//транслируются во внутренние
for (var i = 0; i < rangeField.Length; i++)
{
//преобразовываем клиентские названия полей во внутренние
var innerFieldName = validRequestFields[rangeField[i]];
//если в словаре не такого поля
if (!filtedField.ContainsKey(innerFieldName))
{
//то добавляем его и создаем массив для хранения его значений
filtedField.Add(innerFieldName, new List <string>());
}
//добавляем значение для фильтрации
filtedField[innerFieldName].Add(rangeValue[i]);
}
if (filtedField.Count == 0)
{
throw new SoapException("Нет критериев для фильтрации", new XmlQualifiedName("0"));
}
var data = new DataSet();
var predicatBlock = "";
if (newEar)
{
predicatBlock += " ampc.id is null ";
}
foreach (var fieldName in filtedField.Keys)
{
if (predicatBlock != "")
{
predicatBlock += " and ";
}
predicatBlock += Utils.StringArrayToQuery(filtedField[fieldName], fieldName);
}
Func <MySqlConnection, DisposibleAction> prerequirements;
string command;
if (!onlyLeader)
{
prerequirements = c => InvokeGetActivePrices(c);
command = @"
drop temporary table if exists offers;
create temporary table offers engine memory
SELECT c.id OrderID,
ifnull(c.Code, '') SalerCode,
ifnull(c.CodeCr, '') CreaterCode,
ifnull(ampc.code, '') ItemID,
s.synonym OriginalName,
ifnull(sfc.synonym, '') OriginalCr,
ifnull(c.Unit, '') Unit,
ifnull(c.Volume, '') Volume,
ifnull(c.Quantity, '') Quantity,
ifnull(c.Note, '') Note,
ifnull(c.Period, '') Period,
ifnull(c.Doc, '') Doc,
c.Junk as Junk,
ifnull(c.RequestRatio, 0) RequestRatio,
ifnull(c.OrderCost, 0) MinOrderSum,
ifnull(c.MinOrderCount, 0) MinOrderCount,
0 UpCost,
ap.PriceCode PriceCode,
s.Name SalerName,
ap.PriceDate,
c.ProductId as PrepCode,
c.synonymcode OrderCode1,
c.synonymfirmcrcode OrderCode2,
if(if(round(cc.Cost*ap.UpCost,2)<c.MinBoundCost, c.MinBoundCost, round(cc.Cost*ap.UpCost,2))>c.MaxBoundCost, c.MaxBoundCost, if(round(cc.Cost*ap.UpCost,2)<c.MinBoundCost, c.MinBoundCost, round(cc.Cost*ap.UpCost,2))) as Cost
FROM farm.core0 c
JOIN ActivePrices ap on c.PriceCode = ap.PriceCode
JOIN farm.CoreCosts cc on cc.Core_Id = c.Id and cc.PC_CostCode = ap.CostCode
JOIN Customers.Suppliers as s ON ap.FirmCode = s.Id
JOIN farm.synonym as s ON s.SynonymCode = c.SynonymCode
JOIN Catalogs.Products as p ON p.Id = c.ProductId
LEFT JOIN Catalogs.catalog cl on p.CatalogId = cl.Id
LEFT JOIN Catalogs.Catalognames cm on cl.NameId = cm.Id
LEFT JOIN farm.SynonymFirmCr as sfc ON sfc.SynonymFirmCrCode = c.SynonymFirmCrCode
LEFT JOIN farm.core0 ampc ON ampc.ProductId = c.ProductId and ampc.codefirmcr = c.codefirmcr and ampc.PriceCode = 1864
WHERE ap.pricecode != 2647
";
if (predicatBlock != "")
{
command += " and " + predicatBlock;
}
command += @"; select * from offers ";
}
else
{
prerequirements = c => InvokeGetOffers(c);
command = @"
SELECT c.id OrderID,
ifnull(c.Code, '') SalerCode,
ifnull(c.CodeCr, '') CreaterCode,
ifnull(ampc.code, '') ItemID,
s.synonym OriginalName,
ifnull(sfc.synonym, '') OriginalCr,
ifnull(c.Unit, '') Unit,
ifnull(c.Volume, '') Volume,
ifnull(c.Quantity, '') Quantity,
ifnull(c.Note, '') Note,
ifnull(c.Period, '') Period,
ifnull(c.Doc, '') Doc,
c.Junk as Junk,
ifnull(c.RequestRatio, 0) RequestRatio,
ifnull(c.OrderCost, 0) MinOrderSum,
ifnull(c.MinOrderCount, 0) MinOrderCount,
0 UpCost,
ap.pricecode PriceCode,
s.Name SalerName,
ap.PriceDate,
p.Id PrepCode,
c.synonymcode OrderCode1,
c.synonymfirmcrcode OrderCode2,
offers.MinCost as Cost
FROM UserSettings.MinCosts as offers
JOIN farm.core0 as c on c.id = offers.id
JOIN UserSettings.activeprices as ap ON ap.PriceCode = offers.PriceCode
JOIN Customers.Suppliers as s ON ap.FirmCode = s.Id
JOIN farm.synonym as s ON s.SynonymCode = c.SynonymCode
JOIN Catalogs.Products as p ON p.Id = c.ProductId
LEFT JOIN Catalogs.catalog cl on p.CatalogId = cl.Id
LEFT JOIN Catalogs.Catalognames cm on cl.NameId = cm.Id
LEFT JOIN farm.SynonymFirmCr as sfc ON sfc.SynonymFirmCrCode = c.SynonymFirmCrCode
LEFT JOIN farm.core0 ampc ON ampc.ProductId = c.ProductId and ampc.codefirmcr = c.codefirmcr and ampc.PriceCode = 1864
";
if (predicatBlock != "")
{
command += " where " + predicatBlock;
}
}
//группировка нужна т.к. в асортиментном прайсе амп может быть несколько записей
//соответствующие с одинаковым ProductId и CodeFirmCr но смысла в этом нет
//ни какого они просто не нужны
command += " GROUP BY OrderID";
command += Utils.FormatOrderBlock(sortField, sortOrder);
command += Utils.GetLimitString(selStart, limit);
With.Connection(c => {
using (prerequirements(c)) {
var dataAdapter = new MySqlDataAdapter(command, c);
dataAdapter.Fill(data, "PriceList");
}
});
return(data);
}
public virtual DataSet GetNameFromCatalog(string[] name,
string[] form,
bool newEar,
bool offerOnly,
uint[] priceId,
uint limit,
uint selStart,
string[] mnn,
string[] property)
{
var data = new DataSet();
int i;
var searchByApmCode = name != null && name.Length > 0 && int.TryParse(name[0], out i);
var query = new Query().Select(@"
p.id as PrepCode,
cn.Name,
cf.Form,
cast(ifnull(group_concat(distinct pv.Value ORDER BY prop.PropertyName, pv.Value SEPARATOR ', '), '') as CHAR) as Properties,
c.VitallyImportant,
ifnull(m.Mnn, '') as Mnn,
ifnull(m.Id, '') as MnnId")
.From(@"
Catalogs.Catalog c
JOIN Catalogs.CatalogNames cn on cn.id = c.nameid
left join Catalogs.Mnn m on cn.MnnId = m.Id
JOIN Catalogs.CatalogForms cf on cf.id = c.formid
JOIN Catalogs.Products p on p.CatalogId = c.Id
LEFT JOIN Catalogs.ProductProperties pp on pp.ProductId = p.Id
LEFT JOIN Catalogs.PropertyValues pv on pv.id = pp.PropertyValueId
LEFT JOIN Catalogs.Properties prop on prop.Id = pv.PropertyId")
.Where("p.Hidden = 0", "c.Hidden = 0")
.Where(Utils.StringArrayToQuery(form, "cf.Form"))
.Where(Utils.StringArrayToQueryNull(mnn, "m.Mnn"))
.Where(Utils.StringArrayToQueryNull(property, "pv.Value"))
.GroupBy("p.Id")
.OrderBy("cn.Name, cf.Form")
.Limit(Utils.GetLimitString(selStart, limit).ToLower().Replace("limit ", ""));
if (offerOnly)
{
query.Join("JOIN Farm.Core0 c0 on c0.ProductId = p.Id", "JOIN activeprices ap on ap.PriceCode = c0.PriceCode");
if (newEar || searchByApmCode)
{
query.Join("LEFT JOIN farm.core0 ampc on ampc.ProductId = p.Id and ampc.codefirmcr = c0.codefirmcr and ampc.PriceCode = 1864");
}
if (newEar)
{
query.Where("ampc.id is null");
}
if (searchByApmCode)
{
query.Where(Utils.StringArrayToQuery(name, "ampc.code"));
}
}
if (!searchByApmCode)
{
query.Where(Utils.StringArrayToQuery(name, "cn.Name"));
}
if (offerOnly && priceId != null && !(priceId.Length == 1 && priceId[0] == 0))
{
query.Where(Utils.StringArrayToQuery(priceId, "ap.pricecode"));
}
With.Connection(c => {
if (offerOnly)
{
using (InvokeGetActivePrices(c))
query.Table(data, "Catalog", c);
}
else
{
query.Table(data, "Catalog", c);
}
});
return(data);
}
private DataSet BuildOrderReport(IList <ToOrder> toOrders)
{
var result = new DataSet();
var table = result.Tables.Add("Prices");
table.Columns.Add("OrderID", typeof(long));
table.Columns.Add("OriginalOrderID", typeof(ulong));
var selectOffer = @"
SELECT c.id OrderID,
ifnull(c.Code, '') SalerCode,
ifnull(c.CodeCr, '') CreaterCode,
ifnull(ampc.code, '') ItemID,
s.synonym OriginalName,
ifnull(scr.synonym, '') OriginalCr,
ifnull(c.Unit, '') Unit,
ifnull(c.Volume, '') Volume,
ifnull(c.Quantity, '') Quantity,
ifnull(c.Note, '') Note,
ifnull(c.Period, '') Period,
ifnull(c.Doc, '') Doc,
c.Junk as Junk,
ifnull(c.RequestRatio, 0) RequestRatio,
ifnull(c.OrderCost, 0) MinOrderSum,
ifnull(c.MinOrderCount, 0) MinOrderCount,
0 UpCost,
if(if(round(cc.Cost*ap.UpCost,2)<c.MinBoundCost, c.MinBoundCost, round(cc.Cost*ap.UpCost,2))>c.MaxBoundCost,c.MaxBoundCost, if(round(cc.Cost*ap.UpCost,2)<c.MinBoundCost, c.MinBoundCost, round(cc.Cost*ap.UpCost,2))) Cost,
ap.pricecode SalerID,
s.Name SalerName,
ap.PriceDate,
c.ProductId PrepCode,
c.synonymcode OrderCode1,
c.synonymfirmcrcode OrderCode2
FROM farm.core0 c
JOIN ActivePrices ap on c.PriceCode = ap.PriceCode
JOIN usersettings.PricesData pd on pd.PriceCode = ap.PriceCode
JOIN farm.CoreCosts cc on cc.Core_Id = c.Id and cc.PC_CostCode = ap.CostCode
JOIN Customers.Suppliers as s ON ap.FirmCode = s.Id
JOIN farm.synonym s on s.PriceCode = ifnull(pd.parentsynonym, ap.pricecode) and s.SynonymCode = c.SynonymCode
LEFT JOIN farm.core0 ampc ON ampc.ProductId = c.ProductId and ampc.codefirmcr = c.codefirmcr and ampc.PriceCode = 1864
LEFT JOIN farm.synonymfirmcr scr ON scr.PriceCode = ifnull(pd.ParentSynonym, ap.pricecode) and c.synonymfirmcrcode = scr.synonymfirmcrcode
WHERE c.SynonymCode = ?SynonymCode
and c.SynonymFirmCrCode = ?SynonymFirmCrCode
and c.Junk = ?Junk;" ;
With.Connection(c => {
using (InvokeGetActivePrices(c)) {
result.Tables[0].Columns.Add("OrderLineId", typeof(string));
foreach (var toOrder in toOrders)
{
var row = result.Tables[0].NewRow();
row["OriginalOrderID"] = toOrder.OfferId;
result.Tables[0].Rows.Add(row);
if (toOrder.OrderItem != null)
{
row["OrderID"] = toOrder.OrderItem.Order.RowId;
row["OrderLineId"] = toOrder.OrderItem.RowId.ToString();
continue;
}
var data = new DataSet();
var dataAdapter = new MySqlDataAdapter(selectOffer, c);
dataAdapter.SelectCommand.Parameters.AddWithValue("?SynonymCode", toOrder.OrderCode1);
dataAdapter.SelectCommand.Parameters.AddWithValue("?SynonymFirmCrCode", toOrder.OrderCode2);
dataAdapter.SelectCommand.Parameters.AddWithValue("?Junk", toOrder.Junk);
dataAdapter.Fill(data);
if (data.Tables[0].Rows.Count == 0)
{
row["OrderID"] = -1;
continue;
}
data.Tables[0].Rows[0].CopyTo(row);
}
}
});
return(result);
}
