tijian_tieying/web/dccdc.DAL/Common.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using dccdc.Models;
using Dapper;


namespace dccdc.DAL
{
    public class Common
    {
        public string getParm_Value(string parm_name, string default_value, string remarks)
        {
            //throw new NotImplementedException();
            using (var conn = CommHelper.GetSqlConnection())
            {
                string sql = "select count(1) from System_Parms where parm_name=@parm_name";
                var c = conn.ExecuteScalar<int>(sql, new { parm_name = parm_name });
                if (c == 0)
                {
                    sql = "insert into System_Parms(parm_name,parm_value,parm_remarks) values(@parm_name,@parm_value,@parm_remarks)";
                    conn.Execute(sql, new { parm_name = parm_name, parm_value = default_value, parm_remarks = remarks });
                    return default_value;
                }
                else
                {
                    sql = "select parm_value from System_Parms where parm_name=@parm_name";
                    var parm_value = conn.ExecuteReader(sql, new { parm_name = parm_name });
                    string v = "";
                    if (parm_value.Read())
                    {
                        v = parm_value["parm_value"].ToString();
                    }
                    else
                    {
                        v = "";
                    }
                    parm_value.Close();
                    return v;
                }
            }
        }

        /// <summary>
        /// 获取系统参数列表
        /// </summary>
        /// <param name="page"></param>
        /// <param name="pagesize"></param>
        /// <returns></returns>
        public List<SystemParmsModel> getListPage(int page, int pagesize, string strParamName, string strParamValue, string strParamRemark)
        {
            string sql = "select *,row_number() over(order by id desc) as rownum from System_Parms where 1=1 ";

            if (!string.IsNullOrEmpty(strParamName))
            {
                sql += " and parm_name like '%" + strParamName + "%'";
            }
            if (!string.IsNullOrEmpty(strParamValue))
            {
                sql += " and parm_value like '%" + strParamValue + "%'";
            }
            if (!string.IsNullOrEmpty(strParamRemark))
            {
                sql += " and parm_remarks like '%" + strParamRemark + "%'";
            }

            sql = "select * from (" + sql + ") t where t.rownum>(" + page + "-1)*" + pagesize + " and rownum<=" + page + "*" + pagesize;
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                return conn.Query<Models.SystemParmsModel>(sql).ToList();
            }
        }

        public SystemParmsModel getParm(string parm_name, string default_value, string remarks)
        {
            //throw new NotImplementedException();
            using (var conn = CommHelper.GetSqlConnection())
            {
                string sql = "select count(1) from System_Parms where parm_name=@parm_name";
                var c = conn.ExecuteScalar<int>(sql, new { parm_name = parm_name });
                if (c == 0)
                {
                    sql = "insert into System_Parms(parm_name,parm_value,parm_remarks) values(@parm_name,@parm_value,@parm_remarks)";
                    conn.Execute(sql, new { parm_name = parm_name, parm_value = default_value, parm_remarks = remarks });
                    return new SystemParmsModel { parm_name = parm_name, Parm_value = default_value, Parm_remarks = remarks };
                }
                else
                {
                    sql = "select * from System_Parms where parm_name=@parm_name";
                    var parm_value = conn.Query<SystemParmsModel>(sql, new { parm_name = parm_name }).FirstOrDefault();
                    return parm_value;
                }
            }
        }

        public int getCount(string strParamName, string strParamValue, string strParamRemark)
        {
            //throw new NotImplementedException();
            string sql = "select count(1) from System_Parms where 1=1";
            if(!string.IsNullOrEmpty(strParamName))
            {
                sql += " and parm_name like '%" + strParamName + "%'";
            }
            if (!string.IsNullOrEmpty(strParamValue))
            {
                sql += " and parm_value like '%" + strParamValue + "%'";
            }
            if (!string.IsNullOrEmpty(strParamRemark))
            {
                sql += " and parm_remarks like '%" + strParamRemark + "%'";
            }
            using (var conn = CommHelper.GetSqlConnection())
            {
                return conn.ExecuteScalar<int>(sql);
            }
        }

        public List<SystemParmsModel> GetAllList(string id)
        {
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                string param = "";
                if (id != "")
                {
                    param += " where id =" + id;
                }
                return conn.Query<SystemParmsModel>("select * from System_Parms " + param).ToList();
            }
        }
        /// <summary>
        /// 预约登记维护
        /// </summary>
        /// <param name="parmName"></param>
        /// <returns></returns>
        public SystemParmsModel GetModelBy(string parmName)
        {
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                string sql = "select * from System_Parms where parm_name ='" + parmName + "'";
                var list = conn.Query<SystemParmsModel>(sql);
                var systemParmsModels = list as SystemParmsModel[] ?? list.ToArray();
                if (systemParmsModels.Any())
                {
                    return systemParmsModels.First();
                }
                string sql1 = "insert into System_Parms values('mryyrs','100','每日预约人数')select SCOPE_IDENTITY()";
                int id = conn.Query<int>(sql1).FirstOrDefault();
                SystemParmsModel m = new SystemParmsModel
                {
                    id = id,
                    parm_name = "mryyrs",
                    Parm_remarks = "每日预约人数",
                    Parm_value = "100"
                };
                return m;
            }
        }

        public bool Update(SystemParmsModel model)
        {
            using (var conn = CommHelper.GetSqlConnection())
            {
                string sql = @"UPDATE [dbo].[System_Parms]
   SET [parm_name] = @parm_name
      ,[Parm_value] = @Parm_value
      ,[Parm_remarks] = @Parm_remarks
 WHERE id=@id";

                return (conn.Execute(sql, model) != 0 ? true : false);
            }
        }

        public bool Add(SystemParmsModel model)
        {
            using (var conn = CommHelper.GetSqlConnection())
            {
                string sql = @"INSERT INTO [dbo].[System_Parms]
                    ([parm_name]
                ,[Parm_value]
                ,[Parm_remarks])
            VALUES
            (@parm_name
                , @Parm_value
                , @Parm_remarks)";
                return (conn.Execute(sql, model) != 0 ? true : false);
            }
        }

        public void setParm_Value(string key, string value)
        {
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                string sql = "select * from System_Parms where parm_name ='" + key + "'";
                var list = conn.Query<SystemParmsModel>(sql).FirstOrDefault();
                if (list == null)
                {
                    string sql1 = "insert into System_Parms values('" + key + "','" + value + "','')";
                    conn.Execute(sql1);
                }
                else
                {
                    sql = "update System_Parms set  parm_name=@parm_name,Parm_value=@Parm_value where id=@id";
                    list.parm_name = key;
                    list.Parm_value = value;
                    conn.Execute(sql, list);
                }
                //return m;
            }
        }

        /// <summary>
        /// 根据id删除系统参数
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public object del(string id)
        {
            string sql = @"delete System_Parms where id=@id";

            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                try
                {
                    int c = conn.Execute(sql, new { id = id });
                    if (c > 0)
                    {
                        return new { State = 1, Message = "操作成功！" };
                    }
                    else
                    {
                        return new { State = 0, Message = "操作失败，请联系管理员！" };
                    }
                }
                catch (Exception ex)
                {
                    return new { State = 0, Message = ex.Message };
                }
            }
        }
    }
}