public bool AddGroups(Group group)
{
bool result = false;
try
{
Database db = DatabaseFactory.CreateDatabase(Constant.Database_Connection_Name);
connection = db.CreateConnection();
connection.Open();
transaction = connection.BeginTransaction();
DbCommand dbCommand = db.GetStoredProcCommand(Constant.SP_Groups_Insert);
db.AddInParameter(dbCommand, "@sGroupCode", DbType.String, group.GroupCode);
db.AddInParameter(dbCommand, "@sGroupName", DbType.String, group.GroupName);
db.AddInParameter(dbCommand, "@sDescription", DbType.String, group.Description);
db.AddInParameter(dbCommand, "@tiItemCount", DbType.Int16, group.ItemCount);
db.AddInParameter(dbCommand, "@bIsActive", DbType.Boolean, group.IsActive);
db.AddOutParameter(dbCommand, "@tiGroupId", DbType.Int16, 4);
if (db.ExecuteNonQuery(dbCommand, transaction) > 0)
{
Int16 newGroupId = Convert.ToInt16(db.GetParameterValue(dbCommand, "@tiGroupId"));
if (newGroupId > 0)
{
group.GroupId = newGroupId;
if (this.UpdateGroupItems(group, db, transaction))
{
transaction.Commit();
result = true;
}
}
}
}
catch (System.Exception ex)
{
transaction.Rollback();
throw ex;
}
finally
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
return result;
}
public bool DeleteGroup(Group group)
{
bool result = false;
try
{
Database db = DatabaseFactory.CreateDatabase(Constant.Database_Connection_Name);
DbCommand cmd = db.GetStoredProcCommand(Constant.SP_Groups_Delete);
db.AddInParameter(cmd, "@tiGroupId", DbType.Int16, group.GroupId);
db.ExecuteNonQuery(cmd);
result = true;
}
catch (System.Exception ex)
{
throw ex;
result = false;
}
return result;
}
private bool UpdateGroupItems(Group group, Database db, DbTransaction transaction)
{
bool result = false;
try
{
DbCommand insCommand = db.GetStoredProcCommand(Constant.SP_Groups_InsGroupItems);
db.AddInParameter(insCommand, "@iItemId", DbType.Int32, "ItemId", DataRowVersion.Current);
db.AddInParameter(insCommand, "@tiIGroupId", DbType.Int16, group.GroupId);
DbCommand DelCommand = db.GetStoredProcCommand(Constant.SP_BrandCategories_DelBrandCategories);
db.AddInParameter(DelCommand, "@iItemId", DbType.Int32, "ItemId", DataRowVersion.Current);
db.AddInParameter(DelCommand, "@tiIGroupId", DbType.Int16, group.GroupId);
db.UpdateDataSet(group.GroupItems, group.GroupItems.Tables[0].TableName, insCommand, null, DelCommand, transaction);
result = true;
}
catch (System.Exception ex)
{
throw ex;
}
return result;
}
private Int64 CalculateQIH(Group group)
{
try
{
Int64 minQTY = 0;
DataSet ds = group.GroupItems;
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
{
Int64 rowQTY =0;
foreach (DataRow row in group.GroupItems.Tables[0].Rows)
{
rowQTY = row["BillableQty"] != DBNull.Value ? Convert.ToInt64(row["BillableQty"].ToString()) :
Convert.ToInt64("0");
if (rowQTY == 0)
{
minQTY = 0;
return minQTY;
}
else if (rowQTY > 0 && ( (rowQTY < minQTY) || minQTY ==0 ))
{
minQTY = rowQTY;
}
}
}
return minQTY;
}
catch (System.Exception ex)
{
throw ex;
}
}
public DataSet GetItemsByGroupId(Group group)
{
DataSet ds = null;
try
{
Database db = DatabaseFactory.CreateDatabase(Constant.Database_Connection_Name);
DbCommand cmd = db.GetStoredProcCommand(Constant.SP_Groups_SelItemsByGroupID);
db.AddInParameter(cmd, "@tiGroupId", DbType.Int16, group.GroupId);
ds = db.ExecuteDataSet(cmd);
}
catch (System.Exception ex)
{
ds = null;
throw ex;
}
return ds;
}
public bool GetGroupByID(Group group)
{
bool result = false;
try
{
Database db = DatabaseFactory.CreateDatabase(Constant.Database_Connection_Name);
DbCommand cmd = db.GetStoredProcCommand(Constant.SP_Groups_GetByID);
db.AddInParameter(cmd, "@tiGroupId", DbType.Int16, group.GroupId);
DataSet ds = db.ExecuteDataSet(cmd);
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
{
group.GroupId = ds.Tables[0].Rows[0]["GroupId"] != DBNull.Value ?
Convert.ToInt16(ds.Tables[0].Rows[0]["GroupId"].ToString()) : Convert.ToInt16("0");
group.GroupCode = ds.Tables[0].Rows[0]["GroupCode"] != DBNull.Value ?
Convert.ToString(ds.Tables[0].Rows[0]["GroupCode"].ToString()) : string.Empty;
group.GroupName = ds.Tables[0].Rows[0]["GroupName"] != DBNull.Value ?
Convert.ToString(ds.Tables[0].Rows[0]["GroupName"].ToString()) : string.Empty;
group.Description = ds.Tables[0].Rows[0]["Description"] != DBNull.Value ?
Convert.ToString(ds.Tables[0].Rows[0]["Description"].ToString()) : string.Empty;
group.ItemCount = ds.Tables[0].Rows[0]["ItemCount"] != DBNull.Value ?
Convert.ToInt16(ds.Tables[0].Rows[0]["ItemCount"].ToString()) : Convert.ToInt16("0");
group.IsActive = ds.Tables[0].Rows[0]["IsActive"] != DBNull.Value ?
Convert.ToBoolean(ds.Tables[0].Rows[0]["IsActive"]) : false;
}
if (ds != null && ds.Tables.Count > 1 && ds.Tables[1] != null && ds.Tables[1].Rows.Count > 0)
{
group.Cost = ds.Tables[1].Rows[0]["TotCost"] != DBNull.Value ?
Convert.ToDecimal(ds.Tables[1].Rows[0]["TotCost"].ToString()) : Convert.ToDecimal("0");
group.SellingPrice = ds.Tables[1].Rows[0]["TotSellingPrice"] != DBNull.Value ?
Convert.ToDecimal(ds.Tables[1].Rows[0]["TotSellingPrice"].ToString()) : Convert.ToDecimal("0");
group.MinSellingPrice = ds.Tables[1].Rows[0]["TotMinSellingPrice"] != DBNull.Value ?
Convert.ToDecimal(ds.Tables[1].Rows[0]["TotMinSellingPrice"].ToString()) : Convert.ToDecimal("0");
}
group.GroupItems = this.GetItemsByGroupId(group);
group.QuantityInHand = this.CalculateQIH(group);
result = true;
}
catch (System.Exception ex)
{
throw ex;
result = false;
}
return result;
}