public void OnQueryWhere(NavigatorQueryWhereEventArgs value)
{
NavigatorQueryWhereEventHandler handler = (NavigatorQueryWhereEventHandler)Events[EventQueryWhere];
if (handler != null)
{
handler(this, value);
}
}
internal string WhereString(string sql)
{
string strFilter = "";
if (this.whereItem.Count != 0 && this.OwnerComp != null)
{
foreach (WhereItem wi in this.whereItem)
{
string whereValue = "";
if (this.OwnerComp != null)
whereValue = this.GetValue(wi.Value);
if (objDataSource == null)
{
SetAll();
}
string type = ((InfoDataSet)objDataSource).RealDataSet.Tables[strSourceTab].Columns[wi.FieldName].DataType.ToString().ToLower();
if (wi.GetSign() != "like begin with value" && wi.GetSign() != "like with value")
{
if (type == "system.uint" || type == "system.uint16" || type == "system.uint32" || type == "system.uint64"
|| type == "system.int" || type == "system.int16" || type == "system.int32" || type == "system.int64"
|| type == "system.single" || type == "system.float" || type == "system.double" || type == "system.decimal")
{
strFilter += CliUtils.GetTableNameForColumn(sql, wi.FieldName) + wi.GetSign() + whereValue + " and ";
}
else if (type == "system.datetime")
{
whereValue = CliUtils.FormatDateString(whereValue);
strFilter += CliUtils.GetTableNameForColumn(sql, wi.FieldName) + wi.GetSign() + whereValue + " and ";
}
else
{
strFilter += CliUtils.GetTableNameForColumn(sql, wi.FieldName) + wi.GetSign() + "'" + whereValue + "' and ";
}
}
else
{
if (wi.GetSign() == "like begin with value")
{
strFilter += CliUtils.GetTableNameForColumn(sql, wi.FieldName) + " like '" + whereValue + "%' and ";
}
if (wi.GetSign() == "like with value")
{
strFilter += CliUtils.GetTableNameForColumn(sql, wi.FieldName) + " like '%" + whereValue + "%' and ";
}
}
}
if (strFilter != string.Empty)
{
strFilter = strFilter.Substring(0, strFilter.LastIndexOf(" and "));
}
}
NavigatorQueryWhereEventArgs args = new NavigatorQueryWhereEventArgs(strFilter);
OnQueryWhere(args);
strFilter = args.WhereString;
return strFilter;
}
public string Execute(Panel pn)
{
if (this.BindingSource != null)
{
InfoDataSet ids = this.BindingSource.DataSource as InfoDataSet;
string wherestring = this.GetWhere(pn);
NavigatorQueryWhereEventArgs args = new NavigatorQueryWhereEventArgs(wherestring);
OnQueryWhere(args);
if (!args.Cancel)
{
wherestring = args.WhereString;
ids.SetWhere(wherestring);
}
return wherestring;
}
else
{
throw new Exception(string.Format("BindingSource doesn't exist"));
}
}
protected virtual void DoViewQuery()
{
// (3 : 1 of 2) 在Query時: SetState(Querying);
this.SetState("Querying");
//string strQueryFields = _QueryFields;
InfoBindingSource bSource = new InfoBindingSource();
if (this.ViewBindingSource != null)
{
bSource = this.ViewBindingSource;
}
else if (this.BindingSource != null && this.ViewBindingSource == null)
{
bSource = this.BindingSource;
}
this.Focus();
if (this.QueryMode == QueryModeType.Normal)
{
Form frmNavQuery = new frmNavQuery(this.QueryFields, bSource, this);
frmNavQuery.ShowDialog();
}
else if (this.QueryMode == QueryModeType.ClientQuery)
{
if (this.QueryFields.Count > 0)
{
if (cqform == null)
{
cqform = CopyQueryFileds(1);
}
string strwhere = string.Empty;
try
{
strwhere = cqform.Execute(false);
NavigatorQueryWhereEventArgs args = new NavigatorQueryWhereEventArgs(strwhere);
OnQueryWhere(args);
if (!args.Cancel)
{
strwhere = args.WhereString;
if (strwhere != null)
{
if (this.StatusStrip != null && this.StatusStrip.ShowProgress)
{
this.StatusStrip.ShowProgressBar();
}
((InfoDataSet)bSource.GetDataSource()).SetWhere(strwhere);
if (this.StatusStrip != null && this.StatusStrip.ShowProgress)
{
this.StatusStrip.HideProgressBar();
}
}
}
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
}
else
{
Form frmNavQuery = new frmNavQuery(this.QueryFields, bSource, this);
frmNavQuery.ShowDialog();
}
}
else if (this.QueryMode == QueryModeType.AnyQuery)
{
if (aq == null)
aq = CopyAnyQueryFileds();
String strwhere = aq.Execute(this, false);
NavigatorQueryWhereEventArgs args = new NavigatorQueryWhereEventArgs(strwhere);
OnQueryWhere(args);
args.Cancel = !this.QuerySQLSend;
if (!args.Cancel)
{
strwhere = args.WhereString;
if (strwhere != null)
{
if (this.StatusStrip != null && this.StatusStrip.ShowProgress)
{
this.StatusStrip.ShowProgressBar();
}
((InfoDataSet)bSource.GetDataSource()).SetWhere(strwhere);
if (this.StatusStrip != null && this.StatusStrip.ShowProgress)
{
this.StatusStrip.HideProgressBar();
}
}
}
}
// (3 : 2 of 2) Query後: SetState(Browsed).
this.SetState("Browsed");
}
public void Execute(Panel pn)
{
string strwhere = GetWhere(pn);
NavigatorQueryWhereEventArgs args = new NavigatorQueryWhereEventArgs(strwhere);
OnQueryWhere(args);
if (!args.Cancel)
{
strwhere = args.WhereString;
InfoBindingSource ibs = null;
if (this.ViewBindingSource != null)
{
ibs = this.ViewBindingSource;
}
else
{
ibs = this.BindingSource;
}
((InfoDataSet)ibs.GetDataSource()).SetWhere(strwhere);
}
}
public void Execute(Panel pn)
{
string strwhere = GetWhere(pn);
NavigatorQueryWhereEventArgs args = new NavigatorQueryWhereEventArgs(strwhere);
OnQueryWhere(args);
if (!args.Cancel)
{
strwhere = args.WhereString;
WebDataSource ds = null;
object obj = null;
if (this.ViewBindingObject != null && this.ViewBindingObject != "")
{
obj = this.GetObjByID(this.ViewBindingObject);
}
else if (this.BindingObject != null && this.BindingObject != "")
{
obj = this.GetObjByID(this.BindingObject);
}
if (obj != null && obj is CompositeDataBoundControl)
{
CompositeDataBoundControl dataControl = (CompositeDataBoundControl)obj;
foreach (Control ctrl in this.Page.Form.Controls)
{
if (ctrl is WebDataSource && ((WebDataSource)ctrl).ID == dataControl.DataSourceID)
{
ds = (WebDataSource)ctrl;
}
}
}
ds.SetWhere(strwhere);
this.Page.DataBind();
}
}
private void btnOK_Click(object sender, EventArgs e)
{
infoNavigator.PreQueryField.Clear();
infoNavigator.PreQueryCondition.Clear();
infoNavigator.PreQueryValue.Clear();
string sqlcmd = DBUtils.GetCommandText(bSource);
string strQueryCondition = "";
for (int i = 0; i < colNum; i++)
{
if (comboBoxes[i].Text != string.Empty && textBoxes[i].Text != string.Empty)
{
string columnName = CliUtils.GetTableNameForColumn(sqlcmd, colName[i]);
Type type = ((InfoDataSet)bSource.DataSource).RealDataSet.Tables[bSource.DataMember].Columns[colName[i]].DataType;
string nvarCharMark = type == typeof(string) && queryFields.Count > 0 && (queryFields[i] as QueryField).IsNvarChar ? "N" : string.Empty;
if (string.Compare(textBoxes[i].Text.Trim(), "null", true) == 0) //IgnoreCase
{
if (type == typeof(uint) || type == typeof(UInt16) || type == typeof(UInt32)
|| type == typeof(UInt64) || type == typeof(int) || type == typeof(Int16)
|| type == typeof(Int32) || type == typeof(Int64) || type == typeof(Single)
|| type == typeof(Double) || type == typeof(Decimal) || type == typeof(DateTime))
{
if (comboBoxes[i].Text.Equals("!="))
{
strQueryCondition += columnName + "is not null and ";
}
else
{
strQueryCondition += columnName + "is null and ";
}
}
else
{
if (comboBoxes[i].Text.Equals("!="))
{
strQueryCondition += "(" + columnName + "is not null and" + columnName + "<>'') and ";
}
else
{
strQueryCondition += "(" + columnName + "is null or" + columnName + "='') and ";
}
}
}
else
{
if (type == typeof(Guid))
{
try
{
Guid id = new Guid(textBoxes[i].Text);
}
catch (FormatException)
{
MessageBox.Show(string.Format("{0}'s type should be {1}", labels[i].Text, type.Name));
this.DialogResult = DialogResult.None;
return;
}
}
else
{
try
{
Convert.ChangeType(textBoxes[i].Text, type);
}
catch
{
MessageBox.Show(string.Format("{0}'s type should be {1}", labels[i].Text, type.Name));
this.DialogResult = DialogResult.None;
return;
}
}
if (comboBoxes[i].Text != "%" && comboBoxes[i].Text != "%%")
{
if (type == typeof(uint) || type == typeof(UInt16) || type == typeof(UInt32)
|| type == typeof(UInt64) || type == typeof(int) || type == typeof(Int16)
|| type == typeof(Int32) || type == typeof(Int64) || type == typeof(Single)
|| type == typeof(Double) || type == typeof(Decimal))
{
strQueryCondition += columnName + comboBoxes[i].Text + textBoxes[i].Text + " and ";
}
else if (type == typeof(DateTime))
{
string[] DBType = getDBType();
if (DBType[0] == "1")
strQueryCondition += columnName + comboBoxes[i].Text + " '" + textBoxes[i].Text + "' and ";
else if (DBType[0] == "2")
strQueryCondition += columnName + comboBoxes[i].Text + " '" + textBoxes[i].Text + "' and ";
else if (DBType[0] == "3")
{
string Date = changeDate(textBoxes[i].Text);
strQueryCondition += columnName + comboBoxes[i].Text + " to_Date('" + Date + "', 'yyyymmdd') and ";
}
else if (DBType[0] == "4")
{
DateTime t;
string Date = "";
if (DBType[1] == "0")
{
if (textBoxes[i].Text.Contains("-") || textBoxes[i].Text.Contains("/"))
{
t = Convert.ToDateTime(textBoxes[i].Text);
Date = t.ToString("yyyyMMddHHmmss");
}
else if (textBoxes[i].Text.Length < 14)
Date = textBoxes[i].Text + "000000";
strQueryCondition += columnName + comboBoxes[i].Text + " to_Date('" + Date + "', '%Y%m%d%H%M%S') and ";
}
else if (DBType[1] == "1")
{
if (textBoxes[i].Text.Contains("-") || textBoxes[i].Text.Contains("/"))
{
t = Convert.ToDateTime(textBoxes[i].Text);
Date = t.ToString("MM/dd/yyyy");
}
else if (textBoxes[i].Text.Length < 14)
Date = textBoxes[i].Text + "000000";
strQueryCondition += columnName + comboBoxes[i].Text + "{" + Date + "} and ";
}
}
else if (DBType[0] == "5")
strQueryCondition += columnName + comboBoxes[i].Text + " '" + textBoxes[i].Text + "' and ";
else if (DBType[0] == "6")
{
DateTime t;
string Date = "";
if (textBoxes[i].Text.Contains("-") || textBoxes[i].Text.Contains("/"))
{
t = Convert.ToDateTime(textBoxes[i].Text);
Date = t.ToString("yyyyMMddHHmmss");
}
else if (textBoxes[i].Text.Length < 14)
Date = textBoxes[i].Text + "000000";
strQueryCondition += columnName + comboBoxes[i].Text + " to_Date('" + Date + "', '%Y%m%d%H%M%S') and ";
}
else if (DBType[0] == "7")
{
strQueryCondition += columnName + comboBoxes[i].Text + " '" + textBoxes[i].Text + "' and ";
}
}
else
{
strQueryCondition += columnName + comboBoxes[i].Text + " " + nvarCharMark + "'" + textBoxes[i].Text.Replace("'", "''") + "' and ";
}
}
else
{
if (comboBoxes[i].Text == "%")
{
strQueryCondition += columnName + " like " + nvarCharMark + "'" + textBoxes[i].Text.Replace("'", "''") + "%' and ";
}
if (comboBoxes[i].Text == "%%")
{
strQueryCondition += columnName + " like " + nvarCharMark + "'%" + textBoxes[i].Text.Replace("'", "''") + "%' and ";
}
}
}
}
infoNavigator.PreQueryField.Add(colName[i]);
infoNavigator.PreQueryCondition.Add(comboBoxes[i].Text);
infoNavigator.PreQueryValue.Add(textBoxes[i].Text);
}
if (strQueryCondition != string.Empty)
{
strQueryCondition = strQueryCondition.Substring(0, strQueryCondition.LastIndexOf(" and "));
}
if (!infoNavigator.QuerySQLSend)
{
infoNavigator.OnQueryConfirm(new QueryConfirmEventArgs(strQueryCondition));
}
else
{
NavigatorQueryWhereEventArgs args = new NavigatorQueryWhereEventArgs(strQueryCondition);
infoNavigator.OnQueryWhere(args);
if (!args.Cancel)
{
strQueryCondition = args.WhereString;
if (strQueryCondition != string.Empty)
{
if (infoNavigator.StatusStrip != null && infoNavigator.StatusStrip.ShowProgress)
{
infoNavigator.StatusStrip.ShowProgressBar();
}
((InfoDataSet)(bSource.DataSource)).SetWhere(strQueryCondition);
if (infoNavigator.StatusStrip != null && infoNavigator.StatusStrip.ShowProgress)
{
infoNavigator.StatusStrip.HideProgressBar();
}
if (bSource.List.Count == 0 && infoNavigator.ViewBindingSource != null
&& infoNavigator.ViewBindingSource != infoNavigator.BindingSource)
{
// infoNavigator.BindingSource.Filter = "1<>1";
InfoDataSet ids = infoNavigator.BindingSource.DataSource as InfoDataSet;
if (ids != null)
{
ids.SetWhere("1=0");
}
}
//else
//{
// infoNavigator.BindingSource.Filter = "";
//}
}
else
{
((InfoDataSet)(bSource.DataSource)).ClearWhere();
if (bSource.List.Count != 0 && infoNavigator.ViewBindingSource != infoNavigator.BindingSource)
{
infoNavigator.BindingSource.Filter = "";
}
}
}
}
}
private void btnCancel_Click(object sender, EventArgs e)
{
NavigatorQueryWhereEventArgs args = new NavigatorQueryWhereEventArgs(null);
infoNavigator.OnQueryWhere(args);
}
