You cannot select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
177 lines
11 KiB
C#
177 lines
11 KiB
C#
using EncPassCSharp;
|
|
using System;
|
|
using System.Collections.Generic;
|
|
using System.Data;
|
|
using System.Linq;
|
|
using System.Text;
|
|
using System.Web.Configuration;
|
|
|
|
namespace DAL
|
|
{
|
|
public class StaSquSumCenDAL
|
|
{
|
|
private DalInterface DBHelper;
|
|
public string dbType = WebConfigurationManager.ConnectionStrings["DBType"].ConnectionString;
|
|
|
|
|
|
public StaSquSumCenDAL()
|
|
{
|
|
string DBDataPassWord = EncPassClass.DePassWord(WebConfigurationManager.ConnectionStrings["DBDataPassWord"].ConnectionString);
|
|
if (this.dbType == "1")
|
|
{
|
|
this.DBHelper = new DBHelperSQL();
|
|
string ConnectionStringSQL = string.Format(WebConfigurationManager.ConnectionStrings["ConnectionStringSQL"].ConnectionString, DBDataPassWord);
|
|
this.DBHelper.CreateConnectionStringSql(ConnectionStringSQL);
|
|
}
|
|
else if (this.dbType == "2")
|
|
{
|
|
this.DBHelper = new DBHelperOra();
|
|
string ConnectionStringOracle = string.Format(WebConfigurationManager.ConnectionStrings["ConnectionStringOracle"].ConnectionString, DBDataPassWord);
|
|
this.DBHelper.CreateConnectionStringSql(ConnectionStringOracle);
|
|
}
|
|
else if (this.dbType == "4")
|
|
{
|
|
this.DBHelper = new DBHelperMySQL();
|
|
string ConnectionStringSQL = string.Format(WebConfigurationManager.ConnectionStrings["ConnectionStringMySQL"].ConnectionString, DBDataPassWord);
|
|
this.DBHelper.CreateConnectionStringSql(ConnectionStringSQL);
|
|
}
|
|
}
|
|
|
|
|
|
public DataTable GetStaSumForCen(string classdt, int m)
|
|
{
|
|
string sqlStr = @"select iop.*,stsm.CASHRECCOUNT as SUM_CASHRECCOUNT, stsm.PAYRECCOUNT as SUM_PAYRECCOUNT, stsm.INCASH as SUM_INCASH, stsm.CASH as SUM_CASH, stsm.PAYCASH as SUM_PAYCASH, stsm.PAYINCASH as SUM_PAYINCASH,stsm.MPAYRECCOUNT as SUM_MPAYRECCOUNT, stsm.MPAYCASH as SUM_MPAYCASH, stsm.MPAYINCASH as SUM_MPAYINCASH, stsm.SPCOUNT as SUM_SPCOUNT, stsm.SPCASH as SUM_SPCASH, stsm.SPINCASH as SUM_SPINCASH, stsm.BAK3, stsm.BAK4, stsm.BAK5, stsm.BAK6
|
|
,(select STANAME from NAME_TABLE where stano=iop.stano) STANAME,
|
|
(select ROADNAME from ROAD_TABLE where ROADNO=iop.ROADNO) ROADNAME
|
|
from
|
|
(
|
|
SELECT op.ROADNO, op.STANO, 2 PORTTYPE, op.CLASSDATE,
|
|
SUM(CASE WHEN op.paytype IN(22, 23) THEN 1 ELSE 0 END) PAYRECCOUNT,
|
|
SUM(CASE WHEN op.paytype = 20 THEN 1 ELSE 0 END) MPAYRECCOUNT,
|
|
SUM(CASE WHEN op.paytype NOT IN(20, 22, 23) THEN 1 ELSE 0 END) CASHRECCOUNT,
|
|
SUM(CASE WHEN op.paytype NOT IN(20, 22, 23) THEN op.INCASH ELSE 0 END) INCASH,
|
|
SUM(CASE WHEN op.paytype NOT IN(20, 22, 23) THEN op.CASH ELSE 0 END) CASH,
|
|
SUM(CASE WHEN op.paytype IN(22, 23) THEN op.CASH ELSE 0 END) PAYCASH,
|
|
SUM(CASE WHEN op.paytype IN(22, 23) THEN op.PAYCASH ELSE 0 END) PAYINCASH,
|
|
SUM(CASE WHEN op.paytype = 20 THEN op.CASH ELSE 0 END) MPAYCASH,
|
|
SUM(CASE WHEN op.paytype = 20 THEN op.PAYCASH ELSE 0 END) MPAYINCASH,
|
|
SUM(CASE WHEN op.paytype NOT IN(22, 23) and(cardtp = 6 or multiProvince = 0) THEN 1 ELSE 0 END) SPCOUNT,
|
|
SUM(CASE WHEN op.paytype NOT IN(22, 23) and(cardtp = 6 or multiProvince = 0) THEN op.INCASH + op.PAYCASH ELSE 0 END) SPINCASH,
|
|
SUM(CASE WHEN op.paytype NOT IN(22, 23) and(cardtp = 6 or multiProvince = 0) THEN op.CASH ELSE 0 END) SPCASH,
|
|
SUM(CASE WHEN(op.cardtp not in (22, 23) and op.paytype not in (22, 23)) and(incash > 0 or paycash > 0) and Identification = 2 and paytype != 20 THEN 1 ELSE 0 END) B_COUNT,
|
|
SUM(CASE WHEN(op.cardtp not in (22, 23) and op.paytype not in (22, 23)) and(incash > 0 or paycash > 0) and Identification = 2 and paytype != 20 THEN OP.INCASH + OP.PAYCASH ELSE 0 END) B_INCASH,
|
|
SUM(CASE WHEN(op.cardtp not in (22, 23) and op.paytype not in (22, 23)) and(incash > 0 or paycash > 0) and Identification = 2 and paytype = 20 THEN 1 ELSE 0 END) B_EPAYCOUNT,
|
|
SUM(CASE WHEN(op.cardtp not in (22, 23) and op.paytype not in (22, 23)) and(incash > 0 or paycash > 0) and Identification = 2 and paytype = 20 THEN OP.INCASH + OP.PAYCASH ELSE 0 END) B_EPAYINCASH
|
|
FROM OUT_PORT_{1} op WHERE op.CLASSDATE = '{0}'
|
|
GROUP BY op.ROADNO, op.STANO, op.CLASSDATE
|
|
union
|
|
SELECT op.ROADNO, op.STANO, 1 PORTTYPE, op.CLASSDATE,
|
|
SUM(CASE WHEN op.CARDTP IN(22, 23) THEN 1 ELSE 0 END) PAYRECCOUNT, 0 MPAYRECCOUNT,
|
|
SUM(CASE WHEN op.CARDTP NOT IN(22, 23) THEN 1 ELSE 0 END) CASHRECCOUNT,
|
|
0 CASH, 0 INCASH, 0 PAYCASH, 0 PAYINCASH, 0 MPAYCASH, 0 MPAYINCASH, 0, 0, 0, 0, 0, 0, 0 FROM IN_PORT_{1} op
|
|
WHERE op.CLASSDATE = '{0}' and op.icount <> 0
|
|
GROUP BY op.ROADNO, op.STANO, op.CLASSDATE
|
|
UNION
|
|
select op.roadno, op.stano, 8 PORTTYPE, convert(datetime, left(op.hourbatchNo, 8)),
|
|
sum(case when op.mediaType = 1 then 1 else 0 end) PAYRECCOUNT,0 MPAYRECCOUNT,
|
|
sum(case when op.mediaType = 2 then 1 else 0 end) CASHRECCOUNT,
|
|
sum(case when op.mediaType = 2 then op.payFee else 0 end ) CASH,
|
|
sum(case when op.mediaType = 2 then op.Fee else 0 end ) INCASH,
|
|
sum(case when op.mediaType = 1 then op.payFee else 0 end ) PAYCASH,
|
|
sum(case when op.mediaType = 1 then op.Fee else 0 end ) PAYINCASH,0 MPAYCASH,0 MPAYINCASH,0,0,0,0,0,0,0
|
|
from GANTRY_PASS_{1} op where
|
|
op.hourbatchNo >= '{0}00' and op.hourbatchNo <= '{0}23'
|
|
group by op.roadno,op.stano,left(op.hourbatchNo, 8)
|
|
) iop
|
|
left join
|
|
(
|
|
select * from STATIONSQUADSUM t where 1 = 1 and CLASSDATE = '{0}' and not EXISTS(
|
|
select * from STATIONSQUADSUM t1 where 1 = 1 and CLASSDATE = '{0}' and t.STANO = t1.STANO and t.CLASSDATE = t1.CLASSDATE and t.PORTTYPE = t1.PORTTYPE and t.dt > t1.dt
|
|
)
|
|
) stsm
|
|
on iop.ROADNO = stsm.ROADNO and iop.STANO = stsm.STANO and iop.CLASSDATE = stsm.CLASSDATE and iop.PORTTYPE = stsm.PORTTYPE ORDER BY IOP.STANO,IOP.PORTTYPE";
|
|
string sql = string.Format(sqlStr, classdt, m);
|
|
return DBHelper.Query(sql).Tables[0];
|
|
}
|
|
|
|
|
|
public int GetMaxSEQNO(string sqlwhere)
|
|
{
|
|
string strSql = " SELECT MAX(SEQNO) SEQNO FROM STATIONSQUADSUM WHERE 1=1 " + sqlwhere;
|
|
object o = DBHelper.GetSingle(strSql);
|
|
if (o == null)
|
|
{
|
|
return 1;
|
|
}
|
|
else
|
|
{
|
|
return Convert.ToInt32(o) + 1;
|
|
}
|
|
}
|
|
|
|
public DataTable GetStaSumAll(string classdt,string stano)
|
|
{
|
|
string sqlStr = @"select AREANO,ROADNO,{1} STANO,CLASSDATE,PORTTYPE,sum(CASHRECCOUNT)CASHRECCOUNT,sum(PAYRECCOUNT)PAYRECCOUNT,sum(INCASH)INCASH,sum(CASH)CASH,sum(PAYCASH)PAYCASH,sum(PAYINCASH)PAYINCASH,sum(MPAYRECCOUNT)MPAYRECCOUNT,sum(MPAYCASH)MPAYCASH,sum(MPAYINCASH)MPAYINCASH,sum(SPCOUNT)SPCOUNT,sum(SPCASH)SPCASH,sum(SPINCASH)SPINCASH,sum(BAK3)B_COUNT,sum(BAK4)B_INCASH,sum(BAK5)B_EPAYCOUNT,sum(BAK6)B_EPAYINCASH from(
|
|
select * from STATIONSQUADSUM t where 1 = 1 and CLASSDATE = '{0}' and bak2!=2 and not EXISTS(
|
|
select * from STATIONSQUADSUM t1 where 1 = 1 and CLASSDATE = '{0}' and bak2!=2 and t.STANO = t1.STANO and t.CLASSDATE = t1.CLASSDATE and t.PORTTYPE = t1.PORTTYPE and t.dt > t1.dt
|
|
)
|
|
) t group by AREANO,ROADNO,CLASSDATE,PORTTYPE";
|
|
string sql = string.Format(sqlStr, classdt, stano);
|
|
return DBHelper.Query(sql).Tables[0];
|
|
}
|
|
public bool CheckStanSquanSumExists(int area, int road, int stano, string classDt, int portType, int recctCash, int recctPay, int incash, int cash, int paycash, int payincash, int rectMpay, int mpayCash, int mpayInCash, int rectSP, int spCash, int spInCash, int obuCt, int obuInCash, int noObuCt, int noObuInCash, int usertype)
|
|
{
|
|
string strSql = string.Format("SELECT AREANO FROM STATIONSQUADSUM where AREANO={0} and ROADNO={1} and STANO={2} and CLASSDATE='{3}' and PORTTYPE={4} and CASHRECCOUNT={5} AND PAYRECCOUNT={6} and INCASH={7} and CASH={8} and PAYCASH={9} AND PAYINCASH={10} and MPAYRECCOUNT={11} and MPAYCASH={12} and MPAYINCASH={13} and SPCOUNT={14} and SPCASH={15} and SPINCASH={16} and BAK3={17} and BAK4={18} and BAK5={19} and BAK6={20} and BAK2={21}", area, road, stano, classDt, portType, recctCash, recctPay, incash, cash, paycash, payincash, rectMpay, mpayCash, mpayInCash, rectSP, spCash, spInCash, obuCt, obuInCash, noObuCt, noObuInCash, usertype);
|
|
object o = DBHelper.GetSingle(strSql);
|
|
if (o == null)
|
|
{
|
|
return false;
|
|
}
|
|
else
|
|
{
|
|
return true;
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
///
|
|
/// </summary>
|
|
/// <param name="seqno">序列号</param>
|
|
/// <param name="area">区域</param>
|
|
/// <param name="road">路段</param>
|
|
/// <param name="stano">站</param>
|
|
/// <param name="classDt">统计日</param>
|
|
/// <param name="portType">车道类型</param>
|
|
/// <param name="rectCash">MTC车流</param>
|
|
/// <param name="rectPay">ETC车流</param>
|
|
/// <param name="incash">MTC实收</param>
|
|
/// <param name="cash">MTC应收</param>
|
|
/// <param name="paycash">ETC应收</param>
|
|
/// <param name="payincash">ETC实收</param>
|
|
/// <returns></returns>
|
|
public bool AddStanSquanSum(int seqno, int area, int road, int stano, string classDt, int portType, int rectCash, int rectPay, int incash, int cash, int paycash, int payincash, int rectMpay, int mpayCash, int mpayInCash, int rectSP, int spCash, int spInCash, int isForce, int obuCt, int obuInCash, int noObuCt, int noObuInCash, int usertype, string dis)
|
|
{
|
|
string strSql = "";
|
|
if (dbType == "4")
|
|
{
|
|
strSql = string.Format("insert into STATIONSQUADSUM(SEQNO,AREANO,ROADNO,STANO,CLASSDATE,PORTTYPE,CASHRECCOUNT,PAYRECCOUNT,INCASH,CASH,PAYCASH,PAYINCASH,DT,MPAYRECCOUNT,MPAYCASH,MPAYINCASH,SPCOUNT,SPCASH,SPINCASH,ISFORCE,BAK3,BAK4,BAK5,BAK6,BAK2,BAK11) values({0},{1},{2},{3},'{4}',{5},{6},{7},{8},{9},{10},{11},now(),{12},{13},{14},{15},{16},{17},{18},{19},{20},{21},{22},{23},{24})", seqno, area, road, stano, classDt, portType, rectCash, rectPay, incash, cash, paycash, payincash, rectMpay, mpayCash, mpayInCash, rectSP, spCash, spInCash, isForce, obuCt, obuInCash, noObuCt, noObuInCash, usertype, dis);
|
|
}
|
|
else if (dbType == "1")
|
|
{
|
|
strSql = string.Format("insert into STATIONSQUADSUM(SEQNO,AREANO,ROADNO,STANO,CLASSDATE,PORTTYPE,CASHRECCOUNT,PAYRECCOUNT,INCASH,CASH,PAYCASH,PAYINCASH,DT,MPAYRECCOUNT,MPAYCASH,MPAYINCASH,SPCOUNT,SPCASH,SPINCASH,ISFORCE,BAK3,BAK4,BAK5,BAK6,BAK2,BAK11) values({0},{1},{2},{3},'{4}',{5},{6},{7},{8},{9},{10},{11},CONVERT(VARCHAR(19),GETDATE(),120),{12},{13},{14},{15},{16},{17},{18},{19},{20},{21},{22},{23},{24)", seqno, area, road, stano, classDt, portType, rectCash, rectPay, incash, cash, paycash, payincash, rectMpay, mpayCash, mpayInCash, rectSP, spCash, spInCash, isForce, obuCt, obuInCash, noObuCt, noObuInCash, usertype, dis);
|
|
}
|
|
|
|
|
|
int i = DBHelper.ExecuteSql(strSql);
|
|
if (i == 0)
|
|
{
|
|
return false;
|
|
}
|
|
else
|
|
{
|
|
return true;
|
|
}
|
|
}
|
|
}
|
|
}
|