/// <summary>
/// App获取合同列表信息
/// </summary>
/// <param name="filterText">搜索框填写内容</param>
/// <param name="curRowNum">当前页数第一个数据的位置</param>
/// <param name="pageSize">一页几条数据</param>
/// <returns>合同列表信息</returns>
public List <ContractInfo> GetContracts(string filterText, int curRowNum = 0, int pageSize = 0)
{
List <ContractInfo> infos = new List <ContractInfo>();
sqlStr = "SELECT c.*,s.Name supplierName " +
" FROM tblContract c " +
" LEFT JOIN tblSupplier s ON c.SupplierID=s.ID " +
" WHERE 1=1 ";
if (!string.IsNullOrEmpty(filterText))
{
sqlStr += " AND ( UPPER(c.Name) LIKE @FilterText OR UPPER(c.ID) LIKE @FilterText OR UPPER(c.ContractNum) LIKE @FilterText ) ";
}
sqlStr += " ORDER BY ID";
sqlStr = AppendLimitClause(sqlStr, curRowNum, pageSize);
using (SqlCommand command = ConnectionUtil.GetCommand(sqlStr))
{
if (!string.IsNullOrEmpty(filterText))
{
command.Parameters.Add("@FilterText", SqlDbType.NVarChar).Value = "%" + filterText.ToUpper() + "%";
}
using (DataTable dt = GetDataTable(command))
{
foreach (DataRow dr in dt.Rows)
{
infos.Add(new ContractInfo(dr));
}
}
}
return(infos);
}
/// <summary>
/// 新增日志
/// </summary>
/// <param name="info">日志信息</param>
/// <returns>日志信息</returns>
public void AddAuditDetail(int auditID, DataTable dt)
{
sqlStr = "INSERT INTO tblAuditDetail(AuditID,FieldName,OldValue,NewValue) " +
" VALUES(@AuditID,@FieldName,@OldValue,@NewValue);" +
" SELECT @@IDENTITY";
SqlParameter parameter = null;
using (SqlCommand command = ConnectionUtil.GetCommand(sqlStr))
{
command.Parameters.Add("@AuditID", SqlDbType.Int).Value = auditID;
parameter = command.Parameters.Add("@FieldName", SqlDbType.VarChar);
parameter.SourceColumn = "FieldName";
parameter.SourceVersion = DataRowVersion.Original;
parameter = command.Parameters.Add("@OldValue", SqlDbType.NVarChar);
parameter.SourceColumn = "OldValue";
parameter.SourceVersion = DataRowVersion.Original;
parameter = command.Parameters.Add("@NewValue", SqlDbType.NVarChar);
parameter.SourceColumn = "NewValue";
parameter.SourceVersion = DataRowVersion.Original;
using (SqlDataAdapter da = new SqlDataAdapter())
{
da.InsertCommand = command;
da.Update(dt);
}
}
}
/// <summary>
/// 根据科室获取设备信息
/// </summary>
/// <param name="id">部门ID</param>
/// <returns>设备信息</returns>
public List <EquipmentInfo> GetEquipmentsByDepartmentID(int id)
{
List <EquipmentInfo> infos = new List <EquipmentInfo>();
sqlStr = "SELECT e.*, s.Name AS ManufacturerName, c.ContractID, f.ID AS ConfigLicenceID, f.FileName AS ConfigLicenceName FROM tblEquipment AS e" +
" LEFT JOIN tblSupplier AS s ON e.ManufacturerID=s.ID " +
" LEFT JOIN v_ActiveContract AS c on c.EquipmentID = e.ID" +
" LEFT JOIN tblEquipmentFile AS f on f.EquipmentID = e.ID and f.FileType = @FileType" +
" WHERE DepartmentID = @DepartmentID ";
using (SqlCommand command = ConnectionUtil.GetCommand(sqlStr))
{
command.Parameters.Add("@FileType", SqlDbType.NVarChar).Value = EquipmentInfo.FileTypes.ConfigLicence;
command.Parameters.Add("@DepartmentID", SqlDbType.NVarChar).Value = id;
using (DataTable dt = GetDataTable(command))
{
foreach (DataRow dr in dt.Rows)
{
infos.Add(new EquipmentInfo(dr));
}
}
}
return(infos);
}
/// <summary>
/// Start up the connection to the Seaweedfs server
/// </summary>
public async Task Start()
{
if (_running)
{
System.Diagnostics.Debug.WriteLine("connect is already startup");
}
else
{
System.Diagnostics.Debug.WriteLine("start connect to the seaweedfs master server [" +
ConnectionUtil.ConvertUrlWithScheme(Host + ":" + Port) + "]");
if (_connection == null)
{
_connection = new Connection(
ConnectionUtil.ConvertUrlWithScheme(Host + ":" + Port),
ConnectionTimeout,
StatusExpiry,
MaxConnection,
EnableLookupVolumeCache,
LookupVolumeCacheExpiry);
}
await _connection.Start();
_running = true;
}
}
/// <summary>
/// 根据年份获取各个科室支出
/// </summary>
/// <param name="year">年份</param>
/// <returns>各个科室支出</returns>
public List <ServiceHisCountInfo> GetAccessoryExpenseByDepartment(int year)
{
List <ServiceHisCountInfo> result = new List <ServiceHisCountInfo>();
sqlStr = "SELECT e.DepartmentID AS ObjectID, SUM(ra.Qty * ra.Amount) AS Expenses " +
" FROM tblReportAccessory ra" +
" LEFT JOIN tblDispatchReport dr ON ra.DispatchReportID = dr.ID" +
" LEFT JOIN tblDispatch d ON dr.DispatchID = d.ID" +
" LEFT JOIN tblRequest r ON d.RequestID = r.ID " +
" LEFT JOIN jctRequestEqpt re ON r.ID = re.RequestID " +
" LEFT JOIN tblEquipment e ON re.EquipmentID = e.ID" +
" WHERE DATEPART(YEAR,d.StartDate) = @Year AND d.StatusID = @Approved " +
" GROUP BY e.DepartmentID ";
using (SqlCommand command = ConnectionUtil.GetCommand(sqlStr))
{
command.Parameters.Add("@Year", SqlDbType.Int).Value = year;
command.Parameters.Add("@Approved", SqlDbType.Int).Value = DispatchInfo.Statuses.Approved;
using (DataTable dt = GetDataTable(command))
{
foreach (DataRow dr in dt.Rows)
{
result.Add(new ServiceHisCountInfo(dr));
}
return(result);
}
}
}
/// <summary>
/// 保存服务凭证信息
/// </summary>
/// <param name="dispatchJournal">服务凭证信息</param>
/// <returns>服务凭证ID</returns>
public int AddDispatchJournal(DispatchJournalInfo dispatchJournal)
{
sqlStr = "INSERT INTO tblDispatchJournal(DispatchID,FaultCode,JobContent,FujiComments,ResultStatusID, " +
" FollowProblem,Advice,UserName,UserMobile,Signed,StatusID) " +
" VALUES(@DispatchID,@FaultCode,@JobContent,@FujiComments,@ResultStatusID, " +
" @FollowProblem,@Advice,@UserName,@UserMobile,@Signed,@StatusID); " +
" SELECT @@IDENTITY";
using (SqlCommand command = ConnectionUtil.GetCommand(sqlStr))
{
command.Parameters.Add("@DispatchID", SqlDbType.Int).Value = dispatchJournal.Dispatch.ID;
command.Parameters.Add("@FaultCode", SqlDbType.NVarChar).Value = SQLUtil.TrimNull(dispatchJournal.FaultCode);
command.Parameters.Add("@JobContent", SqlDbType.NVarChar).Value = SQLUtil.TrimNull(dispatchJournal.JobContent);
command.Parameters.Add("@ResultStatusID", SqlDbType.Int).Value = dispatchJournal.ResultStatus.ID;
command.Parameters.Add("@FollowProblem", SqlDbType.NVarChar).Value = SQLUtil.TrimNull(dispatchJournal.FollowProblem);
command.Parameters.Add("@Advice", SqlDbType.NVarChar).Value = SQLUtil.TrimNull(dispatchJournal.Advice);
command.Parameters.Add("@UserName", SqlDbType.NVarChar).Value = SQLUtil.EmptyStringToNull(dispatchJournal.UserName);
command.Parameters.Add("@UserMobile", SqlDbType.VarChar).Value = SQLUtil.EmptyStringToNull(dispatchJournal.UserMobile);
command.Parameters.Add("@Signed", SqlDbType.Bit).Value = dispatchJournal.Signed;
command.Parameters.Add("@StatusID", SqlDbType.Int).Value = dispatchJournal.Status.ID;
command.Parameters.Add("@FujiComments", SqlDbType.NVarChar).Value = SQLUtil.TrimNull(dispatchJournal.FujiComments);
dispatchJournal.ID = SQLUtil.ConvertInt(command.ExecuteScalar());
}
return(dispatchJournal.ID);
}
void OnDeserialized(StreamingContext context)
{
Url = ConnectionUtil.ConvertUrlWithScheme(Url);
PublicUrl = string.IsNullOrEmpty(PublicUrl)
? Url
: ConnectionUtil.ConvertUrlWithScheme(PublicUrl);
}
/// <summary>
/// 根据用户角色ID获取用户信息
/// </summary>
/// <param name="roleIds">用户角色ID</param>
/// <returns>用户信息</returns>
public List <UserInfo> GetActiveUsers(List <int> roleIds)
{
List <UserInfo> infos = new List <UserInfo>();
sqlStr = "SELECT DISTINCT u.* FROM tblUser AS u ";
sqlStr += " WHERE u.IsActive = 1 ";
if (roleIds != null && roleIds.Count > 0)
{
sqlStr += " AND u.RoleID IN ( " + SQLUtil.ConvertToInStr(roleIds) + " )";
}
sqlStr += " ORDER BY u.Name, u.ID ";
using (SqlCommand command = ConnectionUtil.GetCommand(sqlStr))
{
using (DataTable dt = GetDataTable(command))
{
foreach (DataRow dr in dt.Rows)
{
infos.Add(new UserInfo(dr));
}
}
}
return(infos);
}
/// <summary>
/// 新增用户
/// </summary>
/// <param name="info">用户信息</param>
/// <returns>用户信息</returns>
public UserInfo AddUser(UserInfo info)
{
sqlStr = "INSERT INTO tblUser(LoginID,LoginPwd,Name,RoleID,Address, " +
" Mobile,Email,IsActive,LastLoginDate,CreatedDate,VerifyStatus,Department ) " +
" VALUES(@LoginID,@LoginPwd,@Name,@RoleID,@Address, " +
" @Mobile,@Email,@IsActive,null,GETDATE(),@VerifyStatus,@Department );" +
" SELECT @@IDENTITY";
using (SqlCommand command = ConnectionUtil.GetCommand(sqlStr))
{
command.Parameters.Add("@LoginID", SqlDbType.VarChar).Value = info.LoginID;
command.Parameters.Add("@LoginPwd", SqlDbType.VarChar).Value = info.LoginPwd;
command.Parameters.Add("@Name", SqlDbType.NVarChar).Value = info.Name;
command.Parameters.Add("@RoleID", SqlDbType.Int).Value = info.Role.ID;
command.Parameters.Add("@Address", SqlDbType.NVarChar).Value = SQLUtil.EmptyStringToNull(info.Address);
command.Parameters.Add("@Mobile", SqlDbType.VarChar).Value = SQLUtil.EmptyStringToNull(info.Mobile);
command.Parameters.Add("@Email", SqlDbType.VarChar).Value = SQLUtil.EmptyStringToNull(info.Email);
command.Parameters.Add("@IsActive", SqlDbType.Bit).Value = info.IsActive;
command.Parameters.Add("@VerifyStatus", SqlDbType.Int).Value = info.VerifyStatus.ID;
command.Parameters.Add("@Department", SqlDbType.Int).Value = info.Department.ID;
info.ID = SQLUtil.ConvertInt(command.ExecuteScalar());
return(info);
}
}
/// <summary>
/// 获取科室列表信息
/// </summary>
/// <param name="departmentTypeID">科室分类编号</param>
/// <param name="filterField">搜索字段</param>
/// <param name="filterText">搜索内容</param>
/// <returns>科室列表信息</returns>
public int QueryDepartmentCount(int departmentTypeID, string filterField, string filterText)
{
List <DepartmentInfo> departments = new List <DepartmentInfo>();
sqlStr = "SELECT Count(d.ID) FROM lkpDepartment d " +
" WHERE 1=1 ";
if (departmentTypeID > 0)
{
sqlStr += " AND d.TypeID = " + departmentTypeID;
}
if (!string.IsNullOrEmpty(filterText))
{
sqlStr += string.Format(" AND UPPER({0}) LIKE @FilterText OR UPPER(d.Pinyin) LIKE @FilterText ", filterField);
}
using (SqlCommand command = ConnectionUtil.GetCommand(sqlStr))
{
if (!String.IsNullOrEmpty(filterText))
{
command.Parameters.Add("@FilterText", SqlDbType.NVarChar).Value = "%" + filterText.ToUpper() + "%";
}
return(GetCount(command));
}
}
/// <summary>
/// 获取广播列表
/// </summary>
/// <param name="filterField">搜索条件</param>
/// <param name="filterText">搜索框填写内容</param>
/// <param name="curRowNum">当前页数第一个数据的位置</param>
/// <param name="pageSize">一页几条数据</param>
/// <returns>广播列表</returns>
public List <NoticeInfo> QueryNotices(string filterField, string filterText, int curRowNum, int pageSize)
{
List <NoticeInfo> infos = new List <NoticeInfo>();
sqlStr = "SELECT DISTINCT * FROM tblNotice ";
sqlStr += " WHERE 1=1 ";
if (!string.IsNullOrEmpty(filterText))
{
sqlStr += GetFieldFilterClause(filterField);
}
sqlStr += " ORDER BY IsLoop DESC , CreatedDate DESC, ID";
sqlStr = AppendLimitClause(sqlStr, curRowNum, pageSize);
using (SqlCommand command = ConnectionUtil.GetCommand(sqlStr))
{
if (!String.IsNullOrEmpty(filterText))
{
AddFieldFilterParam(command, filterField, filterText);
}
using (DataTable dt = GetDataTable(command))
{
foreach (DataRow dr in dt.Rows)
{
infos.Add(new NoticeInfo(dr));
}
}
}
return(infos);
}
/// <summary>
/// 获取科室
/// </summary>
/// <param name="inputText">搜索内容</param>
/// <param name="curRowNum">当前页数第一个数据的位置</param>
/// <param name="pageSize">一页几条数据</param>
/// <returns>科室信息</returns>
public List <DepartmentInfo> QueryDepartment4AutoCompleteVal(string inputText, int curRowNum = 0, int pageSize = 0)
{
List <DepartmentInfo> infos = new List <DepartmentInfo>();
sqlStr = "SELECT DISTINCT(ve.Department) as Description, d.* FROM lkpDepartment AS d " +
" RIGHT JOIN tblValEquipment AS ve ON ve.Department = d.Description" +
" WHERE 1 = 1 ";
if (!string.IsNullOrEmpty(inputText))
{
sqlStr += " AND (UPPER(ve.Department) LIKE @InputText OR UPPER(d.ID) LIKE @InputText OR UPPER(d.Pinyin) LIKE @InputText)";
}
sqlStr += " ORDER BY d.Seq ";
sqlStr = AppendLimitClause(sqlStr, curRowNum, pageSize);
using (SqlCommand command = ConnectionUtil.GetCommand(sqlStr))
{
if (!string.IsNullOrEmpty(inputText))
{
command.Parameters.Add("@InputText", SqlDbType.NVarChar).Value = "%" + inputText.ToUpper() + "%";
}
using (DataTable dt = GetDataTable(command))
{
foreach (DataRow dr in dt.Rows)
{
infos.Add(new DepartmentInfo(dr));
}
}
}
return(infos);
}
/// <summary>
/// 获取供应商数量
/// </summary>
/// <param name="typeID">供应商类型</param>
/// <param name="status">供应商状态</param>
/// <param name="filterField">搜索字段</param>
/// <param name="filterText">搜索框填写内容</param>
/// <returns>供应商数量</returns>
public int QuerySupplierCount(int typeID, int status, string filterField, string filterText)
{
sqlStr = "SELECT COUNT(DISTINCT s.ID) FROM tblSupplier s ";
sqlStr += " WHERE 1=1 ";
if (typeID != 0)
{
sqlStr += "AND s.TypeID = " + typeID;
}
if (status > -1)
{
sqlStr += " AND s.IsActive = " + status;
}
if (!string.IsNullOrEmpty(filterText))
{
sqlStr += GetFieldFilterClause(filterField);
}
using (SqlCommand command = ConnectionUtil.GetCommand(sqlStr))
{
if (!String.IsNullOrEmpty(filterText))
{
AddFieldFilterParam(command, filterField, filterText);
}
return(GetCount(command));
}
}
/// <summary>
/// 获取供应商
/// </summary>
/// <param name="inputText">搜索内容</param>
/// <param name="curRowNum">当前页数第一个数据的位置</param>
/// <param name="pageSize">一页几条数据</param>
/// <returns>供应商信息</returns>
public List <SupplierInfo> QuerySuppliers4AutoComplete(string inputText, int curRowNum = 0, int pageSize = 0)
{
List <SupplierInfo> infos = new List <SupplierInfo>();
sqlStr = "SELECT s.* FROM tblSupplier AS s WHERE s.IsActive = 1 ";
if (!string.IsNullOrEmpty(inputText))
{
sqlStr += " AND (UPPER(s.Name) LIKE @InputText OR UPPER(s.ID) LIKE @InputText)";
}
sqlStr += " ORDER BY s.ID ";
sqlStr = AppendLimitClause(sqlStr, curRowNum, pageSize);
using (SqlCommand command = ConnectionUtil.GetCommand(sqlStr))
{
if (!string.IsNullOrEmpty(inputText))
{
command.Parameters.Add("@InputText", SqlDbType.NVarChar).Value = "%" + inputText.ToUpper() + "%";
}
using (DataTable dt = GetDataTable(command))
{
foreach (DataRow dr in dt.Rows)
{
infos.Add(new SupplierInfo(dr));
}
}
}
return(infos);
}
/// <summary>
/// 获取下一个资产编号序号
/// </summary>
/// <param name="date">当前日期</param>
/// <returns>下一个资产编号序号</returns>
public string GetNextAssetCode(string date)
{
int seq = 1;
sqlStr = "Select Seq From tblEquipmentCtl WHERE Date = @Date";
using (SqlCommand command = ConnectionUtil.GetCommand(sqlStr))
{
command.Parameters.Add("@Date", SqlDbType.VarChar).Value = date;
Object objSeq = command.ExecuteScalar();
if (objSeq == DBNull.Value || objSeq == null)
{
command.CommandText = "INSERT INTO tblEquipmentCtl (Date, Seq) VALUES (@Date, 1)";
}
else
{
seq = SQLUtil.ConvertInt(objSeq) + 1;
command.CommandText = "UPDATE tblEquipmentCtl SET Seq = Seq + 1 WHERE Date = @Date";
}
command.ExecuteNonQuery();
}
return("HRY" + date + seq.ToString("D3"));
}
/// <summary>
/// 更新用户信息
/// </summary>
/// <param name="info">用户信息</param>
public void UpdateUser(UserInfo info)
{
sqlStr = "UPDATE tblUser SET LoginID = @LoginID, Name = @Name, RoleID = @RoleID, " +
" Mobile = @Mobile,Email=@Email,Address = @Address, IsActive = @IsActive,LoginPwd = @LoginPwd,Department = @Department,VerifyStatus = @VerifyStatus,Comments = @Comments ";
sqlStr += "WHERE ID = @ID";
using (SqlCommand command = ConnectionUtil.GetCommand(sqlStr))
{
command.Parameters.Add("@LoginID", SqlDbType.VarChar).Value = SQLUtil.EmptyStringToNull(info.LoginID);
command.Parameters.Add("@LoginPwd", SqlDbType.VarChar).Value = SQLUtil.EmptyStringToNull(info.LoginPwd);
command.Parameters.Add("@Name", SqlDbType.NVarChar).Value = info.Name;
command.Parameters.Add("@RoleID", SqlDbType.Int).Value = info.Role.ID;
command.Parameters.Add("@Mobile", SqlDbType.VarChar).Value = SQLUtil.EmptyStringToNull(info.Mobile);
command.Parameters.Add("@Address", SqlDbType.NVarChar).Value = SQLUtil.EmptyStringToNull(info.Address);
command.Parameters.Add("@Email", SqlDbType.VarChar).Value = SQLUtil.EmptyStringToNull(info.Email);
command.Parameters.Add("@IsActive", SqlDbType.Bit).Value = info.IsActive;
command.Parameters.Add("@Department", SqlDbType.Int).Value = info.Department.ID;
command.Parameters.Add("@VerifyStatus", SqlDbType.Int).Value = info.VerifyStatus.ID;
command.Parameters.Add("@Comments", SqlDbType.NVarChar).Value = SQLUtil.EmptyStringToNull(info.Comments);
command.Parameters.Add("@ID", SqlDbType.Int).Value = info.ID;
command.ExecuteNonQuery();
}
}
/// <summary>
/// 修改服务凭证信息
/// </summary>
/// <param name="dispatchJournal">服务凭证信息</param>
public void UpdateDispatchJournal(DispatchJournalInfo dispatchJournal)
{
sqlStr = "UPDATE tblDispatchJournal SET DispatchID = @DispatchID,FaultCode=@FaultCode," +
" FujiComments=@FujiComments,JobContent=@JobContent,ResultStatusID=@ResultStatusID,StatusID=@StatusID,FollowProblem=@FollowProblem," +
" Advice=@Advice,UserName=@UserName,UserMobile=@UserMobile, Signed=@Signed";
sqlStr += " WHERE ID = @ID";
using (SqlCommand command = ConnectionUtil.GetCommand(sqlStr))
{
command.Parameters.Add("@DispatchID", SqlDbType.VarChar).Value = dispatchJournal.Dispatch.ID;
command.Parameters.Add("@FaultCode", SqlDbType.NVarChar).Value = SQLUtil.TrimNull(dispatchJournal.FaultCode);
command.Parameters.Add("@JobContent", SqlDbType.NVarChar).Value = SQLUtil.TrimNull(dispatchJournal.JobContent);
command.Parameters.Add("@ResultStatusID", SqlDbType.Int).Value = dispatchJournal.ResultStatus.ID;
command.Parameters.Add("@StatusID", SqlDbType.Int).Value = dispatchJournal.Status.ID;
command.Parameters.Add("@FollowProblem", SqlDbType.NVarChar).Value = SQLUtil.TrimNull(dispatchJournal.FollowProblem);
command.Parameters.Add("@Advice", SqlDbType.NVarChar).Value = SQLUtil.TrimNull(dispatchJournal.Advice);
command.Parameters.Add("@UserName", SqlDbType.NVarChar).Value = SQLUtil.EmptyStringToNull(dispatchJournal.UserName);
command.Parameters.Add("@UserMobile", SqlDbType.VarChar).Value = SQLUtil.EmptyStringToNull(dispatchJournal.UserMobile);
command.Parameters.Add("@Signed", SqlDbType.Bit).Value = dispatchJournal.Signed;
command.Parameters.Add("@ID", SqlDbType.Int).Value = dispatchJournal.ID;
command.Parameters.Add("@FujiComments", SqlDbType.NVarChar).Value = SQLUtil.TrimNull(dispatchJournal.FujiComments);
command.ExecuteNonQuery();
}
}
/// <summary>
/// 获取用户数量
/// </summary>
/// <param name="status">用户状态</param>
/// <param name="roleId">角色编号</param>
/// <param name="verifyStatusID">审批状态</param>
/// <param name="filterField">搜索字段</param>
/// <param name="filterText">搜索框填写内容</param>
/// <returns>用户数量</returns>
public int QueryUsersCount(int status, int roleId, int verifyStatusID, string filterField, string filterText)
{
sqlStr = "SELECT COUNT(DISTINCT u.ID) FROM tblUser AS u ";
sqlStr += " WHERE 1=1 ";
if (status > -1)
{
sqlStr += " AND u.IsActive = " + status;
}
if (roleId != -1)
{
sqlStr += " AND u.RoleID = " + roleId;
}
if (verifyStatusID > 0)
{
sqlStr += " AND u.VerifyStatus = " + verifyStatusID;
}
if (!string.IsNullOrEmpty(filterText))
{
sqlStr += string.Format(" AND UPPER({0}) LIKE @FilterText", filterField);
}
using (SqlCommand command = ConnectionUtil.GetCommand(sqlStr))
{
command.Parameters.Add("@FilterText", SqlDbType.NVarChar).Value = "%" + filterText.ToUpper() + "%";
return(GetCount(command));
}
}
/// <summary>
/// 获取备用机库信息数量
/// </summary>
/// <param name="statusID">备用机状态</param>
/// <param name="filterField">搜索字段</param>
/// <param name="filterText">搜索内容</param>
/// <returns>备用机信息数量</returns>
public int QuerySpareCount(int statusID, string filterField, string filterText)
{
sqlStr = "SELECT COUNT(DISTINCT sp.ID) FROM tblInvSpare sp " +
" LEFT JOIN tblFujiClass2 AS f ON sp.FujiClass2ID = f.ID " +
" WHERE 1=1 ";
if (statusID != 0)
{
sqlStr += InvSpareInfo.SpareStatus.GetSqlFilter(statusID);
}
if (!string.IsNullOrEmpty(filterText))
{
sqlStr += GetFieldFilterClause(filterField);
}
using (SqlCommand command = ConnectionUtil.GetCommand(sqlStr))
{
if (!String.IsNullOrEmpty(filterText))
{
AddFieldFilterParam(command, filterField, filterText);
}
return(GetCount(command));
}
}
/// <summary>
/// 根据零件id获取零件信息
/// </summary>
/// <param name="componentID">零件id</param>
/// <returns>零件信息</returns>
public InvComponentInfo GetComponentByID(int componentID)
{
sqlStr = "SELECT ic.*,c.Name AS ComponentName,c.Description AS ComponentDescription,c.TypeID AS ComponentTypeID,e.Name AS EquipmentName,e.FujiClass2ID AS EquipmentFujiClass2ID,s.Name AS SupplierName FROM tblInvComponent ic " +
" LEFT JOIN tblEquipment AS e ON ic.EquipmentID = e.ID " +
" LEFT JOIN tblSupplier AS s ON ic.SupplierID = s.ID " +
" LEFT JOIN tblComponent AS c ON ic.ComponentID = c.ID " +
" WHERE 1=1 " +
" AND ic.ID = @ID";
using (SqlCommand command = ConnectionUtil.GetCommand(sqlStr))
{
command.Parameters.Add("@ID", SqlDbType.Int).Value = componentID;
using (DataTable dt = GetDataTable(command))
{
DataRow dr = GetDataRow(command);
if (dr != null)
{
return(new InvComponentInfo(dr));
}
else
{
return(null);
}
}
}
}
private async void Sign_up(object sender, RoutedEventArgs e)
{
if (ConnectionUtil.CheckConnection())
{
client = new FireSharp.FirebaseClient(main.config);
FirebaseResponse firebaseResponse = await client.GetAsync("Admin/" + signupName.Text);
Admin foundAdmin = firebaseResponse.ResultAs <Admin>();
if (foundAdmin == null)
{
Admin admin = new Admin
{
Email = signupEmail.Text.ToString(),
Password = Encrypt.GetShaData(signupPassword.Password.ToString()),
Name = signupName.Text.ToString()
};
SetResponse response = await client.SetAsync("Admin/" + signupName.Text, admin);
Admin adminn = response.ResultAs <Admin>();
MessageBox.Show(adminn.Name);
}
else
{
MessageBox.Show("Username taken");
}
}
else
{
MessageBox.Show("error");
}
}
/// <summary>
/// 添加零件
/// </summary>
/// <param name="info">零件信息</param>
/// <returns>零件id</returns>
public int AddComponent(InvComponentInfo info)
{
sqlStr = "INSERT INTO tblInvComponent(ComponentID,EquipmentID,SerialCode,Specification,Model,SupplierID,Price,PurchaseDate,PurchaseID,Comments,StatusID,AddDate) " +
" VALUES(@ComponentID,@EquipmentID,@SerialCode,@Specification,@Model,@SupplierID,@Price,@PurchaseDate,@PurchaseID,@Comments,@StatusID,GetDate()); " +
" SELECT @@IDENTITY ";
using (SqlCommand command = ConnectionUtil.GetCommand(sqlStr))
{
command.Parameters.Add("@ComponentID", SqlDbType.Int).Value = SQLUtil.ConvertInt(info.Component.ID);
command.Parameters.Add("@EquipmentID", SqlDbType.Int).Value = SQLUtil.ConvertInt(info.Equipment.ID);
command.Parameters.Add("@SerialCode", SqlDbType.NVarChar).Value = SQLUtil.TrimNull(info.SerialCode);
command.Parameters.Add("@Specification", SqlDbType.NVarChar).Value = SQLUtil.TrimNull(info.Specification);
command.Parameters.Add("@Model", SqlDbType.NVarChar).Value = SQLUtil.TrimNull(info.Model);
command.Parameters.Add("@SupplierID", SqlDbType.Int).Value = SQLUtil.ConvertInt(info.Supplier.ID);
command.Parameters.Add("@Price", SqlDbType.Decimal).Value = info.Price;
command.Parameters.Add("@PurchaseDate", SqlDbType.DateTime).Value = SQLUtil.ConvertDateTime(info.PurchaseDate);
command.Parameters.Add("@PurchaseID", SqlDbType.Int).Value = SQLUtil.ConvertInt(info.Purchase.ID);
command.Parameters.Add("@Comments", SqlDbType.NVarChar).Value = SQLUtil.TrimNull(info.Comments);
command.Parameters.Add("@StatusID", SqlDbType.Int).Value = SQLUtil.ConvertInt(info.Status.ID);
info.ID = SQLUtil.ConvertInt(command.ExecuteScalar());
return(info.ID);
}
}
/// <summary>
/// 根据类型获取设备收入
/// </summary>
/// <param name="id">设备id</param>
/// <param name="type">类型</param>
/// <param name="year">年份</param>
/// <returns>各个设备收入、服务人次</returns>
public Dictionary <string, double> GetServiceHisIncomesByEquipmentID(int id, int type, int year)
{
Dictionary <string, double> result = new Dictionary <string, double>();
sqlStr = string.Format("SELECT {0} AS ObjectID , SUM(s.Income) AS Incomes " +
" FROM tblServiceHis s " +
" WHERE s.EquipmentID = @ID ", (type == ReportDimension.AcceptanceMonth? " DATEPART(MONTH,s.TransDate) " : "DATEPART(YEAR,s.TransDate) "));
if (type == ReportDimension.AcceptanceMonth)
{
sqlStr += " AND DATEPART(YEAR,s.TransDate) = @Year GROUP BY DATEPART(MONTH,s.TransDate) ";
}
else
{
sqlStr += " GROUP BY DATEPART(YEAR,s.TransDate) ";
}
using (SqlCommand command = ConnectionUtil.GetCommand(sqlStr))
{
command.Parameters.Add("@ID", SqlDbType.Int).Value = id;
command.Parameters.Add("@Year", SqlDbType.Int).Value = year;
using (DataTable dt = GetDataTable(command))
{
foreach (DataRow dr in dt.Rows)
{
result.Add(SQLUtil.TrimNull(dr[0]), SQLUtil.ConvertDouble(dr[1]));
}
return(result);
}
}
}
/// <summary>
/// 修改零件信息
/// </summary>
/// <param name="info">零件信息</param>
/// <returns>零件id</returns>
public int UpdateComponent(InvComponentInfo info)
{
sqlStr = "UPDATE tblInvComponent Set ComponentID=@ComponentID,EquipmentID=@EquipmentID,SerialCode=@SerialCode,Specification=@Specification, " +
" Model=@Model,SupplierID=@SupplierID,Price=@Price,PurchaseDate=@PurchaseDate," +
" PurchaseID=@PurchaseID,Comments=@Comments,StatusID=@StatusID,UpdateDate=GetDate() " +
" WHERE ID = @ID";
using (SqlCommand command = ConnectionUtil.GetCommand(sqlStr))
{
command.Parameters.Add("@ID", SqlDbType.Int).Value = info.ID;
command.Parameters.Add("@ComponentID", SqlDbType.Int).Value = SQLUtil.ConvertInt(info.Component.ID);
command.Parameters.Add("@EquipmentID", SqlDbType.Int).Value = SQLUtil.ConvertInt(info.Equipment.ID);
command.Parameters.Add("@SerialCode", SqlDbType.NVarChar).Value = SQLUtil.TrimNull(info.SerialCode);
command.Parameters.Add("@Specification", SqlDbType.NVarChar).Value = SQLUtil.TrimNull(info.Specification);
command.Parameters.Add("@Model", SqlDbType.NVarChar).Value = SQLUtil.TrimNull(info.Model);
command.Parameters.Add("@SupplierID", SqlDbType.Int).Value = SQLUtil.ConvertInt(info.Supplier.ID);
command.Parameters.Add("@Price", SqlDbType.Decimal).Value = info.Price;
command.Parameters.Add("@PurchaseDate", SqlDbType.DateTime).Value = SQLUtil.ConvertDateTime(info.PurchaseDate);
command.Parameters.Add("@PurchaseID", SqlDbType.Int).Value = SQLUtil.ConvertInt(info.Purchase.ID);
command.Parameters.Add("@Comments", SqlDbType.NVarChar).Value = SQLUtil.TrimNull(info.Comments);
command.Parameters.Add("@StatusID", SqlDbType.Int).Value = SQLUtil.ConvertInt(info.Status.ID);
command.ExecuteNonQuery();
return(info.ID);
}
}
public static Chapter08 CreateDataContext()
{
ConnectionUtil connectionUtil = new ConnectionUtil();
ConnectionStringSettings connectionStringSettings = WebConfigurationManager.ConnectionStrings["Chapter08ConnectionString"];
return(new Chapter08(connectionUtil.CreateConnection(connectionStringSettings)));
}
/// <summary>
/// 根据设备编号获取生命周期信息
/// </summary>
/// <param name="equipmentID">设备ID</param>
/// <returns>生命周期信息</returns>
public List <DispatchInfo> GetTimeLine(int equipmentID)
{
List <DispatchInfo> dispatchInfo = new List <DispatchInfo>();
sqlStr = "SELECT dr.SolutionWay, dr.Comments, d.* FROM tblRequest AS r " +
" LEFT JOIN jctRequestEqpt AS jc ON jc.RequestID = r.ID " +
" LEFT JOIN tblDispatch AS d ON d.RequestID = r.ID " +
" LEFT JOIN tblDispatchReport AS dr ON dr.DispatchID = d.ID " +
" WHERE jc.EquipmentID = @EquipmentID AND d.EndDate IS NOT NULL " +
" ORDER BY d.EndDate DESC, d.ID";
using (SqlCommand command = ConnectionUtil.GetCommand(sqlStr))
{
command.Parameters.Add("@EquipmentID", SqlDbType.Int).Value = equipmentID;
using (DataTable dt = GetDataTable(command))
{
foreach (DataRow dr in dt.Rows)
{
DispatchInfo info = new DispatchInfo();
info.ID = SQLUtil.ConvertInt(dr["ID"]);
info.Request.ID = SQLUtil.ConvertInt(dr["RequestID"]);
info.RequestType.ID = SQLUtil.ConvertInt(dr["RequestType"]);
info.RequestType.Name = LookupManager.GetRequestTypeDesc(info.RequestType.ID);
info.ScheduleDate = SQLUtil.ConvertDateTime(dr["ScheduleDate"]);
info.EndDate = SQLUtil.ConvertDateTime(dr["EndDate"]);
info.DispatchReport.SolutionWay = SQLUtil.TrimNull(dr["SolutionWay"]);
info.DispatchReport.Comments = SQLUtil.TrimNull(dr["Comments"]);
dispatchInfo.Add(info);
}
}
}
return(dispatchInfo);
}
/// <summary>
/// 获取设备信息
/// </summary>
/// <param name="filterText">搜索框填写内容</param>
/// <param name="curRowNum">当前页数第一个数据的位置</param>
/// <param name="pageSize">一页几条数据</param>
/// <returns>设备信息</returns>
public List <EquipmentInfo> GetEquipments(string filterText, int curRowNum = 0, int pageSize = 0)
{
List <EquipmentInfo> infos = new List <EquipmentInfo>();
sqlStr = "SELECT e.*, s.Name AS ManufacturerName, c.ContractID, ct.ScopeID, ct.ScopeComments FROM tblEquipment AS e" +
" LEFT JOIN tblSupplier AS s ON e.ManufacturerID=s.ID " +
" LEFT JOIN v_ActiveContract AS c on c.EquipmentID = e.ID" +
" LEFT JOIN tblContract AS ct on ct.ID = c.ContractID" +
" WHERE 1=1 AND EquipmentStatusID <> @EquipmentStatusID ";
if (!string.IsNullOrEmpty(filterText))
{
sqlStr += " AND ( UPPER(e.Name) LIKE @FilterText OR UPPER(e.EquipmentCode) LIKE @FilterText OR UPPER(e.SerialCode) LIKE @FilterText ) ";
}
sqlStr += " ORDER BY ID Desc";
sqlStr = AppendLimitClause(sqlStr, curRowNum, pageSize);
using (SqlCommand command = ConnectionUtil.GetCommand(sqlStr))
{
command.Parameters.Add("@EquipmentStatusID", SqlDbType.NVarChar).Value = EquipmentInfo.EquipmentStatuses.Scrap;
if (!string.IsNullOrEmpty(filterText))
{
command.Parameters.Add("@FilterText", SqlDbType.NVarChar).Value = "%" + filterText.ToUpper() + "%";
}
using (DataTable dt = GetDataTable(command))
{
foreach (DataRow dr in dt.Rows)
{
infos.Add(new EquipmentInfo(dr));
}
}
}
return(infos);
}
public List <EqptComponentInfo> GetComponentByEpqtID(int eqptID)
{
string getcomponentSql = "(SELECT ic.SerialCode,c.Name,c.Description,mh.UsedDate,mh.DispatchReportID,dr.DispatchID " +
" FROM tblMaterialHistory mh " +
" LEFT JOIN tblInvComponent ic ON mh.ObjectID = ic.ID " +
" LEFT JOIN tblComponent c ON ic.ComponentID = c.ID " +
" LEFT JOIN tblDispatchReport dr ON mh.DispatchReportID = dr.ID " +
" WHERE mh.EquipmentID = @EqptID AND mh.ObjectType = {0}) ";
sqlStr = "SELECT ISNULL(mh1.SerialCode,mh2.SerialCode) as SerialCode,ISNULL(mh1.Name,mh2.Name) as Name, ISNULL(mh1.Description,mh2.Description) as Description,mh1.UsedDate as InstalDate,mh2.UsedDate as RemoveDate,mh1.DispatchReportID as NewDispatchReportID,mh1.DispatchID as NewDispatchID,mh2.DispatchReportID as OldDispatchReportID,mh2.DispatchID as OldDispatchID " +
" FROM {0} as mh1 " +
" FULL JOIN {1} as mh2 ON mh1.SerialCode = mh2.SerialCode " +
" ORDER BY InstalDate DESC,RemoveDate DESC";
sqlStr = string.Format(sqlStr, string.Format(getcomponentSql, ReportMaterialInfo.MaterialTypes.NewComponent), string.Format(getcomponentSql, ReportMaterialInfo.MaterialTypes.OldComponent));
using (SqlCommand command = ConnectionUtil.GetCommand(sqlStr))
{
command.Parameters.Add("@EqptID", SqlDbType.Int).Value = eqptID;
command.Parameters.Add("@NewComponent", SqlDbType.Int).Value = ReportMaterialInfo.MaterialTypes.NewComponent;
command.Parameters.Add("@OldComponent", SqlDbType.Int).Value = ReportMaterialInfo.MaterialTypes.OldComponent;
return(GetList <EqptComponentInfo>(command));
}
}
/// <summary>
/// 根据设备ID获取设备信息
/// </summary>
/// <param name="id">设备ID</param>
/// <returns>设备信息</returns>
public EquipmentInfo GetEquipmentByID(int id)
{
sqlStr = "SELECT e.*, s.Name AS SupplierName, su.Name AS ManufacturerName, c.ContractID,ct.*, f.ID AS ConfigLicenceID, f.FileName AS ConfigLicenceName FROM tblEquipment AS e " +
" LEFT JOIN tblSupplier AS s ON e.SupplierID=s.ID " +
" LEFT JOIN tblSupplier AS su ON e.ManufacturerID=su.ID " +
" LEFT JOIN v_ActiveContract AS c on c.EquipmentID = e.ID" +
" LEFT JOIN tblContract AS ct on ct.ID = c.ContractID" +
" LEFT JOIN tblEquipmentFile AS f on f.EquipmentID = e.ID and f.FileType = @FileType" +
" WHERE e.ID = @ID ";
using (SqlCommand command = ConnectionUtil.GetCommand(sqlStr))
{
command.Parameters.Add("@FileType", SqlDbType.NVarChar).Value = EquipmentInfo.FileTypes.ConfigLicence;
command.Parameters.Add("@ID", SqlDbType.Int).Value = id;
using (DataTable dt = GetDataTable(command))
{
DataRow dr = GetDataRow(command);
if (dr != null)
{
return(new EquipmentInfo(dr));
}
else
{
return(null);
}
}
}
}
/// <summary>
/// 获取合同耗材信息
/// </summary>
/// <param name="contractID">合同id</param>
/// <param name="equipmentID">设备id</param>
/// <returns>合同耗材信息</returns>
public List <ConsumableInfo> GetContractConsumables(int contractID, int equipmentID)
{
List <ConsumableInfo> infos = new List <ConsumableInfo>();
sqlStr = "SELECT c.*, f.Name FujiClass2Name FROM jctContractConsumable j " +
" INNER JOIN tblConsumable AS c ON c.ID = j.ConsumableID " +
" LEFT JOIN tblFujiClass2 f ON f.ID = c.FujiClass2ID " +
" WHERE j.ContractID = @ContractID " +
" AND j.EquipmentID = @EquipmentID";
using (SqlCommand command = ConnectionUtil.GetCommand(sqlStr))
{
command.Parameters.Add("@ContractID", SqlDbType.Int).Value = contractID;
command.Parameters.Add("@EquipmentID", SqlDbType.Int).Value = equipmentID;
using (DataTable dt = GetDataTable(command))
{
foreach (DataRow dr in dt.Rows)
{
infos.Add(new ConsumableInfo(dr));
}
}
}
return(infos);
}
