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("新年度資料設定完成!"); }