using LLAirtightApi;
using Microsoft.Office.Interop.Excel;
using Newtonsoft.Json;
using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Reflection;
using System.Text;

namespace SLZ_4
{
    class SaveRecord
    {
        public static void WriteTXT(ref TEST_PARAM testInfo, string code, string strPath, int iCH)
        {
            string fileName = strPath + "\\" + (iCH + 1).ToString() + "_" + DateTime.Now.ToString("yyyyMMdd") + ".txt";

            string str1 = "时间: " + DateTime.Now 
                + "操作员: " + FormMain.form.labelOperator.Text
                + " 条形码: " + code 
                + " 测试压力: " + testInfo.chTest[iCH].fTestPressure.ToString("F3")
                + " 测试压力单位: " + FormMain.form.strListPressureUnit[testInfo.iPressureUnit] 
                + " 当前压差:  " + testInfo.chTest[iCH].fTestDiffPressure.ToString("F3")
                + " 泄漏量: " + testInfo.chTest[iCH].fLeakAmount.ToString("F3") 
                + " 泄漏量单位: " + FormMain.form.strListLeakageUnit[testInfo.iLeakUnit]
                + " 测试结果: " + FormMain.form.strResult[testInfo.chTest[iCH].ResultFlag] + " \n";

            System.IO.File.AppendAllText(fileName, str1);
        }

        public static void WriteCSV(ref TEST_PARAM testInfo, string code, string strPath, int iCH)
        {
            try
            {
                string fileName = strPath + "\\" + (iCH + 1).ToString() + "_" + DateTime.Now.ToString("yyyyMMdd") + ".csv";
                bool bWriter = false;
                if (!File.Exists(fileName))
                {
                    bWriter = true;
                }
                StreamWriter fileWriter = new StreamWriter(fileName, true, Encoding.UTF8);
                if (bWriter)
                {
                    fileWriter.Write("时间,操作员,条形码,测试压力,测试压力单位,当前压差,泄漏量,泄漏量单位,测试结果" + "\r\n");
                    fileWriter.Flush();
                }

                string nowdate = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss");

                fileWriter.Write(nowdate + ",");
                fileWriter.Write(FormMain.form.labelOperator.Text + ",");
                fileWriter.Write(code + ",");
                fileWriter.Write(testInfo.chTest[iCH].fTestPressure.ToString("F3") + ",");
                fileWriter.Write(FormMain.form.strListPressureUnit[testInfo.iPressureUnit] + ",");
                fileWriter.Write(testInfo.chTest[iCH].fTestDiffPressure.ToString("F3") + ",");
                fileWriter.Write(testInfo.chTest[iCH].fLeakAmount.ToString("F3") + ",");
                fileWriter.Write(FormMain.form.strListLeakageUnit[testInfo.iLeakUnit] + ",");
                fileWriter.Write(FormMain.form.strResult[testInfo.chTest[iCH].ResultFlag] + "\r\n");

                fileWriter.Flush();
                fileWriter.Close();
            }
            catch (Exception ex)
            {
                FileLogger.Log("写入CSV失败: " + ex.Message);
                //MessageBox.Show(ex.Message);
            }
        }
        public static bool OpenExcel(string sPath, string code, string result)
        {
            string path = sPath + "\\" + DateTime.Now.ToString("yyyyMMdd");
            string fileName = path + "\\" + code + "_" + result + "_" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xlsx";
            if (!Directory.Exists(path))
            {
                Directory.CreateDirectory(path);
            }
            try
            {
                FormMain.form.xapp = new Microsoft.Office.Interop.Excel.Application();

                if (File.Exists(fileName) == false)//判断所选路径是否有文件
                {
                    //若不存在该文件，则创建新文件
                    Workbooks xbook1 = FormMain.form.xapp.Workbooks;
                    FormMain.form.xbook = FormMain.form.xapp.Workbooks.Add(true);
                    Worksheet xsheet = (Worksheet)FormMain.form.xbook.Sheets[1];

                    //添加表头
                    string[] fieldArr = { "时间", "条形码", "测试压力", "测试压力单位", "当前压差", "泄漏量", "泄漏量单位", "测试结果" };
                    for (int i = 0; i < fieldArr.Length; i++)
                    {
                        xsheet.Cells[i + 1][1] = fieldArr[i];
                    }
                    FormMain.form.xbook.SaveAs(fileName);//按照指定路径存储新文件
                }
                else
                {
                    //若存在该文件，则打开文件并写入数据
                    FormMain.form.xbook = FormMain.form.xapp.Workbooks._Open(fileName, Missing.Value, Missing.Value,
                                       Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                                       Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                }

                FormMain.form.xapp.DisplayAlerts = false;

            }
            catch (Exception e)
            {
                FileLogger.Log("打开Excel文件失败: " + e.Message, LogLevel.ERROR);
                //MessageBox.Show("打开Excel文件失败: " + e.Message);
                return false;
            }
            return true;
        }
        public static void WriteExcel(TEST_PARAM testInfo, string code, string strPath, int iCH)
        {
            try
            {
                if (OpenExcel(strPath, code, FormMain.form.strResult[testInfo.chTest[iCH].ResultFlag]))
                {
                    Worksheet xsheet = (Worksheet)FormMain.form.xbook.Sheets[1];
                    string[] dataArr = { DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss"), 
                        code, testInfo.chTest[iCH].fTestPressure.ToString("F3"),
                        FormMain.form.strListPressureUnit[testInfo.iPressureUnit], testInfo.chTest[iCH].fTestDiffPressure.ToString("F1"), 
                        testInfo.chTest[iCH].fLeakAmount.ToString("F3"),
                        FormMain.form.strListLeakageUnit[testInfo.iLeakUnit], 
                        FormMain.form.strResult[testInfo.chTest[iCH].ResultFlag]};

                    int c = xsheet.UsedRange.Rows.Count;
                    for (int i = 0; i < dataArr.Length; i++)
                    {
                        xsheet.Cells[i + 1][c + 1] = dataArr[i];
                    }

                    FormMain.form.xbook.Save();
                    if (FormMain.form.xbook != null)
                    {
                        FormMain.form.xbook.Close();
                        FormMain.form.xbook = null;
                        if (FormMain.form.xapp != null)
                        {
                            FormMain.form.xapp.Quit();
                            FormMain.form.xapp = null;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                FileLogger.Log("打开Excel失败: "+ ex.Message);
                //MessageBox.Show("Excel:" + ex.Message);
            }

        }
        public static bool OpenSQL()
        {
            if (FormMain.form.sqlConnection != null)
            {
                if (FormMain.form.sqlConnection.State == ConnectionState.Open)
                {
                    return true;
                }
                else
                {
                    FormMain.form.sqlConnection.Close();
                    FormMain.form.sqlConnection = null;
                }
            }
            try
            {
                //string connectionString = "Data source=" + configParam.databaseParam.sServer;
                //connectionString += ";Initial Catalog=master;User Id=" + configParam.databaseParam.sDBUser
                //    + ";Password=" + configParam.databaseParam.sDBPassword;//定义连接字符串

                //sqlConnection = new SqlConnection(connectionString);//创建连接
                //sqlConnection.Open();//打开并连接数据库

                //if (configParam.databaseParam.sDBName == "")
                //{
                //    configParam.databaseParam.sDBName = "LeakTest";
                //}
                //string selectString = "Select * FROM master..SYSDATABASES WHERE name ='" + configParam.databaseParam.sDBName + "'";//输入sql语句,判断是否存在名为LeakTest的数据库

                //SqlCommand cmd = new SqlCommand(selectString, sqlConnection);
                //var str1 = cmd.ExecuteScalar();
                //if (str1 is null)
                //{
                //    cmd.CommandText = "CREATE DATABASE " + configParam.databaseParam.sDBName;//如果不存在则新建数据库
                //    cmd.ExecuteNonQuery();
                //}


                //sqlConnection.Close();//关闭数据库
                return true;
            }
            catch (Exception e)
            {
                FileLogger.Log("打开数据库失败:" + e.Message, LogLevel.ERROR);
                return false;
                throw;
            }
        }
        public static void WriteSQL(ref TEST_PARAM testInfo, string code, string strPath, int iCH)
        {
            try
            {
                if (OpenSQL())
                {
                    string nowday = DateTime.Now.ToString("yyyy_MM_dd");
                    string selectString2 = "select * from dbo.sysobjects where name=('test" + nowday + "')";//查询是否有名为leaktest+日期的表格
                    //SqlCommand cmd2 = new SqlCommand(selectString2, sqlConnection);
                    //var str2 = cmd2.ExecuteScalar();
                    //if (str2 is null)
                    //{
                    //    //若没有表格则新建表格
                    //    cmd2.CommandText = "CREATE TABLE test" + nowday +
                    //        " ([Time] DATETIME ,[BarCode] VarChar(50),[Pressure] FLOAT,[PressureUnit] VarChar(50)," +
                    //        "[PressureDiff] FLOAT,[Leakage] FLOAT,[LeakageUnit] VarChar(50),[Result] VarChar(50))"; //Office is the named range.
                    //    cmd2.ExecuteNonQuery();
                    //}

                    ////往表格插入数据
                    //cmd2.CommandText = "INSERT INTO test" + nowday +
                    //    " (Time, BarCode, Pressure, PressureUnit, PressureDiff, Leakage, LeakageUnit, Result)" +
                    //    " VALUES('" + DateTime.Now + "', '" + textCode.Text + "', '" + testInfo.fTestPressure.ToString("F3") + "', '" +
                    //    strListPressureUnit[iPressureUnit] + "', '" + testInfo.fTestDiffPressure.ToString("F1") + "', '" +
                    //    testInfo.fLeakAmount.ToString("F3") + "', '" + strListLeakageUnit[ileakageUnit] + "', '" + labelResult.Text + "')";

                    //cmd2.ExecuteNonQuery();
                }
            }
            catch (SqlException ex)
            {
                FileLogger.Log("写入数据库失败: " + ex.Message);
                //MessageBox.Show(ex.Message);
            }
        }
    }
}
