GoldHoo/doc/调整记录/已更新正式环境/jinhu(20160909).sql

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