public static String UpdateLocationProductsLedger(DataTable dt, Boolean is_overwrite)
{
String sql = "";
if (!is_overwrite)
{
if (dt.Rows.Count == 0)
{
return("");
}
if (dt.Rows[0]["reserved_qty"].ToString() == "")
{
dt = ConsolidateDT(dt, "location", "product", "uom", "lot_no", "expiry");
}
foreach (DataRow row in dt.Rows)
{
if (FAQ.IsNewLine(row["location"].ToString(), row["product"].ToString(), row["uom"].ToString(), row["lot_no"].ToString(), row["expiry"].ToString()))
{
sql += " UPDATE LocationProductsLedger SET qty = qty + " + row["qty"].ToString() + " WHERE location = '" + row["location"].ToString() + "' AND product='" + row["product"].ToString() + "' AND uom ='" + row["uom"].ToString() + "' AND lot_no = '" + row["lot_no"].ToString() + "' AND expiry='" + row["expiry"].ToString() + "'\r\n\r\n\r\n\r\n";
}
else
{
sql += DataSupport.GetInsert("LocationProductsLedger", Utils.ToDict(
"location", row["location"].ToString()
, "product", row["product"].ToString()
, "qty", row["qty"].ToString()
, "uom", row["uom"].ToString()
, "lot_no", row["lot_no"].ToString()
, "expiry", row["expiry"].ToString()
));
}
if (row["location"].ToString() == "STAGING-OUT" || row["location"].ToString() == "FOR-RESOLUTION" || row["location"].ToString() == "RELEASED" || row["location"].ToString() == "CANCELLED_PALLET")
{
sql += " UPDATE LocationProductsLedger SET reserved_qty = qty WHERE location = '" + row["location"].ToString() + "' AND product='" + row["product"].ToString() + "' AND uom ='" + row["uom"].ToString() + "' AND lot_no = '" + row["lot_no"].ToString() + "' AND expiry='" + row["expiry"].ToString() + "'\r\n\r\n\r\n\r\n";
}
if (row["reserved_qty"].ToString() != "")
{
sql += " UPDATE LocationProductsLedger SET reserved_qty = reserved_qty + " + row["reserved_qty"].ToString() + " WHERE location = '" + row["location"].ToString() + "' AND product='" + row["product"].ToString() + "' AND uom ='" + row["uom"].ToString() + "' AND lot_no = '" + row["lot_no"].ToString() + "' AND expiry='" + row["expiry"].ToString() + "'\r\n\r\n\r\n\r\n";
}
if (row["to_be_picked_qty"].ToString() != "")
{
sql += " UPDATE LocationProductsLedger SET to_be_picked_qty = to_be_picked_qty + " + row["to_be_picked_qty"].ToString() + " WHERE location = '" + row["location"].ToString() + "' AND product='" + row["product"].ToString() + "' AND uom ='" + row["uom"].ToString() + "' AND lot_no = '" + row["lot_no"].ToString() + "' AND expiry='" + row["expiry"].ToString() + "'\r\n\r\n\r\n\r\n";
}
sql += " UPDATE LocationProductsLedger SET reserved_qty = qty WHERE location = (SELECT location_id FROM Locations WHERE location = location_id AND type='STORAGE - BAD STOCKS'); ";
}
}
return(sql);
}
public static decimal GetPrice(String client, String product, String uom)
{
decimal result = 0;
// Check Promo Price Lists (to be done later)
// Check Default Price List
DataSupport oms = Connection.GetOMSConnection;
result = decimal.Parse(oms.ExecuteDataSet($"SELECT price FROM itemPrice WHERE client = '{client}' product = '{product}' AND uom ='{uom}'").Tables[0].Rows[0][0].ToString());
return(result);
}
public static Dictionary <String, DataRow> BuildIndexs(String sql, String ColumnName, String columnUom)
{
DataTable table = DataSupport.RunDataSet(sql).Tables[0];
Dictionary <String, DataRow> index = new Dictionary <String, DataRow>(table.Rows.Count);
foreach (DataRow Row in table.Rows)
{
index[Row[ColumnName].ToString()] = Row;
index[Row[columnUom].ToString()] = Row;
}
return(index);
}
public static int GetSequence(String trip_id)
{
for (int i = 1; i < 1000; i++)
{
DataTable dt = DataSupport.RunDataSet($"SELECT drop_sequence FROM TripOrders WHERE trip = '{ trip_id }' AND drop_sequence='{ i }'").Tables[0];
if (dt.Rows.Count == 0)
{
return(i);
}
}
return(999);
}
public void UpdateOutgoingShipmentRequest(string pk, string pkValue)
{
var param = new Dictionary <string, object>();
List <string> pv = new List <string> {
pkValue
};
param.Add("out_shipment_id", pkValue);
param.Add("status", "FOR RELEASING");
OMSSqlScript.AppendLine(DataSupport.GetUpdate("OutgoingShipmentRequests", param, pk));
}
private void saveUOM()
{
StringBuilder sql = new StringBuilder();
String id = DataSupport.RunDataSet("Select max(id) from Products").Tables[0].Rows[0][0].ToString();
foreach (DataGridViewRow row in dataGridView1.Rows)
{
if (dataGridView1.Rows.IndexOf(row) == dataGridView1.Rows.Count - 1)
{
break;
}
Dictionary <String, Object> details = new Dictionary <String, Object>();
details.Add("productID", id);
details.Add("prodCode", txtCode.Text);
if (string.IsNullOrEmpty(row.Cells[colUOM.Name].Value as string))
{
details.Add("uom", "");
}
else
{
details.Add("uom", row.Cells[colUOM.Name].Value.ToString());
}
if (string.IsNullOrEmpty(row.Cells[colUOM.Name].Value as string))
{
details.Add("qty", "");
}
else
{
details.Add("qty", row.Cells[colQty.Name].Value.ToString());
}
if (string.IsNullOrEmpty(row.Cells[colPriceType.Name].Value as string))
{
details.Add("priceTypeID", "");
}
else
{
details.Add("priceTypeID", row.Cells[colPriceType.Name].Value.ToString());
}
if (string.IsNullOrEmpty(row.Cells[colPriceType.Name].Value as string))
{
details.Add("unitPrice", "");
}
else
{
details.Add("unitPrice", row.Cells[colPrice.Name].Value.ToString());
}
details.Add("dateStamp", DateTime.Now);
sql.Append(DataSupport.GetInsert("itemPrice", details));
}
DataSupport.RunNonQuery(sql.ToString(), IsolationLevel.ReadCommitted);
MessageBox.Show("Success");
}
private void btnStockCheck_Click(object sender, EventArgs e)
{
DataTable ordersDT = DataSupport.RunDataSet("SELECT * FROM ReleaseOrders WHERE status = 'FOR STOCK CHECKING'").Tables[0];
foreach (DataRow row in ordersDT.Rows)
{
var order_id = row["order_id"].ToString();
var result = FAQ.DoesOrderHaveStocks(order_id);
if (result == false)
{
MessageBox.Show("Can't Reserve order " + order_id);
continue;
}
String sql = " UPDATE ReleaseOrders SET status = 'FOR SCHEDULING' WHERE order_id = '" + order_id + "'; ";
DataTable detailsDT = FAQ.GetOrderDetails(order_id);
foreach (DataRow detail in detailsDT.Rows)
{
var dt = FAQ.WhereAreProductsInWarehouse(detail["product"].ToString(), detail["uom"].ToString());
int qty_to_be_reserved = int.Parse(detail["qty"].ToString());
foreach (DataRow selected_row in dt.Rows)
{
int qty_in_location = int.Parse(selected_row["available_qty"].ToString());
int qty_reserved = qty_in_location;
if (qty_to_be_reserved < qty_in_location)
{
qty_reserved = qty_to_be_reserved;
}
sql += " UPDATE LocationProductsLedger SET reserved_qty = reserved_qty + " + qty_reserved +
" WHERE product = '" + detail["product"].ToString() + "' AND uom = '" + detail["uom"].ToString() + "' " +
" AND lot_no = '" + selected_row["lot_no"].ToString() + "' AND expiry = '" + selected_row["expiry"].ToString() + "' " +
" AND location='" + selected_row["location"].ToString() + "'; ";
qty_to_be_reserved -= qty_reserved;
if (qty_to_be_reserved <= 0)
{
break;
}
}
}
DataSupport.RunNonQuery(sql, IsolationLevel.ReadCommitted);
MessageBox.Show("It works");
}
}
private void cbxwarehouse_SelectedValueChanged(object sender, EventArgs e)
{
try
{
var dt = DataSupport.RunDataSet("SELECT transport_id FROM [TransportProviders] where description = '" + cbxwarehouse.Text + "' ").Tables[0];
foreach (DataRow row in dt.Rows)
{
lblWcode.Text = row["transport_id"].ToString();
}
}
catch
{ }
}
private void AddTripDialog_Load(object sender, EventArgs e)
{
UISetter.SetButtonAppearance(btnAdd);
txtStart.Value = DateTime.Now.AddDays(1);
txtEnd.Value = DateTime.Now.AddDays(1);
var dt = DataSupport.RunDataSet("SELECT * FROM Vehicles").Tables[0];
foreach (DataRow row in dt.Rows)
{
txtVehicle.Items.Add(row["vehicle_id"]);
}
}
private void LoadData()
{
StringBuilder search = new StringBuilder();
//if (cbosearchby.SelectedIndex == 0)
// search.AppendFormat("SELECT * FROM LocationProductsLedger WHERE product = '{0}' order by product,location", txtsearch.Text);
//else if (cbosearchby.SelectedIndex == 0)
// search.AppendFormat("SELECT * FROM LocationProductsLedger WHERE product = '{0}' order by product,location", txtsearch.Text);
foreach (DataRow dRow_locprod in DataSupport.RunDataSet(search.ToString()).Tables[0].Rows)
{
}
}
private void cbxWarehouse_SelectedValueChanged(object sender, EventArgs e)
{
try
{
var dt = DataSupport.RunDataSet("SELECT warehouseCode FROM Warehouses where warehouse_id = '" + cbxWarehouse.Text + "' ").Tables[0];
foreach (DataRow row in dt.Rows)
{
txtCodeS.Text = row["warehouseCode"].ToString();
}
}
catch
{ }
}
private void TripCalendarWindow_Load(object sender, EventArgs e)
{
UISetter.SetButtonAppearance(btnTrip, btnSearch, btnManageTrip, btnChangeRoute, btnRoute, btnDrop);
UISetter.SetGridAppearance(header_grid);
txtEnd.Value = DateTime.Now.AddDays(14);
var dt = DataSupport.RunDataSet("SELECT route_id FROM Routes").Tables[0];
foreach (DataRow row in dt.Rows)
{
cbRoutes.Items.Add(row[0].ToString());
}
}
private void DeclareBadStockWindow_Load(object sender, EventArgs e)
{
DataTable dt = DataSupport.RunDataSet(@"SELECT Declaration_id [Declaration ID], declared_by [Declared By], declared_on[Declared On], '' [Days Ago], Status FROM BadStockDeclarations").Tables[0];
foreach (DataRow row in dt.Rows)
{
DateTime declared_on = DateTime.Parse(row["Declared On"].ToString());
row["Declared On"] = declared_on.ToShortDateString();
row["Days Ago"] = DateTime.Now.Subtract(declared_on).Days;
}
header_grid.DataSource = dt;
}
private void btnDeclareExpiredStocks_Click(object sender, EventArgs e)
{
var row = header_grid.SelectedRows[0];
DataSupport.RunNonQuery(@" UPDATE LocationProductsLedger
SET expiry_status='NEAR EXPIRY'
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() + @"'
");
LoadData();
}
private void cbxCustName_SelectedValueChanged(object sender, EventArgs e)
{
try
{
var dt = DataSupport.RunDataSet("SELECT custCode,address,contactNo,tinNo FROM TransportCustomers where customer = '" + cbxCustName.Text + "' ").Tables[0];
foreach (DataRow row in dt.Rows)
{
txtCodeD.Text = row["custCode"].ToString();
txtAddressD.Text = row["address"].ToString();
}
}
catch
{ }
}
private void uomPriceType()
{
{
colUom.DataSource = DataSupport.RunDataSet("SELECT * FROM Uom").Tables[0];
colUom.DisplayMember = "uom";
colUom.ValueMember = "uom";
}
{
colPriceType.DataSource = DataSupport.RunDataSet("SELECT * FROM PriceType").Tables[0];
colPriceType.DisplayMember = "priceType";
colPriceType.ValueMember = "priceTypeID";
}
}
private void cbxReceived_SelectedValueChanged(object sender, EventArgs e)
{
try
{
var dt = DataSupport.RunDataSet(" select custCode,address from TransportCustomers where customer = '" + cbxReceived.Text + "' ").Tables[0];
foreach (DataRow row in dt.Rows)
{
txtWhseR.Text = row["custCode"].ToString();
txtAddressR.Text = row["address"].ToString();
}
}
catch
{ }
}
private void ManageTrips_Load(object sender, EventArgs e)
{
UISetter.SetGridAppearance(header_grid);
foreach (DataRow row in DataSupport.RunDataSet($"SELECT * FROM Trips WHERE trip_id = '{ trip_id }'; ").Tables[0].Rows)
{
txtTrip.Text = row["trip_id"].ToString();
txtVehicle.Text = row["vehicle"].ToString();
txtTripStart.Text = Convert.ToDateTime(row["expected_start"].ToString()).ToShortDateString();
txtTripEnd.Text = Convert.ToDateTime(row["expected_end"].ToString()).ToShortDateString();
txtRoute.Text = row["route"].ToString();
txtLastUpdatedOn.Text = row["last_updated_on"].ToString();
}
LoadTable();
}
private void DeclareIncompleteWindow_Load(object sender, EventArgs e)
{
txtScan.Select();
DataTable dt = DataSupport.RunDataSet(@"SELECT
[Product]
,[Uom]
,lot_no [Lot No]
,[Expiry]
,expected_qty[Quantity]
,location
FROM[PutawayDetails] WHERE putaway = @id", "id", txtPutawayID.Text).Tables[0];
putaway_details_grid.DataSource = dt;
}
private void button1_Click(object sender, EventArgs e)
{
StringBuilder sql = new StringBuilder();
Dictionary <String, Object> header = new Dictionary <string, object>();
header.Add("employee_id", txtId.Text);
header.Add("name", txtname.Text);
header.Add("position", txtposition.Text);
header.Add("password", SecuritySupport.Encrypt(txtpass.Text));
header.Add("usertype", cbxType.Text);
sql.Append(DataSupport.GetInsert("[employees]", header));
DataSupport.RunNonQuery(sql.ToString(), IsolationLevel.ReadCommitted);
}
private void Sales_Load(object sender, EventArgs e)
{
button1.Visible = false;
{
product();
}
{
var dt = DataSupport.RunDataSet("SELECT custCode FROM base_customer").Tables[0];
foreach (DataRow row in dt.Rows)
{
cbxCustCode.Items.Add(row[0].ToString());
}
}
}
private void btnAdd_Click(object sender, EventArgs e)
{
var row = header_grid.CurrentRow;
// Update Itself
{
Dictionary <String, Object> dict = new Dictionary <string, object>();
dict.Add("trip", trip_id);
dict.Add("order_id", row.Cells["colShipId"].Value.ToString());
dict.Add("client", row.Cells["colClient"].Value.ToString());
dict.Add("customer", row.Cells["colCustomer"].Value.ToString());
dict.Add("status", "FOR RECEIVING");
dict.Add("reference", row.Cells["colRefDoc"].Value.ToString());
dict.Add("reference_date", row.Cells["colRefDocDate"].Value.ToString());
dict.Add("oms", row.Cells["colClient"].Value.ToString());
dict.Add("doc_value", row.Cells["colDocValue"].Value.ToString());
dict.Add("drop_sequence", FAQ.GetSequence(trip_id));
String sql = DataSupport.GetInsert("TripOrders", dict);
string s = row.Cells["colRoute"].Value.ToString();
sql += " UPDATE Trips SET route = '" + row.Cells["colRoute"].Value.ToString() + "', last_updated_on ='" + DateTime.Now + "' WHERE trip_id = '" + trip_id + "' ";
DataSupport.RunNonQuery(sql, IsolationLevel.ReadCommitted);
}
// Update OMS
{
String sql = " UPDATE OutgoingShipmentRequests SET status ='FOR RELEASING' WHERE out_shipment_id = '" + row.Cells["colShipId"].Value.ToString() + "'; ";
Connection.GetOMSConnection.ExecuteNonQuery(sql, IsolationLevel.ReadCommitted);
}
// Update WMS
{
// Sync the trip
String sql = Utils.GetTripWMSSyncSQL(trip_id);
// Sync the orders of the trip
var tripDetailsDT = Utils.GetTripDetails(trip_id);
var tripHeaderRow = Utils.GetTripHeader(trip_id);
foreach (DataRow trip_row in tripDetailsDT.Rows)
{
sql += $"UPDATE ReleaseOrders SET status='FOR PICKING', scheduled_release_date = '{ tripHeaderRow["expected_start"] }' WHERE order_id ='{ trip_row["order_id"] }' AND status='FOR SCHEDULING';";
}
Connection.GetWMSConnection.ExecuteNonQuery(sql, IsolationLevel.ReadCommitted);
}
LoadTable();
}
private void btnStockCheck_Click(object sender, EventArgs e)
{
try
{
var row = headerGrid.SelectedRows[0];
StringBuilder sql = new StringBuilder();
Dictionary <String, Object> header = new Dictionary <string, object>();
DataTable dt = FAQ.GetOMSOutgoingDetails(row.Cells["out_shipment_id"].Value.ToString());
if (FAQ.IsAlreadyDownloaded(row.Cells["out_shipment_id"].Value.ToString()))
{
LedgerSupport.StockCheck();
return;
}
String order_id = row.Cells["out_shipment_id"].Value.ToString();
header.Add("order_id", order_id);
header.Add("client", row.Cells["client"].Value);
header.Add("reference", row.Cells["document_reference"].Value);
header.Add("reference_date", row.Cells["document_reference_date"].Value);
header.Add("order_date", row.Cells["datetime"].Value);
header.Add("recipient", row.Cells["authorized_tms"].Value);
header.Add("customer", row.Cells["customer_id"].Value);
header.Add("oms_shipment_id", row.Cells["out_shipment_id"].Value);
sql.Append(DataSupport.GetInsert("ReleaseOrders", header));
foreach (DataRow detail_row in dt.Rows)
{
Dictionary <String, Object> detail = new Dictionary <string, object>();
detail.Add("release_order", order_id);
detail.Add("product", detail_row["product"]);
detail.Add("uom", detail_row["uom"]);
detail.Add("qty", detail_row["expected_qty"]);
sql.Append(DataSupport.GetInsert("ReleaseOrderDetails", detail));
}
DataSupport.RunNonQuery(sql.ToString(), IsolationLevel.ReadCommitted);
LedgerSupport.StockCheck();
MessageBox.Show("Stock Check Complete");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void dataGridView1_CellValueChanged(object sender, DataGridViewCellEventArgs e)
{
try
{
String code = dataGridView1.Rows[e.RowIndex].Cells[colCode.Name].Value.ToString();
var dt = DataSupport.RunDataSet("select * from base_product where product_code = '" + code + "'").Tables[0];
foreach (DataRow row in dt.Rows)
{
dataGridView1.Rows[e.RowIndex].Cells[coldescription.Name].Value = row["description"].ToString();
}
}
catch
{ }
}
private void cbxSource_SelectedValueChanged(object sender, EventArgs e)
{
try
{
var dt = DataSupport.RunDataSet("Select warehouse_id,warehouseCode,address from Warehouses where description = '" + cbxSource.Text + "' ").Tables[0];
foreach (DataRow row in dt.Rows)
{
txtWhseS.Text = row["warehouseCode"].ToString();
txtAddressS.Text = row["address"].ToString();
WhsID = row["warehouse_id"].ToString();
}
}
catch
{ }
}
private void LoadData()
{
set = DataSupport.RunDataSet(String.Format(@"SELECT * FROM Releases WHERE release_id = '{0}';
SELECT (SELECT client FROM ReleaseOrders RO WHERE RO.order_id = RD.order_id)[Client]
, (SELECT reference FROM ReleaseOrders RO WHERE RO.order_id = RD.order_id)[Order]
, Product, Uom, lot_no[Lot No], Expiry, scanned_qty[Qty]
FROM ReleaseDetailItems RD WHERE release = '{0}';
SELECT * FROM ReleaseTrips WHERE trip_id =(SELECT trip FROM Releases WHERE release_id = '{0}');
", release_id));
foreach (DataRow row in set.Tables[1].Rows)
{
row["Expiry"] = DateTime.Parse(row["Expiry"].ToString()).ToShortDateString();
}
}
private void button2_Click(object sender, EventArgs e)
{
StringBuilder sql = new StringBuilder();
Dictionary <String, Object> header = new Dictionary <String, Object>();
header.Add("product_id", txtProductCode.Text);
header.Add("description", txtDescription.Text);
header.Add("description1", txtDescrip1.Text);
header.Add("description2", txtdescription2.Text);
header.Add("category", txtCat.Text);
header.Add("pcs_per_case", "0");
header.Add("default_owner", "COMARK");
sql.Append(DataSupport.GetInsert("products", header));
DataSupport.RunNonQuery(sql.ToString(), IsolationLevel.ReadCommitted);
}
public void LoadData()
{
DataTable dt = DataSupport.RunDataSet(@"SELECT order_id[Order ID]
,holding_transaction[Issue Source]
,holding_datetime[Issue Datetime]
,holding_reason[Reason]
,Customer
,Client
,total_invoice_amount[Invoice Value]
, status [Order Status]
FROM ReleaseOrders
WHERE holding_transaction IS NOT NULL").Tables[0];
header_grid.DataSource = dt;
}
private void txtCustomer_SelectedValueChanged(object sender, EventArgs e)
{
try
{
var dt = DataSupport.RunDataSet("SELECT customer,address,postalCode FROM TransportCustomers where custCode = '" + txtCustCode.Text + "' ").Tables[0];
foreach (DataRow row in dt.Rows)
{
lblCustname.Text = row["customer"].ToString();
lblAddress.Text = row["address"].ToString();
lblArea.Text = row["postalCode"].ToString();
}
}
catch
{ }
}
public static String ToInsert(this DataTable dt, String table)
{
StringBuilder result = new StringBuilder();
foreach (DataRow row in dt.Rows)
{
Dictionary <String, Object> insert_list = new Dictionary <string, object>();
foreach (DataColumn col in dt.Columns)
{
insert_list.Add(col.ColumnName, row[col]);
}
result.Append(DataSupport.GetInsert(table, insert_list));
}
return(result.ToString());
}
