package com.jqyd.shareInterface;
import java.io.File;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import com.jqyd.app.WriteFile;
import com.jqyd.model.CoUserModule;
import com.jqyd.model.CustomerModule;
import com.jqyd.model.DbsxLcgzModel;
import com.jqyd.model.EmpsModule;
import com.jqyd.model.GroupsModule;
import com.jqyd.model.LocationModule;
import com.jqyd.model.ProTypeModule;
import com.jqyd.model.PublicInfoModule;
import com.jqyd.model.RecordModel;
import com.jqyd.model.YjxxModel;
import com.jqyd.pub.JqydDateUtil;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.util.Log;
/**
* 操作Sqllite3数据库的接口程序
* @author wangliang
*
*/
public class Optdb_interfce {
//数据库对象
private static SQLiteDatabase db = null;
//数据库游标
private Cursor cursor = null;
//数据库名
private static String DATABASE_JQGJ="JQGJ.db";
//数据变量
private String group_flag = "";
private WriteFile wf;
/**
* 数据库表字段信息
*/
//管理员信息表
private static final String T_COUSERS = "CREATE TABLE T_COUSERS(ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,GGUID VARCHAR(50),SJHM VARCHAR(20),QC VARCHAR(100)," +
"JC VARCHAR(50),XT VARCHAR(50),QYDZ VARCHAR(500),XM VARCHAR(30),DISLIMIT VARCHAR(10),JCLB VARCHAR(500),XXCJLB VARCHAR(500)";
//客户信息表
private static final String T_CUSTOMERS = "CREATE TABLE T_CUSTOMERS(ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,CID VARCHAR(50),CNAME VARCHAR(100),SJHM VARCHAR(20)," +
"CZJM VARCHAR(50),GID VARCHAR(50),ADDTIME VARCHAR(30),LINKMAN VARCHAR(30),LINK_SIM VARCHAR(20),SHORT_NAME VARCHAR(50),KH_SORT INT,ADDRESS VARCHAR(500)," +
"LON VARCHAR(30),LAT VARCHAR(30),CLON VARCHAR(30),CLAT VARCHAR(30),KH_STATE INT,EMAIL VARCHAR(50),QQ VARCHAR(20),HONOR VARCHAR(50),DEPARTMENT VARCHAR(50)";
//员工信息表
private static final String T_EMPS = "CREATE TABLE T_EMPS(ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,GUID VARCHAR(50),DWHM VARCHAR(20),XM VARCHAR(30),GID VARCAHR(50)," +
"ZJM VARCHAR(30),ZCSJ VARCHAR(30),HONOR VARCHAR(50),DEPARTMENT VARCHAR(50),EMAIL VARCHAR(50),QQ VARCHAR(20),ADDR VARCHAR(200)";
//分组信息表------------------
private static final String T_GROUPS = "CREATE TABLE T_GROUPS(ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,GID INT,GNAME VARCHAR(50),G_PID INT,G_SORT INT";
//公共信息表--公告、通知
private static final String T_PUBINFOS = "CREATE TABLE T_PUBINFOS(ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,GID INT,TITLE VARCHAR(50),CONTENT VARCHAR(1000)," +
"G_COSIM VARCHAR(30),ADDTIME VARCHAR(30),G_SORT INT,STATE INT";
//产品规格信息表
private static final String T_PRODUCTS = "CREATE TABLE T_PRODUCTS(ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,PID INT,TID VARCHAR(50),P_NAME VARCHAR(50),P_ZJM VARCHAR(50),P_TYPE VARCHAR(50)," +
"P_SORT VARCHAR(30),P_STATUS INT,P_UNIT VARCHAR(20),P_CBJ VARCHAR(20),P_PFJ VARCHAR(20),P_LSJ VARCHAR(20),P_FACTORY VARCHAR(100),P_ADDR VARCHAR(500),P_SALER VARCHAR(50),P_BZ VARCHAR(500),P_ADDTIME VARCHAR(30),P_LB INT,GID INT";
//数据更新记录表
private static final String T_DATA_UPDATES = "CREATE TABLE T_DATA_UPDATES(ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,DATATYPE VARCHAR(20),UTIME VARCHAR(20)";
//数据重传表
private static final String T_RECORDS = "CREATE TABLE T_RECORDS(ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,GID INT,CUSTID VARCHAR(50),LON VARCHAR(30)," +
"LAT VARCHAR(30),ADD_TIME VARCHAR(30),CONTENT VARCHAR(500),JCLB VARCHAR(500),XXCJLB VARCHAR(500),WZBS INT,PIC_DETAIL VARCHAR(100),PIC_URL VARCHAR(100)," +
"BZ VARCHAR(500),RESULT INT,CID VARCHAR(50),LAC VARCHAR(50),NCODE VARCHAR(50),CCODE VARCHAR(50),XHQD VARCHAR(100),RADIUS VARCHAR(50)";
//历史记录表
private static final String T_HISTORYS = "CREATE TABLE T_HISTORYS(ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,GID INT,ZID INT,ADD_TIME VARCHAR(30),CONTENT VARCHAR(500)";
//拜访记录表
private static final String T_VISTORS = "CREATE TABLE T_VISTORS(ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,CID VARCHAR(50),POINT INT";
//基站信息表
private static final String T_LACINFO = "CREATE TABLE T_LACINFO(ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,CID VARCHAR(50),CELL_ID VARCHAR(50)," +
"LAC VARCHAR(50),CCODE VARCHAR(50),NCODE VARCHAR(50),XHQD VARCHAR(100),RADIUS VARCHAR(500),LOC_TYPE INT";
//工作信息表（指：预警信息、经营管控等）
private static final String T_WORKINFOS = "CREATE TABLE T_WORKINFOS(ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,GID INT,TITLE VARCHAR(50),CONTENT VARCHAR(1000)," +
"G_COSIM VARCHAR(30),ADDTIME VARCHAR(30),G_SORT INT,STATE INT";
//任务计划表（指：连续上报功能）
private static final String T_TASKS = "CREATE TABLE T_TASKS(ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,ONTIME VARCAHR(6),OFFTIME VARCHAR(6)," +
"INTERVALTIME VARCHAR(5),EXECUTE VARCHAR(2),REGSIM VARCHAR(20),COSIM VARCHAR(20)";
//代办、流程跟踪信息表
private static final String T_PROCESS = "CREATE TABLE T_PROCESS(ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,SID VARCAHR(50),SPLB INT,S_SJZG VARCAHR(50)" +
",SPCS INT,SPZT INT,ADDTIME VARCHAR(20),SPTIME VARCHAR(20),SPTM VARCHAR(100),GUID VARCHAR(50),BZ VARCHAR(500),SPPERSON VARCHAR(30),JHZT INT," +
"XDR VARCHAR(20),TYPE INT,ZXZT INT";
//预警信息表
private static final String T_PERWARN = "CREATE TABLE T_PERWARN(ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,LB INT,SPID VARCAHR(50)" +
",REGGUID VARCHAR(50),ADDTIME VARCHAR(20),SJZGGUID VARCHAR(50),MS VARCHAR(500),SJZGNAME VARCHAR(30),YGNAME VARCHAR(30)";
//扩展字段值
private static final String extendValue = "REMARK1 VARCHAR(500),REMARK2 VARCHAR(500)," +
"REMARK3 VARCHAR(500),REMARK4 VARCHAR(500),REMARK5 VARCHAR(500)";
/**
* 创建或打开数据库
*/
public Optdb_interfce(Context context){
// wf = new WriteFile(context, JqydDateUtil.getDateDayTwo(new Date())+"DateBaseFile");
try {
this.db = context.openOrCreateDatabase(DATABASE_JQGJ, context.MODE_APPEND, null);
// wf.writeToFile("当前数据库地址："+this.db!=null?this.db.getPath():"数据库对象db为空");
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public void dropDb(){
try {
db.execSQL("DROP DATABASE JQGJ.db");
System.out.println("删除数据库");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
System.out.println("删除数据库异常");
}
}
/**
* 判断数据库是否已打开
* @return
*/
public boolean isOpen(){
boolean flag = false;
try {
if(db != null && db.isOpen()){
//如果游标未关闭，则关闭游标
if(cursor != null && !cursor.isClosed()){
cursor.close();
}
flag = true;
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return flag;
}
//创建数据库表
public void createTable(){
System.out.println("******************开始创建久其管家数据库******************");
try {
db.execSQL(T_COUSERS+","+extendValue+")");
db.execSQL(T_CUSTOMERS+","+extendValue+")");
db.execSQL(T_EMPS+","+extendValue+")");
db.execSQL(T_GROUPS+","+extendValue+")");
db.execSQL(T_PUBINFOS+","+extendValue+")");
db.execSQL(T_PRODUCTS+","+extendValue+")");
db.execSQL(T_DATA_UPDATES+","+extendValue+")");
db.execSQL(T_RECORDS+","+extendValue+")");
db.execSQL(T_HISTORYS+","+extendValue+")");
db.execSQL(T_VISTORS+","+extendValue+")");
db.execSQL(T_LACINFO+","+extendValue+")");
db.execSQL(T_WORKINFOS+","+extendValue+")");
db.execSQL(T_TASKS+","+extendValue+")");
db.execSQL(T_PROCESS+","+extendValue+")");
db.execSQL(T_PERWARN+","+extendValue+")");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
System.out.println("表已存在！");
}
System.out.println("****************创建久其管家数据库成功**********************");
}
public void createTable(String table){
System.out.println("******************开始创建久其管家数据库******************");
try {
db.execSQL(table+","+extendValue+")");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
System.out.println("表已存在！");
}
System.out.println("****************创建久其管家数据库成功**********************");
}
//插入数据
public void updateToDb(String tableName,ArrayList<Object> list){
try{
//写入数据前，先清空数据表（除分组信息表）
if(!tableName.equals("T_GROUPS") && !tableName.equals("T_RECORDS")){
deleteFromDb(tableName);
}
//开始写入数据
if(tableName.equals("T_COUSERS")){
CoUserModule coUser = (CoUserModule) list.get(0);
db.execSQL("INSERT INTO T_COUSERS (GGUID,SJHM,QC,JC,XT,QYDZ,XM,DISLIMIT,JCLB,XXCJLB,REMARK1) " +
"VALUES (?,?,?,?,?,?,?,?,?,?,?)",
new Object[]{coUser.getGuid(),coUser.getSjhm(),coUser.getQc(),coUser.getJc(),coUser.getXtmc(),coUser.getQydz(),coUser.getXm(),coUser.getDislimit(),coUser.getJclb(),coUser.getXxcjlb(),coUser.getTxl()});
}else if(tableName.equals("T_CUSTOMERS")){
for(int i=0;i<list.size();i++){
CustomerModule custUser = (CustomerModule) list.get(i);
db.execSQL("INSERT INTO T_CUSTOMERS (CID,CNAME,CZJM,GID,ADDTIME,LINKMAN,LINK_SIM,SHORT_NAME,KH_SORT,ADDRESS,LON,LAT,CLON,CLAT,KH_STATE,EMAIL,QQ,HONOR,DEPARTMENT,SJHM) " +
"VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
new Object[]{custUser.getCid(),custUser.getCname(),custUser.getCzjm(),custUser.getGid(),custUser.getAddtime(),custUser.getLinkman(),
custUser.getLink_sim(),custUser.getShort_name(),custUser.getKh_sort(),custUser.getAddress(),custUser.getLon(),custUser.getLat(),custUser.getClon(),
custUser.getClat(),custUser.getKh_state(),custUser.getEmail(),custUser.getQq(),custUser.getHonor(),custUser.getDepartment(),custUser.getSjhm()});
//System.out.println("客户信息----------------"+custUser.getCname()+","+custUser.getKh_sort()+","+custUser.getCid());
}
}else if(tableName.equals("T_EMPS")){
for(int i=0;i<list.size();i++){
EmpsModule emp = (EmpsModule) list.get(i);
db.execSQL("INSERT INTO T_EMPS (GUID,DWHM,XM,ZJM,GID,ZCSJ,HONOR,DEPARTMENT,EMAIL,QQ,ADDR,REMARK1,REMARK2) " +
"VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)",new Object[]{emp.getGuid(),emp.getDwhm(),emp.getXm(),emp.getZjm(),emp.getGid(),emp.getZcsj(),emp.getHonor(),emp.getDepartment(),emp.getEmail(),emp.getQq(),emp.getAddr(),emp.getDwfs(),emp.getIsshow()});
}
}else if(tableName.equals("T_GROUPS")){
boolean flag_kh = false;
boolean flag_yg = false;
boolean flag_bp = false;
boolean flag_jp = false;
for(int i=0;i<list.size();i++){
GroupsModule group = (GroupsModule) list.get(i);
//System.out.println("分组信息------------："+group.getG_sort()+"---------"+group.getGname());
//仅删除需要更新的类别的分组信息1、客户2、员工3、本品4、竞品
switch(group.getG_sort()){
case 1:
if(!flag_kh){//客户分组
db.execSQL("DELETE FROM T_GROUPS WHERE G_SORT=1");
flag_kh = true;
}
break;
case 2:
if(!flag_yg){//员工分组
db.execSQL("DELETE FROM T_GROUPS WHERE G_SORT=2");
flag_yg = true;
}
break;
case 3:
if(!flag_bp){//本品分组
db.execSQL("DELETE FROM T_GROUPS WHERE G_SORT=3");
flag_bp = true;
}
break;
case 4:
if(!flag_jp){//竞品分组
db.execSQL("DELETE FROM T_GROUPS WHERE G_SORT=4");
flag_jp = true;
}
break;
}
//删除过后再次插入
db.execSQL("INSERT INTO T_GROUPS (GID,GNAME,G_PID,G_SORT) " +
"VALUES (?,?,?,?)",new Object[]{group.getGid(),group.getGname(),group.getG_pid(),group.getG_sort()});
}
}else if(tableName.equals("T_PUBINFOS")){
for(int i=0;i<list.size();i++){
boolean flag = false;
PublicInfoModule pubinfo = (PublicInfoModule) list.get(i);
//检测公告或通知信息是否已经存在，如果存在则不再插入表中
cursor = db.rawQuery("SELECT * FROM T_PUBINFOS WHERE GID=?",new String[]{pubinfo.getGid()+""});
while(cursor.moveToNext()){
flag = true;
break;
}
//不存在
if(!flag){
db.execSQL("INSERT INTO T_PUBINFOS (GID,TITLE,CONTENT,G_COSIM,ADDTIME,G_SORT,STATE) " +
"VALUES (?,?,?,?,?,?,?)",new Object[]{pubinfo.getGid(),pubinfo.getTitle(),pubinfo.getContent(),pubinfo.getAdduser(),
pubinfo.getAdd_time(),pubinfo.getType(),pubinfo.getState()});
}
}
}else if(tableName.equals("T_PRODUCTS")){
for(int i=0;i<list.size();i++){
ProTypeModule pro_type = (ProTypeModule) list.get(i);
db.execSQL("INSERT INTO T_PRODUCTS (PID,TID,P_NAME,P_TYPE,P_SORT,P_STATUS,P_UNIT,P_CBJ,P_PFJ,P_LSJ,P_FACTORY,P_ADDR,P_SALER,P_BZ,P_ADDTIME,P_LB,GID,P_ZJM) " +
"VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",new Object[]{pro_type.getId(),pro_type.getCode(),pro_type.getName(),pro_type.getModel(),pro_type.getType(),
pro_type.getStatus(),pro_type.getUnit(),pro_type.getCbPrise(),pro_type.getPfPrise(),pro_type.getLsPrise(),pro_type.getProducer(),pro_type.getOrigin(),
pro_type.getJxs(),pro_type.getDescription(),pro_type.getAddtime(),pro_type.getIsbq(),pro_type.getLbid(),pro_type.getZjm()});
}
}else if(tableName.equals("T_RECORDS")){
System.out.println("插入失败记录***************************"+list.size());
RecordModel record = (RecordModel) list.get(0);
System.out.println("插入失败记录时间***************************"+record.getAdd_time());
db.execSQL("INSERT INTO T_RECORDS (GID,CUSTID,LON,LAT,ADD_TIME,CONTENT,JCLB,XXCJLB,WZBS,PIC_DETAIL,PIC_URL,BZ,RESULT,CID,LAC,NCODE,CCODE,XHQD,RADIUS,REMARK1,REMARK2,REMARK3,REMARK4,REMARK5) " +
"VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",new Object[]{record.getGid(),record.getCustid(),record.getLon(),record.getLat(),
record.getAdd_time(),record.getContent(),record.getJclb(),record.getXxcjlb(),record.getWzbs(),record.getPic_detail(),record.getPic_url(),
record.getBz(),record.getResult(),record.getCid(),record.getLac(),record.getNcode(),record.getCcode(),record.getXhqd(),record.getRaduis(),
record.getRemark1(),record.getRemark2(),record.getRemark3(),record.getRemark4(),record.getRemark5()});
}else if(tableName.equals("T_VISTORS")){
db.execSQL("INSERT INTO T_VISTORS (CID,POINT) " +
"VALUES (?,?)",new Object[]{});
}else if(tableName.equals("T_LACINFO")){
for(int i=0;i<list.size();i++){
LocationModule loc = (LocationModule) list.get(i);
db.execSQL("INSERT INTO T_LACINFO (CID,CELL_ID,LAC,CCODE,NCODE,XHQD,RADIUS,LOC_TYPE,REMARK1,REMARK2) " +
"VALUES (?,?,?,?,?,?,?,?,?,?)",new Object[]{loc.getCid(),loc.getCell_id(),loc.getLac(),loc.getCcode(),loc.getNcode(),loc.getXhqd(),
loc.getRadius(),loc.getLoc_type(),loc.getLon(),loc.getLat()});
}
}else if(tableName.equals("T_WORKINFOS")){
for(int i=0;i<list.size();i++){
PublicInfoModule pubinfo = (PublicInfoModule) list.get(i);
db.execSQL("INSERT INTO T_WORKINFOS (GID,TITLE,CONTENT,G_COSIM,ADDTIME,G_SORT,STATE) " +
"VALUES (?,?,?,?,?,?,?)",new Object[]{pubinfo.getGid(),pubinfo.getTitle(),pubinfo.getContent(),pubinfo.getAdduser(),
pubinfo.getAdd_time(),pubinfo.getType(),pubinfo.getState()});
}
}else if(tableName.equals("T_PROCESS")){
for(int i=0;i<list.size();i++){
DbsxLcgzModel process = (DbsxLcgzModel) list.get(i);
db.execSQL("INSERT INTO T_PROCESS (SID,SPLB,S_SJZG,SPCS,SPZT,ADDTIME,SPTIME,SPTM,GUID,BZ,SPPERSON,JHZT,XDR,TYPE,ZXZT) " +
"VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
new Object[]{process.getSpid(),process.getSplb(),process.getSjzgguid(),process.getSpcs(),process.getSpzt(),process.getAddtime(),
process.getSptime(),process.getSptm(),process.getRegguid(),process.getBz(),process.getSpperson(),process.getJhzt(),process.getXdr(),process.getType(),process.getZxzt()});
System.out.println("待办事项、流程跟踪校验----------"+process.getSptm()+","+process.getSpid()+","+process.getType());
}
}else if(tableName.equals("T_PERWARN")){
for(int i=0;i<list.size();i++){
YjxxModel yjxx = (YjxxModel) list.get(i);
db.execSQL("INSERT INTO T_PERWARN (LB,SPID,REGGUID,ADDTIME,SJZGGUID,MS,SJZGNAME,YGNAME) " +
"VALUES (?,?,?,?,?,?,?,?)",
new Object[]{yjxx.getLb(),yjxx.getSpid(),yjxx.getGuid(),yjxx.getZjsj(),
yjxx.getSjzgguid(),yjxx.getMs(),yjxx.getSjzgname(),yjxx.getYgname()});
}
}
}catch(Exception e){
e.printStackTrace();
if(e.getMessage().contains("no such")){
if(tableName.equals("T_LACINFO")){
createTable(T_LACINFO);
updateToDb(tableName,list);
}
}
}
}
/**
* 更新时间节点
* @param optName
* @param timeNode
*/
public void updateTimeNode(String optName,String timeNode){
System.out.println("更新时间节点------------"+optName+","+timeNode);
if(optName.equals("ALL")){
db.execSQL("INSERT INTO T_DATA_UPDATES (DATATYPE,UTIME) VALUES (?,?)",new Object[]{"COUSERS",timeNode});
db.execSQL("INSERT INTO T_DATA_UPDATES (DATATYPE,UTIME) VALUES (?,?)",new Object[]{"CUSTOMERS",timeNode});
db.execSQL("INSERT INTO T_DATA_UPDATES (DATATYPE,UTIME) VALUES (?,?)",new Object[]{"EMPS",timeNode});
db.execSQL("INSERT INTO T_DATA_UPDATES (DATATYPE,UTIME) VALUES (?,?)",new Object[]{"EGROUPS",timeNode});
db.execSQL("INSERT INTO T_DATA_UPDATES (DATATYPE,UTIME) VALUES (?,?)",new Object[]{"PUBINFOS",timeNode});
db.execSQL("INSERT INTO T_DATA_UPDATES (DATATYPE,UTIME) VALUES (?,?)",new Object[]{"PRODUCTS",timeNode});
db.execSQL("INSERT INTO T_DATA_UPDATES (DATATYPE,UTIME) VALUES (?,?)",new Object[]{"LACINFO",timeNode});
db.execSQL("INSERT INTO T_DATA_UPDATES (DATATYPE,UTIME) VALUES (?,?)",new Object[]{"YJXX",timeNode});
db.execSQL("INSERT INTO T_DATA_UPDATES (DATATYPE,UTIME) VALUES (?,?)",new Object[]{"LXSB",timeNode});
db.execSQL("INSERT INTO T_DATA_UPDATES (DATATYPE,UTIME) VALUES (?,?)",new Object[]{"DBSX",timeNode});
db.execSQL("INSERT INTO T_DATA_UPDATES (DATATYPE,UTIME) VALUES (?,?)",new Object[]{"LCGZ",timeNode});
db.execSQL("INSERT INTO T_DATA_UPDATES (DATATYPE,UTIME) VALUES (?,?)",new Object[]{"JYGK",timeNode});
db.execSQL("INSERT INTO T_DATA_UPDATES (DATATYPE,UTIME) VALUES (?,?)",new Object[]{"CGROUPS",timeNode});
db.execSQL("INSERT INTO T_DATA_UPDATES (DATATYPE,UTIME) VALUES (?,?)",new Object[]{"PGROUPS",timeNode});
db.execSQL("INSERT INTO T_DATA_UPDATES (DATATYPE,UTIME) VALUES (?,?)",new Object[]{"JGROUPS",timeNode});
db.execSQL("INSERT INTO T_DATA_UPDATES (DATATYPE,UTIME) VALUES (?,?)",new Object[]{"JP",timeNode});
db.execSQL("INSERT INTO T_DATA_UPDATES (DATATYPE,UTIME) VALUES (?,?)",new Object[]{"YWTZ",timeNode});
}else if(optName.equals("COUSERS")){
db.execSQL("UPDATE T_DATA_UPDATES SET UTIME = '"+timeNode+"' WHERE DATATYPE = 'COUSERS'");
}else if(optName.equals("CUSTOMERS")){
//System.out.println("客户---------------------------------------------");
db.execSQL("UPDATE T_DATA_UPDATES SET UTIME = '"+timeNode+"' WHERE DATATYPE = 'CUSTOMERS'");
}else if(optName.equals("EMPS")){
db.execSQL("UPDATE T_DATA_UPDATES SET UTIME = '"+timeNode+"' WHERE DATATYPE = 'EMPS'");
}else if(optName.equals("PUBINFOS")){
db.execSQL("UPDATE T_DATA_UPDATES SET UTIME = '"+timeNode+"' WHERE DATATYPE = 'PUBINFOS'");
}else if(optName.equals("LACINFO")){
db.execSQL("UPDATE T_DATA_UPDATES SET UTIME = '"+timeNode+"' WHERE DATATYPE = 'LACINFO'");
}else if(optName.equals("YJXX")){
db.execSQL("UPDATE T_DATA_UPDATES SET UTIME = '"+timeNode+"' WHERE DATATYPE = 'YJXX'");
}else if(optName.equals("LXSB")){
db.execSQL("UPDATE T_DATA_UPDATES SET UTIME = '"+timeNode+"' WHERE DATATYPE = 'LXSB'");
}else if(optName.equals("DBSX")){
db.execSQL("UPDATE T_DATA_UPDATES SET UTIME = '"+timeNode+"' WHERE DATATYPE = 'DBSX'");
}else if(optName.equals("LCGZ")){
db.execSQL("UPDATE T_DATA_UPDATES SET UTIME = '"+timeNode+"' WHERE DATATYPE = 'LCGZ'");
}else if(optName.equals("JYGK")){
db.execSQL("UPDATE T_DATA_UPDATES SET UTIME = '"+timeNode+"' WHERE DATATYPE = 'JYGK'");
}
}
/**
* 更新分组时间节点
* @param type
* @param timeNode
*/
public void updateGTimeNode(String optName,int type,String timeNode){
if(optName.equals("GROUP")){
switch(type){
case 1:
db.execSQL("UPDATE T_DATA_UPDATES SET UTIME = '"+timeNode+"' WHERE DATATYPE = 'CGROUPS'");
break;
case 2:
db.execSQL("UPDATE T_DATA_UPDATES SET UTIME = '"+timeNode+"' WHERE DATATYPE = 'EGROUPS'");
break;
case 3:
db.execSQL("UPDATE T_DATA_UPDATES SET UTIME = '"+timeNode+"' WHERE DATATYPE = 'PGROUPS'");
break;
case 4:
db.execSQL("UPDATE T_DATA_UPDATES SET UTIME = '"+timeNode+"' WHERE DATATYPE = 'JGROUPS'");
break;
}
}else if(optName.equals("PRODUCT")){
switch(type){
case 3:
db.execSQL("UPDATE T_DATA_UPDATES SET UTIME = '"+timeNode+"' WHERE DATATYPE = 'PRODUCTS'");
break;
case 4:
db.execSQL("UPDATE T_DATA_UPDATES SET UTIME = '"+timeNode+"' WHERE DATATYPE = 'JP'");
break;
}
}else if(optName.equals("PUBINFO")){
switch(type){
case 1:
db.execSQL("UPDATE T_DATA_UPDATES SET UTIME = '"+timeNode+"' WHERE DATATYPE = 'PUBINFOS'");
break;
case 2:
db.execSQL("UPDATE T_DATA_UPDATES SET UTIME = '"+timeNode+"' WHERE DATATYPE = 'YWTZ'");
break;
}
}else if(optName.equals("PROCESS")){
switch(type){
case 1:
db.execSQL("UPDATE T_DATA_UPDATES SET UTIME = '"+timeNode+"' WHERE DATATYPE = 'DBSX'");
break;
case 2:
db.execSQL("UPDATE T_DATA_UPDATES SET UTIME = '"+timeNode+"' WHERE DATATYPE = 'LCGZ'");
break;
}
}
}
/**
* 更新历史记录表
* @param gid
* @param zid
* @param content
*/
public void updateHistory(int gid,String zid,String content){
System.out.println("---------------------------历史记录记录写入-------------------------------");
boolean flag_content = false;
int count = 0;
try{
//1、查询是否内容已经存在，如果存在则不插入
cursor = db.rawQuery("SELECT * FROM T_HISTORYS WHERE GID = ? AND ZID = ? AND CONTENT = ?",new String[]{gid+"",zid,content});
while(cursor.moveToNext()){
flag_content = true;
}
//2、查询是否历史保存记录已经超过十条，如果超过则更新时间最旧的一条数据
cursor = db.rawQuery("SELECT COUNT(ID) COUNT FROM T_HISTORYS WHERE GID = ? AND ZID = ?",new String[]{gid+"",zid});
while(cursor.moveToNext()){
count = Integer.parseInt(cursor.getString(cursor.getColumnIndex("COUNT")));
}
System.out.println("历史记录保存-------------"+flag_content+"------------"+count);
if(!flag_content && count<10){//执行插入操作
Date date = new Date();
String nowDate= new SimpleDateFormat("yyyy-MM-dd HH:mm").format(date);
db.execSQL("INSERT INTO T_HISTORYS (GID,ZID,ADD_TIME,CONTENT) " +
"VALUES (?,?,?,?)",new Object[]{gid,zid,nowDate,content});
}else if(!flag_content && count>=10){//执行更新记录操作
System.out.println("---------------------------执行更新历史记录-------------------------------");
Date date = new Date();
String nowDate= new SimpleDateFormat("yyyy-MM-dd HH:mm").format(date);
System.out.println(nowDate);
// --日期有问题************************************************************************8
//更新时间最老的记录
db.execSQL("UPDATE T_HISTORYS SET ADD_TIME='"+nowDate+"',CONTENT='"+content+"' WHERE GID="+gid+" AND ZID='"+zid+"' AND ADD_TIME IN " +
"(SELECT MIN(ADD_TIME) FROM T_HISTORYS WHERE GID="+gid+" AND ZID='"+zid+"')");
}
}catch(Exception e){
if(e.getMessage().equals("no such table")){
createTable(T_HISTORYS);
updateHistory(gid,zid,content);
}
}
}
/**
* 查询历史记录信息
* @param gid
* @param zid
* @return
*/
public ArrayList<String> searchHistory(int gid,String zid){
ArrayList<String> list = new ArrayList<String>();
try{
cursor = db.rawQuery("SELECT * FROM T_HISTORYS WHERE GID = ? AND ZID = ? ORDER BY ADD_TIME DESC",new String[]{gid+"",zid});
while(cursor.moveToNext()){
list.add(cursor.getString(cursor.getColumnIndex("CONTENT")));
//System.out.println("查询历史记录---"+cursor.getString(cursor.getColumnIndex("ADD_TIME"))+"---"+cursor.getString(cursor.getColumnIndex("CONTENT")));
}
}catch(SQLiteException e){
if(e.getMessage().contains("no such table")){
createTable(T_HISTORYS);
searchHistory(gid,zid);
}
}catch(Exception e){
e.printStackTrace();
}
return list;
}
public void deleteFromDb(String param){
System.out.println("******************删除数据表中的数据******************"+param);
try {
if(param.equals("ALL")){
db.execSQL("DELETE FROM T_COUSERS");
db.execSQL("DELETE FROM T_CUSTOMERS");
db.execSQL("DELETE FROM T_EMPS");
db.execSQL("DELETE FROM T_GROUPS");
db.execSQL("DELETE FROM T_PUBINFOS");
db.execSQL("DELETE FROM T_PRODUCTS");
db.execSQL("DELETE FROM T_DATA_UPDATES");
db.execSQL("DELETE FROM T_RECORDS");
db.execSQL("DELETE FROM T_HISTORYS");
db.execSQL("DELETE FROM T_VISTORS");
db.execSQL("DELETE FROM T_LACINFO");
db.execSQL("DELETE FROM T_WORKINFOS");
db.execSQL("DELETE FROM T_TASKS");
db.execSQL("DELETE FROM T_PROCESS");
db.execSQL("DELETE FROM T_PERWARN");
}else if(param.equals("T_COUSERS")){
db.execSQL("DELETE FROM T_COUSERS");
}else if(param.equals("T_CUSTOMERS")){
db.execSQL("DELETE FROM T_CUSTOMERS");
}else if(param.equals("T_EMPS")){
db.execSQL("DELETE FROM T_EMPS");
}else if(param.equals("T_GROUPS")){
db.execSQL("DELETE FROM T_GROUPS");
}if(param.equals("T_PRODUCTS")){
db.execSQL("DELETE FROM T_PRODUCTS");
}else if(param.equals("T_LACINFO")){
db.execSQL("DELETE FROM T_LACINFO");
}else if(param.equals("T_WORKINFOS")){
db.execSQL("DELETE FROM T_WORKINFOS");
}else if(param.equals("T_PROCESS")){
db.execSQL("DELETE FROM T_PROCESS");
}else if(param.equals("T_PERWARN")){
db.execSQL("DELETE FROM T_PERWARN");
}else if(param.equals("T_RECORDS")){
db.execSQL("DELETE FROM T_RECORDS");
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* 删除表结构
* @param tableName
*/
public void dropDb(String tableName){
System.out.println("-----------------删除表结构-----------------");
try {
if(tableName.equals("T_DATA_UPDATES")){
db.execSQL("DELETE FROM T_DATA_UPDATES");
}
if(tableName.equals("ALL")){
db.execSQL("DROP TABLE T_COUSERS");
db.execSQL("DROP TABLE T_CUSTOMERS");
db.execSQL("DROP TABLE T_EMPS");
db.execSQL("DROP TABLE T_GROUPS");
db.execSQL("DROP TABLE T_PUBINFOS");
db.execSQL("DROP TABLE T_PRODUCTS");
db.execSQL("DROP TABLE T_DATA_UPDATES");
db.execSQL("DROP TABLE T_RECORDS");
db.execSQL("DROP TABLE T_HISTORYS");
db.execSQL("DROP TABLE T_VISTORS");
db.execSQL("DROP TABLE T_LACINFO");
db.execSQL("DROP TABLE T_WORKINFOS");
db.execSQL("DROP TABLE T_TASKS");
db.execSQL("DROP TABLE T_PROCESS");
db.execSQL("DROP TABLE T_PERWARN");
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
System.out.println("---------------删除表结构异常---------------");
}
}
/**
* 删除连续上报历史保存任务
* @param tableName
*/
public void deleteToTask( int limit){
if(limit!=0){
db.execSQL("DELETE FROM T_RECORDS WHERE ID IN (SELECT ID FROM T_RECORDS WHERE GID = 999 ORDER BY ID ASC LIMIT "+limit+")");
}else{db.execSQL("DELETE FROM T_RECORDS WHERE GID = 999");}
}
/**
* 查询连续上报任务时间
* @return
*/
public String searchTaskTime(){
String taskTime = "";
cursor = db.rawQuery("SELECT * FROM T_DATA_UPDATES WHERE DATATYPE = 'LXSB'",null);
while(cursor.moveToNext()){
taskTime = cursor.getString(cursor.getColumnIndex("UTIME"));
}
return taskTime;
}
/**
* 查询数据库表，获得相应的数据
* @param tableName
* @return
*/
public ArrayList<Object> searchFromDb(String dataType){
//System.out.println("查找数据更新---------"+dataType);
ArrayList<Object> list = new ArrayList<Object>();
if(dataType.equals("DATA_UPDATES")){//获得数据更新的时间节点
ArrayList<String> selList = new ArrayList<String>();
selList.add("COUSERS");
selList.add("CUSTOMERS");
selList.add("EMPS");
selList.add("PUBINFOS");
selList.add("EGROUPS");
selList.add("PRODUCTS");
selList.add("LACINFO");
selList.add("LXSB");
selList.add("YJXX");
selList.add("DBSX");
selList.add("LCGZ");
selList.add("JYGK");
selList.add("CGROUPS");
selList.add("PGROUPS");
selList.add("JGROUPS");
selList.add("JP");
selList.add("YWTZ");
for(int i=0;i<selList.size();i++){
//容错处理，删除重复数据，后期可以把此语句删除********************************************************更新版本 去掉此语句--1.1.3
//db.execSQL("DELETE FROM T_DATA_UPDATES WHERE ID = (SELECT MIN(ID) FROM T_DATA_UPDATES WHERE DATATYPE='COUSERS' GROUP BY DATATYPE HAVING COUNT(DATATYPE)>1)") ;
//************************************************
//System.out.println("是否会出错");
cursor = db.rawQuery("SELECT * FROM T_DATA_UPDATES WHERE DATATYPE = ?",new String[]{selList.get(i)});
while(cursor.moveToNext()){
list.add(cursor.getString(cursor.getColumnIndex("UTIME")));//sqlite3是否提供了内置转换功能，直接将日期格式数据以字符串格式提取出来
System.out.println("--------时间参数---------"+cursor.getString(cursor.getColumnIndex("UTIME"))+"-------"+cursor.getString(cursor.getColumnIndex("DATATYPE")));
}
}
}else if(dataType.equals("COUSERS")){//获得管理员信息
cursor = db.rawQuery("SELECT * FROM T_COUSERS",null);
while(cursor.moveToNext()){
CoUserModule coUser = new CoUserModule();
coUser.setGuid(cursor.getString(cursor.getColumnIndex("GGUID")));
coUser.setDislimit(cursor.getString(cursor.getColumnIndex("DISLIMIT")));
coUser.setJclb(cursor.getString(cursor.getColumnIndex("JCLB")));
coUser.setXxcjlb(cursor.getString(cursor.getColumnIndex("XXCJLB")));
coUser.setSjhm(cursor.getString(cursor.getColumnIndex("SJHM")));
coUser.setTxl(cursor.getString(cursor.getColumnIndex("REMARK1")));
list.add(coUser);
//System.out.println("误差距离---------"+cursor.getString(cursor.getColumnIndex("DISLIMIT")));
}
}else if(dataType.equals("PUBINFOS")){
cursor = db.rawQuery("SELECT * FROM T_PUBINFOS ORDER BY ADDTIME COLLATE NOCASE DESC",null);
while(cursor.moveToNext()){
PublicInfoModule pubInfo = new PublicInfoModule();
pubInfo.setAdd_time(cursor.getString(cursor.getColumnIndex("ADDTIME")));
pubInfo.setAdduser(cursor.getString(cursor.getColumnIndex("G_COSIM")));
pubInfo.setContent(cursor.getString(cursor.getColumnIndex("CONTENT")));
pubInfo.setGid(Integer.parseInt(cursor.getString(cursor.getColumnIndex("GID"))));
pubInfo.setState(Integer.parseInt(cursor.getString(cursor.getColumnIndex("STATE"))));
pubInfo.setTitle(cursor.getString(cursor.getColumnIndex("TITLE")));
pubInfo.setType(Integer.parseInt(cursor.getString(cursor.getColumnIndex("G_SORT"))));
list.add(pubInfo);
}
}
return list;
}
/**
* 获得数据重传记录
* @param gid
* 功能标识1、签到2、签退3、我的日志4、我的请假5、我的计划6、我的差旅7、信息反馈8、订单上报9、销售上报10、竞品上报11、客户拜访12、市场检查13、客户采集999、数据重传等
* @return
*/
public ArrayList<Object> getRecordInfo(int gid){
ArrayList<Object> list = new ArrayList<Object>();
// try{
String sql = "SELECT * FROM T_RECORDS";
if(gid ==0){
//查询全部
}else if(gid==-1){
//查询非定时上报的数据,数据重传模块
sql += " WHERE GID != 999";
}else{
sql += " WHERE GID = "+gid+"";
}
cursor = db.rawQuery(sql,null);
while(cursor.moveToNext()){
System.out.println("----------------------------有保存数据----------------------------"+cursor.getString(cursor.getColumnIndex("GID")));
RecordModel record = new RecordModel();
record.setGid(cursor.getString(cursor.getColumnIndex("GID")));
record.setAdd_time(cursor.getString(cursor.getColumnIndex("ADD_TIME")));
record.setBz(cursor.getString(cursor.getColumnIndex("BZ")));
record.setCcode(cursor.getString(cursor.getColumnIndex("CCODE")));
record.setCid(cursor.getString(cursor.getColumnIndex("CID")));
record.setContent(cursor.getString(cursor.getColumnIndex("CONTENT")));
record.setCustid(cursor.getString(cursor.getColumnIndex("CUSTID")));
record.setJclb(cursor.getString(cursor.getColumnIndex("JCLB")));
record.setLac(cursor.getString(cursor.getColumnIndex("LAC")));
record.setLat(cursor.getString(cursor.getColumnIndex("LAT")));
record.setLon(cursor.getString(cursor.getColumnIndex("LON")));
record.setNcode(cursor.getString(cursor.getColumnIndex("NCODE")));
record.setPic_detail(cursor.getString(cursor.getColumnIndex("PIC_DETAIL")));
record.setPic_url(cursor.getString(cursor.getColumnIndex("PIC_URL")));
record.setRaduis(cursor.getString(cursor.getColumnIndex("RADIUS")));
record.setResult(cursor.getString(cursor.getColumnIndex("RESULT")));
record.setWzbs(cursor.getString(cursor.getColumnIndex("WZBS")));
record.setXhqd(cursor.getString(cursor.getColumnIndex("XHQD")));
record.setXxcjlb(cursor.getString(cursor.getColumnIndex("XXCJLB")));
record.setJiange(cursor.getString(cursor.getColumnIndex("RESULT")));//存放间隔时间，用于连续上报！！！
record.setRemark1(cursor.getString(cursor.getColumnIndex("REMARK1")));
record.setRemark2(cursor.getString(cursor.getColumnIndex("REMARK2")));
record.setRemark3(cursor.getString(cursor.getColumnIndex("REMARK3")));
record.setRemark4(cursor.getString(cursor.getColumnIndex("REMARK4")));
record.setRemark5(cursor.getString(cursor.getColumnIndex("REMARK5")));
record.setId(cursor.getString(cursor.getColumnIndex("ID")));
list.add(record);
}
// }catch(SQLException e){
// if(e.getMessage().contains("no such table")){
// createTable(T_RECORDS);
// getRecordInfo(gid);
// }
// }catch(Exception e){
//
// }
return list;
}
/**
* 根据标识删除相应记录
* @param ids
*/
public void delRecordByIds(String ids){
db.execSQL("DELETE FROM T_RECORDS WHERE ID IN ("+ids+")");
}
/**
* 获得公告、通知、代办事项、流程跟踪等信息
* @param tableName
* @param type
* @return
*/
public ArrayList<Object> searchPubinfos(String dataType,int type){
ArrayList<Object> list = new ArrayList<Object>();
System.out.println("-----------"+type);
if(dataType.equals("PUBINFOS")){
cursor = db.rawQuery("SELECT * FROM T_PUBINFOS WHERE G_SORT=? ORDER BY ADDTIME COLLATE NOCASE DESC",new String[]{type+""});
while(cursor.moveToNext()){
PublicInfoModule pubInfo = new PublicInfoModule();
pubInfo.setAdd_time(cursor.getString(cursor.getColumnIndex("ADDTIME")));
pubInfo.setAdduser(cursor.getString(cursor.getColumnIndex("G_COSIM")));
pubInfo.setContent(cursor.getString(cursor.getColumnIndex("CONTENT")));
pubInfo.setGid(Integer.parseInt(cursor.getString(cursor.getColumnIndex("GID"))));
pubInfo.setState(Integer.parseInt(cursor.getString(cursor.getColumnIndex("STATE"))));
pubInfo.setTitle(cursor.getString(cursor.getColumnIndex("TITLE")));
pubInfo.setType(Integer.parseInt(cursor.getString(cursor.getColumnIndex("G_SORT"))));
list.add(pubInfo);
//System.out.println("----------"+cursor.getString(cursor.getColumnIndex("ADDTIME"))+"--"+cursor.getString(cursor.getColumnIndex("TITLE"))+"--"+cursor.getString(cursor.getColumnIndex("G_SORT")));
}
}else if(dataType.equals("GROUPS")){
cursor = db.rawQuery("SELECT GID,GPID,GNAME FROM T_GROUPS WHERE G_SORT=? AND G_PID=? ",new String[]{type+"","0"});
while(cursor.moveToNext()){
GroupsModule group = new GroupsModule();
group.setGid(Integer.parseInt(cursor.getString(cursor.getColumnIndex("GID"))));
group.setGname(cursor.getString(cursor.getColumnIndex("GNAME")));
group.setG_pid(Integer.parseInt(cursor.getString(cursor.getColumnIndex("G_PID"))));
}
}
return list;
}
/**
* 查询代办事项、流程跟踪等数据
* @param type
* @param dbNum
* @param spzt
* @return
*/
public ArrayList<DbsxLcgzModel> getProcessBySelect(int type,String dbNum){
ArrayList<DbsxLcgzModel> list = new ArrayList<DbsxLcgzModel>();
String sql = "SELECT * FROM T_PROCESS WHERE TYPE = "+type+" AND (SPZT=0 OR (SPZT = 1 AND SPLB = 3))";
if(!dbNum.equals("")){
sql+=" AND SID = '"+dbNum+"'";
}
//cursor = db.rawQuery("SELECT * FROM T_PROCESS WHERE sid = ? ",new String[]{dbNum});
cursor = db.rawQuery(sql,null);
while(cursor.moveToNext()){
DbsxLcgzModel process = new DbsxLcgzModel();
process.setAddtime(cursor.getString(cursor.getColumnIndex("ADDTIME")));
process.setBz(cursor.getString(cursor.getColumnIndex("BZ")));
process.setJhzt(cursor.getString(cursor.getColumnIndex("JHZT")));
process.setRegguid(cursor.getString(cursor.getColumnIndex("GUID")));
process.setSjzgguid(cursor.getString(cursor.getColumnIndex("S_SJZG")));
process.setSpcs(cursor.getString(cursor.getColumnIndex("SPCS")));
process.setSpid(cursor.getString(cursor.getColumnIndex("SID")));
process.setSplb(cursor.getString(cursor.getColumnIndex("SPLB")));
process.setSpperson(cursor.getString(cursor.getColumnIndex("SPPERSON")));
process.setSptime(cursor.getString(cursor.getColumnIndex("SPTIME")));
process.setSptm(cursor.getString(cursor.getColumnIndex("SPTM")));
process.setSpzt(cursor.getString(cursor.getColumnIndex("SPZT")));
process.setXdr(cursor.getString(cursor.getColumnIndex("XDR")));
process.setZxzt(cursor.getString(cursor.getColumnIndex("ZXZT")));
list.add(process);
//System.out.println("获得代办事项信息-------------"+cursor.getString(cursor.getColumnIndex("SPTM"))+","+cursor.getString(cursor.getColumnIndex("SPZT")));
}
return list;
}
/**
* 查询预警信息
* @return
*/
public ArrayList<YjxxModel> getYjxxInfo(String spid){
ArrayList<YjxxModel> list = new ArrayList<YjxxModel>();
String sql = "SELECT * FROM T_PERWARN";
if(!spid.equals("")){
sql+=" SPID = '"+spid+"'";
}
sql+=" ORDER BY ADDTIME COLLATE NOCASE DESC";
cursor = db.rawQuery(sql,null);
while(cursor.moveToNext()){
YjxxModel yjxx = new YjxxModel();
yjxx.setGuid(cursor.getString(cursor.getColumnIndex("REGGUID")));
yjxx.setLb(cursor.getString(cursor.getColumnIndex("LB")));
yjxx.setMs(cursor.getString(cursor.getColumnIndex("MS")));
yjxx.setSjzgguid(cursor.getString(cursor.getColumnIndex("SJZGGUID")));
yjxx.setSjzgname(cursor.getString(cursor.getColumnIndex("SJZGNAME")));
yjxx.setSpid(cursor.getString(cursor.getColumnIndex("SPID")));
yjxx.setYgname(cursor.getString(cursor.getColumnIndex("YGNAME")));
yjxx.setZjsj(cursor.getString(cursor.getColumnIndex("ADDTIME")));
list.add(yjxx);
}
return list;
}
/**
* 更新审批状态
* @param spid
*/
public void updProcessState(String spid,int value){
db.execSQL("UPDATE T_PROCESS SET SPZT="+value+" WHERE SID='"+spid+"'");
}
/**
* 获得公告、通知等信息
* @param select1、未读2、已读
* @return
*/
public ArrayList<Object> searchPubinfos_new(String select){
ArrayList<Object> list = new ArrayList<Object>();
if(select.equals("")){
cursor = db.rawQuery("SELECT * FROM T_PUBINFOS ORDER BY ADDTIME COLLATE NOCASE DESC",null);
}else{
cursor = db.rawQuery("SELECT * FROM T_PUBINFOS WHERE state=? ORDER BY ADDTIME COLLATE NOCASE DESC",new String[]{select});
}
while(cursor.moveToNext()){
PublicInfoModule pubInfo = new PublicInfoModule();
pubInfo.setAdd_time(cursor.getString(cursor.getColumnIndex("ADDTIME")));
pubInfo.setAdduser(cursor.getString(cursor.getColumnIndex("G_COSIM")));
pubInfo.setContent(cursor.getString(cursor.getColumnIndex("CONTENT")));
pubInfo.setGid(Integer.parseInt(cursor.getString(cursor.getColumnIndex("GID"))));
pubInfo.setState(Integer.parseInt(cursor.getString(cursor.getColumnIndex("STATE"))));
pubInfo.setTitle(cursor.getString(cursor.getColumnIndex("TITLE")));
pubInfo.setType(Integer.parseInt(cursor.getString(cursor.getColumnIndex("G_SORT"))));
list.add(pubInfo);
//System.out.println("----------"+cursor.getString(cursor.getColumnIndex("ADDTIME"))+"--"+cursor.getString(cursor.getColumnIndex("TITLE"))+"--"+cursor.getString(cursor.getColumnIndex("G_SORT")));
}
return list;
}
/**
* 获得分组信息---------
* @param tableName
* @param type
* @return
*/
public String RecursionGroup(String pid,int level,int type,int num){
ArrayList<Object> list = new ArrayList<Object>();
String result = "";
if(num == 0){
list = searchGroups(pid,type);
}else{
list = searchFGroups(pid,type);
}
for(int i=0;i<list.size();i++){
num++;
GroupsModule group = (GroupsModule) list.get(i);
String gid = group.getGid()+"";
String gname = group.getGname();
int g_pid = group.getG_pid();
if(searchFGroups(gid,2).isEmpty()){
// System.out.println("没有子节点------"+gid);
group_flag +=gid+","+level+","+gname+"#";
}else{
// System.out.println("存在子节点------"+gid);
group_flag +=gid+","+level+","+gname+"#";
RecursionGroup(gid,(level+1),2,num);
}
}
//System.out.println("分组数据-----------------------"+group_flag);
if(group_flag.equals("")){
result = "";
}else{
result = group_flag.substring(0, group_flag.length()-1);
}
return result;
}
/**
* 根据管理范围逆推获得分组信息
* @param tableName
* @param type
* @return
*/
public String RecursionGroup(String pid,int type){
ArrayList<Object> list = new ArrayList<Object>();
String result = "";
list = searchGroups(pid,type);
for(int i=0;i<list.size();i++){
GroupsModule group = (GroupsModule) list.get(i);
String gid = group.getGid()+"";
String g_pid = group.getG_pid()+"";
if(g_pid.equals("0")){
group_flag +=gid+",";
System.out.println("父节点");
}else{
group_flag +=gid+",";
RecursionGroup(g_pid,2);
}
}
if(group_flag.equals("")){
result = "";
}else{
result = group_flag.substring(0, group_flag.length()-1);
}
System.out.println("分组数据-----------------------"+result);
return result;
}
/**
* 根据分组父节点查询分组信息
* @param pid
* @param type
* @return
*/
public ArrayList<Object> searchFGroups(String pid,int type){
Log.e("xiao","客户分组条件---------------------"+pid+"-------"+type);
System.out.println("客户分组条件---------------------"+pid+"-------"+type);
ArrayList<Object> list = new ArrayList<Object>();
try{
cursor = db.rawQuery("SELECT GID,G_PID,GNAME FROM T_GROUPS WHERE G_PID IN ("+pid+") and G_SORT="+type+"",null);
while(cursor.moveToNext()){
GroupsModule group = new GroupsModule();
group.setGid(Integer.parseInt(cursor.getString(cursor.getColumnIndex("GID"))));
group.setGname(cursor.getString(cursor.getColumnIndex("GNAME")));
group.setG_pid(Integer.parseInt(cursor.getString(cursor.getColumnIndex("G_PID"))));
list.add(group);
Log.e("xiao", "分组测试--------"+group.getGid()+"----"+group.getGname()+"----"+group.getG_pid());
System.out.println("分组测试--------"+group.getGid()+"----"+group.getGname()+"----"+group.getG_pid());
}
}catch(Exception e){}
finally{
cursor.close();
}
return list;
}
/**
* 根据分组子节点查询分组信息
* @param pid
* @param type
* @return
*/
GroupsModule gm;
public ArrayList<Object> searchGroups(String pid,int type){
Log.e("xiao", "客户分组条件---------------------"+pid+"-------"+type);
System.out.println("客户分组条件---------------------"+pid+"-------"+type);
ArrayList<Object> list = new ArrayList<Object>();
try{
cursor = db.rawQuery("SELECT GID,G_PID,GNAME FROM T_GROUPS WHERE GID IN ("+pid+") and G_SORT="+type+"",null);
while(cursor.moveToNext()){
GroupsModule group = new GroupsModule();
group.setGid(Integer.parseInt(cursor.getString(cursor.getColumnIndex("GID"))));
group.setGname(cursor.getString(cursor.getColumnIndex("GNAME")));
group.setG_pid(Integer.parseInt(cursor.getString(cursor.getColumnIndex("G_PID"))));
list.add(group);
gm=group;
Log.e("xiao", "分组测试--------"+group.getGid()+"----"+group.getGname()+"----"+group.getG_pid());
System.out.println("分组测试--------"+group.getGid()+"----"+group.getGname()+"----"+group.getG_pid());
}
}catch(Exception e){
Log.e("xiao", "出错了+"+gm.getGname()+"-----"+gm.getGid()+" "+gm.getG_pid());
}
finally{
if(cursor!=null)cursor.close();
}
return list;
}
/**
* 根据分组查询员工信息
* @param tableName
* @param type
* @return
*/
public ArrayList<Object> searchEmpsByGps(String gid,String select){
//System.out.println("员工条件---------------------"+gid);
ArrayList<Object> list = new ArrayList<Object>();
select = "%"+select+"%";
String sql = "SELECT * FROM T_EMPS WHERE REMARK2='0' AND GID='"+gid+"'";
if(!select.equals("")){
sql+=" AND (XM LIKE '"+select+"' OR ZJM LIKE '"+select+"')";
}
sql+=" ORDER BY ZJM COLLATE NOCASE ASC";
cursor = db.rawQuery(sql,null);
//cursor = db.rawQuery("SELECT * FROM T_EMPS WHERE GID=? ORDER BY ZJM COLLATE NOCASE ASC",new String[]{gid});
while(cursor.moveToNext()){
EmpsModule emp = new EmpsModule();
emp.setDwhm(cursor.getString(cursor.getColumnIndex("DWHM")));
emp.setGuid(cursor.getString(cursor.getColumnIndex("GUID")));
emp.setXm(cursor.getString(cursor.getColumnIndex("XM")));
list.add(emp);
//System.out.println("根据分组查询员工---------"+cursor.getString(cursor.getColumnIndex("XM")));
}
return list;
}
/**
* 根据分组查询客户信息
* @param tableName
* @param type
* @return
*/
public ArrayList<Object> searchCustsByGps(String gid,String select){
//System.out.println("客户条件---------------------"+gid);
ArrayList<Object> list = new ArrayList<Object>();
select = "%"+select+"%";
String sql = "SELECT * FROM T_CUSTOMERS WHERE GID='"+gid+"' AND KH_STATE =1";
if(!select.equals("")){//模糊搜索
sql+=" AND (CNAME LIKE '"+"%"+select+"%"+"' OR CZJM LIKE '"+"%"+select+"%"+"')";
}
sql+=" ORDER BY CZJM COLLATE NOCASE ASC";
cursor = db.rawQuery(sql,null);
while(cursor.moveToNext()){
CustomerModule cust = new CustomerModule();
cust.setCid(cursor.getString(cursor.getColumnIndex("CID")));
cust.setCname(cursor.getString(cursor.getColumnIndex("CNAME")));
cust.setShort_name(cursor.getString(cursor.getColumnIndex("SHORT_NAME")));
list.add(cust);
//System.out.println("根据分组查询客户---------"+cursor.getString(cursor.getColumnIndex("XM")));
}
return list;
}
/**
* 根据客户编号查询客户信息
* @param cid
* @return
*/
public CustomerModule searchCustById(String cid){
CustomerModule cust = new CustomerModule();
cursor = db.rawQuery("SELECT * FROM T_CUSTOMERS WHERE CID=? AND KH_STATE =1 ORDER BY CZJM COLLATE NOCASE ASC",new String[]{cid});
while(cursor.moveToNext()){
cust.setCid(cursor.getString(cursor.getColumnIndex("CID")));
cust.setCname(cursor.getString(cursor.getColumnIndex("CNAME")));
cust.setShort_name(cursor.getString(cursor.getColumnIndex("SHORT_NAME")));
cust.setLink_sim(cursor.getString(cursor.getColumnIndex("LINK_SIM")));
cust.setLinkman(cursor.getString(cursor.getColumnIndex("LINKMAN")));
cust.setAddress(cursor.getString(cursor.getColumnIndex("ADDRESS")));
}
return cust;
}
/**
* 根据客户编号批量查询客户信息
* @param cid
* @return
*/
public ArrayList<CustomerModule> searchCustByIds(String cids){
ArrayList<CustomerModule> list = new ArrayList<CustomerModule>();
cursor = db.rawQuery("SELECT * FROM T_CUSTOMERS WHERE CID IN ("+cids+") AND KH_STATE =1 ORDER BY CZJM COLLATE NOCASE ASC",null);
while(cursor.moveToNext()){
CustomerModule cust = new CustomerModule();
cust.setCid(cursor.getString(cursor.getColumnIndex("CID")));
cust.setCname(cursor.getString(cursor.getColumnIndex("CNAME")));
cust.setLink_sim(cursor.getString(cursor.getColumnIndex("LINK_SIM")));
cust.setLinkman(cursor.getString(cursor.getColumnIndex("LINKMAN")));
cust.setAddress(cursor.getString(cursor.getColumnIndex("ADDRESS")));
cust.setLon(cursor.getString(cursor.getColumnIndex("LON")));
cust.setLat(cursor.getString(cursor.getColumnIndex("LAT")));
cust.setClon(cursor.getString(cursor.getColumnIndex("CLON")));
cust.setClat(cursor.getString(cursor.getColumnIndex("CLAT")));
cust.setSjhm(cursor.getString(cursor.getColumnIndex("SJHM")));
cust.setShort_name(cursor.getString(cursor.getColumnIndex("SHORT_NAME")));
list.add(cust);
}
return list;
}
/**
* 根据批量分组编号查询员工信息
* @param tableName
* @param type
* @return
*/
public ArrayList<Object> searchEmpsByMoreGps(String gids){
ArrayList<Object> list = new ArrayList<Object>();
cursor = db.rawQuery("SELECT * FROM T_EMPS WHERE REMARK2='0' AND GID in ("+gids+") ORDER BY ZJM COLLATE NOCASE ASC",null);
while(cursor.moveToNext()){
EmpsModule emp = new EmpsModule();
emp.setDwhm(cursor.getString(cursor.getColumnIndex("DWHM")));
emp.setGuid(cursor.getString(cursor.getColumnIndex("GUID")));
emp.setXm(cursor.getString(cursor.getColumnIndex("XM")));
emp.setAddr(cursor.getString(cursor.getColumnIndex("ADDR")));
emp.setDepartment(cursor.getString(cursor.getColumnIndex("DEPARTMENT")));
emp.setEmail(cursor.getString(cursor.getColumnIndex("EMAIL")));
emp.setHonor(cursor.getString(cursor.getColumnIndex("HONOR")));
emp.setDwfs(cursor.getString(cursor.getColumnIndex("REMARK1")));
list.add(emp);
//System.out.println("根据分组查询员工---------"+cursor.getString(cursor.getColumnIndex("XM")));
}
return list;
}
/**
* 根据批量分组编号查询产品信息
* @param tableName
* @param type
* @return
*/
public ArrayList<Object> searchProsByMoreGps(String gids){
ArrayList<Object> list = new ArrayList<Object>();
String sql = "SELECT * FROM T_PRODUCTS WHERE GID in ("+gids+") ORDER BY P_ZJM COLLATE NOCASE ASC";
System.out.println("查询产品："+sql);
cursor = db.rawQuery(sql,null);
//cursor = db.rawQuery("SELECT * FROM T_PRODUCTS ORDER BY P_ZJM COLLATE NOCASE ASC",null);
while(cursor.moveToNext()){
ProTypeModule product = new ProTypeModule();
product.setAddtime(cursor.getString(cursor.getColumnIndex("P_ADDTIME")));
product.setCbPrise(cursor.getString(cursor.getColumnIndex("P_CBJ")));
product.setCode(cursor.getString(cursor.getColumnIndex("TID")));
product.setDescription(cursor.getString(cursor.getColumnIndex("P_BZ")));
product.setId(cursor.getString(cursor.getColumnIndex("PID")));
product.setJxs(cursor.getString(cursor.getColumnIndex("P_SALER")));
product.setLbid(cursor.getString(cursor.getColumnIndex("GID")));
product.setLsPrise(cursor.getString(cursor.getColumnIndex("P_LSJ")));
product.setModel(cursor.getString(cursor.getColumnIndex("P_TYPE")));
product.setName(cursor.getString(cursor.getColumnIndex("P_NAME")));
product.setOrigin(cursor.getString(cursor.getColumnIndex("P_ADDR")));
product.setPfPrise(cursor.getString(cursor.getColumnIndex("P_PFJ")));
product.setProducer(cursor.getString(cursor.getColumnIndex("P_FACTORY")));
product.setStatus(cursor.getString(cursor.getColumnIndex("P_STATUS")));
product.setType(cursor.getString(cursor.getColumnIndex("P_SORT")));
product.setUnit(cursor.getString(cursor.getColumnIndex("P_UNIT")));
list.add(product);
}
return list;
}
/**
* 根据批量分组编号查询客户信息
* @param gids -- 分组编号
* @param type -- 客户类别1、企业客户2、个人客户3、联盟商
* @return
*/
public ArrayList<Object> searchCustsByMoreGps(String gids,int custSort){
ArrayList<Object> list = new ArrayList<Object>();
cursor = db.rawQuery("SELECT * FROM T_CUSTOMERS WHERE GID IN ("+gids+") AND KH_SORT="+custSort+" AND KH_STATE =1 ORDER BY CZJM COLLATE NOCASE ASC",null);
while(cursor.moveToNext()){
CustomerModule cust = new CustomerModule();
cust.setCid(cursor.getString(cursor.getColumnIndex("CID")));
cust.setCname(cursor.getString(cursor.getColumnIndex("CNAME")));
cust.setLink_sim(cursor.getString(cursor.getColumnIndex("LINK_SIM")));
cust.setLinkman(cursor.getString(cursor.getColumnIndex("LINKMAN")));
cust.setAddress(cursor.getString(cursor.getColumnIndex("ADDRESS")));
cust.setLon(cursor.getString(cursor.getColumnIndex("LON")));
cust.setLat(cursor.getString(cursor.getColumnIndex("LAT")));
cust.setClon(cursor.getString(cursor.getColumnIndex("CLON")));
cust.setClat(cursor.getString(cursor.getColumnIndex("CLAT")));
cust.setSjhm(cursor.getString(cursor.getColumnIndex("SJHM")));
cust.setShort_name(cursor.getString(cursor.getColumnIndex("SHORT_NAME")));
list.add(cust);
//System.out.println("根据分组查询kehu---------"+cursor.getString(cursor.getColumnIndex("XM")));
}
return list;
}
/**
* 获得员工信息
* @param tableName
* @param type
* @return
*/
public ArrayList<Object> searchEmps(String tableName,String select){
ArrayList<Object> list = new ArrayList<Object>();
String sql = "SELECT * FROM T_EMPS WHERE (REMARK2='0' OR REMARK2 IS NULL) ";
if(tableName.equals("EMPS")){
if(!select.equals("")){
sql+=" AND DWHM='"+select+"'";
}
sql+=" ORDER BY ZJM COLLATE NOCASE ASC";
cursor = db.rawQuery(sql,null);
while(cursor.moveToNext()){
EmpsModule emp = new EmpsModule();
emp.setDwhm(cursor.getString(cursor.getColumnIndex("DWHM")));
emp.setGuid(cursor.getString(cursor.getColumnIndex("GUID")));
emp.setXm(cursor.getString(cursor.getColumnIndex("XM")));
emp.setAddr(cursor.getString(cursor.getColumnIndex("ADDR")));
emp.setDepartment(cursor.getString(cursor.getColumnIndex("DEPARTMENT")));
emp.setEmail(cursor.getString(cursor.getColumnIndex("EMAIL")));
emp.setHonor(cursor.getString(cursor.getColumnIndex("HONOR")));
list.add(emp);
//System.out.println("员工信息排序测试-------------"+emp.getXm()+","+cursor.getString(cursor.getColumnIndex("ZJM")));
}
}
return list;
}
/**
* 获得员工姓名信息
* @param tableName
* @param type
* @return
*/
public String searchEmpName(String guid){
String result = "";
cursor = db.rawQuery("SELECT * FROM T_EMPS WHERE GUID=?",new String[]{guid});
while(cursor.moveToNext()){
result = cursor.getString(cursor.getColumnIndex("XM"));
}
return result;
}
/**
* 模糊搜索员工信息
* @param tableName
* @param type
* @return
*/
public ArrayList<Object> searchEmpsBySelect(String select){
ArrayList<Object> list = new ArrayList<Object>();
cursor = db.rawQuery("SELECT * FROM T_EMPS WHERE REMARK2='0' AND (XM LIKE ? OR ZJM LIKE ? OR DWHM LIKE ?) ORDER BY ZJM COLLATE NOCASE ASC",new String[]{"%"+select+"%","%"+select+"%","%"+select+"%"});
while(cursor.moveToNext()){
EmpsModule emp = new EmpsModule();
emp.setDwhm(cursor.getString(cursor.getColumnIndex("DWHM")));
emp.setGuid(cursor.getString(cursor.getColumnIndex("GUID")));
emp.setXm(cursor.getString(cursor.getColumnIndex("XM")));
emp.setAddr(cursor.getString(cursor.getColumnIndex("ADDR")));
emp.setDepartment(cursor.getString(cursor.getColumnIndex("DEPARTMENT")));
emp.setEmail(cursor.getString(cursor.getColumnIndex("EMAIL")));
emp.setHonor(cursor.getString(cursor.getColumnIndex("HONOR")));
list.add(emp);
}
return list;
}
/**
* 更新阅读状态
* @param tableName
* @param gid
*/
public void updateReadState(String tableName,int gid){
System.out.println("更新阅读状态---------"+gid+"---------"+tableName);
if(tableName.equals("T_PUBINFOS")){
db.execSQL("UPDATE T_PUBINFOS SET STATE=2 WHERE GID = "+gid+"");
}
}
/**
* 获得签到地点信息--用于考勤
* @param select --归属1、企业客户2、个人客户3、考勤地点
* @param select --客户状态0、无效1、正常
* @param select --查询条件--模糊搜索条件
* @return
*/
public ArrayList<CustomerModule> searchkqAddrs(int state,String select){
ArrayList<CustomerModule> list = new ArrayList<CustomerModule>();
String sql = "SELECT * FROM T_CUSTOMERS WHERE KH_SORT IN (1,3) AND KH_STATE = "+state+"";
if(!select.equals("")){//模糊搜索
sql+=" AND (CNAME LIKE '"+"%"+select+"%"+"' OR CZJM LIKE '"+"%"+select+"%"+"')";
}
sql+=" ORDER BY CZJM COLLATE NOCASE ASC";
cursor = db.rawQuery(sql,null);
while(cursor.moveToNext()){
CustomerModule cust = new CustomerModule();
cust.setCid(cursor.getString(cursor.getColumnIndex("CID")));
cust.setCname(cursor.getString(cursor.getColumnIndex("CNAME")));
cust.setLink_sim(cursor.getString(cursor.getColumnIndex("LINK_SIM")));
cust.setLinkman(cursor.getString(cursor.getColumnIndex("LINKMAN")));
cust.setAddress(cursor.getString(cursor.getColumnIndex("ADDRESS")));
cust.setLon(cursor.getString(cursor.getColumnIndex("LON")));
cust.setLat(cursor.getString(cursor.getColumnIndex("LAT")));
cust.setClon(cursor.getString(cursor.getColumnIndex("CLON")));
cust.setClat(cursor.getString(cursor.getColumnIndex("CLAT")));
cust.setSjhm(cursor.getString(cursor.getColumnIndex("SJHM")));
cust.setShort_name(cursor.getString(cursor.getColumnIndex("SHORT_NAME")));
list.add(cust);
/*System.out.println("客户-------------"+cust.getCname()+","
+cursor.getString(cursor.getColumnIndex("KH_SORT"))+","
+cursor.getString(cursor.getColumnIndex("KH_STATE")));*/
}
return list;
}
/**
* 获得客户信息
* @param select --归属1、企业客户2、个人客户3、考勤地点
* @param select --客户状态0、无效1、正常
* @param select --查询条件--模糊搜索条件
* @return
*/
public ArrayList<CustomerModule> searchCustomers(int type,int state,String select){
ArrayList<CustomerModule> list = new ArrayList<CustomerModule>();
String sql = "SELECT * FROM T_CUSTOMERS WHERE KH_SORT="+type+" AND KH_STATE = "+state+"";
if(!select.equals("")){//模糊搜索
sql+=" AND (CNAME LIKE '"+"%"+select+"%"+"' OR CZJM LIKE '"+"%"+select+"%"+"')";
}
sql+=" ORDER BY CZJM COLLATE NOCASE ASC";
cursor = db.rawQuery(sql,null);
while(cursor.moveToNext()){
CustomerModule cust = new CustomerModule();
cust.setCid(cursor.getString(cursor.getColumnIndex("CID")));
cust.setCname(cursor.getString(cursor.getColumnIndex("CNAME")));
cust.setLink_sim(cursor.getString(cursor.getColumnIndex("LINK_SIM")));
cust.setLinkman(cursor.getString(cursor.getColumnIndex("LINKMAN")));
cust.setAddress(cursor.getString(cursor.getColumnIndex("ADDRESS")));
cust.setLon(cursor.getString(cursor.getColumnIndex("LON")));
cust.setLat(cursor.getString(cursor.getColumnIndex("LAT")));
cust.setClon(cursor.getString(cursor.getColumnIndex("CLON")));
cust.setClat(cursor.getString(cursor.getColumnIndex("CLAT")));
cust.setSjhm(cursor.getString(cursor.getColumnIndex("SJHM")));
cust.setShort_name(cursor.getString(cursor.getColumnIndex("SHORT_NAME")));
list.add(cust);
//System.out.println("客户-------------"+cust.getCname()+","+cursor.getString(cursor.getColumnIndex("CID")));
}
return list;
}
/**
* 获得产品信息--本品、竞品
* @param type --产品类型1、本品2、竞品
* @param sort --产品类型（1-商品、2-赠品、3消耗品）
* @param select --查询条件--模糊搜索条件
* @return
*/
public ArrayList<ProTypeModule> searchProducts(int type,int sort, String select){
ArrayList<ProTypeModule> list = new ArrayList<ProTypeModule>();
String sql = "SELECT * FROM T_PRODUCTS WHERE P_LB="+type+"";
if(!select.equals("")){//模糊搜索
sql+=" AND (P_NAME LIKE '"+"%"+select+"%"+"' OR P_ZJM LIKE '"+"%"+select+"%"+"')";
}
if(sort !=0){
sql+=" AND P_SORT="+sort+"";
}
sql+=" ORDER BY P_ZJM COLLATE NOCASE ASC";
cursor = db.rawQuery(sql,null);
while(cursor.moveToNext()){
ProTypeModule product = new ProTypeModule();
product.setAddtime(cursor.getString(cursor.getColumnIndex("P_ADDTIME")));
product.setCbPrise(cursor.getString(cursor.getColumnIndex("P_CBJ")));
product.setCode(cursor.getString(cursor.getColumnIndex("TID")));
product.setDescription(cursor.getString(cursor.getColumnIndex("P_BZ")));
product.setId(cursor.getString(cursor.getColumnIndex("PID")));
product.setJxs(cursor.getString(cursor.getColumnIndex("P_SALER")));
product.setLbid(cursor.getString(cursor.getColumnIndex("GID")));
product.setLsPrise(cursor.getString(cursor.getColumnIndex("P_LSJ")));
product.setModel(cursor.getString(cursor.getColumnIndex("P_TYPE")));
product.setName(cursor.getString(cursor.getColumnIndex("P_NAME")));
product.setOrigin(cursor.getString(cursor.getColumnIndex("P_ADDR")));
product.setPfPrise(cursor.getString(cursor.getColumnIndex("P_PFJ")));
product.setProducer(cursor.getString(cursor.getColumnIndex("P_FACTORY")));
product.setStatus(cursor.getString(cursor.getColumnIndex("P_STATUS")));
product.setType(cursor.getString(cursor.getColumnIndex("P_SORT")));
product.setUnit(cursor.getString(cursor.getColumnIndex("P_UNIT")));
list.add(product);
}
return list;
}
/**
* 根据产品编号，查找商品信息
* @param pnum
* @return
*/
public ProTypeModule searchProductById(String pnum){
System.out.println("商品编号------"+pnum);
ProTypeModule product = new ProTypeModule();
cursor = db.rawQuery("SELECT * FROM T_PRODUCTS WHERE PID="+pnum+"",null);
while(cursor.moveToNext()){
product.setAddtime(cursor.getString(cursor.getColumnIndex("P_ADDTIME")));
product.setCbPrise(cursor.getString(cursor.getColumnIndex("P_CBJ")));
product.setCode(cursor.getString(cursor.getColumnIndex("TID")));
product.setDescription(cursor.getString(cursor.getColumnIndex("P_BZ")));
product.setId(cursor.getString(cursor.getColumnIndex("PID")));
product.setJxs(cursor.getString(cursor.getColumnIndex("P_SALER")));
product.setLbid(cursor.getString(cursor.getColumnIndex("GID")));
product.setLsPrise(cursor.getString(cursor.getColumnIndex("P_LSJ")));
product.setModel(cursor.getString(cursor.getColumnIndex("P_TYPE")));
product.setName(cursor.getString(cursor.getColumnIndex("P_NAME")));
product.setOrigin(cursor.getString(cursor.getColumnIndex("P_ADDR")));
product.setPfPrise(cursor.getString(cursor.getColumnIndex("P_PFJ")));
product.setProducer(cursor.getString(cursor.getColumnIndex("P_FACTORY")));
product.setStatus(cursor.getString(cursor.getColumnIndex("P_STATUS")));
product.setType(cursor.getString(cursor.getColumnIndex("P_SORT")));
product.setUnit(cursor.getString(cursor.getColumnIndex("P_UNIT")));
//System.out.println("商品信息---------"+cursor.getString(cursor.getColumnIndex("P_NAME"))+","+cursor.getString(cursor.getColumnIndex("PID"))+","+cursor.getString(cursor.getColumnIndex("P_UNIT")));
}
return product;
}
/**
* 操作拜访记录表
* @param cid
*/
public boolean updateBfRecord(String opt,String cid,String content){
boolean flag = false;
if(opt.equals("add")){
db.execSQL("INSERT INTO T_VISTORS (CID,REMARK1) VALUES (?,?)",new Object[]{cid,content});
}else if(opt.equals("delete")){
db.execSQL("DELETE FROM T_VISTORS WHERE CID = '"+cid+"'");
}else if(opt.equals("select")){
cursor = db.rawQuery("SELECT * FROM T_VISTORS WHERE CID = ?",new String[]{cid});
while(cursor.moveToNext()){
flag = true;
}
}
return flag;
}
/**
*
* Description: 查询拜访记录表
* Title: selectBfRecord
* @param cid
* @return
* String
*/
public String selectBfRecord(String cid){
String str="";
try {
cursor = db.rawQuery("SELECT * FROM T_VISTORS WHERE CID = ?",new String[]{cid});
while(cursor.moveToNext()){
str=cursor.getString(cursor.getColumnIndex("REMARK1"));
}
} catch (Exception e) {
if(e.getMessage().contains("no such table")){
createTable(T_VISTORS);
selectBfRecord(cid);
}
}
return str;
}
/**
* 传入Cell id，查询基站信息表
* @return
*/
public HashMap<String,String> searchLacs(int cell_id,String custId,int lac){
System.out.println("查询基站表--------基站编号："+cell_id+",客户编号："+custId+",LAC"+lac);
if(custId == null || custId.equals("-1") || custId.equals("")){
cursor = db.rawQuery("SELECT * FROM T_LACINFO WHERE CELL_ID = ? AND LAC = ?",new String[]{cell_id+"",lac+""});
}else{
cursor = db.rawQuery("SELECT * FROM T_LACINFO WHERE CELL_ID = ? AND CID=? AND LAC = ?",new String[]{cell_id+"",custId,lac+""});
}
HashMap<String,String> map = null;
while(cursor.moveToNext()){
map = new HashMap<String,String>();
map.put("lon", cursor.getString(cursor.getColumnIndex("REMARK1")));
map.put("lat",cursor.getString(cursor.getColumnIndex("REMARK2")));
map.put("radius",cursor.getString(cursor.getColumnIndex("RADIUS")));
map.put("loc_type", cursor.getString(cursor.getColumnIndex("LOC_TYPE")));
System.out.println("基站信息-----------经度："+cursor.getString(cursor.getColumnIndex("REMARK1"))+",纬度："+cursor.getString(cursor.getColumnIndex("REMARK2"))
+"loc_type:"+cursor.getString(cursor.getColumnIndex("LOC_TYPE")));
}
System.out.println("查询基站信息表---------结果："+map);
return map;
}
public void insertFileLog(String path,File uploadFile){
//表不存在则创建，否则不处理
db.execSQL("CREATE TABLE IF NOT EXISTS T_UPICLOG(ID INTEGER PRIMARY KEY AUTOINCREMENT, UPLOADFILEPATH VARCHAR(100), SOURCEID VARCAHR(10))");
db.execSQL("insert into T_UPICLOG(uploadfilepath, sourceid) values(?,?)",new Object[]{path,uploadFile.getAbsolutePath()});
}
public void deleteFileLog(File uploadFile){
db.execSQL("DELETE FROM T_UPICLOG WHERE UPLOADFILEPATH=?", new Object[]{uploadFile.getAbsolutePath()});
}
public String getBindId(File uploadFile){
try {
Cursor cursor = db.rawQuery("SELECT SOURCEID FROM T_UPICLOG WHERE UPLOADFILEPATH=?",
new String[]{uploadFile.getAbsolutePath()});
if(cursor.moveToFirst()){
return cursor.getString(0);
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
Log.i("db", "数据库表T_UPICLOG不存在！~");
}
return null;
}
/**
* 关闭Sqllite3数据库
*/
public void close_SqlDb(){
System.out.println("************关闭数据库连接************");
if(cursor != null){
cursor.close();
}
if(db != null){
db.close();
}
}
}