mssql和日常办公日记

mssql:
字符串转日期
a.schedule_date between cast('2022-11-10' as datetime) and cast('2022-11-29' as datetime)
日期截取
SUBSTRING(convert(varchar(101), begntime, 8), 0, 6) AS begntime
转换日期
CONVERT(varchar(100), a.schedule_date, 23) AS scheduleDate
格式化日期
FORMAT(schedule_date, 'yyyy-MM-dd') AS scheduleDate -- yyyy-MM-dd HH:mm:ss

日期大于昨天
select * from mob_use_appointmen where appoint_date > dateadd(DD,-1,getdate())

计算年龄
floor((DateDiff(Day, vis_birth, getdate()))/365.25) age

SELECT FORMAT (getdate(), 'dd-MM-yy') as date
SELECT FORMAT (getdate(), 'hh:mm:ss') as time

生成uniquestirng ID
NEWID()
sysdate()

分页：

WITH selectTemp AS (SELECT TOP 100 PERCENT ROW_NUMBER() OVER (ORDER BY id DESC) as __row_number__, id,order_number FROM mob_use_appointmen WHERE (mark = 1) ORDER BY id DESC) SELECT * FROM selectTemp WHERE __row_number__ BETWEEN 1 AND 10 ORDER BY __row_number__

// 获取数据列表
IPage<SetDoctor> page = new Page<>(setDoctorQuery.getPage(), setDoctorQuery.getLimit());
IPage<SetDoctor> pageData = setDoctorMapper.selectPage(page, queryWrapper);
pageData.convert(x -> {
SetDoctorListVo setDoctorListVo = Convert.convert(SetDoctorListVo.class, x);
return setDoctorListVo;
});

<resultMap type="com.javaweb.generator.entity.GenTable" id="GenTableResult">
<id property="id" column="id"/>
<result property="tableName" column="table_name"/>
<result property="tableComment" column="table_comment"/>
<result property="className" column="class_name"/>
<result property="tplCategory" column="tpl_category"/>
<result property="packageName" column="package_name"/>
<result property="moduleName" column="module_name"/>
<result property="businessName" column="business_name"/>
<result property="functionName" column="function_name"/>
<result property="functionAuthor" column="function_author"/>
<result property="options" column="options"/>
<result property="createUser" column="create_user"/>
<result property="createTime" column="create_time"/>
<result property="updateUser" column="update_user"/>
<result property="updateTime" column="update_time"/>
<result property="note" column="note"/>
<collection property="columns" javaType="java.util.List" resultMap="GenTableColumnResult"/>
</resultMap>

<resultMap type="com.javaweb.generator.entity.GenTableColumn" id="GenTableColumnResult">
<id property="id" column="id"/>
<result property="tableId" column="table_id"/>
<result property="columnName" column="column_name"/>
<result property="columnComment" column="column_comment"/>
<result property="columnType" column="column_type"/>
<result property="javaType" column="java_type"/>
<result property="javaField" column="java_field"/>
<result property="isPk" column="is_pk"/>
<result property="isIncrement" column="is_increment"/>
<result property="isRequired" column="is_required"/>
<result property="isInsert" column="is_insert"/>
<result property="isEdit" column="is_edit"/>
<result property="isList" column="is_list"/>
<result property="isQuery" column="is_query"/>
<result property="queryType" column="query_type"/>
<result property="htmlType" column="html_type"/>
<result property="dictType" column="dict_type"/>
<result property="sort" column="sort"/>
<result property="createUser" column="create_user"/>
<result property="createTime" column="create_time"/>
<result property="updateUser" column="update_user"/>
<result property="updateTime" column="update_time"/>
</resultMap>

/**
* 获取业务表列表
*
* @param page 分页信息
* @param param 参数
* @return
*/
IPage<GenTable> selectGenTableList(IPage<GenTable> page, @Param("param") GenTableQuery param);

<sql id="selectGenTableVo">
select id, table_name, table_comment, class_name, tpl_category, package_name, module_name, business_name, function_name, function_author, options, note, create_user, create_time, update_user, update_time, mark from gen_table
</sql>

<!-- 获取数据表列表 -->
<select id="selectGenTableList" parameterType="com.javaweb.generator.entity.GenTable" resultMap="GenTableResult">
<include refid="selectGenTableVo"/>
<where>
<if test="param.tableName != null and param.tableName != ''">
AND lower(table_name) like lower(concat('%', #{param.tableName}, '%'))
</if>
<if test="param.tableComment != null and param.tableComment != ''">
AND lower(table_comment) like lower(concat('%', #{param.tableComment}, '%'))
</if>
AND mark = 1
</where>
</select>

<!-- 获取数据库表 -->
<select id="selectDbTableList" parameterType="com.javaweb.generator.entity.GenTable" resultMap="GenTableResult">
select table_name, table_comment, create_time, update_time from information_schema.tables
where table_schema = (select database())
AND table_name NOT LIKE 'gen_%'
AND table_name NOT IN (select table_name from gen_table)
<if test="param.tableName != null and param.tableName != ''">
AND lower(table_name) like lower(concat('%', #{param.tableName}, '%'))
</if>
</select>

<!-- 根据表名获取数据表 -->
<select id="selectDbTableListByNames" resultMap="GenTableResult">
select table_name, table_comment, create_time, update_time from information_schema.tables
where table_name NOT LIKE 'gen_%' and table_schema = (select database())
and table_name in
<foreach collection="array" item="name" open="(" separator="," close=")">
#{name}
</foreach>
</select>

获取2周后日期
//当前日期
String currentDate = LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));

LocalDate today = LocalDate.now();
String currentDate2 = today.format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
System.out.println(currentDate2);

LocalDate nextWeek = today.plus(2, ChronoUnit.WEEKS);

currentDate2 = nextWeek.format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
System.out.println(currentDate2);

 

select top 1 hos.hospital_number , h.id , h.interval_num, k.department_name , y.doc_name , p.reg_fee , p.schedule_date \n" +
" , case when p.time_flag = 1 then '上午' when p.time_flag = 2 then '下午' when p.time_flag = 1 then '晚上' end as time_flag\n" +
" , CONVERT(varchar(12) , h.begntime , 108 ) as begntime , CONVERT(varchar(12) , h.endtime , 108 ) as endtime \n" +
" , substring( CONVERT(varchar(12) , h.begntime , 108 ) ,1,5) + '-' + substring( CONVERT(varchar(12) , h.endtime , 108 ) ,1,5) as interval\n" +
" , hos.hospital_name \n" +
" from set_doc_roster_interval as h \n" +
" join set_doc_roster as p on h.schedule_id = p.id \n" +
" join set_doctor as y on y.id = p.dr_code \n" +
" join set_department as k on k.id = p.dep_code \n" +
" join set_hospital as hos on hos.hospital_number = h.hospital_number \n" +
" where h.hospital_number =#{hospitalNumber} \n" +
" and h.id = #{id} ")

http://192.168.1.56:8080/jeecg-boot/#/default/人员对接接口信息/getRyxxUsingGET

getOpenUser
人员信息查询
Parameters

mvn archetype:generate
mvn dependency:tree
mvn dependency:list
mvn clean install -Dmaven.test.skip=true
mvn

 

 

 

 

 

 

 

JsonElement element = json.get("errcode");
JsonArray ja = new JsonArray();
ja.add(element);
return ja;

 

package com.javaweb.common.task;

import com.alibaba.fastjson.JSON;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.google.gson.JsonArray;
import com.google.gson.JsonElement;
import com.google.gson.JsonObject;
import com.google.gson.JsonParser;
import com.javaweb.common.common.WxToken;
import com.javaweb.common.dto.TemplateData;
import com.javaweb.common.exception.CustomException;
import com.javaweb.common.utils.WxjssdkUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.http.HttpEntity;
import org.apache.http.HttpStatus;
import org.apache.http.client.ClientProtocolException;
import org.apache.http.client.methods.CloseableHttpResponse;
import org.apache.http.client.methods.HttpGet;
import org.apache.http.client.methods.HttpPost;
import org.apache.http.entity.ContentType;
import org.apache.http.entity.StringEntity;
import org.apache.http.impl.client.CloseableHttpClient;
import org.apache.http.impl.client.HttpClients;
import org.apache.http.util.EntityUtils;
import org.springframework.scheduling.annotation.Async;
import org.springframework.stereotype.Component;

import java.io.IOException;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

@Component
@Slf4j
public class WxTemplateUtils {

    //设置行业模板
    private static String setIndustryModelUrl = "https://api.weixin.qq.com/cgi-bin/template/api_set_industry?access_token=%s";
    //获取设置的行业信息
    private static String getIndustryUrl = "https://api.weixin.qq.com/cgi-bin/template/get_industry?access_token=%s";
    //获得模板ID
    private static String getModelIdUrl = "https://api.weixin.qq.com/cgi-bin/template/api_add_template?access_token=%s";

    //获取模板列表
    private static String getModelListUrl = "https://api.weixin.qq.com/cgi-bin/template/get_all_private_template?access_token=%s";
    //删除模板
    private static String deleteModelUrl = "https://api.weixin.qq.com/cgi-bin/template/del_private_template?access_token=%s";

    //发送模板消息
    private static String sendModelNoteUrl = "https://api.weixin.qq.com/cgi-bin/message/template/send?access_token=%s";

    //获取全局access_token
    private static String getTokenUrl = "https://api.weixin.qq.com/cgi-bin/token?grant_type=client_credential&appid=%s&secret=%s";

    /**
     * 获取微信全局token
     * @param appid
     * @param secret
     */
    public static JsonObject getGlobalToken(String appid, String secret) {
        CloseableHttpClient client = HttpClients.createDefault();
        HttpGet get = new HttpGet(String.format(getTokenUrl, appid, secret));

        WxToken at = null;
        CloseableHttpResponse res = null;
        JsonObject json = null;
        try {
            res = client.execute(get);
            String responseContent = null; // 响应内容
            HttpEntity entity = res.getEntity();
            responseContent = EntityUtils.toString(entity, "UTF-8");
            json = JsonParser.parseString(responseContent).getAsJsonObject();
            if (res.getStatusLine().getStatusCode() == HttpStatus.SC_OK) {
                return json;
//                if (json.get("errcode") == null) {// 错误时微信会返回错误码等信息，{"errcode":40013,"errmsg":"invalid appid"}
//                    at = new WxToken();
//                    at.setToken(json.get("access_token").getAsString());
//                    at.setExpiresIn(json.get("expires_in").getAsInt());
//                    log.info("access_token::"  + json.get("access_token").getAsString() + json.get("expires_in").getAsInt());
//                } else {
//                    at = new WxToken();
//                    at.setToken(json.get("errmsg").getAsString());
//                    at.setExpiresIn(json.get("errcode").getAsInt());
//                    throw new CustomException(json.get("errmsg").getAsString(), json.get("errcode").getAsInt());
//                }
            }
        } catch (ClientProtocolException e) {
            throw new CustomException("WxjssdkUtil.getToken() 获取微信token失败");
        } catch (IOException e) {
            throw new CustomException("WxjssdkUtil.getToken() 获取微信token失败");
        } finally {
            // 关闭连接 ,释放资源
            //client.getConnectionManager().shutdown();
            try {
                if (res != null){
                    res.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
                throw new CustomException("WxjssdkUtil.getToken() 获取微信token失败");
            }
            try {
                if (client != null) {
                    client.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
                throw new CustomException("WxjssdkUtil.getToken() 获取微信token失败");
            }
        }
        return json;
    }
    /**
     * 设置所属行业
     */
    public static JsonObject settingIndustry () {
        CloseableHttpClient client = HttpClients.createDefault();
        WxToken at = null;
        CloseableHttpResponse res = null;
        JsonObject json = null;
        try {
            HttpPost httpPost = new HttpPost(String.format(setIndustryModelUrl, WxjssdkUtil.WX_TOKEN_MAP.get(WxjssdkUtil.DEFAULT_WX_TOKEN_KEY)));
            Map<String, String> param = new HashMap<>();
            param.put("industry_id1", "1");
            param.put("industry_id2", "22");
            StringEntity reqEntity = new StringEntity(JSON.toJSONString(param), ContentType.create("application/json", "utf-8"));
            // ...
            httpPost.setEntity(reqEntity);
            res = client.execute(httpPost);
            String responseContent = null; // 响应内容
            HttpEntity entity = res.getEntity();
            responseContent = EntityUtils.toString(entity, "UTF-8");
            json = JsonParser.parseString(responseContent).getAsJsonObject();
            if (res.getStatusLine().getStatusCode() == HttpStatus.SC_OK) {
                if (json.get("errcode") == null) {// 错误时微信会返回错误码等信息，{"errcode":40013,"errmsg":"invalid appid"}
                    log.info("jsonjson::"  + json);
                }
                log.info("jsonjson::"  + json);
            }
        } catch (ClientProtocolException e) {
            throw new CustomException("WxjssdkUtil.getToken() 获取微信token失败");
        } catch (IOException e) {
            throw new CustomException("WxjssdkUtil.getToken() 获取微信token失败");
        } finally {
            // 关闭连接 ,释放资源
            //client.getConnectionManager().shutdown();
            try {
                if (res != null){
                    res.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
                throw new CustomException("WxjssdkUtil.getToken() 获取微信token失败");
            }
            try {
                if (client != null) {
                    client.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
                throw new CustomException("WxjssdkUtil.getToken() 获取微信token失败");
            }
        }
        return json;
    }

    /**
     * 获取设置的行业信息
     */
    public static JsonObject getIndustry () {
        CloseableHttpClient client = HttpClients.createDefault();
        HttpGet get = new HttpGet(String.format(getIndustryUrl, WxjssdkUtil.WX_TOKEN_MAP.get(WxjssdkUtil.DEFAULT_WX_TOKEN_KEY)));

        CloseableHttpResponse res = null;
        JsonObject json = null;
        try {
            res = client.execute(get);
            String responseContent = null; // 响应内容
            HttpEntity entity = res.getEntity();
            responseContent = EntityUtils.toString(entity, "UTF-8");
            json = JsonParser.parseString(responseContent).getAsJsonObject();
            if (res.getStatusLine().getStatusCode() == HttpStatus.SC_OK) {
                if (json.get("errcode") == null) {// 错误时微信会返回错误码等信息，{"errcode":40013,"errmsg":"invalid appid"}
                    log.info("getIndustry::"  + json);
                }
                log.info("jsonjson::"  + json);
            }
        } catch (ClientProtocolException e) {
            throw new CustomException("WxjssdkUtil.getToken() 获取微信token失败");
        } catch (IOException e) {
            throw new CustomException("WxjssdkUtil.getToken() 获取微信token失败");
        } finally {
            // 关闭连接 ,释放资源
            //client.getConnectionManager().shutdown();
            try {
                if (res != null){
                    res.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
                throw new CustomException("WxjssdkUtil.getToken() 获取微信token失败");
            }
            try {
                if (client != null) {
                    client.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
                throw new CustomException("WxjssdkUtil.getToken() 获取微信token失败");
            }
        }
        return json;
    }

    /**
     * 获得模板ID
     */
    public static Map<String, String> getModelId (Map<String, String> paramMap, String key, String token) {
        CloseableHttpClient client = HttpClients.createDefault();
        HttpPost post = new HttpPost(String.format(getModelIdUrl, token));
        WxToken at = null;
        CloseableHttpResponse res = null;
        try {
//            Map<String, String> param = new HashMap <>();
//            param.put("template_id_short", key);

            String jsonPost = "{\"template_id_short\":\""+key+"\"}";
            StringEntity param = new StringEntity(jsonPost);
            post.setEntity(param);
            post.setHeader("Accept", "application/json");
            post.setHeader("Content-type", "application/json");
//            StringEntity reqEntity = new StringEntity(JSON.toJSONString(param), ContentType.create("application/json", "utf-8"));
            // ...
//            post.setEntity(reqEntity);
            res = client.execute(post);
            String responseContent = null; // 响应内容
            HttpEntity entity = res.getEntity();
            responseContent = EntityUtils.toString(entity, "UTF-8");
            JsonObject json = JsonParser.parseString(responseContent).getAsJsonObject();
            if (res.getStatusLine().getStatusCode() == HttpStatus.SC_OK) {
                if (json.get("errcode").getAsInt() == 0 &&  "ok".equals(json.get("errmsg").getAsString())) {// 错误时微信会返回错误码等信息，{"errcode":40013,"errmsg":"invalid appid"}
                    log.info("jsonjson::"  + json);
                    paramMap.put(key, json.get("template_id").getAsString());
                } else {
                    throw new CustomException(json.get("errmsg").getAsString(), json.get("errcode").getAsInt());
                }
            }
        } catch (ClientProtocolException e) {
            throw new CustomException("WxjssdkUtil.getToken() 获取微信token失败");
        } catch (IOException e) {
            throw new CustomException("WxjssdkUtil.getToken() 获取微信token失败");
        } finally {
            // 关闭连接 ,释放资源
            //client.getConnectionManager().shutdown();
            try {
                if (res != null){
                    res.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
                throw new CustomException("WxjssdkUtil.getToken() 获取微信token失败");
            }
            try {
                if (client != null) {
                    client.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
                throw new CustomException("WxjssdkUtil.getToken() 获取微信token失败");
            }
        }
        return paramMap;
    }

    /**
     * 获取模板列表
     */
    public static JsonObject getAllPrivateTemplate(String token) {
        CloseableHttpClient client = HttpClients.createDefault();
        HttpGet get = new HttpGet(String.format(getModelListUrl, token));

        CloseableHttpResponse res = null;
        JsonObject json = null;
        try {
            res = client.execute(get);
            String responseContent = null; // 响应内容
            HttpEntity entity = res.getEntity();
            responseContent = EntityUtils.toString(entity, "UTF-8");
            json = JsonParser.parseString(responseContent).getAsJsonObject();
            if (res.getStatusLine().getStatusCode() == HttpStatus.SC_OK) {

            }
        } catch (ClientProtocolException e) {
            throw new CustomException("WxjssdkUtil.getToken() 获取微信token失败");
        } catch (IOException e) {
            throw new CustomException("WxjssdkUtil.getToken() 获取微信token失败");
        } finally {
            // 关闭连接 ,释放资源
            //client.getConnectionManager().shutdown();
            try {
                if (res != null){
                    res.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
                throw new CustomException("WxjssdkUtil.getToken() 获取微信token失败");
            }
            try {
                if (client != null) {
                    client.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
                throw new CustomException("WxjssdkUtil.getToken() 获取微信token失败");
            }
        }
        return json;
    }

    /**
     * 删除模板
     */
    public static JsonObject delPrivateTemplate(String templateId, String token) throws CustomException {
        CloseableHttpClient client = HttpClients.createDefault();

        WxToken at = null;
        CloseableHttpResponse res = null;
        JsonObject json = null;
        try {
            HttpPost httpPost = new HttpPost(String.format(deleteModelUrl, token));
            Map<String, String> param = new HashMap <>();
            param.put("template_id", templateId);
            StringEntity reqEntity = new StringEntity(JSON.toJSONString(param), ContentType.create("application/json", "utf-8"));
            // ...
            httpPost.setEntity(reqEntity);
            res = client.execute(httpPost);
            String responseContent = null; // 响应内容
            HttpEntity entity = res.getEntity();
            responseContent = EntityUtils.toString(entity, "UTF-8");
            json = JsonParser.parseString(responseContent).getAsJsonObject();
            if (res.getStatusLine().getStatusCode() == HttpStatus.SC_OK) {
                if (json.get("errcode").getAsInt() == 0 && "ok".equals(json.get("errmsg").getAsString())) {// 错误时微信会返回错误码等信息，{"errcode":40013,"errmsg":"invalid appid"}
                    log.info("jsonjson::"  + json);
                } else {
                    throw new CustomException(json.get("errmsg").getAsString(), json.get("errcode").getAsInt());
                }
                log.info("jsonjson::"  + json);
            }
        } catch (ClientProtocolException e) {
            throw new CustomException("WxjssdkUtil.getToken() 获取微信token失败");
        } catch (IOException e) {
            throw new CustomException("WxjssdkUtil.getToken() 获取微信token失败");
        } finally {
            // 关闭连接 ,释放资源
            //client.getConnectionManager().shutdown();
            try {
                if (res != null){
                    res.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
                throw new CustomException("WxjssdkUtil.getToken() 获取微信token失败");
            }
            try {
                if (client != null) {
                    client.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
                throw new CustomException("WxjssdkUtil.getToken() 获取微信token失败");
            }
        }
        return json;
    }

    /**
     * 发送模板消息
     * 异步处理
     */
    @Async("orderExecutor")
    public void sendMessage(TemplateData data, String token) {
        CloseableHttpClient client = HttpClients.createDefault();
        WxToken at = null;
        CloseableHttpResponse res = null;
        try {
            HttpPost httpPost = new HttpPost(String.format(sendModelNoteUrl, token));
//            Map<String, String> param = new HashMap <>();
//            param.put("touser", openId);
//            param.put("template_id", templateId);
//            param.put("url", url);
//            param.put("data", data);
            StringEntity reqEntity = new StringEntity(JSON.toJSONString(data), ContentType.create("application/json", "utf-8"));
            // ...
            httpPost.setEntity(reqEntity);
            res = client.execute(httpPost);
            String responseContent = null; // 响应内容
            HttpEntity entity = res.getEntity();
            responseContent = EntityUtils.toString(entity, "UTF-8");
            JsonObject json = JsonParser.parseString(responseContent).getAsJsonObject();
            if (res.getStatusLine().getStatusCode() == HttpStatus.SC_OK) {
                if (json.get("errcode").getAsInt() == 0 && "ok".equals(json.get("errmsg").getAsString())) {// 错误时微信会返回错误码等信息，{"errcode":40013,"errmsg":"invalid appid"}
                    log.info("发送模板成功：：：" + data);
                } else {
                    throw new CustomException(json.get("errmsg").getAsString(), json.get("errcode").getAsInt());
                }
            }
        } catch (ClientProtocolException e) {
            throw new CustomException("WxjssdkUtil.getToken() 获取微信token失败");
        } catch (IOException e) {
            throw new CustomException("WxjssdkUtil.getToken() 获取微信token失败");
        } finally {
            // 关闭连接 ,释放资源
            //client.getConnectionManager().shutdown();
            try {
                if (res != null){
                    res.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
                throw new CustomException("WxjssdkUtil.getToken() 获取微信token失败");
            }
            try {
                if (client != null) {
                    client.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
                throw new CustomException("WxjssdkUtil.getToken() 获取微信token失败");
            }
        }
    }
}