initializeHEADmain

6 files changed, 318 insertions, 0 deletions

diff --git a/src/main/java/com/mesasoft/cn/dao/sqlprovider/AuthSqlProvider.java b/src/main/java/com/mesasoft/cn/dao/sqlprovider/AuthSqlProvider.java
new file mode 100644
index 0000000..3c9c10f
--- /dev/null
+++ b/src/main/java/com/mesasoft/cn/dao/sqlprovider/AuthSqlProvider.java
@@ -0,0 +1,48 @@
+package com.mesasoft.cn.dao.sqlprovider;
+
+import com.mesasoft.cn.SketchApplication;
+import com.mesasoft.cn.modules.constant.ConfigConsts;
+import com.zhazhapan.util.Checker;
+import org.apache.ibatis.annotations.Param;
+import org.apache.ibatis.jdbc.SQL;
+
+/**
+ * @author pantao
+ * @since 2018/1/19
+ */
+public class AuthSqlProvider {
+
+    public String updateAuthById() {
+        return CommonSqlProvider.updateAuthById("auth");
+    }
+
+    public String batchDelete(@Param("ids") String ids) {
+        return "delete from auth where id in " + (ids.startsWith("(") ? "" : "(") + ids + (ids.endsWith(")") ? "" :
+                ")");
+    }
+
+    public String getAuthBy(@Param("id") long id, @Param("userId") int userId, @Param("fileId") long fileId, @Param
+            ("fileName") String fileName, @Param("offset") int offset) {
+        String sql = new SQL() {{
+            SELECT("a.id,a.user_id,a.file_id,u.username,f.name file_name,f.local_url,a.is_downloadable,a" + "" + "" +
+                    ".is_uploadable,a.is_deletable,a.is_updatable,a.is_visible,a.create_time");
+            FROM("auth a");
+            JOIN("user u on u.id=a.user_id");
+            JOIN("file f on f.id=a.file_id");
+            if (id > 0) {
+                WHERE("a.id=#{id}");
+            }
+            if (userId > 0) {
+                WHERE("u.id=#{userId}");
+            }
+            if (fileId > 0) {
+                WHERE("f.id=#{fileId}");
+            } else if (Checker.isNotEmpty(fileName)) {
+                WHERE("f.local_url like '%" + fileName + "%'");
+            }
+            ORDER_BY("a." + SketchApplication.settings.getStringUseEval(ConfigConsts.AUTH_ORDER_BY_OF_SETTINGS));
+        }}.toString();
+        int size = SketchApplication.settings.getIntegerUseEval(ConfigConsts.AUTH_PAGE_SIZE_OF_SETTINGS);
+        return sql + " limit " + (offset * size) + "," + size;
+    }
+}
diff --git a/src/main/java/com/mesasoft/cn/dao/sqlprovider/CommonSqlProvider.java b/src/main/java/com/mesasoft/cn/dao/sqlprovider/CommonSqlProvider.java
new file mode 100644
index 0000000..ad2d024
--- /dev/null
+++ b/src/main/java/com/mesasoft/cn/dao/sqlprovider/CommonSqlProvider.java
@@ -0,0 +1,24 @@
+package com.mesasoft.cn.dao.sqlprovider;
+
+import org.apache.ibatis.jdbc.SQL;
+
+/**
+ * @author pantao
+ * @since 2018/1/19
+ */
+public class CommonSqlProvider {
+
+    private CommonSqlProvider() {}
+
+    public static String updateAuthById(String table) {
+        return new SQL() {{
+            UPDATE(table);
+            SET("is_downloadable=#{isDownloadable}");
+            SET("is_uploadable=#{isUploadable}");
+            SET("is_deletable=#{isDeletable}");
+            SET("is_updatable=#{isUpdatable}");
+            SET("is_visible=#{isVisible}");
+            WHERE("id=#{id}");
+        }}.toString();
+    }
+}
diff --git a/src/main/java/com/mesasoft/cn/dao/sqlprovider/DownloadedSqlProvider.java b/src/main/java/com/mesasoft/cn/dao/sqlprovider/DownloadedSqlProvider.java
new file mode 100644
index 0000000..5c1745f
--- /dev/null
+++ b/src/main/java/com/mesasoft/cn/dao/sqlprovider/DownloadedSqlProvider.java
@@ -0,0 +1,48 @@
+package com.mesasoft.cn.dao.sqlprovider;
+
+import com.mesasoft.cn.SketchApplication;
+import com.mesasoft.cn.modules.constant.ConfigConsts;
+import com.zhazhapan.util.Checker;
+import org.apache.ibatis.annotations.Param;
+import org.apache.ibatis.jdbc.SQL;
+
+/**
+ * @author pantao
+ * @since 2018/1/19
+ */
+public class DownloadedSqlProvider {
+
+    /**
+     * 生成一条下载记录表的查询语句
+     *
+     * @param userId 用户编号
+     * @param fileId 文件编号
+     *
+     * @return SQL语句
+     */
+    public String getDownloadBy(@Param("userId") int userId, @Param("fileId") long fileId, @Param("fileName") String
+            fileName, @Param("categoryId") int categoryId, @Param("offset") int offset) {
+        String sql = new SQL() {{
+            SELECT("d.id,d.user_id,d.file_id,u.username,u.email,f.name file_name,c.name category_name,f.visit_url,d"
+                    + ".create_time");
+            FROM("download d");
+            JOIN("user u on d.user_id=u.id");
+            JOIN("file f on d.file_id=f.id");
+            JOIN("category c on f.category_id=c.id");
+            if (userId > 0) {
+                WHERE("d.user_id=#{userId}");
+            }
+            if (fileId > 0) {
+                WHERE("d.file_id=#{fileId}");
+            } else if (Checker.isNotEmpty(fileName)) {
+                WHERE("f.local_url like '%" + fileName + "%'");
+            }
+            if (categoryId > 0) {
+                WHERE("c.id=#{categoryId}");
+            }
+            ORDER_BY("d." + SketchApplication.settings.getStringUseEval(ConfigConsts.DOWNLOAD_ORDER_BY_OF_SETTINGS));
+        }}.toString();
+        int size = SketchApplication.settings.getIntegerUseEval(ConfigConsts.DOWNLOAD_PAGE_SIZE_OF_SETTINGS);
+        return sql + " limit " + (offset * size) + "," + size;
+    }
+}
diff --git a/src/main/java/com/mesasoft/cn/dao/sqlprovider/FileSqlProvider.java b/src/main/java/com/mesasoft/cn/dao/sqlprovider/FileSqlProvider.java
new file mode 100644
index 0000000..8a2ecdc
--- /dev/null
+++ b/src/main/java/com/mesasoft/cn/dao/sqlprovider/FileSqlProvider.java
@@ -0,0 +1,110 @@
+package com.mesasoft.cn.dao.sqlprovider;
+
+import com.mesasoft.cn.SketchApplication;
+import com.mesasoft.cn.modules.constant.ConfigConsts;
+import com.zhazhapan.modules.constant.ValueConsts;
+import com.zhazhapan.util.Checker;
+import org.apache.ibatis.annotations.Param;
+import org.apache.ibatis.jdbc.SQL;
+import org.springframework.stereotype.Component;
+
+/**
+ * @author pantao
+ * @since 2018/1/19
+ */
+@Component
+public class FileSqlProvider {
+
+    public String updateAuthById() {
+        return CommonSqlProvider.updateAuthById("file");
+    }
+
+    /**
+     * 生成一条文件基本信息的查询语句
+     *
+     * @param userId 用户编号
+     * @param fileId 文件编号
+     *
+     * @return SQL语句
+     */
+    public String getBasicBy(@Param("userId") int userId, @Param("fileId") long fileId, @Param("fileName") String
+            fileName, @Param("categoryId") int categoryId, @Param("offset") int offset) {
+        String sql = new SQL() {{
+            SELECT("f.id,u.username,f.local_url,c.name category_name,f.visit_url,f.download_times," + "f" + "" + "" +
+                    ".create_time");
+            FROM("file f");
+            JOIN("user u on f.user_id=u.id");
+            JOIN("category c on f.category_id=c.id");
+            if (userId > 0) {
+                WHERE("f.user_id=#{userId}");
+            }
+            if (fileId > 0) {
+                WHERE("f.id=#{fileId}");
+            } else if (Checker.isNotEmpty(fileName)) {
+                WHERE("f.local_url like '%" + fileName + "%'");
+            }
+            if (categoryId > 0) {
+                WHERE("c.id=#{categoryId}");
+            }
+            ORDER_BY("f." + SketchApplication.settings.getStringUseEval(ConfigConsts.FILE_ORDER_BY_OF_SETTING));
+        }}.toString();
+        int size = SketchApplication.settings.getIntegerUseEval(ConfigConsts.FILE_PAGE_SIZE_OF_SETTING);
+        return sql + " limit " + (offset * size) + "," + size;
+    }
+
+    private String getSqlEnds(int offset, String orderBy, String search) {
+        int size = SketchApplication.settings.getIntegerUseEval(ConfigConsts.FILE_PAGE_SIZE_OF_SETTING);
+        return getSearch(search) + " order by " + (Checker.isEmpty(orderBy) ? SketchApplication.settings
+                .getStringUseEval(ConfigConsts.FILE_ORDER_BY_OF_SETTING) : orderBy) + " limit " + offset * size
+                + "," + size;
+    }
+
+    public String getAll(@Param("offset") int offset, @Param("categoryId") int categoryId, @Param("orderBy") String
+            orderBy, @Param("search") String search) {
+        return getBaseSql(ValueConsts.FALSE) + " where f.is_visible=1" + (categoryId < 1 ? "" : "  and " +
+                "category_id=#{categoryId}") + " and ((select a.is_visible from auth a where a.file_id=f.id and a" +
+                ".user_id=#{userId}) is null or (a.user_id=#{userId} and a.is_visible=1))" + getSqlEnds(offset,
+                orderBy, search);
+    }
+
+    public String getUserUploaded(@Param("offset") int offset, @Param("search") String search) {
+        return getBaseSql(ValueConsts.FALSE) + " where f.is_visible=1 and (f.user_id=#{userId} or a.is_updatable=1 or" +
+                " a.is_deletable=1)" + getSqlEnds(offset,
+                ValueConsts.EMPTY_STRING, search);
+    }
+
+    public String getUserDownloaded(@Param("offset") int offset, @Param("search") String search) {
+        return getBaseSql(ValueConsts.TRUE) + " where d.user_id=#{userId}" + getSqlEnds(offset, ValueConsts
+                .EMPTY_STRING, search);
+    }
+
+    private String getSearch(String search) {
+        if (Checker.isEmpty(search)) {
+            return ValueConsts.EMPTY_STRING;
+        } else {
+            search = "'%" + search + "%'";
+            return " and (f.name like " + search + " or f.visit_url like " + search + " or f.description like " +
+                    search + " or f.tag like " + search + ")";
+        }
+    }
+
+    private String getBaseSql(boolean isDownloaded) {
+        return new SQL() {{
+            SELECT("distinct f.id,f.user_id,u.username,u.avatar,f.name file_name,f.size,f.create_time,c.name " +
+                    "category_name,f"
+                    + ".description,f.tag,f.check_times,f.download_times,f.visit_url,f.is_uploadable,f.is_deletable,"
+                    + "f.is_updatable,f.is_downloadable,f.is_visible");
+            if (isDownloaded) {
+                SELECT("d.create_time download_time");
+            }
+            FROM("file f");
+            JOIN("user u on u.id=f.user_id");
+            JOIN("category c on c.id=f.category_id");
+            if (isDownloaded) {
+                JOIN("download d on d.file_id=f.id");
+            } else {
+                JOIN("auth a on a.file_id=f.id");
+            }
+        }}.toString();
+    }
+}
diff --git a/src/main/java/com/mesasoft/cn/dao/sqlprovider/UploadedSqlProvider.java b/src/main/java/com/mesasoft/cn/dao/sqlprovider/UploadedSqlProvider.java
new file mode 100644
index 0000000..720b716
--- /dev/null
+++ b/src/main/java/com/mesasoft/cn/dao/sqlprovider/UploadedSqlProvider.java
@@ -0,0 +1,47 @@
+package com.mesasoft.cn.dao.sqlprovider;
+
+import com.mesasoft.cn.SketchApplication;
+import com.mesasoft.cn.modules.constant.ConfigConsts;
+import com.zhazhapan.util.Checker;
+import org.apache.ibatis.annotations.Param;
+import org.apache.ibatis.jdbc.SQL;
+
+/**
+ * @author pantao
+ * @since 2018/2/28
+ */
+public class UploadedSqlProvider {
+
+    /**
+     * 生成一条上传记录表的查询语句
+     *
+     * @param userId 用户编号
+     * @param fileId 文件编号
+     *
+     * @return SQL语句
+     */
+    public String getDownloadBy(@Param("userId") int userId, @Param("fileId") long fileId, @Param("fileName") String
+            fileName, @Param("categoryId") int categoryId, @Param("offset") int offset) {
+        String sql = new SQL() {{
+            SELECT("f.id,f.user_id,u.username,u.email,f.name file_name,c.name category_name,f.local_url,f.visit_url,"
+                    + "" + "" + "" + "f" + ".create_time");
+            FROM("file f");
+            JOIN("user u on f.user_id=u.id");
+            JOIN("category c on f.category_id=c.id");
+            if (userId > 0) {
+                WHERE("f.user_id=#{userId}");
+            }
+            if (fileId > 0) {
+                WHERE("f.id=#{fileId}");
+            } else if (Checker.isNotEmpty(fileName)) {
+                WHERE("f.local_url like '%" + fileName + "%'");
+            }
+            if (categoryId > 0) {
+                WHERE("c.id=#{categoryId}");
+            }
+            ORDER_BY("f." + SketchApplication.settings.getStringUseEval(ConfigConsts.FILE_ORDER_BY_OF_SETTING));
+        }}.toString();
+        int size = SketchApplication.settings.getIntegerUseEval(ConfigConsts.FILE_PAGE_SIZE_OF_SETTING);
+        return sql + " limit " + (offset * size) + "," + size;
+    }
+}
diff --git a/src/main/java/com/mesasoft/cn/dao/sqlprovider/UserSqlProvider.java b/src/main/java/com/mesasoft/cn/dao/sqlprovider/UserSqlProvider.java
new file mode 100644
index 0000000..5d36845
--- /dev/null
+++ b/src/main/java/com/mesasoft/cn/dao/sqlprovider/UserSqlProvider.java
@@ -0,0 +1,41 @@
+package com.mesasoft.cn.dao.sqlprovider;
+
+import com.mesasoft.cn.SketchApplication;
+import com.mesasoft.cn.modules.constant.ConfigConsts;
+import com.mesasoft.cn.modules.constant.DefaultValues;
+import com.zhazhapan.util.Checker;
+import org.apache.ibatis.annotations.Param;
+import org.apache.ibatis.jdbc.SQL;
+
+/**
+ * @author pantao
+ * @since 2018/1/19
+ */
+public class UserSqlProvider {
+
+    public String updateAuthById() {
+        return CommonSqlProvider.updateAuthById("user");
+    }
+
+    public String getUserBy(@Param("permission") int permission, @Param("condition") String condition, @Param
+            ("offset") int offset) {
+        String sql = new SQL() {{
+            SELECT("*");
+            FROM("user");
+            if (permission == DefaultValues.THREE_INT) {
+                WHERE("permission<3");
+            } else if (permission == DefaultValues.TWO_INT) {
+                WHERE("permission<2");
+            } else {
+                WHERE("permission<0");
+            }
+            if (Checker.isNotEmpty(condition)) {
+                WHERE("username like '%" + condition + "%' or email like '%" + condition + "%' or real_name like '" +
+                        condition + "'");
+            }
+            ORDER_BY(SketchApplication.settings.getStringUseEval(ConfigConsts.USER_ORDER_BY_OF_SETTINGS));
+        }}.toString();
+        int size = SketchApplication.settings.getIntegerUseEval(ConfigConsts.USER_PAGE_SIZE_OF_SETTINGS);
+        return sql + " limit " + (offset * size) + "," + size;
+    }
+}