/// <summary>
/// 获取已审核流程列表
/// </summary>
/// <param name="categoryId">分类主键</param>
/// <param name="categorybillFullName">流程</param>
/// <param name="searchValue">关键字</param>
/// <returns></returns>
public DataTable GetAuditRecord(string categoryCode, string categorybillFullName = null, string searchValue = null)
{
string sqlQuery = @" SELECT TOP 400 BaseWorkFlowCurrent.*
FROM BaseWorkFlowCurrent,
(SELECT CurrentFlowId AS CurrentFlowId, MAX(AuditDate) AS AuditDate
FROM BaseWorkFlowHistory
WHERE (BaseWorkFlowHistory.DeletionStateCode = 0)
AND (BaseWorkFlowHistory.AuditUserId = " + this.UserInfo.Id + " OR BaseWorkFlowHistory.ToUserId = " + this.UserInfo.Id + @")
AND (BaseWorkFlowHistory.DeletionStateCode = 0)
GROUP BY CurrentFlowId
) AS BaseWorkFlowHistory
WHERE BaseWorkFlowCurrent.DeletionStateCode = 0
AND BaseWorkFlowCurrent.Id = BaseWorkFlowHistory.CurrentFlowId";
List <IDbDataParameter> dbParameters = new List <IDbDataParameter>();
if (!string.IsNullOrEmpty(categoryCode))
{
BaseWorkFlowBillTemplateManager templateManager = new BaseWorkFlowBillTemplateManager(this.DbHelper, this.UserInfo);
DataTable dataTable = templateManager.Search(string.Empty, categoryCode, string.Empty, null, false);
string categoryCodes = BaseBusinessLogic.FieldToList(dataTable, BaseWorkFlowBillTemplateEntity.FieldCode);
if (!string.IsNullOrEmpty(categoryCodes))
{
sqlQuery += " AND (BaseWorkFlowCurrent.CategoryCode IN (" + categoryCodes + ")) ";
}
}
if (!string.IsNullOrEmpty(categorybillFullName))
{
sqlQuery += " AND (BaseWorkFlowCurrent.CategoryFullName ='" + categorybillFullName + "') ";
}
if (!String.IsNullOrEmpty(searchValue))
{
searchValue = searchValue.Trim();
sqlQuery += " AND (" + BaseWorkFlowCurrentEntity.FieldObjectFullName + " LIKE " + DbHelper.GetParameter(BaseWorkFlowCurrentEntity.FieldObjectFullName);
sqlQuery += " OR " + BaseWorkFlowCurrentEntity.FieldAuditUserRealName + " LIKE " + DbHelper.GetParameter(BaseWorkFlowCurrentEntity.FieldAuditUserRealName);
sqlQuery += " OR " + BaseWorkFlowCurrentEntity.FieldAuditIdea + " LIKE " + DbHelper.GetParameter(BaseWorkFlowCurrentEntity.FieldAuditIdea);
sqlQuery += " OR " + BaseWorkFlowCurrentEntity.FieldAuditStatusName + " LIKE " + DbHelper.GetParameter(BaseWorkFlowCurrentEntity.FieldAuditStatusName);
sqlQuery += " OR " + BaseWorkFlowCurrentEntity.FieldToDepartmentName + " LIKE " + DbHelper.GetParameter(BaseWorkFlowCurrentEntity.FieldToDepartmentName);
sqlQuery += " OR " + BaseWorkFlowCurrentEntity.FieldToUserRealName + " LIKE " + DbHelper.GetParameter(BaseWorkFlowCurrentEntity.FieldToUserRealName) + ")";
if (searchValue.IndexOf("%") < 0)
{
searchValue = "%" + searchValue + "%";
}
dbParameters.Add(DbHelper.MakeParameter(BaseWorkFlowCurrentEntity.FieldObjectFullName, searchValue));
dbParameters.Add(DbHelper.MakeParameter(BaseWorkFlowCurrentEntity.FieldAuditUserRealName, searchValue));
dbParameters.Add(DbHelper.MakeParameter(BaseWorkFlowCurrentEntity.FieldAuditIdea, searchValue));
dbParameters.Add(DbHelper.MakeParameter(BaseWorkFlowCurrentEntity.FieldAuditStatusName, searchValue));
dbParameters.Add(DbHelper.MakeParameter(BaseWorkFlowCurrentEntity.FieldToDepartmentName, searchValue));
dbParameters.Add(DbHelper.MakeParameter(BaseWorkFlowCurrentEntity.FieldToUserRealName, searchValue));
}
sqlQuery += " ORDER BY BaseWorkFlowHistory.AuditDate DESC ";
return(DbHelper.Fill(sqlQuery, dbParameters.ToArray()));
}
/// <summary>
/// 获取已审核流程列表
/// </summary>
/// <param name="categoryId">分类主键</param>
/// <param name="billCategoryCode">流程</param>
/// <param name="searchValue">关键字</param>
/// <returns></returns>
public DataTable GetUserAllBill(string categoryId, string billCategoryCode = null, string searchValue = null)
{
string sqlQuery = @" SELECT BaseWorkFlowCurrent.*
FROM BaseWorkFlowCurrent
WHERE BaseWorkFlowCurrent.DELETIONSTATECODE = 0
AND (BaseWorkFlowCurrent.CreateUserId = '" + this.UserInfo.Id + "')";
List <IDbDataParameter> dbParameters = new List <IDbDataParameter>();
if (!string.IsNullOrEmpty(categoryId))
{
BaseWorkFlowBillTemplateManager templateManager = new BaseWorkFlowBillTemplateManager(this.DbHelper, this.UserInfo);
DataTable dataTable = templateManager.Search(string.Empty, categoryId, string.Empty, null, false);
string categoryCodes = BaseBusinessLogic.FieldToList(dataTable, BaseWorkFlowBillTemplateEntity.FieldCode);
if (!string.IsNullOrEmpty(categoryCodes))
{
sqlQuery += " AND (BaseWorkFlowCurrent.CategoryCode IN (" + categoryCodes + ")) ";
}
}
if (!string.IsNullOrEmpty(billCategoryCode))
{
sqlQuery += " AND (BaseWorkFlowCurrent.CategoryCode ='" + billCategoryCode + "') ";
}
if (!String.IsNullOrEmpty(searchValue))
{
searchValue = searchValue.Trim();
sqlQuery += " AND (" + BaseWorkFlowCurrentEntity.FieldObjectFullName + " LIKE " + DbHelper.GetParameter(BaseWorkFlowCurrentEntity.FieldObjectFullName);
sqlQuery += " OR " + BaseWorkFlowCurrentEntity.FieldAuditUserRealName + " LIKE " + DbHelper.GetParameter(BaseWorkFlowCurrentEntity.FieldAuditUserRealName);
sqlQuery += " OR " + BaseWorkFlowCurrentEntity.FieldAuditIdea + " LIKE " + DbHelper.GetParameter(BaseWorkFlowCurrentEntity.FieldAuditIdea);
sqlQuery += " OR " + BaseWorkFlowCurrentEntity.FieldToDepartmentName + " LIKE " + DbHelper.GetParameter(BaseWorkFlowCurrentEntity.FieldToDepartmentName);
sqlQuery += " OR " + BaseWorkFlowCurrentEntity.FieldToUserRealName + " LIKE " + DbHelper.GetParameter(BaseWorkFlowCurrentEntity.FieldToUserRealName) + ")";
if (searchValue.IndexOf("%") < 0)
{
searchValue = "%" + searchValue + "%";
}
dbParameters.Add(DbHelper.MakeParameter(BaseWorkFlowCurrentEntity.FieldObjectFullName, searchValue));
dbParameters.Add(DbHelper.MakeParameter(BaseWorkFlowCurrentEntity.FieldAuditUserRealName, searchValue));
dbParameters.Add(DbHelper.MakeParameter(BaseWorkFlowCurrentEntity.FieldAuditIdea, searchValue));
dbParameters.Add(DbHelper.MakeParameter(BaseWorkFlowCurrentEntity.FieldToDepartmentName, searchValue));
dbParameters.Add(DbHelper.MakeParameter(BaseWorkFlowCurrentEntity.FieldToUserRealName, searchValue));
}
sqlQuery += " ORDER BY BaseWorkFlowCurrent.SORTCODE ";
return(DbHelper.Fill(sqlQuery, dbParameters.ToArray()));
}
/// <summary>
/// 获取已审核流程列表
/// </summary>
/// <param name="categoryId">分类主键</param>
/// <param name="categorybillFullName">流程</param>
/// <param name="searchValue">关键字</param>
/// <returns></returns>
public DataTable GetAuditRecord(string categoryCode, string categorybillFullName = null, string searchValue = null)
{
string sqlQuery = @" SELECT TOP 400 BaseWorkFlowCurrent.*
FROM BaseWorkFlowCurrent,
(SELECT CurrentFlowId AS CurrentFlowId, MAX(AuditDate) AS AuditDate
FROM BaseWorkFlowHistory
WHERE (BaseWorkFlowHistory.DeletionStateCode = 0)
AND (BaseWorkFlowHistory.AuditUserId = " + this.UserInfo.Id + " OR BaseWorkFlowHistory.ToUserId = " + this.UserInfo.Id + @")
AND (BaseWorkFlowHistory.DeletionStateCode = 0)
GROUP BY CurrentFlowId
) AS BaseWorkFlowHistory
WHERE BaseWorkFlowCurrent.DeletionStateCode = 0
AND BaseWorkFlowCurrent.Id = BaseWorkFlowHistory.CurrentFlowId";
List<IDbDataParameter> dbParameters = new List<IDbDataParameter>();
if (!string.IsNullOrEmpty(categoryCode))
{
BaseWorkFlowBillTemplateManager templateManager = new BaseWorkFlowBillTemplateManager(this.DbHelper, this.UserInfo);
DataTable dataTable = templateManager.Search(string.Empty, categoryCode, string.Empty, null, false);
string categoryCodes = BaseBusinessLogic.FieldToList(dataTable, BaseWorkFlowBillTemplateEntity.FieldCode);
if (!string.IsNullOrEmpty(categoryCodes))
{
sqlQuery += " AND (BaseWorkFlowCurrent.CategoryCode IN (" + categoryCodes + ")) ";
}
}
if (!string.IsNullOrEmpty(categorybillFullName))
{
sqlQuery += " AND (BaseWorkFlowCurrent.CategoryFullName ='" + categorybillFullName + "') ";
}
if (!String.IsNullOrEmpty(searchValue))
{
searchValue = searchValue.Trim();
sqlQuery += " AND (" + BaseWorkFlowCurrentEntity.FieldObjectFullName + " LIKE " + DbHelper.GetParameter(BaseWorkFlowCurrentEntity.FieldObjectFullName);
sqlQuery += " OR " + BaseWorkFlowCurrentEntity.FieldAuditUserRealName + " LIKE " + DbHelper.GetParameter(BaseWorkFlowCurrentEntity.FieldAuditUserRealName);
sqlQuery += " OR " + BaseWorkFlowCurrentEntity.FieldAuditIdea + " LIKE " + DbHelper.GetParameter(BaseWorkFlowCurrentEntity.FieldAuditIdea);
sqlQuery += " OR " + BaseWorkFlowCurrentEntity.FieldAuditStatusName + " LIKE " + DbHelper.GetParameter(BaseWorkFlowCurrentEntity.FieldAuditStatusName);
sqlQuery += " OR " + BaseWorkFlowCurrentEntity.FieldToDepartmentName + " LIKE " + DbHelper.GetParameter(BaseWorkFlowCurrentEntity.FieldToDepartmentName);
sqlQuery += " OR " + BaseWorkFlowCurrentEntity.FieldToUserRealName + " LIKE " + DbHelper.GetParameter(BaseWorkFlowCurrentEntity.FieldToUserRealName) + ")";
if (searchValue.IndexOf("%") < 0)
{
searchValue = "%" + searchValue + "%";
}
dbParameters.Add(DbHelper.MakeParameter(BaseWorkFlowCurrentEntity.FieldObjectFullName, searchValue));
dbParameters.Add(DbHelper.MakeParameter(BaseWorkFlowCurrentEntity.FieldAuditUserRealName, searchValue));
dbParameters.Add(DbHelper.MakeParameter(BaseWorkFlowCurrentEntity.FieldAuditIdea, searchValue));
dbParameters.Add(DbHelper.MakeParameter(BaseWorkFlowCurrentEntity.FieldAuditStatusName, searchValue));
dbParameters.Add(DbHelper.MakeParameter(BaseWorkFlowCurrentEntity.FieldToDepartmentName, searchValue));
dbParameters.Add(DbHelper.MakeParameter(BaseWorkFlowCurrentEntity.FieldToUserRealName, searchValue));
}
sqlQuery += " ORDER BY BaseWorkFlowHistory.AuditDate DESC ";
return DbHelper.Fill(sqlQuery, dbParameters.ToArray());
}
/// <summary>
/// 获取等审核信息
/// </summary>
/// <param name="userId">用户主键</param>
/// <param name="categoryCode">分类代码</param>
/// <param name="searchValue">查询字符串</param>
/// <param name="showAuditReject">显示退回的</param>
/// <returns>数据表</returns>
public DataTable GetWaitForAudit(string userId = null, string categoryCode = null, string categorybillFullName = null, string searchValue = null, bool showAuditReject = true)
{
if (string.IsNullOrEmpty(userId))
{
userId = this.UserInfo.Id;
}
string sqlQuery = " SELECT * "
+ " FROM " + BaseWorkFlowCurrentEntity.TableName
// 未被删除的，有效的数据，还没能审核结束的
+ " WHERE (" + BaseWorkFlowCurrentEntity.FieldDeletionStateCode + " = 0) "
// Enabled 0 表示，审核还没结束
+ " AND (" + BaseWorkFlowCurrentEntity.FieldEnabled + " = 0) ";
if (!showAuditReject)
{
sqlQuery += " AND (" + BaseWorkFlowCurrentEntity.FieldAuditStatus + " != 'AuditReject') ";
}
if (!string.IsNullOrEmpty(userId))
{
// 待审核的工作流（指向用户的）
switch (BaseSystemInfo.UserCenterDbType)
{
case DbTypes.Access:
sqlQuery += " AND (" + BaseWorkFlowCurrentEntity.FieldToUserId + "= '" + userId + "' ";
break;
default:
sqlQuery += " AND (" + BaseWorkFlowCurrentEntity.FieldToUserId + "=" + userId + " ";
break;
}
//（指向角色的）
BaseUserManager userManager = new BaseUserManager(this.UserInfo);
string[] roleIds = userManager.GetAllRoleIds(userId);
if (roleIds != null && roleIds.Length > 0)
{
sqlQuery += " OR " + BaseWorkFlowCurrentEntity.FieldToRoleId + " IN (" + StringUtil.ArrayToList(roleIds) + ")";
}
//（指向部门的）
string[] organizeIds = userManager.GetAllOrganizeIds(userId);
if (organizeIds != null && organizeIds.Length > 0)
{
sqlQuery += " OR (" + BaseWorkFlowCurrentEntity.FieldToUserId + " IS NULL AND + " + BaseWorkFlowCurrentEntity.FieldToDepartmentId + " IN (" + StringUtil.ArrayToList(organizeIds) + "))";
}
sqlQuery += " ) ";
}
if (!string.IsNullOrEmpty(categoryCode))
{
BaseWorkFlowBillTemplateManager templateManager = new BaseWorkFlowBillTemplateManager(this.DbHelper, this.UserInfo);
DataTable dataTable = templateManager.Search(string.Empty, categoryCode, string.Empty, null, false);
string categoryCodes = BaseBusinessLogic.FieldToList(dataTable, BaseWorkFlowBillTemplateEntity.FieldCode);
if (!string.IsNullOrEmpty(categoryCodes))
{
sqlQuery += " AND (BaseWorkFlowCurrent.CategoryCode IN (" + categoryCodes + ")) ";
}
}
if (!string.IsNullOrEmpty(categorybillFullName))
{
sqlQuery += " AND (" + BaseWorkFlowCurrentEntity.TableName + "." + BaseWorkFlowCurrentEntity.FieldCategoryFullName + " ='" + categorybillFullName + "') ";
}
List<IDbDataParameter> dbParameters = new List<IDbDataParameter>();
if (!String.IsNullOrEmpty(searchValue))
{
searchValue = searchValue.Trim();
sqlQuery += " AND (" + BaseWorkFlowCurrentEntity.FieldObjectFullName + " LIKE " + DbHelper.GetParameter(BaseWorkFlowCurrentEntity.FieldObjectFullName);
sqlQuery += " OR " + BaseWorkFlowCurrentEntity.FieldAuditUserRealName + " LIKE " + DbHelper.GetParameter(BaseWorkFlowCurrentEntity.FieldAuditUserRealName);
sqlQuery += " OR " + BaseWorkFlowCurrentEntity.FieldAuditIdea + " LIKE " + DbHelper.GetParameter(BaseWorkFlowCurrentEntity.FieldAuditIdea);
sqlQuery += " OR " + BaseWorkFlowCurrentEntity.FieldAuditStatusName + " LIKE " + DbHelper.GetParameter(BaseWorkFlowCurrentEntity.FieldAuditStatusName);
sqlQuery += " OR " + BaseWorkFlowCurrentEntity.FieldToDepartmentName + " LIKE " + DbHelper.GetParameter(BaseWorkFlowCurrentEntity.FieldToDepartmentName);
sqlQuery += " OR " + BaseWorkFlowCurrentEntity.FieldToUserRealName + " LIKE " + DbHelper.GetParameter(BaseWorkFlowCurrentEntity.FieldToUserRealName) + ")";
if (searchValue.IndexOf("%") < 0)
{
searchValue = "%" + searchValue + "%";
}
dbParameters.Add(DbHelper.MakeParameter(BaseWorkFlowCurrentEntity.FieldObjectFullName, searchValue));
dbParameters.Add(DbHelper.MakeParameter(BaseWorkFlowCurrentEntity.FieldAuditUserRealName, searchValue));
dbParameters.Add(DbHelper.MakeParameter(BaseWorkFlowCurrentEntity.FieldAuditIdea, searchValue));
dbParameters.Add(DbHelper.MakeParameter(BaseWorkFlowCurrentEntity.FieldAuditStatusName, searchValue));
dbParameters.Add(DbHelper.MakeParameter(BaseWorkFlowCurrentEntity.FieldToDepartmentName, searchValue));
dbParameters.Add(DbHelper.MakeParameter(BaseWorkFlowCurrentEntity.FieldToUserRealName, searchValue));
}
// 排序字段
sqlQuery += " ORDER BY " + BaseWorkFlowCurrentEntity.FieldSendDate;
return DbHelper.Fill(sqlQuery, dbParameters.ToArray());
}
/// <summary>
/// 获取已审核流程列表
/// </summary>
/// <param name="categoryId">分类主键</param>
/// <param name="billCategoryCode">流程</param>
/// <param name="searchValue">关键字</param>
/// <returns></returns>
public DataTable GetUserAllBill(string categoryId, string billCategoryCode = null, string searchValue = null)
{
string sqlQuery = @" SELECT BaseWorkFlowCurrent.*
FROM BaseWorkFlowCurrent
WHERE BaseWorkFlowCurrent.DELETIONSTATECODE = 0
AND (BaseWorkFlowCurrent.CreateUserId = '" + this.UserInfo.Id + "')";
List<IDbDataParameter> dbParameters = new List<IDbDataParameter>();
if (!string.IsNullOrEmpty(categoryId))
{
BaseWorkFlowBillTemplateManager templateManager = new BaseWorkFlowBillTemplateManager(this.DbHelper, this.UserInfo);
DataTable dataTable = templateManager.Search(string.Empty, categoryId, string.Empty, null, false);
string categoryCodes = BaseBusinessLogic.FieldToList(dataTable, BaseWorkFlowBillTemplateEntity.FieldCode);
if (!string.IsNullOrEmpty(categoryCodes))
{
sqlQuery += " AND (BaseWorkFlowCurrent.CategoryCode IN (" + categoryCodes + ")) ";
}
}
if (!string.IsNullOrEmpty(billCategoryCode))
{
sqlQuery += " AND (BaseWorkFlowCurrent.CategoryCode ='" + billCategoryCode + "') ";
}
if (!String.IsNullOrEmpty(searchValue))
{
searchValue = searchValue.Trim();
sqlQuery += " AND (" + BaseWorkFlowCurrentEntity.FieldObjectFullName + " LIKE " + DbHelper.GetParameter(BaseWorkFlowCurrentEntity.FieldObjectFullName);
sqlQuery += " OR " + BaseWorkFlowCurrentEntity.FieldAuditUserRealName + " LIKE " + DbHelper.GetParameter(BaseWorkFlowCurrentEntity.FieldAuditUserRealName);
sqlQuery += " OR " + BaseWorkFlowCurrentEntity.FieldAuditIdea + " LIKE " + DbHelper.GetParameter(BaseWorkFlowCurrentEntity.FieldAuditIdea);
sqlQuery += " OR " + BaseWorkFlowCurrentEntity.FieldToDepartmentName + " LIKE " + DbHelper.GetParameter(BaseWorkFlowCurrentEntity.FieldToDepartmentName);
sqlQuery += " OR " + BaseWorkFlowCurrentEntity.FieldToUserRealName + " LIKE " + DbHelper.GetParameter(BaseWorkFlowCurrentEntity.FieldToUserRealName) + ")";
if (searchValue.IndexOf("%") < 0)
{
searchValue = "%" + searchValue + "%";
}
dbParameters.Add(DbHelper.MakeParameter(BaseWorkFlowCurrentEntity.FieldObjectFullName, searchValue));
dbParameters.Add(DbHelper.MakeParameter(BaseWorkFlowCurrentEntity.FieldAuditUserRealName, searchValue));
dbParameters.Add(DbHelper.MakeParameter(BaseWorkFlowCurrentEntity.FieldAuditIdea, searchValue));
dbParameters.Add(DbHelper.MakeParameter(BaseWorkFlowCurrentEntity.FieldToDepartmentName, searchValue));
dbParameters.Add(DbHelper.MakeParameter(BaseWorkFlowCurrentEntity.FieldToUserRealName, searchValue));
}
sqlQuery += " ORDER BY BaseWorkFlowCurrent.SORTCODE ";
return DbHelper.Fill(sqlQuery, dbParameters.ToArray());
}
/// <summary>
/// 获取等审核信息
/// </summary>
/// <param name="userId">用户主键</param>
/// <param name="categoryCode">分类代码</param>
/// <param name="searchValue">查询字符串</param>
/// <param name="showAuditReject">显示退回的</param>
/// <returns>数据表</returns>
public DataTable GetWaitForAudit(string userId = null, string categoryCode = null, string categorybillFullName = null, string searchValue = null, bool showAuditReject = true)
{
if (string.IsNullOrEmpty(userId))
{
userId = this.UserInfo.Id;
}
string sqlQuery = " SELECT * "
+ " FROM " + BaseWorkFlowCurrentEntity.TableName
// 未被删除的，有效的数据，还没能审核结束的
+ " WHERE (" + BaseWorkFlowCurrentEntity.FieldDeletionStateCode + " = 0) "
// Enabled 0 表示，审核还没结束
+ " AND (" + BaseWorkFlowCurrentEntity.FieldEnabled + " = 0) ";
if (!showAuditReject)
{
sqlQuery += " AND (" + BaseWorkFlowCurrentEntity.FieldAuditStatus + " != 'AuditReject') ";
}
if (!string.IsNullOrEmpty(userId))
{
// 待审核的工作流（指向用户的）
switch (BaseSystemInfo.UserCenterDbType)
{
case DbTypes.Access:
sqlQuery += " AND (" + BaseWorkFlowCurrentEntity.FieldToUserId + "= '" + userId + "' ";
break;
default:
sqlQuery += " AND (" + BaseWorkFlowCurrentEntity.FieldToUserId + "=" + userId + " ";
break;
}
//（指向角色的）
BaseUserManager userManager = new BaseUserManager(this.UserInfo);
string[] roleIds = userManager.GetAllRoleIds(userId);
if (roleIds != null && roleIds.Length > 0)
{
sqlQuery += " OR " + BaseWorkFlowCurrentEntity.FieldToRoleId + " IN (" + StringUtil.ArrayToList(roleIds) + ")";
}
//（指向部门的）
string[] organizeIds = userManager.GetAllOrganizeIds(userId);
if (organizeIds != null && organizeIds.Length > 0)
{
sqlQuery += " OR (" + BaseWorkFlowCurrentEntity.FieldToUserId + " IS NULL AND + " + BaseWorkFlowCurrentEntity.FieldToDepartmentId + " IN (" + StringUtil.ArrayToList(organizeIds) + "))";
}
sqlQuery += " ) ";
}
if (!string.IsNullOrEmpty(categoryCode))
{
BaseWorkFlowBillTemplateManager templateManager = new BaseWorkFlowBillTemplateManager(this.DbHelper, this.UserInfo);
DataTable dataTable = templateManager.Search(string.Empty, categoryCode, string.Empty, null, false);
string categoryCodes = BaseBusinessLogic.FieldToList(dataTable, BaseWorkFlowBillTemplateEntity.FieldCode);
if (!string.IsNullOrEmpty(categoryCodes))
{
sqlQuery += " AND (BaseWorkFlowCurrent.CategoryCode IN (" + categoryCodes + ")) ";
}
}
if (!string.IsNullOrEmpty(categorybillFullName))
{
sqlQuery += " AND (" + BaseWorkFlowCurrentEntity.TableName + "." + BaseWorkFlowCurrentEntity.FieldCategoryFullName + " ='" + categorybillFullName + "') ";
}
List <IDbDataParameter> dbParameters = new List <IDbDataParameter>();
if (!String.IsNullOrEmpty(searchValue))
{
searchValue = searchValue.Trim();
sqlQuery += " AND (" + BaseWorkFlowCurrentEntity.FieldObjectFullName + " LIKE " + DbHelper.GetParameter(BaseWorkFlowCurrentEntity.FieldObjectFullName);
sqlQuery += " OR " + BaseWorkFlowCurrentEntity.FieldAuditUserRealName + " LIKE " + DbHelper.GetParameter(BaseWorkFlowCurrentEntity.FieldAuditUserRealName);
sqlQuery += " OR " + BaseWorkFlowCurrentEntity.FieldAuditIdea + " LIKE " + DbHelper.GetParameter(BaseWorkFlowCurrentEntity.FieldAuditIdea);
sqlQuery += " OR " + BaseWorkFlowCurrentEntity.FieldAuditStatusName + " LIKE " + DbHelper.GetParameter(BaseWorkFlowCurrentEntity.FieldAuditStatusName);
sqlQuery += " OR " + BaseWorkFlowCurrentEntity.FieldToDepartmentName + " LIKE " + DbHelper.GetParameter(BaseWorkFlowCurrentEntity.FieldToDepartmentName);
sqlQuery += " OR " + BaseWorkFlowCurrentEntity.FieldToUserRealName + " LIKE " + DbHelper.GetParameter(BaseWorkFlowCurrentEntity.FieldToUserRealName) + ")";
if (searchValue.IndexOf("%") < 0)
{
searchValue = "%" + searchValue + "%";
}
dbParameters.Add(DbHelper.MakeParameter(BaseWorkFlowCurrentEntity.FieldObjectFullName, searchValue));
dbParameters.Add(DbHelper.MakeParameter(BaseWorkFlowCurrentEntity.FieldAuditUserRealName, searchValue));
dbParameters.Add(DbHelper.MakeParameter(BaseWorkFlowCurrentEntity.FieldAuditIdea, searchValue));
dbParameters.Add(DbHelper.MakeParameter(BaseWorkFlowCurrentEntity.FieldAuditStatusName, searchValue));
dbParameters.Add(DbHelper.MakeParameter(BaseWorkFlowCurrentEntity.FieldToDepartmentName, searchValue));
dbParameters.Add(DbHelper.MakeParameter(BaseWorkFlowCurrentEntity.FieldToUserRealName, searchValue));
}
// 排序字段
sqlQuery += " ORDER BY " + BaseWorkFlowCurrentEntity.FieldSendDate;
return(DbHelper.Fill(sqlQuery, dbParameters.ToArray()));
}
/// <summary>
/// 查询
/// </summary>
/// <param name="userInfo"></param>
/// <param name="userId"></param>
/// <param name="categoryCode"></param>
/// <param name="searchValue"></param>
/// <param name="enabled"></param>
/// <param name="deletionStateCode"></param>
/// <returns></returns>
public DataTable Search(BaseUserInfo userInfo, string userId, string categoryCode, string searchValue, bool? enabled, bool? deletionStateCode)
{
// 写入调试信息
#if (DEBUG)
int milliStart = BaseBusinessLogic.StartDebug(userInfo, MethodBase.GetCurrentMethod());
#endif
// 加强安全验证防止未授权匿名调用
#if (!DEBUG)
LogOnService.UserIsLogOn(userInfo);
#endif
DataTable dataTable = new DataTable(BaseWorkFlowBillTemplateEntity.TableName);
using (IDbHelper dbHelper = DbHelperFactory.GetHelper(BaseSystemInfo.WorkFlowDbType))
{
try
{
dbHelper.Open(WorkFlowDbConnection);
BaseWorkFlowBillTemplateManager templateManager = new BaseWorkFlowBillTemplateManager(dbHelper, userInfo);
dataTable = templateManager.Search(userId, categoryCode, searchValue, enabled, deletionStateCode);
BaseLogManager.Instance.Add(dbHelper, userInfo, this.serviceName, MethodBase.GetCurrentMethod());
}
catch (Exception ex)
{
BaseExceptionManager.LogException(dbHelper, userInfo, ex);
throw ex;
}
finally
{
dbHelper.Close();
}
}
// 写入调试信息
#if (DEBUG)
BaseBusinessLogic.EndDebug(MethodBase.GetCurrentMethod(), milliStart);
#endif
return dataTable;
}
/// <summary>
/// 检索数据
/// </summary>
/// <param name="userId">用户Id</param>
/// <param name="categoryCode">单据类别</param>
/// <param name="searchValue">关键字</param>
/// <param name="enabled">是否有效</param>
/// <param name="deletionStateCode">是否删除</param>
/// <returns></returns>
public DataTable SearchBill(string userId, string categoryId, string categorybillFullName, string searchValue, bool?enabled, bool?deletionStateCode)
{
int delete = 0;
if (deletionStateCode != null)
{
delete = (bool)deletionStateCode ? 1 : 0;
}
string sqlQuery = string.Empty;
sqlQuery = " SELECT " + BaseWorkFlowBillTemplateEntity.FieldId
+ " ," + BaseWorkFlowBillTemplateEntity.FieldCategoryCode
+ " ," + BaseWorkFlowBillTemplateEntity.FieldTitle
+ " ," + BaseWorkFlowBillTemplateEntity.FieldCode
+ " ," + BaseWorkFlowBillTemplateEntity.FieldIntroduction
+ " ," + BaseWorkFlowBillTemplateEntity.FieldAuditStatus
+ " ," + BaseWorkFlowBillTemplateEntity.FieldModifiedUserId
+ " ," + BaseWorkFlowBillTemplateEntity.FieldModifiedBy
+ " ," + BaseWorkFlowBillTemplateEntity.FieldModifiedOn
+ " ," + BaseWorkFlowBillTemplateEntity.FieldCreateOn
+ " ," + BaseWorkFlowBillTemplateEntity.FieldSortCode
+ " FROM " + this.CurrentTableName
+ " WHERE " + BaseWorkFlowBillTemplateEntity.FieldDeletionStateCode + " = " + delete;
if (enabled != null)
{
int isEnabled = (bool)enabled ? 1 : 0;
sqlQuery += " AND " + BaseWorkFlowBillTemplateEntity.FieldEnabled + " = " + isEnabled;
}
if (!String.IsNullOrEmpty(userId))
{
sqlQuery += " AND " + BaseWorkFlowBillTemplateEntity.FieldCreateUserId + " = " + userId;
}
if (!string.IsNullOrEmpty(categoryId))
{
BaseWorkFlowBillTemplateManager templateManager = new BaseWorkFlowBillTemplateManager(this.DbHelper, this.UserInfo);
DataTable dataTable = templateManager.Search(string.Empty, categoryId, string.Empty, null, false);
string categoryIds = BaseBusinessLogic.FieldToList(dataTable, BaseWorkFlowBillTemplateEntity.FieldId);
if (!string.IsNullOrEmpty(categoryIds))
{
sqlQuery += " AND (" + BaseWorkFlowBillTemplateEntity.FieldCategoryCode + " IN (" + categoryIds + ")) ";
}
}
if (!String.IsNullOrEmpty(categorybillFullName))
{
sqlQuery += " AND " + BaseWorkFlowBillTemplateEntity.FieldCategoryCode + " = '" + categorybillFullName + "'";
}
List <IDbDataParameter> dbParameters = new List <IDbDataParameter>();
searchValue = searchValue.Trim();
if (!String.IsNullOrEmpty(searchValue))
{
sqlQuery += " AND (" + BaseWorkFlowBillTemplateEntity.FieldTitle + " LIKE " + DbHelper.GetParameter(BaseWorkFlowBillTemplateEntity.FieldTitle);
sqlQuery += " OR " + BaseWorkFlowBillTemplateEntity.FieldCode + " LIKE " + DbHelper.GetParameter(BaseWorkFlowBillTemplateEntity.FieldCode);
sqlQuery += " OR " + BaseWorkFlowBillTemplateEntity.FieldCategoryCode + " LIKE " + DbHelper.GetParameter(BaseWorkFlowBillTemplateEntity.FieldCategoryCode);
sqlQuery += " OR " + BaseWorkFlowBillTemplateEntity.FieldModifiedBy + " LIKE " + DbHelper.GetParameter(BaseWorkFlowBillTemplateEntity.FieldModifiedBy);
sqlQuery += " OR " + BaseWorkFlowBillTemplateEntity.FieldIntroduction + " LIKE " + DbHelper.GetParameter(BaseWorkFlowBillTemplateEntity.FieldIntroduction) + ")";
if (searchValue.IndexOf("%") < 0)
{
searchValue = "%" + searchValue + "%";
}
dbParameters.Add(DbHelper.MakeParameter(BaseWorkFlowBillTemplateEntity.FieldTitle, searchValue));
dbParameters.Add(DbHelper.MakeParameter(BaseWorkFlowBillTemplateEntity.FieldCode, searchValue));
dbParameters.Add(DbHelper.MakeParameter(BaseWorkFlowBillTemplateEntity.FieldCategoryCode, searchValue));
dbParameters.Add(DbHelper.MakeParameter(BaseWorkFlowBillTemplateEntity.FieldModifiedBy, searchValue));
dbParameters.Add(DbHelper.MakeParameter(BaseWorkFlowBillTemplateEntity.FieldIntroduction, searchValue));
}
sqlQuery += " ORDER BY " + BaseWorkFlowBillTemplateEntity.FieldSortCode + " DESC ";
return(DbHelper.Fill(sqlQuery, dbParameters.ToArray()));
}
/// <summary>
/// 检索数据
/// </summary>
/// <param name="userId">用户Id</param>
/// <param name="categoryCode">单据类别</param>
/// <param name="searchValue">关键字</param>
/// <param name="enabled">是否有效</param>
/// <param name="deletionStateCode">是否删除</param>
/// <returns></returns>
public DataTable SearchBill(string userId, string categoryId, string categorybillFullName, string searchValue, bool? enabled, bool? deletionStateCode)
{
int delete = 0;
if (deletionStateCode != null)
{
delete = (bool)deletionStateCode ? 1 : 0;
}
string sqlQuery = string.Empty;
sqlQuery = " SELECT " + BaseWorkFlowBillTemplateEntity.FieldId
+ " ," + BaseWorkFlowBillTemplateEntity.FieldCategoryCode
+ " ," + BaseWorkFlowBillTemplateEntity.FieldTitle
+ " ," + BaseWorkFlowBillTemplateEntity.FieldCode
+ " ," + BaseWorkFlowBillTemplateEntity.FieldIntroduction
+ " ," + BaseWorkFlowBillTemplateEntity.FieldAuditStatus
+ " ," + BaseWorkFlowBillTemplateEntity.FieldModifiedUserId
+ " ," + BaseWorkFlowBillTemplateEntity.FieldModifiedBy
+ " ," + BaseWorkFlowBillTemplateEntity.FieldModifiedOn
+ " ," + BaseWorkFlowBillTemplateEntity.FieldCreateOn
+ " ," + BaseWorkFlowBillTemplateEntity.FieldSortCode
+ " FROM " + this.CurrentTableName
+ " WHERE " + BaseWorkFlowBillTemplateEntity.FieldDeletionStateCode + " = " + delete;
if (enabled != null)
{
int isEnabled = (bool)enabled ? 1 : 0;
sqlQuery += " AND " + BaseWorkFlowBillTemplateEntity.FieldEnabled + " = " + isEnabled;
}
if (!String.IsNullOrEmpty(userId))
{
sqlQuery += " AND " + BaseWorkFlowBillTemplateEntity.FieldCreateUserId + " = " + userId;
}
if (!string.IsNullOrEmpty(categoryId))
{
BaseWorkFlowBillTemplateManager templateManager = new BaseWorkFlowBillTemplateManager(this.DbHelper, this.UserInfo);
DataTable dataTable = templateManager.Search(string.Empty, categoryId, string.Empty, null, false);
string categoryIds = BaseBusinessLogic.FieldToList(dataTable, BaseWorkFlowBillTemplateEntity.FieldId);
if (!string.IsNullOrEmpty(categoryIds))
{
sqlQuery += " AND (" + BaseWorkFlowBillTemplateEntity.FieldCategoryCode + " IN (" + categoryIds + ")) ";
}
}
if (!String.IsNullOrEmpty(categorybillFullName))
{
sqlQuery += " AND " + BaseWorkFlowBillTemplateEntity.FieldCategoryCode + " = '" + categorybillFullName + "'";
}
List<IDbDataParameter> dbParameters = new List<IDbDataParameter>();
searchValue = searchValue.Trim();
if (!String.IsNullOrEmpty(searchValue))
{
sqlQuery += " AND (" + BaseWorkFlowBillTemplateEntity.FieldTitle + " LIKE " + DbHelper.GetParameter(BaseWorkFlowBillTemplateEntity.FieldTitle);
sqlQuery += " OR " + BaseWorkFlowBillTemplateEntity.FieldCode + " LIKE " + DbHelper.GetParameter(BaseWorkFlowBillTemplateEntity.FieldCode);
sqlQuery += " OR " + BaseWorkFlowBillTemplateEntity.FieldCategoryCode + " LIKE " + DbHelper.GetParameter(BaseWorkFlowBillTemplateEntity.FieldCategoryCode);
sqlQuery += " OR " + BaseWorkFlowBillTemplateEntity.FieldModifiedBy + " LIKE " + DbHelper.GetParameter(BaseWorkFlowBillTemplateEntity.FieldModifiedBy);
sqlQuery += " OR " + BaseWorkFlowBillTemplateEntity.FieldIntroduction + " LIKE " + DbHelper.GetParameter(BaseWorkFlowBillTemplateEntity.FieldIntroduction) + ")";
if (searchValue.IndexOf("%") < 0)
{
searchValue = "%" + searchValue + "%";
}
dbParameters.Add(DbHelper.MakeParameter(BaseWorkFlowBillTemplateEntity.FieldTitle, searchValue));
dbParameters.Add(DbHelper.MakeParameter(BaseWorkFlowBillTemplateEntity.FieldCode, searchValue));
dbParameters.Add(DbHelper.MakeParameter(BaseWorkFlowBillTemplateEntity.FieldCategoryCode, searchValue));
dbParameters.Add(DbHelper.MakeParameter(BaseWorkFlowBillTemplateEntity.FieldModifiedBy, searchValue));
dbParameters.Add(DbHelper.MakeParameter(BaseWorkFlowBillTemplateEntity.FieldIntroduction, searchValue));
}
sqlQuery += " ORDER BY " + BaseWorkFlowBillTemplateEntity.FieldSortCode + " DESC ";
return DbHelper.Fill(sqlQuery, dbParameters.ToArray());
}