package com.cnd3b.restcontroller.customer.wechatapplet; import com.alibaba.fastjson.JSONArray; import com.alibaba.fastjson.JSONObject; import com.aliyun.oss.HttpMethod; import com.aliyun.oss.model.GeneratePresignedUrlRequest; import com.aliyun.oss.model.ListObjectsRequest; import com.aliyun.oss.model.OSSObjectSummary; import com.aliyun.oss.model.ObjectListing; import com.cnd3b.common.Controller; import com.cnd3b.common.data.Row; import com.cnd3b.common.data.Rows; import com.cnd3b.common.data.RowsMap; import com.cnd3b.common.data.SQLFactory; import com.cnd3b.common.parameter.parameter; import p2.common.parse.A; import p2.common.parse.S; import p2.pao.PaoRemote; import p2.pao.PaoSetRemote; import p2.pao.R; import p2.util.P2Exception; import java.net.URI; import java.net.URL; import java.net.URLEncoder; import java.sql.Date; import java.util.*; import static com.cnd3b.utility.aliyun.oss.AliyunOSSUtil.*; public class wechatapplet extends Controller { // String url = BUCKE_NAME_1 + "." + AliyunOSSConfigConstant.END_POINT; //根目录 String rootpath = "资料中心/"; /** * 构造函数 * * @param content */ public wechatapplet(JSONObject content) { super(content); } /** * 获取培训学习列表 * * @return */ public String getCoursewareList() throws P2Exception { String ttypedetailid = ""; if (content.containsKey("ttypedetailid")) { ttypedetailid = content.getString("ttypedetailid"); } String keywords = ""; if (content.containsKey("keywords")) { keywords = content.getString("keywords"); } SQLFactory sqlFactory = null; if (ttypedetailid.equals("")) { sqlFactory = new SQLFactory(this, "查询课件列表_全部", pageSize, pageNumber, "t1.fisontop DESC, t1.createdate desc"); sqlFactory.addParameter("keywords", "%" + keywords + "%"); } else { sqlFactory = new SQLFactory(this, "查询课件列表", pageSize, pageNumber, "t1.fisontop DESC, t1.createdate desc"); sqlFactory.addParameter("ttypedetailid", ttypedetailid); sqlFactory.addParameter("keywords", "%" + keywords + "%"); } String sql = sqlFactory.getSQL(); Rows rows = dbConnect.runSqlQuery(sqlFactory.getSQL()); // RowsMap rowsMap = getAttachmentUrl("tarchives_sc", rows.toArrayList("tarchives_scid"), "video"); //查询封面 RowsMap rowsMapCover = getAttachmentUrl("tarchives_sc", rows.toArrayList("tarchives_scid"), "cover"); for (Row row : rows) { // row.put("attinfos", rowsMap.get(row.getString("tarchives_scid"))); Rows coverRows = rowsMapCover.get(row.getString("tarchives_scid")); if (!coverRows.isEmpty()) { row.put("cover", coverRows.get(0).getString("fobsurl")); } else { row.put("cover", ""); } } createRequestLog("培训学习"); return getSucReturnObject().setDataByPaging(rows).preloading(1).toString(); } /** * 获取课件详情 * * @return */ public String getCoursewareDetail() throws P2Exception { String tarchives_scid = content.getString("tarchives_scid"); //已学习人数加一 createUserStudyLog(tarchives_scid); SQLFactory sqlFactory = new SQLFactory(this, "查询课件详细"); sqlFactory.addParameter("tarchives_scid", tarchives_scid); Rows rows = dbConnect.runSqlQuery(sqlFactory.getSQL()); RowsMap rowsMap = getAttachmentUrl("tarchives_sc", rows.toArrayList("tarchives_scid"), "video"); //查询封面 RowsMap rowsMapCover = getAttachmentUrl("tarchives_sc", rows.toArrayList("tarchives_scid"), "cover"); for (Row row : rows) { row.put("attinfos", rowsMap.get(row.getString("tarchives_scid"))); Rows coverRows = rowsMapCover.get(row.getString("tarchives_scid")); if (!coverRows.isEmpty()) { row.put("cover", coverRows.get(0).getString("fobsurl")); } } if (rows.isEmpty()) { return getErrReturnObject().setErrMsg("未找到当前课件").toString(); } createRequestLog("培训学习"); return getSucReturnObject().setData(rows.get(0)).toString(); } public void createUserStudyLog(String tarchives_scid) { Long tuser_studyid = createTableID("tuser_study", "tuser_studyid"); SQLFactory sqlFactory = new SQLFactory(this, "创建用户学习记录"); sqlFactory.addParameter("tuser_studyid", tuser_studyid); sqlFactory.addParameter("tarchives_scid", tarchives_scid); sqlFactory.addParameter("tuserid", parameter.tuserid); dbConnect.runSqlUpdate(sqlFactory); } /** * 获取分类 * * @return * @throws P2Exception */ public String getTypeList() throws P2Exception { String fusertype = content.getString("fusertype"); String where = ""; //1表示业务员 if (fusertype.equals("1")) { where = "t1.fissaler ='1'"; } else { where = "t1.fisagent ='1'"; } String fparentid = content.getString("fparentid"); SQLFactory sqlFactory = new SQLFactory(this, "分类"); sqlFactory.addParameter_SQL("fparentid", fparentid); sqlFactory.addParameter_SQL("where", where); String sql = sqlFactory.getSQL(); Rows rows = dbConnect.runSqlQuery(sql); createRequestLog("培训学习"); return getSucReturnObject().setData(rows).toString(); } /** * 新增日志 */ private void createRequestLog(String type) { new Thread() { @Override public void run() { String sql = "SELECT*FROM tuserrequestlog WHERE fdate='" + getDate_Str() + "' AND tuserid='" + parameter.tuserid + "'"; Rows rows = dbConnect.runSqlQuery(sql); if (rows.isEmpty()) { SQLFactory sqlFactory = new SQLFactory(this, "新增日志"); sqlFactory.addParameter("tuserrequestlogid", createTableID("tuserrequestlog", "tuserrequestlogid")); sqlFactory.addParameter("siteid", parameter.defaultsiteid); sqlFactory.addParameter("fdate", getDate_Str()); sqlFactory.addParameter("flastrequestdate", getDateTime_Str()); sqlFactory.addParameter("tuserid", parameter.tuserid); sqlFactory.addParameter("type", type); dbConnect.runSqlUpdate(sqlFactory.getSQL()); } else { String id = rows.get(0).getString("tuserrequestlogid"); String sqlUpdate = "UPDATE tuserrequestlog SET frequesttimes=frequesttimes+1,flastrequestdate = '" + getDateTime_Str() + "' WHERE tuserrequestlogid = '" + id + "'"; dbConnect.runSqlUpdate(sqlUpdate); } } }.start(); } /** * 获取指定目录下的文件和文件夹 * * @return */ public String getFilesOfPath() { String folderPath = content.getString("folderPath"); //获取经销商类型 String fusertype = getUserFusertype(); //获取用户角色列表 ArrayList listUserRole = getUserRolesList(); //查询对应用户的体系 ArrayList listUserfauthtype = getUserFauthtypeList(); //查询对应账号下有哪些有权限的目录 ArrayList listUserFolders = getUserFolderList(listUserRole, listUserfauthtype); ObjectListing listing = getObjectListing(folderPath); // 遍历所有文件。 List listFiles = getFiles(listing, folderPath); // 遍历所有commonPrefix。 List listFolder = getListFolder(listing, listUserFolders, listUserRole, listUserfauthtype); ArrayList keyStr = new ArrayList<>(); for (JSONObject object : listFolder) { keyStr.add(object.getString("folderpath")); } SQLFactory sqlNameFac = new SQLFactory(this, "查询文件名称"); sqlNameFac.addParameter_in("fpath", keyStr); String sqlName = sqlNameFac.getSQL(); Rows rowsName = dbConnect.runSqlQuery(sqlName); JSONObject object = new JSONObject(); object.put("files", listFiles); object.put("folder", rowsName); // 关闭OSSClient。 ossClient.shutdown(); createRequestLog("资料中心"); return getSucReturnObject().setData(object).toString(); } List listFiles = new ArrayList<>(); List listFolder = new ArrayList<>(); /** * 搜索 * * @return */ public String searchFile() { String keyword = ""; if (content.containsKey("keyword")) { keyword = content.getString("keyword"); } String folderPath = "资料中心/"; //获取经销商类型 String fusertype = getUserFusertype(); //获取用户角色列表 ArrayList listUserRole = getUserRolesList(); //查询对应用户的体系 ArrayList listUserfauthtype = getUserFauthtypeList(); //查询对应账号下有哪些有权限的目录 ArrayList listUserFolders = getUserFolderList(listUserRole, listUserfauthtype); listFolder.clear(); listFiles.clear(); get(folderPath, listUserFolders, listUserRole, listUserfauthtype); // ObjectListing listing = getObjectListing(folderPath); // // 遍历所有文件。 // List listFiles = getFiles(listing, folderPath); // // 遍历所有commonPrefix。 // List listFolder = getListFolder(listing, listUserFolders, listUserRole, listUserfauthtype); ArrayList keyStr = new ArrayList<>(); for (JSONObject object : listFolder) { keyStr.add(object.getString("folderpath")); } SQLFactory sqlNameFac = new SQLFactory(this, "查询文件名称"); sqlNameFac.addParameter_in("fpath", keyStr); String sqlName = sqlNameFac.getSQL(); Rows rowsName = dbConnect.runSqlQuery(sqlName); List listFiles_tmp = new ArrayList<>(); for (JSONObject jsonObject : listFiles) { String name = jsonObject.getString("name"); System.err.println(); if (name.contains(keyword)) { listFiles_tmp.add(jsonObject); } } Rows rows_tmp = new Rows(); for (Row row : rowsName) { if (row.getString("tfilename").contains(keyword)) { rows_tmp.add(row); } } JSONObject object = new JSONObject(); object.put("files", listFiles_tmp); object.put("folder", rows_tmp); // 关闭OSSClient。 ossClient.shutdown(); createRequestLog("资料中心"); return getSucReturnObject().setData(object).toString(); } /** * 获取分享链接id * * @return */ public String getShareLinkId() { JSONArray pathArray = content.getJSONArray("folderPath"); JSONArray urlsArray = content.getJSONArray("urls"); ArrayList list = new ArrayList<>(); // 构造ListObjectsRequest请求。 ListObjectsRequest listObjectsRequest = new ListObjectsRequest(BUCKE_NAME_1); for (Object obj : pathArray) { // 设置prefix参数来获取fun目录下的所有文件。 listObjectsRequest.setPrefix(obj.toString()); // 递归列举目录下的所有文件。 ObjectListing listing = ossClient.listObjects(listObjectsRequest); // 遍历所有文件。 for (OSSObjectSummary objectSummary : listing.getObjectSummaries()) { if (!objectSummary.getKey().endsWith("/")) { list.add("https://" + file_url + "/" + URLEncoder.encode(objectSummary.getKey())); } } } // 关闭OSSClient。 ossClient.shutdown(); for (Object obj : urlsArray) { list.add(obj.toString()); } Long shareid = createTableID("tdownloadfile", "downloadid"); ArrayList sqlList = new ArrayList<>(); for (String strUrl : list) { Long downloadid = createTableID("tdownloadfile", "downloadid"); String sql = "INSERT INTO tdownloadfile (downloadid,rmkenable,tfilepath,tshareid)VALUES('" + downloadid + "', 0,'" + strUrl + "','" + shareid + "')"; sqlList.add(sql); } dbConnect.runSqlUpdate(sqlList); JSONObject object = new JSONObject(); object.put("shareid", shareid); createRequestLog("资料中心"); return getSucReturnObject().setData(object).toString(); } /** * 获取oss文档预览url 60分钟 * * @return */ public String getOssPreviewUrl() { String key = content.getString("key"); // 填写请求预览的文档完整路径,完整路径中不包含Bucket名称。 // 设置样式,样式中包含文档预览参数。 String style = "imm/previewdoc,copy_1"; // 指定生成的签名URL过期时间,最大值为15分钟。本示例指定生成的签名URL过期时间为10分钟。 Date expiration = new Date(System.currentTimeMillis() + 1000 * 60 * 60); GeneratePresignedUrlRequest req = new GeneratePresignedUrlRequest(BUCKE_NAME_1, key, HttpMethod.GET); req.setExpiration(expiration); req.setProcess(style); URL signedUrl = ossClient.generatePresignedUrl(req); System.out.println(signedUrl); // 关闭OSSClient。 ossClient.shutdown(); JSONObject object = new JSONObject(); object.put("url", signedUrl.toString()); return getSucReturnObject().setData(object).toString(); } /** * 添加视频播放记录 * * @return * @throws P2Exception */ public String addViewlog() throws P2Exception { Long tarchives_scid = content.getLong("tarchives_scid"); Long tattachmentid = content.getLong("tattachmentid"); String tprocess = content.getString("tprocess"); String tlogintime = content.getString("tlogintime"); PaoSetRemote paoSetRemote = getP2ServerSystemPaoSet("tviewlog", "tarchives_scid = '" + tarchives_scid + "' and tattachmentid = '" + tattachmentid + "' and tuserid = '" + parameter.tuserid + "' "); if (paoSetRemote.isEmpty()) { PaoRemote paoRemote = paoSetRemote.addAtEnd(); paoRemote.setValue("tarchives_scid", tarchives_scid, 11L); paoRemote.setValue("tattachmentid", tattachmentid, 11L); paoRemote.setValue("tprocess", tprocess, 11L); paoRemote.setValue("tuserid", parameter.tuserid, 11L); paoRemote.setValue("changedate", getDateTime_Str(), 11L); paoRemote.setValue("tlogintime", tlogintime, 11L); paoSetRemote.save(); } else { PaoRemote paoRemote = paoSetRemote.getPao(0); paoRemote.setValue("tlogintime", tlogintime, 11L); paoRemote.setValue("tprocess", tprocess, 11L); paoRemote.setValue("changedate", getDateTime_Str(), 11L); paoSetRemote.save(); } return getSucReturnObject().toString(); } /** * 返回视频播放记录 * * @return * @throws P2Exception */ public String selectViewlog() throws P2Exception { Long tarchives_scid = content.getLong("tarchives_scid"); SQLFactory sqlFactory = new SQLFactory(this, "查询视频播放记录"); sqlFactory.addParameter("tuserid", parameter.tuserid); sqlFactory.addParameter("tarchives_scid", tarchives_scid); String sql = sqlFactory.getSQL(); Rows rows = dbConnect.runSqlQuery(sql); return getSucReturnObject().setData(rows).toString(); } /** * 获取用户角色 * * @return */ public Rows getUserRoles() { //查询对应用户的角色 String sqlRoles = "SELECT wechat_position ,fusertype FROM tuser WHERE tuserid = '" + parameter.tuserid + "'"; return dbConnect.runSqlQuery(sqlRoles); } public ArrayList getUserRolesList() { return getUserRoles().toArrayList("wechat_position"); } /** * 查询用户类型 * * @return */ public String getUserFusertype() { String fusertype = "经销商"; Rows rows = getUserRoles(); if (!rows.isEmpty()) { fusertype = rows.get(0).getString("fusertype"); } return fusertype; } /** * 获取用户体系 * * @return */ public ArrayList getUserFauthtypeList() { String sqlFauthtype = "SELECT fauthtype FROM tagentauth WHERE tagentsid=( SELECT tagentsid FROM tagents WHERE wechat_depid=(SELECT wechat_depid FROM tuser WHERE tuserid= '" + parameter.tuserid + "'))"; return dbConnect.runSqlQuery(sqlFauthtype).toArrayList("fauthtype"); } public ArrayList getUserFolderList(ArrayList listRole, ArrayList listfauthtype) { ArrayList listFolders = new ArrayList<>(); SQLFactory sqlFactory = new SQLFactory(this, "查询账号目录"); sqlFactory.addParameter("tuserid", parameter.tuserid); sqlFactory.addParameter_in("frole", listRole); sqlFactory.addParameter_in("fauthtype", listfauthtype); String sql = sqlFactory.getSQL(); Rows rows = dbConnect.runSqlQuery(sql); if (!rows.isEmpty()) { for (Row row : rows) { if (row.getString("fpath") == null || row.getString("fpath").equals("")) { } else { listFolders.add(row.getString("fpath")); } } } return listFolders; } public ObjectListing getObjectListing(String folderPath) { // 构造ListObjectsRequest请求。 ListObjectsRequest listObjectsRequest = new ListObjectsRequest(BUCKE_NAME_1).withMaxKeys(1000); // 设置正斜线(/)为文件夹的分隔符。 listObjectsRequest.setDelimiter("/"); // 列出fun目录下的所有文件和文件夹。 listObjectsRequest.setPrefix(folderPath); return ossClient.listObjects(listObjectsRequest); } public List getFiles(ObjectListing listing, String folderPath) { List objectSummaryList = new ArrayList<>(); List sums = listing.getObjectSummaries(); //按照时间排序 Collections.sort(sums, (o1, o2) -> { // TODO Auto-generated method stub long dateTime1 = o1.getLastModified().getTime(); long dateTime2 = o2.getLastModified().getTime(); if (dateTime1 == dateTime2) { return 0; } else { //时间正序 // return dateTime1 > dateTime2 ? 1 : -1; //时间倒序 return dateTime1 > dateTime2 ? -1 : 1; } }); ArrayList Listkey = new ArrayList<>(); for (OSSObjectSummary objectSummary : sums) { if (!objectSummary.getKey().equals(folderPath)) { Listkey.add(objectSummary.getKey()); JSONObject object = new JSONObject(); object.put("key", objectSummary.getKey()); object.put("name", objectSummary.getKey().replace(folderPath, "")); object.put("size", objectSummary.getSize() / 1024); object.put("time", getDateTime_Str(objectSummary.getLastModified())); object.put("url", file_url + "/" + objectSummary.getKey()); objectSummaryList.add(object); } } SQLFactory sqlFactory = new SQLFactory(this, "查询转码记录"); sqlFactory.addParameter_in("tkey", Listkey); RowsMap rowsMap = dbConnect.runSqlQuery(sqlFactory).toRowsMap("tkey"); for (JSONObject object : objectSummaryList) { String key = object.getString("key"); Rows rows = rowsMap.getOrDefault(key, new Rows()); if (!rows.isEmpty()) { String tkey_new = rowsMap.get(key).get(0).getString("tkey_new"); if (!tkey_new.equals("")) { object.put("url_code", file_url + "/" + tkey_new); System.err.println(tkey_new); } else { object.put("url_code", ""); } } else { object.put("url_code", ""); } } return objectSummaryList; } public ArrayList check(ArrayList commonPrefixList, ArrayList listRole, ArrayList listfauthtype) { ArrayList commonPrefixList2 = new ArrayList<>(); //查询当前用户的上级部门id String deptId = ""; String sql = "SELECT tdepartmentid FROM tdepartment WHERE wechat_depid = ( SELECT wechat_parendeptid FROM tdepartment WHERE wechat_depid = ( SELECT wechat_depid FROM tuser WHERE tuserid = '" + parameter.tuserid + "'))"; Rows rowsDept = dbConnect.runSqlQuery(sql); if (!rowsDept.isEmpty()) { deptId = rowsDept.get(0).getString("tdepartmentid"); } for (JSONObject obj : commonPrefixList) { String fpath = obj.getString("folderpath"); //开启双向验证 boolean isFrole = false; boolean isFauthtype = false; boolean isTuserid = false; boolean isDept = false; String sqlTemp = "SELECT frole,fauthtype,tuserid,tdepartmentid FROM tfolderauth t1 LEFT JOIN tfolder t2 ON t1.tfolderid =t2.tfolderid WHERE t2.fpath ='" + fpath + "'"; Rows rowsTemp = dbConnect.runSqlQuery(sqlTemp); ArrayList list_Roles = new ArrayList<>(); ArrayList list_fauthtype = new ArrayList<>(); ArrayList list_tuserid = new ArrayList<>(); ArrayList list_tdepartmentid = new ArrayList<>(); for (Row row : rowsTemp) { String frole = row.getString("frole"); String fauthtype = row.getString("fauthtype"); String tuserid = row.getString("tuserid"); String tdepartmentid = row.getString("tdepartmentid"); if (!frole.equals("")) { list_Roles.add(frole); } if (!fauthtype.equals("")) { list_fauthtype.add(fauthtype); } if (!tuserid.equals("0")) { list_tuserid.add(tuserid); } if (!tdepartmentid.equals("0")) { list_tdepartmentid.add(tdepartmentid); } } //验证角色 if (list_Roles.size() == 0) { isFrole = true; } else { for (String str : list_Roles) { if (listRole.contains(str)) { isFrole = true; } } } //验证体系 if (list_fauthtype.size() == 0) { isFauthtype = true; } else { for (String str : list_fauthtype) { if (listfauthtype.contains(str)) { isFauthtype = true; } } } //验证用户 if (list_tuserid.size() == 0) { isTuserid = true; } else { for (String str : list_tuserid) { if (Objects.equals(parameter.tuserid, str)) { isTuserid = true; } } } //验证部门 if (list_tdepartmentid.size() == 0) { isDept = true; } else { for (String str : list_tdepartmentid) { if (Objects.equals(deptId, str)) { isDept = true; } } } if (isFrole && isFauthtype && isTuserid && isDept) { commonPrefixList2.add(obj); } System.err.println(list_Roles.size()); System.err.println(list_fauthtype.size()); System.err.println(list_tuserid.size()); } return commonPrefixList2; } public ArrayList getListFolderAll(ObjectListing listing) { ArrayList listFolderAll = new ArrayList<>(); for (String commonPrefix : listing.getCommonPrefixes()) { System.err.println(commonPrefix); JSONObject object = new JSONObject(); object.put("folderpath", commonPrefix); listFolderAll.add(object); } return listFolderAll; } public ArrayList getListFolder(ArrayList listFolderAll, ArrayList listUserFolders) { ArrayList listFolder = new ArrayList<>(); for (JSONObject object : listFolderAll) { String folderpath = object.getString("folderpath"); if (listUserFolders.size() == 0) { listFolder.add(object); } else { for (String str : listUserFolders) { if (str.equals(folderpath)) { listFolder.add(object); } } } } return listFolder; } public ArrayList getListFolder(ObjectListing listing, ArrayList listUserFolders, ArrayList listRole, ArrayList listfauthtype) { ArrayList listFolderAll = getListFolderAll(listing); ArrayList listFolder = getListFolder(listFolderAll, listUserFolders); if (listFolder.size() == 0) { listFolder = listFolderAll; } else { if (fusertype.equals("经销商")) { //开启双向验证 listFolder = check(listFolder, listRole, listfauthtype); } } return listFolder; } public void get(String folderPath, ArrayList listUserFolders, ArrayList listRole, ArrayList listfauthtype) { ObjectListing listing = getObjectListing(folderPath); // 遍历所有文件。 listFiles.addAll(getFiles(listing, folderPath)); // 遍历所有commonPrefix。 List listFolder_temp = getListFolder(listing, listUserFolders, listRole, listfauthtype); listFolder.addAll(listFolder_temp); if (listFolder_temp.size() > 0) { for (JSONObject object : listFolder_temp) { String path = object.getString("folderpath"); get(path, listUserFolders, listRole, listfauthtype); } } } }