www.pudn.com > Tustena_CRM_OS_3.0.2_Stable.zip > QueryBuilderManager.cs
/// TUSTENA PUBLIC LICENSE v1.0
///
/// Portions Copyright (c) 2003-2005 Digita S.r.l. All Rights Reserved.
///
/// Tustena CRM is a trademark of:
/// Digita S.r.l.
/// Viale Enrico Fermi 14/z
/// 31011 Asolo (Italy)
/// Tel. +39-0423-951251
/// Mail. info@digita.it
///
/// This file contains Original Code and/or Modifications of Original Code
/// as defined in and that are subject to the Tustena Public Source License
/// Version 1.0 (the 'License'). You may not use this file except in
/// compliance with the License. Please obtain a copy of the License at
/// http://www.tustena.com/TPL/ and read it before using this
// file.
///
/// The Original Code and all software distributed under the License are
/// distributed on an 'AS IS' basis, WITHOUT WARRANTY OF ANY KIND, EITHER
/// EXPRESS OR IMPLIED, AND DIGITA S.R.L. HEREBY DISCLAIMS ALL SUCH WARRANTIES,
/// INCLUDING WITHOUT LIMITATION, ANY WARRANTIES OF MERCHANTABILITY,
/// FITNESS FOR A PARTICULAR PURPOSE, QUIET ENJOYMENT OR NON-INFRINGEMENT.
/// Please see the License for the specific language governing rights and
/// limitations under the License.
///
/// YOU MAY NOT REMOVE OR ALTER THIS COPYRIGHT NOTICE!
///
using System;
using System.Collections;
using System.Data;
using System.Diagnostics;
using System.Text;
using System.Web;
using Digita.Tustena.Base;
using Digita.Tustena.Database;
namespace Digita.Tustena
{
public class QueryBuilderManager
{
public DataTable QBManager(int id, Hashtable htParams, bool fromId)
{
return QBManager(id, htParams, -1, ((UserConfig) HttpContext.Current.Session["UserConfig"]).UserGroupId, fromId);
}
public DataTable QBManager(int id, Hashtable htParams)
{
return QBManager(id, htParams, -1, ((UserConfig) HttpContext.Current.Session["UserConfig"]).UserGroupId, true);
}
public DataTable QBManager(int id, Hashtable htParams, int companyId, int groupId, bool fromId)
{
string idQuery = String.Empty;
if (fromId)
idQuery = id.ToString();
else
idQuery = DatabaseConnection.SqlScalar("select id from QB_CustomerQuery where fromwap=" + id.ToString());
string grouping = String.Empty;
string orderby = String.Empty;
string tblstring = String.Empty;
int tblindex = 0;
UserConfig ucCurrent = (UserConfig) HttpContext.Current.Session["UserConfig"];
bool orderAndGroup = false;
{
StringBuilder TablesString = new StringBuilder();
StringBuilder fieldsString = new StringBuilder();
StringBuilder ParamsString = new StringBuilder();
StringBuilder finalQuery = new StringBuilder();
string firstTable = String.Empty;
string qbTablesQuery = "Select QB_All_Tables.ID, QB_All_Tables.TableName, QB_All_Tables.FixedQuery " +
"from QB_CustomerQueryTables " +
"inner join QB_All_Tables on QB_CustomerQueryTables.IDTable=QB_All_Tables.ID " +
"where QB_CustomerQueryTables.IDQuery=" + idQuery + " order by QB_CustomerQueryTables.MainTable desc";
DataSet QBTables = DatabaseConnection.CreateDataset(qbTablesQuery);
if (QBTables.Tables[0].Rows.Count > 0)
{
TablesString.Append(QBTables.Tables[0].Rows[0][1].ToString());
firstTable = QBTables.Tables[0].Rows[0][1].ToString();
if (QBTables.Tables[0].Rows.Count > 1)
foreach (DataRow d in QBTables.Tables[0].Rows)
{
string sqlJoin = "SELECT QB_All_Tables.TableName as FirstTableName,QB_All_Tables.fixedquery, QB_All_Tables_1.TableName AS SecondTableName, QB_Join.FirstField, QB_Join.SecondField, QB_Join.type " +
"FROM QB_Join INNER JOIN " +
"QB_All_Tables ON QB_Join.FirstTableID = QB_All_Tables.id INNER JOIN " +
"QB_All_Tables QB_All_Tables_1 ON QB_Join.SecondTableID = QB_All_Tables_1.id " +
"where QB_Join.SecondTableID=" + QBTables.Tables[0].Rows[0][0].ToString() + " and QB_Join.FirstTableID=" + d[0].ToString();
DataSet QBJoin = DatabaseConnection.CreateDataset(sqlJoin);
if (QBJoin.Tables[0].Rows.Count > 0)
{
DataRow jd = QBJoin.Tables[0].Rows[0];
switch ((byte) jd["type"])
{
case 0:
string fixedq = jd["fixedquery"].ToString();
if(fixedq.Length>0)fixedq=" and "+fixedq;
TablesString.AppendFormat(" LEFT OUTER JOIN {0} as {4} ON {1}.{2} = {4}.{3}{5}", jd["FirstTableName"], jd["SecondTableName"], jd["SecondField"], jd["FirstField"], (tblstring.IndexOf(jd["FirstTableName"].ToString()) != -1) ? jd["FirstTableName"].ToString() + tblindex++.ToString() : jd["FirstTableName"].ToString(),fixedq);
break;
case 1:
TablesString.AppendFormat(" LEFT OUTER JOIN {0} as {4} ON {1}.{2} like '%|'+convert(varchar(10),{4}.{3})+'|%'", jd["FirstTableName"], jd["SecondTableName"], jd["SecondField"], jd["FirstField"], (tblstring.IndexOf(jd["FirstTableName"].ToString()) != -1) ? jd["FirstTableName"].ToString() + tblindex++.ToString() : jd["FirstTableName"].ToString());
break;
}
tblstring += jd["FirstTableName"] + "|";
}
}
}
else
{
}
string qf = "select qb_all_fields.rmValue,qb_all_fields.field,qb_all_fields.JoinID,qb_all_fields.ParentField,qb_all_fields.FieldType,qb_all_tables.TableName,qb_customerqueryfields.ColumnName,qb_all_fields.ID,qb_all_fields.tableId from qb_customerqueryfields " +
"left outer join qb_all_fields on qb_customerqueryfields.IDField=qb_all_fields.id " +
"left outer join qb_all_tables on qb_all_fields.tableId=qb_all_tables.id " +
"where qb_customerqueryfields.IDQuery=" + idQuery + " and qb_customerqueryfields.FieldVisible=1 and QB_CustomerQueryFields.IDTable>0"; // and IDTable="+d[0].ToString();
DataSet qpFieldsQuery = DatabaseConnection.CreateDataset(qf);
string tableIdForId = String.Empty;
foreach (DataRow fi in qpFieldsQuery.Tables[0].Rows)
{
string tableId = DatabaseConnection.SqlScalar("select TableName from QB_All_Tables where id=" + fi["TableID"].ToString());
if (tableIdForId.IndexOf(fi["TableID"].ToString() + "|") < 0)
{
switch (fi["TableID"].ToString())
{
case "1":
fieldsString.AppendFormat("'AID' as rmAID,{0}.ID as AID,", tableId);
grouping += String.Format("{0}.ID,", tableId);
break;
case "2":
fieldsString.AppendFormat("'CID' as rmCID,{0}.ID as CID,", tableId);
grouping += String.Format("{0}.ID,", tableId);
break;
case "78":
fieldsString.AppendFormat("'LID' as rmLID,{0}.ID as LID,", tableId);
grouping += String.Format("{0}.ID,", tableId);
break;
}
tableIdForId += fi["TableID"].ToString() + "|";
}
if (fi[2] == DBNull.Value)
switch (Convert.ToInt32(fi[4]))
{
case 7:
fieldsString.AppendFormat("'{2}' as rm{1},sum({0}.{1}) as {1},", tableId, fi[1].ToString(), (fi["ColumnName"].ToString().Length > 0) ? fi["ColumnName"].ToString() : fi[0].ToString());
orderAndGroup = true;
break;
case 13:
fieldsString.AppendFormat("'{2}' as rmNR${1},(convert(varchar(50),{0}.{1})+'|{3}') as NR${1},", tableId, fi[1].ToString(), (fi["ColumnName"].ToString().Length > 0) ? fi["ColumnName"].ToString() : fi[0].ToString(), fi["ID"].ToString());
grouping += String.Format("{0}.{1},", tableId, fi[1].ToString());
break;
case 6:
fieldsString.AppendFormat("'{2}' as rm{1},CAST({0}.{1} as varchar(8000)) as {1},", tableId, fi[1].ToString(), (fi["ColumnName"].ToString().Length > 0) ? fi["ColumnName"].ToString() : fi[0].ToString());
grouping += String.Format("CAST({0}.{1} as varchar(8000)),", tableId, fi[1].ToString());
break;
case 3:
case 8:
fieldsString.AppendFormat("'{2}' as rmDA${1},{0}.{1} as DA${1},", tableId, fi[1].ToString(), (fi["ColumnName"].ToString().Length > 0) ? fi["ColumnName"].ToString() : fi[0].ToString());
grouping += String.Format("{0}.{1},", tableId, fi[1].ToString());
break;
case 2: // Company categories
fieldsString.AppendFormat("'{2}' as rmCA${1},{0}.{1} as CA${1},", tableId, fi[1].ToString(), (fi["ColumnName"].ToString().Length > 0) ? fi["ColumnName"].ToString() : fi[0].ToString());
grouping += String.Format("{0}.{1},", tableId, fi[1].ToString());
break;
case 14: // Attivit todo
fieldsString.AppendFormat("'{2}' as rmAT${1},{0}.{1} as AT${1},", tableId, fi[1].ToString(), (fi["ColumnName"].ToString().Length > 0) ? fi["ColumnName"].ToString() : fi[0].ToString());
grouping += String.Format("{0}.{1},", tableId, fi[1].ToString());
break;
default:
fieldsString.AppendFormat("'{2}' as rm{1},{0}.{1},", tableId, fi[1].ToString(), (fi["ColumnName"].ToString().Length > 0) ? fi["ColumnName"].ToString() : fi[0].ToString());
grouping += String.Format("{0}.{1},", tableId, fi[1].ToString());
break;
}
else
{
string SqlJoinField = "SELECT QB_All_Tables.TableName as FirstTableName, QB_All_Tables_1.TableName AS SecondTableName, QB_Join.FirstField, QB_Join.SecondField, QB_Join.type, QB_Join.astable " +
"FROM QB_Join INNER JOIN " +
"QB_All_Tables ON QB_Join.FirstTableID = QB_All_Tables.id INNER JOIN " +
"QB_All_Tables QB_All_Tables_1 ON QB_Join.SecondTableID = QB_All_Tables_1.id " +
"where QB_Join.ID=" + fi["JoinID"].ToString();
DataSet drjoin = DatabaseConnection.CreateDataset(SqlJoinField);
DataRow jd = drjoin.Tables[0].Rows[0];
string tempTableName = (tblstring.IndexOf(jd["FirstTableName"].ToString()) != -1) ? jd["FirstTableName"].ToString() + tblindex++.ToString() : jd["FirstTableName"].ToString();
tblstring += jd["FirstTableName"] + "|";
switch ((byte) jd["type"])
{
case 0:
TablesString.AppendFormat(" LEFT OUTER JOIN {0} as {4} ON {1}.{2} = {4}.{3}", jd["FirstTableName"], jd["SecondTableName"], jd["SecondField"], jd["FirstField"], tempTableName);
break;
case 1:
TablesString.AppendFormat(" LEFT OUTER JOIN {0} as {4} ON {1}.{2} like '%|'+convert(varchar(10),{4}.{3})+'|%'", jd["FirstTableName"], jd["SecondTableName"], jd["SecondField"], jd["FirstField"], tempTableName);
break;
case 2:
UserConfig UC = (UserConfig) HttpContext.Current.Session["UserConfig"];
TablesString.AppendFormat(" LEFT OUTER JOIN {0} as {4} ON {1}.{2} = {4}.{3} and {4}.lang='{5}'", jd["FirstTableName"], jd["SecondTableName"], jd["SecondField"], jd["FirstField"], tempTableName,UC.Culture.Substring(0,2));
break;
}
if (fi[3].ToString().IndexOf(",") > 0)
{
string[] pf = fi[3].ToString().Split(',');
if(tempTableName.ToLower().IndexOf("lead")>=0)
fieldsString.AppendFormat("'{2}' as rm{1},(isnull({0}.{3},'')+' '+isnull({0}.{4},'')) as {1},", tempTableName, fi[1].ToString(), (fi["ColumnName"].ToString().Length > 0) ? fi["ColumnName"].ToString() : fi[0].ToString(), pf[0], pf[1]);
else
fieldsString.AppendFormat("'{2}' as rm{1},(isnull({0}.{3},'')) as {1},", tempTableName, fi[1].ToString(), (fi["ColumnName"].ToString().Length > 0) ? fi["ColumnName"].ToString() : fi[0].ToString(), pf[0], pf[1]);
grouping += String.Format("{0}.{1},{0}.{2},", tempTableName, pf[0], pf[1]);
}
else
{
fieldsString.AppendFormat("'{2}' as rm{1},{0}.{3} as {1},", tempTableName, fi[1].ToString(), (fi["ColumnName"].ToString().Length > 0) ? fi["ColumnName"].ToString() : fi[0].ToString(), fi[3].ToString());
grouping += String.Format("{0}.{1},", tempTableName, fi[3].ToString());
}
}
}
if (fieldsString.Length > 0) fieldsString.Remove(fieldsString.Length - 1, 1);
foreach (DataRow d in QBTables.Tables[0].Rows)
{
int idfield = -1;
object vvv = DatabaseConnection.SqlScalartoObj("select IDField from QB_CustomerQueryParamFields where IDQuery=" + idQuery + " and IDTable=" + d[0].ToString());
if (vvv != null)
idfield = (int) vvv;
if (idfield > 0)
{
string qfp = "select qb_all_fields.rmValue,qb_all_fields.field,qb_all_fields.FieldType,qb_all_fields.JoinID,qb_all_fields.ParentField from QB_CustomerQueryParamFields " +
"inner join qb_all_fields on QB_CustomerQueryParamFields.IDField=qb_all_fields.id " +
"where QB_CustomerQueryParamFields.IDQuery=" + idQuery + " and QB_CustomerQueryParamFields.IDTable=" + d[0].ToString();
DataSet qpFieldsQueryP = DatabaseConnection.CreateDataset(qfp);
foreach (DataRow f in qpFieldsQueryP.Tables[0].Rows)
{
foreach (DictionaryEntry myDE in htParams)
{
if (myDE.Key.ToString().ToLower() == f[1].ToString().ToLower())
{
string table0 = d[1].ToString();
string field1 = f[1].ToString();
string field2 = String.Empty;
if (f["JoinID"].ToString().Length > 0)
{
string SqlJoinFieldG = "SELECT QB_All_Tables.TableName as FirstTableName, QB_All_Tables_1.TableName AS SecondTableName, QB_Join.FirstField, QB_Join.SecondField " +
"FROM QB_Join INNER JOIN " +
"QB_All_Tables ON QB_Join.FirstTableID = QB_All_Tables.id INNER JOIN " +
"QB_All_Tables QB_All_Tables_1 ON QB_Join.SecondTableID = QB_All_Tables_1.id " +
"where QB_Join.ID=" + f["JoinID"].ToString();
DataTable drjoin = DatabaseConnection.CreateDataset(SqlJoinFieldG).Tables[0];
table0 = drjoin.Rows[0][0].ToString();
field1 = f["ParentField"].ToString();
field2 = drjoin.Rows[0]["SecondField"].ToString();
}
if (myDE.Value.ToString().Length > 0)
{
string andOr = (fromId) ? "and" : "or";
switch (f[2].ToString())
{
case "0": //textbox (like)
if (myDE.Value.ToString().IndexOf("|") > 0)
{
string[] tempQuery = myDE.Value.ToString().Split('|');
string query = String.Empty;
foreach (string t in tempQuery)
{
if (field1.IndexOf(",") > 0)
{
string[] morefields = field1.Split(',');
foreach (string mf in morefields)
{
query += String.Format("{0}.{1} like '{2}%' or ", table0, mf, t);
}
}
else
query += String.Format("{0}.{1} like '{2}%' or ", table0, field1, t);
}
ParamsString.AppendFormat(" {1} ({0})", query.Substring(0, query.Length - 3), andOr);
}
else
{
string query = String.Empty;
if (field1.IndexOf(",") > 0)
{
string[] morefields = field1.Split(',');
foreach (string mf in morefields)
{
query += String.Format("{0}.{1} like '{2}%' or ", table0, mf, myDE.Value);
}
}
else
query += String.Format("{0}.{1} like '{2}%' or ", table0, field1, myDE.Value);
ParamsString.AppendFormat(" {1} ({0})", query.Substring(0, query.Length - 3), andOr);
}
break;
case "1": //dropdown con =
case "13":
if (myDE.Value.ToString().IndexOf("|") > 0)
{
string[] tempQuery = myDE.Value.ToString().Split('|');
string query = String.Empty;
foreach (string t in tempQuery)
{
query += String.Format("{0}.{1} = '{2}' or ", d[1].ToString(), (field2.Length>0)?field2:f[1].ToString(), t);
}
ParamsString.AppendFormat(" {1} ({0})", query.Substring(0, query.Length - 3), andOr);
}
else
{
if (f["ParentField"] == DBNull.Value)
{
ParamsString.AppendFormat(" {3} {0}.{1} = '{2}'", d[1].ToString(), f[1].ToString(), myDE.Value, andOr);
}
else
{
ParamsString.AppendFormat(" {3} {0}.{1} = '{2}'", d[1].ToString(), field2, myDE.Value, andOr);
}
}
break;
case "2": //dropdown con like e pipe
if (myDE.Value.ToString().IndexOf("|") > 0)
{
string[] tempQuery = myDE.Value.ToString().Split('|');
string query = String.Empty;
foreach (string t in tempQuery)
{
query += String.Format("{0}.{1} like '%|{2}|%' or ", table0, field1, t);
}
ParamsString.AppendFormat(" {1} ({0})", query.Substring(0, query.Length - 3), andOr);
}
else
ParamsString.AppendFormat(" {3} {0}.{1} like '%|{2}|%'", d[1].ToString(), f[1].ToString(), myDE.Value, andOr);
break;
case "3": //data secca
ParamsString.AppendFormat(" {3} Convert(varchar(10),{0}.{1},112)='{2}'", d[1].ToString(), f[1].ToString(), ucCurrent.LTZ.ToUniversalTime(Convert.ToDateTime(myDE.Value, ucCurrent.myDTFI)).ToString(@"yyyyMMdd"), andOr);
break;
case "4": //checkbox
if (myDE.Value != null)
{
ParamsString.AppendFormat(" {2} {0}.{1} = 1", d[1].ToString(), f[1].ToString(), andOr);
}
else
{
ParamsString.AppendFormat(" {2} {0}.{1} = 0", d[1].ToString(), f[1].ToString(), andOr);
}
break;
case "5": // risearch per propietario
ParamsString.AppendFormat(" {3} {0}.{1} = '{2}'", d[1].ToString(), f[1].ToString(), myDE.Value, andOr);
break;
case "8": //data between
string[] valori = myDE.Value.ToString().Split('|');
ParamsString.AppendFormat(" {4} {0}.{1} between '{2}' and '{3}'", d[1].ToString(), f[1].ToString(), ucCurrent.LTZ.ToUniversalTime(Convert.ToDateTime(valori[0], ucCurrent.myDTFI)).ToString(@"yyyyMMdd"), Convert.ToDateTime(valori[1], ucCurrent.myDTFI).ToString(@"yyyyMMdd"), andOr);
break;
case "9": // risearch per aziende
ParamsString.AppendFormat(" {3} {0}.{1} = '{2}'", d[1].ToString(), f[1].ToString(), myDE.Value, andOr);
break;
case "10": // risearch per contatto
ParamsString.AppendFormat(" {3} {0}.{1} = '{2}'", d[1].ToString(), f[1].ToString(), myDE.Value, andOr);
break;
case "11": // risearch per opportunit
ParamsString.AppendFormat(" {3} {0}.{1} like '%|{2}|%'", d[1].ToString(), f[1].ToString(), myDE.Value, andOr);
break;
case "12": // risearch per opportunit
valori = myDE.Value.ToString().Split('|');
if (valori[1] == "0")
{
ParamsString.AppendFormat(" {3} {0}.{1} = {2}", d[1].ToString(), f[1].ToString(), valori[0], andOr);
}
else
{
ParamsString.AppendFormat(" {3} {0}.{1} > {2}", d[1].ToString(), f[1].ToString(), valori[0], andOr);
}
break;
case "14": // Radio Button
ParamsString.AppendFormat(" {3} {0}.{1} = {2}", d[1].ToString(), f[1].ToString(), myDE.Value, andOr);
break;
}
}
}
}
}
}
else
{
if (idfield == 0)
{
foreach (DictionaryEntry myDE in htParams)
{
ParamsString.AppendFormat(" and {0}.ID = {1}", d[1].ToString(), myDE.Value);
}
}
}
}
foreach (DataRow d in QBTables.Tables[0].Rows)
{
if (d["FixedQuery"].ToString().Length > 0 && TablesString.ToString().IndexOf(d["FixedQuery"].ToString())<0)
{
ParamsString.AppendFormat(" and ({0})", d["FixedQuery"].ToString());
}
}
finalQuery.AppendFormat("select {0} ", fieldsString.ToString());
finalQuery.AppendFormat("from {0} ", TablesString.ToString());
finalQuery.Append("where ");
if (fromId)
if((finalQuery.ToString().EndsWith("where ") ||
finalQuery.ToString().EndsWith("where"))
)
if((ParamsString.ToString().StartsWith(" and") ||
ParamsString.ToString().StartsWith("and")))
finalQuery.AppendFormat("{0}", ParamsString.ToString().Substring(4, ParamsString.Length - 4));
else
finalQuery.AppendFormat("{0}", ParamsString.ToString());
else
if((ParamsString.ToString().StartsWith(" and") ||
ParamsString.ToString().StartsWith("and")) || StaticFunctions.IsBlank(ParamsString.ToString()))
finalQuery.AppendFormat("{0}", ParamsString.ToString());
else
finalQuery.AppendFormat(" and {0}", ParamsString.ToString());
else
if((finalQuery.ToString().EndsWith("where ") ||
finalQuery.ToString().EndsWith("where"))
)
finalQuery.AppendFormat("{0}", ParamsString.ToString().Substring(3, ParamsString.Length - 3));
else
finalQuery.AppendFormat("and ({0})", ParamsString.ToString().Substring(3, ParamsString.Length - 3));
string groupby = DatabaseConnection.SqlScalar("select GroupBy from QB_CustomerQuery where ID=" + idQuery);
if (groupby.Length > 0 || orderAndGroup)
{
string[] t;
if(groupby.Length > 0)
t = groupby.Split('|');
else
{
groupby = DatabaseConnection.SqlScalar("select top 1 cast(idtable as varchar)+'|'+cast(idfield as varchar) as groupby from QB_CustomerQueryFields where idQuery="+idQuery+" order by options");
t = groupby.Split('|');
}
string qbGroup = "select qb_all_tables.tablename,qb_all_fields.field as groupby,qb_all_fields.JoinID,qb_all_fields.ParentField from qb_all_tables " +
"join qb_all_fields on qb_all_tables.id=qb_all_fields.tableid " +
"where qb_all_tables.id=" + t[0] + " and qb_all_fields.id=" + t[1] + ";";
DataTable dtGroup = DatabaseConnection.CreateDataset(qbGroup).Tables[0];
string groupFor = String.Empty;
if (dtGroup.Rows[0]["JoinID"].ToString().Length > 0)
{
string SqlJoinFieldG = "SELECT QB_All_Tables.TableName as FirstTableName, QB_All_Tables_1.TableName AS SecondTableName, QB_Join.FirstField, QB_Join.SecondField " +
"FROM QB_Join INNER JOIN " +
"QB_All_Tables ON QB_Join.FirstTableID = QB_All_Tables.id INNER JOIN " +
"QB_All_Tables QB_All_Tables_1 ON QB_Join.SecondTableID = QB_All_Tables_1.id " +
"where QB_Join.ID=" + dtGroup.Rows[0]["JoinID"].ToString();
DataTable drjoin = DatabaseConnection.CreateDataset(SqlJoinFieldG).Tables[0];
if (dtGroup.Rows[0]["ParentField"].ToString().IndexOf(",") > 0)
{
string[] compositegroup = dtGroup.Rows[0]["ParentField"].ToString().Split(',');
foreach (string cg in compositegroup)
{
groupFor += drjoin.Rows[0][0].ToString() + "." + cg + ",";
}
groupFor = groupFor.Substring(0, groupFor.Length - 1);
}
else
groupFor = drjoin.Rows[0][0].ToString() + "." + dtGroup.Rows[0]["ParentField"].ToString();
}
else
{
if (dtGroup.Rows[0][1].ToString().IndexOf(",") > 0)
{
string[] compositegroup = dtGroup.Rows[0][1].ToString().Split(',');
foreach (string cg in compositegroup)
{
groupFor += dtGroup.Rows[0][0].ToString() + "." + cg + ",";
}
groupFor = groupFor.Substring(0, groupFor.Length - 1);
}
else
groupFor = dtGroup.Rows[0][0].ToString() + "." + dtGroup.Rows[0][1].ToString();
}
if (grouping.IndexOf(groupFor) > 0)
{
grouping.Remove(grouping.IndexOf(groupFor), groupFor.Length + 1);
}
if (orderAndGroup)
{
if (grouping.Length > 0)
finalQuery.AppendFormat(" group by {0},{1}", groupFor, grouping.Substring(0, grouping.Length - 1));
else
finalQuery.AppendFormat(" group by {0}", groupFor);
}
else
{
finalQuery.AppendFormat(" order by {0}", groupFor);
}
}
if (orderby.Length > 0 && finalQuery.ToString().IndexOf("group by") < 1)
finalQuery.AppendFormat(" order by {0}", orderby);
DataSet finalDs = DatabaseConnection.CreateDataset(finalQuery.ToString());
G.FixDateTimeZone(finalDs,ucCurrent.LTZ);
DataTable labels = new DataTable();
DataTable freeLabels = new DataTable();
if (finalDs.Tables[0].Rows.Count > 0)
{
string fieldString = String.Empty;
int fieldIndex = 0;
DataRow drlabels = finalDs.Tables[0].Rows[0];
foreach (DataColumn cc in finalDs.Tables[0].Columns)
{
if (cc.ColumnName.Substring(0, 2) == "rm")
{
DataColumn dcDynColumn = new DataColumn();
string columnName = G.rm.GetString("QBTxt" + drlabels[cc.ColumnName].ToString());
if (columnName != null)
{
if (fieldString.IndexOf(columnName + "|") != -1)
dcDynColumn.ColumnName = columnName + "_" + fieldIndex++;
else
dcDynColumn.ColumnName = columnName;
fieldString += columnName + "|";
}
else
{
dcDynColumn.ColumnName = drlabels[cc.ColumnName].ToString();
fieldString += drlabels[cc.ColumnName].ToString() + "|";
}
dcDynColumn.DataType = Type.GetType("System.String");
labels.Columns.Add(dcDynColumn);
}
}
foreach (DataRow dd in finalDs.Tables[0].Rows)
{
DataRow d = labels.NewRow();
foreach (DataColumn cc in finalDs.Tables[0].Columns)
{
if (cc.ColumnName.Substring(0, 2) != "rm")
{
switch (cc.ColumnName.Substring(0, 3))
{
case "AID":
case "CID":
case "LID":
d[dd["rm" + cc.ColumnName].ToString()] = dd[cc.ColumnName].ToString();
break;
case "DA$":
try
{
d[G.rm.GetString("QBTxt" + dd["rm" + cc.ColumnName].ToString())] = Convert.ToDateTime(dd[cc.ColumnName].ToString()).ToString();
}
catch
{
try
{
d[dd["rm" + cc.ColumnName].ToString()] = Convert.ToDateTime(dd[cc.ColumnName].ToString()).ToString();
}
catch
{
d[G.rm.GetString("QBTxt" + dd["rm" + cc.ColumnName].ToString())] = DBNull.Value;
}
}
break;
case "NR$":
try
{
if (dd[cc.ColumnName].ToString().Length > 0)
{
string[] Nrm = dd[cc.ColumnName].ToString().Split('|');
string Nrmvalue = DatabaseConnection.SqlScalar("select rmvalue from QB_FixedDropDownParams where idrif=" + Nrm[1] + " and dropvalue=" + Nrm[0]);
d[G.rm.GetString("QBTxt" + dd["rm" + cc.ColumnName].ToString())] = G.rm.GetString("QBTxt" + Nrmvalue);
}
else
d[G.rm.GetString("QBTxt" + dd["rm" + cc.ColumnName].ToString())] = String.Empty;
}
catch
{
d[dd["rm" + cc.ColumnName].ToString()] = dd[cc.ColumnName].ToString();
}
break;
case "CA$":
try
{
string[] Cat = dd[cc.ColumnName].ToString().Split('|');
string queryCat = String.Empty;
foreach (string c in Cat)
{
if (c.Length > 0)
queryCat += " id=" + c + " or ";
}
DataTable catdt = DatabaseConnection.CreateDataset("select description from CRM_ContactCategories where " + queryCat.Substring(0, queryCat.Length - 3)).Tables[0];
string catList = String.Empty;
foreach (DataRow dr in catdt.Rows)
{
catList += dr[0].ToString() + ",";
}
d[G.rm.GetString("QBTxt" + dd["rm" + cc.ColumnName].ToString())] = catList;
}
catch
{
}
break;
case "AT$":
string todo=string.Empty;
switch(dd[cc.ColumnName].ToString())
{
case "1":
todo=G.rm.GetString("Acttxt71");
break;
case "0":
todo=G.rm.GetString("Acttxt72");
break;
case "2":
todo=G.rm.GetString("Acttxt103");
break;
}
d[G.rm.GetString("QBTxt" + dd["rm" + cc.ColumnName].ToString())] = todo;
break;
default:
try
{
d[G.rm.GetString("QBTxt" + dd["rm" + cc.ColumnName].ToString())] = dd[cc.ColumnName].ToString();
}
catch
{
d[dd["rm" + cc.ColumnName].ToString()] = dd[cc.ColumnName].ToString();
}
break;
}
}
}
labels.Rows.Add(d);
}
string fieldsFlag = String.Empty;
bool toDelete = true;
foreach (DataRow d in QBTables.Tables[0].Rows)
{
StringBuilder FreeFields = new StringBuilder();
string wichid = String.Empty;
string qff = "select AddedFields.Name,AddedFields.ID " +
"from qb_customerqueryfields " +
"INNER JOIN AddedFields ON qb_customerqueryfields.IDField = AddedFields.ID " +
"where qb_customerqueryfields.IDQuery=" + idQuery + " and IDTable=-" + d[0].ToString();
DataSet qpFieldsQueryF = DatabaseConnection.CreateDataset(qff);
if (qpFieldsQueryF.Tables[0].Rows.Count > 0)
{
DataColumn[] newcolumns = new DataColumn[qpFieldsQueryF.Tables[0].Rows.Count + 1];
int i = 0;
foreach (DataRow fi in qpFieldsQueryF.Tables[0].Rows)
{
newcolumns[i++] = new DataColumn(fi[0].ToString(), Type.GetType("System.String"));
}
switch (d[0].ToString())
{
case "1":
wichid = "AID";
break;
case "2":
wichid = "CID";
break;
case "78":
wichid = "LID";
break;
}
fieldsFlag += wichid + "|";
newcolumns[i++] = new DataColumn(wichid, Type.GetType("System.String"));
freeLabels.Columns.AddRange(newcolumns);
DataTable freeparams = DatabaseConnection.CreateDataset("select IDField from QB_CustomerQueryParamFields where IDQuery=" + idQuery + " and IDTable=-" + d[0].ToString()).Tables[0];
if (freeparams.Rows.Count > 0)
{
toDelete = false;
FreeFields.Append("select seconda.* from (");
FreeFields.AppendFormat("SELECT AddedFields.Name, AddedFields_Cross.FIELDVAL, AddedFields_Cross.ID as {0} ", wichid);
FreeFields.Append("FROM AddedFields ");
FreeFields.Append("INNER JOIN AddedFields_Cross ON AddedFields.ID = AddedFields_Cross.IDRif ");
FreeFields.Append("inner join qb_customerqueryfields ON AddedFields.ID=qb_customerqueryfields.IDField ");
FreeFields.AppendFormat("WHERE (ADDEDFIELDS.TableName = {0}) and qb_customerqueryfields.IDQuery={1} and ", d[0].ToString(), idQuery);
FreeFields.Append("AddedFields.ID=qb_customerqueryfields.IDField ");
foreach (DictionaryEntry myDE in htParams)
{
if (myDE.Key.ToString().Substring(0, 1) == "-")
{
FreeFields.AppendFormat("and AddedFields.NAME='{0}' and AddedFields_Cross.FIELDVAL like '%{1}%' ", myDE.Key.ToString().Substring(1, myDE.Key.ToString().Length - 1), myDE.Value.ToString());
}
}
FreeFields.Append(") as prima ");
FreeFields.Append("left outer join (");
FreeFields.AppendFormat("SELECT AddedFields.Name, AddedFields_Cross.FIELDVAL, AddedFields_Cross.ID as {0} ", wichid);
FreeFields.Append("FROM AddedFields ");
FreeFields.Append("INNER JOIN AddedFields_Cross ON AddedFields.ID = AddedFields_Cross.IDRif ");
FreeFields.Append("inner join qb_customerqueryfields ON AddedFields.ID=qb_customerqueryfields.IDField ");
FreeFields.AppendFormat("WHERE (ADDEDFIELDS.TableName = {0}) and qb_customerqueryfields.IDQuery={1} and ", d[0].ToString(), idQuery);
FreeFields.Append("AddedFields.ID=qb_customerqueryfields.IDField ");
FreeFields.AppendFormat(") as seconda on prima.{0} = seconda.{0} order by seconda.AID,seconda.name", wichid);
}
else
{
FreeFields.AppendFormat("SELECT AddedFields.Name, AddedFields_Cross.FIELDVAL, AddedFields_Cross.ID as {0} ", wichid);
FreeFields.Append("FROM AddedFields ");
FreeFields.Append("INNER JOIN AddedFields_Cross ON AddedFields.ID = AddedFields_Cross.IDRif ");
FreeFields.Append("inner join qb_customerqueryfields ON AddedFields.ID=qb_customerqueryfields.IDField ");
FreeFields.AppendFormat("WHERE (ADDEDFIELDS.TableName = {0}) and qb_customerqueryfields.IDQuery={1} and ", d[0].ToString(), idQuery);
FreeFields.Append("AddedFields.ID=qb_customerqueryfields.IDField ");
FreeFields.Append("order by AddedFields_Cross.id ");
}
DataTable dtFree = DatabaseConnection.CreateDataset(FreeFields.ToString()).Tables[0];
foreach (DataRow crossid in dtFree.Rows)
{
try
{
DataRow[] tempdr = freeLabels.Select("AID=" + crossid[wichid]);
DataRow drfiledval;
if (tempdr.Length > 0)
drfiledval = tempdr[0];
else
drfiledval = freeLabels.NewRow();
drfiledval[crossid["Name"].ToString()] = crossid["fieldval"].ToString();
drfiledval[wichid] = crossid[wichid].ToString();
if (!(tempdr.Length > 0))
freeLabels.Rows.Add(drfiledval);
}
catch
{
try
{
DataRow drfiledval;
drfiledval = freeLabels.NewRow();
drfiledval[crossid["Name"].ToString()] = crossid["fieldval"].ToString();
drfiledval[wichid] = crossid[wichid].ToString();
freeLabels.Rows.Add(drfiledval);
}catch{}
}
}
}
}
if (freeLabels.Rows.Count > 0 && labels.Rows.Count > 0)
{
string[] wich = fieldsFlag.Split('|');
foreach (string field in wich)
if (field.Length > 0) DataManipulation.JoinTableByID(labels, freeLabels, field, field, toDelete);
}
}
DataColumn[] keyColumns = new DataColumn[labels.Columns.Count];
for(int dc=0;dc