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