public FieldConfig( ) { IsDefault = false; IsGrouping = false; IsDisplayField = false; Format = DataFormatProvider.FieldFormat.None; }
public void RefreshFormat( ) { DataFormatProvider.FieldFormat format = DataFormatProvider.FieldFormat.Date; switch (formatType) { case ABCDateFormat.Date: format = DataFormatProvider.FieldFormat.Date; break; case ABCDateFormat.DateAndTime: format = DataFormatProvider.FieldFormat.DateAndTime; break; case ABCDateFormat.Month: format = DataFormatProvider.FieldFormat.Month; break; case ABCDateFormat.MonthAndYear: format = DataFormatProvider.FieldFormat.MonthAndYear; break; case ABCDateFormat.Time: format = DataFormatProvider.FieldFormat.Time; break; case ABCDateFormat.Year: format = DataFormatProvider.FieldFormat.Year; break; } DataFormatProvider.ABCFormatInfo formatInfo = DataFormatProvider.GetFormatInfo(format); // this.Properties.Appearance.TextOptions.HAlignment=DevExpress.Utils.HorzAlignment.Far; this.Properties.DisplayFormat.FormatType = formatInfo.FormatInfo.FormatType; this.Properties.DisplayFormat.FormatString = formatInfo.FormatInfo.FormatString; this.Properties.EditFormat.FormatType = formatInfo.FormatInfo.FormatType; this.Properties.EditFormat.FormatString = formatInfo.FormatInfo.FormatString; this.Properties.Mask.UseMaskAsDisplayFormat = true; this.Properties.Mask.EditMask = formatInfo.FormatInfo.FormatString; this.Properties.Mask.MaskType = formatInfo.MaskType; }
public void GetSearchQuery(ABCHelper.ConditionBuilder strBuilder, Control current) { if (current is ISearchControl) { String strResult = ((ISearchControl)current).SearchString; if (String.IsNullOrWhiteSpace(strResult) == false) { strBuilder.AddCondition(strResult); } } else if (current is IABCBindableControl) { System.Reflection.PropertyInfo proInfo = current.GetType().GetProperty((current as IABCBindableControl).BindingProperty); if (proInfo != null) { object objValue = proInfo.GetValue(current, null); if (objValue != null) { String strType = DataStructureProvider.GetCodingType((current as IABCBindableControl).TableName, (current as IABCBindableControl).DataMember); if (strType == "int" || strType == "Nullable<int>" || strType == "double" || strType == "Nullable<double>" || strType == "bool" || strType == "Nullable<bool>") { strBuilder.AddCondition(String.Format(" [{0}] = {1} ", (current as IABCBindableControl).DataMember, objValue)); } if (strType == "String" || strType == "Nullable<String>") { strBuilder.AddCondition(String.Format(" [{0}] LIKE '%{1}%' ", (current as IABCBindableControl).DataMember, objValue.ToString())); } if (strType == "Guid" || strType == "Nullable<Guid>") { strBuilder.AddCondition(String.Format(" [{0}] = '{1}' ", (current as IABCBindableControl).DataMember, objValue.ToString())); } if (strType == "DateTime" || strType == "Nullable<DateTime>") { DataFormatProvider.FieldFormat format = DataFormatProvider.FieldFormat.None; DataFormatProvider.ABCFormatInfo formatInfo = DataFormatProvider.GetFormatInfo((current as IABCBindableControl).TableName, (current as IABCBindableControl).DataMember); if (formatInfo != null) { format = formatInfo.ABCFormat; } if (format == DataFormatProvider.FieldFormat.None) { format = DataFormatProvider.FieldFormat.Date; } DateTime dtValue1 = DateTime.MinValue; DateTime dtValue2 = DateTime.MaxValue; if (objValue is DateTime) { dtValue1 = Convert.ToDateTime(objValue); } if (objValue is Nullable <DateTime> && (objValue as Nullable <DateTime>).HasValue) { dtValue1 = (objValue as Nullable <DateTime>).Value; } if (format == DataFormatProvider.FieldFormat.Month || format == DataFormatProvider.FieldFormat.MonthAndYear) { dtValue1 = new DateTime(dtValue1.Year, dtValue1.Month, 1); dtValue2 = dtValue1.AddMonths(1).AddSeconds(-30); } else if (format == DataFormatProvider.FieldFormat.Year) { dtValue1 = new DateTime(dtValue1.Year, 1, 1); dtValue2 = dtValue1.AddYears(1).AddSeconds(-30); } else { dtValue1 = new DateTime(dtValue1.Year, dtValue1.Month, dtValue1.Day); dtValue2 = dtValue1; } String strFrom = TimeProvider.GenCompareDateTime((current as IABCBindableControl).DataMember, ">=", dtValue1); String strTo = TimeProvider.GenCompareDateTime((current as IABCBindableControl).DataMember, "<=", dtValue2); strBuilder.AddCondition(String.Format(" ( {0} AND {1} ) ", strFrom, strTo)); } } } } foreach (Control ctrl in current.Controls) { GetSearchQuery(strBuilder, ctrl); } }
public static void GenerateDefaultTableConfig( ) { String strCheckCondition = String.Empty; if (DataQueryProvider.IsSQLConnection("STTableConfigs")) { strCheckCondition = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='STTableConfigs'"; } else { strCheckCondition = "SELECT tbl_name FROM sqlite_master WHERE type='table' AND tbl_name='STTableConfigs'"; } DataSet dsTemp = DataQueryProvider.RunQuery(strCheckCondition); if (dsTemp == null || dsTemp.Tables.Count <= 0 || dsTemp.Tables[0].Rows.Count <= 0) { return; } #region Clean First DataSet ds2 = DataQueryProvider.SystemDatabaseHelper.RunQuery("SELECT STTableConfigID,TableName FROM STTableConfigs "); if (ds2 != null && ds2.Tables.Count > 0) { foreach (DataRow dr in ds2.Tables[0].Rows) { if (String.IsNullOrWhiteSpace(dr[0].ToString()) == false) { Guid iID = Guid.Empty; Guid.TryParse(dr[0].ToString(), out iID); String strTableName = dr[1].ToString(); if (DataStructureProvider.DataTablesList.ContainsKey(strTableName) == false) { DataQueryProvider.SystemDatabaseHelper.RunQuery(String.Format("DELETE FROM STTableConfigs WHERE STTableConfigID='{0}'", iID.ToString())); } } } } ds2 = DataQueryProvider.SystemDatabaseHelper.RunQuery("SELECT STFieldConfigID,TableName,FieldName FROM STFieldConfigs "); if (ds2 != null && ds2.Tables.Count > 0) { foreach (DataRow dr in ds2.Tables[0].Rows) { if (String.IsNullOrWhiteSpace(dr[0].ToString()) == false) { Guid iID = Guid.Empty; Guid.TryParse(dr[0].ToString(), out iID); String strTableName = dr[1].ToString(); String strFieldName = dr[2].ToString(); if (DataStructureProvider.DataTablesList.ContainsKey(strTableName) == false || DataStructureProvider.DataTablesList[strTableName].ColumnsList.ContainsKey(strFieldName) == false) { DataQueryProvider.SystemDatabaseHelper.RunQuery(String.Format("DELETE FROM STFieldConfigs WHERE STFieldConfigID='{0}'", iID.ToString())); } } } } #endregion foreach (String strTableName in DataStructureProvider.DataTablesList.Keys) { #region Table Config DataSet ds = DataQueryProvider.SystemDatabaseHelper.RunQuery(String.Format("SELECT COUNT(*) FROM STTableConfigs WHERE TableName='{0}'", strTableName)); if (ds == null || ds.Tables.Count <= 0 || ds.Tables[0].Rows.Count <= 0 || Convert.ToInt32(ds.Tables[0].Rows[0][0]) <= 0) { String strQuery = QueryTemplateGenerator.GenInsert("STTableConfigs"); strQuery = strQuery.Replace("@STTableConfigID", "'" + Guid.Empty.ToString() + "'"); strQuery = strQuery.Replace("@TableName", "'" + strTableName + "'"); String s = strTableName.Substring(2, strTableName.Length - 2); //var r=new System.Text.RegularExpressions.Regex( @"(?<=[A-Z])(?=[A-Z][a-z]) |(?<=[^A-Z])(?=[A-Z]) |(?<=[A-Za-z])(?=[^A-Za-z])" , System.Text.RegularExpressions.RegexOptions.IgnorePatternWhitespace ); //s=r.Replace( s , " " ); strQuery = strQuery.Replace("@CaptionVN", "'" + s + "'"); strQuery = strQuery.Replace("@CaptionEN", "'" + s + "'"); strQuery = strQuery.Replace("@DescriptionVN", "''"); strQuery = strQuery.Replace("@DescriptionEN", "''"); strQuery = strQuery.Replace("@IsCaching", "0"); strQuery = strQuery.Replace("@CalcOnClient", "1"); strQuery = strQuery.Replace("@DeleteByOwnerOnly", "0"); strQuery = strQuery.Replace("@EditByOwnerOnly", "0"); DataQueryProvider.SystemDatabaseHelper.RunScript(strQuery); } #endregion #region Field Config List <String> lstConfig = new List <String>(); ds = DataQueryProvider.SystemDatabaseHelper.RunQuery(String.Format("SELECT STFieldConfigID,FieldName FROM STFieldConfigs WHERE TableName='{0}'", strTableName)); if (ds != null && ds.Tables.Count > 0) { foreach (DataRow dr in ds.Tables[0].Rows) { if (String.IsNullOrWhiteSpace(dr[1].ToString()) == false) { if (lstConfig.Contains(dr[1].ToString()) == false) { lstConfig.Add(dr[1].ToString()); } else { Guid iID = Guid.Empty; Guid.TryParse(dr[0].ToString(), out iID); DataQueryProvider.SystemDatabaseHelper.RunQuery(String.Format("DELETE FROM STFieldConfigs WHERE STFieldConfigID='{0}'", strTableName)); } } } } int iCount = -1; foreach (String strColName in DataStructureProvider.DataTablesList[strTableName].ColumnsList.Keys) { if (DataStructureProvider.IsPrimaryKey(strTableName, strColName)) { continue; } iCount++; if (lstConfig.Contains(strColName) == false) { String strQuery = QueryTemplateGenerator.GenInsert("STFieldConfigs"); Guid iID = Guid.NewGuid(); strQuery = strQuery.Replace("@STFieldConfigID", "'" + iID.ToString() + "'"); strQuery = strQuery.Replace("@TableName", "'" + strTableName + "'"); strQuery = strQuery.Replace("@FieldName", "'" + strColName + "'"); if (DataStructureProvider.IsForeignKey(strTableName, strColName)) { String strFKTableName = DataStructureProvider.GetTableNameOfForeignKey(strTableName, strColName); String strCaptionVN = GetTableCaption(strFKTableName); if (String.IsNullOrWhiteSpace(strCaptionVN)) { strCaptionVN = strFKTableName.Substring(2, strFKTableName.Length - 3); } //var r=new System.Text.RegularExpressions.Regex( @"(?<=[A-Z])(?=[A-Z][a-z]) |(?<=[^A-Z])(?=[A-Z]) |(?<=[A-Za-z])(?=[^A-Za-z])" , System.Text.RegularExpressions.RegexOptions.IgnorePatternWhitespace ); //strCaptionVN=r.Replace( strCaptionVN , " " ); strQuery = strQuery.Replace("@CaptionVN", String.Format("N'{0}'", strCaptionVN)); strQuery = strQuery.Replace("@CaptionEN", "'" + strFKTableName.Substring(2, strFKTableName.Length - 3) + "'"); } else { strQuery = strQuery.Replace("@CaptionVN", String.Format("N'{0}'", GetFieldCaptionFromDictionary(strColName))); strQuery = strQuery.Replace("@CaptionEN", "'" + strColName + "'"); if (DataStructureProvider.IsNOColumn(strTableName, strColName)) { String strTableCap = GetTableCaption(strTableName); if (!String.IsNullOrWhiteSpace(strTableCap)) { strQuery = strQuery.Replace("@CaptionVN", String.Format("N'{0}'", strTableCap)); } } } strQuery = strQuery.Replace("@DescriptionVN", "''"); strQuery = strQuery.Replace("@DescriptionEN", "''"); if (strColName.Equals("ABCStatus") || strColName.Equals("EditCount") || strColName.Equals("CreateTime") || strColName.Equals("CreateUser") || strColName.Equals("UpdateTime") || strColName.Equals("UpdateUser") || strColName.Equals("NoIndex") || strColName.Equals("LastCalcDate")) { strQuery = strQuery.Replace("@InUse", "0"); } else { strQuery = strQuery.Replace("@InUse", "1"); } if (DataStructureProvider.IsNOColumn(strTableName, strColName) || DataStructureProvider.IsNAMEColumn(strTableName, strColName) || DataStructureProvider.IsDisplayColumn(strTableName, strColName)) { strQuery = strQuery.Replace("@IsDefault", "1"); } else { strQuery = strQuery.Replace("@IsDefault", "0"); } strQuery = strQuery.Replace("@AssignedEnum", "''"); strQuery = strQuery.Replace("@FilterString", "''"); strQuery = strQuery.Replace("@SortOrder", String.Format("{0}", iCount)); if (DataStructureProvider.IsNOColumn(strTableName, strColName) || DataStructureProvider.IsNAMEColumn(strTableName, strColName)) { strQuery = strQuery.Replace("@IsDisplayField", "1"); } else { strQuery = strQuery.Replace("@IsDisplayField", "0"); } strQuery = strQuery.Replace("@IsDisplayField", "0"); strQuery = strQuery.Replace("@IsGrouping", "0"); #region DataFormatProvider.FieldFormat String strTypeName = DataStructureProvider.GetCodingType(strTableName, strColName); DataFormatProvider.FieldFormat format = DataFormatProvider.FieldFormat.None; if (strTypeName == "DateTime" || strTypeName == "Nullable<DateTime>") { format = DataFormatProvider.FieldFormat.Date; } if ((strTypeName == "int" || strTypeName == "Nullable<int>" || strTypeName == "Guid" || strTypeName == "Nullable<Guid>") && strColName.ToUpper().Contains("FK_") == false && DataStructureProvider.IsForeignKey(strTableName, strColName) == false && DataStructureProvider.IsPrimaryKey(strTableName, strColName) == false) { format = DataFormatProvider.FieldFormat.Quantity; } if (strTypeName == "double" || strTypeName == "decimal") { if (strColName.ToLower().Contains("amt") || strColName.ToLower().Contains("fee") || strColName.ToLower().Contains("cost") || strColName.ToLower().Contains("expense") || strColName.ToLower().Contains("pay") || strColName.ToLower().Contains("tax") || strColName.ToLower().Contains("gross") || strColName.ToLower().Contains("net") || strColName.ToLower().Contains("amount") || strColName.ToLower().Contains("exchange")) { format = DataFormatProvider.FieldFormat.Currency; } else { format = DataFormatProvider.FieldFormat.Amount; } } if (format != DataFormatProvider.FieldFormat.None) { strQuery = strQuery.Replace("@Format", String.Format("'{0}'", format.ToString())); } else { strQuery = strQuery.Replace("@Format", "''"); } #endregion DataQueryProvider.SystemDatabaseHelper.RunScript(strQuery); } } foreach (String strColName in lstConfig) { if (DataStructureProvider.DataTablesList[strTableName].ColumnsList.ContainsKey(strColName) == false) { DataQueryProvider.SystemDatabaseHelper.RunQuery(String.Format("DELETE FROM STFieldConfigs WHERE TableName='{0}' AND FieldName='{1}'", strTableName, strColName)); } } #endregion } }
public static void InvalidateConfigList( ) { String strCheckCondition = String.Empty; if (DataQueryProvider.IsSQLConnection("STTableConfigs")) { strCheckCondition = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='STTableConfigs'"; } else { strCheckCondition = "SELECT tbl_name FROM sqlite_master WHERE type='table' AND tbl_name='STTableConfigs'"; } DataSet dsTemp = DataQueryProvider.RunQuery(strCheckCondition); if (dsTemp == null || dsTemp.Tables.Count <= 0 || dsTemp.Tables[0].Rows.Count <= 0) { return; } TableConfigList.Clear(); #region Init ConfigList #region Field Config DataSet ds = DataQueryProvider.SystemDatabaseHelper.RunQuery(@"SELECT * FROM STFieldConfigs"); if (ds != null && ds.Tables.Count > 0) { foreach (DataRow dr in ds.Tables[0].Rows) { TableConfig tableConfig = null; String strTableName = dr["TableName"].ToString(); if (TableConfigList.TryGetValue(strTableName, out tableConfig) == false) { tableConfig = new TableConfig(); TableConfigList.Add(strTableName, tableConfig); } if (tableConfig.FieldConfigList.ContainsKey(dr["FieldName"].ToString()) == false) { #region Field FieldConfig config = new FieldConfig(); config.ConfigID = ABCHelper.DataConverter.ConvertToGuid(dr["STFieldConfigID"]); config.FieldName = dr["FieldName"].ToString(); config.CaptionVN = dr["CaptionVN"].ToString(); config.CaptionEN = dr["CaptionEN"].ToString(); config.DescVN = dr["DescriptionVN"].ToString(); config.DescEN = dr["DescriptionEN"].ToString(); config.TypeName = DataStructureProvider.GetCodingType(strTableName, config.FieldName); config.InUse = false; if (dr["InUse"] != DBNull.Value) { config.InUse = Convert.ToBoolean(dr["InUse"]); } if (dr["IsDefault"] != DBNull.Value) { config.IsDefault = Convert.ToBoolean(dr["IsDefault"]); } if (dr["IsDisplayField"] != DBNull.Value) { config.IsDisplayField = Convert.ToBoolean(dr["IsDisplayField"]); } if (dr["IsGrouping"] != DBNull.Value) { config.IsGrouping = Convert.ToBoolean(dr["IsGrouping"]); } if (dr["AssignedEnum"] != DBNull.Value) { config.AssignedEnum = dr["AssignedEnum"].ToString(); } if (dr["FilterString"] != DBNull.Value) { config.FilterString = dr["FilterString"].ToString(); } if (dr["SortOrder"] != DBNull.Value) { config.SortOrder = Convert.ToInt32(dr["SortOrder"]); } #region FieldFormat if (Enum.IsDefined(typeof(DataFormatProvider.FieldFormat), dr["Format"].ToString())) { DataFormatProvider.FieldFormat format = (DataFormatProvider.FieldFormat)Enum.Parse(typeof(DataFormatProvider.FieldFormat), dr["Format"].ToString()); if (format != DataFormatProvider.FieldFormat.None) { config.Format = format; } else { if (config.TypeName == "DateTime" || config.TypeName == "Nullable<DateTime>") { config.Format = DataFormatProvider.FieldFormat.Date; } if ((config.TypeName == "int" || config.TypeName == "Nullable<int>") && config.FieldName.ToUpper().Contains("FK_") == false && DataStructureProvider.IsForeignKey(strTableName, config.FieldName) == false && DataStructureProvider.IsPrimaryKey(strTableName, config.FieldName) == false) { config.Format = DataFormatProvider.FieldFormat.Quantity; } if (config.TypeName == "double" || config.TypeName == "decimal") { if (config.FieldName.ToLower().Contains("amt") || config.FieldName.ToLower().Contains("amount") || config.FieldName.ToLower().Contains("exchange")) { config.Format = DataFormatProvider.FieldFormat.Currency; } else { config.Format = DataFormatProvider.FieldFormat.Amount; } } } } #endregion tableConfig.FieldConfigList.Add(config.FieldName, config); #endregion } } } #endregion #region Table Config ds = DataQueryProvider.SystemDatabaseHelper.RunQuery(@"SELECT * FROM STTableConfigs"); if (ds != null && ds.Tables.Count > 0) { foreach (DataRow dr in ds.Tables[0].Rows) { TableConfig tableConfig = null; String strTableName = dr["TableName"].ToString(); if (TableConfigList.TryGetValue(strTableName, out tableConfig)) { tableConfig.ConfigID = ABCHelper.DataConverter.ConvertToGuid(dr["STTableConfigID"]); tableConfig.TableName = strTableName; tableConfig.CaptionVN = dr["CaptionVN"].ToString(); tableConfig.CaptionEN = dr["CaptionEN"].ToString(); tableConfig.DescVN = dr["DescriptionVN"].ToString(); tableConfig.DescEN = dr["DescriptionEN"].ToString(); if (dr["IsCaching"] != DBNull.Value) { tableConfig.IsCaching = Convert.ToBoolean(dr["IsCaching"]); } else { tableConfig.IsCaching = false; } } } } #endregion #endregion }