/*******************************************************************************************************/
#region CLASS METHODS
public static DataTable compileSummaryData(DataTable dt)
{
DataTable dtSummary = dt.Clone();
Tools.setDataTablePrimaryKey(dtSummary, SaleItem.COL_INVENTORY_ID);
DataRow tempRow;
foreach (DataRow dr in dt.Rows)
{
if (dtSummary.Rows.Contains(dr[SaleItem.COL_INVENTORY_ID]))
{
tempRow = dtSummary.Rows.Find(dr[SaleItem.COL_INVENTORY_ID]);
tempRow[SaleItem.COL_QTY] = Tools.zeroNonNumericString(tempRow[SaleItem.COL_QTY]) + Tools.zeroNonNumericString(dr[SaleItem.COL_QTY]);
tempRow[SaleItem.COL_LENGTH] = Tools.zeroNonNumericString(tempRow[SaleItem.COL_LENGTH]) + Tools.zeroNonNumericString(dr[SaleItem.COL_LENGTH]);
tempRow[SaleItem.COL_SUBTOTAL] = Tools.zeroNonNumericString(tempRow[SaleItem.COL_SUBTOTAL]) + Tools.zeroNonNumericString(dr[SaleItem.COL_SUBTOTAL]);
dtSummary.AcceptChanges();
}
else
{
dtSummary.Rows.Add(dr.ItemArray);
}
}
return(dtSummary);
}
public static DataTable compileCompleteData(DataTable dtDetail)
{
string col_sold = "sold";
Tools.addColumn <bool>(dtDetail, col_sold, 0);
Tools.setDataTablePrimaryKey(dtDetail, InventoryItemCheck.COL_DB_INVENTORYITEMID);
DataTable dtCompilation = Inventory.getAll(false);
Tools.addColumn <int>(dtCompilation, COL_COUNT_DIFF_QTY, 0);
Tools.addColumn <decimal>(dtCompilation, COL_COUNT_DIFF_LENGTH, 0);
foreach (DataRow dr in dtCompilation.Rows)
{
dr[Inventory.COL_QTY] = dtDetail.Compute(string.Format("COUNT({0})", COL_COUNT_ITEMLENGTH), string.Format("inventory_id = '{0}' AND {1} = false", dr[Inventory.COL_DB_ID], col_sold));
dr[Inventory.COL_ITEMLENGTH] = dtDetail.Compute(string.Format("SUM({0})", COL_COUNT_ITEMLENGTH), string.Format("inventory_id = '{0}' AND {1} = false", dr[Inventory.COL_DB_ID], col_sold));
//DataRow drSold = dtSoldItems.Rows.Find(dr[Inventory.COL_DB_ID]);
//if (drSold != null)
//{
// dr[Inventory.COL_QTY] = Convert.ToInt32(dr[Inventory.COL_QTY]) - Convert.ToInt32(drSold[Inventory.COL_QTY]);
// dr[Inventory.COL_ITEMLENGTH] = Convert.ToInt32(Tools.zeroNonNumericString(dr[Inventory.COL_ITEMLENGTH])) - Convert.ToInt32(drSold[Inventory.COL_ITEMLENGTH]);
//}
dr[COL_COUNT_DIFF_QTY] = Convert.ToInt16(dr[Inventory.COL_QTY]) - Convert.ToInt16(dr[COL_COUNT_AVAILABLE_QTY]);
dr[COL_COUNT_DIFF_LENGTH] = Convert.ToDecimal(Tools.zeroNonNumericString(dr[Inventory.COL_ITEMLENGTH])) - Convert.ToDecimal(Tools.zeroNonNumericString(dr[COL_COUNT_AVAILABLE_ITEM_LENGTH]));
}
return(dtCompilation);
}
/*******************************************************************************************************/
#region CLASS METHODS
public static DataTable compileSummaryData(DataTable dt)
{
DataTable dtSummary = dt.Clone();
Tools.setDataTablePrimaryKey(dtSummary, InventoryItem.COL_DB_INVENTORY_ID);
DataRow tempRow;
int i = 0;
foreach (DataRow dr in dt.Rows)
{
if (dtSummary.Rows.Contains(dr[InventoryItem.COL_DB_INVENTORY_ID]))
{
tempRow = dtSummary.Rows.Find(dr[InventoryItem.COL_DB_INVENTORY_ID]);
tempRow[InventoryItem.COL_SALE_QTY] = Tools.zeroNonNumericString(tempRow[InventoryItem.COL_SALE_QTY]) + Tools.zeroNonNumericString(dr[InventoryItem.COL_SALE_QTY]);
tempRow[InventoryItem.COL_DB_LENGTH] = Tools.zeroNonNumericString(tempRow[InventoryItem.COL_DB_LENGTH]) + Tools.zeroNonNumericString(dr[InventoryItem.COL_DB_LENGTH]);
tempRow[InventoryItem.COL_SALE_SUBTOTAL] = Tools.zeroNonNumericString(tempRow[InventoryItem.COL_SALE_SUBTOTAL]) + Tools.zeroNonNumericString(dr[InventoryItem.COL_SALE_SUBTOTAL]);
tempRow[InventoryItem.COL_SALE_ADJUSTEDPRICE] = Tools.zeroNonNumericString(tempRow[InventoryItem.COL_SALE_SUBTOTAL]) / Tools.zeroNonNumericString(tempRow[InventoryItem.COL_DB_LENGTH]);
dtSummary.AcceptChanges();
}
else
{
if (!string.IsNullOrEmpty(dr[InventoryItem.COL_DB_INVENTORY_ID].ToString()))
{
dtSummary.Rows.Add(dr.ItemArray);
}
}
i++;
}
return(dtSummary);
}
public static DataTable compileSummaryData(DataTable dt, bool showColor)
{
DataTable dtSummary = dt.Clone(); //copy table structure without rows of data
Tools.setDataTablePrimaryKey(dtSummary, Inventory.COL_DB_ID);
DataRow tempRow;
decimal totalLength = 0;
foreach (DataRow dr in dt.Rows)
{
tempRow = findCombination(dtSummary, dr, showColor);
if (tempRow != null)
{
totalLength = Tools.zeroNonNumericString(tempRow[Inventory.COL_AVAILABLEITEMLENGTH]) + Tools.zeroNonNumericString(dr[Inventory.COL_AVAILABLEITEMLENGTH]);
if (totalLength > 0)
{
tempRow[Inventory.COL_DB_BUYPRICE] =
((Tools.zeroNonNumericString(tempRow[Inventory.COL_DB_BUYPRICE]) * Tools.zeroNonNumericString(tempRow[Inventory.COL_AVAILABLEITEMLENGTH]))
+ (Tools.zeroNonNumericString(dr[Inventory.COL_DB_BUYPRICE]) * Tools.zeroNonNumericString(dr[Inventory.COL_AVAILABLEITEMLENGTH])))
/ totalLength; //calculate average buy price
tempRow[Inventory.COL_BUYVALUE] = Tools.zeroNonNumericString(tempRow[Inventory.COL_DB_BUYPRICE]) * totalLength; //calculate buy value
tempRow[Inventory.COL_SELLVALUE] = Tools.zeroNonNumericString(tempRow[Inventory.COL_SELLVALUE]) + (Tools.zeroNonNumericString(dr[Inventory.COL_SELLPRICE]) * Tools.zeroNonNumericString(dr[Inventory.COL_AVAILABLEITEMLENGTH])); //calculate buy value
tempRow[Inventory.COL_AVAILABLEITEMLENGTH] = totalLength;
tempRow[Inventory.COL_AVAILABLEQTY] = Tools.zeroNonNumericString(tempRow[Inventory.COL_AVAILABLEQTY]) + Tools.zeroNonNumericString(dr[Inventory.COL_AVAILABLEQTY]);
}
dtSummary.AcceptChanges();
}
else
{
tempRow = dtSummary.Rows.Add(dr.ItemArray);
tempRow[Inventory.COL_BUYVALUE] = Tools.zeroNonNumericString(tempRow[Inventory.COL_DB_BUYPRICE]) * Tools.zeroNonNumericString(tempRow[Inventory.COL_AVAILABLEITEMLENGTH]); //calculate buy value
tempRow[Inventory.COL_SELLVALUE] = Tools.zeroNonNumericString(tempRow[Inventory.COL_SELLPRICE]) * Tools.zeroNonNumericString(tempRow[Inventory.COL_AVAILABLEITEMLENGTH]); //calculate sell value
dtSummary.AcceptChanges();
}
}
return(dtSummary);
}
public static bool update(Guid saleID, Guid?Customers_Id, Guid?Vendors_Id, DataTable saleItems, Guid?transportID, decimal shippingCost, string notes)
{
Sale objOld = new Sale(saleID);
DataTable objOldItems = Tools.setDataTablePrimaryKey(SaleItem.getItems(saleID), SaleItem.COL_ID);
string customerInfo = objOld.customer_info;
//generate log description
string log = "";
//update customer/vendor info
Customer newCustomer = new Customer(Customers_Id);
if (objOld.customer_id != newCustomer.ID)
{
log = Util.appendChange(log, objOld.Customers_Name, newCustomer.Name, "Customer: '{0}' to '{1}'");
log = Util.appendChange(log, objOld.customer_info, newCustomer.Info, "Info: '{0}' to '{1}'");
}
Vendor newVendor = new Vendor(Vendors_Id);
if (objOld.Vendors_Id != Vendors_Id)
{
log = Util.appendChange(log, objOld.Vendors_Name, newVendor.Name, "Vendor: '{0}' to '{1}'");
log = Util.appendChange(log, objOld.customer_info, newVendor.Info, "Info: '{0}' to '{1}'");
}
if (Customers_Id != null)
{
customerInfo = newCustomer.Info;
}
else
{
customerInfo = newVendor.Info;
}
log = ActivityLog.appendChange(log, objOld.TransportName, new Transport(transportID).Name, "Angkutan: '{0}' to '{1}'");
log = ActivityLog.appendChange(log, objOld.ShippingCost.ToString("N2"), shippingCost.ToString("N2"), "Shipping: '{0}' to '{1}'");
log = ActivityLog.appendChange(log, objOld.notes, notes, "Notes: '{0}' to '{1}'");
DataRow row;
Guid id;
decimal oldAdjustment, newAdjustment;
for (int i = saleItems.Rows.Count - 1; i >= 0; i--)
{
row = saleItems.Rows[i];
id = DBUtil.parseData <Guid>(row, SaleItem.COL_ID);
newAdjustment = DBUtil.parseData <decimal>(row, SaleItem.COL_ADJUSTMENT);
oldAdjustment = DBUtil.parseData <decimal>(objOldItems.Rows.Find(id), SaleItem.COL_ADJUSTMENT);
if (oldAdjustment != newAdjustment)
{
log = ActivityLog.appendChange(log, oldAdjustment, newAdjustment, "Inventory Item " + DBUtil.parseData <string>(row, SaleItem.COL_BARCODE) + " adjustment: {0:N2} to {1:N2}");
row[SaleItem.COL_ADJUSTMENT] = newAdjustment;
}
else
{
//remove if has no change
saleItems.Rows.RemoveAt(i);
}
}
if (!string.IsNullOrEmpty(log))
{
//format notes
if (string.IsNullOrWhiteSpace(notes))
{
notes = null;
}
else
{
notes = string.Format("{0:MM/dd/yy}-{1}: {2}", DateTime.Now, GlobalData.UserAccount.name, notes);
}
SqlQueryResult result = DBConnection.query(
false,
DBConnection.ActiveSqlConnection,
QueryTypes.ExecuteNonQuery,
"sale_update",
new SqlQueryParameter(COL_ID, SqlDbType.UniqueIdentifier, saleID),
new SqlQueryParameter(COL_CUSTOMER_ID, SqlDbType.UniqueIdentifier, Util.wrapNullable(Customers_Id)),
new SqlQueryParameter(COL_DB_Vendors_Id, SqlDbType.UniqueIdentifier, Util.wrapNullable(Vendors_Id)),
new SqlQueryParameter(COL_DB_CUSTOMERINFO, SqlDbType.VarChar, Util.wrapNullable(customerInfo)),
new SqlQueryParameter(COL_DB_TRANSPORTID, SqlDbType.UniqueIdentifier, Util.wrapNullable(transportID)),
new SqlQueryParameter(COL_DB_SHIPPINGCOST, SqlDbType.Decimal, shippingCost),
new SqlQueryParameter(COL_DB_Notes, SqlDbType.VarChar, Util.wrapNullable(notes))
);
if (!result.IsSuccessful)
{
return(false);
}
else
{
ActivityLog.submit(saleID, "Update: " + log);
if (saleItems.Rows.Count > 0)
{
SaleItem.updateItems(getListOfSaleItemsForUpdate(saleID, saleItems));
}
}
}
return(true);
}