package cn.com.basic.face.service.sqlite; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import org.xutils.http.RequestParams; import java.util.ArrayList; import java.util.List; import cn.com.basic.face.base.BaseApplication; import cn.com.basic.face.base.Config; 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.entity.Visit; import cn.com.basic.face.discern.entity.VisitDetail; import cn.com.basic.face.discern.query.item.CheckInQueryItem; public class CheckInDao extends BaseDao{ public static CheckInDao instance = new CheckInDao(); public static CheckInDao getInstance() { return instance; } /** * 来访登记左边列表 * @param searchText * @param pageNum * @param isVisitorList true为访客列表,false为人员列表 * 返回 ResultBean { * .... * CheckInQueryItem * } */ public ResultBean findCheckInLeftList(boolean isVisitorList, String searchText, int pageNum) { { SQLiteDatabase db = MainActivity.getInstance().db; ResultBean resultBean = new ResultBean(); try { String sql="SELECT" + " a.register_id," + "a.username," + "a.surveillance_photo," + "d.company_name," + "a.tel," + "a.identify_num," + "b.dept_name," + "d.company_id," + "e.post_name," + "a.mobile_phone," + "a.warning" + " FROM" + " register a" + " LEFT JOIN company d ON d.company_id = a.company_id" + " LEFT JOIN department b ON a.department_id = b.dept_id" + " LEFT JOIN post e ON e.post_id = a.post_id" + " LEFT JOIN ( SELECT register_id, device_company_id from employee ) f ON f.register_id = a.register_id" + " WHERE" + " 1 = 1"; String deviceCompanyId = BaseApplication.getInstance().getAndroidDevice().getCompanyId(); if(isVisitorList) { sql += " AND f.device_company_id = " + deviceCompanyId; }else{ sql += " AND ( f.device_company_id != " + deviceCompanyId+" OR f.device_company_id IS NULL"+")"; } if(searchText != null && !searchText.trim().isEmpty()) { sql += " AND a.username LIKE '%"+ searchText + "%'"; } sql+=" order by a.name_en"; String count = "select count(*) as count from (" + sql + " ) _table"; sql += " LIMIT " + getStartIndex(pageNum) + "," + CommonVariables.Page.DEFAULT_PAGE_SIZE; List list = new ArrayList(); int b = list.size(); CheckInQueryItem cqi = null; Cursor c = db.rawQuery(sql,new String[]{}); Cursor c_count = db.rawQuery(count,new String[]{}); while (c.moveToNext()) { cqi = new CheckInQueryItem(); cqi.setRegisterId(c.getString(c.getColumnIndex("register_id"))); cqi.setUsername(c.getString(c.getColumnIndex("username"))); cqi.setSurveillancePhoto(c.getString(c.getColumnIndex("surveillance_photo"))); cqi.setCompanyName(c.getString(c.getColumnIndex("company_name"))); cqi.setTel(c.getString(c.getColumnIndex("tel"))); cqi.setIdentifyNum(c.getString(c.getColumnIndex("identify_num"))); cqi.setDeptName(c.getString(c.getColumnIndex("dept_name"))); cqi.setCompanyId(c.getString(c.getColumnIndex("company_id"))); cqi.setPostName(c.getString(c.getColumnIndex("post_name"))); cqi.setMobilePhone(c.getString(c.getColumnIndex("mobile_phone"))); cqi.setWarning(c.getString(c.getColumnIndex("warning"))); list.add(cqi); } Integer total = null; while (c_count.moveToNext()) { total = c_count.getInt(c_count.getColumnIndex("count")); } resultBean.setTotalPages(getTotalPageSize(total)); resultBean.setPageNum(pageNum); resultBean.setData(list); c.close(); return resultBean; } catch (Exception e) { e.printStackTrace(); } return resultBean; } } /** * 添加访客记录 * @param params */ public void add(RequestParams params) { params.getStringParameter(Visit.FieldNames.visitReasonId); params.getStringParameter(Visit.FieldNames.visitReason); params.getStringParameter(Visit.FieldNames.remark); params.getStringParameter(Visit.FieldNames.visitorId); params.getStringParameter(Visit.FieldNames.visiteeId); params.getStringParameter(Visit.FieldNames.visitorCompanyId); params.getStringParameter(Visit.FieldNames.visiteeCompanyId); params.getStringParameter(Visit.FieldNames.enterTime); params.getStringParameter(Visit.FieldNames.currentStateId); params.getStringParameter(Visit.FieldNames.warning); params.getStringParameter(VisitDetail.FieldNames.stateId); params.getStringParameter(VisitDetail.FieldNames.createTime); params.getStringParameter(VisitDetail.FieldNames.deviceId); } public boolean hasOfflineCheckInData() { Cursor c = Config.sqlMap.getDb().rawQuery("select count(*) from visit", new String[]{}); c.moveToFirst(); return c.getInt(0) > 0 ? true : false; } }