1 /*
2  *  Make.org Core API
3  *  Copyright (C) 2018 Make.org
4  *
5  * This program is free software: you can redistribute it and/or modify
6  *  it under the terms of the GNU Affero General Public License as
7  *  published by the Free Software Foundation, either version 3 of the
8  *  License, or (at your option) any later version.
9  *
10  *  This program is distributed in the hope that it will be useful,
11  *  but WITHOUT ANY WARRANTY; without even the implied warranty of
12  *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
13  *  GNU Affero General Public License for more details.
14  *
15  *  You should have received a copy of the GNU Affero General Public License
16  *  along with this program.  If not, see <https://www.gnu.org/licenses/>.
17  *
18  */
19 
20 package org.make.api.technical.crm
21 
22 import grizzled.slf4j.Logging
23 import org.make.api.extensions.MakeDBExecutionContextComponent
24 import org.make.api.technical.ShortenedNames
25 import scalikejdbc._
26 
27 import scala.concurrent.Future
28 import org.make.api.technical.DatabaseTransactions._
29 import org.make.core.reference.Country
30 
31 trait DefaultPersistentCrmUserServiceComponent extends PersistentCrmUserServiceComponent with ShortenedNames {
32   self: MakeDBExecutionContextComponent =>
33 
34   override lazy val persistentCrmUserService: DefaultPersistentCrmUserService = new DefaultPersistentCrmUserService
35 
36   class DefaultPersistentCrmUserService extends PersistentCrmUserService {
37 
38     private val PersistentCrmUser = PersistentCrmUserCompanion
39 
40     override def persist(users: Seq[PersistentCrmUser]): Future[Seq[PersistentCrmUser]] = {
41       implicit val cxt: EC = writeExecutionContext
42       Future(NamedDB("WRITE").retryableTx { implicit session =>
43         users.foreach {
44           user =>
45             withSQL {
46               insert
47                 .into(PersistentCrmUser)
48                 .namedValues(
49                   PersistentCrmUser.column.userId -> user.userId,
50                   PersistentCrmUser.column.fullName -> user.fullName,
51                   PersistentCrmUser.column.accountCreationDate -> user.accountCreationDate,
52                   PersistentCrmUser.column.accountCreationOperation -> user.accountCreationOperation,
53                   PersistentCrmUser.column.accountCreationOrigin -> user.accountCreationOrigin,
54                   PersistentCrmUser.column.accountCreationSource -> user.accountCreationSource,
55                   PersistentCrmUser.column.accountCreationLocation -> user.accountCreationLocation,
56                   PersistentCrmUser.column.dateOfBirth -> user.dateOfBirth,
57                   PersistentCrmUser.column.daysBeforeDeletion -> user.daysBeforeDeletion,
58                   PersistentCrmUser.column.daysOfActivity -> user.daysOfActivity,
59                   PersistentCrmUser.column.daysOfActivity30d -> user.daysOfActivity30d,
60                   PersistentCrmUser.column.email -> user.email,
61                   PersistentCrmUser.column.emailHardbounceStatus -> user.emailHardbounceStatus,
62                   PersistentCrmUser.column.emailValidationStatus -> user.emailValidationStatus,
63                   PersistentCrmUser.column.eventsCount -> user.eventsCount,
64                   PersistentCrmUser.column.favoriteCountry -> user.favoriteCountry,
65                   PersistentCrmUser.column.favoriteLanguage -> user.favoriteLanguage,
66                   PersistentCrmUser.column.firstContributionDate -> user.firstContributionDate,
67                   PersistentCrmUser.column.firstname -> user.firstname,
68                   PersistentCrmUser.column.lastActivityDate -> user.lastActivityDate,
69                   PersistentCrmUser.column.lastContributionDate -> user.lastContributionDate,
70                   PersistentCrmUser.column.operationActivity -> user.operationActivity,
71                   PersistentCrmUser.column.sessionsCount -> user.sessionsCount,
72                   PersistentCrmUser.column.sourceActivity -> user.sourceActivity,
73                   PersistentCrmUser.column.totalNumberProposals -> user.totalNumberProposals,
74                   PersistentCrmUser.column.totalNumberVotes -> user.totalNumberVotes,
75                   PersistentCrmUser.column.unsubscribeStatus -> user.unsubscribeStatus,
76                   PersistentCrmUser.column.userType -> user.userType,
77                   PersistentCrmUser.column.accountType -> user.accountType,
78                   PersistentCrmUser.column.zipcode -> user.zipcode
79                 )
80             }.execute()
81         }
82       }).map(_ => users)
83     }
84 
85     override def list(
86       maybeUnsubscribed: Option[Boolean],
87       hardBounce: Boolean,
88       offset: Int,
89       numberPerPage: Int
90     ): Future[Seq[PersistentCrmUser]] = {
91       implicit val cxt: EC = readExecutionContext
92       Future(NamedDB("READ").retryableTx { implicit session =>
93         withSQL {
94           select.all
95             .from(PersistentCrmUser.as(PersistentCrmUser.alias))
96             .where(
97               sqls
98                 .eq(PersistentCrmUser.alias.emailHardbounceStatus, hardBounce)
99                 .and(
100                   maybeUnsubscribed
101                     .map(unsubscribed => sqls.eq(PersistentCrmUser.alias.unsubscribeStatus, unsubscribed))
102                 )
103             )
104             .orderBy(PersistentCrmUser.alias.accountCreationDate.asc, PersistentCrmUser.alias.email.asc)
105             .limit(numberPerPage)
106             .offset(offset)
107         }.map(PersistentCrmUser.apply()).list()
108       })
109     }
110 
111     override def getActiveConsultationUsers(
112       consultationSlug: String,
113       country: Country
114     ): Future[List[PersistentCrmUser]] = {
115       implicit val cxt: EC = readExecutionContext
116       val predicate =
117         sqls
118           .eq(PersistentCrmUser.alias.emailValidationStatus, true)
119           .and(sqls.eq(PersistentCrmUser.alias.emailHardbounceStatus, false))
120           .and(sqls.eq(PersistentCrmUser.alias.unsubscribeStatus, false))
121           .and(sqls.like(PersistentCrmUser.alias.operationActivity, s"%$consultationSlug%"))
122           .and(sqls.eq(PersistentCrmUser.alias.favoriteCountry, country.value))
123 
124       Future(NamedDB("READ").retryableTx { implicit session =>
125         withSQL {
126           select
127             .from(PersistentCrmUser.as(PersistentCrmUser.alias))
128             .where(predicate)
129         }.map(PersistentCrmUser.apply()).list()
130       })
131     }
132 
133     override def getActiveConsultationUsersCount(consultationSlug: String): Future[Int] = {
134       implicit val cxt: EC = readExecutionContext
135       val predicate =
136         sqls
137           .eq(PersistentCrmUser.alias.emailValidationStatus, true)
138           .and(sqls.eq(PersistentCrmUser.alias.emailHardbounceStatus, false))
139           .and(sqls.eq(PersistentCrmUser.alias.unsubscribeStatus, false))
140           .and(sqls.like(PersistentCrmUser.alias.operationActivity, s"%$consultationSlug%"))
141 
142       Future(NamedDB("READ").retryableTx { implicit session =>
143         withSQL {
144           select(sqls.count)
145             .from(PersistentCrmUser.as(PersistentCrmUser.alias))
146             .where(predicate)
147         }.map(_.int(1)).single().getOrElse(0)
148       })
149     }
150 
151     override def findInactiveUsers(offset: Int, numberPerPage: Int): Future[Seq[PersistentCrmUser]] = {
152       implicit val cxt: EC = readExecutionContext
153       Future(NamedDB("READ").retryableTx { implicit session =>
154         withSQL {
155           select.all
156             .from(PersistentCrmUser.as(PersistentCrmUser.alias))
157             .where(sqls.le(PersistentCrmUser.alias.daysBeforeDeletion, 0))
158             .orderBy(PersistentCrmUser.alias.email.asc)
159             .limit(numberPerPage)
160             .offset(offset)
161         }.map(PersistentCrmUser.apply()).list()
162       })
163     }
164 
165     override def truncateCrmUsers(): Future[Unit] = {
166       implicit val cxt: EC = writeExecutionContext
167       Future(NamedDB("WRITE").retryableTx { implicit session =>
168         SQL(s"truncate table ${PersistentCrmUser.tableName}").execute()
169         ()
170       })
171     }
172   }
173 }
174 
175 object PersistentCrmUserCompanion extends SQLSyntaxSupport[PersistentCrmUser] with ShortenedNames with Logging {
176   override val columnNames: Seq[String] =
177     Seq(
178       "user_id",
179       "email",
180       "full_name",
181       "firstname",
182       "zipcode",
183       "date_of_birth",
184       "email_validation_status",
185       "email_hardbounce_status",
186       "unsubscribe_status",
187       "account_creation_date",
188       "account_creation_source",
189       "account_creation_origin",
190       "account_creation_operation",
191       "account_creation_location",
192       "favorite_country",
193       "favorite_language",
194       "total_number_proposals",
195       "total_number_votes",
196       "first_contribution_date",
197       "last_contribution_date",
198       "operation_activity",
199       "source_activity",
200       "days_of_activity",
201       "days_of_activity30d",
202       "user_type",
203       "account_type",
204       "days_before_deletion",
205       "last_activity_date",
206       "sessions_count",
207       "events_count"
208     )
209 
210   override val tableName: String = "crm_user"
211 
212   lazy val alias: SyntaxProvider[PersistentCrmUser] = syntax("crm_user")
213 
214   def apply(
215     resultName: ResultName[PersistentCrmUser] = alias.resultName
216   )(resultSet: WrappedResultSet): PersistentCrmUser = {
217     PersistentCrmUser(
218       userId = resultSet.string(resultName.userId),
219       email = resultSet.string(resultName.email),
220       fullName = resultSet.string(resultName.fullName),
221       firstname = resultSet.string(resultName.firstname),
222       zipcode = resultSet.stringOpt(resultName.zipcode),
223       dateOfBirth = resultSet.stringOpt(resultName.dateOfBirth),
224       emailValidationStatus = resultSet.boolean(resultName.emailValidationStatus),
225       emailHardbounceStatus = resultSet.boolean(resultName.emailHardbounceStatus),
226       unsubscribeStatus = resultSet.boolean(resultName.unsubscribeStatus),
227       accountCreationDate = resultSet.stringOpt(resultName.accountCreationDate),
228       accountCreationSource = resultSet.stringOpt(resultName.accountCreationSource),
229       accountCreationOrigin = resultSet.stringOpt(resultName.accountCreationOrigin),
230       accountCreationOperation = resultSet.stringOpt(resultName.accountCreationOperation),
231       accountCreationLocation = resultSet.stringOpt(resultName.accountCreationLocation),
232       favoriteCountry = resultSet.stringOpt(resultName.favoriteCountry),
233       favoriteLanguage = resultSet.stringOpt(resultName.favoriteLanguage),
234       totalNumberProposals = resultSet.intOpt(resultName.totalNumberProposals),
235       totalNumberVotes = resultSet.intOpt(resultName.totalNumberVotes),
236       firstContributionDate = resultSet.stringOpt(resultName.firstContributionDate),
237       lastContributionDate = resultSet.stringOpt(resultName.lastContributionDate),
238       operationActivity = resultSet.stringOpt(resultName.operationActivity),
239       sourceActivity = resultSet.stringOpt(resultName.sourceActivity),
240       daysOfActivity = resultSet.intOpt(resultName.daysOfActivity),
241       daysOfActivity30d = resultSet.intOpt(resultName.daysOfActivity30d),
242       userType = resultSet.stringOpt(resultName.userType),
243       accountType = resultSet.stringOpt(resultName.accountType),
244       daysBeforeDeletion = resultSet.intOpt(resultName.daysBeforeDeletion),
245       lastActivityDate = resultSet.stringOpt(resultName.lastActivityDate),
246       sessionsCount = resultSet.intOpt(resultName.sessionsCount),
247       eventsCount = resultSet.intOpt(resultName.eventsCount)
248     )
249   }
250 
251 }