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(); }