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.EducationalAdministration;

namespace DigitalSchoolApi.Controllers
{
    public class PayFeeResultController : BaseController
    {

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

        /// <summary>
        /// 每分钟执行缴费后处理
        /// </summary>
        /// <returns></returns>
        public IHttpActionResult GetLastTenMinutes()
        {
            RecurringJob.AddOrUpdate("PayFeeResultMinutes",
                () => GetResult(true),
                Cron.Minutely, TimeZoneInfo.Local);
            return Ok();
        }
        /// <summary>
        /// 每天执行缴费后处理
        /// </summary>
        /// <returns></returns>
        public IHttpActionResult GetLastDay()
        {
            RecurringJob.AddOrUpdate("PayFeeResultDay",
                () => GetResult(false),
                Cron.Daily(0), TimeZoneInfo.Local);
            return Ok();
        }
        /// <summary>
        /// excel开发票任务
        /// </summary>
        /// <returns></returns>
        public IHttpActionResult SetUnInvoiceHandle()
        {
            BackgroundJob.Enqueue(() => UnInvoiceHandle(true));
            return Ok();
        }

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

        
        public void  UnInvoiceHandle(bool isexcel)
        {
            try
            {
                using (IDbConnection conn = new SqlConnection(_sqlConnection))
                {
                    List<StuEnrollFeeOrderEntity> stuorderlist = null;
                    if (isexcel)
                    {
                        stuorderlist = conn.Query<StuEnrollFeeOrderEntity>("select stuno,yearno,orderid from StuEnrollFeeOrder where  Status=999").ToList();
                    }
                    else
                    {
                        stuorderlist = conn.Query<StuEnrollFeeOrderEntity>("select stuno,yearno,orderid from StuEnrollFeeOrder where  Status=1").ToList();
                    }
                    foreach (var stuorderEntity in stuorderlist)
                    {
                        //新老生判断
                        if (Convert.ToInt32(conn.ExecuteScalar(
                                "select count(1) from StuInfoFresh where StuNo='" + stuorderEntity.StuNo + "' and grade='" + stuorderEntity.YearNo.ToString().Substring(2) + "'")) > 0)
                        {
                            conn.Execute("update StuInfoFresh set PayFeeStatus='1' where StuNo='" + stuorderEntity.StuNo + "'");
                            if (Convert.ToInt32(conn.ExecuteScalar(
                                    "select count(1) from StuInfoFresh where StuNo='" + stuorderEntity.StuNo +"' and grade='" + stuorderEntity.YearNo.ToString().Substring(2) + "' and IsInvoice=0 and PayFeeStatus='1' ")) > 0)
                            {
                                //开票
                                Task.Run(() => YKTTrabs.InvoiceEBillMethod(stuorderEntity.orderid, true));
                            }
                        }
                        else
                        {
                            conn.Execute("update StuInfoBasic_PayFee set PayStatus='1' where StuNo='" + stuorderEntity.StuNo + "' and PayYear='" + stuorderEntity.YearNo.ToString().Substring(2) + "'");
                            if (Convert.ToInt32(conn.ExecuteScalar("select count(1) from StuInfoBasic_PayFee where StuNo='" +stuorderEntity.StuNo + "' and PayYear='" +stuorderEntity.YearNo + "' and InvoiceStatus=0 and PayStatus='1'")) > 0)
                            {
                                //开票
                                Task.Run(() => YKTTrabs.InvoiceEBillMethod(stuorderEntity.orderid, false));
                            }
                        }
                    }
                }
            }
            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<StuEnrollFeeOrderEntity> list = conn.Query<StuEnrollFeeOrderEntity>("select * from StuEnrollFeeOrder where PlaceOrderTime>='" + lastdate + "' and Status<>1 and Status<>999").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();
                            conn.Execute("update StuEnrollFeeOrder 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 StuEnrollFeeOrder where StuNo='" + REM1 + "' and Status=1 and YearNo='" + REM2 + "'"));
                            decimal yjcount = Convert.ToDecimal(conn.ExecuteScalar("select isnull(YJAmount,0) from StuEnrollFeeOrder where orderid='" + Orderid + "'"));
                            if (sjcount == yjcount)
                            {
                                //新老生判断
                                if (Convert.ToInt32(conn.ExecuteScalar(
                                        "select count(1) from StuInfoFresh where StuNo='" + REM1 + "' and grade='" + REM2.Substring(2) + "'")) > 0)
                                {
                                    conn.Execute("update StuInfoFresh set PayFeeStatus='1' where StuNo='" + REM1 + "'");
                                    //开票
                                    Task.Run(() => YKTTrabs.InvoiceEBillMethod(Orderid, true));
                                }
                                else
                                {
                                    conn.Execute("update StuInfoBasic_PayFee set PayStatus='1' where StuNo='" + REM1 + "' and PayYear='" + REM2 + "'");
                                    //开票
                                    Task.Run(() => YKTTrabs.InvoiceEBillMethod(Orderid, false));
                                }
                            }
                            else//更新部分缴费状态
                            {
                                //新老生判断
                                if (Convert.ToInt32(conn.ExecuteScalar(
                                        "select count(1) from StuInfoFresh where StuNo='" + REM1 + "' and grade='" + REM2.Substring(2) + "'")) > 0)
                                {
                                    conn.Execute("update StuInfoFresh set PayFeeStatus='4' where StuNo='" + REM1 + "'");
                                    //开票
                                    Task.Run(() => YKTTrabs.InvoiceEBillMethod(Orderid, true));
                                }
                                else
                                {
                                    conn.Execute("update StuInfoBasic_PayFee set PayStatus='4' where StuNo='" + REM1 + "' and PayYear='" + REM2 + "'");
                                    //开票
                                    Task.Run(() => YKTTrabs.InvoiceEBillMethod(Orderid, false));
                                }
                            }
                            //用友系统回写
                            using (IDbConnection yongyouconn = new SqlConnection(__yongyouConnection))
                            {
                                try
                                {
                                    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,'yongyou','准备开始用友回写',getdate())");
                                    }
                                    dynamic stuinfo = yongyouconn.QuerySingle("select * from SCS_XSXX where XH='" + REM1 + "'");
                                    string XSID = stuinfo.XSID.ToString();
                                    string XM = stuinfo.XM.ToString();
                                    string SFZ = stuinfo.SFZ.ToString();
                                    string strSql = "insert into SCS_ORDER(GSDM,KJND,OR_ID,OR_NO,XSID,ZDRDM,ZDRMC,ZDRQ,ZDSJ,ZFZT,CHANNELTYPE,AMOUNT,PAYTYPE,IDCARD,realamount,moneytype,BZ,pstring) " +
                                                    " values('001','" + REM2 + "','" + Orderid + "-" + REM2 + "','" + Orderid + "','" + XSID + "','" + REM1 + "','" + XM + "','" + paytimedatetime.ToString("yyyyMMdd") + "','" + paytimedatetime.ToString("HHmmss") + "','20','4','" + PAYMENT_MONEY + "','ccb','" + SFZ + "','" + PAYMENT_MONEY + "','1','','支付成功')";
                                    yongyouconn.Execute(strSql);
                                    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,'yongyou','用友回写SCS_ORDER完成',getdate())");
                                    }
                                    //用友明细
                                    List<StuEnrollFeeOrderDetailEntity> orderdetaillist = conn.Query<StuEnrollFeeOrderDetailEntity>("select * from StuEnrollFeeOrderDetail where OrderId='" + item.Id + "'").ToList();
                                    for (int i = 0; i < orderdetaillist.Count; i++)
                                    {
                                        string sqldetail = "insert into SCS_ORDEL(GSDM,KJND,OR_ID,XH,SFND,SFXMDM,SFXMMC,JE,SFQJNM) " +
                                                      "values('001','" + REM2 + "','" + Orderid + "-" + REM2 + "','" + (i + 1) + "','" + item.YearNo + "','" + orderdetaillist[i].ChargeItemID + "','" + orderdetaillist[i].ChargeItemName + "','" + orderdetaillist[i].SJAmount + "','" + item.YearNo + "')";
                                        yongyouconn.Execute(sqldetail);
                                    }
                                    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,'yongyou','用友回写SCS_ORDEL完成',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(),122,'yongyou','" + e.Message + "',getdate())");
                                    }
                                }

                            }
                            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 void GetOrderResult(string orderid)
        {
            try
            {
                using (IDbConnection conn = new SqlConnection(_sqlConnection))
                {
                    List<StuEnrollFeeOrderEntity> list = conn.Query<StuEnrollFeeOrderEntity>("select * from StuEnrollFeeOrder where orderid='" + orderid + "' and Status<>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;
                            //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();
                            conn.Execute("update StuEnrollFeeOrder 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 StuEnrollFeeOrder where StuNo='" + REM1 + "' and Status=1 and YearNo='" + REM2 + "'"));
                            decimal yjcount = Convert.ToDecimal(conn.ExecuteScalar("select isnull(YJAmount,0) from StuEnrollFeeOrder where orderid='" + Orderid + "'"));
                            if (sjcount == yjcount)
                            {
                                //新老生判断
                                if (Convert.ToInt32(conn.ExecuteScalar(
                                        "select count(1) from StuInfoFresh where StuNo='" + REM1 + "' and grade='" + REM2.Substring(2) + "'")) > 0)
                                {
                                    conn.Execute("update StuInfoFresh set PayFeeStatus='1' where StuNo='" + REM1 + "'");
                                    //开票
                                    Task.Run(() => YKTTrabs.InvoiceEBillMethod(Orderid, true));
                                }
                                else
                                {
                                    conn.Execute("update StuInfoBasic_PayFee set PayStatus='1' where StuNo='" + REM1 + "' and PayYear='" + REM2 + "'");
                                    //开票
                                    Task.Run(() => YKTTrabs.InvoiceEBillMethod(Orderid, false));
                                }
                            }
                            else//更新部分缴费状态
                            {
                                //新老生判断
                                if (Convert.ToInt32(conn.ExecuteScalar(
                                        "select count(1) from StuInfoFresh where StuNo='" + REM1 + "' and grade='" + REM2.Substring(2) + "'")) > 0)
                                {
                                    conn.Execute("update StuInfoFresh set PayFeeStatus='4' where StuNo='" + REM1 + "'");
                                }
                                else
                                {
                                    conn.Execute("update StuInfoBasic_PayFee set PayStatus='4' where StuNo='" + REM1 + "' and PayYear='" + REM2 + "'");
                                }
                            }
                            //用友系统回写
                            using (IDbConnection yongyouconn = new SqlConnection(__yongyouConnection))
                            {
                                try
                                {
                                    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,'yongyou','准备开始用友回写',getdate())");
                                    }
                                    dynamic stuinfo = yongyouconn.QuerySingle("select * from SCS_XSXX where XH='" + REM1 + "'");
                                    string XSID = stuinfo.XSID.ToString();
                                    string XM = stuinfo.XM.ToString();
                                    string SFZ = stuinfo.SFZ.ToString();
                                    string strSql = "insert into SCS_ORDER(GSDM,KJND,OR_ID,OR_NO,XSID,ZDRDM,ZDRMC,ZDRQ,ZDSJ,ZFZT,CHANNELTYPE,AMOUNT,PAYTYPE,IDCARD,realamount,moneytype,BZ,pstring) " +
                                                    " values('001','" + REM2 + "','" + Orderid + "-" + REM2 + "','" + Orderid + "','" + XSID + "','" + REM1 + "','" + XM + "','" + DateTime.Now.ToString("yyyyMMdd") + "','" + DateTime.Now.ToString("hhmmss") + "','20','4','" + PAYMENT_MONEY + "','ccb','" + SFZ + "','" + PAYMENT_MONEY + "','1','','支付成功')";
                                    yongyouconn.Execute(strSql);
                                    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,'yongyou','用友回写SCS_ORDER完成',getdate())");
                                    }
                                    //用友明细
                                    List<StuEnrollFeeOrderDetailEntity> orderdetaillist = conn.Query<StuEnrollFeeOrderDetailEntity>("select * from StuEnrollFeeOrderDetail where OrderId='" + item.Id + "'").ToList();
                                    for (int i = 0; i < orderdetaillist.Count; i++)
                                    {
                                        string sqldetail = "insert into SCS_ORDEL(GSDM,KJND,OR_ID,XH,SFND,SFXMDM,SFXMMC,JE,SFQJNM) " +
                                                      "values('001','" + REM2 + "','" + Orderid + "-" + REM2 + "','" + (i + 1) + "','" + item.YearNo + "','" + orderdetaillist[i].ChargeItemID + "','" + orderdetaillist[i].ChargeItemName + "','" + orderdetaillist[i].SJAmount + "','" + item.YearNo + "')";
                                        yongyouconn.Execute(sqldetail);
                                    }
                                    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,'yongyou','用友回写SCS_ORDEL完成',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(),122,'yongyou','" + e.Message + "',getdate())");
                                    }
                                }

                            }
                            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())");
                            }
                        }
                    }
                }
            }
            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());
        }
    }
}