target.go 50 KB

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192939495969798991001011021031041051061071081091101111121131141151161171181191201211221231241251261271281291301311321331341351361371381391401411421431441451461471481491501511521531541551561571581591601611621631641651661671681691701711721731741751761771781791801811821831841851861871881891901911921931941951961971981992002012022032042052062072082092102112122132142152162172182192202212222232242252262272282292302312322332342352362372382392402412422432442452462472482492502512522532542552562572582592602612622632642652662672682692702712722732742752762772782792802812822832842852862872882892902912922932942952962972982993003013023033043053063073083093103113123133143153163173183193203213223233243253263273283293303313323333343353363373383393403413423433443453463473483493503513523533543553563573583593603613623633643653663673683693703713723733743753763773783793803813823833843853863873883893903913923933943953963973983994004014024034044054064074084094104114124134144154164174184194204214224234244254264274284294304314324334344354364374384394404414424434444454464474484494504514524534544554564574584594604614624634644654664674684694704714724734744754764774784794804814824834844854864874884894904914924934944954964974984995005015025035045055065075085095105115125135145155165175185195205215225235245255265275285295305315325335345355365375385395405415425435445455465475485495505515525535545555565575585595605615625635645655665675685695705715725735745755765775785795805815825835845855865875885895905915925935945955965975985996006016026036046056066076086096106116126136146156166176186196206216226236246256266276286296306316326336346356366376386396406416426436446456466476486496506516526536546556566576586596606616626636646656666676686696706716726736746756766776786796806816826836846856866876886896906916926936946956966976986997007017027037047057067077087097107117127137147157167177187197207217227237247257267277287297307317327337347357367377387397407417427437447457467477487497507517527537547557567577587597607617627637647657667677687697707717727737747757767777787797807817827837847857867877887897907917927937947957967977987998008018028038048058068078088098108118128138148158168178188198208218228238248258268278288298308318328338348358368378388398408418428438448458468478488498508518528538548558568578588598608618628638648658668678688698708718728738748758768778788798808818828838848858868878888898908918928938948958968978988999009019029039049059069079089099109119129139149159169179189199209219229239249259269279289299309319329339349359369379389399409419429439449459469479489499509519529539549559569579589599609619629639649659669679689699709719729739749759769779789799809819829839849859869879889899909919929939949959969979989991000100110021003100410051006100710081009101010111012101310141015101610171018101910201021102210231024102510261027102810291030103110321033103410351036103710381039104010411042104310441045104610471048104910501051105210531054105510561057105810591060106110621063106410651066106710681069107010711072107310741075107610771078107910801081108210831084108510861087108810891090109110921093109410951096109710981099110011011102110311041105110611071108110911101111111211131114111511161117111811191120112111221123112411251126112711281129113011311132113311341135113611371138113911401141114211431144114511461147114811491150115111521153115411551156115711581159116011611162116311641165116611671168116911701171117211731174117511761177117811791180118111821183118411851186118711881189119011911192119311941195119611971198119912001201120212031204120512061207120812091210121112121213121412151216121712181219122012211222122312241225122612271228122912301231123212331234123512361237123812391240124112421243124412451246124712481249125012511252125312541255125612571258125912601261126212631264126512661267126812691270127112721273127412751276127712781279128012811282128312841285128612871288128912901291129212931294129512961297129812991300130113021303130413051306130713081309131013111312131313141315131613171318131913201321132213231324132513261327132813291330133113321333133413351336133713381339134013411342134313441345134613471348134913501351135213531354135513561357135813591360136113621363136413651366136713681369137013711372137313741375137613771378137913801381138213831384138513861387138813891390139113921393139413951396139713981399140014011402140314041405140614071408140914101411141214131414141514161417141814191420142114221423142414251426142714281429143014311432143314341435143614371438143914401441144214431444144514461447144814491450145114521453145414551456145714581459146014611462146314641465146614671468146914701471147214731474147514761477147814791480148114821483148414851486148714881489149014911492149314941495149614971498149915001501150215031504
  1. package models
  2. import (
  3. "eta/eta_mobile/models/data_manage"
  4. "eta/eta_mobile/utils"
  5. "fmt"
  6. "github.com/beego/beego/v2/client/orm"
  7. "github.com/rdlucklib/rdluck_tools/paging"
  8. "sort"
  9. "strconv"
  10. "strings"
  11. "time"
  12. )
  13. type DataList struct {
  14. TradeCode string `orm:"column(TRADE_CODE)" description:"指标编码"`
  15. SecName string `orm:"column(SEC_NAME)" description:"指标名称"`
  16. Unit string `orm:"column(UNIT)" description:"单位"`
  17. Remark string `orm:"column(REMARK)" description:"备注"`
  18. Frequency string `description:"频度"`
  19. ClassifyId int `description:"分类id"`
  20. ClassifyName string `description:"分类名称"`
  21. Dt string `orm:"column(DT)" description:"录入日期"`
  22. Close float64 `orm:"column(CLOSE)" description:"录入值"`
  23. ModifyTime string `description:"修改时间"`
  24. }
  25. type DataListResp struct {
  26. List []*DataList
  27. Paging *paging.PagingItem `description:"分页数据"`
  28. }
  29. func GetDataList(condition string, pars []interface{}, startSize, pageSize int) (items []*DataList, err error) {
  30. sql := `select a.TRADE_CODE,a.SEC_NAME,a.UNIT,a.frequency,a.classify_id,b.classify_name,c.DT,c.CLOSE,c.modify_time FROM edbdata AS c
  31. inner join edbinfo AS a ON a.TRADE_CODE=c.TRADE_CODE
  32. left join edbdata_classify AS b ON a.classify_id=b.classify_id
  33. where left(a.TRADE_CODE,1)='W' AND a.REMARK='手动' AND a.classify_id>0`
  34. if condition != "" {
  35. sql += condition
  36. }
  37. sql += ` order by c.DT desc limit ?,? `
  38. o := orm.NewOrmUsingDB("edb")
  39. _, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&items)
  40. return
  41. }
  42. func GetDataListCount(condition string, pars []interface{}) (count int, err error) {
  43. sql := ` select count(1) as count FROM edbdata AS c
  44. inner join edbinfo AS a ON a.TRADE_CODE=c.TRADE_CODE
  45. left join edbdata_classify AS b ON a.classify_id=b.classify_id
  46. where left(a.TRADE_CODE,1)='W' AND a.REMARK='手动' AND a.classify_id>0 `
  47. if condition != "" {
  48. sql += condition
  49. }
  50. o := orm.NewOrmUsingDB("edb")
  51. err = o.Raw(sql, pars).QueryRow(&count)
  52. return
  53. }
  54. type DataAddReq struct {
  55. TradeCode string `description:"指标唯一编码"`
  56. CreateDate string `description:"创建日期"`
  57. Close string `description:"录入值"`
  58. }
  59. type Edbdata struct {
  60. TradeCode string `orm:"column(TRADE_CODE);pk" description:"指标编码"`
  61. Dt string `orm:"column(DT)" description:"日期"`
  62. Close string `orm:"column(CLOSE)" description:"值"`
  63. ModifyTime time.Time `orm:"column(modify_time)" description:"修改时间"`
  64. }
  65. func GetDataInfo(tradeCode, creteDate string) (item *Edbdata, err error) {
  66. sql := " SELECT * FROM edbdata WHERE TRADE_CODE=? AND DT=? "
  67. o := orm.NewOrmUsingDB("edb")
  68. err = o.Raw(sql, tradeCode, creteDate).QueryRow(&item)
  69. return
  70. }
  71. func AddEdbdata(item *Edbdata) (lastId int64, err error) {
  72. o := orm.NewOrmUsingDB("edb")
  73. lastId, err = o.Insert(item)
  74. return
  75. }
  76. type DataEditReq struct {
  77. TradeCode string `description:"指标唯一编码"`
  78. CreateDate string `description:"创建日期"`
  79. Close interface{} `description:"录入值"`
  80. OldCreateDate string `description:"旧的录入日期"`
  81. }
  82. // BatchDataEditReq 批量修改指标
  83. type BatchDataEditReq struct {
  84. OldCreateDate string `description:"旧的录入日期"`
  85. CreateDate string `description:"新的录入日期"`
  86. List []DataEditReq `description:"需要修改的数据"`
  87. }
  88. // 编辑数据
  89. func EditEdbdata(item *Edbdata) (err error) {
  90. o := orm.NewOrmUsingDB("edb")
  91. sql := ` UPDATE edbdata SET CLOSE = ?,modify_time=NOW() WHERE TRADE_CODE = ? AND DT = ? `
  92. _, err = o.Raw(sql, item.Close, item.TradeCode, item.Dt).Exec()
  93. return
  94. }
  95. type EdbdataDeleteRecord struct {
  96. Id int `orm:"column(id);pk"`
  97. TradeCode string `orm:"column(TRADE_CODE)" description:"指标编码"`
  98. Dt string `orm:"column(DT)" description:"日期"`
  99. Close string `orm:"column(CLOSE)" description:"值"`
  100. ModifyTime time.Time `orm:"column(modify_time)" description:"修改时间"`
  101. CreateTime time.Time
  102. SysUserId int
  103. }
  104. func AddEdbdataDeleteRecord(item *EdbdataDeleteRecord) (lastId int64, err error) {
  105. o := orm.NewOrmUsingDB("edb")
  106. lastId, err = o.Insert(item)
  107. return
  108. }
  109. // DeleteEdbData 根据指标code和日期删除数据
  110. func DeleteEdbData(tradeCode, dt string) (err error) {
  111. o := orm.NewOrmUsingDB("edb")
  112. sql := ` DELETE FROM edbdata WHERE TRADE_CODE = ? AND DT = ? `
  113. _, err = o.Raw(sql, tradeCode, dt).Exec()
  114. return
  115. }
  116. // DeleteAllEdbData 根据指标code删除数据
  117. func DeleteAllEdbData(tradeCode string) (err error) {
  118. o := orm.NewOrmUsingDB("edb")
  119. sql := ` DELETE FROM edbdata WHERE TRADE_CODE = ? `
  120. _, err = o.Raw(sql, tradeCode).Exec()
  121. return
  122. }
  123. type Edbinfo struct {
  124. TradeCode string `orm:"column(TRADE_CODE);pk" description:"指标code"`
  125. SecName string `orm:"column(SEC_NAME);" description:"指标名称"`
  126. Unit string `orm:"column(UNIT);" description:"单位"`
  127. Remark string `orm:"column(REMARK);" description:"备注"`
  128. Frequency string `description:"频度"`
  129. ClassifyId int `description:"分类id"`
  130. ClassifyName string `description:"分类名称"`
  131. CreateDate string `description:"创建时间"`
  132. UserId int `description:"录入用户id"`
  133. UserName string `description:"录入用户名称"`
  134. NoticeTime string `description:"通知时间"`
  135. Mobile string `description:"录入者手机号"`
  136. }
  137. func GetEdbinfoListCount(condition string, pars []interface{}, mobile string, roleType int) (count int, err error) {
  138. o := orm.NewOrmUsingDB("edb")
  139. sql := ``
  140. if mobile != "" && roleType == 1 {
  141. sql = `SELECT COUNT(1) AS count FROM edbinfo AS a
  142. INNER JOIN edbinfo_user AS c ON a.TRADE_CODE=c.TRADE_CODE AND c.mobile=?
  143. WHERE LEFT(a.TRADE_CODE,1)='W' AND a.REMARK='手动' AND a.classify_id>0`
  144. if condition != "" {
  145. sql += condition
  146. }
  147. err = o.Raw(sql, mobile, pars).QueryRow(&count)
  148. } else {
  149. sql := `SELECT COUNT(1) AS count FROM edbinfo AS a WHERE LEFT(a.TRADE_CODE,1)='W' AND a.REMARK='手动' AND a.classify_id>0`
  150. if condition != "" {
  151. sql += condition
  152. }
  153. err = o.Raw(sql, pars).QueryRow(&count)
  154. }
  155. return
  156. }
  157. func GetEdbinfoList(condition string, pars []interface{}, startSize, pageSize int, mobile string, roleType int) (items []*Edbinfo, err error) {
  158. o := orm.NewOrmUsingDB("edb")
  159. sql := ``
  160. if mobile != "" && roleType == 1 {
  161. sql = ` SELECT DISTINCT a.*,b.classify_name FROM edbinfo AS a
  162. LEFT JOIN edbdata_classify AS b ON a.classify_id=b.classify_id
  163. INNER JOIN edbinfo_user AS c ON a.TRADE_CODE=c.TRADE_CODE AND c.mobile=?
  164. WHERE LEFT(a.TRADE_CODE,1)='W' AND a.REMARK='手动' AND a.classify_id>0`
  165. if condition != "" {
  166. sql += condition
  167. }
  168. sql += ` ORDER BY a.create_date DESC LIMIT ?,? `
  169. _, err = o.Raw(sql, mobile, pars, startSize, pageSize).QueryRows(&items)
  170. } else {
  171. sql = `SELECT DISTINCT a.*,b.classify_name FROM edbinfo AS a
  172. LEFT JOIN edbdata_classify AS b on a.classify_id=b.classify_id
  173. WHERE LEFT(a.TRADE_CODE,1)='W' AND a.REMARK='手动' AND a.classify_id>0`
  174. if condition != "" {
  175. sql += condition
  176. }
  177. sql += ` ORDER BY a.create_date DESC LIMIT ?,? `
  178. _, err = o.Raw(sql, pars, startSize, pageSize).QueryRows(&items)
  179. }
  180. return
  181. }
  182. // EdbParamsInfo 指标数据结构体
  183. type EdbParamsInfo struct {
  184. Unit string `orm:"column(UNIT);" description:"单位"`
  185. Frequency string `orm:"column(frequency);" description:"单位"`
  186. }
  187. // GetEdbUnitList 获取指标单位
  188. func GetEdbUnitList() (items []*EdbParamsInfo, err error) {
  189. o := orm.NewOrmUsingDB("edb")
  190. sql := `SELECT UNIT from edbinfo group by UNIT`
  191. _, err = o.Raw(sql).QueryRows(&items)
  192. return
  193. }
  194. // GetEdbFrequencyList 获取指标频度
  195. func GetEdbFrequencyList(classifyId, userId int) (items []*EdbParamsInfo, err error) {
  196. o := orm.NewOrmUsingDB("edb")
  197. sql := `SELECT frequency from edbinfo a
  198. join edbdata b on a.TRADE_CODE=b.TRADE_CODE
  199. where classify_id = ? `
  200. if userId > 0 {
  201. sql += ` and a.user_id = ` + fmt.Sprint(userId) + ` `
  202. }
  203. sql += ` group by a.frequency`
  204. _, err = o.Raw(sql, classifyId).QueryRows(&items)
  205. return
  206. }
  207. type TargetListResp struct {
  208. List []*Edbinfo
  209. Paging *paging.PagingItem `description:"分页数据"`
  210. }
  211. type EdbinfoAddReq struct {
  212. SecName string `description:"指标名称"`
  213. Unit string `description:"单位"`
  214. Frequency string `description:"频度"`
  215. ClassifyId int `description:"分类id"`
  216. NoticeTime string `description:"通知时间"`
  217. }
  218. // GetMaxTradeCode 获取指标最大trade_code
  219. func GetMaxTradeCode() (max_trade_code string, err error) {
  220. sql := " SELECT MAX(TRADE_CODE) AS max_trade_code FROM edbinfo WHERE LEFT(TRADE_CODE,1)='W' AND TRADE_CODE not like '%index%' and TRADE_CODE NOT LIKE 'WDC%'"
  221. o := orm.NewOrmUsingDB("edb")
  222. err = o.Raw(sql).QueryRow(&max_trade_code)
  223. if (err != nil && err.Error() == utils.ErrNoRow()) || max_trade_code == `` {
  224. max_trade_code = "W00"
  225. }
  226. return
  227. }
  228. func GetEdbinfoBySecName(secName string) (item *Edbinfo, err error) {
  229. sql := `SELECT * FROM edbinfo WHERE SEC_NAME=? AND left(TRADE_CODE,1)='W' AND REMARK='手动' `
  230. o := orm.NewOrmUsingDB("edb")
  231. err = o.Raw(sql, secName).QueryRow(&item)
  232. return
  233. }
  234. func GetEdbinfoByTradeCode(tradeCode string) (item *Edbinfo, err error) {
  235. sql := `SELECT * FROM edbinfo WHERE TRADE_CODE=? `
  236. o := orm.NewOrmUsingDB("edb")
  237. err = o.Raw(sql, tradeCode).QueryRow(&item)
  238. return
  239. }
  240. func AddEdbinfo(tradeCode, secName, unit, remark, frequency, noticeTime string, classifyId int, userId int) (err error) {
  241. sql := `INSERT INTO edbinfo(TRADE_CODE, SEC_NAME,UNIT, REMARK,frequency, classify_id,notice_time,user_id,create_date)
  242. VALUES(?,?,?,?,?,?,?,?,now()) `
  243. o := orm.NewOrmUsingDB("edb")
  244. _, err = o.Raw(sql, tradeCode, secName, unit, remark, frequency, classifyId, noticeTime, userId).Exec()
  245. return
  246. }
  247. func AddEdbinfoUser(tradeCode, mobile string) (err error) {
  248. o := orm.NewOrmUsingDB("edb")
  249. sql := `INSERT INTO edbinfo_user(TRADE_CODE, mobile) VALUES (?,?)`
  250. _, err = o.Raw(sql, tradeCode, mobile).Exec()
  251. return
  252. }
  253. type EdbinfoEditReq struct {
  254. TradeCode string `description:"指标code"`
  255. SecName string `description:"指标名称"`
  256. Unit string `description:"单位"`
  257. Frequency string `description:"频度"`
  258. ClassifyId int `description:"分类id"`
  259. NoticeTime string `description:"通知时间"`
  260. }
  261. func EditEdbinfo(tradeCode, secName, unit, frequency, noticeTime string, classifyId int) (err error) {
  262. sql := `UPDATE edbinfo SET SEC_NAME= ?, UNIT = ?,classify_id=?,frequency=?,notice_time=?,create_date=NOW() WHERE TRADE_CODE=? `
  263. o := orm.NewOrmUsingDB("edb")
  264. _, err = o.Raw(sql, secName, unit, classifyId, frequency, noticeTime, tradeCode).Exec()
  265. return
  266. }
  267. func SearchTargetEntry(classifyId int, keyWord string) (items []*Edbinfo, err error) {
  268. where := ""
  269. pars := make([]interface{}, 0)
  270. sql := `SELECT * FROM edbinfo WHERE LEFT(TRADE_CODE,1)='W' AND REMARK='手动' AND classify_id>0 AND classify_id=? `
  271. pars = append(pars, classifyId)
  272. if keyWord != "" {
  273. sql += `AND SEC_NAME LIKE ? `
  274. pars = utils.GetLikeKeywordPars(pars, keyWord, 1)
  275. }
  276. sql += where
  277. o := orm.NewOrmUsingDB("edb")
  278. _, err = o.Raw(sql, pars...).QueryRows(&items)
  279. return
  280. }
  281. type SearchTargetListResp struct {
  282. List []*Edbinfo
  283. }
  284. type EdbdataClassify struct {
  285. ClassifyId int
  286. ClassifyName string
  287. ParentId int
  288. EdbInfoTotal int
  289. }
  290. func GetEdbdataClassifyByClassifyName(classifyName string) (item *EdbdataClassify, err error) {
  291. sql := `SELECT * FROM edbdata_classify WHERE classify_name=? `
  292. o := orm.NewOrmUsingDB("edb")
  293. err = o.Raw(sql, classifyName).QueryRow(&item)
  294. return
  295. }
  296. type EdbdataClassifyList struct {
  297. ClassifyId int
  298. ClassifyName string
  299. ParentId int
  300. Child []*EdbdataClassify
  301. }
  302. func GetEdbdataClassify(userId int64) (items []*EdbdataClassifyList, err error) {
  303. var newItems []*EdbdataClassifyList
  304. o := orm.NewOrmUsingDB("edb")
  305. sql := ` SELECT classify_id,classify_name,parent_id FROM edbdata_classify WHERE parent_id=0 `
  306. _, err = o.Raw(sql).QueryRows(&newItems)
  307. if err != nil {
  308. return
  309. }
  310. classifyLen := len(newItems)
  311. for i := 0; i < classifyLen; i++ {
  312. var childItems []*EdbdataClassify
  313. parentId := newItems[i].ClassifyId
  314. childSql := ``
  315. if userId > 0 {
  316. userClassifyList, _ := GetManualUserClassify(int(userId))
  317. var userIdArr []string
  318. for _, v := range userClassifyList {
  319. userIdArr = append(userIdArr, strconv.Itoa(v.ClassifyId))
  320. }
  321. userIdStr := strings.Join(userIdArr, ",")
  322. if userIdStr != "" {
  323. childSql = "SELECT a.classify_id,a.classify_name,a.parent_id FROM edbdata_classify AS a WHERE a.is_show=1 and a.classify_id IN(" + userIdStr + ") AND parent_id=? ORDER BY a.create_time ASC "
  324. _, err = o.Raw(childSql, parentId).QueryRows(&childItems)
  325. }
  326. } else {
  327. childSql = "SELECT classify_id,classify_name,parent_id FROM edbdata_classify WHERE is_show=1 and parent_id=? ORDER BY create_time ASC "
  328. _, err = o.Raw(childSql, parentId).QueryRows(&childItems)
  329. }
  330. if err != nil {
  331. return
  332. }
  333. newItems[i].Child = childItems
  334. }
  335. for _, v := range newItems {
  336. childLen := len(v.Child)
  337. if childLen > 0 {
  338. items = append(items, v)
  339. }
  340. }
  341. return
  342. }
  343. type ManualUserClassify struct {
  344. ManualUserClassifyId int `orm:"column(manual_user_classify_id);pk"`
  345. AdminId int
  346. ClassifyId int
  347. CreateTime time.Time
  348. }
  349. func GetManualUserClassify(sysUserId int) (list []*ManualUserClassify, err error) {
  350. o := orm.NewOrmUsingDB("data")
  351. sql := `SELECT * FROM manual_user_classify WHERE admin_id=? `
  352. _, err = o.Raw(sql, sysUserId).QueryRows(&list)
  353. return
  354. }
  355. type EdbdataClassifyResp struct {
  356. List []*EdbdataClassifyList
  357. }
  358. func GetTargetBySecName(secName string) (item *Edbinfo, err error) {
  359. sql := `SELECT * FROM edbinfo WHERE SEC_NAME=? AND left(TRADE_CODE,1)='W' AND REMARK='手动' `
  360. o := orm.NewOrmUsingDB("edb")
  361. err = o.Raw(sql, secName).QueryRow(&item)
  362. return
  363. }
  364. // 更新指标数据信息
  365. func (edbinfo *Edbinfo) Update(cols []string) (err error) {
  366. o := orm.NewOrmUsingDB("edb")
  367. _, err = o.Update(edbinfo, cols...)
  368. return
  369. }
  370. func ModifyTargetClassifyId(tradeCode string, classifyId int) (err error) {
  371. sql := `UPDATE edbinfo SET classify_id=? WHERE TRADE_CODE=? `
  372. o := orm.NewOrmUsingDB("edb")
  373. _, err = o.Raw(sql, classifyId, tradeCode).Exec()
  374. return
  375. }
  376. func GetTargetsDataCount(tradeCode, dt string) (count int, err error) {
  377. sql := `SELECT COUNT(1) AS count FROM edbdata WHERE TRADE_CODE=? AND DT=? `
  378. o := orm.NewOrmUsingDB("edb")
  379. err = o.Raw(sql, tradeCode, dt).QueryRow(&count)
  380. return
  381. }
  382. // GetTargetsDataList 根据code获取指标数据列表
  383. func GetTargetsDataList(tradeCode string) (items []*Edbdata, err error) {
  384. o := orm.NewOrmUsingDB("edb")
  385. sql := `SELECT * FROM edbdata WHERE TRADE_CODE=? ORDER BY DT ASC `
  386. _, err = o.Raw(sql, tradeCode).QueryRows(&items)
  387. return
  388. }
  389. func GetTargetsData(tradeCode, dt string) (item *Edbdata, err error) {
  390. sql := `SELECT * FROM edbdata WHERE TRADE_CODE=? AND DT=? `
  391. o := orm.NewOrmUsingDB("edb")
  392. err = o.Raw(sql, tradeCode, dt).QueryRow(&item)
  393. return
  394. }
  395. func ModifyTargetsDataByImport(tradeCode, dt, close string) (err error) {
  396. sql := `UPDATE edbdata SET CLOSE=?,modify_time=NOW() WHERE TRADE_CODE=? AND DT=? `
  397. o := orm.NewOrmUsingDB("edb")
  398. _, err = o.Raw(sql, close, tradeCode, dt).Exec()
  399. return
  400. }
  401. func AddTargetsDataByImport(tradeCode, dt, close string) (err error) {
  402. sql := `INSERT INTO edbdata(TRADE_CODE, DT,CLOSE, modify_time)VALUES(?,?,?,NOW()) `
  403. o := orm.NewOrmUsingDB("edb")
  404. _, err = o.Raw(sql, tradeCode, dt, close).Exec()
  405. return
  406. }
  407. type EdbdataImportResp struct {
  408. Status int
  409. Msg string
  410. SuccessCount int
  411. FailCount int
  412. }
  413. func GetFailList(sysUserId int) (items []*EdbdataImportFail, err error) {
  414. o := orm.NewOrmUsingDB("edb")
  415. sql := ` SELECT * FROM edbdata_import_fail WHERE sys_user_id=? `
  416. _, err = o.Raw(sql, sysUserId).QueryRows(&items)
  417. return
  418. }
  419. type DataListForExport struct {
  420. TradeCode string `orm:"column(TRADE_CODE)" description:"指标code"`
  421. SecName string `orm:"column(SEC_NAME)" description:"指标名称"`
  422. Unit string `orm:"column(UNIT)" description:"单位"`
  423. Frequency string `description:"频度"`
  424. ClassifyId int `description:"分类id"`
  425. NoticeTime string `description:"通知时间"`
  426. ClassifyName string
  427. Dt string `orm:"column(DT)" description:"日期"`
  428. Close float64 `orm:"column(CLOSE)" description:"值"`
  429. }
  430. func GetDataListForExport(startDate, endDate, frequency, keyWord string, classifyId int) (items []*DataListForExport, err error) {
  431. where := ``
  432. var pars []interface{}
  433. if keyWord != "" {
  434. where = ` AND SEC_NAME LIKE ? `
  435. pars = utils.GetLikeKeywordPars(pars, keyWord, 1)
  436. }
  437. if startDate != "" {
  438. where += ` AND create_date>=? `
  439. pars = append(pars, startDate)
  440. }
  441. if endDate != "" {
  442. where += ` AND create_date<=? `
  443. pars = append(pars, endDate)
  444. }
  445. if frequency != "" {
  446. where += ` AND frequency=? `
  447. pars = append(pars, frequency)
  448. }
  449. if classifyId > 0 {
  450. where += ` AND classify_id=? `
  451. pars = append(pars, classifyId)
  452. }
  453. sql := ` SELECT a.TRADE_CODE,a.SEC_NAME,a.UNIT,a.frequency,a.classify_id,b.classify_name,c.DT,c.CLOSE FROM edbdata AS c
  454. INNER JOIN edbinfo AS a ON a.TRADE_CODE=c.TRADE_CODE
  455. LEFT JOIN edbdata_classify AS b ON a.classify_id=b.classify_id
  456. WHERE LEFT(a.TRADE_CODE,1)='W' AND a.REMARK='手动' AND a.classify_id>0 `
  457. if where != "" {
  458. sql += where
  459. }
  460. sql = sql + " ORDER BY c.DT DESC "
  461. o := orm.NewOrmUsingDB("edb")
  462. _, err = o.Raw(sql, pars).QueryRows(&items)
  463. return
  464. }
  465. type DataDeleteReq struct {
  466. TradeCode string `description:"指标唯一编码"`
  467. CreateDate string `description:"数据录入日期"`
  468. }
  469. func DataDelete(tradeCode, createDate, close string, modifyTime time.Time, sysUserId int) (err error) {
  470. o := orm.NewOrmUsingDB("edb")
  471. to, err := o.Begin()
  472. if err != nil {
  473. return
  474. }
  475. defer func() {
  476. if err != nil {
  477. _ = to.Rollback()
  478. } else {
  479. _ = to.Commit()
  480. }
  481. }()
  482. recordSql := ` INSERT INTO edbdata_delete_record(TRADE_CODE,DT,CLOSE,modify_time,create_time,sys_user_id)
  483. VALUES(?,?,?,?,?,?)`
  484. _, err = to.Raw(recordSql, tradeCode, createDate, close, modifyTime, time.Now(), sysUserId).Exec()
  485. sql := ` DELETE FROM edbdata WHERE TRADE_CODE = ? AND DT = ? `
  486. _, err = to.Raw(sql, tradeCode, createDate).Exec()
  487. return
  488. }
  489. func GetTargetInfoCount(tradeCode string) (count int, err error) {
  490. sql := ` SELECT COUNT(1) AS count FROM edbdata AS c
  491. INNER JOIN edbinfo AS a ON a.TRADE_CODE=c.TRADE_CODE
  492. WHERE a.TRADE_CODE=? `
  493. o := orm.NewOrmUsingDB("edb")
  494. err = o.Raw(sql, tradeCode).QueryRow(&count)
  495. return
  496. }
  497. type TargetDeleteReq struct {
  498. TradeCode string `description:"指标唯一编码"`
  499. }
  500. func TargetDelete(tradeCode string) (err error) {
  501. o := orm.NewOrmUsingDB("edb")
  502. to, err := o.Begin()
  503. if err != nil {
  504. return
  505. }
  506. defer func() {
  507. if err != nil {
  508. _ = to.Rollback()
  509. } else {
  510. _ = to.Commit()
  511. }
  512. }()
  513. sql := " DELETE FROM edbinfo WHERE TRADE_CODE = ? "
  514. _, err = to.Raw(sql, tradeCode).Exec()
  515. sql = " DELETE FROM edbdata WHERE TRADE_CODE = ? "
  516. _, err = to.Raw(sql, tradeCode).Exec()
  517. return
  518. }
  519. type Researcher struct {
  520. AdminId int `description:"系统用户id"`
  521. AdminName string `description:"系统用户名称"`
  522. RealName string `description:"系统用户姓名"`
  523. Role string `description:"系统用户角色"`
  524. Mobile string `description:"手机号"`
  525. TargetCount int `description:"指标数量"`
  526. }
  527. type ResearcherListResp struct {
  528. List []*Researcher
  529. }
  530. func GetResearcherEntry() (items []*Researcher, err error) {
  531. sql := ` SELECT admin_id,admin_name,real_name,mobile,0 as target_count FROM admin WHERE role_type=1 `
  532. o := orm.NewOrm()
  533. _, err = o.Raw(sql).QueryRows(&items)
  534. researchLen := len(items)
  535. edbO := orm.NewOrmUsingDB("edb")
  536. for i := 0; i < researchLen; i++ {
  537. var count int
  538. mobile := items[i].Mobile
  539. sqlCount := ` SELECT COUNT(DISTINCT a.TRADE_CODE) AS count FROM edbinfo_user AS a
  540. INNER JOIN edbinfo AS b ON a.TRADE_CODE=b.TRADE_CODE
  541. WHERE a.mobile=? AND LEFT(b.TRADE_CODE,1)='W' AND b.REMARK='手动' AND b.classify_id>0 `
  542. err = edbO.Raw(sqlCount, mobile).QueryRow(&count)
  543. items[i].TargetCount = count
  544. }
  545. return
  546. }
  547. func GetResearcherEntryByMobile(mobile string) (items []*Researcher, err error) {
  548. sql := ` SELECT admin_id,admin_name,real_name,mobile,0 as target_count FROM admin WHERE role_type=1 `
  549. if mobile != "" {
  550. sql += ` AND mobile IN(` + mobile + `)`
  551. }
  552. o := orm.NewOrm()
  553. _, err = o.Raw(sql).QueryRows(&items)
  554. researchLen := len(items)
  555. edbO := orm.NewOrmUsingDB("edb")
  556. for i := 0; i < researchLen; i++ {
  557. var count int
  558. mobile := items[i].Mobile
  559. sqlCount := ` SELECT COUNT(DISTINCT a.TRADE_CODE) AS count FROM edbinfo_user AS a
  560. INNER JOIN edbinfo AS b ON a.TRADE_CODE=b.TRADE_CODE
  561. WHERE a.mobile=? AND LEFT(b.TRADE_CODE,1)='W' AND b.REMARK='手动' AND b.classify_id>0 `
  562. err = edbO.Raw(sqlCount, mobile).QueryRow(&count)
  563. items[i].TargetCount = count
  564. }
  565. return
  566. }
  567. type EdbinfoItems struct {
  568. TradeCode string `orm:"column(TRADE_CODE);pk" description:"指标code"`
  569. SecName string `orm:"column(SEC_NAME);" description:"指标名称"`
  570. Unit string `orm:"column(UNIT);" description:"单位"`
  571. Remark string `orm:"column(REMARK);" description:"备注"`
  572. Frequency string `description:"频度"`
  573. ClassifyId int `description:"分类id"`
  574. ClassifyName string `description:"分类名称"`
  575. CreateDate string `description:"创建时间"`
  576. UserId int `description:"录入用户id"`
  577. NoticeTime string `description:"通知时间"`
  578. Mobile string `description:"录入者手机号"`
  579. ModifyDate string `description:"待更新日期"`
  580. Status string `description:"状态:未完成/完成"`
  581. }
  582. type TargetItemsResp struct {
  583. List SortEdbInfo
  584. }
  585. type SortEdbInfo []EdbinfoItems
  586. func GetTargetItems(mobile string, classifyId int) (lastItems SortEdbInfo, err error) {
  587. var items []*EdbinfoItems
  588. o := orm.NewOrmUsingDB("edb")
  589. //sql := ` SELECT *,'' modify_date,'' status FROM edbinfo AS a WHERE LEFT(a.TRADE_CODE,1)='W' AND a.REMARK='手动' AND a.classify_id>0 `
  590. sql := ` SELECT *,'' modify_date,'' STATUS FROM edbinfo AS a
  591. WHERE LEFT(a.TRADE_CODE,1)='W' AND a.REMARK='手动' AND a.classify_id>0
  592. `
  593. if classifyId > 0 {
  594. sql += ` AND a.classify_id=` + strconv.Itoa(classifyId) + ``
  595. }
  596. sql += ` GROUP BY a.TRADE_CODE `
  597. //if classifyId > 0 {
  598. // sql = ` SELECT *,'' modify_date,'' status FROM edbinfo AS a
  599. // WHERE a.classify_id=` + strconv.Itoa(classifyId) + ` AND LEFT(a.TRADE_CODE,1)='W' AND a.REMARK='手动' AND a.classify_id>0
  600. // GROUP BY a.TRADE_CODE `
  601. //}
  602. sql = sql + ` ORDER BY CONVERT(a.SEC_NAME USING gbk ) COLLATE gbk_chinese_ci ASC `
  603. _, err = o.Raw(sql).QueryRows(&items)
  604. if err != nil {
  605. return
  606. }
  607. itemsLen := len(items)
  608. nowWeek := time.Now().Weekday().String()
  609. fmt.Println(nowWeek)
  610. finishEdbInfo := SortEdbInfo{}
  611. unFinishEdbInfo := SortEdbInfo{}
  612. for i := 0; i < itemsLen; i++ {
  613. noticeTime := items[i].NoticeTime
  614. frequency := items[i].Frequency
  615. tradeCode := items[i].TradeCode
  616. if noticeTime != "" {
  617. if frequency == "周度" {
  618. noticeArr := strings.Split(noticeTime, " ")
  619. noticeWeek := noticeArr[0]
  620. fmt.Println(noticeWeek)
  621. addDay := 0
  622. if nowWeek == "Sunday" {
  623. if noticeWeek == "周日" {
  624. addDay = 0
  625. } else if noticeWeek == "周一" {
  626. addDay = 1
  627. } else if noticeWeek == "周二" {
  628. addDay = 2
  629. } else if noticeWeek == "周三" {
  630. addDay = 3
  631. } else if noticeWeek == "周四" {
  632. addDay = 4
  633. } else if noticeWeek == "周五" {
  634. addDay = 5
  635. } else if noticeWeek == "周六" {
  636. addDay = 6
  637. } else {
  638. addDay = 0
  639. }
  640. } else if nowWeek == "Monday" {
  641. if noticeWeek == "周日" {
  642. addDay = 6
  643. } else if noticeWeek == "周一" {
  644. addDay = 0
  645. } else if noticeWeek == "周二" {
  646. addDay = 1
  647. } else if noticeWeek == "周三" {
  648. addDay = 2
  649. } else if noticeWeek == "周四" {
  650. addDay = 3
  651. } else if noticeWeek == "周五" {
  652. addDay = 4
  653. } else if noticeWeek == "周六" {
  654. addDay = 5
  655. } else {
  656. addDay = 0
  657. }
  658. } else if nowWeek == "Tuesday" {
  659. if noticeWeek == "周日" {
  660. addDay = 5
  661. } else if noticeWeek == "周一" {
  662. addDay = 6
  663. } else if noticeWeek == "周二" {
  664. addDay = 0
  665. } else if noticeWeek == "周三" {
  666. addDay = 1
  667. } else if noticeWeek == "周四" {
  668. addDay = 2
  669. } else if noticeWeek == "周五" {
  670. addDay = 3
  671. } else if noticeWeek == "周六" {
  672. addDay = 4
  673. } else {
  674. addDay = 0
  675. }
  676. } else if nowWeek == "Wednesday" {
  677. if noticeWeek == "周日" {
  678. addDay = 4
  679. } else if noticeWeek == "周一" {
  680. addDay = 5
  681. } else if noticeWeek == "周二" {
  682. addDay = 6
  683. } else if noticeWeek == "周三" {
  684. addDay = 0
  685. } else if noticeWeek == "周四" {
  686. addDay = 1
  687. } else if noticeWeek == "周五" {
  688. addDay = 2
  689. } else if noticeWeek == "周六" {
  690. addDay = 3
  691. } else {
  692. addDay = 0
  693. }
  694. } else if nowWeek == "Thursday" {
  695. if noticeWeek == "周日" {
  696. addDay = 3
  697. } else if noticeWeek == "周一" {
  698. addDay = 4
  699. } else if noticeWeek == "周二" {
  700. addDay = 5
  701. } else if noticeWeek == "周三" {
  702. addDay = 6
  703. } else if noticeWeek == "周四" {
  704. addDay = 0
  705. } else if noticeWeek == "周五" {
  706. addDay = 1
  707. } else if noticeWeek == "周六" {
  708. addDay = 2
  709. } else {
  710. addDay = 0
  711. }
  712. } else if nowWeek == "Friday" {
  713. if noticeWeek == "周日" {
  714. addDay = 2
  715. } else if noticeWeek == "周一" {
  716. addDay = 3
  717. } else if noticeWeek == "周二" {
  718. addDay = 4
  719. } else if noticeWeek == "周三" {
  720. addDay = 5
  721. } else if noticeWeek == "周四" {
  722. addDay = 6
  723. } else if noticeWeek == "周五" {
  724. addDay = 0
  725. } else if noticeWeek == "周六" {
  726. addDay = 1
  727. } else {
  728. addDay = 0
  729. }
  730. } else if nowWeek == "Saturday" {
  731. if noticeWeek == "周日" {
  732. addDay = 1
  733. } else if noticeWeek == "周一" {
  734. addDay = 2
  735. } else if noticeWeek == "周二" {
  736. addDay = 3
  737. } else if noticeWeek == "周三" {
  738. addDay = 4
  739. } else if noticeWeek == "周四" {
  740. addDay = 5
  741. } else if noticeWeek == "周五" {
  742. addDay = 6
  743. } else if noticeWeek == "周六" {
  744. addDay = 0
  745. } else {
  746. addDay = 0
  747. }
  748. }
  749. modifyDate := time.Now().AddDate(0, 0, addDay)
  750. modifyDateStr := modifyDate.Format(utils.FormatDate)
  751. items[i].ModifyDate = modifyDateStr
  752. modifyDateEndStr := modifyDate.AddDate(0, 0, -7).Format(utils.FormatDate)
  753. fmt.Println("addDay:", addDay)
  754. fmt.Println("modifyDateEndStr:", modifyDateEndStr)
  755. count := 0
  756. sqlCount := ` SELECT COUNT(1) AS num FROM edbdata WHERE TRADE_CODE=? AND DT >= ? AND DT <= ? `
  757. err = o.Raw(sqlCount, tradeCode, modifyDateEndStr, modifyDateStr).QueryRow(&count)
  758. if err != nil {
  759. return nil, err
  760. }
  761. if count > 0 {
  762. items[i].Status = "完成"
  763. finishEdbInfo = append(finishEdbInfo, *items[i])
  764. } else {
  765. items[i].Status = "未完成"
  766. unFinishEdbInfo = append(unFinishEdbInfo, *items[i])
  767. }
  768. } else if frequency == "日度" {
  769. items[i].Status = "完成"
  770. finishEdbInfo = append(finishEdbInfo, *items[i])
  771. } else if frequency == "月度" {
  772. myYear := time.Now().Year()
  773. myMonth := time.Now().Format("01")
  774. startDate, endDate := utils.GetMonthStartAndEnd(strconv.Itoa(myYear), myMonth)
  775. count := 0
  776. sqlCount := ` SELECT COUNT(1) AS num FROM edbdata WHERE TRADE_CODE=? AND DT >= ? AND DT <= ? `
  777. err = o.Raw(sqlCount, tradeCode, startDate, endDate).QueryRow(&count)
  778. if err != nil {
  779. return nil, err
  780. }
  781. if noticeTime != "" {
  782. var modifyDateStr string
  783. strArr := strings.Split(noticeTime, "日")
  784. myYear := time.Now().Year()
  785. myMonth := time.Now().Format("01")
  786. modifyDateStr = strconv.Itoa(myYear) + "-" + myMonth + "-" + strArr[0]
  787. items[i].ModifyDate = modifyDateStr
  788. }
  789. if count > 0 {
  790. items[i].Status = "完成"
  791. finishEdbInfo = append(finishEdbInfo, *items[i])
  792. } else {
  793. items[i].Status = "未完成"
  794. unFinishEdbInfo = append(unFinishEdbInfo, *items[i])
  795. }
  796. } else {
  797. items[i].Status = "完成"
  798. finishEdbInfo = append(finishEdbInfo, *items[i])
  799. }
  800. } else {
  801. if frequency == "月度" {
  802. myYear := time.Now().Year()
  803. myMonth := time.Now().Format("01")
  804. startDate, endDate := utils.GetMonthStartAndEnd(strconv.Itoa(myYear), myMonth)
  805. count := 0
  806. sqlCount := ` SELECT COUNT(1) AS num FROM edbdata WHERE TRADE_CODE=? AND DT >= ? AND DT <= ? `
  807. err = o.Raw(sqlCount, tradeCode, startDate, endDate).QueryRow(&count)
  808. if err != nil {
  809. return nil, err
  810. }
  811. if count > 0 {
  812. items[i].Status = "完成"
  813. finishEdbInfo = append(finishEdbInfo, *items[i])
  814. } else {
  815. items[i].Status = "未完成"
  816. unFinishEdbInfo = append(unFinishEdbInfo, *items[i])
  817. }
  818. } else {
  819. items[i].Status = "完成"
  820. finishEdbInfo = append(finishEdbInfo, *items[i])
  821. }
  822. }
  823. }
  824. sort.Sort(SortByModifyDate{finishEdbInfo})
  825. sort.Sort(SortByModifyDate{unFinishEdbInfo})
  826. lastItems = append(lastItems, unFinishEdbInfo...)
  827. lastItems = append(lastItems, finishEdbInfo...)
  828. return
  829. }
  830. // 获取此 slice 的长度
  831. func (p SortEdbInfo) Len() int { return len(p) }
  832. // 根据元素的状态降序排序
  833. func (p SortEdbInfo) Less(i, j int) bool {
  834. return p[i].Status > p[j].Status
  835. }
  836. // 交换数据
  837. func (p SortEdbInfo) Swap(i, j int) { p[i], p[j] = p[j], p[i] }
  838. // 嵌套结构体 将继承 SortEdbInfo 的所有属性和方法
  839. // 所以相当于SortByName 也实现了 Len() 和 Swap() 方法
  840. type SortByStatus struct{ SortEdbInfo }
  841. // 根据元素的姓名长度降序排序 (此处按照自己的业务逻辑写)
  842. func (p SortByStatus) Less(i, j int) bool {
  843. return len(p.SortEdbInfo[i].Status) > len(p.SortEdbInfo[j].Status)
  844. }
  845. type SortByModifyDate struct{ SortEdbInfo }
  846. // 根据元素的年龄降序排序 (此处按照自己的业务逻辑写)
  847. func (p SortByModifyDate) Less(i, j int) bool {
  848. return p.SortEdbInfo[i].ModifyDate > p.SortEdbInfo[j].ModifyDate
  849. }
  850. type DataCheckResp struct {
  851. Status int `description:"状态:1:该日期已存在数据,是否确认修改?,0:数据不存在"`
  852. Close string `description:"值"`
  853. }
  854. type TargetCheckResp struct {
  855. Status int `description:"状态:1:该指标有关联数据,请先删除数据,0:指标不存在关联数据,可直接删除"`
  856. }
  857. type EdbdataExportList struct {
  858. TradeCode string `orm:"column(TRADE_CODE);" description:"指标code"`
  859. SecName string `orm:"column(SEC_NAME);" description:"指标名称"`
  860. Unit string `orm:"column(UNIT);" description:"单位"`
  861. Remark string `orm:"column(REMARK);" description:"备注"`
  862. Frequency string `description:"频度"`
  863. ClassifyId int `description:"分类id"`
  864. ClassifyName string `description:"分类名称"`
  865. CreateDate string `description:"创建时间"`
  866. Dt string `orm:"column(Dt);" description:"最新一次录入时间"`
  867. }
  868. func GetEdbdataSecName(condition string, pars []interface{}) (items []*EdbdataExportList, err error) {
  869. //sql := `SELECT a.TRADE_CODE,a.SEC_NAME,a.frequency,a.UNIT,MAX(c.DT) AS Dt
  870. // FROM edbdata AS c
  871. // INNER JOIN edbinfo AS a ON a.TRADE_CODE=c.TRADE_CODE
  872. // INNER JOIN edbinfo_user AS d ON a.TRADE_CODE=d.TRADE_CODE
  873. // LEFT JOIN edbdata_classify AS b ON a.classify_id=b.classify_id
  874. // WHERE LEFT(a.TRADE_CODE,1)='W' AND a.REMARK='手动' AND a.classify_id>0`
  875. sql := `SELECT a.TRADE_CODE,a.SEC_NAME,a.frequency,a.UNIT,MAX(c.DT) AS Dt,b.classify_name
  876. FROM edbdata AS c
  877. INNER JOIN edbinfo AS a ON a.TRADE_CODE=c.TRADE_CODE
  878. LEFT JOIN edbdata_classify AS b ON a.classify_id=b.classify_id
  879. WHERE LEFT(a.TRADE_CODE,1)='W' AND a.REMARK='手动' AND a.classify_id>0`
  880. if condition != "" {
  881. sql += condition
  882. }
  883. sql += " GROUP BY a.TRADE_CODE ORDER BY a.TRADE_CODE ASC "
  884. o := orm.NewOrmUsingDB("edb")
  885. _, err = o.Raw(sql, pars).QueryRows(&items)
  886. return
  887. }
  888. func GetEdbDataFrequency(classifyId int) (items []*string, err error) {
  889. sql := `SELECT DISTINCT frequency FROM edbinfo where classify_id=? AND frequency IS NOT NULL ORDER BY FIELD(frequency,'日度','周度','月度','季度','半年度','年度') `
  890. o := orm.NewOrmUsingDB("edb")
  891. _, err = o.Raw(sql, classifyId).QueryRows(&items)
  892. return
  893. }
  894. func GetEdbDataFrequencyByKeyord(keyword string) (items []*string, err error) {
  895. sql := `SELECT DISTINCT frequency FROM edbinfo where SEC_NAME=? ORDER BY FIELD(frequency,'日度','周度','月度','季度','半年度','年度') `
  896. o := orm.NewOrmUsingDB("edb")
  897. _, err = o.Raw(sql, keyword).QueryRows(&items)
  898. return
  899. }
  900. type EdbdataList struct {
  901. Dt string `orm:"column(DT);" description:"录入时间"`
  902. }
  903. func GetEdbdataList(tradeCode string) (items []*EdbdataList, err error) {
  904. sql := ` SELECT DT FROM edbdata WHERE TRADE_CODE IN(` + tradeCode + `) GROUP BY DT ORDER BY DT DESC `
  905. o := orm.NewOrmUsingDB("edb")
  906. _, err = o.Raw(sql).QueryRows(&items)
  907. return
  908. }
  909. type EdbdataItem struct {
  910. TradeCode string `orm:"column(TRADE_CODE);" description:"指标code"`
  911. Dt string `orm:"column(DT);" description:"最新一次录入时间"`
  912. Close float64 `orm:"column(CLOSE);" description:"值"`
  913. }
  914. func GetEdbdataValueByTradeCode(tradeCode, dt string) (item *EdbdataItem, err error) {
  915. sql := ` SELECT TRADE_CODE,DT,CLOSE FROM edbdata WHERE TRADE_CODE=? AND DT=? `
  916. o := orm.NewOrmUsingDB("edb")
  917. err = o.Raw(sql, tradeCode, dt).QueryRow(&item)
  918. return
  919. }
  920. func GetEdbdataAllByTradeCode(tradeCode string) (items []*EdbdataItem, err error) {
  921. sql := ` SELECT * FROM edbdata WHERE TRADE_CODE=? `
  922. o := orm.NewOrmUsingDB("edb")
  923. _, err = o.Raw(sql, tradeCode).QueryRows(&items)
  924. return
  925. }
  926. func GetEdbdataClassifyByParentId(parentId int) (items []*EdbdataClassify, err error) {
  927. sql := ` SELECT * FROM edbdata_classify WHERE parent_id=? `
  928. o := orm.NewOrmUsingDB("edb")
  929. _, err = o.Raw(sql, parentId).QueryRows(&items)
  930. return
  931. }
  932. type LzPriceClassify struct {
  933. ProductName string
  934. }
  935. func GetLzPriceClassify() (items []*LzPriceClassify, err error) {
  936. sql := ` SELECT product_name FROM longzhongpriceinfo GROUP BY product_name ORDER BY product_name DESC `
  937. o := orm.NewOrmUsingDB("edb")
  938. _, err = o.Raw(sql).QueryRows(&items)
  939. return
  940. }
  941. type Longzhongpriceinfo struct {
  942. LongzhongpriceinfoId int `orm:"column(longzhongpriceinfo_id);pk"`
  943. Standard string
  944. ModelName string
  945. Unit string
  946. AreaName string
  947. PriceType string
  948. Memo string
  949. PriceId string
  950. ProductName string
  951. InfoType string
  952. InfoTypeRemark string
  953. MarketName string
  954. ManufactureName string
  955. }
  956. func GetLongzhongpriceinfoByClassifyName(productName string) (items []*Longzhongpriceinfo, err error) {
  957. sql := `SELECT * FROM longzhongpriceinfo WHERE product_name=? ORDER BY longzhongpriceinfo_id ASC `
  958. o := orm.NewOrmUsingDB("edb")
  959. _, err = o.Raw(sql, productName).QueryRows(&items)
  960. return
  961. }
  962. func GetLongzhongPriceDataMaxCount(productName string) (count int, err error) {
  963. o := orm.NewOrmUsingDB("edb")
  964. sql := `SELECT MAX(t.num) AS count FROM (
  965. SELECT COUNT(1) AS num FROM longzhongpriceinfo AS a
  966. INNER JOIN longzhongpricedata AS b ON a.longzhongpriceinfo_id=b.longzhongpriceinfo_id
  967. WHERE a.product_name=?
  968. GROUP BY a.product_name
  969. )AS t `
  970. err = o.Raw(sql, productName).QueryRow(&count)
  971. return
  972. }
  973. type LongzhongpricedataItems struct {
  974. LongzhongpricedataId int `orm:"column(longzhongpricedata_id);pk"`
  975. LongzhongpriceinfoId int
  976. PriceDate string
  977. Memo string
  978. Price float64
  979. CnyPrice float64
  980. ZsyPrice float64
  981. ZshPrice float64
  982. LowPrice float64
  983. HighPrice float64
  984. RisePrice float64
  985. TonPrice float64
  986. PriceType string
  987. UpdateDate string
  988. }
  989. func GetLongzhongPriceDataById(lzPriceInfoId int) (items []*LongzhongpricedataItems, err error) {
  990. o := orm.NewOrmUsingDB("edb")
  991. sql := ` SELECT DISTINCT a.longzhongpriceinfo_id,a.price_date,a.memo,a.price,a.cny_price,a.zsy_price,a.zsh_price,a.low_price,a.high_price,a.rise_price,a.ton_price,a.price_type,a.update_date
  992. FROM longzhongpricedata AS a
  993. WHERE longzhongpriceinfo_id=? ORDER BY price_date DESC `
  994. _, err = o.Raw(sql, lzPriceInfoId).QueryRows(&items)
  995. return
  996. }
  997. func GetLzSurveyClassify() (items []*LzPriceClassify, err error) {
  998. sql := ` SELECT breed_name AS product_name FROM longzhong_survey_product GROUP BY breed_name ORDER BY breed_name DESC `
  999. o := orm.NewOrmUsingDB("edb")
  1000. _, err = o.Raw(sql).QueryRows(&items)
  1001. return
  1002. }
  1003. type LongzhongSurveyProduct struct {
  1004. SurveyProductId int `orm:"column(survey_product_id);pk"`
  1005. ProjectQuotaId int64
  1006. BreedId string
  1007. BreedName string
  1008. QuotaId string
  1009. QuotaName string
  1010. UnitId string
  1011. UnitName string
  1012. SampleType int64
  1013. SampleId string
  1014. SampleName string
  1015. DeviceId string
  1016. Device string
  1017. ProductCraftId string
  1018. ProductCraft string
  1019. ProductLine string
  1020. InputMode int64
  1021. Frequency int64
  1022. InputValue string
  1023. TaskShouldFinishTime int
  1024. CustomId string
  1025. CustomType int64
  1026. Custom string
  1027. QuotaSampleId int64
  1028. StartDate string
  1029. EndDate string
  1030. LzCode string
  1031. }
  1032. func GetLongzhongSurveyProductByClassifyName(productName string) (items []*LongzhongSurveyProduct, err error) {
  1033. sql := `SELECT * FROM longzhong_survey_product WHERE breed_name=? ORDER BY survey_product_id ASC `
  1034. o := orm.NewOrmUsingDB("edb")
  1035. _, err = o.Raw(sql, productName).QueryRows(&items)
  1036. return
  1037. }
  1038. func GetLzSurveyProductByNameAndFrequency(productName string, frequency int) (items []*LongzhongSurveyProduct, err error) {
  1039. sql := `SELECT * FROM longzhong_survey_product WHERE breed_name=? AND frequency=? ORDER BY survey_product_id ASC `
  1040. o := orm.NewOrmUsingDB("edb")
  1041. _, err = o.Raw(sql, productName, frequency).QueryRows(&items)
  1042. return
  1043. }
  1044. func GetExportLzSurveyProductByBreedIds(breedIds []string) (items []*LongzhongSurveyProduct, err error) {
  1045. if len(breedIds) == 0 {
  1046. return
  1047. }
  1048. field := ` survey_product_id, breed_id, breed_name, sample_name, custom, quota_name, lz_code, frequency, unit_name, end_date, input_value `
  1049. sql := `SELECT ` + field + ` FROM longzhong_survey_product WHERE breed_id IN (` + utils.GetOrmInReplace(len(breedIds)) + `) ORDER BY breed_id ASC, frequency ASC, survey_product_id ASC `
  1050. o := orm.NewOrmUsingDB("edb")
  1051. _, err = o.Raw(sql, breedIds).QueryRows(&items)
  1052. return
  1053. }
  1054. func GetLzFrequency(productName string) (items []*int, err error) {
  1055. sql := `SELECT DISTINCT frequency FROM longzhong_survey_product WHERE breed_name=? ORDER BY frequency`
  1056. o := orm.NewOrmUsingDB("edb")
  1057. _, err = o.Raw(sql, productName).QueryRows(&items)
  1058. return
  1059. }
  1060. // EdbInfoItem
  1061. type EdbInfoItem struct {
  1062. TradeCode string `orm:"column(TRADE_CODE);pk" description:"指标code"`
  1063. SecName string `orm:"column(SEC_NAME);" description:"指标名称"`
  1064. Unit string `orm:"column(UNIT);" description:"单位"`
  1065. Remark string `orm:"column(REMARK);" description:"备注"`
  1066. Frequency string `description:"频度"`
  1067. ClassifyId int `description:"分类id"`
  1068. ClassifyName string `description:"分类名称"`
  1069. CreateDate string `description:"创建时间"`
  1070. UserId int `description:"录入用户id"`
  1071. NoticeTime string `description:"通知时间"`
  1072. Mobile string `description:"录入者手机号"`
  1073. ModifyDate string `description:"待更新日期"`
  1074. Status string `description:"状态:未完成/完成"`
  1075. DataList []*Edbdata `description:"指标数据列表"`
  1076. }
  1077. // GetTargetItemList 获取指标列表数据
  1078. func GetTargetItemList(classifyId, edbShowType int, frequency, keyword, tradeCode string, classifyIdStrList []string) (items []*EdbInfoItem, err error) {
  1079. o := orm.NewOrmUsingDB("edb")
  1080. pars := make([]interface{}, 0)
  1081. sql := ` SELECT a.*,'' modify_date,'' STATUS FROM edbinfo AS a `
  1082. if edbShowType != 0 {
  1083. sql = ` SELECT a.*,b.DT,'' modify_date,'' STATUS FROM edbinfo AS a
  1084. left join edbdata b on a.TRADE_CODE=b.TRADE_CODE `
  1085. }
  1086. sql += ` WHERE LEFT(a.TRADE_CODE,1)='W' AND a.REMARK='手动' AND a.classify_id>0 `
  1087. //如果没有分类id集合列表,那么就没有数据了,不用往下执行了,直接返回好了
  1088. if len(classifyIdStrList) <= 0 {
  1089. return
  1090. }
  1091. if len(classifyIdStrList) > 0 {
  1092. sql += ` AND a.classify_id in (` + strings.Join(classifyIdStrList, ",") + `) `
  1093. }
  1094. if classifyId > 0 {
  1095. sql += ` AND a.classify_id=` + strconv.Itoa(classifyId) + ` `
  1096. }
  1097. //频度
  1098. if frequency != "" {
  1099. sql += ` AND a.frequency="` + frequency + `" `
  1100. }
  1101. //关键字
  1102. if keyword != "" {
  1103. sql += ` AND (a.SEC_NAME like ? or a.TRADE_CODE like ? )`
  1104. pars = utils.GetLikeKeywordPars(pars, keyword, 2)
  1105. }
  1106. //指定指标
  1107. if tradeCode != "" {
  1108. sql += ` AND a.TRADE_CODE = "` + tradeCode + `" `
  1109. }
  1110. //指标里面是否有数据
  1111. switch edbShowType {
  1112. case 1:
  1113. sql += ` AND b.CLOSE is not null `
  1114. case 2:
  1115. sql += ` AND b.CLOSE is null `
  1116. }
  1117. sql += ` GROUP BY a.TRADE_CODE `
  1118. sql = sql + ` ORDER BY CONVERT(a.SEC_NAME USING gbk ) COLLATE gbk_chinese_ci ASC `
  1119. _, err = o.Raw(sql, pars).QueryRows(&items)
  1120. return
  1121. }
  1122. // GetLzItemList 模糊查询隆众数据库指标列表
  1123. func GetLzItemList(keyword string) (items []*data_manage.LongzhongSurveyProduct, err error) {
  1124. o := orm.NewOrmUsingDB("edb")
  1125. sql := "SELECT * FROM longzhong_survey_product WHERE CONCAT(sample_name,breed_name,custom,quota_name,lz_code) LIKE ?"
  1126. _, err = o.Raw(sql, utils.GetLikeKeyword(keyword)).QueryRows(&items)
  1127. return
  1128. }
  1129. type lzSurveyData struct {
  1130. DataTime string `orm:"column(data_time)" description:"日期"`
  1131. InputValue string `orm:"column(input_value)" description:"值"`
  1132. }
  1133. // GetLzItemListByCode 根据code查询隆众数据列表
  1134. func GetLzItemListByCode(lzCode string) (items []*lzSurveyData, err error) {
  1135. o := orm.NewOrmUsingDB("edb")
  1136. sql := "SELECT * FROM longzhong_survey_data WHERE survey_product_id=? GROUP BY data_time DESC"
  1137. _, err = o.Raw(sql, lzCode).QueryRows(&items)
  1138. return
  1139. }
  1140. // GetEdbDataListByCodes 通过指标ID获取所有数据
  1141. func GetEdbDataListByCodes(tradeCode string) (items []*Edbdata, err error) {
  1142. sql := ` SELECT TRADE_CODE,DT,round(CLOSE,4) CLOSE,modify_time FROM edbdata WHERE TRADE_CODE IN(` + tradeCode + `) GROUP BY TRADE_CODE,DT ORDER BY DT DESC `
  1143. o := orm.NewOrmUsingDB("edb")
  1144. _, err = o.Raw(sql).QueryRows(&items)
  1145. return
  1146. }
  1147. // TargetItemListResp 指标数据结构体
  1148. type TargetItemListResp struct {
  1149. List []*EdbInfoItem
  1150. FrequencyList []string
  1151. }
  1152. // BatchDataDeleteReq 批量删除某日的指标数据请求结构体
  1153. type BatchDataDeleteReq struct {
  1154. CreateDate string `description:"创建日期"`
  1155. TradeCodeList []string `description:"指标唯一编码列表"`
  1156. }
  1157. // BatchDeleteEdbDataByDate 批量删除某日的指标数据
  1158. func BatchDeleteEdbDataByDate(tradeCodes, dt string, opUserId int) (err error) {
  1159. o := orm.NewOrmUsingDB("edb")
  1160. var list []*Edbdata
  1161. sql := ` select * FROM edbdata WHERE TRADE_CODE in (` + tradeCodes + `) AND DT = ? `
  1162. _, err = o.Raw(sql, dt).QueryRows(&list)
  1163. if err != nil {
  1164. return
  1165. }
  1166. deleteRecordList := make([]*EdbdataDeleteRecord, 0)
  1167. for _, edbDataInfo := range list {
  1168. deleteRecord := &EdbdataDeleteRecord{
  1169. TradeCode: edbDataInfo.TradeCode,
  1170. Dt: edbDataInfo.Dt,
  1171. Close: edbDataInfo.Close,
  1172. ModifyTime: time.Now(),
  1173. CreateTime: time.Now(),
  1174. SysUserId: opUserId,
  1175. }
  1176. deleteRecordList = append(deleteRecordList, deleteRecord)
  1177. }
  1178. if len(deleteRecordList) > 0 {
  1179. _, tmpErr := o.InsertMulti(len(deleteRecordList), deleteRecordList)
  1180. if tmpErr != nil {
  1181. err = tmpErr
  1182. return
  1183. }
  1184. }
  1185. sql = ` DELETE FROM edbdata WHERE TRADE_CODE in (` + tradeCodes + `) AND DT = ? `
  1186. _, err = o.Raw(sql, dt).Exec()
  1187. return
  1188. }
  1189. // BatchDeleteEdbData 批量删除指标数据
  1190. func BatchDeleteEdbData(tradeCode string, opUserId int) (err error) {
  1191. o := orm.NewOrmUsingDB("edb")
  1192. var list []*Edbdata
  1193. sql := ` select * FROM edbdata WHERE TRADE_CODE = ? `
  1194. _, err = o.Raw(sql, tradeCode).QueryRows(&list)
  1195. if err != nil {
  1196. return
  1197. }
  1198. deleteRecordList := make([]*EdbdataDeleteRecord, 0)
  1199. for _, edbDataInfo := range list {
  1200. deleteRecord := &EdbdataDeleteRecord{
  1201. TradeCode: edbDataInfo.TradeCode,
  1202. Dt: edbDataInfo.Dt,
  1203. Close: edbDataInfo.Close,
  1204. ModifyTime: time.Now(),
  1205. CreateTime: time.Now(),
  1206. SysUserId: opUserId,
  1207. }
  1208. deleteRecordList = append(deleteRecordList, deleteRecord)
  1209. }
  1210. _, err = o.InsertMulti(len(deleteRecordList), deleteRecordList)
  1211. if err != nil {
  1212. return
  1213. }
  1214. sql = ` DELETE FROM edbdata WHERE TRADE_CODE = ? `
  1215. _, err = o.Raw(sql, tradeCode).Exec()
  1216. return
  1217. }
  1218. // GetEdbInfoCountByClassifyId 根据指标分类id获取当前分类下的指标数量
  1219. func GetEdbInfoCountByClassifyId(classifyId int) (count int, err error) {
  1220. o := orm.NewOrmUsingDB("edb")
  1221. sql := `SELECT COUNT(1) AS count FROM ( SELECT a.*,b.CLOSE FROM edbinfo AS a
  1222. INNER JOIN edbdata AS b ON a.TRADE_CODE=b.TRADE_CODE
  1223. WHERE LEFT(a.TRADE_CODE,1)='W' AND a.REMARK='手动' AND a.classify_id=? group by a.TRADE_CODE) d `
  1224. err = o.Raw(sql, classifyId).QueryRow(&count)
  1225. return
  1226. }
  1227. // EdbInfoGroupCount 指标分类id获取当前分类下的指标数量
  1228. type EdbInfoGroupCount struct {
  1229. Count int
  1230. ClassifyId int
  1231. }
  1232. // GetEdbInfoGroupCountByClassifyIds 根据指标分类id获取当前分类下的指标数量
  1233. func GetEdbInfoGroupCountByClassifyIds(classifyIds string) (list []*EdbInfoGroupCount, err error) {
  1234. o := orm.NewOrmUsingDB("edb")
  1235. sql := `SELECT COUNT(1) AS count,classify_id FROM ( SELECT a.*,b.CLOSE FROM edbinfo AS a
  1236. INNER JOIN edbdata AS b ON a.TRADE_CODE=b.TRADE_CODE
  1237. WHERE LEFT(a.TRADE_CODE,1)='W' AND a.REMARK='手动' and a.classify_id in (` + classifyIds + `) group by a.TRADE_CODE) d
  1238. GROUP BY classify_id `
  1239. _, err = o.Raw(sql).QueryRows(&list)
  1240. return
  1241. }
  1242. // GetExcelData 获取excel样式数据
  1243. func GetExcelData() (list []*data_manage.ExcelStyle, err error) {
  1244. o := orm.NewOrmUsingDB("edb")
  1245. sql := `SELECT * FROM excel_style `
  1246. _, err = o.Raw(sql).QueryRows(&list)
  1247. return
  1248. }
  1249. // AddExcelData 添加excel样式数据
  1250. func AddExcelData(item *data_manage.ExcelStyle) (id int64, err error) {
  1251. o := orm.NewOrmUsingDB("edb")
  1252. id, err = o.Insert(item)
  1253. return
  1254. }
  1255. type EdbdataFloat struct {
  1256. TradeCode string `orm:"column(TRADE_CODE);pk" description:"指标编码"`
  1257. Dt string `orm:"column(DT)" description:"日期"`
  1258. Close float64 `orm:"column(CLOSE)" description:"值"`
  1259. ModifyTime time.Time `orm:"column(modify_time)" description:"修改时间"`
  1260. }
  1261. func GetTargetsDataFloat(tradeCode, dt string) (item *EdbdataFloat, err error) {
  1262. sql := `SELECT * FROM edbdata WHERE TRADE_CODE=? AND DT=? `
  1263. o := orm.NewOrmUsingDB("edb")
  1264. err = o.Raw(sql, tradeCode, dt).QueryRow(&item)
  1265. return
  1266. }
  1267. func ModifyEdbinfo(tradeCode, unit, frequency string, classifyId int) (err error) {
  1268. sql := `UPDATE edbinfo SET UNIT = ?,frequency=?, classify_id=?, create_date=NOW() WHERE TRADE_CODE=? `
  1269. o := orm.NewOrmUsingDB("edb")
  1270. _, err = o.Raw(sql, unit, frequency, classifyId, tradeCode).Exec()
  1271. return
  1272. }
  1273. func DeleteTargetsDataByImport(tradeCode, dt string) (err error) {
  1274. sql := `DELETE FROM edbdata WHERE TRADE_CODE=? AND DT=? `
  1275. o := orm.NewOrmUsingDB("edb")
  1276. _, err = o.Raw(sql, tradeCode, dt).Exec()
  1277. return
  1278. }
  1279. // GetEdbinfoListByCodeListGroupByUserId 根据指标code列表、用户分组获取指标信息
  1280. func GetEdbinfoListByCodeListGroupByUserId(edbCodeList []string) (items []*Edbinfo, err error) {
  1281. num := len(edbCodeList)
  1282. if num <= 0 {
  1283. return
  1284. }
  1285. o := orm.NewOrmUsingDB("edb")
  1286. sql := `SELECT * FROM edbinfo WHERE TRADE_CODE in (` + utils.GetOrmInReplace(num) + `) GROUP BY user_id `
  1287. _, err = o.Raw(sql, edbCodeList).QueryRows(&items)
  1288. return
  1289. }
  1290. // GetEdbinfoListByCodeListByCodeIdList
  1291. // @Description: 根据指标code列表获取列表信息
  1292. // @param edbCodeList
  1293. // @return items
  1294. // @return err
  1295. func GetEdbinfoListByCodeListByCodeIdList(edbCodeList []string) (items []*Edbinfo, err error) {
  1296. num := len(edbCodeList)
  1297. if num <= 0 {
  1298. return
  1299. }
  1300. o := orm.NewOrmUsingDB("edb")
  1301. sql := `SELECT * FROM edbinfo WHERE TRADE_CODE in (` + utils.GetOrmInReplace(num) + `) `
  1302. _, err = o.Raw(sql, edbCodeList).QueryRows(&items)
  1303. return
  1304. }
  1305. // GetEdbinfoListByCodeListByUserId
  1306. // @Description: 根据用户id列表获取指标列表信息
  1307. // @param userIdList
  1308. // @return items
  1309. // @return err
  1310. func GetEdbinfoListByCodeListByUserId(userIdList []int) (items []*Edbinfo, err error) {
  1311. num := len(userIdList)
  1312. if num <= 0 {
  1313. return
  1314. }
  1315. o := orm.NewOrmUsingDB("edb")
  1316. sql := `SELECT * FROM edbinfo WHERE user_id in (` + utils.GetOrmInReplace(num) + `) `
  1317. _, err = o.Raw(sql, userIdList).QueryRows(&items)
  1318. return
  1319. }
  1320. // ModifyEdbinfoUserIdByCodeList 根据指标code列表修改创建人
  1321. func ModifyEdbinfoUserIdByCodeList(edbCodeList []string, userId int) (err error) {
  1322. num := len(edbCodeList)
  1323. if num <= 0 {
  1324. return
  1325. }
  1326. o := orm.NewOrmUsingDB("edb")
  1327. sql := `UPDATE edbinfo SET user_id=? WHERE TRADE_CODE in (` + utils.GetOrmInReplace(num) + `) `
  1328. _, err = o.Raw(sql, userId, edbCodeList).Exec()
  1329. return
  1330. }
  1331. // ModifyEdbinfoUserIdByOldUserId
  1332. // @Description: 根据旧用户id修改新用户id
  1333. // @author: Roc
  1334. // @datetime 2024-03-25 17:59:32
  1335. // @param oldUserId int
  1336. // @param userId int
  1337. // @return err error
  1338. func ModifyEdbinfoUserIdByOldUserId(oldUserIdList []int, userId int) (err error) {
  1339. num := len(oldUserIdList)
  1340. if num <= 0 {
  1341. return
  1342. }
  1343. o := orm.NewOrmUsingDB("edb")
  1344. sql := `UPDATE edbinfo SET user_id=? WHERE user_id in (` + utils.GetOrmInReplace(num) + `) `
  1345. _, err = o.Raw(sql, userId, oldUserIdList).Exec()
  1346. return
  1347. }
  1348. func GetEdbInfoAdminList() (list []int, err error) {
  1349. sql := `SELECT user_id FROM edbinfo GROUP BY user_id `
  1350. o := orm.NewOrmUsingDB("edb")
  1351. _, err = o.Raw(sql).QueryRows(&list)
  1352. return
  1353. }