package com.b2lmobitech.spottest.sql;

/**
 * Created by home on 2/8/2018.
 */

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;


import com.b2lmobitech.spottest.encaps.Answer;
import com.b2lmobitech.spottest.encaps.Option;
import com.b2lmobitech.spottest.encaps.Question;
import com.b2lmobitech.spottest.encaps.Section;
import com.b2lmobitech.spottest.sql.tables.AnswerTable;
import com.b2lmobitech.spottest.sql.tables.OptionTable;
import com.b2lmobitech.spottest.sql.tables.QuestionTable;
import com.b2lmobitech.spottest.sql.tables.ReferenceTable;
import com.b2lmobitech.spottest.sql.tables.SectionTable;
import com.b2lmobitech.spottest.utils.Utility;

import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;

import java.sql.Ref;
import java.util.ArrayList;
import java.util.Collections;


public class DataBase extends SQLiteOpenHelper {

    private static final String DATABASE_NAME = "survey.db3";
    private static final int DATABASE_VERSION = 1;

    // Variable to hold the database instance
    public SQLiteDatabase db;

    // Context of the application using the database.
    private final Context context;

    // Database open/upgrade helper
    private DataBase dbHelper;

    public DataBase(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        this.context=context;
    }

    @Override
    public void onCreate(SQLiteDatabase _db) {
        _db.execSQL(QuestionTable.CREATE_TABLE);
        _db.execSQL(OptionTable.CREATE_TABLE);
        _db.execSQL(AnswerTable.CREATE_TABLE);
        _db.execSQL(ReferenceTable.CREATE_TABLE);
        _db.execSQL(SectionTable.CREATE_TABLE);

        //   _db.execSQL(LoginDataBaseAdapter.CREATE_TABLE_TRACKDB);
    }

    @Override
    public void onUpgrade(SQLiteDatabase _db, int _oldVersion, int _newVersion) {}

    public void insertOptions(JSONObject jsonObject,String assignId){

        try {
            SQLiteDatabase sdb = getWritableDatabase();

            ContentValues cv = new ContentValues();
            cv.put(OptionTable.OPTIONID, jsonObject.getString("option_id"));
            cv.put(OptionTable.OPTIONNAME, jsonObject.getString("option_name"));
            cv.put(OptionTable.QUESTIONID, jsonObject.getString("question_id"));
            cv.put(OptionTable.ASSIGNID,assignId);

            sdb.insertWithOnConflict(OptionTable.TABLENAME,null, cv, SQLiteDatabase.CONFLICT_REPLACE);
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    public void insertReference(JSONObject jsonObject,String assignId) throws JSONException {
         SQLiteDatabase sdb = getWritableDatabase();

         ContentValues cv = new ContentValues();
         cv.put(ReferenceTable.OPTIONID, jsonObject.getString("option_id"));
         cv.put(ReferenceTable.QUESTIONID, jsonObject.getString("question_id"));
         cv.put(ReferenceTable.ASSIGNID,assignId);
         sdb.insertWithOnConflict(ReferenceTable.TABLENAME,null, cv, SQLiteDatabase.CONFLICT_REPLACE);
    }

    public void insertQuestion(JSONObject jsonObject){
        try {
            SQLiteDatabase sdb = getWritableDatabase();
            ContentValues cv = new ContentValues();
            cv.put(QuestionTable.QUESTIONID, jsonObject.getString(QuestionTable.QUESTIONID));
            cv.put(QuestionTable.QUESTIONNAME, jsonObject.getString(QuestionTable.QUESTIONNAME));
            cv.put(QuestionTable.QUESTIONTYPE, jsonObject.getString(QuestionTable.QUESTIONTYPE));
            cv.put(QuestionTable.TESTID, QuestionTable.TESTID);
            cv.put(QuestionTable.SECTIONID, jsonObject.getString(QuestionTable.SECTIONID));
            cv.put(QuestionTable.ASSIGNID, jsonObject.getString(QuestionTable.ASSIGNID));
            cv.put(QuestionTable.MARK, jsonObject.getString(QuestionTable.MARK));

            sdb.insertWithOnConflict(QuestionTable.TABLENAME,null, cv, SQLiteDatabase.CONFLICT_REPLACE);
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
    public void Updatesectionanswer(String sectionid,int answer){
        try {
            SQLiteDatabase sdb = getWritableDatabase();
            ContentValues cv = new ContentValues();
            cv.put(SectionTable.ANSWERED, answer);
            sdb.updateWithOnConflict(SectionTable.TABLENAME,cv, SectionTable.SECTIONID+" = ? " ,new String[]{sectionid}, SQLiteDatabase.CONFLICT_REPLACE);
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
//--swahti--//
public void insertsection(JSONObject jsonObject,int count,int answeredvalue){
    try {
        SQLiteDatabase sdb = getWritableDatabase();
        ContentValues cv = new ContentValues();
        cv.put(SectionTable.PAPERID, jsonObject.getString(SectionTable.PAPERID));
        cv.put(SectionTable.NAME, jsonObject.getString(SectionTable.NAME));
        cv.put(SectionTable.SECTIONID, jsonObject.getString(SectionTable.SECTIONID));
        cv.put(SectionTable.DESCRIPTION, SectionTable.DESCRIPTION);
        cv.put(SectionTable.ANSWERED, answeredvalue);
        cv.put(SectionTable.COUNT, count);
        sdb.insertWithOnConflict(SectionTable.TABLENAME,null, cv, SQLiteDatabase.CONFLICT_REPLACE);
    } catch (Exception e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
}


    //--swathi--//
    public ArrayList<Option> getOptions(String questionId,String assignId){
        ArrayList<Option> arrayList=new ArrayList<>();
        SQLiteDatabase sdb = getReadableDatabase();
        String query="SELECT * FROM "+OptionTable.TABLENAME +" WHERE "+OptionTable.QUESTIONID+"='"+questionId
                +"' AND "+ OptionTable.ASSIGNID+"='"+assignId+"'";


        Log.d("option_query",query);

        Cursor cursor = sdb.rawQuery(query,null);
        if(cursor.getCount() > 0) {
            cursor.moveToFirst();
            do{
                Option option=new Option();
                option.setOptionId(cursor.getString(cursor.getColumnIndex(OptionTable.OPTIONID)));
                option.setQuestionId(cursor.getString(cursor.getColumnIndex(OptionTable.QUESTIONID)));
                option.setOptionName(cursor.getString(cursor.getColumnIndex(OptionTable.OPTIONNAME)));
                arrayList.add(option);
            }while (cursor.moveToNext());
        }
        cursor.close();
        return arrayList;
    }

    public ArrayList<Question> getQuestions(String sectionId,String assignId){
        ArrayList<Question> arrayList=new ArrayList<>();
        SQLiteDatabase sdb = getWritableDatabase();
        String query="SELECT * FROM "+QuestionTable.TABLENAME +" WHERE "+QuestionTable.SECTIONID+"='"+sectionId+"' AND "
                +QuestionTable.ASSIGNID+"='"+assignId+"'";
        Log.d("query",query);

        Cursor cursor = sdb.rawQuery(query,null);
        if(cursor.getCount() > 0)
        {
            cursor.moveToFirst();
            do{
                Question question=new Question();
                question.setQuestionId(cursor.getString(cursor.getColumnIndex(QuestionTable.QUESTIONID)));
                question.setQuestionName(cursor.getString(cursor.getColumnIndex(QuestionTable.QUESTIONNAME)));
                question.setQuestionType(cursor.getString(cursor.getColumnIndex(QuestionTable.QUESTIONTYPE)));
                question.setSectionId(cursor.getString(cursor.getColumnIndex(QuestionTable.SECTIONID)));
                arrayList.add(question);

            }while (cursor.moveToNext());
        }
        cursor.close();
        return arrayList;
    }
    public ArrayList<Section> getSections( ){
        ArrayList<Section> arrayList=new ArrayList<>();
        SQLiteDatabase sdb = getWritableDatabase();
        String query="SELECT * FROM "+SectionTable.TABLENAME ;
        Log.d("query",query);

        Cursor cursor = sdb.rawQuery(query,null);
        if(cursor.getCount() > 0)
        {
            cursor.moveToFirst();
            do{
                Section section=new Section();
                section.setpaperId(cursor.getString(cursor.getColumnIndex(SectionTable.PAPERID)));
                section.setsectionId(cursor.getString(cursor.getColumnIndex(SectionTable.SECTIONID)));
                section.setsectionname(cursor.getString(cursor.getColumnIndex(SectionTable.NAME)));
                section.setdescription(cursor.getString(cursor.getColumnIndex(SectionTable.DESCRIPTION)));
                section.setcounts(cursor.getInt(cursor.getColumnIndex(SectionTable.COUNT)));
                section.setanswered(cursor.getInt(cursor.getColumnIndex(SectionTable.ANSWERED)));
                arrayList.add(section);

            }while (cursor.moveToNext());
        }
        cursor.close();
        return arrayList;
    }

    public ArrayList<Question> getQuestionInRandomOrder(String sectionId,String assignId){
        ArrayList<Question> arrayList=new ArrayList<>();
        SQLiteDatabase sdb = getWritableDatabase();
        String query="SELECT * " +
                "FROM "+QuestionTable.TABLENAME +" q"
                +" LEFT JOIN "+AnswerTable.TABLENAME+" a ON a." +AnswerTable.QUESTIONID+"= q."+QuestionTable.QUESTIONID
                +" AND a." +AnswerTable.ASSIGNID+"= '"+assignId+"'"
                +" WHERE q."+QuestionTable.SECTIONID+"='"+sectionId+"' AND q." +QuestionTable.ASSIGNID+"='"+assignId+"'"
                +" AND a."+AnswerTable.QUESTIONID+" IS NULL";
        Log.d("query",query);

        Cursor cursor = sdb.rawQuery(query,null);

        if(cursor.getCount() > 0)
        {
            cursor.moveToFirst();
            do{
                Question question=new Question();
                question.setQuestionId(cursor.getString(cursor.getColumnIndex(QuestionTable.QUESTIONID)));
                question.setQuestionName(cursor.getString(cursor.getColumnIndex(QuestionTable.QUESTIONNAME)));
                question.setQuestionType(cursor.getString(cursor.getColumnIndex(QuestionTable.QUESTIONTYPE)));
                question.setSectionId(cursor.getString(cursor.getColumnIndex(QuestionTable.SECTIONID)));
                arrayList.add(question);

            }while (cursor.moveToNext());
        }
        Collections.shuffle(arrayList);
        cursor.close();
        return arrayList;
    }

    public JSONArray getQuestionAnswers(String sectionId,String assignId){
        JSONArray jsonArray=new JSONArray();
        SQLiteDatabase sdb = getWritableDatabase();

        String query="SELECT q." +QuestionTable.QUESTIONNAME+",q." +QuestionTable.QUESTIONID+",a."+AnswerTable.OPTION+",a."+AnswerTable.OPTIONID+
                " FROM "+QuestionTable.TABLENAME +" q "
                +" LEFT JOIN "+AnswerTable.TABLENAME+" a ON a." +AnswerTable.QUESTIONID+"= q."+QuestionTable.QUESTIONID
                +" AND a." +AnswerTable.ASSIGNID+" = q."+QuestionTable.ASSIGNID
                +" WHERE q."+QuestionTable.SECTIONID+"='"+sectionId+"' AND q." +QuestionTable.ASSIGNID+"='"+assignId+"'";

        Log.d("query",query);

        Cursor cursor = sdb.rawQuery(query,null);

        if(cursor.getCount() > 0)
        {
            Log.d("counts",cursor.getCount()+"");

            cursor.moveToFirst();
            do{
                try {
                    JSONObject jsonObject=new JSONObject();
                    jsonObject.put(QuestionTable.QUESTIONNAME,cursor.getString(cursor.getColumnIndex(QuestionTable.QUESTIONNAME)));
                    if(cursor.getString(cursor.getColumnIndex(AnswerTable.OPTION))==null){
                        jsonObject.put(AnswerTable.OPTION,"null");
                    }else{
                        jsonObject.put(AnswerTable.OPTION,cursor.getString(cursor.getColumnIndex(AnswerTable.OPTION)));
                    }
                    jsonObject.put(AnswerTable.OPTIONID,cursor.getString(cursor.getColumnIndex(AnswerTable.OPTIONID)));
                    jsonObject.put(QuestionTable.QUESTIONID,cursor.getString(cursor.getColumnIndex(QuestionTable.QUESTIONID)));
                    jsonArray.put(jsonObject);
                } catch (JSONException e) {
                    e.printStackTrace();
                }
            }while (cursor.moveToNext());

        }
        cursor.close();
        return jsonArray;
    }

    public ArrayList<Answer> getAnswers(String sectionId,String assignId){
        ArrayList<Answer> arrayList=new ArrayList<>();
        SQLiteDatabase sdb = getWritableDatabase();
        String query="SELECT * FROM "+ AnswerTable.TABLENAME +" WHERE "+AnswerTable.SECTIONID+"='"+sectionId+"' AND "
                +AnswerTable.ASSIGNID+"='"+assignId+"'";
        Log.d("query",query);

        Cursor cursor = sdb.rawQuery(query,null);
        if(cursor!=null && cursor.getCount() > 0 && cursor.moveToFirst())
        {
            do{
                Answer answer=new Answer();
                answer.setQuestionId(cursor.getString(cursor.getColumnIndex(AnswerTable.QUESTIONID)));
                answer.setOptionId(cursor.getString(cursor.getColumnIndex(AnswerTable.OPTIONID)));
                answer.setOption(cursor.getString(cursor.getColumnIndex(AnswerTable.OPTION)));
                answer.setAssignId(cursor.getString(cursor.getColumnIndex(AnswerTable.ASSIGNID)));
                answer.setDatetime(cursor.getString(cursor.getColumnIndex(AnswerTable.DATETIME)));
                arrayList.add(answer);
            }while (cursor.moveToNext());
        }
        cursor.close();

        return arrayList;
    }

    public JSONArray getAnswers(String assignId)  {
        SQLiteDatabase sdb = getWritableDatabase();
        JSONArray jsonArray=new JSONArray();
        String query="SELECT * FROM "+ AnswerTable.TABLENAME +" WHERE "+AnswerTable.ASSIGNID+"='"+assignId+"'";

        Cursor cursor = sdb.rawQuery(query,null);
        if(cursor!=null && cursor.getCount() > 0 && cursor.moveToFirst())
        {
            do{
                JSONObject jsonObject=new JSONObject();
                try {
                    if(!cursor.getString(cursor.getColumnIndex(AnswerTable.OPTIONID)).isEmpty()) {
                        jsonObject.put(AnswerTable.QUESTIONID, cursor.getString(cursor.getColumnIndex(AnswerTable.QUESTIONID)));
                        jsonObject.put(AnswerTable.OPTION, cursor.getString(cursor.getColumnIndex(AnswerTable.OPTION)));
                        jsonObject.put(AnswerTable.OPTIONID, cursor.getString(cursor.getColumnIndex(AnswerTable.OPTIONID)));
                        jsonObject.put(AnswerTable.ISIMAGE, cursor.getString(cursor.getColumnIndex(AnswerTable.ISIMAGE)));
                        jsonObject.put(AnswerTable.SECTIONID, cursor.getString(cursor.getColumnIndex(AnswerTable.SECTIONID)));
                        jsonObject.put(AnswerTable.DATETIME, cursor.getString(cursor.getColumnIndex(AnswerTable.DATETIME)));
                        jsonArray.put(jsonObject);
                    }
                } catch (JSONException e) {
                    e.printStackTrace();
                }

            }while (cursor.moveToNext());
        }
        cursor.close();

        return jsonArray;
    }

    public void insertAnswer(String questionId, String sectionId,String assignId, String answer, String optionId, boolean isBlob){
        SQLiteDatabase sdb = getWritableDatabase();
        ContentValues cv = new ContentValues();
        cv.put(AnswerTable.ASSIGNID,assignId);
        cv.put(AnswerTable.QUESTIONID,questionId);
        cv.put(AnswerTable.OPTIONID,optionId);
        cv.put(AnswerTable.SECTIONID,sectionId);
        if(isBlob){
            cv.put(AnswerTable.ISIMAGE,"yes");
            cv.put(AnswerTable.OPTION,answer);
        }else{
            cv.put(AnswerTable.OPTION,answer);
        }
        cv.put(AnswerTable.INSERTED,"no");

        cv.put(AnswerTable.DATETIME, Utility.getCurrentDateTime());

        sdb.insertWithOnConflict(AnswerTable.TABLENAME,null, cv, SQLiteDatabase.CONFLICT_REPLACE);
    }

    public void deleteAnswered(String assignId){
        SQLiteDatabase sdb = getWritableDatabase();
        String where = AnswerTable.ASSIGNID+"= ?"  ;
        sdb.delete(AnswerTable.TABLENAME, where, new String[] { assignId });
    }
    public void deletesectiontable(){
        SQLiteDatabase sdb = getWritableDatabase();
        sdb.delete(SectionTable.TABLENAME, null,null);
    }

    public boolean isQuestionsAlreadyExists(String assignId){
        SQLiteDatabase sdb = getWritableDatabase();
        String query="SELECT * FROM "+QuestionTable.TABLENAME +" WHERE "+QuestionTable.ASSIGNID+"='"+assignId+"'";
        Cursor cursor = sdb.rawQuery(query,null);
        return cursor!=null && cursor.getCount() > 0;

    }

    public boolean isOptionsAlreadyExists(String assignId){
        SQLiteDatabase sdb = getWritableDatabase();
        String query="SELECT * FROM "+OptionTable.TABLENAME +" WHERE "+OptionTable.ASSIGNID+"='"+assignId+"'";
        Cursor cursor = sdb.rawQuery(query,null);
        return cursor!=null && cursor.getCount() > 0;

    }

    public boolean isAnwsersAlreadyExists(String assignId){
        SQLiteDatabase sdb = getWritableDatabase();
        String query="SELECT * FROM "+ReferenceTable.TABLENAME +" WHERE "+ReferenceTable.ASSIGNID+"='"+assignId+"'";
        Cursor cursor = sdb.rawQuery(query,null);
        return cursor!=null && cursor.getCount() > 0;
    }

    public JSONArray getCorrectAnswersCount(String assignId,String sectionId)  {
        SQLiteDatabase sdb = getWritableDatabase();
        JSONArray jsonArray=new JSONArray();

        String query="SELECT *" +
                " FROM "+ AnswerTable.TABLENAME +" a"
                +" INNER JOIN "+ ReferenceTable.TABLENAME+" r" +" ON r."+ReferenceTable.QUESTIONID+"=a."+ AnswerTable.QUESTIONID
                +" AND r."+ReferenceTable.OPTIONID+" = a."+AnswerTable.OPTIONID
                +" WHERE "+AnswerTable.ASSIGNID+"='"+assignId+"' AND "+AnswerTable.SECTIONID+"='"+sectionId+"'";

         query="SELECT *" +
                " FROM "+ AnswerTable.TABLENAME +" a"
                +" INNER JOIN "+ ReferenceTable.TABLENAME+" r" +" ON r."+ReferenceTable.QUESTIONID+"=a."+ AnswerTable.QUESTIONID
                +" AND r."+ReferenceTable.OPTIONID+" = a."+AnswerTable.OPTIONID
                +" AND a."+AnswerTable.ASSIGNID+" = r."+ReferenceTable.ASSIGNID
                +" INNER JOIN "+ QuestionTable.TABLENAME+" q" +" ON r."+ReferenceTable.QUESTIONID+"=q."+ QuestionTable.QUESTIONID
                +" AND q."+QuestionTable.ASSIGNID+" = r."+ReferenceTable.ASSIGNID
                +" WHERE a."+AnswerTable.ASSIGNID+"='"+assignId+"' AND a."+AnswerTable.SECTIONID+"='"+sectionId+"'";

        Cursor cursor = sdb.rawQuery(query,null);

        if(cursor!=null && cursor.getCount() > 0 && cursor.moveToFirst()) {
            do{
                JSONObject jsonObject=new JSONObject();
                try {
                    jsonObject.put(AnswerTable.QUESTIONID,cursor.getString(cursor.getColumnIndex(AnswerTable.QUESTIONID)));
                    jsonArray.put(jsonObject);
                } catch (JSONException e) {
                    e.printStackTrace();
                }

            }while (cursor.moveToNext());
        }
        cursor.close();

        return jsonArray;
    }

    public String getOptionName(String optionId){
        SQLiteDatabase sdb = getWritableDatabase();

        String query="SELECT " +OptionTable.OPTIONNAME+
                " FROM "+ OptionTable.TABLENAME +" WHERE "+OptionTable.OPTIONID+"='"+optionId+"'";
        Log.d("query",query);
        Cursor cursor = sdb.rawQuery(query,null);

        if(cursor!=null && cursor.getCount() > 0 && cursor.moveToFirst()) {
            return cursor.getString(cursor.getColumnIndex(OptionTable.OPTIONNAME));
        }else{
            Log.d("query","empty");

        }
        cursor.close();

        return "";
    }

    public String getCorrectAnswerId(String assignId,String questionId){
        SQLiteDatabase sdb = getWritableDatabase();

        String query="SELECT " +ReferenceTable.OPTIONID+ " FROM "+ ReferenceTable.TABLENAME
                +" WHERE "+ReferenceTable.QUESTIONID+"='"+questionId+"'";
        Log.d("query",query);
        Cursor cursor = sdb.rawQuery(query,null);

        if(cursor!=null && cursor.getCount() > 0 && cursor.moveToFirst()) {
            return cursor.getString(cursor.getColumnIndex(ReferenceTable.OPTIONID));
        }else{
            Log.d("query","empty");

        }
        cursor.close();

        return "";
    }

    public int getMarks(String assignId)  {
        SQLiteDatabase sdb = getWritableDatabase();
        JSONArray jsonArray=new JSONArray();
        String query="SELECT SUM("+QuestionTable.MARK+") as marks" +
                " FROM "+ AnswerTable.TABLENAME +" a"
                +" INNER JOIN "+ ReferenceTable.TABLENAME+" r" +" ON r."+ReferenceTable.QUESTIONID+"=a."+ AnswerTable.QUESTIONID
                +" AND r."+ReferenceTable.OPTIONID+" = a."+AnswerTable.OPTIONID
                +" AND a."+AnswerTable.ASSIGNID+" = r."+ReferenceTable.ASSIGNID
                +" INNER JOIN "+ QuestionTable.TABLENAME+" q" +" ON r."+ReferenceTable.QUESTIONID+"=q."+ QuestionTable.QUESTIONID
                +" AND q."+QuestionTable.ASSIGNID+" = r."+ReferenceTable.ASSIGNID
                +" WHERE a."+AnswerTable.ASSIGNID+"='"+assignId+"'";

        Cursor cursor = sdb.rawQuery(query,null);

        if(cursor!=null && cursor.getCount() > 0 && cursor.moveToFirst()) {
            return cursor.getInt(cursor.getColumnIndex("marks"));
        }
        cursor.close();

        return 0;
    }

    public int getTotalMarks(String assignId)  {
        SQLiteDatabase sdb = getWritableDatabase();
        JSONArray jsonArray=new JSONArray();
        String query="SELECT SUM("+QuestionTable.MARK+") as marks" +
                " FROM "+ QuestionTable.TABLENAME
                +" WHERE "+QuestionTable.ASSIGNID+"='"+assignId+"'";
        Cursor cursor = sdb.rawQuery(query,null);
        if(cursor!=null && cursor.getCount() > 0 && cursor.moveToFirst()) {
            return cursor.getInt(cursor.getColumnIndex("marks"));
        }
        cursor.close();

        return 0;
    }
    //--swathi--
    public void deleteoptions(String assignId){
        SQLiteDatabase sdb = getWritableDatabase();
        String where = OptionTable.ASSIGNID+"= ?"  ;
        sdb.delete(OptionTable.TABLENAME, where, new String[] { assignId });
    }
    public void deletequestions(String assignId){
        SQLiteDatabase sdb = getWritableDatabase();
        String where = QuestionTable.ASSIGNID+"= ?"  ;
        sdb.delete(QuestionTable.TABLENAME, where, new String[] { assignId });
    }
    public JSONArray getAnswers_dontknow(String assignId)  {
        SQLiteDatabase sdb = getWritableDatabase();
        JSONArray jsonArray=new JSONArray();
        String query="SELECT * FROM "+ AnswerTable.TABLENAME +" WHERE "+AnswerTable.ASSIGNID+"='"+assignId+"'";

        Cursor cursor = sdb.rawQuery(query,null);
        if(cursor!=null && cursor.getCount() > 0 && cursor.moveToFirst())
        {
            do{
                JSONObject jsonObject=new JSONObject();
                try {
                    if(cursor.getString(cursor.getColumnIndex(AnswerTable.OPTIONID)).isEmpty()) {
                        jsonObject.put(AnswerTable.QUESTIONID, cursor.getString(cursor.getColumnIndex(AnswerTable.QUESTIONID)));
                        jsonObject.put(AnswerTable.OPTION, cursor.getString(cursor.getColumnIndex(AnswerTable.OPTION)));
                        jsonObject.put(AnswerTable.OPTIONID, cursor.getString(cursor.getColumnIndex(AnswerTable.OPTIONID)));
                        jsonObject.put(AnswerTable.ISIMAGE, cursor.getString(cursor.getColumnIndex(AnswerTable.ISIMAGE)));
                        jsonObject.put(AnswerTable.SECTIONID, cursor.getString(cursor.getColumnIndex(AnswerTable.SECTIONID)));
                        jsonObject.put(AnswerTable.DATETIME, cursor.getString(cursor.getColumnIndex(AnswerTable.DATETIME)));
                        jsonArray.put(jsonObject);
                    }
                } catch (JSONException e) {
                    e.printStackTrace();
                }

            }while (cursor.moveToNext());
        }
        cursor.close();

        return jsonArray;
    }
    public int getAnswerscount(String assignId)  {
        int count =0;
        SQLiteDatabase sdb = getWritableDatabase();
        JSONArray jsonArray=new JSONArray();
        String query="SELECT * FROM "+ AnswerTable.TABLENAME +" WHERE "+AnswerTable.ASSIGNID+"='"+assignId+"'";

        Cursor cursor = sdb.rawQuery(query,null);
        if(cursor!=null && cursor.getCount() > 0 && cursor.moveToFirst())
        {
            count = cursor.getCount();
        }
        else
        {
            count = 0;
        }
        cursor.close();

        return count;
    }
    //--swathi--

}
