OrigtekMNG/ManagementSystem/CashFlowForecast.cs

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

namespace ManagementSystem
{
    public partial class CashFlowForecast : Form
    {
        //程式內共用物件
        public string strFrmStatus = ""; //表單狀態
        public string strPKey = "";  //程式內的Key值
        public string strOwnerForm = "";   //呼叫的Form Name
        public string strKey = "";  //程式內加密的Key值
        public string strActiveUserID = ""; //取得登入作用的使用者帳號
        public string strAccountingBookID = "";  //取得作用中的帳本
        SqlConnection sqlConn = UtilityClass.GetConn(MainForm.strAccountingBookID);
        SqlTransaction sqlTran;
        SqlDataAdapter sqlAdapter = new SqlDataAdapter();
        SqlCommand sqlCmd = new SqlCommand();
        int intStartCount = 0;  //當下Gridview所有的資料筆數
        Int32 intAssetsCount = 0;  //記錄本次資金餘額

        public CashFlowForecast()
        {
            InitializeComponent();
        }

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

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

                //設定畫面初始狀態
                StatusChange("NONE");
                LoadAssets();
                LoadRest();
                UtilityClass.SetGridColor(dgvCashFlow);
            }
            catch (Exception ex)
            {
                ErrorHandler.WriteErrorLog("CashFlowForecast.cs", ex);
            }
        }

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

        private void UnLockForm()  //解除限制唯讀物件
        {
            dgvCashFlow.ReadOnly = false;
        }

        private void LockForm()  //限制唯讀物件
        {
            dgvCashFlow.ReadOnly = true;
        }

        private void SearchEvent()  //查詢事件
        {            
        }

        private void EditEven()  //修改事件
        { 

        }

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

        private int CheckSelected()
        {
            int intCheckCount = 0;
            foreach (DataGridViewRow row in dgvCashFlow.Rows)
            {
                DataGridViewCheckBoxCell Selcell = (DataGridViewCheckBoxCell)row.Cells["cDel"];  //選擇狀態列
                if (Selcell.Value != null)
                {
                    if (Selcell.Value == Selcell.TrueValue)
                    {
                        intCheckCount += 1;
                    }
                }
            }
            return intCheckCount;
        }

        private void GoDel()
        {
            if (CheckSelected() == 0)
            {
                if (MessageBox.Show("刪除的資料將無法還原，請問您是否繼續刪除的動作?", "提示", MessageBoxButtons.YesNo) == DialogResult.Yes)
                {
                    //進行刪除的動作
                }
            }
            else
            {
                MessageBox.Show("請先選擇要刪除的資料!", "提示");
            }            
        }
                
        private Int32 CountAssets(string strAccountingSubjectID)
        {
            try
            {
                Int32 intCount = 0;
                string strDebit = "";
                string strCredit = "";
                string strAssetsList = "Select AccountingID, AccountingDate, AccountingOrder, Debit, Credit, ProjectNumber, ProjectCName, Memo From OTB_FNC_AccountingJournal Where AccountingBookID = '" + strAccountingBookID + "' And AccountingID like '" + DateTime.Now.Year.ToString() + "%' And AccountingSubID ='" + strAccountingSubjectID + "' Order by AccountingID ASC";
                DataTable dtTemp = UtilityClass.GetSQLResult(strAssetsList).Tables["Result"];
                foreach (DataRow dr in dtTemp.Rows)
                { 
                    strDebit = UtilityClass.DecryptDES(dr["Debit"].ToString(),strKey).Replace(",","");
                    if (strDebit == "") strDebit = "0";
                    strCredit = UtilityClass.DecryptDES(dr["Credit"].ToString(),strKey).Replace(",","");
                    if (strCredit == "") strCredit = "0";
                    intCount = intCount + Convert.ToInt32(strDebit) - Convert.ToInt32(strCredit);
                }
                return intCount;
            }
            catch
            {
                return 0;
            }
        }

        private void LoadAssets()
        {
            try
            {
                intAssetsCount = 0;
                string strXMLPath = ConfigurationManager.AppSettings["XMLFilePath"].ToString() + this.Name.ToString() + ".xml";
                if (File.Exists(strXMLPath))
                {
                    XDocument xmlContent = XDocument.Load(strXMLPath);
                    foreach (XElement xmlData in xmlContent.Descendants("Accounting"))
                    {
                        intAssetsCount += CountAssets(xmlData.Element("SubAccounting").Value.ToString());
                    }
                }

                //建立最新餘額資料
                DataGridViewRow dgvRow = new DataGridViewRow();
                dgvRow.CreateCells(dgvCashFlow);
                dgvRow.ReadOnly = true;
                dgvRow.Cells[1].Value = DateTime.Now.ToShortDateString();     //預估日期
                dgvRow.Cells[4].Value = "實際餘額";     
                dgvRow.Cells[7].Value = UtilityClass.MarkNumber(intAssetsCount.ToString());  //實際餘額
                dgvCashFlow.Rows.Add(dgvRow);
                
            }
            catch (Exception ex)
            {
                ErrorHandler.WriteErrorLog("CashFlowForecast.cs", ex);
            }
        }

        private void LoadRest()  //整理餘額資料
        {
            try
            {
                string strIncome = "";
                string strExpand = "";
                string strSQL = "Select * From OTB_FNC_CashFlowForecast Where AccountingBookID = '" + strAccountingBookID + "' Order by ForecastDate";
                DataTable dtTemp = UtilityClass.GetSQLResult(strSQL).Tables["Result"];
                if (dtTemp.Rows.Count > 0)
                {
                    foreach (DataRow dr in dtTemp.Rows)
                    {
                        strIncome = "";
                        strExpand = "";
                        DataGridViewRow dgvForcastRow = new DataGridViewRow();
                        dgvForcastRow.CreateCells(dgvCashFlow);
                        dgvForcastRow.Cells[1].Value = DateTime.Parse(dr["ForecastDate"].ToString()).ToShortDateString(); ;     //預估日期
                        dgvForcastRow.Cells[2].Value = dr["CustomerName"].ToString();     //客戶名稱
                        dgvForcastRow.Cells[3].Value = dr["InvoiceNo"].ToString();     //發票號碼
                        dgvForcastRow.Cells[4].Value = dr["Memo"].ToString();     //備註
                        strIncome = string.IsNullOrEmpty(dr["ForecastIncome"].ToString()) ? "" : UtilityClass.DecryptDES(dr["ForecastIncome"].ToString(), strKey);     //預估收入
                        strExpand = string.IsNullOrEmpty(dr["ForecastExpand"].ToString()) ? "" : UtilityClass.DecryptDES(dr["ForecastExpand"].ToString(), strKey);     //預估收入
                        dgvForcastRow.Cells[5].Value = strIncome;    //預估收入
                        dgvForcastRow.Cells[6].Value = strExpand;    //預估支出
                        intAssetsCount = intAssetsCount + Convert.ToInt32(string.IsNullOrEmpty(strIncome) ? "0" : strIncome.Replace(",", "")) - Convert.ToInt32(string.IsNullOrEmpty(strExpand) ? "0" : strExpand.Replace(",", ""));
                        dgvForcastRow.Cells[7].Value = UtilityClass.MarkNumber(intAssetsCount.ToString());  //實際餘額
                        dgvForcastRow.Cells[8].Value = dr["ItemNo"].ToString();     //專案名稱
                        dgvForcastRow.Cells[10].Value = dr["ProjectCName"].ToString();     //專案名稱
                        dgvCashFlow.Rows.Add(dgvForcastRow);
                    }
                }
            }
            catch (Exception ex)
            {
                ErrorHandler.WriteErrorLog("CashFlowForecast.cs", ex);
            }
        }

        private void ReLoadRest()  //重新整理餘額資料
        {
            string strIncome = "";
            string strExpand = "";
            string strAmount = "";
            try
            {                
                if (dgvCashFlow.Rows.Count > 0)
                {
                    foreach (DataGridViewRow dr in dgvCashFlow.Rows)
                    {
                        if (dr.Index == 0)
                        {
                            strAmount = (string)dr.Cells[7].Value;
                        }
                        else
                        {
                            strIncome = "";
                            strExpand = "";
                            strAmount = (string)dgvCashFlow.Rows[dr.Index - 1].Cells[7].Value;
                            strIncome = (string)dr.Cells[5].Value;    //預估收入
                            strExpand = (string)dr.Cells[6].Value;    //預估支出
                            intAssetsCount = Convert.ToInt32(string.IsNullOrEmpty(strAmount) ? "0" : strAmount.Replace(",", "")) + Convert.ToInt32(string.IsNullOrEmpty(strIncome) ? "0" : strIncome.Replace(",", "")) - Convert.ToInt32(string.IsNullOrEmpty(strExpand) ? "0" : strExpand.Replace(",", ""));
                            dr.Cells[7].Value = UtilityClass.MarkNumber(intAssetsCount.ToString());  //實際餘額
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                ErrorHandler.WriteErrorLog("CashFlowForecast.cs", ex);
            }
        }
        #endregion

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

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

        private void tsbClean_Click(object sender, EventArgs e)
        {
            SetupStatus();
        }

        private void tsbSearch_Click(object sender, EventArgs e)
        {            
        }

        private void tsbCancel_Click(object sender, EventArgs e)
        {
            SetupStatus();
        }

        private void tsbAdd_Click(object sender, EventArgs e)
        {
            StatusChange("Add");
            CashFlowForecastEdit actForm = new CashFlowForecastEdit();
            actForm.strFrmStatus = "ADD";
            actForm.Owner = this;
            actForm.StartPosition = FormStartPosition.CenterParent;
            actForm.ShowDialog();
        }

        private void tsbEdit_Click(object sender, EventArgs e)
        {
            StatusChange("Modify");
            UnLockForm();                
        }

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

        private void tsbSetup_Click(object sender, EventArgs e)
        {
            XMLSetting frmSettingForm = new XMLSetting();
            frmSettingForm.Owner = this;
            frmSettingForm.strOwnerForm = this.Name.ToString();
            frmSettingForm.strXMLName = this.Name.ToString();
            frmSettingForm.StartPosition = FormStartPosition.CenterParent;
            frmSettingForm.ShowDialog();
        }

        private void dgvCashFlow_CellBeginEdit(object sender, DataGridViewCellCancelEventArgs e)
        {
            string strValue = "";
            if (dgvCashFlow.Columns[e.ColumnIndex].Name.ToString() == "cIncome")
            {
                strValue = (string)dgvCashFlow.Rows[e.RowIndex].Cells["cIncome"].Value;
                if (string.IsNullOrEmpty(strValue))
                {
                    return;
                }
                else
                {
                    dgvCashFlow.Rows[e.RowIndex].Cells["cIncome"].Value = strValue.Replace(",", "");
                }
            }

            if (dgvCashFlow.Columns[e.ColumnIndex].Name.ToString() == "cExpand")
            {
                strValue = (string)dgvCashFlow.Rows[e.RowIndex].Cells["cExpand"].Value;
                if (string.IsNullOrEmpty(strValue))
                {
                    return;
                }
                else
                {
                    dgvCashFlow.Rows[e.RowIndex].Cells["cExpand"].Value = strValue.Replace(",", "");
                }
            }
        }

        private void dgvCashFlow_CellEndEdit(object sender, DataGridViewCellEventArgs e)
        {
            //數字呈現方式調整
            string strValue = "";
            if (dgvCashFlow.Columns[e.ColumnIndex].Name.ToString() == "cIncome")
            {
                strValue = (string)dgvCashFlow.Rows[e.RowIndex].Cells["cIncome"].Value;
                if (string.IsNullOrEmpty(strValue))
                {
                    return;
                }
                else
                {
                    strValue = strValue.Replace(",", "");
                    dgvCashFlow.Rows[e.RowIndex].Cells["cIncome"].Value = UtilityClass.MarkNumber(strValue);
                }
            }

            if (dgvCashFlow.Columns[e.ColumnIndex].Name.ToString() == "cExpand")
            {
                strValue = (string)dgvCashFlow.Rows[e.RowIndex].Cells["cExpand"].Value;
                if (string.IsNullOrEmpty(strValue))
                {
                    return;
                }
                else
                {
                    strValue = strValue.Replace(",", "");
                    dgvCashFlow.Rows[e.RowIndex].Cells["cExpand"].Value = UtilityClass.MarkNumber(strValue);
                }
            }
        }


        private void tsbSave_Click(object sender, EventArgs e)  //存在判斷刪除狀態的問題
        {
            string strItemNo = "";
            string strIncome = "";
            string strExpand = "";
            string strSQL = "";
            try
            {
                foreach (DataGridViewRow dr in dgvCashFlow.Rows)
                {
                    strItemNo = (string)dr.Cells["cItemNo"].Value;
                    DataGridViewCheckBoxCell Delcell = (DataGridViewCheckBoxCell)dr.Cells["cDel"];  //刪除狀態列

                    if (Delcell.Value == Delcell.TrueValue)
                    {
                        //判斷刪除被選取
                        if (!string.IsNullOrEmpty(strItemNo))
                        {
                            strSQL = "Delete OTB_FNC_CashFlowForecast Where ItemNo = '" + (string)dr.Cells["cItemNo"].Value + "'";
                            UtilityClass.RunSQLNonReturn(strSQL);
                        }
                    }
                    else
                    {
                        //進行更新                    
                        if (!string.IsNullOrEmpty(strItemNo))
                        {
                            strIncome = (string)dr.Cells["cIncome"].Value;
                            strExpand = (string)dr.Cells["cExpand"].Value;
                            strSQL = "Update OTB_FNC_CashFlowForecast Set ";
                            strSQL += " ForecastDate = '" + dr.Cells["cForecastDate"].Value.ToString() + "',";
                            strSQL += " ForecastIncome = '" + (string.IsNullOrEmpty(strIncome) ? "" : UtilityClass.EncryptDES(strIncome, strKey)) + "',";
                            strSQL += " ForecastExpand = '" + (string.IsNullOrEmpty(strExpand) ? "" : UtilityClass.EncryptDES(strExpand, strKey)) + "',";
                            strSQL += " CustomerName = '" + (string)dr.Cells["cCustomerName"].Value + "',";
                            strSQL += " InvoiceNo = '" + (string)dr.Cells["cInvoiceNo"].Value + "',";
                            strSQL += " Memo = '" + (string)dr.Cells["cMemo"].Value + "',";
                            strSQL += " ProjectNumber = '" + (string)dr.Cells["cProjectNumber"].Value + "',";
                            strSQL += " ProjectCName = '" + (string)dr.Cells["cProjectCName"].Value + "'";
                            strSQL += " Where ItemNo = '" + (string)dr.Cells["cItemNo"].Value + "'";
                            UtilityClass.RunSQLNonReturn(strSQL);
                        }
                    }
                }
                MessageBox.Show("儲存成功", "提示");
                SetupStatus();  //截入初始狀態
            }
            catch (Exception ex)
            {
                MessageBox.Show("儲存有誤", "提示");
                ErrorHandler.WriteErrorLog("CashFlowForecast.cs", ex);
            }
        }

        private void tsbOK_Click(object sender, EventArgs e)
        {
            switch (strFrmStatus.ToString())
            {
                case "MODIFY": //修改
                    EditEven();
                    break;
            }
        }

        private void dgvCashFlow_RowValidating(object sender, DataGridViewCellCancelEventArgs e)
        {
            string strErrorMsg = "";

            try
            {
                //進行資料檢查                
                if (string.IsNullOrEmpty((string)dgvCashFlow.Rows[e.RowIndex].Cells["cForecastDate"].Value))
                {
                    strErrorMsg = "請輸入預估日期";
                }
                else
                { 
                    if (!UtilityClass.IsDate((string)dgvCashFlow.Rows[e.RowIndex].Cells["cForecastDate"].Value))
                    {
                        strErrorMsg = "預估日期格式不對";
                    }
                    else if(Convert.ToDateTime((string)dgvCashFlow.Rows[e.RowIndex].Cells["cForecastDate"].Value) < Convert.ToDateTime(DateTime.Now.ToShortDateString()))
                    {
                        strErrorMsg = "預估日期應大於今天";
                    }
                }

                if ((string)dgvCashFlow.Rows[e.RowIndex].Cells["cMemo"].Value == "")
                {
                    strErrorMsg = "備註不能等於空白";
                }

                if (string.IsNullOrEmpty((string)dgvCashFlow.Rows[e.RowIndex].Cells["cIncome"].Value) && string.IsNullOrEmpty((string)dgvCashFlow.Rows[e.RowIndex].Cells["cExpand"].Value) && e.RowIndex != 0)
                {
                    strErrorMsg = "估預收入及支出不能同時等於空白";
                }

                if (strErrorMsg != "")
                {
                    dgvCashFlow.Rows[e.RowIndex].ErrorText = strErrorMsg;
                    e.Cancel = true;
                }                
            }
            catch (Exception ex)
            {
                ErrorHandler.WriteErrorLog("CashFlowForecast.cs", ex);
            }
        }

        private void dgvCashFlow_RowValidated(object sender, DataGridViewCellEventArgs e)
        {
            dgvCashFlow.Rows[e.RowIndex].ErrorText = "";
            ReLoadRest();
        }
        #endregion

        

        

        

        

    }
}