public static List <ItemManufacturer> GetItemManufacturerAsList(DataTable dt)
{
List <ItemManufacturer> oList = new List <ItemManufacturer>();
foreach (DataRow row in dt.Rows)
{
try
{
ItemManufacturer o = new ItemManufacturer();
o.Gtin = row["GTIN"].ToString();
o.ItemId = Helper.ConvertToInt(row["ITEM_ID"]);
o.ManufacturerId = Helper.ConvertToInt(row["MANUFACTURER_ID"]);
o.BaseUom = row["BASE_UOM"].ToString();
o.Alt1Uom = row["ALT_1_UOM"].ToString();
o.Alt1QtyPer = Helper.ConvertToInt(row["ALT_1_QTY_PER"]);
o.Alt2Uom = row["ALT_2_UOM"].ToString();
o.Alt2QtyPer = Helper.ConvertToInt(row["ALT_2_QTY_PER"]);
o.Price = Helper.ConvertToDecimal(row["PRICE"]);
o.GtinParent = row["GTIN_PARENT"].ToString();
o.BaseUomChildPerBaseUomParent = Helper.ConvertToDecimal(row["BASE_UOM_CHILD_PER_BASE_UOM_PARENT"]);
o.StorageSpace = Helper.ConvertToDecimal(row["STORAGE_SPACE"]);
o.IsActive = Helper.ConvertToBoolean(row["IS_ACTIVE"]);
o.Notes = row["NOTES"].ToString();
o.ModifiedOn = Helper.ConvertToDate(row["MODIFIED_ON"]);
o.ModifiedBy = Helper.ConvertToInt(row["MODIFIED_BY"]);
oList.Add(o);
}
catch (Exception ex)
{
Log.InsertEntity("ItemManufacturer", "GetItemManufacturerAsList", 1, ex.StackTrace.Replace("'", ""), ex.Message.Replace("'", ""));
throw ex;
}
}
return(oList);
}
public static int Update(ItemManufacturer o)
{
try
{
string query = @"UPDATE ""ITEM_MANUFACTURER"" SET ""ITEM_ID"" = @ItemId, ""MANUFACTURER_ID"" = @ManufacturerId, ""BASE_UOM"" = @BaseUom, ""ALT_1_UOM"" = @Alt1Uom, ""ALT_1_QTY_PER"" = @Alt1QtyPer, ""ALT_2_UOM"" = @Alt2Uom, ""ALT_2_QTY_PER"" = @Alt2QtyPer, ""PRICE"" = @Price, ""GTIN_PARENT"" = @GtinParent, ""BASE_UOM_CHILD_PER_BASE_UOM_PARENT"" = @BaseUomChildPerBaseUomParent, ""STORAGE_SPACE"" = @StorageSpace, ""IS_ACTIVE"" = @IsActive, ""NOTES"" = @Notes, ""MODIFIED_ON"" = @ModifiedOn, ""MODIFIED_BY"" = @ModifiedBy WHERE ""GTIN"" = @Gtin ";
List <Npgsql.NpgsqlParameter> parameters = new List <NpgsqlParameter>()
{
new NpgsqlParameter("@Gtin", DbType.String)
{
Value = o.Gtin
},
new NpgsqlParameter("@ItemId", DbType.Int32)
{
Value = o.ItemId
},
new NpgsqlParameter("@ManufacturerId", DbType.Int32)
{
Value = o.ManufacturerId
},
new NpgsqlParameter("@BaseUom", DbType.String)
{
Value = o.BaseUom
},
new NpgsqlParameter("@Alt1Uom", DbType.String)
{
Value = o.Alt1Uom
},
new NpgsqlParameter("@Alt1QtyPer", DbType.Int32)
{
Value = o.Alt1QtyPer
},
new NpgsqlParameter("@Alt2Uom", DbType.String)
{
Value = (object)o.Alt2Uom ?? DBNull.Value
},
new NpgsqlParameter("@Alt2QtyPer", DbType.Int32)
{
Value = (object)o.Alt2QtyPer ?? DBNull.Value
},
new NpgsqlParameter("@Price", DbType.Double)
{
Value = (object)o.Price ?? DBNull.Value
},
new NpgsqlParameter("@GtinParent", DbType.String)
{
Value = (object)o.GtinParent ?? DBNull.Value
},
new NpgsqlParameter("@BaseUomChildPerBaseUomParent", DbType.Double)
{
Value = (object)o.BaseUomChildPerBaseUomParent ?? DBNull.Value
},
new NpgsqlParameter("@StorageSpace", DbType.Double)
{
Value = (object)o.StorageSpace ?? DBNull.Value
},
new NpgsqlParameter("@IsActive", DbType.Boolean)
{
Value = o.IsActive
},
new NpgsqlParameter("@Notes", DbType.String)
{
Value = (object)o.Notes ?? DBNull.Value
},
new NpgsqlParameter("@ModifiedOn", DbType.DateTime)
{
Value = o.ModifiedOn
},
new NpgsqlParameter("@ModifiedBy", DbType.Int32)
{
Value = o.ModifiedBy
}
};
int rowAffected = DBManager.ExecuteNonQueryCommand(query, CommandType.Text, parameters);
AuditTable.InsertEntity("ItemManufacturer", o.Gtin, 2, DateTime.Now, o.ModifiedBy);
// Update the Kitted Items
// HACK: This needs to be fixed
parameters = new List <NpgsqlParameter>()
{
new NpgsqlParameter("@parentGtin", DbType.String)
{
Value = o.Gtin
}
};
query = "DELETE FROM \"ITEM_KIT\" WHERE \"PARENT_GTIN\" = @parentGtin";
DBManager.ExecuteNonQueryCommand(query, CommandType.Text, parameters);
foreach (var itm in o.KitItems)
{
query = "INSERT INTO \"ITEM_KIT\" (\"PARENT_GTIN\",\"CHILD_GTIN\",\"QTY_CHILD_PARENT_UOM\",\"NOTES\",\"MODIFIED_BY\") VALUES (@parentGtin,@childGtin,1,@note,@userId); ";
parameters = new List <NpgsqlParameter>()
{
new NpgsqlParameter("@parentGtin", DbType.String)
{
Value = o.Gtin
},
new NpgsqlParameter("@childGtin", DbType.String)
{
Value = itm.Gtin
},
new NpgsqlParameter("@note", DbType.String)
{
Value = itm.ItemObject.Name
},
new NpgsqlParameter("@userId", DbType.Int32)
{
Value = o.ModifiedBy
}
};
DBManager.ExecuteNonQueryCommand(query, CommandType.Text, parameters);
}
return(rowAffected);
}
catch (Exception ex)
{
Log.InsertEntity("ItemManufacturer", "Update", 2, ex.StackTrace.Replace("'", ""), ex.Message.Replace("'", ""));
}
return(-1);
}
public static int Insert(ItemManufacturer o)
{
try
{
string query = @"INSERT INTO ""ITEM_MANUFACTURER"" (""GTIN"", ""ITEM_ID"", ""MANUFACTURER_ID"", ""BASE_UOM"", ""ALT_1_UOM"", ""ALT_1_QTY_PER"", ""ALT_2_UOM"", ""ALT_2_QTY_PER"", ""PRICE"", ""GTIN_PARENT"", ""BASE_UOM_CHILD_PER_BASE_UOM_PARENT"", ""STORAGE_SPACE"", ""IS_ACTIVE"", ""NOTES"", ""MODIFIED_ON"", ""MODIFIED_BY"") VALUES (@Gtin, @ItemId, @ManufacturerId, @BaseUom, @Alt1Uom, @Alt1QtyPer, @Alt2Uom, @Alt2QtyPer, @Price, @GtinParent, @BaseUomChildPerBaseUomParent, @StorageSpace, @IsActive, @Notes, @ModifiedOn, @ModifiedBy) returning ""GTIN"" ";
List <Npgsql.NpgsqlParameter> parameters = new List <NpgsqlParameter>()
{
new NpgsqlParameter("@Gtin", DbType.String)
{
Value = o.Gtin
},
new NpgsqlParameter("@ItemId", DbType.Int32)
{
Value = o.ItemId
},
new NpgsqlParameter("@ManufacturerId", DbType.Int32)
{
Value = o.ManufacturerId
},
new NpgsqlParameter("@BaseUom", DbType.String)
{
Value = o.BaseUom
},
new NpgsqlParameter("@Alt1Uom", DbType.String)
{
Value = o.Alt1Uom
},
new NpgsqlParameter("@Alt1QtyPer", DbType.Int32)
{
Value = o.Alt1QtyPer
},
new NpgsqlParameter("@Alt2Uom", DbType.String)
{
Value = (object)o.Alt2Uom ?? DBNull.Value
},
new NpgsqlParameter("@Alt2QtyPer", DbType.Int32)
{
Value = (object)o.Alt2QtyPer ?? DBNull.Value
},
new NpgsqlParameter("@Price", DbType.Double)
{
Value = (object)o.Price ?? DBNull.Value
},
new NpgsqlParameter("@GtinParent", DbType.String)
{
Value = (object)o.GtinParent ?? DBNull.Value
},
new NpgsqlParameter("@BaseUomChildPerBaseUomParent", DbType.Double)
{
Value = (object)o.BaseUomChildPerBaseUomParent ?? DBNull.Value
},
new NpgsqlParameter("@StorageSpace", DbType.Double)
{
Value = (object)o.StorageSpace ?? DBNull.Value
},
new NpgsqlParameter("@IsActive", DbType.Boolean)
{
Value = o.IsActive
},
new NpgsqlParameter("@Notes", DbType.String)
{
Value = (object)o.Notes ?? DBNull.Value
},
new NpgsqlParameter("@ModifiedOn", DbType.Date)
{
Value = o.ModifiedOn
},
new NpgsqlParameter("@ModifiedBy", DbType.Int32)
{
Value = o.ModifiedBy
}
};
object id = DBManager.ExecuteScalarCommand(query, CommandType.Text, parameters);
AuditTable.InsertEntity("ItemManufacturer", o.Gtin, 1, DateTime.Now, o.ModifiedBy);
return(1);// int.Parse(id.ToString());
}
catch (Exception ex)
{
Log.InsertEntity("ItemManufacturer", "Insert", 1, ex.StackTrace.Replace("'", ""), ex.Message.Replace("'", ""));
}
return(-1);
}