public static RetourIndexationDocument Suprimer(IndexationDocument indexDoc)
{
SqlConnection con = null;
string strSQL = "";
SqlCommand cmd = null;
RetourIndexationDocument retour = new RetourIndexationDocument();
try
{
retour = new RetourIndexationDocument();
con = new SqlConnection(ConfigurationManager.ConnectionStrings["dsnGED"].ConnectionString);
con.Open();
//Trace
strSQL = @"INSERT INTO [TraceIndexation] ([message],[date]) VALUES(@message,@date)";
cmd = new SqlCommand(strSQL, con);
cmd.Parameters.AddWithValue("@message", (object)("Delete:" + indexDoc.ID_DocumentNortia + ";" + indexDoc.ID_DocumentSalesForce + ";" + indexDoc.TypeDocument + ";" + indexDoc.ID_PliNortia + ";" + indexDoc.ID_PliSalesForce + ";" + indexDoc.TypeActe + ";" + indexDoc.TypeObjetAvenant + ";" + indexDoc.VisibleNOL));
cmd.Parameters.AddWithValue("@date", (object)DateTime.Now);
cmd.ExecuteNonQuery();
strSQL = @"UPDATE [CA_MEDIA] SET VisibleNOL=0,TEK_DateSuppressionSF=@DateSup WHERE pk = @ID_Doc;";
cmd = new SqlCommand(strSQL, con);
cmd.Parameters.AddWithValue("@ID_Doc", (object)indexDoc.ID_DocumentNortia ?? DBNull.Value);
cmd.Parameters.AddWithValue("@DateSup", (object)DateTime.Now);
int nbLigne = cmd.ExecuteNonQuery();
if (nbLigne != 0)
{
retour.CodeRetour = 0;
retour.Message = "Success Delete";
}
else
{
retour.CodeRetour = 2;
retour.Message = "L'identifiant Nortia \"" + indexDoc.ID_DocumentNortia.ToString() + "\" du Document n'existe pas dans la GED";
}
}
catch (Exception ex)
{
retour.CodeRetour = 1;
retour.Message = ex.Message;
}
finally
{
if (con != null && con.State == ConnectionState.Open)
{
con.Close();
}
}
return(retour);
}
public static RetourIndexationDocument Indexer(IndexationDocument indexDoc, string entite = "NSAS")
{
SqlConnection con = null;
string strSQL = "";
SqlCommand cmd = null;
RetourIndexationDocument retour = new RetourIndexationDocument();
try
{
retour = new RetourIndexationDocument();
//Trace
string message = "ID_Doc: " + indexDoc.ID_DocumentNortia + "; ID_DocSF: " + indexDoc.ID_DocumentSalesForce + "; TypeDoc: " + indexDoc.TypeDocument + "; ID_Pli: " + indexDoc.ID_PliNortia + "; ID_PliSF: " + indexDoc.ID_PliSalesForce + "; TypeActe: " + indexDoc.TypeActe + "; ObjetSF: " + indexDoc.TypeObjetAvenant + "; VisibleNOL: " + indexDoc.VisibleNOL;
TraceIndexation(message, "Indexation Update", entite);
if (entite == "NSAS")
{
con = new SqlConnection(ConfigurationManager.ConnectionStrings["dsnGED"].ConnectionString);
}
else
{
con = new SqlConnection(ConfigurationManager.ConnectionStrings["dsnGED_NI"].ConnectionString);
}
strSQL = "SELECT count(*) FROM CA_MEDIA WHERE pk = @ID_Doc AND ID_Pli=@ID_Pli";
cmd = new SqlCommand(strSQL, con);
cmd.Parameters.AddWithValue("@ID_Pli", (object)indexDoc.ID_PliNortia ?? DBNull.Value);
cmd.Parameters.AddWithValue("@ID_Doc", (object)indexDoc.ID_DocumentNortia ?? DBNull.Value);
con.Open();
int nbLigne = (int)cmd.ExecuteScalar();
if (nbLigne != 0)
{
//indexDoc.ID_PliNortia=0: Document Acte, sinon Document Avenant
if (indexDoc.ID_PliNortia != 0)
{
int idTypeActe = TypeDocument.GetIDTypeActe(indexDoc.TypeActe, entite);
strSQL = "UPDATE Pli SET ID_Type_Acte=@ID_Type_Acte, CleSalesForce = @CleSalesForce where ID_Pli = @ID_Pli";
cmd = new SqlCommand(strSQL, con);
cmd.Parameters.AddWithValue("@ID_Type_Acte", (object)idTypeActe ?? DBNull.Value);
cmd.Parameters.AddWithValue("@CleSalesForce", (object)indexDoc.ID_PliSalesForce ?? DBNull.Value);
cmd.Parameters.AddWithValue("@ID_Pli", (object)indexDoc.ID_PliNortia ?? DBNull.Value);
nbLigne = cmd.ExecuteNonQuery();
}
else
{
strSQL = "UPDATE Avenant SET Objet_SF=@Objet_SF, CleSalesForce = @CleSalesForce where ID_Document = @ID_Document";
cmd = new SqlCommand(strSQL, con);
cmd.Parameters.AddWithValue("@Objet_SF", (object)indexDoc.TypeObjetAvenant ?? DBNull.Value);
cmd.Parameters.AddWithValue("@CleSalesForce", (object)indexDoc.ID_PliSalesForce ?? DBNull.Value);
cmd.Parameters.AddWithValue("@ID_Document", (object)indexDoc.ID_DocumentNortia ?? DBNull.Value);
nbLigne = cmd.ExecuteNonQuery();
}
//if ((indexDoc.ID_PliNortia != 0 && nbLigne != 0) || indexDoc.ID_PliNortia == 0)
if (nbLigne != 0)
{
int idTypeDoc = TypeDocument.GetIDTypeDocument(indexDoc.TypeDocument, entite);
if (idTypeDoc > 0)
{
if (entite == "NI")
{
strSQL = "UPDATE CA_MEDIA SET VisibleNOL=@VisibleNOL,ID_Type_Document=@ID_Type_Document,TEK_DateSuppressionSF=null where pk = @ID_Doc;";
cmd = new SqlCommand(strSQL, con);
cmd.Parameters.AddWithValue("@VisibleNOL", (object)indexDoc.VisibleNOL ?? DBNull.Value);
cmd.Parameters.AddWithValue("@ID_Type_Document", (object)idTypeDoc ?? DBNull.Value);
cmd.Parameters.AddWithValue("@ID_Doc", (object)indexDoc.ID_DocumentNortia ?? DBNull.Value);
nbLigne = cmd.ExecuteNonQuery();
strSQL = "SELECT Count(*) FROM IndexationDocument WHERE ID_Document=@ID_Document AND Applicatif='Salesforce' and Objet=@TypeObjetAvenant and Objet_ID=@ID_PliSalesForce";
cmd = new SqlCommand(strSQL, con);
cmd.Parameters.AddWithValue("@ID_Document", (object)indexDoc.ID_DocumentNortia);
cmd.Parameters.AddWithValue("@TypeObjetAvenant", (object)indexDoc.TypeObjetAvenant);
cmd.Parameters.AddWithValue("@ID_PliSalesForce", (object)indexDoc.ID_PliSalesForce);
int nb = (int)cmd.ExecuteScalar();
if (nb > 0)
{
strSQL = "UPDATE IndexationDocument SET CleExterne=@ID_DocumentSalesForce WHERE ID_Document=@ID_Document AND Applicatif='Salesforce' and Objet=@TypeObjetAvenant and Objet_ID=@ID_PliSalesForce";
}
else
{
strSQL = "INSERT INTO IndexationDocument(ID_Document,Applicatif,Objet,Objet_ID,CleExterne) VALUES (@ID_Document,'Salesforce',@TypeObjetAvenant,@ID_PliSalesForce,@ID_DocumentSalesForce)";
}
cmd = new SqlCommand(strSQL, con);
cmd.Parameters.AddWithValue("@ID_PliSalesForce", (object)indexDoc.ID_PliSalesForce ?? DBNull.Value);
cmd.Parameters.AddWithValue("@ID_Document", (object)indexDoc.ID_DocumentNortia);
cmd.Parameters.AddWithValue("@TypeObjetAvenant", (object)indexDoc.TypeObjetAvenant);
cmd.Parameters.AddWithValue("@ID_DocumentSalesForce", (object)indexDoc.ID_DocumentSalesForce);
cmd.ExecuteNonQuery();
}
else if (entite == "NSAS")
{
string nomTable = "";
int idCA = 0;
if (indexDoc.TypeObjetAvenant == "Contrat")
{
nomTable = "ca_contrat";
idCA = GetID_Contrat(indexDoc.ID_PliSalesForce);
}
else
{
int idTypeActe = TypeDocument.GetIDTypeActe(indexDoc.TypeActe);
nomTable = GetTableCA(idTypeDoc, idTypeActe);
if (nomTable != null && nomTable != "")
{
idCA = GetID_Acte(nomTable, indexDoc.ID_PliSalesForce);
}
}
//strSQL = "UPDATE CA_MEDIA SET CleSalesForce = @CleSalesForce, Original=@Original, VisibleNOL=@VisibleNOL,ID_Type_Document=@ID_Type_Document,pkvalue=@pkvalue,[table]=@table where pk = @ID_Doc;";
strSQL = "UPDATE CA_MEDIA SET CleSalesForce = @CleSalesForce, VisibleNOL=@VisibleNOL,ID_Type_Document=@ID_Type_Document,pkvalue=@pkvalue,[table]=@table,TEK_DateSuppressionSF=null where pk = @ID_Doc;";
cmd = new SqlCommand(strSQL, con);
cmd.Parameters.AddWithValue("@CleSalesForce", (object)indexDoc.ID_DocumentSalesForce ?? DBNull.Value);
cmd.Parameters.AddWithValue("@VisibleNOL", (object)indexDoc.VisibleNOL ?? DBNull.Value);
//cmd.Parameters.AddWithValue("@Original", (object)indexDoc.Original ?? DBNull.Value);
cmd.Parameters.AddWithValue("@ID_Type_Document", (object)idTypeDoc ?? DBNull.Value);
if (nomTable == null || nomTable.Trim() == "" || idCA == 0)
{
cmd.Parameters.AddWithValue("@pkvalue", DBNull.Value);
cmd.Parameters.AddWithValue("@table", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@pkvalue", (object)idCA);
cmd.Parameters.AddWithValue("@table", (object)nomTable);
}
cmd.Parameters.AddWithValue("@ID_Doc", (object)indexDoc.ID_DocumentNortia ?? DBNull.Value);
nbLigne = cmd.ExecuteNonQuery();
if (nbLigne != 0)
{
try
{
int ID_TypeDoc_CA = 0;
if (indexDoc.TypeObjetAvenant == "Contrat")
{
ID_TypeDoc_CA = GetTypeDocCA(idTypeDoc);
}
else
{
int idTypeActe = TypeDocument.GetIDTypeActe(indexDoc.TypeActe);
ID_TypeDoc_CA = GetTypeDocCA(idTypeDoc, idTypeActe);
}
if (ID_TypeDoc_CA > 0)
{
if (con != null && con.State == ConnectionState.Open)
{
con.Close();
}
con = new SqlConnection(ConfigurationManager.ConnectionStrings["dsnNortiaCA"].ConnectionString);
strSQL = @"SELECT count(*) FROM [CA_TYPEDOCS_MEDIA] WHERE [fkmedia]= @fkmedia;";
con.Open();
cmd = new SqlCommand(strSQL, con);
cmd.Parameters.AddWithValue("@fkmedia", (object)indexDoc.ID_DocumentNortia);
int nb = (int)cmd.ExecuteScalar();
if (nb > 0)
{
strSQL = @"UPDATE [CA_TYPEDOCS_MEDIA] set [fktypedocs]=@fktypedocs WHERE [fkmedia]= @fkmedia;";
cmd = new SqlCommand(strSQL, con);
cmd.Parameters.AddWithValue("@fktypedocs", (object)ID_TypeDoc_CA ?? DBNull.Value);
cmd.Parameters.AddWithValue("@fkmedia", (object)indexDoc.ID_DocumentNortia);
}
else
{
strSQL = @"INSERT INTO [CA_TYPEDOCS_MEDIA] ([fktypedocs],[fkmedia],[date]) VALUES(@fktypedocs,@fkmedia,@date)";
cmd = new SqlCommand(strSQL, con);
cmd.Parameters.AddWithValue("@fktypedocs", (object)ID_TypeDoc_CA ?? DBNull.Value);
cmd.Parameters.AddWithValue("@fkmedia", (object)indexDoc.ID_DocumentNortia ?? DBNull.Value);
cmd.Parameters.AddWithValue("@date", (object)DateTime.Now);
}
cmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
retour.CodeRetour = 0;
retour.Message = "Success Indexation";
}
else
{
retour.CodeRetour = 5;
retour.Message = "L'identifiant Nortia \"" + indexDoc.ID_DocumentNortia.ToString() + "\" du Document n'existe pas dans la GED";
}
}
}
else
{
retour.CodeRetour = 4;
retour.Message = "Le type de document spécifié \"" + indexDoc.TypeDocument + "\" du Document n'existe pas dans la GED";
}
}
else
{
retour.CodeRetour = 3;
retour.Message = "L'identifiant Nortia \"" + indexDoc.ID_DocumentNortia.ToString() + "\" du Document n'est associé à aucun Pli";
}
}
else
{
retour.CodeRetour = 2;
retour.Message = "L'identifiant du Pli Nortia \"" + indexDoc.ID_PliNortia.ToString() + "\" et l'identifiant du Document Nortia \"" + indexDoc.ID_DocumentNortia.ToString() + "\" ne coresponde pas dans la GED";
}
}
catch (Exception ex)
{
retour.CodeRetour = 1;
retour.Message = ex.Message;
}
finally
{
if (con != null && con.State == ConnectionState.Open)
{
con.Close();
}
}
if (retour.CodeRetour > 0)
{
string message = retour.Message + " - Valeurs: ID_Doc: " + indexDoc.ID_DocumentNortia + "; ID_DocSF: " + indexDoc.ID_DocumentSalesForce + "; TypeDoc: " + indexDoc.TypeDocument + "; ID_Pli: " + indexDoc.ID_PliNortia + "; ID_PliSF: " + indexDoc.ID_PliSalesForce + "; TypeActe: " + indexDoc.TypeActe + "; ObjetSF: " + indexDoc.TypeObjetAvenant + "; VisibleNOL" + indexDoc.VisibleNOL;
TraceIndexation(message, "Indexation Erreur", entite);
}
return(retour);
}
