public static string CreateWorkflow(PNPU_WORKFLOW input)
{
string[] requests = { "INSERT INTO PNPU_WORKFLOW ( WORKFLOW_LABEL) VALUES( @WORKFLOW_LABEL)" };
string[] parameters = new string[] { "@WORKFLOW_LABEL", input.WORKFLOW_LABEL };
return(DataManagerSQLServer.ExecuteSqlTransaction(requests, "PNPU_WORKFLOW", parameters, true));
/*using (var conn = new System.Data.SqlClient.SqlConnection(ParamAppli.ConnectionStringBaseAppli))
* {
* string LastInsertedPK = "";
* try
* {
* conn.Open();
* using (var cmd = new System.Data.SqlClient.SqlCommand("INSERT INTO PNPU_WORKFLOW ( WORKFLOW_LABEL) VALUES( @WORKFLOW_LABEL)", conn))
* {
* cmd.Parameters.Add("@WORKFLOW_LABEL", SqlDbType.VarChar, 254).Value = input.WORKFLOW_LABEL;
* int rowsAffected = cmd.ExecuteNonQuery();
* if (rowsAffected > 0)
* {
* LastInsertedPK = DataManagerSQLServer.GetLastInsertedPK("PNPU_WORKFLOW", ParamAppli.ConnectionStringBaseAppli);
* }
* }
* }
* catch (SqlException ex)
* {
* return ex.ToString();
* }
* return LastInsertedPK;
* }*/
}
public static string AffectWorkflowsProcesses(PNPU_STEP input, string workflowID)
{
string[] requests = { "INSERT INTO PNPU_STEP ( ORDER_ID, ID_PROCESS, WORKFLOW_ID) VALUES( @ORDER_ID, @ID_PROCESS, @WORKFLOW_ID)" };
string[] parameters = new string[] { "@ORDER_ID", input.ID_ORDER.ToString(), "@ID_PROCESS", input.ID_PROCESS.ToString(), "@WORKFLOW_ID", input.ID_WORKFLOW };
return(DataManagerSQLServer.ExecuteSqlTransaction(requests, "PNPU_STEP", parameters, false));
/*using (var conn = new System.Data.SqlClient.SqlConnection(ParamAppli.ConnectionStringBaseAppli))
* {
* try
* {
* conn.Open();
* using (var cmd = new System.Data.SqlClient.SqlCommand("INSERT INTO PNPU_STEP ( ORDER_ID, ID_PROCESS, WORKFLOW_ID) VALUES( @ORDER_ID, @ID_PROCESS, @WORKFLOW_ID)", conn))
* {
* cmd.Parameters.Add("@ORDER_ID", SqlDbType.Int).Value = input.ID_ORDER;
* cmd.Parameters.Add("@ID_PROCESS", SqlDbType.VarChar, 254).Value = input.ID_PROCESS;
* cmd.Parameters.Add("@WORKFLOW_ID", SqlDbType.VarChar, 254).Value = input.ID_WORKFLOW;
* int rowsAffected = cmd.ExecuteNonQuery();
* }
* }
* catch (SqlException ex)
* {
* return ex.ToString();
* }
* return "Requête traitée avec succès et création d’un document.";
* }*/
}
public static string ModifyWorkflow(PNPU_WORKFLOW input, string workflowID)
{
string[] requests = { "UPDATE PNPU_WORKFLOW SET WORKFLOW_LABEL = @WORKFLOW_LABEL WHERE WORKFLOW_ID = @WORKFLOW_ID " };
string[] parameters = new string[] { "@WORKFLOW_ID", workflowID, "@WORKFLOW_LABEL", input.WORKFLOW_LABEL };
return(DataManagerSQLServer.ExecuteSqlTransaction(requests, "PNPU_WORKFLOW", parameters, false));
/*using (var conn = new System.Data.SqlClient.SqlConnection(ParamAppli.ConnectionStringBaseAppli))
* {
* try
* {
* conn.Open();
* using (var cmd = new System.Data.SqlClient.SqlCommand("UPDATE PNPU_WORKFLOW SET WORKFLOW_LABEL = @WORKFLOW_LABEL WHERE WORKFLOW_ID = @WORKFLOW_ID ", conn))
* {
* cmd.Parameters.Add("@WORKFLOW_ID", SqlDbType.Int).Value = workflowID;
* cmd.Parameters.Add("@WORKFLOW_LABEL", SqlDbType.VarChar, 254).Value = input.WORKFLOW_LABEL;
* int rowsAffected = cmd.ExecuteNonQuery();
* }
* }
* catch (SqlException ex)
* {
* return ex.ToString();
* }
* return "Requête traitée avec succès et création d’un document.";
* }*/
}
public string SaveReportInBDD(string json, IProcess process)
{
string[] requests = { "INSERT INTO PNPU_H_REPORT (ITERATION, WORKFLOW_ID, ID_PROCESS, CLIENT_ID, JSON_TEMPLATE) VALUES(@ITERATION, @WORKFLOW_ID, @ID_PROCESS, @CLIENT_ID, @JSON_TEMPLATE)" };
string[] parameters = new string[] { "@ITERATION", "1", "@WORKFLOW_ID", process.WORKFLOW_ID.ToString(), "@ID_PROCESS", process.PROCESS_ID.ToString(), "@CLIENT_ID", process.CLIENT_ID, "@JSON_TEMPLATE", json.Replace("\r\n", "") };
return(DataManagerSQLServer.ExecuteSqlTransaction(requests, "PNPU_H_REPORT", parameters, false));
/*using (var conn = new System.Data.SqlClient.SqlConnection(ParamAppli.ConnectionStringBaseAppli))
* {
* try
* {
* conn.Open();
* using (var cmd = new System.Data.SqlClient.SqlCommand("INSERT INTO PNPU_H_REPORT (ITERATION, WORKFLOW_ID, ID_PROCESS, CLIENT_ID, JSON_TEMPLATE) VALUES(@ITERATION, @WORKFLOW_ID, @ID_PROCESS, @CLIENT_ID, @JSON_TEMPLATE)", conn))
* {
* cmd.Parameters.Add("@ITERATION", SqlDbType.Int, 10).Value = 1;
* cmd.Parameters.Add("@WORKFLOW_ID", SqlDbType.Int, 15).Value = process.WORKFLOW_ID;
* cmd.Parameters.Add("@ID_PROCESS", SqlDbType.Int, 15).Value = process.PROCESS_ID;
* cmd.Parameters.Add("@CLIENT_ID", SqlDbType.VarChar, 64).Value = process.CLIENT_ID;
* cmd.Parameters.Add("@JSON_TEMPLATE", SqlDbType.Text).Value = json.Replace("\r\n", "");
* int rowsAffected = cmd.ExecuteNonQuery();
* }
* }
* catch (SqlException ex)
* {
* string msgError = "L'insertion du report du client " + process.CLIENT_ID + ", process " + process.PROCESS_ID + " ,workflow " + process.WORKFLOW_ID + " a échoué !";
* Console.WriteLine(msgError);
* return false;
* }
* return true;
* }*/
}
static string getAllInfoClient()
{
DataSet result = DataManagerSQLServer.GetDatas(requestAllClient, connectionStringSupport);
string json = JsonConvert.SerializeObject(result, Formatting.Indented);
return(json);
}
public static string DeleteWorkflow(string workflowID)
{
string[] requests = { "DELETE FROM PNPU_WORKFLOW WHERE WORKFLOW_ID = @WORKFLOW_ID " };
string[] parameters = new string[] { "@WORKFLOW_ID", workflowID };
return(DataManagerSQLServer.ExecuteSqlTransaction(requests, "PNPU_WORKFLOW", parameters, false));
/* using (var conn = new System.Data.SqlClient.SqlConnection(ParamAppli.ConnectionStringBaseAppli))
* {
* try
* {
* conn.Open();
* using (var cmd = new System.Data.SqlClient.SqlCommand("DELETE FROM PNPU_WORKFLOW WHERE WORKFLOW_ID = @WORKFLOW_ID ", conn))
* {
* cmd.Parameters.Add("@WORKFLOW_ID", SqlDbType.Int).Value = workflowID;
* int rowsAffected = cmd.ExecuteNonQuery();
* }
* using (var cmd = new System.Data.SqlClient.SqlCommand("DELETE FROM PNPU_STEP WHERE WORKFLOW_ID = @WORKFLOW_ID", conn))
* {
* cmd.Parameters.Add("@WORKFLOW_ID", SqlDbType.Int).Value = workflowID;
* int rowsAffected = cmd.ExecuteNonQuery();
* }
* }
* catch (SqlException ex)
* {
* return ex.ToString();
* }
* return "Requête traitée avec succès et création d’un document.";
* }*/
}
public static string ModifyProcessus(PNPU_PROCESS input, string processID)
{
string[] requests = { "UPDATE PNPU_PROCESS SET PROCESS_LABEL = @PROCESS_LABEL, IS_LOOPABLE = @IS_LOOPABLE WHERE ID_PROCESS = @ID_PROCESS" };
string[] parameters = new string[] { "@ID_PROCESS", processID, "@PROCESS_LABEL", input.PROCESS_LABEL, "@IS_LOOPABLE", input.IS_LOOPABLE };
return(DataManagerSQLServer.ExecuteSqlTransaction(requests, "PNPU_PROCESS", parameters, false));
/*using (var conn = new System.Data.SqlClient.SqlConnection(ParamAppli.ConnectionStringBaseAppli))
* {
* try
* {
* conn.Open();
* using (var cmd = new System.Data.SqlClient.SqlCommand("UPDATE PNPU_PROCESS SET PROCESS_LABEL = @PROCESS_LABEL, IS_LOOPABLE = @IS_LOOPABLE WHERE ID_PROCESS = @ID_PROCESS", conn))
* {
* cmd.Parameters.Add("@ID_PROCESS", SqlDbType.Int).Value = processID;
* cmd.Parameters.Add("@PROCESS_LABEL", SqlDbType.VarChar, 254).Value = input.PROCESS_LABEL;
* cmd.Parameters.Add("@IS_LOOPABLE", SqlDbType.VarChar, 254).Value = input.IS_LOOPABLE;
* int rowsAffected = cmd.ExecuteNonQuery();
* }
* }
* catch (SqlException ex)
* {
* return ex.ToString();
* }
* return "Requête traitée avec succès et création d’un document.";
* }*/
}
public static PNPU_WORKFLOW getWorkflow(string workflowId)
{
DataSet result = DataManagerSQLServer.GetDatas(requestOneWorkflow + "'" + workflowId + "'", ParamAppli.ConnectionStringBaseAppli);
DataTable table = result.Tables[0];
IEnumerable <PNPU_WORKFLOW> listTest = table.DataTableToList <PNPU_WORKFLOW>();
return(listTest.First());
}
/// <summary>
/// Méthode chargeant la liste des M4O et des présentation hérités en standard.
/// </summary>
private void ChargeM4OPresHerites()
{
string sRequete = string.Empty;
DataSet dsDataSet = null;
DataManagerSQLServer dmaManagersqlServer = null;
lObjetsHeritesSTD = new List <string[]>();
lPresentsHeritesSTD = new List <string[]>();
try
{
if (ConnectionStringBaseRef != string.Empty)
{
dmaManagersqlServer = new DataManagerSQLServer();
/* Récupération des M4O hérités au niveau standard */
sRequete = "select OS.ID_T3 AS ID_T3, PMS.ID_PROJECT AS ID_PROJECT, PMS.ID_INSTANCE AS ID_INSTANCE FROM SPR_DIN_OBJECTS OS inner join M4RDM_OS_PROJ_MEMS PMS on (OS.ID = PMS.ID_INSTANCE and PMS.ID_CLASS = 'DIN_OBJECT' AND PMS.ID_PROJECT IN ('STANDARD','_M4ROOT'))";
dsDataSet = dmaManagersqlServer.GetData(sRequete, ConnectionStringBaseRef);
if ((dsDataSet != null) && (dsDataSet.Tables[0].Rows.Count > 0))
{
foreach (DataRow drRow in dsDataSet.Tables[0].Rows)
{
lObjetsHeritesSTD.Add(new string[] { drRow[0].ToString(), drRow[1].ToString(), drRow[2].ToString() });
if (sListeID_T3 != string.Empty)
{
sListeID_T3 += ",";
}
sListeID_T3 += "'" + drRow[0].ToString() + "'";
}
dsDataSet.Clear();
}
/* Récupération des présentations héritées au niveau standard */
sRequete = "select PS.ID_PRESENTATION,PMS.ID_PROJECT,PMS.ID_INSTANCE FROM SPR_DIN_PRESENTS PS inner join M4RDM_OS_PROJ_MEMS PMS on (PS.ID = PMS.ID_INSTANCE and PMS.ID_CLASS = 'DIN_PRESENT' AND PMS.ID_PROJECT IN ('STANDARD','_M4ROOT'))";
dsDataSet = dmaManagersqlServer.GetData(sRequete, ConnectionStringBaseRef);
if ((dsDataSet != null) && (dsDataSet.Tables[0].Rows.Count > 0))
{
foreach (DataRow drRow in dsDataSet.Tables[0].Rows)
{
lPresentsHeritesSTD.Add(new string[] { drRow[0].ToString(), drRow[1].ToString(), drRow[2].ToString() });
if (sListeID_PRES != string.Empty)
{
sListeID_PRES += ",";
}
sListeID_PRES += "'" + drRow[0].ToString() + "'";
}
dsDataSet.Clear();
}
}
}
catch (Exception ex)
{
// TODO, loguer l'exception
}
}
public static IEnumerable <PNPU_STEP> GetAllStep()
{
DataSet result = DataManagerSQLServer.GetDatas(requestAllStep, ParamAppli.ConnectionStringBaseAppli);
DataTable table = result.Tables[0];
IEnumerable <PNPU_STEP> listTest = table.DataTableToList <PNPU_STEP>();
return(listTest);
}
public static PNPU_PROCESS GetProcess(string processId)
{
DataSet result = DataManagerSQLServer.GetDatas(requestOneProcess + processId, ParamAppli.ConnectionStringBaseAppli);
DataTable table = result.Tables[0];
IEnumerable <PNPU_PROCESS> listTest = table.DataTableToList <PNPU_PROCESS>();
return(listTest.First());
}
public static string GetInfoOneClient(string clientName)
{
DataSet result = DataManagerSQLServer.GetDatas(requestOneClient + "'" + clientName + "'", ParamAppli.ConnectionStringSupport);
string json = JsonConvert.SerializeObject(result, Formatting.Indented);
var regex = new Regex(Regex.Escape("Table"));
var newJson = regex.Replace(json, "Clients", 1);
newJson = newJson.Replace("\r\n", "");
return(newJson);
}
public static IEnumerable <PNPU_WORKFLOW> GetAllWorkFLow()
{
DataSet result = DataManagerSQLServer.GetDatas(requestAllWorkflow, ParamAppli.ConnectionStringBaseAppli);
DataTable table = result.Tables[0];
IEnumerable <PNPU_WORKFLOW> listTest = table.DataTableToList <PNPU_WORKFLOW>();
return(listTest);
}
public static IEnumerable <PNPU_WORKFLOWPROCESSES> GetWorkflowProcesses(string workflowId)
{
string OrderBy = " ORDER BY PS.ORDER_ID";
DataSet result = DataManagerSQLServer.GetDatas(requestGetWorkflowProcesses + workflowId + OrderBy, ParamAppli.ConnectionStringBaseAppli);
DataTable table = result.Tables[0];
IEnumerable <PNPU_WORKFLOWPROCESSES> listTest = table.DataTableToList <PNPU_WORKFLOWPROCESSES>();
return(listTest);
}
public static InfoClient getClientsById(string idClient)
{
string finalRequest = string.Format(requestClientById, idClient);
DataSet result = DataManagerSQLServer.GetDatas(finalRequest, ParamAppli.connectionStringSupport);
DataTable table = result.Tables[0];
IEnumerable <InfoClient> listTest = table.DataTableToList <InfoClient>();
return(listTest.First());
}
public static IEnumerable <InfoClient> getClientsWithTypologies()
{
string finalRequest = requestListClientAll;
DataSet result = DataManagerSQLServer.GetDatas(finalRequest, ParamAppli.connectionStringSupport);
DataTable table = result.Tables[0];
IEnumerable <InfoClient> listTest = table.DataTableToList <InfoClient>();
return(listTest);
}
/// <summary>
/// Methode permettant de récupérer dynamiquement la liste des contrôles à lancer en fonction du process, de la typologie client et du type de pack (standard ou non).
/// </summary>
/// <param name="listControl">Au retour de l'appel contient la liste des contrôles à exécuter dans le process</param>
protected void GetListControle(ref List <IControle> listControl)
{
DataManagerSQLServer dmsDataManager = new DataManagerSQLServer();
DataSet dsDataSet;
string sRequete = "SELECT ID_CONTROLE, CONTROLE_LABEL, TYPOLOGY, RUN_STANDARD, ID_PROCESS, ERROR_TYPE, TOOLTIP FROM PNPU_CONTROLE WHERE ID_PROCESS =" + this.PROCESS_ID.ToString();
if ((this.CLIENT_ID != string.Empty) && (this.CLIENT_ID != "ALL"))
{
try
{
string sClient_ID;
if (this.CLIENT_ID.Contains(",") == true)
{
sClient_ID = this.CLIENT_ID.Split(',')[0];
}
else
{
sClient_ID = this.CLIENT_ID;
}
this.TYPOLOGY = ParamAppli.ListeInfoClient[sClient_ID].TYPOLOGY_ID;
if (this.TYPOLOGY != string.Empty)
{
sRequete += " AND ((TYPOLOGY IS NULL) OR (TYPOLOGY LIKE '%*" + this.TYPOLOGY + "*%'))";
}
}
catch (Exception)
{ }
}
/*else if (this.TYPOLOGY != string.Empty)
* {
* sRequete += " AND ((TYPOLOGY IS NULL) OR (TYPOLOGY LIKE '%*" + this.TYPOLOGY + "*%'))";
* }
*/
if (this.STANDARD == false)
{
sRequete += " AND ((RUN_STANDARD IS NULL) OR (RUN_STANDARD <> 'YES'))";
}
listControl.Clear();
dsDataSet = dmsDataManager.GetData(sRequete, ParamAppli.ConnectionStringBaseAppli);
if ((dsDataSet != null) && (dsDataSet.Tables[0].Rows.Count > 0))
{
foreach (DataRow drRow in dsDataSet.Tables[0].Rows)
{
string sControle = drRow[0].ToString();
IControle iControle = (IControle)Activator.CreateInstance(Type.GetType(sControle), this, drRow);
listControl.Add(iControle);
}
}
}
public static string CreateProcess(PNPU_PROCESS input)
{
string[] requests = { "INSERT INTO PNPU_PROCESS ( PROCESS_LABEL, IS_LOOPABLE) VALUES( @PROCESS_LABEL, @IS_LOOPABLE)" };
string[] parameters = new string[] { "@PROCESS_LABEL", input.PROCESS_LABEL, "@IS_LOOPABLE", input.IS_LOOPABLE };
return(DataManagerSQLServer.ExecuteSqlTransaction(requests, "PNPU_PROCESS", parameters, true));
/*string sRequest = "INSERT INTO PNPU_PROCESS ( PROCESS_LABEL, IS_LOOPABLE) VALUES( @PROCESS_LABEL, @IS_LOOPABLE)";
* string sTable = "PNPU_PROCESS";
* string result = DataManagerSQLServer.SendTransactionWithGetLastPKid(sRequest, input, sTable);
*
* return result;*/
}
public static IEnumerable <PNPU_H_REPORT> getReport(decimal idProcess, decimal workflowId, string clientId)
{
String sRequest = "SELECT PHR.ITERATION, PHR.WORKFLOW_ID, PHR.ID_PROCESS, PHR.CLIENT_ID, PHR.JSON_TEMPLATE FROM PNPU_H_REPORT PHR, PNPU_PROCESS PR ";
sRequest += " WHERE PHR.ID_PROCESS = @ID_PROCESS AND PHR.WORKFLOW_ID = @WORKFLOW_ID AND PHR.ID_PROCESS = PR.ID_PROCESS AND ";
sRequest += " ( PHR.CLIENT_ID = @CLIENT_ID OR PR.IS_GLOBAL = '1') ";
DataSet result = DataManagerSQLServer.GetDatasWithParams(sRequest, ParamAppli.ConnectionStringBaseAppli, idProcess, workflowId, clientId);
DataTable table = result.Tables[0];
IEnumerable <PNPU_H_REPORT> listTest = table.DataTableToList <PNPU_H_REPORT>();
return(listTest);
}
public static PNPU_H_STEP getStepHistoric(PNPU_H_STEP step)
{
DataSet result = DataManagerSQLServer.GetDatas(string.Format(requestGetStepHistoric, step.WORKFLOW_ID, step.CLIENT_ID, step.ID_PROCESS, step.ITERATION), ParamAppli.ConnectionStringBaseAppli);
DataTable table = result.Tables[0];
IEnumerable <PNPU_H_STEP> listTest = table.DataTableToList <PNPU_H_STEP>();
if (listTest.Count() >= 1)
{
return(listTest.First());
}
else
{
return(null);
}
}
public static PNPU_H_WORKFLOW getWorkflowHistoric(int workflowId)
{
DataSet result = DataManagerSQLServer.GetDatas(requestOneWorkflowHistoric + "'" + workflowId + "'", ParamAppli.ConnectionStringBaseAppli);
DataTable table = result.Tables[0];
IEnumerable <PNPU_H_WORKFLOW> listTest = table.DataTableToList <PNPU_H_WORKFLOW>();
if (listTest.Count() >= 1)
{
return(listTest.First());
}
else
{
return(null);
}
}
public static int GetNextProcess(decimal wORKFLOW_ID, int processId)
{
string finalRequest = string.Format(requestGetNextProcess, wORKFLOW_ID, processId);
DataSet result = DataManagerSQLServer.GetDatas(finalRequest, ParamAppli.ConnectionStringBaseAppli);
DataTable table = result.Tables[0];
IEnumerable <PNPU_STEP> listTest = table.DataTableToList <PNPU_STEP>();
if (listTest.Count() == 0)
{
return(ParamAppli.ProcessFinished);
}
else
{
return(listTest.First().ID_PROCESS);
}
}
public static string CreateUpdateWorkflowHistoric(PNPU_H_WORKFLOW input)
{
int workFlowId = Decimal.ToInt32(input.WORKFLOW_ID);
if (historicWorkflowExist(workFlowId))
{
//Update for the moment do nothing
return("Requête traitée avec succès et création/mis à jour d'un historique de workflow");
}
else
{
string[] requests = { "INSERT INTO PNPU_H_WORKFLOW ( CLIENT_ID, WORKFLOW_ID, LAUNCHING_DATE, ENDING_DATE, STATUT_GLOBAL) VALUES (@CLIENT_ID, @WORKFLOW_ID, @LAUNCHING_DATE, @ENDING_DATE, @STATUT)" };
string[] parameters = new string[] { "@CLIENT_ID", input.CLIENT_ID, "@WORKFLOW_ID", input.WORKFLOW_ID.ToString(), "@LAUNCHING_DATE", input.LAUNCHING_DATE.ToString("MM/dd/yyyy HH:mm:ss"), "@ENDING_DATE", input.ENDING_DATE.ToString("MM/dd/yyyy HH:mm:ss"), "@STATUT", input.STATUT_GLOBAL };
return(DataManagerSQLServer.ExecuteSqlTransaction(requests, "PNPU_H_WORKFLOW", parameters, true));
/*using (var conn = new System.Data.SqlClient.SqlConnection(ParamAppli.ConnectionStringBaseAppli))
* {
* try
* {
*
* conn.Open();
*
* using (var cmd = new System.Data.SqlClient.SqlCommand("insert into PNPU_H_WORKFLOW ( CLIENT_ID, WORKFLOW_ID, LAUNCHING_DATE, ENDING_DATE, STATUT_GLOBAL) values (@CLIENT_ID, @WORKFLOW_ID, @LAUNCHING_DATE, @ENDING_DATE, @STATUT)", conn))
* {
* cmd.Parameters.Add("@WORKFLOW_ID", SqlDbType.Int).Value = input.WORKFLOW_ID;
* cmd.Parameters.Add("@CLIENT_ID", SqlDbType.VarChar, 254).Value = input.CLIENT_ID;
* cmd.Parameters.Add("@LAUNCHING_DATE", SqlDbType.DateTime).Value = input.LAUNCHING_DATE;
* cmd.Parameters.Add("@STATUT", SqlDbType.VarChar, 254).Value = input.STATUT_GLOBAL;
* cmd.Parameters.Add("@ENDING_DATE", SqlDbType.DateTime).Value = input.ENDING_DATE;
*
* int rowsAffected = cmd.ExecuteNonQuery();
* }
* }
* catch (SqlException ex)
* {
* return ex.ToString();
* }
* return "Requête traitée avec succès et création/mis à jour d'un historique de workflow";
* }*/
}
}
public static IEnumerable <InfoClientStep> GetAllInfoClient(decimal WORKFLOW_ID)
{
// Par défault on charge sur le dashboard le dernier Workflow en cours
string defaultWorkflowID = "(SELECT TOP(1) PHW.WORKFLOW_ID FROM PNPU_H_WORKFLOW PHW, PNPU_H_WORKFLOW PHW2 WHERE PHW.ID_H_WORKFLOW = PHW2.ID_H_WORKFLOW AND PHW.LAUNCHING_DATE = (SELECT MAX(PHW2.LAUNCHING_DATE) FROM PNPU_H_WORKFLOW PHW2))";
string filtre = (WORKFLOW_ID == 0) ? defaultWorkflowID : WORKFLOW_ID.ToString();
string request = "SELECT PHS.ITERATION, PHS.WORKFLOW_ID, PHS.LAUNCHING_DATE, PHS.ENDING_DATE, PHS.ID_STATUT, PHS.CLIENT_ID, PHS.CLIENT_NAME, PHS.TYPOLOGY, PS.ORDER_ID, ";
request += "PS.ID_PROCESS / (SELECT MAX(PS.ID_PROCESS) AS NB_PROCESS FROM PNPU_WORKFLOW PW INNER JOIN PNPU_STEP PS ON PW.WORKFLOW_ID = PS.WORKFLOW_ID WHERE PW.WORKFLOW_ID = " + filtre + " GROUP BY PS.WORKFLOW_ID) *100 AS PERCENTAGE_COMPLETUDE ";
request += "FROM PNPU_H_STEP PHS, PNPU_STEP PS, PNPU_STEP PS2 WHERE PHS.LAUNCHING_DATE = (SELECT MAX(PHS2.LAUNCHING_DATE) FROM PNPU_H_STEP PHS2 WHERE PHS.WORKFLOW_ID = PHS2.WORKFLOW_ID AND PHS.CLIENT_ID = PHS2.CLIENT_ID) AND PHS.WORKFLOW_ID = " + filtre + " ";
request += " AND PS.ORDER_ID = PS2.ORDER_ID AND PS.ID_PROCESS = PS2.ID_PROCESS AND PS.WORKFLOW_ID = PS2.WORKFLOW_ID AND PS.WORKFLOW_ID = PHS.WORKFLOW_ID AND PS.ID_PROCESS = PHS.ID_PROCESS ";
request += "GROUP BY PHS.ITERATION, PHS.WORKFLOW_ID, PHS.LAUNCHING_DATE, PHS.ENDING_DATE, PHS.ID_STATUT, PHS.CLIENT_ID, PHS.CLIENT_NAME, PHS.TYPOLOGY, PHS.ID_PROCESS, PS.ID_PROCESS, PS.ORDER_ID ORDER BY PHS.CLIENT_ID";
DataSet result = DataManagerSQLServer.GetDatas(request, ParamAppli.ConnectionStringBaseAppli);
DataTable table = result.Tables[0];
IEnumerable <InfoClientStep> listTest = table.DataTableToList <InfoClientStep>();
return(listTest);
}
/// <summary>
/// Le but de cette fonction est de déterminer les dépendances de de niveau 1 pour les packs livrés dans le HF.
/// On détecte les tâches CCT dépendantes car au niveau 1 on va récupérer tout le contenu des tâches dépendantes qui manquent sur l'environnement client.
/// </summary>
/// <param name="lTacheDepN2">Ce paramètre contient au retour de l'appel toutes les tâches</param>
/// <returns></returns>
private bool RechercheDepN1(ref List <string> lTacheDepN2)
{
bool bResultat = true;
DataManagerAccess dmaManagerAccess = null;
DataManagerSQLServer dmsManagerSQL = null;
List <string> lTacheCCTHF;
List <string> lTacheDepN1;
string sRequete;
bool bPremierElement = true;
DataSet dsDataSet = null;
string sNomMdb;
string sTacheCCT;
string sListeTacheCCT = string.Empty;
try
{
lTacheDepN2.Clear();
dmaManagerAccess = new DataManagerAccess();
dmsManagerSQL = new DataManagerSQLServer();
lTacheCCTHF = new List <string>();
lTacheDepN1 = new List <string>();
// Récupération de toutes les tâches CCT livrées dans le HF
sRequete = "SELECT DISTINCT(CCT_TASK_ID) FROM M4RDL_PACKAGES";
foreach (string sPathMdb in Process.listMDB)
{
sNomMdb = Path.GetFileName(sPathMdb);
dsDataSet = dmaManagerAccess.GetData(sRequete, sPathMdb);
if ((dsDataSet != null) && (dsDataSet.Tables[0].Rows.Count > 0))
{
foreach (DataRow drRow in dsDataSet.Tables[0].Rows)
{
sTacheCCT = drRow[0].ToString();
if (lTacheCCTHF.Contains(sTacheCCT) == false)
{
lTacheCCTHF.Add(sTacheCCT);
if (bPremierElement == true)
{
bPremierElement = false;
}
else
{
sListeTacheCCT += ",";
}
sListeTacheCCT += "'" + sTacheCCT + "'";
}
}
}
}
if (lTacheCCTHF.Count > 0)
{
sRequete = "SELECT CCT_TASK_ID, DEP_CCT_TASK_ID from M4CFR_VW_CCT_DEPENDANCES where ";
sRequete += "CCT_TASK_ID IN (" + sListeTacheCCT + ") ";
sRequete += "AND DEP_CCT_TASK_ID NOT IN (" + sListeTacheCCT + ") ";
sRequete += "AND DEP_CCT_TASK_ID not like '%DEF%' ";
sRequete += "AND CCT_OBJECT_TYPE+CCT_OBJECT_ID NOT IN ('PRESENTATIONSFR_DP_PAYROLL_CHANNEL','PRESENTATIONSCO_DP_PAYROLL_CHANNEL')";
dsDataSet = dmsManagerSQL.GetData(sRequete, ConnectionStringBaseRef);
if ((dsDataSet != null) && (dsDataSet.Tables[0].Rows.Count > 0))
{
using (var conn = new System.Data.SqlClient.SqlConnection(ParamAppli.ConnectionStringBaseAppli))
{
conn.Open();
using (var cmd = new System.Data.SqlClient.SqlCommand("INSERT INTO PNPU_DEPN1_REF (ID_H_WORKFLOW, CCT_TASK_ID, DEP_CCT_TASK_ID) VALUES( @WORKFLOW_ID, @CCT_TASK_ID,@DEP_CCT_TASK_ID)", conn))
{
cmd.Parameters.Add("@WORKFLOW_ID", SqlDbType.Int);
cmd.Parameters.Add("@CCT_TASK_ID", SqlDbType.VarChar, 30);
cmd.Parameters.Add("@DEP_CCT_TASK_ID", SqlDbType.VarChar, 30);
foreach (DataRow drRow in dsDataSet.Tables[0].Rows)
{
sTacheCCT = drRow[0].ToString() + "*" + drRow[1].ToString();
if (lTacheDepN1.Contains(sTacheCCT) == false)
{
lTacheDepN1.Add(sTacheCCT);
if (lTacheDepN2.Contains(drRow[1].ToString()) == false)
{
lTacheDepN2.Add(drRow[1].ToString());
}
cmd.Parameters[0].Value = Process.WORKFLOW_ID;
cmd.Parameters[1].Value = drRow[0].ToString();
cmd.Parameters[2].Value = drRow[1].ToString();
int rowsAffected = cmd.ExecuteNonQuery();
}
}
}
}
}
}
}
catch (Exception ex)
{
Logger.Log(Process, this, ParamAppli.StatutError, ex.Message);
bResultat = false;
}
return(bResultat);
}
/// <summary>
/// Le but de cette fonction est de déterminer les dépendances de de niveau 1 pour les packs livrés dans le HF.
/// On détecte les tâches CCT dépendantes car au niveau 1 on va récupérer tout le contenu des tâches dépendantes qui manquent sur l'environnement client.
/// </summary>
/// <param name="iNiveau">Niveau de dépendance recherché.</param>
/// <param name="sFiltreNiveauxPrec">Liste des tâches CCT des niveaux précédents, pour ne pas avoir de dépendance cyclique</param>
/// <param name="sFiltreNiveauN">Liste des tâche CCT pour lesquelles on cherche les dépendances</param>
/// <param name="sFiltreNiveauN1">Au retour contient la liste des tâches CCT dépendantes</param>
/// <returns></returns>
private bool RechercheDependances(int iNiveau, string sFiltreNiveauxPrec, string sFiltreNiveauN, ref string sFiltreNiveauN1)
{
bool bResultat = true;
string sTacheCCT;
DataManagerSQLServer dmsManagerSQL = null;
List <string> lTacheCCT;
string sRequete;
bool bPremierElement;
DataSet dsDataSet = null;
const string CCT_OBJECT_TYPE_INT = "'WEB FILE','WEB LITERAL SOC'";
string sListeTacheCCT = string.Empty;
try
{
dmsManagerSQL = new DataManagerSQLServer();
lTacheCCT = new List <string>();
if (sFiltreNiveauN != string.Empty)
{
sRequete = "SELECT A.CCT_TASK_ID,A.CCT_OBJECT_TYPE,A.CCT_OBJECT_ID,A.CCT_PARENT_OBJ_ID,A.DEP_CCT_TASK_ID,A.DEP_CCT_OBJECT_TYPE,A.DEP_CCT_OBJECT_ID,A.DEP_CCT_PARENT_OBJ_ID,A.DEP_CCT_ACTION_TYPE,A.DEP_CCT_PACK_TYPE,A.DEP_CCT_COMMAND_TYPE ";
sRequete += "FROM M4CFR_VW_CCT_DEPENDANCES A ";
sRequete += "INNER JOIN M4RDL_PACKAGES B ON (A.DEP_CCT_TASK_ID = B.CCT_TASK_ID) ";
sRequete += "INNER JOIN M4RDL_RAM_PACKS C ON (C.ID_PACKAGE = B.ID_PACKAGE) ";
sRequete += "WHERE A.CCT_TASK_ID IN (" + sFiltreNiveauN + ") ";
sRequete += "AND A.DEP_CCT_TASK_ID NOT IN (" + sFiltreNiveauN + ") ";
if (sFiltreNiveauxPrec != string.Empty)
{
sRequete += "AND A.DEP_CCT_TASK_ID NOT IN (" + sFiltreNiveauxPrec + ") ";
}
if (CCT_OBJECT_TYPE_INT != String.Empty)
{
sRequete += "AND A.CCT_OBJECT_TYPE NOT IN (" + CCT_OBJECT_TYPE_INT + ") ";
}
sRequete += "AND A.DEP_CCT_TASK_ID not like '%DEF%' ";
sRequete += "AND A.CCT_OBJECT_TYPE+A.CCT_OBJECT_ID NOT IN ('PRESENTATIONSFR_DP_PAYROLL_CHANNEL','PRESENTATIONSCO_DP_PAYROLL_CHANNEL') ";
dsDataSet = dmsManagerSQL.GetData(sRequete, ConnectionStringBaseRef);
if ((dsDataSet != null) && (dsDataSet.Tables[0].Rows.Count > 0))
{
using (var conn = new System.Data.SqlClient.SqlConnection(ParamAppli.ConnectionStringBaseAppli))
{
conn.Open();
sRequete = "DELETE FROM PNPU_DEP_REF WHERE ID_H_WORKFLOW = " + Process.WORKFLOW_ID.ToString() + " AND NIV_DEP = " + iNiveau.ToString();
using (var cmd = new System.Data.SqlClient.SqlCommand(sRequete, conn))
{
int rowsAffected = cmd.ExecuteNonQuery();
}
sRequete = "INSERT INTO PNPU_DEP_REF (";
sRequete += "ID_H_WORKFLOW";
sRequete += ",NIV_DEP";
sRequete += ",CCT_TASK_ID";
sRequete += ",CCT_OBJECT_TYPE";
sRequete += ",CCT_OBJECT_ID";
sRequete += ",CCT_PARENT_OBJ_ID";
sRequete += ",DEP_CCT_TASK_ID";
sRequete += ",DEP_CCT_OBJECT_TYPE";
sRequete += ",DEP_CCT_OBJECT_ID";
sRequete += ",DEP_CCT_PARENT_OBJ_ID";
sRequete += ",DEP_CCT_ACTION_TYPE";
sRequete += ",DEP_CCT_PACK_TYPE";
sRequete += ",DEP_CCT_COMMAND_TYPE";
sRequete += ") VALUES (";
sRequete += "@ID_H_WORKFLOW";
sRequete += ",@NIV_DEP";
sRequete += ",@CCT_TASK_ID";
sRequete += ",@CCT_OBJECT_TYPE";
sRequete += ",@CCT_OBJECT_ID";
sRequete += ",@CCT_PARENT_OBJ_ID";
sRequete += ",@DEP_CCT_TASK_ID";
sRequete += ",@DEP_CCT_OBJECT_TYPE";
sRequete += ",@DEP_CCT_OBJECT_ID";
sRequete += ",@DEP_CCT_PARENT_OBJ_ID";
sRequete += ",@DEP_CCT_ACTION_TYPE";
sRequete += ",@DEP_CCT_PACK_TYPE";
sRequete += ",@DEP_CCT_COMMAND_TYPE ";
sRequete += ")";
using (var cmd = new System.Data.SqlClient.SqlCommand(sRequete, conn))
{
cmd.Parameters.Add("@ID_H_WORKFLOW", SqlDbType.Int);
cmd.Parameters.Add("@NIV_DEP", SqlDbType.Int);
cmd.Parameters.Add("@CCT_TASK_ID", SqlDbType.VarChar, 30);
cmd.Parameters.Add("@CCT_OBJECT_TYPE", SqlDbType.VarChar, 30);
cmd.Parameters.Add("@CCT_OBJECT_ID", SqlDbType.VarChar, 255);
cmd.Parameters.Add("@CCT_PARENT_OBJ_ID", SqlDbType.VarChar, 255);
cmd.Parameters.Add("@DEP_CCT_TASK_ID", SqlDbType.VarChar, 30);
cmd.Parameters.Add("@DEP_CCT_OBJECT_TYPE", SqlDbType.VarChar, 30);
cmd.Parameters.Add("@DEP_CCT_OBJECT_ID", SqlDbType.VarChar, 255);
cmd.Parameters.Add("@DEP_CCT_PARENT_OBJ_ID", SqlDbType.VarChar, 255);
cmd.Parameters.Add("@DEP_CCT_ACTION_TYPE", SqlDbType.VarChar, 10);
cmd.Parameters.Add("@DEP_CCT_PACK_TYPE", SqlDbType.VarChar, 255);
cmd.Parameters.Add("@DEP_CCT_COMMAND_TYPE ", SqlDbType.Decimal);
bPremierElement = true;
sFiltreNiveauN1 = string.Empty;
foreach (DataRow drRow in dsDataSet.Tables[0].Rows)
{
sTacheCCT = drRow[4].ToString();
if (lTacheCCT.Contains(sTacheCCT) == false)
{
lTacheCCT.Add(sTacheCCT);
if (bPremierElement == true)
{
bPremierElement = false;
}
else
{
sFiltreNiveauN1 += ",";
}
sFiltreNiveauN1 += "'" + sTacheCCT + "'";
}
cmd.Parameters[0].Value = Process.WORKFLOW_ID;
cmd.Parameters[1].Value = iNiveau;
for (int iCpt = 0; iCpt < 11; iCpt++)
{
cmd.Parameters[iCpt + 2].Value = drRow[iCpt];
}
int rowsAffected = cmd.ExecuteNonQuery();
}
}
}
}
}
}
catch (Exception ex)
{
Logger.Log(Process, this, ParamAppli.StatutError, ex.Message);
bResultat = false;
}
return(bResultat);
}
/// <summary>
/// Méthode effectuant le contrôle.
/// <returns>Retourne un booléen, vrai si le contrôle est concluant et sinon faux.</returns>
/// </summary>
public string MakeControl()
{
string bResultat = ParamAppli.StatutOk;
string sPathMdb = Process.MDBCourant;
string sRequete = string.Empty;
DataSet dsDataSet = null;
List <string[]> lListeITEMS = new List <string[]>();
string sRequeteControle = string.Empty;
bool bPremierElement;
bool bItemTrouve;
DataManagerAccess dmaManagerAccess = null;
DataManagerSQLServer dmsManagerSQL = null;
try
{
if (ConnectionStringBaseRef != string.Empty)
{
dmaManagerAccess = new DataManagerAccess();
sRequeteControle = "SELECT ID_DMD_COMPONENT, ID_DMD_GROUP FROM M4RCH_DMD_GRP_CMP WHERE ID_DMD_GROUP<> 'DMD_INC_VAL_MSS' AND ID_DMD_COMPONENT IN (";
bPremierElement = true;
// Recherche des items de paie livrés
sRequete = "SELECT A.ID_PACKAGE AS ID_PACKAGE, A.ID_OBJECT AS ID_OBJECT, B.ID_DMD_COMPONENT AS ID_DMD_COMPONENT FROM M4RDL_PACK_CMDS A, M4RCH_ITEMS B WHERE A.ID_PACKAGE LIKE '%_L' AND A.ID_CLASS='ITEM' AND (A.ID_OBJECT LIKE '%HRPERIOD_CALC.%' OR A.ID_OBJECT LIKE '%HRROLE_CALC.%') AND A.CMD_ACTIVE = -1 AND B.ID_TI + '.' + B.ID_ITEM = A.ID_OBJECT ";
dsDataSet = dmaManagerAccess.GetData(sRequete, sPathMdb);
if ((dsDataSet != null) && (dsDataSet.Tables[0].Rows.Count > 0))
{
foreach (DataRow drRow in dsDataSet.Tables[0].Rows)
{
if (bPremierElement == true)
{
bPremierElement = false;
}
else
{
sRequeteControle += ",";
}
if (drRow[2].ToString() != string.Empty)
{
sRequeteControle += "'" + drRow[2].ToString() + "'";
}
else
{
sRequeteControle += "'" + drRow[1].ToString().Substring(drRow[1].ToString().LastIndexOf(".") + 1) + "'";
}
bItemTrouve = false;
for (int elt = 0; elt < lListeITEMS.Count && bItemTrouve == false; elt++)
{
if (lListeITEMS[elt][0] == drRow[1].ToString())
{
lListeITEMS[elt][1] += " / " + drRow[0].ToString();
bItemTrouve = true;
}
}
if (bItemTrouve == false)
{
string sDMD_COMPONENT = string.Empty;
if (drRow[2].ToString() != string.Empty)
{
sDMD_COMPONENT = drRow[2].ToString();
}
else
{
sDMD_COMPONENT = drRow[1].ToString().Substring(drRow[1].ToString().LastIndexOf(".") + 1);
}
lListeITEMS.Add(new string[] { drRow[1].ToString(), drRow[0].ToString(), sDMD_COMPONENT, string.Empty, string.Empty });
}
}
dsDataSet.Clear();
}
// Recherche des payroll items livrés
sRequete = "SELECT A.ID_PACKAGE AS ID_PACKAGE, B.ID_TI + '.' + B.ID_ITEM AS ID_OBJECT, B.ID_DMD_COMPONENT AS ID_DMD_COMPONENT FROM M4RDL_PACK_CMDS A, M4RCH_ITEMS B, M4RCH_PICOMPONENTS C WHERE A.ID_PACKAGE LIKE '%_L' AND A.ID_CLASS='PAYROLL ITEM' AND A.CMD_ACTIVE = -1 AND C.ID_T3 + '.' + C.ID_PAYROLL_ITEM = A.ID_OBJECT AND B.ID_TI =C.ID_TI AND B.ID_ITEM=C.ID_ITEM";
dsDataSet = dmaManagerAccess.GetData(sRequete, sPathMdb);
if ((dsDataSet != null) && (dsDataSet.Tables[0].Rows.Count > 0))
{
foreach (DataRow drRow in dsDataSet.Tables[0].Rows)
{
if (bPremierElement == true)
{
bPremierElement = false;
}
else
{
sRequeteControle += ",";
}
if (drRow[2].ToString() != string.Empty)
{
sRequeteControle += "'" + drRow[2].ToString() + "'";
}
else
{
sRequeteControle += "'" + drRow[1].ToString().Substring(drRow[1].ToString().LastIndexOf(".") + 1) + "'";
}
bItemTrouve = false;
for (int elt = 0; elt < lListeITEMS.Count && bItemTrouve == false; elt++)
{
if (lListeITEMS[elt][0] == drRow[1].ToString())
{
lListeITEMS[elt][1] += " / " + drRow[0].ToString();
bItemTrouve = true;
}
}
if (bItemTrouve == false)
{
string sDMD_COMPONENT = string.Empty;
if (drRow[2].ToString() != string.Empty)
{
sDMD_COMPONENT = drRow[2].ToString();
}
else
{
sDMD_COMPONENT = drRow[1].ToString().Substring(drRow[1].ToString().LastIndexOf(".") + 1);
}
lListeITEMS.Add(new string[] { drRow[1].ToString(), drRow[0].ToString(), sDMD_COMPONENT, string.Empty, string.Empty });
}
}
dsDataSet.Clear();
}
// Recherche des niveaux de saisie dans le mdb
if (bPremierElement == false)
{
sRequeteControle += ") ORDER BY ID_DMD_COMPONENT, ID_DMD_GROUP";
dsDataSet = dmaManagerAccess.GetData(sRequeteControle, sPathMdb);
if ((dsDataSet != null) && (dsDataSet.Tables[0].Rows.Count > 0))
{
foreach (DataRow drRow in dsDataSet.Tables[0].Rows)
{
bItemTrouve = false;
for (int elt = 0; elt < lListeITEMS.Count && bItemTrouve == false; elt++)
{
if (lListeITEMS[elt][2] == drRow[0].ToString())
{
if (lListeITEMS[elt][3] == string.Empty)
{
lListeITEMS[elt][3] = "*";
}
lListeITEMS[elt][3] += drRow[1].ToString() + "*";
bItemTrouve = true;
}
}
}
}
// Recherche des niveaux de saisie dans la base de référence ou la base client
dmsManagerSQL = new DataManagerSQLServer();
if (Process.PROCESS_ID == ParamAppli.ProcessControlePacks)
{
dsDataSet = dmsManagerSQL.GetData(sRequeteControle, ConnectionStringBaseRef);
}
else
{
dsDataSet = dmsManagerSQL.GetData(sRequeteControle, ParamAppli.ListeInfoClient[Process.CLIENT_ID].ConnectionStringQA1);
}
if ((dsDataSet != null) && (dsDataSet.Tables[0].Rows.Count > 0))
{
foreach (DataRow drRow in dsDataSet.Tables[0].Rows)
{
bItemTrouve = false;
for (int elt = 0; elt < lListeITEMS.Count && bItemTrouve == false; elt++)
{
if (lListeITEMS[elt][2] == drRow[0].ToString())
{
if (lListeITEMS[elt][4] == string.Empty)
{
lListeITEMS[elt][4] = "*";
}
lListeITEMS[elt][4] += drRow[1].ToString() + "*";
bItemTrouve = true;
}
}
}
}
for (int elt = 0; elt < lListeITEMS.Count; elt++)
{
if ((lListeITEMS[elt][3] != lListeITEMS[elt][4]) && (lListeITEMS[elt][4] != string.Empty))
{
string sListeElement = VerifieListe(lListeITEMS[elt][4], lListeITEMS[elt][3]);
if (sListeElement != string.Empty)
{
if (sListeElement.IndexOf(",") > -1)
{
Process.AjouteRapport("Perte des niveaux de saisie " + sListeElement + " pour l'item " + lListeITEMS[elt][0] + " (DMD_COMPONENT " + lListeITEMS[elt][2] + ") livré dans le(s) pack(s) " + lListeITEMS[elt][1]);
}
else
{
Process.AjouteRapport("Perte du niveau de saisie " + sListeElement + " pour l'item " + lListeITEMS[elt][0] + " (DMD_COMPONENT " + lListeITEMS[elt][2] + ") livré dans le(s) pack(s) " + lListeITEMS[elt][1]);
}
bResultat = ResultatErreur;
}
}
}
}
}
}
catch (Exception ex)
{
Logger.Log(Process, this, ParamAppli.StatutError, ex.Message);
bResultat = ParamAppli.StatutError;
}
return(bResultat);
}
/// <summary>
/// Méthode effectuant le contrôle.
/// <returns>Retourne un booléen, vrai si le contrôle est concluant et sinon faux.</returns>
/// </summary>
public string MakeControl()
{
string bResultat = ParamAppli.StatutOk;
string sPathMdb = Process.MDBCourant;
string sID_SYNONYM;
Dictionary <string, string> dicListItems = new Dictionary <string, string>();
bool bItemAControler = false;
string sRequeteSqlServer = string.Empty;
DataManagerAccess dmaManagerAccess = null;
try
{
dmaManagerAccess = new DataManagerAccess();
DataSet dsDataSet = dmaManagerAccess.GetData("select ID_ITEM, ID_SYNONYM FROM M4RCH_ITEMS WHERE (ID_TI LIKE '%HRPERIOD%CALC' OR ID_TI LIKE '%HRROLE%CALC') AND ID_TI NOT LIKE '%DIF%' AND ID_SYNONYM <> 0", sPathMdb);
if ((dsDataSet != null) && (dsDataSet.Tables[0].Rows.Count > 0))
{
sRequeteSqlServer = "select ID_ITEM, ID_SYNONYM FROM M4RCH_ITEMS WHERE (ID_TI LIKE '%HRPERIOD%CALC' OR ID_TI LIKE '%HRROLE%CALC') ";
// Ne faire que si pack standard
sRequeteSqlServer += "AND(ID_TI LIKE 'SCO%' OR ID_TI LIKE 'SFR%' OR ID_TI LIKE 'CFR%') ";
sRequeteSqlServer += "AND ID_TI NOT LIKE '%DIF%' AND (";
foreach (DataRow drRow in dsDataSet.Tables[0].Rows)
{
sID_SYNONYM = drRow[1].ToString();
if (dicListItems.ContainsKey(sID_SYNONYM) == false)
{
dicListItems.Add(sID_SYNONYM, drRow[0].ToString());
if (bItemAControler == false)
{
bItemAControler = true;
}
else
{
sRequeteSqlServer += "OR ";
}
sRequeteSqlServer += " (ID_SYNONYM = " + drRow[1].ToString() + " AND ID_ITEM <> '" + drRow[0].ToString() + "') ";
}
}
if (bItemAControler == true)
{
sRequeteSqlServer += ")";
DataManagerSQLServer dmasqlManagerSQL = new DataManagerSQLServer();
// Contrôle sur la base de référence si pack standard, sinon sur base client
if (Process.STANDARD == true)
{
dsDataSet = dmasqlManagerSQL.GetData(sRequeteSqlServer, ParamAppli.ConnectionStringBaseRef[Process.TYPOLOGY]);
}
else
{
dsDataSet = dmasqlManagerSQL.GetData(sRequeteSqlServer, ParamAppli.ListeInfoClient[Process.CLIENT_ID].ConnectionStringQA1);
}
if ((dsDataSet != null) && (dsDataSet.Tables[0].Rows.Count > 0))
{
bResultat = ResultatErreur;
foreach (DataRow drRow in dsDataSet.Tables[0].Rows)
{
Process.AjouteRapport("L'ID_SYNONYM de l'item " + dicListItems[drRow[1].ToString()] + "(" + drRow[1].ToString() + ") est déja utilisé pour l'item " + drRow[0].ToString() + ".");
}
}
}
}
}
catch (Exception ex)
{
Logger.Log(Process, this, ParamAppli.StatutError, ex.Message);
bResultat = ParamAppli.StatutError;
}
return(bResultat);
}
/// <summary>
/// Méthode vérifiant que les M4O modifiés par un pack ne sont pas des objets technos.
/// <returns>Retourne un booléen, vrai si le contrôle est concluant et sinon faux.</returns>
/// <param name="lListeM4O">Contient la liste des M4O livrés et des M4O des NODES livrés.</param>
/// <param name="lListeNODESTRUCTURE">Contient la liste des NODE STRUCTURES livrées et des NODES STRUCTURES des ITEM livrés.</param>
/// <param name="sIDPackageCourant">Contient le nom du pack courant.</param>
/// </summary>
private bool ControleM4OModifiesPack(List <string> lListeM4O, List <string> lListeNODESTRUCTURE, string sIDPackageCourant)
{
DataManagerAccess dmaManagerAccess = null;
DataManagerSQLServer dmsManagerSQL = null;
bool bPremier = true;
string sRequete = string.Empty;
string sPathMdb = Process.MDBCourant;
DataSet dsDataSet = null;
bool bResultat = true;
try
{
dmsManagerSQL = new DataManagerSQLServer();
dmaManagerAccess = new DataManagerAccess();
// Si la liste n'est pas vide on va rechercher les M4O à partir des NODE STRUCTURES modifiées.
if (lListeNODESTRUCTURE.Count > 0)
{
sRequete = "select ID_T3,ID_TI from M4RCH_NODES where ID_TI IN (";
bPremier = true;
foreach (string s in lListeNODESTRUCTURE)
{
if (bPremier == true)
{
bPremier = false;
}
else
{
sRequete += ",";
}
sRequete += "'" + s + "'";
}
sRequete += ") UNION select ID_NODE_T3 AS ID_T3,ID_TI from M4RCH_OVERWRITE_NO where ID_TI IN (";
bPremier = true;
foreach (string s in lListeNODESTRUCTURE)
{
if (bPremier == true)
{
bPremier = false;
}
else
{
sRequete += ",";
}
sRequete += "'" + s + "'";
}
sRequete += ")";
dsDataSet = dmaManagerAccess.GetData(sRequete, sPathMdb);
if ((dsDataSet != null) && (dsDataSet.Tables[0].Rows.Count > 0))
{
foreach (DataRow drRow in dsDataSet.Tables[0].Rows)
{
lListeNODESTRUCTURE.Remove(drRow[1].ToString()); // Je supprime les NS dont on trouve le M4O dans le MDB
if (lListeM4O.Contains(drRow[0].ToString()) == false)
{
lListeM4O.Add(drRow[0].ToString());
}
}
dsDataSet.Clear();
}
// S'il reste des NODE STRUCTURES pour lesquelles nous n'avons pas trouvé le M4O dans le MDB on recherche dans la base de ref.
if (lListeNODESTRUCTURE.Count > 0)
{
sRequete = "select ID_T3,ID_TI from M4RCH_NODES where ID_TI IN (";
bPremier = true;
foreach (string s in lListeNODESTRUCTURE)
{
if (bPremier == true)
{
bPremier = false;
}
else
{
sRequete += ",";
}
sRequete += "'" + s + "'";
}
sRequete += ") UNION select ID_NODE_T3 AS ID_T3,ID_TI from M4RCH_OVERWRITE_NO where ID_TI IN (";
bPremier = true;
foreach (string s in lListeNODESTRUCTURE)
{
if (bPremier == true)
{
bPremier = false;
}
else
{
sRequete += ",";
}
sRequete += "'" + s + "'";
}
sRequete += ")";
if (ConnectionStringBaseRef != string.Empty)
{
dsDataSet = dmsManagerSQL.GetData(sRequete, ConnectionStringBaseRef);
if ((dsDataSet != null) && (dsDataSet.Tables[0].Rows.Count > 0))
{
foreach (DataRow drRow in dsDataSet.Tables[0].Rows)
{
lListeNODESTRUCTURE.Remove(drRow[1].ToString()); // Je supprime les NS dont on trouve le M4O dans le MDB
if (lListeM4O.Contains(drRow[0].ToString()) == false)
{
lListeM4O.Add(drRow[0].ToString());
}
}
dsDataSet.Clear();
}
}
}
}
// On va rechercher les M4O de la liste pour vérifier si ce sont des objets technos.
// On cherche déja dans le MDB
if (lListeM4O.Count > 0)
{
sRequete = "select ID_T3, N_T3FRA, OWNER_FLAG from M4RCH_T3S where ID_T3 IN (";
bPremier = true;
foreach (string s in lListeM4O)
{
if (bPremier == true)
{
bPremier = false;
}
else
{
sRequete += ",";
}
sRequete += "'" + s + "'";
}
sRequete += ")";
dsDataSet = dmaManagerAccess.GetData(sRequete, sPathMdb);
if ((dsDataSet != null) && (dsDataSet.Tables[0].Rows.Count > 0))
{
foreach (DataRow drRow in dsDataSet.Tables[0].Rows)
{
lListeM4O.Remove(drRow[0].ToString()); // Je supprime les M4O trouvés dans le MDB
// Controle si c'est un objet techno
if ((drRow[2].ToString() == "1") || (drRow[0].ToString().Substring(0, 4) == "SRTC"))
{
Process.AjouteRapport("Modification de l'objet techno " + drRow[0].ToString() + " dans le pack " + sIDPackageCourant + ".");
bResultat = false;
}
}
}
// S'il reste des M4O non trouvés dans le MDB on va chercher dans la base de ref.
if (lListeM4O.Count > 0)
{
sRequete = "select ID_T3, N_T3FRA, OWNER_FLAG from M4RCH_T3S where ID_T3 IN (";
bPremier = true;
foreach (string s in lListeM4O)
{
if (bPremier == true)
{
bPremier = false;
}
else
{
sRequete += ",";
}
sRequete += "'" + s + "'";
}
sRequete += ")";
if (ConnectionStringBaseRef != string.Empty)
{
dsDataSet = dmsManagerSQL.GetData(sRequete, ConnectionStringBaseRef);
if ((dsDataSet != null) && (dsDataSet.Tables[0].Rows.Count > 0))
{
foreach (DataRow drRow in dsDataSet.Tables[0].Rows)
{
lListeM4O.Remove(drRow[0].ToString()); // Je supprime les M4O trouvés dans le MDB
// Controle si c'est un objet techno
if ((drRow[2].ToString() == "1") || (drRow[0].ToString().Substring(0, 4) == "SRTC"))
{
Process.AjouteRapport("Modification de l'objet techno " + drRow[0].ToString() + " dans le pack " + sIDPackageCourant + ".");
bResultat = false;
}
}
}
}
}
}
}
catch (Exception ex)
{
// TODO, loguer l'exception
bResultat = true;
}
return(bResultat);
}
/// <summary>
/// Méthode effectuant le contrôle.
/// <returns>Retourne un booléen, vrai si le contrôle est concluant et sinon faux.</returns>
/// </summary>
public string MakeControl()
{
string bResultat = ParamAppli.StatutOk;
string sPathMdb = Process.MDBCourant;
string sRequete;
List <string[]> lListeAControler = new List <string[]>();
bool bPremierElement = true;
string sItem;
DataSet dsDataSet;
string sListeItemsLivres = string.Empty;
DataManagerAccess dmaManagerAccess = null;
try
{
dmaManagerAccess = new DataManagerAccess();
// Recherche des items de paie livrés dans les packs du mdb
sRequete = "SELECT ID_OBJECT FROM M4RDL_PACK_CMDS WHERE ID_CLASS = 'ITEM' AND CMD_ACTIVE=-1 AND ID_OBJECT LIKE '%HR%CALC%' AND ID_OBJECT NOT LIKE '%DIF%'";
dsDataSet = dmaManagerAccess.GetData(sRequete, sPathMdb);
if ((dsDataSet != null) && (dsDataSet.Tables[0].Rows.Count > 0))
{
foreach (DataRow drRow in dsDataSet.Tables[0].Rows)
{
if (sListeItemsLivres != string.Empty)
{
sListeItemsLivres += ",";
}
sListeItemsLivres += "'" + drRow[0].ToString() + "'";
}
}
sRequete = "select ID_TI, ID_ITEM, ID_ITEM_USED_TI, ID_ITEM_USED FROM M4RCH_TOTAL_REF A";
// On livre le total
sRequete += " WHERE (A.ID_TI+'.'+A.ID_ITEM IN (" + sListeItemsLivres + ")) ";
sRequete += " AND (A.ID_ITEM_USED_TI+'.'+ID_ITEM_USED NOT IN (" + sListeItemsLivres + ")) ";
// Et il existe des items du total qu'on ne livre pas
sRequete += " AND (EXISTS (SELECT * FROM M4RCH_TOTAL_REF C WHERE A.ID_TI=C.ID_TI AND A.ID_ITEM=C.ID_ITEM AND C.ID_ITEM_USED_TI+'.'+C.ID_ITEM_USED NOT IN (" + sListeItemsLivres + ")))";
dsDataSet = dmaManagerAccess.GetData(sRequete, sPathMdb);
if ((dsDataSet != null) && (dsDataSet.Tables[0].Rows.Count > 0))
{
sRequete = "SELECT ID_TI + '.' + ID_ITEM FROM M4RCH_ITEMS WHERE ID_TI + '.' + ID_ITEM IN (";
// Recherche les items utilisés dans les totaux
foreach (DataRow drRow in dsDataSet.Tables[0].Rows)
{
sItem = drRow[2].ToString() + "." + drRow[3].ToString();
lListeAControler.Add(new string[] { sItem, drRow[0].ToString() + "." + drRow[1].ToString() });
if (bPremierElement == true)
{
bPremierElement = false;
}
else
{
sRequete += ",";
}
sRequete += "'" + sItem + "'";
}
sRequete += ")";
}
// Recherche des items sur la base de ref
if (lListeAControler.Count > 0)
{
DataManagerSQLServer dmsManagerSQL = new DataManagerSQLServer();
dsDataSet = dmsManagerSQL.GetData(sRequete, ConnectionStringBaseRef);
if ((dsDataSet != null) && (dsDataSet.Tables[0].Rows.Count > 0))
{
foreach (DataRow drRow in dsDataSet.Tables[0].Rows)
{
for (int iCpt = 0; iCpt < lListeAControler.Count; iCpt++)
{
if (lListeAControler[iCpt][0] == drRow[0].ToString())
{
lListeAControler.RemoveAt(iCpt);
iCpt--;
}
}
}
}
if (lListeAControler.Count > 0)
{
bResultat = ResultatErreur;
foreach (string[] sElements in lListeAControler)
{
Process.AjouteRapport("Le total " + sElements[1] + " utilise un item inexistant (" + sElements[0] + ").");
}
}
}
}
catch (Exception ex)
{
Logger.Log(Process, this, ParamAppli.StatutError, ex.Message);
bResultat = ParamAppli.StatutError;
}
return(bResultat);
}
