/// <summary>
/// Search Mode by Id
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public IEnumerable <CodeModel> SearchModelById(int id = 0)
{
List <CodeModel> list = new List <CodeModel>();
StringBuilder sql = new StringBuilder();
sql.AppendFormat(@" SELECT [Id]
,[BCCode]
,[BCCodeDesc]
,[BCCategory]
,[BCCodeOrder]
,[BCIsValid]
,[BCCreateUserNo]
,[BCCreateUserName]
,[BCCreateTime]
,[BCOperateUserNo]
,[BCOperateUserName]
,[BCOperateTime]
FROM {0} with(NOLOCK) ", tableName);
sql.Append(" WHERE 1=1 AND BCIsValid=1 ");
if (!id.Equals(0))
{
sql.AppendFormat("AND Id={0}", id);
}
var ds = ExecuteDataSet(CommandType.Text, sql.ToString());
if (ds != null && ds.Tables.Count > 0)
{
DataTable dt = new DataTable();
dt = ds.Tables[0];
list = DataConvertHelper.DataTableToList <CodeModel>(dt);
}
return(list.AsEnumerable());
}
/// <summary>
/// 描述：根据登录账号获取账号信息
/// 创建标识;cpf23568
/// 创建时间：2017-9-10 21:36:38
/// </summary>
/// <param name="account"></param>
/// <returns></returns>
public AccountModel GetAccountByAccount(string account)
{
var accountInfo = new AccountModel();
SqlParameter[] para =
{
new SqlParameter("@Account", account)
};
var sql = @"SELECT Id,BAAccount,BAPassword,BAUserId,BAType,BAIsValid FROM " + tableName
+ " WITH(NOLOCK) WHERE BAAccount=@Account AND BAIsValid=1";
var ds = ExecuteDataSet(CommandType.Text, sql.ToString(), null, para);
if (ds != null && ds.Tables.Count > 0)
{
DataTable dt = new DataTable();
dt = ds.Tables[0];
if (dt.Rows.Count > 0)
{
accountInfo = DataConvertHelper.DataTableToList <AccountModel>(dt)[0];
}
else
{
return(null);
}
}
else
{
return(null);
}
return(accountInfo);
}
public UserModel GetMaxJobNumber()
{
var model = new UserModel();
var user = new UserModel();
string sql = @"SELECT Top 1 Id,BUName,BUJobNumber,BUSex,BUAvatars,BUPhoneNum,BUEmail,BUDepartId,BUIsValid,BUTitle FROM " + userTableName
+ " WITH(NOLOCK) ORDER BY BUJobNumber DESC ";
var ds = ExecuteDataSet(CommandType.Text, sql);
if (ds != null && ds.Tables.Count > 0)
{
var dt = new DataTable();
dt = ds.Tables[0];
if (dt.Rows.Count > 0)
{
model = DataConvertHelper.DataTableToList <UserModel>(dt)[0];
}
else
{
return(null);
}
}
else
{
return(null);
}
return(model);
}
public List <CodeDefectModel> GetDefectCodeByType(string type)
{
List <CodeDefectModel> list = new List <CodeDefectModel>();
StringBuilder sql = new StringBuilder();
sql.AppendFormat(@" SELECT [Id]
,[BDCodeNameEn]
,[BDCodeNameCn]
,[BDCode]
,[BDCodeType]
,[BDCodeNo]
,[BDIsValid]
,[BDCreateUserNo]
,[BDCreateUserName]
,[BDCreateTime]
,[BDOperateUserNo]
,[BDOperateUserName]
,[BDOperateTime]
FROM {0} with(NOLOCK) ", tableName);
sql.Append(" WHERE 1=1 AND BDIsValid=1 ");
if (!string.IsNullOrEmpty(type))
{
sql.AppendFormat("AND BDCodeType= '{0}' ", type);
}
var ds = ExecuteDataSet(CommandType.Text, sql.ToString());
if (ds != null && ds.Tables.Count > 0)
{
DataTable dt = new DataTable();
dt = ds.Tables[0];
list = DataConvertHelper.DataTableToList <CodeDefectModel>(dt);
}
return(list);
}
public List <CodeModel> GetCodeList(string catrgory)
{
var list = new List <CodeModel>();
var selectSql = new StringBuilder();
selectSql.AppendFormat(@"SELECT [Id]
,[BCCode]
,[BCCodeDesc]
,[BCCategory]
,[BCCodeOrder]
,[BCIsValid]
,[BCCreateUserNo]
,[BCCreateUserName]
,[BCCreateTime]
,[BCOperateUserNo]
,[BCOperateUserName]
,[BCOperateTime]
FROM {0} with(NOLOCK) ", tableName);
selectSql.AppendFormat(" WHERE [BCIsValid] = 1 ");
selectSql.AppendFormat(" AND BCCategory= '{0}' ", catrgory);
selectSql.AppendFormat(" ORDER BY BCCodeOrder ASC ");
var ds = ExecuteDataSet(CommandType.Text, selectSql.ToString());
if (ds != null && ds.Tables.Count > 0)
{
DataTable dt = new DataTable();
dt = ds.Tables[0];
list = DataConvertHelper.DataTableToList <CodeModel>(dt);
}
return(list);
}
public List <UserGroupModel> GetUserGroupListByUserId(long userid)
{
var list = new List <UserGroupModel>();
var sql = string.Format(@"SELECT
R.BURUserId AS 'UserId' ,
R.BURGroupId AS 'GroupId' ,
G.BGCode AS 'GroupCode'
FROM {0} AS R WITH ( NOLOCK )
INNER JOIN {1} AS G WITH ( NOLOCK ) ON G.Id = R.BURGroupId
WHERE R.BURIsValid = 1 AND R.BURUserId = @BURUserId",
tableName,
groupTableName);
SqlParameter[] para =
{
new SqlParameter("@BURUserId", userid),
};
var ds = ExecuteDataSet(CommandType.Text, sql, null, para);
if (ds != null && ds.Tables.Count > 0)
{
DataTable dt = new DataTable();
dt = ds.Tables[0];
list = DataConvertHelper.DataTableToList <UserGroupModel>(dt);
}
return(list);
}
public List <MaToolModel> GetMainMachineList(string key)
{
var list = new List <MaToolModel>();
var selectSql = new StringBuilder();
selectSql.Append(string.Format(@"
SELECT TOP 10 [BMEquipmentNo],[BMFixtureNo],[BMClassification]
FROM {0} with(NOLOCK)
WHERE [BMIsValid] = 1 AND [BMClassification] = 1 ", tableName));
if (!string.IsNullOrEmpty(key))
{
selectSql.Append(string.Format(@" AND BMEquipmentNo like '%{0}%'", key));
}
selectSql.Append(" ORDER BY Id ASC");
var ds = ExecuteDataSet(CommandType.Text, selectSql.ToString());
if (ds != null && ds.Tables.Count > 0)
{
DataTable dt = new DataTable();
dt = ds.Tables[0];
list = DataConvertHelper.DataTableToList <MaToolModel>(dt);
}
return(list);
}
/// <summary>
/// 描述：获取所有的组织框架
/// 创建标识：cpf
/// 创建时间：2017-9-19 20:37:36
/// </summary>
/// <returns></returns>
public List <MaterialInfoModel> SearchMaterialPageList(MaterialSearchModel param, out int totalCount)
{
var list = new List <MaterialInfoModel>();
var selectSql = new StringBuilder();
var countSql = new StringBuilder();
var whereSql = new StringBuilder();
whereSql.Append(" WHERE 1 = 1 ");
if (string.IsNullOrEmpty(param.ProductName))
{
whereSql.Append(string.Format(" AND MIProductName like '%{0}'", param.ProductName));
}
if (string.IsNullOrEmpty(param.CustomerPart))
{
whereSql.Append(string.Format(" AND MICustomerPart like '%{0}'", param.CustomerPart));
}
if (string.IsNullOrEmpty(param.WorkOrder))
{
whereSql.Append(string.Format(" AND MIWorkOrder like '%{0}'", param.WorkOrder));
}
selectSql.Append(string.Format(@"
SELECT newTable.*
FROM (
SELECT TOP ( {0} * {1} )
ROW_NUMBER() OVER ( ORDER BY MIOperateTime DESC) RowNum
,[Id]
,[MICustomerPart]
,[MIProductName]
,[MICustomer]
,[MIPicture]
,[MIIsValid]
,[MICreateUserId]
,[MICreateUserName]
,[MICreateTime]
,[MIOperateUserId]
,[MIOperateUserName]
,[MIOperateTime]
,[MIWorkOrder]
,[MIMaterial]
,[MIMaterialText]
,[MITool]
,[MITotalQty]
FROM {2} with(NOLOCK) {3}
ORDER BY MIOperateTime DESC) newTable
WHERE newTable.RowNum > ( ( {0} - 1 ) * {1} )
", param.CurrentPage, param.PageSize, tableName, whereSql.ToString()));
countSql.Append(string.Format(@"SELECT COUNT(1) FROM {0} with(NOLOCK) {1} ", tableName, whereSql.ToString()));
var ds = SqlHelper.ExecuteDataSet(CommandType.Text, selectSql.ToString());
totalCount = SqlHelper.ExecuteCount(CommandType.Text, countSql.ToString());
if (ds != null && ds.Tables.Count > 0)
{
DataTable dt = new DataTable();
dt = ds.Tables[0];
list = DataConvertHelper.DataTableToList <MaterialInfoModel>(dt);
}
return(list);
}
/// <summary>
///
/// </summary>
/// <param name="materialId"></param>
/// <returns></returns>
public MaToolModel GetMaToolById(int materialId)
{
var data = new MaToolModel();
SqlParameter[] para =
{
new SqlParameter("@Id", materialId)
};
var sql = @"SELECT TOP 1 [Id]
,[BMEquipmentName]
,[BMClassification]
,[BMEquipmentNo]
,[BMFixtureNo]
,[BMType]
,[BMSerialNumber]
,[BMQuantity]
,[BMManufacturedDate]
,[BMPower]
,[BMOutlineDimension]
,[BMAbility]
,[BMNeedPressureAir]
,[BMNeedCoolingWater]
,[BMIncomingDate]
,[BMRemarks]
,[BMIsValid]
,[BMCreateUserNo]
,[BMCreateUserName]
,[BMCreateTime]
,[BMOperateUserNo]
,[BMOperateUserName]
,[BMOperateTime]
FROM " + tableName + " with(NOLOCK) WHERE Id=@Id";
var ds = ExecuteDataSet(CommandType.Text, sql.ToString(), null, para);
if (ds != null && ds.Tables.Count > 0)
{
DataTable dt = new DataTable();
dt = ds.Tables[0];
if (dt.Rows.Count > 0)
{
data = DataConvertHelper.DataTableToList <MaToolModel>(dt)[0];
}
else
{
return(null);
}
}
else
{
return(null);
}
return(data);
}
public IEnumerable <CodeDefectModel> SearchCodeDefectPageList(DefectCodeSearchModel param, out int totalCount)
{
var list = new List <CodeDefectModel>();
var selectSql = new StringBuilder();
var countSql = new StringBuilder();
var whereSql = new StringBuilder();
whereSql.Append(" WHERE 1 = 1 ");
if (!string.IsNullOrEmpty(param.code))
{
whereSql.Append(string.Format(" AND (BDCodeNameEn like '%{0}%' OR BDCodeNameCn like N'%{0}%')", param.code));
}
if (!string.IsNullOrEmpty(param.codetype))
{
whereSql.Append(string.Format(" AND BDCodeType like '%{0}%'", param.codetype));
}
selectSql.Append(string.Format(@"
SELECT newTable.*
FROM (
SELECT TOP ( {0} * {1} )
ROW_NUMBER() OVER ( ORDER BY BDCodeType , BDCodeNo desc) RowNum
,[Id]
,[BDCodeNameEn]
,[BDCodeNameCn]
,[BDCodeType]
,[BDCode]
,[BDCodeNo]
,[BDIsValid]
,[BDCreateUserNo]
,[BDCreateUserName]
,[BDCreateTime]
,[BDOperateUserNo]
,[BDOperateUserName]
,[BDOperateTime]
FROM {2} with(NOLOCK) {3}
ORDER BY BDCodeType , BDCodeNo desc) newTable
WHERE newTable.RowNum > ( ( {0} - 1 ) * {1} )
", param.CurrentPage, param.PageSize, tableName, whereSql.ToString()));
countSql.Append(string.Format(@"SELECT COUNT(1) FROM {0} with(NOLOCK) {1} ", tableName, whereSql.ToString()));
var ds = ExecuteDataSet(CommandType.Text, selectSql.ToString());
totalCount = ExecuteCount(CommandType.Text, countSql.ToString());
if (ds != null && ds.Tables.Count > 0)
{
DataTable dt = new DataTable();
dt = ds.Tables[0];
list = DataConvertHelper.DataTableToList <CodeDefectModel>(dt);
}
return(list);
}
public DepartmentModel GetRootDepartment()
{
var departmentInfo = new DepartmentModel();
var sql = @"SELECT Id,BDParentId,BDDeptName,BDDeptDesc,BDIsMin,BDIsValid,BDCreateUserNo,"
+ "BDCreateUserName,BDCreateTime,BDOperateUserNo,BDOperateUserName,BDOperateTime FROM " + tableName
+ " WITH(NOLOCK) WHERE BDParentId=0";
var ds = ExecuteDataSet(CommandType.Text, sql.ToString(), null);
if (ds != null && ds.Tables.Count > 0)
{
DataTable dt = new DataTable();
dt = ds.Tables[0];
if (dt.Rows.Count > 0)
{
departmentInfo = DataConvertHelper.DataTableToList <DepartmentModel>(dt)[0];
}
else
{
return(null);
}
}
else
{
return(null);
}
//using (var dr = ExecuteReader(CommandType.Text, sql,null))
//{
// if (dr.Read())
// {
// departmentInfo.Id = Convert.ToInt32(dr["Id"]);
// departmentInfo.BDParentId = Convert.ToInt32(dr["BDParentId"]);
// departmentInfo.BDDeptName = dr["BDDeptName"].ToString();
// departmentInfo.BDDeptDesc = dr["BDDeptDesc"].ToString();
// departmentInfo.BDIsMin = Convert.ToInt32(dr["BDIsMin"]);
// departmentInfo.BDIsValid = Convert.ToInt32(dr["BDIsValid"]);
// departmentInfo.BDCreateUserNo = dr["BDCreateUserNo"].ToString();
// departmentInfo.BDCreateUserName = dr["BDCreateUserName"].ToString();
// departmentInfo.BDCreateTime = DateTime.Parse(dr["BDCreateTime"].ToString());
// departmentInfo.BDOperateUserNo = dr["BDOperateUserNo"].ToString();
// departmentInfo.BDOperateUserName = dr["BDOperateUserName"].ToString();
// departmentInfo.BDOperateTime = DateTime.Parse(dr["BDOperateTime"].ToString());
// dr.Close();
// }
// else
// {
// departmentInfo = null;
// }
//}
return(departmentInfo);
}
/// <summary>
/// 描述：获取所有的组织框架
/// 创建标识：cpf
/// 创建时间：2017-9-19 20:37:36
/// </summary>
/// <returns></returns>
public List <DepartmentModel> GetAllDepartMent()
{
var list = new List <DepartmentModel>();
var sql = @"SELECT Id,BDParentId,BDDeptName,BDDeptDesc,BDIsMin,BDIsValid,BDCreateUserNo,
BDCreateUserName,BDCreateTime,BDOperateUserNo,BDOperateUserName,BDOperateTime FROM " + tableName + " WITH(NOLOCK)";
var ds = ExecuteDataSet(CommandType.Text, sql);
if (ds != null && ds.Tables.Count > 0)
{
DataTable dt = new DataTable();
dt = ds.Tables[0];
list = DataConvertHelper.DataTableToList <DepartmentModel>(dt);
}
return(list);
}
/// <summary>
/// 描述：获取所有的有效人员
/// </summary>
/// <returns></returns>
public List <UserModel> GetAllIsValidUser()
{
var list = new List <UserModel>();
string sql = @"SELECT Id,BUName,BUJobNumber,BUSex,BUAvatars,BUPhoneNum,BUEmail,BUDepartId,BUIsValid,
BUTitle,BUCreateUserNo,BUCreateUserName,BUCreateTime,BUOperateUserNo,BUOperateUserName,BUOperateTime FROM " + userTableName + " WITH(NOLOCK)";
var ds = ExecuteDataSet(CommandType.Text, sql);
if (ds != null && ds.Tables.Count > 0)
{
var dt = new DataTable();
dt = ds.Tables[0];
list = DataConvertHelper.DataTableToList <UserModel>(dt);
}
return(list);
}
/// <summary>
/// 查询
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public IEnumerable <SuggestionsInfoModel> SearchSuggestInfoList(SuggestionsSearchModel model)
{
List <SuggestionsInfoModel> list = new List <SuggestionsInfoModel>();
StringBuilder sql = new StringBuilder();
sql.AppendFormat(@" select
Id,
BFType ,
BFPhase ,
BFDesc ,
BFRespUserNo ,
BFRespName ,
BFStatus ,
BFPicture ,
BFFeedBackComment ,
BFCreateUserNo ,
BFCreateUserName ,
BFCreateTime ,
BFOperateUserNo ,
BFOperateUserName ,
BFOperateTime ,
BFIsValid
FROM {0} with(NOLOCK) ", tableName);
sql.Append(" WHERE 1=1 ");
sql.Append(" AND BFIsValid=1 ");
if (model.DateForm != null && model.DateForm > Convert.ToDateTime("0001-01-01 00:00:00"))
{
sql.AppendFormat("AND BFCreateTime >='{0}'", model.DateForm);
}
var aa = DateTime.Parse("0001-01-01 00:00:00");
if (model.DateTo != null && model.DateTo > Convert.ToDateTime("0001-01-01 00:00:00"))
{
sql.AppendFormat("And BFCreateTime<='{0}'", model.DateTo);
}
var ds = ExecuteDataSet(CommandType.Text, sql.ToString());
if (ds != null && ds.Tables.Count > 0)
{
DataTable dt = new DataTable();
dt = ds.Tables[0];
list = DataConvertHelper.DataTableToList <SuggestionsInfoModel>(dt);
}
return(list.AsEnumerable());
}
public UserModel GetUserAndAccountById(int id)
{
var user = new UserModel();
StringBuilder sql = new StringBuilder();
sql.AppendFormat(@" SELECT A.[Id]
,A.[BUName]
,A.[BUJobNumber]
,A.[BUSex]
,A.[BUAvatars]
,A.[BUPhoneNum]
,A.[BUEmail]
,A.[BUDepartId]
,A.[BUTitle]
,A.[BUCreateUserNo]
,A.[BUCreateUserName]
,A.[BUCreateTime]
,A.[BUOperateUserNo]
,A.[BUOperateUserName]
,A.[BUOperateTime]
,A.[BUIsValid]
,A.[BUDepartName]
,A.[BUEnglishName]
,A.[BUPosition]
,A.[BUExtensionPhone]
,A.[BUMobilePhone]
,B.BAAccount AS Account
FROM {0} A WiTH (NOLOCK)
LEFT JOIN {1} B WITH (NOLOCK)
ON A.Id=B.BAUserId AND B.BAIsValid=1 ", userTableName, accountTableName);
sql.AppendFormat(" WHERE A.Id =@Id AND A.BUIsValid=1 ");
SqlParameter[] para = { new SqlParameter("@Id", id) };
var ds = ExecuteDataSet(CommandType.Text, sql.ToString(), null, para);
if (ds != null && ds.Tables.Count > 0)
{
DataTable dt = new DataTable();
dt = ds.Tables[0];
user = DataConvertHelper.DataTableToList <UserModel>(dt)[0];
}
else
{
return(null);
}
return(user);
}
public AccountModel GetAccountByAccountAndPass(string account, string password)
{
var accountInfo = new AccountModel();
SqlParameter[] para =
{
new SqlParameter("@Account", account),
new SqlParameter("@Password", password)
};
var sql = @"SELECT Id,BAAccount,BAPassword,BAUserId,BAType,BAIsValid FROM " + tableName
+ " WITH(NOLOCK) WHERE BAAccount=@Account AND BAPassword=@Password";
var ds = ExecuteDataSet(CommandType.Text, sql.ToString(), null, para);
if (ds != null && ds.Tables.Count > 0)
{
DataTable dt = new DataTable();
dt = ds.Tables[0];
accountInfo = DataConvertHelper.DataTableToList <AccountModel>(dt)[0];
}
else
{
return(null);
}
//using (SqlDataReader dr = ExecuteReader(CommandType.Text, sql, null, para))
//{
// if (dr.Read())
// {
// accountInfo.Id = Convert.ToInt32(dr["Id"]);
// accountInfo.BAPassword = dr["BAPassword"].ToString();
// accountInfo.BAAccount = dr["BAAccount"].ToString();
// accountInfo.BAUserId = Convert.ToInt32(dr["BAUserId"]);
// accountInfo.BAType = Convert.ToInt32(dr["BAType"]);
// accountInfo.BAIsValid = Convert.ToInt32(dr["BAIsValid"]);
// dr.Close();
// }
// else
// {
// accountInfo = null;
// }
//}
return(accountInfo);
}
/// <summary>
/// Search Mode by Id
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public CodeDefectModel GetOneDefectCode(int id = 0)
{
var result = new CodeDefectModel();
SqlParameter[] para =
{
new SqlParameter("@Id", id)
};
var sql = @"SELECT TOP 1 [Id]
,[BDCodeNameEn]
,[BDCodeNameCn]
,[BDCode]
,[BDCodeNo]
,[BDCodeType]
,[BDIsValid]
,[BDCreateUserNo]
,[BDCreateUserName]
,[BDCreateTime]
,[BDOperateUserNo]
,[BDOperateUserName]
,[BDOperateTime]
FROM " + tableName + " with(NOLOCK) WHERE Id=@Id";
var ds = ExecuteDataSet(CommandType.Text, sql.ToString(), null, para);
if (ds != null && ds.Tables.Count > 0)
{
DataTable dt = new DataTable();
dt = ds.Tables[0];
if (dt.Rows.Count > 0)
{
result = DataConvertHelper.DataTableToList <CodeDefectModel>(dt)[0];
}
else
{
return(null);
}
}
else
{
return(null);
}
return(result);
}
public List <CodeDefectModel> GetDefectCodeTypeList()
{
List <CodeDefectModel> list = new List <CodeDefectModel>();
StringBuilder sql = new StringBuilder();
sql.AppendFormat(@" SELECT DISTINCT [BDCodeType]
FROM {0} with(NOLOCK) ", tableName);
sql.Append(" WHERE 1=1 AND BDIsValid=1 ");
var ds = ExecuteDataSet(CommandType.Text, sql.ToString());
if (ds != null && ds.Tables.Count > 0)
{
DataTable dt = new DataTable();
dt = ds.Tables[0];
list = DataConvertHelper.DataTableToList <CodeDefectModel>(dt);
}
return(list);
}
/// <summary>
/// 描述：模糊获取有效用户
/// </summary>
/// <param name="key"></param>
/// <returns></returns>
public List <UserModel> GetSelectUserInfo(string key)
{
var list = new List <UserModel>();
var user = new UserModel();
string sql = string.Format(@"SELECT TOP 5 Id,BUName,BUEnglishName,BUJobNumber FROM {0} WITH(NOLOCK)
WHERE (BUJobNumber like '%{1}%' OR BUName like N'%{1}%' OR BUEnglishName like '%{1}%') AND BUIsValid =1",
userTableName, key);
var ds = ExecuteDataSet(CommandType.Text, sql, null);
if (ds != null && ds.Tables.Count > 0)
{
var dt = new DataTable();
dt = ds.Tables[0];
list = DataConvertHelper.DataTableToList <UserModel>(dt);
}
return(list);
}
/// <summary>
/// 描述：根据部门Id获取部门信息
/// </summary>
/// <param name="departmentId">部门Id</param>
/// <returns></returns>
public DepartmentModel GetDpById(int departmentId)
{
var departmentInfo = new DepartmentModel();
SqlParameter[] para =
{
new SqlParameter("@Id", departmentId)
};
var sql = @"SELECT Id,BDParentId,BDDeptName,BDDeptDesc,BDIsMin,BDIsValid FROM " + tableName
+ " WITH(NOLOCK) WHERE Id=@Id";
var ds = ExecuteDataSet(CommandType.Text, sql.ToString(), null, para);
if (ds != null && ds.Tables.Count > 0)
{
DataTable dt = new DataTable();
dt = ds.Tables[0];
departmentInfo = DataConvertHelper.DataTableToList <DepartmentModel>(dt)[0];
}
else
{
return(null);
}
//using (var dr = ExecuteReader(CommandType.Text, sql,null, para))
//{
// if (dr.Read())
// {
// departmentInfo.Id = Convert.ToInt32(dr["Id"]);
// departmentInfo.BDParentId = Convert.ToInt32(dr["BDParentId"]);
// departmentInfo.BDDeptName = dr["BDDeptName"].ToString();
// departmentInfo.BDDeptDesc = dr["BDDeptDesc"].ToString();
// departmentInfo.BDIsMin = Convert.ToInt32(dr["BDIsMin"]);
// departmentInfo.BDIsValid = Convert.ToInt32(dr["BDIsValid"]);
// dr.Close();
// }
// else
// {
// departmentInfo = null;
// }
//}
return(departmentInfo);
}
public RoleModel GetRoleById(long roleId)
{
var roleModel = new RoleModel();
SqlParameter[] para =
{
new SqlParameter("@Id", roleId),
new SqlParameter("@BRIsValid", EnabledEnum.Enabled.GetHashCode())
};
var sql = "SELECT Id,BRCode,BRName,BRType,BRIsValid FROM " + tableName + " WITH(NOLOCK) WHERE Id=@Id AND BRIsValid=@BRIsValid";
var ds = ExecuteDataSet(CommandType.Text, sql.ToString(), null, para);
if (ds != null && ds.Tables.Count > 0)
{
DataTable dt = new DataTable();
dt = ds.Tables[0];
roleModel = DataConvertHelper.DataTableToList <RoleModel>(dt)[0];
}
else
{
return(null);
}
//using (var dr = ExecuteReader(CommandType.Text, sql, null, para))
//{
// if (dr.Read())
// {
// roleModel.Id = long.Parse(dr["Id"].ToString());
// roleModel.BRCode = dr["BRCode"].ToString();
// roleModel.BRName = dr["BRName"].ToString();
// roleModel.BRType = Convert.ToInt32(dr["BRType"]);
// roleModel.BRIsValid = Convert.ToInt32(dr["BRIsValid"]);
// dr.Close();
// }
// else
// {
// roleModel = null;
// }
//}
return(roleModel);
}
/// <summary>
/// 描述：根据id获取部门信息
/// </summary>
/// <param name="departmentId"></param>
/// <returns></returns>
public DepartmentInfo GetDepartById(int departmentId)
{
var departmentInfo = new DepartmentInfo();
SqlParameter[] para =
{
new SqlParameter("@Id", departmentId)
};
var sql = @"SELECT a.Id Id,a.BDParentId ParentId,a.BDDeptName Name,a.BDDeptDesc Description,b.BDDeptName ParentName FROM " + tableName
+ " a WITH(NOLOCK ) LEFT JOIN " + tableName + " b WITH(NOLOCK ) ON a.BDParentId = b.Id WHERE a.Id=@Id";
var ds = ExecuteDataSet(CommandType.Text, sql.ToString(), null, para);
if (ds != null && ds.Tables.Count > 0)
{
DataTable dt = new DataTable();
dt = ds.Tables[0];
departmentInfo = DataConvertHelper.DataTableToList <DepartmentInfo>(dt)[0];
}
else
{
return(null);
}
//using (var dr = ExecuteReader(CommandType.Text, sql, null,para))
//{
// if (dr.Read())
// {
// departmentInfo.Id = dr["Id"].ToString();
// departmentInfo.ParentId = dr["ParentId"].ToString();
// departmentInfo.Name = dr["Name"].ToString();
// departmentInfo.Description = dr["Description"].ToString();
// departmentInfo.ParentName = dr["ParentName"].ToString();
// dr.Close();
// }
// else
// {
// departmentInfo = null;
// }
//}
return(departmentInfo);
}
/// <summary>
/// Search Mode by Id
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public IEnumerable <ProblemActionContainmentModel> SearchModelById(int id = 0)
{
List <ProblemActionContainmentModel> list = new List <ProblemActionContainmentModel>();
StringBuilder sql = new StringBuilder();
sql.AppendFormat(@" SELECT [Id]
,[PACWhat]
,[PACCheck]
,[PACWhoNo]
,[PACWho]
,[PACPlanDate]
,[PACVarificationDate]
,[PACWhere]
,[PACAttachment]
,[PACAttachmentUrl]
,[PACEffeective]
,[PACComment]
,[PACIsValid]
,[PACCreateUserNo]
,[PACCreateUserName]
,[PACCreateTime]
,[PACOperateUserNo]
,[PACOperateUserName]
,[PACOperateTime]
,[PACProblemId]
FROM {0} with(NOLOCK) ", tableName);
sql.Append(" WHERE 1=1 ");
if (!id.Equals(0))
{
sql.AppendFormat("AND Id={0}", id);
}
var ds = ExecuteDataSet(CommandType.Text, sql.ToString());
if (ds != null && ds.Tables.Count > 0)
{
DataTable dt = new DataTable();
dt = ds.Tables[0];
list = DataConvertHelper.DataTableToList <ProblemActionContainmentModel>(dt);
}
return(list.AsEnumerable());
}
/// <summary>
/// 描述：获取物料信息
/// </summary>
/// <param name="materialId">物料Id</param>
/// <returns></returns>
public MaterialInfoModel GetMaterialById(int materialId)
{
var material = new MaterialInfoModel();
SqlParameter[] para =
{
new SqlParameter("@Id", materialId)
};
var sql = @"SELECT TOP 1 [Id]
,[MICustomerPart]
,[MIProductName]
,[MICustomer]
,[MIPicture]
,[MIIsValid]
,[MICreateUserId]
,[MICreateUserName]
,[MICreateTime]
,[MIOperateUserId]
,[MIOperateUserName]
,[MIOperateTime]
,[MIWorkOrder]
,[MIMaterial]
,[MIMaterialText]
,[MITool]
,[MITotalQty]
FROM " + tableName + " WHERE Id=@Id";
var ds = SqlHelper.ExecuteDataSet(CommandType.Text, sql.ToString(), null, para);
if (ds != null && ds.Tables.Count > 0)
{
DataTable dt = new DataTable();
dt = ds.Tables[0];
material = DataConvertHelper.DataTableToList <MaterialInfoModel>(dt)[0];
}
else
{
return(null);
}
return(material);
}
public UserModel GetUserById(int id)
{
var user = new UserModel();
string sql = @"SELECT Id
,[BUName]
,[BUJobNumber]
,[BUSex]
,[BUAvatars]
,[BUPhoneNum]
,[BUEmail]
,[BUDepartId]
,[BUTitle]
,[BUCreateUserNo]
,[BUCreateUserName]
,[BUCreateTime]
,[BUOperateUserNo]
,[BUOperateUserName]
,[BUOperateTime]
,[BUIsValid]
,[BUDepartName]
,[BUEnglishName]
,[BUPosition]
,[BUExtensionPhone]
,[BUMobilePhone] FROM " + userTableName
+ " WITH(NOLOCK) WHERE Id=@Id";
SqlParameter[] para = { new SqlParameter("@Id", id) };
var ds = ExecuteDataSet(CommandType.Text, sql.ToString(), null, para);
if (ds != null && ds.Tables.Count > 0)
{
DataTable dt = new DataTable();
dt = ds.Tables[0];
user = DataConvertHelper.DataTableToList <UserModel>(dt)[0];
}
else
{
return(null);
}
return(user);
}
public GroupModel GetGroupByGroupName(string groupName)
{
var roloGroupInfo = new GroupModel();
SqlParameter[] para =
{
new SqlParameter("@BGName", groupName),
new SqlParameter("@BGIsValid", EnabledEnum.Enabled.GetHashCode())
};
var sql = "SELECT Id,BGCode,BGName,BGIsValid FROM " + tableName + " WITH(NOLOCK) WHERE BGName=@BGName AND BGIsValid=@BGIsValid";
var ds = ExecuteDataSet(CommandType.Text, sql.ToString(), null, para);
if (ds != null && ds.Tables.Count > 0)
{
DataTable dt = new DataTable();
dt = ds.Tables[0];
roloGroupInfo = DataConvertHelper.DataTableToList <GroupModel>(dt)[0];
}
else
{
return(null);
}
//using (var dr = ExecuteReader(CommandType.Text, sql, null, para))
//{
// if (dr.Read())
// {
// roloGroupInfo.Id = long.Parse(dr["Id"].ToString());
// roloGroupInfo.BGCode = dr["BGCode"].ToString();
// roloGroupInfo.BGName = dr["BGName"].ToString();
// roloGroupInfo.BGIsValid = Convert.ToInt32(dr["BGIsValid"]);
// dr.Close();
// }
// else
// {
// roloGroupInfo = null;
// }
//}
return(roloGroupInfo);
}
/// <summary>
/// 描述：根据角色包id获取角色分配信息
/// </summary>
/// <param name="groupId"></param>
/// <returns></returns>
public List <RoleGroupModel> GetRoleGroupByGroupId(long groupId)
{
var list = new List <RoleGroupModel>();
SqlParameter[] para =
{
new SqlParameter("@BRGGroupId", groupId),
new SqlParameter("@BRGIsValid", EnabledEnum.Enabled.GetHashCode())
};
var sql = "SELECT Id,BRGRoleId,BRGGroupId,BRGIsValid FROM " + tableName + " WITH(NOLOCK) WHERE BRGGroupId=@BRGGroupId AND BRGIsValid=@BRGIsValid";
var ds = ExecuteDataSet(CommandType.Text, sql, null, para);
if (ds != null && ds.Tables.Count > 0)
{
DataTable dt = new DataTable();
dt = ds.Tables[0];
list = DataConvertHelper.DataTableToList <RoleGroupModel>(dt);
}
return(list);
}
public List <UserRoleRelationModel> GetUserRoleRelationByGroupId(long groupid)
{
var list = new List <UserRoleRelationModel>();
var sql = "SELECT Id,BURUserId,BURGroupId,BURIsValid,BURCreateUserId,BURCreateUserName,BURCreateTime," +
"BUROperateUserId,BUROperateUserName,BUROperateTime FROM " + tableName +
" WITH(NOLOCK) WHERE BURGroupId=@BURGroupId AND BURIsValid=1";
SqlParameter[] para =
{
new SqlParameter("@BURGroupId", groupid),
};
var ds = ExecuteDataSet(CommandType.Text, sql, null, para);
if (ds != null && ds.Tables.Count > 0)
{
DataTable dt = new DataTable();
dt = ds.Tables[0];
list = DataConvertHelper.DataTableToList <UserRoleRelationModel>(dt);
}
return(list);
}
public SuggestionsInfoModel GetSuggesById(long userIdlong)
{
List <SuggestionsInfoModel> list = new List <SuggestionsInfoModel>();
StringBuilder sql = new StringBuilder();
sql.AppendFormat(@" select
Id,
BFType ,
BFPhase ,
BFDesc ,
BFRespUserNo ,
BFRespName ,
BFStatus ,
BFPicture ,
BFFeedBackComment ,
BFCreateUserNo ,
BFCreateUserName ,
BFCreateTime ,
BFOperateUserNo ,
BFOperateUserName ,
BFOperateTime ,
BFIsValid
FROM {0} with(NOLOCK) ", tableName);
sql.Append(" WHERE 1=1 ");
sql.Append(" AND BFIsValid=1 ");
if (userIdlong != 0)
{
sql.AppendFormat("AND Id= {0} ", userIdlong);
}
var ds = ExecuteDataSet(CommandType.Text, sql.ToString());
if (ds != null && ds.Tables.Count > 0)
{
DataTable dt = new DataTable();
dt = ds.Tables[0];
list = DataConvertHelper.DataTableToList <SuggestionsInfoModel>(dt);
}
return(list.FirstOrDefault());
}
/// <summary>
/// 描述：获取质量报警信息
/// </summary>
/// <param name="materialId">物料Id</param>
/// <returns></returns>
public ProblemSolvingTeamModel GetSolvingTeamById(int id)
{
var material = new ProblemSolvingTeamModel();
SqlParameter[] para =
{
new SqlParameter("@Id", id)
};
var sql = @"SELECT TOP 1 [Id]
,[PSProblemId]
,[PSUserNo]
,[PSUserName]
,[PSDeskEXT]
,[PSDeptId]
,[PSDeptName]
,[PSUserTitle]
,[PSIsLeader]
,[PSIsValid]
,[PSCreateUserNo]
,[PSCreateUserName]
,[PSCreateTime]
,[PSOperateUserNo]
,[PSOperateUserName]
,[PSOperateTime]
FROM " + tableName + " with(NOLOCK) WHERE Id=@Id";
var ds = ExecuteDataSet(CommandType.Text, sql.ToString(), null, para);
if (ds != null && ds.Tables.Count > 0)
{
DataTable dt = new DataTable();
dt = ds.Tables[0];
material = DataConvertHelper.DataTableToList <ProblemSolvingTeamModel>(dt)[0];
}
else
{
return(null);
}
return(material);
}
