/// <summary>
/// 取得欄位預設值(字串預設為空字串、數值預設為 0，布林值預設為 false，其他為 DBNull.Value)。
/// </summary>
/// <param name="fieldDefine">欄位定義。</param>
public static object GetDefaultValue(GFieldDefine fieldDefine)
{
if (fieldDefine.AllowNull)
return DBNull.Value;
else
return DataFunc.GetDefaultValue(fieldDefine.DbType);
}
/// <summary>
/// 匯入欄位
/// </summary>
/// <param name="originalRow">來源資料列</param>
private void ImportFieldNames(GEntityRow originalRow)
{
foreach (GEntityField field in originalRow.Fields)
{
this.Fields.Add(field.Key, DataFunc.GetDefaultValue(field.Value.GetType()));
}
}
public HttpResponseMessage PostExpensesData([FromBody] List<Expenses> p)
{
DataFunc erpfunc = new DataFunc(db);
try
{
erpfunc.mSaveExpenses(p);
string retval = "[{result:'ok'},{resultdescr:''}]";
var resp = new HttpResponseMessage { Content = new StringContent(retval, System.Text.Encoding.UTF8, "application/json") };
return resp;
}
catch (Exception e)
{
erpfunc.LogToSQL("Save Expenses:" + e.Message, "PostExpensesData", "");
string retval = "[{result:'error'},{resultdescr:'"+e.Message+"'}]";
var resp = new HttpResponseMessage { Content = new StringContent(retval, System.Text.Encoding.UTF8, "application/json") };
return resp;
}
}
/// <summary>
/// 取得作用資料列。
/// </summary>
/// <param name="dataSet">資料集。</param>
/// <param name="tableName">資料表。</param>
/// <param name="fieldName">欄位名稱，若不存在指定欄位，會回傳 null。</param>
/// <param name="rowIndex">資料列索引。</param>
private DataRow GetDataRow(DataSet dataSet, string tableName, string fieldName, int rowIndex)
{
DataTable oTable;
// 資料集為空，則回傳 null
if (BaseFunc.IsEmpty(dataSet))
{
return(null);
}
// 資料表不存在，則回傳 null
if (!dataSet.Tables.Contains(tableName))
{
return(null);
}
// 欄位不存在，則回傳 null
oTable = dataSet.Tables[tableName];
if (!DataFunc.HasField(oTable, fieldName))
{
return(null);
}
// 主檔的資料列索引一徫為 0
if (StrFunc.SameText(dataSet.DataSetName, tableName))
{
rowIndex = 0;
}
// 回傳作用資料列
if (0 <= rowIndex && rowIndex <= oTable.Rows.Count - 1)
{
return(oTable.Rows[rowIndex]);
}
else
{
return(null);
}
}
/// <summary>
/// 執行 Add 方法的實作。
/// </summary>
/// <param name="inputArgs"></param>
/// <param name="outputResult"></param>
protected virtual void DoAdd(GAddInputArgs inputArgs, GAddOutputResult outputResult)
{
var table = DefineFunc.CreateDataTable(this.ProgramDefine.MasterTable);
var dataSet = DataFunc.CreateDataSet(this.ProgID);
dataSet.Tables.Add(table);
table.Rows.Add(table.NewRow());
outputResult.DataSet = dataSet;
}
/// <summary>
/// 執行 Select 方法的實作
/// </summary>
/// <param name="inputArgs"></param>
/// <param name="outputResult"></param>
protected virtual void DoSelect(GSelectInputArgs inputArgs, GSelectOutputResult outputResult)
{
var sTableName = inputArgs.TableName;
var oTableDefine = this.ProgramDefine.Tables[sTableName];
if (BaseFunc.IsNull(oTableDefine))
{
return;
}
// 因為 Find 與 Move 都會統一進入 Select
// 所以放在這邊統一做判斷過濾條件是否有包含 SYS_ID 欄位且其值是 *
// 如滿足條件則需要判斷資料型別，並更改其查詢值
CheckFilterItemsForDbType(inputArgs.FilterItems);
// 取得 Select 的欄位集合字串
var sSelectFields = GetSelectFields(oTableDefine, inputArgs.SelectFields);
var oDbCommandBuilder = this.CreateDbCommandBuilder();
var oDbCommand = oDbCommandBuilder.BuildSelectCommand(sTableName, sSelectFields, inputArgs.FilterItems, inputArgs.UserFilter,
inputArgs.IsOrderBy);
if (BaseFunc.IsNull(oDbCommand))
{
return;
}
var oDataTable = base.DbAccess.ExecuteDataTable(this.DatabaseID, oDbCommand);
oDataTable.TableName = sTableName;
if (inputArgs.IsBuildVirtualField)
{
// 加入虛擬欄位
var oVFBuilder = new GVirtualFieldBuilder(oTableDefine, oDataTable);
oVFBuilder.Execute();
}
else
{
// 判斷 Select 的欄位是否有虛擬欄位
var oVFBuilder = new GVirtualFieldBuilder(oTableDefine, oDataTable);
oVFBuilder.Execute(sSelectFields);
}
// 設定資料表中每個欄位的預設值
BusinessFunc.SetDataColumnDefaultValue(oTableDefine, oDataTable);
// 設定DataTable主索引鍵
if (StrFunc.StrIsNotEmpty(oTableDefine.TablePrimaryKey))
{
DataFunc.DataTableSetPrimaryKey(oDataTable, oTableDefine.TablePrimaryKey);
}
// 資料表同意變更，讓資料表無異動狀態
oDataTable.AcceptChanges();
outputResult.Table = new GEntityTable(oDataTable);
}
/// <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>
/// 依實體欄位定義新增命令參數。
/// </summary>
/// <param name="dbFieldDefine">實體欄位定義。</param>
public virtual DbParameter AddParameter(GDbFieldDefine dbFieldDefine)
{
DbParameter oParameter;
oParameter = AddParameter(dbFieldDefine.FieldName, dbFieldDefine.DbType);
oParameter.SourceColumn = dbFieldDefine.FieldName;
if (!dbFieldDefine.AllowNull)
{
oParameter.Value = DataFunc.GetDefaultValue(dbFieldDefine.DbType);
}
return(oParameter);
}
/// <summary>
/// 資料表是否存在
/// </summary>
/// <param name="tableName"></param>
/// <returns></returns>
private bool TableExists(string tableName)
{
var sql = new StringBuilder();
sql.AppendLine("Select A.name as TableName, B.name as ColumnName");
sql.AppendLine("From sys.tables A");
sql.AppendLine("Inner Join sys.columns B On A.object_id = B.object_id");
sql.AppendLine($"Where A.name = {tableName.SQLStr()}");
var result = this.DbAccess.ExecuteDataTable(this.DatabaseID, sql.ToString());
return(!DataFunc.IsEmpty(result));
}
public HttpResponseMessage PostExpenseTypeData([FromBody] List<ExpenseType> p)
{
DataFunc erpfunc = new DataFunc(db);
erpfunc.mSaveExpenseType(p);
string retval = "[{result:'ok'}]";
var resp = new HttpResponseMessage { Content = new StringContent(retval, System.Text.Encoding.UTF8, "application/json") };
return resp;
}
/// <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>
/// 使用SqlBulkCopy大量新增資料
/// </summary>
/// <param name="databaseID">資料庫編號</param>
/// <param name="progID">程式定義代碼</param>
/// <param name="tableName">資料定義代碼</param>
/// <param name="table">資料表</param>
public void SqlBulkCopy(string databaseID, string progID, string tableName, DataTable table)
{
var progDefine = CacheFunc.GetProgramDefine(progID);
var tableDefine = progDefine.Tables[tableName];
if (tableDefine == null || !tableDefine.IsCreateDbTable || DataFunc.IsEmpty(table))
{
return;
}
var sqlBulkCopy = CreateSqlBulkCopy(databaseID, tableDefine, table);
SqlBulkCopy(sqlBulkCopy, table);
}
public HttpResponseMessage GetMobileData([FromBody] List<RemoteGUIDs> p,string requestcode,string deviceCode,string param)
{
DataFunc erpfunc = new DataFunc(db);
try
{
erpfunc.LogToSQL("GUID Count:" + p.Count, "GetMobileData", "");
}
catch (Exception e)
{
erpfunc.LogToSQL("Logging error:" + e.Message, "GetMobileData", "");
}
DataTable dt=null;
if (requestcode.ToLower() == "allcustomers")
{
dt = erpfunc.mRet_Persons();
}
else if (requestcode.ToLower() == "singlecustomer")
{
dt = erpfunc.mRet_Persons_FromCode(param);
}
else if (requestcode.ToLower() == "expenses")
{
dt = erpfunc.mRetExpenses(p);
}
else
{
return Request.CreateResponse(HttpStatusCode.NotFound, "Request not found");
}
string retval = SQL.DataTableToJSONWithJSONNet(dt);
var resp = new HttpResponseMessage { Content = new StringContent(retval, System.Text.Encoding.UTF8, "application/json") };
return resp;
}
public override int GetHashCode()
{
unchecked
{
var hashCode = base.GetHashCode();
hashCode = (hashCode * 397) ^ (ForEachElementName != null ? ForEachElementName.GetHashCode() : 0);
hashCode = (hashCode * 397) ^ (DisplayName != null ? DisplayName.GetHashCode() : 0);
hashCode = (hashCode * 397) ^ _previousInputsIndex;
hashCode = (hashCode * 397) ^ _previousOutputsIndex;
hashCode = (hashCode * 397) ^ (int)ForEachType;
hashCode = (hashCode * 397) ^ (From != null ? From.GetHashCode() : 0);
hashCode = (hashCode * 397) ^ (To != null ? To.GetHashCode() : 0);
hashCode = (hashCode * 397) ^ (Recordset != null ? Recordset.GetHashCode() : 0);
hashCode = (hashCode * 397) ^ (CsvIndexes != null ? CsvIndexes.GetHashCode() : 0);
hashCode = (hashCode * 397) ^ (NumOfExections != null ? NumOfExections.GetHashCode() : 0);
hashCode = (hashCode * 397) ^ (DataFunc != null ? DataFunc.GetHashCode() : 0);
hashCode = (hashCode * 397) ^ FailOnFirstError.GetHashCode();
hashCode = (hashCode * 397) ^ (ElementName != null ? ElementName.GetHashCode() : 0);
hashCode = (hashCode * 397) ^ (PreservedDataList != null ? PreservedDataList.GetHashCode() : 0);
return(hashCode);
}
}
