MedicalInsurance/ExcelImport.cs

using PTMedicalInsurance.Common;
using PTMedicalInsurance.Helper;
using Newtonsoft.Json.Linq;
using PTMedicalInsurance.Variables;
using System;
using System.Collections.Generic;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Threading;
using System.Windows.Forms;
using PTMedicalInsurance.Entity.Base;
using Ganss.Excel;
using PTMedicalInsurance.Entity;

namespace PTMedicalInsurance.Forms
{
    public partial class ExcelImport : Form
    {

        private string mapSection;
        private List<string> mapLst = new List<string>();

        private int buttonHeight = 30; // 按钮高度
        private int rowSpacing = 20;  // 行间距

        private string excelFile = "";

        private Dictionary<string, KeyValuePair<string, string>> mappingPairs = new Dictionary<string, KeyValuePair<string,string>>();

        public delegate void ImportExcelHandler(string text);

        public event ImportExcelHandler onImport;


        public ExcelImport()
        {
            InitializeComponent();
            rdDrugs.CheckedChanged += checkChange;
            rdService.CheckedChanged += checkChange;
            rdMaterial.CheckedChanged += checkChange;
        }

        private void SourceLabel_MouseDown(object sender, MouseEventArgs e)
        {
            Button sourceLabel = (Button)sender;

            DoDragDrop(sourceLabel, DragDropEffects.Copy);
        }

        /// <summary>
        /// 画连接线
        /// </summary>
        /// <param name="sourceControl"></param>
        /// <param name="targetControl"></param>
        private void DrawLine(Control sourceControl, Control targetControl)
        {
            Graphics g = CreateGraphics();

            // 计算起始点和终点坐标（中心点）
            Point startPoint = new Point(sourceControl.Left + sourceControl.Width / 2,
                sourceControl.Top + sourceControl.Height / 2);
            Point endPoint = new Point(targetControl.Left + targetControl.Width / 2,
                targetControl.Top + targetControl.Height / 2);

            // 绘制连线（直线）
            Pen pen = new Pen(Color.Red);  // 自定义颜色或样式
            g.DrawLine(pen, startPoint.X, startPoint.Y, endPoint.X, endPoint.Y);

            pen.Dispose();
            g.Dispose();
        }

        private void btnLoad_Click(object sender, EventArgs e)
        {
            if(openFileDlg.ShowDialog() == DialogResult.OK)
            {
                excelFile = openFileDlg.FileName;
                initMapper();
            }
            
        }
        private void readIni()
        {
            IniFile ini = new IniFile(Global.curEvt.path + @"\InsuMapRelation.ini");

            
            string excelImportSection = "";
            if (rdDrugs.Checked)
            {
                mapSection = "Drugs";
                excelImportSection = "ExcelImport_Drugs";
            }
            if (rdService.Checked)
            {
                mapSection = "Services";
                excelImportSection = "ExcelImport_Services";
            }
            if (rdMaterial.Checked)
            {
                mapSection = "Materials";
                excelImportSection = "ExcelImport_Materials";
            }

            mapLst = new List<string>();
            mapLst = ini.ReadKeys(mapSection);

            this.maxPreviewRows.Value = !string.IsNullOrEmpty(ini.ReadValue(excelImportSection, "MaxPreviewRows"))  ? int.Parse(ini.ReadValue(excelImportSection, "MaxPreviewRows")) : 20;
            this.maxUploadRows.Value = !string.IsNullOrEmpty(ini.ReadValue(excelImportSection, "MaxRowNumber")) ? int.Parse(ini.ReadValue(excelImportSection, "MaxRowNumber")) : 5000;
            this.headerLineNum.Value = !string.IsNullOrEmpty(ini.ReadValue(excelImportSection, "HeaderLineNum")) ? int.Parse(ini.ReadValue(excelImportSection, "HeaderLineNum")) : 1;
            this.headerStartNO.Value = !string.IsNullOrEmpty(ini.ReadValue(excelImportSection, "HeaderStartNO")) ? int.Parse(ini.ReadValue(excelImportSection, "HeaderStartNO")) : 2;
            this.codeMinLength.Value = !string.IsNullOrEmpty(ini.ReadValue(excelImportSection, "CodeMinLength")) ? int.Parse(ini.ReadValue(excelImportSection, "CodeMinLength")) : 6;
            this.txtCombinMark.Text = !string.IsNullOrEmpty(ini.ReadValue(excelImportSection, "CombinMark")) ?  ini.ReadValue(excelImportSection, "CombinMark") : "-";
            this.tbSheetName.Text = !string.IsNullOrEmpty(ini.ReadValue(excelImportSection, "SheetName")) ? ini.ReadValue(excelImportSection, "SheetName") : "总表";

        }

        private void initMapper()
        {
            mappingPairs.Clear();
            bool limited = true;
            if (maxPreviewRows.Value < 0) limited = false;
            ExcelMapper mapper = createMapper(limited);
            this.loadExcel(mapper,true);
            if (mapper == null || mapper.headers == null)
            {
                MessageBox.Show("mapper为空");
                return;
            }    
            string[] igoreColumns = new string[]{ "HospitalDr","InterfaceDr","HisType","HisTypeName" };
            panelTarget.Controls.Clear();
            panelSource.Controls.Clear();
            mapper.headers.Where((x)=>!string.IsNullOrEmpty(x)).ToList<string>().ForEach((cellName) => {
                Button btn = AddButton(panelSource, cellName);
                btn.MouseDown += SourceLabel_MouseDown;
            });

            foreach (DataGridViewTextBoxColumn c in this.dataGridView1.Columns)
            {
                if (igoreColumns.Contains(c.DataPropertyName)) continue;
                Button btn = AddButton(panelTarget, c.HeaderText);
                btn.AllowDrop = true;
                btn.Tag = c.DataPropertyName;
                btn.DragDrop += panelTarget_DragDrop;
                btn.DragEnter += panelTarget_DragEnter;
            }
        }

        private ExcelMapper createMapper(bool limitedMaxRow)
        {
            try
            {
                ExcelMapper mapper = new ExcelMapper(excelFile);
                // 设置连接符
                mapper.CombinMark = txtCombinMark.Text.Trim();
                // 仅作为预览的数量
                if (limitedMaxRow)
                {
                    mapper.MaxRowNumber = Decimal.ToInt32(maxPreviewRows.Value);
                }
                if (headerLineNum.Value < 1)
                {
                    // 无标题
                    mapper.HeaderRow = false;
                }
                else
                {
                    mapper.HeaderRowNumber = int.Parse(headerLineNum.Value.ToString()) -1 ;
                    mapper.MinRowNumber = int.Parse(headerStartNO.Value.ToString()) -1;
                }
                this.AddMapping(mapper);
                
                //new Thread(new ParameterizedThreadStart(loadExcel)).Start(mapper);
                return mapper;
            }catch(Exception e)
            {
                MessageBox.Show("createMapper:"+e.Message);
            }
            return null;
        }
        private int getHisType(out string typeName)
        {
            int type = 1;
            typeName = rdDrugs.Text;
            if (rdService.Checked)
            {
                type = 2;
                typeName = rdService.Text;
            }
            if (rdMaterial.Checked)
            {
                type = 3;
                typeName = rdMaterial.Text;
            }
            return type;
        }
        private List<MedInsuDirectory> loadExcel(object mapper,bool show)
        {
            List<MedInsuDirectory> list = new List<MedInsuDirectory>();
            try
            {
                string typeName;
                int type = getHisType(out typeName);
                if (mapper == null) return list;
                
                list = (mapper as ExcelMapper).Fetch<MedInsuDirectory>(tbSheetName.Text).ToList();
                //MessageBox.Show(list.Count.ToString());
                //if (mappingPairs.Count != 0)
                list.ForEach((mi) =>
                {
                    mi.HospitalDr = (Global.inf.hospitalDr + "");
                    mi.InterfaceDr = Global.inf.interfaceDr + "";
                    mi.HisType = type.ToString();
                    mi.HisTypeName = typeName;
                });
                // 过滤符合和code特征的数据
                int codeMinLen = int.Parse(codeMinLength.Value.ToString());
                MedInsuDirectory[] dataArray = list.Where((x) => x.Code == null || (x.Code != null && x.Code.Length > codeMinLen)).ToArray();
                //MedInsuDirectory[] dataArray = list.Where((x) =>  x.Code.Length > codeMinLen).ToArray();

                //MedInsuDirectory[] dArray = list.Where((x) => x.Code == null || (x.Code != null && x.Code.Length <= codeMinLen)).ToArray();
                //foreach (var x in dArray)
                //{
                //    Global.writeLog(x.Code);
                //}
                if (show)
                {
                    this.Invoke(new MethodInvoker(() =>
                    {
                        DataGridViewExtensions.Bind(this.dataGridView1, dataArray);
                    }));
                }

                //btnLoad.Enabled = false;
                btnPreview.Enabled = true;
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
            return list;
        }

        private Button AddButton(Panel containerPanel, string buttonText)
        {
            Button newButton = new Button();
            newButton.Name = containerPanel.Name + buttonText;
            newButton.Text = buttonText;
            newButton.Height = buttonHeight;
            newButton.AutoSize = true;
            int buttonCountInColumn = containerPanel.Controls.Count;

            // 设置按钮位置和大小
            newButton.Left = (containerPanel.Width - newButton.Width) / 2;
            if (buttonCountInColumn == 0)
                newButton.Top = rowSpacing / 2;
            else
                newButton.Top = containerPanel.Controls[buttonCountInColumn - 1].Bottom + rowSpacing;
            containerPanel.Controls.Add(newButton);
            return newButton;
        }

        private void panelTarget_DragEnter(object sender, DragEventArgs e)
        {
            if (e.Data.GetDataPresent(typeof(Button)))
                e.Effect = DragDropEffects.Copy;
        }

        private void panelTarget_DragDrop(object sender, DragEventArgs e)
        {
            Button target = (Button)sender;

            Button source = (Button)e.Data.GetData(typeof(Button));

            addMapping(source, target);

        }

        private void addMapping(Button source,Button target)
        {
            //string newKey = "[" + source.Text + "]==>[" + target.Text + "]";
            string newKey = source.Text + "=" + target.Text;
            // 记录excel列名和实体属性名的映射关系
            if (mappingPairs.ContainsKey(newKey)) return;

            mappingPairs.Add(newKey,new KeyValuePair<string, string>(source.Text, target.Text));
            source.BackColor = Color.Cyan;
            target.BackColor = Color.Cyan;
            lstMapping.Items.Add(newKey);
        }

        private void btnPreview_Click(object sender, EventArgs e)
        {
            Thread t = new Thread(() =>
            {
                ExcelMapper mapper = createMapper(maxPreviewRows.Value<0);
                this.loadExcel(mapper, true);
            });
            t.Start();
            btnComplete.Enabled = true;

        }

        private void AddMapping(ExcelMapper mapper)
        {
            foreach(string key in this.mappingPairs.Keys)
            {
                KeyValuePair<string, string> kv = this.mappingPairs[key];
                Type destType = typeof(MedInsuDirectory);
                string name = DataGridViewExtensions.getProperty<MedInsuDirectory>(kv.Value);
                if(!string.IsNullOrEmpty(name))
                {
                    mapper.AddMapping(destType, kv.Key, name);
                }

            }
                
        }

        private void btnComplete_Click(object sender, EventArgs e)
        {
            string tips = string.Format("医院【{0}】，接口【{1}】 \r\n 你确定要导入这些数据吗?", Global.inf.hospitalDr, Global.inf.interfaceDr);
            if (!tools.Confirm(tips)) return;

            new Thread(new ThreadStart(upload)).Start();
        }

        private void upload()
        {
            ExcelMapper excel = createMapper(false);

            List<MedInsuDirectory> list = this.loadExcel(excel,false);
            // 每次最大条数
            int pageSize = Decimal.ToInt32(maxUploadRows.Value);
            int index = 0;
            int pages = list.Count / pageSize;
            if ((list.Count % pageSize) > 0)
            {
                pages = pages + 1;
            }
            while (index < pages)
            {
                int count = pageSize;
                if((index+1) * pageSize > list.Count) count = list.Count % pageSize;

                List<MedInsuDirectory> pageList = list.GetRange(index * pageSize, count);
                // upload to server
                uploadToServer(pageList);
                index++;
            }
            MessageBox.Show("目录上传完成!");
            
        }

        private void uploadToServer(List<MedInsuDirectory> list)
        {
            InvokeHelper helper = new InvokeHelper();
            string data = getUploadParams(list);
            if (this.onImport != null)
            {
                this.onImport.Invoke("开始上传:"+data);
            }
            JObject ret = helper.invokeInsuService(data, "医保目录上传");
            if (this.onImport != null)
            {
                this.onImport.Invoke("返回:"+JsonHelper.toJsonString(ret));
            }
        }

        private string getUploadParams(List<MedInsuDirectory> list)
        {

            //再过滤一次
            int codeMinLen = int.Parse(codeMinLength.Value.ToString());
            MedInsuDirectory[] dataArray = list.Where((x) => (x.Name?.Length>2) && (x.Code?.Length > codeMinLen)).ToArray();

            Request request = new Request();
            request.code = "02020145";
            request.data = list.ToArray();
            request.session = new object[] { LoginUser.Current.user };

            return JsonHelper.toJsonString(request);
        }

        private void ExcelImport_Load(object sender, EventArgs e)
        {
            this.rdDrugs.Checked = true;
            this.btnComplete.Enabled = false;
            this.btnPreview.Enabled = false;
            readIni();
        }

        private void btnMapping_Click(object sender, EventArgs e)
        {
            foreach (Control c in panelSource.Controls)
            {
                Control[] targets = panelTarget.Controls.Find(panelTarget.Name + c.Text, true);
                if (targets != null && targets.Length == 1)
                {
                   addMapping((Button)c, (Button)targets[0]);
                }
            }
            
        }

        private void lstMapping_DoubleClick(object sender, EventArgs e)
        {
            if (lstMapping.SelectedIndex != -1)
            {
                string key = lstMapping.Items[lstMapping.SelectedIndex].ToString();
                lstMapping.Items.Remove(key);
                this.mappingPairs.Remove(key);
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            if (lstMapping.Items.Count == 0)
            {
                MessageBox.Show("映射关系不能为空");
                return;
            }
            IniFile ini = new IniFile(Global.curEvt.path+ @"\InsuMapRelation.ini");
            ini.DeleteSection(mapSection);
            foreach (var item in lstMapping.Items)
            {
                string pair = item.ToString();
                int length = pair.Length;
                int pos = pair.IndexOf("=");
                ini.WriteValue(mapSection, pair.Substring(0, pos), pair.Substring(pos+1, length - pos - 1));
            }

            

        }

        private void button1_Click(object sender, EventArgs e)
        {
            initMapper();
            lstMapping.Items.Clear();
            IniFile ini = new IniFile(Global.curEvt.path + @"\InsuMapRelation.ini");
            foreach (var item in mapLst)
            {
                string sourceField = item.ToString();
                string targetField = ini.ReadValue(mapSection, item.ToString());
                string newKey =  sourceField + "=" + targetField;
                lstMapping.Items.Add(newKey);
                mappingPairs.Add(newKey, new KeyValuePair<string, string>(sourceField ,targetField));
                Control[]  targets = panelTarget.Controls.Find(panelTarget.Name + targetField, true);
                Control[] sources = panelSource.Controls.Find(panelSource.Name + sourceField, true);
                //MessageBox.Show(sourceField);
                //foreach (Control ite in targets)
                //{
                //    MessageBox.Show(((Button)ite).Name);
                //}

                //foreach (Control ite in panelTarget.Controls)
                //{
                //    MessageBox.Show(((Button)ite).Name);
                //}
                if (targets != null && targets.Length == 1)
                { 
                    ((Button)targets[0]).BackColor = Color.Cyan;
                }
                if (sources != null && sources.Length == 1)
                {
                    ((Button)sources[0]).BackColor = Color.Cyan;
                }
            }
        }

        private void checkChange(object sender, EventArgs e)
        {
            readIni();
        }

    }

    
}