/// <summary>
/// 获取我处理中的工单
/// </summary>
/// <returns></returns>
public DataTable GetMyHandlingOrders(int pageIndex, int pageSize, out int count)
{
PermissionService permissionService = new PermissionService();
ViewManager manager = new ViewManager("VO_Order");
//1.0 获取当前员工数据
StaffEntity staff = permissionService.GetCurrentStaff();
//2.0 构建where从句
WhereStatement where = new WhereStatement();
WhereClause clause = new WhereClause(OrderEntity.FieldStatus, Comparison.Equals, (int)OrderStatus.Handling);
clause.AddClause(LogicOperator.Or, Comparison.Equals, (int)OrderStatus.Rejected);
where.Add(clause);
where.Add(OrderEntity.FieldHandlerID, Comparison.Equals, staff.ID);
//2.1 构建orderby 从句
OrderByStatement orderby = new OrderByStatement();
orderby.Add(OrderEntity.FieldPriority, Sorting.Descending);
orderby.Add(OrderEntity.FieldBookingTime, Sorting.Ascending);
//3.0 获取已发起的数据
DataTable dt = manager.GetDataTableByPage(where, out count, pageIndex, pageSize, orderby);
return(dt);
}
/// <summary>
/// 获取我的已发起工单
/// </summary>
/// <returns></returns>
public DataTable GetMyStartedOrders(int pageIndex, int pageSize, out int count)
{
WhereStatement where = new WhereStatement();
//string sStatus = JSRequest.GetRequestUrlParm(OrderEntity.FieldStatus, false);
//int? status = JSValidator.ValidateInt(OrderEntity.FieldStatus, sStatus, false);
//if (status != null) { where.Add(OrderEntity.FieldStatus, Comparison.Equals, status); }
PermissionService permissionService = new PermissionService();
ViewManager manager = new ViewManager("VO_Order");
//1.0 获取当前员工数据
StaffEntity staff = permissionService.GetCurrentStaff();
//2.0 构建where从句
where.Add(OrderEntity.FieldStarterID, Comparison.Equals, staff.ID);
where.Add(OrderEntity.FieldStatus, Comparison.NotEquals, (int)OrderStatus.Canceled);
OrderByStatement orderby = new OrderByStatement();
orderby.Add(OrderEntity.FieldPriority, Sorting.Descending);
orderby.Add(OrderEntity.FieldBookingTime, Sorting.Ascending);
//3.0 获取已发起的数据
DataTable dt = manager.GetDataTableByPage(where, out count, pageIndex, pageSize, orderby);
return(dt);
}
//public WhereClause AddWhere(Enum field, Comparison @operator, object compareValue) { return AddWhere(logicalOperator, field.ToString(), @operator, compareValue, 1); }
public WhereClause AddWhere(LogicOperator logicalOperator, string field, Comparison @operator, object compareValue, int level)
{
WhereClause NewWhereClause = new GeneralWhereClause(logicalOperator, field, @operator, compareValue, level);
_whereStatement.Add(NewWhereClause);
return(NewWhereClause);
}
public Dictionary <string, List <string> > GetRolePermissionScope(RoleEntity role, string resouceCode)
{
Dictionary <string, List <string> > dic = new Dictionary <string, List <string> >();
ViewManager vmanager = new ViewManager("VP_UserRolePermissionScope");
WhereStatement where = new WhereStatement();
where.Add("Type", Comparison.Equals, ResourceType.Data.ToString());
where.Add("RoleID", Comparison.Equals, role.ID);
where.Add("Resource_Code", Comparison.Equals, resouceCode);
int count = 0;
DataTable dt = vmanager.GetDataTable(where, out count);// TODO 这里会有性能问题
if (count == 0)
{
throw new JSException(JSErrMsg.ERR_CODE_NotGrantResource, JSErrMsg.ERR_MSG_NotGrantResource);
}
foreach (DataRow dr in dt.Rows)
{
if (!dic.ContainsKey(dr["OrganizeCategory_Code"].ToString()))
{
List <string> list = new List <string>();
list.Add(dr["Organize_Code"].ToString());
dic.Add(dr["OrganizeCategory_Code"].ToString(), list);
continue;
}
dic[dr["OrganizeCategory_Code"].ToString()].Add(dr["Organize_Code"].ToString());
}
return(dic);
}
/// <summary>
/// 获取对应code下一层的button
/// </summary>
/// <param name="role"></param>
/// <param name="resourceCode"></param>
/// <returns></returns>
public DataTable GetButton(RoleEntity role, string resourceCode)
{
ViewManager vmanager = new ViewManager("VP_RolePermission");
WhereStatement where = new WhereStatement();
where.Add("Resource_SortCode", Comparison.Equals, resourceCode);
int count = 0;
DataTable dt = vmanager.GetDataTable(where, out count);
if (count == 0)
{
throw new JSException(JSErrMsg.ERR_CODE_DATA_MISSING, string.Format(JSErrMsg.ERR_MSG_DATA_MISSING, resourceCode));
}
if (count > 1)
{
throw new JSException(JSErrMsg.ERR_CODE_DATA_REPETITION, string.Format(JSErrMsg.ERR_MSG_DATA_REPETITION, "Resource表的" + resourceCode));
}
WhereStatement where1 = new WhereStatement();
where1.Add("Resource_ParentID", Comparison.Equals, dt.Rows[0]["Resource_ParentID"].ToString());
where1.Add("RolePermission_RoleID", Comparison.Equals, role.ID);
int count1 = 0;
DataTable dt1 = vmanager.GetDataTable(where1, out count1);
return(dt1);
}
public List <StaffEntity> GetAllStaffs()
{
EntityManager <StaffEntity> manager = new EntityManager <StaffEntity>();
WhereStatement where = new WhereStatement();
where.Add(StaffEntity.FieldIsEnable, Comparison.Equals, (int)TrueFalse.True);
where.Add(StaffEntity.FieldIsOnJob, Comparison.Equals, (int)TrueFalse.True);
int count = 0;
List <StaffEntity> list = manager.GetList(where, out count);
return(list);
}
/// <inheritdoc />
public ISelectStringBuilder Where(string columnName, ComparisonOperator @operator, object value)
{
var clause = new WhereClause(this, columnName, @operator, value);
WhereStatement.Add(clause);
return(this);
}
public static bool Exists(string name, Dictionary <string, object> entry)
{
var statement = new WhereStatement();
foreach (var key in entry.Keys)
{
var whereClause = new WhereClause {
FieldName = key, ComparisonOperator = Comparison.Equals, Value = entry[key]
};
statement.Add(whereClause);
}
if (statement.Count == 0)
{
return(false);
}
if (!SQLiteProvider.Provider.GetStoreNames().Any(table => table == name))
{
return(false);
}
return(SQLiteProvider.Provider.Single(name, statement, "*") != null);
}
/// <inheritdoc />
public WhereClause Where(string columnName)
{
var clause = new WhereClause(this, columnName);
WhereStatement.Add(clause);
return(clause);
}
public int Update(List <KeyValuePair <string, object> > targetKVPs, object idValue, string idField = null)
{
WhereStatement whereStatement = new WhereStatement(this.DbHelper);
if (idField == null)
{
whereStatement.Add(this._iEntity.PrimaryKey, Comparison.Equals, idValue);
}
else
{
whereStatement.Add(idField, Comparison.Equals, idValue);
}
int rows = this.Update(targetKVPs, whereStatement);
return(rows);
}
public int Update(T entity, object idValue, string idField = null)
{
WhereStatement whereStatement = new WhereStatement(this.DbHelper);
if (idField == null)
{
whereStatement.Add(this._iEntity.PrimaryKey, Comparison.Equals, idValue);
}
else
{
whereStatement.Add(idField, Comparison.Equals, idValue);
}
int rows = this.Update(whereStatement, entity);
return(rows);
}
public int Update(WhereClause whereClause, T entity)
{
WhereStatement whereStatement = new WhereStatement(this.DbHelper);
whereStatement.Add(whereClause);
int rows = this.Update(whereStatement, entity);
return(rows);
}
public int SetDeleted(WhereClause whereClause)
{
WhereStatement statement = new WhereStatement(this.DbHelper);
statement.Add(whereClause);
int rows = this.SetDeleted(statement);
return(rows);
}
public void Test_GetListManager()
{
IDbHelper dbHelper = DbHelperFactory.GetHelper(BaseSystemInfo.CenterDbConnectionString, BaseSystemInfo.CenterDbType);
UserManager manager = new UserManager(dbHelper);
UserEntity entity = new UserEntity();
WhereStatement where = new WhereStatement(dbHelper);
where.Add(new WhereClause(entity.FieldF3, Comparison.In, new string[] { "333", "3333" }));
where.Add(new WhereClause(entity.FieldF2, Comparison.Like, "aa%"));
OrderByStatement orderby = new OrderByStatement();
orderby.Add(entity.PrimaryKey, Sorting.Ascending);
int count = 0;
List <UserEntity> list = manager.GetList(where, out count, orderby);
List <UserEntity> listByPage = manager.GetListByPage(where, out count, 2, 2, orderby);
}
public bool Exists(string targetField, object targetValue)
{
WhereStatement whereStatement = new WhereStatement(this.DbHelper);
whereStatement.Add(targetField, Comparison.Equals, targetValue);
bool b = Exists(whereStatement);
return(b);
}
public override IQueryElement VisitWhere_clause([NotNull] QueryGrammarParser.Where_clauseContext context)
{
WhereStatement where = new WhereStatement();
IQueryElement clause = Visit(context.clause());
where.Add(clause);
return(where);
}
public void Test_GetSingleManager()
{
IDbHelper dbHelper = DbHelperFactory.GetHelper(BaseSystemInfo.CenterDbConnectionString, BaseSystemInfo.CenterDbType);
UserManager manager = new UserManager(dbHelper);
UserEntity entity = new UserEntity();
WhereStatement where = new WhereStatement(dbHelper);
where.Add(new WhereClause(entity.FieldF3, Comparison.Equals, "333"));
entity = manager.GetSingle(23086, whereStatement: where);
}
public void Test_GetCountManager()
{
IDbHelper dbHelper = DbHelperFactory.GetHelper(BaseSystemInfo.CenterDbConnectionString, BaseSystemInfo.CenterDbType);
UserManager manager = new UserManager(dbHelper);
UserEntity entity = new UserEntity();
WhereStatement where = new WhereStatement(dbHelper);
where.Add(new WhereClause(entity.FieldF3, Comparison.Equals, "333"));
int count = manager.GetCount(where);
Console.WriteLine(count);
}
public void Test_ExistsManager()
{
IDbHelper dbHelper = DbHelperFactory.GetHelper(BaseSystemInfo.CenterDbConnectionString, BaseSystemInfo.CenterDbType);
UserManager manager = new UserManager(dbHelper);
UserEntity entity = new UserEntity();
WhereStatement where = new WhereStatement(dbHelper);
where.Add(new WhereClause(entity.FieldF3, Comparison.Equals, "333"));
bool b = manager.Exists(where);
bool b1 = manager.Exists(entity.FieldF2, "222");
}
public DataTable GetAllPermissions()
{
// TODO 先从缓存里面拿，如果没有再从数据库拿
ViewManager vmanager = new ViewManager("VP_RolePermission");
WhereStatement where = new WhereStatement();
where.Add("PermissionItem_IsEnable", Comparison.Equals, (int)TrueFalse.True);
int count = 0;
DataTable dt = vmanager.GetDataTable(where, out count);
return(dt);
}
public void Test_WhereStatement()
{
//WhereStatement 是Where从句
//{Level1}((Age<15 OR Age>=20) AND (strEmail LIKE 'e%')) OR {Level2}(Age BETWEEN 15 AND 20)，同一个LEVEL用AND连接，不同LEVEL用OR连接
IDbHelper dbHelper = DbHelperFactory.GetHelper(BaseSystemInfo.CenterDbConnectionString, BaseSystemInfo.CenterDbType);
UserEntity entity = new UserEntity();
List <DbParameter> parameters = new List <DbParameter>();
parameters.Clear();
WhereStatement statement = new WhereStatement(dbHelper);
statement.Add(new WhereClause(entity.FieldF1, Comparison.Equals, 1)); //and them together
statement.Add(new WhereClause(entity.FieldF2, Comparison.Equals, 2)); //and them together
string whereSql = statement.BuildWhereStatement();
string whereSqlWithParm = statement.BuildWhereStatement(true, out parameters);
Console.WriteLine(whereSql + "，" + whereSqlWithParm);
parameters.Clear();
WhereStatement statement1 = new WhereStatement(dbHelper);
statement1.Add(new WhereClause(entity.FieldF1, Comparison.In, new string[] { "1", "2" }), 1); //or them together
statement1.Add(new WhereClause(entity.FieldF1, Comparison.Equals, 2), 2); //or them together
string whereSql1 = statement1.BuildWhereStatement();
string whereSqlWithParm1 = statement1.BuildWhereStatement(true, out parameters);
Console.WriteLine(whereSql1 + "，" + whereSqlWithParm1);
parameters.Clear();
WhereStatement statement2 = new WhereStatement(dbHelper);
statement2.Add(entity.FieldF1, Comparison.Like, "1%'", 1); //same as new WhereClause
statement2.Add(entity.FieldF2, Comparison.NotLike, "1%'--", 1); //same as new WhereClause
statement2.Add(entity.FieldF2, Comparison.Equals, "1", 2); //same as new WhereClause
string whereSql2 = statement2.BuildWhereStatement();
string whereSqlWithParm2 = statement2.BuildWhereStatement(true, out parameters);
Console.WriteLine(whereSql2 + "，" + whereSqlWithParm2);
}
private int GetLeaderHandlerID(int[] orderHandlerIDs)
{
int count = 0;
EntityManager <OrderHandlerEntity> manager = new EntityManager <OrderHandlerEntity>();
WhereStatement where = new WhereStatement();
where.Add(OrderHandlerEntity.FieldID, Comparison.In, orderHandlerIDs);
List <OrderHandlerEntity> list = manager.GetList(where, out count);
int leaderID = GetLeaderHandlerID(list);
return(leaderID);
}
//驳回报障，需继续处理
public void RejectOrder(Guid orderID, string remark)
{
PermissionService permissionService = new PermissionService();
EntityManager <OrderEntity> orderManager = new EntityManager <OrderEntity>();
EntityManager <OrderHandlerEntity> orderHandlerManager = new EntityManager <OrderHandlerEntity>();
EntityManager <OrderFlowEntity> orderflowManager = new EntityManager <OrderFlowEntity>();
//1.0 获取当前员工数据
StaffEntity staff = permissionService.GetCurrentStaff();
//1.1 获取工单处理者列表，必备获取领队人的信息
int count = 0;
WhereStatement where = new WhereStatement();
where.Add(OrderHandlerEntity.FieldOrderID, Comparison.Equals, orderID);
List <OrderHandlerEntity> orderHandlers = orderHandlerManager.GetList(where, out count);
//2.0 修改工单实体
List <KeyValuePair <string, object> > kvps = new List <KeyValuePair <string, object> >();
kvps.Add(new KeyValuePair <string, object>(OrderEntity.FieldOperatorID, staff.ID));
kvps.Add(new KeyValuePair <string, object>(OrderEntity.FieldNextOperatorID, GetLeaderHandlerID(orderHandlers)));
kvps.Add(new KeyValuePair <string, object>(OrderEntity.FieldStatus, (int)OrderStatus.Rejected));
kvps.Add(new KeyValuePair <string, object>(OrderEntity.FieldOperateTime, DateTime.Now));
int rows = orderManager.Update(kvps, orderID);
if (rows == 0)
{
throw new JSException(JSErrMsg.ERR_CODE_DATA_MISSING, string.Format(JSErrMsg.ERR_MSG_DATA_MISSING, "工单实体"));
}
//3.0 添加工作流实体
OrderFlowEntity orderflow = new OrderFlowEntity();
orderflow.OrderID = orderID;
orderflow.OperatorID = staff.ID;
orderflow.NextOperatorID = GetLeaderHandlerID(orderHandlers);
orderflow.Operation = (int)OperationEnum.reject;
orderflow.OperateTime = DateTime.Now;
orderflow.Remark = remark;
orderflowManager.Insert(orderflow);
OrderHandleDetailEntity orderHandleDetail = new OrderHandleDetailEntity();
orderHandleDetail.OrderID = orderID;
orderHandleDetail.HandleType = (int)OrderHandleType.Others;
orderHandleDetail.HandleDetail = "驳回";
orderHandleDetail.Remark = remark;
AddHandleDetail(orderHandleDetail);
}
public void Test_WhereClause()
{
//WhereClause 是where从句的子句，
IDbHelper dbHelper = DbHelperFactory.GetHelper(BaseSystemInfo.CenterDbConnectionString, BaseSystemInfo.CenterDbType);
UserEntity entity = new UserEntity();
WhereClause clause = new WhereClause(entity.FieldF1, Comparison.Equals, 1);
clause.AddClause(LogicOperator.Or, Comparison.In, new int[] { 1, 2, 3 });// TODO
WhereStatement statement = new WhereStatement(dbHelper);
statement.Add(clause);
string where = statement.BuildWhereStatement();
Assert.AreEqual(@"(F1 = 1 OR F1 IN ('1', '2', '3'))", where.Trim());
Console.WriteLine(where);
WhereClause clause1 = new WhereClause(entity.FieldF1, Comparison.Equals, 1);
clause1.AddClause(LogicOperator.Or, Comparison.In, 1);// TODO
WhereStatement statement1 = new WhereStatement(dbHelper);
statement1.Add(clause1);
string where1 = statement1.BuildWhereStatement();
Assert.AreEqual(@"(F1 = 1 OR F1 IN (1))", where1.Trim());
Console.WriteLine(where1);
WhereClause clause2 = new WhereClause(entity.FieldF1, Comparison.Equals, 1);
clause2.AddClause(LogicOperator.Or, Comparison.In, new string[] { "1", "2", "3" });// TODO
WhereStatement statement2 = new WhereStatement(dbHelper);
statement2.Add(clause2);
string where2 = statement2.BuildWhereStatement();
Assert.AreEqual(@"(F1 = 1 OR F1 IN ('1', '2', '3'))", where2.Trim());
Console.WriteLine(where2);
WhereClause clause3 = new WhereClause(entity.FieldF1, Comparison.Equals, DBNull.Value);
clause3.AddClause(LogicOperator.Or, Comparison.In, new string[] { "1", "2", "3" });// TODO
WhereStatement statement3 = new WhereStatement(dbHelper);
statement3.Add(clause3);
string where3 = statement3.BuildWhereStatement();
Assert.AreEqual(@"(F1 IS NULL OR F1 IN ('1', '2', '3'))", where3.Trim());
Console.WriteLine(where3);
}
public DataTable GetOrderHandlers(Guid orderID)
{
//只能查视图
ViewManager manager = new ViewManager("VO_OrderHandlers");
WhereStatement where = new WhereStatement();
where.Add(OrderHandlerEntity.FieldOrderID, Comparison.Equals, orderID.ToString());
OrderByStatement orderby = new OrderByStatement();
orderby.Add(OrderHandlerEntity.FieldIsLeader, Sorting.Descending);
int count = 0;
DataTable dt = manager.GetDataTable(where, out count, orderby);
return(dt);
}
public void Test_GetDataTableManager()
{
IDbHelper dbHelper = DbHelperFactory.GetHelper(BaseSystemInfo.CenterDbConnectionString, BaseSystemInfo.CenterDbType);
UserManager manager = new UserManager(dbHelper);
UserEntity entity = new UserEntity();
WhereStatement where = new WhereStatement(dbHelper);
where.Add(new WhereClause(entity.FieldF3, Comparison.Equals, "333"));
OrderByStatement orderby = new OrderByStatement();
orderby.Add(entity.PrimaryKey, Sorting.Ascending);
int count = 0;
DataTable dt1 = manager.GetDataTable(where, out count, orderby);
DataTable dt2 = manager.GetDataTableByPage(where, out count, 2, 2, orderby);
}
public static List <Dictionary <string, object> > Search(string name, string columnName, object value)
{
var column = dataFields.FirstOrDefault(field => field.ColumnName == columnName);
if (column == null)
{
Log.Error("[StoreProvider.GetSingle] No primary field found");
return(null);
}
var where = new WhereStatement();
where.Add(new WhereClause {
FieldName = column.ColumnName, ComparisonOperator = Comparison.Equals, Value = value
});
return(SQLiteProvider.Provider.Select(name, where).ToList());
}
/// <summary>
/// 获取 指定资源code 的整个树形menu
/// </summary>
/// <param name="role"></param>
/// <param name="resourceCode">资源code</param>
/// <returns></returns>
public DataTable GetMenu(RoleEntity role, string resourceCode)
{
//1.0 获取已授权的树形menu
string[] ids = GetTreeMenuIds(role, resourceCode);
//2.0 根据id 获取详细信息
ViewManager vmanager = new ViewManager("VP_RolePermission");
WhereStatement where = new WhereStatement();
where.Add("ID", Comparison.In, ids);
OrderByStatement order = new OrderByStatement("Resource_SortCode", Sorting.Ascending);
int count = 0;
DataTable dt = vmanager.GetDataTable(where, out count, order);
return(dt);
}
public void Test_GetIdsManager()
{
IDbHelper dbHelper = DbHelperFactory.GetHelper(BaseSystemInfo.CenterDbConnectionString, BaseSystemInfo.CenterDbType);
UserManager manager = new UserManager(dbHelper);
UserEntity entity = new UserEntity();
WhereStatement where = new WhereStatement(dbHelper);
//where.Add(new WhereClause(entity.FieldF3, Comparison.Equals, "333"));//Test Return Data
where.Add(new WhereClause(entity.FieldF3, Comparison.Equals, "3334"));//Test Return String[0]
OrderByStatement orderby = new OrderByStatement();
orderby.Add(entity.PrimaryKey, Sorting.Ascending);
orderby.Add(entity.FieldDateTimeType, Sorting.Descending);
string[] ids = manager.GetIds(where, orderby);
Console.WriteLine(ids);
}
public void Test_DeleteManager()
{
IDbHelper dbHelper = DbHelperFactory.GetHelper(BaseSystemInfo.CenterDbConnectionString, BaseSystemInfo.CenterDbType);
UserManager userManager = new UserManager(dbHelper);
UserEntity userEntity = new UserEntity();
userManager.Delete(23081);
WhereClause clause = new WhereClause(userEntity.FieldNumberType, Comparison.GreaterOrEquals, 4);
clause.AddClause(LogicOperator.And, Comparison.LessOrEquals, 6);
userManager.Delete(clause);
WhereStatement statement = new WhereStatement(dbHelper);
statement.Add(userEntity.FieldNumberType, Comparison.GreaterOrEquals, 2);
userManager.Delete(statement);
//userManager.SetDeleted(23081);
}
