mes-client/UAS-出货标签管理(贸易版)/ExportColumns.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_LabelMachine.Entity;

namespace UAS_LabelMachine
{
    public partial class ExportColumns : Form
    {
        DataHelper dh = SystemInf.dh;

        string inoutno = "";
        string custcode = "";

        public ExportColumns()
        {
            InitializeComponent();
        }

        public ExportColumns(string IInoutno)
        {
            InitializeComponent();
            inoutno = IInoutno;
        }

        private void ExportColumns_Load(object sender, EventArgs e)
        {
            LoadData();
        }

        private void SaveColumns_Click(object sender, EventArgs e)
        {
            dh.ExecuteSql("delete from CS_EXPORTSETTING where es_custcode='" + custcode + "'", "delete");
            StringBuilder sql = new StringBuilder();
            List<string> es_filed = new List<string>();
            List<string> es_caption = new List<string>();
            List<string> es_enable = new List<string>();
            List<string> es_prefix = new List<string>();
            List<string> es_suffix = new List<string>();
            List<string> es_detno = new List<string>();
            List<string> es_filed1 = new List<string>();
            List<string> es_istitle = new List<string>();
            es_filed.Add("pi_title"); es_caption.Add("客户"); es_enable.Add("-1"); es_prefix.Add("'客户：'"); es_suffix.Add("''"); es_detno.Add("0.1"); es_filed1.Add("pi_title"); es_istitle.Add("-1");
            es_filed.Add("pi_inoutno"); es_caption.Add("出货单号"); es_enable.Add("-1"); es_prefix.Add("'出货单号：'"); es_suffix.Add("''"); es_detno.Add("0.2"); es_filed1.Add("pi_inoutno"); es_istitle.Add("-1");
            es_filed.Add("pr_spec"); es_caption.Add("型号"); es_enable.Add("-1"); es_prefix.Add("'型号：'"); es_suffix.Add("''"); es_detno.Add("0.3"); es_filed1.Add("pr_spec"); es_istitle.Add("-1");
            es_filed.Add("pi_date"); es_caption.Add("销售日期"); es_enable.Add("-1"); es_prefix.Add("'销售日期：'"); es_suffix.Add("'销售部门:市场部'"); es_detno.Add("0.4"); es_filed1.Add("pi_date"); es_istitle.Add("-1");
            es_filed.Add("pd_ordercode"); es_caption.Add("订单编号"); es_enable.Add("-1"); es_prefix.Add("''"); es_suffix.Add("''"); es_detno.Add("0.5"); es_filed1.Add("pd_ordercode"); es_istitle.Add("-1");
            for (int i = 0; i < FirstPage.Columns.Count; i++)
            {
                for (int j = 0; j < FirstPage.Columns.Count; j++)
                {
                    if (i == FirstPage.Columns[j].DisplayIndex)
                    {
                        //pr_spec1 冲突BUG暂时无解，先特殊处理
                        es_filed.Add(FirstPage.Columns[j].Tag.ToString() == "pr_spec1" ? "pr_spec" : FirstPage.Columns[j].Tag.ToString());
                        es_caption.Add(FirstPage.Columns[j].DataPropertyName);
                        es_enable.Add("-1");
                        es_prefix.Add("''");
                        es_suffix.Add("''");
                        es_detno.Add((FirstPage.Columns[j].DisplayIndex+1).ToString());
                        es_filed1.Add(FirstPage.Columns[j].Tag.ToString());
                        es_istitle.Add("");
                    }
                }
            }
            sql.Clear();
            sql.Append("insert into CS_EXPORTSETTING(es_type,es_filed,es_caption,es_enable,es_prefix,es_suffix,es_detno,es_filed1,es_custcode,es_istitle)");
            sql.Append("values('First',:es_filed,:es_caption,:es_enable,:es_prefix,:es_suffix,:es_detno,:es_filed1,'" + custcode + "',:es_istitle)");
            dh.BatchInsert(sql.ToString(), new string[] { "es_filed", "es_caption", "es_enable", "es_prefix", "es_suffix", "es_detno", "es_filed1", "es_istitle" }, es_filed.ToArray(), es_caption.ToArray(), es_enable.ToArray(), es_prefix.ToArray(), es_suffix.ToArray(), es_detno.ToArray(), es_filed1.ToArray(), es_istitle.ToArray());
            //清除首页赋的值
            es_filed.Clear(); es_caption.Clear();es_enable.Clear();es_prefix.Clear();es_suffix.Clear();es_detno.Clear();es_filed.Clear();es_filed1.Clear();es_istitle.Clear();
            //新生成Data的抬头
            es_filed.Add("pi_title"); es_caption.Add("客户"); es_enable.Add("-1"); es_prefix.Add("'客户：'"); es_suffix.Add("''"); es_detno.Add("0.1"); es_filed1.Add("pi_title"); es_istitle.Add("-1");
            es_filed.Add("pi_inoutno"); es_caption.Add("出货单号"); es_enable.Add("-1"); es_prefix.Add("'出货单号：'"); es_suffix.Add("''"); es_detno.Add("0.2"); es_filed1.Add("pi_inoutno"); es_istitle.Add("-1");
            es_filed.Add("pr_spec"); es_caption.Add("型号"); es_enable.Add("-1"); es_prefix.Add("'型号：'"); es_suffix.Add("'销售部门:市场部'"); es_detno.Add("0.3"); es_filed1.Add("pr_spec"); es_istitle.Add("-1");
            es_filed.Add("pi_date"); es_caption.Add("销售日期"); es_enable.Add("-1"); es_prefix.Add("'销售日期：'"); es_suffix.Add("''"); es_detno.Add("0.4"); es_filed1.Add("pi_date"); es_istitle.Add("-1");
            for (int i = 0; i < DataPage.Columns.Count; i++)
            {
                for (int j = 0; j < DataPage.Columns.Count; j++)
                {
                    if (i == DataPage.Columns[j].DisplayIndex)
                    {
                        //pr_spec1 冲突BUG暂时无解，先特殊处理
                        es_filed.Add(DataPage.Columns[j].Tag.ToString()=="pr_spec1"?"pr_spec": DataPage.Columns[j].Tag.ToString());
                        es_caption.Add(DataPage.Columns[j].DataPropertyName);
                        es_enable.Add("-1");
                        es_prefix.Add("''");
                        es_suffix.Add("''");
                        es_detno.Add((DataPage.Columns[j].DisplayIndex + 1).ToString());
                        es_filed1.Add(DataPage.Columns[j].Tag.ToString());
                        es_istitle.Add("");
                    }
                }
            }
            sql.Clear();
            sql.Append("insert into CS_EXPORTSETTING(es_type,es_filed,es_caption,es_enable,es_prefix,es_suffix,es_detno,es_filed1,es_custcode,es_istitle)");
            sql.Append("values('DataPage',:es_filed,:es_caption,:es_enable,:es_prefix,:es_suffix,:es_detno,:es_filed1,'" + custcode + "',:es_istitle)");
            dh.BatchInsert(sql.ToString(), new string[] { "es_filed", "es_caption", "es_enable", "es_prefix", "es_suffix", "es_detno", "es_filed1", "es_istitle" }, es_filed.ToArray(), es_caption.ToArray(), es_enable.ToArray(), es_prefix.ToArray(), es_suffix.ToArray(), es_detno.ToArray(), es_filed1.ToArray(), es_istitle.ToArray());
        }

        private void ResetColumns_Click(object sender, EventArgs e)
        {
            string close = MessageBox.Show(this.ParentForm, "是否重置列", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Question).ToString();
            if (close == "Yes")
            {
                dh.ExecuteSql("delete from CS_EXPORTSETTING where es_custcode='" + custcode + "'", "delete");
                LoadData();
            }
        }

        private void DeleteColumns_Click(object sender, EventArgs e)
        {
            DataGridViewSelectedCellCollection cl = FirstPage.SelectedCells;
            List<int> deleteIndex = new List<int>();
            for (int i = 0; i < cl.Count; i++)
            {
                if (!deleteIndex.Contains(cl[i].ColumnIndex))
                {
                    deleteIndex.Add(cl[i].ColumnIndex);
                }
            }
            for (int i = FirstPage.Columns.Count - 1; i >= 0; i--)
            {
                for (int j = 0; j < deleteIndex.ToArray().Length; j++)
                {
                    if (deleteIndex.ToArray()[j] == i)
                        FirstPage.Columns.RemoveAt(i);
                }
            }
        }

        private void DeleteDataColumns_Click(object sender, EventArgs e)
        {
            DataGridViewSelectedCellCollection cl = DataPage.SelectedCells;
            List<int> deleteIndex = new List<int>();
            for (int i = 0; i < cl.Count; i++)
            {
                if (!deleteIndex.Contains(cl[i].ColumnIndex))
                {
                    deleteIndex.Add(cl[i].ColumnIndex);
                }
            }
            for (int i = DataPage.Columns.Count - 1; i >= 0; i--)
            {
                for (int j = 0; j < deleteIndex.ToArray().Length; j++)
                {
                    if (deleteIndex.ToArray()[j] == i)
                        DataPage.Columns.RemoveAt(i);
                }
            }
        }

        private void LoadData()
        {
            StringBuilder sql = new StringBuilder();
            custcode = dh.getFieldDataByCondition("prodinout", "pi_cardcode", "pi_inoutno='" + inoutno + "'").ToString();
            DataTable SQL1_ = (DataTable)dh.ExecuteSql("select es_filed,es_caption,es_prefix,es_suffix,es_filed1 from CS_EXPORTSETTING where es_type='DataPage' and es_enable=-1 and es_custcode='" + custcode + "' and nvl(es_istitle,0)<>-1 order by es_detno", "select");
            //没有置顶客户的取空的
            if (SQL1_.Rows.Count == 0)
            {
                SQL1_ = (DataTable)dh.ExecuteSql("select es_filed,es_caption,es_prefix,es_suffix,es_filed1 from CS_EXPORTSETTING where es_type='DataPage' and es_enable=-1 and es_custcode is null  and nvl(es_istitle,0)<>-1 order by es_detno", "select");
            }
            string es_field1 = "";
            for (int i = 0; i < SQL1_.Rows.Count; i++)
            {
                es_field1 += SQL1_.Rows[i]["es_prefix"].ToString() + "||" + SQL1_.Rows[i]["es_filed"].ToString() + "||" + SQL1_.Rows[i]["es_suffix"].ToString() + ",";
            }
            //分页信息的DataTable
            sql.Clear();
            sql.Append("select " + es_field1.Substring(0, es_field1.Length - 1) + " from  prodinout left join prodiodetail on pi_id=pd_piid left join ");
            sql.Append("chip_in_out on pd_id=io_pdid left join chip on ch_code=io_chipcode left join CHIP_WAFERTEST_VIEW on ch_code=chw_chipcode ");
            sql.Append("left join CHIP_EDCDATA_VIEW on che_chipcode=ch_code left join product on pr_code=pd_prodcode left join prodiobarcode on ");
            sql.Append("pib_piid=pi_id and pib_custbarcode=ch_code where pi_inoutno='" + inoutno + "' order by ch_code");
            DataTable dt = (DataTable)dh.ExecuteSql(sql.ToString(), "select");

            for (int i = 0; i < dt.Columns.Count; i++)
            {
                if (dt.Columns[i].ColumnName == (SQL1_.Rows[i]["es_prefix"].ToString() + "||" + SQL1_.Rows[i]["es_filed"].ToString() + "||" + SQL1_.Rows[i]["es_suffix"].ToString()).ToUpper())
                {
                    dt.Columns[i].Caption = SQL1_.Rows[i]["es_filed1"].ToString();
                    dt.Columns[i].ColumnName = SQL1_.Rows[i]["es_caption"].ToString();
                }
            }
            //获取首页需要展示的字段
            DataTable SQL_ = (DataTable)dh.ExecuteSql("select es_filed,es_caption,es_prefix,es_suffix,es_filed1 from CS_EXPORTSETTING where es_type='First' and es_enable=-1 and es_custcode='" + custcode + "' and nvl(es_istitle,0)<>-1 order by es_detno", "select");
            if (SQL_.Rows.Count == 0)
            {
                SQL_ = (DataTable)dh.ExecuteSql("select es_filed,es_caption,es_prefix,es_suffix,es_filed1 from CS_EXPORTSETTING where es_type='First' and es_enable=-1 and es_custcode is null and  nvl(es_istitle,0)<>-1 order by es_detno", "select");
            }
            string es_field = "";
            for (int i = 0; i < SQL_.Rows.Count; i++)
            {
                es_field += SQL_.Rows[i]["es_prefix"].ToString() + "||" + SQL_.Rows[i]["es_filed"].ToString() + "||" + SQL_.Rows[i]["es_suffix"].ToString() + ",";
            }
            //首页信息的DataTable
            sql.Clear();
            sql.Append("select " + es_field.Substring(0, es_field.Length - 1) + "  from FIRSTPAGE_BATCH_VIEW where pi_inoutno='" + inoutno + "'");
            DataTable dt1 = (DataTable)dh.ExecuteSql(sql.ToString(), "select");
            for (int i = 0; i < dt1.Columns.Count; i++)
            {
                if (dt1.Columns[i].ColumnName == (SQL_.Rows[i]["es_prefix"].ToString() + "||" + SQL_.Rows[i]["es_filed"].ToString() + "||" + SQL_.Rows[i]["es_suffix"].ToString()).ToUpper())
                {
                    dt1.Columns[i].Caption = SQL_.Rows[i]["es_filed1"].ToString();
                    dt1.Columns[i].ColumnName = SQL_.Rows[i]["es_caption"].ToString();
                }
            }
            if (FirstPage.DataSource != null)
                FirstPage.DataSource = null;
            if (DataPage.DataSource != null)
                DataPage.DataSource = null;
            FirstPage.DataSource = dt1;
            DataPage.DataSource = dt;
            //将字段赋值到Tag字段，后续保存的时候取值
            for (int i = 0; i < FirstPage.Columns.Count; i++)
            {
                for (int j = 0; j < dt1.Columns.Count; j++)
                {
                    if (FirstPage.Columns[i].Name == dt1.Columns[j].ColumnName)
                    {
                        FirstPage.Columns[i].Tag = dt1.Columns[j].Caption;
                    }
                }
            }

            for (int i = 0; i < DataPage.Columns.Count; i++)
            {
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    if (DataPage.Columns[i].Name == dt.Columns[j].ColumnName)
                    {
                        DataPage.Columns[i].Tag = dt.Columns[j].Caption;
                    }
                }
            }
        }
    }
}