|
|
工厂版2016版本升级到2021版本SQL语句44
发表时间：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 邮 箱：58413709@qq.com 地 址：佛山市顺德区乐从镇新华路1号三乐路口（领航国际604号） 工作时间:(星期一至星期六) 上午08：30-12：00 下午14：00-18：00 |
:58413709
版权所有:佛山市金狐管家科技有限公司 粤ICP备2024235160号-1