private void button3_Click(object sender, EventArgs e)
{
if (!String.IsNullOrEmpty(nationalIdtext.Text))
{
String sql = "select * from Clients where national_id = '" + nationalIdtext.Text + "' and TnazolState <>'F' and check_out='N' and stopState='N'";
conn.SQLCODE(sql, false);
if (conn.myReader.Read())
{
groupBox2.Enabled = true;
agentNameText.Text = conn.myReader["client_name"].ToString();
ID = int.Parse(conn.myReader["ID"].ToString());
String sql2 = "select SUM(paid_value) As total from first_paids where client_id =" + ID + "";
conn.SQLCODE(sql2, false);
if (conn.myReader.Read())
{
moadmValue.Text = conn.myReader["total"].ToString();
}
}
else
{
MessageBox.Show("لا يوجد عميل بهذا الرقم");
}
}
else
{
MessageBox.Show("برجاء اتمام البيانات");
}
}
private void searchBtn_Click(object sender, EventArgs e)
{
conn.startConnection();
if (!String.IsNullOrEmpty(national_id.Text))
{
String sql = "select * from Clients where national_id = '" + national_id.Text + "' and deleted = 'N' and Clients.check_out = 'N' and stopState='N'";
conn.SQLCODE(sql, false);
if (conn.myReader.Read())
{
clientName.Text = conn.myReader["client_name"].ToString();
int client_ID = int.Parse(conn.myReader["ID"].ToString());
if (mo2dmatRadio.Checked)
{
sql = "SELECT first_paids.details as 'التفاصيل',paid_value as 'القيمة المدفوعة', bank_accounts.type as 'الحساب المستلم',paid_date AS 'تاريخ السداد',Clients.client_name as 'اسم العميل' ,first_paids.group_id,first_paids.ID FROM((first_paids INNER JOIN bank_accounts ON first_paids.bank_id = bank_accounts.ID) INNER JOIN Clients ON (first_paids.client_id = Clients.ID and Clients.check_out = 'N' and stopState='N')) where first_paids.client_id = " + client_ID + " order by paid_date";
}
else
{
int PaymentType = (type0Radio.Checked) ? 0 : 1;
sql = "SELECT montsben.details as 'التفاصيل',paid_value as 'القيمة المدفوعة', bank_accounts.type as 'الحساب المستلم',paid_date AS 'تاريخ السداد',Clients.client_name as 'اسم العميل' ,montsben.group_id,montsben.ID FROM((montsben INNER JOIN bank_accounts ON montsben.bank_id = bank_accounts.ID) INNER JOIN Clients ON (montsben.client_id = Clients.ID and Clients.check_out = 'N')) where PaymentType=" + PaymentType + " and montsben.client_id = " + client_ID + " order by paid_date";
}
conn.SQLCODE(sql, true);
DataTable table = new DataTable();
conn.myAdabter.Fill(table);
dataGridView1.DataSource = table;
dataGridView1.Columns[6].Visible = false;
dataGridView1.Columns[5].Visible = false;
dataGridView1.Columns[4].Visible = false;
}
}
}
private void doSome(TextBox Result, String sql2)
{
conn.SQLCODE(sql2, false);
if (conn.myReader.Read())
{
Result.Text = conn.myReader["total"].ToString();
}
}
private void button1_Click(object sender, EventArgs e)
{
conn.startConnection();
if (checkBox1.Checked)
{
try
{
String date1 = dateTimePicker1.Value.ToString("yyyy/MM/dd");
String date2 = dateTimePicker2.Value.ToString("yyyy/MM/dd");
String sql = "SELECT aqsat.details as 'تفاصيل الايصالات', bank_accounts.type as 'الحساب المستلم',qest_date AS 'تاريخ القسط',aqsat.qest_value as 'قيمة القسط', Clients.client_name as 'اسم العميل' ,aqsat.group_id,aqsat.ID FROM((aqsat INNER JOIN bank_accounts ON aqsat.bank_id = bank_accounts.ID) INNER JOIN Clients ON (aqsat.client_id = Clients.ID and Clients.check_out = 'N')) WHERE qest_date between #" + date1 + "# and #" + date2 + "# order by qest_date";
conn.SQLCODE(sql, true);
DataTable table = new DataTable();
conn.myAdabter.Fill(table);
dataGridView1.DataSource = table;
dataGridView1.Columns[5].Visible = false;
dataGridView1.Columns[6].Visible = false;
this.Invoke((MethodInvoker) delegate() { sumValues(3); });
}
catch (Exception ex)
{
MessageBox.Show("خطأ ف البيانات المدخلة");
Logger.WriteLog("[" + DateTime.Now + "] ExceptionString: " + ex.ToString() + " InnerException: " + ex.InnerException + " ExceptionMessage: " + ex.Message + ". [" + this.Name + "] By [" + SessionInfo.empName + "]");
}
}
else
{
if (!String.IsNullOrEmpty(national_id.Text))
{
String sql = "select * from Clients where national_id = '" + national_id.Text + "' and deleted = 'N' and Clients.check_out = 'N'";
conn.SQLCODE(sql, false);
if (conn.myReader.Read())
{
clientName.Text = conn.myReader["client_name"].ToString();
ID = int.Parse(conn.myReader["ID"].ToString());
}
else
{
MessageBox.Show("لا يوجد عضو بهذا الرقم القومي ");
}
String sql2 = "select aqsat.details as 'تفاصيل الايصالات' ,bank_accounts.type as 'الحساب المستلم',qest_date AS 'تاريخ القسط',aqsat.qest_value as 'قيمة القسط' ,group_id,aqsat.ID from (aqsat INNER JOIN bank_accounts ON aqsat.bank_id = bank_accounts.ID) where client_id =" + ID + " order by qest_date";
conn.SQLCODE(sql2, true);
DataTable table = new DataTable();
conn.myAdabter.Fill(table);
dataGridView1.DataSource = table;
dataGridView1.Columns[4].Visible = false;
dataGridView1.Columns[5].Visible = false;
this.Invoke((MethodInvoker) delegate() { sumValues(3); });
}
else
{
MessageBox.Show("برجاء اتمام البيانات");
}
}
}
private void loadAvailableUnits()
{
String sql = "select unit_type , available_units from units ";
conn.SQLCODE(sql, false);
while (conn.myReader.Read())
{
availUnitsCombo.Items.Add(conn.myReader[0].ToString());
availList.Add(int.Parse(conn.myReader[1].ToString()));
}
}
private void searchBtn_Click(object sender, EventArgs e)
{
conn.startConnection();
int ID = -1;
try
{
if (!String.IsNullOrEmpty(national_id.Text))
{
String sql = "select * from Clients where national_id = '" + national_id.Text + "' and deleted = 'N' and Clients.check_out = 'N'";
conn.SQLCODE(sql, false);
if (conn.myReader.Read())
{
clientName.Text = conn.myReader["client_name"].ToString();
ID = int.Parse(conn.myReader["ID"].ToString());
String sql2 = @"SELECT client_name as 'الاسم', client_id, SUM(PAIDVALUE) AS 'اجمالي المدفوع في السنه', DatePart('yyyy', [YEAR]) AS 'السنه'
FROM (SELECT Clients.client_name, aqsat.client_id, aqsat.qest_value AS PAIDVALUE, aqsat.qest_date AS [YEAR]
FROM (aqsat LEFT OUTER JOIN Clients ON aqsat.client_id = Clients.ID)
UNION
SELECT Clients_2.client_name, first_paids.client_id, first_paids.paid_value AS FIRSTSUM, first_paids.paid_date AS [YEAR]
FROM (first_paids LEFT OUTER JOIN
Clients Clients_2 ON first_paids.client_id = Clients_2.ID)
UNION
SELECT Clients_1.client_name, T5sesMoney.client_id, T5sesMoney.paid_value AS MONTSEBSUM, T5sesMoney.paid_date AS [YEAR]
FROM (Clients Clients_1 INNER JOIN
T5sesMoney ON T5sesMoney.client_id = Clients_1.ID)) temp
GROUP BY client_name, client_id, DatePart('yyyy', [YEAR])
HAVING (client_id = " + ID + ") ORDER BY client_id ";
conn.SQLCODE(sql2, true);
DataTable table = new DataTable();
conn.myAdabter.Fill(table);
dataGridView1.DataSource = table;
dataGridView1.Columns[1].Visible = false;
//dataGridView1.Rows[0].Cells[0]
for (int i = 1; i < dataGridView1.Rows.Count; i++)
{
dataGridView1.Rows[i].Cells[0].Value = "";
}
}
else
{
MessageBox.Show("لا يوجد عضو بهذا الرقم القومي ");
}
}
}
catch (Exception ex)
{
MessageBox.Show("خطأ ف البيانات المدخلة");
Logger.WriteLog("[" + DateTime.Now + "] ExceptionString: " + ex.ToString() + " InnerException: " + ex.InnerException + " ExceptionMessage: " + ex.Message + ". [" + this.Name + "] By [" + SessionInfo.empName + "]");
}
}
private void loadBankAccounts()
{
banksData.Clear();
con.startConnection();
String sql = "select id,type from bank_accounts";
con.SQLCODE(sql, false);
while (con.myReader.Read())
{
banksData.Add(new ComboBoxItem
{
Text = con.myReader[1].ToString(),
value = con.myReader[0].ToString()
});
}
}
private void searchButton_Click(object sender, EventArgs e)
{
if (!String.IsNullOrEmpty(SheeqNumText.Text))
{
try
{
conn.startConnection();
int SheeqNum = int.Parse(SheeqNumText.Text);
String sql = "SELECT * FROM cheques WHERE cheque_number = " + SheeqNum + "";
conn.SQLCODE(sql, false);
if (conn.myReader.Read())
{
elmostafeedText.Text = conn.myReader["cheque_owner"].ToString();
cheeqeValue.Text = conn.myReader["cheque_value"].ToString();
dateTimePicker1.Text = conn.myReader["cheque_Esdar_Date"].ToString();
dateTimePicker2.Text = conn.myReader["cheque_Sarf_Date"].ToString();
photos_group.Add(int.Parse(conn.myReader["Group_id"].ToString()));
}
else
{
MessageBox.Show("لا يوجد بيانات شيك بهذا الرقم");
}
}
catch (Exception ex)
{
MessageBox.Show("خطأ ف البيانات المدخلة");
Logger.WriteLog("[" + DateTime.Now + "] ExceptionString: " + ex.ToString() + " InnerException: " + ex.InnerException + " ExceptionMessage: " + ex.Message + ". [" + this.Name + "] By [" + SessionInfo.empName + "]");
}
}
else
{
MessageBox.Show("برجاء اتمام البيانات");
}
}
private void button3_Click(object sender, EventArgs e)
{
String sql = "select * from Clients where national_id = '" + nationalIDMota.Text + "' and check_out='N' and TnazolState ='N' and stopState='N'";
c1.SQLCODE(sql, false);
if (c1.myReader.Read())
{
nameMota.Text = c1.myReader["client_name"].ToString();
motnazelID = int.Parse(c1.myReader["id"].ToString());
}
else
{
MessageBox.Show("لا يوجد عضو بهذا الرقم يمكنه التنازل");
}
}
private void LoadLastTrans()
{
conObj.startConnection();
conObj.SQLCODE("select max(group_id) as oldGroupId from transactions;", false);
while (conObj.myReader.Read())
{
groupID = int.Parse(conObj.myReader["oldGroupId"].ToString()) + 1;
}
}
private bool InsertNationalIdChecker(string text)
{
c1.SQLCODE("select * from clients where national_id='" + text + "'", false);
if (c1.myReader.Read())
{
return(true);
}
return(false);
}
private void LoadCustNames()
{
try
{
conn.startConnection();
String tmpSql = "SELECT client_name FROM Clients ORDER BY client_name";
conn.SQLCODE(tmpSql, false);
while (conn.myReader.Read())
{
cust_nameComb.Items.Add(conn.myReader[0].ToString());
}
}
catch (Exception ex)
{
MessageBox.Show("خطأ اثناء تحميل البيانات");
Logger.WriteLog("[" + DateTime.Now + "] ExceptionString: " + ex.ToString() + " InnerException: " + ex.InnerException + " ExceptionMessage: " + ex.Message + ". [" + this.Name + "] By [" + SessionInfo.empName + "]");
}
}
public void load()
{
String sql = "select group_id,cheque_number AS 'رقم الشيك' , cheque_owner AS 'المستفيد' , cheque_value AS 'قيمة الشيك ' , cheque_Esdar_Date AS 'تاريخ الاصدار ' ,cheque_Sarf_Date AS 'تاريخ الصرف' from cheques";
conn.SQLCODE(sql, true);
DataTable table = new DataTable();
conn.myAdabter.Fill(table);
dataGridView1.DataSource = table;
dataGridView1.Columns[0].Visible = false;
}
private void WarningLetterDetails_Load(object sender, EventArgs e)
{
Dbonj.startConnection();
Dbonj.SQLCODE("select max(LetterNumber) from WarningLetters", false);
if (Dbonj.myReader.Read())
{
lastLetterNumber.Text = Dbonj.myReader[0].ToString();
}
else
{
lastLetterNumber.Text = "0";
}
}
/// <summary>
///
/// </summary>
private void loadGroup()
{
conObj.startConnection();
String inList = string.Join(",", group_id);
if (!String.IsNullOrEmpty(inList))
{
String sql = "select * from transactions where group_id in (" + inList + ")";
conObj.SQLCODE(sql, false);
while (conObj.myReader.Read())
{
photosNames.Add(conObj.myReader["group_id"] + "-" + conObj.myReader["photo_id"]);
}
}
}
private void tryloadthis(String area)
{
bool done = false;
String sql = "select * from units where unit_type=" + area + "";
conn.SQLCODE(sql, false);
if (conn.myReader.Read())
{
done = true;
}
if (!done)
{
tryloadthis(area);
}
}
private void GetData(AutoCompleteStringCollection dataCollection)
{
DataSet ds = new DataSet();
String sql = "SELECT DISTINCT [employee_name] from [employees]";
con.SQLCODE(sql, true);
con.myAdabter.Fill(ds);
con.myAdabter.Dispose();
foreach (DataRow row in ds.Tables[0].Rows)
{
dataCollection.Add(row[0].ToString());
}
}
private void searchBtn_Click(object sender, EventArgs e)
{
conObj.startConnection();
String qury = "select id,client_name from Clients where national_id='" + nationalIdtext.Text + "' and montaseb='Y' and stopState='N'";
conObj.SQLCODE(qury, false);
if (conObj.myReader.Read())
{
custNameText.Text = conObj.myReader[1].ToString();
clientId = int.Parse(conObj.myReader[0].ToString());
}
else
{
MessageBox.Show(" برجاء مراجعة الرقم القومى للعميل لا يوجد عميل منتسب بهذا الرقم القومى");
}
}
private void LoadCheckOut()
{
if (unitTypes.SelectedIndex != -1)
{
try {
conObj.startConnection();
String sql = "SELECT Clients.client_name as 'العميل المنسحب' ,units.unit_type as 'نوع الوحدة' ,cheques.cheque_value as 'قيمة الشيك' , cheques.cheque_number as 'رقم الشيك', cheques.cheque_owner as 'مستفيد الشيك', cheques.cheque_Esdar_Date as 'تاريخ اصدار الشيك' FROM(((Clients INNER JOIN ClientsUnits ON Clients.ID = ClientsUnits.client_id) left JOIN cheques ON Clients.ID = cheques.client_id) INNER JOIN units ON ClientsUnits.unit_id = units.ID) WHERE(units.unit_type = " + int.Parse((unitTypes.SelectedItem as ComboBoxItem).Text) + " and ClientsUnits.RecoredState='C')";
conObj.SQLCODE(sql, true);
DataTable dtNotValid = new DataTable();
conObj.myAdabter.Fill(dtNotValid);
dataGridView1.DataSource = dtNotValid;
}
catch (Exception ex) {
MessageBox.Show("خطأ اثناء تحميل البيانات");
Logger.WriteLog("[" + DateTime.Now + "] ExceptionString: " + ex.ToString() + " InnerException: " + ex.InnerException + " ExceptionMessage: " + ex.Message + ". [" + this.Name + "] By [" + SessionInfo.empName + "]");
}
}
}
private void loginButton_Click(object sender, EventArgs e)
{
con.startConnection();
String sql = "select * from employees where user_name='" + userNameText.Text + "' and user_password='******'";
con.SQLCODE(sql, false);
if (con.myReader.Read())
{
SessionInfo.empName = con.myReader["employee_name"].ToString();
SessionInfo.userName = con.myReader["user_name"].ToString();
SessionInfo.password = con.myReader["user_password"].ToString();
SessionInfo.privlages = con.myReader["privlages"].ToString();
this.DialogResult = DialogResult.OK;
this.Close();
}
else
{
MessageBox.Show("رجاء مراجعه بيانات الدخول");
}
}
private void searchButton_Click(object sender, EventArgs e)
{
if (!String.IsNullOrEmpty(nationalIdtext.Text))
{
conn.startConnection();
String sql = "select * from Clients where national_id = '" + nationalIdtext.Text + "' and TnazolState <>'F' and check_out='N' and stopState='N'";
conn.SQLCODE(sql, false);
if (conn.myReader.Read())
{
agentNameText.Text = conn.myReader["client_name"].ToString();
ID = int.Parse(conn.myReader["ID"].ToString());
}
else
{
MessageBox.Show("لا يوجد بيانات عميل بهذا الرقم");
}
}
else
{
MessageBox.Show("برجاء اتمام البيانات");
}
}
public updateEmployee(String EmName)
{
InitializeComponent();
c.startConnection();
employeeName = EmName;
String sqlaya = "select*from employees where employee_name='" + employeeName + "'";
c.SQLCODE(sqlaya, false);
if (c.myReader.Read())
{
employeeNameText.Text = employeeName;
employeeNameText.Enabled = false;
userNameText.Text = c.myReader["user_name"].ToString();
passwordText.Text = c.myReader["user_password"].ToString();
priv = c.myReader["privlages"].ToString();
upPriv();
}
else
{
MessageBox.Show("لا توجد بيانات لهذا العميل");
this.Close();
}
}
private void searchBtn_Click(object sender, EventArgs e)
{
ClearFormValues();
try
{
if (!string.IsNullOrEmpty(letterNumberTxt.Text))
{
Dbonj.startConnection();
Dbonj.SQLCODE("select * from WarningLetters where LetterNumber=" + letterNumberTxt.Text + "", false);
if (Dbonj.myReader.Read())
{
subjectTxt.Text = Dbonj.myReader["LetterSubject"].ToString();
notesTxt.Text = Dbonj.myReader["LetterNotes"].ToString();
replyTxt.Text = Dbonj.myReader["LetterReply"].ToString();
LetterType.Text = Dbonj.myReader["LetterType"].ToString();
dateTimePicker1.Text = Dbonj.myReader["LetterDate"].ToString();
photosGroup_id = int.Parse(Dbonj.myReader["group_id"].ToString());
attchmentsBtn.Enabled = true;
saveUpdateBtn.Enabled = true;
deleteLetterBtn.Enabled = true;
}
else
{
MessageBox.Show("لا توجد بيانات بهذا الرقم");
}
}
else
{
MessageBox.Show("برجاء البحث برقم الخطاب");
}
}
catch (Exception ex) {
MessageBox.Show("خطأ ف البيانات المدخلة");
Logger.WriteLog("[" + DateTime.Now + "] ExceptionString: " + ex.ToString() + " InnerException: " + ex.InnerException + " ExceptionMessage: " + ex.Message + ". [" + this.Name + "] By [" + SessionInfo.empName + "]");
}
}
private void button1_Click(object sender, EventArgs e)
{
if (!String.IsNullOrEmpty(sheeqNumText.Text))
{
try
{
conn.startConnection();
double SheeqNum = double.Parse(sheeqNumText.Text);
String sql = "SELECT * FROM cheques WHERE cheque_number = " + SheeqNum + "";
conn.SQLCODE(sql, false);
if (conn.myReader.Read())
{
found = true;
sheequeOwner_text.Text = conn.myReader["cheque_owner"].ToString();
valueText.Text = conn.myReader["cheque_value"].ToString();
dateTimePickerEsdar.Text = conn.myReader["cheque_Esdar_Date"].ToString();
dateTimePicker1Sarf.Text = conn.myReader["cheque_Sarf_Date"].ToString();
ID = int.Parse(conn.myReader["ID"].ToString());
sheqNumUpdate.Text = sheeqNumText.Text;
}
else
{
MessageBox.Show("لا توجد بيانات بهذا الرقم");
}
}
catch (Exception ex)
{
MessageBox.Show("خطأ ف اتمام البيانات");
Logger.WriteLog("[" + DateTime.Now + "] ExceptionString: " + ex.ToString() + " InnerException: " + ex.InnerException + " ExceptionMessage: " + ex.Message + ". [" + this.Name + "] By [" + SessionInfo.empName + "]");
}
}
else
{
MessageBox.Show("برجاء اتمام البيانات");
}
}
private void button1_Click(object sender, EventArgs e)
{
#region Validation Region
//validation
{
if (string.IsNullOrWhiteSpace(clientName.Text))
{
nameErr = new ErrorProvider();
nameErr.SetError(clientName, "يجب إدخال إسم العميل ");
arr[0] = false;
}
else
{
// z=true;
arr[0] = true;
if (nameErr != null)
{
nameErr.Clear();
}
}
if (InsertNationalIdChecker(nationalId.Text) || string.IsNullOrWhiteSpace(nationalId.Text) || (nationalId.Text.Length != 14 && nationalId.Text.Length != 16))
{
nationalIdErr = new ErrorProvider();
nationalIdErr.SetError(nationalId, "خطأ متعلق بالرقم القومى متواجد قبل ذلك او ليس 14 رقم او 16 فى حاله اكتر من وحده");
arr[1] = false;
}
else
{
// z=true;
if (nationalIdErr != null)
{
nationalIdErr.Clear();
}
arr[1] = true;
}
if (string.IsNullOrWhiteSpace(membershipId.Text) && !entsabCheck.Checked)
{
membershipIdErr = new ErrorProvider();
membershipIdErr.SetError(membershipId, "يجب إدخال رقم العضوية ");
// z=false;
arr[2] = false;
}
else
{
//z=true;
if (membershipIdErr != null)
{
membershipIdErr.Clear();
}
arr[2] = true;
}
if (string.IsNullOrWhiteSpace(phoneNumber.Text))
{
phoneErr = new ErrorProvider();
phoneErr.SetError(phoneNumber, "يجب إدخال رقم ادخال رقم التليفون ");
//z=false;
arr[3] = false;
}
else
{
//z=true;
if (phoneErr != null)
{
phoneErr.Clear();
}
arr[3] = true;
}
if (string.IsNullOrWhiteSpace(address.Text))
{
addressErr = new ErrorProvider();
addressErr.SetError(address, "يجب إدخال العنوان");
//z=false;
arr[4] = false;
}
else
{
//z=true;
if (addressErr != null)
{
addressErr.Clear();
}
// unitTypeErr = null;
arr[4] = true;
}
if (string.IsNullOrWhiteSpace(unitType.Text))
{
unitTypeErr = new ErrorProvider();
unitTypeErr.SetError(unitType, "يجب إدخال نوع الوحدة");
//z=false;
arr[5] = false;
}
else
{
//z=true;
if (unitTypeErr != null)
{
unitTypeErr.Clear();
}
arr[5] = true;
}
if (entsabCheck.Checked)
{
if (string.IsNullOrWhiteSpace(intesabValue.Text))
{
entesabMoneyErr = new ErrorProvider();
entesabMoneyErr.SetError(intesabValue, "يجب إدخال قيمة المبلغ المدفوع للإنتساب ");
//z=false;
arr[7] = false;
}
else
{
//z=true;
if (entesabMoneyErr != null)
{
entesabMoneyErr.Clear();
}
arr[7] = true;
}
if (string.IsNullOrWhiteSpace(intesabAccount.Text))
{
entesabAccErr = new ErrorProvider();
entesabAccErr.SetError(intesabAccount, "يجب إدخال نوع الحساب ");
//z=false;
arr[9] = false;
}
else
{
//z=true;
if (entesabAccErr != null)
{
entesabAccErr.Clear();
}
arr[9] = true;
}
}
}
#endregion
if (checkfull(arr))
{
try
{
#region insert in Clients Table
if (entsabCheck.Checked)
{
membershipId.Text = "0";
}
String sqlInsert = "insert into Clients(membership_id,client_name,phone_number,address,national_id,deleted,montaseb,delivered,check_out,description,Group_id,TnazolState)"
+ "values(" + int.Parse(membershipId.Text) + ",'" + clientName.Text + "','" + phoneNumber.Text + "','" + address.Text + "','" + nationalId.Text + "','N','N','N','N','" + description.Text + "'," + photosGroup_id + ",'N')";
if (c1.SQLUPDATE(sqlInsert, false))
#endregion
{
#region return the inserted id
String subquery = "select * from Clients where national_id='" + nationalId.Text + "'";
c1.SQLCODE(subquery, false);
int clientId = 0;
while (c1.myReader.Read())
{
clientId = int.Parse(c1.myReader["ID"].ToString());
}
#endregion
#region insert in Clients Units Table
int unitId = int.Parse((unitType.SelectedItem as ComboBoxItem).value);
String sql2 = "insert into ClientsUnits(client_id,unit_id,RecoredState)values(" + clientId + "," + unitId + ",'N')";
c1.SQLUPDATE(sql2, false);
#endregion
if (int.Parse(moqademValue.Text) > int.Parse("0"))
{
#region insert Mo2dam data if not empty
String da = moqademDate.Value.ToString("yyyy/MM/dd");
String moqademSql = "insert into first_paids (client_id,paid_value,paid_date,bank_id) values (" + clientId + "," + int.Parse(moqademValue.Text) + ",#" + da + "#," + int.Parse((accountType.SelectedItem as ComboBoxItem).value) + ")";
c1.SQLUPDATE(moqademSql, false);
#endregion
}
if (entsabCheck.Checked)
{
#region insert Entsab Data if checked
String da1 = intesabDate.Value.ToString("yyyy/MM/dd");
String moqademSql = "insert into montsben (client_id,paid_value,paid_date,bank_id) values (" + clientId + "," + int.Parse(intesabValue.Text) + ",#" + da1 + "#," + int.Parse((intesabAccount.SelectedItem as ComboBoxItem).value) + ")";
c1.SQLUPDATE(moqademSql, false);
String updatesql = "update Clients set montaseb='Y',membership_id =0 where ID=" + clientId;
c1.SQLUPDATE(updatesql, false);
#endregion
}
#region Decreas Available Units
int avaiableunits = 0;
String unitsSql = "select available_units from units where unit_type=" + int.Parse(unitType.Text);
c1.SQLCODE(unitsSql, false);
if (c1.myReader.Read())
{
avaiableunits = int.Parse(c1.myReader["available_units"].ToString());
}
avaiableunits--;
String updateUnitsSql = "update units set available_units=" + avaiableunits + " where unit_type=" + int.Parse(unitType.Text);
c1.SQLUPDATE(updateUnitsSql, true);
if (avaiableunits < 0)
{
MessageBox.Show("تم الحجز لكن عدد الوحدات غير كاافي ");
}
clearFormData();
#endregion
}
}
catch (Exception ex)
{
MessageBox.Show("خطأ ف البيانات المدخلة");
Logger.WriteLog("[" + DateTime.Now + "] ExceptionString: " + ex.ToString() + " InnerException: " + ex.InnerException + " ExceptionMessage: " + ex.Message + ". [" + this.Name + "] By [" + SessionInfo.empName + "]");
}
#region Idk What is this
if (nameErr != null)
{
nameErr.Clear();
}
if (nationalIdErr != null)
{
nationalIdErr.Clear();
}
if (membershipIdErr != null)
{
membershipIdErr.Clear();
}
if (phoneErr != null)
{
phoneErr.Clear();
}
if (addressErr != null)
{
addressErr.Clear();
}
if (unitTypeErr != null)
{
unitTypeErr.Clear();
}
if (entesabMoneyErr != null)
{
entesabMoneyErr.Clear();
}
if (entesabAccErr != null)
{
entesabAccErr.Clear();
}
#endregion
}
else
{
MessageBox.Show("خطأ متعلق بالبيانات");
}
}
private void searchButton_Click(object sender, EventArgs e)
{
try
{
String sql = "select * from Clients where national_id = '" + nationalIdText.Text + "' and stopState='N'";
c1.SQLCODE(sql, false);
int sumOfMoney = 0;
char t = 'm';
int ut = 0;
if (c1.myReader.Read())
{
nameText.Text = c1.myReader["client_name"].ToString();
clientId = int.Parse(c1.myReader["ID"].ToString());
t = char.Parse(c1.myReader["montaseb"].ToString());
//////
String subquery = "select unit_id from ClientsUnits where client_id=" + clientId + " and RecoredState='N'";
c1.SQLCODE(subquery, false);
if (c1.myReader.Read())
{
ut = int.Parse(c1.myReader["unit_id"].ToString());
currentUnitState.Text = AreaOfUnitID(ut) + "";
}
//moqdam
String sqlmoqdm = "select SUM(paid_value) as moqdm from first_paids where client_id=" + clientId;
c1.SQLCODE(sqlmoqdm, false);
if (c1.myReader.Read())
{
if (!String.IsNullOrEmpty(c1.myReader["moqdm"].ToString()))
{
sumOfMoney += int.Parse(c1.myReader["moqdm"].ToString());
}
}
sqlmoqdm = "select SUM(qest_value) as aqsat from aqsat where client_id=" + clientId;
c1.SQLCODE(sqlmoqdm, false);
if (c1.myReader.Read())
{
if (!String.IsNullOrEmpty(c1.myReader["aqsat"].ToString()))
{
sumOfMoney += int.Parse(c1.myReader["aqsat"].ToString());
}
}
//montas
if (t == 'Y')
{
sqlmoqdm = "select SUM(paid_value) as mon from montsben where client_id=" + clientId;
c1.SQLCODE(sqlmoqdm, false);
if (c1.myReader.Read())
{
if (!String.IsNullOrEmpty(c1.myReader["mon"].ToString()))
{
sumOfMoney += int.Parse(c1.myReader["mon"].ToString());
}
}
}
thePaidMoney.Text = sumOfMoney + "";
}
else
{
MessageBox.Show("لا يوجد عميل يمكنة التحويل بهذا الرقم القومي");
}
}
catch (Exception ex)
{
MessageBox.Show("خطأ ف البيانات المدخلة");
Logger.WriteLog("[" + DateTime.Now + "] ExceptionString: " + ex.ToString() + " InnerException: " + ex.InnerException + " ExceptionMessage: " + ex.Message + ". [" + this.Name + "] By [" + SessionInfo.empName + "]");
}
}
private void FillReportBtn_Click(object sender, EventArgs e)
{
warningLettersBtn.Text = "خطابات و انذارات [0]";
if (!String.IsNullOrEmpty(nationalSearch.Text))
{
stoppingGroupbox.Visible = false;
try
{
conObj.startConnection();
String sql = "SELECT Clients.stopReason,Clients.stopDate,Clients.stopState,units.unit_price,units.unit_type, Clients.client_name,Clients.receivingPayments, ClientsUnits.ID as joinid, Clients.membership_id, Clients.phone_number, Clients.address, Clients.deleted, Clients.national_id, Clients.montaseb, Clients.delivered,Clients.ID, Clients.unit_number, Clients.tour_number, Clients.check_out, Clients.description, Clients.TnazolState FROM ((Clients INNER JOIN ClientsUnits ON Clients.ID = ClientsUnits.client_id) INNER JOIN units ON ClientsUnits.unit_id = units.ID) WHERE(Clients.national_id = '" + nationalSearch.Text + "' and check_out = 'N') ORDER BY ClientsUnits.ID DESC";
conObj.SQLCODE(sql, false);
if (conObj.myReader.Read())
{
#region Load Client Data
#region Display Basic Info About the Client
client_id = int.Parse(conObj.myReader["id"].ToString());
client_NameTxt.Text = conObj.myReader["client_name"].ToString();
client_PhoneTxt.Text = conObj.myReader["phone_number"].ToString();
client_NationalTxt.Text = conObj.myReader["national_id"].ToString();
if (conObj.myReader["stopState"].ToString() == "Y")
{
stoppingGroupbox.Visible = true;
stoppingDateLab.Text = conObj.myReader["stopDate"].ToString();
stopReasonTxt.Text = conObj.myReader["stopReason"].ToString();
}
if (conObj.myReader["membership_id"].ToString() == "0")
{
client_MemberShipTxt.Text = "";
}
else
{
client_MemberShipTxt.Text = conObj.myReader["membership_id"].ToString();
}
client_AddressTxt.Text = conObj.myReader["address"].ToString();
client_TourNumTxt.Text = conObj.myReader["tour_number"].ToString();
client_UnitNumTxt.Text = conObj.myReader["unit_number"].ToString();
recivePaymentValueTxt.Text = "0";
recivePaymentValueTxt.Text = conObj.myReader["receivingPayments"].ToString();
unitPriceTxt.Text = conObj.myReader["unit_price"].ToString();
unitTypeLab.Text = conObj.myReader["unit_type"].ToString();
#endregion
#region check Entsab State and Sum of It`s Money
TnazolState = conObj.myReader["TnazolState"].ToString();
if (conObj.myReader["montaseb"].ToString() == "Y")
{
entsabCheckBox.Checked = true;
EntsabGroup.Visible = true;
sql = "select sum(paid_value) from montsben where client_id=" + client_id + " and PaymentType=0";
LoadOneCell(sql, entsabPaid_ValueTxt);
if (String.IsNullOrEmpty(entsabPaid_ValueTxt.Text))
{
entsabPaid_ValueTxt.Text = "0";
}
sql = "select sum(paid_value) from montsben where client_id=" + client_id + " and PaymentType=1";
LoadOneCell(sql, entsabManagPaid_ValueTxt);
if (String.IsNullOrEmpty(entsabManagPaid_ValueTxt.Text))
{
entsabManagPaid_ValueTxt.Text = "0";
}
}
else
{
EntsabGroup.Visible = false;
}
#endregion
#region check Tnazol State and Partner
if (TnazolState != "N")
{
TnazolGroup.Visible = true;
if (TnazolState == "T")
{
sql = "SELECT client_name,ID FROM Clients WHERE(ID =(SELECT From_id FROM tanazolat WHERE(to_id = " + client_id + ")))";
motnazelCheckBox.Checked = false;
motnazelToCheckBox.Checked = true;
conObj.SQLCODE(sql, false);
if (conObj.myReader.Read())
{
tnazolPartenterTxt.Text = conObj.myReader[0].ToString();
partnerID = int.Parse(conObj.myReader[1].ToString());
}
if (partnerID != -1)
{
sql = "SELECT receivingPayments FROM Clients WHERE(ID =" + partnerID + ")";
conObj.SQLCODE(sql, false);
if (conObj.myReader.Read())
{
int oldre = int.Parse(conObj.myReader[0].ToString());
recivePaymentValueTxt.Text = (int.Parse(recivePaymentValueTxt.Text) + oldre).ToString();
}
}
}
if (TnazolState == "F")
{
sql = "SELECT client_name FROM Clients WHERE(ID =(SELECT to_id FROM tanazolat WHERE(from_id = " + client_id + ")))";
motnazelCheckBox.Checked = true;
motnazelToCheckBox.Checked = false;
LoadOneCell(sql, tnazolPartenterTxt);
}
}
else
{
TnazolGroup.Visible = false;
}
#endregion
#region Load Paid Fist Values Sum
sql = "select sum(paid_value) from first_paids HAVING client_id=" + client_id + " OR client_id=" + partnerID + "";
LoadOneCell(sql, client_Firstpaid_SumTxt);
if (String.IsNullOrEmpty(client_Firstpaid_SumTxt.Text))
{
client_Firstpaid_SumTxt.Text = "0";
}
#endregion
#region Load T5ses Money sum
sql = "select sum(paid_value) from T5sesMoney HAVING client_id=" + client_id + " OR client_id=" + partnerID + "";
LoadOneCell(sql, client_T5ses_SumTxt);
if (String.IsNullOrEmpty(client_T5ses_SumTxt.Text))
{
client_T5ses_SumTxt.Text = "0";
}
#endregion
#region Load Aqsat Total Sum
sql = "select sum(qest_value) from aqsat HAVING client_id=" + client_id + " OR client_id=" + partnerID + "";
LoadOneCell(sql, client_Aqsat_SumTxt);
if (String.IsNullOrEmpty(client_Aqsat_SumTxt.Text))
{
client_Aqsat_SumTxt.Text = "0";
}
#endregion
#region Sum All data and Display them
int tmp = int.Parse(client_Firstpaid_SumTxt.Text);
tmp += int.Parse(client_T5ses_SumTxt.Text);
tmp += int.Parse(client_Aqsat_SumTxt.Text);
tmp += int.Parse(recivePaymentValueTxt.Text);
client_Total_SumTxt.Text = tmp.ToString();
unitRestPriceTxt.Text = (int.Parse(unitPriceTxt.Text) - tmp).ToString();
thePercentValueTxt.Text = (0.05 * int.Parse(unitPriceTxt.Text)).ToString();
#endregion
sql = "SELECT count(*) FROM WarningLetters where LetterClient_ID =" + client_id + "";
conObj.SQLCODE(sql, false);
if (conObj.myReader.Read())
{
warningLettersBtn.Text = "[" + conObj.myReader[0].ToString() + "]" + "خطابات الانذار";
}
#endregion
}
else
{
MessageBox.Show("لا يوجد بيانات لهذا العميل");
}
}
catch (Exception ex)
{
MessageBox.Show("خطأ فى بيانات العميل");
Logger.WriteLog("[" + DateTime.Now + "] ExceptionString: " + ex.ToString() + " InnerException: " + ex.InnerException + " ExceptionMessage: " + ex.Message + ". [" + this.Name + "] By [" + SessionInfo.empName + "]");
}
}
}
