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