package com.example.waoperations.CycleCount;

import java.sql.Date;

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

public class CycleCountDatabaseHelper {

        private static final int DATABASE_VERSION = 5;
        private static final String DATABASE_NAME = "WMSCCount.db";
        private static final String TABLE_NAME = "CycleCount_Table";
        private static final String CYCLECOUNT_ID = "_id";
        private static final String PONUMBER = "ponumber";
        private static final String LOCATION = "posicion";
        private static final String SKU = "articulo";
        private static final String DESCRIPTION = "description";
        private static final String QTY = "cantidad";
        private static final String TYPE = "tipo";
        private static final String AMOUNTCOUNTED = "cantidadsacada";
        private static final String SCANNED = "scaneado";
        private static final String ITEMBARCODE = "barcode";
        private static final String ITEMLINE = "line";
        private static final String FACTOR = "factor";
        private static final String COUNTRY = "country";
        private ContactOpenHelper openHelper;
        private SQLiteDatabase database;

        public CycleCountDatabaseHelper(Context context) {
                openHelper = new ContactOpenHelper(context);
                database = openHelper.getWritableDatabase();
        }
        
        public void saveCycleCountLine(String ponumber,String Location,String sku,String description,String qty,String type,String amountcounted,String scanned,String barcode,String line,String factor,String country) {
        	    ContentValues contentValues = new ContentValues();
                contentValues.put(PONUMBER, ponumber);
                contentValues.put(LOCATION, Location);
                contentValues.put(SKU, sku);
                contentValues.put(DESCRIPTION, description);
                contentValues.put(QTY, qty);
                contentValues.put(TYPE, type);
                contentValues.put(AMOUNTCOUNTED, amountcounted);
                contentValues.put(SCANNED, scanned);
                contentValues.put(ITEMBARCODE, barcode);
                contentValues.put(ITEMLINE, line);
                contentValues.put(FACTOR, factor);
                contentValues.put(COUNTRY, country);
                database.insert(TABLE_NAME, null, contentValues);
        }
    
    public void deleteAllData()
        {
        database.delete(TABLE_NAME, null, null);
        }
    
    public Cursor getAllCycleCountLines(String ponumber) {
                return database.rawQuery(
                             "SELECT * FROM " + TABLE_NAME+" WHERE " +PONUMBER+ " ='"
                                     +ponumber+"'",
                             null);
        } 
        public Cursor getCycleCountLines(String ponumber) {
            return database.rawQuery(
                            "SELECT * FROM " + TABLE_NAME+" WHERE " +PONUMBER+ " ='"
                            +ponumber+"'",
                            null);
        }  

        
        public void UpdateScannedItem(String amountpicked,String id) {
    	    ContentValues contentValues = new ContentValues();
            contentValues.put(AMOUNTCOUNTED , amountpicked);
            contentValues.put(SCANNED , "1");
            String strFilter = "_id=" + id;
            database.update(TABLE_NAME, contentValues, strFilter, null);    
        }  
        public void UpdateScannedAmountItem(String qty,String id) {
    	    ContentValues contentValues = new ContentValues();
            contentValues.put(QTY , qty);
            String strFilter = "_id=" + id;
            database.update(TABLE_NAME, contentValues, strFilter, null);    
        }
        
        
        public Cursor getNonScannedItemsPerPO(String PO) {
            String query= "SELECT * FROM "+TABLE_NAME+" where "+SCANNED+"='"+0+"' and "+ PONUMBER+"='"+PO+"' LIMIT 1";
            return database.rawQuery(query,null);
            }
        
        public Cursor getCountItemBarcode(String ponumber,String barcode) {
            return database.rawQuery(
                 "SELECT * FROM " + TABLE_NAME+" WHERE " +LOCATION+ " ='"
                 +ponumber+"' AND "+ITEMBARCODE+" ='"+barcode+"'",
                 null);
        }
        public Cursor getCCItem(String ponumber,String sku) {
            return database.rawQuery(
                            "SELECT * FROM " + TABLE_NAME+" WHERE " +LOCATION+ " ='"
                            +ponumber+"' AND "+SKU+" ='"+sku+"'",
                            null);
        }  
        
        public Cursor getPOItemLocation(String PO,String PSKU,String LOC) {
            String query= "SELECT * FROM "+TABLE_NAME+" where "+ PONUMBER+"='"+PO+"' and "+ SKU+"='"+PSKU+"'and "+ LOCATION+"='"+LOC+"' LIMIT 1";
            return database.rawQuery(query,null);
            }
   
        public Cursor getItemByID(String ID) {
            String query= "SELECT * FROM "+TABLE_NAME+" where "+ CYCLECOUNT_ID+"='"+ID+"'";
            return database.rawQuery(query,null);
            }
        
        public Cursor getItemDetail(String po,String inventdim,String sku) {
            String query= "SELECT * FROM "+TABLE_NAME+" where "+ ITEMLINE+"='"+inventdim+"' AND "+ SKU+"='"+sku+"' AND "+PONUMBER+" ='"+po+"'";
            return database.rawQuery(query,null);
            }
        public Cursor getItemDetailLocation(String po,String location,String sku) {
            String query= "SELECT * FROM "+TABLE_NAME+" where "+ LOCATION+"='"+location+"' AND "+ SKU+"='"+sku+"' AND "+PONUMBER+" ='"+po+"'";
            return database.rawQuery(query,null);
            }
        
        private class ContactOpenHelper extends SQLiteOpenHelper {

                public ContactOpenHelper(Context context) {
                        super(context, DATABASE_NAME, null, DATABASE_VERSION);
                }
                
                @Override
                public void onCreate(SQLiteDatabase database) {
                        database.execSQL("CREATE TABLE " + TABLE_NAME + "("
                                        + CYCLECOUNT_ID + " INTEGER PRIMARY KEY, "
                                        + PONUMBER + " TEXT, "
                                        + LOCATION + " TEXT, "
                                        + SKU + " TEXT, "
                                        + DESCRIPTION + " TEXT, "
                                        + QTY + " TEXT, "
                                        + TYPE + " TEXT, "
                                        + AMOUNTCOUNTED + " TEXT, "
                                        + SCANNED + " TEXT, "
                                        + ITEMBARCODE + " TEXT, "
                                        + ITEMLINE + " TEXT, "
                                        + FACTOR + " TEXT, "
                                        + COUNTRY + " TEXT)");
                }
                @Override
                public void onUpgrade(SQLiteDatabase database, int oldVersion, int newVersion) {
                        database.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME + "");
                        onCreate(database);
                }

        }

}

