tijian_tieying/web/dccdc.DAL/charge_maintDal.cs

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

namespace dccdc.DAL
{
    public class charge_maintDal
    {
        public int getCount()
        {
            string sql = "select count(1) from dbo.charge_maint where 1=1";
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                return conn.ExecuteScalar<int>(sql);
            }
        }

        public List<charge_maintModel> GetAllList(string id)
        {
            string sql = "select * from dbo.charge_maint where 1=1";
            if (id != "")
            {
                sql += " and id=@id";
            }
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                return conn.Query<charge_maintModel>(sql, new { id = id }).ToList();
            }
        }

        public List<charge_maintModel> getList(int page, int pagesize)
        {
            string sql = "select *,row_number() over(order by id desc) as rownum from charge_maint where 1=1";
            sql = "select * from (" + sql + ") t where t.rownum>(" + page + "-1)*" + pagesize + " and rownum<=" + page + "*" + pagesize;
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                return conn.Query<charge_maintModel>(sql).ToList();
            }
        }

        public object save(charge_maintModel model)
        {
            string sql = "";
            if (model.id == 0)
            {
                using (IDbConnection conn = CommHelper.GetSqlConnection())
                {
                    try
                    {
                        string sql2 = "select count(1)  from  charge_maint  where  ((begin_num<=@begin_num  and end_num>=@begin_num) or (begin_num<=@end_num and end_num>=@end_num))";
                        int c = conn.ExecuteScalar<int>(sql2, model);
                        if (c > 0)
                        {
                            return new { State = 0, Message = "与其他发票段有重叠！" };
                        }
                    }
                    catch (Exception ex)
                    {
                        return new { State = 0, Message = ex.Message };
                    }
                }
                sql = @"INSERT INTO [dbo].[charge_maint]
                           ([begin_num]
                           ,[end_num]
                           ,[provide_person]
                           ,[status]
                           ,[receive_person]
                           ,[curr_time]
                           ,[receive_time]
                           ,[over_time]
                           ,[total_money]
                           ,[normal_cou]
                           ,[abnormal_cou]
                           ,[invoice_type])
                     VALUES
                           (@begin_num
                           ,@end_num
                           ,@provide_person
                           ,@status
                           ,@receive_person
                           ,@curr_time
                           ,@receive_time
                           ,@over_time
                           ,@total_money
                           ,@normal_cou
                           ,@abnormal_cou
                           ,@invoice_type)";
            }
            else
            {
                sql = @"UPDATE [dbo].[charge_maint]
                       SET [begin_num] = @begin_num
                          ,[end_num] = @end_num
                          ,[provide_person] = @provide_person
                          ,[status] = @status
                          ,[receive_person] = @receive_person
                          ,[curr_time] = @curr_time
                          ,[receive_time] = @receive_time
                          ,[over_time] = @over_time
                          ,[total_money] = @total_money
                          ,[normal_cou] = @normal_cou
                          ,[abnormal_cou] = @abnormal_cou
                          ,[invoice_type] = @invoice_type
                       WHERE  id=@id";
            }
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                try
                {
                    int c = conn.Execute(sql, model);
                    if (c > 0)
                    {
                        return new { State = 1, Message = "保存成功！" };
                    }
                    else
                    {
                        return new { State = 0, Message = "操作失败，请联系管理员！" };
                    }
                }
                catch (Exception ex)
                {
                    return new { State = 0, Message = ex.Message };
                }
            }
        }
    }
}