會計系統
會計系統，預定以ASP.NET MVC4與SQL Server方式改寫。改寫後的版本預定採用LGPL方式授權，如有任何改進建議，歡迎回報。
開發環境
- IDE：Visual Studio 2012
- 資料庫：SQL Server 2012
- 報表：Crystal Reports
- 用戶端：jQuery、jQuery UI、jqGrid
參考資料
- 參考資料1，ACCESS會計管理系統
- 書名：活用ACCESS會計管理系統 開發與運用
- 作者：平南
- 出版社：金禾資訊
- ISBN 9861491066
- 經原作者同意以註明出處後改寫成Web版
- 參考資料2，會計相關用語（中英翻譯）
- 參考資料3，交換票據相關用語
資料表
最初版本
- 以下Table為以SQL Server匯入參考資料1的書附光碟（檔案：會計管理系統2003.mde）再輸出成SQL指令碼，以取得未設關聯的最初版本的資料表
資料表
/****** Object: Table [dbo].[應付票據明細表] Script Date: 04/30/2013 17:48:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[應付票據明細表](
[甲存序號] [nvarchar](2) NOT NULL,
[支票號碼] [nvarchar](15) NOT NULL,
[受款人] [nvarchar](20) NOT NULL,
[到期日] [datetime] NOT NULL,
[支票金額] [money] NOT NULL,
[開立日期] [datetime] NOT NULL,
[發票號碼] [nvarchar](15) NULL,
[傳票號碼] [nvarchar](8) NULL,
[付票內容] [nvarchar](10) NULL,
[沖帳傳票] [nvarchar](8) NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[銷貨折讓臨時檔案] Script Date: 04/30/2013 17:48:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[銷貨折讓臨時檔案](
[客戶名稱] [nvarchar](20) NOT NULL,
[原幣銷貨折讓] [money] NULL,
[台幣銷貨折讓] [money] NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[銷貨成本臨時檔案] Script Date: 04/30/2013 17:48:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[銷貨成本臨時檔案](
[客戶名稱] [nvarchar](20) NOT NULL,
[台幣銷貨成本淨額] [money] NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[銷貨毛額臨時檔案] Script Date: 04/30/2013 17:48:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[銷貨毛額臨時檔案](
[客戶名稱] [nvarchar](20) NOT NULL,
[原幣銷貨收入毛額] [money] NULL,
[台幣銷貨收入毛額] [money] NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[銷貨毛利分析表] Script Date: 04/30/2013 17:48:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[銷貨毛利分析表](
[客戶名稱] [nvarchar](20) NOT NULL,
[幣別] [nvarchar](3) NOT NULL,
[開始日期] [datetime] NOT NULL,
[截止日期] [datetime] NOT NULL,
[原幣銷貨收入毛額] [money] NULL,
[台幣銷貨收入毛額] [money] NULL,
[原幣銷貨折讓] [money] NULL,
[台幣銷貨折讓] [money] NULL,
[原幣銷貨收入淨額] [money] NULL,
[台幣銷貨收入淨額] [money] NULL,
[台幣銷貨成本淨額] [money] NULL,
[台幣銷貨毛利] [money] NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[試算表臨時檔案] Script Date: 04/30/2013 17:48:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[試算表臨時檔案](
[會計科目] [nvarchar](8) NOT NULL,
[借方金額] [money] NULL,
[借方筆數] [int] NOT NULL,
[貸方金額] [money] NULL,
[貸方筆數] [int] NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[試算表] Script Date: 04/30/2013 17:48:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[試算表](
[會計科目] [nvarchar](8) NOT NULL,
[科目名稱] [nvarchar](20) NOT NULL,
[開始日期] [datetime] NOT NULL,
[截止日期] [datetime] NOT NULL,
[期初餘額] [money] NULL,
[借方金額] [money] NULL,
[借方筆數] [int] NULL,
[貸方金額] [money] NULL,
[貸方筆數] [int] NULL,
[期末餘額] [money] NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[會計類別] Script Date: 04/30/2013 17:48:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[會計類別](
[類別編號] [nvarchar](2) NOT NULL,
[類別名稱] [nvarchar](5) NOT NULL,
[大類名稱] [nvarchar](2) NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[會計科目] Script Date: 04/30/2013 17:48:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[會計科目](
[科目編號] [nvarchar](8) NOT NULL,
[科目名稱] [nvarchar](20) NOT NULL,
[類別編號] [nvarchar](2) NOT NULL,
[使用幣別] [nvarchar](1) NOT NULL,
[沖帳] [bit] NOT NULL,
[傳票科目] [bit] NOT NULL,
[部門區分] [bit] NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[匯率主表] Script Date: 04/30/2013 17:48:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[匯率主表](
[幣別代號] [nvarchar](1) NOT NULL,
[幣別名稱] [nvarchar](3) NOT NULL,
[已過帳] [bit] NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[匯率子表] Script Date: 04/30/2013 17:48:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[匯率子表](
[幣別代號] [nvarchar](1) NOT NULL,
[啟用日期] [datetime] NOT NULL,
[匯率] [money] NOT NULL,
[已過帳] [bit] NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[匯差調整表] Script Date: 04/30/2013 17:48:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[匯差調整表](
[會計科目] [nvarchar](8) NOT NULL,
[沖帳碼] [nvarchar](20) NULL,
[幣別代號] [nvarchar](1) NOT NULL,
[新匯率] [real] NOT NULL,
[原幣餘額] [money] NOT NULL,
[應有台幣餘額] [money] NOT NULL,
[現有台幣餘額] [money] NOT NULL,
[匯兌損益] [money] NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[傳票明細] Script Date: 04/30/2013 17:48:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[傳票明細](
[借貸] [nvarchar](1) NOT NULL,
[傳票號碼] [nvarchar](8) NOT NULL,
[會計科目] [nvarchar](8) NOT NULL,
[幣別代號] [nvarchar](1) NOT NULL,
[成本中心] [nvarchar](3) NOT NULL,
[沖帳碼] [nvarchar](20) NULL,
[摘要] [nvarchar](30) NOT NULL,
[原幣借方金額] [money] NULL,
[原幣貸方金額] [money] NULL,
[台幣借方金額] [money] NULL,
[台幣貸方金額] [money] NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[傳票] Script Date: 04/30/2013 17:48:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[傳票](
[傳票號碼] [nvarchar](8) NOT NULL,
[傳票日期] [datetime] NOT NULL,
[製票] [nvarchar](4) NOT NULL,
[製票日期] [datetime] NOT NULL,
[已過帳] [bit] NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[結算帳戶] Script Date: 04/30/2013 17:48:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[結算帳戶](
[科目編號] [nvarchar](8) NOT NULL,
[年月] [nvarchar](7) NOT NULL,
[上期原幣餘額] [money] NULL,
[上期台幣餘額] [money] NULL,
[原幣借方總計] [money] NULL,
[台幣借方總計] [money] NULL,
[原幣貸方總計] [money] NULL,
[台幣貸方總計] [money] NULL,
[本期原幣餘額] [money] NULL,
[本期台幣餘額] [money] NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[常用摘要] Script Date: 04/30/2013 17:48:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[常用摘要](
[摘要說明] [nvarchar](30) NOT NULL,
[總帳科目] [nvarchar](4) NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[財產類別] Script Date: 04/30/2013 17:48:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[財產類別](
[類別編號] [nvarchar](1) NOT NULL,
[類別名稱] [nvarchar](12) NOT NULL,
[固定資產科目] [nvarchar](8) NOT NULL,
[累計折舊科目] [nvarchar](8) NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[固定資產] Script Date: 04/30/2013 17:48:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[固定資產](
[財產類別] [nvarchar](1) NOT NULL,
[財產編號] [nvarchar](9) NOT NULL,
[財產名稱] [nvarchar](20) NOT NULL,
[廠牌規格] [nvarchar](20) NULL,
[數量] [int] NOT NULL,
[單位] [nvarchar](2) NOT NULL,
[取得日期] [datetime] NOT NULL,
[耐用年數] [smallint] NOT NULL,
[幣別代號] [nvarchar](1) NULL,
[原幣成本] [money] NULL,
[原始成本] [money] NOT NULL,
[使用單位] [nvarchar](3) NOT NULL,
[傳票號碼] [nvarchar](8) NULL,
[備註] [nvarchar](10) NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[受款人] Script Date: 04/30/2013 17:48:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[受款人](
[受款人] [nvarchar](20) NOT NULL,
[會計科目] [nvarchar](8) NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[系統建檔] Script Date: 04/30/2013 17:48:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[系統建檔](
[公司名稱] [nvarchar](10) NULL,
[建檔年月] [nvarchar](7) NULL,
[科目編號] [nvarchar](8) NOT NULL,
[幣別代號] [nvarchar](1) NOT NULL,
[沖帳碼] [nvarchar](20) NULL,
[原幣借方金額] [money] NULL,
[原幣貸方金額] [money] NULL,
[台幣借方金額] [money] NULL,
[台幣貸方金額] [money] NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[沖帳碼明細表] Script Date: 04/30/2013 17:48:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[沖帳碼明細表](
[會計科目] [nvarchar](8) NOT NULL,
[沖帳碼] [nvarchar](20) NOT NULL,
[傳票日期] [datetime] NOT NULL,
[傳票號碼] [nvarchar](8) NOT NULL,
[幣別代號] [nvarchar](1) NOT NULL,
[摘要] [nvarchar](30) NULL,
[原幣借方金額] [money] NULL,
[原幣貸方金額] [money] NULL,
[台幣借方金額] [money] NULL,
[台幣貸方金額] [money] NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[成本中心] Script Date: 04/30/2013 17:48:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[成本中心](
[成本中心] [nvarchar](3) NOT NULL,
[部門別] [nvarchar](10) NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[年度初始值] Script Date: 04/30/2013 17:48:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[年度初始值](
[科目編號] [nvarchar](8) NOT NULL,
[年度別] [nvarchar](4) NOT NULL,
[幣別代號] [nvarchar](1) NOT NULL,
[原幣借方金額] [money] NULL,
[原幣貸方金額] [money] NULL,
[台幣借方金額] [money] NULL,
[台幣貸方金額] [money] NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[甲存帳戶] Script Date: 04/30/2013 17:48:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[甲存帳戶](
[帳戶序號] [nvarchar](2) NOT NULL,
[幣別代號] [nvarchar](1) NOT NULL,
[銀行名稱] [nvarchar](10) NOT NULL,
[銀行帳號] [nvarchar](10) NOT NULL,
[銀行存款科目] [nvarchar](8) NOT NULL,
[應付票據科目] [nvarchar](8) NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[用戶群組] Script Date: 04/30/2013 17:48:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[用戶群組](
[用戶編號] [nvarchar](4) NOT NULL,
[用戶名稱] [nvarchar](4) NULL,
[密碼] [nvarchar](8) NOT NULL,
[用戶群組] [nvarchar](5) NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[付票內容] Script Date: 04/30/2013 17:48:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[付票內容](
[付票內容] [nvarchar](10) NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[月結過帳原幣臨時檔案] Script Date: 04/30/2013 17:48:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[月結過帳原幣臨時檔案](
[科目編號] [nvarchar](8) NOT NULL,
[原幣借方金額] [money] NULL,
[原幣貸方金額] [money] NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[月結過帳台幣臨時檔案] Script Date: 04/30/2013 17:48:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[月結過帳台幣臨時檔案](
[科目編號] [nvarchar](8) NOT NULL,
[台幣借方金額] [money] NULL,
[台幣貸方金額] [money] NULL
) ON [PRIMARY]
GO
資料表名稱與欄位名稱英文化後的版本
- 未完
資料表
資料表
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--會計科目
CREATE TABLE [dbo].[AccountingAccounts](
[AccountNo] [nvarchar](10) NOT NULL,
[AccountName] [nvarchar](20) NOT NULL,
[CategoryNo] [nvarchar](6) NOT NULL,
[CurrencyID] [nvarchar](3) NOT NULL,
[IsStrikeBalance] [bit] NOT NULL,
[IsSummonsAccount] [bit] NOT NULL,
[IsDistinguish] [bit] NOT NULL,
CONSTRAINT [PK_AccountingAccounts] PRIMARY KEY CLUSTERED
(
[AccountNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY];
--甲存帳戶(銀行)
CREATE TABLE [dbo].[AccountingBankAccount](
[BankAccountID] [int] IDENTITY(1,1) NOT NULL,
[CurrencyID] [nvarchar](3) NOT NULL,
[BankID] [nvarchar](3) NOT NULL,
[BankName] [nvarchar](10) NOT NULL,
[BankAccount] [nvarchar](16) NOT NULL,
[BankAccountName] [nvarchar](16) NOT NULL,
[AccountNoForBank] [nvarchar](10) NOT NULL,
[AccountNoForPayable] [nvarchar](10) NOT NULL,
CONSTRAINT [PK_AccountingBankAccount] PRIMARY KEY CLUSTERED
(
[BankAccountID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY];
--會計類別(基礎會計科目)
CREATE TABLE [dbo].[AccountingCategory](
[CategoryNo] [nvarchar](6) NOT NULL,
[CategoryNoLen] [int] NOT NULL,
[CategoryName] [nvarchar](50) NOT NULL,
[CategoryDescription] [nvarchar](250) NOT NULL,
CONSTRAINT [PK_AccountingCategory] PRIMARY KEY CLUSTERED
(
[CategoryNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY];
--結算帳戶(過帳時使用)
CREATE TABLE [dbo].[AccountingClearing](
[AccountNo] [nvarchar](10) NOT NULL,
[ClearingYear] [int] NOT NULL,
[ClearingMonth] [int] NOT NULL,
[BeforeBalancesOriginal] [money] NULL,
[BeforeBalances] [money] NULL,
[DebitTotalAmountOriginal] [money] NULL,
[DebitTotalAmount] [money] NULL,
[CreditTotalAmountOriginal] [money] NULL,
[CreditTotalAmount] [money] NULL,
[CurrentBalancesOriginal] [money] NULL,
[CurrentBalances] [money] NULL
) ON [PRIMARY];
--成本中心
CREATE TABLE [dbo].[AccountingCostCenter](
[CostCenter] [nvarchar](4) NOT NULL,
[DepartmentName] [nvarchar](10) NOT NULL,
[DepartmentDescription] [nvarchar](250) NOT NULL,
CONSTRAINT [PK_AccountingCostCenter] PRIMARY KEY CLUSTERED
(
[CostCenter] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY];
--貨幣
CREATE TABLE [dbo].[AccountingCurrency](
[CurrencyID] [nvarchar](3) NOT NULL,
[CurrencyName] [nvarchar](10) NOT NULL,
[EntityName] [nvarchar](10) NOT NULL,
[PreSymbol] [nvarchar](10) NOT NULL,
[CurrencyUnit] [nvarchar](10) NOT NULL,
[CurrencyMinUnit] [int] NOT NULL,
CONSTRAINT [PK_AccountingCurrency] PRIMARY KEY CLUSTERED
(
[CurrencyID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY];
--傳票
CREATE TABLE [dbo].[AccountingSummons](
[SummonsNo] [nvarchar](10) NOT NULL,
[SummonsDate] [datetime] NOT NULL,
[MakeSummons] [nvarchar](10) NOT NULL,
[MakeDate] [datetime] NOT NULL,
[IsPosted] [bit] NOT NULL,
CONSTRAINT [PK_AccountingSummons] PRIMARY KEY CLUSTERED
(
[SummonsNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY];
--傳票明細
CREATE TABLE [dbo].[AccountingSummonsDetails](
[DetailsID] [int] IDENTITY(1,1) NOT NULL,
[SummonsNo] [nvarchar](10) NOT NULL,
[DetailsMode] [nvarchar](1) NOT NULL,
[AccountNo] [nvarchar](10) NOT NULL,
[CurrencyID] [nvarchar](3) NOT NULL,
[CostCenter] [nvarchar](4) NULL,
[ChongzhangCode] [nvarchar](20) NULL,
[Summary] [nvarchar](30) NOT NULL,
[DebitAmountOriginal] [money] NULL,
[CreditAmountOriginal] [money] NULL,
[DebitAmount] [money] NULL,
[CreditAmount] [money] NULL,
CONSTRAINT [PK_AccountingSummonsDetails] PRIMARY KEY CLUSTERED
(
[DetailsID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY];
檢視
檢視
--分類帳或日記帳用
CREATE VIEW [dbo].[AccountingLedger]
AS
SELECT a.DetailsID, a.SummonsNo, a.DetailsMode, a.CurrencyID, a.AccountNo, a.CostCenter, a.ChongzhangCode,
a.Summary, a.DebitAmountOriginal, a.CreditAmountOriginal, a.DebitAmount, a.CreditAmount, b.SummonsDate,
b.MakeSummons, b.MakeDate, b.IsPosted, c.AccountName, c.CategoryNo, d.CurrencyName, YEAR(b.SummonsDate)
AS SummonsYear, MONTH(b.SummonsDate) AS SummonsMonth, DAY(b.SummonsDate) AS SummonsDay
FROM dbo.AccountingSummonsDetails AS a
LEFT OUTER JOIN dbo.AccountingSummons AS b ON a.SummonsNo = b.SummonsNo
LEFT OUTER JOIN dbo.AccountingAccounts AS c ON a.AccountNo = c.AccountNo
LEFT OUTER JOIN dbo.AccountingCurrency AS d ON a.CurrencyID = d.CurrencyID;
預存程序
Model
- 檔案：\Models\AccountingModels.cs （未完）
AccountingModels.cs
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity;
using System.Globalization;
using System.Web.Mvc;
using System.Web.Security;
using System.ComponentModel;
/// <summary>
/// AccountingModels 的摘要描述
/// </summary>
namespace MvcApp1.Models.Accounting
{
public class AccountingContext : DbContext
{
/*public CalendarContext()
: base("DefaultConnection")
{
}*/
public AccountingContext(string strConnection)
: base(strConnection)
{
//System.Data.Entity.Database.SetInitializer<AccountingContext>(null);
}
#region DbSet設定
//貨幣
public DbSet<AccountingCurrency> DbSetAccountingCurrency { get; set; }
//會計類別(基本會計科目編號定義)
public DbSet<AccountingCategory> DbSetAccountingCategory { get; set; }
//會計科目
public DbSet<AccountingAccounts> DbSetAccountingAccounts { get; set; }
//成本中心
public DbSet<AccountingCostCenter> DbSetAccountingCostCenter { get; set; }
//銀行帳號
public DbSet<AccountingBankAccount> DbSetAccountingBankAccount { get; set; }
#endregion DbSet設定
#region DbSet輸入
//傳票
public DbSet<AccountingSummons> DbSetAccountingSummons { get; set; }
//傳票明細
public DbSet<AccountingSummonsDetails> DbSetAccountingSummonsDetails { get; set; }
#endregion DbSet輸入
#region 查詢用
//分類帳查詢
public DbSet<AccountingLedger> DbSetAccountingLedger { get; set; }
#endregion 查詢用
}
#region 設定
//中英名詞對照
//http://www.ardf.org.tw/html/tifrs1001115.pdf
//貨幣
//參照
// http://zh.wikipedia.org/wiki/ISO_4217
// http://www.bsmi.gov.tw/conditions
[Table("AccountingCurrency")]
public class AccountingCurrency
{
[Key]
[DisplayName("幣別代號")]
[Required(ErrorMessage = "幣別代號必須填寫.")]
[StringLength(3, ErrorMessage = "幣別代號不可超過3個字.")]
public string CurrencyID { get; set; }
[DisplayName("幣別名稱")]
[Required(ErrorMessage = "幣別名稱必須填寫.")]
[StringLength(10, ErrorMessage = "幣別名稱不可超過10個字.")]
public string CurrencyName { get; set; }
[DisplayName("國家名稱")]
[Required(ErrorMessage = "國家名稱必須填寫.")]
[StringLength(10, ErrorMessage = "國家名稱不可超過10個字.")]
public string EntityName { get; set; }
[DisplayName("幣別前置符號")]
[Required(ErrorMessage = "幣別前置符號必須填寫.")]
[StringLength(10, ErrorMessage = "幣別前置符號不可超過10個字.")]
public string PreSymbol { get; set; }
[DisplayName("幣別單位")]
[Required(ErrorMessage = "幣別單位必須填寫.")]
[StringLength(10, ErrorMessage = "幣別單位不可超過10個字.")]
public string CurrencyUnit { get; set; }
[DisplayName("小面額單位")]
[Required(ErrorMessage = "小面額單位必須填寫.")]
[Range(0,3, ErrorMessage = "小面額單位必須介於0至3之間.")]
public int CurrencyMinUnit { get; set; }
}
//會計類別(基本會計科目編號定義)
[Table("AccountingCategory")]
public class AccountingCategory
{
[Key]
[DisplayName("類別編號")]
[Required(ErrorMessage = "類別編號必須填寫.")]
[StringLength(6, ErrorMessage = "類別編號不可超過6個字.")]
public string CategoryNo { get; set; }
//類別編號的碼長? (1,2,4,6)
[DisplayName("編號長度")]
[Range(1, 6, ErrorMessage = "類別編號必須在1~6個字.")]
public int CategoryNoLen { get; set; }
[DisplayName("類別名稱")]
[Required(ErrorMessage = "類別名稱必須填寫.")]
[StringLength(50, ErrorMessage = "類別名稱不可超過50個字.")]
public string CategoryName { get; set; }
[DisplayName("類別說明")]
[Required(ErrorMessage = "類別說明必須填寫.")]
[StringLength(250, ErrorMessage = "類別說明不可超過250個字.")]
public string CategoryDescription { get; set; }
}
//會計科目
[Table("AccountingAccounts")]
public class AccountingAccounts
{
[Key]
[DisplayName("科目編號")]
[Required(ErrorMessage = "科目編號必須填寫.")]
[StringLength(10, ErrorMessage = "科目編號不可超過10個字.")]
public string AccountNo { get; set; }
[DisplayName("科目名稱")]
[Required(ErrorMessage = "科目名稱必須填寫.")]
[StringLength(20, ErrorMessage = "科目名稱不可超過20個字.")]
public string AccountName { get; set; }
[DisplayName("類別編號")]
[Required(ErrorMessage = "類別編號必須填寫.")]
[StringLength(6, ErrorMessage = "類別編號不可超過6個字.")]
public string CategoryNo { get; set; }
[DisplayName("使用幣別")]
[Required(ErrorMessage = "使用幣別必須填寫.")]
[StringLength(3, ErrorMessage = "使用幣別不可超過3個字.")]
public string CurrencyID { get; set; }
[DisplayName("沖帳")]
[Column(TypeName = "bit")]
public bool IsStrikeBalance { get; set; }
[DisplayName("傳票科目")]
[Column(TypeName = "bit")]
public bool IsSummonsAccount { get; set; }
[DisplayName("部門區分")]
[Column(TypeName = "bit")]
public bool IsDistinguish { get; set; }
}
//成本中心
[Table("AccountingCostCenter")]
public class AccountingCostCenter
{
[Key]
[DisplayName("成本中心")]
[Required(ErrorMessage = "成本中心必須填寫.")]
[StringLength(4, ErrorMessage = "成本中心不可超過4個字.")]
public string CostCenter { get; set; }
[DisplayName("部門名稱")]
[Required(ErrorMessage = "部門名稱必須填寫.")]
[StringLength(10, ErrorMessage = "部門名稱不可超過10個字.")]
public string DepartmentName { get; set; }
[DisplayName("部門說明")]
[Required(ErrorMessage = "部門說明必須填寫.")]
[StringLength(250, ErrorMessage = "部門說明不可超過250個字.")]
public string DepartmentDescription { get; set; }
}
//銀行帳戶
[Table("AccountingBankAccount")]
public class AccountingBankAccount
{
[Key]
[DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
public int BankAccountID { get; set; }
[DisplayName("幣別代號")]
[Required(ErrorMessage = "幣別代號必須填寫.")]
[StringLength(3, ErrorMessage = "幣別代號不可超過3個字.")]
public string CurrencyID { get; set; }
[DisplayName("銀行代號")]
[Required(ErrorMessage = "銀行代號必須填寫.")]
[StringLength(3, ErrorMessage = "銀行代號不可超過3個字.")]
public string BankID { get; set; }
[DisplayName("銀行名稱")]
[Required(ErrorMessage = "銀行名稱必須填寫.")]
[StringLength(10, ErrorMessage = "銀行名稱不可超過10個字.")]
public string BankName { get; set; }
[DisplayName("銀行帳號")]
[Required(ErrorMessage = "銀行帳號必須填寫.")]
[StringLength(16, ErrorMessage = "銀行帳號不可超過16個字.")]
public string BankAccount { get; set; }
[DisplayName("帳戶名")]
[Required(ErrorMessage = "帳戶名必須填寫.")]
[StringLength(16, ErrorMessage = "帳戶名不可超過16個字.")]
public string BankAccountName { get; set; }
[DisplayName("銀行存款科目")]
[Required(ErrorMessage = "銀行存款科目必須填寫.")]
[StringLength(10, ErrorMessage = "銀行存款科目不可超過10個字.")]
public string AccountNoForBank { get; set; }
[DisplayName("應付票據科目")]
[Required(ErrorMessage = "應付票據科目必須填寫.")]
[StringLength(10, ErrorMessage = "應付票據科目不可超過10個字.")]
public string AccountNoForPayable { get; set; }
}
#endregion 設定
#region 輸入
//傳票
[Table("AccountingSummons")]
public class AccountingSummons
{
[Key]
[DisplayName("傳票號碼")]
[Required(ErrorMessage = "傳票號碼必須填寫.")]
[StringLength(10, ErrorMessage = "傳票號碼不可超過10個字.")]
public string SummonsNo { get; set; }
[DisplayName("傳票日期")]
[DataType(DataType.DateTime)]
[Required(ErrorMessage = "傳票日期必須填寫.")]
public DateTime SummonsDate { get; set; }
[DisplayName("製票")]
[Required(ErrorMessage = "製票必須填寫.")]
[StringLength(10, ErrorMessage = "製票不可超過10個字.")]
public string MakeSummons { get; set; }
[DisplayName("製票日期")]
[DataType(DataType.DateTime)]
[Required(ErrorMessage = "製票日期必須填寫.")]
public DateTime MakeDate { get; set; }
//[已過帳] [bit] NOT NULL
[DisplayName("已過帳")]
[Column(TypeName = "bit")]
public bool IsPosted { get; set; }
}
//傳票明細
[Table("AccountingSummonsDetails")]
public class AccountingSummonsDetails
{
//序號
[Key]
[DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
public int DetailsID { get; set; }
[DisplayName("傳票號碼")]
[Required(ErrorMessage = "傳票號碼必須填寫.")]
[StringLength(10, ErrorMessage = "傳票號碼不可超過10個字.")]
public string SummonsNo { get; set; }
//借貸 ("借"=借方, "貸"=貸方)
[DisplayName("借貸")]
[Required(ErrorMessage = "借貸必須填寫.")]
[StringLength(1, ErrorMessage = "傳票號碼不可超過1個字.")]
public string DetailsMode { get; set; }
[DisplayName("科目編號")]
[Required(ErrorMessage = "科目編號必須填寫.")]
[StringLength(10, ErrorMessage = "科目編號不可超過10個字.")]
public string AccountNo { get; set; }
[DisplayName("使用幣別")]
[Required(ErrorMessage = "使用幣別必須填寫.")]
[StringLength(3, ErrorMessage = "使用幣別不可超過3個字.")]
public string CurrencyID { get; set; }
[DisplayName("成本中心")]
[StringLength(4, ErrorMessage = "成本中心不可超過4個字.")]
public string CostCenter { get; set; }
[DisplayName("沖帳碼")]
[StringLength(20, ErrorMessage = "沖帳碼不可超過20個字.")]
public string ChongzhangCode {get ; set; }
[DisplayName("摘要")]
[Required(ErrorMessage = "摘要必須填寫.")]
[StringLength(30, ErrorMessage = "摘要不可超過30個字.")]
public string Summary { get; set; }
[DisplayName("原幣借方金額")]
[DataType(DataType.Currency)]
[Column(TypeName = "Money")]
public decimal? DebitAmountOriginal { get; set; }
[DisplayName("原幣貸方金額")]
[DataType(DataType.Currency)]
[Column(TypeName = "Money")]
public decimal? CreditAmountOriginal { get; set; }
[DisplayName("台幣借方金額")]
[DataType(DataType.Currency)]
[Column(TypeName = "Money")]
public decimal? DebitAmount { get; set; }
[DisplayName("台幣貸方金額")]
[DataType(DataType.Currency)]
[Column(TypeName = "Money")]
public decimal? CreditAmount { get; set; }
}
#endregion 輸入
#region 結算用
//結算帳戶
[Table("AccountingClearing")]
public class AccountingClearing
{
[DisplayName("科目編號")]
[Required(ErrorMessage = "科目編號必須填寫.")]
[StringLength(10, ErrorMessage = "科目編號不可超過10個字.")]
public string AccountNo { get; set; }
[DisplayName("結算年")]
[Required(ErrorMessage = "結算年必須填寫.")]
public int ClearingYear { get; set; }
[DisplayName("結算月")]
[Required(ErrorMessage = "結算月必須填寫.")]
public int ClearingMonth { get; set; }
[DisplayName("上期原幣餘額")]
[DataType(DataType.Currency)]
[Column(TypeName = "Money")]
public decimal? BeforeBalancesOriginal { get; set; }
[DisplayName("上期台幣餘額")]
[DataType(DataType.Currency)]
[Column(TypeName = "Money")]
public decimal? BeforeBalances { get; set; }
[DisplayName("原幣借方總計")]
[DataType(DataType.Currency)]
[Column(TypeName = "Money")]
public decimal? DebitTotalAmountOriginal { get; set; }
[DisplayName("台幣借方總計")]
[DataType(DataType.Currency)]
[Column(TypeName = "Money")]
public decimal? DebitTotalAmount { get; set; }
[DisplayName("原幣貸方總計")]
[DataType(DataType.Currency)]
[Column(TypeName = "Money")]
public decimal? CreditTotalAmountOriginal { get; set; }
[DisplayName("台幣貸方總計")]
[DataType(DataType.Currency)]
[Column(TypeName = "Money")]
public decimal? CreditTotalAmount { get; set; }
[DisplayName("本期原幣餘額")]
[DataType(DataType.Currency)]
[Column(TypeName = "Money")]
public decimal? CurrentBalancesOriginal { get; set; }
[DisplayName("本期台幣餘額")]
[DataType(DataType.Currency)]
[Column(TypeName = "Money")]
public decimal? CurrentBalances { get; set; }
}
#endregion 結算用
#region 查詢用
//分類帳查詢
[Table("AccountingLedger")]
public class AccountingLedger
{
/* 傳票明細 */
//序號
[Key]
[DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
public int DetailsID { get; set; }
[DisplayName("傳票號碼")]
[Required(ErrorMessage = "傳票號碼必須填寫.")]
[StringLength(10, ErrorMessage = "傳票號碼不可超過10個字.")]
public string SummonsNo { get; set; }
//借貸 ("借"=借方, "貸"=貸方)
[DisplayName("借貸")]
[Required(ErrorMessage = "借貸必須填寫.")]
[StringLength(1, ErrorMessage = "傳票號碼不可超過1個字.")]
public string DetailsMode { get; set; }
[DisplayName("科目編號")]
[Required(ErrorMessage = "科目編號必須填寫.")]
[StringLength(10, ErrorMessage = "科目編號不可超過10個字.")]
public string AccountNo { get; set; }
[DisplayName("使用幣別")]
[Required(ErrorMessage = "使用幣別必須填寫.")]
[StringLength(3, ErrorMessage = "使用幣別不可超過3個字.")]
public string CurrencyID { get; set; }
[DisplayName("成本中心")]
[StringLength(4, ErrorMessage = "成本中心不可超過4個字.")]
public string CostCenter { get; set; }
[DisplayName("沖帳碼")]
[StringLength(20, ErrorMessage = "沖帳碼不可超過20個字.")]
public string ChongzhangCode {get ; set; }
[DisplayName("摘要")]
[Required(ErrorMessage = "摘要必須填寫.")]
[StringLength(30, ErrorMessage = "摘要不可超過30個字.")]
public string Summary { get; set; }
[DisplayName("原幣借方金額")]
[DataType(DataType.Currency)]
[Column(TypeName = "Money")]
public decimal? DebitAmountOriginal { get; set; }
[DisplayName("原幣貸方金額")]
[DataType(DataType.Currency)]
[Column(TypeName = "Money")]
public decimal? CreditAmountOriginal { get; set; }
[DisplayName("台幣借方金額")]
[DataType(DataType.Currency)]
[Column(TypeName = "Money")]
public decimal? DebitAmount { get; set; }
[DisplayName("台幣貸方金額")]
[DataType(DataType.Currency)]
[Column(TypeName = "Money")]
public decimal? CreditAmount { get; set; }
/* 傳票 */
[DisplayName("傳票日期")]
[DataType(DataType.DateTime)]
[Required(ErrorMessage = "傳票日期必須填寫.")]
public DateTime SummonsDate { get; set; }
[DisplayName("製票")]
[Required(ErrorMessage = "製票必須填寫.")]
[StringLength(10, ErrorMessage = "製票不可超過10個字.")]
public string MakeSummons { get; set; }
[DisplayName("製票日期")]
[DataType(DataType.DateTime)]
[Required(ErrorMessage = "製票日期必須填寫.")]
public DateTime MakeDate { get; set; }
//[已過帳] [bit] NOT NULL
[DisplayName("已過帳")]
[Column(TypeName = "bit")]
public bool IsPosted { get; set; }
/* 會計科目 */
[DisplayName("科目名稱")]
[Required(ErrorMessage = "科目名稱必須填寫.")]
[StringLength(20, ErrorMessage = "科目名稱不可超過20個字.")]
public string AccountName { get; set; }
[DisplayName("類別編號")]
[Required(ErrorMessage = "類別編號必須填寫.")]
[StringLength(6, ErrorMessage = "類別編號不可超過6個字.")]
public string CategoryNo { get; set; }
/* 貨幣 */
[DisplayName("幣別名稱")]
[Required(ErrorMessage = "幣別名稱必須填寫.")]
[StringLength(10, ErrorMessage = "幣別名稱不可超過10個字.")]
public string CurrencyName { get; set; }
/* 查詢用的篩選條件 */
[DisplayName("年")]
public int SummonsYear { get; set; }
[DisplayName("月")]
public int SummonsMonth { get; set; }
[DisplayName("日")]
public int SummonsDay { get; set; }
}
#endregion 查詢用
#region 待清理
//TODO: 以下為待做項目
//[Table("應付票據明細表")]
[Table("AccPayableNotes")]
public class PayableNotes
{
[Key]
[DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
public int id { get; set; }
//[甲存序號] [nvarchar](2) NOT NULL,
[DisplayName("甲存序號")]
[DataType(DataType.Text)]
[Required(ErrorMessage = "甲存序號必須填寫.")]
[StringLength(2, ErrorMessage = "甲存序號不可超過2個字.")]
public string No { get; set; }
//[支票號碼] [nvarchar](15) NOT NULL,
[DisplayName("支票號碼")]
[DataType(DataType.Text)]
[Required(ErrorMessage = "支票號碼必須填寫.")]
[StringLength(15, ErrorMessage = "支票號碼不可超過15個字.")]
public string CheckNo { get; set; }
//[受款人] [nvarchar](20) NOT NULL,
[DisplayName("受款人")]
[DataType(DataType.Text)]
[Required(ErrorMessage = "受款人必須填寫.")]
[StringLength(20, ErrorMessage = "受款人不可超過20個字.")]
public string Payee { get; set; }
//[到期日] [datetime] NOT NULL,
[DisplayName("到期日")]
[DataType(DataType.DateTime)]
[Required(ErrorMessage = "到期日必須填寫.")]
public DateTime Maturity { get; set; }
//[支票金額] [money] NOT NULL,
[DisplayName("支票金額")]
[DataType(DataType.Currency)]
[Column(TypeName = "Money")]
[Required(ErrorMessage = "支票金額必須填寫.")]
public decimal Amount { get; set; }
//[開立日期] [datetime] NOT NULL,
[DisplayName("開立日期")]
[DataType(DataType.DateTime)]
[Required(ErrorMessage = "開立日期必須填寫.")]
public DateTime BillingDate { get; set; }
//[發票號碼] [nvarchar](15) NULL,
[DisplayName("發票號碼")]
[DataType(DataType.Text)]
public string InvoiceNo { get; set; }
//[傳票號碼] [nvarchar](8) NULL,
[DisplayName("傳票號碼")]
[DataType(DataType.Text)]
[StringLength(8, ErrorMessage = "傳票號碼不可超過8個字.")]
public string SummonsNo { get; set; }
//[付票內容] [nvarchar](10) NULL,
[DisplayName("付票內容")]
[DataType(DataType.Text)]
[StringLength(10, ErrorMessage = "付票內容不可超過8個字.")]
public string PaymentContent { get; set; }
//[沖帳傳票] [nvarchar](8) NULL
[DisplayName("沖帳傳票")]
[DataType(DataType.Text)]
[StringLength(8, ErrorMessage = "沖帳傳票不可超過8個字.")]
public string WriteOff { get; set; }
}
//[Table("銷貨折讓臨時檔案")]
[Table("AccSalesAllowances")]
public class SalesAllowances
{
[Key]
[DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
public int id { get; set; }
//[客戶名稱] [nvarchar](20) NOT NULL,
[DisplayName("客戶名稱")]
[Required(ErrorMessage = "客戶名稱必須填寫.")]
[StringLength(20, ErrorMessage = "客戶名稱不可超過20個字.")]
public string CustomerName { get; set; }
//[原幣銷貨折讓] [money] NULL,
[DisplayName("原幣銷貨折讓")]
[DataType(DataType.Currency)]
[Column(TypeName = "Money")]
public decimal? OCSalesAllowance { get; set; }
//[台幣銷貨折讓] [money] NULL
[DisplayName("台幣銷貨折讓")]
[DataType(DataType.Currency)]
[Column(TypeName = "Money")]
public decimal? NTSalesAllowance { get; set; }
}
//[Table("銷貨成本臨時檔案")]
[Table("AccSalesCost")]
public class SalesCost
{
[Key]
[DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
public int id { get; set; }
//[客戶名稱] [nvarchar](20) NOT NULL,
[DisplayName("客戶名稱")]
[Required(ErrorMessage = "客戶名稱必須填寫.")]
[StringLength(20, ErrorMessage = "客戶名稱不可超過20個字.")]
public string CustomerName { get; set; }
//[台幣銷貨成本淨額] [money] NULL
[DisplayName("台幣銷貨成本淨額")]
[DataType(DataType.Currency)]
[Column(TypeName = "Money")]
public decimal NTSalesAllowance { get; set; }
}
//[Table("銷貨毛額臨時檔案")]
[Table("AccSalesGrossRevenue")]
public class SalesGrossRevenue
{
[Key]
[DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
public int id { get; set; }
//[客戶名稱] [nvarchar](20) NOT NULL,
[DisplayName("客戶名稱")]
[Required(ErrorMessage = "客戶名稱必須填寫.")]
[StringLength(20, ErrorMessage = "客戶名稱不可超過20個字.")]
public string CustomerName { get; set; }
//[原幣銷貨收入毛額] [money] NULL,
[DisplayName("原幣銷貨收入毛額")]
[DataType(DataType.Currency)]
[Column(TypeName = "Money")]
public decimal? OCSalesGrossRevenue { get; set; }
//[台幣銷貨收入毛額] [money] NULL
[DisplayName("台幣銷貨收入毛額")]
[DataType(DataType.Currency)]
[Column(TypeName = "Money")]
public decimal? NTSalesGrossRevenue { get; set; }
}
//[Table("銷貨毛利分析表")]
[Table("AccSalesMarginAnalysis")]
public class SalesMarginAnalysis
{
[Key]
[DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
public int id { get; set; }
//[客戶名稱] [nvarchar](20) NOT NULL,
[DisplayName("客戶名稱")]
[Required(ErrorMessage = "客戶名稱必須填寫.")]
[StringLength(20, ErrorMessage = "客戶名稱不可超過20個字.")]
public string CustomerName { get; set; }
//[幣別] [nvarchar](3) NOT NULL,
[DisplayName("客戶名稱")]
[Required(ErrorMessage = "幣別必須填寫.")]
[StringLength(3, ErrorMessage = "幣別不可超過3個字.")]
public string Currency { get; set; }
//[開始日期] [datetime] NOT NULL,
[DisplayName("開始日期")]
[DataType(DataType.DateTime)]
[Required(ErrorMessage = "開始日期必須填寫.")]
public DateTime StartDate { get; set; }
//[截止日期] [datetime] NOT NULL,
[DisplayName("截止日期")]
[DataType(DataType.DateTime)]
[Required(ErrorMessage = "截止日期必須填寫.")]
public DateTime EndDate { get; set; }
//[原幣銷貨收入毛額] [money] NULL,
[DisplayName("原幣銷貨收入毛額")]
[DataType(DataType.Currency)]
[Column(TypeName = "Money")]
public decimal? OCSalesGrossRevenue { get; set; }
//[台幣銷貨收入毛額] [money] NULL
[DisplayName("台幣銷貨收入毛額")]
[DataType(DataType.Currency)]
[Column(TypeName = "Money")]
public decimal? NTSalesGrossRevenue { get; set; }
//[原幣銷貨折讓] [money] NULL,
[DisplayName("原幣銷貨折讓")]
[DataType(DataType.Currency)]
[Column(TypeName = "Money")]
public decimal? OCSalesAllowance { get; set; }// Original currency sales allowance
//[台幣銷貨折讓] [money] NULL
[DisplayName("台幣銷貨折讓")]
[DataType(DataType.Currency)]
[Column(TypeName = "Money")]
public decimal? NTSalesAllowance { get; set; }
//[原幣銷貨收入淨額] [money] NULL,
[DisplayName("原幣銷貨收入淨額")]
[DataType(DataType.Currency)]
[Column(TypeName = "Money")]
public decimal? OCSaleNetProfit { get; set; }
//[台幣銷貨收入淨額] [money] NULL,
[DisplayName("台幣銷貨收入淨額")]
[DataType(DataType.Currency)]
[Column(TypeName = "Money")]
public decimal? NTSaleNetProfit { get; set; }
//[台幣銷貨成本淨額] [money] NULL,
[DisplayName("台幣銷貨成本淨額")]
[DataType(DataType.Currency)]
[Column(TypeName = "Money")]
public decimal? NTSaleNetCost { get; set; }
//[台幣銷貨毛利] [money] NULL
[DisplayName("台幣銷貨毛利")]
[DataType(DataType.Currency)]
[Column(TypeName = "Money")]
public decimal? SalesMargin { get; set; }
}
//[Table("試算表臨時檔案")]
[Table("AccSpreadsheetTemp")]
public class SpreadsheetTemp
{
[Key]
[DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
public int id { get; set; }
//[會計科目] [nvarchar](8) NOT NULL,
[DisplayName("會計科目")]
[Required(ErrorMessage = "會計科目必須填寫.")]
[StringLength(8, ErrorMessage = "會計科目不可超過8個字.")]
public string AccountingNo { get; set; }
//[借方金額] [money] NULL,
[DisplayName("借方金額")]
[DataType(DataType.Currency)]
[Column(TypeName = "Money")]
public decimal? DebitAmount { get; set; }
//[借方筆數] [int] NOT NULL,
[Required(ErrorMessage = "借方筆數必須填寫.")]
public int DebitItems { get; set; }
//[貸方金額] [money] NULL,
[DisplayName("貸方金額")]
[DataType(DataType.Currency)]
[Column(TypeName = "Money")]
public decimal? CreditAmount { get; set; }
//[貸方筆數] [int] NOT NULL
[Required(ErrorMessage = "貸方筆數必須填寫.")]
public int CreditItems { get; set; }
}
[Table("試算表")]
public class Spreadsheet
{
[Key]
[DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
public int id { get; set; }
//[會計科目] [nvarchar](8) NOT NULL,
[DisplayName("會計科目")]
[Required(ErrorMessage = "會計科目必須填寫.")]
[StringLength(8, ErrorMessage = "會計科目不可超過8個字.")]
public string AccountingNo { get; set; }
//[科目名稱] [nvarchar](20) NOT NULL,
[DisplayName("科目名稱")]
[Required(ErrorMessage = "科目名稱必須填寫.")]
[StringLength(20, ErrorMessage = "科目名稱不可超過20個字.")]
public string AccountingName { get; set; }
//[開始日期] [datetime] NOT NULL,
[DisplayName("開始日期")]
[DataType(DataType.DateTime)]
[Required(ErrorMessage = "開始日期必須填寫.")]
public DateTime StartDate { get; set; }
//[截止日期] [datetime] NOT NULL,
[DisplayName("截止日期")]
[DataType(DataType.DateTime)]
[Required(ErrorMessage = "截止日期必須填寫.")]
public DateTime EndDate { get; set; }
//[期初餘額] [money] NULL,
[DisplayName("期初餘額")]
[DataType(DataType.Currency)]
[Column(TypeName = "Money")]
public decimal? OpeningBalance { get; set; }
//[借方金額] [money] NULL,
[DisplayName("借方金額")]
[DataType(DataType.Currency)]
[Column(TypeName = "Money")]
public decimal? DebitAmount { get; set; }
//[借方筆數] [int] NULL,
public int DebitItems { get; set; }
//[貸方金額] [money] NULL,
[DisplayName("貸方金額")]
[DataType(DataType.Currency)]
[Column(TypeName = "Money")]
public decimal? CreditAmount { get; set; }
//[貸方筆數] [int] NULL,
public int? CreditItems { get; set; }
//[期末餘額] [money] NULL
[DisplayName("期初餘額")]
[DataType(DataType.Currency)]
[Column(TypeName = "Money")]
public decimal? EndBalance { get; set; }
}
[Table("匯率主表")]
public class ExchangeRate
{
[Key]
[DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
public int id { get; set; }
//[幣別代號] [nvarchar](1) NOT NULL,
//[幣別名稱] [nvarchar](3) NOT NULL,
//[已過帳] [bit] NOT NULL
}
[Table("匯率子表")]
public class ExchangeRateSub
{
[Key]
[DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
public int id { get; set; }
//[幣別代號] [nvarchar](1) NOT NULL,
//[啟用日期] [datetime] NOT NULL,
//[匯率] [money] NOT NULL,
//[已過帳] [bit] NOT NULL
}
[Table("匯差調整表")]
public class ExchangeDiffAdjustments
{
[Key]
[DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
public int id { get; set; }
//[會計科目] [nvarchar](8) NOT NULL,
//[沖帳碼] [nvarchar](20) NULL,
//[幣別代號] [nvarchar](1) NOT NULL,
//[新匯率] [real] NOT NULL,
//[原幣餘額] [money] NOT NULL,
//[應有台幣餘額] [money] NOT NULL,
//[現有台幣餘額] [money] NOT NULL,
//[匯兌損益] [money] NOT NULL
}
[Table("常用摘要")]
public class SummaryText
{
[Key]
[DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
public int id { get; set; }
//[摘要說明] [nvarchar](30) NOT NULL,
//[總帳科目] [nvarchar](4) NULL
}
[Table("財產類別")]
public class AssetsCategory
{
[Key]
[DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
public int id { get; set; }
//[類別編號] [nvarchar](1) NOT NULL,
//[類別名稱] [nvarchar](12) NOT NULL,
//[固定資產科目] [nvarchar](8) NOT NULL,
//[累計折舊科目] [nvarchar](8) NOT NULL
}
[Table("固定資產")]
public class FixedAssets
{
[Key]
[DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
public int id { get; set; }
//[財產類別] [nvarchar](1) NOT NULL,
//[財產編號] [nvarchar](9) NOT NULL,
//[財產名稱] [nvarchar](20) NOT NULL,
public string AssetsName { get; set; }
//[廠牌規格] [nvarchar](20) NULL,
//[數量] [int] NOT NULL,
public string Quantity { get; set; }
//[單位] [nvarchar](2) NOT NULL,
public string Unit { get; set; }
//[取得日期] [datetime] NOT NULL,
public DateTime Date { get; set; }
//[耐用年數] [smallint] NOT NULL,
//[幣別代號] [nvarchar](1) NULL,
//[原幣成本] [money] NULL,
//[原始成本] [money] NOT NULL,
//[使用單位] [nvarchar](3) NOT NULL,
//[傳票號碼] [nvarchar](8) NULL,
//[備註] [nvarchar](10) NULL
}
[Table("受款人")]
public class Payee
{
[Key]
[DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
public int id { get; set; }
//[受款人] [nvarchar](20) NOT NULL,
//[會計科目] [nvarchar](8) NOT NULL
}
[Table("系統建檔")]
public class SystemArchiving
{
[Key]
[DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
public int id { get; set; }
//[公司名稱] [nvarchar](10) NULL,
//[建檔年月] [nvarchar](7) NULL,
//[科目編號] [nvarchar](8) NOT NULL,
//[幣別代號] [nvarchar](1) NOT NULL,
//[沖帳碼] [nvarchar](20) NULL,
//[原幣借方金額] [money] NULL,
//[原幣貸方金額] [money] NULL,
//[台幣借方金額] [money] NULL,
//[台幣貸方金額] [money] NULL
}
[Table("沖帳碼明細表")]
public class ChongzhangCodes
{
[Key]
[DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
public int id { get; set; }
//[會計科目] [nvarchar](8) NOT NULL,
//[沖帳碼] [nvarchar](20) NOT NULL,
//[傳票日期] [datetime] NOT NULL,
//[傳票號碼] [nvarchar](8) NOT NULL,
//[幣別代號] [nvarchar](1) NOT NULL,
//[摘要] [nvarchar](30) NULL,
//[原幣借方金額] [money] NULL,
//[原幣貸方金額] [money] NULL,
//[台幣借方金額] [money] NULL,
//[台幣貸方金額] [money] NULL
}
[Table("年度初始值")]
public class InitialValueForYear
{
[Key]
[DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
public int id { get; set; }
//[科目編號] [nvarchar](8) NOT NULL,
//[年度別] [nvarchar](4) NOT NULL,
//[幣別代號] [nvarchar](1) NOT NULL,
//[原幣借方金額] [money] NULL,
//[原幣貸方金額] [money] NULL,
//[台幣借方金額] [money] NULL,
}
[Table("用戶群組")]
public class UserGroups
{
[Key]
[DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
public int id { get; set; }
//[用戶編號] [nvarchar](4) NOT NULL,
//[用戶名稱] [nvarchar](4) NULL,
//[密碼] [nvarchar](8) NOT NULL,
//[用戶群組] [nvarchar](5) NOT NULL
}
[Table("付票內容")]
public class PayTicketContent
{
[Key]
[DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
public int id { get; set; }
//[付票內容] [nvarchar](10) NOT NULL
}
[Table("月結過帳原幣臨時檔案")]
public class MonthPostingOC
{
[Key]
[DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
public int id { get; set; }
//[科目編號] [nvarchar](8) NOT NULL,
//[原幣借方金額] [money] NULL,
//[原幣貸方金額] [money] NULL
}
[Table("月結過帳台幣臨時檔案")]
public class MonthPostingNT
{
[Key]
[DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
public int id { get; set; }
//[科目編號] [nvarchar](8) NOT NULL,
//[台幣借方金額] [money] NULL,
//[台幣貸方金額] [money] NULL
}
#endregion 待清理
}
Controller
- 未完
AccountingController.cs
- 檔案：\Controllers\AccountingController.cs （功能選單用）
AccountingController.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Web.Security;
using MvcApp1.Models.Accounting;
namespace MvcApp1.Controllers
{
/// <summary>
/// AccountingController的摘要描述(會計系統)
/// </summary>
public class AccountingController : AuthController
{
// GET: Accounting/Index
[HttpGet]
public ActionResult Index(string func = null)
{
ActionResult authResult = getAuthResult();
if (authResult != null)
return authResult;
if (!(this.IsAdmin || this.IsManager)) {
return this.Redirect("~/Home/Index");
}
if (func == null) {
return View();
} else {
if (func == "Ledger")
{
string strConnection = this.GetConnectionString();
if (strConnection == null)
return Json("No Login", JsonRequestBehavior.AllowGet);
using (AccountingContext db = new AccountingContext(strConnection))
{
var query = db.DbSetAccountingAccounts.OrderBy(m => m.AccountNo);
List<SelectListItem> AccountList = new List<SelectListItem>();
foreach (var dr in query)
{
AccountList.Add(new SelectListItem()
{
Text = dr.AccountNo +" - "+dr.AccountName,
Value = dr.AccountNo
});
}
ViewBag.AccountList = AccountList;
}
}
return View(func);
}
}
}
}
AccountingAccountsController.cs
- 檔案：\Controllers\AccountingAccountsController.cs （會計科目）
AccountingAccountsController.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Web.Security;
using System.Linq.Dynamic;
using System.Web.Script.Serialization;
using MvcApp1.Models.Accounting;
using MvcGrid.Models.Grid;
using MvcGrid.Models.Helpers;
namespace MvcApp1.Controllers
{
/// <summary>
/// AccountingAccountsController的摘要描述(會計系統-會計科目)
/// </summary>
public class AccountingAccountsController : AuthController
{
[HttpPost]
public JsonResult ReadData(GridSettings grid)
{
string strConnection = this.GetConnectionString();
if (strConnection == null)
return Json("No Login", JsonRequestBehavior.AllowGet);
using (AccountingContext db = new AccountingContext(strConnection))
{
var query = db.DbSetAccountingAccounts.AsQueryable();
//filtring
if (grid.IsSearch)
{
//And
if (grid.Where.groupOp == "AND")
{
foreach (var rule in grid.Where.rules)
{
query = query.Where<AccountingAccounts>(
rule.field, rule.data,
(WhereOperation)StringEnum.Parse(typeof(WhereOperation), rule.op));
}
}
else
{
//Or
bool IsInitQuery = true;
var temp = (new List<string>()).AsQueryable();
foreach (var rule in grid.Where.rules)
{
var t = query.Where<AccountingAccounts>(
rule.field, rule.data,
(WhereOperation)StringEnum.Parse(typeof(WhereOperation), rule.op))
.Select(m => m.AccountNo);
if (IsInitQuery)
{
IsInitQuery = false;
temp = t;
}
else
{
temp = temp.Union<string>(t);
}
}
temp = temp.Distinct();
query = query.Where<AccountingAccounts>(m => temp.Contains(m.AccountNo));
}
}
//sorting
if (grid.SortColumn != "")
{
query = query.OrderBy<AccountingAccounts>(grid.SortColumn,
grid.SortOrder);
}
else {
query = query.OrderBy<AccountingAccounts>("AccountNo", "asc");
}
int pageIndex = grid.PageIndex - 1;
int pageSize = grid.PageSize;
int totalRecords = query.Count();
int totalPages = (int)Math.Ceiling((float)totalRecords / (float)grid.PageSize);
//paging
var setData = query.Skip(pageIndex * pageSize)
.Take(pageSize)
.ToArray();
//converting in grid format
var result = new
{
total = totalPages,
page = grid.PageIndex,
records = totalRecords,
rows = setData.ToArray()
};
//convert to JSON and return to client
return Json(result, JsonRequestBehavior.AllowGet);
}
}
[HttpPost]
public ActionResult EditData(string oper, string id, AccountingAccounts data)
{
string strConnection = this.GetConnectionString();
if (strConnection == null)
return Json("No Login", JsonRequestBehavior.AllowGet);
switch (oper)
{
case "add":
if ((id == "_empty") || (id.Substring(0, 3) == "jqg"))
{
using (AccountingContext db = new AccountingContext(strConnection))
{
db.DbSetAccountingAccounts.Add(data);
db.SaveChanges();
}
}
break;
case "edit":
using (AccountingContext db = new AccountingContext(strConnection))
{
AccountingAccounts data2 = db.DbSetAccountingAccounts.SingleOrDefault(ev => ev.AccountNo == id);
if (data2.AccountNo == id)
{
data2.AccountName = data.AccountName;
data2.CategoryNo = data.CategoryNo;
data2.CurrencyID = data.CurrencyID;
data2.IsStrikeBalance = data.IsStrikeBalance;
data2.IsSummonsAccount = data.IsSummonsAccount;
data2.IsDistinguish = data.IsDistinguish;
db.SaveChanges();
}
}
break;
case "del":
using (AccountingContext db = new AccountingContext(strConnection))
{
AccountingAccounts data2 = db.DbSetAccountingAccounts.SingleOrDefault(ev => ev.AccountNo == id);
if (data2.CategoryNo == id)
{
db.DbSetAccountingAccounts.Remove(data2);
db.SaveChanges();
}
}
break;
default:
break;
}
var jsonData = new {
result = "success"
};
return Json(jsonData, JsonRequestBehavior.AllowGet);
}
public ActionResult GetAutoComplete(string find = "")
{
string strConnection = this.GetConnectionString();
if (strConnection == null)
return Json("No Login", JsonRequestBehavior.AllowGet);
using (AccountingContext db = new AccountingContext(strConnection))
{
if (find == "")
{
var query = db.DbSetAccountingAccounts
.OrderBy(m => m.AccountNo)
.Select(x => new {
value = x.AccountNo,
label = x.AccountName,
desc = "沖帳("
+(x.IsStrikeBalance ? "Yes" : "No")
+"), 傳票科目("
+(x.IsSummonsAccount ? "Yes" : "No")
+"), 部門區分("
+(x.IsDistinguish ? "Yes" : "No")
+")",
CurrencyID = x.CurrencyID,
IsStrikeBalance = x.IsStrikeBalance,
IsSummonsAccount = x.IsSummonsAccount,
IsDistinguish = x.IsDistinguish
});
var result = query.ToArray();
//convert to JSON and return to client
return Json(result, JsonRequestBehavior.AllowGet);
}
else {
var query = db.DbSetAccountingAccounts
.Where( m=>m.AccountNo.StartsWith(find))
.OrderBy(m => m.CategoryNo)
.Select(x => new {
value = x.AccountNo,
label = x.AccountName,
desc = ""
});
var result = query.ToArray();
//convert to JSON and return to client
return Json(result, JsonRequestBehavior.AllowGet);
}
}
}
}
}
AccountingBankAccountController.cs
- 檔案：\Controllers\AccountingBankAccountController.cs （銀行帳戶）
AccountingBankAccountController.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Web.Security;
using System.Linq.Dynamic;
using System.Web.Script.Serialization;
using MvcApp1.Models.Accounting;
using MvcGrid.Models.Grid;
using MvcGrid.Models.Helpers;
namespace MvcApp1.Controllers
{
/// <summary>
/// AccountingBankAccountController的摘要描述(會計系統-銀行帳號)
/// </summary>
public class AccountingBankAccountController : AuthController
{
[HttpPost]
public JsonResult ReadData(GridSettings grid)
{
string strConnection = this.GetConnectionString();
if (strConnection == null)
return Json("No Login", JsonRequestBehavior.AllowGet);
using (AccountingContext db = new AccountingContext(strConnection))
{
var query = db.DbSetAccountingBankAccount.AsQueryable();
//filtring
if (grid.IsSearch)
{
//And
if (grid.Where.groupOp == "AND")
{
foreach (var rule in grid.Where.rules)
{
query = query.Where<AccountingBankAccount>(
rule.field, rule.data,
(WhereOperation)StringEnum.Parse(typeof(WhereOperation), rule.op));
}
}
else
{
//Or
bool IsInitQuery = true;
var temp = (new List<int>()).AsQueryable();
foreach (var rule in grid.Where.rules)
{
var t = query.Where<AccountingBankAccount>(
rule.field, rule.data,
(WhereOperation)StringEnum.Parse(typeof(WhereOperation), rule.op))
.Select(m => m.BankAccountID);
if (IsInitQuery)
{
IsInitQuery = false;
temp = t;
}
else
{
temp = temp.Union<int>(t);
}
}
temp = temp.Distinct();
query = query.Where<AccountingBankAccount>(m => temp.Contains(m.BankAccountID));
}
}
//sorting
if (grid.SortColumn != "")
{
query = query.OrderBy<AccountingBankAccount>(grid.SortColumn,
grid.SortOrder);
}
else {
query = query.OrderBy<AccountingBankAccount>("BankAccountID", "asc");
}
int pageIndex = grid.PageIndex - 1;
int pageSize = grid.PageSize;
int totalRecords = query.Count();
int totalPages = (int)Math.Ceiling((float)totalRecords / (float)grid.PageSize);
//paging
var setData = query.Skip(pageIndex * pageSize)
.Take(pageSize)
.ToArray();
//converting in grid format
var result = new
{
total = totalPages,
page = grid.PageIndex,
records = totalRecords,
rows = setData.ToArray()
};
//convert to JSON and return to client
return Json(result, JsonRequestBehavior.AllowGet);
}
}
[HttpPost]
public ActionResult EditData(string oper, string id, AccountingBankAccount data)
{
string strConnection = this.GetConnectionString();
if (strConnection == null)
return Json("No Login", JsonRequestBehavior.AllowGet);
switch (oper)
{
case "add":
if ((id == "_empty") || (id.Substring(0, 3) == "jqg"))
{
using (AccountingContext db = new AccountingContext(strConnection))
{
db.DbSetAccountingBankAccount.Add(data);
db.SaveChanges();
}
}
break;
case "edit":
using (AccountingContext db = new AccountingContext(strConnection))
{
AccountingBankAccount data2 = db.DbSetAccountingBankAccount.SingleOrDefault(ev => ev.BankAccountID == int.Parse(id));
if (data2.BankAccountID.ToString() == id)
{
data2.CurrencyID = data.CurrencyID;
data2.BankID = data.BankID;
data2.BankName = data.BankName;
data2.BankAccount = data.BankAccount;
data2.BankAccountName = data.BankAccountName;
data2.AccountNoForBank = data.AccountNoForBank;
data2.AccountNoForPayable = data.AccountNoForPayable;
db.SaveChanges();
}
}
break;
case "del":
using (AccountingContext db = new AccountingContext(strConnection))
{
AccountingBankAccount data2 = db.DbSetAccountingBankAccount.SingleOrDefault(ev => ev.BankAccountID == int.Parse(id));
if (data2.BankAccountID.ToString() == id)
{
db.DbSetAccountingBankAccount.Remove(data2);
db.SaveChanges();
}
}
break;
default:
break;
}
var jsonData = new {
result = "success"
};
return Json(jsonData, JsonRequestBehavior.AllowGet);
}
}
}
AccountingCategoryController.cs
- 檔案：\Controllers\AccountingCategoryController.cs （會計類別）
AccountingCategoryController.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Web.Security;
using System.Linq.Dynamic;
using System.Web.Script.Serialization;
using MvcApp1.Models.Accounting;
using MvcGrid.Models.Grid;
using MvcGrid.Models.Helpers;
namespace MvcApp1.Controllers
{
/// <summary>
/// AccountingCurrencyController的摘要描述(會計系統-會計類別)
/// </summary>
public class AccountingCategoryController : AuthController
{
[HttpPost]
public JsonResult ReadData(GridSettings grid)
{
string strConnection = this.GetConnectionString();
if (strConnection == null)
return Json("No Login", JsonRequestBehavior.AllowGet);
using (AccountingContext db = new AccountingContext(strConnection))
{
var query = db.DbSetAccountingCategory.AsQueryable();
//filtring
if (grid.IsSearch)
{
//And
if (grid.Where.groupOp == "AND")
{
foreach (var rule in grid.Where.rules)
{
query = query.Where<AccountingCategory>(
rule.field, rule.data,
(WhereOperation)StringEnum.Parse(typeof(WhereOperation), rule.op));
}
}
else
{
//Or
bool IsInitQuery = true;
var temp = (new List<string>()).AsQueryable();
foreach (var rule in grid.Where.rules)
{
var t = query.Where<AccountingCategory>(
rule.field, rule.data,
(WhereOperation)StringEnum.Parse(typeof(WhereOperation), rule.op))
.Select(m => m.CategoryNo);
if (IsInitQuery)
{
IsInitQuery = false;
temp = t;
}
else
{
temp = temp.Union<string>(t);
}
}
temp = temp.Distinct();
query = query.Where<AccountingCategory>(m => temp.Contains(m.CategoryNo));
}
}
//sorting
if (grid.SortColumn != "")
{
query = query.OrderBy<AccountingCategory>(grid.SortColumn,
grid.SortOrder);
}
else {
query = query.OrderBy<AccountingCategory>("CategoryNo", "asc");
}
int pageIndex = grid.PageIndex - 1;
int pageSize = grid.PageSize;
int totalRecords = query.Count();
int totalPages = (int)Math.Ceiling((float)totalRecords / (float)grid.PageSize);
//paging
var setData = query.Skip(pageIndex * pageSize)
.Take(pageSize)
.ToArray();
//converting in grid format
var result = new
{
total = totalPages,
page = grid.PageIndex,
records = totalRecords,
rows = setData.ToArray()
};
//convert to JSON and return to client
return Json(result, JsonRequestBehavior.AllowGet);
}
}
[HttpPost]
public ActionResult EditData(string oper, string id, AccountingCategory data)
{
string strConnection = this.GetConnectionString();
if (strConnection == null)
return Json("No Login", JsonRequestBehavior.AllowGet);
switch (oper)
{
case "add":
if ((id == "_empty") || (id.Substring(0, 3) == "jqg"))
{
using (AccountingContext db = new AccountingContext(strConnection))
{
db.DbSetAccountingCategory.Add(data);
db.SaveChanges();
}
}
break;
case "edit":
using (AccountingContext db = new AccountingContext(strConnection))
{
AccountingCategory data2 = db.DbSetAccountingCategory.SingleOrDefault(ev => ev.CategoryNo == id);
if (data2.CategoryNo == id)
{
data2.CategoryNoLen = data.CategoryNoLen;
data2.CategoryName = data.CategoryName;
data2.CategoryDescription = data.CategoryDescription;
db.SaveChanges();
}
}
break;
case "del":
using (AccountingContext db = new AccountingContext(strConnection))
{
AccountingCategory data2 = db.DbSetAccountingCategory.SingleOrDefault(ev => ev.CategoryNo == id);
if (data2.CategoryNo == id)
{
db.DbSetAccountingCategory.Remove(data2);
db.SaveChanges();
}
}
break;
default:
break;
}
var jsonData = new
{
result = "success"
};
return Json(jsonData, JsonRequestBehavior.AllowGet);
}
public ActionResult GetAutoComplete(string find = "")
{
string strConnection = this.GetConnectionString();
if (strConnection == null)
return Json("No Login", JsonRequestBehavior.AllowGet);
using (AccountingContext db = new AccountingContext(strConnection))
{
if (find == "")
{
var query = db.DbSetAccountingCategory
.OrderBy(m => m.CategoryNo)
.Select(x => new {
value = x.CategoryNo,
label = x.CategoryName,
desc = x.CategoryDescription
});
var result = query.ToArray();
//convert to JSON and return to client
return Json(result, JsonRequestBehavior.AllowGet);
}
else {
var query = db.DbSetAccountingCategory
.Where( m=>m.CategoryNo.StartsWith(find))
.OrderBy(m => m.CategoryNo)
.Select(x => new {
value = x.CategoryNo,
label = x.CategoryName,
desc = x.CategoryDescription
});
var result = query.ToArray();
//convert to JSON and return to client
return Json(result, JsonRequestBehavior.AllowGet);
}
}
}
}
}
AccountingCostCenterController.cs
- 檔案：\Controllers\AccountingCostCenterController.cs （成本中心）
AccountingCostCenterController.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Web.Security;
using System.Linq.Dynamic;
using MvcApp1.Models.Accounting;
using MvcGrid.Models.Grid;
using MvcGrid.Models.Helpers;
namespace MvcApp1.Controllers
{
/// <summary>
/// AccountingCurrencyController的摘要描述(會計系統-成本中心
/// </summary>
public class AccountingCostCenterController : AuthController
{
[HttpPost]
public JsonResult ReadData(GridSettings grid)
{
string strConnection = this.GetConnectionString();
if (strConnection == null)
return Json("No Login", JsonRequestBehavior.AllowGet);
using (AccountingContext db = new AccountingContext(strConnection))
{
var query = db.DbSetAccountingCostCenter.AsQueryable();
//filtring
if (grid.IsSearch)
{
//And
if (grid.Where.groupOp == "AND")
{
foreach (var rule in grid.Where.rules)
{
query = query.Where<AccountingCostCenter>(
rule.field, rule.data,
(WhereOperation)StringEnum.Parse(typeof(WhereOperation), rule.op));
}
}
else
{
//Or
bool IsInitQuery = true;
var temp = (new List<string>()).AsQueryable();
foreach (var rule in grid.Where.rules)
{
var t = query.Where<AccountingCostCenter>(
rule.field, rule.data,
(WhereOperation)StringEnum.Parse(typeof(WhereOperation), rule.op))
.Select(m => m.CostCenter);
if (IsInitQuery)
{
IsInitQuery = false;
temp = t;
}
else
{
temp = temp.Union<string>(t);
}
}
temp = temp.Distinct();
query = query.Where<AccountingCostCenter>(m => temp.Contains(m.CostCenter));
}
}
//sorting
if (grid.SortColumn != "")
{
query = query.OrderBy<AccountingCostCenter>(grid.SortColumn,
grid.SortOrder);
}
else {
query = query.OrderBy<AccountingCostCenter>("CostCenter", "asc");
}
int pageIndex = grid.PageIndex - 1;
int pageSize = grid.PageSize;
int totalRecords = query.Count();
int totalPages = (int)Math.Ceiling((float)totalRecords / (float)grid.PageSize);
//paging
var setData = query.Skip(pageIndex * pageSize)
.Take(pageSize)
.ToArray();
//converting in grid format
var result = new
{
total = totalPages,
page = grid.PageIndex,
records = totalRecords,
rows = setData.ToArray()
};
//convert to JSON and return to client
return Json(result, JsonRequestBehavior.AllowGet);
}
}
[HttpPost]
public ActionResult EditData(string oper, string id, AccountingCostCenter data)
{
string strConnection = this.GetConnectionString();
if (strConnection == null)
return Json("No Login", JsonRequestBehavior.AllowGet);
switch (oper)
{
case "add":
if ((id == "_empty") || (id == "jqg1"))
{
using (AccountingContext db = new AccountingContext(strConnection))
{
db.DbSetAccountingCostCenter.Add(data);
db.SaveChanges();
}
}
break;
case "edit":
using (AccountingContext db = new AccountingContext(strConnection))
{
AccountingCostCenter data2 = db.DbSetAccountingCostCenter.SingleOrDefault(ev => ev.CostCenter == id);
if (data2.CostCenter == id)
{
data2.DepartmentName = data.DepartmentName;
db.SaveChanges();
}
}
break;
case "del":
using (AccountingContext db = new AccountingContext(strConnection))
{
AccountingCostCenter data2 = db.DbSetAccountingCostCenter.SingleOrDefault(ev => ev.CostCenter == id);
if (data2.CostCenter == id)
{
db.DbSetAccountingCostCenter.Remove(data2);
db.SaveChanges();
}
}
break;
default:
break;
}
var jsonData = new {
result = "success"
};
return Json(jsonData, JsonRequestBehavior.AllowGet);
}
public ActionResult GetAutoComplete(string find="")
{
string strConnection = this.GetConnectionString();
if (strConnection == null)
return Json("No Login", JsonRequestBehavior.AllowGet);
using (AccountingContext db = new AccountingContext(strConnection))
{
if (find == "")
{
var query = db.DbSetAccountingCostCenter
.OrderBy(m => m.CostCenter)
.Select(x => new
{
value = x.CostCenter,
label = x.DepartmentName,
desc = x.DepartmentDescription
});
var result = query.ToArray();
//convert to JSON and return to client
return Json(result, JsonRequestBehavior.AllowGet);
}
else
{
var query = db.DbSetAccountingCostCenter
.Where(m => m.CostCenter.StartsWith(find))
.OrderBy(m => m.CostCenter)
.Select(x => new
{
value = x.CostCenter,
label = x.DepartmentName,
desc = x.DepartmentDescription
});
var result = query.ToArray();
//convert to JSON and return to client
return Json(result, JsonRequestBehavior.AllowGet);
}
}
}
}
}
AccountingCurrencyController.cs
- 檔案：\Controllers\AccountingCurrencyController.cs （貨幣）
AccountingCurrencyController.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Web.Security;
using System.Linq.Dynamic;
using MvcApp1.Models.Accounting;
using MvcGrid.Models.Grid;
using MvcGrid.Models.Helpers;
namespace MvcApp1.Controllers
{
/// <summary>
/// AccountingCurrencyController的摘要描述(會計系統-貨幣)
/// </summary>
public class AccountingCurrencyController : AuthController
{
[HttpPost]
public JsonResult ReadData(GridSettings grid)
{
string strConnection = this.GetConnectionString();
if (strConnection == null)
return Json("No Login", JsonRequestBehavior.AllowGet);
using (AccountingContext db = new AccountingContext(strConnection))
{
var query = db.DbSetAccountingCurrency.AsQueryable();
//filtring
if (grid.IsSearch)
{
//And
if (grid.Where.groupOp == "AND")
{
foreach (var rule in grid.Where.rules)
{
query = query.Where<AccountingCurrency>(
rule.field, rule.data,
(WhereOperation)StringEnum.Parse(typeof(WhereOperation), rule.op));
}
}
else
{
//Or
bool IsInitQuery = true;
var temp = (new List<string>()).AsQueryable();
foreach (var rule in grid.Where.rules)
{
var t = query.Where<AccountingCurrency>(
rule.field, rule.data,
(WhereOperation)StringEnum.Parse(typeof(WhereOperation), rule.op))
.Select(m => m.CurrencyID);
if (IsInitQuery)
{
IsInitQuery = false;
temp = t;
}
else
{
temp = temp.Union<string>(t);
}
}
temp = temp.Distinct();
query = query.Where<AccountingCurrency>(m => temp.Contains(m.CurrencyID));
}
}
//sorting
if (grid.SortColumn != "")
{
query = query.OrderBy<AccountingCurrency>(grid.SortColumn,
grid.SortOrder);
}
else {
query = query.OrderBy<AccountingCurrency>("CurrencyID", "asc");
}
int pageIndex = grid.PageIndex - 1;
int pageSize = grid.PageSize;
int totalRecords = query.Count();
int totalPages = (int)Math.Ceiling((float)totalRecords / (float)grid.PageSize);
//paging
var setData = query.Skip(pageIndex * pageSize)
.Take(pageSize)
.ToArray();
//converting in grid format
var result = new
{
total = totalPages,
page = grid.PageIndex,
records = totalRecords,
rows = setData.ToArray()
};
//convert to JSON and return to client
return Json(result, JsonRequestBehavior.AllowGet);
}
}
[HttpPost]
public ActionResult EditData(string oper, string id, AccountingCurrency data)
{
string strConnection = this.GetConnectionString();
if (strConnection == null)
return Json("No Login", JsonRequestBehavior.AllowGet);
switch (oper)
{
case "add":
if ((id == "_empty") || (id.Substring(0, 3) == "jqg"))
{
using (AccountingContext db = new AccountingContext(strConnection))
{
db.DbSetAccountingCurrency.Add(data);
db.SaveChanges();
}
}
break;
case "edit":
using (AccountingContext db = new AccountingContext(strConnection))
{
AccountingCurrency data2 = db.DbSetAccountingCurrency.SingleOrDefault(ev => ev.CurrencyID == id);
if (data2.CurrencyID == id)
{
data2.CurrencyName = data.CurrencyName;
data2.EntityName = data.EntityName;
data2.PreSymbol = data.PreSymbol;
data2.CurrencyUnit = data.CurrencyUnit;
data2.CurrencyMinUnit = data.CurrencyMinUnit;
db.SaveChanges();
}
}
break;
case "del":
using (AccountingContext db = new AccountingContext(strConnection))
{
AccountingCurrency data2 = db.DbSetAccountingCurrency.SingleOrDefault(ev => ev.CurrencyID == id);
if (data2.CurrencyID == id)
{
db.DbSetAccountingCurrency.Remove(data2);
db.SaveChanges();
}
}
break;
default:
break;
}
var jsonData = new {
result = "success"
};
return Json(jsonData, JsonRequestBehavior.AllowGet);
}
public ActionResult GetAutoComplete(string find="")
{
string strConnection = this.GetConnectionString();
if (strConnection == null)
return Json("No Login", JsonRequestBehavior.AllowGet);
using (AccountingContext db = new AccountingContext(strConnection))
{
if (find == "")
{
var query = db.DbSetAccountingCurrency
.OrderBy(m => m.CurrencyID)
.Select(x => new
{
value = x.CurrencyID,
label = x.CurrencyName,
desc = x.EntityName
});
var result = query.ToArray();
//convert to JSON and return to client
return Json(result, JsonRequestBehavior.AllowGet);
}
else
{
var query = db.DbSetAccountingCurrency
.Where(m =>m.CurrencyID.StartsWith(find))
.OrderBy(m => m.CurrencyID)
.Select(x => new
{
value = x.CurrencyID,
label = x.CurrencyName,
desc = x.EntityName
});
var result = query.ToArray();
//convert to JSON and return to client
return Json(result, JsonRequestBehavior.AllowGet);
}
}
}
}
}
AccountingLedgerController.cs
- 檔案：\Controllers\AccountingLedgerController.cs （分類帳）
AccountingLedgerController.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Web.Security;
using System.Linq.Dynamic;
using MvcApp1.Models.Accounting;
using MvcGrid.Models.Grid;
using MvcGrid.Models.Helpers;
namespace MvcApp1.Controllers
{
/// <summary>
/// AccountingLedgerController的摘要描述(會計系統-分類帳
/// </summary>
public class AccountingLedgerController : AuthController
{
public JsonResult ReadData(GridSettings grid)
{
string strConnection = this.GetConnectionString();
if (strConnection == null)
return Json("No Login", JsonRequestBehavior.AllowGet);
using (AccountingContext db = new AccountingContext(strConnection))
{
var query = db.DbSetAccountingLedger.AsQueryable();
//filtring
if (grid.IsSearch)
{
//And
if (grid.Where.groupOp == "AND")
{
foreach (var rule in grid.Where.rules)
{
//if (rule.field == "AccountNo") {
// query = query.Where<AccountingLedger>(m => m.AccountNo == rule.data);
//} else if (rule.field == "Year") {
// query = query.Where<AccountingLedger>(m => m.SummonsDate.Year == int.Parse(rule.data));
//} else if (rule.field == "Month") {
// query = query.Where<AccountingLedger>(m => m.SummonsDate.Month == int.Parse(rule.data));
//}
//else {
query = query.Where<AccountingLedger>(
rule.field, rule.data,
(WhereOperation)StringEnum.Parse(typeof(WhereOperation), rule.op));
//}
}
}
else
{
//Or
bool IsInitQuery = true;
var temp = (new List<int>()).AsQueryable();
foreach (var rule in grid.Where.rules)
{
var t = query.Where<AccountingLedger>(
rule.field, rule.data,
(WhereOperation)StringEnum.Parse(typeof(WhereOperation), rule.op))
.Select(m => m.DetailsID);
if (IsInitQuery)
{
IsInitQuery = false;
temp = t;
}
else
{
temp = temp.Union<int>(t);
}
}
temp = temp.Distinct();
query = query.Where<AccountingLedger>(m => temp.Contains(m.DetailsID));
}
}
//sorting
if (grid.SortColumn != "")
{
query = query.OrderBy<AccountingLedger>(grid.SortColumn,
grid.SortOrder);
}
else {
query = query.OrderBy<AccountingLedger>("SummonsNo", "asc");
}
int pageIndex = grid.PageIndex - 1;
int pageSize = grid.PageSize;
int totalRecords = query.Count();
int totalPages = (int)Math.Ceiling((float)totalRecords / (float)grid.PageSize);
//paging
var setData = query.Skip(pageIndex * pageSize)
.Take(pageSize)
.ToArray();
//converting in grid format
var result = new
{
total = totalPages,
page = grid.PageIndex,
records = totalRecords,
rows = setData.ToArray()
};
//convert to JSON and return to client
return Json(result, JsonRequestBehavior.AllowGet);
}
}
}
}
AccountingSummonsController.cs
- 檔案：\Controllers\AccountingSummonsController.cs （傳票）
AccountingSummonsController.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Web.Security;
using System.Linq.Dynamic;
using MvcApp1.Models.Accounting;
using MvcGrid.Models.Grid;
using MvcGrid.Models.Helpers;
namespace MvcApp1.Controllers
{
/// <summary>
/// AccountingSummonsController的摘要描述(會計系統-傳票
/// </summary>
public class AccountingSummonsController : AuthController
{
//[HttpPost]
public JsonResult ReadData(GridSettings grid)
{
string strConnection = this.GetConnectionString();
if (strConnection == null)
return Json("No Login", JsonRequestBehavior.AllowGet);
using (AccountingContext db = new AccountingContext(strConnection))
{
var query = db.DbSetAccountingSummons.AsQueryable();
//filtring
if (grid.IsSearch)
{
//And
if (grid.Where.groupOp == "AND")
{
foreach (var rule in grid.Where.rules)
{
query = query.Where<AccountingSummons>(
rule.field, rule.data,
(WhereOperation)StringEnum.Parse(typeof(WhereOperation), rule.op));
}
}
else
{
//Or
bool IsInitQuery = true;
var temp = (new List<string>()).AsQueryable();
foreach (var rule in grid.Where.rules)
{
var t = query.Where<AccountingSummons>(
rule.field, rule.data,
(WhereOperation)StringEnum.Parse(typeof(WhereOperation), rule.op))
.Select(m => m.SummonsNo);
if (IsInitQuery)
{
IsInitQuery = false;
temp = t;
}
else
{
temp = temp.Union<string>(t);
}
}
temp = temp.Distinct();
query = query.Where<AccountingSummons>(m => temp.Contains(m.SummonsNo));
}
}
//sorting
if (grid.SortColumn != "")
{
query = query.OrderBy<AccountingSummons>(grid.SortColumn,
grid.SortOrder);
}
else {
query = query.OrderBy<AccountingSummons>("SummonsNo", "asc");
}
int pageIndex = grid.PageIndex - 1;
int pageSize = grid.PageSize;
int totalRecords = query.Count();
int totalPages = (int)Math.Ceiling((float)totalRecords / (float)grid.PageSize);
//paging
var setData = query.Skip(pageIndex * pageSize)
.Take(pageSize)
.ToArray();
//converting in grid format
var result = new
{
total = totalPages,
page = grid.PageIndex,
records = totalRecords,
rows = setData.ToArray()
};
//convert to JSON and return to client
return Json(result, JsonRequestBehavior.AllowGet);
}
}
[HttpPost]
public ActionResult EditData(string oper, string id, AccountingSummons data)
{
string strConnection = this.GetConnectionString();
if (strConnection == null)
return Json("No Login", JsonRequestBehavior.AllowGet);
switch (oper)
{
case "add":
if ((id == "_empty") || (id.Substring(0,3) == "jqg"))
{
using (AccountingContext db = new AccountingContext(strConnection))
{
//產生傳票單號
if (data.SummonsNo == null) {
var find = data.SummonsDate;
var query = db.DbSetAccountingSummons
.Where(m => m.SummonsDate == find)
.Select(m => m.SummonsNo.Substring(6))
.Max();
var No = (query != null) ? (int.Parse(query.ToString()) + 1) : 1;
data.SummonsNo = string.Format("{0:yyMMdd}{1:0000}", find, No);
}
db.DbSetAccountingSummons.Add(data);
db.SaveChanges();
}
}
break;
case "edit":
using (AccountingContext db = new AccountingContext(strConnection))
{
AccountingSummons data2 = db.DbSetAccountingSummons.SingleOrDefault(ev => ev.SummonsNo == id);
if (data2.SummonsNo == id)
{
data2.MakeSummons = data.MakeSummons;
data2.MakeDate = data.MakeDate;
db.SaveChanges();
}
}
break;
case "del":
using (AccountingContext db = new AccountingContext(strConnection))
{
AccountingSummons data2 = db.DbSetAccountingSummons.SingleOrDefault(ev => ev.SummonsNo == id);
if (data2.SummonsNo == id)
{
db.DbSetAccountingSummons.Remove(data2);
db.SaveChanges();
}
}
break;
default:
break;
}
var jsonData = new {
result = "success"
};
return Json(jsonData, JsonRequestBehavior.AllowGet);
}
//測試用Url
// http://localhost/AccountingSummons/GetNewSummonsNo?find=2013/06/01
public string GetNewSummonsNo(DateTime find)
{
string strConnection = this.GetConnectionString();
if (strConnection == null)
return "";
using (AccountingContext db = new AccountingContext(strConnection))
{
//產生傳票單號
var query = db.DbSetAccountingSummons
.Where(m => m.SummonsDate == find)
.Select(m=> m.SummonsNo.Substring(6))
.Max();
var No = (query != null) ? (int.Parse(query.ToString())+1) : 1;
var SummonsNo = string.Format("{0:yyMMdd}{1:0000}", find, No);
return SummonsNo;
}
}
}
}
AccountingSummonsDetailsController.cs
- 檔案：\Controllers\AccountingSummonsDetailsController.cs （傳票明細）
AccountingSummonsDetailsController.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Web.Security;
using System.Linq.Dynamic;
using MvcApp1.Models.Accounting;
using MvcGrid.Models.Grid;
using MvcGrid.Models.Helpers;
namespace MvcApp1.Controllers
{
/// <summary>
/// AccountingSummonsController的摘要描述(會計系統-傳票
/// </summary>
public class AccountingSummonsDetailsController : AuthController
{
public JsonResult ReadData(GridSettings grid)
{
string strConnection = this.GetConnectionString();
if (strConnection == null)
return Json("No Login", JsonRequestBehavior.AllowGet);
using (AccountingContext db = new AccountingContext(strConnection))
{
var query = db.DbSetAccountingSummonsDetails.AsQueryable();
//filtring
if (grid.IsSearch)
{
//And
if (grid.Where.groupOp == "AND")
{
foreach (var rule in grid.Where.rules)
{
query = query.Where<AccountingSummonsDetails>(
rule.field, rule.data,
(WhereOperation)StringEnum.Parse(typeof(WhereOperation), rule.op));
}
}
else
{
//Or
bool IsInitQuery = true;
var temp = (new List<int>()).AsQueryable();
foreach (var rule in grid.Where.rules)
{
var t = query.Where<AccountingSummonsDetails>(
rule.field, rule.data,
(WhereOperation)StringEnum.Parse(typeof(WhereOperation), rule.op))
.Select(m => m.DetailsID);
if (IsInitQuery)
{
IsInitQuery = false;
temp = t;
}
else
{
temp = temp.Union<int>(t);
}
}
temp = temp.Distinct();
query = query.Where<AccountingSummonsDetails>(m => temp.Contains(m.DetailsID));
}
}
//sorting
if (grid.SortColumn != "")
{
query = query.OrderBy<AccountingSummonsDetails>(grid.SortColumn,
grid.SortOrder);
}
else {
query = query.OrderBy<AccountingSummonsDetails>("SummonsNo", "asc");
}
int pageIndex = grid.PageIndex - 1;
int pageSize = grid.PageSize;
int totalRecords = query.Count();
int totalPages = (int)Math.Ceiling((float)totalRecords / (float)grid.PageSize);
//paging
var setData = query.Skip(pageIndex * pageSize)
.Take(pageSize)
.ToArray();
//converting in grid format
var result = new
{
total = totalPages,
page = grid.PageIndex,
records = totalRecords,
rows = setData.ToArray()
};
//convert to JSON and return to client
return Json(result, JsonRequestBehavior.AllowGet);
}
}
[HttpPost]
public ActionResult EditData(string oper, string id, AccountingSummonsDetails data)
{
string strConnection = this.GetConnectionString();
if (strConnection == null)
return Json("No Login", JsonRequestBehavior.AllowGet);
switch (oper)
{
case "add":
if ((id == "_empty") || (id.Substring(0, 3) == "jqg"))
{
using (AccountingContext db = new AccountingContext(strConnection))
{
if (data.CostCenter == "")
data.CostCenter = null;
switch (data.DetailsMode)
{
case "借":
data.CreditAmountOriginal = null;
data.CreditAmount = null;
if (data.CurrencyID == "TWD") {
data.DebitAmount = data.DebitAmountOriginal;
} else {
data.DebitAmount = null; //結匯時再紀錄台幣金額
}
break;
case "貸":
data.DebitAmountOriginal = null;
data.DebitAmount = null;
if (data.CurrencyID == "TWD") {
data.CreditAmount = data.CreditAmountOriginal;
} else {
data.CreditAmount = null; //結匯時再紀錄台幣金額
}
break;
}
db.DbSetAccountingSummonsDetails.Add(data);
db.SaveChanges();
}
}
break;
case "edit":
using (AccountingContext db = new AccountingContext(this.GetConnectionString()))
{
int id2 = int.Parse(id);
AccountingSummonsDetails data2 = db.DbSetAccountingSummonsDetails.SingleOrDefault(ev => ev.DetailsID == id2);
if (data2.DetailsID == id2)
{
//data2.SummonsNo = data.SummonsNo;
data2.AccountNo = data.AccountNo;
data2.CurrencyID = data.CurrencyID;
if (data.CostCenter == "")
data2.CostCenter = null;
else
data2.CostCenter = data.CostCenter;
data2.ChongzhangCode = data.ChongzhangCode;
data2.Summary = data.Summary;
string DetailsMode = data.DetailsMode;
switch (DetailsMode)
{
case "借":
data2.DetailsMode = DetailsMode;
data2.DebitAmountOriginal = data.DebitAmountOriginal;
data2.CreditAmountOriginal = null;
if (data2.CurrencyID == "TWD") {
data2.DebitAmount = data.DebitAmountOriginal;
} else {
data.DebitAmount = null; //結匯時再紀錄台幣金額
}
data2.CreditAmount = null;
break;
case "貸":
data2.DetailsMode = DetailsMode;
data2.DebitAmountOriginal = null;
data2.CreditAmountOriginal = data.CreditAmountOriginal;
data2.DebitAmount = null;
if (data2.CurrencyID == "TWD") {
data2.CreditAmount = data.CreditAmountOriginal;
} else {
data.CreditAmount = null; //結匯時再紀錄台幣金額
}
break;
default:
break;
}
db.SaveChanges();
}
}
break;
case "del":
using (AccountingContext db = new AccountingContext(this.GetConnectionString()))
{
int id2 = int.Parse(id);
AccountingSummonsDetails data2 = db.DbSetAccountingSummonsDetails.SingleOrDefault(ev => ev.DetailsID == id2);
if (data2.DetailsID == id2)
{
db.DbSetAccountingSummonsDetails.Remove(data2);
db.SaveChanges();
}
}
break;
default:
break;
}
var jsonData = new {
result = "success"
};
return Json(jsonData, JsonRequestBehavior.AllowGet);
}
}
}
View
Index.cshtml
- 檔案：\Views\Accounting\Index.cshtml （用途：功能選單）
Index.cshtm
Accounts.cshtml
- 檔案：\Views\Accounting\Accounts.cshtml （用途：會計科目）
Accounts.cshtml
BankAccount.cshtml
- 檔案：\Views\Accounting\BankAccount.cshtml （用途：銀行帳號）
BankAccount.cshtml
Category.cshtml
- 檔案：\Views\Accounting\Category.cshtml （用途：會計類別）
Category.cshtml
CostCenter.cshtml
- 檔案：\Views\Accounting\CostCenter.cshtml （用途：成本中心）
CostCenter.cshtml
Currency.cshtml
- 檔案：\Views\Accounting\Currency.cshtml （用途：貨幣）
Currency.cshtml
Currency.cshtml
- 檔案：\Views\Accounting\Currency.cshtml （用途：貨幣）
Currency.cshtml
Journal.cshtml
- 檔案：\Views\Accounting\Journal.cshtml （用途：日記帳）
Journal.cshtml
Ledger.cshtml
- 檔案：\Views\Accounting\Ledger.cshtml （用途：分類帳）
Ledger.cshtml
Summons.cshtml
- 檔案：\Views\Accounting\Summons.cshtml （用途：傳票）
Summons.cshtml
_SummonsDetails.cshtml
- 檔案：\Views\Accounting\_SummonsDetails.cshtml （用途：傳票明細）
_SummonsDetails.cshtml