JSon file created thru Linc: Records are not distinct

  • Thread starter Thread starter OldeEnglishD
  • Start date Start date
O

OldeEnglishD

Guest
===============================================================

frmMain

===============================================================


using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using ErrorLog;
using Microsoft.Office.Interop.Excel;

namespace CambridgeToyota
{
public partial class frmMain : Form
{
public frmMain()
{
Microsoft.Office.Interop.Excel.Application oXLFrom;
Microsoft.Office.Interop.Excel.Application oXLFromLookup;
_Workbook oWBFrom = null;
_Workbook oWBFromLookup = null;
string strFile = "", strFileLookup = "";
int x = 7, y = 0, intRows = 0, intReturn = 0, intRowsLookup = 0;
var lstOrders = new List<Tuple<string, string, int, string, string, string, string>>();
var lstOrdersLookup = new List<Tuple<string, string, string, string>>();
DialogResult? result = null;

InitializeComponent();
FormClosing += FrmMain_FormClosing;
if (SharedCode.CreateLogFolders(ProductName) == -1)
Environment.Exit(0);
oXLFrom = new Microsoft.Office.Interop.Excel.Application();
oXLFromLookup = new Microsoft.Office.Interop.Excel.Application();

//Main Spreadsheet of Orders
try
{
//x = x / y;
result = dlgSelFile.ShowDialog();
}
catch (Exception ex)
{
System.Windows.Forms.MessageBox.Show("frmMain.ShowDialog Method: " + ex.Message, "Call Information Technology",
System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
LogError.Log(ex, "Select Kanban\\Skid File", "C:\\LogFile\\xxx\\ErrorLog", "frmMain.ShowDialog Method:");
CloseExcelProcesses_dll.Program.CloseProjectExcelFiles(ref oXLFrom, ref oXLFromLookup);
CloseExcelProcesses_dll.Program.Main();
Environment.Exit(0);
}

if (result == DialogResult.OK) // Test result.
{
strFile = this.dlgSelFile.FileName;
}
else
{
CloseExcelProcesses_dll.Program.CloseProjectExcelFiles(ref oXLFrom, ref oXLFromLookup);
CloseExcelProcesses_dll.Program.Main();
Environment.Exit(0);
}

try
{
//File of invoice records to parse
//x = x / y;
oWBFrom = oXLFrom.Workbooks.Open(@strFile);
}
catch (Exception ex)
{
System.Windows.Forms.MessageBox.Show("frmMain.(Open File): " + ex.Message, "Call Information Technology",
System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
LogError.Log(ex, "Open Excel File", "C:\\LogFile\\xxx\\ErrorLog", "frmMain.(Open File):");
CloseExcelProcesses_dll.Program.CloseProjectExcelFiles(ref oXLFrom, ref oXLFromLookup);
CloseExcelProcesses_dll.Program.Main();
Environment.Exit(0);
}


//Lookup Spreadsheet for Orders for Palletization
dlgSelFile.Title = "Select Kinbad/Skid Palletization LOOKUP file to Process";

try
{
//x = x / y;
result = dlgSelFile.ShowDialog();
}
catch (Exception ex)
{
System.Windows.Forms.MessageBox.Show("frmMain.ShowDialog Method: " + ex.Message, "Call Information Technology",
System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
LogError.Log(ex, "Select Kanban\\Skid LOOKUP File", "C:\\LogFile\\xxx\\ErrorLog", "frmMain.ShowDialog Method:");
oWBFrom.Close();
CloseExcelProcesses_dll.Program.CloseProjectExcelFiles(ref oXLFrom, ref oXLFromLookup);
CloseExcelProcesses_dll.Program.Main();
Environment.Exit(0);
}

if (result == DialogResult.OK) // Test result.
{
strFileLookup = this.dlgSelFile.FileName;
}
else
{
oWBFrom.Close();
CloseExcelProcesses_dll.Program.CloseProjectExcelFiles(ref oXLFrom, ref oXLFromLookup);
CloseExcelProcesses_dll.Program.Main();
Environment.Exit(0);
}

try
{
//x = x / y;
//File of invoice records to parse
oWBFromLookup = oXLFromLookup.Workbooks.Open(@strFileLookup);
}
catch (Exception ex)
{
System.Windows.Forms.MessageBox.Show("frmMain.(Open LOOKUP File): " + ex.Message, "Call Information Technology",
System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
LogError.Log(ex, "Open Excel LOOKUP File", "C:\\LogFile\\xxx\\ErrorLog", "frmMain.(Open LOOKUP File):");
oWBFrom.Close();
CloseExcelProcesses_dll.Program.CloseProjectExcelFiles(ref oXLFrom, ref oXLFromLookup);
CloseExcelProcesses_dll.Program.Main();
Environment.Exit(0);
}

lblInitProgress.Text = "Processing...";
foreach (Worksheet sheet in oWBFrom.Worksheets)
{
intRows = SharedCode.TotRowSSCnt(sheet);
}

foreach (Worksheet sheet in oWBFromLookup.Worksheets)
{
intRowsLookup = SharedCode.TotRowSSCnt(sheet);
}

intReturn = FillList.Orders(oWBFrom, oWBFromLookup, ref lstOrders, intRows, ref lstOrdersLookup, intRowsLookup);
oWBFrom = null;
oWBFromLookup = null;
CloseExcelProcesses_dll.Program.CloseProjectExcelFiles(ref oXLFrom, ref oXLFromLookup);
CloseExcelProcesses_dll.Program.Main();
Environment.Exit(0);
}

private void FrmMain_FormClosing(object sender, FormClosingEventArgs e)
{
dlgSelFile.Dispose();
}

}
}


============================================================================

FillList class

============================================================================


using Microsoft.Office.Interop.Excel;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json;
using System.IO;

namespace CambridgeToyota
{
public static class FillList
{
const int PART_NUM = 6;
const int SUPPLIER = 7;
const int QPC = 18;
const int ORDER = 24;
const int DOCK = 26;
const int PLANT = 27;
const int PO_NUM = 28;
const int PO_NUM_LOOKUP = 4;
const int LINE_SIDE_ADDRESS_LOOKUP = 5;
const int PALLET_LOOKUP = 6;
const int SKIDID_LOOKUP = 7;

public static string kanban { get; private set; }

public static int Orders(_Workbook oWBFrom, _Workbook oWBFromLookup, ref List<Tuple<string, string, int, string, string, string, string>> lstOrders, int intRows,
ref List<Tuple<string, string, string, string>> lstOrdersLookup, int intRowsLookup)
{
_Worksheet oSheet;
string strPartNum = "", strOrderNum = "", strSupplier = "", strDock = "", strPlant = "", strPONum = "";
string strLinSideAddr = "", strPallet = "", strSkidID = "", strJSonFile = "";
int i, k = 0, intQPC;
var lstOrdersSorted = new List<Tuple<string, string, int, string, string, string, string>>();
List<Tuple<string, string>> lstGroupCnts = new List<Tuple<string, string>>();
var lstOrdersLookupSorted = new List<Tuple<string, string, int, string, string, string, string>>();
var lstOrdersLookupClone = new List<Tuple<string, string, string, string>>();
//string json;

oSheet = (_Worksheet)oWBFrom.ActiveSheet;

for (i = 2; i <= intRows; i++)
{
strPartNum = oSheet.Cells[i, PART_NUM].Text;
strSupplier = oSheet.Cells[i, SUPPLIER].Text;
intQPC = Convert.ToInt32(oSheet.Cells[i, QPC].Text);
strOrderNum = oSheet.Cells[i, ORDER].Text;
strDock = oSheet.Cells[i, DOCK].Text;
strPlant = oSheet.Cells[i, PLANT].Text;
strPONum = oSheet.Cells[i, PO_NUM].Text;
lstOrders.Add(new Tuple<string, string, int, string, string, string, string>(strPartNum, strSupplier, intQPC,
strOrderNum, strDock, strPlant, strPONum));
}

oSheet = (_Worksheet)oWBFromLookup.ActiveSheet;
for (i = 2; i <= intRowsLookup; i++)
{
strPONum = oSheet.Cells[i, PO_NUM_LOOKUP].Text;
strLinSideAddr = oSheet.Cells[i, LINE_SIDE_ADDRESS_LOOKUP].Text;
strPallet = oSheet.Cells[i, PALLET_LOOKUP].Text;
strSkidID = oSheet.Cells[i, SKIDID_LOOKUP].Text;
lstOrdersLookup.Add(new Tuple<string, string, string, string>(strPONum, strLinSideAddr, strPallet, strSkidID));
}

lstOrdersLookupClone = lstOrdersLookup;
lstOrdersSorted = lstOrders.OrderBy(a => a.Item7).ToList();
var lstOrdersGrouped = lstOrdersSorted
.GroupBy(ac => new
{
ac.Item7
})
.Select(ac => new { PONum = ac.Key.Item7, Cnt = ac.Count() });

strPONum = "";
foreach (var lst in lstOrdersSorted)
{
if (strPONum == lst.Item7.ToString())
k--;
else
k = lstOrdersGrouped.First(a => a.PONum == lst.Item7.ToString()).Cnt;

strPONum = lst.Item7.ToString();
lstGroupCnts.Add(new Tuple<string, string>(strPONum, k.ToString()));
}

//bookmark - google search on C# to create JSON
JObject rss =
new JObject(
new JProperty("exceptions",
new JArray()),
new JProperty("skids",
new JArray("kanbans",
new JArray(JToken.FromObject(
from los in lstOrdersSorted
from lg in lstGroupCnts
//from losLUC in lstOrdersLookupClone
where (los.Item7 == lg.Item1) // && los.Item7 == losLUC.Item1)
select new
{
kanban = los.Item7,
partNumber = los.Item1,
boxNumber = lg.Item2,
//lineSideAddress = losLUC.Item2,
qpc = los.Item3
}).Distinct()
)
),
new JArray("exceptions",
new JArray())
));

strJSonFile = "V:\\AKushner\\Testing\\xxx.json";
if (File.Exists(strJSonFile))
File.Delete(strJSonFile);
while (File.Exists(strJSonFile))
;

// write JSON directly to a file
using (StreamWriter file = File.CreateText(@strJSonFile))
using (JsonTextWriter writer = new JsonTextWriter(file))
{
rss.WriteTo(writer);
}

return 1;
}
}
}

=========================================================================

How do I get distinct rows? group, groupby (where orderby is) doesn't work and .GroupBy as opposed to .Distinct()

doesn't work either. How in the world to you make the selected rows distinct?



=========================================================================

sample output with culprit rows set up for readability:

=========================================================================

{"exceptions":[],"skids":[["kanbans",[{"kanban":"B031","partNumber":"82152 0R350 00","boxNumber":"1","qpc":6},{"kanban":"B254","partNumber":"82154 0R120 00","boxNumber":"1","qpc":12},
{"kanban":"C424","partNumber":"82171 0R640 00","boxNumber":"4","qpc":12},
{"kanban":"C424","partNumber":"82171 0R640 00","boxNumber":"3","qpc":12},
{"kanban":"C424","partNumber":"82171 0R640 00","boxNumber":"2","qpc":12},
{"kanban":"C424","partNumber":"82171 0R640 00","boxNumber":"1","qpc":12},
{"kanban":"C424","partNumber":"82171 0R640 00","boxNumber":"4","qpc":12},
{"kanban":"C424","partNumber":"82171 0R640 00","boxNumber":"3","qpc":12},
{"kanban":"C424","partNumber":"82171 0R640 00","boxNumber":"2","qpc":12},
{"kanban":"C424","partNumber":"82171 0R640 00","boxNumber":"1","qpc":12},
{"kanban":"C424","partNumber":"82171 0R640 00","boxNumber":"4","qpc":12},
{"kanban":"C424","partNumber":"82171 0R640 00","boxNumber":"3","qpc":12},
{"kanban":"C424","partNumber":"82171 0R640 00","boxNumber":"2","qpc":12},
{"kanban":"C424","partNumber":"82171 0R640 00","boxNumber":"1","qpc":12},
{"kanban":"C424","partNumber":"82171 0R640 00","boxNumber":"4","qpc":12},
{"kanban":"C424","partNumber":"82171 0R640 00","boxNumber":"3","qpc":12},
{"kanban":"C424","partNumber":"82171 0R640 00","boxNumber":"2","qpc":12},
{"kanban":"C424","partNumber":"82171 0R640 00","boxNumber":"1","qpc":12},
{"kanban":"C820","partNumber":"82152 0R320 00","boxNumber":"6","qpc":6},{"kanban":"C820","partNumber":"82152 0R320 00","boxNumber":"5","qpc":6},{"kanban":"C820","partNumber":"82152 0R320 00",

========================================================

for distinct rows should appear for the kanban of "C424" and not 16.

Continue reading...
 

Similar threads

O
c# runs fine in release but not in batch file
Replies
0
Views
86
OldeEnglishD
O
O
Program and batch file works fine locally but not on Server
Replies
0
Views
122
OldeEnglishD
O
R
Invoking a method from class library containing code to log messages using Log4Net, using reflection, log file is not created
Replies
0
Views
122
Raghunathan S
R
J
File Output and Parallel Printer(LPT1) output is different
Replies
0
Views
95
Jeff0803
J
S
c# - TCP/IP multiple client not multi threaded
Replies
0
Views
111
StaliiX
S
Back
Top