From 374ce4ffd0c459bb4067e8d5765f972668aff9b1 Mon Sep 17 00:00:00 2001
From: wzp <2040239371@qq.com>
Date: 星期四, 02 三月 2023 14:56:23 +0800
Subject: [PATCH] 变更oracle访问组件
---
web/Dao/GwOrderDao.cs | 89 ++++++++++++++++++++++++++++++++++++++++----
1 files changed, 80 insertions(+), 9 deletions(-)
diff --git a/web/Dao/GwOrderDao.cs b/web/Dao/GwOrderDao.cs
index 1d1944e..7dcdcd4 100644
--- a/web/Dao/GwOrderDao.cs
+++ b/web/Dao/GwOrderDao.cs
@@ -4,7 +4,7 @@
using System.Text;
using Model;
using Common;
-using Oracle.DataAccess.Client;
+using Oracle.ManagedDataAccess.Client;
using System.Data.Common;
namespace Dao
@@ -41,8 +41,9 @@
{
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append(" FROM ( ");
- stringBuilder.Append("SELECT GWO.*, GWC.CLIENT_NAME, GWC.COMPANY ");
+ stringBuilder.Append("SELECT GWO.*, GWC.CLIENT_NAME, GWC.COMPANY, GWOA.AUDITOR ");
stringBuilder.Append(" FROM GW_ORDER GWO ");
+ stringBuilder.Append(" LEFT JOIN (select ORDER_ID, wm_concat(SU.USER_NAME) AUDITOR from (select ORDER_ID, AUDITOR FROM GW_ORDER_AUDIT order by ORDER_ID, AUDIT_SORT) GOA LEFT JOIN SYS_USER SU ON SU.ACCOUNT = GOA.AUDITOR group by ORDER_ID ) GWOA ON GWOA.ORDER_ID = GWO.ORDER_ID ");
stringBuilder.Append(" LEFT JOIN GW_SP GWS ON GWS.SP_ID = GWO.SP_ID ");
stringBuilder.Append(" LEFT JOIN GW_CLIENT GWC ON GWC.CLIENT_ID = GWO.CLIENT_ID ");
stringBuilder.Append(" WHERE 1=1 ");
@@ -168,7 +169,7 @@
}
- //娣诲姞浜у搧鎴栦骇鍝佸垎绫讳俊鎭�
+ //娣诲姞淇℃伅
public bool Add(GwOrder o)
{
StringBuilder stringBuilder = new StringBuilder();
@@ -223,24 +224,23 @@
}
//鏇存柊璁㈠崟淇敼鍓嶄綑棰�
- public bool UpdateBalanceAndStatus(int basicNum, int givingNum, int orderNumTotal, int orderAmountTotal, int payAmount , int beforeGivingNum, int orderStatus, string orderId )
+ public bool UpdateBalanceAndStatus(Int64 basicNum, Int64 givingNum, Int64 orderNumTotal, Int64 orderAmountTotal, Int64 payAmount , Int64 beforeGivingNum, int orderStatus, string orderId )
{
if (string.IsNullOrEmpty(orderId))
return false;
//status:4-鍙栨秷
- return OracleHelper.ExecuteSql("update GW_ORDER SET BASIC_NUM=:BASIC_NUM, GIVING_NUM=:GIVING_NUM, ORDER_NUM_TOTAL=:ORDER_NUM_TOTAL, ORDER_AMOUNT_TOTAL=:ORDER_AMOUNT_TOTAL, PAY_AMOUNT=:PAY_AMOUNT, BEFORE_BALANCE_NUM=:BEFORE_BALANCE_NUM, STATUS = :STATUS where ORDER_ID=:ORDER_ID", OracleHelper.Connection
+ return OracleHelper.ExecuteSql("update GW_ORDER SET BASIC_NUM=:BASIC_NUM, GIVING_NUM=:GIVING_NUM, ORDER_NUM_TOTAL=:ORDER_NUM_TOTAL, ORDER_AMOUNT_TOTAL=:ORDER_AMOUNT_TOTAL, PAY_AMOUNT=:PAY_AMOUNT, STATUS = :STATUS where ORDER_ID=:ORDER_ID", OracleHelper.Connection
, new OracleParameter(":BASIC_NUM", (object)basicNum)
, new OracleParameter(":GIVING_NUM", (object)givingNum)
, new OracleParameter(":ORDER_NUM_TOTAL", (object)orderNumTotal)
, new OracleParameter(":ORDER_AMOUNT_TOTAL", (object)orderAmountTotal)
, new OracleParameter(":PAY_AMOUNT", (object)payAmount)
- , new OracleParameter(":BEFORE_BALANCE_NUM", (object)beforeGivingNum)
, new OracleParameter(":STATUS", (object)orderStatus)
, new OracleParameter(":ORDER_ID", (object)orderId)
) > 0;
}
- //鏇存柊浜у搧鎴栦骇鍝佸垎绫讳俊鎭�
+ //鏇存柊淇℃伅
public bool Update(GwOrder o)
{
OracleParameter[] cmdParms = new OracleParameter[] {
@@ -284,7 +284,7 @@
GwOrder o = new GwOrder();
if (string.IsNullOrEmpty(orderId))
return o;
- using (OracleDataReader reader = OracleHelper.ExecuteReader(string.Format("select * from GW_ORDER where ORDER_ID=:ORDER_ID"), OracleHelper.Connection, new OracleParameter(":ORDER_ID", (object)orderId)))
+ using (OracleDataReader reader = OracleHelper.ExecuteReader(string.Format("select gwo.*, gc.Client_Name, gp.name PRODUCT_NAME from gw_order gwo left join gw_client gc on gc.client_id = gwo.client_id left join gw_product gp on gp.id = gwo.product_id where gwo.ORDER_ID=:ORDER_ID"), OracleHelper.Connection, new OracleParameter(":ORDER_ID", (object)orderId)))
{
if (((DbDataReader)reader).Read())
{
@@ -293,6 +293,76 @@
}
}
return o;
+ }
+
+ //浠婃棩宸插畬鎴愯鍗曟暟鍙婃�婚
+ public List<Dictionary<string, object>> GetOrderDay(string permissionsSQL)
+ {
+ List<Dictionary<string, object>> list = new List<Dictionary<string, object>>();
+ StringBuilder builder = new StringBuilder();
+ builder.Append(" SELECT COUNT(1) order_num, SUM(order_amount_total) order_amount_total FROM ( ");
+ builder.Append(" SELECT a.order_id, a.client_id, a.status, a.order_amount_total ");
+ builder.Append(" , CASE WHEN b.update_time is null then TO_CHAR(a.create_time, 'yyyyMMdd') ELSE TO_CHAR(b.update_time, 'yyyyMMdd') END AS update_time ");
+ builder.Append(" FROM gw_order a ");
+ builder.Append(" LEFT JOIN ( ");
+ builder.Append(" SELECT order_id, max(audit_time) update_time FROM gw_order_audit goa ");
+ builder.Append(" GROUP BY order_id ");
+ builder.Append($" ) b ON b.order_id = a.order_id where update_time>=TO_date('{DateTime.Now.Date}','yyyy-MM-dd HH24:mi:ss') ");
+ builder.Append(" ) ");
+ builder.Append(" WHERE 1=1 AND status=2 ");
+ //builder.Append(" AND update_time=:UPDATE_TIME ");
+ builder.Append(permissionsSQL);
+
+ //using (OracleDataReader reader = OracleHelper.ExecuteReader(builder.ToString(), OracleHelper.Connection, new OracleParameter(":UPDATE_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);
+ int orderNum = oracleReaderWrapper.GetInt("ORDER_NUM", 0);
+ int orderAmountTotal = oracleReaderWrapper.GetInt("ORDER_AMOUNT_TOTAL", 0);
+
+ Dictionary<string, object> map = new Dictionary<string, object>();
+ map.Add("orderNum", orderNum);
+ map.Add("orderAmountTotal", orderAmountTotal);
+ list.Add(map);
+ }
+ }
+ return list;
+ }
+
+ //寰呭鏍歌鍗曟暟鍙婃�婚
+ public List<Dictionary<string, object>> GetOrderAudit(string permissionsSQL)
+ {
+ List<Dictionary<string, object>> list = new List<Dictionary<string, object>>();
+ StringBuilder builder = new StringBuilder();
+ builder.Append(" SELECT COUNT(1) order_num, SUM(order_amount_total) order_amount_total FROM ( ");
+ builder.Append(" SELECT a.order_id, a.client_id, a.status, a.order_amount_total ");
+ //builder.Append(" , CASE WHEN b.update_time is null then TO_CHAR(a.create_time, 'yyyyMMdd') ELSE TO_CHAR(b.update_time, 'yyyyMMdd') END AS update_time ");
+ builder.Append(" FROM gw_order a ");
+ builder.Append(" LEFT JOIN ( ");
+ builder.Append(" SELECT order_id, max(audit_time) update_time FROM gw_order_audit goa ");
+ builder.Append(" GROUP BY order_id ");
+ builder.Append(" ) b ON b.order_id = a.order_id ");
+ builder.Append(" ) ");
+ builder.Append(" WHERE 1=1 AND status IN (0,1) ");
+ builder.Append(permissionsSQL);
+
+ using (OracleDataReader reader = OracleHelper.ExecuteReader(builder.ToString(), OracleHelper.Connection))
+ {
+ while (reader != null && ((DbDataReader)reader).Read())
+ {
+ OracleReaderWrapper oracleReaderWrapper = new OracleReaderWrapper(reader);
+ int orderNum = oracleReaderWrapper.GetInt("ORDER_NUM", 0);
+ int orderAmountTotal = oracleReaderWrapper.GetInt("ORDER_AMOUNT_TOTAL", 0);
+
+ Dictionary<string, object> map = new Dictionary<string, object>();
+ map.Add("orderNum", orderNum);
+ map.Add("orderAmountTotal", orderAmountTotal);
+ list.Add(map);
+ }
+ }
+ return list;
}
//缁熻璁板綍鏁�
@@ -312,6 +382,7 @@
o.Company = oracleReaderWrapper.GetString("COMPANY", "");
o.ClientName = oracleReaderWrapper.GetString("CLIENT_NAME", "");
o.ProductId = oracleReaderWrapper.GetString("PRODUCT_ID", "");
+ o.ProductName = oracleReaderWrapper.GetString("PRODUCT_NAME", "");
o.ComboId = oracleReaderWrapper.GetString("COMBO_ID", "");
o.BasicNum = oracleReaderWrapper.GetInt("BASIC_NUM", 0);
o.GivingNum = oracleReaderWrapper.GetInt("GIVING_NUM", 0);
@@ -322,7 +393,7 @@
o.BeforeBalanceNum = oracleReaderWrapper.GetInt("BEFORE_BALANCE_NUM", 0);
o.Creator = oracleReaderWrapper.GetString("CREATOR", "");
o.CreateTime = oracleReaderWrapper.GetDateTime("CREATE_TIME");
- //o.Auditor = oracleReaderWrapper.GetString("AUDITOR", "");
+ o.Auditor = oracleReaderWrapper.GetString("AUDITOR", ""); //鍚勭骇瀹℃牳浜�
//o.AuditTime = oracleReaderWrapper.GetDateTime("AUDIT_TIME");
o.Status = oracleReaderWrapper.GetInt("STATUS", 0);
o.Remark = oracleReaderWrapper.GetString("REMARK", "");
--
Gitblit v1.9.1