广西健康素养测评系统是为响应国家"健康中国2030"战略规划，落实广西壮族自治区卫生健康委员会关于提升居民健康素养水平的工作要求而开发的综合性测评平台。该系统旨在科学评估广西居民健康素养水平，为卫生健康政策制定提供数据支持，同时帮助居民了解自身健康知识掌握情况
项目职责
作为技术专家，参与系统架构设计优化升级与核心代码优化
技术架构
- 前后端分离，前端React+Antd,后端Laravel
- 疾控中心问卷调查采用Android原生开发
- 数据库：MySQL+MongoDB
来几张截图
随便贴点源码(商业源码不公开)
<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:tools="http://schemas.android.com/tools"
package="com.cdc.survey">
<!-- bugly权限 -->
<uses-permission android:name="android.permission.READ_LOGS" /> <!-- 权限声明 -->
<!-- 访问网络状态 -->
<uses-permission android:name="android.permission.INTERNET" />
<uses-permission android:name="android.permission.ACCESS_NETWORK_STATE" />
<uses-permission android:name="android.permission.ACCESS_WIFI_STATE" />
<uses-permission android:name="android.permission.READ_PHONE_STATE" />
<uses-permission android:name="com.android.launcher.permission.READ_SETTINGS" /> <!-- 录音权限 -->
<uses-permission android:name="android.permission.RECORD_AUDIO" />
<uses-permission android:name="android.permission.RECORD_AUDIO" /> <!-- 读取sd卡数据 -->
<uses-permission android:name="android.permission.WRITE_EXTERNAL_STORAGE" />
<uses-permission android:name="android.permission.WRITE_EXTERNAL_STORAGE" />
<uses-permission android:name="android.permission.READ_EXTERNAL_STORAGE" />
<uses-permission android:name="android.permission.READ_EXTERNAL_STORAGE" /> <!-- 震动 -->
<uses-permission android:name="android.permission.VIBRATE" />
<uses-permission android:name="android.permission.VIBRATE" /> <!-- 摄像头 -->
<uses-permission android:name="android.permission.CAMERA" />
<uses-permission android:name="android.permission.CAMERA" />
<uses-feature android:name="android.hardware.camera" />
<uses-feature android:name="android.hardware.camera.autofocus" />
<uses-feature android:name="android.hardware.camera.flash" /> <!-- 定位 -->
<uses-permission android:name="android.permission.ACCESS_COARSE_LOCATION" />
<uses-permission android:name="android.permission.ACCESS_FINE_LOCATION" /> <!-- 8.0安装权限申请 -->
<uses-permission android:name="android.permission.REQUEST_INSTALL_PACKAGES" />
<uses-permission android:name="android.permission.QUERY_ALL_PACKAGES" />
<uses-permission android:name="android.permission.WRITE_MEDIA_STORAGE" /> <!-- android 10 读写sd卡权限 -->
<uses-permission
android:name="android.permission.WRITE_MEDIA_STORAGE"
tools:ignore="ProtectedPermissions" />
<uses-permission android:name="android.permission.WRITE_SETTINGS" />
<uses-permission android:name="android.permission.MODIFY_AUDIO_SETTINGS" />
<uses-permission android:name="android.permission.MANAGE_EXTERNAL_STORAGE" />
<uses-permission android:name="android.permission.FOREGROUND_SERVICE" />
<uses-permission android:name="android.permission.READ_MEDIA_IMAGES" />
<uses-permission android:name="android.permission.READ_MEDIA_AUDIO" />
<uses-permission android:name="android.permission.READ_MEDIA_VIDEO" />
<uses-feature android:name="android.hardware.camera.any" />
<application
android:name=".theApp"
android:allowBackup="true"
android:icon="@mipmap/ic_launcher"
android:label="@string/app_name"
android:largeHeap="true"
android:networkSecurityConfig="@xml/network_security_config"
android:roundIcon="@mipmap/ic_launcher_round"
android:supportsRtl="true"
android:theme="@style/AppTheme"
tools:ignore="GoogleAppIndexingWarning">
<activity
android:name=".ui.activity.FeedbackSubmitActivity"
android:exported="false" />
<activity
android:name=".ui.activity.WorkPhotosActivity"
android:exported="false" />
<activity
android:name=".ui.activity.QuesSurveyVoiceFillActivity"
android:exported="false" />
<activity
android:name=".ui.activity.SurveyFillActivity"
android:exported="false" /> <!-- 百度地图key, release及debug可共用, 注:这个是个人的key, 以后需要修改成公司的key -->
<meta-data
android:name="com.baidu.lbsapi.API_KEY"
android:value="jtwLcAtqjuDKuDnem54v9svMGG1QNWDC" /> <!-- 百度地图定位服务 -->
<service
android:name="com.baidu.location.f"
android:enabled="true"
android:process=":remote" /> <!-- 沉浸式状态栏immersionbar 适配全面屏 -->
<meta-data
android:name="android.max_aspect"
android:value="2.4" /> <!-- 沉浸式状态栏immersionbar 适配华为（huawei）刘海屏 -->
<meta-data
android:name="android.notch_support"
android:value="true" /> <!-- 沉浸式状态栏immersionbar 适配小米（xiaomi）刘海屏 -->
<meta-data
android:name="notch.config"
android:value="portrait|landscape" />
<provider
android:name=".CDCFileProvider"
android:authorities="${applicationId}.provider"
android:exported="false"
android:grantUriPermissions="true">
<meta-data
android:name="android.support.FILE_PROVIDER_PATHS"
android:resource="@xml/file_paths" />
</provider>
<activity
android:name=".ui.activity.SplashActivity"
android:configChanges="orientation|keyboardHidden"
android:screenOrientation="portrait"
android:theme="@style/splash_style"
android:windowSoftInputMode="adjustResize">
<intent-filter>
<action android:name="android.intent.action.MAIN" />
<category android:name="android.intent.category.LAUNCHER" />
</intent-filter>
</activity>
<activity
android:name=".ui.activity.MainActivity"
android:configChanges="orientation|keyboardHidden"
android:screenOrientation="portrait"
android:windowSoftInputMode="adjustResize" />
<activity
android:name=".ui.activity.SettingActivity"
android:launchMode="singleTask"
android:screenOrientation="portrait" />
<activity
android:name=".ui.activity.AboutActivity"
android:launchMode="singleTask"
android:screenOrientation="portrait" />
<activity
android:name=".ui.activity.LoginActivity"
android:launchMode="singleTask"
android:screenOrientation="portrait" />
<activity
android:name=".ui.activity.UseHelpActivity"
android:launchMode="singleTask"
android:screenOrientation="portrait" />
<activity
android:name=".ui.activity.ChangePwdActivity"
android:launchMode="singleTask"
android:screenOrientation="portrait" />
<activity
android:name=".ui.activity.ChangeTextSizeActivity"
android:launchMode="singleTask"
android:screenOrientation="portrait" />
<activity
android:name=".ui.activity.WebViewActivity"
android:launchMode="singleTask"
android:screenOrientation="portrait" />
<activity
android:name=".ui.activity.FinishStatusActivity"
android:launchMode="singleTask"
android:screenOrientation="portrait" />
<activity
android:name=".ui.activity.MySurveyProjectActivity"
android:launchMode="singleTask"
android:screenOrientation="portrait" />
<activity
android:name=".ui.activity.RecycleRecordActivity"
android:launchMode="singleTask"
android:screenOrientation="portrait" />
<activity
android:name=".ui.activity.ProjectDetailsActivity"
android:launchMode="singleTask"
android:screenOrientation="portrait" />
<activity
android:name=".ui.activity.ProjectProgressActivity"
android:launchMode="singleTask"
android:screenOrientation="portrait" />
<activity
android:name=".ui.activity.QualityControlActivity"
android:launchMode="singleTask"
android:screenOrientation="portrait" />
<activity
android:name=".ui.activity.ProjectSurveyActivity"
android:launchMode="singleTask"
android:screenOrientation="portrait" />
<activity
android:name=".ui.activity.FragmentContainerActivity"
android:configChanges="orientation|keyboardHidden"
android:screenOrientation="portrait"
android:windowSoftInputMode="stateHidden|adjustResize" />
<activity
android:name=".ui.activity.SurveyStepActivity"
android:configChanges="orientation|keyboardHidden"
android:screenOrientation="portrait"
android:windowSoftInputMode="stateHidden|adjustResize" />
<activity
android:name=".ui.activity.UpdateAppActivity"
android:configChanges="orientation|keyboardHidden"
android:theme="@style/AppTheme.transparent"
android:windowSoftInputMode="stateHidden|adjustResize" />
<activity
android:name=".ui.activity.FeedbackActivity"
android:launchMode="singleTask"
android:screenOrientation="portrait" />
<activity
android:name=".ui.activity.UploadWorkPhotoActivity"
android:launchMode="singleTask"
android:screenOrientation="portrait" />
<activity
android:name=".ui.activity.QuesSurveyImageFillActivity"
android:launchMode="singleTask"
android:screenOrientation="portrait" />
<service
android:name="com.tencent.smtt.export.external.DexClassLoaderProviderService"
android:label="dexopt"
android:process=":dexopt" />
</application>
</manifest>
package com.cdc.survey.ui.fragment;
import android.annotation.SuppressLint;
import android.content.Context;
import android.os.Bundle;
import androidx.annotation.Nullable;
import androidx.fragment.app.FragmentActivity;
import android.text.Spannable;
import android.text.TextUtils;
import android.view.Gravity;
import android.view.View;
import android.webkit.JavascriptInterface;
import android.widget.TextView;
import com.blankj.utilcode.util.StringUtils;
import com.cdc.survey.BuildConfig;
import com.cdc.survey.R;
import com.cdc.survey.entity.AppData;
import com.cdc.survey.entity.CdcState;
import com.cdc.survey.entity.CheckStatus;
import com.cdc.survey.entity.DownloadQuestion;
import com.cdc.survey.entity.Person;
import com.cdc.survey.entity.QuesSurvey;
import com.cdc.survey.entity.SurveyProgress;
import com.cdc.survey.entity.resp.RespResult;
import com.cdc.survey.event.AnswerEvent;
import com.cdc.survey.event.Event;
import com.cdc.survey.event.RefreshUploadCountBean;
import com.cdc.survey.listener.SurveyStep;
import com.cdc.survey.net.FetchQuestion;
import com.cdc.survey.net.base.JsonCallback;
import com.cdc.survey.theApp;
import com.cdc.survey.ui.activity.FragmentContainerActivity;
import com.cdc.survey.ui.activity.RecycleRecordActivity;
import com.cdc.survey.ui.base.BaseActivity;
import com.cdc.survey.util.CommonUtils;
import com.cdc.survey.ui.activity.SurveyStepActivity;
import com.cdc.survey.ui.base.BaseFragment;
import com.cdc.survey.ui.dialog.TDialog;
import com.cdc.survey.util.AdBlocker;
import com.cdc.survey.util.CacheUtils;
import com.cdc.survey.util.Checker;
import com.cdc.survey.util.Constants;
import com.cdc.survey.util.DbUtil;
import com.cdc.survey.util.FileUtil;
import com.cdc.survey.util.LogUtil;
import com.cdc.survey.util.PreferUtil;
import com.cdc.survey.util.SPUtils;
import com.cdc.survey.util.SpannableUtil;
import com.cdc.survey.util.TimeUtil;
import com.lzy.okgo.model.Response;
import com.tencent.smtt.export.external.interfaces.JsResult;
import com.tencent.smtt.export.external.interfaces.WebResourceResponse;
import com.tencent.smtt.sdk.WebChromeClient;
import com.tencent.smtt.sdk.WebSettings;
import com.tencent.smtt.sdk.WebStorage;
import com.tencent.smtt.sdk.WebView;
import com.tencent.smtt.sdk.WebViewClient;
import org.greenrobot.eventbus.EventBus;
import org.json.JSONArray;
import org.json.JSONObject;
import java.io.File;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import butterknife.BindView;
import butterknife.OnClick;
import io.reactivex.disposables.CompositeDisposable;
/**
* 调查流程5、问卷题目界面（使用webview显示）
*/
public class StepQuesDetailWithWebviewFragment extends BaseFragment implements SurveyStep {
// 预览
public static final int FLAG_PREVIEW = 1;
// 答题
public static final int FLAG_ANSWER = 2;
// 重新答题
public static final int FLAG_REANSWER = 3;
// 预览答题
public static final int FLAG_VIEW_ANSWER = 4;
public static final int FLAG_ANSWER_FINISH_SUBMIT = 5;
@BindView(R.id.tv_title)
TextView mTvTitle;
@BindView(R.id.webview)
WebView mWebview;
@BindView(R.id.tv_info)
TextView tv_info;
@BindView(R.id.userInfoLayout)
View userInfoLayout;
@BindView(R.id.answerTimeTv)
TextView answerTimeTv;
@BindView(R.id.answerTimeTv1)
TextView answerTimeTv1;
@BindView(R.id.closeIv)
View closeIv;
private long mProjId = 0;
private long mSurveyId = 0;
private int mFlag = FLAG_ANSWER;
//重新进入页面用于控制前一道题能不能修改
private boolean mReFlag;
private AndroidtoJs mAndroidtoJs = new AndroidtoJs();
private long mAutoSave = 0;
private boolean mAutoSaving = false;
//保存答案类型id(身高体重性别等id)
private List<String> fillList = new ArrayList<>();
private SurveyStepActivity mStepActivity;
//题是多选题&不是多选填空&只选择了一道题 不保存答案
private Long saveEndTime = 0l;
//每次进入页面要保存开始时间 离开页面保存结束时间 最后要累计总时间
private CompositeDisposable mCompositeDisposable = new CompositeDisposable();
/**
* @param projId 项目ID
* @param surveyId 调查ID
* @return
*/
public static StepQuesDetailWithWebviewFragment creator(long projId, long surveyId, int flag) {
StepQuesDetailWithWebviewFragment webviewFragment = new StepQuesDetailWithWebviewFragment();
try {
Bundle args = new Bundle();
String fileName = "/question.html";
String url = FileUtil.getSDCardRootDir() + File.separator + Constants.PACKAGE_NAME + File.separator + Constants.TYPE_DOWNLOAD + File.separator + fileName;
args.putString("url", "file:///" + url);
args.putLong("projId", projId);
args.putLong("surveyId", surveyId);
args.putInt("flag", flag);
webviewFragment.setArguments(args);
} catch (Exception e) {
LogUtil.e(e.toString());
}
return webviewFragment;
}
public static boolean checkQuestionHtmlExists(Context context, boolean showDlg) {
String fileName = "/question.html";
String rootPath = FileUtil.getSDCardRootDir() + File.separator + Constants.PACKAGE_NAME + File.separator + Constants.TYPE_DOWNLOAD;
String url = rootPath + File.separator + fileName;
File file = new File(url);
boolean exists = file.exists();
if (!exists && showDlg) {
// 显示对话框
String content = theApp.string(R.string.txt_step_question_html_not_exists) + "\r\n" + fileName;
TDialog.builder((FragmentActivity) context, R.layout.dialog_notify1)
.onDialogInitListener((helper, dialog) ->
{
helper.setText(R.id.tv_content, content);
helper.setOnClickListener(v ->
{
switch (v.getId()) {
case R.id.btn_ok:
break;
default:
break;
}
dialog.dismiss();
}, R.id.btn_ok);
})
.setGravity(Gravity.TOP)
.setCancelable(false)
.show();
}
return exists;
}
@Override
public int contentViewResId() {
return R.layout.fragment_step_ques_detail_with_webview;
}
@Override
public void initUI() {
try {
if (mStepActivity != null) {
mStepActivity.hideTitleAndBottom();
}
} catch (Exception e) {
LogUtil.e(e.toString());
}
handler().postDelayed(() ->
{
if (getFragmentManager() != null) {
try {
String url = getArguments().getString("url");
mProjId = getArguments().getLong("projId");
mSurveyId = getArguments().getLong("surveyId");
mFlag = getArguments().getInt("flag");
// mSurveyId == 0 为预览
// 预览显示标题，非预览就不显示标题
// viewHelper().setVisible(R.id.lay_title, mSurveyId == 0);
if (FLAG_PREVIEW == mFlag || FLAG_VIEW_ANSWER == mFlag || mSurveyId == 0) {
viewHelper().setVisible(R.id.tv_save, false);
}
configWebview();
QuesSurvey survey = DbUtil.queryQuesSurvey(mProjId, mSurveyId);
if (mFlag == FLAG_ANSWER) {
boolean checkQualityState = mStepActivity.checkQualityCollect(survey, false);
//做题全部完成
if (survey.getProgressObj().quesSurvey && !checkQualityState) {
mReFlag = true;
}
// 要判断是否已经填写完成情况信息
if (survey.getProgressObj().quesSurvey && !mStepActivity.checkFinishStatus(survey, false)) {
mReFlag = true;
}
}
//url = "http://192.168.1.19:8099/question.html";
mWebview.loadUrl(url);
if (mFlag == FLAG_VIEW_ANSWER) {
//预览答题 展示性别 出生年月 文化程度 职业
String queryQuesData = DbUtil.queryQuesData(mProjId);
if (survey != null && !survey.answer.isEmpty()) {
//身高 8zuykw7a 体重 xv249m1c
//身高
String height = "";
//出生年
String year = "";
//出生月
String month = "";
//性别
String sex = "";
//体重
String weight = "";
// String maritalStatus = "";
String cultureString = "";
// String nationString = "";
String occupationString = "";
JSONObject object = new JSONObject(survey.answer);
JSONObject fillObject = object.optJSONObject("fill");
fillList.clear();
if (fillObject != null) {
Iterator<String> keys = fillObject.keys();
while (keys.hasNext()) {
String next = keys.next();
String value = fillObject.optString(next);
fillList.add(next + "-" + value);
}
}
// queryQuesData 题目内容
JSONObject queObject = new JSONObject(queryQuesData);
JSONArray questions = queObject.optJSONArray("questions");
if (questions.length() > 0) {
//问卷不能为空
for (int i = 0; i < questions.length(); i++) {
JSONObject questionObject = questions.optJSONObject(i);
String type = questionObject.optString("type");
if (TextUtils.equals(type, "fill") || TextUtils.equals(type, "radio")) {
//填空题
String title = questionObject.optString("title");
//<p><strong>出生年月:<span class=\"fill\"
// data-type=\"number\" data-id=\"qkltn7ql\"
// data-required=\"true\"
// data-type=\"number\" data-id=\"3qyau3ve\"
// title.
if (title.contains("出生年月")) {
//出生年月
int yearIndex = title.indexOf("data-id=") + 9;
int yearIndex_ = title.indexOf(" ", yearIndex) - 1;
String year_ = title.substring(yearIndex, yearIndex_);
int monthIndex = title.lastIndexOf("data-id=") + 9;
int monthIndex_ = title.indexOf(" ", monthIndex) - 1;
String month_ = title.substring(monthIndex, monthIndex_);
//获取年月
for (int j = 0; j < fillList.size(); j++) {
if (fillList.get(j).contains(year_)) {
year = fillList.get(j).split("-")[1];
}
if (fillList.get(j).contains(month_)) {
month = fillList.get(j).split("-")[1];
}
}
} else if (title.contains("您的民族")) {
// //民族 填空需要单独处理
// String nationId = questionObject.optString("id");
// String nationValue = object.optString(nationId);
// JSONArray nationArray = questionObject.optJSONArray("items");
//
// for (int j = 0; j < nationArray.length(); j++) {
// JSONObject nationObject = nationArray.optJSONObject(j);
// String id = nationObject.optString("id");
// if (TextUtils.equals(nationValue, id)) {
// nationString = nationObject.optString("value");
// break;
// }
//
// }
// if (nationString.contains("其他")) {
// //用户选择了其他
// int nationIndex = nationString.indexOf("data-id=") + 9;
// int nationIndex_ = nationString.indexOf(" ", nationIndex) - 1;
// String nation = nationString.substring(nationIndex, nationIndex_);
//
// for (int j = 0; j < fillList.size(); j++) {
// if (fillList.get(j).contains(nation)) {
// nationString = fillList.get(j).split("-")[1];
// }
// }
// }
//
} else if (title.contains("文化程度")) {
//文化程度
String cultureId = questionObject.optString("id");
String cultureValue = object.optString(cultureId);
JSONArray cultureArray = questionObject.optJSONArray("items");
for (int j = 0; j < cultureArray.length(); j++) {
JSONObject cultureObject = cultureArray.optJSONObject(j);
String id = cultureObject.optString("id");
if (TextUtils.equals(cultureValue, id)) {
cultureString = cultureObject.optString("value");
break;
}
}
} else if (title.contains("职业")) {
try {
//职业 填空需要单独处理
String occupationId = questionObject.optString("id");
String occupationValue = object.optString(occupationId);
JSONArray occupationArray = questionObject.optJSONArray("items");
for (int j = 0; j < occupationArray.length(); j++) {
JSONObject occupationAObject = occupationArray.optJSONObject(j);
String id = occupationAObject.optString("id");
if (TextUtils.equals(occupationValue, id)) {
occupationString = occupationAObject.optString("value");
break;
}
}
//选项和填空只能选择其一
if (occupationString.isEmpty() && occupationString.contains("其他")) {
//用户选择了其他
int occupationIndex = occupationString.indexOf("data-id=") + 9;
int occupationIndex_ = occupationString.indexOf(" ", occupationIndex) - 1;
String occupation = occupationString.substring(occupationIndex, occupationIndex_);
for (int j = 0; j < fillList.size(); j++) {
if (fillList.get(j).contains(occupation)) {
occupationString = fillList.get(j).split("-")[1];
}
}
}
} catch (Exception e) {
e.printStackTrace();
}
} else if (title.contains("您目前的身高")) {
// //身高体重
// int heightIndex = title.indexOf("data-id=") + 9;
// int heightIndex_ = title.indexOf(" ", heightIndex) - 1;
// String height_ = title.substring(heightIndex, heightIndex_);
// int weightIndex = title.lastIndexOf("data-id=") + 9;
// int weightIndex_ = title.indexOf(" ", weightIndex) - 1;
// String weight_ = title.substring(weightIndex, weightIndex_);
// for (int j = 0; j < fillList.size(); j++) {
// if (fillList.get(j).contains(height_)) {
// height = fillList.get(j).split("-")[1];
// }
// if (fillList.get(j).contains(weight_)) {
// weight = fillList.get(j).split("-")[1];
// }
// }
} else if (title.contains("性别")) {
//性别
String sexId = questionObject.optString("id");
String sexValue = object.optString(sexId);
JSONArray sexArray = questionObject.optJSONArray("items");
for (int j = 0; j < sexArray.length(); j++) {
JSONObject sexObject = sexArray.optJSONObject(j);
String id = sexObject.optString("id");
if (TextUtils.equals(sexValue, id)) {
sex = sexObject.optString("value");
break;
}
}
} else if (title.contains("您的婚姻情况")) {
//婚姻状况
// String maritalId = questionObject.optString("id");
// String maritalValue = object.optString(maritalId);
// JSONArray maritalArray = questionObject.optJSONArray("items");
// for (int j = 0; j < maritalArray.length(); j++) {
// JSONObject maritalObject = maritalArray.optJSONObject(j);
// String id = maritalObject.optString("id");
// if (TextUtils.equals(maritalValue, id)) {
// maritalStatus = maritalObject.optString("value");
// break;
// }
// }
}
}
}
}
userInfoLayout.setVisibility(View.VISIBLE);
//处理额外数据str.replace(/<[^>]+>/g, "")
String REGEX_HTML = "<[^>]+>";
// 过滤html标签
Pattern p_html = Pattern.compile(REGEX_HTML, Pattern.CASE_INSENSITIVE);
Matcher occupation_html = p_html.matcher(occupationString);
occupationString = occupation_html.replaceAll("");
occupationString.trim(); // 返回文本字符串
Matcher sex_html = p_html.matcher(sex);
sex = sex_html.replaceAll("");
sex.trim(); // 返回文本字符串
Matcher culture_html = p_html.matcher(cultureString);
cultureString = culture_html.replaceAll("");
cultureString.trim(); // 返回文本字符串
// Matcher marital_html = p_html.matcher(maritalStatus);
// maritalStatus = marital_html.replaceAll("");
// maritalStatus.trim(); // 返回文本字符串
// Matcher nation_html = p_html.matcher(nationString);
// nationString = nation_html.replaceAll("");
// nationString.trim(); // 返回文本字符串
StringBuffer stringBuffer = new StringBuffer();
// if (!nationString.isEmpty()) {
// stringBuffer.append("民 族：" + nationString + "\n");
// }
//
// if (!maritalStatus.isEmpty()) {
// stringBuffer.append("婚姻状况：" + maritalStatus + "\n");
// }
if (!sex.isEmpty()) {
stringBuffer.append("性 别：" + sex + "\n");
}
if (!year.isEmpty()) {
stringBuffer.append("出生年月：" + year + "-" + month + "\n");
}
if (!cultureString.isEmpty()) {
stringBuffer.append("文化程度：" + cultureString + "\n");
}
if (!occupationString.isEmpty()) {
stringBuffer.append("职 业：" + occupationString + "\n");
}
// if (!height.isEmpty()) {
// stringBuffer.append("身高：" + height + "米 "
// + "体重：" + weight + "公斤\n");
// }
if (stringBuffer.toString().isEmpty()) {
tv_info.setVisibility(View.GONE);
} else {
tv_info.setVisibility(View.VISIBLE);
tv_info.setText(stringBuffer);
}
}
}
} catch (Exception e) {
LogUtil.e(e.toString());
}
}
}, 300);
}
@OnClick({R.id.img_back, R.id.tv_save})
public void onClick(View v) {
switch (v.getId()) {
case R.id.img_back:
evaluateSubmit();
getBaseActivity().finish(true);
break;
case R.id.tv_save:
evaluateSubmit();
theApp.showToast("保存成功");
break;
default:
break;
}
}
@SuppressLint("WrongConstant")
private void configWebview() {
mWebview.setWebViewClient(new WebViewClient());
mWebview.setScrollBarStyle(View.SCROLL_AXIS_NONE);
mWebview.setOnLongClickListener(v -> true);
closeIv.setOnClickListener(v -> {
userInfoLayout.setVisibility(View.GONE);
});
WebSettings ws = mWebview.getSettings();
// 设置与Js交互的权限
ws.setJavaScriptEnabled(true);
ws.setDefaultTextEncodingName("UTF-8");
// ws.setTextZoom(100);
ws.setSupportZoom(true);
ws.setBuiltInZoomControls(true);
ws.setUseWideViewPort(true);
// ws.setLoadWithOverviewMode(true);
ws.setDomStorageEnabled(true);
ws.setDatabaseEnabled(true);
mWebview.addJavascriptInterface(mAndroidtoJs, "survey");
// mWebview.loadUrl("file:///asset/javascript.html");
mWebview.setWebChromeClient(new WebChromeClient() {
@Override
public boolean onJsAlert(WebView view, String url, String message, JsResult result) {
// 默认返回false
return true;
}
});
mWebview.setWebViewClient(new WebViewClient() {
//帮助WebView处理各种通知、请求事件
private Map<String, Boolean> loadedUrls = new HashMap<>();
@Nullable
@Override
public WebResourceResponse shouldInterceptRequest(WebView view, String url) {
boolean ad;
if (!loadedUrls.containsKey(url)) {
ad = AdBlocker.isAd(url);
loadedUrls.put(url, ad);
} else {
ad = loadedUrls.get(url);
}
return ad ? AdBlocker.createEmptyResource() :
super.shouldInterceptRequest(view, url);
}
@Override
public void onPageFinished(WebView view, String url) {
super.onPageFinished(view, url);
String quesData = DbUtil.queryQuesData(mProjId);
initTitle();
if (Checker.isNotEmpty(quesData)) {
initWebData(quesData);
} else {
new FetchQuestion(mProjId).send(new JsonCallback<RespResult<DownloadQuestion>>() {
private boolean mSucc = false;
@Override
public void onSuccess(Response<RespResult<DownloadQuestion>> response) {
try {
DownloadQuestion downloadQuestion = response.body().result;
String content = downloadQuestion.content;
if (Checker.isNotEmpty(content)) {
mSucc = true;
DbUtil.saveQuesData(mProjId, content);
initWebData(content);
}
} catch (Exception e) {
LogUtil.e(e.toString());
}
}
@Override
public void onError(Response<RespResult<DownloadQuestion>> response) {
super.onError(response);
if (CommonUtils.isNetworkAvailable(theApp.sInstance))
if (getBaseActivity() != null) {
CommonUtils.showDialog(getBaseActivity(), Constants.SERVER_ERROR);
}
}
@Override
public void onFinish() {
super.onFinish();
if (!mSucc) {
if (getFragmentManager() != null) {
theApp.showToast("加载数据出错，请重新下载问卷！");
}
}
}
});
}
}
});
}
/**
* 设置字体大小
*/
private void setTextSize() {
Float fontSizeScale = (float) SPUtils.get(getBaseActivity(), Constants.SP_FontScale, 0.0f);
//默认300 越小越大 320 300 280 260 250
// 875 0.0 1.125 1.25 1.375
int zoom = 0;
if (fontSizeScale == 0.875) {
zoom = 320;
} else if (fontSizeScale == 1.0) {
zoom = 300;
} else if (fontSizeScale == 1.125) {
zoom = 295;
} else if (fontSizeScale == 1.25) {
zoom = 290;
} else if (fontSizeScale == 1.375) {
zoom = 285;
} else {
zoom = 300;
}
String script1 = "javascript:zoomDocument(" + zoom + ")";
mWebview.evaluateJavascript(script1, value ->
{
//此处为 js 返回的结果
});
}
private void initTitle() {
try {
String strTitle = theApp.string(R.string.title_ques_detail);
String subTitle = "";
QuesSurvey survey = DbUtil.queryQuesSurvey(mProjId, mSurveyId);
if (survey != null && Checker.isNotEmpty(survey.person)) {
Person person = Person.parse(survey.person, Person.class);
if (person != null && Checker.isNotEmpty(person.name)) {
subTitle = "(调查对象:" + person.name + ")";
//民族、婚姻状况、文化程度、职业、身高、体重
// tv_info.setText("民族、"+person.birthDate);
}
}
if (Checker.isNotEmpty(subTitle)) {
int cText = theApp.color(R.color.app_color_ques_title);
Spannable spannable = SpannableUtil.builder(strTitle)
.setTextSize(18.40, this.getBaseActivity()).setTextColor(cText)
.append(subTitle).setTextSize(14.0, this.getBaseActivity()).setTextColor(cText)
.create();
mTvTitle.setText(spannable);
} else {
mTvTitle.setText(strTitle);
}
} catch (Exception e) {
LogUtil.e(e.toString());
}
}
private void initWebData(String quesData) {
String state = "";
try {
String answer = "{}";
if (mSurveyId > 0) {
try {
QuesSurvey survey = DbUtil.queryQuesSurvey(mProjId, mSurveyId);
String temp = survey == null || Checker.isEmpty(survey.answer) ? "{}" : survey.answer;
JSONObject json = new JSONObject(temp);
if (mFlag == FLAG_REANSWER) {
if (null != json && json.has("saved")) {
json.remove("saved");
}
}
if (survey != null) {
state = survey.getStatus();
answer = "" + json;
}
} catch (Exception e) {
LogUtil.e(e.toString());
}
}
boolean editable = ((FLAG_VIEW_ANSWER == mFlag) || mReFlag) ? false : true;
String script = "javascript:init(" + quesData + "," + answer + "," + editable + "," + TextUtils.equals(CdcState.SURVEY_REANSWER, state) + "," + "\"" + BuildConfig.VERSION_NAME + "\"" + ")";
mWebview.evaluateJavascript(script, value ->
{
//此处为 js 返回的结果
});
setTextSize();
QuesSurvey surveyBean = DbUtil.queryQuesSurvey(mProjId, mSurveyId);
if (surveyBean != null && Checker.isNotEmpty(surveyBean.person)) {
Person person = Person.parse(surveyBean.person, Person.class);
if (person != null && Checker.isNotEmpty(person.birthDate)) {
String birth = person.birthDate;
int gender = person.gender;
String genderParentId = "";
// "gender":0
//性别
String sex = "";
QuesSurvey survey = DbUtil.queryQuesSurvey(mProjId, mSurveyId);
String queryQuesData = DbUtil.queryQuesData(mProjId);
if (survey != null) {
// queryQuesData 题目内容
JSONObject queObject = new JSONObject(queryQuesData);
JSONArray questions = queObject.optJSONArray("questions");
if (questions.length() > 0) {
//问卷不能为空
for (int i = 0; i < questions.length(); i++) {
JSONObject questionObject = questions.optJSONObject(i);
String type = questionObject.optString("type");
if (TextUtils.equals(type, "radio")) {
//填空题
String title = questionObject.optString("title");
//<p><strong>出生年月:<span class=\"fill\"
// data-type=\"number\" data-id=\"qkltn7ql\"
// data-required=\"true\"
// data-type=\"number\" data-id=\"3qyau3ve\"
// title.
if (title.contains("性别")) {
//性别
genderParentId = questionObject.optString("id");
JSONArray sexArray = questionObject.optJSONArray("items");
for (int j = 0; j < sexArray.length(); j++) {
JSONObject sexObject = sexArray.optJSONObject(j);
String value = sexObject.optString("value");
String genderTemp = (gender == 0 ? "男" : "女");
if (value.contains(genderTemp)) {
sex = sexObject.optString("id");
break;
}
}
}
}
}
}
}
String json = "{\"gender\":" + gender + ",\"genderParentId\":\"" + genderParentId + "\",\"genderId\":\"" + sex + "\"}";
if (birth != null && !TextUtils.isEmpty(birth)) {
String[] split = birth.split("-");
if (split.length > 0 && split[0] != null && split[1] != null) {
String projConfig = DbUtil.querySysConfig(Constants.PROJECT_CONFIG);
if (StringUtils.isEmpty(projConfig)) {
projConfig = PreferUtil.getStringPreference(Constants.PROJECT_CONFIG);
}
String script1 = "javascript:fillBirthYearMonth(" + "\"" + split[0] + "\""
+ "," + "\"" + split[1] + "\"" + "," + json + "," + projConfig + ")";
LogUtil.e("fillBirthYearMonth", script1);
mWebview.evaluateJavascript(script1, value ->
{
//此处为 js 返回的结果
});
}
}
}
}
// 自动保存
if (FLAG_PREVIEW != mFlag) {
mAutoSaving = true;
Runnable autoSave = new Runnable() {
@Override
public void run() {
if (getFragmentManager() != null && mAutoSaving) {
long curr = System.currentTimeMillis();
if ((curr - mAutoSave) / 1000 > 10) {
// 保存动作
evaluateSubmit();
}
handler().postDelayed(this, 1000);
}
}
};
handler().postDelayed(autoSave, 10000);
}
} catch (Exception e) {
LogUtil.e(e.toString());
}
}
private void evaluateSubmit() {
// 预览模式
if (FLAG_PREVIEW == mFlag) {
return;
}
//android 调取web方法
String script = "javascript:submit()";
mWebview.evaluateJavascript(script, answer -> {
try {
//此处为 js 返回的结果
LogUtil.e("JS传出数据:" + answer);
if (Checker.isNotEmpty(answer)) {
if (!answer.equals("null")) {
saveAnswer(answer);
}
}
} catch (Exception e) {
LogUtil.e(e.toString());
}
});
}
private void saveAnswer(String answer) {
try {
if (mProjId > 0 && mSurveyId > 0 && Checker.isNotEmpty(answer)) {
QuesSurvey survey = DbUtil.queryQuesSurvey(mProjId, mSurveyId);
if (survey != null) {
survey.answer = answer;
DbUtil.putQuesSurvey(survey);
mAutoSave = System.currentTimeMillis();
}
}
} catch (Exception e) {
LogUtil.e(e.toString());
}
}
@Override
public void setSurvey(QuesSurvey survey) {
}
@Override
public void add() {
// 外部界面右上角的保存
evaluateSubmit();
theApp.showToast("保存成功");
}
@Override
public boolean save() {
evaluateSubmit();
return true;
}
@Override
public boolean handleBackPressed() {
// 外部界面右上角的保存
evaluateSubmit();
return true;
}
@Override
public void onPause() {
try {
if (getFragmentManager() != null) {
// 变成非活动时，保存答案
QuesSurvey survey = DbUtil.queryQuesSurvey(mProjId, mSurveyId);
if (survey != null && survey.getProgressObj() != null) {
SurveyProgress progObj = survey.getProgressObj();
if (!progObj.quesSurvey) {
evaluateSubmit();
}
}
}
} catch (Exception e) {
LogUtil.e(e.toString());
}
super.onPause();
}
@Override
public void onDestroy() {
super.onDestroy();
EventBus.getDefault().postSticky(new RefreshUploadCountBean(true));
try {
QuesSurvey survey = DbUtil.queryQuesSurvey(mProjId, mSurveyId);
if (survey != null) {
if (survey.status.equals(CdcState.SURVEY_RUNNING)) {
//进行中问卷统计答题时长
saveEndTime = System.currentTimeMillis();
survey.setAnswerTime((saveEndTime - mStepActivity.saveStartTime) + survey.getAnswerTime());
DbUtil.putQuesSurvey(survey);
}
mAutoSaving = false;
WebStorage.getInstance().deleteAllData();
mWebview.clearCache(true);
mWebview.clearFormData();
mWebview.clearHistory();
mWebview.clearMatches();
mWebview.clearSslPreferences();
CacheUtils.clearCache(getContext());
}
if (mCompositeDisposable != null && !mCompositeDisposable.isDisposed()) {
mCompositeDisposable.clear();
mCompositeDisposable.dispose();
}
} catch (Exception e) {
LogUtil.e(e.toString());
}
}
@Override
public boolean needEventBus() {
return true;
}
@Override
public void onEvent(Event event) {
if (event.data instanceof AnswerEvent) {
// 黏性事件需要处理后删除，不删除会反复触发
EventBus.getDefault().removeStickyEvent(event);
AnswerEvent answerEvent = (AnswerEvent) event.data;
if (answerEvent.getFlag() == AnswerEvent.FLAG_FORCE_SUBMIT) {
// 保存答案
save();
handler().postDelayed(() ->
{
if (getFragmentManager() != null) {
QuesSurvey survey = DbUtil.queryQuesSurvey(mProjId, mSurveyId);
if (survey != null) {
SurveyProgress progObj = survey.getProgressObj();
if (progObj != null) {
// 判断是全部完成还是部分完成
boolean forceSubmit = !(progObj.quesSurvey || survey.answerPartly == CheckStatus.AnswerPartly.answer_all);
onFinalSubmit(forceSubmit);
} else {
LogUtil.e("bugly日志反馈信息", "progObj进度数据为空啦" + mProjId + " " + mSurveyId);
}
} else {
LogUtil.e("bugly日志反馈信息", "survey进度数据为空啦" + mProjId + " " + mSurveyId);
}
}
}, 500);
}
}
}
@Override
public void init(SurveyStepActivity activity) {
mStepActivity = activity;
//activity.hideTitleAndBottom();
}
private boolean onFinalSubmit(boolean forceSubmit) {
QuesSurvey survey = DbUtil.queryQuesSurvey(mProjId, mSurveyId);
// 调查进度
SurveyProgress progObj = survey.getProgressObj();
BaseActivity bActivity = getBaseActivity();
//解决bugly类强制类型转换出错 FragmentContainerActivity cannot be cast to com.cdc.survey.ui.activity.SurveyStepActivity
if (bActivity instanceof SurveyStepActivity) {
// 检测是否有录入质控信息: 至少一张图片及一个录音;
SurveyStepActivity surveyStepActivity = (SurveyStepActivity) getBaseActivity();
if (surveyStepActivity != null) {
progObj.collectQc = surveyStepActivity.checkQualityCollect(survey, true);
if (!progObj.collectQc) {
mFlag = FLAG_ANSWER_FINISH_SUBMIT;
theApp.showToast(R.string.txt_step7_checked);
return false;
}
// 要判断是否已经填写完成情况信息
if (!surveyStepActivity.checkFinishStatus(survey, true)) {
mFlag = FLAG_ANSWER_FINISH_SUBMIT;
return false;
}
} else {
return false;
}
// 全部完成还是部分完成
survey.answerPartly = progObj.quesSurvey ? CheckStatus.AnswerPartly.answer_all : CheckStatus.AnswerPartly.answer_part;
// 部分完成则改写完成情况
if (survey.answerPartly == CheckStatus.AnswerPartly.answer_part) {
survey.answerCode = CheckStatus.Personal.complete_part;
} else {
survey.answerCode = CheckStatus.Personal.accept;
}
if (Checker.isNotEmpty(progObj.personalCheckStatus)) {
progObj.personalCheckStatus.get(progObj.personalCheckStatus.size() - 1).status = survey.answerCode;
}
// 完成答题时间
String endTime = TimeUtil.now(TimeUtil.YMD_HMS);
// 只修改答题不更改结束时间
if (!CdcState.SURVEY_REANSWER.equalsIgnoreCase(survey.status)) {
survey.endTime = progObj.quesSurvey ? endTime : "";
saveEndTime = System.currentTimeMillis();
survey.setAnswerTime((saveEndTime - surveyStepActivity.saveStartTime) + survey.getAnswerTime());
}
progObj.commit = progObj.quesSurvey;
survey.setProgressObj(progObj);
DbUtil.putQuesSurvey(survey);
// 调查日期
survey.surveyDate = endTime;
survey.status = CdcState.SURVEY_PRE_UPLOAD;
survey.setUname(AppData.getUserNick());
survey.setSurveyDate(CommonUtils.getStringTime(System.currentTimeMillis()));
// 重置排序字段
survey.resetStatusSort();
DbUtil.putQuesSurvey(survey);
SurveyStepActivity activity = (SurveyStepActivity) getBaseActivity();
if (activity != null) {
activity.endSurvey();
}
} else if (bActivity instanceof FragmentContainerActivity) {
// 只修改答题不更改结束时间
if (CdcState.SURVEY_VALID.equalsIgnoreCase(survey.status)) {
bActivity.finish(true);
} else {
LogUtil.e("非有效完成", progObj.toJsonString());
}
} else if (bActivity instanceof RecycleRecordActivity) {
bActivity.finish(true);
} else {
LogUtil.e("bugly类型转换错误", progObj.toJsonString());
}
return true;
}
// 继承自Object类 web调取android方法
public class AndroidtoJs extends Object {
// 定义JS需要调用的方法
// 被JS调用的方法必须加入@JavascriptInterface注解
@JavascriptInterface
public boolean onSubmit(String answer) {
// public void onSubmit(String answer) {
// 预览模式
if (FLAG_PREVIEW == mFlag) {
return true;
}
if (Checker.isNotEmpty(answer)) {
//LogUtil.d("JS调用了Android的方法, 传出的数据:" + answer);
if (!answer.equals("null")) {
LogUtil.e("AndroidtoJs接受web答案数据---->", answer + "=====");
saveAnswer(answer);
// 全部完成
QuesSurvey survey = DbUtil.queryQuesSurvey(mProjId, mSurveyId);
survey.answerPartly = CheckStatus.AnswerPartly.answer_all;
SurveyProgress progObj = survey.getProgressObj();
progObj.quesSurvey = true;
survey.setProgressObj(progObj);
DbUtil.putQuesSurvey(survey);
mAutoSaving = false;
// app 提交上传数据
boolean b = onFinalSubmit(false);
return b;
} else {
LogUtil.e("AndroidtoJs接受web答案数据===", answer + "=====");
}
}
return true;
}
}
}
<?php
namespace app\common\command;
use app\common\CommonException;
use app\common\model\project\Project;
use app\common\model\RecycleExportTask;
use think\console\Command;
use think\console\Input;
use think\console\Output;
use think\Db;
use think\db\exception\DataNotFoundException;
use think\db\exception\ModelNotFoundException;
use think\Exception;
use think\exception\DbException;
use think\exception\PDOException;
use think\facade\Cache;
use think\facade\Env;
use ZipStream\Exception\FileNotFoundException;
use ZipStream\Exception\FileNotReadableException;
use ZipStream\Exception\OverflowException;
use ZipStream\Option\Archive;
use ZipStream\ZipStream;
class RecycleExportFileTask extends Command
{
const TaskType = RecycleExportTask::TASK_TYPE_EXPORTFILE;
protected function configure()
{
$this->setName('sys:RecycleExportFileTask')
->setDescription('RecycleExportFileTask');
}
const LOCK_NAME = 'lock_RecycleExportTask';
protected function execute(Input $input, Output $output)
{
set_time_limit(0);
ini_set('memory_limit', -1); // 不限内存
if (Cache::get(self::LOCK_NAME) == 1) {
outputLog(self::LOCK_NAME . ' is running');
$output->info('RecycleExportFileTask is running');
return;
}
Cache::set(self::LOCK_NAME, 1, 0);
try {
$task = RecycleExportTask::where('task_type', self::TaskType)
->where('task_status', 0)->find();
$output->info("query =>" . RecycleExportTask::getLastSql());
$output->info("currentTask =>" . json_encode($task));
if (!$task) {
outputLog('没有需要执行的任务');
return;
}
$this->export($task, $output);
} catch (CommonException|DataNotFoundException|ModelNotFoundException|PDOException|DbException|Exception $e) {
outputLog('RecycleExportFileTask导出异常', $e->getTrace());
} finally {
Cache::rm(self::LOCK_NAME);
}
}
/**
* @throws DataNotFoundException
* @throws FileNotFoundException
* @throws OverflowException
* @throws DbException
* @throws ModelNotFoundException
* @throws FileNotReadableException
* @throws CommonException
* @throws Exception
*/
public function export($export_task, Output $output)
{
outputLog('进入RecycleExportFileTask导出方法');
// if ($export_task['task_status'] == 1) {
// outputLog('task is running', $export_task);
// return;
// }
Db::startTrans();
try {
RecycleExportTask::where('id', $export_task['id'])->update([
'task_status' => 1,
]);
$task_info = json_decode($export_task['task_info'], true);
if (!$task_info) {
outputLog('task_info error', $export_task);
throw new CommonException("task_info error");
}
set_time_limit(0);
$where = $task_info['where'];
$params = $task_info['params'];
$search = $params['search'];
$project_id = $search['project_id'];
$list = $this->listTb()
->join('project_survey_file f', 'f.survey_id=s.id')
->field('s.code,s.city,s.zone,s.town,s.village,s.home_address,f.type,f.filepath,f.filename,f.ext')
->where($where)
->order('f.id')
->select();
$base_dir = Env::get('root_path') . 'public';
$total_size = 0;
$total_file = 0;
$total_file_no = 0;
$total_code = [];
foreach ($list as $vo) {
$filepath = $base_dir . $vo['filepath'];
if (!file_exists($filepath)) {
$total_file_no++;
continue;
}
$total_size += filesize($filepath);
$total_file++;
$total_code[$vo['code']] = 1;
}
$stat = [
'文件总数:' => count($list),
'不存在的文件数：' => $total_file_no,
'存在的文件大小：' => round($total_size / 1024 / 1024) . 'M',
'code总数：' => count($total_code)
];
outputLog('开始导出质控文件：' . print_r($stat, true), 'debug');
// 使用项目名称命名
$project = Project::where('id', $project_id)->find();
if (!$project) {
throw new CommonException("此项目不存在:" . $project_id);
}
$options = new Archive();
$outfilepath = $base_dir . "/export_files/" . $project['title'] . '_' . RecycleExportTask::TASK_TYPE_EXPORTFILE . '_' . date('Ymd_') . time() . '.zip';
outputLog("out_filepath =>" . $outfilepath);
$outStream = fopen($outfilepath, 'w+');
$options->setOutputStream($outStream);
$zip = new ZipStream($project['title'] . '.zip', $options);
$total_file = 0;
foreach ($list as $vo) {
$filepath = $base_dir . $vo['filepath'];
if (!file_exists($filepath)) {
continue;
}
$zip_filepath = $vo['city'] . '/' . $vo['zone'] . '/' . $vo['town'] . '/' . $vo['village'] . '/' . $vo['code'] . '/' . $vo['code'] . ($vo['type'] == 1 ? 'pic' : 'rec');
$zip->addFileFromPath($zip_filepath . '/' . $vo['filename'] . '.' . $vo['ext'], $filepath);
$total_file++;
}
if (!$total_file) {
outputLog('尚未有质控文件', $stat);
}
$zip->finish();
fclose($outStream);
$download_url = str_replace($base_dir, '', $outfilepath);
RecycleExportTask::where('id', $export_task['id'])->update([
'download_url' => $download_url,
'task_status' => 2,
'finished_time' => date('Y-m-d H:i:s')
]);
Db::commit();
} catch (\Exception $e) {
outputLog('RecycleExportFileTask_Exception => ', $e->getTrace());
Db::rollback();
throw $e;
}
outputLog('导出质控文件完成', $stat);
}
protected function listTb()
{
return Db::name('project_survey_result')->alias('r')
->join('project_survey s', 's.id=r.survey_id')
->join('project_survey_check c', 'c.survey_id=r.survey_id')
->join('user u', 'u.uid=r.uid', 'left')
->join('org o', 'o.id=u.org_id', 'left');
}
}
<?php
namespace app\common\command;
use app\common\CommonException;
use app\common\model\project\SurveyResult;
use app\common\model\RecycleExportTask;
use PhpOffice\PhpSpreadsheet\Exception;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use SPSS\Sav\Writer;
use think\console\Command;
use think\console\Input;
use think\console\Output;
use think\Db;
use think\db\exception\DataNotFoundException;
use think\db\exception\ModelNotFoundException;
use think\exception\DbException;
use think\exception\PDOException;
use think\facade\Cache;
use think\facade\Env;
use think\facade\Log;
class RecycleExportRawTask extends Command
{
const TaskType = RecycleExportTask::TASK_TYPE_EXPORTRAW;
const LOCK_NAME = 'lock_RecycleExportTask';
protected function configure()
{
$this->setName('sys:RecycleExportRawTask')
->setDescription('RecycleExportRawTask');
}
protected function execute(Input $input, Output $output)
{
set_time_limit(0);
ini_set('memory_limit', -1); // 不限内存
if (Cache::get(self::LOCK_NAME) == 1) {
outputLog(self::LOCK_NAME . ' is running');
$output->info('RecycleExportRawTask is running');
return;
}
Cache::set(self::LOCK_NAME, 1, 0);
try {
$task = RecycleExportTask::where('task_type', self::TaskType)
->where('task_status', 0)->find();
// $output->info("query =>" . RecycleExportTask::getLastSql());
$output->info("currentTask =>" . json_encode($task));
if (!$task) {
outputLog('没有需要执行的任务');
return;
}
$this->export($task, $output);
sleep(30);
outputLog('RecycleExportRawTask 完成');
} catch (Exception|CommonException|DataNotFoundException|ModelNotFoundException|PDOException|DbException|\think\Exception $e) {
outputLog('RecycleExportRawTask导出异常', $e->getTrace());
} finally {
Cache::rm(self::LOCK_NAME);
}
}
/**
* @throws DataNotFoundException
* @throws CommonException
* @throws Exception
* @throws ModelNotFoundException
* @throws PDOException
* @throws DbException
* @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
* @throws \think\Exception
*/
public function export($export_task, Output $output)
{
outputLog('进入RecycleExportRawTask导出方法');
// if ($export_task['task_status'] == 1) {
// outputLog('task is running', $export_task);
// return;
// }
Db::startTrans();
try {
RecycleExportTask::where('id', $export_task['id'])->update([
'task_status' => 1,
]);
$task_info = json_decode($export_task['task_info'], true);
if (!$task_info) {
outputLog('task_info error', $export_task);
throw new CommonException("task_info error");
}
$where = $task_info['where'];
$params = $task_info['params'];
$type = strtolower($params['type']);
$title_list = [
'WJBM' => ['code'],
'Finishcode' => ['finish_code'],
'Sex' => ['gender'],
'Age' => ['age'],
'UsedTime（Min）' => ['answer_time_min'],
'JCD' => ['jcd'],
'XZ' => ['xz'],
'CUN' => ['cun'],
'JTH' => ['jth'],
'city' => ['city'],
'county' => ['zone'],
'town' => ['town'],
'village' => ['village'],
'style' => ['finish_status'],
'style1' => ['finish_reason_text'],
'G02' => ['kish'],
'调查对象' => ['answer_realname'],
'家庭成员编号' => ['answer_id'],
'户主姓名' => ['home_realname'],
'调查对象联系方式' => ['local_addr_surveytel'],
'开始答题时间' => ['answer_time_start'],
'结束答题时间' => ['answer_time_end'],
'家庭地址' => ['home_address'],
'调查地址' => ['address'],
'提交时间' => ['updated'],
'备注' => ['remark'],
'监测点' => ['point_name'],
'监测点年份' => ['year'],
'是否为国家监测点' => ['is_country_text'],
'调查员姓名' => ['interviewer_realname'],
'所属单位' => ['org_name'],
'家庭完成情况' => ['family_status_text'],
'个人完成情况' => ['answer_status_text'],
'问卷完成情况' => ['finish_status_text'],
];
outputLog('step_03');
$project_id = 0;
foreach ($where as $vo) {
outputLog('step_foreach =>', $vo);
if ($vo[0] == 'r.project_id') {
$project_id = $vo[2];
}
}
$column = $this->_getAnswerColumn($project_id); // [A01,A02...] 题号
foreach ($column as $vo) {
$title_list[$vo] = [$vo];
}
outputLog('开始sql查询：');
// 获取数据
// 获取项目名
$project = Db::name('project')->where('id', $project_id)->find();
if (!$project) {
throw new CommonException("此项目不存在:" . $project_id);
}
$queryField = 'r.created,c.commit_time updated,s.code,s.id,r.status,family_status,answer_status,s.point_code,city,zone,town,village,
home_realname,answer_info,r.gender,age,answer_time_start,answer_time_end,answer_time,r.finish_reason,r.finish_status,r.export_data,
s.kish,s.is_country,r.local_addr,s.home_address,s.remark,s.year,u.realname interviewer_realname,o.name org_name,c.status check_status,c.status_level,c.rollback_status';
/*$data_list = $this->listTb()->field($queryField)
->where($where)
->order('s.code')
->select();
outputLog('格式化结果parseList：');
$this->_parseList($data_list);
outputLog('格式化结果parseInfo：');
foreach ($data_list as $key => $item) {
$this->_parseInfo($data_list[$key]);
}*/
/* 分页查询并汇总 */
$pageSize = 2000;
$allData = [];
$output->info("query step_where =>" . json_encode($where));
// 先查询总记录数，用来计算总页数
$totalCount = $this->listTb()->where($where)->count();
outputLog("本次导出总数=>", $totalCount);
$totalPages = ceil($totalCount / $pageSize);
// 循环获取每一页的数据并汇总
for ($i = 1; $i <= $totalPages; $i++) {
$pageData = $this->listTb()->field($queryField)
->where($where)
->order('s.code')
->limit(($i - 1) * $pageSize, $pageSize)
->select();
outputLog('for_loop_格式化结果parseList', $i);
$this->_parseList($pageData);
foreach ($pageData as $key => $item) {
$this->_parseInfo($pageData[$key]);
}
$allData = array_merge($allData, $pageData);
}
/* ------------*/
$data_list = $allData;
outputLog('data_list result => ', ['count' => count($allData)]);
outputLog('step_06', $type);
outputLog('step_07', count($data_list));
// 导出csv，只需要数据视图
if ($type == 'csv') {
$lines = [];
$lines[] = implode(',', array_keys($title_list));
foreach ($data_list as $vo) {
$line = [];
foreach ($title_list as $config) {
$content = $vo[$config[0]] ?? '';
if ($config[0] == 'code') {
$content = "'" . $content;
}
$line[] = $this->csvHandlerStr($content);
}
$lines[] = implode(',', $line);
}
outputLog('step_08', count($lines));
$lines_str = mb_convert_encoding(implode("\n", $lines), "GBK", "UTF-8");
// 输出到文件
$download_url = $this->exportCsv($project['title'], $lines_str);
RecycleExportTask::where('id', $export_task['id'])->update([
'download_url' => $download_url,
'task_status' => 2,
'finished_time' => date('Y-m-d H:i:s')
]);
outputLog('导出CVS完成=>', $download_url);
Db::commit();
return;
}
// 导出xls格式
if ($type == '_____xls') {
outputLog('开始导出xls：');
// 输出数据视图
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getSheet(0);
$sheet->setTitle('数据视图');
//第一行标题行
foreach (array_keys($title_list) as $col_pos => $col) {
$sheet->setCellValueByColumnAndRow($col_pos + 1, 1, $col);
}
foreach ($data_list as $row_pos => $row) {
$col_pos = 1;
foreach ($title_list as $config) {
$column = $row[$config[0]] ?? '';
if ($config[0] == 'code') {
$column = "'" . $column;
}
$sheet->setCellValueByColumnAndRow($col_pos++, $row_pos + 2, $column);
}
}
$download_url = $this->exportXls($project['title'], $spreadsheet);
RecycleExportTask::where('id', $export_task['id'])->update([
'download_url' => $download_url,
'task_status' => 2,
'finished_time' => date('Y-m-d H:i:s')
]);
outputLog('导出xls完成：', $download_url);
Db::commit();
return;
}
// 导出sav格式
if ($type == 'sav') {
outputLog('开始导出sav');
$variables = [];
$pos = 0;
foreach ($title_list as $title => $config) {
$data = [];
$field = $config[0];
foreach ($data_list as $row) {
$val = $row[$field] ?? '';
if ($config[0] == 'code') {
$val = "'" . $val;
}
$data[] = $val;
if (strpos($title, ' ') !== false) {
die('数据有误，非法列："' . $title . '"');
}
}
$pos++;
$variables[] = [
'name' => $field,
'format' => \SPSS\Sav\Variable::FORMAT_TYPE_A,
'width' => 255,//$var[2] - 0,
'decimals' => 0,
'label' => $title,
'value' => '',
'missing' => '',//$var[6],
'columns' => 15,
'align' => 1,
'measure' => \SPSS\Sav\Variable::MEASURE_NOMINAL,
'data' => $data
];
}
outputLog('step_exportSav', count($variables));
$download_url = $this->exportSav('raw', $variables);
RecycleExportTask::where('id', $export_task['id'])->update([
'download_url' => $download_url,
'task_status' => 2,
'finished_time' => date('Y-m-d H:i:s')
]);
outputLog('导出sav完成：', $download_url);
Db::commit();
return;
}
} catch (\Exception $e) {
outputLog('RecycleExportRawTask_Exception => ', $e->getMessage());
Db::rollback();
throw $e;
}
outputLog('未知导出格式：' . $type);
}
protected function listTb()
{
return Db::name('project_survey_result')->alias('r')
->join('project_survey s', 's.id=r.survey_id')
->join('project_survey_check c', 'c.survey_id=r.survey_id')
->join('user u', 'u.uid=r.uid', 'left')
->join('org o', 'o.id=u.org_id', 'left');
}
protected function _parseInfo(&$info)
{
int_to_string($info, SurveyResult::getResultStatus());
$info['family_status_text'] = SurveyResult::getStatusText('family', $info['family_status']);
$info['answer_status_text'] = SurveyResult::getStatusText('answer', $info['answer_status']);
$info['finish_status_text'] = SurveyResult::getStatusText('finish', $info['finish_status']);
if ($info['finish_status'] == 3) {
$info['finish_status_text'] .= '（具体请注明：' . $info['finish_reason'] . '）';
}
// 调查地址（准确则显示定位信息，否则显示手动输入）
$local_addr = json_decode($info['local_addr'], true);
$info['exact'] = $local_addr['exact'];
if ($local_addr['exact']) {
$info['address'] = $local_addr['addr'];
} else {
$info['address'] = $local_addr['surveyCity'] . $local_addr['surveyDistrict'] . $local_addr['surveyStreet'] . $local_addr['surveyAddr'];
}
}
protected function _parseList(&$list)
{
// 根据point_code计算出point_name
Log::write('第一步骤开始：', 'debug');
$db = Db::name('project_survey');
$point_codes = array_unique(array_column($list, 'point_code'));
foreach ($point_codes as $vo) {
if ($vo) {
$db->whereOr('code', 'like', $vo . '%');
} else {
$db->whereOr('point_code', 0);
}
}
$point_rows = $db->column('code,province,city,zone,town,village,home_realname', 'code');
Log::write('第一步骤结束：', 'debug');
int_to_string($list, [
'gender' => ['--', '男', '女'],
'is_country' => ['否', '是']
]);
Log::write('第二步骤开始：', 'debug');
// 15-69的人口数
// TODO: 这里先注释掉
/*
$age_rows = Db::name('project_survey_family')->where('survey_id', 'in', array_column($list, 'id'))
->field('survey_id,count(1) total')->where('age', 'between', '15,69')->group('survey_id')->select();
$age_rows = array_column($age_rows, 'total', 'survey_id');
*/
Log::write('第二步骤结束：', 'debug');
array_walk($list, function (&$item) use ($point_rows) {
// 计算监测点
$point = $point_rows[$item['code']];
$point_code_len = strlen($item['point_code']);
$point_name = [$point['province']];
if ($point_code_len >= 4) {
$point_name[] = $point['city'];
}
if ($point_code_len >= 6) {
$point_name[] = $point['zone'];
}
if ($point_code_len >= 8) {
$point_name[] = $point['town'];
}
if ($point_code_len >= 10) {
$point_name[] = $point['village'];
}
if ($point_code_len >= 14) {
$point_name[] = $point['home_realname'];
}
$item['point_name'] = implode($point_name);
// 15-69的人口数
// $item['total_1569'] = intval($age_rows[$item['id']] ?? 0);
$item['total_1569'] = mt_rand(1, 4);
// outputLog("total_1569=>",$item['total_1569']);
/**未开始
*
* 12拒绝，13.无能力回答，14.不在家，15.无符合条件调查对象，16.无人居住/空房/已无此家庭/不是家庭，17.其他
*
* 21.完成，22.部分完成，，23.拒绝，24.无能力回答，25.不在家，26.其他*/
// 入户状态：11同意/完成；12拒绝；13无能力回答；14不在家；15无符合条件调查对象；16无人居住/空房/已无此家庭/不是家庭；17其它。
// 知情同意登记代码：21同意/完成；22部分完成；23拒绝；24无能力回答；25不在家；26其它。
if ($item['status'] >= 3) {
if ($item['answer_status'] >= 21) {
$map = [
'21' => '完成',
'22' => '部分完成',
'23' => '拒绝',
'24' => '无能力回答',
'25' => '不在家',
'26' => '其它',
];
$item['finish_code'] = $item['answer_status'];
$item['finish_status_text'] = $item['answer_status'] . '.' . ($map[$item['answer_status']] ?? '未知');
} else {
$map = [
'11' => '完成',
'12' => '拒绝',
'13' => '无能力回答',
'14' => '不在家',
'15' => '无符合条件调查对象',
'16' => '无人居住/空房/已无此家庭/不是家庭',
'17' => '其它',
];
$item['finish_code'] = $item['family_status'];
$item['finish_status_text'] = $item['family_status'] . '.' . ($map[$item['family_status']] ?? '未知');
}
} else {
$item['finish_code'] = 0;
$item['finish_status_text'] = '未开始';
}
$answer_info = json_decode($item['answer_info'], true);
$code = $item['code'] . '';
$item['jcd'] = substr($code, 0, 6);
$item['xz'] = substr($code, 6, 2);
$item['cun'] = substr($code, 8, 2);
$item['jth'] = substr($code, 10, 4);
$item['answer_id'] = $answer_info['id'] ?? '';
$item['answer_realname'] = $answer_info['name'] ?? '';
$reason = str_replace("\n", ' ', $item['finish_reason']);
$item['finish_reason_text'] = $item['finish_status'] == 3 ? ('因其他原因，面对面调查（具体请注明：' . $reason . '）') : '';
$export_data = json_decode($item['export_data'], true) ?: [];
foreach ($export_data as $export_vo) {
list($key, $val) = $export_vo;
$item[trim($key)] = trim($val);
}
// G01:15-69岁家庭成员人数
$item['G01'] = $item['total_1569'];
// 答题时长：分钟
$item['answer_time_min'] = round($item['answer_time'] / 60);
// local_addr_surveytel
$local_addr = json_decode($item['local_addr'], true);
$item['local_addr_surveytel'] = $local_addr['surveyTel'] ?? '';
});
}
protected function _getAnswerColumn($project_id): array
{
$data = Db::name('project_survey_result')->where('project_id', $project_id)->order('export_data desc')->value('export_data');
$data = $data ? json_decode($data, true) : [];
$keys = [];
foreach ($data as $vo) {
$keys[] = $vo[0];
}
$keys[] = 'G01';
return array_map('trim', $keys);
}
protected function exportSav($name, $variables): string
{
$writer = new Writer([
'header' => [
'prodName' => "raw",
'layoutCode' => 2,
'compression' => 1,
'weightIndex' => 0,
],
'variables' => $variables
]);
$base_dir = Env::get('root_path') . 'public';
$outfilepath = $base_dir . "/export_files/" . $name . '_' . time() . '.sav';
$writer->save($outfilepath);
return str_replace($base_dir, '', $outfilepath);
}
protected function exportCsv($name, $content): string
{
$base_dir = Env::get('root_path') . 'public';
$outfilepath = $base_dir . "/export_files/" . $name . '_' . RecycleExportTask::TASK_TYPE_EXPORTRAW . '_' . date('Ymd_') . time() . '.csv';
file_put_contents($outfilepath, $content);
return str_replace($base_dir, '', $outfilepath);
}
/**
* @throws Exception
* @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
*/
protected function exportXls($name, $list, $config = [], $auto_width = false)
{
outputLog('进入_exportXls');
if (is_object($list)) {
$spreadsheet = $list;
// 自动列宽
if ($auto_width && count($list) < 500) {
foreach ($spreadsheet->getWorksheetIterator() as $worksheet) {
$spreadsheet->setActiveSheetIndex($spreadsheet->getIndex($worksheet));
$sheet = $spreadsheet->getActiveSheet();
$list2 = $sheet->rangeToArray('A1:' . $sheet->getHighestColumn() . $sheet->getHighestRow(), '', TRUE, TRUE, TRUE);
$colDims = [];
foreach ($list2 as $rowIndex => $rowData) {
foreach ($rowData as $colIndex => $colData) {
$charWidth = mb_strlen($colData) * 1.8;
if (empty($colDims[$colIndex]))
$colDims[$colIndex] = $charWidth;
elseif (!empty($colDims[$colIndex]) && $charWidth > $colDims[$colIndex])
$colDims[$colIndex] = $charWidth;
}
}
foreach ($colDims as $colIndex => $charWidth) {
$sheet->getColumnDimension($colIndex)->setWidth($charWidth + 3); // padding: 3-char width
}
}
}
$spreadsheet->setActiveSheetIndex(0);
$fileName = $name . '_' . RecycleExportTask::TASK_TYPE_EXPORTRAW . '_' . date('Ymd_') . time() . '.xlsx';
// $fileName = iconv(
// "utf-8", "GBK", $fileName
// );
outputLog('开始写入............');
$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
$base_dir = Env::get('root_path') . 'public';
$outfilepath = $base_dir . "/export_files/" . $fileName;
outputLog("out_filepath =>" . $outfilepath);
$writer->save($outfilepath);
$spreadsheet->disconnectWorksheets();
unset($spreadsheet);
outputLog("outputXls =>", $outfilepath);
return str_replace($base_dir, '', $outfilepath);
}
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getSheet(0);
$col = 0;
foreach ($config as $title) {
$col++;
$sheet->setCellValueByColumnAndRow($col, 1, $title);
$sheet->getColumnDimensionByColumn($col)->setWidth(12);
}
foreach ($list as $row => $vo) {
$col = 1;
foreach ($config as $field => $title) {
$sheet->setCellValueByColumnAndRow($col++, $row + 2, $vo[$field]);
}
}
$this->exportXls($name, $spreadsheet);
}
private function csvHandlerStr($str)
{
//csv格式如果有逗号，整体用双引号括起来；如果里面还有双引号就替换成两个双引号，这样导出来的格式就不会有问题了
//去掉换行符
$str = str_replace(PHP_EOL, '', $str);
$tempDescription = $str;
//如果有逗号
if (strstr($str, ",")) {
//如果还有双引号，先将双引号转义，避免两边加了双引号后转义错误
if (strstr($str, "\"")) {
$tempDescription = str_replace($str, "\"", "\"\"");
}
//在将逗号转义
$tempDescription = "\"" . $tempDescription . "\"";
}
return $tempDescription;
}
}
强烈推荐 https://xlswriter-docs.viest.me/ 这个三方库，简直碾压其他库无敌的存在，之前采用的RecycleExportRawTask的方式，50w万条数据耗时要超过十几分钟了，用这个基本1两分钟就搞完，简直炸裂，看下面的代码
<?php
namespace app\common\command;
use app\common\CommonException;
use app\common\model\project\SurveyResult;
use app\common\model\RecycleExportTask;
use PhpOffice\PhpSpreadsheet\Exception;
use SPSS\Sav\Writer;
use think\console\Command;
use think\console\Input;
use think\console\Output;
use think\Db;
use think\db\exception\DataNotFoundException;
use think\db\exception\ModelNotFoundException;
use think\exception\DbException;
use think\exception\PDOException;
use think\facade\Cache;
use think\facade\Env;
use think\facade\Log;
use util\ProgressBar;
use Vtiful\Kernel\Excel;
class RecycleExportRawTaskV1 extends Command
{
const TaskType = RecycleExportTask::TASK_TYPE_EXPORTRAW;
const LOCK_NAME = 'lock_RecycleExportTask';
protected function configure()
{
$this->setName('sys:RecycleExportRawTaskV1')
->setDescription('RecycleExportRawTaskV1');
}
protected function execute(Input $input, Output $output)
{
set_time_limit(0);
ini_set('memory_limit', -1); // 不限内存
if (Cache::get(self::LOCK_NAME) == 1) {
outputLog(self::LOCK_NAME . ' is running');
$output->info('RecycleExportRawTask is running');
return;
}
Cache::set(self::LOCK_NAME, 1, 0);
try {
$task = RecycleExportTask::where('task_type', self::TaskType)
->where('task_status', 0)->find();
$output->info("currentTask =>" . json_encode($task));
if (!$task) {
outputLog('没有需要执行的任务');
return;
}
$this->export($task, $output);
outputLog('RecycleExportRawTask 完成');
} catch (Exception|CommonException|DataNotFoundException|ModelNotFoundException|PDOException|DbException|\think\Exception $e) {
outputLog('RecycleExportRawTask导出异常', $e->getTrace());
} finally {
Cache::rm(self::LOCK_NAME);
}
}
/**
* @throws DataNotFoundException
* @throws CommonException
* @throws Exception
* @throws ModelNotFoundException
* @throws PDOException
* @throws DbException
* @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
* @throws \think\Exception
*/
public function export($export_task, Output $output)
{
outputLog('进入RecycleExportRawTask导出方法');
Db::startTrans();
try {
RecycleExportTask::where('id', $export_task['id'])->update([
'task_status' => 1,
]);
$task_info = json_decode($export_task['task_info'], true);
if (!$task_info) {
outputLog('task_info error', $export_task);
throw new CommonException("task_info error");
}
$where = $task_info['where'];
$params = $task_info['params'];
$type = strtolower($params['type']);
$title_list = [
'WJBM' => ['code'],
'Finishcode' => ['finish_code'],
'Sex' => ['gender'],
'Age' => ['age'],
'UsedTime（Min）' => ['answer_time_min'],
'JCD' => ['jcd'],
'XZ' => ['xz'],
'CUN' => ['cun'],
'JTH' => ['jth'],
'city' => ['city'],
'county' => ['zone'],
'town' => ['town'],
'village' => ['village'],
'style' => ['finish_status'],
'style1' => ['finish_reason_text'],
'G02' => ['kish'],
'调查对象' => ['answer_realname'],
'家庭成员编号' => ['answer_id'],
'户主姓名' => ['home_realname'],
'调查对象联系方式' => ['local_addr_surveytel'],
'开始答题时间' => ['answer_time_start'],
'结束答题时间' => ['answer_time_end'],
'家庭地址' => ['home_address'],
'调查地址' => ['address'],
'提交时间' => ['updated'],
'备注' => ['remark'],
'监测点' => ['point_name'],
'监测点年份' => ['year'],
'是否为国家监测点' => ['is_country_text'],
'调查员姓名' => ['interviewer_realname'],
'所属单位' => ['org_name'],
'家庭完成情况' => ['family_status_text'],
'个人完成情况' => ['answer_status_text'],
'问卷完成情况' => ['finish_status_text'],
];
outputLog('step_03');
$project_id = 0;
foreach ($where as $vo) {
outputLog('step_foreach =>', $vo);
if ($vo[0] == 'r.project_id') {
$project_id = $vo[2];
}
}
$column = $this->_getAnswerColumn($project_id);
foreach ($column as $vo) {
$title_list[$vo] = [$vo];
}
outputLog('开始sql查询：');
// 获取数据
// 获取项目名
$project = Db::name('project')->where('id', $project_id)->find();
if (!$project) {
throw new CommonException("此项目不存在:" . $project_id);
}
$queryField = 'r.created,c.commit_time updated,s.code,s.id,r.status,family_status,answer_status,s.point_code,city,zone,town,village,
home_realname,answer_info,r.gender,age,answer_time_start,answer_time_end,answer_time,r.finish_reason,r.finish_status,r.export_data,
s.kish,s.is_country,r.local_addr,s.home_address,s.remark,s.year,u.realname interviewer_realname,o.name org_name,c.status check_status,c.status_level,c.rollback_status';
/* 分页查询并汇总 */
$pageSize = 2000;
$allData = [];
$output->info("query step_where =>" . json_encode($where));
// 先查询总记录数，用来计算总页数
$totalCount = $this->listTb()->where($where)->count();
outputLog("本次导出 =>", ['type' => $type, 'totalCount' => $totalCount]);
$totalPages = ceil($totalCount / $pageSize);
$output->info('准备导出=>' . $type . '，共' . $totalCount . '条数据');
$beginTime = time();
$output->info('开始导出,耗时较长，请耐心等待,开始时间：' . date('Y-m-d H:i:s'));
if ($type == 'xls') {
$progressBar = new ProgressBar($totalCount);
$excel = $this->prepare_xls($project['title'])->header(array_keys($title_list));
// 循环获取每一页的数据并汇总
for ($i = 1; $i <= $totalPages; $i++) {
$pageData = $this->listTb()->field($queryField)
->where($where)
->order('s.code')
->limit(($i - 1) * $pageSize, $pageSize)
->select();
outputLog('handle page =>', $i);
$this->handle_page_data($pageData, $type, $excel, $title_list, $progressBar);
}
$progressBar->finish();
$outfilepath = $excel->output();
$output->info("export_filename =>" . $outfilepath);
$base_dir = Env::get('root_path') . 'public';
$download_url = str_replace($base_dir, '', $outfilepath);
RecycleExportTask::where('id', $export_task['id'])->update([
'download_url' => $download_url,
'task_status' => 2,
'finished_time' => date('Y-m-d H:i:s')
]);
outputLog('导出xls完成:', $download_url);
}
Db::commit();;
$endTime = time();
$output->info('导出xls完成,结束时间：' . date('Y-m-d H:i:s') . ',耗时:' . ($endTime - $beginTime) . '秒');
} catch (\Exception $e) {
outputLog('RecycleExportRawTask_Exception => ', $e->getMessage());
Db::rollback();
throw $e;
}
outputLog('未知导出格式：' . $type);
}
protected function prepare_xls($name): Excel
{
$fileName = $name . '_' . RecycleExportTask::TASK_TYPE_EXPORTRAW . '_' . date('Ymd_') . time() . '.xlsx';
$base_dir = Env::get('root_path') . 'public' . '/export_files/';
$config = [
'path' => $base_dir // xlsx文件保存路径
];
$excel = new Excel($config);
// 第三个参数 False 即为关闭 ZIP64
return $excel->constMemory($fileName, NULL, false);
}
protected function handle_page_data($pageData, $type, Excel &$excel, $title_list, ProgressBar $progressBar)
{
$this->_parseList($pageData);
//处理每一页的数据
$excel_data = [];
foreach ($pageData as $current) {
$this->_parseInfo($current);
$row_data = [];
foreach ($title_list as $config) {
$column = $current[$config[0]] ?? '';
if ($config[0] == 'code') {
$column = "'" . $column;
}
$row_data[] = $column;
}
$excel_data[] = $row_data;
$progressBar->updateBy(1);
}
/** @var TYPE_NAME $excel */
$excel->data($excel_data);
}
protected function listTb()
{
return Db::name('project_survey_result')->alias('r')
->join('project_survey s', 's.id=r.survey_id')
->join('project_survey_check c', 'c.survey_id=r.survey_id')
->join('user u', 'u.uid=r.uid', 'left')
->join('org o', 'o.id=u.org_id', 'left');
}
protected function _parseInfo(&$info)
{
int_to_string($info, SurveyResult::getResultStatus());
$info['family_status_text'] = SurveyResult::getStatusText('family', $info['family_status']);
$info['answer_status_text'] = SurveyResult::getStatusText('answer', $info['answer_status']);
$info['finish_status_text'] = SurveyResult::getStatusText('finish', $info['finish_status']);
if ($info['finish_status'] == 3) {
$info['finish_status_text'] .= '（具体请注明：' . $info['finish_reason'] . '）';
}
// 调查地址（准确则显示定位信息，否则显示手动输入）
$local_addr = json_decode($info['local_addr'], true);
$info['exact'] = $local_addr['exact'];
if ($local_addr['exact']) {
$info['address'] = $local_addr['addr'];
} else {
$info['address'] = $local_addr['surveyCity'] . $local_addr['surveyDistrict'] . $local_addr['surveyStreet'] . $local_addr['surveyAddr'];
}
}
protected function _parseList(&$list)
{
// 根据point_code计算出point_name
Log::write('第一步骤开始：', 'debug');
$db = Db::name('project_survey');
$point_codes = array_unique(array_column($list, 'point_code'));
foreach ($point_codes as $vo) {
if ($vo) {
$db->whereOr('code', 'like', $vo . '%');
} else {
$db->whereOr('point_code', 0);
}
}
$point_rows = $db->column('code,province,city,zone,town,village,home_realname', 'code');
Log::write('第一步骤结束：', 'debug');
int_to_string($list, [
'gender' => ['--', '男', '女'],
'is_country' => ['否', '是']
]);
Log::write('第二步骤开始：', 'debug');
// 15-69的人口数
// TODO: 这里先注释掉
/*
$age_rows = Db::name('project_survey_family')->where('survey_id', 'in', array_column($list, 'id'))
->field('survey_id,count(1) total')->where('age', 'between', '15,69')->group('survey_id')->select();
$age_rows = array_column($age_rows, 'total', 'survey_id');
*/
Log::write('第二步骤结束：', 'debug');
array_walk($list, function (&$item) use ($point_rows) {
// 计算监测点
$point = $point_rows[$item['code']];
$point_code_len = strlen($item['point_code']);
$point_name = [$point['province']];
if ($point_code_len >= 4) {
$point_name[] = $point['city'];
}
if ($point_code_len >= 6) {
$point_name[] = $point['zone'];
}
if ($point_code_len >= 8) {
$point_name[] = $point['town'];
}
if ($point_code_len >= 10) {
$point_name[] = $point['village'];
}
if ($point_code_len >= 14) {
$point_name[] = $point['home_realname'];
}
$item['point_name'] = implode($point_name);
// 15-69的人口数
// $item['total_1569'] = intval($age_rows[$item['id']] ?? 0);
$item['total_1569'] = mt_rand(1, 4);
// outputLog("total_1569=>",$item['total_1569']);
/**未开始
*
* 12拒绝，13.无能力回答，14.不在家，15.无符合条件调查对象，16.无人居住/空房/已无此家庭/不是家庭，17.其他
*
* 21.完成，22.部分完成，，23.拒绝，24.无能力回答，25.不在家，26.其他*/
// 入户状态：11同意/完成；12拒绝；13无能力回答；14不在家；15无符合条件调查对象；16无人居住/空房/已无此家庭/不是家庭；17其它。
// 知情同意登记代码：21同意/完成；22部分完成；23拒绝；24无能力回答；25不在家；26其它。
if ($item['status'] >= 3) {
if ($item['answer_status'] >= 21) {
$map = [
'21' => '完成',
'22' => '部分完成',
'23' => '拒绝',
'24' => '无能力回答',
'25' => '不在家',
'26' => '其它',
];
$item['finish_code'] = $item['answer_status'];
$item['finish_status_text'] = $item['answer_status'] . '.' . ($map[$item['answer_status']] ?? '未知');
} else {
$map = [
'11' => '完成',
'12' => '拒绝',
'13' => '无能力回答',
'14' => '不在家',
'15' => '无符合条件调查对象',
'16' => '无人居住/空房/已无此家庭/不是家庭',
'17' => '其它',
];
$item['finish_code'] = $item['family_status'];
$item['finish_status_text'] = $item['family_status'] . '.' . ($map[$item['family_status']] ?? '未知');
}
} else {
$item['finish_code'] = 0;
$item['finish_status_text'] = '未开始';
}
$answer_info = json_decode($item['answer_info'], true);
$code = $item['code'] . '';
$item['jcd'] = substr($code, 0, 6);
$item['xz'] = substr($code, 6, 2);
$item['cun'] = substr($code, 8, 2);
$item['jth'] = substr($code, 10, 4);
$item['answer_id'] = $answer_info['id'] ?? '';
$item['answer_realname'] = $answer_info['name'] ?? '';
$reason = str_replace("\n", ' ', $item['finish_reason']);
$item['finish_reason_text'] = $item['finish_status'] == 3 ? ('因其他原因，面对面调查（具体请注明：' . $reason . '）') : '';
$export_data = json_decode($item['export_data'], true) ?: [];
foreach ($export_data as $export_vo) {
list($key, $val) = $export_vo;
$item[trim($key)] = trim($val);
}
// G01:15-69岁家庭成员人数
$item['G01'] = $item['total_1569'];
// 答题时长：分钟
$item['answer_time_min'] = round($item['answer_time'] / 60);
// local_addr_surveytel
$local_addr = json_decode($item['local_addr'], true);
$item['local_addr_surveytel'] = $local_addr['surveyTel'] ?? '';
});
}
protected function _getAnswerColumn($project_id): array
{
$data = Db::name('project_survey_result')->where('project_id', $project_id)->order('export_data desc')->value('export_data');
$data = $data ? json_decode($data, true) : [];
$keys = [];
foreach ($data as $vo) {
$keys[] = $vo[0];
}
$keys[] = 'G01';
return array_map('trim', $keys);
}
protected function exportSav($name, $variables): string
{
$writer = new Writer([
'header' => [
'prodName' => "raw",
'layoutCode' => 2,
'compression' => 1,
'weightIndex' => 0,
],
'variables' => $variables
]);
$base_dir = Env::get('root_path') . 'public';
$outfilepath = $base_dir . "/export_files/" . $name . '_' . time() . '.sav';
$writer->save($outfilepath);
return str_replace($base_dir, '', $outfilepath);
}
protected function exportCsv($name, $content): string
{
$base_dir = Env::get('root_path') . 'public';
$outfilepath = $base_dir . "/export_files/" . $name . '_' . RecycleExportTask::TASK_TYPE_EXPORTRAW . '_' . date('Ymd_') . time() . '.csv';
file_put_contents($outfilepath, $content);
return str_replace($base_dir, '', $outfilepath);
}
private function csvHandlerStr($str)
{
//csv格式如果有逗号，整体用双引号括起来；如果里面还有双引号就替换成两个双引号，这样导出来的格式就不会有问题了
//去掉换行符
$str = str_replace(PHP_EOL, '', $str);
$tempDescription = $str;
//如果有逗号
if (strstr($str, ",")) {
//如果还有双引号，先将双引号转义，避免两边加了双引号后转义错误
if (strstr($str, "\"")) {
$tempDescription = str_replace($str, "\"", "\"\"");
}
//在将逗号转义
$tempDescription = "\"" . $tempDescription . "\"";
}
return $tempDescription;
}
}