tijian_tieying/web/dccdc.DAL/EnterpriceInfoMaintainDal.cs

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

namespace dccdc.DAL
{
    public class EnterpriceInfoMaintainDal
    {
        public int getCount(string key)
        {
            string sql = "select count(1) from dbo.enterprise_info_maintain where 1=1";
            if (!string.IsNullOrEmpty(key))
            {
                sql += " and ( enterprise_name like @qyname or enterprise_pinyincode like @qyname )";
            }
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                return conn.ExecuteScalar<int>(sql, new { enterprise_name = "%" + key + "%" });
            }
        }

        public List<EnterpriceInfoMaintainModel> GetAllList(string id)
        {
            string param = "";
            string sql = "select * from enterprise_info_maintain where status = '是'";
            if (id != "")
            {
                param = " and id =@id";
            }
            sql += param;
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                return conn.Query<EnterpriceInfoMaintainModel>(sql, new {id=id}).ToList();
            }
        }
        public EnterpriceInfoMaintainModel GetItem(EnterpriceInfoMaintainModel model)
        {
            string sql = "select * from enterprise_info_maintain where loginname = @loginname and id != @id";
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                return conn.Query<EnterpriceInfoMaintainModel>(sql, model).FirstOrDefault();
            }
        }
        public EnterpriceInfoMaintainModel GetLoginMessage(string loginname)
        {
            string sql = "select * from enterprise_info_maintain where loginname = @loginname";
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                return conn.Query<EnterpriceInfoMaintainModel>(sql, new { @loginname = loginname }).FirstOrDefault();
            }
        }
        
        public List<EnterpriceInfoMaintainModel> getListPage(int page, int pagesize, string qyname, string lx)
        {
            string sql = "select *,row_number() over(order by id) as rownum from enterprise_info_maintain where 1=1";
            if (!string.IsNullOrEmpty(qyname))
            {
                sql += " and ( enterprise_name like @qyname or enterprise_pinyincode like @qyname )";
            }
            if (!string.IsNullOrEmpty(lx))
            {
                sql += " and attribution=@lx";
            }
            sql = "select * from (" + sql + ") t where t.rownum>(" + page + "-1)*" + pagesize + " and rownum<=" + page + "*" + pagesize;
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                return conn.Query<Models.EnterpriceInfoMaintainModel>(sql, new { qyname = "%" + qyname + "%", lx = @lx }).ToList();
            }
        }

        public List<EnterpriceInfoMaintainModel> getList(int page, int pagesize,string key)
        {
            //throw new NotImplementedException();
            string sql = "select *,row_number() over(order by id desc) as rownum from enterprise_info_maintain where 1=1";
            if (!string.IsNullOrEmpty(key))
            {
                sql += " and ( enterprise_name like @qyname or enterprise_pinyincode like @qyname )";
            }
            sql = "select * from (" + sql + ") t where t.rownum>(" + page + "-1)*" + pagesize + " and rownum<=" + page + "*" + pagesize;
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                return conn.Query<Models.EnterpriceInfoMaintainModel>(sql, new { enterprise_name = "%" + key + "%" }).ToList();
            }
        }

        public int getCount(string qyname, string lx)
        {
            //throw new NotImplementedException();
            string sql = "select count(1) from enterprise_info_maintain where 1=1";
            if(!string.IsNullOrEmpty(qyname))
            {
                sql += " and ( enterprise_name like @qyname or enterprise_pinyincode like @qyname )";
            }
            if(!string.IsNullOrEmpty(lx))
            {
                sql += " and attribution=@lx";
            }

            using (var conn = CommHelper.GetSqlConnection())
            {
                return conn.ExecuteScalar<int>(sql, new { qyname = "%"+qyname+"%", lx = @lx });
            }
        }

        public object save(EnterpriceInfoMaintainModel ct, ERPUser user)
        {
            //throw new NotImplementedException();
            string sql = "";
            if (ct.id == 0)
            {
                sql = @"INSERT INTO [dbo].[enterprise_info_maintain]
           (
[enterprise_name],
[abbreviation],
[enterprise_pinyincode],
[post_id],
[address],
[belong_area],
[area_info_maintain_id],
[belong_trade],
[org_code],
[city_rural_type],
[school_type],
[trade_maintain_id],
[contacts],
[telephone],
[email],
[portraiture],
[introduction],
[status],
[attribution])
     VALUES
           (@enterprise_name,
@abbreviation,
@enterprise_pinyincode,
@post_id,
@address,
@belong_area,
@area_info_maintain_id,
@belong_trade,
@org_code,
@city_rural_type,
@school_type,
@trade_maintain_id,
@contacts,
@telephone,
@email,
@portraiture,
@introduction,
@status,
@attribution)
";
            }
            else
            {
                sql = @"UPDATE [dbo].[enterprise_info_maintain]
   SET [enterprise_name]=@enterprise_name,
[abbreviation]=@abbreviation,
[enterprise_pinyincode]=@enterprise_pinyincode,
[post_id]=@post_id,
[address]=@address,
[belong_area]=@belong_area,
[area_info_maintain_id]=@area_info_maintain_id,
[belong_trade]=@belong_trade,
[org_code]=@org_code,
[city_rural_type]=@city_rural_type,
[school_type]=@school_type,
[trade_maintain_id]=@trade_maintain_id,
[contacts]=@contacts,
[telephone]=@telephone,
[email]=@email,
[portraiture]=@portraiture,
[introduction]=@introduction,
[status]=@status,
[attribution]=@attribution
 WHERE id=@id
";
            }
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                try
                {
                    int c = conn.Execute(sql, ct);
                    if (c > 0)
                    {
                        return new { State = 1, Message = "保存成功！" };
                    }
                    else
                    {
                        return new { State = 0, Message = "操作失败，请联系管理员！" };
                    }
                }
                catch (Exception ex)
                {
                    return new { State = 0, Message = ex.Message };
                }
            }
        }

        public EnterpriceInfoMaintainModel motifyObj(EnterpriceInfoMaintainModel model)
        {
            string sql = "update enterprise_info_maintain set loginname=@loginname,loginpwd=@loginpwd where id = @id";
            using (var conn = CommHelper.GetSqlConnection())
            {
                int flag = conn.Execute(sql, model);
                if (flag > 0)
                {
                    return model;
                }
                else
                {
                    return null;
                }
            }
        }

        /// <summary>
        /// 根据id删除企业
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public object del(string id)
        {
            string sql = @"delete enterprise_info_maintain 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 };
                }
            }
        }
    }
}