FCC/Applications/SinoChem/GenericSinochemEps/EpsTransactionQuery.cs

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(GenericSinochemEpsApp.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)
        {
            List<EpsTransactionModel> epxTrxList = GetCarPlateEpsTrxModelsByTrxStatusCategory(nozzles, topCount, debugLogger);
            return epxTrxList;
        }

        /// <summary>
        /// Get car plate trxes by Trx status category.
        /// </summary>
        private static List<EpsTransactionModel> GetCarPlateEpsTrxModelsByTrxStatusCategory
        (
            List<int> nozzles,
            int topCount,
            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());

            // 4) car plate mop
            sb.Append($" and mop = {(int)EpsTransactionMode.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()
        {
            //string sqlWhere = "";

            string 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}" + 
                        $"or 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})");

            //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);
        }

        public static EpsTransactionModel GetEpsTrxByTTC(string ttc, int nozzleId)
        {
            string querySql = string.Format("select * from eps_trx where ttc = '{0}' and jihao = {1}", ttc, nozzleId);
            var epsTrxList = sqlCmd.ReadData<List<EpsTransactionModel>>(querySql, DbModelConstructors.ConstructEpsTrxModelList);
            if (epsTrxList != null)
                return epsTrxList.FirstOrDefault();
            else
                return null;
        }
        public static EpsTransactionModel GetEpsTrxByLiuShuiNO(string liushuino, int nozzleId)
        {
            string querySql = string.Format("select * from eps_trx where liushuino = '{0}' and jihao = {1}", liushuino, nozzleId);
            var epsTrxList = sqlCmd.ReadData<List<EpsTransactionModel>>(querySql, DbModelConstructors.ConstructEpsTrxModelList);
            if (epsTrxList != null)
                return epsTrxList.FirstOrDefault();
            else
                return null;
        }
        public static EpsTransactionModel RefreshEpsTrx(int id)
        {
            string querySql = string.Format("select * from eps_trx where id = '{0}' ", id);
            var epsTrxList = sqlCmd.ReadData<List<EpsTransactionModel>>(querySql, DbModelConstructors.ConstructEpsTrxModelList);
            if (epsTrxList != null)
                return epsTrxList.FirstOrDefault();
            else
                return null;
        }
        public static void UpdateEpsTrxByttc(string ttc, int nozzleId, decimal amount, string openId, string payMethod, EpsTrxStatus trxStatus)
        {

            string sql = string.Format("update eps_trx" +
                                       " set real_pay_amount = {0},trx_status = {1}, openId = '{2}', payMethod = '{3}'" +
                                       " where ttc = '{4}' and jihao = {5}",
                                        amount, (int)trxStatus, openId, payMethod, ttc, nozzleId);
            sqlCmd.UpdateData(sql);
        }
        public static void UpdateEpsTrxByliushuino(string liushuino, int nozzleId, decimal amount, EpsTrxStatus trxStatus)
        {
            string sql = string.Format("update eps_trx" +
                           " set real_pay_amount = {0},trx_status = {1}" +
                           " where liushuino = '{2}' and jihao = {3}",
                            amount, (int)trxStatus, liushuino, nozzleId);
            sqlCmd.UpdateData(sql);
        }
        public static void DeleteXiaoFei2(string liushuino, int nozzleId)
        {
            string sql = string.Format("delete from xiaofei2 where liushuino = '{0}' and jihao = {1}", liushuino, nozzleId);
            sqlCmd.DeleteData(sql);
        }
        /// <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());
        }

        public static void DeleteEpsTrxPendingBefore(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.BeforeFueling);

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


    }
}