public static TypePret GetTypePret(int id)
{
TypePret type = new TypePret();
SqlConnection sqlConnection = new SqlConnection(constr);
sqlConnection.Open();
String command = "SELECT * FROM " + table + " where Id = @id;";
SqlCommand sqlCommand = new SqlCommand(command, sqlConnection);
using (sqlCommand)
{
sqlCommand.CommandType = CommandType.Text;
sqlCommand.Parameters.AddWithValue("@id", id);
SqlDataReader dataReader = sqlCommand.ExecuteReader();
if (dataReader.Read())
{
type.Id = (int)dataReader[0];
type.nom = (string)dataReader[1];
}
dataReader.Close();
}
sqlConnection.Close();
sqlCommand.Dispose();
return(type);
}
public static List <TypePret> TypesList()
{
List <TypePret> typePrets = new List <TypePret>();
SqlConnection conn = new SqlConnection(constr);
conn.Open();
string sql = "SELECT * FROM " + table + ";";
SqlCommand cmd = new SqlCommand(sql, conn);
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
SqlDataReader dreader = cmd.ExecuteReader();
while (dreader.Read())
{
TypePret type = new TypePret(Convert.ToInt32(dreader[0]), dreader[1].ToString());
typePrets.Add(type);
}
dreader.Close();
cmd.Dispose();
conn.Close();
return(typePrets);
}
public static void DeletType(TypePret typePret)
{
string _query = "DELETE FROM dbo.types WHERE Id=@ID ;";
SqlConnection _con = new SqlConnection(constr);
SqlCommand _command;
_con.Open();
using (_command = new SqlCommand(_query, _con))
{
_command.CommandType = CommandType.Text;
_command.Parameters.AddWithValue("@ID", typePret.Id);
_command.ExecuteNonQuery();
}
_con.Close();
}
public static void AddType(TypePret type)
{
SqlConnection sqlConnection = new SqlConnection(constr);
String command = "INSERT INTO dbo.types VALUES(@Id , @nom);";
SqlCommand sqlCommand = new SqlCommand(command, sqlConnection);
sqlConnection.Open();
using (sqlCommand)
{
sqlCommand.CommandType = CommandType.Text;
sqlCommand.Parameters.AddWithValue("@Id", type.Id);
sqlCommand.Parameters.AddWithValue("@nom", type.nom);
sqlCommand.ExecuteNonQuery();
}
sqlCommand.Dispose();
sqlConnection.Close();
}
public void removeTypePrets(TypePret typePret)
{
GetTypePrets().Remove(typePret);
}
public void addTypePret(TypePret typePret)
{
GetTypePrets().Add(typePret);
}
public void setType(TypePret typePret)
{
type = typePret;
}
public static void AddPret(ServiceSocial serviceSocial)
{
SqlConnection sqlConnection = new SqlConnection(constr);
sqlConnection.Open();
String Command = "INSERT INTO " + table + " VALUES (@Id , @Emp , @service ," +
"@pv , @somme , @remb1 , @date1 , @remb2 , @date2 , @remb3 , @date3" +
", @remb4 , @date4 , @remb5 , @date5" +
", @remb6 , @date6 , @remb7 , @date7 , @remb8 , @date8" +
", @remb9 , @date9 , @remb10 , @date10 ,@observation ,@type ) ; ";
SqlCommand sqlCommand = new SqlCommand(Command, sqlConnection);
using (sqlCommand)
{
sqlCommand.CommandType = CommandType.Text;
sqlCommand.Parameters.AddWithValue("@Id", serviceSocial.getid());
sqlCommand.Parameters.AddWithValue("@Emp", serviceSocial.getEmployer());
if (serviceSocial is PretSociale)
{
sqlCommand.Parameters.AddWithValue("@service", 1);
}
else if (serviceSocial is Dons)
{
sqlCommand.Parameters.AddWithValue("@service", 2);
}
else // Credit Electromenager
{
sqlCommand.Parameters.AddWithValue("@service", 3);
}
sqlCommand.Parameters.AddWithValue("@pv", serviceSocial.getPV().getId());
sqlCommand.Parameters.AddWithValue("@somme", serviceSocial.getPrix());
int j;
for (int i = 0; i < serviceSocial.getprelevements().Count; i++)
{
j = i + 1;
String str = "@remb" + j;
sqlCommand.Parameters.AddWithValue(str, serviceSocial.getprelevement(i));
}
for (int i = 0; i < serviceSocial.getDates().Count; i++)
{
j = i + 1;
String str = "@date" + j;
sqlCommand.Parameters.AddWithValue(str, serviceSocial.getDate(i));
}
for (int i = serviceSocial.getDates().Count; i < 10; i++)
{
j = i + 1;
String str = "@date" + j;
sqlCommand.Parameters.AddWithValue(str, DBNull.Value);
}
if (String.IsNullOrEmpty(serviceSocial.getobservation()))
{
sqlCommand.Parameters.AddWithValue("@observation", DBNull.Value);
}
else
{
sqlCommand.Parameters.AddWithValue("@observation", serviceSocial.getobservation());
}
if (serviceSocial is Pret)
{
TypePret type = ((Pret)serviceSocial).getTypePret();
sqlCommand.Parameters.AddWithValue("@type", type.Id);
}
else
{
sqlCommand.Parameters.AddWithValue("@type", DBNull.Value);
}
sqlCommand.ExecuteNonQuery();
}
sqlCommand.Dispose();
sqlConnection.Close();
try
{
if (serviceSocial is Electromenager)
{
ConnectionBonCmd.AddBonCmd(((Electromenager)serviceSocial).GetBon_Cmd());
ConnectionFacture.AddFacture(((Electromenager)serviceSocial).GetFacture());
}
}
catch { }
}
public static List <ServiceSocial> GetListPrets(int EmpID)
{
List <ServiceSocial> serviceSocials = new List <ServiceSocial>();
PV pv; Service service; TypePret type; Bon_Cmd bon_Cmd; Facture facture;
ServiceSocial serviceSocial;
SqlConnection sqlConnection = new SqlConnection(constr);
sqlConnection.Open();
String command = "SELECT * FROM " + table + " WHERE EmpID = @emp ; ";
SqlCommand sqlCommand = new SqlCommand(command, sqlConnection);
using (sqlCommand)
{
sqlCommand.CommandType = CommandType.Text;
sqlCommand.Parameters.AddWithValue("@emp", EmpID);
SqlDataReader dataReader = sqlCommand.ExecuteReader();
while (dataReader.Read())
{
service = ConnectionService.GetService((int)dataReader[2]);
if (service.nom.Equals("Pret sociale"))
{
PretSociale pretSociale = new PretSociale();
type = new TypePret();
type = ConnectionTypes.GetTypePret((int)dataReader[26]);
pretSociale.setType(type);
serviceSocial = pretSociale;
}
else if (service.nom.Equals("Dons"))
{
Dons dons = new Dons();
type = ConnectionTypes.GetTypePret((int)dataReader[26]);
dons.setType(type);
serviceSocial = dons;
}
else if (service.nom.Equals("Credit Eledtromenager"))
{
Electromenager electromenager = new Electromenager();
try
{
bon_Cmd = ConnectionBonCmd.GetBonCmd((int)dataReader[0]);
electromenager.setBon_cmd(bon_Cmd);
facture = ConnectionFacture.GetFacture((int)dataReader[0]);
electromenager.setFournissure(facture.GetFournisseur());
electromenager.setFacture(facture);
}
catch { }
serviceSocial = electromenager;
}
else
{
serviceSocial = new Pret();
// Cette cas n'est pas valide
throw new Exception();
}
pv = ConnectionPV.GetPV((int)dataReader[3]);
serviceSocial.setPV(pv);
serviceSocial.setid((int)dataReader[0]);
serviceSocial.setEmployer((int)dataReader[1]);
serviceSocial.setPrix(Convert.ToDouble(dataReader[4]));
if (dataReader[25] != DBNull.Value)
{
serviceSocial.setobservation((string)dataReader[25]);
}
int indice = 5;
List <Double> prelev = new List <double>();
List <DateTime> dates = new List <DateTime>();
while (indice < 25)
{
prelev.Add(Convert.ToDouble(dataReader[indice]));
indice++;
if (!(String.IsNullOrEmpty(dataReader[indice].ToString())))
{
dates.Add(Convert.ToDateTime(dataReader[indice]));
}
indice++;
}
serviceSocial.setDates(dates);
serviceSocial.setprelevements(prelev);
serviceSocials.Add(serviceSocial);
}
dataReader.Close();
}
sqlCommand.Dispose();
sqlConnection.Close();
return(serviceSocials);
}
