xuxiuxi

2017-07-21 b5ade4e527bbff041bdb2547f617b03505a7f1f6

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
package cn.com.basic.face.service.sqlite; 
 
import android.database.Cursor; 
import android.database.sqlite.SQLiteDatabase; 
 
import java.util.ArrayList; 
import java.util.List; 
 
import cn.com.basic.face.base.Config; 
import cn.com.basic.face.discern.common.CommonVariables; 
import cn.com.basic.face.discern.common.ResultBean; 
import cn.com.basic.face.discern.query.item.AttendanceQueryItem; 
 
public class VisitDao extends BaseDao{ 
 
    public static VisitDao instance = new VisitDao(); 
    public static VisitDao getInstance() { 
        return instance; 
    } 
 
    /** 
     * 访客管理列表 
     * @param searchText 
     * @param pageNum 
     * @return 
     */ 
    public ResultBean findLeftVisitorList(String searchText, int pageNum) { 
        ResultBean resultBean = new ResultBean(); 
        SQLiteDatabase db = Config.sqlMap.getDb(); 
        db.beginTransaction(); 
        try { 
            StringBuilder sb = new StringBuilder(40); 
            sb.append("SELECT a.register_id,a.username,a.surveillance_photo,d.company_name,a.tel,a.identify_num,b.dept_name,") 
                    .append("d.company_id,e.post_name,a.mobile_phone,0 as type,f.`name` gender,g.`name` identity_type,") 
                    .append("a.birthday,h.`name` country_name,j.username visitee,k.`name` visit_reason,i.enter_time,i.exit_time,") 
                    .append("l.`name` current_status,a.id_card_photo").append(" FROM ").append(" visit i ") 
                    .append(" LEFT JOIN register a on i.visitor_id=a.register_id ") 
                    .append(" LEFT JOIN company d on d.company_id=a.company_id ") 
                    .append(" LEFT JOIN department b on a.department_id = b.dept_id ") 
                    .append(" LEFT JOIN post e on e.post_id=a.post_id ") 
                    .append(" LEFT JOIN dictionary f on f.dict_id=a.gender_id ") 
                    .append(" LEFT JOIN dictionary g on g.dict_id=a.identity_type_id ") 
                    .append(" LEFT JOIN dictionary h on h.dict_id=a.country_id ") 
                    .append(" LEFT JOIN register j on j.register_id=a.register_id ") 
                    .append(" LEFT JOIN dictionary k on k.dict_id=i.visit_reason_id ") 
                    .append(" LEFT JOIN dictionary l on l.dict_id=i.current_state_id ") 
                    .append(" WHERE ").append(" 1 = 1 AND a.register_id IS NOT NULL "); 
            if(searchText != null && !searchText.trim().isEmpty()) { 
                sb.append(" AND b.username LIKE '%").append(searchText).append("%'"); 
            } 
            sb.append(" ORDER BY i.enter_time desc "); 
            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("company_name"))); 
                aqi.setPostName(c.getString(c.getColumnIndex("tel"))); 
                aqi.setGender(c.getString(c.getColumnIndex("identify_num"))); 
                aqi.setDeptName(c.getString(c.getColumnIndex("dept_name"))); 
                aqi.setRegisterId(c.getString(c.getColumnIndex("company_id"))); 
                aqi.setUsername(c.getString(c.getColumnIndex("post_name"))); 
                aqi.setSurveillancePhoto(c.getString(c.getColumnIndex("mobile_phone"))); 
                aqi.setIdentifyNum(c.getString(c.getColumnIndex("type"))); 
                aqi.setPostName(c.getString(c.getColumnIndex("gender"))); 
                aqi.setGender(c.getString(c.getColumnIndex("identity_type"))); 
                aqi.setDeptName(c.getString(c.getColumnIndex("birthday"))); 
                aqi.setRegisterId(c.getString(c.getColumnIndex("country_name"))); 
                aqi.setUsername(c.getString(c.getColumnIndex("visitee"))); 
                aqi.setSurveillancePhoto(c.getString(c.getColumnIndex("visit_reason"))); 
                aqi.setIdentifyNum(c.getString(c.getColumnIndex("enter_time"))); 
                aqi.setPostName(c.getString(c.getColumnIndex("exit_time"))); 
                aqi.setGender(c.getString(c.getColumnIndex("current_status"))); 
                aqi.setDeptName(c.getString(c.getColumnIndex("id_card_photo"))); 
                list.add(aqi); 
            } 
            Integer total = null; 
            while (c_count.moveToNext()) { 
                total = c.getInt(c.getColumnIndex("count")); 
            } 
            resultBean.setTotalPages(getTotalPageSize(total)); 
            resultBean.setPageNum(pageNum); 
            resultBean.setData(list); 
            c.close(); 
            c_count.close(); 
            db.setTransactionSuccessful(); 
        } catch (Exception e) { 
            e.printStackTrace(); 
        } finally { 
            db.endTransaction(); 
        } 
        return resultBean; 
    } 
 
}