123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181 |
- 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;
- }
- }
- }
|