public NewClientData GetClientData(string userId, string clientId, SQLiteConnection connection)
{
try {
List <NewClientData> xx = new List <NewClientData>();
NewClientData x = new NewClientData();
db.AddColumn(userId, db.GetDataBasePath(userId, dataBase), db.clients, "note"); //new column in clients tbl.
db.AddColumn(userId, db.GetDataBasePath(userId, dataBase), db.clientsData, "bodyFatPerc"); //new column in clients tbl.
string sql = string.Format(@"SELECT cd.rowid, cd.clientId, c.birthDate, c.gender, cd.height, cd.weight, cd.waist, cd.hip, cd.pal, cd.goal, cd.activities, cd.diet, cd.meals, cd.date, cd.userId, c.note, cd.bodyFatPerc
FROM clientsdata as cd
LEFT OUTER JOIN clients as c
ON cd.clientId = c.clientId
WHERE cd.clientId = '{0}'", clientId);
using (SQLiteCommand command = new SQLiteCommand(sql, connection)) {
Calculations c = new Calculations();
Goals g = new Goals();
using (SQLiteDataReader reader = command.ExecuteReader()) {
while (reader.Read())
{
x = new NewClientData();
x.id = reader.GetInt32(0);
x.clientId = reader.GetValue(1) == DBNull.Value ? "" : reader.GetString(1);
x.age = C.Age(reader.GetValue(2) == DBNull.Value ? "" : reader.GetString(2));
x.gender.value = reader.GetValue(3) == DBNull.Value ? 0 : reader.GetInt32(3);
x.gender.title = GetGender(x.gender.value).title;
x.height = reader.GetValue(4) == DBNull.Value ? 0.0 : Convert.ToDouble(reader.GetString(4));
x.weight = reader.GetValue(5) == DBNull.Value ? 0.0 : Convert.ToDouble(reader.GetString(5));
x.waist = reader.GetValue(6) == DBNull.Value ? 0.0 : Convert.ToDouble(reader.GetString(6));
x.hip = reader.GetValue(7) == DBNull.Value ? 0.0 : Convert.ToDouble(reader.GetString(7));
x.pal = c.GetPal(reader.GetValue(8) == DBNull.Value ? 0.0 : Convert.ToDouble(reader.GetString(8)));
x.goal.code = reader.GetValue(9) == DBNull.Value ? "" : reader.GetString(9);
x.goal.title = g.GetGoal(x.goal.code).title;
x.activities = JsonConvert.DeserializeObject <List <Activities.ClientActivity> >(reader.GetString(10));
x.diet = JsonConvert.DeserializeObject <Diets.NewDiet>(reader.GetString(11));
x.meals = JsonConvert.DeserializeObject <List <Meals.NewMeal> >(reader.GetString(12));
x.date = reader.GetValue(13) == DBNull.Value ? DateTime.UtcNow.ToString() : reader.GetString(13);
x.userId = reader.GetValue(14) == DBNull.Value ? "" : reader.GetString(14);
x.clientNote = reader.GetValue(15) == DBNull.Value ? "" : reader.GetString(15);
x.bodyFat = new BodyFat.NewBodyFat();
x.bodyFat.bodyFatPerc = reader.GetValue(16) == DBNull.Value ? 0 : Convert.ToDouble(reader.GetString(16));
DetailEnergyExpenditure.DailyActivities da = new DetailEnergyExpenditure.DailyActivities();
x.dailyActivities = da.getDailyActivities(userId, x.clientId);
x.myMeals = GetMyMeals(userId, x.clientId);
x.bmrEquation = C.MifflinStJeor; // TODO GetBmrEquation() & SaveBMREquation()
xx.Add(x);
}
}
}
if (xx.Count > 0)
{
x = xx.OrderByDescending(a => Convert.ToDateTime(a.date)).FirstOrDefault();
}
return(x);
} catch (Exception e) { return(new NewClientData()); }
}
public string GetClientLog(string userId, string clientId)
{
try {
List <NewClientData> xx = new List <NewClientData>();
using (SQLiteConnection connection = new SQLiteConnection("Data Source=" + db.GetDataBasePath(userId, dataBase))) {
connection.Open();
string sql = string.Format(@"SELECT cd.rowid, cd.clientId, c.birthDate, c.gender, cd.height, cd.weight, cd.waist, cd.hip, cd.pal, cd.goal, cd.activities, cd.diet, cd.meals, cd.date, cd.userId, cd.targetedMass
FROM clientsdata as cd
LEFT OUTER JOIN clients as c
ON cd.clientId = c.clientId
WHERE cd.clientId = '{0}'", clientId);
using (SQLiteCommand command = new SQLiteCommand(sql, connection)) {
using (SQLiteDataReader reader = command.ExecuteReader()) {
while (reader.Read())
{
NewClientData x = new NewClientData();
Calculations c = new Calculations();
Goals g = new Goals();
x.id = reader.GetInt32(0);
x.clientId = reader.GetValue(1) == DBNull.Value ? "" : reader.GetString(1);
x.age = C.Age(reader.GetValue(2) == DBNull.Value ? "" : reader.GetString(2));
x.gender.value = reader.GetValue(3) == DBNull.Value ? 0 : reader.GetInt32(3);
x.gender.title = GetGender(x.gender.value).title;
x.height = reader.GetValue(4) == DBNull.Value ? 0.0 : Convert.ToDouble(reader.GetString(4));
x.weight = reader.GetValue(5) == DBNull.Value ? 0.0 : Convert.ToDouble(reader.GetString(5));
x.waist = reader.GetValue(6) == DBNull.Value ? 0.0 : Convert.ToDouble(reader.GetString(6));
x.hip = reader.GetValue(7) == DBNull.Value ? 0.0 : Convert.ToDouble(reader.GetString(7));
x.pal = c.GetPal(reader.GetValue(8) == DBNull.Value ? 0.0 : Convert.ToDouble(reader.GetString(8)));
x.goal.code = reader.GetValue(9) == DBNull.Value ? "" : reader.GetString(9);
x.goal.title = g.GetGoal(x.goal.code).title;
x.activities = JsonConvert.DeserializeObject <List <Activities.ClientActivity> >(reader.GetString(10));
x.diet = JsonConvert.DeserializeObject <Diets.NewDiet>(reader.GetString(11));
x.meals = JsonConvert.DeserializeObject <List <Meals.NewMeal> >(reader.GetString(12));
x.date = reader.GetValue(13) == DBNull.Value ? DateTime.UtcNow.ToString() : reader.GetString(13);
x.userId = reader.GetValue(14) == DBNull.Value ? "" : reader.GetString(14);
x.targetedMass = reader.GetValue(15) == DBNull.Value ? 0.0 : Convert.ToDouble(reader.GetString(15));
xx.Add(x);
}
}
}
}
xx = xx.OrderBy(a => Convert.ToDateTime(a.date)).ToList();
return(JsonConvert.SerializeObject(xx, Formatting.None));
} catch (Exception e) {
L.SendErrorLog(e, null, userId, "ClientsData", "GetClientLog");
return(JsonConvert.SerializeObject(e.Message, Formatting.None));
}
}
public string Load(string userId)
{
try {
SQLiteConnection connection = new SQLiteConnection("Data Source=" + db.GetDataBasePath(userId, dataBase));
connection.Open();
string sql = @"SELECT cd.rowid, cd.clientId, c.birthDate, c.gender, cd.height, cd.weight, cd.waist, cd.hip, cd.pal, cd.goal, cd.activities, cd.diet, cd.meals, cd.date, cd.userId
FROM clientsdata as cd
LEFT OUTER JOIN clients as c
ON cd.clientId = c.clientId
ORDER BY cd.rowid DESC";
SQLiteCommand command = new SQLiteCommand(sql, connection);
List <NewClientData> xx = new List <NewClientData>();
SQLiteDataReader reader = command.ExecuteReader();
while (reader.Read())
{
NewClientData x = new NewClientData();
Calculations c = new Calculations();
Goals g = new Goals();
x.id = reader.GetInt32(0);
x.clientId = reader.GetValue(1) == DBNull.Value ? "" : reader.GetString(1);
x.age = C.Age(reader.GetValue(2) == DBNull.Value ? "" : reader.GetString(2));
x.gender.value = reader.GetValue(3) == DBNull.Value ? 0 : reader.GetInt32(3);
x.gender.title = GetGender(x.gender.value).title;
x.height = reader.GetValue(4) == DBNull.Value ? 0.0 : Convert.ToDouble(reader.GetString(4));
x.weight = reader.GetValue(5) == DBNull.Value ? 0.0 : Convert.ToDouble(reader.GetString(5));
x.waist = reader.GetValue(6) == DBNull.Value ? 0.0 : Convert.ToDouble(reader.GetString(6));
x.hip = reader.GetValue(7) == DBNull.Value ? 0.0 : Convert.ToDouble(reader.GetString(7));
x.pal = c.GetPal(reader.GetValue(8) == DBNull.Value ? 0.0 : Convert.ToDouble(reader.GetString(8)));
x.goal.code = reader.GetValue(9) == DBNull.Value ? "" : reader.GetString(9);
x.goal.title = g.GetGoal(x.goal.code).title;
x.activities = JsonConvert.DeserializeObject <List <Activities.ClientActivity> >(reader.GetString(10));
x.diet = JsonConvert.DeserializeObject <Diets.NewDiet>(reader.GetString(11));
x.meals = JsonConvert.DeserializeObject <List <Meals.NewMeal> >(reader.GetString(12));
x.date = reader.GetValue(13) == DBNull.Value ? DateTime.UtcNow.ToString() : reader.GetString(13);
x.userId = reader.GetValue(14) == DBNull.Value ? "" : reader.GetString(14);
DetailEnergyExpenditure.DailyActivities da = new DetailEnergyExpenditure.DailyActivities();
x.dailyActivities = da.getDailyActivities(userId, x.clientId);
x.myMeals = new MyMeals.NewMyMeals();
xx.Add(x);
}
connection.Close();
return(JsonConvert.SerializeObject(xx, Formatting.None));
} catch (Exception e) { return("Error: " + e); }
}
public NewClientData GetClientData(string userId, string clientId, SQLiteConnection connection)
{
NewClientData x = new NewClientData();
try {
List <NewClientData> xx = new List <NewClientData>();
db.AddColumn(userId, db.GetDataBasePath(userId, dataBase), db.clients, "note"); //new column in clients tbl.
db.AddColumn(userId, db.GetDataBasePath(userId, dataBase), db.clients, "cids", "VARCHAR(200)"); //new column in cids tbl.
db.AddColumn(userId, db.GetDataBasePath(userId, dataBase), db.clients, "dailyActivities", "TEXT");
db.AddColumn(userId, db.GetDataBasePath(userId, dataBase), db.clients, "myRecommendedEnergyIntake", "VARCHAR(50)");
db.AddColumn(userId, db.GetDataBasePath(userId, dataBase), db.clients, "myRecommendedEnergyExpenditure", "VARCHAR(50)");
db.AddColumn(userId, db.GetDataBasePath(userId, dataBase), db.clients, "myMeals", "TEXT");
db.AddColumn(userId, db.GetDataBasePath(userId, dataBase), db.clients, "allergens", "TEXT");
string sql = string.Format(@"SELECT cd.rowid, cd.clientId, c.birthDate, c.gender, cd.height, cd.weight, cd.waist, cd.hip, cd.pal, cd.goal, cd.activities, cd.diet, cd.meals, cd.date, cd.userId, c.note, cd.bodyFatPerc, cd.targetedMass, c.dailyActivities, c.myRecommendedEnergyIntake, c.myRecommendedEnergyExpenditure, c.myMeals
FROM clientsdata as cd
LEFT OUTER JOIN clients as c
ON cd.clientId = c.clientId
WHERE cd.clientId = '{0}'", clientId);
using (SQLiteCommand command = new SQLiteCommand(sql, connection)) {
Calculations c = new Calculations();
Goals g = new Goals();
DetailEnergyExpenditure.DailyActivities da = new DetailEnergyExpenditure.DailyActivities();
string birthDate = null;
using (SQLiteDataReader reader = command.ExecuteReader()) {
while (reader.Read())
{
x = new NewClientData();
x.id = reader.GetInt32(0);
x.clientId = reader.GetValue(1) == DBNull.Value ? "" : reader.GetString(1);
birthDate = reader.GetValue(2) == DBNull.Value ? "" : reader.GetString(2);
x.age = C.Age(birthDate);
x.agemos = C.Agemos(birthDate);
x.days = C.Days(birthDate);
x.gender.value = reader.GetValue(3) == DBNull.Value ? 0 : reader.GetInt32(3);
x.gender.title = GetGender(x.gender.value).title;
x.height = reader.GetValue(4) == DBNull.Value ? 0.0 : Convert.ToDouble(reader.GetString(4));
x.weight = reader.GetValue(5) == DBNull.Value ? 0.0 : Convert.ToDouble(reader.GetString(5));
x.waist = reader.GetValue(6) == DBNull.Value ? 0.0 : Convert.ToDouble(reader.GetString(6));
x.hip = reader.GetValue(7) == DBNull.Value ? 0.0 : Convert.ToDouble(reader.GetString(7));
x.pal = c.GetPal(reader.GetValue(8) == DBNull.Value ? 0.0 : Convert.ToDouble(reader.GetString(8)));
x.goal.code = reader.GetValue(9) == DBNull.Value ? "" : reader.GetString(9);
x.goal.title = g.GetGoal(x.goal.code).title;
x.activities = JsonConvert.DeserializeObject <List <Activities.ClientActivity> >(reader.GetString(10));
x.diet = JsonConvert.DeserializeObject <Diets.NewDiet>(reader.GetString(11));
if (x.diet == null)
{
x.diet = new Diets.NewDiet();
}
x.meals = JsonConvert.DeserializeObject <List <Meals.NewMeal> >(reader.GetString(12));
x.date = reader.GetValue(13) == DBNull.Value ? DateTime.UtcNow.ToString() : reader.GetString(13);
x.userId = reader.GetValue(14) == DBNull.Value ? "" : reader.GetString(14);
x.clientNote = reader.GetValue(15) == DBNull.Value ? "" : reader.GetString(15);
x.bodyFat = new BodyFat.NewBodyFat();
x.bodyFat.bodyFatPerc = reader.GetValue(16) == DBNull.Value ? 0 : Convert.ToDouble(reader.GetString(16));
x.targetedMass = reader.GetValue(17) == DBNull.Value ? 0 : Convert.ToDouble(reader.GetString(17));
string dailyActivities = reader.GetValue(18) == DBNull.Value ? null : reader.GetString(18);
if (!string.IsNullOrWhiteSpace(dailyActivities))
{
var activities = JsonConvert.DeserializeObject <List <DetailEnergyExpenditure.Activity> >(dailyActivities);
x.dailyActivities = new DetailEnergyExpenditure.Activities();
x.dailyActivities.activities = activities;
if (activities != null)
{
x.dailyActivities.energy = activities.Sum(a => a.energy);
x.dailyActivities.duration = activities.Sum(a => a.duration);
}
}
else
{
x.dailyActivities = da.getDailyActivities(userId, x.clientId); // old sistem: data saved in json file
}
x.myCalculation = new Calculations.MyCalculation();
int?myRecommendedEnergyIntake = reader.GetValue(19) == DBNull.Value ? 0 : string.IsNullOrWhiteSpace(reader.GetString(19)) ? 0 : Convert.ToInt32(reader.GetString(19));
int?myRecommendedEnergyExpenditure = reader.GetValue(20) == DBNull.Value ? 0 : string.IsNullOrWhiteSpace(reader.GetString(20)) ? 0 : Convert.ToInt32(reader.GetString(20));
if (myRecommendedEnergyIntake > 0 || myRecommendedEnergyExpenditure > 0)
{
x.myCalculation.recommendedEnergyIntake = myRecommendedEnergyIntake;
x.myCalculation.recommendedEnergyExpenditure = myRecommendedEnergyExpenditure;
}
else
{
x.myCalculation = C.GetJsonFile(userId, x.clientId); // old sistem: data saved in json file
}
string myMeals = reader.GetValue(21) == DBNull.Value ? null : reader.GetString(21);
if (!string.IsNullOrWhiteSpace(myMeals))
{
x.myMeals = JsonConvert.DeserializeObject <MyMeals.NewMyMeals>(myMeals); // new sistem: data saved in db
}
else
{
x.myMeals = GetMyMeals(userId, x.clientId); // old sistem: data saved in json file
}
// TODO: allergens
//x.allergens = reader.GetValue(22) == DBNull.Value ? null : reader.GetString(22);
x.bmrEquation = C.MifflinStJeor; // TODO GetBmrEquation() & SaveBMREquation()
x.percentileSrc = C.WHO;
x.bmiPercentile = C.GetBmiPercentile(x.agemos, x.gender.value, x.percentileSrc);
xx.Add(x);
}
}
}
if (xx.Count > 0)
{
x = xx.OrderByDescending(a => Convert.ToDateTime(a.date)).FirstOrDefault();
}
return(x);
} catch (Exception e) {
L.SendErrorLog(e, clientId, userId, "ClientsData", "GetClientData");
return(x);
}
}