//
// GET: /GridData/
public string GetData(string ID)
{
FieldGroup fieldGroup = new FieldGroup(ID);
FieldGroupDetailCollection fields = fieldGroup.GetFieldsByUser(AccessControl.CurrentLogonUser.Uid);
List<TableFormatString> formatString = new List<TableFormatString>();
foreach (FieldGroupDetail field in fields)
{
if (!String.IsNullOrEmpty(field.Format))
{
formatString.Add(new TableFormatString(field.FieldName, field.Format));
}
}
string strSql = GridManager.GetQuerySql(fieldGroup.SourceName, fields, "RFQID", "StatusID");
if (fieldGroup.SourceName == "V_Supplier")
{
strSql = GridManager.GetQuerySql(fieldGroup.SourceName, fields, "ID");
}
GridData gridData = GridManager.GetGridData(Request, strSql, fieldGroup.Authority);
return gridData.ToJson(formatString.ToArray());
}
public static string GenerateQuery(string groupName)
{
StringBuilder strResult = new StringBuilder();
strResult.Append("<table style='width:100%' id='query-" + groupName + "'><tr>");
FieldGroup fieldGroup = new FieldGroup(groupName);
FieldGroupDetailCollection fieldDetails = fieldGroup.GetFieldsByUser(AccessControl.CurrentLogonUser.Uid);
for (int i = 0; i < fieldDetails.Count; i++)
{
if (i > 1 && (i % 4) == 0)
{
strResult.Append("</tr><tr>");
}
strResult.AppendFormat("<td style='width:8%' align='right'> {0} </td><td style='width:17%'>", fieldDetails[i].DisplayName);
switch (fieldDetails[i].DataType)
{
case BLL.DataModels.FieldInfo.DATATYPE_DATETIME:
strResult.Append(GenerateQueryDateRange(fieldDetails[i]));
break;
case BLL.DataModels.FieldInfo.DATATYPE_INT:
case BLL.DataModels.FieldInfo.DATATYPE_FLOAT:
case BLL.DataModels.FieldInfo.DATATYPE_DOUBLE:
strResult.Append(GenerateQueryNumberBox(fieldDetails[i]));
break;
case BLL.DataModels.FieldInfo.DATATYPE_LIST:
strResult.Append(GenerateQueryList(fieldDetails[i]));
break;
case BLL.DataModels.FieldInfo.DATATYPE_ACTIVITY:
strResult.Append(GenerateQueryActivity(fieldDetails[i]));
break;
case "ext":
strResult.Append(GenerateQueryExt(fieldDetails[i]));
break;
default:
strResult.Append(GenerateQueryTextBox(fieldDetails[i]));
break;
}
strResult.Append("</td>");
}
strResult.AppendFormat("</tr></table><input type='hidden' id='searchGroup' name='searchGroup' value='{0}' />", fieldGroup.GroupName);
return strResult.ToString();
}
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 searchGroupName, List<SqlParameter> listParames)
{
FieldGroup searchGroup = new FieldGroup(searchGroupName);
FieldGroupDetailCollection fields = searchGroup.GetFieldsByUser(AccessControl.CurrentLogonUser.Uid);
return GetWhereSql(request, searchGroupName, fields, listParames);
}
public static GridColumns GetGridFields(string listName, string uid)
{
FieldGroup fieldGroup = new FieldGroup(listName);
FieldGroupDetailCollection fieldDetails = fieldGroup.GetFieldsByUser(uid);
GridColumns gridColumns = new GridColumns();
List<string> colNames = new List<string>();
List<GridColumnModel> colModels = new List<GridColumnModel>();
foreach (FieldGroupDetail field in fieldDetails)
{
colNames.Add(field.DisplayName);
GridColumnModel model = new GridColumnModel();
model.name = field.FieldName;
model.index = field.FieldName;
model.width = field.Width_Detail > 0 ? field.Width_Detail : field.Width;
model.align = field.Align;
colModels.Add(model);
}
return new GridColumns
{
colNames = colNames,
colModel = colModels
};
}
public string GetUserListFields(string ID)
{
string strJson = "[";
FieldGroup fieldGroup = new FieldGroup(ID);
FieldGroupDetailCollection fgdc = fieldGroup.GetFieldsByUser(AccessControl.CurrentLogonUser.Uid);
if (fgdc.Count == 0)
{
fgdc = fieldGroup.GetDefaultFields();
}
foreach (FieldGroupDetail fgd in fgdc)
{
strJson += "{\"id\":\"" + fgd.ID + "\",\"text\":\"" + fgd.DisplayName.Replace("<br />", " ") + "\", \"iconCls\":\"icon-ok\"},";
}
strJson = strJson.TrimEnd(',');
strJson += "]";
return strJson;
}
public string GetAllFPCCategoryAndFields(string ID)
{
List<FieldCategory> list = FieldCategory.GetCategorys(FieldCategory.Category_TYPE_FPC, FieldCategory.Category_TYPE_WORKFLOW);
FieldGroup fieldGroup = new FieldGroup(ID);
FieldGroupDetailCollection fgdc = fieldGroup.GetFieldsByUser(AccessControl.CurrentLogonUser.Uid);
string strJson = "[";
foreach (FieldCategory fc in list)
{
string strCategory = "";
string strFields = "";
FieldInfoCollecton fic = fc.Fields;
foreach (FieldInfo fi in fic)
{
if (fgdc[fi.FieldName] == null && fi.Enable == 1 && String.IsNullOrEmpty(fi.SubDataType))
{
strFields += "{\"id\":\"" + fi.ID + "\",\"text\":\"" + fi.DisplayName.Replace("<br />", " ") + "\", \"iconCls\":\"icon-ok\"},";
}
}
strFields = strFields.TrimEnd(',');
if (!String.IsNullOrEmpty(strFields))
{
strCategory = "{\"id\":\"" + fc.CategoryName + "\", \"IsCategory\":true, \"text\":\"" + fc.CategoryName + "\", \"iconCls\":\"icon-ok\", \"children\":[" + strFields + "]},";
}
strJson += strCategory;
}
strJson = strJson.TrimEnd(',');
strJson += "]";
return strJson;
}
public string GetGridData()
{
FieldGroup fieldGroup = new FieldGroup(Request["groupName"]);
string[] extSqlColumns = String.IsNullOrEmpty(Request["extSqlColumns"]) ? null : Request["extSqlColumns"].Split(',');
FieldGroupDetailCollection fields = fieldGroup.GetFieldsByUser(AccessControl.CurrentLogonUser.Uid);
List<TableFormatString> formatString = new List<TableFormatString>();
foreach (FieldGroupDetail field in fields)
{
if (!String.IsNullOrEmpty(field.Format))
{
formatString.Add(new TableFormatString(field.FieldName, field.Format));
}
}
string strSql = GridManager.GetQuerySql(fieldGroup.SourceName, fields, extSqlColumns);
GridData gridData;
//Filter data for SupplierPricingView
if (AccessControl.IsVendor() &&
(Request["groupName"].ToString().ToUpper() == "SUPPLIERRFQGRID" ||
Request["groupName"].ToString().ToUpper() == "VENDORRFQREPORTGRID"))
{
gridData = GridManager.GetGridData(Request, strSql, fieldGroup.Authority, fields);
}
else
{
if (Request["groupName"].ToString().ToUpper() == "CUSTOMERPROFILEGRID")
{
gridData = GridManager.GetGridData(Request, strSql, fieldGroup);
}
else
{
gridData = GridManager.GetGridData(Request, strSql, fieldGroup.Authority);
}
}
return gridData.ToJson(formatString.ToArray());
}