public static IEnumerable <Product_User> GetByUserId(int userId)
{
string sql = @"Select * FROM " + Product_User.getDbName() + " WHERE userid=@userid";
using (IDbConnection db = new SqlConnection(ConnectionHandler.getConnectionString()))
return(db.Query <Product_User>(sql, new { userid = userId }));
}
public static User GetByEmail(string email)
{
string query = connectionBaseUsers + "WHERE email LIKE @email";
User user;
using (IDbConnection db = new SqlConnection(ConnectionHandler.getConnectionString()))
user = db.QueryFirstOrDefault <User>(query, new { email });
return(user);
}
public static User GetByBarCode(string barcode)
{
string query = connectionBaseUsers + "WHERE barcode LIKE @barcode";
User user;
using (IDbConnection db = new SqlConnection(ConnectionHandler.getConnectionString()))
user = db.QueryFirstOrDefault <User>(query, new { barcode });
if (user != null && user.FirstViewDate.Year == 1)
{
string updatequery = "UPDATE users SET FirstViewDate = GETDATE() WHERE id = @id";
using (IDbConnection db = new SqlConnection(ConnectionHandler.getConnectionString()))
db.Execute(updatequery, new { id = user.id });
}
return(user);
}
public static void OverwriteOrder(IEnumerable <Product_User> newOrderList, string barcode)
{
User user = UserHandler.GetByBarCode(barcode);
string sql = "DELETE FROM {0} WHERE userid=@userid;";
if (newOrderList.Where(x => x.amount > 0).Count() > 0)
{
sql += @" INSERT INTO {0} (userid, produkteid, amount) VALUES {1}; ";
sql = String.Format(sql, Product_User.getDbName(),
generateValueOrder(newOrderList));
}
else
{
sql = String.Format(sql, Product_User.getDbName());
}
using (IDbConnection db = new SqlConnection(ConnectionHandler.getConnectionString()))
db.Execute(sql, new { userid = user.id });
}
public static bool mailNotUsed(string email)
{
bool mailUsed = true;
int counter = 0;
string sql = @"SELECT *
FROM {0}
WHERE email=@email;";
sql = String.Format(sql, User.getDbName());
using (IDbConnection db = new SqlConnection(ConnectionHandler.getConnectionString()))
counter = db.QueryFirstOrDefault <int>(sql, new { email });
if (counter == 0)
{
mailUsed = false;
}
return(!mailUsed);
}
public static string CreateNewUser(User user)
{
int newId;
string query = @"INSERT INTO users
(firstname, lastname,addresse, email, barcode)
output INSERTED.[id]
VALUES(@firstname, @lastname,@addresse, @email,@barcode); ";
string barcodeGenerate = RandomString(29);
string queryCheckUnique = connectionBaseUsers + "WHERE barcode LIKE @barcode";
bool alreadyExists = true;
while (alreadyExists == true)
{
using (IDbConnection db = new SqlConnection(ConnectionHandler.getConnectionString()))
alreadyExists = db.ExecuteScalar <bool>(queryCheckUnique, new { barcode = barcodeGenerate });
barcodeGenerate = RandomString(29);
}
user.barcode = barcodeGenerate;
using (IDbConnection db = new SqlConnection(ConnectionHandler.getConnectionString()))
newId = db.ExecuteScalar <int>(query,
new
{
barcode = barcodeGenerate,
addresse = user.addresse,
firstname = user.firstname,
lastname = user.lastname,
email = user.email
});
_ = EmailHandler.sentMailSentgridAsync("*****@*****.**",
"new user generated",
"new genereated user:"******" " + user.lastname, user.firstname + " " + user.lastname);
sentRegMailToUser(user);
return(barcodeGenerate);
}
public static Product GetById(int id)
{
using (IDbConnection db = new SqlConnection(ConnectionHandler.getConnectionString()))
return(db.QueryFirstOrDefault <Product>(connectionBase + " WHERE id=@id", new { id }));
}
public static IEnumerable <Product> Get()
{
using (IDbConnection db = new SqlConnection(ConnectionHandler.getConnectionString()))
return(db.Query <Product>(connectionBase));
}