1. Debug wechat to obtain wechat bill interface 2. Analyze wechat bill list interface 3. Obtain bills by writing codes and save them in the database 4. Output annual bill statistics through SQL 4.1 By annual total expenditure and income 4.2 By consumption type 4.3 By Monthly Net income and expenditure 4.4 By consumption Quantity postscript
preface
Alipay out of the annual bill, no longer dare to say that this year did not spend much money, think about the usual large payment with Alipay, micro-payment with wechat, this wechat bill data has not been counted in, HMM…
WeChat out of the 2018 WeChat data report but no annual spending bills, WeChat wallet bills in one bill function can see about spending each month income, can also export the billing records can only be derived for three months, statistical year also scored four times what is export CSV format have to merge, simpler data feel futile, My idea is to obtain all wechat consumption bills in 2018 through interface request and query statistics through SQL after importing into the database
1. Debug wechat and obtain wechat billing interface
1.1. First, open the wechat debugging function, and open this link in wechat (address: Debugx5.qq.com), check debug 1.2, connect the Android phone to the computer, open THE USB debugging in the mobile phone developer option, and open the billing page on wechat. 1.3. Open the Inspect page on Chrome and click Bill Inspect. 1.4. Slide down the bill list and pull down the data to see the request, as well as the JSON information returned in Response
2. Analyze wechat bill list interface
Can see the billing Api is a get request interface for: wx.tenpay.com/userroll/us… The request parameters are and return result. The JSON structure is as follows. Record is a list of specific bills
Query (last_create_time and start_time); query (last_create_time and start_time); query (last_create_time and start_time) Is the time of the last bill when turning the page. You can set the time less than January 1, 2018 to jump out of the cycle, so that you can get all the bills of 2018.
Of course there’s a bit of a loophole in this, and it’s good to have all the headers in your mock request, where the parameters have an expiration date and they expire very quickly, The values are userroll_encryption and userroll_pass_ticket in the Cookie of the request header and exportKey respectively. The other values can be copied directly. Their generation rule is not found here, and these three parameters need to be checked again through the grab interface after expiration.
3. Write code to get bills and save them to the database
Recently I have been looking at back-end development, and I have been writing Android server code in Java. IntelliJ IDEA is almost as user-friendly as Android Studio. First build the database table through the JSON of the bill, and then build the model bean, and then write the business logic, and do the post-database operation.
The main logic code is as follows:
// @Async("taskExecutor")
public void createGetBillTask( String exportkey, String userroll_encryption, String userroll_pass_ticket) {
DemoApplication.logger.warn("Create task:"+ exportkey); //copy headMap <String, String> headMaps = new LinkedHashMap<>(); headMaps.put("Accept"."* / *");
headMaps.put("Accept-Encoding"."gzip, deflate");
headMaps.put("Accept-Language"."zh-CN,en-US; Q = 0.8");
headMaps.put("Connection"."keep-alive");
headMaps.put("Cookie"."userroll_encryption=" + userroll_encryption + "; userroll_pass_ticket=" + userroll_pass_ticket);
headMaps.put("Host"."wx.tenpay.com");
headMaps.put("Q-Auth"."Need to copy");
headMaps.put("Q-GUID"."Need to copy");
headMaps.put("Q-UA2"."Need to copy");
headMaps.put("Referer"."https://wx.tenpay.com/?classify_type=0");
headMaps.put("User-Agent"."Mozilla / 5.0 (Linux; The Android 8.0. MIX 2 Build/OPR1.170623.027; Wv) AppleWebKit / 537.36 (KHTML, Like Gecko) Version/4.0 Chrome/57.0.2987.132 MQQBrowser/ 6.2TBS /044408 Mobile Safari/ 537.36mmwebid /4508 X27000038 MicroMessenger / 7.0.1380 (0) the Process/tools NetType/WIFI Language/zh_CN");
headMaps.put("X-DevTools-Emulate-Network-Conditions-Client-Id"."Need to copy");
headMaps.put("X-Requested-With"."com.tencent.mm");
HttpHeaders headers = new HttpHeaders();
headers.clear();
headers.setAll(headMaps);
headers.setExpires(0);
headers.setCacheControl("private, no-store, max-age=0");
HttpEntity entity = new HttpEntity(headers);
OrderResp lastResp = null;
while (true) {
String url = "https://wx.tenpay.com/userroll/userrolllist?classify_type=0&count=" + PAGE_SIZE + "&sort_type=1";
Map<String, Object> queryMaps = new LinkedHashMap<>();
if(lastResp ! = null) {// less than 2017-12-31if (lastResp.getLast_create_time() < 1514736000) {
break;
}
url += "&exportkey={exportkey}&last_bill_id={last_bill_id}&last_bill_type={last_bill_type}&last_create_time={last_create_time}& last_trans_id={last_trans_id}&start_time={start_time}";
queryMaps.put("exportkey", exportkey);
queryMaps.put("last_bill_id", lastResp.getLast_bill_id());
queryMaps.put("last_bill_type", lastResp.getLast_bill_type());
queryMaps.put("last_create_time", lastResp.getLast_create_time());
queryMaps.put("last_trans_id", lastResp.getLast_trans_id());
queryMaps.put("start_time", lastResp.getLast_create_time()); } try { URI uri = restTemplate.getUriTemplateHandler().expand(url, queryMaps); ResponseEntity<OrderResp> resp = resttemplate. exchange(URI, httpmethod.get, entity, orderresp.class); DemoApplication.logger.warn("Mission information:" + uri.toString() + "\nheader:" + resp.getHeaders().toString());
if(! resp.getStatusCode().is2xxSuccessful()) { DemoApplication.logger.warn("Task request network failure:" + resp.toString());
break;
}
OrderResp body = resp.getBody();
if(body == null || body.getRet_code() ! = 0 || body.getRecord() == null || body.getRecord().isEmpty()) { DemoApplication.logger.warn("Task request failed:" + url);
break; } // write to the database orderDao.saveall (records); orderDao.flush(); lastResp = body; long timestamp = lastResp.getLast_create_time(); String format = sdf.format(new Date(timestamp * 1000)); DemoApplication.logger.warn("Mission in progress:" + exportkey + ": imported to:"+ format); } catch (Exception e) { e.printStackTrace(); } try {// interval 1 thread.sleep (1000); } catch (InterruptedException e) { e.printStackTrace(); } } DemoApplication.logger.warn("Complete the mission" + exportkey );
}
Copy the code4. Output annual bill statistics through SQL
After finishing warehousing, I have about 700 bill records. Here are some examples to analyze
4.1. Annual total expenditure and income
select sum(case when fee_attr='positive' thenFee * 0.01elseFee *-0.01 end) as money, sum(case when fee_attr='positive' thenFee * 0.01else0 end) sum(case when fee_attr='negtive' thenFee * 0.01else0 end) as from orderswhere timestamp < 1546272000 and timestamp> 1514736000
Copy the code
4.2 Statistics by consumption type
Select classify_type,count(*),sum(fee * 0.01) as feesum, (select type_str FROM order_type WHERE orders.classify_type=order_type.type) as typestr from orderswhere timestamp < 1546272000 and timestamp> 1514736000
group by classify_type
order by feesum desc;
Copy the code
4.3 Monthly net income and expenditure statistics
select FROM_UNIXTIME(timestamp,'%Y-%m') as time,
sum(case when fee_attr='positive' thenFee * 0.01else 0 end ) as feesumpos,
sum(case when fee_attr='negtive' thenFee * 0.01else 0 end ) as feesumneg,
sum(case when fee_attr='positive' thenFee * 0.01elseFee *-0.01 end) as feesumdda from Orderswhere timestamp < 1546272000 and timestamp> 1514736000
group by time
order by time desc;
Copy the code 
4.4 Statistics by consumption quantity
Select title,count(title) as count from OrderswhereTimestamp < 1546272000 and timestamp> 1514736000 group by title order by desc;Copy the code 
Afterword.
The code is messy mainly to provide an idea, if you want to refer to Github
The follow-up can be improved for the interface output data through the front-end Web page chart library rendering will be more intuitive, there are still a lot of technology stack to learn, of course, technology is just a means mainly or in order to achieve the goal, 2019 fuel ~
