это хранимка
Добавлено @ 09:59
| Код |
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
ALTER procedure pr_orders_detail_search_cash
@goods_name varchar(1000) = Null
, @goods_marking varchar(300) = Null
, @producer_name varchar(300) = Null
, @material_name varchar(300) = Null
, @TradeMark_Name varchar(300) = Null
, @BarCode varchar(50) = Null
, @ClassID int = Null
, @TypeID int = Null
, @Trade_Mark_id int = Null
, @Specified_id int = Null
, @Series_id int = Null
, @producer_id int = Null
, @material_id int = Null
, @size_id int = Null
, @color_mark_id int = Null
, @color_id int = Null
, @good_id int = Null
, @goods_detail_id int = Null
as
/*
Производит поиск товара на складе для добавления в заказ розницы
Автор: Церковный А.А.
Дата создания: 13/07/2005
*/
declare
@store_id int
select @store_id = ActiveStore
from vwCurrentStore (nolock)
SELECT DISTINCT
GD.goods_detail_id
, GD.goods_id
, G.ID_CLASS
, G.ID_TYPE
, G.NAME_GOODS
, IsNull(G.Series_id, 0) as Series_id
, IsNull(G.Specified_id, 0) as Specified_id
, GD.goods_marking
, GD.gd_producer_id
, TM.TradeMark_Name
, GD.producer_id
, GD.producer_name
, GD.gd_material_id
, GD.material_id
, GD.material_name
, GD.gd_size_id
, GD.size_id
, GD.size_name
, GD.group_id
, GD.group_name
, GD.gd_color_mark_id
, GD.color_mark_id
, GD.color_mark_name
, SH.color_id
, gcol.color_name
, GM.BAR_CODE
, GM.BAR_CODE_EXTERNAL
, IsNull(Round(GP.PriceBase*(1+GP.TAX_NDS), 2), 0) as PriceBase
FROM dbo.storehouse AS SH (nolock)
LEFT JOIN dbo.vw_goods_colors gcol (nolock)
ON (SH.color_id = gcol.color_id)
LEFT JOIN dbo.vw_goods_detail_full_balu AS GD (nolock)
ON (SH.goods_detail_id = GD.goods_detail_id)
LEFT JOIN dbo.vw_goods_measure_full_bs AS GM (nolock)
ON (SH.goods_detail_id = GM.ID_GOODS_DETAIL)
LEFT JOIN dbo.vw_goods AS G (nolock)
ON (GD.goods_id = G.ID_GOODS)
LEFT JOIN dbo.goods_Trade_Mark AS TM (nolock)
ON (G.Trade_Mark_id = TM.TradeMark_ID)
LEFT JOIN dbo.vw_goods_params AS GP (nolock)
ON (SH.goods_detail_id = GP.ID_GOODS_DETAIL)
WHERE SH.amount > 0 AND SH.store_id = @store_id
AND (@ClassID IS NULL OR (@ClassID IS NOT NULL AND @ClassID = G.ID_CLASS ))
AND (@TypeID IS NULL OR (@TypeID IS NOT NULL AND @TypeID = G.ID_TYPE ))
AND (@good_id IS NULL OR (@good_id IS NOT NULL AND @good_id = GD.goods_id ))
AND (@goods_detail_id IS NULL OR (@goods_detail_id IS NOT NULL AND @goods_detail_id = GD.goods_detail_id ))
AND (@Trade_Mark_id IS NULL OR (@Trade_Mark_id IS NOT NULL AND @Trade_Mark_id = G.Trade_Mark_id ))
AND (@Specified_id IS NULL OR (@Specified_id IS NOT NULL AND @Specified_id = G.Specified_id ))
AND (@Series_id IS NULL OR (@Series_id IS NOT NULL AND @Series_id = G.Series_id ))
AND (@producer_id IS NULL OR (@producer_id IS NOT NULL AND @producer_id = GD.producer_id ))
AND (@material_id IS NULL OR (@material_id IS NOT NULL AND @material_id = GD.material_id ))
AND (@size_id IS NULL OR (@size_id IS NOT NULL AND @size_id = GD.size_id ))
AND (@color_mark_id IS NULL OR (@color_mark_id IS NOT NULL AND @color_mark_id = GD.gd_color_mark_id))
AND (@color_id IS NULL OR (@color_id IS NOT NULL AND @color_id = SH.color_id ))
AND (@goods_name IS NULL OR (@goods_name IS NOT NULL AND G.NAME_GOODS LIKE @goods_name ))
AND (@TradeMark_Name IS NULL OR (@TradeMark_Name IS NOT NULL AND TM.TradeMark_Name LIKE @TradeMark_Name))
AND (@goods_marking IS NULL OR (@goods_marking IS NOT NULL AND GD.goods_marking LIKE @goods_marking ))
AND (@producer_name IS NULL OR (@producer_name IS NOT NULL AND GD.producer_name LIKE @producer_name ))
AND (@material_name IS NULL OR (@material_name IS NOT NULL AND GD.material_name LIKE @material_name ))
AND (@BarCode IS NULL OR (@BarCode IS NOT NULL AND (GM.BAR_CODE = @BarCode OR GM.BAR_CODE_EXTERNAL = @BarCode)))
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
|
Добавлено @ 10:06
поле NAME_GOODS |
