//檢查客戶代碼是否存在 public bool CheckCustomerID(string CustomerID) { bool FCheck = false; using (var context = new XINEntities()) { var qry = (from R in context.Customer where R.CustomerID == CustomerID select R.CustomerID).FirstOrDefault(); if (qry != null) { FCheck = true; } } if (!FCheck) { if (CustomerID == null) { MessageBox.Show("請輸入客戶代碼。", "參考錯誤", MessageBoxButtons.OK, MessageBoxIcon.Stop); } else { MessageBox.Show(string.Format("客戶代碼{0}輸入錯誤。", CustomerID), "參考錯誤", MessageBoxButtons.OK, MessageBoxIcon.Stop); } } return(FCheck); }
//增加庫存量 public void IncStock(XINEntities _context, string ProductID, int Quantity) { if (ProductID == null) { Exception ex = new Exception("計算庫存量的資料不可以含有空值!"); ex.Source = "null"; throw ex; } //定義影響的資料列數 int rowsAffected; //更新庫存量 rowsAffected = _context.ExecuteStoreCommand( "UPDATE Product SET Stock = Stock + {0} " + "WHERE ProductID = {1} ", Quantity, ProductID); //檢查影響的資料列數 if (rowsAffected == 0) { //修改失敗,舉發例外 Exception ex = new Exception("計算庫存量發生不可預期的錯誤!"); ex.Source = "Quantity"; throw ex; } }
//更新畫面 private void DataRefresh() { try { using (var context = new XINEntities()) { var qdata = from q in context.Customer select q; } dt = dao.Query("SELECT * FROM Sheet101"); var data = from q in dt.AsEnumerable() select new { id = q.Field <string>("Smid"), c = q.Field <int>("Car") }; //var dvgdata = from q in dt.AsEnumerable() // select new // { // DeliveryID = q.Field<string>("DeliveryID"), // DeliveryDate = q.Field<DateTime>("DeliveryDate") // }; this.dgvDelivery.DataSource = data.ToList(); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } }
private void btnPrior_Click(object sender, EventArgs e) { //按下btnPrior按鈕 if (BeforeScroll()) { using (var context = new XINEntities()) { //找出指定資料表的的目前記錄的上一筆記錄 string ESQL = "SELECT T." + FIDFieldName + " AS ID " + "FROM XINEntities." + FTableName + " AS T " + "WHERE T." + FIDFieldName + " < '" + FPrimaryID + "'" + "ORDER BY T." + FIDFieldName + " DESC"; DbDataRecord rec = new ObjectQuery <DbDataRecord> (ESQL, context).FirstOrDefault(); if (rec != null) { if (rec[0] != null) { PrimaryID = (string)(rec[0]); BeforeLoad(); FillData(); AfterLoad(); } } } TBStatus = 0; AfterScroll(); } SetButtons(); }
public override void GenerateReport() { try { using (var context = new XINEntities()) { var qry = from P in context.Product where P.LastDeliveryDate <= FRefrenceDate && P.Stock > 0 orderby P.ProductID select P; ProductBindingSource.DataSource = qry; } //建立報表參數 ReportParameter ReportCondi = new ReportParameter(); ReportCondi.Name = "ReportCondi"; ReportCondi.Values.Add("參考日期:" + FRefrenceDate.ToShortDateString()); //將報表參數反映到報表底稿上的報表參數 this.reportViewerBase.LocalReport.SetParameters( new ReportParameter[] { ReportCondi }); } catch (Exception ex) { MessageBox.Show(ex.Message); } }
private void btnLast_Click(object sender, EventArgs e) { //按下btnLast按鈕 if (BeforeScroll()) { using (var context = new XINEntities()) { //找出指定資料表的第一筆記錄 string ESQL = "SELECT MAX(T." + FIDFieldName + ") AS ID " + "FROM XINEntities." + FTableName + " AS T "; DbDataRecord rec = new ObjectQuery <DbDataRecord> (ESQL, context).FirstOrDefault(); if (rec[0] != null) { PrimaryID = (string)(rec[0]); BeforeLoad(); FillData(); AfterLoad(); } } TBStatus = 0; AfterScroll(); } SetButtons(); }
private void btnOk_Click(object sender, EventArgs e) { CanClose = false; using (var context = new XINEntities()) { var qry = (from M in context.XINUsers where M.EmployeeNo == txtEmployeeNo.Text && M.PasswordCode == txtPasswordCode.Text select new { M.EmployeeNo, M.EmployeeName }).FirstOrDefault(); if (qry == null) { //找不到記錄 MessageBox.Show("使用者代碼或密碼錯誤!", "認證失敗", MessageBoxButtons.OK, MessageBoxIcon.Error); fmMain.FPowerCheck = false; iCheckTimes = iCheckTimes + 1; if (iCheckTimes == 3) { MessageBox.Show("認證失敗已達3次,系統將結束!", "認證失敗", MessageBoxButtons.OK, MessageBoxIcon.Error); CanClose = true; } } else { fmMain.FPowerCheck = true; fmMain.FEmployeeNo = qry.EmployeeNo; fmMain.FEmployeeName = qry.EmployeeName; CanClose = true; } } }
//檢查廠商代碼是否存在 public bool CheckSupplierID(string SupplierID) { bool FCheck = false; using (var context = new XINEntities()) { var qry = (from S in context.Supplier where S.SupplierID == SupplierID select S.SupplierID).FirstOrDefault(); if (qry != null) { FCheck = true; } } if (!FCheck) { if (SupplierID == null) { MessageBox.Show("請輸入廠商代碼。", "參考錯誤", MessageBoxButtons.OK, MessageBoxIcon.Stop); } else { MessageBox.Show(string.Format("廠商代碼{0}輸入錯誤。", SupplierID), "參考錯誤", MessageBoxButtons.OK, MessageBoxIcon.Stop); } } return(FCheck); }
private void btnDelete_Click(object sender, EventArgs e) { if (!XIN.CheckAuthority(FProgramID, 3)) { MessageBox.Show("您沒有刪除記錄的權限。", "操作禁止", MessageBoxButtons.OK, MessageBoxIcon.Stop); return; } //按下btnDelete按鈕 if (BeforeDelete() && (PrimaryID != null)) { if (DeleteData()) { string ESQL; DbDataRecord rec; using (var context = new XINEntities()) { //找出指定資料表的目前被刪除的記錄的下一筆記錄 ESQL = "SELECT T." + FIDFieldName + " AS ID " + "FROM XINEntities." + FTableName + " AS T " + "WHERE T." + FIDFieldName + " > '" + FDeleteID + "'" + "ORDER BY T." + FIDFieldName; rec = new ObjectQuery <DbDataRecord> (ESQL, context).FirstOrDefault(); if (rec != null) { PrimaryID = (string)(rec[0]); BeforeLoad(); FillData(); AfterLoad(); } else { //找出指定資料表的的目前記錄的上一筆記錄 ESQL = "SELECT T." + FIDFieldName + " AS ID " + "FROM XINEntities." + FTableName + " AS T " + "WHERE T." + FIDFieldName + " < '" + FDeleteID + "'" + "ORDER BY T." + FIDFieldName + " DESC"; rec = new ObjectQuery <DbDataRecord> (ESQL, context).FirstOrDefault(); if (rec != null) { PrimaryID = (string)(rec[0]); BeforeLoad(); FillData(); AfterLoad(); } } } SetControls(); AfterDelete(); } } SetButtons(); }
public override void GenerateReport() { using (var context = new XINEntities()) { var qry = from P in context.Product orderby P.ProductID select P; ProductBindingSource.DataSource = qry; } }
public override void GenerateReport() { using (var context = new XINEntities()) { var qry = from S in context.Supplier orderby S.SupplierID select S; SupplierBindingSource.DataSource = qry; } }
private void GetAllSupplier() { using (var context = new XINEntities()) { //取得廠商資料表所有的記錄 var qry = context.Supplier.Execute(MergeOption.OverwriteChanges); //將取得的結果指派給BindingSource控制項的DataSource supplierBindingSource.DataSource = qry; } }
public override void GenerateReport() { using (var context = new XINEntities()) { var qry = from R in context.Customer orderby R.CustomerID select R; CustomerBindingSource.DataSource = qry; } }
private void SearchTextBox_TextChanged(object sender, EventArgs e) { if (SearchTextBox.Text == String.Empty) { //查詢資料為空字串,取得所有的廠商記錄 GetAllSupplier(); } else { switch (SearchByComboBox.SelectedIndex) { case 0: //依廠商編號查詢 using (var context = new XINEntities()) { //取得廠商資料表符合廠商編條件的記錄 var qry = context.Supplier .Execute(MergeOption.OverwriteChanges) .Where(M => M.SupplierID .StartsWith(SearchTextBox.Text)); //將取得的結果指派給BindingSource控制項的DataSource supplierBindingSource.DataSource = qry; } break; case 1: //依廠商簡稱查詢 using (var context = new XINEntities()) { //取得廠商資料表符合廠商簡稱條件的記錄 var qry = context.Supplier .Execute(MergeOption.OverwriteChanges) .Where(M => M.AttribName .StartsWith(SearchTextBox.Text)); //將取得的結果指派給BindingSource控制項的DataSource supplierBindingSource.DataSource = qry; } break; case 2: //依統一編號查詢 using (var context = new XINEntities()) { //取得廠商資料表符合統一編號條件的記錄 var qry = context.Supplier .Execute(MergeOption.OverwriteChanges) .Where(M => M.EarNo .StartsWith(SearchTextBox.Text)); //將取得的結果指派給BindingSource控制項的DataSource supplierBindingSource.DataSource = qry; } break; } } }
private void QryDeliveryData() { using (var context = new XINEntities()) { var qry = from M in context.Delivery join D in context.DeliveryDetails on M.DeliveryID equals D.DeliveryID join P in context.Product on D.ProductID equals P.ProductID where M.CustomerID == QryCustomerID && M.DeliveryDate >= BeginDateDateTimePicker.Value && M.DeliveryDate <= EndDateDateTimePicker.Value && M.DeliveryType == QryDeliveryType orderby M.DeliveryID, D.DeliverySeq select new { M.DeliveryDate, M.DeliveryID, D.DeliverySeq, D.ProductID, P.ProductName, P.Unit, D.Quantity, D.UnitPrice, D.Amount }; //定義儲存格的樣式(針對數值欄位) DataGridViewCellStyle columnStyle = new DataGridViewCellStyle(); //靠右顯示 columnStyle.Alignment = DataGridViewContentAlignment.MiddleRight; //格式顯示的內容,依數值,無小數點 columnStyle.Format = "N0"; //接收資料來源 DataGridView1.DataSource = qry; //欄位寬度 DataGridView1.Columns[0].Width = 100; DataGridView1.Columns[1].Width = 100; DataGridView1.Columns[2].Width = 65; DataGridView1.Columns[3].Width = 100; DataGridView1.Columns[4].Width = 250; DataGridView1.Columns[5].Width = 70; DataGridView1.Columns[6].Width = 70; DataGridView1.Columns[7].Width = 70; DataGridView1.Columns[8].Width = 90; //欄位抬頭 DataGridView1.Columns[0].HeaderText = "出貨日期"; DataGridView1.Columns[1].HeaderText = "出貨單號"; DataGridView1.Columns[2].HeaderText = "項次"; DataGridView1.Columns[3].HeaderText = "商品編號"; DataGridView1.Columns[4].HeaderText = "商品名稱"; DataGridView1.Columns[5].HeaderText = "單位"; DataGridView1.Columns[6].HeaderText = "數量"; DataGridView1.Columns[7].HeaderText = "單價"; DataGridView1.Columns[8].HeaderText = "金額"; //配置資料類型為數值欄位的顯示樣式 DataGridView1.Columns[6].DefaultCellStyle = columnStyle; DataGridView1.Columns[7].DefaultCellStyle = columnStyle; DataGridView1.Columns[8].DefaultCellStyle = columnStyle; } }
private void btnOK_Click(object sender, EventArgs e) { CanClose = false; try { if (NewPasswordTextBox.Text == "") { MessageBox.Show("新密碼不可空白!", "錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error); NewPasswordTextBox.Focus(); return; } if (NewPasswordTextBox.Text != ConfirmPasswordTextBox.Text) { MessageBox.Show("新密碼與確認新密碼不一致!", "錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error); NewPasswordTextBox.Focus(); return; } using (var context = new XINEntities()) { var qry = (from U in context.XINUsers where U.EmployeeNo == fmMain.FEmployeeNo && U.PasswordCode == OrignalPasswordTextBox.Text select U).FirstOrDefault(); if (qry == null) { MessageBox.Show("原來的密碼輸入錯誤!", "錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error); OrignalPasswordTextBox.Focus(); return; } //開始更新使用者的密碼 qry.PasswordCode = NewPasswordTextBox.Text; context.SaveChanges(); MessageBox.Show("密碼變更完成", "變更密碼", MessageBoxButtons.OK, MessageBoxIcon.Information); } } catch (Exception ex) { MessageBox.Show(ex.Message, "錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error); } //成功完成密碼變更,允許關閉視窗 CanClose = true; }
//檢查是否有指定程式的指定操作項目的執行權限 public bool CheckAuthority(string ProgramID, int Operation) { //Operation 1:新增 2:修改 3:刪除 4:列印 //預設擁有全部權限 bool FAuthority = true; //ADMIN不用檢查,擁有全部權限,直接離開 if (fmMain.FEmployeeNo == "ADMIN") { return(true); } using (var context = new XINEntities()) { var qry = (from A in context.UserAuthority where A.EmployeeNo == fmMain.FEmployeeNo && A.ProgramID == ProgramID select new { A.ProgramID, A.Append, A.Edit, A.Report } ).FirstOrDefault(); if (qry != null) { if (Operation == 1) { //新增權限 FAuthority = qry.Append; } else if (Operation == 2) { //修改權限 FAuthority = qry.Edit; } else if (Operation == 3) { //刪除權限(有新增權限就有刪除權限) FAuthority = qry.Append; } else { //列印權限 FAuthority = qry.Report; } } } return(FAuthority); }
private void SyncUserAuthority() { using (var context = new XINEntities()) { string EmployeeNo, ProgramID; try { //找出所有的程式代碼記錄 var qryPrograms = from P in context.Programs select P.ProgramID; //找出所有的使用者代碼記錄 var qrysUsers = from E in context.XINUsers select E.EmployeeNo; foreach (var P in qryPrograms) { ProgramID = (string)P; foreach (var U in qrysUsers) { EmployeeNo = (string)U; var qry = (from A in context.UserAuthority where A.EmployeeNo == EmployeeNo && A.ProgramID == ProgramID select A).FirstOrDefault(); if (qry == null) { //新增使用權限記錄 UserAuthority NewAuthority = new UserAuthority(); NewAuthority.EmployeeNo = EmployeeNo; NewAuthority.ProgramID = ProgramID; NewAuthority.Run = true; NewAuthority.Append = true; NewAuthority.Edit = true; NewAuthority.Report = true; context.AddObject("UserAuthority", NewAuthority); } } } context.SaveChanges(); } catch (Exception ex) { MessageBox.Show(ex.Message, "啟動錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error); } } }
//檢查商品是否存在(包含檢查是否停售) public bool CheckProductID(string ProductID, bool CheckStopSales = false) { bool FCheck = false; bool StopSales = false; using (var context = new XINEntities()) { var qry = (from P in context.Product where P.ProductID == ProductID select new { P.ProductID, P.StopSales }).FirstOrDefault(); if (qry != null) { FCheck = true; StopSales = (bool)qry.StopSales; } } if (!FCheck) { if (ProductID == null) { MessageBox.Show("請輸入商品編號。", "參考錯誤", MessageBoxButtons.OK, MessageBoxIcon.Stop); } else { MessageBox.Show(string.Format("商品編號{0}輸入錯誤。", ProductID), "參考錯誤", MessageBoxButtons.OK, MessageBoxIcon.Stop); } } else { if (CheckStopSales && StopSales) { MessageBox.Show(string.Format("商品編號{0}已經停止交易。", ProductID), "資料停用", MessageBoxButtons.OK, MessageBoxIcon.Stop); FCheck = false; } } return(FCheck); }
//檢查是否有執行指定程式代碼的執行權限 private bool CheckProgramRight(string ProgramID) { var blnRun = false; //檢查指定的程式是否有執行權限 using (var context = new XINEntities()) { var qryAuth = (from M in context.UserAuthority where M.EmployeeNo == FEmployeeNo && M.ProgramID == ProgramID select M).FirstOrDefault(); if (qryAuth != null) { blnRun = Convert.ToBoolean(qryAuth.Run); } } return(blnRun); }
private void SyncProgramsRight() { using (var context = new XINEntities()) { try { string sName, sProgramID; //從MenuStrip1控制項集合找出每一個控制項 foreach (var tsmi in MenuStrip1.Items) { if (!(tsmi is ToolStripItem)) { continue; } //找出MenuStrip1.Items的子控制項 foreach (var SubItem in ((ToolStripMenuItem)(tsmi)).DropDownItems) { //判斷找出的控制項是否為ToolStripMenuItem類別 if (!(SubItem is ToolStripMenuItem)) { continue; } sName = ((ToolStripMenuItem)SubItem).Name.Substring(2, 3); if ((sName == "Dtn") || (sName == "Txn") || (sName == "Rpt") || (sName == "Qry")) { //程式代碼 sProgramID = ((ToolStripMenuItem)SubItem).Name .Substring(2); //取得是否有執行程式的權限 ((ToolStripMenuItem)SubItem).Enabled = CheckProgramRight(sProgramID); } } } } catch (Exception ex) { MessageBox.Show(ex.Message, "啟動錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error); } } }
//取得商品售價 public int GetPrice(string ProductID) { int Price = 0; if (ProductID != null) { using (var context = new XINEntities()) { var qry = (from P in context.Product where P.ProductID == ProductID select P.Price).FirstOrDefault(); if (qry != null) { Price = (int)qry; } } } return(Price); }
//取得客戶地址 public string GetCustomerAddress(string CustomerID) { string CustomerAddress = ""; if (CustomerID != null) { using (var context = new XINEntities()) { var qry = (from R in context.Customer where R.CustomerID == CustomerID select R.DeliveryAddress).FirstOrDefault(); if (qry != null) { CustomerAddress = (string)qry; } } } return(CustomerAddress); }
//取得客戶聯絡人 public string GetCustomerJoinMan(string CustomerID) { string CustomerJoinMan = ""; if (CustomerID != null) { using (var context = new XINEntities()) { var qry = (from R in context.Customer where R.CustomerID == CustomerID select R.JoinMan).FirstOrDefault(); if (qry != null) { CustomerJoinMan = (string)qry; } } } return(CustomerJoinMan); }
//取得廠商地址 public string GetSupplierAddress(string SupplierID) { string SuuplierAddress = ""; if (SupplierID != null) { using (var context = new XINEntities()) { var qry = (from S in context.Supplier where S.SupplierID == SupplierID select S.DeliveryAddress).FirstOrDefault(); if (qry != null) { SuuplierAddress = (string)qry; } } } return(SuuplierAddress); }
//取得商品單位 public string GetUnit(string ProductID) { string Unit = ""; if (ProductID != null) { using (var context = new XINEntities()) { var qry = (from P in context.Product where P.ProductID == ProductID select P.Unit).FirstOrDefault(); if (qry != null) { Unit = (string)qry; } } } return(Unit); }
//取得廠商簡稱 public string GetSupplierAttribName(string SupplierID) { string SupplierAttribName = ""; if (SupplierID != null) { using (var context = new XINEntities()) { var qry = (from S in context.Supplier where S.SupplierID == SupplierID select S.AttribName).FirstOrDefault(); if (qry != null) { SupplierAttribName = (string)qry; } } } return(SupplierAttribName); }
private void SearchTextBox_TextChanged(object sender, EventArgs e) { if (SearchTextBox.Text == String.Empty) { //查詢資料為空字串,取得所有的廠商記錄 GetAllProduct(); } else { switch (SearchByComboBox.SelectedIndex) { case 0: //依商品編號查詢 using (var context = new XINEntities()) { //取得商品資料表商品編號條件的記錄 var qry = context.Product .Execute(MergeOption.OverwriteChanges) .Where(M => M.ProductID .StartsWith(SearchTextBox.Text)); //將取得的結果指派給BindingSource控制項的DataSource productBindingSource.DataSource = qry; } break; case 1: //依商品名稱查詢 using (var context = new XINEntities()) { //取得商品資料符合商品名稱條件的記錄 var qry = context.Product .Execute(MergeOption.OverwriteChanges) .Where(M => M.ProductName .StartsWith(SearchTextBox.Text)); //將取得的結果指派給BindingSource控制項的DataSource productBindingSource.DataSource = qry; } break; } } }
//減少庫存量 public void DecStock(XINEntities _context, string ProductID, int Quantity) { if (ProductID == null) { Exception ex = new Exception("計算庫存量的資料不可以含有空值!"); ex.Source = "null"; throw ex; } //重讀ObjectContext的庫存記錄 _context.Refresh(RefreshMode.StoreWins, _context.Product); //查詢指定品項的庫存量 var qryStock = (from P in _context.Product where P.ProductID == ProductID select P.Stock).FirstOrDefault(); int CurrQty = (int)qryStock; if ((CurrQty - Quantity) < 0) { Exception ex = new Exception(string.Format( "商品編號{0}的庫存量不足,無法出貨!", ProductID)); ex.Source = "Stock"; throw ex; } //定義影響的資料列數 int rowsAffected; //更新庫存量 rowsAffected = _context.ExecuteStoreCommand( "UPDATE Product SET Stock = Stock - {0} " + "WHERE ProductID = {1} ", Quantity, ProductID); //檢查影響的資料列數 if (rowsAffected == 0) { //修改失敗,舉發例外 Exception ex = new Exception("計算庫存量發生不可預期的錯誤!"); ex.Source = "Quantity"; throw ex; } }
private void SyncAdminUsers() { using (var context = new XINEntities()) { //檢查使用者代碼資料表是否有ADMIN的使用者 var qry = (from E in context.XINUsers where E.EmployeeNo == "ADMIN" select E.EmployeeNo).FirstOrDefault(); if (qry == null) { //新增ADMIN使用者 XINUsers NewXINUsers = new XINUsers(); NewXINUsers.EmployeeNo = "ADMIN"; NewXINUsers.EmployeeName = "系統管理員"; NewXINUsers.PasswordCode = "ADMIN"; //加入ADMIN使用者 context.AddObject("XINUsers", NewXINUsers); //更新資料 context.SaveChanges(); } } }