|
Лох Позорный
Разве правильно редактировать и заказ, и справочник одновременно? Желание работодателя, форма пишется под конкретного пользователя.
Лох Позорный
обсуждения я что-то не уяснил - чем же вам вьюха не угодила?
Несколько настроечных параметров, раньше аксессный запрос зачитывал значения с шапки формы и расчетные поля считал на месте (тормозилово), потом сделали на процедуре и все стало летать, только редактируемую таблицу переназначали каждый раз)))
А конструкция источника такая, что вьюха не редактируемая в аксесе, как ни бейся))
Аксесс 2000, все сервис паки, и джет пропатчен.
Источник:
PROCEDURE SuppPriceCompositionP
@SuppPriceID int, @DisLevel money = 1, @CheckExclude int = 0
AS
-- отбирает тело для формы заказов поставщикам
BEGIN
DECLARE @pSumQP money, @pSumQLC money, @pSumQPDisBC money
if @DisLevel<0 or @DisLevel>2
set @DisLevel=1
SELECT dbo.SuppPriceCompositionV.CatEditionID as BCEditionID, dbo.SuppPriceCompositionV.Authors,
dbo.SuppPriceCompositionV.EditionName, dbo.SuppPriceCompositionV.Volume, dbo.SuppPriceCompositionV.Editor,
dbo.SuppPriceCompositionV.SuppPriceID, dbo.SuppPriceCompositionV.AlienID, dbo.SuppPriceCompositionV.ISBN,
dbo.SuppPriceCompositionV.Cost,
dbo.SuppPriceCompositionV.CostUSD, dbo.SuppPriceCompositionV.HC, dbo.SuppPriceCompositionV.HCover, dbo.SuppPriceCompositionV.CatISBN,
dbo.SuppPriceCompositionV.Author, dbo.SuppPriceCompositionV.Title, dbo.SuppPriceCompositionV.FilterString,
dbo.SuppPriceCompositionV.Quantity, dbo.SuppPriceCompositionV.AltQuantity, dbo.SuppPriceCompositionV.RetQuantity,
dbo.SuppPriceCompositionV.Price,
dbo.SuppPriceCompositionV.LastPrice, dbo.SuppPriceCompositionV.LastCost, dbo.SuppPriceCompositionV.LastPriceFillDate,
dbo.SuppPriceCompositionV.EditionID, dbo.SuppPriceCompositionV.TypeBookInPrice, dbo.SuppPriceCompositionV.Comment,
dbo.SuppPriceCompositionV.FoundQuantity, dbo.SuppPriceCompositionV.[Year], dbo.SuppPriceCompositionV.PublishingYear, dbo.SuppPriceCompositionV.Stdt,
dbo.SuppPriceCompositionV.Pages, dbo.SuppPriceCompositionV.CatPages, dbo.SuppPriceCompositionV.CatStdt,
Round(convert(money, ISNULL(dbo.SuppPriceCompositionV.Quantity, 0) * ISNULL(dbo.SuppPriceCompositionV.LastCost, 0)),2) AS QLC,
Round(convert(money, ISNULL(dbo.SuppPriceCompositionV.Quantity, 0) * ISNULL(dbo.SuppPriceCompositionV.Price, 0) * @DisLevel),2) AS QP,
Round(convert(money, ISNULL(dbo.SuppPriceCompositionV.Quantity, 0) * dbo.SuppPriceCompositionV.Cost * @DisLevel
* (1 + dbo.SuppPriceCompositionV.NDSTax)), 2) AS QPDisBC, ROUND(dbo.SuppPriceCompositionV.Cost * (1.0 + dbo.SuppPriceCompositionV.NDSTax), 2) AS CatPrice,
dbo.SuppPriceCompositionV.NDSTax, dbo.SuppPriceCompositionV.Monopoly, dbo.SuppPriceCompositionV.Elitarity, dbo.SuppPriceCompositionV.Running, dbo.SuppPriceCompositionV.AlarmID,
dbo.SuppPriceCompositionV.AlarmQuantity, dbo.BooksAllocationTable.Total, dbo.BooksAllocationTable.Supp,
CASE WHEN [RunQnt] > [Supp] THEN [RunQnt] - [Total] ELSE [Supp] - [Total] END AS Sent, [BooksAllocationTable].Sold,
[BooksAllocationTable].Returned, [BooksAllocationTable].InShop, [BooksAllocationTable].RunQnt, [BooksAllocationTable].DRating,
[BooksAllocationTable].Stability, [BooksAllocationTable].Reserved, Publishers.Publishers, SuppPriceCompositionV.PublishersID,
SuppPriceCompositionV.LastInvDate, SuppPriceCompositionV.LastQuantity, [BooksAllocationTable].LastDenialDate,
[BooksAllocationTable].LastSendDate, [BooksAllocationTable].LastIncomeDate
FROM dbo.SuppPriceCompositionV INNER JOIN
dbo.SuppPrice ON dbo.SuppPriceCompositionV.SuppPriceID = dbo.SuppPrice.SuppPriceID LEFT OUTER JOIN
dbo.BooksAllocationTable ON dbo.SuppPriceCompositionV.EditionID = dbo.BooksAllocationTable.EditionID LEFT OUTER JOIN
dbo.Publishers ON dbo.SuppPriceCompositionV.PublishersID = dbo.Publishers.PublishersID
WHERE dbo.SuppPriceCompositionV.SuppPriceID=@SuppPriceID and
case when @CheckExclude=0 then 1
when @CheckExclude=1 and dbo.SuppPriceCompositionV.TypeBookInPrice<=2 then 1
when @CheckExclude=2 and (ISNull(dbo.SuppPriceCompositionV.Quantity,0)>0 or ISNull(dbo.SuppPriceCompositionV.AltQuantity,0)>0) then 1
when @CheckExclude=3 and dbo.SuppPriceCompositionV.Running=4 then 1 else 0 end = 1
ORDER BY dbo.SuppPriceCompositionV.Authors, dbo.SuppPriceCompositionV.EditionName, dbo.SuppPriceCompositionV.Volume
SuppPriceCompositionV:
SELECT dbo.SuppPriceComposition.*, dbo.BooksCatalog.EditionID AS CatEditionID, dbo.BooksCatalog.Authors, dbo.BooksCatalog.EditionName,
dbo.BooksCatalog.Volume, dbo.BooksCatalog.Editor, dbo.BooksCatalog.HCover, dbo.BooksCatalog.Pages AS CatPages,
dbo.BooksCatalog.Cost AS CatPrice, dbo.BooksCatalog.Cost, dbo.BooksCatalog.NDSTax, dbo.BooksCatalog.ISBN AS CatISBN,
dbo.BooksCatalog.PublishingYear, dbo.BooksCatalog.PublishersID, dbo.BooksCatalog.Stdt AS CatStdt, dbo.BooksCatalog.AlarmQuantity,
dbo.BooksCatalog.AlarmID, dbo.BooksCatalog.CostUSD, dbo.BooksCatalog.Monopoly, dbo.BooksCatalog.Elitarity, dbo.BooksCatalog.Running
FROM dbo.SuppPriceComposition LEFT OUTER JOIN
dbo.BooksCatalog ON dbo.SuppPriceComposition.EditionID = dbo.BooksCatalog.EditionID
Это облегчает постановку вопроса?
|
