Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /*
- * @author Oleg Khalidov <brooth@gmail.com>.
- * -----------------------------------------------
- * Freelance software development:
- * Upwork: https://www.upwork.com/fl/khalidovoleg
- * Freelancer: https://www.freelancer.com/u/brooth
- */
- import { Router, Response } from 'express'
- import { Pool, Connection, PoolConnection } from 'mysql'
- import { Observable, } from 'rxjs'
- import fetch from 'node-fetch';
- import * as uuid from 'uuid'
- import { parseNumber } from 'libphonenumber-js'
- import * as microtime from 'microtime'
- import { SyncContact, ContactPhone, Contact, ContactEmail, GSearchResult } from '../models/domain.models'
- import { AuthRequest } from './auth.controller'
- import { QueryObservable, GetConnectionObservable } from '../utils/rx.utils';
- import { User } from '../models/auth.models';
- import { loadContactNotes } from './contact_notes.controller';
- import { C } from '../c';
- import { googleGeocodeLocation } from './location.controller';
- import { loadContactComments } from './contact_comments.controller';
- import * as md5 from 'md5';
- export class ContactsController {
- private db: Pool
- constructor(connection: Pool) {
- this.db = connection
- this.lookupPhoneLocation = this.lookupPhoneLocation.bind(this)
- }
- router(): Router {
- const router = Router()
- router.get('/', this.get.bind(this))
- router.get('/holders', this.loadHolders.bind(this))
- router.get('/dislike', this.getDisliked.bind(this))
- router.get('/count', this.getContactsCount.bind(this))
- router.get('/common', this.findCommonContacts.bind(this))
- router.post('/', this.add.bind(this))
- router.patch('/', this.sync.bind(this))
- router.patch('/public/:id', this.changePublic.bind(this))
- router.get('/fillHash', this.fillHash.bind(this))
- router.get('/deleteDuplicates', this.deleteDuplicates.bind(this))
- return router
- }
- private findCommonContacts(req: AuthRequest, res: Response) {
- console.log('ContactsController.findCommonContacts()')
- const ids = req.query.ids.split(',')
- GetConnectionObservable(this.db)
- .concatMap(connection => QueryObservable<Contact[]>(connection,
- 'SELECT c.id, cp.value FROM contacts c' +
- ' INNER JOIN contact_phones cp on cp.contactId = c.id' +
- ' WHERE c.id IN (?)',
- [ids])
- .do(_ => connection.release())
- .catch(error => {
- connection.release();
- return Observable.throw(error);
- }))
- .subscribe((data: { id: string, value: string }[]) => {
- const results: { [key: string]: string[] } = {}
- for (let i = 0; i < data.length; i++) {
- const c1 = data[i]
- for (let j = 0; j < data.length; j++) {
- const c2 = data[j]
- if (i == j || c1.id === c2.id)
- continue
- let common = false
- if (c1.value === c2.value) {
- common = true
- } else {
- const phone1Parts = c1.value.match(/\d+/g)
- const phone2Parts = c2.value.match(/\d+/g)
- if (phone1Parts && phone2Parts) {
- const formattedPhone1 = phone1Parts.join('')
- const formattedPhone2 = phone2Parts.join('')
- if (formattedPhone1 === formattedPhone2)
- common = true
- }
- }
- if (common) {
- if (!results[c1.id])
- results[c1.id] = []
- if (!results[c1.id].includes(c2.id))
- results[c1.id].push(c2.id)
- }
- }
- }
- res.send({ results });
- }, error => {
- console.error(error)
- res.status(500).send()
- })
- }
- private getContactsCount(req: AuthRequest, res: Response) {
- console.log('ContactsController.getContactsCount()')
- GetConnectionObservable(this.db)
- .concatMap(connection => QueryObservable<Contact[]>(connection,
- 'SELECT count(*) as count FROM contacts')
- .do(_ => connection.release())
- .catch(error => {
- connection.release();
- return Observable.throw(error);
- }))
- .subscribe(results => {
- res.send({ result: results[0].count });
- }, error => {
- console.error(error)
- res.status(500).send()
- })
- }
- private loadHolders(req: AuthRequest, res: Response) {
- console.log('ContactsController.loadHolders()')
- GetConnectionObservable(this.db)
- .concatMap(connection => QueryObservable<Contact[]>(connection,
- 'SELECT id, name FROM contacts WHERE holderId IS NULL and id != ?',
- [req.user.contactId])
- .do(_ => connection.release())
- .catch(error => {
- connection.release();
- return Observable.throw(error);
- }))
- .subscribe(results => {
- res.send({ results });
- }, error => {
- console.error(error)
- res.status(500).send()
- })
- }
- private changePublic(req: AuthRequest, res: Response) {
- console.log('ContactsController.changePublic()', req.params, req.body)
- const updateTs = microtime.now()
- const contactId = req.params.id
- const value = req.body.public === true
- const restrictedGroup = req.body.restrictedTo || []
- const restrict = value && restrictedGroup.length > 0
- const queries = [
- 'UPDATE contacts SET public = ?, public_restricted =?, updateTs = ? WHERE id = ?']
- const params = [value, restrict, updateTs, contactId]
- if (restrict) {
- restrictedGroup.forEach((holderId: string) => {
- queries.push('INSERT INTO public_restrictions VALUES(?, ?, ?)')
- params.push(uuid.v4(), contactId, holderId)
- })
- } else {
- queries.push('DELETE FROM public_restrictions WHERE contactId = ?')
- params.push(contactId)
- }
- GetConnectionObservable(this.db)
- .concatMap(connection => QueryObservable<Contact[]>(connection,
- queries.join(';'), params)
- .do(_ => connection.release())
- .catch(error => {
- connection.release();
- return Observable.throw(error);
- }))
- .subscribe(_ => {
- res.send({ updateTs });
- }, error => {
- console.error(error)
- res.status(500).send()
- })
- }
- private getDisliked(req: AuthRequest, res: Response) {
- console.log('ContactsController.getDisliked()')
- GetConnectionObservable(this.db)
- .concatMap(connection => QueryObservable<Contact[]>(connection,
- 'SELECT uc.id, uc.localId as lid, uc.name as n, false as lk, h.id as hid, h.name as hn' +
- ' FROM contacts uc' +
- ' LEFT JOIN contacts h ON h.id = uc.holderId' +
- ' WHERE uc.holderId != ? AND uc.deleteTs IS NULL' +
- ' AND EXISTS(SELECT id FROM contact_likes cl WHERE uc.id = cl.contactId AND cl.value = 0 LIMIT 1)' +
- ' LIMIT 1000',
- [req.user.contactId])
- .concatMap(results =>
- attachContactDetails(connection, results.map(contact => {
- contact.p = !!contact.p
- if (contact.lk != null)
- contact.lk = !!contact.lk
- const c = contact as any
- c.h = {
- id: c.hid,
- n: c.hn,
- }
- return contact
- }), null, false, true))
- .do(_ => connection.release())
- .catch(error => {
- connection.release();
- return Observable.throw(error);
- }))
- .subscribe(results => {
- res.send({ results });
- }, error => {
- console.error(error)
- res.status(500).send()
- })
- }
- private get(req: AuthRequest, res: Response) {
- console.log('ContactsController.get()', req.query)
- GetConnectionObservable(this.db)
- .concatMap(connection => (
- req.query.southEastLatitude
- ? this.searchInRegion(req, connection)
- .concatMap(results =>
- attachContactDetails(connection, results, req.user.contactId, 'if public', false))
- : req.query.query != null
- ? this.searchByQuery(req, connection)
- .concatMap(results =>
- attachContactDetails(connection, results, req.user.contactId, 'if public', false))
- : loadUserContacts(connection, req.user,
- parseInt(req.query.since), parseInt(req.query.limit)))
- .do(_ => connection.release())
- .catch(error => {
- connection.release();
- return Observable.throw(error);
- }))
- .subscribe(results => {
- res.send({ results });
- }, error => {
- console.error(error)
- res.status(500).send()
- })
- }
- private searchByQuery(req: AuthRequest, connection: PoolConnection): Observable<Contact[]> {
- console.log('ContactsController.searchByQuery()')
- const queries = req.query.query.split('|') as string[]
- return Observable.from(queries)
- .map(query => '%' + query.trim().toLowerCase() + '%')
- .concatMap(q => QueryObservable<Contact[]>(connection,
- 'SELECT uc.id, uc.name as n, cl.value as lk,' +
- ' COUNT(DISTINCT c.id) as noc,' +
- ' AVG(cp.latitude) as clt,' +
- ' AVG(cp.longitude) as cln' +
- ' FROM contacts uc' +
- ' LEFT JOIN contact_likes cl ON uc.id = cl.contactId AND cl.userId = ?' +
- ' LEFT JOIN contacts c ON c.deleteTs IS NULL AND uc.id = c.holderId AND c.public = 0' +
- ' LEFT JOIN contact_phones cp ON c.id = cp.contactId' +
- ' LEFT JOIN contact_emails ce ON c.id = ce.contactId' +
- ' LEFT JOIN contact_notes cn ON c.id = cn.contactId' +
- ' LEFT JOIN gsearch_results gsr ON gsr.phoneId = cp.id' +
- ' WHERE uc.id != ?' +
- ' AND uc.holderId IS NULL' +
- ' AND (' +
- ' LOWER(c.name) like ?' +
- ' OR LOWER(cp.label) like ?' +
- ' OR cp.value like ?' +
- ' OR cp.country like ?' +
- ' OR cp.locality like ?' +
- ' OR LOWER(ce.label) like ?' +
- ' OR ce.value like ?' +
- ' OR LOWER(cn.text) like ?' +
- ' OR LOWER(gsr.title) like ?' +
- ' OR LOWER(gsr.description) like ?' +
- ' )' +
- ' GROUP BY uc.id, lk',
- [req.user.id, req.user.contactId,
- q, q, q, q, q, q, q, q, q, q, q, q])
- .concatMap(holders =>
- QueryObservable<Contact[]>(connection,
- 'SELECT DISTINCT c.id, c.name as n, hl.value as lk, h.id as hid,' +
- ' h.name as hn' +
- ' FROM contacts c' +
- ' LEFT JOIN contacts h ON h.id = c.holderId' +
- ' LEFT JOIN users hu ON hu.contactId = h.id' +
- ' LEFT JOIN contact_likes hl ON hl.userId = hu.id AND hl.contactId = c.id' +
- ' LEFT JOIN contact_phones cp ON c.id = cp.contactId' +
- ' LEFT JOIN contact_emails ce ON c.id = ce.contactId' +
- ' LEFT JOIN gsearch_results gsr ON gsr.phoneId = cp.id AND c.public = 1' +
- ' LEFT JOIN contact_comments cc ON c.id = cc.contactId AND cc.authorId = ?' +
- ' LEFT JOIN public_restrictions pr ON c.id = pr.contactId AND pr.restrictedTo = ?' +
- ' WHERE c.deleteTs IS NULL' +
- ' AND (' +
- ' (c.holderId != ? AND c.public = 1 AND ' +
- ' (c.public_restricted = 0 OR pr.id IS NOT NULL))' +
- ' OR (c.holderId IS NULL AND c.id NOT IN (?))' +
- ' )' +
- ' AND (' +
- ' LOWER(c.name) like ?' +
- ' OR LOWER(cp.label) like ?' +
- ' OR cp.value like ?' +
- ' OR cp.country like ?' +
- ' OR cp.locality like ?' +
- ' OR LOWER(ce.label) like ?' +
- ' OR ce.value like ?' +
- ' OR LOWER(gsr.title) like ?' +
- ' OR LOWER(gsr.description) like ?' +
- ' OR LOWER(cc.text) like ?' +
- ' )',
- [req.user.contactId, req.user.contactId, req.user.contactId,
- [req.user.contactId, ...holders.map(h => h.id)],
- q, q, q, q, q, q, q, q, q, q, q])
- .map(publics => {
- publics.forEach((s: any) => {
- if (s.hid != null) {
- s.h = {
- id: s.hid,
- n: s.hn,
- }
- }
- if (s.lk != null) s.lk = !!s.lk
- delete s.hid
- delete s.hn
- holders.push(s)
- })
- return holders
- }))
- )
- .reduce((prev, next) => {
- const results = Array.from(prev)
- next.forEach(n => {
- if (prev.findIndex(p => p.id == n.id) == -1)
- results.push(n)
- })
- return results
- })
- }
- private searchInRegion(req: AuthRequest, connection: PoolConnection): Observable<Contact[]> {
- console.log('ContactsController.searchInRegion()')
- const holdersQuery = [
- 'SELECT uc.id, uc.name as n, ucl.value as lk, ucp.country as cn, ucp.locality as lc,',
- ' COUNT(DISTINCT c.id) as noc,',
- ' GROUP_CONCAT(DISTINCT c.id) as cs,' +
- ' AVG(ucp.latitude) as clt,',
- ' AVG(ucp.longitude) as cln',
- ' FROM contacts uc',
- ' LEFT JOIN contact_likes ucl ON uc.id = ucl.contactId AND ucl.userId = ?',
- ' LEFT JOIN contacts c ON c.deleteTs IS NULL AND uc.id = c.holderId AND c.public = 0',
- ' LEFT JOIN contact_phones ucp ON c.id = ucp.contactId']
- const holdersQueryParams = [req.user.id]
- const publicsQuery = [
- 'SELECT DISTINCT c.id, c.name as n, hl.value as lk, h.id as hid, h.name as hn',
- ' FROM contacts c',
- ' LEFT JOIN contacts h ON h.id = c.holderId' +
- ' LEFT JOIN users hu ON hu.contactId = h.id' +
- ' LEFT JOIN contact_likes hl ON hl.userId = hu.id AND hl.contactId = c.id' +
- ' LEFT JOIN contact_comments cc ON c.id = cc.contactId AND cc.authorId = ?' +
- ' LEFT JOIN public_restrictions pr ON c.id = pr.contactId AND pr.restrictedTo = ?' +
- ' INNER JOIN contact_phones cp ON c.id = cp.contactId' +
- ' AND cp.latitude BETWEEN ? AND ?' +
- ' AND cp.longitude BETWEEN ? AND ?'
- ]
- const publicsQueryParams = [
- req.user.contactId, req.user.contactId,
- req.query.northWestLatitude, req.query.southEastLatitude,
- req.query.southEastLongitude, req.query.northWestLongitude]
- if (req.query.query) {
- const commonQueries =
- ' LEFT JOIN contact_emails ce ON c.id = ce.contactId' +
- ' LEFT JOIN gsearch_results gsr ON gsr.phoneId = cp.id'
- holdersQuery.push(' LEFT JOIN contact_phones cp ON c.id = cp.contactId')
- holdersQuery.push(commonQueries)
- publicsQuery.push(commonQueries)
- }
- if (req.query.holderId) {
- holdersQuery.push(' WHERE uc.id = ?')
- holdersQueryParams.push(req.query.holderId)
- publicsQuery.push(' WHERE c.holderId = ?')
- publicsQueryParams.push(req.query.holderId)
- } else {
- holdersQuery.push(' WHERE uc.id != ?')
- holdersQueryParams.push(req.user.contactId)
- publicsQuery.push(' WHERE c.deleteTs IS NULL')
- }
- holdersQuery.push(
- ' AND uc.holderId IS NULL' +
- ' AND ucp.latitude BETWEEN ? AND ?' +
- ' AND ucp.longitude BETWEEN ? AND ?')
- holdersQueryParams.push(
- req.query.northWestLatitude, req.query.southEastLatitude,
- req.query.southEastLongitude, req.query.northWestLongitude)
- publicsQuery.push(
- ' AND (' +
- ' (c.holderId != ? AND c.public = 1 AND ' +
- ' (c.public_restricted = 0 OR pr.id IS NOT NULL))' +
- ' OR (c.holderId IS NULL AND c.id != ?)' +
- ' )')
- publicsQueryParams.push(req.user.contactId, req.user.contactId)
- if (req.query.query) {
- const queries = req.query.query.split('|') as string[]
- const filters = queries
- .map(p => '%' + p.toLowerCase() + '%')
- .map(q => {
- holdersQueryParams.push(q, q, q, q, q, q, q);
- publicsQueryParams.push(q, q, q, q, q, q, q, q);
- return [
- ' LOWER(c.name) like ? ',
- ' LOWER(cp.label) like ? ',
- ' cp.value like ? ',
- ' LOWER(ce.label) like ? ',
- ' ce.value like ? ',
- ' LOWER(gsr.title) like ?',
- ' LOWER(gsr.description) like ?',
- ]
- })
- .reduce((a1, a2) => a1.concat(a2))
- holdersQuery.push(' AND (' + filters.join(' OR ') + ')')
- publicsQuery.push(' AND (' + filters.join(' OR ') + ' OR LOWER(cc.text) like ?)')
- }
- holdersQuery.push(' GROUP BY lc, cn, uc.id, lk')
- return QueryObservable<Contact[]>(connection, holdersQuery.join(''), holdersQueryParams)
- .do(holders => holders.forEach((h: any) => h.cs = h.cs.split(',')))
- .concatMap(holders =>
- QueryObservable<Contact[]>(connection, publicsQuery.join(''), publicsQueryParams)
- .map(publics => {
- publics.forEach((s: any) => {
- if (s.hid != null) {
- s.h = {
- id: s.hid,
- n: s.hn,
- }
- }
- if (s.lk != null) s.lk = !!s.lk
- delete s.hid
- delete s.hn
- holders.push(s)
- })
- return holders
- }))
- }
- private lookupPhoneLocationByK780(number: string): Observable<Partial<ContactPhone> | null> {
- const uri = `http://api.k780.com/?app=phone.get&phone=${number}&appkey=${C.K780_APP_KEY}&sign=${C.K780_APP_SIGN}&format=json`
- return Observable.from(fetch(uri))
- .concatMap(res => res.json())
- .concatMap(json => {
- if (json.success !== '1' || !json.result || !json.result.att)
- return Observable.of(null)
- return googleGeocodeLocation({ address: json.result.att })
- .map(location => {
- if (!location)
- return null
- return {
- c: location.country,
- cc: location.countryCode,
- lc: location.locality,
- lt: location.latitude,
- ln: location.longitude
- }
- })
- })
- .catch(err => {
- console.log('k780 request failed', err)
- return Observable.of(null)
- })
- }
- private lookupChinaLocalPhoneLocation(connection: Connection, number: string):
- Observable<Partial<ContactPhone> | null> {
- return QueryObservable<any[]>(
- connection,
- 'SELECT * FROM phonet WHERE area_code = ? limit 1',
- [number.slice(0, 4)])
- .concatMap(results => results.length ? Observable.of(results) :
- QueryObservable<any[]>(connection,
- 'SELECT * FROM phonet WHERE area_code = ? limit 1',
- [number.slice(0, 3)]))
- .concatMap(results => {
- if (results.length) {
- const phone: Partial<ContactPhone> = {
- lc: results[0].province + ', ' + results[0].city,
- lt: parseFloat(results[0].Lat),
- ln: parseFloat(results[0].Lon),
- }
- // yeah, they exist
- if (phone.lt! > 90 || phone.ln! > 180 ||
- phone.lt! < -90 || phone.ln! < -180) {
- console.log('Invalid coordinates found for phone %s, %d, %d',
- number, phone.lt, phone.ln)
- return Observable.of(null);
- }
- return Observable.of(phone);
- }
- return this.lookupPhoneLocationByK780(number)
- })
- }
- private lookupChinaMobilePhoneLocation(connection: Connection,
- fullNumber: string, shortNumber: string): Observable<Partial<ContactPhone> | null> {
- return QueryObservable<any[]>(connection,
- 'SELECT * FROM phonet WHERE phone = ?',
- [shortNumber.slice(0, 7)])
- .concatMap(results => {
- if (results.length) {
- const phone: Partial<ContactPhone> = {
- lc: results[0].province + ', ' + results[0].city,
- lt: parseFloat(results[0].Lat),
- ln: parseFloat(results[0].Lon),
- }
- if (phone.lt! > 90 || phone.ln! > 180 ||
- phone.lt! < -90 || phone.ln! < -180) {
- console.log('Invalid coordinates found for phone %s, %d, %d',
- shortNumber, phone.lt, phone.ln)
- return Observable.of(null);
- }
- return Observable.of(phone);
- }
- return this.lookupPhoneLocationByK780('+' + fullNumber)
- })
- }
- private lookupInternalPhoneLocation = (connection: Connection, user: User, number: string):
- Observable<Partial<ContactPhone> | null> =>
- QueryObservable<any[]>(connection,
- 'SELECT countryCode as cc FROM contact_phones' +
- ' WHERE contactId = ? AND value = ?',
- [user.contactId, user.phone])
- .concatMap(holderPhones => {
- if (holderPhones.length == 0) {
- console.error('cannot find user phone, user: %s, contactId: %s, phone: %s',
- user.id, user.contactId, user.phone)
- return Observable.of(null);
- }
- const holderPhone = holderPhones[0]
- if (holderPhone.cc == 'CN') {
- return (number.startsWith('0')
- ? this.lookupChinaLocalPhoneLocation(connection, number)
- : this.lookupChinaMobilePhoneLocation(connection, number, number))
- .map(result => {
- if (result != null)
- return {
- ...result,
- vl: true,
- c: 'ä¸å›½',
- cc: holderPhone.cc,
- }
- return result
- })
- } else {
- return Observable.of(null);
- }
- })
- private lookupPhoneLocation(connection: Connection, user: User, _phone: ContactPhone):
- Observable<ContactPhone> {
- const phone: ContactPhone = { ..._phone, vl: false }
- const numberParts = phone.v.match(/\d+/g)
- if (numberParts == null) {
- console.log('invalid phone number %s', phone.v)
- return Observable.of(phone)
- }
- const formattedPhoneNumber = numberParts.join('')
- if (phone.v.startsWith('+')) {
- const data = parseNumber(phone.v, { extended: true });
- if (data.valid === true) {
- phone.vl = true
- phone.cc = data.country
- if (phone.cc == 'CN') {
- phone.c = 'ä¸å›½';
- return this.lookupChinaMobilePhoneLocation(connection,
- formattedPhoneNumber, data.phone as string)
- .map(result => {
- if (result != null)
- return { ...phone, ...result };
- return phone
- })
- }
- }
- return Observable.of(phone);
- } else {
- return this.lookupInternalPhoneLocation(connection, user, formattedPhoneNumber)
- .map(data => {
- if (data != null)
- return { ...phone, ...data };
- return phone
- })
- }
- }
- private add(req: AuthRequest, res: Response) {
- console.log('ContactsController.add()')
- const contact = req.body.items as Contact[]
- GetConnectionObservable(this.db)
- .subscribe(
- connection => {
- this.saveContact(connection, contact[0], req)
- .subscribe(results => {
- res.send({results})
- })
- }
- )
- }
- private searchContact(v: any, contact: any) : any {
- return v.ps.map(a => a.v).filter(b => contact.ps.map(c => c.v).includes(b)).length
- }
- private saveContact(connection: Connection, contact: Contact, req: AuthRequest) {
- return Observable.from(contact.ps)
- .concatMap(phone => this.lookupPhoneLocation(connection, req.user, phone))
- .toArray()
- .map(phones => {
- const id = uuid.v4()
- const ts = microtime.now()
- const result: SyncOperationData = {
- queries: [],
- params: [],
- result: {
- operation: 'CREATE',
- contact: {
- ...contact,
- id,
- uts: ts,
- ps: phones,
- }
- }
- }
- result.queries.push(
- 'INSERT INTO contacts VALUES (?, ?, ?, null, ?, ?, ?, ?, ?, ?)')
- result.params.push(id, ts, ts, req.user.contactId,
- contact.lid, contact.n, contact.p, false, contact.uh)
- phones.forEach(phone => {
- phone.id = uuid.v4()
- result.queries.push('INSERT INTO contact_phones' +
- ' VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)')
- result.params.push(phone.id, id, phone.l, phone.v,
- phone.vl, phone.c, phone.cc, phone.lc,
- toDecimal(phone.lt), toDecimal(phone.ln))
- })
- contact.es.forEach(e => {
- result.queries.push('INSERT INTO contact_emails VALUES (?, ?, ?, ?)')
- result.params.push(uuid.v4(), id, e.l, e.v)
- })
- return result;
- })
- .concatMap(q => QueryObservable(connection, q.queries.join(';'), q.params).mapTo(q.result))
- }
- private updateContact(connection: Connection, old: Contact, contact: Contact, req: AuthRequest) {
- return Observable.from(contact.ps)
- .concatMap(phone => this.lookupPhoneLocation(connection, req.user, phone))
- .toArray()
- .map(phones => {
- const ts = microtime.now()
- const data: SyncOperationData = {
- queries: [
- 'DELETE FROM contact_phones WHERE contactId = ?',
- 'DELETE FROM contact_emails WHERE contactId = ?',
- ],
- params: [old.id, old.id],
- }
- data.queries.push('UPDATE contacts SET updateTs = ?, deleteTs = NULL, name = ?, unique_hash = ?' +
- ' WHERE id = ?')
- data.params.push(ts, contact.n, contact.uh, old.id)
- phones.forEach(p => {
- p.id = uuid.v4()
- data.queries.push('INSERT INTO contact_phones' +
- ' VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)')
- data.params.push(p.id, old.id, p.l, p.v,
- p.vl, p.c, p.cc, p.lc,
- toDecimal(p.lt), toDecimal(p.ln))
- })
- contact.es.forEach(e => {
- data.queries.push('INSERT INTO contact_emails VALUES (?, ?, ?, ?)')
- data.params.push(uuid.v4(), old.id, e.l, e.v)
- })
- return data;
- })
- .concatMap(q => QueryObservable(connection,
- q.queries.join(';'),
- q.params))
- .subscribe()
- }
- private deleteContact(connection: Connection, contact: string) {
- return Observable.of({
- queries: [
- 'DELETE FROM contacts WHERE id = ?',
- 'DELETE FROM contact_notes WHERE contactId = ?',
- 'DELETE FROM contact_phones WHERE contactId = ?',
- 'DELETE FROM contact_emails WHERE contactId = ?'
- ],
- params: [contact, contact, contact, contact],
- })
- .concatMap(q => QueryObservable(connection,
- q.queries.join(';'),
- q.params))
- }
- private formatNumber(number: string): string {
- return number
- .trim()
- .replace(/-|\(|\)|( )/g, '')
- }
- private formatEmail(email: string): string {
- return email
- .trim()
- .toLowerCase()
- }
- private createHash (contact: Contact) : string {
- const phones = contact.ps
- .map(phone => phone.v)
- .map(n => this.formatNumber(n))
- .sort()
- .join('');
- const emails = contact.es
- .map(email => email.v)
- .map(this.formatEmail)
- .sort()
- .join('');
- console.log(`${contact.n}${phones}${emails}`)
- return md5(`${contact.n}${phones}${emails}`)
- }
- private sync(req: AuthRequest, res: Response) {
- console.log('ContactsController.sync() ', req.body.items.length)
- const syncResponse : SyncOperationResponse[] = [];
- const items = req.body.items as Contact[];
- const processedContacts : string[] = [];
- GetConnectionObservable(this.db)
- .concatMap(connection => {
- return QueryObservable<Contact[]>(connection,
- 'SELECT c.id, c.updateTs as uts, c.deleteTs as d, c.localId as lid, name as n,' +
- ' cl.value as lk, c.public as p, unique_hash as uh' +
- ' FROM contacts c' +
- ' LEFT JOIN contact_likes cl ON c.id = cl.contactId AND cl.userId = ?' +
- ' WHERE holderId = ?' +
- ' AND deleteTs is null' +
- ' ORDER BY updateTs',
- [req.user.id, req.user.contactId])
- .concatMap(contacts => attachContactDetails(connection, contacts, null, true, true))
- .concatMap(dbContacts => {
- dbContacts.forEach(contact => {
- const isProcessed = processedContacts.includes(contact.uh)
- if(isProcessed) return
- processedContacts.push(contact.uh)
- const existContactIndex = items.findIndex(localContact => (contact.uh === localContact.ouh) || (contact.uh === localContact.uh))
- if(existContactIndex === -1){
- syncResponse.push({ operation: 'CREATE', contact: contact } as SyncOperationResponse)
- } else {
- if(items[existContactIndex].ouh && items[existContactIndex].uh !== items[existContactIndex].ouh) {
- this.updateContact(connection, contact, items[existContactIndex], req)
- contact.lk ? items[existContactIndex].lk = contact.lk : null
- contact.ns ? items[existContactIndex].ns = contact.ns : null
- contact.p ? items[existContactIndex].p = contact.p : null
- contact.cc ? items[existContactIndex].cc = contact.cc : null
- contact.uts ? items[existContactIndex].uts = contact.uts : null
- syncResponse.push({ operation: 'UPDATE', contact: items[existContactIndex] } as SyncOperationResponse)
- processedContacts.push(items[existContactIndex].uh, items[existContactIndex].ouh)
- } else {
- contact.lid = items[existContactIndex].lid
- // syncResponse.push({ operation: 'SKIP', contact: contact } as SyncOperationResponse)
- }
- }
- })
- items.filter(contact => !processedContacts.includes(contact.uh))
- .forEach(contact => {
- const isProcessed = processedContacts.includes(contact.uh)
- if(!isProcessed){
- this.saveContact(connection, contact, req)
- .subscribe(createdContact => console.log('createdContact=> ', createdContact))
- // syncResponse.push({ operation: 'LOCAL', contact } as SyncOperationResponse)
- processedContacts.push(contact.uh)
- }
- })
- console.log('return')
- return dbContacts;
- })
- .do(_ => connection.release())
- .catch(error => {
- connection.release();
- return Observable.throw(error);
- })
- })
- .toArray()
- .subscribe(
- _ => {
- res.send({ results: syncResponse })
- },
- error => {
- console.error(error)
- res.status(500).send()
- })
- }
- private fillHash(req: AuthRequest, res: Response) {
- console.log('ContactsController.fillHash()')
- const params: any = [];
- const queries: any = [];
- GetConnectionObservable(this.db)
- .concatMap(connection => {
- return QueryObservable<Contact[]>(connection,
- 'SELECT c.id, c.updateTs as uts, c.deleteTs as d, c.localId as lid, name as n,' +
- ' cl.value as lk, c.public as p, unique_hash as uh' +
- ' FROM contacts c' +
- ' LEFT JOIN contact_likes cl ON c.id = cl.contactId' +
- ' AND deleteTs is null' +
- ' ORDER BY updateTs',
- [])
- .concatMap(contacts => attachContactDetails(connection, contacts, null, true, true))
- .concatMap(dbContacts => {
- dbContacts.map(contact => {
- queries.push('UPDATE contacts SET unique_hash = ? WHERE id = ?');
- params.push(this.createHash(contact), contact.id)
- })
- console.log(queries.length)
- Observable.of({
- queries,
- params
- }).concatMap(q => QueryObservable(connection,
- q.queries.join(';'),
- q.params))
- .subscribe()
- return dbContacts;
- })
- .do(_ => connection.release())
- .catch(error => {
- connection.release();
- return Observable.throw(error);
- })
- })
- .toArray()
- .subscribe(
- _ => {
- res.send({ result: 'OK' })
- },
- error => {
- console.error(error)
- res.status(500).send()
- })
- }
- private deleteDuplicates(req: AuthRequest, res: Response) {
- console.log('ContactsController.deleteDuplicates()')
- const params: any = [];
- const queries: any = [];
- GetConnectionObservable(this.db)
- .concatMap(connection => {
- return QueryObservable<User[]>(connection, 'SELECT * from users', [])
- .concatMap(dbUsers => Observable.from(dbUsers)
- .concatMap(user => QueryObservable<Contact[]>(connection,
- 'SELECT c.id, c.updateTs as uts, c.deleteTs as d, c.localId as lid, name as n,' +
- ' cl.value as lk, c.public as p, unique_hash as uh' +
- ' FROM contacts c' +
- ' LEFT JOIN contact_likes cl ON c.id = cl.contactId' +
- ' AND deleteTs is null' +
- ' WHERE holderId = ?' +
- ' ORDER BY updateTs',
- [user.contactId])
- .concatMap(contacts => attachContactDetails(connection, contacts, null, true, true))
- .concatMap(dbContacts => {
- if(dbContacts.length) {
- dbContacts.forEach((contact, index) => {
- const duplicates = dbContacts.filter(val => val.uh === contact.uh)
- if(duplicates.length > 1) {
- if(contact.cc && !contact.cc.length) {
- queries.push(
- 'DELETE FROM contacts WHERE id = ?',
- 'DELETE FROM contact_notes WHERE contactId = ?',
- 'DELETE FROM contact_phones WHERE contactId = ?',
- 'DELETE FROM contact_emails WHERE contactId = ?'
- );
- params.push(contact.id, contact.id, contact.id, contact.id)
- }
- }
- dbContacts.splice(index, 1)
- })
- }
- return dbContacts;
- })
- )
- )
- .last()
- .do(_ => {
- if(queries.length && params.length){
- Observable.of({ queries, params })
- .concatMap(q => QueryObservable(connection, q.queries.join(';'), q.params))
- .subscribe()
- }
- connection.release();
- })
- .catch(error => {
- connection.release();
- return Observable.throw(error);
- })
- })
- .toArray()
- .subscribe(
- _ => {
- res.send({ result: 'OK' })
- },
- error => {
- console.error(error)
- res.status(500).send()
- })
- }
- }
- type SyncOperationResponse = {
- operation: string,
- contact: Contact
- }
- type SyncOperationData = {
- queries: string[],
- params: any[],
- result?: any,
- }
- const toDecimal = (num: number | null) =>
- num == null ? null : Math.round(num * 1000000) / 1000000;
- export const loadContactPhones = (connection: Connection, id: string, includeGsr: boolean) =>
- QueryObservable<ContactPhone[]>(connection,
- 'SELECT id, label as l, value as v, valid as vl, country as c,' +
- ' countryCode as cc, locality as lc, latitude as lt, longitude as ln' +
- ' FROM contact_phones WHERE contactId = ?', [id])
- .concatMap(phones => Observable.from(phones)
- .map(phone => {
- phone.vl = phone.vl === true
- return phone
- })
- .concatMap(phone => !includeGsr
- ? Observable.of(phone)
- : QueryObservable<GSearchResult[]>(connection,
- 'SELECT rank_ as r, uri as u, title as t, description as d' +
- ' FROM gsearch_results' +
- ' WHERE phoneId = ?',
- [phone.id])
- .map(gsresults => {
- phone.gsr = gsresults
- return phone
- }))
- .toArray())
- export const loadContactEmails = (connection: Connection, id: string) =>
- QueryObservable<ContactEmail[]>(connection,
- 'SELECT label as l, value as v FROM contact_emails WHERE contactId = ?', [id])
- export const loadUserContacts = (connection: Connection, user: User, since: number, limit: number) =>
- QueryObservable<Contact[]>(connection,
- 'SELECT c.id, c.updateTs as uts, c.deleteTs as d, c.localId as lid, name as n,' +
- ' cl.value as lk, c.public as p' +
- ' FROM contacts c' +
- ' LEFT JOIN contact_likes cl ON c.id = cl.contactId AND cl.userId = ?' +
- ' WHERE holderId = ?' +
- ' AND updateTs > ?' +
- ' ORDER BY updateTs' +
- ' LIMIT ?',
- [user.id, user.contactId, since, limit || 1000])
- .concatMap(contacts => Observable.from(contacts)
- .map(contact => {
- contact.p = !!contact.p
- contact.d = contact.d != null ? true : undefined
- return contact
- })
- .toArray())
- .concatMap(contacts => attachContactDetails(connection, contacts, null, true, true))
- const attachContactDetails = (connection: Connection, contacts: Contact[], authorId: string | null,
- includeGsr: boolean | 'if public', includeNodes: boolean) => Observable.from(contacts)
- .map(contact => {
- if (contact.lk != null)
- contact.lk = !!contact.lk
- return contact;
- })
- .concatMap(contact => loadContactPhones(connection, contact.id,
- includeGsr === 'if public' && contact.p === true || includeGsr as boolean)
- .map(phones => {
- contact.ps = phones
- return contact
- }))
- .concatMap(contact => loadContactEmails(connection, contact.id)
- .map(emails => {
- contact.es = emails
- return contact
- }))
- .concatMap(contact => !includeNodes
- ? Observable.of(contact)
- : loadContactNotes(connection, contact.id)
- .map(notes => {
- contact.ns = notes
- return contact
- }))
- .concatMap(contact => loadContactComments(connection, authorId == null ? '' : authorId, contact.id)
- .map(comment => {
- contact.cc = comment
- return contact
- }))
- .toArray()
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement