You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
 
 

472 lines
24 KiB

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
}
}