API tools faq
paste
Guest User

Untitled

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