private bool Insert(Client client)
{
try
{
client.Logo = string.IsNullOrEmpty(client.Logo)? "" : client.Logo;
var cmd = DbInfo.CreateDbCommand();
cmd.CommandText = string.Format("select oid from dsto_client where guid='{0}'", client.Key);
var oid = DbInfo.ExecuteScalar(cmd);
string query = string.Empty;
var exists = oid != null;
if (!exists)
{
query = $"INSERT INTO dsto_client ([guid],created_by,[Name],[Email],[Logo],[Contact],[Location],[yref_package]) values('{client.Key}','Admin','{client.Name}','{client.Email}','{client.Logo}','{client.Contact}','{client.Location}','{client.Package.Key}')";
}
else
{
query = $"UPDATE dsto_client SET Name='{client.Name}', " +
$"Email='{client.Email}', " +
$"Contact='{client.Contact}', " +
$"Location='{client.Location}', " +
$"Logo='{client.Logo}', " +
$"[Deleted]='{client.Deleted}', " +
$"yref_package='{client.Package.Key}' " +
$"WHERE oid='{client.OID}'";
}
var added = DbInfo.ExecuteNonQuery(query) > 0;
if (added)
{
oid = DbInfo.ExecuteScalar(cmd);
client.OID = (int)oid;
if (!exists)
{
Billing billing = new Billing();
billing.Package = client.Package;
billing.PaymentStatus = PaymentStatus.Pending;
billing.Client = client;
billing.Bill = client.Package.Price.ToString();
billing.BillingDate = DateTime.Now;
billing.InvoiceNo = "XYZ";
new BillingProvider(DbInfo).Save(billing);
}
foreach (var user in client.Users)
{
user.ClientId = client.OID.ToString();
new UserProvider(DbInfo).AddOrUpdateUser(user);
}
}
return(true);
}
catch (Exception ex)
{
throw ex;
}
}
internal bool Save(Category category)
{
try
{
var cmd = DbInfo.CreateDbCommand();
var missing = Exists(category) == false;
if (missing)
{
string query = $"INSERT INTO dsto_questionaireXcategory ([guid],created_by,[yref_questionaire],[yref_category]) values('{Guid.NewGuid().ToString()}','Admin','{category.QuestionaireId}','{category.Key}')";
return(DbInfo.ExecuteNonQuery(query) > 0);
}
return(false);
}
catch (Exception ex)
{
throw ex;
}
}
public bool AddConfigurationUser(User user)
{
try
{
var cmd = DbInfo.CreateDbCommand();
cmd.CommandText = $"select dsto_user.* from dsto_user inner join dsto_configurationuser on dsto_user.OID = dsto_configurationuser.yref_user where dsto_configurationuser.yref_configuration='{user.ConfigurationId}' and dsto_configurationuser.yref_user='******'";
var oid = DbInfo.ExecuteScalar(cmd);
string Query = string.Empty;
if (oid == null)
{
Query = $"INSERT INTO dsto_configurationuser([yref_user],[yref_configuration]) values('{user.OID}','{user.ConfigurationId}')";
}
else
{
Query = $"UPDATE dsto_configurationuser SET [Deleted]='{user.Deleted}' where [yref_user]='{user.OID}' and [yref_configuration]='{user.ConfigurationId}'";
}
if (DbInfo.ExecuteNonQuery(Query) > -1)
{
if (oid == null)
{
var innerHtml = " <h5> Hello " + user.Firstname + ",</h5>"
+ " <p>" + "Your account has been added to a configuration, you're now eligible to access our mobile application" + "</p>"
+ " <h5 style='margin-top: 85px;'> Best Regards,</h5>"
+ " <span> DCAnalytics Team </span>";
MailService.SendMail(user.Email, true, "Configuration Registration", innerHtml);
}
foreach (UserRight userRight in user.UserRights)
{
userRight.Configuration = new Configuration();
userRight.Configuration.OID = int.Parse(user.ConfigurationId);
userRight.UserId = user.OID;
new UserRightProvider(DbInfo).Save(userRight);
}
}
}
catch (Exception ex)
{
throw ex;
}
return(true);
}
private bool Insert(Region region)
{
try
{
var cmd = DbInfo.CreateDbCommand();
cmd.CommandText = string.Format("select oid from dsto_region where guid='{0}'", region.Key);
var oid = DbInfo.ExecuteScalar(cmd);
string query = string.Empty;
var exists = oid != null;
if (!exists)
{
query = $"INSERT INTO dsto_region ([guid],created_by,[Name],[Prefix], [yref_questionaire]) values('{region.Key}','Admin','{region.Name}','{region.Prefix}', '{region.yref_questionaire}')";
}
else
{
query = $"UPDATE dsto_region SET Name='{region.Name}', " +
$"Prefix='{region.Prefix}' " +
$"Deleted='{region.Deleted}' " +
$"WHERE oid='{region.OID}'";
}
var added = DbInfo.ExecuteNonQuery(query) > 0;
if (added)
{
return(true);
}
else
{
return(false);
}
}
catch (Exception ex)
{
throw ex;
}
}
public bool AddOrUpdateUser(User user)
{
try
{
var cmd = DbInfo.CreateDbCommand();
cmd.CommandText = string.Format("select guid from dsto_User where email='{0}'", user.Email);
var guid = DbInfo.ExecuteScalar(cmd);
string query = string.Empty;
if (guid == null)
{
if (!string.IsNullOrEmpty(user.ClientId))
{
query = $"INSERT INTO dsto_User([guid],firstname,lastname,username,email,isadmin,password,client_id,userType) values('{user.Key}','','','','{user.Email}','{user.IsAdmin}','','{user.ClientId}','{(int)user.UserType}')";
}
else
{
query = $"INSERT INTO dsto_User([guid],firstname,lastname,username,email,isadmin,password,userType) values('{user.Key}','','','','{user.Email}','{user.IsAdmin}','','{(int)user.UserType}')";
}
}
else
{
query = $"UPDATE dsto_User set firstname='{user.Firstname}', " +
$"lastname='{user.Lastname}', username='******', password='******', [Deleted]='{user.Deleted}', usercode = '{user.Usercode}', enabled = '{user.Enabled}' where guid='{guid}'";
}
if (DbInfo.ExecuteNonQuery(query) > -1)
{
cmd.CommandText = string.Format("select OID from dsto_User where email='{0}'", user.Email);
var oid = DbInfo.ExecuteScalar(cmd);
if (guid == null)
{
var innerHtml = "";
if (user.IsAdmin)
{
innerHtml = " <h1 style='text-align: center;color: #299def;'>Welcome aboard</h1>"
+ " <h5> Hello " + user.Firstname + " " + user.Lastname + ",</h5>"
+ " <p>We’re excited you're joining us! You have been invited by DCAnalytics as a System Admin or owner of the account.<p>"
+ " <p>" + "Please click on the link below to complete your account registration" + "</p>"
+ " <a href='" + $"http://www.DCAnalyticsapp.com/#/registration?Id={oid}" + "'> Click here to proceed </a>"
+ " <h5 style='margin-top: 85px;'> Best Regards,</h5>"
+ " <span> DCAnalytics Team </span>";
}
else
{
innerHtml = " <h5> Hello " + user.Firstname + " " + user.Lastname + ",</h5>"
+ " <p>" + "Please click on the link below to complete your account registration" + "</p>"
+ " <a href='" + $"http://www.DCAnalyticsapp.com/#/registration?Id={oid}" + "'> Click here to proceed </a>"
+ " <h5 style='margin-top: 85px;'> Best Regards,</h5>"
+ " <span> DCAnalytics Team </span>";
}
MailService.SendMail(user.Email, true, "Account Registration", innerHtml);
}
foreach (UserRight userRight in user.UserRights)
{
new UserRightProvider(DbInfo).Save(userRight);
}
return(true);
}
return(false);
}
catch (Exception ex)
{
throw ex;
}
}