--SELECT * FROM shop_order.PurchaseOrder =
INSERT INTO dbo.ValueTextMap
( TableName ,
FieldName ,
Value ,
Text ,
IsDefault ,
DisplayOrder ,
Opened ,
Description
)
VALUES ( 'shop_order.PurchaseOrder' , -- TableName - varchar(50)
'OrderStatus' , -- FieldName - varchar(50)
95 , -- Value - int
N'���ֹر�' , -- Text - nvarchar(100)
0 , -- IsDefault - bit
95 , -- DisplayOrder - tinyint
1 , -- Opened - bit
N'�ɹ�����״̬' -- Description - nvarchar(200)
)
INSERT INTO dbo.ValueTextMap
( TableName ,
FieldName ,
Value ,
Text ,
IsDefault ,
DisplayOrder ,
Opened ,
Description
)
VALUES ( 'shop_order.PurchaseOrderLine' , -- TableName - varchar(50)
'LineStatus' , -- FieldName - varchar(50)
95 , -- Value - int
N'���ֹر�' , -- Text - nvarchar(100)
0 , -- IsDefault - bit
95 , -- DisplayOrder - tinyint
1 , -- Opened - bit
N'�ɹ�������״̬' -- Description - nvarchar(200)
)
INSERT INTO dbo.ValueTextMap
( TableName ,
FieldName ,
Value ,
Text ,
IsDefault ,
DisplayOrder ,
Opened ,
Description
)
VALUES ( 'shop_order.PurchaseOrderLine' , -- TableName - varchar(50)
'LineStatus' , -- FieldName - varchar(50)
100 , -- Value - int
N'�ر�' , -- Text - nvarchar(100)
0 , -- IsDefault - bit
100 , -- DisplayOrder - tinyint
1 , -- Opened - bit
N'�ɹ�������״̬' -- Description - nvarchar(200)
)
ALTER VIEW shop_order.V_PurchaseOrderLine
AS
SELECT pol.PurchaseOrderLineId ,
pol.PurchaseOrderId ,
pol.InventoryType ,
pol.ImageItemId ,
pol.CombtItemId ,
pol.CombtItemNumber ,
pol.CombtItemName ,
pol.InventoryId ,
pol.InventoryNumber ,
pol.InventoryName ,
pol.Spec ,
pol.Length ,
pol.Width ,
pol.Height ,
pol.Uom ,
pol.Quantity ,
ISNULL(cl.ClosedQuantity, 0) ClosedQuantity ,
ISNULL(cl.ClosedTime, 0) ClosedTime ,
pol.RemainQty ,
pol.Price ,
pol.IsUrgent ,
pol.IsAllotype ,
pol.IsSpecial ,
pol.SpecialSpec ,
pol.SpecialFare ,
pol.ApprovedIdea ,
pol.Closed ,
pol.ClosedBy ,
pol.ClosedOn ,
pol.ClosedReason ,
pol.LineStatus ,
vtm.Text AS LineStatusText ,
pol.VersionNumber ,
pol.Description ,
vimi.ThumbnailUrl AS MasterImageUrl ,
vimi.HandledUrl
FROM shop_order.PurchaseOrderLine AS pol
LEFT OUTER JOIN basedata.V_InventoryMasterImage AS vimi ON pol.ImageItemId = vimi.InventoryId
LEFT OUTER JOIN dbo.ValueTextMap AS vtm ON vtm.Value = pol.LineStatus
AND vtm.TableName = 'shop_order.PurchaseOrderLine'
AND vtm.FieldName = 'LineStatus'
LEFT JOIN ( SELECT COUNT(1) ClosedTime ,
VoucherId PurchaseOrderLineId ,
SUM(Quantity) ClosedQuantity
FROM [dbo].[VoucherProcess]
WHERE tableName = 'shop_order.PurchaseOrderLine'
GROUP BY VoucherId
) cl ON cl.PurchaseOrderLineId = pol.PurchaseOrderLineId
GO
ALTER VIEW shop_order.V_SalesItem
AS
SELECT basedata.Inventory.InventoryTypeId AS ItemType ,
basedata.Inventory.InventoryId AS ItemId ,
basedata.Inventory.InventoryNumber AS ItemNumber ,
basedata.Inventory.InventoryName AS ItemName ,
basedata.Inventory.AliasName ,
basedata.Inventory.Spec ,
basedata.Inventory.CostPrice ,
basedata.Inventory.SalesPrice ,
basedata.Inventory.SearchCode ,
basedata.Inventory.InventoryCategoryId ,
basedata.InventoryCategory.CategoryNumber ,
basedata.InventoryCategory.CategoryName ,
basedata.InventoryCategory.DisplayOrder ,
basedata.V_InventoryMasterImage.ThumbnailUrl AS MasterImageUrl ,
basedata.Inventory.IsCustomizable ,
basedata.Inventory.Added ,
basedata.Inventory.Status ,
ZH.InventoryName AS ZHInvName ,
ZH.InventoryNumber AS ZHInvNumber ,
DZ.InventoryName AS DZInvName ,
DZ.InventoryNumber AS DZInvNumber ,
basedata.V_InventoryMasterImage.HandledUrl
FROM basedata.Inventory
INNER JOIN basedata.InventoryCategory ON basedata.Inventory.InventoryCategoryId = basedata.InventoryCategory.InventoryCategoryId
LEFT OUTER JOIN basedata.V_InventoryMasterImage ON basedata.Inventory.InventoryId = basedata.V_InventoryMasterImage.InventoryId
LEFT OUTER JOIN basedata.CombtItemMember AS Co ON Co.CombtItemId = basedata.Inventory.InventoryId
LEFT OUTER JOIN basedata.Inventory AS ZH ON ZH.InventoryId = Co.SuitId
LEFT OUTER JOIN basedata.CustomItemMember AS Cu ON Cu.CustomItemId = basedata.Inventory.InventoryId
LEFT OUTER JOIN basedata.Inventory AS DZ ON DZ.InventoryId = Cu.MemberItemId
GO
ALTER VIEW shop_order.V_CartItem
AS
SELECT shop_order.CartItem.CartItemId ,
shop_order.CartItem.SalesType ,
shop_order.CartItem.OrderType ,
shop_order.CartItem.ShoppingCartId ,
shop_order.CartItem.ImageItemId ,
shop_order.CartItem.CombtItemId ,
shop_order.CartItem.CombtItemNumber ,
shop_order.CartItem.CombtItemName ,
shop_order.CartItem.ItemId ,
basedata.Inventory.InventoryNumber AS ItemNumber ,
basedata.Inventory.InventoryName AS ItemName ,
basedata.Inventory.InventoryTypeId AS ItemType ,
basedata.Inventory.Spec ,
basedata.V_InventoryMasterImage.ThumbnailUrl AS MasterImageUrl ,
shop_order.CartItem.Quantity ,
shop_order.CartItem.Price ,
shop_order.CartItem.IsSpecial ,
shop_order.CartItem.SpecialSpec ,
shop_order.CartItem.SpecialFare ,
shop_order.CartItem.AddedOn ,
shop_order.CartItem.Description ,
basedata.V_InventoryMasterImage.HandledUrl
FROM shop_order.CartItem
INNER JOIN basedata.Inventory ON shop_order.CartItem.ItemId = basedata.Inventory.InventoryId
LEFT OUTER JOIN basedata.V_InventoryMasterImage ON shop_order.CartItem.ImageItemId = basedata.V_InventoryMasterImage.InventoryId
GO
ALTER VIEW shop_order.V_PurchaseOrder
AS
SELECT po.PurchaseOrderId ,
po.ShopId ,
s.ShopNumber ,
s.ShopName ,
s.Telephone ,
po.OrderNumber ,
po.OrderType ,
f_OrderType.Text AS OrderTypeText ,
po.SalesType ,
po.SalesActivityId ,
po.OrderedOn ,
po.RequiredOn ,
po.PreviousVoucherId ,
po.Amount ,
po.ShippingMode ,
f_ShippingMode.Text AS ShippingModeText ,
po.ShipedOn ,
po.ReceiptedAt ,
po.PreparedBy ,
po.PreparedOn ,
po.Approved ,
po.ApprovedBy ,
po.ApprovedOn ,
po.ApprovedResult ,
po.ApprovedIdea ,
po.Closed ,
po.ClosedBy ,
po.ClosedOn ,
po.ClosedReason ,
po.OrderStatus ,
f_OrderStatus.Text AS OrderStatusText ,
f_CreditType.Text AS CreditTypeText ,
po.VersionNumber ,
po.CreatedBy ,
po.CreatedOn ,
po.ModifiedBy ,
po.ModifiedOn ,
po.Description ,
so.OrderNumber AS SalesOrderNumber ,
vl.OtVoucherNumber ,
po.SubmitBy ,
po.SubmitOn ,
vol.ClosedQuantity ,
vol.ClosedTime
FROM shop_order.PurchaseOrder AS po
LEFT OUTER JOIN dbo.f_GetTextMap('shop_order.PurchaseOrder',
'OrderType') AS f_OrderType ON po.OrderType = f_OrderType.Value
LEFT OUTER JOIN dbo.f_GetTextMap('shop_order.PurchaseOrder',
'OrderStatus') AS f_OrderStatus ON po.OrderStatus = f_OrderStatus.Value
LEFT OUTER JOIN dbo.f_GetTextMap('sales.SalesOrder', 'CreType') AS f_CreditType ON po.CreditType = f_CreditType.Value
INNER JOIN basedata.Shop AS s ON s.ShopId = po.ShopId
LEFT OUTER JOIN dbo.f_GetTextMap('shop_order.PurchaseOrder',
'ShippingMode') AS f_ShippingMode ON po.ShippingMode = f_ShippingMode.Value
LEFT JOIN sales.SalesOrder so ON so.PreviousVoucherId = po.PurchaseOrderId
LEFT JOIN [U8].[VoucherLink] vl ON so.SalesOrderId = vl.VoucherId
LEFT JOIN ( SELECT COUNT(1) ClosedTime ,
SUM(Quantity) ClosedQuantity ,
PurchaseOrderId
FROM dbo.V_VoucherProcess
GROUP BY PurchaseOrderId
) vol ON vol.PurchaseOrderId = po.PurchaseOrderId
GO
CREATE VIEW dbo.V_VoucherProcess
AS
SELECT CASE TableName
WHEN 'shop_order.PurchaseOrderLine' THEN pol.PurchaseOrderId
WHEN 'shop_order.PurchaseOrder' THEN VoucherId
END AS PurchaseOrderId ,
vp.VoucherProcessId ,
vp.TableName ,
vp.VoucherId ,
vp.[Content] ,
vp.SubmitBy ,
vp.SubmitOn ,
vp.Result ,
vp.ResultContent ,
vp.Quantity ,
vp.Amount ,
vp.ProcessedBy ,
vp.ProcessedOn ,
vp.Status ,
vp.CreatedBy ,
vp.CreatedOn ,
vp.ModifiedBy ,
vp.ModifiedOn ,
vp.Description
FROM dbo.VoucherProcess AS vp
LEFT OUTER JOIN shop_order.PurchaseOrderLine AS pol ON pol.PurchaseOrderLineId = vp.VoucherId