public ConstructionSiteDocumentListResponse Sync(SyncConstructionSiteDocumentRequest request)
        {
            ConstructionSiteDocumentListResponse response = new ConstructionSiteDocumentListResponse();

            try
            {
                response.ConstructionSiteDocuments = new List <ConstructionSiteDocumentViewModel>();

                if (request.LastUpdatedAt != null)
                {
                    response.ConstructionSiteDocuments.AddRange(unitOfWork.GetConstructionSiteDocumentRepository()
                                                                .GetConstructionSiteDocumentsNewerThen(request.CompanyId, (DateTime)request.LastUpdatedAt)
                                                                ?.ConvertToConstructionSiteDocumentViewModelList() ?? new List <ConstructionSiteDocumentViewModel>());
                }
                else
                {
                    response.ConstructionSiteDocuments.AddRange(unitOfWork.GetConstructionSiteDocumentRepository()
                                                                .GetConstructionSiteDocuments(request.CompanyId)
                                                                ?.ConvertToConstructionSiteDocumentViewModelList() ?? new List <ConstructionSiteDocumentViewModel>());
                }

                response.Success = true;
            }
            catch (Exception ex)
            {
                response.ConstructionSiteDocuments = new List <ConstructionSiteDocumentViewModel>();
                response.Success = false;
                response.Message = ex.Message;
            }

            return(response);
        }
        public ConstructionSiteDocumentListResponse GetFilteredConstructionSiteDocuments(int companyId, ConstructionSiteDocumentViewModel filterObject)
        {
            ConstructionSiteDocumentListResponse     response = new ConstructionSiteDocumentListResponse();
            List <ConstructionSiteDocumentViewModel> ConstructionSiteDocuments = new List <ConstructionSiteDocumentViewModel>();

            using (SqliteConnection db = new SqliteConnection("Filename=SirmiumERPGFC.db"))
            {
                db.Open();
                try
                {
                    SqliteCommand selectCommand = new SqliteCommand(
                        SqlCommandSelectPart +
                        "FROM ConstructionSiteDocuments " +
                        "WHERE (" +
                        "   (@ConstructionSiteName IS NULL OR @ConstructionSiteName = '' OR ConstructionSiteName LIKE @ConstructionSiteName) OR " +
                        "   (@ConstructionSiteName IS NULL OR @ConstructionSiteName = '' OR Name LIKE @ConstructionSiteName) " +
                        ") " +
                        "AND (@ConstructionSiteInternalCode IS NULL OR @ConstructionSiteInternalCode = '' OR ConstructionSiteInternalCode LIKE @ConstructionSiteInternalCode) " +
                        "AND (@DateFrom IS NULL OR @DateFrom = '' OR DATE(CreateDate) >= DATE(@DateFrom)) " +
                        "AND (@DateTo IS NULL OR @DateTo = '' OR DATE(CreateDate) <= DATE(@DateTo)) " +
                        "ORDER BY IsSynced, Id DESC;", db);

                    selectCommand.Parameters.AddWithValue("@ConstructionSiteName", (String.IsNullOrEmpty(filterObject.Search_Name) ? "" : "%" + filterObject.Search_Name + "%"));
                    selectCommand.Parameters.AddWithValue("@ConstructionSiteInternalCode", (String.IsNullOrEmpty(filterObject.Search_Code) ? "" : "%" + filterObject.Search_Code + "%"));
                    selectCommand.Parameters.AddWithValue("@DateFrom", ((object)filterObject.Search_DateFrom) ?? DBNull.Value);
                    selectCommand.Parameters.AddWithValue("@DateTo", ((object)filterObject.Search_DateTo) ?? DBNull.Value);
                    selectCommand.Parameters.AddWithValue("@CompanyId", companyId);

                    SqliteDataReader query = selectCommand.ExecuteReader();

                    while (query.Read())
                    {
                        ConstructionSiteDocumentViewModel dbEntry = Read(query);
                        ConstructionSiteDocuments.Add(dbEntry);
                    }
                }
                catch (SqliteException error)
                {
                    MainWindow.ErrorMessage            = error.Message;
                    response.Success                   = false;
                    response.Message                   = error.Message;
                    response.ConstructionSiteDocuments = new List <ConstructionSiteDocumentViewModel>();
                    return(response);
                }
                db.Close();
            }
            response.Success = true;
            response.ConstructionSiteDocuments = ConstructionSiteDocuments;
            return(response);
        }
        public ConstructionSiteDocumentListResponse Sync(SyncConstructionSiteDocumentRequest request)
        {
            ConstructionSiteDocumentListResponse response = new ConstructionSiteDocumentListResponse();

            try
            {
                response = WpfApiHandler.SendToApi <SyncConstructionSiteDocumentRequest, ConstructionSiteDocumentViewModel, ConstructionSiteDocumentListResponse>(request, "Sync");
            }
            catch (Exception ex)
            {
                response.ConstructionSiteDocuments = new List <ConstructionSiteDocumentViewModel>();
                response.Success = false;
                response.Message = ex.Message;
            }

            return(response);
        }
        public JsonResult Sync([FromBody] SyncConstructionSiteDocumentRequest request)
        {
            ConstructionSiteDocumentListResponse response = new ConstructionSiteDocumentListResponse();

            try
            {
                response = this.ConstructionSiteDocumentService.Sync(request);
            }
            catch (Exception ex)
            {
                response.Success = false;
                response.Message = ex.Message;
            }

            return(Json(response, new Newtonsoft.Json.JsonSerializerSettings()
            {
                Formatting = Newtonsoft.Json.Formatting.Indented
            }));
        }
        public ConstructionSiteDocumentListResponse GetConstructionSiteDocumentsByConstructionSite(int companyId, Guid ConstructionSiteIdentifier)
        {
            ConstructionSiteDocumentListResponse     response = new ConstructionSiteDocumentListResponse();
            List <ConstructionSiteDocumentViewModel> ConstructionSiteDocuments = new List <ConstructionSiteDocumentViewModel>();

            using (SqliteConnection db = new SqliteConnection("Filename=SirmiumERPGFC.db"))
            {
                db.Open();
                try
                {
                    SqliteCommand selectCommand = new SqliteCommand(
                        SqlCommandSelectPart +
                        "FROM ConstructionSiteDocuments " +
                        "WHERE ConstructionSiteIdentifier = @ConstructionSiteIdentifier " +
                        "AND CompanyId = @CompanyId " +
                        "ORDER BY IsSynced, Id DESC;", db);

                    selectCommand.Parameters.AddWithValue("@ConstructionSiteIdentifier", ConstructionSiteIdentifier);
                    selectCommand.Parameters.AddWithValue("@CompanyId", companyId);

                    SqliteDataReader query = selectCommand.ExecuteReader();

                    while (query.Read())
                    {
                        ConstructionSiteDocumentViewModel dbEntry = Read(query);
                        ConstructionSiteDocuments.Add(dbEntry);
                    }
                }
                catch (SqliteException error)
                {
                    MainWindow.ErrorMessage            = error.Message;
                    response.Success                   = false;
                    response.Message                   = error.Message;
                    response.ConstructionSiteDocuments = new List <ConstructionSiteDocumentViewModel>();
                    return(response);
                }
                db.Close();
            }
            response.Success = true;
            response.ConstructionSiteDocuments = ConstructionSiteDocuments;
            return(response);
        }
        //public ConstructionSiteDocumentListResponse GetUnSyncedDocuments(int companyId)
        //{
        //    ConstructionSiteDocumentListResponse response = new ConstructionSiteDocumentListResponse();
        //    List<ConstructionSiteDocumentViewModel> viewModels = new List<ConstructionSiteDocumentViewModel>();

        //    using (SqliteConnection db = new SqliteConnection("Filename=SirmiumERPGFC.db"))
        //    {
        //        db.Open();
        //        try
        //        {
        //            SqliteCommand selectCommand = new SqliteCommand(
        //                SqlCommandSelectPart +
        //                "FROM  ConstructionSiteDocuments " +
        //                "WHERE CompanyId = @CompanyId AND IsSynced = 0 " +
        //                "ORDER BY Id DESC;", db);
        //            selectCommand.Parameters.AddWithValue("@CompanyId", companyId);

        //            SqliteDataReader query = selectCommand.ExecuteReader();

        //            while (query.Read())
        //            {
        //                int counter = 0;
        //                ConstructionSiteDocumentViewModel dbEntry = new ConstructionSiteDocumentViewModel();
        //                dbEntry.Id = SQLiteHelper.GetInt(query, ref counter);
        //                dbEntry.Identifier = SQLiteHelper.GetGuid(query, ref counter);
        //                dbEntry.ConstructionSite = SQLiteHelper.GetConstructionSite(query, ref counter);
        //                dbEntry.Name = SQLiteHelper.GetString(query, ref counter);
        //                dbEntry.CreateDate = SQLiteHelper.GetDateTime(query, ref counter);
        //                dbEntry.Path = SQLiteHelper.GetString(query, ref counter);
        //                dbEntry.IsSynced = SQLiteHelper.GetBoolean(query, ref counter);
        //                dbEntry.UpdatedAt = SQLiteHelper.GetDateTime(query, ref counter);
        //                dbEntry.CreatedBy = SQLiteHelper.GetCreatedBy(query, ref counter);
        //                dbEntry.Company = SQLiteHelper.GetCompany(query, ref counter);
        //                viewModels.Add(dbEntry);
        //            }

        //        }
        //        catch (SqliteException error)
        //        {
        //            MainWindow.ErrorMessage = error.Message;
        //            response.Success = false;
        //            response.Message = error.Message;
        //            response.ConstructionSiteDocuments = new List<ConstructionSiteDocumentViewModel>();
        //            return response;
        //        }
        //        db.Close();
        //    }
        //    response.Success = true;
        //    response.ConstructionSiteDocuments = viewModels;
        //    return response;
        //}

        #endregion

        #region Sync

        public void Sync(IConstructionSiteDocumentService ConstructionSiteDocumentService, Action <int, int> callback = null)
        {
            try
            {
                SyncConstructionSiteDocumentRequest request = new SyncConstructionSiteDocumentRequest();
                request.CompanyId     = MainWindow.CurrentCompanyId;
                request.LastUpdatedAt = GetLastUpdatedAt(MainWindow.CurrentCompanyId);

                int toSync      = 0;
                int syncedItems = 0;

                ConstructionSiteDocumentListResponse response = ConstructionSiteDocumentService.Sync(request);
                if (response.Success)
                {
                    toSync = response?.ConstructionSiteDocuments?.Count ?? 0;
                    List <ConstructionSiteDocumentViewModel> items = response.ConstructionSiteDocuments;

                    using (SqliteConnection db = new SqliteConnection("Filename=SirmiumERPGFC.db"))
                    {
                        db.Open();
                        using (var transaction = db.BeginTransaction())
                        {
                            SqliteCommand deleteCommand = db.CreateCommand();
                            deleteCommand.CommandText = "DELETE FROM ConstructionSiteDocuments WHERE Identifier = @Identifier";

                            SqliteCommand insertCommand = db.CreateCommand();
                            insertCommand.CommandText = SqlCommandInsertPart;

                            foreach (var item in items)
                            {
                                deleteCommand.Parameters.AddWithValue("@Identifier", item.Identifier);
                                deleteCommand.ExecuteNonQuery();
                                deleteCommand.Parameters.Clear();

                                if (item.IsActive)
                                {
                                    item.IsSynced = true;

                                    insertCommand = AddCreateParameters(insertCommand, item);
                                    insertCommand.ExecuteNonQuery();
                                    insertCommand.Parameters.Clear();

                                    syncedItems++;
                                    callback?.Invoke(syncedItems, toSync);
                                }
                            }

                            transaction.Commit();
                        }
                        db.Close();
                    }
                }
                else
                {
                    throw new Exception(response.Message);
                }
            }
            catch (Exception ex)
            {
                MainWindow.ErrorMessage = ex.Message;
            }
        }