bozo6

// start namespace ixbob

var ixbob = new Object();
ixbob = {

    /**
    * Return the Mandant used in Bob from the Data\Common\Companiy.adt
    * @ConnectId Connection Id for the COMMON Database of Bob
    * @return null on error.
    */
    getCompanys: function (ConnectId) {

		var data;

		if (log.isDebugEnabled()) log.debug("getCompanys(");

        try {
			data = db.getMultiLine(ConnectId, "SELECT ID, NAME, PATH FROM company");
		}
		catch(e) {
			var error = e;
			data = null;
            log.error("Error getting BOB Mandanten: " + error);
		}

		return data;

        if (log.isDebugEnabled()) log.debug(")getCompanys");
    },
    /**
    * Return the Mandant used in Bob from the Data\Common\Companiy.adt
    * @ConnectId Connection Id for the COMMON Database of Bob
    * @CompanyId BOB Company number (reference)
    * @return null on error.
    */
    getCompanyById: function (ConnectId, CompanyId) {

		var data;
		var sqlQuery;


		if (log.isDebugEnabled()) log.debug("getCompanyById(");

        try
        {
            sqlQuery =  "SELECT ID, NAME, PATH FROM COMPANY WHERE ID = '" + CompanyId + "'";
			data = db.getMultiLine(ConnectId, sqlQuery, 10);
		}
		catch(e) {
			var error = e;
			data = null;
            log.error("Error getting BOB Mandanten: " + error);
		}

		return data;

        if (log.isDebugEnabled()) log.debug(")getCompanys");
    },

	/**
    * Open a custom connection to the Bob Folder indicate by the Mandant URL
    * @ConnectId Connection Id for the FOLDER Database of Bob
	* @strURL The complete JDBC connectionString to the FOLDER database of Bob
    * @throws an Exception on Error
    */
	openConnection: function (connectId, strPath) {

		log.debug("openConnection(");

		if (EM_connections[connectId].initdone == true) {
			return;
		}

		log.debug("Now init JDBC driver");

		var driverName = EM_connections[connectId].driver;
		//var dbUrl = EM_connections[connectId].url;
		var strURL = EM_connections[connectId].url;
		var dbUrl = strURL.replace("#PATH#", strPath);

		var dbUser = EM_connections[connectId].user;
		var dbPassword = EM_connections[connectId].password;

		if (emConnect.decryptAs) {
			dbPassword = emConnect.decryptAs(dbPassword);
		}

		try {

			if (!EM_connections[connectId].classloaded) {
				Class.forName(driverName).newInstance();

				log.debug("Register driver JDBC");
				//DriverManager.registerDriver(new JdbcOdbcDriver());
				EM_connections[connectId].classloaded = true;
			}

			log.debug("Get Connection");
			EM_connections[connectId].dbcn = DriverManager.getConnection(dbUrl, dbUser, dbPassword);

			log.debug("Init done.");

		} catch (e) {
			log.debug("ODBC Exception: " + e);
			throw e;
		}

		EM_connections[connectId].initdone = true;

		log.debug(")openConnection");
	},

	/**
    * Close a custom connection to the Bob Folder indicate by the Mandant URL
    * @ConnectId Connection Id for the FOLDER Database of Bob
    */
	closeConnection: function (connectId) {

		log.debug("closeConnection(");

		if (EM_connections[connectId].initdone) {

			if (EM_connections[connectId].dbcn) {

				try {

					EM_connections[connectId].dbcn.close();
					EM_connections[connectId].initdone = false;
					log.debug("Connection closed: " + connectId);

				} catch (e) {
					log.info("Error closing database " + connectId + ": " + e);
				}
			}
		}

		log.debug(")closeConnection");
	},

    /**
    * Return the Mandant used in Bob from the Data\Common\Companiy.adt
    * @ConnectId Connection Id for the FOLDER Database of Bob
	* @strBarcode The Barcode Data to identify the document
    * @return null on error.
    */
    getDocumentData: function (ConnectId, strBarcode) {

		var data = null;
		var strSQL;

		if (log.isDebugEnabled()) log.debug("getDocumentData(");

        try {

			strSQL = "SELECT HID, HDBK, HFYEAR, HYear, HMonth, HDocNo, HRemInt, HRemExt, abs(HAmount) as HAmount, abs(HBase) as HBase, abs(HTax) as HTax, HDocDate, MODIFIEDBY, MODIFIEDON, HNOTVALIDATED, HPROCSTATUS \
						FROM ac_chisto \
						WHERE HRemInt LIKE '%" + strBarcode + "%' \
						ORDER BY HDocNo DESC";
			if (log.isDebugEnabled()) log.debug(strSQL);
			data = db.getLine(ConnectId, strSQL);
		}
		catch(e) {
			var error = e;
			data = null;
            log.error("Error getting BOB Document data: " + error);
		}

		return data;

        if (log.isDebugEnabled()) log.debug(")getDocumentData");
    },
	/**
    * Returns Data from the ac_chisto.adt
    * @ConnectId Connection Id for the FOLDER Database of Bob
	* @strInvNo InvNumber in the field HRemInt
	* @strVendorId Id of the vendor according to invoice
    * @return null on error.
    */
    getDocumentDataByNumber: function (ConnectId, strInvNo, strVendorId) {

		var data;
		var strSQL;

		if (log.isDebugEnabled()) log.debug("getDocumentDataByNumber");

        try {

			strSQL = "SELECT ac_chisto.HDBK, ac_ahisto.HORDERNO, ac_ahisto.HID, ac_chisto.HDOCNO,  \
                             ac_ahisto.HAMOUNT, ac_ahisto.MODIFIEDBY, ac_ahisto.MODIFIEDON \
					  FROM ac_chisto \
					  JOIN ac_ahisto ON ac_chisto.HDOCNO = ac_ahisto.HDOCNO \
					       AND ac_chisto.HDBK = ac_ahisto.HDBK \
						   AND ac_chisto.HFYEAR = ac_ahisto.HFYEAR AND ac_chisto.HMONTH = ac_ahisto.HMONTH \
					  WHERE (ac_chisto.HREMINT LIKE '%" + strInvNo + "%' \
							OR ac_chisto.HREMEXT LIKE '%" + strInvNo + "%') \
						   AND ac_chisto.HID LIKE '" + strVendorId + "' \
						   AND ac_ahisto.HORDERNO = 2";

			data = db.getLine(ConnectId, strSQL);
		}
		catch(e) {
			var error = e;
			data = null;
            log.error("Error getting BOB Document data: " + error);
		}

		return data;

        if (log.isDebugEnabled()) log.debug("getDocumentDataByNumber");
    },

	/**
    * Returns Data from the ac_chisto.adt
    * @ConnectId Connection Id for the FOLDER Database of Bob
	* @datInvDate Date of the document
	* @strVendorId Id of the vendor according to invoice
	* @numAmount Total Amount of the invoice
    * @return null on error.
    */
    getInvoiceDataBy: function (ConnectId, datInvDate, strVendorId, numAmount) {

		var data;
		var strSQL;
		var m = datInvDate.substring(3,2);
		var d = datInvDate.substring(0,2);
		var y = datInvDate.substring(6,4);

		if (log.isDebugEnabled()) log.debug("getInvoiceDataBy");

        try {

			strSQL = "SELECT ac_chisto.HDBK, ac_ahisto.HORDERNO, ac_ahisto.HID, ac_chisto.HDOCNO,  \
                             ac_ahisto.HAMOUNT, ac_ahisto.MODIFIEDBY, ac_ahisto.MODIFIEDON \
					  FROM ac_chisto \
					  JOIN ac_ahisto ON ac_chisto.HDOCNO = ac_ahisto.HDOCNO \
					       AND ac_chisto.HDBK = ac_ahisto.HDBK \
						   AND ac_chisto.HFYEAR = ac_ahisto.HFYEAR AND ac_chisto.HMONTH = ac_ahisto.HMONTH \
					  WHERE ac_chisto.HAMOUNT = 1288.38 AND ac_chisto.HORDERNO = 2 AND ac_chisto.HDOCDATE = {d '2013-10-31'} AND ac_chisto.HID LIKE '" + strVendorId + "' ";

			data = db.getLine(ConnectId, strSQL);
		}
		catch(e) {
			var error = e;
			data = null;
            log.error("Error getting BOB Invoice data: " + error);
		}

		return data;

        if (log.isDebugEnabled()) log.debug("getInvoiceDataBy");
    },

	/**
    * Return the Mandant used in Bob from the Data\Common\Companiy.adt
    * @ConnectId Connection Id for the FOLDER Database of Bob
	* @strBarcode The Barcode Data to identify the document
    * @return null on error.
    */
    getSupplierData: function (ConnectId, strCID) {

		var data;
		var strSQL;

		if (log.isDebugEnabled()) log.debug("getDocumentData(");

        try {

			strSQL = "SELECT CNAME1, CVATREF, CVATNO, CID, CSUPTYPE \
					    FROM ac_compan \
					   WHERE CID = '" + strCID + "'";

			data = db.getLine(ConnectId, strSQL);
		}
		catch(e) {
			var error = e;
			data = null;
            log.error("Error getting BOB Supplier data: " + error);
		}

		return data;

        if (log.isDebugEnabled()) log.debug(")getDocumentData");
    },

	/**
    * Unblocks the invoice in Bob.
	* Update fields in ac_chisto.adt and ac_kh%JOURNAL%.adt (ex: ac_khafg.adt or ac_khnca.adt
	* Using the BobId AND the barcode to update in Bob.
    */

	unblockInvoice: function (ConnectId, BobId, barcode, journal) {

		var strSQL;

		if (log.isDebugEnabled()) log.debug("unblockInvoice(");

		var result = 0;

        try {
			// update ac_khXXX.adt
			log.debug("update ac_kh" + journal + ".adt");
			strSQL = "Update ac_kh" + journal + " \
			          SET [TISBLOCKED] = False, \
					      [TPROCSTATUS] = '' \
					  WHERE (TDOCNO = " + BobId + ")";

			db.doUpdate(ConnectId, strSQL);

			// update ac_chisto.adt
			log.debug("update ac_chisto.adt");
			strSQL = "Update ac_chisto \
			          SET [HPROCSTATUS] = '' \
					  WHERE (HDOCNO = " + BobId + ") \
					  AND (HREMINT = '" + barcode + "')";

			db.doUpdate(ConnectId, strSQL);

			if (log.isDebugEnabled()) log.debug(")unblockInvoice");

			result = 1;
			return result;
		}
		catch(e) {
			var error = e;
            log.error("Error unblocking invoice in Bob: " + error);
		}
    },

	/**
    * Adds the link to the physical document to BOB.
	* Update fields in ac_linkdoc.adt
	* Using the vendorNumber, periodYear, Journal, and BobId insert in Bob.
    */

	attachFileToDocument: function (ConnectId, vendorNumber, periodYear, Journal, BobId, fileName) {

		var strSQL;

		if (log.isDebugEnabled()) log.debug("attachFileToDocument(");

		var result = "";


		// insert into ac_linkdoc.adt
		log.debug("insert into ac_linkdoc.adt");
		strSQL = "INSERT INTO ac_linkdoc (LHTYPE, LHID, LHFYEAR, LHDBK, LHDOCNO, LNAMEDOC) \
				  VALUES ('S','" + vendorNumber + "','" + periodYear + "','" + Journal + "'," + BobId + ",'" + fileName +"')";

		db.doUpdate(ConnectId, strSQL);

		if (log.isDebugEnabled()) log.debug(")attachFileToDocument");

		result = fileName;

		return result;

    },

	/**
    * Gets the link to the physical document from BOB.
	* Using the vendorNumber, periodYear, Journal, and BobId insert in Bob.
    */

	getAttachedFileFromDocument: function (ConnectId, vendorNumber, periodYear, Journal, BobId) {

		var data;
		var strSQL;

		if (log.isDebugEnabled()) log.debug("getAttachedFileFromDocument(");

		var result = "";

        try {
			log.debug("select from ac_linkdoc.adt");
			strSQL = "SELECT * \
						FROM ac_linkdoc \
						WHERE LHTYPE = 'S' \
						AND LHID = '" + vendorNumber + "' \
						AND LHFYEAR = '" + periodYear + "' \
						AND LHDBK = '" + Journal + "' \
						AND LHDOCNO = " + BobId;

			data = db.getLine(ConnectId, strSQL);

			if (log.isDebugEnabled()) log.debug(")getAttachedFileFromDocument");

			result = data;

		}
		catch(e) {
			var error = e;
            log.error("Error getting the file from Bob: " + error);
		}

		return result;

    }
}
// end of namespace ixbob