记录一下公司数据库升级的步骤

1 --升级GPOS1.0到GPOS1.1数据库的升级脚本 2013-7-4
  2 USE [GPOSDB]
  3 GO
  4 ------------------------------------删除所有存储过程-----------------------------------
  5 --select * from sys.procedures
  6
  7 declare @sql varchar(4000)
  8 set @sql=''
  9 select @sql=@sql+'drop proc '+name+';  ' from sys.procedures
10 --print @sql
11 exec(@sql)
12
13 --------------------------------在[CT_OuterCard]表添加6个字段-------------------------------
14 ALTER TABLE [dbo].[CT_OuterCard] ADD [I_LimitTransCurrCount] INT  NOT NULL CONSTRAINT [DF_CT_OuterCard_I_LimitTransCurrCount]  DEFAULT ((0))
15 ALTER TABLE [dbo].[CT_OuterCard] ADD [I_LimitTransType] [int] CONSTRAINT [DF_CT_OuterCard_I_LimitTransType]  DEFAULT ((0))
16 ALTER TABLE [dbo].[CT_OuterCard] ADD [DE_LimitTransTotal] [decimal](18, 2) NOT NULL CONSTRAINT [DF_CT_OuterCard_DE_LimitTransTotal]  DEFAULT ((0))
17 ALTER TABLE [dbo].[CT_OuterCard] ADD [DE_LimitTransCurrTotal] [decimal](18, 2) NOT NULL CONSTRAINT [DF_CT_OuterCard_DE_LimitTransCurrTotal]  DEFAULT ((0))
18 ALTER TABLE [dbo].[CT_OuterCard] ADD [I_LimitCarNo] [int] NOT NULL CONSTRAINT [DF_CT_OuterCard_I_LimitCarNo]  DEFAULT ((0))
19 ALTER TABLE [dbo].[CT_OuterCard] ADD [D_LimitDate] [datetime] NOT NULL CONSTRAINT [DF_CT_OuterCard_D_LimitDate]  DEFAULT (getdate())
20 --------------------------------------------------------------------------------------------------------------
21 --把刚才在新建查询窗口里生成的存储过程脚本粘贴到下面
22 ---------------------------创建GPOS1.1的所有存储过程---------------------------------------------
23 USE [GPOSDB]
24 GO
25 /****** 对象:  StoredProcedure [dbo].[Report_GreaserSaleStat]  脚本日期: 07/04/2013 13:27:09 ******/
26 SET ANSI_NULLS OFF
27 GO
28 SET QUOTED_IDENTIFIER OFF
29 GO
30
31
32 CREATE PROC [dbo].[Report_GreaserSaleStat]
33 @StartDate datetime,
34 @EndDate datetime,
35 @Action int --0为交易记录，1为班次记录
36
37 
38  insert into #tmpCardAmoutStat
39  (
40  VC_OC_CardNO,
41 
42  set @i=@i+1
43  end
44
45  truncate table #tmpCards
46  insert into #tmpCards(VC_OC_CardNO)
47  select VC_OC_CardNO from CT_OuterCard where isnull(VC_OC_Company,'')=''
48  set @j=1
49  select @cardcount=count(*) from #tmpCards
50  while @j<=@cardcount
51  begin
52  select @VC_OC_CardNO=VC_OC_CardNO from #tmpCards where  IndexId=@j
53  insert into #tmpCardAmoutStat
54  (
55  VC_OC_CardNO,
56 
57
58  insert into #tmpCardAmoutStat
59  (
60  VC_OC_CardNO,
61  CompanyName,
62  VC_OC_UserName,
63  StartAmount,
64  FillMoney,
65  ConsumeSumVol,
66  ConsumeMoney,
67  SumConsumeSumVol,
68  SumConsumeMoney,
69  SumFillMoney
70
71  )
72  select
73  null,
74  null,
75  '客户卡小计',
76  sum(StartAmount),
77  sum(FillMoney),
78  sum(ConsumeSumVol),
79 
80  truncate table #tmpCards
81  insert into #tmpCards(VC_OC_CardNO)
82  select VC_IC_CardNO from CT_InhouseCard where isnull(VC_IC_CardNO,'')<>''
83  set @j=1
84  select @cardcount=count(*) from #tmpCards
85  while @j<=@cardcount
86  begin
87  select @VC_OC_CardNO=VC_OC_CardNO from #tmpCards where  IndexId=@j
88  insert into #tmpCardAmoutStat
89  (
90  VC_OC_CardNO,
91  CompanyName,
92  VC_OC_UserName,
93  StartAmount,
94  FillMoney,
95  ConsumeSumVol,
96  ConsumeMoney,
97  SumConsumeSumVol,
98  SumConsumeMoney,
99  SumFillMoney
100
101  )
102  select
103  @VC_OC_CardNO,
104  '员工卡',
105  isnull((select VC_IC_UserName from CT_InhouseCard where VC_IC_CardNO=@VC_OC_CardNO),''),
106  isnull((select top 1 DE_FD_Amount from CT_FuelingData where VC_FD_Cardno=@VC_OC_CardNO and (D_FD_DateTime<=@StartDate) order by D_FD_DateTime desc),0),
107  isnull((select sum(DE_A_AppendAmount) from CT_Append where VC_A_CardNO=@VC_OC_CardNO and (D_A_AppendDateTime between @StartDate and @EndDate)),0),
108  isnull((select sum(DE_FD_Volume) from CT_FuelingData where VC_FD_Cardno=@VC_OC_CardNO and (D_FD_DateTime between @StartDate and @EndDate)),0),
109  isnull((select sum(DE_FD_Amount) from CT_FuelingData where VC_FD_Cardno=@VC_OC_CardNO and (D_FD_DateTime between @StartDate and @EndDate)),0),
110  isnull((select sum(DE_FD_Volume) from CT_FuelingData where VC_FD_Cardno=@VC_OC_CardNO),0),
111  isnull((select sum(DE_FD_Amount) from CT_FuelingData where VC_FD_Cardno=@VC_OC_CardNO),0),
112  isnull((select sum(DE_A_AppendAmount) from CT_Append where VC_A_CardNO=@VC_OC_CardNO),0)
113  set @j=@j+1
114  end
115
116  insert into #tmpCardAmoutStat
117  (
118  VC_OC_CardNO,
119  CompanyName,
120  VC_OC_UserName,
121  StartAmount,
122  FillMoney,
123  ConsumeSumVol,
124  ConsumeMoney,
125  SumConsumeSumVol,
126  SumConsumeMoney,
127  SumFillMoney
128
129  )
130  select
131  null,
132  null,
133  '员工卡小计',
134  sum(StartAmount),
135  sum(FillMoney),
136  sum(ConsumeSumVol),
137  sum(ConsumeMoney),
138  sum(SumConsumeSumVol),
139  sum(SumConsumeMoney),
140  sum(SumFillMoney)
141  from
142  #tmpCardAmoutStat
143  where
144  CompanyName='员工卡'
145  ---计算员工卡汇总结束---
146  end
147 
148  ----计算总汇总开始---
149  insert into #tmpCardAmoutStat
150  (
151  VC_OC_CardNO,
152  CompanyName,
153  VC_OC_UserName,
154  StartAmount,
155  FillMoney,
156  ConsumeSumVol,
157  ConsumeMoney,
158  SumConsumeSumVol,
159  SumConsumeMoney,
160  SumFillMoney
161
162  )
163  select
164  null,
165  null,
166  '总计',
167  sum(StartAmount),
168  sum(FillMoney),
169  sum(ConsumeSumVol),
170  sum(ConsumeMoney),
171  sum(SumConsumeSumVol),
172  sum(SumConsumeMoney),
173  sum(SumFillMoney)
174  from
175  #tmpCardAmoutStat
176  where
177  (VC_OC_UserName='客户卡小计' or VC_OC_UserName='员工卡小计') and VC_OC_CardNO is null
178  update #tmpCardAmoutStat set EndAmount=StartAmount+FillMoney-ConsumeMoney
179  ---计算总汇总结束---
180  select * from  #tmpCardAmoutStat
181
182  drop table #tmpCards
183  drop table #tmpCompanys
184  drop table #tmpCardAmoutStat
185 GO
186
187 --其他存储过程省略。。。。。。。。。。。