public static DataTable Get(HzConexao connection, ListCampos ls)
{
string sql = ListCampo.RetornaSQL(@"select [cmpIDPERMISSOES] ,[cmpPERSID] ,[cmpLOGINREDE] ,[cmpADMINISTRADOR] FROM [hzSolar].[dbo].[tblPERMISSOES] ", ls);
return(connection.loadDataTable(sql));
}
public static void Excluir(HzConexao connection, string _cmpIDPERMISSOES)
{
string sql = "exec spExcluirPermissao " + _cmpIDPERMISSOES;
connection.executeProcedure(sql);
}
public tblPERMISSOES(HzConexao connection, string _cmpIDPERMISSOES, string cmpPERSID)
{
DataTable dt;
if (string.IsNullOrEmpty(_cmpIDPERMISSOES) == false)
{
string sql = @"select [cmpIDPERMISSOES] ,[cmpPERSID] ,[cmpLOGINREDE] ,[cmpADMINISTRADOR] FROM [hzSolar].[dbo].[tblPERMISSOES]
WHERE cmpIDPERMISSOES = " + _cmpIDPERMISSOES;
dt = connection.loadDataTable(sql);
}
else
{
string sql = @"select [cmpIDPERMISSOES] ,[cmpPERSID] ,[cmpLOGINREDE] ,[cmpADMINISTRADOR] FROM [hzSolar].[dbo].[tblPERMISSOES]
WHERE cmpPERSID = '" + cmpPERSID + "'";
dt = connection.loadDataTable(sql);
}
if (dt.Rows.Count > 0)
{
this.cmpADMINISTRADOR = bool.Parse(dt.Rows[0]["cmpADMINISTRADOR"].ToString());
this.cmpIDPERMISSOES = dt.Rows[0]["cmpIDPERMISSOES"].ToString();
this.cmpLOGINREDE = dt.Rows[0]["cmpLOGINREDE"].ToString();
this.cmpPERSID = dt.Rows[0]["cmpPERSID"].ToString();
}
}
public void Save(HzConexao connection)
{
if (string.IsNullOrEmpty(cmpIDPERMISSOES) == true)
{
cmpIDPERMISSOES = "0";
}
string sql = "exec dbo.spIncluirPermissoes " + cmpIDPERMISSOES + ",'" + cmpPERSID + "','" + cmpLOGINREDE + "','" + cmpADMINISTRADOR + "'";
connection.executeProcedure(sql);
}
///<sumary>
/// Retorna dados da tabela de autenticação
/// <param name="connection"></param>
/// <param name="ls"></param>
/// <returns></returns>
/// </sumary>
public static DataTable Get(HzConexao connection)
{
try
{
string sql = "SELECT [cmpAUTHPERSON] ,[cmpIDPERSON] ,[cmpAUTHID] ,[cmpVALIDFROM] ,[cmpVALIDUNTIL] FROM [hzSolar].[dbo].[tblAUTHPERPERSON]";
return(connection.loadDataTable(sql));
}
catch (Exception ex)
{
throw new Exception("Erro ao carregar as autorizações por pessoas!");
}
}
/// <summary>
///
/// </summary>
/// <param name="connection"></param>
/// <returns></returns>
public static DataTable GET(HzConexao connection)
{
try
{
string sql = "SELECT [PERSCLASSID] ,[PERSCLASSTYPE] ,[DISPLAYTEXTCUSTOMER],DISPLAYTEXT, ISDISABLED,DATEDELETED FROM [acedb].[bsuser].[PERSCLASSES] where persclasstype = 'V' order by DISPLAYTEXTCUSTOMER";
return(connection.loadDataTable(sql));
}
catch (Exception ex)
{
throw new Exception("Erro ao buscar o tipo de pessoas!");
}
}
/// <summary>
/// Exclui os dados gravados da tabela.
/// </summary>
/// <param name="connection"></param>
/// <param name="_cmpIDPERSON"></param>
/// <param name="_cmpAUTHID"></param>
public static void Excluir(HzConexao connection, string _cmpIDPERSON)
{
try
{
string sql = "exec [hzSolar].[dbo].spExcluirAUTHPERPERSON " + _cmpIDPERSON;
connection.executeProcedure(sql);
}
catch (Exception ex)
{
throw new Exception("Erro ao exlcuir a atorização por pessoa!");
}
}
/// <summary>
/// Descrição do campo local na GridView
/// </summary>
/// <param name="connection"></param>
/// <returns></returns>
#endregion
#region Funções
public static DataTable GET(HzConexao connection)
{
try
{
string sql = "SELECT [AUTHID] ,[SHORTNAME], [NAME] FROM [acedb].[bsuser].[AUTHORIZATIONS] order by SHORTNAME";
return(connection.loadDataTable(sql));
}
catch (Exception ex)
{
throw new Exception("Erro ao buscar as autorizações!");
}
}
/// <summary>
/// Retorna dados para alimentar a GridView.
/// </summary>
/// <param name="connection"></param>
/// <param name="ls"></param>
/// <returns></returns>
public static DataTable Get(HzConexao connection, ListCampos ls)
{
try
{
string sql = ListCampo.RetornaSQL("SELECT [cmpAUTHPERSON] ,[cmpIDPERSON] ,[cmpAUTHID] ,[cmpVALIDFROM] ,[cmpVALIDUNTIL] FROM [hzSolar].[dbo].[tblAUTHPERPERSON]", ls);
return(connection.loadDataTable(sql));
}
catch (Exception ex)
{
throw new Exception("Erro ao buscar as autorizações por pessoa!");
}
}
/// <summary>
/// Grava os dados de consulta da tabela tblPERSONS.
/// </summary>
/// <param name="connection"></param>
public void Save(HzConexao connection)
{
try
{
string sql = "exec spIncluirAutorizacao " + cmpIDPERSON + ",'" + cmpAUTHID + "','" + cmpVALIDFROM + "','" + cmpVALIDUNTIL + "'";
connection.executeProcedure(sql);
}
catch (Exception ex)
{
throw new Exception("Erro ao incluira autorização!");
}
}
/// <summary>
/// Consulta da tabela tbl.COMPANIES
/// </summary>
/// <param name="connection"></param>
/// <returns></returns>
public static DataTable GET(HzConexao connection)
{
try
{
string sql = "SELECT [COMPANYID] ,[COMPANYNO] ,[NAME] FROM [acedb].[bsuser].[COMPANIES] order by NAME";
return(connection.loadDataTable(sql));
}
catch (Exception ex)
{
throw new Exception("Erro ao buscar as empresas!");
}
}
/// <summary>
///
/// </summary>
/// <param name="connection"></param>
/// <param name="_cmpIDPERSON"></param>
public static void Desativar(HzConexao connection, int _cmpIDPERSON)
{
try
{
string sql = "exec spDesativarPessoa " + _cmpIDPERSON + "'";
connection.executeProcedure(sql);
}
catch (Exception)
{
throw new Exception("Erro ao desativar a pessoa");
}
}
/// <summary>
/// Exclui os dados gravados da tabela.
/// </summary>
/// <param name="connection"></param>
/// <param name="_cmpIDPERSON"></param>
/// <param name="_cmpAUTHID"></param>
public static void Excluir(HzConexao connection, string _cmpPERSID)
{
try
{
string sql = "exec spExcluirAUTHPERPERSON_BIS " + _cmpPERSID;
connection.executeProcedure(sql);
}
catch (Exception)
{
throw new Exception("Erro ao excluir a autorização!");
}
}
public static string GETNAME(HzConexao connection, string _COMPANYID)
{
try
{
string sql = "SELECT [COMPANYID] ,[COMPANYNO] ,[NAME] FROM [acedb].[bsuser].[COMPANIES] WHERE COMPANYID='" + _COMPANYID + "' order by NAME";
DataTable dt = connection.loadDataTable(sql);
return(dt.Rows[0]["COMPANYNO"].ToString().Trim() + " - " + dt.Rows[0]["NAME"].ToString().Trim());
}
catch (Exception ex)
{
throw new Exception("Erro ao buscar as empresas!");
}
}
/// <summary>
/// Data fim
/// </summary>
/// <param name="connection"></param>
/// <returns></returns>
#endregion
#region Funções
///<sumary>
/// Retorna dados da tabela de autenticação
/// <param name="connection"></param>
/// <param name="ls"></param>
/// <returns></returns>
/// </sumary>
public static DataTable Get(HzConexao connection)
{
try
{
string sql = @"SELECT TOP 1000 [cmpAUTHPERSON]
,[cmpPERSID] ,[cmpAUTHID] ,[cmpVALIDFROM] ,[cmpVALIDUNTIL] FROM [hzSolar].[dbo].[tblAUTHPERPERSON_BIS]";
return(connection.loadDataTable(sql));
}
catch (Exception ex)
{
throw new Exception("Erro ao buscar as autorizações!");
}
}
public tblAUTHPERPERSON(HzConexao connection, string _cmpAUTHPERSON)
{
string sql = "SELECT [cmpAUTHPERSON] ,[cmpIDPERSON] ,[cmpAUTHID] ,[cmpVALIDFROM] ,[cmpVALIDUNTIL] FROM [hzSolar].[dbo].[tblAUTHPERPERSON] where cmpAUTHPERSON=" + _cmpAUTHPERSON;
DataTable dt = connection.loadDataTable(sql);
if (dt.Rows.Count > 0)
{
this.cmpAUTHID = dt.Rows[0]["cmpAUTHID"].ToString();
this.cmpAUTHPERSON = int.Parse(dt.Rows[0]["cmpAUTHPERSON"].ToString());
this.cmpIDPERSON = int.Parse(dt.Rows[0]["cmpIDPERSON"].ToString());
this.cmpVALIDFROM = DateTime.Parse(dt.Rows[0]["cmpVALIDFROM"].ToString());
this.cmpVALIDUNTIL = DateTime.Parse(dt.Rows[0]["cmpVALIDUNTIL"].ToString());
}
}
/// <summary>
/// buscar com filtro
/// </summary>
/// <param name="connection"></param>
/// <param name="ls"></param>
/// <returns></returns>
public static DataTable Get(HzConexao connection, ListCampos ls)
{
try
{
string sql = ListCampo.RetornaSQL(@"SELECT [cmpIDPERSON], [cmpPERSID], [cmpFIRSTNAME], [cmpLASTNAME], [cmpDATEOFBIRTH8], [cmpPASSPORTNO], [cmpIDNUMBER],
[cmpPERSCLASSID], [cmpPHONEOFFICE], [cmpHONEMOBILE], [cmpEMAIL], [cmpCOMPANYID], [cmpNUMBERPLATE], [cmpRESERV2], [cmpRESERV3],
[cmpREASON],cmpVISID,cmpAutoPERSID FROM [hzSolar].[dbo].[tblPERSONS]", ls);
return(connection.loadDataTable(sql));
}
catch (Exception)
{
throw new Exception("Erro ao buscar ao filtrar os dados no Banco!");
}
}
public static bool ExisteVisitante(HzConexao connection, string passportno)
{
string sql = "SELECT [cmpPASSPORTNO] FROM [hzSolar].[dbo].[tblPERSONS] where cmpPASSPORTNO='" + passportno.Trim() + "'";
DataTable dt = connection.loadDataTable(sql);
if (dt.Rows.Count == 0)
{
return(false);
}
else
{
return(true);
}
}
public static bool ExisteVisitante(HzConexao connection, string passportno)
{
string sql = "SELECT PASSPORTNO FROM [acedb].[bsuser].[VISITORS] where PASSPORTNO='" + passportno.Trim() + "'";
DataTable dt = connection.loadDataTable(sql);
if (dt.Rows.Count == 0)
{
return(false);
}
else
{
return(true);
}
}
/// <summary>
/// Dados da autorização
/// </summary>
/// <param name="connection"></param>
/// <param name="_cmpIDPERSON"></param>
/// <returns></returns>
public static DataTable GETAutorizacaoPessoa(HzConexao connection, string _cmpIDPERSON)
{
try
{
string sql = @"SELECT hzSolar.dbo.tblAUTHPERPERSON.cmpAUTHPERSON, hzSolar.dbo.tblAUTHPERPERSON.cmpIDPERSON, hzSolar.dbo.tblAUTHPERPERSON.cmpAUTHID, hzSolar.dbo.tblAUTHPERPERSON.cmpVALIDFROM,
hzSolar.dbo.tblAUTHPERPERSON.cmpVALIDUNTIL, [acedb].[bsuser].AUTHORIZATIONS.SHORTNAME, [acedb].[bsuser].AUTHORIZATIONS.NAME
FROM hzSolar.dbo.tblAUTHPERPERSON INNER JOIN
[acedb].[bsuser].AUTHORIZATIONS ON hzSolar.dbo.tblAUTHPERPERSON.cmpAUTHID = [acedb].[bsuser].AUTHORIZATIONS.AUTHID WHERE hzSolar.dbo.tblAUTHPERPERSON.cmpIDPERSON=" + _cmpIDPERSON;
return(connection.loadDataTable(sql));
}
catch (Exception)
{
throw new Exception("Erro ao buscar a autorização da pessoa!");
}
}
public static string AuthoBIS(HzConexao connection, string loginRede)
{
string sql = @"select [cmpPERSID] FROM [hzSolar].[dbo].[tblPERMISSOES]
WHERE cmpLOGINREDE = '" + loginRede.Trim() + "'";
DataTable dt = connection.loadDataTable(sql);
if (dt.Rows.Count > 0)
{
return(dt.Rows[0]["cmpPERSID"].ToString());
}
else
{
return("");
}
}
public static bool LoginBIS(HzConexao connection, string loginRede)
{
string sql = @"select [cmpIDPERMISSOES] ,[cmpPERSID] ,[cmpLOGINREDE] ,[cmpADMINISTRADOR] FROM [hzSolar].[dbo].[tblPERMISSOES]
WHERE cmpLOGINREDE = '" + loginRede.Trim() + "'";
DataTable dt = connection.loadDataTable(sql);
if (dt.Rows.Count > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// Dados da autorização
/// </summary>
/// <param name="connection"></param>
/// <param name="_cmpIDPERSON"></param>
/// <returns></returns>
public static DataTable GETAutorizacaoPessoa(HzConexao connection, string _cmpPERSID)
{
try
{
string sql = @"SELECT acedb.bsuser.AUTHORIZATIONS.SHORTNAME, acedb.bsuser.AUTHORIZATIONS.NAME, tblAUTHPERPERSON_BIS.cmpAUTHPERSON, tblAUTHPERPERSON_BIS.cmpPERSID,
tblAUTHPERPERSON_BIS.cmpAUTHID, tblAUTHPERPERSON_BIS.cmpVALIDFROM, tblAUTHPERPERSON_BIS.cmpVALIDUNTIL
FROM acedb.bsuser.AUTHORIZATIONS INNER JOIN
tblAUTHPERPERSON_BIS ON acedb.bsuser.AUTHORIZATIONS.AUTHID = tblAUTHPERPERSON_BIS.cmpAUTHID WHERE tblAUTHPERPERSON_BIS.cmpPERSID=" + _cmpPERSID;
return(connection.loadDataTable(sql));
}
catch (Exception)
{
throw new Exception("Erro ao buscar a autorização da pessoa!");
}
}
public static List <tblAUTHPERPERSON> GetlstAUTHPERPERSON(HzConexao connection, string _cmpIDPERSON)
{
List <tblAUTHPERPERSON> lst = new List <tblAUTHPERPERSON>();
DataTable dt = GETAutorizacaoPessoa(connection, _cmpIDPERSON);
for (int i = 0; i < dt.Rows.Count; i++)
{
tblAUTHPERPERSON auto = new tblAUTHPERPERSON();
auto.cmpAUTHID = dt.Rows[0]["cmpAUTHID"].ToString();
auto.cmpAUTHPERSON = int.Parse(dt.Rows[0]["cmpAUTHPERSON"].ToString());
auto.cmpIDPERSON = int.Parse(dt.Rows[0]["cmpIDPERSON"].ToString());
auto.cmpVALIDFROM = DateTime.Parse(dt.Rows[0]["cmpVALIDFROM"].ToString());
auto.cmpVALIDUNTIL = DateTime.Parse(dt.Rows[0]["cmpVALIDUNTIL"].ToString());
auto.SHORTNAME = dt.Rows[0]["SHORTNAME"].ToString();
lst.Add(auto);
}
return(lst);
}
public void CarregarAUTHPERPERSON(HzConexao connection, string _cmpIDPERSON)
{
lstAUTHPERPERSON = new List <tblAUTHPERPERSON>();
DataTable dtAuto = tblAUTHPERPERSON.GETAutorizacaoPessoa(connection, _cmpIDPERSON);
for (int i = 0; i < dtAuto.Rows.Count; i++)
{
tblAUTHPERPERSON autp = new tblAUTHPERPERSON();
autp.cmpAUTHID = dtAuto.Rows[i]["cmpAUTHID"].ToString();
autp.cmpAUTHPERSON = int.Parse(dtAuto.Rows[i]["cmpAUTHPERSON"].ToString());
autp.cmpIDPERSON = int.Parse(dtAuto.Rows[i]["cmpIDPERSON"].ToString());
autp.cmpVALIDFROM = DateTime.Parse(dtAuto.Rows[i]["cmpVALIDFROM"].ToString());
autp.cmpVALIDUNTIL = DateTime.Parse(dtAuto.Rows[i]["cmpVALIDUNTIL"].ToString());
autp.SHORTNAME = dtAuto.Rows[i]["SHORTNAME"].ToString();
lstAUTHPERPERSON.Add(autp);
}
}
public static DataTable GETFuncionarios(HzConexao connection, string Nome)
{
try
{
string[] nomes = Nome.Split(' ');
string firstname = nomes[0];
string lastname = "";
if (nomes.Length > 1)
{
lastname = Nome.Substring(firstname.Length);
}
string sql = "EXEC [dbo].sbBuscarFuncionario '" + firstname + "','" + lastname + "'";
DataTable dt = connection.loadDataTable(sql);
return(dt);
}
catch (Exception)
{
throw new Exception("Erro ao buscar os visitantes pelo nome ou documento!");
}
}
/// <summary>
///
/// </summary>
/// <param name="connection"></param>
public string Save(HzConexao connection)
{
try
{
if (cmpIDPERSON == null)
{
cmpIDPERSON = "0";
}
string sql = "exec spIncluirPersons " + cmpIDPERSON + ",'" + cmpPERSID + "','" + cmpFIRSTNAME + "','" + cmpLASTNAME + "','" + cmpDATEOFBIRTH + "','" + cmpPASSPORTNO + "','" + cmpIDNUMBER + "','" +
cmpPERSCLASSID + "','" + cmpPHONEOFFICE + "','" + cmpPHONEMOBILE + "','" + cmpEMAIL + "','" + cmpCOMPANYID + "','" + cmpNUMBERPLATE + "','" + cmpRESERVE2 + "','" + cmpRESERVE3 + "','" + cmpREASON + "','" + cmpPASSPORTNO + "','" +
cmpVISID + "','" + cmpAutoPERSID + "'";
DataTable dt = connection.loadDataTable(sql);
return(dt.Rows[0][0].ToString());
}
catch (Exception)
{
throw new Exception("Erro ao incluir a pessoa!");
}
}
public tblPERSONS(HzConexao connection, string _PERSID)
{
string sql = @"SELECT PERSID, FIRSTNAME, LASTNAME, DATEOFBIRTH, IDNUMBER, PERSCLASSID, PHONEOFFICE, PHONEMOBILE, EMAIL, COMPANYID, NUMBERPLATE, RESERVE2, RESERVE3, PERSNO, PERSCLASS
FROM acedb.bsuser.PERSONS WHERE PERSID='" + _PERSID + "' AND PERSCLASS='E'";
DataTable dt = connection.loadDataTable(sql);
this.cmpPERSID = dt.Rows[0]["PERSID"].ToString();
this.cmpCOMPANYID = dt.Rows[0]["COMPANYID"].ToString();
this.cmpDATEOFBIRTH = dt.Rows[0]["DATEOFBIRTH"].ToString();
this.cmpEMAIL = dt.Rows[0]["EMAIL"].ToString();
this.cmpFIRSTNAME = dt.Rows[0]["FIRSTNAME"].ToString();
this.cmpPHONEMOBILE = dt.Rows[0]["PHONEMOBILE"].ToString();
this.cmpIDNUMBER = dt.Rows[0]["IDNUMBER"].ToString();
this.cmpLASTNAME = dt.Rows[0]["LASTNAME"].ToString();
this.cmpNUMBERPLATE = dt.Rows[0]["NUMBERPLATE"].ToString();
this.cmpPERSCLASSID = dt.Rows[0]["PERSCLASSID"].ToString();
this.cmpPERSID = dt.Rows[0]["PERSID"].ToString();
this.PERSNO = dt.Rows[0]["PERSNO"].ToString();
this.cmpPHONEOFFICE = dt.Rows[0]["PHONEOFFICE"].ToString();
this.cmpRESERVE2 = dt.Rows[0]["RESERVE2"].ToString();
this.cmpRESERVE3 = dt.Rows[0]["RESERVE3"].ToString();
}
protected void Page_Load(object sender, EventArgs e)
{
HzConexao c = new HzConexao(@"192.168.200.170\desenv", "sa", "rona3007", "HzManutencao", "System.Data.SqlClient");
String strFont = "Helvetica";
int leftMargin = 30;
int topMargin = 30;
int bottomMargin = 30;
int lineHeight = 20;
int linesPerPage;
pdfDocument myDoc = new pdfDocument("Horizon", "Orion");
// Add a page
pdfPage myPage = myDoc.addPage(predefinedPageSize.csSharpPDFFormat);
// Load an image (I was able to load JPEG, GIF, TIFF, and BMP formats)
//myDoc.addImageReference(Server.MapPath("~/Assets/Images/Sample.gif"), "ex");
//myPage.addImage(myDoc.getImageReference("ex"), leftMargin, getTop(myPage, 120));
// Header Text
myPage.addText("Ordem de Serviço", leftMargin, getTop(myPage, 30),
myDoc.getFontReference(strFont), 15, pdfColor.Black);
// Body Text
//myPage.addText("Divisão de Administração Predial, Obras e Instalações", leftMargin, getTop(myPage, 50),
// myDoc.getFontReference(strFont), 10, pdfColor.Black);
//myPage.addText("Relatório emitido em: " + DateTime.Now.ToShortDateString(), leftMargin, getTop(myPage, 70),
// myDoc.getFontReference(strFont), 10, pdfColor.Black);
//pdfTable myTable = new pdfTable(myDoc, 1, new pdfColor("000000"), 2,
// new pdfTableStyle(myDoc.getFontReference(strFont),
// 10, pdfColor.Black, new pdfColor("d9d1b3")),
// new pdfTableStyle(myDoc.getFontReference(strFont),
// 10, pdfColor.Black, pdfColor.White),
// new pdfTableStyle(myDoc.getFontReference(strFont),
// 10, pdfColor.Black, pdfColor.White));
int q = 0;
int pg = 1;
int top = 40;
using (DataTable table = c.loadDataTable("select * from HzManutencao..vwATE_OS where cmpIdOS = 1"))
{
if (table != null && table.Rows.Count > 0)
{
DataRow r = table.Rows[0];
pdfTable myTable = new pdfTable(myDoc, 1, new pdfColor("000000"), 2, new pdfTableStyle(myDoc.getFontReference(strFont), 10, pdfColor.Black, new pdfColor("FFFFFF")),
new pdfTableStyle(myDoc.getFontReference(strFont), 10, pdfColor.Black, pdfColor.White));
myTable.coordX = leftMargin;
myTable.coordY = getTop(myPage, top);
//// Create table's header
int cl = 0;
myTable.tableHeader.rowHeight = 10;
myTable.tableHeader.addColumn(150, predefinedAlignment.csLeft);
myTable.tableHeader[cl++].addText("Nº O.S.");
myTable.tableHeader.addColumn(420, predefinedAlignment.csLeft);
myTable.tableHeader[cl].columnAlign = predefinedAlignment.csLeft;
myTable.tableHeader[cl++].addText(r["cmpNuOS"].ToString());
// Create Table Row
pdfTableRow myRow = myTable.createRow();
cl = 0;
myRow = myTable.createRow();
myRow[cl].columnAlign = predefinedAlignment.csLeft;
myRow[cl++].addText("Data Solicitação:");
myRow[cl++].addText(r["cmpDtAbertura"].ToString().Trim());
myTable.addRow(myRow);
cl = 0;
myRow = myTable.createRow();
myRow[cl].columnAlign = predefinedAlignment.csLeft;
myRow[cl++].addText("Obra:");
myRow[cl++].addText(r["cmpNoObra"].ToString().Trim() + (r["cmpNuContrato"] != null ? " -> Contrato nº " + r["cmpNuContrato"].ToString() : ""));
myTable.addRow(myRow);
cl = 0;
myRow = myTable.createRow();
myRow[cl].columnAlign = predefinedAlignment.csLeft;
myRow[cl++].addText("Soliciante:");
myRow[cl++].addText(r["cmpNoSolicitante"].ToString().Trim());
myTable.addRow(myRow);
cl = 0;
myRow = myTable.createRow();
myRow[cl].columnAlign = predefinedAlignment.csLeft;
myRow[cl++].addText("Local:");
myRow[cl++].addText(r["cmpDcLocal"].ToString().Trim());
myTable.addRow(myRow);
cl = 0;
myRow = myTable.createRow();
myRow[cl].columnAlign = predefinedAlignment.csLeft;
myRow[cl++].addText("Setor:");
myRow[cl++].addText(r["cmpNoSetor"].ToString().Trim());
myTable.addRow(myRow);
cl = 0;
myRow = myTable.createRow();
myRow[cl].columnAlign = predefinedAlignment.csLeft;
myRow[cl++].addText("Data Início:");
myRow[cl++].addText(r["cmpDtInicioAtendimento"].ToString() != "" ? DateTime.Parse(r["cmpDtInicioAtendimento"].ToString()).ToString("dd/MM/yyyy HH:mm") : "");
myTable.addRow(myRow);
cl = 0;
myRow = myTable.createRow();
myRow[cl].columnAlign = predefinedAlignment.csLeft;
myRow[cl++].addText("Data Conclusão:");
myRow[cl++].addText(r["cmpDtConclusaoAtendimento"].ToString() != "" ? DateTime.Parse(r["cmpDtConclusaoAtendimento"].ToString()).ToString("dd/MM/yyyy HH:mm") : "");
myTable.addRow(myRow);
cl = 0;
myRow = myTable.createRow();
myRow[cl].columnAlign = predefinedAlignment.csLeft;
myRow[cl++].addText("Descrição do Serviço:");
myRow[cl++].addText(r["cmpDcDescricaoSolicitacao"].ToString().Trim());
myTable.addRow(myRow);
cl = 0;
myRow = myTable.createRow();
myRow.rowHeight = 50;
myRow[cl].columnAlign = predefinedAlignment.csLeft;
myRow[cl++].addText("Verificação do Serviço:");
myRow[cl++].addText(r["cmpDcObservacaoConclusao"].ToString().Trim());
myTable.addRow(myRow);
top += 10 * 9;
myPage.addTable(myTable);
top += 100;
myPage.addText("Material Utilizado", leftMargin, this.getTop(myPage, top), myDoc.getFontReference(strFont), 10, pdfColor.Black);
top += 12;
string sql = "select * from HzManutencao..vwATE_OSMaterial where cmpIdOS = " + r["cmpIdOS"].ToString();
using (DataTable tblMat = Global.GetConnection().loadDataTable(sql))
{
if (tblMat != null && tblMat.Rows.Count > 0)
{
myTable = new pdfTable(myDoc, 1, new pdfColor("000000"), 2, new pdfTableStyle(myDoc.getFontReference(strFont), 8, pdfColor.Black, new pdfColor("FFFFFF")),
new pdfTableStyle(myDoc.getFontReference(strFont), 8, pdfColor.Black, pdfColor.White));
myTable.coordX = leftMargin;
//top = 210;
myTable.coordY = getTop(myPage, top);
//// Create table's header
cl = 0;
myTable.tableHeader.rowHeight = 10;
myTable.tableHeader.addColumn(350, predefinedAlignment.csLeft);
myTable.tableHeader[cl++].addText("Descrição");
myTable.tableHeader.addColumn(30, predefinedAlignment.csCenter);
myTable.tableHeader[cl].columnAlign = predefinedAlignment.csCenter;
myTable.tableHeader[cl++].addText("Unid");
myTable.tableHeader.addColumn(50, predefinedAlignment.csRight);
myTable.tableHeader[cl].columnAlign = predefinedAlignment.csRight;
myTable.tableHeader[cl++].addText("Qtd.");
myTable.tableHeader.addColumn(50, predefinedAlignment.csRight);
myTable.tableHeader[cl].columnAlign = predefinedAlignment.csRight;
myTable.tableHeader[cl++].addText("Preço");
myTable.tableHeader.addColumn(90, predefinedAlignment.csRight);
myTable.tableHeader[cl].columnAlign = predefinedAlignment.csRight;
myTable.tableHeader[cl++].addText("Sub-Total");
top += 15;
float total = 0f;
foreach (DataRow row in tblMat.Rows)
{
// Create Table Row
myRow = myTable.createRow();
cl = 0;
myRow = myTable.createRow();
myRow[cl].columnAlign = predefinedAlignment.csLeft;
myRow[cl++].addText(row["DcMaterial"].ToString().Trim());
myRow[cl++].addText(row["cmpDcUnidade"].ToString().Trim());
myRow[cl++].addText(decimal.Parse(row["cmpQtMaterial"].ToString()).ToString("0,0.00"));
myRow[cl++].addText(decimal.Parse(row["cmpVlMaterial"].ToString()).ToString("0,0.00"));
myRow[cl++].addText(decimal.Parse(row["cmpVlSubTotal"].ToString()).ToString("0,0.00"));
total += float.Parse(row["cmpVlSubTotal"].ToString());
myTable.addRow(myRow);
top += 12;
}
myPage.addTable(myTable);
top += 3;
myTable = new pdfTable(myDoc, 1, new pdfColor("000000"), 2, new pdfTableStyle(myDoc.getFontReference(strFont), 10, pdfColor.Black, new pdfColor("FFFFFF")),
new pdfTableStyle(myDoc.getFontReference(strFont), 10, pdfColor.Black, pdfColor.White));
myTable.coordX = leftMargin;
myTable.coordY = getTop(myPage, top);
//// Create table's header
cl = 0;
myTable.tableHeader.rowHeight = 10;
myTable.tableHeader.addColumn(570, predefinedAlignment.csRight);
//IFormatProvider fp = new CultureInfo("pt-BR", true);
myTable.tableHeader[cl++].addText("Total: " + total.ToString("0,0.00"));
top += 17;
myPage.addTable(myTable);
}
}
myTable = new pdfTable(myDoc, 1, new pdfColor("000000"), 2, new pdfTableStyle(myDoc.getFontReference(strFont), 10, pdfColor.Black, new pdfColor("FFFFFF")),
new pdfTableStyle(myDoc.getFontReference(strFont), 10, pdfColor.Black, pdfColor.White));
myTable.coordX = leftMargin;
myTable.coordY = getTop(myPage, top);
//// Create table's header
cl = 0;
myTable.tableHeader.rowHeight = 30;
myTable.tableHeader.rowVerticalAlign = predefinedVerticalAlignment.csTop;
myTable.tableHeader.addColumn(570, predefinedAlignment.csLeft);
myTable.tableHeader[cl].columnVerticalAlign = predefinedVerticalAlignment.csTop;
myTable.tableHeader[cl++].addText("Conclusão/Aceite do Solicitante");
top += 30;
cl = 0;
myRow = myTable.createRow();
myRow.rowHeight = 10;
myRow[cl].columnAlign = predefinedAlignment.csLeft;
myRow[cl++].addText("Data: " + DateTime.Now.ToShortDateString() + " às " + DateTime.Now.ToShortTimeString());
myTable.addRow(myRow);
top += 15;
cl = 0;
myRow = myTable.createRow();
myRow.rowHeight = 10;
myRow[cl].columnAlign = predefinedAlignment.csLeft;
myRow[cl++].addText("Satisfeito com o serviço: " + "__Sim __Não");
myTable.addRow(myRow);
top += 15;
myPage.addTable(myTable);
myTable = new pdfTable(myDoc, 1, new pdfColor("000000"), 2, new pdfTableStyle(myDoc.getFontReference(strFont), 10, pdfColor.Black, new pdfColor("FFFFFF")),
new pdfTableStyle(myDoc.getFontReference(strFont), 10, pdfColor.Black, pdfColor.White));
myTable.coordX = leftMargin;
myTable.coordY = getTop(myPage, top);
cl = 0;
myTable.tableHeader.rowHeight = 50;
myTable.tableHeader.rowVerticalAlign = predefinedVerticalAlignment.csBottom;
myTable.tableHeader.addColumn(285, predefinedAlignment.csCenter);
myTable.tableHeader[cl].columnVerticalAlign = predefinedVerticalAlignment.csBottom;
myTable.tableHeader[cl++].addText("De acordo Empresa");
myTable.tableHeader.addColumn(285, predefinedAlignment.csCenter);
myTable.tableHeader[cl].columnVerticalAlign = predefinedVerticalAlignment.csBottom;
myTable.tableHeader[cl].addText("De acordo Gestor");
myPage.addTable(myTable);
myPage.addText("Pág. " + pg++, leftMargin, 10, myDoc.getFontReference(strFont), 5, pdfColor.Black);
//string nomepdf = "c:\\Horizon\\Ocr\\Relatorios\\" + "Relatorio_" + (txtPlacaRelatorio.Text != "" ? txtPlacaRelatorio.Text : "Todas") + "_" + retornaDataRelatorio(txtDataInicial.Text) + "_" + retornaDataRelatorio(txtDataFinal.Text) + ".pdf";
string nomepdf = Server.MapPath("pdf" + DateTime.Now.Day.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Year.ToString() + DateTime.Now.Millisecond.ToString() + ".pdf");
myDoc.createPDF(nomepdf);
System.Diagnostics.Process.Start(nomepdf);
}
}
++q;
//if (q == 30)
//{
// myPage.addText("Pág. " + pg++, leftMargin, 10,
// myDoc.getFontReference(strFont), 5, pdfColor.Black);
// myPage.addTable(myTable);
// myPage = myDoc.addPage(predefinedPageSize.csSharpPDFFormat);
// q = 0;
// myTable = new pdfTable(myDoc, 1, new pdfColor("000000"), 5,
// new pdfTableStyle(myDoc.getFontReference(strFont),
// 10, pdfColor.Black, new pdfColor("d9d1b3")),
// new pdfTableStyle(myDoc.getFontReference(strFont),
// 10, pdfColor.Black, pdfColor.White),
// new pdfTableStyle(myDoc.getFontReference(strFont),
// 10, pdfColor.Black, pdfColor.White));
// myTable.coordX = leftMargin;
// myTable.coordY = getTop(myPage, 100);
// // Create table's header
// myTable.tableHeader.rowHeight = 10;
// c = 0;
// myTable.tableHeader.addColumn(100, predefinedAlignment.csCenter);
// myTable.tableHeader[c++].addText("Placa");
// if (rdbAcesso.IsChecked.Value)
// {
// myTable.tableHeader.addColumn(200, predefinedAlignment.csLeft);
// myTable.tableHeader[c++].addText("Nome");
// }
// myTable.tableHeader.addColumn(150, predefinedAlignment.csCenter);
// myTable.tableHeader[c++].addText("Data");
// if (rdbAcesso.IsChecked.Value)
// {
// myTable.tableHeader.addColumn(100, predefinedAlignment.csCenter);
// myTable.tableHeader[c].addText("Cancela");
// }
// myPage.addText("Procuradoria Geral da República", leftMargin, getTop(myPage, 30),
// myDoc.getFontReference(strFont), 20, pdfColor.Black);
// // Body Text
// myPage.addText("Relatório dos Acessos à Garagem", leftMargin, getTop(myPage, 50),
// myDoc.getFontReference(strFont), 10, pdfColor.Black);
// myPage.addText("Relatório emitido em: " + DateTime.Now.ToShortDateString(), leftMargin, getTop(myPage, 70),
// myDoc.getFontReference(strFont), 10, pdfColor.Black);
//}
}
/// <summary>
///
/// </summary>
/// <param name="connection"></param>
/// <param name="_cmpIDPERSON"></param>
public tblPERSONS(HzConexao connection, string _cmpIDPERSON, string _PERSID, string Origem)
{
try
{
if (Origem == "SQL")
{
try
{
string sql = @"SELECT [cmpIDPERSON],cmpVISID, [cmpPERSID], [cmpFIRSTNAME], [cmpLASTNAME], [cmpDATEOFBIRTH], [cmpPASSPORTNO], [cmpIDNUMBER],
[cmpPERSCLASSID], [cmpPHONEOFFICE], [cmpPHONEMOBILE], [cmpEMAIL], [cmpCOMPANYID], [cmpNUMBERPLATE], [cmpRESERVE2], [cmpRESERVE3],
[cmpREASON] , cmpAutoPERSID FROM [hzSolar].[dbo].[tblPERSONS] WHERE cmpIDPERSON = " + _cmpIDPERSON;
DataTable dt = connection.loadDataTable(sql);
this.cmpCOMPANYID = dt.Rows[0]["cmpCOMPANYID"].ToString();
this.cmpDATEOFBIRTH = dt.Rows[0]["cmpDATEOFBIRTH"].ToString();
this.cmpEMAIL = dt.Rows[0]["cmpEMAIL"].ToString();
this.cmpFIRSTNAME = dt.Rows[0]["cmpFIRSTNAME"].ToString();
this.cmpPHONEMOBILE = dt.Rows[0]["cmpPHONEMOBILE"].ToString();
this.cmpIDNUMBER = dt.Rows[0]["cmpIDNUMBER"].ToString();
this.cmpIDPERSON = dt.Rows[0]["cmpIDPERSON"].ToString();
this.cmpLASTNAME = dt.Rows[0]["cmpLASTNAME"].ToString();
this.cmpNUMBERPLATE = dt.Rows[0]["cmpNUMBERPLATE"].ToString();
this.cmpPERSCLASSID = dt.Rows[0]["cmpPERSCLASSID"].ToString();
this.cmpPERSID = dt.Rows[0]["cmpPERSID"].ToString();
this.cmpPASSPORTNO = dt.Rows[0]["cmpPASSPORTNO"].ToString();
this.cmpPHONEOFFICE = dt.Rows[0]["cmpPHONEOFFICE"].ToString();
this.cmpREASON = dt.Rows[0]["cmpREASON"].ToString();
this.cmpRESERVE2 = dt.Rows[0]["cmpRESERVE2"].ToString();
this.cmpRESERVE3 = dt.Rows[0]["cmpRESERVE3"].ToString();
this.cmpVISID = dt.Rows[0]["cmpVISID"].ToString();
this.cmpAutoPERSID = dt.Rows[0]["cmpAutoPERSID"].ToString();
CarregarAUTHPERPERSON(connection, _cmpIDPERSON);
}
catch (Exception)
{
throw new Exception("Erro ao carregar a pessoa no Banco Solar!");
}
}
else
{
try
{
string sql = @"SELECT [acedb].[bsuser].PERSONS.PERSID, [acedb].[bsuser].PERSONS.FIRSTNAME, [acedb].[bsuser].PERSONS.LASTNAME, [acedb].[bsuser].PERSONS.DATEOFBIRTH, [acedb].[bsuser].VISITORS.PASSPORTNO, [acedb].[bsuser].PERSONS.IDNUMBER,
[acedb].[bsuser].PERSONS.PERSCLASSID, [acedb].[bsuser].PERSONS.PHONEOFFICE, [acedb].[bsuser].PERSONS.PHONEMOBILE, [acedb].[bsuser].PERSONS.EMAIL, [acedb].[bsuser].PERSONS.COMPANYID, [acedb].[bsuser].PERSONS.NUMBERPLATE,
[acedb].[bsuser].PERSONS.RESERVE2, [acedb].[bsuser].PERSONS.RESERVE3, [acedb].[bsuser].VISITORS.REASON,acedb.bsuser.VISITORS.VISID,[acedb].[bsuser].PERSONS.PHONEPRIVATE,[acedb].[bsuser].PERSONS.RESERVE1
FROM [acedb].[bsuser].PERSONS INNER JOIN
[acedb].[bsuser].VISITORS ON [acedb].[bsuser].PERSONS.PERSID = [acedb].[bsuser].VISITORS.PERSID WHERE [acedb].[bsuser].PERSONS.PERSID='" + _PERSID + "'";
DataTable dt = connection.loadDataTable(sql);
this.cmpPERSID = dt.Rows[0]["PERSID"].ToString();
this.cmpCOMPANYID = dt.Rows[0]["COMPANYID"].ToString();
this.cmpDATEOFBIRTH = RetornaData(dt.Rows[0]["DATEOFBIRTH"].ToString());
this.cmpEMAIL = dt.Rows[0]["EMAIL"].ToString();
this.cmpFIRSTNAME = dt.Rows[0]["FIRSTNAME"].ToString();
this.cmpPHONEMOBILE = dt.Rows[0]["PHONEMOBILE"].ToString();
this.cmpIDNUMBER = dt.Rows[0]["RESERVE1"].ToString();
//this.cmpIDPERSON = dt.Rows[0]["IDPERSON"].ToString();
this.cmpLASTNAME = dt.Rows[0]["LASTNAME"].ToString();
this.cmpNUMBERPLATE = dt.Rows[0]["NUMBERPLATE"].ToString();
this.cmpPERSCLASSID = dt.Rows[0]["PERSCLASSID"].ToString();
this.cmpPERSID = dt.Rows[0]["PERSID"].ToString();
this.cmpPASSPORTNO = dt.Rows[0]["PASSPORTNO"].ToString();
this.cmpPHONEOFFICE = dt.Rows[0]["PHONEPRIVATE"].ToString();
this.cmpREASON = dt.Rows[0]["REASON"].ToString();
this.cmpRESERVE2 = dt.Rows[0]["RESERVE2"].ToString();
this.cmpRESERVE3 = dt.Rows[0]["RESERVE3"].ToString();
this.cmpVISID = dt.Rows[0]["VISID"].ToString();
}
catch (Exception ex)
{
throw new Exception("Erro ao carregar a pessoa no BIS!");
}
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}