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);
}
