/*
* This method is used to add a new ShoppingList to the DB.
* This method return the number of lines this method changed in the DB.
*/
public int AddShoppingList(ShoppingList sl)
{
try
{
conn.Open();
DbTransaction dbTrans = conn.GetDbCon().BeginTransaction();
try
{
DbCommand comm = conn.GetDbCon().CreateCommand();
comm.Transaction = dbTrans;
string sql = "INSERT INTO dbo.shoppinglist values(@title, @creationdate, @owner_username, @note)";
comm.CommandText = sql;
DbParameter dbParamTitle = conn.GetDbFactory().CreateParameter();
dbParamTitle.DbType = DbType.AnsiString;
dbParamTitle.Value = sl.Title;
dbParamTitle.ParameterName = "title";
string date = sl.CreationDate.ToString("yyyy MMMM dd HH:mm:ss");
DbParameter dbParamCreationDate = conn.GetDbFactory().CreateParameter();
dbParamCreationDate.DbType = DbType.AnsiString;
dbParamCreationDate.Value = date;
dbParamCreationDate.ParameterName = "creationdate";
DbParameter dbParamOwnerUsername = conn.GetDbFactory().CreateParameter();
dbParamOwnerUsername.DbType = DbType.AnsiString;
dbParamOwnerUsername.Value = sl.OwnerUserName;
dbParamOwnerUsername.ParameterName = "owner_username";
DbParameter dbParamNote = conn.GetDbFactory().CreateParameter();
dbParamNote.DbType = DbType.AnsiString;
dbParamNote.Value = sl.Note;
dbParamNote.ParameterName = "note";
comm.Parameters.Add(dbParamTitle);
comm.Parameters.Add(dbParamCreationDate);
comm.Parameters.Add(dbParamOwnerUsername);
comm.Parameters.Add(dbParamNote);
int result = comm.ExecuteNonQuery();
dbTrans.Commit();
conn.GetDbCon().Close();
return result;
}
catch (SqlException)
{
dbTrans.Rollback();
conn.GetDbCon().Close();
return 0;
}
}
catch (SqlException)
{
conn.GetDbCon().Close();
return 0;
}
}
/*
* This method is used to create a list in the system and in the DB, and add the relations between this new list and its owner.
* This method can return:
* a shoppinglist = the action was succesful and the created shoppinglist is returned
*/
public ShoppingList CreateList(Person p, String title)
{
ShoppingList sl = new ShoppingList(p.Username,title);
p.AddShoppingList(sl);
sDB.AddShoppingList(sl);
int shoppingListID = gm.GetMaxShoppingListNumber();
sDB.AddPersonRelation(p, shoppingListID);
sl.ShoppingListNumber = shoppingListID;
return sl;
}
public void AddShoppingList(ShoppingList list)
{
if (shoppingLists != null)
{
shoppingLists.Add(list);
}
else
{
shoppingLists = new List<ShoppingList>();
shoppingLists.Add(list);
}
}
public PartList FindPartList(ShoppingList sl, string itemName)
{
return sCtrl.FindPartList(sl, itemName);
}
public bool RemovePartList(ShoppingList sl, string itemName)
{
return sCtrl.RemovePartList(sl, itemName);
}
public void AddPartList(ShoppingList sl, PartList psl)
{
sCtrl.AddPartList(sl, psl);
}
/*
* This method is used to get all the ShoppingLists the person with the specified username has created or has acces to.
* This method returns a list of the ShoppingLists this person has created.
*/
public List<ShoppingList> GetShoppingsListsPersonUsername(string username)
{
conn.GetDbCon().Close();
conn.Open();
DbCommand comm = conn.GetDbCon().CreateCommand();
string sql =
"SELECT person.id from dbo.person where person.username = @username";
comm.CommandText = sql;
DbParameter dbParamUsername = conn.GetDbFactory().CreateParameter();
dbParamUsername.DbType = DbType.AnsiString;
dbParamUsername.Value = username;
dbParamUsername.ParameterName = "username";
comm.Parameters.Add(dbParamUsername);
DbDataReader dr = comm.ExecuteReader();
int personid = 0;
while (dr.Read())
{
personid = dr.GetInt32(0);
}
dr.Close();
if (personid != 0)
{
comm = conn.GetDbCon().CreateCommand();
sql =
"SELECT person_shoppinglist.shoppinglist_id from dbo.person_shoppinglist where person_id = @personID";
comm.CommandText = sql;
DbParameter dbParamPersonID = conn.GetDbFactory().CreateParameter();
dbParamPersonID.DbType = DbType.AnsiString;
dbParamPersonID.Value = personid;
dbParamPersonID.ParameterName = "personID";
comm.Parameters.Add(dbParamPersonID);
DbDataReader dr1 = comm.ExecuteReader();
List<int> slID = new List<int>();
while (dr1.Read())
{
slID.Add(dr1.GetInt32(0));
}
dr1.Close();
List<ShoppingList> ShoppingLists = new List<ShoppingList>();
comm = conn.GetDbCon().CreateCommand();
foreach (int i in slID)
{
sql =
"SELECT shoppinglist_number, title, note, creationdate, owner_username from dbo.shoppinglist where shoppinglist.shoppinglist_number = " + i;
comm.CommandText = sql;
DbDataReader dr2 = comm.ExecuteReader();
while (dr2.Read())
{
ShoppingList sl = new ShoppingList();
sl.OwnerUserName = dr2.GetString(4);
int ShoppingListNumber;
ShoppingListNumber = dr2.GetInt32(0);
sl.ShoppingListNumber = ShoppingListNumber;
sl.PartLists = GetPartListsSLNumber(sl.ShoppingListNumber);
sl.Title = dr2.GetString(1);
sl.Note = dr2.GetString(2);
DateTime dt = DateTime.ParseExact(dr2.GetString(3), "yyyy MMMM dd HH:mm:ss",
CultureInfo.GetCultureInfo(0x0406));
sl.CreationDate = dt;
ShoppingLists.Add(sl);
}
dr2.Close();
}
conn.GetDbCon().Close();
return ShoppingLists;
}
return null;
}
/*
* This method is used to remove a PartList from the system and in the DB
* This method can return:
* false = the PartList was not deleted
* true = the PartList was deleted succesfully
*/
public bool RemovePartList(ShoppingList sl, string itemName)
{
bool deleted = false;
PartList returnPartLists = FindPartList(sl, itemName);
if (returnPartLists != null)
{
sl.PartLists.Remove(returnPartLists);
deleted = true;
}
return deleted;
}
/*
* This method adds a PartList to the specified ShoppingList
*/
public void AddPartList(ShoppingList sl, PartList psl)
{
sl.PartLists.Add(psl);
}
/*
* This method is used to find a specific PartList
* This method can return:
* null = the PartList was not found
* a PartList = the PartList was found and returned succcesfully
*/
public PartList FindPartList(ShoppingList sl, string itemName)
{
bool found = false;
PartList returnPartList = null;
int i = 0;
while (i < sl.PartLists.Count && !found)
{
if (sl.PartLists[i].Item.Name == itemName)
{
returnPartList = sl.PartLists[i];
found = true;
}
}
return returnPartList;
}
public void createShoppingList(string shoppingListName, long completionDate, int userId)
{
TimeSpan timeSpan = DateTime.UtcNow - new DateTime(1970,1,1,0,0,0);
ShoppingList shoppingList = new ShoppingList(shoppingListName, (long)timeSpan.TotalSeconds, completionDate);
}