using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Net.Http;
using System.Text;
using System.Threading.Tasks;
using System.Web;
using System.Web.Http;
using System.Xml;
using Dapper;
using Hangfire;
using Learun.Application.TwoDevelopment.ReceiveSendFeeManagement;

namespace DigitalSchoolApi.Controllers
{
    public class PayFeeResultTwoController : BaseController
    {

        private static string _coresqlConnection =
            ConfigurationManager.ConnectionStrings["CoreDBString"].ConnectionString;
        private static string _sqlConnection =
            ConfigurationManager.ConnectionStrings["ConnectionPfcMisDBString"].ConnectionString;
        private static string payresulturl =
            ConfigurationManager.AppSettings["payresulturl"];


        /// <summary>
        /// 定时更新线上收费状态到新生表
        /// </summary>
        /// <returns></returns>
        public IHttpActionResult SyncPayStatusToFresh()
        {
            RecurringJob.AddOrUpdate("SyncPayStatusToFresh",
                () => SyncStuInfoFreshPayStatus(),
                Cron.Minutely, TimeZoneInfo.Local);
            return Ok();
        }

        /// <summary>
        /// 更新线上收费状态到新生表
        /// </summary>
        public void SyncStuInfoFreshPayStatus()
        {
            try
            {
                using (IDbConnection conn = new SqlConnection(_sqlConnection))
                {
                    conn.Execute(@"update StuInfoFresh set payfeestatus=1 
                    from FinaChargeStuYear a left join StuInfoFresh b on a.stuno = b.stuno where a.PayFeeStatus <> 0");
                }
            }
            catch (Exception e)
            {
                using (IDbConnection conncore = new SqlConnection(_coresqlConnection))
                {
                    conncore.Execute(
                        "insert into LR_Base_Log(F_LogId,F_CategoryId,F_SourceObjectId,F_SourceContentJson,F_OperateTime) values(newid(),122,'syncpaystatustofresh','" + e.Message + "',getdate())");
                }
            }
        }

        /// <summary>
        /// 每分钟执行缴费后处理
        /// </summary>
        /// <returns></returns>
        public IHttpActionResult GetLastTenMinutes()
        {
            RecurringJob.AddOrUpdate("PayFeeResultMinutes20",
                () => GetResult(true),
                Cron.Minutely, TimeZoneInfo.Local);
            return Ok();
        }
        /// <summary>
        /// 每天执行缴费后处理
        /// </summary>
        /// <returns></returns>
        public IHttpActionResult GetLastDay()
        {
            RecurringJob.AddOrUpdate("PayFeeResultDay20",
                () => GetResult(false),
                Cron.Daily(0), TimeZoneInfo.Local);
            return Ok();
        }
        /// <summary>
        /// excel开发票任务
        /// </summary>
        /// <returns></returns>
        public IHttpActionResult SetUnInvoiceHandle()
        {
            BackgroundJob.Enqueue(() => UnInvoiceHandle(true));
            return Ok();
        }
        /// <summary>
        /// 根据年度学生缴费id触发开票任务
        /// </summary>
        /// <param name="FSYID"></param>
        /// <returns></returns>
        [HttpPost]
        public IHttpActionResult SetUnInvoiceHandleByFSYID(string FSYID)
        {

            BackgroundJob.Enqueue(() => DoUnInvoiceHandleByFSYID(FSYID));
            return Ok();
        }

        /// <summary>
        /// 补开发票
        /// </summary>
        /// <param name="isexcel">是否excel导入</param>
        /// <returns></returns>
        public IHttpActionResult DoUnInvoiceHandle(bool isexcel)
        {
            UnInvoiceHandle(false);
            return Ok();
        }

        /// <summary>
        /// 根据学生缴费订单id手动处理缴费结果
        /// </summary>
        /// <param name="FCSOId"></param>
        /// <returns></returns>
        [HttpPost]
        public IHttpActionResult SetPayResultHandleByFCSOId(string FCSOId)
        {

            BackgroundJob.Enqueue(() => DoPayResultHandleByFCSOId(FCSOId));
            return Ok();
        }

        public void UnInvoiceHandle(bool isexcel)
        {
            try
            {
                using (IDbConnection conn = new SqlConnection(_sqlConnection))
                {
                    List<FinaChargeStuOrderEntity> stuorderlist = null;
                    if (isexcel)
                    {
                        stuorderlist = conn.Query<FinaChargeStuOrderEntity>(@"select StuNo,YearNo,FSYId,'ZFB' as PayMode from FinaChargeStuOrder where  Status=1 and OrderType=2 
                        group by StuNo, YearNo, FSYId, PayMode").ToList();
                    }
                    else
                    {
                        stuorderlist = conn.Query<FinaChargeStuOrderEntity>(@"select StuNo,YearNo,FSYId,'ZFB' as PayMode from FinaChargeStuOrder where  Status=1 and OrderType=1 
                        group by StuNo, YearNo, FSYId, PayMode").ToList();
                    }
                    foreach (var stuorderEntity in stuorderlist)
                    {
                        //开票
                        YKTTrabs.InvoiceEBillMethodTwo(stuorderEntity);
                    }
                }
            }
            catch (Exception e)
            {
                using (IDbConnection conncore = new SqlConnection(_coresqlConnection))
                {
                    conncore.Execute(
                        "insert into LR_Base_Log(F_LogId,F_CategoryId,F_SourceObjectId,F_SourceContentJson,F_OperateTime) values(newid(),121,'piaoju','" + e.Message + "',getdate())");
                }
            }
        }
        public void DoUnInvoiceHandleByFSYID(string FSYID)
        {
            try
            {
                using (IDbConnection conn = new SqlConnection(_sqlConnection))
                {
                    List<FinaChargeStuOrderEntity> stuorderlist = null;
                    stuorderlist = conn.Query<FinaChargeStuOrderEntity>(@"select StuNo,YearNo,FSYId,'ExcelOffLine' as PayMode from FinaChargeStuOrder where  Status=1 and fsyid='" + FSYID + @"'
                        group by StuNo, YearNo, FSYId, PayMode").ToList();
                    foreach (var stuorderEntity in stuorderlist)
                    {
                        //开票
                        YKTTrabs.InvoiceEBillMethodTwo(stuorderEntity);
                        break;
                    }
                }
            }
            catch (Exception e)
            {
                using (IDbConnection conncore = new SqlConnection(_coresqlConnection))
                {
                    conncore.Execute(
                        "insert into LR_Base_Log(F_LogId,F_CategoryId,F_SourceObjectId,F_SourceContentJson,F_OperateTime) values(newid(),121,'piaoju','" + e.Message + "',getdate())");
                }
            }
        }

        public void GetResult(bool iflasttenminutes)
        {
            try
            {
                using (IDbConnection conn = new SqlConnection(_sqlConnection))
                {
                    //获取最近1小时未支付的订单/1天订单
                    string lastdate = iflasttenminutes
                        ? DateTime.Now.AddMinutes(-30).ToString("yyyy-MM-dd HH:mm:ss")
                        : DateTime.Now.AddDays(-20).ToString("yyyy-MM-dd HH:mm:ss");
                    List<FinaChargeStuOrderEntity> list = conn.Query<FinaChargeStuOrderEntity>("select * from FinaChargeStuOrder where PlaceOrderTime>='" + lastdate + "' and Status=0 and OrderType=1 ").ToList();
                    foreach (var item in list)
                    {
                        //轮询建行商户平台
                        XmlDocument xml = new XmlDocument();
                        xml.Load(AppContext.BaseDirectory + "\\Content\\payxml\\PayResultXMLFile.xml");
                        ((XmlElement)xml.SelectSingleNode("TX/REQUEST_SN")).InnerText = DateTime.Now.ToString("yyyyMMddHHmmssfff");
                        ((XmlElement)xml.SelectSingleNode("TX/TX_INFO/ORDER")).InnerText = item.orderid;
                        ((XmlElement)xml.SelectSingleNode("TX/USER_ID")).InnerText = "002";
                        ((XmlElement)xml.SelectSingleNode("TX/PASSWORD")).InnerText = "xcyz123456";
                        string par = "requestXml=" + xml.InnerXml;
                        //最后将字符串参数进行Url编码 
                        Encoding encoding = Encoding.GetEncoding("gb2312");
                        byte[] payload = encoding.GetBytes(par);
                        HttpClient httpClient = new HttpClient();
                        HttpContent content = new ByteArrayContent(payload);
                        string reStr = string.Empty;
                        try
                        {
                            reStr = httpClient.PostAsync(payresulturl, content).Result.Content.ReadAsStringAsync().Result;
                        }
                        catch (Exception e)
                        {
                            using (IDbConnection conncore = new SqlConnection(_coresqlConnection))
                            {
                                conncore.Execute(
                                    "insert into LR_Base_Log(F_LogId,F_CategoryId,F_SourceObjectId,F_SourceContentJson,F_OperateTime) values(newid(),121,'ccb','FCSOId:" + item.Id + " 对接支付结果地址接口失败:" + reStr + ":" + e.Message + ":" + e.StackTrace + "',getdate())");
                            }
                        }
                        xml.LoadXml(reStr);
                        string s = ((XmlElement)xml.SelectSingleNode("TX/RETURN_CODE")).InnerText;
                        //string s = "000000";
                        if (s.Equals("000000"))
                        {
                            using (IDbConnection conncore = new SqlConnection(_coresqlConnection))
                            {
                                conncore.Execute(
                                    "insert into LR_Base_Log(F_LogId,F_CategoryId,F_SourceObjectId,F_SourceContentJson,F_OperateTime) values(newid(),121,'ccb','FCSOId:" + item.Id + " s = 000000: " + Learun.Util.Str.ReplaceHtml(reStr) + "',getdate())");
                            }
                            string Orderid = ((XmlElement)xml.SelectSingleNode("TX/TX_INFO/LIST/ORDER")).InnerText;
                            string PAYMENT_MONEY = ((XmlElement)xml.SelectSingleNode("TX/TX_INFO/LIST/PAYMENT_MONEY")).InnerText;
                            string ORDER_STATUS = ((XmlElement)xml.SelectSingleNode("TX/TX_INFO/LIST/ORDER_STATUS")).InnerText;
                            string PAY_MODE = ((XmlElement)xml.SelectSingleNode("TX/TX_INFO/LIST/PAY_MODE")).InnerText;
                            string OriOvrlsttnEV_Trck_No = ((XmlElement)xml.SelectSingleNode("TX/TX_INFO/LIST/OriOvrlsttnEV_Trck_No")).InnerText;
                            string TRAN_DATE = ((XmlElement)xml.SelectSingleNode("TX/TX_INFO/LIST/TRAN_DATE")).InnerText;
                            DateTime paytimedatetime = DateTime.Now;
                            DateTime.TryParse(TRAN_DATE, out paytimedatetime);
                            //remark1 为学号
                            string REM1 = ((XmlElement)xml.SelectSingleNode("TX/TX_INFO/LIST/REM1")).InnerText;
                            //remark2 为缴费年度
                            string REM2 = ((XmlElement)xml.SelectSingleNode("TX/TX_INFO/LIST/REM2")).InnerText;
                            //string PAYMENT_MONEY = item.SJAmount.ToString();
                            //string ORDER_STATUS = "1";
                            //string TRAN_DATE = DateTime.Now.ToString();
                            //string PAY_MODE = "ZFB";
                            //string OriOvrlsttnEV_Trck_No = "test001";
                            //string Orderid = item.orderid;
                            //string REM1 = item.StuNo;
                            //string REM2 = item.YearNo.ToString();
                            if (ORDER_STATUS == "1")
                            {
                                conn.Execute("update FinaChargeStuOrder set SJAmount='" + PAYMENT_MONEY + "',Status='" + ORDER_STATUS + "',PayTime='" + TRAN_DATE + "',PayMode='" + PAY_MODE + "',BankOrder='" + OriOvrlsttnEV_Trck_No + "' where orderid='" + Orderid + "'");
                                ////判断实缴金额是否缴清费用
                                //decimal sjcount = Convert.ToDecimal(conn.ExecuteScalar("select isnull(sum(SJAmount),0) from FinaChargeStuOrder where StuNo='" + REM1 + "' and Status=1 and YearNo='" + REM2 + "' "));
                                //decimal yjcount = Convert.ToDecimal(conn.ExecuteScalar("select isnull(YJAmount,0) from FinaChargeStuOrder where orderid='" + Orderid + "'"));
                                //if (sjcount >= yjcount)
                                //{
                                //    //更新缴费状态
                                //    conn.Execute("update FinaChargeStuYear set PayFeeStatus='1' where StuNo='" + REM1 + "' and FSYear='" + REM2 + "'");
                                //}
                                //else//更新部分缴费状态
                                //{
                                //    //更新缴费状态
                                //    conn.Execute("update FinaChargeStuYear set PayFeeStatus='4' where StuNo='" + REM1 + "' and FSYear='" + REM2 + "'");
                                //}
                                //记录缴费前余额、待缴金额
                                var oldFinaChargeStuYearItem = conn.Query<FinaChargeStuYearItemEntity>(@"select b.* from [dbo].[FinaChargeStuOrderDetail] a left join [dbo].[FinaChargeStuYearItem] b on a.ChargeItemCode=b.ChargeItemCode 
                                where a.[FCSOId] = '" + item.Id + "' and b.FSYId = '" + item.FSYId + "'");
                                //实缴、缴费余额更新
                                //FinaChargeStuYearItem表
                                conn.Execute(@"update [dbo].[FinaChargeStuYearItem] set SJAmount=b.SJAmount+a.SJAmount,NeedToPay=NeedToPay-a.SJAmount,PayFeeStatus=(case when(NeedToPay-a.SJAmount<=0) then 1 when(NeedToPay-a.SJAmount=Standard) then 0 else 4 end)
from [dbo].[FinaChargeStuOrderDetail] a left join [dbo].[FinaChargeStuYearItem] b on a.ChargeItemCode=b.ChargeItemCode 
where a.[FCSOId]='" + item.Id + "' and b.FSYId='" + item.FSYId + "'");
                                //FinaChargeStuYear表
                                conn.Execute(@" update FinaChargeStuYear set SJAmount=aa.SJAmount,NeedToPay=aa.NeedToPay,PayFeeStatus=(case when(aa.NeedToPay<=0) then 1 else 4 end)
from (select isnull(sum(a.[NeedToPay]),0) as [NeedToPay],isnull(sum(a.SJAmount),0) as SJAmount,a.FSYear,b.StuNo from FinaChargeStuYearItem a left join FinaChargeStuYear b on a.FSYId=b.FSYId 
group by a.FSYear,b.StuNo ) aa left join 
 FinaChargeStuYear b on aa.stuno=b.stuno and aa.FSYear=b.FSYear
 where aa.StuNo='" + item.StuNo + "' and aa.FSYear='" + item.YearNo + "'");
                                //FinaChargeStudent表
                                conn.Execute(@"update FinaChargeStudent set NeedToPay=a.NeedToPay
 from (select isnull(sum([NeedToPay]),0) as [NeedToPay],StuNo from [FinaChargeStuYear] group by StuNo) a left join 
 FinaChargeStudent b on a.stuno=b.stuno where a.stuno='" + item.StuNo + "'");
                                //FinaChargeStuBalance流水表
                                //记录缴费后余额、待缴金额
                                var newFinaChargeStuYearItem = conn.Query<FinaChargeStuYearItemEntity>(@"select b.* from [dbo].[FinaChargeStuOrderDetail] a left join [dbo].[FinaChargeStuYearItem] b on a.ChargeItemCode=b.ChargeItemCode 
                                where a.[FCSOId] = '" + item.Id + "' and b.FSYId = '" + item.FSYId + "'");
                                IEnumerable<FinaChargeStuOrderDetailEntity> detaillist = conn.Query<FinaChargeStuOrderDetailEntity>("select * from FinaChargeStuOrderDetail where FCSOId='" + item.Id + "'");
                                foreach (var finaChargeStuOrderDetailEntity in detaillist)
                                {
                                    if (finaChargeStuOrderDetailEntity.SJAmount > 0)
                                    {
                                        //计算学生缴费项目当前欠缴金额
                                        var olditem = oldFinaChargeStuYearItem.FirstOrDefault(m => m.ChargeItemCode == finaChargeStuOrderDetailEntity.ChargeItemCode);
                                        var newitem = newFinaChargeStuYearItem.FirstOrDefault(m => m.ChargeItemCode == finaChargeStuOrderDetailEntity.ChargeItemCode);
                                        conn.Execute(@"insert into FinaChargeStuBalance(Id, StuNo, FSYear, FCSOId, ChargeItemCode, ChargeItemName, Standard, DJAmount, PaymentAmount, QJAmount, OldBalance, ChangeAmount, NowBalance, ChangeDate, ChangeType) values(
                                                            newid(),'" + item.StuNo + "','" + item.YearNo + "','" + item.Id + "','" + finaChargeStuOrderDetailEntity.ChargeItemCode + "','" + finaChargeStuOrderDetailEntity.ChargeItemName + "','" + finaChargeStuOrderDetailEntity.YJAmount + "'," +
                                                     "'" + olditem.NeedToPay + "','" + finaChargeStuOrderDetailEntity.SJAmount + "','" + newitem.NeedToPay + "','" + olditem.FSBlance + "'," +
                                                     "'0','" + newitem.FSBlance + "',getdate(),'1')");
                                    }
                                }
                                int PayFeeStatus = Convert.ToInt32(conn.ExecuteScalar("select PayFeeStatus from FinaChargeStuYear where StuNo='" + item.StuNo + "' and FSYear='" + item.YearNo + "'"));
                                if (PayFeeStatus == 1)
                                {
                                    //开票
                                    Task.Run(() => YKTTrabs.InvoiceEBillMethodTwo(item));
                                }
                                using (IDbConnection conncore = new SqlConnection(_coresqlConnection))
                                {
                                    conncore.Execute(
                                        "insert into LR_Base_Log(F_LogId,F_CategoryId,F_SourceObjectId,F_SourceContentJson,F_OperateTime) values(newid(),121,'ccb','FCSOId:" + item.Id + " orderid:" + Orderid + " StuNo:" + REM1 + " 缴费状态更新成功',getdate())");
                                }
                            }
                        }
                        else
                        {
                            using (IDbConnection conncore = new SqlConnection(_coresqlConnection))
                            {
                                conncore.Execute(
                                    "insert into LR_Base_Log(F_LogId,F_CategoryId,F_SourceObjectId,F_SourceContentJson,F_OperateTime) values(newid(),121,'ccb','FCSOId:" + item.Id + " error:code=" + s + "xml=" + Learun.Util.Str.ReplaceHtml(reStr) + "',getdate())");
                            }
                        }
                    }
                }
            }
            catch (Exception e)
            {
                using (IDbConnection conncore = new SqlConnection(_coresqlConnection))
                {
                    conncore.Execute(
                        "insert into LR_Base_Log(F_LogId,F_CategoryId,F_SourceObjectId,F_SourceContentJson,F_OperateTime) values(newid(),121,'ccb','catch error:" + e.Message + ":" + e.StackTrace + "',getdate())");
                }
            }
        }

        public void DoPayResultHandleByFCSOId(string FCSOId)
        {
            try
            {
                using (IDbConnection conn = new SqlConnection(_sqlConnection))
                {
                    var item = conn.QuerySingleOrDefault<FinaChargeStuOrderEntity>("select * from FinaChargeStuOrder where Id='" + FCSOId + "' and Status=0 and OrderType=1 ");
                    if (item != null)
                    {
                        conn.Execute("update FinaChargeStuOrder set Status='1' where Id='" + item.Id + "'");

                        //记录缴费前余额、待缴金额
                        var oldFinaChargeStuYearItem = conn.Query<FinaChargeStuYearItemEntity>(@"select b.* from [dbo].[FinaChargeStuOrderDetail] a left join [dbo].[FinaChargeStuYearItem] b on a.ChargeItemCode=b.ChargeItemCode 
                                where a.[FCSOId] = '" + item.Id + "' and b.FSYId = '" + item.FSYId + "'");
                        //实缴、缴费余额更新
                        //FinaChargeStuYearItem表
                        conn.Execute(@"update [dbo].[FinaChargeStuYearItem] set SJAmount=b.SJAmount+a.SJAmount,NeedToPay=NeedToPay-a.SJAmount,PayFeeStatus=(case when(NeedToPay-a.SJAmount<=0) then 1 when(NeedToPay-a.SJAmount=Standard) then 0 else 4 end)
from [dbo].[FinaChargeStuOrderDetail] a left join [dbo].[FinaChargeStuYearItem] b on a.ChargeItemCode=b.ChargeItemCode 
where a.[FCSOId]='" + item.Id + "' and b.FSYId='" + item.FSYId + "'");
                        //FinaChargeStuYear表
                        conn.Execute(@" update FinaChargeStuYear set SJAmount=aa.SJAmount,NeedToPay=aa.NeedToPay,PayFeeStatus=(case when(aa.NeedToPay<=0) then 1 else 4 end)
from (select isnull(sum(a.[NeedToPay]),0) as [NeedToPay],isnull(sum(a.SJAmount),0) as SJAmount,a.FSYear,b.StuNo from FinaChargeStuYearItem a left join FinaChargeStuYear b on a.FSYId=b.FSYId 
group by a.FSYear,b.StuNo ) aa left join 
 FinaChargeStuYear b on aa.stuno=b.stuno and aa.FSYear=b.FSYear
 where aa.StuNo='" + item.StuNo + "' and aa.FSYear='" + item.YearNo + "'");
                        //FinaChargeStudent表
                        conn.Execute(@"update FinaChargeStudent set NeedToPay=a.NeedToPay
 from (select isnull(sum([NeedToPay]),0) as [NeedToPay],StuNo from [FinaChargeStuYear] group by StuNo) a left join 
 FinaChargeStudent b on a.stuno=b.stuno where a.stuno='" + item.StuNo + "'");
                        //FinaChargeStuBalance流水表
                        //记录缴费后余额、待缴金额
                        var newFinaChargeStuYearItem = conn.Query<FinaChargeStuYearItemEntity>(@"select b.* from [dbo].[FinaChargeStuOrderDetail] a left join [dbo].[FinaChargeStuYearItem] b on a.ChargeItemCode=b.ChargeItemCode 
                                where a.[FCSOId] = '" + item.Id + "' and b.FSYId = '" + item.FSYId + "'");
                        IEnumerable<FinaChargeStuOrderDetailEntity> detaillist = conn.Query<FinaChargeStuOrderDetailEntity>("select * from FinaChargeStuOrderDetail where FCSOId='" + item.Id + "'");
                        foreach (var finaChargeStuOrderDetailEntity in detaillist)
                        {
                            if (finaChargeStuOrderDetailEntity.SJAmount > 0)
                            {
                                //计算学生缴费项目当前欠缴金额
                                var olditem = oldFinaChargeStuYearItem.FirstOrDefault(m => m.ChargeItemCode == finaChargeStuOrderDetailEntity.ChargeItemCode);
                                var newitem = newFinaChargeStuYearItem.FirstOrDefault(m => m.ChargeItemCode == finaChargeStuOrderDetailEntity.ChargeItemCode);
                                conn.Execute(@"insert into FinaChargeStuBalance(Id, StuNo, FSYear, FCSOId, ChargeItemCode, ChargeItemName, Standard, DJAmount, PaymentAmount, QJAmount, OldBalance, ChangeAmount, NowBalance, ChangeDate, ChangeType) values(
                                                            newid(),'" + item.StuNo + "','" + item.YearNo + "','" + item.Id + "','" + finaChargeStuOrderDetailEntity.ChargeItemCode + "','" + finaChargeStuOrderDetailEntity.ChargeItemName + "','" + finaChargeStuOrderDetailEntity.YJAmount + "'," +
                                             "'" + olditem.NeedToPay + "','" + finaChargeStuOrderDetailEntity.SJAmount + "','" + newitem.NeedToPay + "','" + olditem.FSBlance + "'," +
                                             "'0','" + newitem.FSBlance + "',getdate(),'1')");
                            }
                        }
                        int PayFeeStatus = Convert.ToInt32(conn.ExecuteScalar("select PayFeeStatus from FinaChargeStuYear where StuNo='" + item.StuNo + "' and FSYear='" + item.YearNo + "'"));
                        if (PayFeeStatus == 1)
                        {
                            using (IDbConnection conncore = new SqlConnection(_coresqlConnection))
                            {
                                conncore.Execute(
                                    "insert into LR_Base_Log(F_LogId,F_CategoryId,F_SourceObjectId,F_SourceContentJson,F_OperateTime) values(newid(),121,'ccb','【手动处理缴费结果】FCSOId:" + FCSOId + " StuNo:" + item.StuNo + "可以触发开票接口',getdate())");
                            }
                            //开票
                            Task.Run(() => YKTTrabs.InvoiceEBillMethodTwo(item));
                        }
                        using (IDbConnection conncore = new SqlConnection(_coresqlConnection))
                        {
                            conncore.Execute(
                                "insert into LR_Base_Log(F_LogId,F_CategoryId,F_SourceObjectId,F_SourceContentJson,F_OperateTime) values(newid(),121,'ccb','【手动处理缴费结果】FCSOId:" + FCSOId + " StuNo:" + item.StuNo + " 缴费状态更新成功',getdate())");
                        }
                    }

                }
            }
            catch (Exception e)
            {
                using (IDbConnection conn = new SqlConnection(_sqlConnection))
                {
                    conn.Execute("update FinaChargeStuOrder set PayTime=null,PayMode=null,BankOrder=null where Id='" + FCSOId + "'");
                }
                using (IDbConnection conncore = new SqlConnection(_coresqlConnection))
                {
                    conncore.Execute(
                        "insert into LR_Base_Log(F_LogId,F_CategoryId,F_SourceObjectId,F_SourceContentJson,F_OperateTime) values(newid(),121,'ccb','【手动处理缴费结果】catch error:" + e.Message + ":" + e.StackTrace + "',getdate())");
                }
            }
        }

        public void GetOrderResult(string orderid)
        {
            try
            {
                using (IDbConnection conn = new SqlConnection(_sqlConnection))
                {
                    List<FinaChargeStuOrderEntity> list = conn.Query<FinaChargeStuOrderEntity>("select * from FinaChargeStuOrder where orderid='" + orderid + "' and Status=0 and OrderType=1 ").ToList();
                    foreach (var item in list)
                    {
                        //轮询建行商户平台
                        XmlDocument xml = new XmlDocument();
                        xml.Load(AppContext.BaseDirectory + "\\Content\\payxml\\PayResultXMLFile.xml");
                        ((XmlElement)xml.SelectSingleNode("TX/REQUEST_SN")).InnerText = DateTime.Now.ToString("yyyyMMddHHmmssfff");
                        ((XmlElement)xml.SelectSingleNode("TX/TX_INFO/ORDER")).InnerText = item.orderid;
                        ((XmlElement)xml.SelectSingleNode("TX/USER_ID")).InnerText = "002";
                        ((XmlElement)xml.SelectSingleNode("TX/PASSWORD")).InnerText = "xcyz123456";
                        string par = "requestXml=" + xml.InnerXml;
                        //最后将字符串参数进行Url编码 
                        Encoding encoding = Encoding.GetEncoding("gb2312");
                        byte[] payload = encoding.GetBytes(par);
                        HttpClient httpClient = new HttpClient();
                        HttpContent content = new ByteArrayContent(payload);
                        string reStr = httpClient.PostAsync(payresulturl, content).Result.Content.ReadAsStringAsync().Result;
                        xml.LoadXml(reStr);
                        string s = ((XmlElement)xml.SelectSingleNode("TX/RETURN_CODE")).InnerText;
                        //string s = "000000";
                        if (s.Equals("000000"))
                        {
                            using (IDbConnection conncore = new SqlConnection(_coresqlConnection))
                            {
                                conncore.Execute(
                                    "insert into LR_Base_Log(F_LogId,F_CategoryId,F_SourceObjectId,F_SourceContentJson,F_OperateTime) values(newid(),121,'ccb','" + Learun.Util.Str.ReplaceHtml(reStr) + "',getdate())");
                            }
                            string Orderid = ((XmlElement)xml.SelectSingleNode("TX/TX_INFO/LIST/ORDER")).InnerText;
                            string PAYMENT_MONEY = ((XmlElement)xml.SelectSingleNode("TX/TX_INFO/LIST/PAYMENT_MONEY")).InnerText;
                            string ORDER_STATUS = ((XmlElement)xml.SelectSingleNode("TX/TX_INFO/LIST/ORDER_STATUS")).InnerText;
                            string PAY_MODE = ((XmlElement)xml.SelectSingleNode("TX/TX_INFO/LIST/PAY_MODE")).InnerText;
                            string OriOvrlsttnEV_Trck_No = ((XmlElement)xml.SelectSingleNode("TX/TX_INFO/LIST/OriOvrlsttnEV_Trck_No")).InnerText;
                            string TRAN_DATE = ((XmlElement)xml.SelectSingleNode("TX/TX_INFO/LIST/TRAN_DATE")).InnerText;
                            DateTime paytimedatetime = DateTime.Now;
                            DateTime.TryParse(TRAN_DATE, out paytimedatetime);
                            //remark1 为学号
                            string REM1 = ((XmlElement)xml.SelectSingleNode("TX/TX_INFO/LIST/REM1")).InnerText;
                            //remark2 为缴费年度
                            string REM2 = ((XmlElement)xml.SelectSingleNode("TX/TX_INFO/LIST/REM2")).InnerText;
                            //string PAYMENT_MONEY =item.SJAmount.ToString();
                            //string ORDER_STATUS = "1";
                            //string TRAN_DATE = DateTime.Now.ToString();
                            //string PAY_MODE = "alipay";
                            //string OriOvrlsttnEV_Trck_No = "test001";
                            //string Orderid = item.orderid;
                            //string REM1 = item.StuNo;
                            //string REM2 = item.YearNo.ToString();
                            if (ORDER_STATUS == "1")
                            {
                                conn.Execute("update FinaChargeStuOrder set SJAmount='" + PAYMENT_MONEY + "',Status='" + ORDER_STATUS + "',PayTime='" + TRAN_DATE + "',PayMode='" + PAY_MODE + "',BankOrder='" + OriOvrlsttnEV_Trck_No + "' where orderid='" + Orderid + "'");
                                //判断实缴金额是否缴清费用
                                decimal sjcount = Convert.ToDecimal(conn.ExecuteScalar("select isnull(sum(SJAmount),0) from FinaChargeStuOrder where StuNo='" + REM1 + "' and Status=1 and YearNo='" + REM2 + "' and OrderType=1 "));
                                decimal yjcount = Convert.ToDecimal(conn.ExecuteScalar("select isnull(YJAmount,0) from FinaChargeStuOrder where orderid='" + Orderid + "'"));
                                if (sjcount == yjcount)
                                {
                                    //更新缴费状态
                                    conn.Execute("update FinaChargeStuYear set PayFeeStatus='1' where StuNo='" + REM1 + "' and FSYear='" + REM2 + "'");
                                }
                                else//更新部分缴费状态
                                {
                                    //更新缴费状态
                                    conn.Execute("update FinaChargeStuYear set PayFeeStatus='4' where StuNo='" + REM1 + "' and FSYear='" + REM2 + "'");
                                }
                                //实缴、缴费余额更新
                                //FinaChargeStuYearItem表
                                conn.Execute(@"update [dbo].[FinaChargeStuYearItem] set SJAmount=a.SJAmount,FSBlance=FSBlance+a.SJAmount,PayFeeStatus=(case when(FSBlance+a.SJAmount>=0) then 1 else 4 end)
from [dbo].[FinaChargeStuOrderDetail] a left join [dbo].[FinaChargeStuYearItem] b on a.ChargeItemCode=b.ChargeItemCode 
where a.[FCSOId]='" + item.orderid + "' and b.FSYId='" + item.FSYId + "'");
                                //FinaChargeStuYear表
                                conn.Execute(@" update FinaChargeStuYear set SJAmount=aa.SJAmount,FSBlance=aa.FSBlance,PayFeeStatus=(case when(aa.[FSBlance]>=0) then 1 else 4 end)
from (select isnull(sum(a.[FSBlance]),0) as [FSBlance],isnull(sum(a.SJAmount),0) as SJAmount,a.FSYear,b.StuNo from FinaChargeStuYearItem a left join FinaChargeStuYear b on a.FSYId=b.FSYId 
group by a.FSYear,b.StuNo ) aa left join 
 FinaChargeStuYear b on aa.stuno=b.stuno and aa.FSYear=b.FSYear
 where aa.StuNo='" + item.StuNo + "' and aa.FSYear='" + item.YearNo + "'");
                                //FinaChargeStudent表
                                conn.Execute(@"update FinaChargeStudent set FSBlance=a.[FSBlance]
 from (select isnull(sum([FSBlance]),0) as [FSBlance],StuNo from [FinaChargeStuYear] group by StuNo) a left join 
 FinaChargeStudent b on a.stuno=b.stuno where a.stuno='" + item.StuNo + "'");
                                //开票
                                Task.Run(() => YKTTrabs.InvoiceEBillMethodTwo(item));
                                using (IDbConnection conncore = new SqlConnection(_coresqlConnection))
                                {
                                    conncore.Execute(
                                        "insert into LR_Base_Log(F_LogId,F_CategoryId,F_SourceObjectId,F_SourceContentJson,F_OperateTime) values(newid(),121,'ccb','orderid:" + Orderid + " StuNo:" + REM1 + "',getdate())");
                                    conncore.Execute(
                                        "insert into LR_Base_Log(F_LogId,F_CategoryId,F_SourceObjectId,F_SourceContentJson,F_OperateTime) values(newid(),121,'ccb','缴费状态更新成功',getdate())");
                                }
                            }
                        }
                        else
                        {
                            using (IDbConnection conncore = new SqlConnection(_coresqlConnection))
                            {
                                conncore.Execute(
                                    "insert into LR_Base_Log(F_LogId,F_CategoryId,F_SourceObjectId,F_SourceContentJson,F_OperateTime) values(newid(),121,'ccb','error:code=" + s + "xml=" + Learun.Util.Str.ReplaceHtml(reStr) + "',getdate())");
                            }
                        }
                    }
                }
            }
            catch (Exception e)
            {
                using (IDbConnection conncore = new SqlConnection(_coresqlConnection))
                {
                    conncore.Execute(
                        "insert into LR_Base_Log(F_LogId,F_CategoryId,F_SourceObjectId,F_SourceContentJson,F_OperateTime) values(newid(),121,'ccb','" + e.Message + "',getdate())");
                }
            }
        }

        public IHttpActionResult TestOracle()
        {

            return Json(CYDormitoryReturnController.TestOracle());
        }
    }
}