--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