public void Init()
{
cbCompanies.Items.AddRange(Companies.GetCompaniesComboboxItemsList(companiesList).ToArray <ComboboxItem>());
cbAuditTypes.Items.AddRange(AuditTypes.GetAuditTypesComboboxItemsList(auditTypesList).ToArray <ComboboxItem>());
//List<Users> auditorsList = usersList;
//List<Users> auditorsList = new List<Users>();
cbAuditor1.Items.AddRange(Users.GetUsersComboboxItemsList(usersList).ToArray <ComboboxItem>());
cbAuditor2.Items.AddRange(Users.GetUsersComboboxItemsList(usersList).ToArray <ComboboxItem>());
cbSupervisor.Items.AddRange(Users.GetUsersComboboxItemsList(usersList).ToArray <ComboboxItem>());
cbRating.Items.AddRange(AuditRating.GetAuditRatingComboboxItemsList(auditRatingList).ToArray <ComboboxItem>());
}
public List <AuditRev> SelectAuditRev(int Id)
{
List <AuditRev> ret = new List <AuditRev>();
SqlConnection sqlConn = new SqlConnection(SqlDBInfo.connectionString);
string SelectSt = "SELECT 0 as [Id], [Id] as [AuditId], [Year], [CompanyId], [AuditTypeId], " +
"CONVERT(varchar(500), DECRYPTBYPASSPHRASE( @passPhrase , [Title])) as Title, " +
"[ReportDt], " +
"[Auditor1Id], [Auditor2Id], [SupervisorId], " +
"[IsCompleted], [AuditNumber], [IASentNumber],[InsUserId],[UpdUserId],[InsDt], [UpdDt], [RevNo], " +
"(SELECT count(*) FROM [dbo].[Audit_Attachments] T WHERE a.id = T.AuditID and A.RevNo = T.RevNo) as AttCnt, " +
"[AuditRatingId], isnull([IsDeleted], 'FALSE') as IsDeleted " +
"FROM [dbo].[Audit] A " +
"WHERE Id = " + Id.ToString() +
" UNION ALL " +
"SELECT R.[Id], R.[AuditId], R.[Year], R.[CompanyId], R.[AuditTypeId], " +
"CONVERT(varchar(500), DECRYPTBYPASSPHRASE( @passPhrase , R.[Title])) as Title, " +
"R.[ReportDt], " +
"R.[Auditor1Id], R.[Auditor2Id], R.[SupervisorId], " +
"R.[IsCompleted], R.[AuditNumber], R.[IASentNumber], R.[InsUserId], R.[UpdUserId], R.[InsDt], R.[UpdDt], R.[RevNo], " +
"(SELECT count(*) FROM [dbo].[Audit_Attachments] T WHERE R.AuditId = T.AuditID and R.RevNo = T.RevNo) as AttCnt, " +
"R.[AuditRatingId], 'FALSE' as IsDeleted " +
"FROM [dbo].[AuditRev] R " +
"WHERE R.AuditId = " + Id.ToString() +
" ORDER BY RevNo DESC";
SqlCommand cmd = new SqlCommand(SelectSt, sqlConn);
try
{
sqlConn.Open();
cmd.Parameters.AddWithValue("@passPhrase", SqlDBInfo.passPhrase);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
int? Auditor2_Id, Supervisor_Id;
int? AuditRating_Id;
AuditRating AuditRating_rating;
Users Auditor2_User, Supervisor_User;
if (reader["Auditor2Id"] == System.DBNull.Value)
{
Auditor2_Id = null;
Auditor2_User = new Users();
}
else
{
Auditor2_Id = Convert.ToInt32(reader["Auditor2Id"].ToString());
Auditor2_User = new Users(Convert.ToInt32(reader["Auditor2Id"].ToString()));
}
if (reader["SupervisorId"] == System.DBNull.Value)
{
Supervisor_Id = null;
Supervisor_User = new Users();
}
else
{
Supervisor_Id = Convert.ToInt32(reader["SupervisorId"].ToString());
Supervisor_User = new Users(Convert.ToInt32(reader["SupervisorId"].ToString()));
}
if (reader["AuditRatingId"] == System.DBNull.Value)
{
AuditRating_Id = null;
AuditRating_rating = new AuditRating();
}
else
{
AuditRating_Id = Convert.ToInt32(reader["AuditRatingId"].ToString());
AuditRating_rating = new AuditRating(Convert.ToInt32(reader["AuditRatingId"].ToString()));
}
//if (reader["UpdUserId"] == System.DBNull.Value)
//{
// UpdUser_Id = null;
// UpdUser_User = new Users();
// UpdateDt = null; //new DateTime();
//}
//else
//{
// UpdUser_Id = Convert.ToInt32(reader["UpdUserId"].ToString());
// UpdUser_User = new Users(Convert.ToInt32(reader["UpdUserId"].ToString()));
// UpdateDt = Convert.ToDateTime(reader["UpdDt"].ToString());
//}
ret.Add(new AuditRev()
{
Id = Convert.ToInt32(reader["Id"].ToString()),
AuditId = Convert.ToInt32(reader["AuditId"].ToString()),
Year = Convert.ToInt32(reader["Year"].ToString()),
CompanyId = Convert.ToInt32(reader["CompanyId"].ToString()),
Company = new Companies(Convert.ToInt32(reader["CompanyId"].ToString())),
AuditTypeId = Convert.ToInt32(reader["AuditTypeId"].ToString()),
AuditType = new AuditTypes(Convert.ToInt32(reader["AuditTypeId"].ToString())),
Title = reader["Title"].ToString(),
ReportDt = Convert.ToDateTime(reader["ReportDt"].ToString()),
Auditor1ID = Convert.ToInt32(reader["Auditor1Id"].ToString()),
Auditor1 = new Users(Convert.ToInt32(reader["Auditor1Id"].ToString())),
Auditor2ID = Auditor2_Id, //Convert.ToInt32(reader["Auditor2Id"].ToString()),
Auditor2 = Auditor2_User, //new Users(Convert.ToInt32(reader["Auditor2Id"].ToString())),
SupervisorID = Supervisor_Id, //Convert.ToInt32(reader["SupervisorId"].ToString()),
Supervisor = Supervisor_User, //new Users(Convert.ToInt32(reader["SupervisorId"].ToString())),
IsCompleted = Convert.ToBoolean(reader["IsCompleted"].ToString()),
AuditNumber = reader["AuditNumber"].ToString(),
IASentNumber = reader["IASentNumber"].ToString(),
InsUserId = Convert.ToInt32(reader["InsUserId"].ToString()),
InsUser = new Users(Convert.ToInt32(reader["InsUserId"].ToString())),
InsDt = Convert.ToDateTime(reader["InsDt"].ToString()),
UpdUserId = Convert.ToInt32(reader["UpdUserId"].ToString()),
UpdUser = new Users(Convert.ToInt32(reader["UpdUserId"].ToString())),
UpdDt = Convert.ToDateTime(reader["UpdDt"].ToString()),
//UpdUserId = UpdUser_Id,
//UpdUser = UpdUser_User,
//UpdDt = UpdateDt,
RevNo = Convert.ToInt32(reader["RevNo"].ToString()),
AttCnt = Convert.ToInt32(reader["AttCnt"].ToString()),
AuditRatingId = AuditRating_Id,
AuditRating = AuditRating_rating,
IsDeleted = Convert.ToBoolean(reader["IsDeleted"].ToString())
});
}
reader.Close();
sqlConn.Close();
}
catch (Exception ex)
{
MessageBox.Show("The following error occurred: " + ex.Message);
}
return(ret);
}
