public bool QueryCompanyFaxes() { String sqlQueryPart1 = "select company_fax.fax from erp_system.dbo.company_fax where company_fax.branch_serial = "; String sqlQueryPart2 = ";"; sqlQuery = String.Empty; sqlQuery += sqlQueryPart1; sqlQuery += addressSerial; sqlQuery += sqlQueryPart2; BASIC_STRUCTS.SQL_COLUMN_COUNT_STRUCT queryColumns = new BASIC_STRUCTS.SQL_COLUMN_COUNT_STRUCT(); queryColumns.sql_string = 1; if (!initializationObject.GetRows(sqlQuery, queryColumns, BASIC_MACROS.SEVERITY_LOW)) { return(false); } for (int i = 0; i < initializationObject.rows.Count; i++) { companyFaxes.Add(initializationObject.rows[0].sql_string[i]); } return(true); }
public bool GetNewContactId() { String sqlQueryPart1 = @"select max(contact_person_info.contact_id) from erp_system.dbo.contact_person_info where contact_person_info.sales_person_id = "; String sqlQueryPart2 = @" and contact_person_info.branch_serial = "; String sqlQueryPart3 = @";"; sqlQuery = String.Empty; sqlQuery += sqlQueryPart1; sqlQuery += GetSalesPersonId(); sqlQuery += sqlQueryPart2; sqlQuery += GetAddressSerial(); sqlQuery += sqlQueryPart3; BASIC_STRUCTS.SQL_COLUMN_COUNT_STRUCT queryColumns = new BASIC_STRUCTS.SQL_COLUMN_COUNT_STRUCT(); queryColumns.sql_int = 1; if (!initializationObject.GetRows(sqlQuery, queryColumns, BASIC_MACROS.SEVERITY_HIGH)) { return(false); } contactId = initializationObject.rows[0].sql_int[0] + 1; return(true); }
////////////////////////////////////////////////////////////////////// //QUERY FUNCTIONS ////////////////////////////////////////////////////////////////////// public bool GetCompanyAddress() { String sqlQueryPart1 = @"with get_countries_and_states as(select countries.id as country_id, states_governorates.id as state_id, countries.country, states_governorates.state_governorate from erp_system.dbo.states_governorates inner join erp_system.dbo.countries on states_governorates.country = countries.id), get_cities_and_districts as(select cities.state_governorate as state_id, cities.id as city_id, districts.id as district_id, cities.city, districts.district from erp_system.dbo.cities inner join erp_system.dbo.districts on cities.id = districts.city) select get_cities_and_districts.district, get_cities_and_districts.city, get_countries_and_states.state_governorate, get_countries_and_states.country from get_cities_and_districts inner join get_countries_and_states on get_cities_and_districts.state_id = get_countries_and_states.state_id inner join erp_system.dbo.company_address on get_cities_and_districts.district_id = company_address.address where company_address.company_serial = "; String sqlQueryPart2 = @" and get_cities_and_districts.district_id = "; String sqlQueryPart3 = @";"; sqlQuery = String.Empty; sqlQuery += sqlQueryPart1; sqlQuery += companySerial; sqlQuery += sqlQueryPart2; sqlQuery += districtId; sqlQuery += sqlQueryPart3; BASIC_STRUCTS.SQL_COLUMN_COUNT_STRUCT queryColumns = new BASIC_STRUCTS.SQL_COLUMN_COUNT_STRUCT(); queryColumns.sql_string = 4; if (!initializationObject.GetRows(sqlQuery, queryColumns, BASIC_MACROS.SEVERITY_HIGH)) { return(false); } country = initializationObject.rows[3].sql_string[0]; state = initializationObject.rows[2].sql_string[0]; city = initializationObject.rows[1].sql_string[0]; district = initializationObject.rows[0].sql_string[0]; return(true); }
public bool InitializeCompanyInfo(int mCompanySerial) { companySerial = mCompanySerial; String sqlQueryPart1 = @"select generic_work_fields.id, specific_work_fields.id, employees_info.employee_id, company_name.company_name, generic_work_fields.generic_work_field, specific_work_fields.specific_work_field, employees_info.name from erp_system.dbo.company_name inner join erp_system.dbo.employees_info on company_name.added_by = employees_info.employee_id inner join erp_system.dbo.company_field_of_work on company_name.company_serial = company_field_of_work.company_serial inner join erp_system.dbo.specific_work_fields on company_field_of_work.work_field = specific_work_fields.id inner join erp_system.dbo.generic_work_fields on specific_work_fields.generic_work_field = generic_work_fields.id where company_name.company_serial = " ; String sqlQueryPart2 = ";"; sqlQuery = String.Empty; sqlQuery += sqlQueryPart1; sqlQuery += companySerial; sqlQuery += sqlQueryPart2; BASIC_STRUCTS.SQL_COLUMN_COUNT_STRUCT queryColumns = new BASIC_STRUCTS.SQL_COLUMN_COUNT_STRUCT(); queryColumns.sql_int = 3; queryColumns.sql_string = 4; if (!initializationObject.GetRows(sqlQuery, queryColumns, BASIC_MACROS.SEVERITY_HIGH)) { return(false); } primaryFieldId = initializationObject.rows[0].sql_int[0]; secondaryFieldId = initializationObject.rows[1].sql_int[0]; ownerUserId = initializationObject.rows[2].sql_int[0]; companyName = initializationObject.rows[0].sql_string[0]; primaryField = initializationObject.rows[1].sql_string[0]; secondaryField = initializationObject.rows[2].sql_string[0]; ownerUser = initializationObject.rows[3].sql_string[0]; addressKnown = false; return(true); }
public void InitializeColumns(BASIC_STRUCTS.SQL_COLUMN_COUNT_STRUCT columnCount) { currentRow = new BASIC_STRUCTS.SQL_ROW_STRUCT(); currentRow.sql_tinyint = new List <Byte>(); currentRow.sql_smallint = new List <Int16>(); currentRow.sql_int = new List <Int32>(); currentRow.sql_bigint = new List <Int64>(); currentRow.sql_money = new List <Decimal>(); currentRow.sql_decimal = new List <Decimal>(); currentRow.sql_datetime = new List <DateTime>(); currentRow.sql_string = new List <String>(); currentRow.sql_bit = new List <Boolean>(); }
public bool GetNewAddressSerial() { String sqlQueryPart1 = @"select max(address_serial) from erp_system.dbo.company_address;"; sqlQuery = String.Empty;; sqlQuery += sqlQueryPart1; BASIC_STRUCTS.SQL_COLUMN_COUNT_STRUCT queryColumns = new BASIC_STRUCTS.SQL_COLUMN_COUNT_STRUCT(); queryColumns.sql_int = 1; if (!initializationObject.GetRows(sqlQuery, queryColumns, BASIC_MACROS.SEVERITY_HIGH)) { return(false); } SetAddressSerial(initializationObject.rows[0].sql_int[0] + 1); return(true); }
public bool QueryContactComment(int salesPersonId, int branchSerial, int contactId) { String sqlQueryPart1 = @"select contact_person_comment.date_added, contact_person_comment.comment_on_contact from erp_system.dbo.contact_person_comment where contact_person_comment.contact_id = "; String sqlQueryPart2 = @" and contact_person_comment.sales_person_id = "; String sqlQueryPart3 = @" and contact_person_comment.branch_serial = "; String sqlQueryPart4 = @" order by contact_person_comment.date_added DESC;"; sqlQuery = String.Empty; sqlQuery += sqlQueryPart1; sqlQuery += salesPersonId; sqlQuery += sqlQueryPart2; sqlQuery += branchSerial; sqlQuery += sqlQueryPart3; sqlQuery += contactId; sqlQuery += sqlQueryPart4; BASIC_STRUCTS.SQL_COLUMN_COUNT_STRUCT queryColumns = new BASIC_STRUCTS.SQL_COLUMN_COUNT_STRUCT(); queryColumns.sql_datetime = 1; queryColumns.sql_string = 1; if (!initializationObject.GetRows(sqlQuery, queryColumns, BASIC_MACROS.SEVERITY_LOW)) { return(false); } for (int i = 0; i < initializationObject.rows.Count; i++) { COMPANY_ORGANISATION_MACROS.CONTACT_COMMENT_STRUCT tempItem; tempItem.commentDate = initializationObject.rows[i].sql_datetime[0].ToString(); tempItem.comment = initializationObject.rows[i].sql_string[0]; commentsList.Add(tempItem); } return(true); }
public bool InitializeBranchInfo(int mAddressSerial) { addressSerial = mAddressSerial; String sqlQueryPart1 = @"select company_address.company_serial, company_address.address, generic_work_fields.id, specific_work_fields.id, employees_info.employee_id, company_name.company_name, generic_work_fields.generic_work_field, specific_work_fields.specific_work_field, employees_info.name from erp_system.dbo.company_address inner join erp_system.dbo.company_name on company_address.company_serial = company_name.company_serial inner join erp_system.dbo.employees_info on company_name.added_by = employees_info.employee_id inner join erp_system.dbo.company_field_of_work on company_address.company_serial = company_field_of_work.company_serial inner join erp_system.dbo.specific_work_fields on company_field_of_work.work_field = specific_work_fields.id inner join erp_system.dbo.generic_work_fields on specific_work_fields.generic_work_field = generic_work_fields.id where company_address.address_serial = " ; String sqlQueryPart2 = ";"; sqlQuery = String.Empty; sqlQuery += sqlQueryPart1; sqlQuery += addressSerial; sqlQuery += sqlQueryPart2; BASIC_STRUCTS.SQL_COLUMN_COUNT_STRUCT queryColumns = new BASIC_STRUCTS.SQL_COLUMN_COUNT_STRUCT(); queryColumns.sql_int = 5; queryColumns.sql_string = 4; if (!initializationObject.GetRows(sqlQuery, queryColumns, BASIC_MACROS.SEVERITY_LOW)) { return(false); } companySerial = initializationObject.rows[0].sql_int[0]; address = initializationObject.rows[0].sql_int[1]; primaryFieldId = initializationObject.rows[0].sql_int[2]; secondaryFieldId = initializationObject.rows[0].sql_int[3]; ownerUserId = initializationObject.rows[0].sql_int[4]; companyName = initializationObject.rows[0].sql_string[0]; primaryField = initializationObject.rows[0].sql_string[1]; secondaryField = initializationObject.rows[0].sql_string[2]; ownerUser = initializationObject.rows[0].sql_string[3]; SetCompanyAddressIDs(); if (!GetCompanyAddress() && !QueryCompanyPhones() && !QueryCompanyFaxes()) { return(false); } addressKnown = true; return(true); }
public bool GetRows(String sqlQuery, BASIC_STRUCTS.SQL_COLUMN_COUNT_STRUCT columnCount) { try { rows = new List <BASIC_STRUCTS.SQL_ROW_STRUCT>(); sqlConnection = new SqlConnection(sqlConnectionString); sqlConnection.Open(); sqlCommand = new SqlCommand(sqlQuery, sqlConnection); sqlReader = sqlCommand.ExecuteReader(); while (sqlReader.Read()) { currentColumn = 0; InitializeColumns(columnCount); for (int i = 0; i < columnCount.sql_int; i++) { if (sqlReader[currentColumn] == DBNull.Value) { currentRow.sql_int.Add(0); } else { currentRow.sql_int.Add((Int32)sqlReader[currentColumn]); } currentColumn++; } for (int i = 0; i < columnCount.sql_tinyint; i++) { if (sqlReader[currentColumn] == DBNull.Value) { currentRow.sql_tinyint.Add(0); } else { currentRow.sql_tinyint.Add((Byte)sqlReader[currentColumn]); } currentColumn++; } for (int i = 0; i < columnCount.sql_smallint; i++) { if (sqlReader[currentColumn] == DBNull.Value) { currentRow.sql_smallint.Add(0); } else { currentRow.sql_smallint.Add((Int16)sqlReader[currentColumn]); } currentColumn++; } for (int i = 0; i < columnCount.sql_bigint; i++) { if (sqlReader[currentColumn] == DBNull.Value) { currentRow.sql_bigint.Add(0); } else { currentRow.sql_bigint.Add((Int64)sqlReader[currentColumn]); } currentColumn++; } for (int i = 0; i < columnCount.sql_money; i++) { if (sqlReader[currentColumn] == DBNull.Value) { currentRow.sql_money.Add(0); } else { currentRow.sql_money.Add((Decimal)sqlReader[currentColumn]); } currentColumn++; } for (int i = 0; i < columnCount.sql_decimal; i++) { if (sqlReader[currentColumn] == DBNull.Value) { currentRow.sql_decimal.Add(0); } else { currentRow.sql_decimal.Add((Decimal)sqlReader[currentColumn]); } currentColumn++; } for (int i = 0; i < columnCount.sql_datetime; i++) { DateTime nullDatetime = new DateTime(1900, 1, 1); if (sqlReader[currentColumn] == DBNull.Value) { currentRow.sql_datetime.Add(nullDatetime); } else { currentRow.sql_datetime.Add((DateTime)sqlReader[currentColumn]); } currentColumn++; } for (int i = 0; i < columnCount.sql_string; i++) { if (sqlReader[currentColumn] == DBNull.Value) { currentRow.sql_string.Add(string.Empty); } else { currentRow.sql_string.Add((String)sqlReader[currentColumn]); } currentColumn++; } for (int i = 0; i < columnCount.sql_bit; i++) { if (sqlReader[currentColumn] == DBNull.Value) { currentRow.sql_bit.Add(false); } else { currentRow.sql_bit.Add((Boolean)sqlReader[currentColumn]); } currentColumn++; } rows.Add(currentRow); } sqlReader.Close(); } catch (Exception sqlException) { MessageBox.Show("Server connection failed! Please check your internet connection and try again.", "Error", MessageBoxButton.OK, MessageBoxImage.Error); sqlConnection.Close(); return(false); } finally { sqlConnection.Close(); } return(true); }
public bool InitializeContactInfo(int mContactId) { contactId = mContactId; String sqlQueryPart1 = @"select contact_person_mobile.telephone_id, contact_person_personal_emails.email_id, contact_person_info.department, contact_person_info.name, contact_person_info.gender, contact_person_info.email, departments_type.department, contact_person_mobile.mobile, contact_person_personal_emails.personal_email from erp_system.dbo.contact_person_info inner join erp_system.dbo.departments_type on contact_person_info.department = departments_type.id left join erp_system.dbo.contact_person_mobile on contact_person_info.sales_person_id = contact_person_mobile.sales_person_id and contact_person_info.branch_serial = contact_person_mobile.branch_serial and contact_person_info.contact_id = contact_person_mobile.contact_id left join erp_system.dbo.contact_person_personal_emails on contact_person_info.sales_person_id = contact_person_personal_emails.sales_person_id and contact_person_info.branch_serial = contact_person_personal_emails.branch_serial and contact_person_info.contact_id = contact_person_personal_emails.contact_id where contact_person_info.sales_person_id = " ; String sqlQueryPart2 = " and contact_person_info.branch_serial = "; String sqlQueryPart3 = " and contact_person_info.contact_id = "; String sqlQueryPart4 = " order by contact_person_mobile.telephone_id, contact_person_personal_emails.email_id;"; sqlQuery = String.Empty; sqlQuery += sqlQueryPart1; sqlQuery += salesPerson.GetEmployeeId(); sqlQuery += sqlQueryPart2; sqlQuery += GetAddressSerial(); sqlQuery += sqlQueryPart3; sqlQuery += contactId; sqlQuery += sqlQueryPart4; BASIC_STRUCTS.SQL_COLUMN_COUNT_STRUCT queryColumns = new BASIC_STRUCTS.SQL_COLUMN_COUNT_STRUCT(); queryColumns.sql_int = 3; queryColumns.sql_string = 6; if (!initializationObject.GetRows(sqlQuery, queryColumns, BASIC_MACROS.SEVERITY_HIGH)) { return(false); } contactName = initializationObject.rows[0].sql_string[0]; gender = initializationObject.rows[0].sql_string[1]; businessEmail = initializationObject.rows[0].sql_string[2]; department = initializationObject.rows[0].sql_string[3]; departmentId = initializationObject.rows[0].sql_int[2]; for (int i = 0; i < initializationObject.rows.Count; i++) { numberOfSavedPhones = initializationObject.rows[i].sql_int[0]; if (numberOfSavedPhones > 0) { contactPhones[numberOfSavedPhones - 1] = initializationObject.rows[i].sql_string[4]; } } for (int i = 0; i < initializationObject.rows.Count; i++) { numberOfSavedEmails = initializationObject.rows[i].sql_int[1]; if (numberOfSavedEmails > 0) { contactPersonalEmails[numberOfSavedEmails - 1] = initializationObject.rows[i].sql_string[5]; } } QueryContactComment(GetSalesPersonId(), GetAddressSerial(), GetContactId()); return(true); }
public bool InitializeEmployeeInfo(String mBusinessEmail) { businessEmail = mBusinessEmail; String sqlQueryPart1 = @"select employees_info.employee_id, employees_info.employee_department, employees_info.employee_team, employees_info.employee_position, employees_educational_qualifications.graduation_year, employees_educational_qualifications.certificate, employees_educational_qualifications.major, banks_names.id, employees_payroll_info.payroll_id, employees_payroll_info.account_id, employees_salaries.salary, employees_info.birth_date, employees_info.join_date, employees_info.name, employees_info.gender, departments_type.department, teams_type.team, positions_type.position, employees_personal_emails.email, employees_business_phones.phone, employees_personal_phones.phone, educational_degrees.educational_degree, educational_majors.educational_major, employees_initials.employee_initial, banks_names.bank_name from erp_system.dbo.employees_info inner join erp_system.dbo.departments_type on employees_info.employee_department = departments_type.id inner join erp_system.dbo.teams_type on employees_info.employee_team = teams_type.id inner join erp_system.dbo.positions_type on employees_info.employee_position = positions_type.id inner join erp_system.dbo.employees_business_emails on employees_info.employee_id = employees_business_emails.id left join erp_system.dbo.employees_personal_emails on employees_info.employee_id = employees_personal_emails.id left join erp_system.dbo.employees_business_phones on employees_info.employee_id = employees_business_phones.id left join erp_system.dbo.employees_personal_phones on employees_info.employee_id = employees_personal_phones.id left join erp_system.dbo.employees_initials on employees_info.employee_id = employees_initials.id left join erp_system.dbo.employees_educational_qualifications on employees_info.employee_id = employees_educational_qualifications.employee_id left join erp_system.dbo.educational_degrees on employees_educational_qualifications.certificate = educational_degrees.id left join erp_system.dbo.educational_majors on employees_educational_qualifications.major = educational_majors.id left join erp_system.dbo.employees_salaries on employees_info.employee_id = employees_salaries.id left join erp_system.dbo.employees_payroll_info on employees_info.employee_id = employees_payroll_info.employee_id left join erp_system.dbo.banks_names on employees_payroll_info.bank_id = banks_names.id where employees_business_emails.email = '" ; String sqlQueryPart2 = "';"; sqlQuery = string.Empty; sqlQuery += sqlQueryPart1; sqlQuery += businessEmail; sqlQuery += sqlQueryPart2; BASIC_STRUCTS.SQL_COLUMN_COUNT_STRUCT queryColumns = new BASIC_STRUCTS.SQL_COLUMN_COUNT_STRUCT(); queryColumns.sql_int = 9; queryColumns.sql_bigint = 1; queryColumns.sql_money = 1; queryColumns.sql_datetime = 2; queryColumns.sql_string = 12; if (!initializationObject.GetRows(sqlQuery, queryColumns, BASIC_MACROS.SEVERITY_HIGH)) { return(false); } employeeId = initializationObject.rows[0].sql_int[0]; departmentId = initializationObject.rows[0].sql_int[1]; teamId = initializationObject.rows[0].sql_int[2]; positionId = initializationObject.rows[0].sql_int[3]; graduationYear = initializationObject.rows[0].sql_int[4]; educationalQualificationId = initializationObject.rows[0].sql_int[5]; majorId = initializationObject.rows[0].sql_int[6]; salary = initializationObject.rows[0].sql_money[0]; birthDateStruct = initializationObject.rows[0].sql_datetime[0]; joinDateStruct = initializationObject.rows[0].sql_datetime[1]; joinDate = joinDateStruct.ToString(); birthDate = birthDateStruct.ToString(); name = initializationObject.rows[0].sql_string[0]; gender = initializationObject.rows[0].sql_string[1]; department = initializationObject.rows[0].sql_string[2]; team = initializationObject.rows[0].sql_string[3]; position = initializationObject.rows[0].sql_string[4]; personalEmail = initializationObject.rows[0].sql_string[5]; businessPhone = initializationObject.rows[0].sql_string[6]; personalPhone = initializationObject.rows[0].sql_string[7]; educationalQualification = initializationObject.rows[0].sql_string[8]; major = initializationObject.rows[0].sql_string[9]; initials = initializationObject.rows[0].sql_string[10]; payrollInfo = new List <COMPANY_ORGANISATION_MACROS.BANK_STRUCT>(); for (int i = 0; i < initializationObject.rows.Count; i++) { COMPANY_ORGANISATION_MACROS.BANK_STRUCT tempItem = new COMPANY_ORGANISATION_MACROS.BANK_STRUCT(); tempItem.bank_id = initializationObject.rows[i].sql_int[7]; tempItem.payroll_id = initializationObject.rows[i].sql_int[8]; tempItem.account_id = (ulong)initializationObject.rows[i].sql_bigint[0]; tempItem.bank_name = initializationObject.rows[i].sql_string[11]; payrollInfo.Add(tempItem); } return(true); }