{"id":1473,"date":"2021-11-05T16:17:10","date_gmt":"2021-11-05T08:17:10","guid":{"rendered":"https:\/\/www.zeroheart.xyz\/wordpress\/?p=1473"},"modified":"2021-11-05T16:17:10","modified_gmt":"2021-11-05T08:17:10","slug":"pg%e7%9a%84%e4%b8%80%e4%b8%aa%e5%ad%98%e5%82%a8%e8%bf%87%e7%a8%8b%e5%a4%87%e4%bb%bd%e4%b8%8b","status":"publish","type":"post","link":"https:\/\/www.zeroheart.xyz\/wordpress\/?p=1473","title":{"rendered":"pg\u7684\u4e00\u4e2a\u5b58\u50a8\u8fc7\u7a0b\u5907\u4efd\u4e0b"},"content":{"rendered":"\n<p>1.\u6ce8\u610f\u6e38\u6807\u7684\u5904\u7406<\/p>\n\n\n\n<p>2.\u6ce8\u610f\u5b58\u50a8\u8fc7\u7a0b\u4e5f\u53ef\u4ee5\u6253\u5370\u8f93\u51fa\uff0c\u65b9\u4fbf\u8c03\u8bd5<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>CREATE OR REPLACE PROCEDURE \"bsspm\".\"calculate_warning_detail\"(\"sqlstr\" text, \"datestr\" varchar)\n AS $BODY$\n\t\tDECLARE\n\t\trec   RECORD; -- \u8ba1\u7b97\u7ed3\u679c\u904d\u5386\n\t\toldrec   RECORD; -- \u4e4b\u524d\u4fdd\u5b58\u7684\u7ed3\u679c\u904d\u5386\n\t\thandlerec   RECORD; -- \u4e4b\u524d\u4fdd\u5b58\u7684\u5904\u7f6e\u7ed3\u679c\u904d\u5386\n\t\trecords refcursor; -- \u672c\u6b21\u8ba1\u7b97\u7ed3\u679c\n\t\toldrecords refcursor; -- \u4e4b\u524d\u4fdd\u5b58\u7684\u7ed3\u679c\n\t\tsqlxx varchar; -- \u62fc\u63a5\u7684\u8ba1\u7b97sql\n\t\trecordkey varchar; -- \u5224\u65ad\u662f\u5426\u5b58\u5728\u7684key\uff0c\u65b0\u6570\u636e\n\t\trecordkey1 varchar; -- \u5224\u65ad\u662f\u5426\u5b58\u5728\u7684key\uff0c\u8001\u6570\u636e\n\t\texistkey INTEGER DEFAULT 0; -- \u6570\u636e\u5b58\u5728\u4e0e\u5426\n    begin\n\t\t\t\t-- \u53c2\u6570\u5316\u62fc\u63a5\u8ba1\u7b97\u7ea2\u9ec4\u706fsql\n\t\t\t\tsqlxx:='select * from (SELECT \n\t\t\t\t\tbpii.\"ID\",\n\t\t\t\t\tbpii.\"PROJECT_ID\",\n\t\t\t\t\tbpii.\"CONTRACT_ID\",\n\t\t\t\t\tbpii.\"ENTERPRISE_ID\",\n\t\t\t\t\tbpii.\"ENTERPRISE_NAME\",\n\t\t\t\t\tbpii.\"MANAGEMENT_UNIT\",\n\t\t\t\t\tbpii.\"ENTERPRISE_TYPE\",\n\t\t\t\t\tbpii.\"INDICATOR_ID\",\n\t\t\t\t\tbpii.\"INDICATOR_CODE\",\n\t\t\t\t\tbpii.\"INDICATOR_VALUE\",\n\t\t\t\t\tbpii.\"BELONG_ENTERPRISE_ID\",\n\t\t\t\t\tbpii.\"CREATED_TS\",\n\t\t\t\t\t' \n\t\t\t\t\t|| sqlstr || \n\t\t\t\t\t'from \"BSS_PPM_INDICATOR_INFO\" as bpii\n\t\t\t\t\twhere DATE_FORMAT ( bpii.\"CREATED_TS\", ''%Y-%m-%d'') = ''' || datestr || '''\n\t\t\t\t\t) tt where caculateIndicatorLight in (''\u7ea2\u706f'',''\u9ec4\u706f'')\n\t\t\t\t\t';\n\t\t\t\t-- \u67e5\u8be2\u65b0\u8001\u6570\u636e\n\t\t\t\topen oldrecords for EXECUTE('select * from \"BSS_PPM_IND_SUMMARY\" where \"INDICATOR_END_TIME\" = ''9999-12-31''');\n\t\t\t\topen records FOR EXECUTE(sqlxx);\n\t\t\t\t-- **** \u904d\u5386\u65b0\u6570\u636e\uff0c\u5982\u679c\u8001\u6570\u636e\u4e2d\u6ca1\u6709\u5bf9\u5e94\u7684\uff0c\u76f4\u63a5\u63d2\u5165\u65b0\u7684\u8ba1\u7b97\u7ed3\u679c **** \n\t\t\t\tLOOP\n\t\t\t\t\t-- \u904d\u5386\u65b0\u6570\u636e\n\t\t\t\t\t\tFETCH records INTO rec;\n\t\t\t\t\t\tEXIT WHEN NOT FOUND;\n\t\t\t\t\t\trecordkey:= rec.\"INDICATOR_CODE\" || '_' || rec.\"ENTERPRISE_ID\" || '_'  || rec.\"ENTERPRISE_TYPE\" || '_'  || rec.\"CONTRACT_ID\" || '_' || rec.\"PROJECT_ID\";\n\t\t\t\t\t\tLOOP\n\t\t\t\t\t\t\t-- \u904d\u5386\u8001\u6570\u636e\n\t\t\t\t\t\t\tFETCH oldrecords INTO oldrec;\n\t\t\t\t\t\t\tEXIT WHEN NOT FOUND;\n\t\t\t\t\t\t\trecordkey1:= oldrec.\"INDICATOR_CODE\" || '_' || oldrec.\"ENTERPRISE_ID\" || '_'  || oldrec.\"ENTERPRISE_TYPE\" || '_'  || oldrec.\"CONTRACT_ID\" || '_' || oldrec.\"PROJECT_ID\";\n\t\t\t\t\t\t\t\n\t\t\t\t\t\t\tif recordkey = recordkey1 then\n\t\t\t\t\t\t\t\texistkey:= 1;\n\t\t\t\t\t\t\tend if;\n\t\t\t\t\t\tEND LOOP;\n\t\t\t\t\t\t-- ******* \u6e38\u6807\u5f52\u4f4d\n\t\t\t\t\t\tMOVE BACKWARD ALL from oldrecords;\n\t\t\t\t\t\t-- \u65b0\u8bb0\u5f55\u6709\uff0c\u539f\u6765\u7684\u8bb0\u5f55\u6ca1\u6709\uff0c\u589e\u52a0\u4e00\u6761\u8bb0\u5f55\n\t\t\t\t\t\tif existkey = 0 then\n\t\t\t\t\t\t\tRAISE NOTICE 'insert %', rec.\"ID\";\n\t\t\t\t\t\t\tINSERT INTO \"BSS_PPM_IND_SUMMARY\"\n\t\t\t\t\t\t\t(\n\t\t\t\t\t\t\t\t\"ID\",\n\t\t\t\t\t\t\t\t\"BELONG_ENTERPRISE_ID\",\n\t\t\t\t\t\t\t\t\"CONTRACT_ID\",\n\t\t\t\t\t\t\t\t\"ENTERPRISE_ID\",\n\t\t\t\t\t\t\t\t\"ENTERPRISE_NAME\",\n\t\t\t\t\t\t\t\t\"ENTERPRISE_TYPE\",\n\t\t\t\t\t\t\t\t\"INDICATOR_CODE\",\n\t\t\t\t\t\t\t\t\"INDICATOR_ID\",\n\t\t\t\t\t\t\t\t\"MANAGEMENT_UNIT\",\n\t\t\t\t\t\t\t\t\"PROJECT_ID\",\n\t\t\t\t\t\t\t\t\"INDICATOR_END_TIME\",\n\t\t\t\t\t\t\t\t\"INDICATOR_START_TIME\",\n\t\t\t\t\t\t\t\t\"INDICATOR_LEVEL\"\n\t\t\t\t\t\t\t)\n\t\t\t\t\t\t\tVALUES(\n\t\t\t\t\t\t\t\trandom_string(64),\n\t\t\t\t\t\t\t\trec.\"BELONG_ENTERPRISE_ID\",\n\t\t\t\t\t\t\t\trec.\"CONTRACT_ID\",\n\t\t\t\t\t\t\t\trec.\"ENTERPRISE_ID\",\n\t\t\t\t\t\t\t\trec.\"ENTERPRISE_NAME\",\n\t\t\t\t\t\t\t\trec.\"ENTERPRISE_TYPE\",\n\t\t\t\t\t\t\t\trec.\"INDICATOR_CODE\",\n\t\t\t\t\t\t\t\trec.\"INDICATOR_ID\",\n\t\t\t\t\t\t\t\trec.\"MANAGEMENT_UNIT\",\n\t\t\t\t\t\t\t\trec.\"PROJECT_ID\",\n\t\t\t\t\t\t\t\t'9999-12-31',\n\t\t\t\t\t\t\t\trec.\"CREATED_TS\",\n\t\t\t\t\t\t\t\trec.caculateIndicatorLight\n\t\t\t\t\t\t\t);\n\t\t\t\t\t\tend if;\n\t\t\t\t\t\texistkey:= 0;\n\t\t\t\tEND LOOP;\n\t\t\t\t\n\t\t\t\t-- \u6e38\u6807\u5f52\u4f4d\n\t\t\t\tMOVE BACKWARD ALL from oldrecords;\n\t\t\t\tMOVE BACKWARD ALL from records;\n\t\t\t\t\n\t\t\t\t-- \u91cd\u7f6e existkey\n\t\t\t\trecordkey:= '';\n\t\t\t\trecordkey1:= '';\n\t\t\t\texistkey:= 0;\n\t\t\t\t-- **** \u904d\u5386\u8001\u6570\u636e\uff0c1.\u5982\u679c\u6709\u72b6\u6001\u4e0d\u540c\u7684\uff0c\u6d41\u8f6c\u4e00\u4e0b\uff0c2.\u5982\u679c\u6709\u4e4b\u524d\u62a5\u8b66\u73b0\u5728\u8ba1\u7b97\u6ca1\u6709\u7684\uff0c\u5904\u7f6e\u4e00\u4e0b **** \n\t\t\t\tLOOP\n\t\t\t\t\t\tFETCH oldrecords INTO oldrec;\n\t\t\t\t\t\tEXIT WHEN NOT FOUND;\n\t\t\t\t\t\trecordkey:= oldrec.\"INDICATOR_CODE\" || '_' || oldrec.\"ENTERPRISE_ID\" || '_'  || oldrec.\"ENTERPRISE_TYPE\" || '_'  || oldrec.\"CONTRACT_ID\" || '_' || oldrec.\"PROJECT_ID\";\n\t\t\t\t\t\tLOOP\n\t\t\t\t\t\t\tFETCH records INTO rec;\n\t\t\t\t\t\t\tEXIT WHEN NOT FOUND;\n\t\t\t\t\t\t\trecordkey1:= rec.\"INDICATOR_CODE\" || '_' || rec.\"ENTERPRISE_ID\" || '_'  || rec.\"ENTERPRISE_TYPE\" || '_'  || rec.\"CONTRACT_ID\" || '_' || rec.\"PROJECT_ID\";\n\n\t\t\t\t\t\t\tif recordkey = recordkey1 then\n\t\t\t\t\t\t\t\texistkey:= 1;\n\t\t\t\t\t\t\t\t-- \u65b0\u8001\u6570\u636e\u72b6\u6001\u4e0d\u4e00\u81f4\uff0c\u4fee\u6539\u8001\u8bb0\u5f55\u3001\u65b0\u589e\u4e00\u6761\u8bb0\u5f55\n\t\t\t\t\t\t\t\tif oldrec.\"INDICATOR_LEVEL\" != rec.caculateIndicatorLight and oldrec.\"INDICATOR_LEVEL\" != '\u5df2\u5904\u7f6e' then\n\t\t\t\t\t\t\t\t\t-- \u5173\u95ed\u8001\u8bb0\u5f55\n\t\t\t\t\t\t\t\t\tupdate \"BSS_PPM_IND_SUMMARY\" \n\t\t\t\t\t\t\t\t\tset \"INDICATOR_END_TIME\" = TO_DATE(datestr, 'yyyy-MM-dd hh24:mi:ss')\n\t\t\t\t\t\t\t\t\twhere \"ID\" = oldrec.\"ID\";\n\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t-- \u63d2\u5165\u65b0\u8bb0\u5f55\n\t\t\t\t\t\t\t\t\tINSERT INTO \"BSS_PPM_IND_SUMMARY\"\n\t\t\t\t\t\t\t\t\t(\n\t\t\t\t\t\t\t\t\t\t\"ID\",\n\t\t\t\t\t\t\t\t\t\t\"BELONG_ENTERPRISE_ID\",\n\t\t\t\t\t\t\t\t\t\t\"CONTRACT_ID\",\n\t\t\t\t\t\t\t\t\t\t\"ENTERPRISE_ID\",\n\t\t\t\t\t\t\t\t\t\t\"ENTERPRISE_NAME\",\n\t\t\t\t\t\t\t\t\t\t\"ENTERPRISE_TYPE\",\n\t\t\t\t\t\t\t\t\t\t\"INDICATOR_CODE\",\n\t\t\t\t\t\t\t\t\t\t\"INDICATOR_ID\",\n\t\t\t\t\t\t\t\t\t\t\"MANAGEMENT_UNIT\",\n\t\t\t\t\t\t\t\t\t\t\"PROJECT_ID\",\n\t\t\t\t\t\t\t\t\t\t\"INDICATOR_END_TIME\",\n\t\t\t\t\t\t\t\t\t\t\"INDICATOR_START_TIME\",\n\t\t\t\t\t\t\t\t\t\t\"INDICATOR_LEVEL\"\n\t\t\t\t\t\t\t\t\t)\n\t\t\t\t\t\t\t\t\tVALUES(\n\t\t\t\t\t\t\t\t\t\trandom_string(64),\n\t\t\t\t\t\t\t\t\t\trec.\"BELONG_ENTERPRISE_ID\",\n\t\t\t\t\t\t\t\t\t\trec.\"CONTRACT_ID\",\n\t\t\t\t\t\t\t\t\t\trec.\"ENTERPRISE_ID\",\n\t\t\t\t\t\t\t\t\t\trec.\"ENTERPRISE_NAME\",\n\t\t\t\t\t\t\t\t\t\trec.\"ENTERPRISE_TYPE\",\n\t\t\t\t\t\t\t\t\t\trec.\"INDICATOR_CODE\",\n\t\t\t\t\t\t\t\t\t\trec.\"INDICATOR_ID\",\n\t\t\t\t\t\t\t\t\t\trec.\"MANAGEMENT_UNIT\",\n\t\t\t\t\t\t\t\t\t\trec.\"PROJECT_ID\",\n\t\t\t\t\t\t\t\t\t\t'9999-12-31',\n\t\t\t\t\t\t\t\t\t\trec.\"CREATED_TS\",\n\t\t\t\t\t\t\t\t\t\trec.caculateIndicatorLight\n\t\t\t\t\t\t\t\t\t);\n\t\t\t\t\t\t\t\tend if;\n\t\t\t\t\t\t\tEND IF;\n\t\t\t\t\t\tEND LOOP;\n\t\t\t\t\t\t-- \u6e38\u6807\u5f52\u4f4d\n\t\t\t\t\t\tMOVE BACKWARD ALL from records;\n\t\t\t\t\t\t-- \u8001\u8bb0\u5f55\u6709\uff0c\u65b0\u8bb0\u5f55\u6ca1\u6709\uff0c\u5173\u95ed\u539f\u6765\u7684\u72b6\u6001\uff0c\u589e\u52a0\u4e00\u6761\u8bb0\u5f55\uff0c\u9884\u8b66\u7b49\u7ea7\u4e3a\u5df2\u5904\u7f6e\n\t\t\t\t\t\tif existkey = 0 then\n\t\t\t\t\t\t\tif oldrec.\"INDICATOR_LEVEL\" = '\u5df2\u5904\u7f6e' THEN\n\t\t\t\t\t\t\t\tRETURN;\n\t\t\t\t\t\t\tEND IF;\n\t\t\t\t\t\t\t-- \u5173\u95ed\u539f\u6765\u7684\u8bb0\u5f55\n\t\t\t\t\t\t\tupdate \"BSS_PPM_IND_SUMMARY\" \n\t\t\t\t\t\t\tset \"INDICATOR_END_TIME\" = TO_DATE(datestr, 'yyyy-MM-dd hh24:mi:ss')\n\t\t\t\t\t\t\twhere \"ID\" = oldrec.\"ID\";\n\t\t\t\t\t\t\t\n\t\t\t\t\t\t\tselect * into handlerec from \"BSS_PPM_IND_SUMMARY\" \n\t\t\t\t\t\t\twhere \n\t\t\t\t\t\t\t\"INDICATOR_CODE\" = oldrec.\"INDICATOR_CODE\" \n\t\t\t\t\t\t\tand \"PROJECT_ID\" = oldrec.\"PROJECT_ID\"\n\t\t\t\t\t\t\tand \"CONTRACT_ID\" = oldrec.\"CONTRACT_ID\"\n\t\t\t\t\t\t\tand \"ENTERPRISE_TYPE\" = oldrec.\"ENTERPRISE_TYPE\"\n\t\t\t\t\t\t\tand \"INDICATOR_LEVEL\" = '\u5df2\u5904\u7f6e' and DATE_FORMAT ( \"INDICATOR_START_TIME\", '%Y-%m-%d') = datestr;\n\t\t\t\t\t\t\t\n\t\t\t\t\t\t\tIF handlerec.\"ID\" is null then \n\t\t\t\t\t\t\t\t-- \u65b0\u589e\u4e00\u6761\u5904\u7f6e\u7684\u8bb0\u5f55\n\t\t\t\t\t\t\t\tINSERT INTO \"BSS_PPM_IND_SUMMARY\"\n\t\t\t\t\t\t\t\t(\n\t\t\t\t\t\t\t\t\t\"ID\",\n\t\t\t\t\t\t\t\t\t\"BELONG_ENTERPRISE_ID\",\n\t\t\t\t\t\t\t\t\t\"CONTRACT_ID\",\n\t\t\t\t\t\t\t\t\t\"ENTERPRISE_ID\",\n\t\t\t\t\t\t\t\t\t\"ENTERPRISE_NAME\",\n\t\t\t\t\t\t\t\t\t\"ENTERPRISE_TYPE\",\n\t\t\t\t\t\t\t\t\t\"INDICATOR_CODE\",\n\t\t\t\t\t\t\t\t\t\"INDICATOR_ID\",\n\t\t\t\t\t\t\t\t\t\"MANAGEMENT_UNIT\",\n\t\t\t\t\t\t\t\t\t\"PROJECT_ID\",\n\t\t\t\t\t\t\t\t\t\"INDICATOR_END_TIME\",\n\t\t\t\t\t\t\t\t\t\"INDICATOR_START_TIME\",\n\t\t\t\t\t\t\t\t\t\"INDICATOR_LEVEL\"\n\t\t\t\t\t\t\t\t)\n\t\t\t\t\t\t\t\tVALUES(\n\t\t\t\t\t\t\t\t\trandom_string(64),\n\t\t\t\t\t\t\t\t\toldrec.\"BELONG_ENTERPRISE_ID\",\n\t\t\t\t\t\t\t\t\toldrec.\"CONTRACT_ID\",\n\t\t\t\t\t\t\t\t\toldrec.\"ENTERPRISE_ID\",\n\t\t\t\t\t\t\t\t\toldrec.\"ENTERPRISE_NAME\",\n\t\t\t\t\t\t\t\t\toldrec.\"ENTERPRISE_TYPE\",\n\t\t\t\t\t\t\t\t\toldrec.\"INDICATOR_CODE\",\n\t\t\t\t\t\t\t\t\toldrec.\"INDICATOR_ID\",\n\t\t\t\t\t\t\t\t\toldrec.\"MANAGEMENT_UNIT\",\n\t\t\t\t\t\t\t\t\toldrec.\"PROJECT_ID\",\n\t\t\t\t\t\t\t\t\t'9999-12-31',\n\t\t\t\t\t\t\t\t\tTO_DATE(datestr, 'yyyy-MM-dd hh24:mi:ss'),\n\t\t\t\t\t\t\t\t\t'\u5df2\u5904\u7f6e'\n\t\t\t\t\t\t\t\t);\n\t\t\t\t\t\t\tend if;\n\t\t\t\t\t\t\texistkey:= 0;\n\t\t\t\t\t\tend if;\n\t\t\t\tEND LOOP;\n\t\t\t\t\n\t\t\t\tCLOSE oldrecords;\n\t\t\t\tCLOSE records;\n\t\t\t \n\t\t\t\t-- COMMIT;\n    end;\n$BODY$\n  LANGUAGE plpgsql<\/code><\/pre>\n\n\n\n<p><\/p>\n","protected":false},"excerpt":{"rendered":"<p>1.\u6ce8\u610f\u6e38\u6807\u7684\u5904\u7406 2.\u6ce8\u610f\u5b58\u50a8\u8fc7\u7a0b\u4e5f\u53ef\u4ee5\u6253\u5370\u8f93\u51fa\uff0c\u65b9\u4fbf\u8c03\u8bd5<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[117,12],"tags":[],"_links":{"self":[{"href":"https:\/\/www.zeroheart.xyz\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/1473"}],"collection":[{"href":"https:\/\/www.zeroheart.xyz\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.zeroheart.xyz\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.zeroheart.xyz\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.zeroheart.xyz\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1473"}],"version-history":[{"count":2,"href":"https:\/\/www.zeroheart.xyz\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/1473\/revisions"}],"predecessor-version":[{"id":1475,"href":"https:\/\/www.zeroheart.xyz\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/1473\/revisions\/1475"}],"wp:attachment":[{"href":"https:\/\/www.zeroheart.xyz\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1473"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.zeroheart.xyz\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1473"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.zeroheart.xyz\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1473"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}