private void SetButchersLabels()
{
var sqlConn = new SqlConn(DbConnectionString());
try
{
if (sqlConn.TestConnection())
{
var sqlDatatable = new SqlDataTable();
var dt = new DataTable();
var sqlQuery = string.Empty;
sqlQuery = "SELECT * FROM tblCustomer";
dt = SqlDataTable.GetDatatable(sqlConn.GetSqlConnection(), sqlQuery);
SetControlsLookUpEdit(lookUpEdit_Customer, dt, "CustomerName", "IdCustomer", "Customer");
sqlQuery = "SELECT * FROM tblShift";
dt = SqlDataTable.GetDatatable(sqlConn.GetSqlConnection(), sqlQuery);
SetControlsLookUpEdit(lookUpEdit_Shift, dt, "Shift", "IdShift", "Shift");
lookUpEdit_Product.Enabled = false;
}
else
{
throw new Exception("Unexpected error when tried to connect to server and download data.");
}
}
catch (Exception Ex)
{
var message = string.Format(Ex.Message);
var title = "Database connection";
XtraMessageBox.Show(message, title, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void GetDataFromSI(string barCode)
{
var sqlConn = new SqlConn(SIConnectionString());
DataTable dt = new DataTable();
try
{
if (sqlConn.TestConnection())
{
var sqlQuery = string.Empty;
string batchPallet = string.Empty;
switch (barCode.Length)
{
case 14:
batchPallet = barCode.Substring(2);
_batchOrPallet = SqlQueryBatchPallet.PalletBatchField.palletid;
sqlQuery = SqlQueryBatchPallet.SelectPalletBatch(_batchOrPallet, batchPallet);
break;
case 22:
batchPallet = barCode.Substring(0, 12);
_batchOrPallet = SqlQueryBatchPallet.PalletBatchField.batchno;
sqlQuery = SqlQueryBatchPallet.SelectPalletBatch(_batchOrPallet, batchPallet);
break;
default:
batchPallet = barCode;
_batchOrPallet = SqlQueryBatchPallet.PalletBatchField.batchno;
sqlQuery = SqlQueryBatchPallet.SelectPalletBatch(_batchOrPallet, batchPallet);
break;
}
if (_butcherLabelsTable != null)
{
dt = SqlDataTable.GetDatatable(sqlConn.GetSqlConnection(), sqlQuery);
_butcherLabelsTable.Merge(dt);
}
else
{
dt = SqlDataTable.GetDatatable(sqlConn.GetSqlConnection(), sqlQuery);
_butcherLabelsTable = dt;
}
AddDataToGridView(dt);
}
else
{
throw new Exception("Unexpected error when tried to connect to SI database and download data.");
}
}
catch (Exception ex)
{
var message = string.Format(ex.Message);
var title = "SI database connection";
XtraMessageBox.Show(message, title, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void SetControlsLookUpEditProductList()
{
var sqlConn = new SqlConn(DbConnectionString());
var sett = Properties.Settings.Default;
int factoryId = sett.Factory;
string customerId = lookUpEdit_Customer.EditValue.ToString();
string sqlQuery = "SELECT * FROM v_ButcherLabelsProductList WHERE IdCustomer=" + customerId + " AND IdFactory=" + factoryId + "";
var dt = new DataTable();
dt = SqlDataTable.GetDatatable(sqlConn.GetSqlConnection(), sqlQuery);
lookUpEdit_Product.Enabled = true;
lblColorLabel.Text = string.Empty;
var control = lookUpEdit_Product.Properties;
control.DataSource = null;
control.DataSource = dt;
control.DisplayMember = "Description";
control.ValueMember = "ProdCode";
control.AppearanceDropDown.FontSizeDelta = 10;
control.AppearanceDropDownHeader.FontSizeDelta = 10;
LookUpColumnInfoCollection columns = control.Columns;
columns.Clear();
columns.Add(new LookUpColumnInfo("ProdCode", 150, "Code"));
columns.Add(new LookUpColumnInfo("Description", 400, "Description"));
columns.Add(new LookUpColumnInfo("CustomerName", 120, "Customer"));
columns.Add(new LookUpColumnInfo("LabelType", 100, "Label"));
}
private void XmlDataForReport()
{
string sqlQuery = "SELECT ProductionDate, ProdDescription, RawMaterialDescription, Customer, Shift, LabelDescription as Color, Lot, PalletId, Udf2, Udf3, Udf4, KillDate, LabelBatchNumber AS Batch FROM tblButcherLabelsData WHERE ProductionDate = '09/05/2017' AND Customer='Lidl' AND Shift='Dayshift' AND ProdCode='LD5204354' AND FactoryId='1'";
DataTable dt = new DataTable();
var sqlConn = new SqlConn(DbConnectionString());
dt = SqlDataTable.GetDatatable(sqlConn.GetSqlConnection(), sqlQuery);
dt.TableName = "tblPalletBatch";
dt.WriteXmlSchema(@"C:\Users\krzysztof.matyja\Dropbox\Employee365 Projects\Traceability\PalletBatch.xsd");
}
private void InsertLabelDataIntoDatabase(int BatchNumber)
{
var sqlConnection = new SqlConn(DbConnectionString());
// DataTable dt = _butcherLabelsTable;
foreach (DataRow dr in _butcherLabelsTable.Rows)
{
var insert = new InsertCommand(sqlConnection.GetSqlConnection());
insert.ProductionDate = (DateTime)(dateEdit_ProdDate.EditValue);
insert.ProdCode = lookUpEdit_Product.GetColumnValue("ProdCode").ToString();
insert.ProdDescription = lookUpEdit_Product.Text;
insert.Customer = lookUpEdit_Customer.Text;
insert.Shift = lookUpEdit_Shift.Text;
insert.LabelDescription = lookUpEdit_Product.GetColumnValue("LabelType").ToString();
insert.RawMaterialCode = dr["product"].ToString();
insert.RawMaterialDescription = dr["description"].ToString();
switch (_batchOrPallet)
{
case SqlQueryBatchPallet.PalletBatchField.palletid:
insert.PalletId = dr["PalletNumber"].ToString();
break;
case SqlQueryBatchPallet.PalletBatchField.batchno:
insert.BatchNumber = dr["PalletNumber"].ToString();
break;
default:
break;
}
insert.Udf2 = dr["udf2"].ToString();
insert.Udf3 = dr["udf3"].ToString();
insert.Udf4 = dr["udf4"].ToString();
insert.KillDate = (DateTime)(dr["killdate"]);
insert.Lot = dr["lot"].ToString();
insert.LabelBatchNumber = BatchNumber;
insert.Weight = (decimal)(dr["Weight"]);
insert.FactoryId = Properties.Settings.Default.Factory;
insert.ExecuteInsertLabel();
dataForPrintLabel = insert;
}
}
private void GetDataForGridViewBatch()
{
try
{
string sqlQuery = string.Empty;
DataTable dt = new DataTable("Batch");
var sqlConn = new SqlConn(DbConnectionString());
sqlQuery = "SELECT RawMaterialCode AS Code, RawMaterialDescription AS Description, KillDate, Weight, LabelBatchNumber AS Batch " +
"FROM tblButcherLabelsData " +
"WHERE ProductionDate = '" + string.Format("{0:MM/dd/yyyy}", dateEdit_ProdDate.EditValue) + "' AND " +
"Customer='" + lookUpEdit_Customer.Text + "' AND Shift='" + lookUpEdit_Shift.Text + "' AND " +
"ProdCode='" + lookUpEdit_Product.GetColumnValue("ProdCode").ToString() + "' AND " +
"FactoryId='" + Properties.Settings.Default.Factory + "'";
dt = SqlDataTable.GetDatatable(sqlConn.GetSqlConnection(), sqlQuery);
gridControl_Batch.DataSource = null;
gridControl_Batch.DataSource = dt;
}
catch (Exception ex)
{
XtraMessageBox.Show(ex.Message, "Download data from database", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}