电商版本SQL更新

--电商字段添加

IF NOT EXISTS (SELECT *

               FROM INFORMATION_SCHEMA.COLUMNS

               WHERE TABLE_NAME = 'dPdtStoreBar' AND COLUMN_NAME = 'TrayID')

    ALTER TABLE [dbo].[dPdtStoreBar] ADD [TrayID] int DEFAULT 0 NULL

--锁表

IF Object_Id('sLockBil') IS NULL

    CREATE TABLE [dbo].[sLockBil]

    ([SID] [int] NOT NULL IDENTITY(1, 1),

    [comMode] [int] NULL,

    [BillNo] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,

    [modifiedNum] [int] NOT NULL CONSTRAINT [DF_sLockBil_modifiedNum] DEFAULT((0)),

    [modifiedTime] [datetime] NULL CONSTRAINT [DF_sLockBil_modifiedTime] DEFAULT(GetDate())) ON [PRIMARY]

IF NOT EXISTS (SELECT *

               FROM syscolumns

               WHERE id = Object_Id('sTaoBao_Shop') AND name = 'IsSyncStore')

BEGIN

    ALTER TABLE [dbo].[sTaoBao_Shop] ADD [IsSyncStore] bit DEFAULT 0 NULL

    EXEC sp_addextendedproperty 'MS_Description', N'天猫同步后台库存', 'user', 'dbo', 'table',

        'sTaoBao_Shop', 'column', 'IsSyncStore'

END

IF EXISTS (SELECT *

           FROM syscolumns

           WHERE id = Object_Id('dTradeSkAccountDtl') AND name = 'memoText')

    ALTER TABLE [dbo].[dTradeSkAccountDtl]

    ALTER COLUMN [memoText] varchar(4000) COLLATE Chinese_PRC_CI_AS

ELSE

    ALTER TABLE [dbo].[dTradeSkAccountDtl]

    ADD [memoText] varchar(4000) COLLATE Chinese_PRC_CI_AS

IF NOT EXISTS (SELECT *

               FROM syscolumns

               WHERE id = Object_Id('dPdtStgDtl') AND name = 'OthrMemo')

    ALTER TABLE dPdtStgDtl ADD OthrMemo varchar(1000) NULL

IF NOT EXISTS (SELECT *

               FROM syscolumns

               WHERE id = Object_Id('dPdtStoreBar') AND name = 'OutPosID')

    ALTER TABLE dPdtStoreBar ADD OutPosID int NULL

IF Object_Id('YSYSPdtStoreSalePosition') IS NULL

    EXEC('CREATE   PROCEDURE YSYSPdtStoreSalePosition

(

    @DptID       varchar(1000),

@BeginDate   DateTime,     --提成开始日期

@EndDate     DateTime      --提成结束日期

)

AS

DECLARE

    @SQL1 nVARCHAR(4000),

    @SQL2 nVARCHAR(4000),

    @tmpDate   VARCHAR(300),

    @tmpChkInMan   VARCHAR(300),

    @tmpOprMan    VARCHAR(300),

    @tmpPSalesMan VARCHAR(300)

BEGIN

      SET @SQL1 = ''''

      SET @SQL2 = ''''

      SET @SQL1 = ''SELECT [日期] =StockUpDAY, [仓库编码] =Store_Position,

               [楼层] =Store_Floor, [区域] =Large_Area,

               InNumber [进仓件数], [进仓体积] =InSUMCBM,

               OutNumber [出货件数], [出货体积] =OutSUMCBM

          FROM (SELECT Store_Position, Store_Floor, Large_Area,

                   StockUpDAY =CONVERT(char(6), T1.SaleOutStoreDate, 12),

                   OutNumber = SUM(Number), OutSUMCBM =SUM(Number*P.CBM),

                   InNumber = 0, InSUMCBM =0                             

                FROM dPdtStoreBar T1

                 JOIN cStorePosition S ON T1.OutPosID =S.RelCode

                 LEFT JOIN cPdt P ON T1.PdtName =P.UniqueName AND P.comMode =101

                WHERE T1.SaleOutStoreDate BETWEEN ''''''+CONVERT(VARCHAR(20), @BeginDate, 101)+'' 00:00:00''''

                      AND ''''''+CONVERT(VARCHAR(20), @EndDate, 101)+'' 23:59:59''''

                GROUP BY Store_Position, Store_Floor, Large_Area,

                  CONVERT(char(6), T1.SaleOutStoreDate, 12)

                UNION all

                SELECT Store_Position, Store_Floor, Large_Area,

                   StockUpDAY =CONVERT(char(6), T1.BuyInStoreDate, 12),

                   OutNumber =0, OutSUMCBM =0,

                   InNumber = SUM(Number), InSUMCBM =SUM(Number*P.CBM)                             

                FROM dPdtStoreBar T1

                  JOIN cStorePosition S ON (T1.OutPosID =S.RelCode or T1.InStoreID =S.RelCode)

                 LEFT JOIN cPdt P ON T1.PdtName =P.UniqueName AND P.comMode =101

                WHERE T1.BuyInStoreDate BETWEEN ''''''+CONVERT(VARCHAR(20), @BeginDate, 101)+'' 00:00:00''''

                      AND ''''''+CONVERT(VARCHAR(20), @EndDate, 101)+'' 23:59:59''''

                GROUP BY Store_Position, Store_Floor, Large_Area,

                  CONVERT(char(6), T1.BuyInStoreDate, 12)

                ) AS E ''

           + '' ORDER BY StockUpDAY, Store_Position, Store_Floor, Large_Area DESC''

      EXEC(@SQL1)

END

'   )

IF Object_Id('YSYSPdtStoreSalePostName') IS NULL

    EXEC('CREATE   PROCEDURE YSYSPdtStoreSalePostName

(

    @DptID       varchar(1000),

@BeginDate   DateTime,     --提成开始日期

@EndDate     DateTime      --提成结束日期

)

AS

DECLARE

    @SQL1 nVARCHAR(4000),

    @SQL2 nVARCHAR(4000),

    @tmpDate   VARCHAR(300),

    @tmpChkInMan   VARCHAR(300),

    @tmpOprMan    VARCHAR(300),

    @tmpPSalesMan VARCHAR(300)

BEGIN

      SET @SQL1 = ''''

      SET @SQL2 = ''''

      SET @SQL1 = ''SELECT [日期] =StockUpDAY, Post_Name [物流公司],  

               [总件数] =SumNumBox, [总体积] =SumCBM,  

               [已完成备货|件数] =YWNumBox, [已完成备货|体积] =YWCurCBM,

               [未完成备货|件数] =SumNumBox -ISNULL(YWNumBox,0),

               [未完成备货|体积] =SumCBM -ISNULL(YWCurCBM,0),

               [撤单|件数] = CDNumBox, [撤单|体积] = CDSumCBM

          FROM (SELECT T1.Post_Name, StockUpDAY =CONVERT(char(6), T1.consign_time, 12),

                   SumCBM = SUM(T2.SumCBM), SumNumBox =SUM(SumNumBox),

                   YWCurCBM =SUM(YWCurCBM), YWNumBox =SUM(YWNumBox),

                   CDSumCBM = 0, CDNumBox =0                             

                FROM dTaoBaoPdtOdr T1

                   LEFT JOIN (SELECT mRelCode, SumCBM = Sum(Number* CBM), SumNumBox = SUM(NumBox)

                              FROM dTaoBaoPdtOdrDtl

                              GROUP BY mRelCode) AS T2 ON T1.Relcode = T2.mRelCode

                   LEFT JOIN (SELECT mRelCode, YWCurCBM = Sum(Number* CBM), YWNumBox = SUM(NumBox)

                              FROM dTaoBaoPdtOdrDtl D

                              WHERE NOT EXISTS(SELECT ID FROM dPdtStoreSale S

                                           WHERE D.mRelCode =S.OdrID AND D.SerID =S.OdrSerID)

                              GROUP BY mRelCode) AS T3 ON T1.Relcode = T3.mRelCode

                WHERE T1.consign_time BETWEEN ''''''+CONVERT(VARCHAR(20), @BeginDate, 101)+'' 00:00:00''''

                      AND ''''''+CONVERT(VARCHAR(20), @EndDate, 101)+'' 23:59:59''''

                GROUP BY T1.Post_Name, CONVERT(char(6), T1.consign_time, 12)

                UNION ALL

                SELECT T1.Post_Name, StockUpDAY =CONVERT(char(6), S.tDate, 12),

                   SumCBM = 0, SumNumBox =0, YWCurCBM =0, YWNumBox =0,

                   CDSumCBM = SUM(T2.Number* T2.CBM), CDNumBox =SUM(T2.NumBox)

                FROM dTaoBaoPdtOdr T1

                   JOIN dTaoBaoPdtOdrDtl T2 ON T1.RelCode =T2.mRelCode

                   JOIN dPdtStg S ON T1.RelCode =S.UseStatus  

                   JOIN dPdtStgDtl P ON S.relCode =P.mRelCode AND P.OdrID =T2.mRelCode AND P.OdrSerID =T2.SerID

                WHERE FuncName =''''扫码撤单''''

                  AND S.tDate BETWEEN ''''''+CONVERT(VARCHAR(20), @BeginDate, 101)+'' 00:00:00''''

                               AND ''''''+CONVERT(VARCHAR(20), @EndDate, 101)+'' 23:59:59''''

                  AND S.comMode =301

                GROUP BY T1.Post_Name, CONVERT(char(6), S.tDate, 12)

                ) AS E ''

           + '' ORDER BY StockUpDAY, Post_Name DESC''

      EXEC(@SQL1)

END

'   )

IF Object_Id('YSYSPdtStoreSaleStockUp') IS NULL

    EXEC('CREATE   PROCEDURE YSYSPdtStoreSaleStockUp

(

    @DptID       varchar(1000),

@BeginDate   DateTime,     --提成开始日期

@EndDate     DateTime      --提成结束日期

)

AS

DECLARE

    @SQL1 nVARCHAR(4000),

    @SQL2 nVARCHAR(4000),

    @tmpDate   VARCHAR(300),

    @tmpChkInMan   VARCHAR(300),

    @tmpOprMan    VARCHAR(300),

    @tmpPSalesMan VARCHAR(300)

BEGIN

      SET @SQL1 = ''''

      SET @SQL2 = ''''

      SET @SQL1 = ''SELECT [日期] =StockUpDAY, StockUpMan [拣货人], [批次] =PINO,

               [拣货时长] =HourNum, [未完成体积] =SUMCBM -StockUpCBM, StockUpCBM [已完成体积],

               [待完成数] = Number-StockUpNumber, [已完成数] = StockUpNumber,

               [完成率%] = ROUND(ISNULL(StockUpNumber,0)*100/NULLIF(Number,0),2)

          FROM (SELECT StockUpMan, PINO, StockUpDAY =CONVERT(char(6), T1.CreateDate, 12),

                   HourNum =max(datediff(mi, T1.CreateDate, isnull(T1.Modified,T1.CreateDate))),

                   Number = SUM(Number), StockUpNumber =SUM(StockUpNumber),

                   SUMCBM =SUM(Number*CBM), StockUpCBM =SUM(StockUpNumber*CBM)                             

                FROM dPdtStoreSaleStockUp T1

                WHERE T1.CreateDate BETWEEN ''''''+CONVERT(VARCHAR(20), @BeginDate, 101)+'' 00:00:00''''

                      AND ''''''+CONVERT(VARCHAR(20), @EndDate, 101)+'' 23:59:59''''

                GROUP BY StockUpMan, PINO, CONVERT(char(6), T1.CreateDate, 12)

                ) AS E ''

           + '' ORDER BY StockUpDAY, StockUpMan, PINO DESC''

      EXEC(@SQL1)

END

'   )

IF Object_Id('dPdtStoreSaleStockUp') IS NULL

    EXEC('CREATE TABLE dPdtStoreSaleStockUp

(

[ID] [bigint] NOT NULL IDENTITY(1, 1),

[PINO] [bigint] NULL CONSTRAINT [DF__dPdtStore__Trade__1FC09FB8] DEFAULT ((0)),

[StockUpMan] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL CONSTRAINT [DF__dPdtStore__Stock__2391309C] DEFAULT (''''),

[SID] [bigint] NULL,

[OdrID] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL CONSTRAINT [DF__dPdtStore__OdrID__181F7DF0] DEFAULT (''''),

[OdrSerID] [int] NULL,

[OdrSubSerID] [int] NULL,

[PostName] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL CONSTRAINT [DF__dPdtStore__sPost__229D0C63] DEFAULT (''''),

[BuyerNick] [varchar] (270) COLLATE Chinese_PRC_CI_AS NULL CONSTRAINT [DF__dPdtStore__OldCt__1ECC7B7F] DEFAULT (''''),

[CtrSplrName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL CONSTRAINT [DF__dPdtStore__CtrSp__1913A229] DEFAULT (''''),

[Number] [numeric] (18, 3) NOT NULL CONSTRAINT [DF__dPdtStore__Numbe__1A07C662] DEFAULT ((0)),

[StoreID] [int] NULL CONSTRAINT [DF__dPdtStore__Store__1AFBEA9B] DEFAULT ((0)),

[sPdtID] [int] NULL CONSTRAINT [DF__dPdtStore__sPdtI__21A8E82A] DEFAULT ((0)),

[PdtName] [varchar] (500) COLLATE Chinese_PRC_CI_AS NOT NULL CONSTRAINT [DF__dPdtStore__PdtNa__1BF00ED4] DEFAULT (''''),

[CBM] [numeric] (18, 3) NULL CONSTRAINT [DF__dPdtStoreSa__CBM__1CE4330D] DEFAULT ((0)),

[MZWeight] [numeric] (18, 3) NULL CONSTRAINT [DF__dPdtStore__MZWei__1DD85746] DEFAULT ((0)),

[StockUpMemo] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL,

[StockUpNumber] [numeric] (18, 3) NULL CONSTRAINT [DF__dPdtStore__Stock__248554D5] DEFAULT ((0)),

[CreateDate] [datetime] NULL CONSTRAINT [DF__dPdtStore__Creat__2579790E] DEFAULT (getdate()),

[Modified] [datetime] NULL,

[OprMan] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL

) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_dPdtStoreSaleStockUp_SID] ON [dbo].[dPdtStoreSaleStockUp] ([SID]) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_dPdtStoreSaleStockUp_StockUpMan] ON [dbo].[dPdtStoreSaleStockUp] ([StockUpMan]) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_dPdtStoreSaleStockUp_CreateDate] ON [dbo].[dPdtStoreSaleStockUp] ([CreateDate]) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_dPdtStoreSaleStockUp_PINO] ON [dbo].[dPdtStoreSaleStockUp] ([PINO]) ON [PRIMARY]

EXEC sp_addextendedproperty N''MS_Description'', N''批号按时间格式'', ''SCHEMA'', N''dbo'', ''TABLE'', N''dPdtStoreSaleStockUp'', ''COLUMN'', N''PINO''

EXEC sp_addextendedproperty N''MS_Description'', N''备货人'', ''SCHEMA'', N''dbo'', ''TABLE'', N''dPdtStoreSaleStockUp'', ''COLUMN'', N''StockUpMan''

EXEC sp_addextendedproperty N''MS_Description'', N''货审表的S'', ''SCHEMA'', N''dbo'', ''TABLE'', N''dPdtStoreSaleStockUp'', ''COLUMN'', N''SID''

EXEC sp_addextendedproperty N''MS_Description'', N''选择单据编号'', ''SCHEMA'', N''dbo'', ''TABLE'', N''dPdtStoreSaleStockUp'', ''COLUMN'', N''OdrID''

EXEC sp_addextendedproperty N''MS_Description'', N''选择单据序号'', ''SCHEMA'', N''dbo'', ''TABLE'', N''dPdtStoreSaleStockUp'', ''COLUMN'', N''OdrSerID''

EXEC sp_addextendedproperty N''MS_Description'', N''物流'', ''SCHEMA'', N''dbo'', ''TABLE'', N''dPdtStoreSaleStockUp'', ''COLUMN'', N''PostName''

EXEC sp_addextendedproperty N''MS_Description'', N''买家'', ''SCHEMA'', N''dbo'', ''TABLE'', N''dPdtStoreSaleStockUp'', ''COLUMN'', N''BuyerNick''

EXEC sp_addextendedproperty N''MS_Description'', N''出仓'', ''SCHEMA'', N''dbo'', ''TABLE'', N''dPdtStoreSaleStockUp'', ''COLUMN'', N''CtrSplrName''

EXEC sp_addextendedproperty N''MS_Description'', N''销售产品'', ''SCHEMA'', N''dbo'', ''TABLE'', N''dPdtStoreSaleStockUp'', ''COLUMN'', N''sPdtID''

EXEC sp_addextendedproperty N''MS_Description'', N''产品名称=外购＋型号＋规格＋颜色'', ''SCHEMA'', N''dbo'', ''TABLE'', N''dPdtStoreSaleStockUp'', ''COLUMN'', N''PdtName''

EXEC sp_addextendedproperty N''MS_Description'', N''体积'', ''SCHEMA'', N''dbo'', ''TABLE'', N''dPdtStoreSaleStockUp'', ''COLUMN'', N''CBM''

EXEC sp_addextendedproperty N''MS_Description'', N''毛重'', ''SCHEMA'', N''dbo'', ''TABLE'', N''dPdtStoreSaleStockUp'', ''COLUMN'', N''MZWeight''

EXEC sp_addextendedproperty N''MS_Description'', N''修改'', ''SCHEMA'', N''dbo'', ''TABLE'', N''dPdtStoreSaleStockUp'', ''COLUMN'', N''Modified''

'   )

--库存批号

IF NOT EXISTS (SELECT * FROM syscolumns WHERE id = Object_Id('dPdtStore') AND name = 'PINO')

    ALTER TABLE dPdtStore ADD PINO varchar(50) NULL

IF NOT EXISTS (SELECT * FROM syscolumns WHERE id = Object_Id('dPdtStgDtl') AND name = 'Field1')

    ALTER TABLE dPdtStgDtl ADD Field1 varchar(100) NULL

--瑞福祥材料信息字段

--ALTER TABLE cMtl ALTER COLUMN MtlCodeOfOdr Varchar(200)

--ALTER TABLE cMtl ALTER COLUMN Name Varchar(300)

--ALTER TABLE cMtl ALTER COLUMN MtlSpec Varchar(200)

--ALTER TABLE cMtl ALTER COLUMN MtlUnit Varchar(20)

--ALTER TABLE cMtl ALTER COLUMN UniqueMtlName Varchar(550)

--ALTER TABLE dMtlOdrDtl ALTER COLUMN MtlUnName Varchar(550)

--ALTER TABLE dMtlOdrDtl ALTER COLUMN MtlName Varchar(300)

--ALTER TABLE dMtlOdrDtl ALTER COLUMN MtlSpec Varchar(200)

--ALTER TABLE dMtlOdrDtl ALTER COLUMN MemoSplMtl Varchar(300)

--菜鸟添加自定义模板

IF NOT EXISTS (SELECT 1

               FROM INFORMATION_SCHEMA.COLUMNS

               WHERE EXISTS (SELECT 1 FROM sysobjects WHERE name = TABLE_NAME AND type = 'U')

                 AND TABLE_NAME = 'cEleSurface'

                 AND COLUMN_NAME = 'Custom_Url')

BEGIN

    ALTER TABLE [dbo].[cEleSurface] ADD [Custom_Url] varchar(500) NULL

    EXEC sp_addextendedproperty 'MS_Description', N'自定义模板', 'user', 'dbo', 'table', 'cEleSurface',

        'column', 'Custom_Url'

END

IF NOT EXISTS (SELECT NUMERIC_SCALE

               FROM INFORMATION_SCHEMA.COLUMNS

               WHERE TABLE_NAME = 'dTrade' AND COLUMN_NAME = 'UseStatus')

    ALTER TABLE dTrade ADD UseStatus varchar(100) NULL

IF Object_Id('AdjustReturnGoodsToPdtStore') IS NULL

BEGIN

    EXEC('CREATE PROC AdjustReturnGoodsToPdtStore(

@relcode   Varchar(30),

@oprman    Varchar(50),

@isCheckIn Int)

AS

DECLARE @sInBillNo Varchar(30)

DECLARE @StoreID Int

DECLARE @SplrName Varchar(100)

BEGIN

    --/*

    IF NOT EXISTS (SELECT NUMERIC_SCALE

                   FROM INFORMATION_SCHEMA.COLUMNS

                   WHERE TABLE_NAME=''dTrade'' AND COLUMN_NAME=''UseStatus'')

        ALTER TABLE dTrade ADD UseStatus Varchar(100) NULL

    --*/

    --自动生成进仓单

    IF EXISTS (SELECT relCode

               FROM cCtrSplr WITH(NOLOCK)

               WHERE comMode=120 AND dbo.f_getstr(CtryName, 21, ''|'')=''1'')

    BEGIN

        IF   (@isCheckIn=1)

        BEGIN

            DECLARE mycur CURSOR LOCAL

            FOR

            SELECT StoreID

            FROM dTaoBaoPdtOdrTrackTealWith AS A

            JOIN dTaoBaoPdtOdrTrackDtl AS B

              ON A.RelCode=B.mRelCode

            WHERE A.RelCode=@relcode

              AND BillType=3

              AND IsNull(StoreID, 0)<>0

              AND PartStatus=''返回仓库''

              AND IsNull(B.Field4, '''')=''''

            GROUP BY B.StoreID

            OPEN mycur

            FETCH NEXT FROM mycur

            INTO @StoreID

            WHILE @@fetch_Status=0

            BEGIN

                EXEC GetAllBillNo 30, @sInBillNo OUTPUT, 2

                INSERT INTO dPdtStg(comMode, creDpt, relCode, tDate, OprMan, isValidity,

                    ChkInMan, InOutType, FuncName, CtrSplrName, UseStatus, ChkInDate, memoText)

                SELECT 301, @StoreID, @sInBillNo, GetDate(), SendGoodsMan, 0, '''', 1, ''退货进仓'',

                    A.buyer_nick, A.RelCode, NULL, Left(A.PartsDesc, 2000)

                FROM dTaoBaoPdtOdrTrackTealWith AS A

                WHERE A.RelCode=@relcode AND IsSendGoods=1 AND BillType=3

                INSERT INTO dPdtStgDtl(comMode, Number, mRelCode, SerID, OdrID, OdrSerID,

                    OdrSubSerID, SplrName, PdtSeriesName, PdtKind, PdtSort, PdtCode,

                    PdtSpecName, PdtClrName, PdtUnit, CtrSplrName, BuyCode, BarCode, Price,

                    CurTotal, ID, memoText, PdtName)

                SELECT 301, B.Number, @sInBillNo,

                    Row_Number() OVER (ORDER BY B.PdtName ASC) AS serid, B.mRelCode, B.serID,

                    -1, SplrName, PdtSeriesName, ''退货'', ''常规'', PdtCode, PdtSpecName, PdtClrName,

                    ''件'', ''本公司库存'', A.receiver_name, '''', Price, Price * B.Number,

                    Row_Number() OVER (ORDER BY B.PdtName ASC) AS ID, B.memoText,

                    ''常规~''+PdtCode+''~''+IsNull(PdtSpecName, '''')+''~''+IsNull(PdtClrName, ''常规'')

                FROM dTaoBaoPdtOdrTrackTealWith AS A

                JOIN dTaoBaoPdtOdrTrackDtl AS B

                  ON A.RelCode=B.mRelCode

                WHERE(A.RelCode=@relcode   AND BillType=3)

                 AND B.StoreID=@StoreID

                 AND PartStatus=''返回仓库''

                 AND IsNull(B.Field4, '''')=''''

                --核准

                UPDATE dPdtStg

                SET ChkInMan=@oprman, ChkInDate=GetDate()

                WHERE relCode=@sInBillNo AND comMode=301

                INSERT INTO sOprLog(DptID, relCode, FuncID, FuncName, OprType, OprName)

                SELECT 100, @sInBillNo, 30, ''退货专员-验货完成'', ''售后单号:''+@relcode, @oprman

                UNION

                SELECT 100, @relcode, 30, ''验货进仓'', ''进仓单号:''+@sInBillNo, @oprman

                FETCH NEXT FROM mycur

                INTO @StoreID --逐条读取   

            END

            CLOSE mycur

            DEALLOCATE mycur

        END

        --反核

        ELSE IF EXISTS (SELECT 1 FROM dPdtStg WHERE comMode=301

                                                     AND UseStatus=@relcode

                                                     AND isValidity=1)

        BEGIN

            UPDATE dPdtStg

            SET ChkInMan='''', ChkInDate=NULL

            WHERE UseStatus=@relcode AND isValidity=1

            INSERT INTO sOprLog(DptID, relCode, FuncID, FuncName, OprType, OprName)

            SELECT 100, relCode, 30, ''反核进仓单'', ''退货专员-撤回:''+@relcode, @oprman

            FROM dPdtStg

            WHERE UseStatus=@relcode AND comMode=301

        END

    END

    --退厂维修,不减库存,需补货

    IF EXISTS (SELECT relCode

               FROM cCtrSplr WITH(NOLOCK)

               WHERE comMode=120 AND dbo.f_getstr(CtryName, 24, ''|'')=''1'')

    BEGIN

        IF   (@isCheckIn=1)

        BEGIN

            DECLARE mycur CURSOR LOCAL

            FOR

            SELECT B.SplrName

            FROM dTaoBaoPdtOdrTrackTealWith AS A

            JOIN dTaoBaoPdtOdrTrackDtl AS B

              ON A.RelCode=B.mRelCode

            WHERE (A.RelCode=@relcode AND BillType=3)

              --AND IsNull(StoreID, 0)<>0

              AND PartStatus=''退厂维修''

              AND IsNull(B.Field4, '''')=''''

            GROUP BY B.SplrName

            OPEN mycur

            FETCH NEXT FROM mycur

            INTO @SplrName

            WHILE @@fetch_Status=0

            BEGIN

                EXEC GetAllBillNo 28, @sInBillNo OUTPUT, 2

                INSERT INTO dTrade(creDpt, comMode, relCode, UseStatus, tDate, OprMan,

                    RecMoneyWay, Discount, CtrSplrID, CtrSplrName, ContPhone, SendAddress,

                    MobileNumber, memoText, IsNewCtrSplr, IsFit)

                SELECT 20005, 403, @sInBillNo, @relcode, GetDate(), @oprman, Max(Net), 1, Max(B.relCode),

                    A.SplrName, Max(PhoneNumber), Max(B.Address), Max(PhoneNumber),

                    IsNull(Max(A.memoText), '''')+Char(13)+''退厂维修:''+@relcode, 0, 1

                FROM dTaoBaoPdtOdrTrackDtl AS A

                LEFT JOIN cCtrSplr AS B

                       ON A.SplrName=B.Name AND comMode=118

                WHERE A.mRelCode=@relcode AND SplrName=@SplrName AND PartStatus=''退厂维修''

                GROUP BY SplrName

                INSERT INTO dTradeDtl(comMode, mRelCode, SerID, OdrID, OdrSerID, Number,

                    StoreID, Price, FactTotal, NumBox,PdtSort, PdtCode, PdtSpecName, PdtClrName,

                    PdtName, BagSize, SalesWay)

                SELECT 403, @sInBillNo, Row_Number() OVER (ORDER BY B.PdtName ASC)+100,

                    @relcode, B.serID, B.Number, StoreID, B.Price, Price * B.Number, B.Number,''常规'',

                    PdtCode, PdtSpecName, PdtClrName,

                    ''常规~''+PdtCode+''~''+IsNull(PdtSpecName, '''')+''~''+IsNull(PdtClrName, ''常规''),

                    ''本公司库存'', A.receiver_name

                FROM dTaoBaoPdtOdrTrackTealWith AS A

                JOIN dTaoBaoPdtOdrTrackDtl AS B

                  ON A.RelCode=B.mRelCode

                WHERE (A.RelCode=@relcode AND BillType=3)

                  AND SplrName=@SplrName

                  AND PartStatus=''退厂维修''

                  AND B.PdtSort=''常规''

                  AND IsNull(B.Field4, '''')=''''

                INSERT INTO sOprLog(DptID, relCode, FuncID, FuncName, OprType, OprName)

                SELECT 100, @sInBillNo, 26, ''来自退厂维修'', ''售后单号：''+@relcode, @oprman

                UNION

                SELECT 100, @relcode, 26, ''生成采购退货'', ''退货单号：''+@sInBillNo, @oprman

                FETCH NEXT FROM mycur

                INTO @SplrName

            END

            CLOSE mycur

            DEALLOCATE mycur

        END

        --删除采购退货单

        ELSE IF EXISTS (SELECT 1 FROM dTrade WHERE UseStatus=@relcode

                                                    AND comMode=403

                                                    AND isValidity=0)

        BEGIN

            INSERT INTO sOprLog(DptID, relCode, FuncID, FuncName, OprType, OprName)

            SELECT 100, relCode, 28, ''删除'', ''退货专员-撤回:''+@relcode, @oprman

            FROM dTrade

            WHERE UseStatus=@relcode AND comMode=403 AND isValidity=0

            UNION

            SELECT 100, @relcode, 28, ''退货专员-撤回'', ''删除无效退货单:''+relCode, @oprman

            FROM dTrade

            WHERE UseStatus=@relcode AND comMode=403 AND isValidity=0

            DELETE dTrade WHERE UseStatus=@relcode AND isValidity=0

        END

    END

    --退厂冲款--自动生成采购退货单

    IF EXISTS (SELECT relCode

               FROM cCtrSplr WITH(NOLOCK)

               WHERE comMode=120 AND dbo.f_getstr(CtryName, 34, ''|'')=''1'')

    BEGIN

        IF   (@isCheckIn=1)

        BEGIN

            DECLARE mycur CURSOR LOCAL

            FOR

            SELECT B.SplrName

            FROM dTaoBaoPdtOdrTrackTealWith AS A

            JOIN dTaoBaoPdtOdrTrackDtl AS B

              ON A.RelCode=B.mRelCode

            WHERE (A.RelCode=@relcode AND BillType=3)

              AND IsNull(StoreID, 0)<>0

              AND PartStatus=''退厂冲款''

              AND IsNull(B.Field4, '''')=''''

            GROUP BY B.SplrName

            OPEN mycur

            FETCH NEXT FROM mycur

            INTO @SplrName

            WHILE @@fetch_Status=0

            BEGIN

                EXEC GetAllBillNo 28, @sInBillNo OUTPUT, 2

                INSERT INTO dTrade(creDpt, comMode, relCode, UseStatus, tDate, OprMan,

                    RecMoneyWay, Discount, CtrSplrID, CtrSplrName, ContPhone, SendAddress,

                    MobileNumber, memoText)

                SELECT 20005, 403, @sInBillNo, @relcode, GetDate(), @oprman, Net, 1, B.relCode,

                    A.SplrName, PhoneNumber, B.Address, PhoneNumber,

                    IsNull(A.memoText, '''')+Char(13)+''售后退货冲款:''+@relcode

                FROM dTaoBaoPdtOdrTrackDtl AS A

                LEFT JOIN cCtrSplr AS B

                       ON A.SplrName=B.Name AND comMode=118

                WHERE A.mRelCode=@relcode AND SplrName=@SplrName AND PartStatus=''退厂冲款''

                INSERT INTO dTradeDtl(comMode, mRelCode, SerID, OdrID, OdrSerID, Number,

                    StoreID, Price, FactTotal, NumBox,PdtSort,PdtCode, PdtSpecName, PdtClrName,

                    PdtName, BagSize, SalesWay)

                SELECT 403, @sInBillNo, Row_Number() OVER (ORDER BY B.PdtName ASC)+100,

                    @relcode, B.serID, B.Number, StoreID, B.Price, Price * B.Number, B.Number,''常规'',

                    PdtCode, PdtSpecName, PdtClrName,

                    ''常规~''+PdtCode+''~''+IsNull(PdtSpecName, '''')+''~''+IsNull(PdtClrName, ''常规''),

                    ''本公司库存'', A.receiver_name

                FROM dTaoBaoPdtOdrTrackTealWith AS A

                JOIN dTaoBaoPdtOdrTrackDtl AS B

                  ON A.RelCode=B.mRelCode

                WHERE (A.RelCode=@relcode AND BillType=3)

                  AND SplrName=@SplrName

                  AND PartStatus=''退厂冲款''

                  AND B.PdtSort=''常规''

                  AND IsNull(B.Field4, '''')=''''

                INSERT INTO sOprLog(DptID, relCode, FuncID, FuncName, OprType, OprName)

                SELECT 100, @sInBillNo, 26, ''来自退货冲款'', ''售后单号：''+@relcode, @oprman

                UNION

                SELECT 100, @relcode, 26, ''生成采购退货'', ''退货单号：''+@sInBillNo, @oprman

                FETCH NEXT FROM mycur

                INTO @SplrName

            END

            CLOSE mycur

            DEALLOCATE mycur

        END

        --删除采购退货单

        ELSE IF EXISTS (SELECT 1 FROM dTrade WHERE UseStatus=@relcode

                                                    AND comMode=403

                                                    AND isValidity=0)

        BEGIN

            INSERT INTO sOprLog(DptID, relCode, FuncID, FuncName, OprType, OprName)

            SELECT 100, relCode, 28, ''删除'', ''退货专员-撤回:''+@relcode, @oprman

            FROM dTrade

            WHERE UseStatus=@relcode AND comMode=403 AND isValidity=0

            UNION ALL

            SELECT 100, @relcode, 28, ''退货专员-撤回'', ''删除无效采购退货单:''+relCode, @oprman

            FROM dTrade

            WHERE UseStatus=@relcode AND comMode=403 AND isValidity=0

            DELETE dTrade WHERE UseStatus=@relcode AND isValidity=0

        END

    END

END')

END

--备注策略

INSERT INTO sFunc(IsShow, relCode, FuncGroup, FuncName, TFormName, FKind, PntStr, SqlStr,

    SqlListDtl, WhereStr, OrderByName, TFormMode, TableMode, TableName, TableNameDtl,

    TableNameDtlDtl, MasterSql, DtlSql, DtlDtlSql, IsPrint, PrintNum, ItemNum, CurID, vDate,

    BillSign, DateFormat, SerID, SerMode, ViewPrint, CustomPrint, alCount, BaseFuncID, BaseField,

    FuncHelp, FuncDemo, BaseEditDate, WhereStrOthr, ViewOrder, ViewStatus, SecNode)

SELECT 1, (SELECT Max(relCode)FROM sFunc) + 1, 145, '备注策略', 'TcPdtSellerMemoForm', 1, '卖家备注包含',

    'SELECT relCode AS 系统编码,   isNotUsing AS 停用本次策略, Name AS 卖家备注包含,[开始时间] =State + '' '' +City,

    [结束时间] =District + '' '' +Phone,   T1.ChkInMan [核准],   T1.AthrMan [审批], memoText AS 备注

     FROM   cDefine T1', '', '', 'ORDER BY T1.relCode', 127, 127, 'cDefine', '', '',

    'SELECT *   FROM cDefine', '', '', 1, 0, 0, 3, '', '', 'YYMMDD', '', 0, 0, 0, 0, 0, '0', '', '',

    '2013-08-23 21:16:15', '', 2, 2, 108

WHERE NOT EXISTS (SELECT * FROM sFunc WHERE FuncName = '备注策略')

INSERT INTO sFuncDtl(comMode, EmployeID, FuncID, ControlName, ChnName, WinControlName, OrdID)

SELECT 100, 30001, 20, 'actTracking', '开始跟踪', 'actTracking', 20

WHERE NOT EXISTS (SELECT *

                  FROM sFuncDtl

                  WHERE FuncID = 20 AND comMode = 100 AND ControlName = 'actTracking')

INSERT INTO sFuncDtl(comMode, EmployeID, FuncID, ControlName, ChnName, WinControlName, OrdID)

SELECT 100, 30001, 20, 'actOutTime', '解除超时', 'actOutTime', 21

WHERE NOT EXISTS (SELECT *

                  FROM sFuncDtl

                  WHERE FuncID = 20 AND comMode = 100 AND ControlName = 'actOutTime')

DELETE sFuncDtl

WHERE(comMode = 100) AND (FuncID = 20) AND (ControlName = 'actSendGoodNo')

UPDATE sFuncDtl

SET OrdID = 24

WHERE(comMode = 100) AND (FuncID = 20) AND (ControlName = 'actDataExport') AND OrdID <> 24

--开单语句问题修复

UPDATE sFuncSelectData

SET SelectToEditField = 'LowPrice/relCode/' + Replace(SelectToEditField, 'LowPrice/relCode/', '')

WHERE CharIndex('LowPrice/relCode/', SelectToEditField) > 1

--20200924客服部选物流改过程产品加字段

IF NOT EXISTS (SELECT NUMERIC_SCALE

               FROM INFORMATION_SCHEMA.COLUMNS

               WHERE TABLE_NAME = 'cPdt'

                 AND (COLUMN_NAME = 'StopDate' OR COLUMN_NAME = 'ListeDate'))

BEGIN

    ALTER TABLE [dbo].[cPdt] ADD [StopDate] datetime NULL

    ALTER TABLE [dbo].[cPdt] ADD [ListeDate] datetime NULL

END

--YSYSCalOrderPostNameFee

IF Object_Id('YSYSCalOrderPostNameFee') IS NULL

    EXEC('CREATE   PROCEDURE   YSYSCalOrderPostNameFee   --佛山企业用不能在原始原素

(

    @sIsThree   Int =1,

    @sState     varchar(50),

    @sCity      varchar(50),

    @sDistrict   varchar(50),

    @sTown      varchar(50),  

    @sSumCBM    varchar(50),

    @sSumMZWeight   varchar(50),

    @sTotal_fee    varchar(50)='''',

    @sSumInstallFee   varchar(50) ='''',

    @sPostThreeType   varchar(50) =''''

)

AS

DECLARE

    @tSQL   varchar(8000),

    @tmpThree   varchar(8000),

    @NumError      Int,

    @flag Int

BEGIN

    SET @tmpThree = '' AND (P.IsPostThree =1 or P.IsPostThree =0)''

    IF @sIsThree <>1   --非三包

       SET @tmpThree = '' AND (P.IsPostThree =1 or P.IsPostThree = -1)''

    SET @flag=0

    --省市县区

    IF EXISTS(SELECT ID FROM cPostThreeNameTransportPdt P

              WHERE P.State= @sState AND P.City=@sCity AND P.district=@sDistrict

                 AND P.isUsed <> 0)

       SET @tmpThree =@tmpThree

           + '' AND P.State='''''' + @sState + ''''''''

           + '' AND P.City='''''' + @sCity + ''''''''

           + '' AND P.district='''''' + @sDistrict + ''''''''

    ELSE --省市

    IF EXISTS(SELECT ID FROM cPostThreeNameTransportPdt P

              WHERE P.State= @sState AND P.City=@sCity  

                 AND P.isUsed <> 0)

       SET @tmpThree =@tmpThree

           + '' AND P.State='''''' + @sState + ''''''''

           + '' AND P.City='''''' + @sCity + ''''''''

    ELSE

    IF EXISTS(SELECT ID FROM cPostThreeNameTransportPdt P

              WHERE P.State= @sState   

                 AND P.isUsed <> 0)

       SET @tmpThree =@tmpThree + '' AND P.State='''''' + @sState + ''''''''

    ELSE

        BEGIN

            SET @tmpThree =''''

            SET @flag=1

        END

    --佛山企业用 和购版本

    SET @tSQL ='' SELECT Name [名称], [运价] =isnull(P.CBMPrice, 0), [总体积]= '' + @sSumCBM

          + ''   ,[物流费用] =isnull(P.CBMPrice, 0)*'' + @sSumCBM  

          + ''   ,[提货点]=Name, Filed3 =Phone, RelCode, PostThreePercentAge =isnull(P.PostThreePercentAge, 0), ''

          + ''   PostThreeMinfee =isnull(P.PostThreeMinfee, 0), PostPostion=Name, ''

          + ''   PostThreeAreafee =isnull(P.PostThreeAreafee, 0), Address, Phone, ''

          + ''   PostPercentAge =isnull(P.PostPercentAge, 0),ThrType =''''送货到家并安装'''',''

          + ''   CBMPrice =isnull(P.CBMPrice, 0), PostType = Field5, PercentAge, ''

          + ''   PostExpectDay =isnull(P.PostExpectDay, 0) ''

          + '' FROM cDefine T1   ''

          + CASE WHEN @flag=0 THEN '' Cross '' ELSE '' Outer '' END

          + ''   APPLY(SELECT ''+ CASE WHEN @flag=0 THEN '''' ELSE '' Top 1 '' END

          +'' PostThreePercentAge, PostThreeMinfee, PostThreeAreafee, PostPercentAge,''

          + ''   CBMPrice, PostExpectDay FROM cPostThreeNameTransportPdt P ''

          + ''   WHERE P.mRelCode=T1.relCode AND P.IsUsed<>0 ''

          + @tmpThree

          +'') AS P ''

          + '' WHERE T1.comMode = 105 AND T1.isNotUsing =0   ''

          +'' Order by 物流费用 desc''

     EXEC(@tSQL)

END')

ELSE

    EXEC('ALTER   PROCEDURE   YSYSCalOrderPostNameFee   --佛山企业用不能在原始原素

(

    @sIsThree   Int =1,

    @sState     varchar(50),

    @sCity      varchar(50),

    @sDistrict   varchar(50),

    @sTown      varchar(50),  

    @sSumCBM    varchar(50),

    @sSumMZWeight   varchar(50),

    @sTotal_fee    varchar(50)='''',

    @sSumInstallFee   varchar(50) ='''',

    @sPostThreeType   varchar(50) =''''

)

AS

DECLARE

    @tSQL   varchar(8000),

    @tmpThree   varchar(8000),

    @NumError      Int,

    @flag Int

BEGIN

    SET @tmpThree = '' AND (P.IsPostThree =1 or P.IsPostThree =0)''

    IF @sIsThree <>1   --非三包

       SET @tmpThree = '' AND (P.IsPostThree =1 or P.IsPostThree = -1)''

    SET @flag=0

    --省市县区

    IF EXISTS(SELECT ID FROM cPostThreeNameTransportPdt P

              WHERE P.State= @sState AND P.City=@sCity AND P.district=@sDistrict

                 AND P.isUsed <> 0)

       SET @tmpThree =@tmpThree

           + '' AND P.State='''''' + @sState + ''''''''

           + '' AND P.City='''''' + @sCity + ''''''''

           + '' AND P.district='''''' + @sDistrict + ''''''''

    ELSE --省市

    IF EXISTS(SELECT ID FROM cPostThreeNameTransportPdt P

              WHERE P.State= @sState AND P.City=@sCity  

                 AND P.isUsed <> 0)

       SET @tmpThree =@tmpThree

           + '' AND P.State='''''' + @sState + ''''''''

           + '' AND P.City='''''' + @sCity + ''''''''

    ELSE

    IF EXISTS(SELECT ID FROM cPostThreeNameTransportPdt P

              WHERE P.State= @sState   

                 AND P.isUsed <> 0)

       SET @tmpThree =@tmpThree + '' AND P.State='''''' + @sState + ''''''''

    ELSE

        BEGIN

            SET @tmpThree =''''

            SET @flag=1

        END

    --佛山企业用 和购版本

    SET @tSQL ='' SELECT Name [名称], [运价] =isnull(P.CBMPrice, 0), [总体积]= '' + @sSumCBM

          + ''   ,[物流费用] =isnull(P.CBMPrice, 0)*'' + @sSumCBM  

          + ''   ,[提货点]=Name, Filed3 =Phone, RelCode, PostThreePercentAge =isnull(P.PostThreePercentAge, 0), ''

          + ''   PostThreeMinfee =isnull(P.PostThreeMinfee, 0), PostPostion=Name, ''

          + ''   PostThreeAreafee =isnull(P.PostThreeAreafee, 0), Address, Phone, ''

          + ''   PostPercentAge =isnull(P.PostPercentAge, 0),ThrType =''''送货到家并安装'''',''

          + ''   CBMPrice =isnull(P.CBMPrice, 0), PostType = Field5, PercentAge, ''

          + ''   PostExpectDay =isnull(P.PostExpectDay, 0) ''

          + '' FROM cDefine T1   ''

          + CASE WHEN @flag=0 THEN '' Cross '' ELSE '' Outer '' END

          + ''   APPLY(SELECT ''+ CASE WHEN @flag=0 THEN '''' ELSE '' Top 1 '' END

          +'' PostThreePercentAge, PostThreeMinfee, PostThreeAreafee, PostPercentAge,''

          + ''   CBMPrice, PostExpectDay FROM cPostThreeNameTransportPdt P ''

          + ''   WHERE P.mRelCode=T1.relCode AND P.IsUsed<>0 ''

          + @tmpThree

          +'') AS P ''

          + '' WHERE T1.comMode = 105 AND T1.isNotUsing =0   ''

          +'' Order by 物流费用 desc''

     EXEC(@tSQL)

END ')

--SKU的图片

IF NOT EXISTS (SELECT NUMERIC_SCALE

               FROM INFORMATION_SCHEMA.COLUMNS

               WHERE TABLE_NAME = 'sTaoBao_Sku' AND COLUMN_NAME = 'Url')

    ALTER TABLE [dbo].[sTaoBao_Sku] ADD [Url] varchar(1000) NULL

IF Object_Id('dPdtMarketing') IS NULL

BEGIN

    CREATE TABLE [dbo].[dPdtMarketing]

    ([ID] int IDENTITY(1, 1) NOT NULL,

    [ComMode] int NOT NULL,

    [creDpt] int NOT NULL,

    [relCode] varchar(20) NOT NULL,

    [tDate] datetime NOT NULL,

    [allowDel] bit NULL,

    [isValidity] bit NULL,

    [OprMan] varchar(20) NULL,

    [ChkInMan] varchar(20) NULL,

    [ChkInDate] datetime NULL,

    [AthrMan] varchar(20) NULL,

    [AthrDate] datetime NULL,

    [AthrCause] varchar(50) NULL,

    [cntDate] varchar(10) NULL,

    [memoText] varchar(2000) NULL,

    [PrintNum] int NULL,

    [PrintDate] datetime NULL,

    [handleMan] varchar(20) NULL,

    [tMoney] numeric(18, 2) NULL,

    [ShopNo] int NULL,

    [ShopName] varchar(50) NULL,

    [SaleBeginDate] datetime NULL,

    [SaleEndDate] datetime NULL,

    CONSTRAINT [PK_dPdtMarketing] PRIMARY KEY CLUSTERED([creDpt], [relCode], [ComMode])) ON [PRIMARY]

    EXEC('CREATE TRIGGER [dbo].[tr_dPdtMarketing_Delete] ON [dbo].[dPdtMarketing]

FOR DELETE

AS

DECLARE @oRelCode Varchar(20), @oCreDpt Int, @errormsg Varchar(100)

BEGIN

    DECLARE cu_dPdtMarketing_DELETED CURSOR

    FOR

    SELECT relCode, creDpt FROM DELETED

    OPEN cu_dPdtMarketing_DELETED

    FETCH NEXT FROM cu_dPdtMarketing_DELETED

    INTO @oRelCode, @oCreDpt

    WHILE(@@fetch_Status=0)

    BEGIN

        DELETE dPdtMarketingDtl WHERE mRelCode=@oRelCode

        FETCH NEXT FROM cu_dPdtMarketing_DELETED

        INTO @oRelCode, @oCreDpt

    END

    CLOSE cu_dPdtMarketing_DELETED

    DEALLOCATE cu_dPdtMarketing_DELETED

END')

END

IF Object_Id('dPdtMarketingDtl') IS NULL

BEGIN

    CREATE TABLE [dbo].[dPdtMarketingDtl]

    ([ID] int NULL,

    [ComMode] int NOT NULL,

    [mRelCode] varchar(20) NOT NULL,

    [SerID] int NOT NULL,

    [SID] bigint IDENTITY(1, 1) NOT NULL,

    [memoText] varchar(2000) NULL,

    [ZhuanGui_Price] numeric(18, 4) DEFAULT 0 NULL, --专柜

    [RiXiao_Price] numeric(18, 4) DEFAULT 0 NULL, --日销

    [JuJia_Price] numeric(18, 4) DEFAULT 0 NULL, --聚价 专柜*0.45

    [Tmall_Fee] numeric(18, 4) DEFAULT 0 NULL, --Fee 券类

    [PinLei_Fee] numeric(18, 4) DEFAULT 0 NULL,

    [DianPu_Fee] numeric(18, 4) DEFAULT 0 NULL,

    [HongBao_Fee] numeric(18, 4) DEFAULT 0 NULL,

    [TeQuan_Fee] numeric(18, 4) DEFAULT 0 NULL,

    [DaoShou_Price] numeric(18, 4) DEFAULT 0 NULL, --到手价

    [MaxCost_Price] numeric(18, 4) DEFAULT 0 NULL, --最大成本

    [AvgCost_Price] numeric(18, 4) DEFAULT 0 NULL, --平均成本

    [NumCurStore] numeric(18, 4) DEFAULT 0 NULL,

    [SaleNum] numeric(18, 4) DEFAULT 0 NULL, --小梁

    [CustomNum1] numeric(18, 4) DEFAULT 0 NULL,

    [CustomNum2] numeric(18, 4) DEFAULT 0 NULL,

    [CustomNum3] numeric(18, 4) DEFAULT 0 NULL,

    [CustomNum4] numeric(18, 4) DEFAULT 0 NULL,

    [CustomNum5] numeric(18, 4) DEFAULT 0 NULL,

    [Num_iid] int DEFAULT 0 NULL,

    [Sku_id] int DEFAULT 0 NULL,

    [mOuteriid] varchar(200) NULL,

    [mOuterSkuid] varchar(200) NULL,

    [mSkuid] varchar(500) NULL, --唯一产品

    [PdtUrl] varchar(2000) NULL,

    [Picture1] image NULL,

    [PdtSeriesName] varchar(50) NULL,

    [PdtKind] varchar(50) NULL,

    [PdtName] varchar(610) NOT NULL,

    [PdtSort] varchar(10) NULL,

    [PdtCode] varchar(200) NULL,

    [PdtSpecName] varchar(150) NULL,

    [PdtClrName] varchar(150) NULL,

    [PdtUnit] varchar(6) NULL,

    [Custom1] varchar(200) NULL, --title

    [Custom2] varchar(200) NULL,

    [Custom3] varchar(200) NULL,

    [PackageTxt] varchar(200) NULL,

    CONSTRAINT [PK_dPdtMarketingDtl] PRIMARY KEY CLUSTERED([mRelCode], [SerID], [ComMode])) ON [PRIMARY]

    IF NOT EXISTS (SELECT 1

                   FROM sysindexes

                   WHERE name = 'IX_dPdtMarketingDtl_StoreID_sID'

                     AND Object_Name(id) = 'dPdtMarketingDtl')

        CREATE NONCLUSTERED INDEX [IX_dPdtMarketingDtl_StoreID_sID]

            ON [dbo].[dPdtMarketingDtl]([SID])

            ON [PRIMARY]

END

--产品信息新增两个时间字段

IF NOT EXISTS (SELECT NUMERIC_SCALE

               FROM INFORMATION_SCHEMA.COLUMNS

               WHERE TABLE_NAME = 'cPdt' AND COLUMN_NAME = 'StopDate')

    ALTER TABLE cPdt ADD StopDate datetime NULL

IF NOT EXISTS (SELECT NUMERIC_SCALE

               FROM INFORMATION_SCHEMA.COLUMNS

               WHERE TABLE_NAME = 'cPdt' AND COLUMN_NAME = 'ListeDate')

    ALTER TABLE cPdt ADD ListeDate datetime NULL

--客服部表更

UPDATE sFunc

SET DtlSql = 'SELECT DptName [所属店铺], T1.RelCode [系统单号], create_time [创建时间], pay_time [付款时间], SalesMan [业务员], [买家昵称]=buyer_nick, [收货人]=receiver_name, [手机]=receiver_mobile, [电话]=receiver_phone,[地址] = receiver_state + receiver_city + isnull(receiver_district, ''''),   

    divide_order_fee [分摊后实付],seller_memo [卖家备注],   ColorName [标记名称], ColorMan [标记人], Color [标记颜色]

FROM   dTaoBaoPdtOdr   T1 with(Nolock)

LEFT JOIN (SELECT RelCode, DptName=Name FROM cCtrSplr with(Nolock)   WHERE comMode =122) AS Dpt ON T1.creDpt =Dpt.RelCode

LEFT JOIN (SELECT mRelCode, Sum(total_fee) as totalfee, divide_order_fee =SUM(divide_order_fee)

            FROM TaoBaoOrder   GROUP BY mRelCode) AS T ON T1.Relcode = T.mRelCode ',

    DtlDtlSql = 'SELECT DptName [所属店铺], T1.RelCode [系统单号], SalesMan [业务员],

    [剩余(小时)]= datediff(Hour, getDate(), dateadd(day,0,TrackingDate)),

     Post_SendDate [发货时间], Post_ExpectDay [到货天数],

     [买家昵称]=buyer_nick, [收货人]=receiver_name, [手机]=receiver_mobile,

    [电话]=receiver_phone,[地址] = receiver_state + receiver_city + isnull(receiver_district, ''''),

     TrackingMan [跟踪人], IsTrack [跟踪完成],TrackingStatus [跟踪状态],

     consign_time [完成时间],

     divide_order_fee [分摊后实付], ColorName [标记名称], ColorMan [标记人], Color [标记颜色]

FROM   dTaoBaoPdtOdr   T1 with(Nolock)

  LEFT JOIN (SELECT RelCode, DptName=Name FROM cCtrSplr with(Nolock) WHERE comMode =122) AS Dpt ON T1.creDpt =Dpt.RelCode

  LEFT JOIN (SELECT mRelCode, Sum(total_fee) as totalfee,

                   divide_order_fee =SUM(divide_order_fee)

              FROM TaoBaoOrder

              GROUP BY mRelCode) AS T ON T1.Relcode = T.mRelCode'

WHERE(relCode = 20)

INSERT INTO sFuncDtl(comMode, EmployeID, FuncID, ControlName, ChnName, WinControlName, OrdID)

SELECT 100, 30001, 20, 'actTracking', '开始跟踪', 'actTracking', 20

WHERE NOT EXISTS (SELECT *

                  FROM sFuncDtl

                  WHERE FuncID = 20 AND comMode = 100 AND ControlName = 'actTracking')

INSERT INTO sFuncDtl(comMode, EmployeID, FuncID, ControlName, ChnName, WinControlName, OrdID)

SELECT 100, 30001, 20, 'actOutTime', '解除超时', 'actOutTime', 21

WHERE NOT EXISTS (SELECT *

                  FROM sFuncDtl

                  WHERE FuncID = 20 AND comMode = 100 AND ControlName = 'actOutTime')

DELETE sFuncDtl

WHERE(comMode = 100) AND (FuncID = 20) AND (ControlName = 'actSendGoodNo')

UPDATE sFuncDtl

SET OrdID = 24

WHERE(comMode = 100) AND (FuncID = 20) AND (ControlName = 'actDataExport') AND OrdID <> 24

--开单语句问题修复

UPDATE sFuncSelectData

SET SelectToEditField = 'LowPrice/relCode/' + Replace(SelectToEditField, 'LowPrice/relCode/', '')

WHERE CharIndex('LowPrice/relCode/', SelectToEditField) > 1

--

IF NOT EXISTS (SELECT NUMERIC_SCALE

               FROM INFORMATION_SCHEMA.COLUMNS

               WHERE TABLE_NAME = 'cPdt'

                 AND (COLUMN_NAME = 'StopDate' OR COLUMN_NAME = 'ListeDate'))

BEGIN

    ALTER TABLE [dbo].[cPdt] ADD [StopDate] datetime NULL

    ALTER TABLE [dbo].[cPdt] ADD [ListeDate] datetime NULL

END

IF EXISTS (SELECT 1 FROM sysconstraints WHERE Object_Name(constid) = 'DF_sFunc_FuncHelp')

    ALTER TABLE [dbo].[sFunc] DROP CONSTRAINT [DF_sFunc_FuncHelp]

IF EXISTS (SELECT NUMERIC_SCALE

           FROM INFORMATION_SCHEMA.COLUMNS

           WHERE TABLE_NAME = 'sFunc' AND COLUMN_NAME = 'FuncHelp' AND DATA_TYPE <> 'varchar')

BEGIN

    ALTER TABLE sFunc DROP COLUMN FuncHelp

    ALTER TABLE sFunc ADD FuncHelp varchar(100)

    UPDATE sFunc SET FuncHelp = '' WHERE relCode IN ( 20, 21, 23, 45, 124 )

    PRINT 'sFunc:FuncHelp 更改加密设置'

END

--UPDATE sFunc SET FuncHelp='导出Excel加密邮箱不带附件'

--UPDATE sFunc SET FuncHelp='导出Excel不加密邮箱带附件'

--发票

IF NOT EXISTS (SELECT NUMERIC_SCALE

               FROM INFORMATION_SCHEMA.COLUMNS

               WHERE TABLE_NAME = 'dTaoBaoInvoice' AND COLUMN_NAME = 'InvoiceField1')

    ALTER TABLE [dbo].[dTaoBaoInvoice]

    ADD [InvoiceField1] varchar(200) DEFAULT '' NULL

IF NOT EXISTS (SELECT 1

               FROM INFORMATION_SCHEMA.COLUMNS

               WHERE TABLE_NAME = 'dTaoBaoInvoice' AND COLUMN_NAME = 'InvoiceEmail')

    ALTER TABLE [dbo].[dTaoBaoInvoice]

    ADD [InvoiceEmail] varchar(100) DEFAULT '' NULL

--2020-12-11

IF NOT EXISTS (SELECT 1

               FROM INFORMATION_SCHEMA.COLUMNS

               WHERE TABLE_NAME = 'dTaoBaoInvoice' AND COLUMN_NAME = 'InvoiceNo')

    ALTER TABLE [dbo].[dTaoBaoInvoice] ADD [InvoiceNo] varchar(100) NULL

IF NOT EXISTS (SELECT 1

               FROM INFORMATION_SCHEMA.COLUMNS

               WHERE TABLE_NAME = 'dTaoBaoInvoice' AND COLUMN_NAME = 'InvoiceCode')

    ALTER TABLE [dbo].[dTaoBaoInvoice] ADD [InvoiceCode] varchar(100) NULL

IF NOT EXISTS (SELECT 1

               FROM INFORMATION_SCHEMA.COLUMNS

               WHERE TABLE_NAME = 'dTaoBaoInvoice' AND COLUMN_NAME = 'InvoiceMemo')

    ALTER TABLE [dbo].[dTaoBaoInvoice] ADD [InvoiceMemo] varchar(500) NULL

--订单中心状态

INSERT INTO sFuncCount(comMode, relCode, FuncName, ChnName, Fields, OrdID)

SELECT 15, 45, '', '未锁定', 'AND ISNULL(OprMan, '''') =''''', 1

WHERE NOT EXISTS (SELECT *

                  FROM sFuncCount

                  WHERE comMode = 15 AND relCode = 45 AND ChnName = '未锁定')

INSERT INTO sFuncCount(comMode, relCode, FuncName, ChnName, Fields, OrdID)

SELECT 15, 45, '', '已锁定未客审', 'AND ISNULL(OprMan, '''') <>'''' AND IsChkIn <> 1', 2

WHERE NOT EXISTS (SELECT *

                  FROM sFuncCount

                  WHERE comMode = 15 AND relCode = 45 AND ChnName = '已锁定未客审')

INSERT INTO sFuncCount(comMode, relCode, FuncName, ChnName, Fields, OrdID)

SELECT 15, 45, '', '已客审未货审', 'AND IsChkIn = 1 AND IsGoods <> 1', 3

WHERE NOT EXISTS (SELECT *

                  FROM sFuncCount

                  WHERE comMode = 15 AND relCode = 45 AND ChnName = '已客审未货审')

INSERT INTO sFuncCount(comMode, relCode, FuncName, ChnName, Fields, OrdID)

SELECT 15, 45, '', '货审未财审', 'AND IsGoods = 1 AND ISNULL(Post_ReceiverMan, '''') =''''', 5

WHERE NOT EXISTS (SELECT *

                  FROM sFuncCount

                  WHERE comMode = 15 AND relCode = 45 AND ChnName = '货审未财审')

INSERT INTO sFuncCount(comMode, relCode, FuncName, ChnName, Fields, OrdID)

SELECT 15, 45, '', '货审未打印', 'AND IsGoods = 1 AND IsPrint <> 1', 6

WHERE NOT EXISTS (SELECT *

                  FROM sFuncCount

                  WHERE comMode = 15 AND relCode = 45 AND ChnName = '货审未打印')

INSERT INTO sFuncCount(comMode, relCode, FuncName, ChnName, Fields, OrdID)

SELECT 15, 45, '', '财审未打印', 'AND ISNULL(Post_ReceiverMan, '''') <>''   AND IsPrint <> 1', 7

WHERE NOT EXISTS (SELECT *

                  FROM sFuncCount

                  WHERE comMode = 15 AND relCode = 45 AND ChnName = '财审未打印')

INSERT INTO sFuncCount(comMode, relCode, FuncName, ChnName, Fields, OrdID)

SELECT 15, 45, '', '打印未发货', 'AND IsPrint = 1 AND IsSendGoods <> 1', 8

WHERE NOT EXISTS (SELECT *

                  FROM sFuncCount

                  WHERE comMode = 15 AND relCode = 45 AND ChnName = '打印未发货')

INSERT INTO sFuncCount(comMode, relCode, FuncName, ChnName, Fields, OrdID)

SELECT 15, 45, '', '发货未跟踪', 'AND IsSendGoods = 1 AND IsTracking <> 1', 9

WHERE NOT EXISTS (SELECT *

                  FROM sFuncCount

                  WHERE comMode = 15 AND relCode = 45 AND ChnName = '发货未跟踪')

INSERT INTO sFuncCount(comMode, relCode, FuncName, ChnName, Fields, OrdID)

SELECT 15, 45, '', '已货审未确认', 'AND IsGoods = 1 AND IsAccount <> 1', 4

WHERE NOT EXISTS (SELECT *

                  FROM sFuncCount

                  WHERE comMode = 15 AND relCode = 45 AND ChnName = '已货审未确认')

INSERT INTO sFuncCount(comMode, relCode, FuncName, ChnName, Fields, OrdID)

SELECT 15, 45, '', '已客审未发货', 'AND IsChkIn = 1   AND IsSendGoods <> 1', 3

WHERE NOT EXISTS (SELECT *

                  FROM sFuncCount

                  WHERE comMode = 15 AND relCode = 45 AND ChnName = '已客审未发货')