private void updated()
{
StringBuilder sql = new StringBuilder();
var primary = new List <string>();
Dictionary <String, Object> header = new Dictionary <string, object>();
header.Add("transport", txtTransport.Text);
header.Add("customer", txtcustname.Text);
header.Add("custCode", txtCode.Text);
header.Add("address", txtAddress.Text);
header.Add("contactNo", txtcontact.Text);
header.Add("postalCode", txtpostCode.Text);
header.Add("tinNo", txtTin.Text);
header.Add("terms", txtTerms.Text);
header.Add("discount1", txtDis1.Text);
header.Add("discount2", txtDis2.Text);
header.Add("discount3", txtDis3.Text);
header.Add("discount4", txtDis4.Text);
header.Add("discount5", txtDis5.Text);
header.Add("dateStamp", DateTime.Now);
primary.Add("custCode");
sql.Append(DataSupport.GetUpdate("TransportCustomers", header, primary));
DataSupport.RunNonQuery(sql.ToString(), IsolationLevel.ReadCommitted);
MessageBox.Show("Success");
DialogResult = DialogResult.OK;
}
public static void UpdateOrderStatus(String release_id)
{
var dt = DataSupport.RunDataSet("SELECT ORDER_ID[Order], drop_sequence[Drop], Status FROM ReleaseDetails WHERE release = @release_id ORDER BY drop_sequence", "release_id", release_id).Tables[0];
foreach (DataRow row in dt.Rows)
{
String order_id = row["Order"].ToString();
var index = dt.Rows.IndexOf(row);
if (row["Status"].ToString() == "RELEASING")
{
var remaining = int.Parse(DataSupport.RunDataSet("SELECT SUM(order_qty-scanned_qty)[remaining] FROM ReleaseDetailItems WHERE release = @release AND order_id = @order_id", "release", release_id, "order_id", order_id).Tables[0].Rows[0][0].ToString());
if (remaining <= 0)
{
DataSupport.RunNonQuery("UPDATE ReleaseDetails SET Status='RELEASED' WHERE release = @release_id AND order_id = @order_id", "release_id", release_id, "order_id", order_id);
if (index == dt.Rows.Count - 1)
{
DataSupport.RunNonQuery("UPDATE Releases SET Status='RELEASED' WHERE release_id = @release_id", "release_id", release_id);
}
else
{
var next_row = dt.Rows[index + 1];
DataSupport.RunNonQuery("UPDATE ReleaseDetails SET Status='RELEASING' WHERE release = @release_id AND order_id = @order_id", "release_id", release_id, "order_id", next_row["Order"].ToString());
}
}
return;
}
}
}
private void btnLoad_Click(object sender, EventArgs e)
{
var header = JSONSupport.DeserializeObject(textBox1.Text);
var details = JSONSupport.DeserializeToDataTable(header["details"].ToString());
details.Columns.Add("release_order");
header.Remove("details");
String order_id = DataSupport.GetNextMenuCodeInt("ORD");
header.Add("order_id", order_id);
foreach (DataRow row in details.Rows)
{
row["release_order"] = order_id;
}
String sql = DataSupport.GetInsert("ReleaseOrders", header);
foreach (DataRow row in details.Rows)
{
var detail = row.AsDictionary <String, Object>();
sql += DataSupport.GetInsert("ReleaseOrderDetails", detail);
}
DataSupport.RunNonQuery(sql, IsolationLevel.ReadCommitted);
MessageBox.Show("Success");
}
private void btnAdd_Click(object sender, EventArgs e)
{
string message = "";
if (FAQ.IsDatesBlocked(txtVehicle.Text, DateTime.Parse(txtStart.Value.ToShortDateString()), DateTime.Parse(txtEnd.Value.ToShortDateString()), ref message))
{
MessageBox.Show(message);
return;
}
if (txtStart.Value > txtEnd.Value)
{
MessageBox.Show("End Date can't be lower than Start Date");
return;
}
String trip_id = DataSupport.GetNextMenuCodeInt("TR");
Dictionary <String, Object> dict = new Dictionary <string, object>();
dict.Add("trip_id", trip_id);
dict.Add("vehicle", txtVehicle.Text);
dict.Add("in_charge", txtInCharge.Text);
dict.Add("expected_start", txtStart.Value.ToShortDateString());
dict.Add("expected_end", txtEnd.Value.ToShortDateString());
String sql = DataSupport.GetInsert("Trips", dict);
DataSupport.RunNonQuery(sql, IsolationLevel.ReadCommitted);
MessageBox.Show("Success");
DialogResult = DialogResult.OK;
}
private void btnDelete_Click(object sender, EventArgs e)
{
var row = header_grid.SelectedRows[0];
String order_id = row.Cells["order_id"].Value.ToString();
String trip_id = txtTrip.Text;
var tripDetailsDT = Utils.GetTripDetails(trip_id);
// Update itself
{
String sql = "DELETE FROM TripOrders WHERE order_id='" + order_id + "' AND trip='" + txtTrip.Text + "'; ";
DataSupport.RunNonQuery(sql, IsolationLevel.ReadCommitted);
}
// Update OMS
{
String sql = " UPDATE OutgoingShipmentRequests SET status ='FOR SCHEDULING' WHERE out_shipment_id = '" + order_id + "'; ";
Connection.GetOMSConnection.ExecuteNonQuery(sql, IsolationLevel.ReadCommitted);
}
//Update WMS
{
// Sync the trip
string sql = Utils.GetTripWMSSyncSQL(trip_id);
// Sync the orders of the trip
foreach (DataRow trip_row in tripDetailsDT.Rows)
{
sql += $"UPDATE ReleaseOrders SET status = 'FOR SCHEDULING' WHERE order_id = '{ trip_row["order_id"] }';";
}
Connection.GetWMSConnection.ExecuteNonQuery(sql, IsolationLevel.ReadCommitted);
}
LoadTable();
}
private void btnDeclare_Click(object sender, EventArgs e)
{
StringBuilder sql = new StringBuilder();
foreach (DataGridViewRow row in dataGridView1.Rows)
{
if (dataGridView1.Rows.IndexOf(row) == dataGridView1.Rows.Count - 1)
{
break;
}
var primary = new List <string>();
Dictionary <String, Object> header = new Dictionary <String, Object>();
header.Add("productID", txtCode.Text);
header.Add("uom", row.Cells[colUom.Name].Value.ToString());
header.Add("priceTypeID", row.Cells[colPriceType.Name].Value.ToString());
header.Add("unitPrice", row.Cells[colUnitPrice.Name].Value.ToString());
header.Add("dateStamp", DateTime.Now);
header.Add("qty", row.Cells[colQty.Name].Value.ToString());
primary.Add("productID"); primary.Add("uom"); primary.Add("priceTypeID");
if (FAQ.productPriceExist(Convert.ToInt32(txtCode.Text), row.Cells[colUom.Name].Value.ToString(), row.Cells[colPriceType.Name].Value.ToString()))
{
sql.Append(DataSupport.GetUpdate("itemPrice", header, primary));
}
else
{
sql.Append(DataSupport.GetInsert("itemPrice", header));
}
}
DataSupport.RunNonQuery(sql.ToString(), IsolationLevel.ReadCommitted);
MessageBox.Show("Success");
DialogResult = DialogResult.OK;
}
private void SaveData()
{
ResolutionsWindow grandparent = parent.parent;
String trans_source = grandparent.product_row.Cells["Trans"].Value.ToString();
String trans_id = grandparent.product_row.Cells["Id"].Value.ToString();
String line = grandparent.product_row.Cells["Line"].Value.ToString();
int explanation_no = int.Parse(DataSupport.RunDataSet("SELECT COUNT(*)[count] FROM Resolutions WHERE trans_source = '" + trans_source + "' AND trans_id = '" + trans_id + "' AND line='" + line + "'").Tables[0].Rows[0][0].ToString());
// Save Transaction
String sql = DataSupport.GetInsert("Resolutions", Utils.ToDict(
"trans_source", trans_source
, "trans_id", trans_id
, "line", line
, "explanation_no", explanation_no
, "explanation", parent.txtExplanation.Text
, "charge_to", parent.txtChargeTo.Text
, "qty_resolved", parent.txtQtyToResolve.Text
, "resolved_by", parent.txtResolvedBy.Text
, "resolved_on", DateTime.Now
));
DataSupport.RunNonQuery(sql, IsolationLevel.ReadCommitted);
MessageBox.Show("Success");
webBrowser1.DocumentText = webBrowser1.DocumentText.Replace("(issued on save)", trans_id + "-" + line + "-RS-" + explanation_no);
btnPrintPreview.Text = "Print";
btnCancel.Visible = false;
}
private void updated()
{
StringBuilder sql = new StringBuilder();
var primary = new List <String>();
Dictionary <String, Object> header = new Dictionary <string, object>();
header.Add("custCode", txtcustCode.Text);
header.Add("customer", txtcustName.Text);
header.Add("address", txtaddress.Text);
header.Add("businessStyle", txtbusiness.Text);
header.Add("contactNo", txtcontact.Text);
header.Add("postalCode", txtpostCode.Text);
header.Add("tinNo", txttin.Text);
header.Add("terms", txtterms.Text);
header.Add("discount1", txtdisc1.Text);
header.Add("discount2", txtdisc2.Text);
header.Add("discount3", txtdisc3.Text);
header.Add("discount4", txtdisc4.Text);
header.Add("discount5", txtdisc5.Text);
header.Add("zone", txtzone.Text);
header.Add("status", 1);
primary.Add("custCode");
sql.Append(DataSupport.GetUpdate("base_customer", header, primary));
DataSupport.RunNonQuery(sql.ToString(), IsolationLevel.ReadCommitted);
MessageBox.Show("updated");
DialogResult = DialogResult.OK;
}
private void btnDeclareComplete_Click(object sender, EventArgs e)
{
String sql = "";
String putaway_id = txtPutawayID.Text;
DateTime now = DateTime.Now;
// Update Putaway
sql += "UPDATE PutawayDetails SET actual_qty = expected_qty WHERE putaway= '" + putaway_id + "'; ";
// Update Transactions Ledger
DataTable detailsDT = FAQ.GetPutawayDetails(putaway_id);
// Update Transaction Ledger
{
// Out with the container
DataTable outsDT = LedgerSupport.GetLocationLedgerDT();
outsDT.Rows.Add(txtContainer.Text, now, "OUT", "PUTAWAY_DECLARE_COMPLETE", putaway_id);
sql += LedgerSupport.UpdateLocationLedger(outsDT);
// In with the location
DataTable insDT = LedgerSupport.GetLocationLedgerDT();
foreach (DataRow row in detailsDT.Rows)
{
insDT.Rows.Add(row["location"], now, "IN", "PUTAWAY_DECLARE_COMPLETE", putaway_id);
}
sql += LedgerSupport.UpdateLocationLedger(insDT);
}
// Update Location Products Ledger
{
// Out with the container
DataTable outsDT = LedgerSupport.GetLocationProductsLedgerDT();
foreach (DataRow row in detailsDT.Rows)
{
outsDT.Rows.Add(txtContainer.Text, row["product"].ToString(), int.Parse(row["expected_qty"].ToString()) * -1, row["uom"].ToString(), row["lot_no"].ToString(), row["expiry"].ToString());
}
sql += LedgerSupport.UpdateLocationProductsLedger(outsDT);
// In with the location
DataTable insDT = LedgerSupport.GetLocationProductsLedgerDT();
foreach (DataRow row in detailsDT.Rows)
{
insDT.Rows.Add(row["location"].ToString(), row["product"].ToString(), row["expected_qty"].ToString(), row["uom"].ToString(), row["lot_no"].ToString(), row["expiry"].ToString());
}
sql += LedgerSupport.UpdateLocationProductsLedger(insDT);
}
DataSupport.RunNonQuery(sql, IsolationLevel.ReadCommitted);
MessageBox.Show("Success");
this.Close();
}
private void btnReleaseAnyway_Click(object sender, EventArgs e)
{
var row = header_grid.SelectedRows[0];
String order_id = row.Cells["Order ID"].Value.ToString();
DataSupport.RunNonQuery("UPDATE ReleaseOrders SET status='FOR RELEASING' WHERE order_id= @id; ", "id", order_id);
LoadData();
}
private void save()
{
try
{
String DRID = DataSupport.GetNextMenuCodeInt("DR");
StringBuilder sql = new StringBuilder();
Dictionary <String, Object> header = new Dictionary <string, object>();
header.Add("dr_Id", DRID);
header.Add("drNo", txtDRNo.Text);
header.Add("drDate", txtDRDate.Text);
header.Add("poNo", txtREf.Text);
header.Add("terms", txtTerms.Text);
header.Add("typeOfStock", txtTypeOfStocks.Text);
header.Add("custCode", txtCustCode.Text);
header.Add("custName", txtCustName.Text);
header.Add("address", txtaddress.Text);
sql.Append(DataSupport.GetInsert("Global_drTrans", header));
foreach (DataGridViewRow row in dataGridView1.Rows)
{
if (dataGridView1.Rows.IndexOf(row) == dataGridView1.Rows.Count - 1)
{
break;
}
Dictionary <String, Object> detail = new Dictionary <String, Object>();
detail.Add("dr_Id", DRID);
detail.Add("product_code", row.Cells[colCode.Name].Value.ToString());
detail.Add("description", row.Cells[coldescription.Name].Value.ToString());
if (string.IsNullOrEmpty(row.Cells[colbatch.Name].Value as string))
{
detail.Add("batchno", "");
}
else
{
detail.Add("batchno", row.Cells[colbatch.Name].Value.ToString());
}
if (row.Cells[colExdate.Name].Value == null)
{
detail.Add("expiryDate", DBNull.Value);
}
else
{
detail.Add("expiryDate", row.Cells[colExdate.Name].Value.ToString());
}
detail.Add("packSize", row.Cells[colPack.Name].Value.ToString());
detail.Add("pcs", row.Cells[colpcs.Name].Value.ToString());
detail.Add("cases", row.Cells[colcases.Name].Value.ToString());
sql.Append(DataSupport.GetInsert("Global_drTransDetails", detail));
}
DataSupport.RunNonQuery(sql.ToString(), IsolationLevel.ReadCommitted);
MessageBox.Show("Saved");
DialogResult = DialogResult.OK;
}
catch
{
}
}
private void SaveData()
{
String id = DataSupport.GetNextMenuCodeInt("PL");
DateTime now = DateTime.Now;
// Save Transaction
String sql = "";
foreach (DataGridViewRow row in parent.header_grid.SelectedRows)
{
sql += DataSupport.GetInsert("ForDisposals", Utils.ToDict(
"trans_id", id
, "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()
, "reason", row.Cells["Days To Expiry"].Value.ToString()
));
// Update Transaction Ledger
{
DataTable insDT = LedgerSupport.GetLocationLedgerDT();
insDT.Rows.Add("STAGING-OUT", now, "IN", "CANCEL_ORDER", id);
sql += LedgerSupport.UpdateLocationLedger(insDT);
DataTable outsDT = LedgerSupport.GetLocationLedgerDT();
outsDT.Rows.Add(row.Cells["Location"].Value.ToString(), now, "OUT", "CANCEL_ORDER", id);
sql += LedgerSupport.UpdateLocationLedger(outsDT);
}
// Update Location Products Ledger
{
DataTable insDT = LedgerSupport.GetLocationProductsLedgerDT();
insDT.Rows.Add("STAGING-OUT", row.Cells["Product"].Value, row.Cells["Qty"].Value, row.Cells["Uom"].Value, row.Cells["Lot No"].Value, row.Cells["Expiry"].Value);
sql += LedgerSupport.UpdateLocationProductsLedger(insDT);
DataTable outsDT = LedgerSupport.GetLocationProductsLedgerDT();
outsDT.Rows.Add(row.Cells["Location"].Value.ToString(), row.Cells["Product"].Value, int.Parse(row.Cells["Qty"].Value.ToString()) * -1, row.Cells["Uom"].Value, row.Cells["Lot No"].Value, row.Cells["Expiry"].Value, int.Parse(row.Cells["qty"].Value.ToString()) * -1, int.Parse(row.Cells["Qty"].Value.ToString()) * -1);
sql += LedgerSupport.UpdateLocationProductsLedger(outsDT);
}
}
DataSupport.RunNonQuery(sql, IsolationLevel.ReadCommitted);
MessageBox.Show("Success");
webBrowser1.DocumentText = webBrowser1.DocumentText.Replace("(issued on save)", id);
btnPrintPreview.Text = "Print";
btnCancel.Visible = false;
}
private void saved()
{
StringBuilder sql = new StringBuilder();
Dictionary <String, Object> header = new Dictionary <String, Object>();
header.Add("product_code", txtproductCode.Text);
header.Add("customer_code", txtCustCode.Text);
header.Add("description", txtDescription.Text);
sql.Append(DataSupport.GetInsert("base_product", header));
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("product_code", txtproductCode.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("quantity", "");
}
else
{
details.Add("quantity", row.Cells[colQty.Name].Value.ToString());
}
if (string.IsNullOrEmpty(row.Cells[colPriceType.Name].Value as string))
{
details.Add("priceType", "");
}
else
{
details.Add("priceType", row.Cells[colPriceType.Name].Value.ToString());
}
if (string.IsNullOrEmpty(row.Cells[colPriceType.Name].Value as string))
{
details.Add("price", "");
}
else
{
details.Add("price", row.Cells[colPrice.Name].Value.ToString());
}
sql.Append(DataSupport.GetInsert("base_price", details));
}
DataSupport.RunNonQuery(sql.ToString(), IsolationLevel.ReadCommitted);
MessageBox.Show("Success");
DialogResult = DialogResult.OK;
}
public static void StockCheck()
{
DataSupport oms_dh = new DataSupport(String.Format(@"Initial Catalog={0};Data Source= {1};User Id = {2}; Password = {3}", Utils.DBConnection["OMS"]["DBNAME"], Utils.DBConnection["OMS"]["SERVER"], Utils.DBConnection["OMS"]["USERNAME"], Utils.DBConnection["OMS"]["PASSWORD"]));
DataTable ordersDT = DataSupport.RunDataSet(String.Format("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)
{
oms_dh.ExecuteNonQuery("UPDATE OutgoingShipmentRequests SET status = 'INSUFFICIENT STOCKS' WHERE out_shipment_id = '" + row["oms_shipment_id"].ToString() + "';", IsolationLevel.ReadCommitted);
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");
oms_dh.ExecuteNonQuery("UPDATE OutgoingShipmentRequests SET status = 'FOR SCHEDULING' WHERE out_shipment_id = '" + row["oms_shipment_id"].ToString() + "';", IsolationLevel.ReadCommitted);
}
}
private void btnDeclare_Click(object sender, EventArgs e)
{
String outgoing_id = DataSupport.GetNextMenuCodeInt("DR");
StringBuilder sql = new StringBuilder();
Dictionary <String, Object> header = new Dictionary <string, object>();
header.Add("out_shipment_id", outgoing_id);
header.Add("warehouse", txtWarehouse.Text);
header.Add("datetime", DateTime.Now);
header.Add("document_reference", txtReference.Text);
header.Add("client", txtClient.Text);
header.Add("authorized_tms", txtTransportProvider.Text);
header.Add("customer_id", txtCustomer.Text);
header.Add("remarks", txtRemarks.Text);
header.Add("outgoing_type", txOutgoingType.Text);
header.Add("document_reference_date", txtDocDate.Text);
header.Add("status", "FOR STOCK CHECKING");
sql.Append(DataSupport.GetInsert("OutgoingShipmentRequests", header));
foreach (DataGridViewRow row in headerGrid.Rows)
{
if (headerGrid.Rows.IndexOf(row) == headerGrid.Rows.Count - 1)
{
break;
}
Dictionary <String, Object> detail = new Dictionary <string, object>();
detail.Add("out_shipment", outgoing_id);
detail.Add("product", row.Cells[product.Name].Value.ToString());
detail.Add("uom", row.Cells[uom.Name].Value.ToString());
detail.Add("expected_qty", row.Cells[qty.Name].Value.ToString());
sql.Append(DataSupport.GetInsert("OutgoingShipmentRequestDetails", detail));
}
if (string.IsNullOrEmpty(txtWarehouse.Text) || string.IsNullOrEmpty(txtReference.Text))
{
MessageBox.Show("Please input all fields");
}
else
{
if (FAQ.OutDocumentReferenceExist(txtReference.Text))
{
MessageBox.Show("Exist Document Reference");
}
else
{
DataSupport.RunNonQuery(sql.ToString(), IsolationLevel.ReadCommitted);
MessageBox.Show("Success");
this.Close();
}
}
}
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 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 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 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 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 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);
}
private void saved()
{
StringBuilder sql = new StringBuilder();
Dictionary <String, Object> header = new Dictionary <String, Object>();
header.Add("product_id", txtCode.Text);
header.Add("description", txtdesc1.Text);
header.Add("description1", txtdesc2.Text);
header.Add("description2", txtdesc3.Text);
header.Add("category", txtCategory.Text);
header.Add("pcs_per_case", "0");
header.Add("default_owner", "COMARK");
sql.Append(DataSupport.GetInsert("products", header));
DataSupport.RunNonQuery(sql.ToString(), IsolationLevel.ReadCommitted);
saveUOM();
}
private void saved()
{
try
{
String siId = DataSupport.GetNextMenuCodeInt("INVOICE");
StringBuilder sql = new StringBuilder();
Dictionary <String, Object> header = new Dictionary <string, object>();
header.Add("outgoing_Id", siId);
header.Add("siNo", txtSI.Text);
header.Add("sidate", txtSiDate.Text);
header.Add("poNo", txtPO.Text);
header.Add("typeOfStock", cbxTypeOfSTocks.Text);
header.Add("custCode", cbxCustCode.Text);
header.Add("custName", txtCustName.Text);
header.Add("terms", txtTerms.Text);
header.Add("priceType", cbxPriceType.Text);
header.Add("totalAmount", Details[2]);
header.Add("discount", Details[3]);
header.Add("vat", Details[1]);
header.Add("AmountDue", Details[0]);
sql.Append(DataSupport.GetInsert("Global_ProductTrans", header));
foreach (DataGridViewRow row in dataGridView1.Rows)
{
if (dataGridView1.Rows.IndexOf(row) == dataGridView1.Rows.Count - 1)
{
break;
}
Dictionary <String, Object> detail = new Dictionary <String, Object>();
detail.Add("outgoing_Id", siId);
detail.Add("product_code", row.Cells[colCode.Name].Value.ToString());
detail.Add("description", row.Cells[colDescription.Name].Value.ToString());
detail.Add("qty", row.Cells[colQty.Name].Value.ToString());
detail.Add("uom", row.Cells[colUnit.Name].Value.ToString());
detail.Add("price", row.Cells[colPrice.Name].Value.ToString());
detail.Add("dics", row.Cells[colDisc.Name].Value.ToString());
detail.Add("amount", row.Cells[colAmount.Name].Value.ToString());
sql.Append(DataSupport.GetInsert("Global_ProductTransDetails", detail));
}
DataSupport.RunNonQuery(sql.ToString(), IsolationLevel.ReadCommitted);
MessageBox.Show("Saved");
DialogResult = DialogResult.OK;
}
catch
{
}
}
private void saved()
{
MessageBox.Show(WhsID.ToString());
String transID = DataSupport.GetNextMenuCodeInt("STR-OUT");
StringBuilder sql = new StringBuilder();
Dictionary <String, Object> header = new Dictionary <string, object>();
header.Add("shipment_id", transID);
header.Add("warehouse", WhsID);
header.Add("document_reference", txtRefNR.Text);
header.Add("document_reference_date", txtDateRef.Text);
//header.Add("client", "Null");
//header.Add("authorized_shipper", "Null");
header.Add("remarks", txtReason.Text);
header.Add("incoming_type", "STOCK TRANSFER");
header.Add("typeStocks", txtTypeStocks.Text);
header.Add("received", txtReceived.Text);
header.Add("received_on", txtDateRe.Text);
header.Add("status", "FOR RECEIVING");
header.Add("custName", cbxReceived.Text);
header.Add("issued", txtIssued.Text);
header.Add("dateIssued", txtDateI.Text);
header.Add("pickUp", txtPickUp.Text);
header.Add("datePick", txtDateP.Text);
sql.Append(DataSupport.GetInsert("IncomingShipmentRequests", header));
foreach (DataGridViewRow row in dataGridView1.Rows)
{
if (dataGridView1.Rows.IndexOf(row) == dataGridView1.Rows.Count - 1)
{
break;
}
Dictionary <String, Object> detail = new Dictionary <string, object>();
detail.Add("shipment", transID);
detail.Add("product", row.Cells[colCode.Name].Value.ToString());
detail.Add("uom", row.Cells[colUnit.Name].Value.ToString());
detail.Add("expected_qty", row.Cells[colQuantity.Name].Value.ToString());
detail.Add("lot_no", "Null");
detail.Add("expiry", "12-12-2000");
sql.Append(DataSupport.GetInsert("IncomingShipmentRequestDetails", detail));
}
DataSupport.RunNonQuery(sql.ToString(), IsolationLevel.ReadCommitted);
MessageBox.Show("Success");
DialogResult = DialogResult.OK;
}
private void SaveData()
{
String id = DataSupport.GetNextMenuCodeInt("PL");
// Save Transaction
String sql = DataSupport.GetInsert("Picklists", Utils.ToDict(
"picklist_id", id
, "status", "TO BE PICKED"
));
foreach (DataGridViewRow row in parent.picklist_grid.Rows)
{
sql += DataSupport.GetInsert("PicklistDetails", Utils.ToDict(
"picklist", id
, "line", parent.picklist_grid.Rows.IndexOf(row) + 1
, "order_id", row.Cells["order_id"].Value.ToString()
, "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()
, "location", row.Cells["location"].Value.ToString()
));
}
foreach (DataGridViewRow row in parent.picklist_grid.Rows)
{
sql += " UPDATE LocationProductsLedger SET to_be_picked_qty = to_be_picked_qty + " + row.Cells["qty"].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() + "'; ";
}
foreach (DataGridViewRow row in parent.picklist_grid.Rows)
{
sql += " UPDATE ReleaseOrders SET status='FOR PICKING' WHERE order_id='" + row.Cells["order_id"].Value.ToString() + "'; ";
}
DataSupport.RunNonQuery(sql, IsolationLevel.ReadCommitted);
MessageBox.Show("Success");
webBrowser1.DocumentText = webBrowser1.DocumentText.Replace("(issued on save)", id);
btnPrintPreview.Text = "Print";
btnCancel.Visible = false;
}
private void SaveData()
{
String id = DataSupport.GetNextMenuCodeInt("PC");
// Save Transaction
String sql = DataSupport.GetInsert("PhysicalCounts", Utils.ToDict(
"phcount_id", id
, "created_on", DateTime.Now
, "cycle", parent.txtCycle.Value.ToString()
, "cycle_year", parent.txtYear.Text
, "counted_by", parent.txtCountedBy.Text
));
foreach (String location in locations)
{
sql += DataSupport.GetInsert("PhysicalCountDetails", Utils.ToDict(
"phcount", id
, "location", location.Replace("'", "")
));
}
foreach (DataRow row in dt.Rows)
{
sql += DataSupport.GetInsert("PhysicalCountDetailItems", Utils.ToDict(
"phcount", id
, "location", row["Location"]
, "product", row["Product"]
, "uom", row["Uom"]
, "lot_no", row["Lot No"]
, "expiry", row["Expiry"]
, "expected_qty", row["qty"]
, "line", dt.Rows.IndexOf(row) + 1
));
}
DataSupport.RunNonQuery(sql, IsolationLevel.ReadCommitted);
MessageBox.Show("Success");
webBrowser1.DocumentText = webBrowser1.DocumentText.Replace("(issued on save)", id);
btnPrintPreview.Text = "Print";
btnCancel.Visible = false;
}
private void btnSchedule_Click(object sender, EventArgs e)
{
DataTable ordersDT = DataSupport.RunDataSet("SELECT * FROM ReleaseOrders WHERE status = 'FOR SCHEDULING'").Tables[0];
String trip_id = new Random().Next(1, 2000000).ToString();
// Eddie
String sql = " INSERT INTO ReleaseTrips VALUES ('" + trip_id + "', 'FOR RELEASING', 'RANDOM GUY', '" + DateTime.Now.AddDays(5).ToShortDateString() + "' )";
foreach (DataRow row in ordersDT.Rows)
{
// Eddie
sql += " INSERT INTO ReleaseTripDetails VALUES('" + trip_id + "', '" + row["order_id"] + "', '" + ordersDT.Rows.IndexOf(row) + "'); ";
// Ours
sql += " UPDATE ReleaseOrders SET status='FOR PICKING', scheduled_release_date ='" + DateTime.Now.AddDays(5).ToShortDateString() + "' WHERE order_id ='" + row["order_id"] + "' ";
}
DataSupport.RunNonQuery(sql, IsolationLevel.ReadCommitted);
MessageBox.Show("TMS Schedule Received!");
textBox2.Text = trip_id;
}
private void SaveData()
{
String id = DataSupport.GetNextMenuCodeInt("CBPL");
// Save Transaction
String sql = DataSupport.GetInsert("CaseBreak", Utils.ToDict(
"casebreak_id", id
, "approved_on", parent.txtReceivedOn.Value.ToShortDateString()
, "encoded_on", DateTime.Now
, "approved_by", parent.cboReceivedBy.SelectedValue
, "status", "TO BE PICKED"
));
foreach (DataGridViewRow row in parent.picklist_grid.Rows)
{
sql += DataSupport.GetInsert("CaseBreakDetails", Utils.ToDict(
"casebreak", id
, "line", parent.picklist_grid.Rows.IndexOf(row) + 1
, "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()
, "location", row.Cells["location"].Value.ToString()
, "breakto_uom", row.Cells["breakto_uom"].Value.ToString()
));
}
foreach (DataGridViewRow row in parent.picklist_grid.Rows)
{
sql += " UPDATE LocationProductsLedger SET [reserved_qty] = [reserved_qty] + " + row.Cells["qty"].Value.ToString() + ", to_be_picked_qty = to_be_picked_qty + " + row.Cells["qty"].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() + "'; ";
}
DataSupport.RunNonQuery(sql, IsolationLevel.ReadCommitted);
MessageBox.Show("Success");
webBrowser1.DocumentText = webBrowser1.DocumentText.Replace("(issued on save)", id);
btnPrintPreview.Text = "Print";
btnCancel.Visible = false;
}
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();
}