예제 #1
0
        /// <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);
        }
예제 #2
0
        /// <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);
                }
            }
        }
예제 #3
0
        /// <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);
        }
예제 #4
0
        /// <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;
            }
        }
예제 #5
0
        /// <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);
                }
            }
        }
예제 #6
0
        /// <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);
        }
예제 #7
0
파일: DataNode.cs 프로젝트: xgame92/H-Judge
 void OnDeserialized(StreamingContext context)
 {
     Url       = ConnectionUtil.ConvertUrlWithScheme(Url);
     PublicUrl = string.IsNullOrEmpty(PublicUrl)
         ? Url
         : ConnectionUtil.ConvertUrlWithScheme(PublicUrl);
 }
예제 #8
0
파일: UserDao.cs 프로젝트: 10101818/mems_p2
        /// <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);
        }
예제 #9
0
파일: UserDao.cs 프로젝트: 10101818/mems_p2
        /// <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);
            }
        }
예제 #10
0
        /// <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));
            }
        }
예제 #11
0
파일: NoticeDao.cs 프로젝트: 10101818/atoi2
        /// <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);
        }
예제 #12
0
        /// <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);
        }
예제 #13
0
        /// <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));
            }
        }
예제 #14
0
        /// <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);
        }
예제 #15
0
        /// <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"));
        }
예제 #16
0
파일: UserDao.cs 프로젝트: 10101818/mems_p2
        /// <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();
            }
        }
예제 #17
0
        /// <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();
            }
        }
예제 #18
0
파일: UserDao.cs 프로젝트: 10101818/mems_p2
        /// <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));
            }
        }
예제 #19
0
        /// <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));
            }
        }
예제 #20
0
        /// <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);
                    }
                }
            }
        }
예제 #21
0
        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");
            }
        }
예제 #22
0
        /// <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);
            }
        }
예제 #23
0
        /// <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);
                }
            }
        }
예제 #24
0
        /// <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)));
        }
예제 #26
0
        /// <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);
        }
예제 #27
0
        /// <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);
        }
예제 #28
0
        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));
            }
        }
예제 #29
0
 /// <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);
             }
         }
     }
 }
예제 #30
0
        /// <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);
        }