private void cbServicer_SelectedIndexChanged(object sender, EventArgs e)
{
if (cbServicer.Text != "")
{
tbServicerContact.Text = Vasko.ExecuteScalar("SELECT TOP 1 ServicerContact FROM Service WHERE Servicer = '" + cbServicer.Text.Replace("'", "''") + "'");
}
}
private void SendOnService_Load(object sender, EventArgs e)
{
Vasko.FillCombo("SELECT DISTINCT(SerialNumber) as SerialNumber FROM Items", cbSerialNumber, "SerialNumber");
Vasko.FillCombo("SELECT DISTINCT(DMMPropertyNum) as DMMPropertyNum FROM Items", cbDMMNumber, "DMMPropertyNum");
Vasko.FillCombo("SELECT DISTINCT(Servicer) as Servicer FROM Service", cbServicer, "Servicer");
Vasko.FillCombo("SELECT DISTINCT(CostCenter) as CostCenter FROM Service", cbCostCenter, "CostCenter");
}
private void btnDeleteService_Click(object sender, System.EventArgs e)
{
if (outlookGrid1.SelectedRows.Count > 0)
{
if (MessageBox.Show("Are you sure?", "Delete", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) == DialogResult.Yes)
{
String Selected = "";
foreach (DataGridViewRow dr in outlookGrid1.Rows)
{
if (dr.Selected)
{
if (Selected.Length > 0)
{
Selected += "," + dr.Cells["ServiceId"].Value;
}
else
{
Selected = dr.Cells["ServiceId"].Value.ToString();
}
}
}
try
{
String SQL = @"DELETE FROM Service WHERE ServiceId IN (" + Selected + ")";
Vasko.ExecuteCommand(SQL);
MessageBox.Show("Successfully deleted!", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch { MessageBox.Show("The record was not deleted!", "Error", MessageBoxButtons.RetryCancel, MessageBoxIcon.Error); }
ExecuteSearch();
}
}
}
private void btnReturn_Click(object sender, EventArgs e)
{
if (cbAssignedTo.Text.Length < 1)
{
MessageBox.Show("You must fill all values!", "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
if (cbAssignementForm.Checked)
{
CreateAssignment = true;
}
else
{
CreateAssignment = false;
}
try
{
String SQL = "INSERT INTO History (Description,SerialNumber,DMMPropertyNum,Quantity,AssignedTo,AssignedToDep,Status,ProductGroup,IPAddress,UserName,Vreme,Serviser,AssignementId,RelatedBDA,Notes) SELECT Description,SerialNumber,DMMPropertyNum,Quantity,AssignedTo,AssignedToDep,Status,ProductGroup,IPAddress,UserName,Vreme,Serviser,AssignementId,RelatedBDA,Notes FROM Items WHERE ItemId IN (" + ids + ")";
Vasko.ExecuteCommand(SQL);
String PreviousAssigned = Vasko.ExecuteScalar("SELECT TOP 1 AssignedTo FROM Items WHERE ItemId IN (" + ids + ")");
SQL = @"UPDATE Items SET Status='On Stock', AssignedTo='" + cbAssignedTo.Text + "', AssignedToDep='IT', Vreme= Now(), UserName='******', ReturnPersonTmp='" + PreviousAssigned +
@"' WHERE ItemId IN (" + ids + ")";
Vasko.ExecuteCommand(SQL);
}
catch { }
this.Close();
}
}
private void btnSendOnService_Click(object sender, EventArgs e)
{
if (cbSerialNumber.Text != "")
{
if (Convert.ToInt32(Vasko.ExecuteScalar("SELECT COUNT(*) FROM Items WHERE SerialNumber ='" + cbSerialNumber.Text + "'")) > 0)
{
String ItemID = Vasko.ExecuteScalar("SELECT ItemID FROM Items WHERE SerialNumber = '" + cbSerialNumber.Text + "'");
String SQL = "INSERT INTO History (Description,SerialNumber,DMMPropertyNum,Quantity,AssignedTo,AssignedToDep,Status,ProductGroup,IPAddress,UserName,Vreme,Serviser,AssignementId,RelatedBDA,Notes) SELECT Description,SerialNumber,DMMPropertyNum,Quantity,AssignedTo,AssignedToDep,Status,ProductGroup,IPAddress,UserName,Vreme,Serviser,AssignementId,RelatedBDA,Notes FROM Items WHERE ItemId=" + ItemID;
Vasko.ExecuteCommand(SQL);
SQL = @"UPDATE Items SET Status='On Service', Vreme= Now(), UserName='******',Serviser='" + cbServicer.Text + "' WHERE ItemId=" + ItemID;
Vasko.ExecuteCommand(SQL);
SQL = "INSERT INTO Service (ItemId, SentOnDate, Problem, SentBy, Servicer, ServicerContact, ProcessedBy, ProcessedOnDate, RelatedBDAService, CostCenter) VALUES (" +
ItemID + @", '" + calSentOn.Text + "', '" + tbProblem.Text.Replace("'", "''") + "', '" + tbSentBy.Text.Replace("'", "''") + "', '" + cbServicer.Text + "', '" + tbServicerContact.Text.Replace("'", "''") + "', '" + MyGlobals.UserName + "', Now(), '" + tbRelatedBDA.Text + "','" + cbCostCenter.Text + "')";
Vasko.ExecuteCommand(SQL);
NewId = Vasko.ExecuteScalar("SELECT MAX(ServiceId) as ServiceId FROM Service");
PrintReverse = cbReverseForm.Checked;
PrintAddress = cbAddress.Checked;
this.Close();
}
else
{
MessageBox.Show("The inserted Serial Number is not correct!", "Error!", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
else
{
pbRequiredSerial.Visible = true;
}
}
private void cbDMMNumber_SelectedIndexChanged(object sender, EventArgs e)
{
if (cbDMMNumber.Text != "")
{
cbSerialNumber.Text = Vasko.ExecuteScalar("SELECT TOP 1 SerialNumber FROM Items WHERE DMMPropertyNum = '" + cbDMMNumber.Text.Replace("'", "''") + "'");
}
}
private void cbSerialNumber_SelectedIndexChanged(object sender, EventArgs e)
{
if (cbSerialNumber.Text != "")
{
cbDMMNumber.Text = Vasko.ExecuteScalar("SELECT TOP 1 DMMPropertyNum FROM Items WHERE SerialNumber = '" + cbSerialNumber.Text.Replace("'", "''") + "'");
pbRequiredSerial.Visible = false;
}
}
private void btnDodadi_Click(object sender, EventArgs e)
{
try
{
if (tbQuantity.TextBox.Text.Length < 1)
{
tbQuantity.TextBox.Text = "1";
}
String SelectCount = "SELECT COUNT(*) FROM Items WHERE SerialNumber = '" + tbSerialNumber.TextBox.Text + "'";
if (tbIPAddress.TextBox.Text.Length > 0)
{
SelectCount += " OR IPAddress='" + tbIPAddress.TextBox.Text + "'";
}
int Broj = Convert.ToInt32(Vasko.ExecuteScalar(SelectCount));
String dodatok = "";
if (tbIPAddress.TextBox.Text.Length > 0)
{
dodatok = " or with IP Address: " + tbIPAddress.TextBox.Text;
}
String MessageInfo = "Already exists item with serial number: " + tbSerialNumber.TextBox.Text + dodatok + "! Can not be inserted another one!";
if (Broj > 0)
{
MessageBox.Show(MessageInfo, "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
String SQL = @"INSERT INTO Items (Description, SerialNumber, DMMPropertyNum, Quantity, AssignedToDep, AssignedTo, Status,
ProductGroup, IPAddress, Vreme, UserName, Serviser, RelatedBDA, Notes) VALUES ('" + cbDescription.Text + "','" + tbSerialNumber.TextBox.Text +
@"','" + tbDMMPropertyNum.TextBox.Text + "','" + tbQuantity.TextBox.Text + "','" + cbAssignedToDep.Text + "','" + tbAssignedTo.TextBox.Text +
@"','" + cbStatus.Text + "','" + cbProductGroup.Text + "','" + tbIPAddress.TextBox.Text + "', Now(),'" + MyGlobals.UserName + "','" + tbServiser.Text + "','" + tbRelatedBDA.TextBox.Text + "','" + tbNotes.Text + "')";
Vasko.ExecuteCommand(SQL);
InsertComboStavka(cbProductGroup, 1);
InsertComboStavka(cbStatus, 2);
InsertComboStavka(cbAssignedToDep, 3);
if (!cbBarcodeScann.Checked)
{
MessageBox.Show("Successfully added!", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
lblInfo.Text = "Item inserted with barcode scanner!";
tbSerialNumber.TextBox.SelectAll();
tbSerialNumber.TextBox.Focus();
timer.Start();
}
}
}
catch { MessageBox.Show("The record was not added!", "Error", MessageBoxButtons.RetryCancel, MessageBoxIcon.Error); }
}
private void cbProductGroup_SelectedIndexChanged(object sender, EventArgs e)
{
if (FirstLoad)
{
if (cbProductGroup.Text != "System.Data.DataRowView")
{
Vasko.FillCombo("SELECT DISTINCT(Description) FROM Items WHERE ProductGroup = '" + cbProductGroup.Text.Trim() + "'", cbDescription, "Description");
}
}
}
private void Preview_Load(object sender, EventArgs e)
{
Vasko.FillCombo("SELECT Description FROM ComboDef WHERE Grupa = 1 ORDER BY Description", cbProductGroup, "Description");
Vasko.FillCombo("SELECT Description FROM ComboDef WHERE Grupa = 2 ORDER BY Description", cbStatus, "Description");
Vasko.FillCombo("SELECT Description FROM ComboDef WHERE Grupa = 3 ORDER BY Description", cbAssignedToDep, "Description");
tbSelectedProduct.Text = "";
tbSelectedStatus.Text = "";
calenderDatum.Text = "";
}
private void IzbrishiCombo(String vDescription)
{
String SelectCount = "SELECT COUNT(ProductGroup) FROM Items WHERE ProductGroup = " + vDescription + "";
int Broj = Convert.ToInt32(Vasko.ExecuteScalar(SelectCount));
if (Broj <= 0)
{
String SQL = @"DELETE FROM ComboDef WHERE Description = " + vDescription;
Vasko.ExecuteCommand(SQL);
}
}
private void btnReturnFromService_Click(object sender, EventArgs e)
{
String SQL = "INSERT INTO History (Description,SerialNumber,DMMPropertyNum,Quantity,AssignedTo,AssignedToDep,Status,ProductGroup,IPAddress,UserName,Vreme,Serviser,AssignementId,RelatedBDA,Notes) SELECT Description,SerialNumber,DMMPropertyNum,Quantity,AssignedTo,AssignedToDep,Status,ProductGroup,IPAddress,UserName,Vreme,Serviser,AssignementId,RelatedBDA,Notes FROM Items WHERE ItemId=" + itemId;
Vasko.ExecuteCommand(SQL);
SQL = @"UPDATE Items SET Status='On Stock', Vreme= Now(), UserName='******' WHERE ItemId=" + itemId;
Vasko.ExecuteCommand(SQL);
SQL = "UPDATE Service SET ReturnedOnDate = '" + calReturnedOn.Text + "', ReturnedBy='" + tbReturnedBy.Text + "', ReturnInformation ='" + tbReturnInfo.Text + "', ProcessedBy= '" + MyGlobals.UserName + "', ProcessedOnDate=Now() WHERE ServiceId = " + serviceId;
Vasko.ExecuteCommand(SQL);
this.Close();
}
private void InsertComboStavka(ComboBox ComboVlez, Int32 vGrupa)
{
if (ComboVlez.Text.Length > 0)
{
String SelectCount = "SELECT COUNT(*) FROM ComboDef WHERE Grupa = " + vGrupa.ToString() + " AND Description = '" + ComboVlez.Text + "'";
int Broj = Convert.ToInt32(Vasko.ExecuteScalar(SelectCount));
if (Broj <= 0)
{
String SQL = @"INSERT INTO ComboDef (Description, Grupa) VALUES ('" + ComboVlez.Text.Replace("'", "''") + "', " + vGrupa.ToString() + ")";
Vasko.ExecuteCommand(SQL);
}
}
}
private void PrintAddress(String OpId)
{
Funkcii f = new Funkcii();
DatabaseConn dbc = new DatabaseConn();
String Adresa = Vasko.ExecuteScalar("SELECT ServicerContact FROM Service WHERE ServiceID=" + OpId);
Adresa = Adresa.Substring(Adresa.IndexOf("ul"));
Adresa = Adresa.Substring(0, Adresa.IndexOf("www"));
String Datum = DateTime.Now.ToShortDateString();
f.PrintWord(dbc.TemplatePath() + @"\IT_Servicer_Address.dot",
"SELECT i.ProductGroup, i.Description, i.SerialNumber, s.Servicer, '" + Adresa + "' as Address, s.Problem FROM Service as s LEFT OUTER JOIN Items as i ON i.ItemId=s.ItemId WHERE s.ServiceID=" + OpId, "");
}
private void ExecuteSearch()
{
PleaseWait frmPW = new PleaseWait();
frmPW.Show();
Application.DoEvents();
MyDs.Tables.Clear();
outlookGrid1.Rows.Clear();
DataTable dt = new DataTable();
String SQL = @"SELECT s.ServiceId, i.ItemId, i.Description, i.SerialNumber as [Serial Number], i.DMMPropertyNum as [DMM Number], s.SentOnDate as [Sent on Date], s.ReturnedOnDate as [Returned on Date], s.Problem as [Problem with the item], s.SentBy as [Sent on service by], s.ReturnedBy as [Returned from service by], s.ReturnInformation as [Information after servicing], s.Servicer, s.ServicerContact as [Servicer Contact information], s.CostCenter as [Cost Center], s.RelatedBDAService as [Related BDA] FROM Service s LEFT OUTER JOIN Items i ON s.ItemId = i.ItemId";
String WherePart = FillWherePart();
SQL += WherePart;
dt = Vasko.ReturnDataTable(SQL);
MyDs.Tables.Add(dt);
if (MyDs.Tables[0].Rows.Count > 0)
{
int ColumnIndex = 2;
outlookGrid1.BindData(MyDs, MyDs.Tables[0].TableName);
outlookGrid1.GroupTemplate.Column = outlookGrid1.Columns[ColumnIndex];
ListSortDirection lsd = ListSortDirection.Ascending;
outlookGrid1.Sort(new DataRowComparer(ColumnIndex, lsd));
outlookGrid1.ColumnHeadersHeight = 40;
outlookGrid1.Columns[0].Visible = false;
outlookGrid1.Columns[1].Visible = false;
outlookGrid1.Columns[3].Width = 100;
outlookGrid1.Columns[4].Width = 70;
outlookGrid1.Columns[5].Width = 75;
outlookGrid1.Columns[6].Width = 75;
outlookGrid1.Columns[7].Width = 200;
outlookGrid1.Columns[8].Width = 85;
outlookGrid1.Columns[9].Width = 85;
outlookGrid1.Columns[10].Width = 200;
lblNoRows.Text = "Total rows: " + MyDs.Tables[0].Rows.Count;
if (outlookGrid1.Rows[0].Selected)
{
outlookGrid1.Rows[0].Selected = false;
}
}
frmPW.Close();
}
private void ExecuteSearch()
{
PleaseWait frmPW = new PleaseWait();
frmPW.Show();
Application.DoEvents();
MyDs.Tables.Clear();
outlookGrid1.Rows.Clear();
DataTable dt = new DataTable();
String SQL = @"SELECT ItemId, ProductGroup, Description, SerialNumber as [Serial Number], DMMPropertyNum as [DMM Property Number],
Status, AssignedToDep as [Assigned To Department], AssignedTo as [Assigned To], Quantity, IPAddress as [IP Address], Vreme as [Date of change], UserName as [Changed by], Serviser as [Supplier/Servicer], AssignementNumber as [Assignment Form], i.AssignementId, RelatedBDA as [Related BDA], Notes FROM Items i LEFT OUTER JOIN Assignement as a on a.AssignementId = i.AssignementID";
String WherePart = FillWherePart();
SQL += WherePart;
dt = Vasko.ReturnDataTable(SQL);
MyDs.Tables.Add(dt);
if (MyDs.Tables[0].Rows.Count > 0)
{
int ColumnIndex = 1;
outlookGrid1.BindData(MyDs, MyDs.Tables[0].TableName);
outlookGrid1.GroupTemplate.Column = outlookGrid1.Columns[ColumnIndex];
ListSortDirection lsd = ListSortDirection.Ascending;
outlookGrid1.Sort(new DataRowComparer(ColumnIndex, lsd));
outlookGrid1.ColumnHeadersHeight = 40;
outlookGrid1.Columns[0].Visible = false;
outlookGrid1.Columns[1].Visible = false;
outlookGrid1.Columns[2].Width = 220;
outlookGrid1.Columns[5].Width = 70;
outlookGrid1.Columns[8].Width = 30;
outlookGrid1.Columns[9].Width = 70;
outlookGrid1.Columns[10].Width = 70;
outlookGrid1.Columns[11].Width = 70;
outlookGrid1.Columns["AssignementId"].Visible = false;
lblNoRows.Text = "Total rows: " + MyDs.Tables[0].Rows.Count;
if (outlookGrid1.Rows[0].Selected)
{
outlookGrid1.Rows[0].Selected = false;
}
}
frmPW.Close();
}
private void ExportSelected()
{
DataSet SelectedDataSet = new DataSet();
DataTable dt = new DataTable();
String SQL = @"SELECT ProductGroup, Description, SerialNumber as [Serial Number], DMMPropertyNum as [DMM Property Number],
Status, AssignedToDep as [Assigned To Department], AssignedTo as [Assigned To], Quantity, IPAddress as [IP Address], '' as [Date of change], UserName as [Changed by], Serviser as [Supplier/Servicer], AssignementNumber as [Assignement Form], RelatedBDA as [Related BDA], Notes FROM Items i LEFT OUTER JOIN Assignement as a on a.AssignementId = i.AssignementID WHERE 1=2";
dt = Vasko.ReturnDataTable(SQL);
SelectedDataSet.Tables.Add(dt);
foreach (DataGridViewRow dr in outlookGrid1.Rows)
{
if (dr.Selected)
{
DataRow drNew = SelectedDataSet.Tables[0].NewRow();
drNew[0] = dr.Cells[1].Value;
drNew[1] = dr.Cells[2].Value;
drNew[2] = dr.Cells[3].Value;
drNew[3] = dr.Cells[4].Value;
drNew[4] = dr.Cells[5].Value;
drNew[5] = dr.Cells[6].Value;
drNew[6] = dr.Cells[7].Value;
drNew[7] = dr.Cells[8].Value;
drNew[8] = dr.Cells[9].Value;
drNew[9] = dr.Cells[10].Value.ToString();
drNew[10] = dr.Cells[11].Value;
drNew[11] = dr.Cells[12].Value;
drNew[12] = dr.Cells[13].Value;
drNew[13] = dr.Cells[15].Value;
drNew[14] = dr.Cells[16].Value;
//drNew[15] = dr.Cells[15].Value;
SelectedDataSet.Tables[0].Rows.Add(drNew);
}
}
if (SelectedDataSet.Tables[0].Rows.Count > 0)
{
PleaseWait frmPW = new PleaseWait();
frmPW.Show();
Application.DoEvents();
FastExportingMethod.ExportToExcel(SelectedDataSet);//, sfd.FileName);
frmPW.Close();
}
}
private void tbAssign_Click(object sender, EventArgs e)
{
if (cbAssignedTo.Text.Length < 1 || cbAssignToDep.Text.Length < 1)
{
MessageBox.Show("You must fill all values!", "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
if (Convert.ToInt32(tbQuantity.Text) < 1 || Convert.ToInt32(tbQuantity.Text) > Convert.ToInt32(quantity)) // dgvr.Cells["Quantity"].Value))
{
MessageBox.Show("There are not enough quantity to assign this item!", "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
if (cbAssignementForm.Checked)
{
CreateAssignment = true;
}
else
{
CreateAssignment = false;
}
try
{
String SQL = "INSERT INTO History (Description,SerialNumber,DMMPropertyNum,Quantity,AssignedTo,AssignedToDep,Status,ProductGroup,IPAddress,UserName,Vreme,Serviser,AssignementId,RelatedBDA,Notes) SELECT Description,SerialNumber,DMMPropertyNum,Quantity,AssignedTo,AssignedToDep,Status,ProductGroup,IPAddress,UserName,Vreme,Serviser,AssignementId,RelatedBDA,Notes FROM Items WHERE ItemId IN (" + ids + ")"; //dgvr.Cells["ItemId"].Value.ToString() ;
Vasko.ExecuteCommand(SQL);
if (Convert.ToInt32(tbQuantity.Text) >= 1 && Convert.ToInt32(quantity) > 1)
{
int Difference = Convert.ToInt32(quantity) - Convert.ToInt32(tbQuantity.Text);
SQL = @"UPDATE Items SET Quantity= " + Difference.ToString() + ", Notes = Notes & '- " + tbQuantity.Text + " assigned to: " + cbAssignedTo.Text + " on ' & FORMAT(Now(),'dd.MM.yyyy') & ' by:" + MyGlobals.UserName + "\r\n',Vreme= Now(), UserName='******' WHERE ItemId=" + dgvr.Cells["ItemId"].Value.ToString();
}
else
{
SQL = @"UPDATE Items SET Status='Assigned', AssignedTo='" + cbAssignedTo.Text + "', AssignedToDep='" + cbAssignToDep.Text + "', Vreme= Now(), UserName='******' WHERE ItemId IN (" + ids + ")"; //dgvr.Cells["ItemId"].Value.ToString();
}
Vasko.ExecuteCommand(SQL);
}
catch { }
this.Close();
}
}
private void AddItem_Load(object sender, EventArgs e)
{
timer.Tick += new EventHandler(timer_Tick);
timer.Interval = (1000) * (2);
timer.Enabled = true;
Vasko.FillCombo("SELECT Description FROM ComboDef WHERE Grupa = 1 ORDER BY Description", cbProductGroup, "Description");
Vasko.FillCombo("SELECT Description FROM ComboDef WHERE Grupa = 2 ORDER BY Description", cbStatus, "Description");
Vasko.FillCombo("SELECT Description FROM ComboDef WHERE Grupa = 3 ORDER BY Description", cbAssignedToDep, "Description");
FirstLoad = true;
cbProductGroup.SelectedIndex = -1;
cbDescription.SelectedIndex = -1;
cbStatus.SelectedIndex = -1;
SetirajZaEditForma();
SetirajZaEditMultiForma();
}
private void btnDelete_Click(object sender, EventArgs e)
{
if (outlookGrid1.SelectedRows.Count > 0)
{
String TekstZaBrisenje = "";
if (MessageBox.Show("Are you sure?", "Delete", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) == DialogResult.Yes)
{
String Selected = "";
foreach (DataGridViewRow dr in outlookGrid1.Rows)
{
if (dr.Selected)
{
if (Selected.Length > 0)
{
Selected += "," + dr.Cells["ItemId"].Value;
}
else
{
Selected = dr.Cells["ItemId"].Value.ToString(); TekstZaBrisenje = "'" + dr.Cells["ProductGroup"].Value + "'";
}
}
}
try
{
String SQL = @"DELETE FROM Items WHERE ItemId IN (" + Selected + ")";
Vasko.ExecuteCommand(SQL);
if (TekstZaBrisenje != "")
{
IzbrishiCombo(TekstZaBrisenje);
}
MessageBox.Show("Successfully deleted!", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch { MessageBox.Show("The record was not deleted!", "Error", MessageBoxButtons.RetryCancel, MessageBoxIcon.Error); }
ExecuteSearch();
}
}
}
private void ExecuteSearch()
{
PleaseWait frmPW = new PleaseWait();
frmPW.Show();
Application.DoEvents();
MyDs.Tables.Clear();
outlookGrid1.Rows.Clear();
DataTable dt = new DataTable();
String SQL = @"SELECT a.AssignementId, a.Type, AssignementNumber as [Assignment Number], AssignementTo as [Assigned To], AssignedOnDate as [Assigned on Date], AssignedBy as [Assigned By] FROM (Assignement a LEFT OUTER JOIN Assignement_Item ai on ai.AssignementId = a.AssignementId) LEFT OUTER JOIN Items i on i.ItemId = ai.ItemId";
String WherePart = FillWherePart();
SQL += WherePart;
dt = Vasko.ReturnDataTable(SQL);
MyDs.Tables.Add(dt);
if (MyDs.Tables[0].Rows.Count > 0)
{
int ColumnIndex = 3;
outlookGrid1.BindData(MyDs, MyDs.Tables[0].TableName);
outlookGrid1.GroupTemplate.Column = outlookGrid1.Columns[ColumnIndex];
ListSortDirection lsd = ListSortDirection.Ascending;
outlookGrid1.Sort(new DataRowComparer(ColumnIndex, lsd));
outlookGrid1.ColumnHeadersHeight = 40;
outlookGrid1.Columns[0].Visible = false;
outlookGrid1.Columns[1].Visible = false;
lblNoRows.Text = "Total rows: " + MyDs.Tables[0].Rows.Count;
if (outlookGrid1.Rows[0].Selected)
{
outlookGrid1.Rows[0].Selected = false;
}
}
frmPW.Close();
}
private void ExecuteSearch()
{
PleaseWait frmPW = new PleaseWait();
frmPW.Show();
Application.DoEvents();
MyDs.Tables.Clear();
gvHistory.Rows.Clear();
DataTable dt = new DataTable();
String SQL = @"SELECT ItemId, ProductGroup, Description, SerialNumber as [Serial Number], DMMPropertyNum as [DMM Property Number],
Status, AssignedToDep as [Assigned To Department], AssignedTo as [Assigned To], Quantity, IPAddress as [IP Address], Vreme as [Date of change], UserName as [Changed by], Serviser as [Supplier/Servicer], AssignementNumber as [Assignment Form], i.AssignementId, RelatedBDA as [Related BDA], Notes FROM History i LEFT OUTER JOIN Assignement as a on a.AssignementId = i.AssignementID WHERE SerialNumber = '" + Id + "' ORDER BY Vreme DESC";
dt = Vasko.ReturnDataTable(SQL);
bindingSource1.DataSource = dt;
gvHistory.DataSource = bindingSource1;
gvHistory.Columns[0].Visible = false;
gvHistory.Columns[14].Visible = false;
frmPW.Close();
}
private void AssignForm_Load(object sender, EventArgs e)
{
Vasko.FillCombo("SELECT Description FROM ComboDef WHERE Grupa = 3 ORDER BY Description", cbAssignToDep, "Description");
Vasko.FillCombo("SELECT DISTINCT(AssignedTo) as AssignedTo FROM Items", cbAssignedTo, "AssignedTo");
if (more)
{
tbQuantity.Text = "1";
}
else
{
tbQuantity.Text = quantity;// dgvr.Cells["Quantity"].Value.ToString();
}
if (tbQuantity.Text.Length < 1)
{
tbQuantity.Text = "1";
quantity = "1";
}
if (Convert.ToInt32(tbQuantity.Text) < 2)
{
tbQuantity.ReadOnly = true;
cbAssignementForm.Visible = true;
}
}
private void tbReprint_Click(object sender, EventArgs e)
{
if (tbAssignementNumber.Text.Length > 0)
{
PleaseWait frmPW = new PleaseWait();
frmPW.Show();
Application.DoEvents();
String Selected = "";
String SelectedAssignements = "";
String Assigned = "";
String OdbranTip = "";
String AssignementId = Vasko.ExecuteScalar("SELECT AssignementId FROM Assignement WHERE AssignementNumber = '" + tbAssignementNumber.Text + "'");
if (AssignementId != "")
{
String Tip = Vasko.ExecuteScalar("SELECT [Type] FROM Assignement WHERE AssignementID=" + AssignementId);
if (Tip == "1")
{
OdbranTip = "IT_Assignement_Form.dot";
}
else if (Tip == "2")
{
OdbranTip = "IT_Temporary_Form.dot";
}
else if (Tip == "3")
{
OdbranTip = "IT_Returning_Form.dot";
}
DataTable dt = new DataTable();
dt = Vasko.ReturnDataTable("SELECT ItemId FROM Assignement_Item WHERE AssignementId = " + AssignementId);
foreach (DataRow drow in dt.Rows)
{
if (Selected != "")
{
Selected += ",";
}
Selected += drow[0].ToString();
}
if (SelectedAssignements != "")
{
SelectedAssignements += ",";
}
{
if (!SelectedAssignements.Contains(AssignementId))
{
SelectedAssignements += AssignementId;
}
}
Funkcii f = new Funkcii();
DatabaseConn dbc = new DatabaseConn();
string[] AllAssignements = SelectedAssignements.Split(',');
foreach (string str in AllAssignements)
{
String SqlPrint = @"SELECT AssignementTo as AssignedTo, AssignementNumber, AssignedBy, Format(AssignedOnDate, 'dd.MM.yyyy') as AssignedOn FROM Assignement WHERE AssignementId=" + str + "; ";
String SqlPrint2 = @"SELECT ProductGroup, Description, DMMPropertyNum, SerialNumber, Quantity FROM Items WHERE ItemId IN (" + Selected + ") ";
f.PrintWord(dbc.TemplatePath() + @"\" + OdbranTip, SqlPrint, SqlPrint2);
}
}
else
{
MessageBox.Show("There is no assignment form as the entered number!", "Not exists!", MessageBoxButtons.OK);
}
frmPW.Close();
}
}
private void AssignementForm_Preview_Load(object sender, EventArgs e)
{
Vasko.FillCombo("SELECT Description FROM ComboDef WHERE Grupa = 3 ORDER BY Description", cbAssignedToDep, "Description");
ExecuteSearch();
}
private void CreateForm(String Type, Boolean Programaticaly = false)
{
if (outlookGrid1.SelectedRows.Count > 0)
{
PleaseWait frmPW = new PleaseWait();
frmPW.Show();
Application.DoEvents();
String Selected = "";
String Assigned = "";
Boolean First = true;
String SelectedIndexes = "";
foreach (DataGridViewRow dr in outlookGrid1.Rows)
{
if (dr.Selected && dr.Index > 0)
{
if (SelectedIndexes != "")
{
SelectedIndexes += ",";
}
SelectedIndexes += dr.Index.ToString();
if (First)
{
Assigned = Vasko.ExecuteScalar("SELECT AssignedTo FROM Items WHERE ItemID=" + dr.Cells["ItemID"].Value.ToString());// dr.Cells["Assigned To"].Value.ToString();
First = false;
}
if (Assigned != Vasko.ExecuteScalar("SELECT AssignedTo FROM Items WHERE ItemID=" + dr.Cells["ItemID"].Value.ToString()))//dr.Cells["Assigned To"].Value.ToString())
{
goto stop;
}
else
{
if (Selected.Length > 0)
{
Selected += "," + dr.Cells["ItemId"].Value;
}
else
{
Selected = dr.Cells["ItemId"].Value.ToString();
}
}
}
}
string[] selektirani_ids = Selected.Split(',');
string lastInserted = Vasko.ExecuteScalar("SELECT TOP 1 AssignementNumber FROM Assignement ORDER BY AssignedOnDate DESC");
string lastInsertedDate = Vasko.ExecuteScalar("SELECT TOP 1 AssignedOnDate FROM Assignement ORDER BY AssignedOnDate DESC");
string NewAssignementNumber = "";
if (lastInserted != "")
{
if (Convert.ToDateTime(lastInsertedDate).ToShortDateString() == DateTime.Now.ToShortDateString())
{
//string lastDigit = lastInserted.Substring(lastInserted.Length - 2, lastInserted.Length - (lastInserted.Length - 2));
string lastDigit = lastInserted.Substring(11, lastInserted.Length - 11);
int newDigit = Convert.ToInt32(lastDigit) + 1;
if (newDigit < 10)
{
NewAssignementNumber = Type + "-" + lastInserted.Substring(2, 8) + "-0" + newDigit.ToString();
}
else
{
NewAssignementNumber = Type + "-" + lastInserted.Substring(2, 8) + "-" + newDigit.ToString();
}
}
else
{
NewAssignementNumber = Type + "-" + DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString().PadLeft(2, '0') + DateTime.Now.Day.ToString().PadLeft(2, '0') + "-01";
}
}
else
{
NewAssignementNumber = Type + "-" + DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString().PadLeft(2, '0') + DateTime.Now.Day.ToString().PadLeft(2, '0') + "-01";
}
if (Type == "3")
{
String PreviousAssigned = Vasko.ExecuteScalar("SELECT ReturnPersonTmp FROM Items WHERE ItemID = " + selektirani_ids[0]);
Vasko.ExecuteCommand("INSERT INTO Assignement (AssignementNumber, AssignementTo, AssignedOnDate, AssignedBy, Type, AssignementReturnPerson) VALUES ('" + NewAssignementNumber + "','" + Assigned + "',NOW(),'" + MyGlobals.UserName + "'," + Type + ", '" + PreviousAssigned + "');");
}
else
{
Vasko.ExecuteCommand("INSERT INTO Assignement (AssignementNumber, AssignementTo, AssignedOnDate, AssignedBy, Type) VALUES ('" + NewAssignementNumber + "','" + Assigned + "',NOW(),'" + MyGlobals.UserName + "'," + Type + ");");
}
string newID = Vasko.ExecuteScalar("SELECT MAX(AssignementId) FROM Assignement");
foreach (string i in selektirani_ids)
{
String SQL = "INSERT INTO History (Description,SerialNumber,DMMPropertyNum,Quantity,AssignedTo,AssignedToDep,Status,ProductGroup,IPAddress,UserName,Vreme,Serviser,AssignementID) SELECT Description,SerialNumber,DMMPropertyNum,Quantity,AssignedTo,AssignedToDep,Status,ProductGroup,IPAddress,UserName,Vreme,Serviser, AssignementID FROM Items WHERE ItemId=" + i;
Vasko.ExecuteCommand(SQL);
Vasko.ExecuteCommand("INSERT INTO Assignement_Item (AssignementId, ItemID) VALUES(" + newID + "," + i + ")");
if (Type == "1" || Type == "2")
{
Vasko.ExecuteCommand("UPDATE Items SET AssignementID = " + newID + ", Status='Assigned', Vreme= Now(), UserName='******' WHERE ItemID = " + i);
}
else if (Type == "3")
{
String AssignetTo = "AssignedTo='IT Office',";
if (Programaticaly)
{
AssignetTo = "";
}
Vasko.ExecuteCommand("UPDATE Items SET AssignementID = " + newID + ", Status='On Stock', AssignedToDep='IT', " + AssignetTo + " Vreme= Now(), UserName='******' WHERE ItemID = " + i);
}
}
//ExecuteSearch();
string[] splitedIndexes = SelectedIndexes.Split(',');
foreach (string i in splitedIndexes)
{
foreach (DataGridViewRow dr in outlookGrid1.Rows)
{
if (dr.Index == Convert.ToInt32(i))
{
dr.Selected = true;
}
}
}
if (Type == "1")
{
button1_Click(null, null);
}
else if (Type == "2")
{
btnTemporaryForm_Click(null, null);
}
else if (Type == "3")
{
btnReturningForm_Click(null, null);
}
//String SqlPrint = @"SELECT TOP 1 AssignedTo FROM Items WHERE ItemId IN (" + Selected + "); ";
//String SqlPrint2 = @"SELECT ProductGroup, Description, DMMPropertyNum, SerialNumber, '' as OrgNum FROM Items WHERE ItemId IN (" + Selected + ") ";
//f.PrintWord(dbc.TemplatePath() + @"\IT_Assignement_Form.dot", SqlPrint, SqlPrint2);
goto end;
stop : MessageBox.Show("The items are not assigned to the same user and can not be grouped!", "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
end :;
frmPW.Close();
}
}
private void PrintForm(String TemplateForm, String Type)
{
if (outlookGrid1.SelectedRows.Count > 0)
{
PleaseWait frmPW = new PleaseWait();
frmPW.Show();
Application.DoEvents();
String Selected = "";
String SelectedAssignements = "";
String Assigned = "";
String OdbranTip = "";
foreach (DataGridViewRow dr in outlookGrid1.Rows)
{
if (dr.Selected && dr.Index > 0)
{
//if (dr.Cells["Assignement Form"].Value.ToString() != "")
String AssId = Vasko.ExecuteScalar("SELECT AssignementId FROM Items WHERE ItemID=" + dr.Cells["ItemID"].Value.ToString());
if (AssId != "")
{
String Tip = Vasko.ExecuteScalar("SELECT [Type] FROM Assignement WHERE AssignementID=" + AssId);//dr.Cells["AssignementId"].Value.ToString());
if (Tip == "1")
{
OdbranTip = "Assignement Form";
}
else if (Tip == "2")
{
OdbranTip = "Temporary Form";
}
else if (Tip == "3")
{
OdbranTip = "Return Form";
}
if (Tip != Type)
{
goto error2;
}
else
{
DataTable dt = new DataTable();
dt = Vasko.ReturnDataTable("SELECT ItemId FROM Assignement_Item WHERE AssignementId = " + AssId); //dr.Cells["AssignementId"].Value.ToString());
foreach (DataRow drow in dt.Rows)
{
if (Selected != "")
{
Selected += ",";
}
Selected += drow[0].ToString();
}
}
if (SelectedAssignements != "")
{
SelectedAssignements += ",";
}
{
if (!SelectedAssignements.Contains(AssId)) //dr.Cells["AssignementId"].Value.ToString()))
{
SelectedAssignements += AssId; // dr.Cells["AssignementId"].Value.ToString();
}
}
}
else
{
goto error;
}
goto stop;
}
}
stop:
Funkcii f = new Funkcii();
DatabaseConn dbc = new DatabaseConn();
string[] AllAssignements = SelectedAssignements.Split(',');
foreach (string str in AllAssignements)
{
String SqlPrint = "";
if (Type == "3")
{
SqlPrint = @"SELECT AssignementReturnPerson as AssignedTo, AssignementNumber, AssignedBy, Format(AssignedOnDate, 'dd.MM.yyyy') as AssignedOn FROM Assignement WHERE AssignementId=" + str + "; ";
}
else
{
SqlPrint = @"SELECT AssignementTo as AssignedTo, AssignementNumber, AssignedBy, Format(AssignedOnDate, 'dd.MM.yyyy') as AssignedOn FROM Assignement WHERE AssignementId=" + str + "; ";
}
String SqlPrint2 = @"SELECT ProductGroup, Description, DMMPropertyNum, SerialNumber, Quantity FROM Items WHERE ItemId IN (" + Selected + ") ";
f.PrintWord(dbc.TemplatePath() + @"\" + TemplateForm, SqlPrint, SqlPrint2);
}
goto end;
error : MessageBox.Show("One of selected items doesn't has a created form! Please create a form first, and after that try to print it!", "Error!", MessageBoxButtons.OK); goto end;
error2 : MessageBox.Show("One of selected items doesn't have created this type of form! You can print just: " + OdbranTip + "!", "Error!", MessageBoxButtons.OK); goto end;
end : ExecuteSearch();
frmPW.Close();
}
}
private void btnEdit_Click(object sender, EventArgs e)
{
try
{
if (tbQuantity.TextBox.Text == "")
{
tbQuantity.TextBox.Text = "1";
}
if (dgvr.Cells.Count > 0)
{
Boolean CanWrite = true;
if (dgvr.Cells["IP Address"].Value.ToString() != tbIPAddress.TextBox.Text)
{
String SelectCount = "SELECT COUNT(*) FROM Items WHERE IPAddress='" + tbIPAddress.TextBox.Text + "'";
int Broj = Convert.ToInt32(Vasko.ExecuteScalar(SelectCount));
String MessageInfo = "Already exists item with IP Address: " + tbIPAddress.TextBox.Text + "! Can not be inserted another one!";
if (Broj > 0)
{
MessageBox.Show(MessageInfo, "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
CanWrite = false;
goto error1;
}
else
{
CanWrite = true;
}
}
else
{
CanWrite = true;
}
if (dgvr.Cells["Serial Number"].Value.ToString() != tbSerialNumber.TextBox.Text)
{
String SelectCount = "SELECT COUNT(*) FROM Items WHERE SerialNumber='" + tbSerialNumber.TextBox.Text + "'";
int Broj = Convert.ToInt32(Vasko.ExecuteScalar(SelectCount));
String MessageInfo = "Already exists item with Serial Number: " + tbSerialNumber.TextBox.Text + "! Can not be inserted another one!";
if (Broj > 0)
{
MessageBox.Show(MessageInfo, "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
CanWrite = false;
goto error1;
}
else
{
CanWrite = true;
}
}
else
{
CanWrite = true;
}
if (CanWrite)
{
String SQL = "INSERT INTO History (Description,SerialNumber,DMMPropertyNum,Quantity,AssignedTo,AssignedToDep,Status,ProductGroup,IPAddress,UserName,Vreme,Serviser,AssignementId,RelatedBDA,Notes) SELECT Description,SerialNumber,DMMPropertyNum,Quantity,AssignedTo,AssignedToDep,Status,ProductGroup,IPAddress,UserName,Vreme,Serviser,AssignementId,RelatedBDA,Notes FROM Items WHERE ItemId=" + dgvr.Cells["ItemId"].Value.ToString();
Vasko.ExecuteCommand(SQL);
SQL = @"UPDATE Items SET Description='" + cbDescription.Text + "',SerialNumber='" + tbSerialNumber.TextBox.Text +
@"',DMMPropertyNum='" + tbDMMPropertyNum.TextBox.Text + "',Quantity='" + tbQuantity.TextBox.Text + "',AssignedTo='" + tbAssignedTo.TextBox.Text + "',AssignedToDep='" + cbAssignedToDep.Text +
@"',Status='" + cbStatus.Text + "',ProductGroup='" + cbProductGroup.Text + "',IPAddress='" + tbIPAddress.TextBox.Text + "',Vreme= Now(), UserName='******',Serviser='" + tbServiser.Text + "',RelatedBDA='" + tbRelatedBDA.TextBox.Text + "', Notes='" + tbNotes.Text +
@"' WHERE ItemId=" + dgvr.Cells["ItemId"].Value.ToString();
Vasko.ExecuteCommand(SQL);
}
}
else if (dgvrMulti.Cells.Count > 0)
{
String SQL = @"UPDATE Items SET ";
String SQL_ADDED = "";
if (cbProduct.Checked)
{
SQL_ADDED += " ProductGroup='" + cbProductGroup.Text + "'";
}
if (cbDescr.Checked)
{
if (SQL_ADDED != "")
{
SQL_ADDED += ",";
}
SQL_ADDED += " Description='" + cbDescription.Text + "'";
}
if (cbQuantity.Checked)
{
if (SQL_ADDED != "")
{
SQL_ADDED += ",";
}
SQL_ADDED += " Quantity='" + tbQuantity.TextBox.Text + "'";
}
if (cbStat.Checked)
{
if (SQL_ADDED != "")
{
SQL_ADDED += ",";
}
SQL_ADDED += " Status='" + cbStatus.Text + "'";
}
if (cbAssToDep.Checked)
{
if (SQL_ADDED != "")
{
SQL_ADDED += ",";
}
SQL_ADDED += " AssignedToDep='" + cbAssignedToDep.Text + "'";
}
if (cbAssigned.Checked)
{
if (SQL_ADDED != "")
{
SQL_ADDED += ",";
}
SQL_ADDED += " AssignedTo='" + tbAssignedTo.TextBox.Text + "'";
}
if (cbIPAddr.Checked)
{
if (SQL_ADDED != "")
{
SQL_ADDED += ",";
}
SQL_ADDED += " IPAddress='" + tbIPAddress.TextBox.Text + "'";
}
if (cbServicer.Checked)
{
if (SQL_ADDED != "")
{
SQL_ADDED += ",";
}
SQL_ADDED += " Serviser='" + tbServiser.Text + "'";
}
if (cbNotes.Checked)
{
if (SQL_ADDED != "")
{
SQL_ADDED += ",";
}
SQL_ADDED += " Notes='" + tbNotes.Text + "'";
}
if (cbRelatedBDA.Checked)
{
if (SQL_ADDED != "")
{
SQL_ADDED += ",";
}
SQL_ADDED += " RelatedBDA='" + tbRelatedBDA.TextBox.Text + "'";
}
if (SQL_ADDED != "")
{
SQL_ADDED += ", Vreme= Now(), UserName='******' WHERE ItemId IN (" + IdsForUpdate + ")";
}
if (SQL_ADDED != "")
{
String SQL_History = "INSERT INTO History (Description,SerialNumber,DMMPropertyNum,Quantity,AssignedTo,AssignedToDep,Status,ProductGroup,IPAddress,UserName,Vreme,Serviser,AssignementId,RelatedBDA,Notes) SELECT Description,SerialNumber,DMMPropertyNum,Quantity,AssignedTo,AssignedToDep,Status,ProductGroup,IPAddress,UserName,Vreme,Serviser,AssignementId,RelatedBDA,Notes FROM Items WHERE ItemId IN (" + IdsForUpdate + ") ; ";
Vasko.ExecuteCommand(SQL_History);
Vasko.ExecuteCommand(SQL + SQL_ADDED);
}
}
InsertComboStavka(cbProductGroup, 1);
InsertComboStavka(cbStatus, 2);
InsertComboStavka(cbAssignedToDep, 3);
MessageBox.Show("Successfully added!", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.Close();
}
catch { MessageBox.Show("The record was not added!", "Error", MessageBoxButtons.RetryCancel, MessageBoxIcon.Error); }
error1 :;
}