public IEnumerable<Order> GetAllOrders()
{
string Query = "sp_customerorder_select";
DataTable dt = new ConnectionManager().ExecuteGetDataTable(Query);
List<Order> lst = new List<Order>();
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow r in dt.Rows)
{
Order order = new Order();
order.ID = Convert.ToInt32(r["User_id"]);
order.OrderNo = Convert.ToInt32(r["order_no"]);
order.Date = Convert.ToDateTime(r["date"]);
order.TOW = Convert.ToString(r["type_of_work"]);
order.TypeOfWork = (TypeOfWorks)Enum.Parse(typeof(TypeOfWorks), r["type_of_work"].ToString());
order.NOC = Convert.ToInt32(r["number_of_copies"]);
order.Rate = Convert.ToSingle(r["rate"]);
order.Total = Convert.ToSingle(r["total"]);
order.CustomerId = Convert.ToInt32(r["Customer_Id"]);
lst.Add(order);
}
}
return lst;
}
public int InsertOrder(Order order)
{
string Query = "sp_customerorder_insert";
//The Stored_Procedure is used to...
string queselect = "sp_odermanager_select";
int result = new ConnectionManager().ExecuteNonQuery(Query, Createparameters(order));
object id = new ConnectionManager().ExecuteScalar(queselect);
if (id != null && id != DBNull.Value)
{
order.ID = (int)id;
}
else
{
throw new Exception("Could not be saved!");
}
return result;
}
private IEnumerable<Client> GetClients(string Query)
{
DataTable dt = new ConnectionManager().ExecuteGetDataTable(Query);
List<Client> lstClient = new List<Client>();
if (dt != null && dt.Rows.Count > 0)
{
foreach(DataRow r in dt.Rows )
{
Client cl = new Client();
cl.ID = Convert.ToInt32(r["client_id"]);
cl.Name = r["user_name"].ToString();
cl.Pass = r["password"].ToString();
cl.Active = Convert.ToBoolean(r["active"]);
lstClient.Add(cl);
}
}
return lstClient;
}
private IEnumerable<Customer> GetCustomers(string Query)
{
DataTable dt = new ConnectionManager().ExecuteGetDataTable(Query);
List<Customer> lst = new List<Customer>();
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow r in dt.Rows)
{
Customer cs = new Customer();
cs.Id = Convert.ToInt32(r["customer_id"]);
cs.Name = r["first_name"].ToString();
cs.Surname = r["last_name"].ToString();
cs.Add = r["address"].ToString();
cs.NameOfCom = r["name_of_com"].ToString();
cs.Contact = r["contact_no"].ToString();
cs.Mobile = r["mobile"].ToString();
cs.Email= r["email_id"].ToString();
lst.Add(cs);
}
}
return lst;
}
internal Customer GetAllCustData(string Query)
{
DataTable dt = new ConnectionManager().ExecuteGetDataTable(Query);
List<Customer> lst = new List<Customer>();
if (dt != null && dt.Rows.Count > 0)
{
Customer cs = new Customer();
cs.Id = Convert.ToInt32(dt.Rows[0]["customer_id"]);
cs.Name = dt.Rows[0]["first_name"].ToString();
cs.Surname = dt.Rows[0]["last_name"].ToString();
cs.Add = dt.Rows[0]["address"].ToString();
cs.NameOfCom = dt.Rows[0]["name_of_com"].ToString();
cs.Contact = dt.Rows[0]["contact_no"].ToString();
cs.Mobile = dt.Rows[0]["mobile"].ToString();
cs.Email = dt.Rows[0]["email_id"].ToString();
return cs;
}
else
{
return null;
}
}
private void txt_searchfield_TextChanged(object sender, EventArgs e)
{
if (txt_searchfield.TextLength > 0)
{
//AutoCompleteStringCollection namecoll = new AutoCompleteStringCollection();
//string query = "SELECT distinct(first_name+''+last_name)AS name from Customer_Details where first_name LIKE @name OR last_name LIKE @name";
//Customer cs = new CustomerManager().GetSpecificData(query);
txt_searchfield.AutoCompleteSource = AutoCompleteSource.None;
txt_searchfield.AutoCompleteMode = AutoCompleteMode.None;
listBox1.Visible = true;
DataTable dt = new DataTable();
if (cb_search.SelectedItem.ToString() == "First_Name" || cb_search.SelectedItem.ToString() == "Last_Name")
{
string query = string.Format("SELECT distinct(first_name+' '+last_name)AS name, customer_id from Customer_Details where first_name LIKE '{0}%' OR last_name LIKE '{0}%'", txt_searchfield.Text);
dt = new ConnectionManager().ExecuteGetDataTable(query);
listBox1.DisplayMember = dt.Columns[0].ColumnName;
}
//AutoCompleteStringCollection namecoll = new AutoCompleteStringCollection();
//List<string> lst = new List<string>();
//for (int i = 0; i < dt.Rows.Count; i++)
// lst.Add(dt.Rows[i][0].ToString());
//namecoll.AddRange(lst.ToArray());
//txt_searchfield.AutoCompleteCustomSource = namecoll
if (cb_search.SelectedItem.ToString() == "Mobile Number")
{
string query = string.Format("SELECT distinct mobile, customer_id from Customer_Details WHERE mobile LIKE '{0}%'", txt_searchfield.Text);
dt = new ConnectionManager().ExecuteGetDataTable(query);
listBox1.DisplayMember = dt.Columns[0].ColumnName;
}
if (cb_search.SelectedItem.ToString() == "Company Name")
{
string query = string.Format("SELECT distinct name_of_com, customer_id from Customer_Details where name_of_com LIKE '{0}%'", txt_searchfield.Text);
dt = new ConnectionManager().ExecuteGetDataTable(query);
listBox1.DisplayMember = dt.Columns[0].ColumnName;
}
listBox1.ValueMember = dt.Columns[1].ColumnName;
listBox1.DataSource = dt.DefaultView;
}
else
{
listBox1.Visible = false;
}
}
internal int GetCustId(string combodata, string txtvalue)
{
object custid;
string parameter = "", paramvalue = "";
string[] value = txtvalue.Split(' ');
//string Query = "SELECT customer_id FROM Customer_Details WHERE {0}{1}", combodata, txtvalue);
//Code is used for separating first_name and last_name if passed as whole as parameter...
if (combodata == "First_Name")
{
parameter = "first_name";
paramvalue = value[0];
custid = new ConnectionManager().ExecuteScalar(string.Format("SELECT customer_id FROM Customer_Details WHERE {0} like '%{1}%'", parameter, paramvalue), CommandType.Text);
}
else if (combodata == "Last_Name")
{
parameter = "last_name";
paramvalue = value[1];
custid = new ConnectionManager().ExecuteScalar(string.Format("SELECT customer_id FROM Customer_Details WHERE {0} like '%{1}%'", parameter, paramvalue), CommandType.Text);
}
else if (combodata == "Mobile Number")
{
parameter = "contact_no";
paramvalue = txtvalue;
custid = new ConnectionManager().ExecuteScalar(string.Format("SELECT customer_id FROM Customer_Details WHERE {0} = {1}", parameter, paramvalue), CommandType.Text);
}
else
{
parameter = "name_of_com";
paramvalue = txtvalue;
custid = new ConnectionManager().ExecuteScalar(string.Format("SELECT customer_id FROM Customer_Details WHERE {0} = {1}", parameter, paramvalue), CommandType.Text);
}
return Convert.ToInt32(custid);
//SELECT customer_id from (SELECT * FROM( SELECT (first_name + ' ' + last_name) as name,* from Customer_Details)as temp1 WHERE name like '%Pav%')as temp2
}
internal Order GetAllData(string Query)
{
// throw new NotImplementedException();
//Order ord = (Order)new ConnectionManager().ExecuteScalar(que, CommandType.Text);
//return ord;
DataTable dt = new ConnectionManager().ExecuteGetDataTable(Query);
if (dt != null && dt.Rows.Count > 0)
{
Order order = new Order();
order.ID = Convert.ToInt32(dt.Rows[0]["User_id"]);
order.OrderNo = Convert.ToInt32(dt.Rows[0]["order_no"]);
order.Date = Convert.ToDateTime(dt.Rows[0]["date"]);
order.TOW = Convert.ToString(dt.Rows[0]["type_of_work"]);
// order.TypeOfWork = (TypeOfWorks)Enum.Parse(typeof(TypeOfWorks), dt.Rows[0]["type_of_work"].ToString());
order.NOC = Convert.ToInt32(dt.Rows[0]["no_of_copies"]);
order.Rate = Convert.ToSingle(dt.Rows[0]["rate"]);
order.Total = Convert.ToSingle(dt.Rows[0]["total"]);
order.CustomerId = Convert.ToInt32(dt.Rows[0]["Customer_Id"]);
return order;
}
else
{
return null;
}
}
internal int GetCustomerId(string custid)
{
//throw new NotImplementedException();
object cid = new ConnectionManager().ExecuteScalar(custid, CommandType.Text);
return Convert.ToInt32(cid);
}
internal int GenerateOrderNo(int orderMonth, int orderYear)
{
int lastday = DateTime.DaysInMonth(orderYear, orderMonth);
DateTime endDate = new DateTime(orderYear, orderMonth, lastday);
DateTime startDate = new DateTime(orderYear, orderMonth, 1);
object order_no = new ConnectionManager().ExecuteScalar(string.Format("SELECT order_no from Customer_Order WHERE date between '{0:yyyy-MM-dd}' AND '{1:yyyy-MM-dd}'", startDate, endDate));
if (order_no == null || order_no == DBNull.Value)
{
return 1;
}
else
{
return ((int)order_no) + 1;
}
}