private void SubmittoSTG(object sender, EventArgs e)
{
DataTable dt = new DataTable();
String message = "File will be generated to send the Order Details to 3PL warehouse. Continue this operation?";
String caption = "Irreversible Action";
MessageBoxButtons buttons = MessageBoxButtons.YesNo;
var result = MessageBox.Show(message, caption, buttons);
if (result == DialogResult.No)
{
return;
}
DataTable dtSTG = (DataTable)dataGridGP.DataSource;
dt = dtSTG.Copy();
try
{
string csvFileName = "CSV_" + DateTime.Now.ToString("MMddyyyy_HH_mm_ss") + ".csv";
if (STGComm.SubmitDatatoSTG(dt, csvFileName))
{
message = @"File Successfully Generated at C:\3PL_Excel_Exports\CSV\" + csvFileName.Trim();
caption = "Export Successful";
buttons = MessageBoxButtons.OK;
MessageBox.Show(message, caption, buttons);
SqlDataLoad_Click(sender, e);
}
else
{
message = "File Generation failed, Contact Administrator";
caption = "Export Failed";
buttons = MessageBoxButtons.OK;
MessageBox.Show(message, caption, buttons);
}
} catch (Exception ex)
{
message = "File Generation failed. Message: " + ex.Message;
caption = "Export Failed";
buttons = MessageBoxButtons.OK;
MessageBox.Show(message, caption, buttons);
}
}
private System.Data.DataTable GetSTGQuery(List <String> orderNumber)
{
StringBuilder queryBuilder = new StringBuilder();
queryBuilder.Append(@" select * from [View_Item_Shipped] ");
if (orderNumber != null)
{
queryBuilder.Append(" where [Customer Order Number] in (");
foreach (String item in orderNumber)
{
queryBuilder.Append("'" + item + "',");
}
queryBuilder.Length--;
queryBuilder.Append(")");
}
String query = queryBuilder.ToString().TrimEnd(',');
TextHelper.WriteLine(query);
return(STGComm.STGGetConnectionGenerator(query));
}
private Boolean PrepareDataInsert(DataTable dt)
{
return(STGComm.SubmitShippedDatainSystem(dt));
}
private DataSet CompareData(DataTable dtCSV)
{
DataSet ds = new DataSet();
DataTable dtSTGDataFromDB = STGComm.GetSTGDataFromWave(null);
DataColumn dataColumn = new DataColumn("Wave-Order-Item");
dataColumn.Expression = string.Format("{0}+'-'+{1}+'-'+{2}", "BHFWave", "Reference", "Item");
dtCSV.Columns.Add(dataColumn);
dtCSV.Columns[22].ColumnName = "ItemNo";
dtSTGDataFromDB.Columns[1].ColumnName = "OrderNo";
DataColumn dColumn = new DataColumn("Wave-Order-Item");
dColumn.Expression = string.Format("{0}+'-'+{1}+'-'+{2}", "Wave", "OrderNo", "Item");
dtSTGDataFromDB.Columns.Add(dColumn);
DataTable dtResult = DataTableHelper.JoinTwoDataTablesOnOneColumn(dtSTGDataFromDB, dtCSV, "Wave-Order-Item", DataTableHelper.JoinType.Left);
dtResult.TableName = "Result";
dtResult.Columns.Add("Difference");
DataTable dtDuplicate = dtResult.Clone();
dtDuplicate.TableName = "Duplicate";
DataRow newRow = dtDuplicate.NewRow();
var rowsToDelete = new List <DataRow>();
foreach (DataRow dr in dtResult.Rows)
{
dr["Difference"] = ((dr["Ordered Qty"].ToString() == "") ? 0 : int.Parse(dr["Ordered Qty"].ToString())) - ((dr["Shipped"].ToString() == "") ? 0 : int.Parse(dr["Shipped"].ToString()));
if (dr["Shipment Confirmation"].ToString() == "Yes" && int.Parse(dr["Difference"].ToString()) < 1)
{
newRow = dr;
dtDuplicate.ImportRow(newRow);
dtDuplicate.AcceptChanges();
rowsToDelete.Add(dr);
DataRow[] DrImportDelete = dtImport.Select("BHFWave = '" + dr["Wave"].ToString() + "' AND Reference = '" + dr["OrderNo"].ToString() + "' AND Item = '" + dr["Item"].ToString() + "'");
foreach (DataRow row in DrImportDelete)
{
dtImport.Rows.Remove(row);
}
}
}
rowsToDelete.ForEach(x => dtResult.Rows.Remove(x));
ds.Tables.Add(dtResult);
ds.Tables.Add(dtDuplicate);
return(ds);
}
private void SqlDataLoad_Click(object sender, EventArgs e)
{
try
{
TextHelper.WriteLine("Log Path : " + TextHelper.GetTempPath());
dataGridGP.DataSource = null;
dataGridGP.Update();
dataGridGP.Refresh();
DataTable daPowerhouse = new DataTable();
DataTable daGP = new DataTable();
DataTable daSTG = new DataTable();
DataTable dafinal = new DataTable();
DataTable dtItems = new DataTable();
daPowerhouse = BuildPHQuery();
TextHelper.WriteLine("Data received from Powerhouse");
//Removed 09/20/21. Bringing data in the original Query.
//var itemIds = daPowerhouse.AsEnumerable().Select(r => r.Field<String>("StrItemId")).ToList();
//daGP = BuildGPQuery(itemIds);
//TextHelper.WriteLine("Data received from GP Inventory");
List <StgOrdersCSV> ListOfObjects = daPowerhouse.DataTableToList <StgOrdersCSV>();
//Removed 09/20/21. Bringing data in the original Query.
//ListOfObjects = ListToDT.AddGPDatatoObjects(ListOfObjects, daGP, "Inventory");
var ordernNumber = daPowerhouse.AsEnumerable().Select(r => r.Field <String>("StrCustOrdNumber")).ToList();
daGP = BuildGPSalesQuery(ordernNumber);
TextHelper.WriteLine("Data received from GP Sales ");
ListOfObjects = ListToDT.AddGPDatatoObjects(ListOfObjects, daGP, "Order");
//Add Custom Vendor Numbers============================================================
STGComm sTGComm = new STGComm();
dtItems = sTGComm.GetItemsWithVendorNumber();
ListOfObjects = ListToDT.AddSTGVendorNumberDatatoObjects(ListOfObjects, dtItems, "BBBVendorNumber");
//End adding custom vendor numbres======================================================
// START DATA FILTERATION PROCESS BASED ON STG DB
//Get STG data to filter out old waves
daSTG = GetSTGQuery(ordernNumber);
TextHelper.WriteLine("Data received from STG");
LoadDataTable(daSTG, "Shipped");
//dataGridSTG.Columns.Clear();
//dataGridSTG.DataSource = daSTG;
ListOfObjects = ListToDT.FilterDataFromSTG(ListOfObjects, daSTG);
// END FILTERATION PROCESS
TextHelper.WriteLine("Final Table Loaded");
if (ListOfObjects != null && ListOfObjects.Count > 0)
{
dafinal = ListToDT.ToDataTable <StgOrdersCSV>(ListOfObjects);
LoadDataTable(dafinal, "Orders For Ship");
String message = "Data Loading Completed. Showing New Wave Found in PowerHouse";
String caption = "Loading Successfull";
MessageBoxButtons buttons = MessageBoxButtons.OK;
MessageBox.Show(message, caption, buttons);
}
else
{
String message = "No New Waves found in Powerhouse";
String caption = "Loading Successfull";
MessageBoxButtons buttons = MessageBoxButtons.OK;
MessageBox.Show(message, caption, buttons);
}
}
catch (Exception ex)
{
String message = "Error while Getting Data from Powerhouse. Message: " + ex.Message;
String caption = "Data Loading Failed";
MessageBoxButtons buttons = MessageBoxButtons.OK;
MessageBox.Show(message, caption, buttons);
}
//dataGridGP.Columns.Clear();
//dataGridGP.DataSource = dafinal;
}
private Boolean StoreInbondContainerData(DataTable dt)
{
return(STGComm.SubmitInboundImport(dt));
}