MedicalInsurance/Business/Clearing/OutOfAreaPatientClearing.cs

using Newtonsoft.Json.Linq;
using PTMedicalInsurance.Common;
using PTMedicalInsurance.Entity.Base.Clearing;
using PTMedicalInsurance.Forms.Clearings;
using PTMedicalInsurance.Helper;
using PTMedicalInsurance.Variables;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace PTMedicalInsurance.Business.Clearing
{
    //异地清分
    class OutOfAreaPatientClearing
    {
        private InvokeHelper invoker = new InvokeHelper();
        private MIIrisServices mIS = new MIIrisServices();
        private string year;
        private string month;
        private DataTable datatable;
        private string transid;
        private int totalrow;
        private OutOfAreaClearing outOfAreaClearing;

        public OutOfAreaPatientClearing(OutOfAreaClearing _outOfAreaClearing)
        {
            outOfAreaClearing = _outOfAreaClearing;
        }


        #region 查询相关
        /// <summary>
        /// 查询医院医保库里的异地数据
        /// </summary>
        /// <param name="input"></param>
        /// <param name="outMsg"></param>
        /// <returns></returns>
        public int QueryHospitalData(JObject input, out string outMsg)
        {
            string hospitalDr = JsonHelper.getDestValue(input, "hospitalDr");
            DateTime dt = DateTime.Parse(JsonHelper.getDestValue(input, "month"));
            year = dt.ToString("yyyy");
            month = dt.ToString("MM");
            string begin = JsonHelper.getDestValue(input, "beginDate") + "0:00:00";
            string end = JsonHelper.getDestValue(input, "endDate") + "23:59:59"; ;
            string sqlStr = "Select  PatientName,CASE WHEN admType = 1 THEN '门诊' WHEN admType =2 THEN '住院' ELSE '未知' END AS admType,CertificateNO,MdtrtID,SettlementDateTime,SettlementID,Sumamt,FundPaySumamt,'' mdtrt_setl_time ";
            sqlStr = sqlStr + ",'' as mdtrt_id,'' as setl_sn,'' as fulamt_advpay_flag,'' mdtrtarea,'' medins_no,'' certno,'' medfee_sumamt,'' optins_pay_sumamt,'1' cnfm_flag ";
            sqlStr = sqlStr + $" FROM BS_MedInsuSettlement WHERE ValidFlag = 1 and  Hospital_Dr=" + hospitalDr;
            sqlStr = sqlStr + $" and OccurTime>='{begin}'";
            sqlStr = sqlStr + $" and OccurTime<='{end}'";
            sqlStr = sqlStr + " and (left(InsuranceAreaCode,2)<>'44')";
            sqlStr = sqlStr + " order by SettlementDateTime ";

            JObject joRtn = mIS.DynamicQuery(sqlStr, "查询医院医保库里的异地数据");
            return JsonHelper.parseIrisRtnResult(joRtn, out outMsg);
        }

        /// <summary>
        /// 查询中心的异地数据
        /// </summary>
        /// <param name="input"></param>
        /// <param name="outMsg"></param>
        /// <returns></returns>
        public int QueryCenterData(JObject input, out string outMsg)
        {
            JObject joRtn = invoker.invokeCenterService(TradeEnum.QueryOutOfAreaClearingDetail, new JObject(new JProperty("data", input)));
            if (JsonHelper.parseCenterReturnJson(joRtn, out outMsg) != 0)
            {
                return -1;
            }
            else
            {
                outMsg = joRtn.ToString();
                return 0;
            }
        }

        /// <summary>
        /// 循环查询中心数据，最后拼接成JARRY
        /// </summary>
        /// <param name="input"></param>
        /// <param name="ja"></param>
        /// <param name="outMsg"></param>
        /// <returns></returns>
        public int Combin3260Data(JObject input, out JArray ja, out string outMsg)
        {
            QueryOutOfAreaClearingDetailsInput queryInput = new QueryOutOfAreaClearingDetailsInput();
            DateTime dt = DateTime.Parse(input["month"].ToString());
            queryInput.trt_year = dt.ToString("yyyy");
            queryInput.trt_month = dt.ToString("MM");
            int startrow = 1;

            ja = null;
            //var combinedList = new List<QueryOutOfAreaClearingDetailsOutput>();
            while (true)
            {
                queryInput.startrow = startrow;
                if (QueryCenterData(queryInput.ToJson(), out outMsg) != 0)
                {
                    return -1;
                }
                else
                {
                    JObject joRtn = JObject.Parse(outMsg);
                    JArray jaData = JArray.FromObject(joRtn["output"]["data"]);
                    totalrow = int.Parse(joRtn["output"]["totalrow"].ToString());
                    if (ja == null)
                    {
                        ja = jaData;
                    }
                    else
                    {
                        foreach (var jo in jaData)
                        {
                            ja.Add(jo);
                        }
                    }

                    startrow = startrow + jaData.Count;
                    if (jaData.Count < 100)
                    {
                        outMsg = "遍历完成，已查询到返回条数小于100！";
                        return 0;
                    }
                }
            }


        }

        /// <summary>
        /// 跟已查询出的医院数据进行匹配，未匹配成功的标记红色，多匹配的标记黄色，并新增行数显示
        /// </summary>
        /// <param name="dgv"></param>
        /// <param name="ja"></param>
        /// <param name="outMsg"></param>
        /// <returns></returns>
        public int MatchCenterData(DataGridView dgv, JArray ja, out string outMsg)
        {
            outMsg = "";
            //JArray jaDgv = (JArray)dgv.DataSource;
            DataTable dt = (DataTable)dgv.DataSource;
            DataColumn seqnoColumn = new DataColumn("seqno", typeof(int));
            seqnoColumn.DefaultValue = 0;
            dt.Columns.Add(seqnoColumn);
            //先遍历中心数据后遍历医院数据
            foreach (var item in ja)
            {
                bool isExist = false;
                JObject joCenter = (JObject)item;
                foreach (DataRow dr in dt.Rows)
                {
                    if (dr["SettlementID"].ToString() == joCenter["setl_sn"].ToString())
                    {
                        dr["setl_sn"] = joCenter["setl_sn"].ToString();
                        dr["seqno"] = int.Parse(joCenter["seqno"].ToString());
                        dr["medfee_sumamt"] = joCenter["medfee_sumamt"].ToString();
                        dr["fulamt_advpay_flag"] = joCenter["fulamt_advpay_flag"].ToString();
                        dr["optins_pay_sumamt"] = joCenter["optins_pay_sumamt"].ToString();
                        dr["mdtrt_setl_time"] = joCenter["mdtrt_setl_time"].ToString();
                        dr["certno"] = joCenter["certno"].ToString();
                        dr["mdtrt_id"] = joCenter["mdtrt_id"].ToString();
                        dr["medins_no"] = joCenter["medins_no"].ToString();
                        dr["mdtrtarea"] = joCenter["mdtrtarea"].ToString();
                        isExist = true;
                    }
                }
                if (!isExist)
                {
                    // 创建一个新的行
                    DataRow newRow = dt.NewRow();

                    // 设置新行的值
                    newRow["CertificateNO"] = "HIS未查询到，可扩大月份查询";
                    newRow["MdtrtID"] = "";
                    newRow["SettlementDateTime"] = "";
                    newRow["SettlementID"] = joCenter["setl_sn"].ToString();
                    newRow["Sumamt"] = "";
                    newRow["FundPaySumamt"] = "";
                    newRow["setl_sn"] = joCenter["setl_sn"].ToString();
                    newRow["seqno"] = int.Parse(joCenter["seqno"].ToString());
                    newRow["medfee_sumamt"] = joCenter["medfee_sumamt"].ToString();
                    newRow["fulamt_advpay_flag"] = joCenter["fulamt_advpay_flag"].ToString();
                    newRow["optins_pay_sumamt"] = joCenter["optins_pay_sumamt"].ToString();
                    newRow["mdtrt_setl_time"] = joCenter["mdtrt_setl_time"].ToString();
                    newRow["certno"] = joCenter["certno"].ToString();
                    newRow["mdtrt_id"] = joCenter["mdtrt_id"].ToString();
                    newRow["medins_no"] = joCenter["medins_no"].ToString();
                    newRow["mdtrtarea"] = joCenter["mdtrtarea"].ToString();
                    dt.Rows.Add(newRow);
                }
            }


            // 根据选择，使用 LINQ 对 DataTable 重新筛选排序
            var sortedRows = from row in dt.AsEnumerable()
                             orderby row.Field<int>("seqno")
                             select row;

            if (this.outOfAreaClearing.uploadStatus =="未上传")
            {
                 sortedRows = from row in dt.AsEnumerable()
                                 where row.Field<string>("UploadFlag") == "未上传"
                                 orderby row.Field<int>("seqno")
                                 select row;
            }

            
            // 将排序后的行加载到新的 DataTable 中
            DataTable sortedTable = (sortedRows != null && sortedRows.Any()) ?(sortedRows.CopyToDataTable()):null;
            datatable = sortedTable;
            dgv.DataSource = sortedTable;
            ////先遍历医院数据,如果未匹配到中心数据则标红
            //foreach (DataRow dr in dt.Rows)
            //{

            //}

            

            return 0;
        }

        /// <summary>
        /// 匹配医保库的上传记录表
        /// </summary>
        /// <param name="input"></param>
        /// <param name="outMsg"></param>
        /// <returns></returns>
        public int MatchMIUploadRecord(JObject input, out string outMsg)
        {
            outMsg = "";
            return 0;
        }

        #endregion

        #region 上传确认
        /// <summary>
        /// 组织3261的入参
        /// </summary>
        /// <param name="outMsg"></param>
        /// <returns></returns>
        public OutOfAreaClearingConfirmDetail OrgnizeSingle3261Detail(DataRow dr)
        {
            OutOfAreaClearingConfirmDetail detail = new OutOfAreaClearingConfirmDetail();
            detail.certno = dr["CertificateNO"].ToString();
            detail.mdtrt_id = dr["MdtrtID"].ToString();
            detail.mdtrt_setl_time = dr["SettlementDateTime"].ToString();
            detail.setl_sn = dr["SettlementID"].ToString();
            detail.medfee_sumamt = decimal.Parse(dr["Sumamt"].ToString());
            detail.cnfm_flag = dr["cnfm_flag"].ToString();
            detail.optins_pay_sumamt =decimal.Parse(dr["FundPaySumamt"].ToString());
            return detail;
        }

        /// <summary>
        /// 将确认结果上传给中心
        /// </summary>
        /// <param name="input"></param>
        /// <param name="outMsg"></param>
        /// <returns></returns>
        public int UploadToCenter(JObject input, out string outMsg)
        {
            JObject joRtn = invoker.invokeCenterService(TradeEnum.ConfirmOutOfAreaClearing, input);
            if (JsonHelper.parseCenterReturnJson(joRtn, out outMsg) != 0)
            {
                return -1;
            }
            else
            {
                outMsg = joRtn.ToString();
                return 0;
            }
        }

        /// <summary>
        /// 上传异地清分
        /// </summary>
        /// <param name="dgv"></param>
        /// <param name="rows"></param>
        /// <param name="outMsg"></param>
        /// <returns></returns>
        //public int BatchUpload(DataGridView dgv, int rows, out string outMsg)
        //{
        //    outMsg = "";
        //    List<OutOfAreaClearingConfirmDetail> details = new List<OutOfAreaClearingConfirmDetail>(); 
        //    int rowTotalCount = datatable.Rows.Count;
        //    for (int i = 0; i < rowTotalCount; i++)
        //    {
        //        DataRow dr = datatable.Rows[i];
        //        JObject joTmp = new JObject();
        //        if (((i + 1) / rows == 0) || ((i + 1) == rowTotalCount))
        //        {
        //            details = new List<OutOfAreaClearingConfirmDetail>();

        //            //int totalrow = 0;
        //            //if ((i + 1) / rows == 0)
        //            //{
        //            //    totalrow = rows;
        //            //}
        //            //else
        //            //{
        //            //    totalrow = rowTotalCount / rows;
        //            //}

        //            OutOfAreaClearingConfirmInput input = new OutOfAreaClearingConfirmInput();
        //            OutOfAreaClearingConfirmData data = new OutOfAreaClearingConfirmData();
        //            data.trt_year = year;
        //            data.trt_month = month;
        //            data.totalrow = totalrow;
        //            input.detail = details;
        //            input.data = data;
        //            JObject joIn = input.ToJson();
        //            if (UploadToCenter(joIn, out outMsg) != 0)
        //            {

        //            }
        //            else
        //            {
        //                UpdateUploadRecord(JArray.Parse(JsonHelper.getDestValue(joIn, "detail")), out outMsg);
        //            }
        //        }
        //        else
        //        {
        //            details.Add(OrgnizeSingle3261Detail(dr));
        //        }
        //    }
        //    return 0;
        //}

        public int BatchUpload(DataGridView dgv, int rows, out string outMsg)
        {
            int iFalse = 0;outMsg = "";
            foreach (DataRow dr in datatable.Rows)
            {
                string err;
                if (dr["MdtrtID"].ToString() == "")
                {
                    continue;
                }
                if (SingleUpload(dr, out err) != 0)
                {
                    iFalse++;
                    outMsg = outMsg + "\n" + err;
                }
            }
            return -iFalse;
        }
        #endregion

        #region 更新后台
        public int UpdateUploadRecord(JArray ja, out string outMsg)
        {
            outMsg = "";
            int iFalse = 0;
            foreach (JObject jo in ja)
            {
                string err = "";
                if (UpdateSingleUploadRecord(jo, out err) != 0)
                {
                    iFalse++;
                    outMsg = outMsg  + "\n;" + err;
                }
            }

            return iFalse;
        }

        public int UpdateSingleUploadRecord(JObject jo, out string outMsg)
        {
            JObject joUpdateIn = new JObject();
            joUpdateIn.Add("businessCode", "DongGuanOutOfAreaClearing");
            joUpdateIn.Add("businessID", jo["setl_sn"].ToString());//单条传，如果批量传，这么处理就有问题
            //joUpdateIn.Add("memo", jo["cnfm_flag"].ToString());//单条传，如果批量传，这么处理就有问题
            joUpdateIn.Add("memo", transid);//单条传，如果批量传，这么处理就有问题
            joUpdateIn.Add("input", jo);
            dynamic joOut = new
            {
                errorCode = "0",
                cnfm_flag = jo["cnfm_flag"].ToString()
            };
            joUpdateIn.Add("output", JObject.FromObject(joOut));
            //JoIn包含 起止时间，医院ID，接口ID，患者姓名，患者数电号
            JObject joInTmp = JsonHelper.setIrisInpar("02020007", joUpdateIn);
            //SELECT *FROM SQLUser.HB_User WHERE Hospital_Dr = 39
            joInTmp["session"][0]["userID"] = "278";
            joInTmp["session"][0]["hospID"] = Global.inf.hospitalDr;
            JObject joRtn = invoker.invokeInsuService(joInTmp.ToString(), "更新通用记录表");

            return JsonHelper.parseIrisRtnValue(joRtn, out outMsg);
        }

        #endregion

        #region 取消确认
        public int RollBack(JObject input, out string outMsg)
        {
            JObject joRtn = invoker.invokeCenterService(TradeEnum.RollBackOutOfAreaClearing, new JObject(new JProperty("data",input)));
            if (JsonHelper.parseCenterReturnJson(joRtn, out outMsg) != 0)
            {
                return -1;
            }
            else
            {
                outMsg = joRtn.ToString();
                return 0;
            }
        }
        #endregion

        #region 业务封装
        public int Query(JObject input, DataGridView dgv, out string outMsg)
        {
            if (QueryHospitalData(input, out outMsg) != 0)
            {
                return -1;
            }
            else
            {
                JArray jaData = JArray.Parse(JsonHelper.getDestValue(JObject.Parse(outMsg),"data"));
                JObject joRtn = invoker.invokeInsuService(JsonHelper.setIrisInpar("09010129", jaData).ToString(), "根据传入的东莞异地清分记录匹配上传记录表");
                JArray jaMatchedData = JArray.FromObject(joRtn["data"]);
                dgv.DataSource = (DataTable)jaMatchedData.ToObject(typeof(DataTable));
            }
            //JArray jaCenter = new JArray();
            if (Combin3260Data(input, out JArray jaCenter , out outMsg) != 0 ) return -1;
            return MatchCenterData(dgv, jaCenter, out outMsg);
        }

        public int SingleUpload(DataRow dr, out string outMsg)
        {
            List<OutOfAreaClearingConfirmDetail> details = new List<OutOfAreaClearingConfirmDetail>();
            OutOfAreaClearingConfirmInput input = new OutOfAreaClearingConfirmInput();
            OutOfAreaClearingConfirmData data = new OutOfAreaClearingConfirmData();
            data.trt_year = year;
            data.trt_month = month;
            data.totalrow = totalrow;
            details.Add(OrgnizeSingle3261Detail(dr));
            input.detail = details;
            input.data = data;
            if (UploadToCenter(input.ToJson(), out outMsg )!= 0)
            {
                return -1;
            }
            else
            {
                transid = Global.curEvt.msgid;
                return UpdateSingleUploadRecord(JObject.Parse(JsonHelper.getDestValue(input.ToJson(),"detail[0]")),out outMsg);
            }

        }

        public int   Upload(DataGridView dgv, out string outMsg)
        {
            return BatchUpload(dgv, 100, out outMsg);
        }

        public int Upload(object[] o, out string errMsg, DataLoader.ProgressCallback callback, DataLoader.ProgressMaxCallback maxCallback, BackgroundWorker worker)
        {
            int iFalse = 0; errMsg = "";
            int i = 0;
            maxCallback(datatable.Rows.Count);
            foreach (DataRow dr in datatable.Rows)
            {
                i = i + 1;
                // 检查是否应取消操作
                if (worker.CancellationPending)
                {
                    errMsg = "操作取消！";
                    return -1; // 或者其他表示取消的返回值
                }
                callback(i);
                string err;
                if (dr["MdtrtID"].ToString() == "")
                {
                    continue;
                }
                if (SingleUpload(dr, out err) != 0)
                {
                    iFalse++;
                    errMsg = errMsg + "\n" + err;
                }
            }

            if (iFalse == 0) errMsg = "全部上传成功";
            return iFalse;
        }

        public int RollBackSingle(DataGridView dgv, out string outMsg)
        {
            OutOfAreaClearingRollBackInput input = new OutOfAreaClearingRollBackInput();
            RollBack(input.ToJson(), out outMsg);
            return UpdateSingleUploadRecord(input.ToJson(), out outMsg);
        }

        

        public int RollBackAll(out string outMsg)
        {
            OutOfAreaClearingRollBackInput input = new OutOfAreaClearingRollBackInput();
            input.trt_month = month;
            input.trt_year = year;
            input.otransid = "0";
            return RollBack(input.ToJson(), out outMsg);
            //return UpdateSingleUploadRecord(input.ToJson(), out outMsg);
        }

        #endregion

        #region 导出
        public int ExportDataToExcel(object[] o, out string errMsg, DataLoader.ProgressCallback callback, DataLoader.ProgressMaxCallback maxCallback, BackgroundWorker worker)
        {
            Dataloader_ExportContext exportContext = (Dataloader_ExportContext)o[0];

            if ((exportContext.dtExport == null) || (exportContext.dtExport.Rows.Count == 0))
            {
                errMsg = "查询数据集为空！";
                return -1;
            }

            string LSH = DateTime.Now.ToString("yyyy-MM-dd"); //DateTime.Now.ToString("MMddHHmmssffff");
            string filename = exportContext.fileName;
            string filepath = Global.curEvt.path + "\\Export\\异地清分";
            ; string sFliePathName = $@"{filepath}\{filename}";

            //如果不存在目录，则创建目录
            if (!Directory.Exists(filepath))
            {
                //创建文件夹
                DirectoryInfo dirInfo = Directory.CreateDirectory(filepath);
            }

            //如存在同名文件不询问直接删除
            if (File.Exists(sFliePathName))
            {
                File.Delete(sFliePathName);
            }


            //新版方法，用的EPPLUS，方便
            ExportToExcel.ExportDataGridViewToExcel(exportContext.dgv, sFliePathName);
            errMsg = ($"导出成功,文件保存路径：{sFliePathName}");
            return 0;
        }
        #endregion
    }

    class Dataloader_ExportContext
    {
        public DataTable dtExport;
        public DataGridView dgv;
        public string filePath;
        public string fileName;
    }
}