using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace WebService.SDK
{
    public class FeedbackHelper
    {
        #region 方法 -> 收银机反馈记录接口
        /// <summary>
        /// 收银机反馈记录接口
        /// </summary>
        /// <param name="oracleHelper">数据库连接</param>
        /// <param name="feedbackType">反馈类型：
        /// StateFeedback、BaseInfoFeedback</param>
        /// <param name="jsonData">反馈信息Json数据字符串</param>
        /// <returns></returns>
        public FeedbackResult<string> Feedback(OracleHelper oracleHelper, string feedbackType, string jsonData)
        {
            switch (feedbackType.ToUpper())
            {
                case "STATEFEEDBACK":
                    //收银机状态反馈
                    return StateFeedback(oracleHelper,
                        Newtonsoft.Json.JsonConvert.DeserializeObject<
                            Model.StateFeedbackModel>(jsonData)) ?
                            new FeedbackResult<string>()
                            {
                                ResultCode = 100,
                                ResultDesc = $"{feedbackType}记录成功"
                            } :
                            new FeedbackResult<string>()
                            {
                                ResultCode = 305,
                                ResultDesc = $"{feedbackType}记录失败"
                            };
                case "BASEINFOFEEDBACK":
                    //收银机基础数据版本上报
                    return BaseInfoFeedback(oracleHelper,
                        Newtonsoft.Json.JsonConvert.DeserializeObject<
                            Model.BaseInfoFeedbackModel>(jsonData)) ?
                            new FeedbackResult<string>()
                            {
                                ResultCode = 100,
                                ResultDesc = $"{feedbackType}记录成功"
                            } :
                            new FeedbackResult<string>()
                            {
                                ResultCode = 305,
                                ResultDesc = $"{feedbackType}记录失败"
                            };
                case "DATACOLLECTION":
                    //十分钟实时交易统计上报
                    return DataCollectionFeedback(oracleHelper,
                        Newtonsoft.Json.JsonConvert.DeserializeObject<
                            Model.DataCollectionModel>(jsonData)) ?
                            new FeedbackResult<string>()
                            {
                                ResultCode = 100,
                                ResultDesc = $"{feedbackType}记录成功"
                            } :
                            new FeedbackResult<string>()
                            {
                                ResultCode = 305,
                                ResultDesc = $"{feedbackType}记录失败"
                            };
                case "COMMODITYMACHINE":
                    //收银指令结果上报
                    return CommodityMachineFeedback(oracleHelper,
                        Newtonsoft.Json.JsonConvert.DeserializeObject<
                            Model.CommodityMachineModel>(jsonData)) ?
                            new FeedbackResult<string>()
                            {
                                ResultCode = 100,
                                ResultDesc = $"{feedbackType}记录成功"
                            } :
                            new FeedbackResult<string>()
                            {
                                ResultCode = 305,
                                ResultDesc = $"{feedbackType}记录失败"
                            };
                case "MACHINEINFOFEEDBACK":
                    //收银机设备信息上报
                    return MachineInfoFeedback(oracleHelper,
                        Newtonsoft.Json.JsonConvert.DeserializeObject<
                            Model.MachineInfoModel>(jsonData)) ?
                        new FeedbackResult<string>()
                        {
                            ResultCode = 100,
                            ResultDesc = $"{feedbackType}记录成功"
                        } :
                        new FeedbackResult<string>()
                        {
                            ResultCode = 305,
                            ResultDesc = $"{feedbackType}记录失败"
                        };
                default:
                    return
                        new FeedbackResult<string>()
                        {
                            ResultCode = 305,
                            ResultDesc = $"暂不支持{feedbackType}类型记录"
                        };
            }
        }
        #endregion

        #region 方法 -> 收银机反馈数据读取接口
        /// <summary>
        /// 收银机反馈数据读取接口
        /// </summary>
        /// <param name="oracleHelper">数据库连接</param>
        /// <param name="readType">读取类型：
        /// CommodityMachine：获取设备指令数据信息
        /// DataVersion：获取数据版本及传输信息</param>
        /// <param name="jsonData">设备信息Json数据字符串</param>
        /// <returns></returns>
        public string DataRead(OracleHelper oracleHelper, string readType, string jsonData)
        {
            switch (readType.ToUpper())
            {
                case "COMMODITYMACHINE"://收银指令数据
                    return Newtonsoft.Json.JsonConvert.SerializeObject(GetCommodityMachine(oracleHelper,
                        Newtonsoft.Json.JsonConvert.DeserializeObject<Model.DeviceInfoModel>(jsonData)));
                case "DATAVERSION"://收银基础数据版本
                    return Newtonsoft.Json.JsonConvert.SerializeObject(GetDataVersion(oracleHelper,
                        Newtonsoft.Json.JsonConvert.DeserializeObject<Model.DeviceInfoModel>(jsonData)));
                default:
                    return Newtonsoft.Json.JsonConvert.SerializeObject(new FeedbackResult<string>()
                    {
                        ResultCode = 305,
                        ResultDesc = $"暂不支持{readType}类型读取"
                    });
            }
        }
        #endregion

        #region 方法 -> 收银机状态反馈记录
        /// <summary>
        /// 收银机状态反馈记录
        /// </summary>
        /// <param name="oracleHelper">数据库连接</param>
        /// <param name="stateFeedback">状态反馈数据对象</param>
        /// <returns></returns>
        private bool StateFeedback(OracleHelper oracleHelper, Model.StateFeedbackModel stateFeedback)
        {
            //判断必填字段是否为空
            if (stateFeedback == null ||                                                                              //设备信息实体对象
                string.IsNullOrWhiteSpace(stateFeedback.SERVERPARTCODE) ||                  //设备服务区信息
                string.IsNullOrWhiteSpace(stateFeedback.SHOPCODE) ||                            //设备门店信息
                string.IsNullOrWhiteSpace(stateFeedback.MACHINECODE) ||                      //设备编码信息
                string.IsNullOrWhiteSpace(stateFeedback.MACHINENAME) ||                     //设备名称信息
                string.IsNullOrWhiteSpace(stateFeedback.MACHINE_MACADDRESS))         //设备网卡信息
            {
                return false;
            }
            //读取设备标识缓存记录，减少数据库查询次数
            List<string> _StateFeedbackList = (List<string>)CacheHelper.Get("StateFeedback");
            if (_StateFeedbackList == null)
            {
                _StateFeedbackList = new List<string>();
            }
            //判断设备是否第一次上报反馈信息，然后写入设备反馈信息到数据库中
            //优先通过接口缓存的设备标识进行检测，当接口内不存在该设备的标识时，通过数据库查询进行检测
            if (_StateFeedbackList.Contains(stateFeedback.SERVERPARTCODE +
                stateFeedback.SHOPCODE + stateFeedback.MACHINECODE +
                stateFeedback.MACHINENAME + stateFeedback.MACHINE_MACADDRESS) ||
                oracleHelper.ExcuteSqlGetDataSet($@"SELECT 1 
                    FROM HIGHWAY_EXCHANGE.T_STATEFEEDBACK 
                    WHERE SERVERPARTCODE = '{stateFeedback.SERVERPARTCODE}' AND 
                        SHOPCODE = '{stateFeedback.SHOPCODE}' AND 
                        MACHINECODE = '{stateFeedback.MACHINECODE}' AND 
                        MACHINENAME = '{stateFeedback.MACHINENAME}' AND 
                        MACHINE_MACADDRESS = '{stateFeedback.MACHINE_MACADDRESS}' "
                        ).Tables[0].Rows.Count > 0)
            {
                //非首次上报设备心跳信息，更新已有数据
                UpdateTableData(oracleHelper, new List<Model.StateFeedbackModel>() { { stateFeedback } },
                    "HIGHWAY_EXCHANGE.T_STATEFEEDBACK", new string[] { "SERVERPARTCODE", "SHOPCODE",
                        "MACHINECODE", "MACHINENAME", "MACHINE_MACADDRESS" }, true);
            }
            else
            {
                //首次上报设备心跳信息，插入心跳数据
                InsertTableData<Model.StateFeedbackModel>(oracleHelper,
                    new List<Model.StateFeedbackModel>() { { stateFeedback } },
                    "HIGHWAY_EXCHANGE.T_STATEFEEDBACK");
            }
            //更新接口缓存设备标识记录，写入设备信息
            if (!_StateFeedbackList.Contains(stateFeedback.SERVERPARTCODE +
                stateFeedback.SHOPCODE + stateFeedback.MACHINECODE +
                stateFeedback.MACHINENAME + stateFeedback.MACHINE_MACADDRESS))
            {
                _StateFeedbackList.Add(stateFeedback.SERVERPARTCODE +
                    stateFeedback.SHOPCODE + stateFeedback.MACHINECODE +
                    stateFeedback.MACHINENAME + stateFeedback.MACHINE_MACADDRESS);
                CacheHelper.Set("StateFeedback", _StateFeedbackList);
            }
            return true;
        }
        #endregion

        #region 方法 -> 收银机器信息上报
        /// <summary>
        /// 收银机器信息上报
        /// </summary>
        /// <param name="oracleHelper">数据库连接</param>
        /// <param name="machineInfo">收银机器信息数据对象</param>
        /// <returns></returns>
        private bool MachineInfoFeedback(OracleHelper oracleHelper, Model.MachineInfoModel machineInfo)
        {
            //检查非空字段，服务区编码、门店编码、设备网卡地址
            if (machineInfo == null ||
                string.IsNullOrWhiteSpace(machineInfo.SERVERPARTCODE) ||
                string.IsNullOrWhiteSpace(machineInfo.SHOPCODE) ||
                string.IsNullOrWhiteSpace(machineInfo.MACHINE_MACADDRESS))
            {
                return false;
            }
            //从缓存中取出已保存过的机器信息
            List<string> _MachineInfoFeedbackList = (List<string>)CacheHelper.Get("MachineInfoFeedback");
            if (_MachineInfoFeedbackList == null)
            {
                _MachineInfoFeedbackList = new List<string>();
            }
            //判断机器信息是否已经上报过
            if (!_MachineInfoFeedbackList.Contains(machineInfo.MACHINE_MACADDRESS) &&
                oracleHelper.ExcuteSqlGetDataSet($@"SELECT 1 
                    FROM HIGHWAY_EXCHANGE.T_MACHINEINFO
                    WHERE MACHINE_MACADDRESS = '{machineInfo.MACHINE_MACADDRESS}'"
                    ).Tables[0].Rows.Count == 0)
            {
                //未上报过的机器信息插入数据库
                InsertTableData(oracleHelper, new List<Model.MachineInfoModel> { { machineInfo } },
                    "HIGHWAY_EXCHANGE.T_MACHINEINFO", true, "MACHINEINFO_ID",
                    "HIGHWAY_EXCHANGE.SEQ_MACHINEINFO.NEXTVAL");
            }
            //保存机器信息至缓存中，用于上报去重判断
            if (!_MachineInfoFeedbackList.Contains(machineInfo.MACHINE_MACADDRESS))
            {
                _MachineInfoFeedbackList.Add(machineInfo.MACHINE_MACADDRESS);
                CacheHelper.Set("MachineInfoFeedback", _MachineInfoFeedbackList);
            }
            return true;
        }
        #endregion

        #region 方法 -> 收银机基础数据版本记录
        /// <summary>
        /// 收银机基础数据版本记录
        /// </summary>
        /// <param name="oracleHelper">数据库连接</param>
        /// <param name="baseInfoFeedback">数据对象</param>
        /// <returns></returns>
        private bool BaseInfoFeedback(OracleHelper oracleHelper, Model.BaseInfoFeedbackModel baseInfoFeedback)
        {
            //检查非空字段，数据表名和设备网卡标识
            if (baseInfoFeedback == null ||
                string.IsNullOrWhiteSpace(baseInfoFeedback.TABLE_NAME) ||
                string.IsNullOrWhiteSpace(baseInfoFeedback.MACHINE_MACADDRESS))
            {
                return false;
            }
           //从缓存中取出已上报的收银机版本记录数据
            List<string> _BaseInfoFeedbackList = (List<string>)CacheHelper.Get("BaseInfoFeedback");
            if (_BaseInfoFeedbackList == null)
            {
                _BaseInfoFeedbackList = new List<string>();
            }
            //判断是否首次上报版本记录
            if (_BaseInfoFeedbackList.Contains(baseInfoFeedback.TABLE_NAME +
                baseInfoFeedback.MACHINE_MACADDRESS) ||
                oracleHelper.ExcuteSqlGetDataSet($@"SELECT 1 
                    FROM HIGHWAY_EXCHANGE.T_BASEINFOFEEDBACK
                    WHERE TABLE_NAME = '{baseInfoFeedback.TABLE_NAME}' AND 
                        MACHINE_MACADDRESS = '{baseInfoFeedback.MACHINE_MACADDRESS}' "
                        ).Tables[0].Rows.Count > 0)
            {
                //非首次上报，更新数据库已有记录
                UpdateTableData(oracleHelper, new List<Model.BaseInfoFeedbackModel> { { baseInfoFeedback } },
                    "HIGHWAY_EXCHANGE.T_BASEINFOFEEDBACK",
                    new string[] { "TABLE_NAME", "MACHINE_MACADDRESS" }, true);
            }
            else
            {
                //首次上报，插入记录到数据库
                InsertTableData(oracleHelper, new List<Model.BaseInfoFeedbackModel> { { baseInfoFeedback } },
                    "HIGHWAY_EXCHANGE.T_BASEINFOFEEDBACK", true, "BASEINFOFEEDBACK_ID",
                    "HIGHWAY_EXCHANGE.SEQ_BASEINFOFEEDBACK.NEXTVAL");
            }
            //将上报记录写入缓存，用于后续判断
            if (!_BaseInfoFeedbackList.Contains(baseInfoFeedback.TABLE_NAME +
                baseInfoFeedback.MACHINE_MACADDRESS))
            {
                _BaseInfoFeedbackList.Add(baseInfoFeedback.TABLE_NAME +
                    baseInfoFeedback.MACHINE_MACADDRESS);
                CacheHelper.Set("BaseInfoFeedback", _BaseInfoFeedbackList);
            }
            return true;
        }
        #endregion

        #region 方法 -> 实时交易统计记录
        /// <summary>
        /// 实时交易统计记录
        /// </summary>
        /// <param name="oracleHelper">数据库连接</param>
        /// <param name="dataCollectionModel">数据对象</param>
        /// <returns></returns>
        private bool DataCollectionFeedback(OracleHelper oracleHelper, Model.DataCollectionModel dataCollectionModel)
        {
            //关键字段判空处理
            if (dataCollectionModel == null ||
                dataCollectionModel.DATACOLLECTION_DATE == null ||
                string.IsNullOrWhiteSpace(dataCollectionModel.SERVERPARTCODE) ||
                string.IsNullOrWhiteSpace(dataCollectionModel.SHOPCODE) ||
                string.IsNullOrWhiteSpace(dataCollectionModel.MACADDRESS))
            {
                return false;
            }
            try
            {
                //插入数据记录至数据库
                InsertTableData(oracleHelper, new List<Model.DataCollectionModel> { { dataCollectionModel } },
                    "HIGHWAY_EXCHANGE.T_DATACOLLECTION", true, "DATACOLLECTION_ID",
                    "HIGHWAY_EXCHANGE.SEQ_DATACOLLECTION.NEXTVAL");
                return true;
            }
            catch
            {
                return false;
            }
        }
        #endregion

        #region 方法 -> 指令运行结果记录
        /// <summary>
        /// 指令运行结果记录
        /// </summary>
        /// <param name="oracleHelper">数据库连接</param>
        /// <param name="commodityMachineModel">指令数据对象</param>
        /// <returns></returns>
        private bool CommodityMachineFeedback(OracleHelper oracleHelper,
            Model.CommodityMachineModel commodityMachineModel)
        {
            try
            {
                List<Model.CommodityMachineModel> _CommodityMachineList =
                    (List<Model.CommodityMachineModel>)CacheHelper.Get("CommodityMachine");
                bool b_UpdateData = true;
                if (_CommodityMachineList != null)
                {
                    var _CommodityMachine = _CommodityMachineList.Where(p =>
                    {
                        return p.COMMODITYMACHINE_ID == commodityMachineModel.COMMODITYMACHINE_ID &&
                        p.HANDWAY == commodityMachineModel.HANDWAY &&
                        p.MACHINE_MACADDRESS == commodityMachineModel.MACHINE_MACADDRESS;
                    }).FirstOrDefault();
                    //更新缓存记录
                    if (_CommodityMachine != null)
                    {
                        //上报的结果与上一次回传的一致时，不执行数据库更新操作
                        if (_CommodityMachine.UPLOAD_STATE == commodityMachineModel.UPLOAD_STATE &&
                            _CommodityMachine.REMARK_DESC == commodityMachineModel.REMARK_DESC)
                        {
                            b_UpdateData = false;
                        }
                        _CommodityMachine = commodityMachineModel;
                    }
                }
                if (b_UpdateData)
                {
                    //更新数据记录
                    UpdateTableData(oracleHelper, new List<Model.CommodityMachineModel> { commodityMachineModel },
                        "HIGHWAY_EXCHANGE.T_COMMODITYMACHINE",
                        new string[] { "COMMODITYMACHINE_ID", "HANDWAY", "MACHINE_MACADDRESS" },
                        true, new string[] { "UPLOAD_STATE", "REMARK_DESC" });
                }
                return true;
            }
            catch (Exception ex)
            {
                //LogHelper.WriteSendLog("指令执行上报异常：" + ex.Message);
                return false;
            }
        }
        #endregion

        #region 方法 -> 收银机指令数据读取
        /// <summary>
        /// 收银机指令数据读取
        /// </summary>
        /// <param name="oracleHelper">数据库连接</param>
        /// <param name="deviceInfoModel">设备信息对象</param>
        /// <returns></returns>
        private FeedbackResult<Model.CommodityMachineModel> GetCommodityMachine(
            OracleHelper oracleHelper, Model.DeviceInfoModel deviceInfoModel)
        {
            try
            {
                List<Model.CommodityMachineModel> _CommodityMachineList =
                    (List<Model.CommodityMachineModel>)CacheHelper.Get("CommodityMachine");
                if (_CommodityMachineList == null)
                {
                    _CommodityMachineList =
                        Newtonsoft.Json.JsonConvert.DeserializeObject<List<Model.CommodityMachineModel>>(
                        Newtonsoft.Json.JsonConvert.SerializeObject(oracleHelper.ExcuteSqlGetDataSet(
                            $@"SELECT COMMODITYMACHINE_ID,SERVERPARTCODE,SHOPCODE,
                                MACHINENAME,MACHINE_MACADDRESS,MACHINE_IP,MACHINE_STARTDATE,
                                MACHINE_ENDDATE,HANDCONTENT,HANDWAY,FLAG,REPEAT_STATE,
                                DOWNLOAD_STATE,UPLOAD_STATE,INTERVALS_TIME,REMARK_DESC
                            FROM HIGHWAY_EXCHANGE.T_COMMODITYMACHINE
                            WHERE NVL(UPLOAD_STATE,0) <> 3 ").Tables[0]));
                    CacheHelper.Set("CommodityMachine", _CommodityMachineList, DateTimeOffset.Now.AddMinutes(5));
                }

                return new FeedbackResult<Model.CommodityMachineModel>()
                {
                    ResultCode = 100,
                    ResultDesc = "读取成功",
                    Data = _CommodityMachineList.FindAll(p =>
                    {
                        return p.MACHINE_MACADDRESS == deviceInfoModel.DeviceMacaddress;
                    })
                };
            }
            catch (Exception ex)
            {
                return new FeedbackResult<Model.CommodityMachineModel>()
                {
                    ResultCode = 999,
                    ResultDesc = $"系统异常：{ex.Message}"
                };
            }
        }
        #endregion

        #region 方法 -> 获取收银数据版本信息
        /// <summary>
        /// 获取收银数据版本信息
        /// </summary>
        /// <param name="oracleHelper">数据库连接</param>
        /// <param name="deviceInfoModel">设备信息对象</param>
        /// <returns></returns>
        private FeedbackResult<Model.DataVersionModel> GetDataVersion(
            OracleHelper oracleHelper, Model.DeviceInfoModel deviceInfoModel)
        {
            try
            {
                //从缓存读取数据版本记录
                List<Model.DataVersionModel> _DataVersionList =
                    (List<Model.DataVersionModel>)CacheHelper.Get("DataVersion");
                if (_DataVersionList == null || _DataVersionList.Count == 0)
                {
                    //缓存无数据时从数据库逐表读取版本记录
                    List<Model.DataVersionModel> _ServerDataVersionList = new List<Model.DataVersionModel>();
                    List<string> list_Sql = DataVersionSqlByOracle();
                    foreach (string str_Select in list_Sql)
                    {
                        //获取各数据表信息版本记录
                        _ServerDataVersionList = _ServerDataVersionList.Concat(Newtonsoft.Json.JsonConvert.DeserializeObject<List<Model.DataVersionModel>>(
                            Newtonsoft.Json.JsonConvert.SerializeObject(oracleHelper.ExcuteSqlGetDataSet(str_Select).Tables[0]))).ToList();
                    }
                    _DataVersionList = new List<Model.DataVersionModel>(_ServerDataVersionList);
                    //将数据版本记录保存到缓存中
                    CacheHelper.Set("DataVersion", _DataVersionList, DateTimeOffset.Now.AddSeconds(30));
                }
                //返回所查询的收银机对应数据版本记录
                return new FeedbackResult<Model.DataVersionModel>()
                {
                    ResultCode = 100,
                    ResultDesc = "读取成功",
                    Data = _DataVersionList.FindAll(p =>
                    {
                        return p.ServerPartCode == deviceInfoModel.ServerPartCode &&
                        p.ShopCode == deviceInfoModel.ShopCode &&
                        (p.MachineCode == null || p.MachineCode == deviceInfoModel.MachineCode);
                    })
                };
            }
            catch (Exception ex)
            {
                return new FeedbackResult<Model.DataVersionModel>()
                {
                    ResultCode = 999,
                    ResultDesc = $"系统异常：{ex.Message}"
                };
            }
        }
        #endregion

        #region 方法 -> 从数据库获取数据版本信息

        /// <summary>
        /// 数据版本查询语句列表
        /// </summary>
        /// <returns></returns>
        private List<string> DataVersionSqlByOracle()
        {
            return new List<string>
            {
                //各门店商品版本
                @"SELECT B.SERVERPART_CODE AS SERVERPARTCODE,B.SHOPCODE,
                    NULL AS MachineCode,'COMMODITYEX' AS DataTableName,
                    MAX(A.OPERATE_DATE) AS DataVersion,0 AS TransState
                FROM HIGHWAY_EXCHANGE.T_COMMODITYEX_EX A,
                    HIGHWAY_EXCHANGE.T_SHOPMESSAGE_EX B
                WHERE A.SERVERPARTCODE = B.SERVERPART_CODE AND 
                    A.BUSINESSTYPE = B.BUSINESSTYPE AND 
                    B.SERVERPARTSHOP_STATE = 1 AND A.COMMODITY_STATE = 1 AND 
                    B.SHOPCODE IS NOT NULL AND A.OPERATE_DATE <= SYSDATE
                GROUP BY B.SERVERPART_CODE,B.SHOPCODE",
                //各门店收银工号版本
                @"SELECT B.SERVERPART_CODE AS SERVERPARTCODE,B.SHOPCODE,
                    NULL AS MachineCode,'SELLWORKER' AS DataTableName,
                    MAX(A.DOWNLOADDATE) AS DataVersion,0 AS TransState
                FROM HIGHWAY_EXCHANGE.T_SELLWORKER_EX A,
                    HIGHWAY_EXCHANGE.T_SHOPMESSAGE_EX B
                WHERE A.SERVERPARTCODE = B.SERVERPART_CODE AND 
                    B.SERVERPARTSHOP_STATE = 1 AND B.SHOPCODE IS NOT NULL
                GROUP BY B.SERVERPART_CODE,B.SHOPCODE",
                //各门店会员数据版本
                @"SELECT B.SERVERPART_CODE AS SERVERPARTCODE,B.SHOPCODE,
                    NULL AS MachineCode,'MEMBERSHIP' AS DataTableName,
                    MAX(A.OPERATE_DATE) AS DataVersion,0 AS TransState
                FROM HIGHWAY_EXCHANGE.T_MEMBERSHIP A,
                    HIGHWAY_EXCHANGE.T_SHOPMESSAGE_EX B
                WHERE B.SERVERPARTSHOP_STATE = 1 AND B.SHOPCODE IS NOT NULL
                GROUP BY B.SERVERPART_CODE,B.SHOPCODE",
                //各门店促销活动数据版本
                @"SELECT B.SERVERPART_CODE AS SERVERPARTCODE,B.SHOPCODE,
                    NULL AS MachineCode,'SALESPROMOTE' AS DataTableName,
                    MAX(A.SALESPROMOTE_DATE) AS DataVersion,0 AS TransState
                FROM HIGHWAY_EXCHANGE.T_SALESPROMOTE_EX A,
                    HIGHWAY_EXCHANGE.T_SHOPMESSAGE_EX B
                WHERE A.SERVERPARTCODE = B.SERVERPART_CODE AND 
                    A.SHOPCODE = B.SHOPCODE AND B.SERVERPARTSHOP_STATE = 1 AND 
                    B.SHOPCODE IS NOT NULL
                GROUP BY B.SERVERPART_CODE,B.SHOPCODE",
                //新系统各收银机日结数据版本
                @"SELECT B.SERVERPART_CODE AS SERVERPARTCODE,B.SHOPCODE,A.MACHINECODE,
                    (CASE WHEN A.ENDACCOUNT_TYPE = 1 
                    THEN 'ENDACCOUNT_NEW' ELSE 'INSPECTION' END) AS DataTableName,
                    MAX(A.ENDDATE) AS DataVersion,MIN(A.TRANSFER_STATE) AS TransState
                FROM HIGHWAY_EXCHANGE.T_ENDACCOUNT_NEW A,
                    HIGHWAY_EXCHANGE.T_SHOPMESSAGE_EX B
                WHERE A.SERVERPARTCODE = B.SERVERPART_CODE AND 
                    A.SHOPCODE = B.SHOPCODE AND B.SERVERPARTSHOP_STATE = 1 AND 
                    A.ENDACCOUNT_TYPE IN (1,5,6) AND 
                    A.ENDDATE IS NOT NULL AND B.SHOPCODE IS NOT NULL
                GROUP BY B.SERVERPART_CODE,B.SHOPCODE,
                    A.MACHINECODE,A.ENDACCOUNT_TYPE",
                //旧系统各收银机日结数据版本
                @"SELECT B.SERVERPART_CODE AS SERVERPARTCODE,B.SHOPCODE,A.MACHINECODE,
                    (CASE WHEN A.FLAG = 1 THEN 'ENDACCOUNT_NEW' ELSE 'INSPECTION' END) AS DATATABLENAME,
                    MAX(A.ENDACCOUNT_DATE) AS DATAVERSION,MIN(A.TRANSFER_STATE) AS TRANSSTATE
                FROM HIGHWAY_EXCHANGE.T_ENDACCOUNT A,
                    HIGHWAY_EXCHANGE.T_SHOPMESSAGE_EX B
                WHERE A.SERVERPARTCODE = B.SERVERPART_CODE AND 
                    A.SHOPCODE = B.SHOPCODE AND B.SERVERPARTSHOP_STATE = 1 AND 
                    A.FLAG IN (1,5,6) AND A.ENDACCOUNT_DATE IS NOT NULL AND B.SHOPCODE IS NOT NULL
                GROUP BY B.SERVERPART_CODE,B.SHOPCODE,A.MACHINECODE,A.FLAG",
                //新系统各收银机交班数据版本
                @"SELECT B.SERVERPART_CODE AS SERVERPARTCODE,B.SHOPCODE,
                    A.MACHINECODE,'PERSONSELL_NEW' AS DataTableName,
                    MAX(A.ENDDATE) AS DataVersion,MIN(A.TRANSFER_STATE) AS TransState
                FROM HIGHWAY_EXCHANGE.T_PERSONSELL_NEW A,
                    HIGHWAY_EXCHANGE.T_SHOPMESSAGE_EX B
                WHERE A.SERVERPARTCODE = B.SERVERPART_CODE AND 
                    A.SHOPCODE = B.SHOPCODE AND B.SERVERPARTSHOP_STATE = 1 AND 
                    A.ENDDATE IS NOT NULL AND B.SHOPCODE IS NOT NULL AND 
                    A.MACHINECODE IS NOT NULL
                GROUP BY B.SERVERPART_CODE,B.SHOPCODE,A.MACHINECODE",
                //新系统各收银机单品数据版本
                @"SELECT A.SERVERPARTCODE,A.SHOPCODE,
                    A.MACHINECODE,'TRANSFER_SALE' AS DataTableName,
                    MAX(A.ENDDATE) AS DataVersion,MIN(C.TRANSFER_STATE) AS TransState
                FROM HIGHWAY_EXCHANGE.T_ENDACCOUNT_NEW A,
	                HIGHWAY_EXCHANGE.T_COMMODITYSALE_NEW C,
                    HIGHWAY_EXCHANGE.T_SHOPMESSAGE_EX B
                WHERE A.ENDACCOUNT_CODE = C.ENDACCOUNT_CODE AND 
                    A.SERVERPARTCODE = B.SERVERPART_CODE AND 
                    A.SHOPCODE = B.SHOPCODE AND B.SERVERPARTSHOP_STATE = 1 AND 
                    B.SHOPCODE IS NOT NULL AND A.MACHINECODE IS NOT NULL
                GROUP BY A.SERVERPARTCODE,A.SHOPCODE,A.MACHINECODE",
                //旧系统各收银机单品数据版本
                @"SELECT A.SERVERPARTCODE,A.SHOPCODE,
                    A.MACHINECODE,'TRANSFER_SALE' AS DataTableName,
                    MAX(A.ENDDATE) AS DataVersion,MIN(A.TRANSFER_STATE) AS TransState
                FROM HIGHWAY_EXCHANGE.T_COMMODITYSALE A,
	                HIGHWAY_EXCHANGE.T_SHOPMESSAGE_EX B
                WHERE A.SERVERPARTCODE = B.SERVERPART_CODE AND 
                    A.SHOPCODE = B.SHOPCODE AND B.SERVERPARTSHOP_STATE = 1 AND 
                    A.ENDDATE IS NOT NULL AND B.SHOPCODE IS NOT NULL AND 
                    A.MACHINECODE IS NOT NULL
                GROUP BY A.SERVERPARTCODE,A.SHOPCODE,A.MACHINECODE",
                //各收银机移动支付记录数据版本
                @"SELECT A.SERVERPARTCODE,A.SHOPCODE,
                    A.MACHINECODE,'MOBILE_PAY' AS DataTableName,
                    MAX(A.MOBILEPAY_DATE) AS DataVersion,MIN(A.MOBILEPAY_STATE) AS TransState
                FROM HIGHWAY_EXCHANGE.T_MOBILE_PAY A,
                    HIGHWAY_EXCHANGE.T_SHOPMESSAGE_EX B
                WHERE A.SERVERPARTCODE = B.SERVERPART_CODE AND 
                    A.SHOPCODE = B.SHOPCODE AND B.SERVERPARTSHOP_STATE = 1 AND 
                    B.SHOPCODE IS NOT NULL AND NVL(A.MOBILEPAY_RESULT,0) <> 9
                GROUP BY A.SERVERPARTCODE,A.SHOPCODE,A.MACHINECODE",
                //各收银机会员钱包离线交易数据版本
                @"SELECT A.SERVERPARTCODE,A.SHOPCODE,
                    A.MACHINECODE,'RECHARGERECORD' AS DataTableName,
                    MAX(A.RECODE_DATE) AS DataVersion,MIN(A.TRANSFER_STATE) AS TransState
                FROM HIGHWAY_EXCHANGE.T_RECHARGERECORD A,
                    HIGHWAY_EXCHANGE.T_SHOPMESSAGE_EX B
                WHERE A.SERVERPARTCODE = B.SERVERPART_CODE AND 
                    A.SHOPCODE = B.SHOPCODE AND B.SERVERPARTSHOP_STATE = 1 AND 
                    A.RECODE_TYPE IN (3000,3020,4000) AND 
                    B.SHOPCODE IS NOT NULL AND A.MACHINECODE IS NOT NULL
                GROUP BY A.SERVERPARTCODE,A.SHOPCODE,A.MACHINECODE"
            };
        }
        #endregion

        #region 方法 -> 通用表数据添加
        /// <summary>
        /// 通用表数据添加
        /// Mr.Cai 2018-1-2
        /// </summary>
        /// <param name="oracleHelper">数据库SQL执行帮助类</param>
        /// <param name="t">实体类</param>
        /// <param name="tableName">表名</param>
        /// <param name="primaryKey">主键名</param>
        /// <param name="nextval">主键自增SQL函数</param>
        /// <returns></returns>
        private void InsertTableData<T>(OracleHelper oracleHelper, T t, string tableName, string primaryKey, string nextval)
        {
            if (string.IsNullOrEmpty(tableName))
            {
                throw new Exception("表名不可为空！");
            }

            if (string.IsNullOrEmpty(primaryKey))
            {
                throw new Exception("表名主键名不可为空！");
            }

            if (string.IsNullOrEmpty(nextval))
            {
                throw new Exception("序列不可为空！");
            }

            try
            {
                var pros = typeof(T).GetProperties();
                string names = string.Empty;
                string values = string.Empty;
                foreach (var item in pros)
                {
                    //排除主键字段
                    if (item.Name.ToUpper() != primaryKey.ToUpper() &&
                        item.Name.ToUpper() != (primaryKey + "_Encrypt").ToUpper() &&
                        item.Name.ToUpper() != "KEYID")
                    {
                        object value = item.GetValue(t, null);

                        if (IsType(item.PropertyType, "System.String") ||
                            item.PropertyType == typeof(System.String))
                        {
                            if (value != null && !string.IsNullOrEmpty(value.ToString()))
                            {
                                names += (string.IsNullOrEmpty(names) ? "" : ",") + item.Name;
                                values += (string.IsNullOrEmpty(values) ? "" : ",") + "'" + value.ToString().Replace("'", "''") + "'";
                            }
                            continue;
                        }

                        if (IsType(item.PropertyType, "System.Nullable`1[System.Int16]") ||
                            IsType(item.PropertyType, "System.Nullable`1[System.Int32]") ||
                            IsType(item.PropertyType, "System.Nullable`1[System.Int64]") ||
                            IsType(item.PropertyType, "System.Nullable`1[System.Double]") ||
                            IsType(item.PropertyType, "System.Nullable`1[System.Decimal]") ||
                            IsType(item.PropertyType, "System.Nullable`1[System.Boolean]") ||
                            item.PropertyType == typeof(System.Int16) ||
                            item.PropertyType == typeof(System.Int32) ||
                            item.PropertyType == typeof(System.Int64) ||
                            item.PropertyType == typeof(System.Double) ||
                            item.PropertyType == typeof(System.Decimal) ||
                            item.PropertyType == typeof(System.Boolean))
                        {
                            if (value != null)
                            {
                                names += (string.IsNullOrEmpty(names) ? "" : ",") + item.Name;
                                values += (string.IsNullOrEmpty(values) ? "" : ",") + value;
                            }
                            continue;
                        }

                        if (IsType(item.PropertyType, "System.Nullable`1[System.DateTime]") ||
                            item.PropertyType == typeof(System.DateTime))
                        {
                            if (value != null)
                            {
                                names += (string.IsNullOrEmpty(names) ? "" : ",") + item.Name;
                                values += (string.IsNullOrEmpty(values) ? "" : ",") +
                                    string.Format("TO_DATE('{0}','YYYY/MM/DD HH24:MI:SS')", value);
                            }
                            else
                            {
                                names += (string.IsNullOrEmpty(names) ? "" : ",") + item.Name;
                                values += (string.IsNullOrEmpty(values) ? "" : ",") +
                                    string.Format("TO_DATE('{0}','YYYY/MM/DD HH24:MI:SS')", DateTime.Now.ToString());
                            }
                            continue;
                        }
                    }

                }

                string _SqlString = string.Format("INSERT INTO {0}({1},{2}) VALUES({3},{4})", tableName, primaryKey, names, nextval, values);
                oracleHelper.ExcuteSql(_SqlString);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region 方法 -> 通用表数据添加（集合）
        /// <summary>
        /// 通用表数据添加（集合）
        /// Mr.Cai 2018-3-9
        /// </summary>
        /// <param name="oracleHelper">数据库SQL执行帮助类</param>
        /// <param name="listData">实体类集合</param>
        /// <param name="tableName">表名</param>
        /// <param name="isPrimaryKey">是否使用自增主键（序列）</param>
        /// <param name="primaryKey">主键名</param>
        /// <param name="nextval">主键自增SQL函数</param>
        private void InsertTableData<T>(OracleHelper oracleHelper, List<T> listData, string tableName,
            bool isPrimaryKey = false, string primaryKey = "", string nextval = "")
        {
            if (listData == null)
            {
                throw new Exception("数据集合不可为空！");
            }

            if (string.IsNullOrEmpty(tableName))
            {
                throw new Exception("表名不可为空！");
            }

            if (isPrimaryKey)//不插入原来主键，使用自增主键（序列），则判断必要条件
            {
                if (string.IsNullOrEmpty(primaryKey))
                {
                    throw new Exception("表名主键名不可为空！");
                }

                if (string.IsNullOrEmpty(nextval))
                {
                    throw new Exception("序列不可为空！");
                }
            }

            try
            {
                List<string> sqlStringList = new List<string>();
                string sqlString = string.Empty;
                foreach (T t in listData)
                {
                    var pros = typeof(T).GetProperties();
                    string names = string.Empty;
                    string values = string.Empty;
                    foreach (var item in pros)
                    {
                        // isPrimaryKey = true 排除主键字段
                        if ((isPrimaryKey && item.Name.ToUpper() == primaryKey.ToUpper()) ||
                            item.Name.ToUpper().Contains((primaryKey + "_Encrypt").ToUpper()) ||
                            item.Name.ToUpper() == "KEYID")
                        {
                            continue;
                        }

                        object value = item.GetValue(t, null);

                        if (IsType(item.PropertyType, "System.String") ||
                            item.PropertyType == typeof(System.String))
                        {
                            if (value != null && !string.IsNullOrEmpty(value.ToString()))
                            {
                                names += (string.IsNullOrEmpty(names) ? "" : ",") + item.Name;
                                values += (string.IsNullOrEmpty(values) ? "" : ",") + "'" + value.ToString().Replace("'", "''") + "'";
                            }
                            continue;
                        }

                        if (IsType(item.PropertyType, "System.Nullable`1[System.Int16]") ||
                            IsType(item.PropertyType, "System.Nullable`1[System.Int32]") ||
                            IsType(item.PropertyType, "System.Nullable`1[System.Int64]") ||
                            IsType(item.PropertyType, "System.Nullable`1[System.Double]") ||
                            IsType(item.PropertyType, "System.Nullable`1[System.Decimal]") ||
                            IsType(item.PropertyType, "System.Nullable`1[System.Boolean]") ||
                            item.PropertyType == typeof(System.Int16) ||
                            item.PropertyType == typeof(System.Int32) ||
                            item.PropertyType == typeof(System.Int64) ||
                            item.PropertyType == typeof(System.Double) ||
                            item.PropertyType == typeof(System.Decimal) ||
                            item.PropertyType == typeof(System.Boolean))
                        {
                            if (value != null)
                            {
                                names += (string.IsNullOrEmpty(names) ? "" : ",") + item.Name;
                                values += (string.IsNullOrEmpty(values) ? "" : ",") + value;
                            }
                            continue;
                        }

                        if (IsType(item.PropertyType, "System.Nullable`1[System.DateTime]") ||
                            item.PropertyType == typeof(System.DateTime))
                        {
                            if (value != null)
                            {
                                names += (string.IsNullOrEmpty(names) ? "" : ",") + item.Name;
                                values += (string.IsNullOrEmpty(values) ? "" : ",") +
                                    string.Format("TO_DATE('{0}','YYYY/MM/DD HH24:MI:SS')", value);
                            }
                            continue;
                        }
                    }
                    if (isPrimaryKey)
                    {
                        sqlStringList.Add(string.Format("INSERT INTO {0}({1},{2}) VALUES({3},{4})", tableName, primaryKey, names, nextval, values));
                    }
                    else
                    {
                        sqlStringList.Add(string.Format("INSERT INTO {0}({1}) VALUES({2})", tableName, names, values));
                    }
                }

                if (sqlStringList != null && sqlStringList.Count > 0)
                {
                    oracleHelper.ExecuteSqlTran(sqlStringList);
                }
                else
                {
                    throw new Exception("SQL语句不可为空！");
                }
            }
            catch (Exception ex)
            {
                //LogHelper.WriteSendLog(ex.Message);
                throw ex;
            }
        }
        #endregion

        #region 方法 -> 通用表数据修改
        /// <summary>
        /// 通用表数据修改
        /// Mr.Cai 2018-3-15
        /// </summary>
        /// <param name="oracleHelper">数据库SQL执行帮助类</param>
        /// <param name="listData">实体类集合</param>
        /// <param name="tableName">表名</param>
        /// <param name="whereName">条件名数组</param>
        /// <param name="updateNull">是否更新空值字段</param>
        private void UpdateTableData<T>(OracleHelper oracleHelper, List<T> listData, string tableName, 
            string[] whereName, bool updateNull = false, string[] updateName = null)
        {
            if (listData == null)
            {
                throw new Exception("数据集合不可为空！");
            }

            if (whereName == null)
            {
                throw new Exception("修改条件名不可为空！");
            }

            if (string.IsNullOrEmpty(tableName))
            {
                throw new Exception("表名不可为空！");
            }

            try
            {
                string[] sqlString = new string[listData.Count];
                int index = 0;//索引
                foreach (T t in listData)
                {
                    var pros = typeof(T).GetProperties();
                    string modifiedValues = string.Empty;
                    string whereModified = string.Empty;
                    foreach (var item in pros)
                    {
                        // isPrimaryKey = true 排除主键字段
                        if (item.Name.ToUpper().Contains(("_Encrypt").ToUpper()) || item.Name.ToUpper() == "KEYID")
                        {
                            continue;
                        }

                        object value = item.GetValue(t, null);

                        if (IsType(item.PropertyType, "System.String") ||
                            item.PropertyType == typeof(System.String))
                        {
                            if (value != null && !string.IsNullOrEmpty(value.ToString()))
                            {
                                if (updateName == null || updateName.Contains(item.Name.ToUpper()))
                                {
                                    modifiedValues += (string.IsNullOrEmpty(modifiedValues) ? "" : ",") +
                                        item.Name + " = " + "'" + value.ToString().Replace("'", "''") + "'";
                                }
                                for (int i = 0; i < whereName.Length; i++)
                                {
                                    if (whereName[i].ToUpper().Equals(item.Name.ToUpper()))
                                    {
                                        whereModified += (string.IsNullOrEmpty(whereModified) ? "" : " AND ") +
                                            item.Name + " = " + "'" + value + "'";
                                    }
                                }
                            }
                            else
                            {
                                if (updateNull && (updateName == null || updateName.Contains(item.Name.ToUpper())))
                                {
                                    modifiedValues += (string.IsNullOrEmpty(modifiedValues) ? "" : ",") + item.Name + " = " + "NULL";
                                }
                            }
                            continue;
                        }

                        if (IsType(item.PropertyType, "System.Nullable`1[System.Int16]") ||
                            IsType(item.PropertyType, "System.Nullable`1[System.Int32]") ||
                            IsType(item.PropertyType, "System.Nullable`1[System.Int64]") ||
                            IsType(item.PropertyType, "System.Nullable`1[System.Double]") ||
                            IsType(item.PropertyType, "System.Nullable`1[System.Decimal]") ||
                            IsType(item.PropertyType, "System.Nullable`1[System.Boolean]") ||
                            item.PropertyType == typeof(System.Int16) ||
                            item.PropertyType == typeof(System.Int32) ||
                            item.PropertyType == typeof(System.Int64) ||
                            item.PropertyType == typeof(System.Double) ||
                            item.PropertyType == typeof(System.Decimal) ||
                            item.PropertyType == typeof(System.Boolean))
                        {
                            if (value != null)
                            {
                                if (updateName == null || updateName.Contains(item.Name.ToUpper()))
                                {
                                    modifiedValues += (string.IsNullOrEmpty(modifiedValues) ? "" : ",") + item.Name + " = " + value;
                                }
                                for (int i = 0; i < whereName.Length; i++)
                                {
                                    if (whereName[i].ToUpper().Equals(item.Name.ToUpper()))
                                    {
                                        whereModified += (string.IsNullOrEmpty(whereModified) ? "" : " AND ") + item.Name + " = " + value;
                                    }
                                }
                            }
                            else
                            {
                                if (updateNull && (updateName == null || updateName.Contains(item.Name.ToUpper())))
                                {
                                    modifiedValues += (string.IsNullOrEmpty(modifiedValues) ? "" : ",") + item.Name + " = NULL";
                                }
                            }
                            continue;
                        }

                        if (IsType(item.PropertyType, "System.Nullable`1[System.DateTime]") ||
                            item.PropertyType == typeof(System.DateTime))
                        {
                            if (value != null)
                            {
                                if (updateName == null || updateName.Contains(item.Name.ToUpper()))
                                {
                                    modifiedValues += (string.IsNullOrEmpty(modifiedValues) ? "" : ",") + item.Name + " = " +
                                        string.Format("TO_DATE('{0}','YYYY/MM/DD HH24:MI:SS')", value);
                                }
                                for (int i = 0; i < whereName.Length; i++)
                                {
                                    if (whereName[i].ToUpper().Equals(item.Name.ToUpper()))
                                    {
                                        whereModified += (string.IsNullOrEmpty(whereModified) ? "" : " AND ") + item.Name + " = " +
                                            string.Format("TO_DATE('{0}','YYYY/MM/DD HH24:MI:SS')", value);
                                    }
                                }
                            }
                            else
                            {
                                if (updateNull && (updateName == null || updateName.Contains(item.Name.ToUpper())))
                                {
                                    modifiedValues += (string.IsNullOrEmpty(modifiedValues) ? "" : ",") + item.Name + " = NULL";
                                }
                            }
                            continue;
                        }
                    }

                    sqlString[index] += string.Format("UPDATE {0} SET {1} WHERE {2}", tableName, modifiedValues, whereModified);
                    index++;
                }
                oracleHelper.ExecuteSqlTran(sqlString);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region 方法 -> 类型匹配
        /// <summary>
        /// 类型匹配
        /// </summary>
        /// <param name="type">类型</param>
        /// <param name="typeName">类型名称</param>
        /// <returns></returns>
        private bool IsType(Type type, string typeName)
        {
            if (type.ToString() == typeName)
                return true;
            if (type.ToString() == "System.Object")
                return false;
            return IsType(type.BaseType, typeName);
        }
        #endregion

    }

    public class FeedbackResult<T>
    {
        public int ResultCode { get; set; }
        public string ResultDesc { get; set; }
        public List<T> Data { get; set; }
    }
}