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;
}