public IEnumerable<Patchs> postUpdatePatchInformations(Patchs update)
{
List<Patchs> updatePatchInfors = new List<Patchs>();
objConn = objDB.EstablishConnection();
string sqlUpdatePatchInformations = "BEGIN;";
sqlUpdatePatchInformations += " UPDATE patchs SET PatchsName = '" + update.patchsName.ToString()
+ "', PatchsDescription = '" + update.patchsDescription.ToString()
+ "', PatchsUpdateDate = '" + update.patchsUpdateDate.ToString()
+ "', PatchsUpdateBy = '" + update.patchsUpdateBy.ToString()
+ "', PatchsVersionNumber = '" + update.patchsVersionNumber.ToString()
+ "' WHERE PatchsID = '" + Convert.ToInt32(update.patchsID.ToString()) + "';";
sqlUpdatePatchInformations += " UPDATE patchparentversion SET SoftwareVersionID = '" + Convert.ToInt32(update.softwareVersionID.ToString())
+ "', SoftwareTypeID = '" + Convert.ToInt32(update.softwareTypeID.ToString())
+ "'WHERE PatchsID = '" + Convert.ToInt32(update.patchsID.ToString()) + "'; ";
sqlUpdatePatchInformations += "COMMIT;";
objDB.sqlExecute(sqlUpdatePatchInformations, objConn);
objConn.Close();
return updatePatchInfors.ToArray();
}
public IEnumerable<Patchs> postPatchInformations(Patchs items)
{
List<Patchs> patchInfors = new List<Patchs>();
objConn = objDB.EstablishConnection();
string sqlPatchID = "SELECT MAX(p.PatchsID) AS PatchsID, ";
sqlPatchID += "MAX(pv.PatchParentVersionID) AS PatchParentVersionID, ";
sqlPatchID += "MAX(sv.SoftwareVersionID) AS SoftwareVersionID ";
sqlPatchID += "FROM patchparentversion pv ";
sqlPatchID += "INNER JOIN patchs p ON p.PatchsID = pv.PatchsID ";
sqlPatchID += "INNER JOIN softwareversion sv ON sv.SoftwareVersionID = pv.SoftwareVersionID";
DataTable dtPatchID = objDB.List(sqlPatchID, objConn);
int maxPatchID = Convert.ToInt32(dtPatchID.Rows[0]["PatchsID"].ToString()) + 1;
DataTable dtPatchParentVersionID = objDB.List(sqlPatchID, objConn);
int maxPatchParentVersionID = Convert.ToInt32(dtPatchParentVersionID.Rows[0]["PatchParentVersionID"].ToString()) + 1;
DataTable dtSoftwareVersionID = objDB.List(sqlPatchID, objConn);
int maxSoftwareVersionID = Convert.ToInt32(dtSoftwareVersionID.Rows[0]["SoftwareVersionID"].ToString()) + 1;
string sqlInsertPatchInfors = "BEGIN; ";
sqlInsertPatchInfors += "INSERT INTO patchs(PatchsID, PatchParentVersionID, PatchsName, PatchsDescription, ";
sqlInsertPatchInfors += "PatchsInsertDate, PatchsUpdateDate, PatchsInsertBy, PatchsVersionNumber, Activated) ";
sqlInsertPatchInfors += "VALUES('" + maxPatchID + "', '" + maxPatchParentVersionID
+ "', '" + items.patchsName.ToString() + "', '" + items.patchsDescription.ToString()
+ "', '" + items.patchsInsertDate.ToString() + "', '" + items.patchsUpdateDate.ToString()
+ "', '" + items.patchsInsertBy.ToString() + "', '" + items.patchsVersionNumber.ToString() + "', '0');";
sqlInsertPatchInfors += "INSERT INTO patchparentversion(PatchParentVersionID, PatchsID, SoftwareVersionID, SoftwareTypeID, StaffID) ";
sqlInsertPatchInfors += "VALUES('" + maxPatchParentVersionID + "', '" + maxPatchID
+ "','" + items.softwareVersionID.ToString() + "','" + items.softwareTypeID.ToString()
+ "', '" + Convert.ToInt32(items.staffID.ToString()) + "'); ";
//sqlInsertPatch += "INSERT INTO softwareversion(SoftwareVersionID, SoftwareVersionName)";
//sqlInsertPatch += "VALUES('" + maxSoftwareVersionID + "', '" + items.SoftwareVersionName.ToString() + "');";
sqlInsertPatchInfors += "COMMIT;";
objDB.sqlExecute(sqlInsertPatchInfors, objConn);
objConn.Close();
return patchInfors.ToArray();
}
public IEnumerable<Patchs> updateMyPatchInformations(Patchs update)
{
return repository.postUpdatePatchInformations(update);
}
public IEnumerable<Patchs> getPatchInformations()
{
objConn = objDB.EstablishConnection();
List<Patchs> patchs = new List<Patchs>();
string sql = "SELECT p.PatchsID, p.PatchsName, sv.SoftwareVersionID, ";
sql += "CONCAT(sv.SoftwareVersionName, '.', p.PatchsVersionNumber) AS SoftwareVersion, ";
sql += "st.SoftwareTypeID, st.SoftwareTypeName, p.PatchsDescription, ";
sql += "DATE_FORMAT(p.PatchsInsertDate, '%d %M %Y') AS PatchsInsertDate, ";
sql += "DATE_FORMAT(p.PatchsUpdateDate, '%d %M %Y') AS PatchsUpdateDate, ";
sql += "p.PatchsInsertBy, p.PatchsUpdateBy, f.FilesName ";
sql += "FROM patchparentversion pv ";
sql += "INNER JOIN patchs p ON p.PatchsID = pv.PatchsID ";
sql += "INNER JOIN softwareversion sv ON sv.SoftwareVersionID = pv.SoftwareVersionID ";
sql += "INNER JOIN softwaretype st ON st.SoftwareTypeID = pv.SoftwareTypeID ";
sql += "INNER JOIN files f ON f.FilesID = pv.FilesID ";
sql += "WHERE p.Deleted = 0 AND p.Activated = 1 ";
sql += "ORDER BY PatchsUpdateDate DESC";
DataTable dt = objDB.List(sql, objConn);
objConn.Close();
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
Patchs patchInfo = new Patchs();
patchInfo.patchsID = Convert.ToInt32(dt.Rows[i]["PatchsID"].ToString());
patchInfo.patchsName = dt.Rows[i]["PatchsName"].ToString();
patchInfo.softwareVersionID = Convert.ToInt32(dt.Rows[i]["SoftwareVersionID"].ToString());
patchInfo.softwareVersionName = dt.Rows[i]["SoftwareVersion"].ToString();
patchInfo.softwareTypeID = Convert.ToInt32(dt.Rows[i]["SoftwareTypeID"].ToString());
patchInfo.softwareTypeName = dt.Rows[i]["SoftwareTypeName"].ToString();
patchInfo.patchsDescription = dt.Rows[i]["PatchsDescription"].ToString();
patchInfo.patchsInsertDate = dt.Rows[i]["PatchsInsertDate"].ToString();
patchInfo.patchsUpdateDate = dt.Rows[i]["PatchsUpdateDate"].ToString();
patchInfo.patchsInsertBy = dt.Rows[i]["PatchsInsertBy"].ToString();
patchInfo.patchsUpdateBy = dt.Rows[i]["PatchsUpdateBy"].ToString();
patchInfo.filesName = dt.Rows[i]["FilesName"].ToString();
patchs.Add(patchInfo);
}
}
return patchs.ToArray();
}
public IEnumerable<Patchs> PostPatchInfos(Patchs items)
{
return repository.postPatchInformations(items);
}