private void button1_Click(object sender, EventArgs e)
{
string account = textBox1.Text;
if (incidentificate == "select")
{
ShowSelectedData showSelectedData = new ShowSelectedData(account);
this.Hide();
showSelectedData.ShowDialog();
}
if (incidentificate == "repayment")
{
int credit_id = 0;
int credit_type_id = 0;
SqlConnection conn = SQLConnection.connect();
conn.Open();
string creditString = "Select id, credit_type_id from credits where account='" + account + "'";
SqlCommand command_credit = new SqlCommand(creditString, conn);
using (SqlDataReader reader = command_credit.ExecuteReader())
{
if (reader.Read())
{
credit_id = int.Parse(reader["id"].ToString());
credit_type_id = int.Parse(reader["credit_type_id"].ToString());
}
}
Repayment repayment = new Repayment(credit_id, credit_type_id);
this.Hide();
repayment.ShowDialog();
}
}
private void button1_Click(object sender, EventArgs e)
{
DateTime date = Convert.ToDateTime(dateTimePicker1.Value.ToString("yyyy-MM-dd HH:mm:ss"));
int amount = int.Parse(numericUpDown1.Value.ToString());
string account = textBox1.Text;
SqlConnection conn = SQLConnection.connect();
conn.Open();
string creditString = "INSERT INTO credits (client_id, date_of_issue, fully_repaid, credit_type_id, mother_amount, account) OUTPUT INSERTED.ID VALUES ('" + customer_id + "', '" + date + "', '" + 0 + "', '" + credit_type_id + "', '" + amount + "', '" + account + "')";
SqlCommand command = new SqlCommand(creditString, conn);
int credit_id = (Int32)command.ExecuteScalar();
using (SqlCommand cmd = new SqlCommand("CalculateDay", conn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@credit_id", SqlDbType.Int).Value = credit_id;
cmd.Parameters.Add("@credit_type_id", SqlDbType.Int).Value = credit_type_id;
cmd.Parameters.Add("@credit_amount", SqlDbType.BigInt).Value = amount;
cmd.Parameters.Add("@interest_rate", SqlDbType.Float).Value = interest_rate;
cmd.ExecuteNonQuery();
this.Hide();
MessageBox.Show("This credit is successfully added.");
Employee_Page employee = new Employee_Page(GlobalVariables.EmployeeId, GlobalVariables.EmployeeFirstName, GlobalVariables.EmployeeLastName);
employee.ShowDialog();
}
}
private void button1_Click(object sender, EventArgs e)
{
string name = textBox1.Text;
string type_of_property = textBox2.Text;
string address = textBox3.Text;
string phone = textBox4.Text;
string passport_series = textBox5.Text;
string social_card_number = textBox6.Text;
string workplace = textBox7.Text;
string customerString = "INSERT INTO customers (name, type_of_property, costumer_address, phone, passport_series,social_card_number,workplace) OUTPUT INSERTED.ID VALUES ('" + name + "', '" + type_of_property + "', '" + address + "', '" + phone + "', '" + passport_series + "', '" + social_card_number + "', '" + workplace + "')";
SqlConnection conn = SQLConnection.connect();
conn.Open();
SqlCommand command = new SqlCommand(customerString, conn);
//command.ExecuteNonQuery();
int client_id = (Int32)command.ExecuteScalar();
if (checkBox1.Checked)
{
Guarantors guarantors = new Guarantors(client_id);
this.Hide();
guarantors.ShowDialog();
}
else
{
Credit_Types credit_Types = new Credit_Types(client_id);
this.Hide();
credit_Types.ShowDialog();
}
}
private void button1_Click(object sender, EventArgs e)
{
string login = textBox1.Text;
string password = textBox2.Text;
SqlConnection conn = SQLConnection.connect();
conn.Open();
string selectString = "Select id,firstname,lastname from employee where username='******' and pass='******'";
SqlCommand command = new SqlCommand(selectString, conn);
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
GlobalVariables.EmployeeId = int.Parse(reader["id"].ToString());
GlobalVariables.EmployeeFirstName = reader["firstname"].ToString();
GlobalVariables.EmployeeLastName = reader["lastname"].ToString();
Employee_Page employee = new Employee_Page(GlobalVariables.EmployeeId, GlobalVariables.EmployeeFirstName, GlobalVariables.EmployeeLastName);
this.Hide();
employee.ShowDialog();
}
}
conn.Close();
}
private void dateTimePicker1_ValueChanged(object sender, EventArgs e)
{
date = Convert.ToDateTime(dateTimePicker1.Value.ToString("yyyy-MM-dd HH:mm:ss"));
SqlConnection conn = SQLConnection.connect();
conn.Open();
string credit_payment_dayString = "Select id, payment_day, monthly_mother_amount, monthly_interest_amount from credit_payment_day where credit_id='" + credit_id + "' and Month(payment_day)='" + date.Month + "'and Year(payment_day)='" + date.Year + "'";
SqlCommand command_credit_payment_day = new SqlCommand(credit_payment_dayString, conn);
using (SqlDataReader reader = command_credit_payment_day.ExecuteReader())
{
if (reader.Read())
{
command_credit_payment_day_id = int.Parse(reader["id"].ToString());
payment_day = Convert.ToDateTime(reader["payment_day"].ToString());
monthly_mother_amount = float.Parse(reader["monthly_mother_amount"].ToString());
monthly_interest_amount = float.Parse(reader["monthly_interest_amount"].ToString());
}
}
int payment_day_month = int.Parse(date.Month.ToString()) - 1;
string repaymentString = "Select surcharge_mother_amount, surcharge_interest_amount from repayment where credit_id='" + credit_id + "' and Month(date_of_repayment)='" + payment_day_month + "'and Year(date_of_repayment)='" + date.Year + "'";
SqlCommand command_repayment = new SqlCommand(repaymentString, conn);
using (SqlDataReader reader = command_repayment.ExecuteReader())
{
if (reader.Read())
{
try
{
surcharge_mother_amount = (float)reader["surcharge_mother_amount"];
surcharge_interest_amount = (float)reader["surcharge_interest_amount"];
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
}
}
float all_amount = monthly_mother_amount + monthly_interest_amount + surcharge_mother_amount + surcharge_interest_amount;
string str = Math.Round(all_amount, MidpointRounding.AwayFromZero).ToString();
int last = int.Parse(str.Substring(str.Length - 1));
int balance = 0;
if (last != 0)
{
balance = 10 - last;
}
if (last == 0)
{
balance = 10;
}
int amount = (int)Math.Round(all_amount, MidpointRounding.AwayFromZero) + balance;
numericUpDown1.Minimum = amount;
}
private void button1_Click(object sender, EventArgs e)
{
string unicName = "";
int credit_type_id = 0;
int maximum_amount = 0;
int minimum_amount = 0;
if (radioButton1.Checked == true)
{
unicName = "cc01";
}
else if (radioButton2.Checked == true)
{
unicName = "lc12";
}
else if (radioButton3.Checked == true)
{
unicName = "mc02";
}
else
{
unicName = "bcc3";
}
SqlConnection conn = SQLConnection.connect();
conn.Open();
string selectString = "Select id,minimum_amount,maximum_amount,interest_rate from types_of_crediting where unic_name='" + unicName + "'";
SqlCommand command = new SqlCommand(selectString, conn);
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
credit_type_id = int.Parse(reader["id"].ToString());
string min = Convert.ToString(reader["minimum_amount"]);
string max = Convert.ToString(reader["maximum_amount"]);
float interest_rate = float.Parse(reader["interest_rate"].ToString());
if (min == "")
{
minimum_amount = 0;
}
else
{
minimum_amount = int.Parse(min);
}
maximum_amount = int.Parse(max);
Credit credit = new Credit(client_id, credit_type_id, minimum_amount, maximum_amount, interest_rate);
this.Hide();
credit.ShowDialog();
}
}
conn.Close();
}
private void button1_Click(object sender, EventArgs e)
{
string name1 = textBox1.Text;
string type_of_property1 = textBox2.Text;
string address1 = textBox3.Text;
string phone1 = textBox4.Text;
string passport_series1 = textBox5.Text;
string social_card_number1 = textBox6.Text;
string workplace1 = textBox7.Text;
string guarantor1String = "INSERT INTO customers (name, type_of_property, costumer_address, phone, passport_series,social_card_number,workplace,is_guarantor) OUTPUT INSERTED.ID VALUES ('" + name1 + "', '" + type_of_property1 + "', '" + address1 + "', '" + phone1 + "', '" + passport_series1 + "', '" + social_card_number1 + "', '" + workplace1 + "', '" + 1 + "')";
SqlConnection conn = SQLConnection.connect();
conn.Open();
SqlCommand command1 = new SqlCommand(guarantor1String, conn);
int guarantor1 = (Int32)command1.ExecuteScalar();
string name2 = textBox8.Text;
string type_of_property2 = textBox9.Text;
string address2 = textBox10.Text;
string phone2 = textBox11.Text;
string passport_series2 = textBox12.Text;
string social_card_number2 = textBox13.Text;
string workplace2 = textBox14.Text;
string guarantor2String = "INSERT INTO customers (name, type_of_property, costumer_address, phone, passport_series,social_card_number,workplace, is_guarantor) OUTPUT INSERTED.ID VALUES ('" + name2 + "', '" + type_of_property2 + "', '" + address2 + "', '" + phone2 + "', '" + passport_series2 + "', '" + social_card_number2 + "', '" + workplace2 + "', '" + 1 + "')";
SqlCommand command2 = new SqlCommand(guarantor2String, conn);
int guarantor2 = (Int32)command2.ExecuteScalar();
string creditor_guarantor1 = "INSERT INTO creditor_guarantor (creditor_id, guarantor_id) VALUES ('" + client_id + "', '" + guarantor1 + "')";
SqlCommand command21 = new SqlCommand(creditor_guarantor1, conn);
command21.ExecuteNonQuery();
string creditor_guarantor2 = "INSERT INTO creditor_guarantor (creditor_id, guarantor_id) VALUES ('" + client_id + "', '" + guarantor2 + "')";
SqlCommand command22 = new SqlCommand(creditor_guarantor2, conn);
command22.ExecuteNonQuery();
Credit_Types credit_Types = new Credit_Types(client_id);
this.Hide();
credit_Types.ShowDialog();
}
private void button1_Click(object sender, EventArgs e)
{
int sum = int.Parse(numericUpDown1.Value.ToString());
if (int.Parse(date.Day.ToString()) <= int.Parse(payment_day.Day.ToString()))
{
string repayment1String = "INSERT INTO repayment (credit_payment_day_id, credit_id, date_of_repayment, paid_mother_amounth, paid_interest_amounth, paid_prew_surcharge, surcharge_mother_amount, surcharge_interest_amount) VALUES ('" + command_credit_payment_day_id + "', '" + credit_id + "', '" + date + "', '" + 1 + "', '" + 1 + "', '" + 1 + "', '" + 0 + "', '" + 0 + "')";
SqlConnection conn = SQLConnection.connect();
conn.Open();
SqlCommand command1 = new SqlCommand(repayment1String, conn);
command1.ExecuteNonQuery();
}
else
{
float percentage_of_fines_for_each_day_mother_amount = 0;
float percentage_of_fines_for_each_day_interest_amount = 0;
//float mother_amount_surcharge = 0;
//float interest_amount_surcharge = 0;
int difference = int.Parse(date.Day.ToString()) - int.Parse(payment_day.Day.ToString());
SqlConnection conn = SQLConnection.connect();
conn.Open();
string credit_typeString = "Select percentage_of_fines_for_each_day_mother_amount, percentage_of_fines_for_each_day_interest_amount from types_of_crediting where id='" + credit_type_id + "'";
SqlCommand command_credit_type = new SqlCommand(credit_typeString, conn);
using (SqlDataReader reader = command_credit_type.ExecuteReader())
{
if (reader.Read())
{
percentage_of_fines_for_each_day_mother_amount = float.Parse(reader["percentage_of_fines_for_each_day_mother_amount"].ToString());
percentage_of_fines_for_each_day_interest_amount = float.Parse(reader["percentage_of_fines_for_each_day_interest_amount"].ToString());
}
}
float monthly_mother_percent = (monthly_mother_amount * percentage_of_fines_for_each_day_mother_amount) / 100;
float monthly_interest_percent = (monthly_interest_amount * percentage_of_fines_for_each_day_interest_amount) / 100;
mother_amount_surcharge = monthly_mother_percent * (float)difference;
interest_amount_surcharge = monthly_interest_percent * difference;
string sql = "Insert into repayment (credit_payment_day_id, credit_id, date_of_repayment, paid_mother_amounth, paid_interest_amounth, paid_prew_surcharge, surcharge_mother_amount, surcharge_interest_amount) "
+ " values (@credit_payment_day_id, @credit_id, @date_of_repayment, @paid_mother_amounth, @paid_interest_amounth, @paid_prew_surcharge, @surcharge_mother_amount, @surcharge_interest_amount) ";
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = sql;
cmd.Parameters.Add("@credit_payment_day_id", SqlDbType.Int).Value = command_credit_payment_day_id;
cmd.Parameters.Add("@credit_id", SqlDbType.Int).Value = credit_id;
cmd.Parameters.Add("@date_of_repayment", SqlDbType.DateTime).Value = date;
cmd.Parameters.Add("@paid_mother_amounth", SqlDbType.Bit).Value = 1;
cmd.Parameters.Add("@paid_interest_amounth", SqlDbType.Bit).Value = 1;
cmd.Parameters.Add("@paid_prew_surcharge", SqlDbType.Bit).Value = 1;
cmd.Parameters.Add("@surcharge_mother_amount", SqlDbType.Float).Value = mother_amount_surcharge;
cmd.Parameters.Add("@surcharge_interest_amount", SqlDbType.Float).Value = interest_amount_surcharge;
// Execute Command (for Delete,Insert or Update).
cmd.ExecuteNonQuery();
}
this.Hide();
MessageBox.Show("This credit is successfully paid.");
Employee_Page employee = new Employee_Page(GlobalVariables.EmployeeId, GlobalVariables.EmployeeFirstName, GlobalVariables.EmployeeLastName);
employee.ShowDialog();
}
private void ShowSelectedData_Load(object sender, EventArgs e)
{
int creditor_id = 0;
//int[] guarantor_id=new int[2];
int guarantor1_id = 0;
int guarantor2_id = 0;
int credit_type_id = 0;
SqlConnection conn = SQLConnection.connect();
conn.Open();
string creditString = "Select client_id, date_of_issue, fully_repaid, credit_type_id, mother_amount from credits where account='" + account + "'";
SqlCommand command_credit = new SqlCommand(creditString, conn);
using (SqlDataReader reader = command_credit.ExecuteReader())
{
if (reader.Read())
{
creditor_id = int.Parse(reader["client_id"].ToString());
credit_type_id = int.Parse(reader["credit_type_id"].ToString());
label6.Text = reader["date_of_issue"].ToString();
label8.Text = reader["mother_amount"].ToString();
label9.Text = account;
if ((bool)reader["fully_repaid"] == true)
{
label10.Text = "Yes";
}
else
{
label10.Text = "No";
}
}
}
string credit_type = "Select name from types_of_crediting where id='" + credit_type_id + "'";
SqlCommand command_credit_type = new SqlCommand(credit_type, conn);
using (SqlDataReader reader = command_credit_type.ExecuteReader())
{
if (reader.Read())
{
label7.Text = reader["name"].ToString();
}
}
string creditorString = "Select name,type_of_property,costumer_address,phone,passport_series,social_card_number,workplace from customers where id='" + creditor_id + "'";
SqlCommand command_creditor = new SqlCommand(creditorString, conn);
using (SqlDataReader reader = command_creditor.ExecuteReader())
{
if (reader.Read())
{
label18.Text = reader["name"].ToString();
label19.Text = reader["type_of_property"].ToString();
label20.Text = reader["costumer_address"].ToString();
label21.Text = reader["phone"].ToString();
label22.Text = reader["passport_series"].ToString();
label23.Text = reader["social_card_number"].ToString();
label24.Text = reader["workplace"].ToString();
}
}
string creditor_guarantorString = "Select guarantor_id from creditor_guarantor where creditor_id='" + creditor_id + "'";
SqlCommand command_creditor_guarantor = new SqlCommand(creditor_guarantorString, conn);
using (SqlDataReader reader = command_creditor_guarantor.ExecuteReader())
{
int idx = 0;
while (reader.Read() && idx < 2)
{
if (idx == 0)
{
guarantor1_id = int.Parse(reader["guarantor_id"].ToString());
}
if (idx == 1)
{
guarantor2_id = int.Parse(reader["guarantor_id"].ToString());
}
idx++;
}
}
if (guarantor1_id != 0)
{
string guarantor1String = "Select name,type_of_property,costumer_address,phone,passport_series,social_card_number,workplace from customers where id='" + guarantor1_id + "'";
SqlCommand command_guarantor1 = new SqlCommand(guarantor1String, conn);
using (SqlDataReader reader = command_guarantor1.ExecuteReader())
{
if (reader.Read())
{
label32.Text = reader["name"].ToString();
label33.Text = reader["type_of_property"].ToString();
label34.Text = reader["costumer_address"].ToString();
label35.Text = reader["phone"].ToString();
label36.Text = reader["passport_series"].ToString();
label37.Text = reader["social_card_number"].ToString();
label38.Text = reader["workplace"].ToString();
}
}
}
else
{
label32.Text = "";
label33.Text = "";
label34.Text = "";
label35.Text = "";
label36.Text = "";
label37.Text = "";
label38.Text = "";
}
if (guarantor2_id != 0)
{
string guarantor2String = "Select name,type_of_property,costumer_address,phone,passport_series,social_card_number,workplace from customers where id='" + guarantor2_id + "'";
SqlCommand command_guarantor2 = new SqlCommand(guarantor2String, conn);
using (SqlDataReader reader = command_guarantor2.ExecuteReader())
{
if (reader.Read())
{
label46.Text = reader["name"].ToString();
label47.Text = reader["type_of_property"].ToString();
label48.Text = reader["costumer_address"].ToString();
label49.Text = reader["phone"].ToString();
label50.Text = reader["passport_series"].ToString();
label51.Text = reader["social_card_number"].ToString();
label52.Text = reader["workplace"].ToString();
}
}
}
else
{
label46.Text = "";
label47.Text = "";
label48.Text = "";
label49.Text = "";
label50.Text = "";
label51.Text = "";
label52.Text = "";
}
}
