mes-client/UAS_MES_ODLF/FunctionCode/Query/Query_DateRate.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_DateRate : Form
    {
        DataHelper dh = SystemInf.dh;

        DataTable Dbfind;

        ExcelHandler eh = new ExcelHandler();

        public Query_DateRate()
        {
            InitializeComponent();
        }

        private void Export_Click(object sender, EventArgs e)
        {
            if (pr_code.Text == "")
            {
                MessageBox.Show("请输入产品代码");
                return;
            }
            if (EndDate.Value < BeginDate.Value)
            {
                MessageBox.Show("结束日期不能小于起始日期");
            }
            ExcelExport("");
        }

        //选择导出Excel时是选择导出数据的还是模板
        private void ExcelExport(string DataOrTemplet)
        {
            folderBrowserDialog1.Description = "选择导出的路径";
            DialogResult result = folderBrowserDialog1.ShowDialog();
            if (result == DialogResult.OK)
            {
                string v_sql = "";
                string v_sql1 = "";
                string condition = "sp_date between to_date('" + BeginDate.Value.ToString("yyyy-MM-dd HH:mm:ss") + "', 'yyyy-mm-dd hh24:mi:ss') and to_date('" + EndDate.Value.ToString("yyyy-MM-dd HH:mm:ss") + "', 'yyyy-mm-dd hh24:mi:ss') ";
                if (pr_code.Text != "")
                {
                    condition += " and sp_prodcode = '" + pr_code.Text + "'";
                }
                if (ma_code.Text != "")
                {
                    condition += " and sp_makecode = '" + ma_code.Text + "'";
                }
                if (li_code.Text != "")
                {
                    condition += " and sp_linecode = '" + li_code.Text + "'";
                }
                LogicHandler.GetQuerySql(condition, out v_sql, out v_sql1);

                DataTable dt = (DataTable)dh.ExecuteSql(v_sql + v_sql1, "select");
                string FolderPath = folderBrowserDialog1.SelectedPath;


                string begindate = BeginDate.Value.ToString("yyyy-mm-dd");
                string enddate = EndDate.Value.ToString("yyyy-mm-dd");
                string path = eh.ExportExcel_BAIDU(dt, BeginDate.Value, (EndDate.Value - BeginDate.Value).Days + 1, FolderPath);
                string close = MessageBox.Show(this.ParentForm, "导出成功，是否打开文件", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Question).ToString();
                if (close.ToString() == "Yes")
                    System.Diagnostics.Process.Start(path);
            }
        }

        private void Query_SpecialReport_Load(object sender, EventArgs e)
        {
            //工单号放大镜配置
            pr_code.TableName = " product ";
            pr_code.SelectField = "pr_code # 产品编号,pr_detail # 产品名称,pr_spec # 型号";
            pr_code.FormName = Name;
            pr_code.SetValueField = new string[] { "pr_code" };
            pr_code.Condition = "pr_statuscode='AUDITED'";
            pr_code.DbChange += pr_code_DbChange;

            ma_code.TableName = " make left join product on ma_prodcode=pr_code";
            ma_code.SelectField = "ma_code # 工单编号,ma_prodcode # 产品编号,pr_spec # 型号";
            ma_code.FormName = Name;
            ma_code.SetValueField = new string[] { "ma_code" };
            ma_code.Condition = "ma_statuscode='STARTED'";
            ma_code.DbChange += pr_code_DbChange;

            li_code.TableName = " line ";
            li_code.SelectField = "li_code # 线别编号,li_name # 线别名称";
            li_code.FormName = Name;
            li_code.SetValueField = new string[] { "li_code" };
            li_code.Condition = "li_code is not null";
            li_code.DbChange += pr_code_DbChange;
        }

        private void pr_code_DbChange(object sender, EventArgs e)
        {
            Dbfind = pr_code.ReturnData;
            BaseUtil.SetFormValue(this.Controls, Dbfind);
        }

        private void Miss_Test_Click(object sender, EventArgs e)
        {
            folderBrowserDialog1.Description = "选择导出的路径";
            DialogResult result = folderBrowserDialog1.ShowDialog();
            if (result == DialogResult.OK)
            {
                string v_sql = "";
                string v_sql1 = "";
                string condition = "sp_date between to_date('" + BeginDate.Value.ToString("yyyy-MM-dd HH:mm:ss") + "', 'yyyy-mm-dd hh24:mi:ss') and to_date('" + EndDate.Value.ToString("yyyy-MM-dd HH:mm:ss") + "', 'yyyy-mm-dd hh24:mi:ss') ";
                if (pr_code.Text != "")
                {
                    condition += " and sp_prodcode = '" + pr_code.Text + "'";
                }
                if (ma_code.Text != "")
                {
                    condition += " and sp_makecode = '" + ma_code.Text + "'";
                }
                if (li_code.Text != "")
                {
                    condition += " and sp_linecode = '" + li_code.Text + "'";
                }
                LogicHandler.GetMissItemQuerySql(condition, out v_sql, out v_sql1);
                DataTable dt = (DataTable)dh.ExecuteSql(v_sql, "select");
                LogicHandler.GetMissFiveQuerySql(condition, out v_sql, out v_sql1);
                DataTable dt1 = (DataTable)dh.ExecuteSql(v_sql, "select");
                LogicHandler.GetBadFiveQuerySql(condition, out v_sql, out v_sql1);
                DataTable dt2 = (DataTable)dh.ExecuteSql(v_sql, "select");
                string FolderPath = folderBrowserDialog1.SelectedPath;
                Console.WriteLine(dt.Rows.Count);
                Console.WriteLine(dt1.Rows.Count);
                Console.WriteLine(dt2.Rows.Count);
                string path = eh.ExportExcel(new DataTable[] { dt, dt1, dt2 }, FolderPath);
                string close = MessageBox.Show(this.ParentForm, "导出成功，是否打开文件", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Question).ToString();
                if (close.ToString() == "Yes")
                    System.Diagnostics.Process.Start(path);
            }
        }
    }
}