protected override SearchField[] GetWhereFields(SearchField[] searchFields)
{
List<SearchField> whereFields;
whereFields = new List<SearchField>();// (searchFields);
foreach (SearchField sf in searchFields)
{
if (sf.Name.Equals("intforeignid"))
continue;
whereFields.Add(sf);
}
return whereFields.ToArray();
}
protected override SearchField[] GetWhereFields(SearchField[] searchFields)
{
return new SearchField[0];
}
protected override SearchField[] GetHavingFields(SearchField[] searchFields)
{
return new SearchField[0];
}
/// <summary>
/// ViewRealTimeData is a request to the ERP system for real time viewing data from the ERP system.
/// </summary>
/// <param name="queryFields">An array of query fields that represent the parameters needed for the request.</param>
/// <param name="config">the configuration object</param>
/// <returns>The response to ViewRealTimeData is a XmlDocument, which contains a list of real time data.</returns>
public ViewRealTimeDataResult ViewRealTimeData(string entityName, string[] selectFields, SearchField[] searchFields, string[] orderFields, int rowsPerPage, int pageNumber, NorthwindConfig northwindConfig)
{
// declarations
RTDVBase rdtv;
rdtv = RTDVFactory.GetRTDV(entityName);
if (rdtv == null)
throw new Exception(string.Format(Resources.ErrorMessages_RTDVNotImplemented, entityName));
if (orderFields == null)
orderFields = new string[0];
if (searchFields == null)
searchFields = new SearchField[0];
if (selectFields == null)
selectFields = new string[0];
ViewRealTimeDataResult result = new ViewRealTimeDataResult();
result.RealTimeData = rdtv.ViewRealTimeData(entityName, selectFields, searchFields, orderFields, rowsPerPage, pageNumber, northwindConfig);
return result;
}
protected override SearchField[] GetHavingFields(SearchField[] searchFields)
{
List<SearchField> havingFields;
havingFields = new List<SearchField>(searchFields);
return havingFields.ToArray();
}
protected abstract SearchField[] GetHavingFields(SearchField[] list);
protected abstract SearchField[] GetWhereFields(SearchField[] list);
protected virtual string CreateWhereClause(SearchField[] searchFields, ref List<OleDbParameter> oleDbParameterList)
{
// declarations
string whereClause; // the result string.
string fieldName; // the field name to enter into the WHERE clause.
OperatorValue operationTag; // the operator of the Search (WHERE or HAVING) clause.
string fieldValue; // the value to enter into the WHERE clause.
string sqlFieldName; // the representation of the field name in the SQL query.
string sqlOperationTag; // the representation of the operation tag in the SQL query.
string sqlFieldValue; // the representation od the field value (i.e. add % when operator is LIKE)
// initialize where clause
whereClause = string.Empty;
if (searchFields.Length == 0)
return whereClause;
else
whereClause = " WHERE ";
try
{
// iterate through the search fields and build the where clause
foreach (SearchField field in searchFields)
{
fieldName = field.Name; // get the field name
operationTag = field.Operator; // get the operator
fieldValue = field.Value; // get the field value
sqlFieldName = this.GetSqlQueryNameRepresentation(fieldName); // get the SQL query representation of the field name
if ((sqlFieldName == null) || (sqlFieldName == string.Empty))
continue;
sqlOperationTag = this.GetSqlQueryOperatorRepresentation(operationTag); // get the SQL query representation of the operation tag
sqlFieldValue = this.GetSqlQueryFieldValueRepresentation(fieldValue, operationTag); // get the SQL query representation of the field value (i.e. for Contains: %myValue%)
// Create a new parameter
oleDbParameterList.Add(this.CreateOleDbParameter(fieldName, sqlFieldValue));
// enter field name using the SQL representation.
whereClause += "(" + sqlFieldName;
// enter operator tag
whereClause += sqlOperationTag;
// enter placeholder
whereClause += "?)";
// enter separator
whereClause += "AND ";
}
}
catch (Exception exception)
{
#if DEBUG
throw new ArgumentException(string.Format(Resources.ErrorMessages_RTDVSQLQueryBuildError_Debug, _reportName, whereClause, this.GetSqlQueryTemplate()), exception);
#else
throw new ArgumentException(string.Format(Resources.ErrorMessages_RTDVSQLQueryBuildError_Debug, _reportName), exception);
#endif
}
// remove the last separator
whereClause = whereClause.Remove(whereClause.Length - 4);
return whereClause;
}
protected virtual string CreateSqlQuery(SearchField[] whereFields, SearchField[] havingFields, string[] orderFields, out List<OleDbParameter> oleDbParameterList)
{
// declarations
string sqlQuery;
string sqlQueryTemplate;
string sqlWhereClause;
string sqlHavingClause;
string sqlOrderClause;
// initializations
oleDbParameterList = new List<OleDbParameter>();
// Get the SQL query template.
// it has the format: SELECT... FROM... WHERE {0} GROUPBY... HAVING {1} ORDERBY{2}
sqlQueryTemplate = this.GetSqlQueryTemplate();
// Create the 'WHERE' clause of the SQL query
sqlWhereClause = this.CreateWhereClause(whereFields, ref oleDbParameterList);
// Create the 'HAVING' clause of the SQL query
sqlHavingClause = this.CreateHavingClause(havingFields, ref oleDbParameterList);
// Create the 'ORDERBY' clause of the SQL query
sqlOrderClause = this.CreateOrderClause(orderFields);
// create the complete SQL query
sqlQuery = this.ConcatSqlQuery(sqlQueryTemplate, sqlWhereClause, sqlHavingClause , sqlOrderClause);
return sqlQuery;
}
public XmlNode ViewRealTimeData(string entityName, string[] selectFields, SearchField[] searchFields, string[] orderFields, int rowsPerPage, int pageNumber, NorthwindConfig config)
{
// declarations
string sqlQuery;
DataSet resultDataSet;
XmlDocument resultXmlDoc;
SearchField[] whereFieldList;
SearchField[] havingFieldList;
List<OleDbParameter> oleDbParameterList;
// initializations
resultDataSet = null;
oleDbParameterList = null;
// fill the where and having field lists
whereFieldList = this.GetWhereFields(searchFields);
havingFieldList = this.GetHavingFields(searchFields);
// Create an OleDbCommand .
sqlQuery = this.CreateSqlQuery(whereFieldList, havingFieldList, orderFields, out oleDbParameterList);
// Get data from database
this.Fill(sqlQuery, oleDbParameterList, config, out resultDataSet);
// Convert result xml document to the CRM contract format
resultXmlDoc = this.ConvertToXmlDocument(resultDataSet, selectFields, pageNumber, rowsPerPage);
// return the root node xml document
return (XmlNode)resultXmlDoc.DocumentElement;
}
protected override SearchField[] GetWhereFields(SearchField[] searchFields)
{
List<SearchField> whereFields;
// initializations
whereFields = new List<SearchField>();
// iterate through all search fields and copy a reference of the 'where' fields to the result list.
foreach (SearchField field in searchFields)
{
if (field.Name == "Type" || field.Name == "CreatedBy" || field.Name == "DatePaymentDue")
{
whereFields.Add(field);
}
}
return whereFields.ToArray();
}
protected override SearchField[] GetHavingFields(SearchField[] searchFields)
{
List<SearchField> havingFields;
// initializations
havingFields = new List<SearchField>();
// iterate through all search fields and copy a reference of the 'having' fields to the result list.
foreach (SearchField field in searchFields)
{
if (field.Name == "OrderID" || field.Name == "Value" || field.Name == "OrderDate" || field.Name == "AccountID" || field.Name == "intforeignid")
{
havingFields.Add(field);
}
}
return havingFields.ToArray();
}
