public static List <CreateDB.Show> ShowWorker(string userName = "")
{
List <CreateDB.Show> content = new List <CreateDB.Show>();
using (var db = new SQLite.SQLiteConnection(CreateDB.GetDataBasePath, true))
{
var id = userName == "" ? 4: db.ExecuteScalar <int>("select group_id from TableDepartment where Name ='" + userName + "'");
var idChief = db.ExecuteScalar <int>("select id from TableDepartment where Name ='" + userName + "'");
var querySub = db.Query <CreateDB.Show>("select b.name, d.name_group, b.Date, b.wage" +
" from TableDepartment as a, TableSubordination as c, TableDepartment as b, TableGroup as d" +
" where c.chief_id = a.id" +
" and c.department_id = b.id" +
" and b.group_id = d.id" +
" and c.chief_id =" + idChief);
var contentDB = db.Query <CreateDB.Show>("select a.name, b.name_group, a.Date, a.wage from TableDepartment as a, TableGroup as b where a.group_id == b.id");
if (id != 4)
{
querySub.ForEach((x) => content.Add(new CreateDB.Show()
{
Name = x.Name, Name_group = x.Name_group, Date = x.Date, Wage = x.Wage
}));
}
else
{
contentDB.ForEach((x) => content.Add(new CreateDB.Show()
{
Name = x.Name, Name_group = x.Name_group, Date = x.Date, Wage = x.Wage
}));
}
db.Dispose();
}
return(content);
}
public static String getUserType()
{
using (var conn = new SQLite.SQLiteConnection(Path.Combine(System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal), "database.sqlite")))
{
if (conn.ExecuteScalar <int>("SELECT count(*) FROM sqlite_master WHERE type = 'table' AND name = 'User'") != 0)
{
return("Uczestnik");
}
if (conn.ExecuteScalar <int>("SELECT count(*) FROM sqlite_master WHERE type = 'table' AND name = 'Company'") != 0)
{
return("Wystawca");
}
}
return(null);
}
/**
* Saves the data of a hole
* game : the game
* hole : the hole to save
* save : true to save the hole stats, false otherwise. false returns the ScoreHole anyway
* return the created ScoreHole
*/
public static ScoreHole saveForStats(Partie game, Hole hole, bool save)
{
ScoreHole h = new ScoreHole(hole, game.getPenalityCount(), game.getCurrentScore(), isHit(game.Shots, hole.Par), nbCoupPutt(game.Shots), DateTime.Now);
if (save)
{
if (game.Shots.Count == 0)
{
throw new Exception("0 shots dans la liste des shots.");
}
SQLite.SQLiteConnection connection = DependencyService.Get <ISQLiteDb>().GetConnection();
connection.CreateTable <Club>();
connection.CreateTable <MyPosition>();
connection.CreateTable <Shot>();
//first let's insert in the database all the shots currently stored in the game
SQLiteNetExtensions.Extensions.WriteOperations.InsertOrReplaceAllWithChildren(connection, game.Shots, true);
connection.CreateTable <ScoreHole>();
//then creates a ScoreHole object that stores the hole statistics and insert it in the database
SQLiteNetExtensions.Extensions.WriteOperations.InsertWithChildren(connection, h, false);
string sql = @"select last_insert_rowid()";
h.Id = connection.ExecuteScalar <int>(sql);
}
return(h);
}
public static int CountThisWeakTasks()
{
using (var conn = new SQLite.SQLiteConnection(Database.dbPath))
{
return(conn.ExecuteScalar <int>("SELECT Count(*) FROM MainTask WHERE [Date] >= date('now', 'weekday 1', '-7 day', 'start of day') AND [Date] <= date('now', 'weekday 1', 'start of day')"));
}
}
public static int CountTasks()
{
using (var conn = new SQLite.SQLiteConnection(Database.dbPath))
{
return(conn.ExecuteScalar <int>("SELECT Count(*) FROM MainTask"));
}
}
public static void Rights(string userName)
{
using (var db = new SQLite.SQLiteConnection(CreateDB.GetDataBasePath, true))
{
var id = db.ExecuteScalar <int>("select group_id from TableDepartment where Name ='" + userName + "'");
var wage = db.ExecuteScalar <int>("select wage from TableDepartment where Name ='" + userName + "'");
if (id == 4)
{
Data.Salary("Суммарная зарплата всех сотрудников = " + TotalSalary() + " р.");
}
else
{
Data.Salary("Ваша заработная плата за месяц составляет = " + wage + " р.");
}
Data.EventHandler(id);
db.Dispose();
}
}
public int Create(Construction obj)
{
using (SQLite.SQLiteConnection conn = new SQLite.SQLiteConnection(App.DB_PATH))
{
conn.CreateTable <Construction>();
conn.CreateTable <ConstrucionEmployee>();
conn.Insert(obj);
return(conn.ExecuteScalar <int>("SELECT MAX(Id) FROM Construction"));
}
}
public static void DeleteWorker(string name)
{
using (var db = new SQLite.SQLiteConnection(CreateDB.GetDataBasePath, true))
{
var id = db.ExecuteScalar <int>("select id from TableDepartment where Name ='" + name + "'");
db.Delete <CreateDB.TableDepartment>(id);
db.Execute("delete from TableSubordination where chief_id =" + id);
db.Execute("delete from TableSubordination where department_id =" + id);
db.Dispose();
}
}
public static string GetPercentWorker(string nameGroup)
{
double rate;
using (var db = new SQLite.SQLiteConnection(CreateDB.GetDataBasePath, true))
{
rate = db.ExecuteScalar <double>("select percent_worker from TableGroup where Name_group ='" + nameGroup + "'");
db.Dispose();
}
return(rate.ToString());
}
public static MobileLogin GetLogin()
{
using (var conn = new SQLite.SQLiteConnection(Path.Combine(System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal), "database.sqlite")))
{
if (conn.ExecuteScalar <int>("SELECT count(*) FROM sqlite_master WHERE type = 'table' AND name = 'Company'") != 0)
{
return(conn.Table <MobileLogin>().First());
}
}
return(null);
}
public static string GetRate(string nameGroup)
{
int rate;
using (var db = new SQLite.SQLiteConnection(CreateDB.GetDataBasePath, true))
{
rate = db.ExecuteScalar <int>("select rate from TableGroup where Name_group ='" + nameGroup + "'");
db.Dispose();
}
return(rate.ToString());
}
private void LogoutUser()
{
//Remove UserAccount information off device
using (SQLite.SQLiteConnection conn = new SQLite.SQLiteConnection(App.DB_PATH))
{
conn.CreateTable <UserAccount>();
conn.ExecuteScalar <UserAccount>("DELETE FROM UserAccount"); //Remove any/all user account info
}
App.IsUserLoggedIn = false;
App.CurrentUser = null;
}
public static Expo GetUserExpo(int index)
{
using (var conn = new SQLite.SQLiteConnection(Path.Combine(System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal), "database.sqlite")))
{
var count = conn.ExecuteScalar <int>("SELECT count(*) FROM sqlite_master WHERE type = 'table' AND name = 'Expo'");
if (count != 0)
{
return(conn.Table <Expo>().Where(s => s.Id == index).First());
}
}
return(new Expo());
}
//Save
private void saveButton_Clicked(object sender, EventArgs e)
{
Player player = new Player()
{
Name = nameEntry.Text,
Email = emailEntry.Text,
Active = activeSwitch.IsToggled
};
player.Id = openPlayer.Id;
if (player.Name == null || player.Name.ToString().Trim() == "")
{
DisplayAlert("Warning!", "Please enter a player name!", "OK");
return;
}
using (SQLite.SQLiteConnection conn = new SQLite.SQLiteConnection(App.DB_PATH))
{
int numberOfRows = 0;
if (player.Id > 0)
{
numberOfRows = conn.Update(player);
//Update player names within the tournament player rosters
conn.ExecuteScalar <TournamentMainPlayer>("UPDATE TournamentMainPlayer SET PlayerName = ? WHERE PlayerId = ?", player.Name, player.Id);
if (numberOfRows > 0)
{
DisplayAlert("Success", "Player successfully updated", "Great!");
}
else
{
DisplayAlert("Failure", "Player failed to be updated", "Oops!");
}
}
else
{
numberOfRows = conn.Insert(player);
//if (numberOfRows > 0) DisplayAlert("Success", "Player successfully created", "Great!");
//else DisplayAlert("Failure", "Player failed to be created", "Oops!");
if (numberOfRows == 0)
{
DisplayAlert("Failure", "Player failed to be created", "Oops!");
}
}
Navigation.PopAsync();
}
}
public static HistoryU GetHistoryUser(long index)
{
using (var conn = new SQLite.SQLiteConnection(Path.Combine(System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal), "database.sqlite")))
{
var count = conn.ExecuteScalar <int>("SELECT count(*) FROM sqlite_master WHERE type = 'table' AND name = 'HistoryU'");
if (count != 0)
{
if (conn.Table <HistoryU>().Count() != 0)
{
return(conn.Table <HistoryU>().Where(s => s.ID == index).First());
}
}
}
return(new HistoryU());
}
// checks if the databas exists
public static bool checkExistance()
{
using (var conn = new SQLite.SQLiteConnection(loadConnectionString()))
{
var tableExistsQuery = "SELECT name FROM sqlite_master WHERE type='table' AND name='Song';";
var result = conn.ExecuteScalar <string>(tableExistsQuery);
if (result == null)
{
return(false);
}
else
{
return(true);
}
}
}
// checks if song is in databse with the song's path and name
public static Boolean checkIfSongIsInDatabase(string pathAndName)
{
using (var conn = new SQLite.SQLiteConnection(loadConnectionString()))
{
string query = "SELECT EXISTS(SELECT * FROM Song WHERE pathAndName=?)";
int value = conn.ExecuteScalar <int>(query, pathAndName);
if (value == 0)
{
return(false);
}
else
{
return(true);
}
}
}
public void Save(Preference model)
{
using (var db = new SQLite.SQLiteConnection(Settings.DatabasePath))
{
var existingCount = db.ExecuteScalar<int>("select count(Name) from Preference where Name = ?",
model.Name);
if (existingCount == 0)
{
db.Insert(model);
}
else
{
db.Execute("update Preference set Value = ? where Name = ?",
model.Value, model.Name);
}
}
}
public static List <Expo> GetUserExpo()
{
using (var conn = new SQLite.SQLiteConnection(Path.Combine(System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal), "database.sqlite")))
{
var count = conn.ExecuteScalar <int>("SELECT count(*) FROM sqlite_master WHERE type = 'table' AND name = 'Expo'");
if (count != 0)
{
List <Expo> lista = new List <Expo>();
for (int i = 0; i < conn.Table <Expo>().Count(); i++)
{
lista.Add(conn.Table <Expo>().ElementAt(i));
}
return(lista);
}
}
return(new List <Expo>());
}
public static List <Worker> GetWorkersChiefEdit(string userName)
{
List <Worker> workersList = new List <Worker>();
using (var db = new SQLite.SQLiteConnection(CreateDB.GetDataBasePath, true))
{
var idSub = db.ExecuteScalar <int>("select id from TableDepartment where Name ='" + userName + "'");
var querySub = db.Query <CreateDB.Show>("select b.name, d.name_group, b.Date, b.wage" +
" from TableDepartment as a, TableSubordination as c, TableDepartment as b, TableGroup as d" +
" where c.department_id = a.id" +
" and c.chief_id = b.id" +
" and b.group_id = d.id" +
" and c.department_id =" + idSub);
querySub.ToList().ForEach((x) => workersList.Add(new Worker()
{
Id = 1, Name = x.Name
}));
db.Dispose();
}
return(workersList);
}
public static void AddInDataBase(string name, string groupName, string date, string password, List <Worker> workersSub, List <Worker> workerChief, double wage = 0)
{
using (var db = new SQLite.SQLiteConnection(CreateDB.GetDataBasePath, true))
{
List <CreateDB.TableDepartment> insert = new List <CreateDB.TableDepartment>();
List <CreateDB.TableGroup> insertGroup = new List <CreateDB.TableGroup>();
var tableGroup = from s in db.Table <CreateDB.TableGroup>()
where s.Name_group == groupName
select s;
tableGroup.ToList().ForEach(item => insertGroup.Add(item));
insert.Add(new CreateDB.TableDepartment()
{
Group_ID = insertGroup[0].Id, Name = name, Date = Convert.ToDateTime(date), Wage = wage, Password = Action.Encrypt(password)
});
db.InsertAll(insert);
var id = db.ExecuteScalar <int>("select id from TableDepartment where Name ='" + name + "'");
AddSubordination(id, workersSub);
AddChief(id, workerChief);
AddWage(groupName, id);
db.Dispose();
}
}
private bool decryptAndroid(string encryptDb, string key, string decryptDb)
{
bool result = true;
SQLite.SQLiteConnection connection = null;
try
{
connection = new SQLite.SQLiteConnection(encryptDb, true, key);
connection.Execute("ATTACH DATABASE '" + decryptDb + "' AS plaintext KEY '';");
connection.ExecuteScalar <string>("SELECT sqlcipher_export('plaintext');");
connection.Execute("DETACH DATABASE plaintext;");
}
catch (Exception e)
{
Console.WriteLine(e.Message);
result = false;
}
if (connection != null)
{
connection.Close();
}
return(result);
}
public static Boolean updateDatabase()
{
/* if the path has been changed and the database
* exists drop all database data in Song table */
if (Properties.Settings.Default.oldDatabasePath != Properties.Settings.Default.databasePath)
{
if (MediaDatabase.checkExistance() == true)
{
using (var conn = new SQLite.SQLiteConnection(loadConnectionString()))
{
string query = "DROP TABLE Song";
conn.ExecuteScalar <string>(query);
}
}
}
// if database doesn't exist create
if (MediaDatabase.checkExistance() != true)
{
// in sqlite-net-pcl creating a table will create the database if it doesn't exist
createTable();
}
string[] artists;
// if we cannot get directories, access to one has been denied. IE the update failed
try
{
artists = Directory.GetDirectories(Properties.Settings.Default.databasePath);
}
catch
{
return(false);
}
// for each artist found get their albums
foreach (string oneArtist in artists)
{
string[] albums;
// if access to a album directory is denied, the update was unsuccessful
try
{
albums = Directory.GetDirectories(oneArtist);
}
catch
{
return(false);
}
// for each album find the songs
foreach (string oneAlbum in albums)
{
DirectoryInfo dir = new DirectoryInfo(oneAlbum);
// If access to a song in an album directory is denied, the update was unsuccessful
try
{
Directory.GetFiles(oneAlbum);
}
catch
{
return(false);
}
// going through individual files in album directory and adding them to a database
foreach (FileInfo flinfo in dir.GetFiles())
{
string name = flinfo.Name;
// ensuring the file is a .mp3 or .wav
if (name.Contains(".mp3") || name.Contains(".wav"))
{
string pathAndName = oneAlbum + "\\" + name;
// if the song isn't in the database, add it.
// if it is, do nothing.
if (!checkIfSongIsInDatabase(pathAndName))
{
var tfile = TagLib.File.Create(@pathAndName);
string title = tfile.Tag.Title;
string album = tfile.Tag.Album;
string artist = tfile.Tag.FirstAlbumArtist;
TimeSpan duration = tfile.Properties.Duration;
Song song = new Song(name, artist, title, album, duration, pathAndName);
insertMp3(song);
}
}
else
{
continue;
}
}
}
}
return(true);
}
private void UpdateDatbase()
{
try {
using (var conn = new SQLite.SQLiteConnection (pathToDatabase)) {
var num = conn.ExecuteScalar<Int32> ("SELECT count(name) FROM sqlite_master WHERE type='table' and name='CNNote'", new object[]{ });
int count = Convert.ToInt32 (num);
if (count > 0)
return;
conn.CreateTable<CNNote> ();
conn.CreateTable<CNNoteDtls> ();
conn.DropTable<AdPara> ();
conn.CreateTable<AdPara> ();
conn.DropTable<AdNumDate> ();
conn.CreateTable<AdNumDate> ();
conn.DropTable<CompanyInfo> ();
conn.CreateTable<CompanyInfo> ();
conn.DropTable<Trader> ();
conn.CreateTable<Trader> ();
conn.DropTable<AdUser> ();
conn.CreateTable<AdUser> ();
string sql = @"ALTER TABLE Invoice RENAME TO sqlitestudio_temp_table;
CREATE TABLE Invoice (invno varchar PRIMARY KEY NOT NULL, trxtype varchar, invdate bigint, created bigint, amount float, taxamt float, custcode varchar, description varchar, uploaded bigint, isUploaded integer, isPrinted integer);
INSERT INTO Invoice (invno, trxtype, invdate, created, amount, taxamt, custcode, description, uploaded, isUploaded,isPrinted) SELECT invno, trxtype, invdate, created, amount, taxamt, custcode, description, uploaded, isUploaded,0 FROM sqlitestudio_temp_table;
DROP TABLE sqlitestudio_temp_table";
string[] sqls = sql.Split (new char[]{ ';' });
foreach (string ssql in sqls) {
conn.Execute (ssql, new object[]{ });
}
}
} catch (Exception ex) {
AlertShow (ex.Message);
}
}
public async void InsertForeignData(int user_id, int box_id)
{
try
{
var apiSecurity = Application.Current.Resources["APISecurity"].ToString();
AddViewToUser(user_id);
var ForeingUser = await apiService.GetUserId(apiSecurity,
"/api",
"/Users",
user_id);
using (var connSQLite = new SQLite.SQLiteConnection(App.root_db))
{
connSQLite.CreateTable <ForeingProfile>();
}
ForeingBox foreingBox;
ForeingBox A = new ForeingBox();
ForeingBox oldForeing = new ForeingBox();
//Validar que la box no exista
using (var connSQLite = new SQLite.SQLiteConnection(App.root_db))
{
A = connSQLite.FindWithQuery <ForeingBox>("select * from ForeingBox where ForeingBox.BoxId = " + box_id + " and ForeingBox.UserRecivedId = " + MainViewModel.GetInstance().User.UserId);
}
if (A == null)
{
//Inicializar la box foranea
foreingBox = new ForeingBox
{
BoxId = box_id,
UserId = user_id,
Time = DateTime.Now,
ImagePath = ForeingUser.ImagePath,
UserTypeId = ForeingUser.UserTypeId,
FirstName = ForeingUser.FirstName,
LastName = ForeingUser.LastName,
Edad = ForeingUser.Edad,
Ubicacion = ForeingUser.Ubicacion,
Ocupacion = ForeingUser.Ocupacion,
Conexiones = ForeingUser.Conexiones,
UserRecivedId = MainViewModel.GetInstance().User.UserId
};
//Insertar la box foranea
using (var connSQLite = new SQLite.SQLiteConnection(App.root_db))
{
connSQLite.CreateTable <ForeingBox>();
connSQLite.Insert(foreingBox);
}
}
else
{
using (var connSQLite = new SQLite.SQLiteConnection(App.root_db))
{
oldForeing = A;
connSQLite.ExecuteScalar <ForeingBox>("UPDATE ForeingBox SET Conexiones = ? WHERE ForeingBox.UserId = ?", ForeingUser.Conexiones, ForeingUser.UserId);
connSQLite.ExecuteScalar <ForeingBox>("UPDATE ForeingBox SET Time = ? WHERE ForeingBox.BoxId = ?", DateTime.Now, A.BoxId);
A = connSQLite.FindWithQuery <ForeingBox>("select * from ForeingBox where ForeingBox.BoxId = ?", box_id);
}
foreingBox = A;
}
if (box_id != 0)
{
if (A != null)
{
using (var connSQLite = new SQLite.SQLiteConnection(App.root_db))
{
connSQLite.Execute("Delete from ForeingProfile Where ForeingProfile.BoxId = ?", A.BoxId);
}
}
#region Foreing Profiles New Code
await GetListEmail(user_id, box_id);
await GetListPhone(user_id, box_id);
await GetListSM(user_id, box_id);
await GetListWhatsapp(user_id, box_id);
#endregion
if (NotNull1 == true && NotNull2 == true && NotNull3 == true && NotNull4 == true)
{
Device.BeginInvokeOnMainThread(() =>
{
MainViewModel.GetInstance().ForeingBox = new ForeingBoxViewModel(foreingBox);
if (PopupNavigation.PopupStack.Count != 0)
{
PopupNavigation.Instance.PopAllAsync();
}
if (A == null)
{
MainViewModel.GetInstance().ListForeignBox.AddList(foreingBox);
}
else
{
//Box anterior
//oldForeing
MainViewModel.GetInstance().ListForeignBox.UpdateList(foreingBox.UserId);
}
PopupNavigation.Instance.PushAsync(new ForeingBoxPage(foreingBox, true));
});
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
}
public void HomeNavigationView_NavigationItemSelected(object sender, Android.Support.Design.Widget.NavigationView.NavigationItemSelectedEventArgs e)
{
var menuItem = e.MenuItem; menuItem.SetChecked(!menuItem.IsChecked);
Android.Content.Intent intent;
switch (menuItem.ItemId)
{
case Resource.Id.nav_my_expos:
intent = new Android.Content.Intent(this, typeof(MyExposActivity));
StartActivity(intent);
break;
case Resource.Id.nav_help:
intent = new Android.Content.Intent(this, typeof(VideoActivity));
StartActivity(intent);
break;
case Resource.Id.nav_expos:
intent = new Android.Content.Intent(this, typeof(ExposActivity));
StartActivity(intent);
break;
case Resource.Id.nav_prof:
intent = new Android.Content.Intent(this, typeof(MyProfileActivity));
StartActivity(intent);
break;
case Resource.Id.nav_history:
intent = new Android.Content.Intent(this, typeof(HistoryActivity));
StartActivity(intent);
break;
case Resource.Id.nav_logout:
using (var conn = new SQLite.SQLiteConnection(System.IO.Path.Combine(System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal).ToString(), "database.sqlite")))
{
conn.DeleteAll <Login>();
if (conn.ExecuteScalar <int>("SELECT count(*) FROM sqlite_master WHERE type = 'table' AND name = 'User'") != 0)
{
if (LDbConnection.GetCompany() != null)
{
Company c = conn.Table <Company>().First();
conn.DropTable <Company>();
conn.DropTable <Login>();
conn.DropTable <Expo>();
conn.DropTable <HistoryU>();
File.Delete(System.IO.Path.Combine(System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal) + c.Photo1));
File.Delete(System.IO.Path.Combine(System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal) + c.Photo2));
File.Delete(System.IO.Path.Combine(System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal) + c.Photo3));
File.Delete(System.IO.Path.Combine(System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal) + c.Photo4));
File.Delete(System.IO.Path.Combine(System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal) + c.Photo5));
File.Delete(System.IO.Path.Combine(System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal) + c.StandPhoto));
File.Delete(System.IO.Path.Combine(System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal) + c.CompanyLogo));
}
if (LDbConnection.GetUser() != null)
{
User u = conn.Table <User>().First();
conn.DropTable <User>();
conn.DropTable <Login>();
conn.DropTable <Expo>();
conn.DropTable <HistoryU>();
File.Delete(System.IO.Path.Combine(System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal) + u.Photo));
}
if (LDbConnection.GetUserExpo() != null)
{
conn.DropTable <Expo>();
}
if (LDbConnection.GetHistoryUser() != null)
{
if (LDbConnection.GetHistoryUser().Count != 0)
{
conn.DropTable <HistoryU>();
}
}
}
conn.Commit();
conn.Close();
}
intent = new Android.Content.Intent(this, typeof(MainActivity));
StartActivity(intent);
Finish();
break;
}
}
public static async void InsertForeignData(int user_id, int box_id)
{
ApiService apiService = new ApiService();
int user_I = user_id;
var apiSecurity = Application.Current.Resources["APISecurity"].ToString();
User box_detail = new User();
AddViewToUser(user_I);
box_detail = await apiService.GetUserId(apiSecurity,
"/api",
"/Users",
user_I);
using (var connSQLite = new SQLite.SQLiteConnection(App.root_db))
{
connSQLite.CreateTable <ForeingProfile>();
}
ForeingBox foreingBox;
ForeingProfile foreingProfile;
ForeingBox A = new ForeingBox();
ForeingBox oldForeing = new ForeingBox();;
//Validar que la box no exista
using (var connSQLite = new SQLite.SQLiteConnection(App.root_db))
{
A = connSQLite.FindWithQuery <ForeingBox>("select * from ForeingBox where ForeingBox.BoxId = " + box_id + " and ForeingBox.UserRecivedId = " + MainViewModel.GetInstance().User.UserId);
}
if (A == null)
{
//Inicializar la box foranea
foreingBox = new ForeingBox
{
BoxId = box_id,
UserId = user_I,
//Time = Convert.ToDateTime(nfcData[0].time).ToUniversalTime(),
Time = DateTime.Now,
ImagePath = box_detail.ImagePath,
UserTypeId = box_detail.UserTypeId,
FirstName = box_detail.FirstName,
LastName = box_detail.LastName,
Edad = box_detail.Edad,
Ubicacion = box_detail.Ubicacion,
Ocupacion = box_detail.Ocupacion,
Conexiones = box_detail.Conexiones,
UserRecivedId = MainViewModel.GetInstance().User.UserId
};
//Insertar la box foranea
using (var connSQLite = new SQLite.SQLiteConnection(App.root_db))
{
connSQLite.Insert(foreingBox);
}
}
else
{
using (var connSQLite = new SQLite.SQLiteConnection(App.root_db))
{
oldForeing = A;
connSQLite.ExecuteScalar <ForeingBox>("UPDATE ForeingBox SET Conexiones = ? WHERE ForeingBox.UserId = ?", box_detail.Conexiones, box_detail.UserId);
connSQLite.ExecuteScalar <ForeingBox>("UPDATE ForeingBox SET Time = ? WHERE ForeingBox.BoxId = ?", DateTime.Now, A.BoxId);
A = connSQLite.FindWithQuery <ForeingBox>("select * from ForeingBox where ForeingBox.BoxId = ?", box_id);
}
foreingBox = A;
}
try
{
if (box_id != 0)
{
//using (var connSQLite = new SQLite.SQLiteConnection(App.root_db))
//{
// connSQLite.CreateTable<Profile_get>();
//}
if (A != null)
{
using (var connSQLite = new SQLite.SQLiteConnection(App.root_db))
{
connSQLite.Execute("Delete from ForeingProfile Where ForeingProfile.BoxId = ?", A.BoxId);
}
}
#region ForeignProfiles
System.Text.StringBuilder sb;
string cadenaConexion = @"data source=serverappmynfo.database.windows.net;initial catalog=mynfo;user id=adminmynfo;password=4dmiNFC*Atx2020;Connect Timeout=60";
//string cadenaConexion = @"data source=serverappmynfo1.database.windows.net;initial catalog=mynfo;user id=adminmynfo;password=4dmiNFC*Atx2020;Connect Timeout=60";
//Creación de perfiles locales de box local
string queryGetBoxEmail = "select * from dbo.ProfileEmails " +
"join dbo.Box_ProfileEmail on" +
"(dbo.ProfileEmails.ProfileEmailId = dbo.Box_ProfileEmail.ProfileEmailId) " +
"where dbo.Box_ProfileEmail.BoxId = " + box_id;
string queryGetBoxPhone = "select * from dbo.ProfilePhones " +
"join dbo.Box_ProfilePhone on" +
"(dbo.ProfilePhones.ProfilePhoneId = dbo.Box_ProfilePhone.ProfilePhoneId) " +
"where dbo.Box_ProfilePhone.BoxId = " + box_id;
string queryGetBoxSMProfiles = "select * from dbo.ProfileSMs " +
"join dbo.Box_ProfileSM on" +
"(dbo.ProfileSMs.ProfileMSId = dbo.Box_ProfileSM.ProfileMSId) " +
"join dbo.RedSocials on(dbo.ProfileSMs.RedSocialId = dbo.RedSocials.RedSocialId) " +
"where dbo.Box_ProfileSM.BoxId = " + box_id;
string queryGetBoxWhatsappProfiles = "select * from dbo.ProfileWhatsapps " +
"join dbo.Box_ProfileWhatsapp on" +
"(dbo.ProfileWhatsapps.ProfileWhatsappId = dbo.Box_ProfileWhatsapp.ProfileWhatsappId) " +
"where dbo.Box_ProfileWhatsapp.BoxId = " + box_id;
//Consulta para obtener perfiles email
using (SqlConnection conn1 = new SqlConnection(cadenaConexion))
{
sb = new System.Text.StringBuilder();
sb.Append(queryGetBoxEmail);
string sql = sb.ToString();
using (SqlCommand command = new SqlCommand(sql, conn1))
{
conn1.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
foreingProfile = new ForeingProfile
{
BoxId = box_id,
UserId = (int)reader["UserId"],
ProfileName = (string)reader["Name"],
value = (string)reader["Email"],
ProfileType = "Email"
};
//Crear perfil de correo de box local predeterminada
using (var connSQLite = new SQLite.SQLiteConnection(App.root_db))
{
connSQLite.Insert(foreingProfile);
}
}
}
conn1.Close();
}
}
//Consulta para obtener perfiles teléfono
using (SqlConnection conn1 = new SqlConnection(cadenaConexion))
{
sb = new System.Text.StringBuilder();
sb.Append(queryGetBoxPhone);
string sql = sb.ToString();
using (SqlCommand command = new SqlCommand(sql, conn1))
{
conn1.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
foreingProfile = new ForeingProfile
{
BoxId = box_id,
UserId = (int)reader["UserId"],
ProfileName = (string)reader["Name"],
value = (string)reader["Number"],
ProfileType = "Phone"
};
//Crear perfil de teléfono de box local predeterminada
using (var connSQLite = new SQLite.SQLiteConnection(App.root_db))
{
connSQLite.Insert(foreingProfile);
}
}
}
conn1.Close();
}
}
//Consulta para obtener perfiles de redes sociales
using (SqlConnection conn1 = new SqlConnection(cadenaConexion))
{
sb = new System.Text.StringBuilder();
sb.Append(queryGetBoxSMProfiles);
string sql = sb.ToString();
using (SqlCommand command = new SqlCommand(sql, conn1))
{
conn1.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
foreingProfile = new ForeingProfile
{
BoxId = box_id,
UserId = (int)reader["UserId"],
ProfileName = (string)reader["ProfileName"],
value = (string)reader["link"],
ProfileType = (string)reader["Name"]
};
//Crear perfil de teléfono de box local predeterminada
using (var connSQLite = new SQLite.SQLiteConnection(App.root_db))
{
connSQLite.Insert(foreingProfile);
}
}
}
conn1.Close();
}
}
//Consulta para obtener perfiles Whatsapp
using (SqlConnection conn1 = new SqlConnection(cadenaConexion))
{
sb = new System.Text.StringBuilder();
sb.Append(queryGetBoxWhatsappProfiles);
string sql = sb.ToString();
using (SqlCommand command = new SqlCommand(sql, conn1))
{
conn1.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
foreingProfile = new ForeingProfile
{
BoxId = box_id,
UserId = (int)reader["UserId"],
ProfileName = (string)reader["Name"],
value = (string)reader["Number"],
ProfileType = "Whatsapp"
};
//Crear perfil de Whatsapp de box local predeterminada
using (var connSQLite = new SQLite.SQLiteConnection(App.root_db))
{
connSQLite.Insert(foreingProfile);
}
}
}
conn1.Close();
}
}
#endregion
Device.BeginInvokeOnMainThread(() =>
{
//App.Navigator.PushAsync(new ForeingBoxPage(foreingBox, true));
MainViewModel.GetInstance().ForeingBox = new ForeingBoxViewModel(foreingBox);
//App.Navigator.PopAsync();
if (PopupNavigation.PopupStack.Count != 0)
{
PopupNavigation.Instance.PopAllAsync();
}
//PopupNavigation.Instance.PushAsync(new ForeingBoxPage(foreingBox, true));
if (A == null)
{
MainViewModel.GetInstance().ListForeignBox.AddList(foreingBox);
}
else
{
//Box anterior
//oldForeing
MainViewModel.GetInstance().ListForeignBox.UpdateList(foreingBox.UserId);
}
PopupNavigation.Instance.PushAsync(new ForeingBoxPage(foreingBox, true));
});
}
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
}
public bool IsRespondido(Int32 idpesquisa04, String codigo)
{
int count = conn.ExecuteScalar <int>("SELECT COUNT(idpesquisa07) FROM [tb_pesquisa07] where [idpesquisa04] = " + idpesquisa04 + " AND [chavepesquisa] = '" + codigo + "'");
return(count > 0);
}
public bool HasProject()
{
using (var db = new SQLite.SQLiteConnection(Settings.DatabasePath))
{
return db.ExecuteScalar<int>("select count(Id) from Project") > 0;
}
}
public bool Exists(string code, int projectId)
{
using (var db = new SQLite.SQLiteConnection(Settings.DatabasePath))
{
return db.ExecuteScalar<int>("select count(Code) from Locale where ProjectId = ? and Code = ?", projectId, code) > 0;
}
}