///<summary>This method takes care of parsing the query by pulling out SET statements and finding the variables with their assigned values.
///Puts all of this information into the grid.</summary>
private void FillGrid(bool isTypingText = false)
{
Point selectedCell = gridMain.SelectedCell;
List <string> listSetStmts = UserQueries.ParseSetStatements(_queryCur.QueryText);
gridMain.BeginUpdate();
gridMain.ListGridColumns.Clear();
gridMain.ListGridColumns.Add(new GridColumn(Lan.g(gridMain.TranslationName, "Variable"), 200));
gridMain.ListGridColumns.Add(new GridColumn(Lan.g(gridMain.TranslationName, "Value"), 200, true));
gridMain.ListGridRows.Clear();
foreach (string strSetStmt in listSetStmts) //for each SET statement
{
List <QuerySetStmtObject> listQObjs = UserQueries.GetListQuerySetStmtObjs(strSetStmt); //find the variable name
foreach (QuerySetStmtObject qObj in listQObjs)
{
GridRow row = new GridRow();
row.Cells.Add(qObj.Variable);
row.Cells.Add(qObj.Value);
row.Tag = qObj;
gridMain.ListGridRows.Add(row);
}
}
gridMain.EndUpdate();
if (!isTypingText)
{
try {
gridMain.SetSelected(selectedCell);
}
catch {
//suppress if the row doesn't exist (such as filling the grid for the first time)
}
}
}
private void butOK_Click(object sender, System.EventArgs e)
{
if (UserQueryCur == null)
{
MsgBox.Show(this, "Please select an item first.");
return;
}
ReportSimpleGrid report = new ReportSimpleGrid();
if (UserQueryCur.IsPromptSetup && UserQueries.ParseSetStatements(UserQueryCur.QueryText).Count > 0)
{
//if the user is not a query admin, they will not have the ability to edit
//the query before it is run, so show them the SET statement edit window.
FormQueryParser FormQP = new FormQueryParser(UserQueryCur);
FormQP.ShowDialog();
if (FormQP.DialogResult == DialogResult.OK)
{
report.Query = UserQueryCur.QueryText;
DialogResult = DialogResult.OK;
}
}
else
{
//user has permission to edit the query, so just run the query.
DialogResult = DialogResult.OK;
}
}
private void gridMain_CellLeave(object sender, ODGridClickEventArgs e)
{
QuerySetStmtObject qObjCur = (QuerySetStmtObject)gridMain.SelectedGridRows[0].Tag;
string stmtOld = qObjCur.Stmt;
string varOld = qObjCur.Variable;
string valOld = qObjCur.Value;
string valNew = gridMain.SelectedGridRows[0].Cells[1].Text.ToString();
if (UserQueries.SplitQuery(valNew, true, ";").Count > 1)
{
Point _selectedCell = gridMain.SelectedCell;
MsgBox.Show(this, "You may not include semicolons in the value text. Please remove all semicolons.");
gridMain.SelectedGridRows[0].Cells[1].Text = valOld;
gridMain.SetSelected(_selectedCell); //this just refreshes the cell that is being left. Is there an easy way to cancel the CellLeave action?
return;
}
if (valOld == valNew)
{
return; //don't bother doing any of the logic below if nothing changed.
}
//Regular expression for the expression @Variable = Value.
Regex r = new Regex(Regex.Escape(varOld) + @"\s*=\s*" + Regex.Escape(valOld));
string stmtNew = r.Replace(stmtOld, varOld + "=" + valNew);
_queryCur.QueryText = _queryCur.QueryText.Replace(stmtOld, stmtNew);
textQuery.Text = _queryCur.QueryText;
qObjCur.Stmt = stmtNew;
qObjCur.Value = valNew;
gridMain.Rows.OfType <ODGridRow>().Where(x => ((QuerySetStmtObject)x.Tag).Stmt == stmtOld).ToList().ForEach(y => {
((QuerySetStmtObject)y.Tag).Stmt = stmtNew;
});
}
private void FillGrid(bool refreshList = true, bool isScrollToSelection = true)
{
if (refreshList)
{
_listQueries = UserQueries.GetDeepCopy();
}
string[] strSearchTerms = Regex.Split(textSearch.Text, @"\W"); //matches any non-word character
//get all queries that contain ALL of the search terms entered, either in the query text or the query description.
List <UserQuery> listDisplayQueries = _listQueries
.Where(x => strSearchTerms.All(y =>
x.QueryText.ToLowerInvariant().Contains(y.ToLowerInvariant()) || x.Description.ToLowerInvariant().Contains(y.ToLowerInvariant())
)).ToList();
//attempt to preserve the currently selected query.
long selectedQueryNum = 0;
if (gridMain.GetSelectedIndex() != -1)
{
selectedQueryNum = gridMain.SelectedTag <UserQuery>().QueryNum;
}
gridMain.BeginUpdate();
gridMain.ListGridColumns.Clear();
gridMain.ListGridColumns.Add(new GridColumn(Lan.g(gridMain.TranslationName, "Query"), 350));
if (Security.IsAuthorized(Permissions.UserQueryAdmin, true))
{
gridMain.ListGridColumns.Add(new GridColumn(Lan.g(gridMain.TranslationName, "Released"), 55, HorizontalAlignment.Center));
}
gridMain.ListGridRows.Clear();
foreach (UserQuery queryCur in listDisplayQueries)
{
if (!Security.IsAuthorized(Permissions.UserQueryAdmin, true) && !queryCur.IsReleased)
{
continue; //non-released queries only appear for people with UserQueryAdmin permission.
}
GridRow row = new GridRow();
row.Cells.Add(queryCur.Description);
if (Security.IsAuthorized(Permissions.UserQueryAdmin, true))
{
row.Cells.Add(queryCur.IsReleased ? "X" : "");
}
row.Tag = queryCur;
gridMain.ListGridRows.Add(row);
}
gridMain.EndUpdate();
int selectedIdx = gridMain.ListGridRows.Select(x => (UserQuery)x.Tag).ToList().FindIndex(y => y.QueryNum == selectedQueryNum);
if (selectedIdx > -1)
{
gridMain.SetSelected(selectedIdx, true);
}
if (gridMain.GetSelectedIndex() == -1)
{
gridMain.SetSelected(0, true); //can handle values outside of the row count (so if there are no rows, this will not fail)
}
if (isScrollToSelection)
{
gridMain.ScrollToIndex(gridMain.GetSelectedIndex()); //can handle values outside of the row count
}
RefreshQueryCur();
}
private void FillList()
{
UserQueries.Refresh();
int tempIndex = list2.SelectedIndex;
list2.Items.Clear();
for (int i = 0; i < UserQueries.List.Length; i++)
{
this.list2.Items.Add(UserQueries.List[i].Description);
}
list2.SelectedIndex = tempIndex;
}
private void FillList()
{
QueryList = UserQueries.Refresh();
int tempIndex = list2.SelectedIndex;
list2.Items.Clear();
for (int i = 0; i < QueryList.Count; i++)
{
this.list2.Items.Add(QueryList[i].Description);
}
list2.SelectedIndex = tempIndex;
}
private void gridMain_CellClick(object sender, ODGridClickEventArgs e)
{
if (e.Col == 1) //Released Column
{
UserQuery query = gridMain.SelectedTag <UserQuery>();
query.IsReleased = (!query.IsReleased);
UserQueries.Update(query);
DataValid.SetInvalid(InvalidType.UserQueries);
FillGrid(true, false); //Results in RefreshQueryCur()
return;
}
RefreshQueryCur();
}
private void gridMain_CellLeave(object sender, ODGridClickEventArgs e)
{
QuerySetStmtObject qObjCur;
GridRow rowLeaving;
try {
//Can not use gridMain.SelectedGridRows due to leave being hit multiple times by input texboxes.
//Base grid logic fires this leave function multiple times.
rowLeaving = gridMain.ListGridRows[e.Row];
qObjCur = (QuerySetStmtObject)rowLeaving.Tag;
}
catch { //Has occurend when user types SHIFT+ENTER on the keyboard.
return;
}
string stmtOld = qObjCur.Stmt;
string varOld = qObjCur.Variable;
string valOld = qObjCur.Value;
string valNew = rowLeaving.Cells[1].Text.ToString();
if (UserQueries.SplitQuery(valNew, true, ";").Count > 1)
{
Point _selectedCell = gridMain.SelectedCell;
MsgBox.Show(this, "You may not include semicolons in the value text. Please remove all semicolons.");
gridMain.SelectedGridRows[0].Cells[1].Text = valOld;
gridMain.SetSelected(_selectedCell); //this just refreshes the cell that is being left. Is there an easy way to cancel the CellLeave action?
return;
}
if (valOld == valNew)
{
return; //don't bother doing any of the logic below if nothing changed.
}
if (HasQuotes(valOld))
{
valNew = "'" + valNew.Trim().Trim('\"').Trim('\'') + "'";
}
//Regular expression for the expression @Variable = Value.
Regex r = new Regex(Regex.Escape(varOld) + @"\s*=\s*" + Regex.Escape(valOld));
string stmtNew = r.Replace(stmtOld, varOld + "=" + valNew);
_queryCur.QueryText = r.Replace(_queryCur.QueryText, varOld + "=" + valNew);
textQuery.Text = _queryCur.QueryText;
qObjCur.Stmt = stmtNew;
qObjCur.Value = valNew;
gridMain.ListGridRows.OfType <GridRow>().Where(x => ((QuerySetStmtObject)x.Tag).Stmt == stmtOld).ToList().ForEach(y => {
((QuerySetStmtObject)y.Tag).Stmt = stmtNew;
});
}
private void butDelete_Click(object sender, System.EventArgs e)
{
if (list2.SelectedIndex == -1)
{
MessageBox.Show(Lan.g(this, "Please select an item first."));
return;
}
if (MessageBox.Show(Lan.g(this, "Delete Item?"), "", MessageBoxButtons.OKCancel) != DialogResult.OK)
{
return;
}
UserQueries.Delete(UserQueryCur);
list2.SelectedIndex = -1;
FillList();
textTitle.Text = "";
textQuery.Text = "";
textFileName.Text = "";
}
private void butDelete_Click(object sender, System.EventArgs e)
{
//button is disabled for users without Query Admin permission.
if (UserQueryCur == null)
{
MsgBox.Show(this, "Please select an item first.");
return;
}
if (MessageBox.Show(Lan.g(this, "Delete Item?"), "", MessageBoxButtons.OKCancel) != DialogResult.OK)
{
return;
}
UserQueries.Delete(UserQueryCur);
DataValid.SetInvalid(InvalidType.UserQueries);
gridMain.SetSelected(false);
UserQueryCur = null;
FillGrid();
textQuery.Text = "";
}
private void butOK_Click(object sender, System.EventArgs e)
{
if (textTitle.Text == "")
{
MessageBox.Show(Lan.g(this, "Please enter a title first."));
return;
}
UserQueryCur.Description = textTitle.Text;
UserQueryCur.QueryText = textQuery.Text;
UserQueryCur.FileName = textFileName.Text;
if (IsNew)
{
UserQueries.Insert(UserQueryCur);
}
else
{
UserQueries.Update(UserQueryCur);
}
DialogResult = DialogResult.OK;
}
private void butOK_Click(object sender, System.EventArgs e)
{
if (textTitle.Text == "")
{
MessageBox.Show(Lan.g(this, "Please enter a title first."));
return;
}
UserQueryCur.Description = textTitle.Text;
UserQueryCur.QueryText = textQuery.Text;
UserQueryCur.FileName = textFileName.Text;
UserQueryCur.IsReleased = checkReleased.Checked;
UserQueryCur.IsPromptSetup = checkIsPromptSetup.Checked;
if (IsNew)
{
UserQueries.Insert(UserQueryCur);
}
else
{
UserQueries.Update(UserQueryCur);
}
DataValid.SetInvalid(InvalidType.UserQueries);
DialogResult = DialogResult.OK;
}
///<summary>Returns the list of variables in the query contained within the passed-in SET statement.
///Pass in one SET statement. Used in conjunction with GetListVals.</summary>
private List <QuerySetStmtObject> GetListQuerySetStmtObjs(string setStmt)
{
List <string> strSplits = UserQueries.SplitQuery(setStmt, false, ",");
for (int i = 0; i < strSplits.Count; i++)
{
Regex r = new Regex(@"\s*set\s+", RegexOptions.IgnoreCase);
strSplits[i] = r.Replace(strSplits[i], "");
}
UserQueries.TrimList(strSplits);
strSplits.RemoveAll(x => string.IsNullOrWhiteSpace(x) || !x.StartsWith("@") || x.StartsWith("@_"));
List <QuerySetStmtObject> bufferList = new List <QuerySetStmtObject>();
for (int i = 0; i < strSplits.Count; i++)
{
QuerySetStmtObject qObj = new QuerySetStmtObject();
qObj.Stmt = setStmt;
qObj.Variable = strSplits[i].Split(new char[] { '=' }, 2).First();
qObj.Value = strSplits[i].Split(new char[] { '=' }, 2).Last();
bufferList.Add(qObj);
}
return(bufferList);
}
