package ru.drebedengi;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.net.Uri;
import android.util.SparseIntArray;
public class DBAdapter {
private static final String DATABASE_NAME = "drebedengi.db";
private static final int DATABASE_VERSION = 1;
private final Context context;
private DatabaseHelper mOpenHelper;
private SQLiteDatabase db;
private static class DatabaseHelper extends SQLiteOpenHelper {
DatabaseHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL("CREATE TABLE budget_family ("
+ "id integer PRIMARY KEY NOT NULL,"
+ "name char(255) NOT NULL);");
db.execSQL("CREATE TABLE users ("
+ "id numeric(19) PRIMARY KEY NOT NULL,"
+ "nick text,"
+ "pass text,"
+ "is_male boolean NOT NULL);");
db.execSQL("CREATE TABLE currency ("
+ "id integer PRIMARY KEY NOT NULL,"
+ "name char(255) NOT NULL,"
+ "course char(16) NOT NULL,"
+ "server_id integer NOT NULL,"
+ "is_hidden boolean NOT NULL,"
+ "is_autoupdate boolean NOT NULL,"
+ "is_default boolean NOT NULL,"
+ "code char(16),"
+ "family_id integer NOT NULL,"
+ "/* Foreign keys */"
+ "FOREIGN KEY (family_id)"
+ " REFERENCES budget_family(id));");
db.execSQL("CREATE TABLE budget_win32_sync ("
+ "id integer PRIMARY KEY NOT NULL,"
+ "sync_id integer NOT NULL,"
+ "family_id integer NOT NULL,"
+ "is_only_client boolean NOT NULL,"
+ "is_started boolean NOT NULL,"
+ "sync_date date NOT NULL,"
+ "user_id numeric(19) NOT NULL,"
+ "record_count integer NOT NULL,"
+ "/* Foreign keys */"
+ "FOREIGN KEY (family_id)"
+ " REFERENCES budget_family(id),"
+ "FOREIGN KEY (user_id)"
+ " REFERENCES users(id));");
db.execSQL("CREATE TABLE budget_family_member (" //
+ "nuid numeric(19) NOT NULL,"
+ "budget_family_id integer NOT NULL,"
+ "/* Foreign keys */"
+ "FOREIGN KEY (nuid)"
+ " REFERENCES users(id),"
+ "FOREIGN KEY (budget_family_id)"
+ " REFERENCES budget_family(id));");
db.execSQL("CREATE TABLE budget_object_type ("
+ "id integer PRIMARY KEY NOT NULL,"
+ "name char(255) NOT NULL,"
+ "budget_family_id integer NOT NULL,"
+ "/* Foreign keys */"
+ "FOREIGN KEY (budget_family_id)"
+ " REFERENCES budget_family(id));");
db.execSQL("CREATE TABLE budget_plan ("
+ "id integer PRIMARY KEY NOT NULL,"
+ "plan_type integer NOT NULL,"
+ "period_from date NOT NULL,"
+ "period_to date NOT NULL,"
+ "\"limit\" integer NOT NULL,"
+ "wasted integer NOT NULL,"
+ "currency_id integer NOT NULL,"
+ "user_id numeric(19) NOT NULL,"
+ "family_id integer NOT NULL,"
+ "is_periodic boolean NOT NULL,"
+ "is_family boolean NOT NULL,"
+ "comment char(255),"
+ "created_date date NOT NULL,"
+ "modified_date date NOT NULL,"
+ "parent_id integer NOT NULL,"
+ "num_period integer NOT NULL,"
+ "begin_id integer NOT NULL,"
+ "/* Foreign keys */"
+ "FOREIGN KEY (parent_id)"
+ " REFERENCES budget_plan(id),"
+ "FOREIGN KEY (begin_id)"
+ " REFERENCES budget_plan(id));");
db.execSQL("CREATE TABLE budget_object ("
+ "id integer PRIMARY KEY NOT NULL,"
+ "parent_id integer NOT NULL,"
+ "budget_family_id integer NOT NULL,"
+ "type integer NOT NULL,"
+ "name char(255) NOT NULL,"
+ "purse_of_nuid numeric(19),"
+ "is_hidden boolean NOT NULL,"
+ "is_for_duty boolean NOT NULL,"
+ "is_credit_card boolean NOT NULL,"
+ "server_id integer NOT NULL,"
+ "sort integer NOT NULL,"
+ "/* Foreign keys */"
+ "FOREIGN KEY (budget_family_id)"
+ " REFERENCES budget_family(id),"
+ "FOREIGN KEY (type)"
+ " REFERENCES budget_object_type(id),"
+ "FOREIGN KEY (purse_of_nuid)"
+ " REFERENCES users(id),"
+ "FOREIGN KEY (parent_id)"
+ " REFERENCES budget_object(id));");
db.execSQL("CREATE TABLE budget_object_count (" //
+ "id integer PRIMARY KEY NOT NULL,"
+ "nuid numeric(19) NOT NULL,"
+ "budget_object_id integer NOT NULL,"
+ "count integer NOT NULL,"
+ "/* Foreign keys */"
+ "FOREIGN KEY (budget_object_id)"
+ " REFERENCES budget_object(id),"
+ "FOREIGN KEY (nuid)"
+ " REFERENCES users(id));");
db.execSQL("CREATE TABLE budget_object_tags ("
+ "id integer PRIMARY KEY NOT NULL,"
+ "user_id numeric(19) NOT NULL,"
+ "budget_object_id integer NOT NULL,"
+ "name char(64) NOT NULL,"
+ "is_for_sms boolean NOT NULL,"
+ "/* Foreign keys */"
+ "FOREIGN KEY (budget_object_id)"
+ " REFERENCES budget_object(id),"
+ "FOREIGN KEY (user_id)"
+ " REFERENCES users(id));");
db.execSQL("CREATE TABLE budget_plan_category ("
+ "id integer PRIMARY KEY NOT NULL,"
+ "category_id integer NOT NULL,"
+ "budget_plan_id integer NOT NULL,"
+ "\"limit\" integer NOT NULL,"
+ "wasted integer NOT NULL,"
+ "currency_id integer NOT NULL,"
+ "is_exclude boolean NOT NULL,"
+ "/* Foreign keys */"
+ "FOREIGN KEY (category_id)"
+ " REFERENCES budget_object(id),"
+ "FOREIGN KEY (currency_id)"
+ " REFERENCES currency(id),"
+ "FOREIGN KEY (budget_plan_id)"
+ " REFERENCES budget_plan(id));");
db.execSQL("CREATE TABLE budget_record ("
+ "id integer PRIMARY KEY NOT NULL,"
+ "budget_account_id integer NOT NULL,"
+ "budget_object_id integer NOT NULL,"
+ "budget_family_id integer NOT NULL,"
+ "currency_id integer NOT NULL,"
+ "user_nuid numeric(19) NOT NULL,"
+ "account_rest integer,"
+ "difference integer,"
+ "operation_date date NOT NULL,"
+ "comment text,"
+ "is_currency_change boolean NOT NULL,"
+ "is_duty boolean NOT NULL,"
+ "server_id integer NOT NULL,"
+ "/* Foreign keys */"
+ "FOREIGN KEY (budget_account_id)"
+ " REFERENCES budget_object(id),"
+ "FOREIGN KEY (budget_family_id)"
+ " REFERENCES budget_family(id),"
+ "FOREIGN KEY (user_nuid)"
+ " REFERENCES users(id),"
+ "FOREIGN KEY (budget_object_id)"
+ " REFERENCES budget_object(id),"
+ "FOREIGN KEY (currency_id)"
+ " REFERENCES currency(id));");
db.execSQL("CREATE TABLE budget_record_links ("
+ "id integer PRIMARY KEY NOT NULL,"
+ "record1 integer NOT NULL,"
+ "record2 integer NOT NULL,"
+ "type integer NOT NULL,"
+ "/* Foreign keys */"
+ "FOREIGN KEY (record1)"
+ " REFERENCES budget_record(id),"
+ "FOREIGN KEY (record2)"
+ " REFERENCES budget_record(id));");
db.execSQL("CREATE TABLE budget_duty_plan ("
+ "id integer PRIMARY KEY NOT NULL,"
+ "is_change_plan boolean NOT NULL,"
+ "kmh_category_id integer NOT NULL,"
+ "kmh_id integer NOT NULL,"
+ "/* Foreign keys */"
+ "FOREIGN KEY (kmh_category_id)"
+ " REFERENCES budget_object(id),"
+ "FOREIGN KEY (id)"
+ " REFERENCES budget_record(id),"
+ "FOREIGN KEY (kmh_id)"
+ " REFERENCES budget_object(id));");
db.execSQL("CREATE TABLE budget_duty_record ("
+ "real_record_id integer NOT NULL,"
+ "kmh_record_id integer NOT NULL,"
+ "currency_id integer NOT NULL,"
+ "kmh_id integer NOT NULL,"
+ "/* Foreign keys */"
+ "FOREIGN KEY (kmh_id)"
+ " REFERENCES budget_object(id),"
+ "FOREIGN KEY (currency_id)"
+ " REFERENCES currency(id),"
+ "FOREIGN KEY (kmh_record_id)"
+ " REFERENCES budget_record(id),"
+ "FOREIGN KEY (real_record_id)"
+ " REFERENCES budget_record(id));");
db.execSQL("CREATE TABLE client_settings ("
+ "val char(255) NOT NULL,"
+ "\"key\" char(255) NOT NULL,"
+ "userId numeric(19));");
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
onCreate(db);
}
}
public int delete(Uri uri, String selection, String[] selectionArgs) {
// TODO Auto-generated method stub
return 0;
}
public String getType(Uri uri) {
// TODO Auto-generated method stub
return null;
}
public Uri insert(Uri uri, ContentValues values) {
db= mOpenHelper.getWritableDatabase();
// TODO Auto-generated method stub
return null;
}
/* public boolean onCreate() {
mOpenHelper = new DatabaseHelper(getContext());
return (mOpenHelper == null) ? false : true;
}*/
public DBAdapter(Context ctx)
{
this.context = ctx;
mOpenHelper = new DatabaseHelper(context);
}
public Cursor query(Uri uri, String[] projection, String selection,
String[] selectionArgs, String sortOrder) {
// TODO Auto-generated method stub
return null;
}
public int update(Uri uri, ContentValues values, String selection,
String[] selectionArgs) {
// TODO Auto-generated method stub
return 0;
}
public DBAdapter open() throws SQLException
{
db = mOpenHelper.getWritableDatabase();
return this;
}
public void clear()
{
db.delete("budget_family", null, null);
db.delete("users", null, null);
db.delete("currency", null, null);
db.delete("budget_win32_sync", null, null);
db.delete("budget_family_member", null, null);
db.delete("budget_object_type", null, null);
db.delete("budget_plan", null, null);
db.delete("budget_object", null, null);
db.delete("budget_object_count", null, null);
db.delete("budget_object_tags", null, null);
db.delete("budget_plan_category", null, null);
db.delete("budget_record", null, null);
db.delete("budget_record_links", null, null);
db.delete("budget_duty_plan", null, null);
db.delete("budget_duty_record", null, null);
db.delete("client_settings", null, null);
}
public void close()
{
mOpenHelper.close();
}
public long setUser(ContentValues values)
{
try{
long row;
Cursor mCursor=getUser(values.get("id").toString());
if (mCursor != null) {
mCursor.moveToFirst();
}
if(mCursor.getCount()<1)
row = db.insertOrThrow("users", null, values);
// row=db.insert("users", null, values);
else
row=db.update("users", values, "id=?",new String[]{values.get("id").toString()});
mCursor.close();
return row;
} catch (Exception e) {
throw new SQLException();
}
}
public int getFamilyId() throws SQLException
{
Cursor mCursor=db.query(true,"budget_family",new String[]{"id"},null,null,null, null, null, null);
int count=mCursor.getCount();
if (mCursor != null) {
mCursor.moveToFirst();
}
int ret;
if (count<1)
ret= -1;
else
ret=mCursor.getInt(0);
mCursor.close();
return ret;
}
public long setFamilyId(int id, long user_id) throws SQLException
{
try{
ContentValues values = new ContentValues();
values.put("id", id) ;
values.put("name", " ");
long row=db.insertOrThrow("budget_family", null, values);
values.clear();
values.put("nuid", user_id);
values.put("budget_family_id", id);
row+=db.insertOrThrow("budget_family_member", null, values);
values.clear();
values.put("id", 2);
values.put("name", " ");
values.put("budget_family_id", id);
row+=db.insertOrThrow("budget_object_type", null, values);
values.clear();
values.put("id", 3);
values.put("name", " ");
values.put("budget_family_id", id);
row+=db.insertOrThrow("budget_object_type", null, values);
values.clear();
values.put("id", 4);
values.put("name", " ");
values.put("budget_family_id", id);
row+=db.insertOrThrow("budget_object_type", null, values);
return row;
} catch (Exception e) {
throw new SQLException();
}
}
public long setSync(String syncClass, String type, ContentValues values, ContentValues parent) throws SQLException
{
long row = 0;
int i=getFamilyId();
if(syncClass.equals("syncCurrency")){
if(type.equals("insert"))
{
values.put("family_id",i);
row=db.insertOrThrow("currency", null, values);
}} else if (syncClass.equals("syncObject")){
if(type.equals("insert"))
{
values.put("budget_family_id",i);
values.put("id", values.get("server_id").toString());
values.put("sort", values.get("server_id").toString());
row=db.insertOrThrow("budget_object", null, values);
}
} else if (syncClass.equals("syncRecord")){
if(type.equals("insert"))
{
values.put("id", values.get("server_id").toString());
// values.put("budget_account_id
// values.put("budget_object_id
values.put("budget_family_id",i);
// values.put("currency_id
// values.put("user_nuid
// values.put("account_rest //??return
// values.put("difference
// values.put("operation_date
// values.put("comment
// values.put("is_currency_change
// values.put("is_duty", parent.get("is_duty").toString());
// values.put("server_id
row=db.insertOrThrow("budget_record", null, values);
if(parent.containsKey("link_id")){
if (parent.getAsInteger("link_id")>values.getAsInteger("server_id")){
ContentValues link=new ContentValues ();
link.put( "record1",values.getAsInteger("server_id"));
link.put( "record2",parent.getAsInteger("link_id"));
if(values.getAsBoolean("is_currency_change")){
link.put("type",2);
}else{
link.put("type",1);
}
row=db.insertOrThrow("budget_record_links", null, link);
}
}
}
}
return row;
}
public Cursor getUser(String Id) throws SQLException
{
Cursor mCursor =
db.query(true, "users", new String[] {
"nick",
"pass",
"is_male" },
"id=" + Id,
null,
null,
null,
null,
null);
if (mCursor != null) {
mCursor.moveToFirst();
}
return mCursor;
}
public String[] getCurrency(){
Cursor cursor = db.query(true,"currency", new String[]{
"name",
"course"}, null, null, null, null, "name", null);
String[] name=null;
if (cursor != null) {
cursor.moveToFirst();
int i=0;
name=new String[cursor.getCount()];
while(i<cursor.getCount())
{
name[i]=cursor.getString(0)+" = "+cursor.getString(1);
i++;
cursor.moveToNext();
}
}
cursor.close();
return name;
}
int posinarray=0;
int level=0;
public String[] getObjectClass(int type) throws SQLException
{
Cursor cursor = db.query(true,"budget_object", new String[]{
"id",
"parent_id",
"budget_family_id",
"type",
"name",
"purse_of_nuid",
"is_hidden",
"is_for_duty",
"is_credit_card",
"server_id",
"sort"}, "type="+type , null, null, null, "parent_id,name", null);
String[] name=null;
if (cursor != null) {
cursor.moveToFirst();
posinarray=0;
SparseIntArray sia = new SparseIntArray();
int i=0;
int pid=cursor.getInt(1);
name=new String[cursor.getCount()];
sia.append(pid,i);
while (cursor.moveToNext()){
i++;
if (cursor.getInt(1)!=pid)
sia.put(cursor.getInt(1),i);
pid=cursor.getInt(1);
}
cursor.moveToFirst();
int id=0;
while(cursor.getInt(1)<0)
{
level=0;
id=cursor.getInt(0);
name[posinarray]=cursor.getString(4);
posinarray++;
id=sia.get(id);
if (id!=0)
{
int poscursor=cursor.getPosition();
cursor.moveToPosition(id);
name=Savechild(name,cursor,sia);
cursor.moveToPosition(poscursor);
}
if (!cursor.moveToNext())
{
cursor.close();
return name;
}
}
}
int i2=cursor.getCount();
for (int i=0;i<(i2-posinarray);i++)
{
name[i2-i-1]=" ";
}
cursor.close();
return name;
}
String[] Savechild(String[]name,Cursor cursor,SparseIntArray sia)
{
int poscursor=0;
int pid=cursor.getInt(1);
String stlevel=" ";
level++;
for(int i=0;i<level;i++)
{
stlevel+="-";
}
int id;
while(cursor.getInt(1)==pid)
{
name[posinarray]=stlevel+" "+cursor.getString(4);
posinarray++;
id=cursor.getInt(0);
id=sia.get(id);
if (id!=0)
{
poscursor=cursor.getPosition();
cursor.moveToPosition(id);
name=Savechild(name,cursor,sia);
cursor.moveToPosition(poscursor);
}
if (!cursor.moveToNext())
return name;
}
return name;
}
}
|