FCC/Applications/SinoChem/SinochemEps/EpsTransactionQuery.cs

using Dfs.WayneChina.SinochemEps;
using SinoChemCommonUtilities;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Wayne.Lib.Log;

namespace SinochemInternetPlusApp
{
    class EpsTransactionQuery
    {
        private static SqlCommandUtility sqlCmd = new SqlCommandUtility(SinochemEpsApp.PosDatabaseConnString);

        /// <summary>
        /// Get a readonly list of all eps transactions
        /// </summary>
        /// <returns></returns>
        public static List<EpsTransactionModel> GetAllEpsTrxModels()
        {
            string querySql = string.Format(" select * from eps_trx ");
            return sqlCmd.ReadData<List<EpsTransactionModel>>(querySql, DbModelConstructors.ConstructEpsTrxModelList);         
        }              

        /// <summary>
        /// Get a readonly list of the eps transactions for a certain fueling point
        /// </summary>
        /// <param name="nozzleId"></param>
        /// <returns></returns>
        public static List<EpsTransactionModel> GetAllEpsTrxModelsOf(int nozzleId)
        {
            string querySql = string.Format(" select * from eps_trx where jihao={0} ", nozzleId);
            return sqlCmd.ReadData<List<EpsTransactionModel>>(querySql, DbModelConstructors.ConstructEpsTrxModelList);
        }

        /// <summary>
        /// Get a readonly list of the car plate eps transactions for a list of fueling points, except for invalid transactions
        /// </summary>
        /// <param name="nozzles"></param>
        /// <returns></returns>
        public static List<EpsTransactionModel> GetValidCarPlateEpsTrxModels(List<int> nozzles, int topCount, DebugLogger debugLogger)
        {
            const int TrxInProgressCategory = 1;
            const int TrxAlreadyDoneCategory = 2;
            List<EpsTransactionModel> epxTrxList = GetCarPlateEpsTrxModelsByTrxStatusCategory(nozzles, -1, TrxInProgressCategory, debugLogger);
            List<EpsTransactionModel> alreadyDoneTrxList = GetCarPlateEpsTrxModelsByTrxStatusCategory(nozzles, topCount, TrxAlreadyDoneCategory, debugLogger);

            epxTrxList.AddRange(alreadyDoneTrxList);

            return epxTrxList;
        }

        /// <summary>
        /// Get car plate trxes by Trx status category.
        /// </summary>
        private static List<EpsTransactionModel> GetCarPlateEpsTrxModelsByTrxStatusCategory
        (
            List<int> nozzles, 
            int topCount, 
            int trxStatusCategory, 
            DebugLogger debugLogger
        )
        {
            StringBuilder sb = new StringBuilder();

            // 1) select clause
            if (topCount >= 0)
            {
                sb.Append(string.Format(" select top {0} * from eps_trx where", topCount));
            }
            else
            {
                sb.Append(" select * from eps_trx where");
            }

            // 2) which jihao
            sb.Append(" (");
            for (int i = 0; i < nozzles.Count; i++)
            {
                if (i != nozzles.Count - 1)
                    sb.Append($"jihao = {nozzles[i]} or ");
                else
                    sb.Append($"jihao = {nozzles[i]}");
            }
            sb.Append(")");

            // 3) which status category
            sb.Append(" and " + ConvertTrxStatusCategoryToSql(trxStatusCategory));        

            // 4) car plate mop
            sb.Append($" and mop = {(int)TransactionMode.CarPlateMode}");

            // 5) order
            sb.Append(" order by created_time desc");

            debugLogger.Add(sb);

            return sqlCmd.ReadData<List<EpsTransactionModel>>(sb.ToString(), DbModelConstructors.ConstructEpsTrxModelList);
        }

        /// <summary>
        /// Trx status category(valid means not Removed or Unknown):
        /// 1 -- trx in progress
        /// 2 -- trx done and valid
        /// </summary>
        /// <returns></returns>
        private static string ConvertTrxStatusCategoryToSql(int trxStatusCategory)
        {
            string sqlWhere = "";

            switch (trxStatusCategory)
            {
                case 1:
                    sqlWhere = string.Format
                        ($"(trx_status = {(int)EpsTrxStatus.BeforeFueling} " +
                        $"or trx_status = {(int)EpsTrxStatus.Fueling} " +
                        $"or trx_status = {(int)EpsTrxStatus.FuelingDone} " +
                        $"or trx_status = {(int)EpsTrxStatus.BeforePayment})");
                    break;
                case 2:
                    sqlWhere = string.Format
                        ($"(trx_status = {(int)EpsTrxStatus.PaymentOk} " +
                        $"or trx_status = {(int)EpsTrxStatus.PaymentNeedConfirm} " +
                        $"or trx_status = {(int)EpsTrxStatus.PaymentFailed} " +
                        $"or trx_status = {(int)EpsTrxStatus.PaymentOkButNeedRefund} " +
                        $"or trx_status = {(int)EpsTrxStatus.PaymentRefunded})");
                    break;
                default:
                    break;
            }

            return sqlWhere;
        }

        /// <summary>
        /// Get a list of eps transactions in certain status and after certain date time
        /// </summary>
        /// <returns></returns>
        public static List<EpsTransaction> GetEpsTrxesByTrxStatusAndCreatedTime(EpsTrxStatus trxStatus, DateTime createdTimeStart, bool ignoreCreatedTimeStart)
        {
            string querySql = "";
            if (ignoreCreatedTimeStart)
            {
                querySql = string.Format(" select * from eps_trx where trx_status = {0}",
                    (int)trxStatus);
            }
            else
            {
                querySql = string.Format(" select * from eps_trx where trx_status = {0} and created_time >= '{1}'",
                    (int)trxStatus, createdTimeStart.ToString("yyyy-MM-dd HH:mm:ss"));
            }

            return sqlCmd.ReadData<List<EpsTransaction>>(querySql, DbModelConstructors.ConstructEpsTrxList);
        }

        /// <summary>
        /// Get a list of eps transactions in certain notify pos flag and after certain date time
        /// </summary>
        /// <returns></returns>
        public static List<EpsTransaction> GetEpsTrxesByNotifyPosFlagAndCreatedTime(NotifyPosFlag notifyPos, DateTime createdTimeStart)
        {
            string querySql = string.Format(" select * from eps_trx where notify_pos = {0} and created_time >= '{1}'",
                (int)notifyPos, createdTimeStart.ToString("yyyy-MM-dd HH:mm:ss"));
            return sqlCmd.ReadData<List<EpsTransaction>>(querySql, DbModelConstructors.ConstructEpsTrxList);
        }

        /// <summary>
        /// not all eps trxes can be removed; need to check eps trx status
        /// </summary>
        /// <param name="createdTimeStop"></param>
        public static void DeleteEpsTrxesBefore(DateTime createdTimeStop)
        {
            StringBuilder deleteSqlSb = new StringBuilder();

            deleteSqlSb.AppendFormat(" delete from eps_trx where created_time < '{0}'", createdTimeStop.ToString("yyyy-MM-dd HH:mm:ss"));

            deleteSqlSb.Append(" and (");

            deleteSqlSb.AppendFormat("trx_status={0}", (int)EpsTrxStatus.Removed);

            deleteSqlSb.AppendFormat(" or trx_status={0}", (int)EpsTrxStatus.PaymentRefunded);

            deleteSqlSb.AppendFormat(" or trx_status={0}", (int)EpsTrxStatus.PaymentFailed);

            deleteSqlSb.AppendFormat(" or (trx_status={0} and notify_pos={1})", (int)EpsTrxStatus.PaymentOk, (int)NotifyPosFlag.NotifyOk);

            deleteSqlSb.Append(")");

            sqlCmd.DeleteData(deleteSqlSb.ToString());
        }

        
    }
}