public override bool Save(DCAnalyticsObject obj)
{
try
{
SkipCondition condition = obj as SkipCondition;
var exists = RecordExists("dsto_skipcondition", condition.Key);
string query = string.Empty;
string targetKey = (condition.Target.Section != null) ? condition.Target.Section.Key : (condition.Target.SubSection != null) ? condition.Target.SubSection.Key: condition.Target.Question.Key;
if (!exists)
{
query = $"insert into dsto_skipcondition([guid],[created_by],[yref_attribute],[yref_target],[answer],[yref_question],[dataCollectionObectType]) " +
$"values('{condition.Key}','Admin','{condition.AttributeKey}','{targetKey}','{condition.Answer.Key}','{condition.QuestionKey}', '{(int)condition.DataCollectionObectType}')";
}
else
{
query = $"UPDATE dsto_skipcondition SET" +
$"[yref_attribute] = '{condition.AttributeKey}', " +
$"[yref_target] = '{targetKey}', " +
$"[dataCollectionObectType] = '{(int)condition.DataCollectionObectType}', " +
$"[answer] = '{condition.Answer.Key}', " +
$"[Deleted]='{condition.Deleted}' " +
$"WHERE [guid] = '{condition.Key}'";
}
return(DbInfo.ExecuteNonQuery(query) > -1);
}
catch (Exception ex)
{
throw ex;
}
}
public override bool Save(DCAnalyticsObject obj)
{
try
{
UserRight userRight = obj as UserRight;
string query = string.Empty;
var exists = RecordExists("dsto_userright", userRight.Key);
if (!exists)
{
query = $"INSERT INTO dsto_userright([guid],objectname,objecttype,primarykey,yref_user,yref_configuration) values('{userRight.Key}','{userRight.ObjectName}','{(int)userRight.ObjectType}','{userRight.PrimaryKey}', '{userRight.UserId}', '{userRight.Configuration.OID}')";
}
else
{
query = $"UPDATE dsto_userright set objecttype='{(int)userRight.ObjectType}', " +
$"objectname='{userRight.ObjectName}', yref_user='******', [Deleted]='{userRight.Deleted}' " +
$"where guid='{userRight.Key}'";
}
if ((DbInfo.ExecuteNonQuery(query) > -1))
{
foreach (var userPermission in userRight.UserPermissions)
{
new UserPermissionProvider(DbInfo).Save(userPermission);
}
return(true);
}
return(false);
}
catch (Exception ex)
{
throw ex;
}
}
public bool DeletePurchase(int id)
{
string query = $"delete from dsto_Purchase where [oid]={id}";
var rows = DbInfo.ExecuteNonQuery(query);
return(rows > -1);
}
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;
}
}
public override bool Save(DCAnalyticsObject obj)
{
Purchase purchase = obj as Purchase;
string query = string.Empty;
var exists = RecordExists("dsto_Purchase", purchase.Key);
if (!exists)
{
query = $"insert into dsto_Purchase([guid],[created_by],[price],[dateofpurchase],[quantity],[farmerid],[lotid],[configuration_id],[product],[station]) values('{purchase.Key}','{purchase.CreatedBy}','{purchase.Price}','{purchase.DateOfPurchase.ToString("yyyy-MM-dd HH:mm:ss.fff")}','{purchase.Quantity}','{purchase.Farmer}','{purchase.Lotid}','{purchase.ConfigurationId}','{purchase.Product}','{purchase.Station}')";
}
else
{
//update
query = $"UPDATE dsto_Purchase SET [price]={purchase.Price}, " +
$"[dateofpurchase] = '{purchase.DateOfPurchase.ToString("yyyy-MM-dd HH:mm:ss.fff")}', " +
$"[quantity] = '{purchase.Quantity}', " +
$"[created_by] = '{purchase.CreatedBy}', " +
$"[lotid] = '{purchase.Lotid}', " +
$"[farmerid] = '{purchase.Farmer}', " +
$"[product]='{purchase.Product}', " +
$"[station]='{purchase.Station}', " +
$"[Deleted]='{purchase.Deleted}' " +
$"WHERE [guid] = '{purchase.Key}'";
}
if (DbInfo.ExecuteNonQuery(query) > -1)
{
return(true);
}
return(false);
}
private bool Edit(Template template)
{
try
{
string query = $"UPDATE dsto_template SET " +
$"[description]='{template.Description}', " +
$"[name] = '{template.Name}', " +
$"[templateType] = {(int)template.TemplateType}, " +
$"[yref_category] = '{template.Category.Key}', " +
$"[Deleted]='{template.Deleted}' " +
$"WHERE [guid] = '{template.Key}'";
if (DbInfo.ExecuteNonQuery(query) > 0)
{
foreach (var section in template.Sections)
{
section.TemplateKey = template.Key;
new SectionProvider(DbInfo).Save(section);
}
return(true);
}
return(false);
}
catch (Exception ex)
{
throw ex;
}
}
public bool DeleteByReference(string reference)
{
string query = $"delete from dsto_question where yref_questionaire = '{reference}' or yref_section = '{reference}' or yref_subsection = '{reference}'";
var rows = DbInfo.ExecuteNonQuery(query);
return(rows > 0);
}
public bool DeleteDependency(string key)
{
string query = $"delete from dsto_dependency where [guid]='{key}'";
var rows = DbInfo.ExecuteNonQuery(query);
return(rows > -1);
}
public override bool Save(DCAnalyticsObject obj)
{
QuestionProvider provider = new QuestionProvider(DbInfo);
SubSection subsection = obj as SubSection;
var exists = RecordExists("dsto_subsections", subsection.Key);
string query = string.Empty;
if (!exists)
{
query = $"insert into dsto_subsections([guid],Name,[created_by],[yref_section]) values('{subsection.Key}','{subsection.Name}','Admin','{subsection.SectionKey}')";
}
else
{
//update
query = $"UPDATE dsto_subsections SET [Name]='{subsection.Name}', " +
$"[Deleted]='{subsection.Deleted}' " +
$"WHERE [guid] = '{subsection.Key}'";
}
if (DbInfo.ExecuteNonQuery(query) > -1)
{
//save questions
foreach (var qn in subsection.Questions)
{
qn.SubSectionKey = subsection.Key;
provider.Save(qn);
}
return(true);
}
return(false);
}
public bool DeleteUserRight(int oid)
{
string query = $"delete from dsto_userright where [oid]={oid}";
var rows = DbInfo.ExecuteNonQuery(query);
return(rows > -1);
}
public bool DeleteConfigurationUser(User user)
{
string query = $"delete from dsto_ConfigurationUser where [yref_user]='{user.OID}' and [yref_configuration]='{user.ConfigurationId}'";
var rows = DbInfo.ExecuteNonQuery(query);
return(rows > -1);
}
public bool DeleteEnumValue(string key)
{
string query = $"delete from dsto_EnumListValues where [guid]='{key}'";
var rows = DbInfo.ExecuteNonQuery(query);
return(rows > -1);
}
public bool DeleteQuestion(string key)
{
string query = $"delete from dsto_question where [guid]='{key}'";
var rows = DbInfo.ExecuteNonQuery(query);
return(rows > -1);
}
public bool ExecuteQueries()
{
////Execute the queries
foreach (DatabaseQuery q in Queries)
{
if (q.Message == Messages.DropDatabase || q.Message == Messages.CreateDatabase || q.Message == Messages.EnableDBCT || q.Message == Messages.DisableDBCT || q.Message == Messages.AlterDatabase)
{
OnUpgrading(q);
string error = "";
int queryResult = DbInfo.ExecuteNonQuery(q.SqlStatement, true, out error);
if (queryResult > 0 || queryResult == -1)
{
q.Result = 1;
}
else
{
q.Result = -1;
q.ErrorMessage = error;
}
}
}
//// Execute all sql statements NOT related to create, drop or alter database etc...
DbInfo.BeginTransaction(false);
foreach (DatabaseQuery q in Queries)
{
if (q.Message != Messages.DropDatabase && q.Message != Messages.CreateDatabase && q.Message != Messages.EnableDBCT && q.Message != Messages.DisableDBCT && q.Message != Messages.AlterDatabase)
{
OnUpgrading(q);
string error = "";
int queryResult = DbInfo.ExecuteNonQuery(q.SqlStatement, out error);
if (queryResult > 0 || queryResult == -1)
{
q.Result = 1;
}
else
{
q.Result = -1;
q.ErrorMessage = error;
}
}
}
//// Rollback transaction if execution of any sql statement failed on the database. Otherwise commit transaction
if (Queries.HasErrors)
{
DbInfo.RollBackTransaction();
return(false);
}
else
{
DbInfo.CommitTransaction();
return(true);
}
}
private bool Edit(Category category)
{
string query = $"UPDATE dsto_category SET " +
$"[name]='{category.Name}', " +
$"[Deleted] = '{category.Deleted}', " +
$"WHERE [guid] = '{category.Key}'";
return(DbInfo.ExecuteNonQuery(query) > -1);
}
private bool Edit(Package package)
{
string query = $"UPDATE dsto_package SET [name]='{package.Name}', " +
$"[plan] = '{(int)package.Plan}', " +
$"[price] = '{package.Price}', " +
$"[Deleted]='{package.Deleted}' " +
$"WHERE [guid] = '{package.Key}'";
return(DbInfo.ExecuteNonQuery(query) > -1);
}
public bool SaveQuestionaire(Questionaire questionaire)
{
try
{
var exists = RecordExists("dsto_questionaire", questionaire.Key);
string query = string.Empty;
if (!exists)
{
if (string.IsNullOrEmpty(questionaire.ConfigurationId))
{
query = $"insert into dsto_questionaire([guid],[Name],[created_by],[latitude],[longitude],[status],[yref_category],[yref_region],[yref_template],[code]) values('{questionaire.Key}','{questionaire.Name}','{questionaire.CreatedBy}','{questionaire.Latitude}','{questionaire.Longitude}','{(int)questionaire.Status}'," +
$"'{(questionaire.Category.OID == 0 ? DBNull.Value : questionaire.Category.Key)}','{((questionaire.Region.OID != 0) ? questionaire.Region.Key : DBNull.Value)}','{questionaire.Template}', '{(questionaire.Region.OID == 0 ? DBNull.Value : LatestCode(questionaire.Template) + 1)}')";
}
else if (!string.IsNullOrEmpty(questionaire.Template))
{
query = $"insert into dsto_questionaire([guid],[Name],[created_by],[configuration_id],[latitude],[longitude],[status],[yref_template]) values('{questionaire.Key}','{questionaire.Name}','{questionaire.CreatedBy}','{questionaire.ConfigurationId}','{questionaire.Latitude}','{questionaire.Longitude}','{(int)questionaire.Status}','{questionaire.Template}')";
}
else
{
query = $"insert into dsto_questionaire([guid],[Name],[created_by],[configuration_id],[latitude],[longitude],[status]) values('{questionaire.Key}','{questionaire.Name}','{questionaire.CreatedBy}','{questionaire.ConfigurationId}','{questionaire.Latitude}','{questionaire.Longitude}','{(int)questionaire.Status}')";
}
}
else
{
if (!string.IsNullOrEmpty(questionaire.Template))
{
query = $"UPDATE dsto_questionaire SET " +
$"[Name]='{questionaire.Name}', " +
$"[latitude]='{questionaire.Latitude}', " +
$"[longitude]='{questionaire.Longitude}', " +
$"[status]='{(int)questionaire.Status}', " +
$"[yref_template]='{questionaire.Template}', " +
$"[Deleted]='{questionaire.Deleted}' " +
$"WHERE [guid] = '{questionaire.Key}'";
}
else
{
query = $"UPDATE dsto_questionaire SET " +
$"[Name]='{questionaire.Name}', " +
$"[latitude]='{questionaire.Latitude}', " +
$"[longitude]='{questionaire.Longitude}', " +
$"[status]='{(int)questionaire.Status}', " +
$"[Deleted]='{questionaire.Deleted}' " +
$"WHERE [guid] = '{questionaire.Key}'";
}
}
return(DbInfo.ExecuteNonQuery(query) > -1);
}
catch (Exception ex)
{
throw ex;
}
}
private bool Insert(Package package)
{
try
{
string query = $"INSERT INTO dsto_package ([guid],[created_by],[name],[plan],[price]) values('{package.Key}','Admin','{package.Name}',{(int)package.Plan},'{package.Price}')";
return(DbInfo.ExecuteNonQuery(query) > 0);
}
catch (Exception ex)
{
throw ex;
}
}
public bool DeleteQuestionaire(string guid)
{
Questionaire questionaire = GetQuestionaire(guid);
string query = $"delete from dsto_questionaire where [guid]='{guid}'";
foreach (var s in questionaire.Sections)
{
new SectionProvider(DbInfo).DeleteSection(s.Key);
}
var rows = DbInfo.ExecuteNonQuery(query);
return(rows > -1);
}
public bool Delete(int id)
{
string query = $"delete from dsto_client where [OID] = {id}";
Client client = GetClient(id);
foreach (var s in client.Users)
{
new UserProvider(DbInfo).DeleteUser(s.OID);
}
var rows = DbInfo.ExecuteNonQuery(query);
return(rows > 0);
}
public bool Delete(int id)
{
string query = $"delete from dsto_Certification where [OID] = {id}";
Certification certification = GetCertification(id);
foreach (var s in certification.Sections)
{
new SectionProvider(DbInfo).DeleteSection(s.Key);
}
var rows = DbInfo.ExecuteNonQuery(query);
return(rows > 0);
}
private bool Edit(Billing billing)
{
string query = $"UPDATE dsto_Billing SET " +
$"[Bill]='{billing.Bill}', " +
$"[BillingDate] = '{billing.BillingDate.ToString("yyyy-MM-dd HH:mm:ss.fff")}', " +
$"[InvoiceNo] = '{billing.InvoiceNo}', " +
$"[yref_package] = '{billing.Package.Key}', " +
$"[paymentstatus] = '{(int)PaymentStatus.Paid}', " +
$"[clientid] = '{billing.Client.OID}' , " +
$"[Deleted] = '{billing.Deleted}', " +
$"WHERE [guid] = '{billing.Key}'";
return(DbInfo.ExecuteNonQuery(query) > -1);
}
public bool DeleteEnumList(string key)
{
string query = $"delete from dsto_EnumLists where [guid]='{key}'";
EnumList enumList = GetEnumList(key);
foreach (var enumValue in enumList.EnumValues)
{
new EnumListValueProvider(DbInfo).DeleteEnumValue(enumValue.Key);
}
var rows = DbInfo.ExecuteNonQuery(query);
return(rows > -1);
}
public override bool Save(DCAnalyticsObject obj)
{
try
{
Training training = obj as Training;
var query = string.Empty;
var exists = RecordExists("dsto_Training", training.Key);
if (!exists)
{
query = $"insert into dsto_Training([guid],Name,[created_by],[startdate],[enddate],[configuration_id]) values('{training.Key}','{training.Name}','Admin','{training.StartDate.ToString("yyyy-MM-ddThh:mm:ss")}','{training.EndDate.ToString("yyyy-MM-ddThh:mm:ss")}',{training.ConfigurationId})";
}
else
{
//update
query = $"UPDATE dsto_Training SET [Name]='{training.Name}', " +
$"[startdate] = '{training.StartDate.ToString("yyyy-MM-ddThh:mm:ss")}', " +
$"[enddate] = '{training.EndDate.ToString("yyyy-MM-ddThh:mm:ss")}', " +
$"[Deleted]='{training.Deleted}' " +
$"WHERE [guid] = '{training.Key}'";
}
if (DbInfo.ExecuteNonQuery(query) > -1)
{
foreach (var topic in training.Topics)
{
topic.TrainingId = training.Key;
new TopicProvider(DbInfo).Save(topic);
}
foreach (var trainee in training.Trainees)
{
trainee.TrainingId = training.Key;
new TraineeProvider(DbInfo).Save(trainee);
}
foreach (var trainer in training.Trainers)
{
trainer.TrainingId = training.Key;
new TrainerProvider(DbInfo).Save(trainer);
}
}
return(true);
}
catch (Exception ex)
{
throw ex;
}
}
public bool DeleteSubSection(string key)
{
string query = $"delete from dsto_subsections where [guid] = {key}";
SubSection subSection = GetSubSection(key);
string[] guids = new string[subSection.Questions.Count];
//int i = 0;
foreach (var s in subSection.Questions)
{
//guids[i] = s.Key;
//i++;
new QuestionProvider(DbInfo).DeleteQuestion(s.Key);
}
var rows = DbInfo.ExecuteNonQuery(query);
return(rows > 0);
}
private bool Insert(Billing billing)
{
try
{
string query = $"INSERT INTO dsto_billing ([guid],created_by,[bill],[billingdate],[yref_package],[invoiceno],[clientid]) values('{billing.Key}','Admin','{billing.Package.Price}', '{billing.BillingDate.ToString("yyyy-MM-dd HH:mm:ss.fff")}','{billing.Package.Key}','{billing.InvoiceNo}','{billing.Client.OID}')";
if (DbInfo.ExecuteNonQuery(query) > 0)
{
//MailService.SendMail(billing.Client.Email, true, "Client Invoice", "Hello client, your account has been invoinced. <br> You're therefore required to make payment to avoid any inconveniences", "#");
return(true);
}
return(false);
}
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 DeleteSection(string key)
{
string query = $"delete from dsto_sections where [guid]='{key}'";
Section section = GetSection(key);
foreach (var s in section.Questions)
{
new QuestionProvider(DbInfo).DeleteQuestion(s.Key);
}
foreach (var s in section.SubSections)
{
new SubSectionProvider(DbInfo).DeleteSubSection(section.Key);
}
var rows = DbInfo.ExecuteNonQuery(query);
return(rows > -1);
}
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);
}
public bool Delete(string reference, params string[] oids)
{
string filter = string.Empty;
int i = 0;
foreach (var s in oids)
{
filter += $"'{s}'";
if (i < oids.Length - 1)
{
filter += ",";
}
i++;
}
string query = $"delete from dsto_subsections where [guid] IN ({filter}) AND (yref_questionaire = '{reference}' or yref_section = '{reference}')";
var rows = DbInfo.ExecuteNonQuery(query);
return(rows > 0);
}