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 LocustListActivity extends AppCompatActivity
{
	private static final int PERMISSION_REQUEST_CODE_CAMERA = 1;

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

	private Timer timer = new Timer();
	public File file = null;
	//public int g_button=0;

	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_list);
		
		//Загрузка сохранёного языка
		Tools.loadLocale(this);


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

		spiList = (Spinner) findViewById(R.id.spiList);
		ArrayList<String> list = new ArrayList<String>();
		list.add(getString(R.string.Waiting_to_be_filled)); // 0
		list.add(getString(R.string.Not_submitted)); // 1
		list.add(getString(R.string.Submitted)); // 2
		list.add(getString(R.string.All)); // 3
		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(LocustListActivity.this, LocustActivity.class);
					startActivity(intent);
				}
			});


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

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

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

				//onGetPermissions(1);
				Intent intent = new Intent(this, LocustActivity.class);
				startActivity(intent);

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

				AlertDialog.Builder alertDialog = new AlertDialog.Builder(LocustListActivity.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(LocustListActivity.this);
						SQLiteDatabase rdb = null;
						try {
							rdb = dboh.getReadableDatabase();
							rdb.execSQL("delete from frmlocust where send=1");
						}catch(Exception e){
							e.printStackTrace();
						}finally{
							if(rdb!=null){ rdb.close(); }
						}
						dboh.close();
						LocustListActivity.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) + "</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=\"5\"><b>" + getString(R.string.Identification_of_the_place) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\" style=\"text-align: center\" colspan=\"6\"><b>" + getString(R.string.ECOLOGICAL_INFORMATION) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\" style=\"text-align: center\" colspan=\"4\"><b>" + getString(R.string.LOCUST_INFORMATION_INCLUDING_EGG_PODS) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\" style=\"text-align: center\" colspan=\"4\"><b>" + getString(R.string.Eggs) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\" style=\"text-align: center\" colspan=\"5\"><b>" + getString(R.string.Hoppers) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\" style=\"text-align: center\" colspan=\"6\"><b>" + getString(R.string.BANDS) + "</b></td>"; //Кулиги
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\" style=\"text-align: center\" colspan=\"8\"><b>" + getString(R.string.Adults) + "</b></td>"; //Имаго
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\" style=\"text-align: center\" colspan=\"8\"><b>" + getString(R.string.Swarms) + "</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.Surveyed_area_ha) + "</b></td>"; //Обследованная площадь(га)
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Type_of_biotope) + "</b></td>"; //Тип биотопа
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Vegetation) + "</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.Air_temperature) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Wind) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.LOCUST_INFORMATION_INCLUDING_EGG_PODS) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Locust_species) + "</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.Egg_bed) + "</b></td>"; //Залежь кубышек (площадь м)
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Egg_pods)+ " .. "+ getString(R.string.to) + "</b></td>"; //Кубышки(плотность на м) от/до
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Eggs_number) + "</b></td>"; //Яйца(в среднем в кубышке)
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Eggs_viable) + "</b></td>"; //Яйца(% жизнеспособных)
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Natural_enemies) + "</b></td>"; //Наличие естественных врагов(перечисление)
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Hatching) + "</b></td>"; //Отрождение
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Hopper_stages) + "</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.Spatial_distribution) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Hopper_density) + " .. " + getString(R.string.to) + "</b></td>";
				//Кулиги
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Band_stage) + "</b></td>"; //Возраст
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Band_density) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Band_density_to) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Band_sizes) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Number_of_bands) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Action) + "</b></td>";
				//Имаго
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Fledging) + "</b></td>"; //окрыление(%, начало, массовое)
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Maturity) + "</b></td>"; //половозрелость(- или+)
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Phase) + "</b></td>"; //фаза(одиночнаяS, перехoдная Т, стаднаяG)
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Spatial_distribution) + "</b></td>"; //поведение(Изолир., Разреженные, Группы)
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Adult_density) + "</b></td>"; //плотность(на трансекту или га)
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Copulating) + "</b></td>"; //Спаривание (да или нет)
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Laying) + "</b></td>"; //Яйцекладка (да да нет)
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Flying) + "</b></td>"; //полёты(- или+)
				//Стаи
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Swarm_maturity) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Density_of_swarm) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Swarm_size) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Number_of_swarms) + "</b></td>"; //Число стай (на га)
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Copulating) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Laying) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Flying_direction) + "</b></td>";
				html += "<td bgcolor=\"#d1d1d1\" width=\"100px\"><b>" + getString(R.string.Flying_height) + "</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+="	c.name country_name,\n";
				sql+="	cr.name region_name,\n";
				sql+="	area,\n";
				sql+="	district,\n";
				sql+="	observer,\n";
				sql+="	datetime(date, 'unixepoch') date,\n";
				sql+="	terrain,\n";
				sql+="	lat,\n";
				sql+="	lon,\n";
				sql+="	bio_hectare,\n";
				sql+="	COALESCE((SELECT translation FROM _translations t JOIN _languages l ON t.language_id=l.id WHERE t.del = 0 AND identifier = lb.name AND l.short_name='"+Tools.getLang()+"' LIMIT 1),lb.name) bio_biotope_name,\n";
				sql+="	lgr.name bio_greenery,\n";
				sql+="	COALESCE((SELECT translation FROM _translations t JOIN _languages l ON t.language_id=l.id WHERE t.del = 0 AND identifier = lc.name AND l.short_name='"+Tools.getLang()+"' LIMIT 1),lc.name) bio_greenery_cover_name,\n";
				sql+="	bio_temperature,\n";
				sql+="	bio_wind,\n";
				sql+="	CASE locust_have WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' ELSE '' END locust_have,\n";
				sql+="	COALESCE((SELECT translation FROM _translations t JOIN _languages l ON t.language_id=l.id WHERE t.del = 0 AND identifier = lt.name AND l.short_name='"+Tools.getLang()+"' LIMIT 1),lt.name) locust_type_name,\n";
				sql+="	locust_populated,\n";
				sql+="	eggs_capsules_area,\n";
				sql+="	eggs_capsules_density,\n";
				sql+="	eggs_capsules_density_to,\n";
				sql+="	eggs_capsules,\n";
				sql+="	eggs_live,\n";
				sql+="	eggs_enemies,\n";
				sql+="	COALESCE((SELECT translation FROM _translations t JOIN _languages l ON t.language_id=l.id WHERE t.del = 0 AND identifier = b.name AND l.short_name='"+Tools.getLang()+"' LIMIT 1),b.name) larva_born_name,\n";
				sql+="	COALESCE((SELECT translation FROM _translations t JOIN _languages l ON t.language_id=l.id WHERE t.del = 0 AND identifier = lag2.name AND l.short_name='"+Tools.getLang()+"' LIMIT 1),lag2.name) larva_age,\n";
				sql+="	COALESCE((SELECT translation FROM _translations t JOIN _languages l ON t.language_id=l.id WHERE t.del = 0 AND identifier = lp.name AND l.short_name='"+Tools.getLang()+"' LIMIT 1),lp.name) larva_painting_name,\n";
				sql+="	COALESCE((SELECT translation FROM _translations t JOIN _languages l ON t.language_id=l.id WHERE t.del = 0 AND identifier = lbh.name AND l.short_name='"+Tools.getLang()+"' LIMIT 1),lbh.name) larva_behavior_name,\n";
				sql+="	larva_density,\n";
				sql+="	larva_density_to,\n";

				sql+="	COALESCE((SELECT translation FROM _translations t JOIN _languages l ON t.language_id=l.id WHERE t.del = 0 AND identifier = lag.name AND l.short_name='"+Tools.getLang()+"' LIMIT 1),lag.name) kuliguli_age,\n";
				sql+="	kuliguli_density,\n";
				sql+="	kuliguli_density_to,\n";
				sql+="	kuliguli_size,\n";
				sql+="	kuliguli_count,\n";
				sql+="	COALESCE((SELECT translation FROM _translations t JOIN _languages l ON t.language_id=l.id WHERE t.del = 0 AND identifier = lac.name AND l.short_name='"+Tools.getLang()+"' LIMIT 1),lac.name) kuliguli_action_name,\n";

				sql+="	COALESCE((SELECT translation FROM _translations t JOIN _languages l ON t.language_id=l.id WHERE t.del = 0 AND identifier = flg.name AND l.short_name='"+Tools.getLang()+"' LIMIT 1),flg.name) imago_wing_name,\n";
				sql+="	CASE imago_maturity WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' ELSE '' END imago_maturity,\n";
				sql+="	imago_phase,\n";
				sql+="	imago_action,\n";
				sql+="	imago_density,\n";
				sql+="	CASE imago_copulation WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' ELSE '' END imago_copulation,\n";
				sql+="	CASE imago_laying WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' ELSE '' END imago_laying,\n";
				sql+="	CASE imago_flying WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' ELSE '' END imago_flying,\n";
				sql+="	CASE swarm_maturity WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' ELSE '' END swarm_maturity,\n";

				sql+="	COALESCE((SELECT translation FROM _translations t JOIN _languages l ON t.language_id=l.id WHERE t.del = 0 AND identifier = ldy.name AND l.short_name='"+Tools.getLang()+"' LIMIT 1),ldy.name) swarm_density_name,\n";
				sql+="	swarm_size,\n";
				sql+="	swarm_count,\n";
				sql+="	CASE swarm_copulation WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' ELSE '' END swarm_copulation,\n";
				sql+="	CASE swarm_laying WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' ELSE '' END swarm_laying,\n";
				sql+="	COALESCE((SELECT translation FROM _translations t JOIN _languages l ON t.language_id=l.id WHERE t.del = 0 AND identifier = lds.name AND l.short_name='"+Tools.getLang()+"' LIMIT 1),lds.name) swarm_flying_direction_name,\n";
				sql+="	ldy2.name swarm_height,\n";

				sql+="	description\n";
				sql+="FROM\n";
				sql+="	frmlocust fl\n";
				sql+="	LEFT JOIN countries c ON fl.country_id = c.id\n";
				sql+="	LEFT JOIN countriesregions cr ON fl.region_id = cr.id\n";
				sql+="	LEFT JOIN locuststypes lt ON fl.locust_type_id = lt.id\n";
				sql+="	LEFT JOIN list_density ldy ON fl.swarm_density_id = ldy.id\n";
				sql+="	LEFT JOIN list_directions lds ON fl.swarm_flying_direction_id = lds.id\n";
				sql+="	LEFT JOIN list_greenery lgr ON fl.bio_greenery_id = lgr.id\n";
				sql+="	LEFT JOIN list_density ldy2 ON ldy2.id=fl.swarm_height_id\n";
				sql+="	LEFT JOIN list_age lag ON lag.id = fl.kuliguli_age_id\n";
				sql+="	LEFT JOIN list_age lag2 ON lag.id = fl.larva_age_id\n";
				sql+="	LEFT JOIN list_biotope lb on lb.id=fl.bio_biotope_id\n";
				sql+="	LEFT JOIN list_cover lc on lc.id=fl.bio_greenery_cover_id\n";
				sql+="	LEFT JOIN Borns b on b.id=fl.larva_born_id\n";
				sql+="	LEFT JOIN list_paintings lp on lp.id=fl.larva_painting_id\n";
				sql+="	LEFT JOIN list_behaviors lbh on lbh.id=fl.larva_behavior_id\n";
				sql+="	LEFT JOIN list_actions lac on lac.id=fl.kuliguli_action_id\n";
				sql+="	LEFT JOIN Fledgling flg on flg.id=fl.imago_wing_id\n";

				sql+="where fl.del=0\n";

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

						html += "				<tr>";
						html += "<td>" + n + "&nbsp;</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, "lat1") + "&nbsp;</td>";
						html += "<td>" + Tools.getStringFromCursor(cursor, "lon1") + "&nbsp;</td>";
						html += "<td>" + Tools.getStringFromCursor(cursor, "bio_hectare") + "&nbsp;</td>"; //обследованная площадь(га)
						html += "<td>" + Tools.getStringFromCursor(cursor, "bio_biotope_name") + "&nbsp;</td>"; //Тип биотопа
						html += "<td>" + Tools.getStringFromCursor(cursor, "bio_greenery") + "&nbsp;</td>"; //растительность: сухая, всходы, зеленая, засыхающая
						html += "<td>" + Tools.getStringFromCursor(cursor, "bio_greenery_cover_name") + "&nbsp;</td>"; //растительный покров(Редкий, Ср., Густой)
						html += "<td>" + Tools.getStringFromCursor(cursor, "bio_temperature") + "&nbsp;</td>"; //погода: температура воздуха(C)
						html += "<td>" + Tools.getStringFromCursor(cursor, "bio_wind") + "&nbsp;</td>"; //погода: ветер(м/с)
						html += "<td>" + Tools.getStringFromCursor(cursor, "locust_have") + "&nbsp;</td>"; //Присутствует саранча или нет
						html += "<td>" + Tools.getStringFromCursor(cursor, "locust_type_name") + "&nbsp;</td>"; //Вид саранчи
						html += "<td>" + Tools.getStringFromCursor(cursor, "locust_populated") + "&nbsp;</td>"; //Заселённая площадь(га)
						html += "<td>" + Tools.getStringFromCursor(cursor, "eggs_capsules_area") + "&nbsp;</td>"; //залежь кубышек(площадь в м2)
						html += "<td>" + Tools.getStringFromCursor(cursor, "eggs_capsules_density")+" .. "+Tools.getStringFromCursor(cursor, "eggs_capsules_density_to") + "&nbsp;</td>"; //кубышки(плотность на м2) от
						html += "<td>" + Tools.getStringFromCursor(cursor, "eggs_capsules") + "&nbsp;</td>"; //яйца(в среднем в кубышке)
						html += "<td>" + Tools.getStringFromCursor(cursor, "eggs_live") + "&nbsp;</td>"; //яйца(% жизнеспособных)
						html += "<td>" + Tools.getStringFromCursor(cursor, "eggs_enemies") + "&nbsp;</td>"; //наличие естественных врагов(каких?)
						html += "<td>" + Tools.getStringFromCursor(cursor, "larva_born_name") + "&nbsp;</td>"; //отрождение
						html += "<td>" + Tools.getStringFromCursor(cursor, "larva_age") + "&nbsp;</td>"; //возраста: Младшие Средние Старшие
						html += "<td>" + Tools.getStringFromCursor(cursor, "larva_painting_name") + "&nbsp;</td>"; //окраска(одиночнаяS, перехoдная Т, стаднаяG)
						html += "<td>" + Tools.getStringFromCursor(cursor, "larva_behavior_name") + "&nbsp;</td>"; //поведение(Изолир., Разреженные, Группы)
						html += "<td>" + Tools.getStringFromCursor(cursor, "larva_density") + " .. " + Tools.getStringFromCursor(cursor, "larva_density_to") + "&nbsp;</td>"; //плотность на м2 от .. до

						html += "<td>" + Tools.getStringFromCursor(cursor, "kuliguli_age") + "&nbsp;</td>"; //cр. возраст(Младшие Средние Старшие)
						html += "<td>" + Tools.getStringFromCursor(cursor, "kuliguli_density") + "&nbsp;</td>"; //плотн. в кулиге(на м2)
						html += "<td>" + Tools.getStringFromCursor(cursor, "kuliguli_density_to") + "&nbsp;</td>"; //плотн. в кулиге(на м2) до
						html += "<td>" + Tools.getStringFromCursor(cursor, "kuliguli_size") + "&nbsp;</td>"; //размер кулиг(м2)
						html += "<td>" + Tools.getStringFromCursor(cursor, "kuliguli_count") + "&nbsp;</td>"; //число кулиг
						html += "<td>" + Tools.getStringFromCursor(cursor, "kuliguli_action_name") + "&nbsp;</td>"; //поведение: миграция, кормёжка, отдых

						html += "<td>" + Tools.getStringFromCursor(cursor, "imago_wing_name") + "&nbsp;</td>"; //окрыление(%, начало, массовое)
						html += "<td>" + Tools.getStringFromCursor(cursor, "imago_maturity") + "&nbsp;</td>"; //половозрелость(- или+)
						html += "<td>" + Tools.getStringFromCursor(cursor, "imago_phase") + "&nbsp;</td>"; //фаза(одиночнаяS, перехoдная Т, стаднаяG)
						html += "<td>" + Tools.getStringFromCursor(cursor, "imago_action") + "&nbsp;</td>"; //поведение(Изолир., Разреженные, Группы)
						html += "<td>" + Tools.getStringFromCursor(cursor, "imago_density") + "&nbsp;</td>"; //плотность(на трансекту или га)
						html += "<td>" + Tools.getStringFromCursor(cursor, "imago_copulation") + "&nbsp;</td>"; //Спаривание (да или нет)
						html += "<td>" + Tools.getStringFromCursor(cursor, "imago_laying") + "&nbsp;</td>"; //Яйцекладка (да да нет)
						html += "<td>" + Tools.getStringFromCursor(cursor, "imago_flying") + "&nbsp;</td>"; //полёты(- или+)
						//Стаи
						html += "<td>" + Tools.getStringFromCursor(cursor, "swarm_maturity") + "&nbsp;</td>"; //половозрелость(- или+)
						html += "<td>" + Tools.getStringFromCursor(cursor, "swarm_density_name") + "&nbsp;</td>"; //плотность(на м2)
						html += "<td>" + Tools.getStringFromCursor(cursor, "swarm_size") + "&nbsp;</td>"; //размер стаи(на км2)
						html += "<td>" + Tools.getStringFromCursor(cursor, "swarm_count") + "&nbsp;</td>"; //число стай
						html += "<td>" + Tools.getStringFromCursor(cursor, "swarm_copulation") + "&nbsp;</td>"; //Спаривание (да, нет)
						html += "<td>" + Tools.getStringFromCursor(cursor, "swarm_laying") + "&nbsp;</td>"; //Яйцекладка (да, нет)
						html += "<td>" + Tools.getStringFromCursor(cursor, "swarm_flying_direction_name") + "&nbsp;</td>"; //направление полёта
						html += "<td>" + Tools.getStringFromCursor(cursor, "swarm_height") + "&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)
					{
						//Предлагаем открыть созданый файл
						Intent i = new Intent();
						i.setAction(android.content.Intent.ACTION_VIEW);
						i.setDataAndType(Uri.fromFile(LocustListActivity.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;
	}

	//Запрашиваю разрешения сдесь хотя GPS на другой форме использую
	//WRITE_EXTERNAL_STORAGE
/*	public void onGetPermissions(int button)
	{
		g_button = button;

		boolean granted=false;
		if (Build.VERSION.SDK_INT >= Build.VERSION_CODES.M) {

			List<String> list = new ArrayList<String>();

			if (ContextCompat.checkSelfPermission(LocustListActivity.this, Manifest.permission.CAMERA) != PackageManager.PERMISSION_GRANTED) {
				list.add(Manifest.permission.CAMERA);
			}

			if(!list.isEmpty())
			{
				ActivityCompat.requestPermissions(LocustListActivity.this,list.toArray(new String[list.size()]),PERMISSION_REQUEST_CODE_CAMERA);
				granted=false;
			}else
			{
				granted=true;
			}
		}
		if(granted && g_button==1)
		{
			Intent intent = new Intent(this, LocustActivity.class);
			startActivity(intent);
		}
		if(granted && g_button==2)
		{
			//ShowLocustDelActivity();
		}
	}
	@Override
	public void onRequestPermissionsResult(int requestCode, @NonNull String[] permissions, @NonNull int[] grantResults)
	{
		if (requestCode == PERMISSION_REQUEST_CODE_CAMERA) {

			boolean grant=true;
			for(int i=0;i<grantResults.length;i++)
			{
				grant = grant && grantResults[i] == PackageManager.PERMISSION_GRANTED;
			}
			if(grant) {

				if(g_button==1)
				{
					Intent intent = new Intent(this, LocustActivity.class);
					startActivity(intent);
				}
				if(g_button==2)
				{
					//ShowLocustDelActivity();
				}
			}
		}
		super.onRequestPermissionsResult(requestCode, permissions, grantResults);
	}*/
	public void updateList()
	{

		llList.removeAllViews();

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

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

					Intent intent = new Intent(LocustListActivity.this, LocustActivity.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();
	}
}
