public static Boolean ajouterCourrier(CourrierArriveInterne c)
{
try
{
SqlConnection cnx = new SqlConnection(ConfigurationManager.AppSettings["cnx"].ToString());
cnx.Open();
SqlTransaction tran = cnx.BeginTransaction("ajout");
string requete1 = String.Format("Insert into courrier values('{0}','{1}','{2}','{3}','{4}','Cree',{5},{6},{7},null,{8})", c.getReference(), c.getTypecr(), c.getNature(), c.getDate_creation(), c.getObjet(), c.getAgentBO().getId(), c.getAgentUA().getId(), c.getReponse() ? 1 : 0, (c.getDossier() != null) ? "'" + c.getDossier().getReference() + "'" : "null");
//, (c.getDossier()!=null)?c.getDossier().getReference():null
Console.WriteLine(requete1);
SqlCommand cmd = new SqlCommand(requete1, cnx);
cmd.Transaction = tran;
int n = cmd.ExecuteNonQuery();
if (n != 0)
{
cmd = new SqlCommand(String.Format("Insert into courrier_arrive_interne values('{0}','{1}','{2}','{3}',(select id_contact from contacts where nom='{4}'))", c.getReference(), c.getDate_Courrier(), c.getDate_Arrivee(), c.getType(), c.getExpediteur().getNom()), cnx);
cmd.Transaction = tran;
n = cmd.ExecuteNonQuery();
if (n != 0 && (c is Facture))
{
cmd = new SqlCommand(String.Format("Insert into facture values ('{0}','{1}',{2})", c.getReference(), ((Facture)c).getDevice(), (((Facture)c).getMontant() + "").Replace(",", ".")), cnx);
cmd.Transaction = tran;
n = cmd.ExecuteNonQuery();
if (n != 0)
{
tran.Commit();
return(true);
}
else
{
tran.Rollback();
return(false);
}
}
else if (n != 0)
{
tran.Commit();
return(true);
}
else
{
tran.Rollback();
return(false);
}
}
else
{
tran.Rollback();
return(false);
}
}catch (Exception ex)
{
Console.WriteLine(ex.Message);
return(false);
}
}
public static CourrierArriveInterne getCourrierByRef(string ref_cour)
{
SqlConnection cnx = new SqlConnection(ConfigurationManager.AppSettings["cnx"].ToString());
cnx.Open();
SqlCommand cmd = new SqlCommand(String.Format("select cou.ref_cour,f.ref_cour,cou.type,nature,date_creation,objet,etat,reponse,courrier_reponse,ref_dos,c.nom,device,montant,ag.nom,ag.prenom,un.nom,date_cour,date_arrivee,cou.id_agent_ua from courrier cou,contacts c,agent_ua ag,unites un,courrier_arrive_interne cai LEFT OUTER JOIN facture f ON cai.ref_cour=f.ref_cour where cou.ref_cour=cai.ref_cour and cai.id_contact=c.id_contact and cou.id_agent_ua=ag.id_agent and ag.id_unit=un.id_unit and cou.ref_cour = '{0}'", ref_cour), cnx);
SqlDataReader dr = cmd.ExecuteReader();
CourrierArriveInterne c = new CourrierArriveInterne();
if (dr != null && dr.HasRows)
{
if (dr.Read())
{
if (!dr.IsDBNull(1))
{
c = new Facture();
}
else
{
c = new CourrierArriveInterne();
}
c.setReference(dr.GetString(0));
c.setTypecr(dr.GetString(2));
c.setNature(dr.GetString(3));
c.setDate_creation(dr.GetDateTime(4));
c.setObjet(dr.GetString(5));
c.setEtat(dr.GetString(6));
c.setReponse(dr.GetBoolean(7));
c.setCourrier_Reponse(!dr.IsDBNull(8) ? new Courrier(dr.GetString(8)) : null);
c.setDossier((!dr.IsDBNull(9) ? new Dossier(dr.GetString(9)) : null));
c.setExpediteur(new Contact(dr.GetString(10)));
if (c is Facture)
{
((Facture)c).setDevice(dr.GetString(11));
((Facture)c).setMontant((float)dr.GetSqlDecimal(12).Value);
}
c.setAgentUA(new AgentUA(dr.GetInt32(18), dr.GetString(13), dr.GetString(14), new Unite(dr.GetString(15))));
c.setDate_Courrier(dr.GetDateTime(16));
c.setDate_Arrivee(dr.GetDateTime(17));
}
cnx.Close();
dr.Close();
return(c);
}
return(null);
}
protected void Page_Load(object sender, EventArgs e)
{
if (Request.QueryString["ref"] != null)
{
List <Contact> destin = ContactService.getContacts();
id_destinataire.Items.Clear();
id_destinataire.Items.AddRange(destin.Select(ex => new ListItem()
{
Text = ex.getNom(),
Value = ex.getId() + ""
}).ToArray());
List <Unite> l = UniteService.getUnites();
unit.Items.Clear();
unit.Items.Add(new ListItem()
{
Text = "Choisissez une unité",
Value = "0"
});
unit.Items.AddRange(l.Select(u => new ListItem()
{
Text = u.getNom(),
Value = u.getId() + ""
}).ToArray());
unit_copie.Items.Clear();
unit_copie.Items.AddRange(l.Select(u => new ListItem()
{
Text = u.getNom(),
Value = u.getId() + ""
}).ToArray());
CourrierArriveInterne c = CourrierService.getCourrierByRef(Request.QueryString["ref"]);
cour = c;
if (c != null)
{
ref_cour.Text = c.getReference();
reference.Text = c.getReference();
type.Text = c.getTypecr();
nature.Text = c.getNature();
datecrea.Text = c.getDate_creation().ToString().Substring(0, 10);
objet.Text = c.getObjet();
agentua.Text = c.getAgentUA().getNom() + " " + c.getAgentUA().getPrenom();
uniteua.Text = c.getAgentUA().getUnite().getNom();
reponse.Text = c.getReponse() ? "Oui" : "Non";
courrier_reponse.Text = (c.getCourrier_Reponse() != null) ? c.getCourrier_Reponse().getReference() : "Pas de réponse";
ref_dos.Text = (c.getDossier() != null) ? c.getDossier().getReference() : "Sans dossier";
datecour.Text = c.getDate_Courrier().ToString().Substring(0, 10);
datearr.Text = c.getDate_Arrivee().ToString().Substring(0, 10);
expediteur.Text = c.getExpediteur().getNom();
if (c is Facture)
{
facture.Text = "";
device.Text = ((Facture)c).getDevice();
montant.Text = ((Facture)c).getMontant().ToString();
}
else
{
facture.Text = "Non";
device.Text = " - ";
montant.Text = " - ";
}
etat.Text = c.getEtat();
}
}
}
protected void btn_ajout_cr_Click(object sender, EventArgs e)
{
CourrierArriveInterne c;
if (fact.Checked)
{
c = new Facture();
}
else
{
c = new CourrierArriveInterne();
}
//Response.Write(opt_dos.Checked?"Dossier : " +id_ref_dos.Value:"Pas de dossier ");
if (opt_dos.Checked)
{
c.setDossier(new Dossier(id_ref_dos.Value));
}
//Response.Write("Réference du courrier: " + ref_cour.Text.ToString());
c.setReference(ref_cour.Text.ToString());
//Response.Write("Expediteur : " + id_expediteur.Value);
c.setExpediteur(new Contact(id_expediteur.Value));
//Response.Write("type : " + type.Text.ToString()+" ");
c.setTypecr(type.Text.ToString());
//Response.Write("nature : "+nature.Text.ToString() + " ");
c.setNature(nature.Text.ToString());
//Response.Write("obet : " +objetcr.Text.ToString() + " ");
c.setObjet(objetcr.Text.ToString());
//Response.Write("date creation : " +datecrea.Text.ToString() + " ");
c.setDate_creation(DateTime.ParseExact(datecrea.Text.ToString(), "MM/dd/yyyy", System.Globalization.CultureInfo.InvariantCulture));
//Response.Write("date courrier : "+datecour.Text.ToString() + " ");
c.setDate_Courrier(DateTime.ParseExact(datecour.Text.ToString(), "MM/dd/yyyy", System.Globalization.CultureInfo.InvariantCulture));
//Response.Write("date arrivée : " +datearr.Text.ToString() + " ");
c.setDate_Arrivee(DateTime.ParseExact(datearr.Text.ToString(), "MM/dd/yyyy", System.Globalization.CultureInfo.InvariantCulture));
//Response.Write(" unité administrative : " +id_unit.Value+" ");
//Response.Write("Agent de l'unité : " + id_agent_ua.Value.Split(new Char[] { '-' })[0] + " ");
c.setAgentUA(new AgentUA(Int32.Parse(id_agent_ua.Value)));
//.ToString().Split(new Char[]{'-'})[0]
//Response.Write("Type courrier (interne/externe ) : " +typecr.SelectedValue.ToString() + " ");
c.setType((typecr.SelectedValue.ToString().Equals("arr") ? "Arrive" : "Interne"));
//Response.Write("Requis une réponse : " + (reponse.Checked ? "Oui" : "Non "));
c.setReponse(reponse.Checked);
//Response.Write("C'est une facture : " + (fact.Checked ? "Oui, device : "+device.Text.ToString()+" et montant " +montant.Text.ToString() : "Non "));
if (fact.Checked)
{
((Facture)c).setDevice(device.Text.ToString());
((Facture)c).setMontant(float.Parse(montant.Text.ToString().Replace(".", ",")));
}
c.setAgentBO((AgentBO)Session["user"]);
//Response.Write(String.Format("Insert into courrier values('{0}','{1}','{2}','{3}','{4}','Cree',{5},{6},{7},null,null)", c.getReference(), c.getTypecr(), c.getNature(), c.getDate_creation(), c.getObjet(), c.getAgentBO().getId(), c.getAgentUA().getId(), c.getReponse() ? 1 : 0));
//Response.Write(String.Format("Insert into courrier_arrive_interne values('{0}','{1}','{2}','{3}',(select id_contact from contacts where nom='{4}'))", c.getReference(), c.getDate_Courrier(), c.getDate_Arrivee(), c.getType(), c.getExpediteur().getNom()));
//Response.Write(String.Format("Insert into facture values ('{0}','{1}',{2})", c.getReference(), ((Facture)c).getDevice(), ((Facture)c).getMontant() + "").Replace(",", "."));
//Response.Write(CourrierService.ajouterCourrier(c)?"OMG courrier ajouté !":"Non gros fail !");
if (CourrierService.ajouterCourrier(c))
{
CourrierService.repondreCourrier(id_ref_cour_rep.Value, ref_cour.Text.ToString());
result.Text = "Le courrier a été ajouté avec succès !";
}
else
{
result.Text = "Echec d'ajout, veuillez vérifier les données entrés";
}
//Response.Write(cour_rep.Checked ? "Réponse au courrier : " + id_ref_cour_rep.Value : "Ce courrier n'est pas une réponse ");
}
