全站搜索
常见问题分类
 

工厂版

 

电商版

 

通—用

工厂版2016版本升级到2021版本SQL语句

34
发表时间:2020-06-09 17:41

--1,把约束重命名,方便drop

IF EXISTS (SELECT * FROM sysobjects WHERE name = 'ReNameDefaultConstraints' AND type = 'P')

BEGIN

    DROP PROCEDURE ReNameDefaultConstraints

END


EXEC(N'--将约束名设置为DF_TableName_ColumName 支持SQL2000

CREATE   PROCEDURE ReNameDefaultConstraints

AS

BEGIN  

DECLARE @tableName VARCHAR(200)

DECLARE @columname sysname

DECLARE @newtype sysname

DECLARE @default VARCHAR(20)

DECLARE @DFName VARCHAR(256)


DECLARE mycur CURSOR LOCAL

FOR

SELECT   tableName=Object_Name(id), columname=Col_Name(id, colid),DFname=Object_Name(constid),

dfault=B.COLUMN_DEFAULT

FROM sysconstraints A

LEFT JOIN information_schema.columns B ON B.TABLE_NAME=Object_Name(id) AND B.COLUMN_NAME=Col_Name(id, colid)

WHERE colid>0


OPEN mycur


FETCH NEXT FROM mycur

INTO @tableName, @columname, @DFName, @default


WHILE @@fetch_Status=0

BEGIN

    EXEC(''ALTER TABLE ''+@tableName+'' DROP CONSTRAINT [''+@DFName+'']'')


    --PRINT(''ALTER TABLE ''+@tableName+'' DROP CONSTRAINT [''+@DFName+'']'')


    SET @DFName=''DF_''+@tableName+''_''+@columname

    EXEC(''ALTER TABLE ''+@tableName+'' ADD CONSTRAINT [''+@DFName+''] DEFAULT ''+@default+'' FOR [''+@columname+'']'')


    --PRINT(''ALTER TABLE ''+@tableName+'' ADD CONSTRAINT [''+@DFName+''] DEFAULT ''+@default+'' FOR ['' +@columname+'']'')


    FETCH NEXT FROM mycur

    INTO @tableName, @columname, @DFName, @default --逐条读取   

END


CLOSE mycur

DEALLOCATE mycur


END ')


EXEC('EXEC ReNameDefaultConstraints')

PRINT('重命名约束OK')


--锁表

IF Object_Id('sLockBil') IS NULL

BEGIN

    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]

    PRINT('新增:sLockBil表 ')

END

IF Object_Id('sIPPort') IS NULL

    EXEC('CREATE TABLE [dbo].[sIPPort](

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

[IPAddress] [varchar](20) NULL,

[IPPort] [int] NULL,

[UsedNum] [int] NULL,

[NewUsedDate] [datetime] NULL,

[CreateDate] [datetime] NULL

) ON [PRIMARY]



ALTER TABLE [dbo].[sIPPort] ADD   CONSTRAINT [DF_sIPPort_IPPort]   DEFAULT ((0)) FOR [IPPort]


ALTER TABLE [dbo].[sIPPort] ADD   CONSTRAINT [DF_sIPPort_UsedNum]   DEFAULT ((0)) FOR [UsedNum]


ALTER TABLE [dbo].[sIPPort] ADD   CONSTRAINT [DF_sIPPort_NewUsedDate]   DEFAULT (getdate()) FOR [NewUsedDate]


ALTER TABLE [dbo].[sIPPort] ADD   CONSTRAINT [DF_sIPPort_CreateDate]   DEFAULT (getdate()) FOR [CreateDate]

'   )


IF Object_Id('sFuncDtlBak') IS NULL

BEGIN

    SELECT * INTO sFuncDtlBak FROM sFuncDtl WHERE 1 <> 1

PRINT   '添加权限备份表:sFuncDtlBak'

END


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)

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

END

IF Object_Id('[cPdtPartCom]') IS NULL

BEGIN

    EXEC('CREATE TABLE [dbo].[cPdtPartCom]

    ([ID]         [int]            NULL,

    [comMode]     [int]            NOT NULL,

    [mRelCode]    [int]            NOT NULL,

    [serID]       [int]            NOT NULL,

    [UniqueName]   [varchar](300)   NOT NULL,

    [FirCode]     [int]            NULL,

    [SecCode]     [int]            NULL,

    [NumUp]       [numeric](18, 3) NULL,

    [NumDown]     [numeric](18, 3) NULL,

    [NumLoss]     [numeric](18, 3) NULL,

    [MtlPrice]    [numeric](19, 4) NULL,

    [MtlCode]     [varchar](150)   NULL,

    [MtlName]     [varchar](50)    NULL,

    [MtlSpecName] [varchar](150)   NULL,

    [MtlUnit]     [varchar](6)     NULL,

    [MtlClrName]   [varchar](150)   NULL,

    [PaperName]   [varchar](7000)   NULL,

    [PaperLength] [numeric](18, 3) NULL,

    [PaperWidth]   [numeric](18, 3) NULL,

    [PaperHigh]   [numeric](18, 3) NULL,

    [MZWeight]    [numeric](18, 2) NULL,

    [JZWeight]    [numeric](18, 2) NULL,

    [MemoText]    [varchar](7000)   NULL,

    [PdtUseCode]   [varchar](50)    NULL,

    [PdtSplName]   [varchar](170)   NULL,

    [DptStk]      [int]            NULL,

    [BarClrCode]   [varchar](100)   NULL,

    [BarPdtCode]   [varchar](30)    NULL,

    [IsPdtClr]    [bit]            NULL,

    [Marked]      [varchar](10)    NULL,

    [IsBuy]       [bit]            NULL,

    [Custom1]     [varchar](100)   NULL,

    [Custom2]     [varchar](100)   NULL,

    [Custom3]     [varchar](100)   NULL,

    [ZHCustom1]   [varchar](100)   NULL,

    [ZHCustom2]   [varchar](100)   NULL,

    [ZHCustom3]   [varchar](100)   NULL,

    [IsCustom]    [bit]            NULL,

    [BagName]     [varchar](300)   NULL,

    CONSTRAINT [PK_cPdtPartCom] PRIMARY KEY CLUSTERED([comMode] ASC, [mRelCode] ASC, [serID] ASC)WITH(PAD_INDEX = OFF,

                                    STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,

                                    ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)ON [PRIMARY])ON [PRIMARY]




    ALTER TABLE [dbo].[cPdtPartCom]

    ADD CONSTRAINT [DF_cPdtPartCom_UniqueName] DEFAULT('''')FOR [UniqueName]



    ALTER TABLE [dbo].[cPdtPartCom]

    ADD CONSTRAINT [DF_cPdtPartCom_FirCode] DEFAULT((0))FOR [FirCode]



    ALTER TABLE [dbo].[cPdtPartCom]

    ADD CONSTRAINT [DF_cPdtPartCom_SecCode] DEFAULT((0))FOR [SecCode]



    ALTER TABLE [dbo].[cPdtPartCom]

    ADD CONSTRAINT [DF_cPdtPartCom_NumUp] DEFAULT((1))FOR [NumUp]



    ALTER TABLE [dbo].[cPdtPartCom]

    ADD CONSTRAINT [DF_cPdtPartCom_NumDown] DEFAULT((1))FOR [NumDown]



    ALTER TABLE [dbo].[cPdtPartCom]

    ADD CONSTRAINT [DF_cPdtPartCom_NumLoss] DEFAULT((0))FOR [NumLoss]



    ALTER TABLE [dbo].[cPdtPartCom]

    ADD CONSTRAINT [DF_cPdtPartCom_MtlPrice] DEFAULT((0))FOR [MtlPrice]



    ALTER TABLE [dbo].[cPdtPartCom]

    ADD CONSTRAINT [DF_cPdtPartCom_MtlName] DEFAULT('''')FOR [MtlName]



    ALTER TABLE [dbo].[cPdtPartCom]

    ADD CONSTRAINT [DF_cPdtPartCom_MtlSpecName] DEFAULT('''')FOR [MtlSpecName]



    ALTER TABLE [dbo].[cPdtPartCom]

    ADD CONSTRAINT [DF_cPdtPartCom_MtlClrName] DEFAULT('''')FOR [MtlClrName]



    ALTER TABLE [dbo].[cPdtPartCom]

    ADD CONSTRAINT [DF_cPdtPartCom_PaperName] DEFAULT('''')FOR [PaperName]



    ALTER TABLE [dbo].[cPdtPartCom]

    ADD CONSTRAINT [DF_cPdtPartCom_PaperLength] DEFAULT((0))FOR [PaperLength]



    ALTER TABLE [dbo].[cPdtPartCom]

    ADD CONSTRAINT [DF_cPdtPartCom_PaperWidth] DEFAULT((0))FOR [PaperWidth]



    ALTER TABLE [dbo].[cPdtPartCom]

    ADD CONSTRAINT [DF_cPdtPartCom_PaperHigh] DEFAULT((0))FOR [PaperHigh]



    ALTER TABLE [dbo].[cPdtPartCom]

    ADD CONSTRAINT [DF_cPdtPartCom_MZWeight] DEFAULT((0))FOR [MZWeight]



    ALTER TABLE [dbo].[cPdtPartCom]

    ADD CONSTRAINT [DF_cPdtPartCom_JZWeight] DEFAULT((0))FOR [JZWeight]



    ALTER TABLE [dbo].[cPdtPartCom]

    ADD CONSTRAINT [DF_cPdtPartCom_MemoText] DEFAULT('''')FOR [MemoText]



    ALTER TABLE [dbo].[cPdtPartCom]

    ADD CONSTRAINT [DF_cPdtPartCom_DptStk] DEFAULT((0))FOR [DptStk]



    ALTER TABLE [dbo].[cPdtPartCom]

    ADD CONSTRAINT [DF_cPdtPartCom_BarClrCode] DEFAULT('''')FOR [BarClrCode]



    ALTER TABLE [dbo].[cPdtPartCom]

    ADD CONSTRAINT [DF_cPdtPartCom_BarPdtCode] DEFAULT('''')FOR [BarPdtCode]



    ALTER TABLE [dbo].[cPdtPartCom]

    ADD CONSTRAINT [DF_cPdtPartCom_IsPdtClr] DEFAULT((0))FOR [IsPdtClr]



    ALTER TABLE [dbo].[cPdtPartCom]

    ADD CONSTRAINT [DF_cPdtPartCom_Marked] DEFAULT('''')FOR [Marked]



    ALTER TABLE [dbo].[cPdtPartCom]

    ADD CONSTRAINT [DF_cPdtPartCom_Custom1] DEFAULT('''')FOR [Custom1]



    ALTER TABLE [dbo].[cPdtPartCom]

    ADD CONSTRAINT [DF_cPdtPartCom_Custom2] DEFAULT('''')FOR [Custom2]



    ALTER TABLE [dbo].[cPdtPartCom]

    ADD CONSTRAINT [DF_cPdtPartCom_Custom3] DEFAULT('''')FOR [Custom3]



    ALTER TABLE [dbo].[cPdtPartCom]

    ADD CONSTRAINT [DF_cPdtPartCom_ZHCustom1] DEFAULT('''')FOR [ZHCustom1]



    ALTER TABLE [dbo].[cPdtPartCom]

    ADD CONSTRAINT [DF_cPdtPartCom_ZHCustom2] DEFAULT('''')FOR [ZHCustom2]



    ALTER TABLE [dbo].[cPdtPartCom]

    ADD CONSTRAINT [DF_cPdtPartCom_ZHCustom3] DEFAULT('''')FOR [ZHCustom3]



    ALTER TABLE [dbo].[cPdtPartCom]

    ADD CONSTRAINT [DF_cPdtPartCom_IsCustom] DEFAULT((0))FOR [IsCustom]



    ALTER TABLE [dbo].[cPdtPartCom]

    ADD CONSTRAINT [DF_cPdtPartCom_BagName] DEFAULT('''')FOR [BagName]



    EXEC sys.sp_addextendedproperty @name = N''MS_Description'',

        @value = N''包件信息(比如:型号+规格+颜色+自定义),这样跟产品库存同步好操作'', @level0type = N''SCHEMA'',

        @level0name = N''dbo'', @level1type = N''TABLE'', @level1name = N''cPdtPartCom'',

        @level2type = N''COLUMN'', @level2name = N''UniqueName''



    EXEC sys.sp_addextendedproperty @name = N''MS_Description'', @value = N''包件成本'',

        @level0type = N''SCHEMA'', @level0name = N''dbo'', @level1type = N''TABLE'',

        @level1name = N''cPdtPartCom'', @level2type = N''COLUMN'', @level2name = N''NumLoss''



    EXEC sys.sp_addextendedproperty @name = N''MS_Description'', @value = N''材料单位单价,用来算成本的'',

        @level0type = N''SCHEMA'', @level0name = N''dbo'', @level1type = N''TABLE'',

        @level1name = N''cPdtPartCom'', @level2type = N''COLUMN'', @level2name = N''MtlPrice''



    EXEC sys.sp_addextendedproperty @name = N''MS_Description'', @value = N''型号'',

        @level0type = N''SCHEMA'', @level0name = N''dbo'', @level1type = N''TABLE'',

        @level1name = N''cPdtPartCom'', @level2type = N''COLUMN'', @level2name = N''MtlCode''



    EXEC sys.sp_addextendedproperty @name = N''MS_Description'', @value = N''系列'',

        @level0type = N''SCHEMA'', @level0name = N''dbo'', @level1type = N''TABLE'',

        @level1name = N''cPdtPartCom'', @level2type = N''COLUMN'', @level2name = N''MtlName''



    EXEC sys.sp_addextendedproperty @name = N''MS_Description'', @value = N''规格'',

        @level0type = N''SCHEMA'', @level0name = N''dbo'', @level1type = N''TABLE'',

        @level1name = N''cPdtPartCom'', @level2type = N''COLUMN'', @level2name = N''MtlSpecName''



    EXEC sys.sp_addextendedproperty @name = N''MS_Description'', @value = N''单位'',

        @level0type = N''SCHEMA'', @level0name = N''dbo'', @level1type = N''TABLE'',

        @level1name = N''cPdtPartCom'', @level2type = N''COLUMN'', @level2name = N''MtlUnit''



    EXEC sys.sp_addextendedproperty @name = N''MS_Description'', @value = N''颜色'',

        @level0type = N''SCHEMA'', @level0name = N''dbo'', @level1type = N''TABLE'',

        @level1name = N''cPdtPartCom'', @level2type = N''COLUMN'', @level2name = N''MtlClrName''



    EXEC sys.sp_addextendedproperty @name = N''MS_Description'', @value = N''产品类别'',

        @level0type = N''SCHEMA'', @level0name = N''dbo'', @level1type = N''TABLE'',

        @level1name = N''cPdtPartCom'', @level2type = N''COLUMN'', @level2name = N''PaperName''



    EXEC sys.sp_addextendedproperty @name = N''MS_Description'', @value = N''纸板长cm'',

        @level0type = N''SCHEMA'', @level0name = N''dbo'', @level1type = N''TABLE'',

        @level1name = N''cPdtPartCom'', @level2type = N''COLUMN'', @level2name = N''PaperLength''



    EXEC sys.sp_addextendedproperty @name = N''MS_Description'', @value = N''纸板宽cm'',

        @level0type = N''SCHEMA'', @level0name = N''dbo'', @level1type = N''TABLE'',

        @level1name = N''cPdtPartCom'', @level2type = N''COLUMN'', @level2name = N''PaperWidth''



    EXEC sys.sp_addextendedproperty @name = N''MS_Description'', @value = N''纸板宽cm'',

        @level0type = N''SCHEMA'', @level0name = N''dbo'', @level1type = N''TABLE'',

        @level1name = N''cPdtPartCom'', @level2type = N''COLUMN'', @level2name = N''PaperHigh''



    EXEC sys.sp_addextendedproperty @name = N''MS_Description'', @value = N''毛重'',

        @level0type = N''SCHEMA'', @level0name = N''dbo'', @level1type = N''TABLE'',

        @level1name = N''cPdtPartCom'', @level2type = N''COLUMN'', @level2name = N''MZWeight''



    EXEC sys.sp_addextendedproperty @name = N''MS_Description'', @value = N''净重'',

        @level0type = N''SCHEMA'', @level0name = N''dbo'', @level1type = N''TABLE'',

        @level1name = N''cPdtPartCom'', @level2type = N''COLUMN'', @level2name = N''JZWeight''



    EXEC sys.sp_addextendedproperty @name = N''MS_Description'', @value = N''组合型号'',

        @level0type = N''SCHEMA'', @level0name = N''dbo'', @level1type = N''TABLE'',

        @level1name = N''cPdtPartCom'', @level2type = N''COLUMN'', @level2name = N''PdtUseCode''



    EXEC sys.sp_addextendedproperty @name = N''MS_Description'', @value = N''组合规格'',

        @level0type = N''SCHEMA'', @level0name = N''dbo'', @level1type = N''TABLE'',

        @level1name = N''cPdtPartCom'', @level2type = N''COLUMN'', @level2name = N''PdtSplName''



    EXEC sys.sp_addextendedproperty @name = N''MS_Description'', @value = N''组合颜色'',

        @level0type = N''SCHEMA'', @level0name = N''dbo'', @level1type = N''TABLE'',

        @level1name = N''cPdtPartCom'', @level2type = N''COLUMN'', @level2name = N''BarClrCode''



    EXEC sys.sp_addextendedproperty @name = N''MS_Description'', @value = N''产品条形码(系列+名称+型号+规格)'',

        @level0type = N''SCHEMA'', @level0name = N''dbo'', @level1type = N''TABLE'',

        @level1name = N''cPdtPartCom'', @level2type = N''COLUMN'', @level2name = N''BarPdtCode''



    EXEC sys.sp_addextendedproperty @name = N''MS_Description'', @value = N''自定义字段1'',

        @level0type = N''SCHEMA'', @level0name = N''dbo'', @level1type = N''TABLE'',

        @level1name = N''cPdtPartCom'', @level2type = N''COLUMN'', @level2name = N''Custom1''



    EXEC sys.sp_addextendedproperty @name = N''MS_Description'', @value = N''自定义字段1'',

        @level0type = N''SCHEMA'', @level0name = N''dbo'', @level1type = N''TABLE'',

        @level1name = N''cPdtPartCom'', @level2type = N''COLUMN'', @level2name = N''Custom2''



    EXEC sys.sp_addextendedproperty @name = N''MS_Description'', @value = N''自定义字段1'',

        @level0type = N''SCHEMA'', @level0name = N''dbo'', @level1type = N''TABLE'',

        @level1name = N''cPdtPartCom'', @level2type = N''COLUMN'', @level2name = N''Custom3''



    EXEC sys.sp_addextendedproperty @name = N''MS_Description'', @value = N''组合自定义字段1'',

        @level0type = N''SCHEMA'', @level0name = N''dbo'', @level1type = N''TABLE'',

        @level1name = N''cPdtPartCom'', @level2type = N''COLUMN'', @level2name = N''ZHCustom1''



    EXEC sys.sp_addextendedproperty @name = N''MS_Description'', @value = N''组合自定义字段2'',

        @level0type = N''SCHEMA'', @level0name = N''dbo'', @level1type = N''TABLE'',

        @level1name = N''cPdtPartCom'', @level2type = N''COLUMN'', @level2name = N''ZHCustom2''



    EXEC sys.sp_addextendedproperty @name = N''MS_Description'', @value = N''组合自定义字段3'',

        @level0type = N''SCHEMA'', @level0name = N''dbo'', @level1type = N''TABLE'',

        @level1name = N''cPdtPartCom'', @level2type = N''COLUMN'', @level2name = N''ZHCustom3''



    EXEC sys.sp_addextendedproperty @name = N''MS_Description'', @value = N''取消皮号,只保留型号+规格+颜色'',

        @level0type = N''SCHEMA'', @level0name = N''dbo'', @level1type = N''TABLE'',

        @level1name = N''cPdtPartCom'', @level2type = N''COLUMN'', @level2name = N''BagName''')

    PRINT('新增表:cPdtPartCom')

END





--20161129产品规格字段加长


ALTER TABLE [dbo].[cDefine] ALTER COLUMN [pyShort] varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[cPdtCom] ALTER COLUMN [MtlSpecName] varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[cPdtCom] ALTER COLUMN [PdtSplName] varchar(170) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dPdtOdrDtl] ALTER COLUMN [PdtSpecName] varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dTradeDtl] ALTER COLUMN [PdtSpecName] varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dPdtOdrDtlDtl] ALTER COLUMN [PdtSpecName] varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dTradeDtlDtl] ALTER COLUMN [PdtSpecName] varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dWorkShopDtl] ALTER COLUMN [PdtSpecName] varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dWorkShopDtlPdt]

ALTER COLUMN [PdtSpecName] varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dWorkPieceDtl] ALTER COLUMN [PdtSpecName] varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[cPdtPiePrice] ALTER COLUMN [PdtSplName] varchar(170) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dPdtStgDtl] ALTER COLUMN [PdtSpecName] varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dPdtStore] ALTER COLUMN [PdtSpecName] varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[cPdtBomTree] ALTER COLUMN [PdtSpecName] varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dPdtPartDtl] ALTER COLUMN [PdtSpecName] varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dPdtPartStore] ALTER COLUMN [PdtSpecName] varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dPdtStockDtl] ALTER COLUMN [PdtSpecName] varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[cPdtPie] ALTER COLUMN [PdtSpecName] varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dPdtStoreBar] ALTER COLUMN [PdtSpecName] varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dPdtStoreSale] ALTER COLUMN [PdtSpecName] varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dPdtCostPrice] ALTER COLUMN [PdtSpecName] varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[cPdtClr] ALTER COLUMN [PdtSplName] varchar(170) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[cDefine] ALTER COLUMN [SwiftCode] varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dPdtOdrDtl] ALTER COLUMN [PdtClrName] varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dTradeDtl] ALTER COLUMN [PdtClrName] varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dPdtOdrDtlDtl] ALTER COLUMN [PdtClrName] varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dTradeDtlDtl] ALTER COLUMN [PdtClrName] varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dWorkShopDtl] ALTER COLUMN [PdtClrName] varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dWorkShopDtlPdt] ALTER COLUMN [PdtClrName] varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dWorkPieceDtl] ALTER COLUMN [PdtClrName] varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[cPdtPiePrice] ALTER COLUMN [PdtSplName] varchar(170) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dPdtStgDtl] ALTER COLUMN [PdtClrName] varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dPdtStore] ALTER COLUMN [PdtClrName] varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dPdtPartDtl] ALTER COLUMN [PdtClrName] varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dPdtPartStore] ALTER COLUMN [PdtClrName] varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dPdtStockDtl] ALTER COLUMN [PdtClrName] varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dPdtStoreBar] ALTER COLUMN [PdtClrName] varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dPdtStoreSale] ALTER COLUMN [PdtClrName] varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dPdtCostPrice] ALTER COLUMN [PdtClrName] varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dPdtOdrDtl] ALTER COLUMN [CtrType] varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dPdtOdrDtl] ALTER COLUMN [PdtClrWMName] varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dPdtOdrDtl] ALTER COLUMN [PdtSpecNameOld] varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dTradeDtl] ALTER COLUMN [CtrType] varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dTradeDtl] ALTER COLUMN [PdtClrWMName] varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dTradeDtl] ALTER COLUMN [PdtSpecNameOld] varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dWorkShopDtl] ALTER COLUMN [CtrType] varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dWorkShopDtl] ALTER COLUMN [PdtClrWMName] varchar(150) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dWorkShopDtl]

ALTER COLUMN [PdtSpecNameOld] varchar(150) COLLATE Chinese_PRC_CI_AS

PRINT('20161129产品规格字段加长到150 OK')


--创建时间补充


IF NOT EXISTS (SELECT NUMERIC_SCALE

               FROM INFORMATION_SCHEMA.COLUMNS

               WHERE TABLE_NAME = 'cCtrSplr' AND COLUMN_NAME = 'CreateDate')

BEGIN

    ALTER TABLE cCtrSplr ADD CreateDate datetime DEFAULT GetDate()

    PRINT('cCtrSplr填加创建时间 OK')

END


--20170110工价设置增加产品表模式

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

BEGIN

    ALTER TABLE [dbo].[cPdtPiePrice] ADD [PdtMode] int DEFAULT 0 NULL

    PRINT('20170110工价设置增加产品表模式')

END



PRINT('20170110工价设置增加产品表模式')

--20170218过账销售折扣语句

IF NOT EXISTS (SELECT * FROM cAccountItem WHERE comMode = 150 AND relCode = '090')

BEGIN

    INSERT INTO cAccountItem(comMode, relCode, Name, PrelCode, FilialeID, creDpt)

    VALUES(150, '090', '销售折扣与折让', '', 90, '')

    PRINT('20170218过账销售折扣语句 ')

END




IF NOT EXISTS (SELECT NUMERIC_SCALE

               FROM INFORMATION_SCHEMA.COLUMNS

               WHERE TABLE_NAME = 'cAccountSet' AND COLUMN_NAME = 'DiscountSql')

BEGIN

    ALTER TABLE [dbo].[cAccountSet] ADD [DiscountSql] varchar(8000) DEFAULT '' NULL

    PRINT('cAccountSet:DiscountSql ')

END



--EXEC sp_addextendedproperty 'MS_Description', N'用在销售折扣语句', 'user', 'dbo', 'table', 'cAccountSet', 'column', 'DiscountSql'

ALTER TABLE [dbo].[cAccountSet] ALTER COLUMN [TableID] varchar(5000) COLLATE Chinese_PRC_CI_AS


PRINT(' 加长[TableID]OK')


--20170419自定义主键问题

IF NOT EXISTS (SELECT NUMERIC_SCALE

               FROM INFORMATION_SCHEMA.COLUMNS

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

BEGIN

    ALTER TABLE [dbo].[dPdtStoreBar] ADD [SPINO] varchar(100) DEFAULT '' NULL

    PRINT('dPdtStoreBar:SPINO ')

END




IF NOT EXISTS (SELECT NUMERIC_SCALE

               FROM INFORMATION_SCHEMA.COLUMNS

               WHERE TABLE_NAME = 'cDefine' AND COLUMN_NAME = 'IsCustom')

BEGIN

    ALTER TABLE [dbo].[cDefine] ADD [IsCustom] bit DEFAULT 0 NULL

    PRINT('cDefine:IsCustom')

END



IF NOT EXISTS (SELECT NUMERIC_SCALE

               FROM INFORMATION_SCHEMA.COLUMNS

               WHERE TABLE_NAME = 'cPdtCom' AND COLUMN_NAME = 'IsCustom')

BEGIN

    ALTER TABLE [dbo].[cPdtCom] ADD [IsCustom] bit DEFAULT 0 NULL

    PRINT('cPdtCom:IsCustom')

END



IF NOT EXISTS (SELECT NUMERIC_SCALE

               FROM INFORMATION_SCHEMA.COLUMNS

               WHERE TABLE_NAME = 'dWorkShopDtlPdt' AND COLUMN_NAME = 'IsCustom')

BEGIN

    ALTER TABLE [dbo].[dWorkShopDtlPdt] ADD [IsCustom] bit DEFAULT 0 NULL

    PRINT('dWorkShopDtlPdt:IsCustom')

END



IF NOT EXISTS (SELECT NUMERIC_SCALE

               FROM INFORMATION_SCHEMA.COLUMNS

               WHERE TABLE_NAME = 'dPdtOdrDtlDtl' AND COLUMN_NAME = 'IsCustom')

BEGIN

    ALTER TABLE [dbo].[dPdtOdrDtlDtl] ADD [IsCustom] bit DEFAULT 0 NULL

    PRINT('dPdtOdrDtlDtl:IsCustom')

END



IF NOT EXISTS (SELECT NUMERIC_SCALE

               FROM INFORMATION_SCHEMA.COLUMNS

               WHERE TABLE_NAME = 'dWorkShopHisDtlPdt' AND COLUMN_NAME = 'IsCustom')

BEGIN

    ALTER TABLE [dbo].[dWorkShopHisDtlPdt] ADD [IsCustom] bit DEFAULT 0 NULL

    PRINT('dWorkShopHisDtlPdt:IsCustom')

END



IF NOT EXISTS (SELECT NUMERIC_SCALE

               FROM INFORMATION_SCHEMA.COLUMNS

               WHERE TABLE_NAME = 'dPdtOdrHisDtlDtl' AND COLUMN_NAME = 'IsCustom')

BEGIN

    ALTER TABLE [dbo].[dPdtOdrHisDtlDtl] ADD [IsCustom] bit DEFAULT 0 NULL

    PRINT('dPdtOdrHisDtlDtl:IsCustom')

END



IF NOT EXISTS (SELECT NUMERIC_SCALE

               FROM INFORMATION_SCHEMA.COLUMNS

               WHERE TABLE_NAME = 'dTradeHisDtlDtl' AND COLUMN_NAME = 'IsCustom')

BEGIN

    ALTER TABLE [dbo].[dTradeHisDtlDtl] ADD [IsCustom] bit DEFAULT 0 NULL

    PRINT('[dTradeHisDtlDtl]:IsCustom')

END



IF NOT EXISTS (SELECT NUMERIC_SCALE

               FROM INFORMATION_SCHEMA.COLUMNS

               WHERE TABLE_NAME = 'dWorkShopHisDtlPdt' AND COLUMN_NAME = 'IsCustom')

BEGIN

    ALTER TABLE [dbo].[dWorkShopHisDtlPdt] ADD [IsCustom] bit DEFAULT 0 NULL

    PRINT('dWorkShopHisDtlPdt:IsCustom')

END



IF NOT EXISTS (SELECT NUMERIC_SCALE

               FROM INFORMATION_SCHEMA.COLUMNS

               WHERE TABLE_NAME = 'dPdtOdrHisDtlDtl' AND COLUMN_NAME = 'IsCustom')

BEGIN

    ALTER TABLE [dbo].[dPdtOdrHisDtlDtl] ADD [IsCustom] bit DEFAULT 0 NULL

    PRINT('dPdtOdrHisDtlDtl:IsCustom')

END




IF NOT EXISTS (SELECT NUMERIC_SCALE

               FROM INFORMATION_SCHEMA.COLUMNS

               WHERE TABLE_NAME = 'dTradeHisDtlDtl' AND COLUMN_NAME = 'IsCustom')

BEGIN

    ALTER TABLE [dbo].[dTradeHisDtlDtl] ADD [IsCustom] bit DEFAULT 0 NULL

    PRINT('dTradeHisDtlDtl:IsCustom')

END



--20170521会计科目加借贷方向

IF NOT EXISTS (SELECT NUMERIC_SCALE

               FROM INFORMATION_SCHEMA.COLUMNS

               WHERE TABLE_NAME = 'dAccountRegDtl' AND COLUMN_NAME = 'Direction')

BEGIN

    ALTER TABLE [dbo].[dAccountRegDtl] ADD [Direction] varchar(10) NULL

    PRINT('dAccountRegDtl:Direction')

END



--现有凭证加借贷方向

PRINT('现有凭证加借贷方向')

UPDATE dAccountRegDtl

SET Direction = T.Direction

FROM(SELECT Name, Direction FROM cAccountItem WHERE IsNull(PrelCode, '') = '') T

WHERE dAccountRegDtl.Name = T.Name


--20170525工价设置增加备注和增加客户和供应商流水账备注长度

IF NOT EXISTS (SELECT NUMERIC_SCALE

               FROM INFORMATION_SCHEMA.COLUMNS

               WHERE TABLE_NAME = 'cPdtPiePrice' AND COLUMN_NAME = 'memoText')

BEGIN

    ALTER TABLE [dbo].[cPdtPiePrice] ADD [memoText] varchar(2000) NULL

    PRINT('20170525工价设置增加备注和增加客户和供应商流水账备注长度 ')

END



ALTER TABLE [dbo].[cPdtPiePrice] ALTER COLUMN [memoText] varchar(2000) NULL


IF NOT EXISTS (SELECT NUMERIC_SCALE

               FROM INFORMATION_SCHEMA.COLUMNS

               WHERE TABLE_NAME = 'dTradeFareInOutDtl' AND COLUMN_NAME = 'memoText')

BEGIN

    ALTER TABLE [dbo].[dTradeFareInOutDtl] ADD [memoText] varchar(2000) COLLATE Chinese_PRC_CI_AS

    PRINT('dTradeFareInOutDtl: memoText')

END



ALTER TABLE [dbo].[dMtlFareInOutDtl]

ALTER COLUMN [memoText] varchar(2000) COLLATE Chinese_PRC_CI_AS NULL

PRINT('[dMtlFareInOutDtl]:加长到2000')

--20170725会计增加费用部门

IF NOT EXISTS (SELECT NUMERIC_SCALE

               FROM INFORMATION_SCHEMA.COLUMNS

               WHERE TABLE_NAME = 'dAccountRegDtl' AND COLUMN_NAME = 'DptName')

BEGIN

    ALTER TABLE [dbo].[dAccountRegDtl] ADD [DptName] varchar(50) NULL

    PRINT('dAccountRegDtl:DptName')

END



--EXEC sp_addextendedproperty 'MS_Description', N'费用部门', 'user', 'dbo', 'table', 'dAccountRegDtl', 'column', 'DptName'

IF NOT EXISTS (SELECT NUMERIC_SCALE

               FROM INFORMATION_SCHEMA.COLUMNS

               WHERE TABLE_NAME = 'dAccountRegDtl' AND COLUMN_NAME = 'Field1')

BEGIN

    ALTER TABLE [dbo].[dAccountRegDtl] ADD [Field1] varchar(100) NULL

    PRINT('dAccountRegDtl:Field1')

END



IF NOT EXISTS (SELECT NUMERIC_SCALE

               FROM INFORMATION_SCHEMA.COLUMNS

               WHERE TABLE_NAME = 'dAccountRegDtl' AND COLUMN_NAME = 'Field2')

BEGIN

    ALTER TABLE [dbo].[dAccountRegDtl] ADD [Field2] varchar(100) NULL

    PRINT('dAccountRegDtl:Field2')

END



IF NOT EXISTS (SELECT NUMERIC_SCALE

               FROM INFORMATION_SCHEMA.COLUMNS

               WHERE TABLE_NAME = 'dAccountRegDtl' AND COLUMN_NAME = 'Field3')

BEGIN

    ALTER TABLE [dbo].[dAccountRegDtl] ADD [Field3] varchar(100) NULL

    PRINT('dAccountRegDtl:Field3')

END



IF EXISTS (SELECT * FROM sFunc WHERE relCode = 194)

BEGIN

    UPDATE sFunc SET FuncName = '费用部门' WHERE relCode = 194

    PRINT('添加:费用部门')

END


--20171114订单包装件数改字段数字


--dPdtOdrDtl

IF EXISTS (SELECT 1 FROM sysconstraints WHERE Object_Name(constid) = 'DF_dPdtOdrDtl_NumPackage')ALTER TABLE dPdtOdrDtl DROP CONSTRAINT DF_dPdtOdrDtl_NumPackage


ALTER TABLE dPdtOdrDtl ALTER COLUMN NumPackage numeric(18, 3) NULL


ALTER TABLE dPdtOdrDtl ADD CONSTRAINT DF_dPdtOdrDtl_NumPackage DEFAULT 0 FOR NumPackage

PRINT('dPdtOdrDtl:NumPackage 改为 numeric(18, 3) ')

--dPdtOdrDtlDtl

IF EXISTS (SELECT 1 FROM sysconstraints WHERE Object_Name(constid) = 'DF_dPdtOdrDtlDtl_NumPackage')ALTER TABLE dPdtOdrDtlDtl DROP CONSTRAINT DF_dPdtOdrDtlDtl_NumPackage


ALTER TABLE dPdtOdrDtlDtl ALTER COLUMN NumPackage numeric(18, 3)


ALTER TABLE dPdtOdrDtlDtl ADD CONSTRAINT DF_dPdtOdrDtlDtl_NumPackage DEFAULT 0 FOR NumPackage


PRINT('dPdtOdrDtlDtl:NumPackage 改为 numeric(18, 3) ')

--dTradeDtl

IF EXISTS (SELECT 1 FROM sysconstraints WHERE Object_Name(constid) = 'DF_dTradeDtl_NumPackage')ALTER TABLE dTradeDtl DROP CONSTRAINT DF_dTradeDtl_NumPackage


ALTER TABLE dTradeDtl ALTER COLUMN NumPackage numeric(18, 3)


ALTER TABLE dTradeDtl ADD CONSTRAINT DF_dTradeDtl_NumPackage DEFAULT 0 FOR NumPackage

PRINT('dTradeDtl:NumPackage 改为 numeric(18, 3) ')

--dWorkShopDtl

IF EXISTS (SELECT 1 FROM sysconstraints WHERE Object_Name(constid) = 'DF_dWorkShopDtl_NumPackage')ALTER TABLE dWorkShopDtl DROP CONSTRAINT DF_dWorkShopDtl_NumPackage


ALTER TABLE dWorkShopDtl ALTER COLUMN NumPackage numeric(18, 3)


ALTER TABLE dWorkShopDtl ADD CONSTRAINT DF_dWorkShopDtl_NumPackage DEFAULT 0 FOR NumPackage

PRINT('dWorkShopDtl:NumPackage 改为 numeric(18, 3) ')



--20170725取消IX_cPdtCom_Unique

IF EXISTS (SELECT *

           FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS idx

           WHERE idx.CONSTRAINT_NAME = 'IX_cPdtCom_Unique')

BEGIN

    ALTER TABLE [dbo].[cPdtCom] DROP CONSTRAINT [IX_cPdtCom_Unique]

    PRINT('20170725取消IX_cPdtCom_Unique')

END



--20170620分支客户加系列和折扣

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

BEGIN

    ALTER TABLE [dbo].[cCtrSplrDtl] ADD [aDiscount] numeric(18, 3) DEFAULT 1 NULL

    PRINT('cCtrSplrDtl:aDiscount')

END



--20160329材料库存加单价权限

IF NOT EXISTS (SELECT * FROM sFuncDtl WHERE comMode = 100 AND FuncID = 72 AND ControlName = 'actCostPrice')

BEGIN

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

    VALUES(100, 30001, 72, 'actCostPrice', '可看单价', 'actCostPrice', 1)

    PRINT('添加权限:可看单价')

END



--20180721工厂版产品清单拆分保存

ALTER TABLE [dbo].[cPdtCom] ALTER COLUMN [Marked] varchar(50) COLLATE Chinese_PRC_CI_AS


---------------------------------------------我是分割线---------------------------------------------

--   20190101工-增加BagName

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

BEGIN

    ALTER TABLE dWorkShopDtl ADD BagName varchar(300) NULL

    PRINT('dWorkShopDtl:BagName')

END



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

BEGIN

    ALTER TABLE dTradeDtlDtl ADD BagName varchar(300) NULL

    PRINT('dTradeDtlDtl:BagName')

END



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

BEGIN

    ALTER TABLE dWorkShopDtlPdt ADD BagName varchar(300) NULL

    PRINT('dWorkShopDtlPdt:BagName')

END



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

BEGIN

    ALTER TABLE dPdtStgHisDtl ADD BagName varchar(300) NULL

    PRINT('dPdtStgHisDtl:BagName')

END




IF NOT EXISTS (SELECT NUMERIC_SCALE

               FROM INFORMATION_SCHEMA.COLUMNS

               WHERE TABLE_NAME = 'dPdtOdrDtl' AND COLUMN_NAME = 'BagName')

BEGIN

    ALTER TABLE dPdtOdrDtl ADD BagName varchar(300) NULL

    PRINT('dPdtOdrDtl:BagName')

END



IF NOT EXISTS (SELECT NUMERIC_SCALE

               FROM INFORMATION_SCHEMA.COLUMNS

               WHERE TABLE_NAME = 'cPdtCom' AND COLUMN_NAME = 'BagName')

BEGIN

    ALTER TABLE cPdtCom ADD BagName varchar(300) NULL

    PRINT('cPdtCom:BagName')

END



IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = 'cPdtPartCom' AND type = 'U')

BEGIN

    CREATE TABLE [dbo].[cPdtPartCom]

    ([ID]         [int]            NULL,

    [comMode]     [int]            NOT NULL,

    [mRelCode]    [int]            NOT NULL,

    [serID]       [int]            NOT NULL,

    [UniqueName]   [varchar](300)   NOT NULL,

    [FirCode]     [int]            NULL,

    [SecCode]     [int]            NULL,

    [NumUp]       [numeric](18, 3) NULL,

    [NumDown]     [numeric](18, 3) NULL,

    [NumLoss]     [numeric](18, 3) NULL,

    [MtlPrice]    [numeric](19, 4) NULL,

    [MtlCode]     [varchar](150)   NULL,

    [MtlName]     [varchar](50)    NULL,

    [MtlSpecName] [varchar](150)   NULL,

    [MtlUnit]     [varchar](6)     NULL,

    [MtlClrName]   [varchar](150)   NULL,

    [PaperName]   [varchar](7000)   NULL,

    [PaperLength] [numeric](18, 3) NULL,

    [PaperWidth]   [numeric](18, 3) NULL,

    [PaperHigh]   [numeric](18, 3) NULL,

    [MZWeight]    [numeric](18, 2) NULL,

    [JZWeight]    [numeric](18, 2) NULL,

    [MemoText]    [varchar](7000)   NULL,

    [PdtUseCode]   [varchar](50)    NULL,

    [PdtSplName]   [varchar](170)   NULL,

    [DptStk]      [int]            NULL,

    [BarClrCode]   [varchar](100)   NULL,

    [BarPdtCode]   [varchar](30)    NULL,

    [IsPdtClr]    [bit]            NULL,

    [Marked]      [varchar](10)    NULL,

    [IsBuy]       [bit]            NULL,

    [Custom1]     [varchar](100)   NULL,

    [Custom2]     [varchar](100)   NULL,

    [Custom3]     [varchar](100)   NULL,

    [ZHCustom1]   [varchar](100)   NULL,

    [ZHCustom2]   [varchar](100)   NULL,

    [ZHCustom3]   [varchar](100)   NULL,

    [IsCustom]    [bit]            NULL,

    [BagName]     [varchar](300)   NULL)


    ALTER TABLE [dbo].[cPdtPartCom]

    ADD CONSTRAINT [PK_cPdtPartCom] PRIMARY KEY CLUSTERED([comMode] ASC, [mRelCode] ASC, [serID] ASC)ON [PRIMARY]


    ALTER TABLE [dbo].[cPdtPartCom]

    ADD CONSTRAINT [DF_cPdtPartCom_UniqueName] DEFAULT('')FOR [UniqueName]


    ALTER TABLE [dbo].[cPdtPartCom] ADD CONSTRAINT [DF_cPdtPartCom_FirCode] DEFAULT(0)FOR [FirCode]


    ALTER TABLE [dbo].[cPdtPartCom] ADD CONSTRAINT [DF_cPdtPartCom_SecCode] DEFAULT(0)FOR [SecCode]


    ALTER TABLE [dbo].[cPdtPartCom] ADD CONSTRAINT [DF_cPdtPartCom_NumUp] DEFAULT(1)FOR [NumUp]


    ALTER TABLE [dbo].[cPdtPartCom] ADD CONSTRAINT [DF_cPdtPartCom_NumDown] DEFAULT(1)FOR [NumDown]


    ALTER TABLE [dbo].[cPdtPartCom] ADD CONSTRAINT [DF_cPdtPartCom_NumLoss] DEFAULT(0)FOR [NumLoss]


    ALTER TABLE [dbo].[cPdtPartCom] ADD CONSTRAINT [DF_cPdtPartCom_MtlPrice] DEFAULT(0)FOR [MtlPrice]


    ALTER TABLE [dbo].[cPdtPartCom] ADD CONSTRAINT [DF_cPdtPartCom_MtlName] DEFAULT('')FOR [MtlName]


    ALTER TABLE [dbo].[cPdtPartCom]

    ADD CONSTRAINT [DF_cPdtPartCom_MtlSpecName] DEFAULT('')FOR [MtlSpecName]


    ALTER TABLE [dbo].[cPdtPartCom]

    ADD CONSTRAINT [DF_cPdtPartCom_MtlClrName] DEFAULT('')FOR [MtlClrName]


    ALTER TABLE [dbo].[cPdtPartCom]

    ADD CONSTRAINT [DF_cPdtPartCom_PaperName] DEFAULT('')FOR [PaperName]


    ALTER TABLE [dbo].[cPdtPartCom]

    ADD CONSTRAINT [DF_cPdtPartCom_PaperLength] DEFAULT(0)FOR [PaperLength]


    ALTER TABLE [dbo].[cPdtPartCom]

    ADD CONSTRAINT [DF_cPdtPartCom_PaperWidth] DEFAULT(0)FOR [PaperWidth]


    ALTER TABLE [dbo].[cPdtPartCom] ADD CONSTRAINT [DF_cPdtPartCom_PaperHigh] DEFAULT(0)FOR [PaperHigh]


    ALTER TABLE [dbo].[cPdtPartCom] ADD CONSTRAINT [DF_cPdtPartCom_MZWeight] DEFAULT(0)FOR [MZWeight]


    ALTER TABLE [dbo].[cPdtPartCom] ADD CONSTRAINT [DF_cPdtPartCom_JZWeight] DEFAULT(0)FOR [JZWeight]


    ALTER TABLE [dbo].[cPdtPartCom] ADD CONSTRAINT [DF_cPdtPartCom_MemoText] DEFAULT('')FOR [MemoText]


    ALTER TABLE [dbo].[cPdtPartCom] ADD CONSTRAINT [DF_cPdtPartCom_DptStk] DEFAULT(0)FOR [DptStk]


    ALTER TABLE [dbo].[cPdtPartCom]

    ADD CONSTRAINT [DF_cPdtPartCom_BarClrCode] DEFAULT('')FOR [BarClrCode]


    ALTER TABLE [dbo].[cPdtPartCom]

    ADD CONSTRAINT [DF_cPdtPartCom_BarPdtCode] DEFAULT('')FOR [BarPdtCode]


    ALTER TABLE [dbo].[cPdtPartCom] ADD CONSTRAINT [DF_cPdtPartCom_IsPdtClr] DEFAULT(0)FOR [IsPdtClr]


    ALTER TABLE [dbo].[cPdtPartCom] ADD CONSTRAINT [DF_cPdtPartCom_Marked] DEFAULT('')FOR [Marked]


    ALTER TABLE [dbo].[cPdtPartCom] ADD CONSTRAINT [DF_cPdtPartCom_Custom1] DEFAULT('')FOR [Custom1]


    ALTER TABLE [dbo].[cPdtPartCom] ADD CONSTRAINT [DF_cPdtPartCom_Custom2] DEFAULT('')FOR [Custom2]


    ALTER TABLE [dbo].[cPdtPartCom] ADD CONSTRAINT [DF_cPdtPartCom_Custom3] DEFAULT('')FOR [Custom3]


    ALTER TABLE [dbo].[cPdtPartCom]

    ADD CONSTRAINT [DF_cPdtPartCom_ZHCustom1] DEFAULT('')FOR [ZHCustom1]


    ALTER TABLE [dbo].[cPdtPartCom]

    ADD CONSTRAINT [DF_cPdtPartCom_ZHCustom2] DEFAULT('')FOR [ZHCustom2]


    ALTER TABLE [dbo].[cPdtPartCom]

    ADD CONSTRAINT [DF_cPdtPartCom_ZHCustom3] DEFAULT('')FOR [ZHCustom3]


    ALTER TABLE [dbo].[cPdtPartCom] ADD CONSTRAINT [DF_cPdtPartCom_IsCustom] DEFAULT(0)FOR [IsCustom]


    ALTER TABLE [dbo].[cPdtPartCom] ADD CONSTRAINT [DF_cPdtPartCom_BagName] DEFAULT('')FOR [BagName]

    PRINT('添加表:[cPdtPartCom]')

END

ELSE IF NOT EXISTS (SELECT NUMERIC_SCALE

                    FROM INFORMATION_SCHEMA.COLUMNS

                    WHERE TABLE_NAME = 'cPdtPartCom' AND COLUMN_NAME = 'BagName')

BEGIN

    ALTER TABLE cPdtPartCom ADD BagName varchar(300) NULL

    PRINT('cPdtPartCom:BagName')

END



---------------------------------------------我是分割线---------------------------------------------

IF NOT EXISTS (SELECT NUMERIC_SCALE

               FROM INFORMATION_SCHEMA.COLUMNS

               WHERE TABLE_NAME = 'dPdtOdrDtlDtl' AND COLUMN_NAME = 'BagName')

BEGIN

    ALTER TABLE dPdtOdrDtlDtl ADD BagName varchar(300) NULL

    PRINT('dPdtOdrDtlDtl:BagName')

END



IF NOT EXISTS (SELECT NUMERIC_SCALE

               FROM INFORMATION_SCHEMA.COLUMNS

               WHERE TABLE_NAME = 'dTradeDtl' AND COLUMN_NAME = 'BagName')

BEGIN

    ALTER TABLE dTradeDtl ADD BagName varchar(300) NULL

    PRINT('dTradeDtl:BagName')

END



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

BEGIN


    BEGIN

        ALTER TABLE dWorkShopDtl ADD BagName varchar(300) NULL

        PRINT('dWorkShopDtl:BagName')

    END

END



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

BEGIN

    ALTER TABLE dPdtStore ADD BagName varchar(300) NULL

    PRINT('dPdtStore:BagName')

END



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

BEGIN

    ALTER TABLE dPdtStgDtl ADD BagName varchar(300) NULL

    PRINT('dPdtStgDtl:BagName')

END



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

BEGIN

    ALTER TABLE dPdtStoreSale ADD BagName varchar(300) NULL

    PRINT('dPdtStoreSale:BagName')

END



--拆分保存过程SQL2000

IF @@Version LIKE '%2000%'

BEGIN

    IF EXISTS (SELECT * FROM sysobjects WHERE name = 'PdtComToPdtZH' AND type = 'P')

        DROP PROCEDURE PdtComToPdtZH

    EXEC('

CREATE PROCEDURE PdtComToPdtZH(@sRelCode INT)

AS

    DECLARE @NumCount INT, @sPdtRelCode INT, @sTogetherRelCode INT, @tReturn VARCHAR(8000)


    BEGIN

        --销售产品

        SELECT comMode=110, RelCode=Identity(INT, 1, 1), creDpt=Max(NumLoss),

               UniqueName=''110~组合~'' + PdtUseCode + ''~'' + IsNull(PdtSplName, '''') + ''~'' + BarClrCode,

               PdtSeriesName=Max(MtlName), PdtKind=Max(PaperName), PdtUseCode, PdtSplName, BarClrCode,

               nSalePrice=Max(PaperHigh), MakeStore=Max(DptStk), MakeDpt=Max(SecCode),

               BarPdtCode=Max(Marked), CBM=Max(JZWeight), PdtUnit=Max(BagName), ZHCustom1,

               ZHCustom2=Max(ZHCustom2), ZHCustom3=Max(ZHCustom3)

        INTO #PdtComTable

        FROM cPdtCom AS C

        WHERE C.comMode=150

              AND NOT EXISTS (SELECT ID

                              FROM cDefine AS B

                              WHERE B.comMode=110 AND C.PdtUseCode=B.userCode

                                    AND IsNull(C.PdtSplName, '''')=IsNull(B.pyShort, '''')

                                    AND C.BarClrCode=B.SwiftCode)

        GROUP BY PdtUseCode, PdtSplName, BarClrCode, ZHCustom1


        SELECT @sPdtRelCode=IsNull(CurID, 0) + 1 FROM sFunc WHERE relCode=29


        SET @sPdtRelCode=IsNull(@sPdtRelCode, 1)


        INSERT INTO cDefine(comMode, relCode, creDpt, UniqueName, ACNO, pdcArea, userCode, pyShort,

                            SwiftCode, nSalePrice, CBM, MakeStore, makeDpt, ZJPdtCode, PdtSort, preGet,

                            BankAddress, IsSale, UnitName, Custom1, Custom2, Custom3)

        SELECT comMode, RelCode=290000 + @sPdtRelCode + RelCode, creDpt, UniqueName, PdtSeriesName,

               PdtKind, PdtUseCode, IsNull(PdtSplName, ''''), BarClrCode, nSalePrice, CBM, MakeStore,

               MakeDpt, BarPdtCode, ''组合'', 2, ''常规销售'', 1, PdtUnit, ZHCustom1, ZHCustom2, ZHCustom3

        FROM #PdtComTable


        SELECT @NumCount=Count(*)FROM #PdtComTable


        SET @NumCount=IsNull(@NumCount, 0)


        UPDATE sFunc SET CurID=CurID + @NumCount + 1 WHERE relCode=29


        DROP TABLE #PdtComTable


        --包件

        SELECT comMode=101, RelCode=Identity(INT, 1, 1), creDpt=Max(NumLoss),

               UniqueName=''101~常规~'' + MtlCode + ''~'' + IsNull(MtlSpecName, '''') + ''~'' + MtlClrName,

               PdtSeriesName=Max(MtlName), PdtKind=Max(PaperName), PdtCode=MtlCode,

               PdtSpecName=IsNull(MtlSpecName, ''''), PdtClrName=MtlClrName, MakeStore=Max(DptStk),

               MakeDpt=Max(SecCode), NumPackage=Max(NumDown), PackageType=Max(PaperWidth),

               CBM=Max(PaperLength), MZWeight=Max(MZWeight), nSalePrice=Max(MtlPrice),

               BarPdtCode=Max(BarPdtCode), IsPdtClr=Max(CASE WHEN IsPdtClr=1 THEN 1 ELSE 0 END),

               IsCustom=Max(CASE WHEN IsCustom=1 THEN 1 ELSE 0 END), Custom1, Custom2=Max(Custom2),

               Custom3=Max(Custom3)

        INTO #PdtBagTable

        FROM cPdtCom AS C

        WHERE comMode=150

              AND NOT EXISTS (SELECT ID

                              FROM cDefine AS B

                              WHERE B.comMode=101 AND C.MtlCode=B.userCode

                                    AND IsNull(C.MtlSpecName, '''')=IsNull(B.pyShort, '''')

                                    AND C.MtlClrName=B.SwiftCode

                                    AND IsNull(B.Custom1, '''')=IsNull(C.Custom1, ''''))

        GROUP BY MtlCode, MtlSpecName, MtlClrName, C.Custom1


        SET @sPdtRelCode=0


        SELECT @sPdtRelCode=IsNull(CurID, 0) + 1 FROM sFunc WHERE relCode=7


        SET @sPdtRelCode=IsNull(@sPdtRelCode, 1)


        INSERT INTO cDefine(comMode, relCode, creDpt, UniqueName, ACNO, pdcArea, userCode, pyShort,

                            SwiftCode, MakeStore, makeDpt, PdtSort, BagNum, preGet, CBM, MZWeight,

                            nSalePrice, BankAddress, IsPdtClr, IsCustom, Alias, Custom1, Custom2,

                            Custom3)

        SELECT comMode, RelCode=70000 + @sPdtRelCode + RelCode, creDpt, UniqueName, PdtSeriesName,

               PdtKind, PdtCode, PdtSpecName, PdtClrName, MakeStore, MakeDpt, ''常规'', NumPackage,

               PackageType, CBM, MZWeight, nSalePrice, ''常规销售'', IsPdtClr, IsCustom, BarPdtCode, Custom1,

               Custom2, Custom3

        FROM #PdtBagTable


        SELECT @NumCount=Count(*)FROM #PdtBagTable


        SET @NumCount=IsNull(@NumCount, 0)


        UPDATE sFunc SET CurID=CurID + @NumCount + 1 WHERE relCode=7


        DROP TABLE #PdtBagTable


        UPDATE cPdtCom

        SET mRelCode=T.relCode

        FROM(SELECT relCode, userCode, pyShort, SwiftCode, Custom1 FROM cDefine WHERE comMode=110) AS T

        WHERE PdtUseCode=T.userCode AND IsNull(cPdtCom.PdtSplName, '''')=IsNull(T.pyShort, '''')

              AND cPdtCom.BarClrCode=T.SwiftCode AND IsNull(cPdtCom.ZHCustom1, '''')=IsNull(T.Custom1, '''')

              AND comMode=150


        --销售组合信息的体积和毛重是否来自产品清单(自动根据包件体积算组合体积)

        IF EXISTS (SELECT relCode FROM cCtrSplr WHERE comMode=120 AND dbo.f_getstr(CtryName, 5, ''|'')=''1'')

        BEGIN

            UPDATE cDefine

            SET CBM=CASE WHEN T.CBM>0 THEN T.CBM ELSE cDefine.CBM END,

                MZWeight=CASE WHEN T.MZWeight>0 THEN T.MZWeight ELSE cDefine.MZWeight END

            FROM(SELECT mRelCode,

                        CBM=Sum(

                            CASE WHEN(PaperWidth=1) THEN

                            (CASE WHEN(NumDown<>0) THEN (NumUp / NumDown)ELSE 0 END)

                            ELSE NumUp * NumDown END * PaperLength),

                        MZWeight=Sum(

                                 CASE WHEN(PaperWidth=1) THEN

                                 (CASE WHEN(NumDown<>0) THEN (NumUp / NumDown)ELSE 0 END)

                                 ELSE NumUp * NumDown END * MZWeight)

                 FROM cPdtCom

                 WHERE comMode=150

                 GROUP BY mRelCode) AS T

            WHERE cDefine.relCode=T.mRelCode AND cDefine.comMode=110 AND (T.CBM>0 OR T.MZWeight>0)

        END


        UPDATE cDefine

        SET UniqueName=Cast(comMode AS VARCHAR(10)) + ''~'' + PdtSort + ''~'' + userCode + ''~''

                       + IsNull(pyShort, '''') + ''~'' + SwiftCode

                       + CASE WHEN IsCustom=1 THEN ''~'' + Custom1 ELSE '''' END

        WHERE comMode IN (101, 110)


        UPDATE cPdtCom

        SET UniqueName=''常规~'' + MtlCode + ''~'' + IsNull(MtlSpecName, '''') + ''~'' + MtlClrName

                       + CASE WHEN IsCustom=1 THEN ''~'' + Custom1 ELSE '''' END

        WHERE comMode=150


        --更新包件清单的编码   

        UPDATE cPdtCom

        SET FirCode=T.relCode, PaperName=T.pdcArea, NumLoss=T.creDpt, DptStk=T.MakeStore,

            SecCode=T.makeDpt, MemoText=T.ZJPdtCode, IsPdtClr=T.IsPdtClr, IsCustom=T.IsCustom

        FROM(SELECT relCode, pdcArea, creDpt, MakeStore, makeDpt, ZJPdtCode,

                    uniquename=Replace(UniqueName, ''101~'', ''''), IsPdtClr, IsCustom

             FROM cDefine

             WHERE comMode=101) AS T

        WHERE cPdtCom.comMode=150 AND cPdtCom.UniqueName=T.uniquename


        --更新序号

        --DECLARE @minmrelcode INT

        --DECLARE @maxmrelcode INT


        --SELECT @minmrelcode=Min(relCode), @maxmrelcode=Max(relCode)

        --FROM cDefine

        --WHERE comMode=110


        --SET @minmrelcode=IsNull(@minmrelcode, 0)

        --SET @maxmrelcode=IsNull(@maxmrelcode, 0)


        --WHILE(@minmrelcode<=@maxmrelcode)

        --BEGIN

        --    UPDATE cPdtCom

        --    SET ID=T.id

        --    FROM(SELECT Rank() OVER (ORDER BY FirCode) AS id, FirCode

        --         FROM cPdtCom

        --         WHERE comMode=150 AND mRelCode=@minmrelcode) AS T

        --    WHERE cPdtCom.mRelCode=@minmrelcode AND cPdtCom.FirCode=T.FirCode


        --    SET @minmrelcode=@minmrelcode + 1

        --END


        ----插入系列

        --INSERT INTO cDefine(comMode, relCode, creDpt, ChkInMan, AthrMan, Name, Picture, UniqueName)

        --SELECT 170,

        --       (310000 + Rank() OVER (ORDER BY T.MtlName ASC)

        --        +   (SELECT CurID FROM sFunc WHERE relCode=31)), 90002, '''', '''', T.MtlName, '''',

        --       ''170~'' + T.MtlName

        --FROM(SELECT MtlName

        --     FROM cPdtCom

        --     WHERE comMode=150 AND IsNull(MtlName, '''')<>''''

        --     GROUP BY MtlName) AS T

        --WHERE NOT EXISTS (SELECT * FROM cDefine AS CD WHERE CD.comMode=170 AND CD.Name=T.MtlName)


        UPDATE sFunc

        SET CurID=(SELECT Max(relCode)FROM cDefine WHERE comMode=170) - 310000

        WHERE sFunc.relCode=31


        ----插入颜色信息

        --INSERT INTO cDefine(comMode, relCode, creDpt, ChkInMan, AthrMan, Name, Picture, UniqueName)

        --SELECT 109,

        --       (150002 + Rank() OVER (ORDER BY T.BarClrCode ASC)

        --        +   (SELECT CurID FROM sFunc WHERE relCode=15)), 90002, '''', '''', T.BarClrCode, '''',

        --       ''109~'' + T.BarClrCode

        --FROM(SELECT BarClrCode

        --     FROM cPdtCom

        --     WHERE comMode=150 AND IsNull(BarClrCode, '')<>''

        --     GROUP BY BarClrCode) AS T

        --WHERE NOT EXISTS (SELECT * FROM cDefine AS CD WHERE CD.comMode=109 AND CD.Name=T.BarClrCode)


        --UPDATE sFunc

        --SET CurID=(SELECT Max(relCode)FROM cDefine WHERE comMode=109) - 150000

        --WHERE sFunc.relCode=15


        --插入类别

        --      DELETE cdefine WHERE comMode=160

        --      UPDATE sFunc

        --      SET CurID=0

        --      WHERE FuncName=''产品类别''

        --INSERT INTO cDefine(comMode, relCode, creDpt, ChkInMan, AthrMan, Name, Picture, UniqueName)

        --SELECT 160,

        --       (160000 + Rank() OVER (ORDER BY T.PaperName ASC)

        --        +   (SELECT CurID FROM sFunc WHERE FuncName=''产品类别'')), 90002, '''', '''', T.PaperName, '''',

        --       ''160~'' + T.PaperName

        --FROM(SELECT PaperName

        --     FROM cPdtCom

        --     WHERE IsNull(PaperName, '')<>'' AND comMode=150

        --     GROUP BY PaperName) AS T

        --WHERE NOT EXISTS (SELECT * FROM cDefine AS CD WHERE CD.comMode=160 AND CD.Name=T.PaperName)


        UPDATE sFunc

        SET CurID=(SELECT Max(relCode)FROM cDefine WHERE comMode=160) - 160000

        WHERE FuncName=''产品类别''

    END')


END


--拆分保存过程 SQL2008更新

IF(@@Version LIKE '%2008%')

BEGIN

    IF EXISTS (SELECT * FROM sysobjects WHERE name = 'PdtComToPdtZH' AND type = 'P')

        DROP PROCEDURE PdtComToPdtZH

    BEGIN

        EXEC('

CREATE PROCEDURE PdtComToPdtZH(@sRelCode INT)

AS

    DECLARE @NumCount INT, @sPdtRelCode INT, @sTogetherRelCode INT, @tReturn VARCHAR(8000)


    BEGIN

        --销售产品

        SELECT comMode=110, RelCode=Identity(INT, 1, 1), creDpt=Max(NumLoss),

               UniqueName=''110~组合~'' + PdtUseCode + ''~'' + IsNull(PdtSplName, '''') + ''~'' + BarClrCode,

               PdtSeriesName=Max(MtlName), PdtKind=Max(PaperName), PdtUseCode, PdtSplName, BarClrCode,

               nSalePrice=Max(PaperHigh), MakeStore=Max(DptStk), MakeDpt=Max(SecCode),

               BarPdtCode=Max(Marked), CBM=Max(JZWeight), PdtUnit=Max(BagName), ZHCustom1,

               ZHCustom2=Max(ZHCustom2), ZHCustom3=Max(ZHCustom3)

        INTO #PdtComTable

        FROM cPdtCom AS C

        WHERE C.comMode=150

              AND NOT EXISTS (SELECT ID

                              FROM cDefine AS B

                              WHERE B.comMode=110 AND C.PdtUseCode=B.userCode

                                    AND IsNull(C.PdtSplName, '''')=IsNull(B.pyShort, '''')

                                    AND C.BarClrCode=B.SwiftCode)

        GROUP BY PdtUseCode, PdtSplName, BarClrCode, ZHCustom1


        SELECT @sPdtRelCode=IsNull(CurID, 0) + 1 FROM sFunc WHERE relCode=29


        SET @sPdtRelCode=IsNull(@sPdtRelCode, 1)


        INSERT INTO cDefine(comMode, relCode, creDpt, UniqueName, ACNO, pdcArea, userCode, pyShort,

                            SwiftCode, nSalePrice, CBM, MakeStore, makeDpt, ZJPdtCode, PdtSort, preGet,

                            BankAddress, IsSale, UnitName, Custom1, Custom2, Custom3)

        SELECT comMode, RelCode=290000 + @sPdtRelCode + RelCode, creDpt, UniqueName, PdtSeriesName,

               PdtKind, PdtUseCode, IsNull(PdtSplName, ''''), BarClrCode, nSalePrice, CBM, MakeStore,

               MakeDpt, BarPdtCode, ''组合'', 2, ''常规销售'', 1, PdtUnit, ZHCustom1, ZHCustom2, ZHCustom3

        FROM #PdtComTable


        SELECT @NumCount=Count(*)FROM #PdtComTable


        SET @NumCount=IsNull(@NumCount, 0)


        UPDATE sFunc SET CurID=CurID + @NumCount + 1 WHERE relCode=29


        DROP TABLE #PdtComTable


        --包件

        SELECT comMode=101, RelCode=Identity(INT, 1, 1), creDpt=Max(NumLoss),

               UniqueName=''101~常规~'' + MtlCode + ''~'' + IsNull(MtlSpecName, '''') + ''~'' + MtlClrName,

               PdtSeriesName=Max(MtlName), PdtKind=Max(PaperName), PdtCode=MtlCode,

               PdtSpecName=IsNull(MtlSpecName, ''''), PdtClrName=MtlClrName, MakeStore=Max(DptStk),

               MakeDpt=Max(SecCode), NumPackage=Max(NumDown), PackageType=Max(PaperWidth),

               CBM=Max(PaperLength), MZWeight=Max(MZWeight), nSalePrice=Max(MtlPrice),

               BarPdtCode=Max(BarPdtCode), IsPdtClr=Max(CASE WHEN IsPdtClr=1 THEN 1 ELSE 0 END),

               IsCustom=Max(CASE WHEN IsCustom=1 THEN 1 ELSE 0 END), Custom1, Custom2=Max(Custom2),

               Custom3=Max(Custom3)

        INTO #PdtBagTable

        FROM cPdtCom AS C

        WHERE comMode=150

              AND NOT EXISTS (SELECT ID

                              FROM cDefine AS B

                              WHERE B.comMode=101 AND C.MtlCode=B.userCode

                                    AND IsNull(C.MtlSpecName, '''')=IsNull(B.pyShort, '''')

                                    AND C.MtlClrName=B.SwiftCode

                                    AND IsNull(B.Custom1, '''')=IsNull(C.Custom1, ''''))

        GROUP BY MtlCode, MtlSpecName, MtlClrName, C.Custom1


        SET @sPdtRelCode=0


        SELECT @sPdtRelCode=IsNull(CurID, 0) + 1 FROM sFunc WHERE relCode=7


        SET @sPdtRelCode=IsNull(@sPdtRelCode, 1)


        INSERT INTO cDefine(comMode, relCode, creDpt, UniqueName, ACNO, pdcArea, userCode, pyShort,

                            SwiftCode, MakeStore, makeDpt, PdtSort, BagNum, preGet, CBM, MZWeight,

                            nSalePrice, BankAddress, IsPdtClr, IsCustom, Alias, Custom1, Custom2,

                            Custom3)

        SELECT comMode, RelCode=70000 + @sPdtRelCode + RelCode, creDpt, UniqueName, PdtSeriesName,

               PdtKind, PdtCode, PdtSpecName, PdtClrName, MakeStore, MakeDpt, ''常规'', NumPackage,

               PackageType, CBM, MZWeight, nSalePrice, ''常规销售'', IsPdtClr, IsCustom, BarPdtCode, Custom1,

               Custom2, Custom3

        FROM #PdtBagTable


        SELECT @NumCount=Count(*)FROM #PdtBagTable


        SET @NumCount=IsNull(@NumCount, 0)


        UPDATE sFunc SET CurID=CurID + @NumCount + 1 WHERE relCode=7


        DROP TABLE #PdtBagTable


        UPDATE cPdtCom

        SET mRelCode=T.relCode

        FROM(SELECT relCode, userCode, pyShort, SwiftCode, Custom1 FROM cDefine WHERE comMode=110) AS T

        WHERE PdtUseCode=T.userCode AND IsNull(cPdtCom.PdtSplName, '''')=IsNull(T.pyShort, '''')

              AND cPdtCom.BarClrCode=T.SwiftCode AND IsNull(cPdtCom.ZHCustom1, '''')=IsNull(T.Custom1, '''')

              AND comMode=150


        --销售组合信息的体积和毛重是否来自产品清单(自动根据包件体积算组合体积)

        IF EXISTS (SELECT relCode FROM cCtrSplr WHERE comMode=120 AND dbo.f_getstr(CtryName, 5, ''|'')=''1'')

        BEGIN

            UPDATE cDefine

            SET CBM=CASE WHEN T.CBM>0 THEN T.CBM ELSE cDefine.CBM END,

                MZWeight=CASE WHEN T.MZWeight>0 THEN T.MZWeight ELSE cDefine.MZWeight END

            FROM(SELECT mRelCode,

                        CBM=Sum(

                            CASE WHEN(PaperWidth=1) THEN

                            (CASE WHEN(NumDown<>0) THEN (NumUp / NumDown)ELSE 0 END)

                            ELSE NumUp * NumDown END * PaperLength),

                        MZWeight=Sum(

                                 CASE WHEN(PaperWidth=1) THEN

                                 (CASE WHEN(NumDown<>0) THEN (NumUp / NumDown)ELSE 0 END)

                                 ELSE NumUp * NumDown END * MZWeight)

                 FROM cPdtCom

                 WHERE comMode=150

                 GROUP BY mRelCode) AS T

            WHERE cDefine.relCode=T.mRelCode AND cDefine.comMode=110 AND (T.CBM>0 OR T.MZWeight>0)

        END


        UPDATE cDefine

        SET UniqueName=Cast(comMode AS VARCHAR(10)) + ''~'' + PdtSort + ''~'' + userCode + ''~''

                       + IsNull(pyShort, '''') + ''~'' + SwiftCode

                       + CASE WHEN IsCustom=1 THEN ''~'' + Custom1 ELSE '''' END

        WHERE comMode IN (101, 110)


        UPDATE cPdtCom

        SET UniqueName=''常规~'' + MtlCode + ''~'' + IsNull(MtlSpecName, '''') + ''~'' + MtlClrName

                       + CASE WHEN IsCustom=1 THEN ''~'' + Custom1 ELSE '''' END

        WHERE comMode=150


        --更新包件清单的编码   

        UPDATE cPdtCom

        SET FirCode=T.relCode, PaperName=T.pdcArea, NumLoss=T.creDpt, DptStk=T.MakeStore,

            SecCode=T.makeDpt, MemoText=T.ZJPdtCode, IsPdtClr=T.IsPdtClr, IsCustom=T.IsCustom

        FROM(SELECT relCode, pdcArea, creDpt, MakeStore, makeDpt, ZJPdtCode,

                    uniquename=Replace(UniqueName, ''101~'', ''''), IsPdtClr, IsCustom

             FROM cDefine

             WHERE comMode=101) AS T

        WHERE cPdtCom.comMode=150 AND cPdtCom.UniqueName=T.uniquename


        --更新序号

        DECLARE @minmrelcode INT

        DECLARE @maxmrelcode INT


        SELECT @minmrelcode=Min(relCode), @maxmrelcode=Max(relCode)

        FROM cDefine

        WHERE comMode=110


        SET @minmrelcode=IsNull(@minmrelcode, 0)

        SET @maxmrelcode=IsNull(@maxmrelcode, 0)


        WHILE(@minmrelcode<=@maxmrelcode)

        BEGIN

            UPDATE cPdtCom

            SET ID=T.id

            FROM(SELECT Rank() OVER (ORDER BY FirCode) AS id, FirCode

                 FROM cPdtCom

                 WHERE comMode=150 AND mRelCode=@minmrelcode) AS T

            WHERE cPdtCom.mRelCode=@minmrelcode AND cPdtCom.FirCode=T.FirCode


            SET @minmrelcode=@minmrelcode + 1

        END


        --插入系列

        INSERT INTO cDefine(comMode, relCode, creDpt, ChkInMan, AthrMan, Name, Picture, UniqueName)

        SELECT 170,

               (310000 + Rank() OVER (ORDER BY T.MtlName ASC)

                +   (SELECT CurID FROM sFunc WHERE relCode=31)), 90002, '''', '''', T.MtlName, '''',

               ''170~'' + T.MtlName

        FROM(SELECT MtlName

             FROM cPdtCom

             WHERE comMode=150 AND IsNull(MtlName, '''')<>''''

             GROUP BY MtlName) AS T

        WHERE NOT EXISTS (SELECT * FROM cDefine AS CD WHERE CD.comMode=170 AND CD.Name=T.MtlName)


        UPDATE sFunc

        SET CurID=(SELECT Max(relCode)FROM cDefine WHERE comMode=170) - 310000

        WHERE sFunc.relCode=31


        --插入颜色信息

        INSERT INTO cDefine(comMode, relCode, creDpt, ChkInMan, AthrMan, Name, Picture, UniqueName)

        SELECT 109,

               (150002 + Rank() OVER (ORDER BY T.BarClrCode ASC)

                +   (SELECT CurID FROM sFunc WHERE relCode=15)), 90002, '''', '''', T.BarClrCode, '''',

               ''109~'' + T.BarClrCode

        FROM(SELECT BarClrCode

             FROM cPdtCom

             WHERE comMode=150 AND IsNull(BarClrCode, '''')<>''''

             GROUP BY BarClrCode) AS T

        WHERE NOT EXISTS (SELECT * FROM cDefine AS CD WHERE CD.comMode=109 AND CD.Name=T.BarClrCode)


        UPDATE sFunc

        SET CurID=(SELECT Max(relCode)FROM cDefine WHERE comMode=109) - 150000

        WHERE sFunc.relCode=15


       -- 插入类别

              DELETE cdefine WHERE comMode=160

              UPDATE sFunc

              SET CurID=0

              WHERE FuncName=''产品类别''

        INSERT INTO cDefine(comMode, relCode, creDpt, ChkInMan, AthrMan, Name, Picture, UniqueName)

        SELECT 160,

               (160000 + Rank() OVER (ORDER BY T.PaperName ASC)

                +   (SELECT CurID FROM sFunc WHERE FuncName=''产品类别'')), 90002, '''', '''', T.PaperName, '''',

               ''160~'' + T.PaperName

        FROM(SELECT PaperName

             FROM cPdtCom

             WHERE IsNull(PaperName, '''')<>'''' AND comMode=150

             GROUP BY PaperName) AS T

        WHERE NOT EXISTS (SELECT * FROM cDefine AS CD WHERE CD.comMode=160 AND CD.Name=T.PaperName)


        UPDATE sFunc

        SET CurID=(SELECT Max(relCode)FROM cDefine WHERE comMode=160) - 160000

        WHERE FuncName=''产品类别''

    END')

        PRINT('拆分保存过程 SQL2008更新')

    END



END


---------------------------------------------我是分割线---------------------------------------------

--功能自定义过滤字段加长

ALTER TABLE [dbo].[sFunc] ALTER COLUMN [PntStr] varchar(200) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[sFunc] ALTER COLUMN [OrderByName] varchar(300) COLLATE Chinese_PRC_CI_AS


PRINT('--功能自定义过滤字段加长')


--20181126更新订货单出货仓库

UPDATE dPdtOdrDtl

SET PINOStore = T.Name

FROM cCtrSplr T

WHERE PlacedStoreID = T.relCode AND T.comMode = 122


PRINT '更新订货单出货仓库' + Cast(@@RowCount AS varchar(10))



---------------------------------------------我是分割线---------------------------------------------

--20181008产品清单加单位和包装规格

ALTER TABLE [dbo].[cPdtCom] ALTER COLUMN [BarPdtCode] varchar(100) COLLATE Chinese_PRC_CI_AS


---------------------------------------------我是分割线---------------------------------------------

--20180930产品型号字段加长

ALTER TABLE [dbo].[cDefine]

ALTER COLUMN [UniqueName] varchar(500) COLLATE Chinese_PRC_CI_AS NOT NULL


ALTER TABLE [dbo].[cDefine] ALTER COLUMN [Name] varchar(200) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[cDefine] ALTER COLUMN [userCode] varchar(200) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[cPdtCom] ALTER COLUMN [MtlCode] varchar(200) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[cPdtCom] ALTER COLUMN [PdtUseCode] varchar(200) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dPdtOdrDtl] ALTER COLUMN [PdtCode] varchar(200) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dPdtOdrDtlDtl] ALTER COLUMN [PdtCode] varchar(200) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dTradeDtl] ALTER COLUMN [PdtCode] varchar(200) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dTradeDtlDtl] ALTER COLUMN [PdtCode] varchar(200) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dWorkShopDtl] ALTER COLUMN [PdtCode] varchar(200) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dWorkShopDtlPdt] ALTER COLUMN [PdtCode] varchar(200) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dWorkPieceDtl] ALTER COLUMN [PdtCode] varchar(200) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[cPdtPiePrice] ALTER COLUMN [PdtUseCode] varchar(200) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dPdtStgDtl] ALTER COLUMN [PdtCode] varchar(200) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dPdtStore] ALTER COLUMN [PdtCode] varchar(200) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dPdtStoreBar] ALTER COLUMN [PdtCode] varchar(200) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dPdtStoreSale] ALTER COLUMN [PdtCode] varchar(200) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dPdtStockDtl] ALTER COLUMN [PdtCode] varchar(200) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[cPdtBomTree] ALTER COLUMN [PdtCode] varchar(200) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dPdtPartDtl] ALTER COLUMN [PdtCode] varchar(200) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dPdtPartStore] ALTER COLUMN [PdtCode] varchar(200) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[cPdtPie] ALTER COLUMN [PdtCode] varchar(200) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[dPdtCostPrice] ALTER COLUMN [PdtCode] varchar(200) COLLATE Chinese_PRC_CI_AS


ALTER TABLE [dbo].[cPdtClr] ALTER COLUMN [PdtUseCode] varchar(200) COLLATE Chinese_PRC_CI_AS


---------------------------------------------我是分割线---------------------------------------------

--20180911材料增加字段

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

BEGIN

    ALTER TABLE [dbo].[dWorkShopDtlDtl] ADD [SplrName] varchar(200) DEFAULT '' NULL

    PRINT('dWorkShopDtlDtl:SplrName')

END




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

BEGIN

    ALTER TABLE [dbo].[dWorkShopDtlDtl] ADD [SplrMtlName] varchar(200) DEFAULT '' NULL


    PRINT('dWorkShopDtlDtl:SplrMtlName')

END



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

BEGIN

    ALTER TABLE [dbo].[dWorkShopDtlDtl] ADD [SplMtlMemo] varchar(2000) DEFAULT '' NULL



    PRINT('dWorkShopDtlDtl:SplMtlMemo')

END


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

BEGIN

    ALTER TABLE [dbo].[dWorkShopHisDtlDtl] ADD [SplrName] varchar(200) DEFAULT '' NULL

    PRINT('[dWorkShopHisDtlDtl]:SplrName')

END



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

BEGIN


    ALTER TABLE [dbo].[dWorkShopHisDtlDtl] ADD [SplrMtlName] varchar(200) DEFAULT '' NULL


    PRINT('dWorkShopHisDtlDtl:SplrMtlName')

END



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

BEGIN

    ALTER TABLE [dbo].[dWorkShopHisDtlDtl] ADD [SplMtlMemo] varchar(2000) DEFAULT '' NULL

    PRINT('dWorkShopHisDtlDtl:SplMtlMemo')

END



IF NOT EXISTS (SELECT * FROM syscolumns WHERE id = Object_Id('cPdtBom') AND name = 'SplrName')ALTER TABLE [dbo].[cPdtBom] ADD [SplrName] varchar(200)

                                                                                                                              DEFAULT '' NULL


IF NOT EXISTS (SELECT * FROM syscolumns WHERE id = Object_Id('cPdtBom') AND name = 'SplrMtlName')ALTER TABLE [dbo].[cPdtBom] ADD [SplrMtlName] varchar(200)

                                                                                                                                 DEFAULT '' NULL


IF NOT EXISTS (SELECT * FROM syscolumns WHERE id = Object_Id('cPdtBom') AND name = 'SplMtlMemo')ALTER TABLE [dbo].[cPdtBom] ADD [SplMtlMemo] varchar(2000)

                                                                                                                                DEFAULT '' NULL


IF NOT EXISTS (SELECT * FROM syscolumns WHERE id = Object_Id('cMtl') AND name = 'IsOutStore')ALTER TABLE [dbo].[cMtl] ADD [IsOutStore] bit

                                                                                                                          DEFAULT 0 NULL


---------------------------------------------我是分割线---------------------------------------------

--20190410计划单计件加字段

IF NOT EXISTS (SELECT * FROM syscolumns WHERE id = Object_Id('cPdtBomTree') AND name = 'Pr_ParentName')ALTER TABLE [dbo].[cPdtBomTree] ADD [Pr_ParentName] varchar(200) NULL


IF NOT EXISTS (SELECT * FROM syscolumns WHERE id = Object_Id('cPdtBomTree') AND name = 'Pr_ParentSpecName')ALTER TABLE [dbo].[cPdtBomTree] ADD [Pr_ParentSpecName] varchar(200) NULL


IF NOT EXISTS (SELECT * FROM syscolumns WHERE id = Object_Id('dWorkShopDtlProcess') AND name = 'Pr_ParentName')ALTER TABLE [dbo].[dWorkShopDtlProcess] ADD [Pr_ParentName] varchar(200) NULL


IF NOT EXISTS (SELECT * FROM syscolumns WHERE id = Object_Id('dWorkShopDtlProcess') AND name = 'Pr_ParentSpecName')ALTER TABLE [dbo].[dWorkShopDtlProcess] ADD [Pr_ParentSpecName] varchar(200) NULL


IF NOT EXISTS (SELECT * FROM syscolumns WHERE id = Object_Id('cPdtBomTree') AND name = 'PLevel')ALTER TABLE [dbo].[cPdtBomTree] ADD [PLevel] int NULL


IF NOT EXISTS (SELECT * FROM syscolumns WHERE id = Object_Id('dWorkShopDtlProcess') AND name = 'PLevel')ALTER TABLE [dbo].[dWorkShopDtlProcess] ADD [PLevel] int NULL


IF NOT EXISTS (SELECT * FROM syscolumns WHERE id = Object_Id('dWorkShopDtlProcess') AND name = 'ProcessID')ALTER TABLE [dbo].[dWorkShopDtlProcess] ADD [ProcessID] int NULL


IF NOT EXISTS (SELECT * FROM syscolumns WHERE id = Object_Id('dWorkShopDtlProcess') AND name = 'pPrintNum')ALTER TABLE [dbo].[dWorkShopDtlProcess] ADD [pPrintNum] int NULL


IF NOT EXISTS (SELECT * FROM syscolumns WHERE id = Object_Id('dWorkShopDtlProcess') AND name = 'PdtClrName')ALTER TABLE [dbo].[dWorkShopDtlProcess] ADD [PdtClrName] varchar(200) NULL


IF NOT EXISTS (SELECT * FROM syscolumns WHERE id = Object_Id('dWorkShopDtlProcess') AND name = 'PdtSort')ALTER TABLE [dbo].[dWorkShopDtlProcess] ADD [PdtSort] varchar(20) NULL


IF NOT EXISTS (SELECT * FROM syscolumns WHERE id = Object_Id('dWorkShopDtlProcess') AND name = 'pPrintNum')ALTER TABLE [dbo].[dWorkShopDtlProcess] ADD DEFAULT 0 FOR [pPrintNum]


---------------------------------------------我是分割线---------------------------------------------

--20190501工价设置加产品颜色

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

BEGIN

    ALTER TABLE [dbo].[cPdtPiePrice] ADD [PdtPClrName] varchar(200) DEFAULT '' NULL


    EXEC('UPDATE cPdtPiePrice SET PdtUseCode=userCode, PdtSplName=pyShort, PdtPClrName=SwiftCode

FROM cDefine AS T

WHERE(T.relCode=mRelCode) ')

END


---------------------------------------------我是分割线---------------------------------------------

--20190723增加图片字段

IF NOT EXISTS (SELECT * FROM syscolumns WHERE id = Object_Id('cDefine') AND name = 'Pic_url')ALTER TABLE [dbo].[cDefine] ADD [Pic_url] varchar(500) NULL


IF NOT EXISTS (SELECT * FROM syscolumns WHERE id = Object_Id('cMtl') AND name = 'Pic_url')ALTER TABLE [dbo].[cMtl] ADD [Pic_url] varchar(500) NULL


---------------------------------------------我是分割线---------------------------------------------

--20190726订单显示库存改为函数   

IF EXISTS (SELECT * FROM sysobjects WHERE name = 'f_PdtOdrStore' AND type = 'TF')

    DROP FUNCTION f_PdtOdrStore

BEGIN

    EXEC('CREATE   FUNCTION [dbo].[f_PdtOdrStore]

(

    @BillNo      Varchar(100),

    @SerID       Int,

    @PdtID       Int,

    @IsBill      Bit,

    @CtrSplrName Varchar(100),

    @PdtName     Varchar(500),

    @PdtSort     Varchar(20)

)

RETURNS @ReTable Table

(

    StoreName   Varchar(100),

    curStore   Numeric(18, 3),

    CtrStore   Numeric(18, 3),

    CtrOthrNum Numeric(18, 3),

    KeStore    Numeric(18, 3),

    DHWC       Numeric(18, 3)

)

AS

BEGIN

    IF @PdtSort=''组合''

    BEGIN

        IF @IsBill=1 --按订单来提取库存

        BEGIN

            INSERT @ReTable(StoreName, curStore, CtrStore, CtrOthrNum, KeStore, DHWC)

            SELECT M.Name, curStore=IsNull(Min(IsNull(curStore, 0)/ NullIf(B.NumPackage, 0)), 0),

                CtrStore=IsNull(Min(IsNull(CtrStore, 0)/ NullIf(B.NumPackage, 0)), 0),

                CtrOthrNum=IsNull(Min(IsNull(CtrOthrNum, 0)/ NullIf(B.NumPackage, 0)), 0),

                KeStore=IsNull(Min(IsNull(curStore, 0)/ NullIf(B.NumPackage, 0)), 0)

                        -IsNull(Min(IsNull(CtrStore, 0)/ NullIf(B.NumPackage, 0)), 0)

                        -IsNull(Min(IsNull(CtrOthrNum, 0)/ NullIf(B.NumPackage, 0)), 0),

                DHWC=IsNull(Max(IsNull(DHWCNumber, 0)/ NullIf(B.NumPackage, 0)), 0)

            FROM dPdtOdrDtl AS A WITH(NOLOCK)

            JOIN dPdtOdrDtlDtl AS B WITH(NOLOCK)

              ON A.mRelCode=B.mRelCode AND A.serID=B.serID

            LEFT JOIN(SELECT creDpt, PdtName, curStore=Sum(Number),

                          CtrStore=Sum(CASE WHEN CtrSplrName<>''本公司库存'' THEN Number ELSE 0 END)

                      FROM dPdtStore

                      WHERE comMode=300

                      GROUP BY creDpt, PdtName) AS S

                   ON S.creDpt=A.PlacedStoreID AND S.PdtName=B.PdtName

            LEFT JOIN(SELECT StoreID, PdtName, CtrOthrNum=Sum(Number)

                      FROM dPdtStoreSale

                      WHERE comMode=400 AND CtrSplrName=''本公司库存''

                      GROUP BY StoreID, PdtName) AS D

                   ON D.StoreID=A.PlacedStoreID AND D.PdtName=B.PdtName

            LEFT JOIN(SELECT PdtName, PlacedStoreID, DHWCNumber=Sum(WSNumber)

                      FROM(SELECT PdtName, PlacedStoreID, WSNumber=Number-NumHpn

                           FROM dPdtOdrDtl AS K WITH(NOLOCK)

                           JOIN dPdtOdr AS D WITH(NOLOCK)

                             ON K.mRelCode=D.relCode

                           WHERE D.isValidity=1 AND Number-NumHpn>0

                           UNION ALL

                           SELECT K.PdtName, PlacedStoreID,

                               WSNumber=K.Number * K.NumPackage-NumInOut

                           FROM dPdtOdrDtlDtl AS K WITH(NOLOCK)

                           JOIN dPdtOdrDtl AS F WITH(NOLOCK)

                             ON K.mRelCode=F.mRelCode AND K.serID=F.serID

                           JOIN dPdtOdr AS D WITH(NOLOCK)

                             ON F.mRelCode=D.relCode

                           WHERE D.isValidity=1 AND K.Number * K.NumPackage-K.NumInOut>0) AS M

                      GROUP BY PdtName, PlacedStoreID) AS C

                   ON C.PlacedStoreID=A.PlacedStoreID AND C.PdtName=B.PdtName

            LEFT JOIN cCtrSplr AS M WITH(NOLOCK)

                   ON M.relCode=A.PlacedStoreID

            WHERE A.mRelCode=@BillNo AND A.serID=@SerID AND M.Name IS NOT NULL

            GROUP BY M.Name

        END

        ELSE --按产品提取库存

        BEGIN

            INSERT @ReTable(StoreName, curStore, CtrStore, CtrOthrNum, KeStore, DHWC)

            SELECT StoreName=C.Name, curStore=Min(IsNull(curStore, 0)),

                CtrStore=Min(IsNull(CtrStore, 0)), CtrOthrNum=Min(IsNull(CtrOthrNum, 0)),

                [KeStore]=Min(IsNull(T2.curStore, 0)-IsNull(CtrOthrNum, 0)-IsNull(CtrStore, 0)),

                DHWC=Max(IsNull(DHWCNumber, 0))

            FROM cPdtCom AS T1 WITH(NOLOCK)

            LEFT JOIN(SELECT creDpt, PdtName, curStore=Sum(Number),

                          CtrStore=Sum(CASE WHEN CtrSplrName<>''本公司库存'' THEN Number ELSE 0 END)

                      FROM dPdtStore

                      WHERE comMode=300

                      GROUP BY creDpt, PdtName) AS T2

                   ON T1.UniqueName=T2.PdtName

            LEFT JOIN(SELECT StoreID, PdtName, CtrOthrNum=Sum(Number)

                      FROM dPdtStoreSale

                      WHERE comMode=400 AND CtrSplrName=''本公司库存''

                      GROUP BY StoreID, PdtName) AS T3

                   ON T1.UniqueName=T3.PdtName AND T2.creDpt=T3.StoreID

            LEFT JOIN(SELECT PdtName, PlacedStoreID, DHWCNumber=Sum(WSNumber)

                      FROM(SELECT PdtName, PlacedStoreID, WSNumber=Number-NumHpn

                           FROM dPdtOdrDtl AS K WITH(NOLOCK)

                           JOIN dPdtOdr AS D WITH(NOLOCK)

                             ON K.mRelCode=D.relCode

                           WHERE D.isValidity=1 AND Number-NumHpn>0

                           UNION ALL

                           SELECT K.PdtName, PlacedStoreID,

                               WSNumber=K.Number * K.NumPackage-NumInOut

                           FROM dPdtOdrDtlDtl AS K WITH(NOLOCK)

                           JOIN dPdtOdrDtl AS F WITH(NOLOCK)

                             ON K.mRelCode=F.mRelCode AND K.serID=F.serID

                           JOIN dPdtOdr AS D WITH(NOLOCK)

                             ON F.mRelCode=D.relCode

                           WHERE D.isValidity=1 AND K.Number * K.NumPackage-K.NumInOut>0) AS M

                      GROUP BY PdtName, PlacedStoreID) AS DH

                   ON DH.PdtName=T1.UniqueName AND DH.PlacedStoreID=T2.creDpt

            LEFT JOIN cCtrSplr AS C WITH(NOLOCK)

                   ON C.relCode=T2.creDpt

            WHERE T1.mRelCode=@PdtID AND C.Name IS NOT NULL

            GROUP BY C.Name

        END

    END

    ELSE

    BEGIN

        INSERT @ReTable(StoreName, curStore, CtrStore, CtrOthrNum, KeStore, DHWC)

        SELECT StoreName=C.Name, IsNull(curStore, 0), IsNull(CtrStore, 0),

            CtrOthrNum=IsNull(CtrOthrNum, 0)-IsNull(CtrHSNum, 0), IsNull(DHWC, 0),

            [KeStore]=IsNull(curStore, 0)-IsNull(CtrStore, 0)-IsNull(CtrOthrNum, 0)

                      +IsNull(CtrHSNum, 0)

        FROM cCtrSplr AS C WITH(NOLOCK)

        LEFT JOIN(SELECT creDpt, curStore=Sum(Number),

                      CtrStore=Sum(CASE WHEN CtrSplrName<>''本公司库存'' THEN Number ELSE 0 END)

                  FROM dPdtStore WITH(NOLOCK)

                  WHERE PdtName=@PdtName

                  GROUP BY creDpt) AS S

               ON C.relCode=S.creDpt

        LEFT JOIN(SELECT StoreID, CtrOthrNum=Sum(Number),

                      CtrHSNum=Sum(CASE WHEN CtrSplrName<>''本公司库存'' THEN Number ELSE 0 END)

                  FROM dPdtStoreSale WITH(NOLOCK)

                  WHERE comMode=400 AND PdtName=@PdtName

                  GROUP BY StoreID) AS T3

               ON C.relCode=T3.StoreID

        LEFT JOIN(SELECT PlacedStoreID, DHWC=Sum(DHWC)

                  FROM(SELECT PlacedStoreID, DHWC=T2.Number-T2.NumHpn

                       FROM dPdtOdrDtl AS T2 WITH(NOLOCK)

                       JOIN dPdtOdr AS T1 WITH(NOLOCK)

                         ON T1.relCode=T2.mRelCode AND T1.isValidity=1 AND T1.comMode=501

                       WHERE T2.Number-T2.NumHpn>0 AND T2.PdtName=@PdtName

                       UNION ALL

                       SELECT PlacedStoreID, DHWC=T3.Number * T3.NumPackage-T3.NumInOut

                       FROM dPdtOdrDtl AS T2 WITH(NOLOCK)

                       JOIN dPdtOdrDtlDtl AS T3 WITH(NOLOCK)

                         ON T2.mRelCode=T3.mRelCode AND T2.serID=T3.serID

                       JOIN dPdtOdr AS T1 WITH(NOLOCK)

                         ON T1.relCode=T2.mRelCode AND T1.isValidity=1 AND T1.comMode=501

                       WHERE T3.Number * T3.NumPackage-T3.NumInOut>0 AND T3.PdtName=@PdtName) AS M

                  GROUP BY M.PlacedStoreID) AS T5

               ON C.relCode=T5.PlacedStoreID

        WHERE C.comMode=122 AND dptKind=141 AND isNotUsing<>1 AND isUsed=1

    END


    RETURN

END')

    PRINT('20190726订单显示库存改为函数')

END



---------------------------------------------我是分割线---------------------------------------------

--20191020产品替换合并和复制新增加权限

PRINT('20191020产品替换合并和复制新增加权限')

IF NOT EXISTS (SELECT * FROM sFuncDtl WHERE comMode = 100 AND ControlName = 'actReplace' AND FuncID=7)

    INSERT INTO sFuncDtl(comMode, EmployeID, FuncID, ControlName, ChnName, WinControlName, IsUsed, IsReadOnly,

        sFuncCountID, OrdID, BaseTable)

    VALUES(100, 30001, 7, 'actReplace', '包件替换', 'actReplace', 0, 0, 0, 9, '')


IF NOT EXISTS (SELECT * FROM sFuncDtl WHERE comMode = 100 AND ControlName = 'actAddPdtZH' AND FuncID=7)

    INSERT INTO sFuncDtl(comMode, EmployeID, FuncID, ControlName, ChnName, WinControlName, IsUsed, IsReadOnly,

        sFuncCountID, OrdID, BaseTable)

    VALUES(100, 30001, 7, 'actAddPdtZH', '包件合并', 'actAddPdtZH', 0, 0, 0, 10, '')


---------------------------------------------我是分割线---------------------------------------------

IF NOT EXISTS (SELECT * FROM sFuncDtl WHERE comMode = 100 AND ControlName = 'actNewCopy')

BEGIN

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

    SELECT comMode = 100, EmployeID = 30001, relCode, 'actNewCopy', '复制新增', 'actNewCopy', 1, 1

    FROM sFunc

    WHERE(FKind = 1 OR FKind = 2)

      AND NOT EXISTS (SELECT *

                      FROM sFuncDtl

                      WHERE comMode = 100

                        AND EmployeID = 30001

                        AND FuncID = sFunc.relCode

                        AND ControlName = 'actNewCopy'

                        AND ChnName = '复制新增')


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

    SELECT 200, EmployeID, FuncID, 'actNewCopy', '复制新增', 'actNewCopy', 1

    FROM sFuncDtl S

    WHERE ChnName = '新增'

      AND comMode = 200

      AND EXISTS (SELECT relCode FROM sFunc WHERE(FKind = 1 OR FKind = 2) AND S.FuncID = relCode)

      AND NOT EXISTS (SELECT *

                      FROM sFuncDtl K

                      WHERE K.EmployeID = S.EmployeID AND ControlName = 'actNewCopy' AND ChnName = '复制新增')

    GROUP BY EmployeID, FuncID


    UPDATE sFuncDtl SET IsUsed = 1 WHERE ControlName = 'actNewCopy'

    PRINT ' 20191020产品替换合并和复制新增加权限   '

END


---------------------------------------------我是分割线---------------------------------------------


--20200408计划单增加拆分和变更颜色和订单拆分包件

IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = 'SpitWorkShopDtl' AND type = 'P')

    EXEC('CREATE PROCEDURE SpitWorkShopDtl

(@mRelCode VARCHAR(50), --单号

@SerID VARCHAR(50), --序号

@SpitNum VARCHAR(50), --拆分数量

@Field VARCHAR(50) ='''' --暂时不用

)

AS

DECLARE @tmpSerID INT, @tmpComMode INT, @tmpMaxRelCode VARCHAR(20),

        @tmpMaxZHRelCode VARCHAR(20), @tmpAllRelCode VARCHAR(2000), @tmpCode VARCHAR(10),

        @tmpNum VARCHAR(10), @CtrSplrID INT, @CtrSplrName VARCHAR(50), @OutStoreID INT,

        @SprID INT, @SplrName VARCHAR(50), @NumError INT


BEGIN

    SET @NumError=0


    SELECT @tmpSerID=Max(serID)FROM dWorkShopDtl WHERE mRelCode=@mRelCode


    SET @tmpSerID=IsNull(@tmpSerID, 0)+5


    INSERT INTO dWorkShopDtl(ID, comMode, mRelCode, serID, OdrID, subSerID, SubSubSerID,

                             PdtSort, Total, PackageTxt, PdtName, relCode, styleID, stdID,

                             PINO, Number, Price, memoText, IsBuy, ReqDate, PdtCode,

                             PdtSpecName, PdtUnit, PdtClrName, CtrType, PrintLogo, PrintMemo,

                             MtlCommentDate, PdtCommentDate, CtrSplrID, CtrSplrName, NumOrd,

                             CBM, PlacedStoreID, PdtClrWMName, QualityMan, QualityDate,

                             QualityText, PartCode, PartName, NumPackage, PdtSeriesName,

                             PdtKind, NumWastage, PONO, PackageType, PdtSpecNameOld, PlanDate,

                             isPrint, BarClrCode, BarPdtCode, IsCal, Custom1, Custom2, Custom3,

                             BagName)

    SELECT ID, comMode, mRelCode, SerID=@tmpSerID, OdrID, subSerID, SubSubSerID, PdtSort,

           Total, PackageTxt, PdtName, relCode, styleID, stdID, PINO, Number=@SpitNum, Price,

           memoText, IsBuy, ReqDate, PdtCode, PdtSpecName, PdtUnit, PdtClrName, CtrType,

           PrintLogo, PrintMemo, MtlCommentDate, PdtCommentDate, CtrSplrID, CtrSplrName,

           NumOrd, CBM, PlacedStoreID, PdtClrWMName, QualityMan, QualityDate, QualityText,

           PartCode, PartName, NumPackage, PdtSeriesName, PdtKind, NumWastage, PONO,

           PackageType, PdtSpecNameOld, PlanDate, isPrint, BarClrCode, BarPdtCode, IsCal,

           Custom1, Custom2, Custom3, BagName

    FROM dWorkShopDtl

    WHERE mRelCode=@mRelCode AND SerID=@SerID


    SET @NumError=@NumError+@@error


    INSERT INTO dWorkShopDtlPdt(ID, comMode, mRelCode, serID, SubSerID, OdrID, OdrserID,

                                OdrSubSerID, relCode, Number, memoText, PdtName, IsBuy,

                                PdtSort, PdtCode, PdtSpecName, PdtUnit, PdtClrName,

                                PdtSeriesName, PdtKind, NumPackage, MZWeight, JZWeight, CBM,

                                NumBox, PackNum, PackageType, BarClrCode, BarPdtCode, styleID,

                                IsPdtClr, Custom1, Custom2, Custom3, IsCustom, BagName)

    SELECT ID, comMode, mRelCode, SerID=@tmpSerID, SubSerID=@tmpSerID * 1000+SubSerID, OdrID,

           OdrserID, OdrSubSerID, relCode, Number=@SpitNum * NumPackage, memoText, PdtName,

           IsBuy, PdtSort, PdtCode, PdtSpecName, PdtUnit, PdtClrName, PdtSeriesName, PdtKind,

           NumPackage, MZWeight, JZWeight, CBM, NumBox, PackNum, PackageType, BarClrCode,

           BarPdtCode, styleID, IsPdtClr, Custom1, Custom2, Custom3, IsCustom, BagName

    FROM dWorkShopDtlPdt

    WHERE mRelCode=@mRelCode AND SerID=@SerID


    SET @NumError=@NumError+@@error


    UPDATE dWorkShopDtl SET Number=Number-@SpitNum WHERE mRelCode=@mRelCode AND serID=@SerID


    SET @NumError=@NumError+@@error


    UPDATE dWorkShopDtlPdt

    SET Number=Number-@SpitNum * NumPackage

    WHERE mRelCode=@mRelCode AND serID=@SerID


    SET @NumError=@NumError+@@error


         

    IF   (@NumError<>0)

    BEGIN

             

        SET @tmpAllRelCode=''更新数据出错,操作失败!编号:''+Cast(@NumError AS VARCHAR(20))


        RAISERROR(@tmpAllRelCode, 16, 1);


        RETURN;

    END

END ')


---------------------------------------------我是分割线---------------------------------------------


--2020年盘点单合并

IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = 'dPdtStoreBarStock' AND type = 'U')

    CREATE TABLE [dbo].[dPdtStoreBarStock]

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

    [BuyID]            [bigint]         NOT NULL,

    [InStoreID]        [bigint]         NULL,

    [mRelCode]         [varchar](20)    NULL,

    [SerID]            [int]            NULL,

    [SubSerID]         [int]            NULL,

    [CtrSplrName]      [varchar](50)    NULL,

    [PdtName]          [varchar](500)   NOT NULL,

    [SplrName]         [varchar](50)    NULL,

    [PdtSeriesName]    [varchar](50)    NULL,

    [PdtKind]          [varchar](50)    NULL,

    [PdtSort]          [varchar](20)    NULL,

    [PdtCode]          [varchar](150)   NULL,

    [PdtSpecName]      [varchar](50)    NULL,

    [PdtClrName]       [varchar](100)   NULL,

    [Number]           [numeric](18, 3) NULL,

    [CreateDate]       [datetime]       NULL,

    [IsBuyInStore]     [bit]            NULL,

    [BuyInStoreDate]   [datetime]       NULL,

    [IsSaleOutStore]   [bit]            NULL,

    [SaleOutStoreDate] [datetime]       NULL,

    [OthrInStore]      [bit]            NULL,

    [OthrInStoreDate]   [datetime]       NULL,

    [OthrOutStore]     [bit]            NULL,

    [OthrOutStoreDate] [datetime]       NULL,

    [Price]            [numeric](18, 4) NULL,

    [IsPrint]          [bit]            NULL,

    [PrintBillNo]      [varchar](30)    NULL,

    [PrintDate]        [datetime]       NULL,

    [BuyInStoreNo]     [varchar](30)    NULL,

    [SaleOutStoreNo]   [varchar](30)    NULL,

    [OthrInStoreNo]    [varchar](30)    NULL,

    [OthrOutStoreNo]   [varchar](30)    NULL,

    [TradeNo]          [varchar](200)   NULL,

    [sPdtID]           [int]            NULL,

    [Custom1]          [varchar](100)   NULL,

    [Custom2]          [varchar](100)   NULL,

    [Custom3]          [varchar](100)   NULL,

    [PdtUnit]          [varchar](10)    NULL,

    [Memo]             [varchar](1000)   NULL,

    [TradeSerID]       [int]            NULL,

    [DStoreID]         [int]            NULL) ON [PRIMARY]


IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = 'f_split' AND type = 'IF')

    EXEC('CREATE FUNCTION f_split

(

   @s varchar(8000), --待分拆的字符串

   @split varchar(10) --数据分隔符

)returns table

as

return

(

   select substring(@s,number,charindex(@split,@s+@split,number)-number) as col

   from master..spt_values

   where type=''p'' and number<=len(@s+''a'')

     and charindex(@split,@split+@s,number)=number

)'   )


IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = 'PdtStoreStockHB' AND type = 'P')

    EXEC('CREATE PROCEDURE PdtStoreStockHB

(

@sIsStockHB INT, --1、盘点单合并;2、合并相同包件

@sRelCode   VARCHAR(500)

)

AS

DECLARE @tmpRelCode VARCHAR(200), @ErrorNum INT, @newrelcode VARCHAR(20)


BEGIN

    SET @ErrorNum=0

SET XACT_ABORT ON

BEGIN TRANSACTION

    IF @sIsStockHB=1

    BEGIN

        SELECT relcode=col INTO #StockRelcode FROM dbo.f_split(@sRelCode, '','')


        SELECT TOP 1 @newrelcode=relcode FROM #StockRelcode


        IF @newrelcode<>''''

        BEGIN

            --判断是否有serid重复

            IF EXISTS (SELECT   SerID

                       FROM     dPdtStockDtl

                       WHERE    mRelCode IN(SELECT relcode FROM #StockRelcode)

                       GROUP BY SerID

                       HAVING   Count(SerID)>1)

            BEGIN

                CREATE TABLE #temp

                (ID          INT IDENTITY(1, 1),

                 mRelcode    VARCHAR(30),

                 CtrSplrName VARCHAR(50),

                 PdtName     VARCHAR(500)

                )


                INSERT INTO #temp(mRelcode, CtrSplrName, PdtName)

                SELECT   mRelCode, CtrSplrName, PdtName

                FROM     dPdtStockDtl

                WHERE    mRelCode IN(SELECT relcode FROM #StockRelcode)

                ORDER BY mRelCode, CtrSplrName, PdtName


                UPDATE dPdtStockDtl

                SET    SerID=T.ID

                FROM   #temp T

                WHERE   T.mRelcode=dPdtStockDtl.mRelCode AND T.CtrSplrName=dPdtStockDtl.CtrSplrName

                       AND dPdtStockDtl.PdtName=T.PdtName


                DROP TABLE #temp

            END


            SET @ErrorNum=@ErrorNum+@@error


             

            UPDATE dPdtStoreBarStock

            SET    mRelCode=@newrelcode

            FROM   #StockRelcode AS T

            WHERE   mRelCode=relcode


            SET @ErrorNum=@ErrorNum+@@error

        END


        DROP TABLE #StockRelcode

    END


    IF @sIsStockHB=2 --合并相同包件的时候

    BEGIN

        --电商

        UPDATE dPdtStoreBar

        SET    IsBuyInStore=1,  

               IsSaleOutStore=NULL

        FROM   dPdtStoreBarStock T

        WHERE   T.mRelCode=@sRelCode AND dPdtStoreBar.BuyID=T.BuyID

        SET @ErrorNum=@ErrorNum+@@error

    END


    IF @ErrorNum<>0

    BEGIN

        ROLLBACK TRANSACTION


        SET @tmpRelCode=''更新出错!''+''数量:''+Cast(@ErrorNum AS VARCHAR(10))


        SELECT @tmpRelCode


        RETURN;

    END

    ELSE

    BEGIN

        COMMIT TRANSACTION


        SELECT @tmpRelCode

    END

END

  ' )


---------------------------------------------我是分割线---------------------------------------------

--公司信息加长

ALTER TABLE [dbo].[cCtrSplr] ALTER COLUMN [CtryName] varchar(300) COLLATE Chinese_PRC_CI_AS


---------------------------------------------我是分割线---------------------------------------------

--20200612   固定资产生成凭证

--UPDATE dAccountRegDtl

--SET Field1=t.relCode

--FROM dFixedAssets t

--WHERE serID=t.ID AND mRelCode IN(SELECT relCode FROM dAccountReg WHERE ReferBillID='206')

IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = 'FixedAssetsToAccountReg' AND type = 'P')

    EXEC('CREATE   PROCEDURE   FixedAssetsToAccountReg

(

    @CreDpt     Int,      --部门编号

    @JTDate      DATETIME,   --计提日期

@Num         varchar(4), --号

    @OprMan    varchar(50), --操作人

    @CXBillNo    varchar(50)='''', --撤销凭证单号

    @sMdl     int =1   --

)

AS

DECLARE

    @tmpRelCode   VARCHAR(20),

    @NumError     INT,

    @RelCode      INT,

    @tmpPdtName VARCHAR(200)

BEGIN

     

      SET XACT_ABORT ON

      BEGIN TRANSACTION

      SET @NumError =0

      IF @sMdl =1 --折旧生成凭证

      BEGIN

          SET @tmpRelCode = ''''

          EXEC GetAllBillNo 178, @tmpRelCode OUTPUT

          SET @NumError =@NumError + @@error

                 

              --建凭证主表

          INSERT INTO dAccountReg(comMode,creDpt,tDate,oprMan,

             isValidity,ChkInMan,isReferBill,UseTimes,UseStatus,relCode,

             allowDel,CtrSplrID,KdClnPay,MakeID,isRed,tMoney,

             ReferBillID,Word,Number)

           SELECT 151, creDpt, @JTDate, @OprMan, 1, @OprMan,0,0, Convert(VARCHAR(6), Cast(@JTDate AS DATETIME), 112),

             @tmpRelCode,0,0,3, @OprMan,0,Sum(MonthTotal),''206'', ''记'', @Num

           FROM dFixedAssets

           WHERE IsNull(QLBillNo, '''') ='''' AND IsNull(CurTotal, 0) > IsNull(NetValue, 0)

             AND IsNull(CurTotal, 0) >0 AND BeginDate < @JTDate

             AND creDpt =@CreDpt

           GROUP BY creDpt

           SET @NumError =@NumError + @@error

         

          --建凭证子表 借

           INSERT INTO dAccountRegDtl (comMode, mRelCode, serID, remark,

                relCode, Name, SubRelCode, SubName, Field1, Credit, Debit)

           SELECT 151, + @tmpRelCode, M.ID,

              M.relCode + M.Name + IsNull(M.SpecName,''''), P.relCode, P.Name,

              M.SubID, C.Name,   M.relCode, 0,

              MonthTotal =CASE WHEN IsNull(Total,0) -IsNull(OldTotal,0) >IsNull(MonthTotal,0)

                               THEN M.MonthTotal ELSE IsNull(Total,0) -IsNull(OldTotal,0) END

           FROM dFixedAssets M

             LEFT JOIN cAccountItem C ON M.SubID = C.relCode

             LEFT JOIN cAccountItem P ON Left(C.relCode, 3) =P.relCode AND IsNull(P.PrelCode, '''') =''''

           WHERE IsNull(QLBillNo, '''') ='''' AND BeginDate < @JTDate

             AND IsNull(CurTotal, 0) > IsNull(NetValue, 0) AND IsNull(CurTotal, 0) >0

             AND IsNull(Total,0) -IsNull(OldTotal,0) >0

             AND M.creDpt = @CreDpt

           ORDER BY M.ID

           SET @NumError =@NumError + @@error


           INSERT INTO dAccountRegDtl (comMode, mRelCode,serID,remark,

               relCode,Name,SubRelCode,SubName, Debit,Credit)

           SELECT 151, @tmpRelCode, Max(M.ID + 10000000),

              Remark =Cast(Year(@JTDate) AS VARCHAR(6)) + ''年'' + Cast(Month(@JTDate) AS VARCHAR(6))+''月累计折旧'',

             relCode=''039'', Name=''累计折旧'', C.relCode, C.Name, 0,

             Credit =Sum(CASE WHEN IsNull(Total,0) -IsNull(OldTotal,0) >IsNull(MonthTotal,0)

                              THEN M.MonthTotal ELSE IsNull(Total,0) -IsNull(OldTotal,0) END)

           FROM dFixedAssets M

             LEFT JOIN cAccountItem C ON (M.TypeName = C.Name) AND Left(C.relCode, 3) = ''039''   -- M.Name + ISNULL(M.SpecName, '''') = C.Name or

           WHERE IsNull(QLBillNo, '''') ='''' AND BeginDate < @JTDate

             AND IsNull(CurTotal, 0) > IsNull(NetValue, 0) AND IsNull(CurTotal, 0) >0

             AND IsNull(Total,0) -IsNull(OldTotal,0) >0

             AND M.creDpt = @CreDpt

           GROUP BY C.relCode, C.Name

          SET @NumError =@NumError + @@error

         

          --调整固定资产信息

          UPDATE dFixedAssets SET CalcMonth=IsNull(CalcMonth, 0) + 1,

               OldTotal = IsNull(OldTotal, 0) + IsNull(D.Debit, 0),

               CurTotal = CASE WHEN (IsNull(CurTotal, 0) -IsNull(D.Debit, 0) >0) AND (IsNull(Total,0) -IsNull(OldTotal,0) -IsNull(D.Debit, 0)>0)

                              THEN IsNull(CurTotal, 0)-IsNull(D.Debit, 0) ELSE 0 END

          FROM dFixedAssets M

            JOIN dAccountRegDtl D ON D.Field1 =M.relCode  

          WHERE M.comMode=206 AND D.mRelCode =@tmpRelCode

           AND M.creDpt = @CreDpt

           AND IsNull(D.Debit, 0) > 0 AND IsNull(CurTotal, 0) >0

          SET @NumError =@NumError + @@error       

         

          UPDATE dAccountRegDtl SET SubName = B.path

          FROM dbo.f_SubItem('''') AS B

          WHERE SubRelCode = B.RelCode AND dAccountRegDtl.mRelCode =@tmpRelCode

            AND IsNull(SubRelCode, '''') <> ''''


          UPDATE dAccountRegDtl SET Direction = T.Direction

          FROM (SELECT Name, Direction FROM cAccountItem WHERE IsNull(PrelCode, '''') ='''') T

          WHERE dAccountRegDtl.Name =T.Name AND mRelCode =@tmpRelCode

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

          VALUES(@CreDpt, @tmpRelCode, 178, Cast(Month(@JTDate) AS VARCHAR(6))+''月固定资产-计提折旧核准'', ''自动核准凭证'',@OprMan)

       END ELSE

       BEGIN

            UPDATE dFixedAssets SET CalcMonth = IsNull(CalcMonth, 0) -1,

               OldTotal = CASE WHEN IsNull(OldTotal, 0) - IsNull(D.Debit, 0) >0 THEN IsNull(OldTotal, 0) - IsNull(D.Debit, 0) ELSE 0 END,

               CurTotal = IsNull(CurTotal, 0) + IsNull(D.Debit, 0)

            FROM dFixedAssets M

               JOIN dAccountRegDtl D ON D.Field1 =M.relCode   

            WHERE M.comMode=206

                AND D.mRelCode=@CXBillNo

                AND IsNull(D.Debit, 0) > 0

            SET @NumError =@NumError + @@error

                 

            DELETE dAccountReg WHERE relCode=@CXBillNo

            SET @NumError =@NumError + @@error

           

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

            VALUES(@CreDpt, @CXBillNo, 178, ''撤消计提->删除凭证(&D)...'', ''删除凭证'',@OprMan)

       END

           

       IF (@NumError <> 0 )

       BEGIN

            ROLLBACK   TRANSACTION

            IF (IsNull(@tmpPdtName, '''') = '''')

               SET @tmpPdtName =''固定资产-计提折旧失败!错误码:'' + Cast(@NumError AS VARCHAR(20))

            RAISERROR(@tmpPdtName, 16 ,1);

            RETURN;

       END ELSE

       BEGIN

            COMMIT TRANSACTION

       END

END

'   )


---------------------------------------------我是分割线---------------------------------------------

--增加表锁

IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = 'sLock' AND type = 'U')

BEGIN

    CREATE TABLE [dbo].[sLock]

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

    [relCode]   [varchar](20) NOT NULL,

    [OprType]   [int]         NULL,

    [LockManID] [int]         NULL,

    [LockMan]   [varchar](20) NULL,

    [LockTime]   [datetime]    NULL) ON [PRIMARY]


    ALTER TABLE [dbo].[sLock] ADD CONSTRAINT [DF_sLock_OprType] DEFAULT((0))FOR [OprType]


    ALTER TABLE [dbo].[sLock] ADD CONSTRAINT [DF_sLock_LockManID] DEFAULT((0))FOR [LockManID]


    ALTER TABLE [dbo].[sLock] ADD CONSTRAINT [DF_sLock_LockMan] DEFAULT('')FOR [LockMan]

END


---------------------------------------------我是分割线---------------------------------------------

--自定义显示字段

IF NOT EXISTS (SELECT 1

               FROM INFORMATION_SCHEMA.COLUMNS

               WHERE TABLE_NAME = 'sSaveFieldChn' AND COLUMN_NAME = 'IsShow')

BEGIN

    ALTER TABLE [dbo].[sSaveFieldChn] ADD [IsShow] bit DEFAULT 1 NULL


    EXEC(' UPDATE sSaveFieldChn SET IsShow =1 ')

END


--条码表加送货单号

IF NOT EXISTS (SELECT *

               FROM INFORMATION_SCHEMA.COLUMNS

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

                 AND TABLE_NAME = 'dPdtStoreBar'

                 AND (COLUMN_NAME = 'TradeNo' OR COLUMN_NAME = 'TradeSerID'))

BEGIN

    ALTER TABLE [dbo].[dPdtStoreBar] ADD [TradeNo] varchar(30) DEFAULT '' NULL


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


    EXEC sp_addextendedproperty 'MS_Description', N'送货', 'user', 'dbo', 'table', 'dPdtStoreBar', 'column', 'TradeNo'


    EXEC('UPDATE dPdtStoreBar

SET TradeNo=T.OdrID, TradeSerID=T.OdrSubSerID

FROM dPdtStgDtl AS T

WHERE T.mRelCode=OutBillNo

  AND T.comMode=302')

END

-----------------------------------------------分割线--------------------------------------------------

--PdtBarOutStore存储过程改:

/*

UPDATE dPdtStoreBar SET IsSaleOutStore =1, SaleOutStoreDate =getDate(),

          TradeNo =@sTradeBillNo, TradeSerID =@tOdrSerID, OutBillNo =@sOutBillNo

WHERE BuyID =@sBar

*/

IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = 'TradeDlrPdtBar' AND type = 'P')

    EXEC(N'CREATE   PROCEDURE   TradeDlrPdtBar

(

    @BillNo varchar(100),  

@SerID   varchar(20) =''''

)


AS


DECLARE

    @SELECT varchar(8000),

    @SELECT1 varchar(8000)


BEGIN

 

    SET @SELECT =''SELECT T1.CreateDate [条码打印日期], T1.OdrID [计划单号], SPINO [生产批号], ''

        + ''   T1.BuyID [条码], T1.PdtCode [包件名称], T1.PdtSpecName [规格], T1.PdtClrName [颜色], ''   

        + ''   T1.OutBillNo [出仓单号], T1.SaleOutStoreDate [出仓日期], InBillNo [进仓单号], ''

        + ''   BuyInStoreDate [进仓日期],   T1.TradeNo [送货单号], T1.mRelCode [完成单号]''

        + '' FROM dPdtStoreBar T1 WITH (NOLOCK)   ''  

        + '' WHERE   T1.TradeNo ='''''' + @BillNo + '''''' AND T1.TradeSerID ='' +@SerID

        + ''    AND T1.IsSaleOutStore =1 ''  

       

    EXEC(@SELECT)

   

END')

-----------------------------------------------分割线--------------------------------------------------


--- 折率按分支客户经营系列折率

ALTER TABLE [dbo].[cCtrSplr] ALTER COLUMN [MoneyIni] numeric(19, 4)


ALTER TABLE [dbo].[cCtrSplr] ALTER COLUMN [MoneyCln] numeric(19, 4)


ALTER TABLE [dbo].[cCtrSplr] ALTER COLUMN [MoneyPay] numeric(19, 4)


ALTER TABLE [dbo].[cCtrSplr] ALTER COLUMN [CtryName] varchar(300) COLLATE Chinese_PRC_CI_AS


ALTER TABLE cCtrSplrDtl ALTER COLUMN aDiscount numeric(18, 4) NULL

-----------------------------------------------分割线--------------------------------------------------

--材料调价单

/*

DELETE FROM SFUNC WHERE RELCODE =70


INSERT INTO [SFUNC] ([relCode], [FuncGroup], [ViewStatus], [ViewOrder], [FKind], [FuncName], [IsShow], [TableName], [TableMode], [TFormName], [TFormMode], [ID], [SecNode], [PntStr], [SqlStr], [SqlListDtl], [WhereStr], [OrderByName], [TableNameDtl], [TableNameDtlDtl], [MasterSql], [DtlSql], [DtlDtlSql], [IsPrint], [PrintNum], [ItemNum], [CurID], [vDate], [BillSign], [DateFormat], [SerID], [SerMode], [ViewPrint], [CustomPrint], [alCount], [BaseFuncID], [BaseField], [FuncHelp], [FuncDemo], [BaseEditDate], [WhereStrOthr])

VALUES (70, 140, 0, 0, 2, N'材料调价单', 1, N'dMtlOdr', 408, N'TdMtlCostAdjForm', 408, 8, 105, N'单据编号', N'SELECT DptName [仓库],   T1.relCode [单据编号], T1.tDate [日期],  

      T1.ChkInMan AS 核准, T1.AthrMan AS 审批,   handleOfOthr [经手人], tMoney [金额],  

      T1.cntDate AS 结算期, T1.memoText AS 备注

FROM   dMtlOdr T1 with(Nolock)

LEFT JOIN (SELECT RelCode, DptName=Name FROM cCtrSplr WHERE comMode =122) AS Dpt ON T1.creDpt =Dpt.RelCode', N' SELECT T1.relCode [单据编号], T1.tDate [日期], T1.tDate [日期],   MtlCode [编码], MtlName [名称],

       MtlSpec [规格],   MtlUnit [单位], Price [调前成本价], PriceOdr [调后成本价], NumberOdr [库存数量],

       T2.RealPayMoney [调整金额], T2.memoText [备注]

FROM dMtlOdrDtl T2 JOIN dMtlOdr T1 ON T1.relCode = T2.mRelCode', NULL, N'Order by   T1.tDate Desc, T1.relCode Desc', N'dMtlOdrDtl', N'dMtlOdrDtlDtl', N'SELECT * FROM dMtlOdr', N'SELECT * FROM dMtlOdrDtl', N'SELECT * FROM dMtlOdrDtlDtl', 1, 1, 1000, 2, N'2008', N'MTP', N'YYMM', N'9', 1, 1, 1, 1, 11, N'MtlCode', NULL, N'直接打印(&P)...;报表预览(&B);报表预览(其它);打印进仓单(&S)...;定制报表A4(&A)...;定制报表(其它1);定制报表(其它2);定制报表(其它3);定制报表(其它4);单号条码打印(&R)...;其它打印预览(&T)', NULL, NULL)

DELETE FROM sFuncSelectData WHERE funcid =70

INSERT INTO [sFuncSelectData] ([comMode], [FuncID], [FuncName], [OrdID], [SelectEngName], [IsShow], [SelectChnName], [SelectSQL], [SelectToEditField], [sFuncID], [CDSTable], [IsSelectBill], [DateConditionStr], [CompConditionStr], [OrderByName], [SpcConditionStr], [SpcConditionField], [SelectCaption1], [SelectCaption2], [SelectToLocateField], [FillField], [BillFuncID])

VALUES (100, 70, N'材料调价单', 1, N'mlSelectMtl1', 1, N'材料库存', N'SELECT T.MtlKind [材料类别], T.MtlCode [编码], T.MtlName [名称], T.MtlSpec [规格],

   T.MtlUnit [单位], NumCurStk [当前库存], T.MtlUnName,

   T.MemoSplMtl, T.MemoForPdt, T.Price

FROM   dMtlStore T

WHERE T.comMode =300 AND NumCurStk <>0   %0:s', N'MtlKind/材料类别/MtlCode/编码/MtlName/名称/MtlSpec/规格/MtlUnit/单位/NumberOdr/当前库存/MtlUnName/MtlUnName/MemoSplMtl/MemoSplMtl/Price/Price/', 0, N'名称;编码;规格', 0, N'', N'', N'', N'AND t.creDpt = %0:s', N'creDpt', N'', N'', N'', N'', 0)

*/


-----------------------------------------------分割线--------------------------------------------------


---dMtlStore

IF EXISTS (SELECT 1 FROM sysconstraints WHERE Object_Name(constid) = 'DF_dMtlStore_NumCurStk')ALTER TABLE dMtlStore DROP CONSTRAINT DF_dMtlStore_NumCurStk


ALTER TABLE dMtlStore ALTER COLUMN NumCurStk float


ALTER TABLE dMtlStore ADD CONSTRAINT DF_dMtlStore_NumCurStk DEFAULT 0 FOR NumCurStk


IF EXISTS (SELECT 1 FROM sysconstraints WHERE Object_Name(constid) = 'DF_dMtlStore_Total')ALTER TABLE dMtlStore DROP CONSTRAINT DF_dMtlStore_Total


ALTER TABLE dMtlStore ALTER COLUMN Total float


ALTER TABLE dMtlStore ADD CONSTRAINT DF_dMtlStore_Total DEFAULT 0 FOR Total


-----------------------------------------------分割线--------------------------------------------------

--增加tAvgPrice

IF NOT EXISTS (SELECT 1

               FROM INFORMATION_SCHEMA.COLUMNS

               WHERE TABLE_NAME = 'dMtlOdrDtl' AND COLUMN_NAME = 'tAvgPrice')

    ALTER TABLE dMtlOdrDtl ADD tAvgPrice float DEFAULT 0


IF NOT EXISTS (SELECT 1

               FROM INFORMATION_SCHEMA.COLUMNS

               WHERE TABLE_NAME = 'dMtlOdrDtlDtl' AND COLUMN_NAME = 'tAvgPrice')

    ALTER TABLE dMtlOdrDtlDtl ADD tAvgPrice float DEFAULT 0

-----------------------------------------------分割线--------------------------------------------------   

--dMtlOdrDtlDtl 改number为float

IF EXISTS (SELECT 1 FROM sysconstraints WHERE Object_Name(constid) = 'DF_dMtlOdrDtlDtl_Number')ALTER TABLE dMtlOdrDtlDtl DROP CONSTRAINT DF_dMtlOdrDtlDtl_Number


ALTER TABLE dMtlOdrDtlDtl ALTER COLUMN Number float


ALTER TABLE dMtlOdrDtlDtl ADD CONSTRAINT DF_dMtlOdrDtlDtl_Number DEFAULT 0 FOR Number

-----------------------------------------------分割线--------------------------------------------------

--dMtlOdrDtl 改number为float

IF EXISTS (SELECT 1 FROM sysconstraints WHERE Object_Name(constid) = 'DF_dMtlOdrDtl_Number')ALTER TABLE dMtlOdrDtl DROP CONSTRAINT DF_dMtlOdrDtl_Number


ALTER TABLE dMtlOdrDtl ALTER COLUMN Number float


ALTER TABLE dMtlOdrDtl ADD CONSTRAINT DF_dMtlOdrDtl_Number DEFAULT 0 FOR Number

-----------------------------------------------分割线--------------------------------------------------

--改dMtlOdrDtl number为float

IF EXISTS (SELECT 1 FROM sysconstraints WHERE Object_Name(constid) = 'DF_dMtlOdrDtl_PriceOdr')ALTER TABLE dMtlOdrDtl DROP CONSTRAINT DF_dMtlOdrDtl_PriceOdr


ALTER TABLE dMtlOdrDtl ALTER COLUMN PriceOdr float


ALTER TABLE dMtlOdrDtl ADD CONSTRAINT DF_dMtlOdrDtl_PriceOdr DEFAULT 0 FOR PriceOdr

-----------------------------------------------分割线--------------------------------------------------

--dMtlOdrDtl 改Price 为float


IF EXISTS (SELECT 1 FROM sysconstraints WHERE Object_Name(constid) = 'DF_dMtlOdrDtl_Price')ALTER TABLE dMtlOdrDtl DROP CONSTRAINT DF_dMtlOdrDtl_Price


ALTER TABLE dMtlOdrDtl ALTER COLUMN Price float


ALTER TABLE dMtlOdrDtl ADD CONSTRAINT DF_dMtlOdrDtl_Price DEFAULT 0 FOR Price


--委外加工添加核准日期dMtlOdrHpnToWWStore


-----------------------------------------------分割线--------------------------------------------------

--添加材料组装类别

INSERT INTO dStoreFunc(FuncGroup, TableMode, FuncID, FuncName, InOutType, ID)

SELECT 140, 405, 64, '材料组装', 1, 3

WHERE NOT EXISTS (SELECT * FROM dStoreFunc WHERE FuncGroup = 140 AND FuncID = 64 AND FuncName = '材料组装')


DELETE sFuncSelectData WHERE comMode = 100 AND FuncID = 64 AND SelectChnName = '材料组合'


INSERT INTO sFuncSelectData(comMode, FuncID, FuncName, OrdID, SelectEngName, IsShow, SelectChnName, SelectSQL,

    SelectToEditField, sFuncID, CDSTable, IsSelectBill, DateConditionStr, CompConditionStr, OrderByName,

    SpcConditionStr, SpcConditionField, SelectCaption1, SelectCaption2, SelectToLocateField, FillField, BillFuncID)

SELECT 100, 64, '材料出仓', (SELECT Max(OrdID)FROM sFuncSelectData WHERE comMode = 100 AND FuncID = 64) + 1,

    'mlSelectMtl25', 1, '材料组合',

    'SELECT MtlKind [材料类别], T1.userCode [编码], T1.Name [名称], T1.MtlSpec [规格], T1.MtlUnit [库存单位],

    NumCurStk [配套库存],ZHPrice[组合单价], MtlUnName, relCode, Price, ZHPrice, memoText, T1.MemoSplMtl

FROM cMtl T1

LEFT JOIN(SELECT mRelCode, NumCurStk=Min(IsNull(NumCurStk/NullIf(NumUp, 0), 0)),

              ZHPrice=Sum(S.Price*NumUp)

          FROM cPdtBom B

          JOIN cMtl M   ON M.relCode=B.mRelCode AND M.comMode=105

          LEFT JOIN dMtlStore S ON S.comMode=300   %0:s  

                AND B.MtlName=S.MtlName

                AND IsNull(B.MtlSpecName,'''')=IsNull(S.MtlSpec,'''')

                AND B.MtlUnit=S.MtlUnit

          WHERE B.comMode=105

          GROUP BY mRelCode) AS T2

       ON T1.relCode=T2.mRelCode

WHERE(T1.isNotUsing=0)AND (T1.comMode=105 OR T1.IsShare=1 AND T1.comMode=105)',

    'MtlType/RelCode/MtlKind/材料类别/MtlCode/编码/MtlName/名称/MtlSpec/规格/MtlUnit/库存单位/Number/配套库存/Price/ZHPrice/', 0,

    '名称;编码;规格', 0, '', '', 'Order by T1.RelCode desc', 'AND   S.creDpt = %0:s', 'creDpt', '', '', '', '', 3

WHERE NOT EXISTS (SELECT *

                  FROM sFuncSelectData

                  WHERE comMode = 100 AND FuncID = 64 AND SelectChnName = '材料组合' AND IsShow = 1)


-----------------------------------------------分割线--------------------------------------------------

--把编码放到最后去对应,以便取库存单价

UPDATE sFuncSelectData

SET SelectToEditField = Replace(SelectToEditField, 'MtlCode/编码/', '') + 'MtlCode/编码/'

WHERE FuncID IN ( 8, 64, 65, 173, 174 ) AND CharIndex('MtlCode/编码/', SelectToEditField) > 0


--可能没有核准日期


IF NOT EXISTS (SELECT NUMERIC_SCALE

               FROM INFORMATION_SCHEMA.COLUMNS

               WHERE TABLE_NAME = 'dMtlOdr' AND COLUMN_NAME = 'ChkInDate')

    ALTER TABLE dMtlOdr ADD ChkInDate datetime NULL



EXEC ('UPDATE dMtlOdr SET ChkInDate = tDate WHERE IsNull(ChkInDate, '''') = '''' AND isValidity = 1')


EXEC('UPDATE dMtlOdr SET ChkInDate = S.InDate

FROM(SELECT relCode, InDate = ChkInDate FROM dMtlOdr WHERE isValidity = 1 AND comMode = 402) S

WHERE CodeOfMtlPlan = S.relCode AND isValidity = 1 AND IsNull(ChkInDate, '''') = ''''')


-----------------------------------------------分割线--------------------------------------------------

IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS

               WHERE TABLE_NAME = 'dPdtStgdtl' AND COLUMN_NAME = 'InStorePrice')

BEGIN

    ALTER TABLE dPdtStgDtl ADD InStorePrice numeric(18, 4) DEFAULT 0


    ALTER TABLE dPdtStgDtl ADD OutStorePrice numeric(18, 4) DEFAULT 0

END

GO

--CostPrice1

IF NOT EXISTS (SELECT 1

               FROM INFORMATION_SCHEMA.COLUMNS

               WHERE TABLE_NAME = 'dTradeDtlDtl' AND COLUMN_NAME = 'CostPrice1')

    ALTER TABLE dTradeDtlDtl ADD CostPrice1 numeric(18, 4) DEFAULT 0

GO

--CostPrice1

IF NOT EXISTS (SELECT 1

               FROM INFORMATION_SCHEMA.COLUMNS

               WHERE TABLE_NAME = 'dTradeDtl' AND COLUMN_NAME = 'CostPrice1')

    ALTER TABLE dTradeDtl ADD CostPrice1 numeric(18, 4) DEFAULT 0

GO


-----------------------------------------------分割线--------------------------------------------------

IF Object_Id('dPdtCostDtl') IS NULL

BEGIN

    CREATE TABLE [dbo].[dPdtCostDtl]

    ([ID]           int            NULL,

    [ComMode]       int            CONSTRAINT DF_dPdtCostDtl_ComMode DEFAULT 370 NOT NULL,

    [mRelCode]      varchar(20)    COLLATE Chinese_PRC_CI_AS NOT NULL,

    [serID]         int            NOT NULL,

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

    [NumInStore]    numeric(18, 4) CONSTRAINT DF_dPdtCostDtl_NumInStore DEFAULT 0 NOT NULL,

    [NumOutStore]   numeric(18, 4) CONSTRAINT DF_dPdtCostDtl_NumOutStore DEFAULT 0 NULL,

    [NumOut]        numeric(18, 4) CONSTRAINT DF_dPdtCostDtl_NumOut DEFAULT 0 NOT NULL,

    [OutTotal]      numeric(18, 4) CONSTRAINT DF_dPdtCostDtl_OutTotal DEFAULT 0 NOT NULL,

    [Price]         numeric(18, 4) CONSTRAINT DF_dPdtCostDtl_Price DEFAULT 0 NULL,

    [Total]         numeric(18, 4) CONSTRAINT DF_dPdtCostDtl_Total DEFAULT 0 NULL,

    [sAvgPrice]     numeric(18, 4) CONSTRAINT DF_dPdtCostDtl_sAvgPrice DEFAULT 0 NULL,

    [InTotal]       numeric(18, 4) CONSTRAINT DF_dPdtCostDtl_InTotal DEFAULT 0 NULL,

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

    [memoText]      varchar(2000)   COLLATE Chinese_PRC_CI_AS NULL,

    [StoreID]       int            NOT NULL,

    [CtrSplrName]   varchar(200)   COLLATE Chinese_PRC_CI_AS NULL,

    [SplrName]      varchar(200)   COLLATE Chinese_PRC_CI_AS NULL,

    [IsBuy]         bit            NULL,

    [PdtSeriesName] varchar(50)    COLLATE Chinese_PRC_CI_AS NULL,

    [PdtKind]       varchar(50)    COLLATE Chinese_PRC_CI_AS NULL,

    [PdtName]       varchar(610)   COLLATE Chinese_PRC_CI_AS NOT NULL,

    [PdtSort]       varchar(10)    COLLATE Chinese_PRC_CI_AS NULL,

    [PdtCode]       varchar(200)   COLLATE Chinese_PRC_CI_AS NULL,

    [PdtSpecName]   varchar(150)   COLLATE Chinese_PRC_CI_AS NULL,

    [PdtClrName]    varchar(150)   COLLATE Chinese_PRC_CI_AS NULL,

    [PdtUnit]       varchar(6)     COLLATE Chinese_PRC_CI_AS NULL,

    [Custom1]       varchar(100)   COLLATE Chinese_PRC_CI_AS NULL,

    [Custom2]       varchar(100)   COLLATE Chinese_PRC_CI_AS NULL,

    [Custom3]       varchar(100)   COLLATE Chinese_PRC_CI_AS NULL,

    [PackageTxt]    varchar(100)   COLLATE Chinese_PRC_CI_AS NULL,

    CONSTRAINT [PK_dPdtCostDtl] PRIMARY KEY CLUSTERED([mRelCode], [serID], [ComMode])) ON [PRIMARY]

    --IX_dPdtCostDtl_StoreID_PdtName

    IF @@Version LIKE 'Microsoft SQL Server 2008%'

    BEGIN

        EXEC('CREATE NONCLUSTERED INDEX IX_dPdtCostDtl_StoreID_PdtName

   ON [dbo].[dPdtCostDtl]([StoreID], [PdtName]) INCLUDE(NumInStore, NumOut)')


        EXEC('CREATE NONCLUSTERED INDEX IX_dPdtCostDtl_StoreID_sID

    ON [dbo].[dPdtCostDtl](sID) INCLUDE(NumInStore, NumOut, Price, OutTotal)')

    END

    ELSE

    BEGIN

        EXEC('CREATE NONCLUSTERED INDEX IX_dPdtCostDtl_StoreID_PdtName

   ON [dbo].[dPdtCostDtl]([StoreID], [PdtName])')


        EXEC('CREATE NONCLUSTERED INDEX IX_dPdtCostDtl_StoreID_sID

    ON [dbo].[dPdtCostDtl](sID)')

    END

END

GO





--产品库存明细显示PdtStoreAMainDtl



--增加索引 IX_dPdtStg_isValidity

IF @@Version LIKE 'Microsoft SQL Server 2008%'

BEGIN

    IF NOT EXISTS (SELECT 1 FROM sysindexes WHERE name = 'IX_dPdtStg_isValidity' AND Object_Name(id) = 'dPdtStg')

        EXEC('CREATE NONCLUSTERED INDEX IX_dPdtStg_isValidity

ON [dbo].[dPdtStg] ([isValidity])

INCLUDE ([comMode],[creDpt],[relCode],[tDate],[ChkInDate])')

    IF NOT EXISTS (SELECT 1

                   FROM sysindexes

                   WHERE name = 'IX_dPdtStgDtl_CtrSplrName_PdtName' AND Object_Name(id) = 'dPdtStgDtl')

        EXEC('CREATE NONCLUSTERED INDEX IX_dPdtStgDtl_CtrSplrName_PdtName

ON [dbo].[dPdtStgDtl] ([CtrSplrName],[PdtName])

INCLUDE ([mRelCode],[serID],[OdrID],[Number],[memoText])')

END

ELSE

BEGIN

    IF NOT EXISTS (SELECT 1 FROM sysindexes WHERE name = 'IX_dPdtStg_isValidity' AND Object_Name(id) = 'dPdtStg')EXEC('CREATE NONCLUSTERED INDEX IX_dPdtStg_isValidity

ON [dbo].[dPdtStg] ([isValidity])')

    IF NOT EXISTS (SELECT 1

                   FROM sysindexes

                   WHERE name = 'IX_dPdtStgDtl_CtrSplrName_PdtName' AND Object_Name(id) = 'dPdtStgDtl')

        EXEC('CREATE NONCLUSTERED INDEX IX_dPdtStgDtl_CtrSplrName_PdtName ON [dbo].[dPdtStgDtl] ([CtrSplrName],[PdtName])

INCLUDE ([mRelCode],[serID],[OdrID],[Number],[memoText])')

END




--进销存需要的索引

IF @@Version LIKE 'Microsoft SQL Server 2008%'

BEGIN

    IF NOT EXISTS (SELECT 1 FROM sysindexes

                   WHERE name = 'IX_dPdtOdrDtl_dPdtOdrDtl_PlacedStoreID_PdtSort' AND Object_Name(id) = 'dPdtOdrDtl')

        EXEC('CREATE NONCLUSTERED INDEX IX_dPdtOdrDtl_dPdtOdrDtl_PlacedStoreID_PdtSort ON [dbo].[dPdtOdrDtl] ([PlacedStoreID],[PdtSort])

INCLUDE ([mRelCode],[Number],[NumHpn],[PdtName])')

END

ELSE

BEGIN

    IF NOT EXISTS (SELECT 1

                   FROM sysindexes

                   WHERE name = 'IX_dPdtOdrDtl_dPdtOdrDtl_PlacedStoreID_PdtSort' AND Object_Name(id) = 'dPdtOdrDtl')

        EXEC('CREATE NONCLUSTERED INDEX IX_dPdtOdrDtl_dPdtOdrDtl_PlacedStoreID_PdtSort ON [dbo].[dPdtOdrDtl] ([PlacedStoreID],[PdtSort])')

END


IF @@Version LIKE 'Microsoft SQL Server 2008%'

BEGIN

    IF NOT EXISTS (SELECT 1 FROM sysindexes WHERE name = 'IX_dPdtStgDtl_mRelCode' AND Object_Name(id) = 'dPdtStgDtl')

        EXEC('CREATE NONCLUSTERED INDEX IX_dPdtStgDtl_mRelCode ON [dbo].[dPdtStgDtl] ([mRelCode]) INCLUDE ([Number],[PdtName])')

END

ELSE

BEGIN

    IF NOT EXISTS (SELECT 1 FROM sysindexes WHERE name = 'IX_dPdtStgDtl_mRelCode' AND Object_Name(id) = 'dPdtStgDtl')EXEC('CREATE NONCLUSTERED INDEX IX_dPdtStgDtl_mRelCode ON [dbo].[dPdtStgDtl] ([mRelCode])')

END

IF NOT EXISTS (SELECT * FROM sysindexes WHERE id = Object_Id(N'[dbo].[sLockBil]') AND name = N'IX_sLockBil_BillNo')

    EXEC('CREATE NONCLUSTERED INDEX [IX_sLockBil_BillNo] ON [dbo].[sLockBil]   ([BillNo] ASC )   ON [PRIMARY]')


IF NOT EXISTS (SELECT NUMERIC_SCALE

               FROM INFORMATION_SCHEMA.COLUMNS

               WHERE TABLE_NAME = 'dTradeCntRecMoney' AND COLUMN_NAME = 'Field1')

    ALTER TABLE [dbo].[dTradeCntRecMoney] ADD [Field1] varchar(100) NULL

IF NOT EXISTS (SELECT NUMERIC_SCALE

               FROM INFORMATION_SCHEMA.COLUMNS

               WHERE TABLE_NAME = 'dTradeCntRecMoney' AND COLUMN_NAME = 'Field2')

    ALTER TABLE [dbo].[dTradeCntRecMoney] ADD [Field2] varchar(100) NULL

IF NOT EXISTS (SELECT NUMERIC_SCALE

               FROM INFORMATION_SCHEMA.COLUMNS

               WHERE TABLE_NAME = 'dTradeCntRecMoney' AND COLUMN_NAME = 'Field3')

    ALTER TABLE [dbo].[dTradeCntRecMoney] ADD [Field3] varchar(100) NULL

IF NOT EXISTS (SELECT NUMERIC_SCALE

               FROM INFORMATION_SCHEMA.COLUMNS

               WHERE TABLE_NAME = 'dTradeCntRecMoneyHis' AND COLUMN_NAME = 'Field1')

    ALTER TABLE [dbo].[dTradeCntRecMoneyHis] ADD [Field1] varchar(100) NULL

IF NOT EXISTS (SELECT NUMERIC_SCALE

               FROM INFORMATION_SCHEMA.COLUMNS

               WHERE TABLE_NAME = 'dTradeCntRecMoneyHis' AND COLUMN_NAME = 'Field2')

    ALTER TABLE [dbo].[dTradeCntRecMoneyHis] ADD [Field2] varchar(100) NULL

IF NOT EXISTS (SELECT NUMERIC_SCALE

               FROM INFORMATION_SCHEMA.COLUMNS

               WHERE TABLE_NAME = 'dTradeCntRecMoneyHis' AND COLUMN_NAME = 'Field3')

    ALTER TABLE [dbo].[dTradeCntRecMoneyHis] ADD [Field3] varchar(100) NULL



ALTER TABLE [dbo].[dTradeSkAccount] ALTER COLUMN [handleMan] varchar(200) COLLATE Chinese_PRC_CI_AS



INSERT INTO [sFuncCount]([comMode], [relCode], [FuncName], [Name], [ChnName], [Fields], [BaseField], [BaseTable],

    [OrdID], [isShow], [sCountID])

SELECT 15, 18, N'', N'', N'业务员', N'FollowID', N'', N'', 1, 0, 0

WHERE NOT EXISTS (SELECT * FROM [sFuncCount] WHERE comMode = 15 AND relCode = 18 AND FuncName = N'业务员')


INSERT INTO [sFuncCount]([comMode], [relCode], [FuncName], [Name], [ChnName], [Fields], [BaseField], [BaseTable],

    [OrdID], [isShow], [sCountID])

SELECT 15, 18, N'', N'', N'区域', N'CtryArea', N'', N'', 2, 0, 0

WHERE NOT EXISTS (SELECT * FROM [sFuncCount] WHERE comMode = 15 AND relCode = 18 AND FuncName = N'区域')

-----------------------------------------------2021年--------------------------------------------------

IF NOT EXISTS (SELECT NUMERIC_SCALE

               FROM INFORMATION_SCHEMA.COLUMNS

               WHERE TABLE_NAME = 'dPdtCostDtl' AND COLUMN_NAME = 'NumWrok')

    ALTER TABLE [dbo].[dPdtCostDtl]

    ADD [NumWrok] numeric(18, 4) DEFAULT 0 NULL

IF NOT EXISTS (SELECT NUMERIC_SCALE

               FROM INFORMATION_SCHEMA.COLUMNS

               WHERE TABLE_NAME = 'dPdtCostDtl' AND COLUMN_NAME = 'WrokPrice')

    ALTER TABLE [dbo].[dPdtCostDtl]

    ADD [WrokPrice] numeric(18, 4) DEFAULT 0 NULL

IF NOT EXISTS (SELECT NUMERIC_SCALE

               FROM INFORMATION_SCHEMA.COLUMNS

               WHERE TABLE_NAME = 'dPdtCostDtl' AND COLUMN_NAME = 'NumBuy')

    ALTER TABLE [dbo].[dPdtCostDtl]

    ADD [NumBuy] numeric(18, 4) DEFAULT 0 NULL

IF NOT EXISTS (SELECT NUMERIC_SCALE

               FROM INFORMATION_SCHEMA.COLUMNS

               WHERE TABLE_NAME = 'dPdtCostDtl' AND COLUMN_NAME = 'BuyPrice')

    ALTER TABLE [dbo].[dPdtCostDtl]

    ADD [BuyPrice] numeric(18, 4) DEFAULT 0 NULL

IF NOT EXISTS (SELECT NUMERIC_SCALE

               FROM INFORMATION_SCHEMA.COLUMNS

               WHERE TABLE_NAME = 'dPdtCostDtl' AND COLUMN_NAME = 'NumOthr')

    ALTER TABLE [dbo].[dPdtCostDtl]

    ADD [NumOthr] numeric(18, 4) DEFAULT 0 NULL

IF NOT EXISTS (SELECT NUMERIC_SCALE

               FROM INFORMATION_SCHEMA.COLUMNS

               WHERE TABLE_NAME = 'dPdtCostDtl' AND COLUMN_NAME = 'OthrPrice')

    ALTER TABLE [dbo].[dPdtCostDtl]

    ADD [OthrPrice] numeric(18, 4) DEFAULT 0 NULL


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

SELECT 100,30001,20,'actYEJudeg','判断余额是否大于订单金额','actYEJudeg',19

WHERE NOT EXISTS(SELECT   *   FROM sFuncDtl WHERE comMode=100 AND ControlName='actYEJudeg' )










联系方式
 
 

联系人:肖生     

手 机:189-28668085

在线QQ: pa?p=1:58413709:3 肖工

在线QQ: fma.png 冉工

邮 箱:58413709@qq.com

地 址:佛山市顺德区乐从镇新华路1号三乐路口(领航国际604号)