C＃MySql和会计软件 [英] C# MySql and Accounting Software
问题描述
我正在制作会计软件。在会计中，每条记录有两行或更多行/行，例如：
-----------------
销售| 500,000 |
现金| -500,000 |
----------------
以上会计有两行。类似的另一个会计记录我包含四行，如：
-----------------
销售| 500,000 |
折扣| -100,000 |
现金| -400,000 |
----------------
我的问题是，怎么给特定会计记录的唯一ID，例如：
-----------------
1 |销售| 500,000 |
1 |现金| -500,000 |
2 |销售| 500,000 |
2 |折扣| -100,000 |
2 |现金| -400,000 |
----------------
我可以在两个会计之间区分记录
以及当用户使用2行或更多行保存新记帐记录时。 Mysql会自动为这两行或更多行提供3。
是否可能？
Hi,
I am making an accounting software. In accounting, each record has two or more lines/rows such as:
-----------------
Sales | 500,000 |
Cash | -500,000|
----------------
There are two rows in above accounting. similarly another accounting record my contain four rows such as:
-----------------
Sales | 500,000 |
Discount | -100,000|
Cash | -400,000|
----------------
My problem is, how to give a unique id to a particular accounting record such as:
-----------------
1 |Sales | 500,000 |
1 |Cash | -500,000|
2 |Sales | 500,000 |
2 |Discount | -100,000|
2 |Cash | -400,000|
----------------
THAT I CAN DISTINGUISH BETWEEN TWO ACCOUNTING RECORDS
and when user saves a new accounting record with 2 or more rows. Mysql automatically give ''3'' to all those two or more rows.
IS IT POSSIBLE?
推荐答案
Hello,
You will have to to this at the time of insert. First add a column say record_id INTEGER in the table and have a primary key as record_id + account_id (column containing the vlaues Sales, Discount, Cash etc).
Then define following table in your database <pre lang="SQL">CREATE TABLE sequence_data (
sequence_name varchar(100) NOT NULL,
sequence_increment int(11) unsigned NOT NULL DEFAULT 1,
sequence_min_value int(11) unsigned NOT NULL DEFAULT 1,
sequence_max_value bigint(20) unsigned NOT NULL DEFAULT 18446744073709551615,
sequence_cur_value bigint(20) unsigned DEFAULT 1,
sequence_cycle boolean NOT NULL DEFAULT FALSE,
PRIMARY KEY (sequence_name)
)
然后定义以下函数以生成序列号
Then define following function to generate the sequence number
CREATE FUNCTION nextval (seq_name varchar(100))
RETURNS bigint(20) NOT DETERMINISTIC
BEGIN
DECLARE cur_val bigint(20);
SELECT
sequence_cur_value INTO cur_val
FROM
sequence.sequence_data
WHERE
sequence_name = seq_name;
IF cur_val IS NOT NULL THEN
UPDATE
sequence.sequence_data
SET
sequence_cur_value = IF (
(sequence_cur_value + sequence_increment) > sequence_max_value,
IF (
sequence_cycle = TRUE,
sequence_min_value,
NULL
),
sequence_cur_value + sequence_increment
)
WHERE
sequence_name = seq_name;
END IF;
RETURN cur_val;
END;
最后在你的代码中首先调用上面的函数来获取唯一标识符和然后将数据插入到会计表中。以下代码应该让您入门。
Finally in your code first call the above function to get the unique identifier and then insert data in to your accounting table. Following code should get you started.
public void createAcTxn(List<AcEntry> entries) {
string strCon = @"server=localhost;userid=userid;password=pasword;database=mydb";
int retVal = -1;
MySqlCommand seqCmd = null;
MySqlCommand insCmd = null;
MySqlConnection conn = null;
try
{
conn = new MySqlConnection(cs);
conn.Open();
seqCmd = new MySqlCommand("CALL nextval(@seq_name);", conn);
seqCmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add("@seq_name", System.Data.SqlDbType.VarChar, 40).Value = "ACTXN_SEQ";
retVal = (int) seqCmd.ExecuteScalar();
insCmd = new MySqlCommand("INSERT actxn(record_id, account_id, txn_amount) VALUES(@recId, @acId, @amnt)", conn);
insCmd.Prepare();
insCmd.Parameters.Add("@recId", SqlDbType.Int);
insCmd.Parameters.Add("@acId", SqlDbType.VarChar);
insCmd.Parameters.Add("@amnt", SqlDbType.Decimal);
foreach(AcEntry entry in entries) {
incCmd.Parameters(0).Value = retVal;
incCmd.Parameters(1).Value = entry.AccountId;
incCmd.Parameters(2).Value = entry.Amount;
insCmd.ExecuteNonQuery();
}
} catch (MySqlException ex) {
Console.WriteLine("Error: {0}", ex.ToString());
} finally {
doClose(conn); // A Helper method to cleanup & release the connection
}
}
注意：我还没有测试了上面的代码，因此可能存在一些类型/编译错误。您应该可以在IDE中修复它们
问候，
Note: I have not tested the above code, so there might be some type/compilation errors. You should be able to fix those in IDE
Regards,
您将需要序列生成包，它将返回每个序列键的唯一序列号。
A）Record_Key
B）Journal_Key
在插入之前，您必须编写逻辑以将这两个生成的密钥插入记录中。每次插入到记帐表中时，都必须获取Record_Key序列。每次识别会计分支以进行插入时，将获取Journal_key。在你的情况下，1和2是Journal_Key，record_key可以是你的会计表的主键。
You will need sequence generation package which will return unique sequence number for each sequence-key.
A) Record_Key
B) Journal_Key
Before insert you will have to write logic to insert these 2 generated keys into the record. Record_Key sequence has to be fetched every time an insert into the accounting table is done. Journal_key to be fetched every time the accounting legs are identified for insert. In your case 1 and 2 are Journal_Key and record_key can be a primary key for your accounting table.
这篇关于C＃MySql和会计软件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
