using SQLite;
using System;
using System.Collections.Generic;
using System.Text;

namespace TBSMobileApplication.Data
{
    public interface ISQLiteDB
    {
        SQLiteAsyncConnection GetConnection();
    }
}

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;

using Android.App;
using Android.Content;
using Android.OS;
using Android.Runtime;
using Android.Views;
using Android.Widget;
using SQLite;
using TBSMobileApplication.Data;
using TBSMobileApplication.Droid.Data;
using Xamarin.Forms;

[assembly: Dependency(typeof(AndroidSQLiteDb))]

namespace TBSMobileApplication.Droid.Data
{
    public class AndroidSQLiteDb : ISQLiteDB
    {
        public SQLiteAsyncConnection GetConnection()
        {
            var dbFileName = "backend.db3";
            var documentsPath = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal);
            var path = Path.Combine(documentsPath, dbFileName);

            return new SQLiteAsyncConnection(path);
        }
    }
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using TBSMobileApplication.Data;
using TBSMobileApplication.ViewModel;
using Xamarin.Forms;
using Xamarin.Forms.Xaml;

namespace TBSMobileApplication.View
{
    [XamlCompilation(XamlCompilationOptions.Compile)]
    public partial class LoginPage : ContentPage
    {
        public LoginPageViewModel loginModel;
        public LoginPage ()
        {
            InitializeComponent();
            OnAppearingAsync();
            BindingContext = new LoginPageViewModel();
            MessagingCenter.Subscribe<LoginPageViewModel,string>(this, "Login Alert", (sender,Username) =>
            {
                DisplayAlert("Login Error", "Please fill-up the form", "Ok");
            });

            MessagingCenter.Subscribe<LoginPageViewModel, string>(this, "Connected", (sender, Username) =>
            {
                DisplayAlert("Connection Info", "Connected", "Ok");
            });

            MessagingCenter.Subscribe<LoginPageViewModel, string>(this, "Not Connected", (sender, Username) =>
            {
                DisplayAlert("Connection Info", "Not Connected", "Ok");
            });

            MessagingCenter.Subscribe<LoginPageViewModel, string>(this, "Http", (sender, Username) =>
            {
                DisplayAlert("Login Error", "Username or Password is incorrect", "Ok");
            });
        }

        protected async Task OnAppearingAsync()
        {
            var db = DependencyService.Get<ISQLiteDB>();
            var conn = db.GetConnection();

            if (conn != null)
            {
                await conn.CreateTableAsync<UserTable>();
                await conn.CreateTableAsync<ContactsTable>();
                await conn.CreateTableAsync<ActivityTable>();
                await conn.CreateTableAsync<CAFTable>();
            }

            base.OnAppearing();
        }
    }
}

using SQLite;
using System;
using System.Collections.Generic;
using System.Text;

namespace TBSMobileApplication.Data
{
    [Table("tblCaf")]
    public class CAFTable
    {
        [PrimaryKey, MaxLength(100)]
        public string CAFNo { get; set; }
        [MaxLength(100)]
        public string TempCAFNo { get; set; }
        [MaxLength(100)]
        public string EmployeeID { get; set; }
        public DateTime CAFDate { get; set; }
        [MaxLength(100)]
        public string CustomerID { get; set; }
        public DateTime StartTime { get; set; }
        public DateTime EndTime { get; set; }
        [MaxLength(20)]
        public string ActivityID { get; set; }
        public string Photo1 { get; set; }
        public string Photo2 { get; set; }
        public string Photo3 { get; set; }
        [MaxLength(1000)]
        public string Remarks { get; set; }
        [MaxLength(1000)]
        public string OtherConcern { get; set; }
    }
}

using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Net;
using System.Text;
using System.Threading.Tasks;
using TBSMobileApplication.Data;
using TBSMobileApplication.ViewModel;
using Xamarin.Forms;
using Xamarin.Forms.Xaml;

namespace TBSMobileApplication.View
{
    [XamlCompilation(XamlCompilationOptions.Compile)]
    public partial class DatabaseSyncPage : ContentPage
    {
        public DatabaseSyncPage (string contanctID, string host, string database)
        {
            InitializeComponent ();

            InsertUserData(contanctID, host, database);
            InsertCAFData(contanctID, host, database);
        }

        public class UserData
        {
            public string ContactID { get; set; }
            public string UserID { get; set; }
            public string UserPassword { get; set; }
            public string UserType { get; set; }
        }

        //IMPORT USER DATA TO LOCAL DATABASE
        public void InsertUserData(string contact, string host, string database)
        {
            var link = "http://192.168.120.9:7777/TBS/mobile-get-data.php?Host=" + host + "&Database=" + database + "&Contact=" + contact + "&Sync=1";
            var request = HttpWebRequest.Create(string.Format(@link));
            request.ContentType = "application/json";
            request.Method = "GET";

            using (HttpWebResponse response = request.GetResponse() as HttpWebResponse)
            {
                if (response.StatusCode != HttpStatusCode.OK)
                {
                    Console.Out.WriteLine("Error fetching data. Server returned status code: {0}", response.StatusCode);
                }
                else
                {
                    using (StreamReader reader = new StreamReader(response.GetResponseStream()))
                    {
                        var content = reader.ReadToEnd();

                        if (content.Equals("[]") || string.IsNullOrWhiteSpace(content) || string.IsNullOrEmpty(content))
                        {
                            DisplayAlert("Sync Message", "No data found", "Ok");
                        }
                        else
                        {
                            var db = DependencyService.Get<ISQLiteDB>();
                            var conn = db.GetConnection();

                            var result = JsonConvert.DeserializeObject<List<UserData>>(content);
                            for (int i = 0; i < result.Count; i++)
                            {
                                var item = result[i];
                                var contactID = item.ContactID;
                                var userID = item.UserID;
                                var userPassword = item.UserPassword;
                                var userType = item.UserType;

                                var user = new UserTable { ContactID=contactID, UserID=userID, UserPassword=userPassword, UserType=userType};

                                conn.InsertAsync(user);
                            }
                        }

                    }
                }
            }
        }

        public class CAFData
        {
            public string CAFNo { get; set; }
            public string EmployeeID { get; set; }
            public DateTime CAFDate { get; set; }
            public string CustomerID { get; set; }
            public DateTime StartTime { get; set; }
            public DateTime EndTime { get; set; }
            public string ActivityID { get; set; }
            public string Photo1 { get; set; }
            public string Photo2 { get; set; }
            public string Photo3 { get; set; }
            public string Remarks { get; set; }
            public string OtherConcern { get; set; }
        }

        //IMPORT CAF TO LOCAL DATABASE
        public void InsertCAFData(string contact, string host, string database)
        {
            var link = "http://192.168.120.9:7777/TBS/mobile-get-data.php?Host=" + host + "&Database=" + database + "&Contact=" + contact + "&Sync=2";
            var request = HttpWebRequest.Create(string.Format(@link));
            request.ContentType = "application/json";
            request.Method = "GET";

            using (HttpWebResponse response = request.GetResponse() as HttpWebResponse)
            {
                if (response.StatusCode != HttpStatusCode.OK)
                {
                    Console.Out.WriteLine("Error fetching data. Server returned status code: {0}", response.StatusCode);
                }
                else
                {
                    using (StreamReader reader = new StreamReader(response.GetResponseStream()))
                    {
                        var content = reader.ReadToEnd();

                        if (content.Equals("[]") || string.IsNullOrWhiteSpace(content) || string.IsNullOrEmpty(content))
                        {
                            DisplayAlert("Sync Message", "No data found", "Ok");
                        }
                        else
                        {
                            var db = DependencyService.Get<ISQLiteDB>();
                            var conn = db.GetConnection();

                            var result = JsonConvert.DeserializeObject<List<CAFData>>(content);
                            for (int i = 0; i < result.Count; i++)
                            {
                                var item = result[i];
                                var cafNo = item.CAFNo;
                                var empID = item.EmployeeID;
                                var cafDate = item.CAFDate;
                                var customerID = item.CustomerID;
                                var startTime = item.StartTime;
                                var endTime = item.EndTime;
                                var activityID = item.ActivityID;
                                var photo1 = item.Photo1;
                                var photo2 = item.Photo2;
                                var photo3 = item.Photo3;
                                var remarks = item.Remarks;
                                var others = item.OtherConcern;

                                var caf = new CAFTable { CAFNo=cafNo, EmployeeID=empID, CAFDate=cafDate, CustomerID=customerID, StartTime=startTime, EndTime=endTime, ActivityID=activityID, Photo1=photo1, Photo2=photo2, Photo3=photo3, Remarks=remarks, OtherConcern=others };

                                conn.InsertAsync(caf);
                            }
                        }

                    }
                }
            }
        }
    }
}