mes-client/UAS_MES_HY/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_NEW.DataOperate;
using UAS_MES_NEW.Entity;
using UAS_MES_NEW.PublicMethod;

namespace UAS_MES_NEW.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)
                {
                    string InOutNo = "";
                    st_tablesql = dt.Rows[0]["st_tablesql"].ToString();
                    st_id = dt.Rows[0]["st_id"].ToString();
                    st_condition = dt.Rows[0]["st_condition"].ToString();
                    string SQLField = "";
                    string AddCondition = "";
                    if (inoutno.Text != "")
                    {
                        AddCondition = " and ms_outno='" + inoutno.Text + "'";
                    }
                    if (SerialNum.Text != "")
                    {
                        AddCondition += " and ms_sncode='" + SerialNum.Text + "'";
                    }
                    if (CartonBegin.Text != "" || CartonEnd.Text != "")
                    {
                        if (CartonBegin.Text != "" && CartonEnd.Text == "")
                        {
                            AddCondition += " and ms_outboxcode> '" + CartonBegin.Text + "' ";
                        }
                        if (CartonBegin.Text == "" && CartonEnd.Text != "")
                        {
                            AddCondition += " and ms_outboxcode< '" + CartonBegin.Text + "' ";
                        }
                        if (CartonBegin.Text != "" && CartonEnd.Text != "")
                        {
                            AddCondition += " and ms_outboxcode between '" + CartonBegin.Text + "' and '" + CartonEnd.Text + "' ";
                        }
                    }
                    if (PaletteBegin.Text != "" || PaletteBegin.Text != "")
                    {
                        if (PaletteBegin.Text != "" && PaletteEnd.Text == "")
                        {
                            AddCondition += " and v_outboxcode> '" + PaletteBegin.Text + "' ";
                        }
                        if (PaletteBegin.Text == "" && PaletteEnd.Text != "")
                        {
                            AddCondition += " and v_outboxcode< '" + PaletteEnd.Text + "' ";
                        }
                        if (PaletteBegin.Text != "" && PaletteEnd.Text != "")
                        {
                            AddCondition += " and v_outboxcode between '" + PaletteBegin.Text + "' and '" + PaletteEnd.Text + "' ";
                        }
                    }
                    if (InDate.Checked)
                    {
                        AddCondition += " and trunc(ms_indate) = trunc(to_date('" + InDate.Value.ToString("yyyy-MM-dd") + "','yyyy-MM-dd')) ";
                    }
                    string GetInOutNoSQL = "select ms_outno from " + st_tablesql;
                    if (st_condition != "")
                    {
                        GetInOutNoSQL += " where " + st_condition;
                    }
                    //获取出货单号
                    dt = (DataTable)dh.ExecuteSql(GetInOutNoSQL + AddCondition + " and rownum=1", "select");
                    if (dt.Rows.Count > 0)
                    {
                        InOutNo = dt.Rows[0]["ms_outno"].ToString();
                    }
                    DataTable SeqTanle = (DataTable)dh.ExecuteSql("select cl_code,cl_result from commandlog where cl_operate='导出特殊报表' order by cl_id desc", "select");
                    string SEQ_NUMBER = "";
                    string SEQ = "";
                    string SEQ_PreFix = "NL" + DateTime.Now.Year.ToString().Substring(2, 2) + (DateTime.Now.DayOfYear < 100 ? "0" + DateTime.Now.DayOfYear.ToString() : DateTime.Now.DayOfYear.ToString()) + "COC";
                    if (SeqTanle.Rows.Count > 0)
                    {
                        //如果今天已经包含本出货单则导出的使用之前的流水
                        DataRow[] dr = SeqTanle.Select("cl_result='" + InOutNo + "'");
                        if (dr.Length > 0)
                        {
                            SEQ_NUMBER = dr[0]["cl_code"].ToString();
                            SEQ = SEQ_PreFix + SEQ_NUMBER;
                        }
                        else
                        {
                            SEQ_NUMBER = SeqTanle.Rows[0]["cl_code"].ToString();
                            int temp = int.Parse(SEQ_NUMBER) + 1;
                            SEQ_NUMBER = temp.ToString().PadLeft(5, '0');
                            SEQ = SEQ_PreFix + SEQ_NUMBER;
                            LogicHandler.DoCommandLog(Tag.ToString(), User.CurrentStepCode, "", "", User.UserSourceCode, "导出特殊报表", InOutNo, SEQ, SEQ_NUMBER);
                        }
                    }
                    else
                    {
                        SEQ = SEQ_PreFix + "00001";
                        LogicHandler.DoCommandLog(Tag.ToString(), User.CurrentStepCode, "", "", User.UserSourceCode, "导出特殊报表", InOutNo, SEQ, "00001");
                    }
                    dt = (DataTable)dh.ExecuteSql("select stg_field,stg_text,stg_table,stg_formula,stg_type from  SEARCHTEMPLATEgrid where stg_stid='" + st_id + "'", "select");
                    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_text"].ToString() == "SEQ")
                            {
                                SQLField += "'" + SEQ + "'" + "||';' 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;
                    }
                    //界面生成的条件
                    dt = (DataTable)dh.ExecuteSql(sql + AddCondition, "select");
                    //导出Excel的时候返回一个文件名，用户选择是否打开
                    eh.WriteTxt(dt, FolderPath, SEQ);
                    MessageBox.Show("导出成功");
                }
            }
        }

        private void Query_SpecialReport_Load(object sender, EventArgs e)
        {
            InDate.Checked = false;
        }
    }
}