private void dataGridView1_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
{
var dialog = new MasterFile.Products();
string code = dataGridView1.Rows[e.RowIndex].Cells[colCode.Name].Value.ToString();
var dRow = DataSupport.RunDataSet("Select * from base_product join base_price on base_price.product_code = base_product.product_code where base_price.product_code ='" + code + "'").Tables[0];
if (dRow.Rows.Count > 0)
{
foreach (DataRow row in dRow.Rows)
{
dialog.txtproductCode.Text = row["product_code"].ToString();
dialog.txtCustCode.Text = row["customer_code"].ToString();
dialog.txtDescription.Text = row["description"].ToString();
dialog.dataGridView1.DataSource = DataSupport.RunDataSet("Select [uom],quantity,priceType,price from base_price where product_code ='" + code + "'").Tables[0];
}
}
else
{
var dt = DataSupport.RunDataSet("Select * from base_product where product_code ='" + code + "'").Tables[0];
foreach (DataRow row in dt.Rows)
{
dialog.txtproductCode.Text = row["product_code"].ToString();
dialog.txtCustCode.Text = row["customer_code"].ToString();
dialog.txtDescription.Text = row["description"].ToString();
}
}
Products.mode = "update";
dialog.txtproductCode.ReadOnly = true;
dialog.txtCustCode.ReadOnly = true;
dialog.ShowDialog();
display();
}
private void dataGridView1_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
{
var dialog = new MAster.Customer();
dialog.Mode = "updated";
String code = dataGridView1.Rows[e.RowIndex].Cells[custCode.Name].Value.ToString();
var drow = DataSupport.RunDataSet("Select * from TransportCustomers where custCode = '" + code + "'").Tables[0];
foreach (DataRow row in drow.Rows)
{
dialog.txtTransport.Text = row["transport"].ToString();
dialog.txtcustname.Text = row["customer"].ToString();
dialog.txtCode.Text = row["custCode"].ToString();
dialog.txtAddress.Text = row["address"].ToString();
dialog.txtcontact.Text = row["contactNo"].ToString();
dialog.txtpostCode.Text = row["postalCode"].ToString();
dialog.txtTin.Text = row["tinNo"].ToString();
dialog.txtDis1.Text = row["discount1"].ToString();
dialog.txtDis2.Text = row["discount2"].ToString();
dialog.txtDis3.Text = row["discount3"].ToString();
dialog.txtDis4.Text = row["discount4"].ToString();
dialog.txtDis5.Text = row["discount5"].ToString();
dialog.txtZone.Text = row["zone"].ToString();
}
dialog.txtAddress.ReadOnly = true;
dialog.txtcustname.ReadOnly = true;
dialog.txtCode.ReadOnly = true;
dialog.ShowDialog();
display();
}
private void LoadData()
{
DataTable dt = DataSupport.RunDataSet(@"SELECT Location, Product, Uom, lot_no[Lot No],Qty, Expiry, ''[Days To Expiry],expiry_status
FROM LocationProductsLedger
WHERE qty >0 AND location !='RELEASED'
ORDER BY expiry ").Tables[0];
foreach (DataRow row in dt.Rows)
{
DateTime expiry = DateTime.Parse(row["Expiry"].ToString());
DateTime today = DateTime.Parse(DateTime.Now.ToShortDateString());
var total_days = ((int)expiry.Subtract(today).TotalDays).ToString();
if (double.Parse(total_days) <= 0)
{
total_days = "EXPIRED";
}
if (row["expiry_status"].ToString() != "")
{
total_days = row["expiry_status"].ToString();
}
row["Expiry"] = DateTime.Parse(row["Expiry"].ToString()).ToShortDateString();
row["Days To Expiry"] = total_days;
}
dt.Columns.Remove("expiry_status");
header_grid.DataSource = dt;
}
private void SalerProcessing_Load(object sender, EventArgs e)
{
{
var dt = DataSupport.RunDataSet("SELECT customer FROM TransportCustomers ").Tables[0];
txtSoldTo.DataSource = dt;
txtSoldTo.DisplayMember = "customer";
txtSoldTo.ValueMember = "customer";
}
{
var dt = DataSupport.RunDataSet("SELECT product_id FROM Products").Tables[0];
colCode.DataSource = dt;
colCode.DisplayMember = "product_id";
colCode.ValueMember = "product_id";
}
{
var dt = DataSupport.RunDataSet("SELECT transport_id FROM TransportProviders").Tables[0];
cbxWarehouse.DataSource = dt;
cbxWarehouse.DisplayMember = "transport_id";
cbxWarehouse.ValueMember = "transport_id";
}
{
var dt = DataSupport.RunDataSet("SELECT warehouse_id FROM Warehouses").Tables[0];
txtWarehouse.DataSource = dt;
txtWarehouse.DisplayMember = "warehouse_id";
txtWarehouse.ValueMember = "warehouse_id";
}
txtSalesInvoice.KeyPress += new KeyPressEventHandler(KeyBoardSupport.ForNumericOnly_KeyPress);
cbxpriceType.SelectedIndex = 0;
txtTypeStock.SelectedIndex = 0;
}
private void DeliveryProcessing_Load(object sender, EventArgs e)
{
{
var dt = DataSupport.RunDataSet("SELECT product_id FROM Products").Tables[0];
foreach (DataRow row in dt.Rows)
{
colCode.Items.Add(row[0].ToString());
}
}
{
var dt = DataSupport.RunDataSet("SELECT warehouse_id FROM Warehouses").Tables[0];
foreach (DataRow row in dt.Rows)
{
cbxWarehouse.Items.Add(row[0].ToString());
}
}
{
var dt = DataSupport.RunDataSet("SELECT customer FROM TransportCustomers ").Tables[0];
foreach (DataRow row in dt.Rows)
{
cbxCustName.Items.Add(row[0].ToString());
}
}
{
var dt = DataSupport.RunDataSet("SELECT transport_id FROM TransportProviders").Tables[0];
foreach (DataRow row in dt.Rows)
{
txtTransportProvider.Items.Add(row[0].ToString());
}
}
txtDrno.KeyPress += new KeyPressEventHandler(KeyBoardSupport.ForNumericOnly_KeyPress);
}
public static Dictionary <String, DataRow> BuildIndex(String SQLCommand, List <String> ColumnName, String seperator)
{
DataTable table = DataSupport.RunDataSet(SQLCommand).Tables[0];
Dictionary <String, DataRow> index = new Dictionary <String, DataRow>(table.Rows.Count);
foreach (DataRow Row in table.Rows)
{
String key = "";
int cnt = 0;
foreach (String col in ColumnName)
{
if (cnt == ColumnName.Count - 1)
{
key += Row[col].ToString().Trim();
}
else
{
key += Row[col].ToString().Trim() + seperator;
}
cnt++;
}
index[key] = Row;
}
return(index);
}
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 LoadTrips()
{
DateTime start = DateTime.Parse(txtStart.Value.ToShortDateString());
DateTime end = DateTime.Parse(txtEnd.Value.ToShortDateString());
FillCommonData();
var trip_dt = DataSupport.RunDataSet("SELECT * FROM Trips WHERE expected_start >= '" + start + "' AND expected_end >='" + start + "'").Tables[0];
for (int i = 1; i < header_grid.Columns.Count; i++)
{
var col = header_grid.Columns[i];
foreach (DataGridViewRow row in header_grid.Rows)
{
var vehicle = row.Cells[0].Value.ToString();
var date = DateTime.Parse(col.Name);
foreach (DataRow trip_row in trip_dt.Rows)
{
if (vehicle == trip_row["vehicle"].ToString() && date >= DateTime.Parse(trip_row["expected_start"].ToString()) && date <= DateTime.Parse(trip_row["expected_end"].ToString()))
{
row.Cells[col.Name].Value = trip_row["trip_id"];
}
}
}
}
}
public static string GetTripWMSSyncSQL(String trip_id)
{
StringBuilder result = new StringBuilder();
result.AppendLine($"DELETE FROM ReleaseTripDetails WHERE trip = '{ trip_id }';");
result.AppendLine($"DELETE FROM ReleaseTrips WHERE trip_id = '{ trip_id }';");
DataSet set = DataSupport.RunDataSet($"SELECT * FROM Trips WHERE trip_id = '{trip_id}'; SELECT * FROM TripOrders WHERE trip = '{trip_id}';");
DataRow trip_row = set.Tables[0].Rows[0];
Dictionary <String, Object> header = new Dictionary <string, object>();
header.Add("trip_id", trip_id);
header.Add("authorized_receiver", trip_row["in_charge"].ToString());
header.Add("tms_name", DataSupport.GetTMSCode());
result.Append(DataSupport.GetInsert("ReleaseTrips", header));
foreach (DataRow row in set.Tables[1].Rows)
{
Dictionary <String, Object> detail = new Dictionary <string, object>();
detail.Add("trip", trip_id);
detail.Add("order_id", row["order_id"].ToString());
detail.Add("drop_sequence", row["drop_sequence"].ToString());
result.Append(DataSupport.GetInsert("ReleaseTripDetails", detail));
}
return(result.ToString());
}
private void dataGridView1_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
{
var dialog = new MasterFile.addcustomer();
string code = dataGridView1.Rows[e.RowIndex].Cells[colCustCode.Name].Value.ToString();
var dt = DataSupport.RunDataSet("Select * from base_customer where custCode = '" + code + "'").Tables[0];
foreach (DataRow row in dt.Rows)
{
dialog.txtcustCode.Text = row["custCode"].ToString();
dialog.txtcustName.Text = row["customer"].ToString();
dialog.txtaddress.Text = row["address"].ToString();
dialog.txtbusiness.Text = row["businessStyle"].ToString();
dialog.txtcontact.Text = row["contactNo"].ToString();
dialog.txtpostCode.Text = row["postalCode"].ToString();
dialog.txttin.Text = row["tinNo"].ToString();
dialog.txtterms.Text = row["terms"].ToString();
dialog.txtdisc1.Text = row["discount1"].ToString();
dialog.txtdisc2.Text = row["discount2"].ToString();
dialog.txtdisc3.Text = row["discount3"].ToString();
dialog.txtdisc4.Text = row["discount4"].ToString();
dialog.txtdisc5.Text = row["discount5"].ToString();
dialog.txtzone.Text = row["zone"].ToString();
}
dialog.txtcustCode.ReadOnly = true;
addcustomer.mode = "update";
dialog.ShowDialog();
display();
}
private void LoadRoutes()
{
DateTime start = DateTime.Parse(txtStart.Value.ToShortDateString());
DateTime end = DateTime.Parse(txtEnd.Value.ToShortDateString());
FillCommonData();
var route_dt = DataSupport.RunDataSet("SELECT * FROM RouteSchedules WHERE date >= '" + start + "' AND date <='" + end + "' ").Tables[0];
for (int i = 1; i < header_grid.Columns.Count; i++)
{
var col = header_grid.Columns[i];
foreach (DataGridViewRow row in header_grid.Rows)
{
var vehicle = row.Cells[0].Value.ToString();
var date = DateTime.Parse(col.Name);
foreach (DataRow route_row in route_dt.Rows)
{
if (vehicle == route_row["vehicle"].ToString() && date == DateTime.Parse(route_row["date"].ToString()))
{
row.Cells[col.Name].Value = route_row["route"].ToString();
}
}
}
}
}
private void product_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
{
String code = product.Rows[e.RowIndex].Cells[colProductCode.Name].Value.ToString();
txtCode.Text = null;
txtDescription.Text = null;
txtCategory.Text = null;
if (GetProduct(code))
{
if (String.IsNullOrEmpty(Codes.ToString()) || Codes.Equals(""))
{
var dRow = DataSupport.RunDataSet("Select * from products where product_id ='" + code + "'").Tables[0];
foreach (DataRow row in dRow.Rows)
{
txtCode.Text = row["product_id"].ToString();
txtDescription.Text = row["description"].ToString();
txtCategory.Text = row["category"].ToString();
}
tabControl1.SelectedTab = tabPage2;
}
else
{
tabControl1.SelectedTab = tabPage2;
}
label9.Visible = false;
txtSearch.Visible = false;
}
}
private bool GetProduct(String Pcode)
{
try
{
var dt = DataSupport.RunDataSet("select * from itemPrice i join products p on i.productID = p.product_id join uom U on i.uom = u.uom join priceType t on i.priceTypeId = t.priceTypeId where i.productID = '" + Pcode + "'").Tables[0];
if (dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
txtCode.Text = row["product_id"].ToString();
txtDescription.Text = row["description"].ToString();
txtCategory.Text = row["category"].ToString();
dataGridView1.DataSource = DataSupport.RunDataSet("select qty,unitPrice,i.uom,i.priceTypeId from itemPrice i join uom U on i.uom = u.uom join priceType t on i.priceTypeId = t.priceTypeId where productId = '" + Pcode + "'").Tables[0];
}
}
else
{
Codes = "";
uomPriceType();
dataGridView1.Refresh();
dataGridView1.DataSource = DataSupport.RunDataSet("select qty,unitPrice,i.uom,i.priceTypeId from itemPrice i join uom U on i.uom = u.uom join priceType t on i.priceTypeId = t.priceTypeId where productId = '" + Pcode + "'").Tables[0];
}
}
catch
{ }
return(true);
}
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 Returns_Load(object sender, EventArgs e)
{
{
var dt = DataSupport.RunDataSet("SELECT custCode FROM TransportCustomers ").Tables[0];
txtCustCode.DataSource = dt;
txtCustCode.DisplayMember = "custCode";
txtCustCode.ValueMember = "custCode";
}
{
var dt = DataSupport.RunDataSet("SELECT product_id FROM Products").Tables[0];
colCode.DataSource = dt;
colCode.DisplayMember = "product_id";
colCode.ValueMember = "product_id";
}
{
cbxSalesman.DataSource = DataSupport.RunDataSet("Select * from employees").Tables[0];
cbxSalesman.ValueMember = "employee_id";
cbxSalesman.DisplayMember = "name";
}
{
cbxwarehouse.DataSource = DataSupport.RunDataSet("Select * from [TransportProviders]").Tables[0];
cbxwarehouse.ValueMember = "transport_id";
cbxwarehouse.DisplayMember = "description";
}
//priceType();
//uom();
}
public static DataRow GetProductFromBarcode(String barcode)
{
// Search Default UOMs
//{
// DataTable productsDT = DataSupport.RunDataSet("SELECT product_id[PRODUCT], ''[MATCHED_UOM], * FROM Products WHERE pc_barcode = '" + barcode + "' OR case_barcode = '" + barcode + "'").Tables[0];
// if (productsDT.Rows.Count > 0)
// {
// var row = productsDT.Rows[0];
// if (row["pc_barcode"].ToString() == barcode)
// row["MATCHED_UOM"] = "PCS";
// if (row["case_barcode"].ToString() == barcode)
// row["MATCHED_UOM"] = "CASES";
// return row;
// }
//}
// Search Other UOMs
{
DataTable productsDT = DataSupport.RunDataSet("SELECT product[PRODUCT], uom[MATCHED_UOM], * FROM ProductUOMs WHERE barcode = '" + barcode + "' ").Tables[0];
if (productsDT.Rows.Count > 0)
{
var row = productsDT.Rows[0];
return(row);
}
}
return(null);
}
public void LoadData()
{
DataTable dt = DataSupport.RunDataSet(@"SELECT *
FROM
(
SELECT trans_source[Trans], trans_id[Id],line[Line], detected_on[Detected], product[Product], uom[Uom], lot_no[Lot No], expiry[Expiry], location[Location]
,variance_type[Type], variance_qty [Qty]
,variance_qty - ISNULL((SELECT SUM(qty_resolved) FROM Resolutions B WHERE A.trans_id = B.trans_id AND A.trans_source = B.trans_source AND A.line = B.line ),0)[Unresolved Qty]
FROM ForResolutions A
WHERE status = 'FOR RESOLUTION'
) T
WHERE [Unresolved Qty] >0 OR [Detected] >='" + DateTime.Now.AddMonths(-2) + "' ").Tables[0];
foreach (DataRow row in dt.Rows)
{
row["Expiry"] = DateTime.Parse(row["Expiry"].ToString()).ToShortDateString();
}
header_grid.DataSource = dt;
foreach (DataGridViewRow row in header_grid.Rows)
{
if (row.Cells["Unresolved Qty"].Value.ToString() == "0")
{
row.DefaultCellStyle.BackColor = Color.LightYellow;
}
}
}
private void LoadOrders()
{
orders_dict = new Dictionary <string, DataTable>();
foreach (DataGridViewRow row in products_grid.Rows)
{
String product = row.Cells["product"].Value.ToString();
orders_dict.Add(product, DataSupport.RunDataSet(
@"SELECT
P.order_id[Order ID]
, R.total_invoice_amount[Total Invoice Amount]
, R.client[Client]
, R.customer[Customer]
, P.qty[Qty Ordered]
, 'ACTIVE'[Status]
FROM PicklistDetails P
INNER JOIN ReleaseOrders R ON R.order_id = P.order_id
WHERE product = '" + product + @"'
AND uom = '" + row.Cells["uom"].Value.ToString() + @"'
AND lot_no = '" + row.Cells["lot_no"].Value.ToString() + @"'
AND expiry = '" + row.Cells["expiry"].Value.ToString() + "'"
).Tables[0]);
}
}
public static int HowManyPiecesInUOM(String product, String uom, int qty)
{
int result = 0;
//String sql = @"SELECT *,
// case_barcode
// ,
// CASE
// WHEN @uom='PCS'
// THEN 1
// WHEN @uom='PIECES'
// THEN 1
// WHEN @uom = 'CASES'
// THEN pcs_per_case
// ELSE
// (SELECT qty FROM ProductUOMs PU WHERE PU.product = L.product_id AND PU.uom = @uom )
// END
// [IN_PIECES]
// FROM Products L
// WHERE product_id = @product";
String sql = @"SELECT qty [IN_PIECES]
FROM ProductUOMs
WHERE product = @product AND uom = @uom";
DataTable dt = DataSupport.RunDataSet(sql, "product", product, "uom", uom).Tables[0];
result = int.Parse(dt.Rows[0]["IN_PIECES"].ToString()) * qty;
return(result);
}
public static DataTable GetPickedItems(String picklist, String order_id)
{
DataTable dt = DataSupport.RunDataSet(@"SELECT *
FROM
(
SELECT *
,
(SELECT TOP 1 order_id
FROM ForResolutions B
WHERE B.trans_id = A.picklist
AND B.product = A.product
AND B.uom = A.uom
AND B.lot_no = A.lot_no
AND B.expiry = A.expiry
)
[exist]
FROM PicklistDetails A
WHERE picklist='" + picklist + @"' AND order_id = '" + order_id + @"'
) T
WHERE exist IS NULL
"
).Tables[0];
return(dt);
}
public static int HowManyPiecesInWarehouse(String product_id)
{
int result = 0;
String sql = @"SELECT *
,CASE WHEN uom='CASES'
THEN (SELECT pcs_per_case FROM Products WHERE product = product_id) * qty
WHEN uom='PIECES'
THEN qty
WHEN uom='PCS'
THEN qty
ELSE
(SELECT qty FROM ProductUOMs PU WHERE PU.product = L.product AND PU.uom = L.uom ) * qty
END
[IN_PIECES]
FROM LocationProductsLedger L
WHERE product= @product
AND available_qty >0 AND expiry_status IS NULL AND expiry > GETDATE()";
DataTable dt = DataSupport.RunDataSet(sql, "product", product_id).Tables[0];
foreach (DataRow row in dt.Rows)
{
result += int.Parse(row["IN_PIECES"].ToString());
}
return(result);
}
private void DeliveryRegister()
{
System.Data.DataTable result = new System.Data.DataTable();
result.Columns.Add(new DataColumn("dr", typeof(String)));
result.Columns.Add(new DataColumn("drDate", typeof(DateTime)));
result.Columns.Add(new DataColumn("poNo", typeof(String)));
result.Columns.Add(new DataColumn("custname", typeof(String)));
result.Columns.Add(new DataColumn("terms", typeof(String)));
result.Columns.Add(new DataColumn("type", typeof(String)));
DataRow resultRow = result.NewRow();
var dt = DataSupport.RunDataSet("Select * from Global_drTrans where typeOfStock ='" + typeS.Text + "' and drDate between '" + from.Text + "' and '" + to.Text + "'").Tables[0];
foreach (DataRow row in dt.Rows)
{
resultRow = result.NewRow();
resultRow["dr"] = row["drNo"].ToString();
resultRow["drDate"] = row["drDate"].ToString();
resultRow["poNo"] = row["poNo"].ToString();
resultRow["custName"] = row["custName"].ToString();
resultRow["terms"] = row["terms"].ToString();
resultRow["type"] = row["typeOfStock"].ToString();
result.Rows.Add(resultRow);
}
ReportDocument ReportDocs = new ReportDocument();
ReportDocs = new Reports.Register.DrRegister();
ReportDocs.Database.Tables[0].SetDataSource(result);
crystalReportViewer1.ReportSource = ReportDocs;
crystalReportViewer1.Refresh();
}
public static DataTable WhereAreProductsInWarehouse(String product, String uom)
{
//DataTable result = new DataTable();
//result.Columns.Add("location", typeof(String));
//result.Columns.Add("lot_no", typeof(String));
//result.Columns.Add("expiry", typeof(String));
//result.Columns.Add("available_qty", typeof(int));
//foreach(DataRow dRow in DataSupport.RunDataSet(String.Format(@"SELECT LPL.location, LPL.lot_no, LPL.expiry, (LPL.[available_qty]*PU.qty)available_qty
// FROM [LocationProductsLedger] LPL INNER JOIN [productuoms] PU ON PU.product = LPL.product AND PU.uom = LPL.uom
// WHERE LPL.product = '{0}' AND available_qty > 0", product)).Tables[0].Rows)
//{
// //DataRow tblRow = result.NewRow();
// result.Rows.Add(dRow);
//}
return(DataSupport.RunDataSet(String.Format(@"SELECT LPL.location, LPL.lot_no, LPL.expiry, (LPL.[available_qty]*PU.qty)available_qty
FROM [LocationProductsLedger] LPL INNER JOIN [productuoms] PU ON PU.product = LPL.product AND PU.uom = LPL.uom
WHERE LPL.product = '{0}' AND available_qty > 0", product)).Tables[0]);
//return DataSupport.RunDataSet(@"SELECT location, lot_no, expiry, available_qty
// FROM LocationProductsLedger
// WHERE product = @product AND uom = @uom
// AND available_qty >0
// ORDER BY expiry DESC
// ", "product", product, "uom", uom).Tables[0];
}
private void SalesRegister()
{
System.Data.DataTable result = new System.Data.DataTable();
result.Columns.Add(new DataColumn("SI", typeof(String)));
result.Columns.Add(new DataColumn("SIdate", typeof(DateTime)));
result.Columns.Add(new DataColumn("PO", typeof(String)));
result.Columns.Add(new DataColumn("custName", typeof(String)));
result.Columns.Add(new DataColumn("Terms", typeof(String)));
result.Columns.Add(new DataColumn("totalAmount", typeof(Decimal)));
result.Columns.Add(new DataColumn("type", typeof(String)));
DataRow resultRow = result.NewRow();
var dt = DataSupport.RunDataSet("Select * from Global_ProductTrans where typeOfStock ='" + typeS.Text + "' and siDate between '" + from.Text + "' and '" + to.Text + "'").Tables[0];
foreach (DataRow row in dt.Rows)
{
resultRow = result.NewRow();
resultRow["SI"] = row["siNo"].ToString();
resultRow["SIdate"] = row["sidate"].ToString();
resultRow["PO"] = row["poNo"].ToString();
resultRow["custName"] = row["custName"].ToString();
resultRow["Terms"] = row["terms"].ToString();
resultRow["totalAmount"] = row["totalAmount"].ToString();
resultRow["type"] = row["typeOfStock"].ToString();
result.Rows.Add(resultRow);
}
ReportDocument ReportDocs = new ReportDocument();
ReportDocs = new Reports.Register.SalesRegister();
ReportDocs.Database.Tables[0].SetDataSource(result);
crystalReportViewer1.ReportSource = ReportDocs;
crystalReportViewer1.Refresh();
}
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];
if (e.ColumnIndex == dataGridView1.Columns[colCode.Name].Index)
{
foreach (DataRow row in dt.Rows)
{
dataGridView1.Rows[e.RowIndex].Cells[colDescription.Name].Value = row["description"].ToString();
}
}
if (e.ColumnIndex == dataGridView1.Columns[colUnit.Name].Index)
{
dataGridView1.Rows[e.RowIndex].Cells[colDisc.Name].Value = 0;
String unit = dataGridView1.Rows[e.RowIndex].Cells[colUnit.Name].Value.ToString();
var dts = DataSupport.RunDataSet("select * from base_price where product_code ='" + code + "' and uom ='" + unit + "' and priceType ='" + cbxPriceType.Text + "'").Tables[0];
foreach (DataRow rows in dts.Rows)
{
dataGridView1.Rows[e.RowIndex].Cells[colPrice.Name].Value = rows["price"].ToString();
}
}
compute();
}
catch
{ }
}
private void LoadData()
{
txtProduct.Text = product_row.Cells["Product"].Value.ToString();
txtUom.Text = product_row.Cells["Uom"].Value.ToString();
txtLotNo.Text = product_row.Cells["Lot No"].Value.ToString();
txtExpiry.Text = DateTime.Parse(product_row.Cells["Expiry"].Value.ToString()).ToShortDateString();
txtLocation.Text = product_row.Cells["Location"].Value.ToString();
txtSource.Text = product_row.Cells["Id"].Value.ToString();
txtDetected.Text = DateTime.Parse(product_row.Cells["Detected"].Value.ToString()).ToShortDateString();
txtVarianceType.Text = product_row.Cells["Type"].Value.ToString();
txtUnresolvedQty.Text = product_row.Cells["Unresolved Qty"].Value.ToString();
String trans_source = product_row.Cells["Trans"].Value.ToString();
String trans_id = product_row.Cells["Id"].Value.ToString();
String line = product_row.Cells["Line"].Value.ToString();
DataTable dt = DataSupport.RunDataSet(@"SELECT qty_resolved [Qty Resolved],resolved_on[Resolved On], resolved_by[Resolved By], charge_to[Charge To], Explanation
FROM Resolutions
WHERE trans_source = '" + trans_source + @"'
AND trans_id = '" + trans_id + @"'
AND line = '" + line + @"'").Tables[0];
header_grid.DataSource = dt;
}
private void product()
{
var dt = DataSupport.RunDataSet("SELECT product_code FROM base_product").Tables[0];
colCode.DataSource = dt;
colCode.DisplayMember = "product_code";
colCode.ValueMember = "product_code";
}
private void priceType()
{
{
cbxpriceType.DataSource = DataSupport.RunDataSet("SELECT * FROM Pricetype").Tables[0];
cbxpriceType.DisplayMember = "priceType";
cbxpriceType.ValueMember = "priceTypeID";
}
}
private void btnExecute_Click(object sender, EventArgs e)
{
int cycle_days = int.Parse(txtCycleDays.Text);
DataSet set = DataSupport.RunDataSet(@"SELECT * FROM locations; SELECT DISTINCT location, finished_on, cycle
FROM PhysicalCounts INNER JOIN PhysicalCountDetails ON phcount = phcount_id
");
DataTable locationsDT = set.Tables[0];
DataTable physicalCountDT = set.Tables[1];
header_grid.Columns.Clear();
header_grid.Columns.Add("Location", "Location");
header_grid.Columns.Add("Checked", "Checked Within " + cycle_days + " Day(s)? ");
DateTime now = DateTime.Parse(DateTime.Now.ToShortDateString());
DateTime start = now.AddDays(-1 * cycle_days);
DateTime end = now.AddDays(cycle_days);
for (DateTime i = start; i <= end; i = i.AddDays(1))
{
var col = header_grid.Columns[header_grid.Columns.Add(i.ToShortDateString(), i.ToShortDateString())];
if (i == now)
{
col.DefaultCellStyle.BackColor = Color.Orange;
}
}
header_grid.Columns["Location"].Frozen = true;
header_grid.Columns["Checked"].Frozen = true;
foreach (DataRow row in locationsDT.Rows)
{
String location_id = row["location_id"].ToString();
var new_row = header_grid.Rows[header_grid.Rows.Add(location_id)];
Boolean within_cycle = false;
for (int i = 2; i < header_grid.Columns.Count; i++)
{
String datetime = DateTime.Parse(header_grid.Columns[i].HeaderText).ToShortDateString();
foreach (DataRow pc_row in physicalCountDT.Rows)
{
if (pc_row["location"].ToString() == location_id && DateTime.Parse(pc_row["finished_on"].ToString()).ToShortDateString() == datetime)
{
new_row.Cells[i].Value = pc_row["cycle"].ToString();
within_cycle = true;
}
}
}
if (within_cycle)
{
new_row.Cells[1].Value = "YES";
new_row.DefaultCellStyle.BackColor = Color.LightYellow;
}
else
{
new_row.Cells[1].Value = "NO";
}
}
}
public static DataTable WhereAreProductsInWarehouse(String product, String uom)
{
return(DataSupport.RunDataSet(@"SELECT location, lot_no, expiry, available_qty
FROM LocationProductsLedger
WHERE product = @product AND uom = @uom
AND available_qty >0
ORDER BY expiry DESC
", "product", product, "uom", uom).Tables[0]);
}