1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
| #!/bin/sh
HOST="127.0.0.1"
USER="root"
PASSWORD="password"
DATABASE="db_name"
QY_USERID="JiangYuan"
CORP_ID="xxx"
CORP_AGENTID="1000035"
CORP_SECRET="yyy"
ACCESS_TOKEN_URL="https://qyapi.weixin.qq.com/cgi-bin/gettoken?corpid=ID&corpsecret=SECRET"
SEND_APP_MSG_URL="https://qyapi.weixin.qq.com/cgi-bin/message/send?access_token=ACCESS_TOKEN"
DATE=`printf '%(%Y%m%d)T\n' -1`
echo DATE="$DATE"
i=$((`date -d $DATE +'%w'`-1))
MONDAY_DATE=`date -d "$DATE -$i days" +'%Y-%m-%d 00:00:00'`
echo MONDAY_DATE=$MONDAY_DATE
SQL_COMMAND1="SELECT CONCAT(create_name, '：', SUM(count), ',') as '本周截止当前时间【新建课程】总数：\\\\n'
FROM (SELECT create_name, COUNT(1) AS count
FROM course
WHERE tenant_id = 83
AND division_type = 2
AND del_flag = '1'
AND create_time > '$MONDAY_DATE'
GROUP BY create_name
UNION ALL
SELECT c.create_name, COUNT(1) AS count
FROM course c
LEFT JOIN course_subjects s ON c.id = s.course_id
WHERE c.tenant_id = 83
AND c.division_type = 1
AND c.del_flag = '1'
AND c.create_time > '$MONDAY_DATE'
GROUP BY c.create_name) t1
GROUP BY create_name;"
SQL_COMMAND2="SELECT CONCAT(create_name, '：', SUM(num), ',') AS '本周截止当前时间【新建课时】总数：\\\\n'
FROM (SELECT c.create_name, COUNT(1) AS num
FROM course c
LEFT JOIN course_chapter cc ON c.id = cc.course_id
WHERE c.tenant_id = 83
AND c.del_flag = '1'
AND c.create_time > '$MONDAY_DATE'
AND cc.del_flag = '1'
GROUP BY c.create_name) t1
GROUP BY create_name;"
SQL_COMMAND3="SELECT CONCAT(create_name, '：', count(*), ',') as '本周截止当前时间【新建题目】总数：\\\\n'
FROM question
WHERE parent_id = 0
AND del_flag = '0'
AND create_time > '$MONDAY_DATE'
GROUP BY create_by;"
RESULT1=$(/opt/zbox/bin/mysql -h $HOST -u $USER -p$PASSWORD $DATABASE -e "$SQL_COMMAND1")
RESULT2=$(/opt/zbox/bin/mysql -h $HOST -u $USER -p$PASSWORD $DATABASE -e "$SQL_COMMAND2")
RESULT3=$(/opt/zbox/bin/mysql -h $HOST -u $USER -p$PASSWORD $DATABASE -e "$SQL_COMMAND3")
echo $RESULT1 > /tmp/newCourses
echo $RESULT2 > /tmp/newCourseChapters
echo $RESULT3 > /tmp/newQuestions
cat /tmp/newCourses
echo
cat /tmp/newCourseChapters
echo
cat /tmp/newQuestions
get_qy_access_token() {
echo "get_access_token start..."
CURL_URL=$(echo $ACCESS_TOKEN_URL | sed "s/ID/$CORP_ID/g; s/SECRET/$CORP_SECRET/g")
echo CURL_URL=$CURL_URL
CURL_CMD="curl \"$CURL_URL\""
echo $CURL_CMD
CURL_RES=$(eval $CURL_CMD)
echo $CURL_RES
ACCESS_TOKEN=$(echo $CURL_RES | jq -r '.access_token')
echo ACCESS_TOKEN=$ACCESS_TOKEN
}
push_qy_text_app_msg() {
echo "push_qy_text_app_msg start..."
CURL_URL=$(echo $SEND_APP_MSG_URL | sed "s/ACCESS_TOKEN/$ACCESS_TOKEN/g")
echo CURL_URL=$CURL_URL
currentDate=`date +%Y-%m-%d`
newCourses=`perl -pe 's/,/\\\\n/g' /tmp/newCourses`
echo newCourses=$newCourses
newCourseChapters=`perl -pe 's/,/\\\\n/g' /tmp/newCourseChapters`
echo newCourseChapters=$newCourseChapters
newQuestions=`perl -pe 's/,/\\\\n/g' /tmp/newQuestions`
echo newQuestions=$newQuestions
if [[ -z "$newCourses" ]]; then
newCourses="本周截止当前时间【新建课程】总数：0"
fi
if [[ -z "$newCourseChapters" ]]; then
newCourseChapters="本周截止当前时间【新建课时】总数：0"
fi
if [[ -z "$newQuestions" ]]; then
newQuestions="本周截止当前时间【新建题目】总数：0"
fi
finalDataStr="$newCourses \\n $newCourseChapters \\n $newQuestions"
CURL_DATA="{\"touser\":\"$QY_USERID\",\"msgtype\":\"text\",\"agentid\":$CORP_AGENTID,\"text\":{\"content\":\"截止$currentDate >> 本周新增数据统计：\\n $newCourses \\n $newCourseChapters \\n $newQuestions\"}}"
echo "$CURL_DATA"
CURL_CMD="curl \"$CURL_URL\" -H \"Content-Type: application/json\" -d '$CURL_DATA'"
CURL_RES=$(eval $CURL_CMD)
echo $CURL_RES
}
get_qy_access_token
push_qy_text_app_msg
exit 0
|
