ZLPMS/PMS.DBService/SmartForm/SmartFormService.cs

using PMS.BusinessModels.SmartForm;
using PMS.EntityModels.SmartForm;
using PMS.Plugins.Common;
using QWPlatform.IService;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace PMS.DBService.SmartForm
{
    public class SmartFormService : DataServiceBase
    {
        protected override void DBFctory(string conName)
        {
            base.DBFctory(conName);
        }

        /// <summary>
        /// 添加和修改分类
        /// </summary>
        /// <param name="type">1新增，2修改</param>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool AddCategory(int type, ZNBDFLModel model)
        {
          
            if (type == 1)
            {
                model.ID = Guid.NewGuid().ToString();
                return this.InsertBuilder.Column("ID", model.ID)
                        .Column("上级ID", model.上级ID)
                        .Column("序号", model.序号)
                        .Column("名称", model.名称)
                        .Column("应用性质", model.应用性质)
                        .Column("应用范围", model.应用范围)
                        .Insert("智能表单分类")
                        .Execute()>0;

            }
            else
            {
                return this.UpdateBuilder
                        .Column("上级ID", model.上级ID)
                        .Column("序号", model.序号)
                        .Column("名称", model.名称)
                        .Column("应用性质", model.应用性质)
                        .Column("应用范围", model.应用范围)
                        .Update("智能表单分类")
                        .Where("id",model.ID)
                        .Execute()>0;
            }

        }
        public bool DelCategory(ZNBDFLModel model, ref string msg)
        {

          
            var number = this.SqlBuilder.SqlText("select count(1) from 智能表单模板 t where t.分类id=:FLID").Parameters("FLID",model.ID).Select<int>();
            if(number>0)
            {
                msg = "当前分类还有表单,不允许删除!";
                return false;
            }
            number = this.SqlBuilder.SqlText("select count(1) from 智能表单分类 t where t.上级ID=:FLID").Parameters("FLID", model.ID).Select<int>();
            if (number > 0)
            {
                msg = "当前分类还有下级分类,不允许删除!";
                return false;
            }
            var state = this.DeleteBulider
                      .Delete("智能表单分类")
                      .Where("id", model.ID)
                      .Execute()>0;
            return state;

        }
        /// <summary>
        /// 获取所有分类
        /// </summary>
        /// <returns></returns>
        public List<TreeStruct> GetCategory( string id)
        {
            List<TreeStruct> treeStructs = new List<TreeStruct>();
            var dt=new DataTable();
            if (id!=null)
            {
                dt = this.SqlBuilder.SqlText("select id,上级id as sjid, 名称 as text from 智能表单分类 t where 上级ID=:id ").Parameters("id",id).Select();
            }
            else
            {
                dt = this.SqlBuilder.SqlText("select id,上级id as sjid, 名称 as text from 智能表单分类 t ").Select();
            }
           
            if(dt.Rows.Count>0)
            {
                treeStructs = Tools.ConvertToList<TreeStruct>(dt);
            }
            return treeStructs;

        }


        #region 表单
        /// <summary>
        /// 增加数据记录【智能表单信息】
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int Add(ZNBCXXModel model)
        {
            model.ID = Guid.NewGuid().ToString();
            model.JLSJ = this.DataBaseBuilder.GetDateTime;
            model.ZT = 1;
            model.SetDataFactory(this.DataFactoryObject);
            return model.Insert();
        }
        /// <summary>
        /// 更新数据库记录【智能表单信息】
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int UpdateSmartForm(ZNBCXXModel model)
        {
            return this.UpdateBuilder.Update("智能表单信息").Column("分类ID", model.FLID).Column("名称", model.MC).Column("标题", model.BT)
                .Column("填写提示", model.TXTS).Column("填写限制", model.TXXZ).Column("处理模式", model.CLMS)
                .Column("备注", model.BZ).Column("状态", model.ZT).Column("是否提醒", model.SFTX)
                .Column("邮件推送", model.YJTS).Column("提醒号码", model.TXHM).Column("审核提醒", model.SHTX).Column("是否包含附件", model.SHBHFJ)
                .Column("审核邮件推送", model.SHYJTS).Column("审核提醒号码", model.SHTXHM).Where("ID", model.ID).Execute();
        }

        /// <summary>
        /// 删除数据记录【智能表单信息】
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int Delete(ZNBCXXModel model)
        {
            model.SetDataFactory(this.DataFactoryObject);

            return model.Delete("ID");
        }

        /// <summary>
        /// 查询数据对象并进行赋值【查询智能表单信息】
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public ZNBCXX BdxxSelect(string id)
        {
            ZNBCXX ListModel = new ZNBCXX();
            var dt = this.SqlBuilder.SqlText(@"select  j.id,j.分类id as FLID,j.名称 as MC,j.标题 as BT,j.填写提示 as TXTS,j.填写限制 as TXXZ ,j.处理模式 as CLMS
  , j.备注 as BZ , j.建立时间 as JLSJ ,
  m.表单内容 as BDNR,m.表单处理内容 as BDCLNR,
   m.更新时间,j.状态 as ZT ,j.提醒号码 as TXHM  , j.邮件推送 as YJTS ,j.是否提醒 SFTX, j.审核提醒 as SHTX  , j.审核提醒号码 as SHTXHM ,j.审核邮件推送 SHYJTS,j.是否包含附件 SHBHFJ,m.id as BDMBID
  from 智能表单信息 j ,智能表单模板  m where j.id=:id and   m.表单id=j.id   order by m.版本号 desc ").Parameters("id", id).Select();
            if (dt.Rows.Count > 0)
            {
                ListModel = Tools.ConvertToList<ZNBCXX>(dt).FirstOrDefault();
            }
            return ListModel;
        }
        /// <summary>
        /// 列表查询
        /// </summary>
        /// <returns></returns>
        public List<ZNBCXXModel> BdxxSelectList(ZNBCXXModel model)
        {
            //SQL查询参数
            List<IDataParameter> listDP = new List<IDataParameter>();
            var dataSqlWhere = "";
            if (model.FLID!="0")
            {
                dataSqlWhere += " j.分类id =:FLID";
                listDP.Add(new SqlParameter("FLID", model.FLID));
            }
            else
            {
                dataSqlWhere += " j.状态 =1";
               
            }

            List<ZNBCXXModel> ListModel = new List<ZNBCXXModel>();
            var dt = this.SqlBuilder.SqlText(@"select  j.id,j.分类id as FLID,j.名称 as MC,j.标题 as BT,j.填写提示 as TXTS,j.填写限制 as TXXZ ,j.处理模式 as CLMS
  , j.备注 as BZ , j.提醒号码 as TXHM  , j.邮件推送 as YJTS  , j.建立时间 as JLSJ , j.状态 as ZT from 智能表单信息 j where " + dataSqlWhere + "  order by  j.建立时间 asc ").Parameters(listDP.ToArray()).Select();
            if (dt.Rows.Count > 0)
            {
                ListModel = Tools.ConvertToList<ZNBCXXModel>(dt);
            }
            return ListModel;
          
        }



        



        /// <summary>
        /// 增加数据记录【智能表单模板】
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int Add(ZNBCMBModel model)
        {
            var number = this.SqlBuilder.SqlText("select nvl( max( 版本号),0)+1   from 智能表单模板 t  where t.表单ID=:BDID").Parameters("BDID", model.BDID).Select<int>();
            model.BBH = number;
            model.ID = Guid.NewGuid().ToString();
            model.GXSJ = this.DataBaseBuilder.GetDateTime;
            model.SetDataFactory(this.DataFactoryObject);
            return model.Insert();
        }

 


        /// <summary>
        /// 删除数据记录【智能表单模板】
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int Delete(ZNBCMBModel model)
        {
            model.SetDataFactory(this.DataFactoryObject);

            return model.Delete("ID");
        }


        /// <summary>
        /// 查询数据对象并进行赋值【智能表单模板】
        /// </summary>
        /// <returns></returns>
        public ZNBCMBModel Select(ZNBCMBModel model)
        {
            model.SetDataFactory(this.DataFactoryObject);
            model.Select();
            return model;
        }

        /// <summary>
        /// 列表查询【智能表单模板】
        /// </summary>
        /// <returns></returns>
        public List<ZNBCMBModel> SelectList(ZNBCMBModel model)
        {
            model.SetDataFactory(this.DataFactoryObject);
            return model.SelectList<ZNBCMBModel>();
        }

        #endregion


        #region 表单填写记录

        /// <summary>
        /// 列表查询【表单填写记录】
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public List<BDTXJL> SelectRecordList(SelectBDTXJLModel model, string classID )
        {
            int total = 0;
            List<BDTXJL> ListModel = new List<BDTXJL>();
            List<IDataParameter> listDP = new List<IDataParameter>();
            var dataSqlWhere = "";
            if (!model.BDXXID.IsNullOrEmpty())
            {
                dataSqlWhere += " and t.表单信息ID =:BDXXID";
                listDP.Add(new SqlParameter("BDXXID", model.BDXXID));
            }
            if (!model.QDID.IsNullOrEmpty())
            {
                dataSqlWhere += " and t.渠道ID =:QDID";
                listDP.Add(new SqlParameter("QDID", model.QDID));
            }
            if (!model.XMID.IsNullOrEmpty())
            {
                dataSqlWhere += " and t.项目ID =:XMID";
                listDP.Add(new SqlParameter("XMID", model.XMID));
            }
            if (!model.BZ.IsNullOrEmpty())
            {
                model.BZ= model.BZ.TrimEnd(',');
                dataSqlWhere += " and t.填报状态  in (select * from table(f_split_string(:BZ))) ";
                listDP.Add(new SqlParameter("BZ", string.Join(",", model.BZ)));

            }
            if(classID=="1")
            {
                dataSqlWhere += " and j.分类ID  =:分类ID";
                listDP.Add(new SqlParameter("分类ID", "1"));
                
            }
            if (model.TBSJ_START != null)
            {
                dataSqlWhere += " and t.填报时间 BETWEEN  :start_date   AND  to_date(:end_date ,'yyyy-mm-dd hh24:mi:ss')     ";
                listDP.Add(new SqlParameter("start_date", model.TBSJ_START));
                listDP.Add(new SqlParameter("end_date", model.TBSJ_END + " 23:59:59"));



            }
           
            
            var dt = this.SqlBuilder.SqlText(@"select t.id,
t.表单名 as  BDM,
t.表单结果 as BDJG,
t.表单处理结果 as BDCLJG,
t.表单内容 as BDNR,
t.表单处理内容 as BDCLNR,
t.填报状态 as TBZT,
t.填报人 as TBR,
t.填报时间 as TBSJ,
t.审核人 as  SHR,
t.审核时间 as  SHSJ,
t.渠道名称 as  QDMC,
t.项目名称 as XMMC,
t.表单模板ID as BDMBID,
t.表单信息id as BDXXID，
j.是否包含附件 SHBHFJ,
t.备注 as BZ
 from 表单填写记录 t ,智能表单信息 j where    j.id=t.表单信息id " + dataSqlWhere +  "  order by t.填报时间 desc ").Parameters(listDP.ToArray())
       .Paging(model.page, model.rows).Select(out total);
            model.total = total;
            if (dt.Rows.Count > 0)
            {
                ListModel = Tools.ConvertToList<BDTXJL>(dt);
            }
            return ListModel;
        }
        /// <summary>
        /// 填写表单
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool AddRecord(BDTXJLModel model)
        {
            model.ID = Guid.NewGuid().ToString();
            model.TBSJ = this.DataBaseBuilder.GetDateTime;
            model.SetDataFactory(this.DataFactoryObject);
            return model.Insert()>0;
        }
        /// <summary>
        /// 根据手机号获取系统账号信息
        /// </summary>
        /// <param name="tel"></param>
        /// <returns></returns>
        public DataTable GetPushAccountInfo(string tel)
        {
            var dt = this.SqlBuilder.SqlText(@"select * from  系统账户 where  账户  in (select * from table(f_split_string(:tel)))").Parameters("tel", string.Join(",", tel)).Select();
            return dt;
        }
        /// <summary>
        /// 根据人员ID获取系统账户的信息
        /// </summary>
        /// <returns></returns>
        public DataTable GetSysAccountInfo(string id)
        {
            string sql = "select * from  系统账户 where 人员ID=:id";
            DataTable dt = SqlBuilder.SqlText(sql).Parameters("id", id).Select();
            return dt;
        }

        /// <summary>
        /// 更新表单记录
        /// </summary>
        /// <param name="model"></param>
        /// <param name="type">1：更新处理结果，2更新通过、退回状态，3修改填写记录,4删除5暂存</param>
        /// <returns></returns>
        public bool UpdateRecord(BDTXJLModel model,int  type)
        {
            var state = false;
       
            if(type== 1)
            {
                model.SHSJ = this.DataBaseBuilder.GetDateTime;
                state = this.UpdateBuilder.Update("表单填写记录").Column("表单处理结果", model.BDCLJG).Column("审核人ID", model.SHRID).Column("审核人", model.SHR).Column("审核时间", model.SHSJ).Where("ID", model.ID).Execute()>0;
            }
           else if (type == 2)
            {
                model.SHSJ = this.DataBaseBuilder.GetDateTime;
                if(model.TBZT==1)
                {
                    state = this.UpdateBuilder.Update("表单填写记录").Column("填报状态", model.TBZT).Column("审核人ID", model.SHRID).Column("审核人", model.SHR).Column("审核时间", model.SHSJ).Where("ID", model.ID).Execute() > 0;

                }
                else if (model.TBZT == 2)
                {
                    state = this.UpdateBuilder.Update("表单填写记录").Column("填报状态", model.TBZT).Column("审核人ID","").Column("审核人", "").Column("审核时间", null).Where("ID", model.ID).Execute() > 0;

                }
            }
            else if (type == 3)
            {
                model.TBSJ = this.DataBaseBuilder.GetDateTime;
                model.TBZT = 0;
               state = this.UpdateBuilder.Update("表单填写记录").Column("表单结果", model.BDJG).Column("填报状态", model.TBZT).Column("填报时间", model.TBSJ).Where("ID", model.ID).Execute() > 0;
            }
            else if (type == 4)
            {
                state = this.DeleteBulider.Delete("表单填写记录").Where("ID", model.ID).Execute() > 0;
            }
            else if (type == 5)
            {
                model.TBSJ = this.DataBaseBuilder.GetDateTime;
                model.TBZT = 3;
                state = this.UpdateBuilder.Update("表单填写记录").Column("表单结果", model.BDJG).Column("填报状态", model.TBZT).Column("填报时间", model.TBSJ).Where("ID", model.ID).Execute() > 0;
            }
            return state;


        }

        /// <summary>
        /// 查询我的填报记录
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public List<BDTXJL> MyRecordList(BDTXJLModel model)
        {
            List<IDataParameter> listDP = new List<IDataParameter>();
            var dataSqlWhere = " 1=1 ";
      
            if (!model.TBRID.IsNullOrEmpty())
            {
                dataSqlWhere += " and t.填报人ID=:TBRID ";
                listDP.Add(new SqlParameter("TBRID", model.TBRID));
            }

            if (!model.BDM.IsNullOrEmpty())
            {
                dataSqlWhere += " and (  t.表单名 like  '%'||:BDM||'%'  or t.项目名称  like  '%'||:BDM||'%'   )";
                listDP.Add(new SqlParameter("BDM", model.BDM));
            }
            if (!model.ID.IsNullOrEmpty())
            {
                dataSqlWhere += " and   t. ID=:ID";
                listDP.Add(new SqlParameter("ID", model.ID));
            }

            List<BDTXJL> ListModel = new List<BDTXJL>();
            var dt = this.SqlBuilder.SqlText(@"select t.id,
t.表单名 as  BDM,
t.表单结果 as BDJG,
t.表单处理结果 as BDCLJG,
t.表单内容 as BDNR,
t.表单处理内容 as BDCLNR,
t.填报状态 as TBZT,
t.填报人 as TBR,
t.填报人ID as TBRID,
t.填报时间 as TBSJ,
t.审核人 as  SHR,
t.审核时间 as  SHSJ,
t.渠道名称 as  QDMC,
t.项目名称 as XMMC,
t.表单模板ID as BDMBID,
t.表单信息id as BDXXID，
t.备注 as BZ
 from 表单填写记录 t where " + dataSqlWhere + "  order by t.填报时间 desc ").Parameters(listDP.ToArray()).Select();
            if (dt.Rows.Count > 0)
            {
                ListModel = Tools.ConvertToList<BDTXJL>(dt);
            }
            return ListModel;
        }

        #endregion
    }
}