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