API tools faq
paste
Guest User

Untitled

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