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