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