public static void add(string name, GordenItemCategories category, Guid vendorID, Guid retailLengthUnitID, Guid bulkLengthUnitID, Guid?productWidthID, decimal buyRetailPricePerUnit, decimal buyBulkPricePerUnit,
decimal sellRetailPricePerUnit, decimal sellBulkPricePerUnit, string notes)
{
try
{
Guid id = Guid.NewGuid();
using (SqlCommand cmd = new SqlCommand("gordenitem_add", DBConnection.ActiveSqlConnection))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@" + COL_DB_ID, SqlDbType.UniqueIdentifier).Value = id;
cmd.Parameters.Add("@" + COL_DB_NAME, SqlDbType.VarChar).Value = name;
cmd.Parameters.Add("@" + COL_DB_CATEGORYENUMID, SqlDbType.TinyInt).Value = category;
cmd.Parameters.Add("@" + COL_DB_VENDORID, SqlDbType.UniqueIdentifier).Value = vendorID;
cmd.Parameters.Add("@" + COL_DB_PRODUCTWIDTHID, SqlDbType.UniqueIdentifier).Value = Util.wrapNullable(productWidthID);
cmd.Parameters.Add("@" + COL_DB_RETAILLENGTHUNITID, SqlDbType.UniqueIdentifier).Value = retailLengthUnitID;
cmd.Parameters.Add("@" + COL_DB_BULKLENGTHUNITID, SqlDbType.UniqueIdentifier).Value = bulkLengthUnitID;
cmd.Parameters.Add("@" + COL_DB_BUYRETAILPRICEPERUNIT, SqlDbType.Decimal).Value = Util.wrapNullable(buyRetailPricePerUnit);
cmd.Parameters.Add("@" + COL_DB_BUYBULKPRICEPERUNIT, SqlDbType.Decimal).Value = Util.wrapNullable(buyBulkPricePerUnit);
cmd.Parameters.Add("@" + COL_DB_SELLRETAILPRICEPERUNIT, SqlDbType.Decimal).Value = Util.wrapNullable(sellRetailPricePerUnit);
cmd.Parameters.Add("@" + COL_DB_SELLBULKPRICEPERUNIT, SqlDbType.Decimal).Value = Util.wrapNullable(sellBulkPricePerUnit);
cmd.Parameters.Add("@" + COL_DB_NOTES, SqlDbType.VarChar).Value = Util.wrapNullable(notes);
cmd.ExecuteNonQuery();
ActivityLog.submit(id, "Item created");
}
Tools.hasMessage("Item created");
}
catch (Exception ex) { Tools.showError(ex.Message); }
}
/*******************************************************************************************************/
#region DATABASE METHODS
//public static void add(string notes)
//{
// Guid id = Guid.NewGuid();
// try
// {
// using (SqlConnection conn = new SqlConnection(DBUtil.connectionString))
// using (SqlCommand cmd = new SqlCommand("gordenorderitemmaterial_add", conn))
// {
// cmd.CommandType = CommandType.StoredProcedure;
// cmd.Parameters.Add("@" + COL_DB_ID, SqlDbType.UniqueIdentifier).Value = id;
// cmd.Parameters.Add("@" + COL_DB_NOTES, SqlDbType.VarChar).Value = Util.wrapNullable(notes);
// conn.Open();
// cmd.ExecuteNonQuery();
// ActivityLog.submit(conn, id, "Item added");
// }
// }
// catch (Exception ex) { Tools.showError(ex.Message); }
//}
public static void addItems(List <GordenOrderItemMaterial> materials)
{
try
{
foreach (GordenOrderItemMaterial material in materials)
{
using (SqlCommand cmd = new SqlCommand("gordenorderitemmaterial_add", DBConnection.ActiveSqlConnection))
{
Guid id = Guid.NewGuid();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@" + COL_DB_ID, SqlDbType.UniqueIdentifier).Value = id;
cmd.Parameters.Add("@" + COL_DB_GORDENORDERITEMID, SqlDbType.UniqueIdentifier).Value = material.GordenOrderItemID;
cmd.Parameters.Add("@" + COL_DB_GORDENITEMID, SqlDbType.UniqueIdentifier).Value = material.GordenItemID;
cmd.Parameters.Add("@" + COL_DB_DESCRIPTION, SqlDbType.UniqueIdentifier).Value = material.Description;
cmd.Parameters.Add("@" + COL_DB_BUYAMOUNTPERUNIT, SqlDbType.UniqueIdentifier).Value = material.BuyAmountPerUnit;
cmd.Parameters.Add("@" + COL_DB_QTY, SqlDbType.UniqueIdentifier).Value = material.Qty;
cmd.Parameters.Add("@" + COL_DB_NOTES, SqlDbType.VarChar).Value = Util.wrapNullable(material.Notes);
cmd.ExecuteNonQuery();
ActivityLog.submit(id, "Item added");
}
}
}
catch (Exception ex) { Tools.showError(ex.Message); }
}
public static void update(Guid id, string name)
{
try
{
Grade objOld = new Grade(id);
//generate log description
string logDescription = "";
if (objOld.Name != name)
{
logDescription = Tools.append(logDescription, String.Format("Name: '{0}' to '{1}'", objOld.Name, name), ",");
}
if (!string.IsNullOrEmpty(logDescription))
{
using (SqlCommand cmd = new SqlCommand("grade_update", DBConnection.ActiveSqlConnection))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@" + COL_DB_ID, SqlDbType.UniqueIdentifier).Value = id;
cmd.Parameters.Add("@" + COL_DB_NAME, SqlDbType.VarChar).Value = name;
cmd.ExecuteNonQuery();
ActivityLog.submitUpdate(id, logDescription);
}
Tools.hasMessage("Item updated");
}
}
catch (Exception ex) { Tools.showError(ex.Message); }
}
/*******************************************************************************************************/
#region DATABASE METHODS
public static string add(Guid id, Guid customerID, string customerInfo, decimal discountAmount, decimal otherCharges, string notes,
DataTable gordenOrderItems, List <GordenOrderItemMaterial> materials)
{
string no = "";
try
{
using (SqlCommand cmd = new SqlCommand("gordenorder_add", DBConnection.ActiveSqlConnection))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@" + COL_DB_ID, SqlDbType.UniqueIdentifier).Value = id;
cmd.Parameters.Add("@" + COL_DB_CUSTOMERID, SqlDbType.UniqueIdentifier).Value = customerID;
cmd.Parameters.Add("@" + COL_DB_CUSTOMERINFO, SqlDbType.VarChar).Value = customerInfo;
cmd.Parameters.Add("@" + COL_DB_DISCOUNTAMOUNT, SqlDbType.Decimal).Value = discountAmount;
cmd.Parameters.Add("@" + COL_DB_OTHERCHARGES, SqlDbType.Decimal).Value = otherCharges;
cmd.Parameters.Add("@" + COL_DB_NOTES, SqlDbType.VarChar).Value = Util.wrapNullable(notes);
SqlParameter return_value = cmd.Parameters.Add("@return_value", SqlDbType.Int);
return_value.Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
no = Convert.ToInt32(return_value.Value).ToString();
ActivityLog.submit(id, "Item added");
GordenOrderItem.addItems(gordenOrderItems);
GordenOrderItemMaterial.addItems(materials);
}
}
catch (Exception ex) { Tools.showError(ex.Message); }
return(no);
}
public static void update(Guid id, string description, decimal sellAmountPerUnit, int qty, string notes)
{
try
{
string log = "";
GordenOrderItem objOld = new GordenOrderItem(id);
log = ActivityLog.appendChange(log, objOld.Description, description, "Description: '{0}' to '{1}'");
log = ActivityLog.appendChange(log, objOld.SellAmountPerUnit, sellAmountPerUnit, "Sell Amount Per Unit: '{0}' to '{1}'");
log = ActivityLog.appendChange(log, objOld.Qty, qty, "Qty: '{0}' to '{1}'");
log = ActivityLog.appendChange(log, objOld.Notes, notes, "Notes: '{0}' to '{1}'");
using (SqlCommand cmd = new SqlCommand("gordenorderitem_update", DBConnection.ActiveSqlConnection))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@" + COL_DB_ID, SqlDbType.UniqueIdentifier).Value = id;
cmd.Parameters.Add("@" + COL_DB_DESCRIPTION, SqlDbType.VarChar).Value = description;
cmd.Parameters.Add("@" + COL_DB_SELLAMOUNTPERUNIT, SqlDbType.Decimal).Value = sellAmountPerUnit;
cmd.Parameters.Add("@" + COL_DB_QTY, SqlDbType.Int).Value = qty;
cmd.Parameters.Add("@" + COL_DB_NOTES, SqlDbType.VarChar).Value = Util.wrapNullable(notes);
cmd.ExecuteNonQuery();
ActivityLog.submit(id, String.Format("Item updated: {0}", log));
}
}
catch (Exception ex) { Tools.showError(ex.Message); }
}
public static void update(Guid id, string name, GordenItemCategories category, Guid vendorID, Guid retailLengthUnitID, Guid bulkLengthUnitID,
Guid?productWidthID, decimal?buyRetailPricePerUnit, decimal?buyBulkPricePerUnit, decimal?sellRetailPricePerUnit, decimal?sellBulkPricePerUnit, string notes)
{
try
{
GordenItem objOld = new GordenItem(id);
//generate log description
string log = "";
log = ActivityLog.appendChange(log, objOld.Name, name, "Name: '{0}' to '{1}'");
log = ActivityLog.appendChange(log, objOld.CategoryName, category, "Category: '{0}' to '{1}'");
log = ActivityLog.appendChange(log, objOld.VendorID, new Vendor(vendorID).Name, "Vendor: '{0}' to '{1}'");
log = ActivityLog.appendChange(log, objOld.ProductWidthID, new ProductWidth(productWidthID).Name, "Width: '{0}' to '{1}'");
log = ActivityLog.appendChange(log, objOld.RetailLengthUnitID, new LengthUnit(retailLengthUnitID).Name, "Retail Unit: '{0}' to '{1}'");
log = ActivityLog.appendChange(log, objOld.BulkLengthUnitID, new LengthUnit(bulkLengthUnitID).Name, "Bulk Unit: '{0}' to '{1}'");
log = ActivityLog.appendChange(log, objOld.BuyRetailPricePerUnit, buyRetailPricePerUnit, "Buy Retail Price/Unit: '{0}' to '{1}'");
log = ActivityLog.appendChange(log, objOld.BuyBulkPricePerUnit, buyBulkPricePerUnit, "Buy Bulk Price/Unit: '{0}' to '{1}'");
log = ActivityLog.appendChange(log, objOld.SellRetailPricePerUnit, sellRetailPricePerUnit, "Sell Retail Price/Unit: '{0}' to '{1}'");
log = ActivityLog.appendChange(log, objOld.SellBulkPricePerUnit, sellBulkPricePerUnit, "Sell Bulk Price/Unit: '{0}' to '{1}'");
log = ActivityLog.appendChange(log, objOld.Notes, notes, "Notes: '{0}' to '{1}'");
if (string.IsNullOrEmpty(log))
{
Tools.showError("No changes to record");
}
else
{
using (SqlCommand cmd = new SqlCommand("gordenitem_update", DBConnection.ActiveSqlConnection))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@" + COL_DB_ID, SqlDbType.UniqueIdentifier).Value = id;
cmd.Parameters.Add("@" + COL_DB_NAME, SqlDbType.VarChar).Value = name;
cmd.Parameters.Add("@" + COL_DB_CATEGORYENUMID, SqlDbType.TinyInt).Value = category;
cmd.Parameters.Add("@" + COL_DB_VENDORID, SqlDbType.UniqueIdentifier).Value = vendorID;
cmd.Parameters.Add("@" + COL_DB_PRODUCTWIDTHID, SqlDbType.UniqueIdentifier).Value = Util.wrapNullable(productWidthID);
cmd.Parameters.Add("@" + COL_DB_RETAILLENGTHUNITID, SqlDbType.UniqueIdentifier).Value = retailLengthUnitID;
cmd.Parameters.Add("@" + COL_DB_BULKLENGTHUNITID, SqlDbType.UniqueIdentifier).Value = bulkLengthUnitID;
cmd.Parameters.Add("@" + COL_DB_BUYRETAILPRICEPERUNIT, SqlDbType.Decimal).Value = Util.wrapNullable(buyRetailPricePerUnit);
cmd.Parameters.Add("@" + COL_DB_BUYBULKPRICEPERUNIT, SqlDbType.Decimal).Value = Util.wrapNullable(buyBulkPricePerUnit);
cmd.Parameters.Add("@" + COL_DB_SELLRETAILPRICEPERUNIT, SqlDbType.Decimal).Value = Util.wrapNullable(sellRetailPricePerUnit);
cmd.Parameters.Add("@" + COL_DB_SELLBULKPRICEPERUNIT, SqlDbType.Decimal).Value = Util.wrapNullable(sellBulkPricePerUnit);
cmd.Parameters.Add("@" + COL_DB_NOTES, SqlDbType.VarChar).Value = Util.wrapNullable(notes);
cmd.ExecuteNonQuery();
ActivityLog.submit(id, "UPDATE: " + log);
}
Tools.hasMessage("Item updated");
}
}
catch (Exception ex) { Tools.showError(ex.Message); }
}
public static void startProgressDisplay(string displayText)
{
if (!SharedForms.ProgressDisplay.InUse)
{
SharedForms.ProgressDisplay.ActiveForm = Form.ActiveForm;
SharedForms.ProgressDisplay.DisplayText = displayText;
new Thread(SharedForms.ProgressDisplay.run).Start();
}
else
{
Tools.showError("Progress Display is in use. Please notifiy your administrator.");
}
}
/*******************************************************************************************************/
#region CLASS METHODS
public static void updateStatus(Guid id, POItemStatus statusEnumID)
{
try
{
using (SqlCommand cmd = new SqlCommand("poitem_update_status", DBConnection.ActiveSqlConnection))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@" + COL_DB_ID, SqlDbType.UniqueIdentifier).Value = id;
cmd.Parameters.Add("@" + COL_DB_STATUSENUMID, SqlDbType.TinyInt).Value = statusEnumID;
cmd.ExecuteNonQuery();
ActivityLog.submit(id, "Status changed to: " + statusEnumID.ToString());
}
}
catch (Exception ex) { Tools.showError(ex.Message); }
}
public static void update(Guid id, string notes)
{
try
{
string log = "";
GordenOrderItem objOld = new GordenOrderItem(id);
log = ActivityLog.appendChange(log, objOld.Notes, notes, "Notes: '{0}' to '{1}'");
using (SqlCommand cmd = new SqlCommand("gordenorderitemmaterial_update", DBConnection.ActiveSqlConnection))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@" + COL_DB_ID, SqlDbType.UniqueIdentifier).Value = id;
cmd.Parameters.Add("@" + COL_DB_NOTES, SqlDbType.VarChar).Value = Util.wrapNullable(notes);
cmd.ExecuteNonQuery();
ActivityLog.submit(id, String.Format("Item updated: {0}", log));
}
}
catch (Exception ex) { Tools.showError(ex.Message); }
}
public static DataTable get(Guid?id, DateTime?timestamp_Start, DateTime?timestamp_End, Guid?pettyCashRecordsCategories_Id, string notes, bool chkOnlyNotChecked)
{
DataTable datatable = new DataTable();
try
{
using (SqlCommand cmd = new SqlCommand("PettyCashRecords_get", DBConnection.ActiveSqlConnection))
using (SqlDataAdapter adapter = new SqlDataAdapter())
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@" + COL_DB_Id, SqlDbType.UniqueIdentifier).Value = Util.wrapNullable(id);
cmd.Parameters.Add("@" + COL_FILTER_Timestamp_Start, SqlDbType.DateTime).Value = Util.wrapNullable(timestamp_Start);
cmd.Parameters.Add("@" + COL_FILTER_Timestamp_End, SqlDbType.DateTime).Value = Util.wrapNullable(timestamp_End);
cmd.Parameters.Add("@" + COL_DB_PettyCashRecordsCategories_Id, SqlDbType.UniqueIdentifier).Value = Util.wrapNullable(pettyCashRecordsCategories_Id);
cmd.Parameters.Add("@" + COL_DB_Notes, SqlDbType.VarChar).Value = Util.wrapNullable(notes);
cmd.Parameters.Add("@" + FILTER_OnlyNotChecked, SqlDbType.Bit).Value = chkOnlyNotChecked;
SqlParameter return_value = cmd.Parameters.Add("@return_value", SqlDbType.Decimal);
return_value.Direction = ParameterDirection.Output;
adapter.SelectCommand = cmd;
adapter.Fill(datatable);
//calculate balance only if records are not filtered by fields other than start/end time
if (pettyCashRecordsCategories_Id == null)
{
decimal startingBalance = Convert.ToDecimal(return_value.Value);
for (int i = datatable.Rows.Count - 1; i >= 0; i--)
{
startingBalance += DBUtil.parseData <decimal>(datatable.Rows[i], COL_DB_Amount);
datatable.Rows[i][COL_Balance] = startingBalance;
}
}
}
} catch (Exception ex) { Tools.showError(ex.Message); }
return(datatable);
}
public static void add(Guid gordenOrderID, int lineNo, string description, decimal sellAmountPerUnit, int qty, string notes)
{
try
{
using (SqlCommand cmd = new SqlCommand("gordenorderitem_add", DBConnection.ActiveSqlConnection))
{
cmd.CommandType = CommandType.StoredProcedure;
Guid id = Guid.NewGuid();
cmd.Parameters.Add("@" + COL_DB_ID, SqlDbType.UniqueIdentifier).Value = id;
cmd.Parameters.Add("@" + COL_DB_GORDENORDERID, SqlDbType.UniqueIdentifier).Value = gordenOrderID;
cmd.Parameters.Add("@" + COL_DB_LINENO, SqlDbType.TinyInt).Value = lineNo;
cmd.Parameters.Add("@" + COL_DB_DESCRIPTION, SqlDbType.VarChar).Value = description;
cmd.Parameters.Add("@" + COL_DB_SELLAMOUNTPERUNIT, SqlDbType.Decimal).Value = sellAmountPerUnit;
cmd.Parameters.Add("@" + COL_DB_QTY, SqlDbType.Int).Value = qty;
cmd.Parameters.Add("@" + COL_DB_NOTES, SqlDbType.VarChar).Value = Util.wrapNullable(notes);
cmd.ExecuteNonQuery();
ActivityLog.submit(id, "Item added");
}
}
catch (Exception ex) { Tools.showError(ex.Message); }
}
