JLH/JLHHJSvr/Excutor/APP/GetMainInfoExcutor.cs

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using JLHHJSvr.BLL;
using JLHHJSvr.Com.APP;
using JLHHJSvr.Com.Model;
using JLHHJSvr.DBA.DBModle;
using LJLib.DAL.SQL;
using LJLib.Net.SPI.Server;
using LJLib.SQLEX;

namespace JLHHJSvr.Excutor.APP
{
    internal sealed class GetMainInfoExcutor : ExcutorBase<GetMainInfoRequest, GetMainInfoResponse>
    {
        protected override void ExcuteInternal(GetMainInfoRequest request, object state, GetMainInfoResponse rslt)
        {
            var tokendata = BllHelper.GetToken(request.token);
            if (tokendata == null)
            {
                rslt.ErrMsg = "会话已经中断，请重新登录";
                return;
            }

            using (var con = GlobalVar.ConnectionString.NewSqlConnection())
            using (var cmd = con.CreateCommand())
            {
                con.Open();

                var dtNow = DateTime.Now;
                var optionTime = DateTime.Parse(new st_option().GetValue(cmd, 5));
                var todayTime = new DateTime(dtNow.Year, dtNow.Month, dtNow.Day, optionTime.Hour, optionTime.Minute, 0);
                if ((dtNow.Hour + dtNow.Minute/60) < (optionTime.Hour + optionTime.Minute/60))
                {
                    todayTime = todayTime.AddDays(-1);
                }
                var yesterdayTime = todayTime.AddDays(-1);

                //未处理违停数
                cmd.CommandText = @"
                    SELECT COUNT(billid) AS violateCnt
                    FROM st_bill
                    WHERE flag = 0";
                rslt.violateCnt = Convert.ToInt32(cmd.ExecuteScalar());

                //报备数
                cmd.CommandText = @"
                    SELECT COUNT(pid) AS recordCnt
                    FROM st_permit
                    WHERE iftmp = 1
                        AND inuse = 1
                        AND st_permit.begindate > @today";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@today", todayTime);
                rslt.recordCnt = Convert.ToInt32(cmd.ExecuteScalar());

                //已到期备案数
                cmd.CommandText = @"
                    SELECT COUNT(pid) AS expiredCnt
                    FROM st_permit
                    WHERE iftmp = 1
                        AND inuse = 1
                        AND begindate > @yesterday
                        AND enddate < GETDATE()";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@yesterday", yesterdayTime);
                rslt.expiredCnt = Convert.ToInt32(cmd.ExecuteScalar());

                // PC端附加字段
                if (request.type == 1)
                {
                    //总车位数
                    cmd.CommandText = @"
                        SELECT SUM(cnt) AS totalPermitCnt
                        FROM st_road";
                    cmd.Parameters.Clear();
                    rslt.totalPermitCnt = Convert.ToInt32(cmd.ExecuteScalar());


                    var stOption = new st_option {optionid = 4};
                    var expiringOption = Convert.ToInt32(stOption.GetValue(cmd));
                    var expiringDate = DateTime.Now.AddDays(expiringOption);
                    cmd.CommandText = @"
                        SELECT 
                            COUNT(pid) AS soldCnt,
                            SUM(CASE WHEN enddate < @expiringDate
                                AND enddate > GETDATE() THEN 1 ELSE 0 END) AS expiringPermitCnt,
                            SUM(CASE WHEN enddate < GETDATE() THEN 1 ELSE 0 END) AS expiredPermitCnt
                        FROM st_permit
                        WHERE st_permit.inuse = 1 AND st_permit.iftmp = 0";
                    cmd.Parameters.Clear();
                    cmd.Parameters.AddWithValue("@expiringDate", expiringDate);
                    using (var reader = cmd.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            //剩余车位
                            rslt.leftPermitCnt = rslt.totalPermitCnt - Convert.ToInt32(reader["soldCnt"]);
                            //过期车辆
                            rslt.expiredPermitCnt = Convert.ToInt32(reader["expiredPermitCnt"]);
                            //即将过期
                            rslt.expiringPermitCnt = Convert.ToInt32(reader["expiringPermitCnt"]);
                        }
                    }

                    //已到期备案列表
                    var selectStr = @"
                        SELECT 
                            st_permit.pid,
                            st_road.roadname,
                            st_road.roadid,
                            st_permit.carnum,
                            st_permit.owner,
                            st_permit.owner_tel,
                            st_permit.begindate,
                            st_permit.enddate,
                            st_permit.inuse,
                            st_permit.dscrp,
                            st_permit.paytype,
                            st_permit.payamt,
                            st_permit.opemp,
                            st_permit.opdate,
                            st_permit.modemp,
                            st_permit.moddate
                        FROM st_permit
                            INNER JOIN st_road ON st_permit.roadid = st_road.roadid 
                        WHERE st_permit.iftmp = 1
                        AND st_permit.inuse = 1
                        AND st_permit.begindate > @yesterday
                        AND st_permit.enddate < GETDATE()";
                    var today = DateTime.Now;
                    DateTime dateHelper;
                    if (today.Hour < 8)
                    {
                        dateHelper = today.AddDays(-2);
                    }
                    else
                    {
                        dateHelper = today.AddDays(-1);
                    }
                    var yesterday = new DateTime(dateHelper.Year, dateHelper.Month, dateHelper.Day, 8, 0, 0);
                    var outputFields =
                        "pid,roadname,roadid,carnum,owner,owner_tel,begindate,enddate,inuse,dscrp,paytype,payamt,opemp,opdate,modemp,moddate";
                    rslt.expiredRecordList = new List<Permit>();
                    DbSqlHelper.SelectJoin(cmd, selectStr, null,
                        new Dictionary<string, object> {{"@yesterday", yesterday}}, null, outputFields, 0, 0,
                        rslt.expiredRecordList);
                }
            }
        }
    }
}