//檢查客戶代碼是否存在
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();
}
}
}
