OA2021/DAL/ScoreService.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Models;
using System.Data.SqlClient;
using System.Data;

namespace DAL
{
    public class ScoreService
    {
        /// <summary>
        /// 添加分数
        /// </summary>
        /// <param name="s"></param>
        public bool AddScore(Scores s)
        {
            string sql = "INSERT INTO Score(Nid,NName,Score,Weak,Uid,InputTime,STid,STName)VALUES(@Nid,@NName,@Score,@Weak,@Uid,@InputTime,@STid,@STName)";

            SqlParameter[] parameter = new SqlParameter[] { 
                new SqlParameter("@Nid",s.Nid),
                new SqlParameter("@NName",s.NName),
                new SqlParameter("@Score",s.Score),
                new SqlParameter("@Weak",s.Wake),
                new SqlParameter("@Uid",s.Uid),
                new SqlParameter("@InputTime",s.InputTime),
                new SqlParameter("@STid",s.STid),
                new SqlParameter("@STName",s.STName),
            };
            if (SqlHelper.ExecuteNonQuery(sql, CommandType.Text, parameter) > 0)
                return true;
            else
                return false;
        }

        /// <summary>
        /// 根据用户id获取该用户指定时间的评分信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<ViewScore> getScoreByUID(int id, string time)
        {
            List<ViewScore> vss = new List<ViewScore>();
            string sql = "select * from setDataType where Name in ('办公桌面','安全防范能源消耗','请假次数','行为规范','礼貌礼节')";
            SetDataTypeService sdtss = new SetDataTypeService();
            SetDataService setDatasv = new SetDataService();
            List<SetDataType> sdts = sdtss.excuteSql(sql, null);
            if (sdts != null)
            {
                List<SetData> sds = new List<SetData>();

                for (int i = 0; i < sdts.Count; i++)
                {
                    ViewScore vs = new ViewScore();
                    Scores s = new Scores();

                    //根据每个考察类型和时间查询指定用户的分数信息
                    SqlParameter[] parameter = new SqlParameter[]{
                    new SqlParameter("@Uid",id),
                    new SqlParameter("@time",time),
                    new SqlParameter("@sTid",sdts[i].Id)
                    };
                    List<Scores> scores = excuteSql("select * from Score where Uid=@Uid and InputTime=@time and sTid=@sTid", CommandType.Text, parameter);
                    if (scores != null&&scores.Count>0)
                        s = scores[0];
                    vs.Sdt = sdts[i];
                    vs.Sc = s;
                    vss.Add(vs);
                }
            }
            return vss;
        }

        /// <summary>
        /// 返回实体的集合
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="commandType"></param>
        /// <param name="parameter"></param>
        /// <returns></returns>
        private List<Scores> excuteSql(string sql, CommandType commandType, SqlParameter[] parameter)
        {
            return ServiceBase<Scores>.excuteSql(new Scores(), "Scores", sql, CommandType.Text, parameter);
        }

        /// <summary>
        /// 根据考察类型和时间获取员工的评分信息
        /// </summary>
        /// <param name="p"></param>
        /// <param name="p_2"></param>
        /// <param name="stid"></param>
        /// <returns></returns>
        public List<Scores> getScoreBySTID(string time, int uid, int stid)
        {
            SqlParameter[] parameter = new SqlParameter[]{
            new SqlParameter("@Uid",uid),
            new SqlParameter("@time",time),
            new SqlParameter("@sTid",stid)
            };
            return excuteSql("select * from Score where Uid=@Uid and InputTime=@time and sTid=@stid", CommandType.Text, parameter);
        }
    }
}