package com.jiuqi.njt.data;
import java.util.ArrayList;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;
import com.jiuqi.mobile.nigo.comeclose.bean.base.KindsOfCarBean;
import com.jiuqi.njt.model.PeriodData;
import com.jiuqi.njt.util.Constants;
/**
* 操作Sqllite3数据库的接口程序
*/
public class Optdb_interfce {
// 数据库对象
private static SQLiteDatabase db = null;
// 数据库游标
private Cursor cursor = null;
// 数据库名
private static String DATABASE_NJTFORM = "njt_form.db";
public static String TABLE_RECORDS = "T_DATA_RECORDS";
public static String TABLE_PERIODS = "T_PERIODS";
public static String TABLE_SOLUTIONS = "T_SOLUTIONS";
public static String TABLE_NJLEIBIE = "T_NJ_LEIBIE";
// 业务方案时期列表
private static final String T_PERIODS = "CREATE TABLE T_PERIODS(ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,USERNAME VARCHAR(50),PERIOD VARCHAR(50),SOLUTIONID VARCHAR(30),"
+ "ADD_TIME VARCHAR(30)";
/**
* 数据库表字段信息
*/
private static final String T_SOLUTIONS = "CREATE TABLE T_SOLUTIONS(ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,USERNAME VARCHAR(50),NAME VARCHAR(50),SOLUTIONID VARCHAR(30), ADD_TIME VARCHAR(30),STARTTIME VARCHAR(30), ENDTIME VARCHAR(30),CURRENTYEAR VARCHAR(30)";
// 上传数据的表
private static final String T_DATA_RECORDS = "CREATE TABLE T_DATA_RECORDS(ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,USERNAME VARCHAR(50),PERIOD VARCHAR(50),SOLUTIONID VARCHAR(30),"
+ "AUDITOR VARCHAR(30),ADD_TIME VARCHAR(30),STATE INT,ZBNAME VARCHAR(50),ZBVALUE VARCHAR(500)";
// 扩展字段值
private static final String extendValue = "REMARK1 VARCHAR(500),REMARK2 VARCHAR(500),"
+ "REMARK3 VARCHAR(500),REMARK4 VARCHAR(500),REMARK5 VARCHAR(500)";
//农机类别
private static final String T_NJ_LEIBIE = "CREATE TABLE IF NOT EXISTS T_NJ_LEIBIE(ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,NAME VARCHAR(50),CODE LONG(50),PCODE LONG(50),"
+ "ADDTIMESTR VARCHAR(30),LEVEL VARCHAR(30),BIGCLASSCODE VARCHAR(30),BIGCLASSNAME VARCHAR(50),SMALLCLASSNAME VARCHAR(50),SMALLCLASSCODE VARCHAR(30),ITEMNAME VARCHAR(50),ITEMCODE VARCHAR(30),REMARK3 VARCHAR(50),REMARK4 VARCHAR(50),REMARK5 VARCHAR(50))";
/**
* 创建或打开数据库
*/
@SuppressWarnings("static-access")
public Optdb_interfce(Context context) {
try {
this.db = context.openOrCreateDatabase(DATABASE_NJTFORM,
context.MODE_APPEND, null);
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 判断数据库是否已打开
*
* @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) {
e.printStackTrace();
}
return flag;
}
// 创建数据库表
public void createTable() {
try {
db.execSQL(T_SOLUTIONS+ "," + extendValue + ")");
db.execSQL(T_DATA_RECORDS + "," + extendValue + ")");
db.execSQL(T_PERIODS + "," + extendValue + ")");
db.execSQL(T_NJ_LEIBIE );
} catch (SQLException e) {
e.printStackTrace();
System.out.println("表已存在！");
}
}
// 创建数据库表
public void createNJTable() {
try {
db.execSQL(T_NJ_LEIBIE );
} catch (SQLException e) {
e.printStackTrace();
System.out.println("表已存在！");
}
}
// 插入数据
public void InsertToDb(String tableName, ArrayList<KindsOfCarBean> list) {
if(list==null) return ;
if(TABLE_NJLEIBIE.equals(tableName)){
for (Object object : list) {
KindsOfCarBean bean = (KindsOfCarBean) object;
try {
db.execSQL(
"INSERT INTO T_NJ_LEIBIE(NAME,CODE,PCODE,ADDTIMESTR,LEVEL,BIGCLASSCODE,BIGCLASSNAME,SMALLCLASSNAME,SMALLCLASSCODE,ITEMNAME,ITEMCODE) "
+ "VALUES (?,?,?,?,?,?,?,?,?,?,?)",
new Object[] { bean.getName(),bean.getCode(),bean.getpCode(),
bean.getCreateDateStr(),bean.getLevel()+"",
bean.getBigClassCode(), bean.getBigClassName(),bean.getSmallClassName(),bean.getSmallClassCode(),bean.getItemName(),bean.getItemCode()
});
}catch(Exception e){
Log.i("database","插入业务方案失败");
e.printStackTrace();
}
}
}
}
//查询所有农机大类
public ArrayList<KindsOfCarBean> getKindsOfCarBeanInfo(String level) {
ArrayList<KindsOfCarBean> list = new ArrayList<KindsOfCarBean>();
String sql = "SELECT * FROM T_NJ_LEIBIE WHERE LEVEL = ?";
try {
cursor = db.rawQuery(sql, new String[]{level});
while (cursor.moveToNext()) {
KindsOfCarBean kindsOfCarBean = new KindsOfCarBean();
kindsOfCarBean.setName(cursor.getString(cursor.getColumnIndex("NAME")));
kindsOfCarBean.setCode(cursor.getLong(cursor.getColumnIndex("CODE")));
kindsOfCarBean.setpCode(cursor.getLong(cursor.getColumnIndex("PCODE")));
kindsOfCarBean.setBigClassCode(Integer.parseInt(cursor.getString(cursor.getColumnIndex("BIGCLASSCODE"))));
kindsOfCarBean.setBigClassName(cursor.getString(cursor.getColumnIndex("BIGCLASSNAME")));
kindsOfCarBean.setCreateDateStr(cursor.getString(cursor.getColumnIndex("ADDTIMESTR")));
kindsOfCarBean.setSmallClassCode(Integer.parseInt(cursor.getString(cursor.getColumnIndex("SMALLCLASSCODE"))));
kindsOfCarBean.setSmallClassName(cursor.getString(cursor.getColumnIndex("SMALLCLASSNAME")));
kindsOfCarBean.setItemCode(Integer.parseInt(cursor.getString(cursor.getColumnIndex("ITEMCODE"))));
kindsOfCarBean.setItemName(cursor.getString(cursor.getColumnIndex("ITEMNAME")));
list.add(kindsOfCarBean);
}
} catch (Exception e) {
Log.i("database","查询业务方案记录失败");
e.printStackTrace();
}
return list;
}
//根据农机大类名称查询出它下面的所有小类
public ArrayList<KindsOfCarBean> getSmallKindsOfCarBeanInfo(String level,String name) {
ArrayList<KindsOfCarBean> list = new ArrayList<KindsOfCarBean>();
String sql = "SELECT * FROM T_NJ_LEIBIE WHERE LEVEL = ? and BIGCLASSNAME = ?";
try {
cursor = db.rawQuery(sql, new String[]{level,name});
while (cursor.moveToNext()) {
KindsOfCarBean kindsOfCarBean = new KindsOfCarBean();
kindsOfCarBean.setName(cursor.getString(cursor.getColumnIndex("NAME")));
kindsOfCarBean.setCode(cursor.getLong(cursor.getColumnIndex("CODE")));
kindsOfCarBean.setpCode(cursor.getLong(cursor.getColumnIndex("PCODE")));
kindsOfCarBean.setBigClassCode(Integer.parseInt(cursor.getString(cursor.getColumnIndex("BIGCLASSCODE"))));
kindsOfCarBean.setBigClassName(cursor.getString(cursor.getColumnIndex("BIGCLASSNAME")));
kindsOfCarBean.setCreateDateStr(cursor.getString(cursor.getColumnIndex("ADDTIMESTR")));
kindsOfCarBean.setSmallClassCode(Integer.parseInt(cursor.getString(cursor.getColumnIndex("SMALLCLASSCODE"))));
kindsOfCarBean.setSmallClassName(cursor.getString(cursor.getColumnIndex("SMALLCLASSNAME")));
kindsOfCarBean.setItemCode(Integer.parseInt(cursor.getString(cursor.getColumnIndex("ITEMCODE"))));
kindsOfCarBean.setItemName(cursor.getString(cursor.getColumnIndex("ITEMNAME")));
list.add(kindsOfCarBean);
}
} catch (Exception e) {
Log.i("database","查询业务方案记录失败");
e.printStackTrace();
}
return list;
}
//根据农机小类名称查询出它下面的所有农机品目
public ArrayList<KindsOfCarBean> getSmallKindsOfCarBeanPMInfo(String level,String smallClassName) {
ArrayList<KindsOfCarBean> list = new ArrayList<KindsOfCarBean>();
String sql = "SELECT * FROM T_NJ_LEIBIE WHERE LEVEL = ? and SMALLCLASSNAME = ?";
try {
cursor = db.rawQuery(sql, new String[]{level,smallClassName});
while (cursor.moveToNext()) {
KindsOfCarBean kindsOfCarBean = new KindsOfCarBean();
// kindsOfCarBean.setName(cursor.getString(cursor.getColumnIndex("NAME")));
// kindsOfCarBean.setCode(cursor.getLong(cursor.getColumnIndex("CODE")));
// kindsOfCarBean.setCreateDateStr(cursor.getString(cursor.getColumnIndex("ADDTIMESTR")));
// kindsOfCarBean.setBigClassName(cursor.getString(cursor.getColumnIndex("REMARK3")));
// kindsOfCarBean.setSmallClassName(cursor.getString(cursor.getColumnIndex("REMARK4")));
kindsOfCarBean.setName(cursor.getString(cursor.getColumnIndex("NAME")));
kindsOfCarBean.setCode(cursor.getLong(cursor.getColumnIndex("CODE")));
kindsOfCarBean.setpCode(cursor.getLong(cursor.getColumnIndex("PCODE")));
kindsOfCarBean.setBigClassCode(Integer.parseInt(cursor.getString(cursor.getColumnIndex("BIGCLASSCODE"))));
kindsOfCarBean.setBigClassName(cursor.getString(cursor.getColumnIndex("BIGCLASSNAME")));
kindsOfCarBean.setCreateDateStr(cursor.getString(cursor.getColumnIndex("ADDTIMESTR")));
kindsOfCarBean.setSmallClassCode(Integer.parseInt(cursor.getString(cursor.getColumnIndex("SMALLCLASSCODE"))));
kindsOfCarBean.setSmallClassName(cursor.getString(cursor.getColumnIndex("SMALLCLASSNAME")));
kindsOfCarBean.setItemCode(Integer.parseInt(cursor.getString(cursor.getColumnIndex("ITEMCODE"))));
kindsOfCarBean.setItemName(cursor.getString(cursor.getColumnIndex("ITEMNAME")));
list.add(kindsOfCarBean);
}
} catch (Exception e) {
Log.i("database","查询业务方案记录失败");
e.printStackTrace();
}
return list;
}
public void deleteRecord(String solutionid,String period,String username) {
String sql = "DELETE FROM T_DATA_RECORDS WHERE USERNAME = '" + username + "' AND SOLUTIONID = '"+solutionid+"' AND PERIOD = '"+period+"'";
try {
db.execSQL(sql);
} catch (Exception e) {
Log.i("database","删除record记录失败");
e.printStackTrace();
}
}
public void deleteSolution(String solutionid,String username) {
String sql = "DELETE FROM T_SOLUTIONS WHERE USERNAME = '" + username + "' AND SOLUTIONID = '"+solutionid+"'";
try{
db.execSQL(sql);
} catch (Exception e) {
Log.i("database","删除业务方案记录失败");
e.printStackTrace();
}
}
public void deleteSolutionAll() {
String sql = "DELETE FROM T_SOLUTIONS";
try{
db.execSQL(sql);
} catch (Exception e) {
Log.i("database","删除所有业务方案记录失败");
e.printStackTrace();
}
}
public void deletePeriodRecord(String solutionid,String username) {
String sql = "DELETE FROM T_PERIODS WHERE USERNAME = '" + username + "' AND SOLUTIONID = '"+solutionid+"'";
try{
db.execSQL(sql);
} catch (Exception e) {
Log.i("database","删除时期记录失败");
e.printStackTrace();
}
}
/**
* 如果有某个时期的数据则返回true
* @param solutionid
* @param period
* @param username
* @return
*/
public boolean isHasRecord(String solutionid,String period,String username){
boolean isHasRecord = false;
String sql = "SELECT * FROM T_DATA_RECORDS WHERE USERNAME = ? AND SOLUTIONID = ? AND PERIOD = ?";
try {
cursor = db.rawQuery(sql, new String[]{username,solutionid,period});
isHasRecord = cursor.moveToNext();
}catch(Exception e){
}
return isHasRecord;
}
public ArrayList<Object> getNJLeiBie(String solutionid,String level) {
ArrayList<Object> list = new ArrayList<Object>();
String sql = "SELECT * FROM T_NJ_LEIBIE WHERE LEVEl = ? AND SOLUTIONID = ?";
try {
cursor = db.rawQuery(sql, new String[]{solutionid,level});
while (cursor.moveToNext()) {
PeriodData record = new PeriodData();
record.setUsername(cursor.getString(cursor.getColumnIndex("USERNAME")));
record.setPeriod(cursor.getString(cursor.getColumnIndex("PERIOD")));
record.setSolutionId(cursor.getString(cursor.getColumnIndex("SOLUTIONID")));
record.setAddtime(cursor.getString(cursor.getColumnIndex("ADD_TIME")));
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 (Exception e) {
Log.i("database","查询时期表记录失败");
e.printStackTrace();
}
return list;
}
/**
* 关闭Sqllite3数据库
*/
public void close_SqlDb() {
System.out.println("************关闭数据库连接************");
if (cursor != null) {
cursor.close();
}
if (db != null) {
db.close();
}
}
}