public void WMSDetailsInsert(string tripId, string orderId, string dropSequence, string expectedStart)
{
var param = new Dictionary <string, object>();
List <string> pk = new List <string>
{
"order_id",
"status"
};
List <string> pv = new List <string>
{
orderId,
"FOR SCHEDULING"
};
param.Add("trip", tripId);
param.Add("order_id", orderId);
param.Add("drop_sequence", dropSequence);
WMSSqlScript.AppendLine(DataSupport.GetUpsert("ReleaseTripDetails", param, "trip"));
param.Clear();
param.Add("status", "FOR PICKING");
param.Add("order_id", orderId);
param.Add("scheduled_release_date", expectedStart);
WMSSqlScript.AppendLine(DataSupport.GetUpdate("ReleaseOrders", param, pk, pv));
param.Clear();
//WMSSqlScript.AppendLine(DataSupport.GetDelete("ReleaseTripDetails", "trip", tripId));
//WMSSqlScript.AppendLine(DataSupport.GetDelete("ReleaseTrips", "trip_id", tripId));
}
public void WMSHeaderInsert(string tripId, string incharge)
{
var param = new Dictionary <string, object>();
param.Add("trip_id", tripId);
param.Add("authorized_receiver", incharge);
param.Add("tms_name", DataSupport.GetTMSCode());
WMSSqlScript.AppendLine(DataSupport.GetUpsert("ReleaseTrips", param, "trip_id"));
}
public static String UpdateLocationLedger(DataTable dt)
{
String sql = "";
foreach (DataRow row in dt.Rows)
{
sql += DataSupport.GetUpsert("LocationLedger", Utils.ToDict(
"location", row["location"]
, "transaction_datetime", row["transaction_datetime"]
, "transaction_type", row["transaction_type"]
, "transaction_name", row["transaction_name"]
, "transaction_id", row["transaction_id"]
), "location", "transaction_datetime", "transaction_id") + "\r\n\r\n";
}
return(sql);
}
public void InsertTrip(TripUnit unit)
{
var param = new Dictionary <string, object>
{
{ "trip_id", unit.TripId },
{ "vehicle", unit.VehicleId },
{ "in_charge", unit.Incharge },
{ "expected_start", unit.ExpectedStart },
{ "expected_end", unit.ExpectedEnd },
{ "actual_start", unit.ActualStart },
{ "actual_end", unit.ActualEnd },
{ "cost", unit.Cost },
{ "route", unit.RouteId },
{ "last_updated_on", unit.LastUpdated }
};
TMSSqlScript.AppendLine(DataSupport.GetUpsert("Trips", param, "trip_id"));
}
public void InsertTripOrder(TripOrderUnit unit)
{
var param = new Dictionary <string, object>
{
{ "trip", unit.TripId },
{ "order_id", unit.OrderId },
{ "client", unit.ClientId },
{ "customer", unit.CustomerId },
{ "customer_delivery_address", unit.CustomerDeliveryAddress },
{ "status", unit.Status },
{ "remarks", unit.Remarks },
{ "reference", unit.Reference },
{ "reference_date", unit.ReferenceDate },
{ "oms", unit.OrderManagementSystem },
{ "doc_value", unit.DocumentValue },
{ "drop_sequence", unit.DropSequence },
};
TMSSqlScript.AppendLine(DataSupport.GetUpsert("TripOrders", param, "trip", "order_id"));
}
private void btnChangeRoute_Click(object sender, EventArgs e)
{
var cells = header_grid.SelectedCells;
StringBuilder sql = new StringBuilder();
foreach (DataGridViewCell cell in cells)
{
string vehicle = cell.OwningRow.Cells[0].Value.ToString();
string date = cell.OwningColumn.Name;
Dictionary <string, Object> dict = new Dictionary <string, object>();
dict.Add("date", date);
dict.Add("vehicle", vehicle);
dict.Add("route", cbRoutes.Text);
sql.Append(DataSupport.GetUpsert("RouteSchedules", dict, "date", "vehicle"));
}
DataSupport.RunNonQuery(sql.ToString(), IsolationLevel.ReadCommitted);
MessageBox.Show("Success");
LoadRoutes();
}
public static String ToUpsert(this DataTable dt, String table, params String[] primary_keys)
{
StringBuilder result = new StringBuilder();
var list = primary_keys.ToList();
foreach (DataRow row in dt.Rows)
{
Dictionary <String, Object> insert_list = new Dictionary <string, object>();
List <String> primary_list = new List <string>();
foreach (DataColumn col in dt.Columns)
{
insert_list.Add(col.ColumnName, row[col]);
if (list.Contains(col.ColumnName))
{
primary_list.Add(col.ColumnName);
}
}
result.Append(DataSupport.GetUpsert(table, insert_list, primary_list));
}
return(result.ToString());
}
private void btnSave_Click(object sender, EventArgs e)
{
String sql = "";
String putaway_id = txtPutawayID.Text;
// Update the Putaway Details for those declared complete
foreach (DataGridViewRow row in putaway_details_grid.Rows)
{
sql += "UPDATE PutawayDetails SET actual_qty = '" + row.Cells["Quantity"].Value.ToString() + "' WHERE putaway='" + txtPutawayID.Text + "' AND product = '" + row.Cells["product"].Value.ToString() + "' AND uom='" + row.Cells["uom"].Value.ToString() + "' AND lot_no='" + row.Cells["lot no"].Value.ToString() + "' AND expiry='" + row.Cells["expiry"].Value.ToString() + "' ";
}
// Update the Putaway Details for those declared returneds
foreach (DataGridViewRow row in returns_grid.Rows)
{
sql += "UPDATE PutawayDetails SET actual_qty = expected_qty - " + row.Cells["qty"].Value.ToString() + " WHERE putaway='" + txtPutawayID.Text + "' AND product = '" + row.Cells["product"].Value.ToString() + "' AND uom='" + row.Cells["uom"].Value.ToString() + "' AND lot_no='" + row.Cells["lot_no"].Value.ToString() + "' AND expiry='" + row.Cells["expiry"].Value.ToString() + "' ";
}
// Transactions Ledger
sql += DataSupport.GetInsert("LocationLedger", Utils.ToDict(
"location", txtContainer.Text
, "transaction_datetime", DateTime.Now.ToString()
, "transaction_type", "OUT"
, "transaction_name", "PUTAWAY_DECLARE_INCOMPLETE"
, "transaction_id", putaway_id
));
foreach (DataGridViewRow row in putaway_details_grid.Rows)
{
sql += DataSupport.GetUpsert("LocationLedger", Utils.ToDict(
"location", row.Cells["location"].Value.ToString()
, "transaction_datetime", DateTime.Now.ToString()
, "transaction_type", "IN"
, "transaction_name", "PUTAWAY_DECLARE_INCOMPLETE"
, "transaction_id", putaway_id
), "location", "transaction_datetime", "transaction_id");
}
foreach (DataGridViewRow row in returns_grid.Rows)
{
sql += DataSupport.GetUpsert("LocationLedger", Utils.ToDict(
"location", "STAGING-IN"
, "transaction_datetime", DateTime.Now.ToString()
, "transaction_type", "IN"
, "transaction_name", "PUTAWAY_DECLARE_INCOMPLETE"
, "transaction_id", putaway_id
), "location", "transaction_datetime", "transaction_id");
}
// Update Location Products Ledger
foreach (DataGridViewRow row in putaway_details_grid.Rows)
{
sql += "UPDATE LocationProductsLedger SET qty = qty - " + row.Cells["Quantity"].Value.ToString() + " WHERE location = '" + txtContainer.Text + "' AND product='" + row.Cells["product"].Value.ToString() + "' AND uom ='" + row.Cells["uom"].Value.ToString() + "' AND lot_no = '" + row.Cells["lot no"].Value.ToString() + "' AND expiry='" + row.Cells["expiry"].Value.ToString() + "'";
if (FAQ.IsNewLine(row.Cells["location"].Value.ToString(), row.Cells["product"].Value.ToString(), row.Cells["uom"].Value.ToString(), row.Cells["lot no"].Value.ToString(), row.Cells["expiry"].Value.ToString()))
{
sql += "UPDATE LocationProductsLedger SET qty = qty + " + row.Cells["Quantity"].Value.ToString() + " WHERE location = '" + row.Cells["location"].Value.ToString() + "' AND product='" + row.Cells["product"].Value.ToString() + "' AND uom ='" + row.Cells["uom"].Value.ToString() + "' AND lot_no = '" + row.Cells["lot no"].Value.ToString() + "' AND expiry='" + row.Cells["expiry"].Value.ToString() + "'";
}
else
{
sql += DataSupport.GetInsert("LocationProductsLedger", Utils.ToDict(
"location", row.Cells["location"].Value.ToString()
, "product", row.Cells["product"].Value.ToString()
, "qty", row.Cells["Quantity"].Value.ToString()
, "uom", row.Cells["uom"].Value.ToString()
, "lot_no", row.Cells["lot no"].Value.ToString()
, "expiry", row.Cells["expiry"].Value.ToString()
));
}
}
foreach (DataGridViewRow row in returns_grid.Rows)
{
sql += "UPDATE LocationProductsLedger SET qty = qty - " + row.Cells["qty"].Value.ToString() + " WHERE location = '" + txtContainer.Text + "' AND product='" + row.Cells["product"].Value.ToString() + "' AND uom ='" + row.Cells["uom"].Value.ToString() + "' AND lot_no = '" + row.Cells["lot_no"].Value.ToString() + "' AND expiry='" + row.Cells["expiry"].Value.ToString() + "'";
if (FAQ.IsNewLine("STAGING-IN", row.Cells["product"].Value.ToString(), row.Cells["uom"].Value.ToString(), row.Cells["lot_no"].Value.ToString(), row.Cells["expiry"].Value.ToString()))
{
sql += "UPDATE LocationProductsLedger SET qty = qty + " + row.Cells["qty"].Value.ToString() + " WHERE location = '" + "STAGING-IN" + "' AND product='" + row.Cells["product"].Value.ToString() + "' AND uom ='" + row.Cells["uom"].Value.ToString() + "' AND lot_no = '" + row.Cells["lot_no"].Value.ToString() + "' AND expiry='" + row.Cells["expiry"].Value.ToString() + "'";
}
else
{
sql += DataSupport.GetInsert("LocationProductsLedger", Utils.ToDict(
"location", "STAGING-IN"
, "product", row.Cells["product"].Value.ToString()
, "qty", row.Cells["qty"].Value.ToString()
, "uom", row.Cells["uom"].Value.ToString()
, "lot_no", row.Cells["lot_no"].Value.ToString()
, "expiry", row.Cells["expiry"].Value.ToString()
));
}
}
DataSupport.RunNonQuery(sql, IsolationLevel.ReadCommitted);
}
private void btnPrintPreview_Click(object sender, EventArgs e)
{
String now = DateTime.Now.ToString();
String phcount = parent.phcount_id;
RollbackDataSupport ds = new RollbackDataSupport();
// Save to Transaction Tables
String sql = "";
sql += " UPDATE PhysicalCounts SET finished_on = '" + now + "' WHERE phcount_id='" + phcount + "'; ";
DataTable dt = ds.RunDataSet("SELECT * FROM PhysicalCountDetailItems WHERE phcount='" + phcount + "'").Tables[0];
foreach (DataGridViewRow item_row in parent.header_grid.Rows)
{
if (item_row.Cells["expected"].Value.ToString() == "NO")
{
sql += DataSupport.GetUpsert("PhysicalCountDetails", Utils.ToDict(
"phcount", phcount
, "location", item_row.Cells["location"].Value.ToString()
, "status", "ACTIVE"
), "phcount", "location");
sql += DataSupport.GetInsert("PhysicalCountDetailItems", Utils.ToDict(
"phcount", phcount
, "location", item_row.Cells["location"].Value.ToString()
, "product", item_row.Cells["product"].Value.ToString()
, "uom", item_row.Cells["uom"].Value.ToString()
, "lot_no", item_row.Cells["lot_no"].Value.ToString()
, "expiry", item_row.Cells["Expiry"].Value.ToString()
, "expected_qty", 0
, "actual_qty", item_row.Cells["actual_qty"].Value.ToString()
, "line", dt.Rows.Count + 1
));
}
else
{
foreach (DataRow row in dt.Rows)
{
// Skip if expected is empty
if (row["product"].ToString() == "EMPTY")
{
continue;
}
if (row["location"].ToString() == item_row.Cells["location"].Value.ToString() &&
row["product"].ToString() == item_row.Cells["product"].Value.ToString() &&
row["uom"].ToString() == item_row.Cells["uom"].Value.ToString() &&
row["lot_no"].ToString() == item_row.Cells["lot_no"].Value.ToString() &&
DateTime.Parse(row["expiry"].ToString()).ToShortDateString() == item_row.Cells["Expiry"].Value.ToString()
)
{
sql += @"UPDATE PhysicalCountDetailItems SET actual_qty = '" + item_row.Cells["actual_qty"].Value.ToString() + @"'
WHERE phcount = '" + phcount + @"'
AND location = '" + row["location"].ToString() + @"'
AND line = '" + row["line"].ToString() + @"'
";
break;
}
}
}
}
ds.RunNonQuery(sql);
// Update Ledgers
String update_sql = "";
{
DataTable details_dt = ds.RunDataSet("SELECT * FROM PhysicalCountDetailItems WHERE phcount='" + phcount + "'").Tables[0];
foreach (DataRow item_row in details_dt.Rows)
{
if (item_row["shortage"].ToString() != "0")
{
// Update Transaction Ledger
{
// Out with the location
DataTable outsDT = LedgerSupport.GetLocationLedgerDT();
outsDT.Rows.Add(item_row["location"].ToString(), now, "OUT", "PHYSICAL_COUNT", phcount);
update_sql += LedgerSupport.UpdateLocationLedger(outsDT);
}
// Update Location Products Ledger
{
// Out with the location
DataTable outsDT = LedgerSupport.GetLocationProductsLedgerDT();
outsDT.Rows.Add(item_row["location"].ToString(), item_row["product"].ToString(), int.Parse(item_row["shortage"].ToString()) * -1, item_row["uom"].ToString(), item_row["lot_no"].ToString(), item_row["expiry"].ToString());
update_sql += LedgerSupport.UpdateLocationProductsLedger(outsDT);
}
// Update For Resolution
update_sql += DataSupport.GetInsert("ForResolutions", Utils.ToDict(
"trans_source", "PHYSICAL_COUNT"
, "trans_id", phcount
, "detected_on", now
, "product", item_row["product"].ToString()
, "uom", item_row["uom"].ToString()
, "lot_no", item_row["lot_no"].ToString()
, "expiry", item_row["expiry"].ToString()
, "location", item_row["location"].ToString()
, "variance_type", "SHORTAGE"
, "variance_qty", item_row["shortage"].ToString()
, "status", "FOR RESOLUTION"
, "line", details_dt.Rows.IndexOf(item_row) + 1
));
}
else if (item_row["overage"].ToString() != "0")
{
// Update Transaction Ledger
{
// In with the "found" location
DataTable insDT = LedgerSupport.GetLocationLedgerDT();
insDT.Rows.Add(item_row["location"].ToString(), now, "IN", "PHYSICAL_COUNT", phcount);
update_sql += LedgerSupport.UpdateLocationLedger(insDT);
}
// Update Location Products Ledger
{
// In with the "found" location
DataTable insDT = LedgerSupport.GetLocationProductsLedgerDT();
insDT.Rows.Add(item_row["location"].ToString(), item_row["product"].ToString(), item_row["overage"].ToString(), item_row["uom"].ToString(), item_row["lot_no"].ToString(), item_row["expiry"].ToString());
update_sql += LedgerSupport.UpdateLocationProductsLedger(insDT);
}
// Update For Resolution
update_sql += DataSupport.GetInsert("ForResolutions", Utils.ToDict(
"trans_source", "PHYSICAL_COUNT"
, "trans_id", phcount
, "detected_on", now
, "product", item_row["product"].ToString()
, "uom", item_row["uom"].ToString()
, "lot_no", item_row["lot_no"].ToString()
, "expiry", item_row["expiry"].ToString()
, "location", item_row["location"].ToString()
, "variance_type", "OVERAGE"
, "variance_qty", item_row["overage"].ToString()
, "status", "FOR RESOLUTION"
, "line", details_dt.Rows.IndexOf(item_row) + 1
));
}
}
}
if (update_sql != "")
{
ds.RunNonQuery(update_sql);
}
ds.CommitData();
MessageBox.Show("Success");
// webBrowser1.DocumentText = webBrowser1.DocumentText.Replace("(issued on save)", id);
btnPrintPreview.Text = "Print";
btnCancel.Visible = false;
}