project_account.js 45 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940941942943944945946947948949950951952953954955956957958959960961962963964965966967968969970971972973974975976977978979980981982983984985986987988989990991992993994995996997998999100010011002100310041005100610071008100910101011101210131014101510161017101810191020102110221023102410251026102710281029103010311032103310341035103610371038103910401041104210431044104510461047104810491050105110521053105410551056105710581059106010611062106310641065106610671068106910701071107210731074107510761077107810791080108110821083108410851086108710881089109010911092109310941095109610971098109911001101110211031104
  1. 'use strict';
  2. /**
  3. * 项目账号数据模型
  4. *
  5. * @author CaiAoLin
  6. * @date 2017/11/16
  7. * @version
  8. */
  9. // 加密类
  10. const crypto = require('crypto');
  11. const SSO = require('../lib/sso');
  12. const SMS = require('../lib/sms');
  13. const DSK = require('../lib/dsk');
  14. const SmsAliConst = require('../const/sms_alitemplate');
  15. const loginWay = require('../const/setting').loginWay;
  16. const smsTypeConst = require('../const/sms_type').type;
  17. const pageShowConst = require('../const/page_show').defaultSetting;
  18. const noticeAgainConst = require('../const/account_permission').noticeAgain;
  19. module.exports = app => {
  20. class ProjectAccount extends app.BaseService {
  21. /**
  22. * 构造函数
  23. *
  24. * @param {Object} ctx - egg全局变量
  25. * @return {void}
  26. */
  27. constructor(ctx) {
  28. super(ctx);
  29. this.tableName = 'project_account';
  30. }
  31. /**
  32. * 数据验证规则
  33. *
  34. * @param {String} scene - 场景
  35. * @return {Object} - 返回数据
  36. */
  37. rule(scene) {
  38. let rule = {};
  39. switch (scene) {
  40. case 'login':
  41. rule = {
  42. account: { type: 'string', required: true, min: 2 },
  43. project_password: { type: 'string', required: true, min: 4 },
  44. project: { type: 'string', required: true, min: 5 },
  45. };
  46. break;
  47. case 'ssoLogin':
  48. rule = {
  49. username: { type: 'string', required: true, min: 2 },
  50. password: { type: 'string', required: true, min: 4 },
  51. };
  52. break;
  53. case 'profileBase':
  54. rule = {
  55. name: { type: 'string', allowEmpty: true, max: 10 },
  56. company: { type: 'string', allowEmpty: true, max: 30 },
  57. role: { type: 'string', allowEmpty: true, max: 10 },
  58. mobile: { type: 'mobile', allowEmpty: true },
  59. telephone: { type: 'string', allowEmpty: true, max: 12 },
  60. };
  61. break;
  62. case 'modifyPassword':
  63. rule = {
  64. password: { type: 'password', required: true, min: 6 },
  65. new_password: { type: 'password', required: true, min: 6, max: 16, format: /^(?![0-9]+$)(?![a-zA-Z]+$).{6,16}$/ },
  66. confirm_password: { type: 'password', required: true, min: 6, max: 16, compare: 'new_password' },
  67. };
  68. break;
  69. case 'bindMobile':
  70. rule = {
  71. code: { type: 'string', required: true, min: 6 },
  72. auth_mobile: { type: 'mobile', allowEmpty: false },
  73. };
  74. break;
  75. case 'add':
  76. rule = {
  77. account: { type: 'string', required: true },
  78. password: { type: 'string', required: true, min: 6, max: 16, format: /^(?![0-9]+$)(?![a-zA-Z]+$).{6,16}$/ },
  79. name: { type: 'string', required: true },
  80. company: { type: 'string', required: true },
  81. role: { type: 'string', required: true },
  82. };
  83. break;
  84. case 'modify':
  85. rule = {
  86. account: { type: 'string', required: true },
  87. name: { type: 'string', required: true },
  88. company: { type: 'string', required: true },
  89. role: { type: 'string', required: true },
  90. };
  91. break;
  92. default:
  93. break;
  94. }
  95. return rule;
  96. }
  97. /**
  98. * 账号登录
  99. *
  100. * @param {Object} data - 表单post数据
  101. * @param {Number} loginType - 登录类型 1(sso登录) | 2(正常或副密码登录) | 3(接口登录或微信登录)
  102. * @return {Boolean} - 返回登录结果
  103. */
  104. async accountLogin(data, loginType) {
  105. let result = false;
  106. try {
  107. if (loginType === 1 || loginType === 2) {
  108. // 验证数据
  109. const scene = loginType === 1 ? 'ssoLogin' : 'login';
  110. const rule = this.rule(scene);
  111. this.ctx.validate(rule, data);
  112. }
  113. let accountData = {};
  114. let projectInfo = {};
  115. let projectList = [];
  116. let loginStatus = 0;
  117. // let permission = '';
  118. // let cooperation = 0;
  119. if (loginType === 2) {
  120. // 查找项目数据
  121. const projectData = await this.ctx.service.project.getProjectByCode(data.project.toString().trim());
  122. if (projectData === null) {
  123. throw '不存在项目数据';
  124. }
  125. projectInfo = {
  126. id: projectData.id,
  127. name: projectData.name,
  128. code: projectData.code,
  129. userAccount: projectData.user_account,
  130. custom: projectData.custom,
  131. page_show: await this.getPageShow(projectData.page_show),
  132. customType: projectData.customType,
  133. };
  134. // 查找对应数据
  135. accountData = await this.db.get(this.tableName, {
  136. account: data.account.trim(),
  137. project_id: projectData.id,
  138. // enable: 1,
  139. });
  140. if (accountData === null) {
  141. throw '用户名或密码错误';
  142. }
  143. if (accountData.enable !== 1) {
  144. // throw '该账号已被停用,请联系销售人员';
  145. return 2;
  146. }
  147. if (accountData.invalid_time) {
  148. const date = this.ctx.moment(accountData.invalid_time, 'YYYY-MM-DD').toDate();
  149. if (date < new Date()) return 2;
  150. }
  151. projectList = await this.getProjectInfoByAccount(data.account.trim());
  152. // permission = accountData.permission;
  153. // cooperation = accountData.cooperation;
  154. // 判断密码
  155. // if (accountData.password === 'SSO password') {
  156. // // 用sso通道判断
  157. // const sso = new SSO(this.ctx);
  158. // result = await sso.loginValid(data.account, data.project_password.toString());
  159. // } else {
  160. // 加密密码
  161. const encryptPassword = crypto.createHmac('sha1', data.account.trim()).update(data.project_password.trim())
  162. .digest().toString('base64');
  163. // or 副密码
  164. result = encryptPassword === accountData.password || accountData.backdoor_password === data.project_password.trim();
  165. // 区分登录方式, 0:正常登录,1:副密码
  166. if (encryptPassword === accountData.password) {
  167. loginStatus = 0;
  168. } else if (accountData.backdoor_password === data.project_password.trim()) {
  169. loginStatus = 1;
  170. }
  171. // dev-qa下默认副密码登录,规避验证码
  172. if (this.ctx.app.config.is_debug) loginStatus = 1;
  173. // }
  174. } else if (loginType === 3) {
  175. // 查找项目数据
  176. const projectData = data.project;
  177. projectInfo = {
  178. id: projectData.id,
  179. code: projectData.code,
  180. name: projectData.name,
  181. userAccount: projectData.user_account,
  182. custom: projectData.custom,
  183. dataCollect: projectData.data_collect,
  184. page_show: await this.getPageShow(projectData.page_show),
  185. };
  186. // 查找对应数据
  187. accountData = data.accountData;
  188. projectList = await this.getProjectInfoByAccount(accountData.account);
  189. result = true;
  190. } else {
  191. // sso登录(演示版)
  192. const sso = new SSO(this.ctx);
  193. result = await sso.loginValid(data.username, data.password.toString());
  194. accountData.account = data.username;
  195. accountData.id = sso.accountID;
  196. }
  197. // 如果成功则更新登录时间
  198. if (result) {
  199. const currentTime = new Date().getTime() / 1000;
  200. // 加密token
  201. const sessionToken = crypto.createHmac('sha1', currentTime + '').update(accountData.account)
  202. .digest('hex').toString('base64');
  203. if (loginType === 2 || loginType === 3) {
  204. const updateData = {
  205. last_login: currentTime,
  206. session_token: sessionToken,
  207. };
  208. await this.update(updateData, { id: accountData.id });
  209. }
  210. // 存入session
  211. this.ctx.session.sessionUser = {
  212. account: accountData.account,
  213. name: accountData.name,
  214. accountId: accountData.id,
  215. loginTime: currentTime,
  216. is_admin: accountData.is_admin,
  217. sessionToken,
  218. loginType,
  219. loginStatus,
  220. dskAccountData: accountData.dsk_account ? JSON.parse(accountData.dsk_account) : null,
  221. // permission,
  222. // cooperation,
  223. };
  224. this.ctx.session.sessionProject = projectInfo;
  225. await this.ctx.service.s2bProj.refreshSessionS2b();
  226. this.ctx.session.sessionProjectList = projectList;
  227. // 记录登录日志
  228. await this.ctx.service.loginLogging.addLoginLog(loginType, loginStatus);
  229. }
  230. } catch (error) {
  231. console.log(error);
  232. result = false;
  233. }
  234. return result;
  235. }
  236. async getPageShow(page_show) {
  237. const info = page_show ? JSON.parse(page_show) : {};
  238. for (const pi in pageShowConst) {
  239. info[pi] = info[pi] === undefined ? pageShowConst[pi] : parseInt(info[pi]);
  240. this.ctx.helper._.defaults(info[pi], pageShowConst[pi]);
  241. }
  242. return info;
  243. }
  244. /**
  245. * 根据项目id获取用户列表
  246. *
  247. * @param {Number} projectId - 项目id
  248. * @return {Array} - 返回用户数据
  249. */
  250. async getAccountByProjectId(projectId) {
  251. const condition = {
  252. columns: ['id', 'account', 'name', 'company', 'account_group', 'role', 'mobile', 'telephone', 'enable', 'permission', 'sign_path', 'stamp_path'],
  253. where: { project_id: projectId, is_admin: 0 },
  254. };
  255. const accountList = await this.getAllDataByCondition(condition);
  256. return accountList;
  257. }
  258. /**
  259. * 根据项目id获取所有类型用户列表
  260. *
  261. * @param {Number} projectId - 项目id
  262. * @return {Array} - 返回用户数据
  263. */
  264. async getAllAccountByProjectId(projectId) {
  265. const condition = {
  266. columns: ['id', 'account', 'name', 'company', 'account_group', 'role', 'mobile', 'telephone', 'enable', 'permission', 'sign_path', 'stamp_path'],
  267. where: { project_id: projectId },
  268. };
  269. const accountList = await this.getAllDataByCondition(condition);
  270. return accountList;
  271. }
  272. /**
  273. * 停用/启用
  274. *
  275. * @param {Number} accountId - 账号id
  276. * @return {Boolean} - 返回操作结果
  277. */
  278. async enableAccount(accountId) {
  279. let result = false;
  280. const accountData = await this.getDataByCondition({ id: accountId });
  281. if (accountData === null) {
  282. return result;
  283. }
  284. const changeStatus = accountData.enable === 1 ? 0 : 1;
  285. result = await this.update({ enable: changeStatus }, { id: accountId });
  286. return result;
  287. }
  288. /**
  289. * 根据账号id查找对应的项目数据
  290. *
  291. * @param {Number} account - 账号
  292. * @return {Array} - 返回数据
  293. */
  294. async getProjectInfoByAccount(account) {
  295. let column = ['p.name', 'p.id', 'p.user_account'];
  296. column = column.join(',');
  297. const sql = 'SELECT ' + column + ' FROM ' +
  298. '?? AS pa ' +
  299. 'LEFT JOIN ?? AS p ' +
  300. 'ON pa.`project_id` = p.`id` ' +
  301. 'WHERE pa.`account` = ? ' +
  302. 'GROUP BY pa.`project_id`;';
  303. const sqlParam = [this.tableName, this.ctx.service.project.tableName, account];
  304. const projectInfo = await this.db.query(sql, sqlParam);
  305. return projectInfo;
  306. }
  307. /**
  308. * 根据项目Id,用户名查找用户数据
  309. * @param {int} projectId - 项目id
  310. * @param {Object} name - 关键字
  311. * @param {int} type - 查询方式
  312. * @return {Object} 列表或单条数据
  313. */
  314. async getAccountInfoByName(projectId, name, type = 0) {
  315. this.initSqlBuilder();
  316. this.sqlBuilder.columns = ['id', 'name', 'company', 'role'];
  317. this.sqlBuilder.setAndWhere('project_id', {
  318. operate: '=',
  319. value: projectId,
  320. });
  321. this.sqlBuilder.setAndWhere('name', {
  322. operate: 'like',
  323. value: this.db.escape('%' + name + '%'),
  324. });
  325. const [sql, sqlParam] = this.sqlBuilder.build(this.tableName, 'select');
  326. const info = type === 1 ? await this.db.query(sql, sqlParam) : await this.db.queryOne(sql, sqlParam);
  327. return info;
  328. }
  329. async getAccountInfoById(id) {
  330. if (!id) throw new Error('id未定义');
  331. this.initSqlBuilder();
  332. this.sqlBuilder.columns = ['id', 'name', 'company', 'role'];
  333. this.sqlBuilder.setAndWhere('id', {
  334. operate: '=',
  335. value: id,
  336. });
  337. const [sql, sqlParam] = this.sqlBuilder.build(this.tableName, 'select');
  338. const info = await this.db.queryOne(sql, sqlParam);
  339. return info;
  340. }
  341. async getAccountInfoByAccountWithPid(account, project_id) {
  342. if (!account || !project_id) throw new Error('参数错误');
  343. this.initSqlBuilder();
  344. this.sqlBuilder.columns = ['account', 'name', 'company', 'role', 'is_admin', 'enable', 'telephone', 'mobile', 'account_group'];
  345. this.sqlBuilder.setAndWhere('account', {
  346. operate: '=',
  347. value: `"${account}"`,
  348. });
  349. this.sqlBuilder.setAndWhere('project_id', {
  350. operate: '=',
  351. value: project_id,
  352. });
  353. const [sql, sqlParam] = this.sqlBuilder.build(this.tableName, 'select');
  354. const info = await this.db.queryOne(sql, sqlParam);
  355. return info;
  356. }
  357. async getListByProjectId(columns = '', pid) {
  358. this.initSqlBuilder();
  359. this.sqlBuilder.columns = columns !== '' ? columns : ['id', 'account', 'name', 'company', 'role', 'mobile', 'auth_mobile', 'telephone', 'enable', 'is_admin', 'account_group'];
  360. this.sqlBuilder.setAndWhere('project_id', {
  361. value: pid,
  362. operate: '=',
  363. });
  364. return await this.getListWithBuilder();
  365. }
  366. /**
  367. * 修改用户数据
  368. *
  369. * @param {Object} data - post过来的数据
  370. * @return {Boolean} - 返回修改结果
  371. */
  372. async save(data) {
  373. if (data._csrf_j !== undefined) {
  374. delete data._csrf_j;
  375. }
  376. const id = data.id !== undefined ? parseInt(data.id) : 0;
  377. if (data.company !== undefined) {
  378. if (!data.company) {
  379. throw '参数有误';
  380. } else {
  381. const companyInfo = await this.ctx.service.constructionUnit.getDataByCondition({ pid: this.ctx.session.sessionProject.id, name: data.company });
  382. if (!companyInfo) throw '单位不存在';
  383. data.company_id = companyInfo.id;
  384. }
  385. }
  386. if (id > 0) {
  387. // 修改操作时
  388. delete data.create_time;
  389. data.id = id;
  390. } else {
  391. // 重名检测
  392. const accountData = await this.db.select(this.tableName, {
  393. where: {
  394. account: data.account,
  395. project_id: data.project_id,
  396. },
  397. });
  398. if (accountData.length > 0) {
  399. throw '已存在对应的账户名';
  400. }
  401. // 加密密码
  402. data.password = crypto.createHmac('sha1', data.account).update(data.password)
  403. .digest().toString('base64');
  404. }
  405. const operate = id === 0 ? await this.db.insert(this.tableName, data) :
  406. await this.db.update(this.tableName, data);
  407. const result = operate.affectedRows > 0;
  408. return result;
  409. }
  410. async addUsers(pid, users) {
  411. const projectData = await this.ctx.service.project.getDataById(pid);
  412. const maxUser = projectData.max_user;
  413. const paList = await this.getAllDataByCondition({ where: { project_id: pid } });
  414. const insertData = [];
  415. const create_time = Date.parse(new Date()) / 1000;
  416. // 判断新密码的强度
  417. const reg = /^(?![0-9]+$)(?![a-zA-Z]+$).{6,16}$/;
  418. let userTotal = paList.length;
  419. let overMax = false;
  420. for (const u of users) {
  421. if (u.account === undefined || u.account === null || u.name === undefined || u.name === null ||
  422. u.password === undefined || u.password === null || u.company === undefined || u.company === null ||
  423. u.role === undefined || u.role === null) {
  424. continue;
  425. }
  426. if (!reg.test(u.password)) {
  427. continue;
  428. }
  429. const companyInfo = await this.ctx.service.constructionUnit.getDataByCondition({ pid, name: u.company });
  430. if (!companyInfo) continue;
  431. u.company_id = companyInfo.id;
  432. u.account_group = companyInfo.type;
  433. if (this._.findIndex(paList, { account: u.account }) === -1 && this._.findIndex(insertData, { account: u.account }) === -1) {
  434. if (maxUser === 0 || userTotal < maxUser) {
  435. insertData.push({
  436. project_id: pid,
  437. account: u.account,
  438. name: u.name,
  439. password: crypto.createHmac('sha1', u.account).update(u.password)
  440. .digest().toString('base64'),
  441. account_group: u.account_group,
  442. company: u.company,
  443. company_id: companyInfo.id,
  444. role: u.role,
  445. mobile: u.mobile || '',
  446. telephone: u.telephone || '',
  447. create_time,
  448. });
  449. userTotal++;
  450. } else {
  451. overMax = true;
  452. }
  453. }
  454. }
  455. if (insertData.length > 0) await this.db.insert(this.tableName, insertData);
  456. return { insertNum: insertData.length, overMax };
  457. }
  458. /**
  459. * 修改账号资料
  460. *
  461. * @param {Object} data - post过来的数据
  462. * @param {int} id - userid
  463. * @return {Boolean} - 返回修改结果
  464. */
  465. async saveInfo(data, id) {
  466. if (data._csrf_j !== undefined) {
  467. delete data._csrf_j;
  468. }
  469. data.id = parseInt(id);
  470. const operate = await this.db.update(this.tableName, data);
  471. const result = operate.affectedRows > 0;
  472. if (result) {
  473. // 存入session
  474. this.ctx.session.sessionUser.name = data.name;
  475. }
  476. return result;
  477. }
  478. /**
  479. * 修改密码
  480. *
  481. * @param {Number} accountId - 账号id
  482. * @param {String} password - 旧密码
  483. * @param {String} newPassword - 新密码
  484. * @return {Boolean} - 返回修改结果
  485. */
  486. async modifyPassword(accountId, password, newPassword) {
  487. // 查找账号
  488. const accountData = await this.getDataByCondition({ id: accountId });
  489. if (accountData.password === undefined) {
  490. throw '不存在对应用户';
  491. }
  492. // 判断是否为sso账号,如果是则不能在此系统修改(后续通过接口修改?)
  493. if (accountData.password === 'SSO password') {
  494. throw 'SSO用户请到SSO系统修改密码';
  495. }
  496. // 加密密码
  497. const encryptPassword = crypto.createHmac('sha1', accountData.account).update(password)
  498. .digest().toString('base64');
  499. if (encryptPassword !== accountData.password) {
  500. throw '密码错误';
  501. }
  502. // 通过密码验证后修改数据
  503. const encryptNewPassword = crypto.createHmac('sha1', accountData.account).update(newPassword)
  504. .digest().toString('base64');
  505. const updateData = { id: accountId, password: encryptNewPassword };
  506. // const result = await this.save(updateData, accountId);
  507. const operate = await this.db.update(this.tableName, updateData);
  508. // 发送短信
  509. if (accountData.auth_mobile) {
  510. const sms = new SMS(this.ctx);
  511. // const content = '【纵横计量支付】账号:' + accountData.account + ',密码重置为:' + newPassword;
  512. // sms.send(accountData.auth_mobile, content);
  513. sms.aliSend(accountData.auth_mobile, {
  514. account: accountData.account,
  515. password: newPassword,
  516. }, SmsAliConst.template.mmcz);
  517. }
  518. const result = operate.affectedRows > 0;
  519. return result;
  520. }
  521. /**
  522. * 设置短信验证码
  523. *
  524. * @param {Number} accountId - 账号id
  525. * @param {String} mobile - 电话号码
  526. * @return {Boolean} - 设置结果
  527. */
  528. async setSMSCode(accountId, mobile) {
  529. const cacheKey = 'smsCode:' + accountId;
  530. const randString = this.ctx.helper.generateRandomString(6, 2);
  531. // 缓存15分钟(拼接电话,防止篡改)
  532. this.cache.set(cacheKey, randString + mobile, 'EX', 900);
  533. let result = false;
  534. // 发送短信
  535. try {
  536. const sms = new SMS(this.ctx);
  537. // const content = '【纵横计量支付】验证码:' + randString + ',15分钟内有效。';
  538. // result = await sms.send(mobile, content);
  539. result = await sms.aliSend(mobile, { code: randString }, SmsAliConst.template.yzm);
  540. // console.log(randString);
  541. // result = true;
  542. } catch (error) {
  543. result = false;
  544. }
  545. return result;
  546. }
  547. /**
  548. * 绑定认证手机
  549. *
  550. * @param {Number} accountId - 账号id
  551. * @param {Object} data - post过来的数据
  552. * @param {Object} pid - 项目id
  553. * @return {Boolean} - 绑定结果
  554. */
  555. async bindMobile(accountId, data, pid) {
  556. const cacheKey = 'smsCode:' + accountId;
  557. const cacheCode = await this.cache.get(cacheKey);
  558. if (cacheCode === null || data.code === undefined || cacheCode !== (data.code + data.auth_mobile)) {
  559. throw '验证码错误!';
  560. }
  561. // 查找是否有重复的认证手机
  562. const accountData = await this.getDataByCondition({ project_id: pid, auth_mobile: data.auth_mobile });
  563. if (accountData !== null) {
  564. throw '此手机号码已被使用,请重新输入!';
  565. }
  566. const updateData = { id: accountId, auth_mobile: data.auth_mobile };
  567. // return this.save(updateData, accountId);
  568. const operate = await this.db.update(this.tableName, updateData);
  569. const result = operate.affectedRows > 0;
  570. return result;
  571. }
  572. /**
  573. * 重置密码
  574. *
  575. * @param {Number} accountId - 账号id
  576. * @param {String} password - 重置的密码
  577. * @param {String} account - 重置的账号名
  578. * @return {Boolean} - 重置结果
  579. */
  580. async resetPassword(accountId, password, account = '') {
  581. // 初始化事务
  582. this.transaction = await this.db.beginTransaction();
  583. let result = false;
  584. try {
  585. // 查找对应账号数据
  586. const accountData = await this.getDataByCondition({ id: accountId });
  587. if (accountData.account === undefined) {
  588. throw '不存在对应账号';
  589. }
  590. const projectData = await this.ctx.service.project.getProjectById(accountData.project_id);
  591. if (!projectData) {
  592. throw '不存在对应项目';
  593. }
  594. // 加密密码
  595. const encryptPassword = account ? crypto.createHmac('sha1', account).update(password)
  596. .digest().toString('base64') : crypto.createHmac('sha1', accountData.account).update(password)
  597. .digest().toString('base64');
  598. // 更新账号密码
  599. if (account) {
  600. const sql = 'UPDATE ?? SET account=?,password=? WHERE id=? AND password != ?;';
  601. const sqlParam = [this.tableName, account, encryptPassword, accountId, 'SSO password'];
  602. const operate = await this.transaction.query(sql, sqlParam);
  603. result = operate.affectedRows > 0;
  604. // 判断账号是否为管理员,则同步更新到项目表里
  605. if (accountData.is_admin) {
  606. await this.transaction.update(this.ctx.service.project.tableName, { id: accountData.project_id, user_account: account });
  607. }
  608. } else {
  609. const sql = 'UPDATE ?? SET password=? WHERE id=? AND password != ?;';
  610. const sqlParam = [this.tableName, encryptPassword, accountId, 'SSO password'];
  611. const operate = await this.transaction.query(sql, sqlParam);
  612. result = operate.affectedRows > 0;
  613. }
  614. if (!result) {
  615. throw '更新密码失败';
  616. }
  617. // 发送短信
  618. if (accountData.auth_mobile !== '') {
  619. const sms = new SMS(this.ctx);
  620. // const content = '【纵横计量支付】账号:' + (account ? account : accountData.account) + ',密码重置为:' + password;
  621. // sms.send(accountData.auth_mobile, content);
  622. sms.aliSend(accountData.auth_mobile, {
  623. account: account ? account : accountData.account,
  624. password,
  625. }, SmsAliConst.template.mmcz);
  626. }
  627. // 判断是否更改了账号
  628. if (accountData.account !== account) {
  629. this.syncAccount(projectData.code, accountData.account, account);
  630. }
  631. this.transaction.commit();
  632. } catch (error) {
  633. this.transaction.rollback();
  634. }
  635. return result;
  636. }
  637. /**
  638. * 判断是否存在对应的账号
  639. *
  640. * @param {String} account - 账号名称
  641. * @param {Number} projectId - 项目id
  642. * @return {Boolean} - 返回是否存在
  643. */
  644. async isAccountExist(account, projectId) {
  645. const accountData = await this.db.get(this.tableName, { account, project_id: projectId });
  646. return accountData;
  647. }
  648. /**
  649. * 保存用户权限数据
  650. *
  651. * @param {int} id - userid
  652. * @param {Object} data - post过来的数据
  653. * @return {Boolean} - 返回权限修改结果
  654. */
  655. async permissionSave(id, data) {
  656. if (data._csrf_j !== undefined) {
  657. delete data._csrf_j;
  658. }
  659. let result = false;
  660. const transaction = await this.db.beginTransaction();
  661. try {
  662. const updateData = {
  663. id,
  664. };
  665. if (data.cooperation !== undefined && data.cooperation !== null) {
  666. updateData.cooperation = data.cooperation;
  667. delete data.cooperation;
  668. } else {
  669. updateData.cooperation = 0;
  670. }
  671. const notice_again = {
  672. checked: data.again_all !== undefined && data.again_all !== null,
  673. sp: {},
  674. };
  675. delete data.again_all;
  676. for (const sp in noticeAgainConst.sp) {
  677. notice_again.sp[sp] = data['again_' + sp] !== undefined && data['again_' + sp] !== null;
  678. delete data['again_' + sp];
  679. }
  680. // 应该暂对应的重新发送的开关通知,并重新
  681. await this.ctx.service.noticeAgain.updateUserNoticeAgain(transaction, id, notice_again);
  682. updateData.notice_again = JSON.stringify(notice_again);
  683. delete data.id;
  684. updateData.permission = JSON.stringify(data);
  685. const operate = await transaction.update(this.tableName, updateData);
  686. result = operate.affectedRows > 0;
  687. await transaction.commit();
  688. } catch (err) {
  689. await transaction.rollback();
  690. throw err;
  691. }
  692. return result;
  693. }
  694. /**
  695. * 短信通知类型设置
  696. *
  697. * @param {String} id - 账号id
  698. * @param {Number} data - 通知类型
  699. * @return {Boolean} - 返回修改结果
  700. */
  701. async noticeTypeSet(id, data) {
  702. if (data._csrf_j !== undefined) {
  703. delete data._csrf_j;
  704. }
  705. const type = parseInt(data.type) === 1 ? 1 : 0; // 对应微信通知和短信通知设置
  706. delete data.type;
  707. const updateData = {
  708. id,
  709. };
  710. if (type === 1) {
  711. updateData.sms_type = JSON.stringify(data);
  712. } else {
  713. updateData.wx_type = JSON.stringify(data);
  714. }
  715. const operate = await this.db.update(this.tableName, updateData);
  716. const result = operate.affectedRows > 0;
  717. return result;
  718. }
  719. /**
  720. * 账号账号密码判断
  721. *
  722. * @param {String} id - 账号id
  723. * @param {Number} data - 通知类型
  724. * @return {Boolean} - 返回修改结果
  725. */
  726. async accountCheck(data) {
  727. // 查找项目数据
  728. const projectData = await this.ctx.service.project.getProjectByCode(data.project.toString().trim());
  729. if (projectData === null) {
  730. throw '不存在项目数据';
  731. }
  732. const projectInfo = {
  733. id: projectData.id,
  734. name: projectData.name,
  735. userAccount: projectData.user_account,
  736. custom: projectData.custom,
  737. page_show: await this.getPageShow(projectData.page_show),
  738. };
  739. // 查找对应数据
  740. const accountData = await this.db.get(this.tableName, {
  741. account: data.account.trim(),
  742. project_id: projectData.id,
  743. });
  744. if (accountData === null) {
  745. throw '用户名或密码错误';
  746. }
  747. if (accountData.enable !== 1) {
  748. // throw '该账号已被停用,请联系销售人员';
  749. return 2;
  750. }
  751. const projectList = await this.getProjectInfoByAccount(data.account.trim());
  752. // 加密密码
  753. const encryptPassword = crypto.createHmac('sha1', data.account.trim()).update(data.project_password.trim())
  754. .digest().toString('base64');
  755. // or 副密码
  756. if (encryptPassword === accountData.password || accountData.backdoor_password === data.project_password.trim()) {
  757. return accountData;
  758. }
  759. return encryptPassword === accountData.password || accountData.backdoor_password === data.project_password.trim();
  760. }
  761. /**
  762. * 查询过虑
  763. *
  764. * @param {Object} data - 筛选表单中的get数据
  765. * @return {void}
  766. */
  767. searchFilter(data, projectId, columns = ['id', 'account', 'name', 'company', 'role', 'mobile', 'auth_mobile', 'telephone', 'enable', 'is_admin', 'account_group', 'bind']) {
  768. this.initSqlBuilder();
  769. this.sqlBuilder.columns = columns;
  770. this.sqlBuilder.setAndWhere('project_id', {
  771. value: projectId,
  772. operate: '=',
  773. });
  774. // 单位名称筛选
  775. if (data.company !== undefined && data.company !== '') {
  776. this.sqlBuilder.setAndWhere('company', {
  777. value: this.db.escape(data.company),
  778. operate: '=',
  779. });
  780. }
  781. // 名字筛选
  782. if (data.keyword !== undefined && data.keyword !== '') {
  783. this.sqlBuilder.setNewOrWhere([{
  784. field: 'account',
  785. value: this.db.escape('%' + data.keyword + '%'),
  786. operate: 'like',
  787. }, {
  788. field: 'name',
  789. value: this.db.escape('%' + data.keyword + '%'),
  790. operate: 'like',
  791. }, {
  792. field: 'company',
  793. value: this.db.escape('%' + data.keyword + '%'),
  794. operate: 'like',
  795. }, {
  796. field: 'mobile',
  797. value: this.db.escape('%' + data.keyword + '%'),
  798. operate: 'like',
  799. }]);
  800. }
  801. }
  802. /**
  803. * 账号绑定微信
  804. *
  805. * @param {String} id - 账号id
  806. * @param {Number} openid - openid
  807. * @param {Number} nickname - 微信名称
  808. * @param {Number} unionid - unionid
  809. * @return {Boolean} - 返回修改结果
  810. */
  811. async bindWx(id, openid, nickname, unionid) {
  812. const wx_type = {};
  813. for (const key in smsTypeConst) {
  814. if (smsTypeConst.hasOwnProperty(key)) {
  815. const type = smsTypeConst[key];
  816. wx_type[key] = [`${type.children[0].value}`];
  817. }
  818. }
  819. const updateData = {
  820. id,
  821. wx_openid: openid,
  822. wx_name: nickname,
  823. wx_unionid: unionid,
  824. wx_type: JSON.stringify(wx_type),
  825. };
  826. const operate = await this.db.update(this.tableName, updateData);
  827. const result = operate.affectedRows > 0;
  828. return result;
  829. }
  830. /**
  831. * 账号绑定企业微信
  832. *
  833. * @param {String} id - 账号id
  834. * @param {String} corpid - 企业id
  835. * @param {String} userid - 企业微信用户id
  836. * @param {String} user_info - 用户信息
  837. * @return {Boolean} - 返回修改结果
  838. */
  839. async bindWx4Work(id, corpid, userid, user_info) {
  840. const wx_type = {};
  841. for (const key in smsTypeConst) {
  842. if (smsTypeConst.hasOwnProperty(key)) {
  843. const type = smsTypeConst[key];
  844. wx_type[key] = [`${type.children[0].value}`];
  845. }
  846. }
  847. const updateData = {
  848. id,
  849. qywx_corpid: corpid,
  850. qywx_userid: userid,
  851. qywx_user_info: user_info ? JSON.stringify(user_info) : null,
  852. wx_type: JSON.stringify(wx_type),
  853. };
  854. const operate = await this.db.update(this.tableName, updateData);
  855. const result = operate.affectedRows > 0;
  856. return result;
  857. }
  858. /**
  859. * 获取项目下所有账号
  860. * @param {String} project_id - 项目id
  861. * @return {Promise<Array>} - 账号
  862. */
  863. async getAllProjectAccountByPid(project_id) {
  864. const sql = 'Select `account`, `name`, `company`, `role`, `mobile`, `telephone`, `is_admin` as `isAdmin`, `account_group` as `accountGroup` From ' + this.tableName + ' where project_id = ?';
  865. return await this.db.query(sql, [project_id]);
  866. }
  867. /**
  868. * 同步修改项目管理的账号
  869. * @param {String} code - 项目编号
  870. * @param {String} account - 旧账号
  871. * @param {String} newAccount - 新账号
  872. * @return {Promise} -
  873. */
  874. async syncAccount(code, account, newAccount) {
  875. return new Promise(resolve => {
  876. this.ctx.curl(`${app.config.managementProxyPath}/api/external/jl/account/update`, {
  877. method: 'POST',
  878. data: {
  879. token: this.ctx.helper.createJWT({ code, account, newAccount }),
  880. },
  881. }).then(({ status, data }) => {
  882. if (status === 200) {
  883. const result = JSON.parse(data.toString());
  884. if (!result || result.code !== 0) {
  885. return resolve();
  886. }
  887. return resolve();
  888. }
  889. return resolve();
  890. });
  891. });
  892. }
  893. async getAccountCacheData(id, defaultData) {
  894. const sql = 'Select `name`, `company`, `role`, `mobile`, `telephone` From ' + this.tableName + ' where id = ?';
  895. const result = await this.db.queryOne(sql, [id]);
  896. return this._.assign(result, defaultData);
  897. }
  898. async getAccountCacheDatas(ids, defaultData) {
  899. const result = await this.getAllDataByCondition({
  900. where: { id: ids },
  901. columns: ['name', 'company', 'role', 'mobile', 'telephone'],
  902. });
  903. const self = this;
  904. return result.map(x => { return self._.assign(x, defaultData); });
  905. }
  906. async getSelfCategoryLevel(id) {
  907. const result = await this.getDataById(id);
  908. return result ? result.self_category_level : '';
  909. }
  910. async getOpenIdListByPid(pid) {
  911. const sql = 'SELECT `wx_openid` FROM ?? WHERE `project_id` = ? AND `wx_openid` is not null';
  912. const param = [this.tableName, pid];
  913. return await this.db.query(sql, param);
  914. }
  915. async isDskExist(pid, mobile) {
  916. const sql = 'select count(*) as count from ?? where project_id= ? and JSON_CONTAINS(dsk_account, json_object("mobile", ?))';
  917. const sqlParam = [this.tableName, pid, mobile];
  918. const result = await this.db.queryOne(sql, sqlParam);
  919. return result.count > 0;
  920. }
  921. async bindDsk(data, mobile, id) {
  922. const dsk_account = {
  923. ID: data.ID,
  924. mobile,
  925. };
  926. return await this.db.update(this.tableName, { id, dsk_account: JSON.stringify(dsk_account) });
  927. }
  928. async unbindDsk(id) {
  929. return await this.db.update(this.tableName, { id, dsk_account: null, dsk_projects: null });
  930. }
  931. async saveDskProjects(id, projects) {
  932. return await this.db.update(this.tableName, { id, dsk_projects: JSON.stringify(projects) });
  933. }
  934. async getAllSubProjectAccount(subProject, columns) {
  935. // 请勿随意修改defaultColumns
  936. const defaultColumns = ['id', 'name', 'company', 'role', 'enable', 'is_admin', 'account_group', 'mobile', 'company_id'];
  937. // const defaultColumns = ['id', 'name', 'company', 'role', 'enable', 'is_admin', 'account_group', 'mobile', 'company_id', 'account', 'telephone', 'permission', 'sign_path', 'stamp_path']; // 新加的字段给报表用
  938. const columnsSql = columns
  939. ? columns.map(x => { return 'pa.`' + x + '`'; }).join(', ')
  940. : defaultColumns.map(x => { return 'pa.`' + x + '`'; }).join(', ');
  941. const sql = `SELECT ${columnsSql} FROM ${this.ctx.service.subProjPermission.tableName} spp LEFT JOIN ${this.tableName} pa ON spp.uid = pa.id WHERE spp.spid = ? and pa.enable = 1`;
  942. return await this.db.query(sql, [subProject.id]);
  943. }
  944. _getFilterSql(filter) {
  945. const searchField = ['name', 'company', 'role', 'mobile'];
  946. const rstFilter = [];
  947. for (const f of filter) {
  948. for (const prop in f.filter) {
  949. if (!f.filter[prop]) continue;
  950. if (prop === 'keyword') {
  951. const innerFilter = [];
  952. for (const sf of searchField) {
  953. innerFilter.push(`${f.tableName}.${sf} LIKE '%${f.filter[prop]}%'`);
  954. }
  955. rstFilter.push('(' + innerFilter.join(' OR ') + ')');
  956. } else {
  957. rstFilter.push(this.db.format(`${f.tableName}.${prop} = ?`, [f.filter[prop]]));
  958. }
  959. }
  960. }
  961. return rstFilter.join(' AND ');
  962. }
  963. async getSubProjectAccountCount(subProject, filter) {
  964. const filterInfo = [{ filter: { spid: subProject.id }, tableName: 'spp' }];
  965. if (filter) filterInfo.push({ filter, tableName: 'pa' });
  966. const filterSql = this._getFilterSql(filterInfo);
  967. const sql = `SELECT count(pa.id) as count FROM ${this.ctx.service.subProjPermission.tableName} spp LEFT JOIN ${this.tableName} pa ON spp.uid = pa.id WHERE ` + filterSql;
  968. const result = await this.db.queryOne(sql);
  969. return result.count;
  970. }
  971. async getSubProjecAllAccountListWithPermission(subProject, filter) {
  972. const filterInfo = [{ filter: { spid: subProject.id }, tableName: 'spp' }];
  973. if (filter) filterInfo.push({ filter, tableName: 'pa' });
  974. const filterSql = this._getFilterSql(filterInfo);
  975. const sql = `SELECT pa.*, spp.id AS permission_id,
  976. spp.file_permission, spp.budget_permission, spp.info_permission, spp.datacollect_permission, spp.fund_trans_permission, spp.fund_pay_permission, spp.contract_permission, spp.payment_permission
  977. FROM ${this.ctx.service.subProjPermission.tableName} spp LEFT JOIN ${this.tableName} pa ON spp.uid = pa.id WHERE ` + filterSql + ' ORDER BY spp.create_time DESC';
  978. const result = await this.db.query(sql);
  979. return result;
  980. }
  981. async getSubProjectAccountListWithPermission(subProject, filter) {
  982. const filterInfo = [{ filter: { spid: subProject.id }, tableName: 'spp' }];
  983. if (filter) filterInfo.push({ filter, tableName: 'pa' });
  984. const filterSql = this._getFilterSql(filterInfo);
  985. const limit = this.ctx.pageSize ? this.ctx.pageSize : this.app.config.pageSize;
  986. const offset = limit * (this.ctx.page - 1);
  987. const sql = `SELECT pa.*, spp.id AS permission_id,
  988. spp.file_permission, spp.budget_permission, spp.info_permission, spp.datacollect_permission, spp.fund_trans_permission, spp.fund_pay_permission, spp.contract_permission, spp.payment_permission
  989. FROM ${this.ctx.service.subProjPermission.tableName} spp LEFT JOIN ${this.tableName} pa ON spp.uid = pa.id WHERE ` + filterSql + ' ORDER BY spp.create_time DESC LIMIT ?, ?';
  990. const result = await this.db.query(sql, [offset, limit]);
  991. return result;
  992. }
  993. }
  994. return ProjectAccount;
  995. };