· 7 years ago · Sep 10, 2018, 11:38 AM
1<?xml version="1.0" encoding="ISO-8859-1"?>
2<?xml-stylesheet type="text/xsl" href="table.xsl"?>
3
4<application debug="false" beans="com.wanmei.db.passport" beanbase="src/beans">
5
6<connection name="auth0" poolsize="8" url="jdbc:microsoft:sqlserver://192.168.0.2:1433;databasename=PassportBOI" username="sa" password="passportboi"/>
7<driver name="com.microsoft.jdbc.sqlserver.SQLServerDriver" />
8
9<cache name="account_by_id" type="reference" ip="226.1.1.1" port="4444" local_ip="192.168.0.107" size="262144" partition="0"/>
10<cache name="account_by_name" type="reference" ip="226.1.1.1" port="4445" local_ip="192.168.0.107" size="262144" partition="0"/>
11
12<cache name="function_by_id" type="direct" ip="226.1.1.2" port="4446" local_ip="192.168.0.107" size="262144" partition="0"/>
13<cache name="forbid_by_id" type="direct" ip="226.1.1.3" port="4447" local_ip="192.168.0.107" size="262144" partition="0"/>
14<cache name="matrix_by_uid" type="direct" ip="226.1.1.4" port="4448" local_ip="192.168.0.107" size="262144" partition="0"/>
15
16<cache name="phoneuser_by_phone" type="direct" ip="226.1.1.5" port="4449" local_ip="192.168.0.107" size="262144" partition="0"/>
17<cache name="userinfo_by_id" type="direct" ip="226.1.1.6" port="4450" local_ip="192.168.0.107" size="131072" partition="0"/>
18
19
20
21<table name="auth" connection="auth0" operate="replaceA">
22 <column name="userid" sql-type="integer" java-type="java.lang.Integer" not-null="true" />
23 <column name="zoneid" sql-type="integer" java-type="java.lang.Integer" not-null="true" />
24 <column name="rid" sql-type="integer" java-type="java.lang.Integer" not-null="true" />
25 <primarykey name="pk_id" column="userid, zoneid, rid" />
26</table>
27
28<table name="awardnew" connection="auth0" operate="replaceA">
29 <column name="rid" sql-type="integer" java-type="java.lang.Integer" not-null="true" />
30 <column name="number" sql-type="char(8)" java-type="java.lang.String" not-null="true" />
31
32 <primarykey name="pk_awardnew" column="number" />
33 <index name="ind_awardnew_rid" column="rid" />
34</table>
35
36<table name="awardrecord" connection="auth0" operate="replaceA">
37 <column name="id" sql-type="integer" java-type="java.lang.Integer" not-null="true" />
38 <column name="code" sql-type="varchar(8)" java-type="java.lang.String" not-null="true" />
39 <column name="number" sql-type="integer" java-type="java.lang.Integer" not-null="true" />
40 <column name="endtime" sql-type="datetime" java-type="java.lang.String" not-null="true" />
41 <column name="creator" sql-type="varchar(64)" java-type="java.lang.String" not-null="true" />
42 <column name="creatime" sql-type="datetime" java-type="java.lang.String" not-null="true" />
43 <column name="auditor" sql-type="varchar(64)" java-type="java.lang.String" not-null="true" />
44 <column name="auditime" sql-type="datetime" java-type="java.lang.String" not-null="true" />
45 <column name="status" sql-type="integer" java-type="java.lang.Integer" not-null="true" />
46 0: create 1: available 2: overdue
47 <column name="func" sql-type="integer" java-type="java.lang.Integer" not-null="true" />
48 <column name="used" sql-type="integer" java-type="java.lang.Integer" not-null="true" />
49 <primarykey name="pk_awardrecord" column="id" />
50</table>
51
52<table name="awardused" connection="auth0" operate="replaceA">
53 <column name="rid" sql-type="integer" java-type="java.lang.Integer" not-null="true" />
54 <column name="number" sql-type="varchar(32)" java-type="java.lang.String" not-null="true" />
55 <column name="namefrom" sql-type="varchar(32)" java-type="java.lang.String" not-null="true" />
56 <column name="nameto" sql-type="varchar(32)" java-type="java.lang.String" not-null="true" />
57 <column name="usedate" sql-type="datetime" java-type="java.lang.String" not-null="true" />
58 <column name="ip" sql-type="integer" java-type="java.lang.Integer" not-null="true" />
59
60 <primarykey name="pk_awardused" column="number" />
61 <index name="ind_nameto" column="nameto" />
62 <index name="ind_usedate" column="usedate" />
63</table>
64
65<table name="cardnew" connection="auth0" operate="replaceA">
66 <column name="rid" sql-type="integer" java-type="java.lang.Integer" not-null="true" />
67 <column name="number" sql-type="char(25)" java-type="java.lang.String" not-null="true" />
68 <column name="status" sql-type="tinyint" java-type="java.lang.Integer" not-null="true" />
69 0: create 1: available 2: overdue
70 <primarykey name="pk_cardnew" column="number" />
71 <index name="ind_cardnew_rid" column="rid" />
72</table>
73
74<table name="cardrecord" connection="auth0" operate="replaceA">
75 <column name="id" sql-type="integer" java-type="java.lang.Integer" not-null="true" />
76 <column name="code" sql-type="varchar(12)" java-type="java.lang.String" not-null="true" />
77 <column name="number" sql-type="integer" java-type="java.lang.Integer" not-null="true" />
78 <column name="price" sql-type="integer" java-type="java.lang.Integer" not-null="true" />
79 <column name="rate" sql-type="integer" java-type="java.lang.Integer" not-null="true" />
80 <column name="pointcard" sql-type="integer" java-type="java.lang.Integer" not-null="true" />
81 <column name="exchangepoint" sql-type="integer" java-type="java.lang.Integer" not-null="true" />
82 <column name="endtime" sql-type="datetime" java-type="java.lang.String" not-null="true" />
83 <column name="creator" sql-type="varchar(64)" java-type="java.lang.String" not-null="true" />
84 <column name="creatime" sql-type="datetime" java-type="java.lang.String" not-null="true" />
85 <column name="auditor" sql-type="varchar(64)" java-type="java.lang.String" not-null="true" />
86 <column name="auditime" sql-type="datetime" java-type="java.lang.String" not-null="true" />
87 <column name="status" sql-type="integer" java-type="java.lang.Integer" not-null="true" />
88 0: create 1: available 2: overdue
89 <column name="func" sql-type="integer" java-type="java.lang.Integer" not-null="true" />
90 <column name="funcparm" sql-type="integer" java-type="java.lang.Integer" not-null="true" />
91 <column name="used" sql-type="integer" java-type="java.lang.Integer" not-null="true" />
92 <column name="score" sql-type="integer" java-type="java.lang.Integer" not-null="true" />
93
94 <primarykey name="pk_cardrecord" column="id" />
95</table>
96
97<table name="cardused" connection="auth0" operate="replaceA">
98 <column name="rid" sql-type="integer" java-type="java.lang.Integer" not-null="true" />
99 <column name="number" sql-type="varchar(32)" java-type="java.lang.String" not-null="true" />
100 <column name="namefrom" sql-type="varchar(32)" java-type="java.lang.String" not-null="true" />
101 <column name="nameto" sql-type="varchar(32)" java-type="java.lang.String" not-null="true" />
102 <column name="usedate" sql-type="datetime" java-type="java.util.Date" not-null="true" />
103 <column name="ip" sql-type="integer" java-type="java.lang.Integer" not-null="true" />
104 <column name="aid" sql-type="integer" java-type="java.lang.Integer" not-null="true" />
105 <column name="zoneid" sql-type="integer" java-type="java.lang.Integer" not-null="false" />
106
107 <primarykey name="pk_cardused" column="number" />
108 <index name="ind_nameto" column="nameto" />
109 <index name="ind_usedate" column="usedate" />
110</table>
111
112<table name="cardstatuslog" connection="auth0" operate="replaceA">
113 <column name="date" sql-type="datetime" java-type="java.util.Date" not-null="true" />
114 <column name="fromnumber" sql-type="char(25)" java-type="java.lang.String" not-null="true" />
115 <column name="tonumber" sql-type="char(25)" java-type="java.lang.String" not-null="true" />
116 <column name="fromstatus" sql-type="tinyint" java-type="java.lang.Integer" not-null="true" />
117 <column name="tostatus" sql-type="tinyint" java-type="java.lang.Integer" not-null="true" />
118 <column name="op" sql-type="varchar(32)" java-type="java.lang.String" not-null="true" />
119 <column name="retcount" sql-type="integer" java-type="java.lang.Integer" not-null="true" />
120 <primarykey name="pk_cardstatuslog" column="date" />
121</table>
122
123<table name="forbid" connection="auth0" operate="replaceA">
124 <column name="userid" sql-type="integer" java-type="java.lang.Integer" not-null="true" />
125 <column name="type" sql-type="integer" java-type="java.lang.Integer" not-null="true" />
126 <column name="ctime" sql-type="datetime" java-type="java.util.Date" not-null="true" />
127 <column name="forbid_time" sql-type="integer" java-type="java.lang.Integer" not-null="true" />
128 <column name="reason" sql-type="varbinary(255)" java-type="byte[]" not-null="true" />
129 <column name="gmroleid" sql-type="integer" java-type="java.lang.Integer" not-null="false"/>
130
131 <primarykey name="pk_id" column="userid,type" />
132</table>
133
134<table name="point" connection="auth0" operate="replaceA">
135 <column name="uid" sql-type="integer" java-type="java.lang.Integer" not-null="true" />
136 <column name="aid" sql-type="integer" java-type="java.lang.Integer" not-null="true" />
137 <column name="time" sql-type="integer" java-type="java.lang.Integer" not-null="true" />
138 <column name="zoneid" sql-type="integer" java-type="java.lang.Integer" not-null="false" />
139 <column name="zonelocalid" sql-type="integer" java-type="java.lang.Integer" not-null="false" />
140 <column name="accountstart" sql-type="datetime" java-type="java.util.Date" not-null="false" />
141 <column name="lastlogin" sql-type="datetime" java-type="java.util.Date" not-null="false" />
142 <column name="enddate" sql-type="datetime" java-type="java.util.Date" not-null="false" />
143
144 <primarykey name="pk_id" column="uid, aid" />
145 <index name="ind_aid" column="aid,zoneid" />
146</table>
147
148<table name="point_free" connection="auth0" operate="replaceA">
149 <column name="uid" sql-type="integer" java-type="java.lang.Integer" not-null="true" />
150 <column name="aid" sql-type="integer" java-type="java.lang.Integer" not-null="true" />
151 <column name="zoneid" sql-type="integer" java-type="java.lang.Integer" not-null="true" />
152 <column name="lastlogin" sql-type="datetime" java-type="java.util.Date" not-null="true" />
153
154 <primarykey name="pk_id" column="uid, aid" />
155</table>
156
157<table name="function" connection="auth0" operate="replaceA">
158 <column name="uid" sql-type="integer" java-type="java.lang.Integer" not-null="true" />
159 <column name="adduptime" sql-type="integer" java-type="java.lang.Integer" not-null="false" />
160 <column name="score" sql-type="integer" java-type="java.lang.Integer" not-null="false" />
161 <column name="func" sql-type="integer" java-type="java.lang.Integer" not-null="false" />
162 <column name="funcparm" sql-type="integer" java-type="java.lang.Integer" not-null="false" />
163 <column name="addupmoney" sql-type="integer" java-type="java.lang.Integer" not-null="false" />
164 <column name="adduppoint" sql-type="integer" java-type="java.lang.Integer" not-null="false" />
165 <column name="addupscore" sql-type="integer" java-type="java.lang.Integer" not-null="false" />
166 <column name="coin" sql-type="integer" java-type="java.lang.Integer" not-null="false" />
167 <column name="addupcoin" sql-type="integer" java-type="java.lang.Integer" not-null="false" />
168 <column name="soldpoint" sql-type="integer" java-type="java.lang.Integer" not-null="false" />
169
170 <primarykey name="pk_id" column="uid" />
171</table>
172
173<table name="users" connection="auth0" operate="replaceA">
174 <column name="id" sql-type="integer" java-type="java.lang.Integer" not-null="true" />
175 <column name="prompt" sql-type="varchar(32)" java-type="java.lang.String" not-null="true" />
176 <column name="answer" sql-type="varchar(32)" java-type="java.lang.String" not-null="true" />
177 <column name="truename" sql-type="varchar(32)" java-type="java.lang.String" not-null="true" />
178 <column name="idnumber" sql-type="varchar(32)" java-type="java.lang.String" not-null="true" />
179 <column name="email" sql-type="varchar(64)" java-type="java.lang.String" not-null="true" />
180 <column name="mobilenumber" sql-type="varchar(32)" java-type="java.lang.String" not-null="false" />
181 <column name="province" sql-type="varchar(32)" java-type="java.lang.String" not-null="false" />
182 <column name="city" sql-type="varchar(32)" java-type="java.lang.String" not-null="false" />
183 <column name="phonenumber" sql-type="varchar(32)" java-type="java.lang.String" not-null="false" />
184 <column name="address" sql-type="varchar(64)" java-type="java.lang.String" not-null="false" />
185 <column name="postalcode" sql-type="varchar(8)" java-type="java.lang.String" not-null="false" />
186 <column name="gender" sql-type="integer" java-type="java.lang.Integer" not-null="false" />
187 <column name="birthday" sql-type="datetime" java-type="java.util.Date" not-null="false" />
188
189 <column name="qq" sql-type="varchar(32)" java-type="java.lang.String" not-null="false" />
190 <column name="passwd2" sql-type="binary(16)" java-type="byte[]" not-null="false" />
191
192 <primarykey name="pk_id" column="id" />
193</table>
194
195<table name="account" connection="auth0" operate="replaceA">
196 <column name="id" sql-type="integer" java-type="java.lang.Integer" not-null="true" />
197 <column name="name" sql-type="char(16)" java-type="java.lang.String" not-null="true" />
198 <column name="passwd" sql-type="binary(16)" java-type="byte[]" not-null="true" />
199 <column name="creatime" sql-type="datetime" java-type="java.util.Date" not-null="true" />
200 <column name="usertype" sql-type="integer" java-type="java.lang.Integer" not-null="true" />
201 <primarykey name="pk_account" column="id" />
202 <index name="ind_name" unique="true" column="name" />
203 <index name="ind_creatime" column="creatime" />
204</table>
205
206<table name="usb_key" connection="auth0" operate="replaceA">
207 <column name="sn" sql-type="binary(8)" java-type="byte[]" not-null="true" />
208 <column name="passwd" sql-type="binary(16)" java-type="byte[]" not-null="true" />
209 <column name="refcnt" sql-type="integer" java-type="java.lang.Integer" not-null="true" />
210
211 <primarykey name="pk_id" column="sn" />
212</table>
213
214<table name="agentbill" connection="auth0" operate="replaceA">
215 <column name="billid" sql-type="integer" java-type="java.lang.Integer" not-null="true" />
216 <column name="agent" sql-type="char(1)" java-type="java.lang.String" not-null="true" />
217 <column name="uid" sql-type="integer" java-type="java.lang.Integer" not-null="true" />
218 <column name="aid" sql-type="integer" java-type="java.lang.Integer" not-null="true" />
219 <column name="cardtype" sql-type="integer" java-type="java.lang.Integer" not-null="true" />
220 <column name="money" sql-type="integer" java-type="java.lang.Integer" not-null="true" />
221 <column name="addpoint" sql-type="integer" java-type="java.lang.Integer" not-null="true" />
222 <column name="addscore" sql-type="integer" java-type="java.lang.Integer" not-null="true" />
223 <column name="usedate" sql-type="datetime" java-type="java.util.Date" not-null="true" />
224 <column name="cookie1" sql-type="integer" java-type="java.lang.Integer" not-null="false" />
225 <column name="cookie2" sql-type="integer" java-type="java.lang.Integer" not-null="false" />
226 <column name="addcoin" sql-type="integer" java-type="java.lang.Integer" not-null="false" />
227 <column name="awarduid" sql-type="integer" java-type="java.lang.Integer" not-null="false" />
228 <column name="awardpoint" sql-type="integer" java-type="java.lang.Integer" not-null="false" />
229 <column name="awardscore" sql-type="integer" java-type="java.lang.Integer" not-null="false" />
230
231 <primarykey name="pk_agentbill" column="billid, agent" />
232 <index name="ind_agentbill_uidaid" column="uid,aid" />
233 <index name="ind_agentbill_usedate" column="usedate" />
234</table>
235
236<table name="max_ids" connection="auth0" operator="replaceA">
237 <column name="type" sql-type="integer" java-type="java.lang.Integer" not-null="true"/>
238 <column name="maxid" sql-type="integer" java-type="java.lang.Integer" not-null="true"/>
239 <primarykey name="pk_max_ids" column="type" />
240 <!-- agentbill_agent_"a" id = 1 -->
241</table>
242
243<table name="monthbill" connection="auth0" operate="replaceA">
244 <column name="uid" sql-type="integer" java-type="java.lang.Integer" not-null="true" />
245 <column name="aid" sql-type="integer" java-type="java.lang.Integer" not-null="true" />
246 <column name="usepoint" sql-type="integer" java-type="java.lang.Integer" not-null="true" />
247 <column name="monthcount" sql-type="integer" java-type="java.lang.Integer" not-null="true" />
248 <column name="enddate" sql-type="datetime" java-type="java.util.Date" not-null="true" />
249 <column name="usedate" sql-type="datetime" java-type="java.util.Date" not-null="true" />
250
251 <index name="ind_monthbill_uidaid" column="uid,aid" />
252 <index name="ind_monthbill_usedate" column="usedate" />
253</table>
254
255<table name="innerbill" connection="auth0" operate="replaceA">
256 <column name="uid" sql-type="integer" java-type="java.lang.Integer" not-null="true" />
257 <column name="aid" sql-type="integer" java-type="java.lang.Integer" not-null="true" />
258 <column name="addpoint" sql-type="integer" java-type="java.lang.Integer" not-null="true" />
259 <column name="operator" sql-type="varchar(19)" java-type="java.lang.String" not-null="true" />
260 <column name="enddate" sql-type="datetime" java-type="java.util.Date" not-null="false" />
261 <column name="usedate" sql-type="datetime" java-type="java.util.Date" not-null="true" />
262 <column name="monthcount" sql-type="integer" java-type="java.lang.Integer" not-null="false" />
263 <column name="addscore" sql-type="integer" java-type="java.lang.Integer" not-null="false" />
264 <column name="zoneid" sql-type="integer" java-type="java.lang.Integer" not-null="false" />
265
266 <index name="ind_innerbill_uidaid" column="uid,aid" />
267 <index name="ind_innerbill_usedate" column="usedate" />
268</table>
269
270<table name="translog" connection="auth0" operate="replaceA">
271 <column name="zoneid" sql-type="integer" java-type="java.lang.Integer" not-null="true"/>
272 <column name="serial" sql-type="integer" java-type="java.lang.Integer" not-null="true"/>
273 <column name="seller" sql-type="integer" java-type="java.lang.Integer" not-null="true"/>
274 <column name="sellid" sql-type="integer" java-type="java.lang.Integer" not-null="true"/>
275 <column name="buyer" sql-type="integer" java-type="java.lang.Integer" not-null="true"/>
276 <column name="price" sql-type="integer" java-type="java.lang.Integer" not-null="true"/>
277 <column name="point" sql-type="integer" java-type="java.lang.Integer" not-null="true"/>
278 <column name="aid" sql-type="integer" java-type="java.lang.Integer" not-null="true"/>
279 <!--status 0.abort; 1.commit;-->
280 <column name="status" sql-type="integer" java-type="java.lang.Integer" not-null="true"/>
281 <column name="date" sql-type="datetime" java-type="java.util.Date" not-null="true"/>
282
283 <primarykey name="pk_translog" column="zoneid, serial" />
284 <index name="ind_translog_seller" column="seller" />
285 <index name="ind_translog_buyer" column="buyer" />
286 <index name="ind_translog_date" column="date" />
287</table>
288
289<table name="usecashnow" connection="auth0" operate="replaceA">
290 <column name="userid" sql-type="integer" java-type="java.lang.Integer" not-null="true"/>
291 <column name="zoneid" sql-type="integer" java-type="java.lang.Integer" not-null="true"/>
292 <column name="sn" sql-type="integer" java-type="java.lang.Integer" not-null="true"/>
293 <column name="aid" sql-type="integer" java-type="java.lang.Integer" not-null="true"/>
294 <column name="point" sql-type="integer" java-type="java.lang.Integer" not-null="true"/>
295 <column name="cash" sql-type="integer" java-type="java.lang.Integer" not-null="true"/>
296 <!--status 0.queue; 1.create; 2.get sn; 3.send addcash; 4.finish; -->
297 <column name="status" sql-type="integer" java-type="java.lang.Integer" not-null="true"/>
298 <column name="creatime" sql-type="datetime" java-type="java.util.Date" not-null="true"/>
299
300 <index name="ind_usecashnow_uzs" unique="true" column="userid, zoneid, sn" />
301 <index name="ind_usecashnow_creatime" column="creatime" />
302</table>
303
304<table name="usecashlog" connection="auth0" operate="replaceA">
305 <column name="userid" sql-type="integer" java-type="java.lang.Integer" not-null="true"/>
306 <column name="zoneid" sql-type="integer" java-type="java.lang.Integer" not-null="true"/>
307 <column name="sn" sql-type="integer" java-type="java.lang.Integer" not-null="true"/>
308 <column name="aid" sql-type="integer" java-type="java.lang.Integer" not-null="true"/>
309 <column name="point" sql-type="integer" java-type="java.lang.Integer" not-null="true"/>
310 <column name="cash" sql-type="integer" java-type="java.lang.Integer" not-null="true"/>
311 <column name="status" sql-type="integer" java-type="java.lang.Integer" not-null="true"/>
312 <column name="creatime" sql-type="datetime" java-type="java.util.Date" not-null="true"/>
313 <column name="fintime" sql-type="datetime" java-type="java.util.Date" not-null="true"/>
314
315 <index name="ind_usecashlog_uzs" column="userid, zoneid, sn" />
316 <index name="ind_usecashlog_creatime" column="creatime" />
317</table>
318
319<table name="iplimit" connection="auth0" operate="replaceA">
320 <column name="uid" sql-type="integer" java-type="java.lang.Integer" not-null="true" />
321 <column name="ipaddr1" sql-type="integer" java-type="java.lang.Integer" not-null="false" />
322 <column name="ipmask1" sql-type="varchar(2)" java-type="java.lang.String" not-null="false" />
323 <column name="ipaddr2" sql-type="integer" java-type="java.lang.Integer" not-null="false" />
324 <column name="ipmask2" sql-type="varchar(2)" java-type="java.lang.String" not-null="false" />
325 <column name="ipaddr3" sql-type="integer" java-type="java.lang.Integer" not-null="false" />
326 <column name="ipmask3" sql-type="varchar(2)" java-type="java.lang.String" not-null="false" />
327 <column name="enable" sql-type="char(1)" java-type="java.lang.String" not-null="false" />
328 <column name="lockstatus" sql-type="char(1)" java-type="java.lang.String" not-null="false" />
329 <column name="autolock" sql-type="char(1)" java-type="java.lang.String" not-null="false" />
330 <primarykey name="pk_iplimit" column="uid" />
331</table>
332
333<table name="mobilekey" connection="auth0" operate="replaceA">
334 <column name="uid" sql-type="integer" java-type="java.lang.Integer" not-null="true" />
335 <column name="mobilealgorithm" sql-type="integer" java-type="java.lang.Integer" not-null="false" />
336 <column name="mobilekey" sql-type="integer" java-type="java.lang.Integer" not-null="false" />
337 <primarykey name="pk_mobilekey" column="uid" />
338</table>
339
340<table name="matrix" connection="auth0" operate="replaceA">
341 <column name="uid" sql-type="integer" java-type="java.lang.Integer" not-null="true" />
342 <column name="recordid" sql-type="integer" java-type="java.lang.Integer" not-null="true" />
343 <column name="matrixid" sql-type="char(12)" java-type="java.lang.String" not-null="true" />
344 <column name="matrix" sql-type="binary(80)" java-type="byte[]" not-null="true" />
345 <primarykey name="pk_matrix" column="uid" />
346</table>
347
348<table name="awardpoint" connection="auth0" operate="replaceA">
349 <column name="fromuid" sql-type="integer" java-type="java.lang.Integer" not-null="true" />
350 <column name="touid" sql-type="integer" java-type="java.lang.Integer" not-null="true" />
351 <column name="point" sql-type="integer" java-type="java.lang.Integer" not-null="true" />
352 <column name="score" sql-type="integer" java-type="java.lang.Integer" not-null="true" />
353 <column name="awarddate" sql-type="datetime" java-type="java.util.Date" not-null="true"/>
354 <column name="usedate" sql-type="datetime" java-type="java.util.Date" not-null="false"/>
355
356 <primarykey name="pk_awardpoint" column="fromuid" />
357 <index name="ind_awardpoint_touid" column="touid" />
358</table>
359
360<table name="matrixrecord" connection="auth0" operate="replaceA">
361 <column name="id" sql-type="integer" java-type="java.lang.Integer" not-null="true" />
362 <column name="code" sql-type="varchar(12)" java-type="java.lang.String" not-null="true" />
363 <column name="number" sql-type="integer" java-type="java.lang.Integer" not-null="true" />
364 <column name="price" sql-type="integer" java-type="java.lang.Integer" not-null="true" />
365 <column name="rate" sql-type="integer" java-type="java.lang.Integer" not-null="true" />
366 <column name="expiredtime" sql-type="datetime" java-type="java.sql.Timestamp" not-null="true" />
367 <column name="creator" sql-type="varchar(64)" java-type="java.lang.String" not-null="true" />
368 <column name="creatime" sql-type="datetime" java-type="java.lang.String" not-null="true" />
369 <column name="auditor" sql-type="varchar(64)" java-type="java.lang.String" not-null="true" />
370 <column name="auditime" sql-type="datetime" java-type="java.lang.String" not-null="true" />
371 <column name="status" sql-type="integer" java-type="java.lang.Integer" not-null="true" />
372 0: create 1: available 2: overdue
373 <column name="reserve1" sql-type="integer" java-type="java.lang.Integer" not-null="false" />
374 <column name="reserve2" sql-type="integer" java-type="java.lang.Integer" not-null="false" />
375 <column name="reserve3" sql-type="integer" java-type="java.lang.Integer" not-null="false" />
376 <column name="reserve4" sql-type="integer" java-type="java.lang.Integer" not-null="false" />
377
378 <primarykey name="pk_matrixrecord" column="id" />
379</table>
380
381<table name="matrixnew" connection="auth0" operate="replaceA">
382 <column name="recordid" sql-type="integer" java-type="java.lang.Integer" not-null="true" />
383 <column name="id" sql-type="varchar(12)" java-type="java.lang.String" not-null="true" />
384 <column name="matrix" sql-type="varbinary(80)" java-type="byte[]" not-null="false" />
385 <primarykey name="pk_id" column="id" />
386 <index name="ind_matrixnew_rid" column="recordid" />
387</table>
388
389<table name="matrixused" connection="auth0" operate="replaceA">
390 <column name="id" sql-type="char(12)" java-type="java.lang.String" not-null="true" />
391 <column name="matrix" sql-type="binary(80)" java-type="byte[]" not-null="false" />
392 <column name="userid" sql-type="integer" java-type="java.lang.Integer" not-null="false" />
393 <column name="begindate" sql-type="datetime" java-type="java.util.Date" not-null="false" />
394 <column name="canceldate" sql-type="datetime" java-type="java.util.Date" not-null="false" />
395 <column name="ip" sql-type="integer" java-type="java.lang.Integer" not-null="false" />
396 <primarykey name="pk_id" column="id" />
397 <index name="ind_matrixused_userid" column="userid" />
398</table>
399
400<table name="phone" connection="auth0" operator="replaceA">
401 <column name="uid" sql-type="integer" java-type="java.lang.Integer" not-null="true" />
402 <column name="phone" sql-type="char(16)" java-type="java.lang.String" not-null="true" />
403 <primarykey name="pk_uid" column="uid,phone" />
404 <index name="ind_phone" column="phone" />
405</table>
406
407<table name="onlineinfo" connection="auth0" operate="replaceA">
408 <column name="account_pay" sql-type="integer" java-type="java.lang.Integer" not-null="true" />
409 <column name="account_pay_now" sql-type="integer" java-type="java.lang.Integer" not-null="true" />
410 <column name="account_month" sql-type="integer" java-type="java.lang.Integer" not-null="true" />
411 <column name="account_month_now" sql-type="integer" java-type="java.lang.Integer" not-null="true" />
412 <column name="online_total" sql-type="integer" java-type="java.lang.Integer" not-null="true" />
413 <column name="online_pay" sql-type="integer" java-type="java.lang.Integer" not-null="true" />
414 <column name="online_month" sql-type="integer" java-type="java.lang.Integer" not-null="true" />
415 <column name="online_freesvr" sql-type="integer" java-type="java.lang.Integer" not-null="true" />
416 <column name="online_freesvr_payed" sql-type="integer" java-type="java.lang.Integer" not-null="true" />
417 <column name="statdate" sql-type="datetime" java-type="java.util.Date" not-null="true" />
418
419 <index name="ind_onlineinfo_statdate" column="statdate" />
420</table>
421
422<table name="indulge" connection="auth0" operator="replaceA">
423 <column name="id" sql-type="integer" java-type="java.lang.Integer" not-null="true" />
424 <column name="name" sql-type="varbinary(32)" java-type="byte[]" not-null="true" />
425 <column name="idcard" sql-type="char(18)" java-type="java.lang.String" not-null="true" />
426 <column name="verfied" sql-type="integer" java-type="java.lang.Integer" not-null="true" />
427 <primarykey name="pk_indulge" column="id" />
428 <index name="ind_indulge_idcard" column="idcard" />
429</table>
430
431<table name="activecode" connection="auth0" operator="replaceA">
432 <column name="code" sql-type="varchar(16)" java-type="java.lang.String" not-null="true" />
433 <primarykey name="pk_activecode" column="code" />
434</table>
435
436<query name="getIndulgeUserInfo">
437 <table name="indulge" alias="i" />
438 <column name="id" column="i.id" />
439 <column name="name" column="i.name" />
440 <column name="idcard" column="i.idcard" />
441 <column name="verfied" column="i.verfied" />
442 <select name="byId" condition="WHERE i.id = ?"/>
443 <select name="byName" condition="WHERE i.id = (SELECT id FROM account WHERE name=?)"/>
444 <select name="byIdcard" condition="WHERE i.idcard = ?"/>
445</query>
446
447<procedure name="existsIndulge" connection="auth0" operate="replaceB">
448 <parameter name="idcard" sql-type="char(18)" java-type="java.lang.String" in="true" out="false" />
449 <parameter name="truename" sql-type="varbinary(32)" java-type="byte[]" in="true" out="false" />
450 <parameter name="uid" sql-type="integer" java-type="java.lang.Integer" in="false" out="true" />
451 <parameter name="verfied" sql-type="integer" java-type="java.lang.Integer" in="false" out="true" />
452 SELECT TOP 1 @uid = id, @verfied = verfied FROM indulge WHERE idcard = @idcard AND @truename = name
453 IF @@rowcount = 1
454 return 1
455 return 0
456</procedure>
457
458<procedure name="recordIndulgeUser" connection="auth0" operate="replaceB">
459 <parameter name="name" sql-type="varchar(64)" java-type="java.lang.String" in="true" out="false" cache="account_by_name"/>
460 <parameter name="truename" sql-type="varbinary(32)" java-type="byte[]" in="true" out="false" />
461 <parameter name="idcard" sql-type="char(18)" java-type="java.lang.String" in="true" out="false" />
462 <parameter name="verfied" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
463 BEGIN TRAN
464 DECLARE @uid integer
465 SELECT @uid = id FROM account WHERE name = @name
466 IF @@rowcount = 1
467 BEGIN
468 UPDATE indulge SET name = @truename, idcard = @idcard, verfied = @verfied WHERE id = @uid
469 IF @@rowcount = 0
470 INSERT INTO indulge (id, name, idcard, verfied) VALUES (@uid, @truename, @idcard, @verfied)
471 IF @@rowcount = 0
472 BEGIN
473 COMMIT TRAN
474 return 1
475 END
476
477 IF @verfied = 1 OR @verfied = 17
478 UPDATE account SET usertype = usertype | 1572864 WHERE id = @uid --0x180000
479 ELSE
480 UPDATE account SET usertype = (usertype & ~1048576) | 524288 WHERE id = @uid --0x80000
481 COMMIT TRAN
482 return 0
483 END
484 COMMIT TRAN
485 return 1
486</procedure>
487
488<procedure name="updateIndulge" connection="auth0" operate="replaceB">
489 <parameter name="uid" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" cache="account_by_id"/>
490 <parameter name="verfied" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
491 BEGIN TRAN
492 UPDATE indulge SET verfied = @verfied WHERE id = @uid
493 IF @@rowcount = 1
494 BEGIN
495 IF @verfied = 1 OR @verfied = 17
496 UPDATE account SET usertype = usertype | 1572864 WHERE id = @uid --0x180000
497 ELSE
498 UPDATE account SET usertype = (usertype & ~1048576) WHERE id = @uid --0x100000
499 COMMIT TRAN
500 return 0
501 END
502 COMMIT TRAN
503 return 1
504</procedure>
505
506<procedure name="addactivecode" connection="auth0" operate="replaceA">
507 <parameter name="code0" sql-type="varchar(16)" java-type="java.lang.String" in="true" out="false" />
508 <parameter name="code1" sql-type="varchar(16)" java-type="java.lang.String" in="true" out="false" />
509 <parameter name="code2" sql-type="varchar(16)" java-type="java.lang.String" in="true" out="false" />
510 <parameter name="code3" sql-type="varchar(16)" java-type="java.lang.String" in="true" out="false" />
511 <parameter name="code4" sql-type="varchar(16)" java-type="java.lang.String" in="true" out="false" />
512 <parameter name="code5" sql-type="varchar(16)" java-type="java.lang.String" in="true" out="false" />
513 <parameter name="code6" sql-type="varchar(16)" java-type="java.lang.String" in="true" out="false" />
514 <parameter name="code7" sql-type="varchar(16)" java-type="java.lang.String" in="true" out="false" />
515 <parameter name="code8" sql-type="varchar(16)" java-type="java.lang.String" in="true" out="false" />
516 <parameter name="code9" sql-type="varchar(16)" java-type="java.lang.String" in="true" out="false" />
517 BEGIN TRAN
518 IF EXISTS (SELECT * FROM activecode HOLDLOCK WHERE code IN(@code0,@code1,@code2,@code3,@code4,@code5,@code6,@code7,@code8,@code9))
519 BEGIN
520 rollback tran
521 return -1
522 END
523 INSERT INTO activecode (code) VALUES (@code0)
524 INSERT INTO activecode (code) VALUES (@code1)
525 INSERT INTO activecode (code) VALUES (@code2)
526 INSERT INTO activecode (code) VALUES (@code3)
527 INSERT INTO activecode (code) VALUES (@code4)
528 INSERT INTO activecode (code) VALUES (@code5)
529 INSERT INTO activecode (code) VALUES (@code6)
530 INSERT INTO activecode (code) VALUES (@code7)
531 INSERT INTO activecode (code) VALUES (@code8)
532 INSERT INTO activecode (code) VALUES (@code9)
533 COMMIT TRAN
534</procedure>
535
536<procedure name="activeUser" connection="auth0" operate="replaceB">
537 <parameter name="name" sql-type="varchar(64)" java-type="java.lang.String" in="true" out="false" cache="account_by_name" />
538 <parameter name="code" sql-type="varchar(16)" java-type="java.lang.String" in="true" out="false" />
539 <parameter name="type" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
540 <parameter name="error" sql-type="integer" java-type="java.lang.Integer" in="false" out="true" />
541 BEGIN TRAN
542 DECLARE @usertype integer
543 SELECT @error = -1
544 SELECT @usertype = usertype FROM account WHERE name = @name
545 IF @@rowcount = 1
546 BEGIN
547 IF 16>@type AND NOT (@usertype&@type) = @type
548 BEGIN
549 DELETE FROM activecode WHERE code = @code
550 IF @@rowcount = 1
551 BEGIN
552 UPDATE account SET usertype = usertype | @type WHERE name = @name
553 IF @@rowcount = 1
554 SELECT @error = 0
555 ELSE
556 INSERT INTO activecode ( code ) VALUES ( @code )
557 END
558 ELSE
559 SELECT @error = 3
560 END
561 ELSE
562 SELECT @error = 2
563 END
564 ELSE
565 SELECT @error = 1
566 COMMIT TRAN
567</procedure>
568
569<query name="getActivecode">
570 <table name="activecode" alias="a" />
571 <column name="code" column="a.code" />
572 <select name="byCode" condition="WHERE a.code = ?" />
573</query>
574
575<query name="getUserid">
576 <table name="account" alias="u" />
577 <column name="id" column="u.id" />
578 <select name="byName" condition="WHERE u.name = ?" />
579</query>
580
581<query name="getUserInfo">
582 <table name="users" alias="u" />
583 <column name="id" column="u.id" />
584 <column name="prompt" column="u.prompt" />
585 <column name="answer" column="u.answer" />
586 <column name="truename" column="u.truename" />
587 <column name="idnumber" column="u.idnumber" />
588 <column name="email" column="u.email" />
589 <column name="mobilenumber" column="u.mobilenumber" />
590 <column name="province" column="u.province" />
591 <column name="city" column="u.city" />
592 <column name="phonenumber" column="u.phonenumber" />
593 <column name="address" column="u.address" />
594 <column name="postalcode" column="u.postalcode" />
595 <column name="gender" column="u.gender" />
596 <column name="birthday" column="u.birthday" />
597 <column name="qq" column="u.qq" />
598 <select name="byId" condition="WHERE u.id = ?" cache="userinfo_by_id" key="id"/>
599</query>
600
601<query name="getUsername">
602 <table name="account" alias="u" />
603 <column name="name" column="u.name" />
604 <select name="byId" condition="WHERE u.id = ?"/>
605</query>
606
607<procedure name="tryLogin" connection="auth0" operate="replaceB">
608 <parameter name="name" sql-type="varchar(64)" java-type="java.lang.String" in="true" out="false" />
609 <parameter name="passwd" sql-type="binary(16)" java-type="byte[]" in="true" out="false" />
610 IF EXISTS ( SELECT * FROM account WHERE @name = name AND @passwd = passwd )
611 return 1
612 ELSE
613 return 0
614</procedure>
615
616<query name="getUserOnlineInfo">
617 <table name="point" alias="p"/>
618 <column name="zoneid" column="p.zoneid"/>
619 <column name="localsid" column="p.zonelocalid"/>
620 <column name="aid" column="p.aid"/>
621 <select name="byUid" condition="WHERE p.uid = ?"/>
622</query>
623
624<query name="getIPLimit">
625 <table name="iplimit" alias="i" />
626 <column name="uid" column="i.uid" />
627 <column name="ipaddr1" column="i.ipaddr1" />
628 <column name="ipmask1" column="i.ipmask1" />
629 <column name="ipaddr2" column="i.ipaddr2" />
630 <column name="ipmask2" column="i.ipmask2" />
631 <column name="ipaddr3" column="i.ipaddr3" />
632 <column name="ipmask3" column="i.ipmask3" />
633 <column name="enable" column="i.enable" />
634 <column name="lockstatus" column="i.lockstatus" />
635 <column name="autolock" column="i.autolock" />
636 <select name="byUid" condition="WHERE i.uid = ?"/>
637</query>
638
639<query name="acquireIdPasswd">
640 <table name="account" alias="u" />
641 <column name="id" column="u.id" />
642 <column name="name" compute="rtrim(u.name)" java-type="String" />
643 <column name="passwd" column="u.passwd" />
644 <column name="creatime" column="u.creatime" />
645 <column name="usertype" column="u.usertype" />
646 <select name="byName" condition="WHERE u.name = ?" cache="account_by_name" key="name"/>
647 <select name="byId" condition="WHERE u.id = ?" cache="account_by_id" key="id"/>
648</query>
649
650<query name="acquireIdPasswd2">
651 <table name="account" alias="a" />
652 <table name="users" alias="u" />
653 <column name="id" column="u.id" />
654 <column name="passwd2" column="u.passwd2" />
655 <select name="byName" condition="WHERE a.id=u.id AND a.name = ?"/>
656</query>
657
658<query name="acquireForbid" cachevalue="multi">
659 <table name="forbid" alias="fb"/>
660 <column name="userid" column="fb.userid"/>
661 <column name="type" column="fb.type"/>
662 <column name="ctime" column="fb.ctime"/>
663 <column name="forbid_time" column="fb.forbid_time"/>
664 <column name="reason" column="fb.reason"/>
665 <column name="gmroleid" column="fb.gmroleid"/>
666 <select name="byUid" condition="WHERE fb.userid = ?"
667 cache="forbid_by_id" key="userid"/>
668</query>
669
670<query name="acquireUserPrivilege">
671 <table name="auth" alias="au" />
672 <column name="rid" column="au.rid" />
673 <select name="byUidZid" condition="WHERE au.userid = ? AND au.zoneid = ?"/>
674</query>
675
676<query name="acquireUserCreatime">
677 <table name="account" alias="u" />
678 <column name="creatime" column="u.creatime" />
679 <select name="byUid" condition="WHERE u.id = ?"/>
680</query>
681
682<query name="acquireUserType">
683 <table name="account" alias="u" />
684 <column name="usertype" column="u.usertype" />
685 <select name="byName" condition="WHERE u.name = ?"/>
686</query>
687
688<query name="acquirePrivilege">
689 <table name="auth" alias="au" />
690 <table name="account" alias="u" />
691 <column name="userid" column="au.userid" />
692 <column name="zoneid" column="au.zoneid" />
693 <column name="rid" column="au.rid" />
694 <column name="name" column="u.name" />
695 <column name="creatime" column="u.creatime" />
696 <select name="byZid" condition="WHERE au.userid = u.id AND au.zoneid = ?"/>
697 <select name="byUid" condition="WHERE au.userid = u.id AND au.userid = ?"/>
698 <select name="byUidZid" condition="WHERE au.userid = u.id AND au.userid = ? AND au.zoneid = ?"/>
699 <select name="byRidZid" condition="WHERE au.userid = u.id AND au.rid = ? AND au.zoneid = ?"/>
700 <select name="byAll" condition="WHERE au.userid = u.id"/>
701</query>
702
703<query name="getfunction" cachevalue="empty">
704 <table name="function" alias="f"/>
705 <column name="uid" column="f.uid"/>
706 <column name="adduptime" column="f.adduptime"/> <!-- cached but not updated immediately -->
707 <column name="score" column="f.score"/>
708 <column name="func" column="f.func"/>
709 <column name="funcparm" column="f.funcparm"/>
710 <column name="addupmoney" column="f.addupmoney"/>
711 <column name="adduppoint" column="f.adduppoint"/>
712 <column name="addupscore" column="f.addupscore"/>
713 <column name="coin" column="f.coin"/>
714 <column name="addupcoin" column="f.addupcoin"/>
715 <column name="soldpoint" column="f.soldpoint"/>
716
717 <select name="byuid" condition="WHERE f.uid = ?" cache="function_by_id" key="uid"/>
718</query>
719
720<query name="getusecashnow">
721 <table name="usecashnow" alias="u"/>
722 <column name="userid" column="u.userid"/>
723 <column name="zoneid" column="u.zoneid"/>
724 <column name="sn" column="u.sn"/>
725 <column name="aid" column="u.aid"/>
726 <column name="point" column="u.point"/>
727 <column name="cash" column="u.cash"/>
728 <column name="status" column="u.status"/>
729 <column name="creatime" column="u.creatime"/>
730
731 <select name="bystatus" condition="WHERE status = ? AND creatime < dateadd(second,-200,getdate())"/>
732 <select name="byuserzone" condition="WHERE u.userid = ? AND u.zoneid = ?"/>
733 <select name="byuserzonesn" condition="WHERE u.userid = ? AND u.zoneid = ? AND u.sn = ?"/>
734 <select name="byuser" condition="WHERE u.userid = ?"/>
735</query>
736
737<query name="getusecashlog">
738 <table name="usecashlog" alias="u"/>
739 <column name="userid" column="u.userid"/>
740 <column name="zoneid" column="u.zoneid"/>
741 <column name="sn" column="u.sn"/>
742 <column name="aid" column="u.aid"/>
743 <column name="point" column="u.point"/>
744 <column name="cash" column="u.cash"/>
745 <column name="status" column="u.status"/>
746 <column name="creatime" column="u.creatime"/>
747 <column name="fintime" column="u.fintime"/>
748
749 <select name="byuserzone" condition="WHERE u.userid = ? AND u.zoneid = ?"/>
750 <select name="byuserzonesn" condition="WHERE u.userid = ? AND u.zoneid = ? AND u.sn = ?"/>
751 <select name="byuser" condition="WHERE u.userid = ?"/>
752</query>
753
754<procedure name="delUserPriv" connection="auth0" operate="replaceB">
755 <parameter name="userid" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" cache="account_by_id" />
756 <parameter name="zoneid" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
757 <parameter name="rid" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
758 <parameter name="deltype" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
759 BEGIN TRAN
760 IF @deltype = 0 DELETE FROM auth WHERE userid = @userid AND zoneid = @zoneid AND rid = @rid
761 ELSE IF @deltype = 1 DELETE FROM auth WHERE userid = @userid AND zoneid = @zoneid
762 ELSE IF @deltype = 2 DELETE FROM auth WHERE userid = @userid
763 IF NOT EXISTS (SELECT * FROM auth WHERE userid = @userid)
764 UPDATE account SET usertype = usertype & ~131072 WHERE id = @userid --0x20000
765 COMMIT TRAN
766</procedure>
767
768<procedure name="addUserPriv" connection="auth0" operate="replaceB">
769 <parameter name="userid" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" cache="account_by_id" />
770 <parameter name="zoneid" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
771 <parameter name="rid" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
772 BEGIN TRAN
773 INSERT INTO auth VALUES( @userid, @zoneid, @rid )
774 UPDATE account SET usertype = usertype|131072 WHERE id = @userid --0x20000
775 COMMIT TRAN
776</procedure>
777
778<procedure name="changePasswd" connection="auth0" operate="replaceB">
779 <parameter name="name" sql-type="varchar(64)" java-type="java.lang.String" in="true" out="false" cache="account_by_name"/>
780 <parameter name="passwd" sql-type="binary(16)" java-type="byte[]" in="true" out="false" />
781 UPDATE account SET passwd=@passwd WHERE name=@name
782</procedure>
783
784<procedure name="changePasswd2" connection="auth0" operate="replaceB">
785 <!-- changePasswd2 not need userinfo_by_id. getUserInfo do not has passwd2-->
786 <parameter name="name" sql-type="varchar(64)" java-type="java.lang.String" in="true" out="false"/>
787 <parameter name="passwd2" sql-type="binary(16)" java-type="byte[]" in="true" out="false" />
788 UPDATE users SET passwd2=@passwd2 FROM users u,account a WHERE a.name=@name AND a.id=u.id
789</procedure>
790
791<procedure name="queryusbkey" connection="auth0" operate="replaceA"> <!-- DB no update -->
792 <parameter name="sn" sql-type="binary(8)" java-type="byte[]" in="true" out="false" />
793 <parameter name="passwd" sql-type="binary(16)" java-type="byte[]" in="false" out="true"/>
794 SELECT @passwd = NULL
795 SELECT @passwd = passwd FROM usb_key WHERE sn = @sn
796 IF (@@rowcount != 1)
797 BEGIN
798 return -1
799 END
800</procedure>
801
802<procedure name="bindKey" connection="auth0" operate="replaceB">
803 <parameter name="name" sql-type="varchar(64)" java-type="java.lang.String" in="true" out="false" cache="account_by_name" />
804 <parameter name="sn" sql-type="binary(8)" java-type="byte[]" in="true" out="false" />
805 <parameter name="error" sql-type="integer" java-type="java.lang.Integer" in="false" out="true"/>
806 BEGIN TRAN
807 DECLARE @passwd binary(16)
808 DECLARE @usertype_old integer
809 DECLARE @refcnt integer
810 SELECT @error = -1
811
812 SELECT @passwd = passwd, @refcnt = refcnt FROM usb_key WHERE sn = @sn
813 IF @@rowcount = 0
814 SELECT @error = 2
815 ELSE IF @refcnt > 0
816 SELECT @error = 5
817 ELSE
818 BEGIN
819 SELECT @usertype_old = usertype FROM account where name = @name
820 IF @@rowcount = 0
821 SELECT @error = 1
822 ELSE IF (@usertype_old & 240) > 0 --0xF0
823 SELECT @error = 3
824 ELSE
825 BEGIN
826 UPDATE account SET passwd = @passwd, usertype = (usertype&-241)|48 WHERE name = @name --0xFFFFFF0F,0x30
827 IF @@rowcount = 1
828 BEGIN
829 SELECT @error = 0
830 UPDATE usb_key SET refcnt = refcnt + 1 WHERE sn = @sn
831 END
832 END
833 END
834 COMMIT TRAN
835</procedure>
836
837<procedure name="unbindKey" connection="auth0" operate="replaceB">
838 <parameter name="name" sql-type="varchar(64)" java-type="java.lang.String" in="true" out="false" cache="account_by_name" />
839 <parameter name="passwd" sql-type="binary(16)" java-type="byte[]" in="true" out="false" />
840 <parameter name="sn" sql-type="binary(8)" java-type="byte[]" in="true" out="false" />
841 <parameter name="error" sql-type="integer" java-type="java.lang.Integer" in="false" out="true"/>
842 BEGIN TRAN
843 DECLARE @usertype_old integer
844 DECLARE @passwd_old binary(16)
845 SELECT @error = -1
846
847 SELECT @passwd_old = passwd, @usertype_old = usertype FROM account where name = @name
848 IF @@rowcount = 0
849 SELECT @error = 1
850 ELSE IF NOT (@usertype_old & 240) = 48 --0xF0,0x30
851 SELECT @error = 2
852 ELSE IF NOT @passwd_old = (SELECT passwd FROM usb_key WHERE sn = @sn)
853 SELECT @error = 3
854 ELSE
855 BEGIN
856 UPDATE account SET passwd = @passwd, usertype = (usertype&-241) WHERE name = @name --0xFFFFFF0F
857 IF @@rowcount = 1
858 BEGIN
859 SELECT @error = 0
860 UPDATE usb_key SET refcnt = refcnt - 1 WHERE sn = @sn
861 END
862 END
863 COMMIT TRAN
864</procedure>
865
866<procedure name="updateUserInfoById" connection="auth0" operate="replaceB">
867 <parameter name="id" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" cache="userinfo_by_id"/>
868 <parameter name="prompt" sql-type="varchar(32)" java-type="byte[]" in="true" out="false" />
869 <parameter name="answer" sql-type="varchar(32)" java-type="byte[]" in="true" out="false" />
870 <parameter name="truename" sql-type="varchar(32)" java-type="byte[]" in="true" out="false" />
871 <parameter name="idnumber" sql-type="varchar(32)" java-type="byte[]" in="true" out="false" />
872 <parameter name="email" sql-type="varchar(64)" java-type="byte[]" in="true" out="false" />
873 <parameter name="mobilenumber" sql-type="varchar(32)" java-type="byte[]" in="true" out="false" />
874 <parameter name="province" sql-type="varchar(32)" java-type="byte[]" in="true" out="false" />
875 <parameter name="city" sql-type="varchar(32)" java-type="byte[]" in="true" out="false" />
876 <parameter name="phonenumber" sql-type="varchar(32)" java-type="byte[]" in="true" out="false" />
877 <parameter name="address" sql-type="varchar(64)" java-type="byte[]" in="true" out="false" />
878 <parameter name="postalcode" sql-type="varchar(8)" java-type="byte[]" in="true" out="false" />
879 <parameter name="gender" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
880 <parameter name="birthday" sql-type="varchar(32)" java-type="java.lang.String" in="true" out="false" />
881 <parameter name="qq" sql-type="varchar(32)" java-type="byte[]" in="true" out="false" />
882 UPDATE users set prompt=@prompt,answer=@answer,truename=@truename,idnumber=@idnumber,email=@email,mobilenumber=@mobilenumber,province=@province,city=@city,phonenumber=@phonenumber,address=@address,postalcode=@postalcode,gender=@gender,birthday=@birthday,qq=@qq FROM users WHERE @id=id
883</procedure>
884
885<procedure name="deleteTimeoutForbid" connection="auth0" operate="replaceB">
886 <parameter name="userid" sql-type="integer" java-type="java.lang.Integer" in="true" out="false"
887 cache="account_by_id;forbid_by_id" />
888 BEGIN TRAN
889 DELETE FROM forbid WHERE userid=@userid AND datediff(ss,ctime,getdate())>forbid_time
890 IF NOT EXISTS (SELECT * FROM forbid WHERE userid=@userid)
891 UPDATE account SET usertype = usertype & ~262144 WHERE id=@userid
892 COMMIT TRAN
893</procedure>
894
895<procedure name="deleteForbid" connection="auth0" operate="replaceB">
896 <parameter name="userid" sql-type="integer" java-type="java.lang.Integer" in="true" out="false"
897 cache="account_by_id;forbid_by_id" />
898 <parameter name="type" sql-type="integer" java-type="java.lang.Integer" in="true" out="false"/>
899 BEGIN TRAN
900 DELETE FROM forbid WHERE userid=@userid AND type=@type
901 IF NOT EXISTS (SELECT * FROM forbid WHERE userid=@userid)
902 UPDATE account SET usertype = usertype & ~262144 WHERE id=@userid
903 COMMIT TRAN
904</procedure>
905
906<procedure name="addForbid" connection="auth0" operate="replaceB">
907 <parameter name="userid" sql-type="integer" java-type="java.lang.Integer" in="true" out="false"
908 cache="account_by_id;forbid_by_id" />
909 <parameter name="type" sql-type="integer" java-type="java.lang.Integer" in="true" out="false"/>
910 <!--parameter name="ctime" sql-type="datetime" java-type="java.util.Date" in="true" out="false"/-->
911 <parameter name="forbid_time" sql-type="integer" java-type="java.lang.Integer" in="true" out="false"/>
912 <parameter name="reason" sql-type="varbinary(255)" java-type="byte[]" in="true" out="false"/>
913 <parameter name="gmroleid" sql-type="integer" java-type="java.lang.Integer" in="true" out="false"/>
914 BEGIN TRAN
915 DECLARE @old_ctime datetime
916 DECLARE @old_forbid_time integer
917
918 IF @forbid_time > 2
919 BEGIN
920 SELECT @old_ctime = ctime, @old_forbid_time = forbid_time FROM forbid WHERE userid=@userid AND type=@type
921 IF @@rowcount = 0
922 BEGIN
923 INSERT INTO forbid VALUES(@userid,@type,getdate(),@forbid_time,@reason,@gmroleid)
924 UPDATE account SET usertype = usertype|262144 WHERE id = @userid --0x40000
925 END
926 ELSE IF dateadd(second,@forbid_time,getdate()) > dateadd(second,@old_forbid_time,@old_ctime)
927 BEGIN
928 UPDATE forbid SET ctime=getdate(),forbid_time=@forbid_time,reason=@reason,gmroleid=@gmroleid WHERE userid=@userid AND type=@type
929 END
930 END
931 ELSE
932 BEGIN
933 DELETE FROM forbid WHERE userid=@userid AND type=@type
934 IF NOT EXISTS (SELECT * FROM forbid WHERE userid=@userid)
935 UPDATE account SET usertype = usertype & ~262144 WHERE id=@userid
936 END
937 COMMIT TRAN
938</procedure>
939
940<procedure name="setiplimit" connection="auth0" operate="replaceB">
941 <parameter name="uid" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" cache="account_by_id" />
942 <parameter name="ipaddr1" sql-type="integer" java-type="java.lang.Integer" in="true" out="false"/>
943 <parameter name="ipmask1" sql-type="varchar(2)" java-type="java.lang.String" in="true" out="false"/>
944 <parameter name="ipaddr2" sql-type="integer" java-type="java.lang.Integer" in="true" out="false"/>
945 <parameter name="ipmask2" sql-type="varchar(2)" java-type="java.lang.String" in="true" out="false"/>
946 <parameter name="ipaddr3" sql-type="integer" java-type="java.lang.Integer" in="true" out="false"/>
947 <parameter name="ipmask3" sql-type="varchar(2)" java-type="java.lang.String" in="true" out="false"/>
948 BEGIN TRAN
949 UPDATE iplimit SET ipaddr1=@ipaddr1,ipmask1=@ipmask1,ipaddr2=@ipaddr2,ipmask2=@ipmask2,ipaddr3=@ipaddr3,ipmask3=@ipmask3 WHERE uid=@uid
950 IF @@rowcount = 0
951 BEGIN
952 INSERT INTO iplimit (uid,ipaddr1,ipmask1,ipaddr2,ipmask2,ipaddr3,ipmask3,enable) VALUES (@uid,@ipaddr1,@ipmask1,@ipaddr2,@ipmask2,@ipaddr3,@ipmask3,'t')
953 UPDATE account SET usertype = usertype|65536 WHERE id = @uid --0x10000
954 END
955 COMMIT TRAN
956</procedure>
957
958<procedure name="enableiplimit" connection="auth0" operate="replaceB">
959 <parameter name="uid" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" cache="account_by_id" />
960 <parameter name="enable" sql-type="char(1)" java-type="java.lang.String" in="true" out="false"/>
961 BEGIN TRAN
962 UPDATE iplimit SET enable=@enable WHERE uid=@uid
963 IF @@rowcount = 0
964 BEGIN
965 INSERT INTO iplimit (uid,enable) VALUES (@uid,@enable)
966 UPDATE account SET usertype = usertype|65536 WHERE id = @uid --0x10000
967 END
968 COMMIT TRAN
969</procedure>
970
971<procedure name="enableautolock" connection="auth0" operate="replaceB">
972 <parameter name="uid" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" cache="account_by_id" />
973 <parameter name="autolock" sql-type="char(1)" java-type="java.lang.String" in="true" out="false"/>
974 BEGIN TRAN
975 UPDATE iplimit SET autolock=@autolock WHERE uid=@uid
976 IF @@rowcount = 0
977 BEGIN
978 INSERT INTO iplimit (uid,autolock) VALUES (@uid,@autolock)
979 UPDATE account SET usertype = usertype|65536 WHERE id = @uid --0x10000
980 END
981 COMMIT TRAN
982</procedure>
983
984<procedure name="lockuser" connection="auth0" operate="replaceB">
985 <parameter name="uid" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" cache="account_by_id" />
986 <parameter name="lockstatus" sql-type="char(1)" java-type="java.lang.String" in="true" out="false"/>
987 BEGIN TRAN
988 UPDATE iplimit SET lockstatus=@lockstatus WHERE uid=@uid
989 IF @@rowcount = 0
990 BEGIN
991 INSERT INTO iplimit (uid,lockstatus,enable) VALUES (@uid,@lockstatus,'t')
992 UPDATE account SET usertype = usertype|65536 WHERE id = @uid --0x10000
993 END
994 COMMIT TRAN
995</procedure>
996
997<procedure name="testandlockuser" connection="auth0" operate="replaceA">
998 <parameter name="uid" sql-type="integer" java-type="java.lang.Integer" in="true" out="false"/>
999 UPDATE iplimit SET lockstatus='t' WHERE uid=@uid and autolock='t'
1000</procedure>
1001
1002<procedure name="sellpoint" connection="auth0" operate="replaceB">
1003 <parameter name="status" sql-type="integer" java-type="java.lang.Integer" in="true" out="true"/>
1004 <parameter name="zoneid" sql-type="integer" java-type="java.lang.Integer" in="true" out="false"/>
1005 <parameter name="serial" sql-type="integer" java-type="java.lang.Integer" in="true" out="false"/>
1006 <parameter name="seller" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" cache="function_by_id"/>
1007 <parameter name="sellid" sql-type="integer" java-type="java.lang.Integer" in="true" out="false"/>
1008 <parameter name="buyer" sql-type="integer" java-type="java.lang.Integer" in="true" out="false"/>
1009 <parameter name="price" sql-type="integer" java-type="java.lang.Integer" in="true" out="false"/>
1010 <parameter name="point" sql-type="integer" java-type="java.lang.Integer" in="true" out="false"/>
1011 <parameter name="aid" sql-type="integer" java-type="java.lang.Integer" in="true" out="false"/>
1012 BEGIN TRAN
1013 DECLARE @trans_status integer
1014 DECLARE @seller_point integer
1015 DECLARE @buyer_point integer
1016
1017 SELECT @trans_status=status FROM translog WHERE zoneid=@zoneid AND serial=@serial
1018 IF @@rowcount=0
1019 BEGIN
1020 UPDATE point SET time=time-@point WHERE uid=@seller AND aid=@aid AND time>@point+135000-18000
1021 IF @@rowcount=1
1022 BEGIN
1023 UPDATE point SET time=time+@point WHERE uid=@buyer AND aid=@aid
1024 IF @@rowcount=0
1025 INSERT INTO point (uid,aid,time,zoneid,zonelocalid,accountstart,lastlogin,enddate) VALUES (@buyer,@aid,@point+36000,0,0,'','','')
1026 SELECT @status=1
1027 INSERT INTO translog (zoneid,serial,seller,sellid,buyer,price,point,aid,status,date) VALUES(@zoneid,@serial,@seller,@sellid,@buyer,@price,@point,@aid,@status,getdate())
1028
1029 UPDATE function SET soldpoint=soldpoint+@point WHERE @seller = uid
1030 IF @@rowcount = 0
1031 INSERT INTO function (uid,adduptime,score,func,funcparm,addupmoney,adduppoint,addupscore,coin,addupcoin,soldpoint) VALUES ( @seller,0,0,0,0,0,0,0,0,0,@point)
1032
1033 END
1034 ELSE
1035 BEGIN
1036 SELECT @status=0
1037 INSERT INTO translog (zoneid,serial,seller,sellid,buyer,price,point,aid,status,date) VALUES(@zoneid,@serial,@seller,@sellid,@buyer,@price,@point,@aid,@status,getdate())
1038 END
1039 END
1040 ELSE
1041 SELECT @status=@trans_status
1042 COMMIT TRAN
1043</procedure>
1044
1045<!--
1046 status:
1047 1.create ok;
1048 2.get sn ok;
1049 3.send addcash ok;
1050 4.finish ok;
1051 error:
1052 0.³É¹¦;
1053 -1:δ֪´ÃÎó;
1054 -2.¸ÃÕʺŲ»´æÔÚ;
1055 -3:ì½ÓÕʺŷþÎñÆ÷ʧ°Ü;
1056 -4:ÃùÕʺŷþÎñÆ÷·¢ËÃÊý¾Ãʧ°Ü;
1057 -5:ÕʺŷþÎñÆ÷δ·µ»Ø;
1058 -6:ÓÎ÷·þÎñÆ÷²»´æÔÚ»òÕßδÆô¶¯;
1059 -7:¸ÃÓû§ÒÑÓýðÔª±¦ÕýÔÚ»®²¦£¬ÒÑ·ÅÈë¶ÓÃÃ;
1060 -8.¼Æ·ÑÇøÓà ¶î²»×ã»ò»®²¦½ð¶î´ÃÎó£¬ÒÑ·ÅÈë¶ÓÃÃ;
1061 -9:µ÷ÓôÎÃò´ÃÎ󣬵±Ç°×´Ì¬²»Îª1;
1062 -10:µ÷ÓôÎÃò´ÃÎ󣬵±Ç°×´Ì¬²»Îª2;
1063 -11:µ÷ÓôÎÃò´ÃÎ󣬵±Ç°×´Ì¬²»Îª3;
1064 -12:µ÷ÓôÎÃò´ÃÎó£¬Î޴˼Ǽ;
1065 -13:ÃòÓÎ÷·þÎñÆ÷·¢ËûñÈ¡ÃòÃúÅÇëÇóʧ°Ü;
1066 -14:ÃòÓÎ÷·þÎñÆ÷·¢Ëû®²¦ÇëÇóʧ°Ü;
1067 -15:ÃòÓÎ÷·þÎñÆ÷»ñÈ¡ÃòÃúų¬Ê±;
1068 -16:ÓÎ÷·þÎñÆ÷»ñÈ¡ÃòÃúÅʧ°Ü;
1069 -17:ÓÎ÷·þÎñÆ÷»®²¦Ê§°Ü;
1070 -18:ÓÎ÷·þÎñÆ÷½ðÔª±¦Óà ¶î²»×ã;
1071-->
1072<procedure name="usecash" connection="auth0" operate="replaceB">
1073 <parameter name="userid" sql-type="integer" java-type="java.lang.Integer" in="true" out="false"/>
1074 <parameter name="zoneid" sql-type="integer" java-type="java.lang.Integer" in="true" out="false"/>
1075 <parameter name="sn" sql-type="integer" java-type="java.lang.Integer" in="true" out="false"/>
1076 <parameter name="aid" sql-type="integer" java-type="java.lang.Integer" in="true" out="false"/>
1077 <parameter name="point" sql-type="integer" java-type="java.lang.Integer" in="true" out="false"/>
1078 <parameter name="cash" sql-type="integer" java-type="java.lang.Integer" in="true" out="false"/>
1079 <parameter name="status" sql-type="integer" java-type="java.lang.Integer" in="true" out="false"/>
1080 <parameter name="error" sql-type="integer" java-type="java.lang.Integer" in="false" out="true"/>
1081 BEGIN TRAN
1082 DECLARE @sn_old integer
1083 DECLARE @aid_old integer
1084 DECLARE @point_old integer
1085 DECLARE @cash_old integer
1086 DECLARE @status_old integer
1087 DECLARE @creatime_old datetime
1088 DECLARE @need_restore integer
1089 DECLARE @exists integer
1090
1091 SELECT @error = 0
1092 SELECT @need_restore = 0
1093 SELECT @sn_old=sn,@aid_old=aid,@point_old=point,@cash_old=cash,@status_old=status,@creatime_old=creatime FROM usecashnow WHERE @userid=userid AND @zoneid=zoneid AND sn>=0
1094
1095 IF @@rowcount = 1 SELECT @exists = 1
1096 ELSE SELECT @exists = 0
1097
1098 IF @status = 0
1099 BEGIN
1100 IF @exists = 0
1101 BEGIN
1102 SELECT @sn_old=sn,@aid_old=aid,@point_old=point,@cash_old=cash,@status_old=status,@creatime_old=creatime FROM usecashnow WHERE @userid=userid AND @zoneid=zoneid AND @sn=sn
1103 IF @@rowcount = 1
1104 BEGIN
1105 SELECT @point_old = ISNULL(@point_old,0)
1106 UPDATE point SET time=time-@point_old WHERE @userid=uid AND @aid_old=aid AND time>=@point_old
1107 IF @@rowcount = 1
1108 BEGIN
1109 DELETE FROM usecashnow WHERE @userid=userid AND @zoneid=zoneid AND @sn=sn
1110 INSERT INTO usecashnow (userid, zoneid, sn, aid, point, cash, status, creatime) VALUES(@userid, @zoneid, 0, @aid_old, @point_old, @cash_old, 1, @creatime_old )
1111 END
1112 ELSE
1113 SELECT @error = -8
1114 END
1115 ELSE
1116 SELECT @error = -12
1117 END
1118 ELSE
1119 SELECT @error = -7
1120 END
1121 ELSE IF @status = 1
1122 BEGIN
1123 IF @exists = 0
1124 BEGIN
1125 UPDATE point SET time=time-@point WHERE @userid=uid AND @aid=aid AND time>=@point
1126 IF @@rowcount = 1
1127 INSERT INTO usecashnow (userid, zoneid, sn, aid, point, cash, status, creatime) VALUES(@userid, @zoneid, @sn, @aid, @point, @cash, @status, getdate() )
1128 ELSE
1129 BEGIN
1130 INSERT INTO usecashnow SELECT @userid, @zoneid, ISNULL(min(sn),0)-1, @aid, @point, @cash, 0, getdate() FROM usecashnow WHERE userid=@userid and zoneid=@zoneid and 0>=sn
1131 SELECT @error = -8
1132 END
1133 END
1134 ELSE
1135 BEGIN
1136 INSERT INTO usecashnow SELECT @userid, @zoneid, ISNULL(min(sn),0)-1, @aid, @point, @cash, 0, getdate() FROM usecashnow WHERE userid=@userid and zoneid=@zoneid and 0>=sn
1137 SELECT @error = -7
1138 END
1139 END
1140 ELSE IF @status = 2
1141 BEGIN
1142 IF @exists = 1 AND @status_old = 1 AND @sn_old = 0
1143 BEGIN
1144 DELETE FROM usecashnow WHERE @userid=userid AND @zoneid=zoneid AND @sn_old=sn
1145 INSERT INTO usecashnow (userid, zoneid, sn, aid, point, cash, status, creatime) VALUES(@userid, @zoneid, @sn, @aid_old, @point_old, @cash_old, @status, @creatime_old )
1146 END
1147 ELSE
1148 BEGIN
1149 SELECT @error = -9
1150 END
1151 END
1152 ELSE IF @status = 3
1153 BEGIN
1154 IF @exists = 1 AND @status_old = 2
1155 BEGIN
1156 DELETE FROM usecashnow WHERE @userid=userid AND @zoneid=zoneid AND @sn_old=sn
1157 INSERT INTO usecashnow (userid, zoneid, sn, aid, point, cash, status, creatime) VALUES(@userid, @zoneid, @sn_old, @aid_old, @point_old, @cash_old, @status, @creatime_old )
1158 END
1159 ELSE
1160 BEGIN
1161 SELECT @error = -10
1162 END
1163 END
1164 ELSE IF @status = 4
1165 BEGIN
1166 IF @exists = 1
1167 BEGIN
1168 DELETE FROM usecashnow WHERE @userid=userid AND @zoneid=zoneid AND @sn_old=sn
1169 INSERT INTO usecashlog (userid, zoneid, sn, aid, point, cash, status, creatime, fintime) VALUES(@userid, @zoneid, @sn_old, @aid_old, @point_old, @cash_old, @status, @creatime_old, getdate() )
1170 END
1171 IF NOT (@exists = 1 AND @status_old = 3) SELECT @error = -11
1172 END
1173 ELSE
1174 BEGIN
1175 SELECT @error = -12
1176 END
1177
1178 IF @need_restore = 1
1179 BEGIN
1180 UPDATE point SET time=time+@point_old WHERE @userid=uid AND @aid_old=aid
1181 DELETE FROM usecashnow WHERE @userid=userid AND @zoneid=zoneid AND @sn_old=sn
1182 INSERT INTO usecashlog (userid, zoneid, sn, aid, point, cash, status, creatime, fintime) VALUES(@userid, @zoneid, @sn_old, @aid_old, @point_old, @cash_old, @status, @creatime_old, getdate() )
1183 END
1184 COMMIT TRAN
1185</procedure>
1186
1187<procedure name="addusecashnow" connection="auth0" operate="replaceB">
1188 <parameter name="userid" sql-type="integer" java-type="java.lang.Integer" in="true" out="false"/>
1189 <parameter name="zoneid" sql-type="integer" java-type="java.lang.Integer" in="true" out="false"/>
1190 <parameter name="aid" sql-type="integer" java-type="java.lang.Integer" in="true" out="false"/>
1191 <parameter name="cash" sql-type="integer" java-type="java.lang.Integer" in="true" out="false"/>
1192 BEGIN TRAN
1193 DECLARE @sn_old integer
1194 DECLARE @aid_old integer
1195 DECLARE @point_old integer
1196 DECLARE @cash_old integer
1197 DECLARE @status_old integer
1198 DECLARE @creatime_old datetime
1199 DECLARE @need_restore integer
1200 DECLARE @exists integer
1201
1202 DECLARE @status integer
1203 DECLARE @point integer
1204 DECLARE @sn integer
1205 DECLARE @error integer
1206
1207 SELECT @sn = 0
1208 SELECT @status = 1
1209 SELECT @point = @cash * 90
1210 UPDATE point SET time = time + @point WHERE @userid = uid AND @aid = aid
1211
1212 SELECT @error = 0
1213 SELECT @need_restore = 0
1214 SELECT @sn_old=sn,@aid_old=aid,@point_old=point,@cash_old=cash,@status_old=status,@creatime_old=creatime FROM usecashnow WHERE @userid=userid AND @zoneid=zoneid AND sn>=0
1215
1216 IF @@rowcount = 1 SELECT @exists = 1
1217 ELSE SELECT @exists = 0
1218
1219 IF @status = 1
1220 BEGIN
1221 IF @exists = 0
1222 BEGIN
1223 UPDATE point SET time=time-@point WHERE @userid=uid AND @aid=aid AND time>=@point
1224 IF @@rowcount = 1
1225 INSERT INTO usecashnow (userid, zoneid, sn, aid, point, cash, status, creatime) VALUES(@userid, @zoneid, @sn, @aid, @point, @cash, @status, getdate() )
1226 ELSE
1227 BEGIN
1228 INSERT INTO usecashnow SELECT @userid, @zoneid, ISNULL(min(sn),0)-1, @aid, @point, @cash, 0, getdate() FROM usecashnow WHERE userid=@userid and zoneid=@zoneid and 0>=sn
1229 SELECT @error = -8
1230 END
1231 END
1232 ELSE
1233 BEGIN
1234 INSERT INTO usecashnow SELECT @userid, @zoneid, ISNULL(min(sn),0)-1, @aid, @point, @cash, 0, getdate() FROM usecashnow WHERE userid=@userid and zoneid=@zoneid and 0>=sn
1235 SELECT @error = -7
1236 END
1237 END
1238
1239 IF @need_restore = 1
1240 BEGIN
1241 UPDATE point SET time=time+@point_old WHERE @userid=uid AND @aid_old=aid
1242 DELETE FROM usecashnow WHERE @userid=userid AND @zoneid=zoneid AND @sn_old=sn
1243 INSERT INTO usecashlog (userid, zoneid, sn, aid, point, cash, status, creatime, fintime) VALUES(@userid, @zoneid, @sn_old, @aid_old, @point_old, @cash_old, @status, @creatime_old, getdate() )
1244 END
1245 COMMIT TRAN
1246</procedure>
1247
1248<procedure name="adduser" connection="auth0" operate="replaceA">
1249 <!-- adduser not need userinfo_by_id -->
1250 <parameter name="name" sql-type="varchar(64)" java-type="java.lang.String" in="true" out="false" />
1251 <parameter name="passwd" sql-type="binary(16)" java-type="byte[]" in="true" out="false" />
1252 <parameter name="prompt" sql-type="varchar(32)" java-type="byte[]" in="true" out="false" />
1253 <parameter name="answer" sql-type="varchar(32)" java-type="byte[]" in="true" out="false" />
1254 <parameter name="truename" sql-type="varchar(32)" java-type="byte[]" in="true" out="false" />
1255 <parameter name="idnumber" sql-type="varchar(32)" java-type="byte[]" in="true" out="false" />
1256 <parameter name="email" sql-type="varchar(64)" java-type="byte[]" in="true" out="false" />
1257 <parameter name="mobilenumber" sql-type="varchar(32)" java-type="byte[]" in="true" out="false" />
1258 <parameter name="province" sql-type="varchar(32)" java-type="byte[]" in="true" out="false" />
1259 <parameter name="city" sql-type="varchar(32)" java-type="byte[]" in="true" out="false" />
1260 <parameter name="phonenumber" sql-type="varchar(32)" java-type="byte[]" in="true" out="false" />
1261 <parameter name="address" sql-type="varchar(64)" java-type="byte[]" in="true" out="false" />
1262 <parameter name="postalcode" sql-type="varchar(8)" java-type="byte[]" in="true" out="false" />
1263 <parameter name="gender" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1264 <parameter name="birthday" sql-type="varchar(32)" java-type="java.lang.String" in="true" out="false" />
1265 <parameter name="qq" sql-type="varchar(32)" java-type="byte[]" in="true" out="false" />
1266 <parameter name="passwd2" sql-type="binary(16)" java-type="byte[]" in="true" out="false" />
1267 BEGIN TRAN
1268 DECLARE @id integer
1269 DECLARE @now datetime
1270 SELECT @now = getdate()
1271 INSERT INTO account SELECT ISNULL(max(id), 16) + 16, @name, @passwd, @now, 0 FROM account
1272 SELECT @id = id FROM account WHERE name = @name
1273 INSERT INTO users VALUES (@id, @prompt, @answer, @truename, @idnumber, @email, @mobilenumber, @province, @city, @phonenumber, @address, @postalcode, @gender, @birthday, @qq, @passwd2)
1274 COMMIT TRAN
1275</procedure>
1276
1277<procedure name="addbonususer" connection="auth0" operate="replaceA">
1278 <!-- addbonususer not need userinfo_by_id -->
1279 <parameter name="name" sql-type="varchar(64)" java-type="java.lang.String" in="true" out="false" />
1280 <parameter name="passwd" sql-type="binary(16)" java-type="byte[]" in="true" out="false" />
1281 <parameter name="prompt" sql-type="varchar(32)" java-type="byte[]" in="true" out="false" />
1282 <parameter name="answer" sql-type="varchar(32)" java-type="byte[]" in="true" out="false" />
1283 <parameter name="truename" sql-type="varchar(32)" java-type="byte[]" in="true" out="false" />
1284 <parameter name="idnumber" sql-type="varchar(32)" java-type="byte[]" in="true" out="false" />
1285 <parameter name="email" sql-type="varchar(64)" java-type="byte[]" in="true" out="false" />
1286 <parameter name="mobilenumber" sql-type="varchar(32)" java-type="byte[]" in="true" out="false" />
1287 <parameter name="province" sql-type="varchar(32)" java-type="byte[]" in="true" out="false" />
1288 <parameter name="city" sql-type="varchar(32)" java-type="byte[]" in="true" out="false" />
1289 <parameter name="phonenumber" sql-type="varchar(32)" java-type="byte[]" in="true" out="false" />
1290 <parameter name="address" sql-type="varchar(64)" java-type="byte[]" in="true" out="false" />
1291 <parameter name="postalcode" sql-type="varchar(8)" java-type="byte[]" in="true" out="false" />
1292 <parameter name="gender" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1293 <parameter name="birthday" sql-type="varchar(32)" java-type="java.lang.String" in="true" out="false" />
1294 <parameter name="qq" sql-type="varchar(32)" java-type="byte[]" in="true" out="false" />
1295 <parameter name="passwd2" sql-type="binary(16)" java-type="byte[]" in="true" out="false" />
1296 <parameter name="areaid" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1297 <parameter name="freepoint" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1298 <parameter name="score" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1299 <parameter name="func" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1300 <parameter name="funcparm" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1301 BEGIN TRAN
1302 DECLARE @id integer
1303 DECLARE @now datetime
1304 SELECT @now = getdate()
1305 INSERT INTO account SELECT ISNULL(max(id), 16) + 16, @name, @passwd, @now, 0 FROM account
1306 SELECT @id = id FROM account WHERE name = @name
1307 INSERT INTO users VALUES( @id, @prompt, @answer, @truename, @idnumber, @email, @mobilenumber, @province, @city, @phonenumber, @address, @postalcode, @gender, @birthday, @qq, @passwd2 )
1308 INSERT INTO function (uid,adduptime,score,func,funcparm,addupmoney,adduppoint,addupscore,coin,addupcoin,soldpoint) VALUES ( @id, 0, @score, @func, @funcparm, 0, 0, @score, 0, 0, 0 )
1309 COMMIT TRAN
1310</procedure>
1311
1312<procedure name="addzonghenguser" connection="auth0" operate="replaceA">
1313 <!-- addzonghenguser not need userinfo_by_id -->
1314 <parameter name="name" sql-type="varchar(64)" java-type="java.lang.String" in="true" out="false" />
1315 <parameter name="passwd" sql-type="binary(16)" java-type="byte[]" in="true" out="false" />
1316 <parameter name="prompt" sql-type="varchar(32)" java-type="byte[]" in="true" out="false" />
1317 <parameter name="answer" sql-type="varchar(32)" java-type="byte[]" in="true" out="false" />
1318 <parameter name="truename" sql-type="varchar(32)" java-type="byte[]" in="true" out="false" />
1319 <parameter name="idnumber" sql-type="varchar(32)" java-type="byte[]" in="true" out="false" />
1320 <parameter name="email" sql-type="varchar(64)" java-type="byte[]" in="true" out="false" />
1321 <parameter name="mobilenumber" sql-type="varchar(32)" java-type="byte[]" in="true" out="false" />
1322 <parameter name="province" sql-type="varchar(32)" java-type="byte[]" in="true" out="false" />
1323 <parameter name="city" sql-type="varchar(32)" java-type="byte[]" in="true" out="false" />
1324 <parameter name="phonenumber" sql-type="varchar(32)" java-type="byte[]" in="true" out="false" />
1325 <parameter name="address" sql-type="varchar(64)" java-type="byte[]" in="true" out="false" />
1326 <parameter name="postalcode" sql-type="varchar(8)" java-type="byte[]" in="true" out="false" />
1327 <parameter name="gender" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1328 <parameter name="birthday" sql-type="varchar(32)" java-type="java.lang.String" in="true" out="false" />
1329 <parameter name="qq" sql-type="varchar(32)" java-type="byte[]" in="true" out="false" />
1330 <parameter name="passwd2" sql-type="binary(16)" java-type="byte[]" in="true" out="false" />
1331 BEGIN TRAN
1332 DECLARE @id integer
1333 DECLARE @now datetime
1334 SELECT @now = getdate()
1335 INSERT INTO account SELECT ISNULL(max(id), 16) + 16, @name, @passwd, @now, -2147483648 FROM account
1336 SELECT @id = id FROM account WHERE name = @name
1337 INSERT INTO users VALUES (@id, '', '', '', '', @email, @mobilenumber, @province, @city, @phonenumber, @address, @postalcode, @gender, @birthday, @qq, @passwd2)
1338 COMMIT TRAN
1339</procedure>
1340
1341<procedure name="upgradezonghenguser" connection="auth0" operate="replaceB">
1342 <parameter name="uid" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1343 UPDATE account SET usertype = usertype & 2147483647 WHERE id = @uid
1344 IF @@rowcount = 1
1345 return 0
1346 return 1
1347</procedure>
1348
1349<procedure name="remaintime" connection="auth0" operate="replaceB">
1350 <parameter name="uid" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1351 <parameter name="aid" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1352 <parameter name="remain" sql-type="integer" java-type="java.lang.Integer" in="false" out="true" />
1353 <parameter name="freetimeleft" sql-type="integer" java-type="java.lang.Integer" in="false" out="true" />
1354 BEGIN TRAN
1355 DECLARE @enddate datetime
1356 DECLARE @now datetime
1357 SELECT @remain=time, @enddate=enddate FROM point WHERE @uid = uid AND @aid = aid
1358 IF @@rowcount = 0
1359 BEGIN
1360 SELECT @remain = 36000
1361 INSERT INTO point (uid,aid,time,zoneid,zonelocalid,accountstart,lastlogin,enddate) VALUES (@uid,@aid,@remain, 0, 0, '', '', '')
1362 END
1363 SELECT @now = getdate()
1364 SELECT @freetimeleft = CASE WHEN @enddate > @now THEN datediff(second, @now, @enddate) ELSE 0 END
1365 COMMIT TRAN
1366</procedure>
1367
1368<procedure name="adduserpoint" connection="auth0" operate="replaceB">
1369 <parameter name="uid" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1370 <parameter name="aid" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1371 <parameter name="time" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1372 BEGIN TRAN
1373 UPDATE point SET time=ISNULL(time,0)+@time WHERE @uid=uid AND @aid=aid
1374 IF @@rowcount = 0 INSERT INTO point (uid,aid,time,zoneid,zonelocalid,accountstart,lastlogin,enddate) VALUES (@uid,@aid,36000+@time, 0, 0, '', '', '')
1375 COMMIT TRAN
1376</procedure>
1377
1378<procedure name="addscore" connection="auth0" operate="replaceB">
1379 <parameter name="uid" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" cache="function_by_id"/>
1380 <parameter name="addscore" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1381 BEGIN TRAN
1382 if( @addscore > 0 )
1383 BEGIN
1384 UPDATE function SET score=score+@addscore,addupscore=addupscore+@addscore WHERE @uid = uid
1385 IF @@rowcount = 0
1386 INSERT INTO function (uid,adduptime,score,func,funcparm,addupmoney,adduppoint,addupscore,coin,addupcoin,soldpoint) VALUES (@uid, 0, @addscore, 0, 0, 0, 0, @addscore, 0, 0, 0 )
1387 END
1388 ELSE
1389 BEGIN
1390 UPDATE function SET score=score+@addscore WHERE @uid = uid
1391 IF @@rowcount = 0
1392 INSERT INTO function (uid,adduptime,score,func,funcparm,addupmoney,adduppoint,addupscore,coin,addupcoin,soldpoint) VALUES (@uid, 0, @addscore, 0, 0, 0, 0, 0, 0, 0, 0 )
1393 END
1394 COMMIT TRAN
1395</procedure>
1396
1397<procedure name="setfunction" connection="auth0" operate="replaceB">
1398 <parameter name="uid" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" cache="function_by_id" />
1399 <parameter name="func" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1400 <parameter name="funcparm" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1401 BEGIN TRAN
1402 UPDATE function SET func = @func, funcparm = @funcparm WHERE @uid = uid
1403 IF @@rowcount = 0
1404 INSERT INTO function (uid,adduptime,score,func,funcparm,addupmoney,adduppoint,addupscore,coin,addupcoin,soldpoint) VALUES (@uid, 0, 0, @func, @funcparm, 0, 0, 0, 0, 0, 0 )
1405 COMMIT TRAN
1406</procedure>
1407
1408<procedure name="clearonlinerecords" connection="auth0" operate="replaceB">
1409 <parameter name="zoneid" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1410 <parameter name="aid" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1411 BEGIN TRAN
1412 LOCK TABLE point IN EXCLUSIVE MODE
1413 UPDATE point SET zoneid = 0, zonelocalid = 0 WHERE aid = @aid AND zoneid = @zoneid
1414 COMMIT TRAN
1415</procedure>
1416
1417<procedure name="recordonline_free" connection="auth0" operate="replaceB">
1418 <parameter name="uid" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1419 <parameter name="aid" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1420 <parameter name="zoneid" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1421 BEGIN TRAN
1422 UPDATE point_free SET zoneid = @zoneid, lastlogin = getdate() WHERE uid = @uid AND aid = @aid
1423 IF @@rowcount = 0
1424 INSERT INTO point_free (uid,aid,zoneid,lastlogin) VALUES (@uid,@aid,@zoneid,getdate())
1425 COMMIT TRAN
1426</procedure>
1427
1428<procedure name="batchrecordonlinefree" connection="auth0" operate="replaceB">
1429 <parameter name="uid1" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1430 <parameter name="aid1" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1431 <parameter name="zoneid1" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1432 <parameter name="login1" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1433 <parameter name="uid2" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1434 <parameter name="aid2" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1435 <parameter name="zoneid2" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1436 <parameter name="login2" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1437 <parameter name="uid3" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1438 <parameter name="aid3" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1439 <parameter name="zoneid3" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1440 <parameter name="login3" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1441 <parameter name="uid4" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1442 <parameter name="aid4" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1443 <parameter name="zoneid4" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1444 <parameter name="login4" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1445 <parameter name="uid5" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1446 <parameter name="aid5" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1447 <parameter name="zoneid5" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1448 <parameter name="login5" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1449 <parameter name="uid6" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1450 <parameter name="aid6" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1451 <parameter name="zoneid6" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1452 <parameter name="login6" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1453 <parameter name="uid7" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1454 <parameter name="aid7" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1455 <parameter name="zoneid7" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1456 <parameter name="login7" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1457 <parameter name="uid8" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1458 <parameter name="aid8" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1459 <parameter name="zoneid8" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1460 <parameter name="login8" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1461 <parameter name="uid9" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1462 <parameter name="aid9" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1463 <parameter name="zoneid9" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1464 <parameter name="login9" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1465 <parameter name="uid10" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1466 <parameter name="aid10" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1467 <parameter name="zoneid10" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1468 <parameter name="login10" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1469 BEGIN TRAN
1470 DECLARE @now datetime
1471 DECLARE @lastlogin1 datetime
1472 DECLARE @lastlogin2 datetime
1473 DECLARE @lastlogin3 datetime
1474 DECLARE @lastlogin4 datetime
1475 DECLARE @lastlogin5 datetime
1476 DECLARE @lastlogin6 datetime
1477 DECLARE @lastlogin7 datetime
1478 DECLARE @lastlogin8 datetime
1479 DECLARE @lastlogin9 datetime
1480 DECLARE @lastlogin10 datetime
1481 SELECT @now = getdate()
1482 SELECT @lastlogin1 = dateadd(second,@login1,@now)
1483 SELECT @lastlogin2 = dateadd(second,@login2,@now)
1484 SELECT @lastlogin3 = dateadd(second,@login3,@now)
1485 SELECT @lastlogin4 = dateadd(second,@login4,@now)
1486 SELECT @lastlogin5 = dateadd(second,@login5,@now)
1487 SELECT @lastlogin6 = dateadd(second,@login6,@now)
1488 SELECT @lastlogin7 = dateadd(second,@login7,@now)
1489 SELECT @lastlogin8 = dateadd(second,@login8,@now)
1490 SELECT @lastlogin9 = dateadd(second,@login9,@now)
1491 SELECT @lastlogin10 = dateadd(second,@login10,@now)
1492
1493 UPDATE point_free SET zoneid = @zoneid1, lastlogin = @lastlogin1 WHERE uid = @uid1 AND aid = @aid1
1494 IF @@rowcount = 0
1495 INSERT INTO point_free (uid,aid,zoneid,lastlogin) VALUES (@uid1,@aid1,@zoneid1,@lastlogin1)
1496 UPDATE point_free SET zoneid = @zoneid2, lastlogin = @lastlogin2 WHERE uid = @uid2 AND aid = @aid2
1497 IF @@rowcount = 0
1498 INSERT INTO point_free (uid,aid,zoneid,lastlogin) VALUES (@uid2,@aid2,@zoneid2,@lastlogin2)
1499 UPDATE point_free SET zoneid = @zoneid3, lastlogin = @lastlogin3 WHERE uid = @uid3 AND aid = @aid3
1500 IF @@rowcount = 0
1501 INSERT INTO point_free (uid,aid,zoneid,lastlogin) VALUES (@uid3,@aid3,@zoneid3,@lastlogin3)
1502 UPDATE point_free SET zoneid = @zoneid4, lastlogin = @lastlogin4 WHERE uid = @uid4 AND aid = @aid4
1503 IF @@rowcount = 0
1504 INSERT INTO point_free (uid,aid,zoneid,lastlogin) VALUES (@uid4,@aid4,@zoneid4,@lastlogin4)
1505 UPDATE point_free SET zoneid = @zoneid5, lastlogin = @lastlogin5 WHERE uid = @uid5 AND aid = @aid5
1506 IF @@rowcount = 0
1507 INSERT INTO point_free (uid,aid,zoneid,lastlogin) VALUES (@uid5,@aid5,@zoneid5,@lastlogin5)
1508 UPDATE point_free SET zoneid = @zoneid6, lastlogin = @lastlogin6 WHERE uid = @uid6 AND aid = @aid6
1509 IF @@rowcount = 0
1510 INSERT INTO point_free (uid,aid,zoneid,lastlogin) VALUES (@uid6,@aid6,@zoneid6,@lastlogin6)
1511 UPDATE point_free SET zoneid = @zoneid7, lastlogin = @lastlogin7 WHERE uid = @uid7 AND aid = @aid7
1512 IF @@rowcount = 0
1513 INSERT INTO point_free (uid,aid,zoneid,lastlogin) VALUES (@uid7,@aid7,@zoneid7,@lastlogin7)
1514 UPDATE point_free SET zoneid = @zoneid8, lastlogin = @lastlogin8 WHERE uid = @uid8 AND aid = @aid8
1515 IF @@rowcount = 0
1516 INSERT INTO point_free (uid,aid,zoneid,lastlogin) VALUES (@uid8,@aid8,@zoneid8,@lastlogin8)
1517 UPDATE point_free SET zoneid = @zoneid9, lastlogin = @lastlogin9 WHERE uid = @uid9 AND aid = @aid9
1518 IF @@rowcount = 0
1519 INSERT INTO point_free (uid,aid,zoneid,lastlogin) VALUES (@uid9,@aid9,@zoneid9,@lastlogin9)
1520 UPDATE point_free SET zoneid = @zoneid10, lastlogin = @lastlogin10 WHERE uid = @uid10 AND aid = @aid10
1521 IF @@rowcount = 0
1522 INSERT INTO point_free (uid,aid,zoneid,lastlogin) VALUES (@uid10,@aid10,@zoneid10,@lastlogin10)
1523 COMMIT TRAN
1524</procedure>
1525
1526<procedure name="recordonline" connection="auth0" operate="replaceB">
1527 <parameter name="uid" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1528 <parameter name="aid" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1529 <parameter name="zoneid" sql-type="integer" java-type="java.lang.Integer" in="true" out="true" />
1530 <parameter name="zonelocalid" sql-type="integer" java-type="java.lang.Integer" in="true" out="true" />
1531 <parameter name="overwrite" sql-type="integer" java-type="java.lang.Integer" in="true" out="true" />
1532 BEGIN TRAN
1533 DECLARE @tmp_zoneid integer
1534 DECLARE @tmp_zonelocalid integer
1535 SELECT @tmp_zoneid = zoneid, @tmp_zonelocalid = zonelocalid FROM point WHERE uid = @uid AND aid = @aid
1536 IF @@rowcount = 0
1537 INSERT INTO point (uid,aid,time,zoneid,zonelocalid,accountstart,lastlogin,enddate) VALUES (@uid,@aid,36000,@zoneid,@zonelocalid,getdate(),getdate(),'')
1538 ELSE IF @tmp_zoneid = NULL OR @tmp_zoneid = 0 OR @overwrite = 1
1539 UPDATE point SET zoneid=@zoneid, zonelocalid=@zonelocalid, accountstart=getdate(), lastlogin=getdate() WHERE uid = @uid AND aid = @aid
1540 IF @tmp_zoneid = NULL OR @tmp_zoneid = 0
1541 SELECT @overwrite = 1
1542 ELSE
1543 SELECT @zoneid = @tmp_zoneid, @zonelocalid = @tmp_zonelocalid
1544 COMMIT TRAN
1545</procedure>
1546
1547<procedure name="recordoffline" connection="auth0" operate="replaceB">
1548 <parameter name="uid" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1549 <parameter name="aid" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1550 <parameter name="zoneid" sql-type="integer" java-type="java.lang.Integer" in="true" out="true" />
1551 <parameter name="zonelocalid" sql-type="integer" java-type="java.lang.Integer" in="true" out="true" />
1552 <parameter name="overwrite" sql-type="integer" java-type="java.lang.Integer" in="true" out="true" />
1553 BEGIN TRAN
1554 exec accounting @uid, @aid, 1
1555 UPDATE point SET zoneid = 0, zonelocalid = 0 WHERE uid = @uid AND aid = @aid AND zoneid = @zoneid
1556 SELECT @overwrite = @@rowcount
1557 IF @overwrite = 0 SELECT @zoneid = zoneid, @zonelocalid = zonelocalid FROM point WHERE uid = @uid AND aid = @aid
1558 COMMIT TRAN
1559</procedure>
1560
1561<!--
1562 LOCK TABLE point IN EXCLUSIVE MODE
1563 set lock nowait
1564-->
1565<procedure name="accounting" connection="auth0" operate="replaceB">
1566 <!-- function's adduptime do not need cache.
1567 <parameter name="uid" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" cache="function_by_id"/>
1568 -->
1569 <parameter name="uid" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1570 <parameter name="aid" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1571 <parameter name="type" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1572 BEGIN TRAN
1573 DECLARE @remain integer
1574 DECLARE @enddate datetime
1575 DECLARE @accountstart datetime
1576 DECLARE @now datetime
1577 DECLARE @timeused integer
1578 DECLARE @timeminus integer
1579 SELECT @now = getdate()
1580 SELECT @remain = time, @enddate = enddate, @accountstart = accountstart FROM point WHERE @uid = uid AND @aid = aid
1581 IF @@rowcount = 0
1582 INSERT INTO point VALUES(@uid, @aid, 36000, 0, 0, CASE WHEN @type = 1 THEN '' ELSE @now END, '', '' )
1583 ELSE
1584 BEGIN
1585 IF @type = 0 OR '2005-01-01' > @accountstart
1586 BEGIN
1587 SELECT @timeused = 0
1588 SELECT @timeminus = 0
1589 END
1590 ELSE
1591 BEGIN
1592 SELECT @timeused = datediff(second, @accountstart, @now)
1593 SELECT @timeminus = CASE
1594 WHEN '2005-01-01' > @enddate OR (@now > @enddate AND @accountstart > @enddate) THEN @timeused
1595 WHEN @now > @enddate AND @enddate > @accountstart THEN datediff(second,@enddate,@now)
1596 ELSE 0
1597 END
1598 IF @timeminus > @remain SELECT @timeminus = @remain
1599 IF @timeminus > 3600 SELECT @timeminus = CASE WHEN @type = 1 THEN 0 ELSE 300 END
1600 END
1601 UPDATE point SET time=time-@timeminus, accountstart=CASE WHEN @type=1 THEN '' ELSE @now END WHERE @uid=uid AND @aid=aid
1602 UPDATE function SET adduptime = adduptime + @timeused WHERE @uid = uid
1603 IF @@rowcount = 0
1604 INSERT INTO function VALUES ( @uid, @timeused, 0,0,0,0,0,0,0,0,0 )
1605 END
1606 COMMIT TRAN
1607</procedure>
1608
1609<query name="downloadaward">
1610 <table name="awardnew" alias="cn"/>
1611 <table name="awardrecord" alias="cr"/>
1612 <column name="number" column="cn.number"/>
1613 <select name="all" condition="WHERE cn.rid = ? AND cn.rid = cr.id AND cr.status = 0"/>
1614</query>
1615
1616<query name="awardrecordids">
1617 <table name="awardrecord" alias="c"/>
1618 <column name="id" column="c.id"/>
1619 <select name="all" condition="ORDER BY c.id DESC"/>
1620 <select name="new" condition="WHERE c.status = 0 ORDER BY c.id DESC"/>
1621 <select name="audited" condition="WHERE c.status = 1 ORDER BY c.id DESC"/>
1622</query>
1623
1624<query name="awardrecordinfo">
1625 <table name="awardrecord" alias="c"/>
1626 <column name="code" column="c.code"/>
1627 <column name="number" column="c.number"/>
1628 <column name="endtime" column="c.endtime"/>
1629 <column name="creator" column="c.creator"/>
1630 <column name="creatime" column="c.creatime"/>
1631 <column name="auditor" column="c.auditor"/>
1632 <column name="auditime" column="c.auditime"/>
1633 <column name="status" column="c.status"/>
1634 <column name="func" column="c.func"/>
1635 <column name="used" column="c.used"/>
1636
1637 <select name="item" condition="WHERE c.id = ?"/>
1638 <select name="bycode" condition="WHERE c.code = ? and c.status = 1"/>
1639</query>
1640
1641<query name="downloadcard">
1642 <table name="cardnew" alias="cn"/>
1643 <table name="cardrecord" alias="cr"/>
1644 <column name="number" column="cn.number"/>
1645 <select name="all" condition="WHERE cn.rid = ? AND cn.rid = cr.id AND cr.status = 0 ORDER BY cn.number"/>
1646</query>
1647
1648<query name="cardrecordids">
1649 <table name="cardrecord" alias="c"/>
1650 <column name="id" column="c.id"/>
1651 <select name="all" condition="ORDER BY c.id DESC"/>
1652 <select name="new" condition="WHERE c.status = 0 ORDER BY c.id DESC"/>
1653 <select name="audited" condition="WHERE c.status = 1 ORDER BY c.id DESC"/>
1654</query>
1655
1656<query name="cardrecordinfo">
1657 <table name="cardrecord" alias="c"/>
1658 <column name="code" column="c.code"/>
1659 <column name="number" column="c.number"/>
1660 <column name="price" column="c.price"/>
1661 <column name="rate" column="c.rate"/>
1662 <column name="pointcard" column="c.pointcard"/>
1663 <column name="exchangepoint" column="c.exchangepoint"/>
1664 <column name="endtime" column="c.endtime"/>
1665 <column name="creator" column="c.creator"/>
1666 <column name="creatime" column="c.creatime"/>
1667 <column name="auditor" column="c.auditor"/>
1668 <column name="auditime" column="c.auditime"/>
1669 <column name="status" column="c.status"/>
1670 <column name="func" column="c.func"/>
1671 <column name="funcparm" column="c.funcparm"/>
1672 <column name="used" column="c.used"/>
1673 <column name="score" column="c.score"/>
1674
1675 <select name="item" condition="WHERE c.id = ?"/>
1676 <select name="bycode" condition="WHERE c.code = ? and c.status = 1"/>
1677</query>
1678
1679<query name="getcardused">
1680 <table name="cardused" alias="cu"/>
1681 <table name="cardrecord" alias="cr"/>
1682 <column name="namefrom" column="cu.namefrom"/>
1683 <column name="nameto" column="cu.nameto"/>
1684 <column name="number" column="cu.number"/>
1685 <column name="usedate" column="cu.usedate"/>
1686 <column name="pointcard" column="cr.pointcard"/>
1687 <column name="exchangepoint" column="cr.exchangepoint"/>
1688 <column name="ip" column="cu.ip"/>
1689 <column name="aid" column="cu.aid"/>
1690 <column name="zoneid" column="cu.zoneid"/>
1691
1692 <select name="byname" condition="WHERE cu.rid = cr.id AND cu.nameto = ?"/>
1693 <select name="bycard" condition="WHERE cu.rid = cr.id AND cu.number like ?"/>
1694</query>
1695
1696<query name="getUserPoints">
1697 <table name="point" alias="p" />
1698 <column name="aid" column="p.aid" />
1699 <column name="time" column="p.time" />
1700 <column name="enddate" column="p.enddate" />
1701 <select name="byuid" condition="where p.uid=? and NOT p.aid=0" />
1702</query>
1703
1704<query name="getUserAwardPoints">
1705 <table name="account" alias="u" />
1706 <table name="awardpoint" alias="p" />
1707 <column name="name" column="u.name" />
1708 <column name="point" column="p.point" />
1709 <column name="score" column="p.score" />
1710 <column name="awarddate" column="p.awarddate" />
1711 <column name="usedate" column="p.usedate" />
1712 <select name="byuid" condition="where p.touid = ? and u.id = p.fromuid ORDER BY p.awarddate DESC" />
1713</query>
1714
1715<query name="getUserSellPoints">
1716 <table name="account" alias="u" />
1717 <table name="translog" alias="p" />
1718 <column name="zoneid" column="p.zoneid" />
1719 <column name="serial" column="p.serial" />
1720 <column name="sellid" column="p.sellid" />
1721 <column name="buyer" column="u.name" />
1722 <column name="price" column="p.price" />
1723 <column name="point" column="p.point" />
1724 <column name="aid" column="p.aid" />
1725 <column name="status" column="p.status" />
1726 <column name="date" column="p.date" />
1727 <select name="byuid" condition="where p.seller = ? and u.id = p.buyer ORDER BY p.date" />
1728</query>
1729
1730<query name="monthexchanged">
1731 <table name="account" alias="u"/>
1732 <table name="monthbill" alias="m"/>
1733 <column name="uid" column="m.uid"/>
1734 <column name="aid" column="m.aid"/>
1735 <column name="usepoint" column="m.usepoint"/>
1736 <column name="monthcount" column="m.monthcount"/>
1737 <column name="enddate" column="m.enddate"/>
1738 <column name="usedate" column="m.usedate"/>
1739
1740 <select name="byname" condition="WHERE u.id = m.uid AND u.name = ? AND m.usedate >= ? and m.usedate <= ? "/>
1741</query>
1742
1743<procedure name="addawardrecord" connection="auth0" operate="replaceA">
1744 <parameter name="code" sql-type="varchar(8)" java-type="java.lang.String" in="true" out="false" />
1745 <parameter name="endtime" sql-type="varchar(32)" java-type="java.lang.String" in="true" out="false" />
1746 <parameter name="creator" sql-type="varchar(64)" java-type="java.lang.String" in="true" out="false" />
1747 <parameter name="func" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1748 <parameter name="id" sql-type="integer" java-type="java.lang.Integer" in="true" out="true" />
1749 BEGIN TRAN
1750 SELECT @id = ISNULL(max(id), 0)+1 FROM awardrecord HOLDLOCK
1751 INSERT INTO awardrecord VALUES(@id,@code,0,@endtime,@creator,getdate(),@creator,getdate(),0,@func, 0)
1752 COMMIT TRAN
1753</procedure>
1754
1755<procedure name="addaward" connection="auth0" operate="replaceA">
1756 <parameter name="id" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1757 <parameter name="number0" sql-type="char(8)" java-type="java.lang.String" in="true" out="false" />
1758 <parameter name="number1" sql-type="char(8)" java-type="java.lang.String" in="true" out="false" />
1759 <parameter name="number2" sql-type="char(8)" java-type="java.lang.String" in="true" out="false" />
1760 <parameter name="number3" sql-type="char(8)" java-type="java.lang.String" in="true" out="false" />
1761 <parameter name="number4" sql-type="char(8)" java-type="java.lang.String" in="true" out="false" />
1762 <parameter name="number5" sql-type="char(8)" java-type="java.lang.String" in="true" out="false" />
1763 <parameter name="number6" sql-type="char(8)" java-type="java.lang.String" in="true" out="false" />
1764 <parameter name="number7" sql-type="char(8)" java-type="java.lang.String" in="true" out="false" />
1765 <parameter name="number8" sql-type="char(8)" java-type="java.lang.String" in="true" out="false" />
1766 <parameter name="number9" sql-type="char(8)" java-type="java.lang.String" in="true" out="false" />
1767 BEGIN TRAN
1768 IF EXISTS (SELECT * FROM awardused HOLDLOCK WHERE number IN(@number0,@number1,@number2,@number3,@number4,@number5,@number6,@number7,@number8,@number9))
1769 BEGIN
1770 rollback tran
1771 return -1
1772 END
1773 INSERT INTO awardnew VALUES(@id, @number0)
1774 INSERT INTO awardnew VALUES(@id, @number1)
1775 INSERT INTO awardnew VALUES(@id, @number2)
1776 INSERT INTO awardnew VALUES(@id, @number3)
1777 INSERT INTO awardnew VALUES(@id, @number4)
1778 INSERT INTO awardnew VALUES(@id, @number5)
1779 INSERT INTO awardnew VALUES(@id, @number6)
1780 INSERT INTO awardnew VALUES(@id, @number7)
1781 INSERT INTO awardnew VALUES(@id, @number8)
1782 INSERT INTO awardnew VALUES(@id, @number9)
1783 UPDATE awardrecord SET number = number + 10 WHERE id = @id
1784 COMMIT TRAN
1785</procedure>
1786
1787<procedure name="deleteaward" connection="auth0" operate="replaceA">
1788 <parameter name="id" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1789 BEGIN TRAN
1790 DELETE FROM awardrecord WHERE id = @id AND status = 0
1791 if @@rowcount = 1 DELETE FROM awardnew WHERE rid = @id
1792 COMMIT TRAN
1793</procedure>
1794
1795<procedure name="auditaward" connection="auth0" operate="replaceA">
1796 <parameter name="id" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1797 <parameter name="auditor" sql-type="varchar(64)" java-type="java.lang.String" in="true" out="false" />
1798 <parameter name="status" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1799 BEGIN TRAN
1800 UPDATE awardrecord SET auditor = @auditor, auditime = getdate(), status = @status WHERE id = @id AND status = 0
1801 COMMIT TRAN
1802</procedure>
1803
1804<procedure name="addcardrecord" connection="auth0" operate="replaceA">
1805 <parameter name="code" sql-type="varchar(12)" java-type="java.lang.String" in="true" out="false" />
1806 <parameter name="price" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1807 <parameter name="rate" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1808 <parameter name="pointcard" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1809 <parameter name="exchangepoint" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1810 <parameter name="endtime" sql-type="varchar(32)" java-type="java.lang.String" in="true" out="false" />
1811 <parameter name="creator" sql-type="varchar(64)" java-type="java.lang.String" in="true" out="false" />
1812 <parameter name="func" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1813 <parameter name="funcparm" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1814 <parameter name="score" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1815 <parameter name="id" sql-type="integer" java-type="java.lang.Integer" in="true" out="true" />
1816 BEGIN TRAN
1817 SELECT @id = ISNULL(max(id), 0)+1 FROM cardrecord HOLDLOCK
1818 IF NOT EXISTS (SELECT * FROM cardrecord WHERE code = @code)
1819 INSERT INTO cardrecord (id,code,number,price,rate,pointcard,exchangepoint,endtime,creator,creatime,auditor,auditime,status,func,funcparm,used,score) VALUES(@id,@code,0,@price,@rate,@pointcard,@exchangepoint,@endtime,@creator,getdate(),@creator,getdate(),0,@func,@funcparm, 0, @score)
1820 ELSE
1821 SELECT @id = -1
1822 COMMIT TRAN
1823</procedure>
1824
1825<procedure name="addcard" connection="auth0" operate="replaceA">
1826 <parameter name="id" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1827 <parameter name="status" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1828 <parameter name="number0" sql-type="varchar(32)" java-type="java.lang.String" in="true" out="false" />
1829 <parameter name="number1" sql-type="varchar(32)" java-type="java.lang.String" in="true" out="false" />
1830 <parameter name="number2" sql-type="varchar(32)" java-type="java.lang.String" in="true" out="false" />
1831 <parameter name="number3" sql-type="varchar(32)" java-type="java.lang.String" in="true" out="false" />
1832 <parameter name="number4" sql-type="varchar(32)" java-type="java.lang.String" in="true" out="false" />
1833 <parameter name="number5" sql-type="varchar(32)" java-type="java.lang.String" in="true" out="false" />
1834 <parameter name="number6" sql-type="varchar(32)" java-type="java.lang.String" in="true" out="false" />
1835 <parameter name="number7" sql-type="varchar(32)" java-type="java.lang.String" in="true" out="false" />
1836 <parameter name="number8" sql-type="varchar(32)" java-type="java.lang.String" in="true" out="false" />
1837 <parameter name="number9" sql-type="varchar(32)" java-type="java.lang.String" in="true" out="false" />
1838 DECLARE @cardstatus integer
1839
1840 -- cardstatus only value 0 or 1
1841 SELECT @cardstatus = 0
1842 IF (@status = 1)
1843 BEGIN
1844 SELECT @cardstatus = 1
1845 END
1846
1847 BEGIN TRAN
1848 IF EXISTS (SELECT * FROM cardnew HOLDLOCK WHERE number IN(@number0,@number1,@number2,@number3,@number4,@number5,@number6,@number7,@number8,@number9))
1849 BEGIN
1850 rollback tran
1851 return -1
1852 END
1853 INSERT INTO cardnew (rid, number, status) VALUES(@id, @number0, @cardstatus)
1854 INSERT INTO cardnew (rid, number, status) VALUES(@id, @number1, @cardstatus)
1855 INSERT INTO cardnew (rid, number, status) VALUES(@id, @number2, @cardstatus)
1856 INSERT INTO cardnew (rid, number, status) VALUES(@id, @number3, @cardstatus)
1857 INSERT INTO cardnew (rid, number, status) VALUES(@id, @number4, @cardstatus)
1858 INSERT INTO cardnew (rid, number, status) VALUES(@id, @number5, @cardstatus)
1859 INSERT INTO cardnew (rid, number, status) VALUES(@id, @number6, @cardstatus)
1860 INSERT INTO cardnew (rid, number, status) VALUES(@id, @number7, @cardstatus)
1861 INSERT INTO cardnew (rid, number, status) VALUES(@id, @number8, @cardstatus)
1862 INSERT INTO cardnew (rid, number, status) VALUES(@id, @number9, @cardstatus)
1863 UPDATE cardrecord SET number = number + 10 WHERE id = @id
1864 COMMIT TRAN
1865</procedure>
1866
1867
1868<procedure name="getcardstatus" connection="auth0" operate="replaceA">
1869 <parameter name="fromnumber" sql-type="varchar(32)" java-type="java.lang.String" in="true" out="false" />
1870 <parameter name="tonumber" sql-type="varchar(32)" java-type="java.lang.String" in="true" out="false" />
1871 <parameter name="status" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1872 <parameter name="op" sql-type="varchar(32)" java-type="java.lang.String" in="true" out="false" />
1873 <parameter name="retcount" sql-type="integer" java-type="java.lang.Integer" in="false" out="true" />
1874 BEGIN TRAN
1875 SELECT @retcount = count(number) FROM cardnew WHERE number >= @fromnumber and @tonumber >= number and status = @status
1876 INSERT INTO cardstatuslog (date,fromnumber,tonumber,fromstatus,tostatus,op,retcount) VALUES (getdate(),@fromnumber,@tonumber,0-@status,0-@status,@op,@retcount)
1877 COMMIT TRAN
1878</procedure>
1879
1880<procedure name="checkcardstatus" connection="auth0" operate="replaceA">
1881 <parameter name="fromnumber" sql-type="varchar(32)" java-type="java.lang.String" in="true" out="false" />
1882 <parameter name="tonumber" sql-type="varchar(32)" java-type="java.lang.String" in="true" out="false" />
1883 <parameter name="status" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1884 <parameter name="op" sql-type="varchar(32)" java-type="java.lang.String" in="true" out="false" />
1885 <parameter name="retcount" sql-type="integer" java-type="java.lang.Integer" in="false" out="true" />
1886 BEGIN TRAN
1887 SELECT @retcount = count(number) FROM cardnew WHERE number >= @fromnumber and @tonumber >= number and not status = @status
1888 INSERT INTO cardstatuslog (date,fromnumber,tonumber,fromstatus,tostatus,op,retcount) VALUES (getdate(),@fromnumber,@tonumber,@status,@status,@op,@retcount)
1889 COMMIT TRAN
1890</procedure>
1891
1892<procedure name="changecardstatus" connection="auth0" operate="replaceA">
1893 <parameter name="fromnumber" sql-type="varchar(32)" java-type="java.lang.String" in="true" out="false" />
1894 <parameter name="tonumber" sql-type="varchar(32)" java-type="java.lang.String" in="true" out="false" />
1895 <parameter name="fromstatus" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1896 <parameter name="tostatus" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1897 <parameter name="op" sql-type="varchar(32)" java-type="java.lang.String" in="true" out="false" />
1898 <parameter name="retcount" sql-type="integer" java-type="java.lang.Integer" in="false" out="true" />
1899 BEGIN TRAN
1900 SELECT @retcount = 0
1901 UPDATE cardnew set status = @tostatus WHERE number >= @fromnumber AND @tonumber >= number AND @fromstatus = status
1902 SELECT @retcount = @@rowcount
1903 INSERT INTO cardstatuslog (date,fromnumber,tonumber,fromstatus,tostatus,op,retcount) VALUES (getdate(),@fromnumber,@tonumber,@fromstatus,@tostatus,@op,@retcount)
1904 COMMIT TRAN
1905</procedure>
1906
1907<procedure name="deletecard" connection="auth0" operate="replaceA">
1908 <parameter name="id" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1909 BEGIN TRAN
1910 DELETE FROM cardrecord WHERE id = @id AND status = 0
1911 if @@rowcount = 1 DELETE FROM cardnew WHERE rid = @id
1912 COMMIT TRAN
1913</procedure>
1914
1915<procedure name="auditcard" connection="auth0" operate="replaceA">
1916 <parameter name="id" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1917 <parameter name="auditor" sql-type="varchar(64)" java-type="java.lang.String" in="true" out="false" />
1918 <parameter name="status" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1919 BEGIN TRAN
1920 UPDATE cardrecord SET auditor = @auditor, auditime = getdate(), status = @status WHERE id = @id AND status = 0
1921 COMMIT TRAN
1922</procedure>
1923
1924<procedure name="querycardnew" connection="auth0" operate="replaceB">
1925 <parameter name="number" sql-type="varchar(32)" java-type="java.lang.String" in="true" out="false" />
1926 <parameter name="numberall" sql-type="varchar(32)" java-type="java.lang.String" in="false" out="true" />
1927 <parameter name="cnstatus" sql-type="integer" java-type="java.lang.Integer" in="false" out="true" />
1928 <parameter name="code" sql-type="varchar(32)" java-type="java.lang.String" in="false" out="true" />
1929 <parameter name="price" sql-type="integer" java-type="java.lang.Integer" in="false" out="true" />
1930 <parameter name="rate" sql-type="integer" java-type="java.lang.Integer" in="false" out="true" />
1931 <parameter name="pointcard" sql-type="integer" java-type="java.lang.Integer" in="false" out="true" />
1932 <parameter name="exchangepoint" sql-type="integer" java-type="java.lang.Integer" in="false" out="true" />
1933 <parameter name="strendtime" sql-type="varchar(32)" java-type="java.lang.String" in="false" out="true" />
1934 <parameter name="crstatus" sql-type="integer" java-type="java.lang.Integer" in="false" out="true" />
1935 <parameter name="func" sql-type="integer" java-type="java.lang.Integer" in="false" out="true" />
1936 <parameter name="funcparm" sql-type="integer" java-type="java.lang.Integer" in="false" out="true" />
1937 <parameter name="score" sql-type="integer" java-type="java.lang.Integer" in="false" out="true" />
1938 <parameter name="error" sql-type="integer" java-type="java.lang.Integer" in="true" out="true" />
1939 BEGIN TRAN
1940 DECLARE @number_leng integer
1941 DECLARE @cr_id integer
1942 SELECT @error = -1
1943 SELECT @number_leng = char_length(@number)
1944 IF @number_leng >= 10
1945 BEGIN
1946 SELECT TOP 1 @cr_id = cn.rid, @numberall = cn.number, @cnstatus = cn.status FROM cardnew cn WHERE cn.number like @number
1947 IF @@rowcount = 1
1948 BEGIN
1949 SELECT @code = cr.code, @price = cr.price, @rate = cr.rate, @pointcard = cr.pointcard, @exchangepoint = cr.exchangepoint, @strendtime = convert(varchar(12),cr.endtime,102)+' '+convert(varchar(12),cr.endtime,108), @crstatus = cr.status, @func = cr.func, @funcparm = cr.funcparm, @score = cr.score FROM cardrecord cr WHERE @cr_id = cr.id
1950 SELECT @error = 0
1951 END
1952 ELSE
1953 SELECT @error = 1
1954 END
1955 ELSE
1956 SELECT @error = 2
1957 COMMIT TRAN
1958</procedure>
1959
1960<!--
1961 error
1962 -2: ¿¨ºÅ²»´æÔÚ
1963 -3: Óû§²»´æÔÚ
1964 -4: ÃÜÂë´ÃÎó
1965 -5: ¸Ã¿¨ÒѹýÆÚ
1966 -6: ¸Ã¿¨Î´¿ªÃ¨
1967-->
1968<procedure name="usepointcard" connection="auth0" operate="replaceC">
1969 <parameter name="agent" sql-type="char(1)" java-type="java.lang.String" in="true" out="false" />
1970 <parameter name="number" sql-type="varchar(32)" java-type="java.lang.String" in="true" out="false" />
1971 <parameter name="fromname" sql-type="varchar(32)" java-type="byte[]" in="true" out="false" />
1972 <parameter name="uid" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" cache="function_by_id" />
1973 <parameter name="aid" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1974 <parameter name="zoneid" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1975 <parameter name="ip" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
1976 <parameter name="error" sql-type="integer" java-type="java.lang.Integer" in="false" out="true" />
1977 BEGIN TRAN
1978 DECLARE @rid integer
1979 DECLARE @toname varchar(32)
1980 DECLARE @maxid integer
1981 DECLARE @number_code varchar(32)
1982 DECLARE @addpoint integer
1983 DECLARE @addscore integer
1984 DECLARE @crstatus integer
1985 DECLARE @cnstatus integer
1986 DECLARE @func integer
1987 DECLARE @funcparm integer
1988 DECLARE @money integer
1989 DECLARE @nextbillid integer
1990 DECLARE @now datetime
1991 DECLARE @crendtime datetime
1992 DECLARE @oldadduppoint integer
1993 SELECT @now = getdate()
1994
1995 SELECT @rid = cr.id, @money = cr.price, @addpoint = cr.pointcard * cr.exchangepoint, @crendtime = cr.endtime, @crstatus = cr.status, @func = cr.func, @funcparm = cr.funcparm, @addscore = cr.score, @cnstatus = cn.status FROM cardrecord cr, cardnew cn WHERE cn.number = @number AND cn.rid = cr.id
1996 IF @@rowcount = 1
1997 BEGIN
1998 IF NOT @crstatus = 1 OR NOT @cnstatus = 1
1999 SELECT @error = -6
2000 ELSE
2001 BEGIN
2002 SELECT @toname = name FROM account WHERE @uid = id
2003 IF @@rowcount = 1
2004 BEGIN
2005 IF @aid >= 9
2006 BEGIN
2007 SELECT @addscore = 0
2008 END
2009
2010 UPDATE point SET time = time + @addpoint WHERE @uid = uid AND @aid = aid
2011 IF @@rowcount = 0 INSERT INTO point (uid,aid,time,zoneid,zonelocalid,accountstart,lastlogin,enddate) VALUES (@uid,@aid,@addpoint+36000, 0, 0, '', '', '' )
2012
2013 SELECT @oldadduppoint = adduppoint FROM function WHERE @uid = uid
2014 IF @oldadduppoint > 2100000000 UPDATE function SET adduppoint = 0, soldpoint = 0 WHERE @uid = uid
2015
2016 UPDATE function SET score=score+@addscore,func=case when @func > 0 then @func else func end,funcparm=case when @func > 0 then @funcparm else funcparm end,addupmoney=addupmoney+@money,adduppoint=adduppoint+@addpoint,addupscore=addupscore+@addscore WHERE @uid = uid
2017 IF @@rowcount = 0 INSERT INTO function (uid,adduptime,score,func,funcparm,addupmoney,adduppoint,addupscore,coin,addupcoin,soldpoint) VALUES ( @uid, 0, @addscore, @func, @funcparm, @money, @addpoint, @addscore, 0, 0, 0 )
2018
2019 INSERT INTO cardused (rid,number,namefrom,nameto,usedate,ip,aid,zoneid) VALUES (@rid,@number,@fromname,@toname,@now,@ip,@aid,@zoneid)
2020 DELETE FROM cardnew WHERE number = @number
2021 UPDATE cardrecord SET used = used + 1 WHERE id = @rid
2022
2023 UPDATE max_ids SET maxid = max(maxid)+1 WHERE type = 1
2024 SELECT @maxid = max(maxid) FROM max_ids WHERE type = 1
2025 INSERT INTO agentbill VALUES (@maxid, @agent, @uid, @aid, @rid, @money, @addpoint, @addscore, getdate(), @zoneid, 0, 0, 0, 0, 0)
2026
2027 SELECT @error = @addpoint
2028 END
2029 ELSE SELECT @error = -3
2030 END
2031 END
2032 ELSE
2033 BEGIN
2034 SELECT @number_code = substring(@number,1,10)+'%'
2035 IF EXISTS ( SELECT * from cardnew WHERE number like @number_code )
2036 SELECT @error = -4
2037 ELSE
2038 SELECT @error = -2
2039 END
2040 COMMIT TRAN
2041</procedure>
2042
2043<!--
2044 error
2045 -1: δ֪´ÃÎó
2046 -2: Óû§²»´æÔÚ
2047 -3: ýÕÅ¿¨Ãܶ¼´ÃÎó
2048 -4: µÚÒ»ÕÅ¿¨ÃÜ´ÃÎó
2049 -5: µÚ¶þÕÅ¿¨ÃÜ´ÃÎó
2050 -6: ýÕÅ¿¨¶¼²»ÊÇ30Ôª¿¨
2051 -7: µÚÒ»ÕÅ¿¨²»ÊÇ30Ôª¿¨
2052 -8: µÚ¶þÕÅ¿¨²»ÊÇ30Ôª¿¨
2053 DECLARE @toname varchar(32)
2054-->
2055<procedure name="batchusepointcard" connection="auth0" operate="replaceC">
2056 <parameter name="agent1" sql-type="char(1)" java-type="java.lang.String" in="true" out="false" />
2057 <parameter name="number1" sql-type="varchar(32)" java-type="java.lang.String" in="true" out="false" />
2058 <parameter name="agent2" sql-type="char(1)" java-type="java.lang.String" in="true" out="false" />
2059 <parameter name="number2" sql-type="varchar(32)" java-type="java.lang.String" in="true" out="false" />
2060 <parameter name="fromname" sql-type="varchar(32)" java-type="byte[]" in="true" out="false" />
2061 <parameter name="uid" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" cache="function_by_id" />
2062 <parameter name="aid" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
2063 <parameter name="zoneid" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
2064 <parameter name="ip" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
2065 <parameter name="error" sql-type="integer" java-type="java.lang.Integer" in="false" out="true" />
2066 BEGIN TRAN
2067 DECLARE @error1 integer
2068 DECLARE @error2 integer
2069 SELECT @error = -1
2070
2071 exec usepointcard @agent1, @number1, @fromname, @uid, @aid, @zoneid, @ip, @error1 out
2072 exec usepointcard @agent2, @number2, @fromname, @uid, @aid, @zoneid, @ip, @error2 out
2073
2074 IF @error1 > 0 AND @error2 > 0 AND 270000 = @error1 AND 270000 = @error2
2075 BEGIN
2076 COMMIT TRAN
2077 SELECT @error = @error1 + @error2
2078 END
2079 ELSE
2080 BEGIN
2081 ROLLBACK TRAN
2082 IF -3 = @error1 OR -3 = @error2
2083 SELECT @error = -2
2084 ELSE IF 0 >= @error1 AND 0 >= @error2
2085 SELECT @error = -3
2086 ELSE IF 0 >= @error1
2087 SELECT @error = -4
2088 ELSE IF 0 >= @error2
2089 SELECT @error = -5
2090 ELSE IF NOT 270000 = @error1 AND NOT 270000 = @error2
2091 SELECT @error = -6
2092 ELSE IF NOT 270000 = @error1
2093 SELECT @error = -7
2094 ELSE IF NOT 270000 = @error2
2095 SELECT @error = -8
2096 ELSE
2097 SELECT @error = -1
2098 SELECT @error1, @error2, @error
2099 END
2100</procedure>
2101
2102<query name="queryAgentBill">
2103 <table name="agentbill" alias="b"/>
2104 <column name="billid" column="b.billid"/>
2105 <column name="agent" column="b.agent"/>
2106 <column name="uid" column="b.uid"/>
2107 <column name="aid" column="b.aid"/>
2108 <column name="cardtype" column="b.cardtype"/>
2109 <column name="money" column="b.money"/>
2110 <column name="addpoint" column="b.addpoint"/>
2111 <column name="addscore" column="b.addscore"/>
2112 <column name="usedate" column="b.usedate"/>
2113 <column name="cookie1" column="b.cookie1"/>
2114 <column name="cookie2" column="b.cookie2"/>
2115 <column name="addcoin" column="b.addcoin"/>
2116 <column name="awarduid" column="b.awarduid"/>
2117 <column name="awardpoint" column="b.awardpoint"/>
2118 <column name="awardscore" column="b.awardscore"/>
2119 <select name="byBillidAgent" condition="WHERE b.billid = ? and b.agent = ?"/>
2120 <select name="byBillid" condition="WHERE b.billid = ?"/>
2121 <select name="byUidDateRange" condition="(index ind_agentbill_uidaid) WHERE b.uid = ? and b.usedate >= ? and b.usedate <= ?"/>
2122</query>
2123
2124<procedure name="useagentcard" connection="auth0" operate="replaceC">
2125 <parameter name="billid" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
2126 <parameter name="agent" sql-type="char(1)" java-type="java.lang.String" in="true" out="false" />
2127 <parameter name="uid" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" cache="function_by_id" />
2128 <parameter name="aid" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
2129 <parameter name="cardtype" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
2130 <parameter name="money" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
2131 <parameter name="addpoint" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
2132 <parameter name="addscore" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
2133 <parameter name="cookie1" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
2134 <parameter name="cookie2" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
2135 <parameter name="addcoin" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
2136 <parameter name="awarduid" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
2137 <parameter name="awardpoint" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
2138 <parameter name="awardscore" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
2139 <parameter name="error" sql-type="integer" java-type="java.lang.Integer" in="true" out="true" />
2140 BEGIN TRAN
2141 DECLARE @pointexists integer
2142 DECLARE @oldtime integer
2143 DECLARE @oldaddupmoney integer
2144 DECLARE @oldadduppoint integer
2145 SELECT @pointexists = 0
2146 IF NOT EXISTS ( SELECT * FROM agentbill WHERE @billid = billid AND @agent = agent )
2147 BEGIN
2148 -- IF @error = 0
2149 -- BEGIN
2150 -- SELECT @uid = id FROM account WHERE @name = name
2151 -- IF NOT @@rowcount = 1 SELECT @error = 2
2152 -- END
2153 IF @error = 0 AND @awarduid > 0
2154 BEGIN
2155 IF NOT EXISTS ( SELECT * FROM account WHERE @awarduid = id )
2156 SELECT @error = 7
2157 ELSE
2158 BEGIN
2159 SELECT @oldaddupmoney = addupmoney FROM function WHERE @uid = uid
2160 IF @@rowcount = 1 AND @oldaddupmoney > 0 SELECT @error = 8
2161 IF EXISTS ( SELECT * FROM awardpoint where @uid = fromuid )
2162 SELECT @error = 8
2163 SELECT @oldaddupmoney = addupmoney FROM function WHERE @awarduid = uid
2164 IF @@rowcount = 0 OR @oldaddupmoney = 0 SELECT @error = 10
2165 END
2166 END
2167 IF @error = 0
2168 BEGIN
2169 SELECT @oldtime=time FROM point WHERE @uid = uid AND @aid = aid
2170 IF @@rowcount = 1 SELECT @pointexists = 1
2171 IF @pointexists = 1 AND @oldtime+@addpoint > 2100000000 SELECT @error = -1
2172 END
2173 IF @error = 0
2174 BEGIN
2175 INSERT INTO agentbill (billid, agent, uid, aid, cardtype, money, addpoint, addscore, usedate, cookie1, cookie2, addcoin, awarduid, awardpoint, awardscore) VALUES ( @billid, @agent, @uid, @aid, @cardtype, @money, @addpoint, @addscore, getdate(), @cookie1, @cookie2, @addcoin, @awarduid, @awardpoint, @awardscore )
2176 IF @pointexists = 1
2177 UPDATE point SET time = ISNULL(time,0) + @addpoint WHERE @uid = uid AND @aid = aid
2178 ELSE
2179 INSERT INTO point (uid,aid,time,zoneid,zonelocalid,accountstart,lastlogin,enddate) VALUES ( @uid, @aid, @addpoint+36000, 0, 0, '', '', '' )
2180
2181 SELECT @oldadduppoint = adduppoint FROM function WHERE @uid = uid
2182 IF @oldadduppoint > 2100000000 UPDATE function SET adduppoint = 0, soldpoint = 0 WHERE @uid = uid
2183
2184 UPDATE function SET score=score+@addscore,addupmoney=addupmoney+@money,adduppoint=adduppoint+@addpoint,addupscore=addupscore+@addscore,coin=coin+@addcoin,addupcoin=addupcoin+@addcoin WHERE @uid = uid
2185 IF @@rowcount = 0 INSERT INTO function (uid,adduptime,score,func,funcparm,addupmoney,adduppoint,addupscore,coin,addupcoin,soldpoint) VALUES (@uid, 0, @addscore, 0, 0, @money, @addpoint, @addscore, @addcoin, @addcoin, 0 )
2186 IF @awarduid > 0
2187 INSERT INTO awardpoint (fromuid, touid, point, score, awarddate, usedate) VALUES (@uid, @awarduid, @awardpoint, @awardscore, getdate(), null)
2188 END
2189 ELSE
2190 BEGIN
2191 SELECT @uid = @error
2192 INSERT INTO agentbill (billid, agent, uid, aid, cardtype, money, addpoint, addscore, usedate, cookie1, cookie2, addcoin, awarduid, awardpoint, awardscore) VALUES ( @billid, @agent, @uid, @aid, @cardtype, @money, @addpoint, @addscore, getdate(), @cookie1, @cookie2, @addcoin, @awarduid, @awardpoint, @awardscore )
2193 IF NOT @error=2 AND NOT @error=7 AND NOT @error=8 AND NOT @error=10 AND NOT @error=-1
2194 SELECT @error = 0
2195 END
2196 END
2197 ELSE SELECT @error = 1
2198 COMMIT TRAN
2199</procedure>
2200
2201<!--procedure name="addinnerpoint" connection="auth0" operate="replaceC">
2202 <parameter name="uid" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" cache="function_by_id" />
2203 <parameter name="aid" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
2204 <parameter name="zoneid" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
2205 <parameter name="addpoint" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
2206 <parameter name="enddate" sql-type="varchar(64)" java-type="java.lang.String" in="true" out="false" />
2207 <parameter name="monthcount" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
2208 <parameter name="addscore" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
2209 <parameter name="operator" sql-type="varchar(64)" java-type="java.lang.String" in="true" out="false" />
2210 <parameter name="error" sql-type="integer" java-type="java.lang.Integer" in="false" out="true" />
2211 BEGIN TRAN
2212 DECLARE @realenddate datetime
2213 DECLARE @now datetime
2214 DECLARE @enddate_leng integer
2215 DECLARE @oldpoint integer
2216 DECLARE @haspoint integer
2217 SELECT @error = 0
2218 SELECT @now = getdate()
2219
2220 SELECT @realenddate = NULL
2221 SELECT @enddate_leng = char_length(@enddate)
2222 IF @enddate > getdate()
2223 SELECT @realenddate = @enddate
2224 ELSE IF 2 > @enddate_leng
2225 SELECT @realenddate = enddate FROM point WHERE @uid = uid AND @aid = aid
2226
2227 IF @monthcount > 0
2228 SELECT @realenddate = dateadd(day,30*@monthcount,ISNULL(@realenddate,@now))
2229
2230 IF @realenddate = NULL SELECT @realenddate = ''
2231
2232 SELECT @oldpoint = time FROM point WHERE @uid = uid AND @aid = aid
2233 IF @@rowcount = 0 SELECT @haspoint = 0
2234 ELSE SELECT @haspoint = 1
2235 IF @haspoint = 0 SELECT @oldpoint = 0
2236 IF 0 > @addpoint AND 0 > @oldpoint+@addpoint
2237 SELECT @error = -1
2238 ELSE
2239 BEGIN
2240 IF @haspoint = 0
2241 INSERT INTO point (uid,aid,time,zoneid,zonelocalid,accountstart,lastlogin,enddate) VALUES (@uid,@aid,@addpoint+36000, 0, 0, '', '', @realenddate)
2242 ELSE
2243 UPDATE point SET time=time+@addpoint, enddate=@realenddate WHERE @uid = uid AND @aid = aid
2244
2245 UPDATE function SET score=score+@addscore,adduppoint=adduppoint+@addpoint,addupscore=addupscore+@addscore WHERE @uid = uid
2246 IF @@rowcount = 0 INSERT INTO function (uid,adduptime,score,func,funcparm,addupmoney,adduppoint,addupscore,coin,addupcoin,soldpoint) VALUES ( @uid, 0, @addscore, 0, 0, 0, 0, @addscore, 0, 0, 0 )
2247
2248 INSERT INTO innerbill (uid, aid, addpoint, enddate, operator, usedate, monthcount, addscore, zoneid) VALUES (@uid, @aid, @addpoint, @realenddate, @operator, @now, @monthcount, @addscore, @zoneid )
2249 END
2250 COMMIT TRAN
2251</procedure-->
2252
2253<!--procedure name="useawardpoint" connection="auth0" operate="replaceC">
2254 <parameter name="uid" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" cache="function_by_id" />
2255 <parameter name="aid" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
2256 <parameter name="fromname" sql-type="varchar(64)" java-type="java.lang.String" in="true" out="false" />
2257 <parameter name="error" sql-type="integer" java-type="java.lang.Integer" in="false" out="true" />
2258 BEGIN TRAN
2259 DECLARE @fromuid integer
2260 DECLARE @addpoint integer
2261 DECLARE @addscore integer
2262
2263 SELECT @error = 0
2264
2265 IF @error = 0
2266 BEGIN
2267 SELECT @fromuid = id FROM account WHERE @fromname = name
2268 IF NOT @@rowcount = 1 SELECT @error = 2
2269 END
2270 IF @error = 0
2271 BEGIN
2272 SELECT @addpoint=point, @addscore=score FROM awardpoint WHERE @fromuid = fromuid AND @uid = touid
2273 IF NOT @@rowcount = 1 SELECT @error = 3
2274 END
2275 IF @error = 0
2276 BEGIN
2277 IF NOT EXISTS (SELECT * FROM awardpoint WHERE @fromuid=fromuid AND @uid=touid AND usedate = null)
2278 SELECT @error = 4
2279 END
2280 IF @error = 0
2281 BEGIN
2282 UPDATE awardpoint SET usedate = getdate() WHERE @fromuid = fromuid AND @uid = touid
2283
2284 UPDATE point SET time = ISNULL(time,0) + @addpoint WHERE @uid = uid AND @aid = aid
2285 IF @@rowcount = 0 INSERT INTO point (uid,aid,time,zoneid,zonelocalid,accountstart,lastlogin,enddate) VALUES ( @uid, @aid, @addpoint+36000, 0, 0, '', '', '' )
2286
2287 UPDATE function SET score=score+@addscore,adduppoint=adduppoint+@addpoint,addupscore=addupscore+@addscore WHERE @uid = uid
2288 IF @@rowcount = 0 INSERT INTO function (uid,adduptime,score,func,funcparm,addupmoney,adduppoint,addupscore,coin,addupcoin,soldpoint) VALUES (@uid, 0, @addscore, 0, 0, 0, @addpoint, @addscore, 0, 0, 0 )
2289 END
2290 COMMIT TRAN
2291</procedure-->
2292
2293<procedure name="exchangemonth" connection="auth0" operate="replaceB">
2294 <parameter name="name" sql-type="varchar(64)" java-type="java.lang.String" in="true" out="false" />
2295 <parameter name="aid" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
2296 <parameter name="monthcount" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
2297 <parameter name="error" sql-type="integer" java-type="java.lang.Integer" in="false" out="true" />
2298 <parameter name="remain" sql-type="integer" java-type="java.lang.Integer" in="false" out="true" />
2299 <parameter name="strenddate" sql-type="varchar(32)" java-type="java.lang.String" in="false" out="true" />
2300 BEGIN TRAN
2301 DECLARE @uid integer
2302 DECLARE @oldtime integer
2303 DECLARE @oldenddate datetime
2304 DECLARE @usepoint integer
2305 DECLARE @now datetime
2306 DECLARE @enddate datetime
2307 DECLARE @summoney integer
2308 SELECT @error = 0
2309 SELECT @remain = 0
2310 SELECT @uid = id FROM account WHERE @name = name
2311 IF @@rowcount = 1
2312 BEGIN
2313 SELECT @oldtime=time,@oldenddate=enddate FROM point WHERE @uid = uid AND @aid = aid
2314 SELECT @usepoint = @monthcount*522000
2315 IF @@rowcount = 1 AND @oldtime >= @usepoint
2316 BEGIN
2317 SELECT @now = getdate()
2318 IF @now > @oldenddate SELECT @oldenddate = @now
2319 SELECT @enddate = dateadd(day,30*@monthcount,ISNULL(@oldenddate,@now))
2320 UPDATE point SET time = @oldtime-@usepoint, enddate = @enddate WHERE @uid = uid AND @aid = aid
2321 INSERT INTO monthbill (uid, aid, usepoint, monthcount, enddate, usedate) VALUES (@uid, @aid, @usepoint, @monthcount, @enddate, @now )
2322 SELECT @remain = @oldtime-@usepoint
2323 SELECT @strenddate = convert(varchar(12), @enddate, 102) + ' ' + convert(varchar(12), @enddate, 108)
2324 END
2325 ELSE
2326 BEGIN
2327 SELECT @error = 2
2328 SELECT @remain = @oldtime
2329 SELECT @enddate = @oldenddate
2330 SELECT @strenddate = convert(varchar(12), @enddate, 102) + ' ' + convert(varchar(12), @enddate, 108)
2331 END
2332 END
2333 ELSE SELECT @error = 1
2334 COMMIT TRAN
2335</procedure>
2336
2337<procedure name="exchangearea" connection="auth0" operate="replaceB">
2338 <parameter name="name" sql-type="varchar(64)" java-type="java.lang.String" in="true" out="false" />
2339 <parameter name="srcaid" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
2340 <parameter name="destaid" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
2341 <parameter name="time" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
2342 <parameter name="error" sql-type="integer" java-type="java.lang.Integer" in="false" out="true" />
2343 BEGIN TRAN
2344 DECLARE @uid integer
2345 DECLARE @srctime integer
2346 SELECT @error = 0
2347 SELECT @uid = id FROM account WHERE @name = name
2348 IF @@rowcount = 1
2349 BEGIN
2350 SELECT @srctime=time FROM point WHERE @uid = uid AND @srcaid = aid
2351 IF @@rowcount = 1 AND @srctime >= @time
2352 BEGIN
2353 UPDATE point SET time = time+@time WHERE @uid = uid AND @destaid = aid
2354 IF @@rowcount = 0 INSERT INTO point (uid,aid,time,zoneid,zonelocalid,accountstart,lastlogin,enddate) VALUES (@uid,@destaid,@time+36000, 0, 0, '', '', '' )
2355 UPDATE point SET time = time-@time WHERE @uid = uid AND @srcaid = aid
2356 END
2357 ELSE SELECT @error = 2
2358 END
2359 ELSE SELECT @error = 1
2360 COMMIT TRAN
2361</procedure>
2362
2363<procedure name="addmatrixrecord" connection="auth0" operate="replaceA">
2364 <parameter name="code" sql-type="varchar(12)" java-type="java.lang.String" in="true" out="false" />
2365 <parameter name="number" sql-type="integer" java-type="java.lang.Integer" not-null="true" />
2366 <parameter name="price" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
2367 <parameter name="rate" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
2368 <parameter name="expiredtime" sql-type="varchar(32)" java-type="java.lang.String" in="true" out="false" />
2369 <parameter name="creator" sql-type="varchar(64)" java-type="java.lang.String" in="true" out="false" />
2370 <parameter name="id" sql-type="integer" java-type="java.lang.Integer" in="true" out="true" />
2371 BEGIN TRAN
2372 SELECT @id = -1
2373 SELECT id FROM matrixrecord WHERE code = @code
2374 if (@@rowcount > 0)
2375 return -1
2376 SELECT @id = ISNULL(max(id), 0)+1 FROM matrixrecord HOLDLOCK
2377 INSERT INTO matrixrecord (id,code,number,price,rate,expiredtime,creator,creatime,auditor,auditime,status) VALUES(@id,@code,@number,@price,@rate,@expiredtime,@creator,getdate(),@creator,getdate(),0)
2378 COMMIT TRAN
2379</procedure>
2380
2381<procedure name="addnewmatrix" connection="auth0" operate="replaceA">
2382 <parameter name="recordid" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
2383 <parameter name="matrixid" sql-type="varchar(12)" java-type="java.lang.String" in="true" out="false" />
2384 <parameter name="matrix" sql-type="varbinary(80)" java-type="byte[]" in="true" out="false" />
2385 INSERT INTO matrixnew (recordid,id,matrix) VALUES (@recordid,@matrixid,@matrix)
2386
2387 IF (@@rowcount = 0)
2388 return -1
2389 else
2390 return 0
2391</procedure>
2392
2393<procedure name="bindmatrix" connection="auth0" operate="replaceB">
2394 <!-- -1.unknown error; -2.userid invalid; -3. coordinates invalid; -4. verifyCode error;
2395 -5.matrix id invalid; -6.already bind; -7.too many illegal try; -8. bind related method is in process;
2396 -9.bind mobile; -10.matrix not be audited. -11.matrix expired -->
2397 <parameter name="userid" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" cache="account_by_id;matrix_by_uid" />
2398 <parameter name="matrixid" sql-type="varchar(12)" java-type="java.lang.String" in="true" out="false" />
2399 <parameter name="ip" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
2400
2401 BEGIN TRAN
2402 DECLARE @newmatrix varbinary(80)
2403 DECLARE @recordid integer
2404 DECLARE @expiredtime datetime
2405 DECLARE @usertype integer
2406
2407 SELECT @usertype=usertype FROM account where id = @userid
2408 IF @@rowcount = 0
2409 BEGIN
2410 ROLLBACK TRAN
2411 RETURN -2 --userid invalid
2412 END
2413
2414 IF ((@usertype & 240) != 0) --0xF0--
2415 BEGIN
2416 ROLLBACK TRAN
2417 RETURN -6 --already bind, maybe other security --
2418 END
2419
2420 SELECT @newmatrix=n.matrix, @expiredtime=r.expiredtime, @recordid = n.recordid
2421 FROM matrixnew n, matrixrecord r
2422 WHERE n.id = @matrixid AND r.id = n.recordid AND r.status = 1
2423
2424 IF @@rowcount = 0
2425 BEGIN
2426 ROLLBACK TRAN
2427 RETURN -5 --invalid--
2428 END
2429 ELSE IF (getdate() > @expiredtime)
2430 BEGIN
2431 ROLLBACK TRAN
2432 RETURN -11
2433 END
2434
2435 INSERT INTO matrix VALUES( @userid, @recordid, @matrixid, @newmatrix )
2436 IF @@rowcount = 0
2437 BEGIN
2438 ROLLBACK TRAN
2439 RETURN -6 --already bind
2440 END
2441
2442 INSERT INTO matrixused (id,matrix,userid, begindate,ip) VALUES (@matrixid,@newmatrix,@userid,getdate(), @ip)
2443 UPDATE account SET usertype = (usertype & -241)|16 WHERE id = @userid --0xFFFFFF0F--
2444 DELETE FROM matrixnew where id = @matrixid
2445 COMMIT TRAN
2446 RETURN 0
2447
2448</procedure>
2449
2450<procedure name="unbindmatrix" connection="auth0" operate="replaceB">
2451 <!-- -1.unknown error; -2.userid invalid; -3. coordinates invalid; -4. verifyCode error; -5.matrix id wrong; -6.no bind; -7.too many illegal try; -8. bind related method is in process -->
2452 <parameter name="userid" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" cache="account_by_id;matrix_by_uid" />
2453 <parameter name="matrixid" sql-type="varchar(12)" java-type="java.lang.String" in="true" out="false" />
2454 <parameter name="ip" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
2455 BEGIN TRAN
2456
2457 DECLARE @matrixid_indb varchar(12)
2458 DECLARE @begindate datetime
2459 SELECT @begindate = u.begindate, @matrixid_indb = m.matrixid FROM matrix m,matrixused u WHERE m.uid = @userid AND m.matrixid = u.id AND u.canceldate = NULL
2460 IF @@rowcount = 1
2461 BEGIN
2462 IF @begindate > '2008-06-01' AND NOT @matrixid_indb = @matrixid
2463 BEGIN
2464 COMMIT TRAN
2465 return -5
2466 END
2467 END
2468 ELSE
2469 BEGIN
2470 COMMIT TRAN
2471 return -6
2472 END
2473
2474 UPDATE matrixused SET canceldate = getdate(), ip = @ip
2475 FROM matrix
2476 WHERE matrix.uid = @userid AND matrix.matrixid = matrixused.id AND matrixused.canceldate = NULL
2477
2478 IF @@rowcount = 1
2479 BEGIN
2480 DELETE FROM matrix WHERE uid = @userid
2481 UPDATE account SET usertype = (usertype & -241) WHERE id = @userid --0xFFFFFF0F change account status--
2482 END
2483
2484 COMMIT TRAN
2485 RETURN 0
2486</procedure>
2487
2488
2489<procedure name="rebindmatrix" connection="auth0" operate="replaceB">
2490<!-- -1.unknown error;
2491 * -2.userid invalid; -3. coordinates invalid; -4. verifyCodeOld error;
2492 * -5.old matrix id wrong; -6.no bind; -7.too many illegal try;
2493 * -8. bind related method is in process -9. verifyCodeNew error;
2494 * -10.matrix not be audited; -11.matrix expired; -12.new matrix id wrong -->
2495 <parameter name="userid" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" cache="account_by_id;matrix_by_uid" />
2496 <parameter name="matrixidold" sql-type="varchar(12)" java-type="java.lang.String" in="true" out="false" />
2497 <parameter name="matrixid" sql-type="varchar(12)" java-type="java.lang.String" in="true" out="false" />
2498 <parameter name="ip" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
2499
2500 DECLARE @status integer
2501 BEGIN TRAN
2502
2503 EXECUTE @status = unbindmatrix @userid, @matrixidold, @ip
2504 IF @status != 0
2505 BEGIN
2506 ROLLBACK TRAN
2507 return @status
2508 END
2509
2510 EXECUTE @status = bindmatrix @userid, @matrixid, @ip
2511 IF @status != 0
2512 ROLLBACK TRAN
2513 ELSE
2514 COMMIT TRAN
2515 RETURN @status
2516
2517</procedure>
2518
2519<procedure name="auditmatrix" connection="auth0" operate="replaceA">
2520 <parameter name="id" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
2521 <parameter name="auditor" sql-type="varchar(64)" java-type="java.lang.String" in="true" out="false" />
2522 <parameter name="status" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
2523 DECLARE @retcount integer
2524 SELECT @retcount = 0
2525 BEGIN TRAN
2526 UPDATE matrixrecord SET auditor = @auditor, auditime = getdate(), status = @status WHERE id = @id AND status = 0
2527 if @@rowcount =1
2528 SELECT @retcount = count(recordid) FROM matrixnew WHERE recordid = @id
2529 COMMIT TRAN
2530 return @retcount
2531</procedure>
2532
2533<procedure name="deletematrix" connection="auth0" operate="replaceA">
2534 <parameter name="id" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
2535 DECLARE @affectedcount integer
2536 SELECT @affectedcount = 0
2537 BEGIN TRAN
2538 DELETE FROM matrixrecord WHERE id = @id AND status = 0
2539 if @@rowcount = 1
2540 BEGIN
2541 DELETE FROM matrixnew WHERE recordid = @id
2542 SELECT @affectedcount = @@rowcount
2543 END
2544 COMMIT TRAN
2545 return @affectedcount
2546</procedure>
2547
2548<query name="matrixrecordids">
2549 <table name="matrixrecord" alias="c"/>
2550 <column name="id" column="c.id"/>
2551 <select name="all" condition="ORDER BY c.id DESC"/>
2552 <select name="new" condition="WHERE c.status = 0 ORDER BY c.id DESC"/>
2553 <select name="audited" condition="WHERE c.status = 1 ORDER BY c.id DESC"/>
2554</query>
2555
2556<query name="matrixrecordinfo">
2557 <table name="matrixrecord" alias="m"/>
2558 <column name="code" column="m.code"/>
2559 <column name="number" column="m.number"/>
2560 <column name="price" column="m.price"/>
2561 <column name="rate" column="m.rate"/>
2562 <column name="expiredtime" column="m.expiredtime"/>
2563 <column name="creator" column="m.creator"/>
2564 <column name="creatime" column="m.creatime"/>
2565 <column name="auditor" column="m.auditor"/>
2566 <column name="auditime" column="m.auditime"/>
2567 <column name="status" column="m.status"/>
2568 <select name="item" condition="WHERE m.id = ?"/>
2569 <select name="bycode" condition="WHERE m.code = ? and m.status = 1"/>
2570</query>
2571
2572<procedure name="changematrixstatus" connection="auth0" operate="replaceA">
2573 <parameter name="id" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
2574 <parameter name="tostatus" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
2575 <parameter name="retcount" sql-type="integer" java-type="java.lang.Integer" in="false" out="true" />
2576 BEGIN TRAN
2577 SELECT @retcount = -1
2578 if ((@tostatus = 0) or (@tostatus = 1) or (@tostatus = 2))
2579 BEGIN
2580 UPDATE matrixrecord set status = @tostatus WHERE id = @id
2581 SELECT @retcount = count(recordid) FROM matrixnew WHERE recordid = @id
2582 END
2583 COMMIT TRAN
2584</procedure>
2585
2586<procedure name="clearmatrix" connection="auth0" operate="replaceA">
2587 <parameter name="id" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
2588 <parameter name="retcount" sql-type="integer" java-type="java.lang.Integer" in="false" out="true" />
2589 BEGIN TRAN
2590 DECLARE @ostatus integer
2591
2592 SELECT @retcount = -1
2593
2594 SELECT @ostatus = status FROM matrixrecord WHERE id = @id
2595
2596 if (@ostatus = 2)
2597 BEGIN
2598 DELETE FROM matrixrecord WHERE id = @id
2599 DELETE FROM matrixnew WHERE recordid = @id
2600 SELECT @retcount = @@rowcount
2601 END
2602 COMMIT TRAN
2603</procedure>
2604
2605<procedure name="querymatrixnewbyid" connection="auth0" operate="replaceA">
2606 <parameter name="recordid" sql-type="integer" java-type="java.lang.Integer" in="false" out="true" />
2607 <parameter name="id" sql-type="varchar(12)" java-type="java.lang.String" in="true" out="true" />
2608 <parameter name="matrix" sql-type="varbinary(80)" java-type="byte[]" in="false" out="true" />
2609 <parameter name="ret" sql-type="integer" java-type="java.lang.Integer" in="false" out="true" />
2610 SELECT @ret = -1
2611 SELECT @recordid=recordid, @id=id, @matrix=matrix FROM matrixnew WHERE id = @id
2612 IF (@@rowcount = 1)
2613 BEGIN
2614 SELECT @ret =0
2615 END
2616</procedure>
2617
2618<procedure name="querymatrixusedcount" connection="auth0" operate="replaceA">
2619 <parameter name="id" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
2620 <parameter name="retcount" sql-type="integer" java-type="java.lang.Integer" in="false" out="true" />
2621 DECLARE @number integer
2622 DECLARE @newcount integer
2623 SELECT @retcount = -1
2624 SELECT @number=number FROM matrixrecord WHERE id = @id
2625 IF (@@rowcount = 1)
2626 BEGIN
2627 select @newcount=count(*) FROM matrixnew WHERE recordid = @id
2628 SELECT @retcount = @number - @newcount
2629 END
2630</procedure>
2631
2632<query name="downloadmatrix">
2633 <table name="matrixnew" alias="mn"/>
2634 <column name="recordid" column="mn.recordid"/>
2635 <column name="id" column="mn.id"/>
2636 <column name="matrix" column="mn.matrix"/>
2637 <select name="all" condition="WHERE mn.recordid = ? order by mn.id"/>
2638</query>
2639
2640<query name="querynewmatrix">
2641 <table name="matrixnew" alias="mn"/>
2642 <column name="recordid" column="mn.recordid"/>
2643 <column name="id" column="mn.id"/>
2644 <column name="matrix" column="mn.matrix"/>
2645 <select name="id" condition="WHERE mn.id = ? "/>
2646</query>
2647
2648<query name="querymatrixrecordid">
2649 <table name="matrixrecord" alias="m"/>
2650 <column name="id" column="m.id"/>
2651 <select name="id" condition="WHERE m.code = ?"/>
2652</query>
2653
2654<query name="querymatrixused">
2655 <table name="matrixused" alias="m"/>
2656 <column name="id" column="m.id"/>
2657 <column name="matrix" column="m.matrix"/>
2658 <column name="userid" column="m.userid"/>
2659 <column name="begindate" column="m.begindate"/>
2660 <column name="canceldate" column="m.canceldate"/>
2661 <column name="ip" column="m.ip"/>
2662 <select name="id" condition="WHERE m.id = ?"/>
2663 <select name="userid" condition="WHERE m.userid = ?"/>
2664</query>
2665
2666<procedure name="bindmobilekey" connection="auth0" operate="replaceB">
2667 <!-- -1.unknown error; -2.random overrange; -3.keyId overrange; -4.keyId expired; -5.already bind matrix; -6.already
2668 bind mobile; -7.verifycode wrong; -8.userid invalid -->
2669 <parameter name="userid" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" cache="account_by_id" />
2670 <parameter name="key" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
2671 <parameter name="algorithm" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" />
2672 BEGIN TRAN
2673 DECLARE @usertypeFromTable integer
2674
2675 SELECT @usertypeFromTable=usertype FROM account WHERE id = @userid
2676 IF (@@rowcount = 1)
2677 BEGIN
2678 IF ( (@usertypeFromTable & 240) > 0) --0xF0
2679 BEGIN
2680 ROLLBACK TRAN
2681 return -5 --already bind
2682 END
2683 END
2684
2685 UPDATE account SET usertype = (usertype & -241)|32 WHERE id = @userid --0xFFFFFF0F,0x20
2686 INSERT INTO mobilekey (uid, mobilekey, mobilealgorithm) VALUES (@userid,@key,@algorithm)
2687 COMMIT TRAN
2688</procedure>
2689
2690<procedure name="unbindmobilekey" connection="auth0" operate="replaceB">
2691 <!-- -1.unknown error; -2.userid invalid; -3. coordinates invalid; -4. verifyCode error; -5.matrix id wrong; -6.no bind; -7.too many illegal try; -8. bind related method is in process -->
2692 <parameter name="userid" sql-type="integer" java-type="java.lang.Integer" in="true" out="false" cache="account_by_id" />
2693 DECLARE @usertypeFromTable integer
2694
2695 SELECT @usertypeFromTable=usertype FROM account WHERE id = @userid
2696 IF (@@rowcount = 1)
2697 BEGIN
2698 IF ( (@usertypeFromTable & 240) != 32) --0xF0,0x20
2699 BEGIN
2700 return 0 --not bind mobilekey
2701 END
2702 BEGIN TRAN
2703 UPDATE account SET usertype = (usertype & -241) WHERE id = @userid --0xFFFFFF0F
2704 DELETE FROM mobilekey WHERE uid = @userid
2705 COMMIT TRAN
2706 END
2707</procedure>
2708<!--
2709<query name="querymobilekey">
2710 <table name="mobilekey" alias="m"/>
2711 <column name="userid" column="m.uid"/>
2712 <column name="mobilealgorithm" column="m.mobilealgorithm"/>
2713 <column name="mobilekey" column="m.mobilekey"/>
2714 <select name="userid" condition="WHERE m.uid = ?"/>
2715</query>
2716-->
2717<procedure name="querymobilekeybyid" connection="auth0" operate="replaceA">
2718 <parameter name="uid" sql-type="integer" java-type="java.lang.Integer" in="true" out="true" />
2719 <parameter name="mobilealgorithm" sql-type="integer" java-type="java.lang.Integer" in="false" out="true" />
2720 <parameter name="mobilekey" sql-type="integer" java-type="java.lang.Integer" in="false" out="true" />
2721 SELECT @uid=uid,@mobilealgorithm=mobilealgorithm, @mobilekey=mobilekey FROM mobilekey WHERE uid = @uid
2722 IF (@@rowcount != 1)
2723 BEGIN
2724 return -1
2725 END
2726</procedure>
2727
2728<procedure name="querymatrixbyid" connection="auth0" operate="replaceB" cache="matrix_by_uid" key="uid">
2729 <parameter name="uid" sql-type="integer" java-type="java.lang.Integer" in="true" out="true" />
2730 <parameter name="recordid" sql-type="integer" java-type="java.lang.Integer" in="false" out="true" />
2731 <parameter name="matrixid" sql-type="char(12)" java-type="java.lang.String" in="false" out="true" />
2732 <parameter name="matrix" sql-type="binary(80)" java-type="byte[]" in="false" out="true" />
2733 SELECT @uid=uid,@recordid=recordid, @matrixid=matrixid, @matrix=matrix FROM matrix WHERE uid = @uid
2734 IF (@@rowcount != 1)
2735 BEGIN
2736 return -1
2737 END
2738</procedure>
2739
2740
2741<query name="getUserPhone">
2742 <table name="phone" alias="p" />
2743 <column name="phone" column="p.phone" />
2744 <select name="byUid" condition="WHERE p.uid = ?" />
2745 <select name="byName" condition="WHERE p.uid = (SELECT id FROM account WHERE name=?)" />
2746</query>
2747
2748<query name="getPhoneUser" cachevalue="multi">
2749 <table name="phone" alias="p" />
2750 <column name="uid" column="p.uid" />
2751 <column name="phone" compute="rtrim(p.phone)" java-type="String" />
2752 <select name="byPhone" condition="WHERE p.phone = ?" cache="phoneuser_by_phone" key="phone"/>
2753</query>
2754
2755<procedure name="bindPhone" connection="auth0" operate="replaceB">
2756 <parameter name="name" sql-type="varchar(64)" java-type="java.lang.String" in="true" out="false" cache="account_by_name" />
2757 <parameter name="phone" sql-type="char(16)" java-type="java.lang.String" in="true" out="false" cache="phoneuser_by_phone"/>
2758 <parameter name="error" sql-type="integer" java-type="java.lang.Integer" in="false" out="true" />
2759 BEGIN TRAN
2760 SELECT @error = 0
2761 DECLARE @uid integer
2762 DECLARE @usertype_old integer
2763 SELECT @uid = id, @usertype_old = usertype FROM account WHERE name = @name
2764 IF @@rowcount = 0
2765 SELECT @error = 4
2766 ELSE IF (@usertype_old & 240) > 0 AND NOT (@usertype_old & 240) = 64 --0xF0,0x40
2767 SELECT @error = 5
2768 ELSE
2769 BEGIN
2770 IF 5 > (SELECT count(*) FROM phone WHERE uid = @uid)
2771 BEGIN
2772 IF 5 > (SELECT count(*) FROM phone WHERE phone = @phone)
2773 BEGIN
2774 IF EXISTS (SELECT * FROM phone WHERE uid = @uid and phone = @phone)
2775 SELECT @error = 3
2776 ELSE
2777 BEGIN
2778 INSERT INTO phone VALUES( @uid, @phone )
2779 UPDATE account SET usertype = (usertype&-241)|64 WHERE id = @uid --0xFFFFFF0F,0x40
2780 END
2781 END
2782 ELSE
2783 SELECt @error = 2
2784 END
2785 ELSE
2786 SELECT @error = 1
2787 END
2788 COMMIT TRAN
2789</procedure>
2790
2791<procedure name="unbindPhone" connection="auth0" operate="replaceB">
2792 <parameter name="name" sql-type="varchar(64)" java-type="java.lang.String" in="true" out="false" cache="account_by_name" />
2793 <parameter name="phone" sql-type="char(16)" java-type="java.lang.String" in="true" out="false" cache="phoneuser_by_phone"/>
2794 <parameter name="error" sql-type="integer" java-type="java.lang.Integer" in="false" out="true" />
2795 BEGIN TRAN
2796 DECLARE @uid integer
2797 DECLARE @usertype_old integer
2798 SELECT @uid = id, @usertype_old = usertype FROM account where name = @name
2799 IF @@rowcount = 0
2800 SELECT @error = 2
2801 ELSE IF NOT (@usertype_old & 240) = 64 --0xF0,0x40
2802 SELECT @error = 3
2803 ELSE
2804 BEGIN
2805 DELETE phone FROM phone WHERE uid = @uid AND phone = @phone
2806 IF @@rowcount = 1
2807 SELECT @error = 0
2808 ELSE
2809 SELECT @error = 1
2810 IF NOT EXISTS (SELECT * FROM phone WHERE uid = @uid)
2811 UPDATE account SET usertype = (usertype&-241) WHERE name = @name --0xFFFFFF0F
2812 END
2813 COMMIT TRAN
2814</procedure>
2815
2816<procedure name="clearUserPhone" connection="auth0" operator="replaceB">
2817 <parameter name="name" sql-type="varchar(64)" java-type="java.lang.String" in="true" out="false" cache="account_by_name" />
2818 <parameter name="ret" sql-type="integer" java-type="java.lang.Integer" in="false" out="true" />
2819 BEGIN TRAN
2820 DECLARE @uid integer
2821 DECLARE @usertype_old integer
2822 SELECT @uid = id, @usertype_old = usertype FROM account WHERE name = @name
2823 IF @@rowcount = 0
2824 SELECT @ret = -2
2825 ELSE IF NOT (@usertype_old & 240) = 64 --0xF0,0x40
2826 SELECT @ret = -3
2827 ELSE
2828 BEGIN
2829 UPDATE account SET usertype = (usertype&-241) WHERE id = @uid --0xFFFFFF0F
2830 DELETE FROM phone WHERE uid = @uid
2831 SELECT @ret = @@rowcount
2832 END
2833 COMMIT TRAN
2834</procedure>
2835
2836<!-- ´æ´¢¹ý³Ì£º»ñÈ¡Óû§ÃûÃÜÂë -->
2837<procedure name="acquireuserpasswd" connection="auth0" operate="replaceA">
2838 <parameter name="name" sql-type="varchar(64)" java-type="java.lang.String" in="true" out="true" />
2839 <parameter name="id" sql-type="integer" java-type="java.lang.Integer" in="false" out="true" />
2840 <parameter name="passwd" sql-type="binary(16)" java-type="byte[]" in="false" out="true" />
2841 <parameter name="creatime" sql-type="datetime" java-type="java.util.Date" in="false" out="true" />
2842 <parameter name="usertype" sql-type="integer" java-type="java.lang.Integer" in="false" out="true" />
2843 SELECT @id = id, @name = name, @passwd = passwd, @creatime = creatime, @usertype = usertype FROM account WHERE name = @name
2844 if @@rowcount = 0
2845 return -1;
2846 return 0;
2847</procedure>
2848
2849<!-- ´æ´¢¹ý³Ì £º¸ù¾ÃÓû§Ãû²éѯIDºÃ¶þ¼¶ÃÜÂë. -->
2850<procedure name="acquireuserpasswd2" connection="auth0" operate="replaceA">
2851 <parameter name="name" sql-type="varchar(64)" java-type="java.lang.String" in="true" out="false" />
2852 <parameter name="id" sql-type="integer" java-type="java.lang.Integer" in="false" out="true" />
2853 <parameter name="passwd2" sql-type="binary(16)" java-type="byte[]" in="false" out="true" />
2854 SELECT @id = a.id, @passwd2 = u.passwd2 FROM users u,account a WHERE a.name = @name AND a.id=u.id
2855 IF @@rowcount = 0
2856 return -1
2857 return 0
2858</procedure>
2859
2860<!-- ´æ´¢¹ý³Ì£º¸ù¾ÃÓû§Ãû²éѯID. -->
2861<procedure name="getuseridbyname" connection="auth0" operate="replaceA">
2862 <parameter name="name" sql-type="varchar(64)" java-type="java.lang.String" in="true" out="false" />
2863 <parameter name="uid" sql-type="integer" java-type="java.lang.Integer" in="false" out="true" />
2864 SELECT @uid = id FROM account WHERE name = @name
2865 IF @@rowcount = 0
2866 return -1
2867 return 0
2868</procedure>
2869
2870<!-- ´æ´¢¹ý³Ì£º¸ù¾ÃÓû§Ãû²éѯ. -->
2871<procedure name="getuserinfobyname" connection="auth0" operate="replaceA">
2872 <parameter name="name" sql-type="varchar(32)" java-type="java.lang.String" in="true" out="false" />
2873 <parameter name="id" sql-type="integer" java-type="java.lang.Integer" in="false" out="true" />
2874 <parameter name="prompt" sql-type="varchar(32)" java-type="java.lang.String" in="false" out="true" />
2875 <parameter name="answer" sql-type="varchar(32)" java-type="java.lang.String" in="false" out="true" />
2876 <parameter name="truename" sql-type="varchar(32)" java-type="java.lang.String" in="false" out="true" />
2877 <parameter name="idnumber" sql-type="varchar(32)" java-type="java.lang.String" in="false" out="true" />
2878 <parameter name="email" sql-type="varchar(64)" java-type="java.lang.String" in="false" out="true" />
2879 <parameter name="mobilenumber" sql-type="varchar(32)" java-type="java.lang.String" in="false" out="true" />
2880 <parameter name="province" sql-type="varchar(32)" java-type="java.lang.String" in="false" out="true" />
2881 <parameter name="city" sql-type="varchar(32)" java-type="java.lang.String" in="false" out="true" />
2882 <parameter name="phonenumber" sql-type="varchar(32)" java-type="java.lang.String" in="false" out="true" />
2883 <parameter name="address" sql-type="varchar(64)" java-type="java.lang.String" in="false" out="true" />
2884 <parameter name="postalcode" sql-type="varchar(8)" java-type="java.lang.String" in="false" out="true" />
2885 <parameter name="gender" sql-type="integer" java-type="java.lang.Integer" in="false" out="true" />
2886 <parameter name="birthday" sql-type="varchar(32)" java-type="java.lang.String" in="false" out="true" />
2887 <parameter name="creatime" sql-type="varchar(32)" java-type="java.lang.String" in="false" out="true" />
2888 <parameter name="qq" sql-type="varchar(32)" java-type="java.lang.String" in="false" out="true" />
2889 DECLARE @birthday_tmp datetime
2890 DECLARE @creatime_tmp datetime
2891 SELECT @id = a.id, @prompt = u.prompt, @answer = u.answer, @truename = u.truename, @idnumber = u.idnumber, @email = u.email, @mobilenumber = u.mobilenumber, @province = u.province, @city = u.city, @phonenumber = u.phonenumber, @address = u.address, @postalcode = u.postalcode, @gender = u.gender, @birthday_tmp = u.birthday, @creatime_tmp = a.creatime, @qq = u.qq FROM users u, account a WHERE a.name = @name AND a.id = u.id
2892 IF @@rowcount = 0
2893 return -1
2894 SELECT @birthday = convert(varchar(32), @birthday_tmp, 120)
2895 SELECT @creatime = convert(varchar(32), @creatime_tmp, 121)
2896 return 0
2897</procedure>
2898
2899</application>