MES 20210811 강사님 버전

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Common;

using DC00_assm;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using Infragistics.Win;
using Infragistics.Win.UltraWinEditors;
using Infragistics.Win.UltraWinGrid;


namespace DC_Form
{
    public partial class BM_WorkerMaster : DC00_WinForm.BaseMDIChildForm
    {
        private DataTable dtDeptCOde = new DataTable();
        public BM_WorkerMaster()
        {
            InitializeComponent();
        }

        private void BM_WorkerMaster_Load(object sender, EventArgs e)
        {
            try
            {
                // 그리드 셋팅 
                // 1. 공장 컬럼 그리드에 셋팅
                // 1. 컬럼의 명칭과 이름 등록
                grid1.DisplayLayout.Bands[0].Columns.Add("PLANTCODE", "공장");
                // 2. 컬럼의 형식 설정
                grid1.DisplayLayout.Bands[0].Columns["PLANTCODE"].DataType = typeof(string);
                // 3. 컬럼의 넓이 설정
                grid1.DisplayLayout.Bands[0].Columns["PLANTCODE"].Width = 130;
                // 4. 컬럼의 정렬 설정
                grid1.DisplayLayout.Bands[0].Columns["PLANTCODE"].Header.Appearance.TextHAlign = HAlign.Center;
                grid1.DisplayLayout.Bands[0].Columns["PLANTCODE"].CellAppearance.TextHAlign    = HAlign.Left;
                // 5. 컬럼 표시 여부 설정. 
                grid1.DisplayLayout.Bands[0].Columns["PLANTCODE"].Hidden = false;
                // 6. 컬럼 수정 여부 설정.
                grid1.DisplayLayout.Bands[0].Columns["PLANTCODE"].CellActivation = Infragistics.Win.UltraWinGrid.Activation.NoEdit;

                // 공장 컬럼 이외의 그리드 컬럼 셋팅.
                InitColumnUltraGrid("WORKERID",   "작업자ID",  GridColDataType_emu.VarChar,  90, Infragistics.Win.HAlign.Left, true, true);
                InitColumnUltraGrid("WORKERNAME", "작업자명",  GridColDataType_emu.VarChar,  90, Infragistics.Win.HAlign.Left, true, true);
                InitColumnUltraGrid("BANCODE",    "작업반",    GridColDataType_emu.VarChar, 140, Infragistics.Win.HAlign.Left, true, true);
                InitColumnUltraGrid("GRPIDE",     "그룹ID",    GridColDataType_emu.VarChar, 140, Infragistics.Win.HAlign.Left, true, true);
                InitColumnUltraGrid("DEPTCODE",   "부서",      GridColDataType_emu.VarChar, 140, Infragistics.Win.HAlign.Left, true, true);
                InitColumnUltraGrid("PHONENO",    "연락처",    GridColDataType_emu.VarChar,  90, Infragistics.Win.HAlign.Left, true, true);
                InitColumnUltraGrid("INDATE",     "입사일자",  GridColDataType_emu.VarChar,  90, Infragistics.Win.HAlign.Left, true, true);
                InitColumnUltraGrid("OUTDATE",    "퇴사일자",  GridColDataType_emu.VarChar,  90, Infragistics.Win.HAlign.Left, true, true);
                InitColumnUltraGrid("USEFLAG",    "사용여부",  GridColDataType_emu.VarChar,  90, Infragistics.Win.HAlign.Left, true, true);
                InitColumnUltraGrid("MAKER",      "등록자",    GridColDataType_emu.VarChar,  90, Infragistics.Win.HAlign.Left, true, true);
                InitColumnUltraGrid("MAKEDATE",   "등록일시",  GridColDataType_emu.VarChar, 150, Infragistics.Win.HAlign.Left, true, true);
                InitColumnUltraGrid("EDITOR",     "수정자",    GridColDataType_emu.VarChar,  90, Infragistics.Win.HAlign.Left, true, true);
                InitColumnUltraGrid("EDITDATE",   "수정일시",  GridColDataType_emu.VarChar, 150, Infragistics.Win.HAlign.Left, true, true);


                /////////////////////// 콤보 박스 구성 /////////////////////////////////

                // 1. 데이터 베이스 주소
                string Constr = "Data Source = 222.235.141.8; " +
                                "Initial Catalog = GNU_MES_2021; " +
                                "User ID = gnumes ; PassWord = 1122";

                // 2.MSSQL DB 로의 접속 경로 설정
                SqlConnection connect = new SqlConnection(Constr);
                connect.Open();

                // 3. 부서 콤보박스 데이터 DB 에서 조회 할 SQL 구문
                string sSql = "";
                sSql = sSql + " SELECT MINORCODE                          AS CODE_ID,";
                sSql = sSql + "       '[' + MINORCODE + '] ' + CODENAME   AS CODE_NAME";
                sSql = sSql + " FROM TB_Standard WITH(NOLOCK)";
                sSql = sSql + " WHERE MAJORCODE = 'DEPTCODE'";
                sSql = sSql + " AND MINORCODE<> '$'";

                // 4. MSSQL 에서 데이터 조회
                SqlDataAdapter Adapter = new SqlDataAdapter(sSql, connect); 
                Adapter.Fill(dtDeptCOde);
                connect.Close();

                // 5. 가져온 DB 의 데이터를 콤보박스에 등록
                dtDeptCOde.Rows.Add("", "ALL");
                // 5-1 콤보박스에 등록될 데이터 매칭
                cboDeptCode.DataSource    = dtDeptCOde;
                cboDeptCode.ValueMember   = "CODE_ID";
                cboDeptCode.DisplayMember = "CODE_NAME";

                //// 가져온 부서 데이터를 그리드에 매핑(셋팅, 대입)
                ValueList list = new ValueList(); // 그리드의 컬럼에 리스트 형식으로 추가 할 수 있는 클래스
                try
                {
                    if (dtDeptCOde.Rows.Count != 0)
                    {
                        // 부서 데이터를 그리드에 매핑한다.
                        for (int i = 0; i < dtDeptCOde.Rows.Count; i++)
                        {
                            list.ValueListItems.Add(Convert.ToString(dtDeptCOde.Rows[i]["CODE_ID"]),
                                                    Convert.ToString(dtDeptCOde.Rows[i]["CODE_NAME"]));
                        }
                        // 부서(DEPTCODE) 컬럼에 콤보박스 형식의 버튼을 부여하는데, 마우스를 가져다 댔을때만 버튼이 표시된다.
                        grid1.DisplayLayout.Bands[0].Columns["DEPTCODE"].ButtonDisplayStyle =
                                Infragistics.Win.UltraWinGrid.ButtonDisplayStyle.OnRowActivate;
                        grid1.DisplayLayout.Bands[0].Columns["DEPTCODE"].Style =
                                Infragistics.Win.UltraWinGrid.ColumnStyle.DropDownList;
                        // list 에 등록한 부서 데이터를 그리드 DEPTCODE 컬럼에 매핑 한다.
                        grid1.DisplayLayout.Bands[0].Columns["DEPTCODE"].ValueList = list;
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                }


                // 공장 에 대한 데이터 콤보박스에 매핑 (함수를 사용)
                DataTable dtPlantCode = new DataTable();
                dtPlantCode = Standard_Code("PLANTCODE");
                SetComboBoxMaster(cboPlantCode,
                                  dtPlantCode,
                                  dtPlantCode.Columns["CODE_ID"].ColumnName,
                                  dtPlantCode.Columns["CODE_NAME"].ColumnName,
                                  "ALL", "");

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }
        private void InitColumnUltraGrid(string Culumnname, string Caption, GridColDataType_emu Coltype,
                                         int iWidth, HAlign Halign, bool visible, bool editable)
        {
            // 1. 컬럼의 명칭과 이름 등록
            grid1.DisplayLayout.Bands[0].Columns.Add(Culumnname, Caption);
            // 2. 컬럼의 형식 설정
            grid1.DisplayLayout.Bands[0].Columns[Culumnname].DataType = typeof(string);
            // 3. 컬럼의 넓이 설정
            grid1.DisplayLayout.Bands[0].Columns[Culumnname].Width = iWidth;
            // 4. 컬럼의 정렬 설정
            grid1.DisplayLayout.Bands[0].Columns[Culumnname].Header.Appearance.TextHAlign = HAlign.Center;
            grid1.DisplayLayout.Bands[0].Columns[Culumnname].CellAppearance.TextHAlign    = Halign;
            // 5. 컬럼 표시 여부 설정. 
            grid1.DisplayLayout.Bands[0].Columns[Culumnname].Hidden = !visible;
            if (editable == true)
            {
                // 6. 컬럼 수정 여부 설정.
                grid1.DisplayLayout.Bands[0].Columns[Culumnname].CellActivation = Infragistics.Win.UltraWinGrid.Activation.AllowEdit;
            }
            else
                grid1.DisplayLayout.Bands[0].Columns[Culumnname].CellActivation = Infragistics.Win.UltraWinGrid.Activation.NoEdit;
        }

        private DataTable Standard_Code(string sMajorCode)
        {
            // 1. 데이터 베이스 주소
            string Constr = "Data Source = 222.235.141.8; " +
                            "Initial Catalog = GNU_MES_2021; " +
                            "User ID = gnumes ; PassWord = 1122";

            // 2.MSSQL DB 로의 접속 경로 설정
            SqlConnection connect = new SqlConnection(Constr);
            connect.Open();

            // 3. 부서 콤보박스 데이터 DB 에서 조회 할 SQL 구문
            string sSql = "";
            sSql = sSql + " SELECT MINORCODE                          AS CODE_ID,";
            sSql = sSql + "       '[' + MINORCODE + '] ' + CODENAME   AS CODE_NAME";
            sSql = sSql + " FROM TB_Standard WITH(NOLOCK)";
            sSql = sSql + $" WHERE MAJORCODE = '{sMajorCode}'";
            //sSql = sSql + " WHERE MAJORCODE = '" + sMajorCode + "'";
            sSql = sSql + " AND MINORCODE<> '$'";

            // 4. MSSQL 에서 데이터 조회
            SqlDataAdapter Adapter = new SqlDataAdapter(sSql, connect);
            DataTable rtnDtTemp = new DataTable();
            Adapter.Fill(rtnDtTemp);
            connect.Close();
            return rtnDtTemp; // 데이터 테이블 을 반환 한다.
        }
        private void SetComboBoxMaster(UltraComboEditor Combobox, 
                                       DataTable dtTable, 
                                       string ValueMember, 
                                       string DisplayMember, 
                                       string AllDisplay, 
                                       string AllValue)
        {
            // 5. 가져온 DB 의 데이터를 콤보박스에 등록
            dtTable.Rows.Add(AllValue, AllDisplay);
            // 5-1 콤보박스에 등록될 데이터 매칭
            Combobox.DataSource    = dtTable;
            Combobox.ValueMember   = ValueMember;
            Combobox.DisplayMember = DisplayMember;
        }
        private void SetComboUltraGrid(UltraGrid Gird, string ColumnName, DataTable dtTemp,string ValueMember, string Displaymember)
        {
            //// 가져온 부서 데이터를 그리드에 매핑(셋팅, 대입)
            ValueList list = new ValueList(); // 그리드의 컬럼에 리스트 형식으로 추가 할 수 있는 클래스
            try
            {
                if (dtTemp.Rows.Count != 0)
                {
                    // 부서 데이터를 그리드에 매핑한다.
                    for (int i = 0; i < dtTemp.Rows.Count; i++)
                    {
                        list.ValueListItems.Add(Convert.ToString(dtTemp.Rows[i]["CODE_ID"]),
                                                Convert.ToString(dtTemp.Rows[i]["CODE_NAME"]));
                    }
                    // 부서(DEPTCODE) 컬럼에 콤보박스 형식의 버튼을 부여하는데, 마우스를 가져다 댔을때만 버튼이 표시된다.
                    grid1.DisplayLayout.Bands[0].Columns["DEPTCODE"].ButtonDisplayStyle =
                            Infragistics.Win.UltraWinGrid.ButtonDisplayStyle.OnRowActivate;
                    grid1.DisplayLayout.Bands[0].Columns["DEPTCODE"].Style =
                            Infragistics.Win.UltraWinGrid.ColumnStyle.DropDownList;
                    // list 에 등록한 부서 데이터를 그리드 DEPTCODE 컬럼에 매핑 한다.
                    grid1.DisplayLayout.Bands[0].Columns["DEPTCODE"].ValueList = list;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }
        
        public override void DoInquire()
        {
            // 작업자 마스터 조회
            //try
            //{
            //    // Database 접속 주소
            //    string Constr = "Data Source = 222.235.141.8; " +
            //                "Initial Catalog = GNU_MES_2021; " +
            //                "User ID = gnumes ; PassWord = 1122";

            //    // 데이터 팩토리 유형 설정 
            //    DbProviderFactory dataFactory = DbProviderFactories.GetFactory("System.Data.SqlClient");
            //    DbConnection _Sconn = null; // 데이터 베이스 접속 환경 

            //    _Sconn = dataFactory.CreateConnection(); // MSSQL 로 접속 한다.
            //    _Sconn.ConnectionString = Constr;        // 접속 주소로 접속 한다.
            //    _Sconn.Open();                           // DB OPEN

            //    // 조회 조건 변수 선언 및 값 등록
            //    string sPlantCode = Convert.ToString(cboPlantCode.Value); // 공장 
            //    string sDtptCode = Convert.ToString(cboDeptCode.Value);  // 부서
            //    string sWorkerId = Convert.ToString(txtWorkerID.Text);   // 작업자 id
            //    string sWorkerName = Convert.ToString(txtWorkerName.Text); // 작업자 명

            //    // 데이터 베이스 호출 명령어 설정
            //    DbCommand oCmd = _Sconn.CreateCommand();        // 접속 환경에 따른 데이터 베이스 명령 정보 객체 선언
            //    oCmd.Connection = _Sconn;                       // 명령 내용이 수행 할 접속 경로
            //    oCmd.CommandType = CommandType.StoredProcedure; // 질의문 수행 방식(저장프로시저)
            //    oCmd.CommandText = "BM_WorkerMaster_B_S1";      // 저장 프로시저 이름 

            //    string RSCODE = "S";          // 프로시저 성공 여부 
            //    string RSMSG = string.Empty; // 프로시저 수행 메세지

            //    // 저장 프로시저에 던져줄 파라매터 구성
            //    DbParameter Param = dataFactory.CreateParameter();
            //    Param.ParameterName = "PLANTCODE";
            //    Param.Value = sPlantCode;
            //    Param.Direction = ParameterDirection.Input;
            //    oCmd.Parameters.Add(Param);

            //    Param = dataFactory.CreateParameter();
            //    Param.ParameterName = "WORKERID";
            //    Param.Value = sWorkerId;
            //    Param.Direction = ParameterDirection.Input;
            //    oCmd.Parameters.Add(Param);

            //    Param = dataFactory.CreateParameter();
            //    Param.ParameterName = "WORKERNAME";
            //    Param.Value = sWorkerName;
            //    Param.Direction = ParameterDirection.Input;
            //    oCmd.Parameters.Add(Param);

            //    Param = dataFactory.CreateParameter();
            //    Param.ParameterName = "DEPTCODE";
            //    Param.Value = sDtptCode;
            //    Param.Direction = ParameterDirection.Input;
            //    oCmd.Parameters.Add(Param);
            //    //////////////////////////////////////////////////////////////
            //    Param = dataFactory.CreateParameter();
            //    Param.ParameterName = "LANG";
            //    Param.Value = "KO";
            //    Param.Direction = ParameterDirection.Input;
            //    oCmd.Parameters.Add(Param);

            //    Param = dataFactory.CreateParameter();
            //    Param.ParameterName = "RS_CODE";
            //    Param.Size = 1;
            //    Param.Direction = ParameterDirection.Output;
            //    oCmd.Parameters.Add(Param);

            //    Param = dataFactory.CreateParameter();
            //    Param.ParameterName = "RS_MSG";
            //    Param.Size = 200;
            //    Param.Direction = ParameterDirection.Output;
            //    oCmd.Parameters.Add(Param);

            //    // 질의 문 (저장 프로시저) 실행 후 조회 된 내용 받아올 객체 선언
            //    DbDataAdapter oAdap = dataFactory.CreateDataAdapter();
            //    oAdap.SelectCommand = oCmd;  // DB 연결자의 수행 명령 내용 등록

            //    DataTable dtTable = new DataTable();
            //    oAdap.Fill(dtTable); // 연결자 (Adapter) 가 최종 DB 명령 수행 후 데이터 테이블에 등록

            //    _Sconn.Close();

            //    grid1.DataSource = dtTable;
            //}
            //catch (Exception ex)
            //{
            //}

            // DB HELPER 함수를 이용하여 저장 프로시저 조회
            
            // DB OPEN 트랜잭션을 사용하지 않겠다.
            DBHelper helper = new DBHelper("",false);

            try
            {
                // 조회 조건 변수 선언 및 값 등록
                string sPlantCode   = Convert.ToString(cboPlantCode.Value);
                string sDeptCode    = Convert.ToString(cboDeptCode.Value);
                string sWorkerID    = txtWorkerID.Text.ToString();
                string sWorkerName  = Convert.ToString(txtWorkerName.Text);

                DataTable dtTable = new DataTable();
                dtTable = helper.FillTable("BM_WorkerMaster_B_S1", CommandType.StoredProcedure,
                                            helper.CreateParameter("PLANTCODE",  sPlantCode,  DbType.String, ParameterDirection.Input),
                                            helper.CreateParameter("DEPTCODE",   sDeptCode,   DbType.String, ParameterDirection.Input),
                                            helper.CreateParameter("WORKERID",   sWorkerID,   DbType.String, ParameterDirection.Input),
                                            helper.CreateParameter("WORKERNAME", sWorkerName, DbType.String, ParameterDirection.Input));
                grid1.DataSource = dtTable;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
            finally
            {
                helper.Close();
            }
        }
    }
}