private List <ServiceMadeModel> getServicesFromPromo(ClientModel c)
{
List <ServiceMadeModel> lstPromoServices = new List <ServiceMadeModel>();
ServiceMadeModel s = new ServiceMadeModel();
queryString = "SELECT dbspa.tblpromoservicesclient.ID, dbspa.tblpromo.promoname, dbspa.tblpromo.price FROM " +
"(dbspa.tblpromoservicesclient INNER JOIN dbspa.tblpromo ON dbspa.tblpromoservicesclient.promoID = dbspa.tblpromo.ID) " +
"WHERE dbspa.tblpromoservicesclient.clientID = ? AND dateserviced = ?";
parameters = new List <string>();
parameters.Add(c.ID1);
DateTime date = DateTime.Parse(DateTime.Now.ToShortDateString());
parameters.Add(date.Year + "/" + date.Month + "/" + date.Day);
MySqlDataReader reader = conDB.getSelectConnection(queryString, parameters);
while (reader.Read())
{
s.ID = reader["ID"].ToString();
s.ServiceType = reader["promoname"].ToString();
s.Price = reader["price"].ToString();
lstPromoServices.Add(s);
s = new ServiceMadeModel();
}
conDB.closeConnection();
return(lstPromoServices);
}
private void btnDelete_Click(object sender, RoutedEventArgs e)
{
System.Windows.Forms.DialogResult dialogResult = System.Windows.Forms.MessageBox.Show("Are you sure you want to Delete record?", "Delete Record", System.Windows.Forms.MessageBoxButtons.YesNo);
if (dialogResult == System.Windows.Forms.DialogResult.Yes)
{
ServiceMadeModel cm = dgvServiceMade.SelectedItem as ServiceMadeModel;
if (cm != null)
{
int id = Convert.ToInt32(cm.ID);
if (id != 0)
{
deleteServiceMadeRecord(id);
dgvServiceMade.ItemsSource = getServiceForClient(Convert.ToInt32(clientModel.ID1));
System.Windows.MessageBox.Show("Record deleted successfuly!");
conDB.writeLogFile("DELETE CLIENT RECORD: DELETED CLIENT ID: " + cm.ID);
}
}
else
{
System.Windows.MessageBox.Show("No record selected!");
}
}
}
private List <ServiceMadeModel> getServiceMade(int id)
{
List <ServiceMadeModel> lstSMM = new List <ServiceMadeModel>();
ServiceMadeModel smm = new ServiceMadeModel();
queryString = "SELECT dbspa.tblservicemade.ID, serviceTypeID, therapistID, dateServiced, isDiscounted, discountID, " +
"dbspa.tblservicetype.price, dbspa.tblservicetype.serviceType FROM (dbspa.tblservicemade INNER JOIN dbspa.tblservicetype ON dbspa.tblservicemade.serviceTypeID = dbspa.tblservicetype.ID) " +
"WHERE(clientID = ?) AND (dbspa.tblservicemade.isDeleted = 0) AND dateServiced = ?";
parameters = new List <string>();
parameters.Add(id.ToString());
DateTime date = DateTime.Parse(DateTime.Now.ToShortDateString());
parameters.Add(date.Year + "/" + date.Month + "/" + date.Day);
MySqlDataReader reader = conDB.getSelectConnection(queryString, parameters);
while (reader.Read())
{
smm.ID = reader["ID"].ToString();
smm.ServiceTypeID = reader["serviceTypeID"].ToString();
smm.ServiceType = reader["serviceType"].ToString();
smm.Therapist = reader["therapistID"].ToString();
smm.isDiscounted = reader["isDiscounted"].ToString();
smm.Discounted = reader["discountID"].ToString();
smm.Price = reader["price"].ToString();
lstSMM.Add(smm);
smm = new ServiceMadeModel();
}
conDB.closeConnection();
return(lstSMM);
}
private void btnPrint_Click(object sender, RoutedEventArgs e)
{
List <ServiceMadeModel> newListServices = new List <ServiceMadeModel>();
for (int i = 0; i < dgvSalary.Items.Count - 1; i++)
{
var item = dgvSalary.Items[i];
var mycheckbox = dgvSalary.Columns[4].GetCellContent(item) as CheckBox;
ServiceMadeModel smms = item as ServiceMadeModel;
if (smms != null)
{
if (!smms.ifPaid)
{
smms.ifPaid = false;
newListServices.Add(smms);
}
}
}
compute(newListServices);
ReportForm rf = new ReportForm(therapist, lstTherapist);
rf.ShowDialog();
}
private List <ServiceMadeModel> getPromoServicesMadeForClient()
{
List <ServiceMadeModel> lstPromoServices = new List <ServiceMadeModel>();
ServiceMadeModel s = new ServiceMadeModel();
queryString = "SELECT dbspa.tblpromoservicesclient.ID, clientID, promoID, promoprice, loyaltyID, dbspa.tblpromo.promoname,dbspa.tblpromoservicesclient.dateserviced, " +
"IF(dbspa.tblpromoservicesclient.savetocard, 'YES', 'NO') AS 'SAVED TO CARD' FROM (dbspa.tblpromoservicesclient INNER JOIN dbspa.tblpromo " +
"ON dbspa.tblpromoservicesclient.promoID = dbspa.tblpromo.ID) WHERE clientID = ?";
parameters = new List <string>();
parameters.Add(clientModel.ID1);
MySqlDataReader reader = conDB.getSelectConnection(queryString, parameters);
while (reader.Read())
{
s.ID = reader["ID"].ToString();
s.PromoServicesClientID = reader["clientID"].ToString();
s.ServiceType = reader["promoname"].ToString();
s.PromoID = reader["promoID"].ToString();
s.DateServiced = reader["dateserviced"].ToString();
s.Price = reader["promoprice"].ToString();
s.isSavedToCard = reader["SAVED TO CARD"].ToString();
s.ifPromoService = true;
lstPromoServices.Add(s);
s = new ServiceMadeModel();
}
conDB.closeConnection();
return(lstPromoServices);
}
private void btnEdit_Click(object sender, RoutedEventArgs e)
{
ServiceMadeModel servMade = dgvServiceMade.SelectedItem as ServiceMadeModel;
if (servMade != null)
{
if (!string.IsNullOrEmpty(datePicker.Text))
{
DateTime date1 = DateTime.Parse(servMade.DateServiced);
string strDate = DateTime.Now.ToShortDateString();
DateTime now = DateTime.Parse(strDate);
if (date1 < now && user.Type > 1)
{
System.Windows.MessageBox.Show("Sorry. You can't edit previous rendered service!");
}
else
{
servMade.DateServiced = datePicker.Text;
ServiceForm service = new ServiceForm(this, servMade, clientModel, user);
service.ShowDialog();
}
}
else
{
System.Windows.MessageBox.Show("Please select date.");
}
}
else
{
System.Windows.MessageBox.Show("No record selected!");
}
}
private List <ServiceMadeModel> getAllPromoServicesRenderedByDate()
{
List <ServiceMadeModel> lstServiceMade = new List <ServiceMadeModel>();
ServiceMadeModel serviceMade = new ServiceMadeModel();
queryString = "SELECT dbspa.tblpromoservicesclient.ID, dbspa.tblpromo.commission, promoID, promoprice, " +
"loyaltyID, dbspa.tblpromo.promoname,dbspa.tblpromoservicesclient.dateserviced, IF(dbspa.tblpromoservicesclient.savetocard, 'YES', 'NO')" +
" AS 'SAVED TO CARD' FROM (dbspa.tblpromoservicesclient INNER JOIN dbspa.tblpromo ON dbspa.tblpromoservicesclient.promoID = dbspa.tblpromo.ID)" +
" WHERE dateserviced BETWEEN ? AND ? ";
parameters = new List <string>();
DateTime date = DateTime.Parse(datePickerFrom.Text);
parameters.Add(date.Year + "/" + date.Month + "/" + date.Day);
date = DateTime.Parse(datePickerTo.Text);
parameters.Add(date.Year + "/" + date.Month + "/" + date.Day);
MySqlDataReader reader = conDB.getSelectConnection(queryString, parameters);
while (reader.Read())
{
serviceMade.ID = reader["ID"].ToString();
serviceMade.ServiceType = reader["promoname"].ToString();
serviceMade.Price = reader["promoprice"].ToString();
lstServiceMade.Add(serviceMade);
serviceMade = new ServiceMadeModel();
}
conDB.closeConnection();
return(lstServiceMade);
}
private List <ServiceMadeModel> getServiceForClient(int id)
{
List <ServiceMadeModel> lstServiceMade = new List <ServiceMadeModel>();
try
{
queryString = "SELECT dbspa.tblservicemade.ID, dbspa.tblservicetype.servicetype AS 'SERVICE TYPE', " +
"dbspa.tbltherapist.description AS 'THERAPIST', dbspa.tblservicemade.dateServiced, dbspa.tblservicemade.discountID," +
"IF(dbspa.tblservicemade.isDiscounted, ROUND(dbspa.tblservicetype.price - dbspa.tblservicetype.price * (dbspa.tbldiscount.discount / 100)), dbspa.tblservicetype.price) AS 'PRICE', " +
"IF(dbspa.tblservicemade.isDiscounted, 'YES', 'NO') AS 'DISCOUNTED', IF(dbspa.tblservicemade.savetocard, 'YES', 'NO') AS 'SAVED TO CARD', " +
"IF(dbspa.tblservicemade.firstfree, 'YES', 'NO') AS 'FREE 30 MINS', IF(dbspa.tblservicemade.secondfree, 'YES', 'NO') AS 'FREE 1HR', " +
"dbspa.tblservicemade.timeIn, dbspa.tblservicemade.timeOut, dbspa.tblcommissions.commission FROM (((((dbspa.tblservicemade INNER JOIN dbspa.tblclient ON dbspa.tblservicemade.clientID = dbspa.tblClient.ID) " +
"INNER JOIN dbspa.tblservicetype ON tblservicemade.serviceTypeID = dbspa.tblservicetype.ID) " +
"INNER JOIN dbspa.tbltherapist ON dbspa.tblservicemade.therapistID = dbspa.tbltherapist.ID) " +
"INNER JOIN dbspa.tbldiscount ON dbspa.tblservicemade.discountID = dbspa.tbldiscount.ID) " +
"INNER JOIN dbspa.tblcommissions ON dbspa.tblservicemade.commissionID = dbspa.tblcommissions.ID) " +
"WHERE (dbspa.tblclient.ID = ?) " +
"AND (dbspa.tblservicemade.isDeleted = 0) ORDER BY ID desc";
parameters = new List <string>();
parameters.Add(id.ToString());
MySqlDataReader reader = conDB.getSelectConnection(queryString, parameters);
while (reader.Read())
{
ServiceMadeModel serviceMade = new ServiceMadeModel();
serviceMade.ID = reader["ID"].ToString();
DateTime dte = DateTime.Parse(reader["dateServiced"].ToString());
serviceMade.DateServiced = dte.ToShortDateString();
serviceMade.ServiceType = reader["SERVICE TYPE"].ToString();
serviceMade.Therapist = reader["THERAPIST"].ToString();
serviceMade.Price = reader["PRICE"].ToString();
serviceMade.Discounted = reader["DISCOUNTED"].ToString();
serviceMade.Discount = reader["discountID"].ToString();
serviceMade.isSavedToCard = reader["SAVED TO CARD"].ToString();
serviceMade.FirstFree = reader["FREE 30 MINS"].ToString();
serviceMade.SecondFree = reader["FREE 1HR"].ToString();
serviceMade.ifPromoService = false;
serviceMade.TimeIn = reader["timeIn"].ToString();
serviceMade.TimeOut = reader["timeOut"].ToString();
serviceMade.Commission = reader["commission"].ToString();
lstServiceMade.Add(serviceMade);
}
lstServiceMade.AddRange(getPromoServicesMadeForClient());
conDB.closeConnection();
}
catch (Exception ex)
{
System.Windows.MessageBox.Show(ex.Message);
}
return(lstServiceMade);
}
private List <ServiceMadeModel> searchServicesRenderedInPromoServices(List <string> lst)
{
List <ServiceMadeModel> lstPromoServicesRendered = new List <ServiceMadeModel>();
ServiceMadeModel renderedServices = new ServiceMadeModel();
foreach (string strPromoID in lst)
{
queryString = "SELECT dbspa.tblpromoservicemade.ID, dateserviced, dbspa.tbltherapist.description, " +
"dbspa.tblpromo.promoname, (truncate(dbspa.tblpromo.commission / numOfServ, 2)) AS 'commission' FROM (SELECT COUNT(*) as numOfServ " +
"FROM dbspa.tblpromoservices where promoID = ? AND isDeleted = 0) AS tbl1, ((dbspa.tblpromoservicemade INNER JOIN " +
"dbspa.tbltherapist ON dbspa.tblpromoservicemade.therapistID = dbspa.tbltherapist.ID) INNER JOIN " +
"dbspa.tblpromo ON dbspa.tblpromoservicemade.promoID = dbspa.tblpromo.ID) WHERE dbspa.tblpromoservicemade.isDeleted = 0" +
" AND (dbspa.tblpromoservicemade.dateserviced BETWEEN ? AND ?) AND (dbspa.tblpromoservicemade.therapistID = ?)";
parameters = new List <string>();
parameters.Add(strPromoID);
DateTime date = DateTime.Parse(dateFrom.Text);
parameters.Add(date.Year + "/" + date.Month + "/" + date.Day);
date = DateTime.Parse(dateTo.Text);
parameters.Add(date.Year + "/" + date.Month + "/" + date.Day);
parameters.Add(therapist.ID1);
MySqlDataReader reader = conDB.getSelectConnection(queryString, parameters);
while (reader.Read())
{
renderedServices.ifPromoService = true;
renderedServices.ID = reader["ID"].ToString();
DateTime dte = DateTime.Parse(reader["dateserviced"].ToString());
renderedServices.DateServiced = dte.ToShortDateString();
renderedServices.Therapist = reader["description"].ToString();
renderedServices.ServiceType = reader["promoname"].ToString();
renderedServices.Commission = reader["commission"].ToString();
lstPromoServicesRendered.Add(renderedServices);
renderedServices = new ServiceMadeModel();
}
}
conDB.closeConnection();
return(lstPromoServicesRendered);
}
private void btnUpdateRecords_Click(object sender, RoutedEventArgs e)
{
for (int i = 0; i < dgvSalary.Items.Count; i++)
{
progressBar.IsActive = true;
var item = dgvSalary.Items[i];
var mycheckbox = dgvSalary.Columns[4].GetCellContent(item) as CheckBox;
ServiceMadeModel smms = item as ServiceMadeModel;
if (smms != null && mycheckbox != null)
{
updateRecordToPaid((bool)mycheckbox.IsChecked, smms.ID);
conDB.writeLogFile("COMMISSION SET TO PAID?: " + (bool)mycheckbox.IsChecked + " RECORD ID:" + smms.ID);
}
}
MessageBox.Show("SELECTED RECORDS UPDATED SUCCESSFULLY!");
progressBar.IsActive = false;
}
private List <ServiceMadeModel> getAllServicesRenderedByDate()
{
List <ServiceMadeModel> lstServiceMade = new List <ServiceMadeModel>();
ServiceMadeModel serviceMade = new ServiceMadeModel();
queryString = "SELECT dbspa.tblservicemade.ID, dbspa.tblservicetype.servicetype AS 'SERVICE TYPE', " +
"dbspa.tbltherapist.description AS 'THERAPIST', dbspa.tblservicemade.dateServiced, dbspa.tblcommissions.commission, " +
"IF(dbspa.tblservicemade.isDiscounted, ROUND(dbspa.tblservicetype.price - dbspa.tblservicetype.price * (dbspa.tbldiscount.discount / 100)), " +
"dbspa.tblservicetype.price) AS 'PRICE', IF(dbspa.tblservicemade.isDiscounted, 'YES', 'NO') AS 'DISCOUNTED' " +
"FROM (((((dbspa.tblservicemade INNER JOIN dbspa.tblclient ON dbspa.tblservicemade.clientID = dbspa.tblClient.ID) " +
"INNER JOIN dbspa.tblservicetype ON tblservicemade.serviceTypeID = dbspa.tblservicetype.ID) " +
"INNER JOIN dbspa.tbltherapist ON dbspa.tblservicemade.therapistID = dbspa.tbltherapist.ID) " +
"INNER JOIN dbspa.tbldiscount ON dbspa.tblservicemade.discountID = dbspa.tbldiscount.ID) " +
"INNER JOIN dbspa.tblcommissions ON dbspa.tblservicemade.commissionID = dbspa.tblcommissions.ID) WHERE (dbspa.tblservicemade.isDeleted = 0)" +
" AND (dbspa.tblclient.isDeleted = 0) AND (dbspa.tbltherapist.isDeleted = 0) AND (dbspa.tblservicemade.dateServiced BETWEEN ? AND ?)";
parameters = new List <string>();
DateTime date = DateTime.Parse(datePickerFrom.Text);
parameters.Add(date.Year + "/" + date.Month + "/" + date.Day);
date = DateTime.Parse(datePickerTo.Text);
parameters.Add(date.Year + "/" + date.Month + "/" + date.Day);
MySqlDataReader reader = conDB.getSelectConnection(queryString, parameters);
while (reader.Read())
{
serviceMade.ID = reader["ID"].ToString();
serviceMade.ServiceType = reader["SERVICE TYPE"].ToString();
serviceMade.Therapist = reader["THERAPIST"].ToString();
serviceMade.DateServiced = reader["dateServiced"].ToString();
serviceMade.Price = reader["PRICE"].ToString();
serviceMade.Commission = reader["commission"].ToString();
serviceMade.Discounted = reader["DISCOUNTED"].ToString();
lstServiceMade.Add(serviceMade);
serviceMade = new ServiceMadeModel();
}
conDB.closeConnection();
lstServiceMade.AddRange(getAllPromoServicesRenderedByDate());
return(lstServiceMade);
}
public void searchTherapist()
{
lstServiceMade = new List <ServiceMadeModel>();
ServiceMadeModel serviceMade = new ServiceMadeModel();
if (therapist != null)
{
try
{
queryString = "SELECT tblServiceMade.ID, dbspa.tblservicemade.dateServiced as 'DATE SERVICED'," +
"dbspa.tbltherapist.description as 'THERAPIST', dbspa.tblservicetype.serviceType as 'SERVICE TYPE'," +
" dbspa.tblcommissions.commission, dbspa.tblservicemade.isDiscounted, dbspa.tbldiscount.discount, ifPaid, " +
"concat(dbspa.tblclient.firstName, ' ', dbspa.tblclient.lastName) AS CLIENT_NAME FROM (((((dbspa.tblservicemade " +
"INNER JOIN dbspa.tbltherapist ON dbspa.tblservicemade.therapistID = dbspa.tblTherapist.ID) " +
"INNER JOIN dbspa.tblservicetype ON dbspa.tblservicemade.serviceTypeID = dbspa.tblservicetype.ID) " +
"INNER JOIN dbspa.tblcommissions ON dbspa.tblservicemade.commissionID = dbspa.tblcommissions.ID) " +
"INNER JOIN dbspa.tbldiscount ON dbspa.tblservicemade.discountID = dbspa.tbldiscount.ID) " +
"INNER JOIN dbspa.tblclient ON dbspa.tblservicemade.clientID = dbspa.tblclient.ID) " +
"WHERE(dbspa.tbltherapist.ID = ?) AND (dbspa.tblservicemade.dateServiced BETWEEN ? AND ?) AND " +
"(dbspa.tblservicemade.isDeleted = 0) AND (dbspa.tblclient.isDeleted = 0) AND (dbspa.tblservicetype.isDeleted = 0)";
parameters = new List <string>();
parameters.Add(therapist.ID1);
DateTime date = DateTime.Parse(dateFrom.Text);
parameters.Add(date.Year + "/" + date.Month + "/" + date.Day);
date = DateTime.Parse(dateTo.Text);
parameters.Add(date.Year + "/" + date.Month + "/" + date.Day);
MySqlDataReader reader = conDB.getSelectConnection(queryString, parameters);
int i = 0;
double total = 0.0;
while (reader.Read())
{
serviceMade.ID = reader["ID"].ToString();
DateTime dte = DateTime.Parse(reader["DATE SERVICED"].ToString());
serviceMade.DateServiced = dte.ToShortDateString();
serviceMade.Therapist = reader["THERAPIST"].ToString();
serviceMade.ServiceType = reader["SERVICE TYPE"].ToString();
serviceMade.Commission = reader["commission"].ToString();
string strPaid = reader["ifPaid"].ToString();
serviceMade.ClientName = reader["CLIENT_NAME"].ToString();
if (strPaid.Equals("1"))
{
serviceMade.ifPaid = true;
}
else
{
serviceMade.ifPaid = false;
}
serviceMade.ifPromoService = false;
serviceMade.isDiscounted = reader["isDiscounted"].ToString();
serviceMade.Discount = reader["discount"].ToString();
if (serviceMade.isDiscounted.Equals("1"))
{
double a = Convert.ToInt32(serviceMade.Commission);
double b = (Convert.ToDouble(serviceMade.Discount) / 100);
double dsc = a * b;
serviceMade.Commission = (Convert.ToInt32(serviceMade.Commission) - dsc).ToString();
}
lstServiceMade.Add(serviceMade);
serviceMade = new ServiceMadeModel();
}
conDB.closeConnection();
lstServiceMade.AddRange(searchServicesRenderedInPromoServices(getAllPromoIDForTherapist()));
lstServiceMade = lstServiceMade.OrderByDescending(srv => srv.DateServiced).ToList();
dgvSalary.ItemsSource = lstServiceMade;
lblRecordsFound.Content = lstServiceMade.Count + " RECORD/S FOUND.";
foreach (ServiceMadeModel smm in lstServiceMade)
{
if (!smm.ifPaid)
{
double price = Convert.ToDouble(smm.Commission);
total += price;
}
i++;
}
lblTotal.Content = "TOTAL COMMISSION: " + total.ToString();
}
catch (Exception ex)
{
MessageBox.Show("ERROR " + ex.Message);
}
}
}
