public ImportOrderControl()
{
InitializeComponent();
AdjustGridView();
db = new SMEntities();
newOrder();
}
private void BindData()
{
using (var db = new SMEntities())
{
// Bind DataSource
var so = db.ImportOrders.Where(o => true);
if (chbDate.Checked)
{
so = so.Where(o => o.date_import.Year >= dtpFrom.Value.Year &
o.date_import.Month >= dtpFrom.Value.Month &
o.date_import.Day >= dtpFrom.Value.Day &
o.date_import.Year <= dtpTo.Value.Year &
o.date_import.Month <= dtpTo.Value.Month &
o.date_import.Day <= dtpTo.Value.Day);
}
if (cbbStatus.SelectedItem != null && !cbbStatus.SelectedItem.Equals("All"))
{
so = so.Where(o => o.order_status.Equals(cbbStatus.SelectedItem.ToString()));
}
//if (txtUser.Text != string.Empty)
// so = so.Where(p => p.User.user_name.Contains(txtUser.Text));
//so = so.Include(o => o.User);
dataGridView.DataSource = so.ToList();
}
}
public SaleOrderControl()
{
InitializeComponent();
adjustGridView();
db = new SMEntities();
newOrder();
registerHandlers();
}
private void BindData()
{
using (var db = new SMEntities()){
var cust = db.Customers.Where(c => true);
if (txtCustName.Text != String.Empty)
cust = cust.Where(c => c.cust_name.Contains(txtCustName.Text));
dataGridView.DataSource = cust.ToList();
}
}
private void btnCreateNew_Click(object sender, EventArgs e)
{
using(var db = new SMEntities()){
DialogResult result = MessageBox.Show("Bạn muốn tạo mới khách hàng với thông tin như trên?",
"Confirmation",
MessageBoxButtons.YesNoCancel);
if(result == DialogResult.Yes)
{
if (txtCustName.Text == String.Empty || txtAddress.Text == String.Empty)
{
MessageBox.Show("Nhập thiếu thông tin khách hàng");
return;
}
Customer c = db.Customers.Create();
c.cust_name = txtCustName.Text;
c.cust_phone = txtPhone.Text;
c.cust_address = txtAddress.Text;
db.Customers.Add(c);
db.SaveChanges();
}
}
}
private void saveStocks(SMEntities db)
{
// Update Stocks
foreach (var item in saleOrder.SaleOrderItems)
{
Product prod = prodsList.Where(p => p.prod_id.Equals(item.prod_id))
.First();
prod.Stocks.OrderBy(s => s.base_price_by_stock);
// Proceed stocks
if (item.quantity_by_stock > 0)
{
int remainStockNeedProceed = item.quantity_by_stock;
// Take lowest price stock first
foreach (var stock in prod.Stocks)
{
if (stock.quantity_by_stock == 0)// Proceed next stock
continue;
if (remainStockNeedProceed <= 0)// Proceed complete
break;
remainStockNeedProceed = remainStockNeedProceed - stock.quantity_by_stock;
if (remainStockNeedProceed < 0)
{
stock.quantity_by_stock = -remainStockNeedProceed;
db.Entry(stock).State = EntityState.Modified;
break;// Proceed complete
}
else
{
// Proceed the stock
stock.quantity_by_stock = 0;
db.Entry(stock).State = EntityState.Modified;
}
}
}
// Proceed units
if (item.quantity_by_unit > 0 &&
item.quantity_by_unit < item.quantity_control)
{
int remainUnitNeedProceed = item.quantity_by_unit;
// Take lowest price stock first
foreach (var stock in prod.Stocks)
{
if (stock.quantity_by_unit == 0)// Proceed next stock
continue;
if (remainUnitNeedProceed <= 0)// Proceed complete
break;
remainUnitNeedProceed = remainUnitNeedProceed - stock.quantity_by_unit;
if (remainUnitNeedProceed <= 0)
{
stock.quantity_by_unit = -remainUnitNeedProceed;
db.Entry(stock).State = EntityState.Modified;
break;// Proceed complete
}
else
{
// Subtract from availabe stock
if (stock.quantity_by_stock >= 1)
{
stock.quantity_by_stock -= 1;
stock.quantity_by_unit = stock.quantity_control - remainUnitNeedProceed;
db.Entry(stock).State = EntityState.Modified;
}
//else continue proceed next stock
}
}
}
// Update quantity the product
prod.quantity_by_stock -= item.quantity_by_stock;
if (prod.quantity_by_unit >= item.quantity_by_unit)
prod.quantity_by_unit -= item.quantity_by_unit;
else
{
prod.quantity_by_stock -= 1;
prod.quantity_by_unit = prod.quantity_control + prod.quantity_by_unit
- item.quantity_by_unit;
}
db.Entry(prod).State = EntityState.Modified;
if (prod.quantity_by_stock < 0 || prod.quantity_by_unit < 0)
{
MessageBox.Show("Lỗi thực thi.");
}
}
}
private void BindData()
{
using (var db = new SMEntities())
{
// Bind DataSource
var proc = db.Products.Where(p => true);
if (txtId.Text != string.Empty)
proc = proc.Where(p => p.prod_id.Contains(txtId.Text));
if (txtName.Text != string.Empty)
proc = proc.Where(p => p.prod_name.Contains(txtName.Text));
dataGridView.DataSource = proc.ToList();
}
}
public ImportReportControl()
{
InitializeComponent();
db = new SMEntities();
adjustGridView();
}
private void toolStripImport_Click(object sender, EventArgs e)
{
OpenFileDialog dlg = new OpenFileDialog();
if (dlg.ShowDialog(this) == DialogResult.OK)
{
object missing = Type.Missing;
Excel.Range range;
Excel.Worksheet sheet = null;
Excel.Workbook wb = null;
Excel.Application excelApp = new Excel.Application();
wb = excelApp.Workbooks.Open(dlg.FileName,
missing, missing, missing,
missing, missing, missing, missing,
missing, missing, missing, missing,
missing, missing, missing);
sheet = (Excel.Worksheet)wb.Worksheets[1];
range = sheet.UsedRange;
if (range != null)
{
using (var db = new SMEntities())
{
// Bind DataSource
int nRows = range.Rows.Count;
int num;
decimal d;
bool parsed;
for (int iRow = 2; iRow <= nRows; iRow++)
{
Product p = new Product();
// Get ID
range = (Excel.Range)sheet.Cells[iRow, 2];
p.prod_id = range.Text;
// Get Name
range = (Excel.Range)sheet.Cells[iRow, 3];
p.prod_name = range.Text;
// Get Quality_control
range = (Excel.Range)sheet.Cells[iRow, 4];
parsed = int.TryParse(range.Text, out num);
if (parsed)
{
p.quantity_control = num;
}
else p.quantity_control = 1;
// Get Sale_price_by_stock
range = (Excel.Range)sheet.Cells[iRow, 5];
parsed = decimal.TryParse(range.Text, out d);
if (parsed)
{
p.sale_price_by_stock = d;
}
else p.sale_price_by_stock = 0;
// Set sale_price_by_unit
range = (Excel.Range)sheet.Cells[iRow, 6];
parsed = decimal.TryParse(range.Text, out d);
if (parsed)
{
p.sale_price_by_unit = d;
}
else p.sale_price_by_unit = 0;
// Check as if product has been existed
var prod = db.Products.Where(t => t.prod_id.Equals(p.prod_id));
if (!prod.Any())//Not exist
{
db.Products.Add(p);
db.SaveChanges();
}
}
}
}
wb.Close();
excelApp.Quit();
Marshal.ReleaseComObject(wb);
Marshal.ReleaseComObject(sheet);
}
}
private void toolStripButton1_Click(object sender, EventArgs e)
{
if(txtId.Text == string.Empty || txtName.Text == string.Empty ||
txtQC.Text == string.Empty || txtSalePriceStock.Text == string.Empty)
{
MessageBox.Show("Chưa nhập đủ dữ liệu sản phẩm");
return;
}
using (var db = new SMEntities())
{
// Bind DataSource
Product p = new Product();
p.prod_id = txtId.Text.ToString();
p.prod_name = txtName.Text.ToString();
p.quantity_control = int.Parse(txtQC.Text.ToString());
p.sale_price_by_stock = decimal.Parse(txtSalePriceStock.Text.ToString());
p.sale_price_by_unit = decimal.Parse(txtSalePriceUnit.Text.ToString());
// Get default values
p.quantity_by_stock = 0;
p.quantity_by_unit = 0;
p.date_added = DateTime.Now;
p.date_modified = p.date_added;
// Check as if products has been existed
var query = db.Products.Where(t => t.prod_id.Equals(p.prod_id));
if (!query.Any())// Not exist
{
db.Products.Add(p);
db.SaveChanges();
clearEntries();
}
else
{
// Accept change Name and sale price
var result = MessageBox.Show(this, "Sản phẩm đã có trong dữ liệu.\n Bạn có muốn lưu tên và giá bán hiện tại?",
"Lưu sản phẩm hàng",
MessageBoxButtons.YesNo);
if (result == DialogResult.Yes)
{
Product updatePro = query.First();
updatePro.prod_name = p.prod_name;
updatePro.sale_price_by_stock = p.sale_price_by_stock;
updatePro.sale_price_by_unit = p.sale_price_by_unit;
db.SaveChanges();
clearEntries();
}
}
}
}
private void setProductInfo(Product product)
{
txtId.Text = product.prod_id.ToString();
txtName.Text = product.prod_name.ToString();
txtQC.Text = product.quantity_control.ToString();
txtSalePriceStock.Text = product.sale_price_by_stock.ToString();
txtSalePriceUnit.Text = product.sale_price_by_unit.ToString();
using (var db = new SMEntities())
{
Product prod = db.Products.Where(p => p.prod_id.Equals(product.prod_id))
.Include(p => p.Stocks)
.First();
prod.Stocks.OrderByDescending(s => s.quantity_by_stock);
dataGridView.DataSource = prod.Stocks.ToList();
decimal total = 0;
foreach (var stock in prod.Stocks)
{
total += stock.base_price_by_stock * stock.quantity_by_stock +
stock.base_price_by_unit * stock.quantity_by_unit;
}
lblTotal.Text = total.ToString("#,##0.000");
}
}
private void saveStock(SMEntities db)
{
// Save to Stocks
var stockList = db.Stocks;
foreach (var item in importOrder.ImportOrderItems)
{
// Get stock has same prod_id & base_price_by_unit to item
var stock = stockList.Where(s => s.prod_id.Equals(item.prod_id)
&& s.base_price_by_unit.Equals(item.base_price_by_unit));
if (stock.Any())// Exist
{
Stock st = stock.First();
st.quantity_by_stock += item.quantity_by_stock;
st.quantity_by_unit += item.quantity_by_unit;
}
else// Create new stock for the product
{
Stock newStock = db.Stocks.Create();
newStock.prod_id = item.prod_id;
newStock.Product = item.Product;
newStock.quantity_by_stock = item.quantity_by_stock;
newStock.quantity_by_unit = item.quantity_by_unit;
newStock.base_price_by_stock = item.base_price_by_stock;
newStock.base_price_by_unit = item.base_price_by_unit;
newStock.quantity_control = item.quantity_control;
newStock.date_expired = item.date_expired;
// Save to Stocks
db.Stocks.Add(newStock);
}
// Update quantity on Product
var prod = db.Products.Find(item.prod_id);
prod.quantity_by_stock += item.quantity_by_stock;
prod.quantity_by_unit += item.quantity_by_unit;
prod.date_modified = DateTime.Now;
//db.Products.Attach(prod);
db.Entry(prod).State = EntityState.Modified;
}
}
private void BindData()
{
using (var db = new SMEntities())
{
var prods = db.Products.Where(c => true);
if (txtProductID.Text != String.Empty)
prods = prods.Where(c => c.prod_id.Contains(txtProductID.Text));
if (txtProductName.Text != String.Empty)
prods = prods.Where(c => c.prod_name.Contains(txtProductName.Text));
dataGridView.DataSource = prods.ToList();
dataGridView.ClearSelection();
}
}
private void BindData()
{
using (var db = new SMEntities())
{
// Bind DataSource
var so = db.SaleOrders.Where(o => true);
if (txtCustomer.Text != string.Empty)
so = so.Where(p => p.Customer.cust_name.Contains(txtCustomer.Text));
if (chbDate.Checked)
{
so = so.Where(o => o.date_ship.Year >= dtpFrom.Value.Year &
o.date_ship.Month >= dtpFrom.Value.Month &
o.date_ship.Day >= dtpFrom.Value.Day &
o.date_ship.Year <= dtpTo.Value.Year &
o.date_ship.Month <= dtpTo.Value.Month &
o.date_ship.Day <= dtpTo.Value.Day);
}
if (cbbShipStatus.SelectedItem != null && !cbbShipStatus.SelectedItem.Equals("All"))
{
so = so.Where(o => o.order_status.Equals(cbbShipStatus.SelectedItem.ToString()));
}
so = so.Include(o => o.Customer)
.Include(o => o.User);
foreach (var s in so)
{
s.CustomerName = s.Customer.cust_name;
}
dataGridView.DataSource = so.ToList();
}
}