using System;
|
using System.Collections.Generic;
|
using System.Linq;
|
using System.Text;
|
using Model;
|
using Common;
|
using Oracle.DataAccess.Client;
|
using System.Data.Common;
|
|
namespace Dao
|
{
|
/// <summary>
|
/// 订单操作
|
/// </summary>
|
public class GwOrderDao : IDisposable
|
{
|
public void Dispose()
|
{
|
}
|
|
private static GwOrderDao _instance;
|
public static GwOrderDao Instance
|
{
|
get {
|
if (_instance == null)
|
{
|
_instance = new GwOrderDao();
|
}
|
return _instance;
|
}
|
}
|
|
|
//flag: 0-待审核订单(已提交、审核中)；1-已完成订单（审核通过、审核不通过、取消）
|
public List<GwOrder> LoadInfoList(string spId, string clientId, string compay, int flag, DateTime startDate, DateTime endDate, int pageSize, int PageIndex, string permissionsSQL, out int recordcount)
|
{
|
|
List<GwOrder> list = new List<GwOrder>();
|
recordcount = 0;
|
try
|
{
|
StringBuilder stringBuilder = new StringBuilder();
|
stringBuilder.Append(" FROM ( ");
|
stringBuilder.Append("SELECT GWO.*, GWC.CLIENT_NAME, GWC.COMPANY ");
|
stringBuilder.Append(" FROM GW_ORDER GWO ");
|
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 ");
|
|
if (flag == 0)
|
{
|
stringBuilder.Append(" AND GWO.STATUS IN (0, 1) ");
|
}
|
else if (flag == 1)
|
{
|
stringBuilder.Append(" AND GWO.STATUS IN (2, 3, 4) ");
|
}
|
|
/**
|
if ((startDate != null) && (endDate != null))
|
{
|
stringBuilder.Append(" AND GWO.CREATE_TIME BETWEEN " + startDate + " AND " + endDate + " ");
|
}
|
else if ((startDate != null) && (endDate == null) )
|
{
|
stringBuilder.Append(" AND GWO.CREATE_TIME BETWEEN " + startDate + "'AND " + startDate + "");
|
}
|
else if ((endDate != null) && (startDate == null) )
|
{
|
stringBuilder.Append(" AND GWO.CREATE_TIME BETWEEN " + endDate + " AND " + endDate + " ");
|
}
|
**/
|
stringBuilder.Append(" AND GWO.CREATE_TIME BETWEEN :START_TIME AND :END_TIME ");
|
|
if ((spId != null) && (spId != ""))
|
{
|
stringBuilder.Append(" AND GWO.SP_ID like '%" + spId + "%' " );
|
}
|
|
if ((clientId != null) && (clientId != ""))
|
{
|
stringBuilder.Append(" and GWO.CLIENT_ID like '%" + clientId + "%'");
|
}
|
if ((compay != null) && (compay != ""))
|
{
|
stringBuilder.Append(" and GWC.COMPANY like '%" + compay + "%'");
|
}
|
//客户权限SQL脚本
|
stringBuilder.Append(permissionsSQL);
|
|
stringBuilder.Append(" ) T ");
|
|
OracleParameter[] parameterArray = new OracleParameter[] { new OracleParameter(":START_TIME", startDate), new OracleParameter(":END_TIME", endDate) };
|
|
using (OracleDataReader reader = OracleHelper.ExecuteReader("select count(*) as count " + stringBuilder.ToString(), OracleHelper.Connection, parameterArray))
|
{
|
while (reader.Read())
|
{
|
recordcount = this.ReadCount(reader);
|
}
|
}
|
using (OracleDataReader reader2 = OracleHelper.ExecuteReader(PubConstant.doOracleSql(PageIndex, pageSize, recordcount, "select * " + stringBuilder.ToString()).ToString() + " order by CREATE_TIME desc ", OracleHelper.Connection, parameterArray))
|
{
|
while (reader2.Read())
|
{
|
GwOrder o = new GwOrder();
|
if (this.ReadInfo(reader2, o))
|
{
|
list.Add(o);
|
}
|
}
|
}
|
}
|
catch (Exception exception)
|
{
|
LogHelper.Error(exception);
|
return list;
|
}
|
return list;
|
|
}
|
|
/// <summary>
|
/// 获取订单列表
|
/// </summary>
|
/// flag: 0-待审核订单(已提交、审核中)；1-已完成订单（审核通过、审核不通过、取消）
|
/// <returns></returns>
|
public List<GwOrder> List(GwOrder gwOrder, GwSp gwSp, GwClient gwClient, int flag)
|
{
|
List<GwOrder> list = new List<GwOrder>();
|
//recordcount = 0; //
|
try
|
{
|
StringBuilder stringBuilder = new StringBuilder();
|
stringBuilder.Append("SELECT GWO.* ");
|
stringBuilder.Append(" FROM GW_ORDER GWO ");
|
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 ");
|
|
if(flag == 0)
|
{
|
stringBuilder.Append(" AND GWO.STATUS IN (0, 1) ");
|
}
|
else if (flag == 1)
|
{
|
stringBuilder.Append(" AND GWO.STATUS IN (2, 3, 4) ");
|
}
|
|
stringBuilder.Append(" ORDER BY CREATE_TIME DESC ");
|
|
using (OracleDataReader reader = OracleHelper.ExecuteReader(stringBuilder.ToString() , OracleHelper.Connection))
|
{
|
while (((DbDataReader)reader).Read())
|
{
|
GwOrder o = new GwOrder();
|
if (this.ReadInfo(reader, o))
|
list.Add(o);
|
}
|
}
|
}
|
catch (Exception ex)
|
{
|
LogHelper.Error(ex);
|
return list;
|
}
|
return list;
|
}
|
|
|
//添加产品或产品分类信息
|
public bool Add(GwOrder o)
|
{
|
StringBuilder stringBuilder = new StringBuilder();
|
stringBuilder.Append("INSERT INTO GW_ORDER ( ");
|
stringBuilder.Append(" ORDER_ID, SP_ID, CLIENT_ID, PRODUCT_ID, COMBO_ID, BASIC_NUM, GIVING_NUM, PRICE, ORDER_NUM_TOTAL, ORDER_AMOUNT_TOTAL, PAY_AMOUNT, BEFORE_BALANCE_NUM ");
|
stringBuilder.Append(" , CREATOR, CREATE_TIME, STATUS, REMARK ");
|
//stringBuilder.Append(" , AUDITOR, AUDIT_TIME ");
|
stringBuilder.Append(" ) ");
|
stringBuilder.Append(" VALUES (:ORDER_ID, :SP_ID, :CLIENT_ID, :PRODUCT_ID, :COMBO_ID, :BASIC_NUM, :GIVING_NUM, :PRICE, :ORDER_NUM_TOTAL, :ORDER_AMOUNT_TOTAL, :PAY_AMOUNT, :BEFORE_BALANCE_NUM ");
|
stringBuilder.Append(" , :CREATOR, :CREATE_TIME, :STATUS, :REMARK ");
|
//stringBuilder.Append(" , :AUDITOR, :AUDIT_TIME ");
|
stringBuilder.Append(" ) ");
|
|
string sql = stringBuilder.ToString();
|
|
return OracleHelper.ExecuteSql(sql,
|
OracleHelper.Connection,
|
new OracleParameter(":ORDER_ID", (object)o.OrderId),
|
new OracleParameter(":SP_ID", (object)o.SpId),
|
new OracleParameter(":CLIENT_ID", (object)o.ClientId),
|
new OracleParameter(":PRODUCT_ID", (object)o.ProductId),
|
new OracleParameter(":COMBO_ID", (object)o.ComboId),
|
new OracleParameter(":BASIC_NUM", (object)o.BasicNum),
|
new OracleParameter(":GIVING_NUM", (object)o.GivingNum),
|
new OracleParameter(":PRICE", (object)o.Price),
|
new OracleParameter(":ORDER_NUM_TOTAL", (object)o.OrderNumTotal),
|
new OracleParameter(":ORDER_AMOUNT_TOTAL", (object)o.OrderAmountTotal),
|
new OracleParameter(":PAY_AMOUNT", (object)o.PayAmount),
|
new OracleParameter(":BEFORE_BALANCE_NUM", (object)o.BeforeBalanceNum),
|
new OracleParameter(":CREATOR", (object)o.Creator),
|
new OracleParameter(":CREATE_TIME", (object)o.CreateTime),
|
new OracleParameter(":STATUS", (object)o.Status),
|
new OracleParameter(":REMARK", (object)o.Remark)
|
) > 0;
|
}
|
|
//删除
|
public bool Delete(string orderId)
|
{
|
if (string.IsNullOrEmpty(orderId) )
|
return false;
|
return OracleHelper.ExecuteSql("delete from GW_ORDER where ORDER_ID=:ORDER_ID", OracleHelper.Connection, new OracleParameter(":ORDER_ID", (object)orderId)) > 0;
|
}
|
|
//取消
|
public bool UpdateStatus(int status, string orderId)
|
{
|
if (string.IsNullOrEmpty(orderId))
|
return false;
|
//status:4-取消
|
return OracleHelper.ExecuteSql("update GW_ORDER SET STATUS = :STATUS where ORDER_ID=:ORDER_ID", OracleHelper.Connection, new OracleParameter(":STATUS", (object)status), new OracleParameter(":ORDER_ID", (object)orderId)) > 0;
|
}
|
|
//更新订单修改前余额
|
public bool UpdateBalanceAndStatus(int basicNum, int givingNum, int orderNumTotal, int orderAmountTotal, int payAmount , int 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
|
, 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[] {
|
new OracleParameter(":ORDER_ID", (object)o.OrderId),
|
new OracleParameter(":SP_ID", (object)o.SpId),
|
new OracleParameter(":CLIENT_ID", (object)o.ClientId),
|
new OracleParameter(":PRODUCT_ID", (object)o.ProductId),
|
new OracleParameter(":COMBO_ID", (object)o.ComboId),
|
new OracleParameter(":BASIC_NUM", (object)o.BasicNum),
|
new OracleParameter(":GIVING_NUM", (object)o.GivingNum),
|
new OracleParameter(":PRICE", (object)o.Price),
|
new OracleParameter(":ORDER_NUM_TOTAL", (object)o.OrderNumTotal),
|
new OracleParameter(":ORDER_AMOUNT_TOTAL", (object)o.OrderAmountTotal),
|
new OracleParameter(":PAY_AMOUNT", (object)o.PayAmount),
|
new OracleParameter(":BEFORE_BALANCE_NUM", (object)o.BeforeBalanceNum),
|
new OracleParameter(":CREATOR", (object)o.Creator),
|
new OracleParameter(":CREATE_TIME", (object)o.CreateTime),
|
new OracleParameter(":STATUS", (object)o.Status),
|
new OracleParameter(":REMARK", (object)o.Remark)
|
};
|
|
StringBuilder stringBuilder = new StringBuilder();
|
stringBuilder.Append("UPDATE GW_ORDER SET ");
|
//stringBuilder.Append(" ORDER_ID=:ORDER_ID ");
|
stringBuilder.Append(" , SP_ID =:SP_ID ");
|
stringBuilder.Append(" , CLIENT_ID =:CLIENT_ID ");
|
stringBuilder.Append(" , PRODUCT_ID =:PRODUCT_ID ");
|
stringBuilder.Append(" , COMBO_ID =:COMBO_ID ");
|
stringBuilder.Append(" , BASIC_NUM=:BASIC_NUM, GIVING_NUM=:GIVING_NUM, PRICE=:PRICE, ORDER_NUM_TOTAL=:ORDER_NUM_TOTAL, ORDER_AMOUNT_TOTAL=:ORDER_AMOUNT_TOTAL ");
|
stringBuilder.Append(" , PAY_AMOUNT =:PAY_AMOUNT, BEFORE_BALANCE_NUM =:BEFORE_BALANCE_NUM, CREATOR=:CREATOR, CREATE_TIME=:CREATE_TIME ");
|
stringBuilder.Append(" , STATUS=:STATUS, REMARK=:REMARK ");
|
stringBuilder.Append(" WHERE ORDER_ID=:ORDER_ID ");
|
|
string sql = stringBuilder.ToString();
|
return (OracleHelper.ExecuteSql(sql, OracleHelper.Connection, cmdParms) > 0);
|
}
|
|
//获取信息
|
public GwOrder Get(string orderId)
|
{
|
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)))
|
{
|
if (((DbDataReader)reader).Read())
|
{
|
this.ReadInfo(reader, o);
|
return o;
|
}
|
}
|
return o;
|
}
|
|
//统计记录数
|
private int ReadCount(OracleDataReader reader)
|
{
|
OracleReaderWrapper wrapper = new OracleReaderWrapper(reader);
|
return wrapper.GetInt("count", 0);
|
}
|
|
//数据封装
|
private bool ReadInfo(OracleDataReader reader, GwOrder o)
|
{
|
OracleReaderWrapper oracleReaderWrapper = new OracleReaderWrapper(reader);
|
o.OrderId = oracleReaderWrapper.GetString("ORDER_ID", "");
|
o.SpId = oracleReaderWrapper.GetString("SP_ID", "");
|
o.ClientId = oracleReaderWrapper.GetString("CLIENT_ID", "");
|
o.Company = oracleReaderWrapper.GetString("COMPANY", "");
|
o.ClientName = oracleReaderWrapper.GetString("CLIENT_NAME", "");
|
o.ProductId = oracleReaderWrapper.GetString("PRODUCT_ID", "");
|
o.ComboId = oracleReaderWrapper.GetString("COMBO_ID", "");
|
o.BasicNum = oracleReaderWrapper.GetInt("BASIC_NUM", 0);
|
o.GivingNum = oracleReaderWrapper.GetInt("GIVING_NUM", 0);
|
o.Price = oracleReaderWrapper.GetInt("PRICE", 0);
|
o.OrderNumTotal = oracleReaderWrapper.GetInt("ORDER_NUM_TOTAL", 0);
|
o.OrderAmountTotal = oracleReaderWrapper.GetInt("ORDER_AMOUNT_TOTAL", 0);
|
o.PayAmount = oracleReaderWrapper.GetInt("PAY_AMOUNT", 0);
|
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.AuditTime = oracleReaderWrapper.GetDateTime("AUDIT_TIME");
|
o.Status = oracleReaderWrapper.GetInt("STATUS", 0);
|
o.Remark = oracleReaderWrapper.GetString("REMARK", "");
|
return true;
|
}
|
|
}
|
|
}
|
