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.Sql; using System.Data.SqlClient; using System.Configuration; using System.IO; using System.Xml.Linq; using ManagementSystem.Utility; namespace ManagementSystem { public partial class BalanceSheet : Form { //程式內共用物件 string strFrmStatus = ""; //表單狀態 string strActiveUserID = ""; //取得登入作用的使用者帳號 string strAccountingBookID = ""; //取得作用中的帳本 SqlConnection sqlConn = UtilityClass.GetConn(MainForm.strAccountingBookID); SqlCommand sqlCmd = new SqlCommand(); DataSet sdInsurance = new System.Data.DataSet(); string strKey = ""; //程式內加密的Key值 string strTaxAccountingID = ""; //所得稅科目ID public BalanceSheet() { InitializeComponent(); } #region 自定義程式 public void StatusChange(string strStatus) //變更主畫面狀態 { switch (strStatus.ToUpper()) { case "NONE": CleanForm(); CleanToolbar(); ((MainForm)ParentForm).SsStatus.Items["tsslStatus"].Text = ""; strFrmStatus = ""; tsbSearch.Enabled = true; break; case "SEARCH": Application.DoEvents(); tsbSearch.Enabled = false; tsbOK.Visible = true; tsbCancel.Visible = true; ((MainForm)ParentForm).SsStatus.Items["tsslStatus"].Text = "搜尋"; strFrmStatus = "SEARCH"; break; case "ADD": //本功能不提供新增 ((MainForm)ParentForm).SsStatus.Items["tsslStatus"].Text = "新增"; strFrmStatus = "ADD"; break; case "MODIFY": //本功能不提供修改 ((MainForm)ParentForm).SsStatus.Items["tsslStatus"].Text = "修改"; strFrmStatus = "MODIFY"; break; case "DEL": //本功能不提供刪除 ((MainForm)ParentForm).SsStatus.Items["tsslStatus"].Text = "刪除"; strFrmStatus = "DEL"; break; } } private void CleanForm() { cbAccountingYear.DataSource = UtilityClass.GetAccountingYears(5); cbAccountingYear.SelectedIndex = 0; lbLiabilities.Text = ""; lbAssets.Text = ""; lbOwnerRight.Text = ""; crBalanceSheet.Series["Series1"].Points.Clear(); dgvLiabilities.Rows.Clear(); dgvAsset.Rows.Clear(); dgvOwnerRight.Rows.Clear(); tbCharts.SelectedTab = tabBalanceSheet; } private void CleanToolbar() { tsbSearch.Enabled = true; tsbClean.Enabled = true; tsbExit.Enabled = true; tsbOK.Visible = false; tsbCancel.Visible = false; } private void AssetsCount() //計算資產總額 { StringBuilder sbSQL = new StringBuilder(); string strAccountingSubID = ""; string strAccountingSubName = ""; string strAccountingAmount = "0"; string strCredit = ""; string strDebit = ""; string strAssetsCount = "0"; try { sbSQL.Append(" Select J.AccountingBookID, J.AccountingID, J.AccountingDate, J.AccountingSubID, J.AccountingSubName,J.Debit, J.Credit, A.DCClass "); sbSQL.Append(" From OTB_FNC_AccountingJournal J, OTB_FNC_AccountingSubjects A "); sbSQL.Append(" Where J.AccountingSubID = A.AccountingSubID And A.AccountingBookID = '" + strAccountingBookID + "'"); sbSQL.Append(" And ISNULL(J.ClosedAccounting,'N') != 'Y' "); sbSQL.Append(" And J.AccountingBookID ='" + strAccountingBookID + "' "); sbSQL.Append(" And Year(J.AccountingDate) = '" + cbAccountingYear.SelectedValue.ToString() + "'"); sbSQL.Append(" And J.AccountingSubID IN (Select AccountingSubID From OTB_FNC_AccountingSubjects Where AccountingBookID = '" + strAccountingBookID + "' And AccountingClass = 'A') "); sbSQL.Append(" Order by J.AccountingSubID "); using (DataTable dtTemp = UtilityClass.GetSQLResult(sbSQL.ToString()).Tables["Result"]) { if (dtTemp.Rows.Count > 0) { foreach (DataRow drAssetsData in dtTemp.Rows) { if (strAccountingSubID == drAssetsData["AccountingSubID"].ToString()) { strCredit = string.IsNullOrEmpty(drAssetsData["Credit"].ToString()) ? "0" : UtilityClass.DecryptDES(drAssetsData["Credit"].ToString(),strKey).Replace(",", ""); strDebit = string.IsNullOrEmpty(drAssetsData["Debit"].ToString()) ? "0" : UtilityClass.DecryptDES(drAssetsData["Debit"].ToString(), strKey).Replace(",", ""); if (drAssetsData["DCClass"].ToString() == "C") { strAccountingAmount = (Convert.ToInt32(strAccountingAmount) + Convert.ToInt32(strCredit) - Convert.ToInt32(strDebit)).ToString(); } else { strAccountingAmount = (Convert.ToInt32(strAccountingAmount) - Convert.ToInt32(strCredit) + Convert.ToInt32(strDebit)).ToString(); } } else { if (!string.IsNullOrEmpty(strAccountingSubID) && strAccountingAmount != "0") { DataGridViewRow dgvRow = new DataGridViewRow(); dgvRow.CreateCells(dgvAsset); dgvRow.Cells[0].Value = strAccountingSubID; //科目代號 dgvRow.Cells[1].Value = strAccountingSubName; //科目名稱 dgvRow.Cells[2].Value = UtilityClass.MarkNumber(strAccountingAmount); //金額 dgvAsset.Rows.Add(dgvRow); strAssetsCount = (Convert.ToInt32(strAssetsCount) + Convert.ToInt32(strAccountingAmount)).ToString(); strAccountingAmount = "0"; } //清空數值 strAccountingSubID = drAssetsData["AccountingSubID"].ToString(); strAccountingSubName = drAssetsData["AccountingSubName"].ToString(); strCredit = string.IsNullOrEmpty(drAssetsData["Credit"].ToString()) ? "0" : UtilityClass.DecryptDES(drAssetsData["Credit"].ToString(), strKey).Replace(",", ""); strDebit = string.IsNullOrEmpty(drAssetsData["Debit"].ToString()) ? "0" : UtilityClass.DecryptDES(drAssetsData["Debit"].ToString(), strKey).Replace(",", ""); if (drAssetsData["DCClass"].ToString() == "C") { strAccountingAmount = (Convert.ToInt32(strAccountingAmount) + Convert.ToInt32(strCredit) - Convert.ToInt32(strDebit)).ToString(); } else { strAccountingAmount = (Convert.ToInt32(strAccountingAmount) - Convert.ToInt32(strCredit) + Convert.ToInt32(strDebit)).ToString(); } } } if (!string.IsNullOrEmpty(strAccountingSubID) && strAccountingAmount != "0") { DataGridViewRow dgvRow = new DataGridViewRow(); dgvRow.CreateCells(dgvAsset); dgvRow.Cells[0].Value = strAccountingSubID; //科目代號 dgvRow.Cells[1].Value = strAccountingSubName; //科目名稱 dgvRow.Cells[2].Value = UtilityClass.MarkNumber(strAccountingAmount); //金額 dgvAsset.Rows.Add(dgvRow); strAssetsCount = (Convert.ToInt32(strAssetsCount) + Convert.ToInt32(strAccountingAmount)).ToString(); } } lbAssets.Text = UtilityClass.MarkNumber(strAssetsCount); } } catch (Exception ex) { ErrorHandler.WriteErrorLog("BalanceSheet.cs", ex); } } private void LiabilitiesCount() //計算負債總額 { StringBuilder sbSQL = new StringBuilder(); string strAccountingSubID = ""; string strAccountingSubName = ""; string strAccountingAmount = "0"; string strCredit = ""; string strDebit = ""; string strExpenseCount = "0"; try { sbSQL.Append(" Select J.AccountingBookID, J.AccountingID, J.AccountingDate, J.AccountingSubID, J.AccountingSubName,J.Debit, J.Credit, A.DCClass "); sbSQL.Append(" From OTB_FNC_AccountingJournal J, OTB_FNC_AccountingSubjects A "); sbSQL.Append(" Where J.AccountingSubID = A.AccountingSubID And A.AccountingBookID = '" + strAccountingBookID + "'"); sbSQL.Append(" And ISNULL(J.ClosedAccounting,'N') != 'Y' "); sbSQL.Append(" And J.AccountingBookID ='" + strAccountingBookID + "' "); sbSQL.Append(" And Year(J.AccountingDate) = '" + cbAccountingYear.SelectedValue.ToString() + "'"); sbSQL.Append(" And J.AccountingSubID IN (Select AccountingSubID From OTB_FNC_AccountingSubjects Where AccountingBookID = '" + strAccountingBookID + "' And AccountingClass = 'L') "); sbSQL.Append(" Order by J.AccountingSubID "); using (DataTable dtTemp = UtilityClass.GetSQLResult(sbSQL.ToString()).Tables["Result"]) { if (dtTemp.Rows.Count > 0) { foreach (DataRow drLiabilities in dtTemp.Rows) { if (strAccountingSubID == drLiabilities["AccountingSubID"].ToString()) { strCredit = string.IsNullOrEmpty(drLiabilities["Credit"].ToString()) ? "0" : UtilityClass.DecryptDES(drLiabilities["Credit"].ToString(), strKey).Replace(",", ""); strDebit = string.IsNullOrEmpty(drLiabilities["Debit"].ToString()) ? "0" : UtilityClass.DecryptDES(drLiabilities["Debit"].ToString(), strKey).Replace(",", ""); if (drLiabilities["DCClass"].ToString() == "D") { strAccountingAmount = (Convert.ToInt32(strAccountingAmount) - Convert.ToInt32(strCredit) + Convert.ToInt32(strDebit)).ToString(); } else { strAccountingAmount = (Convert.ToInt32(strAccountingAmount) + Convert.ToInt32(strCredit) - Convert.ToInt32(strDebit)).ToString(); } } else { if (!string.IsNullOrEmpty(strAccountingSubID) && strAccountingAmount != "0") { DataGridViewRow dgvRow = new DataGridViewRow(); dgvRow.CreateCells(dgvLiabilities); dgvRow.Cells[0].Value = strAccountingSubID; //科目代號 dgvRow.Cells[1].Value = strAccountingSubName; //科目名稱 dgvRow.Cells[2].Value = UtilityClass.MarkNumber(strAccountingAmount); //金額 dgvLiabilities.Rows.Add(dgvRow); strExpenseCount = (Convert.ToInt32(strExpenseCount) + Convert.ToInt32(strAccountingAmount)).ToString(); strAccountingAmount = "0"; } //清空數值 strAccountingSubID = drLiabilities["AccountingSubID"].ToString(); strAccountingSubName = drLiabilities["AccountingSubName"].ToString(); strCredit = string.IsNullOrEmpty(drLiabilities["Credit"].ToString()) ? "0" : UtilityClass.DecryptDES(drLiabilities["Credit"].ToString(), strKey).Replace(",", ""); strDebit = string.IsNullOrEmpty(drLiabilities["Debit"].ToString()) ? "0" : UtilityClass.DecryptDES(drLiabilities["Debit"].ToString(), strKey).Replace(",", ""); if (drLiabilities["DCClass"].ToString() == "D") { strAccountingAmount = (Convert.ToInt32(strAccountingAmount) - Convert.ToInt32(strCredit) + Convert.ToInt32(strDebit)).ToString(); } else { strAccountingAmount = (Convert.ToInt32(strAccountingAmount) + Convert.ToInt32(strCredit) - Convert.ToInt32(strDebit)).ToString(); } } } if (!string.IsNullOrEmpty(strAccountingSubID) && strAccountingAmount != "0") { DataGridViewRow dgvRow = new DataGridViewRow(); dgvRow.CreateCells(dgvLiabilities); dgvRow.Cells[0].Value = strAccountingSubID; //科目代號 dgvRow.Cells[1].Value = strAccountingSubName; //科目名稱 dgvRow.Cells[2].Value = UtilityClass.MarkNumber(strAccountingAmount); //金額 dgvLiabilities.Rows.Add(dgvRow); strExpenseCount = (Convert.ToInt32(strExpenseCount) + Convert.ToInt32(strAccountingAmount)).ToString(); } } lbLiabilities.Text = UtilityClass.MarkNumber(strExpenseCount); } } catch (Exception ex) { ErrorHandler.WriteErrorLog("BalanceSheet.cs", ex); } } private void OwnerRightCount() //計算業主權益總額 { StringBuilder sbSQL = new StringBuilder(); string strAccountingSubID = ""; string strAccountingSubName = ""; string strAccountingAmount = "0"; string strCredit = ""; string strDebit = ""; string strOwnerRightCount = "0"; try { sbSQL.Append(" Select J.AccountingBookID, J.AccountingID, J.AccountingDate, J.AccountingSubID, J.AccountingSubName,J.Debit, J.Credit, A.DCClass "); sbSQL.Append(" From OTB_FNC_AccountingJournal J, OTB_FNC_AccountingSubjects A "); sbSQL.Append(" Where J.AccountingSubID = A.AccountingSubID And A.AccountingBookID = '" + strAccountingBookID + "'"); sbSQL.Append(" And ISNULL(J.ClosedAccounting,'N') != 'Y' "); sbSQL.Append(" And J.AccountingBookID ='" + strAccountingBookID + "' "); sbSQL.Append(" And Year(J.AccountingDate) = '" + cbAccountingYear.SelectedValue.ToString() + "'"); sbSQL.Append(" And J.AccountingSubID IN (Select AccountingSubID From OTB_FNC_AccountingSubjects Where AccountingBookID = '" + strAccountingBookID + "' And AccountingClass = 'O') "); sbSQL.Append(" Order by J.AccountingSubID, J.AccountingID "); using (DataTable dtTemp = UtilityClass.GetSQLResult(sbSQL.ToString()).Tables["Result"]) { if (dtTemp.Rows.Count > 0) { foreach (DataRow drOwnerRightData in dtTemp.Rows) { if (strAccountingSubID == drOwnerRightData["AccountingSubID"].ToString()) { strDebit = string.IsNullOrEmpty(drOwnerRightData["Debit"].ToString()) ? "0" : UtilityClass.DecryptDES(drOwnerRightData["Debit"].ToString(), strKey).Replace(",", ""); strCredit = string.IsNullOrEmpty(drOwnerRightData["Credit"].ToString()) ? "0" : UtilityClass.DecryptDES(drOwnerRightData["Credit"].ToString(), strKey).Replace(",", ""); strAccountingAmount = (Convert.ToInt32(strAccountingAmount) - Convert.ToInt32(strCredit) + Convert.ToInt32(strDebit)).ToString(); } else { if (!string.IsNullOrEmpty(strAccountingSubID) && strAccountingAmount != "0") { DataGridViewRow dgvRow = new DataGridViewRow(); dgvRow.CreateCells(dgvOwnerRight); dgvRow.Cells[0].Value = strAccountingSubID; //科目代號 dgvRow.Cells[1].Value = strAccountingSubName; //科目名稱 dgvRow.Cells[2].Value = UtilityClass.MarkNumber(strAccountingAmount); //金額 dgvOwnerRight.Rows.Add(dgvRow); strOwnerRightCount = (Convert.ToInt32(strOwnerRightCount) + Convert.ToInt32(strAccountingAmount)).ToString(); strAccountingAmount = "0"; } //清空數值 strAccountingSubID = drOwnerRightData["AccountingSubID"].ToString(); strAccountingSubName = drOwnerRightData["AccountingSubName"].ToString(); strDebit = string.IsNullOrEmpty(drOwnerRightData["Debit"].ToString()) ? "0" : UtilityClass.DecryptDES(drOwnerRightData["Debit"].ToString(), strKey).Replace(",", ""); strCredit = string.IsNullOrEmpty(drOwnerRightData["Credit"].ToString()) ? "0" : UtilityClass.DecryptDES(drOwnerRightData["Credit"].ToString(), strKey).Replace(",", ""); if (drOwnerRightData["DCClass"].ToString() == "D") { strAccountingAmount = (Convert.ToInt32(strAccountingAmount) - Convert.ToInt32(strCredit) + Convert.ToInt32(strDebit)).ToString(); } else { strAccountingAmount = (Convert.ToInt32(strAccountingAmount) - Convert.ToInt32(strCredit) + Convert.ToInt32(strDebit)).ToString(); } } } if (!string.IsNullOrEmpty(strAccountingSubID) && strAccountingAmount != "0") { DataGridViewRow dgvRow = new DataGridViewRow(); dgvRow.CreateCells(dgvOwnerRight); dgvRow.Cells[0].Value = strAccountingSubID; //科目代號 dgvRow.Cells[1].Value = strAccountingSubName; //科目名稱 dgvRow.Cells[2].Value = UtilityClass.MarkNumber(strAccountingAmount); //金額 dgvOwnerRight.Rows.Add(dgvRow); strOwnerRightCount = (Convert.ToInt32(strOwnerRightCount) + Convert.ToInt32(strAccountingAmount)).ToString(); } } lbOwnerRight.Text = UtilityClass.MarkNumber(strOwnerRightCount); } } catch (Exception ex) { ErrorHandler.WriteErrorLog("BalanceSheet.cs", ex); } } private void GoEvent() { AssetsCount(); LiabilitiesCount(); OwnerRightCount(); UtilityClass.SetGridColor(dgvAsset); UtilityClass.SetGridColor(dgvLiabilities); UtilityClass.SetGridColor(dgvOwnerRight); //計算稅前淨利 string strAssets = lbAssets.Text.ToString(); string strLiabilities = lbLiabilities.Text.ToString(); string strOwnerRight = lbOwnerRight.Text.ToString(); strAssets = string.IsNullOrEmpty(strAssets) ? "0" : strAssets.Replace(",", ""); strLiabilities = string.IsNullOrEmpty(strLiabilities) ? "0" : strLiabilities.Replace(",", ""); strOwnerRight = string.IsNullOrEmpty(strOwnerRight) ? "0" : strOwnerRight.Replace(",", ""); ShowBalanceSheetChar(); } private void ShowBalanceSheetChar() { string[] strItems = {"資產總計", "負債總計", "業主權益總計" }; int[] intValues = { Convert.ToInt32(lbAssets.Text.Replace(",", "")), Convert.ToInt32(lbLiabilities.Text.Replace(",", "")), Convert.ToInt32(lbOwnerRight.Text.Replace(",", "")) }; crBalanceSheet.Series["Series1"]["PieLabelStyle"] = "Inside"; crBalanceSheet.Series["Series1"].Points.DataBindXY(strItems, intValues); crBalanceSheet.Series["Series1"].IsValueShownAsLabel = true; } #endregion #region 事件觸發及問題處理 private void BalanceSheet_Load(object sender, EventArgs e)//畫面載入設定 { try { strActiveUserID = MainForm.strActiveUserID; strAccountingBookID = MainForm.strAccountingBookID; strKey = MainForm.strKey; if (MainForm.strKey == "") { //設定Toolbar初始狀態 tsbSearch.Enabled = false; } //設定畫面初始狀態 StatusChange("NONE"); GoEvent(); } catch (Exception ex) { ErrorHandler.WriteErrorLog("BalanceSheet.cs", ex); } } private void tsbSearch_Click(object sender, EventArgs e) { CleanForm(); tsbSearch.Visible = false; tsbOK.Visible = true; tsbCancel.Visible = true; } private void tsbCancel_Click(object sender, EventArgs e) { tsbSearch.Visible = true; tsbOK.Visible = false; tsbCancel.Visible = false; } private void tsbOK_Click(object sender, EventArgs e) { GoEvent(); tsbSearch.Visible = true; tsbOK.Visible = false; tsbCancel.Visible = false; } private void tsbExit_Click(object sender, EventArgs e) { this.Close(); } 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 tsbClean_Click(object sender, EventArgs e) { CleanForm(); } #endregion } }