internal SubSections GetSubSections(Section section, string response_id)
{
try
{
QuestionProvider provider = new QuestionProvider(DbInfo);
string query = $"select * from dsto_subsections where yref_section='{section.Key}' and deleted=0";
var table = DbInfo.ExecuteSelectQuery(query);
if (table.Rows.Count > 0)
{
DataRow row = table.Rows[0];
SubSection subsection = section.SubSections.Add();
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 = provider.GetQuestions(subsection, response_id);
}
return(section.SubSections);
}
catch (Exception ex)
{
throw ex;
}
}
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);
}
internal Sections GetSections(string reference, string response_id = null)
{
try
{
QuestionProvider provider = new QuestionProvider(DbInfo);
Sections sections = new Sections();
string query = $"select * from dsto_sections where (yref_questionaire='{reference}' or yref_field_inspection='{reference}' or yref_certification='{reference}' or yref_template='{reference}') and deleted=0 order by oid asc";
var table = DbInfo.ExecuteSelectQuery(query);
if (table.Rows.Count > 0)
{
foreach (DataRow row in table.Rows)
{
Section section = new Section(null);
section.Key = row["guid"].ToString();
section.OID = int.Parse(row["OID"].ToString());
section.Name = row["Name"].ToString();
section.Description = row["Description"].ToString();
section.Deleted = bool.Parse(row["deleted"].ToString());
section.CreatedBy = row["created_by"].ToString();
section.Questions = provider.GetQuestions(section, response_id);
section.SubSections = new SubSectionProvider(DbInfo).GetSubSections(section, response_id);
sections.Add(section);
}
return(sections);
}
return(null);
}
catch (Exception ex)
{
throw ex;
}
}
public override bool Save(DCAnalyticsObject obj)
{
QuestionProvider qnProvider = new QuestionProvider(DbInfo);
SubSectionProvider sbProvider = new SubSectionProvider(DbInfo);
Section section = obj as Section;
var complete = section.IsCompleted ? 1 : 0;
var exists = RecordExists("dsto_sections", section.Key);
string query = string.Empty;
if (!exists)
{
if (!string.IsNullOrEmpty(section.QuestionaireKey))
{
query = $"insert into dsto_sections([guid],[Name],[Description],[created_by],[isCompleted],[yref_questionaire]) values('{section.Key}','{section.Name}','{section.Description}','Admin',{complete},'{section.QuestionaireKey}')";
}
else if (!string.IsNullOrEmpty(section.CertificationKey))
{
query = $"insert into dsto_sections([guid],[Name],[Description],[created_by],[isCompleted],[yref_certification]) values('{section.Key}','{section.Name}','{section.Description}','Admin',{complete},'{section.CertificationKey}')";
}
else if (!string.IsNullOrEmpty(section.InspectionKey))
{
query = $"insert into dsto_sections([guid],[Name],[Description],[created_by],[isCompleted],[yref_field_inspection]) values('{section.Key}','{section.Name}','{section.Description}','Admin',{complete},'{section.InspectionKey}')";
}
else if (!string.IsNullOrEmpty(section.TemplateKey))
{
query = $"insert into dsto_sections([guid],[Name],[Description],[created_by],[isCompleted],[yref_template]) values('{section.Key}','{section.Name}','{section.Description}','Admin',{complete},'{section.TemplateKey}')";
}
}
else
{
//update
query = $"UPDATE dsto_sections SET [Name]='{section.Name}', " +
$"[Description]='{section.Description}', " +
$"[iscompleted]='{complete}', " +
$"[Deleted]='{section.Deleted}' " +
$"WHERE [guid] = '{section.Key}'";
}
if (DbInfo.ExecuteNonQuery(query) > -1)
{
//save subsections
foreach (var sb in section.SubSections)
{
sb.SectionKey = section.Key;
sbProvider.Save(sb);
}
//save questions
foreach (var qn in section.Questions)
{
qn.SectionKey = section.Key;
qnProvider.Save(qn);
}
return(true);
}
return(false);
}
