public static string GetCompleteQuestPartsRelatedToTaskQry(string taskID)
{
return("SELECT QuestTaskParts.PartIDs, QuestTaskPartsCompleteQuest.QuestNames, Quests.QuestDescriptions FROM QuestTaskPartsCompleteQuest "
+ "INNER JOIN QuestTaskParts ON QuestTaskParts.PartIDs = QuestTaskPartsCompleteQuest.PartIDs "
+ "INNER JOIN Quests ON Quests.QuestNames = QuestTaskPartsCompleteQuest.QuestNames "
+ "WHERE TaskIDs = " + DbCommands.GetParameterNameFromValue(taskID) + ";");
}
/// <summary>
/// A left join is used to check if the grammar rule is related to a particular translation in the
/// vocab rule list. By default the primary key values for the rule list are set to null, and the relation
/// will be set to 0 if for all of the grammar rules unless these args are entered.
/// </summary>
public static string GetGrammarRuleDisplayQry(string englishStr = null, string welshStr = null)
{
string englishStrQry = "''";
string welshStrQry = "''";
if (englishStr != null)
{
englishStrQry = DbCommands.GetParameterNameFromValue(englishStr);
englishStrQry = (englishStrQry == null) ? "''" : englishStrQry;
}
if (welshStr != null)
{
welshStrQry = DbCommands.GetParameterNameFromValue(welshStr);
welshStrQry = (welshStrQry == null) ? "''" : welshStrQry;
}
string sql = "SELECT VocabGrammar.RuleIDs, VocabGrammar.ShortDescriptions, " +
"CASE WHEN VocabGrammar.RuleIDs = VocabRuleList.RuleIDs THEN 1 ELSE 0 END TranslationRules " +
"FROM VocabGrammar " +
"LEFT JOIN VocabRuleList ON VocabGrammar.RuleIDs = VocabRuleList.RuleIDs " +
"AND VocabRuleList.EnglishText = " + englishStrQry + " AND VocabRuleList.WelshText = " + welshStrQry + " " +
"ORDER BY VocabGrammar.RuleIDs ASC;";
Debug.Log(sql);
return(sql);
}
public static string GetDiscoveredGrammarRelatedToVocab(string englishStr = null, string welshStr = null)
{
string englishStrQry = "''";
string welshStrQry = "''";
if (englishStr != null)
{
englishStrQry = DbCommands.GetParameterNameFromValue(englishStr);
englishStrQry = (englishStrQry == null) ? "''" : englishStrQry;
}
if (welshStr != null)
{
welshStrQry = DbCommands.GetParameterNameFromValue(welshStr);
welshStrQry = (welshStrQry == null) ? "''" : welshStrQry;
}
string sql = "SELECT VocabGrammar.RuleIDs, VocabGrammar.ShortDescriptions " +
"FROM VocabGrammar " +
"INNER JOIN DiscoveredVocabGrammar ON VocabGrammar.RuleIDs = DiscoveredVocabGrammar.RuleIDs " +
"AND DiscoveredVocabGrammar.SaveIDs = 0 " +
"INNER JOIN VocabRuleList ON VocabGrammar.RuleIDs = VocabRuleList.RuleIDs " +
"AND VocabRuleList.EnglishText = " + englishStrQry + " AND VocabRuleList.WelshText = " + welshStrQry + " " +
"ORDER BY VocabGrammar.RuleIDs ASC;";
Debug.Log(sql);
return(sql);
}
public static string GetPrefabPartsRelatedToTask(string taskID)
{
return("SELECT QuestTaskParts.PartIDs, QuestTaskPartsPrefab.PrefabLabel "
+ "FROM QuestTaskParts "
+ "INNER JOIN QuestTaskPartsPrefab ON QuestTaskPartsPrefab.PartIDs = QuestTaskParts.PartIDs "
+ "WHERE TaskIDs = " + DbCommands.GetParameterNameFromValue(taskID) + ";");
}
public static string GetEquipItemPartsRelatedToTask(string taskID)
{
return("SELECT QuestTaskParts.PartIDs, QuestTaskPartsEquipItem.ItemNames "
+ "FROM QuestTaskParts "
+ "INNER JOIN QuestTaskPartsEquipItem ON QuestTaskPartsEquipItem.PartIDs = QuestTaskParts.PartIDs "
+ "WHERE TaskIDs = " + DbCommands.GetParameterNameFromValue(taskID) + ";");
}
public static string GetTaggedVocabDisplayQry(string englishStr = null, string welshStr = null)
{
string englishStrQry = "''";
string welshStrQry = "''";
if (englishStr != null)
{
englishStrQry = DbCommands.GetParameterNameFromValue(englishStr);
englishStrQry = (englishStrQry == null) ? "''" : englishStrQry;
}
if (welshStr != null)
{
welshStrQry = DbCommands.GetParameterNameFromValue(welshStr);
welshStrQry = (welshStrQry == null) ? "''" : welshStrQry;
}
string sql = "SELECT TranslationTags.Tags, " +
"CASE WHEN TranslationTags.Tags = VocabTagged.Tags THEN 1 ELSE 0 END TranslationRules " +
"FROM TranslationTags " +
"LEFT JOIN VocabTagged ON TranslationTags.Tags = VocabTagged.Tags " +
"AND VocabTagged.EnglishText = " + englishStrQry + " " +
"AND VocabTagged.WelshText = " + welshStrQry + " " +
"ORDER BY TranslationTags.Tags ASC;";
Debug.Log(sql);
return(sql);
}
public static string GetTasksCompleteFromQuestName(string questName, string saveID)
{
return("SELECT COUNT(*) "
+ "FROM QuestTasksActivated "
+ "INNER JOIN QuestTasks ON QuestTasksActivated.TaskIDs = QuestTasks.TaskIDs "
+ "WHERE QuestTasks.QuestNames = " + DbCommands.GetParameterNameFromValue(questName)
+ " AND QuestTasksActivated.SaveIDs = " + saveID);
}
public static string GetGrammarRelatedToVocab(string EnglishTxt, string WelshTxt)
{
return("SELECT VocabGrammar.ShortDescriptions, VocabGrammar.LongDescriptions "
+ "FROM VocabGrammar "
+ "INNER JOIN VocabRuleList ON VocabGrammar.RuleIDs = VocabRuleList.RuleIDs "
+ "WHERE VocabRuleList.EnglishText = " + DbCommands.GetParameterNameFromValue(EnglishTxt)
+ " AND VocabRuleList.WelshText = " + DbCommands.GetParameterNameFromValue(WelshTxt)
+ " AND VocabGrammar.RuleIDs IN (SELECT DiscoveredVocabGrammar.RuleIDs FROM DiscoveredVocabGrammar WHERE DiscoveredVocabGrammar.SaveIDs = 0)");
}
public static string GetSavedWorldItemsQry(string saveID, string sceneName)
{
string sql = "SELECT LocationX, LocationY, LocationZ, ParentPath, ItemNames, PrefabPath, SaveIDs " +
"FROM SavedWorldItems " +
"WHERE SaveIDs = " + DbCommands.GetParameterNameFromValue(saveID) + " " +
"AND SceneNames = " + DbCommands.GetParameterNameFromValue(sceneName) + ";";
//Debug.Log(sql);
return(sql);
}
public static string GetEquipItemTaskPartCountFromActiveQuests(string itemName, string saveID)
{
return("SELECT COUNT(QuestTaskPartsEquipItem.PartIDs) "
+ "FROM QuestTaskPartsEquipItem "
+ "INNER JOIN QuestTaskParts ON QuestTaskPartsEquipItem.PartIDs = QuestTaskParts.PartIDs "
+ "LEFT JOIN QuestTasks ON QuestTaskParts.TaskIDs = QuestTasks.TaskIDs "
+ "LEFT JOIN QuestsActivated ON QuestTasks.QuestNames = QuestsActivated.QuestNames "
+ "WHERE QuestTaskPartsEquipItem.ItemNames = " + DbCommands.GetParameterNameFromValue(itemName)
+ " AND QuestsActivated.SaveIDs = " + saveID);
}
public static string GetActivateDialoguePartsRelatedToGameTaskQry(string taskID)
{
return("SELECT " +
"QuestTaskParts.PartIDs, " +
"CharacterDialogues.CharacterNames " +
"FROM QuestTaskPartsActivateDialogueNode "
+ "INNER JOIN QuestTaskParts ON QuestTaskPartsActivateDialogueNode.PartIDs = QuestTaskParts.PartIDs "
+ "INNER JOIN DialogueNodes ON QuestTaskPartsActivateDialogueNode.NodeIDs = DialogueNodes.NodeIDs "
+ "INNER JOIN CharacterDialogues ON DialogueNodes.DialogueIDs = CharacterDialogues.DialogueIDs "
+ "WHERE QuestTaskParts.TaskIDs = " + DbCommands.GetParameterNameFromValue(taskID) + ";");
}
public static string GetReadSkillAcquiredForVocabQry(string en, string cy)
{
string sql = "SELECT ProficiencyNames " +
"FROM AcquiredVocabReadSkills " +
"WHERE SaveIDs = 0 " +
"AND EnglishText = " + DbCommands.GetParameterNameFromValue(en) + " " +
"AND WelshText = " + DbCommands.GetParameterNameFromValue(cy);
Debug.Log(sql);
return(sql);
}
public static string GetActiveDialoguesWithCharacter(string charName)
{
string sql = "SELECT ActivatedDialogues.DialogueIDs FROM ActivatedDialogues " +
"INNER JOIN CharacterDialogues ON CharacterDialogues.DialogueIDs = ActivatedDialogues.DialogueIDs " +
"WHERE CharacterDialogues.CharacterNames = " + DbCommands.GetParameterNameFromValue(charName) + " " +
"AND ActivatedDialogues.Completed = 0 " +
"AND SaveIDs = 0;";
Debug.Log(sql);
return(sql);
}
public static string GetCompleteQuestTasksData(string questName, string saveID)
{
string sql = "SELECT DISTINCT QuestTaskPartsCompleteQuest.PartIDs, QuestTaskParts.TaskIDs "
+ "FROM QuestTaskPartsCompleteQuest "
+ "INNER JOIN QuestTaskParts ON QuestTaskPartsCompleteQuest.PartIDs = QuestTaskParts.PartIDs "
+ "INNER JOIN QuestTasksActivated ON QuestTaskParts.TaskIDs = QuestTasksActivated.TaskIDs "
+ "WHERE QuestTaskPartsCompleteQuest.QuestNames = " + DbCommands.GetParameterNameFromValue(questName)
+ " AND QuestsActivated.SaveIDs = " + saveID
+ " AND QuestTaskPartsCompleteQuest.PartIDs NOT IN (SELECT CompletedQuestTaskParts.PartIDs FROM CompletedQuestTaskParts WHERE CompletedQuestTaskParts.SaveIDs = 0)";
return(sql);
}
public static string GetVocabQry(string searchStr = "")
{
string qryStr = "SELECT EnglishText, WelshText FROM VocabTranslations";
if (searchStr != "")
{
string searchParam = DbCommands.GetParameterNameFromValue(searchStr);
string whereStr = " WHERE EnglishText LIKE " + searchParam + " OR WelshText LIKE " + searchParam;
qryStr += whereStr;
}
qryStr += ";";
return(qryStr);
}
public static string GetDialogueDisplayQry(string searchStr = "")
{
string qryStr = "SELECT Dialogues.DialogueIDs, Dialogues.DialogueDescriptions, ActivatedDialogues.SaveIDs "
+ "FROM Dialogues LEFT JOIN ActivatedDialogues ON Dialogues.DialogueIDs = ActivatedDialogues.DialogueIDs AND ActivatedDialogues.SaveIDS = -1";
if (searchStr != "")
{
string searchParam = DbCommands.GetParameterNameFromValue(searchStr);
string whereStr = " WHERE Dialogues.DialogueDescriptions LIKE " + searchParam;
qryStr += whereStr;
}
qryStr += " ORDER BY Dialogues.DialogueIDs ASC;";
return(qryStr);
}
public static string GetTaskResultOptionsToEndCombatWithCharQry(string searchStr = "")
{
string qryStr = "SELECT Characters.CharacterNames, Characters.Scenes "
+ "FROM Characters ";
if (searchStr != "")
{
string searchParam = DbCommands.GetParameterNameFromValue(searchStr);
string whereStr = " WHERE Characters.CharacterNames LIKE " + searchParam + " OR Characters.Scenes LIKE " + searchParam;
qryStr += whereStr;
}
qryStr += " ORDER BY CharacterNames;";
return(qryStr);
}
public static string GetTaskResultOptionsToStartDialogueQry(string searchStr = "")
{
string qryStr = "SELECT Dialogues.DialogueIDs, Dialogues.DialogueDescriptions, CharacterDialogues.CharacterNames "
+ "FROM Dialogues LEFT JOIN CharacterDialogues ON Dialogues.DialogueIDs = CharacterDialogues.DialogueIDs ";
if (searchStr != "")
{
string searchParam = DbCommands.GetParameterNameFromValue(searchStr);
string whereStr = " WHERE Dialogues.DialogueDescriptions LIKE " + searchParam + " OR CharacterDialogues.CharacterNames LIKE " + searchParam;
qryStr += whereStr;
}
qryStr += " ORDER BY CharacterDialogues.CharacterNames;";
return(qryStr);
}
public static string GetDefeatEnemyTagTasksData(string tag, string saveID)
{
string sql = "SELECT DISTINCT QuestTaskPartsDefeatCharTagged.PartIDs, QuestTaskParts.TaskIDs, QuestTasks.QuestNames "
+ "FROM QuestTaskPartsDefeatCharTagged "
+ "INNER JOIN QuestTaskParts ON QuestTaskPartsDefeatCharTagged.PartIDs = QuestTaskParts.PartIDs "
+ "INNER JOIN QuestTasksActivated ON QuestTaskParts.TaskIDs = QuestTasksActivated.TaskIDs "
+ "INNER JOIN QuestTasks ON QuestTaskParts.TaskIDs = QuestTasks.TaskIDs "
+ "INNER JOIN QuestsActivated ON QuestTasks.QuestNames = QuestsActivated.QuestNames "
+ "WHERE QuestTaskPartsDefeatCharTagged.CharacterTags = " + DbCommands.GetParameterNameFromValue(tag)
+ " AND QuestsActivated.SaveIDs = " + saveID
+ " AND QuestTaskPartsDefeatCharTagged.PartIDs NOT IN (SELECT CompletedQuestTaskParts.PartIDs FROM CompletedQuestTaskParts WHERE CompletedQuestTaskParts.SaveIDs = 0)";
return(sql);
}
public static string GetNewActivateGrammarPlayerChoiceResultQry(string searchStr = "")
{
string qryStr = "SELECT VocabGrammar.RuleIDs, VocabGrammar.ShortDescriptions, VocabGrammar.LongDescriptions "
+ "FROM VocabGrammar";
if (searchStr != "")
{
string searchParam = DbCommands.GetParameterNameFromValue(searchStr);
string whereStr = " WHERE VocabGrammar.ShortDescriptions LIKE " + searchParam + " OR VocabGrammar.LongDescriptions LIKE " + searchParam;
qryStr += whereStr;
}
qryStr += ";";
return(qryStr);
}
public static string GetNewActivateDialoguePlayerChoiceResultQry(string searchStr = "")
{
string qryStr = "SELECT Dialogues.DialogueIDs, Dialogues.DialogueDescriptions "
+ "FROM Dialogues";
if (searchStr != "")
{
string searchParam = DbCommands.GetParameterNameFromValue(searchStr);
string whereStr = " WHERE Dialogues.DialogueDescriptions LIKE " + searchParam;
qryStr += whereStr;
}
qryStr += ";";
Debug.Log(qryStr);
return(qryStr);
}
public static string GetTaskPartOptionsActivateDialogueNodeDisplayQry(string searchStr = "")
{
string qryStr = "SELECT Dialogues.DialogueDescriptions, DialogueNodes.NodeText, DialogueNodes.NodeIDs FROM DialogueNodes "
+ "INNER JOIN Dialogues ON Dialogues.DialogueIDs = DialogueNodes.DialogueIDs";
if (searchStr != "")
{
string searchParam = DbCommands.GetParameterNameFromValue(searchStr);
string whereStr = " WHERE Dialogues.DialogueDescriptions LIKE " + searchParam +
" OR DialogueNodes.NodeText LIKE " + searchParam +
" OR DialogueNodes.NodeIDs LIKE " + searchParam;
qryStr += whereStr;
}
qryStr += " ORDER BY Dialogues.DialogueIDs ASC;";
return(qryStr);
}
public static string GetCharacterNamesWithScene(string sceneName)
{
string sceneParam = DbCommands.GetParameterNameFromValue(sceneName);
return("SELECT CharacterNames FROM Characters WHERE scenes = " + sceneParam + ";");
}
public static string GetDefeatCharTagPartsRelatedToTaskQry(string taskID)
{
return("SELECT QuestTaskParts.PartIDs, QuestTaskPartsDefeatCharTagged.CharacterTags FROM QuestTaskParts "
+ "INNER JOIN QuestTaskPartsDefeatCharTagged ON QuestTaskPartsDefeatCharTagged.PartIDs = QuestTaskParts.PartIDs "
+ "WHERE TaskIDs = " + DbCommands.GetParameterNameFromValue(taskID) + ";");
}
public static string GetTasksRelatedToQuest(string questName)
{
return("SELECT * FROM QuestTasks WHERE QuestNames = " + DbCommands.GetParameterNameFromValue(questName) + " "
+ "AND TaskIDs In (SELECT TaskIDs FROM QuestTasksActivated WHERE SaveIDs = 0)");
}
public static string GetTasksDisplayQry(string questName)
{
return("SELECT * FROM QuestTasks WHERE QuestNames = " + DbCommands.GetParameterNameFromValue(questName) + ";");
}