mes-client/UAS-MES/FunctionCode/Query/Query_SpecialReport.cs

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using UAS_MES.DataOperate;
using UAS_MES.Entity;

namespace UAS_MES.Query
{
    public partial class Query_SpecialReport : Form
    {

        DataHelper dh = SystemInf.dh;

        public Query_SpecialReport()
        {
            InitializeComponent();
        }

        private void Export_Click(object sender, EventArgs e)
        {
            ExcelExport("");
        }


        //选择导出Excel时是选择导出数据的还是模板
        private void ExcelExport(string DataOrTemplet)
        {
            //Data表示导出数据
            //Templet表示导出模板
            folderBrowserDialog1.Description = "选择导出的路径";
            DialogResult result = folderBrowserDialog1.ShowDialog();
            if (result == DialogResult.OK)
            {
                string FolderPath = folderBrowserDialog1.SelectedPath;
                ExcelHandler eh = new ExcelHandler();
                DataTable dt = new DataTable();
                string st_tablesql = "";
                string st_id = "";
                string st_condition = "";
                dt = (DataTable)dh.ExecuteSql("select st_id,st_tablesql,st_condition from SEARCHTEMPLATE where st_title='ZIDE出货报表'", "select");
                if (dt.Rows.Count > 0)
                {
                    st_tablesql = dt.Rows[0]["st_tablesql"].ToString();
                    st_id = dt.Rows[0]["st_id"].ToString();
                    st_condition = dt.Rows[0]["st_condition"].ToString();
                    dt = (DataTable)dh.ExecuteSql("select stg_field,stg_text,stg_table,stg_formula,stg_type from  SEARCHTEMPLATEgrid where stg_stid='" + st_id + "'", "select");
                    string SQLField = "";
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        if (dt.Rows[i]["stg_table"].ToString() != "")
                        {
                            if (dt.Rows[i]["stg_type"].ToString() == "DATE")
                            {
                                SQLField += "to_char(" + dt.Rows[i]["stg_field"].ToString() + ",'YYYYMMDD') as " + dt.Rows[i]["stg_text"].ToString() + ",";
                            }
                            else
                            {
                                SQLField += dt.Rows[i]["stg_field"].ToString() + " as " + dt.Rows[i]["stg_text"].ToString() + ",";
                            }
                        }
                        else
                        {
                            if (dt.Rows[i]["stg_formula"].ToString().Replace("'", "").Trim() == "")
                                SQLField += "''" + " as " + dt.Rows[i]["stg_text"].ToString() + ",";
                            else
                                SQLField += dt.Rows[i]["stg_formula"].ToString() + " as " + dt.Rows[i]["stg_text"].ToString() + ",";
                        }
                    }
                    SQLField = SQLField.Substring(0, SQLField.Length - 1);
                    string sql = "select " + SQLField + " from " + st_tablesql;
                    if (st_condition != "")
                    {
                        sql += " where " + st_condition;
                    }
                    if (SerialNum.Text != "")
                    {
                        sql += " and ms_sncode='" + SerialNum.Text + "'";
                    }
                    if (CartonBegin.Text != "" || CartonEnd.Text != "")
                    {
                        if (CartonBegin.Text != "" && CartonEnd.Text == "")
                        {
                            sql += " and ms_outboxcode> '" + CartonBegin.Text + "' ";
                        }
                        if (CartonBegin.Text == "" && CartonEnd.Text != "")
                        {
                            sql += " and ms_outboxcode< '" + CartonBegin.Text + "' ";
                        }
                        if (CartonBegin.Text != "" && CartonEnd.Text != "")
                        {
                            sql += " and ms_outboxcode between '" + CartonBegin.Text + "' and '" + CartonEnd.Text + "' ";
                        }
                    }
                    if (PaletteBegin.Text != "" || PaletteBegin.Text != "")
                    {
                        if (PaletteBegin.Text != "" && PaletteEnd.Text == "")
                        {
                            sql += " and v_outboxcode> '" + PaletteBegin.Text + "' ";
                        }
                        if (PaletteBegin.Text == "" && PaletteEnd.Text != "")
                        {
                            sql += " and v_outboxcode< '" + PaletteEnd.Text + "' ";
                        }
                        if (PaletteBegin.Text != "" && PaletteEnd.Text != "")
                        {
                            sql += " and v_outboxcode between '" + PaletteBegin.Text + "' and '" + PaletteEnd.Text + "' ";
                        }
                    }
                    if (InDate.Text != "")
                    {
                        sql += " and trunc(ms_indate) = trunc(to_date('" + InDate.Value.ToString("yyyy-MM-dd") + "','yyyy-MM-dd')) ";
                    }
                    dt = (DataTable)dh.ExecuteSql(sql, "select");
                    //导出Excel的时候返回一个文件名，用户选择是否打开
                    /*string filePath = */
                    eh.WriteTxt(dt, FolderPath, "NL" + DateTime.Now.Year.ToString().Substring(2,2) + (DateTime.Now.DayOfYear < 100 ? "0" + DateTime.Now.DayOfYear.ToString() : DateTime.Now.DayOfYear.ToString()) + "COC");
                    ////用户选择导出之后是否立即打开
                    //MessageBoxButtons messButton = MessageBoxButtons.YesNo;
                    //string openFile = MessageBox.Show(this.ParentForm, "是否打开文件", "提示", messButton).ToString();
                    //if (openFile == "Yes")
                    //{
                    //    System.Diagnostics.Process.Start(filePath);
                    //}
                }
            }
        }
    }
}