using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SOH.Entities;
using SOH.Data;
using EAS.Services;
using EAS.Data.ORM;
using EAS.Data.Linq;
using SOH.Entities.DTO;
using System.Data.SqlClient;
using System.Data;
using System.IO;

namespace SOH.BLL
{
    [ServiceBind(typeof(IQianTai2))]
    [ServiceObject("前台业务2")]
    public class QianTai2BLL : IQianTai2
    {
        /// <summary>
        /// 根据会员号获取会员信息
        /// </summary>
        /// <param name="hyh"></param>
        /// <returns></returns>
        public t_hyb GetHyInfoByHyh(int hyh)
        {
            using (DbEntities db = new DbEntities())
            {

                var data = from t in db.t_hybs where t.hyh == hyh select t;
                if (data.Any())
                {
                    return data.First();
                }
                else
                {
                    return null;
                }
            }

        }

        /// <summary>
        /// 根据医院代码查询员工信息（状态为未注销）
        /// </summary>
        /// <param name="yydm"></param>
        /// <returns></returns>
        public List<t_ygzd> GetYgzdByFD(short yydm)
        {
            using (DbEntities db = new DbEntities())
            {
                var data = from t in db.t_ygzds where t.zhuxiao == 0 && t.yydm == yydm select t;
                if (data.Any())
                {
                    List<t_ygzd> lty = data.ToList();
                    return lty;
                }
                else
                {
                    return null;
                }
            }
        }

        /// <summary>
        /// 根据条件获取相应的套餐列表
        /// </summary>
        /// <param name="xb">性别</param>
        /// <param name="hyzk">婚姻状况</param>
        /// <returns></returns>
        public List<t_tc> GetTCByCondition(short xb, short hyzk, short fddm)
        {
            List<t_tc> ltt = new List<t_tc>();
            using (DbEntities db = new DbEntities())
            {
                var data = from t in db.t_tcs where t.fddm == fddm && t.upflag=="0" && t.zhuxiao == 0 && (xb == 2 ? true : (t.xb == xb || t.xb == 2)) && (hyzk == 2 ? true : (t.hyzk == hyzk || t.hyzk == 2)) select t;
                if (data.Any())
                {
                    ltt = data.OrderBy(t => t.tcbm).ToList();
                    return ltt;
                }
                else
                {
                    return null;
                }
            }
        }

        public List<t_tc> GetTCByCondition2(short xb, short hyzk, short fddm)
        {
            List<t_tc> ltt = new List<t_tc>();
            using (DbEntities db = new DbEntities())
            {
                var data = from t in db.t_tcs where t.fddm == fddm && t.upflag == "1" && t.zhuxiao == 0 && (xb == 2 ? true : (t.xb == xb || t.xb == 2)) && (hyzk == 2 ? true : (t.hyzk == hyzk || t.hyzk == 2)) select t;
                if (data.Any())
                {
                    ltt = data.OrderBy(t => t.tcbm).ToList();
                    return ltt;
                }
                else
                {
                    return null;
                }
            }
        }

        /// <summary>
        /// 根据身份证号获取会员信息
        /// </summary>
        /// <param name="sfzh"></param>
        /// <returns></returns>
        public t_hyb GetHyBySfzh(string sfzh)
        {
            t_hyb thyb = new t_hyb();
            if (string.IsNullOrEmpty(sfzh))
            {
                return null;
            }
            using (DbEntities db = new DbEntities())
            {
                var data = from t in db.t_hybs where t.sfz == sfzh select t;
                if (data.Any())
                {
                    return data.First();
                }
                else
                {
                    return null;
                }
            }
        }

        /// <summary>
        /// 插入会员信息，返回是否成功，out 会员号
        /// </summary>
        /// <param name="thyb"></param>
        /// <param name="hyh"></param>
        /// <returns></returns>
        public OperationResult AddHyb(t_hyb thyb)
        {
            OperationResult or = new OperationResult();
            using (var db = new DbEntities())
            {
                //取最新会员号
                try
                {
                    thyb.hyh = (int)new BasicBll().GetMax("t_hyb", 1);
                    //db.t_hybs.OrmAccessor.Insert(thyb);
                    db.t_hybs.Insert(thyb);

                }
                catch (Exception ex)
                {
                    or.State = 0;
                    or.Message = (ex.InnerException ?? ex).Message.ToString();

                }
            }
            return or;
        }

        /// <summary>
        /// 个人登记
        /// </summary>
        /// <param name="thyb"></param>
        /// <param name="tg"></param>
        /// <param name="ltj"></param>
        /// <param name="ifExitHy"></param>
        /// <returns></returns>
        public OperationResult GRDJ(t_hyb thyb, t_grgzb tg, List<t_jjxb> ltj, bool ifExitHy)
        {
            OperationResult or = new OperationResult();

            if (tg == null || ltj == null || ltj.Count == 0)
            {

            }
            using (DbEntities db = new DbEntities())
            {
                var tran = db.CreateTransaction();
                try
                {
                    //身份证号不为空的时候判断会员表是否存在此会员
                    if (!string.IsNullOrEmpty(thyb.sfz))
                    {
                        //查询会员表是否有信息
                        var hydata = from a in db.t_hybs
                                     where a.sfz == thyb.sfz
                                     select a;
                        if (hydata.Any()) //存在对应的会员
                        {
                            ifExitHy = true;
                        }
                        else
                        {
                            ifExitHy = false;
                        }
                    }

                    if (ifExitHy) //如果会员存在
                    {
                    }
                    else
                    {
                        db.t_hybs.Insert(thyb);
                    }
                    db.t_grgzbs.Insert(tg);
                    ltj.ForEach(t =>
                    {
                        db.t_jjxbs.Insert(t);
                    });
                    tran.Commit();
                    or.State = 1;
                    or.Message = "操作成功";
                    return or;
                }
                catch (Exception ex)
                {
                    tran.Rollback();
                    or.State = 0;
                    or.Message = (ex.InnerException ?? ex).Message.ToString();
                    return or;
                }

            }
        }


        /// <summary>
        /// 根据条码号取团体中的人员信息
        /// </summary>
        /// <param name="tm"></param>
        /// <param name="sfzh"></param>
        /// <returns></returns>
        public string GetTTDJInfo(int tm)
        {
            //如果我们的条码有规则的话，那么这里就进行条码和身份证的判断
            //tm是int  身份证是string
            using (DbEntities db = new DbEntities())
            {
                var data = from a in db.t_ttgzbs
                           join b in db.t_hts on a.htbm equals b.htbm
                           join c in db.t_khs on b.khbm equals c.khbm
                           join d in db.t_htfzbs on a.htfzbm equals d.htfzbm
                           where a.tm == tm
                           select new
                           {
                               tm = a.tm,
                               khmc = c.khmc,
                               htfzmc = d.htfzmc,
                               htfzbm=d.htfzbm,
                               hyh = a.hyh,
                               xm = a.xm,
                               xb = a.xb,
                               nl = a.nl,
                               hyzk = a.hyzk,
                               ygh = a.ygh,
                               bm = a.ssbm,
                               lxdh = a.tel,
                               csrq = a.csrq,
                               gzfbj = d.gzfbj,
                               jxgzfbj = d.jxgzfbj,
                               bz = a.bz,
                               htzt = b.htzt,
                               htjzrq=b.htjzrq,
                               tmztz = a.tmztz,
                               fddm = a.fddm,
                               sfzh = a.sfzh,
                               tjlb = a.khlx,
                           };
                if (data.Any())
                {
                    string json = Newtonsoft.Json.JsonConvert.SerializeObject(data.ToList());
                    return json;
                }
                else
                {
                    return "";
                }
            }
        }

        /// <summary>
        /// 根据分店代码和客户名称检索团检客户信息
        /// </summary>
        /// <param name="fddm"></param>
        /// <param name="khmc"></param>
        /// <returns></returns>
        public List<Entities.DTO.dto_tjxx> GetTJXX(short fddm, string khmc)
        {
            List<Entities.DTO.dto_tjxx> ldt = new List<Entities.DTO.dto_tjxx>();
            bool temp = string.IsNullOrEmpty(khmc);
            using (DbEntities db = new DbEntities())
            {
                var data = from a in db.t_hts
                           join b in db.t_khs
                           on a.khbm equals b.khbm
                           where a.fddm == fddm && (temp ? true : b.khmc.Contains(khmc))
                           select new SOH.Entities.DTO.dto_tjxx
                           {
                               ywdbdm = a.ywdbdm,
                               htbm = a.htbm,
                               khbm = a.khbm,
                               khmc = b.khmc,
                               nd = a.nd,
                               htbj = a.htbj
                           };
                if (data.Any())
                {
                    ldt = data.ToList();
                }
                else
                {
                    return null;
                }
                return ldt;
            }
        }

        /// <summary>
        /// 根据合同编码、合同状态获取合同中的分组信息
        /// </summary>
        /// <param name="htbm"></param>
        /// <param name="htzt">合同状态，-1表示查询全部</param>
        /// <returns></returns>
        public List<t_htfzb> GetHtfz(int htbm, short htzt)
        {
            using (DbEntities db = new DbEntities())
            {
                bool ifhtzt = false;
                if (htzt == -1)
                {
                    ifhtzt = true;
                }
                var data = from a in db.t_htfzbs
                           join b in db.t_hts
     on a.htbm equals b.htbm
                           where a.htbm == htbm && (ifhtzt ? true : b.htzt == htzt)
                           select a;
                if (data.Any())
                {
                    List<t_htfzb> lth = data.ToList();
                    return lth;
                }
                else
                {
                    return null;
                }
            }
        }

        /// <summary>
        /// 团检登记_现场
        /// </summary>
        /// <param name="tg">带有所需属性的t_ttgzb</param>
        /// <param name="sfbj">公费还是自费的标识，0公费，1自费</param>
        /// <returns></returns>
        public OperationResult Tjdj_XC(t_ttgzb tg, short sfbj, string isOpen)
        {
            OperationResult or = new OperationResult();
            using (DbEntities db = new DbEntities())
            {
                var tran = db.CreateTransaction();
                try
                {
                    DateTime djrq = DateTime.Now;
                    int tmztz = 2; //公费的时候
                    if (sfbj == 1)
                    {
                        tmztz = 1; //自费的时候
                    }
                    int hyh = tg.hyh;
                    int tm = tg.tm;

                    //db.t_ttgzbs.FirstOrDefault(t => t.tm == tg.tm);
                    var data1 = db.t_hybs.FirstOrDefault(t => t.sfz == tg.sfzh);
                    if (tg.sfzh == "" && tg.tel != "")
                    {
                        data1 = db.t_hybs.FirstOrDefault(t => t.tel1 == tg.tel && t.xm == tg.xm);
                    }
                    else if (tg.sfzh != "")
                    {
                        data1 = db.t_hybs.FirstOrDefault(t => t.sfz == tg.sfzh);
                    }
                    else
                    {
                        data1 = null;
                    }

                    if (data1 != null) //会员表中已经存在相应的会员信息
                    {
                        hyh = data1.hyh;
                        //更改t_hyb信息
                        string sql1 = "update t_hyb set xm='" + tg.xm + "',sfz='" + tg.sfzh + "',tel1='" + tg.tel + "',xb=" + tg.xb + ",hyzk=" + tg.hyzk + ",nl=" + tg.nl + "  where hyh=" + hyh;
                        db.DataAccessor.Execute(sql1);
                    }
                    else
                    {
                        //根据条码获取客户单位名称
                        var data2 = from a in db.t_khs
                                    join b in db.t_hts on a.khbm equals b.khbm
                                    join c in db.t_ttgzbs on b.htbm equals c.htbm
                                    where c.tm == tg.tm
                                    select a.khmc;
                        string khmc = data2.FirstOrDefault();

                        //添加会员信息
                        t_hyb th = new t_hyb();
                        th.hybz = 1;
                        th.hyh = tg.hyh;
                        th.xm = tg.xm;
                        th.xb = tg.xb;
                        th.nl = tg.nl;
                        th.hyzk = tg.hyzk;
                        th.csrq = tg.csrq;
                        th.xx = 4;
                        th.sfz = tg.sfzh;
                        th.dw = khmc;
                        th.txdz = tg.bz;
                        th.bz = tg.bz;
                        th.tel1 = tg.tel;
                        th.yhjb = 0;
                        th.upflag = 0;
                        db.t_hybs.Insert(th);
                    }
                    //根据tm更改t_ttgzb中的对应信息
                    //string sql2 = "update t_ttgzb set tmztz=" + tmztz + ",xm='" + tg.xm + "',xb=" + tg.xb + ",hyzk=" + tg.hyzk + ",sfzh='" + tg.sfzh + "',
                    //djczy ='" + tg.djczy + "',csrq='" + tg.csrq.ToString() + "',tjrq=getdate() ,upflag=1,tel='" + tg.tel + "',bz='" + tg.bz + "' where tm=" + tg.tm;
                    //db.DataAccessor.Execute(sql2);
                    var tt = db.t_ttgzbs.FirstOrDefault(t => t.tm == tg.tm);
                    tt.hyh = hyh;
                    tt.tmztz = (short)tmztz;
                    tt.xm = tg.xm;
                    tt.xb = tg.xb;
                    tt.hyzk = tg.hyzk;
                    tt.sfzh = tg.sfzh;
                    tt.djczy = tg.djczy;
                    tt.csrq = tg.csrq.Date;
                    tt.tjrq = DateTime.Now;
                    tt.upflag = 1;
                    tt.tel = tg.tel;
                    tt.bz = tg.bz;
                    db.t_ttgzbs.Update(tt);
                    if (sfbj == 1)  //自费的需要
                    {

                    }
                    else  //公费 ,直接想t_tempdjxm中加入收费信息
                    {
                        //更新t_tempdjxm 中该tm对应的fddm\djrq\ where tm= and ztz=0
                        db.DataAccessor.Execute("update t_tempdjxm set fddm=" + tg.fddm + ",djrq='" + djrq.ToString() + "' where tm=" + tm + " and ztz=0");

                        int htfzbm = tg.htfzbm;
                        if (htfzbm == 0)
                        {
                            or.State = 0;
                            or.Message = "未能找到此客人对应的体检分组";
                            return or;
                        }
                        //根据合同分组编码查询htxmb中的信息
                        var data2 = from b in db.t_htxmbs where b.htfzbm == htfzbm select b;
                        List<t_htxmb> lth = new List<t_htxmb>();
                        if (data2.Any())
                        {
                            lth = data2.ToList();
                        }
                        else
                        {
                            or.State = 0;
                            or.Message = "未能找到此人对应的体检分组";
                            return or;
                        }
                        //拼接zhbm信息，到t_tempdjxm中删除
                        string delstr = "";
                        for (int i = 0; i < lth.Count; i++)
                        {
                            if (i == 0)
                            {
                                delstr = lth[i].zhbm.ToString();
                            }
                            else
                            {
                                delstr += "," + lth[i].zhbm.ToString();
                            }

                        }
                        db.DataAccessor.Execute("delete from t_tempdjxm where tm=" + tm + " and zhbm in (" + delstr + ")");

                        var data3 = from c in db.t_zhxms join d in db.t_htxmbs on c.zhbm equals d.zhbm where d.htfzbm == htfzbm select c;
                        List<t_zhxm> ltz = new List<t_zhxm>();
                        if (data3.Any())
                        {
                            ltz = data3.ToList();
                        }
                        else
                        {
                            or.State = 0;
                            or.Message = "该客户的合同分组下没有任何组合项目";
                            return or;
                        }
                        //循环插入数据
                        for (int j = 0; j < ltz.Count; j++)
                        {
                            string zhbm = ltz[j].zhbm.ToString();
                            string ksbm = ltz[j].ksbm.ToString();
                            string tempstr = "insert into t_tempdjxm(tm,zhbm,ksbm,djrq,ztz) values(" + tm + "," + zhbm + "," + ksbm + ",'" + djrq + "',0) ";
                            db.DataAccessor.Execute(tempstr);
                        }

                    }
                    tran.Commit();
                    or.State = 1;
                    or.Message = "登记成功";

                    if (isOpen == "1")
                    {
                        if (sfbj == 0)
                        {
                            string ksname = PaiDuiJiaoHaoBLL.sendPaiduixinxi(tg.tm);
                            if (!string.IsNullOrEmpty(ksname))
                                or.Message += "请到" + ksname + "体检";
                        }
                    }
                    return or;
                }
                catch (Exception ex)
                {
                    tran.Rollback();
                    or.State = 0;
                    or.Message = (ex.InnerException ?? ex).Message;
                    return or;
                }
            }
        }
        /// <summary>
        /// 团检登记_持卡
        /// </summary>
        public OperationResult Tjdj_CK(t_ttgzb tg, string isOpen)
        {
            OperationResult or = new OperationResult();
            using (DbEntities db = new DbEntities())
            {
                var tran = db.CreateTransaction();
                try
                {
                    short fddm = tg.fddm;
                    DateTime djrq = DateTime.Now;
                    int tm = tg.tm;
                    var tgzb = from a in db.t_ttgzbs where a.tm == tg.tm select a;
                    t_ttgzb tg_old = tgzb.FirstOrDefault(); //根据条码找出t_ttgzb信息
                    int hyh = tg_old.hyh;

                    //4.会员表没有信息的话 在会员表插入一条数据
                    //insert into t_hyb (hybz,hyh,xm,xb,nl,hyzk,csrq,dw,mz,xx,sfz,zy,zw,txdz,yb,tel1,tel2,tel3,email,yhjb,bz) 
                    //values(1,000003037,'曹汝磊',0,24,0,'1991-1-1','邮储银行烟台分行','',4,'','','','','','','','','','','')
                    var data4 = db.t_hybs.FirstOrDefault(t => t.sfz == tg.sfzh);
                    if (tg.sfzh == "" && tg.tel != "")
                    {
                        data4 = db.t_hybs.FirstOrDefault(t => t.tel1 == tg.tel && t.xm == tg.xm);
                    }
                    else if (tg.sfzh != "")
                    {
                        data4 = db.t_hybs.FirstOrDefault(t => t.sfz == tg.sfzh);
                    }
                    else
                    {
                        data4 = null;
                    }

                    if (data4 != null)
                    {
                        hyh = data4.hyh;
                        //原来有会员信息不作插入
                    }
                    else
                    {

                        //取出单位信息
                        var dwdata = from a in db.t_khs
                                     join b in db.t_hts
                                         on a.khbm equals b.khbm
                                     join c in db.t_ttgzbs
                                         on b.htbm equals c.htbm
                                     select a.khmc;
                        string dwmc = dwdata.FirstOrDefault();
                        t_hyb th = new t_hyb();
                        th.hybz = 1;

                        var data5 = db.t_hybs.FirstOrDefault(t => t.hyh == hyh);
                        if (data5 != null)
                        {
                            hyh = new BLL.BasicBll().gethyh(Convert.ToInt32(tg.fddm), 1);
                        }
                        th.hyh = hyh;
                        th.sfz = tg.sfzh;
                        th.xm = tg.xm;
                        th.xb = tg_old.xb;
                        th.nl = tg_old.nl;
                        th.hyzk = tg_old.hyzk;
                        th.csrq = tg.csrq;
                        th.dw = dwmc;
                        th.mz = "";
                        th.xx = 4;
                        th.zw = "";
                        th.txdz = tg.bz;
                        th.yb = "";
                        th.tel1 = tg.tel;
                        th.tel2 = "";
                        th.tel3 = "";
                        th.email = "";
                        th.yhjb = tg_old.tjjb;
                        th.bz = tg.bz;
                        db.t_hybs.Insert(th);
                    }

                    //1.更新t_ttgzb中的该tm对应的tmztz\djczy\tjrq\nl\tel\csrq\upflag=1\bz=''\fddm=2
                    var tt = db.t_ttgzbs.FirstOrDefault(t => t.tm == tg.tm);
                    if (tgzb != null)
                    {
                        tt.hyh = hyh;
                        tt.xm = tg.xm;
                        tt.tmztz = 2;
                        tt.djczy = tg.djczy;
                        tt.tel = tg.tel;
                        tt.upflag = 1;
                        tt.bz = tg.bz;
                        tt.image = tg.image;
                        tt.tjrq = tg.tjrq;
                        db.t_ttgzbs.Update(tt);
                        
                    }
                    //string sql1 = "update t_ttgzb set xm='" + tg.xm + "', tmztz=2,djczy='" + tg.djczy + "',tel='" + 
                    //              tg.tel + "',upflag=1,bz='" + tg.bz + "',image='"+tg.image+"' where tm=" + tg.tm;
                    //db.DataAccessor.Execute(sql1);

                    //2.更新t_tempdjxm 中该tm对应的fddm\djrq\ where tm= and ztz=0
                    db.DataAccessor.Execute("update t_tempdjxm set fddm=" + fddm + ",djrq='" + djrq + "' where tm=" + tm + " and ztz=0");

                    //3.select htfzbm from t_ttgzb where tm=1000003055
                    //select zhbm from t_htxmb where htfzbm=224
                    //根据tm查询htfzbm ，根据htfzbm查询zhbm集合
                    //删除t_tempdjxm 表中的对应tm 并且zhbm在zhbm集合中的信息
                    //将组合编码的相关信息再插入到 t_tempdjxm  
                    //--insert into t_tempdjxm(tm,zhbm,ksbm,djrq,ztz) values(1000003055,14,1,'2015/8/18',0)
                    //var data1 = from a in db.t_ttgzbs where a.tm == tm select a.htfzbm; //查询此人的合同分组编码
                    //if (!data1.Any())
                    //{
                    //    or.State = 0;
                    //    or.Message = "未能找到此客人对应的体检分组";
                    //    return or;
                    //}
                    int htfzbm = tg_old.htfzbm;
                    if (htfzbm == null || htfzbm == 0)
                    {
                        tran.Rollback();
                        or.State = 0;
                        or.Message = "未能找到此客人对应的体检分组";
                        return or;
                    }
                    //根据合同分组编码查询htxmb中的信息
                    var data2 = from b in db.t_htxmbs where b.htfzbm == htfzbm select b;
                    List<t_htxmb> lth = new List<t_htxmb>();
                    if (data2.Any())
                    {
                        lth = data2.ToList();
                    }
                    else
                    {
                        tran.Rollback();
                        or.State = 0;
                        or.Message = "未能找到此人对应的体检分组";
                        return or;
                    }
                    //拼接zhbm信息，到t_tempdjxm中删除
                    string delstr = "";
                    for (int i = 0; i < lth.Count; i++)
                    {
                        if (i == 0)
                        {
                            delstr = lth[i].zhbm.ToString();
                        }
                        else
                        {
                            delstr += "," + lth[i].zhbm.ToString();
                        }

                    }
                    db.DataAccessor.Execute("delete from t_tempdjxm where tm=" + tm + " and zhbm in (" + delstr + ")");

                    var data3 = from c in db.t_zhxms join d in db.t_htxmbs on c.zhbm equals d.zhbm where d.htfzbm == htfzbm select c;
                    List<t_zhxm> ltz = new List<t_zhxm>();
                    if (data3.Any())
                    {
                        ltz = data3.ToList();
                    }
                    else
                    {
                        tran.Rollback();
                        or.State = 0;
                        or.Message = "该客户的合同分组下没有任何组合项目";
                        return or;
                    }

                    //插入瑞美LIS表客户信息数据
                    //if(fddm==2)
                    //{
                    //    lis_reqmain lismain = new lis_reqmain();
                    //    for (int l = 0; l < ltz.Count; l++)
                    //    {
                    //        if (ltz[l].hsqr == 1)
                    //        {
                    //            lismain.testno = tg.tm.ToString();
                    //            lismain.deptno = "体检";
                    //            lismain.reqdatetime = tg.tjrq;
                    //            lismain.reqdoctno = tg.djczy;
                    //            lismain.patno = "";
                    //            lismain.patname = tg.xm;
                    //            switch (tg.xb)
                    //            {
                    //                case 0:
                    //                    lismain.sex = "男";
                    //                    break;
                    //                case 1:
                    //                    lismain.sex = "女";
                    //                    break;
                    //                case -1:
                    //                    lismain.sex = "不祥";
                    //                    break;
                    //            }
                    //            lismain.birthday = tg.csrq;
                    //            lismain.address = "";
                    //            lismain.telephone = tg.tel;
                    //            lismain.status = "1";
                    //            lismain.recievedatetime = DateTime.Now;
                    //            db.lis_reqmains.Insert(lismain);
                    //            break;
                    //        }
                    //    }
                    //}

                    lis_reqitems lisitem = new lis_reqitems();
                    //循环插入数据
                    for (int j = 0; j < ltz.Count; j++)
                    {
                        string zhbm = ltz[j].zhbm.ToString();
                        string ksbm = ltz[j].ksbm.ToString();
                        string tempstr = "insert into t_tempdjxm(tm,zhbm,ksbm,djrq,ztz) values(" + tm + "," + zhbm + "," + ksbm + ",'" + djrq + "',0) ";
                        db.DataAccessor.Execute(tempstr);

                        //瑞美LIS插入检查项目表
                        //if(fddm==2&&ltz[j].hsqr==1)
                        //{
                        //    //lisitem.testno
                        //}
                    }


                    string sqlstr = "update t_tempdjxm set ztz=4,upflag=1,fddm=" + fddm + " ,djrq='" + djrq + "'  from t_tempdjxm,t_zhxm " +
                        "where tm=" + tm + " and t_zhxm.zhbm=t_tempdjxm.zhbm and t_zhxm.jclb=2 and ztz=0";
                    db.DataAccessor.Execute(sqlstr);
                    tran.Commit();

                    or.State = 1;
                    or.Message = "登记成功";
                    if (isOpen == "1")
                    {
                        string ksname = PaiDuiJiaoHaoBLL.sendPaiduixinxi(tm);
                        if (!string.IsNullOrEmpty(ksname))
                            or.Message += "\r\n请到" + ksname + "体检";
                    }
                    return or;

                }
                catch (Exception ex)
                {
                    tran.Rollback();
                    or.State = 0;
                    or.Message = "操作失败：" + (ex.InnerException ?? ex).Message;
                    return or;
                }
            }
        }

        /// <summary>
        /// 根据条码号查询个人团体登记情况信息
        /// </summary>
        /// <param name="tm"></param>
        /// <returns></returns>
        public Entities.DTO.dto_tjdjxx GetTjdjxxAll(int tm)
        {
            using (DbEntities db = new DbEntities())
            {
                Entities.DTO.dto_tjdjxx tjxx = new Entities.DTO.dto_tjdjxx();
                var data = from a in db.t_ttgzbs
                           join b in db.t_hts
                               on a.htbm equals b.htbm
                           join c in db.t_khs on b.khbm equals c.khbm
                           where a.tm == tm
                           select new Entities.DTO.dto_tjdjxx
                           {
                               tm = tm,
                               tmztz = a.tmztz,
                               hyh = a.hyh,
                               xm = a.xm,
                               xb = a.xb,
                               nl = a.nl,
                               hyzk = a.hyzk,
                               ygh = a.ygh,
                               ssbm = a.ssbm,
                               khmc = c.khmc,
                               lx = 1,
                               sfzh = a.sfzh,
                               csrq = a.csrq,
                               tel = a.tel,
                               txdz = a.bz,
                               dxtj = 0,
                               image = a.image
                           };
                if (data.Any())
                {
                    return data.First();
                }
                else
                {
                    var data2 = from a in db.t_grgzbs
                                where a.tm == tm
                                select new Entities.DTO.dto_tjdjxx
                                {
                                    tm = tm,
                                    tmztz = a.tmztz,
                                    hyh = a.hyh,
                                    xm = a.xm,
                                    xb = a.xb,
                                    nl = a.nl,
                                    hyzk = a.hyzk,
                                    ygh = "",
                                    ssbm = "",
                                    khmc = "",
                                    lx = 0,
                                    sfzh = a.sfzh,
                                    csrq = a.csrq,
                                    tel = a.dh,
                                    txdz = a.dz,
                                    dxtj = a.dxtj,
                                    image = a.image
                                };
                    if (data2.Any())
                    {
                        return data2.First();
                    }
                    else
                    {
                        return null;
                    }
                }
            }


            //return 
        }


        /// <summary>
        /// 根据条码号查询团检登记情况信息
        /// </summary>
        /// <param name="tm"></param>
        /// <returns></returns>
        public Entities.DTO.dto_tjdjxx GetTjdjxx(int tm)
        {
            using (DbEntities db = new DbEntities())
            {
                Entities.DTO.dto_tjdjxx tjxx = new Entities.DTO.dto_tjdjxx();
                var data = from a in db.t_ttgzbs
                           join b in db.t_hts
                               on a.htbm equals b.htbm
                           join c in db.t_khs on b.khbm equals c.khbm
                           where a.tm == tm
                           select new Entities.DTO.dto_tjdjxx
                           {
                               tm = tm,
                               tmztz = a.tmztz,
                               hyh = a.hyh,
                               xm = a.xm,
                               xb = a.xb,
                               nl = a.nl,
                               hyzk = a.hyzk,
                               ygh = a.ygh,
                               ssbm = a.ssbm,
                               khmc = c.khmc
                           };
                if (data.Any())
                {
                    return data.First();
                }
                else
                {
                    return null;
                }
            }


            //return 
        }

        //根据条码获取团检的合同状态
        public short GetHtztByTm(int tm)
        {
            using (DbEntities db = new DbEntities())
            {
                //string sqlstr = "select htzt from t_ttgzb,t_ht where t_ht.htbm=t_ttgzb.htbm and tm=" + tm;
                var data = from a in db.t_ttgzbs
                           join b in db.t_hts on a.htbm equals b.htbm
                           where a.tm == tm
                           select b.htzt;
                if (data.Any())
                {
                    short htzt = data.First();
                    return htzt;
                }
                else
                {
                    return -1;
                }
            }


        }

        /// <summary>
        /// 根据条码判断此团检人员是否允许取消登记
        /// </summary>
        /// <returns></returns>
        public OperationResult IfCanCancleDJ_TJ(int tm)
        {
            using (DbEntities db = new DbEntities())
            {
                OperationResult or = new OperationResult();
                var datazt = from a in db.t_ttgzbs where a.tm == tm && (a.tmztz == 1 || a.tmztz == 2) select a;
                {
                    if (datazt.Any())
                    {
                        //继续
                    }
                    else
                    {
                        or.State = 0;
                        or.Message = "该客户的状态无法取消登记";
                        return or;
                    }
                }

                //1.先根据tm查看是否有收费明细，有的不能取消
                var data1 = from a in db.t_sfmxbs where a.tm == tm select a;
                if (data1.Any())
                {
                    or.State = 0;
                    or.Message = "此客户存在交费信息，不能取消登记";
                    return or;
                }

                //2.根据tm查看是否有加减项,有的不能取消
                var data2 = from b in db.t_jjxbs where b.tm == tm select b;
                if (data2.Any())
                {
                    or.State = 0;
                    or.Message = "此客户存在加项信息，不能取消登记";
                    return or;
                }

                //select * from t_tempdjxm where tm=1000003015 and (ztz=1 or ztz=2 or ztz=3 or ztz=4) and 
                //zhbm not in (select zhbm from t_zhxm where jclb=2)
                var data = from a in db.t_tempdjxms
                           join b in db.t_zhxms on a.zhbm equals b.zhbm
                           where a.tm == tm && (a.ztz == 1 || a.ztz == 2 || a.ztz == 3 || a.ztz == 4) && b.jclb != 2
                           select a;
                if (data.Any())
                {
                    or.State = 0;
                    or.Message = "此客户已经有体检项目了，不能取消登记";
                    return or;
                }

                or.State = 1;
                or.Message = "可以取消登记";
                return or;
            }
        }

        /// <summary>
        /// 根据条码号进行团检的取消登记
        /// </summary>
        /// <param name="tm"></param>
        /// <returns></returns>
        public OperationResult CancleDJByTm(int tm)
        {
            using (DbEntities db = new DbEntities())
            {
                OperationResult or = new OperationResult();
                var tran = db.CreateTransaction();
                try
                {
                    //1.update t_ttgzb set tmztz=0,upflag=1 where tm=1000170820
                    string sql1 = "update t_ttgzb set tmztz=0,upflag=1 where tm=" + tm;
                    db.DataAccessor.Execute(sql1);
                    //2.
                    string sql2 = "delete from  t_tempdjxm where tm=" + tm;
                    db.DataAccessor.Execute(sql2);
                    tran.Commit();
                    or.State = 1;
                    or.Message = "取消登记成功";
                    return or;
                }
                catch (Exception ex)
                {
                    tran.Rollback();
                    or.State = 0;
                    or.Message = "取消登记失败，" + (ex.InnerException ?? ex).Message;
                    return or;
                }
            }
        }

        /// <summary>
        /// 团检分组中添加人员
        /// </summary>
        /// <param name="tg"></param>
        /// <returns></returns>
        public OperationResult InsertTtgzb(t_ttgzb tg)
        {
            OperationResult or = new OperationResult();
            using (DbEntities db = new DbEntities())
            {
                if (tg == null)
                {
                    or.State = 0;
                    or.Message = "参数不完整";
                    return or;
                }
                else
                {
                    try
                    {
                        BasicBll bb = new BasicBll();

                        int tm = bb.gettmh(tg.fddm, 1, 1);
                        if (tg.hyh == 0)
                            tg.hyh = bb.gethyh(tg.fddm, 1); ;
                        tg.tm = tm;
                        tg.tmztz = 0;
                        tg.dybz = 0;
                        tg.upflag = 0;
                        tg.cs = 1;
                        int htbm = tg.htbm;
                        //获取当前htbm对应的最大序号
                        int maxxh = 0;
                        var maxall = db.t_ttgzbs.Where(t => t.htbm == htbm).ToArray();
                        if (maxall.Length != 0)
                        {
                            maxxh = db.t_ttgzbs.Where(t => t.htbm == htbm).OrderByDescending(t => t.xh).Select(t => t.xh).First();
                        }
                        else
                        {
                            maxxh = 1;
                        }

                        if (maxxh != 0)
                        {
                            tg.xh = maxxh + 1;
                        }
                        else
                        {
                            tg.xh = 1;
                        }
                        db.t_ttgzbs.Insert(tg);
                        or.State = 1;
                        or.Message = "添加分组中的人员信息成功";
                        or.Tag = tg.hyh + "|" + tg.tm;
                        return or;
                    }
                    catch (Exception ex)
                    {
                        or.State = 0;
                        or.Message = (ex.InnerException ?? ex).Message;
                        return or;
                    }
                }
            }
        }

        /// <summary>
        /// 修改团检分组中的人员信息
        /// </summary>
        /// <param name="tg"></param>
        /// <returns></returns>
        public OperationResult UpdateTtgzb(t_ttgzb tg)
        {
            OperationResult or = new OperationResult();
            using (DbEntities db = new DbEntities())
            {
                try
                {
                    if (tg == null)
                    {
                        or.State = 0;
                        or.Message = "参数不完整";
                        return or;
                    }
                    string sql1 = "update t_ttgzb set sfzh='" + tg.sfzh + "',xm='" + tg.xm + "',hyzk=" + tg.hyzk + ",csrq='" + tg.csrq.ToString() + "',nl=" + tg.nl + ",ygh='" + tg.ygh + "'," +
                        "ssbm='" + tg.ssbm + "',bz='" + tg.bz + "',tel='" + tg.tel + "',image='" + tg.image + "'  where tm=" + tg.tm;
                    db.DataAccessor.Execute(sql1);
                    or.State = 1;
                    or.Message = "修改信息成功";
                    return or;
                }
                catch (Exception ex)
                {
                    or.State = 1;
                    or.Message = (ex.InnerException ?? ex).Message;
                    return or;
                }


            }
        }

        /// <summary>
        /// 更改个人信息
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public OperationResult UpdateGzbInfo(dto_tjdjxx dt)
        {
            using (DbEntities db = new DbEntities())
            {
                OperationResult or = new OperationResult();
                or.State = 0;
                if (string.IsNullOrEmpty(dt.xm))
                {
                    or.Message = "姓名不能为空";
                    return or;
                }
                if (dt.tm == 0 || dt.lx == 2)
                {
                    or.Message = "请求参数不完整，未找到条码信息";
                    return or;
                }
                int tm = dt.tm;
                //根据原来是否有套餐和加项信息 判断修改后的性别和婚姻状况是否可行
                //根据tm查询套餐和加项信息中的xb 和hyzk
                //男女互斥 已婚和未婚互斥
                //此处认为个人修改后的信息必须为正常的性别和婚姻状况即 非0则1
                short xb = dt.xb;
                short hyzk = dt.hyzk;
                bool ifxb = true;
                bool ifhyzk = true;
                if (xb == 2)
                {
                    ifxb = false;
                }
                if (hyzk == 2)
                {
                    ifhyzk = false;
                }
                if (!ifxb && !ifhyzk) //两个都不需要验证
                {

                }
                else
                {
                    if (dt.lx == 0)//个检
                    {
                        var data1 = (from a in db.t_grgzbs
                                     join b in db.t_tcs
                                     on a.tcbm equals b.tcbm
                                     where a.tm == tm
                                     select new
                                     {
                                         xb = b.xb,
                                         hyzk = b.hyzk
                                     }).ToList()
                                   ;

                        var data2 = (from a in db.t_jjxbs
                                     join b in db.t_zhxms
                                     on a.zhbm equals b.zhbm
                                     where
                                     a.tm == tm
                                     select new
                                     {
                                         xb = b.xb,
                                         hyzk = b.hyzk
                                     }).ToList();
                        if (ifxb) //性别需要验证
                        {
                            short xbtemp = 0;
                            if (xb == 0)
                            {
                                xbtemp = 1;
                            }
                            bool xb1 = data1.Where(t => t.xb == xbtemp).Any();
                            bool xb2 = data2.Where(t => t.xb == xbtemp).Any();
                            if (xb1 || xb2) //存在互斥信息
                            {
                                or.Message = "该人员存在与性别不符合的套餐项目或加项信息，性别修改失败";
                                return or;
                            }
                        }
                        if (ifhyzk) //婚姻状况需要验证
                        {
                            short hyzktemp = 0;
                            if (hyzk == 0)
                            {
                                hyzktemp = 1;
                            }
                            bool xb1 = data1.Where(t => t.hyzk == hyzktemp).Any();
                            bool xb2 = data2.Where(t => t.hyzk == hyzktemp).Any();
                            if (xb1 || xb2) //存在互斥信息
                            {
                                or.Message = "该人员存在与婚姻状况不符合的套餐项目或加项信息，婚姻状况修改失败";
                                return or;
                            }
                        }
                    }
                }
                //通过验证后修改信息
                var tran = db.CreateTransaction();
                try
                {
                    if (dt.lx == 0)//个检
                    {
                        var gr = db.t_grgzbs.FirstOrDefault(t => t.tm == dt.tm);
                        gr.xm = dt.xm;
                        gr.image = dt.image;
                        gr.xb = dt.xb;
                        gr.nl = (short)dt.nl;
                        gr.hyzk = dt.hyzk;
                        gr.csrq = dt.csrq.Date;
                        gr.sfzh = dt.sfzh;
                        gr.dh = dt.tel;
                        gr.dz = dt.txdz;
                        gr.dxtj = dt.dxtj;
                        gr.bz = dt.txdz;
                        gr.upflag = 1;
                        db.t_grgzbs.Update(gr);
                        //string sql1 = "update t_grgzb set xm='"+dt.xm+"',xb="+dt.xb+",nl="+dt.nl+",hyzk="+dt.hyzk+",csrq='"+dt.csrq.ToString("yyyy-MM-dd")+"',sfzh='"+dt.sfzh+"',dh='"+dt.tel+"',dz='"+dt.txdz+"',yb='',bz='"+dt.txdz+"',upflag=1,dxtj="+dt.dxtj+"  where tm="+dt.tm;
                        //db.DataAccessor.Execute(sql1);
                        string sql2 = "update t_hyb set hybz=1, xm='" + dt.xm + "',xb=" + dt.xb + ",nl=" + dt.nl + ",hyzk=" + dt.hyzk + " ,upflag=1 where hyh=" + dt.hyh;
                        db.DataAccessor.Execute(sql2);

                        string sql3 = "update pd_yssj set xm='" + dt.xm + "',txm='"+ dt.xm + "' where qtxtid=" + dt.tm;
                        db.DataAccessor.Execute(sql3);

                        string sql4 = "update pd_rylb set xm='" + dt.xm + "',txm='" + dt.xm + "' where qtxtid=" + dt.tm;
                        db.DataAccessor.Execute(sql4);

                        tran.Commit();
                        or.State = 1;
                        or.Message = "修改成功";
                        return or;
                    }
                    else
                    {
                        var tt = db.t_ttgzbs.FirstOrDefault(t => t.tm == dt.tm);
                        tt.xm = dt.xm;
                        tt.image = dt.image;
                        tt.xb = dt.xb;
                        tt.nl = (short)dt.nl;
                        tt.hyzk = dt.hyzk;
                        tt.csrq = dt.csrq.Date;
                        tt.sfzh = dt.sfzh;
                        tt.tel = dt.tel;
                        tt.ygh = dt.ygh;
                        tt.ssbm = dt.ssbm;
                        tt.bz = dt.txdz;
                        tt.upflag = 1;
                        db.t_ttgzbs.Update(tt);
                        //string sql1 = "update t_ttgzb set xm='" + dt.xm + "',xb=" + dt.xb + ",nl=" + dt.nl + ",hyzk=" + dt.hyzk
                        //   + ",csrq='" + dt.csrq.ToString("yyyy-MM-dd") + "',sfzh='" + dt.sfzh + "',tel='" + dt.tel + "',ygh='" + dt.ygh + "',ssbm='" + dt.ssbm + "',bz='" + dt.txdz + "',upflag=1 where tm=" + dt.tm;
                        //db.DataAccessor.Execute(sql1);
                        string sql2 = "update t_hyb set hybz=1, xm='" + dt.xm + "',xb=" + dt.xb + ",nl=" + dt.nl + ",tel1='" + dt.nl + "',hyzk=" + dt.hyzk + ",upflag=1 where hyh=" + dt.hyh;
                        db.DataAccessor.Execute(sql2);

                        string sql3 = "update pd_yssj set xm='" + dt.xm + "' where qtxtid=" + dt.tm;
                        db.DataAccessor.Execute(sql3);

                        string sql4 = "update pd_rylb set xm='" + dt.xm + "' where qtxtid=" + dt.tm;
                        db.DataAccessor.Execute(sql4);

                        tran.Commit();
                        or.State = 1;
                        or.Message = "修改成功";
                        return or;
                    }
                }
                catch (Exception ex)
                {
                    tran.Rollback();
                    or.State = 0;
                    or.Message = (ex.InnerException ?? ex).Message;
                    return or;
                }
            }
        }

    }
}