OrigtekMNG/ManagementSystem/InsuranceMaintain.cs

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
using ManagementSystem.Utility;

namespace ManagementSystem
{
    public partial class InsuranceMaintain : Form
    {
        //程式內共用物件
        string strFrmStatus = ""; //表單狀態
        string strActiveUserID = ""; //取得登入作用的使用者帳號
        SqlConnection sqlConn = UtilityClass.GetConn(MainForm.strAccountingBookID);
        SqlCommand sqlCmd = new SqlCommand();
        DataSet sdInsurance = new System.Data.DataSet();
        string strPKey = "";  //程式內的Key值
        string strKey = "";  //程式內加密的Key值
        int intStartCount = 0;

        public InsuranceMaintain()
        {
            InitializeComponent();
        }

        #region 自定義程式
        private void SetupStatus()  //畫面載入設定
        {
            try
            {
                strActiveUserID = MainForm.strActiveUserID;
                strKey = MainForm.strKey;

                if (MainForm.strKey == "")
                {
                    //設定Toolbar初始狀態
                    tsbAdd.Enabled = false;
                    tsbSave.Enabled = false;
                    tsbDelete.Enabled = false;
                }

                //設定畫面初始狀態
                StatusChange("NONE");
            }
            catch (Exception ex)
            {
                ErrorHandler.WriteErrorLog("InsuranceMaintain.cs", ex);
            }
        }

        private void CleanForm() //清除畫面
        {
            //設定畫面物件狀態
            //清除GridView
            string strSQL = "Select GUIDCode,InsuranceLevel,InsuranceAmountStart,InsuranceAmountEnd,C_LaborProtection,S_LaborProtection,C_HealthInsuranceAmount,S_HealthInsuranceAmount,RetirementAmount From OTB_HR_Insurance Order By InsuranceLevel";
            sdInsurance =UtilityClass.GetSQLResult(strSQL);
            dgvDataMaintain.DataSource = sdInsurance.Tables["Result"];
            Application.DoEvents();
            
        }

        private void CleanToolbar()  //清除工具列
        {
            //設定Toolbar狀態
            tsbAdd.Visible = true;
            tsbAdd.Enabled = true;
            tsbEdit.Visible = true;
            tsbEdit.Enabled = true;
            tsbDelete.Visible = true;
            tsbDelete.Enabled = true;
            tsbSave.Visible = false;
            tsbOK.Visible = false;
            tsbCancel.Visible = false;
        }

        private void UnLockForm()  //解除限制唯讀物件
        {
            dgvDataMaintain.Columns["cGUIDCODE"].ReadOnly = false;
            dgvDataMaintain.Columns["cInsuranceLevel"].ReadOnly = false;
            dgvDataMaintain.Columns["cInsuranceAmountStart"].ReadOnly = false;
            dgvDataMaintain.Columns["cInsuranceAmountEnd"].ReadOnly = false;
            dgvDataMaintain.Columns["cC_LaborProtection"].ReadOnly = false;
            dgvDataMaintain.Columns["cS_LaborProtection"].ReadOnly = false;
            dgvDataMaintain.Columns["cC_HealthInsuranceAmount"].ReadOnly = false;
            dgvDataMaintain.Columns["cS_HealthInsuranceAmount"].ReadOnly = false;
            dgvDataMaintain.Columns["cRetirementAmount"].ReadOnly = false;
        }

        private void LockForm()  //限制唯讀物件
        {
            dgvDataMaintain.AllowUserToAddRows = false;
            dgvDataMaintain.Columns["cDelColumn"].ReadOnly = true;
            dgvDataMaintain.Columns["cGUIDCODE"].ReadOnly = true;
            dgvDataMaintain.Columns["cInsuranceLevel"].ReadOnly = true;
            dgvDataMaintain.Columns["cInsuranceAmountStart"].ReadOnly = true;
            dgvDataMaintain.Columns["cInsuranceAmountEnd"].ReadOnly = true;
            dgvDataMaintain.Columns["cC_LaborProtection"].ReadOnly = true;
            dgvDataMaintain.Columns["cS_LaborProtection"].ReadOnly = true;
            dgvDataMaintain.Columns["cC_HealthInsuranceAmount"].ReadOnly = true;
            dgvDataMaintain.Columns["cS_HealthInsuranceAmount"].ReadOnly = true;
            dgvDataMaintain.Columns["cRetirementAmount"].ReadOnly = true;

        }

        private void StatusChange(string strStatus)  //變更主畫面狀態
        {
            switch (strStatus.ToUpper())
            {
                case "NONE":
                    CleanForm();
                    CleanToolbar();
                    LockForm();
                    dgvDataMaintain.AllowUserToAddRows = false;
                    ((MainForm)ParentForm).SsStatus.Items["tsslStatus"].Text = "";
                    strFrmStatus = "";
                    intStartCount = dgvDataMaintain.Rows.Count;
                    break;
                case "SEARCH":
                    UnLockForm();
                    dgvDataMaintain.DataSource = null;
                    Application.DoEvents();
                    tsbSearch.Visible = false;
                    tsbOK.Visible = true;
                    tsbCancel.Visible = true;
                    ((MainForm)ParentForm).SsStatus.Items["tsslStatus"].Text = "搜尋";
                    strFrmStatus = "SEARCH";
                    break;
                case "ADD":
                    UnLockForm();
                    dgvDataMaintain.AllowUserToAddRows = true;
                    tsbAdd.Visible = false;
                    tsbEdit.Enabled = false;
                    tsbDelete.Enabled = false;
                    tsbSave.Visible = true;
                    tsbCancel.Visible = true;
                    ((MainForm)ParentForm).SsStatus.Items["tsslStatus"].Text = "新增";
                    strFrmStatus = "ADD";
                    break;
                case "MODIFY":
                    UnLockForm();
                    tsbEdit.Visible = false;
                    tsbAdd.Enabled = false;
                    tsbDelete.Enabled = false;
                    tsbSave.Visible = true;
                    tsbCancel.Visible = true;
                    ((MainForm)ParentForm).SsStatus.Items["tsslStatus"].Text = "修改";
                    strFrmStatus = "MODIFY";
                    break;
                case "DEL":
                    dgvDataMaintain.Columns["cDelColumn"].ReadOnly = false;
                    tsbAdd.Enabled = false;
                    tsbEdit.Enabled = false;
                    tsbOK.Visible = true;
                    tsbCancel.Visible = true;
                    tsbDelete.Visible = false;
                    ((MainForm)ParentForm).SsStatus.Items["tsslStatus"].Text = "刪除";
                    strFrmStatus = "DEL";
                    break;
            }
            UtilityClass.SetGridColor(dgvDataMaintain);
        }

        private bool CheckDel()  //確認是否有資料被勾選刪除
        {
            foreach (DataGridViewRow row in dgvDataMaintain.Rows)
            {
                DataGridViewCheckBoxCell Delcell = (DataGridViewCheckBoxCell)row.Cells["cDelColumn"];  //刪除狀態列
                DataGridViewTextBoxCell Keycell = (DataGridViewTextBoxCell)row.Cells["cGUIDCode"];   //需刪除的Key值
                if (Delcell.Value != null)
                {
                    if (Delcell.Value == Delcell.TrueValue)
                    {
                        return true;
                    }
                }
            }
            return false;
        }

        private void DelEven()  //刪除事件
        {
            try
            {
                //宣告物件
                string strDelItem = "";
                if (CheckDel())
                {
                    if (MessageBox.Show("請問您確定要刪除本資料?", "提示", MessageBoxButtons.YesNo) == DialogResult.Yes)
                    {
                        foreach (DataGridViewRow row in dgvDataMaintain.Rows)
                        {
                            DataGridViewCheckBoxCell Delcell = (DataGridViewCheckBoxCell)row.Cells["cDelColumn"];  //刪除狀態列
                            DataGridViewTextBoxCell Keycell = (DataGridViewTextBoxCell)row.Cells["cGUIDCode"];   //需刪除的Key值
                            if (Delcell.Value != null)
                            {
                                if (Delcell.Value == Delcell.TrueValue)
                                {
                                    if (strDelItem == "")
                                    { strDelItem = "'" + Keycell.Value.ToString() + "'"; }
                                    else
                                    { strDelItem += ",'" + Keycell.Value.ToString() + "'"; }
                                }
                            }
                        }
                        string strDelSQL = string.Format("Delete OTB_HR_Insurance Where GUIDCode in ({0})", strDelItem);

                        using (SqlDataAdapter sqlAdapter = new SqlDataAdapter())
                        {
                            //添加參數
                            sqlAdapter.DeleteCommand = new SqlCommand();
                            sqlAdapter.DeleteCommand.Connection = sqlConn;
                            sqlAdapter.DeleteCommand.CommandText = strDelSQL.ToString();
                                
                            if (sqlConn.State == ConnectionState.Closed)  //判斷連線狀態
                            {
                                sqlConn.Open();
                            }

                            sqlAdapter.DeleteCommand.ExecuteNonQuery();
                            MessageBox.Show("資料刪除成功", "提示");
                        }
                    }
                }
                else
                {
                    //未選取要刪除的資料
                    MessageBox.Show("請先選取欲刪除的資料", "提示");
                }
            }
            catch (Exception ex)
            {
                ErrorHandler.WriteErrorLog("InsuranceMaintain.cs", ex);
            }
        }

        private void AddEven()  //新增事件
        {
            try
            {
                //宣告物件
                int intDataCount = dgvDataMaintain.Rows.Count - 1;
                StringBuilder strSQL = new StringBuilder();
                for (int intCount = intStartCount; intCount < intDataCount; intCount++)
                {
                    if (!dgvDataMaintain.Rows[intCount].IsNewRow)
                    {
                        strSQL.Append("Insert Into OTB_HR_Insurance(GUIDCode,InsuranceLevel,InsuranceAmountStart,InsuranceAmountEnd,C_LaborProtection,S_LaborProtection,C_HealthInsuranceAmount,S_HealthInsuranceAmount,RetirementAmount,CreateDate,CreateUser,ModifyDate,ModifyUser)");
                        strSQL.Append("Values (NEWID(),");
                        strSQL.Append(dgvDataMaintain.Rows[intCount].Cells["cInsuranceLevel"].Value.ToString() + ",");
                        strSQL.Append(dgvDataMaintain.Rows[intCount].Cells["cInsuranceAmountStart"].Value.ToString() + ",");
                        strSQL.Append(dgvDataMaintain.Rows[intCount].Cells["cInsuranceAmountEnd"].Value.ToString() + ",");
                        strSQL.Append(dgvDataMaintain.Rows[intCount].Cells["cC_LaborProtection"].Value.ToString() + ",");
                        strSQL.Append(dgvDataMaintain.Rows[intCount].Cells["cS_LaborProtection"].Value.ToString() + ",");
                        strSQL.Append(dgvDataMaintain.Rows[intCount].Cells["cC_HealthInsuranceAmount"].Value.ToString() + ",");
                        strSQL.Append(dgvDataMaintain.Rows[intCount].Cells["cS_HealthInsuranceAmount"].Value.ToString() + ",");
                        strSQL.Append(dgvDataMaintain.Rows[intCount].Cells["cRetirementAmount"].Value.ToString() + ",");
                        strSQL.Append("Getdate(),'" + strActiveUserID + "',Getdate(),'" + strActiveUserID + "') ");
                    }
                }
                
                using (SqlDataAdapter sqlAdapter = new SqlDataAdapter())
                {
                    //添加參數
                    sqlAdapter.InsertCommand = new SqlCommand();
                    sqlAdapter.InsertCommand.Connection = sqlConn;
                    sqlAdapter.InsertCommand.CommandText = strSQL.ToString();

                    if (sqlConn.State == ConnectionState.Closed)  //判斷連線狀態
                    {
                        sqlConn.Open();
                    }

                    sqlAdapter.InsertCommand.ExecuteNonQuery();
                    MessageBox.Show("資料新增成功", "提示");
                    StatusChange("NONE");

                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("資料新增失敗", "提示");
                ErrorHandler.WriteErrorLog("InsuranceMaintain.cs", ex);
            }

        }

        private void EditEven()  //修改事件
        {
            try
            {
                //宣告物件
                StringBuilder sbSQL = new StringBuilder();

                foreach (DataGridViewRow row in dgvDataMaintain.Rows)
                {
                    DataGridViewCheckBoxCell Updcell = (DataGridViewCheckBoxCell)row.Cells["cUPDColumn"];  //修改狀態列
                    DataGridViewTextBoxCell Keycell = (DataGridViewTextBoxCell)row.Cells["cGUIDCode"];   //需修改的Key值
                    if (Updcell.Value != null)
                    {
                        if (Updcell.Value == Updcell.TrueValue)
                        {
                            sbSQL.Append(" Update OTB_HR_Insurance Set ");
                            sbSQL.Append(" InsuranceLevel = " + row.Cells["cInsuranceLevel"].Value.ToString());
                            sbSQL.Append(" ,InsuranceAmountStart = " + row.Cells["cInsuranceAmountStart"].Value.ToString());
                            sbSQL.Append(" ,InsuranceAmountEnd = " + row.Cells["cInsuranceAmountEnd"].Value.ToString());
                            sbSQL.Append(" ,C_LaborProtection = " + row.Cells["cC_LaborProtection"].Value.ToString());
                            sbSQL.Append(" ,S_LaborProtection = " + row.Cells["cS_LaborProtection"].Value.ToString());
                            sbSQL.Append(" ,C_HealthInsuranceAmount = " + row.Cells["cC_HealthInsuranceAmount"].Value.ToString());
                            sbSQL.Append(" ,S_HealthInsuranceAmount = " + row.Cells["cS_HealthInsuranceAmount"].Value.ToString());
                            sbSQL.Append(" ,RetirementAmount = " + row.Cells["cRetirementAmount"].Value.ToString());
                            sbSQL.Append(" ,ModifyDate = Getdate(), ModifyUser ='" + strActiveUserID + "'");
                            sbSQL.Append(" Where GUIDCode ='" + Keycell.Value.ToString() + "'");
                        }
                    }
                }

                if (sbSQL.ToString().Trim() != "")
                {

                    using (SqlDataAdapter sqlAdapter = new SqlDataAdapter())
                    {
                        //添加參數
                        sqlAdapter.UpdateCommand = new SqlCommand();
                        sqlAdapter.UpdateCommand.Connection = sqlConn;
                        sqlAdapter.UpdateCommand.CommandText = sbSQL.ToString();

                        if (sqlConn.State == ConnectionState.Closed)  //判斷連線狀態
                        {
                            sqlConn.Open();
                        }

                        sqlAdapter.UpdateCommand.ExecuteNonQuery();
                        StatusChange("NONE");

                    }
                    
                    MessageBox.Show("資料修改成功", "提示");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("資料修改失敗", "提示");
                ErrorHandler.WriteErrorLog("InsuranceMaintain.cs", ex);
            }
        }

        private void ShowUpdateDate(string strGUID) // 呈現最新修改人
        {
            //排除空值
            if (strGUID.Trim() == "")
            {
                return;
            }
            if (strGUID == null)
            {
                return;
            }

            strPKey = strGUID;
            string strSQL = "Select ModifyDate, ModifyUser From OTB_HR_Insurance Where GUIDCode = '" + strPKey + "'";
            using (SqlDataAdapter sqlAdapter = new SqlDataAdapter())
            {
                //添加參數
                sqlAdapter.SelectCommand = new SqlCommand();
                sqlAdapter.SelectCommand.Connection = sqlConn;
                sqlAdapter.SelectCommand.CommandText = strSQL;

                if (sqlConn.State == ConnectionState.Closed)  //判斷連線狀態
                {
                    sqlConn.Open();
                }

                //進行查詢
                DataTable dtTemp = UtilityClass.GetSQLResult(strSQL).Tables["Result"];
                ((MainForm)ParentForm).SsStatus.Items["tsslModifyUser"].Text = dtTemp.Rows[0]["ModifyUser"].ToString().Trim();
                ((MainForm)ParentForm).SsStatus.Items["tsslModifyDate"].Text = dtTemp.Rows[0]["ModifyDate"].ToString().Trim();
            }
            UtilityClass.SetGridColor(dgvDataMaintain);
        }
                
        #endregion

        #region 事件觸發及問題處理
        private void InsuranceMaintain_Load(object sender, EventArgs e)
        {
            SetupStatus();
        }

        private void tsbEdit_Click(object sender, EventArgs e)
        {
            StatusChange("NONE");
            StatusChange("MODIFY");
        }

        private void tsbCancel_Click(object sender, EventArgs e)
        {
            StatusChange("NONE");
        }

        private void tsbAdd_Click(object sender, EventArgs e)
        {
            StatusChange("NONE");
            StatusChange("ADD");
        }

        private void tsbDelete_Click(object sender, EventArgs e)
        {
            StatusChange("DEL");
        }

        private void tsbOK_Click(object sender, EventArgs e)
        {
            try
            {
                StringBuilder strSQL = new StringBuilder("");
                switch (strFrmStatus.ToString())
                {
                    case "DEL": //刪除
                        DelEven();
                        StatusChange("NONE");
                        break;
                }
                LockForm();
            }
            catch (Exception ex)
            {
                ErrorHandler.WriteErrorLog("InsuranceMaintain.cs", ex);
            }
        }

        private void tsbSearch_Click(object sender, EventArgs e)
        {
            StatusChange("SEARCH");
        }

        private void tsbSave_Click(object sender, EventArgs e)
        {
            try
            {
                StringBuilder strSQL = new StringBuilder("");
                switch (strFrmStatus.ToString())
                {
                     case "ADD": //新增
                        AddEven();
                        break;

                    case "MODIFY": //修改
                        EditEven();
                        break;
                }
                LockForm();
            }
            catch (Exception ex)
            {
                ErrorHandler.WriteErrorLog("InsuranceMaintain.cs", ex);
            }
        }

        private void dgvDataMaintain_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            ShowUpdateDate(dgvDataMaintain.CurrentRow.Cells["cGUIDCode"].Value.ToString());  //顯示最新修改資料
        }

        private void dgvDataMaintain_CellEnter(object sender, DataGridViewCellEventArgs e)
        {
            ShowUpdateDate(dgvDataMaintain.CurrentRow.Cells["cGUIDCode"].Value.ToString());  //顯示最新修改資料
        }

        private void tsbExit_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void dgvDataMaintain_CellValueChanged(object sender, DataGridViewCellEventArgs e)
        {
            if (dgvDataMaintain.CurrentRow != null)
            {
                DataGridViewCheckBoxCell UPDcell = (DataGridViewCheckBoxCell)dgvDataMaintain.CurrentRow.Cells["cUPDColumn"];   //被修改的Key值
                UPDcell.Value = UPDcell.TrueValue;
            }
        }

        private void tsbClean_Click(object sender, EventArgs e)
        {
            CleanForm();
        }
        #endregion

        

        

        

        


    }
}