private void SaveForm()
{
if (string.IsNullOrWhiteSpace(code_tb.Text))
{
MessageBox.Show("Code is mandatory", "FinOrg TrainingSubscription SaveForm");
return;
}
int code = -1;
if (!int.TryParse(code_tb.Text, out code))
{
MessageBox.Show("Please enter number code", "FinOrg TrainingSubscription SaveForm");
return;
}
SqlTransaction tr = null;
try
{
CalculateTotals();
using (SqlConnection con = getSqlConnection())
{
con.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
if (CurrentState == FinOrgFormState.New)
{
// check for existence
cmd = new SqlCommand("SELECT COUNT(*) FROM TR_SUBSCRIPTION WHERE TR_SUBSCRIPTION_CODE = @Code", con);
cmd.Parameters.Add(new SqlParameter("Code", code));
if ((int?)cmd.ExecuteScalar() != 0)
{
MessageBox.Show("Code Exists");
return;
}
cmd.Parameters.Clear();
}
tr = con.BeginTransaction();
cmd.Transaction = tr;
if (CurrentState == FinOrgFormState.New)
{
cmd.CommandText = "INSERT INTO TR_SUBSCRIPTION (TR_SUBSCRIPTION_CODE, CUSTOMER_ACC_NO, TR_SUBSCRIPTION_DATE, TR_SUBSCRIPTION_START_DATE, TR_SUBSCRIPTION_TOTAL, TR_SUBSCRIPTION_DISCOUNT, TR_SUBSCRIPTION_ADVANCE) VALUES (@Code, @CUS_ACC_NO, @Date, @StartDate, @Total, @Discount, @Advance)";
}
else
{
cmd.CommandText = "UPDATE TR_SUBSCRIPTION SET CUSTOMER_ACC_NO = @CUS_ACC_NO, TR_SUBSCRIPTION_DATE = @Date, TR_SUBSCRIPTION_START_DATE = @StartDate, TR_SUBSCRIPTION_TOTAL = @Total, TR_SUBSCRIPTION_DISCOUNT = @Discount, TR_SUBSCRIPTION_ADVANCE = @Advance WHERE TR_SUBSCRIPTION_CODE = @Code; DELETE FROM TR_SUBSCRIPTION_ITEM WHERE TR_SUBSCRIPTION_CODE = @Code";
}
cmd.Parameters.AddWithValue("Code", code);
cmd.Parameters.AddWithValue("CUS_ACC_NO", int.Parse(customer_tb.Text));
cmd.Parameters.AddWithValue("Date", trans_date.Value);
cmd.Parameters.AddWithValue("StartDate", start_date.Value);
cmd.Parameters.AddWithValue("Total", float.Parse(total_tb.Text));
cmd.Parameters.AddWithValue("Discount", float.Parse(discount_tb.Text));
cmd.Parameters.AddWithValue("Advance", float.Parse(advance_tb.Text));
cmd.ExecuteScalar();
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("Code", code);
StringBuilder sb = new StringBuilder();
DataView dv = items.DataSource as DataView;
for (int i = 0; i < dv.Count; i++)
{
DataRowView v = dv[i];
if (i > 0)
{
sb.Append(", ");
}
sb.Append(string.Format("(@Code, @Service{0}, @Period{0}, @Priority{0}, @Rate{0})", i));
cmd.Parameters.AddWithValue("Service" + i, v["TR_SUBITEM_SERVICE_CODE"]);
cmd.Parameters.AddWithValue("Period" + i, v["TR_SUBITEM_PERIOD_CODE"]);
cmd.Parameters.AddWithValue("Priority" + i, v["TR_SUBITEM_PRIORITY_CODE"]);
cmd.Parameters.AddWithValue("Rate" + i, v["TR_SUBITEM_RATE"]);
}
cmd.CommandText = "INSERT INTO TR_SUBSCRIPTION_ITEM (TR_SUBSCRIPTION_CODE, TR_SUBITEM_SERVICE_CODE, TR_SUBITEM_PERIOD_CODE, TR_SUBITEM_PRIORITY_CODE, TR_SUBITEM_RATE) VALUES " + sb.ToString();
cmd.ExecuteNonQuery();
tr.Commit();
}
MessageBox.Show("Success");
SetFormState(FinOrgFormState.View);
} catch (Exception ef)
{
tr.TryRollback();
MessageBox.Show("Failed" + "\n" + ef.Message, "F");
}
}
public void SaveServiceDetails()
{
if (string.IsNullOrWhiteSpace(service_code_tb.Text))
{
MessageBox.Show("Code is mandatory", "FinOrg TrainingService SaveServiceDetails");
return;
}
int code = -1;
if (!int.TryParse(service_code_tb.Text.Trim(), out code))
{
MessageBox.Show("Please enter number code", "FinOrg TrainingService SaveServiceDetails");
return;
}
SqlTransaction tr = null;
try
{
using (SqlConnection con = getSqlConnection())
{
con.Open();
SqlCommand cmd = null;
if (CurrentState == FinOrgFormState.New)
{
// check for existence
cmd = new SqlCommand("SELECT COUNT(*) FROM TR_SERVICES WHERE TR_SERVICE_CODE = @Code", con);
cmd.Parameters.Add(new SqlParameter("Code", code));
if ((int?)cmd.ExecuteScalar() != 0)
{
MessageBox.Show("Code Exists");
return;
}
cmd.Parameters.Clear();
}
tr = con.BeginTransaction();
cmd = new SqlCommand();
cmd.Connection = con;
cmd.Transaction = tr;
if (CurrentState == FinOrgFormState.New)
{
cmd.CommandText = "INSERT INTO TR_SERVICES (TR_SERVICE_CODE, TR_SERVICE_NAME, TR_SERVICE_ANAME) VALUES (@Code, @Name, @EName);";
}
else
{
cmd.CommandText = "UPDATE TR_SERVICES SET TR_SERVICE_NAME = @Name, TR_SERVICE_ANAME = @EName WHERE TR_SERVICE_CODE = @Code; DELETE FROM TR_SERVICE_RATES WHERE TR_SERVICE_CODE = @Code;";
}
cmd.Parameters.Add(new SqlParameter("Code", code));
cmd.Parameters.Add(new SqlParameter("Name", service_ename_tb.Text.Trim()));
cmd.Parameters.Add(new SqlParameter("EName", service_aname_tb.Text.Trim()));
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
cmd.Parameters.Add(new SqlParameter("@Code", code));
string rates_cmd_text = "INSERT INTO TR_SERVICE_RATES () VALUES ";
StringBuilder sb = new StringBuilder();
DataView dv = ratesDataGrid.DataSource as DataView;
for (int i = 0; i < dv.Count; i++)
{
if (i > 0)
{
sb.Append(", ");
}
sb.Append(string.Format("(@Code, @Period{0}, @Priority{0}, @Rate{0})", i));
cmd.Parameters.Add(new SqlParameter("Period" + i, dv[i]["TR_PERIOD_CODE"]));
cmd.Parameters.Add(new SqlParameter("Priority" + i, dv[i]["TR_PRIORITY_CODE"]));
cmd.Parameters.Add(new SqlParameter("Rate" + i, dv[i]["TR_SERVICE_RATE"]));
}
cmd.CommandText = "INSERT INTO TR_SERVICE_RATES (TR_SERVICE_CODE, TR_PERIOD_CODE, TR_PRIORITY_CODE, TR_SERVICE_RATE) VALUES " + sb.ToString();
cmd.ExecuteNonQuery();
tr.Commit();
}
SetFormState(FinOrgFormState.View);
} catch (Exception e)
{
tr.TryRollback();
MessageBox.Show("Failed" + "\n" + e.Message, "FinOrg TrainingService SaveServiceDetails");
}
}