/// <summary>
/// 取得使用到的欄位集合。
/// </summary>
/// <param name="tableDefine">資料表定義。</param>
private GStringHashSet GetUseFields(GTableDefine tableDefine)
{
// 取得資料表定義
var useFieldSet = new GStringHashSet();
if (StrFunc.StrIsEmpty(this.SelectFields))
{
// 包含所有欄位
foreach (GFieldDefine fieldDefine in tableDefine.Fields)
{
useFieldSet.Add(fieldDefine.FieldName);
}
}
else
{
// 加入取回欄位
useFieldSet.Add(this.SelectFields, ",");
// 加入篩選欄位
foreach (GFilterItem filterItem in this.FilterItems)
{
useFieldSet.Add(filterItem.FieldName);
}
// 加入排序欄位
foreach (GSortField sortField in this.SortFields)
{
useFieldSet.Add(sortField.FieldName);
}
}
return(useFieldSet);
}
public void DoCreateProgramDefine()
{
var helper = new InitDataHelper();
var progDefine = new GProgramDefine();
Console.WriteLine("ProgID：");
progDefine.ProgID = Console.ReadLine();
Console.WriteLine("DisplayName：");
progDefine.DisplayName = Console.ReadLine();
Console.WriteLine("Start Create Table");
var tableDefine = new GTableDefine();
tableDefine.PrimaryKey = progDefine.ProgID + "Id";
progDefine.Tables.Add(tableDefine);
Console.WriteLine("TableName：");
tableDefine.TableName = Console.ReadLine();
Console.WriteLine("DbTableName：");
tableDefine.DbTableName = Console.ReadLine();
Console.WriteLine("Table DisplayName：");
tableDefine.DisplayName = Console.ReadLine();
var createField = true;
while (createField)
{
Console.WriteLine("Start Create Field Define");
var fieldDefine = new GFieldDefine();
tableDefine.Fields.Add(fieldDefine);
Console.WriteLine("FieldName：");
fieldDefine.FieldName = Console.ReadLine();
Console.WriteLine("DisplayName：");
fieldDefine.DisplayName = Console.ReadLine();
Console.WriteLine("DbType：");
Console.WriteLine("(0：String, 1：Text, 2：Boolean, 3：Integer, 4：Double, 5：Currency, 6：DateTime, 7：GUID, 8：Binary)");
var dbType = int.Parse(Console.ReadLine());
fieldDefine.DbType = BaseFunc.CEnum <EFieldDbType>(dbType, 0);
Console.WriteLine("MaxLength：");
fieldDefine.MaxLength = int.Parse(Console.ReadLine());
Console.WriteLine("AllowNull：(0: false, 1: true)");
fieldDefine.AllowNull = BaseFunc.CBool(Console.ReadLine());
Console.WriteLine("LinkFieldName：");
fieldDefine.LinkFieldName = Console.ReadLine();
Console.WriteLine("LinkProgID：");
fieldDefine.LinkProgID = Console.ReadLine();
Console.WriteLine("Continue Create Field Define? 1：Yes? 0：No?");
var ans = Console.ReadLine();
if (ans.SameTextOr("No", "0"))
{
createField = false;
}
Console.WriteLine("================================================");
}
helper.ProgDefineToJson(progDefine);
Console.WriteLine($"已產生程式定義檔案：{progDefine.ProgID}/{progDefine.DisplayName}");
Console.ReadKey();
}
/// <summary>
/// 設定資料表中每個欄位的預設值。
/// </summary>
/// <param name="tableDefine">資料表定義。</param>
/// <param name="dataTable">資料表。</param>
public static void SetDataColumnDefaultValue(GTableDefine tableDefine, DataTable dataTable)
{
foreach (DataColumn oColumn in dataTable.Columns)
{
var oFieldDefine = tableDefine.Fields[oColumn.ColumnName];
if (BaseFunc.IsNotNull(oFieldDefine) && !oFieldDefine.AllowNull)
{
oColumn.DefaultValue = DataFunc.GetDefaultValue(oFieldDefine.DbType);
}
}
}
/// <summary>
/// 取得 Select 的欄位集合字串。
/// </summary>
/// <param name="tableDefine">資料表。</param>
/// <param name="selectFields"></param>
private string GetSelectFields(GTableDefine tableDefine, string selectFields)
{
if (StrFunc.StrIsEmpty(selectFields))
{
return(string.Empty);
}
var oSelectFields = StrFunc.StrSplit(selectFields, ",");
var oFields = new GStringHashSet(selectFields, ",");
return(oFields.ToString(","));
}
/// <summary>
/// 取得排序欄位集合。
/// </summary>
private GSortFieldCollection GetSortFields(GTableDefine tableDefine)
{
GSortFieldCollection oSortFields;
// 取得排序欄位集合複本
oSortFields = new GSortFieldCollection();
foreach (GSortField sortField in tableDefine.SortFields)
{
oSortFields.Add(sortField.Clone());
}
return(oSortFields);
}
/// <summary>
/// 產生SqlBulkCopy
/// </summary>
/// <param name="databaseID">資料庫編號</param>
/// <param name="tableDefine">資料表定義</param>
/// <param name="table">資料表</param>
/// <returns></returns>
private SqlBulkCopy CreateSqlBulkCopy(string databaseID, GTableDefine tableDefine, DataTable table)
{
var sqlBulkCopy = new SqlBulkCopy(this.SqlConnection(databaseID))
{
DestinationTableName = tableDefine.DbTableName
};
foreach (var fieldDefine in tableDefine.Fields.Where(x => DataFunc.HasField(table, x.FieldName)))
{
sqlBulkCopy.ColumnMappings.Add(fieldDefine.FieldName, fieldDefine.DbFieldName);
}
return(sqlBulkCopy);
}
/// <summary>
/// 取得查詢資料
/// </summary>
/// <param name="tableDefine">資料表定義</param>
/// <param name="formID">單據編號</param>
/// <returns></returns>
private GEntityTable GetQueryTable(GTableDefine tableDefine, string formID)
{
var fieldName = tableDefine.IsMaster ? SysFields.ID : SysFields.MasterID;
var args = new GSelectInputArgs {
TableName = tableDefine.TableName, FilterItems = { new GFilterItem(fieldName, formID) }
};
foreach (GFilterItem filterItem in tableDefine.FilterItems)
{
args.FilterItems.Add(filterItem.Clone());
}
var result = Select(args);
return(result.Table);
}
/// <summary>
/// 建立資料表關連。
/// </summary>
/// <param name="provider">資料表關連資訊提供者。</param>
/// <param name="tableDefine">資料表定義。</param>
/// <param name="useFields">使用到的欄位集合。</param>
/// <param name="detailTableName">明細資料表名稱。</param>
private void BuildTableJoins(GTableJoinProvider provider, GTableDefine tableDefine, GStringHashSet useFields, string detailTableName = "")
{
foreach (GFieldDefine fieldDefine in tableDefine.Fields)
{
if (fieldDefine.FieldType == EFieldType.DataField && StrFunc.StrIsNotEmpty(fieldDefine.LinkProgID))
{
var oReturnFields = GetReturnFields(tableDefine, fieldDefine.FieldName, useFields);
if (oReturnFields.Count > 0)
{
var key = StrFunc.StrFormat("{0}.{1}.{2}", tableDefine.TableName, fieldDefine.FieldName, fieldDefine.LinkProgID);
var leftTableAlias = StrFunc.StrIsEmpty(detailTableName) ? "A" : "DA";
BuildTableJoin(key, provider, fieldDefine, oReturnFields, leftTableAlias, detailTableName);
}
}
}
}
/// <summary>
/// 取得關連取回欄位集合。
/// </summary>
/// <param name="tableDefine">資料表定義。</param>
/// <param name="linkFieldName">關連來源欄位。</param>
/// <param name="useFields">使用到的欄位集合。</param>
private GStringHashSet GetReturnFields(GTableDefine tableDefine, string linkFieldName, GStringHashSet useFields)
{
GStringHashSet oReturnFields;
GFieldDefine oFieldDefine;
oReturnFields = new GStringHashSet();
foreach (string fieldName in useFields)
{
oFieldDefine = tableDefine.Fields[fieldName];
if (oFieldDefine.FieldType == EFieldType.LinkField && StrFunc.SameText(oFieldDefine.LinkFieldName, linkFieldName))
{
oReturnFields.Add(fieldName);
}
}
return(oReturnFields);
}
/// <summary>
/// 產生程式定義
/// </summary>
/// <param name="progID"></param>
/// <param name="displayName"></param>
/// <returns></returns>
private GProgramDefine CreateProgDefines(string progID, string displayName)
{
var progDefine = new GProgramDefine {
ProgID = progID, DisplayName = displayName
};
var tableDefine = new GTableDefine {
TableName = progID, DisplayName = displayName, DbTableName = progID, PrimaryKey = $"{progID}ID"
};
tableDefine.Fields.Add(new GFieldDefine {
FieldName = SysFields.CompanyID, DisplayName = $"公司編號", MaxLength = 10
});
tableDefine.Fields.Add(new GFieldDefine {
FieldName = SysFields.ID, DisplayName = $"{displayName}編號", MaxLength = 10
});
tableDefine.Fields.Add(new GFieldDefine {
FieldName = SysFields.ViewID, DisplayName = $"{displayName}代碼", MaxLength = 10
});
tableDefine.Fields.Add(new GFieldDefine {
FieldName = SysFields.Name, DisplayName = $"{displayName}名稱", MaxLength = 10
});
progDefine.Tables.Add(tableDefine);
return(progDefine);
}
/// <summary>
/// 建構函式。
/// </summary>
/// <param name="tableDefine">資料表定義。</param>
/// <param name="dataTable">資料表。</param>
public GVirtualFieldBuilder(GTableDefine tableDefine, DataTable dataTable)
{
this.TableDefine = tableDefine;
this.DataTable = dataTable;
}
/// <summary>
/// 取得排序語法。
/// </summary>
/// <param name="helper">資料庫命令輔助類別。</param>
/// <param name="provider">資料表關連資訊提供者。</param>
/// <param name="tableDefine">資料表定義。</param>
/// <param name="sortFields">排序欄位集合。</param>
private string GetSortCommandText(IDbCommandHelper helper, GTableJoinProvider provider, GTableDefine tableDefine, GSortFieldCollection sortFields)
{
StringBuilder oBuilder;
GFieldDefine oFieldDefine;
string sDbFieldName;
string sSortDirection;
// 無排序欄位則傳回空字串
if (BaseFunc.IsEmpty(sortFields) || !sortFields.Any())
{
return(string.Empty);
}
oBuilder = new StringBuilder();
foreach (GSortField sortField in sortFields)
{
// 取得包含資料表別名的欄位名稱
oFieldDefine = tableDefine.Fields[sortField.FieldName];
sDbFieldName = provider.GetDbFieldName(helper, oFieldDefine);
// 排序方式
sSortDirection = sortField.SortDirection == ESortDirection.Ascending ? "ASC" : "DESC";
if (oBuilder.Length > 0)
{
oBuilder.Append(", ");
}
oBuilder.Append(StrFunc.StrFormat("{0} {1}", sDbFieldName, sSortDirection));
}
return(" Order By " + oBuilder.ToString());
}
/// <summary>
/// 取得資料表關連語法。
/// </summary>
/// <param name="helper">資料庫命令輔助類別。</param>
/// <param name="provider">資料表關連資訊提供者。</param>
/// <param name="tableDefine">資料表定義。</param>
private string GeGTableJoinCommandText(IDbCommandHelper helper, GTableJoinProvider provider, GTableDefine tableDefine)
{
StringBuilder oBuffer;
oBuffer = new StringBuilder();
oBuffer.AppendFormat("From {0} {1} ", helper.GetTableName(tableDefine.DbTableName), "A");
oBuffer.AppendLine();
foreach (GTableJoin item in provider.TableJoins)
{
oBuffer.AppendFormat("Left Join {2} {3} On {0}.{1}={3}.{4} ",
item.LeftTableAlias, helper.GetFieldName(item.LeftFieldName),
helper.GetTableName(item.RightTableName), item.RightTableAlias, helper.GetFieldName(item.RightFieldName));
// 過濾公司編號
if (StrFunc.StrIsNotEmpty(item.RightCompanyID))
{
oBuffer.AppendFormat("And {0}.{1}='{2}' ",
item.RightTableAlias, helper.GetFieldName(item.RightCompanyID), this.SessionInfo.CompanyID);
}
oBuffer.AppendLine();
}
return(oBuffer.ToString());
}
