public OleDbCommand CreatedCommand(String str, OleDbConnection MyC) { DatabaseConn dc = new DatabaseConn(); String strConnection = dc.AccessConnString(); OleDbCommand cmd = new OleDbCommand(str, MyC); return(cmd); }
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, ""); }
public bool CheckExistingSQL(String SQL) { DatabaseConn dc = new DatabaseConn(); String strConnection = dc.AccessConnString(); OleDbConnection MyConnection = new OleDbConnection(strConnection); String SelectCount = SQL; OleDbCommand cmd = new OleDbCommand(SelectCount, MyConnection); MyConnection.Open(); int Broj = Convert.ToInt32(cmd.ExecuteScalar()); MyConnection.Close(); if (Broj > 0) { return(true); } else { return(false); } }
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 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(); } }
// Za printanje na Word so prv SQL da polni Bookmarks, a vtoriot da polni tabela public void PrintWord(String Dokument, String Sql, String Sql2 = "") { object missing = System.Reflection.Missing.Value; object oTemplate = Dokument; Microsoft.Office.Interop.Word.ApplicationClass oWordApp = new Microsoft.Office.Interop.Word.ApplicationClass(); Microsoft.Office.Interop.Word.Document oWordDoc = oWordApp.Documents.Add(ref oTemplate, ref missing, ref missing, ref missing); DatabaseConn dc = new DatabaseConn(); String strConnection = dc.AccessConnString(); OleDbConnection MyConn = new OleDbConnection(strConnection); System.Data.DataTable dt = new System.Data.DataTable(); System.Data.DataTable dt2 = new System.Data.DataTable(); DataSet ds = new DataSet(); OleDbDataAdapter da = new OleDbDataAdapter(Sql, MyConn); MyConn.Open(); da.Fill(dt); MyConn.Close(); ds.Tables.Add(dt); if (Sql2 != "") { OleDbDataAdapter da2 = new OleDbDataAdapter(Sql2, MyConn); MyConn.Open(); da2.Fill(dt2); MyConn.Close(); ds.Tables.Add(dt2); } foreach (DataRow dr in ds.Tables[0].Rows) { foreach (DataColumn dcol in ds.Tables[0].Columns) { try { oWordDoc.Bookmarks[dcol.ColumnName].Range.Text = dr[dcol.ColumnName].ToString(); } catch {} } } Int32 tTableForPrint = 0; if (ds.Tables.Count > 1) { try { for (int brTabeli = 1; brTabeli <= ds.Tables.Count - 1; brTabeli++) { int BrRedovi = 1; Boolean tPostoi = false; Boolean tKreiranaTabela = false; int rows = ds.Tables[brTabeli].Rows.Count; int columns = ds.Tables[brTabeli].Columns.Count; if (brTabeli <= oWordDoc.Tables.Count) { tTableForPrint += 1; for (int tBrPostoecki = 1; tBrPostoecki <= oWordDoc.Tables.Count; tBrPostoecki++) { if (oWordDoc.Bookmarks["Table" + brTabeli].Range.Start >= oWordDoc.Tables[tBrPostoecki].Range.Start) { if ((oWordDoc.Bookmarks["Table" + brTabeli].Range.Start >= oWordDoc.Tables[tBrPostoecki].Range.Start && oWordDoc.Bookmarks["Table" + brTabeli].Range.Start <= oWordDoc.Tables[tBrPostoecki].Range.End) || (oWordDoc.Bookmarks["Table" + brTabeli].Range.End >= oWordDoc.Tables[tBrPostoecki].Range.Start && oWordDoc.Bookmarks["Table" + brTabeli].Range.End >= oWordDoc.Tables[tBrPostoecki].Range.End)) //ako ima iskreirano tabeli vo template-ot: { BrRedovi = 1; tPostoi = true; tTableForPrint = tBrPostoecki; for (int tPostoeckiRows = 1; tPostoeckiRows <= oWordDoc.Tables[tBrPostoecki].Rows.Count; tPostoeckiRows++) { for (int tPostoeckiColumns = 1; tPostoeckiColumns <= oWordDoc.Tables[tBrPostoecki].Columns.Count; tPostoeckiColumns++) { if (oWordDoc.Tables[tBrPostoecki].Cell(tPostoeckiRows, tPostoeckiColumns).Range.Text.Length > 2) { BrRedovi += 1; } } cont :; } if (tBrPostoecki > 1) { for (int tBr = 0; tBr <= rows - 2; tBr++) { oWordDoc.Tables[tBrPostoecki].Rows.Add(); } goto cont1; } } else { tPostoi = false; tTableForPrint = tBrPostoecki; } } cont1 :; } } else { tPostoi = false; } if (tPostoi == false) { Range r = oWordDoc.Bookmarks["Table" + brTabeli].Range; oWordDoc.Tables.Add(r, rows + 1, columns); BrRedovi = 2; tTableForPrint += 1; int j = 1; foreach (DataColumn dcc in ds.Tables[brTabeli].Columns) { oWordDoc.Tables[tTableForPrint].Cell(1, j).Range.InsertAfter(dcc.ColumnName); j++; } } int i = 2; // BrRedovi; Boolean Insertiran = false; foreach (DataRow drr in ds.Tables[brTabeli].Rows) { Insertiran = false; for (int m = 2; m <= ds.Tables[brTabeli].Rows.Count; m++) { string pomTekst = oWordDoc.Tables[tTableForPrint].Cell(m, 1).Range.Text; string pomTekst1 = oWordDoc.Tables[tTableForPrint].Cell(m, 2).Range.Text; string pomTekst2 = oWordDoc.Tables[tTableForPrint].Cell(m, 5).Range.Text; pomTekst2 = pomTekst2.Substring(0, pomTekst2.Length - 2); if (drr[0].ToString() == pomTekst.Substring(0, pomTekst.Length - 2) && drr[1].ToString() == pomTekst1.Substring(0, pomTekst1.Length - 2)) { oWordDoc.Tables[tTableForPrint].Cell(m, 3).Range.InsertAfter("\n" + drr[2].ToString()); oWordDoc.Tables[tTableForPrint].Cell(m, 4).Range.InsertAfter("\n" + drr[3].ToString()); oWordDoc.Tables[tTableForPrint].Cell(m, 5).Range.Text = (Convert.ToInt32(pomTekst2) + Convert.ToInt32(drr[4])).ToString(); Insertiran = true; } } int j = 1; if (!Insertiran) { foreach (DataColumn dc2 in ds.Tables[brTabeli].Columns) { oWordDoc.Tables[tTableForPrint].Cell(i, j).Range.InsertAfter(drr[dc2.ColumnName].ToString()); j++; } } i++; } } } catch {} } if (ds.Tables.Count > 1) { for (int m = 2; m <= ds.Tables[1].Rows.Count; m++) { if (oWordDoc.Tables[tTableForPrint].Cell(m, 1).Range.Text.Length == 2) { oWordDoc.Tables[tTableForPrint].Cell(m, 1).Row.Delete(); } } } oWordDoc.Activate(); oWordDoc.Application.Visible = true; oWordDoc.Application.WindowState = Microsoft.Office.Interop.Word.WdWindowState.wdWindowStateMaximize; }