public static DataTable GetAllQueryData(string sourceName, FieldGroupDetailCollection fieldGroupDetails)
{
string strSql = GridManager.GetQuerySql(sourceName, fieldGroupDetails);
List<SqlParameter> listParames = new List<SqlParameter>();
string strWhere = GetWhereSqlPermission(listParames);
if (strWhere != "")
{
strSql += " WHERE 1= 1" + strWhere;
}
return DbHelperSQL.Query(strSql, listParames.ToArray()).Tables[0];
}
public static string GetExportData(HttpRequestBase request, FieldGroup fieldGroup)
{
DataSet ds = new DataSet();
FieldGroupDetailCollection fields = fieldGroup.GetFieldsByUser(AccessControl.CurrentLogonUser.Uid);
FieldGroupDetailCollection mainFields = new FieldGroupDetailCollection();
Dictionary<string, FieldGroupDetailCollection> subFields = new Dictionary<string, FieldGroupDetailCollection>();
string strSql = GridManager.GetQuerySql(fieldGroup.SourceName, fieldGroup.GetFieldsByUser(AccessControl.CurrentLogonUser.Uid));
FieldGroupDetailCollection relationFields = new FieldGroupDetailCollection();
if (!String.IsNullOrEmpty(fieldGroup.SubRelationFields))
{
string[] rfa = fieldGroup.SubRelationFields.Split(',');
if (rfa != null && rfa.Length > 0)
{
foreach (string rf in rfa)
{
FieldGroupDetail rfd = fields.Find(t => String.Compare(t.FieldName, rf.Trim(), true) == 0);
if (rfd != null)
{
relationFields.Add(rfd);
}
}
}
}
foreach (FieldGroupDetail f in fields)
{
if (String.IsNullOrEmpty(f.SubDataType))
{
mainFields.Add(f);
}
else
{
if (!subFields.ContainsKey(f.SubDataType))
{
subFields.Add(f.SubDataType, new FieldGroupDetailCollection());
}
subFields[f.SubDataType].Add(f);
}
}
List<SqlParameter> lstParams = new List<SqlParameter>();
string strWhere = GridManager.GetWhereSql(request, fieldGroup.GroupName, mainFields, lstParams);
if (fieldGroup.Authority)
{
strWhere += GridManager.GetWhereSqlPermission(lstParams);
}
StringBuilder mainSql = new StringBuilder("SELECT ");
foreach (FieldGroupDetail field in mainFields)
{
mainSql.AppendFormat("[{0}] AS [{1}],", field.FieldName, field.DisplayName);
}
mainSql.Remove(mainSql.Length - 1, 1);
mainSql.AppendFormat(" FROM {0} AS t", fieldGroup.SourceName);
mainSql.Append(" WHERE 1=1 ").Append(strWhere);
if (AccessControl.IsVendor() &&
(fieldGroup.GroupName.ToUpper() == "VENDORRFQREPORTGRID" ||
fieldGroup.GroupName.ToUpper() == "SUPPLIERRFQGRID"))
{
string uId = AccessControl.CurrentLogonUser.Uid;
mainSql.Append(" AND RIGHT(NVARCHAR1, CHARINDEX('-', REVERSE(NVARCHAR1)) - 1) = @UserId");
lstParams.Add(new SqlParameter("@UserId", uId));
}
DataTable mainTable = DbHelperSQL.Query(mainSql.ToString(), lstParams.ToArray()).Tables[0].Copy();
mainTable.TableName = "Primary";
ds.Tables.Add(mainTable);
StringBuilder subSql = new StringBuilder();
foreach (KeyValuePair<string, FieldGroupDetailCollection> kv in subFields)
{
subSql.Clear();
subSql.Append("SELECT ");
foreach (FieldGroupDetail field in relationFields)
{
subSql.AppendFormat("t1.[{0}] AS [{1}],", field.FieldName, field.DisplayName);
}
foreach (FieldGroupDetail field in kv.Value)
{
subSql.AppendFormat("t2.[{0}] AS [{1}],", field.FieldName, field.DisplayName);
}
subSql.Remove(subSql.Length - 1, 1);
subSql.AppendFormat(" FROM {0} t1, SGP_SubData t2 WHERE t1.{1}=t2.EntityID AND EntityName = '{2}' {3}", fieldGroup.SourceName, fieldGroup.SourceKey, kv.Key, strWhere);
DataTable subTable = DbHelperSQL.Query(subSql.ToString(), lstParams.ToArray()).Tables[0].Copy();
subTable.TableName = kv.Key;
ds.Tables.Add(subTable);
}
RenderType rt = request.QueryString["renderType"] == "2" ? RenderType.Vertical : RenderType.Horizontal;
return ExcelHelper.DataSetToExcel(ds, rt);
}
public static string GetExportDataForVVI(HttpRequestBase request, FieldGroup fieldGroup)
{
DataSet ds = new DataSet();
FieldGroupDetailCollection fields = fieldGroup.GetFieldsByUser(AccessControl.CurrentLogonUser.Uid);
// FieldGroupDetailCollection mainFields = new FieldGroupDetailCollection();
FieldGroupDetailCollection allFields = new FieldGroupDetailCollection();
// Dictionary<string, FieldGroupDetailCollection> subFields = new Dictionary<string, FieldGroupDetailCollection>();
string strSql = GridManager.GetQuerySql(fieldGroup.SourceName, fieldGroup.GetFieldsByUser(AccessControl.CurrentLogonUser.Uid));
FieldGroupDetailCollection relationFields = new FieldGroupDetailCollection();
if (!String.IsNullOrEmpty(fieldGroup.SubRelationFields))
{
string[] rfa = fieldGroup.SubRelationFields.Split(',');
if (rfa != null && rfa.Length > 0)
{
foreach (string rf in rfa)
{
FieldGroupDetail rfd = fields.Find(t => String.Compare(t.FieldName, rf.Trim(), true) == 0);
if (rfd != null)
{
relationFields.Add(rfd);
}
}
}
}
foreach (FieldGroupDetail f in fields)
{
allFields.Add(f);
}
List<SqlParameter> lstParams = new List<SqlParameter>();
string strWhere = GridManager.GetWhereSql(request, fieldGroup.GroupName, allFields, lstParams);
if (fieldGroup.Authority)
{
strWhere += GridManager.GetWhereSqlPermission(lstParams);
}
StringBuilder mainSql = new StringBuilder("SELECT ");
foreach (FieldGroupDetail field in allFields)
{
mainSql.AppendFormat("[{0}] AS [{1}],", field.FieldName, field.DisplayName);
}
mainSql.Remove(mainSql.Length - 1, 1);
mainSql.AppendFormat(" FROM {0} AS t", fieldGroup.SourceName);
mainSql.Append(" WHERE 1=1 ").Append(strWhere);
DataTable mainTable = DbHelperSQL.Query(mainSql.ToString(), lstParams.ToArray()).Tables[0].Copy();
mainTable.TableName = "Primary";
ds.Tables.Add(mainTable);
//StringBuilder subSql = new StringBuilder();
//foreach (KeyValuePair<string, FieldGroupDetailCollection> kv in subFields)
//{
// subSql.Clear();
// subSql.Append("SELECT ");
// foreach (FieldGroupDetail field in relationFields)
// {
// subSql.AppendFormat("t1.[{0}] AS [{1}],", field.FieldName, field.DisplayName);
// }
// foreach (FieldGroupDetail field in kv.Value)
// {
// subSql.AppendFormat("t2.[{0}] AS [{1}],", field.FieldName, field.DisplayName);
// }
// subSql.Remove(subSql.Length - 1, 1);
// subSql.AppendFormat(" FROM {0} t1, SGP_SubData t2 WHERE t1.{1}=t2.EntityID AND EntityName = '{2}' {3}", fieldGroup.SourceName, fieldGroup.SourceKey, kv.Key, strWhere);
// DataTable subTable = DbHelperSQL.Query(subSql.ToString(), lstParams.ToArray()).Tables[0].Copy();
// subTable.TableName = kv.Key;
// ds.Tables.Add(subTable);
//}
RenderType rt = request.QueryString["renderType"] == "2" ? RenderType.Vertical : RenderType.Horizontal;
return ExcelHelper.DataSetToExcel(ds, rt);
}
public static string GetWhereSql(HttpRequestBase request, string groupName, FieldGroupDetailCollection fields, List<SqlParameter> listParames)
{
string strWhere = "";
foreach (FieldGroupDetail field in fields)
{
strWhere += GetWhereOperational(request.QueryString, field, listParames, groupName);
}
strWhere += GetWhereSqlExt(request, groupName, listParames);
return strWhere;
}
public static string GetQuerySql(string sourceName, FieldGroupDetailCollection fieldGroupDetails, params string[] extFields)
{
StringBuilder sqlBuilder = new StringBuilder("SELECT ");
if (extFields != null)
{
foreach (string extField in extFields)
{
if (!String.IsNullOrEmpty(extField) && fieldGroupDetails[extField] == null)
{
sqlBuilder.AppendFormat("{0},", extField);
}
}
}
foreach (FieldGroupDetail field in fieldGroupDetails)
{
if (String.IsNullOrEmpty(field.SubDataType))
{
if (field.DataType == FieldInfo.DATATYPE_SUMMARY)
{
sqlBuilder.AppendFormat("({0}) AS {1},", field.KeyValueSource, field.FieldName);
}
else
{
sqlBuilder.AppendFormat("{0},", field.FieldName);
}
}
}
sqlBuilder.Remove(sqlBuilder.Length - 1, 1);
sqlBuilder.AppendFormat(" FROM {0} AS t", sourceName);
return sqlBuilder.ToString();
}
/// <summary>
/// Get filtered RFQ data for supplier
/// </summary>
/// <param name="request"></param>
/// <param name="strSql"></param>
/// <param name="auth"></param>
/// <param name="fields"></param>
/// <returns></returns>
public static GridData GetGridData(HttpRequestBase request, string strSql, bool auth, FieldGroupDetailCollection fields)
{
string strWhere = "";
List<SqlParameter> listParames = new List<SqlParameter>();
string searchGroupName = request.QueryString["searchGroup"];
if (!String.IsNullOrEmpty(searchGroupName))
{
strWhere += GetWhereSql(request, searchGroupName, listParames);
}
if (auth)
{
strWhere += GetWhereSqlPermission(listParames);
}
///
foreach (FieldGroupDetail field in fields)
{
if (field.DisplayName.ToUpper() == "RFQ NUMBER")
{
string fieldName = field.FieldName;
string uId = AccessControl.CurrentLogonUser.Uid;
strWhere += string.Format(" AND RIGHT({0}, CHARINDEX('-', REVERSE({0})) - 1) = @UserId",
fieldName);
listParames.Add(new SqlParameter("@UserId", uId));
break;
}
}
if (strWhere != "")
{
strSql += " WHERE 1= 1" + strWhere;
}
return GetGridData(request, strSql, listParames.ToArray());
}
