report_record.go 19 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508
  1. package roadshow
  2. import (
  3. "fmt"
  4. "github.com/beego/beego/v2/client/orm"
  5. "hongze/hz_crm_api/utils"
  6. "strings"
  7. "time"
  8. )
  9. type RsReportRecord struct {
  10. RsReportRecordId int `orm:"column(rs_report_record_id);pk"`
  11. CompanyId int `description:"客户id"`
  12. CompanyName string `description:"客户名称"`
  13. CreditCode string `description:"社会统一信用码"`
  14. CompanyStatus string `description:"客户状态:'试用','永续','冻结','流失','正式','潜在'"`
  15. ResearcherId int `description:"研究员id"`
  16. ResearcherName string `description:"研究员名称"`
  17. ResearcherGroupId int `description:"研究员分组id"`
  18. ResearcherGroupName string `description:"研究员分组名称"`
  19. SellerId int `description:"所属销售id"`
  20. SellerName string `description:"所属销售名称"`
  21. SellerGroupId int `description:"销售分组id"`
  22. SellerGroupName string `description:"销售分组名称"`
  23. RsCalendarId int `description:"路演活动id"`
  24. RsCalendarResearcherId int `description:"路演研究员id"`
  25. StartDate string `description:"开始日期"`
  26. EndDate string `description:"结束日期"`
  27. StartTime string `description:"开始时间"`
  28. EndTime string `description:"结束时间"`
  29. StartWeek string `description:"开始日期对应周"`
  30. EndWeek string `description:"结束日期对应周"`
  31. CreateTime time.Time `description:"记录创建时间"`
  32. RsCalendarResearcherStatus int `description:"路演活动状态"`
  33. PermissionName string `description:"开通品种"`
  34. InteractionNum int `description:"当时用户总的互动量"`
  35. }
  36. // RsReportRecordResp 总统计信息
  37. type RsReportRecordResp struct {
  38. List []GroupReportRecord `description:"系统用户分组信息"`
  39. RsReportRecordNumList []RsReportRecordNum `description:"统计次数"`
  40. }
  41. // GroupReportRecord 分组统计信息
  42. type GroupReportRecord struct {
  43. Item []AdminReportRecord `description:"系统用户信息"`
  44. Name string `description:"分组名"`
  45. GruopId int `description:"分组id"`
  46. RsReportRecordNumList []RsReportRecordNum `description:"统计次数"`
  47. }
  48. // AdminReportRecord 系统用户统计信息
  49. type AdminReportRecord struct {
  50. Name string `description:"系统用户名"`
  51. AdminId int `description:"系统用户id"`
  52. RsReportRecordNumList []RsReportRecordNum `description:"统计次数"`
  53. }
  54. // RsReportRecordNum 系统用户统计信息
  55. type RsReportRecordNum struct {
  56. TryOutNum int `description:"试用路演次数"`
  57. FormalNum int `description:"正式路演次数"`
  58. MeetingNum int `description:"会议次数"`
  59. OnlineNum int `description:"线上路演次数"`
  60. OfflineNum int `description:"线上路演次数"`
  61. RoadShowNum int `description:"路演总次数"`
  62. SalonNum int `description:"沙龙路演次数"`
  63. AskNum int `description:"提交过问答的路演次数"`
  64. StartDate string `description:"开始日期"`
  65. EndDate string `description:"结束日期"`
  66. IsLook bool `description:"是否可以查看详情"`
  67. }
  68. func DeleteRsReportRecord(rsCalendarId, rsCalendarResearcherId int) (err error) {
  69. o := orm.NewOrm()
  70. sql := ` DELETE FROM rs_report_record WHERE rs_calendar_id=? `
  71. _, err = o.Raw(sql, rsCalendarId).Exec()
  72. return
  73. }
  74. type RsReportRecordGroup struct {
  75. AdminId int `description:"系统用户id"`
  76. GroupId int `description:"系统用户分组id"`
  77. AdminName string `description:"系统用户名称"`
  78. Num int `description:"汇总次数"`
  79. }
  80. // GetGroupResearcherRecordList 获取研究员分组数据
  81. func GetGroupResearcherRecordList(condition string, pars []interface{}) (list []*RsReportRecordGroup, err error) {
  82. o := orm.NewOrm()
  83. sql := ` SELECT researcher_id as admin_id,researcher_group_id as group_id,researcher_name admin_name,count(1) num
  84. FROM rs_report_record
  85. WHERE 1=1 and rs_calendar_researcher_status = 2
  86. `
  87. if condition != "" {
  88. sql += condition
  89. }
  90. sql += ` GROUP BY researcher_id`
  91. _, err = o.Raw(sql, pars).QueryRows(&list)
  92. return
  93. }
  94. // GetGroupSellerRecordList 获取销售分组数据
  95. func GetGroupSellerRecordList(condition string, pars []interface{}) (list []*RsReportRecordGroup, err error) {
  96. o := orm.NewOrm()
  97. sql := ` SELECT seller_id as admin_id,seller_group_id as group_id,seller_name admin_name,count(1) num
  98. FROM rs_report_record
  99. WHERE 1=1 and rs_calendar_researcher_status = 2
  100. `
  101. if condition != "" {
  102. sql += condition
  103. }
  104. sql += ` GROUP BY seller_id`
  105. _, err = o.Raw(sql, pars).QueryRows(&list)
  106. return
  107. }
  108. // RsReportRecordList 路演统计详情返回数据
  109. type RsReportRecordList struct {
  110. RsReportRecordId int `orm:"column(rs_report_record_id);pk"`
  111. CompanyId int `description:"客户id"`
  112. CompanyName string `description:"客户名称"`
  113. CreditCode string `description:"社会统一信用码"`
  114. CompanyStatus string `description:"客户状态:'试用','永续','冻结','流失','正式','潜在'"`
  115. ResearcherId int `description:"研究员id"`
  116. ResearcherName string `description:"研究员名称"`
  117. SellerId int `description:"所属销售id"`
  118. SellerName string `description:"所属销售名称"`
  119. RsCalendarId int `description:"路演活动id"`
  120. RsCalendarResearcherId int `description:"路演研究员id"`
  121. StartDate string `description:"开始日期"`
  122. EndDate string `description:"结束日期"`
  123. StartTime string `description:"开始时间"`
  124. EndTime string `description:"结束时间"`
  125. StartWeek string `description:"开始日期对应周"`
  126. EndWeek string `description:"结束日期对应周"`
  127. CreateTime time.Time `description:"记录创建时间"`
  128. RsCalendarResearcherStatus int `description:"路演活动状态"`
  129. ActivityType string `description:"活动类型"`
  130. RoadshowType string `description:"路演形式"`
  131. RoadshowPlatform string `description:"路演平台"`
  132. Theme string `description:"会议主题"`
  133. Province string `description:"省"`
  134. City string `description:"市"`
  135. CooperationName string `description:"合作方名称"`
  136. CompanyIndustry string `description:"客户行业"`
  137. CompanyClassify string `description:"客户分类"`
  138. QuestionStatus int `description:"问答状态:0-未填写;1-已填写"`
  139. QuestionMsgStatus int `description:"问答模板消息:0-未发送;1-已发送"`
  140. CompanyType string `description:"客户类型:FICC/权益"`
  141. }
  142. // GetRsReportRecordList 获取路演统计详情返回数据
  143. func GetRsReportRecordList(condition string, pars []interface{}) (list []*RsReportRecordList, err error) {
  144. o := orm.NewOrm()
  145. sql := ` SELECT a.*,b.theme,b.roadshow_platform,b.province,b.city,b.cooperation_name,c.company_industry,c.company_classify,c.question_status,c.question_msg_status
  146. FROM rs_report_record a
  147. join rs_calendar b on a.rs_calendar_id=b.rs_calendar_id
  148. inner join rs_calendar_researcher AS c ON a.rs_calendar_id=c.rs_calendar_id AND a.rs_calendar_researcher_id=c.rs_calendar_researcher_id
  149. WHERE 1=1 and a.rs_calendar_researcher_status = 2
  150. `
  151. if condition != "" {
  152. sql += condition
  153. }
  154. sql += ` order BY a.start_date asc,a.start_time asc`
  155. _, err = o.Raw(sql, pars).QueryRows(&list)
  156. return
  157. }
  158. type BusinessTripResp struct {
  159. GroupList []*BusinessTripGroup
  160. BaseDate string `description:"用于查询上下两周的开始日期"`
  161. }
  162. type BusinessTripGroup struct {
  163. GroupName string
  164. GroupId int
  165. ResearcherList []*BusinessTripResearcher
  166. }
  167. type BusinessTripResearcher struct {
  168. AdminId int `description:"研究员id"`
  169. RealName string `description:"研究员名称"`
  170. GroupId int `description:"分组id"`
  171. GroupName string `description:"分组名称"`
  172. RoleTypeCode string `description:"角色编码"`
  173. Province string `description:"省"`
  174. City string `description:"市"`
  175. BusinessTripList []*BusinessTrip
  176. }
  177. type BusinessTrip struct {
  178. WeekDate string `description:"开始日期"`
  179. City string `description:"城市"`
  180. Week string `description:"周"`
  181. WeekType string `description:"当前周:current,下一周:next"`
  182. }
  183. // GetResearcherV2 获取研究员列表(冻结的也要)
  184. func GetBusinessTripResearcher() (list []*BusinessTripResearcher, err error) {
  185. o := orm.NewOrm()
  186. sql := ` SELECT * FROM admin AS a
  187. WHERE a.role_type_code IN('researcher','rai_researcher','ficc_researcher','ficc_admin')
  188. AND a.enabled=1 and admin_id !=92 `
  189. _, err = o.Raw(sql).QueryRows(&list)
  190. return
  191. }
  192. type BusinessTripResearcherList struct {
  193. Province string `description:"省"`
  194. City string `description:"市"`
  195. StartDate string `description:"路演开始日期"`
  196. EndDate string `description:"路演结束日期"`
  197. ResearcherId int `description:"研究员id"`
  198. ResearcherName string `description:"研究员姓名"`
  199. StartDateTime string `description:"研究员姓名"`
  200. }
  201. func GetBusinessTripList(adminId int, startDate, endDate string) (list []*BusinessTripResearcherList, err error) {
  202. o := orm.NewOrm()
  203. sql := ` SELECT a.province,a.city,b.start_date,b.end_date,b.researcher_id,b.researcher_name,CONCAT(b.start_date,' ',b.start_time) AS start_date_time
  204. FROM rs_calendar AS a
  205. INNER JOIN rs_calendar_researcher AS b ON a.rs_calendar_id=b.rs_calendar_id
  206. INNER JOIN admin AS c ON b.researcher_id=c.admin_id
  207. WHERE a.roadshow_type='线下'
  208. AND b.status=2
  209. AND a.city<>c.city
  210. AND b.start_date>=?
  211. AND b.start_date<=? `
  212. if adminId > 0 {
  213. sql += ` AND b.researcher_id=? `
  214. //sql += ` GROUP BY b.researcher_id,b.start_date `
  215. sql += ` ORDER BY b.researcher_id ASC,start_date_time ASC `
  216. _, err = o.Raw(sql, startDate, endDate, adminId).QueryRows(&list)
  217. } else {
  218. //sql += ` GROUP BY b.researcher_id,b.start_date `
  219. sql += ` ORDER BY b.researcher_id ASC,start_date_time ASC `
  220. _, err = o.Raw(sql, startDate, endDate).QueryRows(&list)
  221. }
  222. return
  223. }
  224. // GetOverseasGroupSellerRecordList 获取销售分组数据
  225. func _GetOverseasGroupSellerRecordList(condition string, pars []interface{}) (list []*RsReportRecordGroup, err error) {
  226. o := orm.NewOrm()
  227. sql := ` SELECT seller_id as admin_id,seller_group_id as group_id,seller_name admin_name,count(1) num
  228. FROM rs_report_record
  229. WHERE 1=1 and rs_calendar_researcher_status = 2
  230. `
  231. if condition != "" {
  232. sql += condition
  233. }
  234. sql += ` GROUP BY seller_id`
  235. _, err = o.Raw(sql, pars).QueryRows(&list)
  236. return
  237. }
  238. // OverseasAdminReportRecord 系统用户统计信息
  239. type OverseasAdminReportRecord struct {
  240. Name string `description:"系统用户名"`
  241. AdminId int `description:"系统用户id"`
  242. RsReportRecordNumList []RsOverseasReportRecordNum `description:"统计次数"`
  243. }
  244. // RsOverseasReportRecordNum 系统用户统计信息
  245. type RsOverseasReportRecordNum struct {
  246. TryOutNum int `description:"试用路演次数"`
  247. FormalNum int `description:"正式路演次数"`
  248. CloseNum int `description:"关闭客户次数"`
  249. OnlineNum int `description:"线上路演次数"`
  250. OfflineNum int `description:"线上路演次数"`
  251. StartDate string `description:"开始日期"`
  252. EndDate string `description:"结束日期"`
  253. IsLook bool `description:"是否可以查看详情"`
  254. }
  255. // RsOverseasReportRecordResp 总统计信息
  256. type RsOverseasReportRecordResp struct {
  257. List []OverseasAdminReportRecord `description:"系统用户分组信息"`
  258. RsReportRecordNumList []RsOverseasReportRecordNum `description:"统计次数"`
  259. }
  260. func GetOverseasGroupResearcherRecordList(startDate, endDate, companyStatus string) (list []*RsReportRecordGroup, err error) {
  261. var databaseName string
  262. if utils.RunMode == "debug" {
  263. databaseName = "test_v2_hongze_rddp"
  264. } else {
  265. databaseName = "hongze_rddp"
  266. }
  267. companyStatusList := make([]string, 0)
  268. if companyStatus != "" {
  269. companyStatusList = strings.Split(companyStatus, ",")
  270. }
  271. lenCompanyStatusList := len(companyStatusList)
  272. o := orm.NewOrm()
  273. pars := make([]interface{}, 0)
  274. sql1 := `SELECT a.rs_calendar_id,b.researcher_id as admin_id,b.researcher_group_id as group_id,b.researcher_name AS admin_name FROM rs_calendar AS a
  275. INNER JOIN rs_report_record AS b ON a.rs_calendar_id=b.rs_calendar_id
  276. JOIN company_product AS c ON a.company_id=c.company_id AND c.product_id=1
  277. JOIN company AS d ON c.company_id=d.company_id
  278. INNER JOIN overseas_custom_seller AS f ON c.seller_id=f.seller_id
  279. where c.is_overseas = 0 and a.english_company= 0 AND a.source = 0 AND b.rs_calendar_researcher_status=2 AND b.researcher_id != 0 `
  280. if startDate != "" {
  281. sql1 += ` AND b.start_date >= ? `
  282. pars = append(pars, startDate)
  283. }
  284. if endDate != "" {
  285. sql1 += ` AND b.end_date <= ? `
  286. pars = append(pars, endDate)
  287. }
  288. if lenCompanyStatusList > 0 {
  289. sql1 += ` AND d.overseas_status in (` + utils.GetOrmInReplace(lenCompanyStatusList) + ") "
  290. pars = append(pars, companyStatusList)
  291. }
  292. //sql1 += ` GROUP BY admin_id `
  293. sql2 := fmt.Sprintf(`SELECT aa.rs_calendar_id,bb.researcher_id as admin_id,bb.researcher_group_id as group_id,bb.researcher_name AS admin_name FROM rs_calendar AS aa
  294. INNER JOIN rs_report_record AS bb ON aa.rs_calendar_id=bb.rs_calendar_id
  295. INNER JOIN %s.english_company AS cc ON aa.company_id=cc.company_id
  296. where aa.english_company= 1 AND aa.source = 0 AND cc.is_deleted=0 AND bb.rs_calendar_researcher_status=2 AND bb.researcher_id != 0 `, databaseName)
  297. if startDate != "" {
  298. sql2 += ` AND bb.start_date >= ? `
  299. pars = append(pars, startDate)
  300. }
  301. if endDate != "" {
  302. sql2 += ` AND bb.end_date <= ? `
  303. pars = append(pars, endDate)
  304. }
  305. if lenCompanyStatusList > 0 {
  306. sql2 += ` AND cc.overseas_status in (` + utils.GetOrmInReplace(lenCompanyStatusList) + ") "
  307. pars = append(pars, companyStatusList)
  308. }
  309. //sql2 += ` GROUP BY admin_id `
  310. // 列表数据
  311. sql := `SELECT m.*,count(1) as num FROM (` + sql1 + ` UNION ALL ` + sql2
  312. sql += ` ) AS m WHERE 1=1 `
  313. sql += ` Group by admin_id `
  314. _, err = o.Raw(sql, pars).QueryRows(&list)
  315. return
  316. }
  317. func GetOverseasGroupSellerRecordList(startDate, endDate, companyStatus string) (list []*RsReportRecordGroup, err error) {
  318. var databaseName string
  319. if utils.RunMode == "debug" {
  320. databaseName = "test_v2_hongze_rddp"
  321. } else {
  322. databaseName = "hongze_rddp"
  323. }
  324. companyStatusList := make([]string, 0)
  325. if companyStatus != "" {
  326. companyStatusList = strings.Split(companyStatus, ",")
  327. }
  328. lenCompanyStatusList := len(companyStatusList)
  329. o := orm.NewOrm()
  330. pars := make([]interface{}, 0)
  331. sql1 := `SELECT COUNT(DISTINCT a.rs_calendar_id) AS num,b.seller_id as admin_id,b.seller_group_id as group_id,b.seller_name AS admin_name FROM rs_calendar AS a
  332. INNER JOIN rs_report_record AS b ON a.rs_calendar_id=b.rs_calendar_id
  333. JOIN company_product AS c ON a.company_id=c.company_id AND c.product_id=1
  334. JOIN company AS d ON c.company_id=d.company_id
  335. INNER JOIN overseas_custom_seller AS f ON c.seller_id=f.seller_id
  336. where c.is_overseas = 0 and a.english_company= 0 AND a.source = 0 AND b.rs_calendar_researcher_status=2 AND b.researcher_id != 0`
  337. if startDate != "" {
  338. sql1 += ` AND b.start_date >= ? `
  339. pars = append(pars, startDate)
  340. }
  341. if endDate != "" {
  342. sql1 += ` AND b.end_date <= ? `
  343. pars = append(pars, endDate)
  344. }
  345. if lenCompanyStatusList > 0 {
  346. sql1 += ` AND d.overseas_status in (` + utils.GetOrmInReplace(lenCompanyStatusList) + ") "
  347. pars = append(pars, companyStatusList)
  348. }
  349. sql1 += ` GROUP BY admin_id `
  350. sql2 := fmt.Sprintf(`SELECT COUNT(DISTINCT aa.rs_calendar_id) AS num,bb.seller_id as admin_id,bb.seller_group_id as group_id,bb.seller_name AS admin_name FROM rs_calendar AS aa
  351. INNER JOIN rs_report_record AS bb ON aa.rs_calendar_id=bb.rs_calendar_id
  352. INNER JOIN %s.english_company AS cc ON aa.company_id=cc.company_id
  353. where aa.english_company= 1 AND aa.source = 0 AND cc.is_deleted=0 AND bb.rs_calendar_researcher_status=2 AND bb.researcher_id != 0 `, databaseName)
  354. if startDate != "" {
  355. sql2 += ` AND bb.start_date >= ? `
  356. pars = append(pars, startDate)
  357. }
  358. if endDate != "" {
  359. sql2 += ` AND bb.end_date <= ? `
  360. pars = append(pars, endDate)
  361. }
  362. if lenCompanyStatusList > 0 {
  363. sql2 += ` AND cc.overseas_status in (` + utils.GetOrmInReplace(lenCompanyStatusList) + ") "
  364. pars = append(pars, companyStatusList)
  365. }
  366. sql2 += ` GROUP BY admin_id `
  367. // 列表数据
  368. sql := `SELECT m.*,SUM(m.num) AS num FROM (` + sql1 + ` UNION ALL ` + sql2
  369. sql += ` ) AS m WHERE 1=1 `
  370. sql += ` Group by admin_id `
  371. _, err = o.Raw(sql, pars).QueryRows(&list)
  372. return
  373. }
  374. // 根据ID获取详情
  375. func GetRsReportRecordDetailByRsReportRecordId(rsReportRecordId int) (item *RsReportRecord, err error) {
  376. o := orm.NewOrm()
  377. sql := ` SELECT * FROM rs_report_record WHERE rs_report_record_id=? `
  378. err = o.Raw(sql, rsReportRecordId).QueryRow(&item)
  379. return
  380. }
  381. // UpdateRsReportRecordPermissionMulti 批量修改
  382. func UpdateRsReportRecordPermissionMulti(items []*RsReportRecord) (err error) {
  383. o, err := orm.NewOrm().Begin()
  384. if err != nil {
  385. return
  386. }
  387. defer func() {
  388. if err == nil {
  389. o.Commit()
  390. } else {
  391. o.Rollback()
  392. }
  393. }()
  394. //批量修改
  395. p, err := o.Raw("UPDATE rs_report_record SET permission_name = ? WHERE rs_report_record_id = ?").Prepare()
  396. if err != nil {
  397. return
  398. }
  399. defer func() {
  400. _ = p.Close() // 别忘记关闭 statement
  401. }()
  402. for _, v := range items {
  403. _, err = p.Exec(v.PermissionName, v.RsReportRecordId)
  404. if err != nil {
  405. return
  406. }
  407. }
  408. return
  409. }
  410. // UpdateRsReportRecordInteractionNumnMulti 批量修改互动数量
  411. func UpdateRsReportRecordInteractionNumnMulti(items []*RsReportRecord) (err error) {
  412. o, err := orm.NewOrm().Begin()
  413. if err != nil {
  414. return
  415. }
  416. defer func() {
  417. if err == nil {
  418. o.Commit()
  419. } else {
  420. o.Rollback()
  421. }
  422. }()
  423. //批量修改
  424. p, err := o.Raw("UPDATE rs_report_record SET interaction_num = ? WHERE rs_report_record_id = ?").Prepare()
  425. if err != nil {
  426. return
  427. }
  428. defer func() {
  429. _ = p.Close() // 别忘记关闭 statement
  430. }()
  431. for _, v := range items {
  432. _, err = p.Exec(v.InteractionNum, v.RsReportRecordId)
  433. if err != nil {
  434. return
  435. }
  436. }
  437. return
  438. }
  439. func GetReportResearcherExport(condition string, pars []interface{}) (list []*RsReportRecordList, err error) {
  440. o := orm.NewOrm()
  441. sql := ` SELECT a.*,b.theme,b.roadshow_platform,b.province,b.city,b.cooperation_name,c.company_industry,c.company_classify,c.question_status,c.question_msg_status
  442. FROM rs_report_record a
  443. join rs_calendar b on a.rs_calendar_id=b.rs_calendar_id
  444. inner join rs_calendar_researcher AS c ON a.rs_calendar_id=c.rs_calendar_id AND a.rs_calendar_researcher_id=c.rs_calendar_researcher_id
  445. WHERE 1=1 and a.rs_calendar_researcher_status = 2
  446. `
  447. if condition != "" {
  448. sql += condition
  449. }
  450. sql += ` order BY a.start_date DESC,a.start_time DESC`
  451. _, err = o.Raw(sql, pars).QueryRows(&list)
  452. return
  453. }