From 1961452ebc018cf758924f26d9c7aa0b31495227 Mon Sep 17 00:00:00 2001
From: wzp <2040239371@qq.com>
Date: 星期二, 25 十月 2022 16:56:13 +0800
Subject: [PATCH] 开发第三版，主要是修复bug
---
web/Dao/GwStatisV3Dao.cs | 171 ++++++++++++++++++++++++++++++++++++++++++++++++++++++--
1 files changed, 163 insertions(+), 8 deletions(-)
diff --git a/web/Dao/GwStatisV3Dao.cs b/web/Dao/GwStatisV3Dao.cs
index b1826b9..dbedbcf 100644
--- a/web/Dao/GwStatisV3Dao.cs
+++ b/web/Dao/GwStatisV3Dao.cs
@@ -1,5 +1,6 @@
锘�
using Common;
+using Model;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Oracle.DataAccess.Client;
@@ -7,21 +8,38 @@
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
+using System.Text;
namespace Dao
{
public class GwStatisV3Dao : IDisposable
{
- public DataTable Query(string clientID)
+ public void Dispose()
+ {
+ }
+
+ public DataTable Query(string clientID)
{
return OracleHelper.Execute("SELECT * FROM\r\n(\r\nselect STATIS_TIME,\r\nNVL(SUM(AP_STATUS0),0) as AP_STATUS0,\r\nNVL(SUM(AP_STATUS1),0) as AP_STATUS1,\r\nNVL(SUM(AP_STATUS2),0) as AP_STATUS2,\r\nNVL(SUM(AP_STATUS3),0) as AP_STATUS3,\r\nNVL(SUM(AP_STATUS4),0) as AP_STATUS4,\r\nNVL(SUM(AP_STATUS5),0) as AP_STATUS5\r\n from GW_SM_STATIS_V3 where CLIENT_ID=:CLIENT_ID GROUP BY STATIS_TIME ORDER BY STATIS_TIME DESC) WHERE ROWNUM < 30 ORDER BY STATIS_TIME ASC", OracleHelper.Connection, new OracleParameter(":CLIENT_ID", (object) clientID));
}
- public List<GwStatisV3Item> Query(DateTime startTime, DateTime endTime, string clientID, string spID, string apID, string opID, string timeExpression, StatisOption so)
+ //public List<GwStatisV3Item> Query(DateTime startTime, DateTime endTime, string clientID, string spID, string apID, string opID, string timeExpression, StatisOption so)
+ public List<GwStatisV3Item> Query(DateTime startTime, DateTime endTime, string clientID, string spID, string apID, string opID, string timeExpression, string permissionsSQL, StatisOption so)
{
List<GwStatisV3Item> list = new List<GwStatisV3Item>();
- using (OracleDataReader reader = OracleHelper.ExecuteReader(string.Format("SELECT TO_CHAR(STATIS_TIME,'{0}') AS TITLE {1}{2}{3}{4},\r\nSUM(AP_STATUS0) AP_STATUS0,\r\nSUM(AP_STATUS1) AP_STATUS1,\r\nSUM(AP_STATUS2) AP_STATUS2,\r\nSUM(AP_STATUS3) AP_STATUS3,\r\nSUM(AP_STATUS4) AP_STATUS4,\r\nSUM(AP_STATUS5) AP_STATUS5,\r\nSUM(OP_STATUS0) OP_STATUS0,\r\nSUM(OP_STATUS1) OP_STATUS1,\r\nSUM(OP_STATUS2) OP_STATUS2,\r\nSUM(OP_STATUS3) OP_STATUS3,\r\nSUM(OP_STATUS4) OP_STATUS4,\r\nSUM(OP_STATUS5) OP_STATUS5 \r\nFROM GW_SM_STATIS_V3 WHERE (SP_ID=:SP_ID OR :SP_ID IS NULL) AND (CLIENT_ID=:CLIENT_ID OR :CLIENT_ID IS NULL) AND (AP_ID=:AP_ID OR :AP_ID IS NULL) AND (OP_ID=:OP_ID OR :OP_ID IS NULL) AND STATIS_TIME BETWEEN :START_TIME AND :END_TIME GROUP BY TO_CHAR(STATIS_TIME,'{0}') {1}{2}{3}{4} ORDER BY 1 ASC", (object) timeExpression, (so & StatisOption.APID) == StatisOption.APID ? (object) ",AP_ID" : (object) "", (so & StatisOption.OPID) == StatisOption.OPID ? (object) ",OP_ID" : (object) "", (so & StatisOption.ClientID) == StatisOption.ClientID ? (object) ",CLIENT_ID" : (object) "", (so & StatisOption.SpID) == StatisOption.SpID ? (object) ",SP_ID" : (object) ""), OracleHelper.Connection, new OracleParameter(":START_TIME", (object) startTime), new OracleParameter(":END_TIME", (object) endTime), new OracleParameter(":SP_ID", (object) spID), new OracleParameter(":CLIENT_ID", (object) clientID), new OracleParameter(":AP_ID", (object) apID), new OracleParameter(":OP_ID", (object) opID)))
- {
+ //using (OracleDataReader reader = OracleHelper.ExecuteReader(string.Format("SELECT TO_CHAR(STATIS_TIME,'{0}') AS TITLE {1}{2}{3}{4},\r\nSUM(AP_STATUS0) AP_STATUS0,\r\nSUM(AP_STATUS1) AP_STATUS1,\r\nSUM(AP_STATUS2) AP_STATUS2,\r\nSUM(AP_STATUS3) AP_STATUS3,\r\nSUM(AP_STATUS4) AP_STATUS4,\r\nSUM(AP_STATUS5) AP_STATUS5,\r\nSUM(OP_STATUS0) OP_STATUS0,\r\nSUM(OP_STATUS1) OP_STATUS1,\r\nSUM(OP_STATUS2) OP_STATUS2,\r\nSUM(OP_STATUS3) OP_STATUS3,\r\nSUM(OP_STATUS4) OP_STATUS4,\r\nSUM(OP_STATUS5) OP_STATUS5 \r\nFROM GW_SM_STATIS_V3 WHERE (SP_ID=:SP_ID OR :SP_ID IS NULL) AND (CLIENT_ID=:CLIENT_ID OR :CLIENT_ID IS NULL) AND (AP_ID=:AP_ID OR :AP_ID IS NULL) AND (OP_ID=:OP_ID OR :OP_ID IS NULL) AND STATIS_TIME BETWEEN :START_TIME AND :END_TIME GROUP BY TO_CHAR(STATIS_TIME,'{0}') {1}{2}{3}{4} ORDER BY 1 ASC", (object) timeExpression, (so & StatisOption.APID) == StatisOption.APID ? (object) ",AP_ID" : (object) "", (so & StatisOption.OPID) == StatisOption.OPID ? (object) ",OP_ID" : (object) "", (so & StatisOption.ClientID) == StatisOption.ClientID ? (object) ",CLIENT_ID" : (object) "", (so & StatisOption.SpID) == StatisOption.SpID ? (object) ",SP_ID" : (object) ""), OracleHelper.Connection, new OracleParameter(":START_TIME", (object) startTime), new OracleParameter(":END_TIME", (object) endTime), new OracleParameter(":SP_ID", (object) spID), new OracleParameter(":CLIENT_ID", (object) clientID), new OracleParameter(":AP_ID", (object) apID), new OracleParameter(":OP_ID", (object) opID)))
+
+
+ StringBuilder stringBuilder = new StringBuilder();
+ stringBuilder.Append(string.Format("SELECT TO_CHAR(STATIS_TIME,'{0}') AS TITLE {1}{2}{3}{4},\r\nSUM(AP_STATUS0) AP_STATUS0,\r\nSUM(AP_STATUS1) AP_STATUS1,\r\nSUM(AP_STATUS2) AP_STATUS2,\r\nSUM(AP_STATUS3) AP_STATUS3,\r\nSUM(AP_STATUS4) AP_STATUS4,\r\nSUM(AP_STATUS5) AP_STATUS5,\r\nSUM(OP_STATUS0) OP_STATUS0,\r\nSUM(OP_STATUS1) OP_STATUS1,\r\nSUM(OP_STATUS2) OP_STATUS2,\r\nSUM(OP_STATUS3) OP_STATUS3,\r\nSUM(OP_STATUS4) OP_STATUS4,\r\nSUM(OP_STATUS5) OP_STATUS5 \r\nFROM GW_SM_STATIS_V3 WHERE (SP_ID=:SP_ID OR :SP_ID IS NULL) AND (CLIENT_ID=:CLIENT_ID OR :CLIENT_ID IS NULL) AND (AP_ID=:AP_ID OR :AP_ID IS NULL) AND (OP_ID=:OP_ID OR :OP_ID IS NULL) AND STATIS_TIME BETWEEN :START_TIME AND :END_TIME GROUP BY TO_CHAR(STATIS_TIME,'{0}') {1}{2}{3}{4} ", (object)timeExpression, (so & StatisOption.APID) == StatisOption.APID ? (object)",AP_ID" : (object)"", (so & StatisOption.OPID) == StatisOption.OPID ? (object)",OP_ID" : (object)"", (so & StatisOption.ClientID) == StatisOption.ClientID ? (object)",CLIENT_ID" : (object)"", (so & StatisOption.SpID) == StatisOption.SpID ? (object)",SP_ID" : (object)"") );
+
+ //瀹㈡埛鏉冮檺SQL鑴氭湰
+ stringBuilder.Append(permissionsSQL);
+
+ stringBuilder.Append(" ORDER BY 1 ASC ");
+
+ using (OracleDataReader reader = OracleHelper.ExecuteReader(stringBuilder.ToString(), OracleHelper.Connection, new OracleParameter(":START_TIME", (object)startTime), new OracleParameter(":END_TIME", (object)endTime), new OracleParameter(":SP_ID", (object)spID), new OracleParameter(":CLIENT_ID", (object)clientID), new OracleParameter(":AP_ID", (object)apID), new OracleParameter(":OP_ID", (object)opID)))
+ {
GwStatisV3Item gwStatisV3Item1 = new GwStatisV3Item();
gwStatisV3Item1.Title = "鍚堣";
while (reader != null && ((DbDataReader) reader).Read())
@@ -135,11 +153,148 @@
return list;
}
- public void Dispose()
- {
- }
+ //娲诲姩瀹㈡埛锛氬鎴�-鍙戦�侀噺銆愬綋鏃ャ��
+ public List<Dictionary<string, object>> GetActiveClientDay(string permissionsSQL)
+ {
+ List<Dictionary<string, object> > list = new List<Dictionary<string, object> >();
+ StringBuilder builder = new StringBuilder();
+ builder.Append(" SELECT client_id, company, create_time, SUM(ap_status0 ) ap_status0 FROM ( ");
+ builder.Append(" SELECT V3.client_id, gc.COMPANY, TO_CHAR(V3.create_time, 'yyyyMMdd') create_time, (ap_status0 + ap_status1 + ap_status2 + ap_status3 + ap_status4 + ap_status5) ap_status0 ");
+ builder.Append(" FROM (SELECT * FROM GW_SM_STATIS_V3 ");
+ builder.Append(" WHERE 1=1 ");
+ builder.Append(" AND TO_CHAR(create_time, 'yyyyMMdd')=:CREATE_TIME ");
+ builder.Append( permissionsSQL );
+ builder.Append(" ) V3 ");
+ builder.Append(" LEFT JOIN gw_client gc on gc.client_id = V3.client_id ");
+ builder.Append(" ) aa ");
+ builder.Append(" GROUP BY client_id, company, create_time ");
+ builder.Append(" ORDER BY ap_status0 DESC ");
- public int GetTotalCount(string clientID, DateTime dateTime)
+ /**
+ using (OracleDataReader reader = OracleHelper.ExecuteReader("select count(*) as count from (" + builder.ToString() + ") T", OracleHelper.Connection))
+ {
+ while (reader.Read())
+ {
+ OracleReaderWrapper wrapper = new OracleReaderWrapper(reader);
+ recordcount = wrapper.GetInt("count", 0);
+ }
+ }**/
+ using (OracleDataReader reader = OracleHelper.ExecuteReader(builder.ToString(), OracleHelper.Connection, new OracleParameter(":CREATE_TIME", (object)DateTime.Now.ToString("yyyyMMdd"))) )
+ //using (OracleDataReader reader = OracleHelper.ExecuteReader(builder.ToString(), OracleHelper.Connection))
+ //using (OracleDataReader reader = OracleHelper.ExecuteReader(PubConstant.doOracleSql(PageIndex, pageSize, recordcount, builder.ToString()).ToString() , OracleHelper.Connection ))
+ {
+ while (reader != null && ((DbDataReader)reader).Read())
+ {
+ OracleReaderWrapper oracleReaderWrapper = new OracleReaderWrapper(reader);
+ string clientId = oracleReaderWrapper.GetString("CLIENT_ID", "");
+ string company = oracleReaderWrapper.GetString("COMPANY", "");
+ int ap_status0 = oracleReaderWrapper.GetInt("AP_STATUS0", 0);
+
+ Dictionary<string, object> map = new Dictionary<string, object>();
+ map.Add("clientId", clientId);
+ map.Add("company", company);
+ map.Add("ap_status0", ap_status0);
+ list.Add(map);
+ }
+ }
+ return list;
+ }
+
+ //閫氶亾-鍙戦�侀噺銆愬綋鏃ャ��
+ public List<Dictionary<string, object>> GetOpSendDay(string permissionsSQL)
+ {
+ List<Dictionary<string, object>> list = new List<Dictionary<string, object>>();
+ StringBuilder builder = new StringBuilder();
+ builder.Append(" SELECT OP_ID, OP_NAME, create_time, SUM(op_status0 ) op_status0 FROM ( ");
+ builder.Append(" SELECT V3.op_id, gwo.op_name, TO_CHAR(V3.create_time, 'yyyyMMdd') create_time, (op_status0 + op_status1 + op_status2 + op_status3 + op_status4 + op_status5) op_status0 ");
+ builder.Append(" FROM (SELECT * FROM GW_SM_STATIS_V3 ");
+ builder.Append(" WHERE 1=1 ");
+ builder.Append(" AND TO_CHAR(create_time, 'yyyyMMdd')=:CREATE_TIME ");
+ builder.Append(permissionsSQL);
+ builder.Append(" ) V3 ");
+ builder.Append(" LEFT JOIN gw_op gwo on gwo.op_id = V3.op_id ");
+ builder.Append(" ) aa ");
+ builder.Append(" GROUP BY op_id, op_name, create_time ");
+ builder.Append(" ORDER BY op_status0 DESC ");
+
+ /**
+ using (OracleDataReader reader = OracleHelper.ExecuteReader("select count(*) as count from (" + builder.ToString() + ") T", OracleHelper.Connection))
+ {
+ while (reader.Read())
+ {
+ OracleReaderWrapper wrapper = new OracleReaderWrapper(reader);
+ recordcount = wrapper.GetInt("count", 0);
+ }
+ }**/
+ using (OracleDataReader reader = OracleHelper.ExecuteReader(builder.ToString(), OracleHelper.Connection, new OracleParameter(":CREATE_TIME", (object)DateTime.Now.ToString("yyyyMMdd"))))
+ //using (OracleDataReader reader = OracleHelper.ExecuteReader(builder.ToString(), OracleHelper.Connection))
+ {
+ while (reader != null && ((DbDataReader)reader).Read())
+ {
+ OracleReaderWrapper oracleReaderWrapper = new OracleReaderWrapper(reader);
+ string opId = oracleReaderWrapper.GetString("OP_ID", "");
+ string opName = oracleReaderWrapper.GetString("OP_NAME", "");
+ int op_status0 = oracleReaderWrapper.GetInt("OP_STATUS0", 0);
+
+ Dictionary<string, object> map = new Dictionary<string, object>();
+ map.Add("opId", opId);
+ map.Add("opName", opName);
+ map.Add("op_status0", op_status0);
+ list.Add(map);
+ }
+ }
+ return list;
+ }
+
+ //浜у搧-鍙戦�侀噺銆愬綋鏃ャ��
+ public List<Dictionary<string, object>> GetProductSendDay(string permissionsSQL)
+ {
+ List<Dictionary<string, object>> list = new List<Dictionary<string, object>>();
+ StringBuilder builder = new StringBuilder();
+ builder.Append(" SELECT PRODUCT_ID, PRODUCT_NAME, create_time, SUM(ap_status0 ) ap_status0 FROM ( ");
+ builder.Append(" SELECT gs.product_id, gp.name product_name, TO_CHAR(V3.create_time, 'yyyyMMdd') create_time, (ap_status0 + ap_status1 + ap_status2 + ap_status3 + ap_status4 + ap_status5) ap_status0 ");
+ builder.Append(" FROM (SELECT * FROM GW_SM_STATIS_V3 ");
+ builder.Append(" WHERE 1=1 ");
+ builder.Append(" AND TO_CHAR(create_time, 'yyyyMMdd')=:CREATE_TIME ");
+ builder.Append(permissionsSQL);
+ builder.Append(" ) V3 ");
+ builder.Append(" LEFT JOIN gw_sp gs on gs.sp_id = V3.sp_id ");
+ builder.Append(" LEFT JOIN gw_product gp on gp.id = gs.product_id ");
+ builder.Append(" ) aa ");
+ builder.Append(" GROUP BY PRODUCT_ID, PRODUCT_NAME, create_time ");
+ builder.Append(" ORDER BY ap_status0 DESC ");
+
+ /**
+ using (OracleDataReader reader = OracleHelper.ExecuteReader("select count(*) as count from (" + builder.ToString() + ") T", OracleHelper.Connection))
+ {
+ while (reader.Read())
+ {
+ OracleReaderWrapper wrapper = new OracleReaderWrapper(reader);
+ recordcount = wrapper.GetInt("count", 0);
+ }
+ }**/
+ using (OracleDataReader reader = OracleHelper.ExecuteReader(builder.ToString(), OracleHelper.Connection, new OracleParameter(":CREATE_TIME", (object)DateTime.Now.ToString("yyyyMMdd"))))
+ //using (OracleDataReader reader = OracleHelper.ExecuteReader(builder.ToString(), OracleHelper.Connection))
+ {
+ while (reader != null && ((DbDataReader)reader).Read())
+ {
+ OracleReaderWrapper oracleReaderWrapper = new OracleReaderWrapper(reader);
+ string productId = oracleReaderWrapper.GetString("PRODUCT_ID", "");
+ string productName = oracleReaderWrapper.GetString("PRODUCT_NAME", "");
+ int ap_status0 = oracleReaderWrapper.GetInt("AP_STATUS0", 0);
+
+ Dictionary<string, object> map = new Dictionary<string, object>();
+ map.Add("productId", productId);
+ map.Add("productName", productName);
+ map.Add("ap_status0", ap_status0);
+ list.Add(map);
+ }
+ }
+ return list;
+ }
+
+
+ public int GetTotalCount(string clientID, DateTime dateTime)
{
return Convert.ToInt32(OracleHelper.ExecuteScalar("SELECT NVL(SUM(AP_STATUS0 + AP_STATUS1 + AP_STATUS2 + AP_STATUS3 + AP_STATUS4 + AP_STATUS5),0) FROM GW_SM_STATIS_V3 WHERE CLIENT_ID=:CLIENT_ID AND STATIS_TIME >= :STATIS_TIME", OracleHelper.Connection, new OracleParameter(":CLIENT_ID", (object) clientID), new OracleParameter(":STATIS_TIME", (object) dateTime)));
}
--
Gitblit v1.9.1