public MyPatch getMyPatchInformations(int patchID)
{
objConn = objDB.EstablishConnection();
MyPatch myPatchsDetails = new MyPatch();
string sqlMyPatchDetails = "SELECT s.StaffID, p.PatchsID, p.PatchsName, st.SoftwareTypeID, sv.SoftwareVersionID, p.PatchsVersionNumber, ";
sqlMyPatchDetails += "CONCAT(sv.SoftwareVersionName, '.', p.PatchsVersionNumber) AS SoftwareVersionName, s.StaffFirstname, ";
sqlMyPatchDetails += "st.SoftwareTypeName, DATE_FORMAT(p.PatchsInsertDate, '%d %M %Y') AS PatchsInsertDate, ";
sqlMyPatchDetails += "p.PatchsDescription, f.FilesName ";
sqlMyPatchDetails += " FROM patchparentversion ppv";
sqlMyPatchDetails += " INNER JOIN softwareversion sv ON ppv.SoftwareVersionID = sv.SoftwareVersionID";
sqlMyPatchDetails += " INNER JOIN softwaretype st ON ppv.SoftwareTypeID = st.SoftwareTypeID";
sqlMyPatchDetails += " INNER JOIN patchs p ON ppv.PatchsID = p.PatchsID";
sqlMyPatchDetails += " INNER JOIN staffs s ON ppv.StaffID = s.StaffID";
sqlMyPatchDetails += " INNER JOIN files f ON ppv.FilesID = f.FilesID";
sqlMyPatchDetails += " WHERE p.PatchsID = '" + patchID + "' AND p.Deleted = 0 ";
sqlMyPatchDetails += " ORDER BY p.PatchsInsertDate DESC";
DataTable dt = objDB.List(sqlMyPatchDetails, objConn);
objConn.Close();
myPatchsDetails.patchsID = Convert.ToInt32(dt.Rows[0]["PatchsID"].ToString());
myPatchsDetails.patchsName = dt.Rows[0]["PatchsName"].ToString();
myPatchsDetails.softwareTypeName = dt.Rows[0]["SoftwareTypeName"].ToString();
myPatchsDetails.patchsVersionNumber = dt.Rows[0]["PatchsVersionNumber"].ToString();
myPatchsDetails.patchsDescription = dt.Rows[0]["PatchsDescription"].ToString();
myPatchsDetails.softwareTypeID = Convert.ToInt32(dt.Rows[0]["SoftwareTypeID"].ToString());
myPatchsDetails.softwareVersionID = Convert.ToInt32(dt.Rows[0]["SoftwareVersionID"].ToString());
myPatchsDetails.softwareVersionName = dt.Rows[0]["SoftwareVersionName"].ToString();
myPatchsDetails.patchsInsertDate = dt.Rows[0]["PatchsInsertDate"].ToString();
myPatchsDetails.fileName = dt.Rows[0]["FilesName"].ToString();
return myPatchsDetails;
}
public IEnumerable<MyPatch> getMyPatch(int staffID)
{
objConn = objDB.EstablishConnection();
List<MyPatch> titlePatchs = new List<MyPatch>();
string sqlMyPatch = "SELECT p.PatchsID, p.PatchsName, st.SoftwareTypeID, sv.SoftwareVersionID, ";
sqlMyPatch += "CONCAT(sv.SoftwareVersionName, '.', p.PatchsVersionNumber) AS SoftwareVersionName, s.StaffFirstname, ";
sqlMyPatch += " st.SoftwareTypeName, DATE_FORMAT(p.PatchsInsertDate, '%d %M %Y') AS PatchsInsertDate,";
sqlMyPatch += " DATE_FORMAT(p.PatchsUpdateDate, '%d %M %Y') AS PatchsUpdateDate, p.Activated";
sqlMyPatch += " FROM patchparentversion ppv";
sqlMyPatch += " INNER JOIN softwareversion sv ON ppv.SoftwareVersionID = sv.SoftwareVersionID";
sqlMyPatch += " INNER JOIN softwaretype st ON ppv.SoftwareTypeID = st.SoftwareTypeID";
sqlMyPatch += " INNER JOIN patchs p ON ppv.PatchsID = p.PatchsID";
sqlMyPatch += " INNER JOIN staffs s ON ppv.StaffID = s.StaffID";
sqlMyPatch += " WHERE s.StaffID = '" + staffID + "' AND p.Deleted = 0 ";
sqlMyPatch += " ORDER BY p.PatchsUpdateDate DESC";
DataTable dt = objDB.List(sqlMyPatch, objConn);
objConn.Close();
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
MyPatch myPatch = new MyPatch();
myPatch.patchsID = Convert.ToInt32(dt.Rows[i]["PatchsID"].ToString());
myPatch.patchsName = dt.Rows[i]["PatchsName"].ToString();
myPatch.softwareTypeName = dt.Rows[i]["SoftwareTypeName"].ToString();
myPatch.softwareTypeID = Convert.ToInt32(dt.Rows[i]["SoftwareTypeID"].ToString());
myPatch.softwareVersionID = Convert.ToInt32(dt.Rows[i]["SoftwareVersionID"].ToString());
myPatch.softwareVersionName = dt.Rows[i]["SoftwareVersionName"].ToString();
myPatch.patchsInsertDate = dt.Rows[i]["PatchsInsertDate"].ToString();
myPatch.patchsUpdatedate = dt.Rows[i]["PatchsUpdateDate"].ToString();
myPatch.staffFirstname = dt.Rows[i]["StaffFirstname"].ToString();
myPatch.activated = Convert.ToInt32(dt.Rows[i]["Activated"].ToString());
titlePatchs.Add(myPatch);
}
}
return titlePatchs;
}