|
Server : Apache/2.4.43 (Win64) OpenSSL/1.1.1g PHP/7.4.6 System : Windows NT USER-PC 6.1 build 7601 (Windows 7 Professional Edition Service Pack 1) AMD64 User : User ( 0) PHP Version : 7.4.6 Disable Function : NONE Directory : C:/Program Files (x86)/Microsoft Office/OFFICE11/FPCLASS/ |
<%BeginASP%>@ Import Namespace="System.Collections" <%EndASP%>
<%BeginASP%>@ Import Namespace="System.Collections.Specialized" <%EndASP%>
<%BeginASP%>@ Import Namespace="System.Globalization" <%EndASP%>
<%BeginASP%>@ Control ClassName="DBQuery" debug="true" Language="c#" <%EndASP%>
<SCRIPT language="C#" Runat="server">
private System.Web.UI.Control _querySourceControl;
private string _querySourceString;
private bool _submitPressed = false;
private bool _resetPressed = false;
private bool _showDebug = false;
public string QuerySource
{
get{
return _querySourceString;
}
set{
_querySourceString =value;
}
}
public bool Submitted
{
get {
return _submitPressed;
}
}
protected override void OnInit(EventArgs e)
{
_querySourceControl = Page.FindControl(QuerySource);
}
static public string GetUserField(DBQuery query, string fieldName)
{
if( query != null )
{
System.Web.UI.Control theControl = query._querySourceControl.FindControl( fieldName );
if( theControl != null && theControl is TextBox )
return query.TextBoxField( (TextBox)theControl ).Replace( "'", "''" );
}
return string.Empty;
}
public string TextBoxField( TextBox theBox )
{
if( _resetPressed )
{
theBox.Text = string.Empty;
return string.Empty;
}
string Result = theBox.Text;
if( Result.Length == 0 && theBox.Attributes["Default"] != null )
Result = theBox.Attributes["Default"];
return Result;
}
public void ClickSubmitButton(Object s, EventArgs e)
{
_submitPressed = true;
}
public void ClickResetButton(Object s, EventArgs e)
{
_submitPressed = true;
_resetPressed = true;
}
/* Params:
/* whereClause - the text of the query after the word "Where" that has values from the search form inserted into it
*/
static public string CompleteWhereClause( DBQuery query, string whereClause )
{
// This method parses the 'WHERE' clause of a FPDB:DBRegion tag. It replaces
// ::FieldName:: terms with information from user fields, and removes
// 'ColumnName op ::FieldName::' phrases if the field name is not found or
// there is no value entered and no default.
//
// This method does *not* do any other syntax checking. If there is an error
// in the syntax of the clause, it will fall through to the DataView and an
// exception will be thrown from there.
string Result = string.Empty;
string FieldName = string.Empty,
Token = string.Empty,
Phrase = string.Empty,
Conjunction = string.Empty;
int StartIndex = 0,
EndIndex = 0;
bool InField = false,
ValidPhrase = true,
FirstPhrase = true;
string DebugString = string.Empty;
while( EndIndex < whereClause.Length )
{
// Get a "term", either a string of letters and digits or some other single character
if( !System.Char.IsLetterOrDigit( whereClause, EndIndex ) )
EndIndex++;
else
while( System.Char.IsLetterOrDigit( whereClause, EndIndex ) && ++EndIndex < whereClause.Length );
Token = whereClause.Substring( StartIndex, EndIndex - StartIndex );
if( InField )
{
// We're parsing a field name ...
if( Token == ":" && whereClause[ StartIndex + 1 ] == ':' )
{
// ... and we've reached the end ...
EndIndex = StartIndex += 2;
InField = false;
if(!System.Char.IsLetter(FieldName,0))
{
FieldName = "Field" + FieldName;
}
if( GetUserField( query, FieldName ).Length != 0 )
{
Phrase += GetUserField( query, FieldName );
}
else
{
// ... but it doesn't exist or the user didn't enter anything,
ValidPhrase = false;
}
}
else
{
// ... and we continue to parse the field name
FieldName += Token;
StartIndex = EndIndex;
}
}
else
{
DebugString += Token + ";";
if( Token == ":" && whereClause[ StartIndex + 1 ] == ':' )
{
// This is the beginning of a field
EndIndex = StartIndex += 2;
InField = true;
FieldName = string.Empty;
}
else if( Token.Equals("(") )
{
// An open paren gets passed on to the result
Result += Token;
StartIndex = EndIndex;
}
else if( Token.Equals(")") )
{
// A close paren signals the end of a phrase
if( ValidPhrase )
{
if( !FirstPhrase )
{
Result += Conjunction;
}
Result += Phrase;
Conjunction = Token;
FirstPhrase = false;
}
// But it might be empty, so read backward through the result and find out
// if there's anything between this paren and its match
int nIndex = Result.Length;
while( System.Char.IsWhiteSpace( Result, --nIndex ) )
if( nIndex == 0 )
{
throw new ApplicationException( "<%IDS_DBREGION_ASPNET_ERROR_PARAMETER_COLONCOLON_DELIM%>" );
}
if( Result[ nIndex ] == '(' )
{
Result = ( nIndex > 0 ) ? "" : Result.Substring( 0, nIndex );
}
else
{
Result += ")";
Conjunction = string.Empty;
Phrase = string.Empty;
ValidPhrase = true;
}
StartIndex = EndIndex;
}
else if( Token.ToLower(CultureInfo.InvariantCulture ).Equals( "or" ) || Token.ToLower(CultureInfo.InvariantCulture).Equals( "and" ) )
{
// This is a conjunction between phrases
if( ValidPhrase )
{
if( !FirstPhrase )
Result += Conjunction;
Result += Phrase;
Conjunction = Token;
FirstPhrase = false;
}
else if( !Conjunction.ToLower(CultureInfo.InvariantCulture).Equals( "or" ) )
// The last phrase wasn't included by the user. We'll preserve "or" and move on.
Conjunction = Token;
Phrase = string.Empty;
StartIndex = EndIndex;
ValidPhrase = true;
}
else
{
// This is just part of a phrase
Phrase += Token;
StartIndex = EndIndex;
}
}
}
if( InField )
{
throw new ApplicationException( "<%IDS_DBREGION_ASPNET_ERROR_PARAMETER_COLONCOLON_DELIM%>" );
}
if( ValidPhrase )
{
Result += Conjunction + Phrase;
}
if( query != null && query._showDebug)
{
query.debugOutputLabel.Text = Result;
}
return Result;
}
public static String CompleteCustomSql(String sqlText, DBQuery query, ArrayList parameterList)
{
int curChar = 0;
int charMax = 0;
int colonStart = 0;
int SQuoteStart = 0;
int DQuoteStart = 0;
int Start = 0;
int End = 0;
parameterList.Clear();
while ((((curChar + 5) < sqlText.Length) && sqlText.IndexOf("::", curChar) >= 0))
{
charMax = sqlText.Length;
colonStart = sqlText.IndexOf("::", curChar);
SQuoteStart = sqlText.IndexOf('\'', curChar);
DQuoteStart = sqlText.IndexOf('"', curChar);
if (SQuoteStart == -1)
{
SQuoteStart = charMax;
}
if (DQuoteStart == -1)
{
DQuoteStart = charMax;
}
char QuoteDelim = '\0';
int QuoteStart = -1;
int QuoteEnd = charMax;
bool fp_bQuoteFound = false;
int PotQuoteEnd = 0;
if (colonStart > SQuoteStart && DQuoteStart > SQuoteStart) //single quote is first sought for character
{
QuoteDelim = '\'';
QuoteStart = SQuoteStart;
}
else if (colonStart > DQuoteStart && SQuoteStart > DQuoteStart) //double quote is first sought for character
{
QuoteDelim = '"';
QuoteStart = DQuoteStart;
}
else
{
QuoteStart = colonStart;
//The :: comes before any ' or "
}
if(QuoteDelim != '\0')
{
PotQuoteEnd = QuoteStart + 1;
while (fp_bQuoteFound == false && PotQuoteEnd < (charMax - 1))
{
PotQuoteEnd = sqlText.IndexOf(QuoteDelim, PotQuoteEnd);
if(PotQuoteEnd == -1)
{
break;
}
if(PotQuoteEnd == (charMax - 1))
{
QuoteEnd = PotQuoteEnd;
fp_bQuoteFound = true;
break;
}
if(!sqlText.Substring( PotQuoteEnd + 1, 1).Equals(QuoteDelim))
{
QuoteEnd = PotQuoteEnd;
fp_bQuoteFound = true;
}
else
{
PotQuoteEnd = PotQuoteEnd + 2;
}
}
if(fp_bQuoteFound == false)
{
throw new ApplicationException("<%IDS_DBREGION_ASPNET_ERROR_NO_MATCH_QUOTE%>");
}
if(colonStart > QuoteEnd) //there is no user input in this literal string
{
curChar = QuoteEnd + 1;
continue;
}
}
Start = colonStart;
// found a opening ::, find the close ::
End = sqlText.IndexOf("::", Start + 2);
if (End == -1)
{
throw new ApplicationException( "<%IDS_DBREGION_ASPNET_ERROR_COLON_COLON_CUSTOM%>");
}
if (!fp_bQuoteFound)
{
QuoteEnd = End + 1;
}
String Field = sqlText.Substring( Start + 2, End - (Start + 2));
String Value = GetUserField(query, Field);
if (fp_bQuoteFound)
{
String Lead = sqlText.Substring( QuoteStart + 1, colonStart - QuoteStart -1);
String Tail = sqlText.Substring( End + 2, QuoteEnd - End - 2);
if (QuoteDelim == '\"')
{
Lead = Lead.Replace("\"\"", "\"");
Tail = Tail.Replace("\"\"", "\"");
}
else if (QuoteDelim == '\'' )
{
Lead = Lead.Replace("''", "'");
Tail = Tail.Replace("''", "'");
}
Value = Lead + Value + Tail;
}
if(fp_bQuoteFound == false)
{
if(Value.Length == 0)
{
Value = "0";
}
try{
parameterList.Add( Convert.ToDecimal(Value) );
}
catch( FormatException )
{
parameterList.Add( Convert.ToDecimal(0));
}
}
else
{
parameterList.Add( Value );
}
if((sqlText.Length - QuoteEnd) == 1 )
{
sqlText = sqlText.Substring(0, QuoteStart) + "?";
}
else
{
sqlText = sqlText.Substring(0, QuoteStart) + "?" + sqlText.Substring( QuoteEnd + 1);
}
// Fixup the new current position to be after the substituted value
curChar = QuoteStart + 1;
}
return sqlText;
}
</SCRIPT>
<asp:Button ID="SubmitButton" Text="Submit" OnClick="ClickSubmitButton" RunAt="server" />
<asp:Button ID="ResetButton" Text="Reset" OnClick="ClickResetButton" RunAt="server" />
<asp:Label ID="debugOutputLabel" Runat="server" />