void AddNewCustomerWindowObject_OnAddNewcustomerData(CustomersData customerdataobject)
{
int turnover = 0;
customerdataobject.customerTurnOver = turnover;
customerdataobject.customerDue = turnover;
customerdataobject.customerId = DateTime.Now.ToOADate().ToString() + getcustomerid.ToString(); //...SAGNIK(MY THOUGHT)ihave added date string as an ex u can any other thing to make it unique......////need some helppppppppppppppppppppppppppppppppppppppppppp
//customerdataobject.serialNo = customerCount.ToString();
_customersCollection.Add(customerdataobject);
}
public EditCustomerWindow(CustomersData customerToEdit)
{
InitializeComponent();
_customerToEdit = customerToEdit;
NameDataTB.Text = _customerToEdit.customerName;
AddrDataTB.Text = _customerToEdit.customerAdress;
phDataTB.Text = _customerToEdit.phoneNumber;
vatdataTB.Text = _customerToEdit.customerVatNo;
}
private void addBtn_Click(object sender, RoutedEventArgs e)
{
if (CheckDataAvailability() == true)
{
CustomersData customerDataObject = new CustomersData();
customerDataObject.customerName = NameDataTB.Text;
customerDataObject.customerAdress = AssignSpaceIfEmptyField(AddrDataTB);
customerDataObject.customerVatNo = AssignSpaceIfEmptyField(vatdataTB);
customerDataObject.phoneNumber = AssignSpaceIfEmptyField(phDataTB);
if (OnAddNewcustomerData != null)
OnAddNewcustomerData(customerDataObject);
ConnectInsertTocustomersTable(customerDataObject);
this.Close();
}
}
public static List<CustomersData> FetchCustomersList()
{
List<CustomersData> customerCollection = new List<CustomersData>();
MySql.Data.MySqlClient.MySqlConnection msqlConnection = OpenDbConnection();
//define the command reference
MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand();
msqlCommand.Connection = msqlConnection;
if (msqlConnection.State != System.Data.ConnectionState.Open)
msqlConnection.Open();
msqlCommand.CommandText = "SELECT * FROM customers";
MySql.Data.MySqlClient.MySqlDataReader msqlReader = msqlCommand.ExecuteReader();
//_customerCollection.Clear();
while (msqlReader.Read())
{
CustomersData cusData = new CustomersData();
cusData.customerName = msqlReader.GetString("customer_name");
cusData.customerId = msqlReader.GetString("id");
cusData.customerAdress = msqlReader.GetString("address");
cusData.phoneNumber = msqlReader.GetString("ph_no");
customerCollection.Add(cusData);
}
msqlConnection.Close();
return customerCollection;
}
private void ShowCustomerName()
{
MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand();
msqlConnection = new MySql.Data.MySqlClient.MySqlConnection("server=localhost;user id=root;Password=technicise;database=sptdb;persist security info=False");
//define the connection used by the command object
msqlCommand.Connection = msqlConnection;
if (msqlConnection.State != System.Data.ConnectionState.Open)
msqlConnection.Open();
msqlCommand.CommandText = "SELECT customer_name,id FROM customers group by customer_name";
MySql.Data.MySqlClient.MySqlDataReader msqlReader = msqlCommand.ExecuteReader();
_customerCollection.Clear();
while (msqlReader.Read())
{
CustomersData cusData = new CustomersData();
cusData.customerName = msqlReader.GetString("customer_name");
cusData.customerId = msqlReader.GetString("id");
_customerCollection.Add(cusData);
}
msqlConnection.Close();
}
private void fetchecustomerData()
{
msqlConnection = new MySql.Data.MySqlClient.MySqlConnection("server=localhost;user id=root;Password=technicise;database=sptdb;persist security info=False");
try
{ //define the command reference
MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand();
msqlCommand.Connection = msqlConnection;
msqlConnection.Open();
msqlCommand.CommandText = "Select * from customers;";
MySql.Data.MySqlClient.MySqlDataReader msqlReader = msqlCommand.ExecuteReader();
_customersCollection.Clear();
while (msqlReader.Read())
{
CustomersData customersDataObject = new CustomersData();
//customersDataObject.serialNo = msqlReader.GetString("sl_no");
customersDataObject.customerAdress = msqlReader.GetString("address");
customersDataObject.phoneNumber = msqlReader.GetString("ph_no");
customersDataObject.customerName = msqlReader.GetString("customer_name");
customersDataObject.customerVatNo = msqlReader.GetString("vat_no");
customersDataObject.customerId = msqlReader.GetString("id");
customersDataObject.customerTurnOver = msqlReader.GetDouble("turn_over");
customersDataObject.customerDue = msqlReader.GetDouble("due");
_customersCollection.Add(customersDataObject);
}
}
catch (Exception er)
{
MessageBox.Show(er.Message);
}
finally
{
//always close the connection
msqlConnection.Close();
}
}
void editWindow_OnEditCustomersData(CustomersData returnEditedCustomerData)
{
//finding the element
CustomersData vData = _customersCollection.Where(item => item.customerId.Equals(returnEditedCustomerData.customerId)).First();
//finding the element position
int itemIdex = _customersCollection.IndexOf(vData);
//remove the element so that the list gets refreshed
_customersCollection.RemoveAt(itemIdex);
//insert the edited element at same position
_customersCollection.Insert(itemIdex, returnEditedCustomerData);
}
private void EditCustomer(CustomersData returnEditedCustomerData)
{
msqlConnection = new MySql.Data.MySqlClient.MySqlConnection("server=localhost;user id=root;Password=technicise;database=sptdb;persist security info=False");
try
{ //define the command reference
MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand();
msqlCommand.Connection = msqlConnection;
msqlConnection.Open();
msqlCommand.CommandText = "UPDATE customers SET customer_name='" + returnEditedCustomerData.customerName + "', address='" + returnEditedCustomerData.customerAdress + "', ph_no='" + returnEditedCustomerData.phoneNumber + "', vat_no='" + returnEditedCustomerData.customerVatNo + "' WHERE id='" + returnEditedCustomerData.customerId + "'; ";
msqlCommand.ExecuteNonQuery();
}
catch (Exception er)
{
MessageBox.Show(er.Message);
}
finally
{
//always close the connection
msqlConnection.Close();
}
}
private void ConnectInsertTocustomersTable(CustomersData customerDataObject)
{
//define the connection reference and initialize it
msqlConnection = new MySql.Data.MySqlClient.MySqlConnection("server=localhost;user id=root;Password=technicise;database=sptdb;persist security info=False");
try
{
//foreach (customersData cusomerDataObject in _customersCollection)
//{
//define the command reference
MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand();
//define the connection used by the command object
msqlCommand.Connection = msqlConnection;
//open the connection
msqlConnection.Open();
FeedcustomerData(msqlCommand, customerDataObject);
//}
}
catch (Exception er)
{
MessageBox.Show(er.Message);
}
finally
{
msqlConnection.Close();
}
}
void FeedcustomerData(MySql.Data.MySqlClient.MySqlCommand msqlCommand, CustomersData cusomerDataObject)
{
//define the command text
msqlCommand.CommandText = "INSERT INTO customers(id,customer_name,address,ph_no,vat_no,turn_over,due)"
+ "VALUES (@id,@customer_name,@address,@ph_no,@vat_no,@turn_over,@due)";
//msqlCommand.Parameters.AddWithValue("@sl_no", cusomerDataObject.serialNo);
msqlCommand.Parameters.AddWithValue("@id", cusomerDataObject.customerId);
msqlCommand.Parameters.AddWithValue("@customer_name", cusomerDataObject.customerName);
msqlCommand.Parameters.AddWithValue("@address", cusomerDataObject.customerAdress);
msqlCommand.Parameters.AddWithValue("@ph_no", cusomerDataObject.phoneNumber);
msqlCommand.Parameters.AddWithValue("@vat_no", cusomerDataObject.customerVatNo);
msqlCommand.Parameters.AddWithValue("@turn_over", cusomerDataObject.customerTurnOver);
msqlCommand.Parameters.AddWithValue("@due", cusomerDataObject.customerDue);
msqlCommand.ExecuteNonQuery();
}