private void BuildInsertQuery(string tableName)
{
byte i = 0;
string[] columns = new string[Columns.Keys.Count];
string[] parameters = new string[Columns.Values.Count];
Columns.Keys.CopyTo(columns, 0);
foreach (string parameter in Columns.Values)
{
parameters[i] = parameter;
i++;
}
if (columns.Length != 0)
{
Append(EnableTableAndColumnEncapsulation ? "INSERT INTO [{0}] ({1}) VALUES ({2})" : "INSERT INTO {0} ({1}) VALUES ({2})",
string.IsNullOrEmpty(tableName) ? TableName : tableName,
QueryUtility.GetQueryFragment(EnableTableAndColumnEncapsulation ? QueryFormat.DelimitedSquareBracket : QueryFormat.Delimited, columns),
QueryUtility.GetQueryFragment(QueryFormat.Delimited, parameters));
}
else
{
Append(EnableTableAndColumnEncapsulation ? "INSERT INTO [{0}] DEFAULT VALUES" : "INSERT INTO {0} DEFAULT VALUES",
string.IsNullOrEmpty(tableName) ? TableName : tableName);
}
}
private void BuildSelectQuery(string tableName)
{
bool enableSquareBracketEncapsulationOnTable = EnableTableAndColumnEncapsulation;
string[] columns = new string[KeyColumns.Count + Columns.Count];
KeyColumns.Keys.CopyTo(columns, 0);
Columns.Keys.CopyTo(columns, KeyColumns.Count);
Append("SELECT ");
if (EnableReadLimit)
{
Append("TOP {0} ", ReadLimit);
}
Append(QueryUtility.GetQueryFragment(EnableTableAndColumnEncapsulation ? QueryFormat.DelimitedSquareBracket : QueryFormat.Delimited, columns, true));
if (enableSquareBracketEncapsulationOnTable)
{
enableSquareBracketEncapsulationOnTable = !(tableName.Contains("[") && tableName.Contains("]")); // check if we have an overriden tableName with square brackets already integrated and reverse the boolean result.
}
Append(enableSquareBracketEncapsulationOnTable ? " FROM [{0}]" : " FROM {0}", string.IsNullOrEmpty(tableName) ? TableName : tableName);
if (EnableDirtyReads)
{
Append(" WITH(NOLOCK)");
}
AppendWhereClause();
}
/// <summary>
/// 主キーを指定して該当するデータを取得する
/// </summary>
/// <param name="id">主キー</param>
/// <returns>データ、ただし存在しない場合はnull</returns>
public List <T> SelectFromPrimaryKey <U>(Dictionary <string, string> whereQuery)
{
StringBuilder query = new StringBuilder();
query.Append("SELECT * FROM ");
query.Append(TableName);
QueryUtility.CreateWhereQuery(ref query, whereQuery);
Debug.Log(query);
query.Append(";");
DataTable dt = mDb.ExecuteQuery(query.ToString());
if (dt.Rows.Count == 0)
{
return(null);
}
else
{
var list = new List <T>();
foreach (var row in dt.Rows)
{
list.Add(PutData(row));
}
return(list);
}
}
public void GetQueryPropertyNameTest()
{
try
{
// Test exception is thrown.
var result = QueryUtility.GetQueryPropertyName <CardDto>(null);
Assert.True(false);
}
catch (ArgumentNullException ex)
{
Assert.Equal("propertyName", ex.ParamName);
}
catch
{
Assert.True(false);
}
// Property doesn't exist.
Assert.Null(QueryUtility.GetQueryPropertyName <DtoTestObject>("fakeProperty"));
// Attribute doesn't exist.
Assert.Null(QueryUtility.GetQueryPropertyName <DtoTestObject>("Property1"));
// Property exists.
Assert.Equal("jsonProperty2", QueryUtility.GetQueryPropertyName <DtoTestObject>("Property2"));
}
/// <summary>
/// Adds a query parameter.
/// </summary>
/// <typeparam name="U">The type of property to add the query for.</typeparam>
/// <param name="property">The property to add the query for.</param>
/// <param name="value">The value of the query.</param>
/// <returns>The instance of its self with the new query parameter.</returns>
public CardService Where <U>(Expression <Func <CardQueryParameter, U> > property, U value)
{
if (property == null)
{
throw new ArgumentNullException("property");
}
if (value == null)
{
throw new ArgumentNullException("value");
}
MemberExpression expression = property.Body as MemberExpression;
var queryName = QueryUtility.GetQueryPropertyName <CardQueryParameter>(expression.Member.Name);
Type valueType = value.GetType();
if (valueType.IsArray)
{
string val = string.Join("|", (IEnumerable <object>)value);
this._whereQueries[queryName] = val;
}
else
{
this._whereQueries[queryName] = Convert.ToString(value);
}
return(this);
}
Expression IQueryPolicy.ApplyPolicy(Expression expression, MemberInfo member)
{
List <LambdaExpression> ops;
if (operations.TryGetValue(member, out ops))
{
var syntax = Database.Provider.GetService <ISyntaxProvider>();
var result = expression;
foreach (var fnOp in ops)
{
var pop = PartialEvaluator.Eval(fnOp);
result = QueryBinder.Bind(Expression.Invoke(pop, result), syntax);
}
var projection = (ProjectionExpression)result;
if (projection.Type != expression.Type)
{
var fnAgg = QueryUtility.GetAggregator(expression.Type, projection.Type);
projection = new ProjectionExpression(projection.Select, projection.Projector, fnAgg);
}
return(projection);
}
return(expression);
}
/// <summary>
/// Adds a query parameter.
/// </summary>
/// <typeparam name="U">The type of property to add the query for.</typeparam>
/// <param name="property">The property to add the query for.</param>
/// <param name="value">The value of the query.</param>
/// <returns>The instance of its self with the new query parameter.</returns>
public CardService Where <U>(Expression <Func <CardQueryParameter, U> > property, U value)
{
if (property == null)
{
throw new ArgumentNullException(nameof(property));
}
if (EqualityComparer <U> .Default.Equals(value, default(U)))
{
throw new ArgumentNullException(nameof(value));
}
MemberExpression expression = property.Body as MemberExpression;
var queryName = QueryUtility.GetQueryPropertyName <CardQueryParameter>(expression.Member.Name);
Type valueType = value.GetType();
if (valueType.IsArray)
{
_whereQueries[queryName] = string.Join("|", (IEnumerable <object>)value);
}
else
{
_whereQueries[queryName] = Convert.ToString(value);
}
return(this);
}
public void GetQueryPropertyName_PropertyNameNull_Throws()
{
// arrange
// act
// assert
Assert.Throws <ArgumentNullException>(() => QueryUtility.GetQueryPropertyName <DtoTestObject>(null));
}
//GET
public ActionResult CreateInit()
{
var initViewModel = new ContractCreateInitViewModel();
var currentUser = manager.FindById(User.Identity.GetUserId());
string currentName = currentUser.UserName.ToString();
string currentUserId = currentUser.Id.ToString();
ViewBag.OwnerName = currentUserId;
string currentDepartmentName = QueryUtility.GetDepartmentsOfUser(currentName, db).Select(d => d.DepartmentName).FirstOrDefault();
ViewBag.ClientSelect = new SelectList(db.Mandants, "Id", "MandantName");
IQueryable <Mandant> currentClient = QueryUtility.GetClientOfDepartment(currentDepartmentName, db);
string currentClientName = currentClient.Select(c => c.MandantName).FirstOrDefault();
//ViewBag.CoordinatorId = new SelectList(QueryUtility.GetCoordinatorsFromClient(currentClientName, db), "Id", "UserName");
//ViewBag.ClientId = new SelectList(db.Mandants, "Id", "MandantName");
initViewModel.Coordinators = new SelectList(QueryUtility.GetCoordinatorsFromClient(currentClientName, db), "Id", "UserName");
initViewModel.Signers = new SelectList(db.Users);//Chr: All Users, change Later
return(View(initViewModel));
}
public void GetQueryPropertyName_AttributeDoesNotExist_ReturnsNull()
{
// arrange
// act
var result = QueryUtility.GetQueryPropertyName <DtoTestObject>("Property1");
// assert
Assert.Null(result);
}
public void GetQueryPropertyName_Success()
{
// arrange
// act
var result = QueryUtility.GetQueryPropertyName <DtoTestObject>("Property2");
// assert
Assert.Equal("jsonProperty2", result);
}
public dynamic ExcecuteJoinQuery <T, U>(ref AbstractDbTable <T> tableT, ref AbstractDbTable <U> tableU, string joinKey, Dictionary <string, string> whereKey = null)
where T : AbstractData where U : AbstractData
{
StringBuilder query = new StringBuilder();
query.Append("SELECT");
foreach (var select in tableT.ColAddTableName())
{
query.Append(select);
}
foreach (var select in tableU.ColAddTableName())
{
query.Append(select);
}
query.Append("FROM");
query.Append(tableT.GetTableName());
query.Append("LEFT OUTER JOIN");
query.Append(tableU.GetTableName());
query.Append("IN");
query.Append(tableT + "." + joinKey + "=" + tableU + "." + joinKey);
if (whereKey != null)
{
QueryUtility.CreateWhereQuery(ref query, whereKey);
}
query.Append(";");
Debug.Log(query);
DataTable dt = mDb.ExecuteQuery(query.ToString());
if (dt.Rows.Count == 0)
{
return(null);
}
else
{
var tableTListData = new List <T>();
var tableUListData = new List <U>();
foreach (var row in dt.Rows)
{
tableTListData.Add(tableT.PutJoinData(row));
tableUListData.Add(tableU.PutJoinData(row));
}
return(new
{
tableTListData,
tableUListData
});
}
}
protected override Expression VisitColumn(ColumnExpression column)
{
if (column.Alias == _outerAlias)
{
if (!_map.TryGetValue(column, out NamedValueExpression nv))
{
nv = QueryUtility.GetNamedValueExpression($"n{(_iParam++)}", column, (DbType)column.MapInfo.DataType);
_map.Add(column, nv);
}
return(nv);
}
return(column);
}
/// <inheritdoc />
public ICardService Where <U>(Expression <Func <CardQueryParameter, U> > property, U value)
{
if (EqualityComparer <U> .Default.Equals(value, default))
{
throw new ArgumentNullException(nameof(value));
}
if (!(property.Body is MemberExpression expression))
{
throw new ArgumentNullException(nameof(property));
}
var queryName = QueryUtility.GetQueryPropertyName <CardQueryParameter>(expression.Member.Name);
CurrentQueryUrl.SetQueryParam(queryName, Convert.ToString(value));
return(this);
}
/// <summary>
/// Adds a query parameter.
/// </summary>
/// <typeparam name="U">The type of property to add the query for.</typeparam>
/// <param name="property">The property to add the query for.</param>
/// <param name="value">The value of the query.</param>
/// <returns>The instance of its self with the new query parameter.</returns>
public SetService Where <U>(Expression <Func <SetQueryParameter, U> > property, U value)
{
if (property == null)
{
throw new ArgumentNullException(nameof(property));
}
if (EqualityComparer <U> .Default.Equals(value, default(U)))
{
throw new ArgumentNullException(nameof(value));
}
MemberExpression expression = property.Body as MemberExpression;
var queryName = QueryUtility.GetQueryPropertyName <SetQueryParameter>(expression.Member.Name);
_whereQueries[queryName] = Convert.ToString(value);
return(this);
}
/// <summary>
/// Adds a query parameter.
/// </summary>
/// <typeparam name="U">The type of property to add the query for.</typeparam>
/// <param name="property">The property to add the query for.</param>
/// <param name="value">The value of the query.</param>
/// <returns>The instance of its self with the new query parameter.</returns>
public SetService Where <U>(Expression <Func <SetQueryParameter, U> > property, U value)
{
if (property == null)
{
throw new ArgumentNullException("property");
}
if (value == null)
{
throw new ArgumentNullException("value");
}
MemberExpression expression = property.Body as MemberExpression;
var queryName = QueryUtility.GetQueryPropertyName <SetQueryParameter>(expression.Member.Name);
_whereQueries[queryName] = Convert.ToString(value);
return(this);
}
//Helpers
public ActionResult GetJsonUsersFromClient(string client)
{
var departments = QueryUtility.GetDepartmentsFromClient(client, db);
IEnumerable <ContractUser> users = Enumerable.Empty <ContractUser>();
//users.Concat(new[] { manager.FindByName("Admin") });
foreach (Department d in departments)
{
if (d.DepartmentName != null)
{
IEnumerable <ContractUser> addUser = QueryUtility.GetUsersFromDepartment(d.DepartmentName, db).AsEnumerable <ContractUser>();
if (addUser.Any())
{
users = users.Concat(addUser);
}
}
}
//IQueryable<ContractUser> test = QueryUtility.GetUsersFromDepartment(departments.FirstOrDefault().DepartmentName, db);
List <SelectListItem> data = new SelectList(users, "Id", "UserName").ToList();
return(Json(data));
}
public Expression ApplyPolicy(Expression expression, MemberInfo member, Func <Expression, Expression> builder)
{
if (_operations.TryGetValue(member, out List <LambdaExpression> ops))
{
var syntax = _provider.GetService <ISyntaxProvider>();
var result = expression;
foreach (var fnOp in ops)
{
var pop = PartialEvaluator.Eval(fnOp);
result = builder(Expression.Invoke(pop, result));
}
var projection = (ProjectionExpression)result;
if (projection.Type != expression.Type)
{
var fnAgg = QueryUtility.GetAggregator(expression.Type, projection.Type);
projection = new ProjectionExpression(projection.Select, projection.Projector, fnAgg, projection.IsAsync, projection.IsNoTracking);
}
return(projection);
}
return(expression);
}
//CreateInitHelper
public ContractCreateInitViewModel CreateInitHelper(ContractCreateInitViewModel model)
{
//Get User for SelectLists later
var userId = User.Identity.GetUserId();
var currentUser = manager.FindById(userId);
string currentName = currentUser.UserName.ToString();
//If Contract already exists
if (model.ContractId != null)
{
Contract contract = db.Contracts.Find(model.ContractId);
if (contract != null)
{ //set required attributes
model.OwnerId = contract.OwnerId;
model.Description = contract.Description;
model.SignerName = contract.Signer.UserName;
}
}
else
{
//set Signer to current User
model.SignerName = currentUser.UserName;
//Put default value for Owner in the form
model.OwnerId = userId;
}
//Get SelectLists for Dropdown initialize with default User
model.OwnerList = new SelectList(new[] { manager.FindById(userId) }, "Id", "UserName", model.OwnerId);
//Initializes the ClientList with all existent Clients
model.ClientList = new SelectList(db.Clients, "Id", "ClientName");
//Get Department from User
Department currentDepartment = QueryUtility.GetDepartmentsOfUser(currentName, db).FirstOrDefault();
//Get Client off the current User for the signer-Dropdown
string currentClientName;
if (currentDepartment != null)
{
//Saves the Client of Department of current User in currentClient
IQueryable <Client> currentClient = QueryUtility.GetClientOfDepartment(currentDepartment.DepartmentName, db);
currentClientName = currentClient.Select(c => c.ClientName).FirstOrDefault();
if (currentClientName == null)
{
currentClientName = model.ClientList.FirstOrDefault().Text;
}
}
else
{
//this case is only relevent for Users without a department (should only be the Admin)
currentClientName = model.ClientList.FirstOrDefault().Text;
currentDepartment = db.Departments.FirstOrDefault();
}
//Get the Department from the clients for the Owner-DropDown
model.DepartmentList = new SelectList(QueryUtility.GetDepartmentsFromClient(currentClientName, db), "Id", "DepartmentName", currentDepartment.Id);
return(model);
}
//CreateGeneralHelper
public ContractCreateGeneralViewModel CreateGeneralHelper(ContractCreateGeneralViewModel model)
{
Contract contract = db.Contracts.Find(model.ContractId);
if (contract != null)
{
//Fill model with Data from Reload-Model or from current contract, if Reload-Model is empty
model.ContractKindId = (model.ContractKindId != null) ? model.ContractKindId : ((contract.ContractKind != null) ? (int?)contract.ContractKind.Id : null);
model.ContractTypeId = (model.ContractTypeId != null) ? model.ContractTypeId : ((contract.ContractType != null) ? (int?)contract.ContractType.Id : null);
model.ContractSubTypeId = (model.ContractSubTypeId != null) ? model.ContractSubTypeId : ((contract.ContractSubType != null) ? (int?)contract.ContractSubType.Id : null);
model.DepartmentId = (model.DepartmentId != null) ? model.DepartmentId : ((contract.Department != null) ? (int?)contract.Department.Id : null);
model.DepartmentId = (model.SupervisorDepartmentId != null) ? model.SupervisorDepartmentId : ((contract.SupervisorDepartment != null) ? (int?)contract.SupervisorDepartment.Id : null);
model.Remarks = (model.Remarks != null) ? model.Remarks : contract.Remarks;
model.ExtContractNum = (model.ExtContractNum != null) ? model.ExtContractNum : contract.ExtContractNum;
// If Contract is FrameContract
if (contract.IsFrameContract == true)
{
model.FrameOptionChosen = "FrameMain";
}
else
{
//If Contract is Subcontract
if (contract.FrameContract != null)
{
model.MainFrameIdSelected = contract.FrameContract.Id;
model.FrameOptionChosen = "FrameSub";
}
}
//If a new DocAdress was given
if (model.PhysicalDocAdressId == 0)
{
if (contract.PhysicalDocAddress != null)
{
model.PhysicalDocAdressId = contract.PhysicalDocAddress.Id;
model.PDA_Adress = contract.PhysicalDocAddress.Address;
model.PDA_DepartmentId = contract.PhysicalDocAddress.Department.Id;
}
}
model.ContractPartnerId = (model.ContractPartnerId != 0) ? model.ContractPartnerId : ((contract.ContractPartner != null) ? (int?)contract.ContractPartner.Id : null);
}
var userId = User.Identity.GetUserId();
var currentUser = manager.FindById(userId);
//Set the types and kinds
model.ContractKinds = new SelectList(db.ContractKinds, "Id", "Description", model.ContractKindId);
model.ContractTypes = new SelectList(db.ContractTypes, "Id", "Description", model.ContractTypeId);
model.ContractSubTypes = new SelectList(db.ContractSubTypes, "Id", "Description", model.ContractSubTypeId);
model.ContractPartners = new SelectList(db.ContractPartners, "Id", "Name", model.ContractPartnerId);
//Gets Department of inlogged User
var currentDepartment = QueryUtility.GetDepartmentsOfUser(currentUser.UserName, db);
//Get Client of first Department
var currentClient = QueryUtility.GetClientOfDepartment(currentDepartment.Select(d => d.DepartmentName).FirstOrDefault(), db);
//Get all Departments from Client
var DepartmentsFromClient = QueryUtility.GetDepartmentsFromClient(currentClient.Select(d => d.ClientName).FirstOrDefault(), db);
//Init and set Department of inlogged User as Default value
//Doesn't work now with default value;
if (DepartmentsFromClient.Any())
{
model.Departments = new SelectList(DepartmentsFromClient, "Id", "DepartmentName", currentDepartment.FirstOrDefault().Id);
}
else
{
model.Departments = new SelectList(new[] { "No Departments found" });
}
//Christoph: set up the Dropdownlist for FrameContractChoice:
List <SelectListItem> frameContractChoice = new List <SelectListItem>();
frameContractChoice.Add(new SelectListItem
{
Text = "Kein Rahmenvertrag",
Value = "NoFrame",
Selected = true
});
frameContractChoice.Add(new SelectListItem
{
Text = "Ist Hauptvertrag",
Value = "FrameMain"
});
frameContractChoice.Add(new SelectListItem
{
Text = "Ist Untervertrag",
Value = "FrameSub"
});
model.FrameContractChoice = frameContractChoice;
var frameContracts = QueryUtility.GetFrameContractsOfUser(currentUser.UserName, db);
model.MainFrameContracts = new SelectList(frameContracts, "Id", "Description", "---Select framecontract---");
//Add Contract to View to display its information in following Forms (like Status and Name)
model.Contract = contract;
return(model);
}
//==============================================================================
//Json Queries for Javascript Dropdown
public ActionResult GetJsonCoordinatorsFromClient(string client)
{
var data = new SelectList(QueryUtility.GetCoordinatorsFromClient(client, db), "Id", "UserName").ToList();
return(Json(data));
}
public ActionResult GetJsonDepartmentsFromClient(string client)
{
var data = new SelectList(QueryUtility.GetDepartmentsFromClient(client, db), "Id", "DepartmentName").ToList();
return(Json(data));
}
public ActionResult GetJsonUsersFromDepartment(string department)
{
var data = new SelectList(QueryUtility.GetUsersFromDepartment(department, db), "Id", "UserName").ToList();
return(Json(data));
}
public ActionResult GetJsonContractSubTypesFromContractTypes(string type)
{
var data = new SelectList(QueryUtility.GetContractSubTypesFromContractTypes(type, db), "Id", "Description").ToList();
return(Json(data));
}
public ActionResult GetJsonCostCentersFromClient(string client)
{
var data = new SelectList(QueryUtility.GetCostCentersFromClient(client, db), "Id", "Description").ToList();
return(Json(data));
}
