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 cgsqdDal
    {
        public List<cgsqdModel> GetAllList(string id)
        {
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                string sql = "select * from cgsqd where 1=1";
                if (!string.IsNullOrEmpty(id))
                {
                    sql += " and id=@id";
                }
                return conn.Query<cgsqdModel>(sql, new { @id = id }).ToList();
            }
        }

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

        public object save(cgsqdModel model)
        {
            string sql = "";
            if (model.id == 0)
            {
                sql = @"INSERT INTO [dbo].[cgsqd]
                           ([sqdh]
                           ,[sqrid]
                           ,[sqr]
                           ,[sqksid]
                           ,[sqks]
                           ,[sqrq]
                           ,[jflyid]
                           ,[jfly]
                           ,[sqyy]
                           ,[cgje]
                           ,[zt]
                           ,[bmld]
                           ,[bmshsj]
                           ,[dwld]
                           ,[dwshsj]
                           ,[czsj]
                           ,[cgzt])
                     VALUES
                           (@sqdh
                           ,@sqrid
                           ,@sqr
                           ,@sqksid
                           ,@sqks
                           ,@sqrq
                           ,@jflyid
                           ,@jfly
                           ,@sqyy
                           ,@cgje
                           ,@zt
                           ,@bmld
                           ,@bmshsj
                           ,@dwld
                           ,@dwshsj
                           ,@czsj
                           ,@cgzt)";
            }
            else
            {
                sql = @"UPDATE [dbo].[cgsqd]
                       SET [sqdh] = @sqdh
                          ,[sqrid] = @sqrid
                          ,[sqr] = @sqr
                          ,[sqksid] = @sqksid
                          ,[sqks] = @sqks
                          ,[sqrq] = @sqrq
                          ,[jflyid] = @jflyid
                          ,[jfly] = @jfly
                          ,[sqyy] = @sqyy
                          ,[cgje] = @cgje
                          ,[zt] = @zt
                          ,[bmld] = @bmld
                          ,[bmshsj] = @bmshsj
                          ,[dwld] = @dwld
                          ,[dwshsj] = @dwshsj
                          ,[czsj] = @czsj
                          ,[cgzt] = @cgzt
                          WHERE  id=@id";
            }

            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                try
                {
                    int result = conn.Execute(sql, model);
                    if (result > 0)
                        return new { State = 1, Message = "保存成功！" };
                    else
                        return new { State = 0, Message = "保存失败！" };
                }
                catch (Exception ex)
                {
                    return new { State = 0, Message = ex.Message };
                }
            }
        }

        public object opSave(string id, string jflyid, string jfly, string sqyy, string cgje, string items, int sqrid, string sqr, int sqksid, string sqks)
        {
            string errmsg = "";
            string sqdh = getsqdh(out errmsg);
            if (errmsg != "")
                return new { State = 0, Message = "false" };
            cgsqdModel model = new cgsqdModel();
            model.sqdh = sqdh;
            model.sqrid = sqrid;
            model.sqr = sqr;
            model.sqksid = sqksid;
            model.sqks = sqks;
            model.sqrq = DateTime.Now;
            model.jflyid = Convert.ToInt32(jflyid);
            model.jfly = jfly;
            model.sqyy = sqyy;
            model.cgje = Convert.ToDecimal(cgje);
            model.zt = 0;

            string sql = @"INSERT INTO [dbo].[cgsqd]
                           ([sqdh],[sqrid],[sqr],[sqksid],[sqks],[sqrq],[jflyid],[jfly],[sqyy],[cgje],[zt],[bmld],[bmshsj],[dwld],[dwshsj],[czsj],[cgzt])
                     VALUES
                           (@sqdh,@sqrid,@sqr,@sqksid,@sqks,@sqrq,@jflyid,@jfly,@sqyy,@cgje,@zt,@bmld,@bmshsj,@dwld,@dwshsj,@czsj,@cgzt)select SCOPE_IDENTITY()";

            int cgsqdid = 0;
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                try
                {
                    cgsqdid = conn.Query<int>(sql, model).FirstOrDefault();
                }
                catch (Exception ex)
                {
                    return new { State = 0, Message = "false" };
                }
            }

            List<cgsqdmxModel> models = new List<cgsqdmxModel>();
            cgsqdmxModel m;
            foreach (string item in items.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries))
            {
                string[] itemss = item.Split('|');
                if (itemss.Length == 4)
                {
                    m = new cgsqdmxModel();
                    m.sqdid = cgsqdid;
                    m.wzid = Convert.ToInt32(itemss[0]);
                    m.wzmc = itemss[1];
                    m.dj = Convert.ToDecimal(itemss[2]);
                    m.sl = Convert.ToInt32(itemss[3]);
                    m.czsj = DateTime.Now;
                    models.Add(m);
                }
            }

            string sql2 = @"INSERT INTO [dbo].[cgsqdmx]
                           ([sqdid],[wzid],[wzmc],[sl],[dj],[czsj])
                     VALUES
                           (@sqdid,@wzid,@wzmc,@sl,@dj,@czsj)";
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                try
                {
                    int result = conn.Execute(sql2, models);
                    if (result > 0)
                        return new { State = 1, Message = sqdh };
                    else
                        return new { State = 0, Message = "false" };
                }
                catch (Exception ex)
                {
                    return new { State = 0, Message = "false" };
                }
            }
        }

        public object opSave2(string id, string jflyid, string jfly, string sqyy, string cgje, string items, int sqrid, string sqr, int sqksid, string sqks)
        {
            cgsqdModel model = new cgsqdModel();
            model.id = Convert.ToInt32(id);
            model.jflyid = Convert.ToInt32(jflyid);
            model.jfly = jfly;
            model.sqyy = sqyy;
            model.cgje = Convert.ToDecimal(cgje);

            string sql = @"UPDATE [dbo].[cgsqd]
                          SET [jflyid] = @jflyid,[jfly] = @jfly,[sqyy] = @sqyy,[cgje] = @cgje
                          WHERE  id=@id";
            string sql3 = "delete from cgsqdmx where sqdid=@id";

            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                try
                {
                    conn.Execute(sql, model);
                    conn.Execute(sql3, new { @id = id });
                }
                catch (Exception ex)
                {
                    return new { State = 0, Message = "false" };
                }
            }

            List<cgsqdmxModel> models = new List<cgsqdmxModel>();
            cgsqdmxModel m;
            foreach (string item in items.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries))
            {
                string[] itemss = item.Split('|');
                if (itemss.Length == 4)
                {
                    m = new cgsqdmxModel();
                    m.sqdid = Convert.ToInt32(id);
                    m.wzid = Convert.ToInt32(itemss[0]);
                    m.wzmc = itemss[1];
                    m.dj = Convert.ToDecimal(itemss[2]);
                    m.sl = Convert.ToInt32(itemss[3]);
                    m.czsj = DateTime.Now;
                    models.Add(m);
                }
            }

            string sql2 = @"INSERT INTO [dbo].[cgsqdmx]
                           ([sqdid],[wzid],[wzmc],[sl],[dj],[czsj])
                     VALUES
                           (@sqdid,@wzid,@wzmc,@sl,@dj,@czsj)";
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                try
                {
                    int result = conn.Execute(sql2, models);
                    if (result > 0)
                        return new { State = 1, Message = "false" };
                    else
                        return new { State = 0, Message = "false" };
                }
                catch (Exception ex)
                {
                    return new { State = 0, Message = "false" };
                }
            }
        }
        //获取申请单号
        private string getsqdh(out string errmsg)
        {
            errmsg = "";
            string result = "";
            string start = DateTime.Now.ToString("yyyyMM") + "0001";
            string end = DateTime.Now.ToString("yyyyMM") + "9999";
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                string sql = "select top 1 sqdh from cgsqd where sqdh >= '" + start + "' and sqdh<= '" + end + "' order by id desc";
                try
                {
                    var result2 = conn.ExecuteScalar(sql);
                    if (result2 == null)
                        result = start;
                    else
                    {
                        result = (Convert.ToInt32(result2)+1).ToString();
                        if (result == end)
                            errmsg = "单号不足！";
                    }
                }
                catch (Exception ex)
                {
                    errmsg = ex.Message;
                }
            }
            return result;
        }

        public List<cgsqdModel> getCgsqdBySqdh(string sqdh)
        {
            string sql = "select * from cgsqd where sqdh in (@sqdh)";
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                return conn.Query<cgsqdModel>(sql, new { sqdh = sqdh }).ToList();
            }
        }

        public object getCgsqdMxBySqdId(string sqdid)
        {
            string sql = "select a.*,b.ggxh,b.jldw dw,(a.sl*a.dj) hj from cgsqdmx a join wz b on a.wzid=b.id  where a.sqdid in (@sqdid)";
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                return conn.Query(sql, new { sqdid = sqdid }).ToList();
            }
        }

        public object delete(string id)
        {
            string sql = "delete from cgsqd where id=@id";
            string sql2 = "delete from cgsqdmx where sqdid=@id";

            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                try
                {
                    int result = conn.Execute(sql, new { id = id });
                    result = conn.Execute(sql2, new { id = id });
                    if (result > 0)
                        return new { State = 1, Message = "操作成功！" };
                    else
                        return new { State = 0, Message = "操作失败！" };
                }
                catch (Exception ex)
                {
                    return new { State = 0, Message = ex.Message };
                }
            }
        }

        public object check(string id, string zt, string TrueName)
        {
            string sql = "update cgsqd set zt=@zt,bmld=@TrueName,bmshsj=@now where id=@id";
            string sql1 = "update cgsqd set zt=@zt,dwld=@TrueName,dwshsj=@now where id=@id";
            string sql2 = "update cgsqd set zt=@zt where id=@id";

            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                try
                {
                    var paras = new { id = id, zt = zt, TrueName = TrueName, now = DateTime.Now };
                    int result = 0;
                    switch (zt)
                    {
                        case "1": result = conn.Execute(sql, paras); break;
                        case "2": result = conn.Execute(sql1, paras); break;
                        case "-1": result = conn.Execute(sql2, paras); break;
                    }

                    if (result > 0)
                        return new { State = 1, Message = "操作成功！" };
                    else
                        return new { State = 0, Message = "操作失败！" };
                }
                catch (Exception ex)
                {
                    return new { State = 0, Message = ex.Message };
                }
            }
        }

        public int getCount(string key, string ksid)
        {
            string sql = "select count(1) from dbo.cgsqd where 1=1";
            if (!string.IsNullOrEmpty(key))
            {
                sql += " and zt = @key";
            }
            if (!string.IsNullOrEmpty(ksid))
            {
                sql += " and sqksid = @ksid";
            }
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                return conn.ExecuteScalar<int>(sql, new { key = key, ksid = ksid });
            }
        }

        public List<cgsqdModel> getPage(int page, int pagesize, string key, string ksid)
        {
            string sql = "select *,row_number() over(order by id desc) as rownum from cgsqd where 1=1";
            if (!string.IsNullOrEmpty(key))
            {
                sql += " and zt = @key";
            }
            if (!string.IsNullOrEmpty(ksid))
            {
                sql += " and sqksid = @ksid";
            }
            sql = "select * from (" + sql + ") t where t.rownum>(" + page + "-1)*" + pagesize + " and rownum<=" + page + "*" + pagesize;
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                return conn.Query<Models.cgsqdModel>(sql, new { key = key, ksid = ksid }).ToList();
            }
        }
    }
}