//
//Get list
public List <Products> getList()
{
List <Products> mlist = new List <Products>();
SqlDataReader reader = MyConnect.getReader("select * from get_products");
while (reader.Read())
{
Products p = new Products();
p.productID = reader.GetString(0);
p.productName = reader.GetString(1);
Types t = new Types();
t.typeName = reader.GetString(2);
p.Type = t;
Supplier s = new Supplier();
s.companyName = reader.GetString(3);
p.supplier = s;
p.quantily = Convert.ToInt16(reader[4]);
p.purchasePrice = long.Parse(reader[5].ToString());
p.productPrice = long.Parse(reader[6].ToString());
p.warranty = Convert.ToInt16(reader[7]);
mlist.Add(p);
}
reader.Close();
MyConnect.Disconncet();
return(mlist);
}
//Create new account
private void btSignIn_Click(object sender, EventArgs e)
{
if (!String.IsNullOrEmpty(MyConnect.getString("select Email from LogIn where Email=N'" + txNewEmail.Text + "'")))
{
MyChecker.setErr(txNewEmail, errorEmail, "Email Invailable!", Properties.Resources.ERROR); email = false; txNewEmail.Focus(); return;
}
if (!username)
{
txNewUserName.Focus(); return;
}
else if (!email)
{
txNewEmail.Focus(); return;
}
else if (!password)
{
txNewPassword.Focus(); return;
}
else if (!confirompw)
{
txConfiromPw.Focus(); return;
}
else
{
if (ac.AddObject(new Account(txNewUserName.Text, StringPassword.EncrytString(txNewPassword.Text, Properties.Resources.KeyCrypt), cbManager.Checked, txNewEmail.Text)))
{
MessageBox.Show("Create new account successfully!");
}
else
{
MessageBox.Show("Can't create account!", "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
setAutoCompleteCollection();
}
}
//
//Get a product
public Products getObject(string id)
{
Products p = new Products();
SqlDataReader reader = MyConnect.getReader("select * from get_products where ID_Product = N'" + id + "'");
while (reader.Read())
{
p.productID = reader.GetString(0);
p.productName = reader.GetString(1);
Types t = new Types();
t.typeName = reader.GetString(2);
p.Type = t;
Supplier s = new Supplier();
s.companyName = reader.GetString(3);
p.supplier = s;
p.quantily = Convert.ToInt16(reader[4]);
p.purchasePrice = long.Parse(reader[5].ToString());
p.productPrice = long.Parse(reader[6].ToString());
p.warranty = Convert.ToInt16(reader[7]);
}
reader.Close();
MyConnect.Disconncet();
return(p);
}
//
// Monthly report
private void ItemIncome_LinkClicked(object sender, DevExpress.XtraNavBar.NavBarLinkEventArgs e)
{
frmPickDateTime frm = new frmPickDateTime();
frm.myState = EnumPickDateTime.PickYear;
if (frm.ShowDialog() == DialogResult.OK)
{
waitForm.ShowWaitForm();
clearText();
ChartTitle title = new ChartTitle();
title.Text = "Monthly Report";
chartControl.Titles.Add(title);
System.Threading.Thread.Sleep(1000);
table = MyConnect.getTable("execute ShowMothlyReports @y = " + frm.year);
chartControl.DataSource = table;
Series s2 = new Series("Purchase", ViewType.Bar);
s2.View.Color = Color.Blue;
chartControl.Series.Add(s2);
s2.ArgumentDataMember = "Month";
s2.ValueDataMembers.AddRange(new string[] { "TotalPurchase" });
Series s1 = new Series("Sale", ViewType.Bar);
s1.View.Color = Color.PaleVioletRed;
chartControl.Series.Add(s1);
s1.ArgumentDataMember = "Month";
s1.ValueDataMembers.AddRange(new string[] { "TotalSale" });
waitForm.CloseWaitForm();
this.State = EnumReportState.RevenueOfMonth;
}
}
bool checkPrimaryKey(string sql)
{
if (String.IsNullOrEmpty(MyConnect.getString(sql)))
{
return(true);
}
return(false);
}
private void Form1_Load(object sender, EventArgs e)
{
// TODO: This line of code loads data into the 'showEmloyees.show_Employee' table. You can move, or remove it, as needed.
this.show_EmployeeTableAdapter.Fill(this.showEmloyees.show_Employee);
comboBox1.DataSource = MyConnect.getTable("select * from Employee");
comboBox1.DisplayMember = "Name_Emp";
comboBox1.ValueMember = "ID_Emp";
}
public Payment getObject(string id)
{
Payment p = new Payment();
DataTable tabe = MyConnect.getTable("select * from Payment where PaymentType =" + id);
p.id = Convert.ToInt16(tabe.Rows[0][0]);
p.name = tabe.Rows[0][1].ToString();
return(p);
}
public DataTable getTable(dynamic state)
{
if (state == EnumShowProduct.ShowWithName)
{
return(MyConnect.getTable("select ID_Product,Name_Pro from get_products"));
}
if (state == EnumShowProduct.ShowReportQuantily)
{
return(MyConnect.getTable("select * from rptProductQuantily"));
}
return(MyConnect.getTable("select * from get_products"));
}
public OrderBillDetail getObject(string orderID, string productID)
{
System.Data.SqlClient.SqlDataReader reader = MyConnect.getReader("select * from show_orderDetail where ID_Order='" + orderID + "' and ID_Product='" + productID + "'");
OrderBillDetail detail = new OrderBillDetail();
detail.orderBill = new OrderBillDAO().getObject(reader.GetString(0));
detail.product = new ProductDAO().getObject(reader.GetString(1));
detail.quantily = Convert.ToInt16(reader[3].ToString());
detail.discount = Convert.ToInt16(reader[4].ToString());
detail.price = long.Parse(reader[5].ToString());
reader.Close();
MyConnect.Disconncet();
return(detail);
}
public PurchaseBill getObject(string id)
{
PurchaseBill bill = new PurchaseBill();
SqlDataReader reader = MyConnect.getReader("select * from Purchase where ID_Purchase='" + id + "'");
while (reader.Read())
{
bill.purchaseID = reader.GetString(0);
bill.employee = new EmployeeDAO().getObject(reader.GetString(1));
bill.purchaseDate = reader.GetDateTime(2);
bill.totalPrice = long.Parse(reader[3].ToString());
}
return(bill);
}
//Set Auto Complete Collection
void setAutoCompleteCollection()
{
AutoCompleteStringCollection col = new AutoCompleteStringCollection();
SqlDataReader reader = MyConnect.getReader("select * from get_all_user");
while (reader.Read())
{
col.Add(reader.GetString(0));
}
txUserName.AutoCompleteCustomSource = col;
if (!reader.IsClosed)
{
reader.Close();
}
}
public Account getObject(string id)
{
Account account = new Account();
SqlDataReader reader = MyConnect.getReader("select * from LogIn where UserName = N'" + id + "'");
while (reader.Read())
{
account.username = reader.GetString(0);
account.password = reader.GetString(1);
account.service = reader.GetBoolean(2);
account.email = reader.GetString(3);
}
reader.Close();
MyConnect.Disconncet();
return(account);
}
public List <Category> getList()
{
List <Category> mlist = new List <Category>();
SqlDataReader reader = MyConnect.getReader("select * from Categorys");
while (reader.Read())
{
Category c = new Category();
c.categoryID = Convert.ToInt16(reader[0]);
c.categoryName = reader.GetString(1);
mlist.Add(c);
}
reader.Close();
MyConnect.Disconncet();
return(mlist);
}
public List <PurchaseBill> getList()
{
List <PurchaseBill> mlist = new List <PurchaseBill>();
SqlDataReader reader = MyConnect.getReader("select * from get_PurchaseBills");
while (reader.Read())
{
PurchaseBill bill = new PurchaseBill();
bill.purchaseID = reader.GetString(0);
bill.employee = new EmployeeDAO().getObject(reader.GetString(1));
bill.purchaseDate = reader.GetDateTime(2);
bill.totalPrice = long.Parse(reader[3].ToString());
mlist.Add(bill);
}
return(mlist);
}
public PurchaseBillDetail getObject(string purchase, string product)
{
PurchaseBillDetail pd = new PurchaseBillDetail();
SqlDataReader reader = MyConnect.getReader("select * from PurchaseDetail where ID_Purchase ='" + purchase + "' and ID_Product='" + product + "'");
while (reader.Read())
{
pd.bill = new PurchaseBillDAO().getObject(reader.GetString(0));
pd.product = new ProductDAO().getObject(reader.GetString(1));
pd.quantily = reader.GetInt16(2);
pd.price = long.Parse(reader[3].ToString());
}
reader.Close();
MyConnect.Disconncet();
return(pd);
}
public PurchaseBillDetail getObject(string id)
{
PurchaseBillDetail pd = new PurchaseBillDetail();
SqlDataReader reader = MyConnect.getReader("");
while (reader.Read())
{
pd.bill = new PurchaseBillDAO().getObject(reader.GetString(0));
pd.product = new ProductDAO().getObject(reader.GetString(1));
pd.quantily = reader.GetInt16(2);
pd.price = long.Parse(reader[3].ToString());
}
reader.Close();
MyConnect.Disconncet();
return(pd);
}
public Supplier getObject(string id)
{
Supplier s = new Supplier();
SqlDataReader reader = MyConnect.getReader("select * from Suppliers where ID_Supplier='" + id + "'");
while (reader.Read())
{
s.supplierID = reader.GetString(0);
s.companyName = reader.GetString(1);
s.Description = reader.GetString(2);
s.Headquarters = reader.GetString(3);
}
reader.Close();
MyConnect.Disconncet();
return(s);
}
//
// Days Report
private void navBarItem1_LinkClicked_1(object sender, NavBarLinkEventArgs e)
{
frmPickDateTime frm = new frmPickDateTime();
frm.myState = EnumPickDateTime.PickDays;
if (frm.ShowDialog() == DialogResult.OK)
{
waitForm.ShowWaitForm();
clearText();
ChartTitle title = new ChartTitle();
title.Text = "Days Report From : " + frm.startDate.ToShortDateString() + " To " + frm.endDate.ToShortDateString();
chartControl.Titles.Add(title);
System.Threading.Thread.Sleep(1000);
table = MyConnect.getTable("execute DaysReport @start ='" + frm.startDate.ToString("yyyy-MM-dd") + "',@end = '" + frm.endDate.ToString("yyyy-MM-dd") + "'");
Series s1 = new Series("Total", ViewType.Line);
s1.View.Color = Color.Orange;
chartControl.Series.Add(s1);
int i = 0, j = 0;
foreach (DateTime date in MyAction.GetAllDates(frm.startDate, frm.endDate))
{
if (j < table.Rows.Count)
{
DateTime temp = DateTime.Parse(table.Rows[j][0].ToString());
if (temp.Equals(date))
{
s1.Points.Add(new SeriesPoint(temp.ToString("dd-MM-yyyy"), long.Parse(table.Rows[j][1].ToString())));
j++;
}
else
{
s1.Points.Add(new SeriesPoint(date.ToString("dd-MM-yyyy"), 0));
}
}
else
{
s1.Points.Add(new SeriesPoint(date.ToString("dd-MM-yyyy"), 0));
}
i++;
}
chartControl.Series.Add(s1);
//MessageBox.Show(table.Rows[0][1].ToString());
waitForm.CloseWaitForm();
this.State = EnumReportState.RevenueOfDays;
}
}
public Employee getObject(string id)
{
Employee e = new Employee();
SqlDataReader reader = MyConnect.getReader("select * from Employee where ID_Emp=N'" + id + "'");
while (reader.Read())
{
Image image = null;
if (!reader.IsDBNull(6))
{
image = ImageSQL.ByteArrayToImage((byte[])reader[6]);
}
e = new Employee(reader.GetString(0), reader.GetString(1), reader.GetDateTime(2), reader.GetBoolean(3), reader.GetString(4), long.Parse(reader.GetInt64(5).ToString()), image);
}
reader.Close();
return(e);
}
public Customer getObject(string id)
{
Customer c = new Customer();
System.Data.SqlClient.SqlDataReader reader = MyConnect.getReader("select * from Customers where ID_Customer = '" + id + "'");
while (reader.Read())
{
c.ID = reader.GetString(0);
c.Name = reader.GetString(1);
c.phoneNumber = reader[2].ToString();
c.Address = reader.GetString(3);
c.email = reader.GetString(4);
}
reader.Close();
MyConnect.Disconncet();
return(c);
}
public List <PurchaseBillDetail> getList(string id)
{
List <PurchaseBillDetail> mlist = new List <PurchaseBillDetail>();
SqlDataReader reader = MyConnect.getReader("select * from show_purshaseDetial where ID_Purchase='" + id + "'");
while (reader.Read())
{
PurchaseBillDetail pd = new PurchaseBillDetail();
pd.bill = new PurchaseBillDAO().getObject(reader.GetString(0));
pd.product = new ProductDAO().getObject(reader.GetString(1));
pd.quantily = Convert.ToInt16(reader[3]);
pd.price = long.Parse(reader[4].ToString());
}
reader.Close();
MyConnect.Disconncet();
return(mlist);
}
public List <Supplier> getList()
{
List <Supplier> mlist = new List <Supplier>();
SqlDataReader reader = MyConnect.getReader("select * from Suppliers");
while (reader.Read())
{
Supplier s = new Supplier();
s.supplierID = reader.GetString(0);
s.companyName = reader.GetString(1);
s.Description = reader.GetString(2);
s.Headquarters = reader.GetString(3);
mlist.Add(s);
}
reader.Close();
MyConnect.Disconncet();
return(mlist);
}
public Types getObject(string id)
{
Types temp = new Types();
SqlDataReader reader = MyConnect.getReader("select * from ProductTypes where ID_Type = '" + id + "'");
while (reader.Read())
{
temp.typeID = reader.GetString(0);
temp.typeName = reader.GetString(1);
temp.Description = reader.GetString(2);
Category c = new Category();
c.categoryID = Convert.ToInt16(reader[3]);
}
reader.Close();
MyConnect.Disconncet();
return(temp);
}
public List <Customer> getList()
{
List <Customer> mlist = new List <Customer>();
System.Data.SqlClient.SqlDataReader reader = MyConnect.getReader("select * from Customers");
while (reader.Read())
{
Customer c = new Customer();
c.ID = reader.GetString(0);
c.Name = reader.GetString(1);
c.phoneNumber = reader[2].ToString();
c.Address = reader.GetString(3);
c.email = reader.GetString(4);
mlist.Add(c);
}
reader.Close();
MyConnect.Disconncet();
return(mlist);
}
public List <OrderBillDetail> getList(string id)
{
List <OrderBillDetail> mlist = new List <OrderBillDetail>();
DataTable table = MyConnect.getTable("select * from show_orderDetail where ID_Order='" + id + "'");
for (int i = 0; i < table.Rows.Count; i++)
{
DataRow row = table.Rows[i];
OrderBillDetail detail = new OrderBillDetail();
detail.orderBill = new OrderBillDAO().getObject(row[1].ToString());
detail.product = pd.getObject(row[0].ToString());
detail.quantily = Convert.ToInt16(row[3].ToString());
detail.discount = Convert.ToInt16(row[4].ToString());
detail.price = long.Parse(row[5].ToString());
mlist.Add(detail);
}
MyConnect.Disconncet();
return(mlist);
}
public List <Types> getList()
{
List <Types> mlist = new List <Types>();
SqlDataReader reader = MyConnect.getReader("select * from ProductTypes");
while (reader.Read())
{
Types temp = new Types();
temp.typeID = reader.GetString(0);
temp.typeName = reader.GetString(1);
temp.Description = reader.GetString(2);
Category c = new Category();
c.categoryID = Convert.ToInt16(reader[3]);
mlist.Add(temp);
}
reader.Close();
MyConnect.Disconncet();
return(mlist);
}
public List <OrderBIll> getList(DateTime date)
{
List <OrderBIll> mlist = new List <OrderBIll>();
System.Data.SqlClient.SqlDataReader reader = MyConnect.getReader("select * from Orders where OrderDate='" + date.ToString("yyyy-MM-dd") + "'");
while (reader.Read())
{
OrderBIll bill = new OrderBIll();
bill.orderID = reader.GetString(0);
bill.customer = cd.getObject(reader.GetString(1));
bill.employee = ed.getObject(reader.GetString(2));
bill.orderDate = reader.GetDateTime(3);
bill.payType = Convert.ToInt16(reader[4]);
bill.total = long.Parse(reader[5].ToString());
mlist.Add(bill);
}
reader.Close();
MyConnect.Disconncet();
return(mlist);
}
public List <Types> getList(int id)
{
List <Types> mlist = new List <Types>();
SqlDataReader reader = MyConnect.getReader("execute get_typesOnCategory @categoryID = " + id + "");
while (reader.Read())
{
Types temp = new Types();
temp.typeID = reader.GetString(0);
temp.typeName = reader.GetString(1);
temp.Description = reader.GetString(2);
Category c = new Category();
c.categoryID = Convert.ToInt16(reader[3]);
temp.category = c;
mlist.Add(temp);
}
reader.Close();
MyConnect.Disconncet();
return(mlist);
}
//
// Report Product Typer Sold
private void ItemType_LinkClicked(object sender, DevExpress.XtraNavBar.NavBarLinkEventArgs e)
{
waitForm.ShowWaitForm();
clearText();
ChartTitle title = new ChartTitle();
title.Text = "Product Types Report";
chartControl.Titles.Add(title);
chartControl.DataSource = null;
System.Threading.Thread.Sleep(1000);
table = MyConnect.getTable("select * from CountTypePorduct");
Series s1 = new Series("Type", ViewType.Pie);
for (int i = 0; i < table.Rows.Count; i++)
{
s1.Points.Add(new SeriesPoint(table.Rows[i][0].ToString(), Convert.ToInt16(table.Rows[i][1].ToString())));
}
s1.Label.TextPattern = "{A}: {VP:p0}";
chartControl.Series.Add(s1);
waitForm.CloseWaitForm();
this.State = EnumReportState.ProductTypes;
}
//Get list emp
public List <Employee> getList()
{
List <Employee> mList = new List <Employee>();
try
{
SqlDataReader reader = MyConnect.getReader("select * from Employee");
while (reader.Read())
{
Image image = null;
if (!reader.IsDBNull(6))
{
image = ImageSQL.ByteArrayToImage((byte[])reader[6]);
}
Employee e = new Employee(reader.GetString(0), reader.GetString(1), reader.GetDateTime(2), reader.GetBoolean(3), reader.GetString(4), long.Parse(reader.GetInt64(5).ToString()), image);
mList.Add(e);
}
MyConnect.Disconncet();
reader.Close();
}
catch { return(null); }
return(mList);
}
