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);
}
}
private void Catagory_Load()
{
try
{
db = new DbConnectorClass();
adapter = new SqlDataAdapter("select catagory_id, Catagory, SubCatagory from dbo.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, SubCatagory", db.GetConnection());
// Create one DataTable with one column.
DataSet DS = new DataSet();
adapter.Fill(DS);
this.CatagoryDataView.Rows.Clear();
for (int i = 0; i < DS.Tables[0].Rows.Count; i++)
{
DataRow myRow = DS.Tables[0].Rows[i];
DataGridViewRow row = (DataGridViewRow)CatagoryDataView.Rows[0].Clone();
row.Cells[0].Value = myRow[0].ToString().Trim();
row.Cells[1].Value = myRow[1].ToString().Trim();
row.Cells[2].Value = myRow[2].ToString().Trim();
this.CatagoryDataView.Rows.Add(row);
}
// this.ProductDataView.DataSource = DS.Tables[0];
// this.ProductDataView.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public void GetStoreList(bool isMarket, bool isCustomer)
{
try
{
String whereStr = "";
if (isMarket && isCustomer)
{
whereStr = "";
}
else if (isMarket)
{
whereStr = "where isMarket = 1";
}
else if (isCustomer)
{
whereStr = "where isMarket = 0";
}
else
{
whereStr = "where isMarket = 2";
}
db = new DbConnectorClass();
adapter = new SqlDataAdapter("SELECT store_id AS 'Store Id', store_name AS 'Store Name'," +
"store_phone AS Phone, store_fax AS Fax, store_address AS Address, " +
"contact_name AS 'Contact Name', contact_phone AS 'Contact #', store_detail AS 'Store Detail', " +
"isMarket AS 'Is Market'" +
"FROM dbo.store " + whereStr + " order by store_name ", db.GetConnection());
DataSet DS = new DataSet();
adapter.Fill(DS);
this.StoreDataView.Rows.Clear();
for (int i = 0; i < DS.Tables[0].Rows.Count; i++)
{
DataRow myRow = DS.Tables[0].Rows[i];
DataGridViewRow row = (DataGridViewRow)StoreDataView.Rows[0].Clone();
row.Cells[STORE_ID].Value = myRow[STORE_ID].ToString();
row.Cells[STORE_NAME].Value = myRow[STORE_NAME].ToString().Trim();
row.Cells[STORE_PHONE].Value = myRow[STORE_PHONE].ToString().Trim();
row.Cells[STORE_FAX].Value = myRow[STORE_FAX].ToString().Trim();
row.Cells[STORE_ADDRESS].Value = myRow[STORE_ADDRESS].ToString().Trim();
row.Cells[CONTACT_NAME].Value = myRow[CONTACT_NAME].ToString().Trim();
row.Cells[CONTACT_PHONE].Value = myRow[CONTACT_PHONE].ToString().Trim();
row.Cells[STORE_NOTE].Value = myRow[STORE_NOTE].ToString().Trim();
DataGridViewCheckBoxCell chk = (DataGridViewCheckBoxCell)row.Cells[IS_MARKET];
String isMarketVal = myRow[IS_MARKET].ToString().Trim();
if (isMarketVal.Equals("1"))
{
chk.Value = chk.TrueValue;
}
else
{
chk.Value = chk.FalseValue;
}
this.StoreDataView.Rows.Add(row);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public void ProductLoad()
{
try
{
db = new DbConnectorClass();
setComboBox(this.MeatListBox, "MEAT");
setComboBox(this.FrozenListBox, "FROZEN");
setComboBox(this.GroceryListBox, "GROCERY");
setComboBox(this.ProduceListBox, "PRODUCE");
adapter = new SqlDataAdapter(
"Select product_id, product from dbo.product where (catagory != 'MEAT' and catagory != 'FROZEN' and catagory != 'GROCERY' and catagory != 'PRODUCE') order by product;", db.GetConnection());
// Create one DataTable with one column.
this.DS = new DataSet();
adapter.Fill(DS);
this.EtcListBox.DataSource = DS.Tables[0];
this.EtcListBox.DisplayMember = "product";
this.EtcListBox.ValueMember = "product_id";
setChecked(this.MeatListBox);
setChecked(this.FrozenListBox);
setChecked(this.GroceryListBox);
setChecked(this.ProduceListBox);
setChecked(this.EtcListBox);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public void OrderLoadWithOption()
{
bool isBuy = this.BuyCheckBox.Checked;
bool isSell = this.SellCheckBox.Checked;
try
{
db = new DbConnectorClass();
String whereStr = "";
if (isBuy && isSell)
{
whereStr = "";
}
else if (isBuy)
{
whereStr = "where isMarket = 1";
}
else if (isSell)
{
whereStr = "where isMarket = 0";
}
else
{
whereStr = "where isMarket = 2";
}
if (!this.OrderSearchDate.ToString("yyyy-MM-dd").Equals("0001-01-01"))
{
whereStr += " and delivery_date = '" + this.OrderSearchDate.ToString("yyyy-MM-dd") + "'";
}
if (!this.store_id.Equals(""))
{
whereStr += " and t1.store_id = '" + this.store_id + "'";
}
adapter = new SqlDataAdapter(
"Select order_id as 'Order Id', store_name as Store, store_phone as Phone, store_fax as Fax, store_address as Address, delivery_date as 'Delivery Date', ordered_date as 'Ordered Date', total as Total " +
"from dbo.order_list as t1 inner join dbo.store as t2 " +
"on t1.store_id = t2.store_id " + whereStr + " order by delivery_date desc, order_id desc;", db.GetConnection());
// Create one DataTable with one column.
this.DS = new DataSet();
adapter.Fill(DS);
this.OrderListView.DataSource = DS.Tables[0];
this.OrderListView.AutoGenerateColumns = true;
this.OrderListView.AutoResizeColumns();
this.OrderListView.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
}
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 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);
}
public void OrderLoad()
{
try
{
db = new DbConnectorClass();
String query = "select product, " +
"CONCAT(" +
"sum(case when route = 1 then box else 0 end), ' box, ', " +
"sum(case when route = 1 then each else 0 end), ' each, ', " +
"sum(case when route = 1 then pound else 0 end), ' pound') as 'route 1', " +
"CONCAT(" +
"sum(case when route = 2 then box else 0 end), ' box, ', " +
"sum(case when route = 2 then each else 0 end), ' each, ', " +
"sum(case when route = 2 then pound else 0 end), ' pound') as 'route 2', " +
"CONCAT(" +
"sum(case when route = 3 then box else 0 end), ' box, ', " +
"sum(case when route = 3 then each else 0 end), ' each, ', " +
"sum(case when route = 3 then pound else 0 end), ' pound') as 'route 3' " +
"from dbo.cart as c where order_id in " +
"(select order_id from dbo.order_list as o inner join dbo.store as s " +
"on s.store_id = o.store_id where isMarket = '0' AND delivery_date = '" +
Convert.ToDateTime(this.DeliveryScheduleDate.Value.ToString()).ToString("yyyy-MM-dd") +
"') group by c.Product;";
adapter = new SqlDataAdapter(query, db.GetConnection());
// Create one DataTable with one column.
this.DS = new DataSet();
adapter.Fill(DS);
this.DeliveryScheduleView.DataSource = DS.Tables[0];
this.DeliveryScheduleView.AutoGenerateColumns = true;
this.DeliveryScheduleView.AutoResizeColumns();
this.DeliveryScheduleView.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
for (int i = 0; i < this.DeliveryScheduleView.ColumnCount; i++)
{
DeliveryScheduleView.Columns[i].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
DeliveryScheduleView.Columns[i].FillWeight = 20;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
db.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 delieveryReport_Load(object sender, EventArgs e)
{
db = new DbConnectorClass();
String query = "select product, " +
"CONCAT(" +
"sum(case when route = 1 then box else 0 end), ' box, ', " +
"sum(case when route = 1 then each else 0 end), ' each, ', " +
"sum(case when route = 1 then pound else 0 end), ' pound') as 'route1', " +
"CONCAT(" +
"sum(case when route = 2 then box else 0 end), ' box, ', " +
"sum(case when route = 2 then each else 0 end), ' each, ', " +
"sum(case when route = 2 then pound else 0 end), ' pound') as 'route2', " +
"CONCAT(" +
"sum(case when route = 3 then box else 0 end), ' box, ', " +
"sum(case when route = 3 then each else 0 end), ' each, ', " +
"sum(case when route = 3 then pound else 0 end), ' pound') as 'route3' " +
"from dbo.cart as c where order_id in " +
"(select order_id from dbo.order_list as o inner join dbo.store as s " +
"on s.store_id = o.store_id where isMarket = '0' AND delivery_date = '" +
delieveryDate.ToString("yyyy-MM-dd") +
"') group by c.Product;";
adapter = new SqlDataAdapter(query, db.GetConnection());
// Create one DataTable with one column.
this.DS = new DataSet();
adapter.Fill(DS);
ReportDataSource rds = new ReportDataSource("delieverySchedule", DS.Tables[0]);
this.reportViewer2.LocalReport.DataSources.Clear();
this.reportViewer2.LocalReport.DataSources.Add(rds);
// TODO: This line of code loads data into the 'DataSet1.weeklyExpenseDataTable' table. You can move, or remove it, as needed.
Microsoft.Reporting.WinForms.ReportParameter[] param = new Microsoft.Reporting.WinForms.ReportParameter[]
{
new Microsoft.Reporting.WinForms.ReportParameter("delieveryDate", delieveryDate.ToString("MM-dd-yyyy")),
};
this.reportViewer2.LocalReport.SetParameters(param);
this.reportViewer2.LocalReport.Refresh();
this.reportViewer2.RefreshReport();
}
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);
}
}
public void GetProductListOriginal()
{
try
{
String whereStr = "";
if (this.CatagoryBox.SelectedItem != null && !this.CatagoryBox.SelectedItem.Equals(""))
{
whereStr = "where Catagory = '" + this.CatagoryBox.SelectedItem + "'";
}
db = new DbConnectorClass();
adapter = new SqlDataAdapter("select product_id as No, Product, Price, Quantity, Catagory, SubCatagory, Note from dbo.product " + whereStr + " 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, SubCatagory", db.GetConnection());
// Create one DataTable with one column.
DataSet DS = new DataSet();
adapter.Fill(DS);
for (int i = 0; i < DS.Tables[0].Rows.Count; i++)
{
DataRow myRow = DS.Tables[0].Rows[i];
DataGridViewRow row = (DataGridViewRow)ProductDataView.Rows[0].Clone();
row.Cells[0].Value = myRow[0].ToString();
row.Cells[1].Value = myRow[1].ToString();
row.Cells[2].Value = myRow[2].ToString();
row.Cells[3].Value = myRow[3].ToString();
row.Cells[4].Value = myRow[4].ToString().Trim();
SelectedCatagory(row, myRow[4].ToString().Trim());
row.Cells[5].Value = myRow[5].ToString().Trim();
row.Cells[6].Value = myRow[6].ToString();
this.ProductDataView.Rows.Add(row);
}
// this.ProductDataView.DataSource = DS.Tables[0];
// this.ProductDataView.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
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);
}
}
private void LoadWeeklySale()
{
try
{
DateTime Mondate = GetMonday();
DateTime Tuesdate = GetMonday().AddDays(1);
DateTime Wednesdate = GetMonday().AddDays(2);
DateTime Thursdate = GetMonday().AddDays(3);
DateTime Fridate = GetMonday().AddDays(4);
decimal MonTotal = 0;
decimal TuesTotal = 0;
decimal WedTotal = 0;
decimal ThurTotal = 0;
decimal FriTotal = 0;
decimal WeeklyTotal = 0;
MonStr = "Mon (" + Mondate.ToString("MM-dd") + ")";
TuesStr = "Tues (" + Tuesdate.ToString("MM-dd") + ")";
WedStr = "Wednes (" + Wednesdate.ToString("MM-dd") + ")";
ThurStr = "Thurs (" + Thursdate.ToString("MM-dd") + ")";
FriStr = "Fri (" + Fridate.ToString("MM-dd") + ")";
db = new DbConnectorClass();
String query = "select s.Store_id as No, Store_name as Store, " +
"sum(case when delivery_date = '" + Mondate.ToString("yyyy-MM-dd")
+ "' then total else 0 end) as '" + MonStr + "', " +
"sum(case when delivery_date = '" + Tuesdate.ToString("yyyy-MM-dd")
+ "' then total else 0 end) as '" + TuesStr + "', " +
"sum(case when delivery_date = '" + Wednesdate.ToString("yyyy-MM-dd")
+ "' then total else 0 end) as '" + WedStr + "', " +
"sum(case when delivery_date = '" + Thursdate.ToString("yyyy-MM-dd")
+ "' then total else 0 end) as '" + ThurStr + "', " +
"sum(case when delivery_date = '" + Fridate.ToString("yyyy-MM-dd")
+ "' then total else 0 end) as '" + FriStr + "', " +
"sum(case when delivery_date in ('" + Mondate.ToString("yyyy-MM-dd") + "','" +
Tuesdate.ToString("yyyy-MM-dd") + "','" + Wednesdate.ToString("yyyy-MM-dd") + "','" +
Thursdate.ToString("yyyy-MM-dd") + "','" + Fridate.ToString("yyyy-MM-dd") +
"') then total else 0 end) as Total " +
"from dbo.store as s " +
"left outer join dbo.order_list as o on o.store_id = s.store_id where isMarket = 1 " +
"group by s.Store_id, s.store_name;";
adapter = new SqlDataAdapter(query, db.GetConnection());
// Create one DataTable with one column.
this.DS = new DataSet();
adapter.Fill(DS);
for (int i = 0; i < DS.Tables[0].Rows.Count; i++)
{
DataRow dataRow = DS.Tables[0].Rows[i];
MonTotal += Decimal.Parse(dataRow[MONDAY].ToString());
TuesTotal += Decimal.Parse(dataRow[TUESDAY].ToString());
WedTotal += Decimal.Parse(dataRow[WEDNESDAY].ToString());
ThurTotal += Decimal.Parse(dataRow[THURSDAY].ToString());
FriTotal += Decimal.Parse(dataRow[FRIDAY].ToString());
WeeklyTotal += Decimal.Parse(dataRow[WEEKLYTOTAL].ToString());
}
DataRow newRow = DS.Tables[0].NewRow();
newRow["No"] = 999;
newRow["Store"] = "Total";
newRow[MonStr] = MonTotal;
newRow[TuesStr] = TuesTotal;
newRow[WedStr] = WedTotal;
newRow[ThurStr] = ThurTotal;
newRow[FriStr] = FriTotal;
newRow["Total"] = WeeklyTotal;
DS.Tables[0].Rows.Add(newRow);
this.WeeklyExpenseDataView.DataSource = DS.Tables[0];
this.WeeklyExpenseDataView.AutoGenerateColumns = true;
this.WeeklyExpenseDataView.AutoResizeColumns();
this.WeeklyExpenseDataView.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void CreateStore_Load(object sender, EventArgs e)
{
db = new DbConnectorClass();
}
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();
}
private void printWeeklyReport_Load(object sender, EventArgs e)
{
// TODO: This line of code loads data into the 'DataSet1.weeklyExpenseDataTable' table. You can move, or remove it, as needed.
this.weeklyExpenseDataTableTableAdapter.Fill(this.DataSet1.weeklyExpenseDataTable, this.deliveryDate.ToString("yyyy-MM-dd"), this.deliveryDate.AddDays(1).ToString("yyyy-MM-dd"),
this.deliveryDate.AddDays(2).ToString("yyyy-MM-dd"), this.deliveryDate.AddDays(3).ToString("yyyy-MM-dd"), this.deliveryDate.AddDays(4).ToString("yyyy-MM-dd"));
this.weeklyReportViewer.RefreshReport();
try
{
DateTime Mondate = this.deliveryDate;
DateTime Tuesdate = this.deliveryDate.AddDays(1);
DateTime Wednesdate = this.deliveryDate.AddDays(2);
DateTime Thursdate = this.deliveryDate.AddDays(3);
DateTime Fridate = this.deliveryDate.AddDays(4);
//expenseQuery
db = new DbConnectorClass();
String expenseQuery = "select s.Store_id as No, Store_name as Store, " +
"sum(case when delivery_date = '" + Mondate.ToString("yyyy-MM-dd")
+ "' then total else 0 end) as 'Monday', " +
"sum(case when delivery_date = '" + Tuesdate.ToString("yyyy-MM-dd")
+ "' then total else 0 end) as 'Tuesday', " +
"sum(case when delivery_date = '" + Wednesdate.ToString("yyyy-MM-dd")
+ "' then total else 0 end) as 'Wednesday', " +
"sum(case when delivery_date = '" + Thursdate.ToString("yyyy-MM-dd")
+ "' then total else 0 end) as 'Thursday', " +
"sum(case when delivery_date = '" + Fridate.ToString("yyyy-MM-dd")
+ "' then total else 0 end) as 'Friday', " +
"sum(case when delivery_date in ('" + Mondate.ToString("yyyy-MM-dd") + "','" +
Tuesdate.ToString("yyyy-MM-dd") + "','" + Wednesdate.ToString("yyyy-MM-dd") + "','" +
Thursdate.ToString("yyyy-MM-dd") + "','" + Fridate.ToString("yyyy-MM-dd") +
"') then total else 0 end) as Total " +
"from dbo.store as s " +
"left outer join dbo.order_list as o on o.store_id = s.store_id where isMarket = 1 " +
"group by s.Store_id, s.store_name;";
adapter = new SqlDataAdapter(expenseQuery, db.GetConnection());
// Create one DataTable with one column.
DataSet expenseSet = new DataSet();
adapter.Fill(expenseSet);
//saleQuery
db = new DbConnectorClass();
String saleQuery = "select s.Store_id as No, Store_name as Store, " +
"sum(case when delivery_date = '" + Mondate.ToString("yyyy-MM-dd")
+ "' then total else 0 end) as 'Monday', " +
"sum(case when delivery_date = '" + Tuesdate.ToString("yyyy-MM-dd")
+ "' then total else 0 end) as 'Tuesday', " +
"sum(case when delivery_date = '" + Wednesdate.ToString("yyyy-MM-dd")
+ "' then total else 0 end) as 'Wednesday', " +
"sum(case when delivery_date = '" + Thursdate.ToString("yyyy-MM-dd")
+ "' then total else 0 end) as 'Thursday', " +
"sum(case when delivery_date = '" + Fridate.ToString("yyyy-MM-dd")
+ "' then total else 0 end) as 'Friday', " +
"sum(case when delivery_date in ('" + Mondate.ToString("yyyy-MM-dd") + "','" +
Tuesdate.ToString("yyyy-MM-dd") + "','" + Wednesdate.ToString("yyyy-MM-dd") + "','" +
Thursdate.ToString("yyyy-MM-dd") + "','" + Fridate.ToString("yyyy-MM-dd") +
"') then total else 0 end) as Total " +
"from dbo.store as s " +
"left outer join dbo.order_list as o on o.store_id = s.store_id where isMarket = 0 " +
"group by s.Store_id, s.store_name;";
adapter = new SqlDataAdapter(saleQuery, db.GetConnection());
// Create one DataTable with one column.
DataSet saleSet = new DataSet();
adapter.Fill(saleSet);
Microsoft.Reporting.WinForms.ReportParameter[] param = new Microsoft.Reporting.WinForms.ReportParameter[]
{
new Microsoft.Reporting.WinForms.ReportParameter("delieveryDate", Mondate.ToString("(yyyy/MM/dd)") + " - " + Fridate.ToString("(yyyy/MM/dd)"))
};
ReportDataSource rds1 = new ReportDataSource("weeklyExpenseDataTable", expenseSet.Tables[0]);
ReportDataSource rds2 = new ReportDataSource("weeklySaleDataTable", saleSet.Tables[0]);
this.weeklyReportViewer.LocalReport.DataSources.Add(rds1);
this.weeklyReportViewer.LocalReport.DataSources.Add(rds2);
this.weeklyReportViewer.LocalReport.SetParameters(param);
this.weeklyReportViewer.LocalReport.Refresh();
this.weeklyReportViewer.RefreshReport();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
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();
}