Write to CSV with button. Used for Bartender

I was going to post this at the end of another thread, but I wasn’t really related to the original question. I want to as Chris Conn a question about how he writes out the CSV files for bartender (he references it in an answer about labels), and I think that this could be my first foray into putting some custom coding into a dashboard. Is there a way to mention/tag a specific person in a topic?

Anyways, here’s my question.

Chris,
How hard would it be to get a button like you have there to fire in a dashboard that would print (send a CSV to the network location for batender) of the selected rows? I’ve made macros to do that in excel, so I copy and paste the info into excel and then push the button, but if I could skip the excel step, a lot of people would be really happy around here. I see where I would add the code for the button (first time I’ve been in there…) Is that something that would be pretty easy to get a basic example copied and make the adjustments from there? Basically would just be a copy selected, include labels, and write to a csv file. Thanks for you help.

I should be able to put code here right?

Like twitter use the @ @Chris_Conn

gotcha, I don’t use twitter which is why I didn’t know that. Thanks!

Brandon,

That’s super easy! It has a few steps:

  1. Determine what printer (or allow user to select a printer)
  2. Compile the data you want on your label (to pass to bartender) - In you case, sounds like you already have your data in a grid so we just snatch it off there and pass it to…
  3. Function that takes your data and concatenates it into the CSV
  4. Write file to proper BT location that is HEADER SCV FOOTER

Yep - you can do it all in that button click. I’ll share code with you if you like, just don’t let Jose see it cuz he’ll beat me up #PastaProgrammer

//This takes all compiled csv's and selects # of copies, printer, label template, and seed doesn't matter but it ensures a unique bartender filename (which I needed for placing multiple trigger files at once)
private void PrintLabel(string headers, string datas, int copies, string printer, string label, int seed)
	{  //Bartender Header
		string text = string.Format("%BTW% /AF=\"{0}\" /D=\"<Trigger File Name>\" /PRN=\"{1}\"  /R=3 /C={2} /P ", label, printer, copies);
		string[] contents = new string[]
		{
			text,
			"%END%",  //bartender footer
			headers,
			datas
		};
		System.IO.File.WriteAllLines("\\\\UrServer\\c$\\Bartender\\STOCK"+seed.ToString()+".bt", contents);
	}

//This takes all the data I wanted to print and turns it into a CSV (and makes data headers too since I have my BT setup to use them)
	private void MakeLabelData(string pn, string desc, string qty, string matpn, string matdes, string date, string emp, string rev, string job, string dev, string boxnum, string ppc, string wipdes, string poref, string spare)
	{
		this.headers = string.Format("\"{0}\",\"{1}\",\"{2}\",\"{3}\",\"{4}\",\"{5}\",\"{6}\",\"{7}\",\"{8}\",\"{9}\",\"{10}\",\"{11}\",\"{12}\",\"{13}\",\"{14}\"", new object[]
		{
			"pn",
			"desc",
			"qty",
			"matpn",
			"matdes",
			"date",
			"emp",
			"rev",
			"job",
			"dev",
			"boxnum",
			"ppc",
			"wipdes",
			"poref",
			"spare"
		});
		this.datas = string.Format("\"{0}\",\"{1}\",\"{2}\",\"{3}\",\"{4}\",\"{5}\",\"{6}\",\"{7}\",\"{8}\",\"{9}\",\"{10}\",\"{11}\",\"{12}\",\"{13}\",\"{14}\"", new object[]
		{
			pn,
			desc,
			qty,
			matpn,
			matdes,
			date,
			emp,
			rev,
			job,
			dev,
			boxnum,
			ppc,
			wipdes,
			poref,
			spare
		});
	}

//In my case I cycled 3 rows on a grid and created a trigger file for each
private void epiButtonPrint_Click(object sender, EventArgs args)
	{
		string pn = this.epiUltraComboPart.Text;
		for (int i = 0; i < 3; i++)
		{
			int lblqty = (int)this.LabelGrid.Rows[i].Cells[0].Value;
			string partqty = (((int)this.LabelGrid.Rows[i].Cells[1].Value)).ToString();
			if (lblqty > 0 && partqty != null && partqty != "" && partqty != "0")
			{
				//I didn't use all the fields on this particular label so I just put some place holders
				string label = string.Format("C:\\Bartender\\Formats\\cc\\StockLabel.btw", new object[0]);
				this.MakeLabelData(pn, "desc", partqty, "", "", "", ((Session)this.oTrans.Session).EmployeeID, "", "j", "d", "b", "ppc", "wipdes", "poref", "spare");
				this.PrintLabel(this.headers, this.datas, lblqty, this.prntr, label, i);
			}
			//else MessageBox.Show("Failed - lblqty: "+lblqty.ToString() +"  partqty: "+partqty);
		}
	}

For printers, I linked printers to workstations. This is my default printer. I use a BAQ to get that printer based on the current workstation. I also use a BAQ to list all available printers in the grid in my image. The default (or selected) is highlighted but the user can click to select any printer.

	private void GetDefaultPrinter()
	{
		DynamicQueryAdapter dynamicQueryAdapter = new DynamicQueryAdapter(this.oTrans);
		dynamicQueryAdapter.BOConnect();
		QueryExecutionDataSet queryExecutionParametersByID = dynamicQueryAdapter.GetQueryExecutionParametersByID("CC_WORKSTATION_PRINTER");
		queryExecutionParametersByID.ExecutionParameter.Clear();
		queryExecutionParametersByID.ExecutionParameter.AddExecutionParameterRow("WokstationIDParam", ((Session)this.oTrans.Session).WorkstationID, "nvarchar", false, Guid.NewGuid(), "A");
		dynamicQueryAdapter.ExecuteByID("CC_WORKSTATION_PRINTER", queryExecutionParametersByID);
		if (dynamicQueryAdapter.QueryResults.Tables["Results"].Rows.Count > 0)
		{
			this.prntr = dynamicQueryAdapter.QueryResults.Tables["Results"].Rows[0]["SysPrinter_NetworkPath"].ToString();
			return;
		}
		MessageBox.Show("No printers configured. Contact C.Conn");
	}
2 Likes

Awesome, I’ll be giving this a shot. For printers, I’ll keep it simple to start and just set that in the commander/integration builder from bartender. Then after I have that set, I’ll look at how I want to make the printing flexible. I’m sure I’ll have some questions along that way. You’re the best!!

1 Like

You may come across some includes (using…) I forgot but we’ll tackle them.

I did make use of a couple global variables in those snippets:

// Add Custom Module Level Variables Here **
	private string prntr = "?";
	private string headers = "";
	private string datas = "";

And the printer paths are just UNC paths but they need to be installed on the server as far as I know:
\MyPrintServer\PrinterName

1 Like

So I am going to skip the printers to start with, and the header line for the bartender folder. I am going to control which template file is used by the network location that this drops the file into and use the settings in the integration builder to set that for now. So for the print label section I only need the line starting: System.IO.File.WriteAllLines, with the file drop location on my server right? The rest just adds the command header line for BT it looks like, which I won’t be using.

Next question, for the information being pulled from the grid, does it just pull from the BAQ grid that the tracker is tied to? Or does that need to be defined somewhere? And does it use the names of the headers to grab the data? Basically, will it pull data from hidden columns (which would be nice to not confuse users) and does it tie specifically to the names of the columns so that if the order of the columns get changed it doesn’t break everything? Also, yours loops through 3 line of the grid, which works if you always have 3 lines and if you want 3 trigger files. I would rather have one trigger file since I have some reports in bartender that list multiple records on pages instead of individual sheets. Can I make it so that will see what is selected in the grid? or at least print out what’s showing in the grid based on the filters? Obviously the number of lines is going to be variable.
So this line, defines on row in the grid right?

int lblqty = (int)this.LabelGrid.Rows[i].Cells[0].Value;

So I will need to grab all of the rows in the grid in order to get one trigger file. Do you know how I can do that?

Thanks for being willing to help out, and I apologize for being ignorant with programming, but I gotta learn somehow right? By the way, if we get a chance we should meet up at insights, I’ll definitely owe you a few beers if I can get this working.

Brandon

2 Likes

I am all for diving in to learn - I learn better by hard knocks personally.

I’ll try to answer these questions:
Yep, your WriteAllLines will create that file using the contents supplied and the path.

If you pull from the grid, then whatever is already populated can be accessed. I am not 100% sure about accessing hidden fields but I ASSUME it should be the same as a visible column (if not, we can access the datasource that feeds the grid if necessary) You would only need to use a BAQ if the grid in your dashboard (which is already fed by a BAQ) doesn’t have the desired information - so most likely in this case, no, you wont need to mess with any explicit BAQ stuff outside of the dashboard.

I had 3 rows on my example but you can easily cycle all rows by doing something like:
for(int x = 0; x<LabelGrid.Rows.Count; x++)
{
LabelGrid.Rows[x].Whatever
}

or a neater looking c# approach would be:
foreach(var row in LabelGrid.Rows) /.the var row now represents the current row as we iterate
{
//if(row.Selected) for only selected rows
row.Whatever
}

So this line, defines on row in the grid right?
int lblqty = (int)this.LabelGrid.Rows[i].Cells[0].Value;

Well kind of - it’s actually a specific column in a row. notice we have to cast the Cells[0].Value to an int because it is just an object. For a string it would be:
string mygridvalue = (string)this.LabelGrid.Rows[i].Cells[0].Value; It depends on the data type of the data stuffed in the field you are accessing.

Cells[0] meant I am accessing the first cell. You can also access by the column name:
string prntr = Row.Cells[“SysPrinter_NetworkPath”].Value.ToString();

I think you would first make a function to compile your desired data to a csv:

Maybe the easiest way is to go thru your grid (selected rows) and get data then just keep adding onto your CSV string (note we make sure not to put comma before first character and we place quotes around all data)
*NOTE * - I use LabelGrid in the example but you are accessing an existing grid so you will need to do something like:
EpiUltraGrid myGridName = (EpiUltraGrid)csm.GetNativeControlReference(“the guid of your grid here”);

UNTESTED, UGLY CODE FOLLOWS

string myCSV = ""; //global var

private void SomeFunction()
{
myCSV = ""; //make sure our CSV is empty
foreach(var row in LabelGrid.Rows)   `//.the var row now represents the current row as we iterate
{
   if(row.Selected)// for only selected rows
   {
    addDataToCSV(row.Cells["MyColumnName1"].Value.ToString());                          
    addDataToCSV(row.Cells["MyColumnName2"].Value.ToString());                          
    addDataToCSV(row.Cells["MyColumnName3"].Value.ToString());                          

   }
}


//Now myCSV has your data ready to feed to BT. You can write it disk using WriteAllLines. 
}

private void addDataToCSV(string datastring)
        {
            if (myCSV.Length > 0) myCSV += ",";
            myCSV += "\"" + datastring + "\"";
        }
1 Like

Damn italian coder!

1 Like

Come sapevi?

cause of the spaguetti code :wink:
Also, way to go post translate!

1 Like

Refactoring tool for our Italian coder?

[cid:image001.jpg@01D29F33.A97521A0]

3 Likes

OK, so I am working on trying to get this to work. (remember I know veeerrryy little about coding and C# in general). For the first step I just wanted to get a file to pop out. I didn’t care what was in it, just get something to go where I wanted with something in it. I figured that out (although it’s embarrassing how long it took) . So here is the code for that.

	string test = "test";
	System.IO.File.WriteAllText("network location and file name",test);

That go plugged into the event wizard and it created this

private static void epiButton1_Click(object sender, System.EventArgs args)
{
	
	string test = "test";
	System.IO.File.WriteAllText("\network location and file name",test);
	
}

So I was able to figure out the first part of simple making a button do something.

Now I get stuck because I don’t know how exactly to get the data defined. I took your start with the MakeLabelData and shortened it to only part number and description (for example if I just want to make a part label) But I’m not sure where to put that. I’m assuming that you put a private void (that’s like a mini program right?) in front of the event call so that you can use the program within the button click?

It’s looking to me like something even this simple might be past my ability level, I don’t know the bare bones basics that I need to know about C#…

1 Like

Copy your code and attach as a text file here, then list all of the
relevant controls and their guids and I’ll do it up for you

So, This is where the problem begins, when you say list all of the relevant controls and and guids, I sort of know what you are talking about, but not enough to effectively give you what you need. So let’s try this. I created a very simple dashboard with a query that lists all of the parts in the part master and a tracker view with a few simple filters. It’s all stock stuff so it should work in any system. I created the button and the event handler wizard to get to the point where I would start putting in code. (This is where I get stuck, because I don’t know enough about C# to know the basic set up procedures to even get to the googling code pieces to copy and paste in.) All I want is to be able to select a row, or some rows, and the selected information (and the column headers) be populated in a csv and dropped into a folder somewhere on the network. I don’t need to control any printers at this point, or create the BT control header because I will just set all of that up in the integration builder via bartender. The end goal here is to make something that is as simple as possible so that I can learn basic setup and be able to build on this example, although even this simple tool here would save a lot of time to print labels more easily.

PrintLabelTest.dbd (106.7 KB)

And if you don’t have time or want to do this for me, I totally understand. You have definitely already gone above and beyond. Hopefully this starting point will help a lot of other people besides just me and my company.

Thanks,
Brandon

I’m glad you sent me the file so I could see your confusion. I guess I’ve never written custom code on top of a dashboard. I am not even sure how we reference the grid object in this case lol. We’ll figure it out - I need to learn how as well.

In the meantime:

I’ll give you a couple general pointers
private void MakeLabelData(string pn, string desc)
{

}

private means that this function cant be seen outside of this file.
void is the return type (we aren’t returning anything here)
pn and desc are parameters being passed into the function, both are of type string (text)
The { and } denote the start and end of the function. Notice there are other ‘groupings’ of text that use { } as well but they always come in pairs.

In your error above, I believe your problem is your dashboard is defined as static. I’ll avoid trying to go into what all of that means but suffice it to say you can compile by changing the function definition to:
private static void MakeLabelData (string pn, string desc)

Also we have to make our global vars static.

Ok I’ve figured it out. You have to deploy your dashboard as an assembly, Add it to you menu, then you can customize it proper.

The only catches I saw was that I had to access the button you created as a system control (using csm.GetaNativeControlReference) which means you can NOT use the wizard to create the onClick event.

I’ve done that manually.Once you deploy your DB as assembly and open customization, you can use this as a guide. NOTE your GUID’s are probably going to be different for your grid and button but you can get those from the properties tab.

// Created: 3/20/2017 9:31:22 AM
// **************************************************
using System;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.Windows.Forms;
using Ice.BO;
using Ice.UI;
using Ice.Lib;
using Ice.Adapters;
using Ice.Lib.Customization;
using Ice.Lib.ExtendedProps;
using Ice.Lib.Framework;
using Ice.Lib.Searches;
using Ice.UI.FormFunctions;

public class Script
{
	// ** Wizard Insert Location - Do Not Remove 'Begin/End Wizard Added Module Level Variables' Comments! **
	// Begin Wizard Added Module Level Variables **

	// End Wizard Added Module Level Variables **

	// Add Custom Module Level Variables Here **
EpiUltraGrid LabelGrid;
EpiButton PrintButton;
string datas;
string headers;
string myCSV;

	public void InitializeCustomCode()
	{
		// ** Wizard Insert Location - Do not delete 'Begin/End Wizard Added Variable Initialization' lines **
		// Begin Wizard Added Variable Initialization

		// End Wizard Added Variable Initialization

		// Begin Wizard Added Custom Method Calls

		// End Wizard Added Custom Method Calls

 		LabelGrid = (EpiUltraGrid)csm.GetNativeControlReference("8d6c1c8e-c9b5-46f6-b859-4895c6c990ea");
		 PrintButton = (EpiButton)csm.GetNativeControlReference("312c5ff2-89c0-48ac-94ec-b2ec1dfdbf36");

			PrintButton.Click += new EventHandler(PrintClick);
	}

	public void DestroyCustomCode()
	{
		// ** Wizard Insert Location - Do not delete 'Begin/End Wizard Added Object Disposal' lines **
		// Begin Wizard Added Object Disposal

		// End Wizard Added Object Disposal

		// Begin Custom Code Disposal

		// End Custom Code Disposal
			PrintButton.Click -= new EventHandler(PrintClick);
	}

    private void MakeLabelData(string pn, string desc)
	{
		this.headers = string.Format("\"{0}\",\"{1}\"", new object[]
		{
			"pn",
			"desc"
		});
        this.datas = string.Format("\"{0}\",\"{1}\"", new object[]
		{
			pn,
			desc
		});
	}

	private void PrintClick(object sender, EventArgs e)
	{
		string to = "\\\\Server\\Folder\\junk.txt";
                string[] source = {""};


		myCSV = ""; //make sure our CSV is empty
		foreach(var row in LabelGrid.Rows)   //.the var row now represents the current row as we iterate
		{
		   if(row.Selected)// for only selected rows
		   {
		    addDataToCSV(row.Cells["Part_PartNum"].Value.ToString());                          
		    addDataToCSV(row.Cells["Part_PartDescription"].Value.ToString());                          
		    
		
		   }
		}
	
		MessageBox.Show("CSV: " + myCSV);
		source[0] = myCSV;
		System.IO.File.WriteAllLines(to,source);
        
	}

		private void addDataToCSV(string datastring)
        {
            if (myCSV.Length > 0) myCSV += ",";
            myCSV += "\"" + datastring + "\"";
        }

}
2 Likes

Alright, I’ll have to try it later. I’ll have to get customization privaledges turned on. Generally our strategy so far has been to stay away from customizations if we can so we make sure any upgrade will be easier. This doesn’t really apply since it’s to a custom dashboard, but that’s one of the reasons that I haven’t had that turned on for me (along with the fact that I don’t know what I’m doing, but that’s beside the point. :wink:.)

Do I want that whole line in there? Or should I shorten that to just pn and desc?

I’m sure I’ll have more questions, but that was the first thing that I saw that seemed a little odd.

And to reiterate, the things that I should be changing to my system would be the 2 guids, (one for the button and one for the grid), and the network location for the drop. Right?

Sure enough, I missed that. Remove all but your string pn and string desc parameters. (I updated the code)

You are correct, that should get it going <fingers crossed >

I would point out that your BAQ has the PN limited - that may be on purpose but for my testing I had to remove it since I didn’t have any matching pn’s