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 } }