// Decompiled with JetBrains decompiler // Type: Dao.GwStatisV3Dao // Assembly: Dao, Version=3.2.1.428, Culture=neutral, PublicKeyToken=c1a16487c920c3dc // MVID: 22E6EB3F-6D25-4B57-BE09-AD2F5391CFA5 // Assembly location: C:\Users\Administrator\Desktop\wwww\bin\Dao.dll using Common; using Newtonsoft.Json; using Newtonsoft.Json.Linq; using Oracle.DataAccess.Client; using System; using System.Collections.Generic; using System.Data; using System.Data.Common; using Model; namespace Dao { public class GwClientProductDao : IDisposable { /** * 根据客户ID获取客户产品列表 */ public List getClientProduct(string clientID) { string sql = ""; sql += "select aa.*, c.balance, (c.basic_num + c.giving_num ) available_num from " + "( " + " select a.id client_product_id, a.client_id, a.sp_id, a.activate_status, " + " b.id product_id, b.name, b.icon, b.instructions, b.classes, b.parent_id, " + " b.path, b.is_default, b.is_enable, b.sort, b.giving_num, b.remark, b.api_sp_xh_id " + " from gw_client_product a, gw_product b " + " where a.product_id = b.id " + " ) aa " + " left join gw_sp c " + " on aa.sp_id = c.sp_id and aa.client_id = :clientId " //参数：客户ID + " order by sort "; //return OracleHelper.Execute(sql, OracleHelper.Connection, new OracleParameter(":clientId", (object)clientID)); List list = new List(); using (OracleDataReader reader = OracleHelper.ExecuteReader(sql, OracleHelper.Connection, new OracleParameter(":clientId", (object)clientID))) { while (reader != null && ((DbDataReader)reader).Read()) { GwClientProduct gwClientProduct = new GwClientProduct(); OracleReaderWrapper oracleReaderWrapper = new OracleReaderWrapper(reader); gwClientProduct.client_product_id = oracleReaderWrapper.GetString("client_product_id", ""); gwClientProduct.client_id = oracleReaderWrapper.GetString("client_id", ""); gwClientProduct.sp_id = oracleReaderWrapper.GetString("sp_id", ""); gwClientProduct.activate_status = oracleReaderWrapper.GetInt("activate_status", 0); gwClientProduct.product_id = oracleReaderWrapper.GetString("product_id", ""); gwClientProduct.product_name = oracleReaderWrapper.GetString("name", ""); gwClientProduct.icon = oracleReaderWrapper.GetString("icon", ""); gwClientProduct.instructions = oracleReaderWrapper.GetString("instructions", ""); gwClientProduct.classes = oracleReaderWrapper.GetInt("classes", 0); gwClientProduct.parent_id = oracleReaderWrapper.GetString("parent_id", ""); gwClientProduct.path = oracleReaderWrapper.GetString("path", ""); gwClientProduct.is_default = oracleReaderWrapper.GetInt("is_default", 0); gwClientProduct.is_enable = oracleReaderWrapper.GetInt("is_enable", 0); gwClientProduct.sort = oracleReaderWrapper.GetInt("sort", 0); gwClientProduct.balance = oracleReaderWrapper.GetInt("balance", 0); gwClientProduct.giving_num = oracleReaderWrapper.GetInt("giving_num", 0); gwClientProduct.available_num = oracleReaderWrapper.GetInt("available_num", 0); gwClientProduct.remark = oracleReaderWrapper.GetString("remark", ""); gwClientProduct.api_sp_xh_id = oracleReaderWrapper.GetString("api_sp_xh_id", ""); list.Add(gwClientProduct); } } return list; } public bool Add(GwClientProduct o) { return OracleHelper.ExecuteSql("INSERT INTO GW_CLIENT_PRODUCT(ID, CLIENT_ID, SP_ID, PRODUCT_ID, ACTIVATE_STATUS) VALUES(:ID,:CLIENT_ID,:SP_ID,:PRODUCT_ID,:ACTIVATE_STATUS)", OracleHelper.Connection, new OracleParameter(":ID", (object)o.client_product_id), new OracleParameter(":CLIENT_ID", (object)o.client_id), new OracleParameter(":SP_ID", (object)o.sp_id), new OracleParameter(":PRODUCT_ID", (object)o.product_id), new OracleParameter(":ACTIVATE_STATUS", (object)o.activate_status) ) > 0; } public bool update(GwClientProduct o) { string sql = ""; sql += " update gw_client_product set CLIENT_ID=:CLIENT_ID, SP_ID=:SP_ID, PRODUCT_ID=:PRODUCT_ID,ACTIVATE_STATUS=:ACTIVATE_STATUS "; sql += " where ID=:ID "; return OracleHelper.ExecuteSql(sql, OracleHelper.Connection, new OracleParameter(":CLIENT_ID", (object)o.client_id), new OracleParameter(":SP_ID", (object)o.sp_id), new OracleParameter(":PRODUCT_ID", (object)o.product_id), new OracleParameter(":ACTIVATE_STATUS", (object)o.activate_status), new OracleParameter(":ID", (object)o.client_product_id) ) > 0; } /** * 根据客户ID获取客户产品列表 */ public GwClientProduct find(GwClientProduct o) { string sql = ""; //sql += "select * from Gw_Client_Product a where a.id = :client_product_id "; sql += " select a.id client_product_id, a.client_id, a.sp_id, a.activate_status "; sql += " , b.id product_id, b.name product_name, b.icon ,b.instructions, b.classes, b.parent_id "; sql += " , b.path, b.is_default, b.is_enable, b.sort, b.giving_num, b.remark, b.api_sp_xh_id "; sql += " from gw_client_product a "; sql += " left join gw_product b "; sql += " on a.product_id = b.id "; sql += " where b.is_enable = :is_enable and a.id = :client_product_id "; //return OracleHelper.Execute(sql, OracleHelper.Connection, new OracleParameter(":clientId", (object)clientID)); GwClientProduct gwClientProduct = new GwClientProduct(); using (OracleDataReader reader = OracleHelper.ExecuteReader(sql, OracleHelper.Connection, new OracleParameter(":is_enable", (object)o.is_enable), new OracleParameter(":client_product_id", (object)o.client_product_id) )) { if (((DbDataReader)reader).Read()) { this.ReadInfo(reader, gwClientProduct); return gwClientProduct; } } return gwClientProduct; } //数据转换封装 private bool ReadInfo(OracleDataReader reader, GwClientProduct gwClientProduct) { OracleReaderWrapper oracleReaderWrapper = new OracleReaderWrapper(reader); gwClientProduct.client_product_id = oracleReaderWrapper.GetString("client_product_id", ""); gwClientProduct.client_id = oracleReaderWrapper.GetString("client_id", ""); gwClientProduct.sp_id = oracleReaderWrapper.GetString("sp_id", ""); gwClientProduct.activate_status = oracleReaderWrapper.GetInt("activate_status", 0); gwClientProduct.product_id = oracleReaderWrapper.GetString("product_id", ""); gwClientProduct.product_name = oracleReaderWrapper.GetString("name", ""); gwClientProduct.icon = oracleReaderWrapper.GetString("icon", ""); gwClientProduct.instructions = oracleReaderWrapper.GetString("instructions", ""); gwClientProduct.classes = oracleReaderWrapper.GetInt("classes", 0); gwClientProduct.parent_id = oracleReaderWrapper.GetString("parent_id", ""); gwClientProduct.path = oracleReaderWrapper.GetString("path", ""); gwClientProduct.is_default = oracleReaderWrapper.GetInt("is_default", 0); gwClientProduct.is_enable = oracleReaderWrapper.GetInt("is_enable", 0); gwClientProduct.sort = oracleReaderWrapper.GetInt("sort", 0); gwClientProduct.giving_num = oracleReaderWrapper.GetInt("giving_num", 0); gwClientProduct.remark = oracleReaderWrapper.GetString("remark", ""); gwClientProduct.api_sp_xh_id = oracleReaderWrapper.GetString("api_sp_xh_id", ""); return true; } public void Dispose() { } public DataTable GetClientCount(string clientID) { return OracleHelper.Execute("select * from gw_client_product a where a.client_id = :clientId", OracleHelper.Connection, new OracleParameter(":clientId", (object) clientID) ); } /// /// 检查账户产品是否存在 /// /// 账户 /// public bool IsClientProductExists(string clientID, string productId) { if (string.IsNullOrEmpty(clientID) || string.Equals("0", clientID)) return true; if (string.IsNullOrEmpty(productId) || string.Equals("0", productId)) return true; using (OracleDataReader oracleDataReader = OracleHelper.ExecuteReader(string.Format("select * from GW_CLIENT_PRODUCT where CLIENT_ID=:CLIENT_ID AND PRODUCT_ID=:PRODUCT_ID"), OracleHelper.Connection, new OracleParameter(":CLIENT_ID", (object)clientID), new OracleParameter(":PRODUCT_ID", (object)productId))) { if (((DbDataReader)oracleDataReader).Read()) return true; } return false; } } }