private void MonthlyReportBakery_Load(object sender, EventArgs e)
{
decimal FeeRate = 1.8m;
try
{
var headerAdapter = new MyHeaderAdapter();
headerAdapter.Fill(m_DataSet.Header);
TitleSetup Setup = new TitleSetup();
Setup.Load();
FeeRate = Setup.FeeRate();
}
catch
{
MessageBox.Show("標頭資料讀取錯誤,你的資料庫版本可能不對");
}
int count = m_DataSet.Header.Count;
if (count == 0)
{
MessageBox.Show("無資料!");
m_CloseOnLoad = true; // OnLoad時無法呼叫Close(), 設置變數在OnShown呼叫
// Close
return;
}
var row = m_DataSet.Header[count - 1];
Revenue = new RevenueCalcBakery(row.DataDate, FeeRate / 100);
comboBoxMonth.SelectedIndex = row.DataDate.Month - 1;
labelFeeRate.Text = FeeRate.ToString() + "%";
}
private void ReportByTitle_Load(object sender, EventArgs e)
{
var headerAdapter = new MyHeaderAdapter();
m_OrderSet = m_DataSet;
var accTitleAdapter = new VoucherExpense.DamaiDataSetTableAdapters.AccountingTitleTableAdapter();
var bankAccountAdapter = new VoucherExpense.DamaiDataSetTableAdapters.BankAccountTableAdapter();
var expenseAdapter = new VoucherExpense.DamaiDataSetTableAdapters.ExpenseTableAdapter();
var voucherAdapter = new VoucherExpense.DamaiDataSetTableAdapters.VoucherTableAdapter();
var voucherDetailAdapter = new VoucherExpense.DamaiDataSetTableAdapters.VoucherDetailTableAdapter();
var bankDetailAdapter = new VoucherExpense.DamaiDataSetTableAdapters.BankDetailTableAdapter();
var accVoucherAdapter = new VoucherExpense.DamaiDataSetTableAdapters.AccVoucherTableAdapter();
accTitleAdapter.Connection.ConnectionString = DB.SqlConnectString(MyFunction.HardwareCfg);
try
{
headerAdapter.Fill(m_OrderSet.Header);
}
catch { MessageBox.Show("標頭資料讀取錯誤,你的資料庫版本可能不對"); }
int count = m_OrderSet.Header.Count;
if (count == 0)
{
MessageBox.Show("無資料!");
Close();
return;
}
var row = m_OrderSet.Header[count - 1];
Revenue = new RevenueCalcBakery(row.DataDate, 0);
AccList.NewAll();
BankDictionary = new Dictionary <int, BankDefault>();
RevenueCache = new MonthlyReportData[12];
MonthBalances = new CMonthBalance[13];
for (int i = 0; i < 13; i++)
{
MonthBalances[i] = new CMonthBalance();
MonthBalances[i].Month = i + 1;
}
MonthBalances[12].Month = 0; // 第13月統計用
cMonthBalanceBindingSource.DataSource = MonthBalances;
string[] Name = new string[6] {
"資產", "負債", "收入", "成本", "費用", "股東權益"
};
comboBox1.Items.Clear();
comboBox2.Items.Clear();
foreach (string str in Name)
{
comboBox1.Items.Add(str);
comboBox2.Items.Add(str);
}
try
{
accTitleAdapter.Fill(m_DataSet.AccountingTitle);
bankAccountAdapter.Fill(m_DataSet.BankAccount);
expenseAdapter.Fill(m_DataSet.Expense); // expense檔案小,先全部讀進記憶體
voucherAdapter.Fill(m_DataSet.Voucher);
voucherDetailAdapter.Fill(m_DataSet.VoucherDetail);
bankDetailAdapter.Fill(m_DataSet.BankDetail);
accVoucherAdapter.Fill(m_DataSet.AccVoucher);
foreach (var r in m_DataSet.AccountingTitle)
{
AccTitle item = new AccTitle(r.TitleCode, r.Name);
if (r.IsInitialValueNull())
{
item.Money = 0;
}
else
{
item.Money = r.InitialValue;
}
if (r.TitleCode.Length == 0)
{
continue;
}
AccList.Add(item);
}
AccList1.CopyTableFrom(AccList);
foreach (var r in m_DataSet.BankAccount)
{
BankDictionary.Add(r.ID, new BankDefault(r.AccountTitleCode, r.DefaultTitleCode));
}
comboBox1.SelectedIndex = 0;
comboBox2.SelectedIndex = 1;
comboBoxStart.SelectedIndex = MyFunction.IntHeaderMonth;
Setup.Load();
dataGridView1.Focus();
}
catch
{
MessageBox.Show("資料庫讀取錯誤!");
}
}
private void btnAction_Click(object sender, EventArgs e)
{
try
{
if (Directory.Exists(m_Dir))
{
if (MessageBox.Show("目錄" + m_Dir + " 己經存在! 仍要繼續?", "", MessageBoxButtons.YesNo) != DialogResult.Yes)
{
return;
}
}
else
{
Message("建立目錄");
Directory.CreateDirectory(m_Dir);
}
Message("建立新年度的" + HardwareConfig.CfgFileName);
string backupStr = m_HCfg.Database;
m_HCfg.Database = NewSqlDB;
m_HCfg.SaveTo(m_Dir);
m_HCfg.Database = backupStr;
Message("創建 [" + NewSqlDB + "]");
// 檢查 NewSqlDB是否己經存在
var sqlCommand = new SqlCommand("select count(*) from sysdatabases where name='" + NewSqlDB + "'");
string connStr = DB.SqlConnectString(m_HCfg.Local, NewSqlDB);
//MapPath.SqlConnectString(m_HCfg.SqlServerIP, "master", m_HCfg.SqlUserID, m_HCfg.SqlPassword);
SqlConnection sqlMasterConn = new SqlConnection(connStr);
sqlMasterConn.Open();
sqlCommand.Connection = sqlMasterConn;
int num = (int)sqlCommand.ExecuteScalar();
bool createNew = true;
if (num > 0)
{
if (MessageBox.Show("己經存在[" + NewSqlDB + "], 將不新建及複制,只檢查PK及FK.仍要繼續嗎?", "", MessageBoxButtons.YesNo) != DialogResult.Yes)
{
return;
}
createNew = false;
}
if (createNew)
{
// 建立 DataBase
sqlCommand = sqlMasterConn.CreateCommand();
sqlCommand.CommandText = "CREATE DATABASE [" + NewSqlDB + "]";
num = sqlCommand.ExecuteNonQuery();
// 建立所有的空Table
List <string> list = GetTableName(m_DataSet); // 懶得寫SqlCommand,直接重DataSet定義抓
string destDB = "[" + NewSqlDB + "].[dbo].[";
string sourDB = "[" + m_HCfg.Database + "].[dbo].[";
foreach (string name in list)
{
string cmd = "select * into " + destDB + name + "] from " + sourDB + name + "]";
var results = from string s in chListBoxVE.CheckedItems where s == name select s;
if (results.Count() <= 0)
{
sqlCommand.CommandText = cmd + " where 1=0"; // 只建立結構,不帶主Key及FK
sqlCommand.ExecuteNonQuery();
Message("建立空 [" + name + "]");
}
else
{
sqlCommand.CommandText = cmd + " where 1=1"; // 建立結構,也Copy資料
num = sqlCommand.ExecuteNonQuery();
MessageBox.Show("複制 [" + name + "] 共" + num.ToString() + "筆!");
}
}
}
sqlMasterConn.Dispose();
// 建立PrimaryKey
Message("建立各表PrimaryKey!");
SqlConnection newConn = new SqlConnection(DB.SqlConnectString(m_HCfg.Local, NewSqlDB));
newConn.Open();
sqlCommand.Connection = newConn;
string cmdPrifix, cmdMiddle;
string cmdPosfix = " ) WITH (PAD_INDEX = OFF,STATISTICS_NORECOMPUTE = OFF,IGNORE_DUP_KEY = OFF,ALLOW_ROW_LOCKS = ON,ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]";
foreach (DataTable tab in m_DataSet.Tables)
{
sqlCommand.CommandText = "Select * from sys.indexes where object_id=object_id('" + tab.TableName + "') and is_primary_key=1";
num = 0;
object o = sqlCommand.ExecuteScalar();
if (o != null && !o.Equals(DBNull.Value))
{
num = (int)o;
}
if (num > 0)
{
MessageBox.Show("[" + tab.TableName + "] 己經存在主Key,將不新建主Key,請自行檢查是否正確!");
}
else
{
cmdPrifix = "ALTER TABLE [dbo].[" + tab.TableName + "] ADD CONSTRAINT [PK_" + tab.TableName + "] PRIMARY KEY CLUSTERED ( ";
cmdMiddle = "";
foreach (DataColumn col in tab.PrimaryKey)
{
cmdMiddle += ("[" + col.ColumnName + "] ASC,");
}
sqlCommand.CommandText = cmdPrifix + cmdMiddle.Substring(0, cmdMiddle.Length - 1) + cmdPosfix;
sqlCommand.ExecuteNonQuery();
}
}
// 建立Relation,,AutoIncrement
foreach (DataRelation fk in m_DataSet.Relations)
{
cmdPrifix = "ALTER TABLE [dbo].[" + fk.ChildTable.TableName + "] WITH NOCHECK ADD CONSTRAINT [" + fk.RelationName + "] ";
cmdMiddle = "FOREIGN KEY( [" + fk.ChildColumns[0].ColumnName + "] ) ";
cmdPosfix = "REFERENCES [dbo].[" + fk.ParentTable.TableName + "] ([" + fk.ParentColumns[0].ColumnName + "])";
if (fk.ChildColumns.Count() != 1 || fk.ParentColumns.Count() != 1)
{
MessageBox.Show("ForiegnKey:" + fk.RelationName + "超二個欄位,目前系統未支持,請手動修正!");
}
sqlCommand.CommandText = cmdPrifix + cmdMiddle + cmdPosfix;
sqlCommand.ExecuteNonQuery();
// Constrain Check
sqlCommand.CommandText = "ALTER TABLE [dbo].[" + fk.ChildTable.TableName + "] CHECK CONSTRAINT [" + fk.RelationName + "] ";
sqlCommand.ExecuteNonQuery();
}
// 建立Header VEHeader
var sqlConn = new SqlConnection(DB.SqlConnectString(m_HCfg.Local, NewSqlDB));
var sourceConn = new SqlConnection(DB.SqlConnectString(m_HCfg.Local, m_HCfg.Database));
m_OrderSet.Header.Clear();
var row1 = m_OrderSet.Header.AddHeaderRow(new DateTime(m_Year, 1, 1), false, 0, 0, 0, 0);
var adapterBa = new VoucherExpense.DamaiDataSetTableAdapters.HeaderTableAdapter();
adapterBa.Connection = sqlConn;
adapterBa.Update(row1);
DateTime dt = new DateTime(m_Year, 1, 1);
m_DataSet.Header.Clear();
var row2 = m_DataSet.VEHeader.AddVEHeaderRow(dt, false, dt, dt, Application.ProductVersion.Trim());
var adapterVE = new VoucherExpense.DamaiDataSetTableAdapters.VEHeaderTableAdapter();
adapterVE.Connection = sqlConn;
adapterVE.Update(row2);
MessageBox.Show("己建立新年度資料庫!");
}
catch (Exception ex)
{
MessageBox.Show("錯誤:" + ex.Message);
return;
}
MessageBox.Show("新年度資料設定完成!");
}
