using Newtonsoft.Json.Linq;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Dynamic;
using System.IO;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace DataExport
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        public SqlSugarClient Db;
        /// <summary>
        /// 连接
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void BtnDatabaseconn_Click(object sender, EventArgs e)
        {
            string datasource = this.datasource.Text;
            string username = this.username.Text;
            string password = this.password.Text;

            if (string.IsNullOrEmpty(datasource))
            {
                MessageBox.Show("数据库地址不能为空");
                return;
            }
            if (string.IsNullOrEmpty(username))
            {
                MessageBox.Show("用户名不能为空");
                return;
            }
            if (string.IsNullOrEmpty(password))
            {
                MessageBox.Show("密码不能为空");
                return;
            }
            connectState.Text = "未连接";
            connectState.ForeColor = System.Drawing.Color.Red;
            string connectionString = "Data Source="+datasource+";User ID="+ username + ";Password="+ password + ";";
            try
            {
                 Db = new SqlSugarClient(new ConnectionConfig()
                {
                    ConnectionString = connectionString,
                    DbType = SqlSugar.DbType.Oracle,
                    IsAutoCloseConnection = true,
                    InitKeyType = InitKeyType.Attribute
                });
                Db.Ado.CheckConnection();
                MessageBox.Show("连接成功");
                initTableListView();
                connectState.Text = "已连接";
                connectState.ForeColor = System.Drawing.Color.Green;
            }
            catch(Exception ex)
            {
                Db = null;
                MessageBox.Show("连接失败，请检查连接字符串是否正确\n"+ex.Message);
            }
        }
        /// <summary>
        /// 初始化所有表
        /// </summary>
        private void initTableListView()
        {
            List<DbTableInfo> infoList=  Db.DbMaintenance.GetTableInfoList(false);
           
           foreach (DbTableInfo tableInfo in infoList)
            {
                lbxtableinfo.Items.Add(tableInfo.Name);
            }
        }
        /// <summary>
        /// 选择导出文件夹
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnexportSelectFolder_Click(object sender, EventArgs e)
        {
           DialogResult result= folderBrowserDialog1.ShowDialog();
           if (result == DialogResult.OK)
           {
                this.exportPath.Text = folderBrowserDialog1.SelectedPath;
           }
        }
        /// <summary>
        /// 导出按钮
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnexport_Click(object sender, EventArgs e)
        {
            if (Db == null)
            {
                MessageBox.Show("请先连接数据库");
                return;
            }
            if (string.IsNullOrEmpty( this.exportPath.Text))
            {
                MessageBox.Show("请先选择保存的路径");
                return;
            }
            if (lbxtableinfo.SelectedItems.Count == 0)
            {
                MessageBox.Show("请先选择表");
                return;
            }

            string tableName = lbxtableinfo.SelectedItem.ToString();
            string key = exportKey.Text;
            string where = exportWhere.Text;

            string sql = "select * from " + tableName + (string.IsNullOrWhiteSpace(where)? "" : " where " + where);
            List<ExpandoObject> expandList = Db.SqlQueryable<ExpandoObject>(sql).ToList();

            JArray resultArray = JArray.FromObject(expandList);

            JObject jo = new JObject();

            jo["tableName"] = tableName;
            jo["key"] = key;
            jo["dataList"] = resultArray;

            string newFileName = tableName + DateTime.Now.ToString("_yyyyMMddHHssmm")+".json";
            string fullPath = this.exportPath.Text + "\\" + newFileName;
            if(!File.Exists(fullPath))
            {
               FileStream fs= File.Create(fullPath);
               fs.Close();
            }
            File.WriteAllText(fullPath, jo.ToString(), Encoding.UTF8);
            MessageBox.Show("导出成功");
        }
        /// <summary>
        /// 选择导入文件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnInputSelectFile_Click(object sender, EventArgs e)
        {
            DialogResult result=  openFileDialog1.ShowDialog();
            if (result == DialogResult.OK)
            {
                this.inputPath.Text = string.Join(",", this.openFileDialog1.FileNames);
            }
        }
       /// <summary>
       /// 导入
       /// </summary>
       /// <param name="sender"></param>
       /// <param name="e"></param>
        private void btninput_Click(object sender, EventArgs e)
        {
            if (Db == null)
            {
                MessageBox.Show("请先连接数据库");
                return;
            }
            if (string.IsNullOrEmpty(this.inputPath.Text))
            {
                MessageBox.Show("请先选择导入的路径");
                return;
            }
            try
            {
                string[] pathArray = this.inputPath.Text.Split(',');
                foreach (string path in pathArray)
                {
                    string text = File.ReadAllText(path, Encoding.UTF8);
                    JObject jo = JObject.Parse(text);

                    string tableName = jo["tableName"].ToString();
                    string key = jo["key"].ToString();
                    JArray dataList = jo["dataList"].Value<JArray>();

                    if (dataList != null && dataList.Count > 0)
                    {
                        foreach (JToken jtoken in dataList)
                        {

                            List<SugarParameter> paramList = new List<SugarParameter>();
                            JObject data = jtoken as JObject;

                            string fields = "";
                            string values = "";
                            data.Properties().ToList().ForEach(a =>
                            {
                                paramList.Add(new SugarParameter("@" + a.Name, data[a.Name].ToString()));
                                fields += a.Name + ",";
                                values += "@" + a.Name + ",";
                            });

                            fields= fields.TrimEnd(',');
                            values= values.TrimEnd(',');
                            string insertsql = "insert into  " + tableName + " (" + fields + ") values (" + values + ")";
                            string deletsql = "";
                            if (!string.IsNullOrEmpty(key))
                            {
                                deletsql = "delete from " + tableName + " where " + key + " = " + data.GetValue(key, StringComparison.OrdinalIgnoreCase).ToString();
                            }

                            Db.Ado.BeginTran();
                            if (!string.IsNullOrEmpty(deletsql))
                            {
                                Db.Ado.ExecuteCommand(deletsql);
                            }
                            Db.Ado.ExecuteCommand(insertsql, paramList);
                            Db.Ado.CommitTran();
                        }
                    }
                }
                MessageBox.Show("导入成功");
            }
            catch(Exception ex)
            {
                Db.Ado.RollbackTran();
                MessageBox.Show("导入失败\n" + ex.Message);
            }
        }
    }
}