[Actual combat scene] Mall-discount event design plan

• 需求：

1. 折扣管理：
   1. Goods support discount activities,Discount activities support many forms;
   2. Event discount requirements：大于0,小于10,A small tree is allowed;
   3. Once the discount campaign is created,Discounts cannot be modified;
2. 促销管理：
   1. You can check the products corresponding to the participating activities according to the discount activities,Displays the original price of the corresponding active product,折扣,减价,折扣后;
   2. Allows merchants to view products eligible for an event,And a product is allowed to participate in at most one event even if the event has expired,After you have to cancel other activities you have participated in,to participate in another activity;
   3. When setting a product to participate in an event,By default, the discount is calculated according to the discount of the activity、减价、折扣后,But it also allows users to manually modify a certain product or change the discount of certain products in batches,or reduced prices.

• Affected business：

　　目前系统中,About shopping adding that business also affects the business place：

1. 商品展示信息：When displaying product information,It is necessary to determine whether the product has participated in the event;
   1. If you participate in the event,Then the event information will be displayed,At the same time, the corresponding discounted price is changed according to the activity information customized for the product,折扣信息.
   2. 这里提到“Event information for product customization”,It is when an activity is added in promotion management,A record is generated,All products participating in the discount campaign have a message like this;
      1. How to judge whether a product has participated in the event,is based on the commodityID从“Event information for product customization”查询,是否记录.
      2. But when a discount activity is deleted,会提示：“If the discount has already been used by other products,Products that participate in the campaign after deletion will cancel the campaign,是否继续？”,也就是说,When a discount event is deleted,将会从“Event information for product customization”to delete all records under the activity.
   3. If you do not attend the event,Then the item will not display event information,没有折扣、Discount price information.
2. 购物车信息：When displaying product information,It is necessary to determine whether the product has participated in the event;
   1. If you participate in the event,Then the event information will be displayed,At the same time, the corresponding discounted price is changed according to the activity information customized for the product,折扣信息.
   2. If you do not attend the event(Only if the product is participating in the event during the event period,and has been turned on),Then the item will not display event information,没有折扣、Discount price information.
   3. The subtotal in an item price is,If there are discounts available(Only if the product is participating in the event during the event period,and has been turned on)Calculated according to the discounted price;
   4. when calculating the total price,If there are discounts available()It needs to be aggregated according to the discounted price;
3. 订单信息：
   1. When generating the total price to be paid,It needs to be the total price in the shopping cart--The total price in the cart is more itemsID,Find the corresponding information（）And then comprehensively calculate the total price;
   2. The total price in the cart,and the total price in the order,不能靠cookie中的信息,很危险,Be sure to query the database to get the total price.
4. 订单商品信息：
   1. Fields that must be saved in the order product information：商品价格,Whether to participate in discount activities,活动名称,折扣,折扣后价格.Why is this information displayed？To track product prices,To prevent others' order information from being untraceable.
   2. 当然这里“Whether to participate in discount activities”字段,Only if the product is participating in the event during the event period,and has been turned on,is identified astrue.
   3. used for redundancy：Once a discount has been cancelled,However, if a person has already participated in the event and has been included in the order item,after canceling the event,At this point there is no way to know when the user submitted,Have you participated in any activities.

• 开发

1. 添加表“活动信息”
   1. 脚本：

      /****** Object:  Table [dbo].[cx_GoodsMarketingCampaignCategory]    Script Date: 05/23/2015 11:17:53 ******/
      SET ANSI_NULLS ON
      GO
      
      SET QUOTED_IDENTIFIER ON
      GO
      
      CREATE TABLE [dbo].[cx_GoodsMarketingCampaignCategory](
          [ID] [int] IDENTITY(1,1) NOT NULL,
          [Title] [nvarchar](64) NOT NULL,
          [Discount] [decimal](9, 1) NOT NULL,
          [IsWipingPoints11] [bit] NOT NULL,
          [IsWipingPoints01] [bit] NOT NULL,
          [BeginDate] [datetime] NOT NULL,
          [EndDate] [datetime] NOT NULL,
          [Enabled] [bit] NOT NULL,
          [SortID] [int] NOT NULL,
          [ModifyDate] [datetime] NOT NULL,
          [CreateDate] [datetime] NOT NULL,
       CONSTRAINT [PK_cx_GoodsMarketingCampaignCategory] PRIMARY KEY CLUSTERED 
      (
          [ID] ASC
      )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
      ) ON [PRIMARY]
      
      GO
      
      ALTER TABLE [dbo].[cx_GoodsMarketingCampaignCategory] ADD  CONSTRAINT [DF_cx_GoodsMarketingCampaignCategory_CreateDate]  DEFAULT (getdate()) FOR [CreateDate]
      GO

2. 添加表“Event information for product customization”
   1. 脚本：

       1 SET ANSI_NULLS ON
       2 GO
       3 
       4 SET QUOTED_IDENTIFIER ON
       5 GO
       6 
       7 CREATE TABLE [dbo].[cx_GoodsMarketingCampaign](
       8     [ID] [int] IDENTITY(1,1) NOT NULL,
       9     [GoodsID] [int] NOT NULL,
      10     [GoodsMarketingCampaignCategoryOID] [int] NOT NULL,
      11     [Discount] [decimal](9, 1) NOT NULL,
      12     [CreateDate] [datetime] NOT NULL,
      13     [ModifyDate] [datetime] NOT NULL,
      14  CONSTRAINT [PK_cx_GoodsMarketingCampaign] PRIMARY KEY CLUSTERED 
      15 (
      16     [ID] ASC
      17 )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
      18 ) ON [PRIMARY]
      19 
      20 GO

   2. Discount,It is the discount set when the product participates in the event,In order to prevent the owner from modifying the price of a product折扣后价格没有发生变化,There is no saving discounted price here、折扣价格,When the interface displays to view the discount of a participating product,Price after discount,Generate this dynamically“折扣后价格”.

       1 Declare @GoodsID Int;
       2 Declare @NowDateTime Datetime;
       3 
       4 Set @GoodsID=1;
       5 Set @NowDateTime=GETDATE();
       6 
       7 -- Get the original price of products that participated in the campaign,活动折扣,折扣后价格
       8 Select 
       9     T11.SellPrice,
      10     T12.Discount,
      11     (Case When T13.IsWipingPoints01=1 Then Round(T11.SellPrice*T12.Discount,1) 
      12         When T13.IsWipingPoints11=1 Then ROUND(T11.SellPrice*T12.Discount,0)
      13     Else Round(T11.SellPrice*T12.Discount,2) End) as RealPrice
      14 From cx_Article as T10 
      15     inner join cx_ArticleAttributeValue as T11 on T10.ID=T11.ArticleID
      16     inner join cx_ArticleGoodsMarketingCampaign as T12 on T10.ID=T12.ArticleID
      17     inner join cx_GoodsMarketingCampaignCategory as T13 on T12.GoodsMarketingCampaignCategoryOID=T13.ID
      18 Where T13.[Enabled]=1
      19     and T13.BeginDate<[email protected] and T13.EndDate>@NowDateTime

   3. How to judge whether the product activity is valid or not,需要关联到“活动信息”表:

       1 Declare @GoodsID Int;
       2 Declare @NowDateTime Datetime;
       3 
       4 Set @GoodsID=1;
       5 Set @NowDateTime=GETDATE();
       6 
       7 -- Determine whether the product has participated in valid activities
       8 Select COUNT(T10.ID)
       9 From cx_Article as T10 
      10     inner join cx_ArticleGoodsMarketingCampaign as T11 on T10.ID=T11.ArticleID
      11     inner join cx_GoodsMarketingCampaignCategory as T12 on T11.GoodsMarketingCampaignCategoryOID=T12.ID
      12 Where T12.[Enabled]=1
      13     and T12.BeginDate<[email protected] and T12.EndDate>@NowDateTime 

3. 修改表“订单商品信息”阿萨德
   1. in the order information form,Redundant fields must be added：Whether to participate in discount activities,活动名称,折扣,折扣后价格

　　到此,The logic has been completely clear.Suddenly feel the power of words is amazing,I thought so hard,Nor was the business sorted out,The text is much clearer after it is written.

Discount event editor

Discount campaign management

Items that can participate in an event

Participated in an event product