package kz.istt.locust;

import android.annotation.SuppressLint;
import android.app.AlertDialog;
import android.content.DialogInterface;
import android.content.Intent;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.graphics.Color;
import android.os.Bundle;
import android.os.Handler;
import android.os.Message;
import android.provider.Settings;
import android.view.Menu;
import android.view.MenuItem;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.AdapterView;
import android.widget.AdapterView.OnItemSelectedListener;
import android.widget.ArrayAdapter;
import android.widget.LinearLayout;
import android.widget.LinearLayout.LayoutParams;
import android.widget.Spinner;
import android.widget.Toast;

import androidx.appcompat.app.AppCompatActivity;

import com.google.android.material.floatingactionbutton.FloatingActionButton;

import java.io.File;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Timer;
import java.util.TimerTask;

public class LocustDelListActivity extends AppCompatActivity
{

	public LinearLayout llList;
	public MyButton		btn;
	public FloatingActionButton btnAdd = null;
	public Spinner spiList	= null;

	private Timer timer = new Timer();
	public File file = null;

	public String android_id="";

	private final Handler handler = new Handler();
	private final Runnable runnable = new Runnable() {
		@Override
		public void run() {
			updateList();
			handler.postDelayed(this, 1000); // Раз в секунду
		}
	};

	public void alert(String text)
	{
		AlertDialog.Builder adb = new AlertDialog.Builder(this,R.style.AlertDialogTheme);
		adb.setTitle(getString(R.string.Warning));
		adb.setMessage(text);
		adb.setPositiveButton("OK", new DialogInterface.OnClickListener() //Кнопка открыть
		{
			@Override
			public void onClick(DialogInterface arg0, int arg1)
			{}
		});
		AlertDialog ad = adb.show();
		ad.getButton(AlertDialog.BUTTON_POSITIVE).setTextColor(Color.BLACK);
	}

	@Override
	protected void onCreate(Bundle savedInstanceState)
	{
		super.onCreate(savedInstanceState);
		setContentView(R.layout.activity_locust_del_list);
		
		//Загрузка сохранёного языка
		Tools.loadLocale(this);
		android_id = Settings.Secure.getString(getContentResolver(), Settings.Secure.ANDROID_ID);

		
		llList = (LinearLayout) findViewById(R.id.llList);

		/*
		 * btn = (Button) findViewById(R.id.button1); OnClickListener oclBtnRus = new OnClickListener() {
		 * 
		 * @Override public void onClick(View v) { //Отобразим окно уничтожения саранчи Intent intent = new Intent(LocustDelListActivity.this, LocustDelActivity.class); //intent.putExtra("id", b.id); //intent.putExtra("date", b.date); startActivity(intent); } }; btn.setOnClickListener(oclBtnRus);
		 */

		spiList = (Spinner) findViewById(R.id.spiList); // Оператор (пилот, водитель, др...)
		ArrayList<String> list = new ArrayList<String>();
		list.add(getString(R.string.Waiting_to_be_filled));
		list.add(getString(R.string.Not_submitted));
		list.add(getString(R.string.Submitted));
		list.add(getString(R.string.All));
		ArrayAdapter<String> dataAdapter = new ArrayAdapter<String>(this, android.R.layout.simple_spinner_item, list);
		dataAdapter.setDropDownViewResource(android.R.layout.simple_spinner_dropdown_item);
		spiList.setAdapter(dataAdapter);

		spiList.setOnItemSelectedListener(new OnItemSelectedListener()
		{
			@Override
			public void onItemSelected(AdapterView<?> parentView, View selectedItemView, int position, long id)
			{
				//onStart();
				updateList();
			}
			@Override
			public void onNothingSelected(AdapterView<?> parentView)
			{
			}
		});

		btnAdd = (FloatingActionButton) findViewById(R.id.btnAdd);
		btnAdd.setOnClickListener(new View.OnClickListener() {
			public void onClick(View v) {
				Intent intent = new Intent(LocustDelListActivity.this, LocustDelActivity.class);
				startActivity(intent);
			}
		});

		//Чтоб список обновлялся раз в минуту в соответствии с текущем фильтром (для наглядности отправки данных)
		handler.post(runnable); // Начинаем выполнение задачи
	}

	@Override
	public boolean onCreateOptionsMenu(Menu menu)
	{
		getMenuInflater().inflate(R.menu.locust_del_list, menu);
		return true;
	}

	@Override
	public boolean onOptionsItemSelected(MenuItem item)
	{
		switch (item.getItemId())
		{
			case R.id.itemCreate: // Создание карточки

				Intent intent = new Intent(this, LocustDelActivity.class);
				startActivity(intent);

				break;
			case R.id.itemDelete: //Удаление всех отправленных из базы

				AlertDialog.Builder alertDialog = new AlertDialog.Builder(LocustDelListActivity.this,R.style.AlertDialogTheme);//new AlertDialog.Builder(this).create();
				alertDialog.setCancelable(false);
				alertDialog.setTitle(getString(R.string.Warning));
				alertDialog.setMessage(getString(R.string.Cleaning));
				alertDialog.setPositiveButton(getString(R.string.action_delete), new DialogInterface.OnClickListener(){
					@Override
					public void onClick(DialogInterface dialog, int arg1)
					{
						DbOpenHelper dboh = new DbOpenHelper(LocustDelListActivity.this);
						SQLiteDatabase wdb = null;
						try {
							wdb = dboh.getWritableDatabase();
							wdb.execSQL("delete from frmlocustdel where send=1");
						}catch(Exception e){
							e.printStackTrace();
						} finally {
							if(wdb!=null){ wdb.close(); }
						}
						dboh.close();
						LocustDelListActivity.this.onStart();
					}
				});
				alertDialog.setNegativeButton(getString(R.string.Cancel), new DialogInterface.OnClickListener() {
					@Override
					public void onClick(DialogInterface dialog, int arg1)
					{
						dialog.cancel();
					}
				});
				AlertDialog ad = alertDialog.show();
				ad.getButton(AlertDialog.BUTTON_NEGATIVE).setTextColor(Color.BLACK);
				ad.getButton(AlertDialog.BUTTON_POSITIVE).setTextColor(Color.BLACK);

				break;
/*			case R.id.itemExport: // Экспорт в Excel всего что накопилось

				SimpleDateFormat sdf = new SimpleDateFormat("HH:mm:ss dd.MM.yyyy");

				DbOpenHelper dboh = new DbOpenHelper(this);
				SQLiteDatabase db = dboh.getReadableDatabase();

				String user = "";
				Cursor cursor = db.rawQuery("select name,surname,patronymic from _user where del=0;", null);
				if (cursor.moveToFirst())
				{
					user = cursor.getString(cursor.getColumnIndex("surname")) + " " + cursor.getString(cursor.getColumnIndex("name")) + " "
							+ cursor.getString(cursor.getColumnIndex("patronymic"));
				}
				cursor.close();

				String html = "";

				html += "<html>\n";
				html += "	<head>\n";
				html += "		<title>Locust</title>\n";
				html += "		<meta http-equiv=\"Content-Type\" content=\"text/html; charset=UTF-8\">\n";
				html += "		<style>td {mso-number-format: \"\\@\";}</style>";
				html += "	</head>\n";
				html += "	<body>\n";

				html += "<b>" + getString(R.string.title_activity_locust_del) + ": </b><br>";
				html += "<b>" + getString(R.string.Date) + ": </b><i>" + sdf.format(new Date()) + "</i><br>";
				html += "<b>" + getString(R.string.User) + ": </b>" + user;

				html += "		<table border=\"1\" cellspacing=\"0\">\n";
				html += "			<caption><b>" + getString(R.string.Attributes) + "</b></caption>\n";
				html += "			<thead>\n";
				html += "				<tr>";

				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\" style=\"text-align: center\">&nbsp;</td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\" style=\"text-align: center\" colspan=\"4\">&nbsp;</td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\" style=\"text-align: center\" colspan=\"7\"><b>" + getString(R.string.CONTROL_LOCATION) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\" style=\"text-align: center\" colspan=\"6\"><b>" + getString(R.string.VEGETATION_DATA) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\" style=\"text-align: center\" colspan=\"10\"><b>" + getString(R.string.INSECTICIDE_DATA) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\" style=\"text-align: center\" colspan=\"12\"><b>" + getString(R.string.WEATHER_CONDITIONS) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\" style=\"text-align: center\" colspan=\"7\"><b>" + getString(R.string.LOCUST_INFORMATION_INCLUDING_EGG_PODS) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\" style=\"text-align: center\" colspan=\"17\"><b>" + getString(R.string.SPRAY_APPLICATION) + "</b></td>"; //СВЕДЕНИЯ ПО ОПРЫСКИВАНИЮ
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\" style=\"text-align: center\" colspan=\"3\"><b>" + getString(R.string.EFFECTIVENESS) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\" style=\"text-align: center\" colspan=\"5\"><b>" + getString(R.string.SAFETY_AND_ENVIRONMENT) + "</b></td>";
				//html += "<td bgcolor=\"#d1d1d1\" width=\"100px\" style=\"text-align: center\" colspan=\"1\">&nbsp;</td>";

				html += "				</tr>";
				html += "				<tr>";

				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>№</b></td>";

				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Country) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Region) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Area) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Rural_district) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Name_of_survey_team_leader) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Date) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Village) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Lat) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Lon) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Area_infested) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Area_treated) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Vegetation_type) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Height_cm) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Vegetation_crops) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Vegetation_cover) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Damage) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Veg_damage_area) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Trade_name) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.The_active_substance) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Concentration) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Formulation) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Dose_rate) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Rate_of_working_solution) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Expiry_date) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Is_insecticide_mixed_with_water_or_solvent) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.If_yes_what_solvent_and_mixing_name) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.If_yes_what_solvent_and_mixing_ratio) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Time_start) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Time_end) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Temperature_start) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Temperature_end) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Relative_humidity_start) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Relative_humidity_end) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Wind_speed_start) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Wind_speed_end) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Wind_direction_start) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Wind_direction_end) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Spray_direction_start) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Spray_direction_end) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Species) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Hoppers) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Density) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Kuliguli) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Swarm) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Del_Scattered) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Phase) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Spray_platform) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Spray_platform_a) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Spray_platform_g) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Spray_platform_h) + "</b></td>";
				//html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Spray_operator) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Name_of_operator) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Spray_manufacturer_model) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Date_of_last_calibration) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Atomizer_height_above_ground_m) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Coverage_width) + "</b></td>";
				//html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Spacing_between_the_sprayer) + "</b></td>"; //Расстояние между проходами опрыскивателя (м)
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Barriers) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Barrier_width) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Spacing_of_barriers_m) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Speed) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Antenna_DGPS_used) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Ground_marking) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Biological_efficiency_of_treatment) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Time_after_treatment_hours) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Efficacy_mortality_method) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Protective_clothing) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Inform_abaut_spraying) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Safety_non_target) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Safety_non_target_effect) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Comments) + "</b></td>";

				html += "				</tr>\n";
				html += "			</thead>\n";
				html += "			<tbody>\n";

				int n = 0;
				String sql="";

				sql+="SELECT\n";
				sql+="	fld.id,\n";
				sql+="	c.name country_name,\n";
				sql+="	cr.name region_name,\n";
				sql+="	fld.area,\n"; //Район
				sql+="	fld.district,\n"; //Сельский округ и/или хозяйство
				sql+="	fld.observer,\n"; //ФИО наблюдателя
				sql+="	date(fld.date, 'unixepoch') date,\n"; //Должна быть уникальной
				sql+="	fld.terrain,\n"; //Название местности либо урочища
				sql+="	fld.lat,\n"; //Широта
				sql+="	fld.lon,\n"; //Долгота
				sql+="	fld.infested_area,\n"; //заселенная площадь(га)
				sql+="	fld.treated_area,\n"; //обработанная площадь(га)
				sql+="	fld.vegetation_type,\n"; //Тип(дикая, культурная)
				sql+="	fld.vegetation_height,\n"; //Высота (м)
				sql+="	fld.vegetation_crop,\n"; //Текстовое поле для перечисления произрастающих культур
				sql+="	fld.vegetation_cover,\n"; //Растительный покров (%)
				sql+="	fld.vegetation_damage,\n"; //Повреждения растительного покрова(%)
				sql+="	fld.vegetation_damage_area,\n"; //Площадь повреждений (га)
				sql+="	fld.insecticide_name,\n"; //коммерческое название
				sql+="	fld.insecticide_active_substance,\n"; //Наименование активного вещества
				sql+="	fld.insecticide_concentration,\n"; //концентрация(г д.в./л или%)
				sql+="	fld.insecticide_formulation,\n"; //формуляция(УМО, КЭ, др.)
				sql+="	fld.insecticide_dose,\n"; //норма расхода(л/га)
				sql+="	fld.insecticide_rate,\n"; //расход рабочей жидкости(л/га)
				sql+="	date(fld.insecticide_expiry_date, 'unixepoch') insecticide_expiry_date,\n"; //окончание срока действия
				sql+="	CASE fld.insecticide_mixed WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' ELSE '' END insecticide_mixed,\n"; //смешивается ли инсектицид с водой или растворителем?
				sql+="	fld.insecticide_mixed_name,\n"; //если да, то с чем
				sql+="	fld.insecticide_mixed_ratio,\n"; //если да, то в каком соотношении (%)
				sql+="	fld.weather_time_start,\n"; //время начала
				sql+="	fld.weather_time_end,\n"; //время окончания
				sql+="	fld.weather_temperature_start,\n"; //Температура нач.(°C)
				sql+="	fld.weather_temperature_end,\n"; //Температура кон.(°C)
				sql+="	fld.weather_wind_speed_start,\n"; //скорость ветра нач. (м/с)
				sql+="	fld.weather_wind_speed_end,\n"; //скорость ветра кон. (м/с)
				sql+="	ld1.name weather_direction_start,\n"; //направление ветра нач.
				sql+="	ld2.name weather_direction_end,\n"; //направление ветра кон.
				sql+="	ld3.name weather_spray_direction_start,\n"; //направление опрыскивания нач.
				sql+="	ld4.name weather_spray_direction_end,\n"; //направление опрыскивания кон.
				sql+="	fld.locust_speciese,\n"; //вид: CIT, DMA, LMI, др.
				sql+="	fld.locust_hoppers,\n"; //Стадии личинок
				sql+="	fld.locust_density,\n"; //плотность на м2
				sql+="	CASE fld.locust_kuliguli WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' ELSE '' END locust_kuliguli,\n"; //Кулиги (да, нет)
				sql+="	CASE fld.locust_swarm WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' ELSE '' END locust_swarm,\n"; ////Стаи (да, нет)
				sql+="	CASE fld.locust_sparse WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' ELSE '' END locust_sparse,\n"; //Разреженные (да, нет)
				sql+="	p.name locust_phase_name,\n"; //Фаза саранчи (одиночная, переходная, стадная)
				sql+="	st.name spray_platform,\n"; //Вид опрыскивания
				sql+="	sp_a.name spray_platform_a,\n"; //1) «Авиа» - выпадающий список:«Самолет», «Вертолет», «Дельтаплан».
				sql+="	sp_g.name spray_platform_g,\n"; //2) «Наземное» - выпадающий список:«Трактор», «Машина», «Аэроз.генераторG».
				sql+="	sp_h.name spray_platform_h,\n"; //3) «Ручное» - выпадающий список:«Ранцевый», «Моторный», «Батарейный».
				//sql+="	lot.name spray_operatortype_name,\n"; //Оператор (пилот, водитель, др...)
				//sql+="	fld.spray_operator_name,\n"; //Имя оператора
				sql+="	fld.spray_manufacturer_name,\n"; //Марка опрыскивателя
				sql+="	fld.spray_model_name,\n"; //Модель опрыскивателя
				//sql+="	date(fld.spray_date_calibration, 'unixepoch') spray_date_calibration,\n"; //Дата последней калибровки
				sql+="	fld.spray_height,\n"; //Высота над поверхностью почвы (м)
				sql+="	fld.spray_width,\n"; //Ширина захвата (м)
				//sql+="	fld.spray_spacing,\n"; //Расстояние между проходами опрыскивателя (м)
				sql+="	CASE fld.spray_barrier WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' ELSE '' END spray_barrier,\n"; //Барьеры (да, нет)
				sql+="	fld.spray_barrier_width,\n"; //Ширина (м)
				sql+="	fld.spray_barrier_space,\n"; //промежуток (м)
				sql+="	fld.spray_speed,\n"; //Скорость движения (км/ч)
				sql+="	CASE fld.spray_gps WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' ELSE '' END spray_gps,\n"; //Антена: DGPS использовалась
				sql+="	fld.spray_marking,\n"; //Наземная маркировка(Сиг-нальщики, GPS, Машина, Нет)
				sql+="	fld.efficacy_mortality,\n"; //смертность саранчи(%)
				sql+="	fld.efficacy_passed_time,\n"; //Прошло времени после обработки
				sql+="	lm.name efficacy_mortality_method,\n"; //метод подсчета смертности
				sql+="	CASE fld.safety_clothing WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' ELSE '' END safety_clothing,\n"; //Какой защитной одеждой пользовался оператор (да, нет)
				sql+="	fld.safety_inform,\n"; //Кто был оповещен об обработках?
				sql+="	CASE fld.safety_non_target WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' ELSE '' END safety_non_target,\n"; //воздействие на нецелевые организмы (да, нет)
				sql+="	fld.safety_non_target_effect,\n"; //если да, то какое
				sql+="	fld.description\n"; //КОММЕНТАРИИ
				sql+="FROM\n";
				sql+="	frmlocustdel fld\n";
				sql+="	LEFT JOIN countries c ON fld.country_id = c.id\n";
				sql+="	LEFT JOIN countriesregions cr ON fld.region_id = cr.id\n";
				sql+="	LEFT JOIN list_phase p ON fld.locust_phase_id = p.id\n";
				//sql+="	LEFT JOIN list_operatorstypes lot ON lot.id = fld.spray_operatortype_id\n";
				sql+="	LEFT JOIN list_directions ld1 ON ld1.id = fld.weather_direction_start\n";
				sql+="	LEFT JOIN list_directions ld2 ON ld2.id = fld.weather_direction_end\n";
				sql+="	LEFT JOIN list_directions ld3 ON ld3.id = fld.weather_spray_direction_start\n";
				sql+="	LEFT JOIN list_directions ld4 ON ld4.id = fld.weather_spray_direction_end\n";
				sql+="	LEFT JOIN sprayers_types st ON st.id = fld.spray_platform\n";
				sql+="	LEFT JOIN Sprayers sp_a ON sp_a.id = fld.spray_platform_a\n";
				sql+="	LEFT JOIN Sprayers sp_g ON sp_g.id = fld.spray_platform_g\n";
				sql+="	LEFT JOIN Sprayers sp_h ON sp_h.id = fld.spray_platform_h\n";
				sql+="	LEFT JOIN list_mortality lm ON lm.id = fld.efficacy_mortality_method\n";
				sql+="where fld.del=0\n";

				cursor = dboh.getReadableDatabase().rawQuery(sql, null);
				if (cursor.moveToFirst())
				{
					do
					{
						n++;

						html += "				<tr>";
						html += "<td>" + n + "</td>";

						html += "<td>" + Tools.getStringFromCursor(cursor, "country_name") + "&nbsp;</td>";
						html += "<td>" + Tools.getStringFromCursor(cursor, "region_name") + "&nbsp;</td>";
						html += "<td>" + Tools.getStringFromCursor(cursor, "area") + "&nbsp;</td>";
						html += "<td>" + Tools.getStringFromCursor(cursor, "district") + "&nbsp;</td>";
						html += "<td>" + Tools.getStringFromCursor(cursor, "observer") + "&nbsp;</td>";
						html += "<td>" + Tools.getStringFromCursor(cursor, "date") + "&nbsp;</td>";
						html += "<td>" + Tools.getStringFromCursor(cursor, "terrain") + "&nbsp;</td>";
						html += "<td>" + Tools.getStringFromCursor(cursor, "lat") + "&nbsp;</td>";
						html += "<td>" + Tools.getStringFromCursor(cursor, "lon") + "&nbsp;</td>";
						html += "<td>" + Tools.getStringFromCursor(cursor, "infested_area") + "&nbsp;</td>";
						html += "<td>" + Tools.getStringFromCursor(cursor, "treated_area") + "&nbsp;</td>";
						html += "<td>" + Tools.getStringFromCursor(cursor, "vegetation_type") + "&nbsp;</td>";
						html += "<td>" + Tools.getStringFromCursor(cursor, "vegetation_height") + "&nbsp;</td>";
						html += "<td>" + Tools.getStringFromCursor(cursor, "vegetation_crop") + "&nbsp;</td>";
						html += "<td>" + Tools.getStringFromCursor(cursor, "vegetation_cover") + "&nbsp;</td>";
						html += "<td>" + Tools.getStringFromCursor(cursor, "vegetation_damage") + "&nbsp;</td>";
						html += "<td>" + Tools.getStringFromCursor(cursor, "vegetation_damage_area") + "&nbsp;</td>";
						html += "<td>" + Tools.getStringFromCursor(cursor, "insecticide_name") + "&nbsp;</td>";
						html += "<td>" + Tools.getStringFromCursor(cursor, "insecticide_active_substance") + "&nbsp;</td>";
						html += "<td>" + Tools.getStringFromCursor(cursor, "insecticide_concentration") + "&nbsp;</td>";
						html += "<td>" + Tools.getStringFromCursor(cursor, "insecticide_formulation") + "&nbsp;</td>";
						html += "<td>" + Tools.getStringFromCursor(cursor, "insecticide_dose") + "&nbsp;</td>";
						html += "<td>" + Tools.getStringFromCursor(cursor, "insecticide_rate") + "&nbsp;</td>";
						html += "<td>" + Tools.getStringFromCursor(cursor, "insecticide_expiry_date") + "&nbsp;</td>";
						html += "<td>" + Tools.getStringFromCursor(cursor, "insecticide_mixed") + "&nbsp;</td>";
						html += "<td>" + Tools.getStringFromCursor(cursor, "insecticide_mixed_name") + "&nbsp;</td>";
						html += "<td>" + Tools.getStringFromCursor(cursor, "insecticide_mixed_ratio") + "&nbsp;</td>";

						html += "<td>" + Tools.getStringFromCursor(cursor, "weather_time_start")  + "&nbsp;</td>";
						html += "<td>" + Tools.getStringFromCursor(cursor, "weather_time_end") + "&nbsp;</td>";
						html += "<td>" + Tools.getStringFromCursor(cursor, "weather_temperature_start") + "&nbsp;</td>";
						html += "<td>" + Tools.getStringFromCursor(cursor, "weather_temperature_end") + "&nbsp;</td>";
						html += "<td>" + Tools.getStringFromCursor(cursor, "weather_humidity_start") + "&nbsp;</td>";
						html += "<td>" + Tools.getStringFromCursor(cursor, "weather_humidity_end") + "&nbsp;</td>";
						html += "<td>" + Tools.getStringFromCursor(cursor, "weather_wind_speed_start") + "&nbsp;</td>";
						html += "<td>" + Tools.getStringFromCursor(cursor, "weather_wind_speed_end") + "&nbsp;</td>";
						html += "<td>" + Tools.getStringFromCursor(cursor, "weather_direction_start") + "&nbsp;</td>";
						html += "<td>" + Tools.getStringFromCursor(cursor, "weather_direction_end") + "&nbsp;</td>";
						html += "<td>" + Tools.getStringFromCursor(cursor, "weather_spray_direction_start") + "&nbsp;</td>";
						html += "<td>" + Tools.getStringFromCursor(cursor, "weather_spray_direction_end") + "&nbsp;</td>";

						html += "<td>" + Tools.getStringFromCursor(cursor, "locust_speciese") + "&nbsp;</td>";
						html += "<td>" + Tools.getStringFromCursor(cursor, "locust_hoppers") + "&nbsp;</td>";
						html += "<td>" + Tools.getStringFromCursor(cursor, "locust_density") + "&nbsp;</td>";
						html += "<td>" + Tools.getStringFromCursor(cursor, "locust_kuliguli") + "&nbsp;</td>";
						html += "<td>" + Tools.getStringFromCursor(cursor, "locust_swarm") + "&nbsp;</td>";
						html += "<td>" + Tools.getStringFromCursor(cursor, "locust_sparse") + "&nbsp;</td>";
						html += "<td>" + Tools.getStringFromCursor(cursor, "locust_phase_name") + "&nbsp;</td>";
						html += "<td>" + Tools.getStringFromCursor(cursor, "spray_platform") + "&nbsp;</td>";
						html += "<td>" + Tools.getStringFromCursor(cursor, "spray_platform_a") + "&nbsp;</td>";
						html += "<td>" + Tools.getStringFromCursor(cursor, "spray_platform_g") + "&nbsp;</td>";
						html += "<td>" + Tools.getStringFromCursor(cursor, "spray_platform_h") + "&nbsp;</td>";
						//html += "<td>" + Tools.getStringFromCursor(cursor, "spray_operatortype_name") + "&nbsp;</td>";
						//html += "<td>" + Tools.getStringFromCursor(cursor, "spray_operator_name") + "&nbsp;</td>";
						html += "<td>" + Tools.getStringFromCursor(cursor, "spray_manufacturer_name") + "&nbsp;</td>";
						html += "<td>" + Tools.getStringFromCursor(cursor, "spray_model_name") + "&nbsp;</td>";
						//html += "<td>" + Tools.getStringFromCursor(cursor, "spray_date_calibration") + "&nbsp;</td>";
						html += "<td>" + Tools.getStringFromCursor(cursor, "spray_height") + "&nbsp;</td>";
						html += "<td>" + Tools.getStringFromCursor(cursor, "spray_width") + "&nbsp;</td>";
						//html += "<td>" + Tools.getStringFromCursor(cursor, "spray_spacing") + "&nbsp;</td>";
						html += "<td>" + Tools.getStringFromCursor(cursor, "spray_barrier") + "&nbsp;</td>";
						html += "<td>" + Tools.getStringFromCursor(cursor, "spray_barrier_width") + "&nbsp;</td>";
						html += "<td>" + Tools.getStringFromCursor(cursor, "spray_barrier_space") + "&nbsp;</td>";
						html += "<td>" + Tools.getStringFromCursor(cursor, "spray_speed") + "&nbsp;</td>";
						html += "<td>" + Tools.getStringFromCursor(cursor, "spray_gps") + "&nbsp;</td>";
						html += "<td>" + Tools.getStringFromCursor(cursor, "spray_marking") + "&nbsp;</td>";
						html += "<td>" + Tools.getStringFromCursor(cursor, "efficacy_mortality") + "&nbsp;</td>";
						html += "<td>" + Tools.getStringFromCursor(cursor, "efficacy_passed_time") + "&nbsp;</td>";
						html += "<td>" + Tools.getStringFromCursor(cursor, "efficacy_mortality_method") + "&nbsp;</td>";
						html += "<td>" + Tools.getStringFromCursor(cursor, "safety_clothing") + "&nbsp;</td>";
						//html += "<td>" + Tools.getStringFromCursor(cursor, "safety_inform") + "&nbsp;</td>";

						html += "<td>";
						String dv="";
						if(Tools.getStringFromCursor(cursor, "safety_inform").indexOf("1") != -1)
						{
							html += dv + getString(R.string.Farmer);
							dv = ", ";
						}
						if(Tools.getStringFromCursor(cursor, "safety_inform").indexOf("2") != -1)
						{
							html += dv + getString(R.string.Villager);
							dv = ", ";
						}
						if(Tools.getStringFromCursor(cursor, "safety_inform").indexOf("3") != -1)
						{
							html += dv + getString(R.string.Official);
							dv = ", ";
						}
						if(Tools.getStringFromCursor(cursor, "safety_inform").indexOf("4") != -1)
						{
							html += dv + getString(R.string.Beekeeper);
							dv = ", ";
						}
						if(Tools.getStringFromCursor(cursor, "safety_inform").indexOf("5") != -1)
						{
							html += dv + getString(R.string.Other);
							dv = ", ";
						}
						html += "&nbsp;</td>";

						html += "<td>" + Tools.getStringFromCursor(cursor, "safety_non_target") + "&nbsp;</td>";
						html += "<td>" + Tools.getStringFromCursor(cursor, "safety_non_target_effect") + "&nbsp;</td>";
						html += "<td>" + Tools.getStringFromCursor(cursor, "description") + "&nbsp;</td>";

						html += "</tr>\n";

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

				html += "			</tbody>\n";
				html += "		</table>\n";
				html += "	</body>\n";
				html += "</html>\n";

				// Сохраняем на карточку
				sdf = new SimpleDateFormat("yyyyMMdd_HHmmss");
				file = new File(Environment.getExternalStorageDirectory() + File.separator + "Data" + File.separator + "Locust" + File.separator
						+ "locust_" + sdf.format(new Date()) + ".html");
				file.getParentFile().mkdirs();
				// создади переменную для записи создания и наполнения файла
				FileWriter writer = null;
				try
				{
					writer = new FileWriter(file);
				} catch (IOException e)
				{
					Log.e("Error", "Не создался writer", e);
				}

				// запишем в файл пару строк
				try
				{
					writer.write(html);
					writer.flush();
					writer.close();
				} catch (IOException e)
				{
					Log.e("Error", "Не записываются строки", e);
				}

				//Предлагаем открыть созданый файл
				AlertDialog.Builder adb;
				adb = new AlertDialog.Builder(this);
				adb.setTitle(getString(R.string.Warning));  // заголовок
				adb.setMessage(getString(R.string.File_saved_in)+" "+file.getAbsolutePath()); // сообщение
				adb.setPositiveButton(getString(R.string.Open), new DialogInterface.OnClickListener() //Кнопка открыть
				{
					@Override
					public void onClick(DialogInterface arg0, int arg1)
					{
						//Предлагаем открыть созданый файл
						if(LocustDelListActivity.this.file.exists())
						{
							Intent i = new Intent();
							i.setAction(android.content.Intent.ACTION_VIEW);
							i.setDataAndType(Uri.fromFile(LocustDelListActivity.this.file), "text/html");
							startActivity(i);
						}
					}
				});
				adb.setNegativeButton(getString(R.string.Cancel), new DialogInterface.OnClickListener() //Кнопка отмена
				{
					@Override
					public void onClick(DialogInterface arg0, int arg1)
					{

					}
				});
				AlertDialog ad = adb.show();
				ad.getButton(AlertDialog.BUTTON_NEGATIVE).setTextColor(Color.BLACK);
				ad.getButton(AlertDialog.BUTTON_POSITIVE).setTextColor(Color.BLACK);

				break;
*/
			default:
				return false;
		}
		return true;
	}

	public void updateList()
	{
		llList.removeAllViews();

		DbOpenHelper dboh = new DbOpenHelper(LocustDelListActivity.this);
		SQLiteDatabase rdb = null;
		Cursor cursor =null;
		try {
			rdb = dboh.getReadableDatabase();
			switch (spiList.getSelectedItemPosition())
			{
				case 0:
					cursor = rdb.rawQuery("select uid, coalesce(area,'') || ' ' || coalesce(district,'') || ' ' || coalesce(terrain,'') terrain, date from frmlocustdel where del=0 and filled!=1 and device_id='" + android_id + "'", null); //Не отправленные
				break;
				case 1:
					cursor = rdb.rawQuery("select uid, coalesce(area,'') || ' ' || coalesce(district,'') || ' ' || coalesce(terrain,'') terrain, date from frmlocustdel where del=0 and send=0 and filled=1 and device_id='"+android_id+"'", null); //Не отправленные
					break;
				case 2:
					cursor = rdb.rawQuery("select uid, coalesce(area,'') || ' ' || coalesce(district,'') || ' ' || coalesce(terrain,'') terrain, date from frmlocustdel where del=0 and send=1 and device_id='"+android_id+"'", null); //Отправленные
					break;
				default:
					cursor = rdb.rawQuery("select uid, coalesce(area,'') || ' ' || coalesce(district,'') || ' ' || coalesce(terrain,'') terrain, date from frmlocustdel where del=0 and device_id='"+android_id+"'", null); //Все
					//cursor = rdb.rawQuery("select uid, coalesce(district,'') || ' ' || coalesce(terrain,'') terrain, date from frmlocustdel where del=0", null); //Все
					break;
			}

			// Tools.logCursor(cursor);
			OnClickListener oclBtn = new OnClickListener()
			{
				@Override
				public void onClick(View v)
				{
					MyButton b = (MyButton) v;

					Intent intent = new Intent(LocustDelListActivity.this, LocustDelActivity.class);
					intent.putExtra("uid", b.uid1);
					startActivity(intent);
				}
			};

			if (cursor.moveToFirst())
			{
				do
				{
					btn = new MyButton(this);

					btn.setOnClickListener(oclBtn);

					btn.uid1 = cursor.getString(cursor.getColumnIndex("uid"));

					// Преобразует с учётом временой зоны (пример long dv = 946684800L * 1000L; //2000 ный по гринвичу дало 6 утра)
					String vv = "";
					String val = cursor.getString(cursor.getColumnIndex("date"));
					try {
						long dv = Long.parseLong(val) * (long) 1000;// its need to be in milisecond
						Date df = new java.util.Date(dv);
						vv = new SimpleDateFormat("dd.MM.yyyy").format(df);
					} catch (NumberFormatException e) {
					}
					if(vv.isEmpty()) {
						try {
							SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
							Date df = format.parse(val);
							vv = new SimpleDateFormat("dd.MM.yyyy").format(df);
						} catch (ParseException e) {
						}
					}

					btn.setText(cursor.getString(cursor.getColumnIndex("terrain")) + " (" + vv + ")");

					// Устанавливаю высоту в пикселях через высоту макета
					int heightInDp = 40;
					float scale = getResources().getDisplayMetrics().density;
					int heightInPx = (int) (heightInDp * scale + 0.5f); //Перевожу в dp
					LinearLayout.LayoutParams params = new LinearLayout.LayoutParams(
							LayoutParams.MATCH_PARENT,
							heightInPx
					);
					params.bottomMargin = 4;

					llList.addView(btn,params);

				} while (cursor.moveToNext());
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if(cursor!=null){ cursor.close(); }
			if(rdb!=null){ rdb.close(); }
		}
		dboh.close();
	}

	/**
	 * СМ. http://developer.android.com/reference/android/app/Activity.html Событие происходит при старте и при возврате на этот Activity
	 * */
	@Override
	public void onStart()
	{
		super.onStart();

		DbOpenHelper dboh = new DbOpenHelper(this);
		SQLiteDatabase rdb = null;
		Cursor cursor = null;
		try {
			rdb = dboh.getReadableDatabase();
			cursor = rdb.rawQuery("select * from terminals where del=0 and serial='"+ Settings.Secure.getString(getContentResolver(), Settings.Secure.ANDROID_ID)+"';", null);
			if(!cursor.moveToFirst())
			{
				Toast toast = Toast.makeText(getApplicationContext(),
						getResources().getString(R.string.Please_authorize_the_tablet), Toast.LENGTH_LONG);
				toast.show();
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if(cursor!=null){ cursor.close(); }
			if(rdb!=null){ rdb.close(); }
		}
		dboh.close();
	}

}
