|
using Common;
|
using Oracle.DataAccess.Client;
|
using System;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.Data.Common;
|
|
namespace Dao
|
{
|
public class GwStatisRefundDao : IDisposable
|
{
|
public List<GwStatisReturnItem> Query(DateTime startTime, DateTime endTime, string spID, string timeExpression, StatisOption so)
|
{
|
List<GwStatisReturnItem> list = new List<GwStatisReturnItem>();
|
using (OracleDataReader reader = OracleHelper.ExecuteReader(string.Format("SELECT TO_CHAR(STATIS_TIME,'{0}') AS TITLE {1},CLIENT_ID,\r\n SUM(AP_STATUS0) AP_STATUS0,\r\n SUM(AP_STATUS1) AP_STATUS1,\r\n SUM(AP_STATUS2) AP_STATUS2,\r\n SUM(AP_STATUS3) AP_STATUS3,\r\n SUM(AP_STATUS4) AP_STATUS4,\r\n SUM(AP_STATUS5) AP_STATUS5,\r\n SUM(OP_STATUS0) OP_STATUS0,\r\n SUM(OP_STATUS1) OP_STATUS1,\r\n SUM(OP_STATUS2) OP_STATUS2,\r\n SUM(OP_STATUS3) OP_STATUS3,\r\n SUM(OP_STATUS4) OP_STATUS4,\r\n SUM(OP_STATUS5) OP_STATUS5 \r\n FROM GW_SM_STATIS_V3 WHERE (SP_ID=:SP_ID OR :SP_ID IS NULL) AND STATIS_TIME BETWEEN :START_TIME AND :END_TIME GROUP BY TO_CHAR(STATIS_TIME,'{0}') {1},CLIENT_ID ORDER BY 1 ASC", (object)timeExpression, (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)))
|
{
|
GwStatisReturnItem statisReturnItem1 = new GwStatisReturnItem();
|
statisReturnItem1.Title = "合计";
|
while (reader != null && ((DbDataReader)reader).Read())
|
{
|
GwStatisReturnItem statisReturnItem2 = new GwStatisReturnItem();
|
OracleReaderWrapper oracleReaderWrapper = new OracleReaderWrapper(reader);
|
statisReturnItem2.Title = oracleReaderWrapper.GetString("TITLE", "");
|
statisReturnItem2.SpID = oracleReaderWrapper.GetString("SP_ID", "");
|
statisReturnItem2.ClientID = oracleReaderWrapper.GetString("CLIENT_ID", "");
|
statisReturnItem2.APStatus0 = oracleReaderWrapper.GetInt("AP_STATUS0", 0);
|
statisReturnItem2.APStatus1 = oracleReaderWrapper.GetInt("AP_STATUS1", 0);
|
statisReturnItem2.APStatus2 = oracleReaderWrapper.GetInt("AP_STATUS2", 0);
|
statisReturnItem2.APStatus3 = oracleReaderWrapper.GetInt("AP_STATUS3", 0);
|
statisReturnItem2.APStatus4 = oracleReaderWrapper.GetInt("AP_STATUS4", 0);
|
statisReturnItem2.APStatus5 = oracleReaderWrapper.GetInt("AP_STATUS5", 0);
|
statisReturnItem2.OPStatus0 = oracleReaderWrapper.GetInt("OP_STATUS0", 0);
|
statisReturnItem2.OPStatus1 = oracleReaderWrapper.GetInt("OP_STATUS1", 0);
|
statisReturnItem2.OPStatus2 = oracleReaderWrapper.GetInt("OP_STATUS2", 0);
|
statisReturnItem2.OPStatus3 = oracleReaderWrapper.GetInt("OP_STATUS3", 0);
|
statisReturnItem2.OPStatus4 = oracleReaderWrapper.GetInt("OP_STATUS4", 0);
|
statisReturnItem2.OPStatus5 = oracleReaderWrapper.GetInt("OP_STATUS5", 0);
|
list.Add(statisReturnItem2);
|
statisReturnItem1.APStatus0 += statisReturnItem2.APStatus0;
|
statisReturnItem1.APStatus1 += statisReturnItem2.APStatus1;
|
statisReturnItem1.APStatus2 += statisReturnItem2.APStatus2;
|
statisReturnItem1.APStatus3 += statisReturnItem2.APStatus3;
|
statisReturnItem1.APStatus4 += statisReturnItem2.APStatus4;
|
statisReturnItem1.APStatus5 += statisReturnItem2.APStatus5;
|
statisReturnItem1.OPStatus0 += statisReturnItem2.OPStatus0;
|
statisReturnItem1.OPStatus1 += statisReturnItem2.OPStatus1;
|
statisReturnItem1.OPStatus2 += statisReturnItem2.OPStatus2;
|
statisReturnItem1.OPStatus3 += statisReturnItem2.OPStatus3;
|
statisReturnItem1.OPStatus4 += statisReturnItem2.OPStatus4;
|
statisReturnItem1.OPStatus5 += statisReturnItem2.OPStatus5;
|
}
|
list.Add(statisReturnItem1);
|
}
|
return list;
|
}
|
|
public DataTable GetMothStatisData(string spID, DateTime startTime, DateTime endTime)
|
{
|
return OracleHelper.Execute("select AP_REFUND from GW_SM_STATIS_V3 where SP_ID=:SP_ID and STATIS_TIME >= :START_TIME and STATIS_TIME < :END_TIME and rownum=1", OracleHelper.Connection, new OracleParameter(":START_TIME", (object)startTime), new OracleParameter(":END_TIME", (object)endTime), new OracleParameter(":SP_ID", (object)spID));
|
}
|
|
public DataTable GetClientPrice(string spID)
|
{
|
return OracleHelper.Execute("SELECT PRICE FROM GW_SP T WHERE SP_ID=:SP_ID AND ROWNUM=1", OracleHelper.Connection, new OracleParameter(":SP_ID", (object)spID));
|
}
|
|
|
public bool RetiredUpdate(string spID, DateTime startTime, DateTime endTime, int reFund, int txtrefund, int price, string operatorID)
|
{
|
int num = reFund + txtrefund;
|
double num2 = Convert.ToDouble(price) / 1000.0;
|
string str2 = Convert.ToDateTime(startTime.Year + "-" + startTime.Month).ToString("yyyy年MM月");
|
string str3 = "BEGIN\r\n UPDATE GW_SM_STATIS_V3 SET AP_REFUND=:AP_REFUND WHERE SP_ID=:SP_ID AND STATIS_TIME >= :START_TIME AND STATIS_TIME < :END_TIME;\r\n insert into GW_CHARGE_LOG(CHARGE_ID,SP_ID,OCCUR_TIME,AMOUNT,OPERATOR_ID,FLAG,FLUSH_TIME,REMARK,PAYMENT_TYPE,PAYLOG_ID) values(GW_CHARGE_LOG_SEQ.nextval,:SP_ID,sysdate,:AMOUNT,:OPERATOR_ID,0,sysdate,:REMARK,0,0);\r\n END;";
|
OracleParameter[] parameterArray = new OracleParameter[] { new OracleParameter(":START_TIME", startTime), new OracleParameter(":END_TIME", endTime), new OracleParameter(":SP_ID", spID), new OracleParameter(":AP_REFUND", num), new OracleParameter(":AMOUNT", txtrefund * price), new OracleParameter(":OPERATOR_ID", operatorID), new OracleParameter(":REMARK", string.Concat(new object[] { "返还条数：", txtrefund, "，返还价格：", num2, "(元/条)，返还时间：", str2 })) };
|
OracleHelper.ExecuteSql(str3, OracleHelper.Connection, parameterArray);
|
return true;
|
}
|
|
|
//public bool RetiredUpdate(string spID, DateTime startTime, DateTime endTime, int reFund, int txtrefund, int price, string operatorID)
|
//{
|
// int num1 = reFund + txtrefund;
|
// double num2 = Convert.ToDouble(price) / 1000.0;
|
// string str = Convert.ToDateTime((string)(object)startTime.Year + (object)"-" + (string)(object)startTime.Month).ToString("yyyy年MM月");
|
// OracleHelper.ExecuteSql("BEGIN\r\n UPDATE GW_SM_STATIS_V3 SET AP_REFUND=:AP_REFUND WHERE SP_ID=:SP_ID AND STATIS_TIME >= :START_TIME AND STATIS_TIME < :END_TIME;\r\n insert into GW_CHARGE_LOG(CHARGE_ID,SP_ID,OCCUR_TIME,AMOUNT,OPERATOR_ID,FLAG,FLUSH_TIME,REMARK,PAYMENT_TYPE,PAYLOG_ID) values(GW_CHARGE_LOG_SEQ.nextval,:SP_ID,sysdate,:AMOUNT,:OPERATOR_ID,0,sysdate,:REMARK,0,0);\r\n END;", OracleHelper.Connection, new OracleParameter(":START_TIME", (object)startTime), new OracleParameter(":END_TIME", (object)endTime), new OracleParameter(":SP_ID", (object)spID), new OracleParameter(":AP_REFUND", (object)num1), new OracleParameter(":AMOUNT", (object)(txtrefund * price)), new OracleParameter(":OPERATOR_ID", (object)operatorID), new OracleParameter(":REMARK", (object)("返还条数：" + (object)txtrefund + "，返还价格：" + (string)(object)num2 + "(元/条)，返还时间：" + str)));
|
// return true;
|
//}
|
|
public void Dispose()
|
{
|
}
|
}
|
}
|
