private void Store_Load(String id)
{
try
{
isEdit = true;
this.titlePanel.Text = "Edit Product";
db = new DbConnectorClass();
SqlDataReader dbReader = db.RunQuery("select * from dbo.store where store_id = " + id);
if (dbReader.Read())
{
this.dbId = id;
this.storeNameTxt.Text = db.NullToNA(dbReader, "store_name");
this.storePhoneTxt.Text = db.NullToNA(dbReader, "store_phone");
this.storeAddressTxt.Text = db.NullToNA(dbReader, "store_address");
this.storeFaxTxt.Text = db.NullToNA(dbReader, "store_fax");
this.contactNameTxt.Text = db.NullToNA(dbReader, "contact_name");
this.contactPhoneTxt.Text = db.NullToNA(dbReader, "contact_phone");
this.storeDetailTxt.Text = db.NullToNA(dbReader, "store_detail");
Object isMarketValue = db.NullToNA(dbReader, "isMarket");
this.isMarket.Checked = (isMarketValue.Equals("1")) ? true : false;
}
SyncData();
dbReader.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public void OrderLoad(String id)
{
try
{
db = new DbConnectorClass();
adapter = new SqlDataAdapter("SELECT Product, Box, Each, Pound," +
"Price, ((Box+Each+Pound) * price) AS Amount, Market, Route, Note " +
"FROM dbo.cart where (Box+Each+Pound) > 0 and order_id = " + id, db.GetConnection());
// Create one DataTable with one column.
this.DS = new DataSet();
adapter.Fill(DS);
this.orderDataView.Rows.Clear();
for (int i = 0; i < DS.Tables[0].Rows.Count; i++)
{
DataRow myRow = DS.Tables[0].Rows[i];
this.orderDataView.Rows.Add(myRow[PRODUCT], myRow[BOX], myRow[EACH], myRow[POUND], myRow[PRICE], myRow[AMOUNT], myRow[MARKET], myRow[ROUTE], myRow[NOTE]);
double qty = getQty(myRow[BOX], myRow[EACH], myRow[POUND]);
qtyList.Add(myRow[PRODUCT], myRow[BOX]);
if (myRow[ROUTE] != null)
{
String routeVal = myRow[ROUTE].ToString();
this.RouteComboBox.SelectedItem = myRow[ROUTE].ToString();
}
}
dbReader = db.RunQuery("select * from dbo.store as s inner join dbo.order_list as o " +
"on s.store_id = o.store_id where order_id = " + id + ";");
if (dbReader.Read())
{
this.StoreList.SelectedIndex = this.StoreList.FindString(
db.NullToNA(dbReader, "store_id") + ": " + db.NullToNA(dbReader, "store_name") + ", (Contact: " + db.NullToNA(dbReader, "contact_name") + ")");
this.DeliveryDate.Value = Convert.ToDateTime(db.NullToNA(dbReader, "delivery_date"));
this.isMarket = db.NullToNA(dbReader, "isMarket").Equals('1') ? true: false;
}
dbReader.Close();
//this.orderDataView.DataSource = DS.Tables[0];
this.orderDataView.AutoGenerateColumns = true;
this.orderDataView.AutoResizeColumns();
UpdateBalance();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public void OrderLoad(bool isBuy, bool isSell, DateTime searchDate, String storeId)
{
InitializeComponent();
if (!searchDate.ToString("yyyy-MM-dd").Equals("0001-01-01"))
{
this.DeliveryDate.Value = searchDate;
}
this.OrderSearchDate = searchDate;
this.store_id = storeId;
OrderLoad(isBuy, isSell);
if (storeId != null && !storeId.Equals(""))
{
SqlDataReader dbReader = db.RunQuery("select * from dbo.store where store_id = " + storeId + ";");
if (dbReader.Read())
{
this.StoreList.SelectedIndex = this.StoreList.FindString(db.NullToNA(dbReader, "store_name"));
}
dbReader.Close();
}
}
private void SetComboBox()
{
try
{
db = new DbConnectorClass();
dbReader = db.RunQuery("select * from dbo.product order by product asc");
while (dbReader.Read())
{
(this.orderDataView.Columns[0] as DataGridViewComboBoxColumn)
.Items.Add(db.NullToNA(dbReader, "product"));
}
dbReader.Close();
//use count to get order by popular store
dbReader = db.RunQuery("select s.store_id, store_name, store_phone, store_address, contact_name, contact_phone, store_detail, store_fax, isMarket, count(*) as count " +
" from dbo.store as s full outer join dbo.order_list as o on s.store_id = o.store_id " +
" group by s.store_id, store_name, store_phone, store_address, contact_name, contact_phone, store_detail, store_fax, isMarket order by count(*) desc; ");
while (dbReader.Read())
{
ComboboxItem comboItem = new ComboboxItem
{
Text = db.NullToNA(dbReader, "store_id") + ": " + db.NullToNA(dbReader, "store_name") + ", (Contact: " + db.NullToNA(dbReader, "contact_name") + ")",
Value = db.NullToNA(dbReader, "isMarket")
};
this.StoreList.Items.Add(comboItem);
}
this.StoreList.AutoCompleteMode = AutoCompleteMode.Append;
this.StoreList.DropDownStyle = ComboBoxStyle.DropDownList;
this.StoreList.AutoCompleteSource = AutoCompleteSource.ListItems;
dbReader.Close();
this.orderDataView.EditingControlShowing +=
new DataGridViewEditingControlShowingEventHandler(DataGridView_EditingControlShowing);
this.orderDataView.CellValueChanged +=
new DataGridViewCellEventHandler(DataGridView1_CellValueChanged);
this.orderDataView.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void MyStore_Load(object sender, EventArgs e)
{
try
{
db = new DbConnectorClass();
SqlDataReader dbReader = db.RunQuery("select * from dbo.store where store_id = 1");
if (dbReader.Read())
{
this.storeNameTxt.Text = db.NullToNA(dbReader, "store_name");
this.storePhoneTxt.Text = db.NullToNA(dbReader, "store_phone");
this.storeAddressTxt.Text = db.NullToNA(dbReader, "store_address");
this.storeFaxTxt.Text = db.NullToNA(dbReader, "store_fax");
this.storeDetailTxt.Text = db.NullToNA(dbReader, "store_detail");
}
SyncData();
dbReader.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void SelectedCatagory(DataGridViewRow row, string catagoryName)
{
row.Cells[5].Value = 0;
(row.Cells[5] as DataGridViewComboBoxCell).Items.Clear();
if (!row.Cells[4].Value.Equals(""))
{
SqlDataReader dbReader = db.RunQuery("select distinct SubCatagory from dbo.Catagory where Catagory = '" + catagoryName.Trim() + "'");
while (dbReader.Read())
{
(row.Cells[5] as DataGridViewComboBoxCell).Items.Add(db.NullToNA(dbReader, "SubCatagory").Trim());
}
dbReader.Close();
}
}
private void ProductSelectionChanged(object sender, EventArgs e)
{
String prodName = (String)((ComboBox)sender).SelectedItem;
int rowIndex = (int)((DataGridViewComboBoxEditingControl)sender).EditingControlRowIndex;
try
{
db = new DbConnectorClass();
dbReader = db.RunQuery("select * from dbo.product where product ='" + prodName + "';");
if (dbReader.Read())
{
//product, qty, price, amount, market, note
Object[] values = { db.NullToNA(dbReader, "product"), 0,
"$" + db.NullToNA(dbReader, "price"), 0,"",db.NullToNA(dbReader, "note") };
orderDataView.Rows[rowIndex].SetValues(values);
}
dbReader.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public ProductList()
{
InitializeComponent();
//ProductApi.GetProducts();
db = new DbConnectorClass();
SqlDataReader dbReader = db.RunQuery("select Catagory from dbo.Catagory group by Catagory order by CASE Catagory " +
"WHEN 'MEAT' THEN 1 WHEN 'FROZEN' THEN 2 WHEN 'PRODUCE' THEN 3 WHEN 'GROCERY' THEN 4 WHEN 'FRUIT' THEN 5 ELSE 6 END, Catagory");
(this.ProductDataView.Columns[4] as DataGridViewComboBoxColumn).Items.Clear();
while (dbReader.Read())
{
(this.ProductDataView.Columns[4] as DataGridViewComboBoxColumn).Items.Add(db.NullToNA(dbReader, "Catagory").Trim());
this.CatagoryBox.Items.Add(db.NullToNA(dbReader, "Catagory").Trim());
}
dbReader.Close();
GetProductList();
AddBtns();
this.MaximizedBounds = Screen.GetWorkingArea(this);
}
private void saveBtn_Click(object sender, EventArgs e)
{
CheckedListBox[] boxList = new CheckedListBox[5];
boxList.SetValue(this.MeatListBox, 0);
boxList.SetValue(this.FrozenListBox, 1);
boxList.SetValue(this.GroceryListBox, 2);
boxList.SetValue(this.ProduceListBox, 3);
boxList.SetValue(this.EtcListBox, 4);
DataGridView dataView = this.co.getOrderDataView();
//use products to figure out deleted product
for (int b = 0; b < boxList.Length; b++)
{
CheckedListBox listBox = boxList[b];
for (int i = 0; i < listBox.CheckedItems.Count; i++)
{
DataRowView item = (DataRowView)listBox.CheckedItems[i];
String prodName = (String)item.Row.ItemArray[1];
try
{
bool isFound = false;
for (int k = 0; k < dataView.Rows.Count; k++)
{
DataGridViewCellCollection cells = dataView.Rows[k].Cells;
if (cells[0].Value.Equals(prodName))
{
this.products.Remove(prodName);
isFound = true;
break;
}
}
if (!isFound)
{
db = new DbConnectorClass();
SqlDataReader dbReader = db.RunQuery("select * from dbo.product where product ='" + prodName + "';");
if (dbReader.Read())
{
int box = 0;
int each = 0;
int pound = 0;
String price = "$" + db.NullToNA(dbReader, "price");
int amount = 0;
String market = "";
String note = db.NullToNA(dbReader, "note");
//add only new items
dataView.Rows.Add(prodName, box, each, pound, price, amount, market, this.routeValue, note);
}
dbReader.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
//remove unchecked item from order list
for (int k = 0; k < this.products.Count; k++)
{
for (int u = 0; u < dataView.Rows.Count; u++)
{
if (dataView.Rows[u].Cells[0].Value.Equals(this.products[k]))
{
dataView.Rows.RemoveAt(u);
break;
}
}
}
this.Close();
}
private void saveData(bool saveFromBtn)
{
try
{
if (IsWeekend())
{
MessageBox.Show("You cannot make an order on Weekend ", "Choose Again!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
else
{
var x = DialogResult.None;
if (saveFromBtn)
{
x = MessageBox.Show("Do you want to save? ", "Save", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
}
else
{
x = DialogResult.Yes;
}
if (DialogResult.Yes == x && !CheckIfEmpty())
{
String sqlQuery = "";
String routeIdx = "" + (this.RouteComboBox.SelectedIndex + 1);
if (this.orderId == null)
{
sqlQuery = "INSERT INTO dbo.order_list " +
"(store_id, ordered_date, delivery_date, total) VALUES " +
"('" + (this.StoreList.Text.Split(':')[0]) + "', " +
" getDATE(), " +
" '" + Convert.ToDateTime(this.DeliveryDate.Value.ToString()).ToString("yyyy-MM-dd") + "', " +
" '" + this.TotalTxt.Text + "') ";
db.RunQuery(sqlQuery).Close();
SqlDataReader dbReader = db.RunQuery("Select top 1 order_id from dbo.order_list order by order_id desc");
if (dbReader.Read())
{
//product, qty, price, amount, market, note
this.orderId = (String)db.NullToNA(dbReader, "order_id");
}
dbReader.Close();
for (int i = 0; i < this.orderDataView.Rows.Count - 1; i++)
{
DataGridViewRow row = this.orderDataView.Rows[i];
String product = row.Cells[PRODUCT].Value.ToString();
String box = row.Cells[BOX].Value.ToString();
String each = row.Cells[EACH].Value.ToString();
String pound = row.Cells[POUND].Value.ToString();
String price = row.Cells[PRICE].Value.ToString().Substring(1);
String market = row.Cells[MARKET].Value.ToString();
String note = row.Cells[NOTE].Value.ToString();
String route = "";
if (row.Cells[ROUTE].Value != null)
{
route = row.Cells[ROUTE].Value.ToString();
}
else if (!routeIdx.Equals("0"))
{
route = routeIdx;
}
string InsertSql = "INSERT INTO dbo.cart(box, each, pound, product, price, market, note, route, order_id) VALUES(" +
"'" + box + "', '" + each + "', '" + pound + "', N'" + product + "', '" + price + "', N'" +
market + "', N'" + note + "', '" + route + "', '" + this.orderId + "')";
db.RunQuery(InsertSql).Close();
String buyOrSell = this.isMarket ? "+" : "-";
string updateQuantity = "UPDATE dbo.product set quantity = (quantity " + buyOrSell + " " + box + ") where product ='" + product + "'";
db.RunQuery(updateQuantity).Close();
}
}
else
{
sqlQuery = "UPDATE dbo.order_list set " +
"delivery_date = '" + Convert.ToDateTime(this.DeliveryDate.Value.ToString()).ToString("yyyy-MM-dd") + "', " +
"total = '" + this.TotalTxt.Text + "' WHERE order_id= " + this.orderId;
adapter.Update(this.DS);
db.RunQuery(sqlQuery).Close();
for (int i = 0; i < this.orderDataView.Rows.Count; i++)
{
DataGridViewRow row = this.orderDataView.Rows[i];
String product = row.Cells[PRODUCT].Value.ToString();
String box = row.Cells[BOX].Value.ToString();
String each = row.Cells[EACH].Value.ToString();
String pound = row.Cells[POUND].Value.ToString();
String price = row.Cells[PRICE].Value.ToString().Substring(1);
String market = row.Cells[MARKET].Value.ToString();
String note = row.Cells[NOTE].Value.ToString();
String route = "";
if (row.Cells[ROUTE].Value != null)
{
route = row.Cells[ROUTE].Value.ToString();
}
else if (!routeIdx.Equals("0"))
{
route = routeIdx;
}
string whereStr = " WHERE product='" + product + "' and order_id='" + this.orderId + "'";
string checkExist = "SELECT * FROM dbo.cart " + whereStr;
dbReader = db.RunQuery(checkExist);
string InsertSql = "";
if (!dbReader.Read())
{
InsertSql = "INSERT INTO dbo.cart(box, each, pound, product, price, market, note, route, order_id) VALUES(" +
"'" + box + "','" + each + "','" + pound + "', N'" + product + "', '" + price + "', N'" +
market + "', N'" + note + "', '" + route + "', '" + this.orderId + "')";
}
else
{
InsertSql = "UPDATE dbo.cart set box='" + box +
"', price='" + price + "', market=N'" + market + "', note=N'" + note +
"', route='" + route + "' " + whereStr;
}
dbReader.Close();
db.RunQuery(InsertSql).Close();
String updateQuantity = "";
String buyOrSell = this.isMarket ? "-" : "+";
int diff = Int32.Parse(box);
if (qtyList.ContainsKey(product))
{
diff = Int32.Parse(box) - Int32.Parse(qtyList[product].ToString());
}
if (diff != 0)
{
updateQuantity = "UPDATE dbo.product set quantity = (quantity " + buyOrSell + " " + diff + ") where product ='" + product + "'";
db.RunQuery(updateQuantity).Close();
}
}
}
// need to close createStore form after click 'OK' button
isSave = true;
if (saveFromBtn)
{
if (this.ol != null)
{
this.ol.OrderLoad(true, true);
}
this.Close();
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void reportViewer1_Load(object sender, EventArgs e)
{
string storeIdLbl = "";
string orderIdLbl = "";
string totalLbl = "";
string custNameLbl = "";
string custAddrLbl = "";
string custPhoneLbl = "";
string storeNameLbl = "";
string addressLbl = "";
string telLabel = "";
string faxLbl = "";
db = new DbConnectorClass();
dbReader = db.RunQuery("select * from dbo.store as s inner join dbo.order_list as o " +
"on s.store_id = o.store_id where order_id = " + orderId + ";");
if (dbReader.Read())
{
storeIdLbl = db.NullToNA(dbReader, "store_id").PadLeft(5, '0');
orderIdLbl = this.orderId.PadLeft(5, '0');
delieveryDateLbl = db.NullToNA(dbReader, "delivery_date");
totalLbl = this.total;
custNameLbl = db.NullToNA(dbReader, "store_name");
custAddrLbl = db.NullToNA(dbReader, "store_address");
custPhoneLbl = db.NullToNA(dbReader, "store_phone");
}
dbReader.Close();
dbReader = db.RunQuery("select * from dbo.store where store_id = 1;");
if (dbReader.Read())
{
storeNameLbl = db.NullToNA(dbReader, "store_name");
addressLbl = db.NullToNA(dbReader, "store_address");
telLabel = db.NullToNA(dbReader, "store_phone");
faxLbl = db.NullToNA(dbReader, "store_fax");
}
dbReader.Close();
DateTime parsedDate = DateTime.Parse(delieveryDateLbl);
delieveryDateLbl = parsedDate.ToString("MM-dd-yyyy");
Microsoft.Reporting.WinForms.ReportParameter[] param = new Microsoft.Reporting.WinForms.ReportParameter[]
{
new Microsoft.Reporting.WinForms.ReportParameter("orderId", orderIdLbl),
new Microsoft.Reporting.WinForms.ReportParameter("storeId", storeIdLbl),
new Microsoft.Reporting.WinForms.ReportParameter("delieveryDate", delieveryDateLbl),
new Microsoft.Reporting.WinForms.ReportParameter("total", totalLbl),
new Microsoft.Reporting.WinForms.ReportParameter("custName", custNameLbl),
new Microsoft.Reporting.WinForms.ReportParameter("custAddr", custAddrLbl),
new Microsoft.Reporting.WinForms.ReportParameter("custPhone", custPhoneLbl),
new Microsoft.Reporting.WinForms.ReportParameter("storeName", storeNameLbl),
new Microsoft.Reporting.WinForms.ReportParameter("storeAddr", addressLbl),
new Microsoft.Reporting.WinForms.ReportParameter("storeTel", telLabel),
new Microsoft.Reporting.WinForms.ReportParameter("storeFax", faxLbl)
};
this.reportViewer1.LocalReport.SetParameters(param);
try
{
db = new DbConnectorClass();
adapter = new SqlDataAdapter("SELECT Product, Box, Each, Pound," +
"Price, (price * (Box + Each + Pound)) AS Amount, Note " +
"FROM dbo.cart where (box + each + pound) > 0 and order_id = " + orderId, db.GetConnection());
// Create one DataTable with one column.
this.DS = new DataSet();
adapter.Fill(DS);
ReportDataSource rds = new ReportDataSource("Order", DS.Tables[0]);
this.reportViewer1.LocalReport.DataSources.Clear();
this.reportViewer1.LocalReport.DataSources.Add(rds);
this.reportViewer1.LocalReport.Refresh();
this.reportViewer1.RefreshReport();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
this.reportViewer1.RefreshReport();
}