public void Fill(DataTable dataTable, SQLSelect selectCommand, params OleDbParameter[] parameters)
{
OleDbDataAdapter sqlAdapter = new OleDbDataAdapter();
OleDbCommand sqlCommand = ConnectionDB.CreateCommand();
string command = "SELECT ";
foreach (DataColumn dataColumn in dataTable.Columns)
{
if (IsDataColumAppCalculated(dataColumn))
continue;
if (IsDataColumnJoined(dataColumn))
command += "[" + GetDataColumnJoinedTableName(dataColumn) + "].[" + dataColumn.ColumnName + "], ";
else
command += "[" + dataColumn.Table.TableName + "].[" + dataColumn.ColumnName + "], ";
}
command = command.TrimEnd(',', ' ');
command += " FROM [";
command += selectCommand.From + "]";
// dodawanie whera (jesli istnieje)
if (command != string.Empty)
command += " " + selectCommand.Where;
sqlCommand.CommandText = command;
sqlAdapter.SelectCommand = sqlCommand;
//Dodanie parametrów
foreach (OleDbParameter parameter in parameters)
sqlAdapter.SelectCommand.Parameters.Add(parameter);
sqlAdapter.Fill(dataTable);
}
private void GenerateNewLabels(List <DataRow> r)
{
var l = r.GroupBy(p => p[SQLGroup].ToString());
r.ForEach(x =>
{
int _qty = SQLQty != "" ? Convert.ToInt32(x[SQLQty]):1;
var _SP = new SP(Values.gDatos, "pAddEtiquetasDetalle");
_SP.AddParameterValue("codigo", txtCode.Text.ToUpper());
var _split = SQLSelect.Split('|');
string _dataString = "";
_split.ToList().ForEach(s => _dataString += x[s] + "|");
_dataString = _dataString.Substring(0, _dataString.Length - 1);
_SP.AddParameterValue("parametros", SQLParameterString.Replace("'", "#"));
_SP.AddParameterValue("datos", _dataString);
_SP.AddParameterValue("qty", _qty);
_SP.AddParameterValue("Grupo", SQLGroup != "" ? x[SQLGroup] : "");
_SP.Execute();
if (_SP.LastMsg.Substring(0, 2) != "OK")
{
CTWin.MsgError("Error: " + _SP.LastMsg);
return;
}
Application.DoEvents();
});
}
public override string SerializeTableExists(SQLTableExists tableExists)
{
var select = new SQLSelect();
select.Tables.Add("TABLES").SchemaName = "INFORMATION_SCHEMA";
select.Where.Add("TABLE_NAME", ComparisonOperator.EqualTo, tableExists.Name);
return SerializeSelect(select);
}
public override string SerializeBeforeSelectFields(SQLSelect select)
{
var topClause = String.Empty;
if (select.Top > 0)
topClause = " TOP " + select.Top.ToString() + " ";
return base.SerializeBeforeSelectFields(select) + topClause;
}
private void ShowDetails()
{
vsLabels.ClearEspackControl();
vsLabels.ColumnCount = 0;
vsLabels.Conn = Values.gDatos;
string _group = "";
if (vsGroups.CurrentCell != null)
{
_group = (vsGroups.CurrentCell.Value.ToString() != "" ? " and grupo='" + vsGroups.CurrentCell.Value + "'" : "");
}
List <string> _SelectFields = new List <string>();
_SelectFields.Add("IDREG");
_SelectFields.AddRange(SQLSelect.Split('|'));
_SelectFields.Add("QTY");
_SelectFields.Add("PRINTED");
for (int i = 0; i < _SelectFields.Count; i++)
{
vsLabels.AddColumn(_SelectFields[i].ToUpper());
}
SQLPrintable.Split('|').ToList().ForEach(x => ((CtlVSColumn)vsLabels.Columns[x.ToString()]).Print = true);
((CtlVSColumn)vsLabels.Columns[0]).Aggregate = AggregateOperations.COUNT;
((CtlVSColumn)vsLabels.Columns["QTY"]).Aggregate = AggregateOperations.SUM;
var _Sql = string.Format("SELECT IDREG,DATA=datos,QTY,PRINTED=impreso FROM etiquetas_detalle WHERE codigo='{0}' and parametros='{1}'{2} order by datos,idreg", txtCode.Text, SQLParameterString.Replace("'", "#"), _group);
using (var _RS = new DynamicRS(_Sql, Values.gDatos))
{
_RS.Open();
_RS.ToList().ForEach(x =>
{
List <string> row = new List <string>();
row.Add(x["IDREG"].ToString());
row.AddRange(x["DATA"].ToString().Split('|'));
row.Add(x["QTY"].ToString());
row.Add(x["PRINTED"].ToString());
vsLabels.Rows.Add(row.ToArray());
//Application.DoEvents();
//vsLabels.Rows.Add(x["IDREG"].ToString(), x["DATA"].ToString(), x["QTY"].ToString(), x["PRINTED"]);
});
};
//vsLabels.UpdateEspackControl();
}
// Print all "unprinted" labels of the selected group
private void btnPrint_Click(object sender, EventArgs e)
{
SetFormEnabled(false);
if (cboPrinters.Value.ToString() == "")
{
SetFormEnabled(true);
CTWin.MsgError("Select a printer first.");
return;
}
string _printerType = "";
string _printerAddress = "";
int _printerResolution = 0;
using (var _RS = new DynamicRS(string.Format("select descripcion,cmp_varchar,cmp_integer from datosEmpresa where codigo='{0}'", cboPrinters.Value), Values.gDatos))
{
_RS.Open();
_printerAddress = _RS["cmp_varchar"].ToString();
_printerType = _RS["descripcion"].ToString().Split('|')[0];
_printerResolution = Convert.ToInt32(_RS["cmp_integer"]);
}
cLabel _delimiterLabel;
cLabel _label;
if (_printerType == "ZPL")
{
_delimiterLabel = new ZPLLabel(labelHeight, labelWidth, 3, _printerResolution);
if (!NoDelim)
{
delimiterLabel.delim(_delimiterLabel, "START", SQLParameterString.Replace(" WHERE ", ""));
}
_label = new ZPLLabel(labelHeight, labelWidth, 3, _printerResolution);
}
else
{
SetFormEnabled(true);
throw new NotImplementedException();
}
using (var _printer = new cRawPrinterHelper(_printerAddress))
{
//print delimiter start
if (!NoDelim)
{
_printer.SendUTF8StringToPrinter(_delimiterLabel.ToString(), 1);
}
using (var _RS = new DynamicRS(string.Format("Select * from Campos_CS where codigo='{0}'", txtCode.Text), Values.gDatos))
{
_RS.Open();
_RS.ToList().ForEach(z =>
{
float _bh = z["barcodeHeight"] is DBNull ? 0 : Convert.ToSingle(z["barcodeHeight"]);
float _bm = z["barcodeWidthmult"] is DBNull ? 0 : Convert.ToSingle(z["barcodeWidthmult"]);
float _cs = z["charSize"] is DBNull ? 0 : Convert.ToSingle(z["charSize"]);
_label.addLine(Convert.ToInt32(z["Col"]), Convert.ToInt32(z["Fila"]), Convert.ToSingle(CT.Qnuln(z["TamTexto"])), z["Orientacion"].ToString(), z["Estilo"].ToString(), z["Texto"].ToString(), _cs, _bh, _bm);
});
}
Dictionary <string, string> _parameters = new Dictionary <string, string>();
SQLSelect.Split('|').ToList().ForEach(x => _parameters.Add(x, ""));
string _group = "";
vsLabels.ToList().Where(line => line.Cells["PRINTED"].Value.ToString() == "N").ToList().ForEach(line =>
{
_parameters.ToList().ForEach(p => _parameters[p.Key] = line.Cells[p.Key].Value.ToString());
if (SQLGroup != "" && _group != line.Cells[SQLGroup].Value.ToString())
{
_group = line.Cells[SQLGroup].Value.ToString();
if (!NoDelim)
{
delimiterLabel.delim(_delimiterLabel, "GROUP", SQLGroup + "|" + _group);
_printer.SendUTF8StringToPrinter(_delimiterLabel.ToString(), 1);
}
}
_printer.SendUTF8StringToPrinter(_label.ToString(_parameters, Convert.ToInt32(line.Cells["QTY"].Value)), 1);
//cRawPrinterHelper.SendUTF8StringToPrinter(_printerAddress, _label.ToString(_parameters), Convert.ToInt32("2"));
ChangeLineStatus(line);
});
if (!NoDelim)
{
delimiterLabel.delim(_delimiterLabel, "END", "***");
_printer.SendUTF8StringToPrinter(_delimiterLabel.ToString(), 1);
}
SetFormEnabled(true);
}
}
// Get/Generate the labels
private void btnObtain_Click(object sender, EventArgs e)
{
SetFormEnabled(false);
clearing = true;
vsGroups.ClearEspackControl();
vsParameters.ToList().ForEach(z =>
{
Parameters[z.Cells[0].Value.ToString()] = z.Cells[1].Value.ToString();
});
//
var s = Parameters.Where(x => x.Value == "").ToDictionary(a => a.Key, a => a.Value);
//Dictionary<string, string>)
if (s.Count != 0)
{
MessageBox.Show("Parameter " + s.First().Key + " must be entered.", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
SetFormEnabled(true);
clearing = false;
return;
}
;
var _Sql = "SELECT " + SQLSelect.Replace("|", ",") + " FROM " + SQLView;
SQLParameterString = "";
Parameters.ToList().ForEach(x =>
{
Parameters[x.Key] = x.Value;
SQLParameterString += x.Key + (x.Value != "NOTHING" ? "='" + x.Value + "'" : "") + " and ";
});
if (SQLParameterString != "")
{
SQLParameterString = " WHERE " + SQLParameterString.Substring(0, SQLParameterString.Length - 5);
}
_Sql += SQLParameterString;
if (SQLOrder != "")
{
_Sql += " ORDER BY " + SQLOrder.Replace("|", ",");
}
using (var _RS = new DynamicRS(_Sql, Values.gDatos))
{
_RS.Open();
if (_RS.EOF)
{
SetFormEnabled(true);
MessageBox.Show("No rows returned.", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
ShowDetails();
GenerateGroups(_RS.ToList());
if (_RS.RecordCount != vsLabels.RowCount)
{
GenerateNewLabels(_RS.ToList());
ShowDetails();
}
}
SetFormEnabled(true);
clearing = false;
}
public virtual string SerializeSelect(SQLSelect select)
{
if (select.Tables.Count == 0)
throw new Exceptions.DatabaseObjectsException("The table has not been set.");
var tokens = new TokenSerializer();
tokens.Add("SELECT");
tokens.Add(SerializeBeforeSelectFields(select));
tokens.Add(SerializeSelectFields(select.Fields));
tokens.Add("FROM");
tokens.Add(SerializeSelectTables(select.Tables));
tokens.Add(SerializeAfterSelectTables(select));
if (select.Where != null && !select.Where.IsEmpty)
{
tokens.Add("WHERE");
tokens.Add(SerializeConditions(select.Where));
}
if (select.GroupBy != null && !select.GroupBy.IsEmpty)
{
tokens.Add("GROUP BY");
tokens.Add(SerializeSelectGroupByFields(select.GroupBy));
}
if (select.OrderBy != null && !select.OrderBy.IsEmpty)
{
tokens.Add("ORDER BY");
tokens.Add(SerializeSelectOrderByFields(select.OrderBy));
}
if (select.Having != null && !select.Having.IsEmpty)
{
tokens.Add("HAVING");
tokens.Add(SerializeSelectHavingConditions(select.Having));
}
return tokens.ToString();
}
public override string SerializeSelect(SQLSelect select)
{
var tokens = new TokenSerializer();
tokens.Add(base.SerializeSelect(select));
if (select.Top > 0)
tokens.Add("LIMIT " + select.Top.ToString());
return tokens.ToString();
}
public override string SerializeViewExists(SQLViewExists viewExists)
{
var select = new SQLSelect();
select.Tables.Add("X$VIEW");
select.Where.Add("Xv$Name", ComparisonOperator.EqualTo, viewExists.ViewName);
return SerializeSelect(select);
}
public override string SerializeViewExists(SQLViewExists viewExists)
{
var select = new SQLSelect();
select.Tables.Add("VIEWS").SchemaName = "INFORMATION_SCHEMA";
select.Where.Add("TABLE_SCHEMA", ComparisonOperator.EqualTo, "PUBLIC");
select.Where.Add("TABLE_NAME", ComparisonOperator.EqualTo, viewExists.ViewName);
return SerializeSelect(select);
}
public Result Select(SQLSelect sql)
{
return(Select(sql.TableName, sql.ColNames, sql.Wh));
}
public DataTable Fill(DataTable dataTable, SQLSelect selectCommand)
{
Fill(dataTable, selectCommand, new OleDbParameter[] { });
//OleDbDataAdapter sqlAdapter = new OleDbDataAdapter();
//OleDbCommand sqlCommand = ConnectionDB.CreateCommand();
//string command = "SELECT ";
//foreach (DataColumn dataColumn in dataTable.Columns)
//{
// if (IsDataColumAppCalculated(dataColumn))
// continue;
// if (IsDataColumnJoined(dataColumn))
// command += "[" + GetDataColumnJoinedTableName(dataColumn) + "].[" + dataColumn.ColumnName + "], ";
// else
// command += "[" + dataColumn.Table.TableName + "].[" + dataColumn.ColumnName + "], ";
//}
//command = command.TrimEnd(',', ' ');
//command += " FROM [";
//command += selectCommand.From + "]";
//// dodawanie whera (jesli istnieje)
//if (command != string.Empty)
// command += " " + selectCommand.Where;
//sqlCommand.CommandText = command;
//sqlAdapter.SelectCommand = sqlCommand;
//sqlAdapter.Fill(dataTable);
return dataTable;
}
public override string SerializeViewExists(SQLViewExists viewExists)
{
var select = new SQLSelect();
select.Tables.Add("sqlite_master");
select.Where.Add("Type", ComparisonOperator.EqualTo, "View");
select.Where.Add("Name", ComparisonOperator.EqualTo, viewExists.ViewName);
return SerializeSelect(select);
}
public override string SerializeTableExists(SQLTableExists tableExists)
{
var select = new SQLSelect();
select.Tables.Add("sqlite_master");
select.Where.Add("type", ComparisonOperator.EqualTo, "table");
select.Where.Add("name", ComparisonOperator.EqualTo, tableExists.Name);
return SerializeSelect(select);
}
public override string SerializeSelect(SQLSelect select)
{
var tokens = new TokenSerializer();
tokens.Add(base.SerializeSelect(select));
if (select.PerformLocking)
tokens.Add("FOR UPDATE");
return tokens.ToString();
}
public override string SerializeTableExists(SQLTableExists tableExists)
{
var select = new SQLSelect();
select.Tables.Add("X$FILE");
select.Where.Add("Xf$name", ComparisonOperator.EqualTo, tableExists.Name);
return SerializeSelect(select);
}
/// <summary>
/// Serializes any commands that must be placed before the field listing
/// but after the SELECT command.
/// </summary>
public virtual string SerializeBeforeSelectFields(SQLSelect select)
{
return select.Distinct ? "DISTINCT" : String.Empty;
}
public virtual string SerializeAfterSelectTables(SQLSelect select)
{
return String.Empty;
}
public ModelSelect(SQLSelect select)
{
this.selectInstance = select;
this.info = Model.GetInfo <T>();
}
