public User VerifyUser(User user)
{
string query = $"select * from dsto_user where email='{user.Email}' and usercode='{user.Usercode}'";
var table = DbInfo.ExecuteSelectQuery(query);
if (table.Rows.Count > 0 && table.Rows.Count == 1)
{
DataRow row = table.Rows[0];
InitUser(user, row);
return(user);
}
return(null);
}
public EnumList GetEnumList(string id)
{
EnumList enumList = new EnumList(null);
string query = $"select * from dsto_EnumLists where [guid]='{id}'";
var table = DbInfo.ExecuteSelectQuery(query);
if (table.Rows.Count > 0)
{
DataRow row = table.Rows[0];
InitEnumList(enumList, row);
}
return(enumList);
}
public Training LastEntry(string key)
{
string query = $"select * from dsto_Training where guid = '{key}'";
var table = DbInfo.ExecuteSelectQuery(query);
if (table.Rows.Count > 0 && table.Rows.Count == 1)
{
System.Data.DataRow row = table.Rows[0];
DCAnalytics.Training training = new Training(null);
InitTraining(training, row);
return(training);
}
return(null);
}
public UserPermission GetUserPermissionByUserRightId(string userRightId)
{
string query = $"select * from dsto_userpermission where [yref_userRight] = {userRightId}";
var table = DbInfo.ExecuteSelectQuery(query);
if (table.Rows.Count > 0 && table.Rows.Count == 1)
{
DataRow row = table.Rows[0];
UserPermission userPermission = new UserPermissions(null).Add();
InitUserPermission(userPermission, row);
return(userPermission);
}
return(null);
}
public Dependency GetDependency(string id)
{
string query = $"select * from dsto_dependency where [guid] = '{id}'";
var table = DbInfo.ExecuteSelectQuery(query);
if (table.Rows.Count > 0 && table.Rows.Count == 1)
{
System.Data.DataRow row = table.Rows[0];
Dependency dependency = new Dependency(null);
InitDependency(dependency, row);
return(dependency);
}
return(null);
}
public DCAnalytics.Trainer GetTrainer(int id)
{
string query = $"select * from dsto_Trainer where OID = {id}";
var table = DbInfo.ExecuteSelectQuery(query);
if (table.Rows.Count > 0 && table.Rows.Count == 1)
{
System.Data.DataRow row = table.Rows[0];
Trainer trainer = new Trainer(null);
InitTrainer(trainer, row);
return(trainer);
}
return(null);
}
public User GetUser(int id)
{
string query = $"select * from dsto_user where OID = {id}";
var table = DbInfo.ExecuteSelectQuery(query);
if (table.Rows.Count > 0 && table.Rows.Count == 1)
{
DataRow row = table.Rows[0];
User user = new User();
InitUser(user, table.Rows[0]);
return(user);
}
return(null);
}
public DCAnalytics.Topic GetTopic(int id)
{
string query = $"select * from dsto_Topic where OID = {id}";
var table = DbInfo.ExecuteSelectQuery(query);
if (table.Rows.Count > 0 && table.Rows.Count == 1)
{
System.Data.DataRow row = table.Rows[0];
DCAnalytics.Topic topic = new Topic(null);
InitTopic(topic, row);
return(topic);
}
return(null);
}
public Configuration GetConfiguration(string key)
{
string query = $"select * from dsto_configuration where guid = '{key}'";
var table = DbInfo.ExecuteSelectQuery(query);
if (table.Rows.Count > 0 && table.Rows.Count == 1)
{
DataRow row = table.Rows[0];
Configuration config = new Configuration();
InitConfig(config, row);
return(config);
}
return(null);
}
public User GetUser(string email)
{
string query = $"select * from dsto_user where email = '{email}'";
var table = DbInfo.ExecuteSelectQuery(query);
if (table.Rows.Count > 0 && table.Rows.Count == 1)
{
DataRow row = table.Rows[0];
User user = new User();
InitUser(user, row);
user.Usercode = new Random().Next(100000).ToString();
return(user);
}
return(null);
}
public Purchase GetPurchase(int id)
{
string query = $"select * from dsto_Purchase where OID = {id}";
var table = DbInfo.ExecuteSelectQuery(query);
if (table.Rows.Count > 0 && table.Rows.Count == 1)
{
System.Data.DataRow row = table.Rows[0];
Purchase purchase = new Purchase();
InitPurchases(purchase, row);
return(purchase);
}
return(null);
}
public Dependencies GetDependencyByQuestionId(Question question, Answers answers = null, string response_id = null)
{
Dependencies dependencies = new Dependencies(null);
try
{
if (answers.Count > 0)
{
if (RecordExists("dsto_dependency", "yref_question", question.Key))
{
if (!string.IsNullOrEmpty(answers.FirstOrDefault().AnswerText) && int.TryParse(answers.FirstOrDefault().AnswerText, out int n) && question.DataType.Equals(DataTypes.Numeric))
{
for (int occurance = 0; occurance < int.Parse(answers.FirstOrDefault().AnswerText); occurance++)
{
string query = $"select * from dsto_dependency where [yref_question] = '{question.Key}' and Deleted=0";
var table = DbInfo.ExecuteSelectQuery(query);
if (table.Rows.Count > 0)
{
foreach (DataRow row in table.Rows)
{
Dependency dependency = dependencies.Add();
InitDependency(dependency, row, occurance, response_id);
}
}
}
}
}
}
else
{
string query = $"select * from dsto_dependency where [yref_question] = '{question.Key}' and Deleted=0";
var table = DbInfo.ExecuteSelectQuery(query);
if (table.Rows.Count > 0)
{
foreach (DataRow row in table.Rows)
{
Dependency dependency = dependencies.Add();
InitDependency(dependency, row);
}
}
}
}
catch (Exception ex)
{
throw ex;
}
return(dependencies);
}
public Questionaire GetQuestionaire(int id)
{
string query = $"select * from dsto_questionaire where OID = {id}";
var table = DbInfo.ExecuteSelectQuery(query);
if (table.Rows.Count > 0 && table.Rows.Count == 1)
{
DataRow row = table.Rows[0];
Questionaire questionaire = new Questionaire(null);
InitQuestionaire(questionaire, row);
var sectionProvider = new SectionProvider(DbInfo);
return(questionaire);
}
return(null);
}
public Reports GetReports(string response_id)
{
try
{
Reports reports = new Reports();
string query = $"select dsto_questionaire.Longitude as questionaire_lng, dsto_questionaire.Latitude as questionaire_lat, " +
$"dsto_questionaire.deleted as questionaire_deleted, dsto_questionaire.Status as quesitonaire_status, " +
$"dsto_questionaire.Name as questionaire_name, dsto_questionaire.OID as questionaire_oid, " +
$"dsto_questionaire.guid as questionaire_guid, dsto_Certification.guid as certification_key, " +
$"dsto_Certification.created_by as certification_owner, dsto_Certification.OID as certification_oid, " +
$"dsto_Certification.deleted as certification_deleted, dsto_Certification.status as certification_status, dsto_Certification.CertificationType as certificationType, " +
$"dsto_Certification.name as certification_name, dsto_Certification.yref_template as certification_template " +
$"from dsto_Certification inner join dsto_Questionaire on dsto_Certification.farmerid = dsto_Questionaire.guid " +
$"where dsto_questionaire.yref_template='{response_id}'";;
var table = DbInfo.ExecuteSelectQuery(query);
if (table.Rows.Count > 0)
{
foreach (DataRow row in table.Rows)
{
Report report = reports.Add();
report.Questionaire = new Questionaire(null);
report.Questionaire.Key = row["questionaire_guid"].ToString();
report.Questionaire.OID = int.Parse(row["questionaire_oid"].ToString());
report.Questionaire.Name = new SectionProvider(DbInfo).QuestionaireIdentification(response_id, report.Questionaire.Key);
report.Questionaire.Status = (Statuses)Enum.Parse(typeof(Statuses), row["quesitonaire_status"].ToString());
report.Questionaire.Deleted = bool.Parse(row["questionaire_deleted"].ToString());
report.Questionaire.Latitude = Convert.ToDouble(row["questionaire_lat"].ToString());
report.Questionaire.Longitude = Convert.ToDouble(row["questionaire_lng"].ToString());
report.Certification = new Certifications(null).Add((CertificationTypes)Enum.Parse(typeof(CertificationTypes), row["certificationType"].ToString()));
report.Certification.Key = row["certification_key"].ToString();
report.Certification.CreatedBy = new UserProvider(DbInfo).GetCreatedByUser(row["certification_owner"].ToString());
report.Certification.OID = int.Parse(row["certification_oid"].ToString());
report.Certification.Deleted = bool.Parse(row["certification_deleted"].ToString());
report.Certification.Status = (Statuses)Enum.Parse(typeof(Statuses), row["certification_status"].ToString());
report.Certification.Name = row["certification_name"].ToString();
report.Certification.Template = row["certification_template"].ToString();
report.Certification.Sections = new SectionProvider(DbInfo).GetSections(report.Certification.Template, report.Certification.Key);
}
}
return(reports);
}
catch (Exception ex)
{
throw ex;
}
}
public Questionaire GetQuestionaire(string guid)
{
string query = $"select * from dsto_questionaire where [guid] = '{guid}'";
var table = DbInfo.ExecuteSelectQuery(query);
if (table.Rows.Count > 0 && table.Rows.Count == 1)
{
DataRow row = table.Rows[0];
Questionaire questionaire = new Questionaire(null);
InitQuestionaire(questionaire, row);
var sectionProvider = new SectionProvider(DbInfo);
questionaire.Sections = sectionProvider.GetSections(questionaire.Key);
return(questionaire);
}
return(null);
}
internal long LatestCode(string yref_template)
{
try
{
string query = $"select RIGHT('000000' + CAST(MAX(code) AS varchar(6)) , 6) as code from dsto_questionaire where yref_template='{yref_template}'";
var cols = DbInfo.ExecuteSelectQuery(query);
if (cols.Rows.Count == 1)
{
return(Convert.ToInt64(cols.Rows[0]["code"].ToString()));
}
}
catch (Exception ex)
{
}
return(0);
}
public SkipConditions GetConditions(Question question)
{
SkipConditions conditions = new SkipConditions(null);
try
{
string query = $"select * from dsto_skipcondition where [yref_question]='{question.Key}' and Deleted=0 order by oid asc";
var table = DbInfo.ExecuteSelectQuery(query);
if (table.Rows.Count > 0)
{
foreach (DataRow row in table.Rows)
{
SkipCondition condition = new SkipCondition(null);
condition.Key = row["guid"].ToString();
condition.OID = int.Parse(row["OID"].ToString());
condition.Deleted = bool.Parse(row["deleted"].ToString());
condition.Answer = new EnumListValueProvider(DbInfo).GetEnumList(row["answer"].ToString());
condition.DataCollectionObectType = (row["dataCollectionObectType"] != DBNull.Value) ? (DataCollectionObectTypes)Enum.Parse(typeof(DataCollectionObectTypes), row["dataCollectionObectType"].ToString()) : DataCollectionObectTypes.None;
switch (condition.DataCollectionObectType)
{
case DataCollectionObectTypes.Section:
condition.Target.Section = new SectionProvider(DbInfo).GetTargetSection(row["yref_target"].ToString());
break;
case DataCollectionObectTypes.SubSection:
condition.Target.SubSection = new SubSectionProvider(DbInfo).GetSubSection(row["yref_target"].ToString());
break;
case DataCollectionObectTypes.Question:
condition.Target.Question = new QuestionProvider(DbInfo).GetQuestion(row["yref_target"].ToString());
break;
}
condition.AttributeKey = row["yref_attribute"].ToString();
conditions.Add(condition);
}
}
}
catch (Exception ex)
{
throw ex;
}
return(conditions);
}
public DCAnalytics.Certification GetCertification(string id, string template_id = null)
{
string query = $"select * from dsto_Certification where [guid] = '{id}'";
var table = DbInfo.ExecuteSelectQuery(query);
if (table.Rows.Count > 0 && table.Rows.Count == 1)
{
DataRow row = table.Rows[0];
var certificationType = (CertificationTypes)Convert.ToInt32(row["CertificationType"]);
Certification certification = ObjectFactory.CreateCertification(certificationType, null);
InitCertification(certification, row);
certification.Sections = new SectionProvider(DbInfo).GetSections(certification.Key, template_id);
return(certification);
}
return(null);
}
public DCAnalytics.Training GetTraining(string id)
{
string query = $"select * from dsto_Training where guid = '{id}'";
var table = DbInfo.ExecuteSelectQuery(query);
if (table.Rows.Count > 0 && table.Rows.Count == 1)
{
System.Data.DataRow row = table.Rows[0];
DCAnalytics.Training training = new Training(null);
InitTraining(training, row);
training.Trainers = new TrainerProvider(DbInfo).GetTrainers(training.Key);
training.Trainees = new TraineeProvider(DbInfo).GetTrainees(training.Key);
training.Topics = new TopicProvider(DbInfo).GetTopics(training.Key);
return(training);
}
return(null);
}
internal string QuestionaireIdentification(string reference, string response_id)
{
try
{
string query = $"select top 1 * from dsto_sections where yref_questionaire='{reference}' order by OID asc";
var table = DbInfo.ExecuteSelectQuery(query);
if (table.Rows.Count > 0)
{
return(new QuestionProvider(DbInfo).QuestionaireIndentification(table.Rows[0]["guid"].ToString(), response_id));
}
return(null);
}
catch (Exception ex)
{
throw ex;
}
}
public Section GetTargetSection(string key)
{
string query = $"select * from dsto_sections where guid = '{key}'";
var table = DbInfo.ExecuteSelectQuery(query);
if (table.Rows.Count > 0 && table.Rows.Count == 1)
{
DataRow row = table.Rows[0];
Section section = new Section(null);
section.Key = row["guid"].ToString();
section.OID = int.Parse(row["OID"].ToString());
section.Name = row["Name"].ToString();
section.Deleted = bool.Parse(row["deleted"].ToString());
section.CreatedBy = row["created_by"].ToString();
return(section);
}
return(null);
}
public EnumListValue GetEnumList(string id)
{
try
{
string query = $"select * from dsto_EnumListValues where guid = '{id}'";
var table = DbInfo.ExecuteSelectQuery(query);
if (table.Rows.Count > 0 && table.Rows.Count == 1)
{
System.Data.DataRow row = table.Rows[0];
EnumListValue enumListValue = new EnumListValue(null);
InitEnumListValue(enumListValue, row);
return(enumListValue);
}
}
catch (Exception ex)
{
throw ex;
}
return(null);
}
public UserRight UserRight(string permission_id)
{
try
{
string query = $"select * from dsto_userright where yref_permission='{permission_id}'";
var table = DbInfo.ExecuteSelectQuery(query);
if (table.Rows.Count > 0)
{
UserRight userRight = new UserRights(null).Add();
InitUserRight(userRight, table.Rows[0]);
return(userRight);
}
}
catch (Exception ex)
{
throw ex;
}
return(null);
}
public DCAnalytics.SubSection GetSubSection(string key, int occurance = 0, string response_id = null)
{
string query = $"select * from dsto_subsections where [guid] = '{key}' and deleted=0";
var table = DbInfo.ExecuteSelectQuery(query);
if (table.Rows.Count > 0 && table.Rows.Count == 1)
{
DataRow row = table.Rows[0];
SubSection subsection = new SubSection(null);
subsection.Key = row["guid"].ToString();
subsection.OID = int.Parse(row["OID"].ToString());
subsection.Name = row["Name"].ToString();
subsection.Deleted = bool.Parse(row["deleted"].ToString());
subsection.CreatedBy = row["created_by"].ToString();
subsection.Questions = new QuestionProvider(DbInfo).GetQuestions(subsection, response_id, occurance);
return(subsection);
}
return(null);
}
public User AuthoriseUser(Credentials credentials)
{
User user = new User();
try
{
string query = $"select * from dsto_user where username='******' and password='******'";
var table = DbInfo.ExecuteSelectQuery(query);
if (table.Rows.Count > 0 && table.Rows.Count == 1)
{
DataRow row = table.Rows[0];
InitUser(user, row);
}
}
catch (Exception ex)
{
throw ex;
}
return(user);
}
public Section GetSection(string id, string response_id = null)
{
string query = $"select * from dsto_sections where OID = '{id}'";
var table = DbInfo.ExecuteSelectQuery(query);
if (table.Rows.Count > 0 && table.Rows.Count == 1)
{
DataRow row = table.Rows[0];
Section section = new Section(null);
section.Key = row["guid"].ToString();
section.OID = int.Parse(row["OID"].ToString());
section.Name = row["Name"].ToString();
section.Deleted = bool.Parse(row["deleted"].ToString());
section.CreatedBy = row["created_by"].ToString();
section.Questions = new QuestionProvider(DbInfo).GetQuestions(section, response_id);
section.SubSections = new SubSectionProvider(DbInfo).GetSubSections(section, response_id);
return(section);
}
return(null);
}
internal Question GetQuestion(string target)
{
try
{
Questions _questions = new Questions();
string query = $"select * from dsto_questions where [guid] ='{target}' and Deleted=0";
var table = DbInfo.ExecuteSelectQuery(query);
if (table.Rows.Count > 0)
{
Question question = _questions.Add((QuestionTypes)Enum.Parse(typeof(QuestionTypes), table.Rows[0]["question_type"].ToString()));
InitQuestion(table.Rows[0], question);
return(question);
}
return(null);
}
catch (Exception ex)
{
throw ex;
}
}
public Template RetrieveTemplate(string id)
{
Template template = null;
try
{
string query = $"select * from dsto_template where [oid]='{id}'";
var table = DbInfo.ExecuteSelectQuery(query);
if (table.Rows.Count > 0)
{
DataRow row = table.Rows[0];
template = new Template();
SetTemplate(template, row);
}
}
catch (Exception ex)
{
throw ex;
}
return(template);
}
internal string QuestionaireIndentification(string _sectionKey, string response_id)
{
string query = $"select * from dsto_questions where [questiontext]='name' and [yref_section] ='{_sectionKey}' order by oid asc";
var table = DbInfo.ExecuteSelectQuery(query);
if (table.Rows.Count == 1)
{
Question question = new Questions().Add((QuestionTypes)Enum.Parse(typeof(QuestionTypes), table.Rows[0]["question_type"].ToString()));
InitQuestion(table.Rows[0], question, response_id);
if (question.Answers.Count > 0)
{
if (question.Answers.FirstOrDefault() != null)
{
return(question.Answers.FirstOrDefault().AnswerText);
}
}
}
return(null);
}
public Package RetrievePackage(string key)
{
string query = string.Format("select * from dsto_package where [guid]='{0}'", key);
var table = DbInfo.ExecuteSelectQuery(query);
if (table.Rows.Count == 1)
{
DataRow row = table.Rows[0];
Package package = new Package();
package.Key = row["guid"].ToString();
package.CreatedBy = row["created_by"].ToString();
package.Deleted = bool.Parse(row["deleted"].ToString());
package.OID = int.Parse(row["OID"].ToString());
package.Name = row["name"].ToString();
package.Plan = (Plan)Enum.Parse(typeof(Plan), row["plan"].ToString());
package.Price = decimal.Parse(row["price"].ToString());
return(package);
}
return(null);
}