package com.yc.service.upload;

import com.yc.action.grid.GridUtils;
import com.yc.action.upload.AttachmentOA;
import com.yc.entity.attachment.AttachmentOAParam;
import com.yc.entity.attachment.AttachmentOaEntity;
import com.yc.service.BaseService;
import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.stereotype.Service;
import java.util.List;
import java.util.Map;

/**
 * @Author xinyb
 * @Date 15:32 2022/11/1
 **/
@Service("AttachmentOaImpl")
public class AttachmentOaImpl extends BaseService implements AttachmentOaIfc {
    @Override
    public List<Map<String, Object>> getAttachmentOAUserInfo(String unid, Integer seq, String keyword) {
        try {
            //hasView是null值的时候表示没添加过权限设置，统一返回1。后期设置0表示不能查看
            String sql = "set nocount on;\n" +
                    "declare @creatCode varchar(50),@unid varchar(200)="+GridUtils.prossSqlParm(unid)+",@seq int="+seq+"\n" +
                    "select @creatCode=isnull(a.AuthorCode,'') from _sys_Attachment a where a.UNID=@unid and a.seq=@seq\n" +
                    "if @@ROWCOUNT = 0\n" +
                    "begin\n" +
                    "select @creatCode=isnull(b.AuthorCode,'') from _sys_Attachment9 b where b.UNID=@unid and b.seq=@seq\n" +
                    "end\n" +
                    " select a.cccode,a.ccname,a.usercode,a.username,isnull(b.hasview,1) hasview,isnull(b.hasdownload,0) hasdownload,\n" +
                    " isnull(b.hasdelete,0) hasdelete,isnull(b.hasUpdate,0) hasupdate,b.startdate,b.enddate,b.dateflag, isnull(b.allowdownnums,0) allowdownnums,\n" +
                    " isnull(b.allowviewnums,0) allowviewnums,isnull(b.allowupdateNums,0) allowupdatenums, \n" +
                    " isnull(b.viewNums,0) viewnums,isnull(b.downNums,0) downnums,isnull(b.updateNums,0) updatenums, \n" +
                    " (case when a.UserCode=b.usercode then 1 else 0 end) checked ,(case when @creatCode=a.userCode then 1 else 0 end) hasAuthor \n" +
                    " from _sys_LoginUser a  left join _sys_AttachmentPermission b on a.UserCode=b.usercode\n" +
                    " and b.unid= @unid   and b.seq=@seq  where  a.inActive <> 1";
            if (StringUtils.isNotBlank(keyword)) {//查询条件
                sql += " and (a.ccname like '%" + keyword + "%' or a.username like '%" + keyword + "%')";
            }
            return jdbcTemplate.queryForList(sql);
        } catch (Exception e) {
            throw e;
        }
    }

    @Override
    public List<AttachmentOaEntity> getAttachmentOAUser(AttachmentOAParam param) {
        try {
            String sql="set nocount on;\n" +
                    "declare @authorCode varchar(50),@authorName varchar(50),@unid varchar(200)=  "+GridUtils.prossSqlParm(param.getUnid())+"  ," +
                    "@seq int="+param.getSeq()+",@return int=0,@cont int=0\n" +
                    "select @return=1,@authorCode=isnull(a.AuthorCode,''),@authorName=isnull(a.AuthorName,'') from _sys_Attachment a where a.UNID=@unid and a.seq=@seq\n" +
                    "if @return = 0\n" +
                    "begin\n" +
                    "select  @return=1,@authorCode=isnull(b.AuthorCode,''),@authorName=isnull(b.AuthorName,'') from _sys_Attachment9 b where b.UNID=@unid and b.seq=@seq\n" +
                    "end\n" +
                    "if @return=1\n" +
                    "begin\n" +
                    "select @cont=1 from _sys_AttachmentPermission  where unid=@unid and seq=@seq\n" +
                    "if @cont=1\n" +
                    "select unid,seq,userCode,userName,hasView,hasDownload,hasDelete,hasUpdate,startDate,endDate,dateFlag,AllowDownNums,AllowViewNums,AllowUpdateNums,\n" +
                    "isnull(viewNums,0) viewNums,isnull(downNums,0) downNums,isnull(updateNums,0) updateNums,case when usercode = @authorCode then 'true' else 'false' end as  hasAuthor\n" +
                    "from _sys_AttachmentPermission where unid=@unid and seq=@seq\n" +
                    "else\n" +
                    "select @unid as unid,@seq as seq,@authorCode as userCode,@authorName as userName,1 as hasView,1 as hasDownload,1 as hasDelete,1 as hasUpdate,2 as dateFlag,1 as hasAuthor\n" +
                    "end\n" +
                    "else\n" +
                    "raiserror('unid为：%s的附件不存在，请检查',12,1,@unid)\n" +
                    "return";
            return jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(AttachmentOaEntity.class));
        }catch (Exception e){
            throw e;
        }
    }

    @Override
    public Integer setAttachmentOAUser(AttachmentOAParam param) {
        try {
            List<AttachmentOaEntity> oaEntity = param.getOaEntities();
            String sql = "set nocount on ; \n" +
                    " declare @unid varchar(100) =" + GridUtils.prossSqlParm(param.getUnid()) + "," +
                    " @seq int =" + param.getSeq() + "\n";
            sql += " delete a from _sys_AttachmentPermission a where a.unid=@unid and a.seq=@seq\n" ;
            for (AttachmentOaEntity entity : oaEntity) {
                sql += "insert into _sys_AttachmentPermission(uNid,seq,userCode,username,hasView,hasDownload,hasDelete,hasUpdate,startDate," +
                        "endDate,dateFlag,AllowDownNums,AllowViewNums,AllowUpdateNums,downNums,viewNums,updateNums)";
                sql += " values (@unid,@seq," + GridUtils.prossSqlParm(entity.getUserCode()) + "," + GridUtils.prossSqlParm(entity.getUserName()) + "," +
                        (entity.isHasView() ? 1 : 0) + "," + (entity.isHasDownload() ? 1 : 0) + "," + (entity.isHasDelete() ? 1 : 0) + "," +
                        (entity.isHasUpdate() ? 1 : 0)+","+
                        (StringUtils.isNotBlank(entity.getStartDate()) ? "convert(varchar(19),'" + entity.getStartDate() + "',120)" : null) + "," +
                        (StringUtils.isNotBlank(entity.getEndDate())  ? "convert(varchar(19),'" + entity.getEndDate() + "',120)" : null) + "," +
                        entity.getDateFlag()+"," + entity.getAllowDownNums() + "," + entity.getAllowViewNums() + ","+entity.getAllowUpdateNums()+"," +
                         entity.getDownNums()+","+entity.getViewNums()+","+entity.getUpdateNums()+") \n";
            }
            Integer cont = jdbcTemplate.update(sql);
            return cont;
        } catch (Exception e) {
            throw e;
        }
    }

    @Override
    public String setAttachmentUser(AttachmentOaEntity param) {
        try {
            String sql="declare @unid varchar(200)="+GridUtils.prossSqlParm(param.getUnid())+",@seq int= "+param.getSeq()+"," +
                    "@userCode varchar(50)="+GridUtils.prossSqlParm(param.getUserCode())+"," +
                    "@userName varchar(50)="+GridUtils.prossSqlParm(param.getUserName())+",@demo varchar(100),@reutn bit\n" +
                    "select @reutn=1 from _sys_AttachmentPermission where unid=@unid and seq=@seq and usercode=@userCode\n" +
                    "if @reutn =1\n" +
                    "begin\n" +
                    " update a set hasView="+(param.isHasView()?"1":"0")+",hasDownload="+(param.isHasDownload()?"1":"0")+"," +
                    " hasdelete="+(param.isHasDelete()?"1":"0")+",hasUpdate="+(param.isHasUpdate()?"1":"0")+"," +
                    "startDate="+(StringUtils.isNotBlank(param.getStartDate()) ? "convert(varchar(19),'" + param.getStartDate() + "',120)" : null) +"," +
                    "endDate="+(StringUtils.isNotBlank(param.getEndDate()) ? "convert(varchar(19),'" + param.getEndDate() + "',120)" : null) +"," +
                    "dateFlag="+param.getDateFlag()+",AllowDownNums="+param.getAllowDownNums()+",AllowViewNums="+param.getAllowViewNums()+"," +
                    "AllowUpdateNums="+param.getAllowUpdateNums()+" from _sys_AttachmentPermission a  where unid=@unid and seq=@seq and usercode=@userCode\n" +
                    " if @@ROWCOUNT >0\n" +
                    " begin\n" +
                    " set @demo='修改完成！'\n" +
                    " end else\n" +
                    " begin\n" +
                    " set @demo='修改失败！检查修改的用户是否存在'\n" +
                    " end\n" +
                    "end else\n" +
                    "begin\n" +
                    "  insert into _sys_AttachmentPermission(unid,seq,usercode,username,hasView,hasDownload,hasdelete,hasUpdate,startDate,endDate,dateFlag,AllowDownNums,AllowViewNums,AllowUpdateNums)\n" +
                    "  values(@unid,@seq,@userCode,@userName,"+(param.isHasView()?"1":"0")+","+(param.isHasDownload()?"1":"0")+","+(param.isHasDelete()?"1":"0")+"" +
                    "  ,"+(param.isHasUpdate()?"1":"0")+","+(StringUtils.isNotBlank(param.getStartDate()) ? "convert(varchar(19),'" + param.getStartDate() + "',120)" : null)+"" +
                    "  ,"+(StringUtils.isNotBlank(param.getEndDate()) ? "convert(varchar(19),'" + param.getEndDate() + "',120)" : null)+","+param.getDateFlag()+"" +
                    "  ,"+param.getAllowDownNums()+","+param.getAllowViewNums()+","+param.getAllowUpdateNums()+")\n" +
                    "  set @demo='添加成功！'\n" +
                    "end\n" +
                    "select @demo as demo";
            return jdbcTemplate.queryForObject(sql,String.class);
        }catch (Exception e){
            throw e;
        }
    }

    @Override
    public Map<String, Object> getAttachmentOAInfo(AttachmentOAParam param) {
        try {
            String sql="declare @date datetime=CONVERT(varchar(100), GETDATE(), 12), @unid varchar(100)= "+GridUtils.prossSqlParm(param.getUnid())+" ," +
                    " @seq int ="+param.getSeq()+", @userCode varchar(50)= "+GridUtils.prossSqlParm(param.getUserCode())+" \n" +
                    " declare @hasView int,@hasDownload int,@hasDelete int,@hasUpdate int ,@startDate datetime ,@endDate datetime,@reutrn bit,@author varchar(50)\n" +
                    " select  @author=AuthorCode from _sys_Attachment where unid=@unid and seq=@seq\n" +
                    " if @author is null\n" +
                    " begin\n" +
                    " select @author=AuthorCode from _sys_Attachment9 where unid=@unid and seq=@seq\n" +
                    " end\n" +
                    " if @author <> @userCode\n" +
                    " begin\n" +
                    " select @hasView=isnull(a.hasView,1),@hasDownload=isnull(a.hasDownload,0),@hasDelete=isnull(a.hasdelete,0),@hasUpdate=isnull(a.hasUpdate,0),\n" +
                    " @startDate=a.startDate,@endDate=a.endDate from _sys_AttachmentPermission a \n" +
                    " where  a.unid=@unid and seq=@seq and a.usercode=@userCode\n" +
                    " if @@ROWCOUNT = 1\n" +
                    "  begin\n" +
                    "    if @startDate is null set @startDate=@date\n" +
                    "    if @endDate is null set @endDate=@date\n" +
                    "    select @reutrn =1 from _sys_AttachmentPermission a \n" +
                    "      where  (@date between @startDate and @endDate) and a.unid=@unid and seq=@seq and a.usercode=@userCode\n" +
                    "    if @reutrn is null\n" +
                    "     begin\n" +
                    "      set @hasView=0\n" +
                    "      set @hasDownload=0\n" +
                    "      set @hasDelete=0\n" +
                    "      set @hasUpdate=0\n" +
                    "     end\n" +
                    "     select (case when @hasView=1  then 'true' else 'false' end) as hasView,\n" +
                    "     (case when @hasDownload=1 then 'true' else 'false' end) as hasDownload,\n" +
                    "     (case when @hasDelete=1 then 'true' else 'false' end) as hasDelete,\n" +
                    "     (case when @hasUpdate=1 then 'true' else 'false' end) as hasUpdate\n" +
                    "  end\n" +
                    "else\n" +
                    "  begin\n" +
                    "    select 'true'  as hasView,'false' as hasDownload,'false' as hasDelete,'false' as hasUpdate \n" +
                    "  end\n" +
                    " end\n" +
                    " else begin\n" +
                    "     select 'true'  as hasView,'true' as hasDownload,'true' as hasDelete,'true' as hasUpdate \n" +
                    " end";
            return jdbcTemplate.queryForMap(sql);
        }catch (Exception e){
            throw e;
        }
    }

    @Override
    public String getAttachmentAllow(AttachmentOAParam param) {
        try {
            String sql="declare @type varchar(20) = "+GridUtils.prossSqlParm(param.getType())+", " +
                    "@unid varchar(200) =   "+GridUtils.prossSqlParm(param.getUnid())+", " +
                    "@seq int = "+param.getSeq()+" ,@userCode varchar(50) =  "+GridUtils.prossSqlParm(param.getUserCode())+"    \n" +
                    "declare @viewNums int = 0,@downNums int = 0,@updateNums int = 0 ,@allowViewNums int = 0,@allowDownNums int = 0,@allowUpdateNums int = 0\n" +
                    "declare @memo varchar(100)='',@fileName varchar(200),@authorCode varchar(50),@isTable9 int = 0,@cont bit\n" +
                    "declare @totaViewNums int,@totaDownNums int ,@totaUpdateNums int\n" +
                    "select @totaViewNums=a.viewNums,@totaDownNums=a.DownNums,@totaUpdateNums=a.UpdateNums,@fileName=a.OriginalFileName,@authorCode=a.AuthorCode from _sys_Attachment a where a.UNID=@unid and a.seq=@seq     \n" +
                    "if @@ROWCOUNT = 0\n" +
                    "\tbegin\n" +
                    "\t\tselect @isTable9=1,@totaViewNums=a.viewNums,@totaDownNums=a.DownNums,@totaUpdateNums=a.UpdateNums,@fileName=a.OriginalFileName,@authorCode=a.AuthorCode from _sys_Attachment9 a where a.UNID=@unid and a.seq=@seq\n" +
                    "    end\n" +
                    "set @totaViewNums=isnull(@totaViewNums,0) \n" +
                    "set @totaDownNums=isnull(@totaDownNums,0) \n" +
                    "set @totaUpdateNums=isnull(@totaUpdateNums,0) \n" +
                    "select @allowViewNums=isnull(a.AllowViewNums,0),@allowDownNums=isnull(a.AllowDownNums,0),@allowUpdateNums=isnull(a.AllowUpdateNums,0),\n" +
                    "@viewNums=isnull(a.viewNums,0),@downNums=isnull(a.downNums,0),@updateNums=isnull(a.UpdateNums,0)\n" +
                    "from _sys_AttachmentPermission a where a.unid=@unid and a.seq=@seq and a.usercode=@userCode \n" +
                    "if @@ROWCOUNT > 0 or @authorCode = @userCode\n" +
                    "   begin\n" +
                    "     if @type='view'\n" +
                    "        begin\n" +
                    "             if  @authorCode <> @userCode and @allowViewNums <> 0 and @allowViewNums <= @viewNums \n" +
                    "                begin\n" +
                    "\t\t\t\t\t set @cont=0\n" +
                    "\t\t\t\t\t set @memo='你在此附件允许查看的次数已经用完;-1'\n" +
                    "                end\n" +
                    "             else\n" +
                    "                begin\n" +
                    "                    set @viewNums = @viewNums + 1\n" +
                    "                    update a set a.allowViewNums=@allowViewNums,a.ViewNums=@viewNums from _sys_AttachmentPermission a where a.unid=@unid and a.seq=@seq and a.usercode=@userCode                 \n" +
                    "\t\t\t\t\tset @cont=1\n" +
                    "\t\t\t\t\tset @memo='已执行查看次数;0'\n" +
                    "                end\t\t\t \n" +
                    "        end\n" +
                    "     else if @type='down'\n" +
                    "        begin\n" +
                    "             if @authorCode <> @userCode and @allowDownNums <> 0 and @allowDownNums <= @downNums \n" +
                    "                begin\n" +
                    "\t\t\t\t\t set @cont=0\n" +
                    "\t\t\t\t\t set @memo='你在此附件允许下载的次数已经用完;-1'\n" +
                    "                end\n" +
                    "             else\n" +
                    "                begin\n" +
                    "                     set @downNums = @downNums + 1\n" +
                    "                     update a set a.AllowDownNums=@allowDownNums,a.downNums=@downNums from _sys_AttachmentPermission a where a.unid=@unid and a.seq=@seq and a.usercode=@userCode\n" +
                    "\t\t\t\t\t set @cont=1\n" +
                    "\t\t\t\t\t set @memo='已执行下载次数;0'\n" +
                    "\t\t\t\t\t\n" +
                    "                end\t\t\t \n" +
                    "        end\n" +
                    "     else if @type='update'\n" +
                    "\t    begin\n" +
                    "\t\t    if @authorCode <> @userCode and @allowUpdateNums <> 0 and @allowDownNums <= @updateNums \n" +
                    "                begin\n" +
                    "\t\t\t\t\t set @cont=0\n" +
                    "\t\t\t\t\t set @memo='你在此附件允许更新的次数已经用完;-1'\n" +
                    "                end\n" +
                    "             else\n" +
                    "                begin\n" +
                    "                     set @updateNums = @updateNums + 1\n" +
                    "                     update a set a.AllowUpdateNums=@allowUpdateNums,a.UpdateNums=@updateNums from _sys_AttachmentPermission a where a.unid=@unid and a.seq=@seq and a.usercode=@userCode\n" +
                    "\t\t\t\t\t set @cont=1\n" +
                    "\t\t\t\t\t set @memo='已执行更新次数;0'\n" +
                    "                end\t\t\n" +
                    "\t    end\n" +
                    "     else\n" +
                    "        begin\n" +
                    "             set @memo='错误操作类型;-1;0;0;0;0;0;0;'';0'\n" +
                    "        end  \n" +
                    "  end\n" +
                    "else\n" +
                    "   begin\n" +
                    "      set @memo='此用户未设置权限;0'\n" +
                    "\t  set @cont=1\n" +
                    "   end\n" +
                    "---更新附件表里的数量\n" +
                    "if @cont is not null\n" +
                    " begin\n" +
                    "  set @memo=@memo+';'+convert(varchar(20),@viewNums)+';'+convert(varchar(20),@downNums)+';'+convert(varchar(20),@updateNums)\n" +
                    "  if @cont = 1\n" +
                    "   begin\n" +
                    "    if @type='view'\n" +
                    "       begin\n" +
                    "          set @totaViewNums = @totaViewNums + 1\n" +
                    "   \t\t  if @isTable9 = 1\n" +
                    "\t\t    begin\n" +
                    "\t\t\t\t update b set b.ViewNums=@totaViewNums from _sys_Attachment9 b where b.unid=@unid and b.seq=@seq \n" +
                    "\t\t    end\n" +
                    "\t\t  else\n" +
                    "\t\t    begin\n" +
                    "\t\t\t\t update b set b.ViewNums=@totaViewNums from _sys_Attachment b where b.unid=@unid and b.seq=@seq \n" +
                    "\t\t\tend  \n" +
                    "       end\n" +
                    "    else if @type='down'\n" +
                    "       begin\n" +
                    "          set @totaDownNums = @totaDownNums + 1\n" +
                    "\t      if @isTable9 = 1\n" +
                    "\t\t     begin\n" +
                    "\t\t\t  update b set b.DownNums=@totaDownNums from _sys_Attachment9 b where b.unid=@unid and b.seq=@seq \n" +
                    "\t\t     end\n" +
                    "\t      else\n" +
                    "\t\t     begin\n" +
                    "\t\t      update b set b.DownNums=@totaDownNums from _sys_Attachment b where b.unid=@unid and b.seq=@seq \n" +
                    "\t         end  \n" +
                    "       end\n" +
                    "    else if @type='update'\n" +
                    "       begin\n" +
                    "          set @totaUpdateNums = @totaUpdateNums + 1\n" +
                    "\t      if @isTable9 = 1\n" +
                    "\t\t     begin\n" +
                    "\t\t\t  update b set b.UpdateNums=@totaUpdateNums from _sys_Attachment9 b where b.unid=@unid and b.seq=@seq \n" +
                    "\t\t     end\n" +
                    "\t      else\n" +
                    "\t\t     begin\n" +
                    "\t\t      update b set b.UpdateNums=@totaUpdateNums from _sys_Attachment b where b.unid=@unid and b.seq=@seq \n" +
                    "\t         end\n" +
                    "       end\n" +
                    "   end\n" +
                    "  set @memo=@memo+';'+convert (varchar(20),@totaViewnums)+';'+convert (varchar(20),@totaDownNums)+';'+convert (varchar(20),@totaUpdateNums)+';'+@fileName+';'+convert(varchar(20),@seq)\n" +
                    " end\n" +
                    "select @memo as memo";
            return jdbcTemplate.queryForObject(sql,String.class);
        }catch (Exception e){
            throw e;
        }
    }

    @Override
    public String getAttachmentDelete(AttachmentOAParam param) {
        try {
            String sql="declare @unid varchar(200)="+GridUtils.prossSqlParm(param.getUnid())+",@seq int= "+param.getSeq()+"," +
                    "@userCode varchar(50)="+GridUtils.prossSqlParm(param.getUserCode())+",@demo varchar(100),@reutn bit\n" +
                    "select @reutn=1 from _sys_AttachmentPermission where unid=@unid and seq=@seq and usercode=@userCode\n" +
                    "if @reutn =1\n" +
                    "begin\n" +
                    " delete a from _sys_AttachmentPermission a where unid=@unid and seq=@seq and usercode=@userCode\n" +
                    " if @@ROWCOUNT >0\n" +
                    " begin\n" +
                    " set @demo='删除完成！'\n" +
                    " end else\n" +
                    " begin\n" +
                    " set @demo='删除失败！检查删除的用户是否存在'\n" +
                    " end\n" +
                    "end else\n" +
                    "begin\n" +
                    " set @demo='当前用户'+@userCode+'未在权限内，请检查！'\n" +
                    "end\n" +
                    "select @demo as demo";
            return  jdbcTemplate.queryForObject(sql,String.class);
        }catch (Exception e){
            throw e;
        }
    }
}