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 YMDJDal
    {

        public object getymdj()
        {
            string sql = "select vaccine.name as name,sum(YMDJMX.sfje) as num from YMDJ " +
" left join YMDJMX" +
" ON YMDJ.ID = YMDJMX.ymdjbid" +
" left join vaccine" +
" on YMDJMX.ymid = vaccine.id" +
" left join charge" +
" on YMDJ.chargeid = charge.id" +
" where jfzt = '已缴费' and YMDJMX.sfje <>0" +
" and charge.moneydate>convert(varchar(10),getdate(),121) " +
" group by vaccine.name";
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                return conn.Query(sql).ToList();
            }
        }

        public int getDJCount(string zsr, string djr, string djsjb, string djsje)
        {
            string sql = "select count(1) from dbo.YMDJ where 1=1 ";
            if (!string.IsNullOrEmpty(zsr))
            {
                sql += " and name like @zsr";
            }
            if (!string.IsNullOrEmpty(djr))
            {
                sql += " and djrxm like @djr";
            }
            if (!string.IsNullOrEmpty(djsjb))
            {
                sql += " and djsj >= '" + djsjb + "'";
            }
            if (!string.IsNullOrEmpty(djsje))
            {
                sql += " and djsj <= '" + djsje + "'";
            }
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                //new { zsr = "'%" + zsr + "%'", djr = "'%" + djr + "%'" };
                var result = conn.ExecuteScalar<int>(sql, new { zsr = "%" + zsr + "%", djr = "%" + djr + "%" });
                return result;
            }
        }
        public OperationResult delDj(ERPUser user, string id)
        {
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                YMDJDal dal = new YMDJDal();
                YMDJ ymdj = dal.getOneById(id);
                if(ymdj.djrid != user.ID.ToString())
                {
                    return new OperationResult { State = 0, Message = "不能删除其他人的登记！" };
                }else if (ymdj.zs == "1")
                {
                    return new OperationResult { State = 0, Message = "该登记已注射完成，不能删除！" };
                }
                else if (ymdj.jfzt == "已缴费" && ymdj.chargeid != "0")
                {
                    return new OperationResult { State = 0, Message = "该登记已缴费，不能删除！" };
                }
                YMDJMXDal mxDal = new YMDJMXDal();
                List<YMDJMX> ymdjmxs = mxDal.getList(1, 99999, ymdj.ymdjid);
                bool hasZs = false;
                for (int i = 0; i < ymdjmxs.Count(); i++)
                {
                    if(ymdjmxs[i].zssl > 0)
                    {
                        hasZs = true;
                    }
                }
                if (hasZs)
                {
                    return new OperationResult { State = 0, Message = "该登记已有疫苗注射成功，不能删除！" };
                }
                string sql = "delete from YMDJMX where ymdjbid = '" + ymdj.id+"'";
                conn.Execute(sql);
                sql = "delete from YMDJ where id = " + id;
                conn.Execute(sql);
                sql = "delete from charge_detail where chargeid = " + ymdj.chargeid;
                conn.Execute(sql);
                sql = "delete from charge where id = " + ymdj.chargeid;
                conn.Execute(sql);
            }
            return new OperationResult { State = 1, Message = "删除成功！" };
        }
        public YMDJ getOneById(string id)
        {
            //throw new NotImplementedException();
            string sql = "select * from YMDJ where id="+id;
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                return conn.Query<Models.YMDJ>(sql).FirstOrDefault();
            }
        }

        public int getCount(string name, string ymdjid)
        {
            string sql = "select count(1) from dbo.YMDJ where 1=1 ";
            if (!string.IsNullOrEmpty(name))
            {
                sql += " and name like @name";
            }
            if (!string.IsNullOrEmpty(ymdjid))
            {
                sql += " and ymdjid = @ymdjid";
            }
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                return conn.ExecuteScalar<int>(sql, new { name = "'%" + name + "%'", ymdjid = ymdjid });
            }
        }

        public List<YMDJ> getTFDJ(string xm)
        {
            //throw new NotImplementedException();
            string sql = "select YMDJ.* from  YMDJ  left join charge on YMDJ.chargeid = charge.id where jfzt='已缴费' and zs = 0 and charge.money > 0";
            if(!string.IsNullOrEmpty(sql))
            {
                sql += " and YMDJ.name like @xm";
            }
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                return conn.Query<Models.YMDJ>(sql,new { xm = "%" + xm + "%" }).ToList();
            }
        }
        public List<YMDJ> getJFDJ(string xm)
        {
            //throw new NotImplementedException();
            string sql = "select *,row_number() over(order by id) as rownum from YMDJ where jfzt='已缴费' and zs = 0";
            if(!string.IsNullOrEmpty(xm))
            {
                sql += " and name like @xm";
            }
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                return conn.Query<Models.YMDJ>(sql,new { xm = "%"+xm+"%" }).ToList();
            }
        }

        public YMDJ getDJByChargeID(string chargeid)
        {
            //throw new NotImplementedException();
            string sql = "select * from ymdj where chargeid=@chargeid";
            using (var conn = CommHelper.GetSqlConnection())
            {
                return conn.Query<YMDJ>(sql, new { chargeid = chargeid }).FirstOrDefault();
            }
        }

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

        public List<YMDJ> getDLList(int page, int pagesize, string zsr, string djr, string djsjb, string djsje)
        {
            //throw new NotImplementedException();
            string sql = @"select *,row_number() over(order by id) as rownum from YMDJ a left join
                         (select ymdjbid, (select name + ' ' from v_ymdjmx where ymdjbid = aa.ymdjbid for xml path('')) as names from v_ymdjmx aa group by ymdjbid)b on a.id = b.ymdjbid where 1 = 1";
            if (!string.IsNullOrEmpty(zsr))
            {
                sql += " and name like @zsr";
            }
            if (!string.IsNullOrEmpty(djr))
            {
                sql += " and djrxm like @djr";
            }
            if (!string.IsNullOrEmpty(djsjb))
            {
                sql += " and djsj >= '" + djsjb + "'";
            }
            if (!string.IsNullOrEmpty(djsje))
            {
                sql += " and djsj <= '" + djsje + "'";
            }
            sql = "select * from (" + sql + ") t where t.rownum>(" + page + "-1)*" + pagesize + " and rownum<=" + page + "*" + pagesize;
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                return conn.Query<Models.YMDJ>(sql, new { zsr = "%" + zsr + "%", djr = "%" + djr + "%" }).ToList();
            }
        }

        public OperationResult updateZS(string id)
        {
            string sql = "update YMDJ set zs = 1 where id = " + id;
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                try
                {
                    int c = conn.Execute(sql);
                    if (c > 0)
                    {
                        return new OperationResult { State = c, Message = "保存成功！" };
                    }
                    else
                    {
                        return new OperationResult { State = 0, Message = "操作失败，请联系管理员！" };
                    }
                }
                catch (Exception ex)
                {
                    return new OperationResult { State = 0, Message = ex.Message };
                }
            }
        }

        public OperationResult save(YMDJ cpm)
        {
            //throw new NotImplementedException();
            string sql = "";
            if (cpm.id == 0)
            {
                sql = @"INSERT INTO [dbo].[YMDJ]
           ([ymdjid],
[ymjzid],
[name],
[djrid],
[djrxm],
[djsj],
[sfzje],
[jfzt],
[jfsj],
[zs],
[chargeid]
)
     VALUES
           (@ymdjid,
@ymjzid,
@name,
@djrid,
@djrxm,
@djsj,
@sfzje,
@jfzt,
@jfsj,
@zs,
@chargeid
)select SCOPE_IDENTITY()
";
            }
            else
            {
                sql = @"UPDATE [dbo].[YMDJ]
   SET [ymdjid]=@ymdjid,
[ymjzid]=@ymjzid,
[name]=@name,
[djrid]=@djrid,
[djrxm]=@djrxm,
[djsj]=@djsj,
[sfzje]=@sfzje,
[jfzt]=@jfzt,
[jfsj]=@jfsj,
[zs]=@zs,
[chargeid]=@chargeid
 WHERE id=@id
";
            }
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                try
                {
                    int c = conn.Query<int>(sql, cpm).FirstOrDefault();
                    if (c > 0)
                    {
                        return new OperationResult { State = c, Message = "保存成功！" };
                    }
                    else
                    {
                        return new OperationResult { State = 0, Message = "操作失败，请联系管理员！" };
                    }
                }
                catch (Exception ex)
                {
                    return new OperationResult { State = 0, Message = ex.Message };
                }
            }
        }
        /// <summary>
        /// 根据疫苗ID获取收费项目的子项
        /// </summary>
        /// <param name="sfxmid"></param>
        /// <returns></returns>
        public List<ChargeProjectMaintainModel> GetchildsfxmByid(string sfxmid,string jmlx)
        {

            StringBuilder b = new StringBuilder("select * from charge_project_maintain where id in (select add_ids from charge_project_maintain where id =@sfxmid)");

            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                return conn.Query<ChargeProjectMaintainModel>(b.ToString(), new { id = sfxmid }).ToList();
            }
        }
    }
}