package cn.com.basic.face.service.sqlite;
|
|
import android.database.Cursor;
|
import android.database.sqlite.SQLiteDatabase;
|
|
import java.text.SimpleDateFormat;
|
import java.util.ArrayList;
|
import java.util.Date;
|
import java.util.List;
|
|
import cn.com.basic.face.base.BaseApplication;
|
import cn.com.basic.face.base.MainActivity;
|
import cn.com.basic.face.discern.common.CommonVariables;
|
import cn.com.basic.face.discern.common.ResultBean;
|
import cn.com.basic.face.discern.query.item.AttendanceDetailQueryItem;
|
import cn.com.basic.face.discern.query.item.AttendanceQueryItem;
|
|
/**
|
* 考勤记录
|
*/
|
public class AttendanceDao extends BaseDao{
|
|
public static AttendanceDao instance = new AttendanceDao();
|
|
public static AttendanceDao getInstance() {
|
return instance;
|
}
|
|
/**
|
* 加载考勤列表
|
* @param searchText
|
* @param pageNum
|
* @param append
|
* @return
|
* ResultBean{
|
* private String code;
|
private String message;
|
private Object data; List<AttendanceQueryItem>
|
private int totalPages; 总页数
|
private int pageNum; 第几页
|
* }
|
*/
|
public ResultBean reloadLeftList(String searchText, final int pageNum, final boolean append) {
|
ResultBean resultBean = new ResultBean();
|
SQLiteDatabase db = MainActivity.getInstance().db;
|
try {
|
String deviceCompanyId = BaseApplication.getInstance().getAndroidDevice().getCompanyId();
|
StringBuilder sb = new StringBuilder(40);
|
sb.append("SELECT b.register_id,").append(" b.username,").append(" b.surveillance_photo,")
|
.append(" b.identify_num,").append(" e.post_name,").append(" d.name gender,")
|
.append(" f.dept_name").append(" FROM ").append(" employee a ")
|
.append(" LEFT JOIN register b ON a.register_id = b.register_id ")
|
.append(" LEFT JOIN dictionary d ON d.dict_id = b.gender_id ")
|
.append(" LEFT JOIN post e ON e.post_id = b.post_id ")
|
.append(" LEFT JOIN department f ON f.dept_id = b.department_id ")
|
.append(" WHERE ").append(" 1 = 1 ");
|
if(deviceCompanyId != null && !deviceCompanyId.trim().isEmpty()) {
|
sb.append(" AND a.device_company_id = ").append(deviceCompanyId);
|
}
|
if(searchText != null && !searchText.trim().isEmpty()) {
|
sb.append(" AND b.username LIKE '%").append(searchText).append("%'");
|
}
|
sb.append(" GROUP BY b.register_id ")
|
.append(" ORDER BY b.username ");
|
|
StringBuilder count = new StringBuilder(sb.length());
|
count.append("select count(*) as count from (").append(sb).append(" ) _table");
|
sb.append(" LIMIT ").append(getStartIndex(pageNum)).append(",").append(CommonVariables.Page.DEFAULT_PAGE_SIZE);
|
List<AttendanceQueryItem> list = new ArrayList<AttendanceQueryItem>();
|
AttendanceQueryItem aqi = null;
|
Cursor c = db.rawQuery(sb.toString(),new String[]{});
|
Cursor c_count = db.rawQuery(count.toString(),new String[]{});
|
while (c.moveToNext()) {
|
aqi = new AttendanceQueryItem();
|
aqi.setRegisterId(c.getString(c.getColumnIndex("register_id")));
|
aqi.setUsername(c.getString(c.getColumnIndex("username")));
|
aqi.setSurveillancePhoto(c.getString(c.getColumnIndex("surveillance_photo")));
|
aqi.setIdentifyNum(c.getString(c.getColumnIndex("identify_num")));
|
aqi.setPostName(c.getString(c.getColumnIndex("post_name")));
|
aqi.setGender(c.getString(c.getColumnIndex("gender")));
|
aqi.setDeptName(c.getString(c.getColumnIndex("dept_name")));
|
list.add(aqi);
|
}
|
Integer total = null;
|
while (c_count.moveToNext()) {
|
total = c_count.getInt(0);
|
}
|
resultBean.setTotalPages(getTotalPageSize(total));
|
resultBean.setPageNum(pageNum);
|
resultBean.setData(list);
|
} catch (Exception e) {
|
e.printStackTrace();
|
}
|
return resultBean;
|
}
|
|
/**
|
* 加载考勤明细
|
*/
|
public List<AttendanceDetailQueryItem> reloadAttendanceDetail(AttendanceQueryItem item) {
|
List<AttendanceDetailQueryItem> list = new ArrayList();
|
SQLiteDatabase db = MainActivity.getInstance().db;
|
try {
|
StringBuilder sb = new StringBuilder(40);
|
sb.append("SELECT a.time,").append(" b.device_name,").append(" case when a.attendance_type_id=305 then '签到'")
|
.append(" when a.attendance_type_id=306 then '签退'").append(" else '' end attendance_type")
|
.append(" FROM ").append(" attendance a ")
|
.append(" LEFT JOIN device b ON b.device_id = a.device_id ")
|
.append(" WHERE 1=1 ");
|
String registerId = item.getRegisterId();
|
if(registerId != null && !registerId.trim().isEmpty()) {
|
sb.append(" AND a.register_id= ").append(registerId);
|
}
|
String deviceId = BaseApplication.getInstance().getAndroidDevice().getDeviceId();
|
if(deviceId != null && !deviceId.trim().isEmpty()) {
|
sb.append(" AND b.device_id=").append(deviceId);
|
}
|
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
|
sb.append(" AND a.time like '"+sdf.format(new Date())+"%' ");
|
String deviceCompanyId = BaseApplication.getInstance().getAndroidDevice().getCompanyId();
|
if(deviceCompanyId != null && !deviceCompanyId.trim().isEmpty()) {
|
sb.append(" AND a.device_company_id = ").append(deviceCompanyId);
|
}
|
sb.append(" ORDER BY a.time DESC");
|
StringBuilder count = new StringBuilder(sb.length());
|
count.append("select count(*) as count from (").append(sb).append(" ) _table");
|
AttendanceDetailQueryItem aqi = null;
|
Cursor c = db.rawQuery(sb.toString(),new String[]{});
|
Cursor c_count = db.rawQuery(count.toString(),new String[]{});
|
while (c.moveToNext()) {
|
aqi = new AttendanceDetailQueryItem();
|
aqi.setTime(c.getString(c.getColumnIndex("time")));
|
aqi.setDeviceName(c.getString(c.getColumnIndex("device_name")));
|
aqi.setAttendanceType(c.getString(c.getColumnIndex("attendance_type")));
|
list.add(aqi);
|
}
|
Integer total = null;
|
while (c_count.moveToNext()) {
|
total = c_count.getInt(0);
|
}
|
} catch (Exception e) {
|
e.printStackTrace();
|
}
|
return list;
|
}
|
|
}
|
