// 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;
|
using System.Text;
|
|
namespace Dao
|
{
|
public class GwProductDao : IDisposable
|
{
|
|
public List<GwProduct> LoadInfoList(string name, string classes, string is_default, string op_group_id, int pageIndex, int pageSize, out int recordCount)
|
{
|
List<GwProduct> list1 = new List<GwProduct>();
|
recordCount = 0;
|
StringBuilder stringBuilder = new StringBuilder();
|
stringBuilder.Append("from GW_PRODUCT where 1=1");
|
List<OracleParameter> list2 = new List<OracleParameter>();
|
if (name != null && name != "")
|
{
|
list2.Add(new OracleParameter(":name", (object)name));
|
stringBuilder.Append(" and name=:name");
|
}
|
if (classes != null && classes != "")
|
{
|
list2.Add(new OracleParameter(":classes", (object)int.Parse(classes)) );
|
stringBuilder.Append(" and classes=:classes");
|
}
|
if (is_default != null && is_default != "")
|
{
|
list2.Add(new OracleParameter(":is_default", (object)int.Parse(is_default)));
|
stringBuilder.Append(" and is_default=:is_default");
|
}
|
if (op_group_id != null && op_group_id != "")
|
{
|
list2.Add(new OracleParameter(":op_group_id", (object)int.Parse(op_group_id)));
|
stringBuilder.Append(" and op_group_id=:op_group_id");
|
}
|
|
stringBuilder.Append(" order by PATH ");
|
using (OracleDataReader reader = OracleHelper.ExecuteReader("select count(*) as count " + stringBuilder.ToString(), OracleHelper.Connection, list2.ToArray()))
|
{
|
while (((DbDataReader)reader).Read())
|
recordCount = this.ReadCount(reader);
|
}
|
using (OracleDataReader reader = OracleHelper.ExecuteReader(PubConstant.doOracleSql(pageIndex, pageSize, recordCount, "select * " + stringBuilder.ToString()).ToString(), OracleHelper.Connection, list2.ToArray()))
|
{
|
while (((DbDataReader)reader).Read())
|
{
|
GwProduct o = new GwProduct();
|
if (this.ReadInfo(reader, o))
|
list1.Add(o);
|
}
|
}
|
return list1;
|
}
|
|
private int ReadCount(OracleDataReader reader)
|
{
|
int @int;
|
try
|
{
|
@int = new OracleReaderWrapper(reader).GetInt("count", 0);
|
}
|
catch (Exception ex)
|
{
|
LogHelper.Error(ex);
|
return 0;
|
}
|
return @int;
|
}
|
|
|
public DataTable 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 "
|
+ " 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.id and aa.client_id = :clientId "; //参数：客户ID
|
|
return OracleHelper.Execute(sql, OracleHelper.Connection, new OracleParameter(":clientId", (object)clientID));
|
}
|
|
/**
|
* 获取产品分类列表
|
* */
|
public List<GwProduct> getGwProductClassList()
|
{
|
List<GwProduct> list = new List<GwProduct>();
|
using (OracleDataReader reader = OracleHelper.ExecuteReader(string.Format("select * from GW_PRODUCT where IS_ENABLE = 1 AND CLASSES=0 "), OracleHelper.Connection ))
|
{
|
while (reader != null && ((DbDataReader)reader).Read())
|
{
|
GwProduct o = new GwProduct();
|
if (this.ReadInfo(reader, o))
|
list.Add(o);
|
}
|
}
|
return list;
|
}
|
|
/**
|
* 获取产品列表
|
* */
|
public List<GwProduct> getGwProductList()
|
{
|
List<GwProduct> list = new List<GwProduct>();
|
using (OracleDataReader reader = OracleHelper.ExecuteReader(string.Format("select * from GW_PRODUCT where IS_ENABLE = 1 AND CLASSES=1 "), OracleHelper.Connection))
|
{
|
while (reader != null && ((DbDataReader)reader).Read())
|
{
|
GwProduct o = new GwProduct();
|
if (this.ReadInfo(reader, o))
|
list.Add(o);
|
}
|
}
|
return list;
|
}
|
|
/**
|
* 根据产品ID获取产品信息
|
* */
|
public GwProduct find(string productId)
|
{
|
GwProduct o = new GwProduct();
|
if (string.IsNullOrEmpty(productId))
|
throw new ArgumentException("产品ID为空！");
|
using (OracleDataReader reader = OracleHelper.ExecuteReader(string.Format("select * from GW_PRODUCT where ID=:productId"), OracleHelper.Connection, new OracleParameter(":productId", (object)productId)))
|
{
|
if (((DbDataReader)reader).Read())
|
{
|
this.ReadInfo(reader, o);
|
return o;
|
}
|
}
|
return o;
|
}
|
|
//修改
|
public bool Update(GwProduct o)
|
{
|
string sql = "";
|
sql += "update GW_PRODUCT set NAME=:NAME ,ICON=:ICON, INSTRUCTIONS=:INSTRUCTIONS, CLASSES=:CLASSES, PARENT_ID=:PARENT_ID, PATH=:PATH ";
|
sql += " , IS_DEFAULT=:IS_DEFAULT, IS_ENABLE=:IS_ENABLE, SORT=:SORT, OP_GROUP_ID=:OP_GROUP_ID, API_SP_XH_ID=:API_SP_XH_ID, GIVING_NUM=:GIVING_NUM, REMARK=:REMARK ";
|
sql += " where ID=:ID ";
|
|
return OracleHelper.ExecuteSql(sql, OracleHelper.Connection,
|
new OracleParameter(":NAME", (object)o.name),
|
new OracleParameter(":ICON", (object)o.icon),
|
new OracleParameter(":INSTRUCTIONS", (object)o.instructions),
|
new OracleParameter(":CLASSES", (object)o.classes),
|
new OracleParameter(":PARENT_ID", (object)o.parent_id),
|
new OracleParameter(":PATH", (object)o.path),
|
new OracleParameter(":IS_DEFAULT", (object)o.is_default),
|
new OracleParameter(":IS_ENABLE", (object)o.is_enable),
|
new OracleParameter(":SORT", (object)o.sort),
|
new OracleParameter(":OP_GROUP_ID", (object)o.op_group_id),
|
new OracleParameter(":API_SP_XH_ID", (object)o.api_sp_xh_id),
|
new OracleParameter(":GIVING_NUM", (object)o.giving_num),
|
new OracleParameter(":REMARK", (object)o.remark),
|
new OracleParameter(":ID", (object)o.id)
|
) > 0;
|
}
|
|
//添加
|
public bool Add(GwProduct o)
|
{
|
string sql = "";
|
sql += "insert into GW_PRODUCT(ID, NAME, ICON, INSTRUCTIONS, CLASSES, PARENT_ID, PATH, IS_DEFAULT, IS_ENABLE, SORT, OP_GROUP_ID, API_SP_XH_ID, GIVING_NUM, REMARK ) ";
|
sql += " values(:ID, :NAME, :ICON, :INSTRUCTIONS, :CLASSES, :PARENT_ID, :PATH, :IS_DEFAULT, :IS_ENABLE, :SORT, :OP_GROUP_ID, :API_SP_XH_ID, :GIVING_NUM, :REMARK) ";
|
|
return OracleHelper.ExecuteSql(sql, OracleHelper.Connection,
|
new OracleParameter(":ID", (object)o.id),
|
new OracleParameter(":NAME", (object)o.name),
|
new OracleParameter(":ICON", (object)o.icon),
|
new OracleParameter(":INSTRUCTIONS", (object)o.instructions),
|
new OracleParameter(":CLASSES", (object)o.classes),
|
new OracleParameter(":PARENT_ID", (object)o.parent_id),
|
new OracleParameter(":PATH", (object)o.path),
|
new OracleParameter(":IS_DEFAULT", (object)o.is_default),
|
new OracleParameter(":IS_ENABLE", (object)o.is_enable),
|
new OracleParameter(":SORT", (object)o.sort),
|
new OracleParameter(":OP_GROUP_ID", (object)o.op_group_id),
|
new OracleParameter(":API_SP_XH_ID", (object)o.api_sp_xh_id),
|
new OracleParameter(":GIVING_NUM", (object)o.giving_num),
|
new OracleParameter(":REMARK", (object)o.remark)
|
) > 0;
|
}
|
|
//更新产品启停状态
|
public bool UpdateIs_enable(string productId, int is_enable)
|
{
|
return OracleHelper.ExecuteSql("UPDATE GW_PRODUCT SET IS_ENABLE=:IS_ENABLE WHERE ID=:ID", OracleHelper.Connection, new OracleParameter(":IS_ENABLE", (object)is_enable), new OracleParameter(":ID", (object)productId)) > 0;
|
}
|
|
//删除产品
|
public bool Delete(string product_id)
|
{
|
if (string.IsNullOrEmpty(product_id) || string.Equals("0", product_id))
|
return false;
|
return OracleHelper.ExecuteSql("delete from GW_PRODUCT where ID=:ID", OracleHelper.Connection, new OracleParameter(":ID", (object)product_id)) > 0;
|
}
|
|
//检查产品名称
|
public bool IsNameExists(string name)
|
{
|
if (string.IsNullOrEmpty(name) || string.Equals("0", name))
|
return true;
|
using (OracleDataReader oracleDataReader = OracleHelper.ExecuteReader(string.Format("select * from GW_PRODUCT where NAME=:NAME and IS_ENABLE=1 "), OracleHelper.Connection, new OracleParameter(":NAME", (object)name)))
|
return ((DbDataReader)oracleDataReader).Read();
|
}
|
|
public void Dispose()
|
{
|
|
}
|
|
/**
|
* 数据转换封装
|
* */
|
private bool ReadInfo(OracleDataReader reader, GwProduct gwProduct)
|
{
|
OracleReaderWrapper oracleReaderWrapper = new OracleReaderWrapper(reader);
|
gwProduct.id = oracleReaderWrapper.GetString("id", "");
|
gwProduct.name = oracleReaderWrapper.GetString("name", "");
|
gwProduct.icon = oracleReaderWrapper.GetString("icon", "");
|
gwProduct.instructions = oracleReaderWrapper.GetString("instructions", "");
|
gwProduct.classes = oracleReaderWrapper.GetInt("classes", 0);
|
gwProduct.parent_id = oracleReaderWrapper.GetString("parent_id", "");
|
gwProduct.path = oracleReaderWrapper.GetString("path", "");
|
gwProduct.is_default = oracleReaderWrapper.GetInt("is_default", -1);
|
gwProduct.is_enable = oracleReaderWrapper.GetInt("is_enable", -1);
|
gwProduct.sort = oracleReaderWrapper.GetInt("sort", 0);
|
gwProduct.giving_num = oracleReaderWrapper.GetInt("giving_num", 0);
|
gwProduct.op_group_id = oracleReaderWrapper.GetInt("op_group_id", -1);
|
gwProduct.remark = oracleReaderWrapper.GetString("remark", "");
|
gwProduct.api_sp_xh_id = oracleReaderWrapper.GetString("api_sp_xh_id", "");
|
|
return true;
|
}
|
|
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 DataTable LoadGwProductDataTable(string name, string classes, string is_default, string op_group_id)
|
{
|
|
StringBuilder stringBuilder = new StringBuilder();
|
//stringBuilder.Append("select ID, NAME, ICON, INSTRUCTIONS, CLASSES, PARENT_ID, PATH, IS_DEFAULT, IS_ENABLE, SORT, OP_GROUP_ID, API_SP_XH_ID, GIVING_NUM, REMARK from GW_PRODUCT where 1=1");
|
|
stringBuilder.Append(" select * from ( ");
|
stringBuilder.Append(" select ID, NAME, ICON, INSTRUCTIONS, ");
|
stringBuilder.Append(" case CLASSES when 0 then '0-产品分类' ");
|
stringBuilder.Append(" else '1-产品' ");
|
stringBuilder.Append(" END AS ");
|
stringBuilder.Append(" CLASSES, ");
|
stringBuilder.Append(" type_id||'-'||type_name PARENT_ID, ");
|
stringBuilder.Append(" PATH, ");
|
stringBuilder.Append(" case IS_DEFAULT when 0 then '0-否' ");
|
stringBuilder.Append(" else '1-默认' ");
|
stringBuilder.Append(" END AS ");
|
stringBuilder.Append(" IS_DEFAULT, ");
|
stringBuilder.Append(" case IS_ENABLE when 0 then '0-已停用' ");
|
stringBuilder.Append(" else '1-已启用' ");
|
stringBuilder.Append(" END AS ");
|
stringBuilder.Append(" IS_ENABLE, ");
|
stringBuilder.Append(" SORT, ");
|
stringBuilder.Append(" (select group_id||'-'||group_name from gw_op_group where group_id=OP_GROUP_ID ) ");
|
stringBuilder.Append(" OP_GROUP_ID, ");
|
stringBuilder.Append(" (select xh_id||'-'||xh_name from sys_xh where xh_id=API_SP_XH_ID ) ");
|
stringBuilder.Append(" API_SP_XH_ID, ");
|
stringBuilder.Append(" GIVING_NUM, REMARK ");
|
stringBuilder.Append(" from GW_PRODUCT a ");
|
stringBuilder.Append(" left join (select id type_id, name type_name from gw_product where classes=0) b ");
|
stringBuilder.Append(" on a.PARENT_ID = b.type_id ");
|
stringBuilder.Append(" ) aa ");
|
stringBuilder.Append(" where 1=1 ");
|
|
|
OracleParameter[] oracleParameterArray = new OracleParameter[3];
|
if (name != null && name != "")
|
{
|
oracleParameterArray[0] = new OracleParameter(":name", (OracleDbType)126);
|
((DbParameter)oracleParameterArray[0]).Value = (object)name;
|
stringBuilder.Append(" and name=:name");
|
}
|
if (classes != null && classes != "")
|
{
|
oracleParameterArray[1] = new OracleParameter(":classes", (OracleDbType)112);
|
((DbParameter)oracleParameterArray[1]).Value = (object)classes;
|
stringBuilder.Append(" and classes=:classes");
|
}
|
if (is_default != null && is_default != "")
|
{
|
oracleParameterArray[1] = new OracleParameter(":is_default", (OracleDbType)112);
|
((DbParameter)oracleParameterArray[1]).Value = (object)is_default;
|
stringBuilder.Append(" and is_default=:is_default");
|
}
|
if (op_group_id != null && op_group_id != "")
|
{
|
oracleParameterArray[2] = new OracleParameter(":op_group_id", (OracleDbType)112);
|
((DbParameter)oracleParameterArray[2]).Value = (object)op_group_id;
|
stringBuilder.Append(" and op_group_id=:op_group_id");
|
}
|
return OracleHelper.Execute(PubConstant.doOracleSql(100000, stringBuilder.ToString()).ToString(), OracleHelper.Connection, oracleParameterArray);
|
}
|
}
|
}
|
